report generation

[srikanthkilaru]

Hi DS guru's, how ya all doing..?
well I got a question for u...
I got requirement to generate a report and target should be in excel and the format is below...

ROW# count diff AMOUNT
ROW1 100 - -
ROW2 200 - -
ROW3 400 - -
ROW4 600 2 1000 $
ROW5 300 3 2000$
ROW6 200 1 2000$

So, i know to get target in to excel we need to poplulate in .CSV format

here row1 has one query which gets row1 record
row 2 has one query which get row2 record
like that each row has separate query to generate this report

I am using server job for this so cud u help in this requirement
Really appreciate if u cud..

Thanks in advance....

Sri

[mujeebur]

I did similar thing by below, of course may be a better way that could find here!.

On the Job conrol , execute below:

OS = "UNIX"
JobNm = DSGetJobInfo(DSJ.ME, DSJ.JOBNAME)
SqlLoadCmd = ScriptFileDir : "ShellScript.sh " :SqlScriptFile
Call DSExecute(OS, SqlLoadCmd, Output, RtnCode)


Where DynSqlLd.sh : Is the Unix shell script
SqlScriptFiile : Sql Statement script which out put .csv format file

[srikanthkilaru]

Hi Mujeebur Thanks for prompt reply....

EACH record is generated with diffrent sql and there are like 7 sql's with generate each record in the report

And I was wondering what stage to use....? in between to source and target
source is DB2 and target will be .csv format..

ROW1 we get from one sql
ROW2 we get from diffrent sql...
like for every ROW we have to use diffrent SQL and populate into .csv format...

So If i use like 7 source DB2 stages for each SQL whats the transformer i can use between the DB2 and .csv target..

Thanks,
Sri

I did similar thing by below, of course may be a better way that could find here!.

On the Job conrol , execute below:

OS = "UNIX"
JobNm = DSGetJobInfo(DSJ.ME, DSJ.JOBNAME)
SqlLoadCmd = ScriptFileDir : "ShellScript.sh " :SqlScriptFile
Call DSExecute(OS, SqlLoadCmd, Output, RtnCode)


Where DynSqlLd.sh : Is the Unix shell script
SqlScriptFiile : Sql Statement script which out put .csv format file

[ray.wurlod]

How about seven different jobs, each appending to the CSV file? These could be run from a job sequence which creates/truncates the CSV file via an Execute Command activity.

[srikanthkilaru]

Hi Ray, Thanks ray....
I was looking to use link collector....where we can take seven inputs and target to .csv file....
and Is there any way in datastage or thru uinx i can send this .csv file after the ETL process is done.......

Really appreciate ur help....

Thanks,

How about seven different jobs, each appending to the CSV file? These could be run from a job sequence which creates/truncates the CSV file via an Execute Command activity.

Moderator ...

[DSguru2B]

where do you want to send this file?
if it is to some file server, you need to find out what mode of file transfer your client uses. We are doing the same here and we are using connect direct scripts to transfer files from unix onto some windows file server.
if you want the .csv file to be sent to you, you can have a notification stage in a sequence job and in the properties, you will see an option to mail a file to you as an attachment.
If thats what you want, make sure your attachment is well under the limit of File Attachment Size that your email server allows.
Cheers.

[ray.wurlod]

A Link Collector adds unnecessary complexity, to my mind. You are seeking to perform seven separate operations. Make your design modular; use seven separate (and simple to maintain) jobs.

[srikanthkilaru]

Yea Ray I will according to you......
DSguru file should be sent via mail......
Yea I can use notification stage..... Thanks for ur help

Sri

A Link Collector adds unnecessary complexity, to my mind. You are seeking to perform seven separate operations. Make your design modular; use seven separate (and simple to maintain) jobs. ...

[srikanthkilaru]

Hi Thanks, for the help....
got an another question...
I need to generate target file with heading validation report... so can do this in datastage....or anyother ideas

Validation Report
column1 column2 column3
xxxxxxx xxxxxxx xxxxxx
xxxxxxx xxxxxxx xxxxxx

Thanks in advance...

Sri

Yea Ray I will according to you......
DSguru file should be sent via mail......
Yea I can use notification stage..... Thanks for ur help

Sri

A Link Collector adds unnecessary complexity, to my mind. You are seeking to perform seven separate operations. Make your design modular; use seven separate (and simple to maintain) jobs. ...

[ray.wurlod]

Use ExecSH in a before-job or before-stage subroutine to invoke echo (with redirection of stdout) to write the heading line.
In the Sequential File stage set the write mode to "Append to existing file"