Stored Procedure with Return Value

[mhupman@citrrus.com]

I'm attempting to create a process that will loop over files in a given directory, record audit metadata about those files in an audit table, and lastly write the contents of those files into one or more tables.

Currently, I have a sequence job that loops over a list of files in the directory (obtained via a Routine). The sequence job then calls a parallel job, passing the file name as a parameter, and that parallel job streams records from the file into a table. All of this works as expected.

My problem is recording the audit metadata of the file (filename, date, etc.). It makes sense to me to do this via a stored procedure. Additionally, I feel like the sequence job is the correct place for this. Of course, sequence jobs don't have the ability to run Stored Procedure stages. I also would need to capture the output of the Stored Procedure, as that will provide me with an AuditID that I will pass as a parameter to the parallel job.

So my questions are as follows:
- Does my overall architecture seem sound? Sequence Job -> Loop over file list -> Record Audit Info (ideally) -> Call parallel job
- Does it make sense to use a stored procedure to record the audit info?
- If I am using a Stored Procedure, then I would need to capture the return value of the call (AuditID) - just want to make sure this is possible

Thanks in advance for any help you can provide - I'm obviously very new to DataStage so I appreciate any guidance I can get!

[chulett]

Welcome!

Your process is sound and matches what many people here have implemented, I would wager. There are many ways to skin that particular cat and that's certainly one. A couple of points...

I see absolutely no need for a stored procedure, to me that's an unnecessary complication. A routine or even a Server job is much more suited to this in my experience. As to the second point, I wouldn't use a Parallel job for any of this, especially looped. A Server job is more than capable of handling that duty and you don't need all that startup / shutdown / processing overhead that a PX jobs brings to the table.

[mhupman@citrrus.com]

chulett - Thanks for the input! Your point about Parallel vs Server jobs is well taken, I'll bring that up with my teammates.

I think I must have some confusion over the flow of the job that processes the file. Here is what I must do:

1) Detect a file on the filesystem (done by OS command in a Routine)
2) Assign it an AuditID (so make an insert to the Audit table with file metadata)
3) Retrieve that AuditID
4) Stream the data from that file into another table, using the obtained AuditID as the key
5) Finally, rename the file with the AuditID and move it to another folder.

It seems like I need to have step 1 & 5 in a Sequence job where I can execute OS commands (ls, mv, etc.). Based on your input, I should do all my DB work (inserting Audit row, getting AuditID, streaming data into table) in a Server job. However, how can I complete step 5 if the Server job is the only one that knows the AuditID? It seems like I would need a way to pass that value back up to the calling job, which I assume isn't possible.

Is my understanding wrong? Thanks again for your help!

[chulett]

It certainly is possible. Do an exact search here for the DSSetUserStatus function which will allow exactly that - pass the retrieved value back which can then be leveraged by anything downstream of that point.