Implementations to Achieve Performance Goals in The Context of Multi-Dimensional Architecture

This article will concentrate on features of the Oracle, DB2 and Informix embedded SQL implementations, which can be exploited to achieve performance gains, in the context of a multi-dimensional architecture.

By Mark Sitkowski C.Eng, M.I.E.E Design Simulation Systems Ltd

Where a feature is supported on one system, and not another, we will suggest possible alternative approaches, where such exist.

Multiple Connections

Oracle

Oracle permits simultaneous connections to multiple databases.
Within the DECLARE SECTION of code, we define a symbolic name for each database, as per:

 EXEC SQL DECLARE LOCAL_DB DATABASE;
 EXEC SQL DECLARE REMOTE_DB DATABASE;

Then, in the body of the program, we can connect to both databases:

EXEC SQL
 CONNECT :name IDENTIFIED BY :password AT LOCAL_DB USING :database1
 
EXEC SQL
 CONNECT :name IDENTIFIED BY :password AT REMOTE_DB USING :database2

This binds the database names in the variables ‘database1’ and ‘database2’ to LOCAL_DB and REMOTE_DB, so that the latter can be used throughout the rest of the program.
The syntax for DB2 is different:

EXEC SQL
 CONNECT TO :database USER :user USING :password [IN SHARE MODE]

The optional ‘IN SHARE MODE’ permits multiple transactions to occur within the one connection.
It is as simple as:

EXEC SQL
 CONNECT DATABASE :database

In the body of the program, SQL statements are entered with the prefix

EXEC SQL

Oracle differs in providing the ability to simultaneously execute SQL on different databases. In order to achieve this, the preamble is changed to:

EXEC SQL AT SYMBOLIC_DB_NAME

Oracle can simultaneously connect to multiple databases from within one process. However, having done so, each EXEC SQL statement must explicitly state where to EXEC the SQL. Simultaneous connections have the obvious advantage that we can take data from one database, process it and, immediately, deposit it in another.
DB2 can only connect to one database at a time, from within one process, but can connect to any number, sequentially. This means that, if we need two simultaneous connections, we must create two processes, make the connections, and transfer the data between processes via an inter-process communication link.
Informix supports multiple simultaneous connections, like Oracle. However, it uses a different syntax to route the SQL statements to the various connections.
Both DB2 and Informix have the concept of a ‘Dirty Read’, meaning that parallel access by multiple processes to a table is possible. DB2 also permits parallel INSERT and UPDATE access. These abilities permit improving the throughput on what is, traditionally, a huge bottleneck, namely, the row-locking, which occurs in database tables when they are multiply-accessed.
Oracle will permit a transaction to be labeled ‘Read Only’, but is rather vague as to how much parallelism it permits. In the same way, Insert and Update transactions can have an Isolation Level associated with them, but the actual parallelism is left to the discretion of the database engine.
Perhaps, to make up for the above shortcoming, Oracle offers an extremely powerful tool in the form of its array cursors. These permit Select, Insert and Update operations to be performed via host variables, each of which is an array.
The advantage of this approach is that, instead of making 50000 calls to the database engine, to Insert 50000 rows, we only need to make one. Similarly, to retrieve 50000 rows, we only need a single Select statement.