Topic
  • 6 replies
  • Latest Post - ‏2012-12-20T12:45:02Z by smithha
SystemAdmin
SystemAdmin
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
    smithha
    162 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
  • SystemAdmin
    SystemAdmin
    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
    smithha
    162 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.
  • SystemAdmin
    SystemAdmin
    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!
  • SystemAdmin
    SystemAdmin
    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

    Executable not found : rcc
    -user iadmin -password ***** -isHost ispardev -port 9080 -run ACT_DWH_DEV rcc

    Thanks
    Lior Lavi
    ETL Team Leader
  • smithha
    smithha
    162 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

    Executable not found : rcc
    -user iadmin -password ***** -isHost ispardev -port 9080 -run ACT_DWH_DEV rcc

    Thanks
    Lior Lavi
    ETL Team Leader
    Try running the IAJob.sh with the command: -lexecutables project_name

    That should list all the available executables in your project so that you can confirm whether "rcc" is listed there or not. If it is there, then I'd recommend contacting customer support to help resolve the issue.

    Harald