Commands

Adaptive Server 15.7 contains new and changed commands.

New commands
Command Description
alter login

Changes the attributes of a login account

alter login profile

Changes the attributes of a login profile

alter...modify owner

Transfers the ownership of database objects from one owner to another

alter thread pool Alters a thread pool
create login

Creates a login account; specifies a password, a login profile for the account, and user-supplied parameters to be assigned to the account

create login profile

Creates a login profile with specified attributes

create thread pool Creates a user-defined thread pool
deallocate locator

Deletes a large object (LOB) stored in memory and invalidates its LOB locator

drop login

Drops a login account or list of accounts

drop login profile

Drops a login profile or list of login profiles

drop thread pool Drops a user-defined pool
merge

Transfers rows from a source table into a target table

select for update

Exclusively locks rows for subsequent update within the same transaction

truncate lob

Truncates a LOB to a specified length

Changed commands
Command Change
alter database changes
  • allows you to change the compression setting at the database level
  • alter database .. inrow_LOB_length – allows you to change the length of in-row LOB columns database-wide
  • alter database ... log off – removes unwanted portions of a database log, allowing you to shrink log space and free storage without re-creating the database
alter encryption key
  • master and dual master –indicate you are alteing a master or dual master encryption key
  • master key – indicates you are altering the encryption key with the master key
  • [no] dual_control – indicates whether the new key is encrypted using dual control.
  • for recovery – indicates the key copy will be used to recover the master key in case of a lost password
  • for automatic_startup – indicates the key copy will be used to access the master or dual master key after the server starts
  • regenerate key – replaces the raw key value for the master or dual master keys with a new raw key, and re-encrypts all column encryption keys encrypted by the master or dual master keys
alter table
  • allows you to change the compression attributes for tables, columns, and partitions.
  • alter table ... not materialized – indicates you are creating a nonmaterialized column
  • alter table ... add lob-colname – allows you to define newly added nullable LOB columns as in-row, and specify its length
  • alter table ... modify lob-colname – allows you to modify an existing LOB column from off-row to in-row
  • alter table ... modify off row | in row – specifies whether the Java-SQL column is stored separately from the row, or in storage allocated directly in the row

Concatenation operators

The + and || Transact-SQL operators accept LOB locators as expressions for a concatenation operation. The result of a concatenation operation involving one or more locators is a new LOB locator with the same datatype as that referenced by the input locator.

create database
  • compression = indicates the level of compression to be applied to newly created tables or partitions.
  • lob_compression = value – Determines the compression level for the newly created table. Selecting off means the table does not use LOB compression.
  • inrow_lob_length = value – specifies the number of bytes. The range of valid values for inrow_lob_length is 0 through the logical page size of the database.
create encryption key
  • master and dual master – indicate you are creating a master or dual master encryption key
  • passwd system_encr_passwd | master key – indicates you are using system encryption password or the master key for the password
  • [no] dual_control – indicates whether the new key is encrypted using dual control.
create table ... [ in row [(length)] | off row ]

allows you to create a compressed table

create table lets you specify that the data in a LOB column be kept in the row, instead of stored off-row.

declare cursor ... [release_locks_on_close]]

Allows you to configure the lock-releasing behavior of each cursor so that the shared locks can be released when the cursor is closed, even if the transaction is active.

drop encryption key

[dual] master – indicates you are dropping a master or dual master key

dump database ... with shrink_log

Allows you to remove any holes at the end of a database, regardless of whether the database is in a dump sequence.

like clause in a where clause

where clause accepts text and unitext LOB locators, but not image LOB locators, for the variables expression and match_string.

select into ... [in row [(length)] | off row ]

Sets or changes the in-row chracteristics for the text columns in the target table. If you do not specify length, Adaptive Server uses the configured default in-row length.

set
set adds
  • send_locator [on | off] – specifies whether Adaptive Server sends the LOB or the locator that references the LOB in a result set sent to the client.
  • cis_rpc_handling {on | off} – makes CIS the remote procedure call (RPC) handling mechanism the default mechanism for Shared Disk Cluster (SDC) handling
  • encryption passwd <char_literal> for key [dual] master – sets the password for the master or dual master key

where clause extension to support LOBs

where clauses in select, insert, update, and delete statements can include a condition for null large objects (LOBs).

See the Reference Manual: Commands.