Example of a stored procedure user exit

Universal Cache User Guide : 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.

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. If your stored procedure returns 0, then a message is generated to the event log.