Db2 LUW VECTOR datatype in practice

The VECTOR datatype, introduced in Db2 LUW 12.1.2, allows Db2 to store vector embeddings directly in relational tables and query them with SQL. That matters when you want similarity search close to the source data instead of shipping vectors to a separate store.

This article is deliberately practical. It creates a small lab database, loads sample vector data, and shows how to run similarity searches with VECTOR_DISTANCE.

1. Lab goals

By the end of the lab you should be able to:

Create VECTOR columns in Db2 LUW
Load vector values with the VECTOR scalar function
Inspect vector values with VECTOR_SERIALIZE and VECTOR_DIMENSION_COUNT
Run similarity search with VECTOR_DISTANCE
Combine vector similarity with regular relational filters
Understand the most important current restrictions of the datatype

2. Required software

For this lab, use:

Db2 LUW 12.1.2 or later on Linux
A shell account that can switch to the Db2 instance owner

Important version note:

The VECTOR datatype is available from 12.1.2
IBM documents EXPORT and IMPORT support from 12.1.2
IBM documents LOAD support from 12.1.3

To keep the lab simple and portable across 12.1.2 and later, the main flow below uses INSERT statements rather than LOAD.

3. What the `VECTOR` datatype gives you

Db2 supports vectors as a native datatype with this form:

VECTOR(<dimension>, <coordinate-type>)

Current coordinate types are:

REAL or FLOAT32
INT8

The datatype is fixed-length:

Every value must contain exactly the declared number of coordinates
Vectors are only compatible with other vectors of the same dimension and coordinate type

The most useful built-in vector functions for day-to-day SQL work are:

Function	Practical use
`VECTOR`	Convert a string representation into a stored vector
`VECTOR_SERIALIZE`	Convert a vector back to string form
`VECTOR_DISTANCE`	Compute similarity distance between two vectors
`VECTOR_NORM`	Compute the distance from a vector to the zero vector
`VECTOR_DIMENSION_COUNT`	Return the declared dimension

For this article:

We use REAL coordinates
We focus on SQL usage inside Db2, not on embedding generation outside Db2

4. Create the lab database

Before creating the database, switch to the Db2 instance owner account.

Example:

su - db2inst1

If your instance owner has a different name, replace db2inst1 accordingly.

Now create the database:

db2 "create database VECLAB"

5. Create the schema objects

The lab uses a simple document-search model:

One table stores small text chunks and metadata
One VECTOR column stores the embedding for each chunk

cat > /tmp/veclab_schema.sql <<'SQL'
CONNECT TO VECLAB;

CREATE SCHEMA vecdemo;

CREATE TABLE vecdemo.doc_chunk
(
    chunk_id          BIGINT NOT NULL,
    doc_id            BIGINT NOT NULL,
    source_type       VARCHAR(20) NOT NULL,
    language_code     CHAR(2) NOT NULL,
    topic             VARCHAR(40) NOT NULL,
    title             VARCHAR(120) NOT NULL,
    chunk_text        CLOB(8K) NOT NULL,
    embedding         VECTOR(8, REAL),
    created_ts        TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
    PRIMARY KEY (chunk_id)
);

CREATE INDEX vecdemo.ix_doc_chunk_topic
    ON vecdemo.doc_chunk (topic, language_code);

COMMIT;
CONNECT RESET;
SQL

db2 -tvf /tmp/veclab_schema.sql

6. Insert sample vector data

We use eight-dimensional vectors to keep the lab readable. The values are intentionally small so that you can see clearly which rows are near each other.

Each code block is self-contained and can be copied independently.

cat > /tmp/veclab_load.sql <<'SQL'
CONNECT TO VECLAB;

INSERT INTO vecdemo.doc_chunk
    (chunk_id, doc_id, source_type, language_code, topic, title, chunk_text, embedding)
VALUES
    (1, 1001, 'MANUAL', 'EN', 'DB2', 'Db2 backup basics',
     'A short introduction to Db2 backups and recovery.',
     VECTOR('[0.91, 0.87, 0.12, 0.09, 0.18, 0.11, 0.14, 0.10]', 8, REAL)),

    (2, 1002, 'MANUAL', 'EN', 'DB2', 'Db2 restore basics',
     'A short introduction to restore operations and log replay.',
     VECTOR('[0.88, 0.84, 0.10, 0.08, 0.16, 0.10, 0.12, 0.09]', 8, REAL)),

    (3, 1003, 'KB', 'EN', 'LOCKING', 'Lock timeout troubleshooting',
     'Practical checks for timeout events and blocking sessions.',
     VECTOR('[0.12, 0.11, 0.93, 0.89, 0.18, 0.20, 0.09, 0.11]', 8, REAL)),

    (4, 1004, 'KB', 'EN', 'LOCKING', 'Deadlock troubleshooting',
     'Practical checks for deadlock events and conflicting application flows.',
     VECTOR('[0.10, 0.09, 0.90, 0.92, 0.17, 0.18, 0.08, 0.10]', 8, REAL)),

    (5, 1005, 'WIKI', 'EN', 'VECTOR', 'Embedding storage pattern',
     'A note about storing embeddings and original text in the same table.',
     VECTOR('[0.20, 0.18, 0.14, 0.11, 0.95, 0.91, 0.22, 0.19]', 8, REAL)),

    (6, 1006, 'WIKI', 'EN', 'VECTOR', 'Similarity search pattern',
     'A note about nearest-neighbor style search with SQL predicates.',
     VECTOR('[0.18, 0.16, 0.12, 0.10, 0.92, 0.94, 0.21, 0.18]', 8, REAL)),

    (7, 1007, 'WIKI', 'ES', 'VECTOR', 'Patron de almacenamiento de embeddings',
     'Una nota sobre almacenar embeddings y texto original en la misma tabla.',
     VECTOR('[0.19, 0.17, 0.13, 0.11, 0.93, 0.90, 0.23, 0.20]', 8, REAL)),

    (8, 1008, 'WIKI', 'PT', 'VECTOR', 'Padrao de pesquisa por similaridade',
     'Uma nota sobre pesquisa semantica com filtros relacionais adicionais.',
     VECTOR('[0.17, 0.15, 0.11, 0.10, 0.90, 0.93, 0.20, 0.17]', 8, REAL));

COMMIT;
CONNECT RESET;
SQL

db2 -tvf /tmp/veclab_load.sql

7. Inspect the datatype in the catalog

First confirm that Db2 registered the column as a vector.

db2 connect to VECLAB
db2 "
SELECT
    CAST(RTRIM(colname) AS VARCHAR(20)) AS col,
    CAST(RTRIM(typename) AS VARCHAR(20)) AS type_name,
    CAST(length AS VARCHAR(8)) AS len,
    CAST(nulls AS VARCHAR(4)) AS nul
FROM syscat.columns
WHERE tabschema = 'VECDEMO'
  AND tabname = 'DOC_CHUNK'
ORDER BY colno"
db2 connect reset

You should see EMBEDDING with a vector type reported by the catalog.

8. Inspect stored vector values

The vector is stored internally in binary form. To inspect it in SQL output, use VECTOR_SERIALIZE.

db2 connect to VECLAB
db2 "
SELECT
    CAST(chunk_id AS VARCHAR(8)) AS id,
    CAST(RTRIM(topic) AS VARCHAR(12)) AS topic,
    CAST(VECTOR_DIMENSION_COUNT(embedding) AS VARCHAR(6)) AS dim,
    VECTOR_SERIALIZE(embedding) AS vec
FROM vecdemo.doc_chunk
ORDER BY chunk_id
FETCH FIRST 4 ROWS ONLY"
db2 connect reset

This is the first practical point to remember:

Applications often work with string form such as '[0.91, 0.87, ...]'
Db2 stores the actual value as a vector
VECTOR_SERIALIZE is the simplest way to inspect what is stored

9. Run a first similarity search

Now run a nearest-match style query. The reference vector below is deliberately close to the VECTOR topic rows.

For COSINE, smaller distance means more similar vectors.

db2 connect to VECLAB
db2 "
WITH query_vec(qv) AS
(
    VALUES VECTOR('[0.19, 0.17, 0.12, 0.10, 0.94, 0.92, 0.22, 0.18]', 8, REAL)
)
SELECT
    CAST(d.chunk_id AS VARCHAR(8)) AS id,
    CAST(RTRIM(d.topic) AS VARCHAR(12)) AS topic,
    CAST(RTRIM(d.language_code) AS VARCHAR(4)) AS lang,
    CAST(RTRIM(d.title) AS VARCHAR(60)) AS title,
    CAST(DECIMAL(VECTOR_DISTANCE(d.embedding, q.qv, COSINE), 16, 8) AS VARCHAR(18)) AS cosine_dist
FROM vecdemo.doc_chunk d,
     query_vec q
WHERE d.embedding IS NOT NULL
ORDER BY VECTOR_DISTANCE(d.embedding, q.qv, COSINE)
FETCH FIRST 5 ROWS ONLY"
db2 connect reset

In a realistic application, that query vector would come from an embedding model outside Db2. For the lab, it is enough to provide a fixed vector literal.

10. Combine similarity search with relational filters

This is where native vector storage inside Db2 becomes useful. You can filter on normal metadata first and then rank the candidates by vector distance.

The example below restricts the search to English content from the WIKI source.

db2 connect to VECLAB
db2 "
WITH query_vec(qv) AS
(
    VALUES VECTOR('[0.19, 0.17, 0.12, 0.10, 0.94, 0.92, 0.22, 0.18]', 8, REAL)
)
SELECT
    CAST(d.chunk_id AS VARCHAR(8)) AS id,
    CAST(RTRIM(d.source_type) AS VARCHAR(10)) AS src,
    CAST(RTRIM(d.language_code) AS VARCHAR(4)) AS lang,
    CAST(RTRIM(d.topic) AS VARCHAR(12)) AS topic,
    CAST(RTRIM(d.title) AS VARCHAR(60)) AS title,
    CAST(DECIMAL(VECTOR_DISTANCE(d.embedding, q.qv, COSINE), 16, 8) AS VARCHAR(18)) AS cosine_dist
FROM vecdemo.doc_chunk d,
     query_vec q
WHERE d.embedding IS NOT NULL
  AND d.source_type = 'WIKI'
  AND d.language_code = 'EN'
ORDER BY VECTOR_DISTANCE(d.embedding, q.qv, COSINE)
FETCH FIRST 3 ROWS ONLY"
db2 connect reset

That pattern is often more useful than a pure vector-only search:

Narrow candidates with relational predicates
Rank the remaining rows by vector similarity

11. Compare different distance metrics

Db2 supports several metrics in VECTOR_DISTANCE, including:

COSINE
EUCLIDEAN
EUCLIDEAN_SQUARED
DOT
HAMMING
MANHATTAN

These metrics do not mean the same thing, so it is important to choose deliberately:

COSINE

Measures the angular difference between two vectors. In practice, this is the most common choice for semantic-search style embeddings, because it focuses on orientation rather than absolute magnitude.

EUCLIDEAN

Measures straight-line distance between two vectors. This is useful when the magnitude of the vector is part of the signal and not just its direction.

EUCLIDEAN_SQUARED

Similar to Euclidean distance, but without the square root. It preserves ranking relative to Euclidean distance while being expressed on a different numeric scale.

DOT

Computes the dot product rather than a geometric distance in the ordinary sense. It is useful only when the upstream embedding method or ranking logic is explicitly built around dot-product similarity.

MANHATTAN

Measures distance as the sum of absolute coordinate-by-coordinate differences. It is less common for semantic embeddings, but can still be useful in some numerical feature models.

HAMMING

Counts coordinate differences in a discrete way and is normally relevant only for specific vector encodings, especially when the data behaves more like discrete symbols than continuous embeddings.

Start with COSINE for semantic-search and RAG-style embeddings
Use EUCLIDEAN only when your embedding workflow or model documentation expects Euclidean distance
Avoid mixing metrics between testing and production, because the nearest rows can change

The example below keeps the comparison to COSINE and EUCLIDEAN.

db2 connect to VECLAB
db2 "
WITH query_vec(qv) AS
(
    VALUES VECTOR('[0.19, 0.17, 0.12, 0.10, 0.94, 0.92, 0.22, 0.18]', 8, REAL)
)
SELECT
    CAST(d.chunk_id AS VARCHAR(8)) AS id,
    CAST(RTRIM(d.title) AS VARCHAR(60)) AS title,
    CAST(DECIMAL(VECTOR_DISTANCE(d.embedding, q.qv, COSINE), 16, 8) AS VARCHAR(18)) AS cosine_dist,
    CAST(DECIMAL(VECTOR_DISTANCE(d.embedding, q.qv, EUCLIDEAN), 16, 8) AS VARCHAR(18)) AS euclid_dist
FROM vecdemo.doc_chunk d,
     query_vec q
WHERE d.embedding IS NOT NULL
ORDER BY d.chunk_id"
db2 connect reset

For practical work, the key point is not to mix metrics casually. Choose one metric for the search flow and keep it consistent between your embedding generation approach and your ranking logic.

12. Use `VECTOR_NORM` to inspect magnitude

VECTOR_NORM measures the distance from a vector to the zero vector. In practice, that is useful when you want to inspect magnitude rather than distance between two rows.

Typical uses include:

Checking whether embeddings from the same pipeline have roughly comparable magnitude
Spotting suspicious values, such as vectors that are unexpectedly close to zero
Validating whether a preprocessing step appears to have produced normalized vectors or not

That matters because some similarity workflows assume vectors are already normalized, while others do not. VECTOR_NORM gives you a quick SQL-side sanity check before you start comparing large numbers of rows.

db2 connect to VECLAB
db2 "
SELECT
    CAST(chunk_id AS VARCHAR(8)) AS id,
    CAST(RTRIM(title) AS VARCHAR(60)) AS title,
    CAST(DECIMAL(VECTOR_NORM(embedding, EUCLIDEAN), 16, 8) AS VARCHAR(18)) AS vec_norm
FROM vecdemo.doc_chunk
WHERE embedding IS NOT NULL
ORDER BY chunk_id"
db2 connect reset

If the values returned by VECTOR_NORM are all in a narrow band, that suggests your embeddings were generated with fairly consistent magnitude. If one row is much smaller or much larger than the rest, that row is worth inspecting before trusting the similarity results.

13. Export vector values back to string form

Db2 exports vectors as strings. That makes it possible to move vectors between tables or environments without exposing the internal binary representation directly.

This lab keeps the example simple with EXPORT.

db2 connect to VECLAB
db2 "export to /tmp/vecdemo_chunks.del of del modified by nochardel
select chunk_id, topic, vector_serialize(embedding)
from vecdemo.doc_chunk
order by chunk_id"
db2 connect reset

Now inspect the generated file:

sed -n '1,5p' /tmp/vecdemo_chunks.del

The vector values should appear in bracketed string form.

14. Current restrictions that matter in practice

The current implementation is useful, but it is not “just another scalar type”. A few restrictions matter immediately in design work:

A vector column cannot be a primary key, foreign key, unique key, or index key
Vectors cannot be compared directly
You cannot use a vector column as an ORDER BY key by itself
an existing non-vector column cannot be altered into VECTOR
An existing vector column cannot be altered to another coordinate type or dimension
The only default value allowed is NULL

15. What this means architecturally

The biggest value of the VECTOR datatype is not that Db2 suddenly becomes a full end-to-end embedding platform. The value is that you can keep:

The original content
The metadata
The vector embeddings
The SQL filtering logic

in the same database engine.

That is especially useful for:

Semantic search over knowledge articles
RAG-style retrieval over governed internal content
Similarity search that must also honor business predicates such as language, source, category, tenant, or validity date

16. Summary

The VECTOR datatype in Db2 LUW 12.1.2 and later gives you native storage for embeddings inside relational tables.

This lab showed the practical core of the feature:

Define a VECTOR column
Insert vectors with the VECTOR scalar function
Inspect them with VECTOR_SERIALIZE
Compute similarity with VECTOR_DISTANCE
Combine semantic ranking with ordinary relational filtering

This is a useful pattern most for teams. The embedding model can remain outside Db2, while the storage, filtering, and ranking stay inside SQL.

17. Useful IBM documentation

For deeper reading, the most useful IBM references for this topic are:

18. Cleanup

When you finish the lab, remove the database and the temporary files:

db2 force applications all
db2 drop database VECLAB
rm -f /tmp/veclab_schema.sql \
      /tmp/veclab_load.sql \
      /tmp/vecdemo_chunks.del

Menu

Db2 LUW VECTOR datatype in practice

1. Lab goals

2. Required software

3. What the `VECTOR` datatype gives you

4. Create the lab database

5. Create the schema objects

6. Insert sample vector data

7. Inspect the datatype in the catalog

8. Inspect stored vector values

9. Run a first similarity search

10. Combine similarity search with relational filters

11. Compare different distance metrics

12. Use `VECTOR_NORM` to inspect magnitude

13. Export vector values back to string form

14. Current restrictions that matter in practice

15. What this means architecturally

16. Summary

17. Useful IBM documentation

18. Cleanup

More in this area

Db2 LUW VECTOR datatype in practice

1. Lab goals

2. Required software

3. What the VECTOR datatype gives you

4. Create the lab database

5. Create the schema objects

6. Insert sample vector data

7. Inspect the datatype in the catalog

8. Inspect stored vector values

9. Run a first similarity search

10. Combine similarity search with relational filters

11. Compare different distance metrics

12. Use VECTOR_NORM to inspect magnitude

13. Export vector values back to string form

14. Current restrictions that matter in practice

15. What this means architecturally

16. Summary

17. Useful IBM documentation

18. Cleanup

More in this area

Db2 LUW Articles

3. What the `VECTOR` datatype gives you

12. Use `VECTOR_NORM` to inspect magnitude