2478669 - Locking issues due to RUNSTATS on volatile tables TESTDATRNRPART*

• When managing InfoCubes in transaction RSA1 you may encounter long locks due to RUNSTATS being performed on the TESTDATRNRPART* tables.
• This may occur even though these tables are already defined as volatile.

You can observe this in a number of different ways:

• Application Snapshot

 Application handle                         = <APPHANDLE>
 Application status                         = UOW Waiting
 ...
 TP Monitor client application name         = RSA1
 TP Monitor client accounting string        = CL_SQL_STATEMENT==============CP
 ...
 Dynamic SQL statement text:
 LOCK TABLE "TESTDATRNRPARTB" IN SHARE MODE -- LOCATION( CL_SQL_STATEMENT==============CP , 104 ) -- HASH_VALUE( 2510524824 )

• DB6UTIL

If you have a running db6util trace you will observe:

LOCK WAITS:
 ----------

         4272                4275                4358
  (PID:1507646)  <--  (PID:1507646)  <-- (PID:57475142)
     disp+work           disp+work              DB2ATS
             
                             ^                    4359
                             |---------- (PID:57475142)
                                                DB2ATS

      ID      PID       APPL-NAME     HOSTNAME(MEMB)  MODE RMODE OBJECT TABLE

   4272  1507646    disp+work           <hostname>   NON   NON

Status     : UOWWAIT
 User Id    : JDOE
 Wkstn      : <wkstn>
 Appl.      : ptype BTC
 Acc. Info  : CL_SQL_STATEMENT==============CP
 Last SQL   : LOCK TABLE "TESTDATRNRPARTB" IN SHARE MODE -- LOCATION(
              CL_SQL_STATEMENT==============CP , 74 ) -- HASH_VALUE(
              1137236766 )

• SM21/ST11 - SQL2310N  The utility could not generate statistics.  Error "-911"

 C  *** ERROR in DB6Open[/bas/745_REL/src/dbs/db6/dbdb6.c, 3748] CON = 2 (BEGIN)
 C  &+     dsql_db6_open( SQLExecute ): [IBM][CLI Driver][DB2/AIX64] SQL2310N  The utility could not generate statistics.  Er
 C  &+     ror "-911" was returned row=1
 C  &+
 C  &+     CALL ADMIN_CMD(?) /* RUNSTATS */
 C  &+       cursor type=NO_HOLD, isolation=UR, cc_release=YES, optlevel=5, degree=1, op_type=24, read_only=0
 C  &+
 C  &+     Input SQLDA:
 C  &+                        1 CT=CHAR            T=VARCHAR         L=142   P=142   S=0
 C  &+
 C  &+     Input data:
 C  &+     row 1:             1 CHAR            I=142     "runstats on table <SCHEMA>.TESTDATRNRPART9 on all columns with dis"...
 C  &+
 C  &+     ABAP location info 'CL_SQL_STATEMENT==============CP', 500

TESTDATRNRPART0 TESTDATRNRPART1 TESTDATRNRPART2 TESTDATRNRPART3 TESTDATRNRPART4 TESTDATRNRPART5 TESTDATRNRPART6 TESTDATRNRPART7 TESTDATRNRPART8 TESTDATRNRPART9 TESTDATRNRPARTA TESTDATRNRPARTB TESTDATRNRPARTC TESTDATRNRPARTD TESTDATRNRPARTE TESTDATRNRPARTF TESTDATRNRPARTG TESTDATRNRPARTH TESTDATRNRPARTI  TESTDATRNRPARTJ TESTDATRNRPARTK TESTDATRNRPARTL TESTDATRNRPARTM TESTDATRNRPARTNTESTDATRNRPARTO TESTDATRNRPARTP TESTDATRNRPARTQ TESTDATRNRPARTR TESTDATRNRPARTS TESTDATRNRPARTT TESTDATRNRPARTU TESTDATRNRPARTV TESTDATRNRPARTW TESTDATRNRPARTX TESTDATRNRPARTY TESTDATRNRPARTZ I_FORCE_STATISTICS , KBA , BC-DB-DB6 , DB2 Universal Database for Unix / NT , Problem