Oracle implementation

KCML supports Oracle 10g and later through the use of the Oracle Client Interface or OCI. This is a networked client server implementation so the Oracle database server need not be on the same server and multiple KCML application servers can be attached to the same database.

A mapping layer has been added to KCML which translates the standard KDB7 database API operations into equivalent SQL which is then executed against the Oracle database. This has been done in such as way as to require almost no changes to existing KCML KDB7 programs in order to migrate KDB7 tables to Oracle tables.

There is also a simple mapping of KDB datatypes into Oracle native datatypes allowing the easy use of Oracle tools for reporting and the like. However the KDB Oracle tables are all owned by a special user, by default KCC, whose password is hidden and only known to the KCML runtime (and to the SYSDBA). Only the KCC user has been GRANTED update access. This is to guarantee referential integrity withing the application.

The Oracle database must be Unicode with UTF-8 encoding.

Attaching to Oracle

It is not necessary to perform a CREATE DATABASE for Oracle as there is a built in database name of "oracle".

A KI_CONNECT call using the database name "oracle" and a DSN that contains the connect string (e.g. //server/instance) must be used to log into the Oracle database and get a connection number for later use. This will load a shared library containing Oracle support routines (koracle.dll on Windows and koracle.so on Unix). A licence file entry in your KCML licence file is needed to permit the use of this library. The library must be able to load the Oracle OCI Instant Client library so this must be installed on the application server and the library (oci.dll for Windows or libclntsh.so for Unix) must be visible on the path.

When a table is opened it must be done on a handle allocated to that connection so that KCML knows to access Oracle and not the native database. The userid and password fields will be required for Oracle and generally you will want to have one secret special user with read and write permission for programatic access so that normal users do not have write access to tables under their own accounts.

KDB Functionality not supported

• CREATE DATABASE does not apply.
• KI_CREATE, KI_EXTEND, KI_GROW are not supported
• KI_READ_HOLD_NEXT is not supported.

Catalog

KCML maintains a catalog table K$CATALOG2 containing the schema and index information for the tables created by KDB. Only tables in the catalog can be opened with KI_OPEN and accessed with the KDB API. The catalog is created automatically on the first KI_CONNECT for a schema.

Creating tables

Tables are created in the usual way with CREATE TABLE and CREATE INDEX using exactly the same SQL grammar as used in KDB. KCML will convert the native SQL grammar into the Oracle dialect. The table schema is preserved in the KDB catalog and can be recovered with KI_DESCRIBE_COL. The KDB API can only be used against tables created this way. Other Oracle tables can only be accessed with direct SQL.

Opening tables

At the time the table is opened by KI_OPEN KCML will import index and dictionary information from the catalog. Path numbers will be given to the indices on the basis of the order they appear in the dictionary that is in index name order. Note that Oracle table names are not in a tree structure and are upper case.

Sequential access

To process a table sequentially you must call KI_START or KI_START_BEG. This causes KCML to issue a SELECT * statement for all the columns in that table ordered by the appropriate key path. The columns are bound to internal KCML buffers. For each subsequent KI_READ_NEXT a fetch will be issued which copies the data into the bound buffers. KCML will then pack the row buffer passed in the call using standard KCML data types. Therefore the fact that Oracle uses different data types is transparent to the KCML program and no code needs to be changed to move a table from KCML to Oracle.

For rowset operations that traverse one table sequentially joining a second table manually as it were by doing a KI_START on the second table there can be problems with performance on big tables. This is particularly acute when the join key has multiple segments as is normally the case. The Oracle optimise will always perform a table scan when presented with an open ended start operation like this and therefore to avoid significant delays in producing the first row you should consider recoding to use KI_START_ON which limits the result set to only those rows that match one or more key segments exactly or KI_START_BETWEEN to set an upper bound on the key. You can also tell Oracle that you only want a limited number of rows by supplying a maximum row count on any start operation. In general KI_START_ON or KI_START_BETWEEN should always be used in preference to KI_START if the circumstances permit.

Random access and updates

To read a table randomly KCML will issue a SELECT* and an immediate fetch, possibly with a row lock for a KI_READ_HOLD. A KI_REWRITE does a SELECT FOR UPDATE. A KI_UNLOCK will be ignored and you should instead rollback the transaction with THROW ROLLBACK if that is appropriate.

The rowid variable ki_dataptr$ returned from many library functions that identifies a row must be at least _KDB_MAX_ROWID_LEN bytes to hold an Oracle ROWID.

Transactions

To get good performance it is essential to enclose updates inside a transaction defined by a WHILE TRANS statement block. KCML will auto-commit if the database is changed outside an explicit transaction but the overhead for this in Oracle is prohibitive.

It is the programmers responsibility to check status codes returned by KDB API calls and to THROW ROLLBACK to cancel the transaction if an error is detected that cannot be corrected.

OLTP transactions should complete as fast as possible as any resources updated will be locked while the transaction is in progress. Displaying a form or blocking on a socket read is not allowed inside a transaction.

Dead locks

Oracle will detect a deadly embrace and KDB will catch it automatically rolling back the transaction and restarting it at the start of the WHILE TRANS. However most deadlocks can be designed out by having conventions for the order in which resources are locked inside a transaction.

Locks

Locks in Oracle require a transaction. KDB provides the concept of a long term lock for cases where you want to lock a resource outside a transaction e.g. to lock a customer record while it is being updated by a user. KDB will automatically promote a long term lock to a transactional lock when the row is updated by a later KI_UPDATE in a transaction.

Performance

Never generate open ended sequential result sets unless you intend to table scan.

Consider reducing the number of columns retrieved by KDB calls by using views, either implicit views established for rows with DEF VIEW or explicitly using KI_SET_COL_LIST at runtime.

Consider writing report applications using DEF QUERY which uses explicit SQL for joins.

Consider caching read-only tables locally on the application server.

In general Oracle will perform significantly slower than the same operation applied to a native KCML database. This will be most apparent on updates. This is due to extra overhead in compiling and optimising dynamic SQL, binding columns unnecessarily, executing SQL in an non-optimal way, and transactional costs.