DB2: when tablespace run out of pages - container space full
the TAB8K tablespace is defined (by default) as a regular tablespace instead of a large tablespace. Regular tablespaces have an architectural limit of 16,777,216 pages, and you hit that limit in WCSTG1.
The STAGLOG_ARCHIVE table appears to be the reason that we hit the 16.7M page limit, as STAGLOG_ARCHIVE is using over 6.7 million pages of the tablespace (not including indexes). This table is not part of the standard WCS schema.
We resolved this issue by issuing
ALTER TABLESPACE TAB8K CONVERT TO LARGE
. This increased the tablespace's page limit to 4,294,967,296 pages.Technically, when you convert a tablespace to large you are also supposed to reorg all indexes for the tables that reside in the tablespace. However, given the size of all of the standard WCS tables, this is not strictly necessary.
However, I would absolutely recommend that you move the STAGLOG_ARCHIVE table to a new large tablespace. This can be done online with the ADMIN_MOVE_TABLE utility.
$ db2 "alter tablespace tab8k convert to large"
SQL1237W Table space "TAB8K" is being converted from REGULAR to LARGE.
Indexes on tables in this table space must be reorganized or rebuilt to
support large RIDs. SQLSTATE=01686
To see if the tablespace is large or regular
[stg1ins1@db2stg601 ~]
$ db2pd -db wcstg1 -tablespace | more
Database Member 0 -- Database WCSTG1 -- Active -- Up 41 days 06:03:07 -- Date 2024-11-28-12.53.21.843434
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007FB675D3E540 0 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE
0x00007FB675D4BA00 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007FB675D8EF00 2 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1
0x00007FB675D9C3C0 3 DMS Large 8192 32 Yes 32 2 2 Def 1 0 31 Yes TAB8K
0x00007FB675DA9880 4 DMS Regular 16384 32 Yes 32 3 3 Def 1 0 31 Yes TAB16K
0x00007FB675D58EC0 5 SMS SysTmp 8192 32 Yes 32 2 2 On 1 0 31 No TEMPSYS8K
0x00007FB675D66380 6 SMS SysTmp 16384 32 Yes 32 3 3 On 1 0 31 No TEMPSYS16K
0x00007FB675D73840 7 SMS SysTmp 32768 32 Yes 32 4 4 On 1 0 31 No TEMPSYS32K
0x00007FBACDCF0080 8 DMS Large 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSTOOLSPACE
The troubleshooting I did:
db2 "ALTER TABLESPACE tab8k LOWER HIGH WATER MARK"
db2 "ALTER TABLESPACE tab8k INCREASESIZE 512 K MAXSIZE NONE"
This is what showed up in the db2diag:-
FUNCTION: DB2 UDB, buffer pool services, sqlbObtainDataExtent, probe:800
MESSAGE : ZRC=0x85020021=-2063466463=SQLB_END_OF_CONTAINER
"Container space full"
DATA #1 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 104 bytes
Obj: {pool:3;obj:11;type:0} Parent={3;11}
lifeLSN: 0000002118273C92
tid: 0 0 0
extentAnchor: 544
initEmpPages: 0
poolPage0: 0
poolflags: 0x 3102
objectState: 0x 80027
lastSMP: 0
pageSize: 8192
extentSize: 32
bufferPoolID: 2
partialHash: 720899
objDescAttributes: 0
objDescEHLState: 0x00007fbad6b2b178
bufferPool: 0x00007fb1b42d0860
pdef: 0x00007fb675d9c3c0
2024-11-28-11.10.39.287152-360 I137718092E1508 LEVEL: Error
PID : 3189649 TID : 140396382381824 PROC : db2sysc 0
INSTANCE: stg1ins1 NODE : 000 DB : WCSTG1
APPHDL : 0-52238 APPID: 172.31.65.15.51032.241128171020
UOWID : 3 ACTID: 9
AUTHID : UC4GCPDB2STGUSER HOSTNAME: db2stg601
EDUID : 2114 EDUNAME: db2agent (WCSTG1) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbExtendObject, probe:673
MESSAGE : ZRC=0x85020021=-2063466463=SQLB_END_OF_CONTAINER
"Container space full"
DATA #1 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 104 bytes
Obj: {pool:3;obj:11;type:0} Parent={3;11}
lifeLSN: 0000002118273C92
tid: 0 0 0
extentAnchor: 544
initEmpPages: 0
poolPage0: 0
poolflags: 0x 3102
objectState: 0x 80027
lastSMP: 0
pageSize: 8192
extentSize: 32
bufferPoolID: 2
partialHash: 720899
objDescAttributes: 0
objDescEHLState: 0x00007fbad6b2b178
bufferPool: 0x00007fb1b42d0860
pdef: 0x00007fb675d9c3c0
DATA #2 : unsigned integer, 4 bytes
67872
DATA #3 : unsigned integer, 4 bytes
1
DATA #4 : Hex integer, 4 bytes
0x00000220
DATA #5 : Pointer, 8 bytes
0x00007fbd42f01e40
DATA #6 : Pointer, 8 bytes
0x00007fb0947fb7b0
2024-11-28-11.10.39.614200-360 E137719601E749 LEVEL: Warning
PID : 3189649 TID : 140396382381824 PROC : db2sysc 0
INSTANCE: stg1ins1 NODE : 000 DB : WCSTG1
APPHDL : 0-52238 APPID: 172.31.65.15.51032.241128171020
UOWID : 3 ACTID: 9
AUTHID : UC4GCPDB2STGUSER HOSTNAME: db2stg601
EDUID : 2114 EDUNAME: db2agent (WCSTG1) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbPrepareToGrowLastRange, probe:160
MESSAGE : ADM6099W Table space "TAB8K" (ID "3") has reached the maxiumum size
("137438953472" bytes) for a tablespace of this type and page size.
To add more storage to the database, add a new table space.
2024-11-28-11.10.39.614537-360 E137720351E1201 LEVEL: Error
PID : 3189649 TID : 140396382381824 PROC : db2sysc 0
INSTANCE: stg1ins1 NODE : 000 DB : WCSTG1
APPHDL : 0-52238 APPID: 172.31.65.15.51032.241128171020
UOWID : 3 ACTID: 9
AUTHID : UC4GCPDB2STGUSER HOSTNAME: db2stg601
EDUID : 2114 EDUNAME: db2agent (WCSTG1) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbAllocateExtent, probe:982
MESSAGE : ADM6044E The DMS table space "TAB8K" (ID "3") is full. If this is
an autoresize or automatic storage DMS tablespace, the maximum table
space size may have been reached or the existing containers or
storage paths cannot grow any more. Additional space can be added to
the table space by either adding new containers or extending existing
ones using the ALTER TABLESPACE SQL statement. If this is an
autoresize or automatic storage DMS table space, additional space can
be added by adding containers to an autoresize table space or by
adding new storage paths to the storage group it is using.
[stg1ins1@db2stg601 ~]
$ db2pd -db wcstg1 -tab 3
Database Member 0 -- Database WCSTG1 -- Active -- Up 41 days 05:14:32 -- Date 2024-11-28-12.04.46.637909
Tablespace 3 Configuration:
Address Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007FB675D9C3C0 DMS Regular 8192 32 Yes 32 2 2 Def 1 0 31 Yes TAB8K
Tablespace 3 Statistics:
Address TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState
0x00007FB675D9C3C0 16777216 16777184 16777184 0 0 16777184 16777184 0x00000000 1698762169 0 No n/a
Tablespace 3 Autoresize Statistics:
Address AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x00007FB675D9C3C0 Yes Yes 33554432 -1 No None 11/28/2024 06:07:54.180964 Yes
Tablespace 3 Storage Statistics:
Address DataTag Rebalance SGID SourceSGID
0x00007FB675D9C3C0 -1 No 0 -
Containers:
Address ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
0x00007FB675D22120 0 File 16777216 16777184 0 0 /rd100/db2data/stg1ins1/NODE0000/WCSTG1/T0000003/C0000000.USR
[stg1ins1@db2stg601 ~]
$
[stg1ins1@db2stg601 ~]
$ df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 32G 0 32G 0% /dev
tmpfs 32G 52K 32G 1% /dev/shm
tmpfs 32G 568K 32G 1% /run
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/sda2 110G 24G 87G 22% /
/dev/sda1 200M 5.8M 195M 3% /boot/efi
/dev/sdc 99G 17G 82G 18% /rd300
/dev/sde 99G 670M 98G 1% /rd600
/dev/sdb 296G 138G 158G 47% /rd100
/dev/sdf 197G 3.9G 193G 2% /usr/opt/app/db2
/dev/sdd 99G 48G 52G 48% /rd500
/dev/sdg 591G 151G 441G 26% /usr/opt/app/dbdump
tmpfs 6.3G 0 6.3G 0% /run/user/819277508
tmpfs 6.3G 0 6.3G 0% /run/user/1007
ALTER TABLESPACE tab8k ADD (FILE 'cont2' 2000, FILE 'cont3' 2000)
ADD (FILE 'cont4' 2000)
EXTEND (FILE 'cont0' 100)
RESIZE (FILE 'cont1' 3000)
Tablespace ID = 3
Name = TAB8K
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 16777216
Useable pages = 16777184
Used pages = 16777184
Free pages = 0
High water mark (pages) = 16777184
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Minimum recovery time = 2023-10-31-14.22.49.000000
6149643 6752845 WCS.STAGLOG_ARCHIVE
fpage, no of pages with data in them
npage, assigned to table
suggestions:
move staglog_archive table to it's own tablespace
reorg

Comments
Post a Comment