DB2 – Tips and Tidbits for the DB2 for z/OS Systems Programmer

1. DB2 – Tips and Tidbits for the DB2 for z/OS Systems Programmer Rebecca Keller US Software Services

2. Tips and Tidbits for DB2 z/OS What is a Systems Programmers Job Functions? • Installation • Maintenance • System Administration • Problem Determination and Resolution • Local and Disaster Recovery • Performance and Tuning • Support DBA & Application Development Staff • Miscellaneous Tidbits

3. Installation

4. Tips and Tidbits for DB2 z/OS Installation • Create naming conventions before you start • Use Names that indicate Test vs. Production • SSIDIRLM for IRLM Proc Name • Use *Meaningful* WLM APPLENV names • Use System Scope IEFSSN entries unless planning for Data Sharing - Install on all LPARS • Do not put DSNLOAD in the LINKLIST • Use CRC that includes the SSID, not just a single character • Use aliases for software

5. Tips and Tidbits for DB2 z/OS Installation • Use symbolic aliases for Data Sharing • Use SMS management for datasets • Check PDS/e and GSO options for LINKLIST datasets • Plan for Data Sharing carefully. You cannot permanently remove a member from a group, only QUIESCE the member • Create an HFS file system for each subsystem • Mount HFS file systems without the version identifier in it: /usr/lpp/db2/ssid • Auto-mount HFS file systems

6. Tips and Tidbits for DB2 z/OS Installation • Use SCHENV for job execution • Do not use SMP/e libraries for execution libraries • Use granular DB2 authorities – Do not hand out SYSADM • Set the Correct CCSID at installation time • Create at least one Work File Table space (V8) with secondary space

7. Maintenance

8. Tips and Tidbits for DB2 z/OS Maintenance • IBM recommends maintenance quarterly. Is that realistic? • At least receive/apply the maintenance in SMP/e regularly (monthly) • Read Cover letters – check for INCORROUT and Corruption and see if the scenario applies • Apply ERLY code more frequently, then you are at least prepared for any upcoming maintenance • Read the hold data – can have interesting information and alerts you of the actions required • Some interesting tidbits of recent maintenance: • End of LOG RBA • Diagnosis Guide sent in electronic form • AO types which are generally changes in message output that can cause automation to break • Hidden explain tables

9. System Administration

10. Tips and Tidbits for DB2 z/OS System Administration • Active Logs – size them to fit on a single tape, if using real tape - still a good recommendation • Dual active and archive logging • Specify the same active log sizes for all LOGCOPIES • Spread them on different DASD devices and controllers or use PAV/striping • Use DASD TMM/HSM solution if very high volume logging • Archive log cancel offload command – first thing to try, but it does not always work

11. Tips and Tidbits for DB2 z/OS System Administration • ARCHIVE LOG SCOPE(GROUP) for Data Sharing • Errors offloading archive logs or BSDS? Duplicate the other copy • Things to Automate: • Restart DB2, only three times so no recursive restarts • Restart Light for Data Sharing for a failed LPAR • DSNJ110E (Last Log) messages. Not held highlighted by default

12. Tips and Tidbits for DB2 z/OS System Administration • Things to Monitor: • DSNI013I – SSID DSNIDIFS Potentially Inconsistent • SYSCOPY for successful completion of image copies • SYSCOPY for no LOAD/REORG LOG YES • BSDS for LOG status, make sure there are none with permanent errors • Growth on user managed table spaces, approaching the 2GB limit • Space on private volumes, so that extents can be taken • INDOUBT threads, may need to reset whencoordinator is cold started (e.g. DataPropagator Tasks) • Growth in secondary space on DSNDB07

13. Tips and Tidbits for DB2 z/OS System Administration • DSNZPARM Settings: • CHKFREQ: 2-3, but log size matters • URLGWTH: Use to catch threads that commit infrequently • URCHKTH: No more than 24 hours, for URID that may become in-doubt • TCPKEEPALIVE: To match your client thread clean up routines • IDTHTOIN: Match to your TCPKEEPALIVE • OPTHINTS: ON, just in case you need to use hints in V8 • TSTAMP: Definitely timestamp logs, can get a much quicker idea of rate of logging over time • CATALOG: YES for archive logs

14. Tips and Tidbits for DB2 z/OS System Administration • DSNZPARM Settings: • ACCUMACC: NO gets better reporting for DDF threads • LOGAPSTG: 100MB, or else only good on DB2 restart • MAX_NUM_CUR: 99999 • MAX_ST_PROC: 99999 • SQLINTRP: Disable to Avoid -950 SQLCODES • UNION_COLNAME_7: YES, restores V7 behavior • Use Set SYSPARM, table in Install guide for the items that can be set dynamically • Watch for DSNZPARM additions via APAR, like OPTIX* performance “enhancements”

15. Problem Determination and Resolution

16. Tips and Tidbits for DB2 z/OS Problem Determination and Resolution • Have a tool kit ready • Standard jobs: TERSE, FTP, DSN1LOGP, DSN1COPY, DSN1PRNT, DSNJU004, EREP • Less used utilities: DSN1SDMP, DB2PLI8, DIAGNOSE • Verify your dump dataset MAXSPACE, 2GB is a good start • Verify your dumps are copied off SYS1.DUMPxx datasets • Verify IEADMC* and IEADMP* members are set up with the correct parameters to get the storage areas IBM will want to see

17. Tips and Tidbits for DB2 z/OS Problem Determination and Resolution • Check the SYSLOG, sometimes pertinent messages are there and not in the JESLOG of the DB2 address space • Beware that COMMDUMPS will slow down a system as the dump is being taken, can cause MVS paging from DUMPSRV • Get a dump report created regularly from all SVC dumps. Sometimes issues are occurring and no one is reporting/noticing them • Use IPCS, sometimes helpful to do the front end debugging • Use GTF for traces instead of SMF to avoid having to send a lot more data than necessary • IBMLink and search strings: http://www-01.ibm.com/support/docview.wss?rs=64&uid=swg21210011

18. Tips and Tidbits for DB2 z/OS Problem Determination and Resolution • Know how to disable dump analysis tools • Try the “native” IBM way, if using a 3rd party tool, (especially utilities) to eliminate that potential point of failure • Verify that SMF and LOGREC are split off daily

19. Tips and Tidbits for DB2 z/OS Problem Determination and Resolution • Handy MVS and DB2 commands: • D O Operator Information • D U,,,NNNN,1 Device Status • D R,L Outstanding Replies • D A,L Show active Tasks • D PROG,APF APF (and LNKLST) entries • D WLM,APPLENV=* WLM Environment Status • DUMP Force a dump to be taken • SETPROG Update APF and LNKLST • SETSSI Add a Subsystem • SLIP SET SLIP Traps • F IRLMPROC, Many options Modify or “Talk” to the IRLM • D XCF,….. Cross System Coupling Facility • CANCEL THREAD NOBACKOUT DB2 cancel thread command • DIS DB(*) SP(*) RESTRICT DB2 display of restricted objects

20. Local and Disaster Recovery

21. Tips and Tidbits for DB2 z/OS Local and Disaster Recovery • You can initialize SYSUTILX, but might need to start force • Use LISTDEF for backups and recoveries • Beware DB2 managed user indexes on catalog and directory • BSDS will auto-recover on V8 • BSDS is reformatted for V9 in print log map, the DDF section is above the active log report • Recover postponed cancel in V9

22. Tips and Tidbits for DB2 z/OS Local and Disaster Recovery • Catalog datasets for image copies • Must be “Install SYSADM” to recover SYSUSERAUTH • DSNR031I -DB03 DSNRTIMR PROCESSING LOG RECORD AT RBA will show status of log processing while restarting DB2 • Practice, Practice, Practice, Try things on your sandbox

23. Performance and Tuning

24. Tips and Tidbits for DB2 z/OS Performance and Tuning • Request performance baseline before version upgrades • Look for what has changed (Trust but Verify) • Beware of skewed distribution statistics for non-unique indexes • Use RLIMIT and predictive governing • Use UNLOAD utility instead of DSNTIAUL when practical – but benchmark the difference • Use CACHEDYN and MAXKEEPD for dynamic SQL

25. Support DBA and Application Staff

26. Tips and Tidbits for DB2 z/OS Support DBA and Application Development Staff • Recommendations for DBA and Developers: • Use “Online” utilities (SHRLEVEL Change) • Use LISTDEF and Templates • Use log NO for utilities and Inline Copies • Select Count(*) before deleting/updating rows in SPUFI or DSNTEP2, if more than 1000, write a program • Beware “over-indexing”, particularly with non-unique indexes • Beware of using new features immediately • Set VPSEQT=0 if you want to disable sequential pre-fetch • Run better RUNSTATS, and perhaps not as often • Use real time statistics and the interface with DFSORT for utilities • Bind with explain and use an index on the PLAN_TABLE • Use the new enhanced explain output features (DSNTESC job) • Use packages and versioning in V8, free old versions

27. Miscellaneous Tidbits

28. Tips and Tidbits for DB2 z/OS Miscellaneous Tidbits • DSNZ016I -D2T8 DSNZINIT in V9, shows from where exit modules were loaded • SQLDA is larger in V8 than hex ‘FF’, can cause assembly errors • New DISPLAY DB(*) restricted status in V8 and V9 • Sliding scale and z/OS extent consolidation • DSNY014I DB2 started in maintenance mode – must have “Install SYSADM or SYSOPR” authority to access • Pre-compiler issues with some characters on z/OS, use CCSID(1047) on the pre-compile to work around the problem

29. Tips and Tidbits for DB2 z/OS Miscellaneous Tidbits • -805 SQLCODE thrown from SPUFI in V8 CM with Get diagnostics • You can now reassign “Install SYSADM” with Set SYSPARM • “Local” Date and Time Exits, you need for ASCII and Unicode versions in NFM • &SPRMCTU SETC ‘1‘ in DSN6SPRC, to be able to update the catalog using SPUFI • Watch your DDCONS and DETAIL settings in SMFPRM* members

30. Tips and Tidbits for DB2 z/OS Miscellaneous Tidbits • ICOPY status on DSNDB01 objects DSNT360I -D2T8 *********************************** DSNT361I -D2T8 * DISPLAY DATABASE SUMMARY * GLOBAL DSNT360I -D2T8 *********************************** DSNT362I -D2T8 DATABASE = DSNDB01 STATUS = RW DBD LENGTH = 14200 DSNT397I -D2T8 NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE -------- ---- ----- ----------------- -------- -------- -------- ----- DBD01 TS RW SPT01 TS RW SCT02 TS RW SYSUTILX TS RW SYSLGRNX TS RW DSNSCT02 IX RW DSNSPT01 IX RW,ICOPY DSNSPT02 IX RW,ICOPY DSNLUX01 IX RW,ICOPY DSNLUX02 IX RW,ICOPY DSNLLX01 IX RW,ICOPY DSNLLX02 IX RW,ICOPY ******* DISPLAY OF DATABASE DSNDB01 ENDED ********************** DSN9022I -D2T8 DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION

31. * IBM®, DB2®, z/OS®, DataPropagator – are trademarks of the IBM Corporation EDS, an HP company 5400 Legacy Drive Plano, TX 75024 Rebecca.Keller@eds.com EDS and the EDS logo are registered trademarks of Hewlett-Packard Development Company, LP. HP is an equal opportunity employer and values the diversity of its people. ©2008 Hewlett-Packard Development Company, LP.