Additional steps for enabling the common SQL API stored procedures

You can use the common SQL API stored procedures to retrieve information about the data server configuration, the short message text for an SQL code, and system information. These stored procedures are used primarily by tools.

Before you begin

Before you complete these steps, either install the Db2-supplied routines during migration or install the Db2-supplied routines during installation. To create the GET_CONFIG stored procedure with the correct security attributes, job DSNTIJRT must be run by a user ID with SYSOPR and MONITOR1 privileges.

Job DSNTIJRT installs and configures these stored procedures, but you must complete additional steps before you use them.

If GET_SYSTEM_INFO will be called to return the ICF catalog information about Db2 data sets, the user who defines the stored procedure must have MONITOR1 and DISPLAY privileges. If GET_SYSTEM_INFO will be called to return catalog attributes or ICF catalog information about Db2 data sets, the user who defines the stored procedure must have READ access to the OPERCMDS resource MVS.MCSOPER.DSNADMGS. If MVS™.MCSOPER.DSNADMGS does not exist, the user must have READ access to the OPERCMDS resource MVS.MCSOPER.*. Otherwise, catalog attributes information will not be returned from GET_SYSTEM_INFO. To give the user that defines the procedures access to the RACF® OPERCMDS class, the RACF security administrator must:

If the OPERCMDS class is not active, issue the following SETROPTS command to activate the OPERCMDS class:
```
SETROPTS CLASSACT(OPERCMDS)
```
If the generic profile for OPERCMDS is not active, issue the following SETROPTS command to activate it:
```
SETROPTS GENERIC(OPERCMDS)
```
Issue the RDEFINE command to establish a profile for MVS.MCSOPER.console-name. In this example, console-name=DSNADMGS:
```
RDEFINE OPERCMDS MVS.MCSOPER.DSNADMGS UACC(NONE)
```
Give the user ID access to the class. In this example, the user ID is USER001:
```
PERMIT MVS.MCSOPER.DSNADMGS CLASS(OPERCMDS) ID(USER001) ACCESS(READ)
```
Issue the SETROPTS RACLIST command to refresh the OPERCMDS reserve class:
```
SETROPTS RACLIST(OPERCMDS) REFRESH
```

Before you complete the additional steps to enable the common SQL API stored procedures, set up WLM application environments for them to run in. These stored procedures run in WLM-established stored procedures address spaces.

The common SQL API stored procedures are:

SYSPROC.GET_CONFIG
SYSPROC.GET_MESSAGE
SYSPROC.GET_SYSTEM_INFO
SYSPROC.SET_PLAN_HINT

GET_SYSTEM_INFO must run in a separate WLM environment from DSNUTILS or DSNUTILU.

Procedure

To enable the common SQL API stored procedures:

In the JCL for starting the WLM-established address space for running the GET_SYSTEM_INFO stored procedure, ensure that all libraries in the STEPLIB DD concatenation are APF-authorized.