Conversion of DB2 Data to SQL Server through DTS

Last weeks article, "Configuration of STARSQL 3.0" showed you how to prepare the StarSQL driver
for SQL Server to access DB2. This week, we build on that knowledge and have the assumption that your StarSQL ODBC driver has
already been configured. We will dig a little deeper into configuring SQL Server’s DTS (Data Transformation Services) to import
DB2 data and schemas.

A major consideration we must review is the datatypes of your DB2 data. You will need
to check the datatypes on DB2 and SQL Server to make sure that they will
support each other. The chart below covers covers the supportibilty of the datatypes (see books online for more information).

Select a source server

  • Select the destination server and database name you’d like to export the data to (see figure 2).

    Select a destination server and database name
    FIGURE 2 (click for full size)

  • Check the tables you’d wish to import from the destination DB2 server. (HINT : If you are seeing any tables
    here, then your connection to DB2 works and you’ve gotten the hard part working!)

    Check the tables you'd like to import
    FIGURE 3 (click for full size)

  • If you click to the right of any table, under the transform column you will get a column mapping screen (see figure 4).
    This gives you the flexiblity to customize the desination tables. You can also turn on here identity inserting, which will keep
    the integrety of your identity fields during an insert. If this import will be a repetitive task, then you may want to drop and recreate
    the table if doing a complete refresh of the tables.

    The column mapping screen
    FIGURE 4 (click for full size)

  • You may want to modify the schema of the table that will be created on your SQL Server DBMS. You can do this by clicking Edit SQL (see figure 5) and making the necessary changes. Generally it is not a good idea to change the SQL Server defaults during the initial loads. I’ve found SQL Server to have a pretty good native system for making schema creations.

    The Edit SQL Screen
    FIGURE 5 (click for full size)

  • Also in the Column Mappings screen is the Transformations tab (see figure 7). This tab gives you the option
    of adding some scripting business logic into the import of your DB2 data. You can by default use Javascript or ActiveX. You can also click on the browse icon to install
    a custom scripting language, such as Pearl. I would advice against you doing this however, due to the native ActiveX scripting runs much faster. I will go into this option in more detail in a later article.

    The Transformations tab
    FIGURE 6 (click for full size)

  • After completion of the transform section, the wizard will ask you if you’d like to save the package and run immediately or schedule if for a later run.
    Most DB2 databases can be extremely large in size, so it may be better if you schedule the DTS package to run later during an off peak hour. If you also plan to
    repeat the importing process or you are in development, you can save time by saving the DTS package. The repository provides a useful tool for viewing metadata on the DTS package; the only
    drawback being that it is slower loading and more I/O intensive.

    Save or Schedule
    FIGURE 7 (click for full size)

  • Your task will then execute. You may notice in the below DTS package running (figure 8) the error message in the drop table statement.
    This is due to my selection to create the database and drop the database in the transformation section (see figure 4). In other words, SQL Server is issuing a drop command on a table that does not exist yet.

    The executing task
    FIGURE 8 (click for full size)

  • You can later view the package and execute it again in the Data Transformation Services section of Enterprise Manager. Double Click on the package name (if you saved it), and the generic package you created will come up for edit (see figure 8).
    We will go into much further detail about DTS in a later article. Again, feel free to email me if you have any questions. Microsoft will support this also if you are using StarSQL as a driver.

  • Previous articleConfiguration of StarSQL 3.0
    Next articledb_Compare.sql

    Latest Articles