DSXchange

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

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
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

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.

ogmios wrote: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

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.

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).