db2 Enterprise Stage delete using Open Command
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 68
- Joined: Sat Feb 05, 2005 4:59 am
- Location: India
db2 Enterprise Stage delete using Open Command
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
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
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 68
- Joined: Sat Feb 05, 2005 4:59 am
- Location: India
The delete statement is part of the rollback back logic - deleting any rows that got committed on a job abort in the previous runray.wurlod wrote:WHY do you want to run a DELETE statement that will never delete any rows?!!
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Premium Member
- Posts: 68
- Joined: Sat Feb 05, 2005 4:59 am
- Location: India
-
- Premium Member
- Posts: 68
- Joined: Sat Feb 05, 2005 4:59 am
- Location: India
Hivmcburney wrote: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.
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 ?
-
- Premium Member
- Posts: 68
- Joined: Sat Feb 05, 2005 4:59 am
- Location: India
Hiadarsh shrinagesh wrote:vmcburney wrote: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.
Could you tell me what the pid would help me doing - i get a feeling I'm missing something.
-
- Participant
- Posts: 37
- Joined: Sun Aug 01, 2004 1:18 am
Re: db2 Enterprise Stage delete using Open Command
Hi,
Try some other way..
Delete table <---> where 1=1
Is it help you..??
Try some other way..
Delete table <---> where 1=1
Is it help you..??
Thanks,
Cnu
We work together to workout...
Cnu
We work together to workout...
-
- Premium Member
- Posts: 68
- Joined: Sat Feb 05, 2005 4:59 am
- Location: India
Re: db2 Enterprise Stage delete using Open Command
Am sorry but dont u think that wud delete all the records!!!DataStageCnu wrote:Hi,
Try some other way..
Delete table <---> where 1=1
Is it help you..??