DBzTech-Technology Dossier

Unloading Data from DB2 Using DSNTIAUL

IBM provides two primary methods to unload data from a DB2 table:

1. DSNTIAUL program – an older but widely used method, typically run under IKJEFT01, IKJEFT1A, or IKJEFT1B.
2. DSNUTILB program – IBM’s newer Fast Unload Utility, designed for better performance.

Each has its own features, so it’s worth reviewing them before choosing the right one. Below, we’ll focus on DSNTIAUL.

1. DSNTIAUL Program

The DSNTIAUL utility supports two types of unloads:

• Full table unload – dumps all rows from a table.
• Selective SQL unload – retrieves rows based on a SQL query (e.g., with WHERE clauses).

When using DSNTIAUL, the key DD statements are:

• SYSIN → defines the table or SQL to execute.
• SYSTSIN → controls how DSNTIAUL is invoked.

Both methods typically run under IKJEFT1B.

📌 JCL Example – Full Table Unload

//UNLOAD   EXEC PGM=IKJEFT1B, DYNAMNBR=20
//STEPLIB  DD DSN=SYSDB2.DQA0.SDSNLOAD, DISP=SHR
//         DD DSN=SUBSYS.DB2.DSNEXIT, DISP=SHR
//*************************************************************
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSREC00 DD DSN=userid.tablerecords.dataset,
//            DISP=(,CATLG,DELETE),
//            UNIT=SYSDA,SPACE=(CYL,(300,300),RLSE)
//*
//SYSPUNCH DD DSN=userid.tablelayout.dataset,
//            UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE),
//            DISP=(,CATLG,DELETE)
//SYSTSIN  DD *
  SYSTEM (SUBSYS) RUN PROGRAM (DSNTIAUL) PLAN (DSNTIAUL) -
  LIBRARY ('SUBSYS.DB2.RUNLIB.LOAD')
  END
//SYSIN DD *
  DBCREATOR.TABLENAME
/*
//*

In this example, the entire contents of DBCREATOR.TABLENAME are unloaded.

📌 Selective Unload with SQL

If you want to unload rows using a SQL statement (e.g., with WHERE conditions or SELECT *), you must add PARM('SQL') in the SYSTSIN statement. Without it, you’ll likely encounter SQLCODE -104.

Example JCL:

//SYSTSIN DD *
  SYSTEM (SUBSYS) RUN PROGRAM (DSNTIAUL)
         PLAN (DSNTIAUL) -
         LIBRARY ('SUBSYS.DB2.RUNLIB.LOAD') PARM('SQL')
  END
//SYSIN DD *
  SELECT * FROM schema.Tablename;
/*

Here, the query unloads rows from schema.Tablename based on the SQL provided.