Insert a row in target DB2 table when Source DB2 tbl empty

[Titto]

Hi,

I have job which reads a source DB2 table and write it to a sequential file using Transformer. at the same time i am inserting a row to another target DB2 table with some status information, but if the source is empty it is not inserting a row in target DB2 table. Is there a way to do so?
I tried using Constraint to check one of the Job Parameter > 0, still it is not working.

Any help is appreciated!

Thanks

[dsxdev]

Hi
Any stage will execute only when it gets an input or gives an output.

In your case the job design is like

DB2 -----> Transformer ------> DB2

For your target DB2 Stage to execute yo need to output a record from Transformer and for Transformer to output there shoudl be a record input.
So in your source DB2 modify your query to generate atleast record.
This can be achieved by using a aggregate function like sum or get a value from dual and join with the existing query, etc.

So that there is a record output even though the source table is empty.[/code]

[ketfos]

Hi,
What information you want to get inserted in the target, when there is not information in the source record?

Ketfos

[Titto]

I need to insert status of the process like start time stamp end time stamp. but sometimes souce will be empty at that time also i need to insert the status saying that the process is done.

[ketfos]

Hi,
I would suggest you to look into the different system variables for this purpose.
@time, date,....

You can insert this in target table, even if the source is empty

Ketfos

[Titto]

I tried with @INROWNUM = 0, it didn't work out..

[chulett]

I tried with @INROWNUM = 0, it didn't work out..

'Course not, there's no such thing.

As already noted, for the job to write to the audit table you need to have it process at least one row. Now, that can be a bogus record 'unioned' on in your source query that your job knows to not process or do something to generate that one row in your job. For example, an extra link that starts with transform and only generates a single row. I believe there's a FAQ on this technique in the FAQ forum.

Probably better to seperate the stats gathering to another job or possibly an after-job routine. Then you could get the stop/start times and rows counts even if the job processed zero records.

[Titto]

I believe there's a FAQ on this technique in the FAQ forum

I did not find in FAQ's

Is there a way to find SQLCODE in Transformer from pregious DB2 stage?

Thanks

[ketfos]

Hi,
If you something like a reject link coming out of the transformer,
you can use paramters like SQLSTATE,DBMSCODE to find the reason why the record was rejected.


Ketfos