ChangesIn74

1. SQL
   1. Features removed
   2. New reserved keywords
   3. Changes to system tables
   4. New functions
   5. Changes to locking (also 7.3)
   6. Changes to stored procedures (also 7.3)
   7. Changes to ALTER
   8. Other changes
2. Administration
   1. The log handling is completely rewritten
   2. Backups are always consistent (no checkpoint anymore)
   3. Log writing can be switched off
   4. An incremental backup contains changes since last complete backup
   5. A restored database can be smaller than original database
   6. Archive Stage Command for Log Backups
   7. db_offline -quick dropped
   8. Kernel parameter removed
   9. Kernel parameter added
3. Interfaces
   1. Support for JDBC 3.0
   2. Python interfaces have been moved to package sapdb
   3. ODBC
4. Tools
   1. Replication Manager has been renamed to SAP DB Loader
   2. New Features of SAP DB Loader
   3. New Tool DB Analyzer
   4. Some SQL Studio Dialogs/Menus have been renamed
   5. New menu in SQL Studio Catalog Manager

SQL

Features removed

• SELECT DIRECT, SELECT ORDERED

• The user options PERMLIMIT and TEMPLIMIT

• TERMCHAR SETS

• LOCALSYSDBA and SYSDBA (<username>)

• CREATE/DROP INDEX <tablename>.<columnname>

New reserved keywords

• CASE

• GET_OBJECTNAME

• GET_OWNER

• HEXTORAW

• UTCDATE

• WHEN

Changes to system tables

• CONNECTEDUSERS

• INDEXCOLUMNS

• ROLES

• USERS

• TABLEPRIVILEGES

• TRANSACTIONS

New functions

• GET_OWNER, GET_OBJECTNAME - performs name lookup like SQl parser

• UID (also 7.3)

• UTCDATE (also 7.3)

• TIMEZONE (also 7.3)

• CASE (also 7.3)

• HEXTORAW (also 7.3)

Changes to locking (also 7.3)

• FOR UPDATE OF (updatable cursors) now locks the qualified rows exclusive. The option NOWAIT is new.

• the option IGNORE is new in the <lock_option>.

• with ISOLATION LEVEL 2, no temporary table share lock will be done for INSERT, UPDATE and DELETE

Changes to stored procedures (also 7.3)

• DBProcedures are able to return a cursor

• <case_statement> and RETURN

Changes to ALTER

• with ALTER INDEX ... INIT USAGE, the information about index usage can be reset

• with ALTER TABLE ADD, the new column may have a default

• columns of datatype LONG can be handled by ALTER TABLE ADD / DROP. However, it is not possible to set a default for LONG columns in an ALTER TABLE ADD statement. To do this, first add the LONG column using ALTER TABLE ADD, then set the default value for this column using ALTER TABLE MODIFY.

Other changes

• WHERE ROWNO <= 0 is allowed

• scalar subqueries are supported

• new comment style: -- to end of line

• CREATE TABLE ... AS SELECT and INSERT ... SELECT now work with LONG columns

• for a single column, two indices are now possible, one ascending and one descending

• the default code for CHAR columns can now be specified per user to override the installation parameter DEFAULT_CODE

• the current value of a serial will be returned by <table_name>.CURRVAL (also 7.3)

Administration

The log handling is completely rewritten

The management of undo and redo log is completly rewritten. The main difference compared with older releases is that undo and redo log is seperated now. The undo log is written into the data volumes and only the redo log is written to the log volume. This was necessary for livecache recovery and increasing performance. This is also the basis for implementing additional features like multi log queues and unlocked commited read for sql data.

Backups are always consistent (no checkpoint anymore)

Another advantage of the new log handling is that the old "checkpoint" became obsolete. So data backups can always be restarted without log backups.

Log writing can be switched off

In admin mode the transactions can be forced to write no redo log.

An incremental backup contains changes since last complete backup

This simplifies restoring a database as only one incremental backup is needed in addition to the last complete backup. In 7.3, an incremental backup contained the changes since last incremental backup.

A restored database can be smaller than original database

In 7.3, a database for a restore had to to be large enough for n pages, with n being the highest used page number in the backup. With 7.4, n is only the number of pages actually in use.

Archive Stage Command for Log Backups

db_offline -quick dropped

A db_offline will now cancel all running sessions.

Kernel parameter removed

• CONVERTER_CACHE

• DATA_CACHE

• DATA_CACHE_MINSIZE

• EST_INDEX_COLUMNS

• EST_SUBTREE_PAGES

• EST_SUBTREE_RECS

• MAXDATAPAGES

• MAXMAXDATAPNO

• M_DATADEV_?

• M_DATA_TYPE_?

• M_SYSDEV_001

• M_SYS_TYPE_001

• OPTIM_BUILD_RESLT

• OPTIM_FETCH_RESLT

• OPTIM_KEY_INV_RATE

• OPTIM_ORDERBY_IDX

• OPTIM_OR_DISTINCT

• PNO_SUPPLY_MINSIZE

• RESERVED_REDO_SIZE

• ROLLBACK_CACHE

• SERVERNODE

• SYSDEV_001

• SYS_TYPE_001

• TRACE_PAGES_CS

• _COPY_IO_BLOCK_CNT

• _DW_COPY_BOUNDARY

• _FBM_CACHE

• _KERNEL_ALLOC_POOL

• _LOG_CACHE_PAGES

• _LOG_COPY_DISTANCE

• _LOG_START_COPY

• _MAXAUTOBACKUPDEVS

• _MAXDATAWRITER_DEF

• _MAXSYSDEVSPACES

• _MBYTE_CACHE_DEF

• _MIRRORED_DATA

• _MULTIBYTE_CACHE

• _MULTIBYTE_SET1

• _MULTIBYTE_SET2

• _MULTIBYTE_SET3

• _MULTIBYTE_SET4

• _MULTIBYTE_SET5

• _PNO_SUPPLY_SIZE

• _PSE36_USAGE

• _SLOTS_PER_CONVPAG

• _USE_PROC_SCOPE

• _USE_THRD_FOR_TSK

• _USM_CACHE

Kernel parameter added

Kernel parameters are documented in <dependent_path>/env/cserv.pcf.

• ALLOW_DEMANGLE

• CACHE_SIZE, is the sum of DATA_CACHE (removed) and CONVERTER_CACHE (removed)

• CONVERTER_REGIONS, number of latches used to strip the converter

• DEVNO_BIT_COUNT

• EXPAND_COM_TRACE

• FORMATTING_MODE

• HEAP_CHECK_LEVEL

• HIRES_TIMER_TYPE

• LOAD_BALANCING_CHK

• LOAD_BALANCING_DIF

• LOAD_BALANCING_EQ

• OMS_HEAP_BLOCKSIZE

• OMS_HEAP_COUNT

• OMS_HEAP_THRESHOLD

• OMS_STREAM_TIMEOUT

• OMS_VERS_THRESHOLD

• OPTIM_CACHE

• OPTIM_INV_ONLY

• SHOW_MAX_STACK_USE

• XP_DATA_CACHE_RGNS, used to overdrive the number of DATA CACHE latches

Interfaces

Support for JDBC 3.0

• support for JDBC 3.0 has been added

• the javax.sql API is supported now

The driver continues to work seamlessly in JDBC 2.0 environments.

Python interfaces have been moved to package sapdb

• sapdb => sapdb.sql

• dbm => sapdb.dbm

• repman => sapdb.loader

• sapdbapi => sapdb.dbapi

There exist wrapper to access these modules using the old name.

ODBC

• Unicode support (already in 7.3.1, but not in 7.3.0)

Tools

Replication Manager has been renamed to SAP DB Loader

This affects

• repmcli => loadercli

• Python module repman => sapdb.loader

• Perl module SAP::DBTech::repman => SAP::DBTech::loader

• Java class Repman => Loader

There exist wrapper to access the Loader using the old name.

New Features of SAP DB Loader

• Migration of DB or parts of DB (Restartable)

• Migration of DB via pipe directly from one DB to the other

• Support of ASCII / UNICODE-Conversion (UCS2 or UTF8)

• Loader Commands and SQL Statements are not restricted in their length (of 16K) anymore

New Tool DB Analyzer

DB Analyzer is a tool to search for performance bottlenecks.

Some SQL Studio Dialogs/Menus have been renamed

• Query By Example => Form Dialog

• Direct SQL => SQL Dialog

• Menu->File => Menu->Session

New menu in SQL Studio Catalog Manager

• Send To -> Table Defintion

• Send To -> View Defintion

  • ...

The dialogs will open with the selected Catalog Object.