Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2014
    Posts
    3

    Unanswered: SQL Backup pending tablespace problem

    Hi good afternoon.
    We are facing a large problem in our DB2 DataBase,i.e. a SQL0290N error when trying to load an empty file into several tables. Please, any help will is welcome!!

    We are executing and script with a load followed by a SET INTEGRITY:

    >>db2 "load client from $PATH/EmptyData.del of del modified by coldel; replace into maestro_squema.table1";
    >>db2 SET INTEGRITY FOR maestro_squema.table1 ALLOW READ ACCESS IMMEDIATE CHECKED

    The CHECK INTEGRITY returns an error:
    "DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0290N Table space access is not allowed. SQLSTATE=55039"


    And leaves the tablespace containing the "table1" in "0x0020 Backup pending", so when trying to access the "table1" we get a error sqlCode=-668 because the table integrity is not checked.

    We have no idea why the tablespace is moving into "Backup pending" state.

    Tablespace details with LIST TABLESPACE are as follow:

    Tablespace ID = 3
    Name = TBS_DAT_4K
    Type = System managed space
    Contents = All permanent data. Regular table space.
    State = 0x0020
    Detailed explanation:
    Backup pending

    Total pages = 65362
    Useable pages = 65362
    Used pages = 65362

    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 192
    Number of containers = 1
    Minimum recovery time = 2014-01-23-12.11.35.000000


    The tablespace file system has plenty free space in disk.

    Please help!! And thanks so much for reading!!!

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    As the error says, you have to backup the entire tablespace:
    BACKUP DATABASE <dbname> USER <DBAusername> USING <password> TABLESPACE (<tablespace_name>) ONLINE TO <backup_path>
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    May 2005
    Posts
    29
    For future, you could look into using NONRECOVERABLE keyword or COPY YES option in the LOAD command that will avoid having to take a backup every time you do a LOAD. As Florin suggested a backup is required right now to access the objects
    Last edited by db2inst1; 01-24-14 at 11:24.

  4. #4
    Join Date
    Jan 2014
    Posts
    3
    Thats the key!!

    With "NONRECOVERABLE" keyword in the load command the tablespace remains in "Normal" state and the CHECK INTEGRITY can be done.

    Thanks a lot db2inst1 and Florin !!

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    You ARE aware of the consequences?? COPY YES is a safer option.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    no they are not - if they did not know about nonrecoverable they don't know about the use of COPY YES
    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

  7. #7
    Join Date
    Mar 2014
    Posts
    1
    Quote Originally Posted by aflorin27 View Post
    As the error says, you have to backup the entire tablespace:
    BACKUP DATABASE <dbname> USER <DBAusername> USING <password> TABLESPACE (<tablespace_name>) ONLINE TO <backup_path>
    FLORIN I LOVE YOU !!!

    Tnx for the help. Kind of hard to manage a db2 when you have no idea how it works or how it was installed. 2 days of google and this post

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
  •