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.
