Determine why a Datacom SQL query is ineligible for Source Cache

Products

• Datacom
• Datacom/DB

Issue/Introduction

A Datacom SQL query is not being stored in the SQL Source cache.

The Optimization Report does not give any clue why the query was marked ineligible for the cache.

SQL traces show the following messages:

S001E STMTPREP: Statment not eligible for source cache

S001E STMTEXE: NOT SAVING IN SOURCE CACHE

but they do not help in identifying the component that is causing it to be marked ineligible.

The query doesn't use any of the items that per SQL Source Cache page of the Datacom online documentation, here are the conditions that prevent caching of a query:

Queries not cached

Queries with the following syntax are not cached:

1. Special Registers SPLREG_DCM_TSN and SPLREG_DCM_MUF_NAME
2. Labeled Durations
3. predicates comparing between UNSIGNED NUMERIC and CHAR data types
4. column1 LIKE 'XXX%' ← converted to column1 = 'XXX'
5. column1_length_3 = '1234' ← special processing used when the literal is longer than the column

Environment

Release : 15.1

Component: Datacom/SQL

Cause

The query was executed via DBSQLPR with MSG=SS.  The SQL Optimization Report requires the bind process which implies the source cache cannot be used because it avoids the bind process.

Resolution

Use MSG=NN or MSG=ND