DB2 and Delimited File Input Source Utilization In ACR

In this article we will identify different uses of DB2 input sources used with our ACR application.

External translation tables allow you to match data from input sources where the data is in different styles, for example, IL and Illinois. You can also use an external table to obtain additional data that is not available in the input source, for example, a department name, where the input source contains only the department number. ACR/Summary and ACR/Detail allow the creation of an external translation table for the following dataset formats: Physical Sequential, VSAM Key Sequenced, User Program Accessed File, Constant Translation Data, and Web Page.

Common issues or problems that require assistance is the information that needs to be extracted requires translation, and the data resides in a DB2 database.

As a solution to remedy the problems faced you could, run an extract against the DB2 database and use the sequential result as the translation table. (To run an extract, see your DB2 Administrator.)

A secondary remedy suggested would be in ACR/Detail or ACR/Summary, write a user program to read the DB2 data and use the User Program option to read and translate the data. For information about user programs, see the ACR User Program Guide.

A third option is to run an ACR/Detail job that extracts the DB2 data through an input source, and writes the data to a User Report or Freeform Output file (UNIDAT2), and then use the User Report or UNIDAT2 file as the translation table in a subsequent step. See the ACR/Detail User Guide for more information about User Reports or UNIDAT2 files.

In the diagram above there are two DB2 tables requiring extraction. 

A company uses two DB2 tables to store human resource information. One
table contains employee information and the second table contains project
information. The tables have at least one column of data in common. A user
wants to create a single report that displays columns from both tables.
From the first table, the report will include the following:

LASTNAME
FIRSTNME
HIREDATE
SALARY

From the second table, the report will include the following columns:

PROJNAME                                                                                                                                                PRSTDATE
PROJNO

The first table has a column, EMPNO, which contains the employee
number. The second table has a column, RESPEMP, which contains the
employee number for the person responsible for a particular project. This
information is the key that ties the two tables together. 

To extract the data needed from both tables, join the tables.
On the File Organization dialog box, select the Use Freeform SQL
Statements check box.

1. Set up a SELECT statement to show the columns to extract.
SELECT LASTNAME, FIRSTNAME, HIREDATE, SALARY, PROJNAME,
PRSTDATE, PROJNO

2. For z/OS, complete the following task. For Windows, complete this
task after your set up the WHERE statement in step 4.
Assign these fields to keys or internal items. For ACR/Detail, list the keys before the internal items.

3. Set up a FROM statement to show both tables.
FROM EMPLOYEE, PROJECT

4. Set up a WHERE statement to show the correlation of data in the two
tables:
WHERE RESPEMP = EMPNO

Selecting Data Records from a Delimited File

Infogix ACR user programs, such as Delimited Field Access (UUPDLIM),
revise the way an input source is processed. UUPDLIM extracts each
delimited field as its own record, making it possible to use a static position
and length to extract data from a delimited file.

Say you are faced with a problem within your organization. You need to extract data from a delimited file, such as a trade file that contains data delimited with commas. You do not want to process the
header records in the file. Or you may want to begin the data extraction with a particular record.

As a solution you would use the Delimited Field Access user program (UUPDLIM) to extract data
from delimited files. See the ACR User Program Guide. Specify the Sequence ID parameter (/s) to put the sequence numbers on the record (0000000100000001, 0000000100000002, and so on). The first eight
characters of the sequence represent the record number; the second eight characters represent the field number on the record.

Complete the following steps to extract only the required information:
1. To begin processing the data records, but not the header record, in a file with one header record, set up the following SELECT condition:

SELECT if position 1 for a length of 8 is not equal to 00000001
This parameter will not extract the data that appears in the header
record. Adjust the statement to accommodate a file with multiple header
records or to skip some records and begin processing with a record
that appears later in the file.

2. Select the fields you want to extract, based on their field number.
SELECT if position 1 for a length of 8 is not equal to 00000001
SELECT if position 9 for a length of 8 is not equal to 00000004

The example above selects field 4, which is not in the header record.