Although the database engine that you use to build an object store
(IBM® DB2®, Microsoft SQL Server, or Oracle) does not
matter in most cases, there are a few differences in the way each
database engine handles empty strings and database tables.
Empty strings
Each type of database engine treats an empty string (a string
with a length of zero) as follows:
- DB2: String object
of zero length.
- SQL Server: String object
of zero length.
- Oracle: Null. Because Oracle treats empty strings as null, refrain
from setting string-valued properties (both single and multi-valued)
to an empty string (""). These properties include such properties
as the DocumentTitle property of a document and the ChoiceStringValue
property of a choice item in a string-type choice list. Attempting
to set such a property value to an empty string when you are using
an Oracle database can frequently cause an exception stating that
a null value cannot be used. In addition, unless a query specifically
tests for whether property values are null, querying for an object
with one or more empty strings in the values of its properties can
provide unexpected results. Therefore, due to the potential for unexpected
results and exceptions, the use of empty string values is discouraged
if you are using an Oracle database engine.
Database tables
The database tables that are used by each type of database
engine have the following structural differences:
- Table row-size limit:
- DB2: Row-size limit is variable,
depending on the defined tablespace page size:
- Tablespace page size of 8 KB: row-size limit is 8101 bytes.
- Tablespace page size of 16 KB: row-size limit is 16293 bytes.
- Tablespace page size of 32 KB: row-size limit is 32677 bytes.
For DB2,
the row-size limit is applied whenever an attempt is made to add a
column to a table, which is allowed only if the sum of the maximum
sizes of the existing columns, plus that of the new column, is less
than the row-size limit corresponding to the setting of the defined tablespace
page size. Content Platform Engine adds
a column to a table whenever a distinct new property is added to any
class in the family of classes that are sharing that table. For example,
all subclasses of the Document class share the DocVersion
table. Therefore, the row-size limit applies to the total number of
distinct properties, both system and custom, that are defined through
the class definitions of the classes in a family. Because many classes
already have a significant number of system properties, it is recommended
that the tablespace page size be set to 32 KB.
Note:
DB2 for Linux, UNIX and Windows Version
10.1 and earlier and
DB2 for z/OS® have
a 32 KB row-size limit for tables. To address this 32 KB row-size
limit,
Content Platform Engine provides
a table overflow support feature that can be enabled on an object
store, which creates an overflow table if the 32 KB row-size limit
of a base
DB2 table
is exceeded. This feature adds the column for a new class property
into an overflow table when the sum of the new column length plus
the existing column lengths surpass 32 KB (minus overhead). With this
support enabled, there is a potential
DB2 query
impact.
DB2 for Linux, UNIX and Windows Version 10.5 and later
supports an extended row size by default and you do not need to enable
table overflow support on an object store. However, for databases
that are upgraded to
DB2 for Linux, UNIX and Windows Version
10.5 and later, from a release earlier than Version 10.5, you must
run the following command to enable extended row-size support:
UPDATE DATABASE CONFIGURATION FOR dbName USING EXTENDED_ROW_SZ ENAB


- SQL Server: Row-size
limit of 8060 bytes. Because the row-size limit is based on the actual
data that is used in any given row, this limits the number of non-null
property values (and the size of those values) that an object instantiated
from a particular class can have.
- Oracle: No defined row-size limit.
- LocalizedString table (stores the value of the LocalizedText property
of the LocalizedString object):
- SQL Server: Although
the theoretical maximum length of the LocalizedText property is 4000
Unicode characters, the actual maximum is between 3980 and 3995 due
to a length limitation of SQL Server database tables.
The LocalizedString table, in addition to its fixed-length columns,
has two columns that store variable-length Unicode data: locale_name
(stores the value of the LocaleName property) and text (stores the
value of the LocalizedText property). Because SQL Server allows only 8060 bytes
per row and the fixed-length columns in the table require 68 bytes
of storage, the size of both of the locale_name and text columns combined
cannot exceed 7992 bytes (or 3996 Unicode characters). Therefore,
the specific length that is allowed for the LocalizedText property
is determined by the length of the LocaleName property, which allows
up to 16 Unicode characters. For example, if the LocaleName property
of a given object has 10 characters, the LocalizedText property can
hold up to 3986 (3996 minus 10) characters.
- Oracle: The maximum length that is allowed for the LocalizedText
property is 1333 characters.
- Short-string table columns (UsesLongColumn property = false)
have the following characteristics, according to database type:
- DB2
- Data type: vargraphic
- Maximum character length: 4000
- Querying: Queryable using any of the normal operators (>, <, and so on).
If CBR indexing is enabled for the property, also queryable using
full-text search functions such as CONTAINS.
- Indexing: String columns up to a size determined by the database
configuration can be specified in a database index, thus achieving
quick query responses.
- Effective size: Consumes a number of bytes from a row size limit
equal to twice the character length of its string value.
- SQL Server
- Data type: nvarchar
- Maximum character length: 4000
- Querying: Queryable using any of the normal operators (>, <, and so on).
If CBR indexing is enabled for the property, the column is also queryable
by using full-text search functions such as CONTAINS.
- Indexing: String columns up to 900 bytes in size can be specified
in a database index, thus achieving quick query responses.
- Effective size: Consumes a number of bytes from a row size limit
equal to twice the character length of its string value.
- Oracle
- Data type: varchar2
- Maximum character length: 1333
- Querying: Queryable using any of the normal operators (>, <, and so on).
If CBR indexing is enabled for the property, also queryable using
full-text search functions such as CONTAINS.
- Indexing: String columns up to a size determined by the database
configuration can be specified in a database index, thus achieving
quick query responses.
- Long-string table columns (UsesLongColumn property = true)
have the following characteristics, according to database type:
- DB2
- Data type: dbclob
- Maximum character length: 536,870,912
- Querying: Queryable using only the operators LIKE and IS (NOT)
NULL. If CBR indexing is enabled for the property, also queryable
using full-text search functions such as CONTAINS.
- Indexing: Cannot be specified in a database index.
- Effective size: Each large object (LOB) value is stored externally
to its table record in a separate database store. There is a LOB descriptor
for each LOB in a table row that contains control information for
accessing the externally stored LOB data. Because the maximum size
of a LOB column determines the maximum size of a LOB descriptor in
that column, it also affects how many columns can fit in a single row.
Depending on the maximum size of its column, the space
that is used by a LOB descriptor in a row can range from approximately
60 - 300 bytes.
- SQL Server
- Data type: ntext
- Maximum character length: 1,073,741,823
- Querying: Queryable using only the operators LIKE and IS (NOT)
NULL. If CBR indexing is enabled for the property, also queryable
using full-text search functions such as CONTAINS.
- Indexing: Cannot be specified in a database index.
- Effective size: Consumes only 4 bytes from a row size limit, regardless
of the length of its string value. Therefore, if your string-valued
property has limited query requirements, there is a space advantage
to storing it as a long database column even if the size of its maximum
length does not require a long column.
- Oracle
- Data type: clob
- Maximum character length: 1,073,741,823
- Querying: Queryable using only the operators LIKE and IS (NOT)
NULL. If CBR indexing is enabled for the property, also queryable
using full-text search functions such as CONTAINS.
- Indexing: Cannot be specified in a database index.
- Database large objects (LOBs) column sizes are affected by how
much data is configured to be stored in-row, called the inline size. Binary
LOB (BLOB) and Character LOB (CLOB) data might be stored inline with
the rest of the columns in the table, or off-row in a separate LOB
location (which might be specified in a separate tablespace) within
the database. The data in a LOB column is stored in an off-row LOB
location if it exceeds the database inline size for that column.
- On Oracle, the default INLINE size is 4000 bytes.
- On DB2 9.7 and
later, LOBs are auto-inlined up to the LOB descriptor size.
- On SQL Server, some Content Engine columns are specified with
an INLINE size during object store creation.
Inline size settings vary with the database release
version and also with the Content Platform Engine release
version.
String data types are always stored inline,
unless they correspond to user-defined properties defined as long-string
table columns. Values in these columns are stored outside
of the primary row, in the separate off-row LOB location subject to
database release inlining behaviors.
- Numeric data that is specified as a Content Engine double (float64) data type
is stored in a database table differently by each type of database:
- DB2: Numeric data is stored
as a DB2 double (8-byte)
data type. The number has the following range: -1.7976931348623158E+308
to -2.2250738585072014E-308, 0, 2.2250738585072014E-308 to 1.7976931348623158E+308.
- SQL Server: Numeric
data is stored as a SQL Server float
(8-byte) data type with a precision of 15 digits. The number has the
following range: - 1.79E+308 to -2.23E-308, 0, 2.23E-308 to 1.79E+308.
- Oracle: Numeric data is stored as an Oracle NUMBER data type with
a precision of 38 digits. The number can be positive, negative, or
zero and has the following range: 1 x 10E-130 to 9.99...9 x 10E125.