Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2013
    Location
    India
    Posts
    246

    Unanswered: no connection in backup script

    Hello all,

    DB2 10.1 LUW on RHEL5
    I am trying to deploy a local shell script for db offline backup.

    script is as below:


    #!/bin/bash
    BACKUP_DATE=`db2 get snapshot for database on dbmstc | awk '/Database status/,/^$/ { print $0}' | grep -i "Last backup timestamp"| awk '{print $5 " " $6}'`

    echo $BACKUP_DATE
    echo "Snapshot done"

    # 0 - backup not taken
    # 1 - backup already taken
    {
    db2 'connect to dbmstc'
    STATUS=`db2 -x "select case when date(TIMESTAMP_FORMAT(START_TIME,'YYYYMMDDHH24MISS ')) = date(TIMESTAMP_FORMAT ( '$BACKUP_DATE','MM/DD/YYYY HH24:MIS.NNNNNN')) AND OPERATION ='B'AND OPERATIONTYPE='F' AND OBJECTTYPE='D' then '1' else '0' end as status from sysibmadm.db_history fetch first row only"`
    }
    db2 'connect reset'




    if [ "$STATUS" -eq 1 ];
    then
    echo "BACKUP TAKEN at $BACKUP_DATE"
    else
    echo "BACKUP SCRIPT WILL EXECUTE"
    fi




    but while executing statement
    "STATUS=`db2 -x "select case when date(TIMESTAMP_FORMAT(START_TIME,'YYYYMMDDHH24MISS ')) = date(TIMESTAMP_FORMAT ( '$BACKUP_DATE','MM/DD/YYYY HH24:MIS.NNNNNN')) AND OPERATION ='B'AND OPERATIONTYPE='F' AND OBJECTTYPE='D' then '1' else '0' end as status from sysibmadm.db_history fetch first row only"`"

    Error is coming connection to db not exist.. but i have made the connection just before that statement. all statements are error free when executed individually.

    any clues pleases.

    thanks and regards
    ssumit
    ssumit

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Add an error check after the connect...for example:
    (( $? != 0 )) && echo "Failed to connect to the database" && exit 1
    Additionally, Always dot in the relevant db2profile at the start of the script.

  3. #3
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    thanks db2mor,

    I added db2profile. I checked db is connecting when statements are executing out of the script. incorporated error checking but no luck please help..

    thanks in advance

    ssumit
    ssumit

  4. #4
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    output of the script in original post is as follows:


    [db2inst1@db2host db2backup]$ . db_auto_bkp.sh
    07/21/2014 11:41:03.000000
    Snapshot done

    Database Connection Information

    Database server = DB2/LINUX 10.1.0
    SQL authorization ID = DB2INST1
    Local database alias = DBMSTC

    DB20000I The SQL command completed successfully.
    -bash: [: SQL1024N A database connection does not exist. SQLSTATE=08003: integer expression expected
    BACKUP SCRIPT WILL EXECUTE


    not able to understood why db connection is failing ?

    thanks

    ssumit
    ssumit

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

    seems that bash starts a subshell here:
    Code:
    STATUS=`db2 -x "select ..."`
    Use this instead:
    Code:
    tmpf=myfifo
    mkfifo $tmpf
    db2 -x "select ..." > $tmpf &
    STATUS=$(cat $tmpf)
    rm -f $tmpf
    or
    ksh and
    Code:
    db2 -x "select ..." | read STATUS
    Regards,
    Mark.

  6. #6
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    oops, it is the sub-shell. the connection you established disappears as soon as the subshell exits.

  7. #7
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    thanks mark and db2mor,

    it worked.
    ssumit

Posting Permissions

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