how to generate surroagte key for incremental load

[harithay]

Hi all,

i have situation like this


the loading of data take place every month.

after intial load , the next month i may get both new records and already existing records (need to update existing records )

for updation i am taking target key column into hashed file for lookup purpose with source.

for every record i need to generate sequence key (if it is a new record)
what is the procedure to get sequence number
(sample: if i loaded intially 1000 records , for the next load it should start from 1001 if it is a new record)


where should we write constraint for updation(if we get match key with source and hashed file) ?

[sjhouse]

You can use one of the Transforms in the sdk\KeyMgt folder to do surrogaate key management. This transform executes a rooutine that will do what you are asking.

[harithay]

thanks sjhouse,

i used sdkkeymgtgetnxt('account') routine for that to generate sequenc number .

i dont know about updation , if i found key match i need to overwrite the record.

for that what shoud i do.

in target ODBC there is update action ,

can i select 'insert new or update existing rows. is that sufficient for updation?

[kris]

Hi there,

If you want to update existing record and insert new record, 'insert new or update existing rows' should be sufficient enough. But if your sequence number is the primary key?or is it the unique key in the table?
In any case you would be populating new sequence numbers which is okay but when you update the record, you will get new sequence number....so finally you would be having some missing numbers which will not harm you anyways.....

Kris~

[harithay]

Hi Kris

Hi there,

If you want to update existing record and insert new record, 'insert new or update existing rows' should be sufficient enough. But if your sequence number is the primary key?or is it the unique key in the table?
In any case you would be populating new sequence numbers which is okay but when you update the record, you will get new sequence number....so finally you would be having some missing numbers which will not harm you anyways.....

Kris~

i don' t want get missing numbers for updated records.

for inserting new record i should get new sequenc enumber and for matching key i need updation but not with missing new sequenc enumber.

how to achieve this.

[ranga1970]

Simple rule for updattion;

Update by fileds A, B , C...

for example

Code: Select all

INSERT INTO A_TBL (A_NUMBER,A_START_DATE,A_END_DATE,A_PATIENT_ID)VALUES
 (:1,
TO_DATE(:2, 'MMDDYYYY'),
TO_DATE(:3, 'MMDDYYYY'),
:4,);

UPDATE A_TBL SET A_NUMBER=:1,
A_START_DATE=TO_DATE(:2, 'MMDDYYYY'),
A_END_DATE=TO_DATE(:3, 'MMDDYYYY'),
A_ID=:4 WHERE A_NUMBER=:1

apply same rules as SQL[/code]

[amsh76]

If you find the record in the Hash file (i.e. Update Action), you do not execute the routine..this you can achieve by creating two o/p links one for insert and one for update.

If you want to do it in one link (Insert/Update option) then you need to write a derivation for key column..where you check if record is present in HF, if yes pull it from HF else generate a new key.

HTH

[pani]

You can have join/lookup on warehouse to determine, if record already exists or to have two branches. For the insert record, generate 1 up number taking the max from warehouse.

You can build a shared container to abstract the logic of 1 up number and use that in the insert branch.

[ram1899]

harithay

Your design should look like this


Target Table --->Hash File --------Ref Link For TransformStage1
Input File -----> Stream Link for TransformStage1

Out Put Link1 from TransformStage1 Target Table (Update) Out Put Link1 from TransformStage1 Target Table (Insert)


This can be changed the is basic as it will give you very good idea of what is Happening here


In Transform Stage Join the Input and Lkp with Key Column

On the Links you have to give Constraints as

Link 1 : InputFile.KeyColumn = HashFile.Keycolumn
Link : Reject Records check Box

When Sending the Data trough Second Link use the KeyMgt Function


Hope this will Help you


Thanks

Hi all,

i have situation like this


the loading of data take place every month.

after intial load , the next month i may get both new records and already existing records (need to update existing records )

for updation i am taking target key column into hashed file for lookup purpose with source.

for every record i need to generate sequence key (if it is a new record)
what is the procedure to get sequence number
(sample: if i loaded intially 1000 records , for the next load it should start from 1001 if it is a new record)


where should we write constraint for updation(if we get match key with source and hashed file) ?

[harithay]

Hi all,

i got it, thanks to evry one

harithay

Your design should look like this



Target Table --->Hash File --------Ref Link For TransformStage1
Input File -----> Stream Link for TransformStage1

Out Put Link1 from TransformStage1 Target Table (Update) Out Put Link1 from TransformStage1 Target Table (Insert)


This can be changed the is basic as it will give you very good idea of what is Happening here


In Transform Stage Join the Input and Lkp with Key Column

On the Links you have to give Constraints as

Link 1 : InputFile.KeyColumn = HashFile.Keycolumn
Link : Reject Records check Box

When Sending the Data trough Second Link use the KeyMgt Function


Hope this will Help you


Thanks

Hi all,

i have situation like this


the loading of data take place every month.

after intial load , the next month i may get both new records and already existing records (need to update existing records )

for updation i am taking target key column into hashed file for lookup purpose with source.

for every record i need to generate sequence key (if it is a new record)
what is the procedure to get sequence number
(sample: if i loaded intially 1000 records , for the next load it should start from 1001 if it is a new record)


where should we write constraint for updation(if we get match key with source and hashed file) ?

[ram1899]

harithay
Is what is column name into which you are inserting the Surrogate Key I don't see that column name in the Update Sql or I'm I missing somthing Please update

If I'm not worng what you are trying to do is in Update you are not taking the Surrogate Key Column as it is the Unique Key you are gnerating and in your table it might me Key column Please check if it that then you have to take Surrogate Key from Ref Link in Update Link also

hope this will help

Thank you

Hi Ram;

I am using the same procedure what ever u have mentioned except (reject link check mark)


in target i am using 2 ODBC stages, one for insertion , other updation


Initially first time i am loading 33, 139 records. it is running good without errors. if i check performance statistics i am getting 0 rows for update link.


when i load next time 10 records (all are previously existing reocrds with modifications ) , for this job i need to update those 10 records in target table

it is not going to update link.

it is giving follwing error


Basel_To_Target..Transformer_1.DSLink10: DSD.BCIPut call to SQLExecute failed.
SQL statement:UPDATE uci.dbo.account SET source_account_id = ?, account_name = ?, fund_mgr_name = ?, ici_name = ?, unit_head = ?, far_add_dt = ?, far_del_dt = ?, fund_type = ?, far_inv_mgr_name = ?, far_fund_status = ?, last_upd_dtm = ?, last_upd_by = ? WHERE ()
SQLSTATE=37000, DBMS.CODE=170
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
SQLSTATE=37000, DBMS.CODE=8180
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

source_account_id = "00A1 "
account_name = "CONRAD HILTON FOUNDATION "
fund_mgr_name = "FLEMING, TIM "
ici_name = "IIS EQUITY "
unit_head = "CYR, W. "
far_add_dt = "2003-04-30 00:00:00.000"
far_del_dt = NULL
fund_type = "NON-PROFIT FOUNDATION "
far_inv_mgr_name = "WILLIAM D WITTER INC "
far_fund_status = "1"
last_upd_dtm = "2005-05-26 12:25:55.000"
last_upd_by = "dbo"

harithay

Your design should look like this



Target Table --->Hash File --------Ref Link For TransformStage1
Input File -----> Stream Link for TransformStage1

Out Put Link1 from TransformStage1 Target Table (Update) Out Put Link1 from TransformStage1 Target Table (Insert)


This can be changed the is basic as it will give you very good idea of what is Happening here


In Transform Stage Join the Input and Lkp with Key Column

On the Links you have to give Constraints as

Link 1 : InputFile.KeyColumn = HashFile.Keycolumn
Link : Reject Records check Box

When Sending the Data trough Second Link use the KeyMgt Function


Hope this will Help you


Thanks

Hi all,

i have situation like this


the loading of data take place every month.

after intial load , the next month i may get both new records and already existing records (need to update existing records )

for updation i am taking target key column into hashed file for lookup purpose with source.

for every record i need to generate sequence key (if it is a new record)
what is the procedure to get sequence number
(sample: if i loaded intially 1000 records , for the next load it should start from 1001 if it is a new record)


where should we write constraint for updation(if we get match key with source and hashed file) ?

[harithay]

Hi Ram ,

i got it. thanks