Database Engine Differences
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.
- 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 - 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 CONTAINS and FREETEXT.
- 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 CONTAINS and FREETEXT.
- 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 CONTAINS and FREETEXT.
- 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 CONTAINS and FREETEXT.
- 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 and IS (NOT) NULL. If CBR indexing is enabled for the property, also queryable using CONTAINS and FREETEXT.
- 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 CONTAINS and FREETEXT.
- Indexing: Cannot be specified in a database index.