Datastage job scenario question

My input has a unique column-id with the values 10,20, can i get first record in one o/p file,last record in another o/p file and rest of the records in 3rd o/p file?

Questions by premox5   answers by premox5

Showing Answers 1 - 22 of 22 Answers


  • Feb 17th, 2014

You can use Loop to read the values and use Constraints to output the value to the desired output link.

  Was this answer useful?  Yes

Muralidhar Bolla

  • Apr 13th, 2014

In transformer using constraints we can achieve
1). Link--> @inrownum=1
2).link --> lastrow()
3). link --> click the otherwise condition


  • May 27th, 2014

In usual case, we need to use sort, filter and target file. But in this scenario, as the column in unique, they we cannot expect duplicates. Hence take the file of records into Filter stage and provide "Where Clause" as

i) =10
ii) >10 and <90
iii) =100
and let them link to the three files.

  Was this answer useful?  Yes


  • Apr 22nd, 2015

Take three stage variables as stgA,stgB,stgC .
stgA --columnname .
stgB --If columnname = then A else if columnname (inrouwnum >0 and currentdate=sysdate then A esle B
stgC --stgB --If columnname = then A else if columnname (inrouwnum <>1 and currentdate=sysdate then A esle B

Then put constrains on link level as per the required the it will work ...try .


  Was this answer useful?  Yes


  • Apr 23rd, 2015

Can you explain more about this explanation

  Was this answer useful?  Yes


  • May 24th, 2015

this answer works but is not scalable. LastRow() is not available in DS 8.0. the best option in my opinion would be to write a BASIC server routine to return the maximum row number and then proceed to use it in XFMR constraints.

  Was this answer useful?  Yes


  • May 27th, 2015

Consider below points (Source is Flat File):

1] I will generate Row Number for each & every row through "Row Number Column" property

2] I will use Aggregator to find MIN & MAX Row Numbers by grouping all the other columns of file

3] I will use Transformer with the below constraints:
-- Row_Number_Column = Min(Row_Number)
-- Row_Number_Column <> Min(Row_Number) AND Row_Number_Column <> Max(Row_Number)
-- Row_Number_Column = Max(Row_Number)
-- Finally make Execution Mode as sequential for all stages

  Was this answer useful?  Yes


  • Feb 22nd, 2016

I am getting two records instead of one for both first and last record.Is there anything related to partitioning as well?

  Was this answer useful?  Yes


  • Mar 30th, 2016

Its simple.
src --> Transform --> Targ1 & Targ2 & Targ3
Derviation for Transform:
Constrains :
@inrownum=1 --> trg1
Lastrow() --> trg2
select Otherwise --> trg3

  Was this answer useful?  Yes

Pradeep K Amboji

  • May 25th, 2016

@Abhinav . Hope youve the answer already so posting for general info. you were getting two records because your config file has 2 nodes. when using system variables (such as @INROWNUM or @OUTROWNUM ) you have to consider your config file. in you case you have make the transformer sequential or add node constraint

  Was this answer useful?  Yes


  • Nov 23rd, 2016

Using this logic getting 1st tgt --->10,20.
2nd tgt0---->20,30.,
3rd tgt ---> 0 records

  Was this answer useful?  Yes


  • Dec 2nd, 2016

The two records are coming due to partion go to advanced propeties and set execution mode sequential then you will get correct answer while using head or stage

  Was this answer useful?  Yes


  • Jan 22nd, 2017

src-->colgen(generate dmmy column with value 1)--->copy(3 o/ps)
1 o/p --> head
2 o/p -->tail
head and tail -->funnel
3rd copy and funnel -->join(fullouter)-->filter (not null on dummy col)

  Was this answer useful?  Yes

Sirisha d

  • Feb 10th, 2017

Yes, this is related to partitioning.
We have to make sure the processing stages where we write our logic to separate records is running in sequential mode.
The default mode is parallel for processing stages. So we have to explicitly change it to sequential mode to get this output.

  Was this answer useful?  Yes


  • Mar 2nd, 2017

In Transformer stage.
1st O/P FILE
Constraint: @INROWNUM=1
2nd O/P FILE
Constraint: LastRow()
3rd O/P FILE
Constraint: @INROWNUM<>1 AND NOT(LastRow())

  Was this answer useful?  Yes

Ayesha Farid

  • Apr 14th, 2017

File can be processed through UNIX Command to extract head -1 < filename > and tail -1 < filename > to get head and tail records to outfile1 and outfile3 and for rest of the records: read the file through parallel job (remove 1st row in sequence) and put filter (sequential file properties) : tail -1. pass output link to outfile2

  Was this answer useful?  Yes


  • Sep 14th, 2017

@inrow -1 extract to file,Lasrowoutp=extract to other file otherwise 3 file OR
head -1 => 1st file
tail -1 =>2nd file
otherwise link

  Was this answer useful?  Yes


  • Dec 12th, 2017

Note: It will work only transformer stage - Sequential execution mode (Not Parallel mode like 2 node)

  Was this answer useful?  Yes


  • Nov 25th, 2018

if the input has 2 partion then i would get 2 rows for lastrow() function. rest everyhing is correct

  Was this answer useful?  Yes


  • Nov 25th, 2018

If your output file is sequential file then you can you use filter property .

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions