CA Datacom DBID 017 TTM 100% Full

Document ID : KB000117443
Last Modified Date : 12/10/2018
Show Technical Document Details
Issue:
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.
 
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.

The following SQL query can be run with DBSQLPR to determine the current percent blocks used in the TTM:

SELECT TTM_BLKS_ALLOC, 
TTM_BLKS_IN_USE, 
TTM_BLKS_IN_USE * 100 / TTM_BLKS_ALLOC AS PERCENT_BLOCKS 
FROM SYSADM.SQL_STATUS ; 


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 KB000109695 - Increase VIRTUAL TTM size to avoid -560
See DocOps section Extending the Temporary Table Manager (TTM)