Complete this milestone if you are licensed for Code Debug’s Db2 Stored Procedure support.

If your site is not licensed for Db2 Stored Procedure support, go to Milestone 11: Configure BTS Support.

Roles Involved

The following people are required for this milestone:

Tasks

Complete the following tasks to configure Db2 Stored Procedure Support.

Task 10.1 Installation Considerations

A stored procedure can be invoked by a client application running on almost any platform, but some environments may define a time-out interval. Interactive testing of a stored procedure using Code Debug involves establishing breakpoints, stopping at them, and waiting for user input. This means a delay is introduced that might cause the time-out interval to be reached. If this happens, the client application will receive an error and the stored procedure test is likely to end abnormally.

The only solution to this problem is to increase the time-out values at your site. This process could involve numerous technical specialists for any time-out values set in Db2, in the LAN and its servers, in middleware such as Db2/Connect or IBM MQ, or in the client application itself.

Task 10.2 Specify PARMLIB Keywords for Db2 Stored Procedure Support

The following person is required for this task:

In the PARMLIB member for Code Debug TSO, specify:

for each PROCLIB that contains JCL used for your Db2 Stored Procedures.

Task 10.3 Grant Db2 Access to Code Debug TSO

The following person is required for this task:

For each Db2 subsystem which requires Code Debug TSO stored procedure testing, perform the following:

1. Bind that plan name XPvvrrmm where vvrrmm is the version, release, and modification level of Code Debug TSO(CTLSPLAN keyword not required). For example, Code Debug TSO 17.2 would be XP170200.
2. Grant execute on the plan name to either “public” or a list of authorization names allowed to use the Code Debug Db2 stored procedure support feature.

3. Use SLXTSAMP(XPSPJ610) as input to the bind, and either follow your site standard or use the online Bind Facility provided by Code Debug TSO.

   Important

   Code Debug users require read authority to the MVSADMIN.WLM.POLICY facility class to be able to retrieve stored procedure JCL from procedure libraries. If they are not granted read access, they must supply the stored procedure JCL manually.

Task 10.4 Create Workload Manager (WLM) Application Environments

The following person is required for this task:

A Code Debug TSO stored procedure test requires a set of Workload Manager Application Environments that must be defined for testing. Each concurrent Code Debug TSO stored procedure test requires one application environment.

The names of Code Debug WLM Application Environments consist of a four-character prefix and a four-digit number. The default prefix is XPAE. It can be overridden with PARMLIB keyword:

CTLAEPRE=aaaa(where aaaa is a 4-character alphabetic value).

The default number is 10 (indicating 0001 to 0010). It can be overridden with PARMLIB keyword:

CTLAENUM=nn(where nn is a 2-digit number from 01 to 10 inclusive).

Use the IBM Workload Manager (WLM) “Create an Application Environment” screen to create the Code Debug TSO Application Environments as follows:

1. For each Application Environment desired, specify in the Application Environment name field the Code Debug application name that you have selected. (The defaults are XPAE0001 through XPAE0010 and should match the prefix and number selected in the CTLAEPRE and CTLAENUM fields that were specified in the Code Debug TSO PARMLIB member.)
2. Create an additional Application Environment, XPAE0000, for the sample verification programs with the same attributes as the other Application Environments.
3. Specify Db2 in the Subsystem Type field.
4. Specify spaces (ensure they are blank) for the Procedure Name field and the Start Parameters field.
5. Type a 1, 2, or 3 in the field Limit on starting server address spaces for a subsystem instance, then press Enter. Multiple server address spaces are allowed.
6. Using the Install Utility in the WLM application, install the WLM service definition that contains information about these application environments into the WLM couple data set.
7. To activate the new Code Debug TSO application environments, activate the WLM policy from the installed service.

Task 10.5 Activate the Code Debug TSO Db2 Stored Procedure Intercept

The following person is required for this task:

1. Edit SLXTINST(JCLDB2SP) and use the XTUPDATE command to customize the JCL.

2. After the SYSIN DD *, supply the name of the Db2 subsystems as a list of names separated by at least one space, with continuations allowed.

   Important

   With Db2 group name support, a related group name, enclosed in parentheses, can be supplied following the Db2 subsystem name. For example, a group name of DB2G would be coded DB2T(DB2G), where DB2T is the subsystem’s real name, and DB2G is the name of the group it belongs to.

3. Submit the job. It should complete with return code 0.

Task 10.6 Configure z/OS to Start the Stored Procedure Intercept After an IPL

The following person is required for this task:

1. Copy SLXTINST(JCLDB2SP) to one of your installation PROCLIBs and use XTUPDATE to customize the JCL to run as a batch job.
2. Modify JCLDB2SP to meet your site’s standards for a z/OS startup procedure, replacing the JOB statement with a PROC statement.
3. Verify that the PARM statement’s OPTION parameter is set to ACTIVATE.
4. Configure your z/OS system to start JCLDB2SP at IPL time by either:
   • Updating your z/OS PARMLIB member COMMNDxx.
   • Configuring your automated operations application.

An example of the START command is provided in SLXTINST(COMMNDxx).

Task 10.7 Db2 Stored Procedure Installation Verification

As shown in the following table, the base Code Debug product includes four sets of IVP programs in the SLXTSAMP library: one for each programming language supported by Code Debug TSO and Code Debug IMS.

Db2 Stored Procedure IVP Programs

Task 10.7.1 Prepare Sample Programs

Use the applicable BMC Language Processor (LP) to compile or assemble the sample programs in the following order:

1. TRIRPT*
2. TRISPM*
3. TRISPT*

Some main routines statically link to subroutines higher in the list.

See to the BMC AMI Common Shared Services User/Reference Guide for information on using the correct LP (COBOL, PL/I, Assembler, or C).

The SYSLMOD output from these compiles should not be directed to the Code Debug TSO LOADLIB library, but to a data set available to your application programmers for training sessions. For example, you could create a data set named CPWR.XT.TRAINLIB.

To verify that the compile was successful and that a source listing member was created, check the listing under the DDNAME CWPERRM for a message similar to the following:

LISTING pgmname DATED nn/nn/nn AT nn.nn.nn SUCCESSFULLY WRITTEN TO CWPDDIOFor the TRISPM* programs, if the BIND step is not included in your JCL, bind the successfully compiled program either by following your site standard or by using the online Bind Facility provided by Code Debug TSO.

Task 10.7.2 Define Sample Stored Procedure to Db2 Subsystem

Define the Code Debug sample stored procedure TRISPT* (provided in SLXTSAMP member TRISPT*) to each Db2 subsystem in which you want to verify Code Debug’s Db2 stored procedure support. The SQL statements used to define the stored procedure can be found in the language-specific SLXTSAMP member listed in the following table.

SQL Statement Members

The stored procedure must be defined with

'STAY RESIDENT YES'

Task 10.7.3 Prepare to Run Test Session

To run the IVPs, you must:

1. Copy the sample WLM JCL (server) provided in the SLXTSAMP member TRIJCLST to a library of your choice, then customize it so that it conforms to your site’s standards.
2. Copy the sample JCL provided in the SLXTSAMP member TRIJCLSM to a library of your choice, then customize it so that it conforms to your site’s standards. (This job will call the Db2 program TRISPM* that will invoke the Stored Procedure.)

Before proceeding, be sure the following prerequisites have been completed:

• Workload Manager application environments have been defined.
• The test Db2 stored procedure has been defined.
• The sample programs have been compiled, linked, and bound.
• The Code Debug TSO Db2 Stored Procedure Intercept has been activated.

Task 10.7.4 Perform the Db2 Stored Procedure Verification Test Session

1. Invoke the Code Debug TSO Primary Menu.
2. Select 4 (STORED PROC) from the Code Debug TSO Primary Menu.
3. Enter SETUP from the Standard test screen.
4. Select 1 (LOADLIBS) from the Setup Menu.
5. On the Load Module Libraries screen, specify the application load library that contains the language-specific IVP load module. Also specify your Language Environment (LE) run-time library (usually CEE.SCEERUN).
6. Press Enter.
7. Select 6 (DSNLOAD) from the Setup Menu.
8. On the DSNLOAD Libraries screen, specify the Db2 Subsystem name and the associated SDSNLOAD Load Library name.
9. Press Enter.
10. Enter END or press PF3 until you return to the Process DB2 Stored Procedures screen.
11. Specify the SCHEMA. (The default Stored Procedure IVP definition used XT).
12. Specify the Stored Procedure name (TRISPT*).
13. Specify the UserID associated with the program that will invoke the Db2 Stored Procedure.
14. Specify Y on the User Supplied WLM JCL line.
15. Press Enter.
16. The Process Execute JCL screen should be displayed.
17. Specify the customized TRIJCLST data set/member on the Data set Name line, then press Enter.
18. Code Debug displays the Select Job Step screen.
19. Type the I line command in the field to the left of the step to be tested. (For the IVP, the Db2 WLM program is the first and only program in the JCL.)

20. Type SUB on the Command line and press Enter. This will cause Code Debug TSO to submit the batch job for the test. Once the submitted job has started in an initiator, the stored procedure is ready to be called, and Code Debug TSO will be ready to redirect clients/callers to that address space.

    Important

    The stored procedure’s batch address space must be started before starting the client application that will call it.

21. Type STATUS on the Command line of the Select Job Step screen, then press Enter. The batch job Status screen will be displayed.
22. The batch job that was just submitted must show Ready in the CONNECT column of the display. If not, press Enter to update the display.
23. When the job shows Ready, use split screen or another session to submit the customized TRIJCLSM data set/member.
24. Return to the STATUS screen and type A in the CMD column next to the Db2 Stored Procedure batch job.
25. Press Enter.
26. The program source will be displayed.
27. Type GO 1 on the Command line, then press Enter. The execution arrow will be on the next statement. The execution status message indicates that you are:
    BEFORE program-name:line-number
28. Press PF12 (GO). You will receive the message “TEST COMPLETED”.
29. Continue to press PF12 (GO) until you return to the STATUS screen.

Task 10.8 Supplemental Information

If you require additional RACF security for Code Debug TSO Db2 Stored Procedure support, you can find information on implementation in the Code Debug TSO and IMS Advanced Configuration Space.

If you’d like a better understanding of how Workload Manager (WLM), Db2, and the Code Debug TSO Db2 Stored Procedure Intercept function, you can find information in the Code Debug TSO and IMS Advanced Configuration Space.