System Changes for SAP ASE Version 16.0

SAP ASE 16.0 contains system changes.

Global Variables

New global variables:

VariableDescription
@@trigger_name
Returns the name of the trigger that is currently executing
@@tranrollback
Returns the type of rollback encountered, if any.

Configuration Parameters

New parameters:

ParameterDescription
enable utility lvl 0 scan wait
Allows you to run alter table ... add | drop partition commands while Adaptive Server runs isolation level 0 scans.
large allocation auto tune
Configures SAP ASE preallocate large amounts of memory for query execution, which reduces procedure cache contention
engine local cache percent
Allows you to modify the engine local cache as a percentage of procedure cache.
enable large chunk elc
Enables large allocation in the engine local cache.
aggressive task stealing
Sets the SAP ASE scheduler task stealing policy to aggressive.
max network peek depth
Specifies how many levels deep SAP ASE peeks into a connections operating system receive buffer for a pending cancel.
user log cache queue size
Determines whether a queueing strategy is used for logging.
threshold event monitoring
Enable or disable threshold-event recording.
threshold event max messages
Determines the number of events stores in the monThresholdEvent table.

Commands

Changed commands:

CommandDescription
alter database
Fully encrypt an existing database.
alter index
Change the compression state of future index inserts or updates using the index_compression clause.
alter table
For:
  • Index compression – change the compression state of future index inserts or updates using the index_compression clause.
  • Multiple triggers – the table owner can disable any or all of the multiple triggers defined on that table.
  • Residual data removal – automatically remove residual data following delete operations.
create archive database
Create a fully encrypted archive database.
create database
Create a fully encrypted database.
create default
Replace an object's definition using the or replace clause.
create encryption key
Create a database encryption key.
create function
Replace an object's definition using the or replace clause.
create function (SQLJ)
Replace a user-defined SQLJ function's definition using the or replace clause.
create index
Compress an index or index partition with the index_compression clause.
create procedure
Replace an object's definition using the or replace clause.
create procedure (SQLJ)
Replace a SQLJ procedure definition using the or replace clause.
create rule
Replace an object's definition using the or replace clause.
create table
For:
  • Index compression – compress indexes on a specified table using the index_compression clause.
  • Residual data removal – Create a new table that automatically removes residual data when deletions occur.
create trigger on order
For:
  • Multiple triggers – Create multiple triggers, as well as specify the order in which the triggers are fired after you execute a command.
  • or replace – replace an object's definition using create trigger using the or replace clause.
create view
Replace an object's definition using create view using the or replace clause.
drop encryption key
Delete the database encryption key from the sysencryptkeys table in the master database
drop tigger

Use to remove or replace an existing trigger, or multiple triggers.

dump database
Adds a cyclic redundancy check for accidental changes to raw data for database or transaction dumps created with compression.
kill
Adds the with force parameter if you cannot terminiate the process with the regular kill spid parameter.
load database
Adds a cyclic redundancy check for accidental changes to raw data for compressed database or transaction dumps.
select
Issuing select statements that reference only @variables, @@global variables, and constants on SAP ASE version 16.0 and later in chained mode do not start new transactions.
select into
Creates an index compressed table by selecting from an existing table.
set
For:
  • Multiple Triggers – set show_trigger_execution prints to the error log the name of the trigger as it is fired, to help you identify where any processing problems are occurring.
  • Residual data removal – set erase_residual_data {on | off} enables the ability to erase residual data.

Functions

New built-in function:

FunctionDescription
dbencryption_status
Reports on the encryption/decryption status and progress of a database.

System Stored Procedures

New system stored procedures:

ProcedureDescription
sp_confighistory
Creates the ch_events view and displays changes made to SAP ASE configuration.
sp_ dropglockpromote_ptn
Removes partition lock promotion values.
sp_droprowlockpromote_ptn
Removes row partition lock promotion threshold values from a database or table.
sp_helptrigger
Use to:
  • View a list of all the triggers for each of the DML actions against a table
  • See the orders of those triggers that specified the order clause when created
sp_jsconfigure
Configures the Job Scheduler Agent.
sp_loggging_rate
Calculates the transaction log growth rate for the specified time period.
sp_setpglockpromote_ptn
Sets page-lock promotion thresholds for partitions at the server, database, and the table.
sp_setrowlockpromote_ptn
Sets row-lock promotion thresholds for partitions at the server, database, and the table.

Changed system stored procedures:

ProcedureDescription
sp_audit
Adds these auditing options:
  • config_history – enables or disables auditing for configuration history.
  • sproc_auth – enables or disables auditing for authorization checks that are performed inside system stored procedures.
sp_chgattribute
Adds the pnt_locking table attribute, which enables and disables partition-level locking.
sp_ clusterlockusage
Output enhanced to print cluster lock usage specific to partition lock information.
sp_dboption
Removes residual data from delete operations in SAP ASE using the erase residual data parameter.
sp_depends
Lists the multiple triggers associated with the table.
sp_encryption
Supports encrypted databases
sp_familylock
Output enhanced to include the partitionid column.
sp_helpdb
Output enhanced to include information about encrypted databases
sp_lock
Output enhanced to include the partitionid column.

System Tables

New system table:

TableDescription
ch_events
Contains one row for each configuration change event. ch_events is located in the sysmgmtdb database.

Changed system tables:

TableDescription
sysattributes
There are no new columns, however the encrypted database feature introduces 43, a new class that signifies database encryption.
sysconstraints
There are no new columns, however now includes information about multiple triggers.
sysobjects
Changes what sysobjects saves for multiple triggers.

Monitoring Tables

New monitoring table:

TableDescription
monThresholdEvent
Includes one row for each event recorded by SAP ASE.

Changed monitoring tables:

TableDescription
monCachedStatement
  • Updates metrics for some columns in 5 second increments.
  • Increments the UseCount column when statement begin execution
  • Increments the value for columns that describe maximums for currently executing statements.
monDeadLock
The partitionid column displays the unique identifier for the partition.
monLocks
The partitionid column displays the unique identifier for the partition.
monOpenObjectActivity
Adds a number of columns
monOpenPartitionActivity
Adds a number of columns
monProcess
ClientDriverVersion column displays the connectivity driver used by the client program.
monRepLogActivity, monRepScannersTotalTime, and monRepSenders
Require that you enable the activate monitoring configuration parameter to start collecting monitoring data.
monRepScanners
Changes the Status column.
monRepScannersTotalTime
changes the name of the MRPBootstrapTime column to BootstrapTime.
monSysExecutionTime
(Cluster Edition only) The ExecutionTime column displays values for NetworkIO, DeviceIO, and CIPCIO.
monTables
The Description column in SAP ASE 16.0 and later supports 512 characters. Previous releases supported 255 characters.
monTableColumns
  • The Description column in SAP ASE version 16.0 and later supports 512 characters. Previous versions supported 255 characters.
  • The Label column in SAP ASE version 16.0 and later supports 150 characters. Previous versions support 50 characters.

Utilities

Changed utilities:

UtilityDescription
ddlgen
Supports encrypted databases
sybmigrate
Supports encrypted databases
sybrestore
Supports restoring an SAP ASE after a master database corruption