report generation
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 14
- Joined: Wed Aug 31, 2005 3:47 pm
report generation
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
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
srikanth
etl developer
etl developer
Re: report generation
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
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
-
- Participant
- Posts: 14
- Joined: Wed Aug 31, 2005 3:47 pm
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
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
mujeebur wrote: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
srikanth
etl developer
etl developer
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 14
- Joined: Wed Aug 31, 2005 3:47 pm
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,
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,
ray.wurlod wrote: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 ...
srikanth
etl developer
etl developer
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 14
- Joined: Wed Aug 31, 2005 3:47 pm
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
DSguru file should be sent via mail......
Yea I can use notification stage..... Thanks for ur help
Sri
ray.wurlod wrote: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. ...
srikanth
etl developer
etl developer
-
- Participant
- Posts: 14
- Joined: Wed Aug 31, 2005 3:47 pm
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
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
srikanthkilaru wrote: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
ray.wurlod wrote: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. ...
srikanth
etl developer
etl developer
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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"
In the Sequential File stage set the write mode to "Append to existing file"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.