Row Counter

kalkavi · Post by **kalkavi** » Tue Aug 24, 2004 3:09 pm

Hi,

I'm new to Datastage. All the time i was working in cobol and we are used to increment the variables and use them in control reports. Can we build something in datastage similar to that where we can increment a variable and use the vairable to write a control report. Please let me know if this is confusing.

Thanks for the help
Kalyan

KeithM · Post by **KeithM** » Tue Aug 24, 2004 3:31 pm

Datastage does provide some system variable that may give you what you are looking for. @InRowNum and @OutRowNum will give you a count of the number of rows that come in and exit your process. You can also do it on your own using stage variables. If you have more detail on what you are trying to accomplish this forum could probably provide a better answer for you.

kalkavi · Post by **kalkavi** » Tue Aug 24, 2004 3:38 pm

We have three load odbc stages. At the end of the job i need to write a control report which should show # of rows loaded in stage1 , stage2 stage3.

Thanks for the help
Kalyan

kalkavi · Post by **kalkavi** » Tue Aug 24, 2004 3:57 pm

I tried using system variables. But the problem with that is counter is being added for each record. It does not summarize the value.

Thanks
Kalyan

KeithM · Post by **KeithM** » Tue Aug 24, 2004 4:33 pm

You could do it by inserting a hash file into your process. You would write a record out to the hash for each or your three output stages. The key would be a constant indicating either stage1, 2, or 3 and you would have a count field which you could populate with @OutRowNum. Then everytime it writes a record out it will also update the hash with the current record count. At the end the hash will contain the last record number which is also the count of the number of records written. Just be sure to have caching turned off on the hash file so that it gets updated for each record written.

dhiraj · Post by **dhiraj** » Tue Aug 24, 2004 7:03 pm

How are you generating that control report?

are you using a basic routine for that? if so i guess u can use dsgetloginfo() to get the number of records written to each link(odbc). Look in to the documentation for more info.

Dhiraj

ray.wurlod · Post by **ray.wurlod** » Tue Aug 24, 2004 9:00 pm

The link row counts are directly accessible using DSGetLinkInfo() function.

Code: Select all

RowCount = DSGetLinkInfo(hJob, StageName, LinkName, DSJ.LINKROWCOUNT)

chucksmith · Post by **chucksmith** » Wed Aug 25, 2004 7:12 am

If you want to do this in your job, then pass @OUTROWNUM from your transform into an aggregator. In the aggregator, use the LAST derivation. Finally, connect the aggregator to your ODBC stage. If required, place a transform between the aggregator and ODBC stage.

vmcburney · Post by **vmcburney** » Thu Aug 26, 2004 7:17 pm

I think Ray's approach is better, wait until the job has finished and then go and get the link counts. There is no point doing row counting from within the job, it is inefficient as you are counting for each row instead of counting just once, and it is difficult to write out.

There is example code on Ascential devnet submitted by Kim Duke that shows how to pull job stats via routines for the type of job reporting you are looking for.