Porting to DB2 Universal Database Version 8.1 from Sybase

Porting to DB2® Universal Database™ Version 8.1
from Sybase® Adaptive Server Enterprise™
By Amyris V. Rada
DB2 Information Management Software
IBM Toronto Lab
[email protected]
Reprinted courtesy of DB2 Developer Domain, at ibm.com/software/data/developer)
Trademarks
The following terms are trademarks or registered trademarks of the IBM Corporation in the United States and/or
other countries:
AIX
AS/400
DataJoiner
DataPropagator
DataRefresher
DB2
DB2 Connect
DB2 Universal Database
DB2 OLAP Server
Distributed Relational Database Architecture
DRDA
IBM
IMS
iSeries
Informix
MQSeries
MVS/ESA
Net.Data
NUMA-Q
OS/400
OS/390
OS/2
RS/6000
System/390
VM/ESA
VSE/ESA
z/OS
zSeries
Notes is a registered trademark of Lotus Development Corporation and/or IBM Corporation.
The following terms are trademarks or registered trademarks of the companies listed:
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other
countries or both.
Microsoft, Windows, Windows NT and Windows 2000 are trademarks or registered trademarks of Microsoft
Corporation in the United States, other countries or both.
Solaris is a trademark of Sun Microsystems, Inc.
Sybase, Adaptive Server, SQL Server, Transact-SQL, Open Client, and Open Server are trademarks of
Sybase, Inc. or its subsidiaries.
UNIX is a registered trademark of the Open Group in the United States and other countries.
Other trademarks and trade names mentioned herein are the property of their respective owners.
© 2003 International Business Machines Corporation. All rights reserved.
Table of Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Why Port to DB2 UDB? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Integrated Support for Native Environments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Integrated System Management Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Multiplatform tools for DB2 Universal Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Self-managing and Resource Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Data Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Integrated Web Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Web Services Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Integrated Support for Development Environments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Basic Warehousing Functionality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
High Availability Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
IBM Program for Assistance to Developers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Universal Database Product Family . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Personal Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Workgroup Server Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Enterprise Server Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Everyplace Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Run-Time Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Administration Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Application Development Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6
6
6
6
7
7
7
8
8
8
8
9
10
10
11
11
11
11
11
11
12
Data Types Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Administration Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Environments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Administration Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Enterprise-wide data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Database Devices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Database Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Dump/Load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Online Utility Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
System Stored Procedures and Administrative Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Database Consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
17
17
17
18
18
18
19
20
21
22
22
23
Implementation Differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Statement Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Locking Mechanisms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
System Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
IDENTITY Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Java-SQL Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Scrollable Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
ANSI Join Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
User Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
XML in the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
25
25
25
25
26
26
26
27
28
28
28
29
29
30
30
30
31
32
35
36
Global Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Programming Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Programming Interfaces Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Call Level Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
DB2 Java Enablement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Embedded SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Open Client and Open Server Libraries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Java Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
37
37
37
38
40
40
41
Terminology Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
DB2 CLI vs. ODBC Function Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Conversion Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
IBM Migration Toolkit (MTK) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
AllFusion ERwin Data Modeler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Data Junction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Embarcadero Technologies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SQL Conversion Workbench (SQL-CW) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
45
45
46
46
46
47
Resources and References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Introduction
Database management software is now the core of enterprise computing. Companies need access to a wide
range of information such as XML documents, streaming video, and other rich media types. New ways of
working bring new requirements, including digital rights management. The e-business evolution makes
continuous availability a necessity and is driving the convergence of transaction, business intelligence, and
content management applications as companies integrate their business operations. DB2 Universal Database
Version 8 can help your organization meet these challenges.
DB2 Universal Database for UNIX® and Windows® (DB2 UDB) can help improve the performance of database
applications. Many solution developers have already chosen DB2 UDB as their primary development database
environment, and have ported and continue to enable applications to support it in order to take advantage of its
unique features.
DB2 UDB is a true cross-platform database management system (DBMS), running on a wide variety of systems
including Windows 98, Windows NT®, Windows 2000, Solaris, HP-UX, AIX®, and Linux.
DB2 UDB responds quickly to peaks in transaction demand on your web site, expands to hold growing amounts
of information that can be distributed in a number of different databases, and grows with your information
infrastructure from one processor, to multiple processors, to massively parallel clusters. The integration of
partitioning and clustering technology into the new DB2 ® Universal Database Enterprise Server Edition means
that it is flexible enough to meet future growth.
A real database leader in several technologies, DB2 UDB provides the following capabilities:
w
Integrated support for complex data such as text documents, images, video and audio clips
w
Integrated Web access through native support for Java™, Java Database Connectivity (JDBC),
embedded SQL for Java (SQLJ) and Net.Data
w
Integrated system management tools
w
Data replication services.
This article introduces DB2 UDB products and their capabilities, discusses porting databases and applications,
and describes the most important aspects of porting applications from Sybase Adaptive Server Enterprise to DB2
UDB V8. It describes the differences between the two products in database options, data definition language
(DDL), data modeling, SQL considerations, data conversion, and application conversion.
All subsequent references to DB2 imply DB2 UDB for UNIX, Linux and Windows platforms, unless otherwise
specified. All the information contained in this document is based on publicly available information as of January
19, 2003, and is subject to change. IBM disclaims all warranties as to the accuracy, completeness, or adequacy
of such information. IBM shall have no liability for errors, omissions or inadequacies in the information contained
herein or for interpretations thereof.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
5
Why Port to DB2 UDB?
DB2 is a database leader in several technologies, and offers true multi-platform support and scalability. The
same database is able to handle mixed types of workloads on a single server. The DB2 design handles varying
workloads from high-volume online transaction processing (OLTP) to complex multi-user queries while
maintaining an excellent performance.
In addition to scalability and performance, DB2 offers the following advantages:
w
w
w
w
w
w
w
w
w
w
Integrated support for native environments
Integrated system management tools and multiplatform tools
Self-managing and resource tuning capability
Data replication service
Integrated Web access
Web services applications
Integrated support for development environments
Data warehousing functionality
High Availability support
IBM Program for Assistance to Developers
Each of these is described in detail below.
Integrated Support for Native Environments
DB2 conforms to many standards including the operating systems that it supports. It maps closely onto internal
resources for performance and scalability. All these considerations make it more reliable and integrate it to the
operating system.
Integrated System Management Tools
DB2 Version 8 introduced a number of new tools: the Health Monitor, the Health Center, the Replication Center,
and the Storage Management tool. In addition, DB2 V8 includes major improvements to existing tools including
the Configuration Assistant, the Control Center, and the Development Center. Here are some of the capabilities
provided by these tools:
w
The Health Monitor and the Health Center help to monitor the health of DB2 systems. They receive
alerts about potential system health issues and address those health issues before they become real
problems that can affect the system’s performance.
w
The Storage Management tool now available through the Control Center displays a snapshot of the
storage for a particular database, database partition group, or table space.
w
The Configuration Assistant has options to configure both local and remote servers, including DB2
Connect™ servers, or to create configuration templates.
w
The Control Center is a graphical interface that can be used to perform server administrative tasks such
as configuring, backing up and recovering data, managing directories, scheduling jobs, and managing
media, as well as accessing and manipulating databases. The Control Center is a Java application that
can be installed and can be used to administer databases on Windows 32-bit operating systems, Linux,
UNIX and OS/390®.
The Control Center includes access to other tools such as the Replication Center, the Satellite Administration
Center, the Data Warehouse Center (with the Data Warehousing option), the Command Center, the Task Center,
the Information Catalog Center, the Health Center, the Journal, the License Center, the Developer Center, and
the Information Center.
Multiplatform tools for DB2 Universal Database
IBM application tools are designed to enhance DB2 performance and management across AIX, HP-UX, Solaris
Operating Environment, Linux and Microsoft Windows operating systems.
w
DB2 Table Editor for Multiplatforms offers direct DB2 database access to anyone for creating, reviewing,
or updating data. It requires a Java-enabled browser .
w
DB2 Web Query Tool for Multiplatforms provides easy access to enterprise data using complex
functionality.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
6
w
DB2 Recovery Expert for Multiplatforms provides targeted and automated recovery of database assets,
even as systems remain on-line. Built-in self-managing and resource tuning (SMART) features provide
intelligent analysis of altered, corrupted, incorrect, or missing database assets – including tables,
indexes, or data – and automate the process of rebuilding those assets to a correct point in time, all
without disruption to normal database or business operations.
w
DB2 Performance Expert for Multiplatforms provides a comprehensive view that consolidates, reports
and analyzes DB2 performance-related information and recommends changes to improve performance.
DB2 Performance Expert can selectively employ and integrate the view from all trace, snapshot, event,
and DB2 Version 8 Health Monitor output. Plus, it provides on-line snapshot reports and a buffer pool
analyzer and reporting facility. It can selectively store performance data in its own performance data
warehouse which you can study at a later time, both at detailed and rolled-up levels of the data. And with
its starter set of SMART features, DB2 Performance Expert also provides recommendations for system
tuning to gain optimum throughput. It supports DB2 UDB Version 8.
w
DB2 High Performance Unload for Multiplatforms gives customers a fast and efficient tool for unloading
and extracting data for movement across enterprise systems, or for reorganization in-place. The product
delivers high levels of parallelism when either unloading or extracting in partitioned database
environments, both for DB2 Enterprise Server Edition Version 8, and DB2 Enterprise - Extended Edition
Version 7.
Self-managing and Resource Tuning
DB2 Version 8 also adds self-managing and resource tuning (SMART) database technology that lets database
administrators choose to configure, tune and manage their databases with enhanced automation. SMART
database management means administrators spend less time managing routine tasks and more time focusing on
tasks that help enterprises gain and maintain a sustainable competitive advantage.
Data Replication
DB2 includes a replication solution that ensures timely, reliable, and consistent data across an enterprise. DB2
Version 8 replication includes four components:
w
Replication Center
w
Capture program and triggers
w
Apply program
w
Replication Alert Monitor
The Replication Center creates the control tables that are required for replication and stores the initial
information about registered sources, subscription sets, and alert conditions in the control tables. The Capture
program, the Apply program, and the Capture triggers update the control tables to indicate the progress of
replication and to coordinate the processing of changes. The Replication Alert Monitor reads the control tables
that have been updated by the Capture program, Apply program, and the Capture triggers to understand the
problems and progress at a server.
The replication components run independently of each other, and they rely on information that they each store in
the replication control tables to communicate with each other.
Additional products complete the data replication solution by supporting sources and targets that include the DB2
family, IMS™, VSAM, Oracle, Sybase, Microsoft, Lotus Notes®, and others.
w
DB2 DataPropagator™ for OS/390, a feature of Version 6 of DB2 Universal Database Server for OS/390
w
DataPropagator Relational Version 5 Release 1 for AS/400
w
IBM DataPropagator NonRelational.
w
IBM DataJoiner®.
w
Lotus NotesPump.
Integrated Web Access
DB2 provides Web access to enterprise data on DB2 databases through native support for Java/JDBC,
Embedded SQL for Java (SQLJ), and Net.Data®.
JDBC can be used to create applications or applets that access data in DB2 databases. These applets can be run
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
7
inside HTML Web pages on any system with a Java-enabled browser, independent of the client’s platform. The
processing of JDBC applets is shared between the client and the server.
DB2 SQLJ support facilitates the creation, building, and running of SQLJ programs against DB2 UDB databases.
DB2 Net.Data enables application developers to create Internet applications that access data from DB2
databases, are stored on a Web server, and can be viewed from any Web browser. While viewing these
documents, users can either select automated queries or define new ones that retrieve the specified information
directly from a DB2 UDB database.
Web Services Applications
DB2 can be accessed as a Web service provider, and it is usually teamed with IBM® WebSphere® family
products to provide a complete Web services framework.
An easy way to develop data-intensive Web services applications is through DB2’s document access definition
extension (DADx) programming model. DADx files are simply XML documents that contain stored procedure
references, DB2 XML Extender references, or traditional SQL. Best of all, DADx files can be created quickly
without any Java programming. For details see the DB2 UDB Application Development Guide.
For a demonstration of DB2 as a Web service provider, see the IBM Video Central for e-business tutorial at
www.ibm.com/software/data/developer/samples/video/.
Integrated Support for Development Environments
DB2 provides an Application Development Client (ADC) that contains a collection of tools specifically designed
for database application developers. The ADC includes libraries, header files, documented Application
Programming Interfaces (APIs), and sample programs to build database applications.
In DB2 Version 8, the Development Center replaces the Stored Procedure Builder (SPB). A single development
environment that supports the entire DB2 family ranging from the workstation to z/OS™, it provides more
functions and features than the Stored Procedure Builder:
w
An easy-to-use interface for developing routines such as stored procedures and user-defined functions
(UDFs)
w
A set of wizards makes it easy to perform development tasks
w
DB2 development add-ins for Microsoft Visual C++, Microsoft Visual Basic and Microsoft Visual InterDev
in order to provide easy access to the Development Center features
Basic Warehousing Functionality
DB2 UDB offers the Data Warehouse Center, a component that automates data warehouse processing. The
Data Warehouse Center can be used to register and access data sources, define data extraction and
transformation steps, populate data warehouses, automate and monitor warehouse management processes, and
manage and interchange metadata.
The DB2 Warehouse Manager product completes the warehousing capability provided by the Data Warehouse
Center.
High Availability Support
In order to enhance the availability of data, DB2 UDB offers several features such as suspended I/O, split mirror
image, dual logging, parallel recovery, backup from split image, and incremental and delta backups. Suspended
I/O and the db2inidb utility provide the ability to split a mirrored copy of data and make that mirrored copy
available for processing or available to another server. A new database configuration parameter, mirrorlogpath,
allows dual logging. During database recovery, multiple agents take advantage of the extra CPUs available on
SMP machines for better performance. Using backups from a read-only database split mirror and using
incremental/delta options provide faster, enhanced backup and recovery.
Additional high availability features are available in DB2 Version 8:
w
Online table load - Users have full read and write access to all the tables in the table space, except for
the table being loaded. If the load is appending data to the table, the existing data will be available for
read access.
w
Online table reorganization - Applications have access to the table during the reorganization. This
process can be paused and resumed later by anyone with the appropriate authority.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
8
w
Online index reorganization - Users can read and update a table and its existing indexes during an
index reorganization using the new REORG INDEXES command.
w
Configurable online configuration parameters - Over 50 configuration parameters can now be set
online, and these changes take effect immediately.
w
Online buffer pool creation, deletion, and resizing - Users can change buffer pool allocations and
alter database and database manager configuration parameters that affect memory use while DB2 is
running.
w
DMS container operations - Users are allowed to drop a container from a table space, reduce the size
of existing containers, and add new containers to a table space such that a rebalance does not occur.
Fail over support can also be provided through platform-specific software. DB2 provides support for High
Availability Cluster Multiprocessing Enhanced Scalability (HACMP/ES) on AIX, Microsoft Cluster Server on
Windows operating systems, Sun Cluster or VERITAS Cluster Server on the Solaris Operating Environment, and
Multi-Computer/ServiceGuard on Hewlett-Packard. This support consists of configuration instructions and details
for implementation when working with DB2 servers.
IBM Program for Assistance to Developers
PartnerWorld for Developers is an IBM program that provides business, technical, and marketing services to
partners in order to help them in developing and marketing applications. The strategic focuses of this program
are network computing and e-business.
There are three levels of membership: Member, Advanced, and Premier, with specialized program offerings for
each level of membership. More details about membership levels are on the Membership Track Guide that can
be found at www.developer.ibm.com/welcome/guide2003/membership.html.
Benefits offered by this program include the following:
w
Marketing and sales support
Marketing education, Business Partner opportunities, co-marketing promotion, and other opportunities
w
Education and certification
Online education, technical education discounts, technical workshops, business seminars and developer
workshops, professional certification opportunities, interactive e-learning, and calendar of education
events
w
Technical support
Access to cross-platform technical enablement services at the worldwide Solution Partnership Centers,
and industry-leading technical support for developers
w
Incentives
Software discounts, hardware discounts and leases, and discounts on business services such as express
mail, pagers, insurance and car rentals
w
Financing
Flexible financing to help you and your customers acquire hardware, software, and services with
affordable monthly payments
w
Relationship management and membership communications
Electronic access to timely, consistent information and tools based on interests you define, plus
additional relationship management services including telecoverage and/or face-to-face support, based
on your level of membership.
PartnerWorld for Developers Web site, www.developer.ibm.com, is a dynamic, 24-hour, 7-day-a-week service
that provides information about all PartnerWorld program services.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
9
DB2 Universal Database Product Family
The DB2 product family scales through a variety of platforms: AS/400® systems, RS/6000® hardware, IBM
zSeries™ systems, Intel systems, and non-IBM machines such as Hewlett-Packard and Sun Microsystems.
DB2 UDB Version 8 database software servers run on the following software environments: AIX, HP–UX 11i,
Linux, Solaris Operating Environment, Windows NT Version 4, Windows 2000, Windows XP, and Windows .NET
Editions are available for both 32-bit and 64-bit operating environment modes.
There are three types of DB2 clients: Run-Time Client, Administration Client and Application Development
Client. DB2 clients can connect to DB2 servers two releases later or one release earlier than the client’s release
level, as well as to servers at the same release level. This means that a DB2 Version 6 client can connect to DB2
servers at versions 5, 6, 7, and 8.
DB2 Version 8 Clients are available for the following platforms: AIX, HP-UX, Linux, Solaris, Windows NT Version
4, Windows 98, Windows 2000, Windows ME, Windows XP (32-bit and 64-bit editions), and Windows .NET
(32-bit and 64-bit editions). Support on other platforms may be available through earlier versions of DB2 Clients.
In addition to clients, Web access is provided with popular browsers and Java applications using DB2's native
Java/JDBC support and Net.Data.
Figure 1. DB2 UDB Version 8.1 Product Family
The DB2 Server and Clients products and components include:
w
DB2 Personal Edition
w
DB2 Workgroup Server Edition
w
DB2 Enterprise Server Edition
w
DB2 Everyplace™ Edition
w
DB2 Run-Time Client
w
DB2 Administration Client
w
DB2 Application Development Client
DB2 Personal Edition
DB2 Personal Edition is a single-user version of DB2. It can be used to create and manage local databases, or
as a client to DB2 Workgroup Server Edition or Enterprise Server Edition database servers.
DB2 Personal Edition can also act as a satellite, remotely administered from a DB2 Enterprise Server Edition
database server. For more information about satellite functionality, refer to the product satellite administration
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
10
documentation.
DB2 Personal Edition runs on Windows ME, Windows NT Version 4, Windows 2000 (32–bit), Windows XP
(32–bit or 64–bit), and Windows .NET (32–bit or 64–bit).
DB2 Workgroup Server Edition
This product is a multi-user version of DB2. It is designed for use in a Local Area Network (LAN) environment
and provides support for both local and remote DB2 clients. DB2 Workgroup Server Edition also includes data
warehouse capabilities and can be administered remotely from a satellite control database.
DB2 Workgroup Server Edition runs on AIX, HP–UX 11i, Linux, Solaris Operating Environment, Windows NT
Version 4, Windows 2000, Windows XP, and Windows .NET only on 32–bit mode.
DB2 Enterprise Server Edition
This product is a multi-user version of DB2 that allows you create and manage non-partitioned or partitioned
database environments. Partitioned database systems can manage high volumes of data and provide benefits
such as increased performance, high availability, and fail over support.
DB2 Enterprise Server Edition (ESE) provides support for both local and remote DB2 clients. It also includes
DB2 Connect functionality for accessing data stored on midrange and mainframe database systems such as DB2
for iSeries™ or DB2 for z/OS and OS/390.
Satellite administration capabilities allows DB2 ESE to remotely administer DB2 Personal Edition and DB2
Workgroup Server Edition database servers that are configured as satellites.
Another feature worth mentioning is the inclusion of a data warehouse server and related components.
DB2 Enterprise Server Edition runs on AIX, HP–UX 11i, Linux, Solaris Operating Environment, Windows NT
Version 4, Windows 2000, and Windows .NET. It is available on 32–bit and 64–bit mode for all operating
environments except on specific versions of Solaris.
DB2 Everyplace
DB2 Everyplace is a relational database and enterprise synchronization system for mobile and embedded
devices. DB2 Everyplace enables enterprise application functionality and enterprise data to be extended to
mobile devices such as personal digital assistants (PDAs), handheld personal computers (HPCs), and smart
phones. DB2 Everyplace runs on AIX, Linux, Palm OS, QNX Neutrino, Solaris, Symbian, EPOC, Windows NT,
Windows 95, Windows 98, Windows 2000, and Windows CE. It also supports the Java platform.
For data synchronization, DB2 Everyplace Sync Server works with the DB2 database to synchronize mobile data
and applications to and from backend data sources. The platforms supported are AIX, Linux, Windows NT,
Windows 2000, and Windows XP. DB2 Everyplace Sync Server supports DB2 for iSeries, DB2 UDB for OS/390,
Informix®, DB2 UDB for UNIX and Windows, Oracle, Microsoft SQL Server, and Sybase data sources.
DB2 Run-Time Client
The Run-Time Client is a lightweight client that provides the functionality required for an application to access
DB2 UDB and DB2 Connect servers. Functionality includes communication protocol support and support for
application interfaces such as JDBC, SQLJ, Open Database Connectivity (ODBC), CLI, and OLE DB. Most of the
previous Run-Time Client GUI facilities have been removed from Run-Time Client Version 8, therefore disk
requirements have been reduced considerably.
DB2 Run-Time Clients are available for the following platforms: AIX, HP-UX, Linux, the Solaris Operating
Environment, and Windows operating systems.
DB2 Administration Client
From workstations on a variety of platforms, the Administration Client provides the ability to access and
administer DB2 databases. The DB2 Administration Client has all the features of the DB2 Run-Time Client and
also includes all the DB2 administration tools and support for thin clients.
DB2 Administration Clients are available for the following platforms: AIX, HP-UX, Linux, the Solaris Operating
Environment, and Windows operating systems.
DB2 Application Development Client
The DB2 Application Development Client (ADC) is a collection of graphical and non-graphical tools and
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
11
components for developing character-based, multimedia, and object-oriented applications. Special features
include the Development Center and sample applications for all supported programming languages. The ADC
also includes the tools and components provided as part of the DB2 Administration Client product. See the
Application Building Guide: Building and Running Applications for details on how to set up a programming
environment.
DB2 Application Development clients are available for the following platforms: AIX, HP-UX, Linux, the Solaris
Operating Environment, and Windows operating systems.
The ADCs for the supported platforms include the following:
w
Precompilers for C/C++, COBOL, and Fortran, providing the language is supported for that platform.
w
Embedded SQL application support, including programming libraries, include files and code samples.
w
DB2 Call Level Interface (DB2 CLI) application support, including programming libraries, include
files, and code samples to develop applications which are easily ported to ODBC and compiled with an
ODBC SDK. An ODBC SDK is available from Microsoft for Windows operating systems, and from
various other vendors for many of the other supported platforms. For Windows operating systems, DB2
clients contain an ODBC driver that supports applications developed with the Microsoft ODBC Software
Developer’s Kit. For all other platforms, DB2 clients contain an optionally installed ODBC driver that
supports applications that can be developed with an ODBC SDK for that platform, if one exists. Only
DB2 Clients for Windows operating systems contain an ODBC driver manager.
w
DB2 Java Enablement, which includes DB2 Java Database Connectivity (DB2 JDBC) support to
develop Java applications and applets, and DB2 embedded SQL for Java (DB2 SQLJ) support to
develop Java embedded SQL applications and applets.
w
Java Development Kit (JDK). JDK 1.3.1 and Java Runtime Environment (JRE) 1.3.1 from IBM for AIX,
IBM Developer Kit and Runtime Environment 1.3.1 for Linux and for Windows, HP-UX Software
Developer’s Kit and Runtime Environment 1.3.1 for HP-UX, and the Java Development Kit 1.3.1 for
Solaris from Sun Microsystems. Except for Solaris, the respective JDK for each operating system will be
installed if any components are selected for install that require Java to run. If none are selected, the JDK
can still be selected to be installed. On Solaris, JDK 1.3.1, which is shipped with DB2, must be installed
by the user.
w
ActiveX Data Objects (ADO) and Object Linking and Embedding (OLE) Automation Routines
(UDFs and Stored Procedures) on Windows operating systems, including code samples implemented
in Microsoft Visual Basic and Microsoft Visual C++. Also, code samples with Remote Data Objects
(RDO) implemented in Microsoft Visual Basic.
w
Object Linking and Embedding Database (OLE DB) table functions on Windows operating systems.
w
DB2 Development Center, a graphical application that supports the rapid development of routines
(stored procedures and user-defined functions), and structured types. It can be launched as a
stand-alone application or from a DB2 Universal Database center, such as the Control Center, the
Command Center, or the Task Center. The Development Center is implemented with Java, and all
database connections are managed by using a JDBC API. The Development Center also provides a DB2
Development Add-In for each of the following development environments: Microsoft Visual C++, Version
6, Microsoft Visual Basic, Version 6 and Microsoft Visual InterDev, Version 6.
w
Interactive SQL through the Command Center or Command Line Processor (CLP) to prototype SQL
statements or to perform ad hoc queries against the database.
w
A set of documented APIs to enable other application development tools to implement precompiler
support for DB2 directly within their products. For example, IBM COBOL on AIX uses this interface.
Information on the set of Precompiler Services APIs is available from the PDF file, prepapi.pdf, at the
anonymous FTP site: ftp://ftp.software.ibm.com/ps/products/db2/info/.
w
An SQL92 and MVS Conformance Flagger, which identifies embedded SQL statements in applications
that do not conform to the ISO/ANSI SQL92 Entry Level standard, or which are not supported by DB2
UDB for z/OS and OS/390. If you migrate applications developed on a workstation to another platform,
the Flagger saves you time by showing syntax incompatibilities.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
12
DB2 Products
Other important DB2 products are:
w
DB2 Connect Enterprise Edition, a connectivity server that concentrates and manages connections
from multiple desktop clients and web applications to DB2 database servers running on host (OS/390
and z/OS, and DB2 for VSE & VM) or iSeries systems. DB2 Connect Enterprise Edition enables local
and remote client applications to create, update, control, and manage DB2 databases and host systems
using Structured Query Language (SQL), DB2 APIs, ODBC, JDBC, SQLJ, or DB2 CLI. In addition, DB2
Connect supports Microsoft Windows data interfaces such as ADO, RDO, and OLE. This product is
currently available for AIX, HP-UX, Linux, Solaris, and Windows operating systems. These servers
provide support for applications running on AIX, HP-UX, Linux, Solaris operating environment, and
Windows operating systems.
w
DB2 Connect Personal Edition, which provides access from a single workstation to DB2 databases
residing on servers such as OS/390, z/OS, OS/400, VM and VSE, as well as to DB2 Universal Database
servers on UNIX and Windows operating systems. DB2 Connect Personal Edition provides the same rich
set of APIs as DB2 Connect Enterprise Edition. This product is currently available for Linux and Windows
operating systems.
w
DB2 Relational Connect, which is used in a federated system to query and retrieve data located on
other database management systems (DBMSs), such as Oracle, Sybase, and Microsoft SQL Server.
SQL statements can refer to multiple DBMSs or to individual databases in a single statement. For
example, you can join data located in a DB2 UDB table, an Oracle table, and a Sybase view. In DB2
UDB Version 7.1, DB2 Relational Connect was available for Oracle on the Windows NT and AIX
platforms. In DB2 UDB Version 7.2, the Oracle support has been enhanced to include additional
operating systems, and support for Sybase and Microsoft SQL Server data sources has been added.
Supported DBMSs include Oracle, Sybase, Microsoft SQL Server and members of the DB2 Universal
Database family (such as DB2 for OS/390, DB2 for OS/400, and DB2 for Windows). DB2 Relational
Connect is an optional product that can be used with DB2 Universal Database Enterprise Edition, DB2
Universal Database Enterprise Extended Edition, DB2 Connect Enterprise Edition, and DB2 Connect
Unlimited Edition. The operating systems supported are OS/2, Windows NT, Windows 2000, AIX, and
Solaris operating environment.
w
DB2 XML Extender, which provides the ability to store and access XML documents, to generate XML
documents from existing relational data, and to insert rows into relational tables from XML documents.
XML Extender provides new data types, functions, and stored procedures to manage your XML data in
DB2 . This product is available for the following operating systems: OS/390, z/OS, iSeries, AIX, Sun
Solaris, Linux, Windows NT, and Windows 2000.
w
DB2 Net Search Extender, which combines the performance of DB2 Net Search Extender v7.2 with the
search interface and functionality of DB2 Text Information Extender. DB2 Net Search Extender v8.1
now offers users and application programmers a fast, versatile, and intelligent method for searching
full-text documents. It includes word, phrase, fuzzy, and wildcard searches and works seamlessly with
text data contained in DB2 UDB. It is designed to rapidly search and index data without locking database
tables, and it is able to handle the heavy text search demands of large Web sites. This product is
available for AIX, Solaris operating environment, Windows NT, Windows 2000, and Windows XP
operating systems.
w
DB2 OLAP Server™ and its add-on features, such as DB2 OLAP Integration Server and DB2 OLAP
Server Analyzer, which allow you to build online analytical processing (OLAP) applications that are
production-ready and Web-ready. These products are sold separately. DB2 OLAP Server for Version 8
includes DB2 OLAP Server Miner, a no-cost add-on. This feature automatically mines large volumes of
OLAP data. Hybrid analysis is a new function of DB2 OLAP Integration Server that you can use to
access more data without enlarging your OLAP database. It builds a virtual extension from an OLAP
database to the relational database that contains the lowest members of your OLAP hierarchies. The
DB2 OLAP Sever and Integration Server are available on the following operating systems: : AIX, HP-UX,
the Solaris operating environment, Windows NT 4.0, Windows 2000, and Windows XP.
w
DB2 Warehouse Manager, a package that includes components to enhance and complete the
warehousing capability provided by the Data Warehouse Center, a part of DB2. These components are
Data Warehouse tools (Warehouse transformers, Warehouse agent, Classic Connect drivers) and
Information Catalog Manager tools (Information Catalog Center, Information Catalog Center for the Web,
Information Catalog Manager Samples, Manage Information Catalog Wizard). Warehouse Manager
servers are offered for AIX and Windows operating systems. Additional support is provided by
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
13
warehouse agents on the following platforms: OS/390, z/390, OS/400, AIX, Solaris, Linux, and Windows.
For version 8 of DB2 Warehouse Manager the following capabilities have been enhanced: Common
warehouse metamodel (CWM) XML support, Data Warehouse Center column mapping, Data
Warehouse Center cascading processes, multiple wait support, SQL select and update step, and the
Information Catalog Manager.
w
Multiplatform tools for DB2 Universal Database are application tools designed to enhance DB2
Universal Database across the AIX, HP-UX, Solaris Operating Environment, Linux, and Windows,
platforms with the introduction of DB2 Web Query Tool for Multiplatforms, DB2 Table Editor for
Multiplatforms, DB2 Recovery Expert for Multiplatforms, DB2 Performance Expert for Multiplatforms,
and DB2 High Performance Unload for Multiplatforms.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
14
Data Types Conversion
Most of the Sybase data types can be mapped to DB2 UDB. The following table compares all data types:
Sybase Data Type
Name
tinyint
smallint
int
integer
Range of Values
0 to 255
-32768 to 32,767
-231 ( -2,147,483,648) to 231
-1 (2,147483,647)
Bytes of
Storage
1
2
4
DB2 UDB Data
Type Name
smallint
smallint
integer
int
bigint
Range of Values
-32768 to 32767
-32768 to 32767
-231 to 231 - 1
9223372036854775808 to
+9223372036854775807
-1031+1 to 1031-1
( p+s <=31)
Bytes of
Storage
2
2
4
8
numeric(p,s)
dec[imal](p,s)
float(p)
-1038 to 1038 -1
2-17
h/w dependent
4 or 8 1
real
h/w dependent
4
real
double precision
h/w dependent
8
double precision
smallmoney
-214,748.3648 to
214,748.3647
-922,337,203,685,477.5808
to
922,337,203,685,477.5807
January 1, 1900 to June 6,
2079
January 1, 1753 to
December 31, 9999
January 1, 1753 to
December 31, 9999
4
numeric(10,4)
6
8
numeric(19,4)
11
42
timestamp 7
83
timestamp
8
timestamp
money
smalldatetime
datetime
timestamp4
num[eric](p,s)
dec[imal](p,s)
float(p)
date8
time9
char[acter](n)
varchar(n)
char[acter] varying
0 to page size 5
0 to page size 5
n
entry length
char[acter](n)
varchar(n)
character varying
long varchar(n)11
nchar(n)
0 to page size 5
n * char size
nvarchar(n)
nchar varying
0 to page size 5
entry length *
char size 6
unichar
univarchar
binary(n)
0 to page size 5
0 to page size 5
0 to 255
varbinary(n)
0 to 255
bit
0 or 1
text
0 to 231 - 1 (2,147,483,647)
image
0 to 231 - 1 (2,147,483,647)
6
n
entry length
1
multiples of
a page size
multiples of a
page size
(p/2) + 1
4 or 8
0 or from -3.402E+38 to
-1.175E-37, or from
1.175E-37 to 3.402E+38
0 or from -1.79769E+308 to
-2.225E-307, or from
2.225E-307 to 1.79769E+308
4
( 8 digits)
8
(16 digits)
January 1, 0001 to December 10 internal, 26
31, 9999
external
January 1, 0001 to December 10 internal, 26
31, 9999
external
January 1, 0001 to December 10 internal, 26
31, 9999
external
year: 0001 to 9999,
4 internal,
month: 1 to 12,
10 external
day: 1 to 31
hour: 0 to 24.
3 internal,
minutes/seconds: 0 to 59
8 external
1 to 254
n
1 to 32,672 10
entry length
1 to 32,700
graphic(n)
1 to 127
vargraphic(n)
1 to 16,336 10
entry length
2*n
2 * entry length
long vargraphic(n)11 1 to 16,350
2 * entry length
DBCLOB(n)
graphic(n)
vargraphic(n)
character(n) FOR
BIT DATA12
varchar(n) FOR
BIT DATA
character(1) FOR
BITDATA
CLOB(n)
1 to 1 073 741 823
1 to 127
1 to 2000
1 to 254
2*entry length
2*n
2*entry length
n
BLOB(n)
1 to 4000
entry length
0 or 1
1
1 to 231
entry length
1 to 231
entry length
Note: Some DB2 UDB data types that are not available in Sybase have been included in the table.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
15
1
float storage is 4 bytes if precision < 16 or 8 bytes if precision is >= 16.
2
datetime values are accurate to 1/300 of a second on platforms that support this level of granularity. Storage size is 8
bytes: 4 bytes for the number of days since the base date of January 1, 1900, and 4 bytes for the time of day.
3
smalldatetime values are accurate to the minute. Storage size is 4 bytes: 2 bytes for the number of days since January
1, 1900, and 2 bytes for the number of minutes since midnight.
4
timestamp is a user-defined data type that is defined as varbinary(8) NULL. It is used in tables that are to be browsed in
Client-Library applications (see "Browse Mode" for more information). Sybase Adaptive Server automatically updates
the timestamp column each time its row is modified. A table can have only one column of timestamp data type.
5
Maximum column length depends on page size and locking scheme. A page size can be 2K, 4K, 8K or 16K. The
maximum length for a fixed length column is 16,296 for all pages locking and 16,294 for data only locking. The
maximum length for a variable length column is 16,228 for all pages locking and 16,229 for data only locking.
6
This data type allows national character set specifications, where 1 character uses > 1 byte
7
DB2 timestamp format is YYYY-MM-DD-HH-MM-SS-NNNNNN (year-month-day-hour-minutes-seconds-microseconds).
8
date is a three-part value, YYYY-MM-DD (year, month, and day), where year ranges from 0001 to 9999, month ranges
from 01 to 12, and day ranges from 01 to 31.
9
time is a three-part value, HH:MM:SS (hour, minute, and second), designating a time of day under a 24-hour clock.
10
The maximum length for varying-length data types depend on the table space page size, the value provided
corresponds to a table on a table space with page size of 32K. The default page size for a table space is 4K, in which
case the maximum length for varchar is 4000 and for vargraphic is 2000.
11
Special restrictions apply to an expression resulting in a varying-length string data type whose maximum length is
greater than 254 bytes. Such expressions are not permitted in a SELECT DISTINCT statement's SELECT list, a GROUP
BY clause, an ORDER BY clause, a column function with DISTINCT, and a subselect of a set operator other than
UNION ALL.
12
FOR BIT DATA specifies that the contents of the column are to be treated as bit (binary) data. During data transfer with
other systems, code page conversions are not performed. Comparisons are done in binary, irrespective of the database
collating sequence. The smallint datatype can also be used to map this type as long as a check constraint is declared to
restrict the values to 0 and 1.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
16
Administration Issues
This section compares the administrative features available in Sybase Adaptive Server Enterprise Version 12.5
(Adaptive Server) and DB2 UDB Version 8.1 for UNIX, Linux and Windows.
Environments
In Sybase, several Adaptive servers can be created to define different environments. Sybase has a separate
server process for backup.
DB2 UDB uses instances to provide separate environments within the same machine. Other instances can also
be used to restrict access to sensitive information or to limit the impact of instance unavailability. However,
multiple instances require additional system resources (memory and disk space) and more administration. The
resource settings for each instance, including memory, are user-defined and stored in individual configuration
files. The DB2 registry variable DB2INSTANCE indicates the default instance. The command attach enables
applications to specify an instance which may be the current instance, another instance on the same workstation,
or an instance on a remote workstation. The following figure illustrates the relationships among database objects
within instances:
Figure 2. DB2 Database objects
Administration Server
Adaptive Server includes an Open Server program called Backup Server that performs dumps and loads
commands. The Backup Server runs on the same machine as Adaptive Server.
DB2 Administration Server (DAS) is a separate server process that supports TCP/IP communications. The DAS
is used to assist with tasks on DB2 servers (including backups). The DAS assists the Control Center,
Development Center, Replication Center, and Configuration Assistant when working on the following
administration tasks:
w
Enabling remote administration of DB2 servers.
w
Providing the facility for job management, including the ability to schedule.
w
Running of both DB2 and operating system user-defined command scripts.
w
Defining the scheduling of jobs, viewing the results of completed jobs, and performing other
administrative tasks against jobs located either remotely or locally to the DAS using the Task Center.
w
On Windows and UNIX platforms, providing a means for discovering information about the configuration
of DB2 instances, databases, and other DB2 administration servers in conjunction with the DB2
Discovery utility. This information is used by the Configuration Assistant and the Control Center to
simplify and automate the configuration of client connections to DB2 databases.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
17
With DB2 Version 8, a DB2 Administration Server is provided for all DB2 platforms. This includes zSeries
(OS/390 and z/OS only) and iSeries, as well as all supported Windows and UNIX platforms.
Enterprise-wide data
Adaptive Server Component Integration Services allows users to access both Sybase and non-Sybase databases
on different servers. These external data sources include Adaptive Server, Oracle, and DB2.
A DB2 federated system is a special type of distributed database management system (DBMS). A federated
system consists of a DB2 instance that operates as a federated server, a database that acts as the federated
database (one that allows access to one or more data sources), and clients (users and applications) that access
the database and data sources. A federated system supports distributed requests to multiple data sources within
a single SQL statement. For example, you can join data that is located in a DB2 table, an Oracle table, and a
Sybase view in a single SQL statement.
Data sources include relational databases and other types such as Microsoft Excel, BLAST, Table-structured
files, Documentum (EDMS 98), and XML tagged files (1.0 specification). Supported DBMSs include DB2 for
UNIX and Windows, members of the DB2 family (such as Informix, DB2 for OS/390, DB2 for VM and VSE, and
DB2 for iSeries), Sybase, Oracle, and Microsoft SQL Server.
Here is a brief summary of the enhancements to federated support in DB2 Version 8:
w
w
w
Support on additional operating systems: DB2 for Linux, DB2 for HP-UX, and Windows® 2000
Write capability to perform INSERT, UPDATE, and DELETE actions on the all data sources
Ability to create remote tables on relational data sources.
Database Devices
Sybase uses database devices to store data for a database. In UNIX, raw partitions were required as database
devices to ensure data integrity. Adaptive Server version 12 introduces a flag dsync to indicate whether or not a
database device is buffered. This allows you to create database devices as files under UNIX file systems.
DB2 UDB stores data in table spaces. A table space can be either a system managed space (SMS) or a
database managed space (DMS). For an SMS table space, each container is a directory within the operating
system, and the operating system's file manager controls the allocation of storage space. For a DMS table
space, each container is either a fixed-size preallocated file or a physical device such as a disk, and DB2 UDB
controls the storage space. A container is a physical storage device (directory, file, or raw device).
Figure 3. DB2 UDB table spaces
DB2 UDB has three table space types: regular, temporary, and large. Regular table spaces are used for tables,
indexes, and system catalog tables. Temporary table spaces are used during SQL operations that require disk
space, such as sorting or reorganizing tables, creating indexes, and joining tables. Large table spaces are used
to store Large Object Data (LOB). A single table space may consist of several containers. A database can use
different table spaces for indexes, tables, and LOBs. The create table command is used to associate a table to a
table space. It is recommended that users allocate one container for each physical disk to enable I/O parallelism.
Log Files
Sybase log data is recorded on the database device or on a separate log device. The dump transaction
command allows the user to copy the transaction log and remove the inactive portion. The disk mirror command
creates a data device mirror, thus allowing the user to mirror the transaction log by mirroring the database device
or log device where the log data is stored.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
18
DB2 UDB databases have log files associated with them; table spaces are not used for log data. These logs
record all database changes. The number of log files is controlled by two configuration parameters: logprimary
and logsecond. Active logs are used during crash recovery to prevent a failure (such as a system power failure or
an application error) from leaving a database in an inconsistent state. After a failure, the following actions are
taken to ensure the integrity of the database: changes that are already made but are uncommitted are removed
from the database (rolled back), and all committed units of work, which may not have been physically written to
disk, are redone. The size of all the active logs must be less than 256 GB if infinite active logging is not used.
Circular and archive logging are the two methods available in DB2 UDB, and can be specified by database
configuration parameters. Circular logging is the default. It uses a number of online logs for crash recovery in a
circular fashion. The logs are used and retained only to the point of ensuring the integrity of current transactions.
Only full database off-line backups are valid for recovery, and roll forward from the last backup is not allowed.
Archive logging enables forward recovery using active and archived logs to any point in time before the failure.
When changes in the active log are no longer needed for normal processing, the log is closed, and it becomes an
archived log. A database can be rolled forward up to the last completed transaction, after a full database backup
has been restored. Once archive logging is enabled, online database backups can be performed. A user exit
program can be specified to manage archived logs.
The ARCHIVE LOG command closes and truncates the active log file for a recoverable database. This
command can be used to collect a complete set of log files up to a known point. After an online backup is
complete, DB2 UDB forces the currently active log to be closed and as a result it will be archived off. This
ensures that your online backup has a complete set of archived logs available for recovery.
In Version 8, dual logging is provided on all platforms supported by DB2 UDB. This feature is controlled by the
database configuration parameter mirrorlogpath. When a value is present in mirrorlogpath, dual logging is
enabled and this value specifies the secondary log path.
Infinite active logging is new in Version 8. Its allows an active unit of work to span the primary logs and archive
logs, effectively allowing a transaction to use an infinite number of log files. Without infinite active log enabled,
the log records for a unit of work must fit in the primary log space. Infinite active log is enabled by setting
logsecond to -1 if archive logging is enabled. Infinite active log can be used to support environments with large
jobs that require more log space than the one allocated for the primary logs.
The block on log disk full option allows the DB2 server to continue functioning when applications are running and
all the log files from the active log path are full. It can be set using the database configuration parameter
blk_log_dsk_ful in Version 8. When you enable this option, DB2 will retry every five minutes allowing you to
resolve the full disk situation and allowing the applications to complete their execution.
Database Security
Sybase authentication is controlled by users, logins, and groups, which are objects defined in the database. In
Adaptive Server, the unified login required and use security services parameters can enable user authentication
by a third party security service such as Windows NT LAN Manager, DCE, and Kerberos (CyberSAFE) on UNIX.
DB2 UDB authentication of a user is completed using an external security facility such as the native operating
system security, distributed computing environment (DCE), or Kerberos (only on Windows 2000, Windows XP,
and Windows .NET operating systems). A user must have a valid login system name in order to gain access to a
database. The authentication type for each instance determines where it takes place (server or client). The
authentication type is stored in the database manager configuration file at the server.
Sybase authorization is defined by means of roles and privileges. There are three roles: SA, SSO, and OPER.
There is also a special user for each database called the database owner (dbo).
DB2 UDB authorization is defined by means of authorities and privileges. Authority levels provide a method of
grouping privileges and control over higher-level database manager maintenance and utility operations.
Privileges enable users to create or access database resources. Together, these act to control access to the
database manager and its database objects. Users can access only those objects for which they have the
appropriate authorization, that is, the required privilege or authority. The hierarchy between authorities and
privileges is shown in the following figure:
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
19
Figure 4. DB2 Authorities and Privileges
In DB2, the following authorities exist:
w
w
w
w
w
System administration (SYSADM)
System control (SYSCTRL)
System maintenance (SYSMAINT)
Database administration (DBADM)
LOAD
SYSADM, SYSCTRL, and SYSMAINT can have a group name assigned that is managed by the operating
system facility. These settings are stored in the database manager configuration file. When DBADM is granted,
all of the following privileges are also granted: CONNECT, CREATETAB, BINDADD, and IMPLICIT_SCHEMA.
The LOAD authority allows users who are not SYSADM or DBADM to load data into a table. Sybase roles can be
mapped to DB2 authorities or groups to which certain database authorities have been granted.
Sybase uses permissions to restrict access to objects and commands based on a user's identity or group
membership. The commands grant and revoke are used to give or deny users permission to create databases, to
create objects within a database, and to access specified tables, views, and columns.
In DB2, privileges are stored in the database catalogs for a given database. The following types of object
privileges exist: database, schema, table space, table, view, nickname, server, package, index, routine and
sequence. The table space USE privilege controls which table spaces can be used to create tables. The
sequence USAGE privilege is automatically assigned to the sequence creator. The statements grant and revoke
can be executed to assign or to remove privileges to a given user. Most Sybase permissions can be mapped to
DB2 privileges. Some privileges in DB2 UDB are not available in Sybase, such as package and schema
privileges.
Sybase has a special system administrator user (SA) who is responsible for administrative tasks to set up and
maintain the server. In DB2, the System Administration (SYSADM) authority is the highest level of authority
within the database manager, and controls all database objects. This parameter defines the group name with
SYSADM authority for the database. In UNIX, the initial value is null and defaults to the primary group of the
instance owner. In Windows NT, the value defaults to the Administrator Group. Following installation, a different
group name can be assigned to SYSADM within DB2 UDB.
Dump/Load
The dump and load commands on Sybase are limited to databases and transaction logs. All dump operations
must specify a dump device, which can be a database logical device or an operating system filename. When a
database dump is loaded, the database device allocations need to match the usage allocations in the dumped
database. The dump command can be executed while the database is active; it does not reflect any data
changes made to the database after the command begins. It supports point in time recovery using the until_time
option in the load transaction command; however, it cannot be used for a database in which the data and logs
are on the same device or whose log has been truncated since the last dump database command was executed.
DB2 UDB backup and restore procedures can be performed on databases and table spaces. Archived log files
can be backed up in the same fashion as ordinary operating system files. The target device for the backup
operation can be a disk directory, a tape device, a named pipe, a Tivoli Storage Manager (TSM) server, a
storage manager that supports Backup Services APIs (XBSA), or another vendor’s server. The restore command
supports relocation of table spaces using the redirect parameter. This support includes adding, changing, or
removing table space containers. Online backups can be performed when archived logging is enabled.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
20
Two types of incremental backups are available:
w
w
Incremental (changes from last full backup)
Delta (changes from last backup of any type)
Point in time recovery is possible using the rollforward command. When recovering from a system failure, the
time specified should be before the system failure. The load and export commands can be used to back up
individual tables.
The recovery history file contains a summary of the backup information that can be used in case all or part of the
database must be recovered to a given point in time, and it is used to track events related to recovery, such as
backup, restore, and load operations.
DB2 UDB Version 8 includes several backup and recovery enhancements:
w
w
w
w
XBSA support
Ability to restore to systems with different code pages
Faster table space recovery by processing only log files that are needed
Point in time rollforward recovery to local time instead of GMT time
The Backup and Restore wizards have been rewritten to simplify their use. They also support the features added
to backup and restore in Version 8.
Online Utility Tools
Adaptive Server configuration parameters can be managed using the system stored procedure sp_configure,
editing the server configuration file or specifying the name of the configuration file when starting up the server.
Configuration parameters can be dynamic or static. A dynamic parameter new value takes effect right away,
while a static parameter requires to stop and restart the server before the new value takes effect.
With DB2 V8, over 50 configuration parameters can now be set online. Changes to these online configuration
parameters take immediate effect without the need to stop and start the instance, or deactivate and activate the
database. Users can remain connected when the system is tuned or the configuration is changed. All
configuration parameters can be changed using the Control Center GUI or the commands update database
configuration and update database manager configuration.
The bcp utility imports data from a file or exports data to a file. The main uses of these utility are importing data
from other programs or RDBMs, moving data between Adaptive Servers or exporting data from the database.
When using bcp to import to a table the process acquires an exclusive intent lock on the table, an exclusive page
lock on each data page or data row and an exclusive lock on index pages. This utility has two modes Fast bcp
which logs each row insert that it makes and Slow bcp which logs only page allocations, copying data into tables
without indexes or triggers at the fastest speed possible.
DB2 provides separates commands to import and export data. The load command copies data into a DB2 table
from a file, tape, or named pipe on the server. When you're loading data into a table in Version 8, the table
space in which the table resides will no longer be locked. Users have full read and write access to all the tables in
the table space, except for the table being loaded. For the table being loaded, the existing data in the table will
be available for read access if the load is appending data to the table specifying the parameter allow read
access. The default is allow no access. The load command will lock the target table for exclusive access during
the load.
The export command in DB2 copies data to an external file which is determined by a select statement. The file
formats available are the following:
w
w
w
DEL (delimited ASCII format)
WSF (work sheet format)
IXF (integrated exchange format, PC version includes most of the table attributes, as well as any existing
indexes)
Adaptive Server reorg command reorganizes the use of a table space. The four subcommands available define
the level or reorganization. These subcommands are reorg forwarded_rows, reorg reclaim_space, reorg
compact, and reorg rebuild. The reclaim_space and compact subcommands run on tables or indexes. The
reclaim_space, forwarded_rows, and compact subcommands use multiple small transactions of brief duration
which are limited to eight pages of reorg processing, and provide time and resume options to set a time limit and
continue at a later time.The rebuild subcommand holds an exclusive table lock for its entire duration. The opdiag
utility generates reports to asses the need to run the reorg command
DB2 reorg commang reorganizes an index or a table. In Version 8, there are two methods of reorganizing tables
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
21
offline and online using the reorg table command. The parameters allow no access or inplace determines if the
reorganization is offline or online respectively. Online table reorganization allows applications to access the table
during the operation. In addition, online table reorganization can be paused and resumed later by anyone with the
appropriate authority by using the schema and table name. An index can be indicated to physically reorder the
records in the table it is reorganizing. The reorgcheck command calculates statistics on the database to
determine if tables or indexes, or both, need to be reorganized or cleaned up.
During Online index reorganization, the user has the ability to read and update a table and its existing indexes
using the new reorg indexes command in DB2 V8. A "shadow copy" of the index object is made, leaving the
original indexes and the table available for read and write access. Any concurrent transactions that update the
table are logged. Once the logged table changes have been applied and the new index (the shadow copy) is
ready, the new index is made available. While the new index is being made available all access to the table is
prohibited.
Jobs
In Sybase, the Transact-SQL command waitfor defines a specific time, time interval, or event for the execution
of a statement block, stored procedure, or transaction. Sybase uses alarms (database structures) for the proper
execution of this command.
DB2 offers a graphical user interface called the Task Center to organize task flows, schedule tasks, and
distribute notifications about the status of completed tasks. A task can be created from a script that contains
DB2, operating system, or MVS JCL commands. This is particularly useful for scheduling administrative tasks
such as backup.
Another DB2 tool available is the Journal, which permits you to run, schedule, and delete jobs and view the
recovery history log and messages log.
System Stored Procedures and Administrative Tools
Sybase supplies system procedures used for getting reports from and updating system tables. These procedures
are located in a separate database, sybsystemprocs, and are owned by the System Administrator.
DB2 UDB provides an Administrative API to perform database management tasks, such as creating, activating,
backing up, or restoring a database. These APIs can be invoked from any application, embedded SQL programs
or not, written in the following programming languages: C, COBOL, Fortran, and REXX.
Sybase Central is a graphical user interface for managing Sybase products. It runs on Windows NT 4.0 and
Windows 95.
The DB2 Control Center (CC), is a graphical interface to manage a local database server or multiple remote
database servers and their database objects. The CC also manages databases on OS/390 or z/OS. The CC is
available on AIX, HP-UX, Linux, Sun Solaris, and Windows operating systems. From the CC, the following
administration tasks can be performed on database objects:
w
w
w
w
w
w
w
w
w
w
w
w
w
Create and drop a database
Create, alter, and drop a table space or table
Create, alter, and drop an index
Back up and recover a database or table space
Define the replication sources and subscriptions and to replicate data between systems
Set database manager configuration values that affect performance
Configure database manager communications parameters
Generate database information in a profile that can be used to configure clients
Perform licensing tasks, such as confirm DB2 products installed and number of user licenses purchased
Optimize queries, jobs, and scripts
Perform data warehousing tasks
Generate SQL statements used to define database objects
Convert Control Center actions into scripts
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
22
The following figure illustrates the Control Center Main Window:
Figure 5. Control Center Main Window
The following DB2 tools are available from the Control Center toolbar:
w
Command Center, to issue DB2 database commands, SQL statements, and operating system
commands and to view their results
w
Task Center, to create and run operating system level and DB2 command scripts
w
Journal, to run, schedule, and delete jobs and view the recovery history log and messages log.
w
License Center, to configure and to display the status of the DB2 product licenses
w
Replication Center new in DB2 Version 8, to design set up and manage a replication environment
w
Satellite Administration Center, to set up and administer both satellites, and the information that is
maintained in the satellite control tables at a central DB2 control server
w
Data Warehouse Center, to manage Data Warehouse objects
w
Information Catalog Center, to manage business metadata
w
Health Center, to monitor instances. This center also issues alerts about potential problems and provides
recommendations to resolve those problems. It offers specific monitoring tools, such as the Memory
Visualizer to drill-down into specific performance areas.
w
Development Center, to develop stored procedures, user-defined functions, and structured types
w
Information Center, to search for help on tasks, commands, and information in the DB2 library, and also
to update local documentation
The DB2 launchpads and wizards in the Control Center assist in completing administration tasks by stepping you
through the tasks. There are launchpads and wizards to add partitions (Distributed environment), backup
databases, create databases, create table spaces, create tables, monitor workload performance, configure
database manager and database parameters to improve performance, restore databases, and configure
database logging.
Database Consistency
Sybase has a database consistency checker, called dbcc, that enables users to obtain detailed information on the
consistency of tables and indexes. It is recommended that dbcc be run as a regular maintenance task, before
every backup and when a system error occurs. For performance reasons, it is advisable to run dbcc during
non-peak hours. The dbcc utility verifies that the page pointers chain is valid, checks that the row offset table is
consistent, checks that the index rows are in ascending key order on the index pages, and verifies that
non-clustered leaf index keys values are the same as the column value in the data row.
In DB2 UDB, db2dart can be used to verify that the architectural integrity of a database is correct. It is not
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
23
required to be run on a regular basis. DB2 UDB performs an automatic crash recovery after a system failure.
This tool should be run on the DB2 server where the database resides and when there are no active connections
to the database. The inspection options include a database, a table, all table space files and containers, a table
space and all its tables, and a table space structure but not its tables. For complete information on db2dart
options, type db2dart without any options. The following validations are performed:
w
w
w
w
The control information is correct
There are no discrepancies in the format of the data
The data pages are the correct size and contain the correct column types
Indexes are valid
Although Sybase dbcc statements and DB2 UDB tools produce different output information, the following table
only maps the functionality between dbcc commands and db2dart commands:
Sybase dbcc
DB2 UDB db2dart
1
dbcc checkdb (database-name)
db2dart database-name /DB
dbcc checktable (table-name)
db2dart database-name /T /TSI tablespace-id /TN table-name
dbcc checkstorage (database-name)2
db2dart database-name /DB 3
db2 list tablespaces show detail
dbcc checkcatalog (database-name)
db2dart database-name /TS /TSI 0 4
dbcc reindex(table-name)
db2dart database-name /MI /TSI tablespace-id /OI index-object-id 5
1
Default option
dbcc checkstorage contains additional allocation information. It is stored in the database dbccdb.
3
db2dart does not store any data in a database. Additional allocation information can be obtained by issuing
the DB2 command list tablespaces
4
Where 0 is the tablespace-id for the catalog table space , which by default is SYSCATSPACE.
5
Indexes are marked as invalid and rebuilt when the index is first accessed or when the database is restarted, based
on the value of the indexrec database and the database manager configuration parameters (See the Administration
Guide for details.)
2
In DB2 Version 8 the command INSPECT was introduced in order to inspect database for architectural integrity,
checking the pages of the database for page consistency. The inspection checks that the structures of table
objects and structures of table spaces are valid. It is available as an API and can be run online.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
24
Implementation Differences
This section compares implementation features available in Sybase Adaptive Server Enterprise version 12.5
(Adaptive Server) and DB2 UDB Version 8.1 for UNIX, Linux, and Windows.
Isolation Levels
Sybase supports four isolation levels. Level 0 prevents other transactions from changing data already modified,
but lets them read the uncommitted data ("dirty reads"). Level 1 (the default) prevents dirty reads, level 2
prevents non-repeatable reads, and level 3 prevents phantoms. In previous versions, if level 2 was specified,
level 3 was also enforced.
DB2 UDB also supports four isolation levels: Read Stability, Repeatable Read, Cursor Stability, and
Uncommitted Read. Regardless of the isolation level, the database manager places exclusive locks on every row
that is inserted, updated, or deleted. Thus, all isolation levels ensure that any row that is changed by this
application process during a unit of work is not changed by any other application processes until the unit of work
is complete.
Sybase
0
1 (default)
DB2 UDB
Uncommitted Read
Cursor Stability (default)
2
Read Stability
3
Repeatable Read
The defaul level, Cursor Stability, ensures that any row that was changed by another application process cannot
be read until it is committed by that application process.
Statement Isolation Levels
In Sybase, the at isolation clause for a select statement indicates the isolation level for the query. The values that
can be specified as isolation levels are 0, 1, 2, or 3.
DB2 UDB isolation levels can be defined at the statement level for more granularity and improved performance
and concurrency. The with clause at the end of the select statement specifies the desired isolation level. The
values are RR (Repeatable Read), RS (Read Stability), CS (Cursor Stability), and UR (Uncommited Read).
SELECT SALARY+BONUS+COMM AS TOTAL_PAY
FROM EMPLOYEE
ORDER BY TOTAL_PAY
WITH RS
Locking Mechanisms
Adaptive Server has two locking schemes: allpages and data only. The latter offers row-level locking in addition
to page locking. Lock promotion has two tiers: from page locks to table locks or from row locks to table locks.
Lock promotion is controlled by configuration parameters or by the system stored procedures
sp_setrowlockpromote and sp_setpglockpromote to define the number of row locks or page locks that the server
acquires on a table before it attempts to escalate to a table lock. In version 11.9.X, the command lock table was
introduced.
DB2 UDB employs row-level locking by default (page-level locking is not an option). However, the database
manager can escalate a lock to the table level. In order to minimize or avoid escalation, an entire table can be
explicitly locked with the SQL command lock table.
DB2 UDB lock escalation can be influenced by modifying the value of the Maximum Percent of Lock List Before
Escalation (maxlocks) and the Maximum Storage for Lock List (locklist) parameters in the database configuration
file. Also, the locksize parameter of the alter table statement can be used to control how locking is done for a
specific table.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
25
System Databases
The master database controls the operation of Adaptive Server as a whole, and stores information about all user
databases and their associated database devices. DB2 UDB has a catalog table space called SYSCATSPACE,
which contains all the system catalog tables for a single database. All other aspects of database configuration
can be controlled by the DB2 command interface or the Control Center. The operating system security facility
must be used to manage user accounts.
Sybase system procedures are stored in the sybsystemprocs database. The tasks automated by these
procedures can be performed using the DB2 UDB graphical interface Control Center to administer local and
remote databases or DB2 APIs. (See the previous section “Administration Issues - System Stored Procedures”.)
The Sybase model database provides a template, or prototype, for new user databases. Typically, changes made
to the model database are: adding user-defined data types, rules, or defaults; adding users who should have
access to all databases; and granting default privileges particularly for guest accounts. DB2 UDB does not
require a model database: a script can be written to create a database, define data types, and grant privileges.
The db2look command can be used to generate a script that recreates an existing database model and statistics.
Users that are already created can have access to all databases using group membership or trusted connections.
The Sybase temporary database tempdb provides a storage area for temporary tables and other temporary
working storage needs (for example, intermediate results of group by and order by). In DB2 UDB, each database
has one or more temporary table spaces for such purposes. Having a table space instead of a database brings
several advantages: applications running on different databases use different table spaces (storage areas), and
system managed space (SMS) table spaces need very little administration (limited by file system free space).
Sybase provides the following optional system databases: sybsecurity (auditing database), sybsystemdb
(two-phase commit transaction database), pubs2 and pubs3 (sample databases), and dbccdb (dbcc database).
Sybase dbccddb stores configuration information, and the operation activity is recorded by the command dbcc
checkstorage for a target database. It also contains the dbcc stored procedures for creating and maintaining
dbccdb and for generating reports on the results of dbcc checkstorage operations.
Sybase sample databases are pubs2 and pubs3. DB2 UDB provides a sample database called Sample, along
with the API db2sampl to create this database. It also provides First Steps, a graphic tool for Intel platforms that
helps in creating and manipulating the Sample database. The product documentation and sample programs refer
to this database. There is an additional sample database for Data Warehousing functionality that can be created
using the tool First Steps.
Finally, the sybdiagdb database which stores diagnostic configuration data for debugging purposes. Its intended
use is for Sybase Technical Support personnel only. DB2 UDB records details from backup, restore operations,
and load operations to the recovery history. DB2 command list history can be used to display the content of the
recovery file. The Journal tool (from the Control Center) can be used to display the database history. The Alerts
view in the Health Center can be used to display and work with current alerts.
SQL
Sybase is compliant with the SQL92 at the Entry-level. This compliant behavior is set by default for embedded
applications; the command set in Transact-SQL can be used to change it. Also, to be compliant with the SQL92
Entry-level, identifiers must not begin with a pound sign (#), contain lowercase letters, or exceed 18 characters.
Transact-SQL extensions allow the pound sign, the @ sign, lower case letters, and up to 30 characters in length.
DB2 UDB is also compliant with the SQL92 standard at the Entry-level, but includes features from the
Intermediate and Full levels and the future SQL3. There are no commands available to change the SQL
compliance level, but the SQLFLAG option on the PREP command can be used on SQL embedded applications
to check that the SQL syntax conforms to the SQL92 Entry Level syntax. Identifiers for tables, views, and aliases
are limited to 128 characters, column names are limited to 30 characters, and all other object identifiers cannot
exceed 18 characters in length.
Tables
In Adaptive Sever, the full specification of a table name in the create table command includes database name,
owner and table name. The database name and owner are optional, and the default values are the current
database name and the current user. However, if a database name is present the database must exists and the
user must be listed in the sysusers table and have create table permission for that database. This command can
be used to also create global and local temporary tables. The ON clause specify a segment_name where the
table is going to be stored.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
26
A DB2 fully qualified table name consist only of the schema and table name. In order to access a table on
another database an alias must be created, when an alias is defined a server object is specified which contains
the server connection information. Aliases permit access to tables on other local, remote or federated databases.
This command can also create materialized query tables, stage tables, and typed tables. DB2 provides a
command to create temporary tables, please see next topic Temporary Tables. The IN and LONG IN specify a
table space name where the table and the LOB columns will be stored. The INDEX IN specify a table space
name where the indexes will be stored.
Some data types provided by Microsoft SQL Server to define table columns are different than DB2 built-in data
types. The Data Types Conversion section provides a guideline to convert data types between Microsoft SQL
Server and DB2.
The following table summarizes some differences in limitations for tables between Adaptive Server and DB2
UDB:
Description
Table space page size
Maximum length of a row
Sybase
DB2 UDB
2K, 4K, 8K, 16K
4K, 8K, 16K, 32K
16,300 bytes
1
32,677 bytes 2
Maximum columns in a table
1,024
1012 3
Maximum indexes in a table
249 non-clustered
1 clustered
32 767 or storage
600 bytes
1024 bytes
2 billion
65 534
1,024
1012 3
Maximum elements in a select
250
1012 3
Maximum number of tables referenced in a
select or view
16
storage
Maximum number of columns in an
ORDER BY clause
31
1012 3
2014 bytes
32,677 bytes 2
Longest index key
Maximum tables in a database
Maximum columns in a view
Maximum total length of columns in an
ORDER BY clause
1
2
3
This value is based on a page size of 16K, and locking scheme data only.
This value is based on a table space page size of 32K, and varies according to the page size.
This value is based on a table space page size of either 8K, 16K, or 32K.
Temporary Tables
Sybase supports temporary tables, which are stored in the tempdb database. There are two types of temporary
tables, local and global. Local temporary tables have their names start with a number sign ( # ) and are visible
only in the current session (connection), while global temporary tables have their names start with the prefix
"tempdb.." and are visible in all sessions.
In DB2 UDB a declared global temporary table (DGTT) is accessible only by the application that creates it and is
automatically dropped at application termination. Such a table is created using the DDL statement declare global
temporary table. If the with replace clause is specified in this statement, then a DGTT with the same name will be
dropped and replaced with the new definition specified.
DECLARE GLOBAL TEMPORARY TABLE gbl_temp
LIKE employee
ON COMMIT DELETE ROWS
NOT LOGGED
IN usr_tbsp
DGTTs are created on a USER TEMPORARY table space, which can be designated using the IN clause. If this
clause is not specified, a table space is chosen from the set of user temporary table spaces. The table space
must be one for which the user has USE privilege, and it must have sufficient page size to contain the table. No
locking is performed on declared temporary tables, and are always created with the qualifier "SESSION".
Changes can be logged or not optionally.
In DB2 Version 8 enhancements to the DGTTs include index and statistics support to improve performance, and
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
27
undo logging to support rollbacks.
A declared temporary table is semantically equivalent to a Sybase local temporary table. Temporary tables
accessed by connections established by different applications can be implemented with DB2 UDB common table
expressions or with the not logged initially clause on the create table and alter table commands to provide
temporary table characteristics.
Indexes
In Adaptive Server, the create index command allows ordering specification. Prior to Version 11.9.X, all indexes
were created in ascending order. Since Version 11.5, the optimizer is able to perform descending index scans,
using the configuration parameter allow backward scans to enable or disable this optimization.
Sybase uses a B-tree model to represent the cluster index in a sparse index (not every row has an index entry).
DB2 UDB creates indexes on a separate structure that replicates the keys values. The cluster factor of a
clustering index is maintained or improved dynamically as data is inserted into the associated table, by
attempting to insert new rows physically close to the row with index key values in the same range. In both
databases, only one clustered index per table is permitted.
DB2 UDB creates indexes on a separate structure that replicates the keys’ values. The database manager uses a
B+ tree structure for index storage. To maintain the cluster factor of a clustering index or improve it dynamically
as data is inserted into the associated table, DB2 attempts to insert new rows physically close to the rows with
index key values in the same range. In both databases, only one clustered index per table is permitted.
Multidimensional clustering (MDC) provides an elegant method for flexible, continuous, and automatic clustering
of data along multiple dimensions. This results in significant improvement in the performance of queries, as well
as significant reduction in the overhead of data maintenance operations, such as reorganization, and index
maintenance operations during insert, update, and delete operations. Multidimensional clustering is primarily
intended for data warehousing and large database environments, and it can also be used in online transaction
processing (OLTP) environments.
In DB2 Version 8, indexes can be of either type 1 or type 2. DB2 Version 8 adds support for type-2 indexes. A
type-1 index is the older index style, indexes created in earlier versions of DB2 are of this kind. The primary
advantages of type-2 indexes are to improve concurrency because the use of next-key locking is reduced to a
minimum and an index can be created on columns that have a length greater than 255 bytes. A table must have
only type-2 indexes before online table REORG and online table LOAD commands can be used against the
table. They are also required for the new multidimensional clustering facility.
Sybase has a fillfactor clause on create index to specify a percentage value that determines how full Adaptive
Server will make each page when it is creating a new index on existing data. The default is 0, to completely fill
the pages.
DB2 UDB has a pctfree clause on the create index command to specify what percentage of each index page to
leave as free space when building the index. However, if a value greater than 10 is specified, only 10% free
space will be left in non-leaf pages. The default is 10%.
Views
Adaptive Server views cannot include order by or compute clauses, the keyword into, or the union operator in the
select statements that define views.
DB2 definition of a view allows a full select statement .The fullselect must not reference host variables,
parameter markers, or declared temporary tables. However, a parameterized view can be created as an SQL
table function. The full select allows the specification of UNION operator, and order by clause but there are
certain exceptions which are listed on the description of the command create view on the DB2 SQL Reference
Guide.
Constraints
Sybase allows the creation of defaults and rules for columns in a database to define default and check
constraints. Rules cannot be defined for a server-supplied data type or to a column of type text, image, or
timestamp.
In DB2 UDB, check and default constraints are defined using the commands create table and alter table at the
column or table level. There are no restrictions on data types. This declarative form to define check and default
constraints not only is the standard way to restrict column data, but also allows multiple definitions for one
column, and declarations are automatically dropped when the table is dropped.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
28
The Sybase integrity constraints definition can indicate whether the index to be created is nonclustered (default)
or clustered. DB2 UDB creates a unique index, using ascending order for every column in the key. If a cluster
index is required, the command reorganize table, specifying an index, uses the index to physically reorder the
records in the table.
Sybase referential constraints cannot cascade changes through related tables in the database. Triggers must be
programmed for this purpose.
DB2 UDB referential constraint definitions have a rule clause to specify what action to take on dependent tables.
There are four possible actions for the delete rule: NO ACTION (the default), RESTRICT, CASCADE, and SET
NULL. If RESTRICT or NO ACTION is specified, an error occurs and no rows are affected. If CASCADE is
specified, the operation is propagated to all the dependent rows. If SET NULL is specified, each nullable column
of the foreign key of each dependent row is set to null. NO ACTION and RESTRICT are the only possible actions
on the update rule.
DB2 Version 8 informational constraints allows the creation of check and referential integrity (RI) constraints on
tables which are not enforced by the database manager, but can still be exploited by the optimizer. The attributes
ENFORCED and NOT ENFORCED define whether the constraint is enforced by the database manager during
normal operations, such as insert, update, or delete. NOT ENFORCED should only be specified if the table data
is independently known to conform to the constraint. Furthermore the ENABLE QUERY OPTIMIZATION and
DISABLE QUERY OPTIMIZATION attributes define whether the constraint can be used for query optimization
under appropriate circumstances.
IDENTITY Columns
Sybase supports the IDENTITY constraint on numeric columns with a scale of 0. IDENTITY columns start with 1
and increment by 1.
In DB2 UDB, each table may have a single column that is defined with the IDENTITY attribute. The column types
supported are: numeric with scale of 0, integer, smallint, and bigint. An initial value and an increment value can
be specified, the default initial and increment value is 1. The values for an identity column can either be always
generated by DB2 (generated always), or only by default (generated by default). The keywords generated always
indicate that DB2 always generates unique values, and applications are not allowed to provide an explicit value.
The keywords generated by default indicate that an application can explicitly provide values, and only when
values are not given DB2 generates one.
CREATE
(c1
c2
c3
TABLE t1
CHAR(30),
DOUBLE,
INT NOT NULL GENERATED ALWAYS as identity,
(START WITH 100, INCREMENT BY 5))
Java-SQL Columns
Adaptive Server Java-SQL capabilities include Java-SQL classes. Once installed they can be used as datatype
for table columns and Transact-SQL variables. They can also be used as default values for table columns. The
data can selectively be stored in the row (data pages) or outside (similar to text/image columns).
DB2 UDB permits the user to create distinct types based on the built-in source data types and structured types
which consist of several attributes. These user defined data types can be specified as column data types.
DB2 UDB generated columns are defined in a base table where the stored value is computed using an
expression, rather than being specified through an insert or update operation. One or more generated columns
can be added to a table. It is also possible to create a nonunique indexes on a generated column.
CREATE TABLE t1
( c1 INT,
c2 DOUBLE,
c3 DOUBLE GENERATED ALWAYS AS (c1 + c2)
c4 GENERATED ALWAYS AS
(CASE WHEN c1 > c2 THEN 1 ELSE NULL END));
CREATE INDEX i1 ON t1(c4);
When issuing a query that includes the same expression, the generated column can be used directly, or the
query rewrite component of the optimizer can replace the expression with the generated column.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
29
SELECT c1 + c2 FROM t1 WHERE (c1 + c2) * c1 > 100;
can be rewritten as:
SELECT c3 FROM t1 WHERE c3 * c1 > 100 ;
DB2 User defined data types and generated columns are always available to the user independent of the choice
of programming language for the user interface.
Scrollable Cursors
Adaptive Server supports read-only and updatable cursors. Open Client DB-Library supports client-side cursors,
including static, keyset-driven, and dynamic. Backward fetches are available for keyset-driven or dynamic
cursors only. Applications cannot run unless required DB-Library stored procedures are installed. Scrollable
cursors are not supported by the JDBC or Open Client interfaces.
DB2 UDB supports static, forward-only, and scrollable cursors. There are two types of scrollable cursor: static
and keyset-driven. The latter provides the ability to detect or make changes to the underlying data. Application
support for static scrollable cursors is provided through DB2 CLI, ODBC, JDBC, and SQLJ. Keyset-driven
scrollable cursors are supported through DB2 CLI and ODBC.
ANSI Join Operators
Sybase has a specific syntax for right and left outer joins of *= and =* ; however, full outer joins are not
supported. Adaptive Server has included ANSI syntax for left and right outer joins since Version 12.
The DB2 syntax for joins is ANSI-style, with the operators INNER, LEFT [OUTER], RIGHT [OUTER], FULL
[OUTER]. The ANSI join operators also follow the ANSI definitions for join behavior. The ANSI-style join syntax
helps to avoid ambiguous interpretation when other conditions are specified in the WHERE clause.
SELECT deptno, deptname, empno, lastname
FROM department LEFT OUTER JOIN employee
ON mgrno= empno
Transactions
Sybase defines a transaction by enclosing SQL statements and system procedures within the phrases begin
transaction, save transaction, and commit. The save transaction command provides a mechanism for selectively
rolling back portions of a batch. If chained transaction mode is enabled, Adaptive Server implicitly invokes a
begin transaction before the following statements: delete, insert, open, fetch, select, and update. A commit must
still explicitly close the transaction.
DB2 does not require explicit transactions: implicit transaction is the only mode available. However, Compound
SQL (embedded) defines a group of several SQL statements into a single executable block. Compound SQL is
supported through embedded static SQL and the DB2 Call Level Interface. There are two types of compound
SQL:
w
w
Atomic: returns a response when all substatements have been executed successfully or when one of
them ends in an error. When an error occurs, the entire block is rolled back.
Not Atomic: returns a response when all substatements have been executed, regardless of whether or
not a preceding substatement failed. The entire block is rolled back only when the unit of work that
contains it is rolled back.
A Compound SQL (dynamic) statement groups statements together into an executable block. SQL variables can
be declared. This statement can be embedded in a trigger, SQL function, or SQL method, or issued through the
use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.
BEGIN ATOMIC
DECLARE p1 INTEGER DEFAULT 3;
DECLARE s VARCHAR(80);
SET s = 'INSERT INTO employee(empno) VALUES (?);'
PREPARE stmt FROM s;
EXECUTE stmt USING p1;
END;
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
30
DB2 UDB savepoint is similar to a Compound embedded SQL statement. Both mechanisms define a group of
several SQL statements into a single executable block. If any of the substatements ends in an error, only that
substatement will be rolled back. This implementation provides more granularity than a Compound SQL
statement. In order to start the savepoint block, the command savepoint is required. At the end of a savepoint
block of statements, the savepoint can be released (release savepoint savepoint-name) or rolled back to the
savepoint (rollback to savepoint). DB2 does not support the use of a savepoint within another savepoint. DB2
does not enable the use of savepoints within atomic Compound SQL or the use of atomic Compound SQL within
a savepoint.
SAVEPOINT sp1;
PREPARE s1 FROM 'SELECT FROM t1';
ALTER TABLE t1 ADD COLUMN...
PREPARE s2 FROM 'SELECT FROM t2';
OPEN c1 USING s1;
OPEN c2 USING s2;
ROLLBACK TO SAVEPOINT;
FETCH c1; --invalid (DDL statement changed object t1)
FETCH c2; --successful
...
RELEASE SAVEPOINT sp1;
Triggers
Sybase triggers are coded using Transact-SQL and stored in the database itself. Adaptive Server allows nested
triggers by default, and up to 16 levels. Triggers are always activated after a user action is attempted. Only one
trigger per user action (insert, update, delete) per table can be coded, and multiple user actions can be specified
in its definition. Subsequent trigger definitions for the same action will override the previous definition without a
warning.
DB2 UDB triggers are defined with the create trigger statement, stored in the database, and compiled at run time
with the SQL statements that are associated with the trigger. The maximum depth of cascaded triggers is 16. A
triggered action is composed of one or more SQL procedure statements, which can contain a dynamic compound
statement or any of the SQL control statements. Optional or conditional execution of these SQL statements can
be specified using the WHEN clause or the CASE expression. The triggered actions can be insert, update, or
delete. If no cascade before is specified, the triggered action will not cause other triggers to be activated.
Triggers can be activated before or after the triggered action by using the keywords no cascade before or after in
the create trigger statement. Multiple triggers can be created for the same event, activation time, and subject
tables. The order in which the triggers are activated is the same as the order in which they were created. Triggers
cannot call stored procedures, but they can invoke User-Defined Functions (UDFs).
The Sybase deleted and inserted logical tables are transition tables that hold the old values or new values of the
rows that may be changed by the user action.
The DB2 UDB create trigger statement provides referencing for transition variables by specifying correlation
names and provides referencing for transition tables by specifying table names. Correlation names identify a
specific row in the set of rows affected by the triggering SQL operation, while table names identify the complete
set of affected rows. Each row or set of rows affected by the triggering SQL operation is available to the triggered
action by qualifying columns with correlation names and table names. The Microsoft SQL Server if update
(column) can be converted to WHEN (oldtable.column != newtable.column), or the column can be specified in the
trigger definition using update of column to specify the user action that activates the trigger.
CREATE TRIGGER reorder
AFTER UPDATE OF on_hand, max_stocked ON parts
REFERENCING NEW AS n_row
FOR EACH ROW MODE DB2SQL
WHEN (n_row. on_hand < 0.10 *n_row. Max_stocked
AND n_row.order_pending = 'N')
BEGIN ATOMIC
VALUES(issue_ship_request(n_row.MAX_STOCKED -n_row. On_hand,
n_row.PARTNO));
UPDATE PARTS SET parts.order_pending = 'Y'
WHERE parts.partno = n_row.partno;
END
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
31
INSTEAD OF triggers are supported in DB2 Version 8, they provide an extension to the updatability of views.
Using an instead of trigger, the requested update operation against the view gets replaced by the trigger logic,
which performs the operation on behalf of the view. The view must be updatable; refer to the create table
command description in the DB2 SQL Reference for more details. This feature gives transparency to the
application which assumes all operations are performed against the view.
CREATE VIEW EMPV(EMPNO, FIRSTNME, MIDINIT, LASTNAME,HIREDATE,
DEPTNAME)
AS SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME,PHONENO,HIREDATE,
DEPTNAME
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.WORKDEPT = DEPARTMENT.DEPTNO
CREATE TRIGGER EMPV_INSERT
INSTEAD OF INSERT ON EMPV
REFERENCING NEW AS NEWEMP
DEFAULTS NULL FOR EACH ROW MODE DB2SQL
INSERT INTO EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME,WORKDEPT,
PHONENO, HIREDATE)
VALUES(EMPNO, FIRSTNME, MIDINIT, LASTNAME,
COALESCE((SELECT DEPTNO FROM DEPARTMENT AS D
WHERE D.DEPTNAME = NEWEMP.DEPTNAME),
RAISE_ERROR('70001', 'Unknown department name')),
PHONENO, HIREDATE)
Stored Procedures
Sybase stored procedures are coded using Transact-SQL, and are stored in the database. The maximum
number of parameters in a stored procedure is 255. Extended stored procedures are also available, to allow the
execution of external procedural language functions within the database server. Any programming language that
is capable of calling a C language function and manipulating C language data types can be used. Extended
stored procedures are implemented by an Open Server application called XP Server, which runs on the same
machine as Adaptive Server.
Using Adaptive Server Java-SQL capabilities, Java classes can be installed in the database and methods can be
invoked from Transact-SQL. These methods can be defined as SQLJ stored procedures and be manipulated in
the same manner as Transact-SQL stored procedures. SQLJ stored procedures can return result sets and be
called from applications using JDBC or ODBC. Sybase Central can be used to create and execute SQLJ stored
procedures.
DB2 UDB stored procedures can be coded in SQL or in compiled libraries using third-generation languages,
including C, C++, COBOL, Microsoft Visual Basic and Java (SQLJ/JDBC). The maximum number of parameters
is 32767. Nested stored procedures are supported in SQL, Java and C procedures. Multiple result sets can be
returned to the calling stored procedure or the calling application. Stored procedures can run in the same address
space as the database manager, or in a separate space by specifying the clause fenced or not fenced on the
create procedure statement. Running stored procedures that are not adequately checked out on the same
address space as the database server can compromise integrity of such servers.
In SQL Procedures, the procedural logic is defined in the procedure body contained in a create procedure
statement. The DB2 SQL Procedural Language (SQL PL) is based on ANSI/ISO standard SQL/PSM. The
platforms supported are Windows NT, AIX, and Sun Solaris operating systems. The following example shows a
create procedure statement for a simple stored procedure. The procedure name, the list of parameters that are
passed to or from the procedure, and the LANGUAGE parameter are common to all stored procedures.
However, the LANGUAGE value of SQL and the BEGIN/END block, which forms the procedure body, are
particular to an SQL procedure.
CREATE PROCEDURE UPDATE_SALARY_1
(IN EMPLOYEE_NUMBER CHAR(6),
IN RATE INTEGER)
LANGUAGE SQL
BEGIN
UPDATE EMP
SET SALARY = SALARY * (1.0 + RATE / 100.0)
WHERE EMPNO = EMPLOYEE_NUMBER;
END
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
32
After the create procedure statement is executed, the DB2 Parser analyzes the statement. Information is stored
in the catalog tables SYSIBM.SYSPROCEDURES and SYSIBM.SYSPROCPARMS. A C stored procedure is
generated, a build routine is executed, and the shared library containing the procedure is installed in the proper
directory. The shared library compile and link steps require a C or C++ compiler to be available on the database
server machine. However, once an SQL procedure is defined, it can be distributed in a compiled form to DB2
databases that run on the same platform.
DB2 allows the user to extract and install SQL procedures in compiled form from one database into another one.
DB2 provides both a command line interface and a programming interface for the extraction and the installation
operations. The command line interface consists of two CLP commands: GET ROUTINE and PUT ROUTINE.
The programmatic interface consists of two built-in stored procedures: GET_ROUTINE_SAR and
PUT_ROUTINE_SAR. For more information on the command line interface, refer to the Command Reference,
and on the programming interface refer to the SQL Reference.
There is no direct translation between Transact-SQL and DB2 SQL PL statements but there is equivalent
functionality. The following table attempts to establish an equivalency between Transact-SQL and SQL PL for
DB2 for the most relevant statements:
TransactSQL
DB2 SQL PL
DECLARE @varname datatype = defaultvalue
DECLARE varname datatype DEFAULT defaultvalue;
SELECT @var1=value
SET var1 = value;
SELECT @var1=colname from table where…
SET var1 = ( SELECT colname from table where…);
SELECT @v1=col1,@v2=col2,@v3=col3 from table...
SELECT col1,col2,col3 into v1,v2,v3 from table...
WHILE expression BEGIN … END
WHILE expression DO … END WHILE;
CONTINUE
ITERATE
BREAK
LEAVE loop_label
IF (….) BEGIN … END ELSE …..
IF (….) THEN … ELSE ….. END IF;
EXECUTE procname( parm1,parm2,...)
CALL procname( parm1,parm2,…);
EXECUTE @retval=procname( parm1,parm2,…)
CALL procname( parm1,parm2,…);
GET DIAGNOSTICS retval = RETURN_STATUS;
RETURN <int_value>
RETURN < int_expr>;
@@rowcount
GET DIAGNOSTICS <var> = ROW_COUNT
GOTO <label>
GOTO <label>
RAISERROR <error>,"msg"
SIGNAL <sqlstate> SET MESSAGE_TEXT='msg'
( The semantic is slightly different because of the
SQLSTATE type (CHAR5) and RAISERROR does not
interrupt control flow.)
Stored procedures can be programmed using embedded static or dynamic SQL, CLI, or Java. Java Stored
procedures can be coded using SQLJ or JDBC. Procedural logic can be easily implemented in any of the
programming languages to match Transact-SQL logic.
The following figure illustrates the architecture for DB2 stored procedures in SQL or a third-generation language:
Figure 6. DB2 UDB Stored Procedures
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
33
The Application Development Client includes the Development Center, a graphical application that supports the
rapid development of DB2 stored procedures. The Development Center can be used to create, build, and deploy
Java and SQL stored procedures on local and remote DB2 servers.
Other features of the Development Center include:
w
w
w
w
Capability to test and debug the execution of installed stored procedures using the integrated debugger
A view of the contents of the server for each database connection in a project and ability to work with
other database objects such as tables, triggers, and views
Export and import routines and project information
Add-ins for easy access to Development Center features and other DB2 centers from IBM's WebSphere
Studio Workbench, Microsoft Visual Studio.Net, Microsoft Visual Basic, and Microsoft's Visual InterDev
The Development Center manages all database connections using the JDBC API. Several IBM DB2 JDBC
drivers are installed with the ADC. Using a JDBC driver, connections can be established to any local DB2 alias
or remote database. In order to connect to an OS/390 or z/OS server, DB2 Connect must be installed. This tool
is available on AIX, HP-UX, Linux, the Solaris Operating Environment, and Windows. Stored procedure
development and deployment support for the entire DB2 family of servers including OS/390 and AS/400.
Enhancements for routines (stored procedures, user-defined functions, and methods) in DB2 Version 8 include
the following:
w
w
w
w
w
w
w
w
w
w
Catalog views for functions, methods, and stored procedures have been merged into
SYSCAT.ROUTINES and SYSCAT.ROUTINEPARMS. The previous catalog views are still supported for
compatibility.
The routine EXECUTE privilege has been defined to explicitly control who can invoke routines.
New authorities have been defined to explicitly control who can register external routines.
There is an ALTER statement for external routines to change the EXTERNAL NAME to reference a new
routine body.
NOT FENCED routines support nesting and recursion. There are no restrictions on the types of routines
that can be nested. For example, FENCED routines can invoke NOT FENCED routines, and vice-versa.
NOT FENCED stored procedures can return result sets. SQL procedures are automatically registered as
NOT FENCED.
Java routines now support recursion. There are no restrictions on the types of routines that can be
nested. Routines are now implemented using a thread-based model.
Routines defined as thread-safe will run in a single fenced-mode process. There is one process for Java
routines and another process for non-Java routines to reduce the amount of context switching for users
that run large numbers of fenced mode routines. For Java routines, this will also allow resource sharing
of the Java Virtual Machine (JVM).
The DB2 library manager dynamically adjusts its library caching according to your workload.
External UDFs and methods can now contain read-only SQL statements. Both static and dynamic SQL
can be used.
The CALL statement is now a fully compiled statement. This means that the CALL statement can now be
dynamically prepared in CLI, ODBC, embedded SQL, JDBC, and SQLJ. Input arguments to a stored
procedure call can be expressions.
User Defined Functions
Adaptive Server Java-SQL capabilities allow the user to create Java User-Defined Functions and SQLJ UDFs by
adding an SQL wrapper to methods for a class. Once a Java class is installed in the Adaptive Server database,
the create function command must be used to add the SQL wrapper for each method prior to invoking the UDFs
from Transact-SQL statements.
DB2 UDB has five different types of functions that can be created using the CREATED FUNCTION statement:
w
w
w
w
External scalar and table functions written in C, C++, or Java (SQLJ/JDBC) that return a scalar value or
a complete table
OLE DB External table function that allows data access from an OLE DB provider
Source or template functions whose definition is based on an existing function
SQL scalar, table, or row functions that have a body defined by SQL PL statements and that return a
scalar, a row, or a complete table.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
34
The following examples illustrate the DB2 syntax for creating an SQL scalar and SQL table function:
CREATE FUNCTION TAN (X DOUBLE)
RETURNS DOUBLE LANGUAGE SQL CONTAINS SQL
NO EXTERNAL ACTION DETERMINISTIC
RETURN SIN(X)/COS(X);
CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
RETURNS TABLE (EMPNO CHAR(6), LASTNAME VARCHAR(15),
FIRSTNAME VARCHAR(12))
LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT EMPNO, LASTNAME, FIRSTNME
FROM EMPLOYEE
WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO
Java UDFs must be coded and compiled before executing the create function command to register the external
UDF in the database. A copy of the class file should be saved to the default function path when the external
name does not include a full path specification. These same steps apply to all external UDFs. The following
examples include the Java code and the create function statement for the stringlen( ) UDF:
import java.lang.*;
// for String class
import COM.ibm.db2.app.*;
// UDF and associated classes
class UDFsrv extends UDF
{
public void scalarUDF(String inputString, int outputStrLen)
throws Exception
{
try
{
set(2, inputString.length());
}
catch (Exception e)
{
throw e;
}
}
}
CREATE FUNCTION stringlen ( VARCHAR(20) , INT )
RETURNS INT
FENCED VARIANT NO SQL
NO EXTERNAL ACTION
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL
FINAL CALL DISALLOW PARALLEL DBINFO
EXTERNAL NAME "DB2Udf!scalarUDF"
The Development Center can be used to create, build, and deploy the following UDFs:
w
w
w
w
SQL table and scalar UDFs
UDFs that read MQSeries® messages
UDFs that access OLE DB data sources
UDFs that extract data from XML documents
There are many enhancements to routines (stored procedures, user-defined functions, and methods) in DB2
Version 8. The term routine is used to encompass stored procedures, UDFs, and methods. This reflects the fact
that as of DB2 Version 8, parameter styles, data type mappings, and system catalogs are the same for all three
routine types.
Please refer to the previous topic Stored Procedures, which includes a detailed list of enhancements and
highlights for all types of DB2 routines including UDFs.
XML in the Database
Adaptive Server version 12.5 provides methods for storing XML documents and generating them from SQL data.
This feature, written entirely in Java, allows a user to select raw data from Adaptive Server using XQL to
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
35
compose an XML document, and to store XML documents in Adaptive Server.
Sybase provides a Java XML parser to install a query engine which can run as a standalone program or inside
the Adaptive Server. It is also compatible with any query engine that is compliant with SAX 2.0.
DB2 XML Extender provides the ability to store and access XML documents (XML columns), to generate XML
documents from existing relational data, and to insert rows into relational tables from XML documents (XML
collections). The XML Extender provides new data types, functions, and stored procedures to manage XML data
in DB2 . XML UDFs store, retrieve or search XML columns. XML stored procedures compose relational data into
XML documents or decompose XML documents into relational data using Document Access Definition (DAD)
and Document Type Definition (DTD) files. A DAD file specifies the mapping between the XML document and
the DB2 table structure; style sheets and XML schemas specify additional instructions for XML format and
content.
The XML Extender provides three methods of administration: the XML Extender administration wizard, the XML
Extender administration command, and the XML Extender stored procedures.
The XML Extender is supported on the following platforms: iSeries, OS/390, z/OS, AIX, Sun Solaris, Linux,
Windows NT and Windows 2000 operating systems.
Global Variables
Sybase provides global variables to report system or connection information. The global variable names start
with '@@' characters.
DB2 UDB has special registers for that purpose, such as CURRENT SERVER, CURRENT DATE, CURRENT
TIME, and USER. Where a special register is not available to provide an equivalent to a global variable, a DB2
scalar UDF can be defined.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
36
Programming Interfaces
DB2 Programming Interfaces Overview
DB2 provides the following programming interfaces to develop applications:
w
Embedded SQL
Uses SQL statements that are precompiled before a program is compiled. The SQL statements can be
static or dynamic. Using DB2, embedded SQL applications can be coded in the C/C++, COBOL, Fortran,
Java (SQLJ), and REXX programming languages.
w
DB2 Call Level Interface (CLI)
A callable SQL interface based on the X/Open CLI specification; compatible with the Microsoft
Corporation's Open Database Connectivity (ODBC).
w
DB2 Application Programming Interfaces (APIs)
APIs that perform database administration tasks, such as create, activate, back up, and restore. DB2
APIs can be called from applications, including embedded SQL and DB2 CLI applications.
w
Java Development Kit
Tools and environment to develop Java applications and applets. The kit includes driver support for
client applications and applets written in Java using JDBC. It also provides support for embedded SQL
for Java (SQLJ), Java user-defined functions (UDFs), and Java stored procedures.
w
Microsoft Visual Basic and Visual C++
Programming environments used to develop applications conforming to Data Access Object (DAO) and
Remote Data Object (RDO) specifications, and ActiveX Data Object (ADO) applications that use the
Object Linking and Embedding Database (OLE DB) bridge or the IBM® OLE DB Provider for DB2.
w
IBM or third-party tools
Applications can also be developed using tools such as Net.Data, Excel, Perl, PHP, and Open Database
Connectivity (ODBC), as well as end-user tools such as Lotus Approach and its programming language,
LotusScript.
DB2 Version 8 supports compilers, interpreters, and related software for AIX, HP-UX, Linux, Solaris, and
Windows operating systems. This information and supported programming environments are documented in DB2
Application Development Guide: Building and Running Applications and the DB2 UDB V8 application
development page: ibm.com/software/data/db2/udb/ad/. The following figure depicts the architecture for a DB2
client/server application using any of the programming interfaces described above:
Figure 7. Client/Server Architecture
The client application requires the installation of DB2 Run-Time Client or DB2 Administration Client to allow
database connectivity.
DB2 Call Level Interface
DB2 CLI is a programming interface that allows applications to access DB2 databases using dynamic SQL.
These applications can be written in C or C++. DB2 CLI is an alternative to embedded dynamic SQL.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
37
In addition to dynamic SQL, DB2 CLI offers the following advantages :
w
Portability - It uses a standard set of functions to pass SQL statements to the database. It is only
necessary to compile and link a DB2 CLI application before execution; no precompile or bind steps are
needed. It removes the dependence on precompilers.
w
No binding - There is no need to bind individual DB2 CLI applications to each database they access;
only one binding is needed to the bind files that are shipped with DB2 CLI, for all DB2 CLI applications.
w
Multiple connections to a database - DB2 CLI applications can connect to multiple databases,
including multiple connections to the same database, from the same application. Each connection has its
own commit scope.
w
Array fetching and input - Functions can retrieve multiple rows in the database into an array with a
single call. An SQL statement can be executed many times, using an array of input variables.
w
Consistent interface to catalog - It provides a consistent interface among systems to query catalog
information about tables, columns, foreign and primary keys, and user privileges.
w
Extended data conversion - Automatically converts data between SQL and C data types.
w
No global data areas - Eliminates the need for application-controlled global data areas, such as SQLDA
and SQLCA. Instead, it automatically allocates and controls the necessary data structures and provides a
handle to let an application reference them.
w
Retrieve result sets from stored procedures - DB2 CLI applications can retrieve multiple rows and
result sets generated from a stored procedure residing on the server.
w
Scrollable cursors - It supports server-side scrollable cursors that can be used in conjunction with array
output. Static read-only and keyset-driven scrollable cursors can be declared.
The DB2 CLI driver also acts as an ODBC driver when loaded by an ODBC driver manager. It conforms to
ODBC 3.51 (see the CLI Programming Guide for detailed information). DB2 CLI is a self-sufficient driver which
supports a subset of the functions provided by the ODBC driver. The following figure compares the architecture
between an ODBC application and a DB2 CLI application:
Figure 8. DB2 CLI vs. ODBC Architecture
DB2 Java Enablement
DB2 UDB implements two standards-based Java programming APIs: JDBC and SQLJ. DB2's Java enablement
is included in DB2 Application Development Client and it has the following components:
w
Support for client applications and applets written in Java using JDBC
w
Precompile and binding support for client applications and applets written in Java using SQLJ
w
Support for Java UDFs and stored procedures on the server.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
38
Java Runtime Environment (JRE) 1.3.1 is required for running applications and tools on all platforms , except
HP-UX, where JRE 1.4 is required. For application development, Java Development Kit (JDK) is required.
JRE/JDK 1.3.1 is required on all platforms, except 64-bit HP-UX and 64-bit Solaris, where 1.4 is required.
DB2 Version 8 provides Type 2, Type 3 and Type 4 JDBC drivers. The Type 2 and Type 3 drivers continue to
use the DB2 CLI interface to communicate to DB2 UDB servers (OS/400, OS/390, UNIX, Linux, and Windows).
The JDBC Type 2 and Type 3 drivers are provided in the db2java.zip file located in the sqllib\java
directory. DB2 Version 8 adds a new DB2 JDBC Universal Driver (Type 4), which uses the Distributed Relational
Database Architecture™ (DRDA®) protocol for client/server communications. This new driver is provided in the
db2jcc.jar file in the sqllib\java directory.
The following figure shows how JDBC application works with DB2 using the Type 2 driver. Calls to JDBC are
translated to calls to DB2 CLI through Java native methods. JDBC requests flow from the DB2 client through
DB2 CLI to the DB2 server. SQLJ applications use this JDBC support, and in addition require the SQLJ run-time
classes to authenticate and execute any SQL packages that were bound to the database at the precompiling and
binding stage.
Figure 9. Java Application implementation in DB2 with the Type 2 Driver
JDBC Version 2.1 from Sun has two defined parts: the core API, and the Optional Package API. The DB2 type 2
JDBC driver supports the JDBC 2.1 core API; however, it does not support all of the features defined in the
specification. The following features are not supported: Updatable ResultSets, New SQL types (Array, Ref, Java
Object, Struct), Customized SQL type mapping, and Scrollable sensitive ResultSets (scroll type of
ResultSet.TYPE_SCROLL_SENSITIVE). The type 2 driver supports the following features of the Optional
Package API: Java Naming and Directory Interface (JNDI), connection pooling, and Java Transaction APIs
(JTA).
The DB2 type 4 JDBC driver supports the JDBC 2.1 core API, however, it does not support all of the features
defined in the specification. The following features are not supported: Updatable ResultSets, New SQL types
(Array, Ref, Java Object, Struct), and Customized SQL type mapping. The type 4 driver supports JNDI of the
Optional Package API.
For more information on Java application development and the JDBC specification, see the DB2 Universal
Database Java Web site at: www.ibm.com/software/data/db2/java/.
Java 2 Platform, Enterprise Edition (J2EE) defines the standard for developing multi-tier enterprise applications.
The J2EE platform manages the infrastructure and supports the Web services to enable development of secure,
robust and interoperable business applications. The DB2 JDBC Type 2 driver is J2EE certified for use with
WebSphere Application Server (WAS), which means it conforms to J2EE specifications.
The following illustration shows the IBM J2EE application model with all three fundamental parts: components,
containers, and connectors:
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
39
Figure 10. J2EE Application model
Embedded SQL
Sybase provides precompilers for C and COBOL. DB2 UDB supports the C, C++, COBOL, and Fortran
programming languages through its precompilers. It also supports the REXX language (through a dynamic
interpreter) and the Java language.
Sybase uses the variables SQLCODE, SQLCA, and SQLSTATE to communicate between the Adaptive Server
and the application. When using DB2 UDB, set the LANGLEVEL precompile option to SQL92E in order to
declare SQLSTATE and SQLCODE fields explicitly as variables:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6]
long SQLCODE;
...
EXEC SQL END DECLARE SECTION;
Sybase allows the use of all Transact-SQL statements, functions, and control-of-flow language in Embedded
SQL, with a few exceptions. DB2 UDB SQL and Transact-SQL extensions are different; some Transact-SQL
statements are not valid in DB2 UDB.
In Sybase, there are statements that associate the descriptor with an SQL statement and with the appropriate
cursor associated with the SQL statement (allocate descriptor, get descriptor, set descriptor). The DB2 UDB
describe command obtains information about a prepared SQL statement.
In Sybase, the connect statement is used to establish a connection between an application program and
Adaptive Server. The connect statement is also available in DB2 UDB, but the syntax is slightly different: a
database name specification is mandatory, but the user specification is optional.
There are two ways in Sybase to group statements: ANSI/ISO SQL mode and Transact-SQL transaction mode.
The latter provides a save transaction or begin transaction statement. DB2 UDB employs the ANSI/ISO SQL
transaction mode for all programming APIs: a transaction begins implicitly with the first executable SQL
statement, and is ended by either a commit or a rollback statement or when the program ends.
Open Client and Open Server Libraries
Sybase Open Client provides two core programming interfaces to write client applications: Client-Library and
DB-Library. Sybase Open Server provides Server-Library for creating server applications. These libraries offer
different features and data structures for communication (CS_CONNECTION for Client-Library, and
DBPROCESS for DB-Library).
DB2 CLI is a callable SQL interface that can be used to program both client applications and server applications.
The same application does not require any source changes to run against DB2 UDB on any Intel or UNIX
platform; therefore, a single base code will allow support for several platforms. The DB2 CLI driver conforms to
ODBC 3.51. Because of DB2 CLI compliance with ODBC 3.51 functions, the application conversion is reduced to
testing in most cases (see DB2 CLI vs. ODBC Function Map section for a table listing supported APIs).
DB2 APIs are supported from a DB2 CLI or embedded SQL application to perform database administration
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
40
tasks, such as creating, activating, backing up, or restoring a database.
DB2 external stored procedures can be coded in static or dynamic embedded SQL, DB2 CLI, or JDBC.
Embedded SQL offers several 3GL options such as C, C++, COBOL, Fortran, and Java (SQLJ). Since Version
7.2 they can also be written in Microsoft Visual Basic. For more details see Stored Procedures under the section
Implementation Differences.
Applications using the DB2 transaction manager for updating multiple databases only need to use connection
type 2. When using other XA-compliant transaction managers supported by DB2, such as IBM TXSeries™
CICS®, IBM TXSeries Encina®, BEA Tuxedo, or Microsoft Transaction Server the proper API provided by the
transaction manger can be used to program applications.
These programming interfaces are very different, but the effort of porting applications to DB2 can be significantly
reduced by using conversion tools (see the Conversion Tools section).
Java Applications
Adaptive Server provides a runtime environment for Java which allows the following capabilities: Invoke Java
methods in the database, store Java classes as datatypes, and store and query XML in the database. The
run-time environment includes a Java VM and the Java classes, or Java API.
The Sybase Java VM supports a subset of JDK Version 2.0 (UNIX and Windows NT) classes and packages. The
Sybase runtime Java classes are the low-level classes installed automatically when Adaptive Server is installed.
Sybase does not support runtime Java packages and classes that assume a screen display, deal with networking
and remote communications, or handle security. By default, Adaptive Server is not enabled for Java, the
spconfigure command must be used to enable the server for Java.
The Sybase native JDBC driver that comes with Adaptive Server supports JDBC Version 1.2. It is compliant with
and supports several classes and methods of JDBC Version 2.0. A client that requires a JDBC driver must use
jConnect Version 5.2 or later, which supports JDBC Version 2.0.
Sybase provides an Enterprise JavaBean (EJB) Server, this framework can be used to create, deploy, and
manage EJBs in a multi-tier environment.
DB2's Java enablement provides support for client applications and applets written in Java using JDBC or SQLJ.
Plus, support for Java UDFs and Java stored procedures on the server.
For application development in DB2, Java Development Kit (JDK) is required. JRE/JDK 1.3.1 is required on all
platforms, except 64-bit HP-UX and 64-bit Solaris, where 1.4 is required.
DB2 Version 8 provides Type 2, Type 3 and Type 4 JDBC drivers. The DB2 type 2 and type 4 JDBC drivers
support the JDBC 2.1 core API (see prior topic DB2 Java Enablement for more details).
WebSphere® Studio is a suite of tools that can be used for all aspects of Web site development into a common
interface. DB2 provides tight integration with WebSphere Studio. DB2 Universal Developer's Edition includes
WebSphere Application Server Standard Edition (a component of WebSphere Studio) for J2EE application
development. The DB2 JDBC Type 2 driver is J2EE certified for use with WAS. The ADC sample programs
include an EJB sample application which includes two business services. One service allows the user to access
information about an employee and the other service allows the user to retrieve a list of the employee numbers.
The ADC samples also include a sample EAR file when deployed with IBM WebSphere Application Server, it
demonstrates how Java clients can interact with Enterprise Java Beans (EJBs) to access data stored in DB2.
Since Adaptive Server and DB2 UDB support Sun Java SDK for development, the porting effort is reduced to
mainly convert the Transact-SQL statements generated by the Java application. If only classes supported by
both RDBMS are used and SQL statements comply to SQL92E standard, it is possible to have a common base
code.
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
41
Terminology Map
The following table compares the basic DB2 UDB administration-related terms to the equivalent terms used by
Sybase Adaptive Server Enterprise:
DB2 Universal Database
Sybase
Physical Layer
Table space
SMS table space
DMS table space
Container (raw or file)
Logical Layer
Server
Instance (one or more; each instance manages one or
more databases)
Database
Database directories (created by DB2)
Node directory (created by DB2)
Database Manager Configuration File
Database Configuration File
Catalog tables
Database Objects
Schema
Table
Table constraint
View
Index
Recovery log
Archive log
Users and user groups (operating system)
Package
Sample database (called sample)
Administration / Usage
Control Center
Tables assigned to table spaces, containers assigned to
table spaces
Administration commands and statements
Binding (binds a DB2 utility or a program to a database so
it can be used)
Backup database
Archive online log files
Restore from backup
Roll-forward recovery
Crash recovery
Run statistics
Load, Import, Export
Command Processor (CLP)
Segment
n/a
n/a
Database device (raw or file)
Server
SQL Server
Database
Interfaces file
Interfaces file
Server configuration file
Database options and server configuration file
System tables (master database and database)
Schema
Table
Rule and table constraint
View
Index
Transaction log
Transaction log dump
Database users, groups and roles
n/a
Sample database (called pubs2)
SQL Central
Tables assigned to segments, Database devices assigned
to databases and segments assigned to database devices,
System stored procedures
n/a
Dump database
Dump transaction
Load database
Load transaction
Automatic recovery
Update statistics
bcp
ISQL
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
42
DB2 CLI vs. ODBC Function Map
The following table summarizes the two levels of support, provides a complete list of ODBC 3.51 functions, and
indicates if they are supported.
ODBC Features
DB2 ODBC Driver
DB2 CLI
Core Level Functions
Level 1 Functions
Level 2 Functions
Additional DB2 CLI Functions
All
All
All
All, functions can be accessed by
dynamically loading the DB2 CLI
library.
All
All
All, except for SQLDrivers()
SQLSetConnection()
SQLGetEnvAttr()
SQLSetEnvAttr()
SQLSetColAttributes()
SQLGetSQLCA()
SQLBindFileToCol()
SQLBindFileToParam()
SQLExtendedBind()
SQLExtendedPrepare()
SQLGetLength()
SQLGetPosition()
SQLGetSubString()
SQL Data Types
All the types listed for DB2 CLI.
C Data Types
All the types listed for DB2 CLI.
SQL_BIGINT
SQL_BINARY
SQL_BLOB
SQL_BLOB_LOCATOR
SQL_CHAR
SQL_CLOB
SQL_CLOB_LOCATOR
SQL_DBCLOB
SQL_DBCLOB_LOCATOR
SQL_DECIMAL
SQL_DOUBLE
SQL_FLOAT
SQL_GRAPHIC
SQL_INTEGER
SQL_LONG
SQL_LONGVARBINARY
SQL_LONGVARCHAR
SQL_LONGVARGRAPHIC
SQL_NUMERIC
SQL_REAL
SQL_SHORT
SQL_SMALLINT
SQL_TINYINT
SQL_TYPE_DATE
SQL_TYPE_TIME
SQL_TYPE_TIMESTAMP
SQL_VARBINARY
SQL_VARCHAR
SQL_VARGRAPHIC
SQL_WCHAR
SQL_C_BINARY
SQL_C_BIT
SQL_C_BLOB_LOCATOR
SQL_C_CHAR
SQL_C_CLOB_LOCATOR
SQL_C_DATE
SQL_C_DBCHAR
SQL_C_DBCLOB_LOCATOR
SQL_C_DOUBLE
SQL_C_FLOAT
SQL_C_LONG
SQL_C_SHORT
SQL_C_TIME
SQL_C_TIMESTAMP
SQL_C_TINYINT
SQL_C_SBIGINT
SQL_C_UBIGINT
SQL_C_NUMERIC **
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
43
ODBC Features
DB2 ODBC Driver
DB2 CLI
Return codes
All the codes listed for DB2 CLI.
SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_NEED_DATA SQL_NO_DATA_FOUND
SQL_ERROR
SQL_INVALID_HANDLE
SQLSTATES
Multiple connections per application
Dynamic loading of driver
Mapped to X/Open SQLSTATES with
additional IBM SQLSTATES, with the
exception of the ODBC type 08S01.
Supported
Supported
Mapped to X/Open SQLSTATES with
additional IBM SQLSTATES
Supported
Not applicable
** Only supported on Windows platform
The following table map DB2 isolation levels to ODBC transaction isolation levels:
IBM Isolation Level
Cursor Stability
Repeatable Read
Read Stability
Uncommitted Read
ODBC Isolation Level
SQL_TXN_READ_COMMITTED
SQL_TXN_SERIALIZABLE_READ
SQL_TXN_REPEATABLE_READ
SQL_TXN_READ_UNCOMMITTED
Note: SQLSetConnectAttr() and SQLSetStmtAttr() will return SQL_ERROR with an SQLSTATE of HY009 if you try to set an
unsupported isolation level
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
44
Conversion Tools
The most common approaches to converting a database application are manual conversion, dynamic call
translation, and automated conversion. In general, conversion tools take in source code and translate data
management calls to an equivalent SQL call. Information from the source and target database, as well as
program code, is used to build the new SQL statements. Some tools use an expert system to make decisions
over the generated SQL statements by cross-referencing the original and the new databases.
IBM DB2 Migration Toolkit (MTK)
MTK helps to migrate from Oracle, Sybase and Microsoft SQL Server (Versions 6 and 7) databases to DB2 UDB
Version 7.2 and Version 8.1 databases on any supported DB2 UDB workstation platform. The toolkit can also aid
in migration to DB2 UDB Server for OS/390 and z/OS, and DB2 UDB for AS/400. It runs on AIX, Linux, Sun
Solaris, Windows NT, and Windows 2000. The only language available is English.
Figure 11. Toolkit Menu
The toolkit includes the following features:
w
A wizard to migrate simple databases
w
A full-featured GUI interface (Toolkit) to further refine the migration and tailor options for complex
databases conversions
w
The migration source can be a database data source or a DDL script
w
Converts Transact-SQL or PL/SQL object definitions (including stored procedures, user-defined
functions, triggers, packages, tables, views, indexes and sequences.)
w
Converts Transact-SQL or PL/SQL scripts to DB2 scripts
w
Generates and run Scripts to unload data from source and upload data to DB2
w
Tracks the status of object conversions and data movement -- including error messages, error location
and DDL change reports -- using the detailed migration log file and report.
The migration process in MTK is project-based and has the following steps:
w
Create a project
w
Specify source (ODBC DSN or SQL script)
w
Convert - generates DB2 scripts and reports error messages
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
45
w
Refine - user changes SQL statements to correct errors or rename objects, after modifications are made
the user must return to convert in order to apply the changes
w
Generate Data Transfer scripts
w
Deploy to DB2 - creates database or connects to an existing one, creates database objects and
optionally loads data using scripts from the previous step.
MTK is available for a complementary download from www.ibm.com/software/data/db2/migration/mtk/.
The DB2 Developer Domain (DB2DD) library contains many references to material related to migration to DB2
topics, specifically a tutorial and a technical article about MTK:
w
Sample Database Migration Tutorial Using IBM DB2 Migration Toolkit. This tutorial demonstrates
the use of MTK to convert a database model from a Microsoft SQL Server DDL script to a DB2
database. The converted objects will include a stored procedure:
www7b.boulder.ibm.com/dmdd/library/tutorials/0209jarzebowicz/index.html
w
Automate the Switch to DB2 Using the IBM DB2 Migration Toolkit. This technical article explains
how MTK works:
www7b.software.ibm.com/dmdd/library/techarticle/0209jarzebowicz/0209jarzebowicz.html.
AllFusion Erwin Data Modeler
This product is a database modeling tool aids in designing, creating and maintaining databases, data warehouses
and enterprise data models. A logical model, along with business rules, defines the database, and a physical
model represents the target database. This tool allows visualization of the structure, key elements, and design of
a database. It automatically generates tables, stored procedures, and trigger code for leading databases, such as
DB2 and Microsoft SQL Server.
ERwin can also be used to reverse-engineer database objects using a DDL script or an existing database. The
physical model allows users to select different target databases and generate a DDL script for every target. Using
this feature, DDL scripts for different databases and versions can be easily supported.
For more information, contact Computer Associates International Inc. (www3.ca.com)
Data Junction
Data Junction offers a transformation tool for DB2 data migration and application integration. It is a visual design
tool for building and testing data transformations that work between DB2 and other data formats, such as
Microsoft SQL Server. Microsoft SQL Server is supported through a native API, a Massive Insert API, and the
bcp command.
Projects and transformations designed with Data Junction can be executed by the DJEngine. This tool executes
data transformations on demand or as scheduled.
The graphic interface allows the definition of source-to-target mapping and transformation rules. It accounts for
data type differences, and can set various filters to dynamically modify target columns during the conversion
process.
For additional information, contact Data Junction Corporation (www.datajunction.com).
Embarcadero Technologies
This company offers a suite of products for database administration, design and development. Some of these
products are:
w
Data Voyager - Generate and deploy Java and web-based applications. Management of data in relational
databases.
w
DBArtisan - Manage databases across the enterprise to achieve higher availability, performance and
security.
w
Embarcadero SQL Debugger - Isolate and correct errors on database logic in applications. Included in
DBArtisan and Rapid SQL.
w
Embarcadero SQL Tuner - Troubleshoot and rewrite database logic to improve application performance.
w
ER/Studio - Design, document and maintain databases and data warehouses for higher performance and
quality. Reverse engineer database objects (DDL).
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
46
w
ER/Studio Repository - Distribute work among the modeling team members.
w
Rapid SQL - Write, debug and tune database logic in applications.
For more information, contact Embarcadero Technologies (www.embarcadero.com/products/products.asp)
SQL Conversion Workbench (SQL-CW)
One of the automated tools available today is the SQL conversion workbench. This PC-based tool kit from
Mantech Systems Solutions Corporation (MSSC) facilitates the conversion of various databases and their
associated applications to any database in the DB2 UDB family on any supported platform. Microsoft SQL Server
Version 7 is one of the database management systems supported.
The SQL-CW tool does the following:
w
Provides metrics for the source database in order to estimate cost and effort
w
Allows modifications to the design and data definition of the target database
w
Generates DDL scripts for the target database
w
Unloads data and creates load scripts
w
Allows code re-engineering
w
Generates procedural code for stored procedures and triggers
w
Generates application code for DB-Lib, Client-Lib, and E-SQL applications
For more information, contact MSSC (www.mssc-mantech.com/products/products.html/).
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
47
Resources and References
DB2 Universal Database Version 8 Documentation
What’s New?
Quick Beginnings for DB2 Personal Edition
Quick Beginnings for DB2 Servers
Administration Guide: Planning
Administration Guide: Implementation
Application Building Guide: Building and Running Applications
Application Building Guide: Programming Server Applications
Call Level Interface Guide and Reference, Volume 1 and 2
Command Reference
DB2 Data Warehouse Center Administration Guide
Guide to GUI Tools for Administration and Development
SQL Reference, Volume 1 and 2
Replication Guide and Reference
DB2 Universal Database Certification Guide, 3rd Edition
by Jonathan Cook, Robert Harbus, and Tetsuya Shirai.
Published by Prentice Hall, 2000. ISBN 0-13-086755-1.
A Complete Guide to DB2 Universal Database
by Don Chamberlin.
Published by Morgan Kaufmann, 1998. ISBN 1-55860-482-0
Sybooks , Sybase documentation online
Reference Manual. Adaptive Server Enterprise 12.5
Transact-SQL User’s Guide. Adaptive Server Enterprise 12.5
System Administration Guide. Adaptive Server Enterprise 12.5
Open Client DB-Library/C Reference Manual
What’s New in Sybase Adaptive Server 11.5.X
New Functionality in Adaptive Server Enterprise 11.9.2
What’s New in Sybase Adaptive Server Enterprise 12.5
Sybase Adaptive Server 11
by John Kirkwood
Published by International Thomson Computer Press.
DB2 Migration Toolkit documentation
Conversion reference guide
Migration Toolkit online help
AllFusion Erwin Data Modeler Brochure , Fact Sheet and Product Announcement
by Computer Associates International, Inc.
Published at www3.ca.com/Solutions/Product.asp?ID=260.
Data Junction Technical Overview
Published at www.datajunction.com/products/dj_technical.html and
www.datajunction.com/products/matrix_formats_1.html
Embarcadero Technologies
Product overview at www.embarcadero.com/products/products.asp
SQL Conversion Workbench User's Guide
by Allyson Hart Benavides
Published by Mantech System Solutions Corporation, 1998. No. 986ASQL-CW
For more information
DB2 Family
www.ibm.com/software/data/db2
DB2 Product and Service Technical Library
www.ibm.com/software/data/db2/library
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
48
DB2 Maintenance
www.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/download.d2w/report
ftp://ftp.software.ibm.com/ps/products/db2/fixes/<language>/<platform>/<FixPak#>
Data Management education page
www.ibm.com/software/data/education.html
DB2 on the web page
www.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/db2web.d2w/report
DB2 Migration Web site
www.ibm.com/software/data/db2/migration
DB2 Developer Domain Library
www.ibm.com/software/data/developer
DB2 Today
www.ibm.com/software/data/db2today/
IBM PartnerWorld for Developers Program
www.developer.ibm.com/
Porting to DB2 UDB Version 8.1 from Sybase Adaptive Server Enterprise
49