DB2 query to use Incoming data

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
Steve@HSBC
Participant
Posts: 40
Joined: Tue Mar 02, 2004 8:21 am

DB2 query to use Incoming data

Post by Steve@HSBC »

I have a shared container which is selecting records based on a join and a where predicate. The where statement needs to have the value passed into the shared container and then DB2 can look up the data i need and spit it out.

I know how to do this in an insert, using (?,?,? etc) but how do i do this in a simple select using a reference link (lookup)?

the sql i have is:

Code: Select all

SELECT   A.LOCA_ID
       , B.IP_ID
       , A.ADR_TYCD
       , A.ADR_DES_1
       , A.ADR_DES_2
       , A.ADR_DES_3
       , A.ADR_DES_4
       , A.ADR_DES_5
       , A.ADR_DES_6
       , A.ADR_DES_7
       , A.ADR_DES_8
       , A.ADR_DES_9
       , A.ADR_DES_10
       , A.ADR_DES_1A
       , A.ADR_DES_2A
       , A.ADR_DES_3A
       , A.ADR_DES_4A
       , A.ADR_DES_5A
       , A.ADR_DES_6A
       , A.ADR_DES_7A
       , A.ADR_DES_8A
       , A.ADR_DES_9A
       , A.ADR_DES_10
from #pUKDWSCHEMA#.ADR A, #pUKDWSCHEMA#.LOCA_RELN B
where A.LOCA_ID = B.LOCA_ID
and B.#pLkUp#=IPID
and B.IP_LOCA_RELN_TYCD in ( '1', '4')
order by B.IP_LOCA_RELN_TYCD ASC, B.REC_ACDT DESC
fetch first 1 rows only


;
where IPID is i have tried ?.

Any ideas or suggestions?

Cheers
Sainath.Srinivasan
Participant
Posts: 3337
Joined: Mon Jan 17, 2005 4:49 am
Location: United Kingdom

Post by Sainath.Srinivasan »

You should be able to use '?' or ':1' to substitute the incoming link value in the shared container.

Try creating a job with the link within the job and then convert the part of job into a shared container.
Steve@HSBC
Participant
Posts: 40
Joined: Tue Mar 02, 2004 8:21 am

Post by Steve@HSBC »

Think i may be doing something wrong.

When i try and use :1 i get the following error:

Code: Select all

DB2_UDB_API_2: [IBM][CLI Driver][DB2/AIX64] SQL0312N  The host variable "1" is used in a dynamic SQL statement, a view definition, or a trigger definition.  SQLSTATE=42618
Not sure what i need to do.. i have tried atering the job so it looks like this now:
Image
Steve@HSBC
Participant
Posts: 40
Joined: Tue Mar 02, 2004 8:21 am

Post by Steve@HSBC »

I have left the job as in the picture above and have linked the ID in the transformer as a key field and done the koin that way (before i had the hash file as a stream link instead of DB2).

I now do not use ? or :1 (as i couldn't get them to work not sure why).

So so far it works.

Can you use order by's in sql statements? i am expecting upto 2-3 rows per ID and want to limit them to the latest row, i did this by an order by col1 asc, col2 desc, and then selected the first row. How can i do this in DS?
Sainath.Srinivasan
Participant
Posts: 3337
Joined: Mon Jan 17, 2005 4:49 am
Location: United Kingdom

Post by Sainath.Srinivasan »

By the principle, a lookup in DataStage returns only one row to the main stream. I assume it is the last record of the 'record set of multiple records'. Hence if you have an 'Order By' so that the last record retrieved for that query appears at the bottom, you may be able to achieve your desired result.
Baldmartyr
Participant
Posts: 108
Joined: Mon Oct 21, 2002 8:30 am

Post by Baldmartyr »

Steve@HSBC wrote:
Not sure what i need to do.. i have tried atering the job so it looks like this now:
Image
Steve, using a pic like that is a great way to communicate. I give it the webmaster's thumbs up! :D
David Baldwin
Former DSXchange Webmaster
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Sainath.Srinivasan wrote:By the principle, a lookup in DataStage returns only one row to the main stream. I assume it is the last record of the 'record set of multiple records'. Hence if you have an 'Order By' so that the last record retrieved for that query appears at the bottom, you may be able to achieve your desired result.
Actually, from what I recall you get the first record. :? I'd suggest writing your query such it doesn't return multiple records so that you know for certain what record you will get.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

As Craig says, with an OCI stage you will get the first matching row (check off the property to ignore errors). With a DB2 stage, there is no such option to ignore errors, so the job will just blow up when you match more than one row.

Mike
Steve@HSBC
Participant
Posts: 40
Joined: Tue Mar 02, 2004 8:21 am

Post by Steve@HSBC »

Baldmartyr, glad you approve of the pic (didn't think to ask before posting). Paintedover host is free to use as well adn generated the forum code to show it like that.

Thanks for the help in the end i got DS to generate its own sql and the ip_id = ? works when it does it!!!!

I have aslo added an order by clause to the where statement and a fetch first 1 row only and i get no errors (might be of use to someone).

Thanks again.
Post Reply