DB2 UDB To PostgreSQL Conversion Guide

1 DB2 UDB To PostgreSQL Conversion GuideVersion UDB TO PostgreSQL Conversion GUIDEDB2 UDB To PostgreSQL MigrationDRAFT VERSION : UDB To PostgreSQL Conversion GuideVersion OF CONTENTS1. Conversion SQL Components - DB2 Data Special Data Table Sequence Number (Auto generated ID column).. Special Stored SQL BETWEEN EXISTS / NOT EXISTS IN / NOT IN LIKE IS NULL / IS NOT NULL Using WITH phrase at the top of the query to define a common table Full-Select in the FROM part of the Full-Select in the SELECT part of the CASE Column OLAP ROWNUMBER & Scalar Scalar Functions - IBM DB2 vs ORDER BY, GROUP BY & ORDER GROUP DYNAMIC Joins .. Left-outer Right-outer Manipulating Resultset returned by Called Function ( ).. UNION & UNION UNION Dynamic UDB To PostgreSQL Conversion GuideVersion Condition Print Output Implicit casting in double to integer double to integer (Round).

2 Double to integer (lower possible integer).. Select from Variables declaration and Conditional statements and flow control (supported by PostgreSQL )..423 UDB To PostgreSQL Conversion GuideVersion IntroductionSince migrating from DB2 UDB to PostgreSQL requires a certain level of knowledge in bothenvironments, the purpose of this document is to identify the issues in the process involvedmigrating from DB2 UDB to PostgreSQL document also relates the required information on PostgreSQL equivalents of DB2 UDB andits syntax of PurposeThe intent of this document is to serve as a valid reference - in the near future - for the process ofmigrating the structure as well as data from IBM DB2 database to PostgreSQL database . ScopeThe scope of this document is limited to the extent of identifying the PostgreSQL equivalents ofvarious SQL components, column / OLAP / Scalar functions, Order by / Group by / Having, Joins,Sub-queries, Union / Intersect / Except clauses that are currently defined for DB2 UDB To PostgreSQL Conversion GuideVersion Conversion ReferenceThis section briefly discusses the different steps involved in Conversion process from DB2 UDB ToolsThe following tools, could be used while migrating data from DB2 to PostgreSQL .

3 Aqua Data Studio and above Mainly used for exporting DB2 data to csv format andimporting csv format into SQL Components - DB2 Data TypesData TypesIBM DB2 PostgreSQLCHAR(n)CHAR(n)DATEDATESome Valid Inputs:now, today, tomorrow, yesterday now ::datetimeDECIMAL(m,n)DECIMAL(m,n)INTEGE RINTEGERSMALLINTSMALLINTTIMESTAMPTIMESTA MPSome Valid Inputs:now, today, tomorrow, yesterdayTIMETIMESome Valid Inputs:nowVARCHAR(n)VARCHAR(n) Special Data TypesSpecial Data TypesIBM DB2 PostgreSQLCLOBTEXT (maximum of 1GB)DB2 UDB To PostgreSQL Conversion GuideVersion (max 1GB) (Binary data - byte array)CURRENT TIMESTAMPCURRENT_TIMESTAMPE xample :CREATE TABLE products (..created_date TIMESTAMP DEFAULTCURRENT_TIMESTAMP,..);CURRENT TIMECURRENT_TMEE xample :CREATE TABLE products (..reordered_time TIMESTAMP DEFAULTCURRENT_TIME,..);CURRENT DATECURRENT_DATEE xample :CREATE TABLE products (..reordered_date TIMESTAMP DEFAULTCURRENT_DATE.)

4 ;GENERATED BY DEFAULT AS IDENTITY Example :CREATE TABLE products (product_no INTEGER nextval( products_product_no_seq ),..);Using SERIALCREATE TABLE products (product_no SERIAL,..);refcursorThis is special data type of CURSOR <cursor_name> refcursor;DB2 UDB To PostgreSQL Conversion GuideVersion Table Check ConstraintsA check constraint is the most generic constraint type. It allows you to specify that the valuein a certain column must satisfy a Boolean (truth-value) / DeclarationIBM DB2 PostgreSQLCREATE TABLE <table> (<column1>,..,<columnX> CONSTRAINT<constraints name> CHECK(<Condition>));CREATE TABLE <table> (<column1>,..,<columnX> CONSTRAINT<constraints name> CHECK(<Condition>));Example UsageCREATE TABLE products (product_no INTEGER,name VARCHAR(30),price INTEGER,category INTEGERCONSTRAINT my_catg CHECK(category IN (1,2,3,4)));CREATE TABLE products (product_no INTEGER,name TEXT,price INTEGER CONSTRAINT positive_price CHECK (price > 0),category INTEGER); Not-Null ConstraintsA not-null constraint simply specifies that a column must not assume the null / DeclarationIBM DB2 PostgreSQLCREATE TABLE <table> (<column1> NOT NULL.)

5 ,<columnX>);CREATE TABLE <table> (<column1> NOT NULL,..,<columnX>);Example UsageDB2 UDB To PostgreSQL Conversion GuideVersion TABLE products (product_no INTEGER NOTNULL,name VARCHAR(30) NOTNULL,price INTEGER CONSTRAINT positive_price CHECK (price > 0));CREATE TABLE products (product_no INTEGER NOTNULL,name TEXT NOT NULL,price INTEGER CONSTRAINT positive_price CHECK (price > 0)); Unique ConstraintsUnique constraints ensure that the data contained in a column or a group of columns isunique with respect to all the rows in the / DeclarationIBM DB2 PostgreSQLCREATE TABLE <table> (<column1> NOT NULL,..,<columnX>CONSTRAINT <constraintname> UNIQUE (<column>)) DATE CAPTURE NONE IN <Datatablespace name> INDEX IN <indextablespace name>;CREATE TABLE <table> (<column1> NOT NULL,..,<columnX>CONSTRAINT <constraint name>UNIQUE (<column>) USING INDEXTABLESPACE <Index tablespace name>) TABLESPACE <Data tablespacename>;Example UsageCREATE TABLE products (product_no INTEGER NOTNULL,name VARCHAR(30) NOTNULL,price INTEGER CONSTRAINT positive_price CHECK (price > 0),CONSTRAINT unq_prod_noUNIQUE (product_no)) DATA CAPTURE NONE INmydataspace INDEX IN myindexspace;CREATE TABLE products (product_no INTEGER NOTNULL,name TEXT NOT NULL,price INTEGER CONSTRAINT positive_price CHECK (price > 0),CONSTRAINT unq_prod_noUNIQUE (product_no) USING INDEXTABLESPACE myindexspace) TABLESPACE mydataspace;DB2 UDB To PostgreSQL Conversion GuideVersion Primary Key ConstraintsTechnically, a primary key constraint is simply a combination of a unique constraint and anot-null / DeclarationIBM DB2 PostgreSQLCREATE TABLE <table> (<column1> NOT NULL.

6 ,<columnX>CONSTRAINT <constraintname> PRIMARY KEY (<column>)) DATE CAPTURE NONE IN <Datatablespace name> INDEX IN <indextablespace name>;CREATE TABLE <table> (<column1> NOT NULL,..,<columnX>CONSTRAINT <constraint name>PRIMARY KEY (<column>) USING INDEXTABLESPACE <Index tablespace name>) TABLESPACE <Data tablespacename>;Example UsageCREATE TABLE products (product_no INTEGER NOTNULL,name VARCHAR(30) NOTNULL,price INTEGER CONSTRAINT positive_price CHECK (price > 0),CONSTRAINT pk_prod_noPRIMARY KEY (product_no)) DATA CAPTURE NONE INmydataspace INDEX IN myindexspace;CREATE TABLE products (product_no INTEGER NOTNULL,name TEXT NOT NULL,price INTEGER CONSTRAINT positive_price CHECK (price > 0),CONSTRAINT pk_prod_noPRIMARY KEY (product_no) USINGINDEX TABLESPACE myindexspace) TABLESPACE mydataspace; Foreign Key ConstraintsA foreign key constraint specifies that the values in a column (or a group of columns) mustmatch the values appearing in some row of another table.

7 We say this maintains thereferential integrity between two related / DeclarationIBM DB2 PostgreSQLDB2 UDB To PostgreSQL Conversion GuideVersion TABLE <table> (<column1> NOT NULL,..,<columnX>CONSTRAINT <constraintname> FOREIGN KEY (<column>)REFERENCES <ref table name>(<column>)) DATE CAPTURE NONE IN <Datatablespace name> INDEX IN <indextablespace name>;CREATE TABLE <table> (<column1> NOT NULL,..,<columnX>CONSTRAINT <constraint name>FOREIGN KEY (<column>) REFERENCES<ref table name>(<column>)) TABLESPACE <Data tablespacename>;Example UsageCREATE TABLE products (product_no INTEGER NOTNULL,name VARCHAR(30) NOTNULL,price INTEGER CONSTRAINT positive_price CHECK (price > 0),CONSTRAINT pk_prod_noPRIMARY KEY (product_no)) DATA CAPTURE NONE INmydataspace INDEX IN myindexspace;CREATE TABLE orders (order_no INTEGER NOTNULL,product_no INTEGER,quantity DECIMAL(12,4),CONSTRAINT fk_prod_noFOREIGN KEY (product_no)REFERENCES products(product_no)) DATA CAPTURE NONE INmydataspace INDEX IN myindexspace;CREATE TABLE products (product_no INTEGER NOTNULL,name TEXT NOT NULL,price INTEGER CONSTRAINT positive_price CHECK (price > 0),CONSTRAINT pk_prod_noPRIMARY KEY (product_no) USINGINDEX TABLESPACE myindexspace) TABLESPACE mydataspace.

8 CREATE TABLE orders (order_no INTEGER NOT NULL,product_no INTEGER,quantity DECIMAL(12,4),CONSTRAINT fk_prod_noFOREIGN KEY (product_no)REFERENCES products(product_no)) TABLESPACE mydataspace; Sequence Number (Auto generated ID column)The data types serial and bigserial are not true types, but merely a notational convenience forsetting up unique identifier columns (similar to the AUTO_INCREMENT property supported bysome other databases).DB2 UDB To PostgreSQL Conversion GuideVersion <sequence name> should be unique for database level and it minvalue n, is the numberat which the sequence : The sequence is always incremented by tables created are later associated with the already created sequence, using nextval('<sequence_name>') / DeclarationIBM DB2 PostgreSQLCREATE TABLE <table> (<column1> NOT NULLGENERATED BY DEFAULT AS IDENTITY(START WITH n, INCREMENT BY x NOCACHE),..,<columnX>);CREATE SEQUENCE <sequence_name>MINVALUE n;CREATE TABLE <table> (<column1> DEFAULT nextval('<sequence_name>').)

9 ,<columnX>);Example UsageCREATE TABLE products (product_no INTEGER NOTNULL GENERATED BY DEFAULT ASIDENTITY (START WITH 11,INCREMENT BY 1, NO CACHE),name VARCHAR(30) NOTNULL,price INTEGER);CREATE SEQUENCE products_seq_prdnoMINVALUE 1;CREATE TABLE products (product_no INTEGER nextval(' products_seq_prdno')name TEXT NOT NULL,price INTEGER CONSTRAINT positive_price CHECK (price > 0),CONSTRAINT pk_prod_noPRIMARY KEY (product_no) USINGINDEX TABLESPACE myindexspace)TABLESPACE mydataspace;DB2 UDB To PostgreSQL Conversion GuideVersion Special CLOBE quivalents / DeclarationIBM DB2 PostgreSQLCLOB(n) - n <= 2 GBTEXT (max 1GB)Example UsageCREATE TABLE CLOB(1M),..);CREATE TABLE orders (..notes TEXT(1M),..); BLOBE quivalents / DeclarationIBM DB2 PostgreSQLBLOB(n) - n <= 2 GBBYTEA (maximum 1GB) binary data bytearrayExample ViewsEquivalents / DeclarationIBM DB2 PostgreSQLCREATE VIEW <view_name> ASsql statement;CREATE OR REPLACE VIEW <view_name>ASsql statement;Example UsageDB2 UDB To PostgreSQL Conversion GuideVersion VIEW products_v ASSELECT x,y.

10 FROM ;CREATE OR REPLACE VIEW products_vASSELECT x,y,..FROM ; TriggerEquivalents / DeclarationIBM DB2 PostgreSQLCREATE TRIGGER <trigger name>AFTER INSERTON <table name>REFERENCINGNEW AS NFOR EACH ROWMODE DB2 SQLBEGIN ;CREATE TRIGGER <trigger name>AFTER INSERTON <table name>FOR EACH ROWEXECUTE PROCEDURE function_name();Example UsageDB2 UDB To PostgreSQL Conversion GuideVersion TABLE emp_audit(operation CHAR(1) NOTNULL,..);CREATE TRIGGER process_emp_auditAFTER INSERTON emp_auditREFERENCINGNEW AS NFOR EACH ROWMODE DB2 SQLBEGIN ATOMICINSERT INTO emp_auditSELECT I , now(), user, N.*;END;CREATE TABLE emp_audit(operation CHAR(1) NOT NULL,..);CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGERLANGUAGE plpgsqlAS$emp_audit$BEGININSERT INTO emp_audit SELECT I , now(), user, NEW.*;RETURN NEW;END;$emp_audit$;CREATE TRIGGER emp_auditAFTER INSERT ON emp_auditFOR EACH ROW EXECUTEPROCEDURE process_emp_audit(); FunctionsEquivalents / DeclarationIBM DB2 PostgreSQLDB2 UDB To PostgreSQL Conversion GuideVersion FUNCTION <function_name>(parameter.)