Limited supportBMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see UNLOAD PLUS for DB2 13.1.

Example 14 — Handling an abnormal termination

This example illustrates how to set up UNLOAD PLUS to delete specific files and issue a particular return code should an abnormal termination occur.

This job illustrates the following features:

  • If the unload is unsuccessful and you specify ON FAILURE ALL TERMINATE UTILITY RETCODE returnCode, UNLOAD PLUS terminates and issues the return code that you specify. Having UNLOAD PLUS issue a particular return code enables you to perform an additional step based on that return code.
  • If you also specify DELETEFILES YES, UNLOAD PLUS deletes all SYSREC, SYSRED, and SORTWK files. Using this feature reduces DASD overhead and results in better data integrity by eliminating data sets that could contain only part of the data.

Message 50041I indicates the status of zIIP processing. For this example, the ZIIP installation option is ENABLED, but an XBM subsystem was not specified. UNLOAD PLUS automatically located an available XBM subsystem to provide zIIP processing.

The following table describes the key command options for this job:

Command options used in JCL

Description

DIRECT NO

Tells UNLOAD PLUS to use Db2 dynamic SQL to process the SELECT statements and read the table data

Using DIRECT NO allows you to unload data by using the full range of functionality that the Db2 SQL SELECT statement provides. However, be aware that DIRECT NO is not a high-performance solution for unloading large volumes of data.

ON FAILURE

Tells UNLOAD PLUS how to handle an abnormal termination (when UNLOAD PLUS ends with a return code that is greater than or equal to 8, or when it abends)

ALL TERMINATE UTILITY RETCODE 11

Specifies that, if a failure occurs in any phase, you want the utility to terminate and issue return code 11

DELETEFILES YES

Deletes the SYSREC, SYSRED, and SORTWK files if a failure occurs

UNLOADDN SYSREC ACTIVE NO

Disables dynamic allocation for the primary unload data set, overriding the default in the installation options module

SELECT *

Unloads all columns

FROM

Specifies the table to unload

WHERE

Qualifies the data to unload

The following figure shows the JCL for example 14:

//        JOB
//UNLOAD14 EXEC PGM=ADUUMAIN,REGION=0M,
//         PARM=(DEHJ,'ADUXM14','NEW ',,'MSGLEVEL(1)')
//*********************************************************************
//STEPLIB  DD DISP=SHR,DSN=<product.libraries>
//         DD DISP=SHR,DSN=DB2.DSNEXIT
//         DD DISP=SHR,DSN=DB2.DSNLOAD
//SYSIN    DD   *
  UNLOAD
         DIRECT NO
         ON FAILURE ALL TERMINATE UTILITY RETCODE 11
         DELETEFILES YES
         UNLOADDN SYSREC ACTIVE NO        
*
    SELECT *
           FROM SYSIBM.SYSCOLUMNS A
           WHERE TBCREATOR='SYSIBM';
/*
//SYSREC01 DD DSN=ADU.EXAMPL14.SYSREC01,DISP=(NEW,CATLG),
//            UNIT=SYSDA,SPACE=(TRK,(1,0),RLSE)
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*

SYSPRINT for example 14

The following figure shows the SYSPRINT for example 14:

                                *****  B M C   U N L O A D   P L U S   F O R   D B 2    V11R1.00 *****
                                             (C) COPYRIGHT 1991 - 2013 BMC SOFTWARE, INC.
 BMC50001I UTILITY EXECUTION STARTING   10/28/2013   13:08:33 ...
 BMC50002I UTILITY ID = 'ADUXM14'.  DB2 SUBSYSTEM ID = 'DEHJ'.  OPTION MODULE = 'ADU$OPTS'.
 BMC50471I z/OS 2.1.0,PID=HBB7790,DFSMS FOR Z/OS=2.1.0,DB2=11.1.0
 BMC50471I REGION=0M,BELOW 16M=8844K,ABOVE 16M=1413184K,IEFUSI=NO,CPUS=3
 BMC50471I MEMLIMIT=17592186040320M,AVAILABLE=17592186040311M,MEMLIMIT SET BY:REGION=0

 BMC50471I UNLOAD PLUS FOR DB2--V11.01.00
 BMC50471I   NO MAINTENANCE TO REPORT
 BMC50471I DB2 UTILITIES COMMON CODE--V11.01.00
 BMC50471I   NO MAINTENANCE TO REPORT
 BMC50471I SOLUTION COMMON CODE--V11.01.00
 BMC50471I   MAINT: BPJ0661  BPJ0667  BPJ0671  BPJ0674  BPJ0675  BPJ0676  BPJ0682  BPJ0686  BPJ0689  BPJ0697
 BMC50471I BMCSORT ENGINE--V02.04.01
 BMC50471I   MAINT: BPJ0691
 BMC50471I BMC STATS API--V11.01.00
 BMC50471I   MAINT: BPU5409  BPU5534  BPU5674
 BMC50471I EXTENDED BUFFER MANAGER--V06.01.00
 BMC50471I   MAINT: BPE0401  BPE0403  BPE0405  BPE0407  BPE0410  BPE0412  BPE0416

 BMC50471I ANALYZE=(DB2STATS,NOLIMIT)              FORCE_RPT=NO                            SMAX=16
 BMC50471I CENTURY=(1950,2049)                     FORCE=NONE                              SMCORE=(0K,0K)
 BMC50471I CHANGE_CONSISTENT=NO                    HISTORY=YES                             SORTNUM=32
 BMC50471I CHANGE_QUIESCE=NO                       IBUFFS=25                               SQLDELAY=3
 BMC50471I CMAX=16                                 INLINE=NO                               SQLRETRY=100
 BMC50471I CMRATIO=50                              LOADDECP=NO                             TAPEDISP=DELETE
 BMC50471I CONSTRULES=BMC                          LOCKROW=YES                             TASKMAX=200%
 BMC50471I CURRENTDEGREE=NONE                      MAXP=5                                  UBUFFS=25
 BMC50471I DELFILES=YES                            MSGLEVEL=1                              UNLDMAX=200%
 BMC50471I DRNDELAY=1                              NULLCHAR=X'6F'                          UNLOADDN_ACTIVE=(YES,NO)
 BMC50471I DRNRETRY=255                            NULLTYPE=T1                             UNLOADDN=(SYSREC,SYSRED)
 BMC50471I DRNWAIT=NONE                            OPNDB2ID=YES                            USELRECL=NO
 BMC50471I DSPLOCKS=DRNFAIL                        PLAN=ADUQA                              UXSTATE=SUP
 BMC50471I EXCLDUMP=(X37,X22,X06)                  RECFM=AUTO                              WORKUNIT=SYSALLDA
 BMC50471I FILEREFDN=SYSREF                        ROWSETSZ=100                            ZIIP=ENABLED
 BMC50471I FILL=NO                                 SDUMP=YES                               ZONEDDECOVP=(C,D)
 BMC50471I FORCE_AT=(START,3)                      SHRLEVEL=REFERENCE


 BMC50471I TAPES=NONE

 BMC50470I OUTPUT   = SYSREC                             SYSRED                               SYSREF
 BMC50470I UNIT     = SYSALLDA                           SYSALLDA                             SYSALLDA
 BMC50470I VOLCNT   = 25                                 25                                   25
 BMC50470I GDGLIMIT = 5                                  5                                    5
 BMC50470I GDGEMPTY = NO                                 NO                                   NO
 BMC50470I GDGSCRAT = NO                                 NO                                   NO
 BMC50470I STORCLAS = NONE                               NONE                                 NONE
 BMC50470I DATACLAS = NONE                               NONE                                 NONE
 BMC50470I MGMTCLAS = NONE                               NONE                                 NONE
 BMC50470I UNITCNT  = 0                                  0                                    0
 BMC50470I SPACE    = CYL                                CYL                                  CYL
 BMC50470I PCTPRIM  = AUTO                               AUTO                                 AUTO
 BMC50470I MAXPRIM  = 0                                  0                                    0
 BMC50470I MAXSECD  = 0                                  0                                    0
 BMC50470I FILESZPCT  100                                100                                  100
 BMC50470I NBRSECD  = AUTO                               AUTO                                 AUTO
 BMC50470I DISKRETN = NONE                               NONE                                 NONE
 BMC50470I DISKEXPD = NONE                               NONE                                 NONE
 BMC50470I RETPD    = NONE                               NONE                                 NONE
 BMC50470I EXPDT    = 99000                              99000                                99000
 BMC50470I TRTCH    = NONE                               NONE                                 NONE
 BMC50470I DSNTYPE  = NONE                               NONE                                 PDS
 BMC50483I SYSREC     VOLUMES=NONE
 BMC50483I SYSRED     VOLUMES=NONE
 BMC50483I SYSREF     VOLUMES=NONE

 BMC50483I SYSREC     DSNAME=&USERID.&TYPE.S&SELNUM
 BMC50483I SYSRED     DSNAME=&USERID.&TYPE.S&SELNUM
 BMC50483I SYSREF     DSNAME=&USERID.BMC


 BMC50471I DB2 DSNHDECP MODULE SETTINGS:
 BMC50471I VERSION                 = 1110
 BMC50471I SUBSYSTEM DEFAULT       = DEHJ
 BMC50471I CHARACTER SET           = ALPHANUM
 BMC50471I DATE FORMAT             = USA
 BMC50471I TIME FORMAT             = USA
 BMC50471I LOCAL DATE LENGTH       = 0
 BMC50471I LOCAL TIME LENGTH       = 0
 BMC50471I DECIMAL POINT           = PERIOD
 BMC50471I DECIMAL ARITHMETIC      = 15
 BMC50471I DELIMITER               = DEFAULT
 BMC50471I SQL DELIMITER           = DEFAULT
 BMC50471I ENCODING SCHEME         = EBCDIC
 BMC50471I APPL. ENCODING SCHEME   = EBCDIC
 BMC50471I MIXED                   = NO
 BMC50471I EBCDIC CCSID            = (37,65534,65534)
 BMC50471I ASCII CCSID             = (819,65534,65534)
 BMC50471I UNICODE CCSID           = (367,1208,1200)
 BMC50471I IMPLICIT TIME ZONE      = CURRENT (-05:00)

 BMC50028I DB2 MODE = NFM
 BMC50471I BMC_BMCUTIL       ='BMCUTIL.CMN_BMCUTIL'
 BMC50471I BMC_BMCSYNC       ='BMCUTIL.CMN_BMCSYNC'
 BMC50471I BMC_BMCHIST       ='BMCUTIL.CMN_BMCHIST'
 BMC50471I BMC_BMCXCOPY      ='BMCUTIL.CMN_BMCXCOPY'


 BMC50102I   UNLOAD
 BMC50102I          DIRECT NO
 BMC50102I          ON FAILURE ALL TERMINATE UTILITY RETCODE 11
 BMC50102I          DELETEFILES YES
 BMC50102I          UNLOADDN SYSREC ACTIVE NO
 BMC50102I *
 BMC50102I     SELECT *
 BMC50102I            FROM SYSIBM.SYSCOLUMNS A
 BMC50102I            WHERE TBCREATOR = 'SYSIBM';

 BMC51654I DIRECT NO IN EFFECT

 BMC50109I 'DIRECT NO' OPTION SPECIFIED.  OPTION 'SHRLEVEL' IGNORED
 BMC50353I FOR BIT DATA COLUMN HIGH2KEY WILL NOT BE TRANSLATED
 BMC50353I FOR BIT DATA COLUMN LOW2KEY WILL NOT BE TRANSLATED
 BMC51689I ESTIMATED ROWS FOR SELECT 1 IS 0

 BMC50004I UTILINIT PHASE COMPLETE.  ELAPSED TIME = 00:00:00

 BMC51639I FOR DDNAME 'SYSREC01' DSN=ADU.EXAMPL14.SYSREC01,DCB=(RECFM=VB,BLKSIZE=27993,LRECL=7199)
 BMC50041I 0: ZIIP  ENABLED (0) USING XBM SUBSYSTEM XBMB
 BMC50474I BELOW 16M = 8508K, ABOVE 16M = 1407052K, CPUS = 3
 BMC51702I MAX TASKS = 1
 BMC50411U UNLOAD TASK NUMBER 1 ABNORMALLY TERMINATED WITH SYSTEM ABEND CODE = D37, REASON CODE = 00000004
 BMC50476I DDNAME = SYSREC01, I/OS = 1, I/O WAITS = 0, RDB LOCK WAITS = 0
 BMC51676I UNLOAD STATISTICS:  0 ROWS PROCESSED FOR SELECT STATEMENT NO. 1, 0 DISCARDED
 BMC51674I UNLOAD STATISTICS:  3415 RECORDS WRITTEN TO DDNAME 'SYSREC01'
 BMC51675I UNLOAD STATISTICS:  0 RECORDS DISCARDED DUE TO ERRORS
 BMC50041I 0: ZIIP NOT ENABLED (0) USING XBM SUBSYSTEM XBMB
 BMC50019I UTILITY REQUESTED TO TERMINATE DUE TO FAILURE IN PHASE 'UNLOAD'

 BMC50894I DELETE FILES PROCESS STARTING AT  10/28/2013  13:08:34
 BMC50318I DATASET SUCCESSFULLY DELETED, DDNAME = 'SYSREC01', DSNAME = 'ADU.EXAMPL14.SYSREC01'
 BMC50895I DELETE FILES PROCESS COMPLETE.  ELAPSED TIME =  00:00:00

 BMC50006I UTILITY EXECUTION COMPLETE, RETURN CODE = 11

Related topics

Examples-of-UNLOAD-PLUS-jobs

DELETEFILES

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*