SQLFORMATDISCARDFILE

Valid For

Replicat

Description

Use SQLFORMATDISCARDFILE to create a file for SQLCI formatted statements of operations that have been discarded because of errors. Once the original problem is corrected, SQL discard files can be used as input to replicate the failed operations.

The SQL formatted records include SET SESSION ABORT ERROR ON to cause the SQL action to abend if there is an error. BEGIN and COMMIT WORK statements are also included.

The requirements for using the SQL discard file are:

• The target for the Replicat must be SQL/MP. (The source database may be any that Oracle GoldenGate supports.)

• The contents of the SQL formatted discard file can be edited, but must be valid input to SQLCI. Because of the possible size of the file, TEDIT should be used; EDIT can fail on a memory error.

The SQL discard file is an edit type file so changes can be made if necessary. The size of the file is set at extents (70, 70, 128) and this should not be changed.

Note: The extents should not be changed because making it 1) smaller may result in an error 45 indicating the file is full when more lines could be processed 2) larger may suppress an error 46 indicating there are more lines than an edit file may contain.

See Using the SQL Formatted Discard File for information on editing the file and the rules that must be followed for valid input to SQLCI.

Default

No SQL discard file created.

Syntax

SQLFORMATDISCARDFILE file_name
[, APPEND | PURGE | ROLLOVER]
[, LINECOUNT number]
[, OWNER (group_number, user_number)]
[, SECURE "rwep"]

file_name

A physical file name or an existing define name of class map. The file_name must include the file location.

APPEND | PURGE | ROLLOVER

Specifies the handling of an existing SQL discard file.

• APPEND - Adds a record to the end of the file.

• PURGE - Purges an existing file and creates a new one. This is the default if no option is entered.

• ROLLOVER - Renames the existing discard file by appending a sequence number to the file_name according to these rules:

  • If the file-name is 7 characters or less, 1 digit is appended.

  • If the file name is 8 characters, the file will not rollover and a warning is given that the name is too long. This is true when the rollover is requested from GGSCI as well as from the DISCARDFILE parameter.

LINECOUNT number

Sets the number of lines in the edit file that will trigger rollover. number must be at least 5000 and no greater than 98000. The default is 98000 when no value is entered.

OWNER (group_number, user_number)

Defines ownership of the discard file.

SECURE “rwep“

Secures the file using standard Guardian security for read, write, execute and purge operations.

Example

SQLFORMATDISCARDFILE $DATA01.G11RPT.SQLDIS, APPEND, LINECOUNT 24000