5 Replies Latest reply: Jan 23, 2014 10:16 PM by MikeN RSS

    Golden Gate exception handling

    User9935203-Oracle

      Has anyone customized the userexit function for exception handling? My customer has below questions,

       

      1) How to define/get an “out” parameter to userexit and how it can be used ? The input-parameters are explained with the option of “PARAMS” of CUSEREXIT

      2). How to assign the column-value-string returned by Userexit to a table-column inside a MAP statement of replicat-parameter file ?

      3). How to use userexit function: get_column_value_from_name ,or, any other functionality that can be helpful for our objective here. An end-to-end working example can help.

       

      Thanks in advance.

      Sanjeev Thakur

        • 1. Re: Golden Gate exception handling
          MikeN

          user9935203 wrote:

           

          Has anyone customized the userexit function for exception handling? My customer has below questions,

           

          1) How to define/get an “out” parameter to userexit and how it can be used ? The input-parameters are explained with the option of “PARAMS” of CUSEREXIT

          2). How to assign the column-value-string returned by Userexit to a table-column inside a MAP statement of replicat-parameter file ?

          3). How to use userexit function: get_column_value_from_name ,or, any other functionality that can be helpful for our objective here. An end-to-end working example can help.

           

          Thanks in advance.

          Sanjeev Thakur

           

          The only way to assign values back into the trail is in the user-exit (the C code) itself, not in the prm file.  You have to be careful not to over-flow the field, though; e.g.,. if it's a 4-character column, you can only assign a maximum of 4 (different) characters to the column.  There are demo C-user-exits distributed with GoldenGate; see for example, UserExitExamples/ExitDemo/exitdemo.c

          • 2. Re: Golden Gate exception handling
            User9935203-Oracle

            Thanks for your reply. This is what they tried to use UserExit for additional functionality and thus, following Metalink Doc ID 1479489.1. They were able to compile and generate ".so" file for exitdemo.c. But userexit in replict parameter file, abends with the follwoing error:

            OGG-01126  Abending at user-exit request.

             

            Does anyone know of any other customers doing something similar or this is bad design?

            • 3. Re: Golden Gate exception handling
              MikeN

              user9935203 wrote:

               

              Thanks for your reply. This is what they tried to use UserExit for additional functionality and thus, following Metalink Doc ID 1479489.1. They were able to compile and generate ".so" file for exitdemo.c. But userexit in replict parameter file, abends with the follwoing error:

              OGG-01126  Abending at user-exit request.

               

              Does anyone know of any other customers doing something similar or this is bad design?

               

               

              I see -- you're using the result of the user-exit in a replicat/map, instead of writing it out to a target trail (I misunderstood that part, but it's only a minor difference.)  ...It's possible that all you would need to do when making the callback to set the column value is to also modify the record structure flag "source_or_target", setting it to EXIT_FN_TARGET_VAL (instead of "SOURCE_VAL".)  (But no guarantees, sorry... there's a lot that could go wrong when trying to get user-exits to work.)  Another thing to look at for inspiration would be the other user-exit demo's provided in the GoldenGate distribution (UserExitExamples/{ExitDemo, ExitDemo_lobs, ExitDemo_more_recs, ExitDemo_passthru, ExitDemo_pk_befores}) -- some even include (iir) examples of executing SQL directly from the user-exit, instead of relying on the GG "map" to do the updating.

               

              However, just as an alternative to try instead of user-exits -- and, since this is just for exception handling (so, the processing overhead would only apply when there is unexpected data) -- you might try "sqlexec" instead of user-exits.  They are more commonly used for this use-case, and a little easier to develop, debug and maintain.  The docs are pretty clear on how to get a value from a sqlexec and use it in a replicat "map" statement.

              • 4. Re: Golden Gate exception handling
                User9935203-Oracle

                Thanks Mike, this is helpful. I will forward the details. Just an fyi, here's the wholisitc picture what they are trying to accomplish,

                 

                What we are trying :

                 

                To capture the information for any exception due to data issue or any other Oracle error at the Target database of a typical Goldengate replication setup. This is intended to be achieved through storing the information for a failed DML in a generic exception table for all the tables configured for Goldengate replication setup. This framework has been tested, but with one caveat which is detailed below.

                 

                Details of current focus:

                 

                We plan to store the details of PK/UK/Unique-key columns with their values in one column: ROW_KEY_VALUES, of the generic exception table in the following format:

                 

                ROW_KEY_VALUES = keycol1,keycol2,keycol3…. : value- keycol1, value- keycol2, value- keycol3,………………….

                 

                Tried so-far:

                 

                I used a SQLEXEC call to an in-house developed stored function to extract the PK/UK column names and then, assigned it to  the table-column (ROW_KEY_VALUES ) within MAP statement:

                 

                The above gives me the PK/UK column string like :

                1). for Single col PK/UK for a table, say: EMP table: "EMPNO : "

                2). For Multi col PK/UK combination for a table, say :DEPT table: "DEPTNO,DNAME,LOC : "

                 

                Issue details :

                 

                As I pointed earlier, our objective is to store the Key-cols with their values in ROW_KEY_VALUES column, e.g.-continuing from example above:

                 

                1). for Single col PK/UK for one table, say: EMP table: "EMPNO : 9640"

                2). For Multi col PK/UK combination for a table, say :DEPT table:  "DEPTNO,DNAME,LOC : 20,TOG,Dallas "

                 

                I am not aware of any function that can extract the column-values by passing the column names within GG functionality. The manuals suggest that UserExit function : get_column_value_from_name  could  help in this regard. So, In order to address the requirement, as-of-now we look forward to find the answers for the following questions:

                 

                1). How to define/get an “out” parameter to userexit and how it can be used ? The input-parameters are explained with the option of “PARAMS” of CUSEREXIT

                2). How to assign the column-value-string returned by Userexit to a table-column inside a MAP statement of replicat-parameter file ?

                3). How to use userexit function: get_column_value_from_name ,or, any other functionality that can be helpful for our objective here. An end-to-end working example can help.

                • 5. Re: Golden Gate exception handling
                  MikeN

                  > user9935203 wrote:

                  > 1). How to define/get an “out” parameter to userexit and how it can be used?

                  > The input-parameters are explained with the option of “PARAMS” of CUSEREXIT


                  In the user-exit, you have the record data structure; you can literally overwrite the column value.


                  > 2). How to assign the column-value-string returned by Userexit to a table-column

                  > inside a MAP statement of replicat-parameter file ?


                  By setting the source_or_target flag to "target" in the record data structure, the value "set" would be the mapped target value, not the source value: e.g., prm file:  map src.table1, target targ.table1.... colmap(target_col1 = source_col1...)... =>  user-exit source code: record_def* record; record->source_or_target = EXIT_FN_TARGET_VAL => target value is updated in code, instead of (unused) source value.


                  > 3). How to use user-exit function: get_column_value_from_name ,or, any other functionality

                  > that can be helpful for our objective here. An end-to-end working example can help.


                  See the following demo user-exit which has some of this functionality:   {gg_install_dir}/UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c

                   

                  Or, instead, perhaps just make a SQL call directly from the user-exit to the database.  Then, do as much (or as little) as you want in the 'map', which becomes practically irrelevant. You have the column names and column values in the user-exit; so just do the SQL directly from there?


                  (And it probably goes without saying, but:  This is a complicated requirement to implement. Or rather, it's a deceptively simple requirement that's complicated to implement. A lot of effort is being expended to merge column values & their names into a single columns... Which in general is always a bad idea (and anti-relational & anti-database.)  If the values have to be used again, they'll just have to be parsed.  On the other hand, if the "exception" table merely contained all the same columns as the "regular" tables, plus perhaps a couple extra "meta" columns, then the solution becomes a relatively trivial replicat "map" statement:  map src, targ, sqlexec(...), colmap(usedefaults, targ_meta_col=some_value, targ_all_key_cols=sqlexec.result....)  Although I totally understand if this decision is out of your control.)


                  Good luck,

                  -m