CA IDMS EXPLAIN with quoted strings DB006001 Unrecognizable token

Products

• IDMS
• IDMS - Database

Issue/Introduction

Trying to run a CA IDMS EXPLAIN statement on a SELECT statement which has a quoted character string, getting error on the quoted string:

Status = -4       SQLSTATE = 42600        Messages follow:  
Syntax error in line 4 at column 47, token = KATHERINE'     
DB006001 T130 C-4M330: Unrecognizable token                 

 

Environment

Release : 19.0

Component : CA IDMS SQL

Resolution

In the EXPLAIN statement, the whole SQL statement must be enclosed in single quotes.
If there are any singles quotes within the statement such as in a quoted character string, then they have to be replaced with 2 single quotes ( not double quotes).

Example:

SQL Statement:

SELECT EMP_ID_0415, EMP_FIRST_NAME_0415, EMP_LAST_NAME_0415
  FROM EMPNET.EMPLOYEE 
  WHERE EMP_ID_0415=23 AND EMP_FIRST_NAME_0415='KATHERINE';

In an EXPLAIN the statement is:

EXPLAIN STATEMENT
'
SELECT EMP_ID_0415, EMP_FIRST_NAME_0415, EMP_LAST_NAME_0415
  FROM EMPNET.EMPLOYEE 
  WHERE EMP_ID_0415=23 AND EMP_FIRST_NAME_0415=''KATHERINE''
'
INTO TABLE segment.ACCESS_PLAN; 

An alternative to coding a value is to use a question mark to indicate a hard-coded value or a host variable in a program and the quote isn't necessary. The question mark can be used for both numeric and character parameters.

EXPLAIN STATEMENT
'
SELECT EMP_ID_0415, EMP_FIRST_NAME_0415, EMP_LAST_NAME_0415
  FROM EMPNET.EMPLOYEE
  WHERE EMP_ID_0415=? AND EMP_FIRST_NAME_0415=?
'
INTO TABLE segment.ACCESS_PLAN;

 

Additional Information

See documentation on the EXPLAIN statement.