Thanks all. I don't know what could invalidate it but I just did my test again, quiet database, prepared the statement, looked at the cache and all is well. Thanks for taking the time to provide some information.
Brett -----Original Message----- From: Rick Hillegas [mailto:[email protected]] Sent: Thursday, August 07, 2014 2:09 PM To: [email protected] Subject: Re: Question on why the statement cache is saying a statement is invalid On 8/7/14 10:42 AM, Dag H. Wanvik wrote: > If it appears to be invalid right after you compile it, I don't know > how that could happen. Rick, Knut? The invalid statement is a gigantic piece of sql which depends on a lot of objects. The invalidation of any of those objects could have made the statement invalid. > > Dag > > On 07. aug. 2014 18:51, Bergquist, Brett wrote: >> >> 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 >> >
