Retrieving multiple values from a sequence

[Nicole]

I am trying to workaround the fact that I am not able to use a sparse lookup in the job I am developing.

Basically I have this query -

SELECT (NEXTVAL FOR COM.DLN_SEQ) FROM SYSIBM.SYSDUMMY1

This query only retrieves the NEXT 1 value - but I need to retrieve the next 1000 - does anyone have any idea how to do this? I know it is more SQL than datastage but I figured there have got to be some good sql people out there. I found a couple sites with some info and am trying to apply it but no luck so far.

For instance this site link below suggested something like:
select nextval('seq') from generate_series(1,1000);

http://www.depesz.com/index.php/2008/03 ... sequences/

[ray.wurlod]

Why not simply use a Surrogate Key Generator stage, in which you can set the block size property? This stage can access your database sequence, or it can use a state file that you can initialize from the database.

[Nicole]

Thanks Ray - I looked at the surrogate key generator and it looks like it might work - one question - do I need that particular stage? I also see there is a surrogate key generator tab in the stage properties of the transformer stage - which I am using in my job already - can I create the surrogate key generator from the transformer stage here? Thanks again.

Why not simply use a Surrogate Key Generator stage, in which you can set the block size property? This stage can access your database sequence, or it can use a state file that you can initialize from ...

[ray.wurlod]

Yes you can do that also. It uses the same functions under the covers.

[Nicole]

I am having issues with the Surrogate Key Generator - and I see others have had the same problem, but I can't seem to figure it out - it seems this

Transformer_81,0: Error Idx = 1;
DB2Driver Embedded SQL message: ;
sqlcode = -1390;
sqlstate =
Transformer_81,0: Failed to connect to the database
Transformer_81,0: Library orchdb2op is loaded.

In the logs it also says that it is going to the default APT file - even though I specified in the stage use default database: false and use default server: false - should it still look at the default apt file?

Other than that, I feel that I have all of the correct values set - what else could be wrong?

Yes you can do that also. It uses the same functions under the covers. ...

[rcanaran]

I need to do something similar, but I have one concern about using the surrogate key generator: What do I need to do to update the sequence in the database?

There are other non-DS processes (web services) that use the sequence in the Oracle database.

[chulett]

Using the sequence directly would mean all that is already handled by the database.

[rcanaran]

Using the sequence directly would mean all that is already handled by the database.

Using directly means either using the sequence in a sparse lookup or manually coding the insert sql? I take it that using the surrogate key generator would NOT be recommended in this case.

[chulett]

No, by 'directly' I meant directly in the Surrogate Key Generator stage. Now granted, I've never done this myself as of yet, so just going by what I've read posted here but my understanding is that the stage can directly access the sequence and thus the fact that others may be using it at the same time would be a non-issue.

Trying to start a series of keys from a database sequence and then update the sequence once your job is done sounds like a bad idea if there's any kind of 'concurrency' needed.

[rcanaran]

Thanks.
Good point re: concurrency.
I'll play with the surrogate key generator some more.