Sequence Number
Moderators: chulett, rschirm, roy
Sequence Number
Hi
I need help to create sequence number.
Actually my input is Sequential file and output is oracle table.The file has 750000 records.
I used surrogate key generator stage.generated sequence number first time.But i will get the input file every week.so i need to load every week.
now my output key column need to start with 750001,750002....and so on..
so how can i create the sequence Give me any idea
I need help to create sequence number.
Actually my input is Sequential file and output is oracle table.The file has 750000 records.
I used surrogate key generator stage.generated sequence number first time.But i will get the input file every week.so i need to load every week.
now my output key column need to start with 750001,750002....and so on..
so how can i create the sequence Give me any idea
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Prior to running the job, probably in another job, obtain the next available surrogate key value and write it to that other job's user status area or to a file. Pick up the value and pass it to the job containing the generator as a job parameter value. This will be the initial value to use in the generator (insert job parameter).
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
No, because I specified "the next available key", rather than the current max key value. implying that there would be a +1 in the SELECT statement.
Code: Select all
SELECT MAX(SKey) + 1 FROM tablename;
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Of course using an Oracle sequence is a possibility. However, there is a risk of introducing a bottleneck if the sequence is not set up to deliver blocks of values.
But the Original Poster seemed to be asking how to use (initialize) the Surrogate Key Generator stage.
But the Original Poster seemed to be asking how to use (initialize) the Surrogate Key Generator stage.
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.
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
So, in other words - yes.chulett wrote:If it is truly the 'Initial Value' to use in the Surrogate Key Generator, then should you not increment your current max value by 1 before you poke it in there?ray.wurlod wrote:No, because I specified "the next available key", rather than the current max key value. implying that there would be a +1 in the SELECT statement.Code: Select all
SELECT MAX(SKey) + 1 FROM tablename;
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
As far as I know, you can if you do it in a BASIC transformer - with all the restrictions and caveats that that would bring along with it.throbinson wrote:I thought a job User Status area was strictly Server job's only? Can it be used in a EE job to pass a sequence number?
Or simply plug a Server job into your jobstream for that menial task.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
Thank you for your response Ray.
I created another job to pick next available number and stored it in a file.
Now how can i pass the value to the parameter?.I passed the file name to the parameter.but it is not taking the value.it is taking default value 0.
Please give me Idea..
your response would be appreciated.
Thanks
Reddy
I created another job to pick next available number and stored it in a file.
Now how can i pass the value to the parameter?.I passed the file name to the parameter.but it is not taking the value.it is taking default value 0.
Please give me Idea..
your response would be appreciated.
Thanks
Reddy
ray.wurlod wrote:Prior to running the job, probably in another job, obtain the next available surrogate key value and write it to that other job's user status area or to a file. Pick up the value and pass it to the jo ...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Execute Command activity to read the file, perhaps using cat command.
Use $CommandOutput activity variable - maybe needing to trim field marks - to supply the job paramter value. Search the forum for details - it's been explained before.
Use $CommandOutput activity variable - maybe needing to trim field marks - to supply the job paramter value. Search the forum for details - it's been explained before.
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.
I am getting the below error
LaboneSeq..JobControl (@Job_Activity_0): Controller problem: Error calling DSSetParam(StartValue), code=-4
[ParamValue/Limitvalue is not appropriate]
Can you please give me idea
LaboneSeq..JobControl (@Job_Activity_0): Controller problem: Error calling DSSetParam(StartValue), code=-4
[ParamValue/Limitvalue is not appropriate]
Can you please give me idea
ray.wurlod wrote:Execute Command activity to read the file, perhaps using cat command.
Use $CommandOutput activity variable - maybe needing to trim field marks - to supply the job paramter value. Search the f ...