Issue with getting table row counts using DataStage
Posted: Tue May 10, 2011 2:44 pm
Scenario: I am trying to get row counts for all the tables that exist in a Schema and put them into a "csv" file which I am e-mailing to the team (an auditing process)
My approach: I have 2 jobs (PX) in place for this. Job-1 queries the systables and gets the list (<schema>.<table> format). Job-2 is coded in a way to accept a table name as input parameter and do a simple "select count(*) from #param#" and place it in a csv. I have a job sequence in place which is as follows:
Unix Command 1 activity uses the following command:
User Variable Activity has an Ereplace function to remove any "\n" new line characters.
Problem: The sequence runs fine until the last table and then it aborts for the last table. The log says (an example):
Can anyone shed some light into this and guide me as to where I am going wrong?
My approach: I have 2 jobs (PX) in place for this. Job-1 queries the systables and gets the list (<schema>.<table> format). Job-2 is coded in a way to accept a table name as input parameter and do a simple "select count(*) from #param#" and place it in a csv. I have a job sequence in place which is as follows:
Code: Select all
Job-1 --> Unix Command 1 --> Start Loop
| |
| |
| User Variable Activity
| |
| |
| Job-2
| |
| |
|----- End-loop --> notify (with attachment)
Code: Select all
paste -sd "," <file name -- created from Job-1 -- tables list>
Code: Select all
Trim( Ereplace( Ereplace( fmt_table_list_delimited.$CommandOutput, char(10), '' ), char(13), '' ) )
Initially, I wasn't having the User Variable activity to handle new lines in the job design. Later, I suspected the issue is with the trailing New line character and used the Ereplace command. I used it before the loop as well as inside the loop. But, still this abort is occurring and I am unable to understand why?!(@Start_loop): Loop through list (55 items): iteration 55
(@Job-2): Will execute error activity: Exception
(DSSendMail): Sent message to ...
Can anyone shed some light into this and guide me as to where I am going wrong?