Input Key File Specification panel
The following panel is displayed when you select Option 4 - Input Key File on the Extract Menu.
You can specify an Input Key File for either a Db2 or MVS driving object.
Input Key File Specification
Command ===>
Driving Object: LOCATION.FRSAMP.PART_TABLE
Specify Input Key File Information:
Extract Driven by Input Key File ===> N ( Yes or No )
File Name ===>
Member ===>
Specify Delimited File Information:
Delimited ===> NO ( Yes or No )
Quotation Marks ===> 1 (1 = Single, 2 = Double)
Column Delimiter ===> ,
Quotes on Character Data ===> YES (Yes or No)
Quotes on Numeric Data ===> NO (Yes or No)
If you are not using an Input Key File as input to the selection criteria for the driving object, simply enter No (default) for the Extract Driven by Input Key File field and press END to return to the Extract Menu.
If, however, you intend to use an Input Key File as input criteria for the driving object, you must enter Yes for the Extract Driven by Input Key File field, enter the key file’s file name and specify the key file’s format (standard Db2 or delimited). Then press Enter to continue.
The Output Key files produced from the KEY relationships (see also Output Key Files) can be used as Input (External) Key files within other File-AID/RDX extracts.
This functionality provides solutions for the following:
Split the Extract
Some large extracts can be broken up an into multiple jobs so that independent legs of the related objects can be extracted concurrently. The use of a KEY relationship provides a technique to accomplish this.
One extract request can be defined to extract the higher level related objects and produce Output Key files for each independent leg of the related set. Once that Extract has been run the Output Key files can then be used an Input Keys files for other extracts.
You must ensure that the relationships in the subsequent extracts are really independent and do not require additional rows to be extracted from objects in the original extract. Also, since the Driving object for the subsequent extracts would be a dependent object from the first extract, the DPSI switches (see Extract Navigation Criteria) for the parent path of these relationships should be set to N.
- Unequal Column Lengths
When column lengths are unequal in a relationship, File-AID/RDX logic forces a tablespace scan due to the unequal column lengths. For example, in a situation where the dependent value always had trailing blanks and the parent value did not, KEY file generation could be used for this relationship. Then, in the resulting Output Key file the parent value could be padded with blanks and the Output Key file could be used as the Input Key file which would allow for an index scan to be used.
The body of the Key File Specification screen consists of the following fields:
Specify Input Key File Information Area
Extract Driven by Input Key File
Specify Yes only when you are using an Input (external) Key File as selection input criteria for the driving object. No is the default.
File Name
Specify the name of the file containing the keys you want to use as input criteria for the driving object.
Member
If the file is partitioned, specify the name of the member that contains the key data.
Specify Delimited File Information Area
Delimited
Specify whether the key file’s format is Standard Db2 (No, default) or Delimited (Yes). Yes will continue with Input Key File - Delimited screen. No will continue with Input Key File - Positional screen.
Review the delimited file format options currently set by File-AID/RDX. Compare them to the actual delimited key file and modify the format options as needed.
Quotation Marks
Specify which quotation marks, single (’) or double (") are used in the key file. Enter a 1 for single quotation mark. Enter a 2 for double quotation mark.
Column Delimiter
Specify which character is used as the column delimiter. The column delimiter marks the end of the previous and the beginning of the next column. Any displayable character, including blank character, is acceptable.
Quotes on Character Data
Specify whether character data is enclosed by quotation marks. When set to Yes, File-AID/RDX expects character data to look like "JONES","MARY". Numbers containing special characters, for example date fields, are considered character data. For example, if set to Yes the date field could look like this "1995-09-29". When set to No the data in the key file would look like this: JONES,MARY or 1995-09-29.
Quotes on Numeric Data
Specify whether numeric data is enclosed by quotation marks. When set to Yes, the numeric data in the key file should look like "12345","6789". When set to No, File-AID/RDX expects numeric data to look like 12345,6789.
Input Key File - Positional
When you specify a non-delimited, standard Db2 format Input Key File as selection criteria for the driving object, the Input Key File - Positional screen is displayed as shown in the following figure.
Enter the key information for the applicable column(s) and press END to exit this screen.
Input Key File - Positional
Command ===> SCROLL ===> CSR
Driving Object: LOCATION.FRSAMP.ORDER_TABLE
Display Input Key File ===> B ( N = No; B = Browse; E = Edit)
Key-Format Codes available 1 = Display, 2 = Packed Decimal, 3 = Binary
Column Name Type(Length) Key-Postion Key-Length Key-Format
-------------------- ------------------ ----------- ---------- ----------
ORDER_NUMBER CHAR(6)
CUST_NUM CHAR(6)
SOC_SEC_NUM CHAR(11)
CREDIT_CARD_NUM CHAR(16)
ORD_TYPE CHAR(8)
ORD_DATE DATE
ORD_STAT CHAR(8)
ORD_AMOUNT DEC(9,2)
ORD_DEPOSIT DEC(9,2)
ORD_LINE_COUNT INTEGER
SHIP_CODE CHAR(2)
SHIP_DATE DATE
ORD_DESCRIPTION VCHAR(100)
** End of Columns **
Display Input Key File
Specify whether you want to view or edit the contents of the key file you specified as selection input for the driving object. You may want to browse or edit the key file to verify the key file’s content.
N | Don’t browse or edit the key file. |
B | File-AID/RDX will show you the contents of the key file in browse mode. Use the standard UP, DOWN, LEFT, or RIGHT scrolling commands to view the file. Press End to exit the browse mode. |
E | File-AID/RDX will show you the contents of the key file in edit mode. Use the standard ISPF Edit commands to edit the file. Press End to exit the edit mode. |
Column Name
Displays the names of the columns in the driving object.
Type(Length)
Displays the data type of each column in the driving object. For more information about Db2 data types, see Data Type Values.
Key File Options
When you want to use the values of a key file as selection input (WHERE EQUAL TO) for a column of the driving object, you must enter all three fields of the Key File Options. For example, you intend to extract all rows in the Db2 driving table where column CUST_NUM equals the value(s) of the corresponding column in the key file. In the key file, the value to use starts in Key-Position 7, has a Key-Length of 6, and Key-Format 1 (Display). File-AID/RDX will open the key file, take the values it finds from position 7 to 12 for all records, and extracts all rows matching the key values.
Key-Position
Specify the starting position of the selection value in the key file that contains selection criteria values for the corresponding column of the driving object.
Key-Length
Specify the length of the selection value in the key file that contains selection criteria values for the corresponding column of the driving object. See Maximum Key File Lengths for the allowable lengths for each key file format.
Key-Format
Enter the Key File Format Code that represents the non-delimited key file’s column type format. The valid codes are listed in the upper section of the screen. They are
1 | Display format. |
2 | Packed Decimal format. |
3 | Binary format. Valid only for Db2 INTEGER and SMALL_INTEGER column types. |
When key file options are entered, File-AID/RDX checks that format and length are compatible with the Db2 column type and length.
The following table lists the maximum key file lengths allowed for each key file format and the corresponding Db2 column type.
Maximum Key File Lengths
Db2 Column Type | Length - Display Format 1 | Length - Packed Decimal Format 2 | Length - Binary Format 3 |
|---|---|---|---|
CHAR | <= Db2 column length | <= (Db2 column length)/2 | Not allowed |
SMALL INTEGER | <=6 | <=3 | =2 |
INTEGER | <=11 | <=6 | =4 |
DECIMAL | <=Precision+2 | <=(Precision+1)/2 | Not allowed |
Input Key File - Delimited
When you specify a delimited format Input Key File as selection criteria for the driving object, the Input Key File - Delimited screen is displayed as shown in the following figure.
Enter the key field number for the applicable column(s) and press END to exit this screen.
Input Key File - Delimited
Command ===> SCROLL ===> CSR
Driving Object: LOCATION.FRSAMP.ORDER_TABLE
Display Input Key File ===> B ( N = No; B = Browse; E = Edit)
Column Name Type(Length) Field-Number in delimited file
-------------------- ------------------ ------------------------------
ORDER_NUMBER CHAR(6)
CUST_NUM CHAR(6)
SOC_SEC_NUM CHAR(11)
CREDIT_CARD_NUM CHAR(16)
ORD_TYPE CHAR(8)
ORD_DATE DATE
ORD_STAT CHAR(8)
ORD_AMOUNT DEC(9,2)
ORD_DEPOSIT DEC(9,2)
ORD_LINE_COUNT INTEGER
SHIP_CODE CHAR(2)
SHIP_DATE DATE
ORD_DESCRIPTION VCHAR(100)
** End of Columns **
Display Input Key File
Specify whether you want to view or edit the contents of the key file you specified as selection input for the driving object. You may want to browse or edit the key file to verify the key file’s content.
N | Don’t browse or edit the key file. |
B | File-AID/RDX will show you the contents of the key file in browse mode. Use the standard UP, DOWN, LEFT, or RIGHT scrolling commands to view the file. Press End to exit the browse mode. |
E | File-AID/RDX will show you the contents of the key file in edit mode. Use the standard ISPF Edit commands to edit the file. Press End to exit the edit mode. |
Column Name
Displays the names of the columns in the driving object.
Type(Length)
Displays the data type of each column in the driving object. For more information about Db2 data types, see Data Type Values.
Field Number in Delimited File
When using a delimited Input Key file you specify the field number from the key file that contains selection criteria values for the corresponding column(s) of the driving object. For example, you want to extract all rows in the Db2 driving object where column ORDER_NUMBER equals the value(s) of Field 2 in the delimited key file.
Primary Commands
The following File-AID/RDX-specific primary commands are valid on this screen:
CANCEL (CAN)
Enter the CANCEL command to return to the Selection Criteria Specification screen without saving any changes. There are no operands associated with this command.
END
Verifies the SQL syntax and returns you to the Selection Criteria Specification screen. There are no operands associated with this command.
UDTSWAP (UDTS)
Swaps the display for distinct type columns from UDT name to the built-in data type and length and vice versa. There are no operands associated with this command.
UDTTYPE (UDT)
Lists all distinct data types (UDTs) that are part of the extract. The list displays the schema, UDT name, and the built-in data type and length. There are no operands associated with this command.