DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Tue Mar 02, 2004 11:53 am Reply with quote    Back to top    

Does anyone know of a good way to reference in the next value from Oracle
into a DataStage server job? We are building a database in Oracle with
DataStage and need to write the id's along with using those id's as foreign
keys in other columns for tables in the same DataStage job.

Thanks - Rob

Rob Takkunen
International Decision Systems
Integrated Solutions Specialist
Direct: 612.851.3567
Mobile: 612.805.8826
rtakkunen@idsgrp.com

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Tue Mar 02, 2004 12:02 pm Reply with quote    Back to top    

I think you can use database triggers to generate sequence numbers.

Robert Takkunen wrote:

> Does anyone know of a good way to reference in the next value from Oracle
> into a DataStage server job? We are building a database in Oracle with
> DataStage and need to write the id's along with using those id's as foreign
> keys in other columns for tables in the same DataStage job.
>
> Thanks - Rob
>
> Rob Takkunen
> International Decision Systems
> Integrated Solutions Specialist
> Direct: 612.851.3567
> Mobile: 612.805.8826
> rtakkunen@idsgrp.com
>
> _______________________________________________
> datastage-users mailing list
> datastage-users@oliver.com
> http://www.oliver.com/mailman/listinfo/datastage-users

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Tue Mar 02, 2004 1:30 pm Reply with quote    Back to top    

It's easy and it's exactly what you do with Oracle.

In your ODBC or ORAOCI stage use user-defined SQL. Assuming it's an INSERT, refer to the sequence's NEXTVAL property to supply the value. And - here's the trick - your DataStage job does NOT provide a value for this column. So your SQL has the appearance:

INSERT INTO tablename(col1, col2, col3, col4) VALUES (atlas.NEXTVAL, :1, :2, :3);

and your DataStage job supplies three (not four) columns on the link.

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Tue Mar 02, 2004 1:32 pm Reply with quote    Back to top    

Hello Rob - We use this on our DataStage/Oracle jobs. Here are the steps we
use:

1.) Set up a specific Oracle sequence for the table you will be writing to
in Oracle.
2.) Design DataStage job to have an update path and an insert path.
3.) On Insert (so lookup is Null), have an Oracle Stage lookup that gets
NEXTVAL for your sequence.

Example: SELECT MY_ORACLE_SEQ.NEXTVAL FROM DUAL;

A Oracle trigger should work also.

Joel Kalton

-----Original Message-----
From: Robert Takkunen [mailto:rtakkunen@idsgrp.com]
Sent: Tuesday, March 02, 2004 11:54 AM
To: 'DataStage Users Discussion List'
Subject: Reference atlas.nextval from oracle


Does anyone know of a good way to reference in the next value from Oracle
into a DataStage server job? We are building a database in Oracle with
DataStage and need to write the id's along with using those id's as foreign
keys in other columns for tables in the same DataStage job.

Thanks - Rob

Rob Takkunen
International Decision Systems
Integrated Solutions Specialist
Direct: 612.851.3567
Mobile: 612.805.8826
rtakkunen@idsgrp.com

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Tue Mar 02, 2004 1:43 pm Reply with quote    Back to top    

Ray, what if you need the nextval in the transformer stage because you are
also using it for foreign keys in other related tables? Would you break the
process into a separate job for every table and then reference in that table
for the foreign key?

-----Original Message-----
From: datastage-users-bounces@oliver.com
[mailto:datastage-users-bounces@oliver.com] On Behalf Of Ray Wurlod
Sent: Tuesday, March 02, 2004 1:30 PM
To: DataStage Users Discussion List
Subject: Re: Reference atlas.nextval from oracle


It's easy and it's exactly what you do with Oracle.

In your ODBC or ORAOCI stage use user-defined SQL. Assuming it's an INSERT,
refer to the sequence's NEXTVAL property to supply the value. And - here's
the trick - your DataStage job does NOT provide a value for this column. So
your SQL has the appearance:

INSERT INTO tablename(col1, col2, col3, col4) VALUES (atlas.NEXTVAL, :1, :2,
:3);

and your DataStage job supplies three (not four) columns on the link.

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users
_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Tue Mar 02, 2004 1:59 pm Reply with quote    Back to top    

How about using OCI stage to look-up/reference 'select atlas.nextval,1
hardcoded from dual' as user defined query. 'hardcoded' can be joined to
another hard coded value in transformer.



Suresh Chilukuri

.state.nj.us> cc: (bcc: Malathi Vellanki/FIRNY/NorthAmerica/MCKINSEY)
Sent by: Subject: Re: Reference atlas.nextval from oracle
datastage-users-bounc
es@oliver.com
03/02/2004 01:02 PM
Please respond to
DataStage Users
Discussion List






I think you can use database triggers to generate sequence numbers.

Robert Takkunen wrote:

> Does anyone know of a good way to reference in the next value from Oracle
> into a DataStage server job? We are building a database in Oracle with
> DataStage and need to write the id's along with using those id's as
foreign
> keys in other columns for tables in the same DataStage job.
>
> Thanks - Rob
>
> Rob Takkunen
> International Decision Systems
> Integrated Solutions Specialist
> Direct: 612.851.3567
> Mobile: 612.805.8826
> rtakkunen@idsgrp.com
>
> _______________________________________________
> datastage-users mailing list
> datastage-users@oliver.com
> http://www.oliver.com/mailman/listinfo/datastage-users

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users



_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Tue Mar 02, 2004 2:21 pm Reply with quote    Back to top    

Excellent!! I think this will work and gets me in the right direction.
Thanks everyone for your help.

-----Original Message-----
From: datastage-users-bounces@oliver.com
[mailto:datastage-users-bounces@oliver.com] On Behalf Of
Malathi_Vellanki@mckinsey.com
Sent: Tuesday, March 02, 2004 1:59 PM
To: DataStage Users Discussion List
Cc: DataStage Users Discussion List; datastage-users-bounces@oliver.com
Subject: Re: Reference atlas.nextval from oracle







How about using OCI stage to look-up/reference 'select atlas.nextval,1
hardcoded from dual' as user defined query. 'hardcoded' can be joined to
another hard coded value in transformer.




Suresh Chilukuri


.state.nj.us> cc: (bcc: Malathi
Vellanki/FIRNY/NorthAmerica/MCKINSEY)
Sent by: Subject: Re: Reference
atlas.nextval from oracle
datastage-users-bounc

es@oliver.com

03/02/2004 01:02 PM

Please respond to

DataStage Users

Discussion List









I think you can use database triggers to generate sequence numbers.

Robert Takkunen wrote:

> Does anyone know of a good way to reference in the next value from
> Oracle into a DataStage server job? We are building a database in
> Oracle with DataStage and need to write the id's along with using
> those id's as
foreign
> keys in other columns for tables in the same DataStage job.
>
> Thanks - Rob
>
> Rob Takkunen
> International Decision Systems
> Integrated Solutions Specialist
> Direct: 612.851.3567
> Mobile: 612.805.8826
> rtakkunen@idsgrp.com
>
> _______________________________________________
> datastage-users mailing list
> datastage-users@oliver.com
> http://www.oliver.com/mailman/listinfo/datastage-users

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users



_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users
_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Mon Mar 08, 2004 1:00 am Reply with quote    Back to top    

However, be careful of the performance : if you have a huge number of lines
in your stream link, and for each line a lookup is done, your job can take
a lot of time to be executed.

I have succesfully used user-defined sql (as proposed by Ray) few months
ago, but it seems to me that, it works only if you put the seq.nextval as
the last column.

Kasia


At 02/03/2004 21:21, you wrote:
>Excellent!! I think this will work and gets me in the right direction.
>Thanks everyone for your help.
>
>-----Original Message-----
>From: datastage-users-bounces@oliver.com
>[mailto:datastage-users-bounces@oliver.com] On Behalf Of
>Malathi_Vellanki@mckinsey.com
>Sent: Tuesday, March 02, 2004 1:59 PM
>To: DataStage Users Discussion List
>Cc: DataStage Users Discussion List; datastage-users-bounces@oliver.com
>Subject: Re: Reference atlas.nextval from oracle
>
>
>
>
>
>
>
>How about using OCI stage to look-up/reference 'select atlas.nextval,1
>hardcoded from dual' as user defined query. 'hardcoded' can be joined to
>another hard coded value in transformer.
>
>
>
>
> Suresh Chilukuri
>
> Users Discussion List
> .state.nj.us> cc: (bcc: Malathi
>Vellanki/FIRNY/NorthAmerica/MCKINSEY)
> Sent by: Subject: Re: Reference
>atlas.nextval from oracle
> datastage-users-bounc
>
> es@oliver.com
>
> 03/02/2004 01:02 PM
>
> Please respond to
>
> DataStage Users
>
> Discussion List
>
>
>
>
>
>
>
>
>
>I think you can use database triggers to generate sequence numbers.
>
>Robert Takkunen wrote:
>
> > Does anyone know of a good way to reference in the next value from
> > Oracle into a DataStage server job? We are building a database in
> > Oracle with DataStage and need to write the id's along with using
> > those id's as
>foreign
> > keys in other columns for tables in the same DataStage job.
> >
> > Thanks - Rob
> >
> > Rob Takkunen
> > International Decision Systems
> > Integrated Solutions Specialist
> > Direct: 612.851.3567
> > Mobile: 612.805.8826
> > rtakkunen@idsgrp.com
> >
> > _______________________________________________
> > datastage-users mailing list
> > datastage-users@oliver.com
> > http://www.oliver.com/mailman/listinfo/datastage-users
>
>_______________________________________________
>datastage-users mailing list
>datastage-users@oliver.com
>http://www.oliver.com/mailman/listinfo/datastage-users
>
>
>
>_______________________________________________
>datastage-users mailing list
>datastage-users@oliver.com
>http://www.oliver.com/mailman/listinfo/datastage-users
>_______________________________________________
>datastage-users mailing list
>datastage-users@oliver.com
>http://www.oliver.com/mailman/listinfo/datastage-users



------------------------------------------------------------------------------
Kasia Lewicka katarzyna.lewicka@cgey.com
ETL Team Leader
Business Intelligence / Business Solution Skill
Cap Gemini Ernst & Young France
phone +33 [0]5 56 46 72 85

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Mon Mar 08, 2004 1:37 pm Reply with quote    Back to top    

It's not that it has to be last, it's merely that the job does not provide a column. For example INSERT INTO table (c1,c2,c3,c4) VALUES (atlas.nextval,:1,:2,:3); would suffice, and the job would only supply three columns on that link (values for columns c2, c3, and c4.
----- Original Message -----
From: Kasia Lewicka
Date: Mon, 08 Mar 2004 08:00:59 +0100
To: DataStage Users Discussion List
Subject: RE: Reference atlas.nextval from oracle
>
> I have succesfully used user-defined sql (as proposed by Ray) few months
> ago, but it seems to me that, it works only if you put the seq.nextval as
> the last column.
>
> Kasia

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours