SAP HANA Database 2.0 SPS 06 - introduces new and changed features for the SAP HANA SQL and System Views Reference
SQL Statements (New and Changed)
- ALTER DATABASE Statement (Changed)
- You can now remove all installed licenses on tenant databases from the system database using this statement. ALTER DATABASE Statement (Tenant Database Management)
- ALTER SYSTEM CLIENTPKI { UPDATE / DROP } Statement (New)
- You can now manage CLIENTPKI certificates and authorities. ALTER SYSTEM CLIENTPKI { UPDATE / DROP } (System Management)
- ALTER SYSTEM REMOVE SQL PLAN CACHE ENTRY Statement (Changed)
- The statement now supported the WHERE clause. ALTER SYSTEM REMOVE SQL PLAN CACHE ENTRY Statement (System Management)
- ALTER TABLE Statement (Changed)
You can now add a primary key using an existing unique index. ALTER TABLE Statement (Data Definition)
- You can now move multiple partitions in parallel and online. Non-heterogeneous Alter Partition Clauses, Heterogeneous Alter Partition Clauses
- ALTER USER Statement (Changed)
- You can now add multiple mappings between a JWT or SAML provider and a user. When removing these mappings, you can specify an individual mapping or remove all mappings between the user and provider. ALTER USER Statement (Access Control)
- BACKUP DATA Statement (Changed)
- You can now use the COMPRESSED option to create a backup using native SAP HANA compression. BACKUP DATA Statement (Backup and Recovery)
- BACKUP ENCRYPTION ROOT KEYS Statement (Changed)
- You must now back up all keys when using a local secure store with an active key management system, to ensure that a full LSS backup is available in the required single-file format. BACKUP ENCRYPTION ROOT KEYS Statement (Backup and Recovery)
- CONNECT Statement (Changed)
You can now connect using a JWT token. CONNECT Statement (Session Management)
- CREATE/ALTER JWT PROVIDER Statements (Changed)
- The JWT provider now checks for additional claims during authentication. It also allows you to set the XS_APPLICATIONUSER session variable during login. To support this functionality, PRIORITY has been added to the JWT_PROVIDERS System View and the JWT_PROVIDER_CLAIMS system view has be introduced to view claims defined for a JWT provider. CREATE JWT PROVIDER Statement (Access Control), ALTER JWT PROVIDER Statement (Access Control), JWT_PROVIDERS System View, JWT_PROVIDER_CLAIMS System View
- CREATE / ALTER TABLE Statement (Changed)
- You can now create system-versioned tables with transactional system-time.
- You can now group table options for replica tables.
- Dynamic interval now works with TINYINT, SMALLINT, INT, and BIGINT columns.
- You can now perform primary key checks on heterogeneous partitions.
CREATE TABLE Statement (Data Definition), Heterogeneous Create Partition Clauses, ALTER TABLE Statement (Data Definition), Heterogeneous Alter Partition Clauses
- CREATE / ALTER USERGROUP Statement (Changed)
The clause ENABLE / DISABLE CLIENT CONNECT allows you to restrict the ability for members of a usergroup to connect to the database. ALTER USERGROUP Statement (Access Control), CREATE USERGROUP Statement (Access Control)
- CREATE / ALTER WORKLOAD CLASS Statements (Changed)
You can now specify a hierarchy for workload classes to inherit TOTAL STATEMENT MEMORY LIMIT from a parent workload class.
New admission control queue and reject threshold properties have been implemented.- ADMISSION CONTROL QUEUE CPU THRESHOLD
- ADMISSION CONTROL QUEUE MEMORY THRESHOLD
- ADMISSION CONTROL REJECT CPU THRESHOLD
- ADMISSION CONTROL REJECT MEMORY THRESHOLD
Columns were added to the WORKLOAD_CLASSES, M_WORKLOAD_CLASS_STATISTICS, M_ADMISSION_CONTROL_QUEUES, M_ADMISSION_CONTROL_EVENTS, and M_LOAD_HISTORY_SERVICE views for these properties.
- CREATE DATABASE Statement (Changed)
- This statement now supports the key management configuration ID parameter when creating a database. CREATE DATABASE Statement (Tenant Database Management)
- CREATE / DROP INDEX Statement (Changed)
- The ONLINE clause now supports non-replicated, non-history tables when creating and dropping a non-unique index on non-paged base columns. CREATE INDEX Statement (Data Definition), DROP INDEX Statement (Data Definition)
- CREATE/DROP PUBLIC KEYS Statements (New)
You can now manage public keys for use with JWT authentication. CREATE PUBLIC KEY Statement (System Management), DROP PUBLIC KEY Statement (System Management), ALTER PSE Statement (System Management).
Two new views, PUBLIC_KEYS and PSE_PUBLIC_KEYS have been created to support this management. PUBLIC_KEYS System View, PSE_PUBLIC_KEYS System View
- CREATE TABLE (Changed)
The clause WITH INDEX <defined_index_name_list> has been added to the CREATE TABLE LIKE clause. For each specified index on the source table, a matching index is created on the target table. CREATE TABLE Statement (Data Definition)
- CREATE / ALTER TABLE Statement (Changed)
- The DYNAMIC clause for the OTHERS partition has been extended to include a time interval property. The property lets you specify the interval between the last and the next range partition created dynamically. CREATE TABLE Statement (Data Definition), ALTER TABLE Statement (Data Definition)
- CREATE / ALTER / DROP X509 PROVIDER Statements (New)
This syntax allows you to manage X.509 providers. CREATE X509 PROVIDER (Access Control), ALTER X509 PROVIDER (Access Control), DROP X509 PROVIDER (Access Control)
Two new system views, X509_PROVIDERS and X509_PROVIDER_RULES, have been added. The X509_USER_MAPPINGS system view has an additional column, X509_PROVIDER_NAME.
- DELETE Statement (Changed)
- Rows that are fully contained within a given time period now be deleted. DELETE Statement (Data Manipulation)
- RECOVER ENCRYPTION ROOT KEYS Statement (Changed)
- You can now specify AND SETTINGS to recover a full LSS backup.
You can now use SECURE STORE SECOND ACCESS for recovery if LSS is used with an active key management system that can no longer be reached.
RECOVER ENCRYPTION ROOT KEYS Statement (Backup and Recovery)
- SELECT Statement (Changed)
You can now specify columns to be protected against concurrent updates in a SELECT statement. SELECT Statement (Data Manipulation)
- TRUNCATE TABLE Statement (Changed)
- You can now truncate specific partitions of a table. TRUNCATE TABLE Statement (Data Manipulation)
SQL Functions (New and Changed)
- ABAP_DF16RAW_TO_SMALLDECIMAL / ABAP_DF34RAW_TO_DECIMAL Function (New)
- Two new functions have been introduced to from ABAP decfloat type (DF16_RAW and DF34_RAW) to HANA decfloat type (smalldecimal and decimal). ABAP_DF16RAW_TO_SMALLDECIMAL Function (String), ABAP_DF34RAW_TO_DECIMAL Function (String)
- CAST Function (Changed)
- When you cast a parameter as a data type, the parameter is now bound as that data type. Previously the parameter was bound as a string and converted. CAST Function (Data Type Conversion)
- CURRENT_TIMESTAMP and CURRENT_UTCTIMESTAMP Function (Changed)
You can now specify the precision (0-7) of the number of sub-seconds to display. CURRENT_TIMESTAMP Function (Datetime)CURRENT_UTCTIMESTAMP Function (Datetime)
- JSON_TABLE Function (Changed)
- This function now supports different JSON path expressions depending on their location in grammar and corresponding error types. JSON_TABLE Function (JSON)
- ENCRYPTION_ROOT_KEYS_EXTRACT_ALL_KEYS_FOR_DATABASE (New)
- This new function extracts all the encryption root keys of a database and sends them to a client session as a CLOB. The root keys of a tenant database can be extracted using this function either in the tenant database or in the system database if the system database has encryption configuration control for the tenant database. ENCRYPTION_ROOT_KEYS_EXTRACT_ALL_KEYS_FOR_DATABASE Function (Security)
System Views (New and Changed)
- KEY_MANAGEMENT_CONFIGURATIONS System View (Changed)
- A new column has been added: CONFIGURATION_ID. KEY_MANAGEMENT_CONFIGURATIONS System View
- TABLE_COLUMNS System View (Changed)
- The FUZZY_SEACH_MODE column as been increased to display 32 characters. TABLE_COLUMNS System View
- M_BACKUP_CATALOG_FILES System View (Changed)
Four new columns have been added: BACKINT_FALLBACK_USED, CONFIGURATION_INCLUDED, COMPRESSED_SIZE, COMPRESSION_ALGORITHM. M_BACKUP_CATALOG_FILES System View
- M_BACKUP_CONFIGURATION System View (Changed)
- Five new columns have been added: IS_LOG_BACKUP_COMPRESSION_ENABLED, LOG_BACKUP_COMPRESSION_ALGORITHM, LOG_BACKUP_COMPRESSION_LEVEL, DATA_BACKUP_COMPRESSION_ALGORITHM, DATA_BACKUP_COMPRESSION_LEVEL.
- M_CONNECTIONS System View (Changed)
Three new columns have been added: SSL_VERSION, SSL_CIPHER, and HAS_SSL_CLIENT_CERTIFICATE. M_CONNECTIONS System View
- M_CS_INDEXES System View (Changed)
- Four new columns have been added to the view: MEMORY_SIZE_IN_TOTAL, MEMORY_SIZE_IN_CONCAT, MOST_SELECTIVE_COLUMN_NAME, and INVERTED_INDIVIDUAL_COST. M_CS_INDEXES System View
- M_DELTA_MERGE_STATISTICS System View (Changed)
- Several new columns have been added to the view for improved monitoring. M_DELTA_MERGE_STATISTICS System View
- M_OUT_OF_MEMORY_EVENTS System View (Changed)
A new column has been introduced: WORKLOAD_CLASS_NAME. M_OUT_OF_MEMORY_EVENTS System View
A new- M_SERVICE_THREADS and M_SERVICE_THREAD_SAMPLES System Views (Changed)
A new column has been introduced: WORKLOAD_CLASS_NAME. M_SERVICE_THREADS System View, M_SERVICE_THREAD_SAMPLES System View
- M_SQL_ANALYZER_PLAN_TRACES System View (New)
Displays metadata of .plv files generated by the PlanTracer. This information is provided for PUBLIC users. M_SQL_ANALYZER_PLAN_TRACES System View
- M_SQL_PLAN_CACHE System View (Changed)
Four new columns have been introduced: TOTAL_EXECUTION_CPU_TIME, AVG_EXECUTION_CPU_TIME, MIN_EXECUTION_CPU_TIME, and MAX_EXECUTION_CPU_TIME. M_SQL_PLAN_CACHE System View
- M_SQL_PLAN_STATISTICS System View (Changed)
Four new columns have been introduced: TOTAL_EXECUTION_CPU_TIME, AVG_EXECUTION_CPU_TIME, MIN_EXECUTION_CPU_TIME, and MAX_EXECUTION_CPU_TIME. M_SQL_PLAN_STATISTICS System View
- M_TABLE_REPLICATION_VOLUME_STATISTICS, M_TABLE_REPLICATION_VOLUME_STATISTICS_RESET, M_TABLE_REPLICATION_VOLUME_HISTORY System Views (New)
- Three new monitoring views have been introduced for monitoring asynchronous table replication (ATR), optimistic synchronous table replication (OSTR), and remote table replication (RTR). M_TABLE_REPLICATION_VOLUME_STATISTICS, M_TABLE_REPLICATION_VOLUME_HISTORY, M_TABLE_REPLICATION_VOLUME_STATISTICS_RESET
- USERGROUPS System View (Changed)
The IS_CLIENT_CONNECT_ENABLED column has been added in support of the ability to enable and disable client connections by usergroup.USERGROUPS System View
- VIRTUAL_FUNCTIONS System View (Changed)
- Three new columns have been added: FUNCTION_USAGE_TYPE, OWNER_NAME, CREATE_TIME. VIRTUAL_FUNCTIONS System View
- VIRTUAL_FUNCTION_PARAMETERS and VIRTUAL_FUNCTION_PARAMETER_COLUMNS System Views (New)
- Two new views have been added to support virtual table user-defined functions. VIRTUAL_FUNCTION_PARAMETERS System View, VIRTUAL_FUNCTION_PARAMETER_COLUMNS System View
Embedded Statistics System View (New and Changed)
- HOST_DELTA_MERGE_STATISTICS System View (Changed)
- Several new columns have been added to the view for improved monitoring.
Other Changes by Feature Name (New and Changed)
- Scheduler Job (New and Changed)
- You can now create, alter, drop, and comment on scheduler jobs. Several views were also created to support this feature.
- HINT Details (New and Changed)
The following new hints have been added:
- CS_JOIN_REDUCTION_MULTICOLUMN_FIRST(<table_name><column_name>)
Specifies the column to be evaluated first in the JoinEngine semi-join reduction, in the case of a multicolumn join with skewed data distribution.
HINT Details
- SESSION USER Data Masking (Changed)
- Tables and views now support session user data masking. CREATE TABLE Statement (Data Definition), ALTER TABLE Statement (Data Definition), CREATE VIEW Statement (Data Definition), ALTER VIEW Statement (Data Definition), TABLES System View, VIEWS System View, EFFECTIVE_MASK_EXPRESSIONS System View
- Monitor Table Partition Operations (Changed)
- You can now monitor the history of all partition-relevant operations on a partitioned table.
- The configuration parameter enable in the table_partition_operation_tracesection in the ALTER SYSTEM ALTER CONFIGURATION statement lets you turn monitoring ON and OFF. ALTER SYSTEM ALTER CONFIGURATION Statement (System Management),
- The M_TABLE_PARTITION_OPERATIONS monitoring view lets you view Partition Operation Tracing information from the memory ring buffer or disk trace files. M_TABLE_PARTITION_OPERATIONS System View
- The new TABLE PARTITION OPERATIONS trace_type in the ALTER SYSTEM CLEAR TRACES statement lets you clear the monitored information. ALTER SYSTEM CLEAR TRACES Statement (System Management)
- Use Certificate Names when Creating Certificates
- When creating or dropping certificates and PSEs, you can now specify a certificate name or a certificate id. The CERTIFICATES and PSE_CERTIFICATES System Views have been updated to include the certificate name. CREATE CERTIFICATE Statement (System Management), DROP CERTIFICATE Statement (System Management), ALTER PSE Statement (System Management), CERTIFICATES System View, PSE_CERTIFICATES System View
Thanks
Rupesh Chavan
Link
SAP HANA SQL and System view Reference