Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: alter tablespace with reduce option

    DB2 v8

    Tablespace ID = 6
    Name = DMSTS
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 2000
    Useable pages = 1984
    Used pages = 24
    Free pages = 1960
    High water mark (pages) = 24
    Page size (bytes) = 4096
    Extent size (pages) = 8
    Prefetch size (pages) = 16
    Number of containers = 2
    Minimum recovery time = 2009-04-25-00.59.32.000000


    db2 "alter tablespace dmsts reduce (all containers 980)"
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL20170N There is not enough space in the table space "DMSTS" for the
    specified action. SQLSTATE=57059


    Can someone explain why I can't reduce it by 980.

  2. #2
    Join Date
    Feb 2009
    Posts
    114
    List both containers - each should have enough unused space to be reduced by 980. Even if so, it may sometimes not work. I have had better luck doing it on each of the containers individually rather than "all containers".

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Tablespace Containers for Tablespace 6

    Container ID = 0
    Name = /home/test/test/NODE0000/SQL00001/dmsts1
    Type = File
    Total pages = 1000
    Useable pages = 992
    Accessible = Yes
    Container ID = 1
    Name = /home/test/test/NODE0000/SQL00001/dmsts2
    Type = File
    Total pages = 1000
    Useable pages = 992
    Accessible = Yes

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    any other reason why it didn't work?

    Here is another example:

    Tablespace ID = 6
    Name = DMSTS
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 3000
    Useable pages = 2976
    Used pages = 24
    Free pages = 2952
    High water mark (pages) = 24
    Page size (bytes) = 4096
    Extent size (pages) = 8
    Prefetch size (pages) = 24
    Number of containers = 3
    Minimum recovery time = 2009-04-25-20.34.19.000000


    db2 "alter tablespace dmsts reduce (all containers 984)"
    DB20000I The SQL command completed successfully.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    let me clarify my question - why doesn't each container have enough unused space to be reduced by 980?

  6. #6
    Join Date
    Feb 2009
    Posts
    114
    This has never really worked right. Even with all the latest "enhancements". Estimate how many "free" pages each container should have and start with 50% of that on alter reduce. It may still not work however.

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    But there is a reason why each container doesn't have enough free space to be reduced by 980 pages...

  8. #8
    Join Date
    Feb 2009
    Posts
    114
    What I am saying is that each container may very well have plenty of unused space, yet you may not be able to release even half of it (or any of it) because "alter reduce/resize" code has never worked right and still does not

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Sorry db2dummy, the alter reduce code is correct in this case. The error message is valid.

    You're partially correct... one of the containers doesn't have enough free space to be reduced by 980 pages (it's a hint). In my second example with 3 containers, each container has 984 free pages and therefore it completed successfully.

  10. #10
    Join Date
    Feb 2009
    Posts
    114
    That is precisely why I suggested that you do each container separately instead of "all containers"

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    But what is the reason behind it? I was able to use a single command in my second example.

    Here is one way to resolve the error:

    db2 "alter tablespace dmsts reduce (all containers 976)"
    DB20000I The SQL command completed successfully.

    db2 "alter tablespace dmsts reduce (file 'dmsts1' 8)"
    DB20000I The SQL command completed successfully.

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Space is allocated/deallocated by extents.

    3 extents are in use
    245 extents are free (122 free extents in each container + 1 extent)

    122 * 8 = 976. This is why reducing both containers by 980 didn't work.

Posting Permissions

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