My input has a unique column-id with the values 10,20,30.....how 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?

Bhargav konduru

• Nov 21st, 2024

In TFM stage do the below constraints 1). Link--> @inrownum=1 2).link --> lastrow() 3). link --> click the otherwise condition Some more things needs to do with above 1.In TFM properties set pa...

Prashant

• Nov 25th, 2018

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

Answer Question  Select Best Answer

input file A contains12345678910input file B contains6789101112131415Output file X contains12345Output file y contains678910Output file z contains1112131415How can we do in this in a single ds job in px ?....could you please give me the logic to implement ???

Read Best Answer

Editorial / Best Answer

Answered by: vinod chowdary

• Jul 28th, 2011

---

Hello guy's, I would like to solve this by using the Change capture stage. First, i am going to use source as A and refrerence as B both of them are connected to Change capture stage. From, change capture stage it connected to filter stage and then targets X,Y and Z. In the filter stage: keychange column=2 it goes to X [1,2,3,4,5] Keychange column=0 it goes to Y [6,7,8,9,10] Keychange column=1 it goes to Z [11,12,13,14,15] Revert me PLz

Priya Ranjan kumar

• Aug 28th, 2017

You can solve this question in easily way like.
First use the funnel to take the input data from different sources and used check box for unique data(No duplicate data) then
you can use transformer where you can apply logic like. @INROW

Pavan Kumar

• Jul 30th, 2015

Using change capture stage:

File1(Master),File2----> change capture---> Filter ---->
T1, T2, T3
In Filter,
Change_code=1 then send then to T1-->(Insert records)
Change_code=0 then send then to T2-->(Copy records)
Change_code=2 then send then to T3-->(Delete records)

Improve Answer

I have source like this

a,b,c,1,2,3 ( All this in one column)

I wanna target following below

a,b,c,1 ( Ist row)
a,b,c,2 (2nd row)
a,b,c,3 (3rd row)

Thanks in advance.

lalit

• Sep 12th, 2017

Its really simple Take one sequential file which contains data a,b,c,1,2,3 ( First line column name=false) Then take one copy stage with three output link and then copy a,b,c,1 at one output link ...

Ram

• Mar 10th, 2016

Hi Tulasi,
This is for you..

Src -->Trns (use Function Str(inputcolumn,@inputrow) --> Trg

Thats all !

Answer Question  Select Best Answer

source table
name
A
A
B
B
B
C
C
D

In source table data like this
but I want traget table like this
name count
A 1
A 2
B 1
B 2
B 3
C 1
C 2
D 1


pls any one one solve this........

sachin kumar

• Jun 11th, 2024

Seq----transformer---seq.
Go to the stage variable properties create two staging variables first is s1 and second is s2 .
s2 = if inputcolumn = s1 then s2 +1 else 1
s1= inputcolumn
create new column is count.
s2= count

Rams

• Jul 10th, 2021

Seqfile---Sort----Trans-seqfile
Sort Stage---KeyChange column true
Transfor- in advance tab set mode-Sequence
Create one stage Variable: Stagevar= if KeyChangecol=1 then 1 else Stagevar+1
Create on extracolumn in output give Stagevar=Newcolname

Answer Question  Select Best Answer

Hi,

Can you please solve this in datastage..

my input is
name,city
johnson,newyork
johnson,mumbai
johnson,delhi

want output as
name,city1,city2,city3
johnson,newyork,mumbai,delhi

pls explain with steps
thanking in advance...

Ruchi Gautami

• Dec 30th, 2019

File -->> Sort -->> Transformer -->> O/P File Sort: Enable key column change as true. Transformer: Define one stage variable SVar if key_change =1 then city else SVar:,:city Define 3 columns Cit...

Alisha

• Aug 8th, 2019

Can you please elaborate the Staging variable condition? how we define staging variable and how we are putting in conditon

Answer Question  Select Best Answer

A sequential file has 8 records with one column, below are the values in the column separated by space,1 1 2 2 3 4 5 6In a parallel job after reading the sequential file 2 more sequential files should be created, one with duplicate records and the other without duplicates.File 1 records separated by space: 1 1 2 2File 2 records separated by space: 3 4 5 6How will you do it

Ram

• Jul 1st, 2016

Hi Pooja, Its absolutely possible.. Src --> Copy(linksort) ---> Aggr(count rows) another link from copy -----------------> Join (Copy & aggr) ---> Filter(count=1 for trg1 and co...

Pooja Trivedi

• Jun 30th, 2016

This will not give the desired output as the we want the duplicate records also n number of times where n is the number of record present in the file.

Answer Question  Select Best Answer

I have data in Table as

ID
1
2
3
1
4
3
3
5
6
6
7

Now I want to have Output as repeatating records in one table & non-repeatating in another table

Table 1 :
Id
2
4
5
7

Table 2:
ID
1
1
3
3
3
6
6

anirudh

• May 9th, 2017

You can use sort->aggregrator->count_rows->transformer stage write constraints row_count>1 to one file and row_count>1 to another output file

Neha

• Apr 7th, 2017

Seq File-> Aggregator (Count group by ID)->Transformer(Two output link , Use @Iteration1 and put @Iteration value in OpLnk2

Answer Question  Select Best Answer

what is the difference between dataset stage and sequential file stage? even one more to be added here, What is the difference between dataset stage and fileset stage?

Sourabh Kulkarni

• Jan 22nd, 2023

Deepak!, how to load a sequential file stage having data more than 2 Gb?

ravikumar

• Jul 24th, 2017

1) Sequential file stage we can use UNIX commands and in dataset stage we cant use UNIX commands.
2) Sequential file stage filename save as .txt and dataset - .ds

Answer Question  Select Best Answer

We use a) "Iconv" function - Internal Convertion. b) "Oconv" function - External Convertion. Function to convert mm/dd/yyyy format to yyyy-dd-mm is Oconv(Iconv(Filedname,"D/MDY[2,2,4]"),"D-MDY[2,2,4]") 

vipul choudhary

• Aug 22nd, 2013

There are two types of hash file

1. modulus hash file- file size increases randomly
2. splitting hash file-file size decreases randomly

Muralidhar

• Jul 29th, 2013

By default datastage date format is :yyyy-mm-dd.The input is mm/dd/yyyy. String_to_date(inputdate,"%mm%dd%yyyy")

Answer Question  Select Best Answer

I have 3 jobs A,B & C, which are dependent each other, I want to run A & C jobs daily and B job run only on Sunday. How can I do it?

sudheer

• Aug 20th, 2015

A Job ----> Routine Activity ---Sunday---> B Job ----> C Job
--- Not Sunday -----> C Job

bimaljsr@gmail.com

• Aug 10th, 2015

Sequence Design -

A Job---> (B sequence explained later) --> C Job

B sequence contents
Dummy job to check specific day ---> condition activity with 2 link --> if Sunday run job B, if not Sunday run dummy job and finish.

Answer Question  Select Best Answer

ss

• Mar 28th, 2013

JJ, you are wrong.

Apt_ Dump_Score
By using this only we can find.

jj

• Nov 26th, 2012

1 conductor process
3 Section leader process
3 player process ( modify and filter are combined)
1 source sequential file
1 target sequential file
Total : 9

Answer Question  Select Best Answer

If you have a huge volume of data to be referenced, which stage will you use? Join or Lookup stage? Why

Gopi N

• Jul 11th, 2012

If we have a Huge data at reference defiantly we should go to Join stage because Look up takes much time to process hence we will use entire partition but in Join stage we will give the sorted data and it will simplify better than Look-up

arjunreddy

• Aug 24th, 2011

Look up stage

Answer Question  Select Best Answer

How to remove leading zeros in data and transform data to target?

Sunitha

• Dec 2nd, 2013

The main differences between 8.1 and 8.5 are 8.5 has Input looping ,Output looping. 8.5 has saving ,editing and compling is 40% faster. 8.5 has functions like LastRow,LastInGroup and Iteration system...

Rupesh Agrawal

• Nov 8th, 2013

Convert data to integer and convert back to string.

Answer Question  Select Best Answer

i have a sequence of job in DataStage which is taking more than 4 hrs which is supposed to complete in less than 1 hr
so what could be the possibilities to take much longer time than expected?

RamyaSujith

• Oct 6th, 2014

Check if any stage is reading/processing data sequentially which could have been done in parallel.

Raveena Mittal

• Sep 11th, 2014

When sometimes ,some DML statements are executed in the db and they are not committed,then also it may keep the job running for long hours

Answer Question  Select Best Answer

input is
cola
1
2
3
this should be populated at the output as

cola
1
2 2
3 3 3

satish

• Apr 2nd, 2015

In Transformer use stage variable Str(%Input_column%,%Input_column%)

sai3689

 Profile Answers by sai3689 Questions by sai3689

• Mar 17th, 2015

@iteration

Answer Question  Select Best Answer

can anyone pls solve in datastage.........
source column is..
A
A
B
B
B
C
C
D
........want output columns(2 cols) as.........
A 1
A 2
B 1
B 2
B 3
C 1
C 2
D 1

THANX IN ADVANCE.

Amulya Kumar Panda

• Jul 11th, 2016

Job flow sequential_file-> eggregate->sort->tranformer-> sequential

fast aggregate stage use count rows and add extra column seq_no
next stage sort -key source column sort order ascending.
tranformer stage use loop while ex-@ITERATION

Kumar

• Jul 8th, 2016

Seq file>transformer>Seq file
1.Mode of execution in transformer must be sequential and data must be sorted on col1
2. Transformer logic:
Stv1=Input column
Stv2=if Stv1=Stv3 Then Stv2+1 Else 1
Stv3=Stv1
3.Output of transformer
Col1,Col2
Input column,Stv2

Answer Question  Select Best Answer

In almost all cases we have to delete the data inserted by this from DB manually and fix the job and then run the job again.

Read Best Answer

Editorial / Best Answer

ritu singhai  

 Profile Answers by ritu singhai Questions by ritu singhai

• Member Since Jul-2008 | Jul 5th, 2008

---

Have you set the compilation options for the sequence so that in case job aborts, you need not to run it from from the first job. By selecting that compilation option you can run that aborted sequence from the point the sequence was aborted.
Like for example, you have 10 jobs(job1, job2, job3 etc.) in a sequence and the job 5 aborts, then by checking "Add checkpoints so sequence is restartable on failure" and "Automatically handle activities that fail" you can restart this sequence from job 5 only. it will not run the jobs 1,2,3 and 4.
Please check these options in your sequence.
Hope this helps.

Mallikarjuna_G

 Profile Answers by Mallikarjuna_G Questions by Mallikarjuna_G

• Aug 10th, 2013

Two things needs to be handled when a Job Sequence aborts. 1. We must have an exception handling code that notifies us about the failure 2. When we re-run the sequence after fixing the Sequence, it ...

Riten

• May 30th, 2012

To Handle Aborted Sequence These are the steps to be taken: First Add Terminator to Job Sequence and Choose Terminator with Other option(Trigger in Job sequence) ( if job fails it will go to Other lin...

Improve Answer

How do you you delete header and footer on the source sequential file and how do you create header and footer on target sequential file using datastage?

Monisha jain

• Jul 31st, 2017

In sequential file using filter property where we can use UNIX command: sed -e 1d -e 1p

sri

• Mar 28th, 2017

Use UNIX command in the sequential file stage
sed -n1!p filename|sed -n $!p

Answer Question  Select Best Answer

How to Delete Dataset in Datastage Parallel Jobs?

ramoojobs

 Profile Answers by ramoojobs Questions by ramoojobs

• May 11th, 2010

From Unix / Linux1) Go to the path where DataSet exists2) Use ORCHADMIN DELETE

siddy76

 Profile Answers by siddy76 Questions by siddy76

• Dec 8th, 2009

Go to the particular folder where the datasets are located, then typeorchadmin rm <datasetname.ds>

Answer Question  Select Best Answer

Can any one help....
Source is.....
a
b
c
d

want target as.....
a,b,c,d

thanks in advance

Rajendra

• Jul 30th, 2024

Without "Pivot stage"
Source --> Transfer --> Target
In Transfermer Stage (Sequential mode)
1) Stage Variable: sv = sv:,:inputcolumn
2) Constraint: LastRow()
3) Derivation: Trim(sv,,,"L")

kalyanreddy

• Jan 30th, 2024

Using with pivot stage, we can convert the rows into columns ; source --> pivot -->target.

Answer Question  Select Best Answer