IBM Support

Select from Contents performance

Question & Answer


Question

Why does a select from Contents take a long time?

Cause

Contents is a very complex view

Answer

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
http://www-01.ibm.com/support/docview.wss?uid=swg21683049

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.

[{"Product":{"code":"SSGSG7","label":"Tivoli Storage Manager"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":"Server","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF025","label":"Platform Independent"},{"code":"PF033","label":"Windows"}],"Version":"6.3;7.1","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}}]

Product Synonym

TSM ADSM ITSM IBM Spectrum Protect

Document Information

Modified date:
17 June 2018

UID

swg21684150