oracle sequences in Datastage

[reddy]

Hi Guys,

How can i use Oracle Sequences in Datastage jobs????

I am new to datastage.

I would appreciate your help.

Thanks
Narasa

[ram1899]

Hi Guys,

How can i use Oracle Sequences in Datastage jobs????

I am new to datastage.

I would appreciate your help.

Thanks
Narasa

Hi Narasa,
You can either use the Oracle Sequence ( Sql Command ) when seleting the Data or Loading the Data Into Target tables. In both the cases you have use User Defined SQL when selecting ot Inserting the Data.Other way is using the Oracle system Dummy table.

If you want to create the Sequence keys why don't you use the KeyManagment Transform function which comes as default

Hope this will help you

[jmorgan]

In an OCI stage (or an ODBC or OLEDB stage) supporting a reference lookup, you can use a user-written SQL statement such as

Code: Select all

SELECT seqname.NEXTVAL FROM DUAL WHERE :1 = 1;

; supply a reference key expression that is always 1.

[chulett]

You don't need the 'where' clause or the reference key expression to accomplish this. Use a select in the form of:

Code: Select all

SELECT seqname.NEXTVAL FROM DUAL

as custom SQL in the OCI lookup stage and define one column to hold the value. Make sure the column is not marked as a key column.

No need to provide a Key Expression for the lookup, simply use the supplied sequence number you'll find in the lookup for each row processed by the job.

Walla!

[ray.wurlod]

I think you'll find that you need the WHERE clause if you use a Dynamic Relational Stage, even if its connection protocol is set to Oracle. I had assumed that the OP required to invoke the sequence for each row processed through the job.

I've also done this with generated SQL. My "table definition" on the link has one integer column. Its derivation is seqname.NEXTVAL. The table name on the General tab is DUAL, and the Selection tab contains the WHERE clause discussed earlier.

No need for custom SQL.

Viola!
(deliberate spelling error in an attempt at humour)

[chulett]

I think you'll find that you need the WHERE clause if you use a Dynamic Relational Stage, even if its connection protocol is set to Oracle. I had assumed that the OP required to invoke the sequence for each row processed through the job.

No idea on the DRS, haven't had the pleasure yet. Does work as noted in an OCI stage - and for every row processed.

I've also done this with generated SQL. My "table definition" on the link has one integer column. Its derivation is seqname.NEXTVAL. The table name on the General tab is DUAL, and the Selection tab contains the WHERE clause discussed earlier.

No need for custom SQL.

Excellent. Nice catch. Something that me, Mr 'I avoid custom sql at all costs' Dude should have noted.

Viola!
(deliberate spelling error in an attempt at humour)

And a darn good one, too.

Victrola!

[metabill]

This sure sounds like a nice approach to getting the sequence number without using custom SQL, but I haven't yet been able to get it to work. I get errors when I try to view data from the DRS or OCI stage that I created to use nextval. Possibly I've misunderstood some part of the suggestion.

I used a single integer column (arbitrarily named my_id) on the link with the suggested derivation (my_seq.nextval), and did not select this as a key column. I used a table name of DUAL, and a WHERE clause of :1 = 1. The causes the generated SQL to unfortunately look like "SELECT MY_SEQ.NEXTVAL FROM DUAL WHERE <<missing key columns>> AND :1 = 1". Can you please suggest a fix? Thanks.

[chulett]

Two columns and Column Generated sql. First one is marked as a Key column and its derivation is "1". Second one is a non-key column with seqname.nextval as the derivation. Put in dual as the table name. Do not add anything in the where clause. You'll end up with something like this:

Code: Select all

SELECT 1, my_seq.nextval FROM dual WHERE 1=:1

Then always put a '1' in the Key Expression to use the lookup.

[metabill]

Thank you so much! That did the trick. It's really nice to be able to rely on generated SQL for this instead of having to go the user-defined SQL route.

-Bill