Topic
4 replies Latest Post - ‏2012-11-23T04:53:02Z by SystemAdmin
SystemAdmin
SystemAdmin
61 Posts
ACCEPTED ANSWER

Pinned topic How to retrieve source schema name from user exit stored procedure in CDC

‏2012-10-23T13:11:25Z |
Hi,
I have a subscription that calls a 'After Refresh' User-Exit stored procedure from Management Console. Now, I can access various system variables like source and target table names using 's$srcTabId' and 's$tgtTabId' respectively. But I could not find a variable, system or journal, that gives me the source schema name. Is there any way to access the source schema name from stored procedure?
Appreciate your help.
Updated on 2012-11-23T04:53:02Z at 2012-11-23T04:53:02Z by SystemAdmin
  • GlenSakuth
    GlenSakuth
    9 Posts
    ACCEPTED ANSWER

    Re: How to retrieve source schema name from user exit stored procedure in CDC

    ‏2012-10-23T15:17:08Z  in response to SystemAdmin
    Depending on which source db and CDC version you are using, the Source Table Library (JOLIB) may contain the source schema name.

    Regards,

    Glen.
    • SystemAdmin
      SystemAdmin
      61 Posts
      ACCEPTED ANSWER

      Re: How to retrieve source schema name from user exit stored procedure in CDC

      ‏2012-11-22T06:44:15Z  in response to GlenSakuth
      Thanks Glen. My source datastore is DB2 and target is Oracle. I am using CDC 6.5.2 http://Build 6.5.1633.0. I have a many to one mapping from DB2 to Oracle table. I have added a stored procedure user exit on AFTER REFRESH and in the procedure I am trying to access the schema of the source DB2 table. The stored procedure runs on the target(Oracle) side. I tried searching for JOLIB but couldn't get much help.(sorry, still new to CDC concepts) I could access the system name using j$SYSTEM but j$LIBRARY didn't work.
      Appreciate if you could help me figure out the exact syntax to be used in Oracle stored procedure to fetch the schema name of the source DB2 table.

      Thanks for your time,
      Satyajit
  • GlenSakuth
    GlenSakuth
    9 Posts
    ACCEPTED ANSWER

    Re: How to retrieve source schema name from user exit stored procedure in CDC

    ‏2012-11-22T16:54:21Z  in response to SystemAdmin
    Hi Satyajit,

    Unfortunately, this is not available for the platforms you are using. Below is a link to the fields supported:

    http://publib.boulder.ibm.com/infocenter/iidr/v10r1m2/topic/com.ibm.cdcdoc.cdcformssql.doc/concepts/retrievingjournalcontrolfieldsusingthejprefix_jsql.html?resultof=%22%6a%6f%75%72%6e%61%6c%22%20%22%63%6f%6e%74%72%6f%6c%22%20%22%66%69%65%6c%64%73%22%20%22%66%69%65%6c%64%22%20

    Given this, a (admittedly not great) work-around would be to have a script on the source which gets the tables schema names and writes it to a table that is mirrored to the target. You could then do a look up on this target table.

    Regards,

    Glen.
    • SystemAdmin
      SystemAdmin
      61 Posts
      ACCEPTED ANSWER

      Re: How to retrieve source schema name from user exit stored procedure in CDC

      ‏2012-11-23T04:53:02Z  in response to GlenSakuth
      Appreciate your help Glen. I will try out your approach.

      Cheers,
      Satyajit