db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Question on why the statement cache is saying a statement is invalid
Date Thu, 07 Aug 2014 16:51:46 GMT
I am trying to figure out an issue where the optimizer is taking a long time to compile a statement
and want to make sure that the subsequent times through it will use the cached statement if
present in the statement cache.

I prepare the statement and then I look at the cache with:

SELECT * FROM SYSCS_DIAG.STATEMENT_CACHE

And it shows that the statement is invalid.  Why would the a statement be in the cache and
be invalid?

ij> SELECT * FROM SYSCS_DIAG.STATEMENT_CACHE;
ID                                  |SCHEMANAME                                          
                                                                           |SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3d3b0068-0147-b12d-b252-000065089f97|CSEM                                                
                                                                           |SELECT * FROM
SYSCS_DIAG.STATEMENT_CACHE order by ID desc
                                                                                         
                                                                            |true |true |NULL
5e838074-0147-b12d-b252-000065089f97|APP                                                 
                                                                           |call SYSIBM.SQLCAMESSAGE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                                                                                         
                                                                            |true |true |NULL
6302404f-0147-b12d-b252-000065089f97|CSEM                                                
                                                                           |call SYSIBM.SQLCAMESSAGE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                                                                                         
                                                                            |true |true |NULL
65f6406f-0147-b12d-b252-000065089f97|CSEM                                                
                                                                           |SELECT
t0.ID,
t0.DTYPE,
t0.RELATIVE_POSITION,
t0.ALIAS_NAME,
t0.DESCRIPTION,
t0.DEVICE_NAME,
t0.OPLOCK,
t0.PARENTENTITY_ID,
t1.ID,
t1.MODEL,
t1.SERIAL_NUMBER,
t1.HARDWARE_TYPE,
t1.HARDWARE_VERSION,
t2.ID,
t2.NEXT_CONFIGURATION_NUMBER,
t2.NEWEST_CONFIGURATIONSET_ID,
t2.OLDEST_CONFIGURATIONSET_ID,
t3.ID,
t3.TEMPLATE,
t3.SYSTEM_TEMPLATE,
t3.ADMIN_ENABLED,
t3.TEMPLATE_PROPERTIES_ID,
t3.ENTITY_FACET_MANAGER_ID,
t4.ID,
t4.LAST_OBSERVED_PING,
t4.SNMP_READ_TIMESTAMP,
t4.LAST_OBSERVED_SNMP_WRITE,
t4.LAST_OBSERVED_SNMP_READ,
t4.PING_TIMESTAMP,
t4.SNMP_WRITE_TIMESTAMP,
t5.ID,
t5.POWER_LED_STATE_TIMESTAMP,
t5.LAST_OBSERVED_STATUS_LED_STATE,
t5.MAINBOARD_VOLTAGE_STATUS_TIMESTAMP,
t5.LAST_OBSERVED_MAINBOARD_VOLTAGE_STATUS,
t5.STATUS_LED_STATE_TIMESTAMP,
t5.LAST_OBSERVED_POWER_LED_STATE,
t6.ID,
t7.ID
FROM CORE_V1.SNMP_DEVICE t9,
CORE_V1.SNMP_DEVICE_IP t8,
PKG_9145E_V1.CHASSIS_9145E_104_0_0 t7,
PKG_9145E_V1.CHASSIS_9145E_BC04 t6,
PKG_9145E_V1.CHASSIS_9145E t5,
CORE_V1.AGENT_MANAGED_HARDWARE t4,
CORE_V1.MANAGED_HARDWARE t3,
CORE_V1.CONFIGURABLE_HARDWARE t2,
CORE_V1.HARDWARE t1,
CORE_V1.DEVICE_ENTITY t0
WHERE
(
   (
      (t8.IP = CSEM.STRING_TO_IP(?))
      AND
      (
         (
            (t7.ID = t0.ID)
            AND
            (
               (t6.ID = t0.ID)
               AND
               (
                  (t5.ID = t0.ID)
                  AND
                  (
                     (t4.ID = t0.ID)
                     AND
                     (
                        (t3.ID = t0.ID)
                        AND ((t2.ID = t0.ID) AND (t1.ID = t0.ID))
                     )
                  )
               )
            )
         )
         AND (t0.DTYPE = 'CHASSIS_9145E_104_0_0')
      )
   )
   AND ((t9.AGENT_MANAGED_HARDWARE_ID = t0.ID) AND (t8.SNMPDEVICE_ID = t9.ID))
)                                                                                        
                                                                                         
                   |true |true |NULL
832a0051-0147-b12d-b252-000065089f97|CSEM                                                
                                                                           |SELECT
t0.ID,
t0.DTYPE,
t0.RELATIVE_POSITION,
t0.ALIAS_NAME,
t0.DESCRIPTION,
t0.DEVICE_NAME,
t0.OPLOCK,
t0.PARENTENTITY_ID,
t1.ID,
t1.MODEL,
t1.SERIAL_NUMBER,
t1.HARDWARE_TYPE,
t1.HARDWARE_VERSION,
t2.ID,
t2.NEXT_CONFIGURATION_NUMBER,
t2.NEWEST_CONFIGURATIONSET_ID,
t2.OLDEST_CONFIGURATIONSET_ID,
t3.ID,
t3.TEMPLATE,
t3.SYSTEM_TEMPLATE,
t3.ADMIN_ENABLED,
t3.TEMPLATE_PROPERTIES_ID,
t3.ENTITY_FACET_MANAGER_ID,
t4.ID,
t4.LAST_OBSERVED_PING,
t4.SNMP_READ_TIMESTAMP,
t4.LAST_OBSERVED_SNMP_WRITE,
t4.LAST_OBSERVED_SNMP_READ,
t4.PING_TIMESTAMP,
t4.SNMP_WRITE_TIMESTAMP,
t5.ID,
t5.POWER_LED_STATE_TIMESTAMP,
t5.LAST_OBSERVED_STATUS_LED_STATE,
t5.MAINBOARD_VOLTAGE_STATUS_TIMESTAMP,
t5.LAST_OBSERVED_MAINBOARD_VOLTAGE_STATUS,
t5.STATUS_LED_STATE_TIMESTAMP,
t5.LAST_OBSERVED_POWER_LED_STATE,
t6.ID,
t7.ID
FROM CORE_V1.SNMP_DEVICE t9,
CORE_V1.SNMP_DEVICE_IP t8,
PKG_9145E_V1.CHASSIS_9145E_104_0_0 t7,
PKG_9145E_V1.CHASSIS_9145E_BC04 t6,
PKG_9145E_V1.CHASSIS_9145E t5,
CORE_V1.AGENT_MANAGED_HARDWARE t4,
CORE_V1.MANAGED_HARDWARE t3,
CORE_V1.CONFIGURABLE_HARDWARE t2,
CORE_V1.HARDWARE t1,
CORE_V1.DEVICE_ENTITY t0
WHERE
(
   (
      (t8.IP = ?)
      AND
      (
         (
            (t7.ID = t0.ID)
            AND
            (
               (t6.ID = t0.ID)
               AND
               (
                  (t5.ID = t0.ID)
                  AND
                  (
                     (t4.ID = t0.ID)
                     AND
                     (
                        (t3.ID = t0.ID)
                        AND ((t2.ID = t0.ID) AND (t1.ID = t0.ID))
                     )
                  )
               )
            )
         )
         AND (t0.DTYPE = 'CHASSIS_9145E_104_0_0')
      )
   )
   AND ((t9.AGENT_MANAGED_HARDWARE_ID = t0.ID) AND (t8.SNMPDEVICE_ID = t9.ID))
)                                                                                        
                                                                                         
                                      |true |false|NULL
6ebdc076-0147-b12d-b252-000065089f97|APP                                                 
                                                                           |SELECT * FROM
SYSCS_DIAG.STATEMENT_CACHE
                                                                                         
                                                                            |true |true |NULL
c5bf806d-0147-b12d-b252-000065089f97|SYS                                                 
                                                                           |SELECT    M->allProceduresAreCallable(),
 M->allTablesAreSelectable(),
M->supportsCatalogsInTableDefinitions(),        M->supportsCatalogsInIndexDefinitions(),
       M->supportsCatalogsInPrivilegeDefinitions(),    M->supportsPositionedDelete(),
 M->supportsPositionedUpdate(),  M->supportsSelectForUpdate(),
2589c06b-0147-b12d-b252-000065089f97|CSEM                                                
                                                                           |CALL SYSIBM.MetaData()
                                                                                         
                                                                            |true |true |NULL

Mime
View raw message