Comprehensive understanding of various data types in DB2 Databases

Comprehensive understanding of various data types in DB2 databases the built-in data types of DB2 databases can be divided into numeric, character string, and 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 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. Www.2cto.com * 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 (p) and decimal place (s) of the number ). 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. Www.2cto.com * Single-precision floating-point. REAL: Single-precision floating-point is a 32-bit approximate value of a 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 a 64-bit approximate value of a 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 variable-length string of the VARCHAR (n), character varying (n), or char varying (n): VARCHAR (n) type. The maximum length is 32,672 bytes. * Long varchar: a long varchar string can contain up to 32,700 bytes. * Character Large Object String (Character Large Object String), CLOB (n [K | M | G]): CLOB is a variable-length String with a maximum length of 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,024 (the maximum value of n is 2,097,152 ). If nM is specified, the maximum length is n * 1,048,576 (the maximum value of n is 2,048 ). If nG is specified, the maximum length is n * 1,073,741,824 (the maximum value of n is 2 ). CLOB is used to store data based on single-byte character set (SBCS) characters or mixed (Multi-byte character set (MBCS) and SBCS) characters. 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. Www.2cto.com * 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]): the double-byte character large object is a variable-length double-byte character graphic 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,024 (the maximum value of n is 1,048,576 ). If nM is specified, the maximum length is n * 1,048,576 (the maximum value of n is 1,024 ). If nG is specified, the maximum length is n * 1,073,741,824 (the maximum value of n is 1 ). DBCLOB is used to store data based on big DBCS (double-byte character set) characters. 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,024 (the maximum value of n is 2,097,152 ). If nM is specified, the maximum length is n * 1,048,576 (the maximum value of n is 2,048 ). If nG is specified, the maximum length is n * 1,073,741,824 (the maximum value of n is 2 ). Datetime 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 composed of three parts (hours, minutes, And seconds ). 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 composed 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, CHAR scalar functions (see "parts of speech" in SQL) can be used to create string representations of date and time values. * A date value string is 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 Time Value string starts with a number and is a string of no less than four 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. The above is the data types in the DB2 database that I have summarized for you. It may not be comprehensive yet. I will continue to introduce it to you later. This article is a good learning material for some database beginners. I hope you can make full use of the learning resources.