SQL Statements

Language elements that are found in the syntax of many SQL statements.

Conventions used in the SQL syntax descriptions.

Some statement titles are followed by an indicator in square brackets that shows where the statement can be used.

The FIRST, TOP, and LIMIT clauses allow you to return a subset of the rows that satisfy the WHERE clause. The FIRST, TOP, and LIMIT clauses can be used within any SELECT query block that includes an ORDER BY clause. FIRST, TOP, and LIMIT can only be used in the top query block in a statement.

Allocates space for a SQL descriptor area (SQLDA).

Modifies connection information for the SAP IQ agent.

Upgrades a database created with a previous version of the software, adds or removes jConnect™ for JDBC™ support, or defines management of system procedure execution. Run this statement with DBISQL Interactive SQL.

Changes the read/write mode, changes the size, or extends an existing dbspace.

Renames a user-defined domain or data type. Does not rename Java types.

Changes the definition of an event or its associated handler for automating predefined actions. Also alters the definition of scheduled actions.

Modifies an existing function. Include the entire modified function in the ALTER FUNCTION statement.

Renames indexes in base or global temporary tables, foreign key role names of indexes and foreign keys explicitly created by a user, or changes the clustered nature of an index on a catalog store table. You cannot rename indexes created to enforce key constraints.

Any changes to an LDAP server configuration object are applied on subsequent connections. Any connection already started when the change is applied does not immediately reflect the change.

Modifies configuration for the existing user-defined logical server in the database. This statement enforces consistent shared system temporary store settings across physical nodes shared by logical servers.

Changes existing login policies or configures logical server access.

Modifies some or all option values for the root logical server policy or a user-created logical server policy. This statement enforces consistent shared system temporary store settings across physical nodes shared by logical servers.

Renames the multiplex and stores the multiplex name in SYS.ISYSIQINFO system table.

Changes the name, catalog file path, role, or status of the given server.

Replaces an existing procedure with a modified version. Include the entire modified procedure in the ALTER PROCEDURE statement, and reassign user permissions on the procedure.

Migrates a compatibility role to a user-defined system role, then automatically drops the compatibility role.

Alters a sequence. This statement applies to SAP IQ catalog store tables only.

Modifies the attributes of a remote server. Changes made by ALTER SERVER do not take effect until the next connection to the remote server.

Causes the database server to act as a Web server

Changes the settings of an existing spatial reference system.

Modifies a table definition.

Renames, moves or alters the definition of a TEXT index.

Alters a text configuration object.

Replaces a trigger definition with a modified version. You must include the entire new trigger definition in the ALTER TRIGGER statement. This statement applies to SAP IQ catalog store tables only.

Changes user settings.

Replaces a view definition with a modified version.

Backs up an SAP IQ database on one or more archive devices.

Groups SQL statements together.

Groups CREATE INDEX statements together for execution at the same time.

Use this statement to begin a user-defined transaction.

Invokes a procedure.

The CASE statement is a control statement that lets you choose a list of SQL statements to execute based on the value of an expression.

Checkpoints the database.

Closes any open result sets in Interactive SQL (dbisql).

Closes a named cursor.

Stores a comment, in the system tables, about a database object.

Makes changes to the database permanent, or terminates a user-defined transaction.

Activates the Interactive SQL (dbisql) configuration window.

Establishes a connection to the database identified by <database-name> running on the server identified by <engine-name>.

Associates an SAP IQ agent for SAP IQ Cockpit with the named server to support high availability.

Creates a database consisting of several operating system files.

Creates a new dbspace and the associated dbfiles for the IQ main store, cache dbspace, catalog store, or RLV store.

Creates a user-defined data type in the database.

Defines an event and its associated handler for automating predefined actions. Also defines scheduled actions.

Creates a new proxy table that represents an existing table on a remote server.

Assigns an alternate login name and password to be used when communicating with a remote server.

Creates a user-defined function in the database. A function can be created for another user by specifying an owner name. Subject to permissions, a user-defined function can be used in exactly the same way as other non-aggregate functions.

Creates an index on a specified table, or pair of tables. Once an index is created, it is never referenced in a SQL statement again except to delete it using the DROP INDEX statement.

Creates a new LDAP server configuration object for LDAP user authentication. Parameters defined during the creation of an LDAP server configuration object are stored in the ISYSLDAPSERVER (system view SYSLDAPSERVER) system table.

Creates a user-defined logical server. This statement enforces consistent shared system temporary store settings across physical nodes shared by logical servers.

Creates a login policy in the database.

Creates a user-defined logical server policy. This statement enforces consistent shared system temporary store settings across physical nodes shared by logical servers.

Adds a user-defined message to the SYSUSERMESSAGES system table for use by PRINT and RAISERROR statements.

Creates a multiplex server.

Creates a new user-defined SQL procedure in the database.

Creates a new procedure that is compatible with SAP ASE.

Creates an interface to a native or external procedure.

Creates a new role, extends an existing user to act as a role, or manages role administrators on a role.

Creates a schema, which is a collection of tables, views, and permissions and their associated permissions, for a database user.

Creates a sequence that can be used to generate primary key values that are unique across multiple tables, and for generating default values for a table. This statement applies to SAP IQ catalog store tables only.

Adds a server to the ISYSSERVER table.

Permits a database server to act as a Web server.

Creates or replaces a spatial reference system.

Creates or replaces a spatial unit of measurement.

Creates a new table in the database or on a remote server.

Creates a text configuration object.

Creates a TEXT index and specifies the text configuration object to use.

Creates a trigger on a table. This statement applies to SAP IQ catalog store tables only.

Creates a user.

Creates a new variable of the specified data type.

Creates a view on the database. Views are used to give a different perspective on the data even though it is not stored that way.

Frees memory associated with a SQL descriptor area.

Declares host variables in an Embedded SQL program. Host variables are used to exchange data with the database.

Declares a SQL variable within a compound statement (BEGIN... END).

Declares a cursor. Cursors are the primary means for manipulating the results of queries.

Declares a cursor that is compatible with SAP ASE.

Declares a local temporary table.

Deletes all the rows from the named table that satisfy the search condition. If no WHERE clause is specified, all rows from the named table are deleted.

Deletes the data at the current location of a cursor.

Gets information about the host variables required to store data retrieved from the database or host variables used to pass data to the database.

Drops a connection with the database.

Removes objects from the database.

Deletes an SAP IQ agent for SAP IQ Cockpit.

Drops any user connection to the database.

Drops a database and its associated dbspace segment files.

Drops an external login from the SAP IQ system tables.

Removes the named LDAP server configuration object from the SYSLDAPSERVER system view after verifying that the LDAP server configuration object is not in a READY or ACTIVE state.

Removes a login policy from the database.

Drops a user-defined logical server. This statement enforces consistent shared system temporary store settings across physical nodes shared by logical servers.

Removes a logical server policy from the multiplex.

Deletes a server from the multiplex.

Removes a user-defined role from the database or converts a user-extended role to a regular user.

Drops a sequence. This statement applies to SAP IQ catalog store tables only.

Drops a remote server from the SAP IQ system tables.

Deletes a Web service.

Drops a spatial reference system.

Drops a spatial unit of measurement.

Frees resources used by the named prepared statement. These resources are allocated by a successful PREPARE statement, and are normally not freed until the database connection is released.

Drops a text configuration object.

Removes a TEXT index from the database.

Removes a trigger from the database. This statement applies to SAP IQ catalog store tables only.

Removes a user.

Eliminates a SQL variable that was created using the CREATE VARIABLE statement.

Executes a SQL statement.

Invokes a procedure, as an SAP ASE-compatible alternative to the CALL statement.

Extends the range of statements that can be executed from within procedures. It lets you execute dynamically prepared statements, such as statements that are constructed using the parameters passed in to a procedure.

Leaves Interactive SQL.

Retrieves one row from the named cursor. The cursor must have been previously opened.

Repeats the execution of a statement list once for each row in a cursor.

You can execute a SQL query against your database and return the results as a JSON document by using the FOR JSON clause in a SELECT statement.

Sends native syntax to a remote server.

Specifies the database tables or views involved in a SELECT statement.

Retrieves information about variables within a descriptor area, or retrieves actual data from a variable in a descriptor area.

Branches to a labeled statement.

Allows users to manage passwords for other users and administer the CHANGE PASSWORD system privilege.

Grants CONNECT privilege to a user.

Grants CREATE privilege on a specified dbspace to the specified users and roles.

Grants database object-level privileges on individual tables or views to a user or role.

Grants EXECUTE privilege on a procedure or user-defined function.

Creates an explicit integrated login mapping between one or more Windows user profiles and an existing database user ID. This allows a user who successfully logged in to their local machine to connect to a database without having to provide a user ID or password.

Creates a Kerberos-authenticated login mapping from one or more Kerberos principals to an existing database user ID. This allows a user who has successfully logged in to Kerberos (user who has a valid Kerberos ticket-granting ticket) to connect to a database without having to provide a user ID or password.

Grants roles to users or other roles, with or without administrative rights.

Grants the ability for one user to impersonate another user and to administer the SET USER system privilege.

Grants specific system privileges to users or roles, with or without administrative rights.

Grants the USAGE system privilege on a specified sequence to a user or role.

Lets you conditionally execute the first list of SQL statements whose <search-condition> evaluates to TRUE.

Provides conditional execution of a Transact-SQL statement, as an alternative to the SAP IQ IF statement.

Includes a file into a source program to be scanned by the SQL source language preprocessor.

Inserts a single row or a selection of rows, from elsewhere in the current database, into the table. This command can also insert a selection of rows from another database into the table.

Makes Java classes available for use within a database.

Starts a cache monitor that collects buffer cache statistics.

Continues execution by leaving a compound statement or LOOP.

Imports data into a database table from an external file.

Prevents other concurrent transactions from accessing or modifying a table within the specified time.

Repeats the execution of a statement list.

Displays a message, which can be any expression. Clauses can specify where the message is displayed.

Opens a previously declared cursor to access information from the database.

Writes the information retrieved by the current query to a file.

Specifies parameters to an Interactive SQL (dbisql) command file.

Prepares a statement to be executed later or used for a cursor.

Displays a message on the message window of the database server.

Inserts a row into the specified cursor.

Allows user-defined errors to be signaled, and sends a message on the client.

Reads Interactive SQL (dbisql) statements from a file.

Refreshes a text index.

Releases a savepoint within the current transaction.

Removes a class, a package, or a JAR file from a database. Removed classes are no longer available for use as a variable type. Any class, package, or JAR to be removed must already be installed.

Resignals an exception condition.

Restores an SAP IQ database backup from one or more archive devices.

Resumes execution of a procedure that returns result sets.

Exits a function or procedure unconditionally, optionally providing a return value. Statements following RETURN are not executed.

Removes the ability of a user to manage passwords and administer the system privilege.

Removes a user from the database.

Removes CREATE privileges on the specified dbspace from the specified user IDs.

Removes EXECUTE permissions that were given using the GRANT statement.

Removes the INTEGRATED LOGIN permissions that were given using the GRANT statement.

Removes KERBEROS LOGIN permissions that were given using the GRANT statement.

Removes object-level privileges that were given using the GRANT statement.

Removes a users membership in a role or his or her ability to administer the role.

Removes the ability for one user to impersonate another user and to administer the SET USER system privilege.

Removes specific system privileges from specific users and the right to administer the privilege.

Removes USAGE privilege on a specified sequence.

Undoes any changes made since the last COMMIT or ROLLBACK.

Cancels any changes made since a savepoint was established. Changes made prior to the savepoint are not undone; they are still pending.

Cancels any changes made since a savepoint was established using SAVE TRANSACTION. Changes made prior to the SAVE TRANSACTION are not undone; they are still pending.

Establishes a savepoint within the current transaction.

Establishes a savepoint within the current transaction.

Retrieves information from the database.

Assigns a value to a SQL variable.

Sets database options in an SAP ASE-compatible manner.

Changes the active database connection.

Describes the variables in a SQL descriptor area, and places data into the descriptor area.

Changes options that affect the behavior of the database and its compatibility with Transact-SQL. Setting the value of an option can change the behavior for all users or an individual user, in either a temporary or permanent scope.

Changes Interactive SQL (dbisql) options.

Tells the SQL preprocessor to use a SQLCA other than the default global <sqlca>.

Allows a user to temporarily assume the roles and system privileges of another user (also known as impersonation) to perform operations, provided they already have the minimum required privileges to perform the task to begin with.

Lets you raise an exception condition.

Starts a database on the specified database server.

Starts a database server.

Starts an external environment.

Loads the Java VM at a convenient time, so that when the user starts to use Java functionality, there is no initial pause while the Java VM is loaded.

Stops a database on the specified database server.

Stops a database server.

Stops an external environment.

Releases resources associated with the Java VM to economize on the use of system resources.

Triggers a named event. The event may be defined for event triggers or be a scheduled event.

Deletes all rows from a table or materialized view without deleting the table definition.

Deletes the data in a MANUAL or an AUTO REFRESH text index.

Combines the results of two or more select statements.

Modifies existing rows of a single table, or a view that contains only one table.

Modifies the data at the current location of a cursor.

Validates the current database, or a single table, materialized view, or index in the IQ catalog (system) store.

Validates changes to the settings of existing LDAP server configuration objects before applying them.

Delays processing for the current connection for a specified amount of time or until a given time.

Specifies error handling in an Embedded SQL program.

Provides repeated execution of a statement or compound statement.