Question & Answer
Why does a select from Contents take a long time?
Contents is a very complex view
Contents is a very complex view made up of 4 tables. Because of this complexity it is expected a Select against this view may have a high cost in CPU and buffer. Depending on the select against this view it may cause a server crash if the buffer/temp space is exhausted. Should a select experience a high performance/resource impact the following can be done:
1) Canceling a long running SQL statement on the server
2) Change the select so that it uses less resources. This becomes more involved as a "DB2 Explain" would be needed against the original select and the proposed selects to determine the costs.
Note: In the case of a crash the activity log may show messages similar to the following. Check the session number. Determine if this session is issuing the Selects. If so, then there is a good chance the cost of the select was very high or the resources are limited. Selects run in TEMPSPACE.:
08/13/14 03:02:01 ANR0171I tbnsql.c(735): Error detected on 75:1, database in evaluation mode. (SESSION: 68514)
08/13/14 03:02:01 ANR0175E tbnsql.c(735): Space exhausted for one or more of the following: DB, LOG, or instance directory. (SESSION: 68514) 08/13/14 03:02:01 ANR0162W Supplemental database diagnostic information: -1:57011:-968 ([IBM][CLI Driver][DB2/AIX64] SQL0968C The file system is full. SQLSTATE=57011). (SESSION: 68514)
08/13/14 03:02:02 Transaction hash table contents (slots=256):
From the db2diag.log it will show which tablespace filled ( "TEMPSPACE1" ):
2014-08-13-03.02.01.635819-240 E95795090A778 LEVEL: Error
PID : 26607616 TID : 5185 PROC : db2sysc 0
INSTANCE: tsmcor01 NODE : 000
EDUID : 5185 EDUNAME: db2pclnr (TSMDB1) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbClnrAsyncWriteCompletion, probe:0
MESSAGE : ADM6017E The table space "TEMPSPACE1" (ID "1") is full.
Detected on container "/tsm/tsmcor01/db4/tsmcor01/NODE0000/TSMDB1/T0000001/C0000003.TMP"
(ID "3"). The underlying file system is full or the maximum allowed
space usage for the file system has been reached. It is also possible that there are user limits in place with respect to maximum file size
and these limits have been reached.
TSM ADSM ITSM IBM Spectrum Protect
17 June 2018