Universal Cache User Guide : IBM InfoSphere CDC for solidDB® reference : User exits for IBM InfoSphere CDC : Retrieving data with a stored procedure user exit
  
Retrieving data with a stored procedure user exit
You can retrieve data from your source table by passing system parameters to your stored procedure. You can retrieve the following type of data:
▪Retrieve system values (s$): When passed to a stored procedure, the s$ prefix makes system values available from the source database to your stored procedure. For example, s$entry identifies the entry point at which IBM InfoSphere CDC had run the user exit.
▪Retrieve journal control fields (j$): When passed to a stored procedure, the j$ prefix makes journal control fields available from the source database to your stored procedure. For example, j$USER identifies the user ID of the person that made the update on the source table. This is useful if you are using the stored procedure to audit table or row-level operations that have occurred on the source table.
▪Retrieve data values: Depending on the prefix you pass to the stored
procedure, you can retrieve data from the source database and make it available to your stored procedure. For example, you can use b$ to retrieve the before image of the source column.
Each of these values can be used as input parameters for the stored procedure user exit that you write. The format used to retrieve data is slightly different depending on the product that you are using:
For IBM InfoSphere CDC, the format is <x>$<value> where <x> represents the prefix and <value> represents the name of the value to be retrieved.
Retrieving system values using the s$ prefix
This prefix is used to retrieve system values. The table below presents and briefly describes these values.
Prefix and value
Data type
Description
s$entry
NUMBER
Represents the entry point from where the stored procedure was invoked. You can invoke a stored procedure from the following entry points:
▪1—indicates that IBM InfoSphere CDC has invoked the stored procedure before a table clear (truncate) operation
▪2—indicates that IBM InfoSphere CDC has invoked the stored procedure after a table clear (truncate) operation
▪3—indicates that IBM InfoSphere CDC has invoked the stored procedure before a row insert operation
▪4—indicates that IBM InfoSphere CDC has invoked the stored procedure after a row insert operation
▪5—indicates that IBM InfoSphere CDC has invoked the stored procedure before a row update operation
▪6—indicates that IBM InfoSphere CDC has invoked the stored procedure after a row update operation
▪7—indicates that IBM InfoSphere CDC has invoked the stored procedure before a row delete operation
▪8—indicates that IBM InfoSphere CDC has invoked the stored procedure after a row delete operation
▪9—indicates that IBM InfoSphere CDC has invoked the stored procedure before a table refresh operation
▪10—indicates that IBM InfoSphere CDC has invoked the stored procedure after a table refresh operation
s$srcSysId
VARCHAR
Identifies uniquely the location of the source data.
s$srcTabId
VARCHAR
Represents the name of the source table in the source database that sends replicated data to the target.
s$tgtTabId
VARCHAR
Represents the name of the target table in the target database that receives replicated data from the source.
Retrieving journal control fields using the j$ prefix
This prefix is used to retrieve information about the operation that occurred on the source system. You can use jb$ with IBM InfoSphere CDC to retrieve the same information.
The available values are listed:
Prefix and value
Data type
Description
j$CCID
VARCHAR
Identifies the transaction with the insert, update, or delete operation.
j$CODE
VARCHAR
Identifies the type of journal or log entry, either “U” for a refresh operation or “R” for mirroring.
j$CTRR or j$CNTRRN
VARCHAR
Identifies the relative record number of the source table that recorded the journal/log entry.
CTRR or CNTRRN contains meaningful information when you invoke your stored procedure on the insert entries that make up the refresh.
j$ENTT or j$ENTTYP
VARCHAR
Generates journal or log codes that identify the operation type on the source system.
j$JRN or j$JOURNAL
VARCHAR
The name of the journal/log where IBM InfoSphere CDC is reading insert, update, or delete operations from.
j$JOB
VARCHAR
Identifies the name of the job that made the insert, update, or delete on the source system.
j$MBR or j$MEMBER
VARCHAR
Identifies the name of the source table or its alias.
j$NBR or j$JOBNO
VARCHAR
Identifies the process ID of the program on the source table that is making the insert, update, or delete operation.
j$PGM or j$PROGRAM
VARCHAR
Identifies the name of the program on the source system that made the insert, update or delete operation.
j$SEQN or j$SEQNO
VARCHAR
Identifies the sequence number of the insert, update, or delete operation in the journal or log.
j$SYNM or j$SYSTEM
VARCHAR
Identifies the host name of the source system.
j$USER
VARCHAR
Identifies the database user name that made the insert, update, or delete operation on the source.
j$USPF
VARCHAR
Identifies the operating system user name that made the insert, update, or delete operation on the source.
j$TSTP or j$TIMSTAMP
VARCHAR
Identifies the date and time of when the insert, update, or delete operation or refresh was made on the source. In environments that support microsecond precision, the date and time format of this journal control field is YYYY-MM-DD-HH:MM:SS.UUUUUU. Otherwise, IBM InfoSphere CDC sets the microsecond component UUUUUU to zeroes or does not include it at all.
Retrieving data values using b$, a$, k$, and d$ prefixes
Four prefixes are used to retrieve data:
Prefix
Mode
Description
b$<source column name>
Input
Used to retrieve the before image of the data in a source column. The before image is the original image from the source table column before any transformation is applied to it.
For example, you may have made the following UPDATE to your source table:
UPDATE source_table set MYCOLUMN = 2 where MYCOLUMN = 1;
This will set 2 on all rows where MYCOLUMN was 1 before the execution of this SQL statement.
When you define a stored procedure and decide that you want the stored procedure to retrieve the before image of MYCOLUMN, you would specify the following:
b$MYCOLUMN;
This returns a value of 1.
a$<source column name>
Input
Used to retrieve the after image of the data in a source column. The after image is the translated data from the source table column. For example, the data that was translated by a derived expression.
For example, you may have made the following UPDATE to your source table:
UPDATE source_table set MYCOLUMN = 2 where MYCOLUMN = 1;
This will set 2 on all rows where MYCOLUMN was 1 before the execution of this SQL statement.
When you define a stored procedure and decide that you want the stored procedure to retrieve the after image of MYCOLUMN, you would specify the following:
a$MYCOLUMN;
This returns a value of 2.
k$<target key column name>
Input
Used to access the target table to find the rows that need to be modified.
Key columns are not available for auditing.
d$<target column name>
Input/Output
Used to retrieve data values after transformation, which will be used to update the table in the target database. Only these values may be modified by the stored procedure.
See also
User exits for IBM InfoSphere CDC