Generate Sequence in Oracle DB in PX jobs.

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
ks489
Participant
Posts: 14
Joined: Fri Apr 15, 2005 1:45 pm

Generate Sequence in Oracle DB in PX jobs.

Post by ks489 »

Hi,

I am using a parallel job to generate a sequence in Oracle.
Every record will have that unique number.

I am using a LookUp stage to achieve this. heres how my job looks like:

Primary source _______ Lookup Stage _______Output File (ID, Val1,...)
(Sequential File) ^
(Key,Val1) Reference Link |
Oracle Sparse Lookup
(Key, ID )

NOTE: I have given same names (meta data) for Key in primary source as well as Oracle Sparse Lookup.

Here is the query i m using in Oracle Sparse Lookup.

SELECT ID_SEQUENCE.NEXTVAL Id
FROM DUAL
where 1=ORCHESTRATE.Key

where Key(ORCHESTRATE.Key) is the Key Defined on Primary source.
It is nothing but a constant 1 in all the records.

in the above case, i am getting a failed lookup, and the value of ID is Null in all the lookups.

So i tried creating a temporary table (Key varchar(1), ID NUMBER) in Oracle for testing purpose

and this query works fine, in Oracle Sparse lookup.
SELECT ID
FROM TEMP_TABLE
WHERE Key=ORCHESTRATE.Key

I guess the problem is the Dual table dont have a column by name "1".
But Similar kind of query is working for me In DataStage Server Lookup,
with slight difference as follows:
SELECT 1, ID_SEQUENCE.NEXTVAL Id FROM DUAL
where 1=:1

But my requirement is a Parallel Job. If i dont get it to work, i guess i can go for a join, but i dont want to really do a join for simply generating Sequences. Also i cant use Surrogate Key generator, as this sequences have to be from Oracle Server, and many users acess this sequence in parallel.

Thanks in advance, and excuse me for this lengthy description.

Warm Regards,
Kash.
roy
Participant
Posts: 2598
Joined: Wed Jul 30, 2003 2:05 am
Location: Israel

Post by roy »

Hi,
running:

Code: Select all

select * from dual
returns 1 column named DUMMY with the value 'X' (a 1 character string with Capital X)
Can you try using this column name and value for your lookup and see if it works?
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.

Search before posting:)

Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Image
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Why don't you just use the sequence when inserting the rows with user-defined SQL? For example

Code: Select all

INSERT INTO table(col1, col2, col3)  VALUES (tableseq.NEXTVAL, :1, :2);
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
bibhudc
Charter Member
Charter Member
Posts: 20
Joined: Thu Jun 19, 2003 12:26 pm

Post by bibhudc »

ray.wurlod wrote:Why don't you just use the sequence when inserting the rows with user-defined SQL? For example

Code: Select all

INSERT INTO table(col1, col2, col3)  VALUES (tableseq.NEXTVAL, :1, :2);
ray's method above is the more efficient way to do it... We normally do it this way in server jobs too and found it to be way faster than a lookup. [Ofcourse, custom sql means that you have to add some comments in the job to remind yourself, just in case you add or drop some columns in future development.]

Also, if you have a large datasource, (and since you would be running a parallel load), you may want to increase the Oracle sequence cache to a bigger number than the default value of 1 -- this will ensure that the several simultaneous processes don't encroach upon each other, waiting for the next sequence number... i.e. to avoid contention on the sequence.
Bibhu
ratikmishra1
Charter Member
Charter Member
Posts: 42
Joined: Wed Aug 18, 2004 2:49 pm

Post by ratikmishra1 »

Hi Kash,

Did you finally manage to get it working? I've the same requirement and I've tried lots of combinations, but nothing seems to work!

Thanks
Rati
kwwilliams
Participant
Posts: 437
Joined: Fri Oct 21, 2005 10:00 pm

Post by kwwilliams »

Did you try using Ray's Method above? If you did and it doesn't work, do you get an error?
ks489
Participant
Posts: 14
Joined: Fri Apr 15, 2005 1:45 pm

Re: Generate Sequence in Oracle DB in PX jobs.

Post by ks489 »

First of all very sorry for not replying to my post earlier.

I was out of town, so didnt login.

Secondly, Thanks Ray for your suggested solution.

But i cannot do it, because I need that sequence as a reference (Refrential integrity) in some other table. i.e when i generate the sequence for the same record, it has to refrence other table as a foriegn key. And thats why i cannot use it in the insert statment.
But thats a good suggestion may be in some other cases it will work wonders.

Also, thanks Bibhu for suggetion to increase the cache size, but i believe that is something our Oracle DBA would have permission to do. And our DBA kinda is a lazy person. so cannot expect anythign from that.

finally, i solved the issue by developing a Server job and not spending my time doing too much research on how i would get to work it in a Parallel Job.

And i sincerely feel, Tableseq.NextVal doesnt work in Lookup stage in Parallel Jobs. IBM Developers might want to give another look at it and may be incorporate that functionality in Hawk Release.

Thanks All.
kash
Post Reply