Sequence Number

[reddy12]

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

[ray.wurlod]

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).

[chulett]

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]

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;

[iDomz]

Can't you use mysequence.NEXTVAL in your user generated query for oci stage? No need for additional jobs or surrogate key generator in that case. Not tried it myself, so YMMV.

currently working on v8 and it has the ability to connect to oracle/db2 to use database sequences

[ray.wurlod]

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.

[throbinson]

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?

[chulett]

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?

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;

So, in other words - yes.

[chulett]

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?

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.

Or simply plug a Server job into your jobstream for that menial task.

[throbinson]

Since this is so close to a hijack, I won't continue the thread, rather I'll say thanks! I hadn't thought that and move on.

[reddy12]

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

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 ...

[ray.wurlod]

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.

[reddy12]

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

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 ...

[chulett]

Search the forums for "ParamValue/Limitvalue is not appropriate".

[reddy12]

I really appreciated for all your responses

Thank you very much to Ray,Craig,DSguru.Problem solved