SQL Statements

Descriptions of the SQL statements available in SAP Sybase IQ, including some that can be used only from Embedded SQL or Interactive SQL.

• Common Elements in SQL Syntax
  Language elements that are found in the syntax of many SQL statements.
• Syntax Conventions
  Conventions used in the SQL syntax descriptions.
• Statement Applicability Indicators
  Some statement titles are followed by an indicator in square brackets that shows where the statement can be used.
• Row Limitation Clauses in SELECT Query Blocks
  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.
• ALLOCATE DESCRIPTOR Statement [ESQL]
  Allocates space for an SQL descriptor area (SQLDA).
• ALTER AGENT Statement
  Modifies connection information for the SAP Sybase IQ agent.
• ALTER DATABASE Statement
  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.
• ALTER DBSPACE Statement
  Changes the read/write mode, changes the size, or extends an existing dbspace.
• ALTER DOMAIN Statement
  Renames a user-defined domain or data type. Does not rename Java types.
• ALTER EVENT Statement
  Changes the definition of an event or its associated handler for automating predefined actions. Also alters the definition of scheduled actions.
• ALTER FUNCTION Statement
  Modifies an existing function. Include the entire modified function in the ALTER FUNCTION statement.
• ALTER INDEX 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.
• ALTER LDAP SERVER Statement
  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.
• ALTER LOGICAL SERVER Statement
  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.
• ALTER LOGIN POLICY Statement
  Changes existing login policies or configures logical server access.
• ALTER LS POLICY Statement
  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.
• ALTER MULTIPLEX RENAME Statement
  Renames the multiplex and stores the multiplex name in SYS.ISYSIQINFO system table.
• ALTER MULTIPLEX SERVER Statement
  Changes the name, catalog file path, role, or status of the given server.
• ALTER PROCEDURE Statement
  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.
• ALTER ROLE Statement
  Migrates a compatibility role to a user-defined system role, then automatically drops the compatibility role.
• ALTER SEQUENCE statement
  Alters a sequence. This statement applies to SAP Sybase IQ catalog store tables only.
• ALTER SERVER Statement
  Modifies the attributes of a remote server. Changes made by ALTER SERVER do not take effect until the next connection to the remote server.
• ALTER SERVICE Statement
  Causes the database server to act as a Web server
• ALTER SPATIAL REFERENCE SYSTEM Statement
  Changes the settings of an existing spatial reference system.
• ALTER TABLE Statement
  Modifies a table definition.
• ALTER TEXT INDEX Statement
  Renames, moves or alters the definition of a TEXT index.
• ALTER TEXT CONFIGURATION Statement
  Alters a text configuration object.
• ALTER TRIGGER statement
  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 Sybase IQ catalog store tables only.
• ALTER USER Statement
  Changes user settings.
• ALTER VIEW Statement
  Replaces a view definition with a modified version.
• BACKUP DATABASE Statement
  Backs up an SAP Sybase IQ database on one or more archive devices.
• BEGIN … END Statement
  Groups SQL statements together.
• BEGIN PARALLEL IQ … END PARALLEL IQ Statement
  Groups CREATE INDEX statements together for execution at the same time.
• BEGIN TRANSACTION Statement [T-SQL]
  Use this statement to begin a user-defined transaction.
• CALL Statement
  Invokes a procedure.
• CASE Statement
  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.
• CHECKPOINT Statement
  Checkpoints the database.
• CLEAR Statement [Interactive SQL]
  Closes any open result sets in Interactive SQL (dbisql).
• CLOSE Statement [ESQL] [SP]
  Closes a named cursor.
• COMMENT Statement
  Stores a comment, in the system tables, about a database object.
• COMMIT Statement
  Makes changes to the database permanent, or terminates a user-defined transaction.
• CONFIGURE Statement [Interactive SQL]
  Activates the Interactive SQL (dbisql) configuration window.
• CONNECT Statement [ESQL] [Interactive SQL]
  Establishes a connection to the database identified by database-name running on the server identified by engine-name.
• CREATE AGENT Statement
  Associates an SAP Sybase IQ agent for SAP Control Center with the named server to support high availability.
• CREATE DATABASE Statement
  Creates a database consisting of several operating system files.
• CREATE DBSPACE Statement
  Creates a new dbspace and the associated dbfiles for the IQ main store, cache dbspace, catalog store, or RLV store.
• CREATE DOMAIN Statement
  Creates a user-defined data type in the database.
• CREATE EVENT Statement
  Defines an event and its associated handler for automating predefined actions. Also defines scheduled actions.
• CREATE EXISTING TABLE Statement
  Creates a new proxy table that represents an existing table on a remote server.
• CREATE EXTERNLOGIN Statement
  Assigns an alternate login name and password to be used when communicating with a remote server.
• CREATE FUNCTION Statement
  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.
• CREATE INDEX Statement
  Creates an index on a specified table, or pair of tables. Once an index is created, it is never referenced in an SQL statement again except to delete it using the DROP INDEX statement.
• CREATE LDAP SERVER 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.
• CREATE LOGICAL SERVER Statement
  Creates a user-defined logical server. This statement enforces consistent shared system temporary store settings across physical nodes shared by logical servers.
• CREATE LOGIN POLICY Statement
  Creates a login policy in the database.
• CREATE LS POLICY Statement
  Creates a user-defined logical server policy. This statement enforces consistent shared system temporary store settings across physical nodes shared by logical servers.
• CREATE MESSAGE Statement [T-SQL]
  Adds a user-defined message to the SYSUSERMESSAGES system table for use by PRINT and RAISERROR statements.
• CREATE MULTIPLEX SERVER Statement
  Creates a multiplex server.
• CREATE PROCEDURE Statement
  Creates a new user-defined SQL procedure in the database.
• CREATE PROCEDURE Statement [T-SQL]
  Creates a new procedure that is compatible with Adaptive Server.
• CREATE PROCEDURE Statement (External Procedures)
  Creates an interface to a native or external procedure.
• CREATE ROLE Statement
  Creates a new role, extends an existing user to act as a role, or manages role administrators on a role.
• CREATE SCHEMA Statement
  Creates a schema, which is a collection of tables, views, and permissions and their associated permissions, for a database user.
• CREATE SEQUENCE statement
  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 Sybase IQ catalog store tables only.
• CREATE SERVER Statement
  Adds a server to the ISYSSERVER table.
• CREATE SERVICE Statement
  Permits a database server to act as a Web server.
• CREATE SPATIAL REFERENCE SYSTEM Statement
  Creates or replaces a spatial reference system.
• CREATE SPATIAL UNIT OF MEASURE Statement
  Creates or replaces a spatial unit of measurement.
• CREATE TABLE Statement
  Creates a new table in the database or on a remote server.
• CREATE TEXT CONFIGURATION Statement
  Creates a text configuration object.
• CREATE TEXT INDEX Statement
  Creates a TEXT index and specifies the text configuration object to use.
• CREATE TRIGGER statement
  Creates a trigger on a table. This statement applies to SAP Sybase IQ catalog store tables only.
• CREATE USER Statement
  Creates a user.
• CREATE VARIABLE Statement
  Creates a new variable of the specified data type.
• CREATE VIEW Statement
  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.
• DEALLOCATE DESCRIPTOR Statement [ESQL]
  Frees memory associated with an SQL descriptor area.
• Declaration Section [ESQL]
  Declares host variables in an Embedded SQL program. Host variables are used to exchange data with the database.
• DECLARE Statement
  Declares an SQL variable within a compound statement (BEGIN... END).
• DECLARE CURSOR Statement [ESQL] [SP]
  Declares a cursor. Cursors are the primary means for manipulating the results of queries.
• DECLARE CURSOR Statement [T-SQL]
  Declares a cursor that is compatible with Adaptive Server.
• DECLARE LOCAL TEMPORARY TABLE Statement
  Declares a local temporary table.
• DELETE Statement
  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.
• DELETE (positioned) Statement [ESQL] [SP]
  Deletes the data at the current location of a cursor.
• DESCRIBE Statement [ESQL]
  Gets information about the host variables required to store data retrieved from the database or host variables used to pass data to the database.
• DISCONNECT Statement [Interactive SQL]
  Drops a connection with the database.
• DROP Statement
  Removes objects from the database.
• DROP AGENT Statement
  Deletes an SAP Sybase IQ agent for SAP Control Center.
• DROP CONNECTION Statement
  Drops any user connection to the database.
• DROP DATABASE Statement
  Drops a database and its associated dbspace segment files.
• DROP EXTERNLOGIN Statement
  Drops an external login from the SAP Sybase IQ system tables.
• DROP LDAP SERVER Statement
  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.
• DROP LOGIN POLICY Statement
  Removes a login policy from the database.
• DROP LOGICAL SERVER Statement
  Drops a user-defined logical server. This statement enforces consistent shared system temporary store settings across physical nodes shared by logical servers.
• DROP LS POLICY Statement
  Removes a logical server policy from the multiplex.
• DROP MULTIPLEX SERVER Statement
  Deletes a server from the multiplex.
• DROP ROLE Statement
  Removes a user-defined role from the database or converts a user-extended role to a regular user.
• DROP SEQUENCE statement
  Drops a sequence. This statement applies to SAP Sybase IQ catalog store tables only.
• DROP SERVER Statement
  Drops a remote server from the SAP Sybase IQ system tables.
• DROP SERVICE Statement
  Deletes a Web service.
• DROP SPATIAL REFERENCE SYSTEM Statement
  Drops a spatial reference system.
• DROP SPATIAL UNIT OF MEASURE Statement
  Drops a spatial unit of measurement.
• DROP STATEMENT Statement [ESQL]
  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.
• DROP TEXT CONFIGURATION Statement
  Drops a text configuration object.
• DROP TEXT INDEX Statement
  Removes a TEXT index from the database.
• DROP TRIGGER statement
  Removes a trigger from the database. This statement applies to SAP Sybase IQ catalog store tables only.
• DROP USER Statement
  Removes a user.
• DROP VARIABLE Statement
  Eliminates an SQL variable that was created using the CREATE VARIABLE statement.
• EXECUTE Statement [ESQL]
  Executes an SQL statement.
• EXECUTE Statement [T-SQL]
  Invokes a procedure, as an Adaptive Server-compatible alternative to the CALL statement.
• EXECUTE IMMEDIATE Statement [ESQL] [SP]
  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.
• EXIT Statement [Interactive SQL]
  Leaves Interactive SQL.
• FETCH Statement [ESQL] [SP]
  Retrieves one row from the named cursor. The cursor must have been previously opened.
• FOR Statement
  Repeats the execution of a statement list once for each row in a cursor.
• FOR JSON Statement
  You can execute an SQL query against your database and return the results as a JSON document by using the FOR JSON clause in a SELECT statement.
• FORWARD TO Statement
  Sends native syntax to a remote server.
• FROM Clause
  Specifies the database tables or views involved in a SELECT statement.
• GET DESCRIPTOR Statement [ESQL]
  Retrieves information about variables within a descriptor area, or retrieves actual data from a variable in a descriptor area.
• GOTO Statement [T-SQL]
  Branches to a labeled statement.
• GRANT CHANGE PASSWORD Statement
  Allows users to manage passwords for other users and administer the CHANGE PASSWORD system privilege.
• GRANT CONNECT Statement
  Grants CONNECT privilege to a user.
• GRANT CREATE Statement
  Grants CREATE privilege on a specified dbspace to the specified users and roles.
• GRANT Object-Level Privilege Statement
  Grants database object-level privileges on individual tables or views to a user or role.
• GRANT EXECUTE Statement
  Grants EXECUTE privilege on a procedure or user-defined function.
• GRANT INTEGRATED LOGIN Statement
  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.
• GRANT KERBEROS LOGIN Statement
  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.
• GRANT ROLE Statement
  Grants roles to users or other roles, with or without administrative rights.
• GRANT SET USER Statement
  Grants the ability for one user to impersonate another user and to administer the SET USER system privilege.
• GRANT System Privilege Statement
  Grants specific system privileges to users or roles, with or without administrative rights.
• GRANT USAGE ON SEQUENCE Statement
  Grants the USAGE system privilege on a specified sequence to a user or role.
• IF Statement
  Lets you conditionally execute the first list of SQL statements whose search-condition evaluates to TRUE.
• IF Statement [T-SQL]
  Provides conditional execution of a Transact-SQL statement, as an alternative to the SAP Sybase IQ IF statement.
• INCLUDE Statement [ESQL]
  Includes a file into a source program to be scanned by the SQL source language preprocessor.
• INSERT Statement
  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.
• INSTALL JAVA Statement
  Makes Java classes available for use within a database.
• IQ UTILITIES Statement
  Starts a cache monitor that collects buffer cache statistics.
• LEAVE Statement
  Continues execution by leaving a compound statement or LOOP.
• LOAD TABLE Statement
  Imports data into a database table from an external file.
• LOCK TABLE Statement
  Prevents other concurrent transactions from accessing or modifying a table within the specified time.
• LOOP Statement
  Repeats the execution of a statement list.
• MESSAGE Statement
  Displays a message, which can be any expression. Clauses can specify where the message is displayed.
• OPEN Statement [ESQL] [SP]
  Opens a previously declared cursor to access information from the database.
• OUTPUT Statement [Interactive SQL]
  Writes the information retrieved by the current query to a file.
• PARAMETERS Statement [Interactive SQL]
  Specifies parameters to an Interactive SQL (dbisql) command file.
• PREPARE Statement [ESQL]
  Prepares a statement to be executed later or used for a cursor.
• PRINT Statement [T-SQL]
  Displays a message on the message window of the database server.
• PUT Statement [ESQL]
  Inserts a row into the specified cursor.
• RAISERROR Statement [T-SQL]
  Allows user-defined errors to be signaled, and sends a message on the client.
• READ Statement [Interactive SQL]
  Reads Interactive SQL (dbisql) statements from a file.
• REFRESH TEXT INDEX Statement
  Refreshes a text index.
• RELEASE SAVEPOINT Statement
  Releases a savepoint within the current transaction.
• REMOVE Statement
  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.
• RESIGNAL Statement
  Resignals an exception condition.
• RESTORE DATABASE Statement
  Restores an SAP Sybase IQ database backup from one or more archive devices.
• RESUME Statement
  Resumes execution of a procedure that returns result sets.
• RETURN Statement
  Exits a function or procedure unconditionally, optionally providing a return value. Statements following RETURN are not executed.
• REVOKE CHANGE PASSWORD Statement
  Removes the ability of a user to manage passwords and administer the system privilege.
• REVOKE CONNECT Statement
  Removes a user from the database.
• REVOKE CREATE Statement
  Removes CREATE privileges on the specified dbspace from the specified user IDs.
• REVOKE EXECUTE Statement
  Removes EXECUTE permissions that were given using the GRANT statement.
• REVOKE INTEGRATED LOGIN Statement
  Removes the INTEGRATED LOGIN permissions that were given using the GRANT statement.
• REVOKE KERBEROS LOGIN Statement
  Removes KERBEROS LOGIN permissions that were given using the GRANT statement.
• REVOKE Object-Level Privilege Statement
  Removes object-level privileges that were given using the GRANT statement.
• REVOKE ROLE Statement
  Removes a users membership in a role or his or her ability to administer the role.
• REVOKE SET USER Statement
  Removes the ability for one user to impersonate another user and to administer the SET USER system privilege.
• REVOKE System Privilege Statement
  Removes specific system privileges from specific users and the right to administer the privilege.
• REVOKE USAGE ON SEQUENCE Statement
  Removes USAGE privilege on a specified sequence.
• ROLLBACK Statement
  Undoes any changes made since the last COMMIT or ROLLBACK.
• ROLLBACK TO SAVEPOINT Statement
  Cancels any changes made since a savepoint was established. Changes made prior to the savepoint are not undone; they are still pending.
• ROLLBACK TRANSACTION Statement [T-SQL]
  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.
• SAVEPOINT Statement
  Establishes a savepoint within the current transaction.
• SAVE TRANSACTION Statement [T-SQL]
  Establishes a savepoint within the current transaction.
• SELECT Statement
  Retrieves information from the database.
• SET Statement [ESQL]
  Assigns a value to an SQL variable.
• SET Statement [T-SQL]
  Sets database options in an Adaptive Server-compatible manner.
• SET CONNECTION Statement [ESQL] [Interactive SQL]
  Changes the active database connection.
• SET DESCRIPTOR Statement [ESQL]
  Describes the variables in an SQL descriptor area, and places data into the descriptor area.
• SET OPTION Statement
  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.
• SET OPTION Statement [Interactive SQL]
  Changes Interactive SQL (dbisql) options.
• SET SQLCA Statement [ESQL]
  Tells the SQL preprocessor to use an SQLCA other than the default global sqlca.
• SETUSER Statement
  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.
• SIGNAL Statement
  Lets you raise an exception condition.
• START DATABASE Statement [Interactive SQL]
  Starts a database on the specified database server.
• START ENGINE Statement [Interactive SQL]
  Starts a database server.
• START EXTERNAL ENVIRONMENT statement
  Starts an external environment.
• START JAVA Statement
  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.
• STOP DATABASE Statement [Interactive SQL]
  Stops a database on the specified database server.
• STOP ENGINE Statement [Interactive SQL]
  Stops a database server.
• STOP EXTERNAL ENVIRONMENT statement
  Stops an external environment.
• STOP JAVA Statement
  Releases resources associated with the Java VM to economize on the use of system resources.
• TRIGGER EVENT Statement
  Triggers a named event. The event may be defined for event triggers or be a scheduled event.
• TRUNCATE Statement
  Deletes all rows from a table or materialized view without deleting the table definition.
• TRUNCATE TEXT INDEX Statement
  Deletes the data in a MANUAL or an AUTO REFRESH text index.
• UNION Operation
  Combines the results of two or more select statements.
• UPDATE Statement
  Modifies existing rows of a single table, or a view that contains only one table.
• UPDATE (positioned) Statement [ESQL] [SP]
  Modifies the data at the current location of a cursor.
• VALIDATE Statement
  Validates the current database, or a single table, materialized view, or index in the IQ catalog (system) store.
• VALIDATE LDAP SERVER Statement
  Validates changes to the settings of existing LDAP server configuration objects before applying them.
• WAITFOR Statement
  Delays processing for the current connection for a specified amount of time or until a given time.
• WHENEVER Statement [ESQL]
  Specifies error handling in an Embedded SQL program.
• WHILE Statement [T-SQL]
  Provides repeated execution of a statement or compound statement.