Effect of parameters for the detector datastore unload

Products

• Subsystem Analyzer for DB2 for z/OS
• Database Management for DB2 for z/OS - Performance Suite
• Database Management for DB2 for z/OS - SQL Performance Suite
• Detector for DB2 for z/OS
• Database Management for DB2 for z/OS - Administration Suite
• Database Management for DB2 for z/OS - Recovery Suite
• Database Management for DB2 for z/OS - Utilities Suite

Issue/Introduction

We changed the detector unload parameters and experienced a seven fold increase in output for the OBJECT table and three fold for the STANDARD table.

Before: (for each of five members)

SSID=XXXX              
DATASTORE=DATASTOR   
VCAT=HGHLVL
UNLOAD=Y             
HOSTVARS=Y           
REPORT=PROG           
REPORT=PLAN           
REPORT=SQL           
REPORT=SQLE           
FORMAT=DETAIL         
REPORT=EXCP           

After: (Covers 5 members)

 DSGROUP=YYYYY                                                       
 DATASTORE=DATASTOR                                                 
 VCAT=HGHLVL                                              
 DATATYPE=ALL                                                       
 UNLOAD=Y              Y/N - WRITE UNLOAD RECORDS TO SYSREC DD       
 UNLDOBJ=Y             Y/N - WRITE TABLE AND INDEX UNLOAD RECORDS   
 UNLDKEYS=Y            Y/N - INCLUDE ANY ADDITIONAL THREAD KEYS     
 HOSTVARS=Y            Y/N - WRITE HOST VARIABLE UNLOAD RECORDS     
 UNLDTEXT=Y            Y/N - WRITE SQL TEXT UNLOAD RECORDS FOR       
 AGGREGATE=N           Y/N - REPORT EACH INTERVAL OR AGGREGATE ALL   
 SQLQUAL=N             Y/N - WRITE SQLQUAL CONTROL CARDS TO PPAEXPL. 

We realize UNLDKEYS=Y would increase volume but it is not yet enabled in the profile.

The biggest change for table OBJECT  was that PREPARE when from 925,794 out of 2,148,161 to 14,041,803 out of 15,059,552. Without PREPARE there was actually a slight decrease in rows but that is likely normal day to day variation.

It is our hope to capture USERID detail but want to manage data size. Is there a way to get set up to collect key information without this huge increase in data size?

 

Environment

Release : 20.0

Component : Detector for DB2 for z/OS

Resolution

Sent user the link explaining the different parms of PDTBATCH for more details see Report, Unload, and EXPLAIN Parameters