Setting up DB2

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

• Create a database called RESERVDB for use by the Airline Reservations sample
• Bind the DB2 CLI packages
• Create tables called XMLFLIGHTTB and XMLPASSENGERTB for use by the Airline Reservations sample
• Populate the sample data into the XMLFLIGHTTB table
• Create an ODBC definition for the database

Note. When you create and access the RESERVDB tables, be aware of the following issues:

• When you run the script that creates the tables in the RESERVDB 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 XMLFLIGHTTB table name is USERID.XMLFLIGHTTB.
• 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 tables, include the schema name in the node, for example, Database.USERID.XMLFLIGHTTB

Complete the following steps:

1. Open a new document in a text editor, copy and paste the following script, which creates the RESERVDB database and runs the BIND utility, into the document, name the file airline1.sql and save the file.

   • On Windows:

     You must replace install_dir with the path to your DB2 product installation directory, for example, C:\PROGRA~1\IBM\SQLLIB

     This location can be obtained by using the following db2level command:

     CREATE DB RESERVDB
     CONNECT TO RESERVDB
     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. This location can be obtained by using the following db2level command:

     CREATE DB RESERVDB
     CONNECT TO RESERVDB
     BIND install_dir/bnd/@db2cli.lst grant public CLIPKG 5
     TERMINATE
           

2. In a DB2 command window, enter the following command to ensure that DB2 is started:

   db2start

3. In a DB2 command window, navigate to the folder that contains airline1.sql and enter the following command:

   db2 -vf airline1.sql

4. Open a new document in a text editor, copy and paste the following script, which creates the XMLFLIGHTTB and XMLPASSENGERTB tables and populates the XMLFLIGHTTB table, into the document, name the file airline2.sql and save the file.

   CONNECT TO RESERVDB
   DROP TABLE XMLFLIGHTTB
   CREATE TABLE XMLFLIGHTTB (FLIGHTDATE CHAR(8) NOT NULL, FLIGHTNO CHAR(5) NOT NULL, ECONOMICCLASS INTEGER NOT NULL, FIRSTCLASS INTEGER NOT NULL, TOTALECONOMIC INTEGER NOT NULL, TOTALFIRST INTEGER NOT NULL, ECONOMICPRICE INTEGER NOT NULL, FIRSTPRICE INTEGER NOT NULL, STARTPOINT CHAR(20) NOT NULL, ENDPOINT CHAR(20) NOT NULL, RESERVATIONSEQNO INTEGER NOT NULL, CONSTRAINT FLIGHT_UNIQ UNIQUE(FLIGHTDATE, FLIGHTNO))
   INSERT INTO XMLFLIGHTTB VALUES('20030218', 'CA937', 0, 0, 200, 50, 200, 300, 'BEIJING', 'LONDON', 0)
   INSERT INTO XMLFLIGHTTB VALUES('20030525', 'BA039', 0, 0, 180, 40, 220, 320, 'LONDON', 'BEIJING', 0)
   INSERT INTO XMLFLIGHTTB VALUES('20030525', 'CA937', 0, 0, 200, 50, 200, 300, 'BEIJING', 'LONDON', 0)
   INSERT INTO XMLFLIGHTTB VALUES('20030219', 'BA039', 0, 0, 180, 40, 220, 320, 'LONDON', 'BEIJING', 0)
   DROP TABLE XMLPASSENGERTB
   CREATE TABLE XMLPASSENGERTB (LASTNAME CHAR(20) NOT NULL, FIRSTNAME CHAR(20) NOT NULL, FLIGHTNO CHAR(5) NOT NULL, FLIGHTDATE CHAR(8) NOT NULL, CLASSTYPE CHAR(1) NOT NULL, RESERVATIONNO VARCHAR(40) NOT NULL, PRIMARY KEY(RESERVATIONNO))
   TERMINATE
         

5. In a DB2 command window, navigate to the folder that contains airline2.sql and enter the following command:

   db2 -vf airline2.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 and XMLPASSENGERTB is the name of the table:

   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.XMLPASSENGERTB" 
         is an undefined name. SQLSTATE=42704

   Ignore these messages. The messages are displayed because the script attempts to remove any existing tables called XMLFLIGHTTB and XMLPASSENGERTB before it creates the new tables, but if you have not run the script before, the script cannot find any existing tables.
6. Create an ODBC definition for the RESERVDB database with RESERVDB 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