Database Engine Differences

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.