Releasing LOB space in DB2 databases

You can release the large object (LOB) storage in the DB2 database.

Pre-requisite:

Plan downtime for upgrading IBM® UrbanCode® Deploy and releasing LOB space.

Procedure:

Release LOB storage for DB2 database using the following steps:

Set the experimental.vcUpgrade.enabled=true parameter in the conf/server/installed.properties file.
Restart the IBM UrbanCode Deploy server.
Check on the progress of the conversion:
```
select count(*) from vc_persistent_record where persistent_data is not null
```
The compression process is complete when the row count reported by the query is 0.
Catalog the IBM UrbanCode Deploy DB2 database:
```
db2 catalog tcpip node dbname remote ipAddress server port
db2 catalog database dbname at node dbname
```
For example, enter the following command:
```
db2 catalog tcpip node mydb remote 10.134.119.178 server 48408
```
You must catalog the database only once on the client system. If you have to catalog a different database, either use a different node and database name (mydb in this example) or uncatalog the cataloged database:
```
db2 uncatalog database mydb
db2 uncatalog node mydb
```
Connect to the cataloged database:
```
db2 connect to mydb user db2inst1 using db2inst1
```
Catalog the database every time you start up the DB2 command window.

Check the database size:

db2 -v "CALL GET_DBSIZE_INFO(?, ?, ?, -2)"

Check how much space each tablespace is using:

db2 -v "SELECT varchar(tbsp_name, 30) as tbsp_name, decimal(tbsp_free_pages * tbsp_page_size)/1024/1024 as free_space_mb, decimal(tbsp_total_pages * tbsp_page_size)/1024/1024 as total_space_mb FROM TABLE(MON_GET_TABLESPACE('',-2))"

Compress the database table:
```
db2 -v "REORG TABLE VC_PERSISTENT_RECORD LONGLOBDATA"
db2 -v "ALTER TABLESPACE USERSPACE1 LOWER HIGH WATER MARK"
db2 -v "ALTER TABLESPACE USERSPACE1 REDUCE MAX"
```
The preceding example includes the assumption that the vc_persistent_record table is in a tablespace called USERSPACE1. Replace USERSPACE1 with the tablespace name that contains the vc_persistent_record table.

Note: The ALTER TABLESPACE commands must only be performed if the IBM UrbanCode Deploy is offline.

Post-requisite:

Verify that the space is reduced:
```
db2 -v "CALL GET_DBSIZE_INFO(?, ?, ?, -2)"
```
Set the experimental.vcUpgrade.enabled parameter back to false in the conf/server/installed.properties file.