Sybase IQ material - Quick Ref

Contents
IQ 12.6 Commands
IQ 12.6 Datatypes
IQ 12.6 Functions
IQ 12.6 System Procedures
IQ 12.6 Catalogue Procedures
IQ 12.6 Multiplex System Procedures
IQ 12.6 ASE Compatibility System Procedures
IQ 12.6 ASE Compatibility Catalogue Procedures
IQ 12.6 Extended Procedures
IQ 12.6 General Database Options
IQ 12.6 T-SQL Compatibility Options
IQ 12.6 DBISL Options
IQ 12.6 Server Properties
IQ 12.6 Database Properties


IQ 12.6 Commands
Two styles of commenting; the same as in ASE :-
/*
a comment between delimiters on one or more lines
*/
-- a comment until the end of this line
ALLOCATE DESCRIPTOR descriptor-name
[ WITH MAX { integer | host-variable } ]
^ALTER DBSPACE dbspace-name
{ READWRITE | READONLY | RELOCATE
| SIZE dbspace-size [ KB | MB | GB | TB | PAGES ]
| ADD dbspace-size [ KB | MB | GB | TB | PAGES ] }
ALTER EVENT event-name
[ DELETE TYPE | TYPE event-type ]
{ WHERE { trigger-condition | NULL }
| { ADD | [ MODIFY ] | DELETE } SCHEDULE schedule-spec
}
[ ENABLE | DISABLE ]
[ [ MODIFY ] HANDLER compound-statement | DELETE HANDLER }
^ALTER INDEX index-name ON [ owner.]table-name
RENAME [ AS | TO ] new-name
^ALTER [ INDEX ] FOREIGN KEY role-name ON [ owner.]table-name
RENAME [ AS | TO ] new-name
ALTER PROCEDURE [ owner.]procedure-name procedure-definition
ALTER SERVER server-name
[ CLASS ’server-class’ ]
[ USING ’connection-info’ ]
[ CAPABILITY ’cap-name ’ { ON | OFF } ]
^ALTER SERVICE service-name [ TYPE 'service-type-string' ]
[ attributes ] [ AS statement ]
+ALTER TABLE [ owner.]table-name
{ ADD column-definition [ column-constraint ]...
| ADD table-constraint
| MODIFY column-definition
| MODIFY column-name [ IDENTITY ] [ DEFAULT AUTOINCREMENT ] [ NOT ]
NULL
| MODIFY column-name [ CONSTRAINT constraint-name ] CHECK NULL
| MODIFY column-name CHECK ( new-condition )
| ALTER column-name column-modification
| ALTER constraint-name CHECK ( new-condition )
| { DELETE | DROP } column-name
| { DELETE | DROP } CHECK
| { DELETE | DROP } CONSTRAINT constraint-name
| { DELETE | DROP } UNIQUE ( column-name [, ...] )
| { DELETE | DROP } PRIMARY KEY
| { DELETE | DROP } FOREIGN KEY role-name
| RENAME new-table-name
| RENAME column-name TO new-column-name
| RENAME constraint-name TO new-constraint-name
}
^ column-definition = column-name data-type [ NOT NULL ] [ IDENTITY ]
[ DEFAULT AUTOINCREMENT ]
+ column-constraint = [ CONSTRAINT constraint-name ]
{ UNIQUE
| PRIMARY KEY
| REFERENCES table-name [ ( column-name ) ] [ actions ]
| CHECK ( condition )
| IQ UNIQUE ( integer )
}
integer : Should always specify IQ UNIQUE even if more than 65,536 distinct values
(alternatively, set option MINIMIZE_STORAGE to ON)
+ table-constraint:
{ UNIQUE ( column-name [, ...] )
| PRIMARY KEY ( column-name [, ...] )
| foreign-key-constraint
| CHECK ( condition )
}
foreign-key-constraint = FOREIGN KEY [ role-name ] [ ( column-name [, ...] ) ]
REFERENCES table-name [ ( column-name [, ...] ) ] [ actions ]
actions = [ ON {UPDATE | DELETE} action ]
action = { RESTRICT }
ALTER VIEW
[ owner.]view-name [( column-name [, ... ] )]
AS select-without-order-by
[ WITH CHECK OPTION ]
+BACKUP DATABASE
[ CRC { ON | OFF } ]
[ ATTENDED { ON | OFF } ]
[ BLOCK FACTOR integer ]
[ { FULL | INCREMENTAL | INCREMENTAL SINCE FULL } ]
[ { VIRTUAL DECOUPLED | VIRTUAL ENCAPSULATED ‘shell_command’ } ]
TO archive_device [ SIZE integer ] [ STACKER integer ]
[ [ TO archive_device [ SIZE integer ] [ STACKER integer ] ] … ]
[ WITH COMMENT string ]
[ statement-label : ]
BEGIN [ [ NOT ] ATOMIC ]
[ local-declaration ; ... ]
statement-list
[ EXCEPTION [ exception-case ... ] ]
END [ statement-label ]
BEGIN PARALLEL IQ
statement-list
END PARALLEL IQ
Sybase® IQ™ 12.6 Quick Reference Guide Page 2a Sybase® IQ™ 12.6 Quick Reference Guide Page 2b
BEGIN TRAN[SACTION] [ transaction-name ]
[variable = ] CALL procedure-name ( [ expression] [, ... ] )
[variable = ] CALL procedure-name ( [ parameter-name = expression ] [, ... ] )
CASE value-expression
WHEN [ constant | NULL ] THEN statement-list
[ [ WHEN [ constant | NULL ] THEN statement-list ] ... ]
[ ELSE statement-list ]
END CASE
^Use the format above for the best performance
CASE
WHEN value-expression = constant THEN statement-list
[ [ WHEN value-expression = constant THEN statement-list ] … ]
[ELSE statement-list ]
END CASE
CHECKPOINT
CLEAR
CLOSE cursor-name
^COMMENT ON
{ COLUMN [ owner.]table-name .column-name
| EVENT event-name
| FOREIGN KEY [ owner.]table-name .role-name
| INDEX [ [ owner.]table.]index-name
| JAVA CLASS java-class-name
| JAVA JAR java-jar-name
| LOGIN integrated_login_id
| PROCEDURE [ owner.]procedure-name
| SERVICE web-service-name
| TABLE [ owner.]table-name
| USER userid
| VIEW [ owner.]view-name
}
IS comment
COMMIT [ WORK ]
COMMIT TRAN[SACTION ] [ transaction-name ]
CONFIGURE
CONNECT
[ TO engine-name ]
[ DATABASE database-name ]
[ AS connection-name ]
[ USER ] userid [ IDENTIFIED BY password ]
CONNECT USING connect-string
+CREATE DATABASE db-name
[ [ TRANSACTION ] { LOG ON [ log-file-name ]
[ MIRROR mirror-file-name ] } ]
[ CASE { RESPECT | IGNORE } ]
[ PAGE SIZE page-size ]
[ COLLATION collation-label ]
[ ENCRYPTED { ON | OFF | key-spec} ]
[ BLANK PADDING ON ]
[ JAVA { ON | OFF } ]
[ JCONNECT { ON | OFF } ]
[ PASSWORD CASE { RESPECT | IGNORE } ]
[ IQ PATH iq-file-name ]
[ IQ SIZE iq-file-size ]
[ IQ PAGE SIZE iq-page-size ]
[ BLOCK SIZE block-size ]
[ IQ RESERVE sizeMB ]
[ TEMPORARY RESERVE sizeMB ]
[ MESSAGE PATH message-file-name ]
[ TEMPORARY PATH temp-file-name ]
[ TEMPORARY SIZE temp-db-size ]
+ page-size = { 4096 | 8192 | 16384 | 32768 }
iq-page-size = { 65536 | 131072 | 262144 | 524288 }
block-size = { 4096 | 8192 | 16384 | 32768 }
^ collation-label = string
^ key-spec: [ ON ] KEY key [ ALGORITHM ’AES’ ]
+CREATE DBSPACE dbspace-name AS filename
[ { IQ STORE | IQ TEMPORARY STORE | IQ LOCAL STORE
| CATALOG STORE } ]
[ [ SIZE ] file-size ]
[ RESERVE sizeMB ]
CREATE { DOMAIN | DATATYPE } domain-name data-type [ [ NOT ] NULL ]
CREATE EVENT event-name
[ TYPE event-type
[ WHERE trigger-condition [ AND trigger-condition ], ... ]
| SCHEDULE schedule-spec, ... ]
[ ENABLE | DISABLE ]
[ AT { CONSOLIDATED | REMOTE | ALL } ]
[ HANDLER
BEGIN
schedule-statements
END
]
CREATE EXISTING TABLE [owner.]table_name
[ (column-definition, ...) ] AT ’location-string’
CREATE EXTERNLOGIN login-name TO remote-server
REMOTE LOGIN remote-user
[ IDENTIFIED BY remote-password ]
+CREATE FUNCTION [ owner.]function-name ( [ parameter, … ] )
RETURNS data-type routine-characteristics
{ compound-statement | AS tsql-compound-statement | external-name }
^ routine-characteristics = ON EXCEPTION RESUME | [ NOT ] DETERMINISTIC
^ tsql-compound-statement = sql-statement
sql-statement ...
^ external-name = EXTERNAL NAME library-call
| EXTERNAL NAME java-call LANGUAGE JAVA
^ library-call = '[operating-system:]function-name@library.dll; ...'
^ operating-system = WindowsNT | UNIX
^ java-call = '[package-name .]class-name .method-name method-signature'
^ method-signature = ( [ field-descriptor, ... ] ) return-descriptor
^ field-descriptor | return-descriptor = Z | B | S | I | J | F | D | C | V |
[descriptor | Lclass-name ] ;
CREATE [ UNIQUE ] [ index-type ] INDEX index-name
ON [ owner.]table-name
( column-name [, ...])
[ { IN | ON } dbspace-name ]
[ NOTIFY integer ]
[ DELIMITED BY ‘separators-string ‘ ]
[ LIMIT maxwordsize-integer ]
index-type = { CMP | HG | HNG | LF | WD | DATE | TIME | DTTM }
CREATE JOIN INDEX join-index-name FOR join-clause
^ join-clause = [ ( ] join-expression join-type join-expression
[ ON search-condition ] [ ) ]
^ join-expression = { table-name | join-clause }
^ join-type = [ NATURAL ] FULL [ OUTER ] JOIN
^ search-condition = [ ( ] search-expression [ AND search-expression ] [ ) ]
^ search-expression = [ ( ] [ table-name. ] column-name = [ table-name. ]
column-name [ ) ]
Sybase® IQ™ 12.6 Quick Reference Guide Page 3a Sybase® IQ™ 12.6 Quick Reference Guide Page 3b
CREATE MESSAGE message-number AS ' message-text '
CREATE PROCEDURE [ owner.]procedure-name ( [ parameter , ... ] )
{ EXTERNAL NAME library-call | [ DYNAMIC RESULT SETS integer-expression ]
EXTERNAL NAME java-call LANGUAGE JAVA | [ RESULT ( result-column , ... ) ]
[ ON EXCEPTION RESUME ]
compound-statement
| AT location-string
}
CREATE PROCEDURE [owner.]procedure_name
[ [ ( ] @parameter_name data-type [ = default ] [ OUTPUT ] [, ..] [ ) ] ]
[ WITH RECOMPILE ]
AS
statement-list
CREATE SCHEMA AUTHORIZATION userid
[ [ { create-table-statement | create-view-statement | grant-statement } ] ... ]
server-class = { ASAJDBC | ASEJDBC | ASAODBC | ASEODBC | DB2ODBC
| MSSODBC | ORAODBC | ODBC}
connection-info = { machine-name:port-number [ /dbname ] | data-source-name }
CREATE SERVER server-name CLASS ’server-class’ USING ’connection-info’
[ READ ONLY ]
^CREATE SERVICE service-name TYPE service-type-string [ attributes ]
[ AS statement ]
^ attributes = [AUTHORIZATION { ON | OFF } ] [ SECURE { ON | OFF }
] [ USER { user-name | NULL } [ ] URL [ PATH ] { ON | OFF | ELEMENTS }
] [ USING { SOAP-prefix | NULL } ]
^ service-type-string = { 'RAW ' | 'HTML ' | 'XML ' | 'SOAP ' | ' DISH ' }
+CREATE [ GLOBAL TEMPORARY ] TABLE [ owner.]table-name
( column-definition [ column-constraint ]
[, column-definition [ column-constraint ]... ]
[, table-constraint ]... )
[ { IN | ON } dbspace-name ]
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
| NOT TRANSACTIONAL]
[ AT location-string ]
location-string = { remote-server-name .[db-name ].[owner].object-name
| remote-server-name ;[db-name ];[owner];object-name }
See ALTER TABLE for column-constraint and table-constraint
CREATE VARIABLE identifier data-type
CREATE VIEW [ owner.]view-name [ ( column-name [, ...] ) ]
AS select-without-order-by
[ WITH CHECK OPTION ]
DEALLOCATE DESCRIPTOR descriptor-name :string
EXEC SQL BEGIN DECLARE SECTION;
C declarations
EXEC SQL END DECLARE SECTION;
DECLARE variable_name data-type
+DECLARE cursor-name
[ { SCROLL | NO SCROLL | DYNAMIC SCROLL } ]
CURSOR FOR { select-statement | statement-name
[ FOR {READ ONLY | UPDATE [ OF column-name-list ] } ]
| USING variable-name }
DECLARE cursor-name CURSOR
FOR select-statement
[ FOR { READ ONLY | UPDATE } ]
DECLARE LOCAL TEMPORARY TABLE table-name
( column-definition [ column-constraint ]...
[, column-definition [ column-constraint ]... ]
[, table-constraint ]... )
[ ON COMMIT { DELETE | PRESERVE } ROWS NOT TRANSACTIONAL ]
DELETE [ FROM ] [ owner.]table-name
[ FROM table-list ]
[ WHERE search-condition ]
^DELETE WHERE CURRENT OF cursor-name
DESCRIBE
[ USER TYPES ]
[ { ALL | BIND VARIABLES FOR | INPUT | OUTPUT | SELECT LIST FOR } ]
[ { LONG NAMES [ long-name-spec ] | WITH VARIABLE RESULT } ]
[ FOR ] { statement-name | CURSOR cursor-name } INTO sqlda-name
DISCONNECT [ { connection-name | CURRENT | ALL } ]
+DROP
{ DBSPACE dbspace-name
| { DATATYPE | DOMAIN } datatype-name
| EVENT event-name
| INDEX [ [owner].table-name .]index-name
| JOIN INDEX [ owner.]join-index-name
| MESSAGE message-number
| TABLE [ owner.]table-name
| VIEW [ owner.]view-name
| PROCEDURE [ owner.]procedure-name
| FUNCTION [ owner.]function-name
}
DROP CONNECTION connection-id
+DROP DATABASE db-filename [ KEY key-spec ]
DROP EXTERNLOGIN login-name TO remote-server
DROP SERVER server-name
^DROP SERVICE service-name
DROP STATEMENT [ owner.]statement-name
DROP VARIABLE identifier
EXECUTE ( string-expression )
EXECUTE statement-name
[ { USING DESCRIPTOR sqlda-name | USING host-variable-list } ]
[ { INTO DESCRIPTOR into-sqlda-name | INTO into-host-variable-list ]
[ ARRAY :nnn } ]
EXECUTE [ @return_status = ] [owner.]procedure_name
{ [ @parameter-name = ] expression | [ @parameter-name = ]
@variable [ output ] } ,...
+EXECUTE IMMEDIATE [ execute-option ] string-expression
{ EXIT | QUIT | BYE }
FETCH
{ NEXT | PRIOR | FIRST | LAST | ABSOLUTE row-count | RELATIVE row-count }
cursor-name
{ [ INTO host-variable-list ] | USING DESCRIPTOR sqlda-name
| INTO variable-list }
[ PURGE ] [ BLOCK n ] [ ARRAY fetch-count ]
INTO variable-list
IQ CACHE row-count
[ statement-label: ]
FOR for-loop-name AS cursor-name
CURSOR FOR statement
[ { FOR UPDATE | FOR READ ONLY } ]
DO statement-list
END FOR [ statement-label ]
FORWARD TO server-name { sql-statement }
FORWARD TO [ server-name ]
GET DESCRIPTOR descriptor-name
{ hostvar = COUNT } | VALUE n assignment [, ...] }
Sybase® IQ™ 12.6 Quick Reference Guide Page 4a Sybase® IQ™ 12.6 Quick Reference Guide Page 4b
label:
GOTO label
GRANT CONNECT TO userid [, ...] IDENTIFIED BY password [, ...]
GRANT
{ DBA
| GROUP
| MEMBERSHIP IN GROUP userid [, ...]
| RESOURCE
| ALL
}
TO userid [, ...]
GRANT
{ ALL [ PRIVILEGES ]
| ALTER
| DELETE
| INSERT
| REFERENCES [ ( column-name [, ...] ) ]
| SELECT [ ( column-name [, ...] ) ]
| UPDATE [ ( column-name ,... ) ]
}
ON [ owner.]table-name TO userid [, ...] [ WITH GRANT OPTION ]
GRANT EXECUTE ON [ owner.]procedure-name TO userid [, ...]
GRANT INTEGRATED LOGIN TO user_profile_name [, ...] AS USER userid
HELP [ topic ]
IF search-condition THEN statement-list
[ ELSE IF search-condition THEN statement-list ]...
[ ELSE statement-list ]
END IF
IF expression
statement
[ ELSE [ IF expression ] statement ]...
IF expression
BEGIN
statement-list
END
[ ELSE
BEGIN
statement-list
END ]
INCLUDE filename
INSERT [ INTO ] [ owner.]table-name [ ( column-name [, ...] ) ]
VALUES ( expression ... )
INSERT [ INTO ] [ owner.]table-name [ ( column-name [, ...] ) ]
insert-load-options
select-statement
+INSERT [ INTO ] [ owner.]table-name [ ( column-name [, ...] ) ]
insert-load-options
[ LOCATION 'servername.dbname '
[ ENCRYPTED PASSWORD ][ PACKETSIZE packet-size ] ]
{ select-statement }
INSTALL JAVA [ install-mode ] [ JAR jar-name ] FROM source
IQ UTILITIES { MAIN | PRIVATE }
[ INTO ] table-name
{ START MONITOR ['monitor-options'] | STOP MONITOR }
LEAVE statement-label
+LOAD [ INTO ] TABLE [ owner ].table-name
( load-specification [, ...] )
FROM { 'filename -string' | filename -variable } [, ...]
[ CHECK CONSTRAINTS { ON | OFF }
IGNORE CONSTRAINT constrainttype [, ...] ]
QUOTES OFF
ESCAPES OFF
[ FORMAT { 'ascii' | 'binary' } ]
[ DELIMITED BY 'string' ]
[ STRIP { ON | OFF } ]
[ WITH CHECKPOINT { ON | OFF } ]
[ { BLOCK FACTOR number | BLOCK SIZE number } ]
[ BYTE ORDER { NATIVE | HIGH | LOW } ]
[ LIMIT number-of-rows ]
[ NOTIFY number-of-rows ]
[ ON FILE ERROR { ROLLBACK | FINISH | CONTINUE} ]
[ PREVIEW { ON | OFF } ]
[ ROW DELIMITED BY 'delimiter-string' ]
[ SKIP number-of-rows ]
[ START ROW ID number ]
[ UNLOAD FORMAT ]
[ IGNORE CONSTRAINT constrainttype [, ...] ]
[ MESSAGE LOG ‘string’ ROW LOG ‘string’ [ ONLY LOG logwhat [, ...] ]
[ LOG DELIMITED BY ‘string’ ]
load-specification = { column-name [ column-spec ] | FILLER ( filler-type ) }
column-spec = { ASCII ( input-width ) | BINARY [ WITH NULL BYTE ]
| PREFIX { 1 | 2 | 4 } | 'delimiter-string' | DATE ( input-date-format )
| DATETIME ( input-datetime-format ) }
[ NULL ( { BLANKS | ZEROS | 'literal ', ... } ) ]
filler-type = { input-width | PREFIX { 1 | 2 | 4 } | 'delimiter-string' }
constrainttype = { CHECK integer | UNIQUE integer | NULL integer
| FOREIGN KEY integer | DATA VALUE integer | ALL integer }
logwhat = { CHECK | ALL | NULL | UNIQUE | DATA VALUE | FOREIGN KEY }
[ statement-label: ]
[ WHILE search-condition ] LOOP
statement-list
END LOOP [ statement-label ]
+MESSAGE expression, ...
[ TYPE { INFO | ACTION | WARNING | STATUS } ]
[ TO {CONSOLE | CLIENT [ FOR { CONNECTION conn_id | ALL } ] | LOG }
[ DEBUG ONLY ] ]
OPEN cursor-name
[ USING [ DESCRIPTOR { sqlda-name | host-variable [, ...] } ] ]
[ WITH HOLD ]
^OUTPUT TO filename [ APPEND ] [ VERBOSE ] [ FORMAT output-format ]
[ ESCAPE CHARACTER character ] [ DELIMITED BY string ]
[ QUOTE string [ ALL ] ] [ COLUMN WIDTHS (integer, …) ]
[ HEXADECIMAL { ON | OFF | ASIS } ] [ ENCODING encoding ]
output-format = ASCII| DBASEII | DBASEIII | EXCEL | FIXED | FOXPRO | HTML
| LOTUS | SQL | XML
encoding = string or identifier
PARAMETERS parameter1, parameter2, ...
PREPARE statement-name FROM statement
[ DESCRIBE describe-type INTO [ [ SQL ] DESCRIPTOR ] descriptor ]
[ WITH EXECUTE ]
PRINT format-string [, arg-list ]
^PUT cursor-name [ USING DESCRIPTOR sqlda-name | FROM hostvarlist ]
[ INTO { DESCRIPTOR into-sqlda-name | into-hostvar-list } ] [ ARRAY :nnn ]
RAISERROR error-number [ format-string ] [, arg-list ]
READ filename [ parameters ]
Sybase® IQ™ 12.6 Quick Reference Guide Page 5a Sybase® IQ™ 12.6 Quick Reference Guide Page 5b
RELEASE SAVEPOINT [ savepoint-name ]
REMOVE JAVA classes_to_remove
RESIGNAL [ exception-name ]
+RESTORE DATABASE ’db_file’ FROM ’archive_device’ [ FROM ’archive_device’ ] …
[ KEY key_spec ] [ RENAME dbspace_name TO ’new_dbspace_path’ ] ...
[ CATALOG ONLY ]
RESUME cursor-name
RESUME [ ALL ]
RETURN [ ( expression ) ]
REVOKE
{ CONNECT | DBA | INTEGRATED LOGIN | GROUP
| MEMBERSHIP IN GROUP userid [, ...] | RESOURCE }
FROM userid [, ...]
REVOKE
{ ALL [PRIVILEGES] | ALTER | DELETE | INSERT | REFERENCE
| SELECT [ ( column-name [, ...] ) ] | UPDATE [ ( column-name [, ...] ) ] }
ON [ owner.]table-name FROM userid [, ...]
REVOKE EXECUTE ON [ owner.]procedure-name FROM userid [, ...]
ROLLBACK [ WORK ]
ROLLBACK TO SAVEPOINT [ savepoint-name ]
SAVEPOINT [ savepoint-name ]
+SELECT [ ALL | DISTINCT ] [ FIRST | TOP number-of-rows ] select-list
[ INTO { host-variable-list | variable-list | table-name } ]
[ FROM table-list ]
[ WHERE search-condition ]
[ GROUP BY { expression [,...] | ROLLUP ( expression [,...] )
| CUBE ( expression [,...] ) } ]
[ HAVING search-condition ]
[ ORDER BY { expression | integer } [ ASC | DESC ] [, ...] ]
SET
SET identifier = expression
SET option-name option-value
SET CONNECTION [ connection-name ]
SET DESCRIPTOR descriptor-name
{ COUNT = { integer | hostvar } | VALUE n assignment [, ...] }
SET [ EXISTING ] [ TEMPORARY ] OPTION
[ userid.| PUBLIC.]option-name = [ option-value ]
SET PERMANENT
SET SQLCA sqlca
SIGNAL exception-name
+START DATABASE database-file
[ AS database-name ]
[ ON engine-name ]
[ AUTOSTOP { YES | NO } ]
[ KEY key ]
START ENGINE AS engine-name [ STARTLINE command-string ]
START JAVA
STOP DATABASE database-name [ ON engine-name ] [ UNCONDITIONALLY ]
STOP ENGINE engine-name [ UNCONDITIONALLY ]
STOP JAVA
SYNCHRONIZE JOIN INDEX [ join-index-name [, join-index-name [, … ] ] ]
TRIGGER EVENTevent-name [ ( parm = value, ... ) ]
TRUNCATE TABLE [ owner.]table-name
select-without-order-by
UNION [ ALL ] select-without-order-by
[ UNION [ ALL ] select-without-order-by ]...
[ ORDER BY integer [ ASC | DESC ] [, ...] ]
UPDATE table
SET [column-name = expression, ...
[ FROM table-expression [, … ] ]
[ WHERE search-condition ]
[ ORDER BY expression [ ASC | DESC ] ,... ]
table-expression = table-spec | table-expression join-type table-spec
[ ON condition ] | tableexpression, ...
^UPDATE table-list
SET set-item, …
WHERE CURRENT OF cursor-name
^WAITFOR { DELAY time | TIME time }
WHENEVER
{ SQLERROR | SQLWARNING | NOTFOUND }
{GOTO label | STOP | CONTINUE | C code; }
WHILE expression
statement
IQ 12.6 Datatypes
Datatypes Synonyms Range
Exact Numeric datatypes (Can’t have WD, DATE, TIME or DTTM indexes)
bigint -9223372036854775808 to 9223372036854775807
unsigned bigint 0 to 18446744073709551615
integer int -2147483648 to 2147483647
unsigned integer unsigned int 0 to 4294967295
smallint -32768 to 32767
tinyint 0 to 255
decimal [ (p, s) ] -10126 to 10126 - 1
numeric [ (p, s) ] -10126 to 10126 - 1
Approximate numeric datatypes (Can’t have CMP, HNG, WD, DATE, TIME or
DTTM indexes : HG not recommended)
double ±2.2250738585072e-308 to ±1.79769313486231e+308
float As for real
float (precision) As for real or double, depending upon precision
real ±1.175494351e-38 to ±3.402823466e+38
Money datatypes (Can’t have WD, DATE, TIME or DTTM indexes)
smallmoney numeric (10, 4) -999,999.9999 to 999,999.9999
money numeric (19, 4) -999,999,999,999,999.9999 to 999,999,999,999,999.9999
Date / time datatypes (Can’t have WD index. DATE index only on date. TIME index
only on time. DTTM index only on datetime, smalldatetime
and timestamp)
date 0001 to 9999
datetime As for timestamp
smalldatetime As for timestamp
time 00:00:00.000000 to 23:59:59.999999
timestamp 0001-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999
Sybase® IQ™ 12.6 Quick Reference Guide Page 6a Sybase® IQ™ 12.6 Quick Reference Guide Page 6b
Character datatypes (Can’t have DATE, TIME or DTTM indexes if up to 255 bytes,
only default, WD & CMP indexes possible if more than 255
bytes)
character char 32,767 bytes or fewer
character varying varchar 32,767 bytes or fewer
^uniqueidentifierstr Implemented as char (36)
^long varchar Character Large Object : Separately licensed option :
allows data with a length up to 512 TB for 128 KB page
size or 2 PB for 512 KB page size
Binary datatypes (Can’t have HNG, WD, DATE, TIME or DTTM indexes)
binary 255 bytes or fewer
varbinary 32,767 bytes or fewer (only default & CMP indexes
possible if more than 255 bytes)
^long binary Binary Large Object : Separately licensed option : allows
data with a length up to 512 TB for 128 KB page size or 2
PB for 512 KB page size
^image As for long binary
Bit datatype (Can only have default index)
bit 0, 1 or NULL
Java to SQL data type conversion
Java Type SQL Type
String CHAR
String VARCHAR
String TEXT
java.math.BigDecimal NUMERIC
Java.math.BigDecimal MONEY
Boolean BIT
Byte TINYINT
Short SMALLINT
Int INTEGER
Long BIGINT
Float REAL
Double DOUBLE
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP
java.lang.Double DOUBLE
java.lang.Float REAL
java.lang.Integer INTEGER
java.lang.Long INTEGER
SQL to Java data type conversion
SQL Type Java Type
CHAR String
VARCHAR String
NUMERIC java.math.BigDecimal
DECIMAL java.math.BigDecimal
MONEY java.math.BigDecimal
BIT boolean
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT double
DOUBLE double
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
IQ 12.6 Functions
ABS ( numeric-expr )
ACOS ( numeric-expr )
ARGN ( integer-expr, expression [ , ...] )
ASCII ( string-expr )
ASIN ( numeric-expr )
ATAN ( numeric-expr )
ATAN2 ( numeric-expr1, numeric-expr2 )
AVG ( { DISTINCT column-name | numeric-expr } )
^BIT_LENGTH ( column-name )
BYTE_LENGTH ( string-expr )
CAST ( expression AS datatype )
CEILING ( numeric-expr )
CHAR ( integer-expr )
CHAR_LENGTH ( string-expr )
CHARINDEX ( string-expr1, string-expr2 )
COALESCE ( expression, expression [ , expression ...] )
COL_LENGTH ( table-name, column-name )
COL_NAME ( table-id, column-id [ , database-id ] )
CONNECTION_PROPERTY ( { property-id | property-name } ... [ , connection-id ] )
CONVERT ( datatype, expression [ , format-style ] )
COS ( numeric-expr )
COT ( numeric-expr )
COUNT ( * )
COUNT ( { DISTINCT column-name | expression } )
DATALENGTH ( expression )
DATE ( expression )
DATEADD ( date-part, numeric-expression, date-expr )
DATEDIFF ( date-part, date-expr1, date-expr2 )
DATEFORMAT ( datetime-expr, string-expr )
DATENAME ( date-part, date-expr )
DATEPART ( date-part, date-expr )
DATETIME ( expression )
DAY ( date-expr )
DAYNAME ( date-expr )
DAYS ( date-expr )
DAYS ( date-expr, date-expr )
DAYS ( date-expr, integer-expr )
DB_ID ( [ database-name ] )
DB_NAME ( [ database-id ] )
DB_PROPERTY ( { property-id | property-name } ... [ , {database-id | database-name } ] )
DEGREES ( numeric-expr )
DENSE_RANK ()
DIFFERENCE ( string-expr1, string-expr2 )
DOW ( date-expr )
EVENT_CONDITION ( condition-name )
EVENT_CONDITION_NAME ( integer )
EVENT_PARAMETER ( context-name )
EXP ( numeric-expr )
FLOOR ( numeric-expr )
GETDATE ()
HEXTOINT ( hexadecimal-string )
HOUR ( datetime-expr )
HOURS ( datetime-expr )
HOURS ( datetime-expr, datetime-expr )
HOURS ( datetime-expr, integer-expr )
^HTTP_DECODE ( string )
^HTTP_ENCODE ( string )
^HTTP_VARIABLE ( var-name [ [ , instance ] , header-field ] )
IFNULL ( expression1, expression2 [ , expression3 ] )
INDEX_COL ( table-name, index-id, key_# [ , user-id ] )
INSERTSTR ( numeric-expr, string-expr1, string-expr2 )
INTTOHEX ( integer-expr )
^ISDATE ( string )
ISNULL ( expression, expression [ , expression ...] )
^ISNUMERIC ( string )
LCASE ( string-expr )
LEFT ( string-expr, numeric-expr )
LENGTH ( string-expr )
LOCATE ( string-expr1, string-expr2 [ , numeric-expr ] )
Sybase® IQ™ 12.6 Quick Reference Guide Page 7a Sybase® IQ™ 12.6 Quick Reference Guide Page 7b
LOG ( numeric-expr )
LOG10 ( numeric-expr )
LOWER ( string-expr )
LTRIM ( string-expr )
MAX ( { DISTINCT column-name | expression } )
MIN ( { DISTINCT column-name | expression } )
MINUTE ( datetime-expr )
MINUTES ( datetime-expr )
MINUTES ( datetime-expr, datetime-expr )
MINUTES ( datetime-expr, integer-expr )
MOD ( dividend, divisor )
MONTH ( date-expr )
MONTHNAME ( date-expr )
MONTHS ( date-expr )
MONTHS ( date-expr, date-expr )
MONTHS ( date-expr, integer-expr )
NEXT_CONNECTION ( { NULL | connection-id } )
NEXT_DATABASE ( { NULL | database-id } )
^NEXT_HTTP_HEADER header-name
^NEXT_HTTP_VARIABLE var-name
NOW ( * )
NTILE ( integer )
NULLIF ( expression1, expression2 )
NUMBER ( * )
OBJECT_ID ( object-name )
OBJECT_NAME ( object-id [ , database-id ] )
^OCTET_LENGTH ( column-name )
PATINDEX ( '%pattern%', string_expr )
PERCENT_RANK ()
PERCENTILE_CONT ( numeric-expr )
PERCENTILE_DISC ( numeric-expr )
PI ( * )
POWER ( numeric-expr1, numeric-expr2 )
PROPERTY ( { property-number | property-name } )
PROPERTY_DESCRIPTION ( { property-number | property-name } )
PROPERTY_NAME ( property-number )
PROPERTY_NUMBER ( property-name )
QUARTER ( date-expr )
RADIANS ( numeric-expr )
RAND ( [ integer-expr ] )
RANK ()
REMAINDER ( numeric-expr, numeric-expr )
REPEAT ( string-expr, numeric-expr )
REPLACE ( original-string, search-string, replace-string )
REPLICATE ( string-expr, integer-expr )
RIGHT ( string-expr, numeric-expr )
ROUND ( numeric-expr, integer-expr )
ROWID ( table-name )
RTRIM ( string-expr )
SECOND ( datetime-expr )
SECONDS ( datetime-expr )
SECONDS ( datetime-expr, datetime-expr )
SECONDS ( datetime-expr, integer-expr )
SIGN ( numeric-expr )
SIMILAR ( string-expr1, string-expr2 )
SIN ( numeric-expr )
SORTKEY ( string_expr [ collation-name ] )
SOUNDEX ( string-expr )
SPACE ( integer-expr )
SQRT ( numeric-expr )
+STDDEV ( [ ALL ] expression )
STR ( numeric_expr [ , length [ , decimal ] ] )
STRING ( string1 [ , string2, ..., string99 ] )
STUFF ( string-expr1, start, length, string-expr2 )
SUBSTRING ( string-expr, integer-expr [ , integer-expr ] )
SUM ( { DISTINCT column-name | expression } )
SUSER_ID ( [ user-name ] )
SUSER_NAME ( [ user-id ] )
TAN ( numeric-expr )
TODAY ( * )
TRIM ( string-expr )
“TRUNCATE” ( numeric-expr, integer-expr )
TRUNCNUM ( numeric-expression, integer-expression )
UCASE ( string-expr )
UPPER ( string-expr )
USER_ID ( [ user-name ] )
USER_NAME ( [ user-id ] )
+VARIANCE ( [ ALL ] expression )
WEEKS ( date-expr )
WEEKS ( date-expr, date-expr )
WEEKS ( date-expr, integer-expr )
YEAR ( date-expr )
YEARS ( date-expr )
YEARS ( date-expr, date-expr )
YEARS ( date-expr, integer-expr )
YMD ( year-num, month-num, day-num )
For all following procedures, parameters can be enclosed in ( and ) when ‘call’ used
IQ 12.6 System Procedures
sp_iqaddlogin loginname, password [, number_of_connections ]
[ , password_expiration ]
sp_iqcheckdb ’mode target [...] [ resources resource-percent ]’
sp_iqcheckoptions
sp_iqcolumn [ table_name ] [, table_owner ]
sp_iqcolumn [ table_name=’tablename ’ ] [, table_owner=’tableowner’ ]
sp_iqcommandstats [ verbosity_level | search_string ]
sp_iqconnection [ connhandle ]
sp_iqconstraint [ table-name ] [ , column-name ] [ , table-owner ]
sp_iqcontext [ connhandle ]
+sp_iqdbsize [ main | local ]
^sp_iqdbspace [ dbspace-name ]
^sp_iqdbspaceinfo [ ‘dbspace-name-pattern’ ] [ , ’local’]
sp_iqdbstatistics
sp_iqdroplogin userid
sp_iqestdbspaces db_size_in_bytes, iq_page_size,
min_#_of_bytes, max_#_of_bytes ]
sp_iqestjoin table1_name , table1_row_#, table2_name , table2_row_#,
relation , iq_page_size
sp_iqestspace table_name , #_of_rows, iq_page_size
sp_iqindex [ table_name ] [ , column_name ] [ , table_owner ]
sp_iqindex [ table_name=’tablename ’ ]
[ , column_name=’columnname ’ ] [ , table_owner=’tableowner’ ]
sp_iqindex_alt [ table_name ] [ , column_name ] [ , table_owner ]
sp_iqindex_alt [ table_name=’tablename ’ ]
[ , column_name=’columnname ’ ] [ , table_owner=’tableowner’ ]
^dbo.sp_iqindexfragmentation ‘target‘
^sp_iqindexinfo ‘{ database | local | [ table table-name | index index-name ] [...] }
[ resources resource-percent ]’
sp_iqindexsize [ [ owner.]table.]index_name
sp_iqjoinindexsize join_index_name
sp_iqlistexpiredpasswords
sp_iqlistlockedusers
sp_iqlistpasswordexpirations
sp_iqlocklogin loginname , ‘{ lock | unlock }’
sp_iqlocks [ connection, ] [ [ owner.]table_name , ] [ max_locks, ] [ sort_order ]
sp_iqmodifyadmin ‘{ [ enable | disable | user_connections |
db_connections | password_expiration | password_warning ] }’ [ , value ]
sp_iqmodifylogin ‘{ loginname | all overrides }’, ‘option’, value
sp_iqpassword caller_password, new_password [ , loginname ]
sp_iq_process_login
^sp_iqrebuildindex table_name , index_clause
^sp_iqrelocate ‘target [ maxsize nMB ] [ resources resource-percent ]’
^sp_iq_reset_identity table_name , table_owner , value
^dbo.sp_iqrowdensity ‘target ‘
+sp_iqspaceinfo [ ‘main | local | [ table table-name | index index-name ] [...] ‘]
sp_iqspaceused out mainKB unsigned bigint, out mainKBUsed unsigned bigint,
out tempKB unsigned bigint, out tempKBUsed unsigned bigint
sp_iqstatus
sp_iqtable [ table_name ] [ , table_owner ] [ , table_type ]
Sybase® IQ™ 12.6 Quick Reference Guide Page 8a Sybase® IQ™ 12.6 Quick Reference Guide Page 8b
sp_iqtable [ table_name=’tablename ’ ]
[ , ] [ table_owner=’tableowner’ ] [ , ] [ table_type=’tabletype’ ]
sp_iqtablesize [ table_owner.]table_name
sp_iqtransaction
sp_iqview [ view_name ],[view_owner ],[view_type ]
sp_iqview [view_name=’viewname ’ ] [ , ]
[ view_owner=’viewowner’ ] [ , ] [ view_type=’viewtype’ ]
IQ 12.6 Catalogue Procedures
^sa_audit_string string
sa_checkpoint_execute ’shell_commands’
^sa_conn_activity
sa_conn_info [ connection-id ]
sa_conn_properties [ connection-id ]
sa_conn_properties_by_conn [ property-name ]
sa_conn_properties_by_name [ connection-id ]
sa_db_info [ database-id ]
sa_db_properties [ database-id ]
^sa_disable_auditing_type [ 'string’ ]
^sa_enable_auditing_type [ 'string’ ]
sa_eng_properties
sa_flush_cache
^sa_make_object objtype, objname [ , owner [ , tabname ]
sa_server_option option_name , option_value
^sa_set_http_header field-name , value
^sa_set_http_option option-name , value
^sa_validate [ tbl_name ] [ , owner_name [ , check_type ]
sp_login_environment
sp_remote_columns servername [ , tablename ] [ , owner ] [ , database ]
sp_remote_exported_keys server_name , sp_name [ , sp_owner ]
[ , sp_qualifier ]
sp_remote_imported_keys server_name , sp_name [ , sp_owner ]
[ , sp_qualifier ]
^sp_remote_primary_keys server_name [ , table_name ]
[ , table_owner ] [ , table_qualifier]
+sp_remote_tables servername [, tablename ] [ , owner ] [ , table_qualifier ]
[ , with_table_type ]
sp_servercaps servername
sp_tsql_environment
IQ 12.6 Multiplex System Procedures (Read the Ref Guide before using)
dbo.sp_iq_mpx_init ()
sp_iqendmpx
dbo.sp_iqendmpx ()
“DBA”.sp_iqevbegintxn ()
sp_iqmakempx ( IN l_host_name VARCHAR (40),
IN l_server_name VARCHAR (30),
IN l_db_path VARCHAR (1024),
IN l_port_number VARCHAR (40) )
“DBA”.sp_iqmpxaddremoteusers ( IN l_server_name VARCHAR (30) default NULL,
IN qName VARCHAR (30) default NULL )
“DBA”.sp_iqmpxaliasdbspace ( IN _dbspace_name VARCHAR (128),
IN _server VARCHAR (30),
IN _path VARCHAR (255) default NULL,
IN _fromserver VARCHAR (30) default ‘ ‘,
IN _offset UNSIGNED BIGINT default NULL )
dbo.sp_iqmpxcountdbremote ()
sp_iqmpxcreatepublication ( IN l_newserver VARCHAR (255) default NULL )
sp_iqmpxcreatequeryserver ( IN l_host_name VARCHAR (40),
IN l_server_name VARCHAR (30),
IN l_db_path VARCHAR (1024),
IN l_port_number VARCHAR (40) )
sp_iqmpxdropdbspace ( IN l_dbspace_name varchar(128) )
sp_iqmpxdroppublication ()
sp_iqmpxdropqueryserver ( IN l_server_name VARCHAR(30) )
sp_iqmpxdropserverdbspaces ( IN ln_server_name varchar(30) )
sp_iqmpxdumptlvlog ()
sp_iqmpxexcludeserver ( IN _server VARCHAR(30), IN _reqStat VARCHAR(10) )
^sp_iqmpxgetconnversion ()
dbo.sp_iqmpxmakeclean ()
^sp_iqmpxpassthrough ( IN sqlcmd VARCHAR(1024) )
sp_iqmpxpostsyncqueryserver ()
sp_iqmpxprotectexec ( IN _cmd VARCHAR(1024) )
sp_iqmpxreplacewriteserver ( IN l_new_server_name VARCHAR(30) )
sp_iqmpxresetquerysubscription ( IN l_server_name VARCHAR(30) )
sp_iqmpxretryexec ( IN _cmd VARCHAR(1024), IN _msg VARCHAR(1024) )
sp_iqmpxsetpublisher ( IN l_server_name VARCHAR(30) default NULL )
sp_iqmpxstopdbremote ()
sp_iqmpxsubscribeuser ( IN _user VARCHAR(30),
IN _path LONG VARCHAR default NULL,
IN _perm VARCHAR(15) default ‘REMOTE’ )
sp_iqmpxunsubscribeuser ( IN _user VARCHAR(30) )
dbo.sp_iqmpxvalidate ( IN _show_msgs CHAR(1) DEFAULT ‘Y’ )
sp_iqmpxversionfetch ( out CatalogID unsigned bigint, out VersionID unsigned bigint,
out OAVID unsigned bigint, out ServerType char(1),
out CatalogSync char(1), out WCatalogID unsigned bigint,
out WVersionID unsigned bigint )
sp_iqmpxversioninfo ()
IQ 12.6 ASE Compatability System Procedures
sp_addgroup group-name
sp_addlogin userid, password [, defdb [, deflanguage [, fullname ] ] ]
sp_addmessage messagenum, message_text [, language ]
sp_addtype typename, datatype, [, "identity" | nulltype ]
sp_adduser login_name [, name_in_db [, group-name ] ]
sp_changegroup new-group-name, userid
sp_dboption [ dbname, optname, { true | false } ]
sp_dropgroup group-name
sp_droplogin userid
sp_dropmessage messagenumber [, language ]
sp_droptype typename
sp_dropuser userid
sp_getmessage message-num, @msg-var output [, language ]
sp_helptext ’owner.object-name ’ – Must supply owner
sp_password caller_passwd, new_passwd [, userid ]
IQ 12.6 ASE Compatability Catalogue Procedures
sp_columns [ table-name [, table-owner ] [, table-qualifier ] [, column-name ] ]
sp_fkeys [ pktable_name [, pktable-owner ] [, pktable-qualifier ] [, fktable-name ]
[, fktable_owner ] [, fktable-qualifier ] ]
sp_pkeys table-name [, table-owner ] [, table-qualifier ]
sp_special_columns table-name [, table-owner ] [, table-qualifier ] [, col-type ]
sp_sproc_columns proc-name [, proc-owner ] [, proc-qualifier ] [, column-name ]
sp_stored_procedures [ sp-name [, sp-owner ] [, sp-qualifier ] ]
sp_tables [ table-name [, table-owner ] [, table-qualifier ] [, table-type ] ]
IQ 12.6 Extended Procedures
xp_cmdshell ( command [ , ‘no_output’ ] )
xp_msver ( option )
xp_read_file ( file-name )
xp_scanf ( input-string , format-string [ , param1 … [ , param50 ] ] )
+xp_sendmail recipient, [ subject ] [ , cc_recipient ] [ , bcc_recipient ] [ , “message” ]
[ , include_file ]
xp_sprintf ( output-string, format-string [ , param1 … [ , param50 ] ] )
xp_startmail [ mail_user, mail_password ]
^xp_startsmtp smtp_sender, smtp_server [ , smtp_port ] [ , timeout ]
xp_stopmail ()
^xp_stopsmtp ()
xp_write_file ( file-name , file-contents )
Sybase® IQ™ 12.6 Quick Reference Guide Page 9a Sybase® IQ™ 12.6 Quick Reference Guide Page 9b
IQ 12.6 General Database Options ( * = change with care )
To view options from every section together, enter SET in DBISQL with nothing else
General Database Option Values Default
AGGREGATION_PREFERENCE -3 to 3 0
APPEND_LOAD ON, OFF OFF
^AUDITING ON, OFF OFF
BACKUP_EXEC_CMD n/a n/a
BIT_VECTOR_PINNABLE_CACHE_
PERCENT*
0 to 100 40
BLOCKING OFF OFF
^BT_PREFETCH_MAX_MISS 0 to 1,000 2
BT_PREFETCH_SIZE 0 to 100 10
CACHE_PARTITIONS power of 2, 0 to 64 0
CHECKPOINT_TIME number of minutes 60
CIS_ROWSET_SIZE integer 50
COMMAND_STATS ON, OFF OFF
CONVERT_HG_TO_1242 ON, OFF OFF
CONVERT_VARCHAR_TO_1242 ON, OFF OFF
COOPERATIVE_COMMIT_TIMEOUT integer 250
COOPERATIVE_COMMITS ON, OFF ON
CURSOR_WINDOW_ROWS 20 to 100,000 200
DATE_FIRST_DAY_OF_WEEK 0 to 6 0
DATE_FORMAT string ’YYYY-MM-DD’
DATE_ORDER ’YMD’, ’DMY’,
’MDY’
’YMD’
^DBCC_LOG_PROGRESS ON, OFF OFF
DBCC_PINNABLE_CACHE_
PERCENT
0 to 100 50
^DDL_OPTIONS2 0 to 3 0
^DEBUG_MESSAGES ON, OFF OFF
^DEDICATED_TASK ON, OFF OFF
^DEFAULT_LIKE_MATCH_SELECTIVITY 0 to 100 15
^DEFAULT_LIKE_RANGE_SELECTIVITY 0 to 100 15
DELAYED_COMMIT_TIMEOUT integer 500
DELAYED_COMMITS OFF OFF
DISABLE_RI_CHECK ON, OFF OFF
DISK_STRIPING ON, OFF ON
^EARLY_PREDICATE_EXECUTION ON, OFF ON
EXTENDED_JOIN_SYNTAX ON, OFF ON
^FLATTEN_SUBQUERIES ON, OFF OFF
FORCE_DROP ON, OFF OFF
FORCE_NO_SCROLL_CURSORS ON, OFF OFF
^FORCE_UPDATABLE_CURSORS ON, OFF OFF
^FPL_EXPRESSION_MEMORY_KB 0 to 20000 1024
FP_PREDICATE_WORKUNIT_PAGES integer 400
FP_PREFETCH_SIZE 0 to 100 10
GARRAY_FILL_FACTOR_PERCENT 0 to 1000 25
GARRAY_INSERT_PREFETCH_SIZE 0 to 100 3
GARRAY_RO_PREFETCH_SIZE 0 to 100 10
HASH_PINNABLE_CACHE_
PERCENT*
0 to 100 20
HASH_THRASHING_PERCENT 0 to 100 10
HG_DELETE_METHOD 0, 1, or 2 0
HG_SEARCH_RANGE integer 10
^IDENTITY_ENFORCE_UNIQUENESS ON, OFF OFF
^IDENTITY_INSERT = 'tablename' = ' '
^INDEX_ADVISOR ON, OFF OFF
INDEX_PREFERENCE -10 to 10 0
^INFER_SUBQUERY_PREDICATES ON, OFF OFF
^IN_SUBQUERY_PREFERENCE -3 to 3 0
^IQGOVERN_MAX_PRIORITY 1 to 3 2
^IQGOVERN_PRIORITY 1 to 3 2
^IQGOVERN_PRIORITY_TIME 1 to 1,000,000 secs 0 (disabled)
+IQMSG_LENGTH_MB 0 to 2047 0 (no limit)
ISOLATION_LEVEL 0, 1, 2, 3 0
General Database Option Values Default
JAVA_HEAP_SIZE integer 1,000,000
JAVA_NAMESPACE_SIZE integer 4,000,000
JOIN_EXPANSION_FACTOR 0 to 100 30
JOIN_OPTIMIZATION ON, OFF ON
JOIN_PREFERENCE -7 to 7 0
JOIN_SIMPLIFICATION_THRESHOLD 1 to 64 15
LARGE_DOUBLES_ACCUMULATOR ON, OFF OFF
LF_BITMAP_CACHE_KB 1 to 8 4
+LOAD_MEMORY_MB 0 to 2,000 0
^LOCAL_RESERVED_DBSPACE_MB integer > 0 in MB 200
LOG_CONNECT ON, OFF ON
LOG_CURSOR_OPERATIONS ON, OFF OFF
LOGIN_MODE STANDARD,
MIXED,
INTEGRATED
STANDARD
LOGIN_PROCEDURE string sp_iq_process_
login
MAIN_CACHE_MEMORY_MB 1 to 4,194,303 16
^MAIN_KB_PER_STRIPE integer > 0 in KB 1
MAIN_RESERVED_DBSPACE_MB integer > 0 in MB 200
MAX_CARTESIAN_RESULT integer 10,000,000
^MAX_CLIENT_NUMERIC_PRECISION 0 to 126 0
^MAX_CLIENT_NUMERIC_SCALE 0 to 126 0
MAX_CUBE_RESULT 0 to 250,000,000 10,000,000
MAX_CURSOR_COUNT integer 50
MAX_HASH_ROWS integer to
250,000,000
2,500,000
MAX_IQ_THREADS_PER_
CONNECTION
2 to 1,000 72
^MAX_IQ_THREADS_PER_TEAM 1 to 1,000 48
MAX_JOIN_ENUMERATION 1 to 64 15
MAX_QUERY_PARALLELISM integer <= # CPUs 24
^MAX_QUERY_TIME 0 to 2^32 – 1 0 (disabled)
+MAX_STATEMENT_COUNT integer 100
MAX_WARNINGS integer 2^64 - 1
^MIN_NLPDJ_TABLE_SIZE 1 to 4,294,967,295 10,000
MIN_PASSWORD_LENGTH integer >= 0 0 characters
^MIN_SMPDJ_OR_HPDJ_FILTERED_
SIZE
1 to 4,294,967,295 25,000
^MIN_SMPDJ_OR_HPDJ_INDIRECT_
SIZE
1 to 4,294,967,295 500,000
^MIN_SMPDJ_OR_HPDJ_TABLE_SIZE 1 to 4,294,967,295 100,000
MINIMIZE_STORAGE ON, OFF OFF
^MONITOR_OUTPUT_DIRECTORY string database
directory
NOEXEC ON, OFF OFF
NON_ANSI_NULL_VARCHAR ON, OFF OFF
NOTIFY_MODULUS integer 100,000
^ODBC_DISTINGUISH_CHAR_AND_
VARCHAR
ON, OFF OFF
^ON_CHARSET_CONVERSION_
FAILURE
string IGNORE
OS_FILE_CACHE_BUFFERING ON, OFF OFF
OUT_OF_DISK_MESSAGE_REPEAT integer 120
OUT_OF_DISK_WAIT_TIME integer 30
PARALLEL_GBH_ENABLED ON, OFF ON
^PARALLEL_GBH_MIN_ROWS_PER_
UNIT
0 to 4,294,967,295 3,000,000
PARALLEL_GBH_UNITS 0 to 100 0
PRECISION 126 126
PREFETCH ON, OFF ON
PREFETCH_BUFFER_LIMIT integer 0
PREFETCH_BUFFER_PERCENT 0 to 100 40
PREFETCH_FP_PERCENT 0 to 100 50
PREFETCH_GARRAY_PERCENT 0 to 100 60
Sybase® IQ™ 12.6 Quick Reference Guide Page 10a Sybase® IQ™ 12.6 Quick Reference Guide Page 10b
General Database Option Values Default
PREFETCH_SORT_PERCENT 0 to 100 50
^PRESERVE_SOURCE_FORMAT ON, OFF ON
QUERY_DETAIL ON, OFF OFF
QUERY_NAME string empty string
QUERY_PLAN ON, OFF ON
QUERY_PLAN_AFTER_RUN ON, OFF OFF
QUERY_PLAN_AS_HTML ON, OFF OFF
QUERY_ROWS_RETURNED_LIMIT integer 0
QUERY_TEMP_SPACE_LIMIT integer 2,000
QUERY_TIMING ON, OFF OFF
RECOVERY_TIME number of minutes 2
^RETURN_DATE_TIME_AS_STRING ON, OFF OFF
ROW_COUNT integer 0
SCALE 0 to 126 38
^SIGNIFICANTDIGITSFORDOUBLE
EQUALITY
0 to 15 0
SORT_PHASE1_HELPERS integer 3
SORT_PINNABLE_CACHE_
PERCENT*
0 to 100 20
^SUBQUERY_PLACEMENT_
PREFERENCE
-1 to 1 0
^SUPPRESS_TDS_DEBUGGING ON, OFF OFF
SWEEPER_THREADS_PERCENT 1 to 40 10
^TDS_EMPTY_STRING_IS_NULL ON, OFF OFF
+TEMP_CACHE_MEMORY_MB 1 to 4,194,303 12
^TEMP_DISK_PER_STRIPE integer > 0 in KB 1
TEMP_EXTRACT_APPEND ON, OFF OFF
TEMP_EXTRACT_BINARY ON, OFF OFF
TEMP_EXTRACT_COLUMN_
DELIMITER
string ’,’
TEMP_EXTRACT_NAME1 -
TEMP_EXTRACT_NAME8
string ’’ (empty string)
^TEMP_EXTRACT_NULL_AS_EMPTY ON, OFF OFF
TEMP_EXTRACT_NULL_AS_ZERO ON, OFF OFF
TEMP_EXTRACT_QUOTE string ’’ (empty string)
TEMP_EXTRACT_QUOTES ON, OFF OFF
TEMP_EXTRACT_QUOTES_ALL ON, OFF OFF
TEMP_EXTRACT_ROW_DELIMITER string ’’ (empty string)
TEMP_EXTRACT_SIZE1 -
TEMP_EXTRACT_SIZE8
AIX & HP-UX: 0 –
64 GB, Sun Solaris:
0 – 512 GB,
Windows: 0 – 128
GB
0
TEMP_EXTRACT_SWAP ON, OFF OFF
TEMP_KB_PER_STRIPE integer > 0 in KB 1
TEMP_RESERVED_DBSPACE_MB integer > 0 in MB 200
^TEMP_SPACE_LIMIT_CHECK ON, OFF OFF
TIME_FORMAT string ’HH:NN:ss.SSS’
TIMESTAMP_FORMAT string ’YYYY-MM-DD
HH:NN:ss.SSS’
TRIM_PARTIAL_MBC ON, OFF OFF
TRUNCATE_WITH_AUTO_COMMIT ON, OFF ON
USER_RESOURCE_RESERVATION integer 1
VIRTUAL_BACKUP ON, OFF OFF
WASH_AREA_BUFFERS_PERCENT 1 to 100 20
WAIT_FOR_COMMIT ON, OFF OFF
IQ 12.6 T-SQL Compatibility Options ( * = not supported by IQ )
T-SQL Compatibility Option Values Default
ALLOW_NULLS_BY_DEFAULT ON, OFF ON
ANSI_BLANKS*
ANSI_CLOSE_CURSORS_ON_
ROLLBACK
ON ON
ANSI_INTEGER_OVERFLOW*
ANSI_PERMISSIONS ON, OFF ON
ANSINULL ON, OFF ON
ANSI_UPDATE_CONSTRAINTS OFF, CURSORS,
STRICT
CURSORS
^ASE_BINARY_DISPLAY ON, OFF ON
AUTOMATIC_TIMESTAMP OFF OFF
CHAINED ON, OFF ON
CLOSE_ON_ENDTRANS ON ON
+CONTINUE_AFTER_RAISEERROR ON, OFF ON
CONVERSION_ERROR ON, OFF ON
DIVIDE_BY_ZERO_ERROR ON, OFF ON
ESCAPE_CHARACTER* ON ON
FIRE_TRIGGERS*
FLOAT_AS_DOUBLE ON, OFF OFF
NEAREST_CENTURY 0 to 100 50
NON_KEYWORDS Comma separated
keywords list
No keywords
turned off
ON_TSQL_ERROR STOP,
CONTINUE,
CONDITIONAL
CONDITIONAL
PERCENT_AS_COMMENT ON, OFF ON
QUERY_PLAN_ON_OPEN*
QUOTED_IDENTIFIER ON, OFF ON
RI_TRIGGER_TIME*
SQL_FLAGGER_ERROR_LEVEL E, I, F, W W
SQL_FLAGGER_WARNING_LEVEL E, I, F, W W
STRING_RTRUNCATION ON, OFF OFF
TEXTSIZE*
TSQL_HEX_CONSTANT ON, OFF OFF
TSQL_VARIABLES ON, OFF OFF
IQ 12.6 DBISQL Options ( * = not supported by IQ )
DBISQL Option Values Default
AUTO_COMMIT ON, OFF OFF
AUTO_REFETCH ON, OFF ON
BELL ON, OFF ON
COMMAND_DELIMITER string ’;’
COMMIT_ON_EXIT ON, OFF ON
^DEFAULT_ISQL_ENCODING identifier or string empty string
(use system
code page)
ECHO ON, OFF ON
HEADINGS ON, OFF ON
INPUT_FORMAT*
^ISQL_COMMAND_TIMING ON, OFF ON
^ISQL_ESCAPE_CHARACTER single character \ (backslash)
^ISQL_FIELD_SEPARATOR string , (comma)
ISQL_LOG file name ’’
^ISQL_QUOTE string ' (single
apostrophe)
NULLS ON, OFF NULL
ON_ERROR STOP, CONTINUE, PROMPT,
EXIT, NOTIFY_CONTINUE,
NOTIFY_STOP, NOTIFY_EXIT
PROMPT
^OUTPUT_FORMAT ASCII, DBASEII, DBASEIII,
EXCEL, FIXED, FOXPRO,
HTML, LOTUS, SQL, XML
ASCII
^OUTPUT_LENGTH integer 0
^OUTPUT_NULLS string 'NULL'
STATISTICS 0, 3, 4, 5, 6 3
+TRUNCATION_LENGTH integer 256
Sybase® IQ™ 12.6 Quick Reference Guide Page 11a Sybase® IQ™ 12.6 Quick Reference Guide Page 11b
IQ 12.6 Server Properties ( Use with the function “property” or use
sa_eng_properties to view all properties )
Server Property Description
ActiveReq The number of server threads that are currently
handling a request.
AvailIO Reserved
BuildChange Reserved
BuildClient Reserved
BuildProduction Undocumented
BuildReproducible Reserved
BytesReceived The number of bytes received during client/server
communications.
^BytesReceivedUncomp The number of bytes that would have been received
during client/server communications if compression
was disabled. (This value is the same as the value for
BytesReceived if compression is disabled.)
BytesSent The number of bytes sent during client/server
communications.
^BytesSentUncomp The number of bytes that would have been sent
during client/server communications if compression
was disabled. (This value is the same as the value for
BytesSent if compression is disabled.)
C2 Returns YES if the -sc option was used when the
server was started. Otherwise, returns NO.
CacheHitsEng The number of database page lookups.
^CachePinned Undocumented
CacheReadEng Undocumented
CacheReplacements The number of pages in the cache that have been
replaced.
CharSet The character set in use by the database server.
^CommandLine The command line that was used to start the server.
If the encryption key for a database was specified
using the -ek option, the key is replaced with a
constant string of asterisks in the value returned by
this property. (If you need to specify the encryption
key, you can start the database server with the -ep
option to be prompted for the key, or use the START
DATABASE statement. As well, if the database can
be autostarted, the key can be provided in the
DBKEY connection parameter.)
^CompactPlatformVer A condensed version of the PlatformVer property.
CompanyName The name of the company owning this software.
ConnsDisabled The number of connections disabled.
^ConsoleLogFile Returns the name of the file where messages from
the database server window are logged if the –o
option was specified, otherwise returns an empty
string.
CurrentCacheSize The current cache size, in kilobytes.
DefaultCollation For IQ databases, a reasonable alternative to the
default collation. ISO_BINENG is the default collation
for all IQ databases created as of version 12.4.2. This
behavior differs from Adaptive Server Anywhere,
where the DefaultCollation server property shows the
collation that would be used for new databases, if
none is explicitly specified.
DiskReadEng Undocumented
^FipsMode Returns YES if the -fips option was specified when
the database server was started, and NO otherwise.
Not output by sa_eng_properties.
FreeBuffers The number of available network buffers.
^FunctionName Undocumented
^IdleTimeout The default idle timeout.
^IsFipsAvailable Returns YES if the FIPS DLL is installed, and NO
otherwise. Not output by sa_eng_properties.
^IsIQ Returns YES if the server is an IQ server.
Server Property Description
^IsJavaAvailable Returns YES if the JavaVM is installed, and NO if the
JavaVM is not installed. This property only indicates if
the Java VM is available, not whether it is currently
being used.
IsNetworkServer Returns YES if connected to a network database
server, and NO if connected to a personal database
server.
IsRuntimeServer Returns YES if connected to the limited desktop
runtime database server, and NO otherwise.
JavaGlobFix Java VM global fixed size.
^JavaObjectsEnabled Undocumented
Language The locale language for the server.
LegalCopyright The copyright string for the software.
LegalTrademarks Trademark information for the software.
LicenseCount The number of licensed seats or processors.
LicensedCompany The name of the licensed company.
LicensedUser The name of the licensed user.
^LicensesInUse The number of concurrent users currently connected
to the network server, as determined by the number
of unique client network addresses connected to the
server.
LicenseType The license type. Can be networked seat (per-seat)
or cpu-based.
LivenessTimeout The client liveness timeout default.
LockedHeapPages The number of heap pages locked in the cache.
^MachineName The name or IP address of the computer running a
database server.
MainHeapBytes The number of bytes used for global server data
structures.
MainHeapPages The number of pages used for global server data
structures.
^MapPhysicalMemoryEng Undocumented
MaxCacheSize The maximum allowed cache size, in kilobytes.
MaxMessage The current maximum line number that can be
retrieved from the server’s message window. This
represents the most recent message displayed in the
server’s message window.
+Message, linenumber A line from the server’s message window, prefixed by
the date and time the message appeared. The
second parameter specifies the line number. The
value returned by PROPERTY ( "message" ) is the
first line of output that was written to the Server
Messages window. Calling PROPERTY ( "message",
i ) returns the i-th line of server output (with zero
being the first line). The buffer is finite, so as
messages are generated, the first lines are dropped
and may no longer be available in memory. In this
case, NULL is returned.
^MessageText,
linenumber
The text associated with the specified line number in
the server’s message window, without a date and
time prefix. The second parameter specifies the line
number.
^MessageTime,
linenumber
The date and time associated with the specified line
number in the server’s message window. The second
parameter specifies the line number.
^MessageWindowSize The maximum number of lines that can be retrieved
from the server’s message window.
MinCacheSize The minimum allowed cache size, in kilobytes.
MultiPacketsReceived The number of multi-packet deliveries received during
client/server communications.
MultiPacketsSent The number of multi-packet deliveries sent during
client/server communications.
Name The name of the server.
Sybase® IQ™ 12.6 Quick Reference Guide Page 12a Sybase® IQ™ 12.6 Quick Reference Guide Page 12b
Server Property Description
^NativeProcessor
Architecture
On platforms where a processor can be emulated
(such as X86 on Win64), returns a string that
identifies the native processor type. In all other cases,
it returns the same value as PROPERTY (
‘ProcessorArchitecture’ ). Values can include : 32-
bit Windows (not CE) - X86 ; NetWare - X86 ; Intel
Solaris - X86 ; CE - SH3, SH4, MIPS or ARM ; 64-bit
Windows - IA64 or AMD64 64-bit ; UNIX - IA64 or
AMD64 ; Solaris – SPARC ; AIX – PPC ; MAC OS –
PPC ; HP - PA_RISC ; DEC UNIX – ALPHA ; Linux -
X86, SPARC, IA64
NumProcessorsAvail The number of processors on the server.
NumProcessorsMax The maximum number of processors used. Normally
this should be 2 for dbeng.exe and 0 for dbsrv.exe.
^OmniIdentifier Undocumented
PacketsReceived The number of client/server communication packets
received.
^PacketsReceived
Uncomp
The number of packets that would have been
received during client/server communications if
compression was disabled. (This value is the same
as the value for PacketsReceived if compression is
disabled.)
PacketsSent The number of client/server communication packets
sent.
^PacketsSentUncomp The number of packets that would have been sent
during client/server communications if compression
was disabled. (This value is the same as the value for
PacketsSent if compression is disabled.)
PageSize The size of the database server cache pages. This
can be set using the -gp option, otherwise, it is the
maximum database page size of the databases
specified on the command line.
PeakCacheSize The largest value the cache has reached in the
current session, in kilobytes.
Platform The operating system on which the software is
running. For example, if you are running on Windows
2000, this property returns Windows2000.
^PlatformVer The operating system on which the software is
running, including build numbers, service packs, etc.
For example, it could return Windows 2000 Build
2195 Service Pack 3.
ProcessCPU CPU usage statistics for the server process. Values
are in seconds. This property is supported on
Windows NT/2000/XP, Windows 95/98/Me, and
UNIX. This property is not supported on Windows CE
or NetWare.
ProcessCPUSystem Process CPU system usage. Values are in seconds.
This property is supported on Windows NT/2000/XP,
Windows 95/98/Me, and UNIX. This property is not
supported on Windows CE or Net-Ware.
ProcessCPUUser Process CPU user usage. Values are in seconds.
This property is supported on Windows NT/2000/XP,
Windows 95/98/Me, and UNIX. This property is not
supported on Windows CE or Net-Ware.
^ProcessorArchitecture A string that identifies the processor type. Values can
include : 32-bit Windows (not CE) - X86 ; NetWare -
X86 ; Intel Solaris - X86 ; CE - SH3, SH4, or ARM ;
64-bit Windows - IA64 or AMD64 64-bit ; UNIX - IA64
or AMD64 ; Solaris – SPARC ; AIX – PPC ; MAC OS
– PPC ; HP - PA_RISC ; DEC UNIX - ALPHA
ProductName The name of the software.
ProductVersion The version of the software being run.
^ProfileFilterConn Returns the ID of the connection being monitored if
procedure profiling for a specific connection is turned
on. Otherwise, returns an empty string. You control
procedure profiling by user with the sa_server_option
procedure. Not output by sa_eng_properties
Server Property Description
^ProfileFilterUser Returns the name of the user being monitored if
procedure profiling for a specific user is turned on.
Otherwise, returns an empty string. You control
procedure profiling by user with the sa_server_-
option procedure. Not output by sa_eng_properties
QuittingTime Shutdown time for the server. If none is specified, the
value is none.
RememberLastStatement Returns ON if the server is recording the last
statement prepared by each connection, and OFF
otherwise.
RemoteputWait The number of times the communication link has had
to wait because it does not have buffers available to
send information. This statistic is collected for
NetBIOS and IPX protocols only.
Req The number of times the server has been entered to
allow it to handle a new request or continue
processing an existing request.
^RequestFilterConn Undocumented
^RequestFilterDB Undocumented
RequestLogFile The name of the request logging file. An empty string
is returned if there is no level logging.
+RequestLogging ALL, SQL, or NONE.
^RequestLogNumFiles The number of request log files being kept. Not
output by sa_eng_properties
^RequestLogMaxSize Undocumented
SendFail The number of times that the underlying
communications protocols have failed to send a
packet.
^StartTime The date/time that the server started.
^Tempdir The directory in which temporary files are stored by
the server.
^Threads Undocumented
^TimeZoneAdjustment The number of minutes that must be added to the
Coordinated Universal Time (UTC) to display time
local to the server.
TotalBuffers The total number of network buffers.
UnschReq The number of requests that are currently queued up
waiting for an available server thread.



IQ 12.6 Database Properties ( Use with the function “db_property” or use
sa_db_properties to view all database properties )
Database Property Description
Alias The database name.
^AuditingTypes Undocumented
BlankPadding The status of the blank padding feature. Returns ON if
the database has blank padding enabled.
^BlobArenas The status of the BlobArenas feature. Returns ON if the
database stores extension (BLOB) pages separately
from table pages for the database.
CacheHits The number of database page lookups satisfied by
finding the page in the cache.
CacheRead The number of database pages that have been looked
up in the cache.
CacheReadIndInt The number of index internal-node pages that have
been read from the cache.
CacheReadIndLeaf The number of index leaf pages that have been read
from the cache.
CacheReadTable The number of table pages that have been read from
the cache.
^Capabilities The capability bits enabled for the database. This
property is primarily for use by technical support.
CaseSensitive The status of the case sensitivity feature. Returns ON if
the database is case sensitive.
^CaseSensitive
Passwords
The status of password case sensitivity. In versions
9.0.0 and later, password case sensitivity is
independent of database case sensitivity. Returns ON if
database passwords are case sensitive.
Sybase® IQ™ 12.6 Quick Reference Guide Page 13a Sybase® IQ™ 12.6 Quick Reference Guide Page 13b
Database Property Description
CharSet The character set of the database.
^CheckpointLogBitmap
PagesWritten
Undocumented
^CheckpointLogBitmap
Size
Undocumented
^CheckpointLogCommit
ToDisk
Undocumented
^CheckpointLogPage
InUse
Undocumented
^CheckpointLogPages
Relocated
Undocumented
^CheckpointLogPages
Written
Undocumented
^CheckpointLogSave
Preimage
Undocumented
^CheckpointLogSize Undocumented
^CheckpointLogWrites Undocumented
CheckpointUrgency The time that has elapsed since the last checkpoint, as
a percentage of the checkpoint time setting of the
database.
^Checksum Returns ON if database page checksums are enabled
for the database.
Chkpt The number of checkpoints that have been performed.
ChkptFlush The number of ranges of adjacent pages written out
during a checkpoint.
ChkptPage The number of transaction log checkpoints.
^ClusteredIndexes Undocumented
Collation Undocumented
CommitFile The number of times the server has forced a flush of
the disk cache. On Windows and NetWare platforms,
the disk cache does not need to be flushed if
unbuffered (direct) IO is used.
^CompressedBTrees Returns ON if Compressed B-tree indexes are
supported.
Compression The compression status of the database. Returns either
ON (meaning the database is compressed) or OFF. If a
write file is created on a compressed database, the
write file is NOT compressed. Starting a write file
created on a compressed database and selecting
db_property (‘compression’), returns OFF.
ConnCount The number of connections to the database.
^CurrentRedoPos The current offset in the transaction log file where the
next database operation is to be logged.
CurrIO The current number of file I/Os that were issued by the
server but have not yet completed.
CurrRead The current number of file reads that were issued by
the server but have not yet completed.
CurrWrite The current number of file writes that were issued by
the server but have not yet completed.
^DBFileFragments The number of database file fragments. This property is
supported on Windows NT/2000/XP.
DiskRead The number of pages that have been read from disk.
DiskReadIndInt The number of index internal-node pages that have
been read from disk.
DiskReadIndLeaf The number of index leaf pages that have been read
from disk.
DiskReadTable The number of table pages that have been read from
disk.
DiskWrite The number of modified pages that have been written
to disk.
Database Property Description
^DriveType dbspace The drive on which the database file is located. Returns
CD, FIXED, RAMDISK, REMOTE, REMOVABLE, and
UNKNOWN. On UNIX, depending on the version of
UNIX and the type of drive, it may not be possible to
determining the drive type. In these cases “UNKNOWN”
is returned. When used with db_extended_property,
you can specify which dbspace you want the size for.
dbspace can be either the name of the dbspace or the
file_id of the dbspace. Leaving dbspace unspecified or
using system both refer to the system dbspace. If the
specified dbspace does not exist, the property function
returns NULL. If the name of a dbspace is specified and
the ID of a database that is not the database of the
current connection is also specified, the function also
returns NULL.
Encryption The type of encryption applied to the database. Returns
None, Simple, or AES.
^ExprCacheAbandons Undocumented
^ExprCacheDropsTo
ReadOnly
Undocumented
^ExprCacheEvicts Undocumented
^ExprCacheHits Undocumented
^ExprCacheInserts Undocumented
^ExprCacheLookups Undocumented
^ExprCacheResumes
OfReadWrite
Undocumented
^ExprCacheStarts Undocumented
ExtendDB The number of pages by which the database file has
been extended.
ExtendTempWrite The number of pages by which temporary files have
been extended.
File The file name of the database root file, including path.
^FileSize dbspace When used with db_property, this property returns the
file size of the system dbspace, in pages. When used
with db_extended_property, you can specify which
dbspace you want the size for. dbspace can be either
the name of the dbspace, the file_id of the dbspace, or
temporary to refer to the temporary dbspace. You can
also specify translog to return the size of the log file.
Finally, you can specify writefile to refer to the write file.
When using a write file, FileSize on a dbspace returns
the amount of space in the virtual dbspace, represented
by the underlying dbspace plus the modifications to that
dbspace that have been stored in the write file. Leaving
the dbspace unspecified, or using system, both refer to
the system dbspace. If the specified dbspace does not
exist, the property function returns NULL. If the name of
a dbspace is specified and an id or name of a database
which is not the database of the current connection is
also specified, the function also returns NULL.
FileVersion The version of the database file. This does not
correspond to a software release version.
^FreePageBitMaps Returns ON if free database pages are managed
via bitmaps.
Sybase® IQ™ 12.6 Quick Reference Guide Page 14a Sybase® IQ™ 12.6 Quick Reference Guide Page 14b
Database Property Description
^FreePages dbspace FreePages is only supported on databases created
with version 8.0.0 or later. When used with
db_property, this property returns the number of free
pages in the system dbspace. When used with
db_extended_property, you can specify which
dbspace you want the number of free pages for.
dbspace can be either the name of the dbspace, the
file_id of the dbspace, or temporary to refer to the
temporary dbspace. You can also specify translog to
return the number of free pages in the log file. Finally,
you can specify writefile to refer to the write file. When
using a write file, FreePages on a dbspace returns the
number of free pages in the virtual dbspace,
represented by the underlying dbspace plus the
modifications to that dbspace that have been stored in
the write file. Leaving the dbspace unspecified, or using
system both refer to the system dbspace. If the
specified dbspace does not exist, the property function
returns null. If the name of a dbspace is specified and
an id or name of a database which is not the database
of the current connection is also specified, the function
also returns null.
FullCompare The number of comparisons that have been performed
beyond the hash value in an index.
GetData Undocumented
GlobalDBId The value of the GLOBAL_DATABASE_ID option used
to generate unique primary key values in a replication
environment.
^HashForcedPartitions Undocumented
^HashRowsFiltered Undocumented
^HashRowsPartitioned Undocumented
^HashWorkTables Undocumented
^HistogramHashFix Undocumented
^Histograms Returns ON if optimizer statistics are maintained
as histograms.
IdleCheck The number of times that the server’s idle thread has
become active to do idle writes, idle checkpoints, and
so on.
IdleChkpt The number of checkpoints completed by the server’s
idle thread. An idle checkpoint occurs whenever the idle
thread writes out the last dirty page in the cache.
IdleChkTime The number of 100ths of a second spent checkpointing
during idle IO.
IdleWrite The number of disk writes that have been issued by the
server’s idle thread.
IndAdd The number of entries that have been added to
indexes.
^IndexStatistics Undocumented
IndLookup The number of entries that have been looked up in
indexes.
IOToRecover The estimated number of I/O operations required to
recover the database.
IQStore ON for all IQ databases. Reserved
JavaHeapSize Heap size per Java VM.
JavaNSSize Java VM Namespace size.
^JDKVersion The Java runtime library version used by this database.
Language Returns a comma-separated list of languages known to
be supported by the database collation. The languages
are in two-letter ISO format. If the language is not
known (usually a custom collation), the return value is
NULL.
^LargeProcedureIDs Returns ON if 32-bit stored procedure IDs are
supported for the database.
LockTablePages The number of pages used to store lock information.
^LogFileFragments The number of log file fragments. This property is
supported on Windows NT/2000/XP.
Database Property Description
LogFreeCommit The number of Redo Free Commits. A "Redo Free
Commit" occurs when a commit of the transaction log is
requested but the log has already been written (so the
commit was done for "free").
LogName The file name of the transaction log, including path.
LogWrite The number of pages that have been written to the
transaction log.
LTMGeneration The generation number of the LTM or Replication
Agent. This property is primarily for use by technical
support.
LTMTrunc The minimal confirmed log offset for the Replication
Agent.
MapPages The number of map pages used for accessing the lock
table, frequency table, and table layout.
MaxIO The maximum value that CurrIO has reached.
MaxRead The maximum value that CurrRead has reached.
MaxWrite The maximum value that CurrWrite has reached.
MultiByteCharSet Returns ON if the database uses a multi-byte character
set.
Name The database name (identical to alias).
^NamedConstraints Undocumented
PageRelocations The number of relocatable heap pages that have been
read from the temporary file.
PageSize The page size of the Catalog Store, in bytes.
^PreserveSource Returns ON if the database preserves the source for
procedures and views.
ProcedurePages The number of relocatable heap pages that have been
used for procedures.
^ProcedureProfiling Returns ON if procedure profiling is turned on for the
database.
^QueryBypassed The number of requests optimized by the optimizer
bypass.
^QueryCachedPlans The number of cached execution plans across all
connections.
^QueryCachePages The number of pages used to cache execution plans.
^QueryJHToJNLOpt
Used
Undocumented
^QueryLowMemory
Strategy
The number of times the server changed its execution
plan during execution as a result of low memory
conditions. The strategy can change because less
memory is available than the optimizer estimated, or
because the execution plan required more memory
than the optimizer estimated.
^QueryOptimized The number of requests fully optimized.
^QueryBypassed The number of requests reused from the plan cache.
^QueryRowsBuffer
Fetch
Undocumented
^QueryRows
Materialized
Undocumented
ReadOnly Returns ON if the database is being run in readonly
mode.
RecoveryUrgency An estimate of the amount of time required to recover
the database.
^RecursiveIterations Undocumented
^RecursiveIterations
Hash
Undocumented
^RecursiveIterations
Nested
Undocumented
^RecursiveJNLMisses Undocumented
^RecursiveJNLProbes Undocumented
RelocatableHeapPages The number of pages used for relocatable heaps
(cursors, statements, procedures, triggers, views, etc.).
RemoteTrunc The minimal confirmed log offset for the SQL Remote
Message Agent.
RollbackLogPages The number of pages in the rollback log.
^SeparateCheckpoint
Log
Returns ON if the checkpoint log for the database is
maintained at the end of the SYSTEM dbspace.
Sybase® IQ™ 12.6 Quick Reference Guide Page 15a Sybase® IQ™ 12.6 Quick Reference Guide Page 15b
Database Property Description
^SeparateForeignKeys Returns ON if primary and foreign keys are stored
separately.
^SortMergePasses Undocumented
^SortRowsMaterialized Undocumented
^SortRunsWritten Undocumented
^SortSortedRuns Undocumented
^SortWorkTables Undocumented
^StringHistogramsFix Undocumented
SyncTrunc The minimal confirmed log offset for the MobiLink client
dbmlsync executable.
^TableBitMaps Returns ON if the database supports table bitmaps.
^TablesQualTriggers Undocumented
^TempFileName The file name of the database temporary file, including
path.
^TempTablePages The number of pages in the temporary file used for
temporary tables.
^TransactionsSpan
Logs
Returns ON if transactions can span multiple log files.
TriggerPages The number of relocatable heap pages used for
triggers.
^VariableHashSize Returns ON if the hash size can be specified for B-tree
indexes.
ViewPages The number of relocatable heap pages used for views.