We have a table that contains about 30,000 rows and is defined as:
CREATE MULTISET TABLE MKTSPACE.POA_RECORD_XML ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
POA_ID INTEGER NOT NULL,
STATUS VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CREATED_BY VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
WORKFLOW_STATUS VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
ACCT_NOTATED VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,
SUBMIT_TEAM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
MISC1 VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
CONSTRAINT pk PRIMARY KEY ( POA_ID ))
We run the following SQL:
/* XML EXTRACTS fieldname.function = poa_record.xmlextract */
CAST(POA_RECORD.XMLEXTRACT('/variable/type',NULL) AS VARCHAR(200) CHARACTER SET UNICODE ) AS POA_TYPE,
CAST(POA_RECORD.XMLEXTRACT('/variable/isValid',NULL) AS VARCHAR(3) CHARACTER SET UNICODE ) AS POA_VALID,
WHEN POA_Record_XML.WORKFLOW_STATUS = 'SEND FOR REVIEW' THEN 'Y' ELSE 'N'
END AS SEND_FOR_REVIEW_IND,
WHEN POA_Record_XML.WORKFLOW_STATUS = 'COMPLETE' THEN 'Y' ELSE 'N'
END AS COMPLETE_IND,
WHEN POA_Record_XML.WORKFLOW_STATUS = 'RETENTION' THEN 'Y' ELSE 'N'
END AS RETENTION_IND
FROM MKTSPACE.POA_RECORD_XML ;
We receive the following error:
SELECT Failed.  Reached LOB Segments threshold limit.
Are we using the correct XML function?
we had a subset of 100 records and it was working but when we jumped it up to the full amount (34,000) it errors out
After searching through knowledge articles, it seems that this may be an issue of XML shredding. It's a known issue with no workaround that has a fix in 10.15.04.01. We are at 10.15.02.11 on our production box, but just applied quarterly maintenance to our DRT box, so I'm going to have the users try it there.