Reached LOB Segments threshold limit

Database
Enthusiast

Reached LOB Segments threshold limit

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,
     DEFAULT MERGEBLOCKRATIO
     (
      POA_ID INTEGER NOT NULL,
      STATUS VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      CREATED_BY VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      SUBMIT_DATE TIMESTAMP(6),
      EXPIRATION_DATE TIMESTAMP(6),
      WORKFLOW_STATUS VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      POA_RECORD XML,
      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,
      STATUS_DATE TIMESTAMP(6),
CONSTRAINT pk PRIMARY KEY ( POA_ID ))
;

 

We run the following SQL:

SELECT
  POA_Record_XML.POA_ID,
        POA_Record_XML.ACCT_NOTATED,
        POA_Record_XML.EXPIRATION_DATE,
        POA_Record_XML.MISC1,
        POA_Record_XML.STATUS,
        POA_Record_XML.STATUS_DATE,
        POA_Record_XML.SUBMIT_DATE,
        POA_Record_XML.SUBMIT_TEAM,
        POA_Record_XML.WORKFLOW_STATUS,
         /* 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,

        CASE
            WHEN POA_Record_XML.WORKFLOW_STATUS = 'SEND FOR REVIEW' THEN 'Y' ELSE 'N'
        END AS SEND_FOR_REVIEW_IND,

        CASE
            WHEN POA_Record_XML.WORKFLOW_STATUS = 'COMPLETE' THEN 'Y' ELSE 'N'
        END AS COMPLETE_IND,

        CASE
            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.  [7548] Reached LOB Segments threshold limit.

Are we using the correct XML function?

  • lob
  • lob segments
  • xml
  • xml function
  • xmlextract
2 REPLIES
Enthusiast

Re: Reached LOB Segments threshold limit

ALSO

 

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 

Enthusiast

Re: Reached LOB Segments threshold limit

UPDATE:

 

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.