Database inserts and updates with datastage

[ScubaSteve]

As part of a migration process I have to record receipt and validation of multiple input files to the process.
To do this I need to to do the following:
* Create an entry in a a batch register table on an oracle database - I have written the following sql to do this:

Code: Select all

INSERT INTO MIGRATIONBATCH
(MIGRATIONBATCHID, BATCHCREATEDATE)
VALUES
(batch_seq.nextval, sysdate);

select file_seq.currval as NewBatchID from dual;

* Create an entry in a a file register table on an oracle database linked to this batch record - the sql to do this will be similar to the previous step buit would need the id obtained there as input. Something like:

Code: Select all

INSERT INTO INPUTFILE
(INPUTFILEID, MIGRATIONBATCHID, FILENAME, STATUS)
VALUES
(file_seq.nextval, [NewBatchID from previous step], [FileName], 'Awaiting Validation');
[b]*[/b] A Px process is then to be run that will retrieve entries for a specified file name that are in "Awaiting Validation" status and perform data validation actions and if successful set file to "Valid"

I have been working with the ODBC connector to test some of these concepts. I can retrieve data from an oracle with a standard select but sql code like the listings above i get execute failed errors without any additional info to follow up on.

How do I go about tackling this kind of process in datastage?

[ray.wurlod]

Step 1 is very straightforward. Generate the current date (CurrentDate() function) in a single generated row and use user-defined SQL to invoke the NEXTVAL property of the sequence.

Step 2 is likewise very simple; use that SQL to retrieve the CURRVAL property and park it somewhere (a file or the job's user status area).

Step 3 is a job that has a parameter whose value has to be supplied with the result of Step 2. This is most easily done in the sequence that controls executions of Steps 2 and 3 (and maybe Step 1 as well).

[ScubaSteve]

Thanks for taking the time to respond:
I am however having a hard time making sense of it, being fairly new to datastage. My comments are listed beneath your steps.

Step 1 is very straightforward. Generate the current date (CurrentDate() function) in a single generated row and use user-defined SQL to invoke the NEXTVAL property of the sequence.

I am unsure why you would want to generate the date seperately as i can use sysdate in Oracle as part of the insert which i have to do at some point.
As i understand this step as you propose it i will have a row generator outputting a field containing current date and a odbc stage with userdefined sql in to retrieve the next value for the batch sequence.
I need to use these values in an insert so I was hoping to connect the links from the row gen and odbc stage into another odbc stage but i cant do this as I get the following error:

(40828) The destination stage does not support input links when it has non-reject output links.

Step 2 is likewise very simple; use that SQL to retrieve the CURRVAL property and park it somewhere (a file or the job's user status area).

Similarly to the previous steps I need to access these values from within a odbc connector to do inserts.
Furthermore, as far as I know UserStaus is not accessible in a Px job. File storage is probably the only option in a Px job.

Step 3 is a job that has a parameter whose value has to be supplied with the result of Step 2. This is most easily done in the sequence that controls executions of Steps 2 and 3 (and maybe Step 1 as well).

With all the uncertainty in my mind on the previous 2 steps I have not considered what comes after that yet.

[ray.wurlod]

You need to generate a row to have DataStage trigger the insert. Unless you want to generate a dummy column that will also be inserted, I feel it's just as easy to generate the date.

You MUST have two jobs if you are using parallel jobs. Blocking operations (writing to and reading from the same stage) are prohibited. You can do this in a server job, however.