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