13 Replies Latest reply: Feb 17, 2013 11:48 PM by 961076 RSS

    how does the extract get the filter condition ?

    961076
      hi everybody,
      i have a doubt. i searched a lot for the explanation but couldn't find.

      i have a source table TAB1 with columns A(primary key),B,C
      there is an extract on TAB1, ext1, which has a filter clause " *filter (@compute (c)=1)* "
      when i make an update on TAB1 with the following query
      update TAB1 set B=100 where A=200;*
      the redo log will not have the value of C stored for this transaction (it will have the values of A,B only as per my knowledge)
      but the extract has to check if the transaction satisfies the filter clause "c=1" which cannot be checked from redo logs
      how will it check?

      i have made the update, confirmed that the update got reflected in the target table too, then checked v$sql area for any select query made by goldengate on source table to check for the value of C for the particular transaction but i didn't find any such query

      Edited by: 958073 on Feb 13, 2013 12:41 PM

      Edited by: 958073 on Feb 13, 2013 12:42 PM
        • 1. Re: how does the extract get the filter condition ?
          satrap
          There are two ways of doing it:

          1) enable supplemental logging for column C, so everytime a record is updated in the table, column C is logged into the redo logs. This can be done as

          GGSCI> ADD TRANDATA <schema_name>.TAB1 COLS(C)

          2) Use FETCHCOLS in the TABLE clause of your extract param file to enable the fetching of column c from the source database when the value is not in the transaction record. This can be done by adding the following

          TABLE TAB1, FETCHCOLS (C),
          FETCHBEFOREFILTER,
          FILTER (@COMPUTE (c)=1);
          • 2. Re: how does the extract get the filter condition ?
            961076
            the problem is that although i added trandata the updates are not being captured.
            it worked till two days back and when i tried yesterday it did not capture updates (inserts and deletes are being captured just fine)

            i tried adding trandata again but it says trandata is already enabled for the table
            • 3. Re: how does the extract get the filter condition ?
              satrap
              Delete trandata and then add it again

              DELETE TRANDATA <schema_name>.TAB1
              ADD TRANDATA <schema_name>.TAB1 COLS(C)
              • 4. Re: how does the extract get the filter condition ?
                961076
                i did that, but still no change
                • 5. Re: how does the extract get the filter condition ?
                  satrap
                  Just to confirm if it an issue of the TRANDATA or the FILTER, could you remove the filter, restart the extract and see if the updates are getting replicated.
                  • 6. Re: how does the extract get the filter condition ?
                    961076
                    i did this test

                    created two tables app_test4,app_test4_copy... both with primary keys b,d... extract has a filter a=500

                    initial load successfull without trandata
                    inserted 200 records, captured
                    updated source table based on b,d columns, update not captured
                    updated source table based on b,a columns, update not captured
                    updated source table based on c,a columns, update not captured

                    trandata enabled for app_test4
                    updated source tables based on b,d columns, update not captured

                    stopped extracts and replicats
                    disabled trandata for app_test4
                    again trandata enabled for app_test4
                    inserted a row, captured
                    updated source table based on b,d columns, update not captured

                    stopped extract and replicat
                    added fetchcols(a), fetchbeforefilter to extract params
                    updated source table based on b,d columns, update captured

                    stopped extract and replicat
                    removed filter and fetchcols
                    updated source table based on b,d, update captured
                    • 7. Re: how does the extract get the filter condition ?
                      satrap
                      It is recommended to to put the FILTER in the data pump. That way you can look into the extract trail file using logdump and see if the update is being captured. Then you can again see the remote trail to see if the pump filtered it out.
                      • 8. Re: how does the extract get the filter condition ?
                        961076
                        can you tell me how to check the trail file
                        • 9. Re: how does the extract get the filter condition ?
                          satrap
                          In GGSCI, check the info of the pump

                          GGSCI> info DP, detail

                          EXTRACT DP Last Started 2013-01-30 11:54 Status RUNNING
                          Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
                          Log Read Checkpoint File ./dirdat/lk000006
                          2013-02-12 11:16:23.000000 RBA 7575

                          Exit GGSCI

                          From the GG home directory, launch logdump

                          $ logdump
                          open the trail file the pump is reading from (i.e. the extract is writing to )
                          $ open ./dirdat/lk000006
                          Turn on header information
                          $ ghdr on
                          Turn on detail data
                          $ detail data
                          To see the last change written to this trail, position it on the end of the file
                          $ pos eof
                          Position it to read in reverse from the latest change
                          $ pos rev
                          Read the last change
                          $ next
                          Read the second last change
                          $ next
                          and so on ....

                          You can see the actual column values in there.
                          • 10. Re: how does the extract get the filter condition ?
                            961076
                            i created new tables, added trandata before creating extract,replicat
                            like you said i kept the filter clause in replicat
                            initial load was successful
                            then i started inserting some rows and they were replicated successfully

                            made an update on a row
                            checked the extract stats
                            GGSCI> stats exte
                            it says 200 inserts and 1 update
                            checked the replicat stats
                            GGSCI> stats repe
                            it says 200 inserts and 0 updates
                            so i guess the problem is with filter working along with trandata

                            Edited by: 958073 on Feb 15, 2013 6:08 PM
                            • 11. Re: how does the extract get the filter condition ?
                              961076
                              i tried this

                              add trandata gg.app_test5, cols (a)*

                              now the updates are being captured and replicated properly

                              guess we need to mention the columns whose trandata we want to be added
                              • 12. Re: how does the extract get the filter condition ?
                                satrap
                                That is what I was trying to say in my very first reply....

                                "
                                1) enable supplemental logging for column C, so everytime a record is updated in the table, column C is logged into the redo logs. This can be done as

                                GGSCI> ADD TRANDATA <schema_name>.TAB1 COLS(C)

                                "
                                • 13. Re: how does the extract get the filter condition ?
                                  961076
                                  Sorry satrap,i tried "add trandata gg.app_test5" thinking that it will add trandata to all columns when you first said, but didnt work so later i mentioned the column "a" then it worked.. anyway thanks