Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2010
    Posts
    8

    Unhappy Unanswered: Parallel Online Database Backups from Same Instance

    A project I am on uses EMC Legato to backup DB2 database using the appropriate agents. It frequently complains when it kicks off two parallel backups from the same instance when one of the backups needs a lock on a shared tablespace which more often than not is "syscatspace". Escalatation to IBM didn't get anywhere and we are finding it hard to schedule the work off-peak.

    Has anyone out there experienced this or anything similar?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What is the point of two parallel backups?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2010
    Posts
    8
    Just to make myself clear, we are talking about the concurrent backup of two or more databases in the same instance. One point is to save time. Another point is to not have to bother about backup scheduling. Legato and other backup managers can do things in parallel.

  4. #4
    Join Date
    Jan 2010
    Posts
    335
    Sounds more like an locking issue from the Application.

  5. #5
    Join Date
    Jan 2010
    Posts
    8
    It is a locking error - in DB2. Had it been the Legato software, we would have seen the error in Legato's logs but the error is reported in db2diag.log. The tablespace has been marked as BACKUP_IN_PROGRESS and that makes it exclusive from a backup perspective.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Sorry, but your explanation does not make much sense. No tablespace is ever shared between databases, so backups of two separate databases can run simultaneously without affecting each other. Each tablespace is placed into "backup in progress" state as it is being backed up, which is not an error.

    I think you need to make sure that multiple Legato agents do not attempt to back up the same database at the same time. If you see any particular error in db2diag.log you should probably post it here.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    [removed duplicate post]
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jan 2010
    Posts
    8

    Parallel Online Database Backups from Same Instance

    If absolutely no tablespace is shared, then where does syscatspace sit? Does every database have a syscatspace? You can assume that no more than one backup of a database is set off at any given time.

    As requested, a snippet from db2diag.log

    2010-01-28-03.00.08.553634+060 E9180A740 LEVEL: Error
    PID : 1998902 TID : 39529 PROC : db2sysc 0
    INSTANCE: ptlinst1 NODE : 000 DB : RELDB61
    APPHDL : 0-12405 APPID: 10.58.45.97.38010.100128020007
    AUTHID : DB2INST1
    EDUID : 39529 EDUNAME: db2agent (RELDB61) 0
    FUNCTION: DB2 UDB, database utilities, sqlubBuildAppTbsp, probe:1653
    MESSAGE : SQL2048N An error occurred while accessing object "". Reason code:
    "".
    DATA #1 : String, 166 bytes
    The BACKUP_IN_PROGRESS state is already on for this tablespace.
    This tablespace is already in the midst of being backed up and
    cannot have a concurrent backup on it.
    DATA #2 : String, 11 bytes
    SYSCATSPACE

  9. #9
    Join Date
    Jan 2010
    Posts
    335
    Each Database has a SYSCATSPACE. It's the System Catalog of the database, where the System-tables reside. The ID of the tablespace is always 0.

    db2 list tablespaces show detail

    db2 list tablespace containers for <ID> (0 for syscatspace)

    Does DB2 perform Auto-Backups?
    Is AUTO_DB_BACKUP set to on?
    (db2 get db cfg for RELDB61 | grep AUTO_DB_BACKUP)

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by indri_cilia View Post
    DATA #1 : String, 166 bytes
    The BACKUP_IN_PROGRESS state is already on for this tablespace.
    This tablespace is already in the midst of being backed up and
    cannot have a concurrent backup on it.
    DATA #2 : String, 11 bytes
    SYSCATSPACE
    I think this tells you all you need to know.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by indri_cilia View Post
    You can assume that no more than one backup of a database is set off at any given time.
    The error in db2diag.log that you posted obviously contradicts this statement.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Jan 2010
    Posts
    8
    Nick, thanks for setting me right on the syscatspace issue. Wrong assumption on my part and, as you said, all indications are that we've got two or more online backups of the same database occurring at the same time.

    I have been thrown off by the fact that IBM say I cannot do a parallel backup of six databases in the one instances. Quote

    But the real problem here is that you are trying to execute online backups for all 6 databases at same time, and this is not compatible.

    Unquote.

    I had a PMR out on this so it should have been an official response. We're now saying that response is false. My question went out to the forum more as a feeler to see if anybody else out there might find it convenient to backup multiple databases at once (no doubt it is) with a view to push for a feature request as opposed to fixing up some mistake which I may have done.

    I'll assume the latter and go over my steps again.

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Not sure what they meant by "not compatible", it's difficult to tell outside the context. Technically speaking, there is nothing that prevents you from backing up multiple databases simultaneously. However, there's something to keep in mind:
    - all utilities, including backup, share the same memory pool, which should have sufficient size to allow simultaneous backups;
    - if the vendor library is used to access backup media (which I suspect may be the case with Legato), make sure it supports simultaneous operations, especially if the backup images go to different media and/or storage classes;
    ---
    "It does not work" is not a valid problem statement.

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Could it be that your db is cataloged as some alias name and your backup job is getting the db name from "list db directory" and trying to backup the same database in parallel.

    Here is an example of me reproducing this error:

    bkogan@panipuri /home/bkogan > db2 catalog db sample as sample2
    DB20000I The CATALOG DATABASE command completed successfully.
    DB21056W Directory changes may not be effective until the directory cache is
    refreshed.

    bkogan@panipuri /home/bkogan > db2 terminate
    DB20000I The TERMINATE command completed successfully.


    From one session:

    bkogan@panipuri /home/bkogan > db2 backup db sample online
    Backup successful. The timestamp for this backup image is : 20100129163547


    From another session:
    bkogan@panipuri /home/bkogan > db2 backup db sample2 online
    SQL2048N An error occurred while accessing object "0". Reason code: "6".



    2010-01-29-16.35.46.025030-300 E9772A731 LEVEL: Error
    PID : 884820 TID : 3667 PROC : db2sysc
    INSTANCE: bkogan NODE : 000 DB : SAMPLE
    APPHDL : 0-660 APPID: *LOCAL.bkogan.100129213539
    AUTHID : BKOGAN
    EDUID : 3667 EDUNAME: db2agent (SAMPLE2)
    FUNCTION: DB2 UDB, database utilities, sqlubBuildAppTbsp, probe:1653
    MESSAGE : SQL2048N An error occurred while accessing object "". Reason code:
    "".
    DATA #1 : String, 166 bytes
    The BACKUP_IN_PROGRESS state is already on for this tablespace.
    This tablespace is already in the midst of being backed up and
    cannot have a concurrent backup on it.
    DATA #2 : String, 11 bytes
    SYSCATSPACE

  15. #15
    Join Date
    Jan 2010
    Posts
    8
    Hi db2girl. You hit the nail on the head. We've got the same databases on two instances and aliases for the db2inst1 user in both environments were pointing to one. Nothing to do with shared tablespaces in the one instance. Just a dud setup. Thanks you Nick, Cougar for your input.

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
  •