Tablespace in Load pending state

grofaty · #1 (**permalink**) 07-08-04, 08:38

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

dsusendran · #2 (**permalink**) 07-08-04, 10:12

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

Newbie

J Petruk · #3 (**permalink**) 07-08-04, 10:45

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.

grofaty · #4 (**permalink**) 07-09-04, 01:57

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

ARWinner · #5 (**permalink**) 07-09-04, 08:10

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

dsusendran · #6 (**permalink**) 07-09-04, 10:12

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

grofaty · #7 (**permalink**) 07-12-04, 01:35

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

grofaty · #8 (**permalink**) 07-13-04, 02:57

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

dsusendran · #9 (**permalink**) 07-13-04, 10:13

See:

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

Quote:

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

sathyaram_s · #10 (**permalink**) 07-13-04, 16:57

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

grofaty · #11 (**permalink**) 07-15-04, 07:38

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

bmujeeb · #12 (**permalink**) 07-15-04, 10:33

There is change object id , go to syscat.tables and use tabname,tableid

regards,

mujeeb

grofaty · #13 (**permalink**) 07-16-04, 02:40

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

Internet Services

Web Development

Digital Marketing

Consumer Tech