Issue with getting table row counts using DataStage

[vivekgadwal]

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:

Code: Select all

Job-1 --> Unix Command 1 --> Start Loop
                         |           |
                         |           |
                         |        User Variable Activity
                         |           |
                         |           |
                         |          Job-2
                         |           |
                         |           |
                         |-----  End-loop --> notify (with attachment)

Unix Command 1 activity uses the following command:

Code: Select all

paste -sd "," <file name -- created from Job-1 -- tables list>

User Variable Activity has an Ereplace function to remove any "\n" new line characters.

Code: Select all

Trim( Ereplace( Ereplace( fmt_table_list_delimited.$CommandOutput, char(10), '' ), char(13), '' ) )

Problem: The sequence runs fine until the last table and then it aborts for the last table. The log says (an example):

(@Start_loop): Loop through list (55 items): iteration 55
(@Job-2): Will execute error activity: Exception
(DSSendMail): Sent message to ...

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?!

Can anyone shed some light into this and guide me as to where I am going wrong?

[ray.wurlod]

Do you have an empty line at the end of the list?

[vivekgadwal]

I haven't checked it in a hex editor. I probably should have. My suspicion arises from the fact that the process is simple enough that this is the only explanation I could think of. I will check it and let you know. If my earlier statement is wrong, please do let me know if there is something else.

Thanks.

[ray.wurlod]

My experience is that, when the final item in the list fails, it is usually for this reason. You can add an extra command to your UNIX command pipeline to remove empty final line, or you can interpolate a user variables activity to achieve the same end.

[vivekgadwal]

My experience is that, when the final item in the list fails, it is usually for this reason. You can add an extra command to your UNIX command pipeline to remove empty final line, or you can interpolate a user variables activity to achieve the same end.

Thanks for the reply. I agree with you. I added the User variable activity precisely for this reason. However, I am not achieving the desired result. Can you please verify and let me know if I am doing this stripping of new line character(s) properly?

[ray.wurlod]

My preferred mechanism is to use a tr command at the end of the UNIX command pipeline to translate the newlines into something else (usually pipe, but comma is OK if there are no commas in your data). For example

Code: Select all

ls -1 filepattern | tr '\n' '|'

Of course, this may have an empty line at the end, but I can test for this within the loop, or pipe it through a sed or awk command to stop it coming in in the first place. Or even grep for a newline at the beginning of the line before the tr command.

[vivekgadwal]

Thanks Ray.

I used your suggestion and tweaked my job design as such:

1) Piped the output of the paste command to the "tr" command to convert the new line into "|"
2) Used Ereplace to replace "|" with "".

This did the trick! :D