Database connection attributes
Numeric connection attributes can be set with KI_CONNECT_SET_ATTR and inspected with KI_CONNECT_GET_ATTR.
String connection attributes can be set with KI_CONNECT_SET_ATTR_STR and inspected with KI_CONNECT_GET_ATTR_STR.
Available attributes are listed below:
_KDB_CONN_ATTR_CACHESIZE
read-write number
This controls the size of the Oracle OCI client SQL statement cache. By default client side caching is disabled and the cache size is zero. The value of this attribute specifies the number of SQL statements that are held in the client cache. The Oracle suggested value is 20. This form of caching can improve performance but may involve extra memory in both the client application server and the database server. It must be set before the connection is established and once connected cannot be changed.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_PARTITION
read-write number
This can be used to enable or disable support for partitioning. Partitioned tables are expected to have the partition number as the last 4 digits of the table name as in BR_01_CUSTF_0001. KI_OPEN will check for the underscore and the partition number then open the table CF_01_CUSTF as a partioned table and use the partition number, here 0001, in all references to the table. The table must have been created as a partitioned table and the partitioning column specified in the CREATE TABLE statement that created it.
There are several possible values for this parameter specified here. There is also an environment variable documented here.
As partitioning is an optional Oracle feature, it is not supported in KDB and though it can be simulated on non-Oracle SQL databases. The attribute is defaulted during the connection. It is set to 0 for no partitioning in KDB (this is the only meaningful value for these databases) and 2 for simulated partitioning in PostgreSQL. For Oracle connections the value depends on whether the server supports the feature. If so then it is enabled and the attribute defaults to 1, otherwise it is set to 2 for simulated partitioning. Partitioning can be disabled for Oracle by setting the PARTITIONS environment variable to FALSE.
If you need to change this attribute to a a value other than the default it should be done immediately after connecting to the database. Do not change this attribute for a database once partitionable tables have been created.
Partitioning comes into its own when there are a number of identical tables representing say branches or departments. They can be consolidated into one big table which allows for more flexible reporting and reduces the number of distinct SQL statements need to access it thus reducing the parse overhead. This advantage is still available with simulated partitioning but the performance in general will be inferior to the database partitioning.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_XA
read-write number
Setting this attribute to TRUE enables support for distributed XA transactions and allows for autonomous transactions. By default these are disabled. For performance reasons only enable this if you need the functionality of autonomous transactions. Be aware that if XA is enabled then Oracle will timeout a blocked transaction treating it as a deadlock. In Oracle 9i that timeout period is one minute.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_LOCKS
read-write number
This controls lock checking in KI_REWRITE, KI_DELETE and KI_UNLOCK. If this attribute is set to TRUE then KCML will track locks on row and error if updates are attempted when no lock has been taken.
This can be set either before or immediately after the connection.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_VIEWS
read-write number
This controls view emulation for KDB and cached tables. If this attribute is set to TRUE then KCML will mask out columns not found in views so that KDB behaves like Oracle. See KI_SET_COL_LIST and DEFVIEW.
On Oracle systems, for KI_WRITE and KI_REWRITE operations, setting this attribute will perform a consistency check on columns outside the view in the supplied row buffer. On a KI_WRITE such columns must have default values from the current default row while on a KI_REWRITE they must be consistent with the values in the held row read from the database. If inconsistent a _KE_BADBUFFER error will result. This ensures that Oracle systems behave like KDB systems.
This can be set either before or immediately after the connection.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_DEADLOCK
read-write number
If this is set to a non-zero value then a counter is incremented on every WHILE TRANS and when the count reaches the set value, that transaction will simulate deadly embraces on the 2nd and subsequent calls to KI_READ_HOLD, KI_READ_HOLD_PTR and KI_START with a lock. The lock call will fail, the transaction will rollback and the WHILE TRANS LOOP will restart. On each restarted iteration the lock count will be incremented so that eventually all the locks in the trsansaction will be tested. The normal backoff delay and max number of retries limits are ignored in thsi mode. Deadlock simulation is only implemented for transactional SQL databases.
It is highly recommended that you set this when testing applications though it should never be set in production environments. Deadlocks can happen at any time and can be difficult to test without resorting to this sort of aid. Unless you test for rollback on deadlock it is possible to miss classic bugs such as not resetting counters at the start of the transaction and relying on their previous value outside it.
If you set the attribute to the special value of _KDB_FORCE_RETRY_TRANS then all outer WHILE TRANS blocks will be performed exactly twice simulating a clustered database server failover at the point of the commit in WHILE TRANS.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_TRANS_STATUS
read-write number
This can be used to get or set the value of the inner most WHILE TRANS status. It is intended for setting this status on databases that do not support transactions and which cannot use THROW ROLLBACK.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_ALLOWINTX
read-write number
A boolean flag which if set TRUE will allow forms to block for input from the user while in a transaction. The default is FALSE.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_SET_ENV
read-write number
Setting this attribute to TRUE enables the setting of environment variables when connecting to a service. Default behavour is for environment variables to be set only for the first service connected to.
This attribute must be set before the connection.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_CURSORCACHE
read-write number
Specifies the number of SQL execution plans that KCML can cache on a random access handle. The default value set in KI_CONNECT is 5 and the maximum is 15. Note that while this can dramatically reduce the number of hard parses, and thus increase performance, for repetitive operations, it can take up substantial memory on both the database server and the applications server. Setting this to zero will disable caching on both random and sequential access. If changed it should be done immediately after the KI_CONNECT.
This attribute provides the default value for the handle attribute _KDB_HAND_ATTR_CURSORCACHE.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_DEFER_OPEN
read-write number
This flag is set using the KDB_DEFER_ENUM enumeration. If set to _KDB_DEFER_OPEN then tables will not be opened in KI_OPEN but the open will be deferred to the first access on the table. This can be a big performance win in complex applications that open lots of tables up front. Note that the existence of the table is still checked in KI_OPEN but by setting it to _KDB_DEFER_FULLY that test too can be deferred to the first access. The default is _KDB_IMMEDIATE_OPEN, to open the table immediately in KI_OPEN, for all database types other than Oracle where _KDB_DEFER_OPEN is the default. This default is set in KI_CONNECT and if you wish to change it then that can be done at any time after the connection.
Introduced with KCML 6.64.
_KDB_CONN_ATTR_FAILOVER
read-write number
Setting this attribute to a non-zero value will make KCML simulate database server failure after the specified number of database round trips have elapsed. KCML will then disconnect and reconnect to the next database server in the KI_CONNECT DSN server list before retrying the statement or transaction. This allows you to check that your application will support transparent failover on a clustered database like Oracle's RAC.
If the value is less than 36 it will rounded up to 36. The environment variable KDB_FAILOVER can also be used to set this attribute during KI_CONNECT. It would normally be set to a number (less than 32767) but if set to the string "RANDOM" then KCML will choose randomly a number between 36 and 100 as the value.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_IN_DP_LOAD
read only number
This is a read-only boolean flag that will be set when a OCI Direct Path Load operation is in progess due to a KI_WRITE_RAW on a handle belonging to the connection.
Introduced with KCML 6.90.
_KDB_CONN_ATTR_DP_LOAD_BUFSZ
read-write number
This is the size of the buffer used by the data pump (i.e. the Oracle OCI Direct Path Loader or the PostgreSQL COPY statement) in KI_WRITE_RAW. It is in units of kilobytes and defaults to 1024. You may need to change the size, after the KI_CONNECT, to cope with very large BLOBs and generally the bigger the buffer the more efficient the loader will be.
The default is set in KI_CONNECT and it can be changed at any time after that. Setting the special value of 1 will disable the data pump forcing individual INSERTs.
Introduced with KCML 6.90.
_KDB_CONN_ATTR_PREFETCH
read-write number
This is the initial value assigned to the handle prefetch count when a handle is allocated though it can be altered on a handle by handle basis with KI_HANDLE_SET_ATTR. It controls the number of rows prefetched to the client from the Oracle database server when reading from a sequential result set. It is 20 by default. It is not currently used by PostgreSQL.
This attribute provides the default value for the handle attribute _KDB_HAND_ATTR_PREFETCH.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_RETRY_MAX
read-write number
A read-write attribute controlling the maximum number of times a WHILE TRANS loop will be retried following a deadlock. The default is 5, set when the handle is allocated, and it can be changed at any time.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_RETRY_DELAY
read-write number
A read-write attribute controlling the initial delay before a WHILE TRANS loop is retried following a deadlock. Each time the transaction is retried the delay is increased by this initial amount so the fifth loop, say, takes five times as long as the first one. The default is 500ms, set when the handle is allocated, and it can be changed at any time.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_RETRY_COUNT
read only number
A read-only attribute returning the number of times the current transaction has been retied following a deadlock. It will be zero if no deadlock has occurred and -1 if not in a transaction.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_KEY_PADDING
read-write number
A read-write attribute that controls how CHAR columns in a KI_START or KI_START_ROWkey are scanned looking for HEX(FF) characters. It has no effect on KDB and only applies to SQL databases.
If zero, the default when the handle is allocated, the key is passed through to the database as is. This appears to work as expected for Oracle even if the column is not legal UTF-8.
If set to 1 then CHAR columns are scanned looking for HEX(FF) and if found a KE_BADBUFFER error status will be returned and the START will fail. KCML will also check other columns where HEX(FF) is not legal in the first byte and throw an error if one is found.
If set to 2 then CHAR columns will be scanned for HEX(00) and HEX(FF) and, if necessary, appropriate SUBSTR() operations will be generated in the SQL to handle it. Columns that start incorrectly with HEX(FF) will be treated as insignificant, as will subsequent columns in the key.
This attribute can be changed at any time.
Introduced with KCML 6.90.
_KDB_CONN_ATTR_START_WINDOW
read-write number
This read-write attribute sets the size of the window to be used on open ended starts in KI_START, KI_START_ROW and KI_START_BEG. If zero, the default on Oracle connections, then windowing is disabled. The size is set to 20 in KI_CONNECT for PostgreSQL. Setting a window limits the potentially long delay when reading the first row when the database is building the result set to just the time it takes to get that number of rows. KCML will automatically fetch the next window when a window is exhausted so there are extra round trips to the database involved.
Windowing is not used in KI_START_BETWEEN, if the number of rows is limited in the start, or if the bClosed flag is set.
The attribute is ignored on KDB tables where start windows add no benefit.
This attribute provides the default value for the handle attribute _KDB_HAND_ATTR_START_WINDOW.
Introduced with KCML 6.90.
_KDB_CONN_ATTR_OPT_TRANS
read-write number
This read-write boolean attribute enables optimistic transactions for a connection. The default is FALSE. It can only be set for connections that support it (currently Oracle and PostgreSQL) and it must be set immediately after KI_CONNECT is called.
Introduced with KCML 6.90.
_KDB_CONN_ATTR_CACHE_ROWS
read-write number
Maximum number of rows permitted in local cache files. Default value is 65534.
This attribute can be set at any time.
Introduced with KCML 7.15.
_KDB_CONN_ATTR_FIX_MINUS
read-write number
Not implemented.
Introduced with KCML 6.90.
_KDB_CONN_ATTR_UPDATE_TX_MAX
read-write number
The number of updates and commits that will force an optimistic transaction to commit.
Introduced with KCML 7.02.
_KDB_CONN_ATTR_UPDATE_COUNT
read only number
Count of number of updates or deletes on the connection for the current transaction.
Introduced with KCML 7.02.
_KDB_CONN_ATTR_NO_COMMIT_ON_BREAK
read only number
By default any optimistic transactions are committed when breaking into a program and entering the workbench. As the developer steps through the program any optimistic transactions will be immediately committed before control is returned to the workbench. This is to prevent blocking other users which might be forced to wait for resources held in the transaction. However debuging certain issues may require that the workbench does not interfere with the transaction and so this attribute can be set, at any time, to disable the default autocommit behaviour.
Introduced with KCML 7.02.
_KDB_CONN_ATTR_READ_ONLY
read-write number
Set this before KI_CONNECT to open a read-only database
Introduced with KCML 6.90.
_KDB_CONN_ATTR_START_WINDOW_THRESHOLD
read-write number
With adaptive start windows the number of rows in the window will double when we have read this threshold number of windows. The default is zero to disable adaptive windowing.
This attribute provides the default value for the handle attribute _KDB_HAND_ATTR_START_WINDOW_THRESHOLD.
Introduced with KCML 7.02.
_KDB_CONN_ATTR_START_WINDOW_MAX
read-write number
With an adaptive start window the number of rows will double when we pass thresholds up to this maximum number of rows. A value of zero will cause KCML to limit the rows to the number that will fit in memory.
This attribute provides the default value for the handle attribute _KDB_HAND_ATTR_START_WINDOW_MAX.
Introduced with KCML 7.02.
_KDB_CONN_ATTR_RECYCLEBIN
read-write number
This enables you to toggle the Oracle Recycle Bin on and off for this connection. By default the recycle bin is off for KCML sessions.
Introduced with KCML 7.03.
_KDB_CONN_ATTR_RELOCK
read-write number
For SQL databases. If we try to relock a long term lock for our session then don't return KE_LOCKED.
Introduced with KCML 7.04.
_KDB_CONN_ATTR_RELOCKTRANS
read-write number
For SQL databases. Allow transactional lock on a row that has been long term locked by the same session.
Introduced with KCML 7.10.
_KDB_CONN_ATTR_CATALOG_CACHE
read-write number
This enables you to cache K$CATALOG entries on the application server. Must be set before the connection to the database is made.
Introduced with KCML 7.04.
_KDB_CONN_ATTR_CHECKUTF8
read-write number
For SQL databases. Check on INSERT for truncated UTF8 and replace with blanks. This is enabled by default.
Introduced with KCML 7.06.
_KDB_CONN_ATTR_NOEXPANDHINT
read-write number
Oracle database. Add NO_EXPAND hint to SQL generated by a KI_START_BETWEEN. This is disabled by default.
Introduced with KCML 7.04.
_KDB_CONN_ATTR_WSMETHOD
read-write number
Selects method to use to generate Word Search Indexes on Oracle.
Introduced with KCML 7.07.
_KDB_CONN_ATTR_TABLECOMPRESSION
read-write number
Oracle database. Combine flags to enable various compression options. If KDB_ORACLE_COMPRESS_TABLE is set then every table created with 'CREATE TABLE' will be compressed for OLTP. If ORACLE_COMPRESS_LOB is set then each LOB will be created in Oracle SecureFile format and compressed.
Introduced with KCML 7.08.
_KDB_CONN_ATTR_COMPANYPARTITION
read-write number
SQL database. Enable automatic table partitioning by a table company id.
Introduced with KCML 7.08.
_KDB_CONN_ATTR_AUTO_CATALOG_UPGRADE
read-write number
SQL database. Enable automatic upgrade of K$CATALOG. TRUE by default.
Introduced with KCML 7.14.
_KDB_CONN_ATTR_VARCHAR_NULL_FILTER
read-write number
SQL databases. When enabled, KCML will replace NULL values in inserts/updates to NOT NULL VARCHAR columns with single spaces.
Introduced with KCML 7.16.
_KDB_CONN_ATTR_CREATE_PRIMARY_KEYS
read-write number
Oracle databases. When enabled, CREATE UNIQUE INDEX with path 1 will set the relevant column(s) as a primary key for the table, provided that:
Introduced with KCML 7.16.
_KDB_CONN_ATTR_CATALOG_CACHE_LIFETIME
read-write number
How long (in seconds) KCML should cache catalog entries for. If zero (the default), no cache is maintained.
Introduced with KCML 7.17.
_KDB_CONN_ATTR_TYPE_CONVERSIONS
read-write number
Controls type conversions for columns without a specified type, like SUM() and COUNT().
Introduced with KCML 7.17.
_KDB_CONN_ATTR_AMEND_ORACLE_ROWIDS
read-write number
Oracle databases. When enabled, ROWIDs will have the high bits of the file number segment zeroed out.
Introduced with KCML 7.22.
_KDB_CONN_ATTR_MAX_CURSOR_CACHE
read-write number
Sets the number of random access execution plans that can be cached per connection. The default is 256, the maximum is determined by database configuration and setting it to zero will disable caching.
Introduced with KCML 7.22.
_KDB_CONN_ATTR_MAX_DEF_QUERY_PARAMS
read-write number
Sets the maximum number of parameters that can be bound to a DefQuery. The default is 256, the maximum is dependent on the specific DB engine in use.
Introduced with KCML 7.24.
_KDB_CONN_ATTR_ADC_PORT
read-write number
Local port number that can be used for notifications that ADC data has changed.
Introduced with KCML 7.26.
_KDB_CONN_ATTR_SHORT_ROWS
read-write number
If true, then Suppress errors introduced in KCML 07.28 where the passed row buffer is shorter than the table row length.
Introduced with KCML 7.28.
_KDB_CONN_ATTR_CLIENT_ID
read only string
This string comes from the CLIENT_IDENTIFIER column of the Oracle V$SESSION table. It can be up to 64 bytes. It is generated internally by KCML at connect time and includes a client identifier, the username from the application server and the connect id.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_CLIENT_INFO
read-write string
This string is exposed through the CLIENT_INFO column of the Oracle V$SESSION table. It can be up to 64 bytes. It must be set before KI_CONNECT.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_RESOURCE_GROUP
read-write string
This selects the Oracle Resource Consumer Group used by the session. It can be set before KI_CONNECT and changed at any time. By default "LOW", "MEDIUM" and "HIGH" resource groups are provided. For more information see the Oracle documentation.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_COMMIT_WRITE
read-write string
This sets the Oracle COMMIT_WRITE initialization parameter used by the session. It can be set before KI_CONNECT and changed at any time. A default value is taken from the spfile or pfile. Useful values could be "IMMEDIATE,WAIT" for interactive sessions and "BATCH,NOWAIT" for end of day options. For more information see the Oracle documentation.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_AUDIT_NAME
read-write string
Specifies the user name to be stamped into any USERSTAMP column when a row is updated. This will be set to the value of the read-only LOGNAME environment variable on the application server at the time of the KI_CONNECT but it can be changed after the connect.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_RO_ROLE
read-write string
Specifies a role that will be GRANTed read-only access to the database tables when they are created, e.g. for ODBC access. Users can then be created with that role. It can be set at any time.
Introduced with KCML 6.60.
_KDB_CONN_ATTR_CACHE_DIR
read-write string
A directory to use for local cache files overriding any default or environment variable specification. The directory will be created if it does not exist.
This attribute can be set at any time.
Introduced with KCML 6.90.
_KDB_CONN_ATTR_TABLESPACE_PREFIX
read-write string
Mangle application tablespace names with the prefix for database tablesapce names. E.g ACCOUNTS will be created and accessed in database as TABLESPACE_PREFIXACCOUNTS
Introduced with KCML 7.03.
_KDB_CONN_ATTR_FAILOVER_ERRORS
read-write string
Comma separated list of error numbers such as "10,100,1000". The application should fetch the existing list and append to it. The list will be sorted and duplicates removed automatically.
Introduced with KCML 7.16.