IBM Support

Your SQL query fails with error 208 when PDQ is enabled

Troubleshooting


Problem

You run an SQL query with PDQ enabled and it fails with SQL error -208.

Symptom

Your query fails with the following error:

208: Memory allocation failed during query processing.

Cause

The amount of memory available for PDQ queries is under-configured. The memory grant manager, which services PDQ queries, cannot allocate the minimum memory required for your query.


Note: There may be other reasons for a query failing with error 208.

Diagnosing The Problem

  • Method 1. Adjust DS_TOTAL_MEMORY

  • 1. Increase the value of DS_TOTAL_MEMORY and take the engine offline and then back online. Alternatively, you can set the value temporarily with this command:

    onmode -M <mem size in KB>

    2. Run the query after resetting DS_TOTAL_MEMORY.
  • Method 2. Obtain a stack trace.

    You can hang the engine and force an assert failure on the 208 error. See the Related information section below for instructions. The stack of the running sqlexec thread will resemble this stack:
      (oninit)afstack
      (oninit)afhandler
      (oninit)affail_interface
      (oninit)check_traperror
      (oninit)sqerr  
      (oninit)sqnomem
      (oninit)mgm_register_query
      (oninit)register_query
      (oninit)prepselect
      (oninit)open_cursor
      (oninit)sql_open
      (oninit)sq_open
      (oninit)sqmain  
      (oninit)spawn_thread
      (oninit)startup

Resolving The Problem

Increase the value of the DS_TOTAL_MEMORY configuration and take the engine offline and then back online.

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"11.5;11.7;12.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
29 September 2018

UID

swg21382893