DB2 UDB To PostgreSQL Conversion Guide Version 1.0
TABLE OF CONTENTS
1. INTRODUCTION...............................................................................................................4
1.1 Purpose.......................................................................................................................................4
1.2 Scope..........................................................................................................................................4
2 CONVERSION REFERENCE................................................................................................5
2.1 Tools...........................................................................................................................................5
2.2 SQL Components - DB2 Objects...............................................................................................5
2.2.1 Data Types.......................................................................................................................................5
2.2.2 Special Data Types..........................................................................................................................5
2.2.3 Table Constraints.............................................................................................................................7
2.2.4 Sequence Number (Auto generated ID column)............................................................................10
2.2.5 Special Objects...............................................................................................................................12
2.2.6 Views..............................................................................................................................................12
2.2.7 Trigger............................................................................................................................................13
2.2.8 Functions........................................................................................................................................14
2.2.9 Stored Procedures..........................................................................................................................15
2.3 SQL Predicates.........................................................................................................................18
2.3.1 BETWEEN Predicate.....................................................................................................................18
2.3.2 EXISTS / NOT EXISTS Predicate..................................................................................................19
2.3.3 IN / NOT IN Predicate....................................................................................................................20
2.3.4 LIKE Predicate................................................................................................................................20
2.3.5 IS NULL / IS NOT NULL Predicate................................................................................................21
2.4Temporary Tables......................................................................................................................21
2.4.1 Using WITH phrase at the top of the query to define a common table expression.......................21
2.4.2 Full-Select in the FROM part of the query.....................................................................................22
2.4.3 Full-Select in the SELECT part of the query..................................................................................23
2.5 CASE Expression.....................................................................................................................24
2.6 Column Functions.....................................................................................................................24
2.7 OLAP Functions........................................................................................................................25
2.7.1 ROWNUMBER & ROLLUP............................................................................................................25
2.8 Scalar Functions.......................................................................................................................26
2.8.1 Scalar Functions - IBM DB2 vs PostgreSQL.................................................................................26
2.9 ORDER BY, GROUP BY & HAVING.......................................................................................31
2.9.1 ORDER BY.....................................................................................................................................31
2.9.2 GROUP BY.....................................................................................................................................32
2.9.3 HAVING..........................................................................................................................................32
2.10 DYNAMIC Cursors..................................................................................................................33
2.11 Joins .......................................................................................................................................34
2.11.1 Self-Join.......................................................................................................................................34
2.11.2 Left-outer Join...............................................................................................................................34
2.11.3 Right-outer Join............................................................................................................................34
2.12 Sub-Query...............................................................................................................................34
2.13 Manipulating Resultset returned by Called Function (Associate..)........................................35
2.14 UNION & UNION ALL.............................................................................................................39
2.14.1 UNION..........................................................................................................................................39
2.14.2 UNION ALL...................................................................................................................................40
2.15 Dynamic SQL..........................................................................................................................41