db2 Enterprise Stage delete using Open Command

[adarsh shrinagesh]

Hi

I am trying to execute a Delete statement in the Open Command section of an DB2 Enterprise Edition stage

ex - delete from table where 1=2

My doubt is if the where clause does not return data the delete statement returns an error code like :

db2 => DELETE FROM CARD.ADARSHDUMMY WHERE 1=2
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. SQLSTATE=02000

this also causes the job to fail if i use a similar query in the open command coz of the return code
Tgt_ABCD_FF,0: ERROR during execution of DB2-CLI requests: SQL_NO_DATA_FOUND; SQLSTATE = 02000; NativeErrorCode = 100; Message = [IBM][CLI Driver][DB2/AIX64] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000.

- is there any way i can treat this error as Non-Fatal.


Ps - I want to avoid using a Message Handler

[vmcburney]

You could run the delete as a seperate job and use a row generator (set to 1 row) to create the single delete statement. The Open statement has the error problem and it will try to execute the statement on all nodes, so you are also getting multiple duplicate deletes running at the same time.

[ray.wurlod]

WHY do you want to run a DELETE statement that will never delete any rows?!!

[adarsh shrinagesh]

WHY do you want to run a DELETE statement that will never delete any rows?!!

The delete statement is part of the rollback back logic - deleting any rows that got committed on a job abort in the previous run

The where condition(in delete) holds the timestamp for the last run - deleting any records if the job aborted on the last run.

Therefore the delete would hold good if the last run had aborted however if the last was a success it would not find any rows to delete.

where status ='F' and eff_timestamp=TimestampOfLastRun

[ray.wurlod]

OK, it was the WHERE 1=2 that got to me!

[vmcburney]

So you could perform your delete if your job aborts, a sequence job trigger executes a delete/rollback job that gets passed the process id and table name as a parameter and removes the transactions from that table. Could be used for any table.

[adarsh shrinagesh]

If I am not wrong the DB2 UDB API has the Toggle Switch for
Treat Errors as Non Fatal in the Before/After SQL Tabs which alleviates this problem

Do we have a similar option in The DB2 Enterprise Stage.

thnx

[adarsh shrinagesh]

So you could perform your delete if your job aborts, a sequence job trigger executes a delete/rollback job that gets passed the process id and table name as a parameter and removes the transactions from that table. Could be used for any table.

Hi

That solution does seem viable - and we were thinking on those lines as an alternative
But we were trying to accomplish this in the same job itself using the Open command

We already have routines that are being called once a job aborts to update the control table - n we cud stuff this logic in there

but should'nt the open cmd work ?

[adarsh shrinagesh]

So you could perform your delete if your job aborts, a sequence job trigger executes a delete/rollback job that gets passed the process id and table name as a parameter and removes the transactions from that table. Could be used for any table.

Hi

Could you tell me what the pid would help me doing - i get a feeling I'm missing something.

[DataStageCnu]

Hi,

Try some other way..

Delete table <---> where 1=1


Is it help you..??

[adarsh shrinagesh]

Hi,

Try some other way..

Delete table <---> where 1=1


Is it help you..??

Am sorry but dont u think that wud delete all the records!!!