CONVERT

CONVERT
  INPUT_FILE { (USEDD) | explicitfilename }
  CONVERTED_FILE (  File Allocation Parameters )
  CONTROL_FILE  ( File Allocation Parameters )
  { TABLE_MAP mapid.name | TABLE_MAP_DEFINE (parameters) ; }
  [ DISCARD_COUNT n ]
  [ CONVERSION_ERROR_RC4 { NO | YES } ]
  [ REPORT_LEVEL { DETAIL | SUMMARY } ]
  [ SORT_ROWS { NO | YES } ]
  [ AGING (parameters) ]
  [ EXTERNAL_FILE ( File Allocation Parameters ) 
     [ TABLE (creator.tablename,dsname) ]
     [ FIELD_DELIMITER ‘c‘ ]
     [ STRING_DELIMITER ‘c' ]
     [ ESCAPE_CHARACTER ‘c' ]
     [ GENERATE_HEADER{ NO | YES }
        [ BEGIN_LABEL string ]
        [ END_LABEL string ]
        [ HEADER_DELIMITER ‘c' ]
        [ USE_COLUMN_LABELS { NO | YES } ] ] ]

INPUT_FILE

The fully qualified name of the Archive or Extract File to be converted. INPUT_FILE must be included in the CONVERT statement.

INPUT_FILE values must be enclosed in parentheses. DSNAME is required; other keywords and operands are optional, depending on site requirements.

USEDD

Use the file name specified in the PSDFEXTR control card statement.

explicitfilename

The fully qualified name of the Archive or Extract File

CONVERTED_FILE

The converted Extract or Archive File. See File Allocation Parameters to name and allocate the converted file. This keyword is required when

• INPUT_FILE is an Extract File and you want to write the converted data to a new Extract File
• INPUT_FILE is an Archive File and you want to write the converted data to a new Archive File

Omit this keyword when:

• INPUT_FILE is an Extract or Archive file and you want to write the converted data to an external file in CSV format
• INPUT_FILE is an Extract File, and you want the converted data to replace the contents of the input Extract File

CONTROL_FILE

The Control File. This keyword is required. Use the keywords in File Allocation Parameters to name and allocate the control file.

TABLE_MAP

The name of the table map to be used. TABLE_MAP is required if the converted file is an Archive or Extract File. Omit this keyword if the converted file is an external file in CSV format.

mapid.name

The fully qualified name of a new Table Map.

TABLE_MAP_DEFINE

The Optim™ online process generates this keyword when it creates a CONVERT job for batch execution.

When you create a CONVERT job outside of the Optim online process, the best practice is to use the TABLE_MAP keyword to refer to a named Table Map in the Optim Directory.

Place the TABLE_MAP_DEFINE parameters within parentheses. A semicolon must follow the close parenthesis. (See TABLE_MAP_DEFINE Parameters for the allowable parameters.)

DISCARD_COUNT

The maximum number of discarded rows for the CONVERT process. If the maximum value is exceeded, the CONVERT process terminates. Omit this keyword to allow an unlimited number of rows to be discarded.

n

Number in the range 1 - 4,294,967,295.

CONVERSION_ERROR_RC4

Option to enforce how the return code is set when conversion errors are detected, or rows discarded from user exits or data privacy functions.

NO

Set the return code to 0 regardless of any conversion errors that were detected. This is the default.

YES

Set the return code to 4 to indicate that one or more conversion errors were detected.

REPORT_LEVEL

The level of detail provided in the Process Report.

DETAIL

Produce a detailed report (default).

SUMMARY

Produce a summary report.

SORT_ROWS

Option to sort rows for destination tables with a cluster index. Specify:

NO

Do not sort rows (default).

YES

Sort rows

AGING

This keyword indicates that date values in the source columns are to be aged. It provides parameters to be used in the aging process. See AGING Keyword Parameters for detailed information on using this keyword.

EXTERNAL_FILE

Option to convert to an external file in CSV format. (The output for each table in the report will be directed to this file unless a TABLE keyword is supplied to direct the output for a specific table to a different file.) Omit the CONVERTED_FILE keyword if you use this keyword. See File Allocation Parameters for keywords to name and allocate the external file.

Use the following keywords to control options for the external file. These keywords apply only if you use the EXTERNAL_FILE keyword.

TABLE

Write the converted output for a specific table to an external file other than the file specified in the EXTERNAL_FILE keyword. All file allocation parameters for the EXTERNAL_FILE keyword, except dsname, apply to this file. You can use multiple TABLE keywords, provided each refers to a different table.

(cid.tblname,dsname)

The fully qualified table name and data set name, enclosed in parentheses and separated by a comma.

FIELD_DELIMITER

The character used to separate values in a row. A comma is the default.

'c'

The field delimiter character, enclosed in single (‘ ') or double quotes (“ ”).

STRING_DELIMITER

The character used to separate character literal values. A blank is the default.

‘c'

The string delimiter character, enclosed in single (‘ ') or double quotes (“ ”).

ESCAPE_CHARACTER

The character used to generate the value of a character normally used as a delimiter.

‘c'

The escape delimiter character, enclosed in single (‘ ') or double quotes (“ ”).

GENERATE_HEADER

Option to generate a header for the external file.

NO

Do not generate a header (default).

YES

Generate a header.

The following keywords are valid only if GENERATE_HEADER is YES.

BEGIN_LABEL

Option to place a label before the first column in the table.

string

The character string (up to 10 alphanumeric characters) to use as a label. To include the table name, specify $table.

Note: If you enclose the string in quotes, the quotes are considered part of the string and included in the header.

END_LABEL

Option to place a label after the last column in the table.

string

The character string (up to 10 alphanumeric characters) to use as a label.

Note: If you enclose the string in quotes, the quotes are considered part of the string and included in the header.

HEADER_DELIMITER

The character used to separate column headings. A comma is the default.

‘c'

The header delimiter character, enclosed in single (‘ ') or double quotes (“ ”).

USE_COLUMN_LABELS

Indicate whether to use column labels in the header.

NO

Do not use column labels in the header (default).

YES

Use column labels in the header.

Examples

The following are examples of CONVERT batch statement usage:

1. To convert an Archive File, write the converted data to a new Archive File, and create a summary process report, specify:

   CONVERT INPUT_FILE PSTUSER.ARCSALES
      CONVERTED_FILE (DSNAME PSTHR.SAL)
      CONTROL_FILE (DSNAME PSTUSER.CVCON)
      TABLE_MAP PSTUSER.MAP11
      REPORT_LEVEL SUMMARY
   

2. To convert an Extract File to CSV format, allocate 60 tracks of space for the output file, and generate a header that has the table name as its label, specify:

   CONVERT INPUT_FILE PSTUSER.NESALE
      CONTROL_FILE (PSTUSER.SALECTRL)
      EXTERNAL_FILE (DSNAME  PSTUSER.SALECSV
                     PRIMARY  60T)
      GENERATE_HEADER YES
      BEGIN_LABEL $table