Corporation. All rights reserved.<br><br> Table of Contents 36 Stored Procedures ............................................................................ 35 Triggers ..................................................................................... 34 Transactions .................................................................................<br><br> 33 ANSI Join Operators ........................................................................... 33 Scrollable Cursors ............................................................................ 33 Constraints ..................................................................................<br><br> 32 Computed Columns ........................................................................... 32 IDENTITY Columns ........................................................................... 32 Views ......................................................................................<br><br> 31 Indexes ..................................................................................... 30 Temporary Tables ............................................................................ 30 Tables ......................................................................................<br><br> 29 SQL Standard Compliance ...................................................................... 29 System Databases ............................................................................ 28 Locking Mechanisms ..........................................................................<br><br> 28 Statement Isolation Levels ...................................................................... 28 Isolation Levels ............................................................................... 28 Implementation Differences ........................................................................<br><br> 26 Database Consistency ......................................................................... 24 System Stored Procedures and Administrative Tools ................................................. 24 Tasks ......................................................................................<br><br> 23 Online Utility Tools ............................................................................ 22 Backup and Restore ........................................................................... 21 Database Security ............................................................................<br><br> 20 Log Files .................................................................................... 20 Database Devices ............................................................................ 19 Federated SQL Server 2000 Servers ..............................................................<br><br> 18 Administration Server .......................................................................... 18 Environments ................................................................................ 18 Administration Issues ..............................................................................<br><br> 16 Data Types Conversion ............................................................................ 14 DB2 Products ................................................................................ 13 DB2 Application Development Client ..............................................................<br><br> 13 DB2 Administration Client ....................................................................... 13 DB2 Run-Time Client Lite ....................................................................... 13 DB2 Run-Time Client ..........................................................................<br><br> 12 DB2 Everyplace Edition ........................................................................ 12 DB2 Enterprise Server Edition ................................................................... 12 DB2 Workgroup Server Edition ..................................................................<br><br> 12 DB2 UDB Express Edition ...................................................................... 12 DB2 Personal Edition .......................................................................... 11 DB2 Universal Database Product Family ............................................................<br><br> 10 IBM Program for Assistance to Developers ......................................................... 9 High Availability Support .......................................................................... 9 Basic Warehousing Functionality ...................................................................<br><br> 9 Tools for Building Web Applications ................................................................. 8 Integrated Web Access .......................................................................... 8 Integrated Support for Development Environments .....................................................<br><br> 8 Data Replication and Publishing .................................................................... 7 Self-managing and Resource Tuning ................................................................ 7 Multiplatform tools for DB2 Universal Database ........................................................<br><br> 6 Integrated System Management Tools ............................................................... 6 Integrated Support for Native Environments .......................................................... 6 Why Port to DB2 UDB?<br><br> ............................................................................... 5 Introduction ......................................................................................... 53 Resources and References .........................................................................<br><br> 51 Embarcadero Technologies ..................................................................... 51 AllFusion ERwin Data Modeler ................................................................... 50 IBM Migration Toolkit (MTK) .....................................................................<br><br> 50 Conversion Tools .................................................................................. 48 DB2 CLI vs. ODBC Function Map ...................................................................<br><br> 47 Terminology Map .................................................................................. 46 Web Services Applications ...................................................................... 45 Programming APIs Differences ..................................................................<br><br> 44 Embedded SQL Differences ..................................................................... 42 DB2 Java Enablement ......................................................................... 42 DB2 Call Level Interface ........................................................................<br><br> 41 DB2 Programming Interfaces Overview ............................................................ 41 Programming Interfaces ........................................................................... 40 Global Variables ..............................................................................<br><br> 39 XML Support ................................................................................. 38 User Defined Functions ........................................................................ Introduction Database management software is now the core of enterprise computing.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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 5 Why port to DB2 UDB? DB2 UDB Version 8.2 delivers new features to address the ever-increasing demands and requirements on customer 9s data.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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 9s 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.<br><br> 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.<br><br> Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000 6 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.<br><br> 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.<br><br> 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 3 including tables, indexes, or data 3 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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 7 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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).<br><br> 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 9s platform.<br><br> 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 8 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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 .<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000 9 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.<br><br> 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.<br><br> 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 .<br><br> 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 10 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.<br><br> DB2 UDB Version 8 database software servers run on the following software environments: AIX, HP 3UX 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.<br><br> 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).<br><br> 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.<br><br> 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 11 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.<br><br> 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 3bit), Windows XP (32 3bit or 64 3bit), and Windows Server 2003 (32 3bit or 64 3bit).<br><br> 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.<br><br> 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.<br><br> 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.<br><br> DB2 Workgroup Server Edition runs on AIX, HP 3UX 11i, Linux (Intel, iSeries", pSeries), Solaris Operating Environment, Windows NT Version 4, Windows 2000, Windows XP, and Windows Server 2003 only on 32 3bit 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.<br><br> 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.<br><br> Another feature worth mentioning is the inclusion of a data warehouse server and related components. DB2 Enterprise Server Edition runs on AIX, HP 3UX 11i, Linux (Intel, iSeries, pSeries, zSeries), Solaris Operating Environment, Windows NT Version 4, Windows 2000, and Windows Server 2003. It is available on 32 3bit and 64 3bit mode for all operating environments except on specific versions of Solaris.<br><br> 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.<br><br> 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.<br><br> 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 12 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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 9s Kit.<br><br> 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.<br><br> 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 13 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.<br><br> 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++.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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 .<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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).<br><br> 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 14 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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).<br><br> 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.<br><br> 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.<br><br> 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 15 Data types conversion Most Microsoft SQL Server 2000 data types can be mapped to DB2.<br><br> The following table compares all data types: 13 N/A character(16) FOR BITDATA 13 N/A uniqueidentifier entry length 1 to 2 31 BLOB(n) multiples of a pa e 6 2 31 - 1 (2,147,483,647) image entry length 1 to 2 31 CLOB(n) multiples of a page 6 2 31 - 1 (2,147,483,647) text 1 0 or 1 character(1) FOR BITDATA 12 1 0 or 1 bit entry length 1 to 32,672 varchar(n) FOR BIT DATA entry length 1 to 8000 varbinary(n) binar var in n 1 to 254 character(n) FOR BIT DATA n 1 to 8000 binary(n) 2 * entry length 1 to 1 073 741 823 DBCLOB(n) multiples of a page 2 30 - 1 (1,073,741,823) ntext 2 * entry length 1 to 16,350 long vargraphic 11 2 * entry len th 1 to 16,336 10 vargraphic(n) entry length 1 to 4000 nvarchar(n) 2 * n 1 to 127 graphic(n) 2 * n 1 to 4000 nchar(n) entry length 1 to 32,700 long varchar 11 entry length 1 to 32,672 10 varchar(n) character varying entry length 1 to 8000 varchar(n) char[acter] var in n 1 to 254 char [acter](n) n 1 to 8000 char [acter](n) 3 internal, 8 external hour: 0 to 24, minutes/seconds: 0 to 59 time 9 4 internal, 10 external year: 0001 to 9999, month: 1 to 12,day: 1 to 31 date 8 character(8) FOR BITDATA 8 4 varbinary(8) timestamp 10 internal, 26 external January 1, 0001 to December 31, 9999 timestamp 8 3 January 1, 1753 to December 31, 9999 datetime 10 internal, 26 external January 1, 0001 to December 31, 9999 timestamp 7 4 2 January 1, 1900 to June 6, 2079 smalldatetime 11 numeric(19,4) 8 -922,337,203,685,477.5808 to 922,337,203,685,477.5807 money 6 numeric(10,4) 4 -214,748.3648 to 214,748.3647 smallmoney 8 (16 digits) 0 or from -1.79769E+308 to -2.225E-307, or from 2.225E-307 to1.79769E+308 double precision synonym for float double precision 4 ( 8 digits) 0 or from -3.402E+38 to -1.175E-37, or from 1.175E-37 to 3.402E+38 real 4 (7 digits) 0 or from 1.18E-38 to 3.40E+38, or from -1.18E-38 to -3.40E+38 real 4 or 8 float(p) 4 or 8 1 (15 digits) 0 or from -2.23E-308 to -1.79E+308, or from 2.23E-308 to 1.79E+308 float(p) (p/2) + 1 -10 31 +1 to 10 31 -1 ( p+s <=31) num [eric](p,s) dec [imal](p,s) 2-17 -10 38 - 1 to 10 38 -1 numeric(p,s) dec [imal](p,s) 8 9223372036854775808 to +9223372036854775807 bigint 8 9223372036854775808 to +9223372036854775807 bigint 4 -2 31 to 2 31 - 1 integer in 4 -2 31 ( -2,147,483,648) to 2 31 -1 (2,147483,647) int integer 2 -32768 to 32767 smallint 2 -32768 to 32,767 smallint 2 -32768 to 32767 smallint 1 0 to 255 tinyint Bytes of Stora e Range of Values DB2 UDB Data T e Name Bytes of Stora e Range of Values MS SQL Data T e Name 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 16 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 .<br><br> 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.<br><br> 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.<br><br> They are not datetime data. Storage is as binary (8) varbinary (8) data. A table can have only one timestamp column .<br><br> 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).<br><br> 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. 10 The maximum length for varying-length data types depend on the table space page size.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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 17 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000 18 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.<br><br> 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.<br><br> 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.<br><br> 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.<br><br> DB2 9s "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.<br><br> 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.<br><br> 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.<br><br> Components of a Federated System Porting to DB2 UDB Version 8.2 from Microsoft SQL Server 2000 19 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.<br><br> 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.<br><br> 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).<br><br> 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).<br><br> 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.<br><br> 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.<br><br> 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.<br><br> Log files Microsoft SQL Server 2000 transaction log consists of one or more log files, each containing a

less