* sql commands *


<[ABORT BACKUP]>
    abort-backup-statement ::=
            ABORT BACKUP

<[ABORT CONNECTION]>
    abort-connection-statement ::=
            ABORT CONNECTION connection-id
            
<[ADD user to GROUP]>
    add-user-to-group-statement ::=
            ADD sec-user [,sec-user]... TO GROUP group-name

<[ADD jar file]>
    add-jar-file-statement ::=
            ADD JARFILE [owner.]logical_file_name physical_jarfile_name;

<[ADD TRACE]>
    add-trace-on-table ::=
            ADD TRACE ON table-name

<[ALTER DATAFILE]>
    alter-datafile-statement ::=
            ALTER DATAFILE filename ADD unsigned-integer PAGES

<[ALTER INDEX]>
    alter-index-statement ::=
            ALTER INDEX index-identifier ON base-table-name RENAME TO index-identifier

<[ALTER PASSWORD]>
    alter-password-statement ::=
              ALTER PASSWORD old-pwd TO new-pwd
            | ALTER PASSWORD OF user-name TO new-pwd

<[ALTER TDE PASSWORD]>
    alter-tde-password-statement ::=
              ALTER TDE PASSWORD [old-pwd] TO new-pwd

<[ALTER ACL ORDER]>
    alter-acl-order-statement ::=
              ALTER ACL ORDER OF user-name TO ALLOW [BLOCK]
            | ALTER ACL ORDER OF user-name TO BLOCK [ALLOW]

<[ALTER MAX CONNECTION]>
    alter-max-connection-statement ::=
              ALTER MAX CONNECTION OF user-name TO integer

<[ALTER REPLICATION]>
    alter-replication-statement ::=
            ALTER REPLICATION replication-name ON base-table-name
            { ADD REPLICATE TO 
                 {remote-table-name 
                   [(column-identifier [, column-identifier] ...)]  
                   [CLEAR DATA | FLUSH DATA | CLEAR AND FLUSH DATA] 
                 [, remote-table-name 
                   [(column-identifier [,column-identifier,] ...)]  
                   [CLEAR DATA | FLUSH DATA | CLEAR AND FLUSH DATA] ] 
                 ...                            
            | DROP REPLICATE TO 
                 { remote-table-name 
                 [, remote-table-name 
                 ...
            }

<[ALTER SCHEDULE]>
    alter-schedule-statement ::=
            ALTER SCHEDULE FOR REPLICATION TO remote-db-name
            { BEGIN AT yy/mm/dd hh:mm:ss EVERY n DAYS
            | BEGIN AT yy/mm/dd hh:mm:ss EVERY hh:mm:ss
            | BEGIN AT yy/mm/dd hh:mm:ss EVERY n DAYS AND hh:mm:ss
            | IDENTIFIED BY user-name [password]
            | RETRY n TIMES [AFTER n SECONDS]
            | STOP ON ERROR
            | IGNORE ON ERROR}

<[ALTER UNION VIEW]>
    alter-union-view-statement ::=
            ALTER UNION VIEW base-table-name
            { attach base-table-name
            | detach base-table-name
            | ADD TABLE base-table-name  [IN tablespace-name] check-table-constraint [LOCK MODE {TABLE | PAGE | ROW}] [FILLFACTOR unsigned-integer] [NOCACHE]
            | add-primary-key-def
            | add-foreign-key-def
            | add-unique-key-def
            | DROP PRIMARY KEY
            | DROP FOREIGN KEY foreign-key-name
            | ADD  [COLUMN] alter-column-definition
            | ADD  (alter-column-definition
                      [, alter-column-definition] ...)
            | DROP [COLUMN] column-identifier {RESTRICT | CASCADE}
            | DROP [COLUMN](column-identifier
                      [, column-identifier] ...) {RESTRICT | CASCADE}
            | MODIFY modify-column-definition
            | MODIFY (modify-column-definition
                            [, modify-column-definition] ...)
            | RENAME TO base-table-name
            }

<[ALTER TABLE]>
    alter-table-statement ::=
            ALTER TABLE base-table-name
            { add-primary-key-def
            | add-foreign-key-def
            | add-unique-key-def
            | DROP PRIMARY KEY
            | DROP FOREIGN KEY foreign-key-name
            | ADD  [COLUMN] alter-column-definition
            | ADD  DYNAMIC [COLUMN] alter-column-definition
            | ADD  (alter-column-definition
                      [, alter-column-definition] ...)
            | DROP [COLUMN] column-identifier {RESTRICT | CASCADE}
            | DROP DYNAMIC [COLUMN] column-identifier
            | DROP [COLUMN](column-identifier
                      [, column-identifier] ...) {RESTRICT | CASCADE}
            | MODIFY modify-column-definition
            | MODIFY DYNAMIC column-identifier TYPE TO data-type give-opt
            | MODIFY (modify-column-definition
                            [, modify-column-definition] ...)
            | SET DYNAMIC MAX max-value
            | SET NOCACHE {ON | OFF}
            | SET FILLFACTOR unsigned-integer
            | SET LOCK MODE {PAGE | TABLE | ROW }
            | SET SERIAL signed-integer
            | RENAME TO base-table-name
            | MOVE TABLESPACE tablespace-name
            }

<(alter-column-definition)>
    alter-column-definition ::=
            column-definition give-opt column-sequence

<(modify-column-definition)>
    modify-column-definition ::=
            attribute-modify-column-def
            | full-attributes-modify-column-def

<(attribute-modify-column-def)>
    attribute-modify-column-def ::=
            column-identifier NAME TO column-identifier
            | column-identifier TYPE TO data-type give-opt
            | column-identifier NULL TO NOT NULL give-opt
            | column-identifier NOT NULL TO NULL
            | column-identifier SET DEFAULT [SYSTEM|USER] default-val [ON UPDATE]
            | column-identifier DEFAULT TO [SYSTEM|USER] default-val [ON UPDATE]
            | column-identifier CONSTRAINT TO check-opt
            | column-identifier BEFORE column-identifier 
            | column-identifier AFTER  column-identifier 
            | column-identifier DROP DEFAULT
            | column-identifier DROP CONSTRAINT
			| column-identifier {ENCRYPT | DECRYPT}

<(full-attributes-modify-column-def)>
    column-identifier TO alter-column-definition

<(give-opt)>
    give-opt ::=
            GIVE default-val
            | GIVE SEQUENTIAL
            | GIVE SEQ

<(primary-key-def)>
    primary-key-def ::=
       [CONSTRAINT constraint-name ] PRIMARY KEY 
                    (column-identifier [, column-identifier] ...) [DISABLE]
       | [CONSTRAINT constraint-name ] PRIMARY KEY column-identifier [DISABLE]

<(add-primary-key-def)>
    add-primary-key-def ::=
       [ADD [CONSTRAINT constraint-name]] PRIMARY KEY 
                    (column-identifier [, column-identifier] ...)
                    [IN tablespace-name] [DISABLE]
       | PRIMARY KEY column-identifier [IN tablespace-name] [DISABLE]

<(add-unique-key-def)>
    add-unique-key-def ::=
       [ADD [CONSTRAINT constraint-name]] UNIQUE
                    (column-identifier [, column-identifier] ...)
                    [IN tablespace-name]

<(unique-key-def)>
    unique-key-def ::=
       [CONSTRAINT constraint-name ] UNIQUE
                    (column-identifier [, column-identifier] ...)
       | [CONSTRAINT constraint-name ] UNIQUE column-identifier

<(foreign-key-def)>
    foreign-key-def ::=
            FOREIGN KEY foreign-key-name (referencing-columns)
                   references-key
            | FOREIGN KEY foreign-key-name column-identifier
                   references-key
            | CONSTRAINT constraint-name FOREIGN KEY (referencing-columns)
                   references-key

<(add-foreign-key-def)>
    foreign-key-def ::=
            [ADD [CONSTRAINT constraint-name]] FOREIGN KEY foreign-key-name 
                  (referencing-columns) references-key
            | [ADD [CONSTRAINT constraint-name]] FOREIGN KEY foreign-key-name 
                  column-identifier references-key

<(col-key-definition)>
    col-key-definition ::=
            [CONSTRAINT constraint-name] PRIMARY KEY
            | [CONSTRAINT constraint-name] UNIQUE
            | [CONSTRAINT constraint-name] references-key

<(references-key)>
    references-key ::=
            REFERENCES base-table-name
              [(referenced-columns)]
              [referential-triggered-action]

<(column-sequence)>
    column-sequence ::=
            BEFORE column-identifier
            | AFTER column-identifier

<(check-opt)>
    check-opt ::=
            CHECK expression

<(referential-triggered-action)>
    referential-triggered-action ::=
            update-rule [delete-rule]
            | delete-rule [update-rule]

<(update-rule)>
    update-rule ::=
            ON UPDATE referential-action

<(delete-rule)>
    delete-rule ::=
            ON DELETE referential-action

<(referential-action)>
    referential-action ::=
            CASCADE
            | SET NULL
            | SET DEFAULT
            | NO ACTION

<[ALTER TABLESPACE]>
    alter-tablespace-statement ::=
            ALTER TABLESPACE tablespace-name 
            { ADD DATAFILE tsfile
        | DROP DATAFILE filename
            | SET AUTOEXTEND OFF
            | SET AUTOEXTEND ON
            | SET READ WRITE
            | SET READ ONLY
            }

<[ALTER TRIGGER]>
    alter-trigger-statement ::=
            ALTER TRIGGER trigger-name 
            { ON base-table-name { ENABLE | DISABLE }
            | REPLACE WITH { BEFORE | AFTER } trigger-event
              ON base-table-name
              triggered-action 
            }

<[ALTER VIEW]>
    alter-view-statement ::=
            ALTER VIEW { view-name | ALL } COMPILE

<[BEGIN BACKUP]>
    begin-backup-statement ::=
            BEGIN BACKUP
            | BEGIN INCREMENTAL BACKUP [TO CURRENT]

<[BEGIN END]>
    anonymous-procedure ::=
            BEGIN sql-procedure-statement; END

<[CALL PROCEDURE]>
    call-procedure-statement ::=
            [? =] CALL procedure-name 
                [(procedure-value [, procedure-value ...])]


<[CHECK]>
    check-statement ::=
            CHECK {CATALOG
                  |DB
                  |FILE  file-name
                  |INDEX table-name.index-identifier
                  |TABLE table-name
                  |TABLESPACE tablespace-name}


<[CHECKPOINT]>
    checkpoint-statement ::=
            CHECKPOINT


<[COMMIT]>
    commit-statement ::=
            COMMIT [WORK]


<[CONNECT]>
    connect-statement ::=
            CONNECT TO db-name [user-name [password]]


<[CREATE DB]>  
    create-db-statement ::=
            CREATE {DB | DATABASE} db-name

<[CREATE COMMAND]>
    create-command-statement ::=
            CREATE [OR REPLACE] COMMAND command-name AS dml-statement

<(dml-statement)>
    dml-statement ::=
    { select-statement 
      | insert-statement
      | update-statement-positioned
      | update-statement-searched
      | delete-statement-positioned
      | delete-statement-searched  
      | select-into-statement
    }

                    
<[CREATE DOMAIN]>  
    create-domain-statement ::=
            CREATE DOMAIN domain-name [AS] data-type [ENCRYPT]
            [TEXT CONVERTER function-name]
            [default-clause]
            [domain-constraint]


<[CREATE GROUP]>
    create-group-statement ::=
            CREATE GROUP group-name


<[CREATE INDEX]>
    create-index-statement ::=
            CREATE [UNIQUE | AUTO] INDEX index-identifier
            ON {base-table-name | UNION VIEW base-table-name}
            ({column-identifier | expression}  [ASC|DESC]
            [, {column-identifier | expression} [ASC|DESC]]...)
            [WHERE search-condition]
            [IN tablespace-name]
            [FILLFACTOR unsigned-integer]
            [DISABLE]


<[CREATE PROCEDURE]>
    create-procedure-statement ::=
            CREATE [OR REPLACE] PROCEDURE procedure-name 
                [(procedure-parameter [, procedure-parameter ...])] 
                { 
                   [RETURNS STATUS]
                 | [RETURNS [STATUS,] procedure-result [,procedure-result ...]]
                [LANGUAGE JAVA FROM package.class.method([ ' argtype[,argtype ...] ' ]) , 
                [owner.]java-sourcecode-jar-file [, owner.related-jar-file]]
                }
          | CREATE [OR REPLACE] PROCEDURE FROM source-file-path
          | CREATE [OR REPLACE] PROCEDURE procedure-name 
                [(sql-procedure-parameter [, sql-procedure-parameter ...])] 
                LANGUAGE SQL
                BEGIN
                     sql-procedure-statement;
                END
          | CREATE [OR REPLACE] [LOCAL | GLOBAL] TEMP PROCEDURE procedure-name 
                [(sql-procedure-parameter [, sql-procedure-parameter ...])] 
                LANGUAGE SQL
                BEGIN
                     sql-procedure-statement;
                END
      

<[CREATE REPLICATION]>
    create-replication-statement ::=
            CREATE [ASYNC] REPLICATION replication-name 
            WITH PRIMARY AS {base-table-name | UNION VIEW base-table-name}
               [(column-identifier [, column-identifier] ...)] 
               [WHERE search-condition] 
            REPLICATE TO 
               { remote-table-name
                 [(column-identifier [, column-identifier] ...)]  
                 [CLEAR DATA | FLUSH DATA | CLEAR AND FLUSH DATA] 
                 [NO CASCADE]
               [, remote-table-name 
                 [(column-identifier [,column-identifier,] ...)]  
                 [CLEAR DATA | FLUSH DATA | CLEAR AND FLUSH DATA] 
                 [NO CASCADE]]
                
               ...


<[CREATE SCHEDULE]>
    create-schedule-statement ::=
            CREATE SCHEDULE FOR [EXPRESS] REPLICATION TO remote-db-name
            [(ORACLE | INFORMIX | SYBASE | MICROSOFT)] 
            BEGIN AT yy/mm/dd hh:mm:ss
            {EVERY n DAYS | EVERY hh:mm:ss | EVERY n DAYS AND hh:mm:ss}
            [RETRY n TIMES [AFTER n SECONDS]]
            [STOP ON ERROR]
            [WITH NO CHECK]
            IDENTIFIED BY user-name [password]


<[CREATE SCHEMA]>
    create-schema-statement ::=
            CREATE SCHEMA {schema-name | [schema-name] AUTHORIZATION user-name}


<(schema-name)>    
            schema-name ::= user-defined-name


<[CREATE SYNONYM]>
    create-synonym-statement ::=
            CREATE [OR REPLACE] SYNONYM synonym-name FOR
             {remote-table-name | view-name}

<[CREATE TABLE]>
    create-table-statement ::=
            CREATE { [[LOCAL] TEMP | [LOCAL] TEMPORARY] | MEMORY } TABLE
             base-table-name (column-element [,column-element] ...
             [[,primary-key-def] | [,unique-key-def] | [,foreign-key-def] | 
             [,check-table-constraint]])
            [IN tablespace-name]
            [LOCK MODE {TABLE | PAGE | ROW}]
            [FILLFACTOR unsigned-integer]
            [NOCACHE]


<(column-element)>
            column-element ::= column-definition


<(column-definition)> 
            column-definition ::=
                column-identifier data-type [ENCRYPT | DECRYPT]
                [NULL | NOT NULL] [default-clause]
                | [col-key-definition]
                | [check-column-constraint]


<(check-column-constraint)>
            check-column-constraint ::=
                [CONSTRAINT constraint-name] CHECK expression

<(check-table-constraint)>
            check-table-constraint ::=
                [CONSTRAINT constraint-name] CHECK expression

<[CREATE TABLE AS SELECT]>
    create-table-as-select-statement ::=
            CREATE TABLE table-name [(column-identifier[,column-identifier]...)]
            AS [(] [ basic-select-statement | select-union-select-statement | select-except-select-statement ] [)]
            [IN tablespace-name]
            [LOCK MODE {TABLE | PAGE | ROW}]
            [FILLFACTOR unsigned-integer]
            [NOCACHE]

<[CREATE TABLESPACE]>
    create-tablespace-statement ::=
            CREATE [AUTOEXTEND] TABLESPACE tablespace-name [backup-mode]
            DATAFILE [tsfile [,tsfile]....]

<[CREATE TEXT INDEX]>
    create-text-index-statement ::=
            CREATE [SIGNATURE] TEXT INDEX index-identifier
            ON {base-table-name | UNION VIEW base-table-name}
            (column-identifier[,column-identifier]...)
            [TOTAL TEXT SIZE unsigned-integer MB]
            [SCALE unsigned-integer]
            [ORDER BY column-identifier [ASC|DESC]]

<[CREATE IVF TEXT INDEX]>
    create-ivf-text-index-statement ::=
            CREATE IVF TEXT INDEX index-identifier
            ON {base-table-name | UNION VIEW base-table-name}
            (column-identifier[,column-identifier]...)
            [STORAGE PATH storage-path]
            [TOTAL TEXT SIZE unsigned-integer MB]
            [ORDER BY column-identifier [ASC|DESC]]

<[CREATE HASH INDEX]>
    create-hash-index-statement ::=
            CREATE HASH INDEX index-identifier
            ON base-table-name (column-identifier[,column-identifier]...)
            [BUCKET unsigned-integer]

<[CREATE TRIGGER]>
    create-trigger-statement ::= 
            CREATE [OR REPLACE] TRIGGER trigger-name 
            { BEFORE | AFTER } trigger-event
            ON {base-table-name | UNION VIEW base-table-name}
            triggered-action


<[CREATE VIEW]>
    create-view-statement ::=
            CREATE [OR REPLACE] VIEW view-name 
            [(column-identifier[,column-identifier]...)]
            AS [(] [ basic-select-statement | select-union-select-statement | select-except-select-statement ] [)]

<[CREATE UNION VIEW]>
    create-union-view-statement ::=
            CREATE [OR REPLACE] UNION VIEW view-name 
            FOR table-identifier[,table-identifier]...
            PARTITION BY column-identifier[,column-identifier]...

<[CREATE DATABASE LINK]>
    create-database-link ::= 
            CREATE [PUBLIC|PRIVATE] DATABASE LINK link-identifier
            CONNECT TO remote-identname
            [IDENTIFIED BY user-name [password]]

<[CLOSE DATABASE LINK]>
    close-database-statement ::=
            CLOSE DATABASE LINK close-object

<(close-object)>
    close-object ::= 
      ALL
    | NONACTIVE
    | link-identifier
    | remote-identname

<(remote-identname)>
    remote-identname ::=
    database-identifier
    | database-identifier@host-identifier

<(host-identifier)>
    host-identifier ::=
    identifier
    | identifier.host-identifier

<[DELETE-STATEMENT-POSITIONED]>  /* program mode */
    delete-statement-positioned ::=
            DELETE FROM remote-table-name [table-option] WHERE CURRENT OF cursor-name


<[DELETE-STATEMENT-SEARCHED]>
    delete-statement-searched ::=
            DELETE FROM remote-table-name [table-option] [WHERE search-condition]

<[DEF-PROC]>
    def-proc-statement ::=
            DEF PROC|PROCEDURE procedure-name

<[DEF-TABLE]>
    def-table-statement ::=
            DEF TABLE table-name

<[DEF-VIEW]>
    def-view-statement ::=
            DEF VIEW view-name

<[DISCONNECT]>
    disconnect-statement ::=
            DISCONNECT [all]

<[DISABLE INDEX]>
    disable-index-statement ::=
            DISABLE INDEX index-identifier FOR table-name 
          | DISABLE INDEX ALL FOR table-name

<[DROP COMMAND]>
    drop-command-statement ::=
            DROP COMMAND [IF EXISTS] command-name

<[DROP DOMAIN]>
    drop-domain-statement ::=
            DROP DOMAIN domain-name {RESTRICT | CASCADE}


<[DROP GROUP]>
    drop-group-statement ::=
            DROP GROUP group-name


<[DROP INDEX]>
    drop-index-statement ::=
            DROP INDEX index-identifier FROM {table-name | UNION VIEW table-name}

<[DROP PROJECT]>
    drop-project-statement ::=
            DROP PROJECT project-name

<[DROP PROCEDURE]>
    drop-procedure-statement ::=
            DROP PROCEDURE [IF EXISTS] procedure-name


<[DROP REPLICATION]>
    drop-replication-statement ::=
            DROP REPLICATION replication-name FROM {base-table-name | UNION VIEW base-table-name}

<[DROP SCHEDULE]>
    drop-schedule-statement ::=
            DROP SCHEDULE FOR REPLICATION TO remote-db-name

<[DROP SCHEMA]>     
    drop-schema-statement ::=
            DROP SCHEMA schema-name {RESTRICT | CASCADE}


<[DROP SYNONYM]>
    drop-synonym-statement ::=
            DROP SYNONYM [IF EXISTS] synonym-name


<[DROP TABLE]>
    drop-table-statement ::=
            DROP TABLE [IF EXISTS] table-name {RESTRICT | CASCADE}


<[DROP TABLESPACE]>
    drop-tablespace-statement ::=
            DROP TABLESPACE tablespace-name

<[DROP TEXT INDEX]>
    drop-text-index-statement ::=
            DROP TEXT INDEX index-identifier FROM {table-name | UNION VIEW table-name}

<[DROP TRIGGER]>
    drop-trigger-statement :: = 
            DROP TRIGGER [IF EXISTS] trigger-name FROM {table-name | UNION VIEW table-name}
    
<[DROP VIEW]>
    drop-view-statement ::=
            DROP VIEW [IF EXISTS] view-name {RESTRICT | CASCADE}

<[DROP DATABASE LINK]>
    drop-database-statement ::=
            DROP [PUBLIC|PRIVATE] DATABASE LINK link-identifier

<[END BACKUP]>
    end-backup-statement ::=
            END BACKUP {DATAFILE | JOURNAL}

<[EXECUTE COMMAND]>
    execute-command-statement ::=
            EXECUTE COMMAND command-name {(command-value[,command-value]...)}

<[EXECUTE PROCEDURE]>
    execute-procedure-statement ::=
            EXECUTE PROCEDURE procedure-name 
                [(procedure-value [, procedure-value ...])]

<[EXPORT-STATEMENT]>
    export-statement ::=
            EXPORT { [INTO data-file] TABLE table-name |
                     INTO data-file FROM (select-statement) }
            [DESCRIPTION description-file]
            [LOG log-file]
            [STOP_ON_ERROR]

<[GRANT ACCESS]>
    grant-access-statement ::=
            GRANT ACCESS TO {PUBLIC | auth-user-list} ip-address-list
      
<[GRANT IP PRIVILEGE]>
    grant-ip-privilege-statement ::=
            GRANT {ALLOW | BLOCK} TO {PUBLIC | auth-user-list} ip-address-list
            
<[GRANT]>
    grant-statement ::=
            GRANT {ALL [PRIVILEGES] | grant-table-privilege | grant-column-privilege}
            ON table-name
            TO {PUBLIC | auth-user-list}
            | GRANT {SYSDBA|DBA|RESOURCE} TO auth-user-list
            | GRANT CONNECT 
              TO user-name [password] [max-connection-clause] [, user-name [password] [max-connection-clause]]...
            | GRANT EXECUTE ON {COMMAND | PROJECT | PROCEDURE} exeobj-name
              TO {PUBLIC | auth-user-list} 

<(grant-table-privilege)>
            grant-table-privilege ::=
                {DELETE | SELECT| INDEX | ALTER | UPDATE | INSERT | REFERENCE}
                [, grant-table-privilege]...

<(grant-column-privilege)>
            grant-column-privilege ::=
                  UPDATE [(column-identifier[,column-identifier]...)]
                | INSERT [(column-identifier[,column-identifier]...)]
                | REFERENCE [(column-identifier[, column-identifier]...)]

<(max-connection-clause)>
            max-connection-clause ::=
                  WITH MAX CONNECTION integer
          Comment:
          The valid range for max connection is:
                 -1: cannot connect
                  0: no limit for connection
          1-license: limit user's active number of max connection, the max value 
                     is user license

<[INSERT-STATEMENT]>
    insert-statement ::=
            INSERT [OR REPLACE] INTO remote-table-name [(column-identifier[,column-identifier]...)]
            { VALUES (insert-value[,insert-value]...)
            | DEFAULT VALUES
            | select-order-by-statement }

<[IMPORT-STATEMENT]>
    import-statement ::=
            IMPORT table-name [FROM data-file]
            [DESCRIPTION description-file]
            [LOG log-file]
            [STOP_ON_ERROR]

<[KILL]>
    kill-statement ::=
            KILL CONNECTION connection-id

    
<[LOCK TABLE]>
    lock-table-statement ::=
            LOCK TABLE table-name IN {SHARE|EXCLUSIVE} MODE [WAIT|NO WAIT]


<[MOVE FILE OBJECT]>
    move-file-object-statement ::=
            MOVE FILE OBJECT old-file-name TO new-file-name

<[REBUILD INDEX]>
    rebuild-index-statement ::=
            REBUILD INDEX index-identifier FOR {table-name | UNION VIEW table-name} [IN tablespace-name]
          | REBUILD INDEX ALL FOR table-name
          | REBUILD INDEX index-identifier FOR table-name EXPRESS
          | REBUILD INDEX ALL FOR table-name EXPRESS

<[REBUILD TEXT INDEX]>
    rebuild-text-index-statement ::=
            REBUILD TEXT INDEX index-identifier FOR {table-name | UNION VIEW table-name} [FULL|INCREMENTAL]

<[REBUILD COMMAND]>
    rebuild-command-statement ::=
            REBUILD COMMAND command-identifier

<[REBUILD SQLSP]>
    rebuild-sqlsp-statement ::=
            REBUILD PROCEDURE {sqlsp-identifier | ALL}

<[REMOVE jar file]>
    remove-jar-file-statement ::=
            REMOVE JARFILE [owner.]logical_file_name;

<[REMOVE TRACE]>
    remove-trace-on-table ::=
                        REMOVE TRACE ON table-name

<[REMOVE user FROM GROUP]>
    remove-user-from-group-statement ::=
            REMOVE sec-user [,sec-user]... FROM GROUP group-name

<[REMOVE MODULE]>
    remove-module-from-project-statement ::=
            REMOVE MODULE [user-name.]module-name FROM PROJECT project-name

<[RESUME SCHEDULE]>
    resume-schedule-statement ::
            RESUME SCHEDULE FOR REPLICATION TO remote-db-name

<[REVOKE ACCESS]>
    revoke-access-statement ::=
            REVOKE ACCESS FROM { PUBLIC | auth-user-list} ip-address-list

<[REVOKE IP PRIVILEGE]>
    revoke-ip-privilege-statement ::=
            REVOKE {ALLOW | BLOCK} FROM { PUBLIC | auth-user-list} ip-address-list
          | REVOKE {ALLOW | BLOCK} FROM user-name ALL
<[REVOKE]>
    revoke-statement ::=
             REVOKE {ALL [PRIVILEGES] | revoke-table-privilege | revoke-column-privilege}
             ON table-name
             FROM { PUBLIC | auth-user-list}
            | REVOKE {SYSDBA|DBA|RESOURCE|CONNECT} {RESTRICT | CASCADE}
             FROM auth-user-list
            | REVOKE EXECUTE ON {COMMAND | PROJECT | PROCEDURE} exeobj-name
             FROM auth-user-list
        
<(revoke-table-privilege)>
            revoke-table-privilege ::=
                {DELETE | SELECT| INDEX | ALTER | UPDATE | INSERT | REFERENCE}
                [, revoke-table-privilege]...

<(revoke-column-privilege)>
            revoke-column-privilege ::=
                  UPDATE [(column-identifier[,column-identifier]...)]
                | INSERT [(column-identifier[,column-identifier]...)]
                | REFERENCE [(column-identifier[, column-identifier]...)]

<[ROLLBACK]>
    rollback-statement ::=
            ROLLBACK {[WORK] | TO [SAVEPOINT] savepoint-name}


<[SAVEPOINT]>
    savepoint-statement ::=
            SAVEPOINT savepoint-name


<[SELECT]>
    select-statement ::=
              basic-select-syntax limit-syntax
            | select-union-select-statement limit-syntax
            | select-except-select-statement limit-syntax

    
<(basic-select-syntax)>
    basic-select-syntax ::=
              select-where-syntax 
            | select-group-by-syntax


<(select-where-syntax)> 
    select-where-syntax ::=
            SELECT [ALL | DISTINCT] select-list
            FROM [SEQUENCE | SEQ] table-source-list
            [WHERE search-condition]


<(select-group-by-syntax)>
    select-group-by-syntax ::=
            select-where-syntax 
            GROUP BY group-by-specification[,group-by-specification]...
            [USING { SORT | HASH }] [HAVING search-condition]


<[SELECT ORDER BY]>
    select-order-by-statement ::=
            select-statement
            ORDER BY sort-specification [,sort-specification]...


<[SELECT UNION SELECT]> 
    select-union-select-statement ::=
              basic-select-syntax UNION [ALL] basic-select-syntax
            | basic-select-syntax UNION [ALL] 
              ( basic-select-syntax 
                [ORDER BY sort-specification [,sort-specification] ...]
                limit-syntax
              )

<[SELECT EXCEPT SELECT]> 
    select-except-select-statement ::=
              basic-select-syntax EXCEPT basic-select-syntax
            | basic-select-syntax EXCEPT 
              ( basic-select-syntax 
                [ORDER BY sort-specification [,sort-specification] ...]
                limit-syntax
              )

<[SELECT FOR BROWSE]>  /* dirty read */
    select-for-browse-statement ::=
              select-statement FOR BROWSE
            | select-union-select-statement FOR BROWSE
            | select-except-select-statement FOR BROWSE


<[SELECT FOR UPDATE]>  /* program mode */
    select-for-update-statement ::=
            SELECT [ALL | DISTINCT] select-list
            FROM remote-table-name
            [WHERE search-condition]
            FOR UPDATE [OF column-name[,column-name]...]


<[SELECT INTO]>
    select-into-statement ::=
              select-order-by-statement
              INTO [TEMPORARY [TABLE] | MEMORY [TABLE] | TABLE] remote-table-name
              [(column-identifier[,column-identifier]...)]
            | SELECT [ALL | DISTINCT] select-list INTO remote-table-name
              [(column-identifier[,column-identifier]...)]
              FROM [SEQUENCE | SEQ] table-source-list
              [WHERE search-condition]
              [GROUP BY (column-identifier [, column-identifier] ...)]
              [USING { SORT | HASH }] [HAVING search-condition]
              [ORDER BY sort-specification [,sort-specification] ...]

<[SELECT WITHOUT FROM]>
    select-without-from-statement ::=
              SELECT select-nofrom-list

<(limit-syntax)>
    limit-statement ::=
              LIMIT [offset,] rows
            | LIMIT rows OFFSET offset

<[SHRINK DATAFILE]>
    shrink-datafile-statement ::=
            SHRINK DATAFILE filename TRUNCATEONLY [ WITH n FREE PAGES ]
 
<[SHRINK TABLESPACE]>
    shrink-tablespace-statement ::=
            SHRINK TABLESPACE tablespace-name
            {
              TRUNCATEONLY [ WITH n FREE PAGES ]
            | COMPRESSONLY [[WITH] n [PAGES|MB]]
            }
 
<[START DB]>
    start-db-statement ::=
            START {DB | DATABASE} db-name [user-name] [password]


<(statement)>
    statement ::= 
              abort-backup-statement
            | add-user-to-group-statement
            | add-jar-file-statement
            | alter-datafile-statement
            | alter-index-statement
            | alter-password-statement
            | alter-acl-order-statement
            | alter-max-connection-statement
            | alter-replication-statement
            | alter-schedule-statement
            | alter-table-statement
            | alter-tablespace-statement
            | alter-trigger-statement
            | alter-union-view-statement
            | alter-view-statement
            | begin-backup-statement
            | begin-work-statement
            | check-statement
            | checkpoint-statement
            | commit-statement  
            | connect-statement  
            | create-db-statement  
            | create-domain-statement
            | create-group-statement  
            | create-index-statement  
            | create-table-statement  
            | create-table-as-select-statement  
            | create-replication-statement
            | create-schedule-statement  
            | create-synonym-statement  
            | create-tablespace-statement
            | create-union-view-statement
            | create-view-statement 
            | delete-statement-positioned   /* program mode */
            | delete-statement-searched  
            | drop-domain-statement  
            | drop-group-statement  
            | drop-index-statement  
            | drop-command-statement
            | drop-project-statement
            | drop-procedure-statement
            | drop-replication-statement
            | drop-schedule-statement
            | drop-synonym-statement  
            | drop-table-statement  
            | drop-tablespace-statement  
            | drop-view-statement  
            | end-backup-statement
            | grant-access-statement  
            | grant-ip-privilege-statement
            | grant-statement  
            | insert-statement  
            | lock-table-statement  
            | move-file-object-statement
            | remove-jar-file-statement
            | remove-module-from-project-statement
            | remove-datafile-from-tablespace-statement
            | remove-user-from-group-statement
            | resume-schedule-statement  
            | revoke-access-statement
            | revoke-ip-privilege-statement
            | revoke-statement  
            | rollback-statement  
            | savepoint-statement  
            | select-statement  
            | select-order-by-statement
            | select-union-select-statement 
            | select-except-select-statement
            | select-into-statement  
            | select-for-browse-statement   /* dirty read   */
            | select-for-update-statement   /* program mode */
            | select-without-from-statement
            | start-db-statement  
            | suspend-schedule-statement
            | sync-schedule-statement 
            | truncate-table-statement
            | truncate-union-view-statement
            | terminate-db-statement
            | update-statement-positioned  /* program mode */
            | update-statement-searched
            | update-statistics-statement
            | update-tablespace-statistics-statement
            | set-transaction-statement

<[SUSPEND SCHEDULE]>
    suspend-schedule-statement ::
            SUSPEND SCHEDULE FOR REPLICATION TO remote-db-name

<[SYNC SCHEDULE]>
    sync-schedule-statement ::
            {SYNCHRONIZE | SYNC} REPLICATION TO remote-db-name [WAIT | NO WAIT]

<[SYNC AUTO INDEX]>
    sync-auto-index-statement ::
            SYNC AUTO INDEX

<[TERMINATE DB]>
    terminate-db-statement ::
            TERMINATE {DB | DATABASE}

<[TRUNCATE TABLE]>
    truncate-table-statement ::
            TRUNCATE TABLE table-name

<[TRUNCATE UNION VIEW]>
    truncate-union-view-statement ::
            TRUNCATE UNION VIEW table-name

<[UPDATE-STATEMENT-POSITIONED]>  /* program mode */
    update-statement-positioned ::=
            UPDATE remote-table-name [table-option]
            SET column-identifier = {expression | subquery | NULL}
                [, column-identifier = {expression | subquery | NULL}]...
            WHERE CURRENT OF cursor-name


<[UPDATE-STATEMENT-SEARCHED]>
    update-statement-searched ::=
            UPDATE remote-table-name [table-option]
            SET column-identifier = {expression | subquery | NULL}
                [, column-identifier = {expression | subquery | NULL}]...
            [WHERE search-condition]


<[UPDATE STATISTICS]>
    update-statistics-statement ::=
            UPDATE STATISTICS [all | update-statistics-list]
            [SAMPLE = unsigned-integer]

<[UPDATE TABLESPACE STATISTICS]>
    update-tablespace-statistics-statement ::=
            UPDATE TABLESPACE STATISTICS tablespace-name
            [SAMPLE = unsigned-integer]

<[UPDATE STATISTICS SET]>
    set-table-statistics-statement ::=
            UPDATE STATISTICS SET update-statistics-list
            { MODE = { 0 | 1 | 2 } | SAMPLE = unsigned-integer }

<[SET TRANSACTION]>
    set-transaction-statement ::=
            SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED
                                              | REPEATABLE READ | SERIALIZABLE }
______________________________________________________________________________
* sql command elements *


<(aggregate-function)>
    aggregate-function ::= 
            {   AVG
              | BIT_AND
              | BIT_OR
              | BIT_XOR
              | FIRST
              | JSON_AGG
              | JSON_OBJECT_AGG
              | LAST
              | MAX
              | MIN
              | STRING_AGG
              | STDDEV_POP
              | STDDEV_SAMP
              | SUM
              | VAR_POP
              | VAR_SAMP
              | XMLAGG
            } ([ALL | DISTINCT] expression [,comparison-predicate])
            | COUNT (* [,comparison-predicate])
            | COUNT ([ALL | DISTINCT] expression [,comparison-predicate])

<(alias-column-name)>
    alias-column-name ::= user-defined-name

<(approximate-numeric-literal)>
    approximate-numeric-literal ::= mantissaexponent
            mantissa ::= exact-numeric-literal
            exponent ::= [+|-] unsigned-integer


<(approximate-numeric-type)>
    approximate-numeric-type ::=    
            FLOAT
            |REAL
            |DOUBLE [PRECISION]


<(argument-list)>
    argument-list ::= expression | expression, argument-list


<(auth-user-list)>
    auth-user-list ::= auth-user | auth-user, auth-user-list


<(auth-user)>
    auth-user ::= group-name | user-name


<(backup-mode)>
    backup-mode ::= BACKUP BLOB ON
                  | BACKUP BLOB OFF


<(base-table-identifier)>
    base-table-identifier ::= user-defined-name


<(base-table-name)>
    base-table-name ::= [schema-name.]base-table-identifier


<(between-predicate)>
    between-predicate ::= column-name BETWEEN expression AND expression


<(binary-literal)>
    binary-literal ::= hex-string-literal


<(binary-type)>
    binary-type ::=
            BINARY(length)


<(blob-type)>
    blob-type ::=
            LONG VARCHAR
            |LONG VARBINARY
            |CLOB
            |BLOB
            |CHARACTER LARGE OBJECT
            |BINARY LARGE OBJECT
            |FILE
            |NCLOB
            |NATIONAL CHAR LARGE OBJECT
            |NCHAR LARGE OBJECT
            |NATIONAL LONG VARCHAR
            |media-domain-type
            |JSONCOLS

<(builtin-function)>
    builtin-funciton ::=
                function-name ( param-list )

<(odbc-scalar-function)>
    odbc-scalar-function ::=
                { fn scalar-function-name ( param-list ) }

<(case-expression)>
    case-expression ::=
              nullif-expresssion 
            | coalesce-expression 
            | CASE WHEN search_condition THEN expression [ELSE expression] END
            | CASE expression WHEN expression THEN expression [ELSE expression] END

<(cast-specification)>
    cast-specification ::=
                CAST ( expression AS data-type )


<(character)>
    character ::= {any character in the implement's character set}
                = quote quote


<(character-string-literal)>
    character-string-literal ::= '{character}...'


<(character-string-type)>
    character-string-type ::=
            CHAR(length)
            |CHARACTER(length)
            |VARCHAR(length)            
            |CHAR VARYING(length)
            |CHARACTER VARYING(length)
            |NCHAR(length)
            |NATIONAL CHAR(length)
            |NVARCHAR(length)
            |NATIONAL VARCHAR(length)
            |NCHAR VARYING(length)
            |NATIONAL CHAR VARYING(length)
            |NATIONAL CHARACTER VARYING(length)

<(chinese-word)>
    chinese-word ::=
            {two subsequent letters where the first letter's value is within (0x81-0xFF)}

<(coalesce-expression)>
    coalesce-expression ::= COALESCE (expression, expression [, ... expression] )

<(column-identifier)>
    column-identifier := user-defined-name


<(column-name)>
    column-name ::= [{remote-table-name|correlation-name}.]column-identifier

<(command-name)>
    command-name ::= [schema-name.]command-identifier

<(command-identifier)>
    command-identifier ::= user-defined-name

<(command-value)>
    command-value ::= dynamic-parameter
                      |literal
                      |function-name()
                      |NULL
                      |DEFAULT

<(comparison-operator)>
    comparison-operator ::= < | > | <= | >= | = | <> | !=


<(comparison-predicate)>
    comparison-predicate ::=
            expression comparison-operator expression


<(connection-id)>
    connection-id ::= unsigned-integer 


<(correlation-name)>
    correlation-name ::= user-defined-name


<(data-type)>
    data-type ::= 
            character-string-type
            |exact-numeric-type
            |approximate-numeric-type
            |binary-type
            |serial-type
            |blob-type
            |date-time-type


<(date-format)>
    date-format ::= mm/dd/yy              mm: 1-12
                   | mm-dd-yy             dd: 1-31
                   | dd/mon/yy            yy: 0-99
                   | mm/dd/yyyy           yyyy: 0-9999
                   | mm-dd-yyyy           mon: Jan-Dec
                   | yyyy/mm/dd
                   | yyyy-mm-dd
                   | dd/mon/yyyy
                   | dd.mm.yyyy
                   | yyyy.mm.dd


<(date-literal)>
    date-literal ::= date-string-literal


<(date-string-literal)>
    date-string-literal ::= 'date-format'd 
                            | 'date-format'D
                            | DATE 'date-format'
                            | {d 'date-format'}
                            | {D 'date-format'}


<(date-time-type)>
    date-time-type ::= 
            DATE
            |TIME
            |TIMESTAMP

<(non-ansi-outer-join-table-ref)>
    non-ansi-outer-join-table-ref ::= table-source [,] { LEFT OUTER JOIN | LEFT JOIN | OUTER }
                                      { table-source | ( table-source-list ) }

<(db-name)>
    db-name ::= user-defined-name


<(default-clause)>  
    default-clause ::=
            [SYSTEM | USER] DEFAULT default-val [ON UPDATE]

<(default-val)>
    default-val ::=
            { literal | NULL | builtin-function | CURRENT_USER 
              CURRENT_TIME | CURRENT_DATE | CURRENT_TIMESTAMP }


<(digit)>
    digit ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9


<(domain-constraint)>  
    domain-constraint ::=
            CHECK expression


<(domain-name)>
    domain-name ::= user-defined-name


<(dynamic-parameter)>
    dynamic-parameter ::= ?


<(exact-numeric-literal)>
    exact-numeric-literal ::=
            [+|-]{unsigned-integer[.unsigned-integer]
            |unsigned-integer.
            |.unsigned-integer}


<(exact-numeric-type)>
    exact-numeric-type ::=
            SMALLINT
            |INTEGER
            |INT
            |BIGINT
            |DECIMAL [ ( precision [,scale] ) ]
            |NUMERIC [ ( precision [,scale] ) ]

<(exeobj-name)>
    exeobj-name ::= {command-name | project-name | procedure-name}

<(exeobj-identifier)>
    exeobj-identifier ::= user-defined-name

<(exists-predicate)>
    exists-predicate ::= EXISTS ( subquery )


<(expression)>
    expression ::= term | expression {+|-|"||"} term


<(factor)>
    factor ::= [+|-]primary


<(file-extension)>
    file-extension ::= user-defined-name


<(file-name)>
    file-name ::= user-defined-name
                  | user-defined-name.file-extension
                  | quote user-defined-name quote


<(constraint-name)>
    constraint-name ::= user-defined-name

<(foreign-key-name)>
    foreign-key-name ::= user-defined-name


<(function-name)>
    function-name ::= user-defined-name

<(scalar-function-name)>
    scalar-function-name :: includes (1) string-functions
                                     (2) numeric-functions 
                                     (3) cast-functions   
                                     (4) date-time-timestamp-functions

<(group-name)>
    group-name ::= user-defined-name

<(group-by-specification)>
    group-by-specification ::= column-identifier
                               | builtin-function


<(hex-string-literal)>
    hex-string-literal ::= '{hexit}...'x
                           | '{hexit}...'X
                           | {x 'hexit'}
                           | {X 'hexit'}


<(hexit)>
    hexit ::= digit | A | B | C | D | E | F | a | b | c | d | e | f

    
<(index-identifier)>
    index-identifier ::= user-defined-name


<(index-name)>
    index-name ::= table-name.index-identifier


<(index-qualifier)>
    index-qualifier ::= user-defined-name


<(in-predicate)>
    in-predicate ::= column-name [NOT] IN 
                    { ( in-value-list ) 
                    | ( subquery )


<(insert-value)>
    insert-value ::=
            dynamic-parameter
            |literal
            |NULL
            |expression
            |special-serial-syntax


<(in-value-list)>
    in-value-list ::= literal [TO literal]
                    | NULL
                    | DEFAULT

<(join-table-ref)>
    join-table-ref ::= table-source { , | CROSS JOIN } table-source
                        | table-source join-type table-source ON search-condition
                        | table-source join-type table-source USING 
                                       (column-identifier [, column-identifier] ...)
                        | table-source NATURAL join-type table-source

<(joined-table)>
    joined-table ::=  join-table-ref
                     | non-ansi-outer-join-table-ref
                     | odbc-outer-join-table-ref
                     | [(] < joined_table > [)]

<(join-type)>
    join-type ::= { [ INNER [LOOP | MERGE] ] JOIN | outer-join-type }

<(keyword)>
    keyword ::= reserved-word
             | non-reserved-word

<(length)>
    length ::= unsigned-integer


<(letter)>
    letter ::= lower-case-letter 
             | upper-case-letter 
             | special-letter 
             | chinese-word


<(like-predicate)>
    like-predicate ::= column-name [NOT] LIKE pattern-value


<(literal)>
    literal ::= character-string-literal
            |nchar-string-literal
            |nchar-binary-literal
            |numeric-literal
            |binary-literal
            |date-literal
            |time-literal
            |timestamp-literal


<(lower-case-letter)>
    lower-case-letter ::=
            a | b | c | d | e | f | g | h | i | j | k | l | m |
            n | o | p | q | r | s | t | u | v | w | x | y | z


<[module-name]>
    module-name ::= user-defined-name

<(match-predicate)>
    match-predicate ::= column-name [NOT] [CASE] MATCH pattern-value

<(contains-predicate)>
    contains-predicate ::= CONTAINS ( column-name || column-name... , pattern-value [, 'case' ] )
    comment: contains predicate is match predicate on multi-columns

<(media-domain-type)>
    media-domain-type ::= MSWORDTYPE
                         |MSPPTTYPE
                         |MSEXCELTYPE
                         |PDFTYPE
                         |HTMLTYPE
                         |XMLTYPE
                         |MSWORDFILETYPE
                         |MSPPTFILETYPE
                         |MSEXCELFILETYPE
                         |PDFFILETYPE
                         |HTMLFILETYPE
                         |XMLFILETYPE 
                                          

<(contain-predicate)>
    contain-predicate ::= column-name [NOT] [CASE] CONTAIN pattern-value

<(nchar-binary-literal)>
    nchar-binary-literal ::= '{unicode hexit}...'u
                           | '{unicode hexit}...'U

<(nchar-string-literal)>
    nchar-string-literal ::= n'{character}...'
                           | N'{character}...'

<(new-file-name)>
    new-file-name ::= quote user-defined-name quote


<(new-pwd)>
    new-pwd ::= password

<(non-reserved-word)>
    non-reserved-word :: keyword that is not listed in the reserved word

<(null-predicate)>
    null-predicate ::= column-name IS [NOT] NULL

<(nullif-expression)>
    nullif-expression ::= NULLIF ( expression, expression )

<(numeric-literal)>
    numeric-literal ::= exact-numeric-literal|approximate-numeric-literal


<(old-file-name)>
    old-file-name ::= quote user-defined-name quote


<(old-pwd)>
    old-pwd ::= password


<(odbc-outer-join-table-reference)>
    odbc-outer-join-table-reference ::= {oj outer-join-table-ref ON search-condition}

 
<(outer-join-table-ref)>
    outer-join-table-ref ::= table-source outer-join-type table-source


<(outer-join-type)>
    outer-join-type ::=
                       RIGHT [OUTER] [LOOP | MERGE] JOIN
                     | LEFT [OUTER] [LOOP | MERGE] JOIN
                     | OUTER [ [LOOP | MERGE] JOIN]
 

<(procedure-exact-numeric-type)>
    procedure-exact-numeric-type ::=
            SMALLINT
            |INTEGER
            |INT
            
<[procedure-data-type]>
    procedure-data-type ::=
            character-string-type
            |procedure-exact-numeric-type
            |approximate-numeric-type
            |date-time-type

<[parameter-type]>
    parameter-type ::= IN | INPUT | OUT | OUTPUT

<(password)>
    password :: =
            user-defined-name
          | NULL               /* means no password */


<(pattern-value)>
    pattern-value ::= character-string-literal|dynamic-parameter
            (In a character-string-literal in a like-predicate,
             the percent character ('%') matches 0 or more of any character,
             the underscore character ('_') matches 1 character.)


<(precision)>
    precision ::= unsigned integer


<(predicate)>
    predicate ::= comparison-predicate 
                  | between-predicate
                  | in-predicate
                  | like-predicate 
                  | null-predicate 
                  | match-predicate
                  | contains-predicate
                  | contain-predicate
                  | quantified-comparison-predicate
                  | exists-predicate


<(primary)>
    primary ::= column-name
                |dynamic-parameter
                |literal
                |aggregate-function
                |odbc-scalar-function
                |builtin-function
                |(expression)
                |cast-specification
                |case-expression
                |window-function
                |scalar-valued-function


<(procedure-name)>
    procedure-name :: = [module-name.][schema-name.]procedure-identifier

<[procedure-parameter]>
    procedure-parameter ::= 
            procedure-data-type parameter-identifier [parameter-type]

<[sql-procedure-parameter]>
    sql-procedure-parameter ::= 
            [parameter-type] parameter-identifier procedure-data-type 

<[procedure-result]>
    procedure-result ::=
            procedure-data-type parameter-identifier

<(procedure-identifier)>
    procedure-identifier :: = user-defined-name

<(procedure-value)>
    procedure-value ::= dynamic-parameter
                      |literal

<(project-name)>
    project-name :: = project-identifier

<(project-identifier)>
    project-identifier :: = user-defined-name

<(quantified-comparison-predicate)>
    quantified-comparison-predicate ::= 
                expression comparison-operator {ALL | ANY} ( subquery )


<(query-specification)>
    query-specification ::=
            SELECT [ALL | DISTINCT] select-list
            FROM [SEQUENCE | SEQ] table-source-list
            [WHERE search-condition]


<(quote)>
    quote ::= '


<(referenced-columns)>
    referenced-columns ::= (column-identifier[,column-identifier]...)

<(referencing-columns)>
    referencing-columns ::= (column-identifier[,column-identifier]...)

<(reserved-word)>
    reserved-word :: 
         ABSOLUTE | ACL | ACTION | ADD | ADMIN | AFTER | AGGREGATE | ALL | ALIAS | ALLOCATE | ALLOW 
       | ALTER | AND | ANY | ARE | ARRAY | AS | ASC | ASSERTION
       | AT
       | BEFORE | BEGIN | BIGINT | BIGSERIAL | BINARY | BIT | BLOB | BLOCK | BOOLEAN | BOTH | BREADTH
       | BY
       | CALL | CASCADE | CASCADED | CASE | CAST | CATALOG | CHECK
       | CLASS | CLOB | CLOSE | COLLATE | COLLATION | COLUMN | COMMIT | COMPLETION
       | CONNECT | CONNECTION | CONSTRAINT | CONSTRAINTS | CONSTRUCTOR | CONTINUE | CORRESPONDING | CREATE
       | CROSS | CUBE | CURRENT | CURRENT_DATE | CURRENT_PATH | CURRENT_ROLE | CURRENT_TIME | CURRENT_TIMESTAMP
       | CURRENT_USER | CURSOR | CYCLE
       | DATE | DAY | DEALLOCATE | DEC | DECIMAL | DECLARE | DEFAULT | DEFERRABLE
       | DEFERRED | DELETE | DEPTH | DEREF | DESC | DESCRIBE | DESCRIPTOR | DESTROY
       | DESTRUCTOR | DETERMINISTIC | DICTIONARY | DIAGNOSTICS | DISCONNECT | DISTINCT | DOMAIN | DOUBLE
       | DROP | DYNAMIC
       | EACH | ELSE | END | END-EXEC | EQUALS | ESCAPE | EVERY | EXCEPT
       | EXCEPTION | EXEC | EXECUTE | EXTERNAL
       | FALSE | FETCH | FIRST | FLOAT | FOR | FOREIGN | FOUND | FROM
       | FREE | FULL | FUNCTION
       | GENERAL | GET | GLOBAL | GO | GOTO | GRANT | GROUP | GROUPING
       | HAVING | HOST
       | IDENTITY | IGNORE | IMMEDIATE | IN | INDICATOR | INITIALIZE | INITIALLY | INNER
       | INOUT | INPUT | INT | INTEGER | INTERSECT | INTO | IS
       | ISOLATION | ITERATE
       | JOIN
       | KEY
       | LANGUAGE | LARGE | LAST | LATERAL | LEADING | LESS | LEVEL | LIKE
       | LIMIT | LOCAL | LOCALTIME | LOCALTIMESTAMP | LOCATOR
       | MAP | MATCH | MODIFIES | MODIFY | MODULE
       | NAMES | NATIONAL | NATURAL | NCHAR | NCLOB | NEXT | NO | NONE
       | NOT | NULL | NUMERIC
       | OBJECT | OF | OFF | ON | ONLY | OPEN | OPERATION | OPTION
       | OR | ORDER | ORDINALITY | OUT | OUTER | OUTPUT
       | PAD | PARTIAL | PATH | POSTFIX | PREFIX | PREORDER
       | PREPARE | PRESERVE | PRIMARY | PRIOR | PRIVILEGES | PROCEDURE
       | READ | READS | REAL | RECURSIVE | REFERENCES | REFERENCING | RELATIVE
       | RESTRICT | RESULT | RETURN | RETURNS | REVOKE | ROLE | ROLLBACK | ROLLUP
       | ROUTINE | ROW | ROWS
       | SAVEPOINT | SCROLL | SCOPE | SEARCH | SECTION | SELECT | SERIAL 
       | SEQUENCE | SESSION | SESSION_USER | SET | SETS | SIZE | SMALLINT | SOME
       | SPECIFIC | SPECIFICTYPE | SQL | SQLEXCEPTION | SQLSTATE | SQLWARNING | START
       | STATIC | STRUCTURE | SYSTEM_USER
       | TABLE | TEMPORARY | TERMINATE | THAN | THEN | TIME | TIMESTAMP | TIMEZONE_HOUR
       | TIMEZONE_MINUTE | TO | TRAILING | TRANSACTION | TRANSLATION | TREAT | TRIGGER | TRUE
       | UNDER | UNION | UNKNOWN | UNNEST | UPDATE | USAGE | USING
       | VALUES | VARCHAR | VARIABLE | VARYING | VIEW
       | WHEN | WHENEVER | WHERE | WITH | WITHOUT | WORK | WRITE
       | ZONE


<(savepoint-name)>
    savepoint-name ::= user-defined-name


<(scalar-valued-function)>
    scalar-valued-function ::=
         { GREATEST | LEAST } ( expression , expression [, ... expression] )


<(scale)>
    scale ::= unsigned integer


<(search-condition)>
    search-condition ::=
            boolean-term [OR search-condition]
            boolean-term ::= boolean-factor [AND boolean-term]
            boolean-factor ::= [NOT]boolean-primary
            boolean-primary ::= predicate | (search-condition)


<(sec-user)>
    sec-user :: =
        user-name
        | group-name


<(select-list)>
    select-list ::= *|select-sublist[,select-sublist]...


<(select-sublist)>
    select-sublist ::= { expression | {remote-table-name|correlation-name}.* } [ [AS] alias-column-name ]


<(select-nofrom-list)>
    select-nofrom-list ::= *|select-nofrom-sublist[,select-nofrom-sublist]...


<(select-nofrom-sublist)>
    select-nosublist ::= { special-serial-syntax | expression | {remote-table-name|correlation-name}.* } [ [AS] alias-column-name ]


<(special-serial-syntax)>
    special-serial-syntax ::= table-name.{CURRVAL|NEXTVAL|CURRSER}


<(sort-specification)>
    sort-specification ::= {unsigned-integer | column-name | expression} [ASC|DESC]


<(serial-type)>
    serial-type ::=
            SERIAL [(n)]
            | BIGSERIAL [(n)]


<(special-letter)>
    special-letter ::=
            $ | # | @ | _ 

<(sqlsp-identifier)>
    sqlsp-identifier ::= user-defined-name

<(subquery)>
    subquery ::= basic-select-syntax


<(synonym-name)>
    synonym-name ::= user-defined-name


<(table-identifier)>
    table-identifier ::= user-defined-name


<(table-name)>
    table-name ::= [schema-name.]table-identifier


<(remote-table-name)>
    remote-table-name ::= [db-name[@server-name]:]table-name


<(db-name)>
    db-name ::= user-defined-name


<(server-name)>
    server-name ::= user-defined-name


<(table-reference)>
    table-reference ::= remote-table-name [table-option] [correlation-name]


<(table-source-list)>
    table-source-list ::= table-source ,...

<(table-source)>
    table-source ::= table-reference 
                     | join-table 
                     | ( {basic-select-syntax | select-union-select-statement | select-except-select-statement} ) [AS] correlation-name
                       [(column-identifier [, column-identifier] ...)]

<(tablespace-name)>
    tablespace-name ::= user-defined-name


<(table-option)>
    table-option ::= ( {table-index-option | table-text-index-option} )


<(table-index-option)>
    table-index-option ::= INDEX [=] {0 | 1 | index-identifier} [{asc | desc}]


<(table-text-index-option)>
    table-text-index-option ::= TEXT INDEX [=] index-identifier
 

<(term)>
    term ::= factor | term {*|/} factor


<(time-format)>
    time-format ::= hh:mm:ss.fff
                    | hh:mm:ss 
                    | hh:mm
                    | hh
                    | hh:mm:ss.fff tt
                    | hh:mm:ss tt
                    | hh:mm tt
                    | hh tt
                    | tt hh:mm:ss.fff
                    | tt hh:mm:ss
                    | tt hh:mm
                    | tt hh


<(time-literal)>
    time-literal ::= time-string-literal


<(time-string-literal)>
    time-string-literal ::= 'time-format't 
                            | 'time-format'T
                            | TIME 'time-format'
                            | {t 'time-format'}
                            | {T 'time-format'}


<(timestamp-format)>
    timestamp-format ::= date-format [time-format]


<(timestamp-literal)>
    timestamp-literal ::= timestamp-string-literal


<(timestamp-string-literal)>
    timestamp-string-literal ::= 'timestamp-format'ts 
                            | 'timestamp-format'TS
                            | TIMESTAMP 'timestamp-format'
                            | {ts 'timestamp-format'}
                            | {TS 'timestamp-format'}

<(trigger-name)>
    trigger-name ::= user-defined-name
 
<(trigger-event)>
    trigger-event ::=
      INSERT
    | DELETE
    | UPDATE [ OF column-identifier[,column-identifier]... ]
 
<(triggered-action)> 
    triggered-action ::=
      { [referencing-clause] FOR EACH ROW [ WHEN ( search-condition ) ]
        | FOR EACH STATEMENT }
        { (triggered-SQL-statement) | BEGIN sql-procedure-statement; END }
        
<(triggered-SQL-statement)> 
    triggered-SQL-statement ::= 
    { insert-statement             
      | update-statement-positioned
      | update-statement-searched  
      | delete-statement-positioned
      | delete-statement-searched  
      | execute-procedure-statement 
    }

<(referencing-clause)>
    referencing-clause ::= 
         REFERENCING { OLD | NEW } AS correlation-name 
                     [{ NEW | OLD } AS correlation-name]


<(tsfile)> 
    tsfile ::= tsfilename [TYPE = DATA]
            | tsfilename TYPE = BLOB


<(tsfilename)> 
    tsfilename ::= filename
            | file_name = 'physical_file_name unsigned-integer[M|G]'


<(unsigned-integer)>
    unsigned-integer ::= {digit}...


<(update-statistics-list)>
    update-statistics-list ::= table-name [(update-statistics-index-option)]


<(update-statistics-index-option)>
    update-statistics-index-option ::= INDEX [index-name [,index-name]...]
                               

<(upper-case-letter)>
    upper-case-letter ::=
            A | B | C | D | E | F | G | H | I | J | K | L | M |
            N | O | P | Q | R | S | T | U | V | W | X | Y | Z


<(user-defined-name)>
    user-defined-name ::=
            letter[digit|letter|_]...
          | "user-defined-name"


<(user-name)>
    user-name ::= user-defined-name


<(view-name)>
    view-name ::= user-defined-name


<(window-function)>
    window-function ::= 
                { ROW_NUMBER|RANK|DENSE_RANK}() 
                 ( [ PARTITION BY { column-name | expression } 
                                   [, {column-name|expression}]... ]
                 ORDER BY { column-name| expression } [ASC|DESC]
                          [, {column-name|expression} [ASC|DESC]]... )

______________________________________________________________________________
* xsqlm commands *


<[!(History Command Execution)]> - (ISQL COMMAND)
    Grammar:
      |>>-- ! --+--------------------+--<<|
                +-- ! ---------------+
                +-- command_number --+
                +-- match_string ----+
    Example:
      dmSQL> !5;                   /* 5th history command                        */
      dmSQL> !-4;                  /* 4th history command(from current)          */  
      dmSQL> !sel;            /* history command with prefix <sel>(from current) */
      dmSQL> !'select *';          /* interchangable use of single/double quotes */   
      dmSQL> !"insert into";                   /* double quotes needed for space */
      dmSQL> !'select * from "emp tab"';           /* match pattern: <"emp tab"> */
      dmSQL> !"select * from ""emp tab"" where";   /* match pattern: <"emp tab"> */


<[$(Shell Command)]> - (ISQL COMMAND)
    Grammar:
      $ "command-string";
    Example:
      dmSQL> $ "cp test.cmd test.tmp"; /* interchangable use of single/double */
      dmSQL> $ 'more test.doc';        /* quotes for separating spaces        */
      dmSQL> $ ls;                     /* quick way */


<[EDIT]> - (ISQL COMMAND)
    Grammar:
      |>>-- EDIT --+--------------+--<<|
                   +-- filename --+
    Example:
      dmSQL> edit;                           /* call editor: EDITOR        */
      dmSQL> edit emp.scr;                   /* file: <WORKDIR/emp.scr>    */
      dmSQL> edit /opt/db1/emp.scr;          /* file: </fullpath/filename> */


<[HELP]> - (ISQL COMMAND)
    Grammar:
      |>>-- HELP --+----------------------+--<<|
                   +-- ALL ---------------+
                   +-- keyword_to_match --+
    Example
      dmSQL> help;                                   /* show main keywords         */
      dmSQL> help all;                               /* show main and sub keywords */
      dmSQL> help table;                   /* show keywords with substring <table> */
      dmSQL> help "create table";   /* show keywords with substring <create table> */


<[HISTORY]> - (ISQL COMMAND)
    Grammar:
      |>>-- [HISTORY|HIST] --+--------------------+--<<|
                             +-- ! ---------------+
                             +-- command_number --+
    Example:
      dmSQL > hist;       /* list all history commands              */
      dmSQL > hist !;     /* list last history command              */
      dmSQL > hist 5;     /* list 5th history command               */
      dmSQL > hist -4;    /* list 4th history command(from current) */


<[LOAD]> - (ISQL COMMAND)
    Grammar:
      |>>-- LOAD --+-- [DATABASE|DB] FROM unload_filename --+-- [commit_num] --+--<<|
                   +-- TABLE   FROM unload_filename ---------+
                   +-- SCHEMA  FROM unload_filename ---------+
                   +-- DATA    FROM unload_filename ---------+
                   +-- PROJECT FROM unload_filename ---------+
                   +-- MODULE  FROM unload_filename ---------+
                   +-- PROC    FROM unload_filename ---------+
                   +-- PROCEDURE FROM unload_filename -------+    
                   +-- EXTERNAL DB FROM 'select-statement' TO table-name -------+
    Example:
      dmSQL> load db from empdb.scr;     /* WORKDIR/empdb.scr loaded     */
      dmSQL> load table from keytab;     /* WORKDIR/keytab.scr loaded    */
      dmSQL> load data from minetab;     /* minetab already exists !!    */
      dmSQL> load db from empdb 100;     /* commit work per 100 commands  */
      dmSQL> load db from empdb;         /* commit work per 1000 commands */
      dmSQL> load proc from file1;       /* WORKDIR/file1.scr loaded      */
      dmSQL> set external db 'DSN=DBAMPLE4;UID=SYSADM;';  /* must set external db for load external db */
      dmSQL> load external db from 'select * from emptab' to emptab;  /* data: "select-statement" */


<[PRINT]> - (ISQL COMMAND)
    Grammar:
      |>>-- PRINT --+-- string --+--<<|   /* \n and \t allowed in string */


<[QUIT]> - (ISQL COMMAND)
    Grammar:
      |>>-- [QUIT|Q] --<<|   /* automatically disconnect all connections */
                             /* and quit the dmSQL                       */


<[RUN]> - (ISQL COMMAND) 
    Grammar:
      |>>-- RUN --+-- script_filename --+--<<|
    Example:
      dmSQL> run mobile.sql;               /* file: <WORKDIR/mobile.sql> */
      dmSQL> run /disk2/usr/kk/mobile.sql; /* file: </disk2/usr/kk/... > */


<[SET]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET --+--<<|  
    Example:        
      dmSQL> set;      /* list flag settings */


<[SET AUTOCOMMIT]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET AUTOCOMMIT --+-- ON ---+--<<|
                             +-- OFF --+
    Example:
      dmSQL> set autocommit on;   /* turn on autocommit mode */

<[SET BROWSE]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET BROWSE --+-- ON ---+--<<|
                         +-- OFF --+
    Example:
      dmSQL> set browse on;   /* turn on browse mode */

<[SET BKSVR CMP]> - (SQL COMMAND)
    Grammar:
      |>>-- SET BKSVR CMP --+-- ON ---+--<<|
                            +-- OFF --+
    Example:
      dmSQL> set bksvr cmp on;   /* turn on compact mode */

<[SET BACKUP OFF]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET BACKUP OFF --+--<<|
    Example:
      dmSQL> set backup off;   /* turn off backup mode */


<[SET BLOB BACKUP ON]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET BLOB BACKUP ON --+--<<|
    Example:
      dmSQL> set blob backup on;   /* turn on blob backup mode */


<[SET BLOBSHOW]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET BLOBSHOW --+-- [SIZE|FILE|PRINT] --+--<<|  
    Example: 
                         /* flag setting for blob/file field of select table */
      dmSQL> set blobshow size;         /* show data size of blob/file field */
      dmSQL> set blobshow file;     /* fetch blob/file field data into files */
      dmSQL> set blobshow print; /* display blob/file field data onto stdout */


<[SET BLOBPREF]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET BLOBPREF --+-- blobpref_string --+--<<|
    Example: 
      dmSQL> set blobshow file;        /* request for data fetch into files */
      dmSQL> set blobpref "kk";       /* filename as: kk"rowcount"."columncount" */
      dmSQL> select b1,b2,f3 from emptab; /* fetch the blob/file data into files */


<[SET BLOBROWMAX]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET BLOBROWMAX --+-- blobrowmax_number --+--<<|
    Example: 
                                       /* set max rows lest that the file system be full */
      dmSQL> set blobshow file;                     /* request for fetch data into files */
      dmSQL> set blobpref "kk";               /* filename as: kk"rowcount"."columncount" */
      dmSQL> set blobrowmax 5;        /* only blob/file data of first 5 rows are allowed */  
      dmSQL> select b1 from emptab;    /* only b1 of first 5 rows are written into files */


<[SET BLOBWIDTH]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET BLOBWIDTH --+-- blobwidth_number --+--<<|   
    Example:
                                     /*  1 <= blobwidth_number <= 256 */
                                     /* the first n bytes of blob/file field to print */
      dmSQL> set blobshow print;     /* request for data print onto stdout */
      dmSQL> set blobwidth 20;       /* first 20 bytes to print */
      dmSQL> select b1 from emptab;  /* first 20 bytes of b1(s) are printed */

<[SET BLOCK DELIMITER]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET BLOCK DELIMITER --+-- [block delimiter] --+--<<|  
    Example: 
      /* set block delimiter before create sql procedure in dmSQL */
      dmSQL> set block delimiter @@;  /* set block delimiter @@   */
      dmSQL> set block delimiter;     /* set block delimiter null */ 

<[SET CB MODE]> - (SQL COMMAND)
    Grammar:
      |>>-- SET CB MODE --+--- CLOSE -----+-- <<|
                          +--- DELETE ----+
                          +--- PRESERVE --+
    Example
      dmSQL> set cb mode preserve ;   /* set cb mode to preserve */

<[SET CHKHVUDF]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET CHKHVUDF --+-- ON -------+--<<|
                           +-- OFF-------+
    Example:
      dmSQL> set chkhvudf on; /* set check host variable support no-parameter udf on */
      dmSQL> create table t1 (c1 timestamp);
      dmSQL> insert into t1 values(?);
      dmSQL/Val> 'NOW()';
      dmSQL/Val> end;
              :
              :
      dmSQL> set chkhvudf off; /* set check host variable support no-parameter udf off */

<[SET CLIENT_CHAR_SET]> - (SQL COMMAND)
    Grammar:
      |>>-- SET CLIENT_CHAR_SET --+--- charset_string -----+-- <<|
 
    Example
      dmSQL> set client_char_set 'ASCII' ;   /* set client charset to ascii */
      dmSQL> set client_char_set 'GBK';      /* set client charset to gbk   */

<[SET CONCAT NULL RETURN]> - (SQL COMMAND)
    Grammar:
      |>>-- SET CONCAT NULL RETURN --+--- NULL -----+-- <<|
                                     +--- STRING ---+
    Example
      dmSQL> set concat null return string ;   /* set concat null return string */


<[SET DATA BACKUP ON]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET DATA BACKUP ON --+--<<|
    Example
      dmSQL> set data backup on;   /* turn on data backup mode */


<[SET DATE INPUT FORMAT]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET DATE INPUT FORMAT --+-- ALL --------------+--<<|
                                    +-- date_expression --+
    Example
      dmSQL> set date input format 'yyyy-mm-dd';


<[SET DATE OUTPUT FORMAT]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET DATE OUTPUT FORMAT --+-- date_expression --+--<<|
    Example
      dmSQL> set date output format 'yyyy-mm-dd';


<[SET DATE FORMAT]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET DATE FORMAT --+-- date_expression --+--<<|
    Example
      dmSQL> set date format 'yyyy-mm-dd'; /* set date input/output format */


<[SET DBMODE]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET DBMODE --+-- SINGLE --+--<<|
                         +-- MULTI ---+
    Example
      dmSQL> set dbmode single;    /* single user mode */
      dmSQL> set dbmode multi;     /* multi-user  mode */


<[SET DDB LOGIN TIMEOUT]> - (SQL COMMAND)
    Grammar:
      |>>-- SET DDB LOGIN TIMEOUT --+-- login_timeout_sec_number --+--<<|
    Example:
      dmSQL> set ddb login timeout 20;    /* set login timeout to 20 seconds */
                                          /* for ddb mode                    */

<[SET DDB LOCK TIMEOUT]> - (SQL COMMAND)
    Grammar:
      |>>-- SET DDB LOCK TIMEOUT --+-- lock_timeout_sec_number --+--<<|
    Example:
      dmSQL> set ddb lock timeout 1;      /* set lock timeout to 1 second    */
                                          /* for ddb mode                    */


<[SET DFO DUP]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET DFO DUPMODE --+-- NULL --+--<<|
                              +-- COPY --+
    Example:
      dmSQL> set dfo dupmode copy;   /* set copy mode to duplicate fo in ddb */

<[SET ECHO]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET ECHO --+-- ON  --+--<<|
                       +-- OFF --+
    Example:
      dmSQL> set echo off;  /* result of executing script file not displayed */


<[SET EDITOR]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET EDITOR --+-- editor_name ---+--<<|
    Example:
      dmSQL> set editor joe;  /* call joe if an editor is needed */
      
<[SET ERRMSG_CHAR_SET]> - (SQL COMMAND)
    Grammar:
      |>>-- SET ERRMSG_CHAR_SET --+-- errmsg_charset_expression --+--<<|
    Example:
      dmSQL> set errmsg_char_set 'en';      /* set error charset to English */
      dmSQL> set errmsg_char_set 'zh_TW'    /* set error charset to zh_TW   */
      dmSQL> set errmsg_char_set 'ja.UTF-8' /* set error charset to ja and use UTF-8 encode */
      dmSQL> set errmsg_char_set 'ja_JP.EUC-JP'; /* set error charset to ja and use euc-jp  */
                                                 /* endoce */     


<[SET EXTNAME TO]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET EXTNAME TO --+-- file-extension ---+--<<|
                             +-- SOURCE         ---+
    Example:
      dmSQL> set extname to bmp;    /* set extension name to "bmp" */
      dmSQL> set extname to SOURCE; /* the extension name is set equal 
                                           that of the client's source file */


<[SET FETCH]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET FETCH --+-- ALL ---------------------+--<<|
                        +-- tuple_number_per_fetch --+
    Example:
      dmSQL> set fetch 10;   /* fetch 10 tuples at a time in select table */
      dmSQL> set fetch all;  /* fetch all tuples at a time in select table */


<[SET FLIGHTRECORDER]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET [FLIGHTRECORDER|FLTREC] --+-- ON  --+--<<|
                                          +-- OFF --+
    Example:
      dmSQL> set fltrec on;  /* executed commands appended into dmrecord.sql */


<[SET FLUSH]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET FLUSH --<<|
    Example:
      dmSQL> set flush;  /* flush database replication to slave site(s) */


<[SET FO TYPE]> - (SQL COMMAND)
    Grammar:
      |>>-- SET FO TYPE --+-- BLOB --+--<<|
                          +-- FILE --+
    Example:
      dmSQL> set fo type BLOB;  /* set file object described type to blob */

<[SET GETENV]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET GETENV --+-- [ON|OFF] --+--<<|  
    Example: 
               /* flag setting for translating environment variables */
      dmSQL> set getenv on;      /* translate environment variables */
      dmSQL> set getenv off;     /* Don't translate environment variables */

<[SET INPUT PARAM]> - (SQL COMMAND)
    Grammar:
      |>>-- SET INPUT PARAM --+-- number --+-- as --+-- CFILE --+--<<|
                              +---- ALL ---+        +-- ASCII --+
    Example:
      /* Set the nth parameter as client file for next SQL statement when the
         nth parameter is binded as SQL_C_CHAR to SQL BLOB type. */
      dmSQL> set input param 2 as CFILE; /* set the 2nd parameter is binded as client file */
      dmSQL> set input param 9 as CFILE; /* set the 9th parameter is binded as client file */
    Example:
      /* turn off the CFILE setting */
      dmSQL> set input param all as ASCII; /* turn off the CFILE setting */

<[SET ITCMD]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET ITCMD --+-- ON ---+--<<|
                        +-- OFF --+
    Example:
      dmSQL> set itcmd on;  /* set implicit type casting mode on  */
      dmSQL> create table t1 (c1 char(20)); 
      dmSQL> insert into t1 values(123456); 
      dmSQL> select * from t1 where c1=123456;
               :
               :
      dmSQL> set itcmd off; /* set implicit type casting mode off */

<[SET LASTTRAN]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET LASTTRAN --+-- ABORT  --+--<<|
                           +-- COMMIT --+
                           +-- ASK -----+
    Example:
      dmSQL> set lasttran abort;  /* if autocommit off, abort last transaction */
                                  /* before disconnect each connection         */ 


<[SET LINEWIDTH]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET LINEWIDTH --+-- OFF  --------------+--<<|
                            +-- linewidth_number --+
    Example:
      dmSQL> set linewidth 80;   /* set linewidth of screen */
      dmSQL> select * from emptab; 
               :
               :
      dmSQL> set linewidth off;  /* set no linewidth limit */ 


<[SET LOADAUTOINDEX]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET LOADAUTOINDEX --+-- ON ---+--<<|
                                +-- OFF --+
    Example:
      dmSQL> set loadautoindex on;  /* set load auto index on  */
      dmSQL> set loadautoindex off; /* set load auto index off */


<[SET LOADDATA]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET LOADDATA --+-- SKIP [ERROR] ----------+--<<|
                           +-- STOP [ON ERROR] -------+
    Example:
      dmSQL> set loaddata skip;   /* set load data skip error */
      dmSQL> load data from file1;
               :
               :
      dmSQL> set loaddata stop on error;  /* set load data stop when error occurs */
 
 
<[SET LOADDB]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET LOADDB --+-- SAFE -------+--<<|
                         +-- FAST -------+
    Example:
      dmSQL> create db db1;
      dmSQL> set loaddb safe;   /* set load db with journal mode */
      dmSQL> load db from file1;
      dmSQL> set loaddb fast;   /* set load db with no journal mode */


<[SET FASTCOPY]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET FASTCOPY --+-- ON ---+--<<|
                           +-- OFF --+
    Example:
      dmSQL> set fastcopy on;   /* set fastcopy on */

<[SET LOAD SYSTEM DEFAULT]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET LOAD SYSTEM DEFAULT --+-- ON ---+--<<|
                                      +-- OFF --+
    Example:
      dmSQL> set load system default off; /* when load db/table, the values of */
                                          /* columns with SYSTEM DEFAULT       */
                                          /* attribute can not be overridden   */
                                          /* by system */                                      

<[SET LOCK TIMEOUT]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET LOCK TIMEOUT --+-- lock_timeout_sec_number --+--<<|
    Example
      dmSQL> set lock timeout 0;   /* no wait at all    */
      dmSQL> set lock timeout 5;   /* 5 seconds to wait */
      dmSQL> set lock timeout -5;  /* always wait(n<0)  */


<[SET LOGIN TIMEOUT]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET LOGIN TIMEOUT --+-- login_timeout_sec_number --+--<<|
    Example:
      dmSQL> set login timeout 20;    /* set login timeout as 20 seconds */


<[SET PRINTTO]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET PRINTTO --+-- OFF -------------------------+--<<|
                          +-- ALL --+-- printto_filename --+
                          +---------+
    Example:
      dmSQL> set printto sunday.dat;  /* result directed into WORKDIR/sunday.dat */ 
                                      /* instead of stdout */
      dmSQL> set printto ALL sunday.dat;  /* error/result directed into WORKDIR/sunday.dat */ 
                                          /* instead of stdout */

<[SET REMOVE SPACE PADDING]> - (SQL COMMAND)
    Grammar:
      |>>-- SET REMOVE SPACE PADDING --+-- ON ---+--<<|
                                       +-- OFF --+
    Example:
      dmSQL> set remove space padding on; /* remove trailing space for CHAR type */


<[SET RUNSCRIPT STOP ON ERROR]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET RUNSCRIPT STOP ON ERROR --+-- OFF ---------------+--<<|
                                          +-- ON  ---------------+
                                          +-- ROLLBACK ----------+
                                          +-- COMMIT   ----------+
    Example:
      /* Set the following operation when any error occured when running a script */
      dmSQL> set runscript stop on error off;       /* do not stop on error (default)         */
      dmSQL> set runscript stop on error on;        /* stop on error                          */
      dmSQL> set runscript stop on error rollback;  /* stop and rollback transaction on error */
      dmSQL> set runscript stop on error commit;    /* stop and commit   transaction on error */

<[SET STRING CONCAT]> - (SQL COMMAND)
    Grammar:
      |>>-- SET STRING CONCAT  --+-- ON ---+--<<|
                                 +-- OFF --+
    Example:
      dmSQL> set string concat on; /* remove trailing space for each CHAR type */
                                   /* operand before string concat opeartion   */

<[SET STRING LENGTH]> - (SQL COMMAND)
    Grammar:
      |>>-- SET STRING LENGTH --+-- string length value ---+--<<|
    Example:
      dmSQL> set string length 64; /* set described length for string type UDF to 64 */


<[SET SHOWTIME]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET SHOWTIME --+-- ON  --+--<<|
                           +-- OFF --+
    Example:
      dmSQL> set showtime off;  /* result of execution time is not displayed */

<[SET SYSINFO CLEAR]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET SYSINFO CLEAR --+--<<|
    Example
      dmSQL> set sysinfo clear;  /* clear system information */

<[SET SYSTEM DEFAULT]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET SYSTEM DEFAULT --+-- ON  --+--<<|
                                 +-- OFF --+
    Example:
      dmSQL> set system default on;  /* the values of columns with SYSTEM   */
                                     /* DEFAULT attribute can be overridden */
                                     /* by system */                               

<[SET TIME INPUT FORMAT]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET TIME INPUT FORMAT --+-- ALL --------------+--<<|
                                    +-- time_expression --+
    Example
      dmSQL> set time input format "hh:mm:ss";


<[SET TIME OUTPUT FORMAT]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET TIME OUTPUT FORMAT --+-- time_expression --+--<<|
    Example
      dmSQL> set time output format "hh:mm:ss";


<[SET TIME FORMAT]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET TIME FORMAT --+-- time_expression --+--<<|
    Example
      dmSQL> set time format "hh:mm:ss";  /* set time input/output */


<[SET UNLOAD BROWSE]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET UNLOAD BROWSE --+-- ON  --+--<<|
                                +-- OFF --+
    Example
      dmSQL> set unload browse on;  /* set unload browse on */


<[SET UNLOAD CHARTOHEX]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET UNLOAD CHARTOHEX --+-- AUTO --+--<<|
                                   +-- ON   --+
                                   +-- OFF  --+
    Example
      dmSQL> set unload chartohex on;  /* set unload char data to hex format */


<[SET UNLOAD CURSERIAL]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET UNLOAD CURSERIAL --+-- ON  --+--<<|
                                   +-- OFF --+
    Example
      dmSQL> set unload curserial on;  /* set unload table's current serial */


<[SET UNLOAD FILEOBJ]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET UNLOAD FILEOBJ --+-- ON  --+--<<|
                                 +-- OFF --+
                                 +-- NAME -+
    Example
      dmSQL> set unload fileobj on;  /* default: set to unload system fileobj to workdir */
      dmSQL> set unload fileobj off; /* when set, user need to manually copy system fileobj to workdir when load db */
      dmSQL> set unload fileobj name; /* when set, user must make sure system fileobj exist at same directory at server side when load db */


<[SET UNLOAD PROCDEF]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET UNLOAD PROCDEF --+-- ON  --+--<<|
                                 +-- OFF --+
    Example
      dmSQL> set unload procdef on;  /* set to unload proc definition when unload db */


<[SET UNLOAD SPLITFILE]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET UNLOAD SPLITFILE --+-- ON  --+--<<|
                                   +-- OFF --+
    Example
      dmSQL> set unload splitfile on;  /* set to split unload script file as table def, */
                                       /* data, index and other schema                  */

<[SET UNLOAD EXTERNAL]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET UNLOAD EXTERNAL --+-- connection_string  --+--<<|
    Example
      dmSQL> set unload external 'DSN=DBAMPLE4;UID=SYSADM;';  /* set unload external */

<[SET EXTERNAL DB]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET EXTERNAL DB--+-- connection_string  --+--<<|
    Example
      dmSQL> set external db 'DSN=DBAMPLE4;UID=SYSADM;';  /* set external db*/

<[SET WORKDIR]> - (ISQL COMMAND)
    Grammar:
      |>>-- SET WORKDIR --+-- directory_path --+--<<|
    Example:
      dmSQL> set workdir sales;             /* path: <WORKDIR/sales>    */
      dmSQL> set workdir /usr/local/sales;  /* path: </usr/local/sales> */


<[UNLOAD]> - (ISQL COMMAND)
    Grammar:
      |>>-- UNLOAD --+-- [DATABASE|DB] TO unload_filename ---------------------+--<<|
                     +-- DB SCHEMA     TO unload_filename ---------------------+
                     +-- TABLE   FROM on_patt.tn_patt TO unload_filename [WITH NO INDEX] [AS [target_owner.]target_table]------+
                     +-- INDEX   FROM on_patt.tn_patt TO unload_filename ------+
                     +-- SCHEMA  FROM on_patt.tn_patt TO unload_filename ------+
                     +-- DATA    FROM on_patt.tn_patt TO unload_filename ------+
                     +-- DATA    FROM (select-statement) TO unload_filename ---+
                     +-- PROJECT FROM on_patt.pj_patt TO unload_filename ------+
                     +-- MODULE on_patt.module_patt FROM on_patt.pj_patt TO unload_filename --+
                     +-- PROC    FROM on_patt.proc_patt TO unload_filename ----+
                     +-- PROCEDURE FROM on_patt.proc_patt TO unload_filename --+
                     +-- PROC DEFINITION FROM on_patt.proc_patt TO unload_filename --+
                     +-- PROCEDURE DEFINITION FROM on_patt.proc_patt TO unload_filename --+
    Example:
      dmSQL> unload db to unldfile; /* currently connected db */    
      dmSQL> unload db schema to unldfile; /* all schemas in db      */    
      dmSQL> unload table from emptab to empfle; /* table: <emptab>        */
      dmSQL> unload table from emptab to empfle with no index; /* table: <emptab> no index */
      dmSQL> unload table from emptab to empfle as emptab2;/* table: <emptab> has target_table */
      dmSQL> unload index from emptab to empfle; /* index of <emptab>      */
      dmSQL> unload data from "p tab" to empfle; /* table: <p tab>         */
      dmSQL> unload data from (select * from emptab where empid > 20000) to empfle; /* data: "select-statement" */
      dmSQL> unload schema from a% to empshfle; /* tables: n-char with prefix "a" */
      dmSQL> unload table from b_ to emptbfle; /* tables: 2-char with prefix "b" */
      dmSQL> unload table from a\% to emptbfle; /* table: <a%>            */
      dmSQL> unload table from a\_ to emptbfle; /* table: <a_>            */
      dmSQL> unload table from a\\ to emptbfle; /* table: <a\>            */
      dmSQL> unload table from tom.%  to emptbfle; /* all tables of user tom     */
      dmSQL> unload table from %.work to emptbfle; /* table work(s) of all users */
      dmSQL> unload table from %      to emptbfle; /* all tables of current user */
      dmSQL> unload project from prj1 to pjfile; /* project: pj1 */
      dmSQL> unload module md1 from project prj1 to pjfile; /* module md1 from project: pj1 */
      dmSQL> unload procedure from sp% to spfile; /* procedures: n-char with prefix "sp" */
      dmSQL> unload procedure definition from sp% to spfile; /* all procedure definition of current user */


<[USE]> - (ISQL COMMAND)
    Grammar:
      |>>-- USE --+---------------------------------+--<<|
                  +-- NEW --------------------------+
                  +-- use_number -------------------+
                  +-- [DATABASE|DB] database_name --+
    Example:
      dmSQL> use;              /* list use usages                  */
      dmSQL> use 5;            /* switch to use 5                  */
      dmSQL> use db incomedb;  /* switch to the use connected to <incomedb> */
      dmSQL> use new;          /* find a free use and switch to it */


<[FETCH STATE]> 
    Example:
    dmSQL/Fet>   ;            /* fetch next default tuples    */
    dmSQL/Fet> 3 ;            /* fetch next 3 tuples          */
    dmSQL/Fet> all ;          /* fetch all the rest of tuples */
    dmSQL/Val/Fet> 5 ;        /* fetch next 5 tuples          */
    dmSQL/Val/Fet> end ;      /* abort fetch and quit FETCH state */
    dmSQL/Val/Fet> help end;  /* execute isql command: help   */


<[VALUE STATE]>
    Example:
    dmSQL/Val> v1,v2,... ;    /* value list for host variable */
    dmSQL/Val> end ;          /* quit VALUE state          */
    dmSQL/Val> set ;          /* execute isql command: set */


<[END]> - (ISQL COMMAND)
    Grammar:
      |>>-- END --<<|       
    Example:
    dmSQL/Val> end ;          /* abort value-list input and quit VALUE state */
    dmSQL/Val/Fet> end ;      /* abort fetch and quit FETCH state  */


<[COMMENT]> - (ISQL COMMAND)
    Grammar:
      |>>-- // comment_to_end_of_line -----<<|
      |>>-- /* comment */ --<<|
    Example:
        /* comments can only be used in script files */            
        set;                              // comment(to end-of-line)
        connect to EMPDB SYSADM PASSWD;   /* connect to db        */
        //
        // table schema 
        create table tb1                     /* tb1               */
                           (name char(20),   // name
                            age  int,        /* age               */
                            addr char(100),  // address 
                            tel  char(10)    /* telephone number  */
                             );      


______________________________________________________________________________
* built-in functions *


<[Built-in Functions]>
   Classes of built-in functions:
      String Functions
      Numeric Functions
      Date Time Timestamp Functions
      System Functions
      Add On Functions
      Xml Functions

<[String Functions]>
   ASCII
   CHAR
   CONCAT
   CHAR[ACTER]_LENGTH
   INSERT
   LCASE
   LEFT
   LENGTH
   LOCATE
   LOWER
   LTRIM
   POSITION
   REPEAT
   REPLACE
   RIGHT
   RTRIM
   SPACE
   SUBSTRING
   TRIM
   UCASE
   UPPER
   STRTOINT
   FORMAT_BYTES


<[Numeric Functions]>
   ABS
   ACOS
   ASIN
   ATAN
   ATAN2
   ATOF
   CEILING
   COS
   COSH
   COT
   DEGREES
   EXP
   FIX
   FLOOR
   FREXPE
   FREXPM
   FTOA
   HYPOT
   LDEXP
   LOG
   LOG10
   MOD
   MODFI
   MODFM
   PI
   POW
   RADIANS
   RAND
   RND
   ROUND
   SIGN
   SIN
   SINH
   SQRT
   TAN
   TANH
   

<[Time and Date Functions]>
   ADD_DAYS
   ADD_MONTHS
   ADD_YEARS
   ADD_HOURS
   ADD_MINS
   ADD_SECS
   CURDATE
   CURTIME
   DATE
   DAYNAME
   DAYOFMONTH
   DAYOFWEEK
   DAYOFYEAR
   DAYS_BETWEEN
   HMS
   HOUR
   LAST_DAY
   MDY
   MINUTE
   MONTH
   MONTHNAME
   NEXT_DAY
   NOW
   QUARTER
   SECS_BETWEEN
   SECOND
   TIMESTAMPADD
   TIMESTAMPDIFF
   DATEPART
   TIMEPART
   FRACTIONPART
   WEEK
   YEAR
   INVDATE
   INVTIME
   INVTIMESTAMP
   CURRENT_DATE
   CURRENT_TIME
   CURRENT_TIMESTAMP


<[System Functions]>
   DATABASE
   USER
   FILENAME
   UNCNAME
   FILELEN
   FILEEXIST
   GETEXTNAME
   CURRENT_USER
   SESSION_USER
   IFNULL
   DBFILE_SIZE


<[Add On Functions]>
   HIGHLIGHT
   HTMLHIGHLIGHT
   SUBBLOB
   SUBBLOBTOCHAR
   SUBBLOBTOBIN
   PURETEXT
   CHECKMEDIAFORMAT
   DOCTOTXT
   XLSTOTXT
   PPTTOTXT
   PDFTOTXT
   HTMTOTXT
   HITCOUNT
   HITPOS
   BLOBLEN
   HTMLTITLE
   TO_DATE
   U16TOU8
   U8TOU16
   DATETOSTR
   TIMETOSTR
   TIMESTAMPTOSTR
   AES_ENCRYPT
   AES_DECRYPT

<[Xml Functions]>
   XMLELEMENT
   XMLFOREST
   XMLCOMMENT   
   XMLAGG


<[ASCII]>
  ASCII(STRING string_exp)
     Returns the ASCII code value of the leftmost character of string_exp
     as an integer


<[CHAR]>
  CHAR(INT code)
     Return the character that has the ASCII code value specified by code.
     The value of code should be between 0 and 255; otherwise, the return
     value is data source-dependent.


<[CONCAT]>
  CONCAT(STRING string_expr1, STRING string_expr2)
     Returns a character string that is the result of concatenating
     string_expr2 to string_ep1. The resulting string is DBMS dependent.
     For example, if the column represented by sring_exp1 contained a NULL
     value, DB2 would return NULL, but SQL Server would return the non-NULL
     string.
     Comment: if string_expr1 is NULL and string_expr2 is NULL return NULL
              if string_expr1 is NOT NULL and string_expr2 is NULL return
                 string_expr1
              if string_expr1 is NULL and string_expr2 is NOT NULL return
                 string_expr2


<[INSERT]>
  INSERT(STRING string_exp1, INT start, INT length, STRING string_exp2)
     Returns a character string where length characters have been
     deleted from string_exp1 beginning at start and where string_exp2
     has been inserted into string_exp1, beginning at start
     comment: if string_exp1 is NULL return NULL
              if start, length or string_exp2 is NULL return string_exp1
              if start <= 0 or length < 0 return string_exp1
              if start > length of string_exp1 return string_exp1


<[LCASE]>
  LCASE(STRING string_exp)
    Converts all upper case characters in string_exp to lower case

<[LOWER]>
  LOWER(STRING string_exp)
    Converts all upper case characters in string_exp to lower case


<[LEFT]>
  LEFT(STRING string_exp, INT count)
     Returns the left most count of characters of string_exp
     if count < 0 return NULL


<[LENGTH]>
  LENGTH(STRING string_exp)
     Returns the number of characters in string_exp, excluding trailing
     blanks and the string termination character.

<[CHAR_LENGTH/CHARACTER_LENGTH]>
  CHAR_LENGTH(STRING string_exp) or CHARACTER_LENGTH(STRING string_exp)
     Returns the number of characters in string_exp, excluding trailing
     blanks and the string termination character.


<[LOCATE]>
  LOCATE(STRING string_exp1, STRING string_exp2,  INT start)
     Returns the starting position of the first occurrence of
     string_exp1 within string_exp2, The search for the first
     occurrence of string_exp1 begins with the first character position
     in string_exp2 unless the optional argument, start, is specified.
     If start is specified, the search begins with the character
     position indicated by the value of start.  The first character
     position in string_exp2 is indicated by the value 1.  If 
     string_exp1 is not found within string_exp2, the value 0 will be
     returned.
     comment: (in that order)
        (1) if either string_exp1 or string_exp2 is null, the result should
            be null
        (2) if start is null, return 0
        (3) if string_exp1 is an empty string, the result should be 1

<[POSITION]>
  POSITION(string_exp1 IN string_exp2)
  comment:
        the same function as LOCATE(string_exp1, string_exp2, 0)

<[LTRIM]>
  LTRIM(STRING string_exp)
     Return the characters of string_exp, with leading spaces removed


<[REPEAT]>
  REPEAT(STRING string_exp, count)
     Returns a character string composed of string_exp repeated count times.
     Comment:   
         if string_exp is null or count is null return null
         if count < 0 or string_exp is empty string return empty string


<[REPLACE]>
  REPLACE(STRING string_exp1, STRING string_exp2, STRING string_exp3)
     Replaces all occurrences of string_exp2 in string_exp1 with  
     string_exp3
     Comment:   
       if string_exp1 is null return null
       if string_exp2 or string_exp3 is null return string_exp1
       if string_exp2 length is 0 return string_exp1


<[RIGHT]>
  RIGHT(STRING string_exp, int count)
     Returns the rightmost count of characters of string_exp
     if count < 0 return NULL


<[RTRIM]>
  RTRIM(STRING string_exp)
     Return the characters of string_exp, with trailing spaces removed


<[SPACE]>
  SPACE(int count)
     Returns a character string consisting of count spaces
     comment: if count < 0 return NULL  

<[STRTOINT]>
  STRTOINT(STRING string_exp)
     Converts the string to an bigint, when the string argument is NULL,
     a NULL value will be returned. An error will be returned if the string 
     cannot be converted to an bigint.

<[FORMAT_BYTES]>
  FORMAT_BYTES(BIGINT bytes)
     Convert the bytes to human-readable format and return the string.  
     When the input argument is NULL, a NULL vaule is returned.  An error 
     will be returned if the bytes cannot be converted.
  Example:
     FORMAT_BYTES(1048576);

<[SUBSTRING]>
  (1) SUBSTRING(STRING string_exp, int start, int length)
      Returns a character string that is derived from string_exp
      beginning at the character position specified by start for
      length characters
      comment:
          if string_exp is null return null
          if start or length is null return string_exp
          if start < 0 or length < 0 return null
          if start > string_exp length return null
          if length is 0 return empty string
  (2) SUBSTRING(string_exp FROM start FOR length)
      comment:
      The same function as (1) except that when length is 0,
      the rest of string from start to the end of string is displayed.

<[TO_DATE]>
  TO_DATE( string_expr, date_format_string )
     Converts the string to a DATE by the date_format_string.  The supported format
     for year is 'Y' or 'y', month is 'M', 'm' or 'mon', day is 'D' or 'd' and 
     the seperator is '-' or '/'.
     
<[U16TOU8]>
  U16TOU8( LONG VARCHAR nclob )
     convert UTF16 code to UTF8 code. 
     Returns BLOB type.
  Comment:
     NCHAR,CLOB type are also valid input.
     CHAR type is also valid input,  but the results possible to convert error.
     BLOB type is invalid input.        

<[U8TOU16]>
  U8TOU16( LONG VARBINARY blob )
     convert UTF8 code to UTF16 code. 
     Returns NCLOB type.
  Comment:
     NCHAR,NCLOB type are also valid input.
     CHAR,CLOB type are also valid input, but the results possible to convert error.
     if put UTF-8 code in CLOB type, the results are convert correctly.       

<[DATETOSTR]>
  DATETOSTR( DATE, date_format_string )
     Converts the DATE to a string by the date_format_string.
     Comment: The ivalid date_format_string is 
        ''
        'mm/dd/yy'
        'mm-dd-yy'
        'dd/mon/yy' 
        'dd-mon-yy'
        'mm/dd/yyyy'
        'mm-dd-yyyy'
        'yyyy/mm/dd'
        'yyyy-mm-dd'
        'dd/mon/yyyy'
        'dd-mon-yyyy'
        'dd.mm.yyyy'
        'yyyy.mm.dd'

<[TIMETOSTR]>
  TIMETOSTR( DATE, time_format_string )
     Converts the TIME to a string by the time_format_string.
     Comment: The ivalid time_format_string is 
        ''
        'hh:mm:ss.fff'
        'hh:mm:ss'
        'hh:mm'
        'hh'
        'hh:mm:ss.fff tt'
        'hh:mm:ss tt'
        'hh:mm tt'
        'hh tt'
        'tt hh:mm:ss.fff'
        'tt hh:mm:ss'
        'tt hh:mm'
        'tt hh'

<[TIMESTAMPTOSTR]>
  TIMESTAMPTOSTR( TIMESTAMP, date_format_string, time_format_string )
     Converts the TIMESTAMP to a string by the date_format_string and time_format_string.
     Comment: The ivalid date_format_string is 
        ''
        'mm/dd/yy'
        'mm-dd-yy'
        'dd/mon/yy' 
        'dd-mon-yy'
        'mm/dd/yyyy'
        'mm-dd-yyyy'
        'yyyy/mm/dd'
        'yyyy-mm-dd'
        'dd/mon/yyyy'
        'dd-mon-yyyy'
        'dd.mm.yyyy'
        'yyyy.mm.dd'
     Comment: The ivalid time_format_string is 
        ''
        'hh:mm:ss.fff'
        'hh:mm:ss'
        'hh:mm'
        'hh'
        'hh:mm:ss.fff tt'
        'hh:mm:ss tt'
        'hh:mm tt'
        'hh tt'
        'tt hh:mm:ss.fff'
        'tt hh:mm:ss'
        'tt hh:mm'
        'tt hh'

<[AES_ENCRYPT]>
  AES_ENCRYPT( binary, key_string )
     encrypt the binary  to a cipher string, the cipher key is key_string

<[AES_DECRYPT]>
  AES_DECRYPT( binary, key_string )
     decrypt the cipher binary to a result,  the cipher key is key_string

<[TRIM]>
  TRIM([[[LEADING|TRAILING|BOTH] [match_exp]] FROM] string_exp)
     Return the characters of string_exp, with leading, trailing, or 
     both leading and trailing characters that are in match_exp removed
     comment:
         (1) if LEADING|TRAILING|BOTH is not specified, BOTH case is used
         (2) if match_exp is not specified, space character is used

<[UCASE]>
  UCASE(STRING string_exp)
     Converts all lower case characters in string_exp to uppercase.
     comment: if string_exp is null return null 

<[UPPER]>
  UPPER(STRING string_exp)
     Converts all lower case characters in string_exp to uppercase.
     comment: if string_exp is null return null 


<[ABS]>
  ABS(DOUBLE x)
      Returns the absolute value of x as a double-precision
      floating-point number.


<[ACOS]>
  ACOS(DOUBLE val)
      Returns the arc cosine of val in the range 0 to pi as
      a double-precision floating-point number.


<[ASIN]>
  ASIN(DOUBLE val)
      Returns the arc sin of val in the range -pi/2 to pi/2 as
      a double-precision floating-point number.


<[ATAN]>
  ATAN(DOUBLE val)
      Returns the arc tangent of val in the  range  -pi/2  to
      pi/2 as a double-precision floating-point number.


<[ATAN2]>
  ATAN2(DOUBLE y, DOUBLE x)
      Returns the arc tangent of y/x in the  range -pi to pi
      as a double-precision floating-point number.


<[ATOF]>
  ATOF(CHAR(30) str)
      Returns as a double-precision floating-point number
      the  value represented by the character string pointed to by
      str.


<[CEILING]>
  CEILING(DOUBLE val)
      Returns the least integral value  greater  than  or
      equal  to  val as a double-precision floating-point number.


<[COS]>
  COS(DOUBLE rad_val)
      Returns the cosine of rad_val as a double-precision
      floating-point number. 
      rad_val is expressed in radians.


<[COSH]>
  COSH(DOUBLE val)
      Compute direct hyperbolic function for a real argument.
      Returns a double-precision floating-point number.


<[COT]>
  COT(DOUBLE rad_val)
     Returns the cotangent of rad_val as
     a double-precision floating-point number.
     rad_val is expressed in radians.


<[DEGREES]>
  DEGREES(DOUBLE rad_val)
     Return the number of degrees converted from rad_val radians as
     a double-precision floating-point number.


<[DIFFERENCE]>
  DIFFERENCE(STRING string_expr1, STRING string_expr2)
     Returns an integer value from 0 to 4 to measure the difference between the 
     SOUNDEX() values of two strings. A value of 0 means weak or no similarity 
     between SOUNDEX() values; 4 means strongly similar or identical SOUNDEX() values.


<[EXP]>
  EXP(DOUBLE x)
      Returns the exponential function e**x as
      a double-precision floating-point number.


<[FIX]>
  FIX(DEC(38,19) x) or RND(DEC(38,19) x)
      Returns the integral part of the real number x as an
      bigint number.


<[FLOOR]>
  FLOOR(DOUBLE x)
      Returns the greatest integral  value  less  than  or
      equal  to x as a double-precision floating-point number.


<[FREXPE]>
  FREXPE(DOUBLE)
      Returns the exponent of the real number val as an integer number.
      The base of the exponent is 2.


<[FREXPM]>
  FREXPM(DOUBLE val)
      Returns the mantissa of the real number val as a double-precision
      floating-point number.
      The base of the exponent is 2.


<[FTOA]>
  FTOA(DOUBLE val, INT sca, CHAR(1) mode)
      Returns a string which is converted from the given value val.
      The number of digits after decimal point is specified by sca.
      If mode is 'f' or 'F', the string contains a decimal point   
      without an exponential sign 'E'; for example, given val = 123.456789,
      sca = 2, mode = 'f', the string will be '123.45'.
      If mode is 'e' or 'E', the string contains an exponential sign
      'e' or 'E'; for example, given val = 123.456789,
      sca = 2, mode = 'E', the string will be '1.23E2'.


<[HYPOT]>
  HYPOT(DOUBLE x,DOUBLE y)
      Returns a double-precision floating-point number z where
      z*z = x*x + y*y. 


<[LDEXP]>
  LDEXP(DOUBLE x,INT n)
      Returns x * 2**n as a double-precision floating-point number.


<[LOG]>
  LOG(DOUBLE x)
      Returns the natural logarithm of x as
      a double-precision floating-point number.


<[LOG10]>
  LOG10(DOUBLE x)
      Return the logarithm to base 10 as
      a double-precision floating-point number.


<[MOD]>
  MOD(DOUBLE x,DOUBLE y)
      Returns the remainder (modulus) of x divided by y as
      a double-precision floating-point number.


<[MODFI]>
  MODFI(DOUBLE value)
      Return the integer part of the real number value as
      a double-precision floating-point number.


<[MODFM]>
  MODFM(DOUBLE value)
      Return the scale part of the real number value as
      a double-precision floating-point number.


<[PI]>
  PI()
     Returns the constant value 3.1415926535897936 as
     a DECIMAL(17,16). 


<[POW]>
  POW(DOUBLE x,DOUBLE y) or POWER(DOUBLE x, DOUBLE y)
      Returns x**y as a double-precision floating-point number.


<[POWER]>
  POW(DOUBLE x,DOUBLE y) or POWER(DOUBLE x, DOUBLE y)
      Returns x**y as a double-precision floating-point number.


<[RADIANS]>
  RADIANS(DOUBLE deg)
     Return the number of radians converted from deg degrees as
     a double-precision floating-point number.


<[RAND]>
  RAND()
      Return a random integer value.


<[RND]>
  FIX(DEC(38,19) x) or RND(DEC(38,19) x)
      Returns the integeral part of the real number x as an
      bigint number.


<[ROUND]>
  ROUND(DEC(38,19) x)
      Returns the closest bigint number of the real number x.


<[SIGN]>
  SIGN(DOUBLE val)
     Returns the sign of a number codes as +1 for positive,
     0 for zero, and -1 for negative. 
     Returns an integer value 1, 0 or -1.


<[SIN]>
  SIN(DOUBLE rad_val)
      Returns the sine of rad_val as a double-precision
      floating-point number.
      rad_val is expressed in radians.


<[SINH]>
  SINH(DOUBLE val)
      Compute direct hyperbolic function for a real argument.
      Returns a double-precision floating-point number.


<[SOUNDEX]>
  SOUNDEX(STRING string_exp)
      Returns a four digit SOUNDEX code based on the sound of the english words in 
      the string_exp.


<[SQRT]>
  SQRT(DOUBLE x)
     Return a double-precision floating-point number y where
     x = y*y. 


<[TAN]>
  TAN(DOUBLE rad_val)
     Returns the tangent of rad_val as
     a double-precision floating-point number.
     rad_val is expressed in radians.


<[TANH]>
  TANH(DOUBLE val)
     Compute direct hyperbolic function for a real argument.
     Returns a double-precision floating-point number.


<[ADD_DAYS]>
  ADD_DAYS(DATE date_val,INT s)
     Returns a date which is got from adding s days to date_val.
     s can be a negative number.


<[ADD_MONTHS]>
  ADD_MONTHS(DATE date_val,INT s)
     Returns a date which is got from adding s months to date_val.
     s can be a negative number.


<[ADD_YEARS]>
  ADD_YEARS(DATE date_val,INT s)
     Returns a date which is got from adding s years to date_val.
     s can be a negative number.


<[ADD_HOURS]>
  ADD_HOURS(TIME time_val,INT s)
     Returns a time which is got from adding s hours to time_val.
     s can be a negative number.


<[ADD_MINS]>
  ADD_MINS(TIME time_val,INT s)
     Returns a time which is got from adding s minutes to time_val.
     s can be a negative number.


<[ADD_SECS]>
  ADD_SECS(TIME time_val,INT s)
     Returns a time which is got from adding s seconds to time_val.
     s can be a negative number.


<[CURDATE]>
  CURDATE()
     Return current date.

<[CURRENT_DATE]>
  CURRENT_DATE ::=  CURRENT_DATE [()]
     Return current date.

<[CURTIME]>
  CURTIME()
     Return current time.

<[CURRENT_TIME]>
  CURRENT_TIME ::=  CURRENT_TIME [()]
     Return current time.

<[DATE]>
  DATE(DATE date1)
     Internally, date1 is stored as an integer in database.
     DATE(date1) will returns the integer value of date1.  


<[DAYNAME]>
  DAYNAME(DATE date1)
     Returns a character string containing the data-source specific
     name of the day (for example, Sunday through Saturday). 


<[DAYOFMONTH]>
  DAYOFMONTH(DATE date1)
     Returns the day of the month in date1 as an integer value in the
     range 1-31.


<[DAYOFWEEK]>
  DAYOFWEEK(DATE date1)
     Returns the day of the week in date1 as an integer value in the range
     of 1-7, where 1 represent Sunday. 



<[DAYOFYEAR]>
  DAYOFYEAR(DATE date1)
     Returns the day of the year in date1 as an integer value in the range
     1-366.


<[DAYS_BETWEEN]>
  DAYS_BETWEEN(DATE date1,DATE date2)
     Returns the number of days between the given two dates.
     date1 can be earlier or later than date2. 


<[HMS]>
  HMS(INT hour_val,INT minute_val,INT second_val) 
     Returns the time containing hour = hour_val, minute = minute_val, and
     second = second_val.


<[HOUR]>
  HOUR(TIME time1)
     Returns the hour in time1 as an integer value in the range 0-23.


<[LAST_DAY]>
  LAST_DAY(DATE date1)
      Returns the last date of the month which date1 belongs to.


<[MDY]>
  MDY(INT month_val, INT day_val, INT year_val)
     Returns the date containing month = month_val, day = day_val, and
     year = year_val.


<[MINUTE]>
  MINUTE(TIME time1)
     Returns the minute in time1 as an integer value in the range of 0-59.


<[MONTH]>
  MONTH(DATE date1)
     Returns the month in date1 as an integer value in the range of 1-12.


<[MONTHNAME]>
  MONTHNAME(DATE date1)
     Returns a character string containing the data-source specific
     name of the month (for example, Jan through Dec). 


<[NEXT_DAY]>
  NEXT_DAY(DATE date1, CHAR(9) WeekDay)
     Returns the date of the next first WeekDay.
     For example, date1 = '10/18/1995' is Wednesday,
                  given WeekDay = 'Monday', 
                  NEXT_DAY('10/18/1995', 'Monday') will return '10/23/1995'.


<[NOW]>
  NOW()
     Returns current date and time as a timestamp value.

<[CURRENT_TIMESTAMP]>
  CURRENT_TIMESTAMP ::=  CURRENT_TIMESTAMP [()]
     Returns current date and time as a timestamp value.


<[QUARTER]>
  QUARTER(DATE date1)
     Returns the quarter in date1 as an integer value in the range
     of 1-4, where 1 represents January 1 through March 31.


<[SECS_BETWEEN]>
  SECS_BETWEEN(TIME time1,TIME time2)
     Returns the number of seconds between the given two times.
     time1 can be earlier or later than time2.


<[SECOND]>
  SECOND(TIME time1)
     Returns the second in time1 as an integer value in the range of 0-59.


<[TBEG5]>
  TBEG5(TIME time1)
     Return the beginning time of a 5 minutes period.


<[TBEG15]>
  TBEG15(TIME time1)
     Return the beginning time of a quarter (15 minutes) period.


<[TBEG30]>
  TBEG30(TIME time1) 
     Return the beginning time of a half hour (30 minutes) period.


<[TBEG60]>
  TBEG60(TIME time1) 
     Return the beginning time of a hour (60 minutes) period.


<[TIMESTAMPADD]>
  TIMESTAMPADD(STRING interval, INT integer_exp, TIMESTAMP ts)
     Returns the timestamp calculated by adding integer_exp intervals of
     type interval to timestamp ts. 


<[TIMESTAMPDIFF]>
  TIMESTAMPDIFF(STRING interval, TIMESTAMP ts1, TIMESTAMP ts2)
     Returns number of intervals of type interval of ts2 - ts1
     The type of the result number is double, which may be negative.


<[DATEPART]>
  DATEPART(TIMESTAMP ts1)
     Returns date part of ts1
     For example, if ts1 = '1996-01-16 10:10:10', DATEPART(ts1) will return
     '1996-01-16'


<[TIMEPART]>
  TIMEPART(TIMESTAMP ts1)
     Returns date part of ts1
     For example, if ts1 = '1996-01-16 10:10:10', TIMEPART(ts1) will return
     '10:10:10'

<[FRACTIONPART]>
  FRACTIONPART(TIMESTAMP ts1)
     Returns fraction part of ts1
     For example, if ts1 = '1996-01-16 10:10:10.882', TIMEPART(ts1) will return
      882000000

<[WEEK]>
  WEEK(DATE date1)
     Returns the week of the year in date1 as an integer value in the range
     of 1-53.


<[YEAR]>
  YEAR(DATE date1)
     Returns the year in date1 as an integer value.
     The range is from 1 to 9999.


<[INVDATE]>
  INVDATE(DATE date1)
     Returns 1, 0 or -1.
     If returns 1, it means date1 is an invalid date (out of date range)
     If returns 0, it means date1 is a valid date.
     If returns -1, it means date1 is NULL.


<[INVTIME]>
  INVTIME(TIME time1)
     Returns 1, 0 or -1.
     If returns 1, it means time1 is an invalid time (out of time range)
     If returns 0, it means time1 is a valid time.
     If returns -1, it means time1 is NULL.


<[INVTIMESTAMP]>
  INVTIMESTAMP(TIMESTAMP ts1)
     Returns 1, 0 or -1.
     If returns 1, it means ts1 is an invalid timestamp (out of timestamp range)
     If returns 0, it means ts1 is a valid timestamp.
     If returns -1, it means ts1 is NULL.


<[DATABASE]>
  DATABASE()
     Returns the name of the database corresponding to the connection handle
     (hdbc).(The name of the database is also available by calling 
     SQLGetConnectOption with the SQL_CURRENT_QUALIFIER connection option).


<[USER]>
  USER()
     Returns the user's authorization name. (The user's authorization name
     is also available via SQLGetInfo by specifying the information type: 
     SQL_USER_NAME)

<[CURRENT_USER]>
  CURRENT_USER
     Returns the user's authorization name who is currently connected.
     For now, it's the same as USER().

<[SESSION_USER]>
  SESSION_USER
     Returns the user name of the user who open the database connection.
     Currently it's the same as USER().

<[IFNULL]>
  IFNULL (expr1, expr2)
     If expr1 is null, expr2 is returned. If expr1 is not null, expr1 is returned. 
     The possible data type or types of value must be compatible with the data type of expr1.

<[DBFILE_SIZE]>
  DBFILE_SIZE(STRING dbfile_keyword)
     Return the file or directory size by searching the dbfile or keyword in the dmconfig.ini
     file and get its physical file or directory size if specified config keyword such as DB_DBDIR, 
     DB_FODIR,DB_TMPDIR, DB_BKDIR, DB_BKODR and DB_LGDIR.
     Example:
         select DBFILE_SIZE(FILE_NAME) FROM SYSFILE;
         select DBFILE_SIZE('DB_DBDIR');

<[FILENAME]>
  FILENAME(FILE fileObj)
     Return a string of file name.


<[UNCNAME]>
  UNCNAME(FILE fileObj)
     Return a string of file name in UNC format. (For Windows NT only)


<[FILELEN]>
  FILELEN(FILE fileObj)
     Return an integer value of file length.

<[FILEEXIST]>
  FILEEXIST(FILE fileObj)
     Returns 1, 0, negative number or NULL.
     If returns 1, it means the file of fileObj exists.
     If returns 0, it means the file of fileObj doesn't exist.
     If returns negative number, means the fileobj has other error
     If returns NULL, it means fileObj is NULL.

<[INTERVAL]>
     If interval = SQL_TSI_YEAR        or 'SQL_TSI_YEAR'        or 'Y', which means years   
     If interval = SQL_TSI_QUARTER     or 'SQL_TSI_QUARTER'     or 'Q', which means quarters
     If interval = SQL_TSI_MONTH       or 'SQL_TSI_MONTH'       or 'M', which means months 
     If interval = SQL_TSI_WEEK        or 'SQL_TSI_WEEK'        or 'W', which means weeks  
     If interval = SQL_TSI_DAY         or 'SQL_TSI_DAY'         or 'D', which means days   
     If interval = SQL_TSI_HOUR        or 'SQL_TSI_HOUR'        or 'h', which means hours  
     If interval = SQL_TSI_MINUTE      or 'SQL_TSI_MINUTE'      or 'm', which means minutes
     If interval = SQL_TSI_SECOND      or 'SQL_TSI_SECOND'      or 's', which means seconds
     If interval = SQL_TSI_FRAC_SECOND or 'SQL_TSI_FRAC_SECOND' or 'f', which means fractions

<[HIGHLIGHT]>
  HIGHLIGHT(LONG VARCHAR source, STRING pattern, INT sensitive, 
            STRING pretag, STRING endtag)
    Returns nclob that after highlighting the 
    pattern in source.
      sensitive denotes whether to match English in sensitive mode
      pretag and endtag will be added just before and after the matched 
      pattern

<[HITCOUNT]>
  HITCOUNT(LONG VARCHAR source, STRING pattern, INT sensitive)
    Returns the count of the patterns found in the source.
      sensitive denotes whether to match English in sensitive mode

<[HITPOS]>
  HITPOS(LONG VARCHAR source, STRING pattern, INT sensitive, 
         INT n, INT return_type)
    Returns the position of n-th pattern found in the source. The
    position is count from 1. If return 0, it means the n-th pattern 
    is not found.
      sensitive denotes whether to match English in sensitive mode
      return_type indicates the position information to return
        0: returns begin offset (default setting)
        1: returns end offset
        2: returns pattern length (end offset - begin offset + 1)
        3: returns the combination information of begin offset (in the 
           higher 24 bits) and pattern length (in the lower 8 bits)

<[HTMLHIGHLIGHT]>
  HTMLHIGHLIGHT(LONG VARCHAR source, STRING pattern, INT sensitive, 
                STRING pretag, STRING endtag)
    Returns nclob after highlighting the pattern in 
    html source. Only the text outside HTML tag will be highlighted.
      sensitive denotes whether to match English in sensitive mode
      pretag and endtag will be added just before and after the matched 
      pattern
    
<[HTMLTITLE]>
  HTMLTITLE(LONG VARCHAR source)
    Returns the HTML title in the HTML source.

<[BLOBLEN]>
  BLOBLEN(LONG VARBINARY object)
    Returns the length of a BLOB object. The object type can be 
    LONG VARBINARY, LONG VARCHAR or FILE.


<[SUBBLOB]>
  SUBBLOB(LONG VARBINARY blob, INT start, INT length)
    Returns a temporary BLOB that is derived from input blob
    beginning at the byte position specified by start for length
    bytes. The first byte of BLOB is counted from 1.
      If blob is NULL return NULL.
      If start or length is NULL return the temporary BLOB
        which is the same as blob.
      If start <= 0 or length < 0 return NULL.
      If start > blob length return NULL.
      If length is 0 return empty temporary BLOB.


<[SUBBLOBTOCHAR]>
  SUBBLOBTOCHAR(LONG VARBINARY blob, INT start, INT length)
    Returns a character string at most 3992 bytes that is derived
    from input blob beginning at the byte position specified by
    start for length bytes. The first byte of BLOB is counted from 1.
      If blob is NULL return NULL.
      If start or length is NULL return the string the same as blob.
      If start <= 0 or length < 0 return NULL.
      If start > blob length return NULL.
      If length is 0 return empty string.


<[SUBBLOBTOBIN]>
  SUBBLOBTOBIN(LONG VARBINARY blob, INT start, INT length)
    Returns a binary string at most 3992 bytes that is derived
    from input blob beginning at the byte position specified by
    start for length bytes. The first byte of BLOB is counted from 1.
      If blob is NULL return NULL.
      If start or length is NULL return the string the same as blob.
      If start <= 0 or length < 0 return NULL.
      If start > blob length return NULL.
      If length is 0 return empty string.


<[PURETEXT]>
  PURETEXT(LONG VARBINARY blob)
    Converts blob to a temporary BLOB containing the pure text of blob as unicode.
    Comment:
       When use PURETEXT on the column with media-domain-type or a domain with 
       text converter will implicitly call the text converter function.


<[GETEXTNAME]>
  GETEXTNAME(LONG VARBINARY blob)
    Returns the extension name of blob, e.g. 'BMP', 'AVI', etc.


<[CHECKMEDIAFORMAT]>
  CHECKMEDIAFORMAT(LONG VARBINARY blob, STRING media_format)
   Returns whether the blob content matches the specified media format.
     If returns 1, it means the blob's content matches the specified media format
     If returns 0, it means the blob's content does not match the specified media format.
     If returns NULL, it means the blob is NULL.
   Comment:
      The valid media_format:
         - 'DOC': Microsoft Words Document
         - 'XLS': Microsoft Excel Document
         - 'PPT': Microsoft Power Point Document
         - 'HTM': HyperText Markup Language
         - 'XML': Extensible Markup Language
         - 'PDF': Portable Document Format


<[DOCTOTXT]>
  DOCTOTXT(LONG VARBINARY blob)
    Converts word document to a temporary BLOB containing the pure text of blob as unicode.
    Returns temp blob or NULL.
      It returns NULL when blob is NULL.
      It returns temp blob when it can convert the blob to pure text successfully.
      If returns error when it cannot convert the blob to pure text.


<[XLSTOTXT]>
  XLSTOTXT(LONG VARBINARY blob)
    Converts excel document to a temporary BLOB containing the pure text of blob as unicode.
    Returns temp blob or NULL.
      It returns NULL when blob is NULL.
      It returns temp blob when it can convert the blob to pure text successfully.
      If returns error when it cannot convert the blob to pure text.


<[PPTTOTXT]>
  PPTTOTXT(LONG VARBINARY blob)
    Converts powerpoint document to a temporary BLOB containing the pure text of blob as unicode.
    Returns temp blob or NULL.
      It returns NULL when blob is NULL.
      It returns temp blob when it can convert the blob to pure text successfully.
      If returns error when it cannot convert the blob to pure text.


<[PDFTOTXT]>
  PDFTOTXT(LONG VARBINARY blob)
    Converts pdf document to a temporary BLOB containing the pure text of blob as unicode.
    Returns temp blob or NULL.
      It returns NULL when blob is NULL.
      It returns temp blob when it can convert the blob to pure text successfully.
      If returns error when it cannot convert the blob to pure text.


<[HTMTOTXT]>
  HTMTOTXT(LONG VARBINARY blob)
    Converts html document to a temporary BLOB containing the pure text of blob as local code.
    Returns temp blob or NULL.
      It returns NULL when blob is NULL.
      It returns temp blob when it can convert the blob to pure text successfully.
      
      
<[XMLELEMENT]>
  XMLELEMENT(name name [, xmlattributes(value AS attname [, ... ])] [, content, ...])
    Produces an xml element with the given name, attributes and content. 
    name : xml element tag name. If the name contains invalid name character, 
           it will use hex format to replace it. 
           For example, if name is 'phone number'(there is a space between phone and number), 
           the tag name would be replaced as phone_x20_number.
    attname : attribute name
    content : can be plain text, sub xml element, or xml comment. 
      If there is no content, then the xml element would be displayed as an empty element like <ABC/>. 
      No extra new line added after start or end tag.
      
      
<[XMLFOREST]>
  XMLFOREST(content [AS name] [, ...])
    Produces an xml forest (sequence) of elements using the given name and content.
      If name is not specified and the content value is a column reference, 
      then the default would be the column name.
      
      
<[XMLCOMMENT]>
  XMLCOMMENT(text)
    The input is sql expression which can produce nchar or char data. 
    The output is a string in XML comment format which starts with <!-- and ends with -->.
      If the text contains any escape characters (eg, < > & ), 
      those characters will be replaced with entity display.
      
            
<[XMLAGG]>
  XMLAGG(xml)
    Concatenates the input values across rows.
    The input of xmlagg should be an xml fragment. 
    The output is CLOB type            


______________________________________________________________________________
* system stored procedures *

<[GETSYSTEMOPTION]>
  GETSYSTEMOPTION(VARCHAR(32) option_name INPUT, VARCHAR(8576) option_value OUTPUT)
    Gets the system information specified by option_name.
    Comment:
      The valid option_name: 
      - 'fodir' : the system file object directory (DB_FoDir).
      - 'lgsvr' : server log level (DB_LGSVR)
      - 'lgerr' : server log error level (DB_LGERR)
      - 'lgstm' : server log statment execution time over n secs (DB_LGSTM)
      - 'lgsys' : server log the system info (DB_LGSYS)
      - 'lgfsz' : server log file size (DB_LGFSZ)
      - 'lgfno' : server log file number (DB_LGFNO)
      - 'lgsql' : server log the sql command (DB_LGSQL)
      - 'lgpln' : server log the execution plan (DB_LGPLN)
      - 'lgpar' : server log the input parameter value (DB_LGPAR)
      - 'lglck' : server log extra lock time out informatin when it exceed 
                  error argument's length (DB_LGLCK)
      - 'lgdir' : server log directory (DB_LGDIR)
      - 'lgday' : the number of days to keep server log files (DB_LGDAY)
      - 'lgzip' : zip closed log files (DB_LGZIP)
      - 'bkchk' : whether check database before full backup and differential
                  backup (DB_BKCHK)
      - 'bkcmp' : the compact backup mode (DB_BKCMP)
      - 'bkdir' : directory to store backup journal files (DB_BKDIR)
      - 'bkfom' : the file object (FO) backup mode (DB_BKFOM)
      - 'bkfrm' : the format Backup Server used to name incremental backup 
                  journal files (DB_BKFRM)
      - 'bkful' : the percentage full of the journal files that triggers the 
                  backup server to perform an incremental backup (DB_BKFUL)
      - 'bkitv' : the backup time interval (DB_BKITV)
      - 'bkodr' : the directories where the backup server puts the previous 
                  version of full backup files (DB_BKODR)
      - 'bkrts' : whether the backup server includes the read-only tablespace 
                  files when performing a full-backup (DB_BKRTS)
      - 'bkspm' : the store procedure(SP) backup mode(DB_BKSPM) 
      - 'bksvr' : whether a backup server is activated (DB_BKSVR)
      - 'bktim' : the first time a backup server performs an incremental 
                  backup (DB_BKTIM)
      - 'bkzip' : whether the backup files are compressed by a backup server 
                  when performing full backups (DB_BKZIP)
      - 'bmode' : backup mode(DB_DBDIR)
      - 'ctblm' : the default lock mode used when creating a table (DB_CTBLM)
      - 'dbkmx' : the maximum number of differential backup after a full 
                  backup (DB_DBKMX)
      - 'dbktv' : the differential backup time interval (DB_DBKTV)
      - 'dbname': the database name of current connection
      - 'ddbmd' : whether the DDB (Distributed DataBase) function is enabled 
                  on the database server (DD_DDBMD)
      - 'eatrpt': the database server's Subscriber Daemon TCP/IP port 
                  number (DB_ETRPT)
      - 'extnp' : a size for DBMaker to extend autoextend tablespace (DB_EXTNP)
      - 'fbktm' : the first time the Backup Server will perform a full 
                  backup (DB_FBKTM)
      - 'fbktv' : the full backup time interval (DB_FBKTV)
      - 'fkchk' : turn on or turn off foreign key check at local connection
      - 'fosub' : the maximum number of file objects that may be stored in 
                  each system file object subdirectory (DB_FOSUB)
      - 'fullbkid' : the full backup id
      - 'idxdp' : auto drop index threshold for auto index daemon (DB_IDXDP)
      - 'idxln' : auto create index threshold for auto index daemon (DB_IDXLN)
      - 'idxtm' : the start time for auto index daemon (DB_IDXTM)
      - 'idxtv' : the auto index daemon interval (DB_IDXTV)
      - 'idxsv' : activate auto index daemon (DB_IDXSV)
      - 'isolv' : the default transaction isolation level when a user connects
                  to the database (DB_ISOLV)
      - 'letpt' : the Lock Escalation Threshold for escalating a page lock 
                  to a table lock (DB_LETPT)
      - 'letrp' : the Lock Escalation Threshold for an escalating rowlock to
                  a page lock (DB_LETRP)
      - 'lic_acl' : Access Control List
      - 'lic_bkserver' : Backup Server
      - 'lic_dbrep' : Database Replication
      - 'lic_dci' : Database Cobol Interface
      - 'lic_ddb' : Distributed Database
      - 'lic_edition' : Edition
      - 'lic_expiredate' : License Expiration Date
      - 'lic_freetrial' : Free Trial Period
      - 'lic_fulltext' : FullText Indexing
      - 'lic_hostconn' : Host Connection
      - 'lic_ioserver' : IO Server
      - 'lic_locale' : Locale Language
      - 'lic_maxconn' : Max Connection
      - 'lic_maxdbsize' : Max Database Size
      - 'lic_maxjnfsz' : Max Journal File Size
      - 'lic_maxpgsize' : Max Page Size
      - 'lic_netzc' : Network Compression    
      - 'lic_platform' : Platform 
      - 'lic_product' : Product Name  
      - 'lic_serialid' : Serial ID 
      - 'lic_startdate' : License Start Date
      - 'lic_upgrade' : Upgradable  
      - 'lic_userinfo' : User Information 
      - 'lic_version' : Version
      - 'sqlst' : the display mode of the SQL command monitor (DB_SQLST)
      - 'stsvr' : start update statistics daemon (DB_STSVR)
      - 'stmod' : the incremental update statistics mode for a 
                  database (DB_STMOD)
      - 'ststm' : the start time for update statistics (DB_STSTM)
      - 'ststv' : the update statistics daemon interval (DB_STSTV)
      - 'stssp' : the update statistics sample (DB_STSSP)
      - 'usrfo' : user file objects can be inserted in a database (DB_USRFO)
      - 'tde_status' : the status of tde column encryption

<[SETSYSTEMOPTION]>
  SETSYSTEMOPTION(VARCHAR(32) option_name INPUT, VARCHAR(8576) option_value INPUT)
    Sets the system option to the specified value.
    Comment:
      The valid option_name:
      - 'fodir' : on-line change the system file object directory (DB_FoDir).
                  option_name is the new full path. Empty string, i.e. '', disables
                  the feature of the system file object.
      - 'lgsvr' : server log level (DB_LGSVR)
      - 'lgerr' : server log error level (DB_LGERR)
      - 'lgstm' : server log statment execution time over n secs (DB_LGSTM)
      - 'lgsys' : server log the system info (DB_LGSYS)
      - 'lgfsz' : server log file size (DB_LGFSZ)
      - 'lgfno' : server log file number (DB_LGFNO)
      - 'lgsql' : server log the sql command (DB_LGSQL)
      - 'lgpln' : server log the execution plan (DB_LGPLN)
      - 'lgpar' : server log the input parameter value (DB_LGPAR)
      - 'lglck' : server log extra lock time out informatin when it exceed 
                  error argument's length (DB_LGLCK)
      - 'lgdir' : server log directory (DB_LGDIR)
      - 'lgday' : the number of days to keep server log files (DB_LGDAY)
      - 'lgzip' : zip closed log files (DB_LGZIP)
      - 'addslave' : add slave database address(RP_SLADR)
      - 'bkchk' : whether check database before full backup and differential
                  backup (DB_BKCHK)
      - 'bkcmp' : the compact backup mode (DB_BKCMP)
      - 'bkdir' : directory to store backup journal files (DB_BKDIR)
      - 'bkfom' : the file object (FO) backup mode (DB_BKFOM)
      - 'bkfrm' : the format Backup Server used to name incremental backup 
                  journal files (DB_BKFRM)
      - 'bkful' : the percentage full of the journal files that triggers the 
                  backup server to perform an incremental backup (DB_BKFUL)
      - 'bkitv' : the backup time interval (DB_BKITV)
      - 'bkodr' : the directories where the backup server puts the previous 
                  version of full backup files (DB_BKODR)
      - 'bkrts' : whether the backup server includes the read-only tablespace 
                  files when performing a full-backup (DB_BKRTS)
      - 'bkspm' : the store procedure(SP) backup mode(DB_BKSPM)  
      - 'bksvr' : whether a backup server is activated (DB_BKSVR)
      - 'bktim' : the first time a backup server performs an incremental 
                  backup (DB_BKTIM)
      - 'bkzip' : whether the backup files are compressed by a backup server 
                  when performing full backups (DB_BKZIP)
      - 'bmode' : backup mode(DB_DBDIR)
      - 'ctblm' : the default lock mode used when creating a table (DB_CTBLM)
      - 'dbkmx' : the maximum number of differential backup after a full 
                  backup (DB_DBKMX)
      - 'dbktv' : the differential backup time interval (DB_DBKTV)
      - 'ddbmd' : whether the DDB (Distributed DataBase) function is enabled 
                  on the database server (DD_DDBMD)
      - 'delslave' : delete slave datatbase address(RP_SLADR)
      - 'extnp' : a size for DBMaker to extend autoextend tablespace (DB_EXTNP)
      - 'fbktm' : the first time the Backup Server will perform a full 
                  backup (DB_FBKTM)
      - 'fbktv' : the full backup time interval (DB_FBKTV)
      - 'fkchk' : turn on or turn off foreign key check
      - 'fosub' : the maximum number of file objects that may be stored in 
                  each system file object subdirectory (DB_FOSUB)
      - 'idxdp' : auto drop index threshold for auto index daemon (DB_IDXDP)
      - 'idxln' : auto create index threshold for auto index daemon (DB_IDXLN)
      - 'idxtm' : the start time for auto index daemon (DB_IDXTM)
      - 'idxtv' : the auto index daemon interval (DB_IDXTV)
      - 'idxsv' : activate auto index daemon (DB_IDXSV)
      - 'letpt' : the Lock Escalation Threshold for escalating a page lock 
                  to a table lock (DB_LETPT)
      - 'letrp' : the Lock Escalation Threshold for an escalating rowlock to
                  a page lock (DB_LETRP)
      - 'lic_reload' : Reload License
      - 'sqlst' : the display mode of the SQL command monitor (DB_SQLST)            
      - 'stsvr' : start update statistics daemon (DB_STSVR)
      - 'stmod' : the incremental update statistics mode for a database (DB_STMOD)
      - 'ststm' : the start time for update statistics (DB_STSTM)
      - 'ststv' : the update statistics daemon interval (DB_STSTV)
      - 'stssp' : the update statistics sample (DB_STSSP)
      - 'sts_abort':  abort ongoing update statistics
      - 'usrfo' : user file objects can be inserted in a database (DB_USRFO)
      - 'fkchk' : disable foreign key check for current connection
      - 'startbackup' : option to act backup server to process full or 
                        incremental backup
      - 'tde_open' : to open tde column encryption
      - 'tde_open_auto' : open tde column encrypt automatically when connecting db
      - 'tde_close' : to close tde column encryption
      - 'tde_close_auto' : close tde column encrypt automatically

      
<[SETSYSTEMOPTIONW]>
  SETSYSTEMOPTIONW(VARCHAR(32) option_name INPUT, VARCHAR(8576) option_value INPUT)
    Sets the system option to the specified value and writes run time setting to dmconfig.ini.
    Comment:
      The valid option_name:
      - 'fodir' : on-line change the system file object directory (DB_FoDir).
                  option_name is the new full path. Empty string, i.e. '', disables
                  the feature of the system file object.
      - 'lgsvr' : server log level (DB_LGSVR)
      - 'lgerr' : server log error level (DB_LGERR)
      - 'lgstm' : server log statment execution time over n secs (DB_LGSTM)
      - 'lgsys' : server log the system info (DB_LGSYS)
      - 'lgfsz' : server log file size (DB_LGFSZ)
      - 'lgfno' : server log file number (DB_LGFNO)
      - 'lgsql' : server log the sql command (DB_LGSQL)
      - 'lgpln' : server log the execution plan (DB_LGPLN)
      - 'lgpar' : server log the input parameter value (DB_LGPAR)
      - 'lglck' : server log extra lock time out informatin when it exceed 
                  error argument's length (DB_LGLCK)
      - 'lgdir' : server log directory (DB_LGDIR)
      - 'lgday' : the number of days to keep server log files (DB_LGDAY)
      - 'lgzip' : zip closed log files (DB_LGZIP)
      - 'addslave' : add slave database address(RP_SLADR)
      - 'bkchk' : whether check database before full backup and differential
                  backup (DB_BKCHK)
      - 'bkcmp' : the compact backup mode (DB_BKCMP)
      - 'bkdir' : directory to store backup journal files (DB_BKDIR)
      - 'bkfom' : the file object (FO) backup mode (DB_BKFOM)
      - 'bkfrm' : the format Backup Server used to name incremental backup 
                  journal files (DB_BKFRM)
      - 'bkful' : the percentage full of the journal files that triggers the 
                  backup server to perform an incremental backup (DB_BKFUL)
      - 'bkitv' : the backup time interval (DB_BKITV)
      - 'bkodr' : the directories where the backup server puts the previous 
                  version of full backup files (DB_BKODR)
      - 'bkrts' : whether the backup server includes the read-only tablespace 
                  files when performing a full-backup (DB_BKRTS)
      - 'bkspm' : the store procedure(SP) backup mode(DB_BKSPM) 
      - 'bksvr' : whether a backup server is activated (DB_BKSVR)
      - 'bktim' : the first time a backup server performs an incremental 
                  backup (DB_BKTIM)
      - 'bkzip' : whether the backup files are compressed by a backup server 
                  when performing full backups (DB_BKZIP)
      - 'bmode' : backup mode(DB_DBDIR)
      - 'ctblm' : the default lock mode used when creating a table (DB_CTBLM)
      - 'dbkmx' : the maximum number of differential backup after a full 
                  backup (DB_DBKMX)
      - 'dbktv' : the differential backup time interval (DB_DBKTV)
      - 'ddbmd' : whether the DDB (Distributed DataBase) function is enabled 
                  on the database server (DD_DDBMD)
      - 'delslave' : delete slave datatbase address(RP_SLADR)
      - 'extnp' : a size for DBMaker to extend autoextend tablespace (DB_EXTNP)
      - 'fbktm' : the first time the Backup Server will perform a full 
                  backup (DB_FBKTM)
      - 'fbktv' : the full backup time interval (DB_FBKTV)
      - 'fkchk' : turn on or turn off foreign key check
      - 'fosub' : the maximum number of file objects that may be stored in 
                  each system file object subdirectory (DB_FOSUB)
      - 'idxdp' : auto drop index threshold for auto index daemon (DB_IDXDP)
      - 'idxln' : auto create index threshold for auto index daemon (DB_IDXLN)
      - 'idxtm' : the start time for auto index daemon (DB_IDXTM)
      - 'idxtv' : the auto index daemon interval (DB_IDXTV)
      - 'idxsv' : activate auto index daemon (DB_IDXSV)
      - 'letpt' : the Lock Escalation Threshold for escalating a page lock 
                  to a table lock (DB_LETPT)
      - 'letrp' : the Lock Escalation Threshold for an escalating rowlock to
                  a page lock (DB_LETRP)
      - 'sqlst' : the display mode of the SQL command monitor (DB_SQLST)
      - 'stsvr' : start update statistics daemon (DB_STSVR)
      - 'stmod' : the incremental update statistics mode for a database (DB_STMOD)
      - 'ststm' : the start time for update statistics (DB_STSTM)
      - 'ststv' : the update statistics daemon interval (DB_STSTV)
      - 'stssp' : the update statistics sample (DB_STSSP)
      - 'usrfo' : user file objects can be inserted in a database (DB_USRFO)


<[COPYTABLE]>
  COPYTABLE(VARCHAR(32)   source_schema_name INPUT,
            VARCHAR(32)   source_table_name INPUT,
            VARCHAR(32)   destination_schema_name INPUT,
            VARCHAR(32)   destination_table_name INPUT,
            VARCHAR(128)  tablespace_lock_mode_option_string INPUT,
            VARCHAR(2048) where_condition_string INPUT,
            INT           fg_rename_index INPUT,
            INT           commit_count INPUT)
    Copy the source table's definition and data into the destination table.
    Comment: 
    1. It must be run when autocommit is on
    2. User can set 0 or 1 for rename index according DCI rule flag
    3. Set commit count to commit when copying every nth data.  User can set 0 to n.
    NOTE: When any error occurs, any operation before the error occurred will be 
          committed. The command that did not execute for copy table will be showed 
          in the _spusr.log.  User can set DB_SPLOG directory in the dmconfig.ini 
          or find it in the directory where user executes the application.

          
<[GETCPUNUMBER]>
  GETCPUNUMBER(INT CPU_NUMBER OUTPUT)
    Get the logical cpu number

<[SCHEDULE_ALTER]>
  SCHEDULE_ALTER(VARCHAR(128) SCHEDULE_NAME INPUT,
                 VARCHAR(128) TASK_NAME INPUT,
                 VARCHAR(512) TIMETABLE INPUT,
                 VARCHAR(32) STARTTIME INPUT,
                 VARCHAR(32) ENDTIME INPUT)
        Alter an existing schedule.
          Comment:
          1. SCHEDULE_NAME: the name of the existing schedule to alter.
          2. TASK_NAME: the name of the task involved into the schedule.
          3. TIMETABLE: the timetable of the task's execution; it is composed of fivefields
                        in sequence: minute, hour, day-of-month, month, day-of-week,
                        and the five fields should be separated by a space.
          4. STARTTIME: the date and time when the schedule starts;its format is
                        yyyy-mm-dd hh:mm:ss.
          5. ENDTIME: the date and time when the schedule expires;its format is
                      yyyy-mm-dd hh:mm:ss.

<[SCHEDULE_CREATE]>
  SCHEDULE_CREATE(VARCHAR(128) SCHEDULE_NAME INPUT,
                  VARCHAR(128) TASK_NAME INPUT,
                  VARCHAR(512) TIMETABLE INPUT,
                  VARCHAR(32) STARTTIME INPUT,
                  VARCHAR(32) ENDTIME INPUT)
    Create a schedule.
      Comment:
      1. SCHEDULE_NAME: the name of the schedule to create.
      2. TASK_NAME: the name of the task involved into the schedule.
      3. TIMETABLE: the timetable of the task's execution; it is composed of fivefields
                    in sequence: minute, hour, day-of-month, month, day-of-week,
                    and the five fields should be separated by a space.
      4. STARTTIME: the date and time when the schedule starts; its format is
                    yyyy-mm-dd hh:mm:ss.
      5. ENDTIME: the date and time when the schedule expires; its format is 
                  yyyy-mm-dd hh:mm:ss.

<[SCHEDULE_DISABLE]>
  SCHEDULE_DISABLE(VARCHAR(128) SCHEDULE_NAME INPUT)
    Disable a schedule.
      Comment:
      1. SCHEDULE_NAME: the name of the schedule to disable.

<[SCHEDULE_DROP]>
  SCHEDULE_DROP(VARCHAR(128) SCHEDULE_NAME INPUT)
    Delete an existing schedule.
      Comment:
      SCHEDULE_NAME: the name of the schedule to delete.

<[SCHEDULE_ENABLE]>
  SCHEDULE_ENABLE(VARCHAR(128) SCHEDULE_NAME INPUT)
    Enable a schedule.
      Comment:
      SCHEDULE_NAME: the name of the schedule to enable.

<[SCHEDULE_RELOAD]>
  SCHEDULE_RELOAD
    Reload all enabled schedules into system.

<[SCHELOG_CLEAN]>
  SCHELOG_CLEAN(INT RESERVE_DAY INPUT)
    Clean excessive logs and only keep logs of recently days.
      comment:
      RESERVE_DAY: the number of days between creation time of schedule logs to
                   delete and that of the most recent schedule logs.

<[SETAFFINITY]>
  SETAFFINITY(INT CONNECTION_ID INPUT,CHAR AFFINITY_MASK(64) INPUT)
    Set specified affinity mask to the connection.
    Comment:
      The valid affinity mask is made up of '1' or '0'.
       '1' : the cpu is valid for connection
       '0' : the cpu is invalid for connection

       
<[SETPRIORITY]>
  SETPRIORITY(INT CONNECTION_ID INPUT,INT PRIORITY_LEVEL INPUT)
    Set specified priority to the connection.
    Comment:
      The valid priority : 
       1 : lowest priority
       2 : lower priority
       3 : normal priority
       4 : higher priority
       5 : highest priority
    NOTE: user can not set a higher priority in Linux


<[CSVEXPORT]>
  CSVEXPORT(VARCHAR(16000) SQL_STRING,
            VARCHAR(2) COLUMN_DELIMITER,
            VARCHAR(3) DISPLAY_NAME,
            VARCHAR(256) OPTION_STRING,
            VARCHAR(256) CSV_FILE)
    Exports table or select satement from a DBMaker database to an excel(.csv) file.
    The result is similiar to dmSQL's export's result.        
    Comment: 
      1. SQL_STRING: Sql statement or table name for exported object
      2. COLUMN_DELIMITER: Column delimiter;The default value (i.e., ';') is used if a NULL or empty string is present.
      3. DISPLAY_NAME: Specify whether to export column or label name in output file;The default value (i.e., 'No') is used if a NULL or empty string is present.
      4. CSV_FILE : Full path of output exported csv file
      5. OPTION_STRING: Description string for option flags. (i.e.,'LOB_FORMAT=EXTERNAL')
                       [LOB_FORMAT= INTERNAL | EXTERNAL]¡­This specifies that when exporting
    Example:
      call CSVEXPORT('select * from t1',';','yes','','/csvexport/csvexport.csv');
      or
      call CSVEXPORT('t1',';','yes','','/csvexport/csvexport.csv');


<[DEFTABLE]>
  DEFTABLE(VARCHAR(128)   TABLE_SCHEMA_NAME INPUT,
           VARCHAR(128)   TABLE_NAME INPUT)
           RETURNS	
           VARCHAR(512)   TABLE_DEFINITION
    Returns the table definition as result rows.  
    The result is similar to dmSQL's def table's result.
    Comment:
      If table does not exist, 0 rows will be returned


<[EXTENDTS]>
  EXTENDTS(VARCHAR(128)   TABLESPACE_NAME INPUT,
           VARCHAR(100)   THRESHOLD_PAGE  INPUT,
           VARCHAR(100)   THRESHOLD_FRAME INPUT,
           VARCHAR(256)   FILE_PREFIX     INPUT,
           VARCHAR(256)   FILE_PATH       INPUT)
    Check and extend tablespace by adding page or file according to 
    the threshold_page/frame.
    The syntax for threshold_page/frame is "nFree nAdd nMax".
    EXTENDTS() will check and extend when the tablespace's 
    free page/frame < nFree and add nAdd page/frame.
    If user has specified the FILE_PREFIX, it will
    check whether the data file's npage + nAdd > nMax and
    add a file as FILE_PATH/FILE_PREFIX_%d.DB/BB.
    Example:
    CALL EXTENDTS('TS1', '10280 20560 10G', '10280 20560 10G','TS1','/data/ts1'); 
    or 
    CALL EXTENDTS('TS1', '100M 200M 10G', '100M 200M 10G','TS1','/data/ts1'); 
    NOTE: User need to create task scheduler to monitor and trigger the stored 
          procedure when the file size approaches the limit.
    Example: To check the tablespace TS1 every 5 mins
    call task_create('task1','STORE_PROCEDURE',
             'call extendts(''TS1'', ''200M 200M 8G'', ''200M 200M 8G'', ''TS1'', ''/data/ts1'')');
    call schedule_create('sche1','task1','*/5 * * * *','2025-01-01 12:12:00','2030-12-31 12:12:00');
    call schedule_enable('sche1');

   
<[SHOWINDEX]>
  SHOWINDEX(VARCHAR(128)   TABLE_SCHEMA_NAME INPUT,
           VARCHAR(128)   TABLE_NAME INPUT)
           RETURNS
           VARCHAR(128)   TABLE_OWNER,
           VARCHAR(128)   TABLE_NAME,
           INT            NON_UNIQUE,
           VARCHAR(128)   INDEX_NAME,
           INT            TYPE,
           INT            ORDINAL_POSITION,
           VARCHAR(128)   COLUMN_NAME,
           CHAR(1)        ASC_OR_DESC,
           INT            CARDINALITY,
           INT            PAGES,
           VARCHAR(256)   FILTER_CONDITION,
           INT            FOREIGN_KEY;
    Returns the index definition as result rows.
    Comment: 
      If table or index does not exist, 0 rows will be returned


<[SHOWDMLOG]>
  SHOWDMLOG(VARCHAR(256)       DMLOG_NAME INPUT,
            VARCHAR(256)       SEARCH_STRING INPUT,
            INT                START_LINENO INPUT,
            INT                SHOW_NLINE INPUT)
            RETURNS
            INT                LINE_NO,
            VARCHAR(2048)      CONTENT
    Returns the server site's dmlog file's content. 
    If file does not exist, 0 rows will be returned.
    Comment: 
      1. DMLOG_NAME: supports DMERROR.LOG, DMEVENT.LOG, DMBACKUP.LOG, RP.LOG
                    ATRP.LOG, ATRERROR.LOG, dmBackup.his, dmconfig.ini or any 
                    file under DBDIR and its file extension as log, old, txt or ini.
      2. SEARCH_STRING: search the file's content and returns the line that contains
                       the search_string.  If search_string is null or '' means do not
                       need to search.
      3. START_LINENO: start lineno offset for search or show the file content.  
                       If start_lineno is null or 0, then it will start from first line.
      4. SHOW_NLINE: show how many lines after search_string.
    Example:  
      1. Show DMERROR.LOG's content
         dmSQL> call SHOWDMLOG('DMERROR.LOG', null, null, null);
      2. Show DMERROR.LOG with search_string '2020/01/02'
         dmSQL> call SHOWDMLOG('DMERROR.LOG', '2020/01/02', null, null);
      3. Show DMERROR.LOG with search_string '2020/01/02' with more lines if windows server.
         dmSQL> call SHOWDMLOG('DMERROR.LOG', '2020/01/02', 0, 5);
      4. Show DMERROR.LOG with search_string '2020/01/02' and start from line 10
         dmSQL> call SHOWDMLOG('DMERROR.LOG', '2020/01/02', 10, 5);



<[TASK_ALTER]>
  TASK_ALTER(VARCHAR(128) TASK_NAME INPUT,
             VARCHAR(16) TASK_TYPE INPUT,
             VARCHAR(2048) ACTIONS INPUT)
    Alter an existing task.
      Comment:
      1. TASK_NAME: the name of the existing task to alter.
      2. TASK_TYPE: the type of the task. There are three options: SQL_
                    STATEMENT (abbr. SQL), STORE_PROCEDURE (abbr.SP),
                    and EXECUTABLE (abbr. EXEC).
      3. ACTIONS: the actions that the existing task performs regularly.

<[TASK_CREATE]>
  TASK_CREATE(VARCHAR(128) TASK_NAME INPUT,
              VARCHAR(16) TASK_TYPE INPUT,
              VARCHAR(2048) ACTIONS INPUT)
    Create a task.
      Comment:
      1. TASK_NAME: the name of the task to create.
      2. TASK_TYPE: the type of the task to create. There are three options:
                    SQL_STATEMENT (abbr. SQL), STORE_PROCEDURE
                   (abbr. SP), and EXECUTABLE (abbr. EXEC).
      3. ACTIONS: the actions that the task will perform regularly.

<[TASK_DROP]>
  TASK_DROP(VARCHAR(128) TASK_NAME INPUT)
    Delete an existing task.
      Comment:
      TASK_NAME: the name of the task to delete.
______________________________________________________________________________
* editing commands(PC-versioned vs. UNIX-line/page-versioned) *


<[^V]>(i)
   UNIX-page-versioned dmsql:   
     - Invoke designated editor to edit command buffer.(EDIT state) 


<[^C]>
   UNIX-line-versioned dmsql: 
     - Quit dmsql abnormally.
   PC-versioned dmsql:
     - Standard copy action.  


<[^\]>
   UNIX-line-versioned dmsql:
     - Quit (large) tuple-fetching of select command. 
     - Quit (nested) script files execution 
   PC-versioned dmsql:
     - Quit (large) tuple-fetching of select command.
     - Quit (nested) script files execution 


<[^Y]>
   PC-versioned dmsql:
     - Abort contain of (current) command buffer.


<[^T]>
   PC-versioned dmsql:
     - Lock display and only print the last screen of
       command result.(This will reduce response time)


<[^Z]>
   UNIX-line-versioned dmsql:
     - Abort contain of (current) command buffer. 
   PC-versioned dmsql:
     - Standard undo action. 


<[^I]>(nitialize)
   UNIX-page-versioned dmsql:
     - Clear command buffer.(EDIT state) 


<[^H]>(ack)
   UNIX-page-versioned dmsql:
     - Kill the char left to cursor.(EDIT state) 


<[BACKSPACE]>
   UNIX-page-versioned dmsql:
     - Kill the char left to cursor.(EDIT state) 


<[^X]>(erase)
   UNIX-page-versioned dmsql:
     - Kill the char on cursor.(EDIT state)


<[DELETE]>
   UNIX-page-versioned dmsql:
     - Kill the char on cursor.(EDIT state)


<[^A]>(head)
   UNIX-page-versioned dmsql:
     - Move cursor to the head of line.(EDIT state) 


<[^E]>(nd)
   UNIX-page-versioned dmsql:
     - Move cursor to the end of line.(EDIT state)


<[^L]>(eft)
   UNIX-page-versioned dmsql:
     - Move cursor a char left.(EDIT state) 


<[^R]>(ight)
   UNIX-page-versioned dmsql:
     - Move cursor a char right.(EDIT state)


<[^U]>(p)
   UNIX-page-versioned dmsql:
     - Read previous command into command buffer.(EDIT state)


<[^D]>(own)
   UNIX-page-versioned dmsql:
     - Read next command into command buffer.(EDIT state) 


<[:ed]>
   UNIX-page-versioned dmsql:
     - Read history command into command buffer.(EDIT state) 
       Grammar:
         |>>-- :ed --+----------------------------+--<<|
                     +-- ! -----------------------+
                     +-- history_command_number --+
       Example:
         dmSQL>:ed ;     /* edit the last executed command */ 
         dmSQL>:ed -4;
   UNIX-page-versioned dmsql:
     - (same as page version)


<[ENTER]>
   UNIX-page-versioned dmsql:
     - In EDIT state, if a command is found in command buffer, execute it.
       Otherwise, move cursor to next line to read input.
       In VIEW state, Line down.
   UNIX-line-versioned dmsql:
   PC-line-versioned dmsql:
     - If a command is found in command buffer, execute it. Otherwise, move
       cursor to next line to read input.


<[LEFT ARROW]>
   UNIX-page-versioned dmsql: 
     - In EDIT state, move cursor a char left.
       In VIEW state, Page up. 


<[RIGHT ARROW]>
   UNIX-page-versioned dmsql:
     - In EDIT state, move cursor a char right.
       In VIEW state, Page down.


<[UP ARROW]>
   UNIX-page-versioned dmsql:
     - In EDIT state, read previous command into command buffer.
       In VIEW state, Line up.


<[DOWN ARROW]>
   UNIX-page-versioned dmsql:
     - In EDIT state, read next command into command buffer.
       In VIEW state, Line down.


<[^B]>(ackward)   
   UNIX-page-versioned dmsql:
     - In EDIT state, enter VIEW state and page up. 
       In VIEW state, Page up.


<[^P]>(revious)
   UNIX-page-versioned dmsql:
     - In EDIT state, enter VIEW state and line up. 
       In VIEW state, Line up.


<[^F]>(orward)   
   UNIX-page-versioned dmsql:
     - In VIEW state, Page down.


<[^N]>(ext)   
   UNIX-page-versioned dmsql:
     - VIEW state, Line down.


<[version]> 
     - PC-versioned dmsql(16-bit/32-bit). 
     - UNIX-page/line-versioned dmsql. 


<[EDIT state]>
   UNIX-page-versioned dmsql:
     - Initial state for dmsql in which commands are executed.
       Use Ctrl-B or Ctrl-P to enter VIEW state for browsing
       scrolled-up data. 


<[VIEW state]>
   UNIX-page-versioned dmsql:
     - The state where scrolled-up data are browsed.
       Use VIEW-state-special keys to browse the scrolled up data.
       Those Keys other than VIEW-state-special keys to back to EDIT state.









