Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2002
    Posts
    5

    Red face Unanswered: Passing Sql Errors to Unix ksh Script

    Can someone tell me how to pass the db2 sql errors out to
    a ksh script? I'm new to db2 and we have several scripts that
    run at night - and where the unix script processes ok we are
    getting some sql errors that we'd like to trap for -

    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Little Rock, AR
    Posts
    17

    Re: Passing Sql Errors to Unix ksh Script

    Originally posted by manialg
    Can someone tell me how to pass the db2 sql errors out to
    a ksh script? I'm new to db2 and we have several scripts that
    run at night - and where the unix script processes ok we are
    getting some sql errors that we'd like to trap for -

    Thanks
    Here's a quick example of an easy way to do it:


    SQLCODE=0

    SQLCODE=`db2 -ec +o "update db cfg for $DBNAME using dbheap 11370"`;

    if [ $SQLCODE -ne 0 ]
    then
    echo " "
    echo "Error: Failed to Update Db Cfg for $DBNAME"
    echo "Error: SQLCODE = $SQLCODE"
    exit 1
    Laura Smith, DBA

  3. #3
    Join Date
    Oct 2011
    Posts
    12

    Question

    Quote Originally Posted by llsmith View Post
    Here's a quick example of an easy way to do it:


    SQLCODE=0

    SQLCODE=`db2 -ec +o "update db cfg for $DBNAME using dbheap 11370"`;

    if [ $SQLCODE -ne 0 ]
    then
    echo " "
    echo "Error: Failed to Update Db Cfg for $DBNAME"
    echo "Error: SQLCODE = $SQLCODE"
    exit 1
    Dear Sir,

    I followed above script to prepare my ksh script with minor change, it always said the line "if [ $SQLCODE -ne 0 ]" is not vallid, and the return status = 0.

    My ksh script as below.
    db2 "set serveroutput on"
    SQLCODE=0
    SQLCODE=`db2 -td/ +o -f abc.sql`;
    if [ $SQLCODE -eq 0 ]
    then
    echo "sucess"
    exit 0
    else
    echo "failed"
    exit 1
    fi
    -------------------------------
    In abc.sql,
    BEGIN
    declare v_count integer;

    open cursor
    loop
    fetch xx into xx;
    .....
    set v_count = v_count + 1
    update abc set no_of_count = v_count where ....
    call dbms_output.put_line(v_count);
    end loop
    close cursor
    END
    /

    What wrong of the script ? Please kindly help.

  4. #4
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    I am not sure that
    Code:
    SQLCODE=`db2..........`
    is necessarily correct. SQLCODE is equal to the standard output of the script, not the exit code.
    The variable $? contains the exit code, 0 if successful.
    Better to do.
    Code:
    db2..................
    if [ $? != 0 ]
    then
    echo error message
    fi

  5. #5
    Join Date
    Oct 2011
    Posts
    12
    Quote Originally Posted by kitaman View Post
    I am not sure that
    Code:
    SQLCODE=`db2..........`
    is necessarily correct. SQLCODE is equal to the standard output of the script, not the exit code.
    The variable $? contains the exit code, 0 if successful.
    Better to do.
    Code:
    db2..................
    if [ $? != 0 ]
    then
    echo error message
    fi
    Dear Kitaman,

    It work, thanks ! But if I need to do some checking in sql and return false/-1 to the ksh script, how can I do it ? I tried to use command raise_error or signal, but it said it is invalid. Please kindly help.

    Thanks !

  6. #6
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Wrap the db2 command inside a script.
    The $? variable is equal to the exit code from the script.
    So.

    Code:
    db2  ..................
    if [ error condition a ]
    then
    exit 2
    fi
    if  [ error condition b ]
    then
    exit 3
    fi
    $? will have a value of 2 if condition a is met, 3 if condition b is met, 0 if the script terminates normally, and 1 if it fails for any other reason.

  7. #7
    Join Date
    Oct 2011
    Posts
    12
    Quote Originally Posted by kitaman View Post
    Wrap the db2 command inside a script.
    The $? variable is equal to the exit code from the script.
    So.

    Code:
    db2  ..................
    if [ error condition a ]
    then
    exit 2
    fi
    if  [ error condition b ]
    then
    exit 3
    fi
    $? will have a value of 2 if condition a is met, 3 if condition b is met, 0 if the script terminates normally, and 1 if it fails for any other reason.
    Dear Kitaman,

    Thanks for your help.
    I added "exit 2" in my sql and add the return code checking as below. After testing, whatever I set the exit value to 3,4,5 for testing, why it still return 4 ? Also, how can I show message as a log file during the job is running. I tried to usd dbms_output.put_line('start...'), but it seems not work.

    in ksh.
    db2 "set serveroutput on"
    $status=%?
    if [ $status -eq 0]
    then
    exit 0
    fi
    if [ $status -eq 2]
    then
    echo "warnning"
    exit 0
    fi
    if [ $status -gt 2]
    then
    echo "fail checking"
    exit 1
    else
    exit 1
    fi

    ---
    in sql

    if checking failed ...
    exit 2
    end if

    Thanks !

  8. #8
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    This line is incorrect.
    Code:
    $status=%?
    it should be
    Code:
    status=$?

  9. #9
    Join Date
    Oct 2011
    Posts
    12
    Quote Originally Posted by kitaman View Post
    This line is incorrect.
    Code:
    $status=%?
    it should be
    Code:
    status=$?
    Sorry for typing mistake. Actually, in my ksh, it state $status=$?. Whatever I set the exit code is 2 or 3, it still show the exit code is 4 in ksh. Do you know why ? Am I wrongly use the exit code ?

    In sql.
    exit 2;

    In ksh.
    status=$?
    echo status

    Many Thanks !

Posting Permissions

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