GETENV

@GETENV returns a variety of information about Oracle GoldenGate processing, including lag information, the last replicated operation, and Oracle GoldenGate environment information.

Syntax

@GETENV (info_type)

info_type is one of the following.

"COMMITTIMESTAMP": Returns the timestamp when the transaction was committed as an integer representing the Julian GMT.
"JULIANTIMESTAMP": Returns the current Julian GMT timestamp in the form of an integer such as 211919385876765584.
"LOCALTIMESTAMP": Returns the current system time as an integer representing the Julian LCT.
("LAG", "unit"): Returns lag information. See "Reporting Lag Information".
("LASTERR","option"): Returns information about the last replicated operation, including detailed error information. See "Returning Information from Replicat".
("GGENVIRONMENT", "option"): Returns Oracle GoldenGate environment information. See "Returning Oracle GoldenGate Environment Information".
("GGFILEHEADER", "option"): Returns the format and properties of an Oracle GoldenGate trail file, which is stored in the file header record sent from open systems.
("GGHEADER", "option"): Returns Oracle GoldenGate record header information. See "Returning Record Header Information".
("RECORD", "option"): Returns information about the records that are being processed (such as the sequence number of the trail file), or the name of the source application program that altered the Enscribe file record. See "Returning Record Location and Source Application Information".
"RECSOUTPUT": Returns the total number of records processed.
("TLFKEY", SYSKEY unique_key): Enables a unique key to be associated with TLF/PTLF records in the ACI BASE24 application. See "Associating BASE24 Keys and Records".

Reporting Lag Information

Use the "LAG" option of @GETENV to return lag information. Lag is the difference between the time a record was processed by the Extract or Replicat program and the timestamp of that record in the data source. Both LAG and unit must be enclosed within double quotes.

Syntax

@GETENV ("LAG", "unit")

"unit" is one of the following.

"SEC": Returns the lag in seconds. This is the default when a unit is not explicitly provided for LAG.
"MSEC": Returns the lag in milliseconds.
"MIN": Returns the lag in minutes.

Returning Information from Replicat

Use the "LASTERR" option of @GETENV to return information about the last operation processed by the Replicat program. Options provide error information. Both LASTERR and option must be enclosed within double quotes.

Syntax

@GETENV ("LASTERR", "option")

"option" is one of the following.

"DBERRNUM"

Returns the database error number associated with the failed operation.

"DBERRMSG"

Returns the database error message associated with the failed operation.

"OPTYPE"

Returns the operation type that was attempted.

"OSERRNUM"

Specifies a NonStop operating system error.

"ERRTYPE"

Returns the type of error. Possible results are:

DB (for database errors)
MAP (for errors in mapping before replicating the record).

Returning Oracle GoldenGate Environment Information

Use the GGENVIRONMENT option of @GETENV to return information about the Oracle GoldenGate environment. This option is valid for the Extract and Replicat program processing.

Syntax

@GETENV ("GGENVIRONMENT", "option")

"option" is one of the following.

GROUPNAME: Returns the Extract or Replicat group name.
HOSTNAME: Returns the name of the host running the Extract or Replicat programs.
OSUSERNAME: Returns the operating system user name that started the process.

Returning Record Header Information

Use the GGHEADER option of @GETENV to return record header information. This option is valid for the Extract and Replicat processes.

Syntax

@GETENV ("GGHEADER", "option")
"option" is one of the following.

TABLENAME or SOURCEFILENAME: Returns the source table name.

TARGETFILENAME: Returns the target file name in use for the current record if there is a mapped target or targetname in use.

BEFOREAFTERINDICATOR

Returns the before or after indicator indicating whether the record is a before-image or after-image. Possible results are:

BEFORE (before-image)
AFTER (after-image)

COMMITTIMESTAMP

Returns the transaction timestamp (the time when the transaction committed) converted to the local time zone as a string in the format of YYYY-MM-DD HH:MI:SS.FFFFFF, for example:

2010-01-24 17:08:59.000000

LOGPOSITION

Returns the audit log position.

LOGRBA

Returns the relative byte address for the audit log.

OPTYPE

Returns the type of operation. Possible results are:

INSERT
UPDATE
DELETE
ENSCRIBE COMPUPDATE
SQL COMPUPDATE
PK UPDATE
TRUNCATE

If the operation is not one of the above types, then the function returns the word TYPE with the number assigned to the type. For more information about possible record types, see the file format information in Using the Logdump Utility

RECORDLENGTH

Returns the record length.

TRANSACTIONINDICATOR

Returns the transaction indicator. Possible results are:

BEGIN - Returned when the record header TransInD is 0 indicating the first statement in the transaction.
MIDDLE - Returned when the header TransInD is 1 indicating a statement in the middle of the transaction.
END - Returned when the TransInD is 2 indicating the last statement in the transactions
WHOLE - Returned when the TransInD is 3 indicating only one statement in the transaction.

Returning File Header Information

Use the GGFILEHEADER option of @GETENV to return attributes of an Oracle GoldenGate extract file or trail file that are stored in the file header sent from an Oracle GoldenGate system on Windows or UNIX. Every file in such a trail contains this header. The header describes the file itself and the environment in which it is used.

The file header is stored as a record at the beginning of a trail file preceding the data records. The information that is stored in the trail header provides enough information about the records to enable an Oracle GoldenGate process to determine whether the records are in a format that the current version of Oracle GoldenGate supports.

The trail header fields are stored as tokens, where the token format remains the same across all versions of Oracle GoldenGate. If a version of Oracle GoldenGate does not support any given token, that token is ignored. Deprecated tokens are assigned a default value to preserve compatibility with previous versions of Oracle GoldenGate.

This option is valid for the Replicat process. Both GGFILEHEADER and return_value must be enclosed within double quotes.

Note:

If a given database, operating system, or Oracle GoldenGate version does not provide information that relates to a given token, a NULL value will be returned.

Syntax

@GETENV ("GGFILEHEADER", "return_value")

The following sections describe the valid values for "return_value":

TrailInfo: Information about the trail file

"COMPATIBILITY"

The Oracle GoldenGate compatibility level of the trail file. The compatibility level of the current Oracle GoldenGate version must be greater than, or equal to, the compatibility level of the trail file to be able to read the data records in that file. Current valid values are 0 or 1.

1 means that the trail file is of Oracle GoldenGate version 10.0 or later, which supports file headers that contain file versioning information.
0 means that the trail file is of an Oracle GoldenGate version that is older than 10.0. File headers are not supported in those releases. The 0 value is used for compatibility to those Oracle GoldenGate versions.

"CHARSET"

The global character set of the trail file. For example:

WCP1252-1

"CREATETIMESTAMP"

The time that the trail was created, in local GMT Julian time in INT64.

"URI"

The universal resource identifier of the process that created the trail file, in the format of:

host_name:dir[:dir][:dir_n] group_name

host_name is the name of the server that hosts the process
dir is a subdirectory of the Oracle GoldenGate installation path.
group_name is the name of the process group that is linked with the process.

Example:

sys1:home:oracle:v9.5:extora

Shows where the trail was processed and by which process. This includes a history of previous runs.

"URIHISTORY"

List of the URIs of processes that wrote to the trail file before the current process.

For a primary Extract, this field is empty.
For a data pump, this field is URIHistory + URI of the input trail file.

"FILENAME"

Name of the trail file. Can be absolute or relative path, with forward or backward slash depending on the file system.

"FILEISTRAIL"

True/false flag indicating whether the trail file is a single file (such as one created for a batch run) or a sequentially numbered file that is part of a trail for online, continuous processing. If false, the SeqNum subtoken is not valid.

"FILESEQNO"

The sequence number of the trail file, without any leading zeros. For example, if a file sequence number is aa000026, FILESEQNO returns 26.

"FILESIZE"

Size of the trail file. It returns NULL on an active file and returns a size value when the file is full and the trail rolls over.

"FIRSTRECCSN"

The commit sequence number (CSN) of the first record in the trail file.Value is NULL until the trail file is completed.

"LASTRECCSN"

Returns the commit sequence number (CSN) of the last record in the trail file.Value is NULL until the trail file is completed.

"FIRSTRECIOTIME"

The time that the first record was written to the trail file. Value is NULL until the trail file is completed.

"LASTRECIOTIME"

The time that the last record was written to the trail file. Value is NULL until the trail file is completed.

ProducerInfo: Information about the Oracle GoldenGate process that created the trail file

"GROUPNAME"

The group name that is associated with the Extract process that created the trail. The group name is that which was given in the ADD EXTRACT command. For example, "ggext."

"DATASOURCE"

The data source that was read by the process. Can be one of:

DS_EXTRACT_TRAILS (source was an Oracle GoldenGate extract file, populated with change data)
DS_LOG_TABLE (source was an Oracle GoldenGate log table, used for trigger-based extraction)
DS_DATABASE (source was a direct select from database table written to a trail, used for SOURCEISTABLE-driven initial load)
DS_TRAN_LOGS (source was the database transaction log)
DS_INITIAL_DATA_LOAD (source was Extract; data taken directly from source tables)
DS_VAM_EXTRACT (source was a vendor access module)
DS_VAM_TWO_PHASE_COMMIT (source was a VAM trail)

"GGMAJORVERSION"

The major version of the Extract process that created the trail, expressed as an integer (xx).

"GGMINORVERSION"

The minor version of the Extract process that created the trail, expressed as an integer (xx.xx).

"GGMAINTENANCELEVEL"

The maintenance version of the process (xx.xx.xx).

"GGBUGFIXLEVEL"

The patch version of the process (xx.xx.xx.xx).

"GGBUILDNUMBER"

The build number of the process.

"GGVERSIONSTRING"

The version string of the process. For example 11.1.1.17A not for production.

MachineInfo: Information about the local host of the trail file

"HOSTNAME"

The DNS name of the computer where the Extract that wrote the trail is running. For example:

sysa
sysb
paris
hq25

"OSVERSION"

The major version of the operating system of the computer where the Extract that wrote the trail is running. For example:

Version s10_69
#1 SMP Fri Feb 24 16:56:28 EST 2006
5.00.2195 Service Pack 4

"OSRELEASE"

The release version of the operating system of the computer where the Extract that wrote the trail is running. For example, release versions of the examples given for OSVERSION could be:

5.10
2.6.9-34.ELsmp
2000 Advanced Server

"OSTYPE"

The type of operating system of the computer where the Extract that wrote the trail is running. For example:

SunOS
Linux
Microsoft Windows

"HARDWARETYPE"

The type of hardware of the computer where the Extract that wrote the trail is running. For example:

sun4u
x86_64
x86

DatabaseInfo: Information about the database that produced the data in the trail file

"DBTYPE"

The type of database that produced the data in the trail file. Some examples are:

DB2 UDB
DB2 ZOS
CTREE
MSSQL
MYSQL
ORACLE
SQLMX
SYBASE
TERADATA
TIMESTEN
NONSTOP

"DBNAME"

The name of the database, for example findb.

"DBINSTANCE"

The name of the database instance, if applicable to the database type, for example ORA1022A.

"DBCHARSET"

The character set that is used by the database that produced the data in the trail file. (For some databases, this will be empty.)

"DBMAJORVERSION"

The major version of the database that produced the data in the trail file.

"DBMINORVERSION"

The minor version of the database that produced the data in the trail file.

"DBVERSIONSTRING"

The maintenance (patch) level of the database that produced the data in the trail file.

"DBCLIENTCHARSET"

The character set of the database client.

"DBCLIENTVERSIONSTRING"

Returns the maintenance (patch) level of the database client. (For some databases, this will be empty.)

ContinuityInfo: Recovery information carried over from the previous trail file

"RECOVERYMODE": Internal use
"LASTCOMPLETECSN": Internal use
"LASTCOMPLETEXIDS": Internal use
"LASTCSN": Internal use
"LASTXiD": Internal use
"LASTCSNTS": Internal use

Returning Record Location and Source Application Information

Use the RECORD option of @GETENV to return location information of a record in the Oracle GoldenGate trail file or source application process information. The location information uniquely identifies a record through the sequence number of the trail file and the relative byte address or the transaction identifier. Source application information identifies the source program that alters the Enscribe file record.

Syntax

@GETENV ("RECORD", "option")

"option" is one of options described in the following sections:

Record Location Options

FILERBA: Returns the relative byte address (RBA) of the record within the FILESEQNO trail file.
FILESEQNO: Returns the sequence number of the trail file without any leading zeros.
TRANSID: Returns the TMF transaction identifier for which the record was altered.

Source Application Options

PROGRAMNAME: Returns the name of the source application program that altered the Enscribe file record.
PROCESSNAME: Returns the process identifier (PID) of the source application process that altered the Enscribe file record.

Associating BASE24 Keys and Records

Use the TLFKEY option of @GETENV to associate a unique key with TLF/PTLF records in the ACI BASE24 application. The 64-bit key is composed of the following concatenated items:

the number of seconds since 2000.
the block number of the record in the TLF/PTLF block multiplied by ten.
the node specified by the user (must be between 0 and 255).

This option is valid for the Extract and Replicat processes.

Syntax

@GETENV ("TLFKEY", SYSKEY, unique_key)

unique_key

The NonStop node number of the source TLF/PTLF file.

Example: @GETENV ("TLFKEY", SYSKEY, 7)