[TRAFODION-2759] Use of rowset with large memory leads to various errors at runtime

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: any
• Fix Version/s: 2.3
• Component/s: sql-cmp
• Assignee: Suresh Subbiah
• Reporter: Suresh Subbiah
• Created: 30/Sep/17 03:24
• Updated: 15/Sep/18 07:32
• Resolved: 03/Oct/17 22:53

Description

We are going to impose an upper limit on the maximum amount of memory a rowset can take at compile time. rowset is here defined as all the all the array parameters in the query. Max memory is computed by sum of storage lengths of each type of the array parameters multiplied by declared rowset length (# of rows in rowset).

Limit is specified by cqd MEMORY_LIMIT_ROWSET_IN_MB. Default is 500. Can be set to -1 or 0 to avoid this check and revert to current behaviour.

CREATE TABLE TRAFODION.SCH.MM_FMIS_BILL
(
BILL_ID VARCHAR(50 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, OBJECT_ID VARCHAR(100 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, OBJECT_TYPE VARCHAR(20 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, INTERFACE_TYPE VARCHAR(20 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, SYN_FLAG NUMERIC(3, 0) DEFAULT NULL NOT SERIALIZED
, SYN_DATE TIMESTAMP(6) DEFAULT NULL NOT SERIALIZED
, SYN_MSG VARCHAR(4000 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
, SYN_COUNT NUMERIC(9, 0) DEFAULT NULL NOT SERIALIZED
, LOCKING_ID VARCHAR(36 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
, CREATE_DATE TIMESTAMP(6) DEFAULT NULL NOT SERIALIZED
, MODIFY_DATE TIMESTAMP(6) DEFAULT NULL NOT SERIALIZED
, REQUEST_MSG VARCHAR(32700 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
, RESPONSE_MSG VARCHAR(32700 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
, EXCEPTION_MSG VARCHAR(32700 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
, PRIMARY KEY (BILL_ID ASC)
)
ATTRIBUTES ALIGNED FORMAT
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'GZ',
MEMSTORE_FLUSH_SIZE = '1073741824'
)
;

PREPARE S1 FROM
UPSERT USING LOAD INTO "MM_FMIS_BILL"
("BILL_ID", "OBJECT_ID", "OBJECT_TYPE", "INTERFACE_TYPE", "SYN_FLAG", "SYN_DATE", "SYN_MSG", "SYN_COUNT", "LOCKING_ID", "CREATE_DATE", "MODIFY_DATE", "REQUEST_MSG", "EXCEPTION_MSG")
VALUES (?[3000], ?[3000], ?[3000], ?[3000], ?[3000], ?[3000], ?[3000], ?[3000], ?[3000], ?[3000], ?[3000], ?[3000], ?[3000]) ;

– With the change, this error will be seen.

• • • ERROR[30050] Memory required by input rowset buffer is 797 MB, exceeding specified limit of 500. Row length is 278581 bytes and rowset has a maximum of 3000 elements.

• • • ERROR[8822] The statement was not prepared.