Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
CREATE TABLE statementUse the CREATE TABLE statement to create a new permanent table in the current database.
Storage optionsUse the FRAGMENT BY clause, PUT clause, and EXTENT size options of the CREATE TABLE statement to specify the storage location, distribution scheme, and extent size for the table. Use the FRAGMENT BY clause, PUT clause, EXTENT size options, and COMPRESSED option of the CREATE TABLE statement to specify the storage location, the distribution scheme, the extent size for the table, and whether the table is enabled for the automatic compression of large amounts of new row data.
FRAGMENT BY clauseUse the FRAGMENT BY clause to create a fragmented table and to specify its storage distribution scheme. The keywords PARTITION BY are a synonym for FRAGMENT BY.
Expression Fragment ClauseIn an expression-based distribution scheme, each fragment expression in a rule specifies a storage space. Each fragment expression in the rule isolates data and aids the database server in searching for rows.
User-Defined Functions in Fragment Expressions

User-Defined Functions in Fragment Expressions

For rows that include user-defined data types, you can use comparison conditions or user-defined functions to define the range rules. In the following example, comparison conditions define the range rules for the long1 column, which contains an opaque data type:

FRAGMENT BY EXPRESSION
   long1 < '3001' IN dbsp1,
   long1 BETWEEN '3001' AND '6000' IN dbsp2,
   long1 > '6000' IN dbsp3;

An implicit, user-defined cast converts 3001 and 6000 to the opaque type.

Alternatively, you can use user-defined functions to define the range rules for the opaque data type of the long1 column:

FRAGMENT BY EXPRESSION
   (lessthan(long1,'3001')) IN dbsp1,
   (greaterthanorequal(long1,'3001') AND 
   lessthanorequal(long1,'6000')) IN dbsp2,
   (greaterthan(long1,'6000')) IN dbsp3;

Explicit user-defined functions require parentheses around the entire fragment expression before the IN clause, as the previous example shows.

User-defined functions in a fragment expression can be written in SPL or in the C or Java™ language. These functions must satisfy four requirements:

They must evaluate to a Boolean value.
They must be nonvariant.
They must reside within the same database as the table.
They must not generate OUT nor INOUT parameters.

For information on how to create UDRs for fragment expressions, refer to HCL OneDB™ User-Defined Routines and Data Types Developer's Guide.