Working with KCML temporary files and tables

KCML temporary files and tables are intended as a convenient way to ensure that these files and tables are removed when a program has finished with them, even if the program terminates abnormally. On Unix platforms this is achieved by unlinking the files from the filesystem after they have been opened. This means that although they exist and use disk space (as seen by df) they do not appear in the filesystem's directories (as seen by ls). On the Microsoft Windows operating system this is achieved using a special flag that indicates the files should be removed when they are closed, so unlike Unix they continue to appear in the filesystem's folders.

Temporary files

Temporary files are identified as having filenames starting with three hashes (###). They can be created with OPEN# and they will be removed by CLOSE#.

Example

OPEN #stream,"###tempfile","W","0666"
...
CLOSE #stream

Temporary KISAM files

Temporary KISAM files are identified by having names starting with three hashes (i.e. ###). They can be created with KI_CREATE and opened with KI_OPEN. They are removed by KI_CLOSE.

Temporary KDB tables

Temporary KDB tables are identified by having names starting with three hashes (###), can be created with CREATE TABLE, and manipulated with CREATE TABLE, CREATE INDEX, INSERT, DELETE, SELECT etc. They can then be opened with KI_OPEN and removed with KI_CLOSE.

Tables created with CREATE TABLE are removed as soon as they are opened with KI_OPEN. The handle they are opened on can be used to add rows to the table. It may not appear possible to subsequently issue a CREATE INDEX or INSERT, DELETE, SELECT etc as the table has already been removed. However, so long as a handle is still open on the table these commands are still possible. The table can be identified either as the "###...." name it was created with or, more efficiently as "#n" where n is the handle that the table is open on.

Tables created with CREATE TABLE within a temporary TABLESPACE have the leading three hashes (###) added automatically. Temporary tables created in an Oracle database will have the leading # characters replaced with $ characters.

It is possible to redirect temporary tables from an Oracle database to a KDB database for performance reasons. The table will then be created in the filesystem of the application server and will only be visible to the process that created it but access will be faster as there will be no network delays. The KI_REDIR_CONNECT call is used to nominate the KDB connection to be used for temporary tables. The KDB handle to be used must already have been allocated. KI_OPEN will spot the leading # characters and redirect automatically to the nominated connection but when the table is created or dropped using DDL the SQL must be processed on the KDB handle directly.

Example

sql$ = "CREATE TABLE ""###temp"" (PARTNUM INTEGER(4) DEFAULT 900, PID VARCHAR(8), SNAME VARCHAR(20) OCCURS 2 DEFAULT 'Nicklaus', bavg NUMERIC(7,2) OCCURS 5 DEFAULT 15.75) TYPE 7, RECLEN 200"
CALL KI_PREPARE ki_handle,sql$ TO ki_status
CALL KI_EXECUTE ki_handle TO ki_status
CALL KI_CLOSE ki_handle TO ki_status
REM Put an index on the working table
sql$ = "CREATE UNIQUE INDEX ""###temp_A1"" ON ""###temp"" (PID)"
CALL KI_PREPARE ki_handle,sql$ TO ki_status
CALL KI_EXECUTE ki_handle TO ki_status
CALL KI_CLOSE ki_handle TO ki_status
CALL KI_OPEN ki_handle,"###temp","W" TO ki_status
...
CALL KI_CLOSE ki_handle