Bookmark and Share
RSS

Recent Posts

New and Changed Subsystem Parameters in DB2 11

June 20, 2017

Even though DB2 12 is now available, a fair number of customers are now or have recently migrated from DB2 10 to DB2 11. I know this because I've been getting queries about subsystem parameters (zparms) default changes in DB2 11. So I'll share with you what I've been telling them: IBM, thankfully, documents this information in the Knowledge Center's What’s New guide:

What follows are lengthy lists of new and changed zparms. I'd like to say these are all the most important zparms, but every shop has its own priorities. Make sure you review the default values being used and confirm that these are good settings for your environment. The default settings can be found in the IBM DB2 11 for z/OS Installation and Migration Guide.

For the new zparms, I've included my own comments
  • APPLCOMPAT--I recommend keeping the default on migration V10R1 and REBINDing all existing PLANS and PACKAGES. Then change default to V11R1 for new applications being developed. You can override the default with the BIND & REBIND parameter.
  • AUTHEXIT_CACHEREFRESH and AUTHEXIT_CHECK--These two new zparms, when set to their non-default values, address what may have been the final two nagging issues with regard to having RACF manage DB2 internal security. (Namely, that a DB2-related authorization change made in RACF would not cause DB2 to refresh in-memory security information caches accordingly, and RACF would only check the ID of a process for bind or rebind authority, as opposed to checking the package owner ID for that authority.)
  • CDDS_MODE
  • CDDS_PREFIX
  • DISALLOW_SEL_INTO_UNION--This will become standard in DB2 12, so make sure you check this now and clean up your applications before migrating.
  • INDEX CLEANUP THREADS--Just be aware that you have ten new threads running to cleanup index. Large shops with thousands of indexes to clean may want to consider increasing this number to keep up with DELETE processing.
  • LIKE_BLANK_INSIGNIFICANT--This becomes important to applications that are sensitive to trailing blanks on compare operations, such as Java.
  • MAXSORT_IN_MEMORY--Increasing the value of this parameter can enable more DB2 SQL-related sort work to be accomplished in-memory, thereby improving CPU efficiency.
  • OBJECT_CREATE_FORMAT--Once you start converting existing objects from basic to extended, you should set this parameter to EXTENDED as the default for any new objects being created.
  • PARAMDEG_DPSI--If you're using data partitioning secondary indexes and seeing long elapse times, consider setting this parameter to 2 initially to see if performance improves.
  • PARAMDEG_UTIL--The default value is 99. Monitor your utilities (such as Online REORG) to ensure you have the memory and space available to support this number of parallel subtasks.
  • PCTFREE_UPD--AUTO could be a good setting for table spaces that are growing significantly due to row length-increasing UPDATEs. (This can be determined by checking the value of UPDATESIZE in a table space's row in the SYSIBM.SYSTABLESPACESTATS real-time statistics table in the catalog. This would include tables stored in compressed table spaces as well as variable-length columns being updated.) Set this to AUTO for every tablespace that contains variable length fields, or when using compressed table spaces. This way you'll avoid having to run REORG for rows that can no longer fit on the same page after an update.
  • PKGREL_COMMIT
  • PREVENT_ALTERTB_LIMITKEY
  • REMOTE_COPY_SW_ACCEL
  • REORG_DROP_PBG_PARTS
  • REORG_MAPPING_DATABASE
  • STATFDBK_SCOPE
  • TEMPLATE_TIME
  • UTILITY_OBJECT_CONVERSION
  • WFSTGUSE_AGENT_THRESHOLD
  • WFSTGUSE_SYSTEM_THRESHOLD

Now for the changed zparms. Details included here were gleaned from the IBM Knowledge Center:
  • DSMAX--The upper limit for the maximum number of data sets that can be open at one time is changed from 100,000 to 200,000.
  • EDMDBDC--The upper limit for the minimum size (in KB) of the DBD cache that is to be used by EDM is changed from 2,097,152 to 4,194,304.
  • EDM_SKELETON_POOL--The upper limit of the minimum size of the EDM skeleton pool (in KB) is changed from 2,097,152 to 4,194,304.
  • EDMSTMTC--The upper limit for the size (in KB) of the statement cache that is to be used by the EDM is changed from 1,048,576 to 4,194,304.
  • MAXKEEPD--The upper limit for the total number of prepared, dynamic SQL statements that can be saved past a commit point by all threads in the system using the KEEPDYNAMIC(YES) bind option is changed is changed from 65,535 to 204,800.
  • REORG_PART_SORT_NPSI--The default value is changed from NO to AUTO.
  • SUBQ_MIDX--The default value is changed from DISABLE to ENABLE.
Finally, here's a list of zparms that were removed DB2 11:
  • ASSIST
  • COORDNTR
  • DISABSCL
  • DISALLOW_DEFAULT_COLLID
  • MSVGP and MSVGP2
  • OJPERFEH
  • OPTIOPIN
  • OTPIOWGT
  • OPTIXIO
  • PGRNGSCR
  • PTCDIO
  • RETVLCFK
  • SEQCACH
  • SEQPRES
  • SMSDCFL
  • SMSDCIX
  • STATCLUS

Posted June 20, 2017 | Permalink

comments powered by Disqus