7.3. IBM Db2

<< Prev   Next >>

• Right operand of the LIKE comparison operation can only be a literal (parameter) or an expression over literals.Template characters are only "_" (any character) and "%" (a sequence of any characters).
• Untyped NULL. The type of column resulting from the SELECT NULL query is the most common flexible type, not a simple type compatible with any other. You cannot use such columns in operations where fields of flexible types cannot be used. For example, SELECT ISNULL(f1 + 1, 1) FROM (SELECT NULL f1) t1 on Db2 will cause an error as an operand of the <+> operation cannot be a field of a flexible type.
• Maximum numeric data length is 31 characters, not 38.
• Maximum length of resources of accumulation and accounting registers is 25 characters, not 32.
• Maximum size of unlimited length data is 1 GB.
• Maximum number of columns in the operator's selection list cannot exceed 1012. When you define the number of columns, take into account that 1C:Enterprise creates several columns in the DBMS table for fields of the flexible type.
• Rules for assessing the result accuracy when performing arithmetic operations are different from other DBMS.
• Subquery in the BY section of the query language may slow down the query execution. If a subquery is repeatedly used in the BY section (for example, joining several tables by conditions that contain a subquery), the query might not be executed.
• If a query contains a concatenation operation of 10 or more string values that include fields and literals, and the concatenation result is involved in the comparison, an error may occur: DBMS error: SQL0401N. The operands specified for the '...' operation have incompatible data types.
• Join in a query is not allowed if two tables are joined with a condition that includes a comparison of the table fields.
• If the COLLATION_SEQUENCE parameter in the DBMS settings is not equal to UCA500R1_LROOT_AN_CX_EX_FX_HX_NX_S2, the following happens when you start the application:
  • Background database configuration update is not supported.
  • Case sensitivity when comparing strings. In implicit string comparisons performed by DISTINCT, GROUP BY, and UNION of the query language, uppercase and lowercase letters differ. Query execution has the following specifics:
    • When you use the DISTINCT and UNION (without ALL) statements, field values of the query result of the String type are considered different if they differ only in letter cases. When you use other DBMS, they are considered the same.
    • When you use the ORDER BY statement, the String type values that differ only in the letter case are considered different:
      • If the query contains the UNION, UNION ALL, or DISTINCT statements.
      • Query selection list contains the CASE expression which has nested queries.
      • Current user has data access restrictions, and the query does not contain the ALLOWED keyword.
    • In other cases, string data comparison is case-insensitive. String type values that differ only in the letter case are considered the same:
      • When you explicitly compare fields of the String type.
      • When you use the GROUP BY statement.
      • In the ORDER BY statement except for the listed cases.

        The COLLATION_SEQUENCE DBMS parameter is set when the database is created. If the database was created by 1C:Enterprise version 8.1.12 or earlier, this parameter is not equal to UCA500R1_LROOT_AN_CX_EX_FX_HX_NX_S2 and the above specifics will take place. If the database is created by 1C:Enterprise version 8.1.13 and later, this parameter is set to UCA500R1_LROOT_AN_CX_EX_FX_HX_NX_S2 and the above restrictions will not take place.

• If one of the following situations is detected:
  • Long query execution time.
  • Large number of hard drive operations during query execution.
  • HASH JOIN statement in the query plan.
  • Query starts executing significantly faster when the current query optimization changes from 5 to 0.

    We recommend that you execute the db2set DB2_OVERRIDE_BPF=5000 command and restart the IBM Db2 DBMS. Do not perform it on IBM Db2 9.7 FixPack 5 and higher DBMS versions.

• If the query involves tables of configuration objects that are a part of a common attribute that is a separator, you cannot use subqueries in the conditions of joining tables.

<< Prev   Next >>