delete the data

mandla.srinivas · #1 (**permalink**) 01-05-07, 15:16

I am trying to delete the data which is older than 2 years.
So I have written the following query.But it is not getting executed successfully.Could you please help me in correcting the following query.
INL_PPN_TMS is time stamp.

delete from ABC
where date(ABC.INL_PPN_TMS) < (select date (current date -2 year) from sysibm.sysdummy1)

jayecarter · #2 (**permalink**) 01-05-07, 16:23

What is the error you recieve?

n_i · #3 (**permalink**) 01-05-07, 17:31

Quote:

Originally Posted by William of Occam

One should not increase, beyond what is necessary, the number of entities required to explain anything.

Code:

delete from ABC
where date(ABC.INL_PPN_TMS) < current date -2 years

jayecarter · #4 (**permalink**) 01-05-07, 17:39

So how do you know this didn't execute successfully? What sqlcode or sqlstate was returned?

mandla.srinivas · #5 (**permalink**) 01-06-07, 11:38

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. SQLSTATE=02000
------------------------------------------------------------------------
that particular query was giving the above message.So I thought this is an error.When it was not able to get the data which satisifies the delete statement condition it was above message.

I could run my informatica mapping success fully which is having this query.

Thank you very much for the response.

jayecarter · #6 (**permalink**) 01-06-07, 12:48

Is your issue resolved then?

Peter.Vanroose · #7 (**permalink**) 01-07-07, 06:45

Quote:

Originally Posted by n_i

Code:

delete from ABC
where date(ABC.INL_PPN_TMS) < current date -2 years

Or most probably even better

Code:

delete
from  ABC
where INL_PPN_TMS < current date - 2 years

And indeed, an SQLCODE of 100 (or equivalently a "SQL0100W") is perfectly OK since it just means that no rows were older than 2 years.

grofaty · #8 (**permalink**) 01-08-07, 00:55

mandla.srinivas,
SQL0100W - character W means warning. Character E means Error. So you got only warning, I can be everything ok, just to let you know no rows were deleted.

stolze · #9 (**permalink**) 01-17-07, 05:36

Try in your shell:

Code:

db2 "? SQL100W"

You can replace SQL100W with any error you receive from DB2. Actually, you can omit the trailing W, E, C, or I character, and you don't need the leading 0's in the number:

Code:

db2 "? sql100"

The only other thing to remember is how to get the message number based on the SQLCODE. You just remove the sign and prepend the string "sql".

Internet Services

Web Development

Digital Marketing

Consumer Tech