Best way to process a list and do perform SQL one at a time

[IPD]

Hi,

I am trying to model the following in datastage but note sure of the best method:

using datastage client v7.5.1A
Server 7.5.2

What I am trying to do to take a list of PNs from a file, and then process each of these PNs one by one against a DB. The sql is recursive on this db and many rows can be returned from one PN. Thus the reason why I like to process one PN ata time.

How is this best achieved in Datastage?

[ray.wurlod]

(a) a "list of things" loop in a job sequence

(b) a server job that iterates through the list (by reading the file) and invokes another job to process the data using UtilityRunJob() function from a Transformer stage

The second approach is necessary if you have a version earlier than 7.5, which is the version in which job sequence loops were introduced.

[JoshGeorge]

Start a loop (1 to EOF), read current loop (counter) number line from the seq file using UVactivity (Write a routine to read seq file for the counter specific line) and pass that PN as parameter to your Job.

[IPD]

Ok I have some ideas on what has been said and view some other topics but we are new to datastage and need a little hand holding, if you don't mind. I read the loop documentation but it's very basic. So this is what I tried to do:

I have an execute command step that simply cat's a file:

Code: Select all

cat /sail/u/whitef/RoHS/active_batterries.txt

This is linked to the Start Loop activity where I have list loop selected. From the pull down list I selected $CommandOutput
which inserts this:

Code: Select all

#Execute_Command_15.$CommandOutput#

Here is my first problem what do I use as a delimiter for the EOL? Or how do I give each line a delimiter? As I would want only 1 part number at a time.

My next step is to link to a job activity which would be a DB2 API query.
I need the #Execute_Command_15.$CommandOutput# value in the query. How do I pass this on into the db2 SQL?

[ray.wurlod]

Specify the UNIX newline character as the delimiter. The easiest way to do this is to set up a user variable, and assign Char(10) to it, upstream of the StartLoop activity.

Pass the value of StartLoopName.$Counter to the job as a parameter. This will contain the value of the current item from the loop list.

[IPD]

Ray,

Thank you very much for the information. This has helped me a lot but I still have one issue and I believe it is centered around the delimiter in the file. Here is what I have done:

If I use your suggestion on the CHar(10) [by way we are on AIX but I think it is the same], the loop tries to pass in the whole file. So as a test, I first just entered in values to make sure everything worked. Doing this worked, the db2 SQL recieved the values and processed correctly. So I decide to add commas to the file, and use comma delimited. When this is done the loop activity reads the file but adds one more value then it should. It iterates the first time fine but fails with this error on the 2nd iteration:
oop_test..JobControl (@Job_Activity_28): Controller problem: Error calling DSSetParam(PN), code=-4
[ParamValue/Limitvalue is not appropriate]

I read the forums and found tidbits about using field or ereplace commands but this didn't do anything for me(I used them in the execute sequence stage and the start loop stage). The db2 job thinks the parameter is
DB2_UDB_API_26,0: Fatal Error: Fatal: [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "nd_15.$CommandOutput". Expected tokens may include: ")". SQLSTATE=42601
SQLExecDirect: Error executing statement 'SELECT * from iwh.asp_part where part_number=EReplace(Execute_Command_15.$CommandOutput'.

So I am not sure how to proceed , I am very close but I am obvisouly missing something here.

[IPD]

Still need help:

If I modify the file for the cat command to:

Code: Select all

 '000000000001','000000000002','000000000003'

and use comma delimited in the loop it will work until the last record.

if I add an extra , to the end:

Code: Select all

'000000000001','000000000002','000000000003',

it thinks there are 4 entires and aborts on the 4th iteration.

in AIX i can manually delete the last end of file character by issuing ctrl-d twice. The loop works without failure.

So it definetely failing on the end of file marker(or something similiar) but I do not know how to automatically remove this from the file or make data stage ignore this. Can someone set me straight, please..it's driving me nuts.

[ray.wurlod]

Code: Select all

Left(ExecuteCommand.$CommandOutput, Len(ExecuteCommand.$CommandOutput) - 1)

[IPD]

Thank you Ray,

I understand what you have suggested but we either have a bug or something else.

If I use your statement in the start loop activity which I believe is where this would go, what gets past to the job activity is

Code: Select all

PN = Left('0000044H2790'

so its finding the input from the file but everything else seems to be a string rather executing the Left command.

So am I misunderstand here or is there another issue?

[JoshGeorge]

Why not post your sequence design here. Start loop to End Loop stages.

[IPD]

Ok here is a link to the job report:

http://ipd.bravehost.com/loop_test1.htm

This might shed some light on my problem...thank you.

[ray.wurlod]

You should be passing just a value, not an assignment statement. Get rid of the "PN = ".

[IPD]

You should be passing just a value, not an assignment statement. Get rid of the "PN = ".

I apologize, I should not have highlighted the following in code, my fault.

Code:
PN = Left('0000044H2790'

This was suppose to be the output from the director on the job parameter, showing that it was passing somehting different than expected.

[ray.wurlod]

Just to clarify, the expression (containing the Left() function and activity variables) needs to occur in a job sequence, in the grid where you assign values to the job parameters before invoking the job run request. Is this your case?

[IPD]

Ray, thank you. I had the code in the start loop activity rather than in the
Sequence job activity. Once I made that change it all worked. Thank you again for your patience and help.