4 Field Conversion Functions
-
Working with columns
-
Working with dates
-
Working with numbers and arithmetic expressions
-
Working with conditional statements
-
Working with character and numerical strings
-
Working with stored procedures
-
Returning error and lag information
Overview of Functions
This section provides an overview of Oracle GoldenGate functions for the NonStop platform.
Function Summaries
This section summarizes the Oracle GoldenGate functions for NonStop, based on their functionality. An alphabetized reference of the functions follows this section.
Working with Numbers and Arithmetic Expressions
Function | Description |
---|---|
COMPUTE |
Returns the result of an arithmetic expression. |
CONVERTFLOAT |
Converts Tandem float numbers from the HP NonStop to and from the IEEE format used for Windows and UNIX. |
Working with Conditional Statements
Function | Description |
---|---|
CASE |
Selects a value depending on a series of value tests. |
EVAL |
Selects a value depending on a series of independent tests. |
GETVAL |
Extracts parameters from a stored procedure as input to a |
IF |
Selects one of two values depending on whether a conditional statement returns |
Working with Character and Numeric Strings
Function | Description |
---|---|
BINARY |
Keeps source data in its original binary format in the target column when the source column is defined as a character column. |
HIGHVAL | LOWVAL |
Emulates the COBOL high and low value functions. Sets COBOL-type group level to either a high or low values when specified conditions are met. |
NUMBINNUMBIN |
Converts a binary string into a number (for example, a 48-bit Himalaya timestamp). |
NUMSTR |
Converts a string into a number. |
STRCAT |
Concatenates one or more strings. |
STRCMP |
Compares two strings and returns a result of less than, equal, or greater than. |
STREQ |
Compares two strings and returns a Boolean result of equal or not equal. |
STREXT |
Extracts selected characters from a string. |
STRFIND |
Finds the occurrence of a string within a string. |
STRLEN |
Returns the length of a string. |
STRLTRIM |
Trims leading spaces in a column. |
STRNCAT |
Concatenates one or more strings up to a limited number of characters per string. |
STRNCMP |
Compares two strings up to a certain number of characters. |
STRNUM |
Converts a number into a string, with justification and zero-fill options. |
STRRTRIM |
Trims trailing spaces in a column. |
STRSUB |
Substitutes one string for another within a column. |
STRTRIM |
Trims leading and trailing spaces in a column. |
STRUP |
Changes a string to uppercase. |
Environmental Information
Function | Description |
---|---|
GETENV |
Returns information about the Oracle GoldenGate environment. |
BINARY
Use the @BINARY
function when a source column referenced by a column-conversion function is defined as a character column but contains binary data that must remain binary on the target. By default, once a column is referenced by a column function, the data is converted (if necessary) to ASCII and assumed to be a null terminated string. The @BINARY()
function copies arbitrary binary data to the target column.
Syntax
@BINARY (column_name
)
Example
This example shows how the binary data in the source column ACCT_CREATE_DATE
will be copied to the target column ACCT_CHIEF_COMPLAINT
.
MAP \PROD.$DATA1.FINANCE.ACCTOLD, TARGET $DATA01.REPT.ACCT, COLMAP (USEDEFAULTS, ACCT-CHIEF-COMPLAINT = @IF (@NUMBIN (ACCT-CREATE-DATE) < 12345, "xxxxxx", @BINARY(ACCT-CHIEF-COMPLAINT) );
CASE
Allows the user to select a value depending on a series of value tests. There is no practical limit to the number of cases; however, for numerous cases, it is beneficial to list the most frequently encountered conditions first.
Syntax
@CASE (value
,test_value1
,test_result1
[,test_value2
,test_result2
] [, ...] [,default_result
])
Examples
- Example 1
-
The following returns "A car" if
PRODUCT_CODE
is"CAR
" and "A truck" ifPRODUCT_CODE
is"TRUCK
". In this case, ifPRODUCT_CODE
fits neither of the first two cases, aFIELD_MISSING
indication is returned.@CASE (PRODUCT_CODE, "CAR", "A car", "TRUCK", "A truck")
- Example 2
-
In this modified case, assuming
PRODUCT_CODE
is neither"CAR
" nor"TRUCK
","A vehicle
" is returned.@CASE (PRODUCT_CODE, "CAR", "A car", "TRUCK", "A truck", "A vehicle")
COLTEST
Use COLTEST
to perform conditional calculations. COLTEST
can check for one or more of the following column conditions and returns TRUE
if one of the following column conditions are met.
-
PRESENT
, which indicates a column is present in the source record and not null. In a compressed record, columns may be missing, but this not the same as null. -
NULL
, indicating the column is present andNULL
. -
MISSING
, indicating that the column is not present. -
INVALID
, indicating the column is present but contains invalid data. For example, aPIC 9(3)
field that contains spaces yields anINVALID
condition.
Syntax
@COLTEST (source_field
,test_item
[,test_item
] [, ...])
Examples
- Example 1
-
This example shows how you can calculate the value of a
HIGH_SALARY
column only if theSALARY
field in the source record is both present and greater than a certain number. Set up a test condition with the@IF
function to return the result ofSALARY
when part of the current record and exceeding250000
, otherwise returnNULL
:HIGH_SALARY = @IF(@COLTEST(BASE_SALARY, PRESENT) AND BASE_SALARY > 250000, BASE_SALARY, @COLSTAT(NULL))
In this example, the condition
BASE_SALARY > 250000
is evaluated only whenSALARY
is present in the source record and not null. If the presence of the column was not tested first, the column would not have been mapped, because the result would have been missing. - Example 2
-
In the following example, 0 is returned when
AMT
field is missing or invalid, otherwiseAMT
is returned.AMOUNT = @IF (@COLTEST (AMT, MISSING, INVALID), 0, AMT)
COMPUTE
@COMPUTE
returns the value of an arithmetic expression to a target column. The value returned from the function is in the form of a string.
You can omit @COMPUTE
when returning the value of an arithmetic expression to another function.
Syntax
@COMPUTE (value
operator
value
)
CONVERTFLOAT
@CONVERTFLOAT
converts HP NonStop Tandem (TDM) float numbers to Windows and UNIX Institute of Electrical and Electronics Engineers (IEEE) format or from IEEE float format to TDM float format.
The @CONVERTFLOAT
function always converts if you map the column; it does not check whether it is a 32 or 64 bit float data type. However, it is not necessary or wise to use @CONVERTFLOAT
to map 32 bit float columns for conversion to IEEE, because Oracle GoldenGate for Windows and UNIX automatically does this conversion.
Note:
To use @CONVERTFLOAT
, the Extract and Replicat must be native objects; not TNS.
Syntax
@CONVERTFLOAT (col_name
, (TOIEEE | TOTDM))
Example
COLMAPnum01
= @CONVERTFLOAT (num01
, TOIEEE)
DATE
@DATE
returns dates and times in a variety of formats to the target column based on the format passed into the source. @DATE
converts virtually any type of input into a valid SQL date. @DATE
can also be used to extract portions of a date field, or to compute a numeric timestamp field based on a date.
Syntax
@DATE ("output_descriptor
", "input_descriptor
",source_field
[, "input_descriptor
",source_field
] [, ...])
-
output_descriptor
-
A string containing date descriptors and optional literal values. For example, the descriptor
YYYY
corresponds to a four-digit year, the descriptorMI
describes minutes, while spaces, colons or other literals are output as is. See "Date Descriptors" for descriptions. -
input_descriptor
-
A string containing a series of date descriptors and optional literal values. For example, the descriptor
YYYY
corresponds to a four-digit year, the descriptorMI
describes minutes. Date descriptors are strung together to describe the field or column that follows in the next parameter. See "Date Descriptors" for descriptions. -
source_field
-
The name of a source field supplying the preceding input.
Date Descriptors
Descriptor | Description |
---|---|
CC |
Century |
YY |
Two-digit year |
YYYY |
Four-digit year |
MM |
Numeric month |
MMM |
Alphanumeric month, such as |
DD |
Numeric day of month |
DDD |
Numeric day of the year, such as |
DOW0 |
Numeric day of the week where Sunday = |
DOW1 |
Numeric day of the week where Sunday = |
DOWA |
Alphanumeric day of the week, such as |
HH |
Hour |
MI |
Minute |
SS |
Seconds |
JTSLCT |
Use for a Julian timestamp that is already local time, or to keep local time when converting to a Julian timestamp. An example of a 48-bit NonStop to 64-bit Julian for LCT to LCT time is: date = @date ("JTSLCT", "TTS", @numbin(date)); An example of a NonStop 64-bit Julian for LCT to date type in Oracle: date = @date ("YYY-MM-DD HH:MI:SS", "JTSLCT", date); |
JTSGMT |
Julian timestamp, the same as |
JTS |
Julian timestamp. For more information see "Using JUL and JTS". |
JUL |
Julian day |
TTS |
NonStop 48-bit timestamp |
PHAMIS |
PHAMIS application date format |
FFFFFF |
Fraction (up to microseconds) |
STRATUS |
STRATUS application timestamp that returns microseconds since 1/1/1980. |
CDATE |
C timestamp in seconds since the Epoch. |
Using JUL and JTS
JUL
and JTS
produce numbers you can use in numeric expressions.
Example
The following expression produces the time at which an order is filled.
ORDER_FILLED = @DATE ("YYYY-MM-DD:HH:MI:SS", "JTS", @DATE ("JTS", "YYMMDDHHMISS", ORDER-TAKEN-TIME) + ORDER-MINUTES * 60 * 1000000)
The above expression changes ORDER-TAKEN-TIME
into a Julian timestamp, then adds ORDER-MINUTES
converted into microseconds to this timestamp (the inner @DATE
expression). This expression is passed back as a new Julian timestamp to the outer @DATE
expression, which converts it back to a more readable date and time.
Working with Date Strings
Descriptor string "YYYYMMDD
" indicates that the following numeric or character field contains (in order) a four-digit year (YYYY
), month (MM
), and day (DD
).
Descriptor string "DD/MM/YY
" indicates that the field contains the day, a slash, the month, a slash, and the two digit year.
Converting Two-digit Years into Four-digit Values
In an instance where a two-digit year is supplied, but a four-digit year is required in the output, several options exist.
-
A century is hard-coded, as in the
"CC
",19
or"CC
",20
. -
The
@IF
function is used, as in"CC
",@IF
(YY > 70, 19, 20)
. This causes century to be set to19
when year is greater than70
, otherwise20
. -
The system calculates the century automatically. If the year is less than
50
, the system calculates a century of20
; otherwise, the century calculates to19
.
Example
The following show some ways to use date conversions.
-
Converting year, month and day fields into a SQL date.
DATE_COL = @DATE ("YYYY-MM-DD", "YY", date1.yr, "MM", date1.mm, "DD", date1.dd)
-
Converting the date at the group level (assuming year, month, and day are part of
date1
).DATE_COL = @DATE ("YYYY-MM-DD", "YYMMDD", date1)
-
Converting to a date and time, defaulting seconds to zero.
DATE_COL = @DATE ("YYYY-MM-DD:HH:MI:00", "YYMMDD", date1, "HHMI", time1)
-
Converting a numeric field stored as
YYYYMMDDHHMISS
to a SQL date.DATETIME_COL = @DATE ("YYYY-MM-DD:HH:MI:SS", "YYYYMMDDHHMISS", NUMERIC-DATE)
-
Converting a numeric field stored as
YYYYMMDDHHMISS
to a Julian timestamp.JULIAN_TS_COL = @DATE ("JTS", "YYYYMMDDHHMISS", NUMERIC-DATE)
-
Converting a Julian timestamp field to two columns: a datetime field in the format
YYYYMMDDHHMISS
, and a fraction field, which holds the microseconds portion of the timestamp.DATETIME_COL = @DATE ("YYYY-MM-DD:HH:MI:SS", "JTS", JTS-FIELD), FRACTION_COL = @DATE ("FFFFFF", "JTS", JTS-FIELD)
DATEDIFF
@DATEDIFF
calculates the difference between two dates or datetimes, in days or seconds.
Syntax
@DATEDIFF ("difference
",date
,date
)
Examples
- Example 1
-
To calculate the number of days since the beginning of the year
2000
:differential = (@DATEDIFF("DD","2000-01-01",@DATENOW()))
- Example 2
-
To calculate the actual day of the year in the above example (
@DATEDIFF
returns0
for2000-01-01
):todays_day = @COMPUTE(@DATEDIFF("DD","2000-01-01",@DATENOW()))+1)
EVAL
Allows the user to select a value depending on a series of independent tests. There is no practical limit to the number of conditions. If the number of cases is large, it is beneficial to list the most frequently encountered conditions first.
Syntax
@EVAL (condition1
,result1
[,condition2
,result2
] [, ...] [,default_result
])
Examples
- Example 1
-
In the following example, if
AMOUNT
is greater than 10000,"high amount
" is returned. IfAMOUNT
is greater than5000
(and less than or equal to10000
),"somewhat high
" is returned (unless the prior condition was satisfied). If neither condition is satisfied, aCOLUMN_MISSING
indicator is returned because a default result is not specified.AMOUNT_DESC = @EVAL (AMOUNT > 10000, "high amount", AMOUNT > 5000, "somewhat high")
- Example 2
-
The following is a modification of the preceding example. It returns the same results, except that a default value is specified, and a result of
"lower
" is returned ifAMOUNT
is less than or equal to5000
.@EVAL (AMOUNT > 10000, "high amount", AMOUNT > 5000, "somewhat high", "lower")
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.
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 Using the Logdump Utility. -
-
RECORDLENGTH
-
TRANSACTIONINDICATOR
-
Returns the transaction indicator. Possible results are:
-
BEGIN
- Returned when the record headerTransInD
is0
indicating the first statement in the transaction. -
MIDDLE
- Returned when the headerTransInD
is1
indicating a statement in the middle of the transaction. -
END
- Returned when theTransInD
is2
indicating the last statement in the transactions -
WHOLE
- Returned when theTransInD
is3
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. Iffalse
, theSeqNum
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
returns26
. -
"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 forSOURCEISTABLE
-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
and255)
.
This option is valid for the Extract and Replicat processes.
GETVAL
Use the @
GETVAL
function to extract values from a query so that they can be used as input to a FILTER
or COLMAP
clause of a MAP
or TABLE
statement.
Whether or not a parameter value can be extracted with @GETVAL
depends upon the following:
-
Whether or not the query executed successfully.
-
Whether or not the query results have expired.
Handling Missing Column Values
When a value cannot be extracted, the @GETVAL
function results in a "column missing" condition. Typically, this occurs for update operations if the database only logs values for columns that were changed.
Usually this means that the column cannot be mapped. To test for missing column values, use the @COLTEST
function to test the result of @GETVAL
, and then map an alternative value for the column to compensate for missing values, if desired. Or, to ensure that column values are available, you can use the FETCHCOLS
or FETCHCOLSEXCEPT
option of the TABLE
or MAP
parameter to fetch the values from the database if they are not present in the log. (Enabling supplemental logging for the necessary columns also would work.)
Syntax
@GETVAL (name
.parameter
)
-
name
-
The name of the query. When using
SQLEXEC
to execute the query, the valid value is the logical name specified with theID
option of theSQLEXEC
clause.ID
is a requiredSQLEXEC
argument for queries. -
parameter
-
Valid values are one of the following.
-
The name of the parameter in the query from which the data will be extracted and passed to the column map.
-
RETURN_VALUE
, if extracting values returned by a query.
-
Example
The following example enables the COMPUTE
statements to call two stored procedures selectbal
and selecttran
by referencing the logical name within the @GETVAL
function and referring appropriately to the results of each.
MAP \NY.$DATA1.GGSDAT.ACCTTR, TARGET \NY.$DATA1.GGSDAT.ACCTBL SQLEXEC (ID selecttran, ON UPDATES, ON INSERTS, QUERY " select tran_type, tran_amt from $DATA1.GGSDAT.ACCTTR " " where ACCT_NUM = ?P1 ", PARAMS (P1 = ACCT_NUM), ERROR REPORT) SQLEXEC (ID selectbal, ON UPDATES, ON INSERTS, QUERY " select acct_balance from $DATA1.GGSDAT.ACCTBL " " where ACCT_NUM = ?P1 ", PARAMS (P1 = ACCT_NUM), ERROR REPORT) COLMAP (USEDEFAULTS, acct_balance = @IF (@GETVAL (selecttran.tran_type) = 1 @COMPUTE (@GETVAL (selectbal.acct_balance) - selecttran.tran_amt), @COMPUTE (@GETVAL (selectbal.acct_balance) + selecttran.tran_amt)) );
HIGHVAL | LOWVAL
Use the @
HIGHVAL
and @LOWVAL
functions when you need to generate a value, but you want to constrain it within an upper or lower limit. These functions emulate the COBOL functions of the same name.
Use @HIGHVAL
and @LOWVAL
only with string and binary data types. Using them with decimal or date data types, or with SQLEXEC, can cause errors.
Note:
Invalid maps to incorrect type will result in a mapping error 222.
Syntax
@HIGHVAL ([length
]) | @LOWVAL ([length
])
Examples
- Example 1
-
This example sets COBOL-type group level to low values if key is less than
50
, and it sets COBOL-type group level to high values if the key is greater than50
.MAP \PROD.$DATA.MASTER.CUSTOMER, TARGET \BACK.$DATA.MASTER.CUSTOMER, DEF CUSTOMER-REC, TARGETDEF NEW_CUSTOMER_REC, COLMAP (USEDEFAULTS, CUST-KEY = CUST-KEY,     GROUP-LEVEL = @IF (CUST-KEY < 50,@LOWVAL(), @HIGHVAL()));
- Example 2
-
The following example assumes that the size of the
GROUP-LEVEL
field is 5 bytes.Function statement Results GROUP-LEVEL = @HIGHVAL ()
{0xFF, 0xFF, 0xFF, 0xFF, 0xFF}
GROUP-LEVEL = @LOWVAL ()
{0x00, 0x00, 0x00, 0x00, 0x00}
GROUP-LEVEL = @HIGHVAL (3)
{0xFF, 0xFF, 0xFF}
GROUP-LEVEL = @LOWVAL (3)
{0x00, 0x00, 0x00}
IF
@IF
returns one of two values, based upon a condition.
Syntax
@IF (conditional_expression
,nonzero_value
,zero_value
)
Examples
- Example 1
-
The following returns
AMT
only ifAMT
is greater than zero, otherwise zero is returned.AMOUNT_COL = @IF (AMT <= 0, 0, AMT)
- Example 2
-
The following returns
WEST
ifSTATE
isCA
,AZ
orNV
, otherwise returnsEAST
.REGION = @IF (@VALONEOF (STATE, "CA", "AZ", "NV"), "WEST", "EAST")
- Example 3
-
The following returns
NULL
unless bothPRICE
andQUANTITY
are greater than zero.ORDER_TOTAL = @IF (PRICE > 0 AND QUANTITY > 0, PRICE * QUANTITY, @COLSTAT(NULL)
- Example 4
-
The following returns
NULL
unless bothPRICE
andQUANTITY
are greater than zero.COLSTAT(NULL)
creates a null value in the target column.ORDER_TOTAL = @IF (PRICE > 0 AND QUANTITY > 0, PRICE * QUANTITY, @COLSTAT(NULL)
- Example 5
-
The following returns
NULL
if eitherPRICE
orQUANTITY
isNULL
. When any columns in an expression areNULL
, this is the default action.ORDER_TOTAL = @IF (@COLTEST (PRICE, NULL) OR @COLTEST(QUANTITY, NULL), @COLSTAT(NULL), PRICE * QUANTITY)
NUMBIN
@NUMBIN
turns a binary string of eight or fewer bytes, into a number. Use this when the source DDL defines a byte stream that is actually a number as a string.
Syntax
@NUMBIN (source_column
)
RANGE
The @RANGE
function, used within the FILTER
option, helps divide workload into multiple, randomly distributed groups of data, while guaranteeing that the same row will always be processed by the same program. For example, @RANGE
can be used to split the workload by different key ranges for a heavily accessed table into different Replicat processes.
The user specifies both a range that applies to the current process, and the total number of ranges (generally the number of processes), and optionally a list of column names to use to calculate the range against.
@RANGE
computes a hash value of all the columns specified, or if no columns are specified, the primary key columns of the source table. A remainder of the hash and the total number of ranges is compared with the ownership range to determine whether or not @RANGE
produces true or false results. Oracle GoldenGate adjusts the total number of ranges so that they are evenly distributed.
Note:
Calculating ranges in an Extract parameter file is more efficient that doing so in a Replicat parameter file. Calculating ranges on the target requires Replicat to read all of the Oracle GoldenGate trail data to find the data meeting each range specification.
Note:
Using the @RANGE
function within a FILTER
provides different capabilities, such as specifying columns, than using the RANGE
option of FILE
or MAP
. And both of these are different than the RANGE
option of ALTINPUT
.
Syntax
@RANGE (range
,total_ranges
[,column
] [,column
] [, ...]))
-
range
-
The range assigned to the specified process or trail. Valid values are
1
,2
,3
, and so forth, with the maximum value being the value defined bytotal_ranges
. -
total_ranges
-
The total number of ranges allocated. For example, to divide data into three groups, use the value
3
. -
column
-
The name of a column, or columns, on which to base the range allocation. This argument is optional. If not used, Oracle GoldenGate calculates ranges based on the table's primary key.
Examples
- Example 1
-
In the following example, the workload is split into three ranges, between three Replicat processes, based on the
ID
column of theSRCTAB
table.Replicat parameter file #1 contains:
MAP $PRODSRC.PRODMSTR.SRCTAB, TARGET $PROD.MASTER.TARGTAB, FILTER (@RANGE(1,3, ID));
Replicat parameter file #2 contains:
MAP $PRODSRC.PRODMSTR.SRCTAB, TARGET $PROD.MASTER.TARGTAB, FILTER (@RANGE(2,3, ID);
Replicat parameter file #3 contains:
MAP $PRODSRC.PRODMSTR.SRCTAB, TARGET $PROD.MASTER.TARGTAB, FILTER (@RANGE(3,3, ID));
- Example 2
-
In the following example, the
TABLE
parameter in the Extract parameter file splits the processing load into two trails. Since no columns were defined on which to base the range calculation, Oracle GoldenGate will use the primary key columns.RMTTRAIL $DATA.GGSDAT.AA TABLE ACCOUNT, FILTER (@RANGE (1, 2)); RMTTRAIL $DATA.GGSDAT.BB TABLE ACCOUNT, FILTER (@RANGE (2, 2));
- Example 3
-
In the following example, the
ORDMASTR
table has a key ofORDERID
and theORDDETL
table has a key ofITEMNUM
. Because the keyORDERID
establishes relativity, it is used in@RANGE
filters for both tables to preserve referential integrity. The load is split into two ranges.(Parameter file #1)
MAP $PRODSRC.PRODMSTR.ORDMASTR, TARGET $PROD.MASTER.ORDMASTR, FILTER (@RANGE (1, 2, ORDERID)); MAP $PRODSRC.PRODMSTR.ORDDETL, TARGET $PROD.MASTER.ORDDETL, FILTER (@RANGE (1, 2, ORDERID));
(Parameter file #2)
MAP $PRODSRC.PRODMSTR.ORDMASTR, TARGET $PROD.MASTER.ORDMASTR, FILTER (@RANGE (2, 2, ORDERID)); MAP $PRODSRC.PRODMSTR.ORDDETL, TARGET $PROD.MASTER.ORDDETL, FILTER (@RANGE (2, 2, ORDERID));
STRCAT
Use the @STRCAT
function to concatenate one or more strings. The string can be either the name of a column or a literal string. Enclose literals within quotes.
Syntax
result = @STRCAT (string1
,string2
[,...])
Example
The following creates a phone number from three fields and includes the constant values.
PHONE_NO = @STRCAT ("(", AREA_CODE,")",PREFIX,"-", PHONE)
STRCMP
Use the @STRCMP
function to compare two character columns or literal strings. Enclose literals within quotes.
@STRCMP
returns the following:
-
-1
if the first string is less than the second. -
0
if the strings are equal. -
1
if the first string is greater than the second.
Trailing spaces are truncated before comparing the strings.
Syntax
@STRCMP (string1
,string2
)
Example
The following example compares two literal strings and returns 1
because the first string is greater than the second.
@STRCMP ("JOHNSON", "JONES")
STREQ
Use @STREQ
to determine if two strings are equal. The result is either:
-
0
= strings are not equal -
1
= strings are equal
Syntax
result = @STREQ (string1
,string2
)
Example
The following filter clause compares the value of the variable REGION
to the literal value "EAST
". If Region = EAST
, the record passes the filter.
FILTER (@STREQ (REGION, "EAST"))
STREXT
Use the @STREXT
function to extract a portion of a string.
Syntax
result = @STREXT (string
,begin_position
,end_position
)
-
string
-
The string from which to extract. The string can be either the name of a column or a literal string. Enclose literals within quotes.
-
begin_position
-
The character position at which to begin extracting.
-
end_position
-
The character position at which to end extracting. The end position is included in the extraction.
Example
The following example uses three @STREXT
functions to extract a phone number into three different columns.
AREA_CODE = @STREXT (PHONE, 1, 3), PREFIX = @STREXT (PHONE, 4, 6), PHONE_NO = @STREXT (PHONE, 7, 10)
STRFIND
Use the @STRFIND
function to determine the position of a string within a string column or else return zero if not found. Optionally, @STRFIND
can accept a starting position to search within the string.
Syntax
result = @STRFIND (string
, "search_string
" [,begin_position
])
-
string
-
The string from which to extract. The string can be either the name of a column or a literal string. Enclose literals within quotes.
-
"search_string"
-
The string for which to search within the string. Enclose the search string within quotes.
-
begin_position
-
The character position at which to begin searching.
Example
Assuming the string for ACCT
is ABC123ABC
, the following are possible results.
result = @STRFIND (ACCT, "23") returns 5. result = @STRFIND (ACCT, "ZZ") returns 0. result = @STRFIND (ACCT, "ABC", 2) returns 7.
STRNCAT
Use the @STRNCAT
function to concatenate one or more strings to a maximum length.
Syntax
result = @STRNCAT (string
,max_length
[,string
,max_length
, ...])
Example
The following concatenates two strings and results in "ABC123
."
PHONE_NO = @STRNCAT ("ABCDEF", 3, "123", 3)
STRNCMP
Use @STRNCMP
to compare two strings, up to a specified number of characters in each string. Trailing spaces are truncated before comparing the strings. The compare returns:
-
0 if the strings are equal.
-
-1 if the first string is less than the second string.
-
1 if the first string is greater than the second string.
Syntax
@STRNCMP (compare_data
,compare_data
,max_compare_length
)
Example
This example returns 0
, since the first two characters of both strings are equal.
result = @STRNCMP ("JOHNSON", "JONES", 2)
STRNUM
Use @STRNUM
to convert a number into a string and specify the output format and padding.
Syntax
@STRNUM (field
, {LEFT | LEFTSPACE | RIGHT | RIGHTZERO} [length
])
-
field
-
The name of the source numeric field.
-
LEFT
-
Left justify, fill the rest of the target column with spaces
-
LEFTSPACE
-
Left justify, fill the rest of the target column.
-
RIGHT
-
Right justify, fill with spaces
-
RIGHTZERO
-
Right justify, fill the rest of the target column with zeros
-
length
-
Specifies the output length, when any of the options are used that specify padding (all but
LEFT
).
Example
If field NUM
has the value 15 and the target column CHAR1
is a maximum of 5 characters, the following examples show the different types of results obtained with formatting options.
Function statement | Results (- denotes a space) |
---|---|
CHAR1 = @STRNUM (NUM, LEFT) |
15 |
CHAR1 = @STRNUM (NUM, LEFTSPACE) |
15--- |
CHAR1 = @STRNUM (NUM, RIGHTZERO) |
00015 |
CHAR1 = @STRNUM (NUM, RIGHT) |
---15 |
If an output length
of 4 is specified in the preceding example, the following shows the different types of results.
Function statement | Results (- denotes a space) |
---|---|
CHAR1 = @STRNUM (NUM, LEFTSPACE, 4) |
15-- |
CHAR1 = @STRNUM (NUM, RIGHTZERO, 4) |
0015 |
CHAR1 = @STRNUM (NUM, RIGHT, 4) |
--15 |
STRSUB
Use STRSUB
to substitute one string within another string field or constant.
Syntax
@STRSUB (source_string_or_col
,search_string
,substitute_string
, ...)
Examples
- Example 1
-
For this example, the source string is
"123ABC123"
. The value"123"
is to be replaced with"xx"
. The result isxxABCxx
.result = @STRSUB ("123ABC123", "123", "xx")
- Example 2
-
For this example, the source string is "
123ABC123
". The value "A
" is to be replaced with "z
""1
" is replaced with"0
". The result is023zBC023
.result = @STRSUB ("123ABC123", "A", "z", "1", "0")
TOKEN
Use the @TOKEN
function to retrieve data that is stored in the user token area of the Oracle GoldenGate trail record header. Tokens are defined in the Extract parameter file by means of the TOKENS
clause of the FILE
or TABLE
parameter. The token data can be mapped to a target column by means of a COLMAP
clause or used within a SQLEXEC statement or Oracle GoldenGate macro or user exit.
VALONEOF
Use @VALONEOF
to compare a field or string to a list of values. If the field is in the list, 1
is returned, otherwise 0
is returned.
Syntax
@VALONEOF (expression
,value
[,value
] [, ...])
Example
If STATE
is CA
or NY
, this expression returns "COAST
".
@IF (@VALONEOF (STATE, "CA", "NY"), "COAST", "MIDDLE")