utility run job: get return values

chulett · Post by **chulett** » Fri Oct 06, 2006 7:51 am

That just sounds like normal hashed file reference work. Can you not build a hashed file with the appropriate data and look it up in the XML job flow?

stivazzi · Post by **stivazzi** » Fri Oct 06, 2006 9:20 am

but i need a db connection... i need to use a value taken from an xml tag as a parameter...
ex:
<tag>valueTag</tag>
my select will be: SELECT A,B, from table where C = valueTag
ho can i use hashed file? i need to pass my valueTag to oracle stage...

thanks
Andrea

chulett · Post by **chulett** » Fri Oct 06, 2006 10:57 am

This - leveraging hashed files - is really basic stuff and core to properly using the product. It's not something a post or two on the web will help a great deal with. Do you have any DataStage resources locally there with you, people that are more familiar with the product that could help?

Have you not done anything with hashed files yet in DataStage?

Basically, you would create a hashed file of all applicable values from the database, keyed by 'C' with 'A' and 'B' as data fields. Then it's trivial to pick them up in the jobstream. The 'trick' is creating just what you need in the hashed file rather than dumping every possible value. However, if that is not all that large, it may very well be the answer.

Or for small volumes you could do the lookup directly against Oracle using an OCI stage. It is slow but 'slow' is a relative term and the runtime of that solution may be perfectly acceptable to you.

chulett · Post by **chulett** » Sat Oct 07, 2006 3:14 pm

Let's give this a shot with the OCI stage. There's no need to create another job that needs to run for each row of the XML file you process. Instead, you can connect to your Oracle database right in the XML job and just issue the singleton query once for every record being processed. As noted, not as efficient as the hashed file approach but perfectly fine for the 'small' volumes I'll wager you are dealing with.

Drop an OCI stage on the top of the canvas and drag a link down to the transformer where you need to do the lookup. It will create a dashed line rather than a solid line to indicate a reference link.

Inside the OCI stage, set the Query Type to 'Generate SELECT clause from column list; enter other values'. On the SQL tab, click on the SQL Clauses and put the table name in there for now, nothing else. Then on the Columns tab, define the three columns that you need in this order: C,A,B. Mark the 'C' column as the Key column, the other two should say 'No'.

Back on the SQL tab, you should see something like this as your generated SQL:

Code: Select all

SELECT C,A,B FROM YOUR_TABLE WHERE C = :1

This gives you what you need to get your A,B values for every occurance of 'C' that flows through your job. Open up the transformer and you'll see a red Key Expression on the lookup - that's where you drag the name of the incoming data field that has the value of 'C' that needs to be thrown against the reference query.

Hope that helps. Post back if you have any questions or concerns.

stivazzi · Post by **stivazzi** » Mon Oct 09, 2006 4:36 am

hi chulett, i'm using your approach method to do what i need.. i have

xml -->transform <--oracle stage (through reference)
and from transform ---> collector (other data coming from other ways)
i have in oracle stage "select C,a,b from table where C = :1)
and in transformer a value of a tag linked to the only value (the C key) coming from reference ...
the error is "ORA-01008: not all variables bound".... the only variable i have is C... can not understand what it refers to...

thanks

Andrea

chulett · Post by **chulett** » Mon Oct 09, 2006 7:15 am

Is the stage generating the sql or did you type it in by hand? I'm guessing the later or you shouldn't be having this issue. Make sure all three columns are defined in the stage, the 'C' column is marked as a Key (the other two must not be) and see if that fixes it.