Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    40

    Question Unanswered: Tablespace in Backup pending state

    Hi Folks,

    We are cuurently using db2 9.7 on AIX platoform for datawarehousing env. The problem is >> Lots of load jobs are running, all are non-recoverable. There are few tables which are enabled for NOT LOGGED INITALLY, also the load fails somtimes due to file missing or other issues.

    Issue >>
    One of the tablespace going in backup pending mode

    Any idea of what could the reason other than rollforward point - in - time or recoverable load ???

    Thanks in advance !!!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    NOT LOGGED INITIALLY is immediately turned off once the next commit happens.

    The only reasons that I know why a specific tablespace would be in backup pending state is:
    • Certain schema changes require an offline backup.
    • A Load was run without COPY YES or without NONRECOVERABLE keyword.


    A database can be in backup pending mode if logging is changed from circular to archive logging, or certain other logging parms are changed.

    You might want to check the db2diag.log to see if there any messages in there that might help understand what happened.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2008
    Posts
    40
    So, even we put the tablespace in non-recoverable mode, specifically what could be the other reasons for tablespace into backup pending mode ??

    Is it true, that if the load fails due to any reason, does it put tablespace into backup pending mode ???

    NOT LOGGED INITALLY does not affects in any way, right ???

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by jayshah7 View Post
    So, even we put the tablespace in non-recoverable mode...
    What exactly did you do (exact command) to put the tablespace in non-recoverable mode?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2008
    Posts
    40
    Please find one of the script :

    echo "################## RLS DB LOAD SCRIPT v1.2 ########################"
    echo "### ###"
    echo "### USAGE : sh loadrls.sh (from instance user) ###"
    echo "### Caution: Please check the path of the files before executing ###"
    echo "### Written by: Prem kumar John victor (1343182) ###"
    echo "### ###"
    echo "################################################# ##########################"
    echo ""
    echo "Please wait while loading..........."


    #path='/rlsdb/db2/ddb2bhi/'
    #path='/rlsdb/bh/dev/download/'

    path='/pdw/Bahrain/prod/download/'


    SCRIPT_PATH=`pwd`
    SCRIPT_OUT=${SCRIPT_PATH}/log/load_rlsdb_`date +'%d%m%y%H%M'`.log

    echo "Loading details can be found in $SCRIPT_OUT"



    db2 connect to DPPDWHK0;

    db2 "select CURRENT TIMESTAMP from sysibm.sysdummy1" >> $SCRIPT_OUT;



    for j in `ls $path|awk -F. '{print $1}'|grep -v 'BDWLT'`
    do
    echo $j
    db2 "load from /dev/null of del terminate into SDPDWBH.${j} nonrecoverable";
    #db2 list tables|grep -i ${j}>> $SCRIPT_OUT;
    echo "The table $j is load replaced" ;
    db2 "load client from ${path}${j}.TXT of del MODIFIED BY NOCHARDEL COLDEL| savecount 20000 replace into SDPDWBH.${j} nonrecoverable" >> $SCRIPT_OUT;
    db2 "load from /dev/null of del terminate into SDPDWBH.${j} nonrecoverable";
    done

    for j in `ls $path|awk -F. '{print $1}'|grep -w 'BDWLT'`
    do
    db2 "load from /dev/null of del terminate into SDPDWBH.${j} nonrecoverable";
    echo "The table $j is load inserted" ;
    db2 "load client from ${path}${j}.TXT of del MODIFIED BY NOCHARDEL COLDEL| savecount 20000 insert into SDPDWBH.${j} nonrecoverable" >> $SCRIPT_OUT;
    db2 "load from /dev/null of del terminate into SDPDWBH.${j} nonrecoverable";
    done
    echo "Starting Checkpending state check......."
    db2 connect to DPPDWHK0;

    stitycount=`db2 -x "select 'db2 set integrity for SDPDWBH.'||lower(Tabname)||' immediate checked;' from syscat.tables where status='C' and tabschema='SDPDWBH' "|wc -l`

    if [ $stitycount -ne 0 ]; then

    db2 connect to DPPDWHK0;
    echo "Doing set integrity......"

    for setity in `db2 -x "select 'set integrity for SDPDWBH.'||lower(Tabname)||' immediate checked;' from syscat.tables where status='C' and tabschema='SDPDWBH' "`
    do
    db2 ${setity} >> $SCRIPT_OUT;
    done
    else
    echo "No table is in check pending state..."
    fi


    db2 "select CURRENT TIMESTAMP from sysibm.sysdummy1" >> $SCRIPT_OUT;

    cat $SCRIPT_OUT|grep -ie "Number of rows read" -e "Number of rows loaded" -e "Number of rows committed" -e "Number of rows rejected" -e ".TXT"

  6. #6
    Join Date
    Feb 2008
    Posts
    40
    By putting tablespace in non-recoverable mode, I meant load in non-recoverable mode.

  7. #7
    Join Date
    Feb 2008
    Posts
    40
    Please reply

Posting Permissions

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