how to generate surroagte key for incremental load
Moderators: chulett, rschirm, roy
how to generate surroagte key for incremental load
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) ?
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) ?
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?
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?
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~
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~
Hi Kris
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.
i don' t want get missing numbers for updated records.kris wrote: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~
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.
Simple rule for updattion;
Update by fileds A, B , C...
for example
apply same rules as SQL[/code]
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]
RRCHINTALA
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
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
Re: how to generate surroagte key for incremental load
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
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
harithay wrote: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
Re: how to generate surroagte key for incremental load
Hi all,
i got it, thanks to evry one
i got it, thanks to evry one
ram1899 wrote: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
harithay wrote: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) ?
Re: how to generate surroagte key for incremental load
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
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
harithay wrote: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"
ram1899 wrote: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
harithay wrote: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