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-management system with all the tools and utilities you would expect. With the DB2 .NET Enablement product, many of these tools are integrated into Visual Studio. There are, however, a few DB2 tools you should use to perform common functions such as creating databases and tables. This article explores how to use these tools as you create a copy of the Music database to be used in future DataStream columns.
Create a DB2 Database
Before you can experiment with the DB2 tools in Visual Studio, you need to create a DB2 database. Although you can do this by coding the proper SQL statements, you'll find it much easier to use the DB2 Control Center utility. This utility lets you create databases, tables, views, stored procedures, and other database objects (see Figure 1).
Figure 1. The DB2 Control Center
provides the tools needed to create databases, tables, views, stored
procedures, and other database objects.
To create a new database using the DB2 Control Center, choose Start | Programs | IBM DB2 | General Administration Tools | Control Center. In the left pane, you'll see an icon tree. Drill down the tree until you reach the Databases node, right-click on it, and choose Create | Database Using 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 and alias, and DataStream Sample Database for the comment. Continue through the wizard by clicking on the Next button. Then click on Finish to create the database. Note that creating the database can take several minutes.
Create DB2 Tables
Once you've created a database, you can create a table using the DB2 Control Center. Expand the icon tree to show your new database and the objects it contains. (If your database isn't listed, right-click on the Databases icon and choose Refresh.) Right-click on the Table icon and choose Create from the pop-up menu. This starts the Create Table Wizard. The wizard will 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 columns in the table. Because this is a new table, no columns are listed. Click on the Add button to display the Add Column dialog box (see Figure 2). Enter the column name and choose the appropriate data type. Once all the information has been entered, click on OK to close the dialog box, or click on Apply to save the information about the column and leave the dialog box open for the next column.
Figure 2. The Add Column dialog box
allows you to define a new column in the table.
The columns and their data types found in the Customer table 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 data through the Control Center by right-clicking on the table name and choosing Load from the pop-up menu. This starts the Load Wizard, which collects the information needed to load a disk file into the table. The first step of the wizard lets you specify whether the incoming data replaces the data in the table or appends the data to the table. Because this table is empty, either choice is fine.
In the second step, you need to choose the input file format. This article's downloadable sample code is in Integrated Exchange Format (IXF) and is named CUSTOMERS.IXF. You then need to specify the full path name of the input file along with the full path name of a file that will contain any error messages generated by the load process.
You can skip through the remaining steps in the wizard until you reach the Scheduling Task Execution step. Now choose Run without saving the task history and click on the Finish button to load the data.
If you are comfortable with DB2 and the DB2 Command Center, you can combine the table creation and data loading into a single SQL command. This command creates the CUSTOMERS table using the information from the IXF file, then loads the data directly into the newly created table:
IMPORT FROM c:\customers.ixf OF IXF MESSAGES c:\customers.msg
CREATE INTO ADMINISTRATOR.CUSTOMERS;
The sample code in this article is available for download.
Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send e-mail to mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.
