Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Tablespace in Load pending state

    Hi,

    I have loaded the table from ixf format into database and the regula tablespace USERSPACE1 state has become in "Load pending state". How to "free" the tablespace to normal state?

    Grofaty

  2. #2
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Lightbulb

    You might want to reissue the Load command again. If it does not work use Load with TERMINATE option (with care).

    Newbie

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by dsusendran
    You might want to reissue the Load command again. If it does not work use Load with TERMINATE option (with care).

    Newbie
    Very important - check the documentation for your version, the effects of this command have changed! In v5 it left you in restore pending, in v6 it left you with an empty table... I think in v8 it's finally ok, as long as you're not doing a REPLACE.
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    This is my testing environment, so no mather if I lose data in this table. I would like just to access other tables in this tablespace.

    I have executed command:
    C:\aaa>db2 load from c:\aaa\datafile.ixf of ixf terminate into db2admin.users

    Error
    SQL3508N Error in accessing a file or path of type "RESTART/TERMINATE INFO"
    during load or load query. Reason code: "1". Path: "".

    What to do? Is load command correct?

    My system:
    db2 v7.2 fp5 on Windows XP sp 1

    Thanks,
    Grofaty

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Grofaty,

    I just received an email about this topic this morning. Check out this web site, it might help.

    http://www-106.ibm.com/developerwork...nyk/index.html

    Andy

  6. #6
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Lightbulb Syntax

    See:

    http://www.ispirer.com/doc/sqlways36/db2/db2_load.html

    From above link,

    LOAD [CLIENT] FROM filename | pipename | device OF filetype
    LOBS FROM lobpath MODIFIED BY filetype_mod METHOD load_method
    [SAVECOUNT n] [ROWCOUNT n] [WARNINGCOUNT n] [MESSAGES message_file]
    INSERT | REPLACE | RESTART | TERMINATE INTO table_name [(column {, column})]


    Options:

    CLIENT - Specifies that the data to be loaded resides on a remotely connected client. If this option is not specified the data must reside on the server.

    The CLIENT option is available in DB2 7.1 or later.

    Your syntax seems to be correct. Try putting the path within quotes like

    C:\aaa>db2 load from "c:\aaa\datafile.ixf" of ixf terminate into db2admin.users

    I was able to do a test with

    LOAD FROM "test.ixf" OF IXF MESSAGES "testmsg.txt" TERMINATE INTO DBO.TBL1 COPY NO INDEXING MODE AUTOSELECT;

    HTH,
    Newbie
    Last edited by dsusendran; 07-09-04 at 11:16.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    I have tried this command:
    LOAD FROM "datafile.ixf" OF IXF MESSAGES "testmsg.txt" TERMINATE INTO db2admin.users COPY NO INDEXING MODE AUTOSELECT

    But I still got the same error:
    SQL3508N Error in accessing a file or path of type "RESTART/TERMINATE INFO"
    during load or load query. Reason code: "1". Path: "".

    Any idea why?

    Thanks,
    Grofaty
    Last edited by grofaty; 07-13-04 at 03:57.

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    One more question. Is there a way to find out which table caused "load pending" state of tablespace? How to find table name which produced this state?

    Thanks,
    Grofaty

  9. #9
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Question

    See:

    http://www-306.ibm.com/cgi-bin/db2ww...e.d2w/sec6sub5


    If any of the above methods fails to bring the table back online, it can often be a quiesce problem, or, in some cases a control file problem. You might get a helpful and informative message such as

    SQL3508N Error in accessing a file or path of type
    "RESTART/TERMINATE INFO" during load or load query.
    Reason code: "1". Path: "".
    I am not sure how to find the table which caused this state. Sorry. Anyone........

    Newbie

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    db2 list tablespaces show details

    will show the state of the tabelspace and also the quiescer ... Check the tablename in SYSCAT.TABLES using the quiescer id and tablespace id

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    Hi sathyaram_s,

    I have executed the following command:
    db2 list tablespaces show detail

    I just can't see the "the quiescer" or "quiescer id"? How to see that information?

    The result is:
    Code:
               Tablespaces for Current Database
    
     Tablespace ID                        = 0
     Name                                 = SYSCATSPACE
     Type                                 = System managed space
     Contents                             = Any data
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 1810
     Useable pages                        = 1810
     Used pages                           = 1810
     Free pages                           = Not applicable
     High water mark (pages)              = Not applicable
     Page size (bytes)                    = 4096
     Extent size (pages)                  = 32
     Prefetch size (pages)                = 16
     Number of containers                 = 1
    
     Tablespace ID                        = 1
     Name                                 = TEMPSPACE1
     Type                                 = System managed space
     Contents                             = System Temporary data
     State                                = 0x0000
       Detailed explanation:
         Normal
     Total pages                          = 1
     Useable pages                        = 1
     Used pages                           = 1
     Free pages                           = Not applicable
     High water mark (pages)              = Not applicable
     Page size (bytes)                    = 4096
     Extent size (pages)                  = 32
     Prefetch size (pages)                = 16
     Number of containers                 = 1
    
     Tablespace ID                        = 2
     Name                                 = USERSPACE1
     Type                                 = System managed space
     Contents                             = Any data
     State                                = 0x0008
       Detailed explanation:
         Load pending
     Total pages                          = 5682
     Useable pages                        = 5682
     Used pages                           = 5682
     Free pages                           = Not applicable
     High water mark (pages)              = Not applicable
     Page size (bytes)                    = 4096
     Extent size (pages)                  = 32
     Prefetch size (pages)                = 16
     Number of containers                 = 1
     State change tablespace ID           = 2
     State change object ID               = 4
    Last edited by grofaty; 07-15-04 at 08:42.

  12. #12
    Join Date
    Mar 2004
    Posts
    448
    There is change object id , go to syscat.tables and use tabname,tableid

    regards,

    mujeeb

  13. #13
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thanks to you all, it works.

    I did:
    1. db2 list tablespaces show detail
    2. from the above command result I searced the tablespace state = "Load pending"
    3. from load pending tablespace state there is "State change object ID " = 4
    4. db2 select tabschema, tabname, tableid from syscat.tables where tableid=4
    5. I got the result db2admin.work (this is the name I was looking for)
    6. I executed on Windows: db2 load from nul: of ixf terminate into db2admin.work
    7. The tablespace has been moved to normal state.

    My problem was: I was trying to "load terminate" into table that was not in load pending state!!! So loading into table that is not in load pending state produces stupid error:
    Code:
    "SQL3508N  Error in accessing a file or path of type "RESTART/TERMINATE INFO"
    during load or load query.  Reason code: "1".  Path: "".
    So figuring out which table made the load pending state of tablespace has solved my problem.

    Thanks to you all folks,
    Grofaty
    Last edited by grofaty; 07-16-04 at 03:46.

Posting Permissions

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