Create DB2 Databases and Tables: Part II

DataStream

LANGUAGES: VB

TECHNOLOGIES: ASP.NET | ADO.NET | SQL Server | Access | DB2

Create DB2 Databases and Tables: Part II

Use the DB2 tools to create a copy of a database.

By Wayne S. Freeze

Like SQL Server, DB2 is a full-fledged database-managementsystem with all the tools and utilities you would expect. With the DB2 .NETEnablement product, many of these tools are integrated into Visual Studio.There are, however, a few DB2 tools you should use to perform common functionssuch as creating databases and tables. This article explores how to use thesetools as you create a copy of the Music database to be used in futureDataStream columns.

Create a DB2 Database

Before you can experiment with the DB2 tools in VisualStudio, you need to create a DB2 database. Although you can do this by codingthe proper SQL statements, you'll find it much easier to use the DB2 ControlCenter utility. This utility lets you create databases, tables, views, storedprocedures, and other database objects (see Figure 1).

Figure 1. The DB2 Control Centerprovides the tools needed to create databases, tables, views, storedprocedures, and other database objects.

To create a new database using the DB2 Control Center,choose Start | Programs | IBM DB2 | General Administration Tools | ControlCenter. In the left pane, you'll see an icon tree. Drill down the tree untilyou reach the Databases node, right-click on it, and choose Create | DatabaseUsing Wizard.

The wizard then prompts you for the name of the database,the database alias, and a comment. Specify Music for both the database name andalias, and DataStream Sample Database for the comment. Continue through thewizard by clicking on the Next button. Then click on Finish to create thedatabase. Note that creating the database can take several minutes.

Create DB2 Tables

Once you've created a database, you can create a tableusing the DB2 Control Center. Expand the icon tree to show your new databaseand the objects it contains. (If your database isn't listed, right-click on theDatabases icon and choose Refresh.) Right-click on the Table icon and chooseCreate from the pop-up menu. This starts the Create Table Wizard. The wizardwill prompt you for the table's schema (use ADMINISTRATOR), the table's name(use CUSTOMERS), and comments. Click on the Next button to continue.

The second step of the wizard contains a list of columnsin the table. Because this is a new table, no columns are listed. Click on theAdd button to display the Add Column dialog box (see Figure 2). Enter thecolumn name and choose the appropriate data type. Once all the information hasbeen entered, click on OK to close the dialog box, or click on Apply to savethe information about the column and leave the dialog box open for the nextcolumn.

Figure 2. The Add Column dialog boxallows you to define a new column in the table.

The columns and their data types found in the Customertable are:

CUSTOMER, INTEGER

NAME, VARCHAR (64)

STREET, VARCHAR (64)

CITY, VARCHAR (64)

STATE, CHARACTER (64)

ZIPCODE, VARCHAR (10)

COUNTRY, SMALLINT

PHONE, VARCHAR (32)

EMAILADDRESS, VARCHAR (128)

DATEADDED, TIMESTAMP

DATEUPDATED, TIMESTAMP

MAILINGLIST, SMALLINT

COMMENTS, VARCHAR (256)

Load DB2 Data

Now that you have a table, you can load the sample datathrough the Control Center by right-clicking on the table name and choosingLoad from the pop-up menu. This starts the Load Wizard, which collects theinformation needed to load a disk file into the table. The first step of thewizard lets you specify whether the incoming data replaces the data in thetable or appends the data to the table. Because this table is empty, eitherchoice is fine.

In the second step, you need to choose the input fileformat. This article's downloadable sample code is in Integrated ExchangeFormat (IXF) and is named CUSTOMERS.IXF. You then need to specify the full pathname of the input file along with the full path name of a file that willcontain any error messages generated by the load process.

You can skip through the remaining steps in the wizarduntil you reach the Scheduling Task Execution step. Now choose Run withoutsaving the task history and click on the Finish button to load the data.

If you are comfortable with DB2 and the DB2 CommandCenter, you can combine the table creation and data loading into a single SQLcommand. This command creates the CUSTOMERS table using the information fromthe IXF file, then loads the data directly into the newly created table:

IMPORT FROM c:customers.ixf OF IXFMESSAGES c:customers.msg

   CREATE INTO ADMINISTRATOR.CUSTOMERS;

The sample code in thisarticle is available for download.

Wayne S. Freeze isa full-time computer book author with more than a dozen titles to his credit,including Windows Game Programming with VisualBasic and DirectX (Que) and UnlockingOLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25years of experience using all types of computers, from small, embeddedmicroprocessor control systems to large-scale IBM mainframes. Freeze has amaster's degree in management information systems as well as degrees incomputer science and engineering. You can visit his Web site at http://www.JustPC.comand send e-mail to mailto:[email protected].He loves reading e-mail from his readers, whose ideas, questions, and insightsoften provide inspiration for future books and articles.