Topic
7 replies Latest Post - ‏2013-02-02T01:01:32Z by SystemAdmin
stoned99
stoned99
26 Posts
ACCEPTED ANSWER

Pinned topic Explain fails with SQLCODE 203, SQLSTATE 01552 Errorcode -4220

‏2013-01-29T17:21:15Z |
Hopefully someone from IBM can address this.

I have a complex query that executes successfully (but slow), that I'm trying to tune via explain. I'm running the most current version of DS, and using the Query Tuning tool (not paid addon).

During the Parse Query step explain fails catastrophically. I've included the SQL below for reference. For the record it is used to generate a flat file with headers and footers.

Exception Message:
An internal error occurred. Contact IBM Software Support for assistance.

Exception Stack Trace:
com.ibm.datatools.dsoe.common.exception.DSOEException: com.ibm.db2.jcc.am.SqlException: jcc103712036http://3.64.106 Exception occurred during clob conversion. See attached Throwable for details. ERRORCODE=-4220, SQLSTATE=null

at com.ibm.datatools.dsoe.parse.zos.Parser.generateXMLFiles(Unknown Source)

at com.ibm.datatools.dsoe.parse.zos.Parser$ProcessorThread.run(Unknown Source)

Caused by: com.ibm.db2.jcc.am.SqlException: jcc103712036http://3.64.106 Exception occurred during clob conversion. See attached Throwable for details. ERRORCODE=-4220, SQLSTATE=null

at com.ibm.db2.jcc.am.bd.a(Unknown Source)

at com.ibm.db2.jcc.am.bd.a(Unknown Source)

at com.ibm.db2.jcc.am.bd.a(Unknown Source)

at com.ibm.db2.jcc.am.je.<init>(Unknown Source)

at com.ibm.db2.jcc.t4.c.b(Unknown Source)

at com.ibm.db2.jcc.am.fc.a(Unknown Source)

at com.ibm.db2.jcc.am.fc.X(Unknown Source)

at com.ibm.db2.jcc.am.ResultSet.getObjectX(Unknown Source)

at com.ibm.db2.jcc.am.ResultSet.getObject(Unknown Source)

at com.ibm.db2.jcc.am.ResultSet.getObject(Unknown Source)

... 2 more

Caused by: java.nio.charset.MalformedInputException: Input length = 15655

at com.ibm.db2.jcc.am.r.a(Unknown Source)

... 9 more

Caused by: sun.io.MalformedInputException

at sun.io.ByteToCharUTF8.convert(Unknown Source)

... 10 more
WITH INDEMNITY_FAMILIES (FAMILY_ID , RELATION_ID, RISK, TRUST_SW, PART_SSNO, SSNO, LAST_NAME, FIRST_NAME, GENDER, BIRTH_DATE, BCC_GROUP, BCC_CASE, ELIG_PERIOD, STATUS, PLANTYPE, PREMIUM_SW, MEDICAL_PLAN) AS
(
select R.FAMILY_ID , R.RELATION_ID, R.RISK, R.TRUST_SW, R.PART_SSNO, R.SSNO, R.LAST_NAME, R.FIRST_NAME, R.GENDER, R.BIRTH_DATE,
L.BCC_GROUP, L.BCC_CASE,
M.ELIG_PERIOD, m.STATUS, m.PLANTYPE, M.PREMIUM_SW,
C.COVERAGE_CODE as MEDICAL_PLAN
from (SELECT DISTINCT family_id FROM FDBMD.MEM_ACCUM_ENTRY A WHERE YEAR(A.APPLY_DATE) = 2012) A INNER JOIN
FDBEL.ELIG_COVERAGE C ON C.family_id = A.family_id
AND CURRENT DATE >= C.FROM_DATE AND CURRENT DATE <= C.THRU_DATE
AND C.COVERAGE_TYPE = 'MEDICAL' AND C.COVERAGE_CODE IN (1, 3, 5, 6, 91) INNER JOIN
fdbel.elig_mthdtl M ON M.family_id = C.family_id inner join
FDBEL.REG_MASTER R ON R.family_id = C.family_id AND R.RELATION_ID = 0 INNER JOIN
FDBEL.BLUECR_ACCOUNT_LOOKUP L ON L.MEDICAL_PLAN = C.COVERAGE_CODE
AND L.STATUS = M.STATUS
AND L.PLANTYPE = M.PLANTYPE
AND L.PREMIUM_SW = M.PREMIUM_SW
AND CURRENT DATE >= L.FROM_DATE AND CURRENT DATE <= L.THRU_DATE
AND L.TRUST_SW = R.TRUST_SW
AND L.RISK = R.RISK
WHERE M.ELIG_PERIOD = (SELECT ELIG_PERIOD FROM FDBEL.elig_mthdtl WHERE FAMILY_ID = A.FAMILY_ID order by ELIG_PERIOD DESC fetch first 1 row only)
)

select CAST(
'HDR'
|| 'UFCW FOOD' || REPEAT(' ', 41)
|| 'CA04031X' || REPEAT(' ', 2)
|| COALESCE(replace(char(CURRENT DATE, ISO),'-','') , '00000000')
|| 'H'
|| '20120101'
|| '20121231' AS CHAR(250) ) AS ANTHEM_HEADER
FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT * FROM (
select CAST(

RTRIM(LTRIM(INDEMNITY_FAMILIES.BCC_CASE)) || REPEAT(' ', 10 - LENGTH(RTRIM(LTRIM(INDEMNITY_FAMILIES.BCC_CASE))))
|| 'UFCW FOOD' || REPEAT(' ', 41) -- CASE NAME CHAR(50)
|| RTRIM(LTRIM(INDEMNITY_FAMILIES.BCC_GROUP)) || REPEAT(' ', 10 - LENGTH(RTRIM(LTRIM(INDEMNITY_FAMILIES.BCC_GROUP))))
|| 'FW' || SUBSTR(DIGITS(INDEMNITY_FAMILIES.FAMILY_ID), 4, 7) || REPEAT(' ', 11)
|| SUBSTR(DIGITS(INDEMNITY_FAMILIES.PART_SSNO), 2, 9) || REPEAT(' ', 11)
|| COALESCE(RTRIM(LEFT(RTRIM(LTRIM(ACCUMULATOR_ROLLUP.FIRST_NAME)), 25)) || REPEAT(' ', 25 - LENGTH(RTRIM(LEFT(RTRIM(LTRIM(ACCUMULATOR_ROLLUP.FIRST_NAME)), 25)))),REPEAT(' ', 25))
|| COALESCE(RTRIM(LEFT(RTRIM(LTRIM(ACCUMULATOR_ROLLUP.LAST_NAME)), 25)) || REPEAT(' ', 25 - LENGTH(RTRIM(LEFT(RTRIM(LTRIM(ACCUMULATOR_ROLLUP.LAST_NAME)), 25)))),REPEAT(' ', 25))
|| IFNULL(CHAR(ACCUMULATOR_ROLLUP.GENDER), REPEAT(' ', 1))
|| COALESCE(replace(char(ACCUMULATOR_ROLLUP.BIRTH_DATE, ISO),'-','') , '00000000')
|| COALESCE(RTRIM(LEFT(RTRIM(LTRIM(ACCUMULATOR_ROLLUP.ACCUM_NAME_EXTERNAL)), 8)) || REPEAT(' ', 8 - LENGTH(RTRIM(LEFT(RTRIM(LTRIM(ACCUMULATOR_ROLLUP.ACCUM_NAME_EXTERNAL)), 8)))),REPEAT(' ', 8))
|| COALESCE(CAST(DIGITS(CAST(SUM_ACCUM_DOLLAR * 100 AS DECIMAL(12))) AS CHAR(12)), REPEAT('0', 12))
|| COALESCE(CAST(DIGITS(CAST(SUM_ACCUM_DAYS AS DECIMAL(4))) AS CHAR(4)), REPEAT('0', 4))
|| COALESCE(CAST(DIGITS(CAST(SUM_ACCUM_OTHER AS DECIMAL(4))) AS CHAR(4)), REPEAT('0', 4))
|| '20120101'
|| '20121231'
|| REPEAT(' ', 37) AS CHAR(250) ) AS ANTHEM_ACCUMULATOR
FROM INDEMNITY_FAMILIES INDEMNITY_FAMILIES INNER JOIN

(
SELECT AX.ACCUM_NAME_EXTERNAL, UA.FAMILY_ID, UA.RELATION_ID, UA.SSNO, UA.LAST_NAME, UA.FIRST_NAME, UA.GENDER, UA.BIRTH_DATE, sum(UA.ACCUM_DOLLAR) AS SUM_ACCUM_DOLLAR,
sum(UA.ACCUM_DAYS) AS SUM_ACCUM_DAYS, SUM(UA.ACCUM_OTHER) as SUM_ACCUM_OTHER
FROM FDBMD.ACCUMULATOR_XREF AX INNER JOIN

(SELECT A.ACCUM_ID, A.VALUE_TYPE,
R.FAMILY_ID, R.RELATION_ID, R.SSNO, R.LAST_NAME, R.FIRST_NAME, R.GENDER, R.BIRTH_DATE,
CAST(COALESCE(
CASE WHEN A.VALUE_TYPE = 1 THEN
( SELECT SUM(MA.ENTRY_VALUE)
FROM FDBMD.MEM_ACCUM_ENTRY MA
WHERE MA.family_ID = R.FAMILY_ID
AND MA.RELATION_ID = R.RELATION_ID
AND MA.ACCUM_ID = A.ACCUM_ID
AND (YEAR(MA.APPLY_DATE) = 2012 OR MA.ACCUM_ID IN (47, 55, 71, 74, 75, 248, 249)) )
END, 0) AS DECIMAL(12,2)) AS ACCUM_DOLLAR,
CAST(COALESCE(
CASE WHEN A.VALUE_TYPE = 0 THEN
( SELECT SUM(MA.ENTRY_VALUE)
FROM FDBMD.MEM_ACCUM_ENTRY MA
WHERE MA.family_ID = R.FAMILY_ID
AND MA.RELATION_ID = R.RELATION_ID
AND MA.ACCUM_ID = A.ACCUM_ID
AND (YEAR(MA.APPLY_DATE) = 2012 OR MA.ACCUM_ID IN (47, 55, 71, 74, 75, 248, 249)) )
END, 0) AS DECIMAL(4)) AS ACCUM_DAYS,
CAST(COALESCE(
CASE WHEN A.VALUE_TYPE = 2 THEN
( SELECT SUM(MA.ENTRY_VALUE)
FROM FDBMD.MEM_ACCUM_ENTRY MA
WHERE MA.family_ID = R.FAMILY_ID
AND MA.RELATION_ID = R.RELATION_ID
AND MA.ACCUM_ID = A.ACCUM_ID
AND (YEAR(MA.APPLY_DATE) = 2012 OR MA.ACCUM_ID IN (47, 55, 71, 74, 75, 248, 249)) )
END, 0) AS DECIMAL(4)) AS ACCUM_OTHER

FROM INDEMNITY_FAMILIES I INNER JOIN
FDBEL.REG_MASTER R ON I.FAMILY_ID = R.FAMILY_ID,
FDBMD.ACCUMULATOR A
WHERE A.FAMILY_SW = 0

) AS UA ON AX.ACCUM_ID = UA.ACCUM_ID AND AX.active_sw = 1
GROUP BY AX.ACCUM_NAME_EXTERNAL, UA.FAMILY_ID, UA.RELATION_ID, UA.SSNO ,UA.LAST_NAME, UA.FIRST_NAME, UA.GENDER, UA.BIRTH_DATE, UA.ACCUM_DOLLAR,UA.ACCUM_DAYS, UA.ACCUM_OTHER
UNION ALL

SELECT AX.ACCUM_NAME_EXTERNAL, UA.FAMILY_ID, UA.RELATION_ID, UA.SSNO, UA.LAST_NAME, UA.FIRST_NAME, UA.GENDER, UA.BIRTH_DATE,
sum(UA.ACCUM_DOLLAR) AS SUM_ACCUM_DOLLAR,sum(UA.ACCUM_DAYS) AS SUM_ACCUM_DAYS, SUM(UA.ACCUM_OTHER) as SUM_ACCUM_OTHER
FROM FDBMD.ACCUMULATOR_XREF AX INNER JOIN

(SELECT A.ACCUM_ID, A.VALUE_TYPE,
R.FAMILY_ID, R.RELATION_ID, R.SSNO, R.LAST_NAME, R.FIRST_NAME, R.GENDER, R.BIRTH_DATE,
CAST(COALESCE(
CASE WHEN A.VALUE_TYPE = 1 THEN
( SELECT SUM(MA.ENTRY_VALUE)
FROM FDBMD.MEM_ACCUM_ENTRY MA
WHERE MA.family_ID = R.FAMILY_ID
AND MA.RELATION_ID = R.RELATION_ID
AND MA.ACCUM_ID = A.ACCUM_ID
AND YEAR(MA.APPLY_DATE) = 2012)
END, 0) AS DECIMAL(12,2)) AS ACCUM_DOLLAR,
CAST(COALESCE(
CASE WHEN A.VALUE_TYPE = 0 THEN
( SELECT SUM(MA.ENTRY_VALUE)
FROM FDBMD.MEM_ACCUM_ENTRY MA
WHERE MA.family_ID = R.FAMILY_ID
AND MA.RELATION_ID = R.RELATION_ID
AND MA.ACCUM_ID = A.ACCUM_ID
AND YEAR(MA.APPLY_DATE) = 2012)
END, 0) AS DECIMAL(4)) AS ACCUM_DAYS,
CAST(COALESCE(
CASE WHEN A.VALUE_TYPE = 2 THEN
( SELECT SUM(MA.ENTRY_VALUE)
FROM FDBMD.MEM_ACCUM_ENTRY MA
WHERE MA.family_ID = R.FAMILY_ID
AND MA.RELATION_ID = R.RELATION_ID
AND MA.ACCUM_ID = A.ACCUM_ID
AND YEAR(MA.APPLY_DATE) = 2012)
END, 0) AS DECIMAL(4)) AS ACCUM_OTHER

FROM INDEMNITY_FAMILIES R,
FDBMD.ACCUMULATOR A
WHERE A.FAMILY_SW = 1

) AS UA ON AX.ACCUM_ID = UA.ACCUM_ID AND AX.active_sw = 1
GROUP BY AX.ACCUM_NAME_EXTERNAL, UA.FAMILY_ID, UA.RELATION_ID, UA.SSNO ,UA.LAST_NAME, UA.FIRST_NAME, UA.GENDER, UA.BIRTH_DATE, UA.ACCUM_DOLLAR, UA.ACCUM_DAYS, UA.ACCUM_OTHER

) AS ACCUMULATOR_ROLLUP ON ACCUMULATOR_ROLLUP.FAMILY_ID = INDEMNITY_FAMILIES.FAMILY_ID

ORDER BY ACCUMULATOR_ROLLUP.FAMILY_ID, ACCUMULATOR_ROLLUP.RELATION_ID, ACCUMULATOR_ROLLUP.ACCUM_NAME_EXTERNAL
) AS ANTHEM_BODY

UNION ALL

select CAST(
'FTR'
|| DIGITS((SELECT COUNT(*) * 26 FROM FDBEL.REG_MASTER R INNER JOIN INDEMNITY_FAMILIES I ON R.FAMILY_ID = I.FAMILY_ID) + (SELECT COUNT(*) * 4 FROM INDEMNITY_FAMILIES I))
|| (select CAST(generate_unique() AS CHAR(10) FOR SBCS DATA) from sysibm.sysdummy1)
AS CHAR(250) ) AS ANTHEM_FOOTER
FROM SYSIBM.SYSDUMMY1

FOR READ ONLY WITH UR
Updated on 2013-02-02T01:01:32Z at 2013-02-02T01:01:32Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1632 Posts
    ACCEPTED ANSWER

    Re: Explain fails with SQLCODE 203, SQLSTATE 01552 Errorcode -4220

    ‏2013-01-29T19:20:16Z  in response to stoned99
    HI
    If you are using DB2 for Z/OS V9.1 NFM, please check if you have applied APAR PM28144, which fixes the character conversion issue.

    And we also recommend that you upgrade Data Studio client to later release, such as V3.1.1 or V3.2.

    Regards,
    Xin
  • stoned99
    stoned99
    26 Posts
    ACCEPTED ANSWER

    Re: Explain fails with SQLCODE 203, SQLSTATE 01552 Errorcode -4220

    ‏2013-01-29T19:51:01Z  in response to stoned99
    We're running DB2 10 Compatibility mode, and I'm running DS 3.2, is there a equiv APAR ?
    • stoned99
      stoned99
      26 Posts
      ACCEPTED ANSWER

      Re: Explain fails with SQLCODE 203, SQLSTATE 01552 Errorcode -4220

      ‏2013-02-01T18:51:41Z  in response to stoned99
      Any follow up from IBM support, or is another lack of support issue !!
      • SystemAdmin
        SystemAdmin
        1632 Posts
        ACCEPTED ANSWER

        Re: Explain fails with SQLCODE 203, SQLSTATE 01552 Errorcode -4220

        ‏2013-02-01T20:29:42Z  in response to stoned99
        Hi,
        I am sorry for the delayed response to previous question.
        We consulted with DB2 development team and confirmed that the APAR applies to V10NFM and V10CM.
        However, in order to be 100% sure that the APAR will resolve your issue, we would like to collect a few more things from you to recreate the problem in house.

        1. Query Tuner trace
        To enable query tuner trace,
        -- go to Data Studio's top menu, Windows -> Preference.
        -- Once the preference dialog pops up, expand node "Data Manager"->"Query Tuner" in the navigation panel on the left.
        -- On the right panel, you will see a check box option "Enable Query Tuner Trace" (unchecked by default). check this box
        -- Choose the directory where you want to store the trace file
        -- Click on the button "Clear Trace" to clean any potential history trace
        -- Remember to come back here to disable the trace after recreating the problem

        2. Jcc trace
        To enable JCC trace,
        -- Go to Data Source Explorer view where you connect to database for tuning.
        -- Right-click on the targeted connection profile, choose "Properties" from the context menu
        -- Once the Properties dialog pops up, select "Driver Properties" from the navigation
        -- On the right, select "Tracing" tab. You will see the check box option "Disable tracing" checked by default. Uncheck this option
        -- Choose directory where you want to save the trace file
        -- Click on "Select All" for the Trace levels.
        -- Click OK.
        -- Remember to come back here to disable the trace after recreating the problem.

        3. Service SQL files, which include object DDLs, statistics and related environment data. We can simulate your query environment based on these files.
        To collect the Service SQL files,
        -- From Data Source Explorer, right-click on the subsystem name under the targeted connection profile
        -- Choose "Analyze and Tune" -> "Capture Environment" -> "Capture Query Environment" from the popup context menu.
        -- A wizard dialog pops up, input the query that you were trying to tune and other options on the wizard.
        -- follow the wizard instructions to collect and send us the files.

        In order to better assist you and keep formal service records for this problem, we would prefer you open a PMR and send us the above service data via PMR.

        If you have any concern about the above steps or any other questions, please email me @ xinwu@us.ibm.com or call my office line: (408)463-5948.

        regard,
        Xin Wu
        • SystemAdmin
          SystemAdmin
          1632 Posts
          ACCEPTED ANSWER

          Re: Explain fails with SQLCODE 203, SQLSTATE 01552 Errorcode -4220

          ‏2013-02-01T23:04:35Z  in response to SystemAdmin
          A minor correction. Since explaining the query failed in your case, collecting service SQL against the original query may not work either because it is based on explain. Please use the following:
          SELECT 1 FROM FDBMD.MEM_ACCUM_ENTRY,
          FDBEL.ELIG_COVERAGE,
          FDBEL.ELIG_MTHDTL,
          FDBEL.REG_MASTER,
          FDBEL.BLUECR_ACCOUNT_LOOKUP,
          FDBMD.ACCUMULATOR_XREF,
          FDBEL.REG_MASTER,
          FDBMD.ACCUMULATOR;

          Just make sure this query includes all the tables and views that the original query accesses to.

          regards,
          Xin Wu
          • stoned99
            stoned99
            26 Posts
            ACCEPTED ANSWER

            Re: Explain fails with SQLCODE 203, SQLSTATE 01552 Errorcode -4220

            ‏2013-02-02T00:39:25Z  in response to SystemAdmin
            ok, PMR 41678 227 000 has been created, and used to upload the table structures and traces files. I discussed the PMR mentioned earlier and it has been supersedes by UK67578, which has been applied already.
            • SystemAdmin
              SystemAdmin
              1632 Posts
              ACCEPTED ANSWER

              Re: Explain fails with SQLCODE 203, SQLSTATE 01552 Errorcode -4220

              ‏2013-02-02T01:01:32Z  in response to stoned99
              Hi,
              Thank you! We got the PMR and will update the PMR with any progress.

              regards,
              Xin Wu