1.1

If a query includes search conditions only for col1 and col3, then the index is used only for the col1 search
condition, because col1 is a prefix of the indexed columns. The search condition for col3 is treated as a separate,
unindexed filter operation.
Create additional indexes when necessary to ensure that queries use a prefix of an index's columns.
Case Sensitivity for Indexes
By default all indexes are case-sensitive. SQLFire supports case-insensitive indexes for CHAR and VARCHAR
columns if you specify the caseSensitive=false hint at the end of the CREATE INDEX statement. A
case insensitive index enables queries to look up column values while ignoring case differences.
SQLFire performs case insensitive index lookups only for equality-based criteria that appear in queries. Case is
not ignored when using the LIKE clause, or when using the >, >=, <, or <= operators.
Note: Use the EXPLAIN command and view the generated query plan to verify that case insensitive
index searches are used where needed.
For columns that are not part of an index, SQLFire observes case-sensitivity only if it is explicitly required (for
example, if the query specifies UPPER(column-name='UPPERCASE_VALUE').
Queries that use the OR clause to perform comparisons on columns of a case-sensitive index must use the UPPER
function on those columns to ensure correct results.
Example
Create an index on two columns:
CREATE INDEX idx ON FLIGHTS (flight_id ASC, segment_number
DESC);
Create a case-insensitive index:
CREATE INDEX idx ON FLIGHTS (flight_id ASC, segment_number
DESC) -- SQLFIRE-PROPERTIES caseSensitive=false <return>
;
CREATE PROCEDURE
Creates a Java stored procedure that can be invoked using the CALL statement.
Syntax
CREATE PROCEDURE
procedure-name
([ procedure-parameter [, procedure-parameter] * ])
LANGUAGE JAVA
PARAMETER STYLE JAVA
{ NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA }
[ [DYNAMIC]
RESULT SETS
integer]
EXTERNAL NAME 'procedure_external_class.method'
Description
CREATE PROCEDURE creates a Java stored procedure that can be invoked using the CALL on page 460
statement. A procedure is invoked on the current member. Because the procedure is written in Java code, it can
473
SQL Language Reference