Step by step: How to extend tablespace for DB2 on AIX
The following steps are used to resolve full table space issue without having to
re-create your database:
- 1. Find out which file containers are used for that tablespace (fully used)
- 2. Do you have enough space on the current file systems?
- 3. Increase the size for volume group
- 4. Increase size for logical volumes
- 5. Increase size for file systems
- 6. And finally, increase DB tablespace!
As your database tables grow, your originally planned disk space may simply run out. This is seen when you get
the following error from DB2 db2diag.log (for both DB2 V8 and V9):
2007-08-29-13.02.27.267969-420 E14615A1054 LEVEL: Error
PID : 3743818 TID : 1 PROC : db2agent (PWTEST01) 0
INSTANCE: db2inst1 NODE : 000 DB : PWTEST01
APPHDL : 0-662 APPID: 10.0.4.50.54736.070829194050
AUTHID : FNRUN
FUNCTION: DB2 UDB, buffer pool services, sqlbAllocateExtent, probe:830
MESSAGE : ADM6044E The DMS table space "L002" (ID "105") 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 an automatic storage database.
|
You can alter tablespace (as recommended by the error message) to increase its size, but you may simply run
out of disk space that the tablespace resides on. Many volume managers and file systems allow you to extend disk space
as well, and that is what you have to do. The following steps apply to DB2 and AIX, but the process can work for
other databases and operating systems as well. We expect you to replace commands used when working on other platforms.
1. Find out which file containers are used for that tablespace (which is full):
Normally this is done by search for the name of the tablespace in your
database definition script. Or if you don't have that (because the application
created it during installation), you can use the db2look command to dump
that to a file. Then do a grep on the name of the tablespace:
bash-3.00# grep L002 *
dbcreate.ddl:CREATE LARGE TABLESPACE L002 IN DATABASE PARTITION
dbcreate.ddl: USING (FILE '/db/a33/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a34/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a35/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a36/pwtest01/L002' 250M)
so we found the files used.
|
2. Do you have enough space on the current file systems?
# df -k
/dev/lv33 10256384 2387712 77% 27 1% /db/a33
/dev/lv34 10256384 2387704 77% 28 1% /db/a34
/dev/lv35 10256384 2387712 77% 27 1% /db/a35
/dev/lv36 10256384 2387704 77% 28 1% /db/a36
77% used. 10GB per file system, leaving about 230MB free space per.
You can check what else is on each of the file system:
bash-3.00# ls -l /db/a33/pwtest01/
total 15654912
-rw-rw-rw- 1 db2inst1 db2admi 0 Jul 24 02:42 .SQLCRT.FLG
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L001
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L002
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L003
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L004
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L005
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L006
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U027
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U029
|
3. Increase the size for volume group
If you think you need to extend tablespace size beyond 1GB (250Mx4), then you must increase
the file system (77% used already). But first, you need to extend size for logical volume,
but before you can do that, you need to extend size for the volume group that the logical
volume resides in ( we will use lv33 as an example, you just repeat the same for lv34,35,36).
First, find out the sizes for the volume group vg33:
bash-3.00# lsvg vg33
VOLUME GROUP: vg33 VG IDENTIFIER: 00ce3a8a00004c0000000113f77641d1
VG STATE: active PP SIZE: 32 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 319 (10208 megabytes)
MAX LVs: 256 FREE PPs: 5 (160 megabytes)
LVs: 2 USED PPs: 314 (10048 megabytes)
OPEN LVs: 2 QUORUM: 2
TOTAL PVs: 1 VG DESCRIPTORS: 2
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 1 AUTO ON: yes
MAX PPs per VG: 32512
MAX PPs per PV: 1016 MAX PVs: 32
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
So vg33 is only 10GB. needs to extend that; Let's see what hdisks are in vg33:
# datapath query device
...
DEV#: 33 DEVICE NAME: vpath33 TYPE: 2107900 POLICY: Optimized
SERIAL: 75FABV1103D
===========================================================================
Path# Adapter/Hard Disk State Mode Select Errors
0 fscsi0/hdisk39 OPEN NORMAL 6735259 0
1 fscsi1/hdisk79 OPEN NORMAL 6743800 0
So if vpath33 offers 10GB space, then we need to add more vpath to the vg so
that it gets more hdisks.
Performce vg extend task in smitty (assume you run on AIX):
___________________________________________________________________________
Force the creation of a volume group? no
* VOLUME GROUP name [vg33]
* PHYSICAL VOLUME names []
PHYSICAL VOLUME names
Move cursor to desired item and press F7.
ONE OR MORE items can be selected.
Press Enter AFTER making all selections.
> vpath40
> vpath41
> vpath42
___________________________________________________________________________
This adds 3 more vpaths (here we used the same size: 10GB each vpath) to get
an additional 30GB more disk space.
|
4. Increase size for logical volumes
lv33 on vg33 now can be extended. Note: if your logical volume (lv) has a
MAX LOGICAL PARTITIONS setting, you may need to increase it so that addtional
partitions can be created on the new disks. Otherwise, you will be capped
at the old size. For example, you may need to change MAX LOGICAL PARTITIONS from
512 to 1024 (done in smitty).
To increase lv size, simply do in smitty the following:
Increase the Size of a Logical Volume
Type or select values in entry fields.
Press Enter AFTER making all desired changes.
[Entry Fields]
* LOGICAL VOLUME name lv34
* Number of ADDITIONAL logical partitions [939] <=== new size!
PHYSICAL VOLUME names [vpath43 vpath44 vpath45] <== new disks!
POSITION on physical volume middle
RANGE of physical volumes minimum
MAXIMUM NUMBER of PHYSICAL VOLUMES [32]
to use for allocation
Allocate each logical partition copy yes
on a SEPARATE physical volume?
File containing ALLOCATION MAP []
Note: 939 is "additional" partitions. The final number of partitions will be 313+939.
This is now 40GB.
|
5. Increase size for file systems
Now that lv33 is increased, it is time to increase the file system size. Also do this in
smitty.
Type or select values in entry fields.
Press Enter AFTER making all desired changes.
[Entry Fields]
File system name /db/a33
NEW mount point [/db/a33]
SIZE of file system
Unit Size 512bytes
Number of units [20512768]
Mount GROUP []
Mount AUTOMATICALLY at system restart? yes
PERMISSIONS read/write
Mount OPTIONS []
Start Disk Accounting? no
Fragment Size (bytes) 4096
Number of bytes per inode 32768
Compression algorithm no
Large File Enabled true
Allocation Group Size (MBytes) 16
Some calculations here for clarity:
Number of unites, 20512768*512 = 10G
4 times that yields 20512768*4 = 82051072 (this should be 40GB).
Command: OK stdout: yes stderr: no
Before command completion, additional instructions may appear below.
Filesystem size changed to 82051072
After the above commands runs successfully, check the size of the file system:
$ df -k
/dev/lv33 41025536 33034296 20% 27 1% /db/a33
/dev/lv34 10256384 2387704 77% 28 1% /db/a34
/dev/lv35 10256384 2387712 77% 27 1% /db/a35
/dev/lv36 10256384 2387704 77% 28 1% /db/a36
Now /db/a33 is 40GB!
Repeat for 34,35 & 36. You should get to:
/dev/lv33 41025536 33034296 20% 27 1% /db/a33
/dev/lv34 41025536 33034288 20% 28 1% /db/a34
/dev/lv35 41025536 33034296 20% 27 1% /db/a35
/dev/lv36 41025536 33034288 20% 28 1% /db/a36
|
6. And finally, increase DB tablespace!
. ~db2inst1/sqllib/db2profile
db2 connect to pwtest01 user pwtest using pwtest1
db2 "ALTER TABLESPACE L002 RESIZE (ALL 2000 M)"
(this increases L002 from 1GB to 8GB, because ALL extends all 4 file containers)
Now, take a look at the new file containers for L002:
bash-3.00# ls -l /db/a33/pwtest01/
total 19238912
-rw-rw-rw- 1 db2inst1 db2admi 0 Jul 24 02:42 .SQLCRT.FLG
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L001
-rw------- 1 db2inst1 db2admi 2097152000 Aug 29 15:58 L002 <== new size!!
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L003
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L004
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L005
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L006
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 15:57 U027
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U029
Your database should be happy now!
|
|
