Id to be Incremented for every job run

[rkumar28]

Hi,

I am new to DataStage. I have a situation related to one of the jobs I am creating on DataStage. I have a BatchId column in one of my RDBMS tables. I have to populate it with Unique ID that is same per job run. If I run the Job today and insert 10 rows, the BatchID column needs to be same(say: 100) for all the rows inserted. If I run the job again after a week and insert new rows, BatchId needs to be (say 101) for all the new rows inserted. The BatchId needs to increment by 1 for every job run.
I will really appreciate any help in this regard.

Thanks

[ogmios]

If the BatchId is per job per run you could use e.g. a "before" SQL (not available in ODBC stages, but exists in DB2 stages and others) to update the BatchId before using it in your main SQL. As in

Code: Select all

UPDATE BATCH_ID_TABLE 
SET BATCHID = BATCHID + 1
WHERE NAME = 'YourJobName';

If the BatchId is for all jobs, you could write a seperate DataStage job to update the BatchId and run this at the start of your control job.

Ogmios

[rkumar28]

Hi Ogmios,

Thanks for the quick response. We are using ODBC to connect to the TeraData Database as teradata stage is not available to us.


I am not sure if I should ask this question in this or put this as a new topic: How do we generate the Unique Sequence ID like (1,2,3....) for every row in DataStage.

If the BatchId is per job per run you could use e.g. a "before" SQL (not available in ODBC stages, but exists in DB2 stages and others) to update the BatchId before using it in your main SQL. As in

Code: Select all

UPDATE BATCH_ID_TABLE 
SET BATCHID = BATCHID + 1
WHERE NAME = 'YourJobName';

If the BatchId is for all jobs, you could write a seperate DataStage job to update the BatchId and run this at the start of your control job.

Ogmios

[ray.wurlod]

This is a very common requirement.

Your choices include a separate job to select the current maximum value from BATCH_ID_TABLE (ODBC stage), add 1 to it (Transformer stage) and update the value (BATCH_ID_TABLE).

You can also use BASIC SQL Client Interface (BCI) functions if you have a licensed ODBC driver - search the forum for BCI - in which case you can execute the SQL directly from DataStage BASIC.

[ray.wurlod]

Unique sequence should be another topic.

There are two system variables that you can use, @INROWNUM and @OUTROWNUM (possibly added to a constant, which you can keep in a Stage variable).