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

Back to top

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

Back to top

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:

				
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)
                                )
      

Back to top

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.

Back to top

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,?)" 

  
  Value of output parameters
  --------------------------
  Parameter Name  : P_MESSAGE
  Parameter Value : Processing Done

  Return Status = 0

 

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');

Back to top

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.

Back to top

Downloads

Information about download methods

Resources

Learn

• developerWorks DB2 for Linux, UNIX, and Windows product page: Read articles and tutorials and connect to other resources to expand your DB2 skills.
  
  
• DB2 Express-C product page: Learn about the no-charge version of DB2 Express Edition for the community.
  
  
• "A UDF for File Output and Debugging from SQL" (developerWorks, February 2003): Get details on how to use the file output UDF.
  
  
• developerWorks Information Management zone: Expand your skills on DB2 and other IBM Information Management products.
  
  
• Stay current with developerWorks technical events and Webcasts.
  
  

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

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Prerequisites and limitations
• Tested environments
• About the script
• Installation
• Using the utility
• Samples
• Error conditions
• Source code
• Conclusion
• Downloads
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers