Dataset stage and sequential file stage
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?
- Jan 22nd, 2023
Deepak!, how to load a sequential file stage having data more than 2 Gb?
- 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
Job has no config file
If the paralle or seq job do not have configuration file..will it run successfully or not. ( If the job throws any error if yes what type of error it will be)
- Aug 14th, 2022
No.it doesnt run
- Oct 25th, 2019
Job will run using the default config file. It will not bort
Delete Duplicates Using Transformer
1.Without using stage variable how can we delete the duplicates using Transformer?
2.If we will remove duplicates using Transformer then minimum how many stage variable required for this?
- Jul 10th, 2021
The prerequisite for this is data should be partitioned by a key and sorted.
Later, follow the below steps:
S2 = if input.column = S1 then 0 else 1
S1 = input.column
In constraint : S2=1
And, you will get the expected result.
- May 26th, 2017
In the transformer STAGE PROPERTIES --> select the INPUT table --> Under that select PARTITIONING --> select HASH --> Select the I/P column and select the Sort and Unique checkbox.
Compile and Run
Datastage Real time scenario
In source table data like this
but I want traget table like this
pls any one one solve this........
- Jul 10th, 2021
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
- Dec 8th, 2020
You can solve this problem by using two way 1. using a transformer 2. using aggregator and transformer By using 1st method you can create 2 stage variable i.e Stagevar1 and Stagevar2 In StageVar1 wr...
Single Column Source Data
Source have single column data, How can I get Target like row 1 goes to target 1, row 2 goes to target 2.
How can you achieve this scenario?
- May 9th, 2020
We can use the option "generate multiple files" in sequential file stage and then assign the data column as Key. SO with every change in key it will generate a new target file.
- Dec 7th, 2019
There are thousand rows, who will write thousand different where conditions and then will configure 1000 different files, is there any other solution which dynamic?
Having single input source want three different outputs.
I have a source file having data like:
i want three output from the above input file, these output would be:
1) having only unique records no duplicates should be there. Like:
2) having only duplicate records,...
- Dec 30th, 2019
File -->> Sort -->> Transformer -->> O/P File
Define the key field and in Property set Key Change column as TRUE.
In Constraints put the below condition:
KeyChange=1 for Unique record O/P
KeyChange=0 for Duplicate O/P.
- Nov 20th, 2019
1) Unique- send as it is with link sort
2) Duplicate only- Loop through Count of each row count (constraint Count . 1)
3)Unique Only- Constraints Count = 1
How to convert multiple rows into single row in datastage
Can you please solve this in datastage..
my input is
want output as
pls explain with steps
thanking in advance...
- 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...
- Aug 8th, 2019
Can you please elaborate the Staging variable condition? how we define staging variable and how we are putting in conditon
Pre Row Salary, Next Row Salary, Rank and Dense Rank
How to find out second highest salary as group wise by using datastage? Can you explain scenarios like pre row salary, next row salary, rank and dense rank
- Sep 6th, 2019
Pre and next row salary can be get using analytical functions lag() and lead() functions in SQL
how i can achieve below logic in datastage
A B C D
B C D
it can be number and symbol also
- Jul 27th, 2019
Let assume the input file has one column and the value is "ABCD"
Seq file --> Transformer --> Seq File
In Transformer Create Two stage variable
Len(Inut_Field) -> stageVar
StageVar + 1 -> StageVar1
Under looping give the iteration as @ITERATION
Compile Source Records
I have 10 Million records in source and target 5 million records were populated then job was aborted again i go and compile and run the job when that time job will run from 5million 1 record to remain records how?
- Mar 9th, 2019
Use Upsert function in database connector stage. Or use lookup stage to perform lookup on the source and reference as your target data and load into your target stage
Convert 1 file into 3 files
we want to generate output like:
Col1 Col2 Col3
C1 C2 C3
C4 C5 C6
C7 C8 C9
Can anyone please help me on this scenario?
- Mar 4th, 2019
Use below constraints in xfm to move the data into 3 different columns. Make the xfm partition to run sequential.
mod(@inrownum,3) = 1
mod(@inrownum,3) = 2
mod(@inrownum,3) = 0
This will move first record to first output file, second to second and third record to third reference link.
- Nov 25th, 2018
Partion by Range
Datastage job scenario question
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?
- Nov 25th, 2018
If your output file is sequential file then you can you use filter property .
- Nov 25th, 2018
If the input has 2 partion then i would get 2 rows for lastrow() function. rest everyhing is correct
Load Different Data Files in Single Target Table
How to load the different data files in single target table using one data stage job? Files are having different name and data but target table in static.
- Nov 17th, 2018
If the input file has same metadata and matching file name pattern, then 1. Set Sequential file Read method property to "File Pattern" 2. Provide File Pattern Else, if different file name, then sp...
- Jun 18th, 2018
You can write a multi instance job. And parameterise the file name..by keeping table name constant..
How to count the records except header and footer in a sequential file
I have a sequential file it is having some records and the same file is having header and footer now my question is how to count the records in a file which is not counts the header and footer records and then transform the records in to target and then again we fetch the header and footer to that file which records are matched with the header
- Oct 24th, 2018
Cat | sed 1d ; $d | wc -l
sed 1d --- for removing 1st row
sed $d ---- for removing last row
How to get top five rows in DataStage?
How to get top five rows in DataStage? I tried to use @INROWNUM,@OUTROWNUM system variables in transformer..but they are not giving unique sequential numbers for every row...please help!
Thanks in advance!!
- Sep 24th, 2018
Also you can get form sequential file (Read first row (5))
- Oct 9th, 2017
If you want to use transformer only so you need to sort that data first....and after that you need to know how many no. of partitions you have in your transformer stage. Because @inrownum and @outrown...
1 input file with 100 fields, Load 1st field to 1st target and so on...
Input file contains 100 fields, target is
1st field is load into Target 1
2nd field is load into Target 2
3rd field is load into Target3
100th field is load into Target100
How can achieve this scenario ?
- Sep 18th, 2018
Step 1: take the columns names in a col_lits.txt file. $head -1 file_name > col_list #IFS="," (say the file was a comma seprated file or if other than , can use tr "other delimiter" ,) for colnm in...
- Apr 5th, 2018
Create 100 target with that one field in DML. Then use a reformat having input file and map each field to target as per requirement.
What about System variables?
- May 23rd, 2018
Out of all those , i am not able to find/call them in a transformer , like @LOGNAME , while i use this in a stage variable, it throws error , saying its not defined. I am using Datastage 11.5 version. Any idea how to get this done ?
- Apr 15th, 2009
The variables which used in through out the system not in singale project is called System variables .some of the system variables are @FALSE,@PARTITIONNUM ,@NUMPARTITIONS,@TRUE, which are avaialable in Transformer
Single Datastage Job Different Output
I have to design a job, if I run the job it should get 10 records from emp, if another person runs he should get all the records form emp. How to design such a job?
- May 2nd, 2018
If you are using Sequential file stage, there we have an option "Read the first row", make it parametrized.
and pass the value to the job using dsjob command as the parameter. also, you may like to use Invocation ID as you need to run the job with the different instances.
123abc456xyz convert to 123456 ---file1 abcxyz ----file2
In a Column 123abc456xyz convert into two files like 123456 fil1 and abcxyz file2
- Apr 8th, 2018
By using convert fun we can achieve it:
in Tx->functions->string-> select convert
Convert (0123456789,,inputLink) ==
Convert (abced....xyz, ,InputLink)==
Target2 : 123456
Which partition we have to use for Aggregate Stage in parallel jobs ?
- Mar 15th, 2018
Hash partitioning and in link sorting on grouping keys give better performance and correct results if it is in parallel mode and Auto partition will give correct results if there is no sorting happened only one of the keys we are grouping in previous stage
- Jul 12th, 2017
Hello Harish I would like to ask you a question How I can choose the appropriate partition for each stage and job how can I analyse situation
DataStage Interview Questions