Results 1 to 9 of 9

Thread: delete the data

  1. #1
    Join Date
    Jan 2007
    Posts
    6

    Unanswered: delete the data

    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)

  2. #2
    Join Date
    Aug 2002
    Location
    St. Louis
    Posts
    16
    What is the error you recieve?

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Aug 2002
    Location
    St. Louis
    Posts
    16
    So how do you know this didn't execute successfully? What sqlcode or sqlstate was returned?

  5. #5
    Join Date
    Jan 2007
    Posts
    6
    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.

  6. #6
    Join Date
    Aug 2002
    Location
    St. Louis
    Posts
    16
    Is your issue resolved then?

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    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.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •