Example of a stored procedure user exit

Replication with Infosphere CDC : IBM InfoSphere CDC for solidDB® reference : User exits for IBM InfoSphere CDC : Example of a stored procedure user exit

The following code snippet is an example of a stored procedure user exit.

Code

create or replace procedure
   PROD.AUDIT_STPROC (
   result       OUT INT,
   returnMsg OUT CHAR,
   s$entry      IN NUMBER,
   s$srcSysId IN CHAR
   s$srcTabId   IN CHAR,
   s$tgtTabId   IN CHAR,
   j$ENTT       IN CHAR,
   a$IDNO       IN NUMBER,
   a$PRICE      IN NUMBER,
   a$DESC       IN CHAR,
   a$LONGDESC   IN CHAR,
   a$TRANSDATE  IN DATE,
   d$IDNO       IN NUMBER,
   d$PRICE      IN NUMBER,
   d$DESC       IN CHAR,
   d$LONGDESC   IN CHAR,
   d$TRANSDATE  IN DATE
)

The parameters you declare and want to pass to your stored procedure must be valid data types.

The following parameters are mandatory and must be declared in your stored procedure:

result—Returns a value of '0' which indicates the stored procedure was successful or an error which may be an integer.

returnMsg—Returns an error message in the Event Log.

The following parameters have been declared in this stored procedure:

▪s$entry—retrieves the entry point at which the stored procedure was called. In this example, IBM InfoSphere CDC calls the user exit at every entry point.

▪s$srcSysId—retrieves the location of source data

▪s$srcTabId—retrieves the name of the source table

▪s$tgtTabId—retrieves the name of the target table

▪j$ENTT—retrieves the journal code that indicates the type of operation that took place on the source table

▪a$—retrieves the after image of the IDNO, PRICE, DESC, LONGDESC, and TRANSDATE source columns

▪d$—retrieves the transformed data of the IDNO, PRICE, DESC, LONGDESC, and TRANSDATE target columns

IS
ENTRYPOINT VARCHAR(50);
BEGIN
CASE s$entry
WHEN 16 THEN ENTRYPOINT := ’User Exit program called Before Insert’;
WHEN 1048576 THEN ENTRYPOINT := ’User Exit program called After Insert’;
WHEN 64 THEN ENTRYPOINT := ’User Exit program called Before Update’;
WHEN 4194304 THEN ENTRYPOINT := ’User Exit program called After Update’;
END CASE;

This stored procedure user exit can be invoked from these entry points.

insert into PROD.AUDIT_TABLE1 values (
s$entry, s$srcSysId, s$srcTabId, s$tgtTabId, j$ENTT, a$IDNO, a$PRICE, a$DESC, a$LONGDESC, a$TRANSDATE, d$IDNO, d$PRICE, d$DESC, d$LONGDESC, d$TRANSDATE, ENTRYPOINT);

This stored procedure user exit will INSERT these values into PROD.AUDIT_TABLE1.

result := 1;
returnMsg := ’OK’; END AUDIT_STPROC;

This stored procedure user exit was successful.

Note If your stored procedure returns a '0', then a message is generated to the event log.