DSXchange

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.

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,

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

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.

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

Did you try using Ray's Method above? If you did and it doesn't work, do you get an error?

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