oracle ogg 列名转换,ogg oracle goldengate 能实现数据转换吗

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

User Tokens

A user token is information data that is captured and stored in the user token area of the GoldenGate record header, in the trail record for replication. These are typically used to store environment or system information but the values could be anything, even values calculated from the database column values. Token data can be retrieved and used in many ways to customize the way that GoldenGate delivers data.

To define a user token and associate it with data, use the TOKENS clause of the TABLE parameter in the Extract parameter file. It can be used to define a user token and associate it with GoldenGate environment data using the @GETENV function.

Syntax TABLE

There is a limit on the total space allocated for token keys and values stored in the trail. The token area in the record header permits up to 2,000 bytes of data. Token names, the length of the data, and the data itself must fit into that space

A best practice for naming tokens could be to call them the same name as the @getenv call, with a user-defined prefix such as "TKN".

For example, for the commit sequence number (using "getenv" category "TRANSACTION" and key value "CSN"), the token would be called "TKN-TRANSACTION-CSN".

TABLE SRC.AUDIT,

TOKENS (TKN-OSUSER = @GETENV("GGENVIRONMENT","OSUSERNAME"),

TKN-TRANSACTION-CSN =@GETENV(“TRANSACTION”,”CSN”),

TKN-DBNAME = @GETENV ("DBENVIRONMENT","DBNAME"),

TKN-HOSTNAME = @GETENV ("GGENVIRONMENT","HOSTNAME"),

TKN-COMMITTIME = @GETENV("GGHEADER","COMMITTIMESTAMP"),

TKN-REC-FILESEQNO=@GETENV ("RECORD", "FILESEQNO"),

TKN-REC-FILERBA=@GETENV ("RECORD", "FILERBA"));

The @TOKEN function is used to retrieve token data that is stored in the user token area of the GoldenGate record header. To use the defined token data in target tables, use the @TOKEN column-conversion function in the COLMAP clause of a Replicat MAP statement. The @TOKEN function maps the name of a token to a target column. Syntax @TOKEN (“”)

The following MAP statement maps target columns “osuser,”, “transaction_csn,” and so forth to tokens “tk-osuser,” “tk-transaction-csn,” and so forth.

MAP SRC.AUDIT, TARGET TRG.AUDIT,

COLMAP (USEDEFAULTS,

OSUSER = @TOKEN("TKN-OSUSER"),

TRANSACTION_CSN = @TOKEN("TKN-TRANSACTION-CSN"),

DBNAME = @TOKEN("TKN-DBNAME"),

HOSTNAME = @TOKEN("TKN-HOSTNAME"),

COMMITTIME = @TOKEN("TKN-COMMITTIME"),

RECFILESEQNO = @TOKEN ("TKN-REC-FILESEQNO"),

REC-FILERBA = @TOKEN ("TKN-REC-FILERBA"));

As an alternative, you can use @TOKEN within a SQLEXEC statement, a GoldenGate macro, or a user exit.

For more information about using tokens, see the GoldenGate for Windows and UNIX Administrator Guide.@GETENV

We can use the @GETENV function to return information about the Oracle GoldenGate environment:

Syntax @GETENV ("OSVARIABLE", "HOME")

- General information types, eg: lag information, last replicated operation, including detailed error information.

- Table-level statistics information types (starting 11.2).

- Oracle GoldenGate information types, eg: GoldenGate environment, record header information.

- Database information types, eg: information about a source transaction.

- Operating system information type, eg: information about operating system environment variable.

- Base 24 information types.

This option is valid for Extract and Replicat You can use the information as input into the following:

- Stored procedures or queries (with SQLEXEC)

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

In the following replicat file we add a column in the target with the COMMITTIMESTAMP value:

MAP GGS.TCUSTORD, TARGET GGS.TCUSTORD,

COLMAP (USEDEFAULTS,

COMMIT_HEADER = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));

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

- The GET_ENV_VALUE user exit function.

- Macros:

MACRO #optype

BEGIN

GETENV("GGHEADER", "OPTYPE")END;[This section is not visible to customers.]

See also: Oracle GoldenGate Windows and UNIX Reference Guide

The results are printed to screen and the report file. Use one GETENV statement per variable to be retrieved. The function only returns an exact match, other variables that contain “HOME,” such as ORACLE_HOME, would not be returned.

Not all @getenv keys return meaningful values in all environments. For example, a key may return a value on Oracle on Unix but not MS SQL Server on Windows. Also, values on one platform may have slightly different semantics for a different OS or database. Some values returned by @getenv are always available from the trail (such as commit timestamps), and therefore do not need to be stored as tokens.

There is a parameter named GETENV parameter that allows to view environment variables that were set with the SETENV parameter. It is valid for Extract and Replicat.