SQL0302N error when creating or updating a document

Symptom

Error SQL0302N is returned during a call to create or update an item or document:

SQL0302N  The value of a host variable in the EXECUTE or OPEN 
statement is too large for its corresponding use.

Possible cause

The BLOB or CLOB size in the following Db2® stored procedure definitions is 320 KB:

• ICMCREATEITEMS
• ICMCREATEDOCPART
• ICMUPDATEITEMS
• ICMUPDATEDOCPART

The size of the item information (total attribute size plus additional header information) cannot exceed 320 KB. If this limit is exceeded while creating or updating an item or document, error SQL0302N displays.

Action

To increase the limit, the appropriate stored procedure must be manually dropped and recreated with a larger limit. A good estimate for the appropriate limit is the total size of the item attributes plus an additional 100 KB for header information.

Execute the appropriate DB2 commands, depending on when the error occurs. This example uses 5 MB as the new limit.

For errors during create/update item

DROP PROCEDURE ICMCREATEITEMS;
CREATE PROCEDURE ICMCREATEITEMS
 ( 
OUT     lRC             INTEGER,
OUT     lReason         INTEGER,
OUT     lExtRC          INTEGER,
OUT     lExtReason      INTEGER,
IN      sTraceLevel     SMALLINT,
IN      lReserved1      INTEGER,
IN      szUserInfo      VARCHAR(254),
IN      szUserToken     CHAR(32), 
INOUT   lReserved       INTEGER,
In      lLibraryID      INTEGER,
In      sNumOfItems     SMALLINT,
InOut   ItemCLOB        CLOB(5M), 
InOut   ItemBLOB        BLOB(5M),
InOut   szItemReqNum    SMALLINT,
In      sTran           SMALLINT,
InOut   szTranID        CHAR(26), 
Out     szTranToken     VARCHAR(254)
   ) 
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE DB2SQL
NO DBINFO
FENCED
PROGRAM TYPE SUB
EXTERNAL NAME 'ICMNLSSP!ICMcreate_Items'; 

DROP PROCEDURE ICMUPDATEITEMS;
CREATE PROCEDURE ICMUPDATEITEMS
  (
OUT     lRC             INTEGER, 
OUT     lReason         INTEGER, 
OUT     lExtRC          INTEGER,
OUT     lExtReason      INTEGER,
IN      sTraceLevel     SMALLINT,  
IN      lReserved1      INTEGER, 
IN      szUserInfo      VARCHAR(254), 
IN      szUserToken     CHAR(32),
INOUT   lReserved       INTEGER,  
In      lLibraryID      INTEGER,  
In      sNumOfItems     SMALLINT, 
InOut   ItemCLOB        CLOB(5M),
InOut   ItemBLOB        BLOB(5M), 
InOut   sItemReqNum     SMALLINT, 
In      sTran           SMALLINT, 
InOut   szTranID        CHAR(26),   
Out     szTranToken     VARCHAR(254)
  ) 

DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE DB2SQL
NO DBINFO
FENCED
PROGRAM TYPE SUB
EXTERNAL NAME 'ICMNLSSP!ICMupdate_Items';    

For errors during create/update document

DROP PROCEDURE ICMCREATEDOCPART;
CREATE PROCEDURE ICMCREATEDOCPART
 (
    OUT     lRC             INTEGER,
    OUT     lReason         INTEGER,
    OUT     lExtRC          INTEGER,
    OUT     lExtReason      INTEGER,
    IN      sTraceLevel     SMALLINT,
    IN      lReserved1      INTEGER,
    IN      szUserInfo      VARCHAR(254),
    IN      szUserToken     CHAR(32),
    INOUT   lReserved       INTEGER,
    In      lLibraryID      INTEGER,
    In      sNumOfItems     SMALLINT,
    InOut   ItemCLOB        CLOB(5M),
    InOut   ItemBLOB        BLOB(5M),
    InOut   szItemReqNum    SMALLINT,
    In      sTran           SMALLINT,
    InOut   szTranID        CHAR(26),
    Out     szTranToken     VARCHAR(254)
 )
 DYNAMIC RESULT SETS 0
 LANGUAGE C
 PARAMETER STYLE DB2SQL
 NO DBINFO
 FENCED
 PROGRAM TYPE SUB
 EXTERNAL NAME 'ICMNLSSP!ICMcreate_DocPart';

DROP PROCEDURE ICMUPDATEDOCPART;
CREATE PROCEDURE ICMUPDATEDOCPART
 (
    OUT     lRC             INTEGER,
    OUT     lReason         INTEGER,
    OUT     lExtRC          INTEGER,
    OUT     lExtReason      INTEGER,
    IN      sTraceLevel     SMALLINT,
    IN      lReserved1      INTEGER,
    IN      szUserInfo      VARCHAR(254),
    IN      szUserToken     CHAR(32),
    INOUT   lReserved       INTEGER,
    In      lLibraryID      INTEGER,
    In      sNumOfItems     SMALLINT,
    InOut   ItemCLOB        CLOB(5M),
    InOut   ItemBLOB        BLOB(5M),
    InOut   sItemReqNum     SMALLINT,
    In      sTran           SMALLINT,
    InOut   szTranID        CHAR(26),
    Out     szTranToken     VARCHAR(254)
)
 DYNAMIC RESULT SETS 0
 LANGUAGE C
 PARAMETER STYLE DB2SQL
 NO DBINFO
 FENCED
 PROGRAM TYPE SUB
 EXTERNAL NAME 'ICMNLSSP!ICMupdate_DocPart';