Field Conversion Functions

4 Field Conversion Functions

Learn how to use Oracle GoldenGate field conversion functions. These functions allow you to manipulate numbers, strings and source columns or field values into the appropriate format for target columns.

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

4.1 Overview of Functions

This section provides an overview of Oracle GoldenGate functions for the NonStop platform.

4.1.1 Function Syntax

Field conversion functions use the following general syntax:

@FUNCTION (expression)

@FUNCTION: The function name, such as @DATE or @IF.
(expression): The operations for the function to perform. The operations depend on the function.

4.1.2 Function Summaries

This section summarizes the Oracle GoldenGate functions for NonStop, based on their functionality. An alphabetized reference of the functions follows this section.

4.1.2.1 Working with Columns

Function	Description
COLSTAT	Returns whether a column is missing, `NULL` or an invalid value.
COLTEST	Tests whether a column is present in a record, missing, `NULL` or an invalid value.
VALONEOF	Returns `TRUE` if a column contains one of a list of values.

4.1.2.2 Working with Dates

Function	Description
DATE	Returns a date from a variety of sources in a variety of output formats.
DATEDIFF	Returns the difference between two dates or datetimes, in days or seconds
DATENOW	Returns the current date and time.

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

4.1.2.4 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 `FILTER` or `COLMAP` clause.
IF	Selects one of two values depending on whether a conditional statement returns `TRUE` or `FALSE`.

4.1.2.5 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.
NUMBIN NUMBIN	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.

4.1.2.6 Environmental Information

Function	Description
GETENV	Returns information about the Oracle GoldenGate environment.

4.2 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)          
);

4.3 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])

value: The column you are testing values for.
test_value1: The value to test against the value you are reading.
test_result1: The result to return.
default_result: The result returned when test_values are not entered.

Examples

Example 1

The following returns "A car" if PRODUCT_CODE is "CAR" and "A truck" if PRODUCT_CODE is "TRUCK". In this case, if PRODUCT_CODE fits neither of the first two cases, a FIELD_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")

4.4 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 and NULL.
MISSING, indicating that the column is not present.
INVALID, indicating the column is present but contains invalid data. For example, a PIC 9(3) field that contains spaces yields an INVALID condition.

Syntax

@COLTEST (source_field, test_item [, test_item] [, ...])

source_field: The name of the field or column that is the source of the data being tested.
test_item: One of: PRESENT, MISSING, INVALID, or NULL.

Examples

Example 1

This example shows how you can calculate the value of a HIGH_SALARY column only if the SALARY 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 of SALARY when part of the current record and exceeding 250000, otherwise return NULL:

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 when SALARY 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, otherwise AMT is returned.

AMOUNT = @IF (@COLTEST (AMT, MISSING, INVALID), 0, AMT)

4.5 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)

value: One or more values on which you are performing calculations.
operator: A valid arithmetic or logical operator.

Examples

Example 1

This example adds AMT and AMT2 and returns the total to AMOUNT_TOTAL.

AMOUNT_TOTAL = @COMPUTE (AMT + AMT2)

Example 2

This example is invalid because the expression is not enclosed in parentheses.

AMOUNT_TOTAL = AMT + AMT2

4.6 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))

col_name: The name of the column containing the float numbers to be converted.
TOIEEE | TOTDM: The format to which to convert; IEEE or TDM.

Example

COLMAP num01 = @CONVERTFLOAT (num01, TOIEEE)

4.7 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 descriptor MI 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 descriptor MI 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 `APR`, `OCT`
DD	Numeric day of month
DDD	Numeric day of the year, such as `001`, or `365`
DOW0	Numeric day of the week where Sunday = `0.`
DOW1	Numeric day of the week where Sunday = `1`.
DOWA	Alphanumeric day of the week, such as `SUN`, `MON`, `TUE`
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`
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). This is valid for both source as well as target details.
FFFFFFFFFFFF	Fraction (up to picoseconds) valid for source detail only.
NNN	Fraction for nanoseconds positions.
PPP	Fraction for picoseconds positions.
STRATUS	STRATUS application timestamp that returns microseconds since 1/1/1980.
CDATE	C timestamp in seconds since the Epoch.
TZ	Timezone

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 to 19 when year is greater than 70, otherwise 20.
The system calculates the century automatically. If the year is less than 50, the system calculates a century of 20; otherwise, the century calculates to 19.

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)
```

Example: Working with an OpenSys timestamps having fractional positions greater than 6 or with time zone.

This example shows how to map a DB2 TIMESTAMP (12) with TIME ZONE to DATETIME fields.

Target columns
DATE12 DATETIME YEAR TO FRACTION(6) DEFAULT NULL, 
DATE12_F DATETIME FRACTION TO FRACTION(6) DEFAULT NULL,
DATE12_TZ INTERVAL HOUR(2) TO MINUTE DEFAULT NULL

date12 = @date ("YYYY-MM-DD:HH:MI:SS.FFFFFF",
                "YYYY-MMDD:HH:MI:SS.FFFFFFNNNPPP", date12),

date12_f = @date ("NNNPPP", "YYYY-MM-DD:HH:MI:SS.FFFFFFNNNPPP", date12),
date12_tz = @date ("TZ", "YYYY-MM-DD:HH:MI:SS.FFFFFFNNNPPP TZ", date12),

4.8 DATEDIFF

@DATEDIFF calculates the difference between two dates or datetimes, in days or seconds.

Syntax

@DATEDIFF ("difference", date, date)

"difference"

The difference between the specified dates. Valid values can be:

DD to compute the difference in days.
SS to compute the difference in seconds.

date

A string in the format of YYYY-MM-DD[*HH:MI[:SS]], where * can be a colon (:) or a blank space.

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 returns 0 for 2000-01-01):

todays_day = @COMPUTE(@DATEDIFF("DD","2000-01-01",@DATENOW()))+1)

4.9 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])

condition: A conditional test using standard conditional operators.
result: A value or string to return based on the results of the conditional test. Enclose literals within double quotes.
default_result: A default result to return if none of the conditions is satisfied. A default result is optional.

Examples

Example 1

In the following example, if AMOUNT is greater than 10000, "high amount" is returned. If AMOUNT is greater than 5000 (and less than or equal to 10000), "somewhat high" is returned (unless the prior condition was satisfied). If neither condition is satisfied, a COLUMN_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 if AMOUNT is less than or equal to 5000.

@EVAL (AMOUNT > 10000, "high amount", AMOUNT > 5000, "somewhat high", "lower")

4.10 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)

4.11 GETTLFTOKEN

Use @GETTLFTOKEN() to map a specific TLF/PTLF record token to a fixed format part of the output buffer. This removes the requirement of a User Exit to accomplish the same function.

Syntax

@GETTLFTOKEN ("value", length, ["NOWARN"])

"value"

Token ID

length

Length of the output TKN DDL structure.

["NOWARN"]

Option NOWARN will hide warnings, such as :

The target field being smaller than the source and a truncation is occurring.
The length provided for the USER-DATA field (QZ Token) does match the actual length.
The default is to display warnings to the report file.

Example

TKNB0 = @GETTLFTOKEN ("B0", 448),

For more information, see Detokenizing Base24 (Classic) TLF/PTLF Records with Oracle GoldenGate Mapping in the Administering Oracle GoldenGate for HP NonStop guide.

4.12 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 the ID option of the SQLEXEC clause. ID is a required SQLEXEC 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))
);

4.13 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])

length: Optional. Specifies the binary output length in bytes. The maximum value of length is the length of the target column.

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 than 50.

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}

4.14 IF

@IF returns one of two values, based upon a condition.

Syntax

@IF (conditional_expression, nonzero_value, zero_value)

conditional_expression: The conditional expression.
non-zero_value: The value if the expression is non-zero. A non-zero result is considered TRUE.
zero_value: The value if the expression is zero. A zero result is considered FALSE.

Examples

Example 1

The following returns AMT only if AMT is greater than zero, otherwise zero is returned.

AMOUNT_COL = @IF (AMT <= 0, 0, AMT)

Example 2

The following returns WEST if STATE is CA, AZ or NV, otherwise returns EAST.

REGION = @IF (@VALONEOF (STATE, "CA", "AZ", "NV"), "WEST", "EAST")

Example 3

The following returns NULL unless both PRICE and QUANTITY are greater than zero.

ORDER_TOTAL = @IF (PRICE > 0 AND QUANTITY > 0, PRICE * QUANTITY, @COLSTAT(NULL)

Example 4

The following returns NULL unless both PRICE and QUANTITY 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 either PRICE or QUANTITY is NULL. When any columns in an expression are NULL, this is the default action.

ORDER_TOTAL = @IF (@COLTEST (PRICE, NULL) OR @COLTEST(QUANTITY, NULL), @COLSTAT(NULL), PRICE * QUANTITY)

4.15 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)

4.16 NUMSTR

@NUMSTR converts a string (character) field or value into a number. Use this to map a string field into a number, or to use a string field that contains only numbers in an arithmetic expression.

Syntax

@NUMSTR (convert_field)

convert_field: A character column or a literal string.

4.17 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 by total_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 the SRCTAB 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 of ORDERID and the ORDDETL table has a key of ITEMNUM. Because the key ORDERID 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));

4.18 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 [,...])

string1: The first string to be concatenated.
string2: The second string to be concatenated.

Example

The following creates a phone number from three fields and includes the constant values.

PHONE_NO = @STRCAT ("(", AREA_CODE,")",PREFIX,"-", PHONE)

4.19 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)

string1: The first column or literal string to be compared.
string2: The second column or literal string to be compared.

Example

The following example compares two literal strings and returns 1 because the first string is greater than the second.

@STRCMP ("JOHNSON", "JONES")

4.20 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)

string1: The first string to compare.
string2: The second string to compare.

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"))

4.21 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)

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

4.23 STRLEN

Use the @STRLEN function to return the length of a string, in number of characters.

Syntax

result = @STRLEN (string)

string: Can be the name of a column or a literal string. Enclose literals within quotation marks.

Example

@STRLEN (ID_NO)

4.24 STRLTRIM

Use the @STRLTRIM function to trim leading spaces.

Syntax

@STRLTRIM (string)

string: Can be the name of a character column or a literal string. Enclose literals within quotes.

Example

  birth_state = @strltrim(state)

4.25 STRNCAT

Use the @STRNCAT function to concatenate one or more strings to a maximum length.

Syntax

result = @STRNCAT (string, max_length [, string, max_length, ...])

string: Can be the name of a column or a literal string. Enclose literals within quotation marks.
max_length: The maximum string length, in characters.

Example

The following concatenates two strings and results in "ABC123."

PHONE_NO = @STRNCAT ("ABCDEF", 3, "123", 3)

4.26 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)

compare_data: The data to compare. Can be a character column or literal string.
max_compare_length: Specifies a number of characters to be compared in each string. For example, if you specify 2, the first two characters of each string are compared. If they are equal, 0 (zero) is returned.

Example

This example returns 0, since the first two characters of both strings are equal.

result = @STRNCMP ("JOHNSON", "JONES", 2)

4.27 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

4.28 STRRTRIM

Use the @STRRTRIM function to trim trailing spaces.

Syntax

@STRRTRIM (string)

string: Can be the name of a character column or a literal string. Enclose literals within quotes.

Example

 street_address = @strrtrim(address)

4.29 STRSUB

Use STRSUB to substitute one string within another string field or constant.

Syntax

@STRSUB (source_string_or_col, search_string, substitute_string, ...)

source_string_or_col: The string or column to replace.
search_string: The value to be replaced.
substitute_string: The replacement value.

Examples

Example 1

For this example, the source string is "123ABC123". The value "123" is to be replaced with "xx". The result is xxABCxx.

result = @STRSUB ("123ABC123", "123", "xx")

Example 2

For this example, the source string is "123ABC123". The value "A" is to be replaced with "z" and the value "1" is replaced with "0". The result is 023zBC023.

result = @STRSUB ("123ABC123", "A", "z", "1", "0")

4.30 STRTRIM

Use the @STRTRIM function to trim leading and trailing spaces.

Syntax

@STRTRIM (string)

string: Can be the name of a character column or a literal string. Enclose literals within quotes.

Example

 pin_no = @strtrim(custpin)

4.31 STRUP

Use @STRUP to change a character string or field to uppercase.

Syntax

@STRUP (string)

string: Can be the name of a character field or a literal string. Enclose literals within quotes.

Example

The following changes the string, "aaaaa" to "AAAAA".

result = @STRUP ("aaaaa")

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

Syntax

@TOKEN ("token_name")

"token_name": The name of the token for which data is to be retrieved.

4.33 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")