Answer / abhisek

Read the incoming records through any input stage like sequential file/dataset/table.
• Do the required processing for the incoming data.
• After the above processing step, pass the data into the change capture stage.
• The change capture should be having two input links- one is the before dataset and the other is the after dataset. For our job, the before dataset should be the active records present in the table. The active records are all those records which are having EXPR_DT=’2999-12-31’. The after dataset will be the incoming data passed into change capture after all the necessary processing.
• The change capture stage compared the before dataset and after dataset and produces 4 change_codes for each of the records. The 4 change codes are as follows:
“0” – Copy code (The code indicates the after record is a copy of the before record)
“1”-Insert code (The code indicates a new record has been inserted in the after set that did not exist in the before set.)
“2”-Delete code(The code indicates that a record in the before set has been deleted from the after set)
“3”-Edit code(the code indicates the after record is an edited version of the before record)
The copy records are not passed in the change captured stage as since we need only edited, insert records fro SCD II implementation.
• Use a filter stage to separate the records that needs to be expired and inserted.
• Filter the records with change_code = “1 or 3” into the insert records link. Filter the records with change_code=” 3” into update/expiry link.
• The records with change_code=3 are edited records. So the original records corresponding to these edited records are to be made in-active (expired). We can make the records inactive by changing the EXPR_DT<> ‘2999-12-31’.So to make the record inactive change the EXPR_DT with a valid date. For e.g. you can use make the EXPR_DT as the date one less than the date on which you are loading the data into the table. We will assume that we are loading the data on 2008-08-15.So the EXPR_DT for inactive records would become ‘2008-08-14’. The date 2008-08-15 can be made as the EFCT_DT for records to be inserted.
• To get the original records which needs to be expired, “look-up” the target table for all the records with change_code=3 which are filtered out separately. Get the original record along with the EFCT_DT of the original record. Then update the records EXPR_DT to ‘2008-08-14’ in the table. Now the original records are made inactive (expired).
• The new updated record (change_code=3) needs to be in table along with the new insert records(change_code=1).This data is filtered out from the “filter” stage and inserted into the table with EFCT_DT=”Data of loading” i.e. “2008-08-15” and EXPR_DT=”2999-12-31”

Can aggregator and transformer stages use to sort the data? How ?

2 Answers  

---

What are the types of hashed files in data stage

0 Answers  

---

what are fact tables and dimension tables? give example assuming one table.

3 Answers   IBM,

---

What can we do with datastage director?

0 Answers  

---

LIST THE BUILT-IN STAGES AVALIABLE IN SERVER JOBS?

2 Answers   Mannar Company, ME,

---

What is size of a transaction and an array means in a datastage?

0 Answers  

---

1.What is a delta record? How you will read it? 2.What are the different types of sorting?

0 Answers   EDS,

---

i have source data like empno,enmae 11 ,aa 12 ,bb i want output like empno,ename 11 ,aa 12 ,bb 11 ,aa 12 ,bb

7 Answers   TCS,

---

what is the exact difference between dataset and fileset in datastage?

5 Answers   TCS,

---

What are the main differences you have observed between 7.x and 8.x version of datastage?

0 Answers  

---

difference between function and procedure...

1 Answers   IBM,

---

Hi, Please tell me how to solve this scenario in datastage ? Here we have 3 columns in a table TEST CODE,ENTRY DATE and BATCH The table looks like CODE ENTRYDATE BATCH 100 100716 1 100 100716 1 100 100716 1 200 122517 2 200 122517 2 302 555555 8 302 555555 8 302 555555 8 We need to create a seqno on grouping these 3 columns. The result should be like this. CODE ENTRYDATE BATCH SEQNO 100 100716 1 1 100 100716 1 2 100 100716 1 3 200 122517 2 1 200 122517 2 2 302 555555 8 1 302 555555 8 2 302 555555 8 3

1 Answers   Alpharithm Technologies,

---