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
