How to increase the Sequence number in PX from Oracle

[dw_prasanta]

Hi All,
I am loading the data in to Oracle9i table using PX. But before loading, I want to retrieve the maximum value of the Sl_No (a sequence number) in to a stage variable and then want to use that value for further to get the next serial number for the incoming source recordas there are some primary key-Foreign key relationship between the incoming records. And also I want to retrieve the value for only once for a single load.

Kindly give me some suggestion how to store the max value from the Oracle by using the Stage variable.

Regards,
Prasanta

[ajith]

Hi All,
I am loading the data in to Oracle9i table using PX. But before loading, I want to retrieve the maximum value of the Sl_No (a sequence number) in to a stage variable and then want to use that value for further to get the next serial number for the incoming source recordas there are some primary key-Foreign key relationship between the incoming records. And also I want to retrieve the value for only once for a single load.

Kindly give me some suggestion how to store the max value from the Oracle by using the Stage variable.

Regards,
Prasanta

There are different ways for implementing this,
one way is to write a script for accomplishing the same,
connect to database in the script and store the sequence value you want to use in a variable. later in the script use this variable along with dsjob command to call the job from the script. This would always work, If you know some scripting.

The syntax would be

dsjob -run -param $seqval jobname


Another would be using an oracle stage and get the highest value using a query and using the same for your purpose, Not nearly as adventurous as the first approach... heh

Hope it helps,
Ajith

[Kirtikumar]

Welcome aboard :D

You can get that value directly in the stage var.
Write a before job routine or script to extract max SL_NO and assign it one job parameter. Then use this parameter while generating the SL_NOs.

But as the job will be parallel, you will have to take care of parallelism while generating sequence number from stagevars.
Better options would be a sequence generator stage with round robin partitioning.

[ray.wurlod]

... or a configuration that uses only a single processing node.

Or a server job to process the single row.

[johnthomas]

What i have done is used the lookup stage , to get the max value from the database . lookup key should always be true (create a dummy column for that). Also we can run the lookup stage in sequential mode,since thatis what we want . In the next stage you can add this to a (sequence number generated using surrogate key generator , or using stage variables)

[chulett]

But before loading, I want to retrieve the maximum value of the Sl_No (a sequence number)

Just as an FYI and to check your terminology... If this 'sequence number' is actually controlled by an Oracle Sequence object then there's no need to get any kind of 'maximum' value or increment it yourself. Is that the case by any chance?

[johnthomas]

I am not sure whether its is a good approach to use oracle sequence for generating sequence since for each incoming record there will be a database call right ? .I prefer doing doing it within datastage for performance reasons

[chulett]

Granted... but just trying to ensure we're answering the right question.

[Titto]

Welcome aboard :D

You can get that value directly in the stage var.
Write a before job routine or script to extract max SL_NO and assign it one job parameter. Then use this parameter while generating the SL_NOs.

But as the job will be parallel, you will have to take care of parallelism while generating sequence number from stagevars.
Better options would be a sequence generator stage with round robin partitioning.

Hi,

is it possible to generate the sl_no using before job routine and assign the value to the same job as a parameter...

any info is appreciated.

[Nageshsunkoji]

Hi ,

What about next val function ?

Just add a new column called SEQ_NUM in database column list and in the Oracle stage in place of '?' give the function nextval for SEQ_NUM.

Try out.

[dw_prasanta]

[quote="Nageshsunkoji"]Hi ,

What about next val function ?

Just add a new column called SEQ_NUM in database column list and in the Oracle stage in place of '?' give the function nextval for SEQ_NUM.

Try out.[/quote]

[quote="Prasanta"]Hi ,

The next val is not working....For the time being, I am doing this by using Lookup...Thanks a lot for all your suggections....

[ray.wurlod]

The problem with using a stage variable, or even a lookup, is that you are getting information on each processing node. This may turn out to be tricky.

Better would be to use a separate job, executing sequentially (or a server job), that extracts the max value once from Oracle and stores it somewhere accessible, such as the job's user status area or a text file.

Your parallel job can then be passed that value as a job parameter, and a suitable expression (involving partition_number and partition_count) created to generate surrogate key values.

[johnthomas]

Hi Ray ,

We could force to run the lookup stage run sequentially , which would avoid any issues due to parallelism . let me know your comments on that

[chulett]

The next val is not working....

What do you mean "the next val is not working"? Selecting nextval (note that it's one word) from an Oracle sequence object works just fine, however you've never confirmed nor denied if that is the source of this mysterious "sequence number". So either you ain't using one or you can't get it to work.

So? Are you needing to use a Sequence object to control these surrogate values? Or can you just take the max value in the target table and start incrementing that during your job run?

A clear answer to that question will cut down on all this speculation and general chit-chat going on right now.

[ray.wurlod]

Hi Ray ,

We could force to run the lookup stage run sequentially , which would avoid any issues due to parallelism . let me know your comments on that

Then you may as well use a server job, which has much lower overheads than a parallel job.

But you'd STILL be doing the lookup for every row processed, which is an immense waste of time. Doing it once, in a prior job, and passing it in as a job parameter value, is far more efficient. AND you can then use parallel execution.