# CHAPTER 2: Product New Features and Enhancements

## 2.1. Case Insensitivity Support

Users can force Case Insensitivity by setting an Empress variable MSCASEINSENSITIVE in the "initfile", e.g.

            MSCASEINSENSITIVE=X

The database components such as table, view, attribute, index, reference, constraint and stored procedure, created after above setting was made, support Case Insensitivity.

This setting must be done before the database components are created.

## 2.2. Using Empress Dynamic/Embedded SQL in Persistent Stored Modules

Empress Persistent Stored Modules (PSM) are a set of dynamically loadable libraries that can contain user-defined functions, procedures, aggregate functions, operators (together they are referred to as routines) and triggers. User-defined procedures storable as database objects are commonly called "stored procedures". In Empress Version 8.62, user can write routines using Empress Dynamic SQL and Embedded SQL Interface (DSQL/ESQL).

### 2.2.1 How To Compile PSM Routines Written By DSQL/ESQL

Routine written in DSQL/ESQL can be precompiled with Empress utility "empesql" and, then compiled with "emppsmcc" to generate dynamic link library.

### 2.2.2 New Reserved Cursor Names Used in Triggers

The following two reserved cursor names:

            emptrig_cursor_old
            emptrig_cursor_new

for triggers are introduced in Empress Version 8.62. These cursors can be used to access the old/new inserted/updated/deleted record values.

In Empress trigger, the FETCH syntax is defined as follows:

            EXEC SQL FETCH  | emptrig_cursor_old |  [attr_list]
                            | emptrig_cursor_new |

                    INTO    | value_list                |
                            | SQL descriptor desc_name  |

Note:

For reserved cursors, user cannot declare, open, close, drop and use fetch options, such as next, prior, absolute <position>, related <position> to operate them.

The UPDATE syntax is as follows:

            EXEC SQL UPDATE |<table>| SET |attr-list VALUES variable-list    |
                            |CURRENT|     |attr = variable {,attr = variable}|

            WHERE CURRENT OF emptrig_cursor_new;

Note:

For UPDATE command, user can assign table name or use keyword "current" to assign the current table. If the table name is assigned, the table check will be executed.

"emptrig_cursor_old" cannot be used in UPDATE command. If UPDATE command is used in "after trigger", it will not have any effect.

The reserved cursors can not be used in a regular DSQL/ESQL program. The following message will be produced:

    "fetch/update trigger cursor and abort trigger operation only supported in
     trigger procedure"

### 2.2.3 ABORT TRIGGER

The ABORT TRIGGER command has the following syntax:

            EXEC SQL TRIGGER ABORT;

It can only be used in a trigger procedure, otherwise the following message will be produced:

    "fetch/update trigger cursor and abort trigger operation only supported in
    trigger procedure"

### 2.2.4 Memory Allocation

Two new memory allocation statements are introduced. They can also be used in a regular ESQL/DSQL program.

            EXEC SQL MEMORY ALLOCATE <variable> '(' | constant    | ')'
                                                    | <variable>  |

            EXEC SQL MEMORY REALLOCATE <variable> '(' | constant   | ')'
                                                      | <variable> |

The following statement can be used to free memory.

            EXEC SQL MEMORY FREE <variable> {, <variable>}

### 2.2.5 INIT and EXIT

The following statements are invalid when used in PSM routines and triggers:

            EXEC SQL INIT;
            EXEC SQL DATABASE is "db";
            EXEC SQL EXIT;

To change a database in PSM routines and triggers a "db:table" construct can be used.

## 2.3. Empress Performance Improvement

Empress has significantly improved the SQL performance in Empress Version 8.62 compared to the Empress Version 8.60.

## 2.4 Database Roles

In Empress Version 8.62, Empress introduced the concept of database roles. Roles are designed to ease the administration of privileges. They control access to database tables and permit easier management in the database.

A Role generally refers to a title or a set of duties, which is a named bundle of zero or more privileges. Roles can be created and dropped using Standard SQL statements.

Roles allow to collect users into a single unit against which privileges can be applied.

In regard to "user", it is either an actual person or an application program that has access to SQL-data. Users are outside schemas. The methods used to create, drop and map it to actual person/application programs are implementation-defined. Empress doesn't provide special commands to create, drop and map user.

Following ANSI SQL-99 standard, Empress supports "create role", "grant role", "revoke role" and "drop role" commands.

The syntax for "create role" command is:

        create role ROLE;

The "create role" statement specifically defines ROLE to be a Role rather than a User. This ROLE cannot be a defined user in the database schema, such as dba, user, PUBLIC, etc.

The syntax for "grant role" command is:

        grant ROLE {, ROLE } to GRANTEE {, GRANTEE } [ WITH ADMIN OPTION ]

The "grant role" statement grants the use of one or more ROLEs to one or more GRANTEEs, including (optionally) PUBLIC. The grantor of the ROLEs must, of course, hold these ROLEs as grantable ROLEs (that is, WITH ADMIN OPTION).

The option WITH ADMIN OPTION clause defines grantable ROLES.

The syntax for "revoke role" command is:

        revoke [ ADMIN OPTION FOR ] ROLE {, ROLE } FROM GRANTEE {, GRANTEE }
                               { RESTRICT | CASCADE }

The "revoke role" statement revokes the use of one or more ROLEs from one or more GRANTEEs.

The ADMIN OPTION FOR clause allows grantor to revoke only the grantability of a ROLE.

If the "revoke role" statement specifies CASCADE, it cascades down to revoke roles that would otherwise be abandoned. If the "revoke role" statement includes RESTRICT, the revoke succeeds only if the role being revoked has no dependent roles.

The syntax for "drop role" command is:

        drop role ROLE;

Only creator of ROLE can drop it. For each user/role, it was granted use of the role, RDBMS will call the following revoke command.

        revoke ROLE from GRANTEE restrict.

For example, if user joe enters the following sequence of commands:

        joe:     create role SUPPORT;
        joe:     grant SUPPORT to peter with admin option;
        joe:     grant SUPPORT to TECH;
        joe:     drop role SUPPORT;

where peter is another user and SUPPORT and TECH are roles. "drop role" command will implicitly invoke the following two commands:

                 revoke SUPPORT from TECH restrict;
                 revoke SUPPORT from peter restrict;

However, if peter granted SUPPORT role to another user before joe tried to drop the SUPPORT role, "drop role" command will fail since it cannot remove dependant roles.

Roles can be viewed using "display role" command. The syntax is:

       display role [ROLE] [all] [| into | FILE];
                                  | onto |

This command displays role[s] in a database and it can be used in Empress interactive SQL. If ROLE is not specified, the information on all database roles will be displayed. More details can be displayed with keyword "all".

The output of a display command may be redirected from the terminal into a new file or appended to an existing file by specifying "into" or "onto", respectively, and a filename.

For further information on "display role" information, enter:

                    help display;

For further information on database roles, enter:

                    help role;

## 2.5. SQL Extension

In Empress Version 8.62, Empress added the following SQL extension

            select attr1 = "ABC", attr2 from tableX

where attr1 = "ABC" will cause the first column in the result set to have a value "ABC".

## 2.6. Data Type Conversion Improvement For SET Operation

In Empress Version 8.62, Empress included data conversion for set operation.

For example,

            select attr1 from t1
                    union all
            select attr2 from t2

Assuming attr1 is defined as char(10) and attr2 is defined as char (20), the above task can be achieved in Empress Version 8.60 by modifying the command into the following syntax:

            select attr1 convert to char(20) from t1
                    union all
            select attr2 from t2

In Empress Version 8.62, Empress will conduct the conversion automatically.

The conversion is conducted for the following four data type categories.

            Numeric
            Char/Text
            Bulk
            Date/Time/Microtimestamp

Empress does not support cross-category conversion. Cross-category conversion can be achieved by explicitly using "CONVERT TO" functionality.

## 2.7 New Variables

In the version 8.62, several new variables are introduced. Here is the list of them:

- MSCASEINSENSITIVE
- MSDBLOCKGRANULARITY
- MSDBPUBLICPRIVILEGES
- MSDBVERSION_FEATURES
- MSNTMPFILECACHE
- MSQUOTEDIDENTIFIERS
- MSSELEXTFETCH
- MSSELMAXROWS
- MSSELTIMEOUT
- MSTMPFILECACHESIZE

All new variables are documented in [Volume A5: Database Administrator's Guide]

## 2.8 User Defined Lock Granularity

In Empress Version 8.62, new user defined lock granularity is introduced via the LOCK LEVEL command. The LOCK LEVEL command sets the level of locking on a table. The new extensions for RECORD and GROUP LOCK LEVEL allow for "n" granularity.

New syntax:

            LOCK LEVEL [ON] table [IS] |TABLE        |;
                                       |GROUP  [(n)] |
                                       |RECORD [(n)] |
                                       |NULL         |

where:

- Specifying RECORD locks only each record as it is encountered in a command.
- Specifying RECORD (n) locks each record as it is encountered in a command plus (n-1) surrounding records. n records, locked in this case, are constituting a "PAGE" lock

For example:

If 100 consecutive records are updated in a transaction in a table with RECORD level locking, until a transaction is committed or rolled back, there will be 100 record locks in the lock manager.

If a RECORD (5) level locking is used in the above case, approximately 20 locks will be present in the lock manager before a transaction is committed or rolled back.

Choosing a right value for lock granularity (i.e. a number of locks in a "PAGE") will be a trade-of between performance and concurrency.

## 2.9 Triggers On Select Statements

In addition to the triggers on DELETE, UPDATE and INSERT statements, Empress RDBMS introduces a unique functionality of triggers on SELECT statements. A SELECT trigger is a procedural processing element, stored in the database and executed automatically by the Empress RDBMS engine when applications or users perform SELECT commands on the database.

Empress RDBMS supports both the "row-level" and "statement-level" SELECT triggers.

The ability to fire a trigger on SELECT statements gives Empress a competitive advantage and it can be used to achieve the following objectives:

- to implement a higher level of security for database tables' look up operations
- to compile audit logs or statistics of database table usage
- to monitor demand for information in an enterprise data warehouse system
- to record query types and user preferences in database WEB publishing

The new SELECT trigger command syntax is adapted to the standard "create trigger" command.

      create  trigger TRIGGER | before |
                              | after  |

        |              delete                    |
        |              insert                    |
        | update [ of ( ATTR_NAME {, ATTR_NAME}) |
        | select [ of ( ATTR_NAME {, ATTR_NAME}) |

        { ,  |              delete                    | }
             |              insert                    |
             | update [ of ( ATTR_NAME {, ATTR_NAME}) |
             | select [ of ( ATTR_NAME {, ATTR_NAME}) |

        on TABLE [for each row [when CONDITION]]

        execute PROCEDURE_NAME;

A database trigger is a procedure without parameter which is associated with a table. Empress RDBMS automatically executes a trigger when a specified SQL statement is issued against the table.

The trigger can be specified to fire either before the statement/operation (INSERT, DELETE, UPDATE) is attempted on a row/record or after the operation has been completed. In the case of the SELECT trigger however, it can be specified to fire only after the SELECT operation has been completed.

UPDATE and SELECT trigger can specify the corresponding attribute lists. If no attribute is specified, it means all attributes of a table.

The "when CONDITION" is associated with "for each row" option, if "for each row" is not assigned in create trigger command, the "when CONDITION" cannot be assigned. Once the "when condition" is assigned for "for each row" option, only in the case when the accessed row satisfies the condition, the trigger will be fired.

The CONDITION in "when" clause is similar to that in the "where" clause, except that all attributes must be in the table associated with the trigger and qualified by either "new." or "old.". Subqueries are not supported.

To remove a trigger, "drop trigger" command is used.

Syntax:

         drop trigger TRIGGER;

For further information on display trigger information, enter:

                    help display;

For further information on enable/disable trigger or change priority of trigger(s), enter:

                    help alter;

## 2.10 Multi-Threaded Clients

In version 8.62 Empress made its ODBC/JDBC clients multi-thread capable.

In previous Empress versions Empress JDBC driver, for example, could be used in threads but the operations done in threads would be serialized. In version 8.62 both ODBC and JDBC client/server drivers could be used in threads achieving true parallelism.

Empress ODBC/JDBC Local Access drivers are thread-safe (i.e. could be used in threaded application), but they don't provide for a true parallelism.

On a multiprocessor system, threading allows an application to perform more than one independent computation at the same time, providing for a true parallelism. A computation intensive threaded application running on a two processor architecture can achieve great performance improvement compared to a single threaded version of the same application. However, database applications are usually not computation intensive but I/O intensive and threading might not lead to a performance improvement.

Furthermore, threading has its cost. Despite powerful advantages of threading, there are some clear drawbacks, such as: an introduction of computing overhead (thread synchronization and scheduling) and a requirement for strong programming discipline when developing applications. Multi-thread applications are also significantly harder to debug.

Database application programmers should take a decision "to thread or not to thread" in their applications very carefully.

To acquire true parallelism in Empress database applications (using ODBC or JDBC client/server drivers), one should issue concurrent SQL operations (e.g. issue concurrent queries from different threads) by starting up multiple connections and by associating those connections with each thread.

## 2.11 Empress JAVA Interactive SQL

Empress JAVA Interactive SQL is a graphical JAVA SQL client program configured as a Stand-alone JDBC program, that allows users to view the structure of a local Empress database using JDBC , browse data in Empress tables and issue SQL commands on Empress tables. This allows users in local mode to set up and define Empress databases, request information from Empress databases and administer Empress databases using a graphical JAVA environment.

Empress JAVA Interactive SQL can be invoked by using "empjavasql" command.

## 2.12 Support for ANSI SQL-99 SET Operations

In version 8.62 Empress application programming interfaces support ANSI SQL-99 UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT and INTERSECT ALL operations, which are based on the well-known union, difference and intersection operations of the SET Theory.

For more information please see [Volume A4 SQL Reference]

## 2.13 Support for ANSI SQL-99 Outer Joins

In version 8.62 Empress application programming interfaces also support ANSI SQL-99 Outer Joins, such as:

       LEFT OUTER JOIN
       RIGHT OUTER JOIN
       FULL OUTER JOIN

For more information please see [Volume A4 SQL Reference]

## 2.14 Empress Import Utility Enhancement

Empress import utility empimpt, in the version V8.62, is capable of importing database table(s) from an export file created in a different codeset from a native codeset of the version invoked by empimpt.

empimpt includes the codeset option and also displays available translatable code sets.

Empress utility empvers, in the version V8.62, also prints the native codeset information.

For example:

            empimpt -codeset SJIS db1.exp db2

will treat data in db1.exp export file as stored in codeset SJIS in order to translate it into the native codeset (e.g. EUC-JP) of the version invoked by empimpt.

For more information please see "man" pages for Empress import utility empimpt (i.e. man empimpt, or empimpt -help)

## 2.15 Reserved Keywords

There are several new reserved keywords introduced in version 8.62. Additional reserved keywords are:

            ?
            ADMIN
    	AUTHORIZATION
    	BINARY
    	CACHE
            CASCADE
    	CAST
    	CHAR
    	CHARACTER
    	CIPHER
    	CONCAT
            CURRENT
    	DAY
            DBA
    	DYNAMIC
    	ENCRYPTED
    	ENDVALUE
    	FALSE
    	INCREMENT
    	LARGE
            MASTERS
            MINUS
    	MINUTE
    	MONTH
    	NATIONAL
    	NCHAR
    	NEXTVALUE
    	NOLOCK
    	NOSHMEM
    	OBJECT
            OJ
    	POSITION
    	REFERENCE
            REPLICATES
            RESTRICT
    	RESULT
            ROLE
    	SCHEMA
    	SECOND
    	SEQID
    	SEQUENCE
    	SETS
    	SQL_TSI_DAY
    	SQL_TSI_FRAC_SECOND
    	SQL_TSI_HOUR
    	SQL_TSI_MINUTE
    	SQL_TSI_MONTH
    	SQL_TSI_QUARTER
    	SQL_TSI_SECOND
    	SQL_TSI_WEEK
    	SQL_TSI_SECOND
    	SQL_TSI_YEAR
    	STARTVALUE
            SUBSET
    	TIME
    	TIMESTAMP
    	TIMESTAMPADD
    	TIMESTAMPDIFF
    	TRUE
    	TYPE
    	VARYING
    	WEEK
    	WITHOUT
    	YEAR
            {
            {D
    	{FN
    	{OJ
            {TS
            }

For a complete list of all reserved keywords, please refer to our Reserved Keywords document.

The following reserved keywords are made obsolete:

            CENTER
            CENTRE
            INPUT
            LEFTRIGHT
            MOVE
            PRECISION
            SHARE

A significant amount of work in version 8.62 was done to increase user's capability to handle reserved keywords.

For example, although SIN, COS and LOG are reserved words for built-in mathematical functions, one could use them as attribute names in CREATE TABLE command:

            CREATE TABLE TABLEX (SIN INT, COS INT, LOG CHAR(10));

This was not possible in previous Empress versions.

## 2.16 Support for Java Data Sources and JNDI

In the version V8.62, Empress JDBC Category 2 drivers support Java Data Sources and Java Naming and Directory Interface (JNDI).

The JDBC 2.0 standard extension API introduced the concept of data sources (javax.sql.DataSource Interface). Data Sources are standard, general-use objects for specifying databases or other resources to use. Data sources can optionally be bound to Java Naming and Directory Interface (JNDI) entities so that users can access databases by logical names, for convenience and portability.

Empress has implemented the DataSource interface by using a "Basic implementation" defined in Sun Microsystems JDBC specification.

An alternative to the DriverManager facility, a DataSource object has properties that can be modified when necessary. If the data source is moved to a different server or to a different location on the same server, or the port number of the Empress Connectivity Server has changed, all these properties could be modified in the Data Source object. The benefit is that because the data source's properties can be changed, user's Java application accessing that data source does not need to be changed.

This functionality is a more powerful alternative to the previous JDBC DriverManager facility. For further information about data sources and JNDI, refer to the Sun Microsystems specification for the JDBC package.

Java applications using Java Data Sources and JNDI would have to include (i.e. import) the following two groups of methods:

            import javax.naming.*;
            import javax.sql.DataSource;

## 2.17 ODBC Interface

### 2.17.1 ODBC Local Access via ODBC Manager

With V8.62 Microsoft Windows ODBC applications can use Empress ODBC Local Access Driver with ODBC Driver Manager. This opens a door to usage of Empress ODBC Local Access Driver with 3rd party ODBC compliant tools such as Microsoft Visual Basic or Microsoft Access.

### 2.17.2 Multiple Connections in Local Access Mode

In the version V8.62, both ODBC and JDBC Local Access Drivers support multiple connections. Previous versions of those drivers could handle only one connection at the time.

### 2.17.3 Empress ODBC Data Source Name Setup Improvement

A new Empress ODBC Data Source Name Setup allows testing a connection to a Data Source with supplied parameters. In this way users can spot connection problems before they run the application.

### 2.17.4 Providing standard (default) labels for ODBC Drivers in Data Sources

In previous Empress ODBC driver versions, whenever ODBC driver is upgraded user needed to update a Data Source with the association to a new driver. With V8.62 Empress provides standard (default) labels for ODBC Drivers so that Data Sources won't require a change when ODBC Driver is upgraded.

New labels ("Name" in "ODBC Data Source Administrator") used for Empress ODBC drivers are:

       Empress ODBC Local Access Interface [Default]
       Empress ODBC Interface [Default]

### 2.17.5 Compliance to Microsoft .NET Technologies

Empress V8.62 ODBC driver supports several Microsoft .NET technologies. Empress V8.62 ODBC driver can be used in .NET environment to access Empress database through OLEDB.NET provider or ODBC.NET, a data provider that is an add-in component to the Microsoft .NET framework.

## 2.18 Scrollable Cursor Functionality

In version 8.62 Empress introduces a new scrollable cursor functionality in Dynamic SQL Interface (i.e. SQL Precompiler). The changes in the syntax for DECLARE CURSOR (SENSITIVE, INSENSITIVE, ASENSITIVE and SCROLL options) and FETCH (FIRST, LAST, ABSOLUTE, RELATIVE options) commands were made in order to acquire additional capability.

The similar functionality is also available in Empress V8.62 ODBC interface.