Selective Column/Delimited Extract

When you specify D for the Add Selection Criteria prompt in the Extract DB2 - Driving Object panel and press Enter, File-AID for Db2 starts the Delimited Extract/Selective Column extract option specification.

Delimited Format Options

The Delimited Format Options screen displays when you specify D for the Add Selection Criteria prompt in the Extract DB2 - Driving Object panel. This screen shows the current options for the delimited extract file format. Use it to modify the options as needed to later download the extracted data. The modified options are saved only for your current session. The following figure shows a sample screen with prefilled options (not the defaults).

Delimited Format Options Screen

Dataset Format

Specify whether the data should be extracted in standard Db2 or delimited file format. You must enter either a 1 or 2, or you receive the error message “Invalid value”.

Enter a 1 for standard file format.

Enter a 2 for delimited file format.

Specify Delimited Format Options

Quotation Marks

Specify which quotation marks, single (’) or double (“) should be used in the delimited file format. Double quotation marks are the initial default.

Enter a 1 for single quotation mark.

Enter a 2 for double quotation mark.

Column Delimiter

Specify which character should be 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. Comma (,) is the initial default.

Use Quotes on Character Data

Specify whether character data will be enclosed by quotation marks. When set to YES (initial default), the output looks 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 output would look like this: JONES,MARY or 1995-09-29.

Use Quotes on Numeric Data

Specify whether numeric data will be enclosed by quotation marks. When set to YES, the output looks like “12345”,”6789”. There should not be any decimal points specified for fixed or floating point decimal columns. When set to NO, the output looks like 12345,6789. NO is the initial default.

Extract Column Names

Specify whether column names will be included as the first row in the extract file. Column names follow the same format as for Character data. An example is “EMPLOYEE_ID”,”LASTNAME”,”FIRSTNAME” etc. If YES, the extract file includes all selected column names. The initial setting is NO.

Date Format

Specify the format to mask date data. Any valid date format is acceptable, for example YYYY/MM/DD, DD.MM.YYYY, or MM/DD/YY. The initial date format is the same as the installation default for date format.

Press END (PF3) to continue to the Object Selection Criteria - Selective Columns screen.

Object Selection Criteria

This Object Selection Criteria screen (Object Selection Criteria - Selective Columns) displays after you press END (PF3) in the Delimited Format Options Screen. The screen is used to select which columns to extract, indicate the order of extraction, and to provide selection criteria for extraction of specific rows of data.

If triggers exist on the extract table, the message ‘Triggers not activated’ displays in the upper right-hand corner of the Object Selection Criteria screen.

Object Selection Criteria - Selective Columns

SEL

Place an S next to the column(s) from which you want data extracted. Unselected columns will not be included in the extract file.

Key/Index Indicator

(Untitled 1 byte field) Indicates P Primary key, F Foreign Key, or I High Order Index key (if not Primary or Foreign) for the column as an aid for users to create SQL that will perform well.

COLUMN NAME

Displays the names of the columns in the table, view, or alias.

TYPE(LEN)

Displays the type and length of data in each column.

Distinct types are identified by their names.

ORDER

Specify an integer to define how the extracted rows should be sorted in the extract, based on the data content of the columns. Entering 1 indicates the primary sort column.

A/D

Indicate whether ordered columns are to be sorted in ascending (A) or descending (D) order. If neither is specified, A is the default.

WHERE

Enter WHERE clauses to limit row selection. The WHERE field can be scrolled left and right. To scroll the WHERE field to the right, place the cursor in the WHERE field and press PF11. To scroll to the left, place the cursor in the WHERE field and press PF10. Scrolling allows you to view up to 256 characters.

The following delimiters can be used in the clauses:

>	OR	< >
<	> =	BETWEEN
=	< =	IN
¬ =	¬ >	LIKE
AND	¬ <	NOT

>	> =	< >
<	< =	¬ <
=	¬ =	¬ >

For all other WHERE clauses, you must enter the CAST function manually.

Press END (PF3) to return to the Extract DB2 - Driving Object panel.