< Previous | Next >

Creating new tables in the SAMPLE database

Use the SQL editor in the workbench to save and run a SQL script that creates three tables with XML columns.
Tip: You can also run this SQL script using the DB2® Command Line Processor.

To create new tables in the SAMPLE database:

  1. Right-click the SQL Scripts folder in the XMLSchema project that you created in the last lesson.
  2. Select New > SQL or XQuery Script
  3. In the Name field, type CREATE_TABLES, select the SQL editor as the editor, and click Finish. The SQL editor opens. The SQL script is displayed in the Data Project Explorer in your project.
  4. Copy and paste the following text into the SQL editor:
    --DROP TABLE_TEST_SCHEMA.NOTE;
    CREATE TABLE TEST_SCHEMA.NOTE
      ( 
      TO           CHAR (50),
      FROM         CHAR (50),
      HEADING      CHAR (50),
      BODY         CHAR (50)  
      ) ;
    
    --DROP TABLE TEST_SCHEMA.CUSTOMER;
    CREATE TABLE TEST_SCHEMA.CUSTOMER 
      ( 
      CID        BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 32672 NO CYCLE CACHE 20),
      INFO       XML,
      HISTORY    XML
      ) ;
    
    --DROP TABLE TEST_SCHEMA.PurchaseOrder
    CREATE TABLE TEST_SCHEMA.PurchaseOrder 
      ( 
      POID   BIGINT NOT NULL,
      STATUS VARCHAR(10) NOT NULL,
      CUSTID BIGINT,
      ORDERDATE DATE,
      PORDER XML,
      COMMENTS VARCHAR(1000)
      ) ;
  5. Save the SQL statement in the editor.
  6. Right-click in a blank area of the editor, and select Run SQL. The SQL script runs on the database, and three new tables are created and displayed in the Data Source Explorer in the TEST_SCHEMA relational schema. The SQL Results view displays the results of the actions, including any messages or errors.
    Screen capture showing the new tables in the Data Source Explorer, as described.
< Previous | Next >

Feedback