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



Group memberships:
Premium Members

Joined: 05 Jul 2012
Posts: 15
Location: Chennai
Points: 140

Post Posted: Thu Jan 11, 2018 12:42 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi all,
I got requirement to change the below existing query in to a DS Job

PFB the sample query

INSERT INTO Table_Name (A, B, C, D, E, F, G, H)
VALUES ((SELECT MAX(id) + 1 FROM id_table), '', 5, 'ABBEY', '', 1, 1, 'EFG');


How can I achieve inserting the first value(select query from a table) using the DS job.
Kindly help

_________________
Thanks
Nibu Mathew Babu
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42334
Location: Denver, CO
Points: 217378

Post Posted: Thu Jan 11, 2018 12:45 pm Reply with quote    Back to top    

Your source would be the "SELECT MAX(id) + 1" from the table noted and then you would use another stage (say, a transformer) to add the other hard-coded values to the row before sending it to the target.

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
nibumathewbabu



Group memberships:
Premium Members

Joined: 05 Jul 2012
Posts: 15
Location: Chennai
Points: 140

Post Posted: Thu Jan 11, 2018 3:01 pm Reply with quote    Back to top    

Thanks Craig for the swift response but there is a small change in requirement

if you see the above query, the value for field A should come from select query, B,C,D,E,F Values will come from a source file
columns G,H will be again from transformer(Hard coded)

The whole idea is there are some fields from an input file they need to add some more fields which static value(hard coded from transformer) and a key value for the row,(which is our select maxid query)

Kindly suggest,

_________________
Thanks
Nibu Mathew Babu
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 282

Points: 2811

Post Posted: Fri Jan 12, 2018 10:00 am Reply with quote    Back to top    

you will need to take care here due to parallel processing. if you query the table and get max is 100, then try to insert 10 records on 4 nodes, you will then insert 101 4 times, 102 4 times, and 103 2 times if you don't handle the parallel logic properly.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42334
Location: Denver, CO
Points: 217378

Post Posted: Fri Jan 12, 2018 3:04 pm Reply with quote    Back to top    

For multiple records that need to start with MAX+1 and (I assume) keep incrementing with each row, I'd suggest a different approach. I'd look into sending that current MAX value into the job a ...

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
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