NUMBER

NUMBER is a new datatype that represents an exact numeric value with fixed precision and scale.

The syntax for NUMBER datatype is: 

NUMBER [(precision [,scale])] [SIGNED|UNSIGNED]

Where,

precision specifies the total number of digits. The supported range is 1 to 128.

scale specifies the number of digits to the right of the decimal point. It should not exceed precision. The supported range is 0 to 128.

Precision, scale values and sign type are optional. The default is NUMBER (18,6) SIGNED. If both precision and scale values are not specified, the default precision and scale values are used. If the precision value is specified but the scale value is not specified, then the scale is zero.

The NUMBER datatype is stored in binary and is supported in hardware for signed numbers with a precision of up to 9 and unsigned numbers with a precision of up to 18. In all other cases, the number is supported in software, which is less efficient.

Two new attributes NUMBER_DEFAULT_PRECISION and NUMBER_DEFAULT_SCALE are introduced to customize the default precision and scale values for NUMBER data type. For more information, see Attributes.

The NUMBER datatype is supported by:

  • All SQL/MX Utilities

  • All JDBC and ODBC drivers

Embedded C/C++ and COBOL applications do not support the NUMBER datatype.

Considerations

  • The NUMBER datatype is implemented as a mapping to existing NUMERIC data type. The SHOWDDL command displays the corresponding NUMERIC mapping of the NUMBER column.

  • The queries using NUMBER datatype cannot be cached by MFC.

Examples

This example shows the NUMBER column created without precision and scale values specified. The default (precision, scale) values, that is, (18,6) are used. SHOWDDL output displays it as NUMERIC(18,6). 
>>create table numtest1(col1 NUMBER);

--- SQL operation complete.
>>showddl numtest1;
 
CREATE TABLE CAT.SCH.NUMTEST1
  (
    COL1                             NUMERIC(18, 6) DEFAULT NULL
  )
  LOCATION \NSK.$DATA.ZSDC68HQ.HF6XZH00
  NAME NSK_DATA_ZSDC68HQ_HF6XZH00
  ATTRIBUTES BLOCKSIZE 4096
  NO PARTITION
  ;
--- SQL operation complete.
This example shows the NUMBER column created with only precision value specified. The scale value is considered as '0'. 
>>create table numtest2 ( col1 NUMBER(20));

--- SQL operation complete.
 
>>showddl numtest2;
 
CREATE TABLE CAT.SCH.NUMTEST2
  (
    COL1                   NUMERIC(20, 0) DEFAULT NULL
  )
  LOCATION \NSK.$DATA2.ZSDC68HQ.JRW5ZK00
  NAME NSK_DATA2_ZSDC68HQ_JRW5ZK00
  ATTRIBUTES BLOCKSIZE 4096
  NO PARTITION
  ;
 
--- SQL operation complete.
This example shows the NUMBER column created with both the precision and scale values specified. 
>>create table numtest3 ( col1 NUMBER(20,10));
--- SQL operation complete.
 
>>showddl numtest3;

CREATE TABLE CAT.SCH.NUMTEST3
  (    
				COL1                   NUMERIC(20, 10) DEFAULT NULL
  )
  LOCATION \NSK.$DATA.ZSDC68HQ.X63DCR00
  NAME NSK_DATA_ZSDC68HQ_X63DCR00
  ATTRIBUTES BLOCKSIZE 4096
  NO PARTITION
  ;
 
--- SQL operation complete.