Re: SQL0101 - length of a table's record

On 10-Aug-2016 10:38 -0500, Darryl Freinkel wrote:

Something I have not had to deal with before.

I have to create a table that has about 400 fields which are mostly
VARCHAR and of length 100 to 2000 each. So I get SQL0101.

I have always heard that the table sizes can go into the yoda byte
sizes but never a record length maximum.

For now I have reduced the field sizes to 50 chars to get passed the
issue.

This is a V5R4 system.

Newer doc references, but little difference despite denoted ≥changes≤:

[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzlimtabs.htm]
_SQL limits_
"The following tables describe certain SQL and database limits imposed by the DB2® for i database manager.
Note:

System storage limits may preclude the limits specified here. For example, see _Maximum row sizes_ (see following link).
…"

IBM i 7.1->Database->Reference->SQL reference->Statements->CREATE TABLE
[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzhctabl.htm?view=kc#rbafzhctabl__rbafzmaxrcd]
"…
_Maximum row sizes_

There are two maximum row size restrictions referred to in the description of column-definition.

• ≥The maximum row buffer size is 32766 or, if a VARCHAR, VARGRAPHIC, VARBINARY, LOB, or XML column is specified, 32740.≤

• ≥The maximum row data size is 3 758 096 383 if a LOB or XML column is specified; this size is determined when a row is inserted or updated. If a LOB or XML column is not specified, then the maximum row data size is 32766 or, if a VARCHAR, VARGRAPHIC, or VARBINARY column is specified, 32740.≤
…"

Is there a work around or different way to use these huge record
sizes?

Conspicuously, as inferred from the above, the use of CLOB in place of the larger VARCHAR columns enables larger effective Record Format (RCDFMT) maximum record length. Otherwise, if LOB data is not a good choice, then probably best to introduce a surrogate key and store some of the column data in matching row data across multiple table; a query format, even if not encapsulated in a VIEW, has the same limitation, so there may be little value in this option.