Consistency of zParms in Db2 Data Sharing Groups
WRITTEN BY: EMIL KOTRC, BROADCOM
I really never paid too much attention to the consistency of zParms in a data sharing group until recently. If you plan to prepare for Db2 13 migration and you are about to activate the very last function level of Db2 12 - V12R1M510, you need to make sure that all your packages that are still in use were last rebound with Db2 11 at least. The activation process of this function level runs a query, which relies on the LASTUSED column of SYSPACKAGE catalog table. More on that in this blog.
However, the LASTUSED column might not provide accurate information in case when there is no data collected. The collection is driven by a zParm DISABLE_EDMRTS, by default it is NO and the statistics are collected, but you may have set it to YES for some reasons. Now back to the original point: what if you have a different setting of this zParm on different members? Nothing super terrible happens, but the information might not be accurate and in the worst case, the activation process succeeds even if there are old packages in use by a member where the LASTUSED statistics is not collected. You might experience some autobind issues during the migration, but other than that, all should be good.
That is for this particular zParm. However, there are other zParms that you should treat with care in a data sharing environment. There are actually four categories of zParms in respect of a data sharing group:
- zParms that must be different on each member
- zParms that must be the same on every member
- zParms with other recommendations
- The rest of the zParms
This is all very well documented in the IBM manuals, but let's take a brief look into these individual categories.
Note. Please note that despite the fact I mainly speak about the zParms, it is not exclusively about zParms only. There are other parameters involved such as DECP, IRLM, and others. Also, if you want to check these parameters fully you may need to check other sources than zParms, such as BSDS for port numbers, DECP for application defaults, etc.
1. Parameters that must be different
The list of such parameters comprises mainly of the parameters required for logging and subsystem identification. As we know each member in the data sharing group has its own logging environment (active and archive logs and the bootstrap datasets). The documentation lists the following parameters:
Active Logs: COPY 1 PREFIX
Active Logs: COPY 2 PREFIX
Archive Logs: COPY 1 PREFIX (ARCPFX1 parameter)
Archive Logs: COPY 2 PREFIX (ARCPFX2 parameter)
Bootstrap Data Sets : COPY 1 NAME
Bootstrap Data Sets : COPY 2 NAME
COMMAND PREFIX
Db2 NETWORK LUNAME
MEMBER IDENTIFIER
MEMBER NAME
PARAMETER MODULE
PROC NAME
RESYNC PORT
SUBSYSTEM NAME
SUBSYSTEM SEQUENCE
SUBSYSTEM NAME (IRLM)
WORK FILE DB2. Parameters that must be the same
This is a much longer list:
APPL COMPAT LEVEL
AUTH EXIT CACHE REFR
AUTH EXIT CHECK
CATALOG ALIAS
DEL CF STRUCTS
DBADM CREATE AUTH
Db2 LOCATION NAME
DESCRIBE FOR STATIC
DIRECTORY AND CATALOG DATA
DIRECTORY AND CATALOG INDEXES
DRDA PORT
EVALUATE UNCOMMITTED
EXTENDED SECURITY
GROUP ATTACH
GROUP NAME
INDEX SPACE ALLOCATION
IRLM XCF GROUP NAME
INSTALL DD CONTROL SUPT
LIKE_BLANK_INSIGNIFICANT in macro DSN6SPRM
MAX UTILS PARALLELISM
MINIMUM DIVIDE SCALE
PAD INDEXES BY DEFAULT
PERCENT FREE FOR UPDATE
REAL TIME STATS
REVOKE DEP PRIV
SECURE PORT
SITE TYPE
SKIP UNCOMM INSERTS
STATISTICS FEEDBACK
STATISTICS HISTORY
STATISTICS ROLLUP
SYSTEM ADMIN 1
SYSTEM ADMIN 2
SYSTEM OPERATOR 1
SYSTEM OPERATOR 2
SECURITY ADMIN 1
SEC ADMIN 1 TYPE
SECURITY ADMIN 2
SEC ADMIN 2 TYPE
SEPARATE SECURITY
TABLE SPACE ALLOCATION
TEMPLATE TIME
TCP/IP ALREADY VERIFIED
TRACKER SITE
UNICODE IFCIDS
VARY DS CONTROL INTERVALI really haven't checked what happens if you try to run a data sharing group with some of these parms with different values, not sure if Db2 actually does some checking for some of them or you would experience unexpected behavior. Please share your experience in the comments.
3. Parameters with other recommendations
Here is the list of zParms that are provided with some recommendations whether they should be the same or not used
DEALLOC PERIOD
DEFAULT 4-KB BUFFER POOL FOR USER DATA
DEFAULT 8-KB BUFFER POOL FOR USER DATA
DEFAULT 16-KB BUFFER POOL FOR USER DATA
DEFAULT 32-KB BUFFER POOL FOR USER DATA
DEFAULT BUFFER POOL FOR USER LOB DATA
DEFAULT BUFFER POOL FOR USER XML DATA
DEFAULT BUFFER POOL FOR USER INDEXES
DEVICE TYPE 1
DEVICE TYPE 2
DISABLE EDM RTS
READ COPY2 ARCHIVE
READ TAPE UNITS
RETAINED LOCK TIMEOUT
START IRLM CTRACEAs you can see the DISABLE EDM RTS is listed here and IBM recommends to have it the same on all members. This is the zParm I mentioned at the beginning and you should have it the same on all members for consistency.
4. All the remaining zParms
In general the values of most parameters do not need to be unique with the exceptions listed above. However, I believe it is still good to at least review the values of all zParms across members, which leads us to the next section.
How to check consistency of zParms
OK, so now we learned that some parameters must be unique and some must be the same. How to actually check the consistency and values of zParms across all members in the data sharing group? Well, there are some tools that can help you with that (some of them with no additional cost - let me know if you are interested), but you can also do it on your own pretty easily. You can check your DSNTIJUZ jobs or you can for instance display your existing zParms.
In one of my prior blogs I have shown a simple REXX script that externalizes the zParms, DECP, IRLM and some other parms via the ADMIN_INFO_SYSPARM stored procedure. If you run this on all your members, save the outputs, and compare, that could do the work. For instance, I was just running SUPERC to compare the zParms using:
//STEP3 EXEC PGM=ASMFSUPC,REGION=4M,
// PARM='LINECMP DELTAL LONGLN NOPRTCC'
//STEPLIB DD DSN=ASMA.SASMMOD2,DISP=SHR
//NEWDD DD DISP=SHR,DSN=zparms1
//OLDDD DD DISP=SHR,DSN=zparms2
//OUTDD DD SYSOUT=*
//SYSIN DD *
DPLINE 'ARCPFX'
DPLINE 'IRLM'
DPLINE 'SSID'
DPLINE 'DSNZPARM'
DPLINE 'MEMBNAME'
However, this does not scale well if your group has more than two or three members, because you need to run a pairwise compare or use some advanced compare tools.
So I rather took a different approach that would work with any group:
- I created a table ZPARMST with just three columns: PARM, VALUE, SSID
CREATE TABLE ZPARMST(PARM VARCHAR(50), VALUE VARCHAR(50), SSID CHAR(4));
- Updated my REXX script to insert the values into this table and ran it on every member. See the appendix for the full REXX. Keep in mind that you need to make sure to execute it on every member! Otherwise some data will be missing.
- I did the analysis using the following query:
SELECT * FROM ZPARMST WHERE (PARM, VALUE) IN (
SELECT PARM, VALUE FROM ZPARMST
WHERE
PARM NOT IN ('MEMBNAME', 'DSNZPARM', 'ARCPFX1', 'ARCPFX2',
'IRLMPRC', 'IRLMSID')
GROUP BY PARM, VALUE
HAVING COUNT(*) < (SELECT COUNT(DISTINCT SSID) FROM ZPARMST)
)
ORDER BY PARM, SSID, VALUE; The SQL will return only the zParms that have a different value at least on one member and it will list the values of such parameters on all the members. Some zParms that must be unique by nature are excluded.
In my case, the group has three members and I have seen the following results:
+----------------------------------------------------------------------------------------------------------------+
| PARM | VALUE | SSID |
+----------------------------------------------------------------------------------------------------------------+
1_| ACCEL | NO | DSN1 |
2_| ACCEL | AUTO | DSN2 |
3_| ACCEL | COMMAND | DSN3 |
4_| ACCELMODEL | NO | DSN1 |
5_| ACCELMODEL | YES | DSN2 |
6_| ACCELMODEL | YES | DSN3 |
7_| EDMDBDC | 0000015000 | DSN1 |
8_| EDMDBDC | 0000020000 | DSN2 |
9_| EDMDBDC | 0000015000 | DSN3 |
10_| EDMSTMTC | 0000030000 | DSN1 |
11_| EDMSTMTC | 0000050000 | DSN2 |
12_| EDMSTMTC | 0000030000 | DSN3 |
13_| PRIQTY | 0000006000 | DSN1 |
14_| PRIQTY | 0000001000 | DSN2 |
15_| PRIQTY | 0000001000 | DSN3 |
16_| QUERY_ACCELERATION | NONE | DSN1 |
17_| QUERY_ACCELERATION | NONE | DSN2 |
18_| QUERY_ACCELERATION | ELIGIBLE | DSN3 |
19_| UTILS_USE_ZSORT | NO | DSN1 |
20_| UTILS_USE_ZSORT | YES | DSN2 |
21_| UTILS_USE_ZSORT | NO | DSN3 |
+----------------------------------------------------------------------------------------------------------------+
None of the zParms listed above is recommended to be the same nor must be the same, but I think it is good to see the differences on different members. The rest of the zParms that are not reported are either the same on all members or ignored by the query.
What do you use to check your zParms across the data sharing group? Please share in the comments if you have any good tricks or ideas.
Appendix. REXX to INSERT zParms into a table
//REXXGEN EXEC PGM=IEBGENER
//SYSIN DD DUMMY
//SYSPRINT DD SYSOUT=*
//SYSUT2 DD DSN=&&TEMPPDS(ZPARMS),
// DISP=(,PASS),
// UNIT=SYSDA,SPACE=(TRK,(1,1,1),RLSE),
// DCB=(RECFM=FB,LRECL=80)
//SYSUT1 DD *
/* REXX */
parse arg ssid
address tso "SUBCOM DSNREXX"
if rc then do
rc = rxsubcom('ADD','DSNREXX','DSNREXX')
if rc then exit
end
address dsnrexx
connect ssid
if sqlcode \= 0 then call sqlerror
proc = 'SYSPROC.ADMIN_INFO_SYSPARM' /* procedure to execute */
empty = ''
emptI = -1
msg = left(' ', 1331, ' ') /* output message */
msg_ind = '' /* indicator not null */
/* call the procedure */
execsql "call :proc (:empty :emptI, :rc, :msg :msg_ind)"
/* stored procedure returns +466: number of result sets */
if sqlcode < 0 then call sqlerror
/* to get the output from the call,
we need to associate the locator */
execsql "DESCRIBE PROCEDURE",
" :proc INTO :sqlda"
if sqlcode \= 0 then call sqlerror
execsql "ASSOCIATE LOCATOR (:result) ",
"WITH PROCEDURE :proc"
if sqlcode \= 0 then call sqlerror
/* allocate cursor for the result set */
execsql "ALLOCATE C101 CURSOR FOR RESULT SET :result"
if sqlcode \= 0 then call sqlerror
cursor = 'C101'
execsql "DESCRIBE CURSOR :cursor INTO :sqlda"
if sqlcode \= 0 then call sqlerror
do until(sqlcode \= 0)
execsql "FETCH C101 INTO " ,
":rownum, :macro, :parameter, :install_panel" ,
", :install_field, :install_location, :valux, :additional_info"
if sqlcode = 0 then do
say left(rownum,3) left(macro,8) left(install_panel,8) ,
left(install_field,24) ,
left(install_location,3) ,
left(additional_info,8) ,
left(parameter,24) ,
valux
insert = "INSERT INTO ZPARMST VALUES(" ,
"'" || parameter || "'," ,
"'" || valux || "'," ,
"'" || ssid || "')"
execsql insert
if sqlcode \= 0 then call sqlerror
end
end
execsql "CLOSE CURSOR :CURSOR"
execsql "COMMIT"
address dsnrexx "DISCONNECT"
rc = rxsubcom('DELETE','DSNREXX','DSNREXX')
exit
sqlerror:
say 'SQLCODE ='sqlcode
say 'SQLSTATE='sqlstate
say 'SQLERRMC='sqlerrmc
say 'SQLERRP ='sqlerrp
say 'SQLERRD ='sqlerrd.1','sqlerrd.2','sqlerrd.3,
||','sqlerrd.4','sqlerrd.5','sqlerrd.6
say 'SQLWARN ='sqlwarn.0','sqlwarn.1','sqlwarn.2,
||','sqlwarn.3','sqlwarn.4','sqlwarn.5,
||','sqlwarn.6','sqlwarn.7','sqlwarn.8,
||','sqlwarn.9','sqlwarn.10
exit 8
/*
//*
//RUNREXX EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//STEPLIB DD DISP=SHR,DSN=prefix.SDSNEXIT
// DD DISP=SHR,DSN=prefix.SDSNLOAD
//SYSEXEC DD DSN=&&TEMPPDS,DISP=(OLD,DELETE)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
%ZPARMS dsn1
/* Author: Emil Kotrč, emil.kotrc@broadcom.com
Created: 2023-01-12 Thu 09:24