Setting up DB2

These instructions assume that you are using DB2 Universal Database and guide you through the following tasks:

Create a database called USERDB for use by the Data Warehouse sample
Bind the DB2 CLI packages
Create a table called DATAWAREHOUSE for use by the Data Warehouse sample
Create an ODBC definition for the database

Note. When you create and access the DATAWAREHOUSE table, be aware of the following issues:

When you run the script that creates the table in the USERDB database, the user identifier from which you run the script determines the database schema name. For example, if your user identifier is USERID, the fully qualified name of the DATAWAREHOUSE table is USERID.DATAWAREHOUSE.
When you set a node to access a table, if the integration node is not using the same user identifier to access the database that you used when you created the database and table, include the schema name in the node. For example, Database.USERID.DATAWAREHOUSE.

Complete the following steps:

Open a new document in a text editor, copy and paste the following script, which creates the USERDB database and runs the BIND utility, into the document, name the file datawarehouse1.sql and save the file.
- On Windows:
  You must replace install_dir with the path to your DB2 product installation directory, such as C:\PROGRA~1\IBM\SQLLIB. You can find this location by using the db2level command:
```
CREATE DB USERDB
CONNECT TO USERDB
BIND 'install_dir\bnd\@db2cli.lst' blocking all grant public
TERMINATE
      
```
- On Linux:
  You must replace install_dir with the path to your DB2 product installation directory. You can find this location by using the db2level command:
```
CREATE DB USERDB
CONNECT TO USERDB
BIND install_dir/bnd/@db2cli.lst grant public CLIPKG 5
TERMINATE
      
```

Open a new document in a text editor, copy and paste the following script, which creates the DATAWAREHOUSE table, into the document, name the file datawarehouse2.sql and save the file.

CONNECT TO USERDB
DROP TABLE DATAWAREHOUSE
CREATE TABLE DATAWAREHOUSE ( MESSAGEDATA BLOB  (1 M )  NOT NULL , PUTTIMESTAMP TIMESTAMP  NOT NULL , DBINSERTTIMESTAMP TIMESTAMP  NOT NULL  WITH DEFAULT CURRENT_TIMESTAMP )
TERMINATE

In a DB2 command window, enter the following command to ensure that DB2 is started:
```
db2start
```
In the same DB2 command window, return to the command prompt by typing the following command:
```
quit
```
Navigate to the folder where you saved the file datawarehouse1.sql in step 1 above and enter the following command:
```
db2 -vf datawarehouse1.sql
```
Navigate to the folder where you saved the file datawarehouse2.sql in step 2 above and enter the following command:
```
db2 -vf datawarehouse2.sql
```
Wait for the script to finish running. If you are running the script for the first time, the following message is displayed, where DB2ADMIN is your user name:
```
DB21034E The command was processed as an SQL statement because it was not a valid Command Line 
      Processor command. During SQL processing it returned: 
      SQL0204N "DB2ADMIN.DATAWAREHOUSE" is an undefined name. SQLSTATE=42704
```
Ignore this message. The message is displayed because the script attempts to remove an existing table called DATAWAREHOUSE before it creates a new table, but if you have not run the script before, the script cannot find the existing table.
Create an ODBC definition for the USERDB database with USERDB as the data source name (DSN).
For instructions, see Enabling ODBC connections to the databases in the IBM Integration Bus documentation.

Back to Setting up the database

Back to sample home