Results 1 to 10 of 10

Thread: bash sqlstate

  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: bash sqlstate

    I am not a real programmer but using shell scripts to get db2 data
    whenever we execute db2 commands we check rc to see if ok
    bit the question would be : is there a possibility to retrieve the sqlstate with shell script ? in case we execute connect we need to know if we can connect or if db is in standby mode ? (this could be retrieved from remote attach, but for some other info we would like to get sqlstate)
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Here is an example for backup command. This uses ksh, but may work for bash, or just include the first line and ksh will be invoked.

    Code:
    #!/bin/ksh
    
    DB=$1
    
    . $HOME/sqllib/db2profile
    
    db2 -v "backup db $DB online to /backups/$DB compress include logs"
    
    RC=$?
    
    if      [[ $RC -gt 0 ]]
    then
            xxxxx        
    else
            yyyyy
    fi
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    thanks for the update
    but $? returns 0-2-4 for ok-warning-error not sqlstate

    option for clp -ec returns sqlcode : -1776(4)

    thanks for all update...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Correct, the CLP exit codes are only those documented at:
    Command line processor return codes

    The reason for that design is probably portability - while Unix type environments had 2 (or more) bytes for exit-codes, other operating systems (e.g. legacy DOS) had only 1 byte.

    You cannot force the SQLSTATE value to be an exit-code, unless you wrap the CLP calls.

    With the -es option you get the SQLSTATE on the stdout - which means that you must capture and parse that output and search for the SQLSTATE. This is messy, but possible.

    In some previous cases I have used this combination: "db2 -o- -es ..."
    which discards the output and only gives the SQLSTATE
    but that has limited general use, although it easier to parse.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by przytula_guy View Post
    thanks for the update
    but $? returns 0-2-4 for ok-warning-error not sqlstate

    option for clp -ec returns sqlcode : -1776(4)

    thanks for all update...
    I was assuming that if you got a non-zero return code in the script, that you would just display the entire DB2 error message from your output. If you need to capture only the actual sqlstate or sqlcode in a variable, you would have to parse that. However if the command could be invoked in a stored procedure (many can be) it can be captured with a Condition Handler as a return parmameter of the SP.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    yes, thanks all
    the reason for this was : we want to connect to a remote db and have to find out if it is primary or standby according the rc we get (there could also be error because instance is down-db unavailable..) : we could attach to remote instance and get info from db cfg..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This can be used to check HADR role and backup if database is not STANDBY. Obviously can be adapted to other needs:

    Code:
    if (db2 get db config for $1 |grep -sq "role.*\(PRIMARY\|STANDARD\)" )
    then
           db2 backup db ....;
    fi
    I use this to check if instance running

    Code:
    db2 list active databases > $OUTPUTFILE
    COUNT=`grep "SQL1032" $OUTPUTFILE |wc -l`
    
    if      [[ $COUNT -gt 0 ]]
    then
            <send email alarm that instance not running;>
    fi
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I have a script that calls 2 other scripts: reorg, runstats and then executes db2rbind. All write to separate log files. I want to add error and possibly warning checking (check for $? = 4 or $? = 2). If some error/warning is detected, it will execute another script that writes to a log file monitored by ITM monitoring product.


    What's the best way to accomplish this? Do I have to add $? for every reorg/runstats command? Or could I grep the log files for != DB20000I or something similar?

    Does anyone have any examples of error checking for reorg/runstats/rebind maintenance scripts?

  10. #10
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    I prefer that my scripts *always* check every CLP return code, and log anything higher than 3 to a file.

    Depending on what I ask the CLP to do, the exit-code 1 or 2 can be significant so I test for them individually only when that is actually necessary.

    If you script correctly, this can be very economical and has minimal overhead - the benefits far outweigh the disadvantages.

    Example: use a function (which takes arguments) to do the logging work, suppose for example you called it logError and it took a single argument which is the string to log, then after each CLP call (in either bash or korn shell) I would then use:

    db2 "do something for me"
    (( $? > 3 )) && logError "Something bad happened".


    Note: I use "> 3", because the CLP exit-code can be 0 (success) 1(no rows), 2(warning) 3 (no rows + warning). Anything higher than 3 is some error condition.

    If you are smarter, you can have the common function take extra arguments such as whether or not to abort, whether to alert, the script-name and line-number for the error etc etc.

    You can do the same thing in Windows CMD scripting (just different syntax of course).

Posting Permissions

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