Results 1 to 9 of 9
  1. #1
    Join Date
    May 2012
    Posts
    3

    Red face Unanswered: tables get lock during backup in db2 v9.5

    hello every body
    i have a problem in db2 v9.5.i hope you can help

    In IBM DB2 v9.5,when I back up my database and restore it on a new database, some of my tables in restored database are locked.
    My back up command is :
    CONNECT TO MYDB;
    QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
    CONNECT RESET;
    ; BACKUP DATABASE MYDB TO "C:\" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING
    CONNECT TO MYDB;
    UNQUIESCE DATABASE;
    ; CONNECT RESET;
    This problem occurs every time I backup and restore my DB.
    thank you in advance

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    would it be possible to display the error /msg... you get when you say
    "some of my tables in restored database are locked"
    how locked ?
    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

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    The title of your post says "tables get lock during backup"
    and your text says "tables in restored database are locked"

    Do you see the confusion?

    Can you explain your problem more clearly? (use translate.google.com if English is not your first language).

  4. #4
    Join Date
    May 2012
    Posts
    3

    table lock probelm

    hello every body
    thanx for your answers.
    i want to explain my problem more clearly.when i restore my databse,i can not access some of my tables,can not select from them or insert into them
    the error msg is:
    DB2 sql error:sqlcode=-668,sqlstate=57016,sqlErrmc=7,mySchema.myTable,Dri ver=3.57.82

    thank you all

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by hamsoo View Post
    hello every body
    thanx for your answers.
    i want to explain my problem more clearly.when i restore my databse,i can not access some of my tables,can not select from them or insert into them
    the error msg is:
    DB2 sql error:sqlcode=-668,sqlstate=57016,sqlErrmc=7,mySchema.myTable,Dri ver=3.57.82

    thank you all
    Is this error for all tables or some tables in particular? The explanation for the error is:

    db2 "? SQL668"
    SQL0668N Operation not allowed for reason code "<reason-code>" on table
    "<table-name>".

    Explanation:

    Access to table "<table-name>" is restricted. The cause is based on the
    following reason codes "<reason-code>":
    [...]

    7

    The table is in the reorg pending state. This can occur after
    an ALTER TABLE statement containing a REORG-recommended
    operation.


    My guess is that you should reorg the table(s) on the database where the backup origins from as well. You can check the status regarding this with:

    select NUM_REORG_REC_ALTERS
    from SYSIBMADM.ADMINTABINFO
    where tabname = ?

    If NUM_REORG_REC_ALTERS exceeds 3 the table has to be
    reorged (well, I tried it once on 9.5 and once on 9.7 :-)

    Unfortenately reading from SYSIBMADM.ADMINTABINFO is painfully slow, I
    did a typing error on the tablename (i.e. the table did not exists), and
    the query took nearly 20 seconds. I guess it does not do a regular
    table access, but some magic woodoo under the covers.

    I usually adhere to "better safe than sorry" and do a reorg whenever I operate on a table in a way that causes this counter to increase
    --
    Lennart

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    This seems simple, just look up the meaning of SQL0668N with reason code 7.
    Probably someone did an ALTER TABLE at the time the backup was being taken.

    Find any tables that are in status 'reorg pending' then perform REORG on those tables.

    Example code to show tables are are in 'reorg pending' status.

    select TABSCHEMA, TABNAME
    from SYSIBMADM.ADMINTABINFO
    where REORG_PENDING = 'Y'


    -- for each table that results from that query above:

    db2 reorg table myschema.mytable

  7. #7
    Join Date
    May 2012
    Posts
    3

    Unhappy

    thank you all for your answers.
    dear db2mor,this problem occurs every time i backup and restore.and i'm sure that no one execute ALTER TABLE command on tables.i solve this by executing REORG command,but i want to prevent my tables from being locked.
    i wish some body can help me
    thanx

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Hamsoo, the work 'locked' has a specific meaning in DB2.
    Your tables are not locked in the correct DB2 definition of that word.
    At least one of your tables are in 'Reorg Pending' (this is not 'locked').
    In that state of reorg-pending, DB2 will prevent index-scans on the table until you perform reorg.

    Do you have AUTO_MAINT = ON on your database?
    You could (on a test environment) check if disabling auto-maint changes the symptom.


    Another thing you can do is to assert (by running a script) *before* backup that there are no tables in reorg-pending.

    Alternatively when the restore-database completes successfully , then run a script to reorg any tables that are in reorg-pending.

    But correcting the problem at source database is usually the best thing to do.

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Quote Originally Posted by hamsoo View Post
    BACKUP DATABASE MYDB TO "C:\" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING
    You take an OFFLINE db backup - there are no connections except for the backup operation. Then you restore from this offline backup to a new db - do you apply the logs after the restore?

    As db2mur said, make sure the table(s) are not in reorg pending prior to performing a backup.

Tags for this Thread

Posting Permissions

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