IBM DB2 to Greenplum Migration Reference

Technical information on migration from IBM DB2 to Greenplum.

Last Update: IBM DB2 10.1 and Greenplum 4.2

IBM DB2 to Greenplum Migration Tools

Data Types

Converting built-in SQL data types:

	DB2			Greenplum
1	BLOB(n)		Binary large object	BYTEA
2	CLOB(n)		Character large object	TEXT
3	LONG VARCHAR		Variable-length string	TEXT
4	REAL		32-bit floating point number	REAL
5	VARCHAR(n) FOR BIT DATA	1 ⇐ n ⇐ 32 672	Variable-length binary string	BYTEA

Data type options and attributes:

	DB2	Greenplum
1	GENERATED ALWAYS AS IDENTITY	Sequence and DEFAULT NEXTVAL('seq_name')
2	GENERATED BY DEFAULT AS IDENTITY	Sequence and DEFAULT NEXTVAL('seq_name')

LOB data type options and attributes:

	DB2		Greenplum
1	LOGGED	NOT LOGGED	Removed
2	COMPACT	NOT COMPACT	Removed
3	INLINE LENGTH size		Removed

Built-in SQL Functions

Converting built-in SQL functions:

	DB2		Greenplum
1	CURRENT DATE	Get current date	CURRENT_DATE
2	CURRENT TIMESTAMP	Get current date and time	CURRENT_TIMESTAMP

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

	DB2	Greenplum
1	GENERATED ALWAYS AS IDENTITY	SEQUENCE and DEFAULT NEXTVAL('seq_name')
2	IN data_tablespace	Removed
3	INDEX IN index_tablespace	Removed
4	COMPRESS YES \| NO	Removed

DEFAULT clause:

	DB2	Greenplum
1	col INTEGER WITH DEFAULT	col INTEGER DEFAULT 0

ALTER TABLE Statement

Converting ALTER TABLE statement keywords and clauses:

	DB2	Greenplum
1	ADD PRIMARY KEY (col1, …)	Added DISTRIBUTED BY to CREATE TABLE to define data distribution
2	ALTER COLUMN col RESTART WITH num	Changed to ALTER SEQUENCE seq_name RESTART WITH num
3	PCTFREE integer	Commented

CREATE INDEX Statement

Converting CREATE INDEX statement keywords and clauses:

	DB2	Greenplum
1	UNIQUE	Adding DISTRIBUTED BY to CREATE TABLE to define data distribution
2	CLUSTER	Removed
3	COMPRESS NO \| YES	Removed
4	ALLOW REVERSE SCANS	Removed
5	PCTFREE integer	WITH (FILLFACTOR = 100 - integer)
6	MINPCTUSED integer	Removed
7	SPECIFICATION ONLY	Index is commented

Other SQL Statements

Converting other SQL statements, their keywords and clauses:

	DB2	Greenplum
1	CONNECT TO database	Commented
2	CONNECT RESET	Commented
3	COMMENT ON COLUMN table.column IS 'text'	No changes required
4	COMMENT ON INDEX index IS 'text'	No changes required
5	COMMENT ON TABLE table IS 'text'	No changes required
6	COMMIT [WORK]	COMMIT [WORK \| TRANSACTION]

Command Line Processor (CLP) Commands

Converting DB2 Command Line Processor (CLP) commands:

	DB2	Greenplum
1	TERMINATE	Commented

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference