8 Integrating Data

Ensure that the variable and value you specify in a comparison match appropriately. Compare:

• Characters with literal string

• Numeric fields with numeric values, which can include a sign and decimal point

• SQL datetime types to literal strings, using the format in which the field is retrieved by a program

When a compressed update record is encountered for a table, only part of the record image is available for the condition evaluation. By default, when a column required by the condition evaluation is missing, the record is ignored and output to the discard file, and a warning is issued.

• Use only columns that appear in the primary key of the record, since key fields are always present in compressed records.

• Test for a column's presence first, then for the column's value.

To test for a column's presence, use the following syntax:

field [= | <>] [@PRESENT | @ABSENT]

The following example returns all records when the AMOUNT field is over 10000 and does not cause a record to be discarded when AMOUNT is absent.

WHERE (AMOUNT = @PRESENT AND AMOUNT > 10000)

Evaluate SQL columns for NULL values with the @NULL clause.

The following test returns TRUE if the column is NULL, and FALSE for all other cases (including a column missing from the record).

WHERE (AMOUNT = @NULL)

The following test returns TRUE only if the column is present in the record and not NULL.

WHERE (AMOUNT = @PRESENT AND AMOUNT <> @NULL)

Numeric fields are converted from one type and scale to match the type and scale of the target. If the scale of the source is larger than that of the target, the number is truncated on the right. If the target scale is larger than the source, the number is padded with zeros.

Varchar and character columns can accept other character, varchar, group, and datetime columns, or string literals enclosed in quotation marks. If the target character column is smaller than that of the source, the character column is truncated on the right.

Date-time fields can accept datetime and character columns, as well as string literals. If you attempt to map a character into a datetime column, make sure it conforms to the Oracle GoldenGate external SQL format (YYYY-MM-DD:HH:MI:SS.FFFFFF). Required precision varies according to data type and target platform. Datetime columns are truncated on the right as necessary. If the source column is not as long as the target, the column is extended on the right with the values for the current date and time.

1. Edit the Extract parameter file.

   GGSCI> TEDIT PARAMS EXTDEMO
   

2. Specify a table name

   TABLE $DATA.MASTER.PRODUCT,
   

3. Enter the desired tokens. The @GETENV function, quotation marks and comma delimiter are required.

   TOKENS
     (
      TKN-GROUP-NAME   =@GETENV ("GGENVIRONMENT", "GROUPNAME"),
      TKN-HOST-NAME    =@GETENV ("GGENVIRONMENT", "HOSTNAME"),
      TKN-OS-USER      =@GETENV ("GGENVIRONMENT", "OSUSERNAME"),
      TKN-BA           =@GETENV ("GGHEADER", "BEFOREAFTERINDICATOR"),
      TKN-COMMIT-TS    =@GETENV ("GGHEADER", "COMMITTIMESTAMP"),
      TKN-LOG-POSITION =@GETENV ("GGHEADER", "LOGPOSITION"),
      TKN-LOG-RBA      =@GETENV ("GGHEADER", "LOGRBA"),
      TKN-TABLE        =@GETENV ("GGHEADER", "TABLENAME"),
      TKN-OP-TYPE      =@GETENV ("GGHEADER", "OPTYPE"),
      TKN-REC-LEN      =@GETENV ("GGHEADER", "RECORDLENGTH"),
      TKN-TRNS-IND     =@GETENV ("GGHEADER", "TRANSACTION INDICATOR"),
     );
   

4. Exit the parameter file.

To retrieve values, you must include a MAP parameter and a COLMAP clause in the Replicat parameter file, then use the @TOKEN function to specify the values to retrieve.

MAP $DATA.MASTER.PRODUCT, TARGET $DATA.MASTER.PRODUCT_CHANGES,
  COLMAP (USEDEFAULTS,
          SOURCE_GROUP      =@TOKEN ("TKN-GROUP-NAME"),
          SOURCE_HOST       =@TOKEN ("TKN-HOST-NAME"),
          SOURCE_USER       =@TOKEN ("TKN-OS-USER"),
          BEFORE_AFTER_IND  =@TOKEN ("TKN-BA"),
          TIMESTAMP         =@TOKEN ("TKN-COMMIT-TS"),
          SOURCE_TABLE      =@TOKEN ("TKN-TABLE"),
          IO_TYPE           =@TOKEN ("TKN-OP-TYPE"));

The @TOKEN function requires quotation marks.

When you specify COLMAP USEDEFAULTS, Extract maps columns in the source table to columns in the target with the same name. At startup, Extract outputs column names that match and will map to each other.

The USEDEFAULTS parameter allows matching columns to be mapped, plus additional columns. This can be useful when the source and target definitions are similar but not identical.

If you set up global column mapping rules with COLMATCH parameters, you can map columns with different names to each other using default mapping. See the Extract and Replicat COLMATCH parameter for more details.

When unspecified or no match is found in a default map, a target field defaults to one of the following:

If the target table contains names corresponding to the transactional columns described above, the special column values are mapped to the target record format.

The following is the source Enscribe DDL for the examples in this section.

RECORD PRODUCT-REC.
FILE IS PRODDAT KEY-SEQUENCED AUDIT.
05 PROD-KEY.
   10 CODE1 PIC X(2).
   10 CODE2 PIC 9(2).
05 PROD-INDEX1.
   10 PRICE PIC 9(7)V9(2) COMP.
   10 CODE1 PIC X(2).
   10 CODE2 PIC 9(2).
05 PROD-INDEX2.
   10 INVENTORY PIC 9(5).
   10 CODE1 PIC X(2).
   10 CODE2 PIC 9(2).
05 DESC     PIC X(40).
KEY IS PROD-KEY.
END.

The following is the target SQL DDL for the examples in this section.

Target SQL DDL
CREATE TABLE PRODTAB
(
CODE CHAR(4) NOT NULL
, PRICE NUMERIC (8,2) NOT NULL
, INVENTORY DECIMAL (6)
, MANAGER CHAR (20) NOT NULL
, DESC VARCHAR (30)
, UPDATE_TIME DATETIME YEAR TO SECOND NOT NULL
, PRIMARY KEY (CODE)
);

Note that one can move a group level (PROD-KEY) to a character field. This is feasible since CODE2 is a DISPLAY field, not a COMP. Also, the user does not have to qualify PRICE, INVENTORY or DESC since they are all unique in the source definition. UPDATE_TIME will default to the time at which EXTRACT processes the record. PRICE may be truncated since it has one more significant digit in the source field than in the target.

FILE $DAT11.OLDAPP.PRODFL,
DEF PRODUCT-REC,
TARGET $DATA6.NEWAPP.PRODTAB,
COLMAP
  (CODE      = PROD-KEY,
   PRICE     = PROD-INDEX1.PRICE,
   INVENTORY = INVENTORY,
   MANAGER   = "Jack Smith",
   DESC      = DESC);

Since this mapping takes the primary key value from a non-primary key source, it discards the result whenever a source record is updated without updating the price. In the following example, even if AUDITCOMPRESS is used, updates can be delivered since the primary key is always present.

FILE $DAT11.OLDAPP.PRODFL,
DEF PRODUCT-REC,
TARGET $DATA6.NEWAPP.PRODTAB,
COLMAP
  (CODE      = PROD-INDEX1.CD1,
   PRICE     = PROD-INDEX1.PRICE,
   INVENTORY = INVENTORY,
   MANAGER   = "Unknown",
   DESC      = DESC);