The case of the rapidly expanding TEMPSPACE1 tablespace

An application install was forcing  the TEMPSPACE1 to fill very quickly and constantly getting to the system disk is full error. The query was crashing as disk full limits were stopping the query to finish successfully.The db2diag log entries were referencing  ADM6017E The following table space is full. Table space name TEMPSPACE1. Disk is full

A sql trace captured the query running during this activity. Within the query there was a scalar function.I used the query on How to list UDF in DB2 and returned the routine body.

An examination of the routine revealed some useful details. The routine accepted as an input argument for xml files p_fieldsXml BLOB(1M)

The function did some data manipulation and created some output into a data type VARCHAR(8000).

This was a recordset of 1.5 million rows with some very large xml files. This function was the culprit creating the high requirement for TEMPSPACE1

My feedback to the application was to either create a process to clear out the large objects that are TEMPSPACE1 hungry or alternatively offer clearer direction on preparing \ capacity planning TEMPSPACE1 usage .

Read more

Trace sql statements in DB2 database (DBA DB2)