Insert data

[bobby]

hi,
i am using SCD type 1 , Everytime we insert new data ,i am using surrogated keys user define sql , i have few question , i wanna insert new data so that prvious data be overwritten everytime i write a data , i have created seq in oracle so evrytime i truncate table and load data , sql set to user define sql ,please advise on that.
Thanks,
Bob

[chulett]

Wow... all that in one sentence.

Unfortunately, I have no idea what kind of specific information you are looking for. I can only make a few comments and inferences.

* To 'insert new data so that previous data be overwritten' use one of the Update actions. One does a simple update of the existing record based on the defined keys and the other ('Replace Completely') does a delete and insert.

* If you really need to use an Oracle Sequence for your surrogate keys, you can use Custom Sql in an OCI lookup. There are much better ways to do this, by the way. Make sure there is a single (non-key) field defined in the lookup and your custom sql does a 'select seqname.nextval from dual'.

* Other than that, I can't image you really need to use Custom SQL.

* Are you truncating the table every time you load into it? Are you sure this is what you need to do?

* Best Practice says you should build an 'Inverse Hash Lookup' of Business Keys from your current surrogate keys in the target dimension so you can tell which records should be inserts (and need a new surrogate key) and those that should be updates (and thus don't). Is this build into your job, one way or another?

[bobby]

Hi Chulett,
l am doing like this i have table aa columns aa1(pk) aa2, aa3 from my generated sql i copy and paste to user define sql and make aa1 as surrogated key same as u said aa1.nextval in user define sql,
NOW WHEN I COMPILE AND RUN LETS SAY SAME JOB i have 10 rows when i compile first i saw 10 ROWS when i compile second time and run second time i saw pervious 10 rows and same 10 rows are added agin, i mean i wanna see only 10 rows of latest job not pervious when i saw data in view, So pl. guide
Thanks
Bob

[ray.wurlod]

You need to design a mechanism that doesn't re-select rows that have already been processed, unless changes have occurred to those rows that make them candidates for further processing.

The technique is known generically as Changed Data Detection, and there are many ways to accomplish it. Search this forum for examples, or enrol on the DataStage Best Practices class from Ascential (and its prerequisites).

To load an Oracle table using a sequence you create a statement such as

Code: Select all

INSERT INTO tablename (col1, col2, col3) 
VALUES (myseq.NEXTVAL, :1, :2);

and deliver only col2 and col3 values from your DataStage job. If you deliver a col1 value, it will assume that it has to overwrite that key value (depending on your update rule, and whether the primary key column (col1) has a UNIQUE constraint).

Perhaps an SQL class from Oracle would help too.