Printing zPARMs of DB2 using IBM-supplied stored procedure DSNWZP

There are times where it is needed to check the Zparm values of DB2. It required to verify the change(before and after) you have made or just to check the existing values.

If you have any tool, then you can easily verify it. One more place is there to look at these values is D710.SDSNSAMP(DSNTIJUZ). This job actually sets the parameter values. you can get the latest info if the member is used in Edit mode while submitting this job last time. Otherwise you need to configure a IBM supplied Stored procedure "DSNWZP" for this.

You can find this in SDSNSAMP:

The new job DSNTEJ6Z prepare and invoke the sample program DSN8ED7 which is a sample caller of the stored procedure DSNWZP, a DB2 provided stored procedure that returns the current settings of your DB2 subsystem parameters.

DSN8ED7 formats the results from DSNWZP in a report format and prints it.

Please note that before running DSN8ED7 the stored procedure DSNWZP must exist on your DB2 subsystem. Installation job DSNTIJSG creates and binds the DB2 provided stored procedure DSNWZP.

Calling DSNWZP strored procedure from ReXX.
===================================
Here is the sample code I got from a very good post at IDUG. After execution of this rexx it will open a target dataset to show the zPARM values automatically in a formatted manner.
/****************************** REXX ********************************/
/** */
/** EXEC : DISPZPRM */
/*I PURPOSE : REXX TO CALL THE DSNWZP STORED PROCEDURE AND PARSE */
/*I THE OUTPUT FIELD INTO A READABLE FILE. THROW YOU */
/*I INTO EDIT OF THE OUPUT DATASET SO YOU CAN FIND / SORT */
/*I OR DO WHATEVER YOU WANT. */
/** */
/**********************************************************************/
ARG SSID
IF SSID = '' THEN SSID = 'DSN2'
PROCNAME = 'DSNWZP '
STATUS = MSG('OFF')
ADDRESS TSO
"FREE DDNAME(RPTOUT)"
CALL LISTDSI ( ZPARM.TXT DIRECTORY NORECALL )
IF SYSREASON = 9 THEN "HDELETE ZPARM.TXT WAIT "
ELSE "DELETE ZPARM.TXT "
"ALLOC FI(RPTOUT) DA(ZPARM.TXT)
NEW LRECL(500) SP(5 2) CYL RECFM(F B) DSORG(PS) "
"FREE DDNAME(RPTOUT)"
ADDRESS TSO "SUBCOM DSNREXX"
IF RC = 1
THEN S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT " SSID
IF SQLCODE <> 0
THEN CALL SQLERROR

/* SET UP SQLDA FOR CALL TO DSNWZP */

INSQLDA.SQLD = 1
INSQLDA.1.SQLTYPE = 449 /* VARCHAR */
INSQLDA.1.SQLLEN = 32000
INSQLDA.1.SQLIND = 0
INSQLDA.1.SQLDATA = ' '

/* CALL PROCEDURE DSNWZP */

ADDRESS DSNREXX
"EXECSQL CALL :PROCNAME USING DESCRIPTOR :INSQLDA "

IF SQLCODE = 0 THEN DO

ADDRESS ISPEXEC
"LMINIT DATAID(DATAIDX) DATASET(ZPARM.TXT) ENQ(MOD) "
"LMOPEN DATAID("DATAIDX") OPTION(OUTPUT) "

RESULT = INSQLDA.1.SQLDATA
PARMCOUNT = 1
I = POS(X2C(404025),RESULT)
DO WHILE ( I <> 0)
RESULT = SUBSTR(RESULT,4)
NI = POS(X2C(404025),RESULT)
IF NI = 0 THEN NI = LENGTH(RESULT) + 1
ZPARM.PARMCOUNT = SUBSTR(RESULT,1,NI-1)
ZPARM.0 = PARMCOUNT
RESULT = SUBSTR(RESULT,NI)
I = POS(X2C(404025),RESULT)
END

DO I = 1 TO ZPARM.0
RECVAL = ZPARM.I
CALL PRCS_LINE
ADDRESS ISPEXEC
"LMPUT DATAID("DATAIDX") MODE(INVAR)
DATALOC(RECVAL) DATALEN(500) "
END
ADDRESS ISPEXEC
"LMCLOSE DATAID("DATAIDX")"
"EDIT DATAID("DATAIDX")"
"LMFREE DATAID("DATAIDX")"
ADDRESS TSO
"DELETE ZPARM.TXT "
END
ELSE DO
SAY 'UNEXPECTED SQLCODE ON CALL TO DSNWZP '
SAY 'SQL CODE = ' SQLCODE
END
RETURN 0

/* TAKE THE VALUE AND SPACE IT OUT AND REARRANGE IT FOR DISPLAY */
/* IF YOU DO NOT LIKE THE ORDER SIMPLY CHANGE THE LAST LINE */
PRCS_LINE:
ORECVAL = RECVAL
L = POS('/',RECVAL)
W1 = SUBSTR(LEFT(RECVAL,L-1),1,9)
WR = SUBSTR(RECVAL,L+1)
L = POS('/',WR)
W2 = SUBSTR(LEFT(WR,L-1),1,9)
WR = SUBSTR(WR,L+1)
L = POS('/',WR)
W3 = SUBSTR(LEFT(WR,L-1),1,25)
WR = SUBSTR(WR,L+1)
L = POS('/',WR)
W4 = SUBSTR(LEFT(WR,L-1),1,9)
WR = SUBSTR(WR,L+1)
L = POS('/',WR)
W5 = SUBSTR(LEFT(WR,L-1),1,9)
WR = SUBSTR(WR,L+1)
L = POS('/',WR)
W6 = SUBSTR(LEFT(WR,L-1),1,40)
WR = SUBSTR(WR,L+1,32)
RECVAL = W3||WR||W6||W4||W5||W2||W1
RETURN
/*-------------------------------------------------------------------*/
SQLERROR: PROCEDURE EXPOSE SQLCODE SQLERRP SQLERRMC SQLERRD. SQLWARN. ,
SQLSTATE
/*-------------------------------------------------------------------*/
SAY "SQLCODE = " SQLCODE
SAY "SQLSTATE = " SQLSTATE
SAY "SQLERRP = " SQLERRP
SAY "SQLERRMC = " SQLERRMC
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 12
RETURN
/*-------------------------------------------------------------------*/


Cheers...
Prakash C. Singh
IBM Certified DB2 DBA