Prepopulation script generator for DB2

An easy way to create insert metadata scripts

Learn about a utility that you can use to create prepopulation scripts for DB2® from existing data in tables. The utility can also be used to create insert scripts. Sample code for the utility is included in this article.

Shishir Narain (nshishir@in.ibm.com), Staff Software Engineer, IBM

Shishir Narain works for the Websphere Partner Gateway team as a database developer, and this work is outcome of a real life problem on the project. He holds a post graduate degree in Industrial and Management Engineering. He has over eight years of software industry experience. When he is not playing with his son, he spends time "testing the Internet."


developerWorks Contributing author
        level

27 July 2006

Also available in Chinese

Introduction

Most business applications need data prepopulated within a database before an application can be used. Normally, prepopulation information can come from spreadsheets, flat files, and so on. This data is then abstracted in the form of Structured Query Language (SQL) scripts, a tedious task if the prepopulation data is huge.

The sample application this article describes can be used in such cases along with LOAD utility to generate the scripts very easily. Also, this program can be used when we have data in tables for which we want to create insert statements. The generator program writes the script into a file that can be used to populate the data across platforms and across servers.

Most of the integrated developement environments (IDEs) have similar features, but you can use this sample program simply from a DB2 command prompt. The other unique characteristics are:

  1. Ability to customize the program: The source code is included as a download in this article.
  2. Ability to exclude columns: While generating data, you can exclude columns and the script will adhere by not generating data for them.
  3. Ability to include a WHERE clause: The program allows you to restrict the rows based on conditions.

Prerequisites and limitations

  • DB2 8.1.2 or above
  • PUT LINE user-defined function (UDF) as described in the article "A UDF for File Output and Debugging from SQL" (developerWorks, February 2003). The UDF in the article is used to write the script in the file. Connect to the link on how to install and use the UDF. The article also explains how to override the default file creation location.

The limitations of this utility are:

  • The LOB and its variant columns cannot be selected
  • The utility has not been tested in globalised locales
  • The utility currently works with a limited set of data types. It can be enhanced by adding program logic for the desired datatype. Please see the source code for details

Tested environments

The utility was tested in a single partition database on the following platforms:

  • Windows 2000 SP4: DB2 V8.1.2
  • Windows 2000 SP4: DB2 V8.2
  • Win XP: DB2 V8.2 Viper RC1
  • Linux AS3, DB2 V8.2

About the script

The script works by getting the data from the table with the conditions provided to the script. It then constructs the SQL file using the data, protecting the data types and then writing into the output file using Izuha's file writing utility. Here is the signature of the procedure:

Listing 1. Create insert procedure signature
CREATE PROCEDURE Create_Inserts(
 IN   p_Table_Name    VARCHAR(100)
,IN   p_file_name     VARCHAR(100)
,IN   p_exclude_cols  VARCHAR(100)
,IN   p_where         VARCHAR(500)
,OUT  p_message       VARCHAR(100)
                                )

Installation

  • Windows

    Follow these steps to prepare to use the script on Windows:

    1. Download create_inserts.zip from the article, unzip it to an appropriate directory, and change to that directory.
    2. Open a DB2 command window and establish the database connection.
    3. Add the procedure to the database using the create_inserts.sql file which is included with create_inserts.zip:

      db2 connect to database user username using password
      
      db2 -td/ create_inserts.sql
  • UNIX

    Follow these steps to prepare to use the script on UNIX:

    1. Download create_inserts.tar and extract the tar file.
    2. Connect as the db2 instance user.
    3. Add the procedure to the database using the create_inserts.sql file which is included with create_inserts.tar:

      tar -xvf create_inserts.tar
      
      db2 connect to database user username using password
      
      db2 -td/ create_inserts.sql

Using the utility

The program takes in the following input:

  1. Table name: The name of the table for which insert scripts need to be created.
  2. File name: The name of the file used by the UDF for writing the script.
  3. Exclude columns: The name of the columns that you want to be excluded can be null.
  4. Where clause: The limiting condition for extraction can be null.

The program constructs the query using the supplied information, then retrieves the data, formats it, and writes the data into the output file. The program returns an output message that reflects if the Generation was done successfully or not.


Samples

The samples refer to the SAMPLE database that comes with DB2. To install the SAMPLE database, run the command db2sampl from the db2 command prompt. Here are a few sample usage scenarios:

Listing 2. Sample usage scenarios
1. db2 "call create_inserts('ORG','OrgInserts.sql','DEPTNUMB,LOCATION',
'where  MANAGER = 30',?)"

2. db2 "call create_inserts('ORG','OrgEntry.sql',null,'where DEPTNAME = ''Plains''',?)"

3. db2 "call create_inserts('ORG','OrgPrepop.sql',null,null,?)"
Listing 3. Sample program response when it runs successfully
  Value of output parameters
  --------------------------
  Parameter Name  : P_MESSAGE
  Parameter Value : Processing Done

  Return Status = 0
Listing 4. Output generated in the files, as specified in the above three commands
1. -----Created using Insert Generator
INSERT INTO ORG ( DEPTNAME ,MANAGER ,DIVISION)  VALUES( 'South Atlantic' , 30 ,
'Eastern');

2. -----Created using Insert Generator
INSERT INTO ORG ( DEPTNUMB ,DEPTNAME ,MANAGER ,DIVISION ,LOCATION)  VALUES( 
51 , 'Plains' , 140 , 'Midwest' , 'Dallas');

3. -----Created using Insert Generator
INSERT INTO ORG ( DEPTNUMB ,DEPTNAME ,MANAGER ,DIVISION ,LOCATION)  VALUES( 
10 , 'Head Office' , 160 , 'Corporate' , 'New York');
INSERT INTO ORG ( DEPTNUMB ,DEPTNAME ,MANAGER ,DIVISION ,LOCATION)  VALUES( 
15 , 'New England' , 50 , 'Eastern' , 'Boston');
INSERT INTO ORG ( DEPTNUMB ,DEPTNAME ,MANAGER ,DIVISION ,LOCATION)  VALUES( 
20 , 'Mid Atlantic' , 10 , 'Eastern' , 'Washington');
INSERT INTO ORG ( DEPTNUMB ,DEPTNAME ,MANAGER ,DIVISION ,LOCATION)  VALUES( 
38 , 'South Atlantic' , 30 , 'Eastern' , 'Atlanta');
INSERT INTO ORG ( DEPTNUMB ,DEPTNAME ,MANAGER ,DIVISION ,LOCATION)  VALUES( 
42 , 'Great Lakes' , 100 , 'Midwest' , 'Chicago');
INSERT INTO ORG ( DEPTNUMB ,DEPTNAME ,MANAGER ,DIVISION ,LOCATION)  VALUES( 
51 , 'Plains' , 140 , 'Midwest' , 'Dallas');
INSERT INTO ORG ( DEPTNUMB ,DEPTNAME ,MANAGER ,DIVISION ,LOCATION)  VALUES( 
66 , 'Pacific' , 270 , 'Western' , 'San Francisco');
INSERT INTO ORG ( DEPTNUMB ,DEPTNAME ,MANAGER ,DIVISION ,LOCATION)  VALUES( 
84 , 'Mountain' , 290 , 'Western' , 'Denver');

Error conditions

  1. If the columns contain LOB datatype variants, then the following error is reported: "The program failed. Check if table contains incompatible datatype."
  2. If all columns are excluded, then the following error is reported: "The program failed. There were no columns to be selected from the specified table."

Source code

The source code is available under "Downloads" and can be modified if required.

Conclusion

This utility provides the database developers a handy tool for creating prepopulation scripts as well as insert scripts. This utility can be used along with db2look to generate the entire schema information in SQL. The limitation of LOBs can be overcome by using the EXPORT utility to copy the LOB to the filesystem.


Downloads

DescriptionNameSize
Windows version of scriptcreate_inserts.zip2KB
UNIX version of scriptcreate_inserts.tar10KB

Resources

Learn

Get products and technologies

  • Download a free trial version of DB2 9.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
  • IBM trial software: Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=150260
ArticleTitle=Prepopulation script generator for DB2
publish-date=07272006