Troubleshooting
Problem
Explain will not work if SQL contains special host variables, they need to be replaced with CAST(? AS INT) to make the Explain work in IBM DSM.
Resolving The Problem
DSM automatically replaced the host variables with parameter marker, and in most cases, after we do this, the statement will become explainable. But there are some special cases - just replacing the host variables with parameter marker doesn't work, user will meet -417/-418 error, but such cases aren't common. User need to manually change some special host variables with CAST function containing the parameter marker. See the example below. As, we don't know the host variables data type, so we can not automatically make such changes for user.
Example non-Explainable SQL:
SELECT WORKFLOW_ID, WFD_ID, START_TIME, (
SELECT ( CASE
WHEN BASIC_STATUS = :L0 THEN -:L1
WHEN BASIC_STATUS = :L2 THEN :L3
WHEN BASIC_STATUS = :L4 THEN :L5
WHEN BASIC_STATUS = :L6 THEN :L7
WHEN BASIC_STATUS IN ( :L8 , :L9 ) AND NEXT_AI_ID = -:L10 THEN :L11
WHEN BASIC_STATUS = :L12 THEN :L13
WHEN BASIC_STATUS IN ( :L14 , :L15 ) AND NEXT_AI_ID = -:L16 THEN :L17
WHEN BASIC_STATUS = :L18 THEN :L19
WHEN BASIC_STATUS IN ( :L20 , :L21 , :L22 , :L23 , :L24 , :L25 , :L26 , :L27 , :L28 , :L29 ) AND NEXT_AI_ID = -:L30 AND WFE_STATUS = -:L31 THEN :L32
WHEN BASIC_STATUS IN ( :L33 , :L34 , :L35 , :L36 , :L37 , :L38 , :L39 , :L40 , :L41 , :L42 ) AND NEXT_AI_ID = -:L43 THEN :L44
WHEN BASIC_STATUS IN ( :L45 , :L46 , :L47 , :L48 , :L49 ) AND ( NEXT_AI_ID = ACTIVITYINFO_ID OR WFE_STATUS IN ( :L50 , :L51 , :L52 , :L53 , :L54 , :L55 , :L56 , :L57 , :L58 ) ) THEN :L59
WHEN BASIC_STATUS IN ( :L60 , :L61 , :L62 , :L63 , :L64 ) THEN :L65
WHEN BASIC_STATUS = :L66 THEN :L67
WHEN BASIC_STATUS IN( :L68 , -:L69 ) AND NEXT_AI_ID = -:L70 THEN :L71
WHEN BASIC_STATUS IN (:L72 ,:L73 ) AND NEXT_AI_ID = -:L74 THEN :L75
WHEN NEXT_AI_ID != -:L76 AND NEXT_AI_ID != -:L77 THEN (
SELECT CASE WHEN COUNT(*) > :L78 THEN :L79 ELSE :L80 END
FROM WF_INACTIVE
WHERE EXISTS (
SELECT WF_ID
FROM WF_INACTIVE WFI
WHERE WFI.WF_ID = WFC_MAIN_1.WORKFLOW_ID))
ELSE -:L81
END ) STATE_1
............
.....................
Modified same SQL that is Explainable:
SELECT WORKFLOW_ID, WFD_ID, START_TIME, (
SELECT (CASE
WHEN BASIC_STATUS = ? THEN -CAST(? AS INT)
WHEN BASIC_STATUS = ? THEN CAST(? AS INT)
WHEN BASIC_STATUS = ? THEN CAST(? AS INT)
WHEN BASIC_STATUS = ? THEN CAST(? AS INT)
WHEN BASIC_STATUS IN (? , ? ) AND NEXT_AI_ID = -? THEN CAST(? AS INT)
WHEN BASIC_STATUS = ? THEN ?
WHEN BASIC_STATUS IN (? , ? ) AND NEXT_AI_ID = -? THEN CAST(? AS INT)
WHEN BASIC_STATUS = ? THEN CAST(? AS INT)
WHEN BASIC_STATUS IN (? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) AND NEXT_AI_ID = -? AND WFE_STATUS = -? THEN CAST(? AS INT)
WHEN BASIC_STATUS IN (? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) AND NEXT_AI_ID = -? THEN CAST(? AS INT)
WHEN BASIC_STATUS IN (? , ? , ? , ? , ? ) AND ( NEXT_AI_ID = WFC_STATE_1.ACTIVITYINFO_ID OR WFE_STATUS IN (? , ? , ? , ? , ? , ? , ? , ? , ? ) ) THEN CAST(? AS INT)
WHEN BASIC_STATUS IN (?, ?, ?, ?, ?) THEN CAST(? AS INT)
WHEN BASIC_STATUS = ? THEN CAST(? AS INT)
WHEN BASIC_STATUS IN (? , -? ) AND NEXT_AI_ID = -? THEN CAST(? AS INT)
WHEN BASIC_STATUS IN (? , ? ) AND NEXT_AI_ID = -? THEN CAST(? AS INT)
WHEN NEXT_AI_ID <> -? AND NEXT_AI_ID <> -? THEN (
SELECT CASE WHEN COUNT(*) > ? THEN CAST(? AS INT) ELSE CAST(? AS INT) END
FROM WF_INACTIVE
WHERE EXISTS (
SELECT WF_ID
FROM WF_INACTIVE AS WFI
WHERE WF_ID = WFC_MAIN_1.WORKFLOW_ID))
ELSE -CAST(? AS INT)
END) AS STATE_1
..................
..............................
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg22002584