2.88 FORMATASCII | NOFORMATASCII

Valid for

Extract

Description

Use FORMATASCII to format subsequent output in external ASCII format. FORMATASCII applies to all FILE or TABLE specifications that follow the FORMATASCII entry, and can be turned off with the NOFORMATASCII parameter.

Using FORMATASCII, you can format output compatible with the majority of popular database load utilities and other tools.

You can specify the parameter with or without options. If you don't include options, records are output as follows:

  • An operation type character, I, D, U, V (insert, delete, update, compressed update).

  • A before or after-image indicator, B or A.

  • The file or table name.

  • If the NAMES option (the default) is selected: field name, field value, field name, field value...

  • If the NONAMES option is selected: field value, field value... (NAMES format is always used for compressed records).

  • Between each of the above items, a field delimiter (which defaults to tab).

  • A new line character (line feed).

Before the beginning of the first record output for each transaction, a begin transaction record will appear. This record includes the following information:

  • The begin transaction indicator, B.

  • The timestamp at which the record committed.

  • The sequence number of the audit trail in which the commit was found.

  • The relative byte address (RBA) of the commit record within the audit trail.

  • Delimiters following each of the above fields, followed by a new line character.

After the last record in each transaction is output, a commit record will appear. This record contains C, the delimiter and a new line character.

Every record in a transaction, and no other records, are contained between the begin and commit indicators. Each combination of commit timestamp and RBA is unique and increases as records are output.

You can customize the output format with options described in the syntax.

Do not use this format if the data is to be processed by Replicat. Replicat expects the default format.

Syntax

NOFORMATACSII | FORMATASCII [, option, ...]

option is one of the following.

BCP

Formats the output for compatibility with SQL Server's BCP (Bulk Copy Program) high-speed load utility.

Options that can be used with BCP are:

BCPRECORDLIMITER

The option changes the line delimiter from only line feed ('\n') to line feed and carriage return ('\r\n'). Use when sending data to Windows or UNIX SQL in bulk copy format. NonStop will not receive data in this format.

TIMESTAMP {0 | 3 | 6}

Valid when sending data to Windows or UNIX SQL in bulk copy format. The numeric options set the fractional part of the datetime data type:

  • 0 — truncate the fractional portion of the timestamp

  • 3 — include three digits in the fractional portion

  • 6 — include six digits in the fractional portion

For example, the datetime 2013-12-11 18:26:06:35.123456 would be output as 2013-12-11 18:26:06:35 with the 0 option, 2013-12-11 18:26:06:35.123 with the 3 option, and 2013-12-11 18:26:06:35.123456 with 6.

The default format for the datetime data type is 0, truncate the fractional portion.

Note:

FORMATLOCAL must be used with datetime fractional options 3 or 6 for proper processing by the Collector on a Windows or Linux/UNIX system.

COLHDRS

Outputs the table's column names before the data. COLHDRS takes effect only when extracting directly from the table (rather than the TMF audit trails).

{DATE | TIME | TS}

Specifies one of the following:

  • DATE — date (year to day),

  • TIME — time (year to second) before record data,

  • TS — transaction timestamp (year to fraction).

DELIMITER delimiter | COLUMNDELIMITER delimiter | RECORDDELIMITER delimiter

Each option sets an alternative delimiter for the column or record.

For example:

FORMATASCII DELIMITER 0x09 RECORDDELIMITER 0x0a
FORMATASCII COLUMNDELIMITER ',' RECORDDELIMITER ';'
FORMATASCII DELIMITER '/' RECORDDELIMITER 0x0a

Note that the character specified for the field delimiter using either DELIMITER or COLUMNDELIMITER cannot be the same as the character specified for the record using RECORDDELIMITER.

The options for setting the delimiters include the following:

DELIMITER delimiter

An alternative field/column delimiter (the default is tab). Use the word TAB to delimit with tabs, otherwise use a single character enclosed in single quotes (for example, '/') or a hexadecimal (for example, 0x0a).

COLUMNDELIMITER delimiter

This is an alias of DELIMITER.

RECORDDELIMITER delimiter

Sets the record delimiter . The delimiter can be specified as a single printable character enclosed within single quotes (for example, ';') or a hexadecimal (for example, 0x0a).

EXTRACOLS number_of_columns

Includes placeholders for additional columns at the end of each record. Use this when a target table has more columns than the source.

FILE

Includes just the file name portion of the file or table (default is the fully qualified file name).

NAMES | NONAMES

Includes or excludes column names from the output. For compressed records, column names are included unless you also specify PLACEHOLDERS.

NOHDRFIELDS header_option

Suppresses output of transaction information, the operation type character, the before or after-image indicator, and the file or table name.

You can customize header information by including a header_option as follows:

  • IND includes the before or after indicator

  • OP includes the operation type character

  • WHOLEFILE includes the fully qualified file name

  • FILE includes the file name portion of the file or table (as specified above)

NOQUOTE

Excludes quotation marks from character-type data. The default is to use quotation marks.

NOSYSKEY

Omits the record SYSKEY (relative or entry key) from the output, if one exists.

NOTRANSTMTS

Excludes transaction information.

NULLISSPACE

Outputs NULL fields as empty fields. The default is to output null fields as the word NULL.

PLACEHOLDERS

Outputs a placeholder for missing fields or columns. For example, if the second and fourth columns are missing in a four column table, the data would appear similar to:

'ABC',,123,,
SQLLOADER

Generates a file compatible with the Oracle SQL*Loader high-speed data load utility. SQLLOADER produces a fixed-length, ASCII-formatted file. Use this option only when one table's data is written to the Oracle GoldenGate trail (usually in the initial-load, SOURCEISFILE case).

Example

The following is a sample table and description. CUSTNAME is the primary key.

$DATA1.TEST.CUSTOMER:
CUSTNAME   CHAR(10)
LOCATION   CHAR(10)
BALANCE    INTEGER

The transaction on this table is:

BEGIN WORK;
INSERT INTO CUSTOMER VALUES ("Eric", "San Fran", 550);
UPDATE CUSTOMER SET BALANCE = 100 WHERE CUSTNAME = "Eric";
COMMIT WORK;

Entering FORMATASCII produces:

B,2010-02-17:14:09:46.421335,8,1873474,
I,A,\GGS.$DATA1.TEST.CUSTOMER,CUSTNAME,'Eric',LOCATION, 'San Fran',BALANCE,550,
V,A,\GGS.$DATA1.TEST.CUSTOMER,CUSTNAME,'Eric',BALANCE,100, C,

Entering FORMATASCII, NONAMES, DELIMITER '|', FILE produces:

B|2010-02-17:14:09:46.421335|8|1873474|
I|A|CUSTOMER|'Eric'|'San Fran'|550|
V|A|CUSTOMER|CUSTNAME|'Eric'|BALANCE|100|
C|

The last record returns column names because the record is a compressed update.

Entering FORMATASCII, NOHDRFIELDS, FILE, OP, TS, NONAMES, NOQUOTE produces:

I,CUSTOMER,2010-02-17:14:09:46.421335,Eric,San Fran,550,
V,CUSTOMER,2010-02-17:14:09:46.421335,Eric,,100,

The absence of the second field in the update record is indicated by two consecutive commas. Ordering of header fields is predetermined and is not affected by the ordering of options.

The ampersand (&) in the parameter entry continues the parameter to the next line in the parameter file.