Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    61

    Question Unanswered: What's the problem with LOAD utility - Unable to allocate new pages in table space "U

    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

  2. #2
    Join Date
    Mar 2004
    Posts
    61
    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

  3. #3
    Join Date
    Sep 2003
    Posts
    237
    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

  4. #4
    Join Date
    Mar 2004
    Posts
    61
    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,

  5. #5
    Join Date
    Mar 2004
    Posts
    61
    Same result from Control Center. What's wrong with the DB2?

    Thanks,
    Last edited by hiolgc; 10-05-04 at 18:15.

  6. #6
    Join Date
    Mar 2004
    Posts
    61

    Unhappy

    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,

  7. #7
    Join Date
    Sep 2003
    Posts
    237
    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
    mota

  8. #8
    Join Date
    Mar 2004
    Posts
    448
    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

  9. #9
    Join Date
    Mar 2004
    Posts
    61
    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,
    Last edited by hiolgc; 10-06-04 at 17:38.

  10. #10
    Join Date
    Mar 2004
    Posts
    61
    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,

  11. #11
    Join Date
    Mar 2004
    Posts
    448
    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

Posting Permissions

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