1. Reference for Oracle GoldenGate
  2. Oracle GoldenGate Common Components
  3. Column Conversion Functions
  4. @GETENV

@GETENV

Use the @GETENV function to return information about the Oracle GoldenGate environment. You can use the information as input into the following:

  • Stored procedures or queries (with SQLEXEC)

  • Column maps (with the COLMAP option of TABLE or MAP)

  • User tokens (defined with the TOKENS option of TABLE and mapped to target columns by means of the @TOKEN function)

  • The GET_ENV_VALUE user exit function (see "GET_ENV_VALUE")

    Note:

    All syntax options must be enclosed within quotes as shown in the syntax descriptions.

Syntax

@GETENV (
'LAG' , 'unit' |
'LASTERR' , 'error_info' |
'JULIANTIMESTAMP' |
'JULIANTIMESTAMP_PRECISE' |
'RECSOUTPUT' |
{'STATS'|'DELTASTATS'}, ['TABLE', 'table'], 'statistic' |
'GGENVIRONMENT', 'environment_info' |
'GGFILEHEADER', 'header_info' |
'GGHEADER', 'header_info' |
'RECORD', 'location_info' |
'DBENVIRONMENT', 'database_info'
'TRANSACTION', 'transaction_info' |
'OSVARIABLE', 'variable' |
'TLFKEY', SYSKEY, unique_key
'USERNAME',
'OSUSERNAME',
'MACHINENAME',
'PROGRAMNAME',
'CLIENTIDENTIFIER',
)

'LAG' , 'unit'

Valid for Extract and Replicat.

Use the LAG option of @GETENV to return lag information. Lag is the difference between the time that a record was processed by Extract or Replicat and the timestamp of that record in the data source.

Syntax

@GETENV ('LAG', {'SEC'|'MSEC'|'MIN'})
'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.

'LASTERR' , 'error_info'

Valid for Replicat.

Use the LASTERR option of @GETENV to return information about the last failed operation processed by Replicat.

Syntax

@GETENV ('LASTERR', {'DBERRNUM'|'DBERRMSG'|'OPTYPE'|'ERRTYPE'})
'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. For a list of Oracle GoldenGate operation types, see Administering Oracle GoldenGate.

'ERRTYPE'

Returns the type of error. Possible results are:

  • DB (for database errors)

  • MAP (for errors in mapping)

'JULIANTIMESTAMP' | 'JULIANTIMESTAMP_PRECISE'

Valid for Extract and Replicat.

Use the JULIANTIMESTAMP option of @GETENV to return the current time in Julian format. The unit is microseconds (one millionth of a second). On a Windows machine, the value is padded with zeros (0) because the granularity of the Windows timestamp is milliseconds (one thousandth of a second). For example, the following is a typical column mapping: 

MAP dbo.tab8451, Target targ.tabjts, COLMAP (USEDEFAULTS, &
JTSS = @GETENV ('JULIANTIMESTAMP')
JTSFFFFFF = @date ('yyyy-mm-dd hh:mi:ss.ffffff', 'JTS', &
@getenv ('JULIANTIMESTAMP') ) )
;

Possible values that the JTSS and JTSFFFFFF columns can have are: 

212096320960773000 2010-12-17:16:42:40.773000
212096321536540000 2010-12-17:16:52:16.540000
212096322856385000 2010-12-17:17:14:16.385000
212096323062919000 2010-12-17:17:17:42.919000
212096380852787000 2010-12-18:09:20:52.787000

The last three digits (the microseconds) of the number all contain the padding of 0s .

Optionally, you can use the 'JULIANTIMESTAMP_PRECISE' option to obtain a timestamp with high precision though this may effect performance.

Note:

Do not use these values for ordering operations. Instead use this value: @COMPUTE(@COMPUTE(@NUMSTR(@GETENV ("RECORD", "FILESEQNO")*100000000000)+@NUMSTR(@GETENV ("RECORD", "FILERBA")))"

Syntax

@GETENV ('JULIANTIMESTAMP')
@GETENV ('JULIANTIMESTAMP_PRECISE')

'RECSOUTPUT'

Valid for Extract.

Use the RECSOUTPUT option of @GETENV to retrieve a current count of the number of records that Extract has written to the trail file since the process started. The returned value is not unique to a table or transaction, but instead for the Extract session itself. The count resets to 1 whenever Extract stops and then is started again.

Syntax

@GETENV ('RECSOUTPUT')

{'STATS'|'DELTASTATS'}, ['TABLE', 'table'], 'statistic'

Valid for Extract and Replicat.

Use the STATS and DELTASTATS options of @GETENV to return the number of operations that were processed per table for any or all of the following:

  • INSERT operations

  • UPDATE operations

  • DELETE operations

  • TRUNCATE operations

  • Total DML operations

  • Total DDL operations

  • Number of conflicts that occurred, if the Conflict Detection and Resolution (CDR) feature is used.

  • Number of CDR resolutions that succeeded

  • Number of CDR resolutions that failed

Any errors in the processing of this function, such as an unresolved table entry or incorrect syntax, returns a zero (0) for the requested statistics value.

Understanding How Recurring Table Specifications Affect Operation Counts

An Extract that is processing the same source table to multiple output trails returns statistics based on each localized output trail to which the table linked to @GETENV is written. For example, if Extract captures 100 inserts for table ABC and writes table ABC to three trails, the result for the @GETENV is 300 

EXTRACT ABC
...
EXTTRAIL c:\ogg\dirdat\aa;
TABLE TEST.ABC;
EXTTRAIL c:\ogg\dirdat\bb;
TABLE TEST.ABC;
TABLE EMI, TOKENS (TOKEN-CNT = @GETENV ('STATS', 'TABLE', 'ABC', 'DML'));
EXTTRAIL c:\ogg\dirdat\cc;
TABLE TEST.ABC;

In the case of an Extract that writes a source table multiple times to a single output trail, or in the case of a Replicat that has multiple MAP statements for the same TARGET table, the statistics results are based on all matching TARGET entries. For example, if Replicat filters 20 rows for REGION 'WEST,' 10 rows for REGION 'EAST,' 5 rows for REGION 'NORTH,' and 2 rows for REGION 'SOUTH' (all for table ABC) the result of the @GETENV is 37. 

REPLICAT ABC
...
MAP TEST.ABC, TARGET TEST.ABC, FILTER (@STREQ (REGION, 'WEST'));
MAP TEST.ABC, TARGET TEST.ABC, FILTER (@STREQ (REGION, 'EAST'));
MAP TEST.ABC, TARGET TEST.ABC, FILTER (@STREQ (REGION, 'NORTH'));
MAP TEST.ABC, TARGET TEST.ABC, FILTER (@STREQ (REGION, 'SOUTH'));
MAP TEST.EMI, TARGET TEST.EMI, &
    COLMAP (CNT = @GETENV ('STATS', 'TABLE', 'ABC', 'DML'));

Capturing Multiple Statistics

You can execute multiple instances of @GETENV to get counts for different operation types.

This example returns statistics only for INSERT and UPDATE operations: 

REPLICAT TEST
..
..
MAP TEST.ABC, TARGET TEST.ABC, COLMAP (USEDEFAULTS, IU = @COMPUTE (@GETENV &
    ('STATS', 'TABLE', 'ABC', 'DML') - (@GETENV ('STATS', 'TABLE', &
    'ABC', 'DELETE'));

This example returns statistics for DDL and TRUNCATE operations: 

REPLICAT TEST2
..
..
MAP TEST.ABC, TARGET TEST.ABC, COLMAP (USEDEFAULTS, DDL = @COMPUTE &
(@GETENV ('STATS', 'DDL') + (@GETENV ('STATS', 'TRUNCATE'));

Example Use Case

In the following use case, if all DML from the source is applied successfully to the target, Replicat suspends by means of EVENTACTIONS with SUSPEND, until resumed from GGSCI with SEND REPLICAT with RESUME.

GETENV used in Extract parameter file: 

TABLE HR1.HR*;
TABLE HR1.STAT, TOKENS ('env_stats' = @GETENV ('STATS', 'TABLE', &
    'HR1.HR*', 'DML'));

GETENV used in Replicat parameter file: 

MAP HR1.HR*, TARGET HR2.*;
MAP HR1.STAT, TARGET HR2.STAT, filter (
    @if (
    @token ('stats') =
    @getenv ('STATS', 'TABLE', 'TSSCAT.TCUSTORD', 'DML'), 1, 0 )
    ),
    eventactions (suspend);

Using Statistics in FILTER Clauses

Statistics returned by STATS and DELTASTATS are dynamic values and are incremented after mapping is performed. Therefore, when using CDR statistics in a FILTER clause in each of multiple MAP statements, you need to order the MAP statements in descending order of the statistics values. If the order is not correct, Oracle GoldenGate returns error OGG-01921. For detailed information about this requirement, see Document 1556241.1 in the Knowledge base of My Oracle Support at http://support.oracle.com.

Example 3-1 MAP statements containing statistics in FILTER clauses

In the following example, the MAP statements containing the filter for the CDR_CONFLICTS statistic are ordered in descending order of the statistic: >3, then =3, then <3. 

MAP TEST.GG_HEARTBEAT_TABLE, TARGET TEST.GG_HEARTBEAT_TABLE COMPARECOLS (ON UPDATE ALL),RESOLVECONFLICT(UPDATEROWEXISTS,(DEFAULT, OVERWRITE)),FILTER (@GETENV ("STATS", "CDR_CONFLICTS") > 3),EVENTACTIONS (LOG INFO);MAP TEST.GG_HEARTBEAT_TABLE, TARGET TEST.GG_HEARTBEAT_TABLE COMPARECOLS (ON UPDATE ALL),RESOLVECONFLICT(UPDATEROWEXISTS,(DEFAULT, OVERWRITE)),FILTER (@GETENV ("STATS", "CDR_CONFLICTS") = 3),EVENTACTIONS (LOG WARNING);MAP TEST.GG_HEARTBEAT_TABLE, TARGET TEST.GG_HEARTBEAT_TABLE COMPARECOLS (ON UPDATE ALL),RESOLVECONFLICT(UPDATEROWEXISTS,(DEFAULT, OVERWRITE)),FILTER (@GETENV ("STATS", "CDR_CONFLICTS") < 3),EVENTACTIONS (LOG WARNING);

Syntax

@GETENV ({'STATS' | 'DELTASTATS'}, ['TABLE', 'table'], 'statistic')
{'STATS' | 'DELTASTATS'}

STATS returns counts since process startup, whereas DELTASTATS returns counts since the last execution of a DELTASTATS.

The execution logic is as follows:

  • When Extract processes a transaction record that satisfies @GETENV with STATS or DELTASTATS, the table name is matched against resolved source tables in the TABLE statement.

  • When Replicat processes a trail record that satisfies @GETENV with STATS or DELTASTATS, the table name is matched against resolved target tables in the TARGET clause of the MAP statement.

'TABLE', 'table'

Executes the STATS or DELTASTATS only for the specified table or tables. Without this option, counts are returned for all tables that are specified in TABLE (Extract) or MAP (Replicat) parameters in the parameter file.

Valid table_name values are:

  • 'schema.table' specifies a table.

  • 'table' specifies a table of the default schema.

  • 'schema.*' specifies all tables of a schema.

  • '*' specifies all tables of the default schema.

For example, the following counts DML operations only for tables in the hr schema: 

MAP fin.*, TARGET fin.*;
MAP hr.*, TARGET hr.*;
MAP hq.rpt, TARGET hq.rpt, COLMAP (USEDEFAULTS, CNT = @GETENV ('STATS', 'TABLE', 'hr.*', 'DML'));

Likewise, the following counts DML operations only for the emp table in the hr schema: 

MAP fin.*, TARGET fin.*;
MAP hr.*, TARGET hr.*;
MAP hq.rpt, TARGET hq.rpt, COLMAP (USEDEFAULTS, CNT = @GETENV ('STATS', 'TABLE', 'hr.emp', 'DML'));

By contrast, because there are no specific tables specified for STATS in the following example, the function counts all INSERT, UPDATE, and DELETE operations for all tables in all schemas that are represented in the TARGET clauses of MAP statements: 

MAP fin.*, TARGET fin.*;
MAP hr.*, TARGET hr.*;
MAP hq.rpt, TARGET hq.rpt, COLMAP (USEDEFAULTS, CNT = &
@GETENV ('STATS', 'DML'));
'statistic'

The type of statistic to return. See Using Statistics in FILTER Clauses for important information when using statistics in FILTER clauses in multiple TABLE or MAP statements.

'INSERT'

Returns the number of INSERT operations that were processed.

'UPDATE'

Returns the number of UPDATE operations that were processed.

'DELETE'

Returns the number of DELETE operations that were processed.

'DML'

Returns the total of INSERT, UPDATE, and DELETE operations that were processed.

'TRUNCATE'

Returns the number of TRUNCATE operations that were processed. This variable returns a count only if Oracle GoldenGate DDL replication is not being used. If DDL replication is being used, this variable returns a zero.

'DDL'

Returns the number of DDL operations that were processed, including TRUNCATEs and DDL specified in INCLUDE and EXCLUDE clauses of the DDL parameter, all scopes (MAPPED, UNMAPPED, OTHER). This variable returns a count only if Oracle GoldenGate DDL replication is being used. This variable is not valid for 'DELTASTATS'.

'CDR_CONFLICTS'

Returns the number of conflicts that Replicat detected when executing the Conflict Detection and Resolution (CDR) feature.

Example for a specific table:

@GETENV ('STATS','TABLE','HR.EMP','CDR_CONFLICTS')

Example for all tables processed by Replicat:

@GETENV ('STATS','CDR_CONFLICTS')
'CDR_RESOLUTIONS_SUCCEEDED'

Returns the number of conflicts that Replicat resolved when executing the Conflict Detection and Resolution (CDR) feature.

Example for a specific table:

@GETENV ('STATS','TABLE','HR.EMP', 'CDR_RESOLUTIONS_SUCCEEDED')

Example for all tables processed by Replicat:

@GETENV ('STATS','CDR_RESOLUTIONS_SUCCEEDED')
'CDR_RESOLUTIONS_FAILED'

Returns the number of conflicts that Replicat could not resolve when executing the Conflict Detection and Resolution (CDR) feature.

Example for a specific table:

@GETENV ('STATS','TABLE','HR.EMP', 'CDR_RESOLUTIONS_FAILED')

Example for all tables processed by Replicat:

@GETENV ('STATS','CDR_RESOLUTIONS_FAILED')

'GGENVIRONMENT' , 'environment_info'

Valid for Extract and Replicat.

Use the GGENVIRONMENT option of @GETENV to return information about the Oracle GoldenGate environment.

Syntax

@GETENV ('GGENVIRONMENT', {'DOMAINNAME'|'GROUPDESCRIPTION'|'GROUPNAME'|
   'GROUPTYPE'|'HOSTNAME'|'OSUSERNAME'|'PROCESSID')
'DOMAINNAME'

(Windows only) Returns the domain name associated with the user that started the process.

'GROUPDESCRIPTION'

Returns the description of the group, taken from the checkpoint file. Requires that a description was provided with the DESCRIPTION parameter when the group was created with the ADD command in GGSCI.

'GROUPNAME'

Returns the name of the process group.

'GROUPTYPE'

Returns the type of process, either EXTRACT or REPLICAT.

'HOSTNAME'

Returns the name of the system running the Extract or Replicat process.

'OSUSERNAME'

Returns the operating system user name that started the process.

'PROCESSID'

Returns the process ID that is assigned to the process by the operating system.

'GGHEADER' , 'header_info'

Valid for Extract and Replicat.

Use the GGHEADER option of @GETENV to return information from the header portion of an Oracle GoldenGate trail record. The header describes the transaction environment of the record. For more information on record headers and record types, see Administering Oracle GoldenGate.

Syntax

@GETENV ('GGHEADER', {'BEFOREAFTERINDICATOR'|'COMMITTIMESTAMP'|'LOGPOSITION'|
   'LOGRBA'|'OBJECTNAME'|'TABLENAME'|'OPTYPE'|'RECORDLENGTH'|
   'TRANSACTIONINDICATOR'})

Note:

Do not use TIMESTAMP_PRECISE for ordering operations. Instead use this value: @COMPUTE(@COMPUTE(@NUMSTR(@GETENV ("RECORD", "FILESEQNO"))*100000000000)+@NUMSTR(@GETENV ("RECORD", "FILERBA")))
'BEFOREAFTERINDICATOR'

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

  • BEFORE (before image)

  • AFTER (after image)

'COMMITTIMESTAMP'

Returns the transaction timestamp (the time when the transaction committed) expressed in the format of YYYY-MM-DD HH:MI:SS.FFFFFF, for example: 

2011-01-24 17:08:59.000000
'LOGPOSITION'

Returns the position of the Extract process in the data source. (See the LOGRBA option.)

'LOGRBA'

LOGRBA and LOGPOSITION store details of the position in the data source of the record. For transactional log-based products, LOGRBA is the sequence number and LOGPOSITION is the relative byte address. However, these values will vary depending on the capture method and database type.

'OBJECTNAME' | 'TABLENAME'

Returns the table name or object name (if a non-table object).

'OPTYPE'

Returns the type of operation. Possible results are:

INSERT
UPDATE
DELETE
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.

'RECORDLENGTH'

Returns the record length in bytes.

'TRANSACTIONINDICATOR'

Returns the transaction indicator. The value corresponds to the TransInd field of the record header, which can be viewed with the Logdump utility.

Possible results are:

  • BEGIN (represents TransInD of 0, the first record of a transaction.)

  • MIDDLE (represents TransInD of 1, a record in the middle of a transaction.)

  • END (represents TransInD of 2, the last record of a transaction.)

  • WHOLE (represents TransInD of 3, the only record in a transaction.)

'GGFILEHEADER' , 'header_info'

Valid for Replicat only.

Use the GGFILEHEADER option of @GETENV to return attributes of an Oracle GoldenGate Extract file or trail file. These attributes are stored as tokens in the file header.

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', {'COMPATIBILITY'|'CHARSET'|'CREATETIMESTAMP'|
   'FILENAME'|'FILETYPE'|'FILESEQNO'|'FILESIZE'|'FIRSTRECCSN'|
   'LASTRECCSN'|'FIRSTRECIOTIME'|'LASTRECIOTIME'|'URI'|'URIHISTORY'|
   'GROUPNAME'|'DATASOURCE'|'GGMAJORVERSION'|'GGMINORVERSION'|
   'GGVERSIONSTRING'|'GGMAINTENANCELEVEL'|'GGBUGFIXLEVEL'|'GGBUILDNUMBER'|
   'HOSTNAME'|'OSVERSION'|'OSRELEASE'|'OSTYPE'|'HARDWARETYPE'|
   'DBNAME'|'DBINSTANCE'|'DBTYPE'|'DBCHARSET'|'DBMAJORVERSION'|
   'DBMINORVERSION'|'DBVERSIONSTRING'|'DBCLIENTCHARSET'|'DBCLIENTVERSIONSTRING'|
   'LASTCOMPLETECSN'|'LASTCOMPLETEXIDS'|'LASTCSN'|'LASTXID'|
   'LASTCSNTS'|'RECOVERYMODE'})
'COMPATIBILITY'

Returns the 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 from 0 or 6.

  • 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 backward compatibility to those Oracle GoldenGate versions.

  • 5 means that the trail file is of Oracle GoldenGate version 12.2 or later.

  • 6 means that the trail file is of Oracle GoldenGate version 12.3.0.1.

    This value keeps increasing as per the Oracle GoldenGate version depending on the trail file version.

'CHARSET'

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

WCP1252-1

'CREATETIMESTAMP'

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

'FILENAME'

Returns the name of the trail file. Can be an absolute or relative path, with a forward or backward slash depending on the file system.

'FILETYPE'

Returns a numerical value 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. The valid values are:

  • 0 - EXTFILE

  • 1 - EXTTRAIL

  • 2 - UNIFIED and EXTFILE

  • 3 - UNIFIED and EXTTRAIL

'FILESEQNO'

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

'FILESIZE'

Returns the 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'

Returns the commit sequence number (CSN) of the first record in the trail file.Value is NULL until the trail file is completed. For more information about the CSN, see Administering Oracle GoldenGate.

'LASTRECCSN'

Returns the commit sequence number (CSN) of the last record in the trail file.Value is NULL until the trail file is completed. For more information about the CSN, see Administering Oracle GoldenGate.

'FIRSTRECIOTIME'

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

'LASTRECIOTIME'

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

'RECOVERYMODE'

Returns recovery information for internal Oracle GoldenGate use. It is usually set to APPENDMODE.

'URI'

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

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

Where:

  • 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.

The following example shows where the trail was processed and by which process. This includes a history of previous runs.

sys1:home:oracle:v9.5:extora
'URIHISTORY'

Returns a 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.

'GROUPNAME'

Returns the name of the group that is associated with the Extract process that created the trail. The group name is the one that was supplied when the ADD EXTRACT command was issued.

'DATASOURCE'

Returns the data source that was read by the process as a number. The return value can be one of the following:

  • DS_EXTRACT_TRAILS: The source was an Oracle GoldenGate extract file, populated with change data. The return value is 0.

  • DS_DATABASE: The source was a direct select from database table written to a trail, used for SOURCEISTABLE-driven initial load. The return value is 2.

  • DS_TRAN_LOGS: The source was the database transaction log. The return value is 3.

  • DS_INITIAL_DATA_LOAD: The source was a direct select from database tables for an initial load. The return value is 4.

  • DS_VAM_EXTRACT: The source was a vendor access module (VAM). The return value is 5.

  • DS_VAM_TWO_PHASE_COMMIT: The source was a VAM trail. The return value is 6.

'GGMAJORVERSION'

Returns the major version of the Extract process that created the trail, expressed as an integer. For example, if a version is 1.2.3, it returns 1.

'GGMINORVERSION'

Returns the minor version of the Extract process that created the trail, expressed as an integer. For example, if a version is 1.2.3, it returns 2.

'GGVERSIONSTRING'

Returns the maintenance (or patch) level of the Extract process that created the trail, expressed as an integer. For example, if a version is 1.2.3, it returns 3.

'GGMAINTENANCELEVEL'

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

'GGBUGFIXLEVEL'

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

'GGBUILDNUMBER'

Returns the build number of the process.

'HOSTNAME'

Returns the DNS name of the machine where the Extract that wrote the trail is running. For example:

  • sysa

  • sysb

  • paris

  • hq25

'OSVERSION'

Returns the major version of the operating system of the machine 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'

Returns the release version of the operating system of the machine 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

'OSTYPE'

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

  • SunOS

  • Linux

  • Microsoft Windows

'HARDWARETYPE'

Returns the type of hardware of the machine where the Extract that wrote the trail is running. For example:

  • sun4u

  • x86_64

  • x86

'DBNAME'

Returns the name of the database, for example findb.

'DBINSTANCE'

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

'DBTYPE'

Returns the type of database that produced the data in the trail file. Can be one of:

DB2 UDB
DB2 ZOS
MSSQL
MYSQL
ORACLE
TERADATA
ODBC
'DBCHARSET'

Returns 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'

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

'DBMINORVERSION'

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

'DBVERSIONSTRING'

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

'DBCLIENTCHARSET'

Returns the character set that is used by the database client.

'DBCLIENTVERSIONSTRING'

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

'LASTCOMPLETECSN'

Returns recovery information for internal Oracle GoldenGate use.

'LASTCOMPLETEXIDS'

Returns recovery information for internal Oracle GoldenGate use.

'LASTCSN'

Returns recovery information for internal Oracle GoldenGate use.

'LASTXID'

Returns recovery information for internal Oracle GoldenGate use.

'LASTCSNTS'

Returns recovery information for internal Oracle GoldenGate use.

'RECORD' , 'location_info'

Valid for a data pump Extract or Replicat.

Use the RECORD option of @GETENV to return the location or Oracle rowid of a record in an Oracle GoldenGate trail file.

Syntax

@GETENV ('RECORD', {'TIMESTAMP_PRECISE'|'FILESEQNO'|'FILERBA'|'ROWID'|'RSN'|'TIMESTAMP'})
'TIMESTAMP_PRECISE'

Valid for a data pump, Extract, or Replicat.

The TIMESTAMP_PRECISE option returns the timestamp from year to microseconds. However, depending on the database, the value can be in milliseconds with 0 microseconds.

'FILESEQNO'

Returns the sequence number of the trail file without any leading zeros.

'FILERBA'

Returns the relative byte address of the record within the FILESEQNO file.

'ROWID'

(Valid for Oracle) Returns the row id of the record.

'RSN'

Returns the record sequence number within the transaction. This value does not always generate uniquely increasing values and should not be used to order operations. For ordering transactions or DML operations within a transaction, use the information outlined in MOS DOC ID 1340823.1.

'TIMESTAMP'

Returns the timestamp of the record.

Example:

REC-TIMESTAMP: 2017-10-31 06:21:07 REC-TIMESTAMP-PRECISE: 2017-10-31 06:21:07.478064

'DBENVIRONMENT' , 'database_info'

Valid for Extract and Replicat.

Use the DBENVIRONMENT option of @GETENV to return global environment information for a database.

Syntax

@GETENV ('DBENVIRONMENT', {'DBNAME'|'DBVERSION'|'DBUSER'|'SERVERNAME'})
'DBNAME'

Returns the database name.

'DBVERSION'

Returns the database version.

'DBUSER'

Returns the database login user. Note that SQL Server does not log the user ID.

'SERVERNAME'

Returns the name of the server.

'TRANSACTION' , 'transaction_info

Valid for Extract.

Use the TRANSACTION option of @GETENV to return information about a source transaction. This option is valid for the Extract process but not for pump Extract and Replicat.

Syntax

@GETENV ('TRANSACTION', {'TIMESTAMP_PRECISE'|'TRANSACTIONID'|'XID'|'CSN'|'TIMESTAMP'|'NAME'|
   'USERID'|'USERNAME'|'PLANNAME' | 'LOGBSN' | 'REDOTHREAD' | 'PROGRAMNAME' | 'CLIENTIDENTIFIER' | 'MACHINENAME' | 'USERNAME')

Note:

Do not use TIMETSAMP_PRECISE or TIMESTAMP for ordering operations. Instead use this value: @COMPUTE(@COMPUTE(@NUMSTR(@GETENV ("RECORD", "FILESEQNO"))*100000000000)+@NUMSTR(@GETENV ("RECORD", "FILERBA")))
'TIMESTAMP_PRECISE'
This option is valid for Extract. Use the TIMESTAMP_PRECISE returns the timestamp from year to microseconds. However, depending on the database, the value can be in milliseconds with 0 microseconds
'TRANSACTIONID' | 'XID'

Returns the transaction ID number. Either TRANSACTIONID or XID can be used. The transaction ID and the CSN are associated with the first record of every transaction and are stored as tokens in the trail record. For each transaction ID, there is an associated CSN. Transaction ID tokens have no zero-padding on any platform, because they never get evaluated as relative values. They only get evaluated for whether they match or do not match. Note that in the trail, the transaction ID token is shown as TRANID.

'CSN'

Returns the commit sequence number (CSN). The CSN is not zero-padded when returned for these databases: Oracle, DB2 LUW, and DB2 z/OS. For all other supported databases, the CSN is zero-padded.

Note that in the trail, the CSN token is shown as LOGCSN. See the TRANSACTIONID | XID environment value for additional information about the CSN token.

For more information about the CSN, see Administering Oracle GoldenGate.

'TIMESTAMP'

Returns the commit timestamp of the transaction.

'NAME'

Returns the transaction name, if available.

'USERID'

(Oracle) Returns the Oracle user ID of the database user that committed the last transaction. This is not valid for pump Extract and/or Replicat.

'USERNAME'

(Oracle) Returns the Oracle user name of the database user that committed the last transaction. This is not valid for pump Extract and/or Replicat.

'PLANNAME'

(DB2 z/OS) Returns the plan name under which the current transaction was originally executed. The plan name is included in the begin unit of recovery log record.

'LOGBSN'

Returns the begin sequence number (BSN) in the transaction log. The BSN is the native sequence number that identifies the beginning of the oldest uncommitted transaction that is held in Extract memory. For example, given an Oracle database, the BSN would be expressed as a system change number (SCN). The BSN corresponds to the current I/O checkpoint value of Extract. This value can be obtained from the trail by Replicat when @GETENV ('TRANSACTION', 'LOGBSN') is used. This value also can be obtained by using the INFO REPLICAT command with the DETAIL option. The purpose of obtaining the BSN from Replicat is to get a recovery point for Extract in the event that a system failure or file system corruption makes the Extract checkpoint file unusable. See Administering Oracle GoldenGate for more information about recovering the Extract position.

'REDOTHREAD'

Returns the thread number of a RAC node extract; on non-RAC node extracts the value is always 1. For data pump and Replicat, the thread id used by Extract capture of a RAC node is returned; on non-RAC, @GETENV() returns an error. Logdump shows the token, ORATHREADID, in the token section if the transaction is captured by Extract on a RAC node.

‘PROGRAMNAME’ 
Name of the program or application that started the transaction or session.
‘CLIENTIDENTIFIER’
Value set by using DBMS_SESSION_.set_identifier().
‘MACHINENAME’
Name of the host, machine, or server where database is running
‘USERNAME’
Database login user name.

Example:

DB2 zOS: 
TRANS-TIMESTAMP:         2017-10-31 06:21:07 
TRANS-TIMESTAMP-PRECISE: 2017-10-31 06:21:07.485792

'OSVARIABLE' , 'variable'

Valid for Extract and Replicat.

Use the OSVARIABLE option of @GETENV to return the string value of a specified operating-system environment variable.

Syntax

@GETENV ('OSVARIABLE', 'variable')
'variable'

The name of the variable. The search is an exact match of the supplied variable name. For example, the UNIX grep command would return all of the following variables, but @GETENV ('OSVARIABLE', 'HOME') would only return the value for HOME: 

ANT_HOME=/usr/local/ant
JAVA_HOME=/usr/java/j2sdk1.4.2_10
HOME=/home/judyd
ORACLE_HOME=/rdbms/oracle/ora1022i/64

The search is case-sensitive if the operating system supports case-sensitivity.

'TLFKEY' , SYSKEY, 'unique_key'

Valid for Extract and Replicat.

Use the TLFKEY option of @GETENV to associate a unique key with TLF/PTLF records in ACI's 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).

Syntax

@GETENV ('TLFKEY', SYSKEY, unique_key)
SYSKEY, unique_key

The NonStop node number of the source TLF/PTLF file. Do not enclose this syntax element in quotes.

Example:

GETENV ('TLFKEY', SYSKEY, 27)