 | Level: Intermediate Shishir Narain (nshishir@in.ibm.com), Staff Software Engineer, IBM
27 Jul 2006 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.
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:
- Ability to customize the program: The source code is included as a download in this article.
- Ability to exclude columns: While generating data, you can exclude columns and the script will adhere by not generating data for them.
- 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
Using the utility
The program takes in the following input:
- Table name: The name of the table for which insert scripts need to be created.
- File name: The name of the file used by the UDF for writing the script.
- Exclude columns: The name of the columns that you want to be excluded can be null.
- 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
- If the columns contain LOB datatype variants, then the following error is reported:
"The program failed. Check if table contains incompatible datatype."
- 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 | Description | Name | Size | Download method |
|---|
| Windows version of script | create_inserts.zip | 2KB | HTTP |
|---|
| UNIX version of script | create_inserts.tar | 10KB | HTTP |
|---|
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
About the author | | | 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."
|
Rate this page
| |
