Release Notes IBM DB2 Universal Database Release Notes Version 6 © Copyright International Business Machines Corporation 2002. All rights reserved. Note to U.S. Government Users -- Documentation related to restricted rights -- Use, duplication or disclosure is subject to restrictions set forth in GSA ADP Schedule contract with IBM Corp. ------------------------------------------------------------------------ Table of Contents Welcome to DB2 Universal Database Version 6.1! Special Notes * 1.1 Supported Netscape Versions for the Control Center * 1.2 PostScript Books Now on a Separate CD * 1.3 Restrictions When Using Tokens that are Generated as a Result of Queries Against Tables with DATALINK Columns (Windows NT) * 1.4 ADSM and the Data Links Manager on Windows NT * 1.5 User Name Cannot be the Same as the Windows NT Computer Name * 1.6 Snapshot Commands Now Require an Instance Attachment * 1.7 AutoLoader Prerequisite on AIX Version 4.2 * 1.8 Unmounting CD-ROM or NFS File Systems After Installing UNIX Product Documentation * 1.9 HP-UX DBCS Online Document Search Fails * 1.10 Multiple DB2 Clients Connecting to Server Simultaneously Might Get CPI-C Error 9 * 1.11 Microsoft SNA Server returns CMALLC Error 20 if APPC Security Setting is Selected * 1.12 IBM DB2 Query Patroller Released in English Only * 1.13 IBM Professional Certification Program for DB2 Universal Database * 1.14 Running DB2 for AIX on an SMP Box * 1.15 OS/2 Warp3 SMP Must Have IP08529 Applied for DB2 to Work * 1.16 SUNLINK Version 9.1 Patches for Solaris DRDA Application Server * 1.17 Other Solaris Patches * 1.18 SQL2043N on Solaris * 1.19 Traditional Chinese big5 Locale on Solaris * 1.20 ODBC Driver Manager and ODBC DLL Levels Shipped with DB2 UDB Version 6.1 Windows 32 (NT/95/98) * 1.21 Refresh GUI Resources if Modified by Multiple Sources * 1.22 Error When Attempting to Allocate a Session with VTAM While Using Integrated SNA Support, Personal Communications (PCOMM), or Communications Server (CS) * 1.23 Error When Attempting to Create a Japanese Sample Database with Code Set 954 from the DB2 Installer (Japanese TurboLinux 3.0 or Japanese TurboLinux Server 1.0) * 1.24 Code Page Restriction for DBCS File Names * 1.25 Database Administration Server (DAS) Instance Migration * 1.26 License Management and Tracking is Not Supported on DB2 Gateway Configurations Using DCE Encryption * 1.27 Limitations on Response File Install * 1.28 The SNA Stack Must be Installed Before the CAE (Administration Client) * 1.29 Updatable Keyset Cursor Restrictions * 1.30 Increasing the Number of File Descriptors on UNIX Based Systems * 1.31 LIBPATH Must List \sqllib\dll\odbc.dll as the First odbc.dll (OS/2 Only) * 1.32 Using DB2 UDB with OS/2 JFS Filesystem * 1.33 IBM JDK Does Not Work With SQLJ Administering Satellites Guide and Reference * 2.1 Error Logging on a Satellite * 2.2 Disabling Notification on a Satellite * 2.3 Synchronization Test Issues o 2.3.1 Cataloging the Satellite Control Database at a Satellite o 2.3.2 Synchronization Test Prompts for Recataloging of Satellite Control Database * 2.4 Setting the Execution Starting Point to the Next Batch Step * 2.5 Administering Satellites Guide and Reference Correction Administration Guide * 3.1 Version 1 Clients Are Not Supported in Version 6 * 3.2 Federated Server Functionality Not Available * 3.3 Design and Implementation: Implementing Your Design * 3.4 Design and Implementation: Incompatibilities Between Releases * 3.5 Performance: System Catalog Statistics * 3.6 Performance: SQL Explain Facility * 3.7 Configuring DB2 in the IBM LDAP Environment (Windows Only) o 3.7.1 Creating an LDAP User o 3.7.2 Configuring the LDAP User for DB2 Applications * 3.8 Design and Implementation: Configuring DB2 * 3.9 INTRA_PARALLEL Database Manager Configuration Parameter * 3.10 NUM_POOLAGENTS Database Manager Configuration Parameter (Agent Pool Size) * 3.11 TP_MON_NAME Database Manager Configuration Parameter (Transaction Processor Monitor Name) * 3.12 MAXAPPLS Database Configuration Parameter * 3.13 BUFFPAGE Database Configuration Parameter * 3.14 Performance: DB2 Registry and Environment Variables * 3.15 Performance: DB2NODE is Not a Registry Variable * 3.16 DB2_NUM_FAILOVER_NODES (A New DB2 Registry Variable) * 3.17 Design and Implementation: National Language Support (NLS) * 3.18 Problems When Adding Nodes to Partitioned Databases * 3.19 SCSI Tape Drive Support in Linux Administrative API Reference * 4.1 How the API Descriptions are Organized/C API Syntax * 4.2 db2LoadQuery - Load Query * 4.3 sqlarbnd - Rebind * 4.4 Compound=x File Type Modifier (sqluimpr - Import) * 4.5 SQLFUPD * 4.6 SQLOPT * 4.7 SQLULOAD-IN Data Structure * 4.8 Transaction Manager Log Records Application Building Guide * 5.1 Introduction * 5.2 Setup * 5.3 Building Java Applets and Applications * 5.4 Building AIX Applications o 5.4.1 Micro Focus COBOL o 5.4.2 REXX * 5.5 Building HP-UX Applications * 5.6 Building OS/2 Applications * 5.7 Building Silicon Graphics IRIX Applications o 5.7.1 MIPSpro C o 5.7.2 MIPSpro C++ * 5.8 Building Applications for Windows 32-bit Operating Systems * 5.9 Building OS/2 Programs - Java - Building SQLJ Programs Application Development Guide * 6.1 Federated Server Functionality Not Available * 6.2 SQLJ Programming * 6.3 JDBC Version 2.0 Support * 6.4 DB2 UDB Integration into the Visual Studio Development Environment * 6.5 SQL Statements in Stored Procedures * 6.6 Writing Stored Procedures * 6.7 Debugging Java Stored Procedures/Distributed Debugger * 6.8 PARAMETER STYLE JAVA Stored Procedures * 6.9 Installing, Removing, and Replacing JAR Files CLI Guide and Reference * 7.1 How to Bind the DB2 CLI/ODBC Driver to the Database * 7.2 New CLI/ODBC Configuration Keyword o 7.2.1 CLIPKG o 7.2.2 QUERYTIMEOUTINTERVAL * 7.3 SQLSetStmtAttr() change for SQL_ATTR_QUERY_TIMEOUT * 7.4 Using Reference Types * 7.5 Errors Rebinding Version 5.2 CLI (IBM DB2 ODBC Driver) Packages Command Reference * 8.1 db2advis - DB2 Index Advisor * 8.2 db2batch - Benchmark Tool * 8.3 db2jstrt - DB2 JDBC Applet Server (New Command) * 8.4 db2look - DB2 Statistics Extraction Tool * 8.5 db2move - Database Movement Tool * 8.6 db2rbind - Rebind all Packages * 8.7 db2sql92 - SQL92 Compliant SQL Statement Processor * 8.8 Issuing Commands from the MS-DOS Prompt in Command Line Mode (Windows 95) * 8.9 ADD DATALINKS MANAGER * 8.10 BIND * 8.11 GET SNAPSHOT * 8.12 Compound=x File Type Modifier (Import) * 8.13 MIGRATE DATABASE * 8.14 PRUNE HISTORY/LOGFILE * 8.15 REBIND * 8.16 RECONCILE * 8.17 REORGANIZE TABLE * 8.18 REORGCHK * 8.19 RESTORE DATABASE * 8.20 RUNSTATS * 8.21 Using Command Line SQL Statements Data Movement Utilities Guide and Reference * 9.1 Compound=x File Type Modifier (Import) * 9.2 Checking for Constraints Violations (Load) * 9.3 SQLULOAD-IN Data Structure * 9.4 Example Load Sessions/API Examples/tload.sqc Sample Program * 9.5 db2LoadQuery - Load Query API * 9.6 AutoLoader Options (SPLIT_ONLY) Installation and Configuration Supplement * 10.1 Federated Server Functionality Not Available * 10.2 Setting Up Server Communications * 10.3 Using The Command Line Processor to Configure Server Communications - Configuring APPC on the Server * 10.4 Accessing DB2 Universal Database Servers from Host and AS/400 Application - Supported Clients * 10.5 APPC Connections (SNA) * 10.6 Using the DB2 Universal Database Server from Host and AS/400 Clients Message Reference * 11.1 Federated Server Functionality Not Available * 11.2 SQL0109N (Addition to Explanation) * 11.3 SQL0112N (Changed Message) * 11.4 SQL0120N (Changed Message) * 11.5 SQL0206N (Changed Message) * 11.6 SQL1179W (New Message) * 11.7 SQL1592N (Changed Message) * 11.8 SQL1596N (New Message) * 11.9 SQL1704N (New Reason Codes) * 11.10 SQL2554N (New Reason Codes) * 11.11 SQL3604N * 11.12 SQL4304N (New Reason Codes) * 11.13 SQLSTATE: 01639 (New) * 11.14 SQLSTATE: 42903 (Changed) * 11.15 SQLSTATE: 42928 (New) DB2 Query Patroller Administration Guide DB2 Query Patroller Installation Guide Quick Beginnings * 14.1 Multiple Network Adapters and the db2nchg Command * 14.2 Reapply the Service Pack if Any New Windows NT Components Have Been Installed * 14.3 Connecting to Windows NT DB2 UDB Server with IBM CS/NT Version 6 * 14.4 Syntax Error in the Migration Section * 14.5 Error in the Migrating Instances Section * 14.6 Windows NT and OS/2 Migration Information * 14.7 UNIX Platform Migration Information * 14.8 Migration Recommendation Relating to DMS File Table Spaces * 14.9 Administering Instances and Databases with the DB2 Administration Tools * 14.10 Monitoring Partitioned Databases * 14.11 Installing DB2 on NEC PC98 Machines Running Windows 95 * 14.12 Uninstalling DB2 on NEC PC98 Machines Running Windows 95 * 14.13 db2start on Windows NT Not Returning Correct Message * 14.14 Prerequisite for APPC on AIX * 14.15 Memory Windows for HP-UX 11 * 14.16 DB2 Driver Error with Lotus Domino Go Webserver * 14.17 CCA Cannot Detect PCOMM on Windows NT with IBM PCI Token-Ring Adapter * 14.18 Restarting a Failed Database Partition Server * 14.19 Workload Transfer in the Event of Hardware or Software Failure * 14.20 Disk Requirements for Clients * 14.21 Estimating Fixed Disk Requirements for Servers * 14.22 DB2CKMIG Reporting Potential Views that May or May Not Work After Database Migration * 14.23 Changes to SNA Configuration During Migration from DB2 Version 5.x to DB2 Version 6.1 When Using IBM eNetwork Communications Server for AIX (CS/AIX) * 14.24 Single-User Install of DB2 for Windows NT * 14.25 Planning for Installation o 14.25.1 NetFinity Support Program Service Must Be Stopped Before Installing DB2 UDB EEE - Windows NT o 14.25.2 Recommended Fix Level for ADSM on AIX o 14.25.3 Recommended Windows NT Level * 14.26 Other Installation and Configuration Tasks o 14.26.1 Problem Affecting NIS Users on AIX Versions 4.2 or Later * 14.27 Setting up the CCA and the Control Center in a Partitioned Database Environment (DB2 Universal Database Enterprise - Extended Edition for AIX) * 14.28 Considerations for Remotely Administering Clusters Using the Control Center * 14.29 DB2 SNMP Subagent * 14.30 ADSM Client Setup for EEE on Solaris - Use db2profile * 14.31 Configuring NetBIOS on Windows NT Servers * 14.32 IBM DB2 Stored Procedure Builder o 14.32.1 Configuring DB2 UDB for Stored Procedure Builder o 14.32.2 Obtaining authorization to create stored procedures o 14.32.3 Known problems with Stored Procedure Builder o 14.32.4 Unsupported items o 14.32.5 Late usability information + 14.32.5.1 Running Stored Procedure Builder + 14.32.5.2 SQL Assistant + 14.32.5.3 Searching in the Project window + 14.32.5.4 Filtering stored procedures + 14.32.5.5 Creating stored procedures that contain static SQL (SQLJ) + 14.32.5.6 Returning result sets + 14.32.5.7 Building stored procedures that contain static SQL (SQLJ) + 14.32.5.8 Stopping a build or run process + 14.32.5.9 Printing from Stored Procedure Builder + 14.32.5.10 DB2SPB.INI file additions o 14.32.6 Remote debugging with Stored Procedure Builder + 14.32.6.1 Setting up DB2 UDB to debug stored procedures + 14.32.6.2 Debugger availability + 14.32.6.3 Debugger requirements o 14.32.7 Running Stored Procedure Builder as an add-in from another development application + 14.32.7.1 Setting Stored Procedure Builder to run as an add-in in Microsoft Visual Basic + 14.32.7.2 Setting Stored Procedure Builder to run as an add-in in Microsoft Visual C++ DB2 Data Links Manager Quick Beginnings * 15.1 HTML Version Not Available * 15.2 Windows NT Prerequisite * 15.3 Case Sensitivity of Instance Name When Connecting to DLFM * 15.4 Using NFS to Access a Remote DLFS File System * 15.5 DLFM Migration and Environment Variables * 15.6 DB2 Data Links Manager System Setup and Backup Recommendations * 15.7 DB2 Data Links Manager Recovery Scenarios * 15.8 Recovery with DLFM * 15.9 Implications of Changing the DLFM_BACKUP_DIR_NAME Registry Value * 15.10 Recovering from a Crash on the Data Links Server DB2 Replication Guide and Reference * 16.1 About this book * 16.2 Part 2. Administration: Planning for Replication/Deciding What to Replicate/Replicating Large Objects * 16.3 Part 2. Administration: Replication Restrictions for DB2 UDB Enterprise - Extended Edition * 16.4 Cold-starting the Capture Program with CCD Targets * 16.5 Key Update Restrictions * 16.6 Informix Logging and DJRA * 16.7 Replicating Large Objects * 16.8 Part 3. Operations: New Invocation Parameter for the Apply Program * 16.9 Chapter 9. Capture and Apply for AS/400: Connecting to an AS/400 Server * 16.10 MAX_SYNCH_MINUTES Support * 16.11 CRTSQLPKG Command * 16.12 Chapter 10. Capture and Apply for UNIX Platforms/Starting Capture for UNIX Platforms * 16.13 Part 5. The DataJoiner Replication Administration Tool * 16.14 Part 6. Reference Information/Chapter 21. Table Structures: Consistent-change-data-tables (CCD) * 16.15 Chapter 23. Capture and Apply Messages * 16.16 Appendix A. How the DB2 library is Structured SQL Reference * 17.1 Federated Server Functionality Not Available * 17.2 OLAP Function * 17.3 Supported Functions * 17.4 Changes to CREATE PROCEDURE * 17.5 Missing Restriction from the Description of CREATE TABLE * 17.6 Changes Related to CREATE TABLE Pertaining to the Inheritance of Defaults * 17.7 Changes to PREPARE, Untyped Parameter Marker Usage Table * 17.8 SET INTEGRITY * 17.9 Changes to UPDATE System Monitor Guide * 18.1 Changes to Statement Operation * 18.2 Information Available from Event Monitors Troubleshooting Guide Client Configuration Assistant (CCA) * 20.1 Using Configuration Profiles Generated by the GUI Tools * 20.2 CLI/ODBC Settings Notebook * 20.3 Tab Truncation in CCA SmartGuide (OS/2) * 20.4 Current Limitations on Client Configuration Export/Import Functionality Control Center * 21.1 Starting the Control Center * 21.2 No Help on Windows 95 If the url.dll File Is Not in the \windows\system Directory * 21.3 db2look and db2advis Must be Installed to Use "Generate DDL" and the "Index SmartGuide" * 21.4 No Access to DB2 for OS/390 Version 5 through the Control Center * 21.5 Infopops * 21.6 Entry Field Input Method for Korean Characters in the Control Center * 21.7 Corrupted Fields When running the Korean Control Center on an English Version of Netscape (OS/2) * 21.8 Modify the DBCS Search Form When the Web Control Center is Installed Without the Product Documentation (UNIX only) * 21.9 JDK Problem When Running the Control Center in Simplified Chinese on UNIX Based Systems * 21.10 JDK Problem When Running the Control Center in Simplified Chinese or Korean on HP-UX * 21.11 Decimal Separator Problem for Some Locales When Running the Control Center on Netscape Version 4.5 * 21.12 Java Exception When Running the Control Center on Netscape Version 4.5 * 21.13 Control Center/Web Administration Problem on Netscape Version 4.5 (Simplified Chinese) * 21.14 Special Character Problem for Some Locales When Running the Control Center on Netscape Version 4.5 * 21.15 Typing Characters in the Control Center Using the AltGr Key * 21.16 Running the Control Center on the Hummingbird Exceed X Window Server * 21.17 Running the Control Center as an Applet * 21.18 Online Help Not Coming Up on OS/2 * 21.19 Online Help Not Coming Up on UNIX Based Systems * 21.20 Online Help Not Coming Up on HP-UX Version 10 * 21.21 Browser Opens Blank After Pressing the Help Button on Linux * 21.22 Federated Database System * 21.23 Locate Notebook * 21.24 TCP/IP Bug on the Linux 2.2.2 Kernel that Causes Search Discovery to Fail * 21.25 Use the Database Administration Server (DAS) When Cataloging a Communication Node on a Remote DB2 Server * 21.26 Performance Monitor * 21.27 Opening the Event Analyzer Window * 21.28 Summary Tables * 21.29 Web Administration * 21.30 Log File Size (logfilsiz) Database Configuration Parameter * 21.31 Web Control Center Monitoring * 21.32 Help for the Statement Termination Character Field (Satellite Administration Center) * 21.33 Promote Source and Promote Subscription Windows (Satellite Administration Center) * 21.34 Create and Edit Authentication Help (Satellite Administration Center) * 21.35 Incorrect Behavior in the Satellite Administration Center * 21.36 Limit on Log Details Information (Satellite Administration Center) Tivoli-ready Support Miscellaneous * 23.1 DB2 Family Newsletter ------------------------------------------------------------------------ Welcome to DB2 Universal Database Version 6.1! This file contains information about the following products that was not available when the DB2 manuals were printed: IBM DB2 Universal Database Satellite Edition, Version 6.1 IBM DB2 Universal Database Personal Edition, Version 6.1 IBM DB2 Universal Database Workgroup Edition, Version 6.1 IBM DB2 Universal Database Enterprise Edition, Version 6.1 IBM DB2 Data Links Manager for AIX, Version 6.1 IBM DB2 Data Links Manager for Windows NT, Version 6.1 IBM DB2 Universal Database Enterprise - Extended Edition for AIX, Version 6.1 IBM DB2 Universal Database Enterprise - Extended Edition for Windows NT, Version 6.1 IBM DB2 Universal Database Enterprise - Extended Edition for the Solaris Operating Environment, Version 6.1 IBM DB2 Query Patroller for AIX, Version 6.1 IBM DB2 Query Patroller for the Solaris Operating Environment, Version 6.1 IBM DB2 Personal Developer's Edition, Version 6.1 IBM DB2 Universal Developer's Edition, Version 6.1 A separate Release Notes file, installed as READCON.TXT, is provided for the following products: IBM DB2 Connect Personal Edition, Version 6.1 IBM DB2 Connect Enterprise Edition, Version 6.1 A file that contains troubleshooting and other information about the Control Center can be found at: sqllib/cc/prime/readme.htm The What's New book contains both an overview of some of the major DB2 enhancements for Version 6.1, and a detailed description of these new features and enhancements. ------------------------------------------------------------------------ Special Notes ------------------------------------------------------------------------ 1.1 Supported Netscape Versions for the Control Center English Netscape 4.5 and higher is the only supported browser for the Control Center running as an applet. ------------------------------------------------------------------------ 1.2 PostScript Books Now on a Separate CD All of the DB2 Version 6.1 documentation in PostScript format can be found on a separate CD that is shipped with the product. ------------------------------------------------------------------------ 1.3 Restrictions When Using Tokens that are Generated as a Result of Queries Against Tables with DATALINK Columns (Windows NT) Following are some restrictions on using the tokens that are generated as a result of queries against a table with one or more DATALINK columns: * You must login as an "ordinary user" to the DB2 client machine from which the query (for example, the SELECT statement) is issued. Ordinary user means a user who does not have administrator authority; it refers to a user who belongs only to the "Users" group. If you login as an administrator, the tokens will not work. You will receive a message stating that the "system cannot find the file specified". * From the client machine, you must connect (as an ordinary user) to the NTFS shared drive that is taken over by the Data Links File Manager. The following example uses the NET USE command to connect to the shared drive: C:\> net use \\datalinkmachine\shareddrive /user:username password * When accessing the file using the token, double quotation marks should be used around the file path, because there is a semicolon after the token. For example: "\\datalinkmachine\shareddrive\token;filename" ------------------------------------------------------------------------ 1.4 ADSM and the Data Links Manager on Windows NT If you have ADSM installed on your machine, please remove the file "ADSMV3.DLL" from the sqllib\bin directory after you install the Data Links Manager. This is to avoid loading a different ADSM library than the one you have already installed. ------------------------------------------------------------------------ 1.5 User Name Cannot be the Same as the Windows NT Computer Name If you are using a domain user to access DB2, the user name cannot be the same as the Windows NT computer name of the DB2 server machine. ------------------------------------------------------------------------ 1.6 Snapshot Commands Now Require an Instance Attachment The ability to perform database monitor requests over database connections, as well as instance attachments, can be a problem when both database connections and instance attachments are held by a single application. Since it is not clear which connection should have the snapshot flown over it, only database monitor actions over an instance attachment will be permitted. To address the table function requirement for a database connection, a user must hold a database connection when taking a write to file snapshot. This connection can then be used through the table function to query the snapshot results. ------------------------------------------------------------------------ 1.7 AutoLoader Prerequisite on AIX Version 4.2 On AIX, db2atld may hang unless the following libraries (at the indicated levels or greater) are present: bos.rte.libc 4.2.1.13 (PTF U458582) bos.rte.libpthreads 4.2.1.5 (PTF U458538) ------------------------------------------------------------------------ 1.8 Unmounting CD-ROM or NFS File Systems After Installing UNIX Product Documentation If you choose to install DB2 UDB Version 6 product documentation on your UNIX based operating system, an HTML search system called NetQuestion will also be installed (if it is not already present on your system). If NetQuestion is not already present on your system, you may find that, after installing your DB2 product documentation, you are not able to unmount the installation file system (typically a CD-ROM, but NFS file systems can also be affected). This problem is caused by NetQuestion daemons and processes that are started during the installation of the product documentation. To unmount your installation file system, you must stop all NetQuestion daemons and processes. You can do this easily by using a shell script called db2nqadm. Download db2nqadm from IBM's service ftp site to your system. If you are having problems with an NFS mounted file system, do not place this script in that file system. As root, type db2nqadm stop and press . This stops all NetQuestion daemons and processes running on your system. Unmount your file system as usual. When you have finished unmounting your file system, type db2nqadm start and press . This restarts NetQuestion daemons and processes. ------------------------------------------------------------------------ 1.9 HP-UX DBCS Online Document Search Fails If you have installed DB2 for HP-UX, along with product documentation or Control Center help for one or more DBCS locales (Japanese, Korean, Simplified Chinese, or Traditional Chinese), and you attempt to search the DB2 product documentation or help for any DBCS locale, you might receive an error message on the search results page. If this occurs, it is possible that an outdated DBCS search CGI has been installed. To determine if this is the case, switch to /var/docsearch/cgi-bin and check the size of db2srdbcs, which is the DBCS CGI file. If the file size of db2srdbcs is 189,308 bytes, it is the outdated version. The correct version of db2srdbcs is located in /opt/IBMdb2/V6.1/doc/en_US/html. Switch to this directory and copy db2srdbcs to /var/docsearch/cgi-bin. The new size of db2srdbcs should be 279,424 bytes. Searching the DB2 product documentation should now work. ------------------------------------------------------------------------ 1.10 Multiple DB2 Clients Connecting to Server Simultaneously Might Get CPI-C Error 9 When you define a Transaction Program in Communications Server (CS) or Personal Communications (PCOMM) on Windows NT, please remember to deselect the dynamically loaded advanced option. Failing to do so, your DB2 clients might get the following error: SQL30081N A communication error has been detected. Communication protocol being used: "APPC". Communication API being used: "CPI-C". Location where the error was detected: "". Communication function detecting the error: "cmsend". Protocol specific error code(s): "9", "*", "*". SQLSTATE=08001 ------------------------------------------------------------------------ 1.11 Microsoft SNA Server returns CMALLC Error 20 if APPC Security Setting is Selected When you configure Microsoft SNA Server, be sure to deselect the APPC Security Setting for your SNA Subdomain as follows: 1. Click Start -> Programs -> Microsoft SNA Server -> Manager. The Microsoft SNA Server Manager window opens. 2. Right mouse click on your SNA subdomain and select Properties. The SNA Subdomain Properties window opens. 3. Select the Security tab. 4. Ensure that the APPC LU Type option is deselected. 5. Click OK and then click File -> Save to save the change. ------------------------------------------------------------------------ 1.12 IBM DB2 Query Patroller Released in English Only IBM DB2 Query Patroller for AIX, Version 6.1, and IBM DB2 Query Patroller for the Solaris Operating Environment, Version 6.1, which include the following tools: * Query Enabler * Query Monitor * Query Administrator * Query Patroller-Tracker are being released in English only. ------------------------------------------------------------------------ 1.13 IBM Professional Certification Program for DB2 Universal Database The DB2 documentation states: To find out about the IBM Professional Certification Program for DB2 Universal Database, go to http://www.software.ibm.com/data/db2tech/db2cert.html This should be changed to: To find out about the IBM Professional Certification Program for DB2 Universal Database, go to http://www.ibm.com/certify and select a certification roadmap from the choices offered under the "DB2 Universal Database" heading. ------------------------------------------------------------------------ 1.14 Running DB2 for AIX on an SMP Box Please apply the fix for DB2 for AIX APAR IX88758 (PTF U464388), a prerequisite if you will be running DB2 for AIX on an SMP box. This fix is for both AIX 4.2 and AIX 4.3. ------------------------------------------------------------------------ 1.15 OS/2 Warp3 SMP Must Have IP08529 Applied for DB2 to Work OS/2 Warp3 SMP FixPak IP08529 is a prerequisite for DB2 on this operating system. ------------------------------------------------------------------------ 1.16 SUNLINK Version 9.1 Patches for Solaris DRDA Application Server To make an incoming connection to a Solaris DB2 Universal Database server with SUNLINK 9.1, using Distributed Relational Database Architecture (DRDA), requires Solaris patches 106162 and 105860. ------------------------------------------------------------------------ 1.17 Other Solaris Patches Please be aware of the following in connection with the Solaris 2.5.1 Operating Environment: Bug ID # 4024649 Bug ID # 4107724 is addressed by Patch # 103640-26 Bug ID # 4052568 is addressed by Patch # 103640-27 Bug ID # 4062430 is addressed by Patch # 103640-27 Bug ID # 4054742 is addressed by Patch # 103640-27 Bug ID # 4181592 is addressed by Patch # 103640-28 Please be aware of the following in connection with the Solaris 2.6 Operating Environment: Bug ID # 4052568 Bug ID # 4107724 is addressed by Patch # 105181-09 Please consult the SunSolve information database or contact your Sun representative for more information. ------------------------------------------------------------------------ 1.18 SQL2043N on Solaris If you are getting SQL2043N on Solaris, add a line in /etc/services to set a maxuprc value. Note also that text of SQL2043N refers to "maxuproc", which is incorrect on Solaris. ------------------------------------------------------------------------ 1.19 Traditional Chinese big5 Locale on Solaris SQLJ and JDBC messages for the Traditional Chinese big5 locale on Solaris cannot be seen. ------------------------------------------------------------------------ 1.20 ODBC Driver Manager and ODBC DLL Levels Shipped with DB2 UDB Version 6.1 Windows 32 (NT/95/98) When a customer installs DB2 UDB Version 6.1, the install will upgrade the level of ODBC DLLs that are on the system, and the level of the ODBC driver manager, if they are not at the levels that have been tested and shipped with Version 6.1. Since these upgrades may cause some ODBC applications to fail, it might be useful to test the ODBC applications with the upgraded ODBC files. Following is a list of the ODBC files shipped with DB2 UDB Version 6.1. The files originate from the MSDASDK 2.0 from Microsoft. Last Modified Size Filename Version -------------------------------------------------------------- 04/09/97 05:00p 2,151 12520437.CPX N/A 04/09/97 05:00p 2,233 12520850.CPX N/A 06/20/98 04:43p 26,224 ODBC16GT.DLL 3.510.3002.13 06/22/98 02:57p 202,000 ODBC32.DLL 3.510.3002.13 06/20/98 04:43p 6,656 ODBC32GT.DLL 3.510.3002.13 06/20/98 04:47p 9,216 ODBCAD32.EXE 3.510.3002.13 06/22/98 02:57p 7,984 ODBCCP32.CPL 3.510.3002.13 06/22/98 02:57p 87,216 ODBCCP32.DLL 3.510.3002.13 06/22/98 02:57p 196,096 ODBCCR32.DLL 3.510.3002.13 06/20/98 04:47p 68,608 ODBCINT.DLL 3.510.3002.13 06/22/98 02:57p 142,144 ODBCTRAC.DLL 3.510.3002.13 06/20/98 04:37p 4,656 DS16GT.DLL 3.510.3002.13 06/20/98 04:37p 5,632 DS32GT.DLL 3.510.3002.13 08/11/97 08:07a 17,408 MSCPXL32.DLL 3.50.0300 06/08/98 07:17p 63,760 MSDADC.DLL 02.00.3002.4 06/08/98 07:17p 17,856 MSDAENUM.DLL 02.00.3002.4 06/08/98 07:17p 30,992 MSDAER.DLL 02.00.3002.4 06/08/98 06:36p 15,360 MSDAERR.DLL 02.00.3002.4 06/08/98 07:17p 8,160 MSDAPS.DLL 02.00.3002.4 06/19/98 09:57a 286,992 MSDASQL.DLL 02.00.3002.11 06/18/98 07:08p 9,728 MSDASQLR.DLL 02.00.3002.11 06/08/98 07:16p 39,088 MSDATT.DLL 02.00.3002.4 11/14/97 06:43a 29,456 MTXDM.DLL 1997.11.532.0 ------------------------------------------------------------------------ 1.21 Refresh GUI Resources if Modified by Multiple Sources If you view or modify resources from multiple sources (command line processor, Client Configuration Assistant, or Control Center, for example) at the same time on the same system, you must be sure to refresh those resources in each source being run. For example, a user opens the Control Center (CC), and then opens the Client Configuration Assistant (CCA) and modifies a database entry. The user must refresh the Control Center view to reflect changes made through the CCA before actions are taken against the modified resource through the CC. All combinations of command line processor, Client Configuration Assistant, and Control Center require this synchronization step. ------------------------------------------------------------------------ 1.22 Error When Attempting to Allocate a Session with VTAM While Using Integrated SNA Support, Personal Communications (PCOMM), or Communications Server (CS) Windows NT, 95 and 98 users who are using Integrated SNA Support, Personal Communications (PCOMM), or Communications Server (CS) will receive the following error when attempting to allocate a session with VTAM if the environment variable APPCLLU is not set to the LU that is to be designated as the default LU: SQL30081N A communication error has been detected. Communication protocol being used: "APPC". Communication API being used: "CPI-C". Location where the error was detected: "". Communication function detecting the error: "cmallc". Protocol specific error code(s): "1", "*", "*". SQLSTATE=08001 The solution to this problem is to set the environment variable APPCLLU to the LU that is to be designated as the default LU. ------------------------------------------------------------------------ 1.23 Error When Attempting to Create a Japanese Sample Database with Code Set 954 from the DB2 Installer (Japanese TurboLinux 3.0 or Japanese TurboLinux Server 1.0) Japanese TurboLinux 3.0 or Japanese TurboLinux Server 1.0 users will receive an error when attempting to create a Japanese sample database with code set 954 from the DB2 installer. One solution is to log in as the instance owner and create the sample database manually by issuing the "db2sampl" command. Another is to add the following statement to /etc/skel/.bashrc: export LANG=ja_JP.ujis ------------------------------------------------------------------------ 1.24 Code Page Restriction for DBCS File Names A double-byte character set (DBCS) file name is not supported if the database server code page differs from the operating system code page. ------------------------------------------------------------------------ 1.25 Database Administration Server (DAS) Instance Migration To migrate a DB2 Version 5 DAS instance to Version 6.1, issue the db2imigr command as you would when migrating any instance. ------------------------------------------------------------------------ 1.26 License Management and Tracking is Not Supported on DB2 Gateway Configurations Using DCE Encryption License management and tracking is not supported on DB2 gateway configurations using DCE encryption, because the user ID is encrypted until it gets to the host database. The ticket that DCE uses to transmit this data is unique each time, and the gateway does not have the ability to decrypt it. ------------------------------------------------------------------------ 1.27 Limitations on Response File Install You should be aware of the following limitations when using the response file method to install DB2 on UNIX platforms: 1. The response file install can be used to create and configure a new instance. However, you cannot use it to modify an existing instance's configuration (for example, its database manager configuration parameter values, or instance profile registry values). 2. The response file for Query Patroller (db2qps.rsp) is used to set up an existing Enterprise - Extended Edition instance. You cannot use it to create a new instance. To create a new Enterprise - Extended Edition instance, you need to use the response file db2udbeee.rsp. 3. If you set any instance or global profile registry keywords to BLANK (the literal "BLANK"), the effect is to delete that keyword from the list of currently set keywords. If the registry variable corresponding to the keyword is not already set, and you run a response file install with this keyword set to BLANK, then you will receive an error. 4. If you are using the response file to install on Linux, please ensure that you have sufficient space prior to installing. Otherwise you may need to do some manual cleanup if the installation fails. 5. You can use the response file to install additional components/products after an initial install. However, you should not comment out the PROD keyword when you do so. Otherwise you may have some missing components even with a successful response file install. ------------------------------------------------------------------------ 1.28 The SNA Stack Must be Installed Before the CAE (Administration Client) If the CAE is installed before the SNA stack, the CAE will not detect the SNA after startup. ------------------------------------------------------------------------ 1.29 Updatable Keyset Cursor Restrictions There is a known problem when using keyset cursors with some ADO objects within Visual Basic. Some ADO objects created using ADO Code binding are not updatable, and are thrown by ADO if an insert, update or deletion is attempted. For example, when used with ADO Code binding, the ADO Grid object will throw a run-time error "3265" after issuing the insert, update or deletion request. The workaround for this problem is to use ADO Data Control binding with these ADO objects. The ADO Grid object is updatable when used with ADO Data Control binding. ------------------------------------------------------------------------ 1.30 Increasing the Number of File Descriptors on UNIX Based Systems On some UNIX based systems, the number of file descriptors must be increased before DB2 can be loaded as an application. From the sh shell, enter ulimit -n # Set # to 512 or higher (for example, 1000). ------------------------------------------------------------------------ 1.31 LIBPATH Must List \sqllib\dll\odbc.dll as the First odbc.dll (OS/2 Only) On OS/2, the \sqllib\dll\odbc.dll file must appear as the first odbc.dll in the LIBPATH parameter of the config.sys file. If it is not listed first, you will have problems connecting to DB2 through ODBC applications. ------------------------------------------------------------------------ 1.32 Using DB2 UDB with OS/2 JFS Filesystem DB2 UDB fails at times when using OS/2 JFS Filesystem, due to incorrect data being written to or read from disk. Using a JFS cache size of 16KB has been shown to solve this problem in some cases. Please refer to DB2 UDB information APAR II11878 for further details. ------------------------------------------------------------------------ 1.33 IBM JDK Does Not Work With SQLJ When using IBM JDK 1.1.7 enhanced version on Windows NT and OS/2, the SQLJ translator generates the following exception: +++ Exception: Unknown (code=c0000005) for JITC at 10054a1a (id=d3) The workaround is to turn off the JIT compiler: sqlj -J-nojit filename.sqlj This problem has been filed with IBM JDK support (APAR JR13262 for Windows NT, APAR IC24168 for OS/2, and APAR IY01074 for AIX). ------------------------------------------------------------------------ Administering Satellites Guide and Reference ------------------------------------------------------------------------ 2.1 Error Logging on a Satellite On the satellite, the log file (instance_directory\satellite\progress.log) records progress information about the current or last synchronization session. If an error occurs during script execution, the SQLCODE -3968 is reported at the satellite and stored in the progress.log file. ------------------------------------------------------------------------ 2.2 Disabling Notification on a Satellite The notifylevel database manager configuration parameter specifies the type of messages that are written to the instance.nfy diagnostic file on the satellite. Because the same information is written to the db2diag.log file, you should consider setting the notifylevel to 0 so that the instance.nfy file is not used. For more information about the notifylevel parameter, refer to the Administration Guide. For more information about the db2diag.log file, refer to the Troubleshooting Guide. ------------------------------------------------------------------------ 2.3 Synchronization Test Issues Following are the known issues that apply when running the DB2 Synchronizer application in test mode (db2sync -t command). 2.3.1 Cataloging the Satellite Control Database at a Satellite When you use the Catalog Control Database window to catalog the satellite control database at a satellite, you can specify the protocol parameters manually. If you do, you can specify either the service name or the port number in the Service name field. If you specify the service name, you must ensure that an associated entry exists in the local services file. If you specify the port number, these entries do not have to exist. 2.3.2 Synchronization Test Prompts for Recataloging of Satellite Control Database If the satellite control database, SATCTLDB, is not cataloged at the satellite, use the db2sync -t command to open the DB2 Synchronizer application in test mode. The Catalog Control Database window will open, in which you can specify the catalog information for the satellite control database. After the satellite control database is cataloged at the satellite, two situations can occur in which you will be prompted to recatalog the satellite control database: * If the satellite cannot contact the DB2 control server over the network, you will be prompted to recatalog after the message for SQLCODE -30081 is displayed. If the catalog information is correct, close the Catalog Control Database window. If the connection could not be established because the catalog information is not correct, use the Catalog Control Database window to recatalog the satellite control database. * If one of the stored procedures at the DB2 control server is not bound to the satellite control database. In this situation, the SQLCODE -805 is issued. To correct this problem, you must bind DB2SATCS to the satellite control database. (This problem does not occur if you use the DB2 installation program to create the satellite control database.) ------------------------------------------------------------------------ 2.4 Setting the Execution Starting Point to the Next Batch Step When you set the execution starting point for a satellite, you can specify that the satellite begin executing a group batch (setup, update, or cleanup batch) after the last batch step in the batch. This prevents the satellite from executing any existing batch steps for that batch. You may want to do this if, for example, you migrate a satellite from DB2 Personal Edition to DB2 Satellite Edition. In this situation, the satellite already has a database, database objects, and data, and you will likely use a fix batch to modify the migrated satellite to the configuration that you want (if necessary). You will not want this satellite to execute the setup batch to create the database definition. To prevent the satellite from executing the batch steps of a group batch, use the Set Execution Starting Point Window. When you specify the execution starting point for the satellite, select the Next batch step from the group batch. In this way, the satellite will not execute any of the existing batch steps of the group batch when it synchronizes. Note:You cannot specify that a satellite execute a fix batch from the Next batch step. If you later add one or more new batch steps to the group batch, the satellite will execute these batch steps. That is, the Next batch step is always after the last batch step in a group batch. ------------------------------------------------------------------------ 2.5 Administering Satellites Guide and Reference Correction In the "Customizing the Generated Response File" section of the "Performing a Mass Deployment" chapter, the following statement is made: If you set the application version on the satellite before running the db2rspgn utility, do not use the DB2.DB2SATELLITEAPPVER keyword to set the application version. The value specified by the keyword will be overwritten when the DB2 instance client profile is imported. This statement is not correct. Importing the client profile of the DB2 instance does not set the application version (DB2SATELLITEAPPVER). During a mass deployment, you can set the application version: * When installing DB2 Satellite Edition with a generated response file. In this situation, specify a value for the keyword DB2.DB2SATELLITEAPPVER. Activate the line containing the keyword (by removing the asterisk) and provide a value. * When installing the end user application. For more information, refer to the "Customizing the Operating Environment of Each Satellite" section of the "Performing a Mass Deployment" chapter. ------------------------------------------------------------------------ Administration Guide ------------------------------------------------------------------------ 3.1 Version 1 Clients Are Not Supported in Version 6 As of Version 6, Version 1.x clients, including the clients packaged with DB2 Parallel Edition Version 1.2 servers, are no longer supported. ------------------------------------------------------------------------ 3.2 Federated Server Functionality Not Available The distributed join installation feature and associated federated system functionality, as documented, are not currently supported with DB2 UDB Version 6.1. ------------------------------------------------------------------------ 3.3 Design and Implementation: Implementing Your Design The following has been added to the section "Before Creating a Database/DB2 Administration Server (DAS)/Setting Up DAS with EEE Systems/Discovery of Administration Servers, Instances, and Databases": The Other Systems (Search the network) icon will only appear if the client's discover parameter is set to SEARCH. This is the default setting. The following has been added to the section "Creating a Database/Creating a Table Space/RAW I/O": In addition to the Linux operating system, the Windows 95 and Windows 98 operating systems do not allow you to attach a direct disk access (raw) device to the DB2 Universal Database system. ------------------------------------------------------------------------ 3.4 Design and Implementation: Incompatibilities Between Releases The following has been added to the section "DB2 Universal Database Version 5 Incompatibilities/Application Programming/NS, NW and NX Locks": The incompatibility described in this section has been removed in Version 6. ------------------------------------------------------------------------ 3.5 Performance: System Catalog Statistics The following has been added to the section "Collecting and Using Distribution Statistics": Distribution statistics can be removed by updating SYSSTAT.COLDIST and setting all the COLVALUE and VALCOUNT values to either 0 or -1 for the columns for which distribution statistics are no longer needed. ------------------------------------------------------------------------ 3.6 Performance: SQL Explain Facility The reference in the section "SQL Advise Facility" to a table called "EVALUATE_INDEXES" is incorrect. The correct name of the table is "ADVISE_INDEX". ------------------------------------------------------------------------ 3.7 Configuring DB2 in the IBM LDAP Environment (Windows Only) The following information is missing from the Administration Guide - Design and Implementation, Appendix N. Lightweight Directory Access Protocol (LDAP) Directory Services: Before you can use DB2 in the IBM LDAP environment, you must configure the following on each machine: * The LDAP server's TCP/IP host name and port number. These values can be entered during installation, or you can manually set them later by using the db2set command: db2set DB2LDAPHOST=hostname[:port] where hostname is the LDAP server's TCP/IP hostname, and [port] is the port number. If a port number is not specified, DB2 will use the default LDAP port (389). * The LDAP base distinguished name (baseDN), where DB2 objects are located. If you are using IBM SecureWay LDAP Directory server Version 3.1, you do not have to configure the base distinguished name, since DB2 can dynamically obtain this information from the server. However, if you are using IBM eNetwork Directory Server Version 2.1, you must configure the LDAP base distiguished name on each machine by using the db2set command: db2set DB2LDAP_BASEDN=baseDN where baseDN is the name of the LDAP suffix that is defined at the LDAP server. This LDAP suffix will be used to contain DB2 objects. * The LDAP user's distinguished name (DN) and password (only required if you plan to use LDAP to store DB2 user specific information). 3.7.1 Creating an LDAP User DB2 supports setting DB2 registry variables and CLI configuration at the user level. User level support provides user-specific settings in a multi-user environment, such as Windows NT Terminal Server, where each logon user can customize his own environment without interfering with the system environment or another user's environment. When using the IBM LDAP directory, you must define an LDAP user (or person) before you can store user level information in LDAP. You can create an LDAP user in one of the following two ways: * Create an LDIF file to contain all attributes for the user object, then run the LDIF import utility to import the object into the LDAP directory. The LDIF utility for the IBM LDAP server is LDIF2DB. * Use the Directory Management Tool (DMT), available only for the IBM SecureWay LDAP Directory Server Version 3.1, to create the user (or person) object. An LDIF file containing the attributes for a person object will look similar to the following: File name: newuser.ldif dn: cn=Mary Burnnet, ou=DB2 UDB Development, ou=Toronto, o=ibm, c=ca objectclass: ePerson cn: Mary Burnnet sn: Burnnet uid: mburnnet userPassword:password telephonenumber: 1-416-123-4567 facsimiletelephonenumber: 1-416-123-4568 title: Software Developer Following is an example of the LDIF command to import an LDIF file using the IBM LDIF import utility: ldif2db -i newuser.ldif Notes: 1. You must run the ldif2db command from the LDAP server machine. 2. You must grant the required access (ACL) to the LDAP user object, so that the LDAP user can add, delete, read, and write to his own object. To grant ACL for the user object, use the LDAP Directory Server Web Administration tool. 3.7.2 Configuring the LDAP User for DB2 Applications Before running DB2, you must configure the LDAP user distinguished name (DN) and password for the current logon user. This can be done using the db2ldcfg utility: db2ldcfg -u -w -> set the user's DN and password -r -> clear the user's DN and password For example: db2ldcfg -u "cn=Mary Burnnet,ou=DB2 UDB Development,ou=Toronto,o=ibm,c=ca" -w password ------------------------------------------------------------------------ 3.8 Design and Implementation: Configuring DB2 In the section "Database Log Activity", the following change is needed to the "Number of Commits to Group (mincommit)" database configuration parameter. Add the following information at the beginning of the "Recommendation" section of the parameter description: Recommendation: The value for this parameter should be the default (1). Only increase this parameter by one for every 500 users; or, increase this parameter if you determine you have a high transaction rate. ------------------------------------------------------------------------ 3.9 INTRA_PARALLEL Database Manager Configuration Parameter In Chapter 2 ("Application Considerations") of the Administration Guide (Performance), in the section titled "Parallel Processing of Applications", change the following: There is a database manager configuration parameter, intra_parallel, that enables or disables instance parallelism support. The default is "NO" for a uni-processor system and "YES" for SMP machines. to There is a database manager configuration parameter, intra_parallel, that enables or disables instance parallelism support. ------------------------------------------------------------------------ 3.10 NUM_POOLAGENTS Database Manager Configuration Parameter (Agent Pool Size) Agents from the agent pool (num_poolagents) will be reused as coordinator agents: * For remote TCP/IP-based applications; or, * For local applications on UNIX-based operating systems; or, * For both local and remote applications on the Windows NT operating system and OS/2. Otherwise, remote applications will always create a new agent. ------------------------------------------------------------------------ 3.11 TP_MON_NAME Database Manager Configuration Parameter (Transaction Processor Monitor Name) The Transaction Processor Monitor Name (tp_mon_name) default should be changed to for all operating systems. ------------------------------------------------------------------------ 3.12 MAXAPPLS Database Configuration Parameter This parameter specifies the maximum number of application programs (both local and remote) that can connect to the database at one time. The upper limit for this parameter has been increased from 5000 to 64000. The default value for this parameter is incorrectly documented as 40 on UNIX based systems, and 20 on Windows NT and OS/2. The default value is actually 40 on UNIX based systems and Windows NT, and 20 on OS/2. ------------------------------------------------------------------------ 3.13 BUFFPAGE Database Configuration Parameter The minimum size of buffpage (and any buffer pool that is sized through SQL) is now 2 pages. However, the suggested minimum size of any buffer pool is at least 2*maxappls*dft_degree, to reduce the chances of not being able to fix a page because the buffer pool is full. ------------------------------------------------------------------------ 3.14 Performance: DB2 Registry and Environment Variables In the table under DB2_LIKE_VARCHAR, the values shown should be changed to: Default=NO Values: YES, NO, or a floating point constant between 0 and 5.0. In the table under DB2_BINSORT, the default value shown should be changed to YES. ------------------------------------------------------------------------ 3.15 Performance: DB2NODE is Not a Registry Variable As of DB2 Version 5.2 and later, the environment/registry variable DB2NODE can no longer be set in the registry. That is, it can only be set as an environment variable. ------------------------------------------------------------------------ 3.16 DB2_NUM_FAILOVER_NODES (A New DB2 Registry Variable) This new registry variable applies to DB2 UDB EEE configurations only. If DB2 UDB is configured for high availability, and a node fails, this node can be restarted as a second logical node on a different host. The new registry variable allows a user to specify how much memory to reserve for FCM resources for failover nodes. For example, host A has two logical nodes, (1) and (2), and host B has two logical nodes, (3) and (4). Assume the user has set DB2_NUM_FAILOVER_NODES=2. The high availability configuration specifies that, in the event of node failure, the node(s) will be restarted on the other machine. During db2start, both A and B will reserve enough memory for the FCM component to run up to 4 logical nodes. Therefore, if B were to fail, we could successfully restart nodes (3) and (4) on host A, and vice-versa. The default value for this registry variable is 2. It can be set to a value of less than 2, and there is no maximum value. The only limitation is the amount of physical memory on each machine. ------------------------------------------------------------------------ 3.17 Design and Implementation: National Language Support (NLS) The following statement should be added to the Unicode support section of this appendix: Connection of a UTF-8 (code page 1208) client to a non-Unicode database is not supported. The table of string types under section "Bidirectional-specific CCSIDs" of the National Language Support appendix in the Design and Implementation volume contains the following line: 4 Visual Arabic LTR Shaped OFF This line should be changed to: 4 Visual Passthrough LTR Shaped OFF The last table in the "National Language Support" appendix contains the following line: 055 Brazil dd.mm.yy JIS LOC LOC, USA, EUR, ISO This line should be changed to: 055 Brazil dd/mm/yyyy JIS LOC LOC, EUR, ISO Following are two new entries for this table: 355 Albania yyyy-mm-dd JIS LOC LOC, USA, EUR, ISO 084 Vietnam dd/mm/yyyy JIS LOC LOC, EUR, ISO ------------------------------------------------------------------------ 3.18 Problems When Adding Nodes to Partitioned Databases When adding nodes to partitioned databases, two problems may surface under the conditions described below. A number of suggested "workarounds" are discussed in the context of a sample database with two partitions (MPP1 with node numbers 0 and 1). Nodegroup NG1 includes nodes 0 and 1, and table space TS1 uses a page size of 8KB. When adding nodes to partitioned databases that have temporary table spaces of 8KB, 16KB, or 32KB pages, DB2 will return "SQL6073N Add Node operation failed", because only the IBMDEFAULTBP bufferpool exists (with a page size of 4KB) at the time of creating this node. PROBLEM: The db2start command is used to add a node to the current partitioned database. Example: db2start nodenum 2 addnode hostname NEWHOST port 2 which normally returns: SQL6075W The start database manager operation successfully added the node. The node is not active until all nodes are stopped and started again. will now return: SQL6073N Add Node operation failed. SQLCODE = "<-902>" PROBLEM: The ADD NODE command is used on the new node after the db2nodes.cfg file is manually updated with the new node description. Example: Edit db2nodes.cfg to add the node information for the new node. db2 add node which normally returns: DB20000I The ADD NODE command completed successfully. will now return: SQL6073N Add Node operation failed. SQLCODE = "<-902>" One workaround is to run "db2set DB2_HIDDENBP=16" before issuing db2start or the ADD NODE command. This will enable DB2 to allocate hidden 8KB, 16KB, and 32KB bufferpools of 16 pages each, and enable the add node operation to proceed successfully by creating the non-4KB page size table spaces on the new node. Another workaround is to specify the WITHOUT TABLESPACES option with the ADD NODE or the db2start command, and later to manually create the bufferpools using the CREATE BUFFERPOOL statement, and the temporary table spaces using the ALTER TABLESPACE statement. When adding a node to an existing nodegroup through the ALTER NODEGROUP ADD NODE statement, where the existing nodegroup has table spaces with 8KB, 16KB, or 32KB pages, DB2 will return "SQL0647N Bufferpool '' is not currently active", because the non-4KB bufferpools created on the new node have not yet been activated for the table spaces. PROBLEM: The ALTER NODEGROUP statement is used to add a node to a nodegroup. Example: db2start db2 connect to MPP1 db2 alter nodegroup NG1 add node (2) which normally returns: SQL1759W Redistribute nodegroups required to change data partitioning for objects in nodegroup. will now return: SQL0647N Bufferpool '' is currently not active. The workaround is to create 8KB, 16KB, or 32KB page size bufferpools, and then to reconnect to the database before issuing the ALTER NODEGROUP statement. Example: db2start db2 connect to MPP1 db2 create bufferpool BP1 size 1000 pagesize 8192 db2 connect reset db2 connect to MPP1 db2 alter nodegroup NG1 add node (2) or db2set DB2_HIDDENBP=16 db2start db2 connect to MPP1 db2 alter nodegroup NG1 add node (2) PROBLEM: The ALTER TABLESPACE statement is used to add a table space to a node. Example: db2start db2 connect to MPP1 db2 alter nodegroup NG1 add node (2) without tablespaces db2 alter tablespace TS1 add ('TS1') on node (2) which normally returns: SQL1759W Redistribute nodegroups required to change data partitioning for objects in nodegroup. will now return: SQL0647N Bufferpol '' is currently not active. The workaround is to reconnect to the database after the ALTER NODEGROUP WITHOUT TABLESPACES statement. Example: db2start db2 connect to MPP1 db2 alter nodegroup add node (2) without tablespaces db2 connect reset db2 connect to MPP1 db2 alter tablespace TS1 add ('TS1') on node (2) or db2set DB2_HIDDENBP=16 db2start db2 connect to MPP1 db2 alter nodegroup add node (2) without tablespaces db2 alter tablespace TS1 add ('TS1') on node (2) ------------------------------------------------------------------------ 3.19 SCSI Tape Drive Support in Linux Under most versions of Linux, using DB2's default buffer sizes for backup and restore to a SCSI tape device will result in an SQL2025, reason code "75". To prevent the overflow of Linux internal SCSI buffers, use the following formula: bufferpages <= ST_MAX_BUFFERS * ST_BUFFER_BLOCKS / 4 where bufferpages is the value of BACKBUFSZ or RESTBUFSZ. ST_MAX_BUFFERS and ST_BUFFER_BLOCKS are defined in the Linux kernel under drivers/scsi. ------------------------------------------------------------------------ Administrative API Reference ------------------------------------------------------------------------ 4.1 How the API Descriptions are Organized/C API Syntax Change the second bullet to: Generic APIs have names that contain the prefix "db2g", followed by a string that matches the C API name. Data structures used by generic APIs have names that also contain the prefix "db2g". ------------------------------------------------------------------------ 4.2 db2LoadQuery - Load Query Change the name of the generic API "db2GenLoadQuery" to "db2gLoadQuery". Change the name of the structure used by the generic API "db2gLoadQuery" to "db2gLoadQueryStruct". A Fortran version of the "loadqry" sample program is not available. ------------------------------------------------------------------------ 4.3 sqlarbnd - Rebind For both the C API "sqlarbnd" and the generic API "sqlgrbnd", replace the "void * pReserved" parameter with "struct sqlopt * pRebindOptions": pRebindOptions Input. A pointer to the sqlopt structure, used to pass rebind options to the API. For more information about this structure, see SQLOPT. The following table lists valid values for the TYPE and the VAL fields of the rebind options structure (see SQLOPT), as well as their corresponding CLP options. For a description of the rebind options (including default values), see the Command Reference. REBIND Option Types and Values CLP Option Option Type Option Value RESOLVE ANY SQL_RESOLVE_OPT SQL_RESOLVE_ANY RESOLVE CONSERVATIVE SQL_RESOLVE_OPT SQL_RESOLVE_CONSERVATIVE ------------------------------------------------------------------------ 4.4 Compound=x File Type Modifier (sqluimpr - Import) If this modifier is specified, and the transaction log is not sufficiently large, the import operation will fail. The transaction log must be large enough to accommodate either the number of rows specified by COMMITCOUNT, or the number of rows in the data file if COMMITCOUNT is not specified. It is therefore recommended that the COMMITCOUNT option be specified to avoid transaction log overflow. This modifier is incompatible with INSERT_UPDATE mode, hierarchical tables, and the USEDEFAULTS modifier. ------------------------------------------------------------------------ 4.5 SQLFUPD The data type for the "dl_upper" database configuration parameter is Sint16, and for the "dl_token" parameter, it is char(10). The book incorrectly specifies the reverse. There is a missing value in the list of valid values for the "authentication" database manager configuration parameter. The missing value is "SQL_AUTHENTICATION_DCE_SVR_ENC (6)". ------------------------------------------------------------------------ 4.6 SQLOPT All references to bind and precompile should include rebind, because this structure can now also be used to pass bind options to sqlarbnd - Rebind. ------------------------------------------------------------------------ 4.7 SQLULOAD-IN Data Structure The description of this structure is missing the "indexing_mode" parameter. Supported values (defined in sqlutil.h) are: SQLU_INX_AUTOSELECT SQLU_INX_REBUILD SQLU_INX_INCREMENTAL SQLU_INX_DEFRRED For an explanation of these indexing modes, see the description of the LOAD command in the Command Reference. ------------------------------------------------------------------------ 4.8 Transaction Manager Log Records Appendix G describes DB2 log records. Following is a modified description of the record structure of some of the transaction manager log records: * Local Pending List: 20 bytes for log header, 4 bytes for commit time, 2 bytes for auth id length, auth id (variable length, indicated by the length field), pending list entries (note: auth id length and auth id are only there if the log record is marked as propagatable) * Global Pending List: 20 bytes for log header, 2 bytes for auth id length, auth id (variable length, indicated by the length field), pending list entries (note: auth id length and auth id are only there if the log record is marked as propagatable; also, there is no commit time) * Normal/Heuristic/MPP Coordinator/MPP Subordinator Commit, Normal/Heuristic Abort: change authid from fixed length 9-byte field to variable length * XA Prepare: 20 bytes for log header, 4 bytes for prepare time, 8 bytes for log space used by transaction, 4 bytes for transaction node list size, transaction node list (variable length, indicated by the previous field), XA identifier for transaction, application information structure (consists of 4 bytes for length, 4 bytes for code page, 4 bytes for transaction start time, 20 bytes for application name, 32 bytes for application identifier, 4 bytes for sequence number, 20 bytes for client database alias, variable length for auth id), variable length for synclog information * MPP Subordinator Prepare: 20 bytes for log header, 4 bytes for prepare time, 8 bytes for log space used by transaction, 6 bytes for coordinator LSN, 2 bytes for padding (no real data), 20 bytes for SQLP_GXID, application information structure (consists of 4 bytes for length, 4 bytes for code page, 4 bytes for transaction start time, 20 bytes for application name, 32 bytes for application identifier, 4 bytes for sequence number, 20 bytes for client database alias, variable length for auth id) ------------------------------------------------------------------------ Application Building Guide ------------------------------------------------------------------------ 5.1 Introduction The following should be added to the section "About the DB2 Software Developer's Kit": The DB2 SDK provides developers with two Visual Studio Version 6.0 development extensions. These extensions plug into the Visual C++ IDE to enable the launching of various DB2 GUI tools, and facilitate the development of embedded SQL applications and embedded SQL stored procedures using Microsoft Visual Studio Version 6.0. These two extensions are available from: www.software.ibm.com\data\db2\udb\ide The following should be added to the section "Sample Programs": Note:The CLI programs sendda and showda are not supported on AIX, Linux, or Windows 32-bit operating systems. The CLI program, async, is not supported on OS/2. The CLI program duowcon is not supported on HP-UX Version 10 and Silicon Graphics IRIX, and the CLI program multicon only works if two or more different databases are specified; it does not work for multiple connections to the same database. ------------------------------------------------------------------------ 5.2 Setup In the section, "Setting the Windows 32-bit Operating Systems Environment", the INCLUDE path is given as %DB2PATH\INCLUDE instead of %DB2PATH%\INCLUDE. The sentence containing this typo is correctly rendered as follows: When building C or C++ programs, you must ensure that the INCLUDE environment variable contains %DB2PATH%\INCLUDE as the first directory. ------------------------------------------------------------------------ 5.3 Building Java Applets and Applications The JDBC sample program, UseThrds, will only run on a server, and not on a client. The Java makefile supplied with the sample programs incorrectly includes this program with the make command for running JDBC samples on the client: make otherclijdbc on UNIX platforms, and nmake oth_clijdbc on OS/2 and Windows 32-bit operating systems. In the section, "SQLJ Programs", the build files bldsqlj and bldsqljs, as documented, provide the optional parameters database name, user ID, and password for the db2profc command, but do not provide them for the SQLJ command. These optional parameters have been added for the SQLJ command in the build files. These files are in the %DB2PATH%\samples\java directory on OS/2 and Windows 32-bit operating systems, and in the sqllib/samples/java directory on UNIX platforms. In the section, "General Points for DB2 Applets", the following point should be included: On Windows platforms, the JDBC applet server, the db2jd listener, can be started automatically or manually as a service. However, if the db2jd listener is started as a service and the port number is different from the default number, 6789, the port number has to be entered manually each time the service is started or the system reboots. This is not very convenient when you want it to start automatically. DB2 now provides a new db2set environment variable, DB2JD_PORT_NUMBER. By setting this variable to a port number of your choice, every time db2jd is started without a port number passed in from the command line, this port number will be used. If a port number is passed in directly, that port will be used. If DB2JD_PORT_NUMBER is not set and no port number is passed in, the default port number, 6789, will be used. Refer to the Command Reference for information on how to use the db2set command. The valid value for this entry is any port number (an integer) that is greater than 1024, and is not currently in use. To build and run JDBC and SQLJ programs on Silicon Graphics IRIX, you must compile and run the programs as o32 objects. To compile and run Java programs as o32 objects, perform the following steps: 1. Include the sqllib/lib and sqllib/lib32 libraries in your LD_LIBRARY_PATH environment variable. For example, you can add the following lines to a Korn shell script: export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/sqllib/lib export LD_LIBRARYN32_PATH=$LD_LIBRARY_PATH:$HOME/sqllib/lib32 2. To compile and run a JDBC program as an o32 object, you must include the -o32 flag in your build commands. For example, to compile and run the JDBC sample program Dynamic.java, use the following commands: javac -o32 Dynamic.java java -o32 Dynamic userid password 3. To compile and run an SQLJ program as an o32 object, you must include the -o32 flag in your build commands. For example, to compile and run the SQLJ sample program App.sqlj, use the following commands: sqlj -J-o32 App.sqlj embprep App sample userid passwd java -o32 App userid passwd Note:In the previous sqlj command, do not include a space between -J and -o32. The Java makefile, and the bldsqlj build script, located in the sqllib/samples/java directory, need to be modified with the above options before they can be used on Silicon Graphics IRIX. The JDBC applet server (db2jd) used to set the LANG environment variable to ensure that the code page under which it is running is in synchrony with the JDBC client. However, since this causes problems on some operating systems (for example, on Solaris), db2jd no longer sets the LANG environment variable, and it is now the responsibility of the user to ensure that the JDBC applet client and server are under the same code page. ------------------------------------------------------------------------ 5.4 Building AIX Applications 5.4.1 Micro Focus COBOL In the section "Embedded SQL Stored Procedures", the sub-section "Exiting the Stored Procedure" is no longer needed, and can be disregarded. Stored procedures no longer need to exit with the statement: move SQLZ-HOLD-PROC to return-code The two Micro Focus COBOL stored procedure samples supplied by DB2, outsrv and inpsrv, have been changed to reflect this. Instead of using the above statement, they use the following: move SQLZ-DISCONNECT-PROC to return-code 5.4.2 REXX DB2 Universal Database Version 6.1 supports Object REXX for AIX Version 1.1. ------------------------------------------------------------------------ 5.5 Building HP-UX Applications In "Micro Focus COBOL", in the section "Embedded SQL Stored Procedures", the following should be added: Note:At the time of this writing, Micro Focus COBOL Stored procedures are not supported on DB2 for HP-UX Version 11.0. For the latest DB2 for HP-UX application development information, please check the Web site at: http://www.software.ibm.com/data/db2/udb/ad In "Micro Focus COBOL", in the section "Embedded SQL Stored Procedures", the sub-section "Exiting the Stored Procedure" is no longer needed, and can be disregarded. Stored procedures no longer need to exit with the statement: move SQLZ-HOLD-PROC to return-code The two Micro Focus COBOL stored procedure samples supplied by DB2, outsrv and inpsrv, have been changed to reflect this. Instead of using the above statement, they use the following: move SQLZ-DISCONNECT-PROC to return-code ------------------------------------------------------------------------ 5.6 Building OS/2 Applications In the section, "IBM VisualAge C++ for OS/2 Version 3", sub-section, "DB2 CLI Stored Procedures", the command file, bldclisp, as documented, does not have the object file, samputil.obj, in the link step. The correct link step is in the bldclisp command file in the %DB2PATH%\samples\cli directory, as follows: ilink /NOFREE /MAP /NOI /DEBUG /ST:64000 %1.obj samputil.obj,%1.dll,,db2cli.lib,%1.def; ------------------------------------------------------------------------ 5.7 Building Silicon Graphics IRIX Applications 5.7.1 MIPSpro C In the section, "Multi-threaded Applications", the script file bldccmt, as documented, should include the link option, -lpthread. The script file has this option in the sqllib/samples/c directory. Here are the correct link steps in bldccmt: # Link the program. cc -o $1 $1.o util.o -L$DB2PATH/lib -rpath $DB2PATH/lib -lm -ldb2_th -lpthread # Link the program for n32 object type support. # cc -n32 -o $1 $1.o util.o -L$DB2PATH/lib32 -rpath $DB2PATH/lib32 -lm -ldb2_th -lpthread 5.7.2 MIPSpro C++ In the section, "Multi-threaded Applications", the script file bldCCmt, as documented, should include the link option, -lpthread. The script file has this option in the sqllib/samples/cpp directory. Here are the correct link steps in bldCCmt: # Link the program. CC -o $1 $1.o util.o -L$DB2PATH/lib -rpath $DB2PATH/lib -lm -ldb2_th -lpthread # Link the program for n32 object type support. # CC -n32 -o $1 $1.o util.o -L$DB2PATH/lib32 -rpath $DB2PATH/lib32 -lm -ldb2_th -lpthread ------------------------------------------------------------------------ 5.8 Building Applications for Windows 32-bit Operating Systems In the section, "IBM VisualAge COBOL", sub-section, "Embedded SQL Stored Procedures", the batch file bldvacbs is documented as having "lib" in the first line of the link step. This should be ilib. The link step in the batch file in %DB2PATH%\samples\cobol has the correct link step as follows: rem Link the stored procedure and create a shared library. ilib /nol /gi:%1 %1.obj ilink /free /nol /dll db2api.lib %1.exp %1.obj iwzrwin3.obj ------------------------------------------------------------------------ 5.9 Building OS/2 Programs - Java - Building SQLJ Programs To build and run SQLJ programs with the OS/2 Java Development Kit (JDK), you must turn off the just-in-time compiler of the JDK with the following command: SET JAVA_COMPILER=NONE SQLJ source files may include an end-of-file character (EOF) that causes the SQLJ translator "sqlj" to fail on OS/2 with an exception similar to the following: Cursor.sqlj:122.2: Error: Java Parsing. Encountered: \u001a Expected: ; "abstract" ...; "public" ...; "interface" ...; "#sql" ...; ";" ...; Total 1 error. To remove the offending EOF, open the SQLJ source file in a text editor that does not automatically append an EOF, modify the file by adding a blank line to the end of the file, and resave the file. ------------------------------------------------------------------------ Application Development Guide ------------------------------------------------------------------------ 6.1 Federated Server Functionality Not Available The distributed join installation feature and associated federated system functionality, as documented, are not currently supported with DB2 UDB Version 6.1. ------------------------------------------------------------------------ 6.2 SQLJ Programming All SQLJ stored procedures must be run in FENCED mode. SQLJ and JDBC result set interoperability is not supported. The SQLJ option "-default-customizer" is not supported. To customize SQLJ profiles, use the "db2profc" command (see the Command Reference). SQLJ applications that use iterators for positioned updates and deletes cannot use a FOR UPDATE OF clause in the SQLJ statement that assigns the iterator. The sample SQLJ program "Openftch.sqlj", as documented in the book, erroneously uses the FOR UPDATE OF clause. Please refer to the "Openftch.sqlj" program available in the Java samples directory of the DB2 Software Developer's Kit for a correct example of using positioned update iterators in an SQLJ application. To explicitly identify the columns that are updatable for an iterator, use the updateColumns keyword in the iterator declaration clause. For example, to identify a column named JOB updatable for an iterator called OpF_Curs, use the following declaration clause: #sql public iterator OpF_Curs implements ForUpdate with (updateColumns = "JOB") (String, short); ------------------------------------------------------------------------ 6.3 JDBC Version 2.0 Support JDBC Version 2.0 support (limited to Windows NT) is currently available only to JDBC applications and applets, not to Java stored procedures or user-defined functions, or to SQLJ applications or applets. ------------------------------------------------------------------------ 6.4 DB2 UDB Integration into the Visual Studio Development Environment The DB2 SDK provides developers with two Visual Studio Version 6.0 development extensions. These extensions plug into the Visual C++ IDE to enable the launching of various DB2 GUI tools, and facilitate the development of embedded SQL applications and embedded SQL stored procedures using Microsoft Visual Studio Version 6.0. These two extensions are available from: www.software.ibm.com\data\db2\udb\ide To enable the extensions, do the following: 1. Register the extensions by executing the command: db2vscmd register 2. Activate the extensions by customizing Visual Studio: a. Launch MS Visual C++. b. Select the Tools Menu. c. Select the Customize menu item. d. Select the Add-ins and Macro Files tab. e. Select the check box for the IBM DB2 UDB Project Add-In and for the IBM DB2 UDB Tools Add-In. It is assumed that the following standard Visual Studio Version 6.0 components are installed: * Microsoft Visual Basic 6.0 Run-Time * Microsoft Common Controls * Microsoft Internet Controls * Visual C++ Debugger ActiveX Control * Visual C++ Project System ActiveX Control * Visual C++ Shared Objects ActiveX Control * Visual C++ Text Editor ActiveX Control All of these components are installed as part of the Microsoft Visual Studio Version 6.0 product. More information about the DB2 Visual Studio extensions can be found at: www.software.ibm.com\data\db2 and www.software.ibm.com\data\db2\udb\ide ------------------------------------------------------------------------ 6.5 SQL Statements in Stored Procedures In Chapter 5, "Stored Procedures", replace the text of the subsection entitled "SQL Statements in Stored Procedures" with the following: Stored procedures can contain SQL statements. When you issue the CREATE PROCEDURE statement, you should specify the type of SQL statements the stored procedure executes, if any. If you do not specify a value when you register the stored procedure, the database manager uses the restrictive default value MODIFIES SQL DATA. To indicate the type of SQL used in the stored procedure, you can use one of the following four options: NO SQL Indicates that the stored procedure cannot execute any SQL statements (SQLSTATE 38001). CONTAINS SQL Indicates that SQL statements that neither read nor modify SQL data can be executed by the stored procedure (SQLSTATE 38004). Statements that are not supported in any stored procedure return a different error (SQLSTATE 38003). READS SQL DATA Indicates that some SQL statements that do not modify SQL data can be included in the stored procedure (SQLSTATE 38002). Statements that are not supported in any stored procedure return a different error (SQLSTATE 38003). MODIFIES SQL DATA Indicates that the stored procedure can execute any SQL statement except statements that are not supported in stored procedures (SQLSTATE 38003). For more information about the CREATE PROCEDURE statement, refer to the SQL Reference. ------------------------------------------------------------------------ 6.6 Writing Stored Procedures The Java code for the sample stored procedure Outsrv.sqlj, found in "Chapter 6. Writing Stored Procedures", has changed since the book was published. Please refer to the sample Outsrv.sqlj in the Java samples directory for an updated version of the stored procedure. ------------------------------------------------------------------------ 6.7 Debugging Java Stored Procedures/Distributed Debugger The Distributed Debugger is part of IBM VisualAge for Java Professional Edition. The Debugger daemon on the client side is started with: idebug -qdaemon -quiport= The environment variable for the location of source code on the client is DER_DBG_PATH. Please refer to the IBM Distributed Debugger online help for further information. ------------------------------------------------------------------------ 6.8 PARAMETER STYLE JAVA Stored Procedures The java_method_signature described in the SQLJ Routines specification is not supported for the CREATE PROCEDURE and CREATE FUNCTION statements. DB2 ignores the java_method_signature if you specify it in a CREATE PROCEDURE or CREATE FUNCTION statement. Java arrays are not supported as parameters to PARAMETER STYLE JAVA routines. You cannot use the SQL types of BINARY, VARBINARY, LONGVARBINARY, BLOB, CLOB, and DBCLOB as parameters with PARAMETER STYLE JAVA routines. If you need to work with LOB data types as parameters, consider using PARAMETER STYLE DB2GENERAL stored procedures. To return result sets from a PARAMETER STYLE JAVA stored procedure, the number of result set parameters for the stored procedure must be equal to the number you declare in the DYNAMIC RESULT SETS clause of the CREATE PROCEDURE statement. ------------------------------------------------------------------------ 6.9 Installing, Removing, and Replacing JAR Files The sqlj.install_jar and related procedures do not issue a COMMIT after they complete, because this would also commit any other outstanding changes. The calling application must issue a COMMIT when the unit of work is complete. If these procedures are issued as CLP commands, they respect the setting of the CLP autocommit flag. There is no online help available for the CALL command issued from the command line processor. For more information, refer to the Command Reference. The sqlj.install_jar and sqlj.replace_jar procedures place jar files into the database system for use as Java routines. The Java run time environment on the DB2 server will open all the jar files at initialization time, and keep these files locked as long as it is operational. This is done for performance reasons. The impact is that a replace or remove jar action will fail if the physical jar file is locked. Issuing a "db2 force applications all" command, followed by db2stop, then db2start prior to a replace or remove jar request will ensure that no fenced or unfenced Java routines are executing. ------------------------------------------------------------------------ CLI Guide and Reference ------------------------------------------------------------------------ 7.1 How to Bind the DB2 CLI/ODBC Driver to the Database The following information replaces the information currently available in this section. The table below lists DB2 CLI bind files. The first 4 bind files (db2clixx where xx is sh, sn, bh, and bn) are used mainly for execution of DB2 CLI application SQL statements. The bind files db2clish.bnd and db2clisn.bnd contains 64 sections each. The bind files db2clibh.bnd and db2clibn.bnd contains 384 sections each. Each time one of these bind files gets bound against a database, the binder will generate multiple packages whose names follow the convention listed in the table below. For each isolation level and With Hold/Without Hold property, the binder will create 3 packages (3 is the default number) from each bind file. Therefore, for each isolation level and With Hold/Without Hold combination, there are (64 * 3) + (384 * 3) or 1344 sections available for execution of DB2 CLI application SQL statements. This number should meet the need of most applications. If your application requires more sections, you can increase the number of sections by one of the following two ways: 1. Add the CLI/ODBC configuration keyword CLIPKG to the db2cli.ini file to specify the number of large packages (containing 384 sections) to be generated. For example, you can create 5 packages (instead of the default 3) for each of the large bind files by adding the following to the db2cli.ini file: [dbname] CLIPKG=5 Note that if you add the CLIPKG keyword to the [COMMON] section of db2cli.ini file, it will be applied to all databases. 2. If you choose not to use CLI/ODBC configuration keyword you can also manually bind the bind files with the bind option CLIPKG. For example, you can use the following CLP command to create 5 packages for each large bind file: db2 bind @db2cli.lst grant public CLIPKG 5 Note: * When you increase the number of sections using either method, you MUST update the db2cli.ini on the Server with the keyword CLIPKG showing the exact number of large packages that you created or specified. This is required especially if you have CLI/ODBC Stored Procedures. * The keyword or bind option CLIPKG only applies to large packages (containing 364 sections). The number of small packages (containing 64 sections) is 3 and cannot be changed. * The value of CLIPKG keyword or bind option can be from 3 to 30. It is recommended that you only increase the number of sections enough to run your application as the packages take up space in the database. Although the autobind feature of the CLI/ODBC driver will attempt to bind CLI bind files if the CLI packages do not exist in the database or their time stamps have changed, the administrator should explicitly bind the required files. This is to avoid the following two situations: 1. The user does not have the required privilege or authorization to bind the bind files. 2. The autobind is invoked in the middle of a transaction and AUTOCOMMIT is set to OFF. (You can get into this situation after installing a Service Pack and the database is an existing database). In this case, if the server is DB2 UDB on UNIX and Intel platforms V6.1 or later, the autobind will open a second connection and perform the binding. If the second connection fails to open for any reason, the current transaction will be rolled back and the bind will be done using the current connection. If the server is on a host system other than UNIX and Intel platforms, the current transaction will always be rolled back and the bind will be done using the current connection. In summary, if AUTOCOMMIT is set to OFF, and the server is not a DB2 UDB V6.1 or later on a UNIX or Intel platform, the administrator should always manually bind the CLI bind files to existing database every time a Service Pack is installed. Table 1. DB2 CLI Bind Files and Package Names Needed by DB2 Needed by DRDA Bind File Name Package Name Universal Database servers db2clish.bnd SQLLFyxx Yes Yes db2clisn.bnd SQLLCyxx Yes Yes db2clibh.bnd SQLLDyxx Yes Yes db2clibn.bnd SQLLEyxx Yes Yes db2cliws.bnd SQLL65zz Version 2 or later No db2clims.bnd SQLL75zz No DB2 for MVS/ESA db2clivm.bnd SQLL85zz No SQL/DS db2cliv1.bnd SQLLB5zz Version 1 only No db2cliv2.bnd SQLL95zz Version 2 or later No db2clias.bnd SQLLA5zz No DB2 Universal Database for AS/400 Note: * Where 'xx' is a hexadecimal value between 00 - FF. * Where 'y' ranges between 0 - 4. * Where 'zz' is unique for each platform. Previous versions of DB2 servers do not need all of the bind files and will therefore return errors at bind time. The db2cli.lst file contains the names of the required bind files for DB2 CLI to connect to DB2 Version 2 or later servers. The db2cli1.lst file contains the names of the required bind files for DB2 CLI to connect to DB2 Version 1 servers. For DRDA servers: * use one of ddcsvm.lst, ddcsmvs.lst, ddcsvse.lst, or ddcs400.lst bind list files. * Refer to the Quick Beginnings or DB2 Connect Enterprise Edition for OS/2 and Windows NT Quick Beginnings for details about required bind options. ------------------------------------------------------------------------ 7.2 New CLI/ODBC Configuration Keyword The following CLI/ODBC configuration keywords have been added for v6.1 but are not documented in the manual: 7.2.1 CLIPKG Keyword Description: number of large packages to be generated db2cli.ini Keyword Syntax: CLIPKG=3 | 4 | ... | 30 Default Setting: 3 DB2 CLI/ODBC Settings Tab: This keyword cannot be set using the CLI/ODBC Settings notebook. The db2cli.ini file must be modified directly to make use of this keyword. Usage Notes: If the value is NOT an integer between 3 and 30, the default will be used without error or warning. This keyword is used to increase the number of sections for SQL statements in CLI/ODBC applications. If it is used, the administrator should explicitly bind the required CLI bind files with CLIPKG bind option. Also, the db2cli.ini file on the server (DB2 UDB V6.1 or later on UNIX or Intel platforms) must be updated with the same value of CLIPKG. 7.2.2 QUERYTIMEOUTINTERVAL Keyword Description: Delay (in seconds) between checking for a query timeout db2cli.ini Keyword Syntax: QUERYTIMEOUTINTERVAL=0 | positive integer Default Setting: 5 seconds DB2 CLI/ODBC Settings Tab: This keyword cannot be set using the CLI/ODBC Settings notebook. The db2cli.ini file must be modified directly to make use of this keyword. Usage Notes: An application can use the SQLSetStmtAttr() function to set the SQL_ATTR_QUERY_TIMEOUT statement attribute. This indicates the number of seconds to wait for an SQL statement to execute before returning to the application (see 7.3, SQLSetStmtAttr() change for SQL_ATTR_QUERY_TIMEOUT. The QUERYTIMEOUTINTERVAL configuration keyword is used to indicate how long the CLI driver should wait between checks to see if the query has completed. For instance, suppose SQL_ATTR_QUERY_TIMEOUT is set to 25 seconds (timeout after waiting for 25 seconds), and QUERYTIMEOUTINTERVAL is set to 10 seconds (check the query every 10 seconds). The query will not time out until 30 seconds (the first check AFTER the 25 second limit). There may be cases where the SQL_ATTR_QUERY_TIMEOUT is set to a value which is too low, and the query should NOT be timed out. If the application cannot be modified (i.e., a third party ODBC application), then the QUERYTIMEOUTINTERVAL can be set to 0, and the CLI driver will ignore the SQL_ATTR_QUERY_TIMEOUT setting. ------------------------------------------------------------------------ 7.3 SQLSetStmtAttr() change for SQL_ATTR_QUERY_TIMEOUT The description of SQL_ATTR_QUERY_TIMEOUT should be replaced with the following: SQL_ATTR_QUERY_TIMEOUT A 32-bit integer value that indicates the number of seconds to wait for an SQL statement to execute before returning to the application. This option can be set and used to terminate long running queries. If set to 0, there is no time-out. See 7.2.2, QUERYTIMEOUTINTERVAL for complete details. ------------------------------------------------------------------------ 7.4 Using Reference Types The following section is added to "Chapter 3. Using Advanced Features": In addition to the distinct types, user defined structured types can also be defined and used as the type for a table or view. Tables or views that are defined using a structured type are called typed tables or typed views. Structured types can be defined in a hierarchy with subtypes and supertypes. These structured types are created using the CREATE TYPE statement. Rows of a typed table or view are identified with an object identifier (OID) that is a reference type. A reference type is defined to have a target type, which must be a structured type. When the root structured type (the structured type without a supertype) is defined, the representation type for the reference type is defined as based on a built-in data type. Similar to user defined types (UDTs), a reference type shares its internal representation with an existing type, but is considered to be a separate and incompatible type for most operations. Reference types provide a means of referring to rows in typed tables or typed views. Applications continue to work with C data types for application variables, and only need to consider the reference types when constructing C statements. This means: o All SQL to C data type conversion rules that apply to the reference type's underlying SQL built-in type apply to the reference type. o The reference type will have the same default C Type as the underlying SQL built-in type. o SQLDescribeCol() will return the representation (built-in) type information. The target type name can be obtained by calling SQLColAttribute() with the input descriptor type set to SQL_DESC_REFERENCE_TYPE. o SQL predicates that involve parameter markers must be explicitly cast to the reference type. This is required, because the application can only deal with the built-in types. Before any operation can be performed using the parameter, it must be cast from the C built-in type to the reference type; otherwise, an error will occur when the statement is prepared. For complete rules and a description of reference types, refer to the SQL Reference. ------------------------------------------------------------------------ 7.5 Errors Rebinding Version 5.2 CLI (IBM DB2 ODBC Driver) Packages When using db2rbind to rebind all database packages for a Version 5 database migrated to Version 6, some packages (starting with SQLL) may fail. The failing packages were part of any Version 5 CAE prior to FixPak 7 These errors can safely be ignored. However, it is necessary to upgrade all Version 5 CAEs to FixPak 7 or higher in order for the CLI/ODBC schema functions, such as SQLTables() and SQLColumns(), to be supported against a Version 6 database. Otherwise, SQL0805N errors will be returned whenever these functions are executed. Once all CAEs have been upgraded, a Version 5 database may still contain older packages that will give errors on rebind at migration time. ------------------------------------------------------------------------ Command Reference ------------------------------------------------------------------------ 8.1 db2advis - DB2 Index Advisor The syntax diagram and parameter description incorrectly refer to the parameter "-f filename"; this should be changed to "-i filename". In the Examples section, the third example shows the use of options "-c" and "-n". However, these options are not supported, and are not included in the syntax diagram. The example should be disregarded. The Usage Notes section has been changed to: For dynamic SQL statements, the frequency with which statements are executed can be obtained from the monitor as follows: 1. Issue db2 reset monitor for database . Wait for an appropriate interval of time. 2. Issue db2 get snapshot for dynamic sql on write to file. 3. Issue db2 "insert into advise_workload (select 'myworkload', 0,stmt_text, cast(generate_unique() as char(254)), num_executions, 1, num_executions, 0, 0 from table(SYSFUN.SQLCACHE_SNAPSHOT()) as correlation_name)". The default frequency for each SQL statement in a workload is 1, and the default importance is also 1. The generate_unique() function assigns a unique identifier to the statement, which can be updated by the user to be a more meaningful description of that SQL statement. ------------------------------------------------------------------------ 8.2 db2batch - Benchmark Tool Change the first sentence of the Usage Notes section to: Although SQL statements can be up to 65 535 characters in length, no text line in the input file can exceed 3 898 characters, and long statements must be divided among several lines. ------------------------------------------------------------------------ 8.3 db2jstrt - DB2 JDBC Applet Server (New Command) The following information is to be added to the Command Reference: db2jstrt - DB2 JDBC Applet Server Starts the JDBC applet server on the specified TCP/IP port number. The JDBC applet server is a server daemon that enables SQLJ and JDBC applets to connect to the DB2 server. To terminate the JDBC applet server, kill the JDBC applet server process. The JDBC applet server process is identified as "db2jd" on UNIX operating systems, and "db2jd.exe" on OS/2 and Windows 32-bit operating systems. Syntax: +-----6789------+ | | >>---db2jstrt------+---------------+-------->< | | +--+ where represents a TCP/IP port number that is not registered for another service. The following entry is to be added to the index: "db2jd -- see db2jstrt". ------------------------------------------------------------------------ 8.4 db2look - DB2 Statistics Extraction Tool Change the first paragraph of the description of this tool to: Generates DDL statements to reproduce the objects of a production database on a test database. The utility can also generate the required DML statements to replicate the statistics on those objects in the test database. The description of the "-e" option states that triggers are not supported. That is no longer true. If the database contains triggers, db2look will generated the DDL statements to recreate these triggers if the "-e" option is specified. The "-a" option specifies all creators. If this option is specified, the utility will consider all the user tables in the database, The "-a" option does not require that the "-t" option also be specified. The following options are missing from the documentation: * Specify the "-l" option (database layout) to get the DDL for table spaces, bufferpools, or nodegroups. * Specify the "-x" option to get authorization DDL statements. * Specify the "-i" option when working with a remote database. Following is the correct syntax for db2look: >>-db2look---d--DBname----+--------------+-------------> '--u--Creator--' >-----+-------------------------+----------------------> '-+----+---+----+--+----+-' '--s-' '--g-' '--a-' >-----+--------------------------------------------------------------------------------------+> '-+----+---o--Fname--+-----------------+--+-----------------+--+----+--+----+--+----+--' '--p-' '--e--+----------+' '--m--+----------+' '--c-' '--r-' '--h-' '--t--Tname' '--t--Tname' >-----+---------------------------------------------------+---------------------------------->< '-+----+---+----+--+------------+---+-------------+-' '--l-' '--x-' '--i--userID-' '--w--password' ------------------------------------------------------------------------ 8.5 db2move - Database Movement Tool When issued on a Version 5.2 client against a Version 6.1 database, this command does not support table or column names that are greater than 18 characters in length. ------------------------------------------------------------------------ 8.6 db2rbind - Rebind all Packages This command has a new option, /r, with two valid values, "conservative" and "any": >>--db2rbind--database--/l logfile--.-----.--.------------------------.----> '-all-' '-/u userid--/p password-' .--/r conservative--. >--+-------------------+-------------------------------------------------->< '--/r any-----------' Specifying "/r conservative" causes the RESOLVE CONSERVATIVE option to be used during rebind. Only functions and types in the SQL path that were defined before the last explicit bind time stamp (for each package) are considered for function and type resolution. Conservative binding semantics are used. This is the default. Specifying "/r any" causes the RESOLVE ANY option to be used during rebind. Any of the functions and types in the SQL path are considered for function and type resolution. Conservative binding semantics are not used. ------------------------------------------------------------------------ 8.7 db2sql92 - SQL92 Compliant SQL Statement Processor Change the second to the last paragraph of the Usage Notes section to: SQL statements can be up to 65 535 characters in length. Statements must be terminated by a semicolon. ------------------------------------------------------------------------ 8.8 Issuing Commands from the MS-DOS Prompt in Command Line Mode (Windows 95) When commands are entered from the MS-DOS prompt in command line mode, the Windows 95 DOS command processor preprocesses them in such a way that everything entered between pairs of percent signs (%) is interpreted as a variable name. For example: db2 "SELECT NAME, CREATOR FROM SYSIBM.SYSPLAN WHERE NAME LIKE 'SQLL%' OR NAME LIKE 'SQLC%'" gets passed to db2.exe as: "SELECT NAME,CREATOR FROM SYSIBM.SYSPLAN WHERE NAME LIKE 'SQLL'" (%' OR NAME LIKE 'SQLC% is treated by DOS as a variable whose value is NULL) To avoid this behavior, use a double percent sign (%%) to indicate that a percent sign (and not a variable) is to be passed to the program: db2 "SELECT NAME, CREATOR FROM SYSIBM.SYSPLAN WHERE NAME LIKE 'SQLL%%' OR NAME LIKE 'SQLC%%'" This gets passed to db2.exe as: "SELECT NAME, CREATOR FROM SYSIBM.SYSPLAN WHERE NAME LIKE 'SQLL%' OR NAME LIKE 'SQLC%'" ------------------------------------------------------------------------ 8.9 ADD DATALINKS MANAGER When registering one or more DB2 Data Links Managers for a database using this command, ensure that the DB2 Data Links Manager is not registered twice; otherwise, error SQL20056N with reason code "99" may be returned during DataLink processing. The db2diag.log file for the DB2 Data Links Manager server that is registered twice will have the following entry when such a failure occurs: dfm_xnstate_cache_insert : Duplicate txn entry. dfmBeginTxn : Unable to insert ACTIVE transaction in cache, rc = 41. DLFM501E : Transaction management service failed. ------------------------------------------------------------------------ 8.10 BIND Following are some bind considerations in a client/server environment: In a client/server environment, when a BIND command is issued on a pre-Version 6.1 client connected to a Version 6.1 server, an erroneous SQL0020W may be returned to the client. This situation will be encountered if the command uses either or both of the parameters DYNAMICRULES or OWNER. Although the error message is displayed, the parameters are still effective, and will be used. Any other parameters displayed in the SQL0020W message are correctly noted as errors, and will be ignored as indicated by the message. These other parameters include, but are not restricted to, the following: ACTION, CCSIDG, CCSIDM, CCSIDS, CHARSUB, CNULREQD, DEC, DECDEL, GENERIC, RELEASE, REPLVER, RETAIN, STRDEL, TEXT, VALIDATE, VERSION. ------------------------------------------------------------------------ 8.11 GET SNAPSHOT The format of the data returned from this command has changed for Version 6, and the sample outputs for Table Snapshot & Database Lock Snapshot are incorrect. Longer table names do not permit the collected data to be displayed in a tabular format. Table and lock data is now returned in individual lines. For example: Table Schema Table Name Table Type Rows Written Rows Read Overflows -------------------- -------------------- -------------------- ------------ ---------- ---------- USER1 STAFF User 0 35 0 ... becomes Table Schema = USER1 Table Name = STAFF Table Type = User Rows Written = 0 Rows Read = 35 Overflows = 0 ------------------------------------------------------------------------ 8.12 Compound=x File Type Modifier (Import) If this modifier is specified, and the transaction log is not sufficiently large, the import operation will fail. The transaction log must be large enough to accommodate either the number of rows specified by COMMITCOUNT, or the number of rows in the data file if COMMITCOUNT is not specified. It is therefore recommended that the COMMITCOUNT option be specified to avoid transaction log overflow. This modifier is incompatible with INSERT_UPDATE mode, hierarchical tables, and the USEDEFAULTS modifier. ------------------------------------------------------------------------ 8.13 MIGRATE DATABASE If an error occurs during migration, it may be necessary to issue a "db2 terminate" command before attempting the suggested user response. For example, if a log full error occurs during migration (SQL1704: Database migration failed. Reason code "3".), it will be necessary to issue "db2 terminate" before increasing the values of the database configuration parameters LOGPRIMARY and LOGFILSIZ. The CLP must refresh its database directory cache if the migration failure occurs after the database has already been relocated (which is likely to be the case when a "log full" error returns). ------------------------------------------------------------------------ 8.14 PRUNE HISTORY/LOGFILE Change the description of "LOGFILE PRIOR TO log-file-name" to the following: Specifies a string for a log file name, for example S0000100.LOG. All log files prior to (but not including) the specified log file will be deleted. The LOGRETAIN database configuration parameter must be set to "RECOVERY" or "CAPTURE". ------------------------------------------------------------------------ 8.15 REBIND This command has a new option, RESOLVE, with two valid values, "ANY" and "CONSERVATIVE": .--RESOLVE--ANY-----------. >>-REBIND-+---------+--package-name--+-------------------------+-->< '-PACKAGE-' '--RESOLVE--CONSERVATIVE--' RESOLVE Specifies whether rebinding of the package is to be performed with or without conservative binding semantics. This affects whether new functions and data types are considered during function resolution and type resolution on static DML statements in the package. This DB2 option is not supported by DRDA. Valid values are: ANY Any of the functions and types in the SQL path are considered for function and type resolution. Conservative binding semantics are not used. This is the default. CONSERVATIVE Only functions and types in the SQL path that were defined before the last explicit bind time stamp are considered for function and type resolution. Conservative binding semantics are used. ------------------------------------------------------------------------ 8.16 RECONCILE Reconcile produces a message file (reconcil.msg) in the instance path on UNIX based systems, and in the install path on Windows NT and 0S/2. This file will contain warning and error messages that are generated during validation of the exception table. In the Usage Notes section, there is a list of possible violations that can appear in the exception table, or an exception report. Following are two new error codes that should be added to this list: 00008 File restored but modified file has been copied to .MOD 00009 File is already linked to another table. ------------------------------------------------------------------------ 8.17 REORGANIZE TABLE For typed tables, the specified table name must be the name of the hierarchy's root table. The following sentence is to be added to the first paragraph of the Usage Notes section: The reorganize utility will implicitly close all the cursors. ------------------------------------------------------------------------ 8.18 REORGCHK For typed tables, the specified table name must be the name of the hierarchy's root table. ------------------------------------------------------------------------ 8.19 RESTORE DATABASE On Windows operating systems and on OS/2, specify only the drive letter when using the "TO target-directory" parameter. If a longer path is specified, an error is returned. ------------------------------------------------------------------------ 8.20 RUNSTATS The description of "TABLE table-name" includes the statement: For row types, table-name must be the name of the hierarchy's root table. This statement should be changed to: For typed tables, the specified table name must be the name of the hierarchy's root table. ------------------------------------------------------------------------ 8.21 Using Command Line SQL Statements For an updated version of Table 8, "SQL Statements (DB2 Universal Database)", see the Application Development Guide, or the CLI Guide and Reference. ------------------------------------------------------------------------ Data Movement Utilities Guide and Reference ------------------------------------------------------------------------ 9.1 Compound=x File Type Modifier (Import) If this modifier is specified, and the transaction log is not sufficiently large, the import operation will fail. The transaction log must be large enough to accommodate either the number of rows specified by COMMITCOUNT, or the number of rows in the data file if COMMITCOUNT is not specified. It is therefore recommended that the COMMITCOUNT option be specified to avoid transaction log overflow. This modifier is incompatible with INSERT_UPDATE mode, hierarchical tables, and the USEDEFAULTS modifier. ------------------------------------------------------------------------ 9.2 Checking for Constraints Violations (Load) Replace the fifth paragraph in this section with the following: When issuing the SET INTEGRITY statement, you can specify the INCREMENTAL option to explicitly request incremental processing. In most cases, this option is not needed, because the default behavior is incremental processing. If incremental processing is not possible, full processing is used automatically. When the INCREMENTAL option is specified, but incremental processing is not possible, an error is returned if: o New constraints have been added to the table or to its parent table while both tables are in check pending state. o A load replace operation has taken place, or the NOT LOGGED INITIALLY WITH EMPTY TABLE option has been activated, after the last integrity check on the table. o A parent table has been load replaced or checked for integrity non-incrementally. o The table was in check pending state before migration. Full processing is required the first time the table is checked for integrity after migration. o The table space containing the table or its parent has been rolled forward to a point in time. ------------------------------------------------------------------------ 9.3 SQLULOAD-IN Data Structure The description of this structure is missing the "indexing_mode" parameter. Supported values (defined in sqlutil.h) are: SQLU_INX_AUTOSELECT SQLU_INX_REBUILD SQLU_INX_INCREMENTAL SQLU_INX_DEFRRED For an explanation of these indexing modes, see the description of the LOAD command. ------------------------------------------------------------------------ 9.4 Example Load Sessions/API Examples/tload.sqc Sample Program This sample program is missing the "indexing_mode" parameter of the sqluload-in data structure. It should be added as follows: /* the sqluload input structure */ ... InputInfo.statsopt = SQLU_STATS_NONE; /* don't bother collecting them */ InputInfo.indexing_mode = SQLU_INX_AUTOSELECT; /* let load choose indexing mode */ ------------------------------------------------------------------------ 9.5 db2LoadQuery - Load Query API Change the name of the generic API "db2GenLoadQuery" to "db2gLoadQuery". Change the name of the structure used by the generic API "db2gLoadQuery" to "db2gLoadQueryStruct". A Fortran version of the "loadqry" sample program is not available. ------------------------------------------------------------------------ 9.6 AutoLoader Options (SPLIT_ONLY) If SPLIT_FILE_LOCATION points to an NFS directory that is mounted across all partitions after a SPLIT_ONLY operation, a LOAD_ONLY operation will be able to access the split data files without any user intervention. However, if SPLIT_FILE_LOCATION does not point to an NFS directory after a SPLIT_ONLY operation, users are required to manually move the split data files from each partition to the directory in which the LOAD_ONLY operation will be executed. ------------------------------------------------------------------------ Installation and Configuration Supplement ------------------------------------------------------------------------ 10.1 Federated Server Functionality Not Available The distributed join installation feature and associated federated system functionality, as documented, are not currently supported with DB2 UDB Version 6.1. ------------------------------------------------------------------------ 10.2 Setting Up Server Communications This section states that eNetwork Communications Server for AIX V5.0.2.4 is the minimum required level. This has changed to V 5.0.3.0 , which can be downloaded from: http://service.software.ibm.com/cgi-bin/support/rs6000.support/downloads Select AIX General Software Fixes, AIX Fix Distribution Service, AIX Version 4 & PTF Number (4 character minimum), and enter search string 5.0.3.0 . Select Find Fix. Once it is listed, select Get Fix Package, and follow the instructions. ------------------------------------------------------------------------ 10.3 Using The Command Line Processor to Configure Server Communications - Configuring APPC on the Server The DB2SERVICETPINSTANCE registry variable must be set globally, so that all instances on a given machine can obtain its value. The command to set this globally is: db2set -g db2servicetpinstance= If DB2SERVICETPINSTANCE is not set, and multiple instances are configured to support APPC connections, unpredictable results will occur; that is, it will not be possible to predict which instance listens for the default TPs. ------------------------------------------------------------------------ 10.4 Accessing DB2 Universal Database Servers from Host and AS/400 Application - Supported Clients The list of supported clients should be changed to: - DB2 for AS/400 V3R2 and V4 (or higher) - DB2 for VM & VSE Version 5 (or higher). ------------------------------------------------------------------------ 10.5 APPC Connections (SNA) This section states that eNetwork Communications Server for AIX V5.0.2.4 is the minimum required level. This has changed to V5.0.3.0 , which can be downloaded from: http://service.software.ibm.com/cgi-bin/support/rs6000.support/downloads Select AIX General Software Fixes, AIX Fix Distribution Service, AIX Version 4 & PTF Number (4 character minimum), and enter search string 5.0.3.0 . Select Find Fix. Once it is listed, select Get Fix Package, and follow the instructions. ------------------------------------------------------------------------ 10.6 Using the DB2 Universal Database Server from Host and AS/400 Clients This section states that if only a single instance will be accessed by the host or AS/400 client, there is no need to specify the DB2SERVICETPINSTANCE registry value. This should be changed to: If there is only a single instance configured to accept inbound APPC connections, there is no need to set the DB2 SERVICETPINSTANCE registry value. ------------------------------------------------------------------------ Message Reference ------------------------------------------------------------------------ 11.1 Federated Server Functionality Not Available The distributed join installation feature and associated federated system functionality, as documented, are not currently supported with DB2 UDB Version 6.1. ------------------------------------------------------------------------ 11.2 SQL0109N (Addition to Explanation) The following statement should be added to the end of the first paragraph in the explanation: The OVER clause can only be specified following a function that is supported as an OLAP function. ------------------------------------------------------------------------ 11.3 SQL0112N (Changed Message) SQL0112NThe operand of the column function "" includes a column function, an OLAP function, a scalar fullselect, or a subquery. Explanation: The operand of a column function cannot include a: * column function * OLAP function * scalar fullselect * subquery. In a SELECT list, the operand of an arithmetic operator cannot be a column function that includes the DISTINCT keyword. The statement cannot be processed. User Response: Correct the use of the column function to eliminate the invalid expression and try again. sqlcode: -112 sqlstate: 42607 ------------------------------------------------------------------------ 11.4 SQL0120N (Changed Message) SQL0120NA WHERE clause, GROUP BY clause, HAVING clause, SET clause, or SET transition-variable statement contains a column or OLAP function. Explanation: A WHERE clause can contain a column function only if that clause appears within a subquery of a HAVING clause, and the argument of the column function is a correlated reference to a group. A GROUP BY clause can contain a column function only if the argument of the column function is a correlated reference to a column in a different subselect than the one containing the GROUP BY clause. A SET clause of an UPDATE statement or a SET transition-variable statement can only include a column function within a fullselect on the right hand side of an assignment. An OLAP function can only appear in the select list of a subselect. The statement cannot be processed. User Response: Change the statement so that the column or OLAP function is not used or used only where it is supported. sqlcode: -120 sqlstate: 42903 ------------------------------------------------------------------------ 11.5 SQL0206N (Changed Message) SQL0206N"" is not a column in an inserted table, updated table, or any table identified in a FROM clause, or is not a valid transition variable for the subject table of a trigger. Explanation: This error can occur in the following cases: * For an INSERT or UPDATE statement, the specified column is not a column of the table, or view that was specified as the object of the insert or update. * For a SELECT or DELETE statement, the specified column is not a column of any of the tables or views identified in a FROM clause in the statement. * For a PARTITION BY clause or and ORDER BY clause included with an OLAP function, the specified column is a correlated column reference to a higher level subselect, which is not allowed. * For an ORDER BY clause, the specified column is a correlated column reference in a subselect, which is not allowed. * For a CREATE TRIGGER statement: o A reference is made to a column of the subject table without using an OLD or NEW correlation name. o The left hand side of an assignment in the SET transition-variable statement in the triggered action specifies an old transition variable where only a new transition variable is supported. The statement cannot be processed. User Response: Verify that the column and table names are specified correctly in the SQL statement. For a SELECT statement, ensure that all the required tables are named in the FROM clause. For OLAP functions, ensure the column references in the window partition clause and the window order clause are to columns in the same subselect. For a subselect in an ORDER BY clause, ensure that there are no correlated column references. If a correlation name is used for a table, verify that subsequent references use the correlation name and not the table name. For a CREATE TRIGGER statement, ensure that only new transition variables are specified on the left hand side of assignments in the SET transition-variable statement, and that any references to columns of the subject table have a correlation name specified. sqlcode: -206 sqlstate: 42703 ------------------------------------------------------------------------ 11.6 SQL1179W (New Message) SQL1179WThe "" called "" may require the invoker to have necessary privileges on data source objects. Explanation: The object identified by "" references an OLE DB table function or a nickname where the actual data exists at a data source. When the data source data is accessed, the user mapping and authorization checking is based on the user that initiated the operation. If the "" is SUMMARY TABLE, then the operation is refreshing the data for the summary table. The user that invoked the REFRESH TABLE or SET INTEGRITY statement that cause the refresh may be required to have the necessary privileges to access the underlying data source object at the data source. If the "" is VIEW, then any user of the view may be required to have the necessary privileges to access the underlying data source object at the data source. In either case, an authorization error may occur when the attempt is made to access the data source object. User Response: Granting of privileges to the view or summary table may not be sufficient to support operations that access the data from the data source. User access may need to be granted at the data source for the underlying data source objects of the view or summary table. sqlcode: 1179 sqlstate: 01639 ------------------------------------------------------------------------ 11.7 SQL1592N (Changed Message) Replace the existing description of this message with the following: SQL1592NThe INCREMENTAL option is not valid with reason code "" since the table "" cannot be incrementally processed. Explanation: The cause is based on the "": 31 The table is not in check pending state if the INCREMENTAL option is specified in a REFRESH TABLE statement. 32 The table is not a REFRESH IMMEDIATE summary table. 33 A Load Replace or Load Insert has occurred to the table if it is a summary table. 34 A Load Replace has occurred to the table or the NOT LOGGED INITIALLY WITH EMPTY TABLE option has been activated after the last integrity check. 35 One of the following: o New constraint has been added to the table itself or its parents (or its underlying table if it is a summary table) while in check pending. o If it is a summary table, a Load Replace has occurred to any underlying table of the table after the last refresh. o If it is a summary table, some of its underlying table has been taken out of check pending before it gets refreshed. o If it is a summary table, some of its underlying tables were loaded. The table was then refreshed, followed by some more loads to the same underlying tables. o Some of its parents (or underlying table if it is a summary table) have been Load Replaced or checked for integrity non-incrementally. o The table was in check pending state before migration. Full processing is required for the first time the table is checked for integrity after migration. o The table space containing the table or its parent has been rolled forward to a point in time. 36 Incremental processing of a summary table is not currently supported. User Response: Do not specify the INCREMENTAL option. The system will check the entire table for constraint violations (or if it is a summary table, recompute the summary table definition query). sqlcode: -1592 sqlstate: 55019 ------------------------------------------------------------------------ 11.8 SQL1596N (New Message) SQL1596NWITH EMPTY TABLE cannot be specified for "" that is a REFRESH IMMEDIATE summary table or has a dependent REFRESH IMMEDIATE summary table. Explanation: The table "" is a REFRESH IMMEDIATE summary table or is referenced in the query for at least one REFRESH IMMEDIATE summary table. The WITH EMPTY TABLE clause cannot be specified when altering a table to ACTIVATE NOT LOGGED INITIALLY on a table with such a dependency. The statement cannot be processed. User Response: Remove the WITH EMPTY TABLE clause from the ALTER TABLE statement. sqlcode: -1596 sqlstate: 42928 ------------------------------------------------------------------------ 11.9 SQL1704N (New Reason Codes) Reason code 14 Explanation: Table has an invalid primary key or unique constraint. User Response: Table has an index that was erroneously used for a primary key or unique constraint. Drop the primary key or unique constraint that uses the index. This must be done in the release of the database manager in use prior to the current release. Resubmit the database migration command under the current release and then recreate the primary key or unique constraint. Reason code 15 Explanation: Table does not have a unique index on the REF IS column. User Response: Create a unique index on the REF IS column of the typed table using the release of the database manager in use prior to the current release. Resubmit the database migration command under the current release. Reason code 16 Explanation: Table is not logged but has a DATALINK column with file link control. User Response: Drop the table and then create the table without the not logged property. This must be done in the release of the database manager in use prior to the current release. Resubmit the database migration command under the current release. Reason Code 17 Explanation: Fail to allocate new page from the DMS system catalog table space. User Response: Restore database backup onto its previous database manager system. Add more containers to the table space. It is recommended to allocate 70% free space for database migration. Move back to the current release and migrate the database. ------------------------------------------------------------------------ 11.10 SQL2554N (New Reason Codes) Possible new reason codes: 5. Exception table has DATALINK columns defined as file link control. 6. Table is in DataLink Reconcile Not Possible state. 7. Exception table cannot be specified when reconciling typed table. 8. Setting/Resetting table to/from DataLink Reconcile Pending/ DataLink Reconcile Not Possible state failed. 9. The database manager configuration parameter DATALINKS is set to NO. Possible solutions: 5. Ensure that the exception table has DATALINK columns all defined as NO LINK CONTROL. 6. Look into the Administrative Guide to see how to take a table out of DataLink Reconcile Not Possible state. 7. Do not specify an exception table when reconciling typed table. 8. Examine the db2diag.log for problem determination information and contact an IBM Service Representative for assistance. 9. Ensure that the database manager configuration parameter DATALINKS is set to YES when using the RECONCILE utility. ------------------------------------------------------------------------ 11.11 SQL3604N This message also applies to reconcile exception tables. ------------------------------------------------------------------------ 11.12 SQL4304N (New Reason Codes) Two new reason codes have been added to message SQL4304N: 4 - Cannot load driver for "jdbc:default:connection". 5 - Cannot establish default context. Additional diagnostic information is written to the db2diag.log file in each case. ------------------------------------------------------------------------ 11.13 SQLSTATE: 01639 (New) New SQLSTATE text: The view or summary table may require the invoker to have necessary privileges on data source objects. ------------------------------------------------------------------------ 11.14 SQLSTATE: 42903 (Changed) Changed SQLSTATE text: A WHERE, VALUES, GROUP BY, HAVING, or SET clause includes an invalid reference, such as a column or OLAP function. ------------------------------------------------------------------------ 11.15 SQLSTATE: 42928 (New) New SQLSTATE text: WITH EMPTY TABLE cannot be specified for a REFRESH IMMEDIATE summary table or a table that has a dependent REFRESH IMMEDIATE summary table. ------------------------------------------------------------------------ DB2 Query Patroller Administration Guide Following is new information that is to be added to specified sections of the book: In Chapter 2, Administration, section: Parameters: IWM_LOCAL_SERVANTS By default, the start program (iwm) starts five iwm_local processes. If a different number of iwm_local processes is required, set the IWM_LOCAL_SERVANTS environment variable to that number before invoking the start program (iwm). In Chapter 4, Using the Command Line Interface, section: Submit Command (iwm_submit): When executing a Select statement against a lowercase table or column name using iwm_submit, the Select statement must be defined in an SQL statement file (sql_stmt_file) and submitted using the -F parameter. See Table 4. iwm_submit Parameter Descriptions for information on the -F parameter and SQL statement files. In Chapter 4, Using the Command Line Interface, section: Monitoring and Controlling Nodes: If the iwm_cmd is used to stop node manager processes, the Server or Agent component must be stopped and restarted to turn the node manager processes on again. In Chapter 5, Error Log Monitoring, section: Running the Log Monitor: If the IWM_LOGMON environment variable is already set, the iwm_logmon command can only be invoked using the start program (iwm). If the IWM_LOGMON environment variable is set, any invocation of the "iwm_logmon -c command will cause an error to occur and iwm_logmon will fail to run. In Chapter 9, Configuring the DB2 Query Patroller Client, section: Client Configuration Options: The DB2IWM.INI file contains an n-tier configuration option that allows queries to be posted to the DB2 Query Patroller server without requiring user intervention. When this option is set, the user may also specify a result destination for queries by setting the "LastResultDestination" variable. LastResultDestination = { None| } -"None" is the default value and directs the result set to a DB2 table. -"Result Destination Name" is the value set for the Destination Name field on the Result Set Administration page of the QueryAdministrator utility. There is an error in the documentation for the n-tier option. "DB2_IWM_SUBMITWAIT variable" should read "n-tier option". In Chapter 12, User Administration, section: Creating a User Profile: This release of DB2 Query Patroller will not support user groups. Because a group cannot be added to the User Profile table, a profile for each user of DB2 Query Patroller must be individually added to the User Profile table. This action is performed using the User Administration page of the QueryAdministrator utility. ------------------------------------------------------------------------ DB2 Query Patroller Installation Guide Following is new information that is to be added to the specified section of the book: In Chapter 2, Installing DB2 Query Patroller, section: Before You Begin: Before installing DB2 Query Patroller ensure that vmstat is installed on the server machine. During a manual installation, the DB2 table space that will be used for the the DB2 Query Patroller must be created in one nodegroup. ------------------------------------------------------------------------ Quick Beginnings ------------------------------------------------------------------------ 14.1 Multiple Network Adapters and the db2nchg Command Following is a pre-installation tip for inclusion in the "DB2 Enterprise - Extended Edition for Windows NT Quick Beginnings" book: If you are planning to use multiple network adapters, you must use the db2nchg command to specify the TCP/IP address for the netname field in the db2nodes.cfg file. For more information, refer to the Administration Guide. ------------------------------------------------------------------------ 14.2 Reapply the Service Pack if Any New Windows NT Components Have Been Installed Any Windows NT component that copies an older version of any ODBC file could result in the failure of DB2 to launch properly. For example, Service Packs 3 and 4 install the following versions of the following .dll files: ODBCINT.DLL version 3.0.2822 ODBC32.DLL version 3.0.2822 When the service pack finishes installing, it launches a pop-up window suggesting that the service pack should be reinstalled (from the original Windows NT installation CD) after any new Windows NT component is installed. Some DB2 components (such as DB2 Universal Database Web Administration) suggest the installation of a Web server such as Microsoft Peer Web Services. When this Windows NT component is installed, it installs an older version of the following file: ODBCINT.DLL version 2.50.3006 DB2 will not run if ODBCINT.DLL and ODBC32.DLL are not the same version. For example, if MS Peer Web Services is installed before the DB2 installation, an error will appear near the end of the DB2 installation, stating that the ODBC files are at different versions and need to be reinstalled. Use the service pack to install the matching DLL files. ------------------------------------------------------------------------ 14.3 Connecting to Windows NT DB2 UDB Server with IBM CS/NT Version 6 In order for DB2 UDB clients to be able to connect to a Windows NT DB2 UDB server with IBM CS/NT Version 6, the TP_SECURITY_BEHAVIOR field in the *.ACG file must be manually set to "IGNORE_IF_NOT_DEFINED". Security validation will be performed at the DB2 UDB level only (not at the SNA level). The default setting ("VERIFY_EVEN_IF_NOT_DEFINED") forces SNA validation, regardless of how the listening TP on the DB2 UDB Windows NT server is defined. ------------------------------------------------------------------------ 14.4 Syntax Error in the Migration Section The syntax diagram in the Migration section incorrectly shows the db2iupdt command as "db2updt". ------------------------------------------------------------------------ 14.5 Error in the Migrating Instances Section The following step should be removed from the section "Prepare the DB2 Instance for Migration": Stop the DB2 license daemon by entering the db2licm end command. ------------------------------------------------------------------------ 14.6 Windows NT and OS/2 Migration Information On Windows NT, you should run the db2ckmig executable before installing DB2 Version 6.1. The db2ckmig utility checks a database to ensure that it is ready to be migrated. It is recommended that you run db2ckmig against all databases in your system before continuing the installation. The db2ckmig.exe file is located in the \DB2\COMMON directory on the DB2 Version 6.1 CD. Command Syntax: >>-db2ckmig----+-database-+-- -l filename ----+------------+------> '--e-------' '--u userid--' >-----+--------------+------------------------------------------->< '--p password--' The "-l " option allows you to specify an output file. If any errors are found, you should check the Quick Beginnings book, Appendix C, for information about possible errors and how to correct them. Once db2ckmig has completed successfully, you should take a full offline backup of the database. Next, you can install DB2 Version 6.1. The installation process will automatically migrate the database instance and the administration instance. Once DB2 Version 6.1 has been installed, you can migrate each database: >>-MIGRATE----+-DATABASE-+--database-alias----------------------> '-DB-------' >-----+---------------------------------------+---------------->< '-USER--username--+------------------+--' '-USING--password--' ------------------------------------------------------------------------ 14.7 UNIX Platform Migration Information The UNIX installation process does not automatically migrate the database instance for you. You can install the DB2 Version 6.1 code on UNIX without migrating Version 5 or Version 2 instances. Once the DB2 Version 6.1 code is installed, you should log on to the system as the DB2 instance owner. Then: step 1. Log in as the instance owner. step 2. Enter the following command: DB2DIR/bin/db2ckmig -h -a 0 -l INSTHOME/migration.log where DB2DIR = /usr/lpp/db2_06_01 on AIX = /opt/IBMdb2/V6.1 on HP-UX, Solaris, or SGI IRIX INSTHOME is the home directory of the instance, and migration.log is the name of the output file. step 3. Check the log file. The log file displays the errors that occur when you run the db2ckmig command. If it shows any errors, check the Quick Beginnings book. step 4. Check that the migration log file is empty before continuing with the instance migration. step 5. Backup the database after making corrections. step 6. Run db2ckmig. Once you have verified that the databases can be migrated, you can migrate your DB2 instances. You should stop DB2, and ensure that there are no DB2 processes still running. For more information, refer to the Quick Beginnings book, Appendix C, in the section "Prepare DB2 Instance for Migration". Note:The steps to migrate the administration instance are the same as the steps to migrate the database instance. The only difference is that the administration instance does not have any databases, so once you have migrated the administration instance, you are done (there is no database migration). 1. Log in as user with root authority. 2. Run the db2imigr command as follows: DB2DIR/instance/db2imigr [-d] [-a AuthType] [-u fencedID] InstName where DB2DIR = /usr/lpp/db2_06_01 on AIX = /opt/IBMdb2/V6.1 on HP-UX, Solaris, or SGI IRIX and where: -d Sets the debug mode that you can use for problem determination. This parameter is optional. -a AuthType Specifies the authentication type for the instance. Valid authentication types are (SERVER), (CLIENT), and (DCS). If the -a parameter is not specified, the authentication type defaults to (SERVER), if a DB2 server is installed. Otherwise, the AuthType is set to (CLIENT). This parameter is optional. Notes: 1. The authentication type of the instance applies to all databases owned by the instance. 2. While authentication type (DCE) is an optional parameter, it is not valid to choose (DCE) for this command. -u fencedID Is the user under which the fenced user-defined functions (UDFs) and stored procedures will execute. This parameter is optional only when a DB2 Run-Time Client is installed. It is required for all other DB2 products. It is also required if you migrate a Version 2 instance. InstName Is the login name of the instance owner. 3. If there are any errors while verifying that all databases can be migrated, see Table 15 and take the suggested corrective actions. Then re-issue the db2imigr command. The final step is to migrate each of the databases in the instance (this step should be skipped for the administration instance). To migrate databases owned by an instance, perform the following steps: step 1. Log on with a user ID that has SYSADM authority. step 2. Ensure that the databases you want to migrate are cataloged. step 3. Migrate the database. The syntax for the database migration is: >>-MIGRATE----+-DATABASE-+--database-alias----------------------> '-DB-------' >-----+---------------------------------------+---------------->< '-USER--username--+------------------+--' '-USING--password--' ------------------------------------------------------------------------ 14.8 Migration Recommendation Relating to DMS File Table Spaces If you have a SYSCAT table space which uses a DMS file, it is recommended that the table space have approximately 70% free space for migration. Otherwise, the migration may fail with error SQL1704N (reason code 17). The db2ckmig utility will check the SYSCAT table space to ensure that there is enough free space available for successful migration. If this error occurs even after issuing the db2ckmig command, the only solution is to restore the database backup under the Version 5 (or Version 2) instance, add more containers, and then try migration again. You can check the size of the SYSCAT table space by issuing: db2 list tablespaces show detail ------------------------------------------------------------------------ 14.9 Administering Instances and Databases with the DB2 Administration Tools The FMID that is used by the Control Center when you are administering an OS/390 database is JDB661D. ------------------------------------------------------------------------ 14.10 Monitoring Partitioned Databases A partitioned database must contain more than one node for monitoring in Version 6. ------------------------------------------------------------------------ 14.11 Installing DB2 on NEC PC98 Machines Running Windows 95 When you install DB2 with documentation on an NEC PC98 running Windows 95, the installation stops at 96%. To complete the installation: 1. End the IMQCRINS.EXE process. To end this process, press to open the Task Manager. Select the IMQCRINS process and click on the End push button. 2. When the installation has completed, reboot the system. ------------------------------------------------------------------------ 14.12 Uninstalling DB2 on NEC PC98 Machines Running Windows 95 If you have installed DB2 with documentation on an NEC PC98 running Windows 95, the uninstall operation stops before it has completed. To complete the uninstall operation: 1. End the UNINSTNQ.EXE process. To end this process, press to open the Task Manager. Select the UNINSTNQ process, and click on the End push button. 2. Delete the imnnq_95 directory from the drive where DB2 was installed. ------------------------------------------------------------------------ 14.13 db2start on Windows NT Not Returning Correct Message When running the db2start command (or using the NET START command) to start the database manager on the Windows NT environment, the command will not return any warnings if any communication subsystem failed to start. The user should always examine the NT Event Log or the DB2DIAG.LOG for any errors that may have occurred during the running of db2start. This occurs because the database manager on a Windows NT environment is implemented as an NT service, and hence does not return an error if the service is started successfully. ------------------------------------------------------------------------ 14.14 Prerequisite for APPC on AIX The prerequisite for APPC on AIX is IBM Comm. Server 5.0.3.0 (not 5.0.2.4). ------------------------------------------------------------------------ 14.15 Memory Windows for HP-UX 11 Memory windows is for users on large HP 64-bit machines, who want to take advantage of greater than 1.75GB of shared memory for 32-bit applications. Memory windows makes available a separate 1GB of shared memory per process or groups of processes. This allows an instance to have its own 1GB of shared memory, plus the 0.75GB of global shared memory. If users want to take advantage of this, they can run multiple instances, each in its own window. Following are prerequisites and conditions for using memory windows: * Patches: Extension Software 12/98, and PHKL_17795. * The $DB2INSTANCE variable must be set for the instance. * There must be an entry in the /etc/services.window file for each DB2 instance that you want to run under memory windows. For example: db2instance1 50 db2instance2 60 Note: There can only be a single space between the name and the ID. * Any DB2 commands that you want to run on the server, and that require more than a single statement, must be run using a TCPIP loopback method. This is because the shell will terminate when memory windows finishes processing the first statement. DB2 Service knows how to accomplish this. * Any DB2 command that you want to run against an instance that is running in memory windows must be prefaced with db2win (located in sqllib/bin). For example: db2win db2start db2win db2stop * Any DB2 command that is run outside of memory windows (but when memory windows is running) should return a 1042. For example: db2win db2start <== OK db2 connect to db <==SQL1042 db2stop <==SQL1042 db2win db2stop <== OK ------------------------------------------------------------------------ 14.16 DB2 Driver Error with Lotus Domino Go Webserver If you are using Lotus Domino Go Webserver and get the following error: java.sql.SQLException: DB2 Driver error allocating environment handle, rc=-1 Restart the Web server and specify the db2instance, as follows: startsrc -e "DB2INSTANCE=yourInstanceName" -s httpd Ensure that CLASSPATH and LD_LIBRARY_PATH are set correctly in the httpd.conf file (using the directives JavaClassPath and JavaLibPath respectively). Ensure that they point to the DB2 UDB Version 6.1 files that are required. ------------------------------------------------------------------------ 14.17 CCA Cannot Detect PCOMM on Windows NT with IBM PCI Token-Ring Adapter If you see IBM PCI Token-Ring Adapter in the list of network adaptors on Windows NT (Start -> Settings -> Control Panel -> Network -> Adapters), make sure that you have the latest device driver for this adapter. If not, download a new driver from the following: http://www.networking.ibm.com/support Select IBM PCI Token-Ring Adapter Select Downloads Select -All- Operational Code Select NDIS Device Driver Diskette Select Diskette Image Follow the instructions on the Web to install the new driver. ------------------------------------------------------------------------ 14.18 Restarting a Failed Database Partition Server In the introduction to the DB2 Enterprise - Extended Edition book, in the multiple logical nodes section, the documentation incorrectly instructs you to use the "db2start nodenum" command, or the Start Node option in the Control Center, to restart a failed database partition server. You should enter the "db2start restartnode" command, or use the Restart option in the Control Center, to restart a failed node. ------------------------------------------------------------------------ 14.19 Workload Transfer in the Event of Hardware or Software Failure In the introduction to the DB2 Enterprise - Extended Edition for UNIX Quick Beginnings book, in the high availability section, the documentation notes that on AIX, High Availability Cluster Multi-Processing (HACMP) will transfer the workload from one processor to another, in the event of hardware failure. HACMP will also transfer the workload from one processor to another in the event of software failure. ------------------------------------------------------------------------ 14.20 Disk Requirements for Clients In the DB2 Enterprise - Extended Edition for UNIX Quick Beginnings book, in the client components section, the table that lists the disk requirements for each client incorrectly lists the Client Configuration Assistant (CCA) as a component for each client. For this release, the CCA is only available on OS/2 and Windows 32-bit operating systems. ------------------------------------------------------------------------ 14.21 Estimating Fixed Disk Requirements for Servers In the DB2 Enterprise - Extended Edition for UNIX Quick Beginnings book, in the estimating fixed disk requirements section, there is a table that lists all of the disk requirements for a server product. There is a tip attached to this table that notes the following: The online documentation in HTML format component will install the DB2 documentation in a compressed format. You may need extra disk space temporarily for decompression. Once you have finished the installation, remove the fileset to reclaim the disk space. You should remove the fileset after you have manually decompressed and untarred it. ------------------------------------------------------------------------ 14.22 DB2CKMIG Reporting Potential Views that May or May Not Work After Database Migration In Version 6, some unused columns have been removed, and new columns have been added to the catalog tables and catalog views. There is also a large number of catalog column types that have been changed from one type to another, for example from CHAR(8) to VARCHAR(128). As a result, user defined views based on catalog tables and views must be regenerated so that the column types can be changed accordingly. This view regeneration is done as part of database migration. Note that some types of user defined views will not be regenerated, and may be marked inoperative during this process. The db2ckmig utility will attempt to list all such affected views. Following is a brief description of the type of views whose regeneration may fail: * A view that is based on a summary table, which in turn is based on catalog tables or views. This type of view, and all of its hierarchy views, will not be regenerated, and will be marked inoperative. * A view that is based on catalog tables or catalog views. This type of view, and all its hierarchy views, will not be regenerated, and will be marked inoperative. * A view that is based on the following catalog tables when the view text is directly referencing any dropped column of the catalog in Version 6, or using "SELECT *" to reference the catalog. SYSIBM.SYSCOLUMNS SYSIBM.SYSPLANDEP SYSIBM.SYSSECTION SYSIBM.SYSSTMT SYSIBM.SYSTABAUTH SYSIBM.SYSTABLES SYSIBM.SYSTABLESPACES SYSIBM.SYSVIEWS SYSIBM.SYSDATATYPES SYSIBM.SYSINDEXES * A view that is based on the following catalog views when the view text is directly referencing any dropped column of the catalog view in Version 6, or using "SELECT *" to reference the catalog view. SYSCAT.CHECKS SYSCAT.COLUMNS SYSCAT.COLCHEKCS SYSCAT.COLDIST SYSCAT.DBAUTH SYSCAT.DATATYPES SYSCAT.EVENTMONITORS SYSCAT.FUNCPARMS SYSCAT.FUNCTIONS SYSCAT.INDEXES SYSCAT.PACKAGEDEP SYSCAT.PACKAGES SYSCAT.TABLES SYSCAT.TABLESPACES SYSCAT.TRIGGERS SYSCAT.VIEWDEP SYSCAT.VIEWS SYSCAT.PROCEDURE SYSCAT.PROCPARMS SYSCAT.COLPROPERTIES SYSSTAT.INDEXES * User defined views that are using one of the following special registers or built-in functions may not work after database migration, because the object name has been increased from VARCHAR(18) TO VARCHAR(128), or from CHAR(8) TO VARCHAR(128) in Version 6. As a result, the query maximum row size may exceed the limit of 4001 (for a Version 2 migrated database or a Version 5 migrated database with 4KB pages) or 8101 (for a Version 5 migrated database with 8KB pages). Special Register: CURRENT EXPLAIN MODE CURRENT SERVER USER Built-in Function: TABLE_NAME TABLE_SCHEMA TYPE_SCHEMA The db2ckmig utility will report the following warning for each view exhibiting one of the properties described above: This view references a system entity that has changed; this may affect migration. It is recommended that you check each of your views and determine whether they have any of these properties. You should drop such views and their dependent views, because they will not work. The name of any view that cannot be regenerated is logged in the db2diag.log file during database migration. ------------------------------------------------------------------------ 14.23 Changes to SNA Configuration During Migration from DB2 Version 5.x to DB2 Version 6.1 When Using IBM eNetwork Communications Server for AIX (CS/AIX) In DB2 Version 5.2 or earlier, a transaction program (TP) had to be defined in the SNA stack (CS/AIX), and the DB2 database manager configuration, for an inbound conversation. When you move to DB2 Version 6.1, you must upgrade CS/AIX to Version 5.0.3.0 . Because a TP cannot be defined in this version of CS/AIX, you can remove the TP definition from CS/AIX when migrating from Version 5.x; alternatively, DB2 will delete the TP definition from the CS/AIX configuration file on the first invocation of db2start, using the TP definition defined in the database manager configuration file. ------------------------------------------------------------------------ 14.24 Single-User Install of DB2 for Windows NT In previous versions of DB2 for Windows NT, the user installing DB2 had to be an administrator on the Windows NT machine. This requirement no longer applies to Windows NT Version 6 releases of DB2 clients, the DB2 Software Developer's Kit, DB2 Connect Personal Edition, and DB2 Satellite Edition. Now, even a user who does not have administrative authority for the local machine, can install and use DB2. This change applies to both the administrative and the run-time client. Some information about DB2 that must appear in the registry must now be entered in the HKEY_CURRENT_USER folder in the registry. Although many items will be stored under the HKEY_LOCAL_MACHINE folder in the registry for single user installed DB2 products, the environment settings must be changed in HKEY_CURRENT_USER. System shortcuts must be changed to user shortcuts for the single user install. Moreover, since services are required to install any of the single user DB2 products, but cannot be created without administrative authority, services that would be automatically started are run as processes when a non-administrator installs. Following are known limitations: * Installation of NetQuestion and Personal Communications still requires administrative authority. Therefore, any single user installed DB2 product will not have the ability to search through the documentation, and will not be able to install and use integrated SNA support with DB2 Personal Connect, unless the system already has an integrated SNA stack. * Control Server Synchronization will not be a selectable component during DB2 Satellite Edition installation, if the user doing the install is not an administrator. An entry will be put into the registry for each non-administrator that has DB2 installed. These entries can be found in the registry, under HKEY_LOCAL_MACHINE\Software\IBM\DB2\Users. Following are some specific install scenarios: * A user has installed a DB2 product using the single user install method, and then an administrator installs any DB2 product on the same machine. In this scenario, the administrator install will remove any single-user installs that exist on the system, resulting in a clean install of the administrator product. The administrator install will overwrite all of the user's services, shortcuts, and environment variables from the previous installation of DB2. * A user has installed a DB2 product using the single user install method, and then a second user installs any DB2 product on the same machine. In this scenario, the second user install will fail, and return an error message that the user must be an administrator to install the product. * An administrator has installed a DB2 product, and then a user attempts to install any DB2 single-user product on the same machine. In this scenario, the single user install will fail, and return an error message that the user must be an administrator to install the product. ------------------------------------------------------------------------ 14.25 Planning for Installation 14.25.1 NetFinity Support Program Service Must Be Stopped Before Installing DB2 UDB EEE - Windows NT The"Netfinity Support Program" service will affect the installation of DB2 UDB EEE for Windows NT when upgrading DB2 UDB. In order to upgrade DB2 UDB EE to DB2 UDB EEE, the "Netfinity Support Program" service should be stopped before the installation of DB2 UDB EEE is begun. 14.25.2 Recommended Fix Level for ADSM on AIX In an AIX partitioned database environment, after an online table space backup has been taken using ADSM, DB2 may fail if the database is restored. The symptom is that the online restore fails, and the DB2 instance is brought down (traps) with the diagnostic message SIG11sqloEDUCodeTrap. The solution to this problem is to install the ADSM Client fix pack 3.1.0.x or higher. 14.25.3 Recommended Windows NT Level For logging to raw devices with DB2 on Windows NT, Microsoft Service Pack 3 for Windows NT 4.0 is required. A problem where Windows NT committed memory would rise to equal real memory has been fixed in Microsoft Service Pack 4. ------------------------------------------------------------------------ 14.26 Other Installation and Configuration Tasks The updates in this section relate to installation and configuration tasks. 14.26.1 Problem Affecting NIS Users on AIX Versions 4.2 or Later Note:This section is updated from the Version 5.2 Release Notes with the inclusion of the AIX Version 4.1.4 restriction. The AIX Version 4.1.5 restriction was previously documented. On AIX Versions 4.2 or later, when a user is running Network Information System (NIS) for password authentication, the message DB21015E may be received when the command line processor times out. The command line processor back-end program cannot recognize the user ID of the DB2 instance from the NIS master server in this case. To correct this problem: 1. Remove the user ID from the NIS master server. 2. Create the user ID in the /etc/passwd file locally on each machine, ensuring that the user ID is placed before the NIS magic cookie in the file. The magic cookie looks like this: :!:::::: ------------------------------------------------------------------------ 14.27 Setting up the CCA and the Control Center in a Partitioned Database Environment (DB2 Universal Database Enterprise - Extended Edition for AIX) For performance reasons, it is not recommended that you set discover=SEARCH on the administration server that resides on a key catalog node or Control Center node. ------------------------------------------------------------------------ 14.28 Considerations for Remotely Administering Clusters Using the Control Center After you install DB2 Universal Database Enterprise - Extended Edition for Windows NT on multiple nodes, the setup process will only configure the instance-owning machine to be the co-ordinator node for communications from client machines. To remotely administer nodes using the Control Center, you will need to enable communications on each of the nodes. You need to update the services file and specify the port that you want the server to listen on for incoming client requests from the Control Center. The services file is located in the \winnt\system32\drivers\etc directory. Note:The location of the services file can depend on the products you have installed on your system. See your TCP/IP documentation for more information. To enable communications on one of the nodes, reserve a TCP/IP port on each of the machines. Using a local text editor, add the Port entry to the services file for TCP/IP support. For example: db2cntlc 4000/tcp # Reserved for Control Center - Coordinator function where: db2cntlc is the Service name that has been set by the instance owner, 4000 is the port number, and tcp is the communication protocol that you are using. ------------------------------------------------------------------------ 14.29 DB2 SNMP Subagent Simple Network Management Protocol (SNMP) products include IBM NetView for AIX and IBM NetView for OS/2. DB2 provides support for SNMP management products via the DB2 SNMP Subagent, included as a component of the DB2 Server. SNMP management products such as NetView allow centralized management of both hardware and software components of a system. The DB2 SNMP Subagent allows DB2 servers to be managed just like any other SNMP-managed resource. The software requirements for using the SNMP Subagent are as follows: * For OS/2: o DPI 1.1 support provided by TCP/IP Version 2.0 or later, or o DPI 2.0 support provided by Warp Connect installed with OS/2 Warp, or o DPI 2.0 support provided by IBM SystemView Agent. * For Windows NT or AIX: o DPI 2.0 support provided by IBM SystemView Agent. ------------------------------------------------------------------------ 14.30 ADSM Client Setup for EEE on Solaris - Use db2profile When setting up ADSM Client for DB2 UDB Enterprise - Extended Edition for Solaris, the DSMI_DIR, DSMI_CONFIG, and DSMI_LOG ADSM environment variables should be set up in db2profile. If these variables are merely exported using .profile or .kshrc, they will not be available to each node in the partitioned database environment. The ADSM client on Solaris must be at Version 3.1 or higher. ------------------------------------------------------------------------ 14.31 Configuring NetBIOS on Windows NT Servers NetBIOS on Windows NT does not properly receive data that is larger than the receive buffer. However, you can use the DB2 registry value db2nbrecvbuffsize to overcome this problem. You need to set the receive buffer on the server to be larger than the largest amount of data that will be sent by a client. The default RQRIOBLK size is 32767 bytes, but the default server "receive buffer size" is 4096 bytes. Before you start DB2, issue the following command: db2set db2nbrecvbuffsize=32768 -g This command sets the server's receive buffer size globally. ------------------------------------------------------------------------ 14.32 IBM DB2 Stored Procedure Builder Welcome to the IBM DB2 Stored Procedure Builder. This section contains the latest information about Stored Procedure Builder configuration, known problems, usability, and the remote debugger. 14.32.1 Configuring DB2 UDB for Stored Procedure Builder Before you run Stored Procedure Builder, you must configure DB2 UDB in the following ways: * Set the path for the Java Development Kit (JDK) by entering the following command from the DB2 Command Window: DB2 UPDATE DATABASE MANAGER CONFIGURATION USING JDK11_PATH x:\sqllib\java\jdk where x: is the drive on which you installed DB2 UDB * We recommend that you set the Java heap size to 4096 bytes. From the DB2 Command Window, enter the following command: DB2 UPDATE DATABASE MANAGER CONFIGURATION USING JAVA_HEAP_SZ 4096 * We recommend that you set the application heap size to 1024 bytes. From the DB2 Command Window, enter the following command: DB2 UPDATE DATABASE MANAGER CONFIGURATION FOR database_name USING APPLEHEAPSZ 1024 * Set the DB2 parameter KEEPDARI to NO if you are frequently rebuilding and testing stored procedures. In the DB2 Command Window, enter the following command: DB2 UPDATE DATABASE MANAGER CONFIGURATION USING KEEPDARI NO * To enable double-byte character set (DBCS) support for DB2 UDB, add the following lines to the DB2CLI.INI file located on the computer where you installed Stored Procedure Builder: PATCH1=65600 PATCH2=7 For more information about DBCS support, see the IBM DB2 UDB Java DBCS Support Web page at http://www.software.ibm.com/data/db2/java/dbcsjava.html. * For these configuration settings described above, you must stop and restart the database server before the new settings will take effect. From the DB2 Command Window, type: db2stop db2start 14.32.2 Obtaining authorization to create stored procedures To create stored procedures with Stored Procedure Builder, you must have the following authorizations: * CONNECT, which allows you to access the target databases on which you want to build stored procedures * CREATE PROCEDURE, which allows you to register stored procedures with a database server. This authorization requires one of the following privileges: o SYSADM or DBADM o CREATEIN for the schema if the schema name of the stored procedure refers to an existing schema CREATEIN allows you to create objects within the schema. o IMPLICIT_SCHEMA authority on the database if the implicit or explicit schema name of the stored procedure does not exist IMPLICIT_SCHEMA allows you to implicitly create a schema when creating an object with a CREATE statement and specifying a schema name that does not already exist. SYSIBM becomes the owner of the implicitly created schema and PUBLIC is given the privilege to create objects in this schema. * Table privileges including the following privileges: o SELECT, which allows you to retrieve rows from a table or view, to create a view on a table, and to run the EXPORT utility o INSERT, which allows you to insert an entry into a table or view, and to run the IMPORT utility o UPDATE, which allows you to change an entry in a table, a view, or one or more specific columns in a table or view o DELETE, which allows you to delete rows from a table or view To run a stored procedure so that you can test it, you must have one of the following privileges at run time: * SYSADM or DBADM * EXECUTE or CONTROL for the package associated with the stored procedure To use the IBM Distributed Debugger to debug stored procedures built on a DB2 workstation, you need table privileges for the debug table (DB2DBG.ROUTINE_DEBUG) and the source table. Contact your DB2 database administrator (DBA) for help in obtaining the required authorizations and privileges. For more information about database authorizations, see IBM DB2 Administration Guide. 14.32.3 Known problems with Stored Procedure Builder Stored Procedure Builder generates Java class and method names for stored procedure source code based on the stored procedure name you provide. This dependency might cause invalid Java class and method names to be generated. If this occurs, use the editor in Stored Procedure Builder to modify the class and method names in the source code. Rebuild the stored procedure by right-clicking on the procedure in the tree view and selecting Build. Due to a current JDK 1.1.7 restriction, stored procedures with class names, method names, or jar IDs containing non-Latin characters cannot be built successfully. For details about this problem, see bug number 4092784 at the Java Developer Connection web site (http://developer.java.sun.com). When switching modes in the Japanese language version of Stored Procedure Builder (for example, when switching from English character input to Japanese character input), you must type text in a text buffer separate from the Stored Procedure Builder interface. After you press the Enter key, the text appears in the corresponding field in the Stored Procedure Builder interface. This is currently a Swing 1.0.3 restriction. On double-byte character set (DBCS) systems, a backslash (\) is displayed as a path separator in Stored Procedure Builder text fields, instead of the Yen or Won symbols. This problem does not affect the behavior of Stored Procedure Builder. For details about this problem, see bug number 4238902 at the Java Developer Connection web site (http://developer.java.sun.com). In the Korean language version of Stored Procedure Builder, Korean characters and English characters appear misaligned in the interface. This problem does not affect the behavior of Stored Procedure Builder. In the Chinese language version of Stored Procedure Builder, English text at the end of wrapped lines in multi-line labels and pop-up information might be truncated. This problem does not affect the behavior of Stored Procedure Builder. For details about this problem, see bug number 4238902 at the Java Developer Connection Web site (http://developer.java.sun.com). You cannot use the Stored Procedure Properties notebook to change the number of result sets that a stored procedure returns. To change the number of result sets, specify a number in the source code using the editor. You cannot specify a new project path using the Project Properties notebook. To change the project path, save the project in a new location using File -> Save As in the Project window. 14.32.4 Unsupported items Stored Procedure Builder does not currently support programming languages other than Java. Stored Procedure Builder does not currently support connections to DB2 on AS/400 or OS/390. 14.32.5 Late usability information 14.32.5.1 Running Stored Procedure Builder In order to properly run Stored Procedure Builder, you must set the DB2 path for the Java Development Kit (JDK). See "Configuring DB2 UDB for Stored Procedure Builder" in these release notes for more information. If you do not set this parameter, you will receive the following error message when building or running a stored procedure: [IBM][CLI Driver][DB2/NT] SQL4301N Java interpreter startup or communication failed, reason code "1". SQLSTATE=58004 You must set the DB2 parameter KEEPDARI to NO if you are frequently rebuilding and testing stored procedures. See "Configuring DB2 UDB for Stored Procedure Builder" in these release notes for more information. If you do not set this parameter, you might experience problems building stored procedures because of a locked jar file. 14.32.5.2 SQL Assistant In Stored Procedure Builder, you can click the Define SQL button in the Java Stored Procedure SmartGuide to launch the SQL Assistant SmartGuide. SQL Assistant automatically creates SQL statements using the conditions you provide. To specify host variables in SQL statements created with SQL Assistant, you must prefix variable names with a colon (:) before typing any additional characters. If you add a colon to the beginning of the name after you have already typed the name, SQL Assistant interprets the name as a literal string. To check whether what you have typed is interpreted as a host variable or as a string, click on the SQL tab in SQL Assistant to view the SQL statement. Literal strings are surrounded by single quotes ('). To enter a host variable, click in one of the Value fields on the Condition page of SQL Assistant to enable the Variable button. Click the Variable button to open the Create a New Variable window. Type a leading colon and the name of your host variable and click OK. You can enter host variables identified with a leading colon in the following fields in SQL Assistant: * The Value fields in the Insert step of an INSERT statement * The Value fields in the Update step of an UPDATE statement * The Values fields in the Condition step of SELECT, SELECT UNIQUE, or DELETE statements 14.32.5.3 Searching in the Project window You can search for specific text in the Details View of the Stored Procedure Builder Project window using the Search toolbar button. To search for text in the Details View: 1. Click on the Stored Procedures folder in the tree view to select it. 2. Click the Search toolbar button. The Search in Details View window appears. 3. In the Search string field, type the text you want to search for in the Details View. 4. Optional: Select the Case sensitive search checkbox to differentiate between uppercase, lowercase, and mixed case text strings. 5. Click the Search button. Matching text is highlighted in the Details View. Click the Search button again to find the next occurrence of the specified text string. The Search in Details View window will notify you if no matches are found. 14.32.5.4 Filtering stored procedures Single quotes (') and double quotes (") typed in the Values field of the Filter Stored Procedures window and page are treated as regular characters and not as wildcards or characters denoting literal strings. For example, if you type "procedure1" with the surrounding double quotes in the Values field of the Name comparison, Stored Procedure Builder will include the double quotes as part of the stored procedure name. 14.32.5.5 Creating stored procedures that contain static SQL (SQLJ) SQLJ only supports SQL syntax that follows the SQL92 or earlier standards. This restriction applies to all static SQL statements in your stored procedures. Stored procedures which contain static (SQLJ) statements must be run in FENCED mode in this release of the DB2 Universal Database. 14.32.5.6 Returning result sets Result sets can only be returned from stored procedures running in FENCED mode. This restriction applies to stored procedures that contain either static or dynamic SQL. To write a Java method that returns a result set, declare the method with an additional parameter that is a single-element array of the Java ResultSet class. 14.32.5.7 Building stored procedures that contain static SQL (SQLJ) You must connect to a database using the IBM DB2 alias driver if you want to build stored procedures that include static SQL. 14.32.5.8 Stopping a build or run process You cannot stop the build or run process for a stored procedure as described in the online help for Stored Procedure Builder. The "gears" push button is unavailable in the Progress window. 14.32.5.9 Printing from Stored Procedure Builder The location of the cursor or the selected item in the Project window determines what will be printed when you select File -> Print. To print the source code of a stored procedure: 1. Right-click a stored procedure in the tree view and select Get Source to view the source code in the editor pane. 2. Right-click in the editor pane and select File -> Print. Stored Procedure Builder prints the source code currently shown in the editor pane. To print a summary sheet containing details about the project and individual stored procedures: 1. Select the project folder in the tree view. 2. Select File -> Print from the Project window. Stored Procedure Builder prints a summary sheet containing information about the project and details about each stored procedure. Note: Stored Procedure Builder will also print the source code for each stored procedure as part of the summary if you have previously selected Get Source for that stored procedure in the tree view. 14.32.5.10 DB2SPB.INI file additions You can control the preservation of the temporary build directory after a build failure by editing the DB2SPB.INI file. The following line in the DB2SPB.INI file specifies whether the temporary build directory is preserved or discarded (the default is FALSE): BUILD_KEEP_TMPDIR_AFTER_FAILURE = FALSE You can specify whether or not error messages appear in the pop-up error console window by editing the DB2SPB.INI file. (Error messages are always written to the DB2SPB.LOG file.) The default for the following line is FALSE, specifying that the pop-up error console window will not appear: ENABLE_STDERR_CONSOLE = FALSE 14.32.6 Remote debugging with Stored Procedure Builder Remote debugging in Stored Procedure Builder is supported with the IBM Distributed Debugger which comes with VisualAge for Java v3.0. The Distributed Debugger consists of the debug engine and the debugger user interface. The debug engine runs on the machine executing the code you are debugging (often the server machine) and controls the code being debugged. The debugger user interface can run on any machine (often the client workstation) and provides a consistent user interface for debugging. Note: The IBM Distributed Debugger is referenced in the online help for Stored Procedure Builder as the "VisualAge Remote Debugger." Note: The debug table (DB2DBG.ROUTINE_DEBUG) is incorrectly referenced in the online help for Stored Procedure Builder as DB2DBG.SYS_UDFSP_DEBUG. 14.32.6.1 Setting up DB2 UDB to debug stored procedures You must configure DB2 UDB so that you can debug stored procedures that you create with Stored Procedure Builder. * To set up DB2 UDB for debugging stored procedures, enter the following command from the DB2 Command Window: DB2SET DB2ROUTINE_DEBUG=ON * If the debug table was not created when you installed Stored Procedure Builder, run the DB2DEBUG.DDL from the directory x:\sqllib\misc\db2debug.ddl, where x: is the drive on which you installed DB2 UDB. Requirement: When you debug stored procedures by using Stored Procedure Builder and the IBM Distributed Debugger, the debugger daemon must be running on your client workstation. 14.32.6.2 Debugger availability Remote debugging of stored procedures with DB2 Universal Database v6.1 and Stored Procedure Builder is currently supported on the following servers: AIX version 4.2.1 AIX version 4.3.3 Windows NT 4.0 with service pack 4 Remote debugging of stored procedures is supported on the following clients: Windows NT 4.0 with service pack 4 The IBM Distributed Debugger supports a number of additional client and server platforms. At this time, support for debugging DB2 stored procedures is limited to the platforms listed above. 14.32.6.3 Debugger requirements Refer to the IBM VisualAge for Java 3.0 documentation for system requirements for the IBM Distributed Debugger. 14.32.7 Running Stored Procedure Builder as an add-in from another development application You can launch Stored Procedure Builder as a separate application from the IBM DB2 UDB program group or from within any of the following development applications: * Microsoft Visual Basic Version 5 and Version 6 * Microsoft Visual Studio Version 5 and Version 6 14.32.7.1 Setting Stored Procedure Builder to run as an add-in in Microsoft Visual Basic 1. If Microsoft Visual Basic was not installed when you installed DB2 UDB, you must perform the following steps to register the add-in with Visual Basic: a. Open a DOS command prompt and change to the directory x:\sqllib\bin, where x: is the drive on which you have installed DB2 UDB. b. Enter the following command: db2spbvb -addtoini 2. Start Visual Basic. 3. Select Add-Ins --> Add-In Manager. The Add-In Manager window opens. 4. Select IBM DB2 Stored Procedure Builder. 5. Click OK. Stored Procedure Builder is added to the Add-Ins menu. 14.32.7.2 Setting Stored Procedure Builder to run as an add-in in Microsoft Visual C++ 1. If Microsoft Visual Studio was not installed when you installed DB2 UDB, you must perform the one of following steps to register the add-in with Visual Studio: o If you have Visual Studio 5, copy the file DB2SSPB.DLL from the directory x:\sqllib\bin to y:\Program Files\DevStudio\SharedIDE\AddIn, where x: is the drive on which you have installed DB2 UDB and y: is the drive on which you have installed Visual Studio 5. o If you have Visual Studio 6, copy the file DB2SPBVS.DLL from the directory x:\sqllib\bin to y:\Program Files\Microsoft Visual Studio\Common\MSDev98\AddIns, where x: is the drive on which you have installed DB2 UDB and y: is the drive on which you have installed Visual Studio 6. 2. Start Microsoft Visual C++. 3. Select Tools --> Customize. The Customize window opens. 4. In the Customize window, make sure that IBM DB2 Stored Procedure Builder is selected. 5. Click Close. The Stored Procedure Builder icon is added to the icon toolbar. ------------------------------------------------------------------------ DB2 Data Links Manager Quick Beginnings ------------------------------------------------------------------------ 15.1 HTML Version Not Available The DB2 Data Links Manager for AIX Quick Beginnings book and the DB2 Data Links Manager for Windows NT Quick Beginnings book are not available in HTML format. ------------------------------------------------------------------------ 15.2 Windows NT Prerequisite Windows NT 4.0 FixPak 4 is a prerequisite for Data Links on Windows NT. ------------------------------------------------------------------------ 15.3 Case Sensitivity of Instance Name When Connecting to DLFM If you are registering a Windows NT instance on an AIX Data Links Manager, the instance name must be in uppercase. ------------------------------------------------------------------------ 15.4 Using NFS to Access a Remote DLFS File System An SQL SELECT on a DATALINK column which was defined with the "READ PERMISSION DATABASE" option will return the URL or file name with an access token embedded within the name. This access token has an expiration time, as defined by the DL_EXPINT database configuration parameter. When using an NFS client to access such files, you may find that the access token does not expire appropriately. This can be due to caching done by the NFS client. It has been observed that the use of long file names (greater than 12 characters) may mitigate this problem. Whenever you remotely mount a DLFS file system via NFS, you may also want to include the mount option "noac". For example: mount -o noac birdcage:/dlink /dlink ------------------------------------------------------------------------ 15.5 DLFM Migration and Environment Variables The Version 5.2 manual documents a specific set of environment variables that must be set for the Data Links Manager to function properly. These are: DLFM_INSTALL_PATH DLFM_PORT DLFM_LOG_LEVEL DB2_RR_TO_RS DLFM_BACKUP_DIR_NAME Version 5.2 to Version 6 migration converts these environment variables to DB2 registry variables. Registry variables offer more function and flexibility than environment variables. However, because of this, it is possible that migration will not be performed exactly as expected. The registry variables should be checked (using the db2set command) after migration. ------------------------------------------------------------------------ 15.6 DB2 Data Links Manager System Setup and Backup Recommendations The following system setup and backup procedures are recommended for easier recovery: o Place the DLFM database (DLFM_DB), any file systems under control of the DB2 Data Links Filesystem Filter (DLFF), the DLFM backup directory, and the DLFM home directory on different file systems. Ensure they do not share disks. o Back up any file systems under the control of the DLFF and the DLFM on a storage manager, such as IBM's ADSM. In the event of disk failure, this will provide added protection. o Have one database associated with one or more DB2 Data Links Managers. Avoid having two databases associated with one DB2 Data Links Manager; otherwise, certain recovery scenarios will become more complex than is necessary. o Perform full database backups of the DB2 databases (as well as the DLFM_DB database on the DB2 Data Links Manager) at regular intervals. ------------------------------------------------------------------------ 15.7 DB2 Data Links Manager Recovery Scenarios This section lists some of the possible DB2 Data Links Manager failure scenarios and the steps required to recover from them. For a description of recommended procedures for easier recovery, see 15.6, DB2 Data Links Manager System Setup and Backup Recommendations. DEFINITIONS DLFS file system - Registered prefix (example: /dlink) DLFM backup directory - Directory where files are backed up (example: /home/dlfm/dlfm_backup) DLFM home directory - Home directory of DLFM user ID (example: /home/dlfm) DLFM DB2 database - DB2 database that contains all meta-data (DLFM_DB) DB2 database - Registered database that contains DATALINK data type (example: CROWN) COMPONENTS THAT MAY REQUIRE RECOVERY o The DB2 database containing a table which has a DATALINK column o The DB2 Data Links Manager's database (DLFM_DB) o Data Links Filesystem Filter (DLFF) o File systems under the control of the DB2 DLFF which are registered to the Data Links Manager o The DB2 Data Links Manager's backup directory RECOVERY SCENARIOS The following recovery scenarios may require operations to be performed on both the DB2 node and the DB2 File Manager node. The DB2 database will be referred to as "CROWN" throughout all examples, and the DB2 table containing the DATALINK column will be referred to as "DATALINKTABLE". Scenario #1A: DB2 Database is lost or was accidentally dropped, but DB2 backup and log files are available. Note: In this scenario, the DB2 database has not been dropped from the DLFM server. Recovery Steps: Step 1. On the DB2 server, enter the following commands: db2 "restore database CROWN" db2 "rollforward database CROWN to end of logs" db2 "reconcile table DATALINKTABLE" Scenario #1B: DB2 database explicitly dropped, but DB2 backup and log files are available. Recovery Steps: Step 1. Ensure that the drop database operation is complete, and that all files associated with that database have been unlinked. Step 2. On the Data Links server enter the following commands: db2 "connect to dlfm_db" db2 "select dbid, dbname, dbinst, hostname from dfm_dbid" db2 "update dfm_dbid set action=5 where dbid=x'35B3D7BE0006BF7B'" (Set dbid equal to value found in previous step.) Step 3. On the DB2 server enter the following commands: db2 "restore database CROWN" db2 "rollforward database CROWN to end of logs" db2 "reconcile table DATALINKTABLE" Scenario #2: The DLFM_DB database is lost, but the backup and all log files for the DLFM_DB database are available. Recovery Steps: Step 1. On the Data Links server enter the following commands: db2 "restore database dlfm_db" db2 "rollforward database dlfm_db to end of logs" Step 2. On the DB2 server enter the following command: db2 "connect to CROWN" db2 "reconcile table DATALINKTABLE" Scenario #3: The DLFM_DB database is lost, a backup of the DFLM_DB database is available, but the not all of the log files are available. Recovery Steps: Step 1. On the Data Links server enter the following commands: db2 "restore database dlfm_db" db2 "rollforward database dlfm_db to end of logs" db2 "connect to CROWN" Step 2. On the DB2 server enter the following command: db2 "connect to CROWN" db2 "reconcile table DATALINKTABLE" Scenario #4: The DLFS file system is lost. Recovery Steps: Step 1. Restore the DLFS file system from your storage manager. Step 2. On the DB2 Server enter the following command: db2_recon_aid CROWN Scenario #5: The DLFM backup directory is lost. Recovery Steps: Restore the DLFM backup directory from your storage manager. Scenario #6: The DLFS file system and the DLFM backup directory are lost. Recovery Steps: Step 1. Restore the DLFM backup directory from your storage manager. Step 2. Restore the DLFS file system from your storage manager. Step 3. On the DB2 Server enter the following command: db2_recon_aid CROWN Scenario #7: The DLFM_DB database, the DLFM backup directory, and the DLFS file system are lost, but the backup, and all log files for the DLFM_DB database are available. Recovery Steps: Step 1. On the Data Links server enter the following commands: db2 "restore database dlfm_db" db2 "rollforward database dlfm_db to end of logs" Step 2. Restore the DLFM backup directory from your storage manager. Step 3. Restore the DLFS file system from your storage manager. Step 4. On the DB2 Server enter the following command: db2_recon_aid CROWN Scenario #8: The DLFM_DB, the DLFS file system, and the DLFM backup directory are lost. The backup of the DLFM_DB database is available, but not all log files are available. Recovery Steps: Step 1. On the Data Links server enter the following commands: db2 "restore database dlfm_db" db2 "rollforward database dlfm_db to end of logs" Step 2. Restore the DLFM backup directory from your storage manager. Step 3. Restore the DLFS file system from your storage manager. Step 4. On the DB2 Server enter the following command: db2_recon_aid CROWN Scenario #9: The DB2 database, the DLFM_DB database, the DLFS file system and DLFM backup directory are lost, but backup, and all log files for the DLFM_DB database are available. Recovery Steps: Step 1. On the Data Links server enter the following commands: db2 "restore database dlfm_db" db2 "rollforward database dlfm_db to end of logs" Step 2. Restore the DLFM backup directory from your storage manager. Step 3. Restore the DLFS file system from your storage manager. Step 4. On the DB2 server enter the following commands: db2 "restore database CROWN" db2 "rollforward database CROWN to end of logs" db2 "connect to CROWN" db2 "reconcile table DATALINKTABLE" NOTE: Rollforward to a point-in-time (PIT) may not put tables that have all DATALINK columns defined as "recovery = no" into DataLink_Reconcile_Pending (DRP) state. For all such tables, run the reconcile utility. ------------------------------------------------------------------------ 15.8 Recovery with DLFM In the recovery scenarios, when a DB2 database is dropped or lost, the recovery steps on DB2 should be: 1. Restore the database. 2. Rollforward to end of logs. This will put all tables with DATALINK columns into DRNP state; they must be reconciled. 3. For each table in DRNP state: db2 set constraints for to datalink reconcile pending db2 set constraints for
to datalink reconcile pending immediate unchecked db2 reconcile
------------------------------------------------------------------------ 15.9 Implications of Changing the DLFM_BACKUP_DIR_NAME Registry Value If you change the setting of the DLFM_BACKUP_DIR_NAME registry variable between ADSM and disk at run time, you should be aware that the archived files are not moved to the newly specified archive location. For example, if you start the Data Links File Manager with the DLMF_BACKUP_DIR_NAME registry value set to ADSM, and change the setting of the DLFM_BACKUP_DIR_NAME registry value to a disk location, all newly archived files will be stored in the new location on the disk. The files that were previously archived to ADSM will not be moved to the new disk location. ------------------------------------------------------------------------ 15.10 Recovering from a Crash on the Data Links Server In the "DB2 Data Links Manager for Windows NT Quick Beginnings" book, the following should be noted in Chapter 5 "Recovering from a Crash on the Data Links Server", in the section "Running RECONCILE after restoring a file system": The db2_recon_aid utility is not shipped with this release of DB2 Data Links Manager for Windows NT. ------------------------------------------------------------------------ DB2 Replication Guide and Reference ------------------------------------------------------------------------ 16.1 About this book For the most up-to-date version of the data replication scenario (Chapter 3) and other information, go to the DB2 DataPropagator Web page (http://www.software.ibm.com/data/dpropr), click the "Library" link, then go to the section titled "Hints, Tips, and Documentation Corrections." ------------------------------------------------------------------------ 16.2 Part 2. Administration: Planning for Replication/Deciding What to Replicate/Replicating Large Objects Replication is not supported for DB2 Extenders for Text, Audio, Video and Image, or other extenders where additional control files associated with the extender's LOB column data are maintained outside of the database. ------------------------------------------------------------------------ 16.3 Part 2. Administration: Replication Restrictions for DB2 UDB Enterprise - Extended Edition DB2 UDB EEE is supported for replication with the following restrictions: * DB2 UDB EEE can be a target server for Apply. * DB2 UDB EEE can also be a staging platform (the middle tier in a 3-tier configuration). Changes captured at tier 1 can be replicated to CCD tables in a DB2 UDB EEE tier 2 database, and then replicated from there to any tier 3 target. * Change capture can be done on DB2 UDB EEE if the source table is not partitioned and resides on the catalog node. Any replication control tables must also be non-partitioned and reside on the catalog node. * The DB2 UDB Control Center does not provide choices to allow DB2 UDB EEE objects as replication sources, or make them part of a replication subscription. Use DJRA to define DB2 UDB EEE objects as replication sources or make them part of replication subscriptions. ------------------------------------------------------------------------ 16.4 Cold-starting the Capture Program with CCD Targets In the case of NonCondensed, NonComplete CCD, a gap will be detected if you bring down the Capture program and cold start it again. It's not recommended that you bring down the Capture program and cold start again with this kind of target type. To recover, set SYNCHPOINT, SYNCHTIME, and LASTSUCCESS to NULL in the subscription set table. Then cold start the Capture program, and start the Apply program. Data integrity could be lost, in which case you would need to manually insert the missing rows into the target table. ------------------------------------------------------------------------ 16.5 Key Update Restrictions A particular form of key update, using the syntax SET KEYCOL = KEYCOL + n, cannot be replicated correctly with respect to condensed copies. In such a case, a different column must be used as the subscription key. If no suitable alternate key exists in the source table, then, before defining the table as a replication source, the table needs to be altered to include a new column. Existing source table rows must have unique values assigned to the new column. Thereafter, unique key values must be assigned to the new column whenever rows are inserted into the source table. This new column must be included in the subscription of any condensed copy (user copy, point in time, condensed CCD, replica), all of which require a unique key. ------------------------------------------------------------------------ 16.6 Informix Logging and DJRA It is recommended that you enable transaction logging for Informix databases involved in replication. ------------------------------------------------------------------------ 16.7 Replicating Large Objects Replication is not supported for DB2 Extenders for Text, Audio, Video, Image, or other extenders where additional control files associated with the extender's LOB column data are maintained outside of the database. ------------------------------------------------------------------------ 16.8 Part 3. Operations: New Invocation Parameter for the Apply Program The ERRWAIT(n) Apply program invocation parameter enables you to control the number of seconds that the Apply program waits before attempting to retry after an error condition occurs. The default wait period is 5 minutes (300 seconds). UNIX example: asnapply aplqual1 errwait(600) In this example, aplqual1 is the Apply qualifier, and 600 specifies that the Apply program wait 600 seconds (10 minutes) before retrying after it encounters an error condition. Notes: 1. The ERRWAIT(n) invocation parameter is not available on DPROPR/400. 2. If you specify a number that is too small for 'n,' and do not correct the error in a timely manner, the Apply program runs almost continuously, and therefore generates many rows in the Apply trail table. ------------------------------------------------------------------------ 16.9 Chapter 9. Capture and Apply for AS/400: Connecting to an AS/400 Server Before you begin: * Make sure that you have the DB2 Personal Edition or other client installed on your workstation. * Make sure that your workstation has TCP/IP. To connect to an AS/400 server from DB2 for Windows NT: 1. Determine the name of the relational database on the AS/400 server: a. Log on to the AS/400 server that you want to connect to. b. Submit a dsprdbdire command and then specify local for *LOCAL. c. Locate the name of the database in the output. In the following output example, the database name is DB2400e: Relational Remote Option Database Location MYDBOS2 9.112.14.67 RCHASDPD RCHASDPD DB2400E *LOCAL RCHASLJN RCHASLJN 2. Catalog the AS/400 database on DB2 for Windows NT: a. From your Windows NT workstation, click Start -> Programs -> DB2 for Windows NT -> Command Window. The DB2 CLP command window opens. b. In the command window, enter the following three commands in order: db2 catalog tcpip node server_name remote server_name server 446 system server_name ostype OS400 db2 catalog dcs database rdb_name AS rdb_name db2 catalog database rdb_name AS rdb_name at node server_name authentication dcs where server_name is the name of the AS/400 system, and rdb_name is the name of the AS/400 relational database that you found in step 1. 3. Stop DB2 for Windows NT. Close all the Control Centers and any other DB2 tools, and type db2stop and db2admin stop in one of the command windows. 4. Ensure that the AS/400 User Profile that you will use to log on uses CCSID 37: a. Log on to the AS/400 system. b. Enter the following command: CHGUSRPRF USRPRF(SINN) CCSID(37) where SINN is the user profile. 5. Ensure that the DDM server is started on the AS/400 system by entering: STRTCPSVR SERVER(*DDM) 6. On Windows NT, start the DB2 Control Center or the DataJoiner Replication Administration tool (DJRA), and connect to the AS/400 database. ------------------------------------------------------------------------ 16.10 MAX_SYNCH_MINUTES Support DPROPR/400 allows 5769DP2 users to run Version 1 registration/subscription pairs concurrently with Version 5 replication source/target pairs. However, MAX_SYNCH_MINUTES support is not available in such an environment. Do not specify MAX_SYNCH_MINUTES in a subscription set if Version 1 and Version 5 pairs will coexist. ------------------------------------------------------------------------ 16.11 CRTSQLPKG Command You must use the Create SQL Package (CRTSQLPKG) command to create the packages necessary to use Apply for AS/400 with remote systems on platforms other than AS/400. For example: CRTSQLPKG PGM(QDPR/QZSNAPV2) RDB(remote_RDB_name) MODULE(QZSNICHG) CRTSQLPKG PGM(QDPR/QZSNAPV2) RDB(remote_RDB_name) MODULE(QZSNAPV2) See "DB2 for AS/400 SQL Programming V4R3 (SC41-5611-02)" for more information about the CRTSQLPKG command. ------------------------------------------------------------------------ 16.12 Chapter 10. Capture and Apply for UNIX Platforms/Starting Capture for UNIX Platforms AIX example: export LIBPATH=db2instance_home_directory/sqllib/lib:/usr/lib:/lib export LANG=en_US HP-UX example: export SHLIB_PATH=db2instance_home_directory/sqllib/lib:/usr/lib:/lib export LANG=en_US Solaris example: export LD_LIBRARY_PATH=instanceowner/sqllib/lib:/usr/lib:/lib export LANG=en_US export NLS_PATH=~/usr/lib/locale/%L/%N:/instanceowner/sqllib/msg/en_US/%N ------------------------------------------------------------------------ 16.13 Part 5. The DataJoiner Replication Administration Tool IBM has changed the default installation directory for DJRA: it is now SQLLIB\DJRA\djra.exe. ------------------------------------------------------------------------ 16.14 Part 6. Reference Information/Chapter 21. Table Structures: Consistent-change-data-tables (CCD) For CCD tables: * For condensed CCD tables, a unique index is required for user-data primary-key columns to maintain the CCD table. * An internal CCD table is an alternate source for the original user table. The user table does not include computed columns; therefore, computed columns should not be included in the CCD subscriptions. ------------------------------------------------------------------------ 16.15 Chapter 23. Capture and Apply Messages Apply program message ASN1069E: The Apply program has detected a referential integrity violation and compensated the rejected transactions. See the unit-of-work table for details. The error code is %1. Explanation: A referential integrity violation was detected when replicating data from the replica to the user table. User Response: See the unit-of-work table for details. Apply program message ASN1071: The Apply program could not reposition the work file. The error code is %1. Explanation: The Apply program has detected an error while reading the temporary work file. User Response: Contact your IBM Service representative. ------------------------------------------------------------------------ 16.16 Appendix A. How the DB2 library is Structured For late-breaking information that could not be included in the DB2 books: * On UNIX-based platforms, see the Release.Notes file. This file is located in the DB2DIR/Readme/%L directory, where %L is the locale name and DB2DIR is: o /usr/lpp/db2_06_01 on AIX o /opt/IBMdb2/V6.1 on HP-UX, Solaris, and Silicon Graphics IRIX o /usr/IBMdb2/V6.1 on Linux. Printing the PostScript books (on UNIX-based platforms): Step 3: Decompress and print the manual you require using the following command for Linux: zcat | lpr -P PSPrinter_queue ------------------------------------------------------------------------ SQL Reference ------------------------------------------------------------------------ 17.1 Federated Server Functionality Not Available The distributed join installation feature and associated federated system functionality, as documented, are not currently supported with DB2 UDB Version 6.1. As a result, the following new statements are not supported: ALTER NICKNAME, ALTER SERVER, ALTER USER MAPPING, CREATE FUNCTION(Template), CREATE FUNCTION MAPPING, CREATE NICKNAME, CREATE SERVER, CREATE TYPE MAPPING, CREATE USER MAPPING, CREATE WRAPPER, GRANT(Server Privileges), REVOKE (Server Privileges), SET PASSTHRU, and SET SERVER OPTION. ------------------------------------------------------------------------ 17.2 OLAP Function In Chapter 3 ("Expressions"), the syntax diagram has been changed to: .-operator------------------------------------. V | >>----+-----+---+-function--------------------+--+------------->< +- + -+ +-(expression)----------------+ '- - -' +-constant--------------------+ +-column-name-----------------+ +-host-variable---------------+ +-special-register------------+ +-(scalar-fullselect)---------+ | (1) | +-labeled-duration------------+ | (2) | +-case-expression-------------+ | (3) | +-cast-specification----------+ | (4) | +-dereference-operation-------+ | (5) | '-OLAP-function---------------' operator: (6) |---+-CONCAT------+---------------------------------------------| +- / ---------+ +- * ---------+ +- + ---------+ '- - ---------' Notes: 1.See Labeled Durations for more information. 2.See CASE Expressions for more information. 3.See CAST Specifications for more information. 4.See Dereference Operations for more information. 5.See OLAP Functions for more information. 6.|| may be used as a synonym for CONCAT. Following is a new subsection, "OLAP Functions", to be added to the end of "Expressions": OLAP-function: >--+--| ranking-function |----+------------------------------------> '--| numbering-function |--' ranking-function: |--+--RANK()-------+--OVER--(--+-------------------------------+--> '--DENSERANK()--' '--| window-partition-clause |--' >---| window-order-clause |--)-------------------------------------| numbering-function: |--ROWNUMBER()-----OVER--(-+-------------------------------+------> '--| window-partition-clause |--' >---+---------------------------+--)-------------------------------| '--| window-order-clause |--' window-partition-clause: .---,----------------------. V | |----PARTITION BY------partitioning-expression--+------------------| window-order-clause: .---,--------------------------------. | | V .--ASC-----. | >----ORDER BY-----sort-key-expression--+----------+--+------------| '--DESC----' OnLine Analytical Processing (OLAP) functions provide the ability to return ranking and row numbering information in a query result. An OLAP function can be included in expressions in a select-list or the ORDER BY clause of a select-statement (SQLSTATE 42903). An OLAP function cannot be used as an argument of a column function (SQLSTATE 42607). The query result to which the OLAP function is applied is the result table of the innermost subselect that includes the OLAP-function. The ranking function computes the ordinal rank of a row within the partition of the row according to the ordering of the rows in the partition. Rows that are not distinct with respect to the ordering within their partition are assigned the same rank. The results of ranking may be defined with or without gaps in the numbers resulting from duplicate values. If RANK is specified, the rank of a row is defined as 1 plus the number of rows that strictly precede the row. Thus if two or more rows are not distinct with respect to the ordering, there will be one or more gaps in the sequential rank numbering. If DENSERANK is specified, the rank of a row is defined as 1 plus the number of rows preceding the row that are distinct with respect to the ordering. Therefore, there will be no gaps in the sequential rank numbering. The ROWNUMBER function computes the sequential row number, starting with 1 for the first row, of the row within its partition according to the ordering. If the ORDER BY clause is not specified in the window specification, the row numbers are assigned to the rows in arbitrary order as returned by the subselect (not according to any ORDER BY clause in the select-statement). The data type of the result is big integer. The result cannot be null. PARTITION BY (partitioning-expression,...) Defines the partition within which the ranking or numbering is applied. A partitioning-expression is an expression used in defining the partitioning of the result set. Each column-name referenced in a partitioning-expression must unambiguously reference a column of the result set of the subselect of the OLAP function (SQLSTATE 42702 or 42703). The length of each partitioning-expression must not be more than 255 bytes (SQLSTATE 42907). A partitioning-expression cannot include a scalar-fullselect (SQLTATE 42822) or any function that is not deterministic or has an external action (SQLSTATE 42845). ORDER BY (sort-key-expression,...) Defines the ordering of rows within a partition that helps determine the value of the OLAP functions (it does not define the ordering of the result set, although it may appear that way in some situations). A sort-key-expression is an expression used in defining the ordering of the result set. Each column-name referenced in a sort-key-expression must unambiguously reference a column of the result set of the subselect of the OLAP function (SQLSTATE 42702 or 42703). The length of each sort-key-expression must not be more than 255 bytes (SQLSTATE 42907). A sort-key-expression cannot include a scalar-fullselect (SQLTATE 42822) or any function that is not deterministic or has an external action (SQLSTATE 42845). This clause is required for the RANK and DENSERANK functions (SQLSTATE 42601). ASC Uses the values of the sort-key-expression in ascending order. Null values are considered last in the order. DESC Uses the values of the sort-key-expression in descending order. Null values are considered first in the order. Examples: 1. Display the ranking of employees, in order by surname, according to their total salary (based on salary plus bonus) that have a total salary more than $30,000. SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE WHERE SALARY+BONUS > 30000 ORDER BY LASTNAME Note that if the result is required to be ordered by the ranking, replace the ORDER BY clause with: ORDER BY RANK_SALARY or ORDER BY RANK() OVER (ORDER BY SALARY+BONUS DESC) 2. Rank the departments according to their average total salary. SELECT WORKDEPT, AVG(SALARY+BONUS) AS AVG_TOTAL_SALARY, RANK() OVER (ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL FROM EMPLOYEE GROUP BY WORKDEPT ORDER BY RANK_AVG_SAL 3. Rank the employees within a department according to their education level. Having multiple employees with the same rank in the department should not increase the next ranking value. SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL, DENSERANK() OVER (PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL FROM EMPLOYEE ORDER BY WORKDEPT, LASTNAME 4. Provide row numbers in the result of a query. SELECT ROWNUMBER() OVER (ORDER BY WORKDEPT,LASTNAME) AS NUMBER, LASTNAME, SALARY FROM EMPLOYEE ORDER BY WORKDEPT,LASTNAME; 5. List the top five wage earners. SELECT EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY FROM (SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK() OVER ORDER BY (SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE) AS RANKED_EMPLOYEE WHERE RANK_SALARY < 6 ORDER BY RANK_SALARY; Note that a nested table expression was used to first compute the result including the rankings before the rank could be used in the where clause. A common table expression could also have been used. ------------------------------------------------------------------------ 17.3 Supported Functions In the "Supported Functions" table in Chapter 4, the row for RAISE_ERROR should reference table footnote 3 to indicate that the function cannot be used as a source function. ------------------------------------------------------------------------ 17.4 Changes to CREATE PROCEDURE Add the following syntax element to the syntax diagram, after the FENCED clause: .--MODIFIES SQL DATA--. --*--+---------------------+--- +--NO SQL-------------+ +--CONTAINS SQL-------+ '--READS SQL DATA-----' Note: The * represents a large bullet in the diagram. Add the following description: MODIFIES SQL DATA, NO SQL, CONTAINS SQL, or READS SQL DATA Indicates whether the stored procedure can execute any SQL statements, and if so, what type of SQL statements. See Table xx for a detailed list of SQL statements that can be executed under each data access indication. MODIFIES SQL DATA Indicates that the stored procedure can execute any SQL statement except statements that are not supported in stored procedures (SQLSTATE 38003). NO SQL Indicates that the stored procedure cannot execute any SQL statements (SQLSTATE 38001). CONTAINS SQL Indicates that SQL statements that neither read nor modify SQL data can be executed by the stored procedure (SQLSTATE 38004). Statements that are not supported in any stored procedure return a different error (SQLSTATE 38003). READS SQL DATA Indicates that some SQL statements do not modify SQL data can be included in the stored procedure (SQLSTATE 38002). Statements that are not supported in any stored procedure return a different error (SQLSTATE 38003). Add the following bullet with the table to the Notes section: o The following table indicates whether or not an SQL statement (specified in the first column) is allowed to execute in a stored procedure with the specified SQL data access indication. If an executable SQL statement is encountered in a stored procedure defined with NO SQL, SQLSTATE 38001 is returned. For other executions contexts, SQL statements that are not supported in any context return SQLSTATE 38003. For other SQL statements not allowed in a CONTAINS SQL context, SQLSTATE 38004 is returned, and in a READS SQL DATA context, SQLSTATE 38002 is returned. Table xx. SQL Statement and SQL Data Access Indication SQL Statement NO SQL CONTAINS SQL READS SQL DATA MODIFIES SQL DATA ---------------------- ------ ------------ -------------- ----------------- ALTER... N N N Y BEGIN DECLARE SECTION Y (1) Y Y Y CALL N N N N CLOSE CURSOR N N Y Y COMMENT ON N N N Y COMMIT (2) N N N N Compound SQL N Y Y Y CONNECT N N (3) N (3) N (3) CREATE ... N N N Y DECLARE CURSOR Y (1) Y Y Y DELETE N N N Y DESCRIBE N N Y Y DISCONNECT N N (3) N (3) N (3) DROP .... N N N Y END DECLARE SECTION Y (1) Y Y Y EXECUTE N Y (4) Y (4) Y EXECUTE IMMEDIATE N Y (4) Y (4) Y EXPLAIN N N N Y FETCH N N Y Y FREE LOCATOR N Y Y Y FLUSH EVENT MONITOR N N N Y GRANT ... N N N Y INCLUDE Y (1) Y Y Y INSERT N N N Y LOCK TABLE N Y Y Y OPEN CURSOR N N Y Y PREPARE N Y Y Y REFRESH TABLE N N N Y RELEASE CONNECTION N N (3) N (3) N (3) RENAME TABLE N N N Y REVOKE ... N N N Y ROLLBACK (2) N N N N SELECT INTO N N Y Y SET CONNECTION N N (3) N (3) N (3) SET INTEGRITY N N N Y SET special register N Y Y Y UPDATE N N N Y VALUES INTO N N Y Y WHENEVER Y (1) Y Y Y Table notes: o Although the SQL option implies that no SQL statements can be specified, non-executable statements are not restricted. o Implicit commits or rollbacks issued by utilities (such as REORG TABLE) are also not supported in stored procedures. o Connection management statements are not allowed in all stored procedure execution contexts. o It depends on the statement being executed. The statement specified for the EXECUTE statement must be a statement that is allowed in the context of the particular SQL access level in effect. For example, if the SQL access level in effect is READS SQL DATA, the statement must not be an INSERT, UPDATE, or DELETE. ------------------------------------------------------------------------ 17.5 Missing Restriction from the Description of CREATE TABLE The following restriction is missing from the description of CREATE TABLE when defining a summary table: When REFRESH DEFERRED or REFRESH IMMEDIATE is specified, the fullselect cannot include expressions that are a LOB type (or a distinct type based on a LOB type). ------------------------------------------------------------------------ 17.6 Changes Related to CREATE TABLE Pertaining to the Inheritance of Defaults In the syntax diagram, replace the fragment "summary-table-options" with the following: summary-table-options: |--+--DEFINITION ONLY--+----------------------+----------+----| | '--INCLUDING DEFAULTS--' | '--DATA INITIALLY DEFERRED--REFRESH--+--DEFERRED---+--' '--IMMEDIATE--' Under summary-table-definition, fullselect description, delete the last paragraph which incorrectly states: Furthermore, for REFRESH IMMEDIATE, the base table must have at least one unique index defined and the SELECT clause must include all of the columns of this unique index. Replace the following text under "DEFINITION ONLY": DEFINITION ONLY The query is used only to define the table. The table is not populated using the results of query and the REFRESH TABLE statement cannot be used. When the CREATE TABLE statement is completed, the table is no longer considered a summary table. The columns of the table are defined based on the definitions of the columns that result from the fullselect. If the fullselect references a single table in the FROM clause, select list items that are columns of that table are defined using the column name, data type, and nullability characteristic of the referenced table. INCLUDING DEFAULTS The defaults for any updatable columns of the fullselect are defined for the corresponding columns of the created table. Columns that are not updatable in the fullselect will not have a default defined in the corresponding column of the created table. Replace the following text in the description of "LIKE table-name1 or view-name or nickname": LIKE table-name1 or view-name or nickname Specifies that the columns of the table have exactly the same name and description as the columns of the identified table (table-name1), view (view-name) or nickname (nickname). The name specified after LIKE must identify a table, view or nickname that exists in the catalog. A typed table or typed view cannot be specified. The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table, view or nickname. The implicit definition includes the column name, data type, nullability characteristic and column default value of each of the columns of table-name1. The implicit definition includes the column name, data type, and nullability characteristic of each of the result columns of fullselect defined in view-name. The implicit definition includes the column name, data type, and nullability characteristic of each column of nickname. The implicit definition does not include any other attributes of the identified table, view or nickname. Thus the new table does not have any unique constraints, foreign key constraints, triggers, or indexes. The table is created in the table space implicitly or explicitly specified by the IN clause, and the table has any other optional clause only if the optional clause is specified. ------------------------------------------------------------------------ 17.7 Changes to PREPARE, Untyped Parameter Marker Usage Table Replace "Data Type" column entry for the row in the table where the first column (Untyped Parameter Marker Location) entry is "Any or all operands of the IN list of the IN predicate" with the following text: Results of applying the Rules for Result Data Types on all operands of the IN predicate (operands to the left and right of the IN predicate) that are other than untyped parameter markers. Note that this may change the data type determined for the parameter marker by the previous versions, since it will consider the data types of operands on both sides of the IN keyword, instead of using the data type of the first operand (the operand to the left of the IN keyword). ------------------------------------------------------------------------ 17.8 SET INTEGRITY Replace the sixth bullet in the "Notes" section with the following: o Situations in which the system must check the whole table for integrity (INCREMENTAL option cannot be specified), for the statement SET INTEGRITY for T IMMEDIATE CHECKED, are: 1. when new constraints have been added to T itself, or to any of its parents which are in check pending state 2. when a Load Replace has taken place into T, or the NOT LOGGED INITIALLY WITH EMPTY TABLE option has been activated after the last integrity check on T 3. (cascading effect of full processing) when any parent of T has been Load Replaced or checked for integrity non-incrementally 4. if the table was in check pending state before migration, full processing is required the first time the table is checked for integrity after migration 5. if the table space containing the table or its parent has been rolled forward to a point in time. ------------------------------------------------------------------------ 17.9 Changes to UPDATE Under the description of "CURRENT OF cursor-name", add the following restriction: This form of UPDATE cannot be used if target of the update is a view that includes an OLAP function in the select list of the fullselect that defines the view (SQLSTATE 42828). ------------------------------------------------------------------------ System Monitor Guide ------------------------------------------------------------------------ 18.1 Changes to Statement Operation For event monitoring, the Statement Operation data element (operation) does not log an event for every SQL FETCH operation. This differs from Version 2, as it is prohibitive from a CPU cost and a storage space cost to record this database activity. ------------------------------------------------------------------------ 18.2 Information Available from Event Monitors In an XA environment, an event monitor will not log transaction records. ------------------------------------------------------------------------ Troubleshooting Guide Each new release of DB2 Universal Database builds upon the solid foundation of its predecessor. This is why the IBM DB2 Universal Database Troubleshooting Guide Version 5, included with your DB2 product, retains its value as a diagnostic source, in consultation with DB2 Customer Service. However, to reflect the enhancements and additions to IBM DB2 Universal Database Version 6, the English version of the DB2 Universal Database Troubleshooting Guide Version 6; is available online at http://www.software.ibm.com/data/db2/library/publications/ Korean and Japanese versions of the guide will also be available. To order a hardcopy (English) of the DB2 Universal Database Troubleshooting Guide Version 6, use form number S10J-8169-01. ------------------------------------------------------------------------ Client Configuration Assistant (CCA) ------------------------------------------------------------------------ 20.1 Using Configuration Profiles Generated by the GUI Tools If you plan to ftp a configuration profile from one machine to another, ensure that you ftp the file in binary. ------------------------------------------------------------------------ 20.2 CLI/ODBC Settings Notebook The CLI/ODBC Settings Notebook is available on OS/2, Windows 95, and Windows NT. It is accessible from the Client Configuration Assistant, or from the ODBC Driver Manager (if it is installed on the system). The notebook can only be used to modify the configuration settings for databases configured as ODBC data sources. If you want to modify the settings of a CLI data source using the notebook, you must register the database as an ODBC data source. The settings (stored in the db2cli.ini file) affect both CLI and ODBC applications accessing the database. The DB2 CLI/ODBC driver default behavior can be modified by specifying values for both the PATCH1 and PATCH2 keyword through either the db2cli.ini file or through the SQLDriverConnect() or SQLBrowseConnect() CLI API. The PATCH1 keyword is specified by adding together all keywords that the user wants to set. For example, if patch 1, 2, and 8 were specified, then PATCH1 would have a value of 11. Following is a description of each keyword value and its effect on the driver: 1 - This makes the driver search for "count(exp)" and replace it with "count(distinct exp)". This is needed because some versions of DB2 support the "count(exp)" syntax, and that syntax is generated by some ODBC applications. Needed by Microsoft applications when the server does not support the "count(exp)" syntax. 2 - Some ODBC applications are trapped when SQL_NULL_DATA is returned in the SQLGetTypeInfo() function for either the LITERAL_PREFIX or LITERAL_SUFFIX column. This forces the driver to return an empty string instead. Needed by Impromptu 2.0. 4 - This forces the driver to treat the input time stamp data as date data if the time and the fraction part of the time stamp are zero. Needed by Microsoft Access. 8 - This forces the driver to treat the input time stamp data as time data if the date part of the time stamp is 1899-12-30. Needed by Microsoft Access. 16 - Not used. 32 - This forces the driver to not return information about SQL_LONGVARCHAR, SQL_LONGVARBINARY, and SQL_LONGVARGRAPHIC columns. To the application it appears as though long fields are not supported. Needed by Lotus 123. 64 - This forces the driver to NULL terminate graphic output strings. Needed by Microsoft Access in a double byte environment. 128 - This forces the driver to let the query "SELECT Config, nValue FROM MSysConf" go to the server. Currently the driver returns an error with associated SQLSTATE value of S0002 (table not found). Needed if the user has created this configuration table in the database and wants the application to access it. 256 - This forces the driver to return the primary key columns first in the SQLStatistics() call. Currently, the driver returns the indexes sorted by index name, which is standard ODBC behavior. 512 - This forces the driver to return FALSE in SQLGetFunctions() for both SQL_API_SQLTABLEPRIVILEGES and SQL_API_SQLCOLUMNPRIVILEGES. 1024 - This forces the driver to return SQL_SUCCESS instead of SQL_NO_DATA_FOUND in SQLExecute() or SQLExecDirect() if the executed UPDATE or DELETE statement affects no rows. Needed by Visual Basic applications. 2048 - Not used. 4096 - This forces the driver to not issue a COMMIT after closing a cursor when in autocommit mode. 8192 - This forces the driver to return an extra result set after invoking a stored procedure. This result set is a one row result set consisting of the output values of the stored procedure. Can be accessed by Powerbuild applications. 32768 - This forces the driver to make Microsoft Query applications work with DB2 MVS synonyms. 65536 - This forces the driver to manually insert a "G" in front of character literals which are in fact graphic literals. This patch should always be supplied when working in an double byte environment. 131072 - This forces the driver to describe a time stamp column as a CHAR(26) column instead, when it is part of an unique index. Needed by Microsoft applications. 262144 - This forces the driver to use the pseudo-catalog table db2cli.procedures instead of the SYSCAT.PROCEDURES and SYSCAT.PROCPARMS tables. 524288 - This forces the driver to use SYSTEM_TABLE_SCHEMA instead of TABLE_SCHEMA when doing a system table query to a DB2/400 V3.x system. This results in better performance. 1048576 - This forces the driver to treat a zero length string through SQLPutData() as SQL_NULL_DATA. The PATCH2 keyword differs from the PATCH1 keyword. In this case, multiple patches are specified using comma separators. For example, if patch 1, 4, and 5 were specified, then PATCH2 would have a value of "1,4,5". Following is a description of each keyword value and its effect on the driver: 1 - This forces the driver to convert the name of the stored procedure in a CALL statement to uppercase. 2 - Not used. 3 - This forces the driver to convert all arguments to schema calls to uppercase. 4 - This forces the driver to return the Version 2.1.2 like result set for schema calls (that is, SQLColumns(), SQLProcedureColumns(), and so on), instead of the Version 5 like result set. 5 - This forces the driver to not optimize the processing of input VARCHAR columns, where the pointer to the data and the pointer to the length are consecutive in memory. 6 - This forces the driver to return a message that scrollable cursors are not supported. This is needed by Visual Basic programs if the DB2 client is Version 5 and the server is DB2 UDB Version 5. 7 - This forces the driver to map all GRAPHIC column data types to the CHAR column data type. This is needed in a double byte environment. 8 - This forces the driver to ignore catalog search arguments in schema calls. 9 - Do not commit on Early Close of a cursor 10 - Not Used 11 - Report that catalog name is supported, (VB stored procedures) 12 - Remove double quotes from schema call arguments, (Visual Interdev) 13 - Do not append keywords from db2cli.ini to output connection string 14 - Ignore schema name on SQLProcedures() and SQLProcedureColumns() 15 - Always use period for decimal separator in character output 16 - Force return of describe information for each open 17 - Do not return column names on describe 18 - Attempt to replace literals with parameter markers 19 - Currently, DB2 MVS V4.1 does not support the ODBC syntax where parenthesis are allowed in the ON clause in an Outer join clause. Turn on this patch2 will cause IBM DB2 ODBC driver to strip the parenthesis when the outer join clause is in an ODBC escape sequence. This patch2 should only be used when going against DB2 MVS 4.1. 20 - Currently, DB2 on MVS does not support BETWEEN predicate with parameter markers as both operands (expression ? BETWEEN ?). Turn on this patch will cause the IBM ODBC Driver to rewrite the predicate to (expression >= ? and expression <= ?). 21 - Set all OUTPUT only parameters for stored procedures to SQL_NULL_DATA 22 - This patch2 causes the IBM ODBC driver to report OUTER join as not supported. This is for application that generates SELECT DISTINCT col1 or ORDER BY col1 when using outer join statement where col1 has length greater than 254 characters and causes DB2 UDB to return an error (since DB2 UDB does not support greater-than-254 byte column in this usage 23 - Do not optimize input for parameters bound with cbColDef=0 ------------------------------------------------------------------------ 20.3 Tab Truncation in CCA SmartGuide (OS/2) Tabs in the Client Configuration Assistant SmartGuide on OS/2 may be truncated to one character if certain Matrox video drivers are used. These video drivers (for example, the 2.23.082 Matrox Millenium drivers) are not supported, because they have been shown not to work properly with the DB2 GUI tools. ------------------------------------------------------------------------ 20.4 Current Limitations on Client Configuration Export/Import Functionality The custom export (the Export button in the CCA with the Customize option) and the new "export all" function (db2cfexp on the command line, or the Export button in the CCA with the All option), which allow you to export client connectivity information, do not export administrator node information. When this profile is then imported on a different client using either the new "import all" function (db2cfimp on the command line), or through the CCA (on OS/2 or Windows operating systems only) through the "import all" or import customize functions, the database and instance information are cataloged without the system information. This will not affect application connectivity to the database, but it will cause the DB2 Control Center running on the client to interpret DB2 Version 5.x or DB2 Version 6.1 databases as DB2 Version 2 databases instead. DB2 Version 5.2 or DB2 Version 6.1 Control Centers are not able to properly administer DB2 Version 2 instances or databases, because there is no administration server for that database level. Similarly, they cannot properly administer DB2 Version 5.x or DB2 Version 6.1 instances or databases, unless the administration server node information is cataloged. Following are suggested workarounds: * The easiest workaround is to use the DB2 known discovery or DB2 search discovery mechanisms to discover the system on which the instance or database that you want to administer is located, and to (automatically) catalog the node information through those mechanisms. This can be accomplished from the CCA or from the Control Center. For more information about the search and known discovery procedures, refer to the Administration Guide. * If discovery is unavailable, or if you want to create multiple clients that can perform administrative tasks, and you do not want to go to each machine and use the discovery mechanism to catalog the necessary information, you can generate a "server profile" at the server, and then use "non-interactive import" of that profile at the client. A server profile can be generated using "export server profile" from the Control Center, or by issuing "db2genp" from the command line. The profile (generate.spf) can be found in the /sqllib/tmp directory on UNIX based systems, or in the \sqllib\\tmp directory on OS/2 or Windows operating systems. Non-interactive import can be invoked by selecting "Import ALL" from the CCA (on Win32 or OS/2 clients), or by issuing "db2cfimp fname" from the command line. Multiple administration nodes can be configured by importing multiple server profiles. Non-interactive import will import and set up node and database directories to connect to all the databases in that server profile. If that is not a desired outcome, the administrator can edit the profile(s) and remove all groups from the profile (except for groups headed by [File_Description] or [adminst>.. ]) prior to the non-interactive import. ------------------------------------------------------------------------ Control Center ------------------------------------------------------------------------ 21.1 Starting the Control Center For information on installing, configuring, and starting the Control Center, refer to the Control Center README located at sqllib/cc/prime/readme.htm. ------------------------------------------------------------------------ 21.2 No Help on Windows 95 If the url.dll File Is Not in the \windows\system Directory The url.dll file can be extracted from the MicroSoft Plus! CD (it is not installed by default on Windows 95). For more information, visit: http://support.microsoft.com/support/kb/articles/Q147/3/83.asp ------------------------------------------------------------------------ 21.3 db2look and db2advis Must be Installed to Use "Generate DDL" and the "Index SmartGuide" To use "Generate DDL" and the "Index SmartGuide" in the Control Center, db2look and db2advis must be installed on the specific DB2 server. On OS/2 and the Windows operating system, these utilities are only installed if the "Miscellaneous database tools" component is selected during installation of the DB2 server. ------------------------------------------------------------------------ 21.4 No Access to DB2 for OS/390 Version 5 through the Control Center The Control Center makes reference to both Version 5 and Version 6 of DB2 for OS/390. Access to DB2 for OS/390 Version 5 through the Control Center is not currently available. ------------------------------------------------------------------------ 21.5 Infopops Some windows, such as the SmartGuides and the help for DB2 UDB for OS/390 objects, have more detailed help known as infopops. In these windows, you can display the help for a field or control by selecting it and pressing F1. However, infopops are not available in the Multi-Site Update SmartGuide. To see the infopops on a list on page 2 and page 4 of the Create Table SmartGuide, select an entry in the list and press F1; you cannot invoke them by placing the mouse pointer over the list. If you want to see infopops on single and multi-column table fields in SmartGuides, select an entry (row) in the table field and press F1. Mouse-over invocation of infopops on these fields will work only if there are selected entries in the table fields, and you place the mouse pointer over the selected entry. To see infopops for a single or multi-column table field in some of the DB2 UDB for OS/390 dialogs, select an entry (row) in the table field and press F1. Mouse-over invocation of infopops on these fields will work only if there are selected entries in the table fields, and you place the mouse pointer over the selected entry. Dialogs for replication objects and actions that appear under a DB2 UDB for OS/390 subsystem in the Control Center object tree do not have infopops on their fields and controls. Because of a limitation in the Java Virtual Machine, when you're using a SmartGuide while running the Control Center as an applet and you press F1 to get help on a control, the window help for the SmartGuide may appear instead of the infopop for the control. When using a Control Center dialog at screen resolution 640x480 (and in some cases 800x600), the infopops that appear for text fields might be off the edge of the dialog. In these cases, move the dialog to the left or to the right to view the infopops. ------------------------------------------------------------------------ 21.6 Entry Field Input Method for Korean Characters in the Control Center When entering Korean characters for input fields in the Control Center, the interim characters will be displayed on the character input console instead of directly in the entry field. The finalized characters are displayed in the entry field. ------------------------------------------------------------------------ 21.7 Corrupted Fields When running the Korean Control Center on an English Version of Netscape (OS/2) When running the Korean Control Center on OS/2, using an English version of Netscape, the following text fields on the db2cc.htm page might be corrupted: Server IP name/address Server port Start Control Center If this occurs, simply type the correct server IP address and server port, and then click the Start Control Center button. The Control Center will open. ------------------------------------------------------------------------ 21.8 Modify the DBCS Search Form When the Web Control Center is Installed Without the Product Documentation (UNIX only) If you installed the DB2 Control Center online help in Japanese, Korean, Simplified Chinese, or Traditional Chinese, but not the product documentation, searching the Control Center help will not work unless the DB2 search form (db2srch.htm) is modified. These changes are only required on UNIX based systems. First, add the following line before any other tags: where **LANG** is set to JAP for Japanese, KOR for Korean, CHS for Simplified Chinese, and CHT for Traditional Chinese. For instance, add the following line for Korean: Also, change this line (replacing the "Y" with "N"): to this: Do not translate the text in any tag. ------------------------------------------------------------------------ 21.9 JDK Problem When Running the Control Center in Simplified Chinese on UNIX Based Systems When running the Control Center in Simplified Chinese on UNIX based operating systems, the window title will not be displayed correctly. All frame titles will be "AWTapp", and all dialog titles will be "Dialog". This is a known JDK problem that will be fixed in future releases. ------------------------------------------------------------------------ 21.10 JDK Problem When Running the Control Center in Simplified Chinese or Korean on HP-UX When running the Control Center in Simplified Chinese and Korean locales on the HP-UX operating system, all characters will appear as "?" in the window client area, except the title bar. This is a known HP-UX JDK problem that will be fixed in future releases. ------------------------------------------------------------------------ 21.11 Decimal Separator Problem for Some Locales When Running the Control Center on Netscape Version 4.5 When running the Control Center on Netscape Version 4.5, the decimal separator used in the number fields may not be correct for some locales (for example, Spain and Norway), because a back-level JDK Version 1.1.5 is being used by the Web browser (Netscape). This problem does not exist if you are running the Control Center in application mode, which uses JDK Version 1.1.7. ------------------------------------------------------------------------ 21.12 Java Exception When Running the Control Center on Netscape Version 4.5 When running the Control Center with a JDK/JRE level older than 1.1.7, the following exception may appear on the console: java.lang.NullPointerException at java.awt.Component$NativeInLightFixer.componentMoved(Compiled Code) at java.awt.Component.processComponentEvent(Compiled Code) at java.awt.Component.processEvent(Compiled Code) at java.awt.Container.processEvent(Compiled Code) at java.awt.Component.dispatchEventImpl(Compiled Code) at java.awt.Container.dispatchEventImpl(Compiled Code) at java.awt.Component.dispatchEvent(Compiled Code) at java.awt.EventDispatchThread.run(Compiled Code) This exception will not cause any functional problems. Currently, Netscape 4.5 is using JDK Version 1.1.5, which will show this exception on the Java console. ------------------------------------------------------------------------ 21.13 Control Center/Web Administration Problem on Netscape Version 4.5 (Simplified Chinese) The Control Center and Web Administration cannot be run on the Simplified Chinese (zh_CN) version of Netscape 4.5. ------------------------------------------------------------------------ 21.14 Special Character Problem for Some Locales When Running the Control Center on Netscape Version 4.5 When running the Control Center on a machine that has an European keyboard, some of the special characters cannot be entered in the entry field or multi-line edit area. These special characters are entered by holding down the "AltGr" key and pressing another key. This is a JDK problem which has been fixed in JDK Version 1.1.8. However, DB2 is shipped with JDK/JRE Version 1.1.7, and the Web browser (Netscape) is using JDK Version 1.1.5. This problem does not exist if you are running the Control Center in application mode, which uses JDK Version 1.1.7. Another alternative is to set the system input locale to English. ------------------------------------------------------------------------ 21.15 Typing Characters in the Control Center Using the AltGr Key If you are using an enhanced keyboard that has the AltGr key, you may not be able to enter characters in Control Center fields using AltGr directly from the keyboard. You can enter their ASCII equivalents instead. The table below contains the character in the Character column and the ASCII value in the Corresponding ASCII Value column. To enter these characters, hold down the Alt key and type in the corresponding numbers on the numeric keypad of your keyboard. Character Corresponding ASCII Value # 35 $ 36 , 44 - 45 . 46 / 47 ; 59 @ 64 [ 91 \ 92 ] 93 ^ 94 _ 95 ` 96 { 123 | 124 } 125 ~ 126 euro 128 ¢ 0162 £ 0163 ¤ 0164 § 0167 « (left angle quote) 0171 ¬ 0172 ° 0176 ± 0177 2 0178 3 0179 ´ 0180 µ (micro second) 0181 1 0185 » (right angle quote) 0187 ¼ 0188 ½ 0189 ¾ 0190 À 0192 È 0200 É 0201 ß (sharp S) 0223 à 0224 è 0232 é 0233 ------------------------------------------------------------------------ 21.16 Running the Control Center on the Hummingbird Exceed X Window Server When running the Control Center on the Hummingbird Exceed X Window Server, some dialogs (or some infopops on dialogs) may appear partially truncated. This problem does not occur on an AIX workstation. ------------------------------------------------------------------------ 21.17 Running the Control Center as an Applet If you are running the Control Center as an applet, and you issue an export server profile command from the GUI, the function may not be able to write the profile to the local machine if all the security checks are in place. In this case, you can retrieve the profile from the administration server of the system against which you issued the export command. On UNIX based systems, the profile will be located in: /sqllib/tmp On OS/2 and Windows operating systems, the profile will be located in: sqllib\\tmp ------------------------------------------------------------------------ 21.18 Online Help Not Coming Up on OS/2 If, when you press F1 or press the Help button on a window, the online help does not come up, or if you get an error message and the online help does not come up, ensure that the subdirectory that contains the Netscape executable (by default in \netscape\program\netscape.exe) is included in your PATH environment variable (set in your config.sys file). ------------------------------------------------------------------------ 21.19 Online Help Not Coming Up on UNIX Based Systems If, when you press F1 or press the Help button on a window, the online help does not come up, or if you get an error message and the online help does not come up, ensure that Netscape 4.51 is installed, and that the directory that contains the Netscape script file (for example, /usr/bin) is included in your PATH environment variable. If pressing the Help button has no effect, and Netscape is in the $PATH, try starting Netscape from the command line and leaving it running. The Control Center help may be able to connect to a running browser when it cannot start the browser. ------------------------------------------------------------------------ 21.20 Online Help Not Coming Up on HP-UX Version 10 If, when you press F1 or press the Help button on a window, the online help does not come up, ensure that Netscape 4.51 is installed, and that the "netscape" command is in the $PATH of the user running the Control Center. If the Netscape program is a shell script that calls the Netscape executable, make sure that the shell script passes all command line parameters to the executable using the "$*" special variable. If Netscape can be invoked from the command line, but not from the Control Center, try starting Netscape from the command line, and leave it running. The Control Center help may be able to connect to a running browser when it cannot start the browser. If the online help stops coming up even while Netscape is running, try closing the Control Center and then reopening it. ------------------------------------------------------------------------ 21.21 Browser Opens Blank After Pressing the Help Button on Linux If the Netscape program is called by a shell script named "netscape" on your system, the shell script must pass all command line arguments to the Netscape executable. That is, it should invoke Netscape using a command ending with the special variable "$*" (without the double quotation marks). Otherwise, pressing the Help button may start the browser without loading the help file, and the Control Center may hang until the browser is stopped. ------------------------------------------------------------------------ 21.22 Federated Database System The distributed join installation feature and associated federated system functionality, as documented, are not currently supported with DB2 Universal Database Version 6.1. The following tasks can be accessed from the Online help Table of contents, but are not available for version 6.1: * Federated Database System - Working With * Nicknames - Working With o Filtering tables for creating nicknames o Creating nicknames o Changing options for nicknames * Servers - Working With o Creating a server o Selecting server options o Adding a server option o Adding new server options o Altering the values for server options o Dropping server options * User Mappings - Working With o Creating user mappings o Adding a user ID o Altering user mappings * Wrappers - Working With o Creating a wrapper ------------------------------------------------------------------------ 21.23 Locate Notebook The Locate notebook will be available only for DB2 for OS/390 in Version 6. ------------------------------------------------------------------------ 21.24 TCP/IP Bug on the Linux 2.2.2 Kernel that Causes Search Discovery to Fail On the Linux 2.2.2 kernel, there is a TCP/IP bug that will cause search discovery to fail with a recvfrom error. Search discovery is issued from the Control Center using the Refresh button in the Add System SmartGuide. Search discovery on Linux does not work with token-ring interfaces. ------------------------------------------------------------------------ 21.25 Use the Database Administration Server (DAS) When Cataloging a Communication Node on a Remote DB2 Server If you manually catalog a communication node directly to the server instance on a remote DB2 server; that is, bypass the Database Administration Server on that machine, that type of connection will appear in the Control Center as a "DRDA" connection, even though the connection will not be using DRDA to connect to the server instance. Use the Database Administration Server to optimize Control Center manipulation of a remote server. ------------------------------------------------------------------------ 21.26 Performance Monitor The Performance Monitor is not supported in VGA; the minimum color requirement is 256. There is a change in Version 6 of icon behavior for performance monitoring. The icon color will appear green as soon as monitoring is turned on for an object. Step 8 of the task steps for Defining the Thresholds Settings for a Performance Monitor should be replaced with the following: 8. On the right side of the Thresholds page, select the actions to be taken if the performance variable's value crosses a threshold. 1. Select a threshold or zone from the Threshold action list. 2. Choose one or more check boxes to identify the actions to be taken if a value enters the selected zone: Add an entry to the Alert Center. Issue a warning beep. Run a command. Specify the command file name in the entry field. Have a message sent to the user. Enter an appropriate message in the input window below the User message action. The Thresholds Action Section of the Thresholds page - Fields and Controls online help panel should be replaced with the following: Threshold action This section defines the actions to be taken when the value for a performance variable crosses a threshold or zone border. The Threshold action drop-down is used to select a threshold or zone border. The check boxes identify the actions that can be taken: * Add an entry to the Alert Center. * Issue a warning beep. * Run a command. Specify the command file name in the entry field. * Have a message sent to the user. Enter an appropriate message in the input window below the User message action. ------------------------------------------------------------------------ 21.27 Opening the Event Analyzer Window To open Event Analyzer -- * Type the DB2 command db2eva. The Event Analyzer window opens. ------------------------------------------------------------------------ 21.28 Summary Tables Summary tables can be identified as follows: From the Control Center, highlight tables in the object tree. In the list of tables shown in the contents pane, Summary tables are identified by having an S appear in the type column. ------------------------------------------------------------------------ 21.29 Web Administration Web Administration, the DB2 component of IBM Suites Web Administration, lets you start, stop, and force applications off DB2 instances, and grant or revoke user authorities to databases. Before attempting to run Web Administration, make sure you have set up and are able to run the DB2 Control Center. For more information on setting up the Control Center, refer to the online Control Center Installation and Configuration document located in sqllib\cc\prime\readme.htm. To access the Web Administration interface independently of IBM Suites Web Administration, use any of the browsers recommended in the Control Center Installation and Configuration document to access the following URL: http:///sqllib/cc/c2/start.html where is the name of the machine where your Web server resides. If you choose to use a virtual directory, substitute this directory for the home directory. For example, if you map sqllib to a virtual directory called temp on a Web server named yourserver, a client would use the URL http://yourserver/temp/cc/c2/start.html ------------------------------------------------------------------------ 21.30 Log File Size (logfilsiz) Database Configuration Parameter On the Configure Database notebook, on the Logs page, when you click on the Log File Size parameter, the Hint text says the range for NT is [4 - 4095] Pages(4KB). For NT, the range is actually [4 - 65535] Pages(4KB). ------------------------------------------------------------------------ 21.31 Web Control Center Monitoring To run a "command" as one of the alert actions on UNIX based systems requires that you be very specific about paths to executables and files. Furthermore, if executing a shell script, it is necessary to execute a shell. For example, if you want to run "myscript", enter something like the following in the "Run command" text field: /usr/bin/sh /home/username/myscript Within "myscript", it is important to include full paths and to ensure that commands can execute without user input. For example, to delete a file in your home directory, the script would contain the line: /usr/bin/rm -rf /home/username/file_to_remove Windows NT will use your PATH setting. However, batch files and executables should include extensions (for example, use "x:\directory\mybatch.bat", not just "mybatch"). ------------------------------------------------------------------------ 21.32 Help for the Statement Termination Character Field (Satellite Administration Center) For the Change Batch Step and the View Batch Step notebooks, and for the Create Script and the Edit Script windows, the help for the Statement termination character field should be: Use this field to indicate the statement termination character that you want to use for scripts that execute against DB2 instances or DB2 databases. The semicolon (;) character is the default statement termination character. Regardless of the statement termination character that you use, a new line is always used as the statement continuation character. ------------------------------------------------------------------------ 21.33 Promote Source and Promote Subscription Windows (Satellite Administration Center) The online help for the Satellite Administration Center contains information about the Promote Source and the Promote Replication Subscription windows. These windows are not available with the Control Center for Version 6.1. For the interim, to promote replication sources and subscriptions, use the promote functions that are supplied with the DB2 DataJoiner Replication Administration (DJRA) tool. For information about installing and starting the DJRA tool, refer to the "Replication Guide and Reference". For information about how to use the DJRA tool, refer to the online help that is provided with it. ------------------------------------------------------------------------ 21.34 Create and Edit Authentication Help (Satellite Administration Center) The help for the Create Authentication window and the Edit Authentication window states that the password of an authentication credential can be a maximum of 256 characters. This information is not correct. The maximum supported length for a password is 31 characters. ------------------------------------------------------------------------ 21.35 Incorrect Behavior in the Satellite Administration Center Following are known problems with the Satellite Administration Center, which is available from the Control Center. Note:You should always refresh the current view after committing any action. In this way, the Satellite Administration Center always displays the most up-to-date information. * Typically when you change an object from a location other than its folder, the Satellite Administration Center returns the message SAT2018 Are you sure you want to change the definition of the object?. In some situations, this confirmation is not displayed. For example, you can remove a batch step from a test batch by using the Change Level notebook, and this message is not displayed. * When you copy a batch by using the Copy Batch window, the new copy of the batch does not include the batch steps that were in the source batch. To add batch steps to the new batch, edit it, and add the required batch steps. Notes: 1. When you create a script that executes against the operating system on the satellite, remember to specify a valid extension (.bat or .cmd), or else the script cannot be executed. This information is in the Administering Satellites Guide and Reference, but is not in the online help provided with the Satellite Administration Center. 2. You do not always need to use a fix batch to fix a satellite (for example, in the event of a setup problem). In this situation, you can enable the satellite (it will be automatically disabled when it fails), correct the environment problem, then have the satellite synchronize again. You should always check the logs associated with the satellite before attempting to fix the satellite. ------------------------------------------------------------------------ 21.36 Limit on Log Details Information (Satellite Administration Center) The log details information that is available in the Satellite Administration Center about the synchronization session for a satellite can be a maximum of 256 KB. After the 256 KB limit is reached, additional log detail information is truncated. If you are using batches that result in more than 256 KB of detailed log information being written to the Satellite Administration Center and a satellite reports an error during a synchronization session (that is, the satellite is in the failed state), the log details may not indicate the problem. In this situation, you can determine the batch where the error occurred and the return code as follows: 1. Issue a query against the SATELLITES table. The SATELLITES table contains the ID column for the satellite, and the GRP_BEF_BATCH_STEP, the GRP_REP_BATCH_STEP, and the GRP_AFT_BATCH_STEP columns. These three columns indicate the last batch step that the satellite executed for the setup, update, and cleanup batches, respectively. For example, if you issue a query for the satellite and obtain a value from the GRP_BEF_BATCH_STEP column of 3, and your setup batch has 7 batch steps, then you know that the satellite encountered an error at batch step 3. 2. Determine the error code that was returned by the failed batch step. To determine the error, open the satellite details view. From the satellite details view, select the failed satellite, and view its logs. The Show Logs window opens, which displays the internal and external return codes. For additional information, refer to the online help that is available from the Satellite Administration Center. ------------------------------------------------------------------------ Tivoli-ready Support With the purchase of an IBM software product that carries the "Tivoli Ready" logo, you have the ability to manage your IBM software products through the Tivoli Enterprise management products, allowing you to automatically discover, monitor, and inventory one or more "Tivoli Ready" applications. IBM software products that are "Tivoli Ready" can be managed through either the Tivoli Enterprise Console (TEC), or through Tivoli Global Enterprise Manager (GEM). Supported TME configurations include managing IBM applications that are installed on TME managed nodes, PC managed nodes, and endpoints in a distributed environment. On the following Tivoli Web site, a free Tivoli Ready Enablement package is available for Tivoli customers to download: http://www.support.tivoli.com/tme10gem/tivoli-ready Additional information about this program is available from the following IBM Web site: http://www.software.ibm.com/tivoli-ready The Tivoli Ready instrumentation, when configured, provides you with the ability to: * Graphically view the health of the DB2 instances and the Administration Server through Tivoli Global Enterprise Manager Version 2.2 or Tivoli Enterprise Console (TEC). * Inventory DB2 using Tivoli Inventory. Your Tivoli Administrator will need to: * Install and configure the "Tivoli Ready" enablement package to activate the monitoring on each machine that is to be monitored. The enablement package and detailed instructions on how to install, configure and customize can be found at: http://www.support.tivoli.com/tme10gem/tivoli-ready * Import .amp files into the GEM Version 2.2 application, as described in the GEM Version 2.2 documentation. These files are needed on the server to configure the Tivoli Enterprise Environment, and can be found in /db2/tivready: OS/2: db2os2ce.amp DB2 Connect Enterprise Edition db2os2ee.amp DB2 UDB Enterprise Edition db2os2wg.amp DB2 UDB Workgroup Edition Windows NT: db2ntce.amp DB2 Connect Enterprise Edition db2ntee.amp DB2 UDB Enterprise Edition db2nteee.amp DB2 UDB Enterprise - Extended Edition db2ntwg.amp DB2 UDB Workgroup Edition AIX: db2ace.amp DB2 Connect Enterprise Edition db2aee.amp DB2 UDB Enterprise Edition db2aeee.amp DB2 UDB Enterprise - Extended Edition HP-UX: db2hce.amp DB2 Connect Enterprise Edition db2hee.amp DB2 UDB Enterprise Edition Solaris: db2sce.amp DB2 Connect Enterprise Edition db2see.amp DB2 UDB Enterprise Edition db2seee.amp DB2 UDB Enterprise - Extended Edition * Enable Tivoli Inventory, by copying the SYS inventory file to a directory that is defined in your current Tivoli Inventory Profile to be scanned by the Inventory application. Additional detailed information is available in the Tivoli Ready enablement package. Information about support for Tivoli Ready Enablement is available from: http://www.support.tivoli.com/tme10gem/tivoli-ready Product-specific updates to Tivoli Ready enablement components (such as AMP and Inventory signature updates) are available from: http://www.software.ibm.com/tivoli-ready ------------------------------------------------------------------------ Miscellaneous ------------------------------------------------------------------------ 23.1 DB2 Family Newsletter For the latest information about the DB2 family of products, you may subscribe to the "DB2 Family Newsletter" (English only). Simply send your request in a FAX to 1-905-316-4733, and include the following information: Name Company name Full mailing address Telephone number DB2 products you currently use