Topic
• 6 replies
• Latest Post - ‏2012-12-20T12:45:02Z by smithha
294 Posts

# Pinned topic Tables Rows Count

‏2012-12-18T14:20:53Z |
how do you suggest to perform Rows count comparison between two tables?
Updated on 2012-12-20T12:45:02Z at 2012-12-20T12:45:02Z by smithha
• smithha
161 Posts

#### Re: Tables Rows Count

‏2012-12-19T13:33:04Z
The simplest way is to create a rule for each table such as FieldX Exists and bind that to the key field. Each rule will generate statistics for Total Records and Total Rows Met (those should generally be equal on a key field).

Then create a Metric that compares the Total Records for table1 and the Total Records for table2. Assuming either one could be higher than the other a good Metric expression would be: *ABS(ruleA Total Records - ruleB TotalRecords) *
Include a Benchmark for the Metric so that you can indicate what variance, if any, you tolerate.

For more on logic for Metrics see: http://publib.boulder.ibm.com/infocenter/iisinfsv/v8r7/topic/com.ibm.swg.im.iis.ia.quality.doc/topics/dq_functions_syntax_metrics.html

Harald
294 Posts

#### Re: Tables Rows Count

‏2012-12-19T14:54:33Z
• smithha
• ‏2012-12-19T13:33:04Z
The simplest way is to create a rule for each table such as FieldX Exists and bind that to the key field. Each rule will generate statistics for Total Records and Total Rows Met (those should generally be equal on a key field).

Then create a Metric that compares the Total Records for table1 and the Total Records for table2. Assuming either one could be higher than the other a good Metric expression would be: *ABS(ruleA Total Records - ruleB TotalRecords) *
Include a Benchmark for the Metric so that you can indicate what variance, if any, you tolerate.

For more on logic for Metrics see: http://publib.boulder.ibm.com/infocenter/iisinfsv/v8r7/topic/com.ibm.swg.im.iis.ia.quality.doc/topics/dq_functions_syntax_metrics.html

Harald
Thank!!

Actually I also thought about this solution today , but you helped with the ABS idea.

Now, is there a way to combine this metric in DataStage ETL flow?
• smithha
161 Posts

#### Re: Tables Rows Count

‏2012-12-19T15:43:46Z
Thank!!

Actually I also thought about this solution today , but you helped with the ABS idea.

Now, is there a way to combine this metric in DataStage ETL flow?
The rules and metrics can be invoked via API, so one option would be to create a DS Job Sequence with specific steps to run each rule and then run the metric.
294 Posts

#### Re: Tables Rows Count

‏2012-12-20T07:13:58Z
• smithha
• ‏2012-12-19T15:43:46Z
The rules and metrics can be invoked via API, so one option would be to create a DS Job Sequence with specific steps to run each rule and then run the metric.
thanks!
294 Posts

#### Re: Tables Rows Count

‏2012-12-20T07:44:46Z
• smithha
• ‏2012-12-19T15:43:46Z
The rules and metrics can be invoked via API, so one option would be to create a DS Job Sequence with specific steps to run each rule and then run the metric.
Hi Smithha,
When invoking IA Metrica (Metric name "rcc") from Linux using IAJob.sh script but getting the following error message:"Executable not found : rcc"

See the full log from linux:
root@ispardev IAJob_Script# /DataStageInst/IBM/InformationServer/ASBNode/bin/./IAJob.sh -user iadmin -password ***** -isHost ispardev -port 9080 -run ACT_DWH_DEV rcc
-> executing task '-run' with projectName = ACT_DWH_DEV, executableName = rcc

-user iadmin -password ***** -isHost ispardev -port 9080 -run ACT_DWH_DEV rcc

Thanks
Lior Lavi
• smithha
161 Posts

#### Re: Tables Rows Count

‏2012-12-20T12:45:02Z
Hi Smithha,
When invoking IA Metrica (Metric name "rcc") from Linux using IAJob.sh script but getting the following error message:"Executable not found : rcc"

See the full log from linux:
root@ispardev IAJob_Script# /DataStageInst/IBM/InformationServer/ASBNode/bin/./IAJob.sh -user iadmin -password ***** -isHost ispardev -port 9080 -run ACT_DWH_DEV rcc
-> executing task '-run' with projectName = ACT_DWH_DEV, executableName = rcc