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

Skills transfer to DB2 Universal Database

*Table 8. Similarities and differences in database objects*
Data Type Category	Data Type Name in MySQL	Range	Availability in MySQL	Availability in DB2 Express	Data Type Name in DB2 Express	Range	Comment
Numeric	Tinyint	Signed [-128..127] \| Unsigned [0..255]	√	×	NA	NA	There isn't a numeric type of this range for DB2 Express. You can always use Smallint for this range
	Smallint	Signed [-32768..32767] \| Unsigned [0..65535]	√	√	Smallint	Signed [-32768..32767] \| Unsigned [0..65535]	It's an exact match in DB2 Express
	Mediumint	Signed [-8388608 .. 8388607] \| Unsigned [0 .. 16777215]	√	×	NA	NA	There 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]	√	√	Integer	Signed [-2147483648 .. 2147483647] \| Unsigned [0 .. 4294967295]	It's an exact match in DB2 Express
	Bigint	Signed [-9223372036854775808 .. 9223372036854775807] \| Unsigned [0 .. 18446744073709551615]	√	√	Bigint	Signed [-9223372036854775808 .. 9223372036854775807] \| Unsigned [0 .. 18446744073709551615]	It's an exact match in DB2 Express
	Float	Signed [-3.402823466E+38 .. -1.175494351E-38, 0] \| Unsigned [1.175494351E-38 to 3.402823466E+38]	√	√	Real	Signed [-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]	√	√	Double	Signed [-1.7976931348623157E+308 .. -2.2250738585072014E-308, 0] \| Unsigned [2.2250738585072014E-308 .. 1.7976931348623157E+308]	It's an exact match in DB2 Express
String	Char	0 .. 255 (length in characters)	√	√	Char (length in bytes)	0 .. 255	It's an exact match in DB2 Express except the length is in characters for MySQL and bytes for DB2 Express
	Varchar	0 .. 65535 (length in characters)	√	√	Varchar (length in bytes)	0 .. 32672	Different length for DB2 Express. Note that DB2 Express also provides Long Varchar with 0 .. 32700 (length in bytes)
	Binary	0..255 (length in bytes)	√	√	Char (length in bytes)	0 .. 255	It's an exact match in DB2 Express. Matched in length in bytes but not MySQL and DB2 Express name it differently
	Varbinary	0 .. 65535 (length in bytes)	√	√	Varchar (length in bytes)	0 .. 32672	Different 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^8	√	√	Blob	0 .. 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^16	√	√	Blob	0 .. 2147483647 (length in bytes)	Same explanation as the above for DB2 Express BLOB
		MEDIUMBLOB/MEDIUM TEXT - 0 .. 2 ^ 24	√	√	Blob	0 .. 2147483647 (length in bytes)	Same explanation as the above for DB2 Express BLOB
		LONGBLOB/LONGTEXT - 0 .. 2 ^ 32	√	√	Blob	0 .. 2147483647 (length in bytes)	Same explanation as the above for DB2 Express BLOB
Time & Date	Datetime	'1000-01-01 00:00:00' to '9999-12-31 23:59:59' \| format (YYYY-MM-DD HH:MM:SS)	√	√	Timestamp	0001-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)	√	√	Date	0001-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
	Timestamp	With multiple formats (refer to datatype)	√	√	Timestamp	0001-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