oracle sequence resued for processing.

efxuser · Post by **efxuser** » Mon Mar 02, 2009 3:19 pm

I searched the forum for oracle sequences for surrogate key generator.

My requirement is that I use oracle sequence .nextval to insert into oracle table.Also use these sequence numbers for later use.

I came across so many posts about sparse lookup and getting max sequence number from oracle ,keymanagement functions that now i am totally lost.

can some body kindly detail me steps i should follow for these? I am trying to take a gues shere...

1. Try to find maximum sequence number.
2. use .nextval to insert into oracle
3.Lost here...how do I get these rows along with sequence numbers to be used for further rpocessing?

Even you point me to other posts which i should foillow will also be more helpful.

Also, my admin seems convinced that surrogate key generator stage in ds is not efficient .ANy truth to his claim?

Thanks
[/img]

betterthanever · Post by **betterthanever** » Mon Mar 02, 2009 3:35 pm

you want to use that DB sequence to create the unique number every time you run one particular job or you want to use that DB sequence in other jobs as well???

betterthanever · Post by **betterthanever** » Mon Mar 02, 2009 3:52 pm

Code: Select all

                  source
                    |
                    |
                    |
                    |
Ora(Reference)-----Lkp-------Target 
( Lkp type: sparse)
(Read Method : User-Defined sql)
(SQL Query : select sample_seq.nextval as seq_num from dual)

on the Lkp stage map the field seq_num to the ouput field which is unique seq number on the target side

Mike · Post by **Mike** » Mon Mar 02, 2009 4:23 pm

If given a choice, I never use a database sequence (or identity column). They are too slow and and promote inefficient flow design.

Database sequences are fine for OLTP processing, but add far too much overhead for bulk data processing.

The surrogate key generator stage with a state file is the way to go. The NextSurrogetKey() function is available for use in a transformer stage or SCD stage when and where it is needed for efficient flow design.

The more parallel processing you have, the worse the database sequence bottleneck will become.

Mike

betterthanever · Post by **betterthanever** » Mon Mar 02, 2009 4:30 pm

i agree....

betterthanever · Post by **betterthanever** » Mon Mar 02, 2009 4:31 pm

i agree....

efxuser · Post by **efxuser** » Tue Mar 03, 2009 8:06 am

thanks for your quick response.can you please elaborate about the first stage you have (oracle reference) what is that to do? also in the lookup stage I dont find any option for sparse lookup?

efxuser · Post by **efxuser** » Tue Mar 03, 2009 8:08 am

thanks for your quick response.can you please elaborate about the first stage you have (oracle reference) what is that to do? also in the lookup stage I dont find any option for sparse lookup?

efxuser · Post by **efxuser** » Tue Mar 03, 2009 8:10 am

thanks for your quick response.can you please elaborate about the first stage you have (oracle reference) what is that to do? also in the lookup stage I dont find any option for sparse lookup?

betterthanever · Post by **betterthanever** » Tue Mar 03, 2009 10:10 am

efxuser wrote:thanks for your quick response.can you please elaborate about the first stage you have (oracle reference) what is that to do? also in the lookup stage I dont find any option for sparse lookup?

can you please elaborate about the first stage you have (oracle reference) what is that to do?----just to fetch the number from DB sequence.

also in the lookup stage I dont find any option for sparse lookup?---you would set this in oracle stage not in lookup stage

betterthanever · Post by **betterthanever** » Tue Mar 03, 2009 10:11 am

duplicate post

betterthanever · Post by **betterthanever** » Tue Mar 03, 2009 10:12 am

duplicate post

chulett · Post by **chulett** » Tue Mar 03, 2009 10:21 am

Sheesh... posts so good they need to be repeated - three times. Seriously, please start checking after you post and delete any duplicates, especially if it takes 'some time' to complete - that's pretty much a sure sign you'll have duplicate posts.

betterthanever · Post by **betterthanever** » Tue Mar 03, 2009 10:27 am

[quote="chulett"]Sheesh... posts so good they need to be repeated - [i]three times.[/i] Seriously, please start checking after you post and delete any duplicates, especially if it takes 'some time' to complete - that's pretty much a sure sign you'll have duplicate posts.[/quote]
can you help me delete the duplicate ones???

chulett · Post by **chulett** » Tue Mar 03, 2009 10:38 am

Once they've been replied to, they cannot be deleted, which is why it's important to check right away when you suspect there might have been a problem. Best you could do now would be to edit two of them and just say 'duplicate post' or some such in the body.

DSXchange

oracle sequence resued for processing.

oracle sequence resued for processing.

Re: oracle sequence resued for processing.

Re: oracle sequence resued for processing.