Restore the samples on IBM® DB2 using a script

You can use scripts to restore backup files for sample databases for DB2®.

To set up the sample database, you must extract the GS_DB tar.gz file, customize a configuration file, and run the setup script.

There are prerequisites for installing the Great Outdoors sample database for DB2 on Linux, UNIX and Windows. Before you can install the sample databases, you must verify or configure privileges.

Extract the GS_DB.tar.gz file and retain the original directory structure. If you use WinZip to extract the DB2 move file on Microsoft Windows operating system, ensure that the TAR file smart CR/LF conversion option is not selected.
On Linux and UNIX operating systems, modify the file permissions on the setupGSDB.sh file so that it is executable: chmod u+x setupGSDB.sh.
Ensure that the user ID used to set up the database has DBADM authority or the following authorities in DB2:
- CREATETAB
- CREATE_NOT_FENCED_ROUTINE
- LOAD

Optional: Editing the configuration file

The configuration file contains the default configuration options that are used when creating the GOSALES data. The default configuration settings are listed in the following table.

Table 1. Optional values for restoring the samples on IBM DB2
Configuration Setting	Default	Description
GOSALES_INST	GS_DB	Used to set the name or alias of the database.
GOSALES_BLU	N	Change to 'Y' if creating tables organized by column.
GOSALES_CREATEDB		Optional: Causes an existing database with the same name to be dropped.
GOSALES_DB_TERRITORY	US	When creating a database this is the territory of the UTF-8 database that is created.
GOSALES_BP GOSALES_TS	GOSALES_BP GOSALES_TS	Optional: Enter the buffer pool and tablespace name, if these are to be created by the script.
GOSALES_GRANTEES	GOSALES, DB2ADMIN	Enter the list of users, groups or PUBLIC that will have CONTROL permissions for the GOSALES, GOSALESHR, GOSALESMR and GOSALESRT schemas. This string needs to follow the syntax of the GRANT command.
GOSALESDW_GRANTEES	GOSALESDW DB2ADMIN	Enter the list of users, groups or PUBLIC that will have CONTROL permissions for the GOSALESDW schema.
GOSALES_DPF	N	Change to 'Y' if installing a database partitioned environment (DPF)
GOSALES_SCHEMA GOSALESHR_SCHEMA GOSALESMR_SCHEMA GOSALESRT_SCHEMA GOSALESDW_SCHEMA	GOSALES GOSALESHR GOSALESMR GOSALESRT GOSALESDW	Enter the names to be used for each schema.

You can customize the sample configuration file to use settings other than the default values.

The setup script creates the GS_DB database, table spaces, tables, views, grants privileges, and modifies the schema names for the sample database. In most situations, you can accept the default options. If you want to change the database name or modify the users or groups that have permissions on the data, you must update the GOSalesConfig configuration file.

Edit the configuration file by using a text editor.

Note: If you edit UNIX shell scripts in a Windows environment, ensure that you preserve the UNIX line endings.

The configuration file on Windows is GOSalesConfig.bat. The configuration file on UNIX is GOSalesConfig.sh.

By default, the GS_DB database name is used and permissions are granted to the DB2ADMIN (Linux, UNIX, Windows) and GOSALES users.

Running the setup script in interactive mode

In interactive mode, the setupGSDB script prompts you to confirm or provide configuration information for the GS_DB database installation. You can accept the default settings or provide different settings to replace the defaults.

Run the setup script for your operating system.

Table 2. Running the samples restore script
Operating System	Command
Microsoft Windows	In a DB2 command window, change to the GS_DB/win directory and run the setupGSDB.bat script.
UNIX	From a shell prompt, source the db2profile change to the GS_DB/unix directory, and run the setupGSDB.sh script.

Press Enter to proceed. The script displays a summary of your choices before you commit to changes to your environment. If you approve the choices, press Enter and the script makes the changes. For example, you might see the following message:

Please confirm the following settings:
Database Name: GS_DB
Column-organized tables: N (DB2 on UNIX only)
Drop and Recreate Database: Y
DPF environment: N
Create a 32 K Bufferpool named: GOSALES_BP
Create a 32 K Tablespace named: GOSALES_TS
GOSALES Grant users/groups: GOSALES, DB2ADMIN
GOSALESDW Grant users/groups: GOSALESDW, DB2ADMIN
Administration User Name: db2admin
Import the sample data to the following schemas:
GOSALES
GOSALESHR
GOSALESMR
GOSALESRT
GOSALESDW
WARNING: If the database GS_DB already exists it will be dropped
Continue creating the sample data with these settings? (Y/N) Default=Y:

The GS_DB database is set up.

Running the setup script with command line options

The setupGSDB script lets you provide information on the command line to reduce the number of prompts from the script.

From a command line, run the script for your operating system. On Windows use setupGSDB.bat. On UNIX or Linux operating systems use setupGSDB.sh.

You can run the setupGSDB script with the following options:

Table 3. setupGSDB options for IBM DB2
Option	Description
-createdb	Creates the database. This option drops any existing database with the same name. It creates the required buffer pool and table space.
-database database name	Specifies the name of the database. This value overrides the default value of GS_DB.
-userid administration_user_ID	Specifies the name of the DB2 administrator user ID that is used to create the database.
-password administration_user_ID	Specifies the password for the DB2 administrator user ID.
-noprompt	Indicates that no prompt will display. This option runs the script in silent mode. Any missing information causes the script to fail. You will not be prompted for any confirmations.

Example 1: You are a DB2 administrator and want to create the default GS_DB database on the local node. You run the following command:

setupGSDB -createDB -noprompt

Example 2: You want to create the tables in an existing database named GSDBY, and you want to use the administrator user ID db2admin. Run the following command:

setupGSDB -database GSDBY -userid db2admin

The script prompts you for the password when it connects to GSDBY. The script will replace any tables that already exist in the GSDBY database, unless you choose to drop the database.

Optional: Installing the sample data on a remote server

If the GS_DB sample database is installed on a remote server in your environment, you can link to it by cataloguing the remote database on your local computer and then running the setup script locally.

If the sample database does not yet exist on the remote server, create it with the CREATE DATABASE command. The database requires a UTF-8 codeset and a bufferpool pagesize of 32 KB for the default and temporary table spaces. For example, on the remote server, create the database by running the following command:
```
CREATE
DATABASE GS_DB USING CODESET UTF-8 TERRITORY US PAGESIZE 32K
```

On your local computer, catalog the remote database:

db2
catalog tcpip node nodename remote ipaddr server port_number
db2 catalog database GS_DB as GS_DB at node nodename

On your local computer, run the script:

setupGSDB
-database GS_DB -userid administration_user_ID

You are prompted for a password to connect to the database.