Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
6 replies Latest Post - ‏2012-12-20T12:45:02Z by smithha
SystemAdmin
SystemAdmin
294 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Tables Rows Count

    ‏2012-12-19T13:33:04Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Tables Rows Count

      ‏2012-12-19T14:54:33Z  in response to smithha
      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
        ACCEPTED ANSWER

        Re: Tables Rows Count

        ‏2012-12-19T15:43:46Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

          Re: Tables Rows Count

          ‏2012-12-20T07:13:58Z  in response to smithha
          thanks!
        • SystemAdmin
          SystemAdmin
          294 Posts
          ACCEPTED ANSWER

          Re: Tables Rows Count

          ‏2012-12-20T07:44:46Z  in response to smithha
          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
            ACCEPTED ANSWER

            Re: Tables Rows Count

            ‏2012-12-20T12:45:02Z  in response to SystemAdmin
            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