Sequence generator
Moderators: chulett, rschirm, roy
Sequence generator
Hi all,
I have a field in the target table namly row_id integer, which should hold the sequence numbers starting from 1..n for every new record (Insert records).
For ex:
Are there any builtin functions in Data Stage or any server processings?
Please let me know what would be the best approach?
Thanks much!
I have a field in the target table namly row_id integer, which should hold the sequence numbers starting from 1..n for every new record (Insert records).
For ex:
Are there any builtin functions in Data Stage or any server processings?
Please let me know what would be the best approach?
Thanks much!
Bestest!
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The most efficient, if you want to start from 1 for each run, is to use the system variable @OUTROWNUM, since it is keeping that count in any case.
However, if you want a genuine sequence (starting again from where it left off), then either the SDK key management routines as mentioned, or a database sequence or auto-incrementing data type, can be used.
However, if you want a genuine sequence (starting again from where it left off), then either the SDK key management routines as mentioned, or a database sequence or auto-incrementing data type, can be used.
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.
Hi, you mentioned a database sequence, how would you use database sequence in datastage?ray.wurlod wrote:The most efficient, if you want to start from 1 for each run, is to use the system variable @OUTROWNUM, since it is keeping that count in any case.
However, if you want a genuine sequence (starting again from where it left off), then either the SDK key management routines as mentioned, or a database sequence or auto-incrementing data type, can be used.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Do NOT provide a key value from the job. Use user-defined SQL.SPA_BI wrote:Hi, you mentioned a database sequence, how would you use database sequence in datastage?
Code: Select all
INSERT INTO tablename (keycol, col1, col2, col3) VALUES (seqname.NEXTVAL, :1, :2, :3)
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.
Hi
I need to create rowid ( sequence numbers) into target table. i am using KeyMgtGetNextValue() function to populate rowid, which is always giving same ids. What my requirement is i need one similat to Informatica's Sequence generater transformation. is there any way we can generate id's like incrementally?
Day 1: starts with 1 and max would be 2009
Day 2: starts with 2010 and increment by 1 max would be 5545
Day 3 should start 5546
CAn some one help me to building my sequence generator logic which i mensioned above in Datastage.
I need to create rowid ( sequence numbers) into target table. i am using KeyMgtGetNextValue() function to populate rowid, which is always giving same ids. What my requirement is i need one similat to Informatica's Sequence generater transformation. is there any way we can generate id's like incrementally?
Day 1: starts with 1 and max would be 2009
Day 2: starts with 2010 and increment by 1 max would be 5545
Day 3 should start 5546
CAn some one help me to building my sequence generator logic which i mensioned above in Datastage.
Bestest!
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
Hi John,
KeyMgtGetNextValue is the function that you need. It works exactly as you have laid out in your Day 1, Day 2, Day 3 scenario. You can take a look at the code for the routine/transform using DataStage Manager. I'm not sure what you could be doing wrong, but do note that you have to pass the same value to the function each day (I usually recommend using the table name). I have seen some new developers try to pass a number to the function.
Mike
KeyMgtGetNextValue is the function that you need. It works exactly as you have laid out in your Day 1, Day 2, Day 3 scenario. You can take a look at the code for the routine/transform using DataStage Manager. I'm not sure what you could be doing wrong, but do note that you have to pass the same value to the function each day (I usually recommend using the table name). I have seen some new developers try to pass a number to the function.
Mike
Or look under the surrogate keys folder of the functions I gave you. There's an ...AssignSkeyParallel function that works in blocks for parallel assignment if you call it that way (any blocksize other than 1 will fetch numbers in that block range). Shoot me an email if you have any questions.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: