Porting to DB2. Universal Database. Version 8.2
from Microsoft. SQL Server2000
By Amyris V. Rada
IBM Toronto Lab
arada@ca.ibm.com
(Reprinted courtesy of developerWorks DB2, at ibm.com /developerworks/db2/)
Trademarks
The following terms are trademarks or registered trademarks of the IBM Corporation in the United States and/or
other countries:
AIXInformix
AS/400MQSeries
DataJoinerMVS/ESA
DataPropagatorNet.Data
DataRefresherNUMA-
Q
DB2OS/400
DB2 ConnectOS/390
DB2 Universal DatabaseOS/
2
DB2 OLAP ServerRS/6000
Distributed Relational Database ArchitectureSystem/390
DRDAVM/ESA
IBMVSE/ESA
IMSz/OS
iSeries 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, Microsoft SQL Server, VisualBasic, Visual C++, Visual InterDev, Visual Studio, Windows, Windows
NT and Windows 2000 are trademarks or registered trademarks of Microsoft Corporation in the United States,
other countries or both.
UNIX is a registered trademark of the Open Group in the United States and other countries.
Other company, product, and service names may be trademarks or service marks of others.
. 2004 International Business Machines Corporation. All rights reserved.
Table of Contents
Introduction .........................................................................................
5
Why Port to DB2 UDB? ...............................................................................
6
IntegratedSupport for NativeEnvironments ..........................................................
6
Integrated System ManagementTools ...............................................................
6
Multiplatformtools for DB2 Universal Database ........................................................
7
Self-managing andResource Tuning ................................................................
7
DataReplication and Publishing ....................................................................
8
IntegratedSupport for Development Environments .....................................................
8
Integrated Web Access ..........................................................................
8
Tools for Building Web Applications .................................................................
9
Basic Warehousing Functionality ...................................................................
9
High Availability Support ..........................................................................
9
IBM Program for Assistance toDevelopers ......................................................... 10
DB2 Universal Database Product Family ............................................................ 11
DB2 PersonalEdition .......................................................................... 12
DB2 UDB Express Edition ...................................................................... 12
DB2 Workgroup Server Edition .................................................................. 12
DB2 Enterprise Server Edition ................................................................... 12
DB2 EveryplaceEdition ........................................................................ 12
DB2 Run-TimeClient .......................................................................... 13
DB2 Run-Time Client Lite ....................................................................... 13
DB2 Administration Client ....................................................................... 13
DB2Application Development Client .............................................................. 13
DB2 Products ................................................................................ 14
Data Types Conversion ............................................................................ 16
Administration Issues .............................................................................. 18
Environments ................................................................................ 18
Administration Server .......................................................................... 18
Federated SQL Server 2000Servers .............................................................. 19
Database Devices ............................................................................ 20
Log Files .................................................................................... 20
Database Security ............................................................................ 21
Backup and Restore ........................................................................... 22
Online Utility Tools ............................................................................ 23
Tasks ...................................................................................... 24
SystemStored Procedures and Administrative Tools ................................................. 24
Database Consistency ......................................................................... 26
Implementation Differences ........................................................................ 28
Isolation Levels ............................................................................... 28
Statement IsolationLevels ...................................................................... 28
Locking Mechanisms .......................................................................... 28
System Databases ............................................................................ 29
SQL StandardCompliance ...................................................................... 29
Tables ...................................................................................... 30
Temporary Tables ............................................................................ 30
Indexes ..................................................................................... 31
Views ...................................................................................... 32
IDENTITY Columns ........................................................................... 32
Computed Columns ........................................................................... 32
Constraints .................................................................................. 33
Scrollable Cursors ............................................................................ 33
ANSI JoinOperators ........................................................................... 33
Transactions ................................................................................. 34
Triggers ..................................................................................... 35
Stored Procedures ............................................................................ 36
User Defined Functions ........................................................................ 38
XML Support ................................................................................. 39
Global Variables .............................................................................. 40
Programming Interfaces ........................................................................... 41
DB2 Programming Interfaces Overview ............................................................ 41
DB2 Call Level Interface ........................................................................ 42
DB2 JavaEnablement ......................................................................... 42
Embedded SQL Differences ..................................................................... 44
Programming APIs Differences .................................................................. 45
Web Services Applications ...................................................................... 46
Terminology Map .................................................................................. 47
DB2 CLI vs. ODBC Function Map ................................................................... 48
Conversion Tools .................................................................................. 50
IBM Migration Toolkit (MTK) ..................................................................... 50
AllFusion ERwin Data Modeler ................................................................... 51
Embarcadero Technologies ..................................................................... 51
Resources and References ......................................................................... 53
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 (DB2 UDB) for UNIX. and
Windows. can help your organization meet these challenges.
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 DB2 Universal Database Enterprise Server Edition (ESE) means that it is flexible enough
to meet future growth.
A real database leader in several technologies, DB2 UDB provides the following capabilities:
.
Integrated support for complex data such as text documents, images, video and audio clips
.
Integrated Web access through native support for Java., Java Database Connectivity (JDBC), embedded
SQL for Java (SQLJ) and Net.Data
.
Integrated system management tools
.
Data replication services
.
High-availability disaster recovery (HADR)
This article introduces DB2 UDB products and their capabilities, discusses porting databases and applications, and
describes the most important aspects of porting applications from Microsoft SQL Server 2000 to DB2 UDB V8.2. 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 06,
2005, 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.2 from Microsoft SQL Server 2000
Why port to DB2 UDB?
DB2 UDB Version 8.2 delivers new features to address the ever-increasing demands and requirements on
customer’s data. The broadened autonomic (also known as self-managing) computing solutions offered report
immediate benefits to Database Administrators. These solutions automate and simplify potentially time-consuming
and complex database tasks. For example, this release simplifies performance optimization for specific workloads
by introducing the Design Advisor, a self-configuring tool that automates database design.
For application developers, DB2 UDB Version 8.2 delivers a significant amount of new capabilities as well as further
integration of DB2 tooling into the Microsoft .NET and WebSphere Studio./Java environments. This simplifies the
development and deployment of DB2 applications, which allows application developers to take advantage of the
openness, performance, and scalability of DB2, regardless of the back-end database or the application architecture.
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.
In addition to this, DB2 offers the following advantages:
.
Integrated support for native environments
.
Integrated system management tools and multiplatform tools
.
Self-managing and resource tuning capabiity
.
Data replication and publishing
.
Integrated support for development environments
.
Integrated Web access
.
Tools for building Web Applications
.
Basic 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 easier to integrate it to
the operating system.
Integrated system management tools
DB2 UDB 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 UDB Version 8 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:
.
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.
.
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.
.
The Configuration Assistant has options to configure both local and remote servers, including DB2
Connect. servers, or to create configuration templates.
.
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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
The DB2 advisors, wizards, and launchpads are also integrated into the Control Center. They assist in performing
administrative tasks step-by-step such as create databases, backup, resotore, design database, configure database
and setup high-availability disaster recovery (HADR).
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.
.
DB2 Table Editor for Multiplatforms offers direct DB2 database access to anyone for creating, reviewing,
or updating data. It requires a Java-enabled browser.
.
DB2 Web Query Tool for Multiplatforms provides easy access to enterprise data using complex
functionality.
.
DB2 Recovery Expert for Multiplatforms provides targeted and automated recovery of database assets,
even as systems remain online. 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.
.
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 online 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.
.
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.
.
DB2 Test Database Generator rapidly populates application and testing environments and simplifies
problem resolution. It can easily create test data from scratch or from existing data sources and maintains
referential integrity while extracting data sets from source databases. It can create complete or scaled down
copies of production databases while masking sensitive production data for use in a test environment.
Offered for DB2 on OS/390, z/OS, UNIX, and Windows.
Self-managing and resource tuning
DB2 UDB self-managing and resource tuning (SMART) database technology 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.
An example of this enhanced manageability is the Design advisor, which assists DBAs in making optimal and
comprehensive database design decisions. This self-configuring tool greatly simplifies the design process by using
workload, database, and hardware information to recommend specific performance-acceleration options for routine
design tasks.
Another example of a new time-saving feature is the ability to automate database maintenance activities such as
backup, table defragmentation (reorg), and table statistics gathering (runstats). DB2 UDB Version 8.2 provides the
ability to easily schedule these tasks to run automatically based on factors determined by the DBA, such as
available maintenance windows, percentage of log files used, and so on.
Other manageability enhancements in DB2 UDB Version 8.2 include self-healing features such as the Health Center
Recommendation Advisor and automated log file management; and self-tuning features for backup and restore
operations, as well as the ability to throttle backup operations and statistics collection. Also available is enhanced
sampling functionality, both row and page level, for faster, and potentially more frequent collection of statistics. This
improves query optimization in challenging business intelligence environments.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Data replication and publishing
DB2 UDB Version 8.2 provides two different solutions to replicate data from and to relational databases, that
ensures timely, reliable, and consistent data across an enterprise: SQL replication and Q replication. In SQL
replication, committed source changes are staged in relational tables before being replicated to target systems. In Q
replication, committed source changes are written in messages that are transported through WebSphere MQ
message queues to target systems. DB2 UDB Version 8.2 also provides a solution called event publishing for
converting committed source changes into messages in an XML format and publishing those messages to
applications such as message brokers.
DB2 Version 8 SQL replication includes four components:
.
Replication Center
.
Capture program and triggers
.
Apply program
.
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 SQL 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 SQL replication solution by supporting sources and targets that include the DB2
family, IMS., VSAM, Oracle, Sybase, Microsoft, Lotus Notes., and others.
.
DB2 DataPropagator. for OS/390, a feature of Version 6 of DB2 Universal Database Server for OS/390
.
DataPropagator Relational Version 5 Release 1 for AS/400
.
IBM DataPropagator NonRelational.
.
IBM DataJoiner.
.
Lotus NotesPump
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:
.
An easy-to-use interface for developing routines, such as stored procedures and user-defined functions
(UDFs)
.
A set of wizards makes it easy to perform development tasks
.
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
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
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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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.
Tools for building Web applications
DB2 UDB supports all the key Internet standards, making it an ideal database for use on the Web. It has in-memory
speed to facilitate Internet searches and complex text matching, combined with the scalability and availability
characteristics of a relational database. Because DB2 Universal Database supports WebSphere, Java, and XML
Extender, it makes it easy for you to deploy your e-business applications.
DB2 Universal Developer's Edition has several tools that provide Web enablement support. WebSphere Studio
Application Developer is an integrated development environment (IDE) that enables you to build, test, and deploy
Java applications to a WebSphere Application Server and DB2 UDB. WebSphere Studio is a suite of tools that
brings all aspects of Web site development into a common interface. WebSphere Application Server Advanced
Edition (single-server) provides a robust deployment environment for e-business applications. Its components let
you build and deploy personalized, dynamic Web content quickly and easily.
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.
Starting in the Data Warehouse Center, in DB2 UDB Version 8.2, the warehouse control database must be a UTF-8
(Unicode Transformation Format, or Unicode) database. This requirement provides expanded language support for
the Data Warehouse Center. The Warehouse Control Database Management tool can be used to migrate the
metadata from a specified database into a new Unicode database.
The DB2 Warehouse Manager product completes the warehousing capability provided by the Data Warehouse
Center.
High Availability support
In order to ensure continuous availability of data, DB2 UDB offers several features such as high-availability disaster
recovery, suspended I/O, split mirror image, dual logging, parallel recovery, backup from split image, and
incremental and delta backups.
DB2 high-availability disaster recovery (HADR) is a data replication feature that provides a high-availability solution
for both partial and complete site failures. HADR protects against data loss by replicating data changes from a
source database, called the primary, to a target database, called the standby. With HADR, the standby database
can take over in seconds and clients that were using the old primary database can be redirected to the standby
database by using automatic client reroute or retry logic in the application. Since HADR uses TCP/IP for
communication between the primary and standby databases, the databases can be situated in different locations.
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. The 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:
.
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.
.
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.
.
Online index reorganization -Users can read and update a table and its existing indexes during an index
reorganization using the new REORG INDEXES command.
.
Configurable online configuration parameters - Over 50 configuration parameters can now be set online,
and these changes take effect immediately.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
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.
.
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. This support consists of configuration
instructions and details for implementation when working with DB2 servers. DB2 provides support for High
Availability Cluster Multiprocessing Enhanced Scalability (HACMP/ES) on AIX, Microsoft Cluster Server on Windows
operating systems, Multi-Computer/ServiceGuard on Hewlett-Packard, and Sun Cluster or VERITAS Cluster Server
on the Solaris Operating Environment.
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/guide/membership.html.
Benefits offered by this program include the following:
.
Marketing and sales support
Marketing education, Business Partner opportunities, co-marketing promotion, and other opportunities
.
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
.
Technical support
Access to cross-platform technical enablement services at the worldwide Solution Partnership Centers, and
industry-leading technical support for developers
.
Incentives
Software discounts, hardware discounts and leases, and discounts on business services such as express
mail, pagers, insurance, and car rentals
.
Financing
Flexible financing to help you and your customers acquire hardware, software, and services with affordable
monthly payments
.
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.2 from Microsoft SQL Server 2000
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
and are available for both 32-bit and 64-bit operating environment modes.
There are four types of DB2 clients: Run-Time Client, DB2 Run-Time Client Lite, Administration Client and
Application Development Client. DB2 UDB Version 8 clients support DB2 UDB Version 8 servers; they can also
connect to DB2 UDB Version 7 servers only if the DB2 server is running Distributed Relational Database
Architecture. (DRDA.)-AS. DB2 UDB Version 6 and Version 7 clients can connect to DB2 UDB Version 8 servers
with limited capacity. DB2 UDB Version 8 clients cannot connect to DB2 Connect Version 7 servers.
DB2 Version 8 Clients are available for the following platforms: AIX, HP-UX, Linux, Solaris operating environment,
Windows NT Version 4, Windows 98, Windows 2000, Windows ME, Windows XP (32-bit and 64-bit editions), and
Windows Server 2003 (32-bit and 64-bit editions). Support on other platforms may be available through earlier
versions of DB2 Clients.
The DB2 Run-Time Client Lite is a new installable component in DB2 UDB Version 8.2 that facilitates access to DB2
servers from Windows-based applications.
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 Product Family
The DB2 Server and Clients products and components include:
.
DB2 Personal Edition
.
DB2 Workgroup Server Edition
.
DB2 Enterprise Server Edition
.
DB2 Everyplace Edition
.
DB2 Run-Time Client
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
DB2 Run-Time Client Lite
.
DB2 Administration Client
.
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
documentation.
DB2 Personal Edition runs on Linux and Windows ME, Windows NT Version 4, Windows 2000 (32–bit), Windows
XP (32–bit or 64–bit), and Windows Server 2003 (32–bit or 64–bit).
DB2 UDB Express Edition
DB2 UDB Express Edition (DB2 Express) is a full-function DB2 relational database, which provides very attractive
entry-level pricing for the Small and Medium Business (SMB) market. It is offered in both per-user or per-processor
(up to two CPUs) pricing models to provide choices to match SMB customer needs. It comes with simplified
packaging, and is easy to transparently install within an application. It is fully compatible with, scalable to, and has all
the autonomic manageability features of its higher-priced family of offerings. DB2 Express can be deployed on
Windows NT Version 4, Windows 2000, Windows XP, and Windows Server 2003. Only support for 32-bit platforms
is provided; support for 64-bit platforms will be available at a later date.
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 (Intel, iSeries., pSeries), Solaris Operating
Environment, Windows NT Version 4, Windows 2000, Windows XP, and Windows Server 2003 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 (Intel, iSeries, pSeries, zSeries), Solaris Operating
Environment, Windows NT Version 4, Windows 2000, and Windows Server 2003. 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 database 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 back-end data sources. The platforms supported are AIX, Solaris Operating
Environment, 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, Linux and Windows, Cloudscape, Lotus. Domino
Server, Oracle, Microsoft SQL Server, and Sybase data sources.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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 Run-Time Client Lite
The DB2 Run-Time Client Lite (DB2 RTCL) is designed to be redistributable by independent software vendors
(ISVs) and to be used for application distribution in mass deployment scenarios typical of large enterprises. Similar
to the DB2 Run-Time Client component, DB2 RTCL provides application interfaces (CLI, ODBC, OLE DB, .NET
Data provider, and JDBC) and the network libraries that are required for DB2 applications to run. It is available only
on Windows operating systems and supports only the TCP/IP and Named Pipes communication protocols.
The main features of the DB2 RTCL are:
.
A significantly smaller disk footprint
.
Shipped as a single executable making it easy to redistribute and deploy
.
Windows Installer Merge Module (.msm file) is available, which simplifies the integration of the DB2 RTCL
code within a larger application
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 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:
.
Precompilers for C/C++, COBOL, and Fortran, providing the language is supported for that platform.
.
Embedded SQL application support, including programming libraries, include files and code samples.
.
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.
.
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.
.
Java Development Kit (JDK) or equivalent, is shipped with DB2 for all supported operating systems. JDK
1.3.1 and Java Runtime Environment (JRE) 1.3.1 from IBM for AIX, IBM Developer Kit and Runtime
Environment (Java 2 Technology Edition) Version 1.3.1 and 1.4.1 Service Release 1 (32-bit version) for
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Linux and Windows, HP-UX 32-bit: Software Developer's Kit and Runtime Environment 1.4.2.01 for HP-UX
11.0 and 11i PA-RISC from Hewlett-Packard, and Solaris 32-bit: JDK Versions 1.3.1 and 1.4.2 and Solaris
64-bit: JDK Version 1.4.2 for Solaris from Sun Microsystems. When you install the DB2 ADC on AIX and
Linux, the Java Development Kit is installed if an earlier version of the Java Development Kit is not
detected. When you install the DB2 ADC on Windows, the Java Development Kit is always installed. For
complete details see section DB2 supported development software in the DB2 Application Development
Guide: Building and Running Applications.
.
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.
.
Object Linking and Embedding Database (OLE DB) table functions on Windows operating systems.
.
C# and Visual Basic .NET applications and CLR .NET routines on Windows operating systems.
.
DB2 Development Center, a graphical application that supports the rapid development of routines (stored
procedures and user-defined functions), and structured types. The Development Center provides a single
development environment that supports the entire DB2 family ranging from the workstation to z/OS. You
can launch the Development Center as a stand-alone application or from a DB2 Universal Database center,
such as the Control Center, 7 the Command Editor, or the Task Center. The Development Center is
implemented with Java, and all database connections are managed by using a Java Database Connectivity
(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.
.
Interactive SQL through the Command Editor or Command Line Processor (CLP) to prototype SQL
statements or to perform ad hoc queries against the database.
.
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 DB2
application development Web site: http://www.ibm.com/software/data/db2/udb/ad/v8/bldg/prepapi.pdf.
.
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.
DB2 products
Other important DB2 products are:
.
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.
.
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.
.
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 (32-bit), HP-UX
(32-bit or 64-bit on a PA-RISC platform), Solaris operating environment (32-bit), Linux (Intel 32-bit), and
Windows 2000 (32-bit).
.
DB2 Net Search Extender, which combines performance with the search interface and functionality of DB2
Text Information Extender. DB2 Net Search Extender V8 adds rich full-text search to enhance database
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
applications with the information users need, and provides query performance and scalability by integrating
caching and optimization technologies. It is most appropriate for high end e-business applications that are
database intensive, since it is designed to work closely with DB2 Universal Database applications. This
product is available for AIX, HP-UX, Solaris operating environment, Linux (Intel, Solaris), Windows NT,
Windows 2000, Windows Server 2003, and Windows XP operating systems.
.
DB2 Information Integrator provides the foundation for a strategic information integration framework that
helps customers speed time to market for new applications, get more value and insight from existing assets,
and control IT costs. Designed to meet a diverse range of data integration requirements for business
intelligence and business integration, it provides a range of capabilities: enterprise search, data federation,
data transformation, data placement (caching and replication), and data event publishing. DB2 Information
and Integration offerings are available on AIX, HP-UX, Solaris operating environment, Linux for Intel, and
Windows operating environment.
.
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. DB2 OLAP Server Spreadsheet Services provides a highly intuitive, user-friendly, thin-client
spreadsheet environment for OLAP analysis with DB2 OLAP Server through integration into Excel. Other
components are DB2 OLAP Server Administration Services and DB2 OLAP Server Deployment Services.
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, Solaris operating
environment, Windows NT 4.0, Windows 2000, and Windows XP.
.
DB2 Warehouse Manager, a infrastructure 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 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.
.
DB2 Universal Database Data Warehouse Editions provide a comprehensive BI platform with everything
needed to deploy, and to build next generation business intelligence solutions. There are two editions:
Standard and Enterprise. It includes DB2, federated data access, data partitioning, integrated OLAP,
advanced data mining, enhanced ETL using DB2 Warehouse Manager, workload management, and
provides light BI for the desktop. The product components vary depending on the type of edition.
.
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, DB2 Test Database
Generator, and DB2 High Performance Unload for Multiplatforms.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Data types conversion
Most Microsoft SQL Server 2000 data types can be mapped to DB2. The following table compares all data types:
MS SQL Data
Type Name
Range of Values Bytes ofStorage
DB2 UDB Data
Type Name
Range of Values Bytes ofStorage
tinyint 0 to 255 1 smallint -32768 to 32767 2
smallint -32768 to 32,767 2 smallint -32768 to 32767 2
int
integer
-231 ( -2,147,483,648) to 231 -1
(2,147483,647)
4 integer
int
-231 to 231 - 1 4
bigint 9223372036854775808 to
+9223372036854775807
8 bigint 9223372036854775808 to
+9223372036854775807
8
numeric(p,s)
dec[imal](p,s)
-1038 - 1 to 1038 -1 2-17 num[eric](p,s)
dec[imal](p,s)
-1031+1 to 1031-1
( p+s <=31)
(p/2) + 1
float(p) 0 or from -2.23E-308 to
-1.79E+308, or from
2.23E-308 to 1.79E+308
4 or 8 1
(15 digits)
float(p) 4 or 8
real 0 or from 1.18E-38 to
3.40E+38, or from -1.18E-38
to -3.40E+38
4
(7 digits)
real 0 or from -3.402E+38 to
-1.175E-37, or from 1.175E-37
to 3.402E+38
4
( 8 digits)
double precision synonym for float double precision 0 or from -1.79769E+308 to
-2.225E-307, or from
2.225E-307 to1.79769E+308
8
(16 digits)
smallmoney -214,748.3648 to
214,748.3647
4 numeric(10,4) 6
money -922,337,203,685,477.5808 to
922,337,203,685,477.5807
8 numeric(19,4) 11
smalldatetime January 1, 1900 to June 6,
2079
42 timestamp 7 January 1, 0001 to December
31, 9999
10 internal,
26 external
datetime January 1, 1753 to December
31, 9999
83 timestamp January 1, 0001 to December
31, 9999
10 internal,
26 external
timestamp varbinary(8) 84 character(8) FOR
BITDATA
date8 year: 0001 to 9999,
month: 1 to 12,day: 1 to 31
4 internal,
10 external
time9 hour: 0 to 24,
minutes/seconds: 0 to 59
3 internal, 8
external
char[acter](n) 1 to 8000 n char[acter](n) 1 to 254 n
varchar(n)
char[acter]
varying
1 to 8000 entry length varchar(n)
character varying
1 to 32,67210 entry length
long varchar11 1 to 32,700 entry length
nchar(n) 1 to 4000 2 * n graphic(n) 1 to 127 2 * n
nvarchar(n) 1 to 4000 entry length vargraphic(n) 1 to 16,33610 2 * entry
length
long vargraphic11 1 to 16,350 2 * entry
length
ntext 230 - 1 (1,073,741,823) multiples of
a page
DBCLOB(n) 1 to 1 073 741 823 2 * entry
length
binary(n)1 to 8000 n character(n) FOR
BIT DATA
1 to 254 n
varbinary(n)
binary varying
1 to 8000 entry length varchar(n) FOR
BIT DATA
1 to 32,672 entry length
bit 0 or 1 1 character(1) FOR
BITDATA12
0 or 1 1
text 231 - 1 (2,147,483,647) multiples of
a page 6
CLOB(n) 1 to 231 entry length
image 231 - 1 (2,147,483,647) multiples
of a page6
BLOB(n) 1 to 231 entry length
uniqueidentifier N/A character(16) FOR
BITDATA13
N/A 13
Note: Some DB2 data types that are not available in Microsoft SQL Server have been included in the table.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
1
float(p) storage is 4 bytes if p < 8; 8 bytes if 8 <= p < 16. If p < 8, float is a synonym for real.
2
datetime values are accurate to 1/300 of a second. Storage size is 8 bytes: 4 bytes for the number of days before or after
January 1, 1900, and 4 bytes for the number of milliseconds after midnight. Negative values represent dates prior to the base
date.
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
Values in the timestamp columns indicate the sequence of Microsoft SQL Server activity on the row. They are not datetime
data. Storage is as binary(8) varbinary(8) data. A table can have only one timestamp column.
6
Page size is 2K. The column size is 0 until it is initialized.
7
The DB2 UDB 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 = 0001 to 9999, month = 1 to 12, and day = 1
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.
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. A check constraint must be declared to restrict the character values to just 0 or 1. The smallint datatype can also
be used to map this type.
13
uniqueidentifier columns can only be initialized using the newid function or converting from a string constant in the following
format "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" (each x is a hexadecimal digit in the range 0-9 or a-f). The function newid is
non-deterministic and returns a unique value for column data type uniqueidentifier. DB2 provides an equivalent function to
newid, called generate_unique function, which returns a bit data character string 13 bytes long that is unique compared to
any other execution of the same function. This function is defined as not-deterministic and the result of the function is a
unique value that includes the internal form of the Universal Time, Coordinated (UTC) and the partition number where the
function was processed. The length of this column can be reduced from 16 to 13 if the generate_unique function is used to
generate its value.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Administration issues
This section describes administrative features available in Microsoft (MS) SQL Server 2000 and in DB2 UDB
Version 8.2 for UNIX, Linux, and Windows.
Environments
Microsoft SQL Server 2000 supports the installation of multiple instances on the same computer. Each instance
runs independently from all others and has its own set of system and user databases. Only one instance of SQL
Server Version 6.5 or 7.0 can run at the same time as multiple instances of SQL Server 2000. When multiple
instances are running simultaneously, the amount of memory is dynamically allocated for each specific instance by
an algorithm. The relative workload of each instance is also a consideration. Applications must connect to an
instance before they can connect to a database, similar to remote instance connections.
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
Microsoft SQL Server 2000 provides a set of services that allow administrators to schedule the automatic execution
of repetitive tasks. The server automatically acquires system resources such as memory and disk space when
needed, and frees the resources when they are no longer required.
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. The DAS assists the Control Center, Development Center, Replication
Center, and Configuration Assistant when working on the following administration tasks:
.
Enabling remote administration of DB2 servers.
.
Providing the facility for job management, including the ability to schedule.
.
Running of both DB2 and operating system user-defined command scripts.
.
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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
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.
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.
Federated capability
A federation of servers running Microsoft SQL Server 2000 can be grouped to implement database partitioning and
scalability. However, updateable distributed partitioned views are required to partition data horizontally across a
group of servers. Each database server is autonomous.
DB2 ESE supports database partitioning. In an MPP or cluster configuration, DB2 ESE distributes data across
multiple partitions, or subsets, of the database, which reside on multiples Single Partition (SP) or SMP servers. A
unique partition map allows DB2 to manage the distribution and redistribution of data as required. Access plans are
automatically created for parallel execution with standard SQL statements, such as READ, INSERT, UPDATE, and
DELETE. Data scans, joins, sorts, load balancing, table reorganization, data load, index creation, indexed access,
backup, and restore are all performed on all nodes simultaneously. DB2’s "shared nothing" architecture allows
parallel query support with minimal data transfer across nodes. The number of partitions has little impact on partition
traffic between partitions; performance scales in a near-linear fashion when you add nodes to your MPP or add
SMPs to a cluster. Because the data partitioning and parallel execution are handled by the DB2 UDB ESE server,
access to the database is completely transparent to the application and no additional changes are required to
support partitioning.
It is important to note that a federated server is a different concept in DB2 from in Microsoft SQL Server. 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.
Figure 3. Components of a Federated System
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Data sources as shown in the previous figure 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:
.
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
Microsoft SQL Server 2000 uses operating system files and filegroups to store databases and transaction logs; it no
longer uses logical devices. A database consists of two or more files: one to store data and another to store
transaction log records. These database files can automatically grow at a specified increment until they reach a
defined limit or while free space is available on the disk. A set of database files can be grouped in a filegroup, and
database objects can be placed on a specified filegroup.
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 4. 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
Microsoft SQL Server 2000 transaction log consists of one or more log files, each containing a contiguous set of log
records. The log is no longer represented as a system table (Version 6.5). Each physical log file segments into a
number of virtual log files. The size or number of virtual log files is determined dynamically and cannot be
configured. The transaction log wraps around log files, and files are extended if filegrowth is specified until maxsize
is reached or free space is available on disk when the end of the logical log reaches the start of the logical log.
Log truncation frees space for new log records by deleting old log records. Log truncation occurs after executing
BACKUP LOG statement, and every time a checkpoint is issued.
Disk mirror operations are no longer available on Microsoft SQL Server 2000. Hardware devices that provide
mirroring capabilities, such as RAID, are recommended.
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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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.
The logarchmeth1 and logarchmeth2 database configuration parameters are set to OFF. 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 using archived logs. Archive logging can be enabled using the logarchmeth1 database configuration
parameter, once 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.
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 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.
When creating an online backup image, the log files required to restore and recover a database can included in the
image using the INCLUDE LOGS option of the BACKUP DATABASE command. When this option is specified, the
backup utility will truncate the currently active log file and copy the necessary set of log extents into the backup
image.
Database security
Microsoft SQL Server 2000 authentication is determined at installation time. The possible selections are Windows or
SQL Server authentication. The former uses Microsoft Windows NT or Windows 2000 security facility for user
validation; the latter is provided for backward compatibility and is required on Windows 95 and 98 where no security
facility is present in the operating system.
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 (AIX, Solaris Operating Environment,
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.
New in DB2 UDB Version 8.2 custom authentication and group management mechanisms is possible in the form of
loadable plugins that DB2 will load and access to perform user authentication. This provides new alternatives to the
authentication methods currently supported by DB2.
Microsoft SQL Server includes several predefined roles with implied permissions. The two types of predefined roles
are fixed server and fixed database. Microsoft Windows NT and Windows 2000 groups can be used in much the
same way as roles.
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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
authorization, that is, the required privilege or authority. The hierarchy between authorities and privileges is shown in
the following figure:
Figure 5. DB2 Authorities
In DB2, the following authorities exist:
.
System administration (SYSADM)
.
System control (SYSCTRL)
.
System maintenance (SYSMAINT)
.
System monitor authority (SYSMON)
.
Database administration (DBADM)
.
LOAD
SYSADM, SYSCTRL, SYSMAINT, and SYSMON 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: BINDADD, CONNECT, CREATETAB, and
IMPLICIT_SCHEMA. All privileges including LOAD can be managed using the GRANT and REVOKE SQL
commands. The LOAD authority allows users who are not SYSADM or DBADM to load data into a table. Microsoft
SQL Server roles can be mapped to DB2 authorities or groups to which certain database authorities have been
granted.
Microsoft SQL Server 2000 uses permissions to restrict access to objects and commands based on a user's identity
or group membership. Database permissions can be assigned directly to Windows NT and Windows 2000 users.
The commands grant and revoke can authorize or prevent users to access views and execute stored procedures or
Transact-SQL statements. Most Microsoft SQL Server permissions can be mapped to DB2 privileges.
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. Some privileges in DB2 are not available in Microsoft SQL Server,
such as package and schema privileges.
Microsoft SQL Server 2000 has a fixed server role called sysadmin with permissions to perform any activity in SQL
Server; user accounts can become members of the sysadmin role. The built-in login system administrator (SA) is
only provided for backward compatibility. 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.
Backup and restore
Microsoft SQL Server 2000 backup and restore commands can be used to back up and restore databases,
transaction logs, database files, and filegroups. The backup operation requires a permanent or temporary backup
device. The restore operation permits relocation of database files. Online backup, differential backup, and
point-in-time recovery are available. A set of backup history tables is maintained in the msdb database.
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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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.
Two types of incremental backups are available:
.
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.
Other backup and recovery enhancements available in DB2 UDB Version 8 include:
.
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.
The new recover database command in DB2 UDB V8.2 combines the functionality of the restore database and
rollforward commands. A database can be recovered to a specific point-in-time and there is no need to indicate
which database backup image must be restored or which log files are required to reach the specified point-in-time.
The recover database command also supports recovery operations to the end of the log files.
DB2 UDB Version 8.2 will now automatically choose the number of buffers, the buffer size, and the parallelism
settings for both backup and restore operations. The values chosen are based on the amount of memory available,
the number of processors available, and the database configuration. The objective is to minimize the amount of time
required for backup and restore operations to complete.
Online utility tools
Microsoft SQL Server 2000 configuration options can be managed using SQL Server Enterprise Manager or the
sp_configure system stored procedure. However, not all options can be configured using the SQL Server Enterprise
Manager. Some configuration options require you 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, the Configuration advisor, or the commands update
database configuration and update database manager configuration.
The bcp utility copies data to a SQL Server 2000 instance from a data file with a format specified by the user. This
utility uses row-level locks unless the TABLOCK hint is specified or table lock on bulk load option is enabled.
The load command in DB2 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.
Microsoft SQL Server 2000 does not provide a command or system stored procedure to defragment just a specific
table. Data is also reorganized when indexes are defragmented.
DB2 Version 8 now provides two methods of reorganizing tables 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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
The Microsoft SQL Server 2000 command DBCC INDEXDEFRAG defragments clustered and secondary indexes
on a table or view. This command reorders the leaf level pages only, and it matches the physical order to the
left-to-right logical order of the leaf nodes. It is an online operation and it is not recommended when the indexes are
heavily fragmented.
During Online index reorganization in DB2 V8, the user has the ability to read and update a table and its existing
indexes using the new reorg indexes command. 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.
Microsoft SQL Server 2000 allows statistical information regarding the distribution of values in a column to be
created. This statistical information can be used by the query processor to determine the optimal strategy for
evaluating a query.
When you create an index, Microsoft SQL Server 2000 automatically stores statistics about the distribution of values
in the indexed columns. Based on these statistics, the query optimizer estimates the cost of using the index for a
query. The AUTO_CREATE_STATISTICS database option is set to ON by default, which will automatically creates
statistics for columns without indexes that are used in a predicate. As the data is updated, Microsoft SQL Server will
automatically update statistics periodically using data sampling. The commands create statistics and update
statistics allows control on the amount of data sample, frequency of updates, and other specifics.
Table statistics are used by the DB2 query optimizer in selecting the best access plan for any given query, so it is
important that statistics remain current to accurately reflect the state of a table at any given time. As the activity
against a table increases, so should the frequency of statistics collection. Starting in Version 8.2, the RUNSTATS
utility provides the option to collect statistics on a sample of the data in the table by using the TABLESAMPLE
option. This feature increases the efficiency of statistics collection since sampling uses only a subset of the data, at
the same time the sampling methods ensure a high level of accuracy. Throttling of the RUNSTATS utility limits the
amount of resources consumed by the utility, based on the current level of database activity. When database activity
is low, the utility runs more aggressively; when database activity increases, the resources allocated to executing
RUNSTATS are reduced.
Tasks
The Microsoft SQL Server 2000 Agent services provide automation for alerts and administration tasks. Jobs can be
created using Microsoft Enterprise Manager and can be written in operating system commands, Transact-SQL
statements, Microsoft ActiveX Script, or replication tasks.
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
Microsoft SQL Server 2000 supplies system stored procedures to update and generate reports from system tables
and to perform administrative tasks. Several system stored procedure such as sp_help, are provided for backward
compatibility or to return new syntax information. These procedures are stored in the master database and are
owned by the System Administrator (SA). Microsoft SQL Server 2000 also offers SQL-DMO, SQL-NS, DTS, and
Replication Component APIs which are composed of OLE Automation objects that encapsulate either DDL or
system stored procedures.
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.
Microsoft Management Console (MMC) is a new user interface and framework for the management of Microsoft
server products. SQL Server Enterprise Manager is a graphical application to configure and manage Microsoft SQL
Servers and database objects, and it is the Microsoft SQL Server MMC snap-in. It runs on Windows 95, Windows
98, Windows NT, and Windows 2000.
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:
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
Add DB2 UDB systems, federated systems, DB2 UDB for z/OS and OS/390 systems, IMSysplexes,
instances, databases, and database objects to the object tree
.
Manage database objects: Create, alter, and drop databases, table spaces, tables, views, indexes, triggers,
and schemas
.
Manage data: Load, import, export, gather statistics, and reorganize data
.
Perform preventive maintenance by backing up and restoring databases or table spaces
.
Configure and tune instances and databases
.
Manage database connections, such as DB2 Connect servers and subsystems
.
Manage IMS systems, DB2 UDB for z/OS and OS/390 subsystems
.
Manage applications
.
Analyze queries using Visual Explain to look at access plans
.
Launch other tools such as the Command Editor and the Health Center
.
Generate SQL statements used to define database objects
.
Convert Control Center actions into scripts
The CC is available in three different views:
.
Basic. This view provides core DB2 UDB functionality, which includes the essential objects, such as
databases, tables, and stored procedures.
.
Advanced. This view displays all objects and actions available in the Control Center. This is the view that
you should select if you are working in an enterprise environment and want to connect to DB2 for z/OS or
IMS.
.
Custom. This view gives you the ability to tailor the object tree and the object actions to your specific needs.
The following figure illustrates the Control Center Basic View:
Figure 6. Control Center Main Window
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
The following DB2 tools are available from the Control Center toolbar:
.
Command Center, to issue DB2 database commands, SQL statements, and operating system commands
and to view their results
.
Task Center, to create and run operating system level and DB2 command scripts
.
Journal, to run, schedule, and delete jobs and view the recovery history log and messages log
.
License Center, to configure and to display the status of the DB2 product licenses
.
Replication Center new in DB2 Version 8, to design set up and manage a replication environment
.
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
.
Data Warehouse Center, to manage Data Warehouse objects
.
Information Catalog Center, to manage business metadata
.
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.
.
Development Center, to develop stored procedures, user-defined functions, and structured types
.
Information Center, to search for help on tasks, commands, and information in the DB2 library, and also to
update local documentation
The DB2 advisors, 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), back
up databases, create databases, create table spaces, create tables, bulk load data, design databases, monitor
workload performance, configure database manager and database parameters to improve performance, restore
databases, configure database logging, set up Activity Monitor, and set up HADR.
Database consistency
Microsoft SQL Server 2000 provides dbcc statements to check database consistency. These statements perform
physical and logical consistency checks on a database and can fix certain problems. In earlier versions, it was
recommended that dbcc statements be run prior to a backup. In Version 7, this is no longer recommended prior to
doing a backup, but is still advised after a system failure.
The dbcc statements are grouped into four categories: maintenance, miscellaneous, status, and validation.
Maintenance tasks are performed on a database, index, or filegroup. Miscellaneous tasks include enabling row-level
locking or removing a dynamic-link library (DLL) from memory. Status checks are performed on system information,
such as current statistics, trace flags, user options, and last statement sent form a client. Validation operations are
performed on a database, table, index, catalog, filegroup, system tables, or allocation of database pages. Also for
database maintenance, the sqlmaint utility performs DBCC checks, backs up a database and its transaction log,
updates statistics, and rebuilds indexes on one or more databases.
In DB2 Version 8 the command INSPECT was introduced in order to inspect the 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. It will write out unformatted
inspection data results to the results file specified, and if there are no errors the file is deleted, otherwise it remains
in the diagnostic data directory path. The inspection result data will require to be formatted out with the utility
db2inspf.
Another tool in DB2 UDB, db2dart can also be used to verify that the architectural integrity of a database is correct. It
is not 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:
.
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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
The use of the INSPECT command is recommended over the db2dart tool, since the INSPECT command can be
run online and does not impede server availavility. Sometimes, DB2 support will direct customers to use db2dart to
remedy very specific problems, working together with support is the best way to take advantage of this powerful tool.
Although Microsoft SQL Server dbcc statements and DB2 UDB tools produce different output information, the
following table only attemps to map some of the functionality between dbcc commands and db2dart commands:
Microsoft SQL Server dbcc DB2 UDB db2dart
dbcc checkdb (database-name) db2dart database-name /DB 1
dbcc checktable (table-name) db2dart database-name /T /TSI tablespace-id /TN table-name
dbcc checkfilegroup (filegroup-name | filegroup-id)2 db2dart database-name /TS /TSI tablespace-id 3
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
2 dbcc checkfilegroup checks the integrity of the filegroup and all its tables
3 db2dart /TS checks the table space and all its tables. Of the additional options, /TSC checks only the constructs of atable space, and /TSF checks all table space files and containers
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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Implementation differences
This section describes implementation features available in Microsoft SQL Server 2000 and DB2 UDB Version 8.2
for UNIX, Linux, and Windows.
Isolation levels
Microsoft SQL Server 2000 supports four isolation levels: Read Uncommitted, Read Committed, Repeatable Read,
and Serializable. If the level is set to Read Uncommitted, dirty reads, nonrepeatable reads, and phantom values can
occur. If the level is set to Read Committed, dirty reads cannot occur but nonrepeatable reads and phantom values
still occur. At the Repeatable Read level, nonrepeatable reads cannot occur but phantom values still occur.
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.
Microsoft SQL Server DB2 UDB
Read Uncommitted Uncommitted Read
Read Committed (default) Cursor Stability (default)
Repeatable Read Read Stability
Serializable 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.
DB2 permits the deferral of row locks for Cursor Stability or Read Stability isolation scans in some situations until a
record is known to satisfy the predicates of a query in order to improve concurrency. By default, when row-locking is
performed during a table or index scan, DB2 locks each row that is scanned before determining whether the row
qualifies for the query or not. It may be possible to defer row locking until after it is determined that a row qualifies for
a query in order to improve the concurrency of scans.
By enabling the DB2_EVALUNCOMMITTED registry variable, we can take advantage of lock deferral. Once this
variable is enabled, predicate evaluation can occur on uncommitted data. This means that a row that contains an
uncommitted update may not satisfy the query, whereas if the predicate evaluation waited until the updated
transaction completed, the row may satisfy the query. Additionally, uncommitted deleted rows are skipped during
table scans. DB2 will skip deleted keys in type-2 4 index scans if the DB2_SKIPDELETED registry variable is also
enabled.
Statement isolation levels
In Microsoft SQL Server 2000, the with keyword in the from clause for a select statement indicates table hints. The
table hints are group by lock granularity, and isolation level. The values that can be specified as isolation level hints
are READCOMMITTED, READUNCOMMITTED, REPEATABLEREAD, and SERIALIZABLE.
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_PAYFROM employeeORDER BY TOTAL_PAYWITH RS
Locking mechanisms
Microsoft SQL Server 2000 has three levels of locking: row, page, and table locks. The default is row-level locking.
Lock escalation is automatically performed from row locks and page locks into table locks when a transaction
exceeds its escalation threshold. Lock escalation thresholds are determined dynamically by Microsoft SQL Server
and cannot be configured.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
The locks option can be set to the maximum number of available locks, limiting the amount of memory Microsoft
SQL Server uses for locks. The default setting is 0, which allows the server to dynamically allocate memory for locks
(up to 40% of the total memory allocated by the server).
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.
System databases
The master database controls the operation of Microsoft SQL Server 2000 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.
The msdb database is used by Microsoft SQL Server 2000, SQL Server Enterprise Manager, and SQL Server
Agent to store data for alerts, scheduled tasks, and backup and restore history information. 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 Healt Center can be used to display and work with current alerts.
The Microsoft SQL Server 2000 distribution database is used for replication and stores data including transactions,
snapshot jobs, synchronization status, and replication history information. All servers that are configured for remote
distribution or combined Publisher/Distributor have a distribution database.
DB2 replication components use control tables to communicate with each other and to manage replication requests,
such as defining and managing replication sources and targets, capturing changes, replicating changes, and
tracking how many changes are replicated and how many remain to be done. The control tables for the Capture
program reside on the source database, and the control tables for the Apply program reside on the target
databases. The Replication Alert Monitor reads all the control tables and notifies contacts when the alert condition
occurs.
The Microsoft SQL Server 2000 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. Users that are already created can have access to all databases using group membership or trusted
connections.
The Microsoft SQL Server 2000 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).
Microsoft SQL Server 2000 provides two sample databases called pubs and Northwind, which can be optionally
installed. 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.
SQL standard compliance
Microsoft SQL Server 2000 features comply with the entry level of ANSI SQL92 standard and with the Federal
Information Processing Standards (FIPS 127-2) as established by the US National Institute of Standards and
Technology (NIST). The commands SET FIPS_FLAGGER and SET ANSI_DEFAULTS change the level of
compliance and SQL-92 behavior. The length of Identifiers is limited to 128 characters.
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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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. The
maximum SQL statement lenght is 2MB,
Tables
The full specification of a table name in the Microsoft SQL Server create table command includes database name,
owner and table name. The database name is optional and defaults to the current database name. However, if a
database name is present the database must exists and the current connection login must be associated with an
existing user ID on such database. This command can be used to also create global and local temporary tables.
The ON and TEXTIMAGE_ON clauses specify a file_group where the table or the large columns (text, ntext, and
image) are going to be stored.
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 Microsoft SQL Server 2000 and
DB2 UDB:
Description Microsoft SQL Server DB2 UDB
Table space/database file page size 8K 4K, 8K, 16K, 32K
Maximum length of a row 8,060 bytes 32,677 bytes 1
Maximum number of columns in a table 1,024 1,012 2
Maximum number of indexes in a table 249 non-clustered
1 clustered
32,767 or storage
Maximum number of tables in a database Limited by number of database
objects 3
65,534 4
Longest index key 900 bytes 1,024 bytes
Maximum number of constraints on a table 250 Unique keys
253 Foreign Keys
storage
Maximum number of columns in a view 1,024 5,000 5
Maximum number of tables referenced in a
select or view
256 storage
Maximum number of columns in an ORDER
BY clause
Limited to total length of
8060 bytes
1,012 2
Maximum total length of columns in an
ORDER BY clause
8,060 bytes 32,677 bytes 1
1
This value is based on a table space page size of 32K, and varies according to the page size.
2
This value is based on a table space page size of either 8K, 16K, or 32K.
3
The total number of database objects can not exceed 2,147,483,647.
4
This limit is for SMS table spaces only
5
This maximum can be achieved using a join in the CREATE VIEW statement.
Temporary tables
Microsoft SQL Server 2000 supports temporary tables, which are stored in the tempdb database. There are two
types of temporary tables, local and global. The names of local temporary tables start with a number sign (#) and
local tables are visible only in the current session (connection), while the names of global temporary tables start with
a double number sign (##) and global tables 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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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_tempLIKE employeeON 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
undo logging to support rollbacks.
A declared temporary table is semantically equivalent to a Microsoft SQL Server 2000 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
Microsoft SQL Server 2000 indexes can now specify ascending or descending order. In the previous versions, 6.5
and 7, there was no clause in the command create index to specify ordering. In DB2 UDB, indexes can be defined in
ascending or descending order, and you can allow both forward and reverse scans by specifying allow reverse
scans in the create index statement. The ordering property can be useful in executing queries with the order by and
group by clauses.
Microsoft SQL Server 2000 uses the same B-tree model to represent cluster and non cluster indexes, except for the
following two differences: non cluster indexes are not ordered physically according to the index key, and the leaf
nodes consist of index rows rather than data pages.
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.
Microsoft SQL Server 2000 has a fillfactor clause on the create index command to indicate a percentage value for
how full a leaf level page of the index will be when a new index is created on existing data. The default is 0, which
indicates to the server to completely fill the leaf level pages and to leave space in non-leaf pages for at least one
row.
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%.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Views
Indexes for views can be created under Microsoft SQL Server 2000 under certain conditions, as a way to materialize
views. The first index created on a view must be a unique clustered index; after that any additional nonclustered
indexes can be created. Creating a unique clustered index on a view results in storing the result set in the database
when the view is executed. The query optimizer determines if a query will benefit from using any indexes defined on
views or on base tables. The optimizer can choose an indexed view even when it is not referenced in the from
clause. The options NOEXPAND or EXPAND VIEWS can be specified to force or prevent the optimizer from using
indexed views.
DB2 UDB indexes can only be defined on base tables. Materialized query tables (MQT) are introduced in DB2 V8
and allow view materialization. A MQT is a table whose definition is based on the result of a query, and whose data
is in the form of precomputed results that are taken from one or more tables on which the materialized query table
definition is based. If the SQL compiler determines that a query will run more efficiently against a MQT than the base
table, the query executes against the MQT. This kind of optimization can be faster than additional indexes on a view.
To create a MQT, use the CREATE TABLE statement with the AS fullselect clause and the IMMEDIATE or
REFRESH DEFERRED options.
The summary tables, also known as automatic summary tables (ASTs) are considered to be a special type of MQT
whose fullselect contains a GROUP BY clause summarizing data from the tables referenced in the fullselect. The
summary tables or ASTs were available in previous versions of DB2.
IDENTITY columns
Microsoft SQL Server IDENTITY property provides system-generated values in sequence. The IDENTITY property
can be defined on only one column per table of the following types: numeric with a scale of 0, decimal with a scale of
0, integer, smallint, and tinyint. A seed value and an increment value can be specified; if neither is provided
IDENTITY columns start with 1 and increment by 1. Microsoft SQL Server also supports the uniqueidentifier data
type. The value of a column defined as uniqueidentifier can be generated using the NEWID function or defining a
string constant in hexadecimal digits.
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 TABLE t1
(c1 CHAR(30),
c2 DOUBLE,
c3 INT NOT NULL GENERATED ALWAYS as identity,
(START WITH 100, INCREMENT BY 5))
Computed columns
Microsoft SQL Server 2000 computed columns are not stored in the table and, as the name suggests, are
calculated using the expression that defines the column value. Indexes can be created on computed columns, but
there are restrictions, such as the expression should be deterministic.
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 non-unique 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.2 from Microsoft SQL Server 2000
SELECT c1 + c2 FROM t1 WHERE (c1 + c2) * c1 > 100 ;
can be rewritten as:
SELECT c3 FROM t1 WHERE c3 * c1 > 100 ;
Constraints
Microsoft SQL Server 2000 allows the creation of defaults and rules for columns in a database to define default and
check constraints as a backward compatibility feature. The standard way to define check and default constraints is
by using check and default clauses in the create table or alter table commands. Rules cannot be defined for an SQL
Server-supplied data type or for a column of text, image, or timestamp type and only one rule can be bound to a
column.
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.
The Microsoft SQL 2000 Server integrity constraints definition can indicate whether the index to be created is
nonclustered (the 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.
Microsoft SQL Server 2000 referential constraints have two possible actions: CASCADE and NO ACTION (the
default). Previous versions of Microsoft SQL Server cannot cascade changes through related tables in the database;
you need to use triggers to achieve these actions.
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.
Scrollable cursors
Microsoft SQL Server 2000 has support for all ANSI-style cursors: static, dynamic, forward only, and keyset-driven.
Transact-SQL includes support for INSENSITIVE and SCROLL cursor behavior and for all fetch options (FIRST,
LAST, NEXT, PRIOR, RELATIVE, and ABSOLUTE). Cursor support is available through the following APIs: ADO
(Microsoft ActiveX Data Object), OLE DB, ODBC (Open Database Connectivity), and DB-Library.
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
Microsoft SQL Server 2000 has the ability to specify outer joins on the FROM or WHERE clause. The ANSI-style
join syntax is supported on the FROM clause for left, right, and full outer joins. The specific syntax for the right and
left outer joins of *= and =* on the WHERE clause is only supported for backward compatibility.
The DB2 syntax for joins is ANSI-style, with the operators INNER, LEFT [OUTER], RIGHT [OUTER], and 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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
SELECT deptno, deptname, empno, lastnameFROM department LEFT OUTER JOIN employeeON mgrno= empno
Transactions
Microsoft SQL Server supports explicit transactions defined by the statements begin transaction, save transaction,
commit transaction, and rollback transaction. If implicit_transaction mode is enabled, SQL 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.
Microsoft SQL Server supports savepoint as a mechanism to roll back portions of transactions. The command save
transaction creates a savepoint that can be rolled back later with the command rollback 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:
.
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;
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).
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;
DB2 does not support savepoint-related SQL statements within the definition body of a trigger.
DB2 supports nested savepoints, this means a savepoint can be set up within another savepoint. There is no limit
to the number of savepoints and the number of nested savepoint levels. With nested savepoints, an application can
have multiple levels of savepoints active simultaneously, and the application can roll back to any active savepoint,
as required. A rollback to a particular savepoint statement also releases any active nested savepoints within the
savepoint being rolled back.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Savepoints offer the following advantages over compound SQL blocks:
.
Enhanced control of transactions, if a statement within the scope of a savepoint fails, the application can roll
back all of the statements in the scope of the savepoint, but commit the work performed by statements
outside of the scope of the savepoint.
.
Less locking contention, locks are adquired as each statement in the scope of the savepoint is issued.
.
Improved integration with application logic, for example an application can either roll back the work
performed within the savepoint or commits the work performed in the entire transaction, which may include
SQL statements that have becomed part of the savepoint scope.
Triggers
Microsoft SQL Server 2000 triggers are coded using Transact-SQL and are stored in the database itself. SQL
Server allows nested triggers, up to 32 levels. Direct or indirect recursion in triggers is available as a database
configuration option. Triggers can be activated after or instead of the triggering event. INSTEAD OF triggers
override the triggering action and are executed prior to applying any constraints defined on the table. Multiple
AFTER triggers per table can be coded, but only one INSTEAD OF trigger. Multiple user actions can be specified for
all triggers. INSTEAD OF triggers can be defined on views, but AFTER triggers can not. The system stored
procedure sp_settriggerorder can be used to set the first and last AFTER triggers to be executed; any other AFTER
triggers are executed in random order.
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. 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. The maximum depth of cascaded triggers is 16. Triggers can invoke SQL and
external stored procedures, and User-Defined Functions (UDFs) by executing the CALL statement.
The Microsoft SQL Server deleted and inserted logical tables are transition tables, which 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 partsREFERENCING NEW AS n_rowFOR EACH ROW MODE DB2SQLWHEN (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
Microsoft SQL Server 2000 INSTEAD OF triggers override the triggering action and constraints are not checked
prior to the trigger body execution. There are also restrictions such as, updateable views and tables with a foreign
key defining a cascade action are not valid object specifications when creating INSTEAD OF triggers.
INSTEAD OF triggers are supported since 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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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_INSERTINSTEAD OF INSERT ON EMPV
REFERENCING NEW AS NEWEMP
DEFAULTS NULL FOR EACH ROW MODE DB2SQLINSERT INTO EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME,WORKDEPT,
PHONENO, HIREDATE)
VALUES(EMPNO, FIRSTNME, MIDINIT, LASTNAME,
COALESCE((SELECT DEPTNO FROM DEPARTMENT AS DWHERE D.DEPTNAME = NEWEMP.DEPTNAME),
RAISE_ERROR('70001', 'Unknown department name')),
PHONENO, HIREDATE)
Stored procedures
Microsoft SQL 2000 Server stored procedures are coded using Transact-SQL and are stored in the database. The
maximum number of parameters in a stored procedure is 1024. Microsoft SQL Server also has extended stored
procedures that are functions contained in a dynamic link library; these procedures can be loaded and executed in a
similar way to Transact-SQL stored procedures. Extended procedures run within the address space of Microsoft
SQL Server, and only by storing them in a separate instance they will be able to run in a separate memory space.
However, distributed queries may be required if extended procedures reside on a separate instance. Extended
stored procedures can only be programmed using the SQL Server Open Data Services API.
DB2 UDB stored procedures can be coded in SQL or in compiled libraries using third-generation languages,
including C, C++, COBOL, .NET common language runtime languages, OLE, and Java (SQLJ/JDBC). The stored
procedures code in a supported third generation language are called external stored procedures. 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.
The following figure illustrates the architecture for DB2 stored procedures in SQL or a third-generation language:
Figure 7. DB2 UDB Stored Procedures
One a stored procedure is created, they are invoked by executing the CALL from client applications, external and
SQL stored procedures, triggers, dynamic compound statements, and the Command line processor (CLP).
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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
After the create procedure statement is executed, its procedural statements are converted to a native representation
that is stored in the database catalogs, as is done with other SQL statements. When an SQL procedure is called,
the native representation is loaded from the catalogs and the DB2 engine executes the procedure.
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 = defaultvalueDECLARE 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>;
@@rowcountGET 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.
DB2 allows the user to extract and install external stored 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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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:
.
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:
.
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
Microsoft SQL Server 2000 supports user-defined functions through the statement CREATE FUNCTION. Earlier
versions did not include this feature. Two types of user-defined functions available: scalar and table-valued. The
latter returns a table. Multiple statements can be defined enclosed by BEGIN/END keywords.
DB2 UDB has five different types of functions that can be created using the CREATED FUNCTION statement:
.
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.2 from Microsoft SQL Server 2000
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 ACTIONDETERMINISTIC
RETURN
SELECT EMPNO, LASTNAME, FIRSTNME
FROM EMPLOYEE
WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO
External UDFs must be coded and compiled before executing the create function statement that registers them in
the database. For a Java UDF, 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. The following examples include the Java code and the
create function statement for the stringlen( ) UDF:
import java.lang.*; // for String classimport COM.ibm.db2.app.*; // UDF and associated classes
class UDFsrv extends UDF
{
public void scalarUDF(String inputStr, int outputStrLen)
throws Exception
{
try
{ set(2, inputStr.length())
;
}
catch (Exception e)
{ throw e;
}
}
}
CREATE FUNCTION stringlen ( VARCHAR(20) , INT )
RETURNS INT
FENCED VARIANT NO SQLNO 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:
.
SQL table and scalar UDFs
.
UDFs that read WebSphere MQ 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 support
Microsoft SQL Server 2000 support for XML consists of the ability to access the server using a URL, support for
XML-Data schemas, and the ability to specify XPath queries against these schemas, store XML data using functions
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
and stored procedures, and compose XML data using the SELECT statement with the FOR XML clause. The
Microsoft SQL Server 2000 OLE DB provider (SQLOLEDB) allows XML documents to be set as command text and
to return result sets as a stream.
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.
A new standard SQL/XML function in DB2 UDB Version 8.2, XMLSERIALIZE (with the CONTENT option), allows
the conversion of an XML data type value into a result string data type that is appropriate for the length of the XML
output. The result type can be specified as CHAR or VARCHAR for better performance. The XML data type is an
internal representation of XML and can be used only as input to functions that accept this data type as input. XML is
a transient data type that cannot be stored in the database or returned to an application.
Serialization is the inverse operation of parsing; it is the process of converting a parsed XML value into a textual
XML value. XMLSERIALIZE converts an XML expression into an SQL string value that, in turn, can be bound out to
host character variables.
Global variables
Microsoft SQL Server provides global variables to report system or connection information. Microsoft SQL Server
2000 substitutes scalar functions for some global variables. 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 UDFs can be defined.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Programming interfaces
DB2 programming interfaces overview
DB2 provides the following programming interfaces to develop applications:
.
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.
.
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).
.
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.
.
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.
.
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.
.
ADO.NET Interface
Development of ADO.NET applications using DB2 .NET Data Provider, OLE DB .NET Data Provider, or
ODBC .NET Data Provider to access DB2 family of databases and other datasources.
.
IBM or third-party tools
Applications can also be developed using tools such as Net.Data, Excel, Perl, 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 8. Client/Server Architecture
The client application requires the installation of DB2 Run-Time Client, DB2 Run-Time Client Lite, or DB2
Administration Client to allow database connectivity.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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.
In addition to dynamic SQL, DB2 CLI offers the following advantages:
.
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.
.
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.
.
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.
.
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.
.
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.
.
Extended data conversion - Automatically converts data between SQL and C data types.
.
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.
.
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.
.
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 that 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 9. 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:
.
Support for client applications and applets written in Java using JDBC
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
Precompile and binding support for client applications and applets written in Java using SQLJ
.
Support for Java UDFs and stored procedures on the server
DB2 UDB Version 8.2 supports JDK 1.4 on all supported workstation operating system environments, except on AIX
4.3 and Linux IA64, where IBM Developer Kit 1.3.1 is still required. DB2 UDB for Solaris will ship with JDK 1.4.2.
DB2 UDB for all other supported workstation operating systems will ship with JDK 1.4.1 (or the equivalent
vendor-named Java developer kit).
Depending upon your operating system, the IBM Developer Kit for Java (SDK) 1.3.1, SDK 1.4.1, or SDK 1.4.2 is
required for DB2 to run the DB2 Control Center or to create and run Java applications, including stored procedures
and user-defined functions. The IBM SDK is supported, as well as the HP-UX JDK and the Solaris Operating
Environment JDK. For the most up-to-date SDK information for application development, see the topic platform
support on the DB2 Java application development Web page ibm.com/software/data/db2/udb/ad/v8/java/.
The DB2 Universal JDBC Driver is an entirely new driver written completely in Java that uses the DRDA protocol for
client/server communications. Therefore, you can expect some differences in behavior between this driver and other
drivers in prior versions to DB2 Version 8.2. These differences are documented under the topic JDBC differences
between the DB2 Universal JDBC Driver and other DB2 JDBC drivers, in the Application Development Guide:
Programming Client Applications. This new driver is provided in the db2jcc.jar file in the sqllib/javadirectory. It is a single driver that includes JDBC type 2 and JDBC type 4 behavior, as well as SQLJ support.
When an application loads the DB2 Universal JDBC Driver, a single driver instance is loaded for type 2 and type 4
implementations. The application can make type 2 and type 4 connections using this single driver instance. The type
2 and type 4 connections can be made concurrently. DB2 Universal JDBC Driver type 2 driver behavior is referred
to as DB2 Universal JDBC Driver type 2 connectivity. DB2 Universal JDBC Driver type 4 driver behavior is referred
to as DB2 Universal JDBC Driver type 4 connectivity.
The following figure shows how a JDBC application connects to a datasource using the DB2 Universal JDBC Driver
type 4 connectivity. The application connects using the DriverManager interface, which is available for all levels of
JDBC. Connecting using the DataSource interface is available with JDBC 2.0 and above.
Figure 10. Java application implementation in DB2 Universal JDBC Driver type 4 connectivity
The DB2 JDBC type 2 Driver for Linux, UNIX, and Windows (also known as DB2 JDBC type 2 legacy driver) is
deprecated as of DB2 Version 8.2. The DB2 JDBC type 3 driver for Linux, UNIX and Windows (also known as DB2
JDBC net driver) is deprecated as of DB2 Version 8.1.
The DB2 Universal JDBC Driver supports these JDBC and SQLJ functions:
.
Most of the methods that are described in the JDBC 1.2 and JDBC 2.0 specifications, and some of the
methods that are described in the JDBC 3.0 specifications. See the topic Comparison of driver support for
JDBC APIs in the Application Development Guide: Programming Client Applications.
.
SQLJ statements that perform equivalent functions to all JDBC methods.
.
Connections that are enabled for connection pooling. WebSphere Application Server or another application
server does the connection pooling.
.
Implementation of Java user-defined functions and stored procedures (Universal Type 2 Connectivity only).
.
Global transactions that run under WebSphere Application Server Version 5.0 and above.
.
Support for distributed transaction management. This support implements the Java 2 Platform, Enterprise
Edition (J2EE) Java Transaction Service (JTS) and Java Transaction API (JTA) specifications, which
conform to the X/Open standard for global transactions (Distributed Transaction Processing: The XA
Specification, available from www.opengroup.org).
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
For more information on Java application development and the JDBC specification, see the DB2 Universal
Database Java Web site at .ibm.com/software/data/db2/udb/ad/v8/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 Universal JDBC driver type 2 connectivity offers support for
distributed transaction processing that conforms to the XA specification. This support implements the J2EE Java
Transaction Service (JTS) and Java Transaction API (JTA) specifications on DB2 UDB for Linux, UNIX, and
Windows systems only.
The following illustration shows the IBM J2EE application model with all three fundamental parts: components,
containers, and connectors:
Figure 11. J2EE Application model
Embedded SQL differences
Microsoft SQL Server 2000 provides precompilers for C, Microsoft Embedded SQL for the C development
environment (ESQL/C).
DB2 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.
ESQL/C has the data structure SQLCA to communicate between the Microsoft SQL Server and the application, and
the fields SQLCODE and SQLSTATE can be explicitly declared as application variables. ESQL/C allows the use of
all Transact-SQL statements, functions, and control-of-flow language in embedded SQL with a few exceptions. The
ANSI/ISO SQL and Transact-SQL transaction modes are available in ESQL/C. The latter provides a save
transaction or begin transaction statement. In ESQL/C, the connect statement is used to establish a connection
between an application program and a specific database with a supplied user name and password.
When using DB2 precompilers, 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;
DB2 SQL and Transact-SQL extensions are different; some Transact-SQL statements are not valid in DB2 UDB.
DB2 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.
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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Programming APIs differences
Microsoft SQL Server 2000 provides the DB-Library API for C, DAO for Microsoft Visual Basic, and Microsoft Visual
C++, and ADO and RDO for Microsoft Visual Basic to write client applications for the Windows operating systems.
The ODBC API is also provided for C, C++, and Microsoft Visual Basic to write ODBC client/server applications that
connect to a database server using the ODBC SQL Server driver. The ODBC Driver supports the ODBC 3.51
specification and ODBC 2.5 or earlier versions following the definitions in the 3.51 specification.
The Microsoft SQL Server Database Services API includes: SQL Distributed Management Objects (DMO) to write
server administration programs; Open Data Services to write server applications for client/server systems or
extended stored procedures; Distributed Transaction (MS DTC) to define distributed transactions; and Data
Transformation Services (DTS) to execute complex data transformations. Meta Data Services is intended to store
metadata, and it is designed to be integrated with other tools and applications such as OLAP and data warehousing.
Analysis Services API gives applications access to the OLAP and data-mining facilities.
Microsoft Visual Studio .NET Framework Class Library contains the System.Data namespace, which consists
mostly of the classes that enable an application to manage data from multiple data sources. This namespace
contains the SQL Server .NET Data Provider and the OLE DB .NET Data Provider which allow direct access to data
sources. Each .NET data provider has a corresponding DataAdapter that can be used as a bridge between a data
source and a DataSet component.
Microsoft SQL Server 2000 includes a SQL Server WMI provider to allow WMI applications get information on SQL
Server databases and instances.
DB2 UDB supports applications written on Microsoft Visual Basic and Microsoft Visual C++, database applications
that conform to the Data Access Object (DAO) and Remote Data Object (RDO) specifications using Microsoft OLE
DB to ODBC Bridge or the IBM OLE DB Provider for DB2.
The IBM OLE DB Provider for DB2, whose provider name is IBMDADB2, enables OLE DB consumers to access
data on a DB2 server. This support gives OLE DB-based applications the ability to extract or query DB2 data using
the OLE interface. If DB2 Connect is installed, these OLE DB consumers can also access data on a host DBMS
such as DB2 for MVS, DB2 for VM/VSE, or SQL/400. The IBM OLE DB Provider for DB2 complies with Version 2.5
of the Microsoft OLE DB specification.
The DB2 .NET Data Provider is an extension of the ADO.NET interface that allows .NET applications to access a
DB2 database through a secure connection, execute commands, and retrieve result sets. In order to develop and
run applications that use the DB2 .NET Data Provider, the .NET Framework Version 1.0 or 1.1 is required. The DB2
.NET Data Provider allows .NET applications to access database on DB2 UDB Version 8 for Windows, UNIX, and
Linux-based computers and all supported DB2 family databases through DB2 Connect.
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 tasks,
such as creating, activating, backing up, or restoring a database.
Since DB2 supports ODBC and OLE DB, the OLE DB .NET Data Provider and the ODBC .NET Data Provider allow
direct access to DB2 databases from Microsoft Visual Studio .NET applications. Furthermore, IBM announced last
year that it has joined Microsoft's Visual Studio .NET Integration Program (VSIP) as part of IBM's open strategy.
This support for VSIP will enable developers to use .NET Framework for application development and DB2 as
database server. DB2 will have a unified interface for accessing enterprise data and deploying business logic
directly into the database, thus ensuring seamless integration with DB2 and Microsoft Visual Studio .NET.
The DB2 Version 8 WMI provider allows WMI applications to monitor DB2 server services, enumerate and create
databases, configure operational settings and perform database backup, restore, and roll-forward operations.
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 DB2 UDB Version 7.2,
they can also be written in Microsoft Visual Basic. For more details, see the topic 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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
DB2 OLAP Server desktop graphic interface can be used for easily mapping relational data sources to OLAP
structures to create OLAP applications. The Information Catalog Manager helps to locate data within an
organization, and uses an information catalog database to store information about data (metadata). The Information
Catalog Manager provides C language API calls for programming applications.
These programming interfaces are all very different, but the effort of porting applications to DB2 can be significantly
reduced by using conversion tools (see the section Conversion Tools of this paper).
When porting application to access DB2 databases, it is important to consider using server-side features such as
MDC tables or MQT to improve database performance with no impact on application development. See section
Implementation Issues for details on these DB2 server-side features.
Web services applications
SQL Server 2000 Web Services Toolkit contains tools, code samples, white papers, and SQLXML 3.0 extensions
needed to build XML Web services and Web applications with SQL Server 2000. SQLXML 3.0 is an extension to the
built-in XML capabilities of Microsoft SQL Server 2000 to create XML Web services from SQL Server stored
procedures or server-side XML templates. SQLXML 3.0 also includes extensions to the .NET Framework that adds
SQLXML capabilities to the programming languages supported by Microsoft Visual Studio .NET, including C# and
Microsoft Visual Basic .NET
DB2 can be accessed as a Web service provider, and it is usually teamed with WebSphere family products to
provide a complete Web services framework. Web services object runtime framework (WORF) is part of DB2 UDB
ESE Version 8, and WebSphere Studio Version 5. WORF is in the following path in DB2 Universal Database
Version 8: <DB2 UDB installed location>/samples/java/Websphere/dxxworf.zip.
DB2 offers an easy way to develop data-intensive Web services applications through the 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. DADx files can be created quickly without
any Java programming. For details, see the DB2 UDB Application Development Guide.
Web services continues to be a focal point as the new application development paradigm. DB2 UDB Version 8.2
has enhanced Web service support as a provider. SOAP engine transparency is now provided, allowing more
choices of soap engines to be plugged into the run time. With the new dynamic query capability, the Web service
client can determine at invocation which query should be executed, in addition to the fixed transactions/queries in
DADXes. A tracing component has also been added to make it easier to diagnose errors in the provider. Another
important feature is the support for Web service inspection language (WSIL) so that users can easily explore the
Web services that are offered in the DB2 Web services provider.
For a demonstration of DB2 as a Web service provider, see the IBM Video Central for e-business tutorial at
ibm.com/developerworks/db2/library/samples/videocentral/videocentral.html.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Terminology map
The following table compares the basic DB2 UDB administration-related terms to the equivalent terms used by
Microsoft SQL Server.
DB2 Universal Database Microsoft SQL Server
Physical Layer
Table space Filegroup
SMS table space Database file with FILEGROWTH > 0
DMS table space Database file with SIZE >0, FILEGROWTH = 0
Container (raw or file) Database file (raw or operating system file)
Logical Layer
Server Server
Instance (One or more; each instance manages one or more
databases)
n/a
Database Database
Database directories (created by DB2) SQL Enterprise Manager registry
Node directory (created by DB2) SQL Enterprise Manager registry
Database Manager configuration file Windows NT Registry
Database configuration file Windows NT Registry
Catalog tables System tables (master database and database)
Database Objects
Schema Schema
Table Table
Table constraint Rule and table constraint
View View
Index Index
Recovery log Transaction log
Archive log Transaction log dump
Users and user groups (operating system) NT users and groups, Database users, groups and roles
Package n/a
Sample database (called sample) Sample database (called pubs)
Administration / Usage
Control Center Microsoft management Console, SQL Enterprise Manager
Tables assigned to table spaces, containers assigned to table
spaces
Tables assigned to filegroups, database files grouped into
filegroups.
Administration commands and statements System stored procedures
Binding (binds a DB2 utility or a program to a database so it
can be used)
n/a
Backup database Backup database
Archive online log files Backup log
Restore from backup Restore database
Roll-forward recovery Restore log
Crash recovery Automatic recovery
Run statistics Update statistics, Create statistics
Load, Import, Export bcp
Command Processor, Visual Explain SQL Server Query Analyzer
Command Line Processor (CLP) ISQL
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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 All All
Level 1 Functions All All
Level 2 Functions All All, except for SQLDrivers()
Additional DB2 CLI Functions All, functions can be accessed by
dynamically loading the DB2 CLI library.
SQLSetConnection()
SQLGetEnvAttr()
SQLSetEnvAttr()
SQLSetColAttributes()
SQLGetSQLCA()
SQLBindFileToCol()
SQLBindFileToParam()
SQLExtendedBind()
SQLExtendedPrepare()
SQLGetLength()
SQLGetPosition()
SQLGetSubString()
SQL 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
C Data Types All the types listed for DB2 CLI. 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.2 from Microsoft SQL Server 2000
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 Mapped to X/Open SQLSTATES with
additional IBM SQLSTATES, with the
exception of the ODBC type 08S01.
Mapped to X/Open SQLSTATES with
additional IBM SQLSTATES
Multiple connections per application Supported Supported
Dynamic loading of driver Supported Not applicable
** Only supported on Windows platform
The following table map DB2 isolation levels to ODBC transaction isolation levels:
IBM Isolation Level ODBC Isolation Level
Cursor Stability SQL_TXN_READ_COMMITTED
Repeatable Read SQL_TXN_SERIALIZABLE_READ
Read Stability SQL_TXN_REPEATABLE_READ
Uncommitted 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.2 from Microsoft SQL Server 2000
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 version 1/3 is designed to migrate data and the query and procedure language from the following source
database management systems:
.
Informix Dynamic Server Versions 7.3 and 9
.
Informix Extended Parallel Server (XPS) (partial support)
.
Microsoft SQL Server, Versions 6, 7, and 2000
.
Oracle 8i and partial support for Oracle 9i
.
Sybase Enterprise, versions 11 and 12
to the following target systems:
.
IBM DB2 UDB Universal Database for Workstation platforms, Version 8.1 or later
.
IBM DB2 UDB Universal Database for i5/OS, Version 5.2 or later
.
IBM DB2 UDB Universal Database for z/OS, Version 8
It runs on AIX, Linux, Sun Solaris, and Windows. The only language available is English.
Figure 12. Toolkit Menu Specify Source tab
The toolkit includes the following features:
.
A wizard to migrate simple databases
.
A full-featured GUI interface (Toolkit) to further refine the migration and tailor options for complex databases
conversions
.
The migration source can be a database data source or a DDL script
.
Converts Transact-SQL or PL/SQL object definitions (including stored procedures, user-defined functions,
triggers, packages, tables, views, indexes and sequences)
.
Converts Transact-SQL or PL/SQL scripts to DB2 scripts
.
Generates and run Scripts to unload data from source and upload data to DB2
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
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:
.
Create a project
.
Specify source (ODBC DSN or SQL script)
.
Convert - generates DB2 scripts and reports error messages
.
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
.
Generate Data Transfer scripts
.
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 developerWorks DB2 library contains many references to material related to migration to DB2 topics,
specifically a tutorial and a technical article about MTK:
.
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:
ibm.com/developerworks/db2/library/tutorials/0209jarzebowicz/index.html
.
Automate the Switch to DB2 Using the IBM DB2 Migration Toolkit. This technical article explains how
MTK works: ibm.com/developerworks/db2/library/techarticle/0209jarzebowicz/0209jarzebowicz.html.
.
Move data using the IBM DB2 Migration Toolkit. If your application recently ported to IBM DB2 Universal
Database (UDB) and you want to move data from an existing deployment of Oracle or SQL Server to DB2,
this article shows you how to do this using the IBM DB2 Migration Toolkit:
ibm.com/developerworks/db2/library/techarticle/dm-0411yip/
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. (ca.com)
Pervasive
Pervasive offers a transformation tools for DB2 data movement and application integration such as Pervasive Data
Integrator, Pervasive Migration Toolkit and Pervasive Data Exchange.
For additional information, contact Pervasive (www.pervasive.com/products/).
Embarcadero Technologies
This company offers a suite of products for database administration, design and development. Some of these
products are:
.
DBArtisan - Manage databases across the enterprise to achieve higher availability, performance and
security.
.
Embarcadero SQL Debugger - Isolate and correct errors on database logic in applications. Included in
DBArtisan and Rapid SQL.
.
Embarcadero SQL Tuner - Troubleshoot and rewrite database logic to improve application performance.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
ER/Studio - Design, document and maintain databases and data warehouses for higher performance and
quality. Reverse engineer database objects (DDL).
.
ER/Studio Repository - Distribute work among the modeling team members.
.
Rapid SQL - Write, debug and tune database logic in applications.
For more information, contact Embarcadero Technologies (www.embarcadero.com/products/products.html)
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Resources and references
DB2 Universal Database Version 8.2 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 DatabaseV8.1 for Linux, UNIX, and Windows Database Administration Certification Guidea
by Baklarz and Wong
Publication Order Number: 0-13-046361-2
DB2 Universal Database V8.1 Application Development Certification Guide
by Steve Sanyal, Kevin Gashyna, David Martineau, and Mike Kyprianou
Publication Order Number: 0-13-100771-8
Microsoft SQL Server 2000 books online (Updated 2004)
What’s New?
SQL Server Architecture
Creating and Maintaining Databases
Replication
Building SQL Server Applications
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.
Pervasive Product Overview
Product overview at www.pervasive.com/products/
Embarcadero Technologies
Product overview at www.embarcadero.com/products/products.asp
For more information
DB2 Family
www.ibm.com/software/data/db2
DB2 Product and Service Technical Library
www.ibm.com/software/data/technical/
DB2 Maintenance
www.ibm.com/software/data/db2/udb/support/downloadv8.html
ftp://ftp.software.ibm.com/ps/products/db2/fixes/ <language>/<platform>/<FixPak#>
DB2 Support
www.ibm.com/software/data/db2/udb/support/
Complimentary DB2 Self-study courses
www.ibm.com/software/data/education/selfstudy.html
Data Management education page
www.ibm.com/software/data/education.html
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Migration Station at developerWorks DB2
www.ibm.com/developerworks/ondemand/migrate/index.html
DB2 Technical resources for DB2 information management software (developerworks DB2)
www.ibm.com/developerworks/db2/
DB2 Migration Web site
www.ibm.com/software/data/db2/migration
DB2 Information Management Software Today
www.ibm.com/software/data/db2infonews/
IBM PartnerWorld Program
www.developer.ibm.com/
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
....更多联系本人:MSN:zhoujianguo_leo@hotmail.com
from Microsoft. SQL Server2000
By Amyris V. Rada
IBM Toronto Lab
arada@ca.ibm.com
(Reprinted courtesy of developerWorks DB2, at ibm.com /developerworks/db2/)
Trademarks
The following terms are trademarks or registered trademarks of the IBM Corporation in the United States and/or
other countries:
AIXInformix
AS/400MQSeries
DataJoinerMVS/ESA
DataPropagatorNet.Data
DataRefresherNUMA-
Q
DB2OS/400
DB2 ConnectOS/390
DB2 Universal DatabaseOS/
2
DB2 OLAP ServerRS/6000
Distributed Relational Database ArchitectureSystem/390
DRDAVM/ESA
IBMVSE/ESA
IMSz/OS
iSeries 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, Microsoft SQL Server, VisualBasic, Visual C++, Visual InterDev, Visual Studio, Windows, Windows
NT and Windows 2000 are trademarks or registered trademarks of Microsoft Corporation in the United States,
other countries or both.
UNIX is a registered trademark of the Open Group in the United States and other countries.
Other company, product, and service names may be trademarks or service marks of others.
. 2004 International Business Machines Corporation. All rights reserved.
Table of Contents
Introduction .........................................................................................
5
Why Port to DB2 UDB? ...............................................................................
6
IntegratedSupport for NativeEnvironments ..........................................................
6
Integrated System ManagementTools ...............................................................
6
Multiplatformtools for DB2 Universal Database ........................................................
7
Self-managing andResource Tuning ................................................................
7
DataReplication and Publishing ....................................................................
8
IntegratedSupport for Development Environments .....................................................
8
Integrated Web Access ..........................................................................
8
Tools for Building Web Applications .................................................................
9
Basic Warehousing Functionality ...................................................................
9
High Availability Support ..........................................................................
9
IBM Program for Assistance toDevelopers ......................................................... 10
DB2 Universal Database Product Family ............................................................ 11
DB2 PersonalEdition .......................................................................... 12
DB2 UDB Express Edition ...................................................................... 12
DB2 Workgroup Server Edition .................................................................. 12
DB2 Enterprise Server Edition ................................................................... 12
DB2 EveryplaceEdition ........................................................................ 12
DB2 Run-TimeClient .......................................................................... 13
DB2 Run-Time Client Lite ....................................................................... 13
DB2 Administration Client ....................................................................... 13
DB2Application Development Client .............................................................. 13
DB2 Products ................................................................................ 14
Data Types Conversion ............................................................................ 16
Administration Issues .............................................................................. 18
Environments ................................................................................ 18
Administration Server .......................................................................... 18
Federated SQL Server 2000Servers .............................................................. 19
Database Devices ............................................................................ 20
Log Files .................................................................................... 20
Database Security ............................................................................ 21
Backup and Restore ........................................................................... 22
Online Utility Tools ............................................................................ 23
Tasks ...................................................................................... 24
SystemStored Procedures and Administrative Tools ................................................. 24
Database Consistency ......................................................................... 26
Implementation Differences ........................................................................ 28
Isolation Levels ............................................................................... 28
Statement IsolationLevels ...................................................................... 28
Locking Mechanisms .......................................................................... 28
System Databases ............................................................................ 29
SQL StandardCompliance ...................................................................... 29
Tables ...................................................................................... 30
Temporary Tables ............................................................................ 30
Indexes ..................................................................................... 31
Views ...................................................................................... 32
IDENTITY Columns ........................................................................... 32
Computed Columns ........................................................................... 32
Constraints .................................................................................. 33
Scrollable Cursors ............................................................................ 33
ANSI JoinOperators ........................................................................... 33
Transactions ................................................................................. 34
Triggers ..................................................................................... 35
Stored Procedures ............................................................................ 36
User Defined Functions ........................................................................ 38
XML Support ................................................................................. 39
Global Variables .............................................................................. 40
Programming Interfaces ........................................................................... 41
DB2 Programming Interfaces Overview ............................................................ 41
DB2 Call Level Interface ........................................................................ 42
DB2 JavaEnablement ......................................................................... 42
Embedded SQL Differences ..................................................................... 44
Programming APIs Differences .................................................................. 45
Web Services Applications ...................................................................... 46
Terminology Map .................................................................................. 47
DB2 CLI vs. ODBC Function Map ................................................................... 48
Conversion Tools .................................................................................. 50
IBM Migration Toolkit (MTK) ..................................................................... 50
AllFusion ERwin Data Modeler ................................................................... 51
Embarcadero Technologies ..................................................................... 51
Resources and References ......................................................................... 53
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 (DB2 UDB) for UNIX. and
Windows. can help your organization meet these challenges.
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 DB2 Universal Database Enterprise Server Edition (ESE) means that it is flexible enough
to meet future growth.
A real database leader in several technologies, DB2 UDB provides the following capabilities:
.
Integrated support for complex data such as text documents, images, video and audio clips
.
Integrated Web access through native support for Java., Java Database Connectivity (JDBC), embedded
SQL for Java (SQLJ) and Net.Data
.
Integrated system management tools
.
Data replication services
.
High-availability disaster recovery (HADR)
This article introduces DB2 UDB products and their capabilities, discusses porting databases and applications, and
describes the most important aspects of porting applications from Microsoft SQL Server 2000 to DB2 UDB V8.2. 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 06,
2005, 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.2 from Microsoft SQL Server 2000
Why port to DB2 UDB?
DB2 UDB Version 8.2 delivers new features to address the ever-increasing demands and requirements on
customer’s data. The broadened autonomic (also known as self-managing) computing solutions offered report
immediate benefits to Database Administrators. These solutions automate and simplify potentially time-consuming
and complex database tasks. For example, this release simplifies performance optimization for specific workloads
by introducing the Design Advisor, a self-configuring tool that automates database design.
For application developers, DB2 UDB Version 8.2 delivers a significant amount of new capabilities as well as further
integration of DB2 tooling into the Microsoft .NET and WebSphere Studio./Java environments. This simplifies the
development and deployment of DB2 applications, which allows application developers to take advantage of the
openness, performance, and scalability of DB2, regardless of the back-end database or the application architecture.
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.
In addition to this, DB2 offers the following advantages:
.
Integrated support for native environments
.
Integrated system management tools and multiplatform tools
.
Self-managing and resource tuning capabiity
.
Data replication and publishing
.
Integrated support for development environments
.
Integrated Web access
.
Tools for building Web Applications
.
Basic 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 easier to integrate it to
the operating system.
Integrated system management tools
DB2 UDB 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 UDB Version 8 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:
.
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.
.
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.
.
The Configuration Assistant has options to configure both local and remote servers, including DB2
Connect. servers, or to create configuration templates.
.
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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
The DB2 advisors, wizards, and launchpads are also integrated into the Control Center. They assist in performing
administrative tasks step-by-step such as create databases, backup, resotore, design database, configure database
and setup high-availability disaster recovery (HADR).
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.
.
DB2 Table Editor for Multiplatforms offers direct DB2 database access to anyone for creating, reviewing,
or updating data. It requires a Java-enabled browser.
.
DB2 Web Query Tool for Multiplatforms provides easy access to enterprise data using complex
functionality.
.
DB2 Recovery Expert for Multiplatforms provides targeted and automated recovery of database assets,
even as systems remain online. 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.
.
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 online 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.
.
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.
.
DB2 Test Database Generator rapidly populates application and testing environments and simplifies
problem resolution. It can easily create test data from scratch or from existing data sources and maintains
referential integrity while extracting data sets from source databases. It can create complete or scaled down
copies of production databases while masking sensitive production data for use in a test environment.
Offered for DB2 on OS/390, z/OS, UNIX, and Windows.
Self-managing and resource tuning
DB2 UDB self-managing and resource tuning (SMART) database technology 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.
An example of this enhanced manageability is the Design advisor, which assists DBAs in making optimal and
comprehensive database design decisions. This self-configuring tool greatly simplifies the design process by using
workload, database, and hardware information to recommend specific performance-acceleration options for routine
design tasks.
Another example of a new time-saving feature is the ability to automate database maintenance activities such as
backup, table defragmentation (reorg), and table statistics gathering (runstats). DB2 UDB Version 8.2 provides the
ability to easily schedule these tasks to run automatically based on factors determined by the DBA, such as
available maintenance windows, percentage of log files used, and so on.
Other manageability enhancements in DB2 UDB Version 8.2 include self-healing features such as the Health Center
Recommendation Advisor and automated log file management; and self-tuning features for backup and restore
operations, as well as the ability to throttle backup operations and statistics collection. Also available is enhanced
sampling functionality, both row and page level, for faster, and potentially more frequent collection of statistics. This
improves query optimization in challenging business intelligence environments.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Data replication and publishing
DB2 UDB Version 8.2 provides two different solutions to replicate data from and to relational databases, that
ensures timely, reliable, and consistent data across an enterprise: SQL replication and Q replication. In SQL
replication, committed source changes are staged in relational tables before being replicated to target systems. In Q
replication, committed source changes are written in messages that are transported through WebSphere MQ
message queues to target systems. DB2 UDB Version 8.2 also provides a solution called event publishing for
converting committed source changes into messages in an XML format and publishing those messages to
applications such as message brokers.
DB2 Version 8 SQL replication includes four components:
.
Replication Center
.
Capture program and triggers
.
Apply program
.
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 SQL 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 SQL replication solution by supporting sources and targets that include the DB2
family, IMS., VSAM, Oracle, Sybase, Microsoft, Lotus Notes., and others.
.
DB2 DataPropagator. for OS/390, a feature of Version 6 of DB2 Universal Database Server for OS/390
.
DataPropagator Relational Version 5 Release 1 for AS/400
.
IBM DataPropagator NonRelational.
.
IBM DataJoiner.
.
Lotus NotesPump
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:
.
An easy-to-use interface for developing routines, such as stored procedures and user-defined functions
(UDFs)
.
A set of wizards makes it easy to perform development tasks
.
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
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
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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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.
Tools for building Web applications
DB2 UDB supports all the key Internet standards, making it an ideal database for use on the Web. It has in-memory
speed to facilitate Internet searches and complex text matching, combined with the scalability and availability
characteristics of a relational database. Because DB2 Universal Database supports WebSphere, Java, and XML
Extender, it makes it easy for you to deploy your e-business applications.
DB2 Universal Developer's Edition has several tools that provide Web enablement support. WebSphere Studio
Application Developer is an integrated development environment (IDE) that enables you to build, test, and deploy
Java applications to a WebSphere Application Server and DB2 UDB. WebSphere Studio is a suite of tools that
brings all aspects of Web site development into a common interface. WebSphere Application Server Advanced
Edition (single-server) provides a robust deployment environment for e-business applications. Its components let
you build and deploy personalized, dynamic Web content quickly and easily.
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.
Starting in the Data Warehouse Center, in DB2 UDB Version 8.2, the warehouse control database must be a UTF-8
(Unicode Transformation Format, or Unicode) database. This requirement provides expanded language support for
the Data Warehouse Center. The Warehouse Control Database Management tool can be used to migrate the
metadata from a specified database into a new Unicode database.
The DB2 Warehouse Manager product completes the warehousing capability provided by the Data Warehouse
Center.
High Availability support
In order to ensure continuous availability of data, DB2 UDB offers several features such as high-availability disaster
recovery, suspended I/O, split mirror image, dual logging, parallel recovery, backup from split image, and
incremental and delta backups.
DB2 high-availability disaster recovery (HADR) is a data replication feature that provides a high-availability solution
for both partial and complete site failures. HADR protects against data loss by replicating data changes from a
source database, called the primary, to a target database, called the standby. With HADR, the standby database
can take over in seconds and clients that were using the old primary database can be redirected to the standby
database by using automatic client reroute or retry logic in the application. Since HADR uses TCP/IP for
communication between the primary and standby databases, the databases can be situated in different locations.
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. The 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:
.
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.
.
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.
.
Online index reorganization -Users can read and update a table and its existing indexes during an index
reorganization using the new REORG INDEXES command.
.
Configurable online configuration parameters - Over 50 configuration parameters can now be set online,
and these changes take effect immediately.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
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.
.
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. This support consists of configuration
instructions and details for implementation when working with DB2 servers. DB2 provides support for High
Availability Cluster Multiprocessing Enhanced Scalability (HACMP/ES) on AIX, Microsoft Cluster Server on Windows
operating systems, Multi-Computer/ServiceGuard on Hewlett-Packard, and Sun Cluster or VERITAS Cluster Server
on the Solaris Operating Environment.
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/guide/membership.html.
Benefits offered by this program include the following:
.
Marketing and sales support
Marketing education, Business Partner opportunities, co-marketing promotion, and other opportunities
.
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
.
Technical support
Access to cross-platform technical enablement services at the worldwide Solution Partnership Centers, and
industry-leading technical support for developers
.
Incentives
Software discounts, hardware discounts and leases, and discounts on business services such as express
mail, pagers, insurance, and car rentals
.
Financing
Flexible financing to help you and your customers acquire hardware, software, and services with affordable
monthly payments
.
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.2 from Microsoft SQL Server 2000
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
and are available for both 32-bit and 64-bit operating environment modes.
There are four types of DB2 clients: Run-Time Client, DB2 Run-Time Client Lite, Administration Client and
Application Development Client. DB2 UDB Version 8 clients support DB2 UDB Version 8 servers; they can also
connect to DB2 UDB Version 7 servers only if the DB2 server is running Distributed Relational Database
Architecture. (DRDA.)-AS. DB2 UDB Version 6 and Version 7 clients can connect to DB2 UDB Version 8 servers
with limited capacity. DB2 UDB Version 8 clients cannot connect to DB2 Connect Version 7 servers.
DB2 Version 8 Clients are available for the following platforms: AIX, HP-UX, Linux, Solaris operating environment,
Windows NT Version 4, Windows 98, Windows 2000, Windows ME, Windows XP (32-bit and 64-bit editions), and
Windows Server 2003 (32-bit and 64-bit editions). Support on other platforms may be available through earlier
versions of DB2 Clients.
The DB2 Run-Time Client Lite is a new installable component in DB2 UDB Version 8.2 that facilitates access to DB2
servers from Windows-based applications.
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 Product Family
The DB2 Server and Clients products and components include:
.
DB2 Personal Edition
.
DB2 Workgroup Server Edition
.
DB2 Enterprise Server Edition
.
DB2 Everyplace Edition
.
DB2 Run-Time Client
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
DB2 Run-Time Client Lite
.
DB2 Administration Client
.
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
documentation.
DB2 Personal Edition runs on Linux and Windows ME, Windows NT Version 4, Windows 2000 (32–bit), Windows
XP (32–bit or 64–bit), and Windows Server 2003 (32–bit or 64–bit).
DB2 UDB Express Edition
DB2 UDB Express Edition (DB2 Express) is a full-function DB2 relational database, which provides very attractive
entry-level pricing for the Small and Medium Business (SMB) market. It is offered in both per-user or per-processor
(up to two CPUs) pricing models to provide choices to match SMB customer needs. It comes with simplified
packaging, and is easy to transparently install within an application. It is fully compatible with, scalable to, and has all
the autonomic manageability features of its higher-priced family of offerings. DB2 Express can be deployed on
Windows NT Version 4, Windows 2000, Windows XP, and Windows Server 2003. Only support for 32-bit platforms
is provided; support for 64-bit platforms will be available at a later date.
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 (Intel, iSeries., pSeries), Solaris Operating
Environment, Windows NT Version 4, Windows 2000, Windows XP, and Windows Server 2003 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 (Intel, iSeries, pSeries, zSeries), Solaris Operating
Environment, Windows NT Version 4, Windows 2000, and Windows Server 2003. 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 database 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 back-end data sources. The platforms supported are AIX, Solaris Operating
Environment, 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, Linux and Windows, Cloudscape, Lotus. Domino
Server, Oracle, Microsoft SQL Server, and Sybase data sources.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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 Run-Time Client Lite
The DB2 Run-Time Client Lite (DB2 RTCL) is designed to be redistributable by independent software vendors
(ISVs) and to be used for application distribution in mass deployment scenarios typical of large enterprises. Similar
to the DB2 Run-Time Client component, DB2 RTCL provides application interfaces (CLI, ODBC, OLE DB, .NET
Data provider, and JDBC) and the network libraries that are required for DB2 applications to run. It is available only
on Windows operating systems and supports only the TCP/IP and Named Pipes communication protocols.
The main features of the DB2 RTCL are:
.
A significantly smaller disk footprint
.
Shipped as a single executable making it easy to redistribute and deploy
.
Windows Installer Merge Module (.msm file) is available, which simplifies the integration of the DB2 RTCL
code within a larger application
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 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:
.
Precompilers for C/C++, COBOL, and Fortran, providing the language is supported for that platform.
.
Embedded SQL application support, including programming libraries, include files and code samples.
.
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.
.
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.
.
Java Development Kit (JDK) or equivalent, is shipped with DB2 for all supported operating systems. JDK
1.3.1 and Java Runtime Environment (JRE) 1.3.1 from IBM for AIX, IBM Developer Kit and Runtime
Environment (Java 2 Technology Edition) Version 1.3.1 and 1.4.1 Service Release 1 (32-bit version) for
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Linux and Windows, HP-UX 32-bit: Software Developer's Kit and Runtime Environment 1.4.2.01 for HP-UX
11.0 and 11i PA-RISC from Hewlett-Packard, and Solaris 32-bit: JDK Versions 1.3.1 and 1.4.2 and Solaris
64-bit: JDK Version 1.4.2 for Solaris from Sun Microsystems. When you install the DB2 ADC on AIX and
Linux, the Java Development Kit is installed if an earlier version of the Java Development Kit is not
detected. When you install the DB2 ADC on Windows, the Java Development Kit is always installed. For
complete details see section DB2 supported development software in the DB2 Application Development
Guide: Building and Running Applications.
.
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.
.
Object Linking and Embedding Database (OLE DB) table functions on Windows operating systems.
.
C# and Visual Basic .NET applications and CLR .NET routines on Windows operating systems.
.
DB2 Development Center, a graphical application that supports the rapid development of routines (stored
procedures and user-defined functions), and structured types. The Development Center provides a single
development environment that supports the entire DB2 family ranging from the workstation to z/OS. You
can launch the Development Center as a stand-alone application or from a DB2 Universal Database center,
such as the Control Center, 7 the Command Editor, or the Task Center. The Development Center is
implemented with Java, and all database connections are managed by using a Java Database Connectivity
(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.
.
Interactive SQL through the Command Editor or Command Line Processor (CLP) to prototype SQL
statements or to perform ad hoc queries against the database.
.
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 DB2
application development Web site: http://www.ibm.com/software/data/db2/udb/ad/v8/bldg/prepapi.pdf.
.
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.
DB2 products
Other important DB2 products are:
.
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.
.
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.
.
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 (32-bit), HP-UX
(32-bit or 64-bit on a PA-RISC platform), Solaris operating environment (32-bit), Linux (Intel 32-bit), and
Windows 2000 (32-bit).
.
DB2 Net Search Extender, which combines performance with the search interface and functionality of DB2
Text Information Extender. DB2 Net Search Extender V8 adds rich full-text search to enhance database
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
applications with the information users need, and provides query performance and scalability by integrating
caching and optimization technologies. It is most appropriate for high end e-business applications that are
database intensive, since it is designed to work closely with DB2 Universal Database applications. This
product is available for AIX, HP-UX, Solaris operating environment, Linux (Intel, Solaris), Windows NT,
Windows 2000, Windows Server 2003, and Windows XP operating systems.
.
DB2 Information Integrator provides the foundation for a strategic information integration framework that
helps customers speed time to market for new applications, get more value and insight from existing assets,
and control IT costs. Designed to meet a diverse range of data integration requirements for business
intelligence and business integration, it provides a range of capabilities: enterprise search, data federation,
data transformation, data placement (caching and replication), and data event publishing. DB2 Information
and Integration offerings are available on AIX, HP-UX, Solaris operating environment, Linux for Intel, and
Windows operating environment.
.
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. DB2 OLAP Server Spreadsheet Services provides a highly intuitive, user-friendly, thin-client
spreadsheet environment for OLAP analysis with DB2 OLAP Server through integration into Excel. Other
components are DB2 OLAP Server Administration Services and DB2 OLAP Server Deployment Services.
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, Solaris operating
environment, Windows NT 4.0, Windows 2000, and Windows XP.
.
DB2 Warehouse Manager, a infrastructure 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 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.
.
DB2 Universal Database Data Warehouse Editions provide a comprehensive BI platform with everything
needed to deploy, and to build next generation business intelligence solutions. There are two editions:
Standard and Enterprise. It includes DB2, federated data access, data partitioning, integrated OLAP,
advanced data mining, enhanced ETL using DB2 Warehouse Manager, workload management, and
provides light BI for the desktop. The product components vary depending on the type of edition.
.
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, DB2 Test Database
Generator, and DB2 High Performance Unload for Multiplatforms.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Data types conversion
Most Microsoft SQL Server 2000 data types can be mapped to DB2. The following table compares all data types:
MS SQL Data
Type Name
Range of Values Bytes ofStorage
DB2 UDB Data
Type Name
Range of Values Bytes ofStorage
tinyint 0 to 255 1 smallint -32768 to 32767 2
smallint -32768 to 32,767 2 smallint -32768 to 32767 2
int
integer
-231 ( -2,147,483,648) to 231 -1
(2,147483,647)
4 integer
int
-231 to 231 - 1 4
bigint 9223372036854775808 to
+9223372036854775807
8 bigint 9223372036854775808 to
+9223372036854775807
8
numeric(p,s)
dec[imal](p,s)
-1038 - 1 to 1038 -1 2-17 num[eric](p,s)
dec[imal](p,s)
-1031+1 to 1031-1
( p+s <=31)
(p/2) + 1
float(p) 0 or from -2.23E-308 to
-1.79E+308, or from
2.23E-308 to 1.79E+308
4 or 8 1
(15 digits)
float(p) 4 or 8
real 0 or from 1.18E-38 to
3.40E+38, or from -1.18E-38
to -3.40E+38
4
(7 digits)
real 0 or from -3.402E+38 to
-1.175E-37, or from 1.175E-37
to 3.402E+38
4
( 8 digits)
double precision synonym for float double precision 0 or from -1.79769E+308 to
-2.225E-307, or from
2.225E-307 to1.79769E+308
8
(16 digits)
smallmoney -214,748.3648 to
214,748.3647
4 numeric(10,4) 6
money -922,337,203,685,477.5808 to
922,337,203,685,477.5807
8 numeric(19,4) 11
smalldatetime January 1, 1900 to June 6,
2079
42 timestamp 7 January 1, 0001 to December
31, 9999
10 internal,
26 external
datetime January 1, 1753 to December
31, 9999
83 timestamp January 1, 0001 to December
31, 9999
10 internal,
26 external
timestamp varbinary(8) 84 character(8) FOR
BITDATA
date8 year: 0001 to 9999,
month: 1 to 12,day: 1 to 31
4 internal,
10 external
time9 hour: 0 to 24,
minutes/seconds: 0 to 59
3 internal, 8
external
char[acter](n) 1 to 8000 n char[acter](n) 1 to 254 n
varchar(n)
char[acter]
varying
1 to 8000 entry length varchar(n)
character varying
1 to 32,67210 entry length
long varchar11 1 to 32,700 entry length
nchar(n) 1 to 4000 2 * n graphic(n) 1 to 127 2 * n
nvarchar(n) 1 to 4000 entry length vargraphic(n) 1 to 16,33610 2 * entry
length
long vargraphic11 1 to 16,350 2 * entry
length
ntext 230 - 1 (1,073,741,823) multiples of
a page
DBCLOB(n) 1 to 1 073 741 823 2 * entry
length
binary(n)1 to 8000 n character(n) FOR
BIT DATA
1 to 254 n
varbinary(n)
binary varying
1 to 8000 entry length varchar(n) FOR
BIT DATA
1 to 32,672 entry length
bit 0 or 1 1 character(1) FOR
BITDATA12
0 or 1 1
text 231 - 1 (2,147,483,647) multiples of
a page 6
CLOB(n) 1 to 231 entry length
image 231 - 1 (2,147,483,647) multiples
of a page6
BLOB(n) 1 to 231 entry length
uniqueidentifier N/A character(16) FOR
BITDATA13
N/A 13
Note: Some DB2 data types that are not available in Microsoft SQL Server have been included in the table.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
1
float(p) storage is 4 bytes if p < 8; 8 bytes if 8 <= p < 16. If p < 8, float is a synonym for real.
2
datetime values are accurate to 1/300 of a second. Storage size is 8 bytes: 4 bytes for the number of days before or after
January 1, 1900, and 4 bytes for the number of milliseconds after midnight. Negative values represent dates prior to the base
date.
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
Values in the timestamp columns indicate the sequence of Microsoft SQL Server activity on the row. They are not datetime
data. Storage is as binary(8) varbinary(8) data. A table can have only one timestamp column.
6
Page size is 2K. The column size is 0 until it is initialized.
7
The DB2 UDB 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 = 0001 to 9999, month = 1 to 12, and day = 1
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.
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. A check constraint must be declared to restrict the character values to just 0 or 1. The smallint datatype can also
be used to map this type.
13
uniqueidentifier columns can only be initialized using the newid function or converting from a string constant in the following
format "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" (each x is a hexadecimal digit in the range 0-9 or a-f). The function newid is
non-deterministic and returns a unique value for column data type uniqueidentifier. DB2 provides an equivalent function to
newid, called generate_unique function, which returns a bit data character string 13 bytes long that is unique compared to
any other execution of the same function. This function is defined as not-deterministic and the result of the function is a
unique value that includes the internal form of the Universal Time, Coordinated (UTC) and the partition number where the
function was processed. The length of this column can be reduced from 16 to 13 if the generate_unique function is used to
generate its value.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Administration issues
This section describes administrative features available in Microsoft (MS) SQL Server 2000 and in DB2 UDB
Version 8.2 for UNIX, Linux, and Windows.
Environments
Microsoft SQL Server 2000 supports the installation of multiple instances on the same computer. Each instance
runs independently from all others and has its own set of system and user databases. Only one instance of SQL
Server Version 6.5 or 7.0 can run at the same time as multiple instances of SQL Server 2000. When multiple
instances are running simultaneously, the amount of memory is dynamically allocated for each specific instance by
an algorithm. The relative workload of each instance is also a consideration. Applications must connect to an
instance before they can connect to a database, similar to remote instance connections.
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
Microsoft SQL Server 2000 provides a set of services that allow administrators to schedule the automatic execution
of repetitive tasks. The server automatically acquires system resources such as memory and disk space when
needed, and frees the resources when they are no longer required.
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. The DAS assists the Control Center, Development Center, Replication
Center, and Configuration Assistant when working on the following administration tasks:
.
Enabling remote administration of DB2 servers.
.
Providing the facility for job management, including the ability to schedule.
.
Running of both DB2 and operating system user-defined command scripts.
.
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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
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.
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.
Federated capability
A federation of servers running Microsoft SQL Server 2000 can be grouped to implement database partitioning and
scalability. However, updateable distributed partitioned views are required to partition data horizontally across a
group of servers. Each database server is autonomous.
DB2 ESE supports database partitioning. In an MPP or cluster configuration, DB2 ESE distributes data across
multiple partitions, or subsets, of the database, which reside on multiples Single Partition (SP) or SMP servers. A
unique partition map allows DB2 to manage the distribution and redistribution of data as required. Access plans are
automatically created for parallel execution with standard SQL statements, such as READ, INSERT, UPDATE, and
DELETE. Data scans, joins, sorts, load balancing, table reorganization, data load, index creation, indexed access,
backup, and restore are all performed on all nodes simultaneously. DB2’s "shared nothing" architecture allows
parallel query support with minimal data transfer across nodes. The number of partitions has little impact on partition
traffic between partitions; performance scales in a near-linear fashion when you add nodes to your MPP or add
SMPs to a cluster. Because the data partitioning and parallel execution are handled by the DB2 UDB ESE server,
access to the database is completely transparent to the application and no additional changes are required to
support partitioning.
It is important to note that a federated server is a different concept in DB2 from in Microsoft SQL Server. 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.
Figure 3. Components of a Federated System
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Data sources as shown in the previous figure 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:
.
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
Microsoft SQL Server 2000 uses operating system files and filegroups to store databases and transaction logs; it no
longer uses logical devices. A database consists of two or more files: one to store data and another to store
transaction log records. These database files can automatically grow at a specified increment until they reach a
defined limit or while free space is available on the disk. A set of database files can be grouped in a filegroup, and
database objects can be placed on a specified filegroup.
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 4. 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
Microsoft SQL Server 2000 transaction log consists of one or more log files, each containing a contiguous set of log
records. The log is no longer represented as a system table (Version 6.5). Each physical log file segments into a
number of virtual log files. The size or number of virtual log files is determined dynamically and cannot be
configured. The transaction log wraps around log files, and files are extended if filegrowth is specified until maxsize
is reached or free space is available on disk when the end of the logical log reaches the start of the logical log.
Log truncation frees space for new log records by deleting old log records. Log truncation occurs after executing
BACKUP LOG statement, and every time a checkpoint is issued.
Disk mirror operations are no longer available on Microsoft SQL Server 2000. Hardware devices that provide
mirroring capabilities, such as RAID, are recommended.
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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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.
The logarchmeth1 and logarchmeth2 database configuration parameters are set to OFF. 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 using archived logs. Archive logging can be enabled using the logarchmeth1 database configuration
parameter, once 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.
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 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.
When creating an online backup image, the log files required to restore and recover a database can included in the
image using the INCLUDE LOGS option of the BACKUP DATABASE command. When this option is specified, the
backup utility will truncate the currently active log file and copy the necessary set of log extents into the backup
image.
Database security
Microsoft SQL Server 2000 authentication is determined at installation time. The possible selections are Windows or
SQL Server authentication. The former uses Microsoft Windows NT or Windows 2000 security facility for user
validation; the latter is provided for backward compatibility and is required on Windows 95 and 98 where no security
facility is present in the operating system.
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 (AIX, Solaris Operating Environment,
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.
New in DB2 UDB Version 8.2 custom authentication and group management mechanisms is possible in the form of
loadable plugins that DB2 will load and access to perform user authentication. This provides new alternatives to the
authentication methods currently supported by DB2.
Microsoft SQL Server includes several predefined roles with implied permissions. The two types of predefined roles
are fixed server and fixed database. Microsoft Windows NT and Windows 2000 groups can be used in much the
same way as roles.
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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
authorization, that is, the required privilege or authority. The hierarchy between authorities and privileges is shown in
the following figure:
Figure 5. DB2 Authorities
In DB2, the following authorities exist:
.
System administration (SYSADM)
.
System control (SYSCTRL)
.
System maintenance (SYSMAINT)
.
System monitor authority (SYSMON)
.
Database administration (DBADM)
.
LOAD
SYSADM, SYSCTRL, SYSMAINT, and SYSMON 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: BINDADD, CONNECT, CREATETAB, and
IMPLICIT_SCHEMA. All privileges including LOAD can be managed using the GRANT and REVOKE SQL
commands. The LOAD authority allows users who are not SYSADM or DBADM to load data into a table. Microsoft
SQL Server roles can be mapped to DB2 authorities or groups to which certain database authorities have been
granted.
Microsoft SQL Server 2000 uses permissions to restrict access to objects and commands based on a user's identity
or group membership. Database permissions can be assigned directly to Windows NT and Windows 2000 users.
The commands grant and revoke can authorize or prevent users to access views and execute stored procedures or
Transact-SQL statements. Most Microsoft SQL Server permissions can be mapped to DB2 privileges.
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. Some privileges in DB2 are not available in Microsoft SQL Server,
such as package and schema privileges.
Microsoft SQL Server 2000 has a fixed server role called sysadmin with permissions to perform any activity in SQL
Server; user accounts can become members of the sysadmin role. The built-in login system administrator (SA) is
only provided for backward compatibility. 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.
Backup and restore
Microsoft SQL Server 2000 backup and restore commands can be used to back up and restore databases,
transaction logs, database files, and filegroups. The backup operation requires a permanent or temporary backup
device. The restore operation permits relocation of database files. Online backup, differential backup, and
point-in-time recovery are available. A set of backup history tables is maintained in the msdb database.
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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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.
Two types of incremental backups are available:
.
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.
Other backup and recovery enhancements available in DB2 UDB Version 8 include:
.
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.
The new recover database command in DB2 UDB V8.2 combines the functionality of the restore database and
rollforward commands. A database can be recovered to a specific point-in-time and there is no need to indicate
which database backup image must be restored or which log files are required to reach the specified point-in-time.
The recover database command also supports recovery operations to the end of the log files.
DB2 UDB Version 8.2 will now automatically choose the number of buffers, the buffer size, and the parallelism
settings for both backup and restore operations. The values chosen are based on the amount of memory available,
the number of processors available, and the database configuration. The objective is to minimize the amount of time
required for backup and restore operations to complete.
Online utility tools
Microsoft SQL Server 2000 configuration options can be managed using SQL Server Enterprise Manager or the
sp_configure system stored procedure. However, not all options can be configured using the SQL Server Enterprise
Manager. Some configuration options require you 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, the Configuration advisor, or the commands update
database configuration and update database manager configuration.
The bcp utility copies data to a SQL Server 2000 instance from a data file with a format specified by the user. This
utility uses row-level locks unless the TABLOCK hint is specified or table lock on bulk load option is enabled.
The load command in DB2 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.
Microsoft SQL Server 2000 does not provide a command or system stored procedure to defragment just a specific
table. Data is also reorganized when indexes are defragmented.
DB2 Version 8 now provides two methods of reorganizing tables 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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
The Microsoft SQL Server 2000 command DBCC INDEXDEFRAG defragments clustered and secondary indexes
on a table or view. This command reorders the leaf level pages only, and it matches the physical order to the
left-to-right logical order of the leaf nodes. It is an online operation and it is not recommended when the indexes are
heavily fragmented.
During Online index reorganization in DB2 V8, the user has the ability to read and update a table and its existing
indexes using the new reorg indexes command. 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.
Microsoft SQL Server 2000 allows statistical information regarding the distribution of values in a column to be
created. This statistical information can be used by the query processor to determine the optimal strategy for
evaluating a query.
When you create an index, Microsoft SQL Server 2000 automatically stores statistics about the distribution of values
in the indexed columns. Based on these statistics, the query optimizer estimates the cost of using the index for a
query. The AUTO_CREATE_STATISTICS database option is set to ON by default, which will automatically creates
statistics for columns without indexes that are used in a predicate. As the data is updated, Microsoft SQL Server will
automatically update statistics periodically using data sampling. The commands create statistics and update
statistics allows control on the amount of data sample, frequency of updates, and other specifics.
Table statistics are used by the DB2 query optimizer in selecting the best access plan for any given query, so it is
important that statistics remain current to accurately reflect the state of a table at any given time. As the activity
against a table increases, so should the frequency of statistics collection. Starting in Version 8.2, the RUNSTATS
utility provides the option to collect statistics on a sample of the data in the table by using the TABLESAMPLE
option. This feature increases the efficiency of statistics collection since sampling uses only a subset of the data, at
the same time the sampling methods ensure a high level of accuracy. Throttling of the RUNSTATS utility limits the
amount of resources consumed by the utility, based on the current level of database activity. When database activity
is low, the utility runs more aggressively; when database activity increases, the resources allocated to executing
RUNSTATS are reduced.
Tasks
The Microsoft SQL Server 2000 Agent services provide automation for alerts and administration tasks. Jobs can be
created using Microsoft Enterprise Manager and can be written in operating system commands, Transact-SQL
statements, Microsoft ActiveX Script, or replication tasks.
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
Microsoft SQL Server 2000 supplies system stored procedures to update and generate reports from system tables
and to perform administrative tasks. Several system stored procedure such as sp_help, are provided for backward
compatibility or to return new syntax information. These procedures are stored in the master database and are
owned by the System Administrator (SA). Microsoft SQL Server 2000 also offers SQL-DMO, SQL-NS, DTS, and
Replication Component APIs which are composed of OLE Automation objects that encapsulate either DDL or
system stored procedures.
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.
Microsoft Management Console (MMC) is a new user interface and framework for the management of Microsoft
server products. SQL Server Enterprise Manager is a graphical application to configure and manage Microsoft SQL
Servers and database objects, and it is the Microsoft SQL Server MMC snap-in. It runs on Windows 95, Windows
98, Windows NT, and Windows 2000.
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:
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
Add DB2 UDB systems, federated systems, DB2 UDB for z/OS and OS/390 systems, IMSysplexes,
instances, databases, and database objects to the object tree
.
Manage database objects: Create, alter, and drop databases, table spaces, tables, views, indexes, triggers,
and schemas
.
Manage data: Load, import, export, gather statistics, and reorganize data
.
Perform preventive maintenance by backing up and restoring databases or table spaces
.
Configure and tune instances and databases
.
Manage database connections, such as DB2 Connect servers and subsystems
.
Manage IMS systems, DB2 UDB for z/OS and OS/390 subsystems
.
Manage applications
.
Analyze queries using Visual Explain to look at access plans
.
Launch other tools such as the Command Editor and the Health Center
.
Generate SQL statements used to define database objects
.
Convert Control Center actions into scripts
The CC is available in three different views:
.
Basic. This view provides core DB2 UDB functionality, which includes the essential objects, such as
databases, tables, and stored procedures.
.
Advanced. This view displays all objects and actions available in the Control Center. This is the view that
you should select if you are working in an enterprise environment and want to connect to DB2 for z/OS or
IMS.
.
Custom. This view gives you the ability to tailor the object tree and the object actions to your specific needs.
The following figure illustrates the Control Center Basic View:
Figure 6. Control Center Main Window
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
The following DB2 tools are available from the Control Center toolbar:
.
Command Center, to issue DB2 database commands, SQL statements, and operating system commands
and to view their results
.
Task Center, to create and run operating system level and DB2 command scripts
.
Journal, to run, schedule, and delete jobs and view the recovery history log and messages log
.
License Center, to configure and to display the status of the DB2 product licenses
.
Replication Center new in DB2 Version 8, to design set up and manage a replication environment
.
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
.
Data Warehouse Center, to manage Data Warehouse objects
.
Information Catalog Center, to manage business metadata
.
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.
.
Development Center, to develop stored procedures, user-defined functions, and structured types
.
Information Center, to search for help on tasks, commands, and information in the DB2 library, and also to
update local documentation
The DB2 advisors, 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), back
up databases, create databases, create table spaces, create tables, bulk load data, design databases, monitor
workload performance, configure database manager and database parameters to improve performance, restore
databases, configure database logging, set up Activity Monitor, and set up HADR.
Database consistency
Microsoft SQL Server 2000 provides dbcc statements to check database consistency. These statements perform
physical and logical consistency checks on a database and can fix certain problems. In earlier versions, it was
recommended that dbcc statements be run prior to a backup. In Version 7, this is no longer recommended prior to
doing a backup, but is still advised after a system failure.
The dbcc statements are grouped into four categories: maintenance, miscellaneous, status, and validation.
Maintenance tasks are performed on a database, index, or filegroup. Miscellaneous tasks include enabling row-level
locking or removing a dynamic-link library (DLL) from memory. Status checks are performed on system information,
such as current statistics, trace flags, user options, and last statement sent form a client. Validation operations are
performed on a database, table, index, catalog, filegroup, system tables, or allocation of database pages. Also for
database maintenance, the sqlmaint utility performs DBCC checks, backs up a database and its transaction log,
updates statistics, and rebuilds indexes on one or more databases.
In DB2 Version 8 the command INSPECT was introduced in order to inspect the 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. It will write out unformatted
inspection data results to the results file specified, and if there are no errors the file is deleted, otherwise it remains
in the diagnostic data directory path. The inspection result data will require to be formatted out with the utility
db2inspf.
Another tool in DB2 UDB, db2dart can also be used to verify that the architectural integrity of a database is correct. It
is not 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:
.
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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
The use of the INSPECT command is recommended over the db2dart tool, since the INSPECT command can be
run online and does not impede server availavility. Sometimes, DB2 support will direct customers to use db2dart to
remedy very specific problems, working together with support is the best way to take advantage of this powerful tool.
Although Microsoft SQL Server dbcc statements and DB2 UDB tools produce different output information, the
following table only attemps to map some of the functionality between dbcc commands and db2dart commands:
Microsoft SQL Server dbcc DB2 UDB db2dart
dbcc checkdb (database-name) db2dart database-name /DB 1
dbcc checktable (table-name) db2dart database-name /T /TSI tablespace-id /TN table-name
dbcc checkfilegroup (filegroup-name | filegroup-id)2 db2dart database-name /TS /TSI tablespace-id 3
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
2 dbcc checkfilegroup checks the integrity of the filegroup and all its tables
3 db2dart /TS checks the table space and all its tables. Of the additional options, /TSC checks only the constructs of atable space, and /TSF checks all table space files and containers
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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Implementation differences
This section describes implementation features available in Microsoft SQL Server 2000 and DB2 UDB Version 8.2
for UNIX, Linux, and Windows.
Isolation levels
Microsoft SQL Server 2000 supports four isolation levels: Read Uncommitted, Read Committed, Repeatable Read,
and Serializable. If the level is set to Read Uncommitted, dirty reads, nonrepeatable reads, and phantom values can
occur. If the level is set to Read Committed, dirty reads cannot occur but nonrepeatable reads and phantom values
still occur. At the Repeatable Read level, nonrepeatable reads cannot occur but phantom values still occur.
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.
Microsoft SQL Server DB2 UDB
Read Uncommitted Uncommitted Read
Read Committed (default) Cursor Stability (default)
Repeatable Read Read Stability
Serializable 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.
DB2 permits the deferral of row locks for Cursor Stability or Read Stability isolation scans in some situations until a
record is known to satisfy the predicates of a query in order to improve concurrency. By default, when row-locking is
performed during a table or index scan, DB2 locks each row that is scanned before determining whether the row
qualifies for the query or not. It may be possible to defer row locking until after it is determined that a row qualifies for
a query in order to improve the concurrency of scans.
By enabling the DB2_EVALUNCOMMITTED registry variable, we can take advantage of lock deferral. Once this
variable is enabled, predicate evaluation can occur on uncommitted data. This means that a row that contains an
uncommitted update may not satisfy the query, whereas if the predicate evaluation waited until the updated
transaction completed, the row may satisfy the query. Additionally, uncommitted deleted rows are skipped during
table scans. DB2 will skip deleted keys in type-2 4 index scans if the DB2_SKIPDELETED registry variable is also
enabled.
Statement isolation levels
In Microsoft SQL Server 2000, the with keyword in the from clause for a select statement indicates table hints. The
table hints are group by lock granularity, and isolation level. The values that can be specified as isolation level hints
are READCOMMITTED, READUNCOMMITTED, REPEATABLEREAD, and SERIALIZABLE.
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_PAYFROM employeeORDER BY TOTAL_PAYWITH RS
Locking mechanisms
Microsoft SQL Server 2000 has three levels of locking: row, page, and table locks. The default is row-level locking.
Lock escalation is automatically performed from row locks and page locks into table locks when a transaction
exceeds its escalation threshold. Lock escalation thresholds are determined dynamically by Microsoft SQL Server
and cannot be configured.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
The locks option can be set to the maximum number of available locks, limiting the amount of memory Microsoft
SQL Server uses for locks. The default setting is 0, which allows the server to dynamically allocate memory for locks
(up to 40% of the total memory allocated by the server).
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.
System databases
The master database controls the operation of Microsoft SQL Server 2000 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.
The msdb database is used by Microsoft SQL Server 2000, SQL Server Enterprise Manager, and SQL Server
Agent to store data for alerts, scheduled tasks, and backup and restore history information. 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 Healt Center can be used to display and work with current alerts.
The Microsoft SQL Server 2000 distribution database is used for replication and stores data including transactions,
snapshot jobs, synchronization status, and replication history information. All servers that are configured for remote
distribution or combined Publisher/Distributor have a distribution database.
DB2 replication components use control tables to communicate with each other and to manage replication requests,
such as defining and managing replication sources and targets, capturing changes, replicating changes, and
tracking how many changes are replicated and how many remain to be done. The control tables for the Capture
program reside on the source database, and the control tables for the Apply program reside on the target
databases. The Replication Alert Monitor reads all the control tables and notifies contacts when the alert condition
occurs.
The Microsoft SQL Server 2000 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. Users that are already created can have access to all databases using group membership or trusted
connections.
The Microsoft SQL Server 2000 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).
Microsoft SQL Server 2000 provides two sample databases called pubs and Northwind, which can be optionally
installed. 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.
SQL standard compliance
Microsoft SQL Server 2000 features comply with the entry level of ANSI SQL92 standard and with the Federal
Information Processing Standards (FIPS 127-2) as established by the US National Institute of Standards and
Technology (NIST). The commands SET FIPS_FLAGGER and SET ANSI_DEFAULTS change the level of
compliance and SQL-92 behavior. The length of Identifiers is limited to 128 characters.
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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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. The
maximum SQL statement lenght is 2MB,
Tables
The full specification of a table name in the Microsoft SQL Server create table command includes database name,
owner and table name. The database name is optional and defaults to the current database name. However, if a
database name is present the database must exists and the current connection login must be associated with an
existing user ID on such database. This command can be used to also create global and local temporary tables.
The ON and TEXTIMAGE_ON clauses specify a file_group where the table or the large columns (text, ntext, and
image) are going to be stored.
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 Microsoft SQL Server 2000 and
DB2 UDB:
Description Microsoft SQL Server DB2 UDB
Table space/database file page size 8K 4K, 8K, 16K, 32K
Maximum length of a row 8,060 bytes 32,677 bytes 1
Maximum number of columns in a table 1,024 1,012 2
Maximum number of indexes in a table 249 non-clustered
1 clustered
32,767 or storage
Maximum number of tables in a database Limited by number of database
objects 3
65,534 4
Longest index key 900 bytes 1,024 bytes
Maximum number of constraints on a table 250 Unique keys
253 Foreign Keys
storage
Maximum number of columns in a view 1,024 5,000 5
Maximum number of tables referenced in a
select or view
256 storage
Maximum number of columns in an ORDER
BY clause
Limited to total length of
8060 bytes
1,012 2
Maximum total length of columns in an
ORDER BY clause
8,060 bytes 32,677 bytes 1
1
This value is based on a table space page size of 32K, and varies according to the page size.
2
This value is based on a table space page size of either 8K, 16K, or 32K.
3
The total number of database objects can not exceed 2,147,483,647.
4
This limit is for SMS table spaces only
5
This maximum can be achieved using a join in the CREATE VIEW statement.
Temporary tables
Microsoft SQL Server 2000 supports temporary tables, which are stored in the tempdb database. There are two
types of temporary tables, local and global. The names of local temporary tables start with a number sign (#) and
local tables are visible only in the current session (connection), while the names of global temporary tables start with
a double number sign (##) and global tables 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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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_tempLIKE employeeON 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
undo logging to support rollbacks.
A declared temporary table is semantically equivalent to a Microsoft SQL Server 2000 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
Microsoft SQL Server 2000 indexes can now specify ascending or descending order. In the previous versions, 6.5
and 7, there was no clause in the command create index to specify ordering. In DB2 UDB, indexes can be defined in
ascending or descending order, and you can allow both forward and reverse scans by specifying allow reverse
scans in the create index statement. The ordering property can be useful in executing queries with the order by and
group by clauses.
Microsoft SQL Server 2000 uses the same B-tree model to represent cluster and non cluster indexes, except for the
following two differences: non cluster indexes are not ordered physically according to the index key, and the leaf
nodes consist of index rows rather than data pages.
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.
Microsoft SQL Server 2000 has a fillfactor clause on the create index command to indicate a percentage value for
how full a leaf level page of the index will be when a new index is created on existing data. The default is 0, which
indicates to the server to completely fill the leaf level pages and to leave space in non-leaf pages for at least one
row.
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%.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Views
Indexes for views can be created under Microsoft SQL Server 2000 under certain conditions, as a way to materialize
views. The first index created on a view must be a unique clustered index; after that any additional nonclustered
indexes can be created. Creating a unique clustered index on a view results in storing the result set in the database
when the view is executed. The query optimizer determines if a query will benefit from using any indexes defined on
views or on base tables. The optimizer can choose an indexed view even when it is not referenced in the from
clause. The options NOEXPAND or EXPAND VIEWS can be specified to force or prevent the optimizer from using
indexed views.
DB2 UDB indexes can only be defined on base tables. Materialized query tables (MQT) are introduced in DB2 V8
and allow view materialization. A MQT is a table whose definition is based on the result of a query, and whose data
is in the form of precomputed results that are taken from one or more tables on which the materialized query table
definition is based. If the SQL compiler determines that a query will run more efficiently against a MQT than the base
table, the query executes against the MQT. This kind of optimization can be faster than additional indexes on a view.
To create a MQT, use the CREATE TABLE statement with the AS fullselect clause and the IMMEDIATE or
REFRESH DEFERRED options.
The summary tables, also known as automatic summary tables (ASTs) are considered to be a special type of MQT
whose fullselect contains a GROUP BY clause summarizing data from the tables referenced in the fullselect. The
summary tables or ASTs were available in previous versions of DB2.
IDENTITY columns
Microsoft SQL Server IDENTITY property provides system-generated values in sequence. The IDENTITY property
can be defined on only one column per table of the following types: numeric with a scale of 0, decimal with a scale of
0, integer, smallint, and tinyint. A seed value and an increment value can be specified; if neither is provided
IDENTITY columns start with 1 and increment by 1. Microsoft SQL Server also supports the uniqueidentifier data
type. The value of a column defined as uniqueidentifier can be generated using the NEWID function or defining a
string constant in hexadecimal digits.
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 TABLE t1
(c1 CHAR(30),
c2 DOUBLE,
c3 INT NOT NULL GENERATED ALWAYS as identity,
(START WITH 100, INCREMENT BY 5))
Computed columns
Microsoft SQL Server 2000 computed columns are not stored in the table and, as the name suggests, are
calculated using the expression that defines the column value. Indexes can be created on computed columns, but
there are restrictions, such as the expression should be deterministic.
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 non-unique 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.2 from Microsoft SQL Server 2000
SELECT c1 + c2 FROM t1 WHERE (c1 + c2) * c1 > 100 ;
can be rewritten as:
SELECT c3 FROM t1 WHERE c3 * c1 > 100 ;
Constraints
Microsoft SQL Server 2000 allows the creation of defaults and rules for columns in a database to define default and
check constraints as a backward compatibility feature. The standard way to define check and default constraints is
by using check and default clauses in the create table or alter table commands. Rules cannot be defined for an SQL
Server-supplied data type or for a column of text, image, or timestamp type and only one rule can be bound to a
column.
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.
The Microsoft SQL 2000 Server integrity constraints definition can indicate whether the index to be created is
nonclustered (the 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.
Microsoft SQL Server 2000 referential constraints have two possible actions: CASCADE and NO ACTION (the
default). Previous versions of Microsoft SQL Server cannot cascade changes through related tables in the database;
you need to use triggers to achieve these actions.
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.
Scrollable cursors
Microsoft SQL Server 2000 has support for all ANSI-style cursors: static, dynamic, forward only, and keyset-driven.
Transact-SQL includes support for INSENSITIVE and SCROLL cursor behavior and for all fetch options (FIRST,
LAST, NEXT, PRIOR, RELATIVE, and ABSOLUTE). Cursor support is available through the following APIs: ADO
(Microsoft ActiveX Data Object), OLE DB, ODBC (Open Database Connectivity), and DB-Library.
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
Microsoft SQL Server 2000 has the ability to specify outer joins on the FROM or WHERE clause. The ANSI-style
join syntax is supported on the FROM clause for left, right, and full outer joins. The specific syntax for the right and
left outer joins of *= and =* on the WHERE clause is only supported for backward compatibility.
The DB2 syntax for joins is ANSI-style, with the operators INNER, LEFT [OUTER], RIGHT [OUTER], and 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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
SELECT deptno, deptname, empno, lastnameFROM department LEFT OUTER JOIN employeeON mgrno= empno
Transactions
Microsoft SQL Server supports explicit transactions defined by the statements begin transaction, save transaction,
commit transaction, and rollback transaction. If implicit_transaction mode is enabled, SQL 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.
Microsoft SQL Server supports savepoint as a mechanism to roll back portions of transactions. The command save
transaction creates a savepoint that can be rolled back later with the command rollback 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:
.
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;
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).
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;
DB2 does not support savepoint-related SQL statements within the definition body of a trigger.
DB2 supports nested savepoints, this means a savepoint can be set up within another savepoint. There is no limit
to the number of savepoints and the number of nested savepoint levels. With nested savepoints, an application can
have multiple levels of savepoints active simultaneously, and the application can roll back to any active savepoint,
as required. A rollback to a particular savepoint statement also releases any active nested savepoints within the
savepoint being rolled back.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Savepoints offer the following advantages over compound SQL blocks:
.
Enhanced control of transactions, if a statement within the scope of a savepoint fails, the application can roll
back all of the statements in the scope of the savepoint, but commit the work performed by statements
outside of the scope of the savepoint.
.
Less locking contention, locks are adquired as each statement in the scope of the savepoint is issued.
.
Improved integration with application logic, for example an application can either roll back the work
performed within the savepoint or commits the work performed in the entire transaction, which may include
SQL statements that have becomed part of the savepoint scope.
Triggers
Microsoft SQL Server 2000 triggers are coded using Transact-SQL and are stored in the database itself. SQL
Server allows nested triggers, up to 32 levels. Direct or indirect recursion in triggers is available as a database
configuration option. Triggers can be activated after or instead of the triggering event. INSTEAD OF triggers
override the triggering action and are executed prior to applying any constraints defined on the table. Multiple
AFTER triggers per table can be coded, but only one INSTEAD OF trigger. Multiple user actions can be specified for
all triggers. INSTEAD OF triggers can be defined on views, but AFTER triggers can not. The system stored
procedure sp_settriggerorder can be used to set the first and last AFTER triggers to be executed; any other AFTER
triggers are executed in random order.
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. 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. The maximum depth of cascaded triggers is 16. Triggers can invoke SQL and
external stored procedures, and User-Defined Functions (UDFs) by executing the CALL statement.
The Microsoft SQL Server deleted and inserted logical tables are transition tables, which 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 partsREFERENCING NEW AS n_rowFOR EACH ROW MODE DB2SQLWHEN (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
Microsoft SQL Server 2000 INSTEAD OF triggers override the triggering action and constraints are not checked
prior to the trigger body execution. There are also restrictions such as, updateable views and tables with a foreign
key defining a cascade action are not valid object specifications when creating INSTEAD OF triggers.
INSTEAD OF triggers are supported since 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
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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_INSERTINSTEAD OF INSERT ON EMPV
REFERENCING NEW AS NEWEMP
DEFAULTS NULL FOR EACH ROW MODE DB2SQLINSERT INTO EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME,WORKDEPT,
PHONENO, HIREDATE)
VALUES(EMPNO, FIRSTNME, MIDINIT, LASTNAME,
COALESCE((SELECT DEPTNO FROM DEPARTMENT AS DWHERE D.DEPTNAME = NEWEMP.DEPTNAME),
RAISE_ERROR('70001', 'Unknown department name')),
PHONENO, HIREDATE)
Stored procedures
Microsoft SQL 2000 Server stored procedures are coded using Transact-SQL and are stored in the database. The
maximum number of parameters in a stored procedure is 1024. Microsoft SQL Server also has extended stored
procedures that are functions contained in a dynamic link library; these procedures can be loaded and executed in a
similar way to Transact-SQL stored procedures. Extended procedures run within the address space of Microsoft
SQL Server, and only by storing them in a separate instance they will be able to run in a separate memory space.
However, distributed queries may be required if extended procedures reside on a separate instance. Extended
stored procedures can only be programmed using the SQL Server Open Data Services API.
DB2 UDB stored procedures can be coded in SQL or in compiled libraries using third-generation languages,
including C, C++, COBOL, .NET common language runtime languages, OLE, and Java (SQLJ/JDBC). The stored
procedures code in a supported third generation language are called external stored procedures. 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.
The following figure illustrates the architecture for DB2 stored procedures in SQL or a third-generation language:
Figure 7. DB2 UDB Stored Procedures
One a stored procedure is created, they are invoked by executing the CALL from client applications, external and
SQL stored procedures, triggers, dynamic compound statements, and the Command line processor (CLP).
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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
After the create procedure statement is executed, its procedural statements are converted to a native representation
that is stored in the database catalogs, as is done with other SQL statements. When an SQL procedure is called,
the native representation is loaded from the catalogs and the DB2 engine executes the procedure.
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 = defaultvalueDECLARE 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>;
@@rowcountGET 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.
DB2 allows the user to extract and install external stored 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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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:
.
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:
.
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
Microsoft SQL Server 2000 supports user-defined functions through the statement CREATE FUNCTION. Earlier
versions did not include this feature. Two types of user-defined functions available: scalar and table-valued. The
latter returns a table. Multiple statements can be defined enclosed by BEGIN/END keywords.
DB2 UDB has five different types of functions that can be created using the CREATED FUNCTION statement:
.
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.2 from Microsoft SQL Server 2000
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 ACTIONDETERMINISTIC
RETURN
SELECT EMPNO, LASTNAME, FIRSTNME
FROM EMPLOYEE
WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO
External UDFs must be coded and compiled before executing the create function statement that registers them in
the database. For a Java UDF, 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. The following examples include the Java code and the
create function statement for the stringlen( ) UDF:
import java.lang.*; // for String classimport COM.ibm.db2.app.*; // UDF and associated classes
class UDFsrv extends UDF
{
public void scalarUDF(String inputStr, int outputStrLen)
throws Exception
{
try
{ set(2, inputStr.length())
;
}
catch (Exception e)
{ throw e;
}
}
}
CREATE FUNCTION stringlen ( VARCHAR(20) , INT )
RETURNS INT
FENCED VARIANT NO SQLNO 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:
.
SQL table and scalar UDFs
.
UDFs that read WebSphere MQ 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 support
Microsoft SQL Server 2000 support for XML consists of the ability to access the server using a URL, support for
XML-Data schemas, and the ability to specify XPath queries against these schemas, store XML data using functions
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
and stored procedures, and compose XML data using the SELECT statement with the FOR XML clause. The
Microsoft SQL Server 2000 OLE DB provider (SQLOLEDB) allows XML documents to be set as command text and
to return result sets as a stream.
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.
A new standard SQL/XML function in DB2 UDB Version 8.2, XMLSERIALIZE (with the CONTENT option), allows
the conversion of an XML data type value into a result string data type that is appropriate for the length of the XML
output. The result type can be specified as CHAR or VARCHAR for better performance. The XML data type is an
internal representation of XML and can be used only as input to functions that accept this data type as input. XML is
a transient data type that cannot be stored in the database or returned to an application.
Serialization is the inverse operation of parsing; it is the process of converting a parsed XML value into a textual
XML value. XMLSERIALIZE converts an XML expression into an SQL string value that, in turn, can be bound out to
host character variables.
Global variables
Microsoft SQL Server provides global variables to report system or connection information. Microsoft SQL Server
2000 substitutes scalar functions for some global variables. 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 UDFs can be defined.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Programming interfaces
DB2 programming interfaces overview
DB2 provides the following programming interfaces to develop applications:
.
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.
.
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).
.
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.
.
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.
.
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.
.
ADO.NET Interface
Development of ADO.NET applications using DB2 .NET Data Provider, OLE DB .NET Data Provider, or
ODBC .NET Data Provider to access DB2 family of databases and other datasources.
.
IBM or third-party tools
Applications can also be developed using tools such as Net.Data, Excel, Perl, 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 8. Client/Server Architecture
The client application requires the installation of DB2 Run-Time Client, DB2 Run-Time Client Lite, or DB2
Administration Client to allow database connectivity.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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.
In addition to dynamic SQL, DB2 CLI offers the following advantages:
.
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.
.
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.
.
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.
.
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.
.
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.
.
Extended data conversion - Automatically converts data between SQL and C data types.
.
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.
.
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.
.
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 that 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 9. 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:
.
Support for client applications and applets written in Java using JDBC
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
Precompile and binding support for client applications and applets written in Java using SQLJ
.
Support for Java UDFs and stored procedures on the server
DB2 UDB Version 8.2 supports JDK 1.4 on all supported workstation operating system environments, except on AIX
4.3 and Linux IA64, where IBM Developer Kit 1.3.1 is still required. DB2 UDB for Solaris will ship with JDK 1.4.2.
DB2 UDB for all other supported workstation operating systems will ship with JDK 1.4.1 (or the equivalent
vendor-named Java developer kit).
Depending upon your operating system, the IBM Developer Kit for Java (SDK) 1.3.1, SDK 1.4.1, or SDK 1.4.2 is
required for DB2 to run the DB2 Control Center or to create and run Java applications, including stored procedures
and user-defined functions. The IBM SDK is supported, as well as the HP-UX JDK and the Solaris Operating
Environment JDK. For the most up-to-date SDK information for application development, see the topic platform
support on the DB2 Java application development Web page ibm.com/software/data/db2/udb/ad/v8/java/.
The DB2 Universal JDBC Driver is an entirely new driver written completely in Java that uses the DRDA protocol for
client/server communications. Therefore, you can expect some differences in behavior between this driver and other
drivers in prior versions to DB2 Version 8.2. These differences are documented under the topic JDBC differences
between the DB2 Universal JDBC Driver and other DB2 JDBC drivers, in the Application Development Guide:
Programming Client Applications. This new driver is provided in the db2jcc.jar file in the sqllib/javadirectory. It is a single driver that includes JDBC type 2 and JDBC type 4 behavior, as well as SQLJ support.
When an application loads the DB2 Universal JDBC Driver, a single driver instance is loaded for type 2 and type 4
implementations. The application can make type 2 and type 4 connections using this single driver instance. The type
2 and type 4 connections can be made concurrently. DB2 Universal JDBC Driver type 2 driver behavior is referred
to as DB2 Universal JDBC Driver type 2 connectivity. DB2 Universal JDBC Driver type 4 driver behavior is referred
to as DB2 Universal JDBC Driver type 4 connectivity.
The following figure shows how a JDBC application connects to a datasource using the DB2 Universal JDBC Driver
type 4 connectivity. The application connects using the DriverManager interface, which is available for all levels of
JDBC. Connecting using the DataSource interface is available with JDBC 2.0 and above.
Figure 10. Java application implementation in DB2 Universal JDBC Driver type 4 connectivity
The DB2 JDBC type 2 Driver for Linux, UNIX, and Windows (also known as DB2 JDBC type 2 legacy driver) is
deprecated as of DB2 Version 8.2. The DB2 JDBC type 3 driver for Linux, UNIX and Windows (also known as DB2
JDBC net driver) is deprecated as of DB2 Version 8.1.
The DB2 Universal JDBC Driver supports these JDBC and SQLJ functions:
.
Most of the methods that are described in the JDBC 1.2 and JDBC 2.0 specifications, and some of the
methods that are described in the JDBC 3.0 specifications. See the topic Comparison of driver support for
JDBC APIs in the Application Development Guide: Programming Client Applications.
.
SQLJ statements that perform equivalent functions to all JDBC methods.
.
Connections that are enabled for connection pooling. WebSphere Application Server or another application
server does the connection pooling.
.
Implementation of Java user-defined functions and stored procedures (Universal Type 2 Connectivity only).
.
Global transactions that run under WebSphere Application Server Version 5.0 and above.
.
Support for distributed transaction management. This support implements the Java 2 Platform, Enterprise
Edition (J2EE) Java Transaction Service (JTS) and Java Transaction API (JTA) specifications, which
conform to the X/Open standard for global transactions (Distributed Transaction Processing: The XA
Specification, available from www.opengroup.org).
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
For more information on Java application development and the JDBC specification, see the DB2 Universal
Database Java Web site at .ibm.com/software/data/db2/udb/ad/v8/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 Universal JDBC driver type 2 connectivity offers support for
distributed transaction processing that conforms to the XA specification. This support implements the J2EE Java
Transaction Service (JTS) and Java Transaction API (JTA) specifications on DB2 UDB for Linux, UNIX, and
Windows systems only.
The following illustration shows the IBM J2EE application model with all three fundamental parts: components,
containers, and connectors:
Figure 11. J2EE Application model
Embedded SQL differences
Microsoft SQL Server 2000 provides precompilers for C, Microsoft Embedded SQL for the C development
environment (ESQL/C).
DB2 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.
ESQL/C has the data structure SQLCA to communicate between the Microsoft SQL Server and the application, and
the fields SQLCODE and SQLSTATE can be explicitly declared as application variables. ESQL/C allows the use of
all Transact-SQL statements, functions, and control-of-flow language in embedded SQL with a few exceptions. The
ANSI/ISO SQL and Transact-SQL transaction modes are available in ESQL/C. The latter provides a save
transaction or begin transaction statement. In ESQL/C, the connect statement is used to establish a connection
between an application program and a specific database with a supplied user name and password.
When using DB2 precompilers, 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;
DB2 SQL and Transact-SQL extensions are different; some Transact-SQL statements are not valid in DB2 UDB.
DB2 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.
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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Programming APIs differences
Microsoft SQL Server 2000 provides the DB-Library API for C, DAO for Microsoft Visual Basic, and Microsoft Visual
C++, and ADO and RDO for Microsoft Visual Basic to write client applications for the Windows operating systems.
The ODBC API is also provided for C, C++, and Microsoft Visual Basic to write ODBC client/server applications that
connect to a database server using the ODBC SQL Server driver. The ODBC Driver supports the ODBC 3.51
specification and ODBC 2.5 or earlier versions following the definitions in the 3.51 specification.
The Microsoft SQL Server Database Services API includes: SQL Distributed Management Objects (DMO) to write
server administration programs; Open Data Services to write server applications for client/server systems or
extended stored procedures; Distributed Transaction (MS DTC) to define distributed transactions; and Data
Transformation Services (DTS) to execute complex data transformations. Meta Data Services is intended to store
metadata, and it is designed to be integrated with other tools and applications such as OLAP and data warehousing.
Analysis Services API gives applications access to the OLAP and data-mining facilities.
Microsoft Visual Studio .NET Framework Class Library contains the System.Data namespace, which consists
mostly of the classes that enable an application to manage data from multiple data sources. This namespace
contains the SQL Server .NET Data Provider and the OLE DB .NET Data Provider which allow direct access to data
sources. Each .NET data provider has a corresponding DataAdapter that can be used as a bridge between a data
source and a DataSet component.
Microsoft SQL Server 2000 includes a SQL Server WMI provider to allow WMI applications get information on SQL
Server databases and instances.
DB2 UDB supports applications written on Microsoft Visual Basic and Microsoft Visual C++, database applications
that conform to the Data Access Object (DAO) and Remote Data Object (RDO) specifications using Microsoft OLE
DB to ODBC Bridge or the IBM OLE DB Provider for DB2.
The IBM OLE DB Provider for DB2, whose provider name is IBMDADB2, enables OLE DB consumers to access
data on a DB2 server. This support gives OLE DB-based applications the ability to extract or query DB2 data using
the OLE interface. If DB2 Connect is installed, these OLE DB consumers can also access data on a host DBMS
such as DB2 for MVS, DB2 for VM/VSE, or SQL/400. The IBM OLE DB Provider for DB2 complies with Version 2.5
of the Microsoft OLE DB specification.
The DB2 .NET Data Provider is an extension of the ADO.NET interface that allows .NET applications to access a
DB2 database through a secure connection, execute commands, and retrieve result sets. In order to develop and
run applications that use the DB2 .NET Data Provider, the .NET Framework Version 1.0 or 1.1 is required. The DB2
.NET Data Provider allows .NET applications to access database on DB2 UDB Version 8 for Windows, UNIX, and
Linux-based computers and all supported DB2 family databases through DB2 Connect.
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 tasks,
such as creating, activating, backing up, or restoring a database.
Since DB2 supports ODBC and OLE DB, the OLE DB .NET Data Provider and the ODBC .NET Data Provider allow
direct access to DB2 databases from Microsoft Visual Studio .NET applications. Furthermore, IBM announced last
year that it has joined Microsoft's Visual Studio .NET Integration Program (VSIP) as part of IBM's open strategy.
This support for VSIP will enable developers to use .NET Framework for application development and DB2 as
database server. DB2 will have a unified interface for accessing enterprise data and deploying business logic
directly into the database, thus ensuring seamless integration with DB2 and Microsoft Visual Studio .NET.
The DB2 Version 8 WMI provider allows WMI applications to monitor DB2 server services, enumerate and create
databases, configure operational settings and perform database backup, restore, and roll-forward operations.
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 DB2 UDB Version 7.2,
they can also be written in Microsoft Visual Basic. For more details, see the topic 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.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
DB2 OLAP Server desktop graphic interface can be used for easily mapping relational data sources to OLAP
structures to create OLAP applications. The Information Catalog Manager helps to locate data within an
organization, and uses an information catalog database to store information about data (metadata). The Information
Catalog Manager provides C language API calls for programming applications.
These programming interfaces are all very different, but the effort of porting applications to DB2 can be significantly
reduced by using conversion tools (see the section Conversion Tools of this paper).
When porting application to access DB2 databases, it is important to consider using server-side features such as
MDC tables or MQT to improve database performance with no impact on application development. See section
Implementation Issues for details on these DB2 server-side features.
Web services applications
SQL Server 2000 Web Services Toolkit contains tools, code samples, white papers, and SQLXML 3.0 extensions
needed to build XML Web services and Web applications with SQL Server 2000. SQLXML 3.0 is an extension to the
built-in XML capabilities of Microsoft SQL Server 2000 to create XML Web services from SQL Server stored
procedures or server-side XML templates. SQLXML 3.0 also includes extensions to the .NET Framework that adds
SQLXML capabilities to the programming languages supported by Microsoft Visual Studio .NET, including C# and
Microsoft Visual Basic .NET
DB2 can be accessed as a Web service provider, and it is usually teamed with WebSphere family products to
provide a complete Web services framework. Web services object runtime framework (WORF) is part of DB2 UDB
ESE Version 8, and WebSphere Studio Version 5. WORF is in the following path in DB2 Universal Database
Version 8: <DB2 UDB installed location>/samples/java/Websphere/dxxworf.zip.
DB2 offers an easy way to develop data-intensive Web services applications through the 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. DADx files can be created quickly without
any Java programming. For details, see the DB2 UDB Application Development Guide.
Web services continues to be a focal point as the new application development paradigm. DB2 UDB Version 8.2
has enhanced Web service support as a provider. SOAP engine transparency is now provided, allowing more
choices of soap engines to be plugged into the run time. With the new dynamic query capability, the Web service
client can determine at invocation which query should be executed, in addition to the fixed transactions/queries in
DADXes. A tracing component has also been added to make it easier to diagnose errors in the provider. Another
important feature is the support for Web service inspection language (WSIL) so that users can easily explore the
Web services that are offered in the DB2 Web services provider.
For a demonstration of DB2 as a Web service provider, see the IBM Video Central for e-business tutorial at
ibm.com/developerworks/db2/library/samples/videocentral/videocentral.html.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Terminology map
The following table compares the basic DB2 UDB administration-related terms to the equivalent terms used by
Microsoft SQL Server.
DB2 Universal Database Microsoft SQL Server
Physical Layer
Table space Filegroup
SMS table space Database file with FILEGROWTH > 0
DMS table space Database file with SIZE >0, FILEGROWTH = 0
Container (raw or file) Database file (raw or operating system file)
Logical Layer
Server Server
Instance (One or more; each instance manages one or more
databases)
n/a
Database Database
Database directories (created by DB2) SQL Enterprise Manager registry
Node directory (created by DB2) SQL Enterprise Manager registry
Database Manager configuration file Windows NT Registry
Database configuration file Windows NT Registry
Catalog tables System tables (master database and database)
Database Objects
Schema Schema
Table Table
Table constraint Rule and table constraint
View View
Index Index
Recovery log Transaction log
Archive log Transaction log dump
Users and user groups (operating system) NT users and groups, Database users, groups and roles
Package n/a
Sample database (called sample) Sample database (called pubs)
Administration / Usage
Control Center Microsoft management Console, SQL Enterprise Manager
Tables assigned to table spaces, containers assigned to table
spaces
Tables assigned to filegroups, database files grouped into
filegroups.
Administration commands and statements System stored procedures
Binding (binds a DB2 utility or a program to a database so it
can be used)
n/a
Backup database Backup database
Archive online log files Backup log
Restore from backup Restore database
Roll-forward recovery Restore log
Crash recovery Automatic recovery
Run statistics Update statistics, Create statistics
Load, Import, Export bcp
Command Processor, Visual Explain SQL Server Query Analyzer
Command Line Processor (CLP) ISQL
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
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 All All
Level 1 Functions All All
Level 2 Functions All All, except for SQLDrivers()
Additional DB2 CLI Functions All, functions can be accessed by
dynamically loading the DB2 CLI library.
SQLSetConnection()
SQLGetEnvAttr()
SQLSetEnvAttr()
SQLSetColAttributes()
SQLGetSQLCA()
SQLBindFileToCol()
SQLBindFileToParam()
SQLExtendedBind()
SQLExtendedPrepare()
SQLGetLength()
SQLGetPosition()
SQLGetSubString()
SQL 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
C Data Types All the types listed for DB2 CLI. 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.2 from Microsoft SQL Server 2000
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 Mapped to X/Open SQLSTATES with
additional IBM SQLSTATES, with the
exception of the ODBC type 08S01.
Mapped to X/Open SQLSTATES with
additional IBM SQLSTATES
Multiple connections per application Supported Supported
Dynamic loading of driver Supported Not applicable
** Only supported on Windows platform
The following table map DB2 isolation levels to ODBC transaction isolation levels:
IBM Isolation Level ODBC Isolation Level
Cursor Stability SQL_TXN_READ_COMMITTED
Repeatable Read SQL_TXN_SERIALIZABLE_READ
Read Stability SQL_TXN_REPEATABLE_READ
Uncommitted 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.2 from Microsoft SQL Server 2000
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 version 1/3 is designed to migrate data and the query and procedure language from the following source
database management systems:
.
Informix Dynamic Server Versions 7.3 and 9
.
Informix Extended Parallel Server (XPS) (partial support)
.
Microsoft SQL Server, Versions 6, 7, and 2000
.
Oracle 8i and partial support for Oracle 9i
.
Sybase Enterprise, versions 11 and 12
to the following target systems:
.
IBM DB2 UDB Universal Database for Workstation platforms, Version 8.1 or later
.
IBM DB2 UDB Universal Database for i5/OS, Version 5.2 or later
.
IBM DB2 UDB Universal Database for z/OS, Version 8
It runs on AIX, Linux, Sun Solaris, and Windows. The only language available is English.
Figure 12. Toolkit Menu Specify Source tab
The toolkit includes the following features:
.
A wizard to migrate simple databases
.
A full-featured GUI interface (Toolkit) to further refine the migration and tailor options for complex databases
conversions
.
The migration source can be a database data source or a DDL script
.
Converts Transact-SQL or PL/SQL object definitions (including stored procedures, user-defined functions,
triggers, packages, tables, views, indexes and sequences)
.
Converts Transact-SQL or PL/SQL scripts to DB2 scripts
.
Generates and run Scripts to unload data from source and upload data to DB2
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
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:
.
Create a project
.
Specify source (ODBC DSN or SQL script)
.
Convert - generates DB2 scripts and reports error messages
.
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
.
Generate Data Transfer scripts
.
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 developerWorks DB2 library contains many references to material related to migration to DB2 topics,
specifically a tutorial and a technical article about MTK:
.
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:
ibm.com/developerworks/db2/library/tutorials/0209jarzebowicz/index.html
.
Automate the Switch to DB2 Using the IBM DB2 Migration Toolkit. This technical article explains how
MTK works: ibm.com/developerworks/db2/library/techarticle/0209jarzebowicz/0209jarzebowicz.html.
.
Move data using the IBM DB2 Migration Toolkit. If your application recently ported to IBM DB2 Universal
Database (UDB) and you want to move data from an existing deployment of Oracle or SQL Server to DB2,
this article shows you how to do this using the IBM DB2 Migration Toolkit:
ibm.com/developerworks/db2/library/techarticle/dm-0411yip/
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. (ca.com)
Pervasive
Pervasive offers a transformation tools for DB2 data movement and application integration such as Pervasive Data
Integrator, Pervasive Migration Toolkit and Pervasive Data Exchange.
For additional information, contact Pervasive (www.pervasive.com/products/).
Embarcadero Technologies
This company offers a suite of products for database administration, design and development. Some of these
products are:
.
DBArtisan - Manage databases across the enterprise to achieve higher availability, performance and
security.
.
Embarcadero SQL Debugger - Isolate and correct errors on database logic in applications. Included in
DBArtisan and Rapid SQL.
.
Embarcadero SQL Tuner - Troubleshoot and rewrite database logic to improve application performance.
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
.
ER/Studio - Design, document and maintain databases and data warehouses for higher performance and
quality. Reverse engineer database objects (DDL).
.
ER/Studio Repository - Distribute work among the modeling team members.
.
Rapid SQL - Write, debug and tune database logic in applications.
For more information, contact Embarcadero Technologies (www.embarcadero.com/products/products.html)
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Resources and references
DB2 Universal Database Version 8.2 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 DatabaseV8.1 for Linux, UNIX, and Windows Database Administration Certification Guidea
by Baklarz and Wong
Publication Order Number: 0-13-046361-2
DB2 Universal Database V8.1 Application Development Certification Guide
by Steve Sanyal, Kevin Gashyna, David Martineau, and Mike Kyprianou
Publication Order Number: 0-13-100771-8
Microsoft SQL Server 2000 books online (Updated 2004)
What’s New?
SQL Server Architecture
Creating and Maintaining Databases
Replication
Building SQL Server Applications
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.
Pervasive Product Overview
Product overview at www.pervasive.com/products/
Embarcadero Technologies
Product overview at www.embarcadero.com/products/products.asp
For more information
DB2 Family
www.ibm.com/software/data/db2
DB2 Product and Service Technical Library
www.ibm.com/software/data/technical/
DB2 Maintenance
www.ibm.com/software/data/db2/udb/support/downloadv8.html
ftp://ftp.software.ibm.com/ps/products/db2/fixes/ <language>/<platform>/<FixPak#>
DB2 Support
www.ibm.com/software/data/db2/udb/support/
Complimentary DB2 Self-study courses
www.ibm.com/software/data/education/selfstudy.html
Data Management education page
www.ibm.com/software/data/education.html
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
Migration Station at developerWorks DB2
www.ibm.com/developerworks/ondemand/migrate/index.html
DB2 Technical resources for DB2 information management software (developerworks DB2)
www.ibm.com/developerworks/db2/
DB2 Migration Web site
www.ibm.com/software/data/db2/migration
DB2 Information Management Software Today
www.ibm.com/software/data/db2infonews/
IBM PartnerWorld Program
www.developer.ibm.com/
Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000
....更多联系本人:MSN:zhoujianguo_leo@hotmail.com
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
