Submitted Questions

  • Oracle Connector Stage and Oracle Enterprise Satge

    1. What is the difference between Oracle connector stage and Oracle enterprise stage. 2. If we can achieve Oracle enterprise stages task using Oracle connector stage then why Oracle enterprise stage exists?


    • Feb 3rd, 2017

    Oracle connector having test connection. Ie plugin which is not Present in the enterprise state


    • Aug 24th, 2016

    2) Ans: min of 3 stage variables are required according to my view. example: if the source has two columns cid, address. we want to remove the duplicates in cid, our job design follows like below se...

  • 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?


    • 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


    • Mar 27th, 2017

    Take two stage variable
    ST1 := if ST2 inputcolumn then 1 else 0
    ST2 := inputcolumn

    take the output of ST1 to a column "Check" and in constraints write a condition , check =1. this will filter the records with value 0

  • Load 1 input column values (with delemeter) into different target columns

    I have column X which having 3 values A;B;C. How can I load these values into 3 diff cols in the target. If tomorrow I will receive 100 values for same single column I will load into 100 target cols respectively (Same de-limiter) ?


    • Mar 2nd, 2018

    Using Field function. Field(column,delimeter,1) and so on


    • Mar 3rd, 2017

    Loop through the incoming field using looping functionality in transformer. Extract the individual values using field function and the iteration number. Output the individual values to the output link...

  • Handle Rejects in Transformer

    How to handle rejects in Transformer & How many rejects a xfm support?


    • Aug 24th, 2016

    The transformer only rejects the data of a column which has nulls and that column is used in the expression then that type data is rejected by transformer stage

    No other data is rejected by Transformer Stage
    Only one reject link supported by transformer

  • Minimum Number of Input and Output for Lookup

    What is the minimum number of input and output required for Lookup ?


    • Aug 24th, 2016

    Min of 2 inputs is required by lookup stage and 1 output link is mandatory for any type of lookup i.e. we have 4 types of lookups from 8.0 version those Normal lookup, sparse lookup (only supports 2 inputs one is primary and second is reference), range lookup, caseless lookup

  • Identify Normal and Sparse Lookup Datastage Job

    By seeing a Datastage job how you will identify which one is Normal lookup and which one is Sparse Lookup?


    • May 23rd, 2017

    Sparse look up can be used only for DB stages as Source and Target where as Normal can be used for both files as well as DB stages


    • Mar 30th, 2017

    It can be viewed when you open the Oracle connector stage and where the lookup type is mentioned.
    By default lookup type is normal

  • Split the Input Columns into Different Target

    I have a source file having 4 columns. How can I store first 2 columns values into one target and the next 2 columns values into another target? Can anyone suggest me here how can I achieve this using Copy stage and without using Copy Stage?


    • Aug 4th, 2016


    you can try with 1) copy stage & 2) transformer stage


  • Merge Two Columns into One Column in Target

    I have a file having columns C1,C2,C3,C4,C5 with comma delimited.In target I want store the first two columns value into one column. Like below Input Output I need -------------- ---------------------- C1 C2 C3 C4 C5 C1(C1 & C2 value) C3 C4 C5 That means my input is 5 columns & Output will be 4 columns. Can anyone suggest how Can I achieve this in DS job ?


    • Aug 26th, 2016

    Create a new stage variable and using concatenation operator combine first two columns data and map it to a column.


    • Aug 26th, 2016

    You can try using copy & tx stages

  • dsjob run command in Unix platform

    We are using below command in Unix to run a Datastage Job: dsjob run mode project_name job_name When we are executing the above command in Unix what exactly it returns?


    • Aug 4th, 2016

    is it question for reviewers or your doubt ?
    you got answer if you run the command.

    Thanks !

  • Track Source File name in Target File

    I have 5 source files. In target I need to write it into a single file. But in the output file I need the corresponding input file name i.e which records are coming from which source file. How to achieve this?


    • Aug 8th, 2016

    Hi Aloka we can use seq file properties -> Options -> File Name Column = FileNameCol
    you will get source file name to each column. You can identify easily...


    • Aug 4th, 2016

    Use file name column option in seq file stage & do funnel then you will get required output.

    Thanks !

  • Print Minimum & Maximum Salary of Respective Employee

    I have an input file in the below format : NAME SAL ---- --- A 4000 B 3000 C 8000 A 2000 B 7000 C 5000 B 2000 C 9000 A 1000 If I will use Sort ---> Aggregator Group by then it will give 3 columns like NAME,MAX(),MIN() NAME MAX() MIN() ---- ----- ----- A 4000 1000 But my requirement is to generate the output which will give the maximum & minimum salary...


    • Apr 7th, 2017

    Use Seq File->Aggregator Stage (group by Salary, Emp_Name and find min, max salary)->use Pivot(horizontal Pivot)

  • Load 10 Input files into 10 Target tables at a time

    I have the 10 input file like F1,F2...F10 then I need to load these 10 input files into 10 target Output tables like T1,T2...T10. Here is the scenario for 10 tables But in future If i will receive 100 input files then I need to load it into respective 100 target tables. After loading the input files into target tables I need a confirmation in the respective target tables(By input File name) Please...


    • Jun 27th, 2017

    This can be achieved by creating multiple instance job and parameterizing the source file name, table name and also need to enable run time column propagation.


    • Apr 24th, 2017

    We can do that allowing multiple instances for a single job in the job properties tab.

  • Generate the Occurrence Number in Output File

    How to generate the occurrence no in out put file for the respective no of records in input file I need a count in the target file how many times the same record is available in input file. INPUT ----- ID NAME LOC 20 B Y 10 A X 20 B Y 30 C Z 10 A X 10 A X 20 B Y 20 B Y 30 C Z OUTPUT I NEED ----------------------- ID NAME LOC OCCURENCE 10 A X ...


    • Jul 22nd, 2016

    Thanks Sri for your reply. After using 3 stage variable in xfm we can generate the count of the respective record.But after that i need no remove the duplicates to get the unique respective record in...


    • Jul 20th, 2016

    First sort the incoming rows by ID, NAME and LOC.
    Then take three stage variables in the transformer

    StageVar2=if StageVar=StageVar1 then StageVar2+1 else 1
    map the StageVar2 to the output link column

  • How to automate a Datastage job with out using Sequencer

    Without using Sequencer how to design a Datastage job which normally receives input file to run. If tomorrow we will not receive any input file Job should not fail. It will run successfully without any warnings.


    • Sep 6th, 2016

    Hi Aloka,

    you can achieve this by using unix shell scripting and schedule the job/script in any third party scheduling tool.

    Thanks !


    • Sep 1st, 2016

    Using JCL (Job Control Language) we can achieve this.

  • Which Case you will go for star schema and snow flake schema

    In your project Which Case you will go for star schema designing and which Case you will go for snow flake schema designing?


    • Jul 4th, 2016

    Snowflake schema is used for dimension Analysis. Eg: How many online accounts does an advertiser have.

    Star schema is used for metric analysis. Eg: What is the revenue of a customer.


    • Jun 30th, 2016

    Star schema --> Fact table is connect with different Dimension tables.
    Snowflake schema --> Fact table is connect with different dimension tables. And dimension table/tables refer to another dimensional table.

    Thanks !

  • Load a date field value from sequencial file to databse table with out using Transformer

    I have an input file in below format Name DOB ------ ------ A 10-05-1990 B 07-12-2000 Q1 -> How we will load the above file data into a target data base table in a simplest method without using Transformer ? Q2 -> How will load the Date column into 3 splitted columns(DD|MM|YYYY). Like below Name DOB ------ ------- A 10|05|1990 B 07|12|2000

    amulya panda

    • Aug 2nd, 2016

    Use in transformer stage and use Ereplace(SOURCE_COLUMN_NAME,"-","|")

    Gagandeep Singh

    • Jul 29th, 2016

    Define delimiter as - in Sequential file stage

  • Project Data Model

    Basically which data model do we use in the project?

    Pavan Kumar

    • Jul 5th, 2016

    Hi alok It totally depends on the project. 1) Entity Relation model or ER model is used when the data source is small as the number of joins are less. 2) Dimention model a) Star : Used for...

  • Which table will load first - Fact or Dimension Table?

    In Data warehouse which table will load first and why? Fact or Dimension?


    • Jul 20th, 2016

    Dimension table will load first and then fact tables.


    • Jun 30th, 2016

    Hi Aloka,
    Fact table is connect with different dimension tables with foreign key relationship. Fact table having only Ids & Measures. Dimension tables having detailed descriptions of the Ids.

    Thank you !

  • Change Partition to Auto in Join stage

    What will happen if we will change the partition to Auto in Join stage?


    • Feb 17th, 2017

    We usually go for join stage when the source input data is huge!!! If we select Hash partitioning only then it will improve the performance of job because Hash partitioning ensures that the data with ...


    • Jul 1st, 2016

    Thanks Suresh for your reply !
    But when Hash partition used..It will also verify for all the partition.
    It will be more helpful if you will elaborate more on this.

  • How to load 2 files data into a single file without using join stage

    I have 2 files having different meta data and 2 files does not have any common key. How can I load the 2 files data into a single file without using Join stage?


    • Feb 9th, 2017

    Can we use Merge?

    Pavan Kumar

    • Jul 11th, 2016

    If Metadata is same then you can use "Funnel stage". If tables are different then, use column generator to create a common column for both the tables, then using join stage (Full outer join), join the two different tables.

  • How Can we run a Datastage job form sequential mode to parallel mode

    Suppose I have designed a Datastage job using sequential file stage,It will run in sequential mode.How can I run it it Parallel mode.


    • Sep 13th, 2016

    Aloka, There are two ways in your example. In sequential file stage, number of readers per node and multiple nodes. These options will run an sequential stage in parallel.


    • Jul 24th, 2016

    In sequential file stage, we have two options No. of readers per node and reading from multiple nodes. We can run job in parallel mode.

  • How we can implement Bulk Collect methodology in Datastage

    I have faced an interview question recently. In database we are using Bulk Collect concept to load the bunch of records ata time. How we will achieve the same process in Datastage ? Can we use any paticular stage or any other methodology we can implement. Can any one help me on this. Thanks in advance. Aloka


    • Jun 10th, 2016

    Bulk collect is retrieving multiple rows. With a single fetch.. Datastage is by default working in parellel and processing multiple rows at a time. As per my knowledge there is no such specific way to implement this

    Deepak Sharma

    • Jun 9th, 2016

    I think your question is about bulk load of data. I have used Bulk Load method in Oracle Connector stage. Setting Bulk load property ensures that data gets loaded in the bulk load mode.