oracle sequences in Datastage
Moderators: chulett, rschirm, roy
oracle sequences in Datastage
Hi Guys,
How can i use Oracle Sequences in Datastage jobs????
I am new to datastage.
I would appreciate your help.
Thanks
Narasa
How can i use Oracle Sequences in Datastage jobs????
I am new to datastage.
I would appreciate your help.
Thanks
Narasa
Re: oracle sequences in Datastage
reddy wrote: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
In an OCI stage (or an ODBC or OLEDB stage) supporting a reference lookup, you can use a user-written SQL statement such as ; supply a reference key expression that is always 1.
Code: Select all
SELECT seqname.NEXTVAL FROM DUAL WHERE :1 = 1;
![Idea :idea:](./images/smilies/icon_idea.gif)
Code: Select all
SELECT seqname.NEXTVAL FROM DUAL
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!
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
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!
![Laughing :lol:](./images/smilies/icon_lol.gif)
(deliberate spelling error in an attempt at humour)
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.
No idea on the DRS, haven't had the pleasure yet. Does work as noted in an OCI stage - and for every row processed.ray.wurlod wrote: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.
Excellent. Nice catch. Something that me, Mr 'I avoid custom sql at all costs' Dude should have noted.ray.wurlod then wrote: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.
And a darn good one, too.Lastly, ray.wurlod wrote:Viola!![]()
(deliberate spelling error in an attempt at humour)
![Wink :wink:](./images/smilies/icon_wink.gif)
Victrola!
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
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:
Then always put a '1' in the Key Expression to use the lookup.
Code: Select all
SELECT 1, my_seq.nextval FROM dual WHERE 1=:1
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers