Sybase IQ Administration Guide
|Chapter 2: Setting Up and Using Sybase IQ|
|Commands and Functions|
DDL (Data Definition Language) statements let you define and modify your database schema and your indexspace. DML (Data Manipulation Language) statements let you query your data. There are four types of commands in this product.
Most Transact-SQL is supported directly or via passthrough in Sybase IQ release 11.0. Some commands are sent to SQL Server for interpretation and are not interpreted locally by the Sybase IQ engine. Here is the interpretation order:
Otherwise, it is passed to the SQL Server catalog database.
Transact-SQL commands that are not supported in this release pass through the parser without error, indicating that the syntax is understood, but cannot be handled by Sybase IQ during execution. In many cases you receive an error message stating that they are not supported in this release. If you want to use these commands, you must do so through the isql command interface and SQL Server directly.
Note: Sybase IQ is integrated into the SQL Server environment. There are two sets of DDL commands that this environment recognizes: Transact-SQL and Sybase IQ commands. If you are doing an operation that involves equivalent commands from both command sets, Sybase IQ DDL commands and Transact-SQL equivalent DDL commands must be executed in an explicit order. If you do not issue the commands in the correct order, Sybase IQ issues error messages when the next USE command is issued and when you close the indexspace.
In the Sybase IQ documentation set, commands and functions supported by Sybase IQ are in UPPERCASE, to differentiate those commands handled by SQL Server. For more information about the Sybase IQ commands and functions, see Sybase IQ Language Reference and also the reference section at the back of Sybase IQ Query Guide. For more information about Transact-SQL, see your SQL Server documentation.
Most Transact-SQL commands are passed directly through to SQL Server. This section summarizes the Transact-SQL commands supported by Sybase IQ or passed on to SQL Server, and it describes some of the language differences between the two.
These Transact-SQL commands can be entered through Sybase IQ Open Server and are passed directly to SQL Server:
Sybase IQ currently does not support these Transact-SQL commands:
The Sybase IQ support for arithmetic expressions in select lists, WHERE, GROUP BY, and HAVING clauses follows the SQL-92 standard, with the following caveats:
Sybase IQ does not support the into clause, the compute clause, or the corresponding keyword of the UNION clause. Corresponding columns must have identical attributes.
The execute command is supported, although Sybase IQ does not support variables.
Sybase IQ supports the CAST, DATEPART, and SUBSTRING functions. Sybase IQ also supports these aggregate functions: COUNT, MIN, MAX, SUM, and AVG, without any of the extensions supported by Transact-SQL. The Sybase IQ support for aggregate functions in SELECT lists, GROUP BY, and HAVING clauses follows the SQL-92 standard, with the following caveat: the predicate in a WHERE clause cannot refer to an aggregate function.
Sybase IQ currently does not support the convert function. See the CAST function for the SQL-92 implementation. It also does not support any other string functions, the system functions, and text and image functions.
Sybase IQ is Transact-SQL compliant, with the following exceptions:
For all Sybase IQ language syntax, you can optionally qualify with the database.owner.name:
This syntax is SQL-92 compliant, except that Sybase IQ does not support delimited identifiers, and SQL-92 does not support case sensitivity except within delimited identifiers.
The LIKE predicate is SQL-92 compliant. LIKE does not support the Transact-SQL extensions to treatment of blanks with respect to wildcards, and extended regular expression support or the Transact-SQL extension to search for DATETIME values.
Sybase IQ does not support some, any, or all, but does support EXISTS. Sybase IQ does not support match, is true, is false, or is unknown conditions, but does support IS NULL.
The following commands are recognized by Sybase IQ. Some are unique to Sybase IQ; they are used with the Sybase IQ indexspace and are known only to the Sybase IQ parser. If you do not have Sybase IQ running and you use these commands, you will get an error from the SQL Server parser.
Indicates the indexsets used by the query expression to produce a virtual table. The FROM clause is always required in a SELECT statement or query expression.
GROUP BY clause
Groups the output of a SELECT statement by one or more columns and returns a single row of information for each group.
Restricts the groups you retrieve in a GROUP BY clause of a SELECT statement.
IQ ADD SEGMENT FOR database_name
Creates a new segment for an existing Sybase IQ indexspace across multiple operating system files to add more free space for indexspace.
IQ ALTER INDEXSET FOR table_name
Adds or drops a column from an existing indexset.
Backs up a Sybase IQ indexspace on either raw partitions or operating system files.
IQ CLOSE INDEXSPACE
Closes the current indexspace without exiting from Sybase IQ session.
IQ CREATE [UNIQUE] INDEX index_name ON table_name (column_name) indextype
Adds an index of a given indextype to a column in an indexset. If the indexset is part of a joined indexset, steps above are also performed on the joined indexset.
Only columns with Sybase IQ indexes may have data. If a column does not have a Sybase IQ index, Sybase IQ produces an error and passes the query off to an attached or associated database.
IQ CREATE INDEXSET FOR table_name
Creates an indexset and defines its columns.
IQ CREATE INDEXSPACE FOR database_name
Defines an indexspace and creates the first segment.
IQ CREATE INDEXSPACE TEMPLATE [FOR table_name]
Creates a script that in turn can be used to create an indexspace, indexsets, columns, and indexes based on an existing underlying database. This script, after appropriate editing, can be used as input to Sybase IQ through some front-end tool (like isql).
IQ CREATE JOINED INDEXSET
Defines a group of prejoined indexsets to improve the performance of queries against multiple indexsets in the same indexspace.
Checks every block in the indexspace and saves the information in the current session until the next IQ DBCC CHECKDB command is issued.
IQ DELETE FROM INDEXSET FOR table_name
Deletes rows from an indexset.
IQ DROP INDEX index_name
Deletes a column index.
IQ DROP INDEXSET FOR table_name
Deletes an indexset and its column indexes.
IQ DROP INDEXSPACE FOR database_name
Deletes an indexspace and all its contents.
IQ DROP JOINED INDEXSET joined_indexset_name
Deletes a joined indexset and its column indexes.
IQ DROP SEGMENT FOR database_name
Drops the last segment of a Sybase IQ indexspace.
IQ INSERT INTO
Adds new rows to the column indexes of an indexset.
IQ INSERT INTO JOINED INDEXSET FOR
Inserts data into joined indexset one indexset at a time.
Tells Sybase IQ to ignore next statement and pass it directly through to SQL Server.
Restores a Sybase IQ indexspace and associated catalog information (full restore) or the indexspace only (partial restore). You can also use this command to display the header information for the indexspace without performing any restore operation.
Sets Sybase IQ query processing options, session defaults, system-wide defaults.
IQ SHOW INDEXSET
Displays information about the named indexset. Information includes column names, datatypes, column length, precision, scale, whether or not column allows NULLs, and so on.
IQ SHOW INDEXSET INDEXES
Displays information about the indexes on columns in a specified indexset. Information includes index names, types of indexes, column names, and uniqueness settings.
IQ SHOW INDEXSET JOINED INDEXSETS
Displays information about joined indexsets associated with the specified indexset. Information includes the name and join relationships of each joined indexset.
IQ SHOW INDEXSPACE | INDEXSETS
Displays information about the indexspace you are using. Information includes the name of indexspace and a listing of the indexsets that are part of that indexspace. The indexset listing includes creation, last update time, and the number of columns in each indexset.
IQ SHOW INDEXSPACE INDEXES
Displays information about column indexes associated with Sybase IQ indexspace you are using. Information includes name and type of each index, indexset and columns from which they are derived, and other data such as uniqueness.
IQ SHOW INDEXSPACE JOINED INDEXSETS
Displays information about the joined indexsets associated with the Sybase IQ indexspace you are using. Information includes the name and join relationships of each joined indexset.
IQ SHOW SET VALUES
Displays an alphabetized list of IQ SET command options and their values.
IQ SHOW SIZE
Displays the size, in logical blocks, of the specified indexspace object. Object can be a column index, a joined indexset, or an indexset.
Displays information about the indexspace that you are using.
ORDER BY clause
Sorts by column the result table returned by a SELECT statement.
Retrieves rows and columns from tables in an indexspace.
Some features like compute, text and image and other datatypes, the holdlock clause, and the for browse clause are not supported in this release. If any unsupported clauses are used, the entire query is passed through to SQL Server.
Passes the shutdown command to SQL Server and exits Sybase IQ. This command can only be issued by a System Administrator.
Specifies an indexspace with which you want to work. If no indexspace exists, but the underlying database does, Sybase IQ passes the command to SQL Server.
Sets conditions for rows that are retrieved by a SELECT statement.
Use the IQ PASS command to bypass the Sybase IQ parser and send a command directly to SQL Server. Here is the syntax for the IQ PASS command:
IQ PASS rest_of_line
Any syntax following the words IQ PASS is passed directly to SQL Server.
IQ PASS sp_helpdb
IQ PASS is designed to handle those statements that Sybase IQ does not automatically recognize as passthrough statements.
Use this statement for any Sybase command, including Sybase stored procedures. The IQ PASS statement lets you process statements that are forced to have multiple lines, such as a statement that defines a stored procedure. Sybase IQ normally does not allow such statements.
WARNING! Do not use IQ PASS with a USE command.
Using the IQ PASS statement instead of making Sybase IQ determine which statements to pass through avoids ambiguity and may make statements process more rapidly in certain situations. Because SQL Server stores the execution plan for a stored procedure the first time it is run, subsequent run time is much shorter than for the equivalent set of stand-alone statements.
When you pass a stored procedure: