Although the database engine that you use to build an object store
(IBM® DB2®, Microsoft SQL Server, or Oracle)
will not matter in most cases, there are a few differences in the
way each database engine handles empty strings and database
tables that you should note.
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, you
should refrain from setting string-valued properties (both
single and multi-valued) to an empty string (""). This includes
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 using an Oracle database will 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 will 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 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 8K: row-size limit is 8101 bytes.
- Tablespace page size of 16K: row-size limit is 16293 bytes.
- Tablespace page size of 32K: 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 will be 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 setting of the defined tablespace page size described
above. The Content Engine adds
a column to a table whenever a distinct new property
is added to any class in the family of classes 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, 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 32k.
Note: You
can enable table overflow support on an object store,
which creates an overflow table if the 32K row-size
limit of a base
DB2 table
is exceeded. With this support enabled, there's a potential
DB2 query
impact.
- SQL Server: Row-size
limit of 8060 bytes. Because the row-size limit is based on the
actual data 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 given 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 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 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 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 andIBM FileNet® Image Services(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 used by a LOB descriptor in a row can range
from approximately 60 to 300 bytes.
- SQL Server
- Data type: ntext
- Maximum character length: 1,073,741,823
- Querying: Queryable using only the operators LIKE andIBM FileNet Image Services(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 andIBM FileNet Image Services(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, LOB's 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 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.