Creating a task to just count source table records

[RaySRI1]

I don't have direct access to the source tables so I would like to create a replicate task to just count the records in the source tables.  Can I create task to just count the source table records?  I would prefer not to transfer gb's of data, but can I create a task to move data to a test table but not impact other tasks using the same source table?  Could I create a filter that would prevent data from being transferred but yet give me a count in the logs or on the monitor on the total records scanned? Any other ideas?

Thanks

[lyka · Accepted solution]

Hello,

You can try this -  run a full load only task using a NULL target endpoint

Please find the steps below to create NULL target endpoint,
1. hold down CTRL key, click on "Manage Endpoint Connections".
2. click "New Endpoint Connection".
3. set the Role to "Target"
4. In the "Type" list, you should find a type called "NULL Target (>/dev/null)"

Then please use this NULL Target endpoint in your task.

Hope this helps

Thanks

Lyka

[RaySRI1]

That works! thank you very much for the quick response. Weekend is starting on a high note. 

[lyka]

Hi @RaySRI1 

im glad it worked out! have a great long weekend 🙂

Thanks

Lyka

[Heinvandenheuvel]

The solution you chose is still transferring all rows from the source to Replicate, only to ignore them after that. That does not seem to address your stated objective " I would prefer not to transfer gb's of data,"

Is there a single row (system) table in the source DB, not part of the Replication?  See if you can solve this using a bunch of source lookups using that single row in <some_schema>.<some_table_with_1_row> as the table to load in your count task but dropping all existing columns for that table and adding a fresh column for each table for which you need a count using a source_lookup as expression.
Each of those (many) expressions would look like:
Added column name = <own>_<table>_<count>
Expression = source_lookup(0,'<some_schema>','<some_table_with_1_row>',
'(SELECT COUNT(1) FROM <own>.<table>)','1=1')

Can you create a helper table in the source database such as: create table table_row_counts ( owner_name varchar(30), table_name varchar(30));
Now add a single column with a single expression to count for each source row: source_lookup(0,$OWNER_NAME,$TABLE_NAME,'COUNT(1)','1=1')

Both methods tested, and they work for me for an Oracle source.

No data, other than the counts transferred (and the owner/tablename columns for the second solution).

Hein.

[john_wang]

Hello @RaySRI1 , copy @lyka , @Heinvandenheuvel ,

if the source DB is Oracle then Materialized View can be archive that by the most efficient way. the key problem is if it's allowed to create such a view in PROD system.

Regards,
John.