HP 3000 Manuals HP 3000 Manuals
Chapter 1 SQL Syntax Summary
SQL Statements
SQL Statement Summary
--------------------------------------------------------------------------------------------
| | |
| Statement | Function |
| | |
--------------------------------------------------------------------------------------------
| | |
| ADD DBEFILE | Associates a DBEFile with a DBEFileSet. |
| | |
| ADD TO GROUP | Adds one or more users or groups, or a combination of users and |
| | groups, to an authorization group. |
| | |
| ALTER DBEFILE | Changes the TYPE attribute of a DBEFile. |
| | |
| ALTER TABLE | Adds one or more new columns that allow null values to the |
| | right-hand side of an existing table.[REV BEG] |
| | |
| Assignment (=) | Is used in a procedure to assign a value to a local variable or |
| | procedure parameter. |
| | |
| BEGIN | Defines a group of statements within a procedure.[REV END] |
| | |
| BEGIN ARCHIVE | Is used in conjunction with the COMMIT ARCHIVE statement to |
| | reclaim log file space after a DBEnvironment operating in |
| | archive mode is backed up. |
| | |
| BEGIN DECLARE SECTION | Indicates the beginning of a host variable declaration section |
| | in an application program. |
| | |
| BEGIN WORK | Begins a transaction and sets the isolation level. |
| | |
| CHECKPOINT | Causes an ALLBASE/SQL checkpoint record to be written to the log |
| | file and flushes the log and data buffers to disk. |
| | |
--------------------------------------------------------------------------------------------
Table 1-0. SQL Statement Summary (cont.)
--------------------------------------------------------------------------------------------
| | |
| Statement | Function |
| | |
--------------------------------------------------------------------------------------------
| | |
| CLOSE | Is used in an application program to close a cursor. |
| | |
| COMMIT ARCHIVE | Is used in conjunction with the BEGIN ARCHIVE statement to |
| | create an archive record in the rollforward log to mark the |
| | beginning of the new log. |
| | |
| COMMIT WORK | Ends the current transaction. All changes made during the |
| | transaction are committed (made permanent). |
| | |
| CONNECT | Starts a DBE session for a specific DBEnvironment. |
| | |
| CREATE DBEFILE | Defines and creates a DBEFile. |
| | |
| CREATE DBEFILESET | Defines and creates a DBEFileSet. |
| | |
| CREATE GROUP | Defines a new authorization group. |
| | |
| CREATE INDEX | Creates an index on one or more columns of a table and assigns a |
| | name to the new index.[REV BEG] |
| | |
| CREATE PROCEDURE | Defines a procedure for storage in the DBEnvironment. |
| | |
| CREATE RULE | Defines a rule and associates it with specific kinds of data |
| | manipulation on a particular table. |
| | |
| CREATE SCHEMA | Creates a schema and associates an authorization name with |
| | it.[REV END] |
| | |
| CREATE TABLE | Defines a table. It also defines the locking strategy that |
| | ALLBASE/SQL automatically uses when the table is accessed and in |
| | some cases automatically issues a GRANT statement. |
| | |
| CREATE TEMPSPACE | Defines an area in which temporary files can be created during |
| | sort operations. |
| | |
| CREATE VIEW | Creates a view of a table, another view, or a combination of |
| | tables and views. |
| | |
| DECLARE CURSOR | Associates a cursor with a specified SELECT statement. |
| | |
--------------------------------------------------------------------------------------------
Table 1-0. SQL Statement Summary (cont.)
-----------------------------------------------------------------------------------------------------
| | |
| Statement | Function |
| | |
-----------------------------------------------------------------------------------------------------
| [REV BEG] | |
| | |
| DECLARE Variable | Defines a local variable within a procedure.[REV END] |
| | |
| DELETE | Deletes a row or rows from a table. |
| | |
| DELETE WHERE CURRENT | Deletes the current row. The current row is the row in the |
| | active set pointed to by a cursor after the FETCH statement is |
| | issued. |
| | |
| DESCRIBE | Is used in an application program to get information about the |
| | results of a statement preprocessed with the PREPARE |
| | statement.[REV BEG] |
| | |
| DISABLE RULES | Turns rule checking off for the current DBEnvironment session. |
| | |
| DISCONNECT | Terminates a connection with a DBEnvironment or terminates all |
| | DBEnvironment connections established within an application or |
| | an ISQL session.[REV END] |
| | |
| DROP DBEFILE | Removes the definition of a DBEFile from the system catalog. |
| | |
| DROP DBEFILESET | Removes the definition of a DBEFileSet from the system catalog. |
| | |
| DROP GROUP | Removes the definition of an authorization group from the system |
| | catalog. |
| | |
| DROP INDEX | Deletes the specified index. |
| | |
| DROP MODULE | Deletes all sections associated with the specified module from |
| | the system catalog.[REV BEG] |
| | |
| DROP PROCEDURE | Deletes the specified procedure. |
| | |
| DROP RULE | Deletes the specified rule.[REV END] |
| | |
| DROP TABLE | Deletes the specified table, all indexes and views defined on |
| | the table, and all authorizations granted on the table. |
| | |
| DROP TEMPSPACE | Deletes a previously defined TempSpace. |
| | |
-----------------------------------------------------------------------------------------------------
Table 1-0. SQL Statement Summary (cont.)
--------------------------------------------------------------------------------------------
| | |
| Statement | Function |
| | |
--------------------------------------------------------------------------------------------
| | |
| DROP VIEW | Deletes the definition of the specified view from the system |
| | catalog, along with all authorizations granted on the view, and |
| | any other views that reference the dropped view.[REV BEG] |
| | |
| ENABLE RULES | Turns rule checking on for the current DBEnvironment |
| | session.[REV END] |
| | |
| END DECLARE SECTION | Indicates the end of the host variable declaration section in an |
| | application program. |
| | |
| EXECUTE | Causes ALLBASE/SQL to execute a statement that has been |
| | dynamically prepared for execution by the PREPARE statement. |
| | |
| EXECUTE IMMEDIATE | Dynamically prepares and executes an SQL statement.[REV BEG] |
| | |
| EXECUTE PROCEDURE | Invokes a procedure.[REV END] |
| | |
| FETCH | Advances the position of an OPENed cursor to the next row of the |
| | active set and copies selected columns into the specified host |
| | variables.[REV BEG] |
| | |
| GENPLAN | Places the access plan generated by the optimizer for a SELECT, |
| | UPDATE, or DELETE statement into the pseudotable SYSTEM.PLAN. |
| | |
| GOTO | Permits a jump to a labeled statement within a procedure.[REV |
| | END] |
| | |
| GRANT | Gives specified authority to one or more users or authorization |
| | groups.[REV BEG] |
| | |
| IF | Allows conditional execution of one or more statements within a |
| | procedure.[REV END] |
| | |
| INCLUDE | Includes declarations for structures used to pass information |
| | between ALLBASE/SQL and a program. |
| | |
| INSERT | Adds rows (tuples) to a table.[REV BEG] |
| | |
| Labeled Statement | Identifies an SQL statement that can be referred to within the |
| | procedure.[REV END] |
| | |
--------------------------------------------------------------------------------------------
Table 1-0. SQL Statement Summary (cont.)
--------------------------------------------------------------------------------------------
| | |
| Statement | Function |
| | |
--------------------------------------------------------------------------------------------
| | |
| LOCK TABLE | Provides a means of explicitly acquiring a lock on a table, to |
| | override the automatic locking provided by ALLBASE/SQL in the |
| | locking modes implicit in the CREATE TABLE statement. |
| | |
| OPEN | Is used in an application program to open a cursor. |
| | |
| PREPARE | Dynamically preprocesses an SQL statement for later |
| | execution.[REV BEG] |
| | |
| PRINT | Is used inside a procedure to store the content of user-defined |
| | strings, local variables, parameters, or built-in variables in |
| | the message buffer for display by ISQL or an application |
| | program. |
| | |
| RAISE ERROR | Causes an error to occur and causes the given error number and |
| | text to be put into the ALLBASE/SQL message buffer.[REV END] |
| | |
| REFETCH | Refetches the current row again, and acquires stronger locks in |
| | a transaction using RC or RU isolation levels. |
| | |
| RELEASE | Terminates a DBEnvironment session. |
| | |
| REMOVE DBEFILE | Disassociates a DBEFile from a DBEFileSet. |
| | |
| REMOVE FROM GROUP | Removes one or more users or authorization groups from |
| | membership in a specified authorization group. |
| | |
| RESET | Resets ALLBASE/SQL accounting and statistical data for a |
| | DBEnvironment.[REV BEG] |
| | |
| RETURN | Permits you to exit from a procedure with an optional return |
| | code.[REV END] |
| | |
| REVOKE | Takes away authority that was previously granted by means of the |
| | GRANT statement. |
| | |
| ROLLBACK WORK | Undoes changes you have made to the DBEnvironment during the |
| | current transaction, releases locks held by the transaction, and |
| | closes cursors opened during the transaction. |
| | |
| SAVEPOINT | Defines a savepoint within a transaction. |
| | |
| SELECT | Retrieves data from one or more tables or views. |
| | |
--------------------------------------------------------------------------------------------
Table 1-0. SQL Statement Summary (cont.)
-----------------------------------------------------------------------------------------------------
| | |
| Statement | Function |
| | |
-----------------------------------------------------------------------------------------------------
| [REV BEG] | |
| | |
| SET CONNECTION | Sets the current connection within the list of connected |
| | DBEnvironments. |
| | |
| SET CONSTRAINTS | Sets the UNIQUE, REFERENTIAL or CHECK constraint error checking |
| | mode. |
| | |
| SET DML ATOMICITY | Sets the general error checking level in data manipulation |
| | statements. |
| | |
| SET MULTITRANSACTION | Provides the capability of switching between single-transaction |
| | mode and multi-transaction mode. |
| | |
| SET PRINTRULES | Specifies whether rule names and statement types are to be |
| | issued as messages when the rules are fired during a |
| | DBEnvironment session. |
| | |
| SET USER TIMEOUT | Specifies the amount of time the user will wait if the requested |
| | database resource is unavailable.[REV END] |
| | |
| SQLEXPLAIN | Places a message describing the meaning of a return code for |
| | ALLBASE/SQL into a host variable. |
| | |
| START DBE | Starts a DBEnvironment in single user or multiuser mode. |
| | |
| START DBE NEW | Configures a new DBEnvironment. |
| | |
| START DBE NEWLOG | Creates one or two new log files for a DBEnvironment. |
| | |
| STOP DBE | Terminates a DBE session and any transactions in progress. |
| | |
| TERMINATE USER | Terminates one or more DBE sessions associated with a DBEUserID |
| | or session ID. |
| | |
| TRANSFER OWNERSHIP | Makes a user or authorization group or class name the owner of a |
| | table, view, or authorization group. |
| | |
| UPDATE | Updates the values in one or more columns in all rows of a table |
| | or in rows that satisfy a search condition. |
| | |
| UPDATE STATISTICS | Updates the system catalog to reflect a table's current |
| | characteristics, such as the number of rows and average row |
| | size. |
| | |
-----------------------------------------------------------------------------------------------------
Table 1-0. SQL Statement Summary (cont.)
--------------------------------------------------------------------------------------------
| | |
| Statement | Function |
| | |
--------------------------------------------------------------------------------------------
| | |
| UPDATE WHERE CURRENT | Updates the values of one or more columns in the current row |
| | associated with a cursor.[REV BEG] |
| | |
| VALIDATE | Validates modules and procedures that have already been |
| | created.[REV END] |
| | |
| WHENEVER | Is used in an application program to specify an action to be |
| | taken depending on the outcome of subsequent SQL statements.[REV |
| | BEG] |
| | |
| WHILE | Allows looping within a procedure.[REV END] |
| | |
--------------------------------------------------------------------------------------------
ADD DBEFILE
ADD DBEFILE DBEFileName TO DBEFILESET DBEFileSetName
ADD TO GROUP
{DBEUserID}
ADD {GroupName} [,...] TO GROUP TargetGroupName
{ClassName}
ALTER DBEFILE
{TABLE}
ALTER DBEFILE DBEFileName SET TYPE = {INDEX}
{MIXED}
ALTER TABLE
{AddColumnSpecification }
ALTER TABLE [Owner.]TableName {AddConstraintSpecification }
{DropConstraintSpecification}
AddColumnSpecification.
ADD{(ColumnDefinition [,...] )}
{Column Definition }
AddConstraintSpecification.
{ {UniqueConstraint [CONSTRAINT ConstraintID] } }
{({ReferentialConstraint [CONSTRAINT ConstraintID]} [,...] )}
ADD CONSTRAINT{ {CheckConstraint [CONSTRAINT ConstraintID] } }
{{UniqueConstraint [CONSTRAINT ConstraintID] } }
{{ReferentialConstraint [CONSTRAINT ConstraintID]} }
{{CheckConstraint [CONSTRAINT ConstraintID] } }
DropConstraintSpecification.
DROP CONSTRAINT{(ConstraintID [,...] )}
{ConstraintID }
Assignment (=)
{:LocalVariable } = Expression;
{:ProcedureParameter}
BEGIN
BEGIN [Statement;] [...] END;
BEGIN ARCHIVE
BEGIN ARCHIVE
BEGIN DECLARE SECTION
BEGIN DECLARE SECTION
BEGIN WORK
[RR]
BEGIN WORK [Priority] [CS]
[RC]
[RU]
CHECKPOINT
CHECKPOINT
CLOSE
CLOSE CursorName
COMMIT ARCHIVE
COMMIT ARCHIVE
COMMIT WORK
COMMIT WORK [RELEASE]
CONNECT
CONNECT TO {'DBEnvironmentName'} [AS {'ConnectionName'}]
{:HostVariable1 } [ {:HostVariable2 }]
[USER {'UserID' } [USING :HostVariable4]]
[ {:HostVariable3} ]
CREATE DBEFILE
CREATE DBEFILE DBEFilename WITH PAGES = DBEFileSize, NAME = 'SystemFileName'
[, INCREMENT = DBEFileIncrSize[, MAXPAGES = DBEFileMaxSize]]
[ {TABLE}]
[,TYPE = {INDEX}]
[ {MIXED}]
CREATE DBEFILESET
CREATE DBEFILESET DBEFileSetName
CREATE GROUP
CREATE GROUP [Owner.]GroupName
CREATE INDEX
CREATE [UNIQUE] [CLUSTERING] INDEX [Owner.]IndexName ON
[Owner.]TableName ( {ColumnName [ASC ]} [,...] )
{ [DESC]}
CREATE PROCEDURE
CREATE PROCEDURE [Owner.]ProcedureName [LANG = ProcLangName]
[(ParameterDeclaration [, ParameterDeclaration] [...])] AS BEGIN
ProcedureStatement; [...] END;
ParameterDeclaration.
ParameterName ParameterType [LANG = ParameterLanguage]
[DEFAULT DefaultValue] [NOT NULL] [OUTPUT]
CREATE RULE
{ON }
CREATE RULE [Owner.]RuleName AFTER StatementType [,...] {OF }
{FROM}
{INTO}
[Owner].TableName [REFERENCING {OLD AS OldCorrelationName}[...]]
[ {NEW AS NewCorrelationName} ]
[WHERE FiringCondition] EXECUTE PROCEDURE [OwnerName.]ProcedureName
[(ParameterValue [,...])]
CREATE SCHEMA
[TableDefinition ]
[ViewDefinition ]
[IndexDefinition ]
CREATE SCHEMA AUTHORIZATION AuthorizationName [ProcedureDefinition] [...]
[RuleDefinition ]
[CreateGroup ]
[AddToGroup ]
[GrantStatement ]
CREATE TABLE
[PUBLIC ]
CREATE [PUBLICREAD] TABLE [Owner.]TableName [LANG = TableLanguageName]
[PRIVATE ]
{ColumnDefinition }
( {[{UniqueConstraint } ]} [,...])
{[{ReferentialConstraint} [CONSTRAINT ConstraintID]]}
{[{CheckConstraint } ]}
[UNIQUE HASH ON (HashColumnName [,...]) PAGES = PrimaryPages]
[HASH ON CONSTRAINT [ConstraintID] PAGES = PrimaryPages ]
[CLUSTERING ON CONSTRAINT [ConstraintID] ]
[IN DBEFileSetName]
ColumnName {ColumnDataType }
{LongColumnType [IN DBEFileSet]}
[ {Constant }]
[LANG = ColumnLanguageName] [DEFAULT {NULL }]
[ {CurrentFunction}]
[NOT NULL [{UNIQUE } [CONSTRAINT ConstraintID]] ]
[ [{PRIMARY KEY} ] ]
[ ][...]
[REFERENCES RefTableName [(RefColumnName)] [CONSTRAINT ConstraintID][...]]
[ ]
[CHECK (SearchCondition) [CONSTRAINT ConstraintID] ]
CREATE TEMPSPACE
CREATE TEMPSPACE TempSpaceName WITH [MAXFILEPAGES = MaxTempFileSize,]
LOCATION ='PhysicalLocation'
CREATE VIEW
CREATE VIEW [Owner.]ViewName [( ColumnName [,...])] AS QueryExpression
[WITH CHECK OPTION [CONSTRAINT ConstraintID]]
DECLARE CURSOR
DECLARE CursorName [IN DBEFileSetName] CURSOR FOR {QueryExpression}
{StatementName }
[FOR UPDATE OF {ColumnName} [,...]]
DECLARE Variable
DECLARE {LocalVariable} [,...] VariableType [LANG = VariableLangName]
[ {Constant }]
[DEFAULT {NULL }] [NOT NULL]
[ {CurrentFunction}]
DELETE
DELETE FROM {[Owner.]TableName} [WHERE SearchCondition]
{[Owner.]ViewName }
DELETE WHERE CURRENT
DELETE FROM {[Owner.]TableName} WHERE CURRENT OF CursorName
{[Owner.]ViewName }
DESCRIBE
DESCRIBE [OUTPUT] StatementName {INTO } [[SQL] DESCRIPTOR] {SQLDA }
[INPUT ] {USING} {AreaName}
DISABLE RULES
DISABLE RULES
DISCONNECT
{'ConnectionName' }
{'DBEnvironmentName'}
DISCONNECT {:HostVariable }
{ALL }
{CURRENT }
DROP DBEFILE
DROP DBEFILE DBEFileName
DROP DBEFILESET
DROP DBEFILESET DBEFileSetName
DROP GROUP
DROP GROUP GroupName
DROP INDEX
DROP INDEX [Owner.]IndexName [FROM [Owner.]TableName]
DROP MODULE
DROP MODULE [Owner.]ModuleName [PRESERVE]
DROP PROCEDURE
DROP PROCEDURE [Owner.]ProcedureName [PRESERVE]
DROP RULE
DROP RULE [Owner.]RuleName [FROM TABLE [Owner.]TableName]
DROP TABLE
DROP TABLE [Owner.]TableName
DROP TEMPSPACE
DROP TEMPSPACE TempSpaceName
DROP VIEW
DROP VIEW [Owner.]ViewName
ENABLE RULES
ENABLE RULES
END DECLARE SECTION
END DECLARE SECTION
EXECUTE
EXECUTE {StatementName }
{[Owner.]ModuleName [(SectionNumber)]}
[ {[SQL] DESCRIPTOR{SQLDA } }]
[ { {AreaName} }]
[USING { }]
[ {:HostVariableName[[INDICATOR] :IndicatorVariable] [,...]}]
[ {:Buffer [,:StartIndex [, :NumberOfRows]] }]
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE {'String' }
{:HostVariable}
EXECUTE PROCEDURE
EXECUTE PROCEDURE [:ReturnStatusVariable =] [Owner.]ProcedureName
[( [ParameterValue [OUTPUT]] [, [ParameterValue [OUTPUT]]][...])]
FETCH
{INTO HostVariableSpecification }
{ {[SQL]DESCRIPTOR {SQLDA }}}
[BULK] FETCH CursorName {USING { {AreaName}}}
{ { }}
{ {HostVariableSpecification }}
BULK HostVariableSpecification.
:Buffer [,:StartIndex [,:NumberOfRows]]
Non-BULK HostVariableSpecification.
{:HostVariable [[INDICATOR] :Indicator]} [,...]
GENPLAN
GENPLAN [WITH (VariableName DataType [,..])] FOR SQLStatement
GOTO
{GOTO } {Label }
{GO TO} {Integer}
GRANT
Grant Authority for a Table or View.
{ALL [PRIVILEGES] }
{{SELECT } }
{{INSERT } }
GRANT {{DELETE } } ON {[Owner.]TableName}
{{ALTER } |,...|} {[Owner.]ViewName }
{{INDEX } }
{{UPDATE [(ColumnName [,...])] } }
{{REFERENCES [(ColumnName [,...])]} }
{DBEUserID}
TO {GroupName} [,...] [WITH GRANT OPTION] [BY {DBEUserID}]
{ClassName} [ {ClassName}]
{PUBLIC }
Grant RUN or EXECUTE Authority.
{{DBEUserID} }
GRANT {RUN ON [Owner.]ModuleName } TO {{GroupName} [,...]}
{EXECUTE ON PROCEDURE [Owner.]ProcedureName} {{ClassName} }
{PUBLIC }
Grant CONNECT, DBA, or RESOURCE Authority.
{CONNECT } {DBEUserID}
GRANT{DBA }TO {GroupName}[,...]
{RESOURCE} {ClassName}
IF
IF Condition THEN [Statement;[...]]
[ELSEIF Condition THEN [Statement; [...]]] [ELSE [Statement; [...]]]
ENDIF;
INCLUDE
INCLUDE {SQLCA}
{SQLDA}
INSERT - I
[BULK] INSERT INTO {[Owner.]TableName} [({ColumnName} [,...])] VALUES
{[Owner.]ViewName }
{SingleRowValues}
({BulkValues })
{? }
Single Row Values.
{NULL }
{USER }
{:HostVariable [[INDICATOR] :IndicatorVariable]}
{? }
{:LocalVariable }
{:ProcedureParameter }
{::Built-inVariable }
{ConversionFunction }
{CurrentFunction }
{ {Integer} }
{[+] {Float } }
{[-] {Decimal} }
{ }
{'CharacterString' }
{0xHexadecimalString }
{'LongColumnIOString' }
{[,...] }
LongColumnIOString.
[{> } {FileName[.Group[.Account]]}]
[{>>} {CharString$ }]
< {FileName[.Group[.Account]]} [{>!} {CharString$CharString }]
{%HeapAddress:LengthofHeap } [ ]
[>%$ ]
Bulk Values.
:Buffer [,:StartIndex [, :NumberOfRows]]
Dynamic Parameter Substitution.
(? [,...] )
INSERT - II
INSERT INTO {[Owner.]TableName} [(ColumnName [,...])] QueryExpression
{[Owner.]ViewName }
Labeled Statement
Label: Statement
LOCK TABLE
LOCK TABLE [Owner.]TableName IN {SHARE [UPDATE]} MODE
{EXCLUSIVE }
OPEN
OPEN CursorName [KEEP CURSOR [WITH LOCKS ]] USING
[ [WITH NOLOCKS]]
[[SQL] DESCRIPTOR{SQLDA } ]
[ {AreaName} ]
[ ]
[HostVariableName[[INDICATOR] :IndicatorVariable] [,...]]
PREPARE
PREPARE {StatementName } [IN DBEFileSetName] FROM
{[Owner.]ModuleName [(SectionNumber)]}
{'String' }
{:HostVariable}
PRINT
{'Constant' }
PRINT {:LocalVariable };
{:Parameter }
{::Built-inVariable}
RAISE ERROR
RAISE ERROR [ErrorNumber] [MESSAGE ErrorText]
REFETCH
REFETCH CursorName INTO {:HostVariable [[INDICATOR] :Indicator]} [,...]
RELEASE
RELEASE
REMOVE DBEFILE
REMOVE DBEFILE DBEFileName FROM DBEFILESET DBEFileSetName
REMOVE FROM GROUP
{DBEUserID}
REMOVE {GroupName} [,...] FROM GROUP [Owner.]TargetGroupName
{ClassName}
RESET
{SYSTEM.ACCOUNT [FOR USER {* }]}
RESET { [ {DBEUserID}]}
{ }
{SYSTEM.COUNTER }
RETURN
RETURN [ReturnStatus];
REVOKE
Revoke Authority on a Table or View.
{ALL [PRIVILEGES] }
{[SELECT ] }
{[INSERT ] }
REVOKE {[DELETE ] } ON
{[ALTER ] |,...|}
{[INDEX ] }
{[UPDATE [({ColumnName} [,...])] ] }
{[REFERENCES [({ColumnName} [,...])]] }
{DBEUserID}
{[Owner.]TableName} FROM {GroupName} [,...] [CASCADE]
{[Owner.]ViewName } {ClassName}
{PUBLIC }
Revoke RUN or EXECUTE Authority.
REVOKE [RUN ON [Owner.]ModuleName ] FROM
[EXECUTE ON PROCEDURE [Owner.]ProcedureName]
{{DBEUserID} }
{{GroupName} [,...]}
{{ClassName} }
{PUBLIC }
Revoke CONNECT, DBA, or RESOURCE Authority.
{CONNECT } {DBEUserID}
REVOKE {DBA } FROM {GroupName} [,...]
{RESOURCE} {ClassName}
ROLLBACK WORK
[ {SavePointNumber }]
[TO {:HostVariable }]
ROLLBACK WORK [ {:LocalVariable }]
[ {:ProcedureParameter}]
[ ]
[RELEASE ]
SAVEPOINT
[:HostVariable ]
SAVEPOINT [:LocalVariable ]
[:ProcedureParameter]
SELECT
Select Statement Level.
[BULK] QueryExpression [ORDER BY {ColumnID [ASC ]} [,...]]
[ { [DESC]} ]
Subquery Level.
(QueryExpression)
Query Expression Level.
{QueryBlock } [UNION [ALL] {QueryBlock }] [...]
{(QueryExpression)} [ {(QueryExpression)}]
Query Block Level.
SELECT [ALL ] SelectList [INTO HostVariableSpecification] FROM
[DISTINCT]
FromSpec [,...] [WHERE SearchCondition1] [GROUP BY GroupColumnList]
[HAVING SearchCondition2]
SelectList.
{* }
{[Owner.]Table.* }
{CorrelationName.* } [,...]
{Expression }
{[[Owner.]Table.]ColumnName}
{CorrelationName.ColumnName}
HostVariableSpecification--Without BULK Option.
{:HostVariable [[INDICATOR] :Indicator]} [,...]
FromSpec.
{TableSpec }
{(FromSpec) }
{ [INNER ] }
{FromSpec NATURAL [LEFT [OUTER] ] JOIN {TableSpec } }
{ [RIGHT [OUTER]] {(FromSpec)} }
{ }
{ [INNER ] }
{FromSpec [LEFT [OUTER] ] JOIN {TableSpec } {ON SearchCondition3}}
{ [RIGHT [OUTER]] {(FromSpec)} {USING (ColumnList) }}
TableSpec.
[Owner.]TableName [CorrelationName]
SET CONNECTION
SET CONNECTION {'ConnectionName'}
{:HostVariable }
SET CONSTRAINTS
SET ConstraintType [,...] CONSTRAINTS {DEFERRED }
{IMMEDIATE}
SET DML ATOMICITY
SET DML ATOMICITY AT {ROW } LEVEL
{STATEMENT}
SET MULTITRANSACTION
SET MULTITRANSACTION {ON }
{OFF}
SET PRINTRULES
SET PRINTRULES [ON ]
[OFF]
SET USER TIMEOUT
{{TimeoutValue } [SECONDS]}
{{:HostVariable} [MINUTES]}
SET USER TIMEOUT [TO] { }
{DEFAULT }
{MAXIMUM }
SQLEXPLAIN
SQLEXPLAIN :HostVariable
START DBE
START DBE 'DBEnvironmentName' [AS ConnectionName'] [MULTI]
[BUFFER = (DataBufferPages, LogBufferPages)]
[TRANSACTION = MaxTransactions ]
[ {TimeoutValue [SECONDS]}]
[MAXIMUM TIMEOUT = { [MINUTES]}]
[ { }]
[ {NONE }]
[ ] |,...|
[ {TimeoutValue [SECONDS]}]
[DEFAULT TIMEOUT = { [MINUTES]}]
[ { }]
[ {MAXIMUM }]
[ ]
[RUN BLOCK = ControlBlockPages ]
START DBE NEW
START DBE 'DBEnvironmentName' [AS 'ConnectionName'] [MULTI] NEW
[DUAL LOG ]
[BUFFER = (DataBufferPages, LogBufferPages)]
[LANG = LanguageName ]
[TRANSACTION = MaxTransactions ]
[ {TimeoutValue [SECONDS]}]
[MAXIMUM TIMEOUT = { [MINUTES]}]
[ { }]
[ {NONE }]
[ ] |,...|
[ {TimeoutValue [SECONDS]}]
[DEFAULT TIMEOUT = { [MINUTES]}]
[ { }]
[ {MAXIMUM }]
[ ]
[RUN BLOCK = ControlBlockPages ]
[DBEFile0Definition ]
[DBELogDefinition ]
START DBE NEWLOG
START DBE 'DBEnvironmentName' [AS 'ConnectionName'] [MULTI] NEWLOG
[{ARCHIVE} |...| LOG ]
[{DUAL } ]
[ ]
[BUFFER = (DataBufferPages, LogBufferPages)]
[TRANSACTION = MaxTransactions ]
[ {TimeoutValue [SECONDS]}]
[MAXIMUM TIMEOUT = { [MINUTES]}]
[ { }] |,...| NewLogDefinition
[ {NONE }]
[ ]
[ {TimeoutValue [SECONDS]}]
[DEFAULT TIMEOUT = { [MINUTES]}]
[ { }]
[ {MAXIMUM }]
[ ]
[RUN BLOCK = ControlBlockPages ]
NewLogDefinition.
LOG DBEFILE DBELog1ID [AND DBELog2ID] WITH PAGES = DBELogSize, NAME =
'SystemFileName1' [AND ]
['SystemFileName2']
STOP DBE
STOP DBE
TERMINATE USER
TERMINATE USER {DBEUserID}
{SessionID}
TRANSFER OWNERSHIP
{[TABLE] [Owner.]TableName }
TRANSFER OWNERSHIP OF {[VIEW] [Owner.]ViewName } TO NewOwnerName
{PROCEDURE [Owner.]ProcedureName}
{GROUP GroupName }
UPDATE
{ {Expression }}
UPDATE {[Owner.]TableName} SET {ColumnName = {'LongColumnIOString'}}
{[Owner.]ViewName } { {NULL }}
[,...] [WHERE SearchCondition]
LongColumnIOString.
{[< {FileName[.Group[.Account]]}] }
{[ {%HeapAddress:LengthofHeap }] }
{ }
{[{> } {FileName[.Group[.Account]]}]} |...|
{[{>>} {CharString$ }]}
{[{>!} {CharString$CharString }]}
{[ ]}
{[>%$ ]}
UPDATE STATISTICS
UPDATE STATISTICS FOR TABLE {[Owner.]TableName }
{SYSTEM.SystemViewName}
UPDATE WHERE CURRENT
{ {Expression }}
UPDATE {[Owner.]TableName} SET {ColumnName = {'LongColumnIOString'}}
{[Owner.]ViewName } { {NULL }}
[,...] WHERE CURRENT OF CursorName
LongColumnIOString.
{[< {FileName[.Group[.Account]]}] }
{[ {%HeapAddress:LengthofHeap }] }
{ }
{[{> } {FileName[.Group[.Account]]}]} |...|
{[{>>} {CharString$ }]}
{[{>!} {CharString$CharString }]}
{[ ]}
{[>%$ ]}
VALIDATE
{MODULE {[Owner.]ModuleName}[,...] }
{ }
VALIDATE {PROCEDURE {[Owner.]ProcedureName}[,...]}
{ }
{ALL {MODULES } }
{ {PROCEDURES} }
WHENEVER
{SQLERROR } {STOP }
WHENEVER {SQLWARNING} {CONTINUE}
{NOT FOUND } {Label }
{Label }
WHILE
WHILE Condition DO [Statement; [...]] ENDWHILE;