ADMIN_INFO_SYSPARM stored procedure
The SYSPROC.ADMIN_INFO_SYSPARM stored procedure returns the system parameters, application defaults module, and IRLM parameters of a connected Db2 subsystem, or member of its data sharing group.
Environment
ADMIN_INFO_SYSPARM runs in a WLM-established stored procedures address space, where NUMTCB=1 is required.
Authorization
- The EXECUTE privilege on the package for DSNADMIZ
- Ownership of the package
- PACKADM authority for the package collection
- SYSADM authority
Syntax
The following syntax diagram shows the SQL CALL statement for invoking this stored procedure:
Option descriptions
- db2-member
- Specifies the name of the Db2 data sharing group member that you want to get
the system parameters, DSNHDECP or a user-specified application defaults module, and IRLM parameters
from.
Specify NULL for this parameter if you are retrieving the system parameters, DSNHDECP values, and IRLM parameters from the connected Db2 subsystem.
This is an input parameter of type VARCHAR(8).
- return-code
- Provides the return code from the stored procedure. The following values are possible:
- 0
- The call completed successfully.
- 12
- The call did not complete successfully. The message output parameter contains messages that describe the IFI error or SQL error that is encountered by the stored procedure.
This is an output parameter of type INTEGER.
- message
- Contains messages that describe the IFI error or SQL error that was encountered by the stored
procedure. If an error did not occur, a message is not returned.
This is an output parameter of type VARCHAR(1331).
Example
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/******************** DB2 SQL Communication Area ********************/
EXEC SQL INCLUDE SQLCA;
int main( int argc, char *argv[] ) /* Argument count and list */
{
/****************** DB2 Host Variables ****************************/
EXEC SQL BEGIN DECLARE SECTION;
/* SYSPROC.ADMIN_INFO_SYSPARM parameters */
char db2_member[9]; /* Data sharing group member */
short int ind_db2_member; /* Indicator variable */
long int retcd; /* Return code */
short int ind_retcd; /* Indicator variable */
char errmsg[1332]; /* Error message */
short int ind_errmsg; /* Indicator variable */
/* Result set locators */
volatile SQL TYPE IS RESULT_SET_LOCATOR VARYING *rs_loc1;
/* Result set row */
long int rownum; /* Sequence number of the */
/* table row (1,...,n) */
char macro[9]; /* Macro that contains the */
/* system parameter, or */
/* DSNHDECP parameter, or the */
/* name of the IRLM procedure */
/* that z/OS invokes if IRLM */
/* is automatically started */
/* by DB2 */
char parameter[41]; /* Name of the system */
/* parameter, DSNHDECP */
/* parameter, or IRLM */
/* parameter */
char install_panel[9]; /* Name of the installation */
/* panel where the parameter */
/* value can be changed when */
/* installing or migrating DB2*/
short int ind_install_panel; /* Indicator variable */
char install_field[41]; /* Name of the parameter on */
/* the installation panel */
short int ind_install_field; /* Indicator variable */
char install_location[13]; /* Location of the parameter */
/* on the installation panel */
short int ind_install_location; /* Indicator variable */
char value[2049]; /* Value of the parameter */
char additional_info[201]; /* Reserved for future use */
short int ind_additional_info; /* Indicator variable */
EXEC SQL END DECLARE SECTION;
/******************************************************************/
/* Set the db2_member indicator variable to -1 to get the DB2 */
/* subsystem parameters, DSNHDECP values, and IRLM parameters of */
/* the connected DB2 subsystem. */
/******************************************************************/
ind_db2_member = -1;
/******************************************************************/
/* Call stored procedure SYSPROC.ADMIN_INFO_SYSPARM */
/******************************************************************/
EXEC SQL CALL SYSPROC.ADMIN_INFO_SYSPARM
(:db2_member :ind_db2_member,
:retcd :ind_retcd,
:errmsg :ind_errmsg);
/******************************************************************/
/* Retrieve result set when the SQLCODE from the call is +446, */
/* which indicates that result sets were returned */
/******************************************************************/
if (SQLCODE == +466) /* Result sets were returned */
{
/* Establish a link between the result set and its locator */
EXEC SQL ASSOCIATE LOCATORS (:*rs_loc1)
WITH PROCEDURE SYSPROC.ADMIN_INFO_SYSPARM;
/* Associate a cursor with the result set */
EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :*rs_loc1;
/* Perform fetches using C1 to retrieve all rows from the */
/* result set */
EXEC SQL FETCH C1
INTO :rownum, :macro, :parameter,
:install_panel :ind_install_panel,
:install_field :ind_install_field,
:install_location :ind_install_location,
:value,
:additional_info :ind_additional_info;
while(SQLCODE==0)
{
EXEC SQL FETCH C1
INTO :rownum, :macro, :parameter,
:install_panel :ind_install_panel,
:install_field :ind_install_field,
:install_location :ind_install_location,
:value,
:additional_info :ind_additional_info;
}
EXEC SQL CLOSE C1;
}
return(retcd);
}
Output
This stored procedure returns the following output parameters, which are described in Option descriptions:
- return-code
- message
In addition to the preceding output, the stored procedure returns one result set that contains the parameter settings.
The following table shows the format of the result set that is returned in the created global temporary table SYSIBM.DB2_SYSPARM:
Column name | Data type | Contents |
---|---|---|
ROWNUM |
INTEGER
NOT NULL |
Sequence number of the table row, from 1 to n. |
MACRO |
VARCHAR(8)
NOT NULL |
Macro that contains the system parameter, the dsnhdecp parameter, or the name of the IRLM procedure that z/OS® invokes if IRLM is started automatically by Db2. |
PARAMETER |
VARCHAR(40)
NOT NULL |
Name of the system parameter, dsnhdecp parameter, or IRLM parameter. |
INSTALL_PANEL | VARCHAR(8) | Name of the installation panel where the parameter value can be changed when installing or migrating Db2. |
INSTALL_FIELD | VARCHAR(40) | Name of the parameter on the installation panel. |
INSTALL_LOCATION | VARCHAR(12) | Location of the parameter on the installation panel. |
VALUE |
VARCHAR(2048)
NOT NULL |
The value of the parameter.1 |
ADDITIONAL_INFO | VARCHAR(200) | Specifies whether a parameter can be updated online. If the value is null, the stored procedure could not retrieve the information for the parameter. |
|

Parameter values that are returned in bit format
For certain subsystem parameters, the content of the VALUE column in the returned result set is a bit string.
For subsystem parameters AUDITST, MON, and SMFACCT, the 32 bits of the string correspond to the 32 trace classes. The bit string has the following values:
- If the subsystem parameter value is NO, all bits are 0.
- If the subsystem parameter value is YES, the first bit is 1, and all other bits are 0.
- If the subsystem parameter value is *, all bits are 1.
- Otherwise, the bits that are 1 correspond to the trace classes that are on. For example, if the AUDITST subsystem parameter specification is AUDITST=(1,3,5), the first, third and fifth bits of the bit string are 1, and all other bits are 0:
10101000000000000000000000000000
For subsystem parameters ROUTCDE and ARCWRTC, the 16 bits of the string correspond to the 16 route codes. The bit string has the following values:
- The bits that are 1 correspond to the route codes that are on. For example, if the ROUTCDE subsystem parameter specification is ROUTCDE=(1,3), the first and third bits of the bit string are 1, and all other bits are 0:
1010000000000000
For subsystem parameter SMFSTAT, the 32 bits of the string correspond to the 32 trace classes. The bit string has the following values:
- If the subsystem parameter value is NO, all bits are 0.
- If the subsystem parameter value is YES, the first, third, fourth, fifth, and sixth bits are 1, and all other bits are 0.
- If the subsystem parameter value is *, all bits are 1.
- Otherwise, the bits that are 1 correspond to the trace classes that are on. For example, if the SMFSTAT subsystem parameter specification is SMFSTAT=(1,3,5), the first, third and fifth bits of the bit string are 1, and all other bits are 0:
10101000000000000000000000000000
For subsystem parameter TRACSTR, the 32 bits of the string correspond to the 32 trace classes. The bit string has the following values:
- If the subsystem parameter value is NO, all bits are 0.
- If the subsystem parameter value is YES, the first, second, and third bits are 1, and all other bits are 0.
- If the subsystem parameter value is *, all bits are 1.
- Otherwise, the bits that are 1 correspond to the trace classes that are on. For example, if the TRACSTR subsystem parameter specification is TRACSTR=(1,3,5), the first, third and fifth bits of the bit string are 1, and all other bits are 0:
10101000000000000000000000000000
