Details
-
Task
-
Status: Closed
-
Minor
-
Resolution: Invalid
-
10.5.3.0
-
None
-
Urgent
Description
Hello,
Please help on the syntax of using Cost Based Optimizer that I am using.
SELECT count (REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';
Column name- REPOSITORY_KEY
Table - TIDLRREP
Index name - TIILRREP
Also tried using - DERBY-PROPERTIES
When I through a file using java org.apache.derby.tools.ij C:\mysql.sql > C:\GLS\myoutput.txt
Derby Database Version : db-derby-10.5.3.0-bin
mysql.sql file contains
connect 'jdbc:derby:\test\PerformanceTest;user=admin;password=password';
MaximumDisplayWidth 9999;
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
SELECT count(REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';
Error output in myoutput.txt
ij version 10.5
ij> connect 'jdbc:derby:\test\PerformanceTest;user=admin;password=password';
ij> MaximumDisplayWidth 9999;
ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
0 rows inserted/updated/deleted
ij> SELECT count(REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';;
ERROR 42Y46: Invalid Properties list in FROM list. There is no index 'TIILRREP WHERE REPOSITORY_KEY LIKE 'S%';' on table 'TIDLRREP'.
ij>
Also I have added below entries in my derby.properties file
derby.language.logQueryPlan=true
derby.optimizer.noTimeout=true
When I directly the query on ij ... it just hangs!!
Any help is appreciated.
Thanks
Mamatha