Filtering date with GoldenGate

Today, while trying to break a big table to chunks to improve GoldenGate initial loading, I was trying to utilize the partitioning feature already available with the table which was based on time. Table is weekly partitioned, so if I try to filter rows based on partition, I can get data to be extracted easily by partition pruning. I have to use classic extract as the database version we are upgrading is 11.2.0.2 which doesn't support integrated extract.

So technical environment is as follows

?

1 2 3 4 5 6 7 8 9 10 11

==> [aiacl00378] [oracce] [DVCCE1DB] $ggsci -v   Oracle GoldenGate Command Interpreter for Oracle Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 15 2018 21:16:09   Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.     ==> [aiacl00378] [oracce] [DVCCE1DB] $uname -a Linux aiacl00378 2.6.32-642.3.1.el6.x86_64 #1 SMP Sun Jun 26 18:16:44 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux

As per Oracle support note Doc ID 1458966.1, if you are trying to filter records based on date, the following can be used.

TABLE {source table},
FILTER (@COMPUTE (@DATE("JUL", "YYYY-MM-DD", creation_time) >=
@DATE("JUL", "YYYY-MM-DD", "2012-01-01")) > 0);

My extract parameter file w.r.to the above is as below. 

?

1 2 3 4 5 6 7 8 9

EXTRACT IEXT1 setenv NLS_CHARACTERSET="AL32UTF8" setenv NLS_LANG="AMERICAN_AMERICA.AL32UTF8" setenv ORACLE_SID="DV1DB" USERID ggs_dba, PASSWORD ggs_dba RMTHOST ********, MGRPORT 12000 RMTFILE /u01/app/oracle/product/gg12.3/dirdat/c1, megabytes 2000, PURGE table ccedba.CUSTOMER, filter(@compute(@date('YYYY-MM-DD HH:MI:SS','DD-MMM-YY HH:MI:SS',creation_date) < @date('YYYY-MM-DD HH:MI:SS','DD-MMM-YY HH:MI:SS','22-JUN-18 0 0:00:00'))>0);

This above extract when started didn't extract any records.
Excerpt from report file the extract process generated:

?

*********************************************************************** *                   ** Run Time Statistics **                         * ***********************************************************************     Report at 2018-07-30 07:37:11 (activity since 2018-07-30 07:37:02)   Output to /u01/app/oracle/product/gg12.3/dirdat/c1:   No records extracted.

The database had a handful of records falling under this filter criteria.

?

1 2 3 4 5 6 7 8

SQL> SQL> select count(*) from ccedba.customer   2  where creation_date < to_date('2018-06-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss');     COUNT(*) ----------       3444   SQL>

After trying different ways to make the above work, I came across another support note Doc ID 966966.1 where it talks about default date formats in both Oracle and GoldenGate.

So why not to use the direct format of date where GoldenGate defaults to so that the records can be filtered? And yes, it worked like a charm.
The parameter file looks like the below.

?

1 2 3 4 5 6 7 8

EXTRACT IEXT1 setenv NLS_CHARACTERSET="AL32UTF8" setenv NLS_LANG="AMERICAN_AMERICA.AL32UTF8" setenv ORACLE_SID="DV1DB" USERID ggs_dba, PASSWORD ggs_dba RMTHOST ********, MGRPORT 12000 RMTFILE /u01/app/oracle/product/gg12.3/dirdat/c1, megabytes 2000, PURGE table ccedba.customer, WHERE (CREATION_DATE < '2018-06-22 00:00:00');

Excerpt from the report file generated for the above parameter file.

?

018-07-30 09:02:03  INFO    OGG-01478  Output file /u01/app/oracle/product/gg12.3/dirdat/c1 is using format RELEASE 12.3.   2018-07-30 09:02:03  INFO    OGG-02911  Processing table CCEDBA.CUSTOMER.   *********************************************************************** *                   ** Run Time Statistics **                         * ***********************************************************************     Report at 2018-07-30 09:02:06 (activity since 2018-07-30 09:01:58)   Output to /u01/app/oracle/product/gg12.3/dirdat/c1:   From Table CCEDBA.CUSTOMER:        #                   inserts:      3444        #                   updates:         0        #                   deletes:         0        #                  discards:         0

Look at the where clause for the table extraction. It's just too easy as I only made the date format as 'YYYY-MM-DD HH:MI:SS'.
I'm not sure whether this same approach works with GoldenGate versions below 12.3 as I didn't test for the same.

Note:
I have used options such as FILTER, SQLPREDICATE, WHERE caluses with different combinations of date format before arriving to this simple solution.

Happy Filtering!!!