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.

with    cte(schema) as
        (select schemaname from syscat.schemata where schemaname in ('RAGHUC')),
        cte1(schema, table, mod) as
        (select distinct tbcreator as schema, tbname as table,
        case WHEN (name='SYS_START' and generated='A') THEN 'PERIODOVERRIDE' WHEN (name='SYS_END' and generated='A') THEN 'PERIODOVERRIDE'
        WHEN (name='TRANS_START' and generated='A') THEN 'TRANSACTIONIDOVERRIDE'
        WHEN (name='SYS_START' and generated='') THEN 'PERIODOVERRIDE' WHEN (name='SYS_END' and generated='') THEN 'PERIODOVERRIDE'
        WHEN (name='TRANS_START' and generated='') THEN 'TRANSACTIONIDOVERRIDE'
        WHEN (generated='A' and identity='N') THEN 'GENERATEDOVERRIDE'
        WHEN (generated='A' and identity='Y') THEN 'IDENTITYOVERRIDE' 
        WHEN generated='D' THEN 'GENERATEDOVERRIDE'
        END AS GENERATED
        from sysibm.syscolumns c, sysibm.tables t
        where c.tbcreator=t.table_schema and c.tbname=t.table_name
        and t.table_schema in (select schema from cte)
        and t.table_type='BASE TABLE' and ((c.generated<>'') or (c.generated='' and c.name in ('SYS_START','SYS_END','TRANS_START')))),

    cte2(schema, table, mod, rank) as
        (select *, (select count(*) as count from cte1 b where cte1.schema=b.schema and cte1.table=b.table and cte1.mod>b.mod) as counter
        from cte1),

    load_mod (schema, table, modlist) as
        (select schema, table, cast(listagg((case when rank=0 then ''  || trim(mod)
                                         when rank>0 then ' ' || trim(mod) end
                      ), '') as varchar(100)) as modlist from cte2 group by schema, table),

    column_list as (
    select
       substr(i.table_schema,1,30) as indschema,
       substr(i.table_name,1,100) as indname,
       listagg(CAST(
              case
                  when ic.ordinal_position = 1 then ' ' || case when (DATA_TYPE='DECIMAL' and IS_NULLABLE='YES') THEN 'COALESCE(' || '"' || trim(COLUMN_NAME) || '"' || ',' || 'CAST(NULL as decimal))
                                                           AS ' || '"' || trim(COLUMN_NAME) || '"' ELSE '"' || trim(COLUMN_NAME) || '"' END
                  when ic.ordinal_position > 1 then ', ' || case when (DATA_TYPE='DECIMAL' and IS_NULLABLE='YES') THEN 'COALESCE(' || '"' || trim(COLUMN_NAME) || '"' || ',' || 'CAST(NULL as decimal))
                                                           AS ' || '"' || trim(COLUMN_NAME) || '"' ELSE '"' || trim(COLUMN_NAME) || '"' END
              end
          as varchar(32000)), '') within group (order by ic.ordinal_position) as colnames
    from    sysibm.tables as i
       join sysibm.columns as ic
           on i.table_schema = ic.table_schema
           and i.table_name = ic.table_name
    where   i.table_schema IN (select schema from cte)
           and i.table_type = 'BASE TABLE'
           and (select max(c.ordinal_position) from sysibm.columns c where c.table_name = i.table_name) < 250
    group by    i.table_schema,
           i.table_name,
           i.table_schema,
           i.table_name
    order by    i.table_schema,
           i.table_name,
           i.table_name )

    select 'DECLARE CW CURSOR DATABASE SOURCEDB USER db2inst1 using password123 for SELECT '
            || trim(colnames) || ' from ' ||  '"' || trim(indschema) || '"' || '.' || '"' || trim(indname) || '"' || ' WITH UR;'
            || x'0A' ||
            'LOAD FROM CW OF CURSOR '
            || CASE WHEN MODLIST IS NULL THEN '' ELSE 'MODIFIED BY ' || MODLIST END || ' WARNINGCOUNT 10 MESSAGES /db2loads/messages/'
            || trim(indname) || '.msg REPLACE INTO ' ||  '"' || trim(indschema) || '"' || '.' || '"' || trim(indname) || '"' || ' NONRECOVERABLE ALLOW NO ACCESS;' as loadcmd
    from column_list c left join load_mod t on c.indschema=t.schema and c.indname=t.table with ur;

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.

export to load_from_cursor.sql of del modified by chardel~
with cte(schema) as
(select schemaname from syscat.schemata where schemaname in ('RAGHUC')),
cte1(schema, table, mod) as
(select distinct tbcreator as schema, tbname as table,
case WHEN (name='SYS_START' and generated='A') THEN 'PERIODOVERRIDE' WHEN (name='SYS_END' and generated='A') THEN 'PERIODOVERRIDE'
WHEN (name='TRANS_START' and generated='A') THEN 'TRANSACTIONIDOVERRIDE'
WHEN (name='SYS_START' and generated='') THEN 'PERIODOVERRIDE' WHEN (name='SYS_END' and generated='') THEN 'PERIODOVERRIDE'
WHEN (name='TRANS_START' and generated='') THEN 'TRANSACTIONIDOVERRIDE'
WHEN (generated='A' and identity='N') THEN 'GENERATEDOVERRIDE'
WHEN (generated='A' and identity='Y') THEN 'IDENTITYOVERRIDE'
WHEN generated='D' THEN 'GENERATEDOVERRIDE'
END AS GENERATED
from sysibm.syscolumns c, sysibm.tables t
where c.tbcreator=t.table_schema and c.tbname=t.table_name
and t.table_schema in (select schema from cte)
and t.table_type='BASE TABLE' and ((c.generated<>'') or (c.generated='' and c.name in ('SYS_START','SYS_END','TRANS_START')))),

cte2(schema, table, mod, rank) as
(select *, (select count(*) as count from cte1 b where cte1.schema=b.schema and cte1.table=b.table and cte1.mod>b.mod) as counter
from cte1),

load_mod (schema, table, modlist) as
(select schema, table, cast(listagg((case when rank=0 then '' || trim(mod)
when rank>0 then ' ' || trim(mod) end
), '') as varchar(100)) as modlist from cte2 group by schema, table),

column_list as (
select
i.table_schema as indschema,
i.table_name as indname,
listagg(CAST(
case
when ic.ordinal_position = 1 then ' ' || case when (DATA_TYPE='DECIMAL' and IS_NULLABLE='YES') THEN 'COALESCE(' || '"' || trim(COLUMN_NAME) || '"' || ',' || 'CAST(NULL as decimal))
AS ' || '"' || trim(COLUMN_NAME) || '"' ELSE '"' || trim(COLUMN_NAME) || '"' END
when ic.ordinal_position > 1 then ', ' || case when (DATA_TYPE='DECIMAL' and IS_NULLABLE='YES') THEN 'COALESCE(' || '"' || trim(COLUMN_NAME) || '"' || ',' || 'CAST(NULL as decimal))
AS ' || '"' || trim(COLUMN_NAME) || '"' ELSE '"' || trim(COLUMN_NAME) || '"' END
end
as varchar(32000)), '') within group (order by ic.ordinal_position) as colnames
from sysibm.tables as i
join sysibm.columns as ic
on i.table_schema = ic.table_schema
and i.table_name = ic.table_name
where i.table_schema IN (select schema from cte)
and i.table_type = 'BASE TABLE'
and (select max(c.ordinal_position) from sysibm.columns c where c.table_name = i.table_name) < 500
group by i.table_schema,
i.table_name,
i.table_schema,
i.table_name
order by i.table_schema,
i.table_name,
i.table_name )

select 'DECLARE CW CURSOR DATABASE SOURCEDB USER db2inst1 using password123 for SELECT '
|| trim(colnames) || ' from ' || '"' || trim(indschema) || '"' || '.' || '"' || trim(indname) || '"' || ' WITH UR;'
|| x'0A' ||
'LOAD FROM CW OF CURSOR '
|| CASE WHEN MODLIST IS NULL THEN '' ELSE 'MODIFIED BY ' || MODLIST END || ' WARNINGCOUNT 10 MESSAGES /db2loads/messages/'
|| trim(indname) || '.msg REPLACE INTO ' || '"' || trim(indschema) || '"' || '.' || '"' || trim(indname) || '"' || ' NONRECOVERABLE ALLOW NO ACCESS;' as loadcmd
from column_list c left join load_mod t on c.indschema=t.schema and c.indname=t.table with ur;

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 ?