SQL Statement Changes

Syntax changes for SAP Sybase IQ 16.0.

Statement	Description
ALTER DATABASE	New clauses: RESTART prevents the database from automatically restarting after an upgrade. SYSTEM PROCEDURE AS DEFINER defines whether to execute system procedures that perform privileged tasks with the privileges of the invoker (the person calling the procedure) or the definer (the owner of the procedure). OFF means all system procedures execute with the privileges of the invoker. ON (default), or not specified means pre-16.0 system procedures execute with the privileges of the definer and 16.0 or later system procedures execute with the privileges of the invoker. See Reference: Statements and Options > SQL Statements > ALTER DATABASE
ALTER LDAP SERVER	New statement to modify the defined properties for the LDAP server. See Reference: Statements and Options > SQL Statements > ALTER LDAP SERVER Statement.
ALTER LOGICAL SERVER	New clauses: POLICY clause associates a logical server with a user-defined logical server policy. WITH STOP SERVER clause automatically shuts down all servers in the logical server. AUTO is now a reserved logical server name. See Reference: Statements and Options > SQL Statements > ALTER LOGICAL SERVER Statement.
ALTER LOGIN POLICY	New clauses: DEFAULT_LOGICAL_SERVER sets the target logical server context if you omit LogicalServer from the connection string. LDAP_PRIMARY_SERVER, LDAP_SECONDARY_SERVER, LDAP_AUTO_FAILBACK_PERIOD, LDAP_FAILOVER_TO_STD, and LDAP_REFRESH_ON allow the defining of SAP Sybase IQ LDAP user authentication related properties in login policies. ROOT_AUTO_LOCK_TIME (root login policy only) and AUTO_UNLOCK_TIME let you define user account management related properties in login policies. CHANGE_PASSWORD_DUAL_CONTROL requires input from two users, each granted the CHANGE PASSWORD system privilege, to change the password of another user. See Reference: Statements and Options > SQL Statements > ALTER LOGIN POLICY
ALTER LS POLICY	You can alter user-created policies to control the behavior of associated logical servers in your multiplex. Enable login redirection, specify which nodes in a logical server are available for processing queries, the placement of temporary table data, and how many connections can queue before redirection. New options DQP_ENABLED, LOGIN_REDIRECTION, REDIRECTION_WAITERS_THRESHOLD, and TEMP_DATA_IN_SHARED_TEMP added. New WITH STOP SERVER clause automatically shuts down all servers in the logical server. This statement can also alter the root ls policy to change the value of different ls policy options. See Reference: Statements and Options > SQL Statements > ALTER LS POLICY Statement.
ALTER ROLE	New statement lets you migrates a compatibility role to a user-defined system role, then automatically drops the compatibility role. See Reference: Statements and Options > SQL Statements > ALTER ROLE Statement.
ALTER TABLE	New syntax extensions enable you to either register the table with the RLV store for real-time in-memory updates, or disable RLV storage. New syntax supports changing a table owner. See Reference: Statements and Options > SQL Statements > ALTER TABLE Statement.
ALTER USER	New REFRESH_DN clause lets you clear the saved DN and timestamp for a user, which is used during SAP Sybase IQ LDAP user authentication. See Reference: Statements and Options > SQL Statements > ALTER USER.
COMMENT ON	New LDAP_SERVER clause lets you create a comment on a SAP Sybase IQ LDAP server object. See Reference: Statements and Options > SQL Statements > COMMENT.
COMMENT ON LS POLICY	New clause allows comment to document the purpose or guidelines for using new user-defined logical server policies. See Reference: Statements and Options > SQL Statements > COMMENT ON LS POLICY Statement.
CREATE DATABASE	New SYSTEM PROCEDURE AS DEFINER clause defines whether to execute system procedures that perform privileged tasks with the privileges of the invoker (the person calling the procedure) or the definer (the owner of the procedure). OFF (default), or not specified means all system procedures execute with the privileges of the invoker. ON means pre-16.0 system procedures execute with the privileges of the definer and 16.0 or later system procedures execute with the privileges of the invoker.
CREATE DBSPACE	New syntax extension for creating an RLV store dbspace. See Reference: Statements and Options > SQL Statements > CREATE DBSPACE Statement.
CREATE LDAP SERVER	New statement to create a new LDAP server for LDAP user authentication. See Reference: Statements and Options > SQL Statements > CREATE LDAP SERVER Statement.
CREATE LOGICAL SERVER	New POLICY clause associates a logical server with a user-defined logical server policy. AUTO is now a reserved logical server name. New WITH STOP SERVER clause automatically shuts down all servers in the logical server. See Reference: Statements and Options > SQL Statements > CREATE LOGICAL SERVER Statement.
CREATE LOGIN POLICY	New clauses: DEFAULT_LOGICAL_SERVER sets the target logical server context if you omit LogicalServer from the connection string. LDAP_PRIMARY_SERVER, LDAP_SECONDARY_SERVER, LDAP_AUTO_FAILBACK_PERIOD, LDAP_FAILOVER_TO_STD, and LDAP_REFRESH_ON allow the defining of SAP Sybase IQ LDAP user authentication related properties in login policies. ROOT_AUTO_LOCK_TIME (root login policy only) and AUTO_UNLOCK_TIME let you define user account management related properties in login policies. CHANGE_PASSWORD_DUAL_CONTROL requires input from two users, each granted the CHANGE PASSWORD system privilege, to change the password of another user. See Reference: Statements and Options > SQL Statements > CREATE LOGIN POLICY.
CREATE LS POLICY	New statement lets you create your own policies to control the behavior of associated logical servers in your multiplex. Enable login redirection. Specify which nodes in a logical server are available for processing queries, the placement of temporary table data, and how many connections can queue before redirection. New options DQP_ENABLED, LOGIN_REDIRECTION, REDIRECTION_WAITERS_THRESHOLD, and TEMP_DATA_IN_SHARED_TEMP added. See Reference: Statements and Options > SQL Statements > CREATE LS POLICY Statement.
CREATE ROLE	New statement lets you create a new user-defined role, extends an existing user to act as a role, or manages role administrators on a role. See Reference: Statements and Options > SQL Statements > CREATE ROLE Statement.
CREATE TABLE	New syntax enables you to register the table with the RLV store. CREATE [ GLOBAL TEMPORARY ] TABLE <`table-name`> { ENABLE \| DISABLE } RLV STORE New syntax extensions support range, hash, and hash-range partitioning schemes: `partitioning-scheme:` { `range-partitioning-scheme` \| `hash-partitioning-scheme` \| `hash-range-partitioning-scheme` } Hash partitioning maps data to partitions based on the composite value of a set of partition key columns and an internal hash function. Hash partitioning distributes data to partitions based on composite value of a set of partition key columns, which can enhance query join performance on a large table. Hash-range partitioning is a composite partitioning scheme that distributes data to partitions based on the composite value of a set of partition key columns, an internal hash function, and range sub-partition. Hash-range partitioning maps data to sub-partitions based on a range of sub-partition key values. See Reference: Statements and Options > SQL Statements > CREATE TABLE Statement.
DECLARE LOCAL TEMPORARY TABLE	New syntax enables you to register the table with the RLV store. See Reference: Statements and Options > SQL Statements > DECLARE LOCAL TEMPORARY TABLE Statement.
DROP LDAP SERVER	New statement lets you remove an LDAP server from the database after verifying that it is not in a READY or ACTIVE state. See Reference: Statements and Options > SQL Statements > DROP LDAP SERVER.
DROP LOGICAL SERVER	New WITH STOP SERVER clause automatically shuts down all servers in the logical server. See Reference: Statements and Options > SQL Statements > DROP LOGICAL SERVER
DROP LS POLICY	New statement lets you delete the policy if a user-defined policy is not currently used for any logical server. You cannot drop the root policy. See Reference: Statements and Options > SQL Statements > DROP LS POLICY Statement.
DROP ROLE	New statement lets you remove a user-defined role from the database at any time as long as all dependent roles will are left with the minimum required number of administrator users with active passwords. See Reference: Statements and Options > SQL Statements > DROP ROLE.
INSERT	New INSERT...VALUES support for (DEFAULT), DEFAULT VALUES or VALUES() clauses that insert rows with all default values. Assuming that there are 3 columns in table t2, these examples are semantically equivalent: INSERT INTO t2 values (DEFAULT, DEFAULT, DEFAULT); INSERT INTO t2 DEFAULT VALUES; INSERT INTO t2() VALUES(); INSERT...VALUES also supports multiple rows. The following example inserts 3 rows into table t1: CREATE TABLE t1(c1 varchar(30)); INSERT INTO t1 VALUES ('morning'),('afternoon'), ('evening'); SAP Sybase IQ treats all load/inserts as full-width inserts. Columns not explicitly specified on the load/insert statement, the value loaded will either be the column DEFAULT value for the column (if defined) or (if no DEFAULT value is defined). INSERT no longer supports the START ROW ID option. A LOAD or INSERT statement can still include this option, but SAP Sybase IQ will ignore the value. INSERT/SELECT from an empty table that has a data type mismatch with the target table returns an error rather than not reporting the mismatch. You can use the statement to insert data into an RLV-enabled table. See Reference: Statements and Options > SQL Statements > INSERT Statement.
LOAD TABLE	SAP Sybase IQ treats all load/inserts as full-width inserts. Columns not explicitly specified on the LOAD/INSERT statement, the value loaded will either be the DEFAULT value for the column (if defined) or NULL (if the column has no DEFAULT value). LOAD TABLE no longer supports the START ROW ID option. LOAD or INSERT statements can still include START ROW ID, but SAP Sybase IQ will ignore the value. The load engine no longer supports files in which input rows span file boundaries. A partial input row that occurs at the end of any file will now be treated according to the ON PARTIAL INPUT ROW setting. LOAD TABLE no longer supports UNLOAD FORMAT syntax. You can use the statement to load a file into an RLV-enabled table. The default value of the LOAD TABLE NOTIFY clause was 100000 prior to release 16.0. In SAP Sybase IQ 16.0 the default has changed to 0, meaning no notifications are printed by default. See Reference: Statements and Options > SQL Statements > LOAD TABLE Statement.
INSERT..SELECT	Prior to SAP Sybase IQ 16.0, attempting to use INSERT..SELECT on only NULL values when the source and target data types mismatched inserted the NULL values even when explicit conversion was required. Now SAP Sybase IQ will properly enforce that explicit conversion is required and correctly return the error `Unable to implicitly convert column <name> from data type <type>`. For example: CREATE TABLE t_int(c1 INT null); CREATE TABLE t_char(c1 CHAR(5) null); INSERT INTO t_int values (NULL); INSERT INTO t_char select * from t_int; Prior to 16.0, a NULL value would be inserted into t_char. Starting in 16.0, the required conversion error will occur. To use explicit conversion use CAST or CONVERT, for example: INSERT INTO t_char select cast(c1 as char(5)) from t_int; See Reference: Statements and Options > SQL Statements > INSERT Statement.
GRANT/REVOKE `database object permissions`	New clauses. You can grant and revoke the TRUNCATE and LOAD object permissions to users and roles. See Reference: Statements and Options > SQL Statements > GRANT Database Object Permissions. See Reference: Statements and Options > SQL Statements > REVOKE Database Object Permissions.
GRANT/REVOKE `system privilege`	New statements let you grant and revoke of specific system privileges to specific users, with or without administrative rights. See Reference: Statements and Options > SQL Statements > GRANT System Privilege Statement. See Reference: Statements and Options > SQL Statements > REVOKE System Privilege Statement.
GRANT/REVOKE CHANGE PASSWORD	New statement that lets you allow users to manage passwords for other users and administer the CHANGE PASSWORD system privilege. See Reference: Statements and Options > SQL Statements > GRANT CHANGE PASSWORD Statement. See Reference: Statements and Options > SQL Statements > REVOKE CHANGE PASSWORD Statement.
GRANT/REVOKE SET USER	New statements that allow you to grant and revoke the ability for one user to impersonate another user and administer the SET USER system privilege. See Reference: Statements and Options > SQL Statements > GRANT SET USER Statement. See Reference: Statements and Options > SQL Statements > REVOKE SET USER Statement.
GRANT/REVOKE ROLE	New statements that allow you to grant and revoke roles to users or other roles, with or without administrative rights. See Reference: Statements and Options > SQL Statements > GRANT ROLE Statement. See Reference: Statements and Options > SQL Statements > REVOKE ROLE Statement.
SELECT	New FOR JSON clause lets you execute an SQL statement against your database and return the results as a JSON document.
TRUNCATE TABLE	New syntax extension let you TRUNCATE partitions in hash partitioned and hash-range partitioned tables. See Reference: Statements and Options > SQL Statements > TRUNCATE TABLE Statement.
VALIDATE LDAP SERVER	New statement allows the validation of proposed changes to the settings of existing LDAP servers before applying them. See Reference: Statements and Options > SQL Statements > VALIDATE LDAP SERVER Statement.