Creating and populating the database

These instructions assume that you are using DB2 Universal Database. When you create and access the RESERVDB tables, be aware of the following issues:

The following script creates and populates the database for the Airline Reservations sample:

CREATE DB RESERVDB
CONNECT TO RESERVDB
BIND '%DB2TEMPDIR%\bnd\@db2cli.lst' blocking all grant public
CATALOG SYSTEM ODBC DATA SOURCE RESERVDB
CONNECT RESET
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))
CONNECT RESET
TERMINATE

When you run the script, it performs the following tasks:

To create and populate the database:

  1. Copy and paste the script into a text editor, such as Notepad, and save the file. You can choose any name for the file but the following instructions assume that you have called the file airlinedb2.sql.
  2. In a DB2 command window, enter the following command to ensure that DB2 is started:

    db2start

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

    db2 -vf airlinedb2.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 drop any existing tables called XMLFLIGHTTB and XMLPASSENGERTB before it creates them but if you have not run the script before, it can't find the tables.

  4. Start the DB2 Control Center to check that the database, RESERVDB, and tables, XMLFLIGHTTB and XMLPASSENGERTB, have been created.

Main Page icon   Back to Building the Airline Reservations sample