Raghu On Tech

Pro tips on managing data at scale

DB2 Load From Cursor Command Generator

by | Apr 30, 2020 | 1 comment

Problem:

Very often I work on moving data from one environment to another or from one schema to another. More often than not, I use Db2‘s LOAD utility to move the data quickly. However, when using Db2 LOAD utility depending on the properties of different columns you may need to generate LOAD commands differently.

For e.g. if the table have columns that are GENERATED BY DEFAULT or GENERATED ALWAYS or TEMPORAL type you need to write the LOAD command with different modifiers.

In addition to these, I frequently run into issue when loading data with DECIMAL type columns that are NULLABLE due to Db2‘s inability to CAST these NULL‘s properly. Please take a look at below tech note for more info on this issue.

https://www.ibm.com/support/pages/node/277607

In addition you may want to generate the “LOAD FROM CURSOR” command for a whole schema or multiple schema’s at once. How would you write a script by taking all these factors into consideration ? I wrote below SQL and it saved me quite sometime recently.

 

Solution 1:

This solution works in almost all cases except when tables have large number of columns. Problem is output is being truncated and I am not 100% sure if its due to listagg function or due to the output SQL text exceeding 32K bytes.

Use the following SQL to generate the load from cursor commands for schema RAGHUC by running it against the TARGET database. On 05/10/2020, I updated the SQL so that schema name is specified in only one place. You may specify any number of schema’s in the inlist of the query.

Also this SQL does a remote load from SOURCEDB to TARGETDB. If you are just reading and loading into the same database you just need to modify the DECLARE CURSOR statement at the end of this SQL by removing the database name and authentication details.

 

Solution 2:

I had good luck with this solution regardless the number of columns in the table, I have some tables with close to 400 columns and had no problem generating the load from cursor commands. This solution makes use of export command to generate the load from cursor SQL commands.

Since default export command character delimiter is double quotes, and I have double quotes in my output SQL string to handle the lower case schema, table and column names, I had to choose my export delimiter to be “~” (tilde). Once the export command output is generated just replace “~” with empty string using your favorite editor and you should be ready to roll.

 

Running the above SQL’s will provide you with both DECLARE CURSOR and LOAD commands. You can simply redirect the output to a file and run the file from command line. You may even use this SQL or variation of this SQL to create a shell script like I did with exception handling and alerting.

Unfortunately I can’t share the shell script but I hope this SQL will help you generate the LOAD commands you need and will protect you from running into the same issues that I ran into while doing the data movement between schemas.

PS: Please change the LOAD type from NONRECOVERABLE to COPY YES if you are running LOAD’s in a production or in a HADR environment. A good article on how to perform LOAD‘s in a HADR environment below.

How To Perform Loads In A HADR Environment ?

 

1 Comment

  1. Daniel

    Hi Raghu
    I’ts an awesome script.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Shares
Share This
%d bloggers like this:

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close