Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: DB2 scripting issue on AIX 7.1

    Hello

    Can you please help me to fix below issue on AIX 7.1 .

    I have written 2 simple Korn(ksh93) script to connect to db2 database and select system catalog table.

    Script 1:

    #!/usr/bin/ksh93
    # set -x
    #
    ######## housekeeping ########
    #
    function connect_to_db
    {
    set -x
    typeset db=$1
    typeset -i rc=0
    typeset result=""
    result=$(db2 "connect to $db")
    rc=$?
    }
    function get_table_state
    {
    set -x
    typeset obj=$1
    typeset dbname=$2
    typeset testmode=$3
    typeset -i rc=0
    typeset -u tabschema=${obj%.*}
    typeset -u tabname=${obj#*.}
    typeset result=""

    result=$(connect_to_db $dbname)
    result=$(db2 -a "select count(1) from syscat.tables")
    rc=$?
    }
    #
    ############ main ################################################## ######
    #
    set -x
    val_cntltab=xqctl.CTL00L501T
    val_db='XQ0S0D01' # test supplied arguments

    result=$(get_table_state $val_cntltab $val_db "table")
    exit $rc


    Script 2:

    #!/usr/bin/ksh93
    # set -x
    #

    connect_to_db()
    {
    set -x
    dbname=$1
    result=$( db2 connect to $dbname)

    }


    check_something()
    {
    set -x
    dbname=$1
    result=$(connect_to_db $dbname)
    result=$(db2 -a "select count(1) from syscat.tables")

    }


    dbname=$1
    result=$(check_something $dbname)



    Script 1 fails with an error SQL1327N. If you look at the script, I have done connect before executing the select.

    + result=$'\n Database Connection Information\n\n Database server = DB2/AIX64 9.5.10\n SQL authorization ID = XQ0H1\n Local database alias = XQ0H1D01'
    + rc=0
    + result=''
    + db2 -a 'select count(1) from syscat.tables'
    + result=$'\nSQLCA Information\n\n sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1327 sqlerrml: 5\n sqlerrmc: QUID?\n sqlerrp : SQLEUCCM\n sqlerrd : (1) 0 (2) 0 (3) 0\n (4) 0 (5) 0 (6) 0\n sqlwarn : (1) (2) (3) (4) (5) (6)\n (7) (8) (9) (10) (11)\n sqlstate: 2E000'
    + result=''
    + exit

    and Script 2 runs fine. I am not sure what is wrong with script1 as both scripts are almost same. This issue I am only seeing on AIX 7.1 . On AIX 6.1 both runs fine.

    your help is required to find the exact issue. Let me know if you need any other info.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    3

    Smile DB2 : Scripting issue with AIX 7.1

    Hello

    Can you please help me to fix below issue on AIX 7.1 .

    I have written 2 simple Korn(ksh93) script to connect to db2 database and select system catalog table.

    Script 1:

    #!/usr/bin/ksh93
    # set -x
    #
    ######## housekeeping ########
    #
    function connect_to_db
    {
    set -x
    typeset db=$1
    typeset -i rc=0
    typeset result=""
    result=$(db2 "connect to $db")
    rc=$?
    }
    function get_table_state
    {
    set -x
    typeset obj=$1
    typeset dbname=$2
    typeset testmode=$3
    typeset -i rc=0
    typeset -u tabschema=${obj%.*}
    typeset -u tabname=${obj#*.}
    typeset result=""

    result=$(connect_to_db $dbname)
    result=$(db2 -a "select count(1) from syscat.tables")
    rc=$?
    }
    #
    ############ main ################################################## ######
    #
    set -x
    val_cntltab=xqctl.CTL00L501T
    val_db='XQ0S0D01' # test supplied arguments

    result=$(get_table_state $val_cntltab $val_db "table")
    exit $rc


    Script 2:

    #!/usr/bin/ksh93
    # set -x
    #

    connect_to_db()
    {
    set -x
    dbname=$1
    result=$( db2 connect to $dbname)

    }


    check_something()
    {
    set -x
    dbname=$1
    result=$(connect_to_db $dbname)
    result=$(db2 -a "select count(1) from syscat.tables")

    }


    dbname=$1
    result=$(check_something $dbname)



    Script 1 fails with an error SQL1327N. If you look at the script, I have done connect before executing the select.

    + result=$'\n Database Connection Information\n\n Database server = DB2/AIX64 9.5.10\n SQL authorization ID = XQ0H1\n Local database alias = XQ0H1D01'
    + rc=0
    + result=''
    + db2 -a 'select count(1) from syscat.tables'
    + result=$'\nSQLCA Information\n\n sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1327 sqlerrml: 5\n sqlerrmc: QUID?\n sqlerrp : SQLEUCCM\n sqlerrd : (1) 0 (2) 0 (3) 0\n (4) 0 (5) 0 (6) 0\n sqlwarn : (1) (2) (3) (4) (5) (6)\n (7) (8) (9) (10) (11)\n sqlstate: 2E000'
    + result=''
    + exit

    and Script 2 runs fine. I am not sure what is wrong with script1 as both scripts are almost same. This issue I am only seeing on AIX 7.1 . On AIX 6.1 both runs fine.

    your help is required to find the exact issue. Let me know if you need any other info.

    Thanks

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    You shouldn't use the "db2" commands in sub-shells like "$(db2 ...)". Interpreter starts sub-shell in a new process, and db2 clp can loose communication with its background process.

    So, use:
    db2 "connect to $db"
    instead of:
    result=$(db2 "connect to $db")

    If you want to get a result, use:
    db2 -x "select count(1) from syscat.tables" | read result
    instead of:
    result=$(db2 -x "select count(1) from syscat.tables")
    Regards,
    Mark.

  4. #4
    Join Date
    Mar 2009
    Posts
    3
    Hi Mark

    Thanks for the solution. Actually these scripts are pretty old and these were working fine with AIX 6.1 . With recent migration to AIX 7.1 some scripts are working and some are not. Not sure what has changed in AIX 7.1. Need your expertise to check if I can get these script running without having to change all the scripts.

Posting Permissions

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