To get count(1) of table from routine

synsog · Post by **synsog** » Tue Feb 24, 2015 11:53 pm

Hi All,

Is there any way to create a generic routine where if we pass a table name (and its required parameters to connect to db), it should return the table record count.

Thanks in advance

chulett · Post by **chulett** » Tue Feb 24, 2015 11:59 pm

Undoubtedly. What kind of routine - Parallel C++, Sequence BASIC, something else entirely?

synsog · Post by **synsog** » Wed Feb 25, 2015 12:04 am

Hi Craig,

In Basic routines

Thanks

chulett · Post by **chulett** » Wed Feb 25, 2015 12:14 am

Simplest way would be to leverage the command line interface, say for instance sqlplus for Oracle. What database are we talking about?

Or you could search here for example usage of the BCI functions.

synsog · Post by **synsog** » Wed Feb 25, 2015 12:19 am

ok. its Db2 database.

chulett · Post by **chulett** » Wed Feb 25, 2015 8:53 am

Can't help with specifics there, don't know DB2. However, it wouldn't be all that different from the Oracle example shown here, you just need to know the command line syntax for the DB2 equivalent.

kduke · Post by **kduke** » Sun Mar 01, 2015 9:01 pm

Part of EtlStats was a thing called completeness. I did a blog on it. I did several posts on it. It is a way to audit your data integration project whether it is an EDW, ODS or whatever. I wrote it when I was at Discovery Communications over 10 years ago. They were nice enough to let me share EtlStats and ETL Completeness.

You setup SQL to create a measure for the source and another for the target.

Target SQL:

Code: Select all

Select count(*) from source.customer;

Source SQL:

Code: Select all

Select count(*) from target.Customer_Dim;

Next you get each of these a name like CUST_DIM_SRC and CUST_DIM_TGT. You load each of these into a table called ETL_QA_SQL. You can create as many of the above measures you want. You need one measure for source and one for the target. The SQL can be anything you want as long as it only returns one numeric value. So you can sum(revenue) or count(1) or whatever.

Next you can generate a report where it calculates the percentage of target from the source. So if you have 93 records in the target and 100 in the source then 93 percent is your completeness rate. Sometimes this is valid. Because you might use MDM to merge source customers. You need to figure this out.

When you run the included DataStage jobs to calculate the latest set of measures then it stores the results in ETL_QA_RESULT. It has a field called CREATE_TS for when the measure was calculated. To get the latest it does a max() on this column.

The report uses a table called ETL_QA_RESULT_MAP where you map a source measure name like CUST_DIM_SRC to a target like CUST_DIM_TGT. I included the SQL to run the report in a file called EtlResultCompleteness.sql.

I have SQL Server, Oracle and DB2 versions of all the DDL and SqlScripts. If you can make it work great. If not then create you own. Each measure is sent to and ODBC connection which are parameterized. All the jobs are Server jobs. You want to convert them to PX then that is your problem. Server jobs are more forgiving and work just fine for something like this.

We are working on a product to sell which does the exact same thing. It will have a nice GUI front end and will cost some money.

DO NOT SEND ME AN EMAIL. If you have a question about this or anything else then post it here on this website. I will answer you here. I have used this at several customer sites. It works great. If you can find my blog then there are examples of the reports. They look nice and they are produced from DataStage jobs.

kduke · Post by **kduke** » Mon Mar 02, 2015 6:55 am

Inmon talked about "Sanity Checks" in his first book. This is a "Sanity Check". To this day I am shocked at how few companies have sanity checks and even fewer have automated these checks. This is a very simple process. It does very simple checks which run very fast on source and target systems as to consume very few resources. Every data integration project should do sanity checks. It should be part of best practices. You do your initial QA of a project then every time your ETL runs you do a sanity check.

The SQL above can be tweaked or modified to check incremental loads. If your incremental source load only grabs data where LAST_MOD_TS = yesterday then you can add a daily measure to check incremental. I usually leave in the aggregate measures. So I create CUST_DIM_SRC_TODAY and CUST_DIM_TGT_TODAY to check today's incremental load. So the completeness report has today's percentage and the percentage for the whole table.

You have all the jobs to run this. So you can modify them to meet your needs. You can also eliminate the reports I give you and write your own. You can even run your reports in Cognos or whatever reporting tool you need. You can copy my SQL from the completeness report and paste it into Cognos to help get you started. Run the SQL first. Make sure you understand it.

kduke · Post by **kduke** » Mon Mar 02, 2015 7:00 am

We do not get paid to answer your questions. So do your best to try what we suggest. We want to help you. We just do not want to waste our time when you are not trying to learn this stuff. Remember all our input is just suggestions. We are just trying to point you in the right direction. Take our ideas and make them your own. Hopefully you will post your solutions to help others to see how you adapted our suggestions to meet your needs.

The only pay we get is when someone says thanks or posts how we helped them solve their problem. Ray and Craig have helped lots of us. We should all thank them often.