db2 tips and tidbits for the db2 for z os systems programmer l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
DB2 – Tips and Tidbits for the DB2 for z/OS Systems Programmer PowerPoint Presentation
Download Presentation
DB2 – Tips and Tidbits for the DB2 for z/OS Systems Programmer

Loading in 2 Seconds...

play fullscreen
1 / 31

DB2 – Tips and Tidbits for the DB2 for z/OS Systems Programmer - PowerPoint PPT Presentation


  • 1955 Views
  • Uploaded on

DB2 – Tips and Tidbits for the DB2 for z/OS Systems Programmer Rebecca Keller US Software Services 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'DB2 – Tips and Tidbits for the DB2 for z/OS Systems Programmer' - Roberta


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
db2 tips and tidbits for the db2 for z os systems programmer

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

Rebecca Keller US Software Services

tips and tidbits for db2 z os
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
tips and tidbits for db2 z os4
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
tips and tidbits for db2 z os5
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
tips and tidbits for db2 z os6
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
tips and tidbits for db2 z os8
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
tips and tidbits for db2 z os10
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
tips and tidbits for db2 z os11
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
tips and tidbits for db2 z os12
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
tips and tidbits for db2 z os13
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
tips and tidbits for db2 z os14
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”
tips and tidbits for db2 z os16
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
tips and tidbits for db2 z os17
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

tips and tidbits for db2 z os18
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
tips and tidbits for db2 z os19
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
tips and tidbits for db2 z os21
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
tips and tidbits for db2 z os22
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
tips and tidbits for db2 z os24
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
tips and tidbits for db2 z os26
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
tips and tidbits for db2 z os28
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
tips and tidbits for db2 z os29
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
tips and tidbits for db2 z os30
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

slide31

* 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.