Running AI queries with SQL Data Insights

SQL Data Insights (SQL DI) is an AI-powered Db2 for z/OS® feature that provides semantic search, similarity discovery, and anomaly detection in your data. It leverages AI and advanced IBM® Z technologies to bring SQL-based semantic query to transactional data in Db2, delivering real-time insights without costly data movement and complex AI model management.

SQL DI enables you to discover semantic relationships in your data that traditional SQL queries cannot detect. Using database embedding and deep self-supervised learning, it trains a neural network model and infers semantic meanings from unique values in a user table or view. The inferred meanings in the form of numeric vectors encapsulate data relationships across columns and rows. It then uses the trained model to run AI queries, unlocking patterns, connections, and trends in records that are hidden from traditional SQL queries.

Begin figure description. SQL DI architecture. End figure description — Figure 1. SQL DI architecture

SQL DI is seamlessly integrated into your Db2 environment. The feature includes a runtime model training engine, a data and query engine, and a client application. The model training engine is powered by z/OS Spark and the z/OS AI library stack. At the core of the stack are the Z Deep Neural Network Library (zDNN) , Z AI Optimization (zAIO), and Z AI Data Embedding (zADE) libraries, which together enable SQL DI to take full advantage of IBM Z processors.

The data and query engine is built into Db2 and provides services for data processing and semantic queries. The core of this engine consists of four built-in scalar functions: AI_SIMILARITY, AI_SEMANTIC_CLUSTER, AI_ANALOGY, and AI_COMMONALITY. You can use these functions in SQL statements to ask questions about your data, identify patterns, discover relationships, and analyze commonalities.

The SQL DI client application includes a web user interface (UI), a RESTful application programming interface (API), and a shell command-line interface (CLI). You can use any of the interfaces to manage SQL DI server settings, create AI objects, enable objects for AI query, retrain object models, and perform user tasks. You can also use the API and the CLI to simplify or automate some of those tasks.

With SQL DI, you can quickly tap into the vast amount of mission-critical transactional data in Db2 and easily uncover hidden information across tables and views for actionable insights. You can achieve all these without having to move data to other platforms, deploy additional AI infrastructure, or develop specialized AI expertise.

How does SQL DI work?

SQL DI brings enterprise-grade AI analytics to your fingertips without the complexity typically associated with AI capabilities. Let's take the SQL DI workflow in the web UI as an example. After signing in, you connect SQL DI to Db2, create an AI object from a source Db2 table or view, enable the object for AI queries, and run queries on the object at any time.

To enable the object for AI queries, SQL DI preprocesses the data in the object, trains a neural network model, and loads the model into Db2. For data preprocessing, SQL DI uses z/OS Spark to convert selected columns to text, known as the AI object text data. After data transformation, SQL DI clusters numeric data type values and replaces them with cluster identifiers. In text format, every numeric or categorical data type value is tagged with its column name, and every numeric data type value is also associated with a cluster identifier.

Begin figure description. SQL DI AI query enablement. End figure description — Figure 2. SQL DI AI query enablement

After the data is processed, SQL DI uses Spark, along with the zDNN stack, to train a model with input from the AI object text data set. The training process generates numeric vectors for every unique value in the data set. The numeric vectors represent the inter-column and intra-column semantic relationships between different unique values, also referred to as vocabulary, in the object. SQL DI uses the Db2 LOAD utility to load the trained model into a Db2 table.

After successful enablement, you can run similarity, dissimilarity, clustering, analogy, or commonality queries on the object at any time. Simply select a query type, enter a SQL statement, and run the query. Depending on the query type that you select, SQL DI uses a corresponding Db2 AI function to process your query. It displays the first 50 rows of the query results in the UI and loads the remaining rows into the Db2 table. You can export the displayed rows from the SQL DI UI or download the entire result set from Db2.

During model training, SQL DI also collects key data statistics and renders them into column influence and discriminator scores for the object and the model. A column influence score correlates to the number of user-specified NULL values in a column and indicates the column's influence on the training of the object model. On the other hand, a discriminator score correlates to the number of unique values in a column and measures the column's ability to semantically distinguish its values from other values in the table. The visualized scores can help you understand the results of your AI queries on the object.

As your data changes, the accuracy of the object model may degrade over time. Whenever needed, you can retrain the model if it's eligible. By default, a new model is eligible for retraining if it is originally trained in an SQL DI environment where all the required maintenances (APARs) are already applied. For a model that exists prior to the update, you must delete it first. After applying the required maintenance and upgrading your SQL DI environment, disable the object for AI query. The disablement removes the existing model. Then, re-enable the object for AI query. Successful enablement trains a new model that is automatically eligible for retraining.

SQL DI concepts and definitions

The following concepts and definitions can help you learn, use, and administer SQL DI:

AI object

A SQL DI asset that you can create to contain a Db2 user table or view.

AI object text data

The data in an AI object that is transformed and formatted as text for AI query enablement and used as the input to model training. The number of tokens in the data are the values in every categorical, numeric, and text column that are selected for model training for the AI object.

Vocabulary

The total number of unique values in the selected SQL DI categorical columns and the number of clusters associated with the selected numeric and text columns in the AI object for AI query enablement.

SQL DI data type

A data category descriptor that SQL DI uses to classify data in a table or view in an AI object. You can assign one of the following SQL DI data types to a column in an AI object for column configuration and model training:

Categorical: The SQL DI categorical data type is used for columns with discrete values, each of which is its own entity.
Numeric: The SQL DI numeric data type is used for columns with continuous values.
Key: The SQL DI key data type is used to indicate that a column represents an entire row.

See Enabling or disabling AI query with SQL DI web UI for more information.

AI query

A semantic query that you can run on an AI object to infer hidden relationships between entities in the object. You can select from the following AI query types:

SQL DI AI query types and corresponding Db2 AI functions

SQL DI query type	Description	Corresponding Db2 AI function
Semantic analogy	An analogy query determines if the relationship between two entities applies to that of a second pair of entities.	AI_ANALOGY
Semantic clustering	A clustering query forms a cluster of entities in records and evaluates whether or not an additional entity belongs in the cluster.	AI_SEMANTIC_CLUSTER
Semantic commonality	A commonality query identifies the entities in records that exhibit the most common or uncommon patterns.	AI_COMMONALITY
Semantic dissimilarity	A dissimilarity query finds the outliers from the norm in records.	AI_SIMILARITY
Semantic similarity	A similarity query identifies groups of similar records or entities in records.	AI_SIMILARITY

See Running an AI query with SQL DI web UI for more information.

Vector prefetch

The advanced processes that SQL DI uses to upload the numeric vectors to the zAIO services for scoring calculation. The vectors are generated from an AI object text data set. The existing query processing architecture dictates that Db2 AI functions submit the vectors, record-by-record, to zAIO for processing. To significantly improve the query performance, SQL DI implements vector prefetch, which enables Db2 to upload multiple vectors in a batch at a time.

ibm-data2vec

A z/OS native AI function that SQL DI uses for model training. The ibm-data2vec function is an implementation of a self-supervised database embedding algorithm. The database embedding takes as input a text file that is created from a multi-modal relational table and builds a relationship map between text tokens by using the relational data model. The input training document generated from a relational table consists of string tokens that represent different relational entities in the original Db2 table or view. The ibm-data2vec function views the training document as a set of sentences, where each sentence represents a relational table row.

After model training is completed, ibm-data2vec generates a numeric vector of a pre-defined length (dimension) for each token, and the vector encodes the meaning of that token. The core numerical computations of the training process are paralleled by using multiple threads and accelerated by using hardware-accelerated numerical computations. The final trained model is stored as a binary file that uses the Db2 ZLOAD utility. See ibm-data2vec for more information.

base10Cluster

A z/OS numerical clustering algorithm that clusters together numerically closer items in different buckets, each of which represents a distinct cluster. SQL DI uses the base10Cluster algorithm to process an input data set and generate an output file that lists the number of buckets and their corresponding minimum values. See base10Cluster for more information.

Column influence score

A score that correlates to the number of user-specified and SQL NULL values in a column and indicates the column's influence on the training of the object model. The fewer NULL values the column has, the higher influence score it generates.

Column discriminator score

A score that correlates to the number of unique values in a column and measures the column's ability to semantically distinguish its values from other values in the table. The more unique values the column has, the higher discriminator score it generates.