DB2 data types and DB2 built-in data types

The following articles mainly describe the basic knowledge of DB2 data types. We all know that DB2 has multiple built-in data types, including. Numeric), string character string), graphic string), binary string) or datetime ).

There is also a special data type called DATALINK. The DATALINK value contains logical references to files not stored in the database.

Numeric DB2 data types include SMALLINT, INTEGER, BIGINT, DECIMAL (p, s), REAL, and DOUBLE. All values have symbols and precision. Precision refers to the number of digits other than symbols in binary or decimal format. If the value of a number is greater than or equal to zero, the symbol is considered as positive.

Small integer, SMALLINT: small integer is a two-byte integer with a precision of 5 digits. The range of a small integer is-32,768 to 32,767.

Big INTEGER, INTEGER, or INT: A big INTEGER is a four-byte INTEGER with a precision of 10 digits. The range of a large integer is-2,147,483,648 to 2,147,483,647.

BIGINT: an integer of eight bytes with a precision of 19 characters. The range of a giant integer ranges from-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

DECIMAL (p, s), DEC (p, s), NUMERIC (p, s), or NUM (p, s ): A decimal value is a compressed decimal number, which has an implicit decimal point. The compressed decimal number is stored in binary-coded decimal, BCD) Notation variants. The decimal place depends on the precision of the number p) and decimal place s ).

Decimal place refers to the number of digits in the decimal part. It cannot be a negative number or be greater than the precision. The maximum precision is 31 bits. The decimal type ranges from-10 ** 31 + 1 to 10 ** 31-1.

Single-precision floating-point), REAL: Single-precision floating point is a 32-bit approximate value of the REAL number. The number can be zero, or in the range from-3.402E + 38 to-1.175E-37 or from 1.175E-37 to 3.402E + 38.

Double-precision floating-point), DOUBLE, double precision or FLOAT: Double-precision floating-point is the 64-bit approximate value of the real number. The number can be zero, or in the range from-1.79769E + 308 to-2.225E-307 or from 2.225E-307 to 1.79769E + 308.

A string is a byte sequence. A string includes a CHAR (n) type fixed-length string and a variable-length string of the VARCHAR (n), long varchar, or CLOB (n) type. The length of a string is the number of bytes in the sequence.

Fixed Length string, CHARACTER (n) or CHAR (n): the length of a fixed length string is between 1 and 254 bytes. If the length is not specified, it is regarded as 1 byte.

Variable-length string. A string of the VARCHAR (n), character varying (n), or char varying (n): VARCHAR (n) type is a variable-length string, which can be up to 32,672 bytes.

Long varchar: a long varchar string can contain up to 32,700 bytes.

Character Large Object String), CLOB (n [K | M | G]): CLOB is a variable-length String, which can contain up to 2,147,483,647 bytes. If only n is specified, the value of n is the maximum length. If nK is specified, the maximum length is n * 1, and the maximum value of 024n is 2,097,152 ). If nM is specified, the maximum length is n * 1,048,576 n and the maximum length is 2,048 ).

If nG is specified, the maximum length is 2 for n * 1,073,741,824 n ). CLOB is used to store data based on the single-byte character set, SBCS) character set or based on the mixed multi-byte character set MBCS) and SBCS character set. A graphical string is a sequence of Two-byte characters.

Graphical strings include fixed-length graphical strings of the GRAPHIC (n) type and variable-length graphical strings of the VARGRAPHIC (n), long vargraphic, and DBCLOB (n) type. The length of a string is the number of double-byte characters in the sequence.

Fixed-length graph string, GRAPHIC (n): the length of a fixed-length graph string is between 1 and 127 double-byte characters. If the length is not specified, it is considered to be 1 dubyte.

VARGRAPHIC (n): VARGRAPHIC (n) is a variable-length string with a maximum length of 16,336 double-byte characters.

Long vargraphic: a long vargraphic string can contain up to 16,350 double-byte characters.

Double Byte Character large object string, DBCLOB (n [K | M | G]): Double Byte Character large object is a variable length Double Byte Character graphics string, up to 1,073,741,823 characters. If n is specified, n is the maximum length. If nK is specified, the maximum length is n * 1, and the maximum value of 024n is 1,048,576 ). If nM is specified, the maximum length is n * 1,048,576 n and the maximum length is 1,024 ). If nG is specified, the maximum length is 1 for n * 1,073,741,824 n ). DBCLOB is used to store data based on the double-byte character set of big DBCS. A binary string is a byte sequence. Binary strings include BLOB (n)-type variable-length strings, which are used to hold non-traditional data, slice, voice, or hybrid media, it can also accommodate user-defined types and structured data of user-defined functions.

Binary large object, BLOB (n [K | M | G]): the Binary large object is a variable-length string with a maximum length of 2,147,483,647 bytes. If n is specified, n is the maximum length. If nK is specified, the maximum length is n * 1, and the maximum value of 024n is 2,097,152 ). If nM is specified, the maximum length is n * 1,048,576 n and the maximum length is 2,048 ). If nG is specified, the maximum length is 2 for n * 1,073,741,824 n ).

The datetime DB2 data types include DATE, TIME, and TIMESTAMP. Datetime values can be used in some arithmetic and string operations and are compatible with some strings, but they are neither strings nor numbers.

DATE: DATE is a three-part value year, month, and day ). The year range is from 0001 to 9999. The range of month is from 1 to 12. The range of day is from 1 to n, where the value of n depends on the month. The DATE column is 10 bytes long.

TIME: TIME is a value of hour, minute, and second ). The hour range is from 0 to 24. The minute and second ranges from 0 to 59. If the hour is 24, the values of minute and second are both 0. The TIME column is 8 bytes long.

TIMESTAMP: TIMESTAMP is a value consisting of seven parts: year, month, day, hour, minute, second, And microsecond ). The year range is from 0001 to 9999. The range of month is from 1 to 12. The range of day is from 1 to n, where the value of n depends on the month. The hour range is from 0 to 24. The minute and second ranges from 0 to 59. The microsecond part ranges from 000000 to 999999. If the hour is 24, the minute value, second value, and microsecond value are both 0. The TIMESTAMP column is 26 bytes long. String Representation of the datetime value: although the internal representation of the values of DATE, TIME, and TIMESTAMP is transparent to users, the DATE, TIME, and TIMESTAMP can also be expressed in strings, for the CHAR scalar function, see "part of speech parts of speech)" in SQL. It can be used to create a string representation of a date and time value.

The string of the date value indicates a string of no less than 8 characters starting with a number. The minus zero in the month and day of the date value can be omitted.

A string with a time value starting with a number and a length of not less than 4 characters. The hour part of the time value can be omitted, and the second part can be completely omitted. If the second value is not specified, it is regarded as 0.

The string of the time stamp value indicates a string starting with a number and with a length of not less than 16 characters. The complete time stamp string is represented as a yyyy-mm-dd-hh.mm.ss.nnnnnn. The zeros in front of the month, day, or hour of the time stamp value can be omitted, and the microseconds can be truncated or completely omitted. If the microsecond part of the timestamp value is omitted at the end of zero, it is assumed that the number of vacant digits is zero.

Will the self-growth field design exceed the maximum value? It should not be of the DB2 data type. The range accuracy is 10 digits!