Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    36

    Unanswered: Ignoring an SQLCODE and continue next step in sequence of sql steps

    Hi,

    I'm executing a series of delete steps in a shell script.

    below is the example of delete step:

    db2 delete from contract.pre_air where season<2006
    db2 delete from contract.post_air where season<2006

    first time it's deleting fine. but in the next year when I'm changing the condition to 2007 and if there are no records for <2007 then the below error is coming and subsequent steps are not executing.


    SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
    query is an empty table. SQLSTATE=02000
    Cannot delete data from pre_air


    Now I want to ignore this situation and continue to next step. Can we store the sqlcode? can this be resolved by setting a condition to ignore the sqlcode?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This is not an error but rather a warning condition. A warning does not cause the termination (typically) and the execution continues.

    Of course, this depends on your actual code. If the code doesn't interpret SQLCODEs correctly, i.e. only treat negative SQLCODES as errors, you have to fix the code. Likewise, if you use SQL PL, you may want to have a look at condition handlers with which you can define what should happen in certain conditions - like "no records found" (aka SQLCODE +100).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2007
    Posts
    36
    That's correct.
    Actully I have written a condition saying..

    if [ "$ret_code" -ne "0" ] ; then
    echo "Cannot delete data from PRE_AIR " >> $STARSSCRIPTSLOGDIR/First_Time_Archival.log
    connect reset
    exit 2


    For the return code other than zero, I'm terminating the process. Now I want to change the process that if the records are not there ignore, i.e treat it as zero return code. otherwise terminate.
    ret_code is defined as the output return code(here SQL code)
    definition is as follows ...

    ret_code=$?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You could use:
    Code:
    if [ $ret_code -lt 0 ]; then
        ...
    fi
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Nov 2007
    Posts
    36
    I tried the lessthan zero. but ret_code is updating with 0 when succesfull and not zero when unsuccesful.
    I need to store the sqlcode into separate value and move zero to ret_code when this sql code like in this scenario so that I can ignore the empty records condition.

    Thanks,

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The DB2 command processor returns 2 if there was a warning, and 4 if an error.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Right... this is not the SQLCODE but rather the DB2 CLP return code.

    Another option is to use "db2 -a" to get the SQLCA and then parse the output to extract the SQLCODE from there.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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