Datacom DBID 017 TTM 100% Full

Products

• Datacom
• DATACOM - AD
• Datacom/AD
• Datacom/DB

Issue/Introduction

The SQL Temporary Table Manager (TTM), DBID 017 shows 100% full. One SQL query received SQL code -560. 
Even after doing a dynamic extend the percent full never goes down.

Environment

Release: 15.1

Resolution

The TTM shows 100% because that's the high water mark of allocated blocks. Once blocks are used they will remain allocated but space will be freed once the query ends and reused by subsequent SQL requests that need them. The SQL code -560 only causes the running query to fail. TTM space will then be reused and the next query will not continue to get a -560. The percent usage of the TTM will be reset each time the MUF restarts.

To determine the current percent blocks used in the TTM an SQL query can be run with DBSQLPR, see article Determining the amount of TTM blocks in use.

The TTM can be extended using the DYNAMIC_EXTEND console-like command if using DASD or for a VIRTUAL TTM using the VIRTUAL_DYNAMIC_EXTEND console-like command. 

Note that the VIRTUAL_DYNAMIC_EXTEND changes the VIRTUAL dynamic extend options but does not do the extend. The dynamic extend will occur the next time a query runs which causes the area to run out of space. 

Additional Information

See article Increase VIRTUAL TTM size to avoid -560
See Datacom documentation section Creating and Maintaining the Temporary Table Manager Area