@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.
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.
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.
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.
TABLENAME
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 Logdump Reference for Oracle GoldenGate.
RECORDLENGTH
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"
:
ProducerInfo: Information about the process that created the trail file
MachineInfo: Information about the local host of the trail file
DatabaseInfo: Information about the database that produced the data in the trail file
ContinuityInfo: Recovery information carried over from the previous trail file
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 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
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
Source Application Options
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.