Restriction: This topic applies to Windows environments only.
ALLOCATE CURSOR The ALLOCATE CURSOR statement defines a cursor and associates it with a result set locator variable.
ALTER DATABASE The ALTER DATABASE statement changes the description of the specified database under the currently set XDB Server location.
ALTER FUNCTION The ALTER FUNCTION statement changes the description of an external scalar or external table function at the current server.
ALTER FUNCTION (SQL) The ALTER FUNCTION (SQL) statement changes the description of a user-defined SQL scalar function at the current server.
ALTER INDEX On a mainframe DB2 subsystem, this statement changes the configuration of an existing index. See the ALTER TABLE command for
information on altering indexes that result from UNIQUE, PRIMARY and FOREIGN keys. In some cases, it may be necessary to drop
an index and re-create it using the DROP INDEX and CREATE INDEX commands.
ALTER LOCATION The ALTER LOCATION statement converts (upgrades) a location to DB2 Version 6, 7 or 8 compatibility.
ALTER PASSWORD The ALTER PASSWORD statement enables you to change passwords.
ALTER PROCEDURE (external) The ALTER PROCEDURE statement changes the description of a stored procedure at the current server.
ALTER PROCEDURE (SQL) The XDB Server operating environment only provides syntax support for external Procedures (SQL) at this time.
ALTER SEQUENCE The ALTER SEQUENCE statement changes the attributes of a sequence at the current server. Only future values of the sequence
are affected by the ALTER SEQUENCE statement.
ALTER STOGROUP The ALTER STOGROUP statement changes the specifications of an existing storage group (stogroup) defined within the current
XDB Server location.
ALTER TABLE The ALTER TABLE statement changes the description of a table at the current server.
ALTER TABLESPACE The ALTER TABLESPACE statement changes the specifications of a table space within the currently set XDB Server location.
ALTER VIEW The ALTER VIEW statement regenerates a view using an existing view definition at the current server.
ASSOCIATE LOCATORS The ASSOCIATE LOCATORS statement gets the result set locator value for each result set returned by a stored procedure.
BACKUP LOCATION This XDB Server utility command copies the entire contents of an XDB Server location to the specified drive and path while
the XDB Server location is still generally available.
BEGIN DECLARE SECTION The BEGIN DECLARE SECTION statement (embedded SQL only) marks the beginning of a host variable declare section in an application
program.
BULK The BULK ON command activates the Bulk Upload feature which increases the efficiency of your transactions between the mainframe and XDB Link. The BULK OFF command turns
off the Bulk Upload feature.
CALL The XDB Server can run a stored procedure on the mainframe (through XDB Link) or on an XDB Server through use of the CALL
statement.
CHECKPOINT CHECKPOINT is an SQL command which flushes the database and log files.
CLOSE The CLOSE command (embedded SQL only) closes a cursor previously opened by a DECLARE CURSOR command. If a temporary table
is created as a result of the cursor, that table is deleted when the cursor is closed.
COMMENT ON The COMMENT ON statement adds or replaces descriptive text comments for aliases, columns, distinct types (currently not supported),
stored procedures, tables, triggers, user-defined functions, and views located within the current XDB Server location. All
comments are stored in the catalog tables belonging to that location.
COMMIT The COMMIT statement is used to mark the end of a transaction (unit of work) and preserve all of the database changes made
by the transaction.
COMPACT Use the COMPACT command to pack table, index and dictionary files in order to reuse disk storage space.
CONNECT The CONNECT statement (embedded SQL only) connects the application process to the specified XDB Server or DB2 (through XDB
Link) location. This location becomes the current location for the application process.
CREATE ALIAS The CREATE ALIAS command defines an alternate name for an existing table or view, and records the alias definition in the
XDB Server system catalog at the currently set location. It is not required that the table or view be in the current location.
CREATE AUXILIARY TABLE The CREATE AUXILIARY TABLE statement creates an auxiliary table at the current server for storing LOB data.
CREATE DATABASE The CREATE DATABASE command creates a logical database structure at the currently set XDB Server location.
CREATE DISTINCT TYPE The CREATE DISTINCT TYPE statement defines a distinct type, which is a data type that a user defines. A distinct type must
be sourced on one of the built-in data types. Successful execution of the statement also generates:
CREATE FUNCTION The CREATE FUNCTION statement registers a user-defined function with an application server. You can register three different
types of functions with this statement, each of which is described separately.
CREATE FUNCTION (external scalar) This CREATE FUNCTION statement registers a user-defined external scalar function with an application server.
CREATE FUNCTION (external table) This CREATE FUNCTION statement registers a user-defined external table function with an application server.
CREATE FUNCTION (sourced) This CREATE FUNCTION statement registers a user-defined function that is based on an existing scalar or column function with
an application server.
CREATE FUNCTION (SQL Scalar) This statement is used to define a user-defined SQL scalar function. A scalar function returns a single value each time it
is invoked. Specifying a function is generally valid wherever an SQL expression is valid.
CREATE GLOBAL TEMPORARY TABLE Create a temporary table to store the intermediate results of SQL transactions. The CREATE GLOBAL TEMPORARY TABLE statement
creates a description of a temporary table at the current server.
CREATE INDEX Creates an index on one or more columns of a table at the current location.
CREATE LOCATION The CREATE LOCATION command allows you to define multiple XDB Server locations under the same XDB Server. Each XDB Server
location contains a set of catalog tables for that location.
CREATE PROCEDURE The CREATE PROCEDURE command defines a Stored Procedure to the SQL engine. You can then embed an SQL CALL statement in a program
to invoke the stored procedure.
CREATE PROCEDURE The CREATE PROCEDURE statement defines an SQL procedure at the current server and specifies the source statements for the
procedure.
CREATE SEQUENCE The CREATE SEQUENCE statement creates a sequence at the application server.
CREATE STOGROUP The CREATE STOGROUP command creates an XDB Server stogroup which associates a user-specified directory path with the named
stogroup. Once the SYSIBM.SYSVOLUMES catalog table has been updated to reflect this path assignment, newly created objects
can be physically stored in this directory path by designating the named stogroup when creating the objects.
CREATE SYNONYM The CREATE SYNONYM command is used to define alternate names for tables or views within the currently set location.
CREATE TABLE The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns.
The definition can include other attributes of the table, such as its primary key and its table space.
CREATE TABLESPACE This statement defines a table space in the current location. A table space in the XDB Server system architecture is logically
compatible with DB2 table spaces. However, an XDB Server table space does not physically store XDB Server table (.TAB) files.
This XDB Server interpretation of table spaces differs from a DB2 subsystem, where a table space can physically store multiple
tables. Refer to the appropriate DB2 documentation for details on the mainframe implementation of table spaces.
CREATE TRIGGER The CREATE TRIGGER statement defines a trigger in a schema and builds a trigger package at the current server.
CREATE USER The CREATE USER statement enables you to create additional XDB user accounts.
CREATE VIEW The CREATE VIEW command is used to define a view or virtual table that is based on one or more tables or views. The view definition
is stored at the current location. While a view does not actually contain data, it provides indirect access to data contained
in the tables upon which the view is based. Views may be used to define subsets of tables, or to combine data from several
tables into a logical view. Views may contain expressions that are computed from other columns, including aggregate functions
such as averages, totals, etc.
DECLARE CURSOR The DECLARE CURSOR command (embedded SQL only) is used to define a cursor, which is a named control structure that points to a row in a set of records defined by a query.
DECLARE GLOBAL TEMPORARY TABLE The DECLARE GLOBAL TEMPORARY TABLE statement defines a declared temporary table for the current application process and instantiates
an empty instance of the table for the process.
DECLARE STATEMENT The DECLARE STATEMENT command (embedded SQL only) identifies prepared SQL statements for later use in application programs.
DECLARE TABLE The DECLARE TABLE statement (embedded SQL only) is used for application program documentation and providing the precompiler
with information for checking embedded SQL statements.
DELETE (Positioned) The DELETE statement removes any number of records from a table or view. The table or view can be within the current location
or any other location with which the XDB Server can establish a connection, provided that the user has privileges in that
other location. Deleting a row from a view actually deletes the row from the base table on which the view was created (if
it is an updatable view).
DELETE (Searched) The DELETE statement removes any number of records from a table or view. The table or view can be within the current location
or any other location with which the XDB Server can establish a connection, provided that the user has privileges in that
other location. Deleting a row from a view actually deletes the row from the base table on which the view was created (if
it is an updatable view).
DESCRIBE The DESCRIBE statement (embedded SQL only) obtains information about a previously prepared (PREPARE command) SQL statement
and places this information inside an SQL Descriptor Area (SQLDA). This information is needed in dynamic SQL queries that
utilize a varying-list SELECT statement. A varying-list SELECT statement is a SELECT statement where the number of columns
retrieved is not known before runtime. Alternatively, the DESCRIBE statement can provide information about a specified table
or view.
DESCRIBE CURSOR The DESCRIBE CURSOR statement gets information about the result set that is associated with the cursor. The information, such
as column information, is put into a descriptor. Use DESCRIBE CURSOR for result set cursors from stored procedures. The cursor
must be defined with the ALLOCATE CURSOR statement.
DESCRIBE PROCEDURE The DESCRIBE PROCEDURE statement gets information about the result sets returned by a stored procedure. The information, such
as the number of result sets, is put into a descriptor.
DISABLE LOGIN Use this command to prevent all users, except those with SYSADM privileges, from logging on to the XDB Server. This command
does not affect users who are already logged on.
DROP The DROP statement removes (erases) an object from the current location. When an object is deleted, its description is also
deleted from the system catalog at the current location.
ENABLE LOGIN Use this command to return a server to service after it has been taken out of service with the DISABLE LOGIN command.
END DECLARE SECTION The END DECLARE SECTION statement (embedded SQL only) marks the end of a host variable declaration block in an application
program.
EXECUTE The EXECUTE command (embedded SQL only) executes a previously prepared nonquery statement. To prepare and execute in one step,
see the EXECUTE IMMEDIATE command.
EXECUTE IMMEDIATE The EXECUTE IMMEDIATE statement (embedded SQL only) prepares an executable statement from a character string and then immediately
executes the statement.
EXPLAIN The EXPLAIN statement uses the optimizer to acquire access path selection information about SELECT, INSERT (and the searched
form of UPDATE or DELETE) statements. This information is placed by the XDB Server into a table named PLAN_TABLE. The XDB
Server creates this table automatically if it has not already been created by the user.
FETCH The FETCH command (embedded SQL only) positions a cursor on a row of its result table. It can return zero, one, or multiple
rows and assigns the values of the rows to host variables if there is a target specification.
FREE LOCATOR The FREE LOCATOR statement removes the association between a LOB locator variable and its value.
GET DIAGNOSTICS The GET DIAGNOSTICS statement provides diagnostic information about the last SQL statement (other than a GET DIAGNOSTICS statement)
that was executed. This diagnostic information is gathered as the previous SQL statement is executed. Some of the information
available through the GET DIAGNOSTICS statement is also available in the SQLCA.
GRANT (Database Privileges) The GRANT command confers database, table or view, and system privileges to AuthIDs. The XDB Server supports three separate
GRANT statement formats for granting database, table and system privileges. All relevant objects must be located in the current
location's catalog tables. Granted privileges are recorded in the system catalog for the current location. Specific privileges
may later be revoked (see REVOKE statement).
GRANT (Function or Procedure Privileges) This form of the GRANT statement grants privileges on user-defined functions, cast functions that are generated for distinct
types, and stored procedures.
GRANT (Table or View Privileges) The GRANT command confers database, table or view, and system privileges to AuthIDs. The XDB Server supports three separate
GRANT statement formats for granting database, table and system privileges. All relevant objects must be located in the current
location's catalog tables. Granted privileges are recorded in the system catalog for the current location. Specific privileges
may later be revoked (see REVOKE statement).
GRANT (System Privileges) The GRANT command confers database, table or view, and system privileges to AuthIDs. The XDB Server supports three separate
GRANT statement formats for granting database, table and system privileges. All relevant objects must be located in the current
location's catalog tables. Granted privileges are recorded in the system catalog for the current location. Specific privileges
may later be revoked (see REVOKE statement).
HALT QUERY FROM Use the HALT QUERY FROM command to terminate a running query. You can specify the query by user ID or by node name. This command
is useful for canceling a query operation that is monopolizing the server computer.
HOLD LOCATOR The HOLD LOCATOR statement allows a LOB locator variable to retain its association with a value beyond a unit of work. The
XDB operating environment does not support the LOB data type at this time.
INCLUDE The INCLUDE statement (embedded SQL only) inserts declarations or code into source programs from external files.
INSERT The INSERT command is an SQL command used to insert zero or more rows of data values (or records) into an existing table or
view. The table or view receiving the new rows can be at the current location or any other location accessible to the XDB
Server. Inserting a row into a view also inserts the row into that view's underlying table (if the view is updatable).
LABEL ON The LABEL ON statement adds or replaces descriptive text labels in the system catalog for the current location. These text
labels describe tables, views, aliases, or columns existing at the current location.
LOCK TABLE The LOCK TABLE statement is used to acquire a shared or exclusive lock on a table at the current location.
OPEN The OPEN command (embedded SQL only) opens a previously declared cursor creating a result table containing row data.
PREPARE The PREPARE statement (embedded SQL only) transforms a character string form of an SQL statement into an executable form of
the same statement. The resulting executable statement is known as a prepared statement, while the original character string
is known as the statement string.
REFRESH LOCATIONS REFRESH LOCATIONS is an XDB Server utility command (not supported in DB2 mode) that re-reads the SYSIBM.SYSLOCATIONS catalog
table and updates the system with any new location changes. The REFRESH LOCATIONS command is necessary only if a location
has been manually inserted into SYSIBM.SYSLOCATIONS. If a new location is created using the CREATE LOCATION command, the system
is automatically reset (and the catalog tables updated) to include the new location information.
REFRESH TABLE The REFRESH TABLE statement refreshes the data in a materialized query table. The statement deletes all rows in the materialized
query table, executes the fullselect in the table definition to recalculate the data from the tables specified in the fullselect,
inserts the calculated result into the materialized query table, and updates the catalog for the refresh timestamp and cardinality
of the table. The table can exist at the current server or at any DB2 subsystem with which the current server can establish
a connection.
RELEASE (connection) The RELEASE statement places one or more connections in the release pending state.
RELEASE SAVEPOINT The RELEASE SAVEPOINT statement releases the identified savepoint and any subsequently established savepoints within a unit
of recovery.
RENAME The RENAME statement renames an existing table.
REVOKE (Database Privileges) The REVOKE command cancels database, table or view, or system privileges held by specified AuthIDs. The XDB Server supports
three separate REVOKE formats, one each for revoking database, table or view, and system privileges. All relevant objects
must be located in the current location. Revoked privileges are recorded in the system catalog for the current location.
REVOKE (Function or Procedure Privileges) This form of the REVOKE statement revokes privileges on user-defined functions, cast functions that were generated for distinct
types, and stored procedures.
REVOKE (Sequence Privileges) This form of the REVOKE statement revokes the privileges on a user-defined sequence.
REVOKE (System Privileges) The REVOKE command cancels database, table or view, or system privileges held by specified AuthIDs. The XDB Server supports
three separate REVOKE formats, one each for revoking database, table or view, and system privileges. All relevant objects
must be located in the current location. Revoked privileges are recorded in the system catalog for the current location.
REVOKE (Table or View Privileges) The REVOKE command cancels database, table or view, or system privileges held by specified AuthIDs. The XDB Server supports
three separate REVOKE formats, one each for revoking database, table or view, and system privileges. All relevant objects
must be located in the current location. Revoked privileges are recorded in the system catalog for the current location.
ROLLBACK ROLLBACK is used to mark the end of a transaction and back out of all changes made by the transaction at the current location.
The XDB Server does not rollback data definition language (DDL) commands such as CREATE, DROP, ALTER; data control language
(DCL) commands such as GRANT, and REVOKE; or utility commands such as SET and COPY. When working in a production environment,
it is recommended that you backup your location before and after such commands.
ROLLFORWARD ROLLFORWARD allows users to roll forward the transactions stored in supplied log files to a specified location. This command
must be run while in exclusive use mode.
SAVEPOINT The SAVEPOINT statement sets a savepoint within a unit of recovery to identify a point in time within the unit of recovery
to which relational database changes can be rolled back.
SELECT SELECT commands (or queries) all retrieve information from data tables and display this information in a results table. For
purposes of classification, SELECT commands can be subdivided into three general categories including, subselects (and subqueries),
fullselects and select statements.
SELECT INTO The SELECT INTO command (embedded SQL only) retrieves one row of values from a table or view, and then assigns these values
to one or more host variables specified in the SELECT INTO command statement.
SET AUTOCOMMIT The SET AUTOCOMMIT command is used to turn autocommit on or off. When autocommit is on, a command is automatically committed
as soon as it is successfully executed.
SET COMPATIBILITY The SET COMPATIBILITY command is used to specify a mode of operation. When a particular mode is in effect, SQL commands must
adhere to the syntax of that mode.
SET CONNECTION The SET CONNECTION statement establishes the application server of the process by identifying one of its existing connections.
SET CURRENT DEGREE This command is supported only syntactically by the XDB Server.
SET CURRENT LOCALE LC_CTYPE The SET CURRENT LOCALE LC_CTYPE statement assigns a value to the CURRENT LOCALE LC_CTYPE special register. The special register
allows control over the LC_CTYPE locale for statements that use a function that refers to a locale, such as LCASE, UCASE,
and TRANSLATE (with a single argument).
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION The SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION statement changes the value of the CURRENT MAINTAINED TABLE TYPES
FOR OPTIMIZATION special register.
SET CURRENT OPTIMIZATION HINT The SET CURRENT OPTIMIZATION HINT statement assigns a value to the CURRENT OPTIMIZATION HINT special register.
SET CURRENT PACKAGE PATH The SET CURRENT PACKAGE PATH assigns a value to the CURRENT PACKAGE PATH special register.
SET CURRENT PACKAGESET On mainframe DB2 and XDB Servers, this command assigns a value to the CURRENT PACKAGESET special register. This command is
partially supported by the XDB Server, which adopts the CURRENT SQLID as the qualifier for the package.
SET CURRENT PATH The SET CURRENT PATH statement assigns a value to the CURRENT PATH special register.
SET CURRENT REFRESH AGE The SET CURRENT REFRESH AGE statement changes the value of the CURRENT REFRESH AGE special register.
SET CURRENT RULES The SET CURRENT RULES statement assigns a value to the CURRENT RULES special register.
SET CURRENT SQLID The SET CURRENT SQLID statement changes the AuthID value stored in the CURRENT SQLID special register.
SET Host Variable The SET host-variable assignment statement assigns values, either of expressions or NULL values, to host variables.
SET Transitional Variable The SET transition-variable assignment statement assigns values, either of expressions or NULL values, to transition variables.
SET ISOLATION The SET ISOLATION command is used to set the isolation level for locking in multi-user versions.
SET LOCATION TO The SET LOCATION command logs a user onto a new location, creating an implied connection between the user's sessions and the
specified location. The value of the CURRENT SERVER special register is also updated to reflect the new active location.
SET LOCATION Use this command to prevent all users, except for those with SYSADM privileges, from logging on to a location on the XDB Server.
This command does not affect users who are already using the location.
SET LOG The SET LOG command allows Database Administrators (with SYSADM authority or higher) to suspend logging system operation during
an administrative session, and change the path where location log files are stored.
SET SCHEMA The SET SCHEMA statement assigns a value to the CURRENT SCHEMA special register. If the package is bound with the DYNAMICRULES
BIND option, this statement does not affect the qualifier that is used for unqualified database object references.
SET SORTSPACE Use this command to dynamically reset the size of the sort space on the XDB Server. Active sorts are not affected by this
command.
SHUTDOWN Use the SHUTDOWN command to close the XDB Server program at a specified time. You can also use a variant of this command to
cancel a SHUTDOWN request that you have issued.
SIGNAL SQLSTATE The SIGNAL SQLSTATE statement is used to signal an error. It causes an error to be returned with the specified SQLSTATE and
error description.
START DATABASE The START DATABASE command allows users access to the contents of a named database.
START PROCEDURE The START PROCEDURE command checks the argument list for a stored procedure already defined to the SQL engine. If the stored
procedure meta-data has been imported from another system, this command can be used to check that the argument list is compatible
with XDB syntax rules.
STOP DATABASE The STOP DATABASE command restricts all access to the named database.
TRUNCATE TABLE The TRUNCATE TABLE command performs the same function as the DELETE command, except TRUNCATE TABLE removes all records from a table without recording the changes in the Forward and Backward Log files.
UNLOCK TABLE UNLOCK TABLE is used in multi-user versions to release table locks acquired using the LOCK TABLE command.
UPDATE (Searched) The UPDATE command modifies the values of specified columns in rows of a table or view, if the view is updatable. Modifying
a row of a view also modifies the underlying base table of the view. The UPDATE command statement has two configurations,
depending on whether a searched update or positioned update is performed. A searched update modifies zero or more rows of a table. These updated rows can be optionally selected with
a search condition. A positioned update modifies only one row -- derived as the current position of a previously declared
and opened cursor. The table or view being updated can exist at the current location or at any other location accessible from
the XDB Server.
UPDATE (Positioned) The UPDATE command modifies the values of specified columns in rows of a table or view, if the view is updatable. Modifying
a row of a view also modifies the underlying base table of the view. The UPDATE command statement has two configurations,
depending on whether a searched update or positioned update is performed. A searched update modifies zero or more rows of a table. These updated rows can be optionally selected with
a search condition. A positioned update modifies only one row -- derived as the current position of a previously declared
and opened cursor. The table or view being updated can exist at the current location or at any other location accessible from
the XDB Server.
VALUES The VALUES statement provides a method for invoking a user-defined function from a trigger. Transition variables and transition
tables can be passed to the user-defined function.
VALUES INTO The VALUES INTO statement assigns one or more values to host variables.
WHENEVER The WHENEVER statement (embedded SQL only) has three different forms, each of which changes the execution of a host language
program according to the occurrence of specified exception conditions.