DSJobReport Output
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 136
- Joined: Wed Sep 29, 2004 5:56 am
- Location: Chennai
- Contact:
DSJobReport Output
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:
Load the stage variables into the output columns.
Your output constraint is
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 "")
Your output constraint is
Code: Select all
@INROWNUM = 6
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: 136
- Joined: Wed Sep 29, 2004 5:56 am
- Location: Chennai
- Contact:
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
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:Load the stage variables into the output columns.Code: Select all
svColumn3 : (If @INROWNUM = 3 Then InLink.Line Else "")
Your output constraint isCode: Select all
@INROWNUM = 6
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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: 136
- Joined: Wed Sep 29, 2004 5:56 am
- Location: Chennai
- Contact:
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
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 ""
Output constraint is
Output column derivations are
Anything not mentioned has its default value (apart, of course, from the file names).
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
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
Code: Select all
@INROWNUM = 6
Code: Select all
JobName sv1
Generated sv2
Started sv3
Finished sv4
Elapsed sv5
JobStatus sv6
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.
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.
Regards,
Vignesh.
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.
Mamu Kim
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Only if you're on UNIX or have UNIX tools on Windows. The original poster is on Windows.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 ??
My solution is platform-independent, and makes use of the tool without needing any external components.
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.