[#CONF-6607] DB2 error when BLOB data too large (Confluence page)

Confluence

DB2 error when BLOB data too large (Confluence page)

Created: 18/Jul/06 02:07 AM Updated: 14/Aug/06 07:40 PM

Component/s:

Database / Hibernate

Affects Version/s:

2.2.6

Fix Version/s:

2.2.7

Time Tracking:

Not Specified

Issue Links:

Reference

This issue is related to:

~~CONF-3979~~ DB2 clob columns default to 255.

CONF-3452 DB2 Support

JRA-4912 Handle CLOB/BLOB limits in DB2

Participants:	Ivan Benko [Atlassian], Kim Tang and Okan Karakis
Since last comment:	2 years, 3 weeks, 2 days ago
Resolution Date:	18/Jul/06 05:59 PM
Labels:

Description

« Hide

For details, please refer to the support ticket below:

https://support.atlassian.com/browse/CSP-4584

Error Message: (HibernateTemplate): data integrity violated by SQL ''; nested exception is com.ibm.db2.jcc.b.SqlException: The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use.

All

Comments

Work Log

Change History

Sort Order:

[ Permlink | « Hide ]

Okan Karakis added a comment - 18/Jul/06 07:52 AM

I used the DB2 generated commands below to increase the size to 500,000 bytes which seemed to resolve the issue.

CONNECT TO CONFLUEN USER "db2inst1" USING ;
CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE DB2INST1.BODYCONTENT ( BODYCONTENTID BIGINT NOT NULL , BODY CLOB (500000 ) LOGGED NOT COMPACT , CONTENTID BIGINT ) IN USERSPACE1 ', 1, ? );
;
CONNECT RESET;

[ Permlink | « Hide ]

Ivan Benko [Atlassian] added a comment - 18/Jul/06 05:59 PM

Increased the CustomClobType in BodyContent.hbm.xml field length to 2^31 - 1 to allow storing of BLOBs up to the size of 2 Gb.

[ Permlink | « Hide ]

Kim Tang added a comment - 14/Aug/06 02:59 AM

Increasing the BLOBs size to larger than 1GB broke Confluence during database setup on DB2. DB2 does not allow transaction logging of BLOBs larger than 1GB.

2006-08-14 16:02:22,494 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: create table BODYCONTENT (BODYCONTENTID bigint not null, BODY clob(2147483647), CONTENTID bigint, primary key (BODYCONTENTID))
2006-08-14 16:02:22,571 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -355, SQLSTATE: 42993, SQLERRMC: BODY
2006-08-14 16:02:55,828 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: create index body_content_idx on BODYCONTENT (CONTENTID)
2006-08-14 16:02:55,921 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT
2006-08-14 16:02:56,030 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: alter table BODYCONTENT add constraint FKA898D4778DD41734 foreign key (CONTENTID) references CONTENT
2006-08-14 16:02:56,108 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT
2006-08-14 16:05:19,808 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: create table BODYCONTENT (BODYCONTENTID bigint not null, BODY clob(2147483647), CONTENTID bigint, primary key (BODYCONTENTID))
2006-08-14 16:05:19,824 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -355, SQLSTATE: 42993, SQLERRMC: BODY
2006-08-14 16:05:39,740 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: create index body_content_idx on BODYCONTENT (CONTENTID)
2006-08-14 16:05:39,787 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT
2006-08-14 16:05:39,818 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: alter table BODYCONTENT add constraint FKA898D4778DD41734 foreign key (CONTENTID) references CONTENT
2006-08-14 16:05:39,834 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT
2006-08-14 16:05:44,696 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT
2006-08-14 16:05:44,696 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT
2006-08-14 16:05:44,727 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-204;42704;JIRA.BODYCONTENT
2006-08-14 16:05:44,727 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-204;42704;JIRA.BODYCONTENT
2006-08-14 16:05:44,758 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-204;42704;JIRA.BODYCONTENT
2006-08-14 16:05:44,758 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-204;42704;JIRA.BODYCONTENT

[ Show » ]

Kim Tang added a comment - 14/Aug/06 02:59 AM Increasing the BLOBs size to larger than 1GB broke Confluence during database setup on DB2. DB2 does not allow transaction logging of BLOBs larger than 1GB. 2006-08-14 16:02:22,494 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: create table BODYCONTENT (BODYCONTENTID bigint not null, BODY clob(2147483647), CONTENTID bigint, primary key (BODYCONTENTID)) 2006-08-14 16:02:22,571 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -355, SQLSTATE: 42993, SQLERRMC: BODY 2006-08-14 16:02:55,828 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: create index body_content_idx on BODYCONTENT (CONTENTID) 2006-08-14 16:02:55,921 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT 2006-08-14 16:02:56,030 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: alter table BODYCONTENT add constraint FKA898D4778DD41734 foreign key (CONTENTID) references CONTENT 2006-08-14 16:02:56,108 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT 2006-08-14 16:05:19,808 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: create table BODYCONTENT (BODYCONTENTID bigint not null, BODY clob(2147483647), CONTENTID bigint, primary key (BODYCONTENTID)) 2006-08-14 16:05:19,824 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -355, SQLSTATE: 42993, SQLERRMC: BODY 2006-08-14 16:05:39,740 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: create index body_content_idx on BODYCONTENT (CONTENTID) 2006-08-14 16:05:39,787 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT 2006-08-14 16:05:39,818 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute Unsuccessful: alter table BODYCONTENT add constraint FKA898D4778DD41734 foreign key (CONTENTID) references CONTENT 2006-08-14 16:05:39,834 ERROR [hibernate.tool.hbm2ddl.SchemaExport] execute DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT 2006-08-14 16:05:44,696 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT 2006-08-14 16:05:44,696 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JIRA.BODYCONTENT 2006-08-14 16:05:44,727 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-204;42704;JIRA.BODYCONTENT 2006-08-14 16:05:44,727 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-204;42704;JIRA.BODYCONTENT 2006-08-14 16:05:44,758 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-204;42704;JIRA.BODYCONTENT 2006-08-14 16:05:44,758 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-204;42704;JIRA.BODYCONTENT

[ Permlink | « Hide ]

Ivan Benko [Atlassian] added a comment - 14/Aug/06 07:40 PM

Hi Kim,

Thanks for pointing out this limitation. I just confirmed this after re-reading the relevant chapter in the db2 manual.

Also have added this information to the docs:

http://confluence.atlassian.com/display/DOC/Known+Issues+For+All+Databases

Regards,
Ivan