Database extract function (Batch stream)

When you want to produce a File-AID for IMS extract, it is recommended that you use the JCL that is automatically generated when you execute the online conversation invoked through Option 4.1, Database Extract. If customized JCL is required, the best approach is to begin by using Option 4.1 to generate JCL that is as close to what is required as possible. Save a copy of this JCL by following the instructions found in Editing the Generated JCL. You can then make changes to the generated JCL.

The following section describes required parameter information and DD statements required to execute a File-AID for IMS extract.

The batch job consists of the following steps:

1. SRTGEN1 - Allocate and initialize temporary dataset to-dataset-name.CK.
2. SRTGEN2 - Allocate and initialize temporary dataset to-dataset-name.XK.
3. SRTGEN3 - Allocate and initialize temporary dataset to-dataset-name.RK.
4. SRTKEY - (Optional) sort key file used to drive extract.

5. DBEXTRCT - Data Base Extract (XIXDTFDV)

   Retrieves segments from the extract FROM database, applies selection criteria (if specified), applies application relationships (if specified), and writes the segment data, along with File-AID for IMS/ISPF prefix information, to the extract TO file.

6. DELETE - Deletes temporary data sets allocated in steps 1-3.

7. RPTSTATS - Data Base Extract Summary Reporting (XIXRPTDV)

   Writes a report that summarizes the results of the extract.

8. EXFMT - Reformat Extract file for Disguise.
9. XFRMAIN - Disguise the Extract file.

The data sets allocated and initialized in the first three steps of the extract job are used by XIXDTFDV as it processes logically related databases and application relationships. Database keys required to resolve these relationships are written to the file associated with the SORTIN02 DD statement. Database keys that have already been extracted are written to the SORTIN01 DD statement. XIXDTFDV calls DFSORT to determine which of the keys written to the SORTIN02 DD statement have not been processed. The result is written to the data set associated with the SORTOUT DD statement. XIXDTFDV continues processing unresolved logical and application relationships until either the data set associated with the SORTOUT DD statement is empty, or the chase limit set by the Number of Chase Cycles to Process parameter has been reached.

DFSORT is called using the parameters shown in the following figure. The VLSHRT option is required to allow DFSORT to process records in a variable blocked data set that are shorter than the sort field. The DYNALLOC option will dynamically allocate an optimal number of SORTWRK data sets. root.key.lgth is the length of the root key of the driving database for the extract (the DBD specified as the “FROM” data base. You may chose to remove this option, however, an appropriate number of SORTWRK data sets must be allocated in the DBEXTRCT job step. All remaining sort parameters should not be modified, otherwise the DBEXTRCT step will not function properly.

DFSORT Parameters

Note that the LRECL parameters referenced in the JCL are computed by XIXDTFDV. The calculation is equal to the length of the longest concatenated key of all segments written to the extract dataset plus 11 bytes.

If a file containing database keys is to be used to drive a database extract, it should be sorted in ascending key sequence. JCL generated to by File-AID for IMSto execute an extract will include the step labeled SRTKEYS if a key input file is to be used. This step is not necessary if the file containing the keys is already in sort sequence. If this is the case, delete the SRTKEYS step from the generated JCL. If an unsorted key file is used to drive the extract, the file should contain no duplicates. If duplicate keys are present and the file is not in sort sequence, duplicate records will be extracted. This will cause errors when loading the extracted data into a database.

The parameter information in the PARM field on the EXEC statement for the DBEXTRCT step must have the following format:

EXEC Statement PARM Field - DBEXTRCT Step

In the example below, a BMP extract job is run against the DBD PORDR in a BMP environment and an IMS System of TEST. Existing selection criteria and application relationships are applied.

The following DD statements are required for the DBEXTRCT step:

DBEXTRCT DD Statements

The parameter information in the PARM field on the EXEC statement for the RPTSTATS step must have the following format:

EXEC Statement PARM Field - RPTSTATS Step

The Maximum Print Lines Per Page and Spacing Before Detailed Headers parameters are common report parameters that are explained more fully in Print parameters.

Data format values of D and M are valid only for Double-Byte Character Set (DBCS) support.

The Terminal Type is used to determine the correct character set for the terminal and should be the same as that defined in ISPF Option 0.1 (ISPF Terminal Characteristics).

The following DD statements are required for the RPTSTATS step:

RPTSTATS DD Statements

An extract execution return code (RC) appears in the extract output listing. For return code 16 an error code and message that indicate the specific problem also appears in the listing. In general, the return codes can be summarized as follows:

There are two additional steps: Extract Format, and Data Privacy. The first, Extract Format, allows you to indicate whether to retain or keep the result of the extract process, when you have opted to disguise the extracted data. You can identify the Disguise control file to use, and indicate whether to create an audit file of the disguise processing.

The following DD statements are required for the Extract Format step:

Extract Format

The next step is Data Privacy. The following DD statements are required for the Data Disguise step:

Data Disguise

The File-AID/MVS Copy step copies the XREFs that are to be converted to a temporary data set that is used in the Convert step to convert the copied XREFs to a File-AID/MVS XREF. See to the File-AID Batch Reference manual for more information about the File-AID/MVS Copy and Convert.

Extract File Contents

The TO file produced by the batch extract job is in File-AID for IMS’s unload format. The file consists of one header record, a DBD record if application relationships were applied, and data records for each segment extracted. The following figure shows an example of the contents of an extract file in hexadecimal format.

Extract File Contents - Hexadecimal Format

The header record, which is the first record in the file, contains the product code and version, a one-byte load indicator, the prefix length, and the maximum root key length for the FROM database. This information is used during the Data Base Load function.

The prefix length is the length of the File-AID for IMS control information that is at the front of each data record in the file. The database segment data begins immediately after the prefix in each extract record. The maximum root key length is the length of the longest key for the root segments of all databases participating in the extract. For further information on the contents and layout of this file, see Advanced Topics.

If application relationships are applied, the second record is the DBD record. The DBD record contains the DBD names of application related databases.

Extract File Contents - Hexadecimal Format with Encryption

When Data Disguise is requested, the data that is extracted from the databases is written to an extract file. The content of this extract file is the same as described above with the following exceptions:

1. The header record contains an "E" in position 60 indicating that the extract file is encrypted.
2. The data portion for each extract record is encrypted. The prefix portion of each record is not encrypted.

This encrypted extract file is then passed to the Extract Format process to reformat the extracted data for the disguise process and then deleted. You may request that this file be retained during the Extract/Load dialogue request. This encrypted extract file may not be used to load or reload any databases.

Extract File Contents - Hexadecimal Format with Disguise

The content of this disguised extract file is the same as described above for an extract file with the following exceptions:

1. The header record contains an "D" position 60 indicating that the extract file is disguised.
2. The data portion for each extract record is disguised. The prefix portion of each record is not disguised.

This disguised extract may be used to load or reload databases.