The NUMBER data type supports applications that use the Oracle NUMBER data type.

Important: Db2 Big SQL only tolerates the Oracle NUMBER data type, which is mapped to a data type that is supported in Db2 Big SQL.


When the NUMBER data type is explicitly encountered in SQL statements, the data type is implicitly mapped as follows:
  • If you specify NUMBER without precision and scale attributes, it is mapped to DECIMAL(31,0).
  • If you specify NUMBER(p), it is mapped to DECIMAL(p).
  • If you specify NUMBER(p,s), it is mapped to DECIMAL(p,s).
The maximum supported precision is 31, and the scale must be a positive value that is no greater than the precision. Also, a result of the implicit mapping, messages refer to data type DECIMAL instead of NUMBER. In addition, any operations that describe data types for columns or routines return DECIMAL instead of NUMBER.

Numeric literal support is unchanged: the rules for integer, decimal, and floating-point constants continue to apply. These rules limit decimal literals to 31 digits and floating-point literals to the range of binary double-precision floating-point values. If necessary, you can use a string-to-DECFLOAT(34) cast, using the CAST specification or the DECFLOAT function, for values that are beyond the range of DECIMAL or DOUBLE, up to the range of DECFLOAT(34). There is currently no support for a numeric literal that ends in either D, representing 64-bit binary floating-point values, or F, representing 32-bit binary floating-point values. A numeric literal that includes an E is of type DOUBLE, which you can cast to REAL by using the CAST specification or the REAL cast function.

Db2 Big SQL rounding rules apply.


NUMBER data type support has the following restrictions:
  • There is no support for the following items:
    • A precision attribute greater than 31
    • A precision attribute of asterisk (*)
    • A scale attribute that exceeds the precision attribute
    • A negative scale attribute
  • You cannot create a distinct type with the name NUMBER.