Creating the SAMPLE Database on iSeries

DB2 UDB for iSeries comes with a stored procedure to create a SAMPLE database. You can create the SAMPLE database as follows:

 CALL QSYS.CREATE_SQL_SAMPLE('SAMPLE'); 

The stored procedure creates the sample tables under the schema that is passed in as a parameter (in this case, SAMPLE) and populates them with some data as well. Although the tables created on iSeries are a superset of the tables created on LUW, the data with which they populate differ on the two platforms, and in some cases the tables on iSeries have additional columns. A few tables on iSeries have had aliases created for them to match the table name on LUW, because the table names are different but the table definitions are the same.

Other differences such as the creation of primary keys, foreign keys, and indexes also exist across the platforms. To make the iSeries database objects a complete superset of the LUW objects, you need to create additional objects, as shown in Figure F.1

Figure F.1. Additional DDL for iSeries.

 -- DDL  Statements for primary key on Table STAFF ALTER  TABLE ORG   ADD  PRIMARY KEY           (DEPTNUMB); ---  DDL Statements for primary key on Table STAFF ALTER  TABLE STAFF   ADD  PRIMARY KEY           (ID); 

For reference, Figure F.2 shows the DDL for the SAMPLE database on LUW. You can use this DDL to create the tables for the SAMPLE database, however they will not contain any data. Running the CREATE_SQL_SAMPLE stored procedures will also populate the tables with sample data.

Note

The accompanying CD has the DDL creation script corresponding to the LUW database. It also contains the sample data as populated in the LUW platform. You can use the files if you want identical SAMPLE databases (definition and data) on both platforms.

In addition to the SAMPLE database tables, you still need to create a few other tables to ensure that you can run every example shown in this publication. Figure F.3 provides the DDL for the additional tables. You should create these tables using the same schema used to create the SAMPLE database tables.