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). -
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 as2013-12-11 18:26:06:35
with the0
option,2013-12-11 18:26:06:35.123
with the3
option, and2013-12-11 18:26:06:35.123456
with6
.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
orCOLUMNDELIMITER
cannot be the same as the character specified for the record usingRECORDDELIMITER
.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 wordNULL
. -
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.