DB SQL statements

Syntax Conventions

Syntax diagrams appear in monospace type and use the following conventions:

ALTER INDEX

Description

Alters an existing index by passing specific c-tree file attributes to change, or by renaming the index.

Syntax

ALTER INDEX [ owner_name. ] index_name [ON [ owner_name. ] table_name

{STORAGE_ATTRIBUTES 'attributes'

| RENAME TO new_index_name

};

(Support for altering an index by renaming it, RENAME TO new_index_name, was added in V11.)

Arguments

STORAGE_ATTRIBUTES 'attributes'

A quoted string specifying index attributes to modify. FairCom DB SQL recognizes the following attributes:

• 'HUGE' -- Recreate the index file as a c-tree HUGE file.
• 'partition=<rule>' -- Use this index with <rule> as a partition rule and create table as partitioned file. Only one index can be the partitioning index rule for a table at a time.

Examples

By default, FairCom DB SQL returns an error in response to an ALTER INDEX statement:

ISQL> alter index t1_ix ON t1 RENAME TO t1_mod_ix;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege.
• Ownership of the index.
• EXCLUSIVE file access is required for any ALTER operation.

ALTER SEQUENCE

Syntax

ALTER SEQUENCE sequence_name
[ INCREMENT BY increment_value ]
[ MAXVALUE max_value | NOMAXVALUE ]
[ MINVALUE min_value | NOMINVALUE ]
[ CYCLE | NOCYCLE ]

Description

ALTER SEQUENCE is a SQL command used to alter one or more characteristics of a sequence.

• sequence_name is the name of the sequence that is to be modified.
• increment_value specifies a new increment by value to be associated with the sequence.
• max_value specifies a new maximum value to be associated with the sequence.
• min_value specifies a new minimum value to be associated with the sequence.
• cycle specifies if the sequence should cycle or not.

Note that it is possible to put the sequence into a state from which no further values can be returned through the use of the alter command. For example, if the new max_value is less than the current value of the sequence for an ascending sequence, or if the new increment_value would make the next value be outside of the sequence bounds.

Return Values

See c-tree Plus Error Codes for a complete listing of valid c-tree Plus error values.

See Also

• Sequence Values (Sequence Values, /doc/sqlref/sql-sequence-values.htm)
• DROP SEQUENCE
• CREATE SEQUENCE

ALTER TABLE

Description

Alters the schema of an existing table by adding new columns or modifying existing columns of the specified table.

Syntax

ALTER TABLE [ owner_name. ] table_name

{ ADD new_column_definition

| ADD (new_column_definition [, new_column_definition] ...)

| MODIFY (column_name [ new_column_type ]

[ default_value ] [ NULL | NOT NULL ]

| DROP [ COLUMN ] { column_name | ( column_name , ... ) }

| ADD [ CONSTRAINT new_constraint_name ] table_constraint

| RENAME CONSTRAINT constraint_name TO new_constraint_name

| RENAME COLUMN column_name TO new_column_name

| RENAME TO new_table_name

};

new_column_definition ::

new_column_name column_type

[ [ NULL | NOT NULL ]

[ default_value

| IDENTITY [(seed, increment)] ] ]

default_value::

DEFAULT { literal | USER | NULL | UID | SYSDATE | SYSTIME | SYSTIMESTAMP }
 

table_constraint ::

PRIMARY KEY ( column [, ... ] )

| UNIQUE ( column [, ... ] )

| FOREIGN KEY ( column [, ... ] )

REFERENCES [ owner_name. ] table_name [ ( column [, ... ] ) ]

| CHECK ( search_condition )

[ REORG ]

Notes

• Column additions and modifications have the following restrictions:
  • A NOT NULL column can be added to a table only if the table does not contain any rows.
  • The type can be modified or the length of the column can be decreased only if all the rows contain null values for the column being modified.
  • An existing column can be made NOT NULL only if none of the rows contain a null value for that column.
• When a new column is added to an existing table, it is added after the existing columns for the table.
• Views that automatically refer to all the columns of a table (such as SELECT * FROM ...) need to be dropped and added to select any columns that have been added to the table after the view has been created.
• If you add a FOREIGN KEY constraint to an existing table, the table and column specified in the REFERENCES clause must exist before the constraint will work as expected. However, FairCom DB SQL does not check for the existence of the table and column specified in the REFERENCES clause. It is up to you to make sure they exist.
• When you drop or rename a column, the following database objects dependent on that column are also automatically dropped or updated:
  • Indexes that specify the column as one of the components
  • Update triggers that specify the column
  • Unique, foreign key, and check constraints that refer to the column
  • UPDATE and REFERENCES privileges that refer to the column
• ALTER TABLE DROP COLUMN statements cannot:
  • Specify a column created as a primary key, if columns in other tables refer to the column in a referential constraint
  • Name all the columns in a table
• When new columns are added IDENTITY can be specified for one of the columns only if the table does not already contain an identity column. A DEFAULT value cannot be specified for and IDENTITY column.
• Existing rows are not updated to the default value, even if the data was initially missing. Only new inserted rows as assigned the new default value.
• (FairCom DB SQL V10.3 and later) Any table altered in the structure using ALTER TABLE or truncated using TRUNCATE will have the $DELFLD$ set to 4 bytes despite the size in the original table.
• RENAME CONSTRAINT is available in V11 and later.
• REORG is available in V11.5 and later. See below.

Examples

To add columns to the tmp table:

ISQL> ALTER TABLE tmp

ADD mname CHAR(8) ;

ISQL> ALTER TABLE tmp

ADD (mname CHAR(8), nname CHAR(8)) ;

To add table-level constraints, consider the table tmp consisting of integer fields, fld, fld1, fld2, and fld3.

• To create a unique key on fld:

ISQL> ALTER TABLE tmp ADD UNIQUE(fld) ;

• To create a primary key on fld1:

ISQL> ALTER TABLE tmp ADD primary key(fld1) ;

• To create a foreign key constraint named fri_cns on fld2:

ISQL> ALTER TABLE tmp ADD constraint fri_cns foreign key(fld2) ;

• To create a check constraint, chk_cns on fld3:

ISQL> ALTER TABLE tmp ADD constraint chk_cns (fld2 > 10) ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege.
• Ownership of the table.
• ALTER privilege on the table.

REORG STORAGE_ATTRIBUTES

In V11.5 and later, the REORG option forces table record and index reorganization. It can change the definitions by applying the storage attributes specified.

ALTER TABLE [tbl] REORG STORAGE_ATTRIBUTES '[attribs]'

The specified storage attributes affect the organization of the table and the use of Hot Alter Table (see below). The following storage attributes are valid (anything else is ignored):

• hotalter
• nohotalter
• huge
• nohuge
• rowid_fld
• norowid_fld
• recbyt_idx
• norecbyt_idx

The REORG option is helpful in the rare cases when the index becomes out-of-sync. It is recommended when the Hot Alter Table feature (the default for SQL tables) has been used on a table many times, perform record reorganization to bring all records to the current status.

Hot Alter Table

FairCom's Hot Alter Table feature can dramatically increase efficiency and minimize downtime when rolling out changes to your application data schema. Hot Alter Table allows you to provide "rolling upgrades" to your product deployed in the field. Schema changes no longer cause your customer to go through a lengthy file migration process, which previously could require many hours of downtime. Instead, Hot Alter Table upgrades records upon read or write.

The SQL API supports the Hot Alter Table feature:

1. CREATE TABLE uses storage attributes to explicitly determine if Hot Alter Table is supported or not. If not explicitly set, the default is to disable it. The syntax is:

create table.... storage_attributes 'nohotalter' to not allow hot alter
create table.... storage_attributes 'hotalter' to allow hot alter

1. A SQL_OPTION has been added to ctsrvr.cfg to change the CREATE TABLE default to enable 'hotalter' (if not specifically disabled using the STORAGE_ATTRIBUTE 'nohotalter') on table creation:

SQL_OPTION HOT_ALTER_TABLE

Calls to Alter Table will automatically always attempt to use Hot Alter Table for tables created with the support turned on.

1. After a table is created that supports Hot Alter Table, a SQL Alter Table will perform Hot Alter Table if possible, and if not possible it will perform regular Alter Table.

For example, adding a field to a table does use Hot Alter Table:

ALTER TABLE mytable ADD (newfield VARCHAR(100));

1. The following ALTER TABLE syntax has been added:

ALTER TABLE [tbl] REORG - Forces table record and index reorganization with no definition changes.

ALTER VIEW

Description

Creates a view with the specified name on existing tables and/or views.

Syntax

ALTER VIEW [ owner_name. ] view_name

[ ( column_name, column_name, ... ) ]

AS [ ( ] query_expression [ ) ]

[ WITH CHECK OPTION ] ;

Notes

• ALTER VIEW has the same syntax as CREATE VIEW. ALTER VIEW does not modify an existing view, but rather replaces the existing view with a complete new definition. However, it retains the permissions from the existing view for the new view. It is best practice, therefore, to use a script file to create a view that may be modified in the future. The script can then be modified and used with ALTER VIEW to effect the change without needing to reenter the permissions of the view.
• The owner_name is made the owner of the created view.
• The column names specified for the view are optional and provide an alias for the columns selected by the query specification. If the column names are not specified then the view will be created with the same column names as the tables and/or views it is based on.
• A view is deletable if deleting rows from that view is allowed. For a view to be deletable, the view definition has to satisfy the following conditions:
  • The first FROM clause contains only one table reference or one view reference.
  • There are no aggregate functions, DISTINCT clause, GROUP BY or HAVING clause in the view definition.
  • If the first FROM clause contains a view reference, then the view referred to is deletable.
• A view is updatable if updating rows from that view is allowed. For a view to be updatable, the view has to satisfy the following conditions:
  • The view is deletable (That is, it satisfies all the conditions specified above for deletability).
  • All the select expressions in the first SELECT clause of the view definition are simple column references.
  • If the first FROM clause contains a view reference, then the view referred to is updatable.
• A view is insertable if inserting rows into that view is allowed. For a view to be insertable, the view has to satisfy the following conditions:
  • The view is updatable (That is, it satisfies all the conditions specified above for updatability).
  • If the first FROM clause contains a table reference, then all NOT NULL columns of the table are selected in the first SELECT clause of the view definition.
  • If the first FROM clause contains a view reference, then the view referred to is insertable.
• The WITH CHECK OPTION clause can be specified only if the view is updatable.
• If WITH CHECK OPTION clause is specified when defining a view, then during any update or insert of a row on this view, it is checked that the updated/inserted row satisfies the view definition (That is, the row is selectable using the view).

Examples

ALTER VIEW ne_customers AS

SELECT cust_no, name, street, city, state, zip

FROM customer

WHERE state IN ('NH', 'MA', 'NY', 'VT')

WITH CHECK OPTION ;

ALTER VIEW order_count (cust_number, norders) AS

SELECT cust_no, COUNT(*)

FROM orders

GROUP BY cust_no;

Authorization

The user executing this statement must have the following privileges:

• DBA or RESOURCE privilege.
• SELECT privilege on all the tables/views referred to in the view definition.

If owner_name is specified and is different from the name of the user executing the statement, then the user must have DBA privilege.

CALL

Description

Invokes a built-in FairCom DB SQL procedure or Java stored procedure (JSP).

Syntax

[ ? = ] CALL proc_name([parameter][, ... ]);

Arguments

[ ? = ]

A parameter marker for the return value of the procedure. Programs must determine if the procedure returns a value and use the parameter marker as a placeholder for a variable that will receive the return value.

CALL proc_name

The name of the procedure to invoke.

parameter

Literal or variable values to pass to the procedure.

Example

The following example shows invocation of the SQLPROC built-in procedure from interactive FairCom DB SQL. It invokes SQLPROC to retrieve information about another built-in procedure, SQLTABLES:

CALL SQLPROC('',0,'admin',6,'sqltables',9); -- specific procedure

PROCEDURE_QUALIFIER PROCEDURE_OWNER PROCEDURE_NAME NUM_INPU

------------------- --------------- -------------- --------

NUM_OUTP NUM_RESU REMARKS PROCEDUR

-------- -------- ------- ---------

admin sqltables 6 0

1 Returns info about a table 1 1 record returned

Authorization

• Users must have the DBA or EXECUTE privilege to invoke a stored procedure.
• Users invoking a stored procedure do not need privileges to database objects accessed by the procedure. When a user executes a stored procedure, FairCom DB SQL checks the privileges of the procedure owner, not the procedure user, on any objects that the procedure accesses. This enables a user to execute a procedure successfully even when that user does not have the privileges to directly access objects used by the procedure.

COMMIT WORK

Description

Used to commit a transaction explicitly after executing one or more FairCom DB SQL statements. Committing a transaction makes permanent any changes made by the FairCom DB SQL statements.

Syntax

COMMIT [ WORK ] ;

Notes

• The set of FairCom DB SQL statements executed prior to executing COMMIT WORK statement are executed as one atomic transaction that is recoverable, serializable and durable.
• On a system failure and/or the execution of the ROLLBACK WORK, the transaction is marked for abortion and the database is rolled back to its initial state.
• A commit operation makes any database modifications made by that transaction permanent.
• Once a commit operation is executed the database modifications cannot be rolled back.
• Once a commit operation is executed the transaction modifications are guaranteed to be durable irrespective of any transient system failures.
• The atomicity applies only to the database modification and not to any direct I/O performed to devices such as the terminal, printer and OS files by the application code.
• A commit operation releases any locks implicitly or explicitly acquired by the transaction on the database.
• Under the following exceptional circumstances a commit or a commit to a savepoint may fail causing all changes to be rolled back:
  • Lock timeout errors
  • Hardware errors
  • Software errors
  • Storage space failures

CONNECT

Description

Establishes a connection to a database. Optionally, the CONNECT statement can also specify a name for the connection and a user-name/password for authentication.

Syntax

CONNECT TO connect_string

[AS connection_name]

[USER user_name]

[USING password]

connect_string::

{ DEFAULT

| db_name

| port@host_name:db_name }

Arguments

Arguments to CONNECT must either be string literals enclosed in quotation marks or character-string host variables.

connect_string

The string that specifies the database to connect to. If the CONNECT statement specifies DEFAULT, FairCom DB SQL tries to connect to the environment-defined database, if any. (How you define the default database varies between operating systems. On Unix, the value of the DB_NAME environment variable specifies the default connect string.)

The connect string can be a simple database name or a complete connect string. A complete connect string has the following components:

connection_name

The name for the connection for use in DISCONNECT and SET CONNECTION statements. If the CONNECT statement omits a connection name, the default is the name of the database. Connection names must be unique.

user_name

User name for authentication of the connection. FairCom DB SQL verifies the user name against a corresponding password before it connects to the database. If omitted, the default value depends on the environment. (On UNIX, the value of the DH_USER environment variable specifies the default user name. If DH_USER is not set, the value of the USER environment variable specifies the default user name.)

password

Password for authentication of the connection. FairCom DB SQL verifies the password against a corresponding user name before it connects to the database. If omitted, the default value depends on the environment.

(On Unix, the value of the DH_PASSWD environment variable specifies the default password.)

Notes

• Arguments to CONNECT must either be string literals enclosed in quotation marks or character-string host variables.
• An application can connect to more than one database at a time, with a maximum of 10 connections. However, the application can actually gain access to only one database at a time. The database name specified in the CONNECT statement becomes the active one.
• If an application executes an FairCom DB SQL statement before connecting to a database, FairCom DB SQLtries to connect to the database specified through the DB_NAME environment variable, if that environment variable is defined. If the connection is successful, the FairCom DB SQL statement executes on that database.

Authorization

None.

Examples

The following examples illustrate the CONNECT statement:

• The first statement shown connects to the “salesdb” database on the local system.
• The second statement connects to the “custdb” database on the local system using a network protocol.
• The third statement connects to the “custdb” database on the local mode, using the local pipe IPC protocol.
• The fourth statement connects to the environment-defined database by default (if any).

CONNECT TO 'salesdb' AS 'sales_conn';

CONNECT TO '6597@localhost:custdb' AS 'cust_conn';

CONNECT TO '6597@testbox:custdb' AS 'cust_conn';

CONNECT TO DEFAULT;

CREATE FUNCTION

Description

Creates a User Defined Scalar Function (UDF) for the specified table. User Defined Scalar Functions are an extension to the existing built-in scalar functions and return a single value each time one is invoked. These functions can be used in queries in the same way that system defined scalar functions are used. UDFs are written with Java source code. For more detail on creating and using triggers, see the FairCom DB SQL Guide to Using Stored Procedures and Triggers and User Defined Functions.

Syntax

CREATE FUNCTION [ IF NOT EXISTS ] [ owner_name.]function_name

( [parameter_decl , ... ] )

RETURNS (data_type)

[ IMPORT

java_import_clause ]

BEGIN

java_snippet

END

parameter_decl ::

[ IN ] parameter_name data_type Arguments
 

IF NOT EXISTS

This argument avoids failure by creating the function only if a function of the same name does not already exist.

owner_name

Specifies the owner of the user defined function. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.

function_name

Names the user defined function. DROP FUNCTION statements specify the function_name defined here. FairCom DB SQL also uses function_name in the name of the Java class it creates from the Java snippet.

RETURNS
data_type

The return value of the function as defined by the data_type declaration.

IMPORT
java_import_clause

Specifies standard Java classes to import. The IMPORT keyword must be upper case and on a separate line.

BEGIN
java_snippet

END

The body of the function. The body contains the Java source code that implements the user defined function. The Java statements become a method in a class that FairCom DB SQL creates and submits to the Java compiler.

The BEGIN and END keywords must be upper case and on separate lines.

Notes

• User Defined Scalar Function are a type of FairCom DB SQL expression that return a value based on the argument(s) supplied. User Defined Scalar Function are invoked in exactly the same manner as built in scalar functions.
• User Defined Scalar Functions can be used in the SELECT list or in the WHERE clause. They can be used as parameters of other scalar functions or in any expression. The parameter passed to a user defined scalar function can be a literal, field reference or any expression.

Example

The following example creates a User Defined Scalar Function named ‘str_cat’ that takes two input arguments and returns the concatenated string.

CREATE FUNCTION str_cat(IN org_string VARCHAR(20), IN string_to_concat VARCHAR(20))

RETURNS VARCHAR(40)

IMPORT

import java.math.*;

BEGIN

String new_str = org_string + string_to_concat ;

return new_str;

END

Authorization

Users executing CREATE FUNCTION must have the DBA privilege or RESOURCE privilege. The owner or users with the DBA privilege can execute or drop any User Defined Scalar Function, and grant the EXECUTE privilege to other users.

When a User Defined Scalar Function is executed on behalf of a user with EXECUTE privilege on that User Defined Scalar Function, for the objects that are accessed by the User Defined Scalar Function, the User Defined Scalar Function owner’s privileges are checked and not the user’s. This enables a user to execute a User Defined Scalar Function successfully even when he does not have the privileges to directly access the objects that are accessed by the User Defined Scalar Function, so long as he has EXECUTE privilege on the User Defined Scalar Function.

CREATE INDEX

Description

Creates an index on the specified table using the specified columns of the table. An index improves the performance of FairCom DB SQL operations whose predicates are based on the indexed column. However, an index slows the performance of INSERT, DELETE, and UPDATE operations.

Syntax

CREATE [ FULLTEXT | UNIQUE ] INDEX [ IF NOT EXISTS ] index_name

ON table_name

( {column_name [ASC | DESC]} [, ... ])

[ STORAGE_ATTRIBUTES storage_attributes | fulltext_options ];

storage_attributes ::

'PARTITION=rule' | 'PARTITION'

rule ::

conditional expression referencing columns evaluating to a numeric value.

fulltext_options ::

[ TOKENIZER tokenizer_specs ]

[ MAX_TOKENSIZE number ]

[ UPDATE_MODE upd_option ]

[ TOKENIZER_OPTIONS 'parameters' ]

tokenizer_specs ::

ICU | SIMPLE | PORTER | dllname_for_custom
 

upd_option ::

ASYNC | IMMEDIATE | ATCOMMIT
 

Arguments

UNIQUE

A UNIQUE index will not allow the table to contain any rows with duplicate column values for the set of columns specified for that index.

IF NOT EXISTS

This argument avoids failure by creating the index only if an index of the same name does not already exist.

index_name

The name of the index has to be unique for the specified table.

table_name

The name of the table on which the index is being built.

column_name [ , ... ]

The columns on which searches and retrievals will be ordered. These columns are called the index key. When more than one column is specified in the CREATE INDEX statement a concatenated index is created.

Note: Fields of type LVARBINARY and LVARCHAR cannot be included in a SQL index due to size.

ASC | DESC

The index can be ordered as either ascending (ASC) or descending (DESC) on each column of the concatenated index. The default is ASC.

Full-Text Indexes

Similar to a traditional b-tree index over a FairCom DB data file, you may now define a Full-Text Index (FTI) by specifying which character-type fields to include in this search index. An additional set of FTI files will be maintained on disk. An FTI can be actively maintained in “real-time” or as a deferred operation in the background.

TOKENIZER - This is the algorithm used to create the text tokens to be indexed.

• SIMPLE - uses whitespace and punctuation to delimit case-insensitive terms encoded as ASCII characters.
• PORTER - Porter Stemming algorithm for the English language treats all words as lowercase and reduces regular nouns and verbs to their root form. For example, the phrase, "Americans love their baseball" is reduced to the following stemmed terms: "american", "love", "their", and "baseball". These stemmed terms match the following words: "american", "americans", "Americans", "aMERIcans", "love", "loves", "loved", "loving", "their", "baseball", "baseballs", "bAsEbAlls", and so forth. It does not match uncommon stemming rules, such as matching "him" and "hr" to "their' or matching "loves" to "love". See https://tartarus.org/martin/PorterStemmer/
• ICU - uses whitespace and punctuation to delimit case-insensitive terms encoded as Unicode characters.
• Shared object name of a custom-built tokenizer. Contact FairCom for information regarding a tokenizer SDK.

Default tokenizer:

• SIMPLE for ASCII server
• ICU for Unicode server

TOKENIZER_OPTIONS - options to pass into the specified tokenizer algorithm. Generally, this is intended for custom tokenizers.

MAX_TOKENSIZE - The largest tex token to be indexed. Default is 45 characters.

UPDATE_MODE - Three modes are available

ASYNC (default)

• IMMEDIATE
• ATCOMMIT

Example

Full-Text Index Restrictions

A full-text search can not be created as a UNIQUE index. Error 20006 is returned.

Columns can not be specified as ASC or DESC in creating a full text index.

A full-text index cannot also be created as a partitioned file index.

Storage Attributes

A quoted string that specifies index attributes that are specific to FairCom DB SQL indexes. FairCom DB SQL interprets the following attribute strings:

• ''PARTITION=<rule>' - Creates the index as the partition index to the table enabling multiple table partitions. This option can be specified after a table has been created, and the table will be rebuilt according to the partition rule in effect.
  
  <rule> is a FairCom DB conditional expression evaluating to a numeric value.
• ''PARTITION' - Creates the index as the partition index to the table enabling multiple table partitions. This option can be specified after a table has been created, and the table will be rebuilt according to the partition rule in effect. (This type of partitioned file currently require a server with your partition rules included at compile time. See the FairCom DB Programmer's Reference Guide for details.)

To combine STORAGE_ATTRIBUTE options, separate them with a semicolon (;).

Example

CREATE UNIQUE INDEX postdate_idx ON customer (invoice_date) STORAGE_ATTRIBUTES 'partition' ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege.
• Ownership of the index.
• INDEX privilege on the table.
• EXCLUSIVE file access is required for this operation.

SQL full-text search examples using PORTER English stemming tokenizer

The PORTER stemming tokenizer identifies each word in the search using whitespace and punctuation. Then, regardless of case, the tokenizer finds all matching words by reducing each word in the search to its stem by removing trailing s, er, ing, and ed.

A search string identifies each word using whitespace and/or punctuation and may use double quotes to identify a word or a phrase. Phrases contain two or more words within double quotes and may include whitespace between words. Punctuation, such as .!>, is ignored in the search string when it is not a full-text search operator. Reserved search operators include - " ^ * ( ) NEAR NEAR/n AND OR NOT; However, * ( ) OR NOT are not supported and will return an error when used.

/* =====================================================================

* Full-text search demo using the PORTER tokenizer.

* =====================================================================

*/

/* Create the testsearch table. */

CREATE TABLE testsearch

(

name VARCHAR(30)

, description VARCHAR(500)

)

STORAGE_ATTRIBUTES 'hotAlter; huge; rowid_fld; noRecbyt_idx;'

;

/* Create full-text index on the description field of the testsearch table. Use the PORTER English word stem tokenizer to do case insensitive matching of common word forms */

CREATE FULLTEXT INDEX testsearch_description_porter_fts

ON testsearch( description )

TOKENIZER PORTER

UPDATE_MODE ASYNC

;

/* Insert a record into the testsearch table. */

INSERT INTO testsearch

VALUES('Mike','Americans --love-- their __BASEball__ and <FOOTball>!')

;

/* Look at the record inserted into the testsearch table. */

SELECT description FROM testsearch;

/* =====================================================================

* The following queries find the inserted record

* by searching for words and phrases in the description field:

* 'Americans --love-- their __BASEball__ and <FOOTball>!'.

*

* The PORTER tokenizer reduces the text to

* 'american love their baseball and football'.

*

* The tokenizer identifies each word using whitespace and/or punctuation.

* The tokenizer is case insensitive.

* The tokenizer reduces each word to its stem

* by removing trailing s, er, ing, and ed.

* The tokenizer finds all words matching the stem.

* These words match each other -- even when they are invalid words:

* baseball baseballs baseballing baseballer baseballed

*

* A search string identifies each word using whitespace and/or punctuation.

* A search string may use double quotes to identify a word or a phrase.

* A phrase contains two or more words within double quotes.

* A search string may include any amount of whitespace between words.

* A search string may include

* Punctuation, such as .!>, is ignored in the search string

* when it is not a full-text search operator.

* Reserved search operators include - " ^ * ( ) NEAR NEAR/n AND OR NOT

* The following search operators are unsupported: * ( ) OR NOT

* The server returns an error when you use an unsupported search operator.

* =====================================================================

*/

/* WORD SEARCH: Find records in the testsearch table where the description column contains the word 'baseball' anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'baseball';

/* VERB STEM SEARCH: Find records in the testsearch table where the description column contains common English conjugations of verbs, such as 'loving', 'love', 'loved', and 'loves', anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'loving';

/* NOUN STEM SEARCH: Find records in the testsearch table where the description column contains English singular and plural forms of nouns, such as 'american' and 'americans', anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'american';

/* CASE INSENSITIVE SEARCH: Find records in the testsearch table where the description column contains common forms of the word 'baseBALLs', such as 'baseball' and 'Baseballs' anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'baseBALLs';

/* FIRST WORD SEARCH: Find records in the testsearch table where the description column contains the case-insensitive word 'americans' as the first word in the description field. */

SELECT description FROM testsearch WHERE description MATCH '^americans';

/* FIRST PHRASE SEARCH: Find records in the testsearch table where the description column contains the phrase "americans love" at the begining of the text in the description field. */

SELECT description FROM testsearch WHERE description MATCH ' ^"americans love" ';

/* MULTI-WORD SEARCH: Find records in the testsearch table where the description column contains the words 'baseball' and 'love' in any order anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'baseball love';

/* MULTI-WORD SEARCH ACROSS WHITESPACE: Find records in the testsearch table where the description column contains the words 'baseball' and 'love' in any order anywhere in the description field while ignoring case and whitespace. */

SELECT description FROM testsearch WHERE description MATCH ' Baseball >>> LOVE! ';

/* PHRASE SEARCH: Find records in the testsearch table where the description column contains the case-insensitive phrase "love their" anywhere in the description field. The words 'love" and 'their" must follow each other with no intervening words in the description field. */

SELECT description FROM testsearch WHERE description MATCH ' "love their" ';

/* PHRASE SEARCH ACROSS WHITESPACE: Find records in the testsearch table where the description column contains the case-insensitive phrase "LOVE their" anywhere in the description field. The case-insensitive words 'LOVE" and 'theIR" must follow each other with no intervening words, but any amount of whitespace and punctuation may occur between these words in the description field. */

SELECT description FROM testsearch WHERE description MATCH ' "LOVE theIR" ';

/* STEMMED MULTI-WORD SEARCH: Find records in the testsearch table where the description column contains several words anywhere in the description field, such as 'baseBALLs' and 'american'. The PORTER tokenizer matches each word that has the same English stemming. */

SELECT description FROM testsearch WHERE description MATCH 'Loved their american baseBALLs.';

/* STEMMED PHRASE SEARCH: Find records in the testsearch table where the description column contains common forms of each word in the phrase "and football" anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH ' "and footballs" ';

/* STEMMED WORD SEARCH ACROSS INCORRECT WORD FORMS: Find records in the testsearch table where the description column contains correct and incorrect forms of words, such as 'baseballing', 'baseballer', 'baseballed'. The PORTER stemmer applies its rules to every word -- even when the rule is incorrect in English. */

SELECT description FROM testsearch WHERE description MATCH ' american americans americaning americaner americaned baseball baseballs baseballing baseballer baseballed their theirs theiring theired "and" "ands" "anding" "anded" ';

/* STEMMED FIRST WORD SEARCH: Find records in the testsearch table where the description column contain common forms of the word 'americaning' as the first word in the description field. */

SELECT description FROM testsearch WHERE description MATCH '^americaning';

/* STEMMED FIRST PHRASE SEARCH: Find records in the testsearch table where the description column contains common forms of the words in the phrase "american loving" at the begining of the text in the description field. */

SELECT description FROM testsearch WHERE description MATCH ' ^"american loving" ';

/* NEAR SEARCH: Find records in the testsearch table where the description column contains the words 'Americans' and 'baseball' with no more than ten words between each other anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'Americans NEAR football';

/* NEAR PHRASE SEARCH: Find records in the testsearch table where the description column contains the words 'their' and 'baseballs' with no words between each other. This is the same as using a phrase search. */

SELECT description FROM testsearch WHERE description MATCH 'their NEAR/0 baseball';

/* NEAR N SEARCH: Find records in the testsearch table where the description column contains the words 'love' and 'baseball' with no more than 1 word between each other. This pattern may occur anywhere in the description field with any amount of whitespace and punctuation between the words. */

SELECT description FROM testsearch WHERE description MATCH ' baseball NEAR/1 football ';

/* NOT WORD SEARCH: Find records in the testsearch table where the description column contains the words 'love' and 'baseball' but not 'karate'. NOTE: The minus operator is the NOT operator. It must be adjacent to the word with no intervening whitespace. You must also include a term in the search phrase before the NOT operator. */

SELECT description FROM testsearch WHERE description MATCH 'love baseball -karate';

/* NOT PHRASE SEARCH: Find records in the testsearch table where the description column contains the word 'baseball' but not the phrase "hate karate" and not the word 'soccer'. NOTE: The minus operator is the NOT operator. It must be adjacent to the word with no intervening whitespace. You must also include a term in the search phrase before the NOT operator. */

SELECT description FROM testsearch WHERE description MATCH 'baseball -"hate karate" -soccer';

/* STEMMED NEAR SEARCH: Find records in the testsearch table where the description column contains case-insensitive common forms of the words 'American' and 'baseball' with no more than ten words between each other anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'americaner NEAR baseballs ';

/* STEMMED PHRASE SEARCH: Find records in the testsearch table where the description column contains case-insensitive common forms of the words 'their' and 'baseballs' with no words between each other anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'their NEAR/0 baseBALLS ';

/* STEMMED NEAR N SEARCH: Find records in the testsearch table where the description column contains case-insensitive common forms of the words 'baseball' and 'football' with no more than 1 word between each other anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'baseballing NEAR/1 footballed ';

/* STEMMED NOT WORD SEARCH: Find records in the testsearch table where the description column contains case-insensitive common forms of the words 'love' and 'baseball' but not 'karate'. NOTE: The minus operator must be adjacent to the word or phrase with no intervening whitespace. You must also have a term in the search phrase before the minus - operator. */

SELECT description FROM testsearch WHERE description MATCH 'loves baseballing -karate';

/* STEMMED NOT PHRASE SEARCH: Find records in the testsearch table where the description column contains case-insensitive common forms of the word 'baseball' but not the phrase "hate karate". */

SELECT description FROM testsearch WHERE description MATCH 'loving baseballs -"hating karate" ';

/* STEMMED NOT PHRASE SEARCH: Find records in the testsearch table where the description column contains case-insensitive common forms of the word 'love' but not case-insensitive common forms of the words in the phrase "Americans football". */

SELECT description FROM testsearch WHERE description MATCH 'Loved -"ameRICAN footBALLs" ';

/* Remove the testsearch table. */

DROP TABLE testsearch;

SQL full-text search examples using SIMPLE tokenizer

The SIMPLE tokenizer identifies each word using whitespace and/or punctuation. Then regardless of case, the tokenizer finds all matching words in the search. tokenizer is case insensitive.

A search string identifies each word using whitespace and/or punctuation and may use double quotes to identify a word or a phrase. Phrases contain two or more words within double quotes and may include whitespace between words. Punctuation, such as .!>, is ignored in the search string when it is not a full-text search operator. Reserved search operators include - " ^ * ( ) NEAR NEAR/n AND OR NOT; However, * ( ) OR NOT are not supported and will return an error when used.

/* =====================================================================

* Full-text search demo using the SIMPLE tokenizer.

* =====================================================================

*/

/* Create the testsearch table. */

CREATE TABLE testsearch

(

name VARCHAR(30)

, description VARCHAR(500)

)

STORAGE_ATTRIBUTES 'hotAlter; huge; rowid_fld; noRecbyt_idx;'

;

/* Create full-text index on the description field of the testsearch table. Use the PORTER English word stem tokenizer to do case insensitive matching of common word forms */

CREATE FULLTEXT INDEX testsearch_description_simple_fts

ON testsearch( description )

UPDATE_MODE ASYNC

;

/* Insert a record into the testsearch table. */

INSERT INTO testsearch

VALUES('Mike','Americans --love-- their __BASEball__ and <FOOTball>!')

;

/* Look at the record inserted into the testsearch table. */

SELECT description FROM testsearch;

/* =====================================================================

* The following queries find the inserted record

* by searching for words and phrases in the description field:

* 'Americans --love-- their __BASEball__ and <FOOTball>!'.

*

* The SIMPLE tokenizer reduces the text to

* 'americans love their baseball and football'.

*

* The tokenizer identifies each word using whitespace and/or punctuation.

* The tokenizer is case insensitive.

* A search string identifies each word using whitespace and/or punctuation.

* A search string may use double quotes to identify a word or a phrase.

* A phrase contains two or more words within double quotes.

* A search string may include any amount of whitespace between words.

* A search string may include

* Punctuation, such as .!>, is ignored in the search string

* when it is not a full-text search operator.

* Reserved search operators include - " ^ * ( ) NEAR NEAR/n AND OR NOT

* The following search operators are unsupported: * ( ) OR NOT

* The server returns an error when you use an unsupported search operator.

* =====================================================================

*/

/* WORD SEARCH: Find records in the testsearch table where the description column contains the word 'baseball' anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'baseball';

/* CASE INSENSITIVE SEARCH: Find records in the testsearch table where the description column contains case-insensitive forms of the word 'americans', such as 'Americans' and 'AMERIcans' anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'americans';

/* FIRST WORD SEARCH: Find records in the testsearch table where the description column contains the word 'americans' as the first word in the description field. */

SELECT description FROM testsearch WHERE description MATCH '^americans';

/* FIRST PHRASE SEARCH: Find records in the testsearch table where the description column contains the phrase "americans love" at the begining of the text in the description field. */

SELECT description FROM testsearch WHERE description MATCH ' ^"americans love" ';

/* MULTI-WORD SEARCH: Find records in the testsearch table where the description column contains the words 'baseball' and 'love' in any order anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'baseball love';

/* MULTI-WORD SEARCH ACROSS WHITESPACE: Find records in the testsearch table where the description column contains the words 'baseball' and 'love' in any order anywhere in the description field. NOTE: Any amount of whitespace may occur between these words in the search text. Punctuation, such as .!>, is also ignored in the search string when it is not a full-text search operator. */

SELECT description FROM testsearch WHERE description MATCH ' <Baseball> >>> .LOVE. !!! ';

/* PHRASE SEARCH: Find records in the testsearch table where the description column contains the case-insensitive phrase "love their" anywhere in the description field. The words 'love" and 'their" must follow each other with no intervening words in the description field. */

SELECT description FROM testsearch WHERE description MATCH ' "love their" ';

/* PHRASE SEARCH ACROSS WHITESPACE: Find records in the testsearch table where the description column contains the case-insensitive phrase "LOVE their" anywhere in the description field. The case-insensitive words 'LOVE" and 'theIR" must follow each other with no intervening words, but any amount of whitespace and unreserved punctuation may occur between these words in the description field. */

SELECT description FROM testsearch WHERE description MATCH ' "LOVE! theIR" ';

/* NEAR SEARCH: Find records in the testsearch table where the description column contains the words 'Americans' and 'baseball' with no more than ten words between each other anywhere in the description field. */

SELECT description FROM testsearch WHERE description MATCH 'Americans NEAR football ';

/* NEAR PHRASE SEARCH: Find records in the testsearch table where the description column contains the words 'their' and 'baseballs' with no words between each other. This is the same as using a phrase search. */

SELECT description FROM testsearch WHERE description MATCH 'their NEAR/0 baseball';

/* NEAR N SEARCH: Find records in the testsearch table where the description column contains the words 'love' and 'baseball' with no more than 1 word between each other. This pattern may occur anywhere in the description field with any amount of whitespace and punctuation between the words. */

SELECT description FROM testsearch WHERE description MATCH ' baseball NEAR/1 football ';

/* NOT WORD SEARCH: Find records in the testsearch table where the description column contains the words 'love' and 'baseball' but not 'karate'. NOTE: The minus - operator is the NOT operator. It must be adjacent to the word with no intervening whitespace. You must also include at least one term in the search phrase before the - operator. */

SELECT description FROM testsearch WHERE description MATCH 'love baseball -karate';

/* NOT PHRASE SEARCH: Find records in the testsearch table where the description column contains the word 'baseball' but not the phrase "hate karate" and not the word 'soccer'. NOTE: The minus operator is the NOT operator. It must be adjacent to the word with no intervening whitespace. You must also include a term in the search phrase before the NOT operator. */

SELECT description FROM testsearch WHERE description MATCH 'baseball -"hate karate" -soccer';

/* AND SEARCH: Find records in the testsearch table where the description column contains the word 'baseball' and 'loved'. NOTE: The AND operator is optional. The server automatically adds it between each term in the search expression. The following two queries are functionally identical. */

SELECT description FROM testsearch WHERE description MATCH ' love AND "their baseball" ';

SELECT description FROM testsearch WHERE description MATCH ' love "their baseball" ';

/* SEARCH for reserved words "and", "or", and "near": Find records in the testsearch table where the description column contains the words "baseball" and "and" but not "or" and "near". NOTE: enclose a reserved word in double quotes to treat it as a word instead of an operator. */

SELECT description FROM testsearch WHERE description MATCH ' love "and" -"or" -"near" ';

/* =====================================================================

* The following queries do NOT find the inserted record

* because they do not match the text:

* 'Americans --love-- their __BASEball__ and <FOOTball>!'

* =====================================================================

*/

/* VERB STEM SEARCH: This query returns no results because the SIMPLE tokenizer does not match common English conjugations of verbs, such as 'loving', 'love', 'loved', and 'loves'. The PORTER tokenizer does. */

SELECT description FROM testsearch WHERE description MATCH 'loving';

/* NOUN STEM SEARCH: This query returns no results because the SIMPLE tokenizer does not match common English singular and plural forms of nouns, such as 'american' and 'americans'. The PORTER tokenizer does. */

SELECT description FROM testsearch WHERE description MATCH 'american';

/* FIRST WORD SEARCH: This query returns no results because 'love' is not the first word in the description. */

SELECT description FROM testsearch WHERE description MATCH '^love';

/* STEMMED MULTI-WORD SEARCH: This query returns no results because the SIMPLE tokenizer does not match word stems like the PORTER tokenizer does. */

SELECT description FROM testsearch WHERE description MATCH 'Baseballs american';

/* STEMMED NEAR SEARCH: This query returns no results because the SIMPLE tokenizer does not match word stems like the PORTER tokenizer does. */

SELECT description FROM testsearch WHERE description MATCH 'American NEAR baseballs ';

/* STEMMED PHRASE SEARCH: This query returns no results because the SIMPLE tokenizer does not match word stems like the PORTER tokenizer does. */

SELECT description FROM testsearch WHERE description MATCH 'their NEAR/0 baseballs ';

/* STEMMED NEAR N SEARCH: This query returns no results because the SIMPLE tokenizer does not match word stems like the PORTER tokenizer does. */

SELECT description FROM testsearch WHERE description MATCH 'loves NEAR/1 baseballs ';

/* Remove the testsearch table. */

DROP TABLE testsearch;

CREATE SEQUENCE

Syntax

CREATE SEQUENCE [owner_name.]sequence_name
[ START WITH start_value ]
[ INCREMENT BY increment_value ]
[ MAXVALUE max_value | NOMAXVALUE ]
[ MINVALUE min_value | NOMINVALUE ]
[ CYCLE | NOCYCLE ]

Description

The CREATE SEQUENCE command creates a new sequence in the current database.

• sequence_name is the name that is to be assigned to the sequence. The sequence_name in a database must be unique.
• start_value specifies a starting value for the sequence. The value of start_value must be between -9223372036854775808 and 9223372036854775807. The default is 1.
• increment_value specifies the value by which the sequence is incremented when sequence.NEXTVAL is called. Increment by can be positive or negative. The default is 1.
• max_value specifies the maximum value that can be returned by the sequence. If the sequence is a cycling sequence, then the next value after the max_value will be min_value for an ascending sequence and the next value after min_value will be max_value for an ascending sequence. Note that the value of increment_value may mean that the exact max_value or min_value will never be returned. The default is 9223372036854775807.
• min_value specifies the minimum value that can be returned by the sequence. The default min_value is -9223372036854775808.

When CYCLE is specified, a sequence will wrap from the max_value to the min_value for an ascending sequence or from the min_value to a max_value for a descending sequence. When NOCYCLE (the default) is specified, an error is returned when NEXTVAL (see Sequence Values) would exceed the max_value for an ascending sequence or the min_value for a descending sequence.

Example

The following command creates a sequence called myseq. It starts with a value of 5, increments by 5, and returns a maximum value of 50:

CREATE SEQUENCE myseq START WITH 5 INCREMENT BY 5 MAXVALUE 50;

Return Values

See c-tree Plus Error Codes for a complete listing of valid c-tree Plus error values.

See Also

• Sequence Values (Sequence Values, /doc/sqlref/sql-sequence-values.htm)
• DROP SEQUENCE
• ALTER SEQUENCE

Sequence Values

Sequence values in SQL statements can be referred as follows:

• CURRVAL: Returns the current value of a sequence
• NEXTVAL: Increments the sequence and returns the next value

You must qualify CURRVAL and NEXTVAL with the name of the sequence, for example:

• sequence.CURRVAL
• sequence.NEXTVAL

Example:

select mysequence.nextval;

insert into mytable values (mysequence.nextval, 'aaa');

CREATE PROCEDURE

Description

Creates a stored procedure. Stored procedures contain a Java code “snippet” that is processed by FairCom DB SQL into a Java class definition and stored in the database in text and compiled form. FairCom DB SQL applications invoke stored procedures through the SQL CALL statement or the procedure-calling mechanisms of ODBC and JDBC.

For more detail on creating and using stored procedures, see the FairCom DB SQL Guide to Java Stored Procedures and Triggers.

Syntax

CREATE PROCEDURE [ IF NOT EXISTS ] [ owner_name. ] procname

( [ parameter_decl [ , ... ] ) ]

[ RESULT ( column_name data_type [ , ... ] ) ]

[ IMPORT

java_import_clause ]

BEGIN

java_snippet

END

parameter_decl ::

{ IN | OUT | INOUT } parameter_name data_type

Arguments

IF NOT EXISTS

This argument avoids failure by creating the procedure only if a procedure of the same name does not already exist.

owner_name

Specifies the owner of the procedure. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.

procname

Names the stored procedure. DROP PROCEDURE statements specify the procedure name defined here. c-treeSQL also uses procname in the name of the Java class that it creates from the Java snippet.

IN | OUT | INOUT

Specifies whether the following parameter declaration is input, output, or both:

• Calling applications pass values for input parameters in the CALL statement or CALL escape sequence
• Stored procedures assign values to output parameters as part of their processing
• INOUT parameters both have a value passed in and receive a new value during procedure processing

parameter_name data_type

Names a parameter and associates an FairCom DB SQL data type with it. The data_type must be one of the supported data types described in Data Types.

RESULT ( column_name data_type [ , ... ] )

Specifies columns in the result set the procedure returns. If the CREATE PROCEDURE statement includes this clause, the Java snippet must explicitly insert rows into the result set using the FairCom DB SQL Java class SQLResultSet.

Note that the column_name argument is not used within the stored procedure body. Instead, methods of the FairCom DB SQL Java classes refer to columns in the result set by ordinal number, not by name. (Interactive FairCom DB SQL uses the column names as headers when it displays procedure result sets.)

IMPORT
java_import_clause

Specifies standard Java classes to import. The IMPORT keyword must be upper case and on a separate line.

BEGIN
java_snippet

END

The body of the stored procedure. The body is a sequence of Java statements between the BEGIN and END keywords. The Java statements become a method in a class FairCom DB SQL creates and submits to the Java compiler.

The BEGIN and END keywords must be upper case and on separate lines. You cannot follow the END keyword with a semicolon.

Example

CREATE PROCEDURE new_sal (

IN deptnum INTEGER,

IN pct_incr INTEGER,

)

RESULT (

empname CHAR(20),

oldsal NUMERIC,

newsal NUMERIC

)

BEGIN

StringBuffer ename = new StringBuffer (20) ;

BigDecimal osal = new BigDecimal () ;

BigDecimal nsal = new BigDecimal () ;

SQLCursor empcursor = new SQLCursor (

"SELECT empname, sal, (sal * ( ? /100) + NVL (comm, 0)) total,

FROM emp WHERE deptnum = ? " ) ;

empcursor.setParam (1, pct_incr);

empcursor.setParam (2, deptnum);

empcursor.open () ;

do

{

empcursor.fetch ();

if (empcursor.found ())

{

empcursor.getValue (1, ename);

empcursor.getValue (2, osal);

empcursor.getValue (3, nsal) ;

SQLResultSet.set (1, ename);

SQLResultSet.set (2, osal);

SQLResultSet.set (3, nsal) ;

SQLResultSet.insert ();

}

} while (empcursor.found ()) ;

empcursor.close () ;

END

Authorization

• Users issuing the CREATE PROCEDURE statement must have the DBA privilege or RESOURCE privilege.
• The owner or users with the DBA privilege can execute or drop any stored procedure, and grant the EXECUTE privilege to other users.
• Users must have the DBA or EXECUTE privilege to invoke a stored procedure.
• Users invoking a stored procedure do not need privileges to database objects accessed by the procedure. When a user executes a stored procedure, FairCom DB SQL checks the privileges of the procedure owner, not the procedure user, on any objects that the procedure accesses. This enables a user to execute a procedure successfully even when that user does not have the privileges to directly access objects used by the procedure.

CREATE SYNONYM

Description

Creates a synonym for the table, view or synonym specified. A synonym is an alias that FairCom DB SQL statements can use instead of the name specified when the table, view, or synonym was created.

Syntax

CREATE [PUBLIC] SYNONYM synonym

FOR [owner_name.] { table_name | view_name | synonym } ;

Arguments

PUBLIC

Specifies that the synonym will be public: all users can refer to the name without qualifying it. By default, the synonym is private: other users must qualify the synonym by preceding it with the user name of the user who created it.

Users must have the DBA privilege to create public synonyms.

SYNONYM synonym

Name for the synonym.

FOR [owner_name.] { table_name | view_name | synonym }

Table, view, or synonym for which FairCom DB SQL creates the new synonym.

Example

CREATE SYNONYM customer FOR smith.customer ;

CREATE PUBLIC SYNONYM public_suppliers FOR smith.suppliers ;

Authorization

Users executing CREATE SYNONYM must have the DBA privilege or RESOURCE privilege. Users executing CREATE PUBLIC SYNONYM statement must have the DBA privilege.

CREATE TABLE

Description

Creates a table definition. A table definition consists of a list of column definitions that make up a table row. FairCom DB SQL provides two forms of the CREATE TABLE statement. The first form explicitly specifies column definitions. The second form, with the AS query_expression clause, implicitly defines the columns using the columns in the query expression.

Syntax

CREATE TABLE [ IF NOT EXISTS ] [ owner_name. ] table_name

( column_definition [ , { column_definition | table_constraint } ] ... )

[ STORAGE_ATTRIBUTES 'attributes' ]

;

CREATE TABLE [ IF NOT EXISTS ] [ owner_name. ] table_name

[ ( column_name [NULL | NOT NULL], ... ) ]

[ STORAGE_ATTRIBUTES 'attributes' ]

AS query_expression

;

column_definition ::

column_name data_type

[ DEFAULT { literal | USER | NULL | UID

| SYSDATE | SYSTIME | SYSTIMESTAMP | AUTOTIMESTAMP | AUTOTIMESTAMP_INSERT| AUTOTIMESTAMP_UPDATE | AUTOCHANGEID} ]

| [ IDENTITY [(seed, increment)] | auto_increment [(seed, increment)] ]

[ column_constraint [ column_constraint ... ] ]

Arguments

IF NOT EXISTS

This argument avoids failure by creating the table only if a table of the same name does not already exist.

owner_name

Specifies the owner of the table. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.

table_name

Names the table definition. FairCom DB SQL defines the table in the database named in the last CONNECT statement.

column_name data_type

Names a column and associates a data type with it. The column names specified must be different than other column names in the table definition. The data_type must be one of the supported data types described in Data Types.

DEFAULT

Specifies an explicit default value for a column. The column takes on the value if an INSERT statement does not include a value for the column. If a column definition omits the DEFAULT clause, the default value is NULL.

The DEFAULT clause accepts the following arguments:

IDENTITY

Specifies that the column is an IDENTITY column. An IDENTITY column is one for which the data is not provided by the user but it is automatically generated by the DBMS. There can be only one identity column for a table. A default value cannot be specified for an identity column. IDENTITY can only be specified on columns of type INTEGER, SMALLINT, TINTYINT and BIGINT.

seed

Specifies a value that used for the identity column when the first row is inserted into the table. Specifying a seed value is optional. If a seed value is not specified, then it defaults to 1.

increment

Specifies a value by which the identity value of the previous row is incremented, which is then used for the identity column when the second and subsequent rows are inserted into the table. Specifying the increment value is optional. If it is not specified, then it defaults to 1.

Note: Although both the seed and increment values are optional, they must both be specified or both be left unspecified. It is not possible to specify only one of them.

auto_increment

auto_increment is specified and behaves exactly like IDENTITY, however, values can be automatically overridden from the INSERT statement. When an INSERT statement specifies a value for an IDENTITY column, it inserts the specified value; when the value is omitted, it is generated automatically. This provides flexibility many ORM provides such as SQL Alchemy depend on.

column_constraint

Specifies a constraint that applies while inserting or updating a value in the associated column. For more information, see Column Constraints.

table_constraint

Specifies a constraint that applies while inserting or updating a row in the table. For more information, see Table Constraints.

STORAGE_ATTRIBUTES 'attributes'

Specifies a string describing underlying FairCom DB table storage attributes. For more information see Storage Attributes.

AS query_expression

Specifies a query expression to use for the data types and contents of the columns for the table. The types and lengths of the columns of the query expression result become the types and lengths of the respective columns in the table created. The rows in the resultant set of the query expression are inserted into the table after creating the table. In this form of the CREATE TABLE statement, column names are optional.

If omitted, the names for the table columns are also derived from the query expression. For more information, see Query Expressions.

Examples

In the following example, the user issuing the CREATE TABLE statement must have REFERENCES privilege on the column “itemno” of the table “john.item”.

CREATE TABLE supplier_item (

supp_no INTEGER NOT NULL PRIMARY KEY,

item_no INTEGER NOT NULL REFERENCES john.item (itemno),

qty INTEGER

) ;

The following CREATE TABLE statement explicitly specifies a table owner, admin:

CREATE TABLE admin.account (

account integer,

balance money (12),

info char (84)

) ;

The following example shows the AS query_expression form of CREATE TABLE to create and load a table with a subset of the data in the customer table:

CREATE TABLE admin.dealer (name, street, city, state)

AS

SELECT name, street, city, state

FROM customer

WHERE customer.state IN ('CA','NY', 'TX') ;

The following example includes a NOT NULL column constraint and DEFAULT clauses for column definitions:

CREATE TABLE emp (

empno integer NOT NULL,

deptno integer DEFAULT 10,

join_date date DEFAULT NULL

) ;

Authorization

The user executing this statement must have either DBA or RESOURCE privilege. If the CREATE TABLE statement specifies a foreign key that references a table owned by a different user, the user must have the REFERENCES privilege on the corresponding columns of the referenced table.

The AS query_expression form of CREATE TABLE requires the user to have select privilege on all the tables and views named in the query expression.

See Also:

• Column Constraints
• Table Constraints
• Storage Attributes
• SQL - Create [if not exists] and Drop [if exists]

Column Constraints

Description

Specifies a constraint for a column that restricts the values that the column can store. INSERT, UPDATE, or DELETE statements that violate the constraint fail. FairCom DB SQL returns a constraint violation error with SQLCODE -20116.

Column constraints are similar to table constraints but their definitions are associated with a single column.

Syntax

column_constraint ::

UNIQUE

| NOT NULL [ PRIMARY KEY | UNIQUE ]

| REFERENCES [ owner_name. ] table_name [ ( column_name ) ]

| CHECK ( search_condition )

Arguments

NOT NULL

Restricts values in the column to values that are not null.

NOT NULL PRIMARY KEY

PRIMARY KEY is a valid abbreviation of NOT NULL PRIMARY KEY.

Defines the column as the primary key for the table. There can be only one primary key for a table. A column with the NOT NULL PRIMARY KEY constraint cannot contain null or duplicate values. Other tables can name primary keys as foreign keys in their REFERENCES clauses.

If other tables name primary keys in their REFERENCES clauses, FairCom DB SQL restricts operations on the table containing the primary key:

• DROP TABLE statements that delete the table fail
• DELETE and UPDATE statements that modify values in the column that match a foreign key’s value also fail

The following example shows the creation of a primary key column on the table supplier.

CREATE TABLE supplier (

supp_no INTEGER NOT NULL PRIMARY KEY,

name CHAR (30),

status SMALLINT,

city CHAR (20)

) ;

NOT NULL UNIQUE

Defines the column as a unique key that cannot contain null or duplicate values. Columns with NOT NULL UNIQUE constraints defined for them are also called candidate keys.

Other tables can name unique keys in their REFERENCES clauses. If they do, FairCom DB SQL restricts operations on the table containing the unique key:

• DROP TABLE statements that delete the table fail
• DELETE and UPDATE statements that modify values in the column that match a foreign key’s value also fail

The following example creates a NOT NULL UNIQUE constraint to define the column ss_no as a unique key for the table employee:

CREATE TABLE employee (

empno INTEGER NOT NULL PRIMARY KEY,

ss_no INTEGER NOT NULL UNIQUE,

ename CHAR (19),

sal NUMERIC (10, 2),

deptno INTEGER NOT NULL

) ;

REFERENCES table_name [ (column_name) ]

Defines the column as a foreign key and specifies a matching primary or unique key in another table. The REFERENCES clause names the matching primary or unique key.

A foreign key and its matching primary or unique key specify a referential constraint: A value stored in the foreign key must either be null or be equal to some value in the matching unique or primary key.

You can omit the column_name argument if the table specified in the REFERENCES clause has a primary key and you want the primary key to be the matching key for the constraint.

The following example defines order_item.orditem_order_no as a foreign key that references the primary key orders.order_no.

CREATE TABLE orders (

order_no INTEGER NOT NULL PRIMARY KEY,

order_date DATE

) ;

CREATE TABLE order_item (

orditem_order_no INTEGER REFERENCES orders ( order_no ),

orditem_quantity INTEGER

) ;

Note that the second CREATE TABLE statement in the previous example could have omitted the column name order_no in the REFERENCES clause, since it refers to the primary key of table orders.

CHECK (search_condition)

Specifies a column-level check constraint. FairCom DB SQL restricts the form of the search condition. The search condition must not:

• Refer to any column other than the one with which it is defined
• Contain aggregate functions, subqueries, or parameter references

The following example creates a check constraint:

CREATE TABLE supplier (

supp_no INTEGER NOT NULL,

name CHAR (30),

status SMALLINT,

city CHAR (20) CHECK (supplier.city <> 'MOSCOW')

) ;

Table Constraints

Description

Specifies a constraint for a table that restricts the values that the table can store. INSERT, UPDATE, or DELETE statements that violate the constraint fail. FairCom DB SQL returns a Constraint violation error.

Table constraints have syntax and behavior similar to column constraints. Note the following differences:

• The syntax for table constraints is separated from column definitions by commas.
• Table constraints must follow the definition of columns they refer to.
• Table constraint definitions can include more than one column and FairCom DB SQL evaluates the constraint based on the combination of values stored in all the columns.

Syntax

table_constraint ::

PRIMARY KEY ( column [, ... ] )

| UNIQUE ( column [, ... ] )

| FOREIGN KEY ( column [, ... ] )

REFERENCES [ owner_name. ] table_name [ ( column [, ... ] ) ]

| CHECK ( search_condition )

Arguments

PRIMARY KEY ( column [, ... ] )

Defines the column list as the primary key for the table. There can be at most one primary key for a table.

All the columns that make up a table-level primary key must be defined as NOT NULL, or the CREATE TABLE statement fails. The combination of values in the columns that make up the primary key must be unique for each row in the table.

Other tables can name primary keys in their REFERENCES clauses. If they do, FairCom DB SQL restricts operations on the table containing the primary key:

• DROP TABLE statements that delete the table fail
• DELETE and UPDATE statements that modify values in the combination of columns that match a foreign key’s value also fail

The following example shows creation of a table-level primary key. Note that its definition is separated from the column definitions by a comma:

CREATE TABLE supplier_item (

supp_no INTEGER NOT NULL,

item_no INTEGER NOT NULL,

qty INTEGER NOT NULL DEFAULT 0,

PRIMARY KEY (supp_no, item_no)

) ;

UNIQUE ( column [, ... ] )

Defines the column list as a unique, or candidate, key for the table. Unique key table-level constraints have the same rules as primary key table-level constraints, except that you can specify more than one UNIQUE table-level constraint in a table definition.

The following example shows creation of a table with two UNIQUE table-level constraints:

CREATE TABLE order_item (

order_no INTEGER NOT NULL,

item_no INTEGER NOT NULL,

qty INTEGER NOT NULL,

price MONEY NOT NULL,

UNIQUE (order_no, item_no),

UNIQUE (qty, price)

) ;

FOREIGN KEY ... REFERENCES

Defines the first column list as a foreign key and, in the REFERENCES clause, specifies a matching primary or unique key in another table.

A foreign key and its matching primary or unique key specify a referential constraint: The combination of values stored in the columns that make up a foreign key must either:

• Have at least one of the column values be null
• Be equal to some corresponding combination of values in the matching unique or primary key

You can omit the column list in the REFERENCES clause if the table specified in the REFERENCES clause has a primary key and you want the primary key to be the matching key for the constraint.

The following example defines the combination of columns student_courses.teacher and student_courses.course_title as a foreign key that references the primary key of the table courses. Note that the REFERENCES clause does not specify column names because the foreign key refers to the primary key of the courses table.

CREATE TABLE courses (

teacher CHAR (20) NOT NULL,

course_title CHAR (30) NOT NULL,

PRIMARY KEY (teacher, course_title)

) ;

CREATE TABLE student_courses (

student_id INTEGER,

teacher CHAR (20),

course_title CHAR (30),

FOREIGN KEY (teacher, course_title) REFERENCES courses

) ;

FairCom DB SQL evaluates the referential constraint to see if it satisfies the following search condition:

(student_courses.teacher IS NULL

OR student_courses.course_title IS NULL)

OR

EXISTS (SELECT * FROM student_courses WHERE

(student_courses.teacher = courses.teacher AND

student_courses.course_title = courses.course_title)

)

INSERT, UPDATE or DELETE statements that cause the search condition to be false violate the constraint, fail, and generate an error.

CHECK (search_condition)

Specifies a table-level check constraint. The syntax for table-level and column level check constraints is identical. Table-level check constraints must be separated by commas from surrounding column definitions.

FairCom DB SQL restricts the form of the search condition. The search condition must not:

• Refer to any column other than columns that precede it in the table definition
• Contain aggregate functions, subqueries, or parameter references

The following example creates a table with two column-level check constraints and one table-level check constraint:

CREATE TABLE supplier (

supp_no INTEGER NOT NULL,

name CHAR (30),

status SMALLINT CHECK (

supplier.status BETWEEN 1 AND 100 ),

city CHAR (20) CHECK (

supplier.city IN ('NEW YORK', 'BOSTON', 'CHICAGO')),

CHECK (supplier.city <> 'CHICAGO' OR supplier.status = 20)

) ;

Storage Attributes

A quoted string that specifies specific FairCom DB SQL table attributes. FairCom DB SQL supports the following STORAGE_ATTRIBUTES parameters:

• 'ENCR=AES32' - Turns on encryption for the table. Omit 'ENCR=AES32' from the STORAGE_ATTRIBUTES clause when you do not want to encrypt the table. Note: if encryption is not yet enabled for the database, this setting will cause table creation to fail with error(-17454): CT - Service not supported. To enable encryption for the database, see Advanced Data Encryption in the Server Administrators Guide.
  • AES16 (Rijndael)
  • AES24
  • AES32
• 'RECBYT_IDX' - Creates the table with a RECBYT index. This index is required for physical backward traversal of variable-length type files. The RECBYT index does impose a minimal amount of overhead, however, when inserting/updating/deleting records. This index is not required for usual operations, and as such, is off by default.
• 'NORECBYT_IDX' - (Default) Creates the table without a RECBYT index.
• 'ROWID_FLD' - (Default) The ROWID field is an auto-incrementing number which takes advantage of the c-tree serial segment index mode. This hidden field is a sequential number added to each record insert, and maintained with an associated index. This field is not required for proper SQL operation, however, is referenced directly by the ROWID related scalar functions. In the case of no ROWID field, the record RECBYT value is returned, which may not maintain uniqueness over time. As there is a limit of only one serial segment index per c-tree data file, this value is unavailable for other fields when a ROWID is present. Conversely, no ROWID is available when an existing c-tree data file with a serial segment field is imported into FairCom DB SQL. The IDENTITY field attribute should be used for auto-incrementing values as it is better performing with greater field type flexibility.
• 'NOROWID_FLD' - Creates a table without the ROWID serial segment field and index.
• 'PREIMG' - This option disables transaction logging for the table. This can be useful for temporary FairCom DB SQL tables that are not required to be recovered in the event of catastrophic failure, yet retain atomicity of transactions.
• 'HOTALTER' - Creates the table with Hot Alter Table support. (Supported in V11.5 and later.)
• 'NOHOTALTER' - (default) - Create the table with Hot Alter Table support disabled. (Supported in V11.5 and later.)
• 'HUGE' - (Default) Denotes that a table should be created as a c-tree HUGE file (64-bit file offset addressing).
• 'NOHUGE' - Denotes that a table should be created as a c-tree non-HUGE file (32-bit file offset addressing). These files have a limit of 2Gb or 4Gb depending on your OS platform.

Note: See the SQL_OPTION NO_HUGEFILE FairCom Server configuration option to make NOHUGE the default.

• 'NOMSEC' - Denotes that a table containing time and timestamp fields should be created with these fields not having milliseconds support.
• ‘BLOCK_COMPRESSION' - Denotes that a table should be created with block compression.
• ‘CHANGEID’ - Creates the table with a ‘changeid’ field to make the table compliant with JSON DB expectations. To ensure a SQL CREATE TABLE statement adds a 'changeid' field to a table, include the 'changeid' keyword in the STORAGE_ATTRIBUTES clause. This feature overrides the SQL_OPTION AUTO_CHANGEID setting in ctsrvr.cfg.
• ‘NOCHANGEID’ - Creates the table without the ‘changeid’ field. To ensure a SQL CREATE TABLE statement does not add a 'changeid' field to a table, include the 'nochangeid' keyword in the STORAGE_ATTRIBUTES clause. This feature overrides the SQL_OPTION AUTO_CHANGEID setting in ctsrvr.cfg.
• ‘CTMONEY’ - Creates the table having the SQL money type mapped to c-tree CT_MONEY instead of the default of either CT_CURRENCY or CT_NUMBER based on the precision and scale. Mapping to CT_MONEY implies that it is not possible to use a scale of 4 and despite the precision definition in SQL the maximum value that can be stored is what fits in a LONG.

Examples

Setting a single storage attribute only requires the attribute name to be included between the single quotes following the STORAGE_ATTRIBUTES clause. Double quotes cannot be used:

CREATE TABLE test1 ( id integer ) STORAGE_ATTRIBUTES 'HUGE';

To set multiple storage attributes, separate them with a semicolon. You may include space before or after the semicolon, but it is unnecessary. The last attribute may be followed by a semicolon or not:

CREATE TABLE test2 ( id integer ) STORAGE_ATTRIBUTES 'RECBYT_IDX;ROWID_FLD';

Storage attributes are not case sensitive.

CREATE TABLE test3 ( id integer ) STORAGE_ATTRIBUTES 'recbyt_idx;rowid_fld;hotalter;huge;';

The following attributes cannot be used together:

• RECBYT_IDX and NORECBYT_IDX
• ROWID_FLD and NOROWID_FLD
• HOTALTER and NOHOTALTER
• HUGE and NOHUGE
• CHANGEDID and NOCHANGEID

The following example throws error(-26014): conflicting or duplicate attributes found:

CREATE TABLE test4 ( id integer ) STORAGE_ATTRIBUTES 'huge;noHuge';

Encryption must be enabled in the database before you can use an encryption attribute. When encryption is not enabled, the following example throws error(-17454): CT - Service not supported:

CREATE TABLE test5 ( id integer ) STORAGE_ATTRIBUTES 'ENCR=AES32';

NOTE: The default storage attributes are best unless you need to turn on encryption or turn off transaction processing for a table. You have limited ability to change storage attributes after a table is created because most attributes define how the table is physically created. You may change transaction logging for a table (i.e. PREIMG) using the cttrnmod utility or call the stored procedure fc_set_file_tran_state. This is useful when you want to temporarily suspend transaction processing for a bulk load or to turn it on temporarily for replication. You can also create a new table with the desired storage attributes, copy the old table’s data into it, rename the old table, and rename the new table. When all is working as desired, you can drop the old table. In the SQL Reference Guide, see CREATE TABLE, ALTER TABLE, and DROP TABLE.

The following example insures that a 'changeid' field will be added to the table.

CREATE TABLE test6 ( id integer ) STORAGE_ATTRIBUTES 'hotalter;huge;changeid;';

The following example insures that a 'changeid' field will NOT be added to the table.

CREATE TABLE test7 ( id integer ) STORAGE_ATTRIBUTES 'hotalter;huge;nochangeid;';

The following example assigns 'changeid' functionality to a specified field.

CREATE TABLE test8 ( id bigint, change_tracker bigint default autochangeid);

Deprecated/Removed Attributes

• 'ENCR=crypt' - The following crypt values for 'ENCR=crypt' are deprecated and unsupported.
  • DES8
  • DES16
  • DES24
  • BLF8
  • BLF56
  • TWF16
  • TWF24
  • TWF32

SQL - Create [if not exists] and Drop [if exists]

The CREATE TABLE syntax has been expanded to avoid failure in case the table exists (during create) or does not exist (during drop). The feature is similar to syntaxes found in the MySql and Postgres dialects.

We now implement this feature for tables, indexes, procedures, triggers, and functions:

CREATE TABLE [IF NOT EXISTS]...

DROP TABLE [IF EXISTS]...

CREATE INDEX [IF NOT EXISTS]...

DROP INDEX [IF EXISTS]...

CREATE PROCEDURE [IF NOT EXISTS]...

DROP PROCEDURE [IF EXISTS]...

CREATE TRIGGER [IF NOT EXISTS]...

DROP TRIGGER [IF EXISTS]...

CREATE FUNCTION [IF NOT EXISTS]...

DROP FUNCTION [IF EXISTS]...

Assign Values to Auto-Increment Fields in INSERT

SQL - auto_increment fields enhanced

IDENTITY column support is now "smart" and automatically inserts values when explicitly specified and auto-generates values when they are not (the default existing behavior).

The syntax/definition of auto_increment fields in this release is identical to the current IDENTITY syntax except INSERT statements are smarter. When an INSERT statement specifies a value for an IDENTITY column, it inserts the specified value; when the value is omitted, it is generated automatically.

Prior to this modification, it was necessary to explicitly disable IDENTITY insertion with
SET identity_insert <table> on | off.

Only one table at a time can be set to identity_insert on.

No table definition changes are required to enable this new support. It enables compatibility with many external SQL frameworks such as SQLAlchemy.

CREATE TRIGGER

Description

Creates a trigger for the specified table. A trigger is a special type of stored procedure that helps insure referential integrity for a database.

Triggers contain Java source code which can use FairCom DB SQL Java classes to carry out database operations. Triggers are automatically activated when an INSERT, UPDATE, or DELETE statement affects the trigger’s target table. The Java source code details what actions the trigger takes when it is activated.

For more detail on creating and using triggers, see the FairCom DB SQL Guide to Using Stored Procedures and Triggers.

Syntax

CREATE TRIGGER [ IF NOT EXISTS ] [ owner_name. ] trigname

{ BEFORE | AFTER } { INSERT

| DELETE

| UPDATE [ OF ( column_name [ , ... ] ) }

ON table_name

[ REFERENCING { OLDROW [ , NEWROW ] | NEWROW [ , OLDROW ] } ]

[ FOR EACH { ROW | STATEMENT } ]

[ IMPORT

java_import_clause ]

BEGIN

java_snippet

END

Arguments

IF NOT EXISTS

This argument avoids failure by creating the trigger only if a trigger of the same name does not already exist.

owner_name

Specifies the owner of the trigger. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.

trigname

Names the trigger. DROP TRIGGER statements specify the trigger name defined here. FairCom DB SQL also uses trigname in the name of the Java class that it creates from the Java snippet.

BEFORE | AFTER

The trigger action time. Specifies whether the triggered action implemented by java_snippet executes before or after the triggering INSERT, UPDATE, or DELETE statement.

INSERT | DELETE | UPDATE [ OF column_name [ , ... ] ]

The statement that activates the trigger. Also called the trigger event.

If UPDATE is the trigger event, this clause can include an optional column list. Updates to any of the specified columns or use of a specified column in a search condition to update other values will activate the trigger. As long as a specified column is not used in either case then the trigger will not be activated. If an UPDATE trigger does not include the optional column list, an update statement specifying any of the table columns will activate the trigger.

ON table_name

The name of the table for which the trigger is defined. table_name cannot be the name of a view or a remote table. A triggering statement that specifies table_name causes the trigger to execute.

REFERENCING { OLDROW [ , NEWROW ] | NEWROW [ , OLDROW ] }

Allowed only if the trigger also specifies the FOR EACH ROW clause. The REFERENCING clause provides a mechanism for c-treeSQL to pass row values as input parameters to the stored procedure implemented by java_snippet. The code in java_snippet uses the getValue() method of the NEWROW and OLDROW objects to retrieve values of columns in rows affected by the trigger event and store them in procedure variables.

The meaning of the OLDROW and NEWROW arguments of REFERENCING clause depends on whether the trigger event is INSERT, UPDATE, or DELETE:

• INSERT...REFERENCING NEWROW means the triggered action can access values of columns of each row inserted. c-treeSQL passes the column values specified by the INSERT statement.
• INSERT...REFERENCING OLDROW is meaningless, since there are no existing values for a row being inserted. INSERT...REFERENCING OLDROW generates a syntax error.
• UPDATE...REFERENCING NEWROW means the triggered action can access the values of columns, after they are changed, of each row updated. SQL passes the column values specified by the UPDATE statement.
• UPDATE...REFERENCING OLDROW means the triggered action can access the values of columns, before they are changed, of each row updated. c-treeSQL passes the column values of the row as it exists in the database before the update operation.
• DELETE...REFERENCING OLDROW means the triggered action can access values of columns of each row deleted. c-treeSQL passes the column values of the row as it exists in the database before the delete operation.
• DELETE...REFERENCING NEWROW is meaningless, since there are no new existing to pass for a row being deleted. DELETE...REFERENCING OLDROW generates a syntax error.

UPDATE is the only triggering statement that allows both NEWROW and OLDROW in the REFERENCING clause.

Note that the trigger action time (BEFORE or AFTER) does not affect the meaning of the REFERENCING clause. For instance, BEFORE UPDATE...REFERENCING NEWROW still means the values of columns after they are updated will be available to the triggered action.

The REFERENCING clause generates an error if the trigger does not include the FOR EACH ROW clause.

FOR EACH { ROW | STATEMENT }

The frequency with which the triggered action implemented by java_snippet executes:

• FOR EACH ROW means the triggered action executes once for each row being updated by the triggering statement. CREATE TRIGGER must include the FOR EACH ROW clause if it also includes a REFERENCING clause.
• FOR EACH STATEMENT means the triggered action executes only once for the whole triggering statement. FOR EACH STATEMENT is the default.

IMPORT
java_import_clause

Specifies standard Java classes to import. The IMPORT keyword must be upper case and on a separate line.

BEGIN
java_snippet

END

The body of the trigger. Also called the triggered action. The body contains the Java source code that implements the actions to be completed when a triggering statement specifies the target table. The Java statements become a method in a class the FairCom DB SQL engine creates and submits to the Java compiler.

The BEGIN and END keywords must be upper case and on separate lines.

Notes

• Triggers can take action on their own table so that they invoke themselves. FairCom DB SQL limits such recursion to five levels.
• You can you have multiple triggers on the same table. FairCom DB SQL executes all triggers applicable to a given combination of table, trigger event, and action time. If more than one trigger is applicable for a particular combination, FairCom DB SQL executes the triggers in the order they were created. (You can determine the creation order from the triggerid column in the admin.systrigger system table. The higher the triggerid value, the later the trigger was created.) The interaction of multiple triggers on the same table can be confusing, so exercise care.
• The actions carried out by a trigger may fire another trigger. When this happens, the other trigger’s actions execute before the rest of the first trigger finishes executing. FairCom DB SQL limits such nesting to five levels.
• If a FairCom DB SQL statement both fires a trigger and violates a constraint (possible if the trigger action time is BEFORE), any actions taken by the trigger are rolled back and do not take effect.
• To modify an existing trigger, you must delete it and issue another CREATE TRIGGER statement. You can query the admin.systrigger and sysproctxt system tables for details of the trigger before deleting it.

Example

This example illustrates an update trigger on a table called BUG_INFO. If the STATUS or PRIORITY fields are modified, the trigger modifies the BUG_SUMMARY and BUG_STATUS tables appropriately based on some conditions.

CREATE TRIGGER BUG_UPDATE_TRIGGER

AFTER UPDATE OF STATUS, PRIORITY ON BUG_INFO

REFERENCING OLDROW, NEWROW

FOR EACH ROW

IMPORT

import java.sql.* ;

BEGIN

try

{

// column number of STATUS is 10

String old_status, new_status;

old_status = (String) OLDROW.getValue(10, CHAR);

new_status = (String) NEWROW.getValue(10, CHAR);

if (old_status.equals("OPEN") && new_status.equals("FIXED"))

{

// If STATUS has changed from OPEN to FIXED

// increment the bugs_fixed_cnt by 1 in the

// row corresponding to current month

// and current year

SQLIStatement update_stmt = new SQLIStatement(

" update BUG_STATUS set bugs_fixed_cnt = bugs_fixed_cnt + 1 " +

" where month = ? and year = ?"

);

Integer current_month = 10;

Integer current_year = 1997;

update_stmt.setParam(1, current_month);

update_stmt.setParam(2, current_year);

update_stmt.execute();

SQLIStatement insert_stmt = new SQLIStatement(

" insert into BUG_SUMMARY values (?,?,?)"

);

// Column number for bug_id, priority, reported_on and fixed_on

// are 1, 2, 5, 6

String bug_id, priority;

Date reported_on, fixed_on;

bug_id = (String) NEWROW.getValue(1, CHAR);

priority = (String) NEWROW.getValue(2, CHAR);

reported_on = (Date) NEWROW.getValue(5, DATE);

fixed_on = (Date) NEWROW.getValue(6, DATE);

Integer turn_around_time = fixed_on - reported_on;

insert_stmt.setParam(1, bug_id);

insert_stmt.setParam(2, priority);

insert_stmt.setParam(3, turn_around_time);

insert_stmt.execute();

}

// If PRIORITY has changed to URGENT,

//increment the bugs_escalated by 1 in the month field.

String old_priority, new_priority;

old_priority = (String) OLDROW.getValue(2, CHAR);

new_priority = (String) NEWROW.getValue(2, CHAR);

if(new_priority.equals("URGENT") && old_priority.equals("URGENT"))

{

// If PRIORITY has changed to URGENT

// increment the bugs_escalated by 1 in the row corresponding to current month

// and current year

SQLIStatement update_stmt (

" update BUG_STATUS set bugs_escalated_cnt = bugs_escalated_cnt + 1 " +

" where month = ? and year = ?"

);

Integer current_month = 10;

Integer current_year = 1997;

update_stmt.setParam(1, current_month);

update_stmt.setParam(2, current_year);

update_stmt.execute();

}

}

catch (SQLException e)

{

// Log the exception message from e.

SQLException sqle = new SQLException("UPDATE_BUG_TRIGGER failed");

throw sqle;

}

END

Authorization

Users executing CREATE TRIGGER must have the DBA privilege or RESOURCE privilege.

CREATE VIEW

Description

Creates a view with the specified name on existing tables and/or views.

Syntax

CREATE VIEW [ owner_name. ] view_name

[ ( column_name, column_name, ... ) ]

AS [ ( ] query_expression [ ) ]

[ WITH CHECK OPTION ] ;

Notes

• The owner_name is made the owner of the created view.
• The column names specified for the view are optional and provide an alias for the columns selected by the query specification. If the column names are not specified then the view will be created with the same column names as the tables and/or views it is based on.
• A view is deletable if deleting rows from that view is allowed. For a view to be deletable, the view definition has to satisfy the following conditions:
  • The first FROM clause contains only one table reference or one view reference.
  • There are no aggregate functions, DISTINCT clause, GROUP BY or HAVING clause in the view definition.
  • If the first FROM clause contains a view reference, then the view referred to is deletable.
• A view is updatable if updating rows from that view is allowed. For a view to be updatable, the view has to satisfy the following conditions:
  • The view is deletable (That is, it satisfies all the conditions specified above for deletability).
  • All the select expressions in the first SELECT clause of the view definition are simple column references.
  • If the first FROM clause contains a view reference, then the view referred to is updatable.
• A view is insertable if inserting rows into that view is allowed. For a view to be insertable, the view has to satisfy the following conditions:
  • The view is updatable (That is, it satisfies all the conditions specified above for updatability).
  • If the first FROM clause contains a table reference, then all NOT NULL columns of the table are selected in the first SELECT clause of the view definition.
  • If the first FROM clause contains a view reference, then the view referred to is insertable.
• The WITH CHECK OPTION clause can be specified only if the view is updatable.
• If WITH CHECK OPTION clause is specified when defining a view, then during any update or insert of a row on this view, it is checked that the updated/inserted row satisfies the view definition (That is, the row is selectable using the view).

Examples

CREATE VIEW ne_customers AS

SELECT cust_no, name, street, city, state, zip

FROM customer

WHERE state IN ('NH', 'MA', 'NY', 'VT')

WITH CHECK OPTION ;

CREATE VIEW order_count (cust_number, norders) AS

SELECT cust_no, COUNT(*)

FROM orders

GROUP BY cust_no;

Authorization

The user executing this statement must have the following privileges:

• DBA or RESOURCE privilege.
• SELECT privilege on all the tables/views referred to in the view definition.

If owner_name is specified and is different from the name of the user executing the statement, then the user must have DBA privilege.

DELETE

Description

Deletes zero, one or more rows from the specified table that satisfy the search condition specified in the WHERE clause. If the optional WHERE clause is not specified, then the DELETE statement deletes all rows of the specified table.

Syntax

DELETE FROM [owner_name.] { table_name | view_name }

[ WHERE search_condition ];

Notes

• If the table has primary/candidate keys, and if there exists references from other tables to the rows to be deleted, the statement is rejected.
• While our DELETE doesn't directly support a TOP clause, you can do this with any indexed field with a subquery as such:

DELETE FROM <table> WHERE <idxfield> <= (SELECT MAX(<idxfield>) FROM (SELECT TOP 1000 <idxfield> FROM <table>) x )

Example

DELETE FROM customer

WHERE customer_name = 'RALPH' ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege.
• Ownership of the table.
• DELETE permission on the table.

If the target is a view, then the DELETE privilege is required on the target base table referred to in the view definition.

DISCONNECT

Description

Terminates the connection between an application and a database environment.

Syntax

DISCONNECT

{ connection_name | ALL | CURRENT | DEFAULT }

connection_name::

{ character_literal | host_variable }

Notes

• If a connection name is specified it should identify a dormant or current connection. If the connection_name specified is the current connection, the connection to the dormant DEFAULT database, if any, (this connection should have been previously achieved through CONNECT TO DEFAULT) is made the current connection; else no current connection exists.
• If ALL is specified, all established connections are disconnected. After the execution of this statement, a current connection does not exist.
• If CURRENT is specified, the current connection, if any, is disconnected. Here too, the connection to the dormant DEFAULT database, if any, (this connection should have been previously achieved through CONNECT TO DEFAULT) is made the current connection; else no current connection exists.
• If DEFAULT is specified, the DEFAULT connection, if any, is disconnected. If this connection happens to be the current connection, no current connection exists after the execution of this statement.

Examples

DISCONNECT 'conn_1';

DISCONNECT CURRENT;

DISCONNECT ALL;

DISCONNECT DEFAULT;

Authorization

None.

DROP FUNCTION

Description

Deletes a User Defined Scalar Function (UDF) on the specified table.

Syntax

DROP FUNCTION [IF EXISTS] function_name

Arguments

IF EXISTS

This argument avoids failure by attempting to drop the function only if a function of that name already exists.

function_name

The user defined function to be deleted from the table.

Example

DROP FUNCTION str_cat;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege
• Ownership of the index

DROP INDEX

Description

Deletes an index on the specified table.

Syntax

DROP INDEX [IF EXISTS] [index_owner_name.]index_name

[ON [table_owner_name.]table_name]

Arguments

[IF EXISTS]

This argument avoids failure by attempting to drop the index only if an index of that name already exists.

index_owner_name

If index_owner_name is specified and is different from the name of the user executing the statement, then the user must have DBA privileges.

table_name

The table_name argument is optional. If specified, the index_name is verified to correspond to the table.

Warning: Do not DROP indexes on primary or foreign keys. This can lead to relational integrity issues. Use ALTER TABLE to perform any changes involving constraints.

Example

DROP INDEX custindex ON customer ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege
• Ownership of the index
• EXCLUSIVE file access is required for any this operation

DROP PROCEDURE

Description

Deletes a stored procedure.

Syntax

DROP PROCEDURE [IF EXISTS] [ owner_name. ] procedure_name ;

Arguments

IF EXISTS

This argument avoids failure by attempting to drop the procedure only if a procedure of that name already exists.

owner_name

Specifies the owner of the procedure.

procedure_name

Names of the stored procedure to delete.

Example

DROP PROCEDURE new_sal ;

Authorization

To drop a stored procedure, users must be the owner of the procedure or have the DBA privilege.

DROP SEQUENCE

Syntax

DROP SEQUENCE sequence_name

Description

The DROP SEQUENCE command deletes the specified sequence.

• sequence_name is the name of the sequence that is to be deleted.

Example

To delete the sequence myseq, enter:

DROP SEQUENCE myseq ;

Return Values

See c-tree Plus Error Codes for a complete listing of valid c-tree Plus error values.

See Also

• Sequence Values (Sequence Values, /doc/sqlref/sql-sequence-values.htm)
• CREATE SEQUENCE
• ALTER SEQUENCE

DROP SYNONYM

Description

Drops the specified synonym.

Syntax

DROP [PUBLIC] SYNONYM [owner_name.]synonym ;

Arguments

PUBLIC

Specifies that the synonym was created with the PUBLIC argument.

FairCom DB SQL generates the Base table not found error if DROP SYNONYM specifies PUBLIC and the synonym was not a public synonym. Conversely, the same error message occurs if DROP SYNONYM does not specify public and the synonym was created with the PUBLIC argument.

To drop a public synonym, you must have the DBA privilege.

owner_name

If owner_name is specified and is different from the name of the user executing the statement, then the user must have DBA privileges.

synonym

Name for the synonym.

Example

DROP SYNONYM customer ;

DROP PUBLIC SYNONYM public_suppliers ;

Authorization

Users executing DROP SYNONYM must have either the DBA privilege or be the owner of the synonym. Users executing DROP PUBLIC SYNONYM must have the DBA privilege.

DROP TABLE

Description

Deletes the specified table.

Syntax

DROP TABLE [IF EXISTS] [owner_name.]table_name ;

Notes

• If owner_name is specified and is different from the name of the user executing the statement, then the user must have DBA privileges.
• When a table is dropped, the indexes on the table and the privileges associated with the table are dropped automatically.
• Views dependent on the dropped table are not automatically dropped, but become invalid.
• If the table is part of another table’s referential constraint (if the table is named in another table’s REFERENCES clause), the DROP TABLE statement fails. Use the ALTER TABLE statement to delete any referential constraints that refer to the table before issuing the DROP TABLE statement.
• IF EXISTS avoids failure by attempting to drop the table only if a table of that name already exists.

Example

DROP TABLE customer ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege.
• Ownership of the table.

DROP TRIGGER

Description

Deletes a trigger.

Syntax

DROP TRIGGER [IF EXISTS] [ owner_name. ] trigger_name ;

Arguments

IF EXISTS

This argument avoids failure by attempting to drop the trigger only if a trigger of that name already exists.

owner_name

Specifies the owner of the trigger.

trigger_name

Names of the trigger to delete.

Example

DROP TRIGGER sal_check ;

Authorization

• The DBA privilege entitles a user to drop any trigger.
• The owner of a trigger is given EXECUTE and DROP privilege on that trigger at creation time, by default.

DROP VIEW

Description

Deletes the view from the database.

Syntax

DROP VIEW [owner_name.]view_name ;

Notes

• If owner_name is specified and is different from the name of the user executing the statement, then the user must have DBA privileges.
• When a view is dropped, other views that are dependent on this view are not dropped. The dependent views become invalid.

Example

DROP VIEW newcustomers ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege
• Ownership of the view

EXPLAIN PLAN

Description

Generates a query execution plan for a SQL query.

Syntax

EXPLAIN PLAN [ INTO [ owner_name. ] table_name ] [ SET STATEMENT_ID = <stmt id> ] FOR <SQL statement>

Arguments

table name

The table into which the query plan is to be saved. Any valid table name may be specified. If the table does not exist, then it is created, provided the user has resource privilege. If the current owner is not the owner of the table specified he should have adequate privilege to write into it.

Note: The INTO table_name clause is optional. If it is not specified, then the query plan is stored in the default plan table admin.qep_tbl.

stmt_id

The user provided identifier for the query plan. Any character string (less than 32 characters) can be specified. The stmt_id is stored along with the query plan and is used to distinguish between the various Query plans stored in the Query plan table.

The SET STATEMENT_ID = <stmt id> clause is optional. If not specified, a unique ID of the form qep_stmtid_xx is generated internally.

Notes

For complex SQL operation, the query plan of the SQL query being executed has a very large impact on its performance. It is useful to have a mechanism wherein the execution plan of a query can be made available before actually executing the query. This aids in designing efficient queries.

FairCom DB SQL has the ability to dynamically generate and store query plans. The query plan can be generated and even viewed graphically with the FairCom DB SQL Explorer utility.

An internal stored procedure is used to retrieve the query plan.

access_get_qep( table_owner, table_name, stmt_id, max_nodeinfo_len )

• table_owner is the owner of the query plan table
• table_name is the name of the query plan table where the plan is stored
• stmt_id is the Statement Id provided by the user or the id internally generated when the execution plan was generated.
• max_nodeinfo_len (integer) - is the length of the information displayed per node in the resultset. By default this is set to 256. Set this value to 0 if the default value is to be used (8192).

The output of the procedure is a resultset consisting of a single column. Each record of the resultset corresponds to a node in the query plan. The ordering of the nodes in the resultset is, from bottom to top and left to right of the query execution plan tree.

Authorization

If the table does not exist, then it is created, provided the user has resource privilege. If the current owner is not the owner of the table specified they should have adequate privilege to write into it.

GET DIAGNOSTICS

Description

Retrieves information about the execution of the previous FairCom DB SQL statement. GET DIAGNOSTICS extracts information from the FairCom DB SQL diagnostics area, a data structure that contains information about the execution status of the most recent FairCom DB SQL statement. There are two components to the diagnostics area:

• The header contains overall information about the last FairCom DB SQL statement as a whole
• The detail area contains information for a particular condition (an error, warning, or success condition) associated with execution of the last FairCom DB SQL statement. The diagnostics area can potentially contain multiple detail areas corresponding to multiple conditions generated by the FairCom DB SQL statement described by the header.

Note: The FairCom DB SQL diagnostics area currently supports only one detail area.

There are two forms of the GET DIAGNOSTICS statement, one that extracts header information (GET DIAGNOSTICS), and one that extracts detail information (GET DIAGNOSTICS EXCEPTION number).

Syntax

GET DIAGNOSTICS

:param = header_info_item [ , :param = header_info_item ] ...

GET DIAGNOSTICS EXCEPTION number

:param = detail_info_item [ , :param = detail_info_item ] ...

header_info_item ::

{ NUMBER

| MORE

| COMMAND_FUNCTION

| DYNAMIC_FUNCTION

| ROW_COUNT }

detail_info_item ::

{ CONDITION_NUMBER

| RETURNED_SQLSTATE

| CLASS_ORIGIN

| SUBCLASS_ORIGIN

| TABLE_NAME

| COLUMN_NAME

| MESSAGE_TEXT

| MESSAGE_LENGTH }

Arguments

:parameter

A host-language variable to receive the information returned by the GET DIAGNOSTICS statement. The host-language program must declare parameter to be compatible with the SQL data type of the information item.

header_info_item

One of the following keywords, which returns associated information about the diagnostics area or the FairCom DB SQL statement:

EXCEPTION number

Specifies that GET DIAGNOSTICS extracts detail information. number specifies which of multiple detail areas GET DIAGNOSTICS extracts. Currently, number must be the integer 1.

detail_info_item

One of the following keywords, which returns associated information about the particular error condition:

Notes

The GET DIAGNOSTICS statement itself does not affect the contents of the diagnostics area. This means applications can issues multiple GET DIAGNOSTICS statements to retrieve different items of information about the same FairCom DB SQL statement.

Example

GET DIAGNOSTICS :num = NUMBER, :cmdfunc = COMMAND_FUNCTION

GET DIAGNOSTICS EXCEPTION :num

:sstate = RETURNED_SQLSTATE, :msgtxt = MESSAGE_TEXT

Authorization

GRANT

Description

Grants various privileges to the specified users or user group for the database. There are different forms of the GRANT statement for various purposes:

• To grant database-wide privileges, either system administration (DBA) or general creation (RESOURCE)
• To grant privileges on the specified tables or view
• To grant the privilege to execute the specified stored procedure

Syntax

GRANT { RESOURCE, DBA }

TO {user_name } [ , { user_name } ] ... ;

GRANT { privilege [ , privilege ] ... | ALL [ PRIVILEGES ] }

ON table_name

TO { { user_name | usergroup_name } [ , {user_name | usergroup_name} ] ... | PUBLIC }

[WITH GRANT OPTION] ;

GRANT EXECUTE ON procedure_name

TO { { user_name | usergroup_name } [ , { user_name | usergroup_name } ] ... | PUBLIC } ;

privilege ::

{ SELECT | INSERT | DELETE | ALTER | INDEX

| UPDATE [ (column, column, ... ) ]

| REFERENCES [ (column, column, ... ) ] }

Arguments

DBA

This argument allows the specified users to create, access, modify, or delete any database object and grant other users any privileges. It can only be applied to users.

RESOURCE

This argument allows the specified users to issue CREATE statements. The RESOURCE privilege does not allow users to issue DROP statements on database objects. Only the object's owner and users with the DBA privilege can drop database objects. This argument can only be applied to users.

SELECT

Allows the specified users to read data in the table or view.

INSERT

Allows the specified users to add new rows to the table or view.

DELETE

Allows the specified users to delete rows in the table or view

ALTER

Allows the specified users to modify the table or view

INDEX

Allows the specified users to create an index on the table or view.

UPDATE [ (column, column, ... ) ]

Allows the specified users to modify existing rows in the table or view. If followed by a column list, the users can modify values only in the columns named.

REFERENCES [ (column, column, ... ) ]

Allows the specified users to refer to the table from other tables' constraint definitions. If followed by a column list, constraint definitions can refer only to the columns named. For more details on constraint definitions, see Column Constraints.

ALL

Grants all privileges for the table or view.

ON table_name

The table or view for which FairCom DB SQL grants the specified privileges.

EXECUTE ON procedure_name

Allows execution of the specified stored procedure.

TO user_name [ , user_name ] ...

The list of users for which FairCom DB SQL grants the specified privileges.

TO PUBLIC

Grants the specified privileges to any user with access to the system.

WITH GRANT OPTION

Allows the specified users to grant access rights or a subset of their rights to other users.

Examples

Note: You must commit (or turn on auto-commit) to save your changes. For example, ISQL defaults to auto-commit off, so if you grant a permission and exit, your changes are discarded.

GRANT RESOURCE TO user1;

GRANT SELECT ON custmaster TO odbc_group;

GRANT ALTER ON cust_view TO dbuser1 ;

GRANT SELECT ON newcustomers TO dbuser2 ;

GRANT EXECUTE ON sample_proc TO searle;

commit;

Authorization

The user granting DBA or RESOURCE privileges must have the DBA privilege.

The user granting privileges on a table must have at least one of the following privileges:

• DBA privilege
• Ownership of the table

All the specified privileges on the table, granted with the WITH GRANT OPTION clause

SQL Compliance:

SQL-92, ODBC Core SQL grammar. Extensions: ALTER, INDEX, RESOURCE, DBA privileges

Environment:

Embedded SQL, interactive SQL, ODBC applications

Related Statements:

REVOKE

INSERT

Description

Inserts new rows into the specified table/view that will contain either the explicitly specified values or the values returned by the query expression.

Syntax

//Inserting one record

INSERT INTO [owner_name.] { table_name | view_name }

[ (column_name, column_name, ... ) ]

{ VALUES (value, value, ... )

//Inserting multiple records

INSERT INTO [owner_name.] { table_name | view_name }

[ (column_name, column_name, ... ) ]

{ VALUES (value1-1, value1-2, ... value1-n),

(value2-1, value2-3, ... value2-n),

...

};

//Inserting records from a query

INSERT INTO [owner_name.] { table_name | view_name }

[ (column_name, column_name, ... ) ]

query_expression ;

Notes

• If the optional list of column names is specified, then only the values for those columns need be supplied. The rest of the columns of the inserted row will contain NULL values, provided the table definition allows NULL values and there is no DEFAULT clause for the columns. If a DEFAULT clause is specified for a column and the column name is not present in the optional column list, then the column takes the default value.
• If the optional list is not specified then all the column values have to be either explicitly specified or returned by the query expression. The order of the values should be the same as the order in which the columns have been declared in the declaration of the table/view.
• Explicit specification of the column values provides for insertion of only one row at a time. The query expression option allows for insertion of multiple rows at a time.
• If the table contains a foreign key, and there does not exist a corresponding primary key that matches the values of the foreign key in the record being inserted, the insert operation is rejected.
• You can use INSERT statements with query expressions to transfer rows from one remote table to another.
• IDENTITY columns are populated automatically based on the seed and increment values that were specified for the IDENTITY column, values for the IDENTITY column cannot be specified as part of the INSERT statement. An IDENTITY column cannot be specified in the list of column names.
• Multiple value sets are allowed.
  
  ISQL>create table mult (f1 int, f2 int);
  ISQL>insert into mult values (1,1),(2,2),(3,3);

Parameters are not allowed in multiple value sets.

Examples

//Create table alerts

create table alerts

(

id integer not null identity (1,1)

, date_time timestamp

, alert_type character(20)

, description varchar(200)

, constraint alerts_constraint_pk primary key ( id )

);

//Inserting one record

INSERT INTO alerts

( date_time, alert_type, description )

VALUES

( '01/03/2022 15:16:10.000', 'NOTICE', 'Information' );

//Inserting records from a recordset

INSERT INTO alerts ( date_time, alert_type, description )

VALUES

( '02/04/2023 16:27:21.000', 'WARNING ', 'Minor alert' )

, ( '03/05/2024 17:38:32.000', 'CRITICAL', 'Major alert' )

, ( '04/06/2025 18:49:43.000', 'FAILURE ', 'P1' );

//Inserting records from a SELECT

INSERT INTO alerts ( date_time, alert_type, description )

SELECT date_time, alert_type, description FROM alerts ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege.
• Ownership of the table.
• INSERT privilege on the table.

If a query_expression is specified, then the user must have any of the following privileges:

• DBA privilege.
• SELECT privilege on all the tables/views referred to in the query_expression.

LOCK TABLE

Description

Explicitly locks the specified tables for shared or exclusive access.

Syntax

LOCK TABLE table_name [ , ... ]

IN { SHARE | EXCLUSIVE } MODE

[ NOWAIT ] ;

Notes

• Explicit locking can be used to improve the performance of a single transaction at the cost of decreasing the concurrency of the system and potentially blocking other transactions. It is more efficient to explicitly lock a table if you know ahead of time that the transaction would be updating a substantial part of a table. The efficiency is gained by decreased overhead of the implicit locking mechanism and any potential waits for acquiring page level locks for the table.
• Explicit locking can be used to minimize potential deadlocks in situations where a substantial part of a table is being modified by a transaction. The benefits of table locking should always be compared with the disadvantages of losing concurrency before a choice is made between explicit and implicit locking.
• The SHARE mode allows other transactions to read the table but does not allow modifications on the table.
• The EXCLUSIVE mode does not allow any other transactions to read and/or modify the table.
• If the lock request cannot be honored by the system (due to a conflict lock held by another transaction) then in the normal case the transaction is suspended until the specified lock can be acquired. The NOWAIT option provides an immediate return of control if the lock cannot be acquired.
• Locks that are acquired explicitly and/or implicitly are released only when the transaction is ended using either the COMMIT or the ROLLBACK WORK statement.

Example

LOCK TABLE custmaster

IN EXCLUSIVE MODE ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA, or INSERT, UPDATE, DELETE, or ALTER privilege.

ORDER BY

Description

The ORDER BY clause specifies the sorting of rows retrieved by the SELECT statement. FairCom DB SQL does not guarantee the sort order of rows unless the SELECT statement includes an ORDER BY clause.

Syntax

ORDER BY { expr | posn } [ ASC | DESC ]

[ , { expr | posn } [ASC | DESC] , ... ]

Notes

• Ascending order is the default ordering. The descending order will be used only if the keyword DESC is specified for that column.
• Each expr is an expression of one or more columns of the tables specified in the FROM clause of the SELECT statement. Each posn is a number identifying the column position of the columns being selected by the SELECT statement.
• The selected rows are ordered on the basis of the first expr or posn and if the values are the same then the second expr or posn is used in the ordering.
• The ORDER BY clause if specified should follow all other clauses of the SELECT statement.
• An ORDER BY clause can appear in a subquery, however, subqueries with a combination of TOP, GROUP BY and outer references remain unsupported.
• An ORDER BY clause can appear in a FOR UPDATE query.

Note: This is a non-standard SQL feature as specified by SQL92. Also, full cursor update is not supported by FairCom DB SQL.

• A query expression followed by an optional ORDER BY clause can be specified. In such a case, if the query expression contains set operators, then the ORDER BY clause can specify column names or position from the first SELECT statement.

For example:

-- Get a merged list of customers and suppliers

-- sorted by their name.

(SELECT name, street, state, zip

FROM customer

UNION

SELECT name, street, state, zip

FROM supplier)

ORDER BY 1 ;


(SELECT name, street, state, zip

FROM customer

UNION

SELECT name, street, state, zip

FROM supplier)

ORDER BY customer.name;

• In V11 and later, a query projecting an LVARBINARY column using an ORDER BY clause that requires sorting in memory may result in projecting null values. Prior to this change, LVARCHAR was the only supported long type. The logic has been updated to handle LVARBINARY.

Example

SELECT name, street, city, state, zip

FROM customer

ORDER BY name ;

RENAME

Description

Renames the specified table name, view name or synonym to the new name specified.

Syntax

RENAME [owner_name.] oldname TO [owner_name.] newname ;

Arguments

[owner_name.]

Optional owner-name qualifier for the name. If the owner name is not the same as that of the current user, the current user must have the DBA privilege.

If specified, the owner name must be the same for oldname and newname. In other words, you cannot change the owner of a table, view, or synonym with RENAME.

oldname

Current name of the table, view, or synonym.

newname

New name for the table, view, or synonym.

Example

RENAME sitem TO supplier_item ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege
• Ownership of the table/view/synonym.
• ALTER privilege on the table/view.

REVOKE

Description

Revokes various privileges to the specified users for the database. There are three forms of the REVOKE statement:

• The first form revokes database-wide privileges, either system administration (DBA) or general creation (RESOURCE)
• The second form revokes various privileges on specific tables and views
• The third form revokes the privilege to execute the specified stored procedure

Syntax

REVOKE { RESOURCE | DBA }

FROM { {user_name | usergroup_name} [ , {user_name | usergroup_name} ] ... } ;

REVOKE [ GRANT OPTION FOR ]

{ privilege [ , privilege, ] ... | ALL [ PRIVILEGES ] }

ON table_name

FROM { {user_name | usergroup_name} [ , {user_name | usergroup_name} ] ... | PUBLIC } [ RESTRICT | CASCADE ] ;

REVOKE [ GRANT OPTION FOR ] EXECUTE ON procedure_name

FROM { {user_name | usergroup_name} [ , {user_name | usergroup_name} ] ... | PUBLIC } [ RESTRICT | CASCADE ] ;

privilege ::

{ SELECT | INSERT | DELETE | ALTER | INDEX

| UPDATE [ (column, column, ... ) ]

| REFERENCES [ (column, column, ... ) ] }

Arguments

GRANT OPTION FOR

Revokes the grant option for the privilege from the specified users. The actual privilege itself is not revoked. If specified with RESTRICT, and the privilege was passed on to other users, the REVOKE statement fails and generates an error. Otherwise, GRANT OPTION FOR implicitly revokes any rights the user may have in turn given to other users.

{ privilege [ , privilege, ] ... | ALL [ PRIVILEGES ] }

List of privileges to be revoked. See the description in GRANT (GRANT, SQL GRANT) for details on specific privileges. Revoking RESOURCE and DBA rights can only be done by the administrator or a user with DBA rights.

If a user has been granted access to a table by more than one user then all the users have to perform a revoke for the user to lose his access to the table.

Using the keyword ALL revokes all the rights granted on the table/view.

ON table_name

The table or view for which FairCom DB SQL revokes the specified privileges.

EXECUTE ON procedure_name

Revokes the right to execute the specified stored procedure.

FROM {user_name | usergroup_name} [ , {user_name | usergroup_name} ] ...

Revokes the specified rights on the table or view from the specified list of users.

FROM PUBLIC

Revokes the specified rights on the table or view from any user with access to the system.

RESTRICT | CASCADE

If the REVOKE statement specifies RESTRICT, FairCom DB SQL checks to see if the privilege being revoked was passed on to other users (possible only if the original privilege included the WITH GRANT OPTION clause). If so, the REVOKE statement fails and generates an error. If the privilege was not passed on, the REVOKE statement succeeds.

If the REVOKE statement specifies CASCADE, revoking the access rights of a user also revokes the rights from all users who received the privilege as a result of that user giving the privilege to others.

If the REVOKE statement specifies neither RESTRICT nor CASCADE, the behavior is the same as for CASCADE.

Example

REVOKE INSERT ON customer FROM dbuser1 ;

REVOKE ALTER ON cust_view FROM dbuser2 ;

Authorization

The user revoking DBA or RESOURCE privileges must have the DBA privilege.

The user revoking privileges on a table must have any of the following privileges:

• DBA privilege
• Ownership of the table
• All the specified privileges on the table, granted with the WITH GRANT OPTION clause

SQL Compliance:

SQL-92, ODBC Core SQL grammar. Extensions: ALTER, INDEX, RESOURCE, DBA privileges

Environment:

Embedded SQL, interactive SQL, ODBC applications

Related Statements:

GRANT

ROLLBACK WORK

Description

Ends the current transaction and undoes any database changes performed during the transaction.

Syntax

ROLLBACK [ WORK ] ;

Notes

Under the following exceptional circumstances a commit or a commit to a savepoint may fail causing all changes to be rolled back:

• Lock timeout errors
• Hardware errors
• Software errors
• Storage space failures

Authorization

None.

SELECT

Description

Selects the specified column values from one or more rows contained in the table(s) specified in the FROM clause. The selection of rows is restricted by the WHERE clause. The temporary table derived through the clauses of a select statement is called a result table.

The format of the SELECT statement is a query expression with optional ORDER BY and FOR UPDATE clauses. For more detail on query expressions, see Query Expressions.

Syntax

select_statement ::

query_expression

ORDER BY { expr | posn } [ ASC | DESC ]

[ , { expr | posn } [ASC | DESC] ,... ]

FOR UPDATE [ OF [table].column_name, ... ] [ NOWAIT ];

query_expression ::

query_specification

| query_expression set_operator query_expression

| ( query_expression )

set_operator ::

{ UNION [ ALL ] | INTERSECT | MINUS }

query_specification ::

SELECT [ ALL | DISTINCT ] [ SKIP N ] [ TOP N ]

{

*

| { table_name | alias } . * [, { table_name | alias } . * ] ...

| { { { expr | NULL } [ [ AS ] [ ' ] column_title [ ' ] ] }

|

{ [ [ ' ] column_title [ '] = ] { expr | NULL } }

}

[,

{ { { expr | NULL } [ [ AS ] [ ' ] column_title [ ' ] ] }

|

{ [ [ ' ] column_title [ '] = ] { expr | NULL } }

}

] ...

}

} FROM table_ref [ { ctree ORDERED } ] [ , table_ref [ { ctree ORDERED } ] ...

[ WHERE search_condition ]

[ GROUP BY [table.]column_name

[, [table.]column_name ] ...

[ HAVING search_condition ]

table_ref ::

table_name [ AS ] [ alias [ ( column_alias [ , ... ] ) ] ]

| ( query_expression ) [ AS ] alias [ ( column_alias [ , ... ] ) ]

| [ ( ] joined_table [ ) ]

| procedure_name(proc_arg [,...]) [ AS ] [ alias ]

joined_table ::

table_ref CROSS JOIN table_ref

| table_ref [ INNER | LEFT [ OUTER ] ] JOIN table_ref ON search_condition

Arguments

query_expression

See Query Expressions.

expr

See Expressions

expr can also be a scalar sub-query.

See Scalar Sub-query Expressions

ORDER BY clause
See ORDER BY CLAUSE

FOR UPDATE clause

FROM PIVOT and UNPIVOT

See PIVOT windowing function

See UNPIVOT windowing function

NOWAIT

c-tree supports non-blocking locks with an info type of UPDATE_NOWAIT_SUPPORTED, which returns TRUE or FALSE based on whether the storage system supports NOWAIT or not. If the storage system supports NOWAIT, then SELECT .. FOR UPDATE statements use a new fetch hint TPL_FH_WRITE_WITH_NOWAIT instead of TPL_FH_WRITE. If the row is already locked, instead of waiting (as the old logic would have done), the logic returns the ELCK_UPDATE_NOWAIT error and execution of the statement stops.

Note that the code does not return ELCK_UPDATE_NOWAIT; it returns c-tree error 42 mapped into -17042. The code does not have any check on ELCK_UPDATE_NOWAIT.

procedure_name

A procedure used in a table_ref must return a result_set and may not have any OUT or IN/OUT parameters. That is, only IN parameters are supported.

Row Value Constructors

Row value constructors can be used. For example the following query

select * from rvc where c1 <= 2 and c2 <=2 and c3 <= 2;

can be replaced with this

select * from rvc where (c1,c2,c3) <= (2,2,2);

Note: Use of ODER BY in a FOR UPDATE clause is a non-standard SQL feature as specified by SQL92. Also, full cursor update is not supported by FairCom DB SQL. See UPDATE.

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege
• SELECT permission on all the tables/views referred to in the query_expression.

PIVOT

Declaration

SELECT [ <non-pivoted column> [ AS <column name> ], ]

…

[ <first pivoted column [AS <column name>] ,

[ <second pivoted column [AS <column name>] ,]

…

[ <last pivoted column [AS <column name>] ] ]

FROM

(<SELECT query that produces the data>)

AS <alias for query that produces the data>

PIVOT

(

<aggregation function> ( <aggregated column> )

FOR <column that contains values that will be column headers>

IN ( <first pivoted column>

, <second pivoted column>

, … <last pivoted column>)

) AS <pivot table alias>

[ <optional ORDER BY clause> ]

Description

PIVOT operates on a table-valued expression, transforming unique values from one column in the expression into multiple columns in the result set. PIVOT can also aggregate other columns from the table-valued expression as additional columns in the result set.

It selects data from a table-valued expression, transposing rows to columns. The unique values from one of the columns provides the columns for the resulting result-set.

Example

select * from product_sales;

PRODUCT_NAME STORE_LOCATI NUM_SALES

------------ ------------ ---------

Chair North 55

Desk Central 120

Couch Central 78

Chair South 23

Chair South 10

Chair North 98

Desk West 61

Couch North 180

Chair South 14

Desk North 45

Chair North 87

Chair Central 34

Desk South 42

Couch West 58

Couch Central 27

Chair South 91

Chair West 82

Chair North 37

Desk North 68

Couch Central 54

Chair South 81

Desk North 25

Chair North 46

Chair Central 121

Desk South 85

Couch North 43

Desk West 10

Chair North 5

Chair Central 16

Desk South 9

Couch West 22

Couch Central 59

Chair South 76

Chair West 48

Chair North 19

Desk North 3

Couch West 63

Chair South 81

Desk North 85

Chair North 90

Chair Central 47

Desk West 63

Couch North 28

43 records selected

 

SELECT product_name,

Central, North, South, West

FROM product_sales

PIVOT

(

sum(num_sales)

FOR store_location IN (North, Central, South, West)

) AS pivot_table;

PRODUCT_NAME CENTRAL NORTH SOUTH WEST

------------ ------- ----- ----- ----

Couch 218 251 143

Chair 218 437 376 130

Desk 120 226 136 134

3 records selected

See Also

UNPIVOT

UNPIVOT

Declaration

SELECT [ <non-pivoted column> [ AS <column name> ], ]

…

[ <first pivoted column [AS <column name>] ,

[ <second pivoted column [AS <column name>] ,]

…

[ <last pivoted column [AS <column name>] ] ]

FROM

(<SELECT query that produces the data>)

AS <alias for query that produces the data>

PIVOT

(

<aggregation function> ( <aggregated column> )

FOR <column that contains values that will be column headers>

IN ( <first pivoted column>

, <second pivoted column>

, … <last pivoted column>)

) AS <pivot table alias>

[ <optional ORDER BY clause> ]

Description

UNPIVOT operates on a table-valued expression, like the reverse of PIVOT, rotating columns into column values. UNPIVOT is not an exact reverse of PIVOT as NULL values in UNPIVOT's input will not show in the output and rows have been merged.

It selects data from a table-valued expression, transposing columns to rows.

Example

select * from product_sales;

PRODUCT_NAME STORE_LOCATI NUM_SALES

------------ ------------ ---------

Chair North 55

Desk Central 120

Couch Central 78

Chair South 23

Chair South 10

Chair North 98

Desk West 61

Couch North 180

Chair South 14

Desk North 45

Chair North 87

Chair Central 34

Desk South 42

Couch West 58

Couch Central 27

Chair South 91

Chair West 82

Chair North 37

Desk North 68

Couch Central 54

Chair South 81

Desk North 25

Chair North 46

Chair Central 121

Desk South 85

Couch North 43

Desk West 10

Chair North 5

Chair Central 16

Desk South 9

Couch West 22

Couch Central 59

Chair South 76

Chair West 48

Chair North 19

Desk North 3

Couch West 63

Chair South 81

Desk North 85

Chair North 90

Chair Central 47

Desk West 63

Couch North 28

43 records selected
 

select store_location, product_name, num_sales from pivot_table unpivot ( num_sales for

store_location in (central, south, west, north)) as upvt;

STORE_LOCATION PRODUCT_NAME NUM_SALES

-------------- ------------ ---------

central Couch 218

west Couch 143

north Couch 251

central Chair 218

south Chair 376

west Chair 130

north Chair 437

central Desk 120

south Desk 136

west Desk 134

north Desk 226
 

11 records selected

See Also

PIVOT

SET CONNECTION

Description

SET CONNECTION sets the database associated with the connection name as the current database.

Syntax

SET CONNECTION connection_name ;

connection_name::

{ character_literal | host_variable | DEFAULT }

Notes

If DEFAULT is specified, there should exist a DEFAULT connection (this could have been previously achieved through a CONNECT TO DEFAULT statement). All FairCom DB SQL statements are executed for the current database.

Examples

SET CONNECTION 'salesdb';

SET CONNECTION DEFAULT;
 

Authorization

None. 

SET IDENTITY_INSERT

Description

SET IDENTITY_INSERT toggles ability to insert user defined values into a field defined as an IDENTITY column.

Syntax

SET IDENTITY_INSERT <table> on | off;

Notes

Toggles IDENTITY insert values from an INSERT statement. If the value inserted is larger than the current identity value for the table, the new inserted value is used as the current identity value.

Only one table at a time within a session can be set to IDENTITY_INSERT ON. An error is returned on the second attempt requiring the calling user to first disable support on the current table with SET IDENTITY_INSERT OFF.

Examples

CREATE TABLE salesdb (name CHAR(10), sales_id INTEGER IDENTITY (1,1);

SET IDENTITY_INSERT 'salesdb' ON;

INSERT VALUES ('joe', 199);

SET IDENTITY_INSERT 'salesdb' OFF;

INSERT VALUES ('sally');
 

Authorization

User must own the table or have ALTER permission on the table.

SET TRANSACTION ISOLATION

Description

Explicitly sets the isolation level for a transaction. Isolation levels specify the degree to which one transaction can modify data or database objects being used by another concurrent transaction.

Syntax

SET TRANSACTION ISOLATION LEVEL isolation_level ;

isolation_level ::

READ UNCOMMITTED

| READ COMMITTED

| REPEATABLE READ

| SERIALIZABLE

Arguments

READ UNCOMMITTED

Allows dirty reads, non-repeatable reads, and phantoms (described below in Notes).

READ COMMITTED

Default. Prohibits dirty reads; allows non-repeatable reads and phantoms.

REPEATABLE READ

Prohibits dirty reads and non-repeatable reads; allows phantoms.

SERIALIZABLE

Prohibits dirty reads, non-repeatable reads, and phantoms (see the following notes). It guarantees that concurrent transactions will not affect each other; they behave as if they were executing serially, not concurrently.

Notes

SET TRANSACTION allows the user to choose the isolation level for future transactions. If a transaction is currently active, SET TRANSACTION generates an error.

The isolation level specifies the degree to which one transaction is isolated from the effects of concurrent access of the database by other transactions. The appropriate level of isolation depends on how a transaction needs to be isolated from effects of another transaction. Higher isolation levels provide greater data consistency to the user’s transaction but reduce access to data by concurrent transactions.

The isolation level SERIALIZABLE guarantees the highest consistency. The isolation level READ UNCOMMITTED guarantees the least consistency. Only READ COMMITTED and REPEATABLE READ are supported. The ANSI/ISO standard defines isolation levels in terms of the of the inconsistencies they allow, as detailed next:

Permitted Inconsistencies in Transactions

Authorization

None.

SET SCHEMA

Description

SET SCHEMA specifies a new default qualifier for database object names (database objects include tables, indexes, views, synonyms, procedures, and triggers).

When you connect to a database with a particular user name, that name becomes the default qualifier for database object names. This means you do not have to qualify references to tables, for instance, that were created under the same user name. However, you must qualify references to all other tables with the user name of the user who created them.

SET SCHEMA allows you to change the user name that FairCom DB SQL uses as the default qualifier for database object names. The name specified in SET SCHEMA becomes the new default qualifier for object names.

Note: SET SCHEMA does not change your user name or affect authentication. It only changes the default qualifier.

Syntax

SET SCHEMA ' qualifier_name ' ;

Arguments

' qualifier_name '

The new qualifier name is enclosed in single quotation marks.

Notes

• SET SCHEMA does not check whether qualifier_name is a valid user name.
• Metadata for objects created without an explicit qualifier will show qualifier_name as the owner.
• SET SCHEMA does not start or end a transaction.

Examples

The following interactive SQL example shows changing the default qualifier through SET SCHEMA. The example:

• Invokes ISQL as the user admin, the owner of the system catalog tables
• Queries the systables catalog tables as admin
• Uses SET SCHEMA to change the default qualifier to fred
• Creates a table and queries systables to show that the newly-created table is owned by fred

ISQL> -- What is the user name for the current connection?

ISQL> select user() from syscalctable;

ADMIN

------

admin

1 record selected

ISQL> -- Show the name and owner of non-system tables:

ISQL> select tbl, owner from systables where tbltype <> 'S';

TBL OWNER

--- -----

t1 admin

test admin

test ctree

3 records selected

ISQL> set schema 'fred';

ISQL> create table freds_table (c1 int);

ISQL> create index freds_table_ix on freds_table (c1);

ISQL> select tbl, owner from systables where tbltype <> 'S';

select tbl, owner from systables where tbltype <> 'S';

*

error(-20005): Table/View/Synonym not found

ISQL> -- Oops! Must now qualify references to the admin-owned tables:

ISQL> select tbl, owner from admin.systables where tbltype <> 'S';

TBL OWNER

--- -----

t1 admin

test admin

test ctree

freds_table fred

4 records selected

Authorization

None.

TRUNCATE TABLE

Description

Delete all the rows of a table in a single action.

(In V10.3 and later) Any table truncated using TRUNCATE will have the $DELFLD$ set to 4 bytes despite the size in the original table.

Syntax

TRUNCATE TABLE [ table_name ]

Arguments

table_name

Specifies a single table to truncate.

Authorization

The user must have the DBA privilege or SELECT privilege on all the tables in the database. To issue the TRUNCATE TABLE statement for a specific table, the user must be the owner or have UPDATE privilege on the table.

UPDATE

Description

Updates the columns of the specified table with the given values that satisfy the search_condition.

Syntax

UPDATE table_name

SET assignment, assignment, ...

[ WHERE search_condition ]

assignment ::

column = { expr | NULL }

| ( column, column, ... ) = ( expr, expr, ... )

| ( column, column, ... ) = ( query_expression )

Arguments

If the optional WHERE clause is specified, then only rows that satisfy the search_condition are updated. If the WHERE clause is not specified then all rows of the table are updated.

The expressions in the SET clause are evaluated for each row of the table if they are dependent on the columns of the target table.

If a query expression is specified on the right hand side for an assignment, the number of expressions in the first SELECT clause of the query expression must be the same as the number of columns listed on the left hand side of the assignment.

If a query expression is specified on the right hand side for an assignment, the query expression must return one row.

Expressions other than the query expressions mentioned above can be scalar subqueries. A scalar sub-query returns one value. The expressions and search conditions can also contain scalar sub-queries. Refer to Scalar Sub-query Expressions for more information on the scalar sub-queries.

If a table has check constraints and if the columns to be updated are part of a check expression, then the check expression is evaluated. If the result of evaluation is FALSE, the UPDATE statement fails.

If a table has primary/candidate keys and if the columns to be updated are part of the primary/candidate key, a check is made as to whether there exists any corresponding row in the referencing table. If so, the UPDATE operation fails.

IDENTITY columns cannot be updated and hence cannot be specified as one of the column names for assignment.

Examples

UPDATE orders

SET qty = 12000

WHERE order_no = 1001 ;

UPDATE orders

SET (product) =

(SELECT item_name

FROM items

WHERE item_no = 2401

)

WHERE order_no = 1002 ;

UPDATE orders

SET (amount) = (2000 * 30)

WHERE order_no = 1004 ;

UPDATE orders

SET (product, amount) =

(SELECT item_name, price * 30

FROM items

WHERE item_no = 2401

)

WHERE order_no = 1002 ;

UPDATE orders

SET status='delivered'

WHERE EXISTS (SELECT status

FROM orders o,customers c

WHERE o.cust_id=c.id AND c.name='FairCom');

UPDATE orders

SET product = (

SELECT item_name

FROM items

WHERE item_no = 2401),

amount = (

SELECT price * 30

FROM items

WHERE item_no = 2401)

WHERE order_no = 1002 ;

UPDATE item

SET stock = stock + (

SELECT SUM(order_item.quantity)

FROM order_item

WHERE order_item.order_no = 341

AND order_item.item_no = item.item_no)

WHERE item_no = (

SELECT MAX(item_no)

FROM order_item

WHERE order_no = 341);

Authorization

The user executing this statement must have:

• DBA privilege.
• UPDATE privilege on all the specified columns of the target table and SELECT privilege on all the other tables referred to in the statement.

UPDATE STATISTICS

Description

Queries system tables and updates table and column statistics:

• The number of rows in the table (the cardinality)
• The approximate number of occurrences of a value in each column

The optimizer uses the information from UPDATE STATISTICS to calculate a query strategy for a particular FairCom DB SQL statement.

Until a user, application, or FairCom DB SQL script issues an UPDATE STATISTICS statement, the optimizer bases query strategies on values it generates from various defaults. These values will not lead to the best performance, so it is good practice for database administrators to periodically update statistics.

UPDATE STATISTICS only works on tables that have indexes defined on them.

Syntax

UPDATE STATISTICS [ FOR table_name ]

Arguments

table_name

Specifies a single table on which to update statistics. The default is to update statistics on all tables in the database.

Authorization

To issue the UPDATE STATISTICS statement for all tables in the database, the user must have DBA privilege or SELECT privilege on all the tables in the database. To issue the UPDATE STATISTICS statement for a specific table, the user must be the owner or have SELECT privilege on the table.