Page 1 of 1
DSJobReport Output
Posted: Fri Dec 03, 2004 1:02 am
by rajeev_prabhuat
Hi,
I am having a job which calls the DSJobReport, it gives us a text file and it contains the status of the job, and when i view the data i get it in a single format, like as follows:
**************************************************
STATUS REPORT FOR JOB: ODBC_SEQ_LOAD
Generated: 2004-12-03 11:50:25
Job start time=2004-12-03 11:50:23
Job end time=2004-12-03 11:50:25
Job elapsed time=00:00:02
Job status=2 (Finished with warnings)
Now i want to convert this into a sequential file, how can i go about it with 6 different columns.
Regards,
Rajeev Prabhu
Posted: Fri Dec 03, 2004 1:20 am
by ray.wurlod
Process it with a DataStage job that reads the whole of each line from the text file as a single column, and includes a Transformer stage that generates the six column values.
Derive the six column values in six stage variables initialized to "" with derivation expressions of the form shown in this example for svColumn3:
Code: Select all
svColumn3 : (If @INROWNUM = 3 Then InLink.Line Else "")
Load the stage variables into the output columns.
Your output constraint is
Posted: Fri Dec 03, 2004 3:13 am
by rajeev_prabhuat
Hi Ray,
I did as you told i am getting the out put as follows:
"
**************************************************
STATUS REPORT FOR JOB: ODBC_SEQ_LOAD
Generated: 2004-12-03 11:50:25
Job start time=2004-12-03 11:50:23
Job end time=2004-12-03 11:50:25
Job elapsed time=00:00:02
Job status=2 (Finished with warnings)","","","","",""
this is not what i expected. I wanted in 6 different columns. It has created six columns but first is with the whole string and rest with "". I would like to tell you that, the above text is a singel line value from which i am reading. Hope you get my point.
Regards,
Rajeev Prabhu
ray.wurlod wrote:Process it with a DataStage job that reads the whole of each line from the text file as a single column, and includes a Transformer stage that generates the six column values.
Derive the six column values in six stage variables initialized to "" with derivation expressions of the form shown in this example for svColumn3:
Code: Select all
svColumn3 : (If @INROWNUM = 3 Then InLink.Line Else "")
Load the stage variables into the output columns.
Your output constraint is
Posted: Fri Dec 03, 2004 3:33 pm
by ray.wurlod
Did you do exactly as I asked?
Six stage variables initialized to "" (svColumn1 through svColumn6)
Six stage variable derivations.
For example svColumn3 is derived as svColumn3 : (If @INROWNUM = 3 Then InLink.Line Else "") (maybe you omitted the "svColumn3 :" thinking it was a label)
Six output columns, one containing the result of each stage variable.
A constraint expression @INROWNUM = 6.
I constructed it, and it works fine.
Posted: Sun Dec 05, 2004 9:59 pm
by rajeev_prabhuat
Hi Ray,
I have done as you have told, but i had missed out the constraint, but now i am not at all getting the output in the target sequential file. But when i remove the constraint, it is comming the col_1. Rest of the columns are comming blank. I have taken a print screen of the same, i would like to send it to you, how can i do that in this forum. Please guide.
Regards,
Rajeev Prabhu
ray.wurlod wrote:Did you do exactly as I asked?
Six stage variables initialized to "" (svColumn1 through svColumn6)
Six stage variable derivations.
For example svColumn3 is derived as svColumn3 : (If @INROWNUM = 3 Then InLink.Line Else "") (maybe you omitted the "svColumn3 :" thinking it was a label)
Six output columns, one containing the result of each stage variable.
A constraint expression @INROWNUM = 6.
I constructed it, and it works fine.
Posted: Mon Dec 06, 2004 1:00 am
by ray.wurlod
Click on my identifier; email is enabled from here. Please do the same in your profile; I will send you the image of the working version. Which is exactly as I described earlier.
Posted: Mon Dec 06, 2004 1:08 am
by ray.wurlod
For everyone else:
Input link has one column, called DSLink3.Line. Delimiter character is set to 000.
There are seven stage variables, all initialized to ""
- TrimLine
sv1
sv2
sv3
sv4
sv5
sv6
Derivations for the seven stage variables are
Code: Select all
TrimLine Trim(DSLink3.Line)
sv1 If @INROWNUM = 1 Then sv1 : Field(TrimLine,":",2,1) Else sv1
sv2 If @INROWNUM = 2 Then sv2 : Right(TrimLine,19) Else sv2
sv3 If @INROWNUM = 3 Then sv3 : Right(TrimLine,19) Else sv3
sv4 If @INROWNUM = 4 Then sv4 : Right(TrimLine,19) Else sv4
sv5 If @INROWNUM = 5 Then sv5 : Right(TrimLine,8) Else sv5
sv6 If @INROWNUM = 6 Then sv6 : DSLink3.Line Else sv6
Output constraint is
Output column derivations are
Code: Select all
JobName sv1
Generated sv2
Started sv3
Finished sv4
Elapsed sv5
JobStatus sv6
Anything not mentioned has its default value (apart, of course, from the file names).
Posted: Mon Dec 06, 2004 2:16 am
by vigneshra
But Ray, doing with simple unix commands will simplify the job to a great extent. Using the stream editors like sed will simplify the preparation of the sequential file without having to go for a job. If you are calling DSJobReport from the After-Job subroutine, you can very well use DataStage Basic file manipulation functions to do that. Am I right Ray ??
Regards,
Vignesh.
Posted: Mon Dec 06, 2004 6:31 am
by kduke
I posted a series of jobs on ADN and on my tips page which parse the XML version of these reports into either hash files or Oracle tables. You can download EtlStats.zip and modify these jobs to do whatever you want with this data. There are several posts on how to use these jobs. Do a search to see how to run these jobs.
Posted: Mon Dec 06, 2004 2:19 pm
by ray.wurlod
But Ray, doing with simple unix commands will simplify the job to a great extent. Using the stream editors like sed will simplify the preparation of the sequential file without having to go for a job. If you are calling DSJobReport from the After-Job subroutine, you can very well use DataStage Basic file manipulation functions to do that. Am I right Ray ??
Only if you're on UNIX or have UNIX tools on Windows. The original poster is on Windows.
My solution is platform-independent, and makes use of the tool without needing any external components.
Posted: Mon Dec 06, 2004 2:41 pm
by kduke
EtlStats works on Windows as well. If you have any problems installing then just let me know.