Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: error (nike.apache.org: local policy)
From: "Bergquist, Brett" <BBergquist@canoga.com>
To: "derby-dev@db.apache.org" <derby-dev@db.apache.org>
Date: Tue, 7 May 2013 07:35:26 -0700
Subject: Does the XPLAIN feature work in Derby 10.9.1.0
Thread-Topic: Does the XPLAIN feature work in Derby 10.9.1.0
Thread-Index: Ac5LMB0xsx4XHWbnTLmG9kQM4EmkaA==
Message-ID: <97EB699F861AD841B5908C7CA9C956560269AC2C1731@VSERVER1.canoga.com>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-Type: multipart/alternative;
	boundary="_000_97EB699F861AD841B5908C7CA9C956560269AC2C1731VSERVER1can_"
MIME-Version: 1.0

--_000_97EB699F861AD841B5908C7CA9C956560269AC2C1731VSERVER1can_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I am running Derby 10.9.1.0 and am trying to figure out a  performance prob=
lem 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. Ver=
ify 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 !=3D 'IDLE';
call syscs_util.syscs_set_runtimestatistics(0);
call syscs_util.syscs_set_xplain_schema('');
call syscs_util.syscs_set_xplain_mode(0);

And still get the same error.   Could someone try this on their system and =
see if it works.  It would be most appreciated.

Brett

--_000_97EB699F861AD841B5908C7CA9C956560269AC2C1731VSERVER1can_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40"><head><meta http-equiv=3DContent-Type content=
=3D"text/html; charset=3Dus-ascii"><meta name=3DGenerator content=3D"Micros=
oft Word 12 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DEN-US link=3Dblue vli=
nk=3Dpurple><div class=3DWordSection1><p class=3DMsoNormal>I am running Der=
by 10.9.1.0 and am trying to figure out a&nbsp; performance problem with qu=
ery taking too long.&nbsp;&nbsp; I don&#8217;t actually want to run the que=
ry so from the documentation, I try:<o:p></o:p></p><p class=3DMsoNormal><o:=
p>&nbsp;</o:p></p><p class=3DMsoNormal>call syscs_util.syscs_set_runtimesta=
tistics(1);<o:p></o:p></p><p class=3DMsoNormal>call syscs_util.syscs_set_xp=
lain_schema('STATS');<o:p></o:p></p><p class=3DMsoNormal>call syscs_util.sy=
scs_set_xplain_mode(1);<o:p></o:p></p><p class=3DMsoNormal>--- my query her=
e<o:p></o:p></p><p class=3DMsoNormal>call syscs_util.syscs_set_runtimestati=
stics(0);<o:p></o:p></p><p class=3DMsoNormal>call syscs_util.syscs_set_xpla=
in_schema('');<o:p></o:p></p><p class=3DMsoNormal>call syscs_util.syscs_set=
_xplain_mode(0);<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p=
 class=3DMsoNormal>When it runs my query, I get:<o:p></o:p></p><p class=3DM=
soNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal style=3D'text-indent:.5i=
n'>ERROR XCL16: ResultSet not open. Operation 'getMetaData' not permitted. =
Verify that autocommit is OFF.<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbs=
p;</o:p></p><p class=3DMsoNormal>I try the following with IJ just to see:<o=
:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal=
>call syscs_util.syscs_set_runtimestatistics(1);<o:p></o:p></p><p class=3DM=
soNormal>call syscs_util.syscs_set_xplain_schema('STATS');<o:p></o:p></p><p=
 class=3DMsoNormal>call syscs_util.syscs_set_xplain_mode(1);<o:p></o:p></p>=
<p class=3DMsoNormal>select sql_text from syscs_diag.transaction_table wher=
e status !=3D 'IDLE';<o:p></o:p></p><p class=3DMsoNormal>call syscs_util.sy=
scs_set_runtimestatistics(0);<o:p></o:p></p><p class=3DMsoNormal>call syscs=
_util.syscs_set_xplain_schema('');<o:p></o:p></p><p class=3DMsoNormal>call =
syscs_util.syscs_set_xplain_mode(0);<o:p></o:p></p><p class=3DMsoNormal><o:=
p>&nbsp;</o:p></p><p class=3DMsoNormal>And still get the same error.&nbsp;&=
nbsp; Could someone try this on their system and see if it works.&nbsp; It =
would be most appreciated.<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</=
o:p></p><p class=3DMsoNormal>Brett<o:p></o:p></p></div></body></html>=

--_000_97EB699F861AD841B5908C7CA9C956560269AC2C1731VSERVER1can_--