IBM DB2 UNIVERSAL DATABASE UDB RELEASE NOTES FIXPAK 17 (C) COPYRIGHT INTERNATIONAL BUSINESS MACHINES CORPORATION 1999. ALL RIGHTS RESERVED. CONTENTS | Welcome to DB2 Universal Database FixPak 17! | Main Changes for FixPak 8 1.0 SPECIAL NOTES | 1.1 FixPak 6 Service Information | 1.2 FixPak 17 Service Information 1.3 Product and Service Technical Library | 1.4 DB2 Library and FixPak Documentation | 1.5 Web Control Center Installation 1.6 DB2 Family Newsletter 2.0 ADMINISTRATION GUIDE 2.1 Implementing Your Design | 2.1.1 Creating a Database/Creating a Summary Table 2.1.1.1 Isolation Levels and Query Optimization Using Summary Tables or Replicated Tables | 2.1.1.2 REFRESH IMMEDIATE 2.1.2 Optimizing Performance When Data is Placed on RAID Devices | 2.2 Controlling Database Access | 2.2.1 DRDA-only Client Authentication | 2.2.2 Change Password Enablement for EEE | 2.2.3 Encrypted Passwords | 2.2.4 Loginretries Option (AIX only) 2.3 Auditing DB2 Activities 2.4 Utilities for Moving Data 2.4.1 LOAD, IMPORT, and EXPORT File Formats/PC/IXF File Formats/Code Page Considerations | 2.4.2 Overview of the LOAD Process (Pending States) 2.4.3 Load Restriction on Windows NT and OS/2 SMP-enabled Machines | 2.4.4 Error Scenario for the Autoloader Program on DB2 UDB Enterprise | - Extended Edition for Windows NT | 2.4.5 Changes in Autoloader Authentication Behavior | 2.4.6 Import/Export of WSF Data Containing BIGINT 2.5 Recovering a Database 2.5.1 Backing Up and Restoring Databases on Different Operating Systems 2.5.2 Considerations for Using ADSTAR Distributed Storage Manager (ADSM) | 2.5.3 Configuration Parameters for Database Logging | 2.5.4 Frequency of Backups and Time Required 2.5.5 Recovering a Database (Planning to Use the ROLLFORWARD Command) | 2.6 Using DB2 with an XA-Compliant Transaction Manager | 2.7 Application Considerations | 2.8 Understanding the SQL Compiler 2.9 Operational Performance | 2.9.1 Managing the Database Buffer Pool 2.9.2 Managing Multiple Database Buffer Pools | 2.10 Using the Governor 2.11 Configuring DB2 | 2.11.1 Encrypted Passwords 2.11.2 Tuning Configuration Parameters/Optimizing Hash Join Performance | 2.11.3 Database Log Files | 2.11.4 Trusted Clients and DRDA Use | 2.11.5 Loginretries Option (AIX only) 2.11.6 ASLHEAPSZ and Failed Queries Against Large Tables 2.11.7 INTRA_PARALLEL Setting on an SMP Machine (Windows NT) | 2.12 HACMP ES for AIX | 2.13 DB2 Registry and Environment Variables 2.14 Additional Rah (Run All Hosts) Information (Solaris and AIX only) | 2.15 Unicode/UCS-2 and UTF-8 Support in DB2 UDB | 2.15.1 Introduction | 2.15.1.1 UTF-8 | 2.15.2 UCS-2/UTF-8 Implementation in DB2 UDB | 2.15.2.1 Code Page/CCSID Numbers | 2.15.2.2 Creating a UCS-2 Database | 2.15.2.3 Data Types | 2.15.2.4 Identifiers | 2.15.2.5 UCS-2 Literals | 2.15.2.6 Pattern Matching in a UCS-2 Database | 2.15.2.7 IMPORT/EXPORT/LOAD Considerations | 2.15.2.8 Incompatibilities | 2.16 Using Virtual Interface (VI) Architecture | 2.16.1 Overview of Virtual Interface Architecture | 2.16.2 Install DB2 Universal Database Version 5.2 (EEE) | 2.16.3 Running DB2 UDB V5.2 for Windows NT with GigaNet Interconnect | 2.16.3.1 Setup Procedure for GigaNet Interconnect | 2.16.3.2 Implement DB2 to Run Using VI | 2.16.4 Running DB2 UDB V5.2 for Windows NT with ServerNet Interconnect | 2.16.4.1 Setup Procedure for ServerNet Interconnect | 2.16.4.2 Implement DB2 to Run Using VI 3.0 API REFERENCE 3.1 sqlecrea - Create Database 3.2 sqlubkp - Backup Database 3.3 sqlugtpi - Get Table Partitioning Information 3.4 sqluhget - Retrieve DDL Information From the History File (New API) 3.5 sqluhgne - Get Next Recovery History File Entry 3.6 sqluhops - Open Recovery History File Scan 3.7 sqluload - Load 3.8 SQLE-CLIENT-INFO (Connection Settings) 3.9 SQLFUPD 3.10 SQLUHINFO (SQLUHADM) 3.11 sqluvput - Writing Data to Device 4.0 BUILDING APPLICATIONS FOR WINDOWS AND OS/2 ENVIRONMENTS | 4.1 db2start on Windows NT Not Returning Correct Error Message 4.2 Building Windows NT and Windows 95 Embedded SQL Applications (IBM VisualAge for COBOL) 4.3 Building Java Applications and Applets 4.3.1 Setting the Windows Environment for the Microsoft SDK for Java | 4.3.2 New Client and Server Programs and SQLJ Precompiling Script 5.0 BUILDING APPLICATIONS FOR UNIX ENVIRONMENTS 5.1 SCO UnixWare 7 | 5.1.1 Exporting Functions for Stored Procedures 5.1.2 Micro Focus COBOL Link Option 5.2 Building Java Applications and Applets 5.2.1 Setting the Environment: HP-UX, SCO UnixWare 7 | 5.2.2 Silicon Graphics IRIX Restriction with close() Method 5.2.3 New Client and Server Programs and SQLJ Precompiling Script 6.0 CLI GUIDE AND REFERENCE 6.1 SQLPREPARE() 6.2 SQLDISCONNECT() 6.3 Using Stored Procedures - Programming Stored Procedures To Return Result Sets 6.4 Stored Procedure Catalog Tables | 6.5 SQLFetchScroll(SQL_FETCH_RELATIVE, -1) 7.0 COMMAND REFERENCE 7.1 db2cmd - Open DB2 Command Window 7.2 db2eva - Event Analyzer 7.3 db2evmon - Event Monitor Productivity Tool 7.4 db2look - DB2 Statistics Extraction Tool 7.5 db2set - DB2 Profile Registry Command 7.6 db2upd52 - Update Catalog to Support Version 5.2 7.7 BACKUP DATABASE 7.8 EXPORT 7.9 GET DATABASE MANAGER CONFIGURATION 7.9.1 New Parameter catalog_noauth | 7.9.2 TRUST_ALLCLNTS Updated 7.10 IMPORT 7.11 LIST DATABASE DIRECTORY 7.12 LOAD 7.13 RECONCILE 7.14 RESTORE DATABASE | 7.15 START DATABASE MANAGER 8.0 EMBEDDED SQL PROGRAMMING GUIDE 8.1 Writing User-Defined Functions (Interface between DB2 and a UDF/The Arguments Passed from DB2 to a UDF) 8.2 Programming in C and C++ (Programming Considerations/Host Variables) 8.2.1 Declaring Host Variables 8.2.2 Handling Graphic Host Variables (Windows Operating System) | 8.3 Programming in COBOL (Host Structure Support) 8.4 Programming in Java (Creating Java UDFs and Stored Procedures/Classes for Java Stored Procedures and UDFs) 8.5 A JDK Problem That Affects JDBC Programs Running under a DBCS Code Page on AIX 8.6 JDBC Sample Applications Restriction on SCO UnixWare 7 | 8.7 JDBC 2.0 | 8.8 Perl Access to DB2 UDB Databases 8.9 DATALINK Data Type 8.10 Multithreaded UNIX Applications Working with Code Page and Country Code 8.11 Error Processing for UDF Call Types 8.11.1 Scratchpad and Final Call Type Arguments for EXTERNAL TABLE Functions | 8.11.2 External UDF Error Processing Model for Scalar Functions | 8.12 NOT ATOMIC Compound SQL 8.13 Other Revisions to the Book 8.13.1 Revisions to Chapter 1 8.13.2 Revisions to Chapter 4 8.13.3 Revisions to Chapter 7 8.13.4 Revisions to Chapter 9 8.13.5 Revisions to Chapter 15 8.13.6 Revisions to Sample Programs 8.13.7 Other File Changes 9.0 INSTALLING AND CONFIGURING DB2 CLIENTS 9.1 Configuring Client to DB2 Server Communications Using the CCA 10.0 MESSAGES REFERENCE 10.1 Error in Description of DBI1768W 10.2 Changed/Additional Reason Codes for SQL0270N 10.3 New Description for SQL0351N 10.4 New Description for SQL0352N 10.5 New Description for SQL0357N 10.6 New Description for SQL0432N 10.7 New Description for SQL0680N 10.8 Additional Reason Code for SQL0903N 10.9 Additional Reason Code for SQL0911N 10.10 New Description for SQL1339N 10.11 SQL5048N or SQL0901N When Using Change Password Support | 10.12 New Message SQL5156N: Value of DBM configuration parameter | TRUST_ALLCLNTS 10.13 Additional Reason Code for SQL20053N 10.14 New Message Text for SQL20056N 10.15 Additional Reason Code for SQL30083N 11.0 QUICK BEGINNINGS 11.1 Installing DB2 on NEC PC98 Machines Running Windows 95 11.2 Uninstalling DB2 on NEC PC98 Machines Running Windows 95 11.3 Planning for Installation 11.3.1 Software Requirements/Server Product Requirements 11.3.2 Client Product Requirements/Software Requirements for OS/2 Clients | 11.3.3 Control Center Requirement on OS/2 Warp Server V.4 in DBCS | Environment 11.3.4 DB2 for AS/400 SNA Connections to DB2 Universal Database for AIX, OS/2, and Windows NT | 11.3.5 TP Name must be Unique among DB2 Instances on the Same Machine 11.3.6 Software Prerequisites/DB2 for OS/390 Recommended Fixes 11.4 Getting Started with DB2 Universal Database (Changing Passwords) 11.5 Getting Started with DB2 Universal Database (Counting Concurrent Users on AIX) 11.6 Working with Instances (Adding a Database Partition Server to an Instance, Using DB2 Extended Enterprise Edition for Windows NT/Domain User ID Requirement for Using Raw Device) | 11.7 db2start on NT Not Returning Correct Message 11.8 Migrating from Previous Versions and Releases | 11.9 Upgrading from DB2 Release 5.0 to Release 5.2 | 11.10 DB2 Driver error with Lotus Domino Go webserver 11.11 Running Your Own Applications (Running Java Programs) 11.12 Contents of the DB2 Products (Abbreviated Names for Locales) 12.0 QUICK BEGINNINGS FOR FILE MANAGER | 12.1 Client Configuration Import and Export | 12.2 DB2 Data Links Manager Cannot Be Installed on a DB2 Enterprise - | Extended Edition System 12.3 DLFM Administration User ID 12.4 Using NFS to Access a Remote DLFS File System 12.5 Restrictions Pertaining to Datalinks Clients 12.6 Changed Setup Option for DLFM 12.7 Recovering from a Disk Crash on the File Manager Server | 12.8 DataLink Number of Backups (dl_num_backup) 12.9 DB2 File Manager Recovery Scenarios 12.10 Datalinks Configuration File Cannot be Created or Modified through the Command Line Processor (CLP) 13.0 QUICK BEGINNINGS CHANGES DOCUMENTED BEFORE VERSION 5.2 13.1 DB2 Products 13.2 Planning for Installation 13.2.1 OS/2 Software Requirements 13.2.1.1 Possible Client-to-Server Connectivity 13.2.2 NetFinity Support Program Must Be Stopped - Windows NT 13.2.3 Communications Server for Windows NT - Fixes Required 13.2.4 Client Application Enabler Upgrade Recommended for Partitioned Database Users 13.2.5 Client Application Enabler Upgrade Recommended for Windows ODBC Users 13.2.6 Recommended Fix Level for ADSM on AIX 13.2.7 CS/NT is Required for Two-phase Commit Support on Windows NT 13.2.7.1 Registering CS/NT as a Windows NT Service 13.2.8 DCE Support on Windows NT and Windows 95 | 13.2.9 Recommended Windows NT Level 13.2.10 Assumption of SYSADM Authority (UNIX) 13.3 Other Installation and Configuration Tasks 13.3.1 Configuring CS/NT for Syncpoint Manager (SPM) 13.3.1.1 Additional Information when using the SNA SPM | 13.3.2 Problem Affecting NIS Users on AIX Versions 4.1.4 and 4.1.5 13.3.3 db2cpic.dll - Extended SNA Security Codes on Windows NT and Windows 95 13.3.4 DB2NTMEMSIZE Profile Variable for Windows NT 13.4 Snapshot Monitoring in the Control Center 13.5 Accessing DB2 Data from the Web 13.6 Setting Network-Related Parameters on the RS/6000 SP 13.7 Setting up the CCA and the Control Center in a Partitioned Database Environment (DB2 Universal Database Extended Enterprise Edition for AIX) 13.8 Migrating from Previous Versions (UNIX) 13.9 DB2 SNMP Subagent 13.10 Configuring Client to DB2 Server Communication Using the CCA 13.11 Administering and Using OS/2 User Profile Management 13.12 ADSM Client Setup for EEE on Solaris - Use db2profile 13.13 Connections from DB2 for OS/390 Version 5 to DB2 Universal Database Version 5 13.14 Configuring NetBIOS on Windows NT Servers 13.15 Shared Memory Addresses - Windows NT or Windows 95 13.16 Running the DB2 JDBC Applet Server as a Windows NT Service 13.17 Obtaining Information 13.17.1 Online Help 13.17.2 DB2 Books 13.17.3 Printing the PostScript Books 13.17.4 Viewing Online Books 13.17.5 Setting up a Document Server 13.17.6 Searching Online Books 13.18 Microsoft Internet Explorer 4.0 HTTP 1.1 Settings 13.19 Considerations When Remotely Administering Clusters Using the Control Center 14.0 REPLICATION GUIDE AND REFERENCE 14.1 New Platforms Supported 14.2 New Messages 14.3 LONG VARCHAR Support by the Capture program for DB2 UDB Version 5.2 | 14.4 DataJoiner Replication Administration Tool README 15.0 ROAD MAP TO DB2 PROGRAMMING 15.1 About the Application Developer's Kit 16.0 SQL REFERENCE | 16.1 Naming Conventions (cursor-name) | 16.2 ALTER TABLE | 16.3 Compound SQL 16.4 CREATE FUNCTION 16.4.1 DBINFO | 16.4.2 Error Processing for UDF Call Types | 16.5 CREATE TABLE | 16.5.1 CREATE TABLE Description | 16.5.2 CREATE TABLE Notes | 16.6 CREATE TABLESPACE 16.7 Appendix A. SQL Limits/Database Manager Limits 17.0 SYSTEM MONITOR GUIDE AND REFERENCE 17.1 Event Monitors are Created with Local Scope 18.0 WHAT'S NEW | 18.1 Performance (Query/Summary Tables) | 18.2 Building Java Applications and Applets Updates 18.3 Web Control Center and NetQuestion (Enabling the Web Control Center Remote Documentation Searches) 18.4 Control Center Restrictions | 18.5 Java Development Kit level for Web Control Center 19.0 HTML SEARCH SERVER FOR OS/2: SEARCHING HTML DOCUMENTATION 19.1 Installing the HTML Search System on OS/2 19.1.1 Prerequisites for the Search System on OS/2 19.1.2 Configuring TCP/IP on OS/2 19.1.3 Verifying TCP/IP Configuration 19.1.4 Locating the Search System Directory 19.1.5 Changing the Port Number for the Search Server 19.1.6 Diagnosing Search Server Install Errors 19.2 Using the HTML Search System 19.2.1 Starting and Stopping the Search Server 19.2.2 Searching while Disconnected from the Network 19.2.3 File Not Found Errors 19.2.4 If Search Does Not Work (ERROR 500 when trying to search) 19.2.5 Error Detected When Starting the Search Service 19.3 Uninstalling the HTML Search System 19.3.1 Problems Uninstalling the Search System on OS/2 20.0 HTML SEARCH SERVER FOR WINDOWS NT, WINDOWS 95, AND WINDOWS 98: SEARCHING HTML DOCUMENTATION 20.1 Restrictions on Use 20.2 Installing the HTML Search System on Windows NT, Windows 95, and Windows 98 20.2.1 Prerequisites for the Search System 20.2.2 Stop Any Previously Installed Version of the Search System 20.2.3 Locating the Search System Directory 20.2.4 Changing to a Different Port Number for the Search Server 20.2.5 Installing DB2 Universal Database on a LAN-Connected Drive 20.2.6 Diagnosing Search Server Install Errors 20.3 Using the HTML Search System 20.3.1 Starting and Stopping the Server 20.3.2 Searching with Proxies enabled in Netscape or Internet Explorer 20.3.3 Searching with a Laptop on Windows 95 20.3.4 If Search Does Not Work (ERROR 500 when trying to search) 20.3.5 File Not Found Errors 20.3.6 Error Detected when Starting the Search Service 20.4 Uninstalling the HTML Search System 20.4.1 If the Search System is not Uninstalled from Windows NT, Windows 95, or Windows 98 20.4.2 Rebooting After Uninstalling the Product | 21.0 NETQUESTION WORKAROUNDS | 21.1 IMNSearch (NetQuestion) installation problems on UNIX platforms | 21.2 Installing NetQuestion on HPUX-10 | 21.3 NetQuestion and the DB2 CAE for UNIX platforms | 21.4 NetQuestion DBCS on AIX platforms 22.0 SCOHELP INFORMATION 22.1 How to Access SCOhelp 22.2 Working With National Languages and SCOhelp 22.2.1 Special Considerations When Using the Spanish Language Extensions 22.2.2 Changing Default SCOhelp Language on the Server 22.2.3 Setting the Language SCOhelp Returns for a Specific Client 22.2.4 Using the Search 22.3 Web Control Center Help and SCOhelp 22.4 Support Level Supplement (SLS) PTF7004A, the UnixWare 7 Language Extension Supplement 22.4.1 Modifications to lang.conf for Simplified Chinese and Brazilian Portuguese 22.5 SCOhelp Search Index Files | Welcome to DB2 Universal Database FixPak 17! NOTE: Set the font to monospace for better viewing of these Release Notes. | This README file contains information for the release of FixPak 17 that was | not available when the DB2 manuals were issued. It consists of the README | file issued with the release of DB2 Version 5.2 as revised with this new | information. | A revision bar, "|", on the left side of a page indicates that the line on | the same level has been modified or added since the release of the Version | 5.2 README file. This file includes information on the following products: | IBM DB2 Universal Database Enterprise Edition, Version 5.2 | IBM DB2 Universal Database Enterprise - Extended Edition, Version 5.2 | IBM DB2 Universal Database Personal Edition, Version 5.2 | IBM DB2 Universal Database Workgroup Edition, Version 5.2 | IBM DB2 Personal Developer's Edition, Version 5.2 | IBM DB2 Universal Developer's Edition, Version 5.2 NOTE: VisualAge for Basic and Lotus Approach are no longer bundled in the product boxes. A separate Release Notes file, installed as READDCS.TXT, is provided for the following products: IBM DB2 Connect Enterprise Edition, Version 5.2 IBM DB2 Connect Personal Edition, Version 5.2 | Some books in the DB2 Universal Database library have not been updated for | Version 5.2. These include: o Administration Getting Started | o Building Applications for Windows and OS/2 Environments o CLI Guide and Reference o Embedded SQL Programming Guide o Master Index o SQL Getting Started o System Monitor Guide and Reference o Troubleshooting Guide. | The What's New book contains both an overview of some of the major DB2 | enhancements for Version 5 and Version 5.2, as well as a detailed description | of Version 5.2 enhancements, especially for the books not updated for Version | 5.2. New features that were available as part of DB2 Universal Database | Version 5 and DB2 Connect Version 5 FixPaks are considered Version 5.2 | enhancements. | To get the full DB2 information on a topic you are looking for, use the | appropriate book in the DB2 library in conjunction with the What's New book, | as well as the information in this README file. | MAIN CHANGES FOR FIXPAK 8 Note: There are no additional changes for FixPak 17, except the PTF numbers for each platform (see section 1.2). | This section describes the main changes for FixPak 8 and points you to where | these changes are documented. | CHANGE PASSWORD ENABLEMENT FOR EEE | DB2 Administrators may now allow others to change passwords on AIX and NT EEE | systems through the profile registry variable "DB2CHGPWD_EEE=". For | full details, see section 2.2.2, "Change Password Enablement for EEE". | DRDA-ONLY CLIENT AUTHENTICATION | The DB2 UDB server can now distinguish between DRDA clients from DB2 for MVS | and OS/390, DB2 for VM and VSE, and DB2 for OS/400, and other clients. This | allows you to protect against these other clients. The specified clients | above can be trusted to perform client-side authentication, and you can now | also determine where these clients are authenticated. For full details, see | section 2.2.1, "DRDA-only Client Authentication". | ENCRYPTED PASSWORDS | A new database manager configuration value, "SERVER_ENCRYPT", has been added | for the parameter "AUTHENTICATION". "SERVER_ENCRYPT" now has the same | semantics as "SERVER" authentication currently has, with the added feature | that any passwords sent over a network will be encrypted. For full details, | see section 2.2.3, "Encrypted Passwords". | JDBC 2.0 | The JDBC 2.0 driver shipped with this FixPak supports the JDBC 2.0 core API. | However, due to the unavailability of certain features of the DB2 Engine, not | all features defined in the specification are supported. For full details, | see section 8.7, "JDBC 2.0". | NETQUESTION WORKAROUNDS | This section addresses: | o IMNSearch (NetQuestion) installation problems on UNIX platforms | o Installing NetQuestion on HPUX-10 | o NetQuestion and the DB2 CAE for UNIX platforms | o NetQuestion DBCS on AIX platforms | For full details, see section 21.0, "NetQuestion Workarounds". | SUMMARY TABLE SUPPORT | Enhancements have been made to summary table support. The REFRESH IMMEDIATE | option is now available. For full details, see section 16.2, "ALTER TABLE" on | page 135, and section 16.5, "CREATE TABLE". | UNICODE/UCS-2 AND UTF-8 SUPPORT | The Unicode character encoding standard is a fixed-length, character encoding | scheme that includes characters from almost all the living languages of the | world. ISO and IEC 10646 standard (ISO/IEC 10646) specifies the Universal | Multiple-Octet Coded Character Set (UCS) that has a 2-byte version (UCS-2) | and a 4-byte version (UCS-4). DB2 UDB supports UCS-2, that is, Unicode | without surrogates. | With UCS-2 or Unicode encoding, ASCII and control characters are also two | bytes long, and the lead byte is zero. This could be a major problem for | ASCII-based applications and ASCII file systems because in UCS-2 strings | extraneous NULLs may appear anywhere in the string. A transformation | algorithm, known as UTF-8, can be used to circumvent this problem for | programs that rely on ASCII code being invariant. DB2 implements UTF-8. | For full details, see section 2.15, "Unicode/UCS-2 and UTF-8 Support in DB2 | UDB". | USING VIRTUAL INTERFACE (VI) ARCHITECTURE | DB2 UDB now supports two implementations of the VI architecture: One from | GigaNet and the other from ServerNet. For full details, see section 2.16.3, | "Running DB2 UDB V5.2 for Windows NT with GigaNet Interconnect". 1.0 SPECIAL NOTES | 1.1 FIXPAK 6 SERVICE INFORMATION | DB2 UDB Version 5.2 can be purchased and installed separately, or it can be | installed as a FixPak on top of DB2 UDB Version 5.0. You can upgrade Version | 5.0 to Version 5.2 with FixPak 6. The PTF numbers for FixPak 6 are listed in | the following table by operating system: +---------------------------------------------------------------------------+ | | Table 1. PTF Numbers for DB2 UDB FixPak 6 | +-------------------------------------+-------------------------------------+ | | OPERATING SYSTEM | PTF NUMBER FOR DB2 UDB FIXPAK 6 | +-------------------------------------+-------------------------------------+ | | AIX | U459852 | +-------------------------------------+-------------------------------------+ | | HP-UX 10 | U459854 | +-------------------------------------+-------------------------------------+ | | HP-UX 11 | U459872 | +-------------------------------------+-------------------------------------+ | | OS/2 | WR09073 | +-------------------------------------+-------------------------------------+ | | SCO UnixWare 7 | U459870 | +-------------------------------------+-------------------------------------+ | | Silicon Graphics IRIX | U459871 | +-------------------------------------+-------------------------------------+ | | Solaris | U459853 | +-------------------------------------+-------------------------------------+ | | Windows 95, Windows 98, and Windows | WR09074 | | | NT | | +-------------------------------------+-------------------------------------+ | | Windows 3.1 and Windows for | WR09076 | | | Workgroups 3.11 | | +-------------------------------------+-------------------------------------+ NOTE: Running the DB2 File Manager on AIX Version 4.2 requires a minimum of PTF U456886. The following PTF numbers, from SCO, are required for DB2 UDB Version 5.2 to run on SCO UnixWare 7: ptf7003c ptf7010 ptf7013 | For where to find the latest information about SCO PTFs required for DB2 UDB | Version 5.2, please see section 1.3, "Product and Service Technical Library". The following patch levels are required for running the Sun Cluster software: 105786-05 - Required patch for Sun Cluster 2.x on Solaris 2.6. 2.1_105458-06 - Required Sun Cluster patch level for DB2. | 1.2 FIXPAK 17 SERVICE INFORMATION | This FixPak cannot be installed on top of existing DB2 UDB Version 5.0 code. | You must be at the DB2 UDB Version 5.2 level. In order to get to the Version | 5.2 level, you can download and install FixPak 6. Once FixPak 6 is installed, | you can then apply FixPak 17. +---------------------------------------------------------------------------+ | | Table 2. PTF Numbers for DB2 UDB FixPak 17 | +-------------------------------------+-------------------------------------+ | | OPERATING SYSTEM | PTF NUMBER FOR DB2 UDB FIXPAK 17 | +-------------------------------------+-------------------------------------+ | | AIX | U480150 | +-------------------------------------+-------------------------------------+ | | HP-UX 10 | U480152 | +-------------------------------------+-------------------------------------+ | | HP-UX 11 | U480153 | +-------------------------------------+-------------------------------------+ | | OS/2 | WR21287 | +-------------------------------------+-------------------------------------+ | | SCO UnixWare 7 | U480154 | +-------------------------------------+-------------------------------------+ | | Silicon Graphics IRIX | U480155 | +-------------------------------------+-------------------------------------+ | | Solaris | U480151 | +-------------------------------------+-------------------------------------+ | | Windows 95, Windows 98, and Windows | WR21288 | | | NT | | +-------------------------------------+-------------------------------------+ | | Windows 3.1 and Windows for | WR21289 | | | Workgroups 3.11 | | +-------------------------------------+-------------------------------------+ 1.3 PRODUCT AND SERVICE TECHNICAL LIBRARY For complete, up-to-date DB2 information, including information on issues discovered after this README file was published, use the Product and Service Technical Library on the World Wide Web: http://www.software.ibm.com/data/db2/library | 1.4 DB2 LIBRARY AND FIXPAK DOCUMENTATION | In Version 5.2, most of the books that make up the product libraries have | been updated. The DB2 Library books are available online in HTML format. Most | are also available in PDF and postscript format. Refer to section 1.3, | "Product and Service Technical Library" above for where to access the books | online. See the What's New book for the complete list of updated books, | instructions for printing them, and the form numbers that you can use to | order them. You can use form number SBOF-8921-00 to order the complete set of | the books that were updated for Version 5.2. FixPak information that was previously included in files read1297.txt, read0398.txt, and read0698.txt has been included: o In the updated documentation, if the book was updated o In the What's New book, if the book was not updated. In addition, information that was not available when the books were printed is included in these Release Notes. | 1.5 WEB CONTROL CENTER INSTALLATION The Web Control Center is a Java version of the DB2 Universal Database Control Center (a database administration tool). Its design is modelled on the original DB2 Control Center, yet it provides a more flexible network-centric administration environment. The Web Control Center is implemented as a Java applet that uses DB2's JDBC support. | The FixPak will not install the Web Control Center but will upgrade an | existing version. To obtain the Web Control Center if you do not have it, | download it from the following URL: http://www.software.ibm.com/data/db2/udb/webcc 1.6 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 2.0 ADMINISTRATION GUIDE 2.1 IMPLEMENTING YOUR DESIGN | 2.1.1 CREATING A DATABASE/CREATING A SUMMARY TABLE 2.1.1.1 Isolation Levels and Query Optimization Using Summary Tables or Replicated Tables In general, a summary table or a replicated table is used for optimization of a query if the isolation level of the summary table or the replicated table is higher than or equal to the isolation level of the query. For example, if a query is running under the cursor stability (CS) isolation level, only summary tables and replicated tables that are defined under CS or higher isolation levels are used for optimization. | 2.1.1.2 REFRESH IMMEDIATE | The REFRESH IMMEDIATE option is now supported for summary tables. For more | information, please see section 16.2, "ALTER TABLE", and section | 16.5, "CREATE TABLE". 2.1.2 OPTIMIZING PERFORMANCE WHEN DATA IS PLACED ON RAID DEVICES This section describes how to optimize performance when data is placed on Redundant Array of Independent Disks (RAID) devices. In general, you should do the following for each table space that uses a RAID device: o Define a single container for the table space (using the RAID device). o Make the EXTENTSIZE of the table space equal to, or a multiple of, the RAID stripe size. o Ensure that the PREFETCHSIZE of the table space is: - the RAID stripe size multiplied by the number of RAID parallel devices (or a whole multiple of this product), and - a multiple of the EXTENTSIZE. o Use the DB2_PARALLEL_IO registry variable (described below) to enable parallel I/O for the table space o Use the DB2_STRIPED_CONTAINERS registry variable (described below) to ensure extent boundaries are aligned in the table space. DB2_PARALLEL_IO When reading data from, or writing data to table space containers, DB2 may use parallel I/O if the number of containers in the database is greater than 1. However, there are situations when it would be beneficial to have parallel I/O enabled for single container table spaces. For example, if the container is created on a single RAID device that is composed of more than one physical disk, you may want to issue parallel read and write calls. To force parallel I/O for a table space that has a single container, you can use the DB2_PARALLEL_IO registry variable. This variable can be set to "*" (asterisk), meaning every table space, or it can be set to a list of table space IDs separated by commas. For example: db2set DB2_PARALLEL_IO=* {turn parallel I/O on for all table spaces} db2set DB2_PARALLEL_IO=1,2,4,8 {turn parallel I/O on for table spaces 1, 2, 4, and 8} After setting the registry variable, DB2 must be stopped (db2stop), and then restarted (db2start), for the changes to take effect. DB2_STRIPED_CONTAINERS Currently when creating a DMS table space container (device or file), a one-page tag is stored at the beginning of the container. The remaining pages are available for data storage by DB2, and are grouped into extent-sized blocks. When using RAID devices for table space containers, it is suggested that the table space be created with an extent size that is equal to, or a multiple of, the RAID stripe size. However, because of the one-page container tag, the extents will not line up with the RAID stripes, and it may be necessary during an I/O request to access more physical disks than would be optimal. DMS table space containers can now be created in such a way that the tag exists in its own (full) extent. This avoids the problem described above, but it requires an extra extent of overhead within the container. To create containers in this fashion, you must set the DB2 registry variable DB2_STRIPED_CONTAINERS to "ON", and then stop and restart your instance: db2set DB2_STRIPED_CONTAINERS=ON db2stop db2start Any DMS container that is created (with CREATE TABLESPACE or ALTER TABLESPACE) will have new containers with tags taking up a full extent. Existing containers will remain unchanged. To stop creating containers with this attribute, reset the variable, and then stop and restart your instance: db2set DB2_STRIPED_CONTAINERS= db2stop db2start The Control Center and the LIST TABLESPACE CONTAINERS command will not show whether a container has been created as striped or not. They will continue to use "file" or "device", depending on how the container was created. To verify that a container was created as striped, you can use the /DTSF option of DB2DART to dump table space and container information, and look at the type field for the container in question. Also, the query container APIs, sqlbftcq( ) and sqlbtcq( ), can be used to create a simple application that will display the type. Definitions for these new types have been added to the sqlutil.h header file: #define SQLB_CONT_STRIPED_DISK 5 /* DMS: Striped disk */ #define SQLB_CONT_STRIPED_FILE 6 /* DMS: Striped file */ | 2.2 CONTROLLING DATABASE ACCESS | 2.2.1 DRDA-ONLY CLIENT AUTHENTICATION | In the section "Selecting an Authentication Method for Your Server", two new | paragraphs are added: | To protect against all clients except DRDA clients from DB2 for MVS and | OS/390, DB2 for VM and VSE, and DB2 for OS/400, set the "trust_allclnts" | parameter to DRDAONLY. Only these clients can be trusted to perform | client-side authentication. All other clients must provide a user ID and | password to be authenticated by the server. | The "trust_clntauth" parameter is used to determine where the above clients | are authenticated: if "trust_clntauth" is "client", authentication takes | place at the client. If "trust_clntauth" is "server", authentication takes | place at the client when no password is provided and at the server when a | password is provided. | 2.2.2 CHANGE PASSWORD ENABLEMENT FOR EEE | DB2 Administrators may now allow others to change passwords on AIX and | Windows NT EEE systems through the profile registry variable | "DB2CHGPWD_EEE=". | NOTE: This is not supported on Sun EEE systems. | The default for this variable is "NOT SET" (disabled). Other values for | "DB2CHGPWD_EEE" are the standard boolean values used by other DB2 profile | variables. | The DB2 Administrator is responsible for ensuring that the passwords for all | nodes are maintained centrally using either a Windows NT Domain Controller on | Windows NT, or NIS on AIX. | NOTE: If the passwords are not maintained centrally, enabling the | "DB2CHGPWD_EEE" variable will allow for the possibility that passwords | may not be consistent across all nodes. That is, if a user uses the | "change password" feature, then the user's password will only be | changed at the node to which they connect. | 2.2.3 ENCRYPTED PASSWORDS | A new "DBM CFG" (database manager configuration) value "SERVER_ENCRYPT" has | been added for the parameter "AUTHENTICATION". "SERVER_ENCRYPT" now has the | same rules as "SERVER" authentication, with the added feature that any | passwords sent over a network will be encrypted. Also, a new value | "DCS_ENCRYPT" has been added for DB2 Connect support. This value is | documented in the file READDCS.TXT, available with the following products: | IBM DB2 Connect Enterprise Edition, Version 5.2 | IBM DB2 Connect Personal Edition, Version 5.2 | The same parameter has been added to the "CATALOG DATABASE" authentication | clause as an option. | RESTRICTIONS | 1. In terms of database support, only DB2/390 and DB2 UDB accept an | encrypted password for this initial implementation. DB2 UDB support is | initially limited to the following platforms: | o Windows 95/98 | o Windows NT | o AIX | 2. No CCA/Discovery Support for the new ENCRYPT versions of authentication | is supported. | 3. No Support in the Control Center. | 4. No encryption of the new password for change password support. | 5. No DCE Directory support of the new authentication types SERVER_ENCRYPT | and DCS_ENCRYPT. | 6. No DRDA AS support for encryption. | 7. Any encryption which occurs will only be for the password - user ID and | the remainder of the flow remain unencrypted. | 2.2.4 LOGINRETRIES OPTION (AIX ONLY) | DB2 Universal Database on AIX has added the functionality to log failed | password attempts with the operating system and detect when a client has | exceeded the number of allowable login tries as specified by the | "loginretries" parameter. 2.3 AUDITING DB2 ACTIVITIES There is no audit support for summary tables, typed tables, or replicated tables. 2.4 UTILITIES FOR MOVING DATA 2.4.1 LOAD, IMPORT, AND EXPORT FILE FORMATS/PC/IXF FILE FORMATS/CODE PAGE CONSIDERATIONS If you want to load or import an IXF format file containing DBCS data, you must have the corresponding conversion files (located in sqllib\conv) installed on your client machine. The names of these conversion files contain both the source and the target code page numbers; the extension is always converting code page 932 to 943. If your client machine does not have the appropriate conversion files, you can copy them from a server machine to the sqllib\conv directory of the client machine. Be sure to copy the files from a compatible platform; for example, if the client is running on a UNIX based operating system, copy the files from a server that is also running on a UNIX based operating system. | 2.4.2 OVERVIEW OF THE LOAD PROCESS (PENDING STATES) | In the section "Overview of the LOAD Process", a revision to the paragraph | discussing pending states is made: | After the LOAD process completes, if: | o "logretain" or "userexit" is "on", COPY NO (which is the default) is | selected and NONRECOVERABLE is not selected; or, | o "logretain" or "userexit" is "off," COPY YES is selected and | NONRECOVERABLE is not selected | then any associated table spaces are placed in a BACKUP PENDING state. | In the section "LOAD Exception Table," two new paragraphs are added: | LOAD, when using the FOR EXCEPTION option, will check for rows that have | unique index violations. LOAD will not check for constraint or foreign key | violations other than for unique constraint violations. | A "unique key" is a key that is constrained so that no two of its values are | equal. The mechanism used to enforce the constraint is called a unique index. | A primary key is a special case of a unique key. A table cannot have more | than one primary key. 2.4.3 LOAD RESTRICTION ON WINDOWS NT AND OS/2 SMP-ENABLED MACHINES In DB2 UDB Version 5.2 for Windows NT and DB2 UDB Version 5.2 for OS/2, users operating in an SMP environment might occasionally experience a system hang during a load operation. This is a known problem that occurs only during index creation (the BUILD phase of the load utility) involving large volumes of data (on the order of several gigabytes). To avoid this problem, it is recommended that existing indexes on a loading table be dropped before issuing the LOAD command, and recreated using the CREATE INDEX statement after the load operation has completed successfully. An alternative that does not require the dropping of indexes is to perform the load operation into a staging table that does not have any indexes defined on it. The target table can subsequently be updated by issuing an INSERT statement encompassing all of the data from the staging table. | 2.4.4 ERROR SCENARIO FOR THE AUTOLOADER PROGRAM ON DB2 UDB ENTERPRISE - | EXTENDED EDITION FOR WINDOWS NT | The following applies to an error scenario for the autoloader program on IBM | DB2 Universal Database Enterprise - Extended Edition for Windows NT. It | explains the symptom and nature of the problem, and also a workaround. | When running the autoloader program, "db2atld.exe", on a multi-homed machine, | that is, a machine with multiple network cards installed, ensure that the | machine is configured correctly by trying the following operations: | o When you type the "hostname" command on the machine where the autoloader | is started, and also ping this host name from the same machine, the IP | address returned should be the same as the result when this host name is | pinged from another machine in your DB2 MPP node list. If the machine is | not configured correctly, you will see an "SQL6555N" error from the | autoloader program, and you will also see the error message, "errno = | 10061", i.e. connection refused, in the "db2diag.log" file(s) on some of | the loading nodes defined by the "OUTPUTNODES" parameter in your | autoloader configuration file. | On Windows NT machines, the IP address returned for a local host name is not | retrieved from the "DNS" or "hosts" file, but from information configured | locally in the Control Panel network icon. | There is a bug in Windows NT 4.0 where the binding order configured in the | Control Panel network icon does not influence the IP address order returned | on a multi-homed machine. See Microsoft Support Online article ID Q171320 to | resolve this problem. (When you ping a host name, it is the first IP address | that is returned.) | 2.4.5 CHANGES IN AUTOLOADER AUTHENTICATION BEHAVIOR | The concept of a local database connection has been extended for the MPP | environments to include connections from any node of a given MPP instance. | That means that even though the instance is configured using AUTHENTICATION | server, a password will no longer be required if a connection is being | attempted from one of the nodes defined in the "db2nodes.cfg" file. The | Autoloader will make use of this new connection behavior when the | AUTHENTICATION flag in the "autloader.cfg" file is not set or is set to NO, | and the password for autoloader is not defined. | With this new database connection behavior, the password is only mandatory | for autoloader execution if a password is required for remote execution of | programs in your system. For example, if the ".rhosts" file on a UNIX system | was not set up properly to enable "rsh" execution, then the password is | required. | 2.4.6 IMPORT/EXPORT OF WSF DATA CONTAINING BIGINT | When exporting "BIGINT" or "DECIMAL" columns to WSF (Work Sheet Format), only | values that fall in the range of type DOUBLE can be exported accurately. | Values that do not fall in this range will still be exported to the target | media. However, loading back these values (via "LOAD" or "IMPORT") may result | in incorrect data. The end result will vary by platform. 2.5 RECOVERING A DATABASE 2.5.1 BACKING UP AND RESTORING DATABASES ON DIFFERENT OPERATING SYSTEMS You cannot back up a database or table space on one operating system and restore it to another operating system by using the BACKUP DATABASE and RESTORE DATABASE commands. Instead, you move tables from one operating system to another by using the db2move command (or by exporting, then importing or loading the data). For more information, see "Moving Data Using the db2move Tool" in the Administration Guide. 2.5.2 CONSIDERATIONS FOR USING ADSTAR DISTRIBUTED STORAGE MANAGER (ADSM) The current ADSM client on the Windows operating system and OS/2 is non-reentrant, and so multiple sessions cannot be created with the backup, restore, or load utilities from a single machine. In a single node configuration, if a user attempts to issue a backup command such as: db2 backup db sample use adsm open 3 sessions DB2 will detect that multiple sessions are not supported by ADSM, and will return SQL2032N. The equivalent scenario also applies to load copies using ADSM. However, in an MLN configuration on Windows NT, DB2 may not be able to detect the use of multiple sessions on a single machine if each logical node attempts to create only one session. If multiple logical nodes are being backed up, restored, or loaded in parallel using ADSM, DB2 will allow the operation to proceed if each node attempts to use a single session, even though the logical nodes actually reside on the same physical hardware. This can lead to failed backup attempts, and hung load processes, and should not be attempted. | 2.5.3 CONFIGURATION PARAMETERS FOR DATABASE LOGGING | The following information should be added: | The total log file size limit is 4 GB. That is, the number of logfiles | (LOGPRIMARY + LOGSECOND) multiplied by the size of each logfile in bytes | (LOGFILSIZ * 4096) must be less than 4 GB. | 2.5.4 FREQUENCY OF BACKUPS AND TIME REQUIRED | In the section on "Frequency of Backups and Time Required," the following | information should be added: | If the amount of time needed to apply archived logs when recovering and | rolling forward a database is a major concern, then you will need to consider | the cost of having more frequent backups. If your database is very active, | more logging occurs and therefore more frequent database backups are | recommended. More frequent database backups reduces the number of archived | logs you need to apply when rolling forward through archived logs. 2.5.5 RECOVERING A DATABASE (PLANNING TO USE THE ROLLFORWARD COMMAND) You cannot roll forward a partitioned database from a Version 2 client. | 2.6 USING DB2 WITH AN XA-COMPLIANT TRANSACTION MANAGER | Within the sub-section 'Using the DB2 Universal Database XA Switch' under the | 'XA Function Supported' section in this chapter, the last line in "OS/2 | Platform" should be changed to: | With either method, you must link your application with db2api.lib. The last line in the "Windows NT Platform" section should be changed to: With any of these methods, ensure that you link with db2api.lib. | References to "db2app.lib" should be changed to "db2api.lib." | 2.7 APPLICATION CONSIDERATIONS | In the section on "Locking," just before the "Attributes of Locks" section, | the following paragraph should be added: | You may want to consider defining a monitor that will record when deadlocks | occur. Use the CREATE EVENT MONITOR statement described in the SQL Reference | to create the monitor. | In the section on "Using a select-statement," the OPTIMIZE FOR and FETCH | FIRST example should be modified to show the following: | SELECT EMPNAME, SALARY FROM EMPLOYEE | ORDER BY SALARY DESC | FETCH FIRST 100 ROWS ONLY | OPTIMIZE FOR 20 ROWS | The change is simply the reversal of the order of the last two lines of the | example. | 2.8 UNDERSTANDING THE SQL COMPILER | In the section on "Hash Join," the following information should be added: | To realize the performance benefits of hash join, it may be necessary to | change the value of the "sortheap" database configuration parameter, and the | "sheapthres" database manager configuration parameter. | For decision support queries, hash join access plans use more sort heap space | than do non-hash join plans. When "sheapthres" is set to be very close to | "sortheap" (that is, less than a factor of two or three per concurrent | query), a hash join runs with much less memory than the optimizer | anticipated. When executing with limited memory, hash joins can be very slow. | The problem occurs in queries with multiple sorts and hash joins, in which | the first sorts or hash joins acquire most of the available memory. | The solution is to configure "sheapthres" to be large enough (relative to | "sortheap"). 2.9 OPERATIONAL PERFORMANCE | 2.9.1 MANAGING THE DATABASE BUFFER POOL | The following information should be added: | The storage associated with the buffer pool is allocated when a database is | activated or when the first application connects to the database. | Applications are the primary beneficiaries of the buffer pool; once | applications are all disconnected, the storage associated with the buffer | pool is de-allocated. 2.9.2 MANAGING MULTIPLE DATABASE BUFFER POOLS When working on your database design, you may have determined that tables with 8KB page sizes were best. As a result, you should create a buffer pool with an 8KB page size (along with one or more table spaces with the same page size). If you have chosen to place your tables in an 8KB table space, it is recommended that your temporary table spaces have an 8KB page size as well, and they should be assigned to the same 8KB page size buffer pool. The default buffer pool (IBMDEFAULTBP) or any other 4KB buffer pool to which any 4KB temporary table spaces are assigned should be kept small, since the database manager will favor the 8KB temporary table spaces, and therefore the 8KB buffer pool. | 2.10 USING THE GOVERNOR | In the section on "The Governor Deamon" add the following information: | The note concerned with using the governor as an alternate means to adjust | agent priorities should have the following information added: "(This note | does not apply to OS/2 or Windows NT platforms.)" | In the section on the "Governor Configuration File", replace the first and | second paragraphs of the information under "action/priority" with the | following: | Specifies a change to the priority of agents working for the application. | Valid values are from -20 to +20. 2.11 CONFIGURING DB2 | 2.11.1 ENCRYPTED PASSWORDS | The AUTHENTICATION type (authentication) parameter is updated as follows: | A new value, SERVER_ENCRYPT, has been added to this parameter. This value has | the same rules as SERVER authentication, with the added feature that any | passwords sent over a network are encrypted. For restrictions on the use of | this value, see section 2.2.3, "Encrypted Passwords". | A new value, DCS_ENCRYPT, has been added to this parameter. This value has | been added for support of DB2 Connect. Details on this new value are | documented in the READDCS.TXT file for the following two products: | o IBM DB2 Connect Enterprise Edition, Version 5.2 | o IBM DB2 Connect Personal Edition, Version 5.2. 2.11.2 TUNING CONFIGURATION PARAMETERS/OPTIMIZING HASH JOIN PERFORMANCE To realize the performance benefits of hash join, it may be necessary to change the value of the SORTHEAP database configuration parameter, and the SHEAPTHRES database manager configuration parameter. Hash join plans use more sort heap space than other join plans. When SHEAPTHRES is set to be very close to SORTHEAP (less than a factor of 2 or 3 per concurrent query), hash join runs with much less memory than the optimizer anticipated. When executing with limited memory, hash join can be very slow. The problem occurs in queries with multiple sorts and hash joins, in which the first sorts or hash joins acquire most of the available memory. The solution is to configure SHEAPTHRES to be large enough (relative to SORTHEAP). For more information about hash join, including how to determine whether a hash join strategy has been selected, see the description of db2expln in the SQL Reference, and the What's New book (Appendix A - System Monitor Guide and Reference Updates). See also the description of the new hash join registry variable (DB2_HASH_JOIN) below. | 2.11.3 DATABASE LOG FILES | The "Size of Log Files (logfilsz)" parameter is updated as follows: | The total log file size limit is 4 GB. That is, the number of logfiles | (LOGPRIMARY + LOGSECOND) multiplied by the size of each logfile in bytes | (LOGFILSIZ * 4096) must be less than 4 GB. | 2.11.4 TRUSTED CLIENTS AND DRDA USE | The section of the chapter dealing with "Instance Administration," the "Trust | All Clients (trust_allclnts)" parameter is updated as follows: | The Range of values for the parameter now includes DRDAONLY in addition to NO | and YES. | The description for the parameter now includes the following two paragraphs: | Setting the "trust_allclnts" parameter to DRDAONLY protects against all | clients except DRDA clients from DB2 for MVS and OS/390, DB2 for VM and VSE, | and DB2 for OS/400. Only these clients can be trusted to perform client-side | authentication. All other clients must provide a user ID and password to be | authenticated by the server. | The "trust_clntauth" parameter is used to determine where the above clients | are authenticated: if "trust_clntauth" is "client", authentication takes | place at the client. If "trust_clntauth" is "server", authentication takes | place at the client when no password is provided and at the server when a | password is provided. | 2.11.5 LOGINRETRIES OPTION (AIX ONLY) | DB2 Universal Database on AIX has added the functionality to log failed | password attempts with the operating system and detect when a client has | exceeded the number of allowable login tries as specified by the | "loginretries" parameter. 2.11.6 ASLHEAPSZ AND FAILED QUERIES AGAINST LARGE TABLES DB2 can fail while fetching query results from large tables (that is, tables with greater than a 4005-character record length and more than 500 columns). This can be overcome by increasing the value of the ASLHEAPSZ database manager configuration parameter from the default value of 15. 2.11.7 INTRA_PARALLEL SETTING ON AN SMP MACHINE (WINDOWS NT) On Windows NT, on an SMP machine, the INTRA_PARALLEL database manager configuration parameter will be set to YES when an instance is created with DB2 Version 5.2. With Version 5.0, it would have been set to NO. | 2.12 HACMP ES FOR AIX | The section "Cluster Configuration," has an example showing how to create | containers for a two-node system that is updated as follows: | CREATE TABLESPACE TS3 MANAGED BY SYSTEM USING | ('/TS/cont $N%2, '/TS3/cont $N%2+2') | The following containers would be used: | /TS3/cont0 - on Node 0 | /TS3/cont2 - on Node 0 | /TS3/cont1 - on Node 1 | /TS3/cont3 - on Node 1 | The change was made to be consistent with the 'TS3' subdirectory. | 2.13 DB2 REGISTRY AND ENVIRONMENT VARIABLES | Parameter Operating Values Description | System | ------------------------------------------------------------------------------------- | DB2CHGPWD_EEE AIX and Default=NULL DB2 Administrators can allow non- | Windows NT Values: YES or NO administrators to change passwords. | (not supported The administrator is responsible for | on Sun EEE) ensuring that the passwords for all | database partitions (nodes) are | maintained centrally using either | a Windows NT Domain Controller on | Windows NT, or NIS on AIX. If not | maintained centrally, passwords may | not be consistent across all database | partitions. This could result in a | password only being changed at the | database partition to which the user | connects to make the change. | Parameter Operating Values Description | System | ------------------------------------------------------------------------------------- | DB2_GRP_LOOKUP Windows NT DEFAULT=null Specifies which Windows NT | Values: local, domain security mechanism will be | used to enumerate the groups | that a user belongs to. | NOTE: For FixPak 7, the "db2_grp_lookup" registry variable has been enhanced | to support the keyword "domain", in addition to the currently | supported keyword "local". | db2set db2_grp_lookup=domain | should be set under the following conditions: | 1. Your machine is not a domain-controller. | 2. Your machine is in a domain other than the domain where the domain | accounts reside. This occurs using the Master Account Domain model | where accounts are defined in a domain trusted by the resource | domains. | 3. You have backup domain controllers for the accounts domain. | This parameter applies to both client and server configurations. | Setting this parameter will tell DB2 to use a domain controller in the | current domain to locate a controller for the accounts domain. | PROBLEM BEING SOLVED: | In order to enumerate groups (and to find out whether you are an | administrator), DB2 uses an NT API to find a domain controller for the | domain in which the account is defined. It uses an API that tries to | find the Primary Domain Controller (PDC) and, failing that, a backup | domain controller. If the machine that is running this API is also a | domain controller, this will always work. If your machine is not a | domain controller, then this methodology will fail when the PDC is | down. When "db2_grp_lookup=domain", DB2 will find a domain controller | in YOUR domain with which to run the API to determine the domain | controller for the accounts domain. This will not fail when the PDC is | down. Parameter Operating Values Description System ------------------------------------------------------------------------------------ DB2_HASH_JOIN All DEFAULT=NO Specifies hash join as Values: YES or NO a possible join method when compiling an access plan. | Parameter Operating Values Description | System | ------------------------------------------------------------------------------------ | DB2_NEW_CORR_SQ_FF All DEFAULT=OFF Affects the selectivity value | Values: ON or OFF computed by the SQL optimizer | for certain subquery predicates | when it is set to ON. It can be | used to improve the accuracy of | the selectivity value of equality | subquery predicates that use the | MIN or MAX aggregate function in | the select list of the subquery. | For example: | SELECT * FROM T WHERE T.COL = | (SELECT MIN(T.COL) FROM T WHERE ...) | Parameter Operating Values Description | System | ------------------------------------------------------------------------------------- | DB2MEMDISCLAIM AIX DEFAULT=(not set) Depending on the workload being executed | Values: YES or NO and the pool agents configuration, you | may run into a situation where the | committed memory for each DB2 agent will | stay above 32MB even when the agent is | idle. This behavior is expected and | usually results in good performance as | the memory is kept for fast re-use. | However, on a memory constrained system, | this may not be a desirable side effect. | To avoid this condition issue the | following: | db2set DB2MEMDISCLAIM=yes | Disclaiming memory tells the AIX operating | system to stop paging the area so that it | no longer occupies any real storage. | Setting DB2MEMDISCLAIM to YES tells DB2 | UDB to disclaim some or all memory once | freed, depending on DB2MEMMAXFREE. This | will ensure that the memory is made | readily available for other processes as | soon as it is freed. See also | DB2MEMMAXFREE. | Parameter Operating Values Description | System | ------------------------------------------------------------------------------------- | DB2MEMMAXFREE AIX DEFAULT=(not set) Specifies the amount of free memory that | Values: 4000000 is retained by each DB2 agent. You may | to 256000000 set this variable to a value between 4 | and 256 MB. We recommend that if you use | this feature, you specify a value of 8MB: | db2set DB2MEMMAXFREE=8000000 | See also DB2MEMDISCLAIM. | There is a new registry variable to tune bufferpools. This is how you use it: | 1. Create a file | 2. Set the file as follows: | db2set DB2BPVARS= | For example, the file "f:\BPVARSFILE" has the following content: ----------------------------------------------------------------------------- | # turn on scatter read for raw DMS devices | NT_SCATTER_DMSDEVICE=1 ----------------------------------------------------------------------------- | You would do the following using this file: | db2set DB2BPVARS=f:\BPVARSFILE | The currently supported parameter is: | Parameter Operating Values Description | System | ------------------------------------------------------------------------------------- | NT_SCATTER_DMSDEVICE NT DEFAULT=0(OFF) Can be used to turn scatter | Values=0(OFF) read on for DMS device | or 1(ON) containers. This can only | be enabled (turned ON) if | DB2NTNOCACHE is set to ON in | the registry. A warning message | will be written to the | db2diag.log if DB2NTNOCACHE | is OFF (or not set), and | scatter read will remain | disabled. It is recommended for | systems with a large amount of | sequential prefetching against | RAW containers. 2.14 ADDITIONAL RAH (RUN ALL HOSTS) INFORMATION (SOLARIS AND AIX ONLY) The following is to be added to the rah information in Appendix Q. To enhance performance, rah has been extended to use tree_logic on large systems. That is, rah will check how many nodes the list contains, and if that number exceeds a threshold value, it constructs a subset of the list and sends a recursive invocation of itself to those nodes. At those nodes, the recursively invoked rah follows the same logic until the list is small enough to follow the standard logic (now the "leaf-of-tree" logic) of sending the command to all nodes on the list. The threshold can be specified by environment variable RAHTREETHRESH, or defaults to 15. In the case of a multiple-logical-node-per-physical-node system, db2_all will favor sending the recursive invocation to distinct physical nodes, which will then rsh to other logical nodes on the same physical node, thus also reducing inter-physical-node traffic. (This point applies only to db2_all, not rah, since rah always sends only to distinct physical nodes.) This version of rah has nearly identical syntax and semantics as the old version (supplied in the product as rah.sh_old), except for some minor restrictions on what options can be used: o The user must use ksh (kornshell) as the shell. If the user is using a different shell, rah_tree will issue a warning and use non-tree logic. o The single quotation mark (') prefix character, requesting echo of the command, cannot be honored, and is ignored. o When both the < (all-but-me) and > (substitute <> by host index) options are specified, the host index is different from what non-tree rah/db2_all would have substituted. NOTE: The () and ## substitutions should work identically to the old rah/db2_all. o It is strongly recommended that the user ID setup at all nodes in the list be identical; for example, the current working directory from which the rah_tree command or the db2_tree command is issued should exist on all nodes; the rah_tree executable must be found in the current $PATH on all nodes (the one in effect when the rah_tree command is issued); the rahwaitfor executable must be found in that path, and so on. Certain environment differences between nodes can be tolerated, but many can not. o The command to be executed must not start with the characters "-o, -b, -d, or -x", because rah_tree will interpret these to be flags. o When specifying parallel execution, the order in which hosts return their output is likely to be different from the order generated by non-tree rah, which tends to return output in list order. o Whenever rah_tree or db2_tree is invoked, it compares the number of destination nodes with a threshold value, as described above. The threshold can be specified by environment variable: export RAHTREETHRESH=nn where nn can be any positive integer or defaults to 15. | 2.15 UNICODE/UCS-2 AND UTF-8 SUPPORT IN DB2 UDB | These two standards are documented here. | 2.15.1 INTRODUCTION | The Unicode character encoding standard is a fixed-length, character encoding | scheme that includes characters from almost all the living languages of the | world. Unicode characters are usually shown as "U+xxxx" where xxxx is the | hexadecimal code of the character. | Each character is 16 bits (2 bytes) wide, regardless of the language. While | the resulting 65 536 code elements are sufficient for encoding most of the | characters of the major languages of the world, the Unicode standard also | provides an extension mechanism, that allows for encoding as many as a | million more characters. This extension reserves a range of code values | (U+D800 to U+D8FF, known as "surrogates") for encoding some 32-bit characters | as two successive code elements. | The International Organization for Standardization (ISO) and International | Electrotechnical Commission (IEC) 10646 standard (ISO/IEC 10646) specifies | the Universal Multiple-Octet Coded Character Set (UCS) that has a 2-byte | version (UCS-2) and a 4-byte version (UCS-4). The 2-byte version of this ISO | standard UCS-2 is identical to Unicode without surrogates. ISO 10646 also | defines an extension technique, for encoding some UCS-4 codes in a UCS-2 | encoded string. This extension called UTF-16, is identical to Unicode with | surrogates. | DB2 UDB supports UCS-2, that is, Unicode without surrogates. | 2.15.1.1 UTF-8 | With UCS-2 or Unicode encoding, ASCII and control characters are also two | bytes long, and the lead byte is zero. For example, NULL is U+0000 and | CAPITAL LETTER A is represented by U+0041. This could be a major problem for | ASCII-based applications and ASCII file systems, because in a UCS-2 strings, | extraneous NULLs may appear anywhere in the string. A transformation | algorithm, known as UTF-8, can be used to circumvent this problem for | programs that rely on ASCII code being invariant. | UTF-8 (UCS Transformation Format 8), is an algorithmic transformation which | transforms fixed-length UCS-2 and UCS-4 characters into variable-length byte | strings. In UTF-8, ASCII characters are represented by their usual | single-byte codes, but non-ASCII characters in UCS-2 become two or three | bytes long. In other words, UTF-8 transforms UCS-2 characters to a multi-byte | codeset, for which ASCII is invariant. The number of bytes for each UCS-2 | character in UTF-8 format can be determined from the following table: | UCS-2 (hex) UTF-8 (binary) Description | ------------ -------------------------- ---------------- | 0000 to 007F 0xxxxxxx ASCII | 0080 to 07FF 110xxxxx 10xxxxxx up to U+07FF | 0800 to FFFF 1110xxxx 10xxxxxx 10xxxxxx other UCS-2 | NOTE: The range D800 to DFFF is to be excluded from treatment | by the third row of this table which corresponds to the | UCS-4 range 0000 0800 to 0000 FFFF. | In all the above, a series of x's indicate the UCS bit representation of the | character. For example, U0080 transforms into 11000010 10000000. | 2.15.2 UCS-2/UTF-8 IMPLEMENTATION IN DB2 UDB | 2.15.2.1 Code Page/CCSID Numbers | Within IBM, the UCS-2 code page has been registered as code page 1200. All | code pages are defined with growing character sets, that is, when new | characters are added to a code page, the code page number does not change. | Code page 1200 always refers to the current version of Unicode/UCS-2, and has | been used for UCS-2 support in DB2 UDB. | A specific repertoire of the UCS standard, as defined by Unicode 2.0 and | ISO/IEC 10646-1, has also been registered within IBM as CCSID 13488. This | CCSID (13488) has been used internally by DB2 UDB for storing graphic string | data in euc-Japan and euc-Taiwan databases. CCSID 13488 and code page 1200 | both refer to UCS-2, and are handled the same way except for the value of | their "double-byte" (DBCS) space: | CP/CCSID Single Byte (SBCS) space Double Byte (DBCS) space | --------- ------------------------ ------------------------ | 1200 N/A U+0020 | 13488 N/A U+3000 | NOTE: In a UCS-2 database, U+3000 has no special meaning. | Regarding the conversion tables, since code page 1200 is a superset of CCSID | 13488, the exact same (superset) tables are used for both. | In IBM, UTF-8 has been registered as CCSID 1208 with growing character set | (sometimes also referred to as code page 1208). As new characters are added | to the standard, this number (1208) will not change either. 1208 is used as | the multi-byte code page number for DB2's UCS-2/UTF-8 support. | DB2 UDB supports UCS-2 as a new multi-byte code page. The MBCS code page | number is 1208, which is the database code page number, and the code page of | character string data within the database. The double-byte code page number | (for UCS-2) is 1200 which is the code page of graphic string data within the | database. When a database is created in UCS-2/UTF-8, CHAR, VARCHAR, LONG | VARCHAR, and CLOB data, are stored in UTF-8, and GRAPHIC, VARGRAPHIC, LONG | VARGRAPHIC, and DBCLOB data, are stored in UCS-2. We will simply refer to | this as a UCS-2 database. | 2.15.2.2 Creating a UCS-2 Database | By default, databases are created in the code page of the application | creating them. Therefore, if you create your database from a UTF-8 client | (for example, the UNIVERSAL locale of AIX), or if DB2CODEPAGE environment | variable on the client is set to 1208, your database will be created as a | UCS-2 database. Alternatively, you can explicitly specify "UTF-8" as the | CODESET name, and use any valid two letter TERRITORY code supported by DB2 | UDB. Refer to the Administration Guide, and the appendix on "NLS Support" for | a complete list of territory codes supported by DB2 UDB. | For example, to create a UCS-2 database from the CLP, with the territory code | for United States, issue: | DB2 CREATE DATABASE dbname USING CODESET UTF-8 TERRITORY US | The default collation sequence for a UCS-2 database is IDENTITY, which | provides UCS-2 code point order. Therefore, by default, all UCS-2/UTF-8 | characters are ordered and compared according to their UCS-2 code point | sequence. | All cultural-sensitive parameters such as date/time format, decimal | separator, and others, are based on the current territory of the client. | A UCS-2 database allows connection from every single-byte and multi-byte code | page supported by DB2 UDB. Code page character conversions between client's | code page and UTF-8 are automatically performed by the database manager. Data | in graphic string types, is always in UCS-2 and does not go through code page | conversions. The Command Line Processor (CLP) environment is an exception. If | you SELECT graphic string (UCS-2) data from the CLP, the returned graphic | string data is converted (by the CLP) from UCS-2 to the code page of your | client environment. | Every client is limited by the character repertoire, the input methods, and | the fonts supported by its environment, but the UCS-2 database itself accepts | and stores all UCS-2 characters. Therefore, every client usually works with a | subset of UCS-2 characters, but the database manager allows the entire | repertoire of UCS-2 characters. | When characters are converted from a local code page to UTF-8, there is a | possibility of expansion in the number of bytes. There is no expansion for | ASCII characters, but other UCS-2 characters expand by a factor of two or | three. The number of bytes of each UCS-2 character in UTF-8 format can be | determined from the above table (section about UTF-8). | 2.15.2.3 Data Types | All data types supported by DB2 UDB, are also supported in a UCS-2 database. | In particular, graphic string data, is supported for UCS-2 database and is | stored in UCS-2/Unicode. Every client, including SBCS clients, can work with | graphic string data types in UCS-2/Unicode when connected to a UCS-2 | database. | A UCS-2 database is like any MBCS database where character string data is | measured in number of bytes. When working with character string data in | UTF-8, one should not assume that each character is one byte. In multi-byte | UTF-8 encoding, each ASCII character is one byte, but non-ASCII characters | take two or three bytes each. This should be taken into account when defining | CHAR fields. Depending on the ratio of ASCII to non-ASCII characters, a CHAR | field of size n bytes, can contain anywhere from n/3 to n characters. | Using character string UTF-8 encoding versus graphic string UCS-2 data type | also has an impact on the total storage requirements. For a situation where | the majority of characters are ASCII, with some non-ASCII characters in | between, storing UTF-8 data may be a better alternative because the storage | requirements are closer to one byte per character. On the other hand, for | situations where the majority of characters are non-ASCII characters that | expand to three-byte UTF-8 sequences (for example, ideographic characters), | the UCS-2 graphic-string format may be a better alternative because every | UCS-2 character requires exactly two bytes, rather than three bytes for each | corresponding character in UTF-8 format. | SQL scalar functions that operate on character strings, such as LENGTH, | SUBSTR, POSSTR, MAX, MIN, and the like, in MBCS environments operate on | number of "bytes" rather than number of "characters." The behaviour is the | same in a UCS-2 database. For example, the LENGTH of a character string | field, is the length in the number of bytes not the number of characters. For | a description of the behaviour of these functions, refer to the DB2 UDB SQL | Reference. | SQL CHAR data types are supported by C language's "char" data type in user | programs. SQL GRAPHIC data types are supported by "sqldbchar" in user C | programs. Note that, for a UCS-2 database, "sqldbchar" data is always in | big-endian (high byte first) format. When an application program is | connected to a UCS-2 database, character string data is converted between the | application code page and UTF-8 by DB2 UDB, but graphic string data is always | in UCS-2. | 2.15.2.4 Identifiers | In a UCS-2 database, all identifiers are in multi-byte UTF-8. Therefore, it | is possible to use any UCS-2 character in identifiers where the use of a | character in the extended character set (for example, an accented character, | or a multi-byte character) is allowed by DB2 UDB. Please refer to the | appendix "Naming Rules" in the Administration Guide for details of which | identifiers allow use of extended characters. | Clients can enter any character which is supported by their SBCS/MBCS | environment, and all the characters in the identifiers will be converted to | UTF-8 by the database manager. Two points need to be taken into account when | specifying National Language characters in identifiers in a UCS-2 database: | 1. Each non-ASCII character takes two or three bytes. Therefore, an n-byte | identifier, can only hold somewhere between n/3 and n characters, | depending the ratio of ASCII to non-ASCII characters. If you have only | one or two non-ASCII (for example, accented) characters, the limit is | closer to n characters, while for an identifier which is completely | non-ASCII (for example, in Japanese), only n/3 characters can be used. | 2. If identifiers are to be entered from different client environments, they | should be defined using the common subset of characters available to | those clients. For example, if a UCS-2 database is to be accessed from | Latin-1, Arabic, and Japanese environments, all identifiers should | realistically be limited to ASCII. | 2.15.2.5 UCS-2 Literals | UCS-2 literals can be specified in two ways: | 1. As a GRAPHIC string constant using the G'...', or N'....' format as | described in the SQL Reference, Chapter 3 "Language Elements," the | section "Constants," the subsection "Graphic String Constants." Any | literal specified in this way will be converted by the database manager | from the application code page to UCS-2. | 2. As a UCS-2 hexadecimal string, using the UX'....' or GX'....' format. The | constant specified between quotes after UX or GX must be a multiple of 4 | hexadecimal digits. Each four digits represent one UCS-2 code point. | When using the Command Line Processor (CLP), the first method is easier if | the UCS-2 character exists in the local application code page (for example, | for entering any code page 850 character from a terminal that is using code | page 850). The second method should be used for characters which are outside | the application code page repertoire (for example, for specifying Japanese | characters from a terminal that is using code page 850). | 2.15.2.6 Pattern Matching in a UCS-2 Database | Pattern matching is one area where the behaviour of existing MBCS databases | is slightly different from the behaviour of a UCS-2 database. | For MBCS databases in DB2 UDB, the current behaviour is as follows: If the | match-expression contains MBCS data, the pattern can include both SBCS and | MBCS characters. The special characters in the pattern are interpreted as | follows: | o An SBCS underscore refers to one SBCS character. | o A DBCS underscore refers to one MBCS character. | o A percent (either SBCS or DBCS) refers to a string of zero or more SBCS | or MBCS characters. | If the match-expression contains graphic string DBCS data, the expressions | contain only DBCS characters. The special characters in the pattern are | interpreted as follows: | o A DBCS underscore refers to one DBCS character. | o A DBCS percent refers to a string of zero or more DBCS characters. | In a UCS-2 database, there is really no distinction between "single-byte" and | "double-byte" characters; every UCS-2 character occupies two bytes. Although | the UTF-8 format is a "mixed-byte" encoding of UCS-2 characters, there is no | real distinction between SBCS and MBCS characters in UTF-8. Every character | is a UCS-2 character, irrespective of its number of bytes in UTF-8 format. | When specifying a character string, or a graphic string expression, an | underscore refers to one UCS-2 characters and a percent refers to a string of | zero or more UCS-2 characters. | On the client side, the character string expressions are in the code page of | the client, and will be converted to UTF-8 by the database manager. SBCS | client code pages, do not have any DBCS percent or DBCS underscore, but every | supported code page contains a single-byte percent (corresponding to U+0025) | and a single-byte underscore (corresponding to U+005F). The interpretation of | special characters for a UCS-2 database is as follows: | o An SBCS underscore (corresponding to U+0025) refers to one UCS-2 | character in a graphic string expression, or to one UTF-8 character in a | character string expression. | o An SBCS percent (corresponding to U+005F) refers to a string of zero or | more UCS-2 characters in a graphic string expression, or to a string of | zero or more UTF-8 characters in a character string expression. | DBCS code pages, additionally support a DBCS percent sign (corresponding to | U+FF05) and a DBCS underscore (corresponding to U+FF3F). These characters | have no special meaning for a UCS-2 database. | For the optional "escape-expression" which specifies a character to be used | to modify the special meaning of the underscore and percent characters, only | ASCII characters, or characters that expand into a two-byte UTF-8 sequence, | are supported. If you specify an ESCAPE character which expands to a | three-byte UTF-8 value, you will get an error message (SQL0130N error, | SQLSTATE 22019). | 2.15.2.7 IMPORT/EXPORT/LOAD Considerations | The DEL, ASC, and PC/IXF file formats are supported for a UCS-2 database as | described in this section. The WSF format is not supported. Also, autoloader | is not supported for a UCS-2 database at the present time. | When exporting from a UCS-2 database to an ASCII delimited (DEL) file, all | character data is converted to the application code page. Both character | string and GRAPHIC string data are converted to the same SBCS or MBCS code | page of the client. This is the existing behaviour for the export of any | database, and cannot be changed because the entire ASCII delimited file can | have only one code page. Therefore, if you export to an ASCII delimited file, | only those UCS-2 characters that exist in your application code page would be | saved. Other characters are replaced with the default substitution character | for the application code page. For UTF-8 clients (code page 1208) there is no | data loss because all UCS-2 characters are supported by UTF-8 clients. | When importing from an ASCII file (DEL or ASC) to a UCS-2 database, character | string data is converted from the application code page to UTF-8, and GRAPHIC | string data is converted from the application code page to UCS-2. There is | no data loss. If you want to import ASCII data that has been saved under a | different code page, you should switch the data file code page before issuing | the import command. One way to accomplish this, is to set DB2CODEPAGE to the | code page of the ASCII data file. | The range of valid ASCII delimiters, for SBCS and MBCS clients is identical | to what is currently supported by DB2 UDB for these clients. The range of | valid delimiters for UTF-8 clients is 0x01 to 0x7F, with the usual | restrictions. Refer to the "IMPORT/EXPORT/LOAD Utility File Formats" appendix | in the Command Reference for a complete list of these restrictions. | When exporting from a UCS-2 database to a PC/IXF file, character string data | is converted to the SBCS/MBCS code page of the client. GRAPHIC string data is | not converted and is stored in UCS-2 (code page 1200). There is no data loss. | When importing from an PC/IXF file to a UCS-2 database, character string data | is assumed to be in the SBCS/MBCS code page stored in the PC/IXF header and | GRAPHIC string data is assumed to be in the DBCS code page stored in the | PC/IXF header. Character string data is converted by the IMPORT utility from | the code page specified in the PC/IXF header to the code page of the client, | and then from the client code page to UTF-8 (by the INSERT statement). | GRAPHIC string data is converted by the IMPORT utility from the DBCS code | page specified in the PC/IXF header directly to UCS-2 (code page 1200). | LOAD directly places the data into the database and by default, assumes data | in ASC or DEL files is in the code page of the database. Therefore, by | default no code page conversion takes place for ASCII files. When the code | page of the data file has been explicitly specified (using the MODIFIED BY | codepage=x command parameter), LOAD uses this information to convert from the | specified code page x into the database code page before loading the data. | For PC/IXF files, LOAD always converts from the code pages specified in the | IXF header to the database code page (1208 for CHAR, 1200 for GRAPHIC). | The code page of DBCLOB files (as specified using the MODIFIED BY lobsinfile | command parameter) is always 1200 for UCS-2. The code page of the CLOB files | is the same as the code page of the data files being imported, loaded or | exported. For example, for load or import using PC/IXF format, the CLOB file | is assumed to be in the code page specified by the PC/IXF header. If the | DBCLOB file is in ASC or DEL format, for LOAD the CLOB data is assumed to be | in the code page of the database (unless explicitly specified otherwise using | the MODIFIED BY codepage=x command parameter), and for IMPORT it is assumed | to be in the client application code page. | The NOCHECKLENGTHS option is always set to TRUE for a UCS-2 database because | in a UCS-2 database, any SBCS can be connected to the database for which | there is no DBCS code pages; and also because character strings in UTF-8 | format usually have different lengths than corresponding lengths in client | code pages. | 2.15.2.8 Incompatibilities | For an application connected to a UCS-2 database, the graphic string data is | always in UCS-2 (code page 1200). For applications connected to non UCS-2 | databases, the graphic string data is in the applications DBCS code page; or, | not allowed if the application code page is SBCS. For example, when a 932 | client is connected to a Japanese non UCS-2 database, then the graphic string | data is in code page 301. But for the 932 client applications connected to a | UCS-2 database, the graphic string data is in UCS-2. | 2.16 USING VIRTUAL INTERFACE (VI) ARCHITECTURE | DB2 UDB now supports two implementations of the VI architecture: One from | GigaNet and the other from ServerNet. Each is presented in this section. | 2.16.1 OVERVIEW OF VIRTUAL INTERFACE ARCHITECTURE | Virtual Interface (VI) Architecture is the inter-node communication protocol | alternative to TCP/IP in a Windows NT massively parallel processing (MPP) | environment. VI is a new communication architecture that was developed | jointly by Intel, Microsoft, and Compaq to improve performance over a System | Area Network (SAN). For more information on the architecture, visit: | http://www.viarch.org | There are some similarities between the Public Interconnect which uses as an | example Ethernet and TCP/IP and the Private Interconnect which uses a Network | Interface Card and a protocol. The Network Interface Card and protocol used | in this instance is a GigaNet Network Interface Card and the VI protocol. | VI Architecture has low latency and high bandwidth. In a | communication-intensive environment, using VI Architecture improves the | overall system throughput. The greater the number of nodes in the cluster, | and the greater the amount of data transfered, the greater the benefit from | using VI Architecture. | DB2 UDB supports VI Architecture implementations that comply with the Virtual | Interface Architecture Specification, Version 1.0; the Intel Virtual | Interface (VI) Architecture Developers' Guide, Version 1.0; and pass the | "Virtual Interface Architecture Conformance Suite." The specification is | found at: | http://www.intel.com/design/servers/vi/the_spec/specification.htm | The Developer's Guide and information on the conformance suite is found at: | http://www.intel.com/design/servers/vi/developer/ia_imp_guide.htm | 2.16.2 INSTALL DB2 UNIVERSAL DATABASE VERSION 5.2 (EEE) | Detailed installation information is found in DB2 Enterprise - Extended | Edition for Windows NT Quick Beginnings. | If you are not sure of the service level of the DB2 UDB product you have | already installed, you can determine the UDB service level by going into the | registry: | 1. Enter regedt32 from a command prompt to edit the registry | 2. Choose HKEY_LOCAL_MACHINE | 3. Choose SOFTWARE | 4. Choose IBM | 5. Choose DB2 | 6. Choose DB2 UDB Enterprise - Extended Edition | 7. Choose CurrentVersion | 8. Record the Service Level | If you contact DB2 Service and Support about VI, the service level will be | helpful to determine your installed DB2 level of code including any FixPaks. | This product must be installed in each of the partitions/nodes using the | Virtual Interface Protocol. During the installation procedure, when prompted | choose "This machine will be an instance owning node" on each of the | partitions/nodes. | Update the hosts file with the IP address and host name for each of the | partitions/nodes. The hosts file is found under the | "\winnt\system32\drivers\etc\" directory on the drive where the operating | system was installed. The hosts file must be updated on each of the nodes. | Create the partitioned database (MPP) instance using the instance create | utility. Choose one machine to act as the co-ordinator node. On this machine, | open a DB2 Command Window and enter: | db2icrt /mpp /u:, | This machine is then known as the co-ordinator node or the instance-owning | machine. Node 0 is automatically created on this machine. | On the other partitions/nodes in the database, open a DB2 Command Window and | enter: | db2ncrt /n: /u:, | /i: /o: | The node_number is used to uniquely identify the database partition server | within the database environment. The number must be from 1 to 999. The | instance_owner_name is the computer name of the instance-owning machine | (co-ordinator node). | Testing the installation and create an index: | 1. Open a DB2 Command Window. | 2. Enter "set DB2INSTANCE=" | 3. Ensure the database manager starts on all nodes by entering: "db2start" | 4. Create a sample database by entering: "db2sampl" | 5. Connect to the sample database by entering: "db2 connect to sample" | 6. Try a few SELECT statements with the sample database. | When problems occur in this environment, you can take action based on the | type of problem as presented below: | o Instance creation fails. | Ensure "c:\profiles" is present and is present with each of the share | name "profiles." Ensure all partitions are "pingable" from the | co-ordinator node. | o DB2START fails. | Review the explanation for the returned error code by using the "db2 ? | sqlxxxx" command. There will be a suggested action associated with this | error which you should follow. | A system error may be returned. If this is the case, use a "db2stop" and | retry the "db2start". If the problem persists, attempt to reboot on all | partitions and then retry. | Ensure all partitions have the same date, time, and time zone. The time | does not need to be identical: within one hour is sufficient. | Ensure all the partitions are in one domain and that the user name and | password used belong to the following groups: | - On the domain controller: | - Administrators | - Domain Administrators | - Domain Users | - Users | - On other machines: | - Administrators | - Users | Review the contents of the "Control panel-->Services" to ensure that all | the "DB2: -X" services have the correct DB2ADMIN account | information. | o Command line variable has not been initialized: | Ensure you are running the command in a "DB2 command window." The title | of this window is "DB2 CLP." | o The "rah" command returns immediately without executing the commands | specified: | Run "db2set -g DB2TEMPDIR=C:\TMP" on all machines in the instance. Ensure | the DB2 Remote Command Service is started and with the correct DB2ADMIN | account information. Finally, ensure "c:\temp" and "c:\tmp" are present. | 2.16.3 RUNNING DB2 UDB V5.2 FOR WINDOWS NT WITH GIGANET INTERCONNECT | IBM announced support for Virtual Interface (VI) Architecture with DB2 UDB | EEE V5.2. The documentation provided no specifics regarding the VI products | that have been tested with DB2. This document provides specific information | for GigaNet Interconnect. | To find out about other products adhering to VI Architecture and supported by | DB2 UDB EEE V5.2, please contact the DB2 UDB support organization at | http://www.software.ibm.com/data or call 1-800-237-5511 (only in the U.S.A). | To find out about GigaNet products, or to contact GigaNet Service and | Support, please use the following URL: http://www.giganet.com/ | 2.16.3.1 Setup Procedure for GigaNet Interconnect | The list of the hardware and software required to setup this environment | include the following products: | o GigaNet GNN1000 Network Interface Card | o GigaNet GNX5000 Switch | o GigaNet GNCxx11 Copper Interconnect Cables | o GigaNet cLAN Software, Version 2.0. | The steps required to ensure that GigaNet Interconnect can work with DB2 UDB | are shown below. Each step is a summary of what is required at each step: all | of the details associated with each step are not presented here. You should | also use the referenced documenation at each step which does provide detailed | instructions and direction needed. | Each GigaNet GNN1000 is packaged with a GigaNet cLAN Software CD-ROM. The | CD-ROM contains all of the necessary software to set-up the GigaNet | Interconnect. In addition, the CD-ROM also contains the VI Architecture SDK | and Adobe Acrobat Reader. These two items are only needed by those | individuals that are developing VI-enabled applications. | Summary of steps: | 1. Install Adapter Cards | 2. Install Switches and Cables | 3. Install Adapter Drivers | 4. Install cLAN Management Console | 5. Test the Interconnect | Here are the steps: | 1. Install the GigaNet GNN1000 Network Interface Card. Please refer to the | GigaNet GNN1000 User Guide for installation instructions. | 2. Install the GigaNet GNX5000 Switch and Cables. Please refer to the | GigaNet GNX5000 User Guide for installation instructions. | 3. Install the GigaNet GNN1000 Adapter Driver software on each node | connected to the GNX5000 Switch. Please refer to the GigaNet GNN1000 User | Guide for installation instructions. Here are additional details if you | are installing drivers provided by GigaNet: | a. Remove any previous version of the GNN1000 Driver already installed. | Removal requires the node to be re-booted. | b. Use "Start>Setting>Control Panel>Networks>Adapters>Add" to install | the driver. | c. Click HAVE DISK... and specify the Driver directory on the CD-ROM. | For example, if F: is your CD-ROM drive, then you would use | "F:\Driver" | d. Select "GNN1000 NDIS Adapter" and then click OK. | e. Configure Network protocols to complete the installation. | GigaNet Adapter Driver software is also available on GigaNet's web site, | http://www.giganet.com. Please refer to the download and installation | instructions found on the support page of GigaNet's web site. | The installation of the GNN1000 Adapter Driver causes the node to | re-boot. | 4. The GigaNet cLAN Management Console (GMC) can be used to test the | integrity of the GigaNet Interconnect. The GigaNet cLAN Management | Console is comprised of two parts: the Console, and the Agent. The Agent | must be installed on all nodes in the cluster. The Console can be | installed on any network node that has access to the nodes in the | cluster. The most versatile and recommended installation is that which | has both the Console and the Agent installed on each node in the cluster. | Install the GigaNet cLAN Management Console. Please refer to the GigaNet | GNN1000 User Guide for installation instructions and additional | information about the cLAN Management Console. Here are additional | details on the installation procedure: | a. Insert the cLAN Software CD into the CD-ROM drive. | b. Wait for the CD automatic installation menu to appear. | c. Click on "Install cLAN Management Console." | d. Repeat this installation procedure on each remaining node in the | cluster. | GigaNet cLAN Management Console software is also available on GigaNet's | web site, http://www.giganet.com. Please refer to the download and | installation instructions found on the support page of GigaNet's web | site. | The installation of the cLAN Management Console may cause the node to | re-boot. | 5. Test that the GigaNet Hardware is working. This can be done by doing the | following: | a. Open the GMC. ("Programs>GigaNet>cLAN Management Console") | b. A dialog box is displayed showing all accessible machines in the LAN. | Press ESC. | c. Select CONSOLE>LOCAL from the menu bar. | d. Confirm that all the members in the cluser are shown and that they | are all "Active." | e. Select UTILITIES>VI THROUGHPUT from the menu bar. This will run a | throughput test to check that the data is actually going through the | hardware. | f. Enter in uppercase letters the computer names of the two nodes you | wish to use in the test. Identify the local node as the source node. | g. Click START MEASURING. You should see data being transferred at a | rate of at least 65 MB per second. | h. Click STOP MEASURING to stop the connection test. | i. Repeat the test for the other nodes in the cluster by measuring | throughput between the local node (Source) and the other nodes | (Sink). | If the connection test does not appear to be working, refer to the | troubleshooting sections of the GigaNet GNN1000 User Guide and the | GigaNet GNX5000 User Guide. | 2.16.3.2 Implement DB2 to Run Using VI | On each database partition server in the instance, set the following DB2 | registry variables and carry out the following tasks: | o Set DB2_VI_ENABLE=ON | o Set DB2_VI_DEVICE=nic0 | o Set DB2_VI_VIPL=vipl.dll | o Enter "db2start" on the MPP instance. | o Review the db2diag.log file. There should be one message for each | partition stating that "VI is enabled." | 2.16.4 RUNNING DB2 UDB V5.2 FOR WINDOWS NT WITH SERVERNET INTERCONNECT | IBM announced support for Virtual Interface (VI) Architecture with DB2 EEE | UDB V5.2. The documentation provided no specifics regarding the VI products | that have been tested with DB2. This document provides specific information | for ServerNet Interconnect. | To find out about other products adhering to VI Architecture and supported by | DB2 UDB EEE V5.2, please contact the DB2 UDB support organization at | http://www.software.ibm.com/data or call 1-800-237-5511 (only in the U.S.A). | To find out about ServerNet products, or to contact ServerNet Service and | Support, please use the following URL: http://www.servernet.com/ | 2.16.4.1 Setup Procedure for ServerNet Interconnect | The list of the hardware and software required to setup this environment | include the following products: | o ServerNet PCI Adapter Driver (SPAD), (product ID T0089), version 1.3.5 or | later | o ServerNet Switch 1 | o ServerNet Area Network Manager (SANMan), (product ID T0087), version | 1.1.3 or later. | The following are the steps required to ensure that ServerNet Interconnect | can work with DB2 UDB. Each step is a summary of what is required at each | step: all of the details associated with each step are not presented here. | You should also use the referenced documenation at each step which does | provide detailed instructions and direction needed. | The steps shown below also assume that you are only using up to six (6) nodes | in the cluster. Contact ServerNet if you have a requirement to use more than | six nodes. | Here are the steps: | 1. Install the ServerNet Network Interface Card. Please refer to the | ServerNet-I Virtual Interface Software Release Document, (product ID | N0031) for installation instructions. | 2. Install the ServerNet Switch 1. Please refer to the ServerNet-I Virtual | Interface Software Release Document, (product ID N0031) for installation | instructions. | 3. Uninstall previous ServerNet drivers. (Skip this step if this is your | first time installing ServerNet.) | a. Open the Network control panel. ("Start>Setting>Control | Panel>Network") | b. Click on the ADAPTERS TAB. | c. Remove Tandem ServerNet PCI Adapter Driver. | d. Click on the SERVICES TAB. | e. Remove SANMan. | f. Click on the PROTOCOLS TAB. | g. Remove Tandem ServerNet-I VI Protocol. | 4. Install the Tandem ServerNet PCI Adapter Driver. Here are additional | details if you are installing using the software CD provided by | ServerNet: | a. Open the Network control panel. ("Start>Setting>Control | Panel>Network") | b. Click on the ADAPTERS TAB. (The Adapters screen appears.) | c. Ensure the new ServerNet driver is placed in a separate drive and/or | directory. Then, from the command prompt referencing the correct | drive and/or directory, type "ernnn.exe -d" to start the | self-extracting program. ("ernnn.exe" is the name of the Engineering | Release followed by a number -- ERnnn.EXE -- that identifies the | specific version of the ServerNet driver to be installed.) | d. Change to the drive and/or directory where the extracted files are | located. Change to the "Spad n.n.n \ Free" sub-directory (where | "n.n.n" is the specific version of the product). (If you are working | in a troubleshooting or a development environment, then change to the | "Spad n.n.n \ Checked" sub-directory instead of the "Spad n.n.n \ | Free" sub-directory.) | e. Rename the "oemsetup.multi_node" file to "oemsetup.inf." | f. Choose ADD in the Adapters Tab. (The Select Adapters screen appears.) | g. Click HAVE DISK.... (The Insert Disk screen appears.) | h. Enter the drive and/or directory where the oemsetup.inf file is | located. | i. Ensure the dialog box shows "Tandem ServerNet PCI Adapter Driver" and | then click OK. Ensure the list of adapters now shows the ServerNet | adapter. Click CLOSE. | j. Choose YES to restart the computer. Or, select NO and continue | installing SANMan and the VI Software Developer's Kit (SDK). | 5. Install SANMan. Here are additional details if you are installing using | the software CD provided by ServerNet: | a. Open the Network control panel. ("Start>Setting>Control | Panel>Network") | b. Click on the SERVICES TAB. (The Services screen appears.) | c. Ensure the new ServerNet driver is placed in a separate drive and/or | directory. Then, from the command prompt referencing the correct | drive and/or directory, type "ernnn.exe -d" to start the | self-extracting program. ("ernnn.exe" is the name of the Engineering | Release followed by a number -- ERnnn.EXE -- that identifies the | specific version of the ServerNet driver to be installed.) | d. Choose ADD in the Services Tab. (The Select Services screen appears.) | e. Change to the drive and/or directory where the extracted files are | located. Change to the "SANMan n.n.n \Free" sub-directory (where | "n.n.n" is the specific version of the product). (If you are working | in a troubleshooting or a development environment, then change to the | "SANMan n.n.n \ Checked" sub-directory instead of the "SANMan n.n.n \ | Free" sub-directory.) | f. Determine if the Switch is X or Y by looking at the light on the | Switch. One light says "X," and the one light says "Y." | g. If an X Switch, select X=1 and Y=0. Ensure all cables are connected | to the X port on the network cards. | h. If a Y Switch, select X=0 and Y=1. Ensure all cables are connected to | the Y port on the network cards. | i. Provide the port number of the switch to which the network card on | the current machine is connected. | j. Select "PC" for all six (6) ports. | 6. Install the Virtual Interface Protocol. Here are additional details if | you are installing using the software CD provided by ServerNet: | a. Open the Network control panel. ("Start>Setting>Control | Panel>Network") | b. Click on the PROTOCOLS TAB. (The Network Protocols screen appears.) | c. Ensure the new ServerNet driver is placed in a separate drive and/or | directory. Then, from the command prompt referencing the correct | drive and/or directory, type "ernnn.exe -d" to start the | self-extracting program. ("ernnn.exe" is the name of the Engineering | Release followed by a number -- ERnnn.EXE -- that identifies the | specific version of the ServerNet driver to be installed.) | d. Choose ADD in the Protocols Tab. (The Select Network Protocols screen | appears.) | e. Click HAVE DISK.... (The Insert Disk screen appears.) | f. Enter the drive and/or directory where the extracted files are | located. | 7. Test that the ServerNet Hardware is working. There are no test programs | available. Instead, simply use DB2 to test the ServerNet hardware. | If the hardware does not appear to be working, refer to the ServerNet-I | Virtual Interface Software Release Document, (product ID N0031) for | additional troubleshooting help. | 2.16.4.2 Implement DB2 to Run Using VI | On each database partition server in the instance, set the following DB2 | registry variables and carry out the following tasks: | o Set DB2_VI_ENABLE=ON | o Set DB2_VI_DEVICE=nic0 | o Set DB2_VI_VIPL=vipl.dll | o Enter "db2start" on the MPP instance. | o Review the db2diag.log file. There should be one message for each | partition stating that "VI is enabled." 3.0 API REFERENCE 3.1 SQLECREA - CREATE DATABASE To specify the IDENTITY collating sequence when creating a database, specify SQL_CS_NONE (which implements a binary collating sequence) in the database description block (SQLEDBDESC) structure. 3.2 SQLUBKP - BACKUP DATABASE In the description of the pMediaTargetList parameter (value SQLU_ADSM_MEDIA), the sentence "No additional input is required." should be replaced with: If an SQLU_MEDIA_ENTRY structure is not being used to specify a path for the backup image, initialize the MEDIA pointer in the SQLU_MEDIA_LIST_TARGETS structure to NULL. 3.3 SQLUGTPI - GET TABLE PARTITIONING INFORMATION The following section is missing from the API description: Required Connection Database 3.4 SQLUHGET - RETRIEVE DDL INFORMATION FROM THE HISTORY FILE (NEW API) There is no callerac parameter available in the sqluhgne API with which to retrieve DDL information recorded in the history file. To retrieve this information, a new API has been created: SQL_API_RC SQL_API_FN sqluhget( unsigned short Handle, // IN: Handle returned from sqluhops unsigned short CallerAction, // IN: For special handling of command field unsigned long Reserved_1, // Reserved for future use struct sqluhinfo * pHistoryInfo, // IN/OUT: Pointer to entry data struct sqluhadm * pAdminInfo, // IN/OUT: Pointer to enhanced adminstrative data void * pReserved, // Reserved for future use struct sqlca * pSqlca); // SQLCA The parameters of this API have the same meanings as the corresponding parameters of the sqluhgne API, with the exception of CallerAction, which is new. The reserved fields of this API, Reserved_1 and pReserved, must be initialized to 0 and NULL, respectively. The possible values of CallerAction are: SQLUH_GET_NEXT_ENTRY Retrieves the next matching entry. No DDL information is returned to the caller, but the length field of the sqluhadm.command structure will be set to the length of any DDL field that exists for that entry. SQLUH_GET_DDL If this action is passed to the API immediately after fetching an entry, the DDL data associated with that entry is returned. It is the responsibility of the caller to indicate - through the sqluhadm.command.length field - the number of bytes of memory which have been allocated for DDL data to be returned through the sqluhadm.command.data field. SQLUH_GET_NEXT_ENTRY_DDL Retrieves the next matching entry. It also returns any DDL information which has been recorded for the entry. The caller must indicate - through the sqluhadm.command.length field - the number of bytes of memory which have been allocated for the sqluhadm.command.data field. 3.5 SQLUHGNE - GET NEXT RECOVERY HISTORY FILE ENTRY The second parameter of this API, which was "int callerac", is now "struct sqluhadm *pAdminInfo". The API definition is: SQL_API_RC SQL_API_FN sqluhgne( unsigned short Handle, struct sqluhadm * pAdminInfo, struct sqluhinfo * pHistoryInfo, struct sqlca * pSqlca); 3.6 SQLUHOPS - OPEN RECOVERY HISTORY FILE SCAN The description of the SQLUH_LIST_ADM_ALTER_TABLESPACE caller action should be changed to: Select only the ALTER TABLESPACE records that pass the other filters. The DDL field associated with an entry will not be returned. To retrive the DDL information for an entry, sqluhgne must be called with a caller action of SQLUH_GET_DDL immediately after the entry is fetched. 3.7 SQLULOAD - LOAD This API affects only the partition to which an application is directly connected; the load utility operates on a single database partition only. The Usage Notes section incorrectly states that "summary tables" that are dependent on the tables being loaded are placed in check pending state. It should state that "summary tables defined with REFRESH IMMEDIATE", that are dependent on the tables being loaded, are placed in check pending state. 3.8 SQLE-CLIENT-INFO (CONNECTION SETTINGS) The valid entries for the SQLE-CLIENT-INFO TYPE element should be changed to: SQLE_CLIENT_INFO_USERID SQLE_CLIENT_INFO_WRKSTNNAME SQLE_CLIENT_INFO_APPLNAME SQLE_CLIENT_INFO_ACCTSTR 3.9 SQLFUPD Table 46, "Updateable Database Manager Configuration Parameters" has an additional entry for a new parameter, catalog_noauth: Parameter Name Token Token Value Data Type -------------- ----- ----------- --------- catalog_noauth SQLF_KTN_CATALOG_NOAUTH 314 Uint16 3.10 SQLUHINFO (SQLUHADM) The definition of the SQLUHADM structure should be changed to: SQL_STRUCTURE sqluhadm { char end_time[SQLUH_TIMESTAMP_SZ+1]; // OUT: Completion time stamp of the event char id[SQLUH_ID_SZ+1]; // OUT: Unique identifier of a dropped table struct sqlca event_sqlca; // OUT: SQLCA associated with the entry struct sqlchar command; // IN/OUT: DDL command information }; NOTES: 1. Currently, command data is recorded only for ALTER TABLESPACE events. 2. It is the responsibility of the caller to initialize the sqluhadm.command.length field to the number of bytes of memory which have been allocated for this field. 3. The macro SQLUHADMSIZE(n), defined in sqlutil, is provided to help determine how much memory is required for an sqluhadm structure with space for n characters of command data. 3.11 SQLUVPUT - WRITING DATA TO DEVICE Change "struct Init_output *," in the "C API Syntax" section to "struct Data *,". 4.0 BUILDING APPLICATIONS FOR WINDOWS AND OS/2 ENVIRONMENTS | 4.1 DB2START ON WINDOWS NT NOT RETURNING CORRECT ERROR 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. 4.2 BUILDING WINDOWS NT AND WINDOWS 95 EMBEDDED SQL APPLICATIONS (IBM VISUALAGE FOR COBOL) There is a problem with the "cob2 -dll" command of VisualAge COBOL for Windows NT Version 2.2. The original command in "x:\sqllib\samples\cobol\bldvacbs.bat" for creating a DLL is: cob2 -dll %1.obj db2api.lib This version of VisualAge COBOL returns the following error: fatal error : Unable to open "E:\sqllib\samples\cobol\db2api.lib". This will be corrected in the first FixPak of VisualAge COBOL for Windows NT. Until that FixPak is available, however, the problem can be solved by replacing the above command with: ilib /nol /gi:%1 %1.obj ilink /free /nol /dll db2api.lib %1.exp %1.obj iwzrwin3.obj 4.3 BUILDING JAVA APPLICATIONS AND APPLETS 4.3.1 SETTING THE WINDOWS ENVIRONMENT FOR THE MICROSOFT SDK FOR JAVA There is a new environment variable for specifying which Java Development Kit to use on the Windows operating system for DB2 SQLJ. The environment variable is called DB2JVIEW, and it applies to all DB2 SQLJ commands (db2profc, db2profp, profdb and profp), except sqlj. If DB2JVIEW=1, Microsoft SDK for Java will be used; that is, if you call "profp", it will run as "jview sqlj.runtime.profile.util.ProfilePrinter". If DB2JVIEW=0, or not set, Sun JDK will be used; that is, if you call "profp", it will run as "java sqlj.runtime.profile.util.ProfilePrinter". The command "sqlj" will ignore this variable, and always use the Sun JDK to run. | 4.3.2 NEW CLIENT AND SERVER PROGRAMS AND SQLJ PRECOMPILING SCRIPT | There have been several changes to the Java sample programs documented in | this chapter. The stored procedure and UDF programs have been split into | separate client and server programs. This allows users on client machines to | remotely access stored procedures and UDFs on server machines. The affected | files are now as follows: | DB2STP is a server program. The client program that calls it is | DB2SPCLI. | DB2UDF is a server program. The client program that calls it is | DB2UDCLI. | STP is a server program. The client program that calls it is | STPCLI. | UDF is a client program. The server program that it calls is | UDFSRV. | The script file "embprep" has been created to precompile and bind the SQLJ | programs. It is called by the "makefile" and executes the "db2profc" command | on the Java embedded SQL sample programs. It allows optional arguments for | database, user ID and password, thereby facilitating remote binding to | databases on DB2 servers from client machines. 5.0 BUILDING APPLICATIONS FOR UNIX ENVIRONMENTS 5.1 SCO UNIXWARE 7 | 5.1.1 EXPORTING FUNCTIONS FOR STORED PROCEDURES | In SCO UnixWare 7, functions defined in a client program and not explicitly | exported are not available to a stored procedure library called by the client | program. An alternative to recoding the client program in order to make the | functions external is to use the compile option "-Wl,-Bexport", which exports | all functions defined in the client program. This option has been added to | the makefiles for SCO UnixWare 7 C and C++ to compile the sample client | program "fillcli", which calls the stored procedure library "fillsrv" for | these languages. The makefiles and sample programs are located in the | "sqllib/samples/c" and "sqllib/samples/cpp" directories. 5.1.2 MICRO FOCUS COBOL LINK OPTION In the "Micro Focus COBOL" section, the link option "-lthread" in the "bldmfcc" script file is no longer needed as the thread library has been incorporated into the DB2 library, represented by the "-ldb2" option. 5.2 BUILDING JAVA APPLICATIONS AND APPLETS 5.2.1 SETTING THE ENVIRONMENT: HP-UX, SCO UNIXWARE 7 To build and run Java programs on HP-UX now requires the HP-UX Developer's Kit for Java Release 1.1.3 (or higher). DB2 does not support Java stored procedures and Java user-defined functions (UDFs) on HP-UX and SCO UnixWare servers. Because of this restriction, the following Java sample programs cannot be run against DB2 databases on HP-UX and SCO UnixWare: DB2Stp.java DB2Udf.java CatUdf.sqlj DropUdf.sqlj Stp.sqlj Udfsrv.java | 5.2.2 SILICON GRAPHICS IRIX RESTRICTION WITH CLOSE() METHOD | On Silicon Graphics IRIX, it is a known problem that the connection context | "close()" method may cause a trap. To get around the problem, leave the | connection context to be closed automatically during garbage collection. 5.2.3 NEW CLIENT AND SERVER PROGRAMS AND SQLJ PRECOMPILING SCRIPT There have been several changes to the Java sample programs documented in this chapter. The stored procedure and UDF programs have been split into separate client and server programs. This allows users on client machines to remotely access stored procedures and UDFs on server machines. The affected files are now as follows: DB2STP is a server program. The client program that calls it is DB2SPCLI. DB2UDF is a server program. The client program that calls it is DB2UDCLI. STP is a server program. The client program that calls it is STPCLI. UDF is a client program. The server program that it calls is UDFSRV. The script file "embprep" has been created to precompile and bind the SQLJ programs. It is called by the "makefile" and executes the "db2profc" command on the Java embedded SQL sample programs. It allows optional arguments for database, user ID and password, thereby facilitating remote binding to databases on DB2 servers from client machines. 6.0 CLI GUIDE AND REFERENCE 6.1 SQLPREPARE() The description of SQLPrepare() does not indicate that deferred prepare is now ON by default. This means that the processing of the SQL statements passed to the database using SQLPrepare() does not take place when the SQLPrepare() function is called. Instead, it is processed when SQLExecute() is called. Minimal error checking is performed by DB2 CLI when SQLPrepare() is called (that is, valid function arguments, and so on). The true SQLSTATEs are not returned from the prepare until SQLExecute() is called. Your application should therefore expect SQLSTATEs from SQLPrepare() after the call to SQLExecute(), followed by SQLSTATEs from the SQLExecute() call itself. For more information see "Deferred Prepare now on by Default" in "Appendix B. Migrating Applications", in the section "Changes from Version 2.1.1 to 5.0.0". This describes how to turn deferred prepare off for Version 2 programs, as well as a further description of the deferred prepare process. 6.2 SQLDISCONNECT() The description states that cursors are closed by a call to SQLDisconnect(). This is not the case, however, with stored procedures. An application should not rely on SQLDisconnect() to close cursors, even if it is not a stored procedure. In both cases, the cursor should be closed using SQLCloseCursor(), then the statement handle freed with a call to SQLFreeHandle() with a HandleType of SQL_HANDLE_STMT. 6.3 USING STORED PROCEDURES - PROGRAMMING STORED PROCEDURES TO RETURN RESULT SETS The book indicates that the stored procedures must be run on a remote server. This is no longer the case. Stored procedures running on a local server can return result sets. The book indicates that the stored procedure should call SQLFreeStmt() with either SQL_DROP or SQL_CLOSE; however, only SQL_CLOSE should be used. 6.4 STORED PROCEDURE CATALOG TABLES All stored procedures should be registered in the new SYSCAT.PROCEDURES and SYSCAT.PROCPARMS catalog tables using the CREATE PROCEDURE statement. For more information about this SQL statement, see the SQL Reference. If the stored procedures are not registered using the CREATE PROCEDURE statement, CLI or ODBC applications will not know that they exist. For more information, see the section "Replacement of the Pseudo Catalog Table for Stored Procedures" in the Migration Appendix. | 6.5 SQLFETCHSCROLL(SQL_FETCH_RELATIVE, -1) | There is an error in the documentation for SQLFetchScroll() in the version | 5.0 Call Level Interface Guide and Reference. The table for | SQL_FETCH_RELATIVE rules should be replaced with the following table: | SQL_FETCH_RELATIVE rules: +---------------------------------------------------------------------------+ | | Table 3. SQL_FETCH_RELATIVE Rules: | +-------------------------------------------+-------------------------------+ | | CONDITION | FIRST ROW OF NEW ROWSET | +-------------------------------------------+-------------------------------+ | | (Before start AND FetchOffset > 0) OR | -- (a) | | | (After end AND FetchOffset < 0) | | +-------------------------------------------+-------------------------------+ | | Before start AND FetchOffset <= 0 | Before start | +-------------------------------------------+-------------------------------+ | | CurrRowsetStart = 1 AND FetchOffset < 0 | Before start | +-------------------------------------------+-------------------------------+ | | CurrRowsetStart > 1 AND CurrRowsetStart + | Before start | | | FetchOffset < 1 AND |FetchOffset| > | | | | RowsetSize | | +-------------------------------------------+-------------------------------+ | | CurrRowsetStart > 1 AND CurrRowsetStart + | 1 (b) | | | FetchOffset < 1 AND |FetchOffset| <= | | | | RowsetSize | | +-------------------------------------------+-------------------------------+ | | 1 <= CurrRowsetStart + FetchOffset <= | CurrRowsetStart + FetchOffset | | | LastResultRow | | +-------------------------------------------+-------------------------------+ | | CurrRowsetStart + FetchOffset > | After end | | | LastResultRow | | +-------------------------------------------+-------------------------------+ | | After end AND FetchOffset >= 0 | After end | +-------------------------------------------+-------------------------------+ 7.0 COMMAND REFERENCE 7.1 DB2CMD - OPEN DB2 COMMAND WINDOW This command is available on Windows NT, Windows 95, and Windows 98. The following switches are now supported: /c Invoke command.exe with the /c option (that is, execute the command, and then terminate. For example, "db2cmd /c dir" causes the "dir" command to be invoked in a command window, and then the command window closes. /w Wait until the cmd.exe process ends. For example, "db2cmd /c /w dir" invokes the "dir" command, and db2cmd.exe does not end until the command window closes. /i Run the command window, sharing the same console and inheriting file handles. For example, "db2cmd /c /w /i db2 get dbm cfg > myoutput" invokes cmd.exe to run the db2 command and to wait for its completion. A new console is not assigned, and stdout is piped to file "myoutput". /t Instead of using "DB2 CLP" as the title of the command window, inherit the title from the invoking window. This is useful if you want to set up an icon with a different title that invokes "db2cmd /t". NOTE: All switches must appear before any commands to be executed. For example: db2cmd /t db2 If DB21061E ("Command line environment not initialized.") is returned when bringing up the CLP-enabled DB2 window, or running CLP commands on Windows 95 or Windows 98, the operating system may be running out of environment space. Check the config.sys file for the SHELL environment setup parameter, and increase its value accordingly. For example: SHELL=C:\COMMAND.COM C:\ /P /E:32768 7.2 DB2EVA - EVENT ANALYZER The "Command Parameters" section is missing the following description: -evm evmon-name Specifies the name of the event monitor whose traces are to be analyzed. 7.3 DB2EVMON - EVENT MONITOR PRODUCTIVITY TOOL The "-db" and "-evm" keywords are case sensitive. 7.4 DB2LOOK - DB2 STATISTICS EXTRACTION TOOL Option "-a" generates statistics for all users on the database. If used with option "-e", it generates the DDL for all user tables in the database, but statistics are not generated. NOTES: 1. Some DDL characteristics are not extracted by db2look. 2. If neither -u nor -a is specified, the environment variable USER is used. On UNIX based systems, this variable does not have to be explicitly set; on Windows NT, however, there is no default value for the USER environment variable: on this platform, a user variable in the SYSTEM variables must be set, or a "set USER=" must be issued for the session. 7.5 DB2SET - DB2 PROFILE REGISTRY COMMAND The "Command Parameters" section has been changed to: variable=value Sets a specified variable to a specified value. To delete a variable, do not specify a value for the specified variable. Changes to settings take effect after the instance has been restarted. -g Accesses the global profile variables. -i instance Specifies the instance profile to use instead of the current, or default. node-number Specifies a number listed in the db2nodes.cfg file. -all Displays all occurrences of the local environment variables as defined in: o The environment, denoted by [e] o The node level registry, denoted by [n] o The instance level registry, denoted by [i] o The global level registry, denoted by [g]. -null Sets the value of the variable at the specified registry level to null. This avoids having to look up the value in the next registry level, as defined by the search order. -r instance Resets the profile registry for the given instance. -n DAS node Specifies the remote DB2 administration server node name. -u user Specifies the user ID to use for the administration server attachment. -p password Specifies the password to use for the administration server attachment. -l Lists all instance profiles. -lr Lists all supported registry variables. -v Specifies verbose mode. -h/-? Displays help information. When this option is specified, all other options are ignored, and only the help information is displayed. 7.6 DB2UPD52 - UPDATE CATALOG TO SUPPORT VERSION 5.2 Although this utility is installed at the server, it can be run at a client; to do so, copy the executable from the server to the client. In an MPP environment, the utility must be run from the catalog node of the specified database. 7.7 BACKUP DATABASE To use tape devices, DB2 users on SCO UnixWare 7 need to specify BUFFER to be 16. The default value of BUFFER is 1024 pages. If BUFFER is set to zero, the database manager configuration parameter BACKBUFSZ must be set to 16. 7.8 EXPORT Table 6 ("Valid Delimiters"), and all references to it, should be removed. The information contained in the note at the bottom of the table, and the restrictions that follow the note, are correct, however. NOTE: In a DBCS environment, the pipe (|) character delimiter is not supported. Replace the section "DB2 Data Links Manager Considerations" in the Usage Notes with the following: To ensure that a consistent copy of the table and the corresponding files referenced by the DATALINK columns are copied for export, do the following: 1. To ensure that no update transactions are in progress when the export utility is run, issue the command QUIESCE TABLESPACES FOR TABLE tablename SHARE. 2. Issue the EXPORT command. 3. Run the dlfm_export utility with root authority at each file server. Input to the dlfm_export utility is the control file, server_name, which is generated by the export utility. The dlfm_export utility must be run with root authority in order to successfully archive files to which the DLFM administrator may not have access. 4. To make the table available for updates, issue the command QUIESCE TABLESPACES FOR TABLE tablename RESET. EXPORT is executed as an SQL application. The rows and columns satisfying the SELECT statement conditions are extracted from the database. For the DATALINK columns, the SELECT statement should not specify any scalar function. The export utility uses APIs to extract parts of the DATALINK value, such as link type, file server name, file path name, and comments. Successful execution of EXPORT results in generation of the following files: o An export data file as specified in the EXPORT command. A DATALINK column value in this file is in the format described on page 290. When the DATALINK column value is the SQL NULL value, handling is the same as that for other data types. o Control files server_name, which are generated for each file server (on the Windows operating system and on OS/2, a single control file, ctrlfile.lst, is used by all the file servers). A control file contains the URLs for all the files that are to be exported from that file server. Use the dlfm_export utility to export files from a file server as follows: Usage: dlfm_export [] Description: This is the file name that was generated by running the export utility on the DB2 client. This is the name of the archive file that will be generated (the default is export.tar in the current working directory). A corresponding utility called dlfm_import is provided to retrieve and to restore files from the archive that dlfm_export generates. This utility must be used whether the archived files are being restored on the same or on a different file server. Use the dlfm_import utility to retrieve files from the archive as follows: Usage: dlfm_import [] Description: This is the name of the archive file that will be used to recover the files (the default is export.tar in the current working directory). NOTES: 1. Both dlfm_export and dlfm_import utilities must be run with root authority. In the case of dlfm_export, there may be files to be archived to which the DLFM administrator does not have access. In the case of dlfm_import, root authority is required because the user may want to restore the archived files on a different file server, one that may not have the same directory structure and user IDs as the file server on which the dlfm_export utility was run. 2. The File Manager does not have to be running in order for the dlfm_export and the dlfm_import utilities to run. 3. When running the dlfm_import utility on a file server other than the one on which dlfm_export was run, the files will be restored on the correct paths. The files will be owned by root, in case some of the user IDs do not exist on the new machine. Before inserting the corresponding DATALINK values into the database, it is the administrator's responsibility to ensure that all files have the correct permissions and belong to the correct user IDs. EXPORTING BETWEEN INSTANCES The table below shows how to export DB2 data and the files that are referenced by the instance called SystemA to the instance called SystemB. SystemA uses file servers DLFM1 and DLFM2. SystemB uses file servers DLFMX and DLFMY. The files on DLFM1 will be exported to DLFMX, and the files on DLFM2 will be exported to DLFMY. +------------------------------------------------------------------------------+ | InstanceA with File Servers DLFM1 and DLFM2 | Step | +------------------------------------------------------------------------------+ | DB2 Data | File1 for DLFM1 | File2 for DLFM2 | | | on File | | | | +----------+-----------------+-----------------+-------------------------------+ | | Run the | Run the | 1. Run dlfm_export (as root) | | | dlfm_export | dlfm_export | on both file servers. | | | command | command | This will produce an archive | | | | | on both file servers. | +----------+-----------------+-----------------+-------------------------------+ +------------------------------------------------------------------------------+ | InstanceB with File Servers DLFMX and DLFMY | Step | +------------------------------------------------------------------------------+ | | On DLFMX, | On DLFMY, | 2. Run dlfm_import (as root) | | | restore from an | restore from an | on both file servers. | | | archive | archive | | +----------+-----------------+-----------------+-------------------------------+ | | | | 3. Run the IMPORT command on | | | | | InstanceB, with the parameter | | | | | DL_URL_REPLACE_PREFIX to | | | | | specify the appropriate file | | | | | server for each exported file.| +----------+-----------------+-----------------+-------------------------------+ +------------------------------------------------------------------------------+ | After the import utility is run on InstanceB, InstanceA data and all | | files referenced by DATALINK columns are imported. | +------------------------------------------------------------------------------+ 7.9 GET DATABASE MANAGER CONFIGURATION 7.9.1 NEW PARAMETER CATALOG_NOAUTH A new parameter, catalog_noauth, specifies whether users are able to catalog and uncatalog databases and nodes, or DCS and ODBC directories, without SYSADM authority. The default value (0) for this parameter indicates that SYSADM authority is required. When this parameter is set to 1 (yes), SYSADM authority is not required. | 7.9.2 TRUST_ALLCLNTS UPDATED | "TRUST_ALLCLNTS" as well as "TRUST_CLNTAUTH" are used to determine where | users are validated for the database environment. By accepting the default | for "TRUST_ALLCLNTS", all clients are treated as trusted clients. This means | a level of security is available at the client, and that users can be | validated at the client. Other options may be used to protect the server | against certain clients based on their platform or database protocol. 7.10 IMPORT In an MPP environment, if a database was bound using the INSERT BUF option, buffered insert will be disabled during any import operation to that database if the INSERT_UPDATE is specified. NOCHECKLENGTHS is supported for both the ASC and the IXF file formats. This option does not have to be used with the FORCEIN modifier. If NOCHECKLENGTHS is specified, an attempt is made to import each row, even if the source data has a column definition that exceeds the size of the target table column. Such rows may be successfully imported if code page conversion causes the source data to shrink; for example, 4-byte EUC data in the source could shrink to 2-byte DBCS data in the target, and require half the space. This option is particularly useful if it is known that the source data will fit in all cases despite mismatched column definitions. The current default priority for delimiters is: record delimiter, character delimiter, column delimiter. To protect existing customer applications that depend on the older priority, a new file type modifier, DELPRIORITYCHAR, has been created for the import utility. This modifier reverts the delimiter priorities to: character delimiter, record delimiter, column delimiter. The syntax is: db2 import ... modified by delprioritychar ... 7.11 LIST DATABASE DIRECTORY There can be a maximum of eight opened database directory scans per process. To overcome this restriction for a batch file that issues more than eight LIST DATABASE DIRECTORY commands within a single DB2 session, convert the batch file into a shell script. The "db2" prefix generates a new DB2 session for each command. 7.12 LOAD This command affects only the partition to which a direct connection exists; the load utility operates on a single database partition only. The COPY option is not supported for tables with DATALINK columns. The REPLACE option is not supported for tables with DATALINK columns. The NONRECOVERABLE clause should not be used when DATALINK columns with the FILE LINK CONTROL attribute are present in - or being added to - the table. There is a limit on the combination of DATALINK columns and the number of DLFMs used. There can be at most 256 unique combinations of DLFMs and DATALINK columns in a table. For example, the table can have a maximum of 128 DATALINK columns if the number of DLFMs configured is two. Load copy to tapes is not supported for DB2 servers running on SCO UnixWare 7. The Usage Notes section incorrectly states that "summary tables" that are dependent on the tables being loaded are placed in check pending state. It should state that "summary tables defined with REFRESH IMMEDIATE", that are dependent on the tables being loaded, are placed in check pending state. NOCHECKLENGTHS is supported for both the ASC and the IXF file formats. This option does not have to be used with the FORCEIN modifier. If NOCHECKLENGTHS is specified, an attempt is made to load each row, even if the source data has a column definition that exceeds the size of the target table column. Such rows may be successfully loaded if code page conversion causes the source data to shrink; for example, 4-byte EUC data in the source could shrink to 2-byte DBCS data in the target, and require half the space. This option is particularly useful if it is known that the source data will fit in all cases despite mismatched column definitions. The current default priority for delimiters is: record delimiter, character delimiter, column delimiter. To protect existing customer applications that depend on the older priority, a new file type modifier, DELPRIORITYCHAR, has been created for the load utility. This modifier reverts the delimiter priorities to: character delimiter, record delimiter, column delimiter. The syntax is: db2 load ... modified by delprioritychar ... The new option is not supported in an MPP environment, nor is it supported by the AutoLoader. Load SMP exploitation is disabled when this option is specified (CPU_PARALLELISM is forced to a value of 1). The new option is mutually exclusive with the "DUMPFILE" feature, and is supported only for the DEL file format. 7.13 RECONCILE There is an error in the description of "DLREPORT filename". There is no default value for this parameter. The file name must be a fully qualified file name. For example: /home/user/report.out 7.14 RESTORE DATABASE If the WITHOUT DATALINK option is not specified, and the DB2 File Manager containing the DATALINK data is unavailable, the restore operation will fail. If this option is specified, and the DB2 File Manager containing the DATALINK data is unavailable, all table spaces which contain tables with DATALINK values on the unavailable server are placed in RESTORE PENDING state. To use tape devices, DB2 users on SCO UnixWare 7 need to specify BUFFER to be 16. The default value of BUFFER is 1024 pages. If BUFFER is set to zero, the database manager configuration parameter BACKBUFSZ must be set to 16. | 7.15 START DATABASE MANAGER | 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. 8.0 EMBEDDED SQL PROGRAMMING GUIDE 8.1 WRITING USER-DEFINED FUNCTIONS (INTERFACE BETWEEN DB2 AND A UDF/THE ARGUMENTS PASSED FROM DB2 TO A UDF) There are two changes to the numbered list under the "dbinfo" argument: o Insert a new item 17 in the list. The new item 17 reads as follows: 17. Unique application identifier (appl_id) This field is a pointer to a C null-terminated string which uniquely identifies the application's connection to DB2. It is regenerated at each database connect. The string has a maximum length of 32 characters, and its exact format depends on the type of connection established between the client and DB2. Generally it takes the form .. where the and vary by connection type, but the is a 12 character time stamp of the form YYMMDDHHMMSS, which is potentially adjusted by DB2 to ensure uniqueness. Example: *LOCAL.db2inst.980707130144 o Change the existing item 17 to item 18, and within this item, change "24 characters" to "20 characters": 17. Reserved field (resd2) This field is for future use. It is defined as 20 characters long. 8.2 PROGRAMMING IN C AND C++ (PROGRAMMING CONSIDERATIONS/HOST VARIABLES) 8.2.1 DECLARING HOST VARIABLES BIGINTs can now be declared in one of three ways: o long long o __int64 // (A double underscore precedes "int64".) This declaration is specific to the Microsoft compiler. o sqlint64 This is useful when writing platform-independent applications. The DB2 UDB sqlsystm.h header file will type define sqlint64 as "__int64" on the Windows NT platform when using the Microsoft compiler; otherwise, sqlint64 will be type defined as "long long". 8.2.2 HANDLING GRAPHIC HOST VARIABLES (WINDOWS OPERATING SYSTEM) When specifying the WCHARTYPE CONVERT option on a Windows platform, you should note that wchar_t on Windows platforms is Unicode. Therefore, if your C/C++ compiler's wchar_t is not Unicode, the wcstombs() function call may fail with SQLCODE -1421 (SQLSTATE=22504). If this happens, you can specify the WCHARTYPE NOCONVERT option, and explicitly call the wcstombs() and mbstowcs() functions from within your program. | 8.3 PROGRAMMING IN COBOL (HOST STRUCTURE SUPPORT) | The COBOL precompiler supports declarations of group data items in the host | variable declare section. Among other things, this provides a shorthand for | referring to a set of elementary data items in an SQL statement. For example, | the following group data item can be used to access some of the columns in | the STAFF table of the SAMPLE database: | 01 staff-record. | 05 staff-id pic s9(4) comp-5. | 05 staff-name. | 49 l pic s9(4) comp-5. | 49 d pic x(9). | 05 staff-info. | 10 staff-dept pic s9(4) comp-5. | 10 staff-job pic x(5). | Group data items in the declare section can have any of the valid host | variable types described above as subordinate data items. This includes all | numeric and character types, as well as all large object types. THE | RESTRICTION OF ALLOWING ONLY ONE LEVEL OF GROUP DATA ITEMS HAS BEEN REMOVED, | AND THEY CAN NOW BE NESTED UP TO 10 LEVELS. Note that you must declare | VARCHAR character types with the subordinate items at level 49, as in the | above example. If they are not at level 49, the VARCHAR is treated as a group | data item with two subordinates, and is subject to the rules of declaring and | using group data items. In the example above, "staff-info" is a group data | item, whereas "staff-name" is a VARCHAR. The same principle applies to LONG | VARCHAR, VARGRAPHIC and LONG VARGRAPHIC. You may declare group data items at | any level between 02 and 49. | You can use group data items and their subordinates in four ways: | METHOD 1. | The entire group may be referenced as a single host variable in an SQL | statement: | EXEC SQL SELECT id, name, dept, job | INTO :staff-record | FROM staff WHERE id = 10 END-EXEC. | The precompiler converts the reference to staff-record into a list, separated | by commas, of all the subordinate items declared within staff-record. Each | elementary item is qualified with the group names of all levels to prevent | naming conflicts with other items. This is equivalent to the following | method. | METHOD 2. | The second way of using group data items: | EXEC SQL SELECT id, name, dept, job | INTO | :staff-record.staff-id, | :staff-record.staff-name, | :staff-record.staff-info.staff-dept, | :staff-record.staff-info.staff-job | FROM staff WHERE id = 10 END-EXEC. | NOTE: The reference to "staff-id" is qualified with its group name using the | prefix "staff-record.", and not "staff-id" of "staff-record" as in | pure COBOL. | Assuming there are no other host variables with the same names as the | subordinates of staff-record, the above statement can also be coded as in | method 3, eliminating the explicit group qualification. | METHOD 3. | Here, subordinate items are referenced in a typical COBOL fashion, without | being qualified to their particular group item: | EXEC SQL SELECT id, name, dept, job | INTO | :staff-id, | :staff-name, | :staff-dept, | :staff-job | FROM staff WHERE id = 10 END-EXEC. | As in pure COBOL, this method is acceptable to the precompiler as long as a | given subordinate item can be uniquely identified. If, for example, | "staff-job" occurs in more than one group, the precompiler issues an error | indicating an ambiguous reference: | SQL0088N Host variable "staff-job" is ambiguous. | METHOD 4. | To resolve the ambiguous reference, partial qualification of the subordinate | item can also be used. | EXEC SQL SELECT id, name, dept, job | INTO | :staff-id, | :staff-name, | :staff-info.staff-dept, | :staff-info.staff-job | FROM staff WHERE id = 10 END-EXEC. | Because a reference to a group item alone, as in method 1, is equivalent to a | comma-separated list of its subordinates, there are instances where this type | of reference leads to an error. For example: | EXEC SQL CONNECT TO :staff-record END-EXEC. | Here, the CONNECT statement expects a single character-based host variable. | By giving the "staff-record" group data item instead, the host variable | results in the following precompile-time error: | SQL0087N Host variable "staff-record" is a structure used where | structure references are not permitted. | Other uses of group items which cause an SQL0087N to occur include PREPARE, | EXECUTE IMMEDIATE, CALL, indicator variables, and SQLDA references. Groups | with only one subordinate are permitted in such situations, as are references | to individual subordinates, as in method 2, 3 and 4 above. | INDICATOR TABLES | The COBOL precompiler supports the declaration of tables of indicator | variables, which are convenient to use with group data items. They are | declared as follows: | 01 . | 05 pic s9(4) comp-5 | occurs times. | For example: | 01 staff-indicator-table. | 05 staff-indicator pic s9(4) comp-5 | occurs 7 times. | This indicator table can be used effectively with the first format of group | item reference above: | EXEC SQL SELECT id, name, dept, job | INTO :staff-record :staff-indicator | FROM staff WHERE id = 10 END-EXEC. | Here, the precompiler detects that "staff-indicator" was declared as an | indicator table, and expands it into individual indicator references when it | processes the SQL statement. "staff-indicator"(1) is associated with | "staff-id" of "staff-record", "staff-indicator"(2) is associated with | "staff-name" of "staff-record", and so on. | NOTE: If there are "k" more indicator entries in the indicator table than | there are subordinates in the data item (for example, if | "staff-indicator" has 10 entries, making "k"=6), the "k" extra entries | at the end of the indicator table are ignored. Likewise, if there are | "k" fewer indicator entries than subordinates, the last "k" | subordinates in the group item do not have indicators associated with | them. NOTE THAT YOU CAN REFER TO INDIVIDUAL ELEMENTS IN AN INDICATOR | TABLE IN AN SQL STATEMENT. 8.4 PROGRAMMING IN JAVA (CREATING JAVA UDFS AND STORED PROCEDURES/CLASSES FOR JAVA STORED PROCEDURES AND UDFS) In the section "COM.ibm.db2.app.UDF", there is a list of functions that return specific DBINFO fields. To this list has been added: public String getDBapplid() throws Exception 8.5 A JDK PROBLEM THAT AFFECTS JDBC PROGRAMS RUNNING UNDER A DBCS CODE PAGE ON AIX When running JDBC programs with JDK 1.1.4 bundled with AIX 4.3.1 under a DBCS code page, the DBCS data can not be displayed properly. This is a known problem in this version of JDK, and a fix for it (IX78932) is available. 8.6 JDBC SAMPLE APPLICATIONS RESTRICTION ON SCO UNIXWARE 7 Because a native method cannot make use of the thread library (libthread.so from UnixWare 7; see the UnixWare release notes), DB2 JDBC sample applications (DB2Appl, DB2Udf, and DB2Stp) do not work from a Version 5.2 client on UnixWare 7 connecting to a Version 5 UDB server, or a Version 5 client connecting to a FixPak 3 UDB server. | 8.7 JDBC 2.0 | JDBC 2.0 is the latest version of JDBC from Sun. This version of JDBC has two | defined parts: the CORE API, and the STANDARD EXTENSION API. The core API has | been released. For information on its specification, refer to: | http://www.software.ibm.com/data/db2/java | The standard extension API is currently under review. For information on the | review version of its specification, you can also visit the web page above. | FEATURES | The JDBC 2.0 driver we ship in this FixPak supports the JDBC 2.0 core API. | However, due to the unavailability of certain features of the DB2 Engine, not | all features defined in the specification are supported. Here is a list of | main features that are supported: | o Read-only Scrollable Result Set | o Batch Update (Batch statements and batch prepared statements) | o LOB support | Here is a list of features that are NOT supported: | o Updatable Scrollable ResultSet | o New SQL types (Array, Ref, Distinct, Java Object) | o Customized SQL type mapping | PREREQUISITE | JDK 1.2 | PLATFORM | The JDBC 2.0 driver is currently available only on Windows 95, Windows 98, | and Windows NT operating systems. | COMPATIBILITY | This version of the specification is backward compatible with the previous | version (1.22). However, the DB2 JDBC 1.22 driver supports LOB types as an | extension of the JDBC 1.22 specification, and this extension is not part of | the new specification's backward compatibility. This means that existing JDBC | applications that rely on the LOB support of the JDBC 1.22 driver may not | work with the new driver. To fix the problem, we recommend that you modify | the application to be compliant with the new specification. However, this | solution may not be practical for every situation. As a workaround we provide | a keyword, "JDBCVERSION", that you can set to "122" to tell the JDBC driver | you want the 1.22 version for DB2 LOB support. The default is "200" for 2.0 | version behavior. You can set this keyword in "db2cli.ini", or pass it in as | a connection attribute in the "getConnection" property argument. | INSTALLATION | In this FixPak, the JDBC 1.22 driver is still the default driver. The JDBC | 2.0 driver is included in the "sqllib\java12" directory. To use the JDBC 2.0 | driver, run the "usejdbc2" batch file. This will create an "sqllib\java11" | directory for the 1.22 driver files. It will also back up the JDBC 1.22 | driver files into this directory, and will copy the JDBC 2.0 driver files | from the "sqllib\java12" directory into the appropriate directories. To | switch back to the JDBC 1.22 driver, execute the "usejdbc1" batch file. | 8.8 PERL ACCESS TO DB2 UDB DATABASES | Release 0.70 of the DB2 UDB driver (DBD::DB2) for the Perl Database Interface | (Perl DBI) is now available. | The Perl DBI is an Application Programming Interface (API) that provides | database access for the Perl language. It defines a set of functions, | variables, and conventions that provide a consistent database interface | independent of the actual database being used. | The DBD::DB2 driver works in conjunction with the DBI to access DB2 UDB. | For additional information, see the Web page: | http://www.software.ibm.com/data/db2/perl/ 8.9 DATALINK DATA TYPE Embedded SQL using the DATALINK data type is not supported. 8.10 MULTITHREADED UNIX APPLICATIONS WORKING WITH CODE PAGE AND COUNTRY CODE On AIX, Solaris, HP-UX, SCO UnixWare 7, and Silicon Graphics IRIX, changes have been made to the functions that are used for run time querying of the code page and country code to be used for a database connection. They are now thread safe but can create some lock contention (and resulting performance degradation) in a multithreaded application which uses a large number of concurrent database connections. A new environment variable has been created (DB2_FORCE_NLS_CACHE) to eliminate the chance of lock contention in multithreaded applications. When DB2_FORCE_NLS_CACHE is set to TRUE the code page and country code information is saved the first time a thread accesses it. From that point on the cached information will be used for any other thread that requests this information. By saving this information, lock contention is eliminated and in certain situations a performance benefit will be realized. DB2_FORCE_NLS_CACHE should not be set to true if the application changes locale settings between connections. If this is done then the original locale information will be returned even after the locale settings have been changed. In general, multithreaded applications will not change locale settings. This ensures that the application remains thread safe. 8.11 ERROR PROCESSING FOR UDF CALL TYPES 8.11.1 SCRATCHPAD AND FINAL CALL TYPE ARGUMENTS FOR EXTERNAL TABLE FUNCTIONS Changes have been introduced (a) to give the user explicit control over scratchpad duration, and (b) to avoid inconsistencies in the duration of scratchpads, for table functions written in: o C/C++ - until now, scratchpads always survived across CLOSE/OPEN calls to the UDF o OLE/Java - until now, a fresh scratchpad was always allocated on OPEN This difference has led to the possibility of getting inconsistent results, for example when performing a join. The following changes have been introduced for User Defined Functions (UDFs) which are table functions: 1. FINAL CALL is now optional for the CREATE FUNCTION statement for table functions, as it is for scalar functions. NO FINAL CALL (the default) may thus be specified for table functions. 2. When writing a table function which has a scratchpad, you can elect to have the scratchpad be initialized for each OPEN call to the function, by specifying NO FINAL CALL, in which case the table function is expected to release resources on each CLOSE call. Or, you can elect to have the scratchpad content be preserved across OPEN calls, by specifying FINAL CALL. If you do this, you need not release resources during the CLOSE call processing, because a FINAL call will be made to the table function at end-of-statement. This FINAL CALL is new with this change, as is the balancing FIRST call which takes place before the first OPEN call. These two new call types occur only on table functions which specify FINAL CALL. 3. For Java table functions, a change to the execution model needs to be understood. The following table explains what happens at each significant point in the table function processing of a given statement (the bottom part of each box hints what the code might be written to do for a typical table function which pulls some information in from the Web). Covered are both the NO FINAL CALL and the FINAL CALL cases, assuming SCRATCHPAD in both cases, which account for the new rules described above. | | Point | | NO FINAL CALL | FINAL CALL in | | LANGUAGE JAVA | LANGUAGE JAVA scan | | SCRATCHPAD | SCRATCHPAD time V | | ------------+------------------------------+------------------------------ Before the | No calls. | Class constructor is called first OPEN | | (means new scratchpad). for the | | UDF Method is called with table func. | | "FIRST" call. | |- - - - - - - - - - - - - - - | | Constructor initializes | | class and scratchpad | | variables. | | Method connects to the Web | | Server. | | ------------+------------------------------+------------------------------ At each | Class constructor is called | UDF Method is called with OPEN of the | (means new scratchpad). | "OPEN" call. table func. | UDF Method is called with | | "OPEN" call. | |- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - - | Constructor initializes | Method opens the scan for | class and scratchpad | whatever Web data it wants. | variables. | (Might be able to avoid | Method connects to the Web | reopen after a CLOSE repo- | Server, and opens the scan | sition, depending on what | for Web data. | is saved in scratchpad!) | | ------------+------------------------------+------------------------------ At each | UDF method is called with | UDF Method is called with FETCH for | "FETCH" call. | "FETCH" call. new row of |- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - - table func | Method fetches and returns | Method fetches and returns data. | next row of data, or EOT. | next row of data, or EOT. | | ------------+------------------------------+------------------------------ At each | UDF method is called with | UDF Method is called with CLOSE of the| "CLOSE" call. | "CLOSE" call. table func. | "close" method is called if | | it exists for class. | |- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - - | Method closes its Web scan | Method might reposition to | and disconnects from the | the top of the scan, or | Web Server. | close the scan. It can | "close" does not need to do | save any state in the | anything. | scratchpad, which will | | persist. | | ------------+------------------------------+------------------------------ After the | No calls. | UDF Method is called with last CLOSE | | "FINAL" call. of the table| | "close" method is called if func. | | it exists for class. | |- - - - - - - - - - - - - - - | | Method disconnects from the | | Web Server. | | "close" does not need to do | | anything. | | ------------+------------------------------+------------------------------ Some notes on this table: a. By "UDF Method" we mean the Java class method which implements the UDF; the method identified in the EXTERNAL NAME clause of the CREATE FUNCTION statement. b. For table functions with NO SCRATCHPAD specified, the calls to the UDF method are as indicated in this table, but because the user is not asking for any continuity via a scratchpad, DB2 will cause a new object to be instantiated before each call, by calling the class constructor. It is not clear that table functions with NO SCRATCHPAD (and thus no continuity) can do very useful things, but they are supported. c. These execution models imply the existence of a new class method which provides the call type. This method is provided, as are numerous other methods, with DB2 in the COM.ibm.db2.app.UDF class, and will be defined as: public int getCallType () throws Exception d. These models are TOTALLY COMPATIBLE with what happens with the other UDF languages: C and C++ and OLE. Defines for the new call types are added to sqludf.h, the UDF include file. UDB customers who have existing table functions are advised to examine the above options and make appropriate changes: 1. If you depend on the new scratchpad rules implied by FINAL CALL then you may have to change your UDF to add the new call types, because with FINAL CALL specified, the new calls will be made to the UDF. Also, any acquired resources should be freed when the FINAL call is handled by the table function. 2. If you wish to use the rules implied by NO FINAL CALL, you are advised to change and rerun your CREATE FUNCTION statement with NO FINAL CALL instead of FINAL CALL, so that your UDF will not get the new calls. (Depending on how the UDF is written to examine the call-type argument, it may or may not give bad results if it receives unexpected calls.) 3. If you have a Java table function, then you will have to change your function to conform to the new execution models in the above table. For either execution model (depending on whether you choose FINAL CALL or NO FINAL CALL), you will note that additional calls are made to the UDF method. This gives additional power to the UDF writer, but means that he or she must distinguish the calls. Use of the new getCallType method in the parent class included by DB2 provides this capability. Detailed information changes will appear in the "SQL Reference" and the "Embedded SQL Programming Guide" (ESPG) when they are next updated. Meanwhile, a "Technote" describing these changes in more detail will be made available in the DB2 Technical Library on the World Wide Web. Use the search argument "FINAL CALL". The following sections describe the detailed changes to the "Embedded SQL Programming Guide". In Chapter 6 of the "Embedded SQL Programming Guide", under the heading "User-Defined Functions (UDF)", in the Example "Table Function Returning Document IDs", "FINAL CALL" should now read "NO FINAL CALL", and in the following paragraph note that FINAL CALL need not be specified for each table function. In Chapter 7 of the "Embedded SQL Programming Guide", under the heading "Interface between DB2 and a UDF", in the section "The Arguments Passed from DB2 to a UDF", in the description for the "scratchpad" argument, the text beginning "The actual..." should be replaced by: For table functions, the scratchpad is initialized as above only for the very first call to the UDF if FINAL CALL is specified on the CREATE FUNCTION. This "very first call" has type FIRST. After this call, the scratchpad content is totally under control of the table function. If NO FINAL CALL was specified or defaulted, then the scratchpad is initialized as above for each OPEN call, and the scratchpad content is completely under control of the table function between OPEN calls. (This can be very important for a table function used in a join or subquery. If it is necessary to maintain the content of the scratchpad across OPEN calls, then FINAL CALL must be specified in your CREATE FUNCTION statement. With FINAL CALL specified, in addition to the normal OPEN, FETCH and CLOSE calls, the table function will also receive FIRST and FINAL calls, for the purpose of scratchpad maintenance and resource release.) Also, in the description of the "call-type" argument, the second sentence should be replaced by the following sentence: For external scalar functions this argument is only present if FINAL CALL is specified in the CREATE FUNCTION statement, but for external table functions it is ALWAYS present. After that first paragraph, add the following new paragraph: Note that even though all the current possible values are listed below, your UDF should contain a switch or case statement which explicitly tests for all the expected values, rather than containing "if A do AA, else if B do BB, else it must be C so do CC" type logic. This is because it is possible that additional call types may be added in the future, and if you don't explicitly test for condition C you will have trouble when new possibilities are added. In the same section, where the values for the "call-type" argument are explained for Table Functions, the text should be amended as follows: o -2 This is the FIRST call, which only occurs if the FINAL CALL keyword was specified for the UDF. The scratchpad is set to binary zeros before this call. Argument values are passed to the table function, and it may choose to acquire memory or perform other one-time only resource initialization. Note that this is not an OPEN call, that call follows this one. On a FIRST call the table function should not return any data to DB2, but it can set SQL-state and diagnostic-message argument return values. o -1 This is the OPEN call. The scratchpad will be initialized if NO FINAL CALL is specified, but not necessarily otherwise. (The rest of the text is unchanged.) o 0 (unchanged) o 1 (The first paragraph is unchanged, but the following text should be added) During CLOSE, the UDF should release resources acquired, if NO FINAL CALL was defined for the UDF. If FINAL CALL was defined, a special call (FINAL) is provided for resource release. On a CLOSE call a table function should not return a row to DB2, and in fact any SQL-result and SQL-result-ind values returned are ignored by DB2 upon return. However, the table function may set SQL-state and diagnostic-message argument values, which are handled as usual by DB2. o 2 This is the FINAL call, which balances the FIRST call, and it only occurs if FINAL CALL was defined for the external table function. All system resources should be freed. This call only occurs once per statement. As usual, SQL-state and diagnostic-message argument values may be returned, and they are handled as usual by DB2. Under the heading "Summary of Argument Use", for the argument "scratchpad and call-type", add the text ", and optionally FIRST and FINAL" in both cases. In Chapter 7 of the "Embedded SQL Programming Guide", under the heading "Table Function Considerations", under the first bullet, there are now five call types instead of three: add FIRST and FINAL to the list. Also, add a new bullet in this section as well, which should read as follows: The error processing model for table function calls is as follows: 1. If FIRST call fails, no further calls are made. 2. If FIRST call succeeds, FINAL call is made. 3. If OPEN call fails, no FETCH or CLOSE call is made. 4. If OPEN call succeeds, then FETCH and CLOSE calls are made. In the "Weather Table Function" example in Chapter 7, the UDF definition should specify NO FINAL CALL, and note that FINAL CALL is no longer mandatory for a table function (that is, ignore the statement that says it is mandatory). In Chapter 15 of the "Embedded SQL Programming Guide", entitled "Programming in Java", under the heading "Creating Java UDFs and Stored Procedures", the second sentence of the second paragraph should be changed to read as follows: Once you create and register these UDFs and stored procedures, and place the Java classes in the correct file location, described in "Where to Put Java Classes" on page 514, you can then (for UDFs) reference them in your SQL, or (for stored procedures) call them from your application written in any supported language. And the sentence following that one should be removed. In Chapter 15 of the "Embedded SQL Programming Guide", under the heading "Creating and Using Java User-Defined Functions", the fourth sentence of the first paragraph should be changed to read: You can then refer to it in the SQL of your application. In the subheading "Changing How a Java UDF Runs" under that same heading, the first three paragraphs should be replaced with the following five: Typically, DB2 calls a UDF many times, once for each row of an input or result set in a query. If SCRATCHPAD is specified in the CREATE FUNCTION statement of the UDF, DB2 recognizes that some "continuity" is needed between successive invocations of the UDF, and therefore the implementing Java class is not instantiated for each call, but generally speaking once per UDF reference per statement. Generally it is instantiated before the first call and used thereafter, but may for table functions be instantiated more often-- see the NO FINAL CALL execution model in the subsection which follows this one. If, however, NO SCRATCHPAD is specified for a UDF, either a scalar or table function, then a clean instance is instantiated for each call to the UDF, by means of calling the class constructor. A scratchpad may be useful for saving information across calls to a UDF. Whereas for C, C++ and OLE UDFs the scratchpad is maintained by DB2, in Java the UDF can simply use instance variables. For table functions using a scratchpad, you can control when you get a new instance by use of the FINAL CALL / NO FINAL CALL option on the CREATE FUNCTION statement, as indicated by the execution models in the subsection which follows this one. For scalar functions, you use the same instance for the entire statement. Another choice you have is to use the DB2 provided Java methods getScratchpad() and setScratchpad(), in which case you will be using the DB2-managed scratchpad. The ability to achieve continuity between calls to a UDF by means of a scratchpad, is controlled by the SCRATCHPAD / NO SCRATCHPAD option of CREATE FUNCTION, regardless of whether the DB2 scratchpad or instance variables are used. Please note that every reference to a Java UDF in a query is treated independently, even if the same UDF is referenced multiple times. This is the same as what happens for OLE, C and C++ UDFs as well. At the end of a query, if you specify the FINAL CALL option for a scalar function then the object's close() method is called. For table functions the close() method will always be invoked as indicated in the subsection which follows this one. If you do not define a close() method for your UDF class, then a stub function takes over and the event is ignored. And in the next paragraph, which begins "If you specify the ALLOW PARALLEL ...", the last sentence should be removed. Following the subsection "Changing How a Java UDF Runs", add a new subsection at the same level, entitled "Table Function Execution Model for Java", which contains the following: For table functions written in Java, it is important to understand what happens at each point in DB2's processing of a given statement which is significant to the table function. The table which follows details this information. The bottom part of each box hints what the code might be written to do for a typical table function which pulls some information in from the Web). Covered are both the NO FINAL CALL and the FINAL CALL cases, assuming SCRATCHPAD in both cases. | | Point | | NO FINAL CALL | FINAL CALL in | | LANGUAGE JAVA | LANGUAGE JAVA scan | | SCRATCHPAD | SCRATCHPAD time V | | ------------+------------------------------+------------------------------ Before the | No calls. | Class constructor is called first OPEN | | (means new scratchpad). for the | | UDF Method is called with table func. | | "FIRST" call. | |- - - - - - - - - - - - - - - | | Constructor initializes | | class and scratchpad | | variables. | | Method connects to the Web | | Server. | | ------------+------------------------------+------------------------------ At each | Class constructor is called | UDF Method is called with OPEN of the | (means new scratchpad). | "OPEN" call. table func. | UDF Method is called with | | "OPEN" call. | |- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - - | Constructor initializes | Method opens the scan for | class and scratchpad | whatever Web data it wants. | variables. | (Might be able to avoid | Method connects to the Web | reopen after a CLOSE repo- | Server, and opens the scan | sition, depending on what | for Web data. | is saved in scratchpad!) | | ------------+------------------------------+------------------------------ At each | UDF method is called with | UDF Method is called with FETCH for | "FETCH" call. | "FETCH" call. new row of |- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - - table func | Method fetches and returns | Method fetches and returns data. | next row of data, or EOT. | next row of data, or EOT. | | ------------+------------------------------+------------------------------ At each | UDF method is called with | UDF Method is called with CLOSE of the| "CLOSE" call. | "CLOSE" call. table func. | "close" method is called if | | it exists for class. | |- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - - | Method closes its Web scan | Method might reposition to | and disconnects from the | the top of the scan, or | Web Server. | close the scan. It can | "close" does not need to do | save any state in the | anything. | scratchpad, which will | | persist. | | ------------+------------------------------+------------------------------ After the | No calls. | UDF Method is called with last CLOSE | | "FINAL" call. of the table| | "close" method is called if func. | | it exists for class. | |- - - - - - - - - - - - - - - | | Method disconnects from the | | Web Server. | | "close" does not need to do | | anything. | | ------------+------------------------------+------------------------------ Some notes on this table: 1. By "UDF Method" we mean the Java class method which implements the UDF- the method identified in the EXTERNAL NAME clause of the CREATE FUNCTION statement. 2. For table functions with NO SCRATCHPAD specified, the calls to the UDF method are as indicated in this table, but because the user is not asking for any continuity via a scratchpad, DB2 will cause a new object to be instantiated before each call, by calling the class constructor. It is not clear that table functions with NO SCRATCHPAD (and thus no continuity) can do very useful things, but they are supported. 3. These execution models imply the existence of a new class method which which enables the UDF to identify the call type. This method is provided with DB2 in the COM.ibm.db2.app.UDF class, and will be defined as: public int getCallType () throws Exception 4. These models are TOTALLY COMPATIBLE with what happens with the other UDF languages: C and C++ and OLE. In Chapter 15 of the "Embedded SQL Programming Guide", under the heading "Classes for Java Stored Procedures and UDFs", and further under the subheading "COM.ibm.db2.app.UDF", the paragraph describing the "public void close()" method should be changed to read as follows: This method is called by the database for scalar functions at the end of a statement evaluation, if the UDF was created with the FINAL CALL option, and is analogous to the final call for a C function. For table functions, it is called after the CLOSE call to the UDF method (if NO FINAL CALL is coded or defaulted), or after the FINAL call (if FINAL CALL is coded). If a Java UDF class does not implement this function, a no-op stub function will handle and effectively ignore this event. Immediately following the description for "public void close()", add material for the new method "public int getCallType() throws Exception", which reads as follows: This function is used by table function UDF methods to find out the call type for a particular call. It returns a value as follows (symbolic defines are provided for these values in the Com.ibm.db2.app.UDF class definition): o -2 FIRST call o -1 OPEN call o 0 FETCH call o 1 CLOSE call o 2 FINAL call | 8.11.2 EXTERNAL UDF ERROR PROCESSING MODEL FOR SCALAR FUNCTIONS | In chapter 7, in the section entitled "Other Coding Considerations", and the | subsection entitled "Hints and Tips", there should be a new item 15 added to | the list as follows: | The error processing model for scalar UDFs which are defined with the FINAL | CALL specification is as follows: | 1. If FIRST call fails, no further calls are made. | 2. If NORMAL call fails, no further NORMAL calls are made, but the FINAL | call is made. | This means that if an error is returned on a FIRST call, the UDF must clean | up before returning, because no FINAL call will be made. | If FINAL CALL is not specified for the scalar UDF, then only NORMAL calls are | made, and they cease if an error is returned. | There should also be a new item 16 added to the list as follows: | The error processing model for table functions is defined in the "Table | Functions Considerations" section of this chapter. | 8.12 NOT ATOMIC COMPOUND SQL | DB2-Connect does not support SELECT statements selecting LOB columns in a | compound SQL block. 8.13 OTHER REVISIONS TO THE BOOK The following changes related to the Embedded SQL Programming Guide have been documented in the Release Notes for FixPaks prior to this release. 8.13.1 REVISIONS TO CHAPTER 1 In chapter 1 of the ESPG, in the first paragraph under the heading "DB2 Bind File Dump Tool-db2bfd", there is a reference to "Version 3"; this should be changed to Version 5. 8.13.2 REVISIONS TO CHAPTER 4 In chapter 4, the section "Join Strategies in a Partitioned Database" has several subsections showing examples of the different join strategies. In the pieces of artwork, all occurrences of "LINEITEMS" should be changed to "LINEITEM". Also, the subsection "Broadcast Inner-Table Joins", has incorrect text in the example shown. The text of the example should be: The LINEITEM table is sent to all database partitions that have the ORDERS table. Table queue q3 is broadcast to all database partitions of the outer table. 8.13.3 REVISIONS TO CHAPTER 7 The following changes pertain to chapter 7: o Section OLE Automation UDFs in C++, at the end of the section, in the code: short indicator1, // input indicator short indicator2, // output indicator should be changed to short *indicator1, // input indicator short *indicator2, // output indicator o Section Hints and Tips, item number 4: "reason code 21" should be changed to "reason code 34". o The following changes pertain to the section entitled "The Arguments Passed from DB2 to a UDF": - For the SQL-state argument, for the sqlstate 02000, it should read: 02000 Only valid for the FETCH call to table functions, it means there are no more rows in the table. - For the dbinfo argument, list item "16. Table function column list (tfcolumn)", replace the first paragraph with the following: If this is a table function, this field is a pointer to an array of short integers which is dynamically allocated by DB2. If this is a scalar function, this pointer is null. o The following changes pertain to the section entitled "Using LOB Locators as Parameters or Results": - For the figure showing (function header) declaration of the APIs, entitled "DB2 Lob Locator APIs defined in sqludf.h", the descriptions of the API parameters have been improved. See the actual sqludf.h file for details. - There are five APIs described in the section. Where the return codes from these APIs are described, "other" is cited as a possibility for each API. For each API, the description of "other" should be changed to the following: other Invalid locator or other error (for example, memory error). The value that is returned for these cases is the SQLCODE corresponding to the error condition. For example, -423 means invalid locator. Please note that before returning to the UDF with one of these "other" codes, DB2 makes a judgement as to the severity of the error. For severe errors, DB2 remembers that the error occurred, and when the UDF returns to DB2, regardless of whether the UDF returns an error SQLSTATE to DB2, DB2 takes action appropriate for the error condition. For non-severe errors, DB2 forgets that the error has occurred, and leaves it up to the UDF to decide whether it can take corrective action, or return an error SQLSTATE to DB2. - Three of the five APIs describe a possible return code of -1, meaning that the locator passed on the call has already been freed. These APIs do not return a -1 return code to the UDF. This condition is handled under the "other" return code using SQLCODE -423 (invalid locator). - In item 1: sqludf_length(), an additional return code value is defined: -3 Bad input value is provided to the API. This includes: - udfloc_p (address of locator) is zero - return_len_p (address of where to put length) is zero - In item 2: sqludf_substr(), an additional return code value is defined: -3 Bad input value is provided to the API. This includes: - udfloc_p (address of locator) is zero - start is less than 1 - length is negative - buffer_p (buffer address) is zero - return_len_p (address of where to put length) is zero - In item 3: sqludf_append(), an additional return code value is defined: -3 Bad input value is provided to the API. This includes: - udfloc_p (address of locator) is zero - length is negative - buffer_p (buffer address) is zero - In item 4: sqludf_create_locator(), an additional return code value is defined: -3 Bad input value is provided to the API. This includes: - udfloc_p (address of locator) is zero - loc_type is not one of the three valid values - loc_p (address of where to put locator) is zero. - In item 5: sqludf_free_locator(), an additional return code value is defined: -3 Bad input value is provided to the API. This includes: - udfloc_p (address of locator) is zero - In item 3: sqludf_append(), add the following paragraphs before the discussion of return codes: This API can be used to build very large LOB values in a piecemeal manner. In cases where a large number of appends is used to build a result, the performance of this task can be improved by: - allocating a large application control heap (APP_CTL_HEAP_SZ is the database manager configuration parameter) - doing fewer appends of larger buffers; for example, instead of doing 20 appends of 50 bytes each, doing a single 1000 byte append. SQL applications which build many large LOB values via the sqludf_append() API may encounter errors caused by limitations on the amount of disk space available. The chance of these errors happening can be reduced by: - using larger buffers for the individual appends - doing frequent COMMITs between statements - in cases where each row of a SELECT statement is building a LOB value via this API, using a CURSOR WITH HOLD and doing COMMITs between rows. - In item 5: sqludf_free_locator(), The paragraph beginning "Typically..." is incorrect. Replace it with: Use this API to free any locators that were created with the sqludf_create_locator() API, and which were used only for internal manipulation. It is NOT NECESSARY to free locators passed into the UDF. It is NOT NECESSARY to free any locator created by the UDF via sqludf_create_locator() if that locator is passed out of the UDF as an output. - After the enumerated coverage of the individual APIs, add the following text: The following notes apply to the use of these APIs: 1. A UDF which is defined to return a LOB locator has several possibilities available to it. It can return: a. an input locator passed to it b. an input locator passed to it which has been appended to via sqludf_append() c. a locator created to via sqludf_create_locator(), and appended to via sqludf_append(). 2. A table function can be defined as returning one or more LOB locators. Each of them can be any of the possibilities discussed in the preceding item. It is also valid for such a table function to return the same locator as an output for several table function columns. 3. A LOB locator passed to a table function as an input argument remains alive for the entire duration of the row generation process. In fact, the table function can append to a LOB using such a LOB locator while generating one row, and see the appended bytes on a subsequent row. 4. The internal control mechanisms used to represent a LOB which originated in DB2 as a LOB locator output from a UDF (table or scalar function), take 1950 bytes. For this reason, and because there are limitations on the size of a row which is input to a sort, a query which attempts to sort multiple such LOBs which originated as UDF LOB locators will be limited to (at most) two such values per row, depending on the sizes of the other columns involved. The same limitation applies to rows being inserted into a table. 8.13.4 REVISIONS TO CHAPTER 9 In chapter 9, in the section "Accessing DRDA Servers", the second paragraph in point 1 should read: For IBM products, consult the IBM SQL Reference, Version 5.2 (S10J-8165), before you start coding. 8.13.5 REVISIONS TO CHAPTER 15 The following changes pertain to chapter 15: o The book states that the user's Java classes must implement the interfaces that are provided. However, since the Java library provided in "sqllib/java/db2java.zip" contains classes, the user classes must extend Java classes of the same name. References to class ... implements COM.ibm.db2.app.UDF { ... } should be changed to class ... extends COM.ibm.db2.app.UDF { ... } The same is true for "COM.ibm.db2.app.StoredProc". o The book states that new LOB values can be created by UDFs and stored procedures by using the following classes/routines: COM.ibm.db2.app.Blob public static Blob new() throws Exception; COM.ibm.db2.app.Clob public static Clob new() throws Exception; These were renamed and moved to the new class COM.ibm.db2.app.Lob as the following functions: COM.ibm.db2.app.Lob public static Blob newBlob() throws Exception; COM.ibm.db2.app.Lob public static Clob newClob() throws Exception; o The Java type mapping table includes the following mappings: SQL Type Java Type (UDF) Java Type (Stored Procedure) LONG VARCHAR Clob Clob LONG VARGRAPHIC Clob Clob These should be changed to: LONG VARCHAR String String LONG VARGRAPHIC String String The following information should be added after the second paragraph in the section "Java UDFs and Stored Procedures": When creating a stored procedure in the Java language, you must use the CREATE PROCEDURE statement to register the procedure to the system catalog table SYSCAT.PROCEDURES. For more information, refer to the section on the CREATE PROCEDURE statement in the SQL Reference. The following is a limitation of the Java UDF/stored procedure support: On a mixed code page database server, Java user-defined functions and stored procedures cannot use CLOB type arguments, because random access on character boundaries on large mixed code page strings has not yet been implemented. Full support for all LOB types is intended for SBCS databases. For mixed databases, support is intended for the BLOB and the DBCLOB types. As a workaround, applications running on a mixed database system should convert CLOB arguments to DBCLOB, LONG VARGRAPH, or LONG VARCHAR types. For UDFs, this can be done with the CAST operator. In appendix B, which describes the sample programs, references (in table 24) to the loblocud, db2uext2, and autoloader samples should be removed. For additional information, also refer to 8.13.6, "Revisions to Sample Programs" and 8.13.7, "Other File Changes". In the index, java_heap_sz is incorrectly referred to as java_heap_size. 8.13.6 REVISIONS TO SAMPLE PROGRAMS Revisions to sample programs were made for various languages on the following platforms: AIX, HP-UX, OS/2, Solaris, Windows NT, and Windows 95. The revisions are to correct some code errors and to make better use of API calls within the programs. Some programs have been made generic for all platforms: before they were only available on specific platforms; for example, 'makeapi.sqc'. Also, the interface has been improved for several programs to better guide the user on program input, and to give more informative output. Many redundant files have had their file contents deleted, replaced by the comment "This file is not needed". Where a new file replaces the redundant file's functionality, a reference to this is also provided. The following is a list of the updated files. Each entry contains the name of the file, its language, and the platforms for which it has been changed. Where the same file has been revised in more than one language, one file name is given with multiple extensions, as in "dbconf.c/.cbl/.f/.for". "COBOL" refers to both IBM and Micro Focus COBOL, unless otherwise indicated. "All platforms" refers to the following platforms only: AIX, HP-UX, OS/2, Solaris, Windows NT, and Windows 95. In some situations, a file may not be available on a specific platform; if you are not sure, refer to the README file in the appropriate samples directory. The file entry also contains a comment describing the nature of the change. The changes are as follows: adhoc.sqc C for all platforms Fixed memory leak and improved user interface. blobfile.cmd REXX on AIX, OS/2, and Windows NT The correct file version is now available for OS/2. dbconf.c/.cbl/.f/.for C, COBOL, FORTRAN for all platforms. Fixed 'version' parameter error in API call. Improved the user interface. Recoded Micro Focus COBOL version to make it generic for all platforms. d_dbconf.cbl Micro Focus COBOL for all platforms. Recoded to make generic for all platforms. d_dbmcon.cbl Micro Focus COBOL for all platforms. Recoded to make generic for all platforms. da_manip.h/.sqc C for all platforms Redundant files: contents deleted. dbcat.f/.for FORTRAN for all platforms Fixed 'version' parameter error in API call. dbmconf.cbl/.cmd Micro Focus COBOL for all platforms and REXX on AIX, Windows NT, and OS/2 Recoded Micro Focus COBOL version to make it generic for all platforms. The correct REXX version is now available for OS/2. dbinst.for FORTRAN for OS/2 Improved user interface. dbsnap.cbl IBM COBOL for all platforms Fixed 'version' parameter error in API call. dbstat.sqb Micro Focus COBOL for all platforms Fixed 'version' parameter error in API call. db_udcs.for FORTRAN for OS/2 This program is now available for OS/2 FORTRAN. ebcdicdb.for FORTRAN for OS/2 This program is now available for OS/2 FORTRAN. fillcli.sqc C for all platforms This is a client program; the server program is fillsrv.sqc. These programs have been extensively rewritten, combining the functionality of the supporting programs da_manip, system, and tabinfo (which are now redundant). The use of APIs, demonstration of SQLDA structures, and user interface have all been improved. fillsrv.sqc C for all platforms This is a server program; the client program is fillcli.sqc (see that entry for more information). inpsrv.def Micro Focus on OS/2 Redundant file: contents deleted, as compiler does not need definition file for stored procedures. inpcli.old C for all platforms Redundant file: contents deleted. inpsrv.old C for all platforms Redundant file: contents deleted. largevol.sqc C for all platforms This program is AIX-specific: file contents were deleted on the HP-UX, OS/2, Solaris, Windows NT, and Windows 95 platforms. lobeval.cmd REXX on AIX, Windows NT and OS/2 The correct version is now available for OS/2. makeapi.sqc C for all platforms This replaces the specific program files for AIX and OS/2 with a generic program file that is now available for all platforms. monreset.f/.for FORTRAN program for all platforms Fixed 'version' parameter error in API call. monsz.cbl IBM COBOL for all platforms Fixed 'version' parameter error in API call. outsrv.def Micro Focus COBOL on OS/2. Redundant file: contents deleted, as compiler does not need definition file for stored procedures. inpcli.old C for all platforms Redundant file: contents deleted. inpsrv.old C for all platforms Redundant file: contents deleted. qload.sqb/.sqf/.sqc COBOL, FORTRAN, and C for all platforms Improved interface provides more information and eliminates extraneous 'load file' error. rechist.c/.sqc C for all platforms The non-embedded SQL program rechist.c has been replaced by the embedded SQL program rechist.sqc. The new file corrects a database connect error that occurred with rechist.c. sws.cbl IBM COBOL for all platforms Fixed 'version' parameter error in API call. system.c/.h C for all platforms Redundant files: contents deleted. tabinfo.c/.h C for all platforms Redundant files: contents deleted. tabspace.sqb/.sqf COBOL and FORTRAN for all platforms Improves use of APIs to now demonstrate multiple table spaces. tblsrv.c C for all platforms Corrected error in API call to shared library. 8.13.7 OTHER FILE CHANGES The makefiles, where needed, have been updated to reflect the changes described in the previous section. For example, the C makefiles no longer create and link in the object files da_manip.obj, system.obj and tabinfo.obj for the stored procedure fillsrv. The OS/2 command files for IBM COBOL and FORTRAN, and their respective makefiles, now use a stack size of 64000: the stack size link option for COBOL is now "/ST:64000", and for FORTRAN it is now "stack=64000". It was found that some of the stored procedure programs needed a larger stack size to run correctly. The following command and batch file changes have been made: BLDF32.CMD FORTRAN for OS/2 Redundant file: contents deleted. EMBPREP.BAT COBOL for Windows NT and Windows 95 The correct version of this file is now available. 9.0 INSTALLING AND CONFIGURING DB2 CLIENTS 9.1 CONFIGURING CLIENT TO DB2 SERVER COMMUNICATIONS USING THE CCA A remote system may not be detected if the client is on a different token ring than the server. Both the remote system and the system that initiates the discovery request must be on the same token ring in order for search discovery to work. The TCP/IP host name returned to a client by SEARCH discovery is the same host name that is returned by the DB2 server system when a TCP/IP host name command is issued. On the client, the IP address that this host name maps to is determined by either the TCP/IP domain name server (DNS) configured on the client machine or, if a DNS is not configured, a mapping entry in the client hosts file. If multiple adapter cards are configured on the DB2 server system, ensure that TCP/IP is configured on the server to return the correct host name when a host name command is issued, and that the DNS or local client hosts file on the client maps the host name to the desired IP address. 10.0 MESSAGES REFERENCE 10.1 ERROR IN DESCRIPTION OF DBI1768W Under the "Action" section, references to "sqllib/profile" should be changed to "sqllib/db2profile". 10.2 CHANGED/ADDITIONAL REASON CODES FOR SQL0270N The following reason code for message SQL0270N has been changed: Under "Cause": 23 REPLICATED can only be specified for a summary table. Under "Action": 23 Either remove the REPLICATED specification or ensure that the CREATE TABLE is specified in a summary table definition. The following two reason codes have been added to message SQL0270N: Under "Cause": 30 Typed tables and typed views cannot be defined in a CREATE SCHEMA statement. 31 A partitioning key cannot be defined with more than 500 columns. Under "Action": 30 Issue the CREATE statement for the typed view or typed table outside of the CREATE SCHEMA statement. 31 Reduce the number of columns in the partitioning key. 10.3 NEW DESCRIPTION FOR SQL0351N Message: LOB or DATALINK data in position "" of the select-list cannot be sent using DRDA protocol. Cause: LOB or DATALINK data, or distinct types based on LOB or DATALINK data, cannot be flowed using the DRDA protocol. "" is the position of the element with a LOB or DATALINK data type in the select-list. The statement cannot be processed. Action: Ensure that LOB or DATALINK data is not being retrieved when using DRDA, by excluding them from the select-list. For LOB data, try selecting smaller pieces of the value by using CHAR(SUBSTR(CLOB)). For DATALINK data, try using a function such as DURLCOMPLETE. sqlcode: -351 sqlstate: 56084 10.4 NEW DESCRIPTION FOR SQL0352N Message: LOB or DATALINK data in position "" of the input-list cannot be sent using DRDA protocol. Cause: LOB or DATALINK data cannot be flowed using the DRDA protocol. "" is the position of the element with a DATALINK data type in the input-list. The statement cannot be processed. Action: Ensure that LOB or DATALINK data is not being sent when using DRDA, by excluding it from the input-list. An alternative is to try building a value indirectly using the DLVALUE function. sqlcode: -352 sqlstate: 56084 10.5 NEW DESCRIPTION FOR SQL0357N Message: The DB2 File Manager "" is not currently available. Reason code = "". Cause: A reference to a DATALINK value required access to the DB2 File Manager "". The DB2 File Manager is not currently available as indicated by the reason code. 01 The DB2 File Manager in a DATALINK value is not available. 02 The database server, instance, or database from which the operation was attempted is not registered with the DB2 File Manager in a DATALINK value. 03 Restart recovery is pending or in progress on the DB2 File Manager. 04 The DB2 File Manager in a DATALINK value is registered with the database but is an unknown server. 05 An error occurred while communicating to the DB2 File Manager in a DATALINK value. The statement cannot be processed. Action: The action depends on the reason code as follows. 01 The DB2 File Manager or a communication link may be down. Wait a while and try again, or check with a system administrator for the DB2 File Manager. If the problem persists, disconnect the application from the database, and retry after connecting again. 02 Register the database server, instance, or database with the DB2 File Manager. 03 Wait for the restart recovery to complete on the DB2 File Manager. If the problem persists, check if the registration mentioned in the text for reason code 02 above has been done. This might be one of the reasons for the restart recovery not completing successfully. 04 Check that the DB2 File Manager configured to the database is available on the network. 05 Check that the DB2 File Manager and the communication link are up. If the problem persists, disconnect the application from the database, and retry after connecting again. sqlcode: -357 sqlstate: 57050 10.6 NEW DESCRIPTION FOR SQL0432N The description for SQL0432N has been changed to include the same error for reference types: Message: A parameter marker cannot have the user-defined type name or reference target type name "". Cause: A parameter marker in the statement has been determined to have the user-defined type "", or a reference type with the target type "", based on the context in which it is used. A parameter marker cannot have a user-defined type or reference type as its data type, unless it is part of an assignment (VALUES clause of INSERT, or SET clause of UPDATE), or it is being explicitly cast to a user-defined distinct data type, or reference data type, using the CAST specification. The statement cannot be processed. Action: Use an explicit cast to the user-defined distinct data type, or reference data type for the parameter marker. An alternative is to cast the columns that are user-defined distinct data types to their corresponding source data type, or columns that are reference data types to their corresponding representation type. sqlcode: -432 sqlstate: 42841 The text for SQLSTATE 42841 has been changed to: 42841 A parameter marker cannot be a user-defined type or a reference type. 10.7 NEW DESCRIPTION FOR SQL0680N Modified Cause: The maximum number of columns permitted for each table is based on the page size of the table space. The limits for tables are: o 4KB page size allows 500 columns o 8KB page size allows 1012 columns. The number of columns may have exceeded the maximum allowed in a temporary table with any of the temporary table spaces available to process the statement. New Action: If the error occurs processing a query, create a temporary table space of the largest page size supported if one does not already exist. If such a table space already exists, eliminate one or more columns from the temporary table. 10.8 ADDITIONAL REASON CODE FOR SQL0903N Reason code 3 for message SQL0903N has been changed to: 3. One of the DB2 File Managers participating in the unit of work was unable to prepare to commit. 10.9 ADDITIONAL REASON CODE FOR SQL0911N There is a new reason code for message SQL0911N: 72. Transaction has been rolled back because of an error concerning a DB2 File Manager involved in the transaction. 10.10 NEW DESCRIPTION FOR SQL1339N The following information has been added to the "Cause" section: The error tokens will not be returned for CLI/ODBC applications. CLI/ODBC applications can use SQLGetDiagRec, SQLGetDiagField, or SQLError APIs to get more information about each error. 10.11 SQL5048N OR SQL0901N WHEN USING CHANGE PASSWORD SUPPORT If message SQL5048N or SQL0901N is encountered when using DB2 Version 5.2 Change Password support, the most likely cause of the error is that the release level of the database client is not supported by the release level of the database server. These messages may be returned from a CONNECT or ATTACH request for which a new password was specified and AUTHENTICATION=CLIENT is being used. If so, verify that the local database catalog entry for the named database alias does not specify AUTHENTICATION=SERVER. If the above errors are returned from a CONNECT OR ATTACH request with a new password for a DB2 Common Server Version 2 or DDCS Version 2 multi-user gateway, the local database catalog entry for the database alias must specify AUTHENTICATION=CLIENT. | 10.12 NEW MESSAGE SQL5156N: VALUE OF DBM CONFIGURATION PARAMETER | TRUST_ALLCLNTS | SQL5156N The value of the database manager configuration parameter | "TRUST_ALLCLNTS" must be one of NO, YES, or DRDAONLY. | Cause: The allowed values for the configuration parameter "TRUST_ALLCLNTS" | are: | 1. NO = 0 | 2. YES = 1 | 3. DRDAONLY = 2 | The requested change is not made. | Action: Resubmit the command with a valid value for "TRUST_ALLCLNTS". 10.13 ADDITIONAL REASON CODE FOR SQL20053N The following reason code has been added to message SQL20053N: Under "Cause": 6. The table or view in the FROM clause of every subview in a typed view hierarchy must use OUTER if any subview uses OUTER. Under "Action": 6. If this is the first subview to use OUTER, change the FROM clause so that OUTER is not used. If the superview uses OUTER, include OUTER in the FROM clause of the subview. 10.14 NEW MESSAGE TEXT FOR SQL20056N Following is the updated message text for SQL20056N: Processing on DB2 File Manager "" encountered an error. Reason code = "". Additional diagnostic information "". NOTE: Currently "" will always be set to "UNKNOWN". 10.15 ADDITIONAL REASON CODE FOR SQL30083N The following reason code for change password support, when it fails because of an invalid user name or password, has been added to message SQL30083N: Under "Cause": 24. Username and/or password invalid. This is returned when a change password fails due to username or original password errors. 11.0 QUICK BEGINNINGS 11.1 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. 11.2 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. 11.3 PLANNING FOR INSTALLATION 11.3.1 SOFTWARE REQUIREMENTS/SERVER PRODUCT REQUIREMENTS Since HP is discontinuing support for NETWARE products, IPX/SPX is not supported on HP-UX Version 11.0. 11.3.2 CLIENT PRODUCT REQUIREMENTS/SOFTWARE REQUIREMENTS FOR OS/2 CLIENTS PTF (Program Temporary Fix) 3 for ADSTAR Distributed Storage Manager (ADSM) Version 3 is required for an OS/2 client to work with DB2. | 11.3.3 CONTROL CENTER REQUIREMENT ON OS/2 WARP SERVER V.4 IN DBCS | ENVIRONMENT | If you are running DB2 UDB for OS/2 on OS/2 Warp Server, Version 4 in a | Double Byte Character Set (DBCS) environment, double-clicking on the Control | Center icon returns system error SYS3175. However, after the error occurs | once, if you double-click on the icon again, the Control Center opens. | To stop the SYS3175 message from coming up in the first place, install OS/2 | FixPak WX03005 or a later version. 11.3.4 DB2 FOR AS/400 SNA CONNECTIONS TO DB2 UNIVERSAL DATABASE FOR AIX, OS/2, AND WINDOWS NT Two phase commit using SNA is not supported when connecting from DB2 for AS/400 to DB2 Universal Database Version 5.2 for AIX. It is supported when connecting from DB2 for AS/400 to DB2 Universal Database Version 5.2 for OS/2 and Windows NT. | 11.3.5 TP NAME MUST BE UNIQUE AMONG DB2 INSTANCES ON THE SAME MACHINE | The TP Name has to be unique among DB2 instances residing on the same | machine. If this is not the case, for more than one DB2 instance listening to | the same TP Name, both APPC and TCPIP will assign the first connection to the | correct DB2 instance and will assign the second connection to the incorrect | DB2 instance. 11.3.6 SOFTWARE PREREQUISITES/DB2 FOR OS/390 RECOMMENDED FIXES When accessing a DB2 Universal Database server from a DB2 for OS/390 host the following DB2 for OS/390 PTFs are required: o PTF UQ06843 for APAR PQ05771 o PTF UQ09146 for APAR PQ07537 11.4 GETTING STARTED WITH DB2 UNIVERSAL DATABASE (CHANGING PASSWORDS) The ability to change a password at a DB2 server from a DB2 client is limited to the following DB2 servers: o DB2 for OS/390 V5 or later o DB2 Universal Database for AIX V5.2 o DB2 Universal Database for OS/2 V5.2 o DB2 Universal Database for Windows NT V5.2 11.5 GETTING STARTED WITH DB2 UNIVERSAL DATABASE (COUNTING CONCURRENT USERS ON AIX) Chapter 15 of the Quick Beginnings for UNIX** book refers to the Nodelock Administration Tool (NAT), which is part of the License Use Management Administration Runtime Kit (LUM ARK). To count concurrent users on AIX, the LUM ARK must be installed on your system with the NAT. On some versions of LUM ARK, the NAT is in an optional file set. Please refer to your LUM documentation for more information. You can visit the IBM License Use Management Web site at: http://www.software.ibm.com/is/lum to download the latest LUM ARK and publications. For your convenience, a copy of the LUM ARK is included on the DB2 CD in the /cdrom/LUM directory. 11.6 WORKING WITH INSTANCES (ADDING A DATABASE PARTITION SERVER TO AN INSTANCE, USING DB2 EXTENDED ENTERPRISE EDITION FOR WINDOWS NT/DOMAIN USER ID REQUIREMENT FOR USING RAW DEVICE) The Quick Beginnings for DB2 Extended Enterprise Edition for Windows NT book states that a domain user name is required to create a database partition server, and is used to log on when DB2 is started as a Windows NT service. Note, however, that if raw devices are used, the domain user name must also have local administrative authority, or the user will not have access to the raw devices. | 11.7 DB2START ON 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. 11.8 MIGRATING FROM PREVIOUS VERSIONS AND RELEASES As a result of optimizer enhancements, the plan for any query could change, if rebound. Although every precaution is taken to ensure that the changed plan is at least as good as the plan chosen by the previous version, there is no guarantee of that, since certain changes could cause a different plan to be chosen, and it is not possible to test the virtually unlimited number of scenarios that a user could create. It is recommended that an EXPLAIN be run on all critical queries before migrating versions, in case those queries get rebound to a new plan. If this is done, both the plan and the configuration parameters upon which it is based (under the old release) will be known. This, in turn, will help the user identify the source of any differences under the new version, and restore the conditions for the old plan, if desired. | 11.9 UPGRADING FROM DB2 RELEASE 5.0 TO RELEASE 5.2 | The following information on the "db2upd52" command needs to be added to the | Quick Beginnings books that were not refreshed for DB2 Version 5.2. The books | are: | DB2 Extended Enterprise Edition for UNIX Quick Beginnings | DB2 Personal Edition Quick Beginnings | Quick Beginnings for OS/2 | Quick Beginnings for Windows NT | UPDATING CATALOG TABLE DATA +------------+--------------------------------------------------------------+ | | | You only need to run the DB2UPD52 command if you are | | | | upgrading from Version 5 to Version 5.2 of DB2. If you | | | | upgrade from Version 2 to Version 5.2 of DB2, database | | | | migration will update the data types and the SYSFUN schema. | +------------+--------------------------------------------------------------+ | For DB2 Version 5.2, the SYSIBM.SYSDATATYPES catalog table includes a new | data type, "BIGINT", and other associated functions in the SYSFUN schema. To | support these new features, we recommend that you run the DB2UPD52 command | for each database owned by a DB2 instance being upgraded to Version 5.2. The | command adds the new SYSFUN functions signatures that support the "BIGINT" | data type as follows: | o Add "SYSIBM.BIGINT" to the SYSIBM.SYSDATATYPES catalog table. | o Add the following function signatures to the SYSIBM.SYSFUNCTIONS table: | create function sysfun.abs(bigint) returns bigint ... | create function sysfun.absval(bigint) returns bigint ... | create function sysfun.ceil(bigint) returns bigint ... | create function sysfun.ceiling(bigint) returns bigint ... | create function sysfun.floor(bigint) returns bigint ... | create function sysfun.mod(bigint,bigint) returns bigint ... | create function sysfun.power(bigint,bigint) returns bigint ... | create function sysfun.round(bigint,integer) returns bigint ... | create function sysfun.sign(bigint) returns bigint ... | create function sysfun.trunc(bigint,integer) returns bigint ... | create function sysfun.truncate(bigint,integer) returns bigint ... | The DB2UPD52 command also adds the "REAL" data type to the | SYSIBM.SYSDATATYPES catalog table and the function signature for UCASE | (SYSFUN.UCASE) to the SYSIBM.SYSFUNCTIONS table, if it is missing. For | information on the syntax of the DB2UPD52 command, refer to the Command | Reference. | For more information about the "db2upd52" command, refer to Quick Beginnings | for DB2 Universal Database for UNIX Version 5.2, Chapter 6, "Upgrading from | DB2 Release 5.0 to Release 5.2". | 11.10 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 | Also 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 UDB Version 5.2 files that are | required. 11.11 RUNNING YOUR OWN APPLICATIONS (RUNNING JAVA PROGRAMS) DB2 does not support Java stored procedures and Java user-defined functions (UDFs) accessing DB2 databases on HP-UX and SCO UnixWare servers. 11.12 CONTENTS OF THE DB2 PRODUCTS (ABBREVIATED NAMES FOR LOCALES) The abbreviated name of the Brazilian Portuguese locales for DB2 for Solaris and DB2 for SCO UnixWare 7 has been changed from "pt" to "pt_BR". 12.0 QUICK BEGINNINGS FOR FILE MANAGER | 12.1 CLIENT CONFIGURATION IMPORT AND EXPORT | Client Configuration Export Profiles created at clients with FixPak 7 | installed will only be importable at clients which also have FixPak 7 | installed. | 12.2 DB2 DATA LINKS MANAGER CANNOT BE INSTALLED ON A DB2 ENTERPRISE - | EXTENDED EDITION SYSTEM | DB2 Data Links Manager (formerly known as "DB2 File Manager") can only be | used with DB2 for AIX Enterprise Edition. The Data Links Manager cannot be | used with databases on operating systems other than AIX, and cannot be used | with DB2 Enterprise - Extended Edition (EEE) for AIX. DATALINK values cannot | point to a DB2 EEE system; moreover, the DATALINK data type cannot be used on | EEE systems to reference files on a DB2 Data Links Manager. 12.3 DLFM ADMINISTRATION USER ID The DLFM administration user ID must be "dlfm" for this release. 12.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 12.5 RESTRICTIONS PERTAINING TO DATALINKS CLIENTS Datalinks clients cannot be: o On the Windows 3.1 operating system. o Any level below DB2 Version 5.2, except the client for AIX, which can also be Version 5.0 at FixPak U453782 or higher. 12.6 CHANGED SETUP OPTION FOR DLFM The command for DLFM setup has been changed to accept a dlfm_db configuration file. If the configuration file contains the setup options, the configuration options in the file are used instead of the defaults. The file is optional. If not specified, the defaults are used. The command syntax is now as follows: dlfm setup [] Following is a sample configuration file named dlfm_db.cfg: # Sample dlfm_db.cfg file for DLFM setup # Please note: the equal sign is needed between the option name and the value. # Any of the updateable DB2 database configuration parameters can be included in this file. # There are also three special DLFM-specific options which the user can specify: # DLFM_DB_DIR : directory where the dlfm_db database is to be created # DLFM_DB_CATTBL_DIR : directory where the catalog table space is to be located # DLFM_DB_TMPTBL_DIR : directory where the temporary table space is to be located # If there are no values for these options, or the options are not present, # default values are used. #################### DLFM_DB_DIR= DLFM_DB_CATTBL_DIR= DLFM_DB_TEMPTBL_DIR= LOCKTIMEOUT=60 # Default and recommended value for dlfm_db is 60 LOGFILSIZ=2000 # Default and recommended value for dlfm_db is 2000 LOGPRIMARY=6 # Default and recommended value for dlfm_db is 6 MAXAPPLS=128 # Default and recommended value for dlfm_db is 128 MAXLOCKS=100 # Default and recommended value for dlfm_db is 100 12.7 RECOVERING FROM A DISK CRASH ON THE FILE MANAGER SERVER This section describes how to perform recovery after a disk crash on the File Manager Server, and the backup strategies that the administrator must undertake to support such disaster recovery scenarios. NOTE: In the case of a machine crash on a DB2 File Manager, DB2 applications interacting with the DB2 File Manager can get hung. Use the FORCE APPLICATION command to force such applications off the system. In the case of a machine crash on a DB2 server, the affected DB2 File Managers should also be shut down, using the "dlfm shutdown" command. They can then be brought up again. If the disk containing user data crashes, all user files, along with the directory hierarchy of the file system, are destroyed. To recover from such a scenario, the administrator must make periodic backups of the file system containing the user data and directory hierarchy, so that it can be restored. The restored file system must preserve directory and file ownerships, and time stamps. After restoring the file system, the directory structure must be brought up to the point-in-time of the crash by applying the directory changes that occurred after the file system backup was taken. After this step, the RECONCILE command must be run on all tables containing files on the damaged disk. A utility, "db2_reconcile_aid" (located in sqllib\misc), is provided to simplify this task, and is described below. NOTES: 1. Files that are in linked state, and have the RECOVERY NO option set, are treated as follows: o If the file is not found on the file system, the DATALINK value will be set to NULL. o If the file is found, and it has READ and WRITE PERMISSION FS, no additional checks will be made to validate the correctness of the file. o If the file is found, and it has WRITE PERMISSION BLOCKED, its modification time and file size will be checked. If there is a mismatch in the values, the DATALINK value will be set to NULL. 2. Files that are in a linked state, and have the RECOVERY YES option set will be treated as follows: o If the file is not found, or if the file modification time is less than the file modification time at link time, the correct version of the file will be restored from the archive server. o If the file modification time is greater than the file modification time at link time, the DATALINK value will be set to NULL. 3. Files that are in the unlinked state on the file server are not restored or checked for correctness. BACKING UP AND RESTORING THE FILE SYSTEM The following instructions are specific to AIX Version 4.1.5, and may not apply to other operating systems. Users must be logged in as root to perform backup or restore operations. BACKUP The backup should be made in Version 3 inode format: 1. Unmount the file system to be backed up. System Storage Management --> File Systems --> Unmount a File System 2. Backup the file system. System Storage Management --> File Systems --> Backup a File System An incremental backup strategy could be used to reduce recovery time. Level 0 refers to a full backup, and levels 1 through 9 refer to incremental backups. A level n backup backs up only those files that have changed since a level (n-1) backup. After a level n backup, the next backup to be taken will be a level (n+1) backup. A backup can also be invoked from the command line: % backup - -uf Examples: For a full backup use: % backup -0 -u -f/dev/rmt0 /dlfs1 For the next incremental backup use: % backup -1 -u -f/dev/rmt1 /dlfs1 The incremental backup after this will be: % backup -2 -u -f/dev/rmt2 /dlfs1 3. Mount the file system that was just backed up. System Storage Management --> File Systems --> Mount a File System RESTORE A restore should be performed using one of the backups taken previously: 1. Mount the device where the file system is to be restored. System Storage Management --> File Systems --> Mount a File System 2. Restore the file system. The restore command recognizes the format of the backup. System Storage Management --> File Systems --> Restore a File System A restore can also be invoked from the command line. From the directory where the restore is to be done (that is, the mount point for the file system device), issue the following command: % restore -r -f If an incremental backup strategy is being used, restore the newest level 0 backup, then restore the level 1 backup taken after the level 0 backup. Restore the level n backup taken after the level (n-1) backup. Ensure that the file "restoresymtable" in the root of the file system remains intact between restorations of incremental backups. ATTENTION: Do not use the "-M" option during restoration. The "-M" option changes the time stamp of the files to the time that the file system was restored. If the backup or restore operation is being done through ADSM, a full backup by inode needs to be made after a restore operation to overwrite any previous backups. Dedicated media should be used when making backups. RESTORING ONLY THE DIRECTORIES The user can interactively restore files using the "-i" option on the restore command: % restore -i -f This provides the user with a list of directories or files in the backup, and takes the user into an interactive shell. Specific directories can be restored using the "-h" option. BRINGING THE FILE SYSTEM DIRECTORY HIERARCHY TO THE CURRENT POINT IN TIME The directory changes are logged in the file (err_log)/fsysadm.log, where (err_log) is the error log directory as specified in the /etc/sylog.conf file. This file is appended to, and will not be truncated. There is one entry for each event. Setting the attributes of a file is also logged. The format of the entry is: Time = EUID = UID = GID = Mode = Action = Object type = Path = where: Time is the time of the activity in local time. EUID is the effective user ID of the user performing the action. UID and GID are the user ID and group ID attributes of the file/directory that was created, or whose attributes were modified. Mode is the octal representation of the mode of the file/directory. Action can be CREATE (the file/directory was created), REMOVE (the file/directory was removed), or SETATTR (the mode of the file/directory was modified by the user). Object type can be DIR (directory), or FILE (file). Path is the fully qualified path of the file/directory. RUNNING RECONCILE AFTER RESTORING A FILE SYSTEM The db2_reconcile_aid utility provides a mechanism for checking and running RECONCILE on tables that are potentially inconsistent with the DATALINK file data on the file server after a disk failure on the file server. The syntax is as follows: db2_reconcile_aid -check -db [-server_name ] [-reportdir ] where: -check: List the tables that may need reconciliation. No reconcile operation will be performed. db_name: Name of the database for which the reconcile operation needs to be performed. fileserver_name: Name of the DLFM server for which the reconcile operation is to be performed. If no name is provided, all the file servers will be reconciled. report_directory: Directory containing a report for each of the reconcile operations. For each table on which reconcile was performed, files of the following format will be created: .., where is the schema of the table, is the table name, and is .ulk or .exp. The .ulk file contains a list of files that were unlinked on the file server, and the .exp file contains a list of files that were in exception on the file server. For more information, see the description of the RECONCILE command in the Command Reference. | 12.8 DATALINK NUMBER OF BACKUPS (DL_NUM_BACKUP) | CONFIGURATION TYPE Database | PARAMETER TYPE Configurable | DEFAULT [RANGE] 3 [ 1 - 31 ] | This parameter specifies the number of the most recent DB2 backups (both | database and table space) for which a File Manager keeps backup information. | Files that are unlinked from the database are garbage collected based on this | value. A value of one (1) means that the unlinked files are garbage collected | at the completion of the next DB2 backup. | The default value for this parameter is one (1) backup. | This parameter applies to the DATALINK columns which specify "Recovery=Yes." 12.9 DB2 FILE MANAGER RECOVERY SCENARIOS As with any system, it is possible for a failure to occur that requires a certain level of recovery. DB2's File Manager introduces a new level of complexity to several of the recovery scenarios. This section lists several possible failure scenarios and the steps required to recover from them. DEFINITIONS DLFS file system - Registered prefix (/dlink) DLFM backup directory - Directory where files are backed up (/home/dlfm/dlfm_backup) DLFM home directory - Home directory of DLFM user ID (/home/dlfm) DLFM DB2 database - DB2 database that contains all meta-data (DLFM_DB) DB2 database - Registered database that contains DATALINK data type (CROWN) RECOMMENDED THINGS TO DO PRIOR TO A CRASH Place the DLFM database (DLFM_DB), the DLFS file systems, the DLFM backup directory, and the DLFM home directory on different file systems. Ensure they do not share disks. Back up the DLFS file system and the DLFM backup directory on a storage manager, such as IBM's ADSM. This will afford added protection from disk failure, in the event one occurs. Have one database associated with one or more DB2 File Managers. Avoid having two databases associated with one DB2 File Manager; otherwise, certain recovery scenarios will become more complex than is necessary. Perform full database backups of the DB2 databases (as well as the DLFM_DB database on the DB2 File Manager) at regular intervals. WHAT CAN FAIL? Following are four possible failure components which require recovery: o The DB2 database containing a table which has a DATALINK column o The DB2 File Manager's database (DLFM_DB) o The DB2 File Manager's DLFS file system o The DB2 File Manager's backup directory DB2 File Manager recovery scenarios will be discussed for each of these components. The following recovery scenarios may require operations to be performed on both the DB2 node and the DB2 File Manager node. Each step will be preceded by either "DB2:" or "DLFM:" to indicate the node on which the operation must be completed. 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: FAILURE: DB2 database is lost or dropped; DB2 backup and log files are available. RECOVERY STEPS: NODE OPERATION DB2: db2 "restore database crown" DB2: db2 "rollforward database crown to end of logs" (End of logs is always recommended over point-in-time.) DB2: db2 "reconcile table datalinktable" SCENARIO #1B: FAILURE: DB2 database was explicitly dropped; DB2 backup and log files are available. RECOVERY STEPS: Ensure that the drop database operation is complete, and that all files associated with that database have been unlinked. NODE OPERATION DLFM: db2 "connect to dlfm_db" DLFM: db2 "select dbid, dbname, dbinst, hostname from dfm_dbid" DLFM: db2 "update dfm_dbid set action=5 where dbid=x'35B3D7BE0006BF7B'" (Set dbid equal to value found in previous step.) DB2: db2 "restore database crown" DB2: db2 "rollforward database crown to end of logs" (End of logs is always recommended over point-in-time.) DB2: db2 "reconcile table datalinktable" (Mandatory) SCENARIO #2: FAILURE: DLFM_DB is lost; backup, and all log files for the DLFM_DB database are available. RECOVERY STEPS: NODE OPERATION DLFM: db2 "restore database dlfm_db" DLFM: db2 "rollforward database dlfm_db to end of logs" SCENARIO #3: FAILURE: DLFM_DB is lost; backup of the DLFM_DB database is available, but not all log files are available. RECOVERY STEPS: NODE OPERATION DLFM: db2 "restore database dlfm_db" DB2: db2 "reconcile table datalinktable" SCENARIO #4: FAILURE: DLFS file system is lost. RECOVERY STEPS: NODE OPERATION DLFM: Restore the DLFS file system from your storage manager. DB2: db2_recon_aid crown SCENARIO #5: FAILURE: DLFM backup directory is lost. RECOVERY STEPS: NODE OPERATION DLFM: Restore the DLFM backup directory from your storage manager. SCENARIO #6: FAILURE: DLFS file system is lost; DLFM backup directory is lost. RECOVERY STEPS: NODE OPERATION DLFM: Restore the DLFM backup directory from your storage manager. DLFM: Restore the DLFS file system from your storage manager. DB2: db2_recon_aid crown SCENARIO #7: FAILURE: DLFM_DB is lost; backup, and all log files for the DLFM_DB database are available. DLFS file system is lost; DLFM backup directory is lost. RECOVERY STEPS: NODE OPERATION DLFM: db2 "restore database dlfm_db" DLFM: db2 "rollforward database dlfm_db to end of logs" DLFM: Restore the DLFM backup directory from your storage manager. DLFM: Restore the DLFS file system from your storage manager. (You need the directory structure.) DB2: db2_recon_aid crown SCENARIO #8: FAILURE: DLFM_DB is lost; backup of the DLFM_DB database is available, but not all log files are available. DLFS file system is lost; DLFM backup directory is lost. RECOVERY STEPS: NODE OPERATION DLFM: db2 "restore database dlfm_db" DLFM: Restore the DLFM backup directory from your storage manager. DLFM: Restore the DLFS file system from your storage manager. (You need the directory structure.) DB2: db2_recon_aid crown SCENARIO #9: FAILURE: DB2 database is lost; DLFM_DB is lost; backup, and all log files for the DLFM_DB database are available. DLFS file system is lost; DLFM backup directory is lost. RECOVERY STEPS: NODE OPERATION DLFM: db2 "restore database dlfm_db" DLFM: db2 "rollforward database dlfm_db to end of logs" DLFM: Restore the DLFM backup directory from your storage manager. DLFM: Restore the DLFS file system from your storage manager. DB2: db2 "restore database crown" DB2: db2 "rollforward database crown to end of logs" (End of logs is always recommended over point-in-time.) DB2: 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, insert a row with the DATALINK value after the rollforward to a PIT, issue a SET CONSTRAINTS statement to put the table in DRP, and then run the reconcile utility. 12.10 DATALINKS CONFIGURATION FILE CANNOT BE CREATED OR MODIFIED THROUGH THE COMMAND LINE PROCESSOR (CLP) References to the command: db2 add file manager should be replaced by references to a new utility called db2dlcfg. The syntax of this utility is as follows: db2dlcfg -d -a [-s ] [-p ] -h where: database name Represents the name of the database (mandatory). action Represents the action that is to be performed (mandatory): LIST - List all DB2 File Managers defined for this database. ADD - Add a single DB2 File Manager for access by this database. server host name Represents the DLFM File Manager's fully qualified host name. port number Represents the DLFM File Manager's port number. -h Displays help information. When this option is specified, all other options are ignored and only the help is displayed. For example: db2dlcfg -d SAMPLE -a LIST db2dlcfg -d SAMPLE -a ADD -s myhost.mydomain.mycompany.com -p 10001 If moving from a previous version of DB2 File Manager, do the following: 1. Issue db2stop. 2. Remove all copies of the datalink.cfg file (keeping a backup is recommended). 3. Recreate the datalink.cfg file by adding file servers, using the db2dlcfg utility. Add the file servers in exactly the same order. For example: cat datalink.cfg 1 myfs1.mydomain.mycompany.com 10001 2 myfs2.mydomain.mycompany.com 10001 4 myfs2.mydomain.mycompany.com 10001 (Note that the third one is missing.) mv datalink.cfg datalink.old db2dlcfg -d SAMPLE -a ADD -s myfs1.mydomain.mycompany.com -p 10001 db2dlcfg -d SAMPLE -a ADD -s myfs2.mydomain.mycompany.com -p 10001 db2dlcfg -d SAMPLE -a ADD -s nullfs.mydomain.mycompany.com -p 10001 db2dlcfg -d SAMPLE -a ADD -s myfs4.mydomain.mycompany.com -p 10001 (Note that the third one is null.) 13.0 QUICK BEGINNINGS CHANGES DOCUMENTED BEFORE VERSION 5.2 The additional information in this section relates to the following DB2 Version 5.0 Quick Beginnings manuals: o DB2 UDB Personal Edition Quick Beginnings Version 5.0 o DB2 UDB Enterprise Edition for OS/2 Quick Beginnings Version 5.0 o DB2 UDB Enterprise Edition for Windows NT Quick Beginnings Version 5.0 o DB2 UDB Enterprise Edition - Extended for UNIX Version 5.0 13.1 DB2 PRODUCTS Reference is made in the DB2 manuals to the DB2 Application Developer's Kit. This product is now available in two editions, known as the IBM DB2 Personal Developer's Edition Version 5, and the IBM DB2 Universal Developer's Edition Version 5 products. Both editions are available on OS/2, Windows NT, Windows 95, and Windows 98 only. 13.2 PLANNING FOR INSTALLATION The updates in this section relate to software prerequisites and fixes. 13.2.1 OS/2 SOFTWARE REQUIREMENTS In the "Server Product Requirements" section, the following operating system information should be changed for the DB2UDB Enterprise and Workgroup Edition: From: To: -------------------- --------------------------------------------- OS/2 Warp Server SMP OS/2 Warp Server Advanced V4 with SMP Feature A similar change must be made in the "Client Product Requirements" section for the DB2 Client Application Enabler for OS/2, the SDK and Net.Data: From: To: -------------------- --------------------------------------------- OS/2 Warp Server SMP OS/2 Warp Server Advanced V4 with SMP Feature 13.2.1.1 Possible Client-to-Server Connectivity Using IPX/SPX to communicate between Windows 95, Windows 98, or OS/2 clients and servers is not recommended. This connection is unreliable. Working with Novell, we have determined the cause to be at the IPX/SPX protocol stack level. The problem lies with the level of SPX I supported by Windows 95 and Windows 98, and the level of SPX I supported by OS/2. When Windows 95 or Windows 98 negotiates a connection, it specifies an SPX I level connection, but on Windows 95 or Windows 98, this includes SPX II level full-media size support. However, when OS/2 negotiates an SPX I connection, full-media size is not supported. Novell is currently developing full-media size support for OS/2 SPX I connections, for a consistent representation of SPX I connections. This will prevent connection terminations from occurring when an unexpected packet size is sent. If you would like the status of this development, you can call Novell and ask for the status of SPD147611. 13.2.2 NETFINITY SUPPORT PROGRAM MUST BE STOPPED - 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. 13.2.3 COMMUNICATIONS SERVER FOR WINDOWS NT - FIXES REQUIRED When using DB2 Connect Enterprise Edition for Windows NT and IBM Communications Server for Windows NT (CS/NT), in order to enable the canceling of host queries (SQL CANCEL), you will require DB2 UDB Version 5.2 (or DB2 UDB Extended Enterprise Edition for Windows NT), and the APAR fixes JR11529 and JR11170 for CS/NT. 13.2.4 CLIENT APPLICATION ENABLER UPGRADE RECOMMENDED FOR PARTITIONED DATABASE USERS When using a DB2 UDB V5.0 Client Application Enabler, problems can arise when using the Control Center to attach to an Extended Enterprise Edition (EEE) partitioned database instance. The Control Center originally provided with DB2 Connect and UDB Version 5 did not include the administrative functions required to administer partitioned database systems. As a result, it is necessary to upgrade DB2 clients in order to perform UDB EEE administrative functions. Possible indications that this is necessary include the following: o The Control Center may close each time you try to use it to perform these functions. o Backup and restore of a remote database may appear to work from the command line, but the user is unable to rollforward the database. A system error occurs during rollforward, and it becomes necessary to terminate db2 processes at the client workstation and reboot. If you have multiple logical nodes per single physical node, then the backup and restore commands are not supported from DB2 Version 2 clients. In general, if you want to use the Control Center to administer a DB2 UDB EEE server, then you should upgrade the Client Application Enabler. The fix for this problem was first supplied in the first FixPak for DB2 UDB V5.0: AIX U452196 Solaris U452197 HP-UX U452198 OS/2 WR09003 Windows NT WR09004 Windows 3.1 WR09006 These fixes are obtainable from: "http://www.software.ibm.com/data/db2/db2tech". The correct client level can also be obtained from the client pack that is included with DB2 UDB and DB2 Connect Enterprise Edition Version 5.2. DB2 UDB FixPaks are cumulative. They include fixes in FixPaks 1, 2, 3, 4, 5, and 6, as well as in the Client Application Enabler included with DB2 UDB EEE for Windows NT, and for Solaris, Version 5. 13.2.5 CLIENT APPLICATION ENABLER UPGRADE RECOMMENDED FOR WINDOWS ODBC USERS When using ODBC applications from Windows NT and Windows 95 clients, we recommend that you upgrade the DB2 Client Application Enabler to fix pack level WR09014 or later. This fix is obtainable from: "http://www.software.ibm.com/data/db2/db2tech". The correct client level can also be obtained from the client pack that is included with DB2 UDB and DB2 Connect Enterprise Edition Version 5.2. 13.2.6 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 2.1.0.6 or higher. 13.2.7 CS/NT IS REQUIRED FOR TWO-PHASE COMMIT SUPPORT ON WINDOWS NT Two-phase commit using SNA is now supported on Windows NT when using the DB2 Syncpoint Manager (SPM) function together with IBM Communications Server V5.01 for Windows NT (CS/NT). The SNA-based version of the SPM is part of the following products: o DB2 Connect Enterprise Edition for AIX o DB2 Connect Enterprise Edition for OS/2 o DB2 Connect Enterprise Edition for Windows NT o DB2 UDB Enterprise Edition for AIX o DB2 UDB Enterprise Edition for OS/2 o DB2 UDB Enterprise Edition for Windows NT o DB2 UDB Extended Enterprise Edition for AIX o DB2 UDB Extended Enterprise Edition for Windows NT. The SPM permits DB2 UDB client applications to access host data through DB2 Connect EE with SNA two-phase commit data protection provided by the SPM. Similarly, host database applications can access data on DB2 UDB servers. 13.2.7.1 Registering CS/NT as a Windows NT Service After installing IBM Communications Server for Windows NT (CS/NT) you should register it as a Windows NT Service. This will automatically start CS/NT when the machine is booted. To register CS/NT as an Windows NT Service execute the following command. Either: csstart -a To autostart CS/NT with the default configuration file, or: csstart -a c:\ibmcs\private\your.acg Where C:\IBMCS\PRIVATE\YOUR.ACG is the name of the non-default CS/NT configuration file you wish to be used. The next time your machine is rebooted CS/NT will be started automatically with the requested configuration file. 13.2.8 DCE SUPPORT ON WINDOWS NT AND WINDOWS 95 DB2 Universal Database and DB2 Connect Version 5.2 provide support for: o Gradient DCE CDS on Windows NT and Windows 95 o IBM DCE CDS on Windows NT Two new DLLs have been provided: o db2dce.grd for Gradient DCE CDS o dcedce.ibm for IBM DCE CDS By default, "db2dce.grd" has been copied to "db2dce.dll." NOTE: DB2 will only load "db2dce.dll." You must copy "dcedce.ibm" to "db2dce.dll" if you want to use IBM DCE CDS. In addition to the existing support using APPC, IPX/SPX, NETBIOS, and TCP/IP, support for DCE using the Named Pipes (NPIPE) networking protocol is now provided. A control statement similar to the following example may now also be specified for a DCE object having the DB_Communication_Protocol attribute, with value: NPIPE;Computer_name;Instance_name For more information, see Appendix G of the Administration Guide. | 13.2.9 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. 13.2.10 ASSUMPTION OF SYSADM AUTHORITY (UNIX) In the Quick Beginnings manuals for UNIX, when discussing security issues, it is assumed that the user has not configured the SYSADM_GROUP parameter in the database manager configuration file. If the SYSADM_GROUP parameter is not configured, then the SYSADM group defaults to the primary group of the instance owner. The Quick Beginnings manuals always refer to the primary group of the instance owner as having SYSADM authority. However, if you have configured the SYSADM_GROUP parameter, you should take the references in the manuals to refer to the SYSADM group that you have defined in the database manager configuration file. 13.3 OTHER INSTALLATION AND CONFIGURATION TASKS The updates in this section relate to installation and configuration tasks. 13.3.1 CONFIGURING CS/NT FOR SYNCPOINT MANAGER (SPM) IBM Communications Server for Windows NT Version 5.01 is required if using the SPM over SNA. The SPM is also supported over TCP/IP in DB2 Version 5.2. Chapter 22 of "DB2 Connect Enterprise Edition Quick Beginnings" book describes SPM setup. The following additional instructions assume that you have already configured CS/NT for DB2 Connect. Refer to Chapter 14 of the DB2 Connect Enterprise Edition Quick Beginnings book. 1. The LU to be used by the SPM can be defined in CS/NT using CS SNA Node Configuration/Configure Local LU 6.2. 2. The LU must be syncpoint-enabled. This can be accomplished as follows: a. Start the CS/NT Configuration Verification program. b. Locate the required configuration file. c. Edit this file. d. Search for the LU definition to be used by the SPM. You will find a defintion such as the following: LOCAL_LU=(LU_NAME=SPMNAME LU_ALIAS=SPMNAME LU_SESSION_LIMIT=0 NAU_ADDRESS=0 ROUTE_TO_CLIENT=0 SYNCPOINT_SUPPORT=0) e. Change SYNCPOINT_SUPPORT=0 to SYNCPOINT_SUPPORT=1. f. Save the file. 3. Verify the new configuration. 4. Stop DB2. 5. Ensure that the DB2COMM registry value includes "APPC". Refer to Chapters 24 and 39 of DB2 Connect EE "Quick Beginnings". 6. Stop and restart the CS/NT node. To verify that syncpoint support has been enabled for this LU check the "Local LU6.2" information on the CS/NT SNA Node Operations. The LU should have "Yes" listed in the "Syncpoint Support" column. 7. Update the database manager configuration to set DIAGLEVEL to 4: update dbm cfg using diaglevel 4 You need to set this in order to be able to view possible SPM-related diagnostic messages. 8. Start DB2. 9. Examine "db2diag.log". (When the environment variables DB2PATH and DB2INSTANCE are set to their respective defaults, this is found in the directory SQLLIB\DB2). There, if the the SPM has started successfully, you will see a message to that effect. Note also that when the SPM is started for the first time a directory "\sqllib\spmlog" is created. If this directory does not exist, or if the diagnostics indicate that the SPM has failed to start, then you must recheck your DB2 and CS/NT configurations. The Quick Beginnings manual for DB2 Connect Enterprise Edition will be updated to include this additional information. 13.3.1.1 Additional Information when using the SNA SPM 1. It is very important that CS/NT be started before DB2. DB2 will wait for CS/NT to start before completing its own startup procedures. Therefore, if CS/NT is NOT started before DB2, DB2 will appear to hang upon starting. DB2 is actually waiting for CS/NT to start. It is strongly recommended to register CS/NT as a Windows NT Service. See 13.2.7, "CS/NT is Required for Two-phase Commit Support on Windows NT". CS/NT will then be started automatically. 2. While setting up your environment according to Chapter 22 of the "DB2 Connect Enterprise Edition Quick Beginnings", Microsoft Transaction Server is a Transaction Processing (TP) Monitor. Therefore there is no need for a Transaction Manager Database when using Microsoft Transaction Server. The Quick Beginnings manual for DB2 Connect Enterprise Edition will be updated to include this new information. | 13.3.2 PROBLEM AFFECTING NIS USERS ON AIX VERSIONS 4.1.4 AND 4.1.5 | 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.1.4 and 4.1.5, 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: :!:::::: 13.3.3 DB2CPIC.DLL - EXTENDED SNA SECURITY CODES ON WINDOWS NT AND WINDOWS 95 Extended SNA Security Codes were not implemented by some SNA subsystem providers. This may affect customers using one or more of the following: o DB2 Universal Database (UDB) for Windows NT Version 5 (all editions). o DB2 Connect Enterprise Edition for Windows NT Version 5 o DB2 Connect Personal Edition Version 5 (when running on Windows 95 or Windows NT). A new DLL, called db2cpic2.dll has been provided in order to support Extended SNA Security Codes in these environments. The new DLL was originally packaged with FixPak 1 for DB2 Universal Database Version 5. It is identical to the original DLL db2cpic.dll, except that it includes a call to extract secondary information (cmesi()). +---------------------------------------------------------------------------+ | Table 4. Which version of db2cpic.dll to use? | +------------------------+-------------------------+------------------------+ | IF YOU HAVE THIS SNA | | | | SUBSYSTEM | ON WINDOWS NT USE | ON WINDOWS 95 USE | +------------------------+-------------------------+------------------------+ | IBM Communications | db2cpic2.dll (new) | db2cpic.dll (old) | | Server for Windows NT | | | +------------------------+-------------------------+------------------------+ | IBM Personal | db2cpic2.dll | db2cpic.dll | | Communications for | | | | Windows (see note | | | | below) | | | +------------------------+-------------------------+------------------------+ | Microsoft SNA Server | db2cpic.dll | db2cpic.dll | +------------------------+-------------------------+------------------------+ | Wall Data RUMBA (also | db2cpic2.dll | db2cpic2.dll | | Integrated SNA Support | | | | provided with DB2 | | | | Connect Personal | | | | Edition) | | | +------------------------+-------------------------+------------------------+ If DB2 fails to load db2cpic2.dll, you can do the following in the directory where it has been installed: copy db2cpic2.dll db2cpic2.bak copy db2cpic.dll db2cpic2.dll This will make db2cpic2.dll the same as db2cpic.dll. NOTE: If you wish to use Extended Security Support with DB2 UDB Version 5.0 on Windows 95, we recommend using IBM Personal Communications (PComm) for Windows 95 Version 4.2 (not Version 4.1). Copy db2cpic.dll to db2cpic.bak and copy db2cpic2.dll to db2cpic.dll. This will make db2cpic.dll the same as db2cpic2.dll. 13.3.4 DB2NTMEMSIZE PROFILE VARIABLE FOR WINDOWS NT In "Controlling Your DB2 Environment" in the Quick Beginnings manual for DB2 UDB Extended Enterprise Edition for Windows NT, it is incorrectly stated that "DB2NTMEMSIZE is used to override the upper limit of the DB2 shared memory segment". Windows NT requires that all shared memory segments be reserved at DLL initialization time in order to guarantee matching addresses across processes, and a new profile variable DB2NTMEMSIZE has been introduced to permit the user to override the DB2 defaults on Windows NT if necessary. In most situations the default values should suffice. The following is the list of memory segments and their default sizes: Memory Segment Default Size Override Option ---------------------------------------------------------------------------- Database Kernel 16777216 (16MB) DBMS:[number of bytes] MPP FCM Buffers 33554432 (32MB) FCM:[number of bytes] Database Admin GUI 33554432 (32MB) DBAT:[number of bytes] Fenced DARIs 16777216 (16MB) APLD:[number of bytes] More than one segment may be overridden by separating the override options with ':'. For example, to limit DBMS to (approximately) 256K, and FCM to (approximately) 64MB, use: db2set DB2NTMEMSIZE=DBMS:256000:FCM:64000000 13.4 SNAPSHOT MONITORING IN THE CONTROL CENTER On OS/2, when you resize the Performance Graph View of the Snapshot Monitor to a smaller size, the Control Center may shut down without any error message. You cannot perform an action that either alters, changes, or deletes a database object (such as an instance or database) while you are performing snapshot monitoring on either that object, or on any it its child objects. (In addition, if you are monitoring a partitioned database system, you cannot refresh a view of partitioned database objects.) For example, you cannot monitor database A if you want to remove its instance. If, however, you are monitoring the instance only, you can alter database A. To stop all monitoring for an instance (including any of its child objects), select "Stop all monitoring" from the pop-up menu for the instance. You should always stop monitoring from the instance, as this ensures that all locks that are held by the performance monitor are released. 13.5 ACCESSING DB2 DATA FROM THE WEB Information on using Java with DB2 (JDBC for instance) is located on the internet at: http://www.software.ibm.com/data/db2/java/ 13.6 SETTING NETWORK-RELATED PARAMETERS ON THE RS/6000 SP The following information should be added to the tip that is at the end of Step 1, "Prepare for Installation," in Chapter 6, "Installing DB2 Universal Database Extended Enterprise Edition," of the "IBM DB2 Universal Database Extended Enterprise Edition for AIX Quick Beginnings" book. The tip describes how to ensure that network-related parameters are set on all SP nodes. After the note that specifies "You can use the no -a command to view the current setting for network parameters," add the following paragraph: The above values are minimum values for the parameters. If you are already setting any of the network-related parameters to a larger value, do not set the parameter to the lower value listed above. After the sentence that begins "To view the configuration for css0," add the following paragraph: You should only use the following method to set the network-related parameters if you are not using the /tftpboot/tuning/cst file to tune the system. 13.7 SETTING UP THE CCA AND THE CONTROL CENTER IN A PARTITIONED DATABASE ENVIRONMENT (DB2 UNIVERSAL DATABASE EXTENDED ENTERPRISE EDITION FOR AIX) The following information should be added to Step 8, "Setting up the Client Configuration Assistant and the Control Center" in Chapter 6, "Installing DB2 Universal Database Extended Enterprise Edition," of the "IBM DB2 Universal Database Extended Enterprise Edition for AIX Quick Beginnings" manual. 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. There are additional ways to distribute client requests amongst different coordinator nodes. You can use the Client Configuration Assistant IMPORT/EXPORT or manual catalog functions to distribute the workload. Another alternative is to use the LoadLeveler product to distribute client requests transparently. You cannot use the Server Communications Setup function in the Control Center to perform remote setup of nodes (database partition servers) in the partitioned database environment, if there is no administration server running on the node. Discovery is supported using the TCP/IP protocol. IPX/SPX file server addressing is not supported in the partitioned database environment. 13.8 MIGRATING FROM PREVIOUS VERSIONS (UNIX) In the "Verify that Databases Can Be Migrated" section, it is stated that DB2 must be stopped to prepare the instance for migration. This is incorrect. In order to successfully run the "db2ckmig" tool, the database manager must be running. In the same section, change the following: DB2DIR/instance/db2ckmig -e -a 0 -1 INSTHOME/migration.log to: DB2DIR/bin/db2ckmig -e -a 0 -l INSTHOME/migration.log In the "Migrate the DB2 Instance" section, the "db2imigr" command syntax is shown with "-u fencedID" as an optional parameter. The -u option is not required only when the DB2 Client Application Enabler product is installed; however, for all other DB2 products, the -u option is required. 13.9 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: o For OS/2: - DPI 1.1 support provided by TCP/IP Version 2.0 or later, or - DPI 2.0 support provided by Warp Connect installed with OS/2 Warp, or - DPI 2.0 support provided by IBM SystemView Agent. o For Windows NT or AIX: - DPI 2.0 support provided by IBM SystemView Agent. 13.10 CONFIGURING CLIENT TO DB2 SERVER COMMUNICATION USING THE CCA In order to connect properly using the IBM Communications Server SNA API Client for Windows NT or Windows 95, you must start the APING application prior to starting any GUI application that will connect via APPC. Such GUI applications include the Client Configuration Assistant, Control Center, and any ODBC GUI applications. 13.11 ADMINISTERING AND USING OS/2 USER PROFILE MANAGEMENT There is a change to a note in Table 7 within the section "User Profile Management Passwords" that refers to translated values for user IDs and passwords. The note to Table 7, "Default User IDs and Passwords" states the following: If your country does not appear in the list, assume USERID and PASSWORD are used. If this fails, see the README file. This note is no longer valid and should be disregarded. 13.12 ADSM CLIENT SETUP FOR EEE ON SOLARIS - USE DB2PROFILE When setting up ADSM Client for DB2 UDB Extended Enterprise 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 2.1.0.8 or higher. 13.13 CONNECTIONS FROM DB2 FOR OS/390 VERSION 5 TO DB2 UNIVERSAL DATABASE VERSION 5 The following information should be added to the chapter that describes how to use the DB2 workstation as a server for host applications, in the section that lists the PTFs required for DRDA application servers: Please apply the fix for DB2 for OS/390 APAR PQ07537 if you will be connecting from DB2 for OS/390 Version 5 to DB2 Universal Database Version 5. 13.14 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. 13.15 SHARED MEMORY ADDRESSES - WINDOWS NT OR WINDOWS 95 For Windows NT and Windows 95, in the "Managing Instances" section of the Quick Beginnings manual, the following revised information applies. The default database manager shared memory address is x'20000000' for Windows NT, and x'90000000' for Windows 95. If a shared memory address collision occurs, this value can be modified to force the database manager instance to allocate its shared memory at a different address. Use the DB2DBMSADDR registry profile variable to assign an address value in the range of x'20000000' to x'B0000000' on Windows NT, and x'80000000' to x'B00000000' on Windows 95 using increments of x'4000000'. For example, on Windows 95, try the values: x'840000000', x'88000000', x'8C000000', and so on... The value of DB2DBMSADDR is in hexadecimal format without the preceding 0x and may be set using the db2set profile registry command. For example, to set DB2DBMSADDR to x'84000000', use the following command: db2set DB2DBMSADDR=84000000 13.16 RUNNING THE DB2 JDBC APPLET SERVER AS A WINDOWS NT SERVICE Under the heading "Running Java Applications" - "Java Applets", the Quick Beginnings and Administration Getting Started manuals say: To run your applets, do the following: 1. Start the DB2 JDBC applet server on your Web server by entering: db2jstrt portno where portno is the number of the unused TCP/IP port that you specified in the DB2Applt.java file. This information is out of date, as follows: The DB2 JDBC Applet Server can now be started as a Windows NT service, and it must be registered as a service first, before it is be available in the services section of the control panel. In order to register the service, run the following command while logged on as an administrator: db2regjdbc i To register the service to run with a specific userid, run: db2regjdbc i [userid] [password] To remove the service, run: db2regjdbc u Once the service is registered, it can be started and stopped from the services section of the control panel: 1. Open Control Panel 2. Click on Services 3. Highlight "DB2 JDBC Applet Server". 4. Enter the port number in the command field (the number of the unused TCP/IP port that you specified in the DB2Applt.java file). Click on start. The service can also be set to start and stop automatically. 13.17 OBTAINING INFORMATION 13.17.1 ONLINE HELP The topic "SQL Help", which explains the syntax of SQL statements, is not available on UNIX based operating systems. 13.17.2 DB2 BOOKS The list of language identifiers in the first note at the bottom of the table containing information about the DB2 Books should include the following entries: Language Identifier Greek A Japanese J Korean K Simplified Chinese C Traditional Chinese T 13.17.3 PRINTING THE POSTSCRIPT BOOKS If you prefer to have printed copies of the manuals, you can decompress and print PostScript versions. NOTE: Specify the full path name for the file you intend to print. On OS/2 and Windows platforms: 1. Copy the compressed PostScript files to a hard drive on your system. The files have a file extension of .exe and are located in the x:\doc\language\books\ps directory, where x: is the letter representing the CD-ROM drive and language is the two-character country code that represents your language (for example, EN for English). 2. Decompress the file that corresponds to the book that you want. The result from this step is a printable PostScript file with a file extension of .psz. 3. Ensure that your default printer is a PostScript printer capable of printing Level 1 (or equivalent) files. 4. Enter the following command from a command line: print filename.psz On UNIX-based platforms: 1. Mount the CD-ROM. Refer to your Quick Beginnings manual for the procedures to mount the CD-ROM. 2. Change to /cdrom/doc/%L/ps directory on the CD-ROM, where /cdrom is the mount point of the CD-ROM and %L is the name of the desired locale. The __ manuals will be installed in the previously-mentioned directory with file names ending with .ps.Z. 3. Decompress and print the manual you require using the following command: o For AIX: zcat filename | qprt -P PSPrinter_queue o For HP-UX, Solaris, or SCO UnixWare 7: zcat filename | lp -d PSPrinter_queue o For Silicon Graphics IRIX and SINIX: zcat < filename | lp -d PSPrinter_queue where filename is the name of the full path name and extension of the compressed PostScript file and PSprinter_queue is the name of the PostScript printer queue. For example, to print the English version of Quick Beginnings for UNIX on AIX, you can use the following command: zcat /cdrom/doc/en/ps/db2ixe50.ps.Z | qprt -P ps1 13.17.4 VIEWING ONLINE BOOKS The manuals included with this product are in Hypertext Markup Language (HTML) softcopy format. Softcopy format enables you to search or browse the information, and provides hypertext links to related information. It also makes it easier to share the library across your site. You can use any HTML Version 3.2-compliant browser to view the online books. To view online books: o If you are running DB2 administration tools, use the Information Center. o Use the open file function of your Web browser. The page you open contains descriptions of and links to DB2 books: - On UNIX-based platforms, open the following page: file:/INSTHOME/sqllib/doc/%L/html/index.htm where %L is the locale name. __ - On other platforms, open the following page: sqllib\doc\html\index.htm The path is located on the drive where DB2 is installed. You can also open the page by double-clicking on the DB2 ONLINE BOOKS icon. Depending on the system you are using, the icon is in the main product folder or the Windows Start menu. NOTE: The DB2 ONLINE BOOKS icon is only available if you do not install the Information Center. 13.17.5 SETTING UP A DOCUMENT SERVER By default the DB2 information is installed on your local system. This means that each person who needs access to the DB2 information must install the same files. To have the DB2 information stored in a single location, use the following instructions: 1. Copy all files and sub-directories from \sqllib\doc\html on your local system to a web server. Each book has its own sub-directory containing all the necessary HTML and GIF files that make up the book. Ensure that the directory structure remains the same. 2. Configure the web server to look for the files in the new location. For information, see Setting up DB2 Online Documentation on a Web Server at: http://www.software.ibm.com/data/pubs/papers/db2html.html 3. If you are using the Java version of the Information Center, you can specify a base URL for all HTML files. You should use the URL for the list of books. 4. Once you are able to view the book files, you should bookmark commonly viewed topics such as: o List of books o Tables of contents of frequently used books o Frequently referenced articles like the ALTER TABLE topic o Search form. For information about setting up a search, see the What's New book. 13.17.6 SEARCHING ONLINE BOOKS To search for information in the HTML books, you can do the following: o Click on SEARCH THE DB2 BOOKS at the bottom of any page in the HTML books. Use the search form to find a specific topic. o Click on INDEX at the bottom of any page in an HTML book. Use the Index to find a specific topic in the book. o Display the Table of Contents or Index of the HTML book, and then use the find function of the Web browser to find a specific topic in the book. o Use the bookmark function of the Web browser to quickly return to a specific topic. o Use the search function of the Information Center to find specific topics. 13.18 MICROSOFT INTERNET EXPLORER 4.0 HTTP 1.1 SETTINGS Currently, the default settings on Internet Explorer cause a conflict with the DB2 search server. As a workaround, disable the HTTP 1.1 settings check box from Internet Explorer's advanced options (on each client that will access the documentation). Note that due to a limitation in Internet Explorer this requires a reboot of your system to take effect. 1. From the View menu, select Internet Options. 2. Select the Advanced tab. You'll see a list of sections containing check boxes. 3. Under the section "HTTP 1.1 settings" clear both the "Use HTTP 1.1 through proxy connections" and "Use HTTP 1.1" check boxes. 4. Select OK. 5. Shut down Windows and reboot your computer for the new settings to take effect. 13.19 CONSIDERATIONS WHEN REMOTELY ADMINISTERING CLUSTERS USING THE CONTROL CENTER After you install DB2 UDB EEE for Windows NT on multiple nodes, the install process will only configure the "instance owning node" to be the coordinator node for communications from client machines. In order to remotely administer the cluster using the Control Center you will need to enable communications on each of the nodes. One way to do this is as follows: 1. Reserve a TCP/IP port on each of the machines. For example, let us assume that port 4000 is available on each of the machines. 2. Update the instance configuration file to use that port number for all nodes. At the instance owning node type: DB2 update dbm cfg using svcename 4000 3. Configure each node to accept TCP/IP. At the instance owning node type: rah "db2set db2comm=TCPIP" Each node will accept inbound TCP/IP connections when it is restarted. 14.0 REPLICATION GUIDE AND REFERENCE 14.1 NEW PLATFORMS SUPPORTED IBM Replication is a set of easy-to-use, automated copy tools that replicate data from sources to targets. Data can be copied: o Among DB2 for MVS, DB2 for VSE & VM, DB2 Universal Database, and DB2 for OS/400 database servers o Across platforms for MVS, VM, VSE, OS/400, AIX, HP-UX, SCO UnixWare 7, OS/2, Windows NT, Windows 95, Windows 98, and the Solaris operating environment o On non-DB2 sources and targets such as Oracle, Sybase, Informix, and Microsoft SQLServer (through DB2 DataJoiner). 14.2 NEW MESSAGES Two messages for the Apply program are missing from Chapter 21, "IBM Replication Messages": ASN1054S The Apply program could not find the registration information for source owner "", source table "", and source view qualifier "". Explanation The source table registration is incorrect or incomplete. User Response Drop the registration and redo it. Also make sure that the registration information is in both the register table and the pruning control table. ASN1055S The Apply program could not find the prune control information for source owner "", source table "", source view qualifier "", target owner "", and target table "". Explanation The source table registration is incorrect. User Response Drop the subscription and redo it. 14.3 LONG VARCHAR SUPPORT BY THE CAPTURE PROGRAM FOR DB2 UDB VERSION 5.2 The capture program for DB2 UDB Version 5.2 now has the capability to support LONG VARCHAR data types. New Long Field Manager long records, which contain the information needed to capture changes made to the LONG VARCHAR columns, are propagated. For DB2 UDB Version 5 users who need the new capture capability, there is the option to add an additional clause to the ALTER TABLE statement. There are no changes to the CREATE TABLE syntax. To take advantage of the new LONG VARCHAR support, you must specify the DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS clause on the ALTER TABLE statement. When this DDL statement is executed, the DATA CAPTURE CHANGES column in the SYSCAT.TABLES is set to "L". Whenever base tables with the INCLUDE LONGVAR option row are updated, Long Field Manager long records are written and read by the capture program. These records include both before and after images of columns. If you want to disable the new LONG VARCHAR support, you must specify the appropriate DATA CAPTURE CHANGES value in an ALTER TABLE statement. For example, you could specify "YES" or "NONE" without the additional clause. To register LONG VARCHAR columns, do the following from the Control Center: 1. Find the "Available Columns box" in the "Define as Replication Source" window. 2. Select the LONG VARCHAR columns. The following restriction applies: For a DELETE operation (IBMSNAP_OPERATION="D"), the LONG VARCHAR column values will be null. | 14.4 DATAJOINER REPLICATION ADMINISTRATION TOOL README | Welcome to the DB2 DataJoiner Replication Administration Tool, Version 2.1.1 | (DJRA). | This section contains information you need for installing DJRA: | o About DJRA | o Installing DJRA | ABOUT DJRA | DJRA is available for the first time as a sample application in DB2 UDB | FixPak 7 for Windows 95, Windows 98 and Windows NT. It is only available for | these operating systems. DJRA provides administration functions for | replicating data. DJRA is recommended for customers requiring administrative | functions for DataPropagator Relational for MVS Version 5 and DataPropagator | Relational Version 5 for AS/400. Several of the administration functions | needed for these two platforms are not currently available in the UDB Control | Center. | INSTALLING DJRA | DJRA is located in the directory "\BONUS\REPLICATION". Conceptual, | installation and configuration information is available in the "DataJoiner | Planning, installation and Configuration Guide" at: | http://www.software.ibm.com/data/datajoiner/booksv2/djxn2m79 | The publication can also be ordered separately (SC26-915-01). To print a | postscript copy, download the file "v2ntpic.pdf" from the DataJoiner ftp | site: | ftp://ftp.software.ibm.com/ps/products/datajoiner/info/ | Replication Administration must be installed on a Windows 95, Windows 98, or | Windows NT operating system. To install Replication Administration Version | 2.1.1: | 1. Open 'My Computer' or 'Explorer' | 2. Click on the UDB DB2 CD-ROM | 3. Select BONUS | 4. Select REPLICATION | 5. Run the DJRA program. This uncompresses the file and begins the | installation process. | During installation setup, you can indicate the directory to install DJRA. | You can also browse through the README.TXT file to see the latest information | about the application. 15.0 ROAD MAP TO DB2 PROGRAMMING 15.1 ABOUT THE APPLICATION DEVELOPER'S KIT VisualAge for Basic and Lotus Approach are no longer bundled in the product boxes. 16.0 SQL REFERENCE | 16.1 NAMING CONVENTIONS (CURSOR-NAME) | CURSOR-NAME A long identifier that designates an SQL cursor. For host | compatibility, it supports hyphens as well. | 16.2 ALTER TABLE The ACTIVATE NOT LOGGED INITIALLY clause should not be used when DATALINK columns with the FILE LINK CONTROL attribute are being added to the table. | Replace the description of "table-name" with the following: | "table-name" | Identifies the table to be changed. It must be a table described in the | catalog and must not be a view or catalog table. If it is a summary table, | alterations are limited to activating not logged initially, changing pctfree, | locksize or append. | 16.3 COMPOUND SQL | The following should be added to the "Rules" section: | DB2-Connect does not support SELECT statements selecting LOB columns in a | compound SQL block. 16.4 CREATE FUNCTION 16.4.1 DBINFO Under both CREATE FUNCTION (External Scalar) and CREATE FUNCTION (External Table), there is a list of the information contained in the DBINFO structure which is passed to the UDF if the definer specifies DBINFO. The following bullet has been added to each of these lists: Application ID - unique application ID which is established for each connection to the database. | 16.4.2 ERROR PROCESSING FOR UDF CALL TYPES | In the "CREATE FUNCTION (External Scalar)" section, where the "NO FINAL CALL | or FINAL CALL" specification is discussed, this sentence should be added to | the bottom: | A description of the scalar UDF processing of these calls when errors occur | is included in the Embedded SQL Programming Guide, Chapter 7, "Other Coding | Considerations" section, under "Hints and Tips". | This updated description is in the section 8.11.2, "External UDF Error | Processing Model for Scalar Functions". | In the "CREATE FUNCTION (External Table)" section, where the "NO FINAL CALL | or FINAL CALL" specification is discussed, this sentence should be added to | the bottom: | A description of the table UDF processing of these calls when errors occur is | included in the Embedded SQL Programming Guide, Chapter 7, "Table Function | Considerations" section. | This updated description is in the section 8.11.1, "Scratchpad and Final Call | Type Arguments for EXTERNAL TABLE Functions". | 16.5 CREATE TABLE | 16.5.1 CREATE TABLE DESCRIPTION | Add the following to the description of NOT LOGGED INITIALLY: The NOT LOGGED INITIALLY clause should not be used when DATALINK columns with the FILE LINK CONTROL attribute are present in the table. | Replace the description of "FULLSELECT" under "summary-table-definition" with | the following: | FULLSELECT | Defines the query in which the table is based. The summary-table-options | specified define attributes of the summary table. The option chosen also | defines the contents of the fullselect as follows: | When DEFINITION ONLY is specified, any valid fullselect can be specified. | The data type and nullability attribute are determined from the result | columns of the select list. Every select list element must have a name. | When REFRESH DEFERRED or REFRESH IMMEDIATE is specified, the fullselect | cannot include: | o references to a view, summary table, or typed table in any FROM clause | o expressions that are a reference type or DATALINK type (or distinct type | based on these types) | o functions that have external action | o functions that depend on physical characteristics (for example | NODENUMBER, PARTITION) | o table or view references to system objects (explain tables also should | not be specified). | Furthermore, when REFRESH IMMEDIATE is specified: | o the fullselect must be a subselect | o the subselect cannot include: | - functions that are not deterministic | - scalar fullselects | - predicates with fullselects | - special registers | o a GROUP BY clause must be included in the subselect | o the select list must have a COUNT(*) function (or COUNT_BIG(*)) and no | DISTINCT | o only SUM (of not nullable columns), COUNT, or COUNT_BIG column functions | are allowed in the select list (without DISTINCT) and other select list | items must be included in the GROUP BY clause | o the FROM clause must reference a single base table (tables cannot be | summary tables, typed tables, or views) | o all GROUP BY items must be included in the select list | o no grouping sets are allowed (including CUBE and ROLLUP) or grouping on | constants | o a HAVING clause is not allowed | o if in a multiple partition nodegroup, then a partitioning key must be a | subset of the group by items. | Replace the description of "REPLICATED" with the following: | REPLICATED | Specifies that the data stored in the table is physically replicated on each | database partition of the nodegroup of the table space in which the table is | defined. This means that a copy of all the data in the table exists on each | of these database partitions. This option can only be specified for a summary | table defined with REFRESH DEFERRED (SQLSTATE 42997). | 16.5.2 CREATE TABLE NOTES | Replace the seventh major bullet under "Notes" with the following: | o A REFRESH DEFERRED summary table may be used to optimize the processing | of queries. In order for this optimization to be able to use a summary | table, the fullselect must conform to certain rules in addition to those | already described. The fullselect must: | - be a subselect with a GROUP BY | - not include DISTINCT anywhere in the select list | - not include any grouping sets (including CUBE and ROLLUP) | - not allow nulls in any grouping columns in the group by clause | - not reference any column more than once | - not contain more than 32 columns in the group by clause | - not include any special registers | - not include functions that are not deterministic. | If the query specified when creating a REFRESH DEFERRED summary table | does not conform to these rules, a warning is returned (SQLSTATE 01633). | Add the following new bullet under "Notes": | o If a summary table is defined with REFRESH IMMEDIATE, it is possible for | an error to occur when attempting to apply the change resulting from an | insert or update of the underlying table. The error will cause the | failure of the insert or update of the underlying table. | 16.6 CREATE TABLESPACE | The functionality described in the sub-section "DROPPED TABLE RECOVERY" has | not been implemented. This sub-section should be deleted from Version 5.2 of | the SQL Reference. 16.7 APPENDIX A. SQL LIMITS/DATABASE MANAGER LIMITS The limit for the number of tables in a DMS table space has been increased from 6648 to 51971. This new limit will automatically apply to any table spaces with a page size other than 4KB, and to all newly created 4KB DMS table spaces. For existing 4KB DMS table spaces, the new /ets option of the db2dart utility is used to apply the new limit: 1. Deactivate the database (that is, ensure that no connections to it exist). 2. Invoke: db2dart /ets /tsi . This will check the specified table space for existing table, index, or LONG objects which have identifiers that are incompatible with the application of the new limit. If none are found, the new limit will be applied to that table space. Otherwise, a list of incompatible tables will be provided in the dart report. In this case, those tables must be dropped, and the command run successfully, for the new limit to become effective. The tables can be recreated and reloaded after the utility has been run successfully on all logical nodes. On EEE installations, run this command first on one logical node that is a member of the nodegroup in which the table space resides. If incompatible tables are identified, drop them, and reissue the command. Once you have run the command successfully on one logical node, you must run it again on every logical node in the nodegroup in which the table space resides (either the db2_all or the db2dart_all utility can be used). For information about db2dart options, invoke db2dart without any options (the utility can be found in sqllib\misc). NOTES: 1. If db2dart /ets is successfully run on a table space, restoring a table space backup image taken prior to running dbdart /ets will no longer be supported. 2. If db2dart /ets is successfully run on any table space in a database, restoring a database backup image taken prior to running db2dart /ets, and rolling forward through the point-in-time when db2dart /ets was run, will not be supported. 3. A database backup must be taken immediately after successfully running db2dart /ets (all table spaces on which the tool is successfully run will be placed in backup pending state). 17.0 SYSTEM MONITOR GUIDE AND REFERENCE 17.1 EVENT MONITORS ARE CREATED WITH LOCAL SCOPE The syntax diagram for the CREATE EVENT MONITOR statement shows that by default, event monitors are created with global scope. This is incorrect. Event monitors are created with local scope by default. 18.0 WHAT'S NEW | 18.1 PERFORMANCE (QUERY/SUMMARY TABLES) | The note at the end of this section can be ignored. The "REFRESH IMMEDIATE" | option is now available. | 18.2 BUILDING JAVA APPLICATIONS AND APPLETS UPDATES To build and run embedded SQL for Java (SQLJ) programs now requires the Java Development Kit (JDK) Version 1.1.4 (or higher). There have been several changes to the Java sample programs documented in this appendix. The stored procedure and UDF programs have been split into separate client and server programs. This allows users on client machines to remotely access stored procedures and UDFs on server machines. The programs affected are now as follows: DB2STP is a server program. The client program that calls it is DB2SPCLI. DB2UDF is a server program. The client program that calls it is DB2UDCLI. STP is a server program. The client program that calls it is STPCLI. UDF is a client program. The server program that it calls is UDFSRV. The script file "embprep" on UNIX platforms, "embprep.bat" on Windows platforms, and "embprep.cmd" on OS/2, has been created to precompile and bind the SQLJ programs. It is called by the "makefile" and executes the "db2profc" command on the Java embedded SQL sample programs. It allows optional arguments for database, user ID and password, thereby facilitating remote binding to databases on DB2 servers from client machines. Following are additional limitations on SQLJ support for DB2 UDB Version 5.2: o The precompile option "DATETIME" is not supported. Only the date and time formats of the International Standards Organization are supported. o The precompile option "PACKAGE USING package-name" specifies the name of the package that is to be generated by the precompiler. If a name is not entered, the name of the profile (minus extension and folded to uppercase) is used. Maximum length is 8 characters. Since the SQLJ profile name has the suffix _SJProfileN, where N is the profile key number, the profile name will always be longer than 8 characters. The default package name will be constructed by concatenating the first (8 - pfKeyNumLen) characters of the profile number and the profile key number, where pfKeyNumLen is the length of the profile key number in the profile name. If the length of the profile key number is longer than 7, the last 7 digits will be used without any warnings. For example: profile name default package name --------------------- -------------------- App_SJProfile1 App_SJP1 App_SJProfile123 App_S123 App_SJProfile1234567 A1234567 App_SJProfile12345678 A2345678 o When a java.math.Bigdecimal host variable is used, the precision and scale of the host variable is not available during application precompilation. If the precision and scale of the decimal host variable is not obvious from the context of the statement in which it is used, the precision and scale can be specified using a CAST. o A Java variable with type java.math.BigInteger cannot be used as a host variable in an SQL statement. o All SQLJ stored procedures must be run in FENCED mode. | o The sqlj option "profile" is not supported. To customize the profile | generated by sqlj, use "db2profc". | o The "SET TRANSACTION" clause is not supported. | o The "WITH" clause is not supported. | o Interoperability between SQLJ iterators and JDBC result sets is not | supported. | o Java host expressions cannot be used in SQL statements. Some browsers do not yet have support for loading a serialized object from a resource file associated with the applet. You will get the following error message when trying to load the applet Applt in those browsers: java.lang.ClassNotFoundException: Applt_SJProfile0 As a work-around, there is a utility which converts a serialized profile into a profile stored in Java class format. The utility is a Java class called sqlj.runtime.profile.util.SerProfileToClass. It takes a serialized profile resource file as input and produces a Java class containing the profile as output. Your profile can be converted using the following command: profconv Applt_SJProfile0.ser or java sqlj.runtime.profile.util.SerProfileToClass Applt_SJProfile0.ser The class Applt_SJProfile0.class is created as a result. Replace all profiles in .ser format used by the applet with profiles in .class format. For an SQLJ applet, you need both db2java.zip and runtime.zip files. If you choose not to package all your applet classes, classes in db2java.zip and runtime.zip into a single Jar file, put both db2java.zip and runtime.zip (separated by a comma) into the archive parameter in the "applet" tag. For those browsers that do not support multiple zip files in the archive tag, specify db2java.zip in the archive tag, and unzip runtime.zip with your applet classes in a working directory that is accessible to your web browser. 18.3 WEB CONTROL CENTER AND NETQUESTION (ENABLING THE WEB CONTROL CENTER REMOTE DOCUMENTATION SEARCHES) The instructions for enabling remote documentation searches on UNIX operating systems are incorrect. Correct instructions can be found on the web at: http://www.software.ibm.com/data/pubs/papers/db2html.html The published example of the "nqmap" command for OS/2 and the Windows operating system should be changed to: nqmap -u DB2ADMEN "http://yourserver/doc/html/" DB2ADMEN The instructions for editing your Start HTML Search Server icon's properties on the OS/2 operating system should read: 1. Right-click on the Start HTML Search Server icon in your DB2 for OS/2 folder. Select Settings and click on the Program tab. In the Optional Parameters field, add CONFIG DB2WEBCC.CNF to the end of the string of parameters. Click OK to close. 2. Edit your db2init.cmd file. This file is located in x:\sqllib\bin\, where x: is the drive on which you installed DB2. Find the line in this file containing both "DB2SS.EXE" and "DB2NETQD.EXE". At the end of this line add CONFIG "DB2WEBCC.CNF". (Note that you need double quotation marks around the file name). Save and exit. Continue with the remainder of the instructions for enabling remote documentation searches. If you want to search all the DB2 online information, you must install both the DB2 documentation (referred to as the "Product Library" on UNIX systems) and the Web Control Center with its online help. If you install the Web Control Center and its help, but do not install the rest of the DB2 online documentation, then attempting to select the search categories for administration, programming, and DB2 Connect may produce an error code of 24, indicating that those search indexes are not installed. In the same way, if you install the DB2 online documentation but not the Web Control Center, attempting to search the Web Control Center online help may produce this same error code. 18.4 CONTROL CENTER RESTRICTIONS The following are restrictions of the Control Center: o The audit_buf_sz configuration parameter is not supported in the Control Center configuration notebook. o Sorting of objects below the databases folder (excluding users, groups and table spaces). These are now sorted in alphabetical order. o A partitioned database must contain more than one node to be monitored (DB2 UDB EEE only). | 18.5 JAVA DEVELOPMENT KIT LEVEL FOR WEB CONTROL CENTER | On page 45 of the What's New book, there is an incorrect statement in the Web | Control section. Please disregard the Java Development Kit level mentioned in | the following sentence: | "It runs from any Java-enabled Web browser that supports the Java Development | Kit 1.1.5." | For the most recent list of the web browsers supported and the Java | Development Kit level required, please see the following web page: | http://www.software.ibm.com/data/db2/udb/webcc/ 19.0 HTML SEARCH SERVER FOR OS/2: SEARCHING HTML DOCUMENTATION IBM DB2 Universal Database (DB2 UDB) online documentation comes with an HTML search server to help you find information. These release notes describe the setup and use of the search system on OS/2. 19.1 INSTALLING THE HTML SEARCH SYSTEM ON OS/2 19.1.1 PREREQUISITES FOR THE SEARCH SYSTEM ON OS/2 You must have TCP/IP Version 3 or higher installed on your machine. OS/2 Warp V4 is recommended because it has the appropriate level of TCP/IP. OS/2 Warp V3 Internet Access Kit (IAK) will also work if you set it up for local loopback and if you install the latest FixPak. You need a browser such as Netscape 2.02 for OS/2. If a Netscape browser is not available for your language, use Web Explorer 1.1 or higher. Ensure you turn off proxy handling for localhost in the browser you use. If you are installing DB2 UDB on a system that has VisualAge for C++ for OS/2, you need to have CSD6 or above installed for the VisualAge for C++ product. 19.1.2 CONFIGURING TCP/IP ON OS/2 The search server will function with or without a network adapter installed as long as TCP/IP local loopback and localhost are enabled on your system. To enable local loopback: 1. Open OS/2 TCP/IP folder. 2. Open the TCP/IP Configuration notebook. 3. View the Network page. 4. In the "Interface to Configure" list box, highlight "loopback interface". 5. If the "Enable interface" check box is not selected, select it now. 6. Verify that the "IP address" is 127.0.0.1 and "Subnet Mask" is empty. To enable localhost on your system: 1. To check whether or not localhost is enabled, enter "ping localhost" on an OS/2 command line. o If data is returned, localhost is enabled, and you can skip steps 2 and 3 below and go directly to step 4. o If "localhost unknown" is returned, or if the command hangs, localhost is not enabled. Go to step 2. 2. If you are on a network, make sure that loopback is enabled (see "Enable local loopback", above). 3. If you are not on a network, enable localhost by performing these steps: a. Add the following line after other ifconfig lines in the MPTN\BIN\SETUP.CMD command file: ifconfig lo 127.0.0.1 NOTE: If you have OS/2 Warp with Internet Access Kit, add the line to the \STARTUP.CMD instead of the MPTN\BIN\SETUP.CMD file. If the file doesn't exist, you will have to create it. b. In the TCP/IP configuration folder, perform the following steps: 1) Go to the "Configure Name Resolution Services page". 2) In the "Hostname configuration without a Nameserver" table, add an entry with "IP Address" set to 127.0.0.1 and "Hostname" set to localhost. NOTE: If you have a hostname for your machine on the "Configure LAN Name Resolution Services" page, you must add this name as an alias when you set the IP Address 127.0.0.1 to localhost. c. Select the "Look through HOSTS list before going to the nameserver" list box. NOTE: This step tells OS/2 that when it is looking for a host, such as localhost, it should use the host address found on your machine rather than checking the nameserver. If the host is not defined on your machine, OS/2 continues looking for the host by using the nameserver you configured. d. Close TCP/IP Configuration and reboot the system. e. You should be able to ping localhost without being connected to any network. 4. Verify that your hostname is correct. On an OS/2 command line, enter "hostname". The hostname returned should match the one listed in the TCPIP Configuration notebook on the Hostnames page and it must be less than 32 characters. If the hostname deviates from these conditions, correct it on the Hostnames page. Then correct it in CONFIG.SYS (SET HOSTNAME=), if necessary. Reboot after you make the necessary changes. 19.1.3 VERIFYING TCP/IP CONFIGURATION Click the "Start HTML Search Server" icon, which is located in the DB2 for OS/2 folder. If an error message appears, TCP/IP is not configured properly. Follow the instructions in 19.1.2, "Configuring TCP/IP on OS/2". to make sure that TCP/IP is correctly configured. Reboot OS/2 if you change any settings. 19.1.4 LOCATING THE SEARCH SYSTEM DIRECTORY The search system is stored in its own directory because it may be used by other products. For example, if you installed DB2 UDB along with the search system on drive G, and later you installed VisualAge for Java on drive H, only one search system is installed -- the one that came first. Some instructions in this document require you to specify the location of this directory. To locate this directory enter this command on an OS/2 command line: echo %IMNNLPSSRV% 19.1.5 CHANGING THE PORT NUMBER FOR THE SEARCH SERVER The search server is assigned to port 49213, a number beyond the public ports assigned for TCP/IP. If you have another product that uses this port, you can change the search server port with the following steps: 1. Edit the HTTPD.CNF file in the search system directory and change the port number to one you know is available, preferably above 49000. 2. Go to the DB2 UDB directory and in the %DB2PATH%/doc/html directory, use a text editor to edit the DB2SRCH.HTM file. Change the following line to the port you selected in the previous step:
where: * is D for DBCS and S for SBCS xx is a two-character identifier for the language in which the documentation is written 49213 is the new port for localhost 3. Stop and start the search server by double-clicking on the icons in the DB2 for OS/2 folder. 19.1.6 DIAGNOSING SEARCH SERVER INSTALL ERRORS If you received error messages while installing the search server, enter the following command from an OS/2 command line and follow the instructions that it returns: SNIFFLE /P Make sure you reboot your system after you're done. 19.2 USING THE HTML SEARCH SYSTEM 19.2.1 STARTING AND STOPPING THE SEARCH SERVER After you install the search system, it will start automatically after you reboot. If the search server starts without any errors but you encounter the following error message in your browser, click the "Stop HTML Search Server" icon, then click the "Start HTML Search Server" icon. A network error occurred: unable to connect to server. The server may be down or unreachable. Try connecting again later. After you have finished searching, stop the search server to reclaim the memory it used. To stop it, double-click the "Stop HTML Search Server" icon in the DB2 for OS/2 folder. 19.2.2 SEARCHING WHILE DISCONNECTED FROM THE NETWORK If you are not on a network (for example, if you are using a laptop computer and are temporarily away from a LAN connection), you have to enable localhost to search the documentation. The instructions for doing this task are described in step 3 of 19.1.2, "Configuring TCP/IP on OS/2". 19.2.3 FILE NOT FOUND ERRORS The search system searches predefined indexes. All of these indexes are installed with the search system, regardless of the DB2 UDB products you install. Therefore, when you conduct a search, some of the results might return a "File not found" error because they pertain to a product that is not installed on your system. 19.2.4 IF SEARCH DOES NOT WORK (ERROR 500 WHEN TRYING TO SEARCH) If you installed the search server but you get an error when you try to search the documentation, check the following items: 1. Check that the search system was properly installed. The environment variable IMNNLPSSRV should point to the search system directory (see 19.1.4, "Locating the Search System Directory"). 2. The search system directory should contain these files: o A DB2SRSxx.EXE or DB2SRDxx.EXE executable file (the former is for SBCS documents, the latter is for DBCS documents). The xx is a two-character identifier of the language in which the documents are written. For example, DB2SRSEN.EXE is the executable for English language SBCS documents; DB2SRDCN is for S. Chinese DBCS documents. o DB2HEAD.HTM and DB2FOOT.HTM. If these files are not present, run DB2NETQ.CMD to set up index registration and copy the files to the search system directory. 3. The search system should be registered with product documentation. Invoke this command to list all of the documentation that is registered with the search system: NQMAP -A The documentation for DB2 UDB is called DB2ADMxx, DB2APDxx, or DB2CONxx, where xx is a two-character identifier of the language in which the documents are written. One or more of these names should appear in the list of names that NQMAP returns. If the files are not there, either TCP/IP is incorrectly installed or the indexes are corrupt. Use SNIFFLE /P to fix the former, and reinstall the documentation to fix the latter. 19.2.5 ERROR DETECTED WHEN STARTING THE SEARCH SERVICE If you encounter this error: HS0410: An error was detected when starting the search service. Stop the service and start it again. Press Enter to continue... (Sometimes the error is EHS0411 but the text is the same. This message comes up on a full black screen.) your hostname might have changed since doing the install and you have to record it again. Go into the NetQuestion directory and enter: type netq.cfg to see what hostname NetQuestion is using. If you have changed the hostname, for example while configuring DHCP and DDNS, enter: netqinit Example: netqinit d:\netqos2\data 19.3 UNINSTALLING THE HTML SEARCH SYSTEM After you uninstall DB2 for OS/2, the search system is left on your hard drive. Run UNINSTNQ.CMD to remove it. 19.3.1 PROBLEMS UNINSTALLING THE SEARCH SYSTEM ON OS/2 If you ran UNINSTNQ.CMD and the search system does not uninstall, the cause may be: o TCP/IP was not configured properly. o DB2 UDB or another product is using the search system so it cannot be removed. 1. Follow the instructions in 19.1.2, "Configuring TCP/IP on OS/2" on page 147 to ensure that TCP/IP is configured properly and search server is initialized with a properly configured TCP/IP setup. Make sure you reboot the system if you make any changes to the configuration. 2. To determine which products are still registered with the search system, issue the following command: NQMAP -A If this command returns no indexes, skip to step 6. If the list contains indexes that do not belong to DB2 UDB (that is, their names begin with something other than "DB2"), you cannot remove the search server. If it contains any of the DB2 index file names (DB2ADMxx, DB2APDxx, DB2CONxx), DB2 UDB could not unregister the indexes therefore causing the search system uninstall to fail. This occurs if DB2 UDB was incorrectly uninstalled (for example, the SQLLIB folder was deleted). In this case, you have to manually unregister the indexes and remove the search system directory using the following steps: a. Issue this command to ensure that the search server is running: NETQ START SERVER //for SBCS IMQSS.EXE -START DBCSHELP //for DBCS b. Issue this command for each of the index files: NQMAP -D c. Issue this command for each of the index files: NQDELET //for SBCS TMDELET //for DBCS d. Stop the search server: NETQ STOP SERVER //for SBCS IMQSS.EXE -STOP DBCSHELP //for DBCS e. Issue "NQMAP -A" and verify that there are no DB2 indexes (DB2xxxxx) remaining. If there are, please contact IBM service. f. Issue the following command to verify that no other indexes are active. NQCOUNTI //for SBCS TMCOUNTI //for DBCS NOTE: See 19.1.4, "Locating the Search System Directory". if you do not know where it is installed. o If this command returns data that indicates one or more indexes are still active, the search system cannot be removed because other products are still registered. Do not perform the remaining steps. o If the search system returns "0 indices active", run UNINSTNQ.CMD. (Ignore this message: "SYS0016: The directory cannot be removed".) Verify that the search system directory has been removed. If it is still there, call IBM service. g. After the UNINSTNQ.CMD completes, perform the following steps: 1) Restore CONFIG.SYS from a backup that was created during install. NOTE: Install creates numbered backups, of the form config.xyz, where xyz is the first available number from 000 to 100. The backups will have the time and date stamp of the time of the install. There were two backups created during install: one for the DB2 UDB install, one for the search system install. o To remove only search system settings, use the second backup. o To remove both DB2 UDB and search system settings, use the first backup. 2) Remove the search system's directory and all its subtrees. a) Go to the %TMP%/NETQ directory. b) Delete any files that remain in the NETQ directory. c) Delete the NETQ directory. d) Reboot. 20.0 HTML SEARCH SERVER FOR WINDOWS NT, WINDOWS 95, AND WINDOWS 98: SEARCHING HTML DOCUMENTATION IBM DB2 Universal Database (DB2 UDB) online documentation comes with an HTML search system to help you find information. These release notes describe the setup and use of the search system on Windows NT, Windows 95, and Windows 98. The search system consists of a search engine and a search server. The search system is stored in its own directory because it may be used by other products. 20.1 RESTRICTIONS ON USE This search system does not support Windows 3.1. This search system is not enabled for Thin Clients. If you attempt to search the documentation, you will receive a network error. 20.2 INSTALLING THE HTML SEARCH SYSTEM ON WINDOWS NT, WINDOWS 95, AND WINDOWS 98 20.2.1 PREREQUISITES FOR THE SEARCH SYSTEM The following prerequisites are required: o On Windows NT 4.0, for best results you should have installed Service Pack 3. For information, see http://www.microsoft.com/ntserver/info/servicepack3.htm. o You need a browser, such as Netscape 3.0. Ensure that you turn off proxy handling for localhost in the browser you use. o You must have TCP/IP Version 3 or higher installed on your machine. It must be installed and configured in order for the DB2 UDB search system to function properly. For Windows 95, TCP/IP must be enabled as follows: - For a LAN Adapter configuration: - You must have DNS enabled with a valid host and domain name. - Your LAN DNS must resolve "localhost" to 127.0.0.1. - You cannot run disconnected with a LAN adapter configuration. - For a Dial-Up Adapter configuration: - You must have DNS disabled. - Your TCP/IP Address must be obtained automatically. NOTE: These configuration options will apply to all TCP/IP adapters even though they have only been changed for this one. You will not be able to use both LAN and Dial-Up without reconfiguring. Dial-Up networking TCP/IP properties for your internet service provider(s) (ISP) must be configured as documented by the ISP. The Dial-Up networking TCP/IP properties will override the properties in the Dial-Up Adapter TCP/IP properties configured via the "Network" icon in the Windows 95 Control Panel. The overriding of the properties will only take place so long as the Dial-Up Adapter TCP/IP properties are configured as above. Enabling the DNS in the Dial-Up Adapter TCP/IP properties or setting an IP address in the Dial-Up Adapter TCP/IP properties will interfere with the Dial-Up networking configuration for the ISP and must be avoided. For Windows NT 4.0, either of the TCP/IP configurations detailed above will work. If you are running standalone, you can also enable the MS Loopback Adapter without the other two adapters. 20.2.2 STOP ANY PREVIOUSLY INSTALLED VERSION OF THE SEARCH SYSTEM If the search system was previously installed by another product (for example, VisualAge for Java), the search server must be stopped. To stop the search server, click on "Stop HTML Search Server" in the DB2 menu item of the Start Menu. 20.2.3 LOCATING THE SEARCH SYSTEM DIRECTORY The search system is stored in its own directory because it may be used by other products. For example, if you installed DB2 UDB along with the search system on drive G, and later you installed VisualAge for Java on drive H, only one search system is installed -- the one that came first. Some instructions in this document require you to specify the location of this directory. To locate this directory use this command: echo %IMNINSTSRV% 20.2.4 CHANGING TO A DIFFERENT PORT NUMBER FOR THE SEARCH SERVER The search server is assigned to port 49213, a number beyond the public ports assigned for TCP/IP. If you have another product that uses this port, you can change the search server port with the following steps: 1. Edit the HTTPD.CNF file in the search system directory and change the port number to one you know is available, preferably above 49000. 2. Determine where is by issuing the following command: DB2SET DB2PATH 3. Go to the DB2 UDB directory and in the /doc/html directory, use a text editor to edit the DB2SRCH.HTM file. Change the following line to the port you selected in the previous step: where: * is D for DBCS and S for SBCS xx is a two-character identifier for the language in which the documentation is written 49213 is the new port for localhost 4. Stop and start the search server. To stop the search server, click on "Stop HTML Search Server" in the DB2 menu item of the Start Menu. To start the search server, click on "Start HTML Search Server" in the DB2 menu item of the Start Menu. 20.2.5 INSTALLING DB2 UNIVERSAL DATABASE ON A LAN-CONNECTED DRIVE If you install DB2 UDB on a LAN-connected drive on Windows 95 and the drive does not get reconnected before AUTOEXEC.BAT is run on Windows 95, the search system will not be able to have its environment variables set. The search system sets environment variables through a batch file (IMNENV.BAT) that resides in the search system directory (for example, (F:\IMNNQ_95). To bypass this problem, copy IMNENV.BAT from the search system directory to another drive/directory that is connected before AUTOEXEC.BAT is executed. Then modify AUTOEXEC.BAT to call this batch file on startup. For example, if you copy IMNENV.BAT to C:\WINDOWS\IMNNQ, you can add the following line to AUTOEXEC.BAT: IF EXIST C:\WINDOWS\IMNNQ\IMNENV.BAT CALL IMNENV.BAT 20.2.6 DIAGNOSING SEARCH SERVER INSTALL ERRORS If during DB2 UDB install you encounter a failed search server installation or initialization, the DB2 UDB install will proceed to completion. The following will assist in diagnosing what happened and what to do. Look in the \imnnq\install directory, where is your system's %TEMP% directory, to find the IMNNQ.ERR file. If it does not exist, reboot and try installing the product again. If IMNNQ.ERR does exist, here is the possible contents: 1 - This indicates that the current PATH is too long and adding the search server into the PATH will cause the entire PATH to be erased. Note: The limit on Windows NT 4.0 is 512; on Windows 95 it is 255. It is recommended that you perform the following steps: a) Rename the PATH variable in the AUTOEXEC.BAT file (PATHGOOD), save changes and reboot. b) Remove the IMNNQ.ERR file from \imnnq\install. c) Run the product install again to install the search system properly. d) Merge the PATHGOOD variable with the PATH variable that was created by the latest installation. 2 - Miscellaneous error, please contact IBM service. 3 - Out of disk space error. Please ensure that there is at least 4.5MB of disk space for the search system plus enough space for AUTOEXEC.BAT to be changed for Windows 95. If IMNNQ.ERR contains a message saying XXX.EXE DOES NOT EXIST, the search system executables could not be found. Try rerunning the search system install/initialization program again. See 20.3.4, "If Search Does Not Work (ERROR 500 when trying to search)". 20.3 USING THE HTML SEARCH SYSTEM 20.3.1 STARTING AND STOPPING THE SERVER After you install the search system, it will start automatically after you reboot. If the search server starts without any errors but you encounter the following error message in your browser, click "Stop HTML Search Server", then click on "Start HTML Search Server" in the DB2 menu item of the Start Menu. A network error occurred: unable to connect to server. The server may be down or unreachable. Try connecting again later. After you have finished searching, stop the search server to reclaim the memory it used. To stop it, click on "Stop HTML Search Server" in the DB2 menu item of the Start Menu. 20.3.2 SEARCHING WITH PROXIES ENABLED IN NETSCAPE OR INTERNET EXPLORER If you use Netscape or Internet Explorer with proxies enabled manually, you can speed up search significantly by modifying your proxy information. In Netscape 3: -------------- 1. Select Options - Network Preferences. 2. Click the Proxies tab. 3. Click View at the Manual Proxy Configuration selection. 4. In the "No proxies for" box, type: localhost:49213 If you have other entries here, separate them with commas. 5. Click OK to close the Manual Proxy Configuration window. 6. Click OK to exit the Preferences Window. In Netscape 4 (Communicator): ----------------------------- 1. Select Edit - Preferences. 2. Double-click Advanced in the Category tree. 3. Click Proxies in the Advanced subtree. 4. Click View at the Manual Proxy Configuration selection. 5. In the "Exceptions... Do not use proxy servers for domains beginning with" box, type: localhost:49213 If you have other entries here, separate them with commas. 6. Click OK to close the Manual Proxy Configuration window. 7. Click OK to exit the Preferences Window. In Internet Explorer 3: ----------------------- 1. Select View - Options. 2. Select Connection. 3. In the "Exceptions... Do not use proxy servers for domains beginning with" box, type: localhost:49213 If you have other entries here, separate them with commas. 4. Select the "Do not use proxy server for local (intranet) addresses" box. 5. Click OK to exit the Options Window. In Internet Explorer 4: ----------------------- 1. Select View... Internet Options. 2. Select the Connection tab. 3. Select the "Bypass proxy server for local (Intranet) access" check box. NOTE: this check box is only available if you are using a proxy or socks connection and have selected the "Access the Internet Using a Proxy Server" check box. 4. Select the "Advanced" Button. 5. Type: localhost:49213 in the "Exceptions... Do not use proxy servers for addresses beginning with" box. If you have other entries here, separate the new entry with a semi-colon. 6. Select OK, then OK to exit the Options Tab. 20.3.3 SEARCHING WITH A LAPTOP ON WINDOWS 95 Nameservers: If you use a laptop that is normally connected to a LAN as a disconnected development platform, you may run into difficulty searching. To search successfully, you need to disable your nameserver, IP address in your TCP/IP configuration. In other words, you need two separate TCP/IP configurations--one for connected operations and another for disconnected ones. Best fix for multiple TCP/IP configurations in Windows 95: Windows 95 only allows you to have a single TCP/IP configuration. However, there are shareware utilities available on the internet that let you specify more than one setting, and then change them according to your connection status (connected or disconnected from a LAN). TCPSwitch is one of these programs. Socks servers and proxies: If you are using Netscape 3, and you dial-in to a secure site to access the internet with a proxy or socks server, you will need to delete these settings in Netscape before you can search. This is a bug with Netscape 3--Netscape Communicator's "direct connect" setting should fix this. 20.3.4 IF SEARCH DOES NOT WORK (ERROR 500 WHEN TRYING TO SEARCH) If the product installation worked, but searching does not work, try the following steps: 1. Check that the search system was properly installed. The environment variables IMNINST and IMNINSTSRV should be set and IMNINSTSRV should point to the search system directory (see 20.2.3, "Locating the Search System Directory"). 2. The search system directory should contain these files: o A DB2SRSxx or DB2SRDxx executable file (the former is for SBCS documents, the latter is for DBCS documents). The xx is a two-character identifier of the language in which the documents are written. For example, DB2SRSEN is the executable for English language SBCS documents; DB2SRDCN is for S. Chinese DBCS documents. o DB2HEAD.HTM and DB2FOOT.HTM 3. Ensure that the search system is registered with product documentation. Invoke this command to list all of the documentation that is registered with the search system: NQMAP -A //for SBCS TMMAP -A //for DBCS The documentation for DB2 UDB is called DB2ADMxx, DB2APDxx, or DB2CONxx, where xx is a two-character identifier of the language in which the documents are written. One or more of these names should appear in the list of names that NQMAP (or TMMAP) returns. With the list of indexes the NQMAP (or TMMAP) command provides, you can get additional details on each index with the following command: IMNIXSTA //for SBCS IMQIXSTA //for SBCS This command provides details such as the status of the index and the number of documents in the index. If any of the above conditions are not true, you can rerun the DB2 UDB product installation program. If the only condition that is not true is that the files DB2SRCH.EXE, DB2HEAD.HTM and DB2FOOT.HTM are missing, then you can just copy them over from the directory \misc into search system's directory, (for example, E:\IMNNQ_NT). The product installation program will rerun the search server's installation and initialization. 20.3.5 FILE NOT FOUND ERRORS The search system searches predefined indexes. All of these indexes are installed with the search system, regardless of the DB2 UDB products you install. Therefore, when you conduct a search, some of the results might return a "File not found" error because they pertain to either a product that is not installed on your system, or, a document that you chose not to install during installation. 20.3.6 ERROR DETECTED WHEN STARTING THE SEARCH SERVICE If you encounter this error: EHS0410: An error was detected when starting the search service. Stop the service and start it again. Press Enter to continue... (Sometimes the error is EHS0411 but the text is the same. This message comes up on a full black screen.) your hostname might have changed since doing the install, and you have to record it again. Go into the NetQuestion directory and enter: type netq.cfg to see what hostname NetQuestion is using. If you have changed the hostname, for example while configuring DHCP and DDNS, enter: netqinit Example: netqinit c:\imnnq_nt\data 20.4 UNINSTALLING THE HTML SEARCH SYSTEM NOTE: Before uninstalling the DB2 UDB product, the search system must be stopped. To stop the search system, click on "Stop HTML Search Server" in the DB2 menu item of the Start Menu. The search system is uninstalled with the DB2 UDB product by clicking on the "Uninstall" item in the DB2 menu item of the Start Menu. 20.4.1 IF THE SEARCH SYSTEM IS NOT UNINSTALLED FROM WINDOWS NT, WINDOWS 95, OR WINDOWS 98 If the search system does not get uninstalled, the cause may be: o DB2 UDB or another product is using the search system so it cannot be removed. o The search system uninstall did not perform as expected. To determine which products are still registered with the search system, issue the following command: NQMAP -A //for SBCS TMMAP -A //for DBCS If this command returns no indexes, skip to step 6. If the list contains indexes that do not belong to DB2 UDB (that is, their names begin with something other than "DB2"), you cannot remove the search server. If it contains any of the DB2 index file names (DB2ADMxx, DB2APDxx, DB2CONxx), DB2 UDB could not unregister the indexes therefore causing the search system uninstall to fail. This occurs if DB2 UDB was incorrectly uninstalled (for example, the SQLLIB folder was deleted). In this case, you have to manually unregister the indexes and remove the search system directory using the following steps: 1. Issue this command to ensure that the search server is running: IMNSS START SERVER //for SBCS IMQSS.EXE -START DBCSHELP //for DBCS 2. Issue this command for each of the index files: NQMAP -D //for SBCS TMMAP -D //for DBCS 3. Issue this command for each of the index files: NQDELET //for SBCS TMDELET //for DBCS 4. Stop the search server: IMNSS STOP SERVER //for SBCS IMQSS.EXE -STOP DBCSHELP //for DBCS 5. Issue "NQMAP -A" (or "TMMAP -A") and verify that there are no DB2 indexes (DB2xxxxx) remaining. If there are, please contact IBM service. 6. Issue the following command to verify that no other indexes are active. NQCOUNTI //for SBCS TMCOUNTI //for DBCS NOTE: See 20.2.3, "Locating the Search System Directory" if you do not know where it is installed. o If this command returns data that indicates one or more indexes are still active, the search system cannot be removed because other products are still registered. Do not perform the remaining steps. o If the search system returns "0 indices active", run UNINSTNQ.EXE. If the search system still does not uninstall, you can try removing the product manually: 1. Remove the registry entries that are under \\HKEY_LOCAL_MACHINE\SOFTWARE\IBM\NetQuestion. Remove this entry including all its subtrees. 2. Remove the search system's directory and all its subtrees (for example, D:\IMNNQ_NT). 3. Remove the environment variables IMNINST and IMNINSTSRV and remove the search system path from the PATH environment variable. If you still can't uninstall the search system, call IBM service. 20.4.2 REBOOTING AFTER UNINSTALLING THE PRODUCT After a DB2 UDB uninstall, it is very important to reboot before doing another install. The reason is that some search system DLLs can be held by the operating system and are not removed until the next reboot. If a a search system install happens before the reboot, the newly installed search system DLLs will be deleted on the next reboot, rendering the search system unusable. | 21.0 NETQUESTION WORKAROUNDS | 21.1 IMNSEARCH (NETQUESTION) INSTALLATION PROBLEMS ON UNIX PLATFORMS | In some environments, previous installs or uninstalls of the IMNSearch | (NetQuestion) component make it impossible to install the component again. In | short, the component requires that special userID and groupID names exist to | install correctly. Occasionally (usually when IMNSearch has been incompletely | removed) one or the other of these IDs does not exist. In other cases, users | sometimes do not have the right access needed to create these group IDs and | user IDs successfully at installation. Both of these conditions result in the | IMNSearch component failing to install properly. | PROBLEMS INSTALLING IMNSEARCH FOR THE FIRST TIME ON THE MACHINE | If you experience a problem installing the IMNSearch component, and you do | not have a previous version of the product already installed, then please | completely remove the component and then re-install. Note that this includes | removing the "imnadm" userID and groupID before reinstalling. | PROBLEMS INSTALLING IMNSEARCH WHEN ANOTHER IMNSEARCH COMPONENT IS ALREADY | INSTALLED, OR WAS PREVIOUSLY INSTALLED | If you experience a problem installing the IMNSearch component, and you have | another version of the IMNSearch component already installed (or have had it | installed in the past), then do not remove the existing IMNSearch component. | Instead, check to see if there is a group ID called imnadm. If there is, then | you can either: | 1. Create the imnadm userID and reinstall the IMNSearch component | 2. Delete the imnadm groupID and reinstall the IMNSearch component | PROBLEMS INSTALLING IMNSEARCH IN AN AFS/DCE OR NIS/NIS+ ENVIRONMENT | In environments where user ID and group ID management is centralised, that | is, IDs are created on a server by an authorised administrative user, who | then distributes the ID information to client machines, the "imnadm" user IDs | and group IDs have to be created prior to users installing the IMNSearch | component. Since these user IDs and group IDs are shared among all products | that make use of the IMNSearch component, this step needs to be performed | once prior to installing the first product that uses the component. After the | last product that uses the IMNSearch component is un-installed, then the user | ID and group ID should be removed. | 21.2 INSTALLING NETQUESTION ON HPUX-10 | When installing DB2 Universal Database Version 5.2 for HP-UX 10, you may find | that NetQuestion cannot be installed from the product CD-ROM. You may | discover that when you attempt to search the DB2 product documentation, you | get a message stating that the "connection is refused by server". Check if | NetQuestion is installed with the following command: | swlist -l product | grep -i imn | If nothing is returned, then NetQuestion has not been installed. | In this case, you must install NetQuestion manually. Note that you must be | root to install NetQuestion. | Copy the entire "/NetQ" directory from the installation CD-ROM to your local | hard drive. After copying is finished, you should have the following files in | your local "/NetQ/hpux10" directory: "IMNSearch", "IMNSearchB", "IMNpkgadd", | "IMNpkgaddB", "imnbinstall" and "imninstall". Enter: | imninstall | to start the NetQuestion installation script. Follow the on-screen directions | to install either the SBCS search engine alone or the SBCS and DBCS search | engines together. | TROUBLESHOOTING | If "imninstall" returns an error stating "swcopy failed, see manual pages for | swcopy", it is possible that some other file systems completely unrelated to | DB2 have failed to mount the system. In this case, you must (as root) edit | your local copy of "/NetQ/hpux10/IMNpkgadd" by adding the option: | -x mount_all_filesystems=false | to each occurrence of "swcopy", "swremove" or "swinstall". | 21.3 NETQUESTION AND THE DB2 CAE FOR UNIX PLATFORMS | Neither online documentation nor NetQuestion is shipped on UNIX product CDs | containing just the Client Application Enabler. However, if you install the | CAE from any EE or EEE product CD you can also select to install online | documentation; NetQuestion is installed automatically whenever documentation | is installed. | 21.4 NETQUESTION DBCS ON AIX PLATFORMS | DB2 Universal Database Version 5.2 for AIX uses a level of the NetQuestion | double-byte character set (DBCS) search engine that will not work properly if | certain other levels of NetQuestion are already present on your AIX operating | system. | For example, if DB2 UDB V5.2 for Japanese, Korean, Chinese or Taiwanese is | installed on any AIX operating system where DBCS documentation for VisualAge | C++ Professional Version 4.0 for AIX is already installed, or if it is | installed on an AIX Version 4.3 operating system where DBCS native help is | already installed, you will not be able to use NetQuestion to search any DB2 | DBCS documentation you may have installed. This problem is due to the fact | that the search CGI used by other NetQuestion-exploiting products is | incompatible with the search CGI shipped with DB2 UDB Version 5.2 for AIX. | To correct this problem, you must replace the search CGI in | "/var/docsearch/cgi-bin/" and "/usr/docsearch/cgi-bin/" with an updated | search CGI by doing the following: | 1. Download the updated search CGI (called "db2srdbcs") from: | ftp.software.ibm.com/ps/products/db2/info | to "/var/docsearch/cgi-bin" and "/usr/docsearch/cgi-bin". This will | overwrite the existing "db2srdbcs" files in these directories. | 2. Stop and start the DB2 DBCS search engine for this change to take effect | as follows: | o To stop the search engine, enter: | usr/IMNSearch/bin imqss -stop dbcshelp. | o To start the search engine, enter: | usr/IMNSearch/bin imqss -start dbcshelp. 22.0 SCOHELP INFORMATION The following SCOhelp information is available: o 22.1, "How to Access SCOhelp" o 22.2, "Working With National Languages and SCOhelp" o 22.3, "Web Control Center Help and SCOhelp". o 22.4, "Support Level Supplement (SLS) PTF7004A, the UnixWare 7 Language Extension Supplement". 22.1 HOW TO ACCESS SCOHELP You can access SCOhelp in one of the following ways: o From the command line: Issue the command "scohelp" (/usr/bin/X11/scohelp) at the command line. o From the CDE Desktop: Open the "Help" menu (above the help icon in the Control Panel), then select SCO Help. o From the Panorama desktop: Open the "Root" menu, then select Help. 22.2 WORKING WITH NATIONAL LANGUAGES AND SCOHELP 22.2.1 SPECIAL CONSIDERATIONS WHEN USING THE SPANISH LANGUAGE EXTENSIONS The "UnixWare 7 Update 1 (7.0.1)" CD-ROM provides, among other things, fixes for the Spanish Language Extensions (esle). The following DB2 procedures will not work when LANG is set to "es", unless the UnixWare 7 Update 1 is installed: o Creating an instance (regular or DAS instance): To avoid this problem, set LANG to "en" or "C" before creating the instance, then set it back to "es" once the instance is created. This also means that you will not be able to create an instance using the DB2 installer (db2setup) if LANG is set to "es". o Running the db2ishut command. Again, to avoid this problem, set LANG to "en" or "C" before running db2ishut, then set it back to "es" once it is complete. o Running db2istrt: This command is used to start the DB2 server, and is called by the /etc/rc.db2 script. When the first instance is created, the following line is added to the /etc/inittab file to autostart DB2 services when the workstation is rebooted: db:234:once:/etc/rc.db2 > /dev/console 2>&1 # Autostart DB2 Services If the default value of LANG is "es", this line should be commented out from the /etc/inittab file. Once the reboot has completed, log on as root, set LANG to "en" or "C", then run /etc/rc.db2. Set LANG back to "es" once DB2 has started. Note that once UnixWare 7 Update 1 has been installed to fix problems for the Spanish Language Extensions, these problems will disappear. You should uncomment out the line in the etc/inittab file, so that DB2 services will autostart on the next reboot: db:234:once:/etc/rc.db2 > /dev/console 2>&1 # Autostart DB2 Services 22.2.2 CHANGING DEFAULT SCOHELP LANGUAGE ON THE SERVER If you want to change the language SCOhelp returns by default on the server, edit /usr/ns-home/httpd-scohelphttp/config/lang.conf and set DocumentRoot to the desired default language. For instance: DocumentRoot /usr/lib/scohelp/fr Once this change is made, you must restart the web server, with the command: /usr/ns-home/httpd-scohelphttp/restart If this does not work, try stopping the web server, then restarting it, with the commands: /usr/ns-home/httpd-scohelphttp/stop /usr/ns-home/httpd-scohelphttp/start 22.2.3 SETTING THE LANGUAGE SCOHELP RETURNS FOR A SPECIFIC CLIENT Different clients can have SCOhelp return information in different languages. The SCOhelp server uses the *httpAcceptLanguage resource to determine which language to use when a request is received. You can set *httpAcceptLanguage to the required language as follows: o Netscape 3: Edit the Netscape app-defaults file (an example is at /usr/X/lib/app-defaults) and include the line: *httpAcceptLanguage: fr, en o Netscape 4: Select the Edit menu item, then Preferences. Select Navigator from the tree view, then Languages. Add the required language, and make sure it is first in the list. If English is still displayed by SCOhelp, even after *httpAcceptLanguage is set, try removing English from the list altogether. 22.2.4 USING THE SEARCH Note that there is no search in Simplified Chinese and Brazilian Portuguese. You can select multiple languages to search by clicking on the "Options" button on the SCOhelp search panel. The list of installed languages is displayed; select the set of languages to search. 22.3 WEB CONTROL CENTER HELP AND SCOHELP There is no direct link from the Web Control Center help to the DB2 books (displayed using SCOhelp). Use one of the methods described in 22.1, "How to Access SCOhelp" to run SCOhelp and access the DB2 books. There is no search in the SCO DB2 Web Control Center help. 22.4 SUPPORT LEVEL SUPPLEMENT (SLS) PTF7004A, THE UNIXWARE 7 LANGUAGE EXTENSION SUPPLEMENT The "Support Level Supplement (SLS) PTF7004A" is available from the SCO ftp site at: ftp://ftp.sco.com/SLS/ Download the files ptf7004a.Z and ptf7004a.txt PTF7004A "corrects problems with the configuration of the Verity search engine within SCOhelp and with the localization of various utilities and SCOadmin managers." You are required to install this SLS if you will be viewing the DB2 documentation. See the ptf7004a.txt file for more details. 22.4.1 MODIFICATIONS TO LANG.CONF FOR SIMPLIFIED CHINESE AND BRAZILIAN PORTUGUESE Once the PTF7004A is installed, and before you use SCOhelp in Simplified Chinese or Brazilian Portuguese, you must edit the file /usr/ns-home/httpd-scohelphttp/config/lang.conf and make the following changes: 1. Modify the following two lines in the LocalizedDocRoot list and add "_BR" and "_CN" to the end of the subdirectory names, as follows: LocalizedDocRoot pt_PT.ISO8859-1 /usr/lib/scohelp/pt_BR LocalizedDocRoot zh_ZH.ISO8859-1 /usr/lib/scohelp/zh_CN 2. Modify the various Language Aliases for zh: Make sure that the various Language Aliases for zh are mapped to zh_ZH.ISO8859-1 (an underscore between zh and ZH, NOT a dash). The lines should read: LanguageAlias zh zh_ZH.ISO8859-1 This value then matches the line: LocalizedDocRoot zh_ZH.ISO8859-1 /usr/lib/scohelp/zh_CN Once these changes are made restart (or stop and start) the web server, as described in 22.2.2, "Changing Default SCOhelp Language on the Server" on page 170. 22.5 SCOHELP SEARCH INDEX FILES SCOhelp stores all of its search index files in the /usr/lib/scohelp//_SearchIndex/parts directory. These files will continue to grow in size as you install products, but will not get smaller as you remove products (using the current UnixWare 7 utilities). This is the case for DB2 as well as other products. If you delete these files, your SCOhelp search will no longer work properly. (C) Copyright IBM Corp. 1999