没有合适的资源?快使用搜索试试~ 我知道了~
首页DB2到GreenPlum/PostgreSQL的转换指南
DB2到GreenPlum/PostgreSQL的转换指南
5星 · 超过95%的资源 需积分: 41 74 下载量 32 浏览量
更新于2023-03-16
评论 6
收藏 116KB PDF 举报
详细描述了DB2与GreenPlum/PostgreSQL在各方面的差异,主要用于DB2数据库向GreenPlum/PostgreSQL数据库的迁移。
资源详情
资源评论
资源推荐
DB2 UDB To PostgreSQL Conversion Guide Version 1.0
DB2 UDB TO POSTGRESQL CONVERSION GUIDE
DB2 UDB To PostgreSQL Migration
DRAFT VERSION : 1.0
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
DB2 UDB To PostgreSQL Conversion Guide Version 1.0
2.16 Condition Handling.................................................................................................................41
2.17 Print Output Messages...........................................................................................................42
2.18 Implicit casting in SQL............................................................................................................42
2.18.1Casting double to integer syntax...................................................................................................42
2.18.2Casting double to integer (Round)................................................................................................42
2.18.3Casting double to integer (lower possible integer)........................................................................42
2.19 Select from SYSIBM.SYSDUMMY1.......................................................................................42
2.20 Variables declaration and assignment...................................................................................42
2.21 Conditional statements and flow control (supported by PostgreSQL)...................................42
3 SUMMARY....................................................................................................................44
DB2 UDB To PostgreSQL Conversion Guide Version 1.0
1. Introduction
Since migrating from DB2 UDB to PostgreSQL requires a certain level of knowledge in both
environments, the purpose of this document is to identify the issues in the process involved
migrating from DB2 UDB to PostgreSQL database.
This document also relates the required information on PostgreSQL equivalents of DB2 UDB and
its syntax of usage.
1.1 Purpose
The intent of this document is to serve as a valid reference - in the near future - for the process of
migrating the structure as well as data from IBM DB2 database to PostgreSQL database .
1.2 Scope
The scope of this document is limited to the extent of identifying the PostgreSQL equivalents of
various SQL components, column / OLAP / Scalar functions, Order by / Group by / Having, Joins,
Sub-queries, Union / Intersect / Except clauses that are currently defined for DB2 database.
DB2 UDB To PostgreSQL Conversion Guide Version 1.0
2 Conversion Reference
This section briefly discusses the different steps involved in conversion process from DB2 UDB to
PostgreSQL.
2.1 Tools
The following tools, could be used while migrating data from DB2 to PostgreSQL.
• Aqua Data Studio 4.5.2 and above – Mainly used for exporting DB2 data to csv format and
importing csv format into postgreSQL.
2.2 SQL Components - DB2 Objects
2.2.1 Data Types
Data Types
IBM DB2 PostgreSQL
CHAR(n) CHAR(n)
DATE DATE
Some Valid Inputs:
now, today, tomorrow, yesterday
‘now’::datetime
DECIMAL(m,n) DECIMAL(m,n)
INTEGER INTEGER
SMALLINT SMALLINT
TIMESTAMP TIMESTAMP
Some Valid Inputs:
now, today, tomorrow, yesterday
TIME TIME
Some Valid Inputs:
now
VARCHAR(n) VARCHAR(n)
2.2.2 Special Data Types
Special Data Types
IBM DB2 PostgreSQL
CLOB TEXT (maximum of 1GB)
剩余43页未读,继续阅读
toplancer
- 粉丝: 0
- 资源: 1
上传资源 快速赚钱
- 我的内容管理 收起
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
会员权益专享
最新资源
- c++校园超市商品信息管理系统课程设计说明书(含源代码) (2).pdf
- 建筑供配电系统相关课件.pptx
- 企业管理规章制度及管理模式.doc
- vb打开摄像头.doc
- 云计算-可信计算中认证协议改进方案.pdf
- [详细完整版]单片机编程4.ppt
- c语言常用算法.pdf
- c++经典程序代码大全.pdf
- 单片机数字时钟资料.doc
- 11项目管理前沿1.0.pptx
- 基于ssm的“魅力”繁峙宣传网站的设计与实现论文.doc
- 智慧交通综合解决方案.pptx
- 建筑防潮设计-PowerPointPresentati.pptx
- SPC统计过程控制程序.pptx
- SPC统计方法基础知识.pptx
- MW全能培训汽轮机调节保安系统PPT教学课件.pptx
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
评论2