Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Fri, 17 May 2013 12:13:19 +0000 (UTC)
From: "Brett Bergquist (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12646605.1368015340410.338925.1368792799341@arcas>
In-Reply-To: <JIRA.12646605.1368015340410@arcas>
References: <JIRA.12646605.1368015340410@arcas>
Subject: [jira] [Commented] (DERBY-6216) XPLAIN feature does not work and
 gives ERROR XCL16: ResultSet not open
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-6216?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1366=
0662#comment-13660662 ]=20

Brett Bergquist commented on DERBY-6216:
----------------------------------------

I found that if:

=09call syscs_util.syscs_set_xplain_mode(1);

is used.  When this is used, then you get:

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

Using XPLAIN in NOEXECUTE mode to me is the most important function especia=
lly when trying to figure out why a query that might be taking hours to run=
 is doing so.   Without this working you have to wait hours for the query t=
o run to figure out why.

               =20
> XPLAIN feature does not work and gives ERROR XCL16: ResultSet not open
> ----------------------------------------------------------------------
>
>                 Key: DERBY-6216
>                 URL: https://issues.apache.org/jira/browse/DERBY-6216
>             Project: Derby
>          Issue Type: Bug
>          Components: Tools
>    Affects Versions: 10.9.1.0
>         Environment: Solaris 10, MacOS X 10.6, Windows XP
>            Reporter: Brett Bergquist
>              Labels: features
>
> I am running Derby 10.9.1.0 and am trying to figure out a  performance pr=
oblem with query taking too long.   I don=E2=80=99t actually want to run th=
e 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. V=
erify 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);
> Note I have tried this with real tables as apposed to the syscs_diag.tran=
saction_table with the same result.  The only reason that I posted this wit=
h this table is that it is a table that is accessible for any database.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrato=
rs
For more information on JIRA, see: http://www.atlassian.com/software/jira