Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: how to get full sql code in script

    DB2 v8.2 ESE on AIX v5.3 / DB2 v9.5.1 ESE on AIX v6.1
    anyone please - how can we get full sql code in the script? Like in example below we are using this logic to process sql errors:
    --------------------------------------------------------------
    function check_SQL
    {
    mSQLCode=$?
    if [ $mSQLCode -ne 0 ]; then
    echo "\nSCRIPT FAILED!!! SQL error: $1\n " | tee -a $mLogFile
    exit 1
    fi
    }

    db2 -v connect to $mdbName >> $mLogFile
    check_SQL "Connect to database failed!"
    -------------------------------------------------------------
    output (in file):
    connect to
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "TO".
    Expected tokens may include: "<identifier>". SQLSTATE=42601
    -------------------------------------------------------------

    So in function we get and process only sqlcode 0 or 'not 0'. In this case above mSQLCode=4 but we would like to have 'SQL0104N'.

    Thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can use option '-a' for the DB2 CLP and parse the SQLCODE from the dumped SQLCA.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by stolze
    You can use option '-a' for the DB2 CLP and parse the SQLCODE from the dumped SQLCA.
    the problem is that results and sql erros go to the same output. does this way separate them? would you happen to have an example, Knut?
    thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You are right on that the SQLCA is written to standard output - like the results. So you would have to parse the output. But that is really simple and straight-forward with a regexp (Perl style) like:
    Code:
    /sqlcaid\s*:\s*SQLCA\s*sqlcabc:\s*\d+\s*sqlcode:\s*(\d+)/
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by stolze
    You are right on that the SQLCA is written to standard output - like the results. So you would have to parse the output. But that is really simple and straight-forward with a regexp (Perl style) like:
    Code:
    /sqlcaid\s*:\s*SQLCA\s*sqlcabc:\s*\d+\s*sqlcode:\s*(\d+)/
    I think I can do the same with standard error message (SQL0104N An unexpected token "END-OF-STATEMENT"...). Just need to output it somewhere else...
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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