Hi There,

After deleting almost same rows from the target table ADMIN.INVENTORY, the following LOAD command is used to load data into the table

db2 => load from DailyRL.del of IXF INSERT into ADMIN.INVENTORY

When error "SQL0289N Unable to allocate new pages in table space "USERSPACE1". SQLSTATE=57011" was shown, I waited for a weekend and ran the following command and got the same error. So I extend 500 pages to all three container files and restart the following command and get the same error. What's the problem with this load command?

Thanks,

db2 => load from DailyRL.del of IXF RESTART into ADMIN.INVENTORY
SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL3109N The utility is beginning to load data from file "/db2data1/test/DailyRL.del".

SQL3500W The utility is beginning the "LOAD" phase at time "10-04-2004 15:43:07.205150".

SQL3150N The H record in the PC/IXF file has product "DB2 02.00", date "20040927", and time "160520".

SQL3153N The T record in the PC/IXF file has name "DailyRL.del", qualifier "", and source " ".

SQL3519W Begin Load Consistency Point. Input record count = "0".

SQL3520W Load Consistency Point was successful.

SQL0289N Unable to allocate new pages in table space "USERSPACE1". SQLSTATE=57011

SQL0289N Unable to allocate new pages in table space "USERSPACE1". SQLSTATE=57011

db2 =>

======================
Tablespace Info:

Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = Any data
State = 0x0008
Detailed explanation:
Load pending
Total pages = 769500
Useable pages = 769488
Used pages = 0
Free pages = 0
High water mark (pages) = 0
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 48
Number of containers = 3
State change tablespace ID = 2
State change object ID = 11

==============
its db2diag.log is as the following:

2004-09-30-17.05.48.979092 Instance:test Node:000
PID:2752586(db2agent (DW)) Appid:*LOCAL.test.040930210350
data_management sqldEscalateLocks Probe:2 Database:LOTDW

-- Lock Count, Target : 1147, 573
-- Table (ID) Name : (2;11) ADMIN.INVENTORY
-- Locks, Request Type : 1144, X
-- Result (0 = success): 0


2004-09-30-17.12.23.934780 Instance:test Node:000
PID:2752586(db2agent (LOTDW)) Appid:*LOCAL.test.040930210350
data_management sqldEscalateLocks Probe:2 Database:LOTDW

-- Lock Count, Target : 1148, 574
-- Table (ID) Name : (2;11) ADMIN.INVENTORY
-- Locks, Request Type : 1145, X
-- Result (0 = success): 0


2004-09-30-17.16.19.045928 Instance:test Node:000
PID:2752586(db2agent (LOTDW)) Appid:*LOCAL.test.040930210350
data_management sqldEscalateLocks Probe:2 Database:LOTDW

-- Lock Count, Target : 1148, 574
-- Table (ID) Name : (2;11) ADMIN.INVENTORY
-- Locks, Request Type : 1145, X
-- Result (0 = success): 0


2004-09-30-17.31.34.216059 Instance:test Node:000
PID:2023506(db2lrid) Appid:*LOCAL.test.040930210350
database_utilities DIAG_NOTE Probe:0 Database:LOTDW

Load CPU parallelism is: 2, 0

2004-09-30-17.31.34.322392 Instance:test Node:000
PID:2662600(db2lfrm0) Appid:*LOCAL.test.040930210350
database_utilities sqlulPrintPhaseMsg Probe:0 Database:LOTDW


Starting LOAD phase at 09-30-2004 17:31:34.307192. Table ADMIN.INVENTORY

2004-09-30-17.32.11.831447 Instance:test Node:000
PID:2023506(db2lrid) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbAllocateExtent Probe:830

Tablespace 2(USERSPACE1) is full

2004-09-30-17.32.11.847804 Instance:test Node:000
PID:2023506(db2lrid) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbObtainDataExtent Probe:800
DIA9999E An internal error occurred. Report the following error code :
"0xFFFFD121".

2004-09-30-17.32.11.964401 Instance:test Node:000
PID:2023506(db2lrid) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbObtainDataExtent Probe:800

Obj={pool:2;obj:11;type:0} State=x27 Parent={2;11}, EM=272, PP0=288

Data TitleQLB_OBJECT_DESC PID:2023506 Node:000
0002 000b 0002 000b 0000 0000 0004 0bc6 ...............Æ
8bcc 0000 0000 0000 0000 0110 0000 0000 .Ì..............
0000 0120 0000 0002 0000 0027 0000 0000 ... .......'....
0000 1000 0000 0010 0000 0001 000b 0002 ................
400a 3748 4011 8030 @.7H@..0


2004-09-30-17.32.11.977440 Instance:test Node:000
PID:2023506(db2lrid) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbDMScheckObjAlloc Probe:830
DIA9999E An internal error occurred. Report the following error code :
"0xFFFFD121".

2004-09-30-17.32.11.978524 Instance:test Node:000
PID:2023506(db2lrid) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbDMScheckObjAlloc Probe:830

Obj={pool:2;obj:11;type:0} State=x27 Parent={2;11}, EM=272, PP0=288 Page=731986

Data TitleQLB_OBJECT_DESC PID:2023506 Node:000
0002 000b 0002 000b 0000 0000 0004 0bc6 ...............Æ
8bcc 0000 0000 0000 0000 0110 0000 0000 .Ì..............
0000 0120 0000 0002 0000 0027 0000 0000 ... .......'....
0000 1000 0000 0010 0000 0001 000b 0002 ................
400a 3748 4011 8030 @.7H@..0


2004-09-30-17.32.11.989933 Instance:test Node:000
PID:2023506(db2lrid) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbDMSCheckAllocation Probe:810
DIA9999E An internal error occurred. Report the following error code :
"0xFFFFD121".

2004-09-30-17.32.11.990999 Instance:test Node:000
PID:2023506(db2lrid) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbDMSCheckAllocation Probe:810

Obj={pool:2;obj:11;type:0} State=x27 Parent={2;11}, EM=272, PP0=288 Page=275952

Data TitleQLB_OBJECT_DESC PID:2023506 Node:000
0002 000b 0002 000b 0000 0000 0004 0bc6 ...............Æ
8bcc 0000 0000 0000 0000 0110 0000 0000 .Ì..............
0000 0120 0000 0002 0000 0027 0000 0000 ... .......'....
0000 1000 0000 0010 0000 0001 000b 0002 ................
400a 3748 4011 8030 @.7H@..0


2004-09-30-17.32.12.002901 Instance:test Node:000
PID:2023506(db2lrid) Appid:*LOCAL.test.040930210350
database_utilities DIAG_ERROR Probe:0

Error!!! -11999, 0, Detected in file: sqlulpcr.C, line 628

2004-09-30-17.32.12.011244 Instance:test Node:000
PID:2023506(db2lrid) Appid:*LOCAL.test.040930210350
database_utilities DIAG_ERROR Probe:0

==============

2004-10-04-14.56.40.932562 Instance:test Node:000
PID:2752586(db2agent (LOTDW)) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbIsAllowed Probe:1075 Database:LOTDW

Tablespace 2 : state= 8, action= 0.

2004-10-04-14.56.40.990558 Instance:test Node:000
PID:2752586(db2agent (LOTDW)) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbIsAllowed Probe:1075 Database:LOTDW

Tablespace 3 : state= 8, action= 0.

2004-10-04-14.57.02.640412 Instance:test Node:000
PID:2752586(db2agent (LOTDW)) Appid:*LOCAL.test.040930210350
database_utilities DIAG_ERROR Probe:0 Database:LOTDW

Error!!! LOAD INSERT not allowed in a tablespace of LP/DP 0, 1, Detected in file: sqluvtl2.C, line 825

2004-10-04-14.57.02.663045 Instance:test Node:000
PID:2752586(db2agent (LOTDW)) Appid:*LOCAL.test.040930210350
database_utilities sqluRegisterLoadStart Probe:50 Database:LOTDW

Load Error: LOAD/DELETE pending!

2004-10-04-14.57.02.672486 Instance:test Node:000
PID:2752586(db2agent (LOTDW)) Appid:*LOCAL.test.040930210350
database_utilities sqlu_register_table_load Probe:150 Database:LOTDW

2004-10-04-14.57.02.764865 Instance:test Node:000
PID:2752586(db2agent (LOTDW)) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbTestSetDatapoolState Probe:574 Database:LOTDW
DIA9999E An internal error occurred. Report the following error code :
"0xFFFFFFFF".

2004-10-04-14.57.02.890173 Instance:test Node:000
PID:2752586(db2agent (LOTDW)) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbTestSetDatapoolState Probe:574 Database:LOTDW

oldState = 7, newState = 0, currentState = 8

2004-10-04-15.20.46.939393 Instance:test Node:000
PID:1962160(db2lfrm0) Appid:*LOCAL.test.040930210350
database_utilities sqlulPrintPhaseMsg Probe:0 Database:LOTDW


Starting LOAD phase at 10-04-2004 15:20:46.937520. Table ADMIN.INVENTORY

2004-10-04-15.21.39.797838 Instance:test Node:000
PID:1962160(db2lfrm0) Appid:*LOCAL.test.040930210350
buffer_pool_services sqlbAllocateExtent Probe:830

Tablespace 2(USERSPACE1) is full

After running "load from DailyRL.del of IXF TERMINATE into ADMIN.INVENTORY" and "list tablespaces show detail", we can see there are still some spaces in both tablespaces as the following, so why is "SQL0289N Unable to allocate new pages in table space "USERSPACE1".
SQLSTATE=57011"?

Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 769500
Useable pages = 769488
Used pages = 690864
Free pages = 78624
High water mark (pages) = 769488
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 48
Number of containers = 3

Tablespace ID = 3
Name = INDEXSP
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 256000
Useable pages = 255984
Used pages = 227872
Free pages = 28112
High water mark (pages) = 227872
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 16
Number of containers = 1

userspace1 says a load is pending; use the first load command with the TERMINATE instead of INSERT; then get the tablespace status and the number of free pages; once you make sure userspace1 is ok you can resume the load

mota, thank you, but please see the post before yours. I terminate the load command and run "list tablespaces show detail". From there, you can see there still have some spaces in the two tablespaces.

Thanks,

Same result from Control Center. What's wrong with the DB2?

Thanks,

Use the IMPORT command like below, it runs well:

IMPORT from DailyRL.del of IXF commitcount 1000 INSERT into ADMIN.INVENTORY

The result of this command is as the following:

Number of rows read = 7988717
Number of rows skipped = 0
Number of rows inserted = 7988717
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 7988717

And the tablespaces after IMPORT is below:

Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 769500
Useable pages = 769488
Used pages = 690864
Free pages = 78624
High water mark (pages) = 769488
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 48
Number of containers = 3

Tablespace ID = 3
Name = INDEXSP
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 256000
Useable pages = 255984
Used pages = 236560
Free pages = 19424
High water mark (pages) = 236560
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 16
Number of containers = 1

You can see every thing is in good condition. But What's the matter with the DB2 LOAD command above?

Thanks,

My guess is LOAD, creates db pages(extents) of data in memory and slaps it on(append) {that's the way fastpath load works in oracle}. Its possible you did not have enough free space at the end of tablespace. If after deleting the records, you could reorganize the table, you would compact the data freeing up space at the end of table. Then LOAD should work. Import is like a SQL insert; should run lot slower than LOAD

This is what I am seeing


Total pages = 769500
High water mark (pages) = 769488

Load dumps the pages after the high water mark.

Delete don't decrease the HGM

regards & thanks

Mujeeb

What does the "high water mark" mean? What's the relationship between LOAD command and the "high water mark"? How to decrease "High Water Mark" to the Used Pages?

When REORG TABLE command is executed on the table, the same error as LOAD command is occured.

Thanks,

How to decrease "High Water Mark" to the Used Pages?

When REORG TABLE command is executed on the table, the same error as LOAD command is occured.

Thanks again,

This is a DMS tablespace , add containers to that tablespace.The
dbm automatically rebalance itself in the background.
The other option is the db2dart command, but its a advanced level,
should be used as last option.

regards & thanks

Mujeeb