CREATE TABLE

CREATE TABLE tablename (coldesc [, coldesc] ...) [partspec] [tableparm [, tableparm] ...]
CREATE TABLE tablename [TABLESPACE ts] [partspec] AS SELECT * FROM othertable CREATE TEMPORARY TABLE tablename (coldesc [, coldesc] ...) [tempspec] [partspec] [tableparm [, tableparm] ...]

This SQL statement is used in the first form to create an empty table to the given specification or in the second form to create a table as a copy of an existing table. The third form is used to create a SQL temporary table (see below).

[NAME 'string'] [ROWS integer] [RECLEN integer ] [EXTENT integer] [TYPE integer] [PACKING integer] [BLOCKLEN integer] [NOEDIT 'string'] [[PATH 'string'] | [TABLESPACE "tablespacename"]] [SERIAL mode [colname]] [USERSTAMP colname] [TIMESTAMP colname] [TYPESTAMP colname] [UUIDSTAMP colname] [IPREFIX 'string'] [EXCLUDE COLUMN colname 'string'] [CASE 'string'] [UPDATE 'string'] [REVOKE ( operation )]

PARTITION BY partcol VALUES (partval)

ON COMMIT DELETE ROWS ON COMMIT PRESERVE ROWS default: ON COMMIT DELETE ROWS

The datatype associated with a column is a reserved word as described in this table.

Note that the optional NAME tableparm is needed to create the dictionary but the other tableparms are only required to create a table. Columns should be defined in the left to right order that the columns are laid out in the row buffer. The column attributes can occur in any order.

STARTBYTE	Used to define the 3 byte integer START field in the dictionary. The start byte field in the dictionary can be deduced from the sizes of previous fields, but to deal with overlaps and gap it can be fixed with this expression. When writing SQL from the dictionary kconvdd will only generate this clause if the currently calculated offset differs from the value in START.
ALIAS	Used to define optional ALIAS dictionary field. If not present this field will be blank. Max 8 characters
NAME	Used to define optional LNAME dictionary field. If not present leave this field blank. When generating SQL surround in single quotes as embedded blanks, parentheses and commas are allowed. Max 20 characters.
OCCURS	Defines the OCCURS dictionary field. If not present leave this field blank. Calculate the new start byte from the product of this value and the individual lengths. Set the dictionary field to blank if not specified. Use leading zeroes when generating a (three byte) dictionary field. Only generate in the SQL if dictionary field is non blank.
PRIORITY	Defines the UPFLAG, DSFLAG and RPFLAG dictionary fields. If not present in the SQL then these fields will be left blank. Sets minimum priorities for update, display and reporting. The priorities are single digit integers 0 to 9 or blank.
RANGE	Defines the RANGEFR and RANGETO dictionary fields. Field is expressed in the statement as an ascii number. If not present in the SQL leave these fields blank. Originally intended to set max and min numeric values for a column but as it was implemented using native doubles it is not portable between machines with different byte orders. Do not use.
TOTALLED	This defines the optional TOTALLED dictionary field which can only apply to number fields. The single character dictionary field can be blank (not applicable), Y if this clause present, or N if applicable and not present. The clause will be generated only if the dictionary entry is "Y".
SPECIAL	Defines the USER dictionary field for user defined text. If not present in the SQL this field will be blank.
VALIDATE	Defines the VALTYPE and VALSTR fields in the dictionary. If not present then both fields in the dictionary will be set blank. If the datatype is BIT then this implies a VALTYPE of "C" and a VALSTR of "YN"
OVERLAPS	Defines OVFIELD in the dictionary. The optional integer expression is used to define the OVOFFSET field. Some dictionary rows define fields that overlap others and such rows will have this OVERLAPS field set to the COLUMN that is overlapped. Only if this field is blank should the row be used in calculating start bytes and row lengths. The overlap amount is counted from one not zero so if the BY clause is missing then assume 1. When reading dictionary entries only inspect this field if the OVFIELD is no blanks as it appears to be set to 0x2020 as often as not.
NULL, NOT NULL	This defines if a NULL value is allowed. It is not currently enforced in KDB and there is no schema entry so it will be accepted by the SQL parser and discarded. However with an Oracle table it can be used to disallow blank VARCHAR or BLOB columns.
NO UPDATE
DEFAULT	Specifies a DEFAULT value to go in the default row. If missing a basic default of zero for numerics, spaces for strings, false for booleans and ALL(00) for HEX will be assumed. A non-blank DEFAULT clause is required for mandatory, i.e. NOT NULL, VARCHAR datatypes. It should not be used with BLOB datatypes. For HEX(n) datatypes the default value in quotes should be a hexadecimal representation exactly twice the size of the column.
LANGUAGE	This marks the fields contents for processing with locale and language aware database functions. E.g collation sequence based on the current user locale. See KI_SET_LANGUAGE

STARTBYTE

Used to define the 3 byte integer START field in the dictionary. The start byte field in the dictionary can be deduced from the sizes of previous fields, but to deal with overlaps and gap it can be fixed with this expression. When writing SQL from the dictionary kconvdd will only generate this clause if the currently calculated offset differs from the value in START.

ALIAS

Used to define optional ALIAS dictionary field. If not present this field will be blank.
Max 8 characters

NAME

Used to define optional LNAME dictionary field. If not present leave this field blank. When generating SQL surround in single quotes as embedded blanks, parentheses and commas are allowed. Max 20 characters.

OCCURS

Defines the OCCURS dictionary field. If not present leave this field blank. Calculate the new start byte from the product of this value and the individual lengths. Set the dictionary field to blank if not specified. Use leading zeroes when generating a (three byte) dictionary field. Only generate in the SQL if dictionary field is non blank.

PRIORITY

Defines the UPFLAG, DSFLAG and RPFLAG dictionary fields. If not present in the SQL then these fields will be left blank. Sets minimum priorities for update, display and reporting. The priorities are single digit integers 0 to 9 or blank.

RANGE

Defines the RANGEFR and RANGETO dictionary fields. Field is expressed in the statement as an ascii number. If not present in the SQL leave these fields blank. Originally intended to set max and min numeric values for a column but as it was implemented using native doubles it is not portable between machines with different byte orders. Do not use.

TOTALLED

This defines the optional TOTALLED dictionary field which can only apply to number fields. The single character dictionary field can be blank (not applicable), Y if this clause present, or N if applicable and not present. The clause will be generated only if the dictionary entry is "Y".

SPECIAL

Defines the USER dictionary field for user defined text. If not present in the SQL this field will be blank.

VALIDATE

Defines the VALTYPE and VALSTR fields in the dictionary. If not present then both fields in the dictionary will be set blank. If the datatype is BIT then this implies a VALTYPE of "C" and a VALSTR of "YN"

OVERLAPS

Defines OVFIELD in the dictionary. The optional integer expression is used to define the OVOFFSET field. Some dictionary rows define fields that overlap others and such rows will have this OVERLAPS field set to the COLUMN that is overlapped. Only if this field is blank should the row be used in calculating start bytes and row lengths. The overlap amount is counted from one not zero so if the BY clause is missing then assume 1. When reading dictionary entries only inspect this field if the OVFIELD is no blanks as it appears to be set to 0x2020 as often as not.

NULL,
NOT NULL

This defines if a NULL value is allowed. It is not currently enforced in KDB and there is no schema entry so it will be accepted by the SQL parser and discarded. However with an Oracle table it can be used to disallow blank VARCHAR or BLOB columns.

NO UPDATE

DEFAULT

Specifies a DEFAULT value to go in the default row. If missing a basic default of zero for numerics, spaces for strings, false for booleans and ALL(00) for HEX will be assumed. A non-blank DEFAULT clause is required for mandatory, i.e. NOT NULL, VARCHAR datatypes. It should not be used with BLOB datatypes. For HEX(n) datatypes the default value in quotes should be a hexadecimal representation exactly twice the size of the column.

LANGUAGE

This marks the fields contents for processing with locale and language aware database functions. E.g collation sequence based on the current user locale. See KI_SET_LANGUAGE

These define attributes of the actual table to be created. With the exception of NAME they are not used in the data dictionary specification. The optional NAME clause is used to create a $NAME field in the dictionary whose LNAME field is the 20 character value of the string which is used to describe the purpose of the table. If not present in the ascii a $NAME row will still be generated containing a blank field.

RECLEN	The required row length. If not given it will be deduced from the column specifiers but if given, it must be big enough to encompass all the columns so specified.
ROWS	The maximum number of rows required in the first extent (default 100).
EXTENT	A percentage figure from 0 to 155 defining the amount by which a table grows on auto-extension. For type 7 tables the calculation is based on the total number of pages already in the table, whilst for earlier table types the calculation is made on the maximum number of rows currently allowed in the table. If not present the default value is 50. If set to zero the table will not extend and will raise an error. Use KI_SET_EXTENT to change this value for an existing table.
TYPE	This is one of the integers 3,4,5,6 or 7 and defines the type of KISAM table required for applications that require backward compatibility. If not specified then type 7 is assumed.
PACKING	A percentage figure from 50 to 100 indicating the packing factor for index blocks. If not supplied 50 (75 for type 7 tables) will be used which would be appropriate for a volatile file. Use 100 if you do not expect any inserts after the table has been loaded.
BLOCKLEN	For KDB7 tables this defined the page size for the table in units of 1024 bytes. The default is 8. See comments below. For KDB6 tables this is an integer from the set of 2,4,or 8 indicating the size of an index block in units of 256 bytes.
SERIAL	Indicates which column, if any, is to be used to hold serial numbers for the table. The permissible modes are A, P, M and N, for automatic, partitioned, manual and none respectively. Modes A, P and M require a valid column name, which must be of type INTEGER(4). Choosing automatic mode causes the database to insert consecutively increasing values, starting from 1, into the choosen column whenever a row is inserted into the table. Partitioned mode is like automatic but only applies to partitioned tables and specifies a separate sequence for each partition rather than the automatic setting of one for all partitions.
PATH	Allows the user to specify a file path to be used when the table is created. If a path is not supplied the table will be created in the current working directory, i.e. the directory in which the KCML executable is situated.
TABLESPACE	If a TABLESPACE is specified rather than a PATH the table will be created in the named TABLESPACE. If the TABLESPACE is a TREE tablespace the table name will be parsed to look for branch/company/module information and the table created in the appropriate directory, note, the tablename MUST conform to a specific format for the table to be created correctly in this case.
TIMESTAMP	(Oracle and KDB type 7 tables only) Allows a column to be specified for automatically timestamping rows on KI_WRITE, KI_REWRITE or KI_WRITE_PTR. The column must be of type TIMESTAMP. By default timestamping is turned on when the table is opened by KI_OPEN but can be temporarily turned off with a call to KI_SET_TIMESTAMP. Only one column can be specified for timestamping.
TYPESTAMP	(Oracle tables only) Allows a column to be specified for automatically change-type marking rows on KI_WRITE, KI_REWRITE or KI_WRITE_PTR. The column must be of datatype UNSIGNED INTEGER(1). The value will be one from KDB_CHANGE_TYPE_ENUM. Only one column can be specified.
USERSTAMP	(Oracle and KDB type 7 tables only) Allows a column to be specified for userstamping rows on KI_WRITE, KI_REWRITE or KI_WRITE_PTR. The column must be of type VARCHAR and be less than 255 bytes long. The username is taken from the environment variable LOGNAME at the time of KI_CONNECT and can be at most 20 characters long. By default userstamping is turned on when the table is opened by KI_OPEN but can be temporarily turned off with a call to KI_SET_USERSTAMP. Only one column can be specified for userstamping.
UUIDSTAMP	(Oracle and KDB type 7 tables only) Allows a column to be specified for uuidstamping rows on KI_WRITE, KI_REWRITE or KI_WRITE_PTR - the column will only be filled if it is empty. The column must be of type VARCHAR and be at least 36 bytes long. Only one column can be specified for uuidstamping.
DBIDSTAMP	(Oracle) Allows a column to be specified to receive the DB session UUID on tables that are used as audit tables. The column must be of type VARCHAR and be at least 36 bytes long. Only one column can be specified for uuidstamping.
IPREFIX	(Oracle and KDB type 7 tables only) Specifies an optional prefix that will be prepended to Oracle column names.
EXCLUDE COLUMN	(Type 7 tables only) Allows a column to be specified which will allow rows to be excluded from word search indices. The column must be of type CHAR(1). The parameter string is a list of hex values (up to 16) that will be checked against when determining whether the column should be excluded. If the column contains one of the values in string then word search information will not be created for that row, so for example '0a10' would indicate that if the column contained either ascii decimal 10 or 16 then the row should be excluded from word search indices.
CASE	Specifies the column name style when the table is accessed from ODBC. Values include SHORT, SHORT_LOWER, SHORT_UPPER, LONG, LONG_LOWER and LONG_UPPER. The default is LONG, which uses the column ALIAS value.
UPDATE	Specifies the table accessed mode from ODBC. Values include Y and N. The default is Y, which means the table can be updated.
REVOKE	Specifies the operations that are not permitted on this table. These include ALTER, DELETE, INSERT, SELECT and UPDATE.
ADC [ENABLE \| DISABLE]	Include or exclude the table from waking up the ADC daemon when DML is executed on the table.

RECLEN

The required row length. If not given it will be deduced from the column specifiers but if given, it must be big enough to encompass all the columns so specified.

ROWS

The maximum number of rows required in the first extent (default 100).

EXTENT

A percentage figure from 0 to 155 defining the amount by which a table grows on auto-extension. For type 7 tables the calculation is based on the total number of pages already in the table, whilst for earlier table types the calculation is made on the maximum number of rows currently allowed in the table. If not present the default value is 50. If set to zero the table will not extend and will raise an error. Use KI_SET_EXTENT to change this value for an existing table.

TYPE

This is one of the integers 3,4,5,6 or 7 and defines the type of KISAM table required for applications that require backward compatibility. If not specified then type 7 is assumed.

PACKING

A percentage figure from 50 to 100 indicating the packing factor for index blocks. If not supplied 50 (75 for type 7 tables) will be used which would be appropriate for a volatile file. Use 100 if you do not expect any inserts after the table has been loaded.

BLOCKLEN

For KDB7 tables this defined the page size for the table in units of 1024 bytes. The default is 8. See comments below.
For KDB6 tables this is an integer from the set of 2,4,or 8 indicating the size of an index block in units of 256 bytes.

SERIAL

Indicates which column, if any, is to be used to hold serial numbers for the table. The permissible modes are A, P, M and N, for automatic, partitioned, manual and none respectively. Modes A, P and M require a valid column name, which must be of type INTEGER(4). Choosing automatic mode causes the database to insert consecutively increasing values, starting from 1, into the choosen column whenever a row is inserted into the table. Partitioned mode is like automatic but only applies to partitioned tables and specifies a separate sequence for each partition rather than the automatic setting of one for all partitions.

PATH

Allows the user to specify a file path to be used when the table is created. If a path is not supplied the table will be created in the current working directory, i.e. the directory in which the KCML executable is situated.

TABLESPACE

If a TABLESPACE is specified rather than a PATH the table will be created in the named TABLESPACE. If the TABLESPACE is a TREE tablespace the table name will be parsed to look for branch/company/module information and the table created in the appropriate directory, note, the tablename MUST conform to a specific format for the table to be created correctly in this case.

TIMESTAMP

(Oracle and KDB type 7 tables only) Allows a column to be specified for automatically timestamping rows on KI_WRITE, KI_REWRITE or KI_WRITE_PTR. The column must be of type TIMESTAMP. By default timestamping is turned on when the table is opened by KI_OPEN but can be temporarily turned off with a call to KI_SET_TIMESTAMP. Only one column can be specified for timestamping.

TYPESTAMP

(Oracle tables only) Allows a column to be specified for automatically change-type marking rows on KI_WRITE, KI_REWRITE or KI_WRITE_PTR. The column must be of datatype UNSIGNED INTEGER(1). The value will be one from KDB_CHANGE_TYPE_ENUM. Only one column can be specified.

USERSTAMP

(Oracle and KDB type 7 tables only) Allows a column to be specified for userstamping rows on KI_WRITE, KI_REWRITE or KI_WRITE_PTR. The column must be of type VARCHAR and be less than 255 bytes long. The username is taken from the environment variable LOGNAME at the time of KI_CONNECT and can be at most 20 characters long. By default userstamping is turned on when the table is opened by KI_OPEN but can be temporarily turned off with a call to KI_SET_USERSTAMP. Only one column can be specified for userstamping.

UUIDSTAMP

(Oracle and KDB type 7 tables only) Allows a column to be specified for uuidstamping rows on KI_WRITE, KI_REWRITE or KI_WRITE_PTR - the column will only be filled if it is empty. The column must be of type VARCHAR and be at least 36 bytes long. Only one column can be specified for uuidstamping.

DBIDSTAMP

(Oracle) Allows a column to be specified to receive the DB session UUID on tables that are used as audit tables. The column must be of type VARCHAR and be at least 36 bytes long. Only one column can be specified for uuidstamping.

IPREFIX

(Oracle and KDB type 7 tables only) Specifies an optional prefix that will be prepended to Oracle column names.

EXCLUDE COLUMN

(Type 7 tables only) Allows a column to be specified which will allow rows to be excluded from word search indices. The column must be of type CHAR(1). The parameter string is a list of hex values (up to 16) that will be checked against when determining whether the column should be excluded. If the column contains one of the values in string then word search information will not be created for that row, so for example '0a10' would indicate that if the column contained either ascii decimal 10 or 16 then the row should be excluded from word search indices.

CASE

Specifies the column name style when the table is accessed from ODBC. Values include SHORT, SHORT_LOWER, SHORT_UPPER, LONG, LONG_LOWER and LONG_UPPER. The default is LONG, which uses the column ALIAS value.

UPDATE

Specifies the table accessed mode from ODBC. Values include Y and N. The default is Y, which means the table can be updated.

REVOKE

Specifies the operations that are not permitted on this table. These include ALTER, DELETE, INSERT, SELECT and UPDATE.

ADC [ENABLE | DISABLE]

Include or exclude the table from waking up the ADC daemon when DML is executed on the table.

For type 7 tables the BLOCKLEN parameter is used to determine the page size of the table in kilobytes and should be in the range 8 - 16. The default value is 8 giving an 8kb pagesize and a maximum rowlength of 20 bytes less than this. To avoid memory wastage, database pages ought to be multiples of the memory page size which is usually 4kb. Whilst it is currently possible to mix table pagesizes within a database it is not recomended and the ability to do so may be removed in the future.

Temporary files can be created with CREATE TABLE. Note this isn't the same as temporary SQL tables below. For more information see temporary files.

Create a temporary table on the SQL database. The implementation currently differs between Oracle and PostgreSQL. It may be possible to reconcile these differences at somepoint in the future. At the moment KCML uses the Oracle behaviours. Oracle documentation

The column attributes of PREVAL, POSTVAL, DISPLAY and SEQUENCE and the table attributes of DATESTAMP which were in the KCML 6.00 version of KCML were withdrawn for KCML 6.30.