1 2 3 Previous Next 30 Replies Latest reply: May 28, 2012 1:18 PM by 937674 RSS

    How to get transaction timestamp for a specific column replication

    937674
      Hello Everyone,

      I have a table "SAL" on source end with columns -

      Emp_name varchar2 (50)
      Emp_id varchar2(30)
      Salary number(10)


      At target end I have similar table with an extra column as "+Tran_Data+", which keeps records of latest transaction or changes on source end and updates at target end in form +@datenow().+


      I have recently used COLS (Emp_id) functionality in extract on source end in order to get updates for Emp_id column only at target end.
      With updates in Emp_id on source, it does get replicate on target and tran_data also gets updated with latest timestamp.

      However, while updating any other column for eg. Emp_name which is not being included in COLS (Emp_id), the target table does not get data replicated but the timestamp in tran_data do get updated with latest time.

      Can anyone please help me out how to avoid update in tran_data in above scenario?

      Thanks in advance.
        • 1. Re: How to get transaction timestamp for a specific column replication
          EmaxG
          can you please post your configuration?
          • 2. Re: How to get transaction timestamp for a specific column replication
            937674
            Extraction EXT1 (Source end)

            EXTRACT EXT1
            USERID gg_target, PASSWORD *********
            EXTTRAIL /u3/ncrmst3data/oracleGG/dirdat/e1
            TABLE GG_TARGET.SAL, COLS (Emp_id), TOKENS (TKN-COMMIT-TS = @datenow ());





            Datapump Extraction DPUM1 (Source end)

            EXTRACT DPUM1
            NOPASSTHRU
            RMTHOST 10.77.16.13, MGRPORT 7809
            userid gg_target, password *********
            RMTTRAIL /oraclegg/dirdat/r1
            TABLE GG_TARGET.SAL, COLS (Emp_id);





            Replication REP1 (Destination end)

            replicat REP1
            userid GG_ADMIN password *********
            reperror 00001, discard
            reperror 01004, discard
            reperror 01154, discard
            reperror 01296, discard
            reperror 01151, discard
            ASSUMETARGETDEFS
            HANDLECOLLISIONS
            MAXTRANSOPS 1
            GROUPTRANSOPS 1
            DISCARDFILE /oraclegg/dirrpt/REP1.DSC, APPEND megabytes 20480
            MAP GG_TARGET.SAL, target GG_ADMIN.SAL, COLMAP (USEDEFAULTS,TRAN_DATA=@datenow ());
            • 3. Re: How to get transaction timestamp for a specific column replication
              EmaxG
              The following maps all source columns to target columns where the source/target names are the same, and explicitly maps the target "N" column to the source "K" column:
                  MAP test.table, TARGET test.table2,
                  COLMAP (
                  USEDEFAULTS,
                  N = K);
              The following ignores all columns from the source DB that are not explicitly listed.
                  MAP test.table, TARGET test.table2,
                  COLMAP (
                  N = K,
                  HI = HO,
                  .
                  .
                  .
                  something = else);
              In your case it should work by doing this:
              MAP GG_TARGET.SAL, target GG_ADMIN.SAL, COLMAP (Emp_id=Emp_id,TRAN_DATA=@datenow ());
              Let me know if this works.

              Please remember to mark your questions as answered in order to keep the forum clean.
              Greetings,
              N K

              Edited by: N K on 16-may-2012 5:52
              • 4. Re: How to get transaction timestamp for a specific column replication
                MikeN
                934671 wrote:
                I have a table "SAL" on source end with columns -

                Emp_name varchar2 (50)
                Emp_id varchar2(30)
                Salary number(10)

                At target end I have similar table with an extra column as "+Tran_Data+", which keeps records of latest transaction or changes on source end and updates at target end in form +@datenow().+

                I have recently used COLS (Emp_id) functionality in extract on source end in order to get updates for Emp_id column only at target end.
                With updates in Emp_id on source, it does get replicate on target and tran_data also gets updated with latest timestamp.

                However, while updating any other column for eg. Emp_name which is not being included in COLS (Emp_id), the target table does not get data replicated but the timestamp in tran_data do get updated with latest time.

                Can anyone please help me out how to avoid update in tran_data in above scenario?
                It's not clear exactly what is desired to be replicated from the source to the target... Do you

                (1) want all of the source columns to be replicated, or
                (2) want just "emp_id" from the source, and the generated pseudo-column "tran_data"?

                If option #1: i.e., you do want the target updated for every change that happens on the source (regardless of which column has been updated), but simply do not want "emp_id" to ever be empty, be sure that it is defined as a "pk" column and you've properly run "add trandata" on the source DB table. (If "emp_id" is not a pk column, then use "add trandata" with "cols", and add a "pkcols" option to your "table" and "map" statements.") In this case, for example, if the "salary" column changes, you'll actually get a change event with "tran_data" populated, and "emp_id" will be there too as long as it's a pk (or unique index) column; or, if it's not a pk column, you must either "fetch" it (fetchcols) or force-log the column (add trandata).

                If option #2: i.e., you do not want a change record replicated to the target for every change that happens on the source DB, you'll need to use a filter on the source. In that case, add a "filter" clause to the table statement, and don't replicat the record if "emp_id" is "@absent" (if it's not a pk, and not force-logged), or if "emp_id" is always "@present" (because it's a pk, or is force-logged), then you can prevent the record from being replicated if the before image is equal to the after image.

                Sorry if that's too much info and/or not clear; but if you can provide a concrete example of what you're looking to do, I'm sure you'll get a more clear answer.

                Btw, fyi #1, your pump really could be re-written as follows, since it doesn't do any type of filtering, mapping or user exits (and therefore can be as passthru pump), and since a pump can't ever send more data that what it's receiving, you can wildcard the table name and pass all columns.

                {code}
                EXTRACT DPUM1
                PASSTHRU
                RMTHOST 10.77.16.13, MGRPORT 7809
                RMTTRAIL /oraclegg/dirdat/r1
                TABLE GG_TARGET.*;
                {code}

                And fyi #2, "datenow()" is NOT the commit timestamp. It is the time that GoldenGate is reading the operation from the log which contains the committed transaction. Just as an example, if you stop extract, execute a commit, and start extract an hour later, your real commit timestamp and "datenow()" will be different by an hour.

                Instead, use the following, which actually gets the commit timestamp from the logs (remembering that commit timestamps in Oracle are inherently imprecise, of course):
                {code}
                TKN-COMMIT-TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
                {code}

                Edited by: MikeN on May 16, 2012 10:56 PM

                Edited by: MikeN on May 16, 2012 11:01 PM
                • 5. Re: How to get transaction timestamp for a specific column replication
                  937674
                  Hello Mike,

                  My exact requirement is to replicate only a specific column data from source to destination and update the timestamp at destination table only when the column update of specified column is being applied.

                  Here I have specified Emp_id as the only column to be replicated at destination table by using COLS (Emp_id) in extraction file. As I update Emp_id at source end, it do get replicated at destination table and tran_data timestamp gets updated with latest time.

                  However, when I update any other column lets say, Emp_name which is not mentioned in COLS (Emp_id) although it does not get replicated at destination end but the tran_data timestamp gets updated to latest time which is undesirable.

                  Please see my configuration details again -

                  Extraction EXT1 (Source end)

                  EXTRACT EXT1
                  USERID gg_target, PASSWORD *********
                  EXTTRAIL /u3/ncrmst3data/oracleGG/dirdat/e1
                  TABLE GG_TARGET.SAL, COLS (Emp_id), TOKENS (TKN-COMMIT-TS = @datenow ());



                  Datapump Extraction DPUM1 (Source end)

                  EXTRACT DPUM1
                  NOPASSTHRU
                  RMTHOST 10.77.16.13, MGRPORT 7809
                  userid gg_target, password *********
                  RMTTRAIL /oraclegg/dirdat/r1
                  TABLE GG_TARGET.SAL, COLS (Emp_id);




                  Replication REP1 (Destination end)

                  replicat REP1
                  userid GG_ADMIN password *********
                  reperror 00001, discard
                  reperror 01004, discard
                  reperror 01154, discard
                  reperror 01296, discard
                  reperror 01151, discard
                  ASSUMETARGETDEFS
                  HANDLECOLLISIONS
                  MAXTRANSOPS 1
                  GROUPTRANSOPS 1
                  DISCARDFILE /oraclegg/dirrpt/REP1.DSC, APPEND megabytes 20480
                  MAP GG_TARGET.SAL, target GG_ADMIN.SAL, COLMAP (USEDEFAULTS,TRAN_DATA=@datenow ());




                  I exactly want a dependency between Emp_id and tran_data that when Emp_id is updated, only then tran_data should get updated.


                  Also, want to tell that we cannot use COLS in PASSTHRU mode hence I modified it to NOPASSTHRU.
                  • 6. Re: How to get transaction timestamp for a specific column replication
                    937674
                    Hello N K,

                    As per your directives I tried the below replication statement -

                    MAP GG_TARGET.SAL, target GG_ADMIN.SAL, COLMAP (Emp_id=Emp_id,TRAN_DATA=@datenow ());


                    The tran_data column still gets updated with latest timestamp even if I am updating Emp_name and not Emp_id.

                    Basically, I want a dependency between the update in Emp_id and timestamp in tran_data that the tran_data should only get updated for any update in Emp_id column only.
                    • 7. Re: How to get transaction timestamp for a specific column replication
                      937674
                      Hello Everyone,

                      Is there any way of using FILTER or EVENTACTIONS in replication or extract scripts for solving my problem?

                      Please post a solution or any other related information anybody having for this issue thread.
                      • 8. Re: How to get transaction timestamp for a specific column replication
                        User11957149-Oracle
                        you should read Mike's comments closely. It's strange why you'd update an emp_id column which is most liekly a PK. Depending if it's a PK or not, you could use some built in functions (@present, @absent, @coltest(xx, present), etc) to first check if the transaction contsains data for the desired column before allowing the transaction to be applied on target.
                        • 9. Re: How to get transaction timestamp for a specific column replication
                          937674
                          Hello ,

                          Can you please tell me the syntax of @present or @absent or any other filtering command to be used in my scenario as I cannot find that on net ?

                          Also where I should use that filter statement - In extract or in replication script ?

                          I have posted my extract n replicate configurations in earlier post, one can edit them and provide me with the solution.

                          Thanks in advance.
                          • 10. Re: How to get transaction timestamp for a specific column replication
                            User11957149-Oracle
                            MAP GG_TARGET.SAL, target GG_ADMIN.SAL, FILTER (ON UPDATE, @COLTEST(EMP_ID,PRESENT)),
                            COLMAP (USEDEFAULTS,TRAN_DATA=@datenow ());

                            or,

                            MAP GG_TARGET.SAL, target GG_ADMIN.SAL, WHERE (@PRESENT(EMP_ID)), COLMAP (USEDEFAULTS,TRAN_DATA=@datenow ());
                            • 11. Re: How to get transaction timestamp for a specific column replication
                              937674
                              Hello,

                              Thank you for providing the syntax for @present.

                              I tried it out in my Goldengate configuration, but it didn't worked out. When I am updating any other column also at source end for eg. Emp_name, then also the tran_data is getting updated with latest timestamp at destination end.

                              My major requirement is to make dependency specifically for column Emp_id, that is any change made to this column only at source end, then only tran_data should get updated at destination end.

                              Please help me out in this issue.
                              • 12. Re: How to get transaction timestamp for a specific column replication
                                937674
                                Hello,

                                Please see my requirement according to below example.

                                Suppose I have following data in source table SAL -

                                emp_id      emp_name
                                +1      test+

                                I have following data in target SAL table -

                                emp_id      emp_name       tran_data
                                +1      test           01-MAY-2012 12:00:00+

                                now my requirement is, I have edited emp_id in source as -

                                emp_id      emp_name
                                +2      test+

                                then my target table gets updated as -

                                emp_id      emp_name       tran_data
                                +2      test           01-MAY-2012 12:02:00+

                                but if I update emp_name in source as -

                                emp_id      emp_name
                                +2      test123+

                                my target table shows

                                emp_id      emp_name       tran_data
                                +2      test           01-MAY-2012 12:04:00+

                                but I need tran_data to remain same as it was when only emp_id was modified like -

                                emp_id      emp_name       tran_data
                                +2      test           01-MAY-2012 12:02:00+

                                tran_data should not get updated for any other column change except Emp_id, as I have included COLS (Emp_id).

                                This is what I exactly need. Please help me out in achieving the same.
                                • 13. Re: How to get transaction timestamp for a specific column replication
                                  User11957149-Oracle
                                  If your emp_id is either primary key or unique index, the trail file will always contain data for this column, regardless you modified it or not on source. If this is the case, you will have to do a SQLEXEC to compare the EMP_ID in the trail vs. what existing target value is, If it is a match, that means EMP_ID is not updated on source so do nothing. If not, it means EMP_ID is updated on source and thus change the timestamp value on target.

                                  Why you'd update primary key, if it indeed is, is something i'm curious about.
                                  • 14. Re: How to get transaction timestamp for a specific column replication
                                    937674
                                    Yes my friend, Emp_id is a primary key and that is why I have included it specifically for replication in COLS (Emp_id) in extract script.

                                    And that is why I am not able to attain what I exactly need. The tran_data should only be dependent on any changes in Emp_id column only and not on any other column change. Emp_name is not a primary key and neither needed to be replicated and that is why I need that when any change made in Emp_name should not be replicated at target(which I have achieved) and also tran_data should not get updated(which I have not achieved).

                                    I need primary key changes only to be reflected on target table. The changes in Emp_id and updating of tran_data should be dependent on each other without having any other dependency with other columns.
                                    1 2 3 Previous Next