Details about objects in batch mode
You don't have to use the ISPF panels to generate the reports that contain details about tables, packages, and accelerated tables. Retrieving the details in batch mode can improve deployment and adoption processes.
The results that the detailed batch report generates in the REPORT DD of the job output are similar to the results that are generated by the online DET function.
The following figure shows an example of a generated batch job:
//GETDT1 JOB (ACCOUNTING-INFO),'DB2 UTILITY',
//* RESTART=STEPNAME, <== FOR RESTART REMOVE * AND ENTER STEP NAME
// REGION=0M,NOTIFY=TS5771,
// MSGCLASS=H,
// CLASS=A
//*
//ADBLIBS JCLLIB ORDER=ADB.DEVCUST.PROCLIB
//*
//*
//***********************************************************ADB2SPFB***
//* DB2 ADMIN ISPF BATCH
//**********************************************************************
//S01DET EXEC PGM=IKJEFT01,DYNAMNBR=100
//SYSEXEC DD DISP=SHR,DSN=DMTOOL.SADBEXEC
// DD DISP=SHR,DSN=ADB.VC1APAR.EXEC
// DD DISP=SHR,DSN=GOC.VC1APAR.EXEC
//SYSPROC DD DISP=SHR,DSN=DMTOOL.SADBCLST
// DD DISP=SHR,DSN=ADB.VC1APAR.CLIST
// DD DISP=SHR,DSN=GOC.VC1APAR.CLIST
//ISPLLIB DD DISP=SHR,DSN=DMTOOL.SADBLLIB
// DD DISP=SHR,DSN=ADB.VC1APAR.ISPLLIB
// DD DISP=SHR,DSN=GOC.VC1APAR.ISPLLIB
//STEPLIB DD DISP=SHR,DSN=DMTOOL.SADBLLIB
// DD DISP=SHR,DSN=ADB.VC1APAR.ISPLLIB
// DD DISP=SHR,DSN=GOC.VC1APAR.ISPLLIB
// DD DISP=SHR,DSN=DC1A.SDSNEXIT
// DD DISP=SHR,DSN=DSN.VC10.SDSNLOAD
// DD DISP=SHR,DSN=DSN.VC10.SDSNLOD2
// DD DISP=SHR,DSN='ISP.SISPLOAD'
//ISPMLIB DD DISP=SHR,DSN=DMTOOL.SADBMLIB
// DD DISP=SHR,DSN=ADB.VC1APAR.ISPMLIB
// DD DISP=SHR,DSN=GOC.VC1APAR.ISPMLIB
// DD DISP=SHR,DSN=ISP.SISPMENU
//ISPPLIB DD DISP=SHR,DSN=DMTOOL.SADBPLIB
// DD DISP=SHR,DSN=ADB.VC1APAR.ISPPLIB
// DD DISP=SHR,DSN=GOC.VC1APAR.ISPPLIB
//ISPSLIB DD DISP=SHR,DSN=DMTOOL.SADBSLIB
// DD DISP=SHR,DSN=ADB.VC1APAR.ISPSLIB
// DD DISP=SHR,DSN=GOC.VC1APAR.ISPSLIB
//ISPTLIB DD DSN=&ISPTLIB,DISP=(NEW,DELETE,DELETE),
// DCB=(RECFM=FB,LRECL=80,DSORG=PO),
// SPACE=(80,(1,5,10)),UNIT=SYSALLDA,AVGREC=K
// DD DISP=SHR,DSN=DMTOOL.SADBTLIB
// DD DISP=SHR,DSN=ADB.VC1APAR.ISPTLIB
// DD DISP=SHR,DSN=GOC.VC1APAR.ISPTLIB
// DD DISP=SHR,DSN=ISP.SISPTENU
//ISPPROF DD DSN=&ISPPROF,DISP=(NEW,DELETE,DELETE),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=7920,DSORG=PO)
// SPACE=(80,(1,5,10)),UNIT=SYSALLDA,AVGREC=K
//ISPFILE DD DSN=&ISPFILE,DISP=(NEW,DELETE,DELETE),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=7920,DSORG=PO)
// SPACE=(80,(1,10,10)),UNIT=SYSALLDA,AVGREC=K
//ISPCTL0 DD DSN=&ISPCTL0,DISP=(NEW,DELETE,DELETE),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=7920,DSORG=PS)
// SPACE=(80,(0,5)),UNIT=SYSALLDA,AVGREC=K
//ISPCTL1 DD DSN=&ISPCTL1,DISP=(NEW,DELETE,DELETE),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=7920,DSORG=PS)
// SPACE=(80,(0,5)),UNIT=SYSALLDA,AVGREC=K
//ISPCTL2 DD DSN=&ISPCTL2,DISP=(NEW,DELETE,DELETE),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=7920,DSORG=PS)
// SPACE=(80,(0,5)),UNIT=SYSALLDA,AVGREC=K
//ISPWRK1 DD DSN=&ISPWRK1,DISP=(NEW,DELETE,DELETE),
// DCB=(RECFM=FB,LRECL=256,BLKSIZE=256,DSORG=PS)
// SPACE=(CYL,(5,10)),UNIT=SYSALLDA
//ISPWRK2 DD DSN=&ISPWRK2,DISP=(NEW,DELETE,DELETE),
// DCB=(RECFM=FB,LRECL=256,BLKSIZE=256,DSORG=PS)
// SPACE=(CYL,(5,10)),UNIT=SYSALLDA
//ISPLOG DD SYSOUT=*,DCB=(LRECL=125,BLKSIZE=129,RECFM=VA)
//ISPLIST DD SYSOUT=*,DCB=(LRECL=125,BLKSIZE=129,RECFM=VA)
//REPORT DD SYSOUT=*
//ADBDIAG DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
ISPSTART CMD(%ADBRPGM TS5771P ADBDET DB2SYS(DC1A))
//**********************************************************************
//* END OF ISPF BATCH STEP SET UP
//**********************************************************************
//*
//*************************************************************ADBSBET**
//* START OF PARMS SETUP
//**********************************************************************
//PARMS DD *
TYPE = 'TB' QUAL = 'TS5771' NAME = 'RHPTB%';
TYPE = 'PK' QUAL = 'TDKDB' NAME = 'TRDEL' VERSION = '*';
TYPE = 'AT' QUAL = 'TS577%' NAME = 'T%' ACCELERATOR = '%ACC1';Depending on the object type, you might include the following parameters.
- TYPE
- QUAL
- NAME
- VERSION
- ACCELERATOR
Before you specify values for these parameters, consider the following points that apply to all object types:
- Each TYPE record entry must terminate with a semi-colon (;) to indicate the end of its input parameters.
- The wild cards % or * (all) can be specified for the QUAL, NAME, VERSION and ACCELERATOR parameters.
-
The TYPE parameter is required. The other parameters are optional. For example, leaving the QUAL parameter blank is equivalent to specifying QUAL='*'.
Before you specify values for these parameters, consider the following points that apply to only certain object types:
- Tables
-
- The BET command filters and generates batch jobs only for the following types of tables: C, G, H, P, R, T, X.
- When wild cards are used, details will be generated for above valid table types only. All other table types, which are pulled by using wild cards, are skipped and displayed in SYSPRINT DD. The job will end with RC=4.
- Packages
-
- Each package can include many SQL statements. To avoid long delays or storage and memory issues, consider using absolute values instead of wild cards.
- Accelerated tables
-
- The BET command filters and generates batch jobs only for tables that exist in the SYSIBM.SYSTABLES Db2 catalog table.
- If an error occurs, such as when the definition of a table on Db2 and the accelerator are out of sync, in any table in the list, the error details are displayed in REPORT DD along with details about other valid tables, and the job ends with RC=4. If no other error-free, valid table details can be displayed, the job ends with RC=8. The SYSPRINT DD lists tables that were in error.
The following figure shows an example of a report:
======================
Details for Objects Report
======================
Tables . . . . . . . . . . . . . : 1
Packages . . . . . . . . . . . . : 1
Partitioned Accelerated Tables . : 1
Non-Partitioned Accelerated Tables: 1
Total number of objects . . . . . : 4
=========
Object #1
=========
Details for table (label) : TS5771.RHPTB1
Table information
Table schema . . . : TS5771 Table name . . . . : RHPTB1
Created by . . . . : TS5771 Created . : 2017-06-27-13.49.43.922171
Table space name . : RHPTS1 Database name . . : RHPDB1
Object ID for table: 3 DB ID for database : 549
Maximum row length : 100 Primary key OBID . : N/A
Number of columns : 9 Primary key columns: N/A
Validate procedure : N/A EDIT procedure name: N/A
With row attributes: N/A
Parent relations . : 0 Child relations . : 0
Auditing . . . . . : AUDIT NONE Status . . : No primary key
Data capture . . . : NO Altered . : 2017-06-27-13.49.43.922171
Restrict on DROP . : NO Check constraints : None
Encoding scheme . : U - UNICODE Col. in part. key : 0
Check flag . . . . : No VOLATILE table . . : No
Created in DB2 Ver : Q Dependent MQTS . . : 0
Data version . . . : 0
Table owner . . . : TS5771
Owner type . . . . : Auth ID Append specified . : No
Clone table schema : Clone table name . :
Access control . . : ' ' - Not enforced
Number of hash cols: 0
Versioning schema : Versioning table . :
Archiving schema . : Archiving table . :
Table creation RBA : 00000000008B97D148BA (Hex)
Last alter RBA . . : 00000000008B97D148BA (Hex)
Statistical data . : No valid data available
Stats feedback . . : Yes - statistics recommendations are collected
Associated remarks :
Column information for table : TS5771.RHPTB1
Column Name Col No Col Type Length Scale Null Def FP Col card
------------------ ------ -------- ------ ------ ---- --- -- -----------
EMPNO 1 CHAR 6 0 N N N -1
EMPNAME 2 VARCHAR 30 0 Y Y N -1
HIREDATE 3 DATE 4 0 Y Y N -1
LB1 4 CLOB 4 0 Y Y N -1
LB2 5 CLOB 4 0 Y Y N -1
ISMANAGER 6 INTEGER 4 0 Y Y N -1
DB2_GENERATED 7 ROWID 17 0 N A N -1
LB3 8 CLOB 4 0 Y Y N -1
LB4 9 CLOB 4 0 Y Y N -1
_____________________________________________________________________________
=========
Object #2
=========
Details for package : TRDEL in collection : TDKDB
Package information
Package type . . . . . . . . . . : Trigger package
Version . . . . . . . . . . . . :
Authorization ID of owner . . . : TS5807
Owner type . . . . . . . . . . . : Auth ID
Authorization ID of creator . . : TS5807
Created timestamp . . . . . . . : 2017-09-28-04.42.49.713457
Latest BIND timestamp . . . . . : 2017-09-28-04.42.49.713457
Version under which package bound: V11
Qualifier for unqualified SQL . : TS5807
Operative status of package . . : Package is valid and operative
Resource and authorization check : At BIND time
Size of the base section (bytes) : 3176 (in EDM pool during execution)
Average DML section size (bytes) : 19144 (loaded when needed during exec)
Package bound with EXPLAIN . . . : No
SQLERROR specified at BIND time : No - SQLERROR(NOPACKAGE) specified
BIND or REBIND from remote loc. : No - (RE)BIND was from a local system
Remote packages creation method :
Source of the package . . . . . :
Number of enabled/disabled conn. : 0
Data concurrency . . . . . . . . : B - not required
Effect on blocking . . . . . . : Allow blocking for ambiguous cursors
DEGREE of I/O parallelism . . . : 1 - parallel I/O inhibited
Group member that performed BIND :
Dynamic SQL rules . . . . . . . : Use binders authid and authorizations
Re-optimize SQL at execution time: No - access path determined at BIND time
Defer prepare . . . . . . . . . : No - do not defer prepare to OPEN time
Keep prepared dynamic SQL stmts : No - are destroyed at COMMIT
Protocol for 3 part names . . . : D - uses DRDA
Function resolved at . . . . . . : 2017-09-28-04.42.49.713455
Optimizer hint identifier . . . :
Encode CCSID . . . . . . . . . . : Using EBCDIC default CCSID from install
Write group buffer pool pages . : Normal write
ROUNDING option used on last bind: Round Half Even
Concurrent Access . . . . . . . : Not specified - inherit from DB2 ZPARM
Last date objects used . . . . . : 01/01/0001
SQL path for resolving UDT,UDF,SP: "SYSIBM","SYSFUN","SYSPROC","SYSIBMAD(*1)
Precompiler related information:
Timestamp of precompilation . : 0001-01-01-00.00.00.000000
Consistency token in hex . . . : 1A66985A1A9564C3
SQL escape character . . . . . : ' (apostrophe)
Decimal point character . . . : . (period)
Host program language . . . . : Remotely bound, trigger, or SQL package
Mixed character set . . . . . : N
Decimal 31 used . . . . . . . : No
1 Katakana . . . . . . . . . . . : No
Resource allocation information:
Resources are released . . . . : At COMMIT
Isolation level . . . . . . . : Cursor stability
Temporal special register information:
Sensitive to SYSTEM_TIME . . . : Yes
Sensitive to BUSINESS_TIME . . : Yes
Sensitive to GET_ARCHIVE . . . . : Yes
Bind options:
Access path reuse behavior . . : No - does not reuse paths
Package compat level behavior : V11R1
Static SQL DESCRIBE requests . : Yes - creates DESCRIBE SQLDA
SQL statements in package: TDKDB.TRDEL
SQL in statement: 2 (Stmt id: 4800449 )
UPDATE TDKDB.TRIGGER SET RECCOUNT = (SELECT COUNT (*) FROM
TDKDB.TRIGGER, (SELECT * FROM TDKDB.TRIGGER WHERE NAME LIKE '%A%' OR
NAME LIKE '%Z%') AS B WHERE A.ID = B.ID AND A.NAME = B.NAME) WHERE ID
LIKE '%124%'
_______________________________________________________________________________
==========
Object #3
==========
Details for partitioned accelerated table : TS5771.TQMAOTT1
Partition info . . : BY_GROWTH
Part no : 1
Logical no . . . . : 1
Limit key value . . :
Change information :
Category . . . . : NONE
Last load TS . . : 1970-01-01T00:00:00.000000Z
Type . . . . . . : AcceleratorOnly
Shared tablespace : false
_______________________________________________________________________________
==========
Object #4
==========
Details for non-partitioned accelerated table : TS5771A.TBAD0099
Change information :
Category . . . . : UNKNOWN
Last load TS . . : 2017-06-26T22:02:21.713980Z
Type . . . . . . : DataChange
Shared tablespace : true
______________________________________________________________________________
Long names legend
(*1) - "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TS5807"
=================================
End of Details for Objects Report
=================================