what is slowly changing dimension(SCD)? How do we rectify it in datastage.

sabz R

 Profile Answers by sabz R Questions by sabz R

• Nov 7th, 2014

We can use surrogate key generator to rectify this problem in datastage..

sabz

• Nov 5th, 2014

SCD is a common problem particular to database.This applies to cases where the attributes for a record varies over time.This can be solved by 3 types.type1,type2,type3

Answer Question  Select Best Answer

Explain some scenarios where a sequential file stage runs in parallel?

Chandra

• Jan 22nd, 2015

If you have multiple files ,need to change Read method to FILE PATTERN ,Then its run parallel mode

Balu

• Nov 25th, 2014

Changing the "no.of readers per node" to more than one . you can read a sequential file in parallel mode

Answer Question  Select Best Answer

i have one source file which contains the below data
input column: DOB varchar(8)
20000303
20000409
1999043
1999047
validate the Date if valid date else pass the default-1999-12-31,convert varchar to date

Sam

• Dec 17th, 2014

Hi, Check for valid date as below: 1.> Len of the string =8 then Date < sysdate else default date 2.> We use a) "Iconv" function - Internal Convertion. b) "Oconv" function - Exter...

sonali

• Dec 6th, 2014

In transformer stage use stringtodate(datecol,format)

Answer Question  Select Best Answer

11. Find The Output With out PIVOTE stage
Source File: Target
SNO, MATHS, PHYSICS, CHEMISTRY SNO, MARKS
101, 70, 80, 90 101, 70
102, 65, 55, 75 101, 80
101, 90
102, 65
102, 55
102, 75

sreeni

• Jun 22nd, 2016

Buy using Pivot You can archive.
seq--->Pivot(Hor)--->seq
In pivot properties write a derivation MATHS, PHYSICS, CHEMISTRY

DEEPAK KUMAR

• Jul 7th, 2015

Use @iteration function in transformer

Answer Question  Select Best Answer

Can any one help....
I have single column in Source like.....
a
b
c
d
want target as.....
a,b,c,d
thanks in advance

D Hema

• Apr 23rd, 2015

Use the stag variable in the transformer
StageVar=StageVar:,: input column name
go to the constraints :-Lastrow()
map that stage variable to the output column

jallu

• Apr 11th, 2015

By using pivot stage in datastage

Answer Question  Select Best Answer

Solve with steps in datastage

employee_id, salary
--------------------------
10, 1000
20, 2000
30, 3000
40, 5000

Display as

employee_id, salary, cumulative_sum
-----------------------------------
10, 1000, 1000
20, 2000, 3000
30, ...

varun negi

• Dec 8th, 2015

* You can have a single stage variable svSum with initial value = 0.
* And keep the derivation as svSum = svSum + Salary

dstage_user

• Nov 13th, 2015

You can use following steps :-
1. Create 2 stage variables
a. stgvar1=> salary+ stgvar2
b. stgvar2=> stgvar1
Note :- Set initial value of stgvar2=0.

Assign stgvar2 to cumulative_sum column.

Answer Question  Select Best Answer

You are given 2 jobs say job A and Job B with parameters x and y respectively. You need to create a sequence job. If you pass parameter x then Job A should run, If you pass parameter y then Job B should run, if you dont pass any parameter then Both Job A & B should run

Bharath

• Apr 3rd, 2016

create a sequence job. User_var_act ----> Nested_Condition_Activity----> In Nested condition Activity define two parameters with the values for example JOB1 and JOB2. Link the nested condition ac...

Rohit

• Sep 24th, 2015

This can be done by the use of user variable and nested stage.Define a user variable with the arguments as parameters x and y. After his pass this variable to a nested activity stage. Generate 3 outpu...

Answer Question  Select Best Answer

What is the process to split the large datastage job into smaller jobs?

sai3689

 Profile Answers by sai3689 Questions by sai3689

• Nov 18th, 2015

Thanks Rohit

Rohit

• Oct 16th, 2015

If there is a job having more no of stages say around 20 stages. Then generate the output to an intermediate file like a dataset using the first 10 stages. After generating this use this intermediate dataset to comibine with the remaining stages and then generate the final file

Answer Question  Select Best Answer

I need help validating rows. 1st. I have a sequential file that has 139 rows, 2 columns (TABLENAME, CLOB). Of those rows, two (in TABLENAME column) are named "HEADER" and "TRAILER". I do not want these in my output. How do I keep the 137 rows and cut off the HEADER & TRAILER. 2nd. In the TRAILER row (TABLENAME column), there is a number that reads 00000000137 (in the CLOB column), which is the 137...

Mahesh R

• Nov 18th, 2015

You can use constraints facility in transformer stage, to cut-off rows with HEADER and TRAILER. If you mention the column name with IN condition the rows will get dropped off.

Rudra

• Nov 17th, 2015

One thing is not clear from you question i.e. the record count will be written on 138th record I think. As part of sequential stage ignore the 1st line as header. Use a shell script to get the record ...

Answer Question  Select Best Answer

I have a file structure that I need to add things to and it is as follows:

TABLENAME
PROV_TB
ADDR_TB
ADDR_TB
AFFL_TB
TAX_TB
ETAX_TB
EPAY_TB
PROV_TB

I need to know how to create a sequence number the tables in between the PROV_TB. The PROV_TB has been assigned a number I created (153365000 is the 1st, 153365001 is the...

Ram

• Mar 31st, 2016

Venkat@ How you will get seq 1 with algorithm for 2 partitions and seq 1,2 for 3 partitions
@INROWNUM * @NUMPARTITIONS + @PARTITIONNUM
can you please explain...?

venkat

• Mar 28th, 2016

Syntax is:
@INROWNUM * @NUMPARTITIONS + @PARTITIONNUM
or
Surrogatekey()

Answer Question  Select Best Answer

Given a file with 1000 records, design a job which will abort if it exceeds the record count more than 100

Rajendhar Reddy kuthuru

• Feb 29th, 2016

Use the transformer options Abort after rows by using the Following Logic. Use a stage variable to count the number of rejected records. Create a link in the transformer with the Constraint that th...

Rajendhar Reddy Kuthuru

• Feb 29th, 2016

Source->Transformer->Target

In transformer enable option When "Abort After Rows" is set in a Transformer, the job aborts when the threshold is reached, however no row output is sent downstream.

Keep the peek stage to write the reason for failure in job log

Answer Question  Select Best Answer

I have 5 records in student table. I want maximum score student details. How to do this with transformer stage.
Example:Input::: Student table
Sno smarks
1 50
2 45
3 34
4 50
5 33
I want output like this::
sno smarks
1 50
4 50

dhanu

• Apr 30th, 2016

Use a sorter transformation(sort -desc), add a sequence generator(nxt val=1).

srinivas

• Apr 15th, 2016

We can implement this scenario in transformer stage, for that create three stage variables.

Step1: In transformer stage properties sort the smarks field as descending order.

Step2: Create three stage variables sv1,sv2,sv3
In Sv1: derivation map the smarks column
In Sv2: Sv1

Answer Question  Select Best Answer

We are using Bulk Collect methodology in Database to load the bunch of records at a time.How we will implement the same operation in Datastage. If my source having millions of records, I want to load the bunch of records in to target at a time. How?

Sri

• Jul 20th, 2016

Increase the array size in Target Table stage. Also use Parallel Hints while selecting the records.

Pavan Kumar

• Jul 8th, 2016

You can load the bunch of files at a same time using sequential file stage. In options just select the file location the DB and change the option in the seq file stage to "File Pattern".

Answer Question  Select Best Answer

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

Avni

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

Answer Question  Select Best Answer

In join stage how we will store the rejected record in one file?

Aloka

 Profile Answers by Aloka Questions by Aloka

• Jul 1st, 2016

Thanks Ram !

Ram

• Jun 30th, 2016

Hi Aloka,
Join stage doesnt support reject link. so just connect join stage --> Transformer stage and provide constraints on Transformer to get rejects in Join stage.

Answer Question  Select Best Answer

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

Sri

• Jul 20th, 2016

Dimension table will load first and then fact tables.

Ram

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

Answer Question  Select Best Answer

How we will generate Surrogate Key in Database not in Datastage.

Aloka

 Profile Answers by Aloka Questions by Aloka

• Jul 1st, 2016

Thanks Ram !

Ram

• Jun 30th, 2016

Hi Aloka,
we can generate surrogate key on database creating sequence object.
syntax: sql > create sequence < seq_object name >

Answer Question  Select Best Answer

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

amulya panda

• Aug 3rd, 2016

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

Gagandeep Singh

• Jul 29th, 2016

Define delimiter as - in Sequential file stage

Answer Question  Select Best Answer

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

Purba

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

Ram

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

Answer Question  Select Best Answer

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?

sreeni

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

Ram

• Aug 4th, 2016

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

Thanks !

Answer Question  Select Best Answer