[DERBY-6216] XPLAIN feature does not work and gives ERROR XCL16: ResultSet not open

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.6.2.4, 10.7.1.4, 10.8.3.3, 10.9.1.0
• Fix Version/s: 10.10.2.0, 10.11.1.1
• Component/s: Tools
• Labels: derby_triage10_11 features
• Environment: Solaris 10, MacOS X 10.6, Windows XP
• Urgency: Normal
• Issue & fix info: Repro attached
• Bug behavior facts: Regression, Seen in production
• Assignee: Bryan Pendleton
• Reporter: Brett Bergquist
• Created: 08/May/13 12:15
• Updated: 21/Jan/15 00:23
• Resolved: 05/Mar/14 19:28

Description

I am running Derby 10.9.1.0 and am trying to figure out a performance problem with query taking too long. I don’t actually want to run the query so from the documentation, I try:

call syscs_util.syscs_set_runtimestatistics(1);
call syscs_util.syscs_set_xplain_schema('STATS');
call syscs_util.syscs_set_xplain_mode(1);
— my query here
call syscs_util.syscs_set_runtimestatistics(0);
call syscs_util.syscs_set_xplain_schema('');
call syscs_util.syscs_set_xplain_mode(0);

When it runs my query, I get:

ERROR XCL16: ResultSet not open. Operation 'getMetaData' not permitted. Verify that autocommit is OFF.

I try the following with IJ just to see:

call syscs_util.syscs_set_runtimestatistics(1);
call syscs_util.syscs_set_xplain_schema('STATS');
call syscs_util.syscs_set_xplain_mode(1);
select sql_text from syscs_diag.transaction_table where status != 'IDLE';
call syscs_util.syscs_set_runtimestatistics(0);
call syscs_util.syscs_set_xplain_schema('');
call syscs_util.syscs_set_xplain_mode(0);

Note I have tried this with real tables as apposed to the syscs_diag.transaction_table with the same result. The only reason that I posted this with this table is that it is a table that is accessible for any database.

Issue Links

• is related to: DERBY-6297 Intermittent failures in XplainStatisticsTest.testSimpleXplainOnly
• is related to: DERBY-2487 Enhance Derby with EXPLAIN Functionality
• is related to: DERBY-6481 Derby 10.10 backport issue (winter/spring 2014)