DB2 unload utility using the program DSNTIAUL

Unloading Data from DB2 Using DSNTIAUL: A Step-by-Step Guide

IBM Mainframe developers frequently need to extract database records for testing, reporting, or migration. When it comes to unloading data from DB2 using DSNTIAUL, you are utilizing one of the most reliable and time-tested methods available.

While IBM offers the newer DSNUTILB (Fast Unload Utility) for high-performance needs, the DSNTIAUL program remains a widely used standard. Developers typically execute it under TSO terminal monitors like IKJEFT01, IKJEFT1A, or IKJEFT1B.

Understanding the DSNTIAUL Program Types

Before writing your JCL, you must decide how to extract your records. The DSNTIAUL utility natively supports two types of data unloads:

• Full Table Unload: This method automatically dumps every single row and column from a target table.
• Selective SQL Unload: This method filters and retrieves specific rows based on a custom SQL query, such as using a WHERE clause.

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

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: Performing a 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
/*
//*

Note: In this specific configuration, the system unloads the complete contents of DBCREATOR.TABLENAME into the SYSREC00 sequential dataset. Meanwhile, the SYSPUNCH DD automatically generates the matching load control statements for future use.

How to Run a Selective Unload with SQL

Consequently, if you only need a specific subset of data, you must modify your approach. To unload rows using a custom SQL statement (such as incorporating WHERE conditions or an explicit SELECT *), you must add the PARM('SQL') parameter to your SYSTSIN statement.

If you omit the PARM('SQL') parameter while passing a SQL statement, the system will likely fail and return a SQLCODE -104 syntax error.

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.