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
To execute the CALL statement, the owner of the package or
plan that contains the CALL statement must have one or more of the following privileges on each
package that the stored procedure uses:
- 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:
>>-CALL--SYSPROC.ADMIN_INFO_SYSPARM--(--+-db2-member-+-,--------> '-NULL-------' >--return-code,--message--)------------------------------------><
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
The following C language sample shows how to invoke
ADMIN_INFO_SYSPARM:
#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 *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. |
| 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. |
