Create a Row List File

The Point-and-Shoot facility is used with Archive, Compare, or Move to select the primary key values for rows in the Start Table when you want to extract data from a database or restore it to a database. These values are automatically stored in a Row List file.

The Point-and-Shoot facility is available for DB2^® tables only. If the data does not reside in DB2, you can create a Row List file with ISPF or a similar editor. The file must conform to the format generated by Point-and-Shoot, as follows:

The file must be a member of a partitioned dataset or a sequential dataset.
The file must contain 80-character records. (Note that any line numbers in positions 73-80 are interpreted as data. Do not use line numbers.)
The data in the file must conform to DB2 syntax and rules for column data types. Additionally, the data must match, by data type and length, the attributes of the primary key column(s) in the Start Table.
The DD name of the file must be PSDFPNS.

Example

For example, assume you want to archive or extract specific rows from the DETAILS table using a list created by a method other than Point-and-Shoot. The primary key for the DETAILS table consists of two columns, ORDER_ID and ITEM_ID. These columns are defined as follows:

Column Name	Data Type
ORDER_ID	DEC(5,0)
ITEM_ID	CHAR(5)

The following figure shows an example of the structure of the list. In the example, the file containing the list is displayed in the ISPF editor. The value in the ORDER_ID column is followed by the value in the ITEM_ID column for each row. Commas separate the column values for each row, and semicolons separate each row.

Figure: Primary Key Values

 EDIT ---- FOPDEMO.FOPPK.KEY -------------------------- COLUMNS 001 072
 COMMAND ===>                                           SCROLL ===> CSR
 ****** ************************ TOP OF DATA **************************
 000001 00123, 'CH001'; 00124, 'CH002'; 00125, 'CH003'; 00126, 'CH004';
 000002 00133, 'CH001'; 00134, 'CH002'; 00135, 'CH003'; 00146, 'CH004';
 000003 00153, 'CH001'; 00154, 'CH002'; 00155, 'CH003'; 00156, 'CH004';
 ****** ********************** BOTTOM OF DATA *************************

The format requirements are:

Values must be separated by commas (,) and one or more spaces.
Primary key values for each row must be separated by semicolons (;) and one or more spaces.

Character Data

Character data must be enclosed in single quotes (e.g., ‘abc'). Embedded quotes must be in the form of two single quotes.
Character data in a column can be wrapped to the next line. Each portion of the data must be enclosed in quotes, without an intervening semicolon. For example:

‘Data for Row 1' ‘Col 1', ‘Data for' ‘Row 1 Col 2': The value for a column exceeds the length of the line and wraps to the next line. Each portion of a value is enclosed in single quotes (‘ '). Commas separate column values.

‘Data for Row 1 Col 1', ‘Data for Row 1 Col 2'; ‘Data for Row 2 Col 1', ‘Data for Row 2 Col 2';: The value for a column fills the entire line length. Each column value is enclosed in single quotes (‘ '). Commas separate column values. Semicolons (;) separate column values for each row.

Character data that is stored in fixed-length columns is truncated or padded appropriately to fit the column. Character data that is stored in variable-length columns is truncated if necessary, but is not padded.

Date/Time

All date and time data must be enclosed in single quotes. Any valid DB2 format for these values is acceptable and is handled appropriately.

Numeric Data

Numeric data is not enclosed in quotes. A decimal can be indicated by either a comma or a period, and it is handled appropriately.

Partial Primary Key

You can use this information to create a Point-and-Shoot file to specify an alternate key or a partial primary key. This is very useful for selecting non-unique values or values that do not correspond to the primary key. To indicate that the data in a file contains values for some set of the columns, prefix the file with:

COLUMN-LIST

Names of the columns for which data is supplied

END-COLUMN-LIST

The data for the columns is specified in the order in which the columns are listed.

Example 1: Assume you have a set of ITEMS rows that are not in your DB2 database. However, you want to extract the DETAILS rows from your DB2 database for specific ITEMS. The primary key for the DETAILS rows is comprised of two columns: ORDER_ID and ITEM_ID. However, you prefer to extract rows based on the ITEM_ID only. You can generate a file to use as the Point-and-Shoot file to extract the several DETAILS rows for each specified ITEM_ID value, regardless of the ORDER_ID value.

Figure: Specify Partial Primary Key - Example 1

 EDIT ---- FOPDEMO.FOPPK.KEY --------------------------- COLUMNS 001 07
 COMMAND ===>                                           SCROLL ===> CSR
 ****** ************************ TOP OF DATA **************************
 000001 COLUMN-LIST
 000002  ITEM_ID
 000003 END-COLUMN-LIST
 000004 'CH001'; 'CH002'; 'CH003'; 'CH004'; 
 000005 'CH005'; 'CH006'; 'CH007'; 'CH008'; 
 000006 'CH009'; 'CH010'; 
 ****** ********************** BOTTOM OF DATA *************************

Example 2: Assume that two columns comprise the partial primary key: ORDER_ID and ITEM_ID. Specify the column values in the order they are listed. For each row in this example, the values for ORDER_ID are followed by the values for ITEM_ID, separated by commas. Semicolons separate the rows. Since commas and semicolons delimit the values, entries can span multiple lines and multiple entries can be specified on a single line.

Figure: Specify Partial Primary Key - Example 2

 EDIT ---- FOPDEMO.FOPPK.KEY --------------------------- COLUMNS 001 07
 COMMAND ===>                                           SCROLL ===> CSR
 ****** ************************ TOP OF DATA **************************
 000001 COLUMN-LIST
 000002  ORDER_ID
 000003  ITEM_ID
 000004 END-COLUMN-LIST
 000001 00123, 'CH001'; 00124, 'CH002'; 00125, 'CH003'; 00126, 'CH004';
 000002 00133, 'CH001'; 00134, 'CH002'; 00135, 'CH003'; 00146, 'CH004';
 000003 00153, 'CH001'; 00154, 'CH002'; 00155, 'CH003'; 00156, 'CH004';
 ****** ********************** BOTTOM OF DATA *************************

Use the Row List File

Do the following to use a Row List file for a process:

For an Archive or Extract Process, choose Option 1 TABLES from the EXTRACT Process or ARCHIVE Process menu to display the Select Tables/Views for AD panel.
For a Compare Process that uses an Access Definition as one or both sources, specify that the Access Definition is to be modified and display the Select Tables/Views for AD panel.

For an Archive Selective Restore Process, choose Option 3 SELECTION CRITERIA to display the Archive Selection Criteria - Tables panel.
Enter the POINT command on the panel to display a pop-up window that will prompt you for an Input DSN. Specify the name of the Row List file you created and press END. When the process is performed, that file is used to select rows from the Start Table.