1、Front coverMicrosoft SQL Server to IBM DB2 UDBConversion GuideComplete guide to convert from Microsoft SQL Server to IBM DB2 UDBApplication enrichment through advanced DB2 UDB featuresApplication conversion with detailed examplesWhei-Jen Chen Alain Fisher Stefan Hummel Shailendra KishoreWei Bin Teah

2、 Ted W/redbooksInternational Technical Support OrganizationMicrosoft SQL Server to IBM DB2 UDB Conversion GuideJune 2005SG24-6672-00Note: Before using this information and the product it supports, read the information in “Notices” on page xxi.First Edition (June 2005)This edition appl

3、ies to DB2 UDB Version 8.2, Microsoft SQL Server 2000, and Windows 2000 Server. Copyright International Business Machines Corporation 2005. All rights reserved.Note to U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.Conte

4、ntsFiguresxiiiTablesxixNoticesxxiTrademarksxxiiPrefacexxiiiThe team that wrote this redbookxxivAcknowledgementsxxvBecome a published authorxxviComments welcomexxviiChapter 1. Introduction11.1 DB2 Universal Database - a high-level overview21.2 Project environment71.3 IBM migration offering81.3.1 DB2

5、UDB promotion81.3.2 DB2 migration services and support9Chapter 2. Architecture112.1 Instances122.1.1 SQL Server instance definition122.1.2 DB2 UDB instance definition122.2 Memory allocation142.2.1 SQL Server memory allocation142.2.2 DB UDB memory allocation142.3 Processes162.3.1 SQL Server process m

6、odel162.3.2 DB2 UDB process model172.4 Allocating disk space182.4.1 SQL Server disk allocation182.4.2 DB2 UDB disk allocation182.5 Transaction logs192.5.1 SQL Server transaction log overview202.5.2 DB2 UDB transaction log overview202.6 Query optimization212.6.1 SQL Server query optimization222.6.2 D

7、B2 UDB query optimization22 Copyright IBM Corp. 2005. All rights reserved.iii2.7 Parallelism232.7.1 SQL Server parallelism232.7.2 DB2 UDB parallelism242.8 Message logs242.8.1 SQL Server error logs242.8.2 DB2 error logs242.9 Security262.9.1 SQL Server security262.9.2 DB2 UDB Security262.9.3 Security

8、enhancements in V8.2292.10 High availability302.10.1 SQL Server high availability strategies302.10.2 DB2 high availability strategies30Chapter 3. DB2 UDB administration tools313.1 GUI tools323.1.1 Control Center323.1.2 Command Editor333.1.3 SQL Assist343.1.4 Visual Explain353.1.5 Task Center363.1.6

9、Journal373.1.7 Health Center383.1.8 Development Center393.1.9 Configuration Assistant403.1.10 Information Center413.1.11 License Center423.1.12 Replication Center433.1.13 Data Warehouse Center443.1.14 Information Catalog Center453.1.15 Satellite Administration Center463.1.16 Web administration473.2

10、Wizards493.3 Advisors503.4 Command Line523.5 Utilities533.5.1 Maintaining database integrity553.5.2 Throttling utilities573.5.3 Validating a backup583.5.4 DDL extraction583.6 Performance monitor integration613.7 Optional tools623.7.1 DB2 Performance Expert62ivMicrosoft SQL Server to IBM DB2 UDB Conv

11、ersion Guide3.7.2 DB2 Recovery Expert623.7.3 DB2 High Performance Unload633.7.4 DB2 Test Database Generator633.7.5 DB2 Table Editor643.7.6 DB2 Web Query Tool64Chapter 4. SQL considerations654.1 SQL standard compliance664.2 Data types664.2.1 Data type mapping664.2.2 Strong type casting664.2.3 ROWVERS

12、ION data type674.2.4 NULL value considerations694.2.5 Large object (LOB) considerations694.3 Date and time considerations704.3.1 Retrieving date and time values714.3.2 Date and time conversion714.3.3 Date and time arithmetic734.3.4 Examining date and time components744.3.5 Additional date and time e

13、xamples754.4 String considerations764.5 Case sensitivity774.6 SQL language syntax and semantics794.6.1 SELECT statements794.6.2 SELECT INTO814.6.3 INSERT statements824.6.4 UPDATE and DELETE statements844.6.5 TRUNCATE TABLE844.6.6 ANSI joins854.6.7 ORDER BY and GROUP BY clauses874.6.8 Top n clause874

14、.6.9 Cursors884.7 Built-in SQL functions904.8 System catalog queries914.8.1 Catalog interrogation and instance/database metadata924.8.2 System table mapping944.8.3 Authorizations on system catalog tables954.9 Transact-SQL to SQL PL translation964.9.1 EXECUTE 974.9.2 PRINT984.9.3 ERROR994.9.4 RAISERR

15、OR1014.9.5 SQLSTATUS101Contentsv4.9.6 ROWCOUNT1024.9.7 TRANCOUNT1024.10 XML1034.10.1 FOR XML statement1044.10.2 OPENXML statement1134.11 SQL limits117Chapter 5. Planning for a conversion1195.1 Preparation1205.1.1 Performing a porting assessment1205.1.2 Understanding and selecting conversion tools121

16、5.1.3 Estimating the effort required1225.1.4 Planning the conversion1235.1.5 Becoming educated on DB2 UDB1235.1.6 Environment preparation1245.2 Conversion1245.2.1 Converting the database structure1245.2.2 Converting database objects1265.2.3 Porting additional database components and products1265.2.4

17、 Modifying the application1275.2.5 Modifying the database interface1285.2.6 Migrating the data1315.3 Post-conversion1325.3.1 Performance tuning1325.3.2 Utilizing additional DB2 utilities1345.3.3 Defining a maintenance strategy1345.4 Additional references135Chapter 6. The IBM DB2 Migration Toolkit fo

18、r SQL Server1376.1 MTK technical overview1396.1.1 Supported operating system and versions1396.1.2 Hardware requirements1396.1.3 MTK software requirements1396.1.4 Where to install MTK1396.2 MTK setup1406.3 Using MTK1426.3.1 MTK GUI interface1426.3.2 Migration tasks1436.3.3 The MTK SQL Translator148Ch

19、apter 7. Database structure conversion1517.1 Databases1527.2 Table spaces1527.3 Tables153viMicrosoft SQL Server to IBM DB2 UDB Conversion Guide7.3.1 Data types1547.3.2 IDENTITY1547.3.3 Computed columns1557.3.4 Constraints1567.4 Indexes1587.5 Schemas1597.6 Views1607.7 Privileges1607.8 Examples1627.8.

20、1 Database structure conversion using MTK1627.8.2 Manually converting database structure165Chapter 8. Data and script migration1698.1 Introduction1708.2 A typical data migration process1708.2.1 Migrating a subset of data using MTK1718.2.2 Verifying referential integrity1748.3 Using Export, Import, a

21、nd Load1758.4 Using WebSphere Information Integrator1778.4.1 Accessing SQL Server data from DB2 UDB1788.4.2 Replicating data from SQL Server to DB2 UDB1808.5 Using other tools1828.6 Converting scripts182Chapter 9. Converting database objects1859.1 Temporary tables1869.1.1 Private temporary tables187

22、9.1.2 Global temporary tables1889.1.3 Additional information1889.2 Stored procedures1899.2.1 Temporary stored procedures1909.2.2 Extended stored procedures1909.2.3 Remote procedure calls1919.2.4 PROCID1919.2.5 Simple conversion example1929.2.6 Default parameter values1939.2.7 Passing parameter value

23、s1969.2.8 Returning a cursor to the caller1999.2.9 Exception handling and transaction control2029.2.10 Additional information2089.3 User defined functions2089.3.1 Scalar functions2099.3.2 Table functions210Contentsvii9.3.3 Overcoming inline SQL PL limitations in user defined functions2119.3.4 Additi

24、onal information2149.4 Triggers2159.4.1 Accessing transition values in triggers2169.4.2 Simple conversion example2169.4.3 IF UPDATE(column)2189.4.4 Overcoming inline SQL PL limitations in triggers2199.4.5 INSTEAD OF triggers2229.4.6 Additional information223Chapter 10. Application conversion conside

25、rations22510.1 Introduction22610.2 Converting to DB2 UDB compatible SQL syntax22610.2.1 Using MTKs SQL Translator22610.2.2 Manual conversion22810.3 Identifying transaction differences22810.4 Identifying locking and lock escalation differences22910.4.1 Types of locks23010.4.2 Lock escalation23310.4.3

26、 Deadlock23310.5 Identifying isolation level differences23410.5.1 Repeatable Read (RR)23610.5.2 Read Stability (RS)23710.5.3 Cursor Stability (CS)23710.5.4 Uncommitted Read (UR)23810.6 Identifying and modifying database interfaces23810.6.1 ActiveX Data Objects .NET (ADO.NET) interfaces23910.6.2 Acti

27、veX Data Object (ADO)24210.6.3 Java Database Connectivity (JDBC)24910.6.4 Embedded SQL for C (ESQL/C)24910.7 Integrating DB2 UDB in Integration Development Environment (IDE) . 253 10.7.1 Microsoft .NET add-in (Visual Studio)25310.7.2 IBM WebSphere Studio Application Developer25510.8 Approaching pack

28、aged application migration25610.8.1 SAP25710.8.2 Siebel260Chapter 11. Performing administrative tasks in DB2 UDB26311.1 Working with instances26411.1.1 Create, drop, and list instances26411.1.2 Attaching / switching instances26511.1.3 Start, stop, and quiesce instances26611.1.4 Configure instances26

29、6viiiMicrosoft SQL Server to IBM DB2 UDB Conversion Guide11.2 Working with databases26711.2.1 Create, drop, and list databases26711.2.2 Activate and terminate databases27011.2.3 Connect, disconnect, and quiesce databases27011.2.4 Configure databases27111.2.5 List and force off applications27211.3 Ma

30、naging database storage27211.3.1 Table spaces and containers27311.3.2 Monitoring table space and container storage28411.3.3 Transaction logging28911.4 Working with buffer pools29411.5 Task Center and the DB2 Tools Catalog29611.5.1 DB2 Administration Server and Tools Catalog Database29711.5.2 Task Ce

31、nter and Scheduler29811.6 Backup, recovery, and log administration30511.6.1 Automatic backup maintenance30611.6.2 Backup using throttling mode30811.6.3 Log file inclusion in backup images30811.6.4 Backup compression31011.6.5 Automated log file management31111.6.6 Undo management31211.7 High availabi

32、lity31311.7.1 Failover clustering31311.7.2 HADR31311.7.3 Log mirroring32711.7.4 Replication32811.7.5 Online split mirror and suspended I/O support32911.8 REORG and RUNSTATS33011.8.1 Database reorganization33011.8.2 Database statistics335Chapter 12. Post-conversion tuning considerations33912.1 Perfor

33、mance tuning34012.2 Quick-start tips for performance tuning34012.2.1 Design Advisor34112.2.2 Configuration Advisor34212.2.3 ACTIVATE DATABASE command34512.2.4 RUNSTATS and REORG34612.3 Configuring automatic maintenance34612.4 Other performance tuning advice35012.4.1 Table spaces35012.4.2 Physical pl

34、acement of database objects35212.4.3 Buffer pools355Contentsix12.4.4 Large transactions35812.4.5 Process tuning36312.5 Data access strategies36412.5.1 Indexing36412.5.2 DB2 UDB index expansions36812.5.3 Index reorganization37212.6 Advanced access methods37212.6.1 Materialized query tables37312.6.2 M

35、ultidimensional clustering (MDC) tables37912.7 Optimizer38112.7.1 Optimizer analysis38312.7.2 Optimizer directives391Chapter 13. Testing and troubleshooting39713.1 Planning your testing39813.1.1 Principals of software tests39813.1.2 Test documentation39813.1.3 Test phases40113.1.4 Time planning and

36、time exposure40213.2 Data checking techniques40313.2.1 IMPORT/LOAD messages40413.2.2 Data checking40613.3 Code and application testing40813.3.1 T-SQL to SQL PL object check40813.3.2 Application code check40913.3.3 Security testing41013.3.4 Tools for testing and problem tracking41013.4 Troubleshootin

37、g41013.4.1 Interpreting DB2 informational messages41113.4.2 DB2 diagnostic logs41213.4.3 DB2 support information417Chapter 14. Conversion scenario42114.1 Set up the conversion environment42214.2 Create an MTK project42314.3 Core database extraction and deployment42414.3.1 Specify Source42514.3.2 Con

38、vert42914.3.3 Refine43314.3.4 Generate Data Transfer Scripts43814.3.5 Deploy to DB244114.4 Other database object conversion44414.4.1 View conversion445xMicrosoft SQL Server to IBM DB2 UDB Conversion Guide14.4.2 Function conversion44814.4.3 Stored procedure conversion45214.4.4 Trigger conversion45614

39、.5 Application conversion46114.5.1 Overview of the C# sample application46114.5.2 Set up Visual Studio .NET for the DB2 UDB environment46214.5.3 Add a DB2 library reference to .NET classes46314.5.4 Replace interface-specific objects46314.5.5 Database connection statement46414.5.6 Summary464Appendix

40、A. Terminology mapping465A.1 SQL Server to DB2 UDB terminology comparison466Appendix B. Data type mapping469B.1 Mapping SQL Server data types to DB2 data types470B.2 Supported SQL data types in C/C+471B.3 SQL data types for the DB2 .NET Data Provider476B.4 Supported SQL data types in Java478Appendix

41、 C. Function mapping481C.1 Installation of additional built-in functions482C.2 Mathematical functions483C.3 Character and string functions484C.4 Boolean functions486C.5 Date and time functions487C.6 Metadata functions488C.7 Aggregate functions490C.8 System functions491C.9 Security functions493C.10 M

42、iscellaneous functions494Appendix D. Operator mapping495D.1 Arithmetic operators496D.2 Assignment operators496D.3 String concatenation operators496D.4 Comparison operators497D.5 Logical operators497D.6 Bitwise operators498Appendix E. Administrative tasks mapping501Appendix F. SQL limits505F.1 Identi

43、fier length limits506ContentsxiF.2 Database limits506Appendix G. Additional material509Locating the Web material509Using the Web material509System requirements for downloading the Web material511How to use the Web material511Related publications513IBM Redbooks513Other publications513Online resources

44、514How to get IBM Redbooks515Help from IBM515Index517xiiMicrosoft SQL Server to IBM DB2 UDB Conversion GuideFigures1-1Project environment72-1 DB2 architecture showing a single instance132-2 DB2 shared memory architecture152-3 db2diag.log example253-1 Control Center333-2 Command Editor343-3 Sample SQ

45、L Assist output353-4 Visual Explain output363-5 Task Center373-6 Journal383-7 Health Center393-8 Development Center403-9 Configuration Assistant413-10 Information Center423-11 License Center433-12 Replication Center Q replication launchpad443-13 Data Warehouse Center453-14 Information Catalog Center

46、463-15 Satellite Administration Center473-16 Web Command Center showing script execution483-17 Web Command Center with script execution result493-18 Wizards Launchpad503-19 Configuration Advisor output513-20 CLP in interactive mode523-21 CLP in command mode533-22 DB2LOOK GUI utility596-1 MTK Install

47、ation - Welcome screen1406-2 MTK Installation - Agreement screen1416-3 MTK Installation- Installation folder1416-4 MTK Installation - Progress screen1426-5 MTK Installation - Install Complete Screen1426-6 MTK Main window1436-7 Specify source1446-8 Convert1456-9 Refine1466-10 Generate data transfer s

48、cript1466-11 Deploy to DB21476-12 Overview of MTK conversion tasks148 Copyright IBM Corp. 2005. All rights reserved.xiii6-13 MTK SQL Translator1498-1 Suggested data migration process1718-2 Extract table data in the REDBOOK database within SQL Server1728-3 Global Type Mapping summary1728-4 Generate D

49、ata Transfer Scripts in MTK1738-5 Migrating a subset of the database data using MTK1738-6 Creating nickname using Control Center1788-7 SQL Server tables accessed in Control Center through nicknames1808-8 Show command pop-up window1838-9 Importing SQL scripts into Task Center18410-1 Opening the SQL T

50、ranslator in MTK22710-2 SQL Translator after converting a SQL Server SQL statement22810-3 DB2 UDB LOCK TABLE syntax23210-4 CONNECT database statement in DB2 UDB with lock mode23310-5 Providers in .NET to connect to DB2 UDB24010-6 ODBC Driver Manager environment versus DB2 CLI environment24610-7 Proc

51、edure to build ESQL/C application with DB2 UDB25210-8 Development Center integrated into WebSphere25610-9 SAP migration project plan25910-10 Siebel migration process26011-1 Attaching to an instance using Control Center26511-2 Creating database in Control Center26811-3 Show Command from the standard

52、create database wizard26911-4 Configure database logging after creation of database26911-5 Connect, disconnect, quiesce, and unquiesce menu options27111-6 Configuring database parameters in Control Center27111-7 Default Table Spaces27311-8 Create Table Space Wizard27811-9 Create Table Space Wizard:

53、Type of table space27911-10 Create Table Space Wizard: select buffer pool28011-11 Create table space wizard: container options28111-12 Create table space: summary28211-13 Add table space container wizard28411-14 Storage management interface28611-15 Configure Database Logging Wizard29211-16 Configure

54、 Database Logging Wizard - Summary screen29311-17 Create buffer pool window29611-18 Creating the Tools Catalog from the Control Center29811-19 Entering task information in Task Center30011-20 Entering script information in Task Center30111-21 Entering task run properties30111-22 Scheduling the task in Task Center30211-23 Entering notification information in Task Center303xivMicrosoft SQL Server to IBM DB2 UDB Conversion Guide11