|DB2 Express Loading Data|
Feature: Loading data into DB2 Express using the Load Wizard
To start the load process, highlight the FMASTER table in the upper content pane and the right mouse click on it. A popup menu should appear and choose the Load menuitem. That in turn after about 5-8 seconds cause the Load Wizard to appear and so you are ready to load in data.
Note that the FBID field has been chosen as the primary key and it uses a unique coding of the first and last name of each player. This is one of the nice features of the DB2 load process - DB2 wizards try to anticpate your needs during each step with simple default choices that often work. But users always have recourse to simple data entry choices that can over ride the defaults easily.
Also DB2 wizards always alow shortcuts. Users can finish early and by pass unneeded step. In addition, Table schema data entry is simplified as we shall see just below.
Remember the load task bypasses all triggers and constraint checks; so users must be confident that their data is clean - especially the primary key values are not null nor are they duplicated. Also foreign keys must be valid as well.
The next stage involve specifying the input file format, what the exact set of delimiters that will be used with that input format. There are four types of input file formats allowed in the Load utility: DEL- field delimited format in which some delimiter is used to separate the fields. The fields themselves may also have delimiters - text fields delimited by quotes for example. By clicking on the DEL Options button, users can specify exactly what the delimiters are or specify "none apply". ASC format implies fields in ASCII format and fixed columns, IXF is the Internal Exchange Format which has its own format rules for data exchange, and WSF is the Lotus WorkSheet Format for exchanging data between Lotus 123 spreadsheet and a database.
In this case DEL is the option and after an aborted run we clicked on the
DEL Option button and specified that VARCHAR fields were not delimited and
the default field delimiter was comma. Then we declared mast.csv on the D:
drive as the input source file and after dragging the scroll bar down added
logout.txt as the message logfile.
In our Load operation, the input fields and table fields exactly matched and so no corrections had to be made. The next step of the Load Wizard is Performance. Here users control validity checking, creation or maintenance of indexes and other performance considerations. As this is a first load, none were required. Recovery is the fifth phase of the load Wizard and checkpoints after so many rows are loaded can be set as well as several recovery options. Since the database is relatively small no checkpoints were established and the default recovery options were otherwise used.
The sixth stage, Options, specifies fairly advanced load options associated with large-scale or complex loads (many tables and tablespaces effected) - again we let the default settings stand here and the 7th, Schedule stage (which allows for timed batch processing of the job/task). Stage 8, Summary just tells what is going to be done and so we clicked Finish and less than 10 seconds later the job was done. Not too bad.
Testing the Data
The nice thing about Control Center is that it is easy to test the data
by just opening the table and inspecting the data. We did that and then did
a couple of queries on the data using the query feature of Control Center.
For the SQL newbies/or the rusty-of-skills, Control Center offers SQL Assist that takes a user through all the steps of putting together a query. This is an important feature not apparently available in Oracle 10g or MySQL so we shall cover it in more detail in a special review.
This time there were less documentation hiccups, and DB2 Express performed admirably on load. Admittedly the conditions were benign - no pre-existing tables, simple .csv source data, no concurrent users to complain because of slowed response time during load. But the load was fairly fast for over 3700 records. Top marks to DB2 Express so far for its Load capabilities. The only missing link, XML support; but I just didn't have the heart to go traipsing through Info Center trying to find out what DB2 Express has there.Top of Page Home Tutorials