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.
Generate Sequence in Oracle DB in PX jobs.
Moderators: chulett, rschirm, roy
Hi,
running:
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,
running:
Code: Select all
select * from dual
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.]ray.wurlod wrote:Why don't you just use the sequence when inserting the rows with user-defined SQL? For exampleCode: Select all
INSERT INTO table(col1, col2, col3) VALUES (tableseq.NEXTVAL, :1, :2);
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
-
- Charter Member
- Posts: 42
- Joined: Wed Aug 18, 2004 2:49 pm
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Did you try using Ray's Method above? If you did and it doesn't work, do you get an error?
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
Re: Generate Sequence in Oracle DB in PX jobs.
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
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