DSJobReport Output

[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

[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

Code: Select all

@INROWNUM = 6

[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

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

Code: Select all

@INROWNUM = 6

[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.

[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

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.

[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.

[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

Code: Select all

@INROWNUM = 6

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).

[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.

[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.

[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.

[kduke]

EtlStats works on Windows as well. If you have any problems installing then just let me know.