The case of the rapidly expanding TEMPSPACE1 tablespace

29 January,2017 by Jack Vamvas

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)

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on The case of the rapidly expanding TEMPSPACE1 tablespace

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright