Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    74

    Post Unanswered: About Command Line Processor Return Codes

    HI,all
    We are on DB2 v7.1. We want to get the return codes of CLP in a shell script. I know that the CLP return an exit (or return) code when the command line processor finishes processing a command or an SQL statement. For example, the following Bourne shell script executes the GET DATABASE MANAGER CONFIGURATION command, then inspects the CLP return code:

    db2 get database manager configuration
    if ["$?"="0"]
    then echo "OK!"
    fi

    But how can I get the return code I defined in a SQL procedure. For example, I developed a SQL procedure, the interface of the SQL procedure is "proc(in in_var integer, out errorCode integer, out errorLabel varchar(255))". I invoke the db2 command in a Bourne shell script,

    db2 "call proc(50, ?, ?)"
    ...

    How can I get the errorCode and the errorLabel I defined in the Bourne shell script?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: About Command Line Processor Return Codes

    See if this thread throws light in the right direction ..

    http://dbforums.com/t639245.html

    Cheers

    Sathyaram

    Originally posted by zhouhaiming
    HI,all
    We are on DB2 v7.1. We want to get the return codes of CLP in a shell script. I know that the CLP return an exit (or return) code when the command line processor finishes processing a command or an SQL statement. For example, the following Bourne shell script executes the GET DATABASE MANAGER CONFIGURATION command, then inspects the CLP return code:

    db2 get database manager configuration
    if ["$?"="0"]
    then echo "OK!"
    fi

    But how can I get the return code I defined in a SQL procedure. For example, I developed a SQL procedure, the interface of the SQL procedure is "proc(in in_var integer, out errorCode integer, out errorLabel varchar(255))". I invoke the db2 command in a Bourne shell script,

    db2 "call proc(50, ?, ?)"
    ...

    How can I get the errorCode and the errorLabel I defined in the Bourne shell script?

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    You could play around with awk/grep/sed on the output to get the relevant parts. Read the formatted output into variables in your shell script.

    The example below will work only if the values you are looking to attain do not contain ":" as I have used that as the field delimiter.

    db2 "call proc(50, ?, ?)" | \
    awk -F":" '/(^ERRORLABEL|^ERRORCODE)/ {errs[$1]=$2}
    END {print errs["ERRORLABEL"],errs["ERRORCODE"]}' | \
    read errorCode errorLabel

    HTH
    Last edited by Damian Ibbotson; 01-09-03 at 07:21.

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by Damian Ibbotson
    You could play around with awk/grep/sed on the output to get the relevant parts. Read the formatted output into variables in your shell script.

    The example below will work only if the values you are looking to attain do not contain ":" as I have used that as the field delimiter.

    db2 "call proc(50, ?, ?)" | \
    awk -F":" '/(^ERRORLABEL|^ERRORCODE)/ {errs[$1]=$2}
    END {print errs["ERRORLABEL"],errs["ERRORCODE"]}' | \
    read errorCode errorLabel

    HTH
    You may also have problems using read if your expected values contain whitespace. So...

    db2 "call proc(50, ?, ?)" | \
    awk -F":" '/(^ERRORLABEL|^ERRORCODE)/ {errs[$1]=$2}
    END {print errs["ERRORLABEL"]"#"errs["ERRORCODE"]}' | \
    IFS="#" read errorLabel errorCode
    Last edited by Damian Ibbotson; 01-09-03 at 07:38.

  5. #5
    Join Date
    Jan 2003
    Posts
    74
    Thanks a lot!

  6. #6
    Join Date
    Mar 2006
    Location
    Bogota, Colombia
    Posts
    21

  7. #7
    Join Date
    Mar 2006
    Location
    Bogota, Colombia
    Posts
    21
    The problem of wrapping the command into a subshell is that you lost the output.

    I have been using another solution that keeps the output and analyzes the error code of a stored procedure inside an script

    db2 -r /tmp/db2unit.output -v "call myStoredProcedure"
    tail -1 /tmp/db2unit.output | awk ''''/Return Status/ {print "echo \$((\$SUM+"$4")) > /tmp/sum"}

    /tmp/returnCode.sh

    exit $(cat /tmp/sum)

    https://github.com/angoca/db2unit/bl...Body.sql#L1448
    AngocA

Posting Permissions

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