Leveraging MySQL skills to learn DB2 Express: DB2 versus MySQL administration and basic tasks

Skills transfer to DB2 Universal Database

Return to article

Table 8. Similarities and differences in database objects
Data Type CategoryData Type Name in MySQLRangeAvailability in MySQLAvailability in DB2 ExpressData Type Name in DB2 ExpressRangeComment
NumericTinyintSigned [-128..127] | Unsigned [0..255]×NANAThere isn't a numeric type of this range for DB2 Express. You can always use Smallint for this range
SmallintSigned [-32768..32767] | Unsigned [0..65535]SmallintSigned [-32768..32767] | Unsigned [0..65535]It's an exact match in DB2 Express
MediumintSigned [-8388608 .. 8388607] | Unsigned [0 .. 16777215]×NANAThere isn't a numeric type of this range for DB2 Express. You can always use Integer for this range
Int (or Integer)Signed [-2147483648 .. 2147483647] | Unsigned [0 .. 4294967295]IntegerSigned [-2147483648 .. 2147483647] | Unsigned [0 .. 4294967295]It's an exact match in DB2 Express
BigintSigned [-9223372036854775808 .. 9223372036854775807] | Unsigned [0 .. 18446744073709551615]BigintSigned [-9223372036854775808 .. 9223372036854775807] | Unsigned [0 .. 18446744073709551615]It's an exact match in DB2 Express
FloatSigned [-3.402823466E+38 .. -1.175494351E-38, 0] | Unsigned [1.175494351E-38 to 3.402823466E+38]RealSigned [-3.402823466E+38 .. -1.175494351E-38, 0] | Unsigned [1.175494351E-38 to 3.402823466E+38]It's an exact match in DB2 Express
Double (or Real)Signed [-1.7976931348623157E+308 .. -2.2250738585072014E-308, 0] | Unsigned [2.2250738585072014E-308 .. 1.7976931348623157E+308]DoubleSigned [-1.7976931348623157E+308 .. -2.2250738585072014E-308, 0] | Unsigned [2.2250738585072014E-308 .. 1.7976931348623157E+308]It's an exact match in DB2 Express
StringChar0 .. 255 (length in characters) Char (length in bytes) 0 .. 255It's an exact match in DB2 Express except the length is in characters for MySQL and bytes for DB2 Express
Varchar0 .. 65535 (length in characters)Varchar (length in bytes)0 .. 32672Different length for DB2 Express. Note that DB2 Express also provides Long Varchar with 0 .. 32700 (length in bytes)
Binary0..255 (length in bytes)Char (length in bytes) 0 .. 255It's an exact match in DB2 Express. Matched in length in bytes but not MySQL and DB2 Express name it differently
Varbinary0 .. 65535 (length in bytes)Varchar (length in bytes)0 .. 32672Different length for DB2 Express. Note that DB2 Express also provides Long Varchar with 0 .. 32700 (length in bytes)
BLOB (Binary)/TEXT (Characters)TinyBlob/TinyText - 0 .. 2^8Blob0 .. 2147483647 (length in bytes)MySQL provides binary and text LOBs. They are only differs in size. DB2 Express on the other hand, provides BLOB, CLOB(same length in bytes as BLOB) and DBCLOB (0 .. 1073741823 in characters)
BLOB/TEXT - 0 .. 2^16Blob0 .. 2147483647 (length in bytes)Same explanation as the above for DB2 Express BLOB
MEDIUMBLOB/MEDIUM TEXT - 0 .. 2 ^ 24Blob0 .. 2147483647 (length in bytes)Same explanation as the above for DB2 Express BLOB
LONGBLOB/LONGTEXT - 0 .. 2 ^ 32Blob0 .. 2147483647 (length in bytes)Same explanation as the above for DB2 Express BLOB
Time & DateDatetime'1000-01-01 00:00:00' to '9999-12-31 23:59:59' | format (YYYY-MM-DD HH:MM:SS)Timestamp0001-01-01-00.00.00.000000 to 9999-12-31-24.00.00.000000 | format (YYYY-MM-DD-HH.M.SS.xxxxxx)DB2 Express closest match is Timestamp
Date'1000-01-01' to '9999-12-31'| format (YYYY-MM-DD)Date0001-01-01 to 9999-12-31 | format (YYYY-MM-DD)It matches except DB2 Express allows smallest date to be one millenium more than MySQL
TimestampWith multiple formats (refer to datatype)Timestamp0001-01-01-00.00.00.000000 to 9999-12-31-24.00.00.000000 | format (YYYY-MM-DD-HH.M.SS.xxxxxx)Timestamp is closest in DB2 Express. Note, Timestamp in MySQL allows many formats

Return to article