In this article I’ll provide solutions for problems related to data extraction, specifying sections of an input source, specifying records to extract, determining whether to reconcile data, and extracting large volumes of data using an external translation table.
I. Specifying Sections of an Input Source:
In ACR/Detail, access mode 6, Keys Precede Detail Values, evaluates every record in the input source by selection group, starting with the first group.
Here is a problem scenario:
You have a large file that contains several concatenated reports. Each concatenated report has the word Report in positions 1 through 6. You need to extract data from a specific report, GL-135, but the selection criteria, CREDIT, exists on other reports, as well. As you add the values for the CREDIT selection criteria, you want to include only the CREDIT values from the relevant report. In other words, you want to skip the other reports and process only the relevant report.
Your resolution is as follows:
Complete the steps below to set up selection criteria to show where to begin referencing the data.
1. Set up a literal in an extraction variable and set the value to Y when you reach the beginning of report GL-135. For the same extraction variable, set the literal to N to indicate the end of report GL-135. This step ensures that only the desired report is processed.
SELECT if Pos 1 through 6 = "REPORT"
SELECT if Pos 8 through 6 = GL-135
Move literal "Y" to MyReportExtractionVariable
SELECT if Pos 8 through 6 NOT = GL-135
Move literal "N" to MyReportExtractionVariable
2. Add a SELECT statement for all criteria that pertain to the data on your report to make sure that you have the right report.
SELECT if MyReportExtractionVariable = "Y" and SELECT if Pos 5
through 10 = "CREDIT"
3. Specify a detail field to extract the values from the correct report.
III. Specifying Records to Extract:
When you specify a driver file, the system determines what to extract from other files based on the keys in the driver file. However, sometimes the values that you want to extract are not associated with a key field. For example, you have one file that contains only data that you need to reconcile. You have another file with data that you need to reconcile plus additional data that you do not want to extract at this time. The field value that determines whether to include the data is not a value that you want to use as a key for this job, so you cannot use the driver file option.
Here is a problem scenario:
You want to extract only the values that match the values from another input source. For example, you have a corporate report that contains information for every branch within the company. You need to extract only the information that is pertinent to your department’s branches but you do not want to sort the data by branch number, so you are not including the branch number in the key.
Your resolution is as follows:
Complete the tasks below to specify the records to extract. You will need to set up an external transaction table and define the input source.
Set Up a Translation Table
1. Set up one file as an external translation table.
2. Create a column in the external translation table for the value you want to match, for example, the branch number, and for any other values that you want to extract.
3. Assign a literal value of YES to an extraction variable when the columns match, and assign the values that you want to extract to other extraction variables.
Set Up an Input Source
1. Set up the other file as the input source.
2. Assign a literal value of NO to an extraction variable named BRANCH FOUND.
3. Set up a detail field extraction that will extract the item from the input source, translate the item, and place the item in an extraction variable named MY FIELD.
4. Set up a SELECT statement to check the extraction variable BRANCH FOUND, and create subsequent keys and detail fields to move the values to internal items if BRANCH FOUND = YES.
When you extract data from the input source, the system translates one value at a time, checking the BRANCH FOUND extraction variable to see if the branch is found in both files.
If the branch is found in both files, the flag is set to YES and other data that you specified in external translation table columns is extracted into the extraction variables that you set up. See step 3 under "Set Up a Translation Table." These extraction variables are then available in the input source to place into keys or internal items or for use in subsequent select statements and detail fields.
If the branch is not found, the flag value remains NO and the selection criteria fails. See step 4, above. Processing continues without this item, skipping those records for which the branch number does not match between the two files.
III. Determining Whether to Reconcile Data:
In ACR/Detail, you can specify that the first file processed is the driver file. The system determines whether the keys from the driver file are present in the second file. If the keys from the first file are also present in the second file, the system processes only those keys. Keys in the second file that are not in the driver file are ignored.
Here is a problem scenario:
You need to reconcile two input sources using a 24-byte key, but only for accounts that reside in both input sources. The account number is in the first eight bytes of the key. Because you are matching only a portion of the key, the system cannot use the driver file feature to select the appropriate keys from the second file.
Your resolution is as follows:
Set up two numeric internal items to indicate which files contain the values. When you extract the value from the file, set the item that indicates the original input source for this value to +1.
Next, create reconciliation rules to look for non-zero values for both sources. If both values are non-zero, the account is present in both sources. The extracted values are passed via filter to the next level, which applies the true business rules that use the required 24-byte key.
Complete the steps below to reconcile the two input sources with a 24-byte key.
1. Create a base job that extracts the full 24-byte key but reconcile the job as a base level job with an 8-byte key.
2. Set the filter for a qualifier to ON, so that only the items that you note as out-of-balance are passed to a qualifier. See the “Setting Up Multi-Level Reconciliation” section in the ACR/Detail User Guide.
3. For the first input source, define an internal item that contains a tally to indicate whether the value is found in the first input source.
4. For the second input source, define an additional internal item with a separate tally to indicate whether the value is found in the second input source.
5. Create three reconciliation rules:
A. Create a standard rule to check the first input source internal item for a value greater than zero.
B. Create another standard rule to check the second input source internal item for a value greater than zero.
C. Create a conditional rule to check that both of the standard rules are true. If true, set a return code to flag which keys go to the RLQ.
6. Set up a qualifier job. This job has the same job/step name as the base job but has a value other than spaces in the qualifier field.
7. For the qualifier job, go into the reconciliation level and specify that the job uses the data filter from the base job, which you set up in step 1, with the return code range appropriate for the value that you set up in step 5C.
You can now reconcile with a 24-byte key because your qualifier job has only the data with matching accounts from both files, and all 24 bytes are available.
IV. Extracting Large Volumes of Data Using an External Translation Table:
Regular external translation table processing can take a long time and use a lot of memory if the translation table has a high volume of records. The @ONEROW feature can greatly reduce processing time.
Here is a problem scenario:
In ACR/Detail, you need to extract transactions from daily reports that contain up to a million accounts in random order. Each transaction has a 16-digit account number, but this number is not the key. You want data for only those accounts that are present in the ACR/Detail history file. The number of accounts that reside in the file can be as many as 10,000 accounts.
Your resolution is as follows:
Use the ACR/Detail history VSAM file as an external translation table and use the @ONEROW feature to speed processing. Follow the steps below to set up the history file as an external translation table with @ONEROW processing.
1. Under the Tables option, choose to set up an external translation table.
2. Enter @ONEROW for the description.
3. Choose VSAM Key Sequenced for the Dataset Organization.
4. Refer to the online help to enter the following information:
File ID
File Name
Record Layout
5. For the Build Table Data Option, select On each lookup.
6. Follow the instructions in the online help or the ACR/Detail User Guide to specify the input and output parameters, define the table columns, specify the lookup rules, define the table build rules, and then create the job that will call the external translation table.
7. Select Reconcile to run the job.
Comments
0 comments
Please sign in to leave a comment.