Stored procedures and batch executionexecution

In this chapter we discuss the preparation steps necessary to run DB2 Data Archive Expert archive and retrieve specifications in batch. Modifications to sample REXX stored procedures are discussed as well as the configuration steps necessary in order to use DB2 utility templates are shown.

This chapter contains the following:

򐂰 Batch processing considerations

򐂰 Example batch jobs

5

5.1 Batch processing considerations

In 3.3, “Execute installation verification” on page 35 discussing the IVP, we introduced the batch invocation of an archive specification. We now discuss this topic in more detail showing examples of batch programs invoking the stored procedures provided by the tool.

5.1.1 Preliminary topics

DB2 Data Archive Expert provides a sample REXX program and corresponding execution JCL in the hlq.SAXHSAMP data set. The sample provided is coded to execute an existing table archive specification in batch. With the modifications we described in 3.3, “Execute installation verification” on page 35, you have a working example that allows you to archive from source table to table archive. Refer to Example 3-4 on page 35 for review.

You can write your own program to call the DB2 Data Archive Expert stored procedures in REXX, C, or COBOL. In our examples, we take the existing REXX sample and create a modified version for each type of archive and retrieval specification. We also show an example of how to run a file archive in batch, and specify the use of tape by the use of the DB2 Control Center 390 template tables, described earlier in “DB2 administration clients package” on page 24.

5.1.2 Data Archive Expert stored procedures

Table 5-1 summarizes Data Archive Expert’s stored procedures and their purpose.

Table 5-1 Stored procedure name and usage

The input parameter list for each of the supplied stored procedures is documented in the DB2 Data Archive Expert User’s Guide and Reference, SC18-7344. Using the sample REXX exec and associated JCL contained in hlq.SAHXSAMP members AHXIVPAR and AHXIVPRN, we have built working batch API examples for each type of stored procedure invocation.

5.2 Example batch jobs

We now discuss several examples of archive and retrieve specifications invoked through batch REXX procedures.

Stored Procedure Name Usage

AHXTOOLS.ARCHIVEEXECSP Executes Table Archive Specification AHXTOOLS.RETRIEVEEXECSP Executes Table Retrieve Specification AHXTOOLS.OFFLINEARCHSP Executes File Archive Specification AHXTOOLS.OFFLINERETEXECSP Executes File Retrieve Specification AHXTOOLS.OFFLINEONLARCSP Executes Table Archive to File Archive

Specification

Tip: In this section we modify several members of the target library SAHXSAMP. To keep from losing these modifications whenever maintenance is applied to DB2 Data Archive Expert, we recommend that you create a copy of the hlq.SAHXSAMP library, and modify the supplied REXX and JCL into your own version of SAHXSAMP.

5.2.1 Table archive specification

The sample REXX for this type of table archive is in member AHXIVPAR. We were able to use the REXX as coded in the samplib without any modification for the IVP; however, we found that we needed to code some additional modifications as described below. The parameter list for AHXTOOLS.ARCHIVEEXECSP is shown in Table 5-2.

Table 5-2 ARCHIVEEXECSP parameter list

Having created a table archive specification named batcharchivetest, we then modified the executing JCL provided in the AHXIVPRN member in SAHXSAMP as shown in Example 5-1.

Example 5-1 JCL to exec ARCHIVEEXECSP

//TSOCMD EXEC PGM=IKJEFT01,DYNAMNBR=20 //STEPLIB DD DSN=DB2G7.SDSNLOAD,DISP=SHR //SYSEXEC DD DISP=SHR,DSN=AHX110.SAHXSAMP.IVP //SYSTSPRT DD SYSOUT=*

//SYSTSIN DD *

%AHXIVPAR DB2G,A,*,batcharchivetest,SYSTOOLS,ADMRDEPT='E01' /*

//

Notice that we coded the archive specification name in lower case as DB2 Data Archive Expert allows the creation of mixed cased specification names.

In the DB2 Data Archive Expert User’s Guide and Reference, SC18-7344, the ROWFILTER parameter is described as overriding the previously defined row filter. It also states that if ROWFILTER is null, then DB2 Data Archive Expert applies the row filter used by the previous execution of the archive specification. During our experimentation, we discovered that when we coded the REXX parameter with a null, the stored procedure was interpreting that as a blank row filter, and when applying the archive specification, would materialize a result that was as if there were no row filter within the archive specification definition. We also decided to change the way that the sample REXX handled error codes (it always generated an MVS step return code of 0, even with an unsuccessful invocation of the stored procedure). We modified the sample REXX as follows:

1. We add the declaration shown in Example 5-2 in the front of the sample REXX program.

Positional parameter for procedure Parameter description

Parm 1 SSID of DB2 subsystem to connect to

Parm 2 Action type, A-run specification, C-complete

Parm 3 Version to complete if action is C, otherwise *

Parm 4 Specification name

Parm 5 Schema name of Data Archive Expert metadata

Parm 6 Row filter

Tip: If you elect to create mixed case specification names, remember to edit the executing JCL for the API stored procedure invocation with ISPF edit property CAPS OFF.

Example 5-2 REXX modification 1

2. We then tested for the length of the ROWFILTER parameter and made a different format of the call based on the parameter length. A length of zero indicates no ROWFILTER parameter, and we issued the stored procedure call with the nulled parameter. If we encountered a non-zero length, we issued a second format of the call, which passes the ROWFILTER parameter as coded. See Example 5-3.

3. Finally, we also modified the exit to include the variable return_code whenever we detect an error. This modification is placed after the DSNREXX environment is removed. See

We then run the specification in batch, and the execution generates the output shown in Example 5-5.

Example 5-5 Batch archive specification output

There is no sample REXX or JCL supplied for batch execution of file archive specifications.

We used the modified versions for the table archive REXX described above, and modified these according to the input parameter list defined in the DB2 Data Archive Expert User’s Guide and Reference, SC18-7344. The description of the parameters for