Get the last record from transformer

[richdhan]

Hi,

I need to retrieve the last row from the transformer.i.e If the number of incoming records is 2 then only the 2nd record should be provided as output. I can use @INROWNUM in the constraint but sometimes there is only 1 incoming record. How do I handle this situation?

The transformer should either give the 1st record or the 2nd record based on the count of records(1 or 2). How do I set a constraint for the transformer. Is there a way to change the value of @INROWNUM dynamically in the constraint or any other solution

Note: The maximum number of incoming records is 2

Thanks in advance.
--Rich

As a man thinketh in his heart so does he become

[kcbland]

Use the aggregator stage and choose the LAST method.

[richdhan]

Hi Kenneth,

Thanks.I checked the aggregator stage but there are only two methods hash and sort. Can you explain what do you mean by LAST method. I also checked the parallel job developers guide but could not find any reference with regard to LAST method.

FYI: Iam sorry I had mentioned it as SERVER job it is actually a PARALLEL job.

Thanks in advance
--Rich

[Eric]

There is a system variable @OUTROWNUM as well as @INROWNUM. can that help?

[richdhan]

Hi Eric

As mentioned in my first post I used @INROWNUM in the constraint of the transofrmer. But I need the 1st or 2nd record based on the count of records.i.e @INROWNUM changes dynamically.

So thats were Iam held up. Thanks for yor advice. If you have used any other techique for this scenario pls let me know.

Thanks
--Rich

[MaheshKumar Sugunaraj]

Hi,

You could a Sort Stage and set the required column as descending and then USE @OUTROWNUM = 1 in the constraint and get the data populated.

Hope this helps.

Thanks
SMK

[Anjan Roy]

Use the aggregator stage and choose the LAST method.

I tried this in one of my jobs .. it works but it drastically reduces the performance...

Any other suggestions?

I want to write the Job Name, Input Records, Output Records, Start Time and End Time into a database table. I am doing this via a shared container and passing all the information..

[kduke]

You can buy DwNav or download EtlStats.zip from ADN or my tips page. It is my understanding that MetaStage will also get rows counts. EtlStats has dsx files to import into Ds7.1 or newer. If you are running on older versions then there are several API routines to call to get row counts. You have to get links names then you can get row counts. Several incomplete posts explaining how to do it but not much code posted. There are also several posts on how EtlStats works. It is very simple.

All of these methods work after the job is finished. Some are faster than others. It sounds like you do not need these numbers in the job itself. If not then it is fairly easy. We get our stats at the end of each sequence. If you feed the sequence name into DSJobReportDriver then it will get stats for all the jobs in the sequence. It will get all jobs period if you want. There are several other options as well like all jobs in a category or just one job.

[ogmios]

What I usually do is make a BASIC main job that calls all of the other jobs. You can keep track of the start time before you start the actual job, you can register the end time afterwards and with some interfaces calls of DataStage you can get at the number of rows flowing over specific links.

You can then store this information somewhere. I've found this the easiest so far.

Ogmios

[ray.wurlod]

Code: Select all

  ----->  Transformer  ----->  SeqFile  -----> 

On the output of the SeqFile stage, use tail -1 as a filter.

[Amos.Rosmarin]

Hi,

If your input is a file,
Write a routine that returns the number of rows in the file (using wc -l)
in the transformer creat a stage var that does:
If @INROWNUM = 1 Then routine.countLines(file name) else stage var

Then you can ask in your constraint @INROWNUM = count_stage_var
to get the last row


HTH,
Amos