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.

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".

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

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.

let me clarify my question - why doesn't each container have enough unused space to be reduced by 980?

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.

But there is a reason why each container doesn't have enough free space to be reduced by 980 pages...

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

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.

That is precisely why I suggested that you do each container separately instead of "all containers"

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.

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.