SQLUTB UNLOAD

SQLUTB UNLOAD unloads data from a table in a DBMS, and places it into an output data set file. You can use SQLUTB UNLOAD to unload all rows from a table, or unload only those rows that meet certain criteria, such as those selected from a provided field specification list. Output data sets are compatible with z/OS DB2 in both encoding and formatting, and can be used as input to SQLUTB LOAD.

Important: You can configure the SQLUTB LOAD date format to specify the format(s) of date values in the input dataset. See mbdtconfig command for details.

Executing SQLUTB UNLOAD

SQLUTB UNLOAD is similar to DSNUTILB LOAD. Include an SQLUTB UNLOAD card in your JCL, and execute it by submitting the JCL directly using the JES CONTROL feature in Enterprise Server. The JCL syntax must be z/OS DB2 compatible.

The following is an example of JCL using a UNLOAD card:

//ULEMP001  JOB CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID,REGION=0M
//DELETE  EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  DELETE MCS.UNLOAD.ULEMP001
  SET    MAXCC=0
//DSNUTILB EXEC PGM=HCOUTILB,PARM='HCOD,ULEMP001'
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(10,10))
//SORTOUT  DD UNIT=SYSDA,SPACE=(CYL,(10,10))
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSPUNCH DD SYSOUT=*
//SYSREC   DD DSN=MCS.UNLOAD.ULEMP001,DISP=(MOD,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(1,1)),
//            DCB=(RECFM=VB,LRECL=107,BLKSIZE=6500)
//SYSIN    DD *
   UNLOAD DATA FROM TABLE [DSN8910].[EMP] EBCDIC
/*                                                                      
//
//         PARM='&SYSTEM,&UID,&UTPROC' 
//*STEPLIB  DD   DSN=&LIB,DISP=SHR    
//* 
//SYSPRINT DD   SYSOUT=* 
//UTPRINT  DD   SYSOUT=* 
//SYSUDUMP DD   SYSOUT=*

Where HCOD is the ODBC XAR ID for your database connection, SQLUTB has been established as an alias for program HCOUTILB, and MCS.UNLOAD.ULEMP001 is the cataloged output data file.

In contrast to DB2 for z/OS DSNUTILB UNLOAD, the default code set for the output of SQLUTB UNLOAD is ASCII instead of EBCDIC. Also, the default record format, RECFM, is FB. You can override the RECFM default with DCB in the SYSREC definition.

UNLOAD Options

Supported Specifications and Options
  • ASCII – default code set
  • EBCDIC – to override the default ASCII if needed
  • MAXERR
  • NOPAD
  • NOSUBS
  • PUNCHDDN
  • PART (for DB2 LUW only)
  • TABLESPACE – does not apply to SQL Server
  • UNICODE – code set
  • UNLDDN
Unsupported Specifications and Options
  • CHARDEL
  • COLDEL
  • CCSID
  • DECFLOAT_ROUNDMODE
  • DECPT
  • DELIMITED
  • FLOAT IEEE
  • FLOAT S390
  • FORMAT INTERNAL
  • FROMCOPY
  • FROMCOPYDDN
  • LIST
  • PART (for all DBMSs except DB2 LUW)
  • SHRLEVEL
Supported FROM TABLE Options
  • FIELD SPECIFICATION
  • HEADER
  • POSITION
  • STRIP
  • TRUNCATE
  • WHEN
Unsupported FROM TABLE Options
  • LIMIT
  • SAMPLE
Supported TABLE FIELD TYPES
  • BIGINT
  • BINARY
  • CHAR
  • CINSTANT
  • DATE EXTERNAL
  • DECIMAL (PACKED)
  • DECIMAL EXTERNAL
  • DOUBLE
  • FLOAT EXTERNAL
  • FLOAT
  • GRAPHIC
  • INTEGER EXTERNAL
  • INTEGER
  • REAL
  • SMALLINT
  • TIME EXTERNAL
  • TIMESTAMP EXTERNAL
  • VARBINARY
  • VARCHAR
  • VARGRAPHIC
Unsupported TABLE FIELD TYPES
  • BLOB
  • CLOB
  • DBCLOB
  • GRAPHIC EXTERNAL
  • ROWID
Note: To ensure proper parsing of SYSIN, do not include the tab character (ASCII decimal character code 9; EBCDIC decimal character code 5) in its contents. When parsed, the tab character could cause spurious, unintended errors.