Vector values

Vectors are mathematical representations of structured and unstructured input data that are vital to modern artificial intelligence (AI) applications and semantic search capabilities. Db2 can now import vector data and convert that data to a format that can be queried with SQL statements.

Note: Support for the VECTOR data type is available in Db2® 12.1.2 and later.

Why is vector data important?

Many AI applications that employ machine learning models and deep learning techniques generate vectors in the form of high-dimensional vector embeddings. A high dimensional vector describes quantities of distinct objects. These applications use similarity search techniques to identify vectors with similarities within a data set for use in a reference vector. Similarity is usually measured as a distance function between two vectors, such as Euclidean distance, or cosine similarity.

Many companies are adopting AI and GenAI technologies and seeking cost-effective, secure, and a simplified solution for vector storage. To meet these needs, an application must have efficient similarity search capabilities over a set of high-dimensional vector embeddings. Typically, the number of dimensions needed for these applications range between hundreds to thousands. The number of vectors stored in the databases of these applications is usually very large, as vectors are used to represent small portions of each object. These applications need to run a large number of similarity search queries for a single user prompt to produce an answer. The expected response time for these types of queries ranges from fractions of a second to milliseconds. Vector embeddings usually maintain metadata that can be included in similarity search queries.

Why choose Db2 for storing and working with vector data?

There are databases in the marketplace that are dedicated to vector storage and efficient similarity search. However, these solutions have limitations:
  • They rely on external data repositories. When combining vectors with the original input, users have to work with multiple data repositories, which increases costs, latency, and complexity.
  • They have little or no ability to enhance queries with other filtering capabilities using the metadata associated with the vector embeddings.
  • They have limited capability in other data management areas.
Many Db2 customers already have the original input from which vectors are created right in their Db2 databases, so their vector storage solution is greatly simplified.

The query capabilities of Db2 are unmatched, and the addition of vector similarity search enhances these capabilities.

Db2 has developed industry-leading functionality in areas like scalability, security, auditability, performance, and monitoring over many years. Most vector-specific solutions just can't offer that.

For examples of practical applications of using Db2 to manage vector data, see Use cases.

The VECTOR data type

The VECTOR data type currently supports coordinate types INT8 and REAL/FLOAT32. The VECTOR data type is a fixed length type, where the number of coordinates always equals the dimension. Unlike the fixed types CHAR and BINARY, there is no padding, and all coordinates must be provided to the constructor function. A vector is only compatible with another vector of the same dimension and coordinate type.
Syntax
VECTOR (<dimension> ,<coordinate-type>)
where
  • dimension is an integer literal that specifies the dimension of the vector. For a row-organized tables, the value must be from 1 to 8168 for FLOAT32 and from 1 to 32672 for INT8 (SQLSTATE 42611). For a column-organized tables, the value must be from 1 to 8148 for FLOAT32 and from 1 to 32592 for INT8 (SQLSTATE 42611).
  • coordinate-type is either a REAL or FLOAT32 single-precision, 4-byte floating point value, or an INT8 1-byte integer.
Example
The following example shows the command syntax for creating a table, VTABLE, that contains two columns of different vector data types:
CREATE TABLE VTABLE (RV VECTOR(100, REAL), -- Coordinates are single-precision (4-byte) floating point
                     IV VECTOR(300, INT8)) -- Coordinates are 1-byte integers
This command generates the following table:
$ db2 describe table VTABLE

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
RV                              SYSIBM    VECTOR(FLOAT32)            100     0 Yes
IV                              SYSIBM    VECTOR(INT8)               300     0 Yes

  1 record(s) selected.

Vector functions and their uses

There are several vector-specific scalar functions that you can use to create and manage vectors within Db2:
  • VECTOR: Constructs a vector from an input string.
  • VECTOR_SERIALIZE: Converts a vector to a string.
  • VECTOR_DISTANCE: Calculates the distance between two vectors using the specified metric.
  • VECTOR_NORM: Calculates the distance between one vector and the zero vector using the specified metric.
  • VECTOR_DIMENSION_COUNT: Returns the dimension of the vector type definition, even if the value is null.

UPDATE and INSERT operations with vectors

The VECTOR column stores vectors in binary format. Db2 uses the string form of the vector when interacting with applications.

For example, the new VECTOR function converts a string representation of a vector into its binary form. The following example shows the command syntax for updating an existing vector value by using a host variable and the string form of a vector:

C++
EXEC SQL BEGIN DECLARE SECTION;
  // Generated by db2dclgn
  sqlint32 id;

  struct
  {
    short length;
    char  data[50];
  } f32_3;
EXEC SQL END DECLARE SECTION;

id = 1;
sprintf(f32_3, "[0.123, -0.456, 0.789]");

EXEC SQL UPDATE VECTOR_TB SET F32_3 = VECTOR(:f32_3, 3, FLOAT32) WHERE ID = :id;

EXPORT and IMPORT operations with vectors

The Db2 EXPORT and IMPORT commands support the VECTOR data type:
  • The EXPORT command writes out vector values as character strings and the IMPORT command expects input vector data values to be in string form.
  • The EXPORT command outputs string forms of vector values without unnecessary white space.
  • A small-size INT8 VECTOR type is one that has up to 6534 dimensions, whereas a small-size FLOAT32/REAL VECTOR type has up to 2041 dimensions. For these VECTOR types, the string form of a vector value that is generated by an EXPORT operation does not exceed 32672 bytes.
  • The EXPORT and IMPORT commands handle these small VECTOR types in a similar way to how they handle the VARCHAR type.
  • If a VECTOR type has more than 6534 dimensions for INT8 vectors, or more than 2041 dimensions for FLOAT32/REAL vectors, then the string form of these vectors, when generated by an EXPORT operation, can exceed 32672 bytes in length.
  • The EXPORT and IMPORT commands handle large VECTOR types in a similar way to how they handle CLOB type data, with similar usage scenarios and restrictions as how the EXPORT and IMPORT commands handle real CLOB type.
Rules for running EXPORT commands against vector data
There are options that can be included in an EXPORT statement when working with large object (LOB) data: If a LOB option is specified in an EXPORT statement, vector string values from large VECTOR columns are written into separate LOB data files, and LOB Location Specifiers (LLS) are written into the main output data file to point to the vector string values. If no LOB option is specified, vector string values are written into the main output data file, and are truncated to a maximum of 32700 bytes, if they exceed the length. Warnings for specific data rows are not issued if a value is truncated,.
Note: The IMPORT command does not insert data rows with truncated vector strings into VECTOR columns (SQL0420N). Specify a LOB option to the EXPORT command, unless you have other ways to avoid or mitigate the issue of truncated vector strings.
Rules for running IMPORT commands against vector data
  • When running an IMPORT statement against a table that has a small VECTOR column, the vector string values must reside in the main input data file, the values must not be truncated, and the values must not exceed 32672 bytes in size.
  • If the VECTOR column is large, and no LOB option is specified, then the vector string values must reside in the main input data file. Example LOB options include LOBS FROM and MODIFIED BY LOBSINFILE. Again, the vector string values must not be truncated and not exceed 32672 bytes in size.
  • If the VECTOR column is large and a LOB option is specified, then the main input data file must contain LLS that point to the vector string values in separate LOB data files.
    Note: Sometimes vector input data is not generated by the EXPORT command: It can be constructed manually or by other tools or applications. Importing this vector string data into VECTOR column types follows the same rules as importing data that is generated by the EXPORT command.
Rules that apply to both IMPORT and EXPORT operations with vector data
  • To ensure that vector values are inserted into large VECTOR columns successfully, both the EXPORT command and the corresponding IMPORT command must either use a LOB option or not use a LOB option. Not using a LOB option carries the risk of having truncated vector string values.
  • The EXPORT and IMPORT commands treat the vector string values as strings and not numbers. File type modifiers that apply to numeric values are not applicable to vector string values. Affected file type modifiers include the following:
    • decplusblank.
    • decptx.
    • implieddecimal, for IMPORT only.
    • striplzeros, for EXPORT only.
  • Exporting vector values from a VECTOR column, and then importing the vector values into a VECTOR column of a different type is not supported. Examples of different type are vectors with different dimensions or different coordinate-types.
    Note: The EXPORT and IMPORT commands are client-side features. When running the EXPORT and IMPORT commands from a remote Db2 client, the Db2 client and Db2 server instances must both be running Db2 12.1.2 or later, to ensure they both support VECTOR data type columns.

Vector limitations

The VECTOR data type is not supported in the following object types and operations:
  • Cast specification.
  • Global variable.
  • Local variable in a routine.
  • Routine parameter or return type.
  • User-defined types:
    • Distinct.
    • Array.
    • Row type.
    • Structured type.
  • Anchored data type.
  • Global temporary table columns:
    • CGTT.
    • DGTT.
  • CREATE EXTERNAL TABLE column.
  • CREATE NICKNAME.
  • CREATE TYPE MAPPING.

In addition:

  • Replication does not support a table with a VECTOR column.
  • Vectors cannot be compared.
  • LOAD and INGEST operations do not support vectors.
  • db2dart does not support dumping VECTOR columns with the /DDEL option.
  • Logical backup and restore operations do not support the VECTOR type.
  • ALTOBJ procedure does not support tables with a VECTOR column.
  • An existing column cannot be altered to a VECTOR.
  • An existing VECTOR column cannot be altered to a different data type, nor to a different coordinate-type.
  • An existing VECTOR column dimension cannot be altered.
  • The only DEFAULT value allowed is NULL, consequently a NOT NULL vector column cannot be added to an existing table.
  • A VECTOR expression is not supported in the select list of a REFRESH IMMEDIATE materialized query table.
  • Because there is no concept of greater-than or less-than when working with vectors, a VECTOR column, or an expression with a VECTOR result data type, cannot be used in the following ways:
    • As a primary key.
    • As a foreign key.
    • As a unique key.
    • As a CREATE INDEX key.
    • As an ORDER BY sort key.
    • As a DPF distribution key.
    • As a range partitioning key.
    • As an MDC key.
    • As a RCT key.
    • As a GROUP BY expression.
    • In a join condition other than IS (NOT) NULL.
    • As a SELECT DISTINCT expression.
    • As a COUNT DISTINCT expression.
    • In a set operator other than UNION ALL.

Use cases

Use case 1: Search similar patient records from a Db2 table
You can use the proposed vector support capabilities of Db2 to implement a vector similarity search over relational records.
Consider a database table within Db2 that stores patient records. Each row in the table represents a patient and includes various attributes and information about them, such as their size, health markers, medical conditions, and gender.
Recently, one patient at the table reported experiencing a heart attack. The doctor wants to identify other patients who exhibit similar characteristics to this patient. This information is important because it enables the doctor to identify patients that need further medical tests, such as an angiogram. However, it is crucial to note that the doctor does not want to send all the patients for these tests. This is because these tests can be expensive and might not be necessary for every patient.
To support this use case, the hospital’s secretary needs to find other patients who are most similar to the example patient, referred to as the query patient.
Figure 1. Using a vector in a SQL statement
Data scientists at the hospital use SQL queries to retrieve patient records from a Db2 table into a Python program. They use SQL language and their own views, as well as supported Db2 tools for Python like IBM DB and Db2 Magic commands extension for Jupyter Notebook. These tools enable them to bring all the patient records into their Python notebook.
For each record, the data scientists generate a sentence by concatenating the values from each column along with the name of the column. These sentences represent the patient records, which are then sent to a language model like RoBerta to generate a vector embedding for each sentence. At the end of this process, a single vector represents the characteristics of each patient. The data scientists alter the Patients table by adding a VECTOR column for storing these vectors.
By using UPDATE statements, they store the vectors in the VECTOR column in the patients table. Once the vectors are stored in Db2, the hospital secretary writes SQL queries using VECTOR_DISTANCE function to find the five most similar patients to the query patient.
Use case 2: Vector storage for generative AI / RAG applications
Large language models (LLM) have become increasingly popular in recent years, but they have a limitation. They can only perform well on data they were trained on. When faced with new questions, they often provide incorrect answers or make things up. To address this, a method called retrieval augmented generation (RAG) is used to enhance LLM answers. RAG utilizes a private knowledge repository where a list of relevant documents from the repository is included in the prompt to the LLM. The LLM then attempts to generate an answer based on these documents. Implementing RAG requires secure databases for storing vector embeddings and a secure repository for confidential knowledge content. This content can be sourced from PDFs, Word documents, or wiki pages. The knowledge is converted into vector embeddings and securely stored in the repository.
Db2 allows storing unstructured content in different column types like blob, clob, and JSON. Users can store their private knowledge assets in Db2. Each knowledge asset can have vector embeddings generated for its different sections, which are stored in Db2 using VECTOR columns. A pre-trained or custom-trained LLM, such as llama 3, generates these embeddings.
To start a search, the user sends a prompt to the application. The application vectorizes the prompt using the same LLM used for vectorizing the knowledge assets. After vectorizing the prompt using the same LLM, the application generates a query vector, which is then utilized for a vector similarity search in Db2. This search method retrieves a list of top matching documents, which are added to the original prompt. An instruction is also included for the LLM to answer the user’s query based on the augmented list of documents.
Figure 2. Running a vector similarity search with Db2
Here is an example scenario of implementing a RAG application by using Db2 vector support: A software development company is aiming to implement natural language search for their developer articles. In the past, developers had to rely on keyword-based searches, which had their limitations. It was challenging to find the right keywords, especially for beginners. The search results would often be long lists of documents that developers had to sift through, taking up a significant amount of time and containing irrelevant information. To address these issues, the company’s data science team has devised a plan to use AI technology to enable users to ask questions in natural language. They have stored the content as vector representations in their Db2 database, with the original articles saved as text files.
When a developer asks a question, the application converts it into a vector using the same method employed for the company’s knowledge assets. The application then searches the vectors in the Db2 database and retrieves the most relevant documents. It combines the original text of these documents with the prompt and sends it to the AI model, instructing it to answer the developer’s question using only these documents. The language model endeavors to provide an answer based on the company’s private knowledge stored in Db2.