Retrieving multiple values from a sequence
Moderators: chulett, rschirm, roy
Retrieving multiple values from a sequence
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/
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/
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
ray.wurlod wrote: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 ...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
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?
ray.wurlod wrote:Yes you can do that also. It uses the same functions under the covers. ...
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers