Unlocking the Power of Your Data with IBM Db2 SQL Insights

Introduction

Db2 is well known, especially in the mainframe world. IBM Db2 for z/OS is a relational database management system (RDBMS) built for IBM mainframes, offering high scalability, reliability, and security. First released in 1983, Db2 has evolved over the years to keep up with changing technology. One of its latest features is Db2 SQL Insights. But what is Db2 SQL Insights, and how can it help you or your clients? Let’s find out.

As-Is Process to create AI model from Mainframe Data

Building AI models from scratch is a complex task. Training models outside the mainframe requires extracting and moving data to a distributed environment, which involves specialized skills, lengthy ETL (Extract, Transform, Load) processes, and high processing costs. This makes the entire process slow and resource intensive. But what if you could build AI models directly on the mainframe, using data from existing stores without affecting them? That’s exactly where IBM Db2 SQL Insights comes in.

What is IBM Db2 SQL Insights?

Simply put, IBM Db2 SQL Data Insights is a built-in feature in Db2 v13 that uses AI and deep learning to analyze Db2 data. It lets users run SQL queries on tables and views to find useful patterns and insights—without needing advanced data science skills.

Deep Dive into Db2 SQL Insights offering

The scalar functions, which enable users to query Db2 data, are at the heart of the Db2 SQL insights offering. These built-in SQL functions enable you to ask meaningful queries regarding your data. The table below describes four different types of scalar functions and their respective functionality.

Examples of  Scalar Functions

AI_SIMILARITY

Let's take an example of AI_SIMILARITY built-in function: A credit card provider notices that many customers are leaving (churn), and they want to identify similar customers who might also leave soon. This will help them take proactive steps, such as offering special retention deals.

Here’s an example of how IBM Db2 SQL Insights can help identify customers similar to the one who has already churned (Customer ID: 4400PQ23S). The query below finds customers with similar location, salary, age, and credit card usage patterns:

SELECT X.customerID, AI_SIMILARITY(X.customerID, ’ 4400PQ23S) AS SimilarityScore FROM DSNAIDB.CHURN X WHERE X.customerID <> ’ 4400PQ23S’ ORDER BY SimilarityScore DESC FETCH FIRST 5 ROWS ONLY

Where 4400PQ23S is id of customer who has churned out and based on this customers data patterns ex : location, salary, age, credit card usage etc. this query will list similar customers.

The output may look something like :

The results of AI_SIMILARITY is a floating-point number between -1.0 to 1.0 , where value of 1.0 means very similar and -1.0 means very dissimilar.

The output above indicates that the first customer very similar to previously churned customer, while the second and third customers share some similarities. These customers should be prioritized for proactive outreach.

This way, Db2 SQL Insights helps businesses reduce churn and improve customer loyalty—without needing complex AI expertise.

AI_SEMANTIC_CLUSTER

This function computes a semantic clustering score of a member argument against a set of clustering arguments.

For example , an airline company can send reminders to loyalty customers to use their bonus points before expiration by running the query below. In this case, customers 9102918 and 5593817 have bonus points that are nearing expiry.

SELECT A.*, AI_SEMANTIC_CLUSTER( '9102918', '5593817’) AS SIMILARITY FROM LOYALTY_CUSTOMERS ORDER BY SIMILARITY DESC FETCH FIRST 3 ROWS ONLY

AI_ANALOGY

The AI_ANALOGY function computes an analogy score between two sets of values.

For example , a customer with ID ‘3301918 has churned, as indicated by a 'Y' in the churn column. To identify customers who are unlikely to churn, look for those who share a similar relationship to 'N' in the churn column.

SELECT AI_ANALOGY('Y' USING MODEL COLUMN CHURN, '3301918’' USING MODEL COLUMN CUSTOMERID, 'N' USING MODEL COLUMN CHURN, CUSTOMERID), CHURN.* FROM CHURN ORDER BY 1 DESC FETCH FIRST 5 ROWS ONLY

AI_COMMONALITY

The AI_COMMONALITY function calculates a similarity score by comparing the expression value to the model's center value.

For example, a car insurance company may want to identify customers who show behavior patterns similar to their ideal profile — such as no claims, no traffic violations, and no missed premium payments. By executing the below query , they can generate an initial list of customers who match this pattern.

SELECT AI_COMMONALITY(99102918) AS SCORE, A.* FROM CUST_DATA_IMP A ORDER BY SCORE DESC FETCH FIRST 10 ROWS ONLY;

The Db2 SQL Data Insights – Core Concepts

IBM Db2 SQL Insights leverages two approaches to gather insights from structured data:

Self-supervised Neural Network Approach for Natural Language Processing:  Word Embedding

• Captures word meaning as collective contributions of words in the neighborhood
• Generates semantic representations of words using vectors (Vector Embedding)
• Semantic similarities between words measured using distance between vectors

Extending Vector Embedding Approach to structured databases:  Database Embedding

• Every database instance of item, irrespective of its column type, converted to a text token
• View a database record as an unordered English-like sentence (bag-of-words) of text tokens
• Extend the basic embedding approach to encode the associated relational data model
• Semantic model infers meanings (behavior) of database entities (instances of item) based on their neighboring entities (e.g., within a table row, and across table rows)
• Exploit the trained model to enable new SQL semantic queries that operate on the relational data based on the inferred meaning, not using values

Benefits of Db2 SQL Insights 

Below are the high-level benefits of using Db2 SQL Insights (non-exhaustive):

• Works “as-is” on relational tables: Utilizes a self-supervised vector embedding approach directly on the relational data without requiring any changes.
• No need to move data out: Data remains within the database, eliminating the need for labeling or transferring it for processing.
• Simple AI-enhanced semantic queries: Users can run AI-powered semantic queries using standard SQL, making it accessible without specialized skills like Machine Learning.
• Secure in-database model training and inferencing: Models are trained, and inference is done securely within the database, maintaining data privacy.
• A single model supports multiple query types: One trained model can handle a variety of different types of queries, adding flexibility and efficiency.

z16 (& zNext) and Db2 SQL Insights

IBM's z16, launched in April 2022, is a high-performance mainframe built to provide AI-driven insights, better security, and powerful computing capabilities. Powered by the Telum processor, it brings on-chip AI inferencing, enabling real-time fraud detection and analytics without affecting transaction speeds. This makes the z16 a game-changer for businesses using AI on their mainframe systems. Looking ahead, the mainframe community is excited about the upcoming zNext machine, which will bring even more AI features, with new chips like Telum II and Spyre already announced. These innovations will help clients fully take advantage of running AI workloads on the z platform. With these AI-focused improvements on Mainframe z hardware, it makes sense to run AI workloads directly on the z system and leverage tools like Db2 SQL Insights to achieve the same.

Conclusion

IBM Db2 SQL Insights is more than just a tool—it's an AI-powered assistant that helps you manage and optimize your database efficiently. By offering deep, actionable insights into SQL workloads, it enables faster, more reliable, and optimized database performance.

In today's data-driven world, staying ahead means using smart tools like SQL Insights. Whether you're a DBA, developer, or data engineer, this is the future of database management with IBM Db2 SQL Insights.

References