Db2 tables for SQL DI

When you run the sample DSNTIJAI job to configure your Db2 system for SQL DI, the job creates a set of tables that are used to record and store metadata for AI objects, object models, and tables.

The sample DSNTIJAI job creates the following Db2 tables, tablespaces, and indexes for SQL DI:

Table 1. Db2 tables, tablespaces, and indexes for SQL DI
Table	Description	Tablespace	Index	Index field
SYSAIDB.SYSAIOBJECTS	Contains a row for each Db2 table or view you select for an SQL DI AI object	SYSTSAIO	SYSAIOBJECTSIX1	OBJECT_ID
SYSAIDB.SYSAIOBJECTS		SYSTSAIO	SYSAIOBJECTSIX2	SCHEMA, NAME
SYSAIDB.SYSAICONFIGURATIONS	Contains a row for each configuration ID for an AI object and related attributes	SYSTSAIC	SYSAICONFIGURATIONSIX1	CONFIGURATION_ID
SYSAIDB.SYSAICOLUMNCONFIG	Contains a row for each column and related attributes within a column configuration	SYSTSAID	SYSAICOLUMNCONFIGIX1	CONFIGURATION_ID, COLUMN_NAME, COLUMN_AISQL_TYPE
SYSAIDB.SYSAIMODELS	Contains a row for each AI object model and related table and state information	SYSTSAIM	SYSAIMODELSIX1	MODEL_ID
SYSAIDB.SYSAICOLUMNCENTERS	Contains a row for each column centroid for a trained model	SYSTSAIE	SYSAICOLUMNCENTERSIX2	MODEL_ID, COLUMN_NAME, CENTROID
SYSAIDB.SYSAITRAININGJOBS	Contains a row for each training job that you initiate and job status information	SYSTSAIT	SYSAITRAININGJOBSIX1	TRAINING_JOB_ID
SYSAIDB.SYSAITRAININGJOBS		SYSTSAIT	SYSAITRAININGJOBSIX2	OBJECT_ID, CONFIGURATION_ID, MODEL_ID
SYSAIDB.SYSAIRETRAINLOG	Contains a row for the log and status information for each model retraining job that you initiate	SYSTSAIL	SYSAIDB.SYSAIRETRAINLOGIX1	RETRAIN_LOG_ID

SYSAIDB.SYSAIOBJECTS

The SYSAIDB.SYSAIOBJECTS table contains a row for each Db2 table or view you select for an SQL DI AI object.

Column name	Data type	Description
OBJECT_ID	BIGINT NOT NULL	A unique identifier for the AI object.
OBJECT_NAME	VARCHAR(32)	(Reserved) The name of the AI object.
OBJECT_TYPE	CHAR(1)	An identifier that identifies a Db2 table or view: T Specifies a Db2 table V Specifies a Db2 view
SCHEMA	VARCHAR(128) NOT NULL	The schema of the source Db2 table or view for the AI object.
NAME	VARCHAR(128) NOT NULL	The name of the source Db2 table or view for the AI object.
STATUS	VARCHAR(16) NOT NULL	The status of the AI query enabling process: Enabled Indicates that the AI object is enabled with AI query and that the row for the AI object model is populated. Disabled Indicates that the AI object is not enabled with AI query and that the row for the AI object model is not populated. Training Indicates that the AI object is being enabled with AI query and that the row for the AI object model is being updated. Failed Indicates that the AI query enabling process for the AI object failed.
CONFIGURATION_ID	BIGINT	The identifier for the configuration used for the active model. A null value indicates that there is no active configuration yet.
MODEL_ID	BIGINT	The identifier for the active model. A null value indicates that there is no active model table created yet.
CREATED_BY	VARCHAR(32) (With SESSION_USER as default)	The SQLID of the user to which the object is registered.
CREATED_DATE	VARCHAR(32) (With CURRENT TIMESTAMP as default)	The timestamp when the object was registered.
LAST_UPDATED_BY	VARCHAR(32) (With SESSION_USER as default)	The SQLID of the user who last updated the object.
LAST_UPDATED_DATE	TIMESTAMP (With ROW CHANGE TIMESTAMP as default)	The timestamp when the object was last updated.
DESCRIPTION	VARCHAR(256)	(Reserved) The description of the AI object.

SYSAIDB.SYSAICONFIGURATIONS

The SYSAIDB.SYSAICONFIGURATIONS table contains a row for each configuration ID for an AI object and related attributes.

Column name	Data type	Description
CONFIGURATION_ID	BIGINT NOT NULL	A unique identifier for this configuration.
NAME	VARCHAR(32)	A user-defined name for the configuration.
OBJECT_ID	BIGINT NOT NULL	The identifier of the object for which this configuration is created.
RETRAIN_INTERVAL	INTEGER	The interval at which retraining occurs.
KEEP_ROWIDENTIFIER_KEY	CHAR(1) NOT NULL	An indicator for the presence of the row identifier key in a model: Y Indicates that the row identifier key is kept in the model. N Indicates that the row identifier key is not kept in the model.
NEGLECT_VALUES	VARCHAR(1024)	A semicolon-separated string of values to be treated as null in the model.
CREATED_BY	VARCHAR(32) (With SESSION_USER as default)	The SQLID of the user to which the object is registered.
CREATED_DATE	VARCHAR(32) (With CURRENT TIMESTAMP as default)	The timestamp when the object was registered.
LAST_UPDATED_BY	VARCHAR(32) (With SESSION_USER as default)	The SQLID of the user who last updated the object.
LAST_UPDATED_DATE	TIMESTAMP (With ROW CHANGE TIMESTAMP as default)	The timestamp when the object was last updated.

SYSAIDB.SYSAICOLUMNCONFIG

The SYSAIDB.SYSAICOLUMNCONFIG table contains a row for each column and related attributes within a column configuration.

Column name	Data type	Description
CONFIGURATION_ID	BIGINT NOT NULL	A unique identifier for the column configuration.
COLUMN_AISQL_TYPE	CHAR(1) NOT NULL	A SQL DI data type that you assign to a column in the column configuration: K Indicates that a column is assigned the `key` data type. C Indicates that a column is assigned the `categorical` data type. N Indicates that a column is assigned the `numeric` data type. I Indicates that a column is not assigned a data type. U Indicates that a column is assigned an unsupported data type
COLUMN_NAME	VARCHAR(128) NOT NULL	The name of the column in the column configuration.
COLUMN_PRIORITY	CHAR(1)	(Reserved) The processing priority that you assign to a column in the column configuration: H Indicates an high priority. M Indicates a medium priority. L Indicates a low priority.
COLUMN_VECTOR_CARDINALITY	BIGINT NOT NULL (With -1 as default)	The cardinality of the vectors in the VECTOR column in the vector table.
MAX_DATA_VALUE_LEN	INTEGER NOT NULL (With -1 as default)	The maximum length of the value for the column in the vector table.
NEGLECT_VALUES	VARCHAR(1024)	A semicolon-separated string of values to be treated as null in the model.

SYSAIDB.SYSAIMODELS

The SYSAIDB.SYSAIMODELS table contains a row for each AI object model and related table and state information.

Column name	Data type	Description
CONFIGURATION_ID	BIGINT NOT NULL	A unique identifier for the configuration that is used to create this model.
MODEL_CODE_LEVEL	CHAR(32) NOT NULL (With ' ' as default)	The code level for training the model.
MODEL_ID	BIGINT NOT NULL	A unique identifier for the model.
NAME	VARCHAR(32)	A user-defined name for the model.
OBJECT_ID	BIGINT NOT NULL	An identifier of the object for which this configuration is created.
VECTOR_TABLE_CREATOR	VARCHAR(128)	The name of the user who created the vector table.
VECTOR_TABLE_NAME	VARCHAR(128) NOT NULL	The name of the vector table.
VECTOR_TABLE_STATUS	CHAR(2) NOT NULL	The status of the vector table. I Indicates that the table is initialized for the current process. L Indicates that the table is loading. A Indicates that the table is available for use. E Indicates that the table is in error state.
VECTOR_TABLE_DBID	SMALLINT NOT NULL	The internal identifier of the vector table database.
VECTOR_TABLE_OBID	SMALLINT NOT NULL	The internal identifier of the vector table.
VECTOR_TABLE_IXDBID	SMALLINT NOT NULL	The internal identifier of the vector table index database.
VECTOR_TABLE_IXOBID	SMALLINT NOT NULL	The internal identifier of the vector table index.
VECTOR_TABLE_VERSION	SMALLINT NOT NULL	The internal format number of the vector table.
METRICS	CLOB(500K)	A JSON object to store metrics about the model for display in the user interface.
INTERPRETABILITY_ OCCURENCE_STRUCT	BLOB(2G)	Reserved.
CREATED_BY	VARCHAR(32) (With SESSION_USER as default)	The SQLID of the user who created the model.
CREATED_DATE	TIMESTAMP (With CURRENT TIMESTAMP as default)	The timestamp when the model was created.
LAST_UPDATED_BY	VARCHAR(32) (With SESSION_USER as default)	The SQLID of the user who last updated the model.
LAST_UPDATED_DATE	TIMESTAMP (With ROW CHANGE TIMESTAMP as default)	The timestamp when the model was last updated.
MODEL_ROWID	ROWID NOT NULL	A rowid column to support a LOB table.
DB2_CODE_LEVEL	INTEGER (With 0 as default)	The Db2 PTF level for the retraining job.
TOTAL_ROWS_PROCESSED	BIGINT	The number of rows that are processed by the initial training job.
ROW_CHANGE_TIMESTAMP_MAX_VALUE	TIMESTAMP NOT NULL	Reserved for future use (The latest time when the object table was updated).

SYSAIDB.SYSAICOLUMNCENTERS

The SYSAIDB.SYSAICOLUMNCENTERS table contains a row for each column centroid for a trained model.

Column name	Data type	Description
MODEL_ID	BIGINT NOT NULL	The unique identifier of the model to which the centroid belongs.
COLUMN_NAME	VARCHAR(128) NOT NULL	The name of the column to which the centroid belongs.
CLUSTER_MIN	FLOAT NOT NULL	The numeric center of a cluster.
LABEL	VARCHAR(5) NOT NULL	The label of the vector corresponding to the cluster.

SYSAIDB.SYSAITRAININGJOBS

The SYSAIDB.SYSAITRAININGJOBS table contains a row for each training job that you initiate and job status information.

Column name	Data type	Description
TRAINING_JOB_ID	BIGINT NOT NULL	A unique identifier for the model training job.
OBJECT_ID	BIGINT NOT NULL	The identifier for the object for which the model is being trained.
CONFIGURATION_ID	BIGINT NOT NULL	The identifier for the configuration that is used for the model training.
MODEL_ID	BIGINT NOT NULL	The identifier for the model that is created as a result of training.
STATUS	CHAR(2) NOT NULL	The status of the model training for the object: I Indicates that the training process is being initialized. L Indicating that the data is being loaded for the training job. P Indicates that the data is being processed. T Indicates that the training is started. C Indicates that the training process is completed. F Indicates that the training process failed.
PROGRESS	SMALLINT NOT NULL	The percentage of the training process completed.
RESOURCE	VARCHAR(512) NOT NULL	A JSON object that describes the resources allocated to the training job.
MESSAGES	CLOB(8K)	The output of the training job.
START_TIME	TIMESTAMP NOT NULL	The start time of the training job.
END_TIME	TIMESTAMP	The end time of the training job. A null value indicates that the training job has not yet completed.
CREATED_BY	VARCHAR(32) (With SESSION_USER as default)	The SQLID of the user who initiated the training job.
CREATED_DATE	TIMESTAMP (With CURRENT TIMESTAMP as default)	The timestamp when the training job started.
LAST_UPDATED_BY	VARCHAR(32) (With SESSION_USER as default)	The SQLID of the user who last updated the training job.
LAST_UPDATED_DATE	TIMESTAMP (With ROW CHANGE TIMESTAMP as default)	The timestamp when the training job was last updated.

SYSAIDB.SYSAIRETRAINLOG

The SYSAIDB.SYSAIRETRAINLOG table contains a row for the log and status information for each model retraining job that you initiate.

Column name	Data type	Description
RETRAIN_LOG_ID	BIGINT NOT NULL	A unique identifier for the model retraining job.
OBJECT_ID	BIGINT NOT NULL	The identifier for the object for which the model is being retrained.
CONFIGURATION_ID	BIGINT NOT NULL	The identifier for the configuration that is used for the model retraining job.
MODEL_ID	BIGINT NOT NULL	The identifier for the model that is being retrained.
RETRAIN_STATUS	CHAR(10) NOT NULL	The status of the model retraining job: ACT_FAILED Indicates that model retraining activation failed. ACTIVATED Indicates that model retraining is activated. ACTIVATING Indicates that model retraining is being activated. FAILED Indicates that model retraining failed. STOPPED Indicating that model retraining is stopped. TRAINED Indicates that model retraining completed successfully. TRAINING Indicates that model retraining is in progress.
RETRAIN_TYPE	CHAR(1) NOT NULL	Model retraining type: F Indicates that model retraining uses the full data set as input.
TOTAL_ROWS_PROCESSED	BIGINT	The number of rows that are processed by the retraining job.
CLONE_TABLE_CREATOR	VARCHAR(128) NOT NULL	The SQLID of the user who created the clone table.
CLONE_TABLE_NAME	VARCHAR(128) NOT NULL	The name of the clone table.
DATA_RANGE	CLOB(2048)	Reserved for future use (A JSON object that stores the data range to be used for model retraining).
METRICS	CLOB(10M)	A JSON object that stores the model metrics to be displayed on the UI.
MESSAGES	CLOB(8K)	A JSON object that stores messages generated by the retraining job.
RETRAIN_START_TIME	TIMESTAMP NOT NULL	The start time of the retraining job.
RETRAIN_END_TIME	TIMESTAMP	The end time of the retraining job.
CREATED_BY	VARCHAR(32) NOT NULL (With SESSION_USER as default)	The SQLID of the user who initiated the retraining job.
CREATED_DATE	TIMESTAMP NOT NULL	The timestamp when the retraining job started.
LAST_UPDATED_BY	VARCHAR(32) NOT NULL (With SESSION_USER as default)	The SQLID of the user who last updated the retraining job.
LAST_UPDATED_DATE	TIMESTAMP NOT NULL	The timestamp when the training job was last updated.
MODEL_CODE_LEVEL	CHAR(32) NOT NULL (With ' ' as default)	The model training code level for the retraining job.
DB2_CODE_LEVEL	INTEGER (With 0 as default)	The Db2 PTF level for the retraining job.