k pV1V1by nameby nameserviced kꄦhBOV1V1V1 WO./lpp_name 4 R S db2_02_01 { db2_02_01.sdk.c 04.01.0001.3000 1 N U US_ENG DB2 C Language Include Files and Samples [ *prereq db2_02_01.sdk.c 4.1.1.2 *ifreq db2_02_01.client 04.01.0001.3000 % /usr/lpp/db2_02_01/include/sql.h 136 /usr/lpp/db2_02_01/include/sqlaprep.h 136 /usr/lpp/db2_02_01/include/sqlcli.h 32 /usr/lpp/db2_02_01/include/sqlcli1.h 88 /usr/lpp/db2_02_01/include/sqlcodes.h 80 /usr/lpp/db2_02_01/include/sqlenv.h 192 /usr/lpp/db2_02_01/include/sqlstate.h 64 /usr/lpp/db2_02_01/include/sqlutil.h 368 /usr/lpp/db2_02_01/samples/c/db2mon.c 256 /usr/lpp/db2_02_01/samples/c/evmprint.c 88 ] }  k*V1V1V1 *./usr/lpp/db2_02_01/db2_02_01.sdk.c/4.1.1.3000/liblpp.a  188400 0 68 182306 0 509 678 0 834095614 0 2 444 9 copyright` Licensed Materials - Property of IBM 5765-453 (C) Copyright International Business Machines Corporation. 1993, 1996. 5765-454 (C) Copyright International Business Machines Corporation. 1993, 1996. 5765-455 (C) Copyright International Business Machines Corporation. 1993, 1996. 5765-642 (C) Copyright International Business Machines Corporation. 1993, 1996. All rights reserved. US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. 381 1168 68 834098936 2379 200 644 18 db2_02_01.sdk.c.al` ./usr/lpp/db2_02_01/include/sql.h ./usr/lpp/db2_02_01/include/sqlaprep.h ./usr/lpp/db2_02_01/include/sqlcli.h ./usr/lpp/db2_02_01/include/sqlcli1.h ./usr/lpp/db2_02_01/include/sqlcodes.h ./usr/lpp/db2_02_01/include/sqlenv.h ./usr/lpp/db2_02_01/include/sqlstate.h ./usr/lpp/db2_02_01/include/sqlutil.h ./usr/lpp/db2_02_01/samples/c/db2mon.c ./usr/lpp/db2_02_01/samples/c/evmprint.c 406 1684 678 834098938 0 200 444 20 db2_02_01.sdk.c.size` /usr/lpp/db2_02_01/include/sql.h 136 /usr/lpp/db2_02_01/include/sqlaprep.h 136 /usr/lpp/db2_02_01/include/sqlcli.h 32 /usr/lpp/db2_02_01/include/sqlcli1.h 88 /usr/lpp/db2_02_01/include/sqlcodes.h 80 /usr/lpp/db2_02_01/include/sqlenv.h 192 /usr/lpp/db2_02_01/include/sqlstate.h 64 /usr/lpp/db2_02_01/include/sqlutil.h 368 /usr/lpp/db2_02_01/samples/c/db2mon.c 256 /usr/lpp/db2_02_01/samples/c/evmprint.c 88 1043 2844 1168 834098938 0 200 444 25 db2_02_01.sdk.c.inventory` /usr/lpp/db2_02_01/include/sql.h: owner = bin group = bin mode = 444 type = FILE size = 68183 /usr/lpp/db2_02_01/include/sqlaprep.h: owner = bin group = bin mode = 444 type = FILE size = 68402 /usr/lpp/db2_02_01/include/sqlcli.h: owner = bin group = bin mode = 444 type = FILE size = 15866 /usr/lpp/db2_02_01/include/sqlcli1.h: owner = bin group = bin mode = 444 type = FILE size = 41168 /usr/lpp/db2_02_01/include/sqlcodes.h: owner = bin group = bin mode = 444 type = FILE size = 38753 /usr/lpp/db2_02_01/include/sqlenv.h: owner = bin group = bin mode = 444 type = FILE size = 95268 /usr/lpp/db2_02_01/include/sqlstate.h: owner = bin group = bin mode = 444 type = FILE size = 30768 /usr/lpp/db2_02_01/include/sqlutil.h: owner = bin group = bin mode = 444 type = FILE size = 187182 /usr/lpp/db2_02_01/samples/c/db2mon.c: owner = bin group = bin mode = 444 type = FILE size = 127417 /usr/lpp/db2_02_01/samples/c/evmprint.c: owner = bin group = bin mode = 444 type = FILE size = 41778 173945 176890 1684 834098304 0 200 444 10 lpp.README` *********************************************************************** ** ** ** (c) COPYRIGHT INTERNATIONAL BUSINESS MACHINES CORPORATION 1996. ** ** ALL RIGHTS RESERVED. ** ** ** *********************************************************************** Welcome to IBM Database Server for AIX! Version 4 +--- NOTE! ----------------------------------------------------------+ | | | Set the font to monospace for better viewing of this README. | | | +--------------------------------------------------------------------+ This README file contains information for the following Database Server products and components: AIX-Specific ------------ IBM DB2 Server (DB2 for AIX Version 2.1.1) IBM DB2 World Wide Web Connection IBM Distributed Database Connection Services Multi-User Gateway for AIX (DDCS Multi-User Gateway for AIX Version 2.3.1) For information, see also Part Number Publication ----------- ----------------------------------------------------------- 33H4879 IBM* Database Server for AIX Up and Running! --------------------------------------------------------------------------- 1.0 Contents 2. INSTALLATION NOTES 2.1 System Requirements 2.2 Installing DDCS and Database Server on the Same Workstation 2.2.1 Considerations When DDCS Is Already Installed 2.2.2 Installing DDCS and Database Server 2.2.3 Applying Fixes or Installing Additional Database Server Products 2.3 Prerequisite Notes 2.4 Automatic Adjustments to your System During Install 2.5 Installing Translated Messages on AIX 4.1 Systems 2.6 Information Presentation Facility (IPF/X) Considerations 2.6.1 Consideration for AIX Version 4 (General Users) 2.6.2 Consideration for AIX Version 4 (Simplified Chinese Only) 2.6.3 Considerations for AIX (Japanese Users) 2.6.4 Printing PostScript files from IPF/X 2.7 TZ Environment Variable 2.8 LAN Support Program and NetBIOS rc=0x18 3. MIGRATION CONSIDERATIONS FROM DB2 2.1.0 PRODUCTS OR DB2 2.1.1 BETA DRIVERS 3.1 Coexistance with DB2 V2.1.0 and DDCS V2.3.0 products 3.2 Migrating from the DB2 2.1.0 GA or from the DB2 2.1.1 Beta 3.2.1 Database Considerations 3.2.2 Application Considerations 3.2.3 Installing Over DB2 V2.1.0, DDCS V2.3.0 Products or the DB2 Beta V2.1.1 4. USAGE NOTES 4.1 Database Directory Utility 4.1.1 Linking to Online Books from Database Director Online Help 4.1.2 If Problems are Encountered when Running the Database Director 4.1.3 DBCS Environment 4.2 Roll Forward and Log File Size 4.3 Using DCE Directories on AIX 4.1 Systems 4.4 Import/Load APIs 4.5 sqlestrd() API 4.6 Enabling IBM DB2-SMARTsort for DB2 LOAD 4.6.1 Enabling SMARTsort under DB2 4.6.1.1 Internal Information 4.6.2 Disabling SMARTsort under DB2 4.6.3 Programs 4.6.4 DB2 4.7 DARI and UDF Considerations for National Language-Specific Processing 4.8 Package Name Considerations in Mixed Code Page Environments 4.9 Japanese and Traditional-Chinese EUC Code Page Environments 4.9.1 Mixed EUC/Double-Byte Client and Database Considerations 4.9.2 Character Conversion Considerations for Traditional-Chinese Users 4.9.2.1 Code Page 964 (Traditional-Chinese EUC) 4.9.2.2 Code Page 950 (Traditional-Chinese Big-5) 4.9.3 DDCS-Specific Prerequisites and Limitations 5. ENVIRONMENT CONSIDERATIONS 5.1 Copying a Shared Library 5.2 Receiving Error Message -30081 when Using the TCP/IP Protocol 6. COMMUNICATIONS ISSUES 6.1 TCP/IP 7. PROGRAMMING NOTES 7.1 Sample Applications 7.2 Considerations when Using Micro Focus COBOL 7.2.1 Updating the Micro Focus COBOL Runtime System 7.2.2 Configuring CICS for Use With DB2 2.1.1 and Micro Focus COBOL on UNIX Platforms 7.3 BINARY/COMP-4 COBOL Data Types in the DB2 Precompiler 7.4 Using REDEFINES in COBOL Group Data Items 7.5 Running DB2 for AIX COBOL Stored Procedures 7.6 Building Micro Focus COBOL Stored Procedures on AIX 7.7 Precompiler Customization 7.8 Considerations When Using REXX Stored Procedures 8. AIX ODBC DRIVER 8.1 Common Application Problems and Work Arounds 8.2 SQLDescribeParam() 8.3 New CLI/ODBC Configuration Keywords in db2cli.ini 8.3.1 CLI/ODBC Trace File 8.3.2 Specifying a Directory for Temporary Files 8.3.3 SQLDescribeParam() Enable/Disable 8.3.4 Asynchronous ODBC Enable/Disable 9. CHANGES TO THE DB2 DOCUMENTATION 9.1 For the "Changes for This Release" Section 9.2 Administration Guide 9.3 API Reference 9.3.1 Backup and Restore API's for Vendor Products Appendix DB2-INFO Data Structure 9.3.2 QUIESCE TABLESPACES FOR TABLE 9.4 Command Reference 9.4.1 FORCE APPLICATION 9.4.2 ROLLFORWARD DATABASE 9.4.3 EXPORT 9.4.4 QUIESCE TABLESPACES FOR TABLE 9.4.5 RUNSTATS Enhancement 9.5 Call Level Interface Guide and Reference 9.5.1 SQLColAttributes - Get Column Attributes 9.5.2 New CLI/ODBC Configuration Keyword 9.6 Database System Monitor Guide and Reference 9.6.1 db_conn_time Data Element 9.6.2 Buffer Pool Data Pages Written to Disk 9.6.3 DBMON Element Formula 9.6.4 Buffer Pool Threshold Cleaners Triggered 9.6.5 Event Start Time 9.6.6 Package Cache Inserts/Package Cache Lookups 9.6.7 Status of Database and Status of DB2 Instance 9.7 Planning Guide 9.7.1 Segments Versus 4KB Pages 9.7.2 BUFFPAGE and SORTHEAP 9.8 Configuration Parameters 9.8.1 DIR_CACHE 9.8.2 MAXAPPLS 9.8.3 REC_HIS_RETENTN 9.8.4 Configuration Parameters (YES/NO and ON/OFF Values) 9.8.5 DFT_QUERYOPT 9.8.6 LOGFILSIZ 9.9 Building Your Applications 9.9.1 C++ Type Decoration Consideration 9.10 Installation and Operation Guides 9.11 SQL Reference 9.11.1 ALTER TABLE Notes Addition 9.11.2 SQL Communications Area (SQLCA) 9.11.3 GROUP-BY and ORDER-BY Changes 9.11.3.1 Expressions Section 9.11.3.2 Queries Section 9.12 Application Programming Guide 9.13 References to CompuServe 9.14 Database Server Up and Running! 9.15 Messages Reference 9.15.1 New Messages 9.15.2 Changed Messages 10. DB2 WORLD WIDE WEB CONNECTION FOR AIX INFORMATION 10.1 Installation Notes 10.2 DB2 WWW Connection Configuration 10.3 Usage Notes 10.3.1 Authentication 10.4 Using the Sample Application 10.4.1 Installation Notes 10.4.2 DBCS Considerations When Using the Sample Application 11. DDCS FOR AIX INFORMATION 11.1 Prerequisites 11.1.1 Need to Apply DB2 for MVS PTF to Use DB2DRDA as RTPN 11.2 Documentation Changes 11.2.1 Installation Requirements 11.2.2 National Language (Code Page and Conversion) Information Changes 11.2.3 OS/2 and UNIX-Based Databases 11.2.4 SET CURRENT PACKAGESET Support 11.2.5 DB2 for OS/400 Column Types and Length 11.2.6 Binding DDCS Utilities to an OS/400 Database 11.2.6.1 Possible Solutions 11.2.7 Binding Utilities for Back-Level Clients 11.2.8 Description of Environment Keywords 11.2.9 DDCS (non-OS/2) Auto Disconnect Option Not Supported 11.2.10 SPM Instance Name 11.3 Syncpoint Manager (SPM) 11.3.1 AIX SNA Server Coexistence Considerations 11.3.2 Prerequisite PTFs 11.3.3 AIX/SNA Server Operational Considerations 11.4 Authentication Default 11.5 Performance Troubleshooting 11.6 DDCS 2.3 Gateway Connection to DB2/MVS 11.6.1 Summary of Steps 11.6.2 Products used in this example 11.6.3 Network Element Names 11.6.4 Configuring SNA Communications 11.6.5 Cataloging DB2 Directory Entries at the Workstation 11.6.5.1 Cataloging the Workstation 11.6.5.2 Cataloging the Database 11.6.5.3 Cataloging the DCS Database 11.6.5.4 Verify DCS Directory Information 11.6.6 Configuring VTAM 11.6.7 VTAM DB2 APPL Definition 11.6.7.1 VTAM DB2 PU and LU definitions 11.6.7.2 Sample Log Mode Definition 11.6.8 Configuring DB2/MVS 11.6.8.1 Updating the SYSIBM.SYSUSERNAMES Table 11.6.8.2 Update the SYSIBM.SYSLUNAMES Table 11.7 Connectivity Via DDCS (Thailand Users Only) 11.8 DDCS User's Guide BIND Information 11.9 Using DDCS with Bull SNA on AIX 4.1 11.9.1 Installing Bull SNA Support with DB2/6000 Client or Gateway 11.9.1.1 Configuring the Bull DPX/20 SNA/20 Server 12. SAMPLE TOOLS AND UTILITIES 13. LIMITATIONS IN DOCUMENTED FUNCTIONS 13.1 Transaction Manager Database Considerations 13.2 Recursive SQL Incompatibility 13.3 Command and API Limitations 13.3.1 Remote Administration Command 13.4 Call Level Interface Limitations 13.4.1 Limited Support for SQLDriverConnect on Motif Systems 13.5 WCHARTYPE CONVERT Precompile Option Limitation 13.6 Using DB2 with an XA-Compliant Transaction Manager 13.7 DB2 SNMP Subagent 13.8 Database Director Limitations 13.8.1 Mount Container 13.8.2 Restriction for Visual Explain 13.8.2.1 Snapshot Monitor 13.8.2.2 Event Analyzer 13.9 Online Backup and Load with COPY NO option 14. MISCELLANEOUS 14.1 DB2 Family Newsletter ====================== 2. Installation Notes 2.1 System Requirements See the Up and Running! Guide for the system requirements for Database Server components. 2.2 Installing DDCS and Database Server on the Same Workstation 2.2.1 Considerations When DDCS Is Already Installed If DDCS was installed from the DB2 Family CD or tape, you MUST re-install DDCS using the Database Server CD. Otherwise, you will not be able to apply any Database Server PTFs. Use the instructions in the Up and Running! book to help you install DDCS. 2.2.2 Installing DDCS and Database Server If you want to install both DDCS and Database Server on the same workstation, perform the following steps: o Order the DDCS Multi-User Gateway Version 2.3.1 program package to obtain a license key. Do NOT INSTALL DDCS from this package. o Install both products from the Database Server CD. o Apply the keys for the DB2 Server and DB2 Software Developer's Kit that are provided in the Database Server box. o Apply the key for DDCS that is provided in the DDCS Multi-User Gateway box. YOU MUST INSTALL DDCS FROM THE DATABASE SERVER CD. 2.2.3 Applying Fixes or Installing Additional Database Server Products Once the Database Server product is installed on a workstation, you must install any additional products ONLY from the Database Server CD. DO NOT install any products from the DB2 Family media. You must apply all PTFs for the workstation from the Database Server PTFs. DO NOT apply any PTFs from the DB2 Family PTFs. 2.3 Prerequisite Notes If the prerequisites that are listed in Appendix A of the Installation and Operation Guide are not on the system for the following options, installation of the these options will still proceed successfully: db2_02_01.cs.sna DB2 Communications Support - SNA db2_02_01.cs.drda DB2 Communications Support - DRDA Application Server db2_02_01.ddcs DDCS DRDA Application Requestor These options will only fail to install if their prerequisites are installed but are not at the required levels or required PTFs are missing. Also, there is no installation-time check for the prerequisites listed for the options: db2_02_01.cs.rte DB2 Communications Support - Base with TCPIP db2_02_01.cs.ipx DB2 Communications Support - IPX Therefore, if you wish to use DB2 Communications Support with TCP/IP or IPX/SPX communications protocols, you can either install the required prerequisites before or after you install these DB2 Communications Support options. ipfx.Runtime or ipfx.rte is a prerequisite for: db2_02_01.dd DB2 Database Director Note: For Chinese users, you can only use the ipfx.Runtime prerequisite. 2.4 Automatic Adjustments to your System During Install When the db2_02_01.db2.rte option is installed, the following adjustments are automatically made to your system: o The maximum number of user processes will be set to at least 500. In addition, the following user attributes of the instance owner will be adjusted when an instance is created: 1. The maximum file size (fsize) is set to 2 gigabytes. 2. The File Creation Mask (umask) is set to 022. 2.5 Installing Translated Messages on AIX 4.1 Systems Translated messages for locales, other than En_US (English or United States code page IBM-850), are not automatically installed via SMIT on AIX 4.1 systems according to the setting of primary language environment. To install messages for locales other than English in the United States via SMIT, you need to select "Install/Update From All Available Software" from within the "Install/Update Selectable Software (Custom Install)" menu. After entering the "INPUT device/directory for software", you can then list the "SOFTWARE to install" and select the message options for the desired locales. The message options will be listed as: DB2 Product Messages - where is the desired locale, for example locale=Fr_FR for French in France with the IBM-850 code set. 2.6 Information Presentation Facility (IPF/X) Considerations Database Server products are shipped with IPF/X Version 2.2. This version of IPF/X is automatically installed on your workstation when Database Server is installed using software bundles. The messages and online documentation for the language locale of your workstation are automatically installed when using software bundles to install Database Server. If you want to install documentation for other languages locales, you must also select the message option for each one. For example, to add the French documentation, you would select the db2_02_01.doc.Fr_FR.ipfx and the db2_02_01.msg.Fr_FR options. The DB2 Installation Guides contain information about IPF/X. The information in these sections has changed as follows: o The information in the "Restrictions" sections is no longer valid and should not be used. o Any references to ipfx.Runtime should be changed to ipfx.rte. o In the section "Considerations for Non-English Users" under the "Requirements" heading, the information about installing the proper IPF/X support no longer applies. 2.6.1 Consideration for AIX Version 4 (General Users) All online documentation is created using IBM code pages. To view the books properly, you may have to install additional locales and fonts on your workstation. For example, if you wish to view the online books in English, and your locale setting is En_US, you need to ensure that the En_US locale is also installed on your workstation. You can verify this with the command: locale -a If En_US does not appear in the resulting list, please install the following LPP from your AIX installation media: bos.loc.pc_compat.En_US For other languages, please install the corresponding LPP as follows: Language LPP to be installed -------- ------------------------- English bos.loc.pc_compat.En_US French bos.loc.pc_compat.Fr_FR German bos.loc.pc_compat.De_DE Italian bos.loc.pc_compat.It_IT Portuguese bos.loc.pc_compat.Pt_PT Spanish bos.loc.pc_compat.Es_ES The proper fonts need to be installed as well. Please ensure that the following filesets are installed on your workstation: X11.compat.fnt.pc X11.fnt Otherwise, some of the graphics (especially the tables) will not appear correctly online. If the fonts are installed, but the bos.loc.pc_compat.%L LPPs (or the IBM PC locales) are not installed, then you can try to set the values for xview*genFont and xview*monoFont in you environment to different values. For example, you can add the two following lines to you .Xdefaults file: xview*genFont:-linotype-helvetica-medium-r-normal--19-140-100-100-p-102-ibm-850 xview*monoFont:-linotype-helvetica-medium-r-normal--19-140-100-100-p-102-ibm-850 You can try using other ibm850 fonts. 2.6.2 Consideration for AIX Version 4 (Simplified Chinese Only) If you are running version 4 of the AIX operating system, you require version 4.1.3 to view the online books and online help. This consideration does not apply to AIX version 3. 2.6.3 Considerations for AIX (Japanese Users) For Japanese users to display online books and the online help properly, please include the following lines in the .Xdefaults file under the /home/user_id directory: xview*genFont: romankn17;kanji17;IBM_JPN17;kana17: xview*monoFont: romankn17;kanji17;IBM_JPN17;kana17: xview*iconFont: romankn17;kanji17;IBM_JPN17;kana17: xview*titleFont: romankn17;kanji17;IBM_JPN17;kana17: 2.6.4 Printing PostScript files from IPF/X IPF/X allows printing of individual panels or entire sections to PostScript printers. To allow general user to print, you need to modify the file protection mode for the file /usr/lpp/ipfx/misc/ps.prolog to world-readable. You can do this by logging on with root authority, and then issuing the command: chmod o+r /usr/lpp/ipfx/misc/ps.prolog 2.7 TZ Environment Variable When connecting a client to the server which runs on an operating system that supports the concept of local time versus Universal Coordinated Time (UCT, also known as Greenwich Mean Time or GMT), the client machine should also set up timezone information that corresponds to the server machine. If it is not set, the client may display certain timestamps (that are returned by the server) incorrectly (being off by the difference between the UCT and local time). So for example, if a server is running under Pacific Standard Time (PST) and a client is running under Eastern Standard Time (EST), timestamps on the client will show PST unless the TZ variable is set. 2.8 LAN Support Program and NetBIOS rc=0x18 If you use IBM LAN Support Program to provide NetBIOS communications protocol support, and you load the LAN Support Program device drivers in DOS upper / high memory, you may encounter intermittent NetBIOS communications errors "SQL30081, rc=0x18". To avoid these communications errors, load the LSP device drivers in conventional memory. ====================================================================== 3. Migration Considerations from DB2 2.1.0 Products or DB2 2.1.1 Beta Drivers 3.1 Coexistance with DB2 V2.1.0 and DDCS V2.3.0 products The Database Server for AIX CD contains updated versions of all the products. If you are installing your new product from this CD, you can also update any other DB2 V2.1.0 or DDCS V2.3.0 products currently installed. Alternatively, you can contact IBM Service to obtain the DB2 V2.1.1 or DDCS V2.3.1 service refresh for other products already installed. 3.2 Migrating from the DB2 2.1.0 GA or from the DB2 2.1.1 Beta 3.2.1 Database Considerations The following points apply to the workstations that have either installed the 2.1.1 Beta driver or one of the following products: IBM DB2 for AIX Server V2.1.0 IBM DB2 for AIX Single-User V2.1.0 and they have created databases with these code levels. o It is strongly recommended that you perform an optional migration on any of your databases that were created by the V2.1.0 database manager, in order to give your applications access to the new scalar functions that have been implemented in V2.1.1. o DBADM authority is granted implicitly by DB2 when a SYSADM user creates a view or package that requires DBADM authority. However, if such a user is later removed from the SYSADM group, their DBADM authority will also be removed. Therefore, any users with DBADM authority who do not belong to the SYSADM group must have been explicitly granted DBADM access. o It is recommended that you use the runstats utility to update the index statistics for your tables. o The total database log file size (i.e the combined size of all the primary and secondary files in the database log directory) is limited to 4 gigabytes. This is now enforced in the update database configuration command/API where the product of logfilsiz * (logprimary + logsecond) cannot exceed 1048575. As a result: - Any databases which have been configured in 2.1.0 with values that violate this condition should be re-configured. - If the database has not been reconfigured to satisfy this condition, any attempt to update any other database configuration parameters will fail. 3.2.2 Application Considerations The following points apply to systems that have either installed the 2.1.1 Beta driver or one of the following products: IBM DB2 Software Developer's Kit for AIX V2.1.0 IBM DB2 for AIX Single-User V2.1.0 and applications have been created at these code levels. o If a V2.1.0 application is compiled and linked using an x1C 1.x compiler, the application may fail to run when you upgrade to V2.1.1. There are two possible solutions: 1. If you have the source code of the application, relink the application. 2. If you do not have access to the source code of the application, set the following LIBPATH: LIBPATH=/usr/lpp/db2_01_01/lib:/usr/lpp/db2_01_01/.rtelib:/usr/lib:/lib export LIBPATH o If you are migrating from the V2.1.1 beta driver, you must rebind your applications prior to running them. Use the db2rbind command to rebind all packages on your databases. 3.2.3 Installing Over DB2 V2.1.0, DDCS V2.3.0 Products or the DB2 Beta V2.1.1 If you have instances which have been created with any of the DB2 V2.1.0 or DDCS V2.3.0 products or the Beta V2.1.1 then you will need to perform the following steps: 1. Logon as root 2. Make the directory /usr/lib/db2/V2.1/instance 3. Move /usr/lpp/db2_02_01/instance/db2idir to /usr/lib/db2/V2.1/instance/db2idir 4. For each instance: o Logon as sysadm for the instance o Stop all your DB2 applications o Stop the database manager (db2stop) o Stop the DB2 license daemon (issue the db2licd end command) o Stop all command line processor sessions (Issue a db2 terminate command for in each session that was running the command line processor. This step has to be performed by every user that has an active session that has used the command line processor). Logon as root and do the following: 1. Run the /usr/sbin/slibclean command 2. If the 2.1.1. Beta driver is installed then it is recommended that you de-install the Beta driver first before installing the 2.1.1 code. NOTE: On AIX 3.2 systems, if the Beta driver is in COMMITTED state, you will need to FORCE APPLY the Beta driver first before you can REJECT it. If you have a DB2 V2.1.0 or a DDCS V2.3.0 product installed then you can either de-install these products first or you can install the new DB2 V2.1.1 or DDCS V2.3.1 products on top of them. NOTE: On AIX 3.2 systems, if the DB2 V2.1.0 or DDCS V2.3.0 products are in APPLIED state then you must either REJECT or COMMIT them before you can install the new products (REJECTING the old versions is the recommended approach). 3. Install the DB2 V2.1.1 and/or DDCS V2.3.1 Products using SMIT or installp 4. For each instance on the machine run the command: /usr/lpp/db2_02_01/instance/db2iupdt instance (where instance is the name of the instance. You can get a list of all the instances on the machine by issuing the /usr/lpp/db2_02_01/instance/db2ilist command) =============== 4. Usage Notes 4.1 Database Directory Utility 4.1.1 Linking to Online Books from Database Director Online Help If you are using Database Director online help, and you try double-clicking on a hypertext link in order to open an online book but the book is not displayed (that is, you receive an error message), it is possible that the online book has not been installed. You may need to install the English version of the book if it has not been translated or the BOOKSHELF environment variable has not been set. NOTE: If the book is not translated and you wish to view the English version, update the value of the environment variable BOOKSHELF to include: /usr/lpp/db2_02_01/doc/%L:/usr/lpp/db2_02_01/doc/En_US Where %L represents the language locale you selected in the initial installation of online books. 4.1.2 If Problems are Encountered when Running the Database Director If the Database Director program cannot be loaded, the problem may be caused by the setting of the LIBPATH environment variable. If this problem occurs one solution is to unset the LIBPATH environment variable by issuing the command: unset LIBPATH before running the Database Director again. If you are running another application in the same window as the Database Director and the other application requires you to set LIBPATH, then you can open another window and run the Database Director from the newly open window (make sure you issue the command "unset LIBPATH" in the new window). 4.1.3 DBCS Environment If you are running AIX Version 4.1.3 in the DBCS environment, selecting the Cancel pushbutton from some Database Director windows may result in abnormal ending of your application. This problem is referenced in APAR #IX55448, and is fixed with PTF U441217. 4.2 Roll Forward and Log File Size If the log file size (LOGFILSIZ) was modified during the course of normal processing, then database level roll forward through these log files will behave as follows: On any invocation of roll forward, only log files with the same log file size will be processed. When roll forward encounters a log file of different size (excluding truncated log files), roll forward processing will stop and return error SQL1042N. Diagnostic information will be logged. You will then be required to re-issue the roll forward command to continue processing from the next log file. 4.3 Using DCE Directories on AIX 4.1 Systems To use DCE Directory Services with DB2 on an AIX 4.1 system the AIX 4.1 system must be at level AIX 4.1.4 or higher and dce.pthreads.rte must be installed. If DB2 is installed on a system which is below level AIX 4.1.4 or it is installed on an AIX 4.1.4 or higher system that does not have dce.pthreads.rte installed and you later decide that you want to use DCE Directory Services with DB2 on an AIX 4.1 system then you must do the following: For each instance on the machine 1. Logon as sysadm for the instance 2. Stop all your DB2 applications 3. Stop the database manager (db2stop) 4. Stop the DB2 license daemon (issue the db2licd end command) 5. Stop all command line processor sessions (Issue a db2 terminate command for in each session that was running the command line processor. This step has to be performed by every user that has an active session that has used the command line processor). Logon as root and do the following: 1. Run the /usr/sbin/slibclean command 2. Upgrade your AIX 4.1 system to level AIX 4.1.4 or higher and ensure that you also install the optional DCE Threads Compatibility Library (dce.pthreads.rte) 3. Run the command: /usr/lpp/db2_02_01/cfg/db2cfgos 4. For each instance on the machine run the command: /usr/lpp/db2_02_01/instance/db2iupdt instance (where instance is the name of the instance. You can get a list of all the instances on the machine by issuing the /usr/lpp/db2_02_01/instance/db2ilist command) User C/C++ applications that read DB2 database or database locator or routing information objects from the DCE directory services on AIX 4.1.4, need to relink using the xlc_r4 or xlC_r4 command. The following is the error returned by the application if it is not relinked: The DCE Compatibility Library was not initialized. The application may not have been compiled with the correct compiler (cc_r4/xlc_r4). NOTE: There are no limitations on AIX 3.2. 4.4 Import/Load APIs The following are changes to the import API: o Additional information is stored in IXF files to be used by import with the CREATE option. If you specify a select statement of the format "SELECT * FROM tablename" on export, the following information will also be retrieved and stored in the IXF file: - the default value for each column - the original lob length even if the data is being truncated or the lobs are stored in individual files as specified with the LOBSINFILE option - the user defined type. When the IXF file is used to create a new table, this new information will be used. o You can specify a target tablespace when issuing a import with the CREATE option. New syntax allowed for the tcolstrg is: CREATE INTO tablename [(column_list)] [IN tablespace [INDEX IN indextblespace] [LONG IN longtblspace]] Note that to specify a tablespace for the indexes or long data, you must specify a tablespace for the table. o If you explicitly do not specify a source column for one of the target table columns, one of the following things will happen: - if the column is defaultable, the default will be imported - if the column is nullable and not defaultable, a NULL will be imported - if the column is not nullable and not defaultable, an error will be returned and the utility will stop processing. Previously, if a source column was not provided, and if the column was nullable, a NULL would have been imported, but if the column was not nullable, an error would have been returned. If the old behavior is desired, specify the NODEFAULTS option in the filetype-modifier. The following is a change to both the import and load APIs: o A source column may be designated for a target table column but may be omitted for any given row instance. Examples of missing data are: - for DEL files, ",," is specified for the column - for DEL/ASC/WSF files, a row that does not have enough columns or is not long enough for the original specification. In these cases, import/load was used to import/load a NULL if the column was nullable, and reject the row if the column is not nullable. If the USEDEFAULTS option is specified in the filetype-modifier, the following will be the new behavior: - if the column is defaultable, the default will be imported/loaded - if the column is nullable and not defaultable, a NULL will be imported/loaded - if the column is not nullable and not defaultable, the row will be rejected. 4.5 sqlestrd() API Applications which use the sqlestrd() API to connect to databases must change this to EXEC SQL CONNECT syntax if the application needs to take advantage of using version 2 distributed unit of work features specified by precompiler options. 4.6 Enabling IBM DB2-SMARTsort for DB2 LOAD The following information applies to the use of DB2 LOAD to load a table: For AIX environments, the DB2-SMARTsort Accelerator provides improved performance when compared to the current performance of DB2 LOAD with Index Create without SMARTsort. SMARTsort is designed to work with IBM's DB2 2.1.1 with the appropriate service update. When used in conjunction with DB2's LOAD with Create Index function, SMARTsort can reduce the time it takes to load a database, especially when processing large amounts of data. Once SMARTsort is enabled under DB2, DB2 calls SMARTsort automatically every time the LOAD with Index Create function is invoked. All programs, scripts or command line calls that use DB2 LOAD with Index Create can take full advantage of the performance of SMARTsort. NOTE: See the SMARTsort Release 2 READ.ME file for specific service requirements. Users also have the option to produce a log of all SMARTsort invocations made by DB2, or, they can choose not to create such a log and allow all DB2-to-SMARTsort calls to be transparent. Either way, DB2 Loads will be able to exploit the performance advantages of using SMARTsort, without any additional set-up by the user. 4.6.1 Enabling SMARTsort under DB2 Any DB2 user who wishes to use SMARTsort, during DB2 LOAD with Index Create processing, can enable its use by adding the DB2SORT environmental variable to their .profile or db2profile file. As with other DB2 environmental variables, the DB2SORT environmental variable must be exported before starting DB2. It may also be necessary to issue a 'db2stop' and 'db2start' command to stop and restart the database manager. DB2SORT=SMARTSORT export DB2SORT Figure 1. DB2SORT environmental variable By exporting the optional DB2SORTLOG environmental variable (see Figure 2), you can collect a log of all DB2 invocations of SMARTsort. DB2SORTLOG should be set to the name of a directory that SMARTsort can write to. Each time DB2 invokes SMARTsort, SMARTsort makes an entry to the smrtsort.log file, located in this directory. For example: DB2SORTLOG=/tmp would create a smrtsort.log file and a serverX.log file, in a directory named /tmp. If no directory name is specified, or an invalid directory name is provided, no log file is created. DB2SORTLOG=/tmp export DB2SORTLOG Figure 2. DB2SORTLOG environmental variable The smrtsort.db2 file, located in /usr/lpp/smartsort (see Figure 3), shows how the DB2SORT and DB2SORTLOG environmental variables can be coded in a functioning Shell Script. You can call this Script directly from your db2profile or .profile file. #---------------------------------------------------------------- # Licensed Materials: Property of IBM # 5765-349 # (c) IBM Corporation 1994, 1996 # All Rights Reserved # # NAME: smrtsort.db2 # # DESCRIPTION: This partial script sample demonstrates how to # enable SMARTsort under DB2. # # USAGE: The contents of this script can be added to a user's # db2profile file to enable DB2's use of SMARTsort # during the LOAD operation. # # This script can also be invoked directly after # invoking the db2profile. # # A user may wish to copy this script into their # directory structure and customize it. # #------------------------------------------------------------- #------------------------------------------------------------- # DB2SORT Default= # is set to SMARTSORT to enable the use of SMARTsort during DB2 # LOAD processing. #------------------------------------------------------------- DB2SORT=SMARTSORT export DB2SORT #------------------------------------------------------------- # DB2SORTLOG Default= # is set to a directory name in which SMARTsort is to create a # log file (called smrtsort.log). Each time SMARTsort is invoked # by DB2, SMARTsort will write a message to this log file. See # the SMARTsort Guide and Reference for more information. #---------------------------------------------------------------- #DB2SORTLOG=/tmp #export DB2SORTLOG Figure 3. Shell Script Coding for DB2SORT and DB2SORTLOG 4.6.1.1 Internal Information There are two additional DB2 profile entries needed to activate the use of DB2SORTLOG, they are: DB2SORTDEBUG=ON DB2SORTDATA=ON If either of these entries are OFF, the DB2SORTLOG will not be used. 4.6.2 Disabling SMARTsort under DB2 To revert to the default DB2 sort when executing a DB2 LOAD, simply reset the DB2SORT environment variable (see Figure 4). Again, it may be necessary to issue a 'db2stop' and a 'db2start' to stop and restart the database manager. DB2SORT= export DB2SORT Figure 4. Resetting the DB2SORT environmental variable 4.6.3 Programs The SMARTsort Release 2 DB2-SMARTsort Accelerator is currently only supported on AIX as follows: 1. The minimum release level for AIX is 4.1.3 2. If the DB2-SMARTsort Accelerator is to be used, then DB2 2.1.1 PTF U442530 is required. 3. The required License Use Management run-time programs, and their pre-reqs, are shipped as part of the AIX operating system. Check your AIX operating system to determine if the IBM SystemView License Use Management product, or the iFOR/LS product is installed on your system. 4.6.4 DB2 The DB2-SMARTsort Accelerator consists of SMARTsort Release 2 and PTF U442530 for DB2 V2.1.1, which supports the new DB2SORT and DB2SORTLOG parameters. 4.7 DARI and UDF Considerations for National Language-Specific Processing By default, DB2 DARI stored procedures and user defined functions, when invoked, execute under a default national language environment which may not match the database's. Consequently, country or code page specific operations, such as the use of C wchar_t graphic host variables and functions, may not work as expected. Application developers need to ensure that, if applicable, the correct environment is initialized when the stored procedure or UDF is invoked. 4.8 Package Name Considerations in Mixed Code Page Environments Package names are generated as part of the invocation of the PRECOMPILE PROGRAM command or API. By default, they are generated based on the first 8 bytes of the application program source file (minus the extension and folded to upper case). Optionally, a name can be explicitly defined. Regardless of the origin of the package name attention should be paid in mixed code page environments to ensure that the package name does become modified as result of conversions between the application code page and the database code page. A modification due to character conversion will occur if any of the characters in the package name (or fragments of a character in a multi-byte code page ) are not directly mapped to a valid character in the database code page. In such cases a substitution character replaces the unconverted character. After such a modification, the package name, when converted back to the application code page, may not match the original. An example of a case where this behavior is undesirable would be while using the DB2 Database Director to list and work with packages. Package names displayed may not match expected names. To avoid conversion problems with package names ensure that only characters are used which are valid under both the application and database code pages. 4.9 Japanese and Traditional-Chinese EUC Code Page Environments 4.9.1 Mixed EUC/Double-Byte Client and Database Considerations The administration of database objects in mixed EUC and double-byte code page environments is complicated by the possible expansion or contraction in the length of object names as a result of conversions between the client and database code page. In particular, many administrative commands and utilities have documented limits to the lengths of character strings which they may take as input or output parameters. These limits are typically enforced at the client, unless documented otherwise. For example, the limit for a table name is 18 bytes. It is possible that a character string which is 18 bytes under a double-byte code page is larger, say 21 bytes, under an EUC code page. This hypothetical 21-byte table name would be considered invalid by such commands as REORGANIZE TABLE if used as an input parameter despite being valid in the target double-byte database. Similarly, the maximum permitted length of output parameters may be exceeded, after conversion, from the database code page to the application code page. This may cause either a conversion error or output data truncation to occur. If the extensive use of administrative commands and utilities is expected in a mixed EUC and double-byte environment it is recommended that database objects and associated data are defined with the possibility of length expansion past supported limits, due to conversion, kept in mind. Administration of an EUC database from a double-byte client will face less restrictions then administration of a double-byte database from an EUC client. The reason being that double-byte character strings will always be equal in length or shorter then the corresponding EUC character string. This will generally lead to less problems caused by character string length limits being enforced. Note: In the case of SQL statements, validation of input parameters is not done until the entire statement has been converted to the database code page. This permits character strings to be used which may be technically longer then allowed when represented in the client code page but which meet length requirements when represented in the database code page. For more information concerning mixed code page environments refer to the "Application Programming Guide" and the "SQL Reference". 4.9.2 Character Conversion Considerations for Traditional-Chinese Users A database or application running under the Traditional-Chinese EUC code page will actually store and manipulate graphic data using the ISO 10646 UCS-2 code set, a double-byte encoding scheme which is a proper subset of the full ISO 10646 Unicode standard. As part of the standards definition for Traditional-Chinese, there is a side effect which will be encountered when converting some characters between double-byte or EUC code pages and UCS-2. There are 189 characters (187 radicals, 2 numbers) which share the same UCS-2 code point, when converted, as another character in the code set. When these characters are converted back to double-byte or EUC they will be converted to the code point of the same character's ideograph, with which it shared the same UCS-2 code point, rather then back to the original code point. The character, when displayed, appears the same but is encoded differently. Depending on the design of your application, this behavior may need to be taken into account. The tables below list the EUC (code page 964) and double-byte (code page 950) code points which, when converted to UCS-2 and then back to the original code set, will experience this behavior. Each entry in the table is composed of three columns, for example the first entry in the table for code page 964 is: (1) (2) (3) 964 -> UCS-2 -> 964 ---- ------ -------- a7a1 4e00 c4a1 Each column contains the hexadecimal encoding of a code point belonging to the code set/code page indicated by the column title: o Column (1) contains the original character's code point, read as x'a7a1', under code page 964 (Traditional-Chinese EUC). o Column (2) contains the code point, read as x'4e00', of the character in column (1) as represented under the UCS-2 code set. The arrow, '->', indicates the flow of conversion. x'a7a1', when converted to UCS-2, is x'4e00'. o Column (3) contains the code point, read as x'c4a1', of the character in column (2) as represented under code page 964. The arrow, '->', indicates the flow of conversion. x'4e00', when converted to Traditional-Chinese EUC, is x'c4a1'. Note: For more detailed information concerning the usage of UCS-2 and EUC refer to the "DB2 for AIX Installation and Operation Guide" and the "Application Programming Guide". 4.9.2.1 Code Page 964 (Traditional-Chinese EUC) 964 -> UCS-2 -> 964 964 -> UCS-2 -> 964 964 -> UCS-2 -> 964 ---- ----- -------- ---- ----- -------- ---- ----- -------- a4be 5341 c4b2 a7f4 6c34 c5d5 a8d7 8c37 cbc7 a4c0 5345 c5ab a7f5 706b c5d6 a8d8 8c46 cbc8 a7a1 4e00 c4a1 a7f6 722a c5d7 a8d9 8c55 cbc9 a7a5 4e59 c4a2 a7f7 7236 c5d8 a8da 8c78 8ea2a5a4 a7a7 4e8c c4a8 a7f8 723b c5d9 a8db 8c9d cbca a7a9 4eba c4a9 a7f9 723f 8ea2a1c2 a8dc 8d64 cbcb a7aa 513f c4aa a7fa 7247 c5da a8dd 8d70 cbcc a7ab 5165 c4ab a7fb 7259 c5db a8de 8db3 cbcd a7ac 516b c4ac a7fc 725b c5dc a8df 8eab cbce a7b0 51e0 c4ad a7fd 72ac c5dd a8e0 8eca cbcf a7b1 51f5 8ea2a1a3 a7fe 7384 c6e8 a8e1 8f9b cbd0 a7b2 5200 c4ae a8a1 7389 c6e9 a8e2 8fb0 cbd1 a7b3 529b c4b0 a8a2 74dc c6ea a8e4 9091 cbd7 a7b5 5315 c4b1 a8a3 74e6 c6eb a8e5 9149 cbdc a7b6 531a 8ea2a1a4 a8a4 7518 c6ec a8e6 91c6 cbdd a7b8 5341 c4b2 a8a5 751f c6ed a8e7 91cc cbde a7b9 535c c4b3 a8a6 7528 c6ee a8e8 91d1 cfda a7bb 5382 8ea2a1a5 a8a7 7530 c6f0 a8e9 9577 cfdb a7bd 53c8 c4b4 a8a8 758b c6f4 a8ea 9580 cfdc a7be 53e3 c4c7 a8ab 767d c6f5 a8eb 961c cfdd a7bf 56d7 8ea2a1aa a8ac 76ae c6f6 a8ed 96b9 cfe3 a7c0 571f c4c8 a8ad 76bf c6f7 a8ee 96e8 cfe4 a7c1 58eb c4c9 a8ae 76ee c6f8 a8ef 9752 cfe5 a7c3 5915 c4ca a8af 77db c6f9 a8f0 975e cfe6 a7c4 5927 c4cb a8b0 77e2 c6fa a8f1 9762 d3f2 a7c5 5973 c4cc a8b1 77f3 c6fb a8f2 9769 d3f3 a7c6 5b50 c4cd a8b2 793a c6fc a8f3 97cb d3f4 a7c8 5bf8 c4d0 a8b3 79b8 8ea2a1e7 a8f4 97ed d3f5 a7c9 5c0f c4d1 a8b4 79be c6fd a8f5 97f3 d3f6 a7ca 5c22 c4d2 a8b5 7a74 c6fe a8f6 9801 d3f7 a7cb 5c38 c4d3 a8b6 7acb c7a1 a8f7 98a8 d3f8 a7cc 5c6e 8ea2a1ab a8b7 7af9 c8cc a8f8 98db d3f9 a7cd 5c71 c4d4 a8b8 7c73 c8cd a8f9 98df d3fa a7cf 5de5 c4d6 a8b9 7cf8 c8ce a8fa 9996 d3fb a7d0 5df1 c4d7 a8ba 7f36 c8cf a8fb 9999 d3fc a7d1 5dfe c4da a8bb 7f51 8ea2a2e9 a8fc 99ac d8eb a7d2 5e72 c4db a8bc 7f8a c8d0 a8fd 9aa8 d8ec a7d6 5efe c4dc a8bd 7fbd c8d1 a8fe 9ad8 d8ed a7d7 5f0b c4dd a8be 8001 c8d2 a9a1 9adf 8ea2b3cc a7d8 5f13 c4de a8bf 800c c8d4 a9a2 9b25 d8ee a7db 5f73 8ea2a1ac a8c0 8012 c8d5 a9a3 9b2f 8ea2b3cd a7dc 5fc3 c5c0 a8c1 8033 c8d6 a9a4 9b32 d8ef a7dd 6208 c5c1 a8c2 807f c8d7 a9a5 9b3c d8f0 a7de 6236 c5c2 a8c3 8089 c8d8 a9a6 9b5a dea3 a7df 624b c5c3 a8c4 81e3 c8db a9a7 9ce5 dea4 a7e0 652f c5c5 a8c5 81ea c8dc a9a8 9e75 dea5 a7e2 6587 c5c6 a8c6 81f3 c8dd a9a9 9e7f dea6 a7e3 6597 c5c7 a8c7 81fc c8de a9aa 9ea5 dea7 a7e4 65a4 c5c8 a8c8 820c c8df a9ab 9ebb dea8 a7e5 65b9 c5c9 a8c9 821b c8e0 a9ac 9ec3 e3a7 a7e7 65e5 c5ca a8ca 821f c8e1 a9ad 9ecd e3a8 a7e8 66f0 c5cb a8cb 826e c8e2 a9ae 9ed1 e3a9 a7e9 6708 c5cc a8cc 8272 c8e3 a9af 9ef9 8ea2c1d5 a7ea 6728 c5cd a8cd 8278 8ea2a2ea a9b0 9efd 8ea2c8f5 a7eb 6b20 c5ce a8ce 864d 8ea2a2ef a9b1 9f0e e7ef a7ec 6b62 c5cf a8cf 866b c8e5 a9b2 9f13 e7f0 a7ed 6b79 c5d0 a8d0 8840 c8e6 a9b3 9f20 e7f1 a7ee 6bb3 8ea2a1bf a8d1 884c c8e7 a9b4 9f3b ebfc a7ef 6bcb c5d1 a8d2 8863 c8e8 a9b5 9f4a ebfd a7f0 6bd4 c5d2 a8d3 897e 8ea2a2f0 a9b6 9f52 f0af a7f1 6bdb c5d3 a8d4 898b cbc4 a9b7 9f8d f3be a7f2 6c0f c5d4 a8d5 89d2 cbc5 a9b8 9f9c f3bf a7f3 6c14 8ea2a1c1 a8d6 8a00 cbc6 a9b9 9fa0 8ea2e1c2 4.9.2.2 Code Page 950 (Traditional-Chinese Big-5) 950 -> UCS-2 -> 950 950 -> UCS-2 -> 950 950 -> UCS-2 -> 950 ---- ----- ---- ---- ----- ---- ---- ----- ---- a2cc 5341 a451 f59f 6c34 a4f4 f79e 8c37 a8a6 a2ce 5345 a4ca f5a0 706b a4f5 f79f 8c46 a8a7 f481 4e00 a440 f681 722a a4f6 f7a0 8c55 a8a8 f483 4e59 a441 f682 7236 a4f7 f881 8c78 cba4 f484 4e8c a447 f683 723b a4f8 f882 8c9d a8a9 f485 4eba a448 f684 723f c962 f883 8d64 a8aa f486 513f a449 f685 7247 a4f9 f884 8d70 a8ab f487 5165 a44a f686 7259 a4fa f885 8db3 a8ac f488 516b a44b f687 725b a4fb f886 8eab a8ad f489 51e0 a44c f688 72ac a4fc f887 8eca a8ae f48a 51f5 c942 f689 7384 a5c8 f888 8f9b a8af f48b 5200 a44d f68a 7389 a5c9 f889 8fb0 a8b0 f48c 529b a44f f68b 74dc a5ca f88a 9091 a8b6 f48d 5315 a450 f68c 74e6 a5cb f88b 9149 a8bb f48e 531a c943 f68d 7518 a5cc f88c 91c6 a8bc f48f 5341 a451 f68e 751f a5cd f88d 91cc a8bd f490 535c a452 f68f 7528 a5ce f88e 91d1 aaf7 f491 5382 c944 f690 7530 a5d0 f88f 9577 aaf8 f492 53c8 a453 f691 758b a5d4 f890 9580 aaf9 f493 53e3 a466 f692 767d a5d5 f891 961c aafa f494 56d7 c949 f693 76ae a5d6 f892 96b9 ab41 f495 571f a467 f694 76bf a5d7 f893 96e8 ab42 f496 58eb a468 f695 76ee a5d8 f894 9752 ab43 f497 5915 a469 f696 77db a5d9 f895 975e ab44 f498 5927 a46a f697 77e2 a5da f896 9762 adb1 f499 5973 a46b f698 77f3 a5db f897 9769 adb2 f49a 5b50 a46c f699 793a a5dc f898 97cb adb3 f49b 5bf8 a46f f69a 79b8 c9a8 f899 97ed adb4 f49c 5c0f a470 f69b 79be a5dd f89a 97f3 adb5 f49d 5c22 a471 f69c 7a74 a5de f89b 9801 adb6 f49e 5c38 a472 f69d 7acb a5df f89c 98a8 adb7 f49f 5c6e c94b f69e 7af9 a6cb f89d 98db adb8 f4a0 5c71 a473 f69f 7c73 a6cc f89e 98df adb9 f581 5de5 a475 f6a0 7cf8 a6cd f89f 9996 adba f582 5df1 a476 f781 7f36 a6ce f8a0 9999 adbb f583 5dfe a479 f782 7f51 ca49 f981 99ac b0a8 f584 5e72 a47a f783 7f8a a6cf f982 9aa8 b0a9 f585 5efe a47b f784 7fbd a6d0 f983 9ad8 b0aa f586 5f0b a47c f785 8001 a6d1 f984 9adf d449 f587 5f13 a47d f786 800c a6d3 f985 9b25 b0ab f588 5f73 c94c f787 8012 a6d4 f986 9b2f d44a f589 5fc3 a4df f788 8033 a6d5 f987 9b32 b0ac f58a 6208 a4e0 f789 807f a6d6 f988 9b3c b0ad f58b 6236 a4e1 f78a 8089 a6d7 f989 9b5a b3bd f58c 624b a4e2 f78b 81e3 a6da f98a 9ce5 b3be f58d 652f a4e4 f78c 81ea a6db f98b 9e75 b3bf f58e 6587 a4e5 f78d 81f3 a6dc f98c 9e7f b3c0 f58f 6597 a4e6 f78e 81fc a6dd f98d 9ea5 b3c1 f590 65a4 a4e7 f78f 820c a6de f98e 9ebb b3c2 f591 65b9 a4e8 f790 821b a6df f98f 9ec3 b6c0 f592 65e5 a4e9 f791 821f a6e0 f990 9ecd b6c1 f593 66f0 a4ea f792 826e a6e1 f991 9ed1 b6c2 f594 6708 a4eb f793 8272 a6e2 f992 9ef9 dcb0 f595 6728 a4ec f794 8278 ca4a f993 9efd e0ef f596 6b20 a4ed f795 864d ca4f f994 9f0e b9a9 f597 6b62 a4ee f796 866b a6e4 f995 9f13 b9aa f598 6b79 a4ef f797 8840 a6e5 f996 9f20 b9ab f599 6bb3 c95f f798 884c a6e6 f997 9f3b bbf3 f59a 6bcb a4f0 f799 8863 a6e7 f998 9f4a bbf4 f59b 6bd4 a4f1 f79a 897e ca50 f999 9f52 bea6 f59c 6bdb a4f2 f79b 898b a8a3 f99a 9f8d c073 f59d 6c0f a4f3 f79c 89d2 a8a4 f99b 9f9c c074 f59e 6c14 c961 f79d 8a00 a8a5 f99c 9fa0 efb6 4.9.3 DDCS-Specific Prerequisites and Limitations In order to support connectivity between a DDCS for AIX workstation running under a Japanese or Traditional-Chinese EUC environment and a DB2 for MVS DRDA application server one of the following DB2 for MVS PTFs must be applied: o DB2 for MVS Version 3.1, fix for APAR PN77480 (PTF number not available at time of writing) o DB2 for MVS Version 4.1, fix for APAR PN77480 (PTF number not available at time of writing) Note: Connectivity is not supported between DDCS for AIX workstations running under Japanese or Traditional-Chinese EUC environments and the following DRDA application servers: DB2 for OS/400 DB2 for VM and VSE ============================== 5. Environment Considerations 5.1 Copying a Shared Library On occasion, a problem may occur when attempting to replace either a stored procedure or a user-defined function shared library, with the copy operation failing. This is because AIX keeps a cache of referenced libraries and does not allow the library to be overwritten. One solution is to run /usr/sbin/slibclean to flush the AIX shared library cache. Another solution is to remove the library from the target directory and then copy the library from the source directory to the target directory. 5.2 Receiving Error Message -30081 when Using the TCP/IP Protocol When using the TCP/IP protocol, you may occasionally receive a -30081 error message with the first errno token = ETIMEDOUT on a CONNECT request. This is a result of the server handling too many incoming connection requests, or a very busy network. When an application receives this error, it should pause for a short time and then try the connection again. ========================= 6. Communications Issues 6.1 TCP/IP If an application attempting a CONNECT to the server using the TCP/IP protocol receives a -30081 error message with the first error token containing a value of ETIMEDOUT (10060, 78, 145, 238) or EADDRINUSE (10048, 67, 125, 226), or ECONNREFUSED (10061, 79, 146, 239); these errors indicate that the server is handling too many incoming connection requests or that the network is very busy. The application should pause for a short time then attempt the connection again. Also, in the case where ECONNREFUSED is received, check that TCP/IP support at the database server has been successfully started. If you encounter EADDRINUSE errors during CONNECT, one of the following fixes should be applied for APAR #IX41367: PTF U435180 or PMP 3251 (PTF U493251) ===================== 7. Programming Notes 7.1 Sample Applications The following files contain information about the sample applications which are used in the DB2 publications. The sample applications will only be useful to you if you have the DB2 Software Developer's Kit or DB2 Single-User package for your platform installed. samples/c/README README file for the c sample applications. samples/cobol/README README file for the COBOL sample applications. samples/fortran/README README file for the FORTRAN sample applications. samples/rexx/README README file for the REXX sample applications. samples/cli/README README file for the Call Level Interface sample applications. samples/cli/INTCLI.DOC Documentation for the Interactive Call Level Interface applet. samples/clp/README README file for the Command Line Processor examples. 7.2 Considerations when Using Micro Focus COBOL DB2 v2.1.1 has been modified to correctly handle abnormal termination of Micro Focus COBOL applications compiled with the DB2 PREP command. This ensures that partial transactions are rolled back in the event of an error. It is strongly recommended that any existing DB2 applications precompiled with DB2 PREP and compiled with Micro Focus COBOL be re-precompiled with the updated DB2 v2.1.1 precompiler, and then re-compiled with Micro Focus COBOL. If applications built with Micro Focus COBOL and the IBM precompiler from a previous release/ version are not rebuilt, there is a danger of database corruption if abnormal termination occurs. This support requires Micro Focus COBOL v3.2. Once the necessary version of the Micro Focus compiler is available, add the db2gmf library (-ldb2gmf) to your compile/link command as follows (AIX): cob -x -o app app.cbl -ldb2 -ldb2gmf -L/usr/lpp/db2_02_01/lib For stored procedures, the same new argument (-ldb2gmf) is added to the compile/link command (AIX): cob -x -o app app.cbl -Q -H512 -Q -T512 -Q -e app \ -Q -bE:app.exp -ldb2 -ldb2gmf -L/usr/lpp/db2_02_01/lib Compile/line commands for other UNIX platforms are modified similarly, by adding -ldb2gmf. 7.2.1 Updating the Micro Focus COBOL Runtime System DB2 v2.1.1 for UNIX platforms provides an updated sqllib/lib/db2mkrts.args file. This requires that the Micro Focus COBOL runtime system, rts32, be rebuilt. Follow the instructions for building the Micro Focus COBOL runtime system under 'Developing Applications with Micro Focus COBOL' in the DB2 Installation Guide. 7.2.2 Configuring CICS for Use With DB2 2.1.1 and Micro Focus COBOL on UNIX Platforms DB2 v2.1.1 requires changes in the way CICS is configured for use with Micro Focus COBOL and DB2. The object file sqlgmf.o contained in the /usr/lpp/db2_02_01/lib/libdb2gmf.a archive needs to be extracted, and used in the construction of the CICS runtime system. Referring to the CICS Administration Guide, perform the steps as documented under 'C Transaction Support for DATABASE 2', if you have not already done so. Then execute the following commands: cd /usr/lpp/db2_02_01/lib ar -vx libdb2gmf.a ln -s /usr/lpp/db2_02_01/lib/sqlgmf.o /usr/lib/db2gmf.o Then, perform the steps under 'COBOL Transaction Support for DATABASE 2' in the CICS Administration Guide, with the following change. Where you are instructed to run cicsmkcobol to regenerate cicspsCOBOL, use the following command instead of the one documented in the CICS Administration Guide: cicsmkcobol /usr/lib/db2.o /usr/lib/db2gmf.o This adds the sqlgmf API to the cicsprCOBOL runtime, allowing applications precompiled with the DB2 v2.1.1 COBOL precompiler to function correctly with CICS. 7.3 BINARY/COMP-4 COBOL Data Types in the DB2 Precompiler The DB2 Precompiler supports use of BINARY, COMP-4 and COMP COBOL data types wherever integer host variables and indicators are permitted, as long as the target COBOL compiler views (or can be made to view) BINARY/COMP-4/COMP as equivalent to COMP-5. In the DB2 Application Programming Guide, such host variables and indicators are shown with type COMP-5. Target compilers supported by DB2 which treat BINARY/COMP-4/COMP and COMP-5 as equivalent are: IBM COBOL Set for AIX Micro Focus COBOL for AIX IBM COBOL Visual Set for OS/2 (with the -qbinary(native) option set) 7.4 Using REDEFINES in COBOL Group Data Items The REDEFINES clause may be used when declaring host variables with the DB2 COBOL precompiler. If a member of a group data item is declared with the REDEFINES clause, and that group data item is referred to as a whole in an SQL statement, any subordinate items containing the REDEFINES clause will not be expanded. For example: 01 foo. 10 a pic s9(4) comp-5. 10 a1 redefines a pic x(2). 10 b pic x(10). Referring to foo in an SQL statement like so: ... INTO :foo ... is equivalent to ... INTO :foo.a, :foo.b ... That is, the subordinate item a1 with the REDEFINES is not automatically expanded out in such situations. A subordinate with a REDEFINES may be referred to in an SQL statement explicitly, as in ... INTO :foo.a1 ... or ... INTO :a1 ... if a1 is unambiguous. 7.5 Running DB2 for AIX COBOL Stored Procedures Because of the way AIX loads stored procedures and resolves library references within them, there are generally requirements on how language products like COBOL should be installed. When you are running a normal executable, COBOL for example, these requirements are not a factor. They are, however, when it is a shared object (stored procedure) being loaded on the fly. When a stored procedure is loaded, the chain of libraries it refers to must also be loaded. When AIX searches for a library only indirectly referenced by your program, it must use the path compiled into the library that referenced it when it was built by the language provider (in this case, Micro Focus** COBOL or the IBM COBOL group.) This path may very well not be the same path in which the compiler was installed. (If a library in the chain cannot be found, the stored procedure load will fail, and you receive a -10013 sqlcode.) To solve this problem, you can install the compiler wherever you want, but you should then create symbolic links of all language libraries from the install directory into /usr/lib (a directory which is almost always searched when a library needs to be loaded.) You could link the libraries into sqllib/function (the stored procedure directory), but this only works for one database instance. /usr/lib works for everyone on the machine. Also, copying the libraries in is not advised, especially for Micro Focus COBOL, since there may be problems when multiple copies of the libraries exist. A sample symbolic link of Micro Focus COBOL is provided below (assuming it is installed in /usr/lpp/cobdir): [1] su root [2] cd /usr/lib [3] ln -sf /usr/lpp/cobdir/coblib/*.a . 7.6 Building Micro Focus COBOL Stored Procedures on AIX In order to build stored procedures with Micro Focus COBOL v3.2 for AIX, "-B static" and "-Q -bI:" options must be added to the command line, and "-ldb2" must be removed, relative to what is shown in the Software Developer's Kit for AIX, Building Your Applications manual. Below are shown the modified commands to build the server portion of the inpcli/inpsrv sample client/server pair: db2 connect to sample db2 prep inpsrv.sqb cob -xc inpsrv.cbl cob -x -o inpsrv inpsrv.o -Q -e inpsrv -Q -bE:inpsrv.exp \ -Q -bI:$HOME/sqllib/lib/db2g.imp -B static \ -L$HOME/sqllib/lib 7.7 Precompiler Customization If you are writing your own precompiler interface to DB2 Precompiler Services, you should be aware that the program_id parameter to sqlainit() must indicate static storage. This storage is referred to by the Precompiler Services functions sqlaalhv() and sqlacmpl() during precompilation. The program_id storage must remain accessible and undisturbed until Precompiler Services has been terminated with a call to sqlafini(). 7.8 Considerations When Using REXX Stored Procedures When using host variables in the CALL statement, each host variable must be initialized to a value compatible in type with the data that is returned into it from the server procedure (if any). It should be done even if the corresponding indicator is negative. When using descriptor, SQLDATA must be initialized and contain data compatible in type with the data that is returned from the server procedure (if any). It should be done even if the SQLIND field contains negative value. (See also OUTCLI.CMD example in the Application Programming Guide for common servers). =================== 8. AIX ODBC Driver An AIX ODBC driver is now shipped as part of the DB2/6000 V2.1.1 CAE component. This driver will inter-operate with Visigenic Software's ODBC 2.0 for AIX Driver Manager, Version 1.1. For more information, see the Installing and Using AIX Clients book. 8.1 Common Application Problems and Work Arounds The Patch1 keyword (specified in the sqllib\cfg\db2cli.ini file) is used to specify work arounds for specific application problems. There are currently no work arounds that apply to AIX ODBC applications. NOTE: Most ODBC applications do not support LOB data types and require the LONGDATACOMPAT keyword to be set to 1. 8.2 SQLDescribeParam() With this release of DB2 v2.1.1, the DB2 CLI/ODBC function call SQLDescribeParam() is now supported. This function returns the description of a parameter marker associated with a prepared SQL statement. You will receive an SQLSTATE of S1C00 - 'Driver not capable' if you attempt to run this command against any DB2 common server before this release of 2.1.1, or against a DRDA data source. One output of the SQLDescribeParam() function indicates whether the parameter allows NULL values. Currently the DB2 CLI/ODBC driver always returns SQL_NULLABLE_UNKNOWN. SQLGetFunctions() will not correctly indicate whether SQLDescribeParam() is available. Until you run either SQLPrepare() or SQLExecDirect(), SQLGetFunctions() will always return TRUE (always indicating that SQLDescribeParam() is available). Once you run either of these two functions, SQLGetFunctions() will correctly indicate whether SQLDescribeParam() is supported, based on the version of your DB2 server. See the new db2cli.ini keyword 'DescribeParam' for details on enabling or disabling this new CLI/ODBC function when connecting to DB2 v2.1.1 servers. 8.3 New CLI/ODBC Configuration Keywords in db2cli.ini The following ODBC configuration keywords were not described in the book: CURRENTFUNCTIONPATH = CURRENT_FUNCTION_PATH This keyword defines the path used to resolve function references and data type references that are used in dynamic SQL statements. It contains a list of one or more schema-names, where schema-names are enclosed in double quotes and separated by commas. The default value is "SYSIBM","SYSFUN",X where X is the value of the USER special register delimited by double quotes. The schema SYSIBM does not need to be specified. If it is not included in the function path then it is implicitly assumed as the first schema. This keyword is used as part of the process for resolving unqualified function references that may have been defined in a schema name other than the current users' schema. The order of the schema names determines the order in which the function names will be resolved. For more information on function resolution, refer to the SQL Reference guide. MULTICONNECT = 0 | 1 This keyword is used to specify how "SQLConnect()" requests are mapped to physical database connections. 0 = false Each "SQLConnect()" request by the application will result in a physical database connection. 1 = true All connections for the application are mapped to one physical connection. This may be used if the ODBC application runs out of file handles because it uses so many connections. NOTE: If MULTICONNECT is set to true then all statements are executed on the same connection and therefore in the same transaction. This means that a rollback will rollback ALL statements on all connections. Be sure that the application is designed to work with MULTICONNECT set to true before doing so or the application may not operate correctly. OPTIMIZEFORNROWS = INTEGER This keyword will append the "OPTIMIZE FOR n ROWS" clause to every select statement, where n is an integer larger than 0. The default action is not to append this clause. For more information on the effect of the OPTIMIZE FOR n ROWS clause, refer to the SQL Reference guide. IGNOREWARNINGS = 0 | 1 0 = Warnings reported as usual (default). 1 = All warnings are ignored, SQL_SUCCESS is returned. Use this keyword if an application does not handle warnings properly. 8.3.1 CLI/ODBC Trace File The DB2 CLI/ODBC driver can now write the details of all function calls to a trace file which can be sent to IBM support to help resolve problems. A programmer familiar with the workings of CLI/ODBC could also look at this file to gain a better understanding of program execution. Three additional keywords in the db2cli.ini have been added to facilitate this function. They are located in the [COMMON] section in the initialization file: o Trace = 1 (on) or 0 (off) - defaults to 0 o TraceFileName = If the file does not exist then it will be created, otherwise the new trace information will be appended to the end of the file. No trace will occur, and no error message will be returned, if the filename given is invalid or if the file cannot be created or written to. o TraceFlush = 1 (on) or 0 (off) - defaults to 0 Set this keyword to 1 to force a write to disk after each Trace entry. This will slow down the trace process, but will ensure that each entry is written to disk before the program continues to the next statement. For example, the following settings in the db2cli.ini file will write all details of DB2 CLI/ODBC function calls to a file called C:\TRACE\MONDAY.TRC after each is executed. [COMMON] ... Trace=1 TraceFileName=C:\TRACE\MONDAY.TRC TraceFlush=1 8.3.2 Specifying a Directory for Temporary Files When working with Large Objects (CLOBS, BLOBS, etc...), a temporary file is often created on the client machine to store the information. It is possible that in some cases the system temporary directory setting will not be used. The db2cli.ini keyword 'TempDir' has been created to ensure that the DB2 CLI/ODBC driver manager selects an appropriate path. The keyword is placed in the datasource specific section of the db2cli.ini file, and has the following syntax: o TempDir= When a Large Object is accessed, an SQLSTATE of S1507 will be returned if the path name is invalid, or if the temporary files cannot be created in the directory specified. 8.3.3 SQLDescribeParam() Enable/Disable The keyword 'DescribeParam' has been added to allow you to enable or disable the new SQLDescribeParam() function. The keyword is placed in the datasource specific section of the db2cli.ini file, and has the following syntax: o DescribeParam = 0 or 1 (defaults to 0) 0 = Disable SQLDescribeParam() - default 1 = Enable SQLDescribeParam() This keyword has the following effect based on the version of the DB2 server you are connecting to: o DB2 server before 2.1.1 Keyword has no effect - SQLGetFunctions() will indicate that SQLDescribeParam() is not supported - if you call SQLDescribeParam() it will return SQLSTATE S1C00 - 'Driver not capable' o DB2 server version 2.1.1, up to but not including this National Language version. Set DescribeParam = 0 to avoid errors if your application does call SQLDescribeParam(). This will: - disable the SQLDescribeParam() function (if you call SQLDescribeParam() it will return SQLSTATE S1C00 - 'Driver not capable') - have SQLGetFunctions() indicate that SQLDescribeParam() is not available o This current National Language version of the DB2 server version 2.1.1 Setting DescribeParam to 1 will: - enable the SQLDescribeParam() function - have SQLGetFunctions() indicate that SQLDescribeParam() is available Setting DescribeParam to 0 will: - disable the SQLDescribeParam() function (if you call SQLDescribeParam() it will return SQLSTATE S1C00 - 'Driver not capable') - have SQLGetFunctions() indicate that SQLDescribeParam() is not available 8.3.4 Asynchronous ODBC Enable/Disable The keyword 'AsyncEnable' has been added to allow you to enable or disable the new asynchronous features. The keyword is placed in the datasource specific section of the db2cli.ini file, and has the following syntax: AsyncEnable = 0 or 1 o 0 = Disable the new asynchronous features. The DB2 CLI/ODBC driver will act as it did with previous versions that did not support asynchronous o 1 = Enable the new asynchronous features as described above. ==================================== 9. Changes to the DB2 Documentation 9.1 For the "Changes for This Release" Section The following changes apply to the "Changes for This Release" sections of several DB2 books. o In the DB2 CLI and ODBC Enhancements section, under 'New ODBC Scalar Functions', the new function which calculates the number of seconds since midnight is incorrectly named as MIDNIGHT_SECONDS. This is the correct name for the SYSFUN function, but in ODBC this name is mapped to SECONDS_SINCE_MIDNIGHT instead. o In the section entitled "DB2 CLI and ODBC Enhancements", under "New ODBC Scalar Functions", JULIAN_DAY(day_expr) should be JULIAN_DAY(date_expr). o In the section entitled "DRDA Two-Phase Commit", the following sentence should be clarified as follows, "See the DDCS Installation and Configuration Guide for your platform for instructions on how to install the SPM component and considerations for migrating from Encina Monitor PPC gateway." o In the section entitled "Graphical Tools", the last sentence in the section is augmented to read, For more information on the keywords for the DB2 CLI initialization file (db2cli.ini), see the "Call Level Interface Guide and Reference" or the appendix on "Configuring the Client Workstation" in the "Installing and Using DB2 Clients for Windows" manual. Also, the DB2 Performance Monitor is not available from a Windows client. 9.2 Administration Guide In chapter 5, "Utilities for Moving Data", there is a section "Using the EXPORT Utility" which requires a change to the first note. In the note, the extensions are listed as being from 000 to 999. This is not correct: the first file extension begins with 001. In chapter 6, Recovering a Database, there is a section "Considerations for Managing Log Files" which requires two changes: o The second bullet should have the following change made: The sentence in the middle of the bullet beginning "At this time..." should now read "At this time, if there are no more logs available and you are rolling forward the entire database, you can use the ROLLFORWARD command to stop processing." o The sixth bullet should have the following changes made: The sentence at the beginning of the third paragraph beginning "If you terminate..." should now read "If you are rolling forward the entire database and you terminate the roll-forward recovery (by specifying the STOP option on the ROLLFORWARD command) and the log containing the completion of a transaction has not been applied to the database, then the incomplete transaction will be rolled back to ensure that the database is left in a consistent state." In chapter 6, "Recovering a Database", it is explained that tablespaces can be restored online. However, restoring the tablespace containing the system catalogs requires an exclusive connection, so it must be restored offline. 9.3 API Reference 9.3.1 Backup and Restore API's for Vendor Products Appendix DB2-INFO Data Structure The following ADSM information has been added to the DB2-INFO data structure. Field Name Data Type Description nodename char The current value of ADSM_NODENAME from the database manager configuration parameters. password char The current value of ADSM_PASSWORD from the database manager configuration parameters. owner char The current value of ADSM_OWNER from the database manager configuration parameters. Nodename, password, and owner can be utilized to enable the restore of a backup taken on one node, to a different node (for example, during disaster recovery). By setting these values in the database manager configuration, they will be passed to the vendor device support routines on the sqluvint call. Then they can be used in the retrieval of the correct backup. Owner can be used to specify the name (id) of the individual who performed the backup. These values will only be passed in the DB2_INFO structure on a sqluvint call for a restore. They will be set to null on a backup. Added to C structure char *nodename /* name of node at which the backup */ /* was generated */ char *password /* password for the node at which the backup */ /* was generated */ char *owner /* backup originator id */ 9.3.2 QUIESCE TABLESPACES FOR TABLE The following is a correction to the Usage notes: When a quiesce request is received, the transaction requests a quiesce mode of SHARE, UPDATE, or EXCLUSIVE for the tablespaces of the table. The quiesce mode is granted only if there are no conflicting quiesce modes held by other applications. A quiescer can upgrade their quiesce mode of a tablespace from a less restrictive mode to a more restrictive one (for example, from S to U or X, or U to X). Quiesce modes can not be downgraded. If a quiescer requests a mode lower than one that is already held, the original quiesce mode is maintained. The quiesce mode, authorization ID, and the database agent ID of the quiescer is recorded in the tablespace table so that the quiesce is persistent. When the transaction commits or rolls back, the tablespaces for the table remain quiesced until the quiesce is explicitly reset. No tables can be changed while any tablespace for the table is quiesced in SHARE or UPDATE mode. An application that has the tablespaces for a table quiesced in EXCLUSIVE mode has full (exclusive) access to the table and the tablespaces. There is a limit of five quiescers on a tablespace at any given time. Since EXCLUSIVE is incompatible with any other mode, and UPDATE is incompatible with another UPDATE, the five quiescer limit, if reached, would consist of at least four SHARE modes and a maximum of one UPDATE mode. The tablename parameter in the QUIESCE TABLESPACES FOR TABLE command should be described as follows: Specifies the unqualified table name. The table cannot be a system catalog table. schema.tablename - Specifies the qualified table name. If schema is not provided, the authorization ID used for the database connection will be used as the schema. The table cannot be a system catalog table. 9.4 Command Reference 9.4.1 FORCE APPLICATION The following corrects the parameter description for the FORCE APPLICATION command: The ALL parameter disconnects all users connected to any database defined in the instance. 9.4.2 ROLLFORWARD DATABASE The ROLLFORWARD DATABASE command AND STOP, and the STOP parameters are not required for table space roll-forward. 9.4.3 EXPORT The following corrects the parameter description for the EXPORT command: The MODIFIED BY parameter accepts LOBSINFILE for IXF filetypes. 9.4.4 QUIESCE TABLESPACES FOR TABLE The following is a correction to the Usage notes: When a quiesce request is received, the transaction requests a quiesce mode of SHARE, UPDATE, or EXCLUSIVE for the tablespaces of the table. The quiesce mode is granted only if there are no conflicting quiesce modes held by other applications. A quiescer can upgrade their quiesce mode of a tablespace from a less restrictive mode to a more restrictive one (for example, from S to U or X, or U to X). Quiesce modes can not be downgraded. If a quiescer requests a mode lower than one that is already held, the original quiesce mode is maintained. The quiesce mode, authorization ID, and the database agent ID of the quiescer is recorded in the tablespace table so that the quiesce is persistent. When the transaction commits or rolls back, the tablespaces for the table remain quiesced until the quiesce is explicitly reset. No tables can be changed while any tablespace for the table is quiesced in SHARE or UPDATE mode. An application that has the tablespaces for a table quiesced in EXCLUSIVE mode has full (exclusive) access to the table and the tablespaces. There is a limit of five quiescers on a tablespace at any given time. Since EXCLUSIVE is incompatible with any other mode, and UPDATE is incompatible with another UPDATE, the five quiescer limit, if reached, would consist of at least four SHARE modes and a maximum of one UPDATE mode. The tablename parameter in the QUIESCE TABLESPACES FOR TABLE command should be described as follows: Specifies the unqualified table name. The table cannot be a system catalog table. schema.tablename - Specifies the qualified table name. If schema is not provided, the authorization ID used for the database connection will be used as the schema. The table cannot be a system catalog table. 9.4.5 RUNSTATS Enhancement The new RUNSTATS enhancement allows the user to specify a specific index for which the statistics should be collected as an alternative to INDEXES ALL. The following information will be added to the Command Reference manual: Wherever ---INDEXES ALL--- appears now in the syntax diagram it should be replaced with: --------INDEXES ALL----------- | | -----INDEX index-name--- The following explains the new parameter: AND INDEX INDEX-NAME Update statistics on both the table and its specified index "index-name", where index-name is a fully qualified name in the form: schema.index-name. FOR INDEX INDEX-NAME Update statistics for index index-name only. If statistics on the table have never been generated, the database manager calculates statistics on the table as well as on the index. The index-name is a fully qualified name in the form: schema.index-name. This is an example of the new parameter: Collect distribution statistics on index INDEX1 only: db2 runstats on table smith.table1 with distribution for index smith.index1 9.5 Call Level Interface Guide and Reference 9.5.1 SQLColAttributes - Get Column Attributes In the "Function Arguments" section, the Data Type for the rgbDesc argument is listed as 'SQLCHAR *'. This is incorrect; the correct type for the argument rgbDesc is 'SQLPOINTER'. 9.5.2 New CLI/ODBC Configuration Keyword See section 8.3, "New CLI/ODBC Configuration Keywords in db2cli.ini" for the new CLI/ODBC configuration keywords that are not listed in the CLI Guide and Reference. 9.6 Database System Monitor Guide and Reference 9.6.1 db_conn_time Data Element The "first database connect timestamp" (db_conn_time) element is incorrectly described as being part of the Buffer Pool monitor group. This element is only part of the Buffer Pool monitor group for table space snapshots. For Database and Table snapshots, this data element is part of the Basic monitor group. 9.6.2 Buffer Pool Data Pages Written to Disk In Chapter 5, "Database System Monitor Data Elements", in the sections entitled "Buffer Pool Asynchronous Data Writes" and "Buffer Pool Asynchronous Index Writes" which describe 'pool_async_data_writes' and 'pool_async_index_writes', the following phrase: The number of times a buffer pool data page was physically written to disk, by an asynchronous page cleaner should read as follows in order to indicate that the write could also have been performed by a prefetcher: The number of times a buffer pool data page was physically written to disk, either by an asynchronous page cleaner or by a prefetcher, which may have had to write dirty pages to disk in order to make space for the pages to prefetch 9.6.3 DBMON Element Formula The formula associated with the Buffer Pool Victim Page Cleaners Triggered (pool_drty_pg_steal_clns) data element is incorrect. The formula should be: buffer pool victim page cleaners triggered / ( buffer pool victim page cleaners triggered + buffer pool threshold cleaners triggered + buffer pool log space cleaners triggered) 9.6.4 Buffer Pool Threshold Cleaners Triggered If this value is set too low, pages might be written out too early, requiring them to be read back in. If set too high, then too many pages may accumulate, requiring users to write out pages synchronously. 9.6.5 Event Start Time The following sentence has been added to the description for the Event Start Time: This element, in the sqlm_evmon_start_event API structure indicates the start of the event monitor. 9.6.6 Package Cache Inserts/Package Cache Lookups The formula in the Usage sections for calculating the package cache hit ratio must be updated as follows: 1 - ( Package Cache Inserts / Package Cache Lookups ) If the hit ratio is high (more than 0.8), the cache is performing well. A smaller ratio may indicate that the package cache should be increased. 9.6.7 Status of Database and Status of DB2 Instance In the Usage section for Status of Database Instance, the following is a correction: "SQLM_DB2_ACTIVE is the only valid value for this field." In the Usage section for Status of Database, the following is a correction: "SQLM_DB_ACTIVE is the only valid value for this field." 9.7 Planning Guide 9.7.1 Segments Versus 4KB Pages This section applies to the SQL Reference manual as well. In the chapter entitled "Incompatibilities Between Releases", in the "Configuration Parameters" section, under the heading "Segments Versus 4KB Pages", the following statement exists: All configuration parameters in OS/2 that were expressed in segments in Version 1 are now expressed in 4KB pages. The following text expands on the previous sentence: For an application which makes a call to a configuration API that involves one of the following tokens names, the points listed below apply: SQLF_DBTN_DBHEAP SQLF_DBTN_APPLHEAPSZ SQLF_DBTN_STMTHEAP SQLF_DBTN_SORTHEAP 1. If the application was compiled against Version 1, then if the binary is run against either Version 1 or Version 2, the value specified will be treated as being specified in segments. 2. If the application is re-compiled against Version 2, the values specified will be treated by the Version 2 product as being specified in 4K pages. 3. If, however, the token names were not coded in the application, and instead the actual Version 1 token values were coded (for example, instead of coding with the token name SQLF_DBTN_DBHEAP, the Version 1 token value of 8 is used), then if compiled against either Version 1 or Version 2, these parameters will be treated as being specified in segments. (The token values for these tokens have changed since Version 1). In Version 2, in addition to being specified now in 4K pages, the database configuration parameter sortheap on OS/2 has also changed from being specified as a two-byte integer to being specified as a four-byte integer. If an application written in Version 1 code using the token SQLF_DBTN_SORTHEAP is recompiled against Version 2, it is treated as a two-byte integer as in Version 1 (although the units will change from segments to 4KB pages). However, the full range of values will not be accessible to a two-byte integer. Therefore, it is recommended that the Version 2 token SQLF_DBTN_SORT_HEAP be used, and the application code changed to reflect that the API will treat the token SQLF_DBTN_SORT_HEAP as indicating a four-byte integer, in order to migrate the application code from Version 1 to Version 2, NOTE: In Version 2, all code using the Version 2 token names (see the API Reference) will be treated the same on UNIX and OS/2 platforms with respect to the data type and the units. (The ranges however may differ). 9.7.2 BUFFPAGE and SORTHEAP In the chapter entitled "Incompatibilities Between Releases", in the "Configuration Parameters" section, under the heading "BUFFPAGE and SORTHEAP", the following clarification should be added. Also, please note that the word "byte" should be "bit" in this section in the Planning Guide. Symptom: OS/2 - If an application was compiled against DB2 for OS/2 Version 1 using the old tokens, the binary will run as expected against Version 2 as well. However, since the old tokens indicate that the parameter is to be treated as being specified as a 16 bit unsigned integer, the full Version 2 range of values for BUFFPAGE (up to 524288) will not be available. Thus, if BUFFPAGE is updated (by a Version 2 application or by a Version 2 CLP) to a value such as 100,000, and the Version 1 application is attempting to GET that value, it will not be able to reflect the true value. (Note that with the range in Version 2 for sortheap being 524288 4 KB pages, and with SORTHEAP in DB2 for OS/2 Version 1 being specified in segments, this translates to a new range of up to 32768 segments. This value is in the range of an unsigned 16 bit integer, and hence the DB2 for OS/2 Version 1 binary should not have the same range restrictions for SORTHEAP as it does for BUFFPAGE.) Response: For a Version 1 binary complied under DB2 for OS/2 Version 1, the values used for BUFFPAGE should be restricted to that which can be stored in an unsigned 16 bit integer. For most situations this should be sufficient. When recompiling the application against Version 2 on any platform, use the new tokens for these parameters. 9.8 Configuration Parameters 9.8.1 DIR_CACHE The DDCS User's Guide incorrectly refers to the DIR_CACHE configuration parameter as DIRCACHE. Please refer to the directory cache documentation in the Installing and Using AIX Clients manual, Appendix D, "Configuring the Client Workstation", before attempting to catalog databases. 9.8.2 MAXAPPLS The maximum value of MAXAPPLS is 5000 on UNIX** systems and 1500 on OS/2 systems. The prior value was 1000. The following manuals incorrectly show the prior maximum value of 1000: o DB2 for OS/2 Planning Guide o DB2 for AIX Planning Guide 9.8.3 REC_HIS_RETENTN The Planning Guide (Appendix C) and Installation and Operation Guides (Appendix D) indicate that the maximum value for the database configuration parameter rec_his_retentn is 32767. This is incorrect. The correct value is 30000. 9.8.4 Configuration Parameters (YES/NO and ON/OFF Values) A number of database configuration parameters (including logretain and userexit) are described as having acceptable values of either "Yes"/"No" or "On"/"Off" in the Online Help, and Planning Guide. In some cases, these do not correspond to what the Command Line Processor (CLP) displays. For example, the Planning Guide indicates that the acceptable value for the database configuration parameter autorestart is either Yes or No, whereas CLP displays it as being either ON or OFF. In this context "Yes" is to be considered synonymous with "On", and "No" with "Off". 9.8.5 DFT_QUERYOPT CONFIGURATION TYPE Database PARAMETER TYPE Configurable DEFAULT [RANGE] 5 [ 0-9 ] UNIT OF MEASUREMENT Query optimization class (see below) RELATED PARAMETERS None The query optimization class is used to direct the optimizer to use different degrees of optimization in compiling SQL queries. DFT_QUERYOPT has been added as a database configuration parameter to provide additional flexibility in setting the default query optimization class used when neither the SET CURRENT QUERY OPTIMIZATION statement nor the QUERYOPT bind option are used explicitly. The query optimization classes currently defined are: 0 minimal query optimization 1 roughly comparable to DB2 Version 1 3 moderate query optimization 5 significant query optimization with heuristics to limit the effort expended on selecting an access plan. This is the default. 7 significant query optimization 9 maximal query optimization For additional information and guidance for selecting a suitable query optimization class see the Administration Guide. The API reference describes how a program can retrieve and modify database configuration parameters. Table 39 provides a list describing each parameter. The additional information for the default query optimization class is: PARAMETER NAME dft_queryopt TOKEN SQLF_DBTN_DFT_QUERYOPT TOKEN VALUE 57 DATA TYPE SINT32 The Database Director does not support the update or the display of this parameter. 9.8.6 LOGFILSIZ The maximum value for this database configuration parameter has changed to 65535 on all UNIX platforms (from a value of 16384). 9.9 Building Your Applications 9.9.1 C++ Type Decoration Consideration When writing a stored procedure of a UDF using C++, you may want to consider declaring the procedure of UDF as: extern "C" ...procedure or function declaration... The extern "C" prevents type decoration or mangling of the function name by the C++ compiler. Without this declaration, you have to include all the type decoration for the function name when you call the stored procedure, or issue the CREATE FUNCTION statement. 9.10 Installation and Operation Guides In Chapter 6, "Enabling Communications on a DB2 Server" in the section entitled "Using a Server as a Gateway", the following sentence should be removed, "However the double-hop gateway does not support NetBIOS as an outbound communications protocol." Hopping is now supported for the NetBIOS protocol. The sentence which follows should include NetBIOS as an outbound communications protocol for OS/2. In Appendix F: o Under Datetime Values: - In the table "Formats for String Representations of Dates", change the Site-defined (Local) description of Date Format from "Depends on database country code" to "Depends on application country code". - In the table "Formats for String Representations of Times", change the Site-defined (Local) description of Time Format from "Depends on database country code" to "Depends on application country code". o Under Date and Time Formats change the fourth list item (Output Date Format) preceding the table "Date and Time Formats by Country Code", from "The default output date format is equal to the local date format." to "The default output date format is described in Table nn. Date and Time Formats by Country Code, under column Default Output Date Format" 9.11 SQL Reference 9.11.1 ALTER TABLE Notes Addition The following bullet should be included with the "Notes" found in the section on "ALTER TABLE": o Adding a column with a default value that involves an explicit value or an implicit or explicit datetime special register, will cause all packages with an insert usage on the object table to be invalidated. 9.11.2 SQL Communications Area (SQLCA) Replace the existing text for the "Field values" column for the SQLWARN1 entry in the "Fields of SQLCA" table, with the following: Contains X or W if the value of a string column was truncated when assigned to a host variable. Contains N if the null terminator was truncated. The value of X is returned when all of the following conditions are met: o a mixed code page connection exists where conversion of character string data from the database codepage to the application codepage may involve a change in the length of the data, o the cursor is blocked o an indicator variable was provided by the application. The value returned in the indicator variable will be the length of the resultant character string in the application's code page. In all other cases involving data truncation, versus null terminator truncation, W will be returned. The value returned in the indicator variable in this case will be the length of the resultant character string in the code page of the select list item (either the application's, the database's or none). 9.11.3 GROUP-BY and ORDER-BY Changes 9.11.3.1 Expressions Section The following changes should be applied to the "Expressions" section of the "SQL Reference Changes" section, for CASE Expressions. Replace the second paragraph with the following: If the CASE expression is in a select list, a VALUES clause, an IN predicate, a GROUP BY clause, or an ORDER BY clause, the search-condition in a searched-when-clause cannot be a quantified predicate, IN predicate using a fullselect, or an EXISTS predicate (SQLSTATE: 42625). 9.11.3.2 Queries Section In the select-clause section of the "Queries" section, replace the first bullet in the "If GROUP BY or HAVING is used:" list with: o An expression X (not a column function) used in the select list must have a GROUP BY clause with: - a grouping-expression that matches the portion of the X referencing columns of R (see group-by clause) or - each column of R referenced in X as a separate grouping-expression. GROUP-BY-CLAUSE: Replace the text of the group-by-clause section of the "Queries" section with the following: +--------------------------------------------------------------------+ | | | ,Ŀ | |  GROUP BY  grouping-expression  | | | +--------------------------------------------------------------------+ The GROUP BY clause specifies an intermediate result table that consists of a grouping of the rows of R. R is the result of the previous clause of the subselect. A grouping-expression is an expression used in defining the grouping of R. Each column-name included in grouping-expression must unambiguously identify a column of R (SQLSTATE 42702 or 42703). The length attribute of each grouping-expression must not be more than 254 bytes (SQLSTATE 42907). An grouping-expression cannot include a scalar-fullselect (SQLSTATE 42822) or any function that is variant or has an external action (SQLSTATE 42845). The result of GROUP BY is a set of groups of rows. In each group of more than one row, all values of each grouping-expression are equal; and all rows with the same set of values of the grouping-expressions are in the same group. For grouping, all null values from a grouping-expression are considered equal. A grouping-expression can be used(1) in a search condition in a HAVING clause, in an expression in a SELECT clause or in a sort-key-expression of an ORDER BY clause (see order-by clause for details). In each case, the reference specifies only one value for each group. For example, if the grouping-expression is col1+col2 then an allowed expression in the select list would be col1+col2+3. Associativity rules for expressions would disallow the similar expression, 3+col1+col2, unless parentheses are used to ensure that the corresponding expression is evaluated in the same order. Thus, 3+(col1+col2) would also be allowed in the select list. If the grouping-expression contains varying-length strings with trailing blanks, the values in the group can differ in the number of trailing blanks and may not all have the same length. In that case, a reference to the grouping-expression still specifies only one value for each group, but the value for a group is chosen arbitrarily from the available set of values. Thus, the actual length of the result value is unpredictable. As noted, there are some cases where the GROUP BY clause cannot refer directly to a column that is specified in the SELECT clause as an expression (scalar-fullselect, variant or external action functions). To group using such an expression, use a nested table expression or a common table expression to first provide a result table with the expression as a column of the result. For an example using nested table expressions, see Example 9. The following example is to be added at the end of the examples section. Example 9: Display the average education level and salary for 5 random groups of employees. This query requires the use of a nested table expression to first set a random value for each employee so that it can subsequently be used in the GROUP BY clause. SELECT RANDID , AVG(EDLEVEL), AVG(SALARY) FROM ( SELECT EDLEVEL, SALARY, INTEGER(RAND()*5) AS RANDID FROM EMPLOYEE ) AS EMPRAND GROUP BY RANDID ORDER-BY-CLAUSE: Replace the text of the order-by-clause section of the "Queries" section with the following: +--------------------------------------------------------------------+ | | | ,Ŀ | | ASCĿ | |  ORDER BY  sort-key  | | DESC | | | | SORT-KEY: | | simple-column-name Ĵ | | simple-integerĴ | | sort-key-expression | | | +--------------------------------------------------------------------+ The ORDER BY clause specifies an ordering of the rows of the result table. If a single sort specification (one sort-key with associated direction) is identified, the rows are ordered by the values of that sort specification. If more than one sort specification is identified, the rows are ordered by the values of the first identified sort specification, then by the values of the second identified sort specification, and so on. The length attribute of each sort-key must not be more than 254 characters for a character column or 127 characters for a graphic column (SQLSTATE 42907). A named column in the select list may be identified by a sort-key that is a simple-integer or a simple-column-name. An unnamed column in the select list must be identified by an simple-integer or, in some cases, by a sort-key-expression that matches the expression in the select list (see details of sort-key-expression). A column is unnamed if the AS clause is not specified and it is derived from a constant, an expression with operators, or a function.(2) Ordering is performed in accordance with the comparison rules described in Chapter 3. The null value is higher than all other values. If the ORDER BY clause does not completely order the rows, rows with duplicate values of all identified columns are displayed in an arbitrary order. For the purposes of the descriptions that follow, consider an ordering subselect to be the fullselect of a select-statement that is a subselect. simple-column-name Usually identifies a column of the result table. In this case, simple-column-name must be the column name of a named column in the select list. The simple-column-name may also identify a column name of a table, view or nested table identified in the FROM clause of an ordering subselect. An error occurs if the ordering subselect: o specifies DISTINCT in the select-clause (SQLSTATE 42822) o produces a grouped result and the simple-column-name is not a grouping-expression (SQLSTATE 42803). Determining which column is used for ordering the result is described under "Column name in sort keys" (see "Notes"). simple-integer Must be greater than 0 and not greater than the number of columns in the result table (SQLSTATE 42805). The integer n identifies the nth column of the result table. sort-key-expression An expression that is not simply a column name or an unsigned integer constant. The fullselect of the select-statement must be a subselect to use this form of sort-key. The sort-key-expression cannot include a correlated scalar-fullselect (SQLSTATE 42703) or a function with an external action (SQLSTATE 42845). Any column-name within a sort-key-expression must conform to the rules described under "Column names in sort keys" (see "Notes"). There are a number of special cases that further restrict the expressions that can be specified. o DISTINCT is specified in the SELECT clause of the ordering subselect (SQLSTATE 42822). The sort-key-expression must match exactly with an expression in the select list of the ordering subselect (scalar-fullselects are never matched). o The ordering subselect is grouped (SQLSTATE 42803). The sort-key-expression can: - be an expression in the select list of the ordering subselect, - include a grouping-expression from the GROUP BY clause of the ordering subselect - include a column function, constant or host variable. ASC Uses the values of the column in ascending order. This is the default. DESC Uses the values of the column in descending order. NOTES o COLUMN NAMES IN SORT KEYS: - The column name is qualified. The fullselect in the select-statement must be a subselect (SQLSTATE 42877). The column name must unambiguously identify a column of some table, view or nested table in the FROM clause of the ordering subselect (SQLSTATE 42702). The value of the column is used to compute the value of the sort specification. - The column name is unqualified. -- The fullselect of the select-statement is a subselect. If the column name is identical to the name of more than one column of the result table, the column name must unambiguously identify a column of some table, view or nested table in the FROM clause of the ordering subselect (SQLSTATE 42702). If the column name is identical to one column, that column is used to compute the value of the sort specification. If the column name is not identical to a column of the result table, then it must unambiguously identify a column of some table, view or nested table in the FROM clause of the fullselect in the select-statement (SQLSTATE 42702). -- The fullselect of the select-statement is not a subselect (simple-column-name only). The column name must not be identical to the name of more than one column of the result table (SQLSTATE 42702). The column name must be identical to exactly one column of the result table (SQLSTATE 42707) and this column is used to compute the value of the sort specification. o LIMITS: The use of a sort-key-expression or a simple-column-name where the column is not in the select list may result in the addition of the column or expression to the temporary table used for sorting. This may result in reaching the limit of the number of columns in a table or the limit on the size of a row in a table. Exceeding these limits will result in an error if a temporary table is required to perform the sorting operation. NOTES: (1) In general, the grouping-expression must match part of the expression where it is used. If the concatenation operator is used, the grouping-expression must be used exactly as the expression where it is used. (2) The rules for determining the name of result columns for a fullselect that involves set operators (UNION, INTERSECT, or EXCEPT) can be found in 'fullselect' in the same chapter. 9.12 Application Programming Guide The following information on the bottom of page 24, reads: The host-language linker creates an executable application. For example, o With OS/2, the application can be an executable file or a dynamic link library. o With Windows applications, the application is an executable file. o With UNIX-based systems, the application can be an executable load module or a shared library. It should read as follows: The host-language linker creates an executable application. For example, o On OS/2 or Windows platforms, the application can be an executable file or a dynamic link library (DLL). o On UNIX-based systems, the application can be an executable load module or a shared library. Note that although applications can be DLLs on Windows platforms, for Windows 3.1 or Windows 95, the DLLs are loaded directly by the application and not by the DB2 database manager. On Windows NT, DLLs can be loaded by the database manager. Stored procedures, which are normally built as DLLs (on OS/2 and Windows NT) or shared libraries (on UNIX-based platforms) are unsupported on the Windows 3.1 and Windows 95 platforms as they are client-only. For information on using stored procedures, refer to Chapter 5 in the DB2 Application Programming Guide. 9.13 References to CompuServe In the CompuServe section found in the introductions of the DB2 manuals, and at the end of the manuals in the section on contacting IBM, please note the following corrections: o "Compuserve" should be "CompuServe". o "IBMDB2 Family Forum" should be the "IBM DB2 Family Forum". 9.14 Database Server Up and Running! In Chapter 4, "Installing DB2 World Wide Web Connection On a Separate Server", some option files are missing from the list. It should be as follows: o db2www.www.db2v2 (DB2 World Wide Web Connection for DB2 Version 2) o db2www.www.base (DB2 World Wide Web Connection Base) o db2www.www.icons (DB2 World Wide Web Connection Icons) o db2www.www.sample_html (DB2 World Wide Web Connection Sample HTML) o db2www.www.sample_db (DB2 World Wide Web Connection Sample Database) o db2www.www.sample_macros (DB2 World Wide Web Connection Sample Macros) 9.15 Messages Reference 9.15.1 New Messages The following messages are to be added to the Messages Reference book. SQL2548N DATABASE CODEPAGE INDICATED IN BACKUP IMAGE DIFFERS FROM THAT OF THE CURRENT ON-DISK DATABASE. THE RESTORE OPERATION HAS FAILED. EXPLANATION: The database contained in the backup image contains data stored ina codepage different from that of the database into which the data is being restored. This problem can be caused in three ways: 1. The database you are restoring into has a different codepage than that of the database in the backup image. 2. The user is restoring into a new database from a session with a different codepage than the backup. 3. The backup, image is corrupt, and contains invalid character set information. USER RESPONSE: 1. If restoring over an existing database, make sure that the codepage of the existing database matches that of the backup image. 2. If restoring to a new database, make sure that the codepage of the session from which the restore command was issued is the same as that of the backup image. 3. Contact IBM Service. SQL0214N AN EXPRESSION STARTING WITH "" IN THE "" CLAUSE IS NOT VALID. REASON CODE = "". EXPLANATION: The expression identified by the first part of the expression "" in the "" clause is not valid for the reason specified by the "" as follows: 1 The fullselect of the select-statement is not a subselect. Expressions are not allowed in the ORDER BY clause for this type of select-statement. This reason code occurs only when "" is ORDER BY. 2 DISTINCT is specified in the select clause and the expression cannot be matched exactly with an expression in the select list. This reason code occurs only when "" is ORDER BY. 3 Grouping is caused by the presence of a column function in the ORDER BY clause. This reason code occurs only when "" is ORDER BY. 4 Expression in a GROUP BY clause cannot be a scalar-fullselect. This reason code occurs only when "" is GROUP BY. The statement cannot be processed. USER RESPONSE: Modify the select-statement based on the reason specified by the "" as follows: 1 Remove the expression from the ORDER BY clause. If attempting to reference a column of the result, change the sort key to the simple-integer or simple-column-name form. 2 Remove DISTINCT from the select clause or change the sort key to the simple-integer or simple-column-name form. 3 Add a GROUP BY clause or remove the column function from the ORDER BY clause. 4 Remove any scalar-fullselect from the GROUP BY clause. If grouping is desired on a column of the result that is based on a scalar-fullselect use a nested table expression or a common table expression to first provide a result table with the expression as a column of the result. SQLCODE: -214 SQLSTATE: 42822 9.15.2 Changed Messages The following is additional information for message SQL1419N: EXPLANATION: The package "" can not be executed. The package was bound on a database manager level which lacks EUC (Extended Unix Code) and non-SBCS character conversion support. An attempt to execute the package from one of these environments was detected. USER RESPONSE: To execute the package in the current environment, explicitly rebind the named package using either the REBIND or the BIND command. Otherwise, execute the package from the original binding environment. The following messages are changed as follows: SQL0119N AN EXPRESSION STARTING WITH "" IS SPECIFIED IN A SELECT CLAUSE, HAVING CLAUSE, OR ORDER BY CLAUSE IS NOT SPECIFIED IN THE GROUP BY CLAUSE OR IT IS IN A SELECT CLAUSE, HAVING CLAUSE, OR ORDER BY CLAUSE WITH A COLUMN FUNCTION AND NO GROUP BY CLAUSE IS SPECIFIED. EXPLANATION: The SELECT statement has one of the following errors: o The identified expression and a column function are contained in the SELECT clause, HAVING clause, or ORDER BY clause but there is no GROUP BY clause o The identified expression is contained in the SELECT clause, HAVING CLAUSE, or ORDER BY clause but is not in the GROUP BY clause. The identified expression is an expression that starts with "". The expression may be a single column name. The statement cannot be processed. USER RESPONSE: Correct the statement by including the expression in the GROUP BY clause that are in the SELECT clause, HAVING clause or ORDER BY clause or by removing the column function from the SELECT statement. SQLCODE: -119 SQLSTATE: 42803 SQL0120N A WHERE CLAUSE, GROUP BY CLAUSE, SET CLAUSE, OR SET TRANSITION-VARIABLE STATEMENT CONTAINS A COLUMN FUNCTION. EXPLANATION: A WHERE clause can contain a column function only if that clause appears within a subquery of a HAVING clause and the argument of the column function is a correlated reference to a group. A GROUP BY clause can contain a column function only if the argument of the column function is a correlated reference to a column in a different subselect than the one containing the GROUP BY clause. A SET clause of an UPDATE statement or a SET transition-variable statement can only include a column function within a fullselect on the right hand side of an assignment. The statement cannot be processed. USER RESPONSE: Change the statement so that the column function is not used or used only where it is supported. SQLCODE: -120 SQLSTATE: 42903 SQL0206N "" IS NOT A COLUMN IN AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE OR IS NOT A VALID TRANSITION VARIABLE FOR THE SUBJECT TABLE OF A TRIGGER. EXPLANATION: This error can occur in the following cases: o For an INSERT or UPDATE statement, the specified column is not a column of the table, or view that was specified as the object of the insert or update. o For a SELECT or DELETE statement, the specified column is not a column of any of the tables or views identified in a FROM clause in the statement. o For an ORDER BY clause, the specified column is a correlated column reference in a subselect, which is not allowed. o For a CREATE TRIGGER statement: - A reference is made to a column of the subject table without using an OLD or NEW correlation name. - The left hand side of an assignment in the SET transition-variable statement in the triggered action specifies an old transition variable where only a new transition variable is supported. The statement cannot be processed. USER RESPONSE: Verify that the column and table names are specified correctly in the SQL statement. For a SELECT statement, ensure that all the required tables are named in the FROM clause. For a subselect in an ORDER BY clause, ensure that there are no correlated column references. If a correlation name is used for a table, verify that subsequent references use the correlation name and not the table name. For a CREATE TRIGGER statement, ensure that only new transition variables are specified on the left hand side of assignments in the SET transition-variable statement and that any reference to columns of the subject table have a correlation name specified. SQLCODE: -206 SQLSTATE: 42703 SQL0208N THE ORDER BY CLAUSE IS NOT VALID BECAUSE COLUMN "" IS NOT PART OF THE RESULT TABLE. EXPLANATION: The statement is not valid because a column "" specified in the ORDER BY list is not specified in the SELECT list and is not in the result table. Only columns in the result table can be used to order that result when the fullselect of the select-statement is not a subselect. The statement cannot be processed. USER RESPONSE: To correct the syntax of the statement, either add the specified column to the result table or delete it from the ORDER BY clause. SQLCODE: -208 SQLSTATE: 42707 SQL0582N A CASE EXPRESSION IN A SELECT LIST, VALUES CLAUSE, IN PREDICATE, OR ORDER BY CLAUSE CANNOT INCLUDE A QUANTIFIED PREDICATE, AN IN PREDICATE USING A FULLSELECT OR AN EXISTS PREDICATE. EXPLANATION: A search condition of a CASE expression is: o a quantified predicate (one using SOME, ANY, or ALL), o an IN predicate using a fullselect or o an EXISTS predicate and the CASE expression is part of: o a select list o a VALUES clause o an IN predicate o an GROUP BY clause or o an ORDER BY clause. Such CASE expressions are not supported. The statement cannot be processed. USER RESPONSE: Remove the use of the quantified predicate, IN predicate, or EXISTS predicate from the CASE expression. In some cases, it may be possible to write the query so that the CASE expression is not part of the select list or an IN predicate. SQLCODE: -582 SQLSTATE: 42625 SQL0583N THE USE OF FUNCTION "" IS INVALID BECAUSE IT IS VARIANT OR HAS AN EXTERNAL ACTION. EXPLANATION: The function "" is defined as a variant function or a function with an external action. This type of function is not supported in the context in which it is used. The contexts in which these are not valid are: o as the first operand of a BETWEEN predicate o in the expression prior to the first WHEN keyword in a simple-case-expression. o in an expression of a GROUP BY clause o in an expression of an ORDER BY clause (external action only) The statement cannot be processed. USER RESPONSE: If the use of a variant or external action function was not intended, substitute a function without these characteristics. If the behaviour associated with the variant or external action function is intentional, use the alternate form of the statements that make that intent explicit. o Instead of the BETWEEN predicate, use the corresponding statement using the equivalent combination of comparison predicates (a>=b and a<=c instead of a between b and c). o Instead of a simple-when-clause, use the corresponding searched-when-clause where the function would get specified in each search-condition. o Remove the variant or external action function from the GROUP BY clause. If grouping is desired on a column of the result that is based on a variant or external action function use a nested table expression or a common table expression to first provide a result table with the expression as a column of the result. o Remove the external action function from the ORDER BY clause. If the column is part of the result set of the query, change the expression in the ORDER BY clause to the simple-integer or simple-column-name form of the sort key. SQLCODE: -583 SQLSTATE: 42845 ====================================================== 10. DB2 World Wide Web Connection For AIX Information 10.1 Installation Notes If you have a beta version of DB2 World Wide Web Connection installed on your system, delete it before installing the version included in Database Server. Note: Make sure you back up all html documents before deleting the beta version of the product. If you have installed the DB2 WWW Connection alpha release, remove the line "set LIBPATH=$LIBPATH:" from .cshrc and .profile. If your Web Server uses directories other than /usr/lpp/internet/server_root/cgi-bin for Web Server CGI executable programs, /usr/lpp/internet/server_root/pub for the Web Server document root directory (where HTML source files are stored), and /usr/lpp/internet/server_root/icons for Web Server image files that are used in HTML files, you will need to do the following before running DB2 WWW Connection: - Update your Web Server configuration file to use the above directories -OR- - Copy db2www and db2sql.bnd to your Web Server's directory that stores CGI executable programs. - Copy db2www.ini and all of the htm files to your Web Server's document root directory. - Copy all of the gif files to your Web Server's image directory - Create the tmplobs directory (under the document root directory) and allow your Web Server to have write access to this directory. 10.2 DB2 WWW Connection Configuration Configuration information for DB2 WWW Connection is in the file 'db2www.ini', which resides in the Web Server's document root directory (e.g. /usr/lpp/internet/server_root/pub). Inside the file, there are three entries: MACRO_PATH -specifies where the directory for the macro files are BINDFILE -specifies the location of the bind file for DB2 WWW DB2INSTANCE -specifies the DB2 instance owner of the tables being queried 10.3 Usage Notes o Make sure that the Web Server and database connection are running o Make sure that the file db2www.ini in the 'pub' directory has the correct settings. o If necessary, verify that all DB2 WWW Connection macros (including the sample application macros that are shipped with the product) use the LOGIN and PASSWORD variables. See the explanation under "Authentication" below to determine if this step is necessary or not. o If you use the BLOB and CLOB features of DB2 WWW, be sure to periodically remove old temporary files in tmplobs directory. o Since the file 'db2www.ini' is viewable by other people using a Web Browser, we recommend that appropriate access restrictions on that file be enforced using the access control protocol provided by your Web Server. Further security can be provided by adding encryption and a firewall. Refer to your Web Server documentation for details. o A template macro file (template.d2w) is provided in the macro directory to help you get started writing your own macro files. 10.3.1 Authentication DB2 WWW Connection uses the user ID specified with the macro variables LOGIN and PASSWORD for database access authentication. If these variables are not specified, the user ID which started the Web Server daemon is used for database access authentication. For example, if the Web Server was started with the database administrator's user ID and DB2 WWW Connection uses a macro file that does not have the LOGIN and PASSWORD variables defined, the query will still be successful because the database administrator has privileges which give unlimited database access. If your Web Server was started with a user ID that does not have database access authority, you must specify the LOGIN and PASSWORD macro variables in all macro files that are used. Note that this applies to the sample macros that are shipped with DB2 WWW Connection. Since these macro files do not use the LOGIN and PASSWORD variables, you must add these variables to the macro files. If your Web Server was started with a user ID that has database access authority, you will not need to use the LOGIN and PASSWORD variables in your macro files. However, verify that this user ID has the necessary Web Server directory and file read/write privileges. 10.4 Using the Sample Application 10.4.1 Installation Notes o The db2data subdirectory (installed in /usr/lpp/internet/db2www) contains the necessary data files for installing the sample database, "celdial". These data files were exported using the ixf format. o When following the instructions provided in the Up and Running! book to install the sample application, make sure that the user ID that you are using can become an instance owner user ID. For more information on instance owners, refer to the Up and Running!. o If necessary, update the DB2 WWW Connection macros to use the LOGIN and PASSWORD variables. See the explanation under "Authentication" to determine if this step is necessary or not. o Update your Web Server's configuration to recognize the .htm file extension as an HTML/Text file, or rename all .htm files that were shipped with DB2 WWW Connection to .html files. Note that if you rename these files, you will also need to update the references to these files in the .htm and .d2w files. 10.4.2 DBCS Considerations When Using the Sample Application If you are installing the sample application on a DBCS server machine, add 'MODIFIED BY FORCEIN' to each IMPORT command in the appropriate command file. This modifier directs DB2 to accept the data with no code page conversion. For example, change the line: DB2 IMPORT FROM CUSTSHIP OF IXF CREATE INTO CUSTSHIP to DB2 IMPORT FROM CUSTSHIP OF IXF MODIFIED BY FORCEIN CREATE INTO CUSTSHIP ============================= 11. DDCS For AIX Information 11.1 Prerequisites 11.1.1 Need to Apply DB2 for MVS PTF to Use DB2DRDA as RTPN If you want to use DCE directory services support in DB2 for AIX to connect to DB2 for MVS, you must apply the DB2 for MVS PTF UN73393 that supports the use of DB2DRDA as the name of the remote transaction program name (RTPN). 11.2 Documentation Changes 11.2.1 Installation Requirements The DB2 Planning Guides for OS/2 and AIX contain out of date information for memory and hard disk space requirements for DDCS V2.3.1. Please refer to the information in Chapter 1 of the updated DDCS Installation and Configuration Guides. In Chapter 1, in the section entitled "Installation Requirements", the bullet that says "An appropriate SNA product" is incorrect. It should read: "SNA Server for AIX V2.1 or higher". Also, ignore the sub-bullets referencing the Encina PPC Gateway. 11.2.2 National Language (Code Page and Conversion) Information Changes Some of the information contained in the V2.1.0 DDCS User's Guide has been superseded by updated versions of tables and other information relating to National Language, and related considerations such as character set conversion rules. Please refer to updated information in the DDCS Installation and Configuration Guide (S20H-4794-01). 11.2.3 OS/2 and UNIX-Based Databases Throughout the DDCS manuals, references to OS/2 and UNIX-based databases should be references to the DB2 Common Server Family of Products instead. 11.2.4 SET CURRENT PACKAGESET Support In the DDCS User's Guide in Chapter 7, "Programming in a DRDA Environment", the following sentence should be included: The DDCS support for the SET CURRENT PACKAGESET command is now available for DB2 for MVS/ESA and DB2 for Common Servers. 11.2.5 DB2 for OS/400 Column Types and Length In the DDCS User's Guide in Chapter 7, "Programming in a DRDA Environment", in the section that describes DB2 for OS/400 Column Types and Length, the following text should exist: DB2 for OS/400 Version 3.1 supports the column types GRAPHIC and VARGRAPHIC. Please refer to your DRDA AS documentation for the most up-to-date features supported by your DRDA Server. 11.2.6 Binding DDCS Utilities to an OS/400 Database The following step is documented in the DDCS User's Guide in Chapter 4 to allow you to bind all the DDCS utilities to the DRDA server database; however, an error occurs. The error is a result of using the GRANT bind option when binding a list of bind files. The bind receives a -901 sqlcode from the OS/400 database on the third bind file in the list. db2 bind @ddcs400.lst blocking all sqlerror continue grant public messages ddcs400.msg 11.2.6.1 Possible Solutions 1. PTF SF23634 is available for the OS/400 V3.1 database to fix this problem. 2. Alternatively, use either of the following two methods: a. Issue separate bind requests for each bind file in ddcs400.lst, (19 bind files resulting in 19 bind requests), for example: ===> connect step db2 connect to DBALIAS user USERID using PASSWORD ===> bind step db2 bind db2ajgrt.bnd blocking all sqlerror continue grant public messages db2ajgrt.msg db2 bind db2clics.bnd blocking all sqlerror continue grant public messages db2clics.msg ... db2 bind db2uimpm.bnd blocking all sqlerror continue grant public messages db2uimpm.msg ===> drop connection db2 connect reset b. Issue the bind request against ddcs400.lst but omit the GRANT PUBLIC bind option. As a separate step, grant execute permission to public on each of the packages. Use the ddcspkgn utility to determine the package names of the bind files in ddcs400.lst. An example is provided below. Refer to Chapter 4 of the DDCS User's Guide for more details on this option. ===> connect step db2 connect to DBALIAS user USERID using PASSWORD ===> bind step db2 bind @ddcs400.lst blocking all sqlerror continue messages ddcs400.msg ===> determine the package names of bind files in ddcs400.lst ddcspkgn @ddcs400.lst ===> grant step db2 grant execute on package SQLAB4C0 to public db2 grant execute on package SQLL14C0 to public ... db2 grant execute on package SQLUF4C0 to public ===> drop connection db2 connect reset 11.2.7 Binding Utilities for Back-Level Clients In the DDCS User's Guide, in Appendix D, "Binding Utilities for Back Level Clients", the command to find out the package names for bind files should read ddcspkgn @bindfile.lst, NOT ddcspkgn bindfile.lst. 11.2.8 Description of Environment Keywords The description of the following environment keywords and their respective values, in the IBM DDCS User's Guide is incomplete. DB2ACCOUNT DB2BQTIME DB2COMM DB2CHKPTR DB2BQTRY DB2DIRPATHNAME DB2DBDFT DB2RQTIME DB2CLIENTCOMM DB2INSTANCE DB2IQTIME DB2ROUTE DB2LOADREC DB2OPTIONS DB2INSPATH 11.2.9 DDCS (non-OS/2) Auto Disconnect Option Not Supported In the DDCS User's Guide, in Chapter 3 "Updating Database Directories" in the section entitled "Parameter String" under the heading "DCS Directory", the description of INTERRUPT_ENABLED should read: On non-OS/2 platforms, DDCS does not support the option of automatically disconnecting when an application using it receives an interrupt request. 11.2.10 SPM Instance Name In Appendix D, "LU6.2 Sync Point Manager Considerations", in the section "Installing the SPM", the DDCS Installation and Configuration Guide for AIX refers to the "SPM database". This should read "SPM instance", as referred to in the DB2 Command Reference: Before issuing LIST DRDA INDOUBT TRANSACTIONS, the application must be connected to the Sync Point Manager (SPM) instance. Use the SPM_NAME database manager configuration parameter as the dbalias on the CONNECT statement. Throughout the "Installing SPM" section in Appendix D, wherever the words "SPM database" appear alone without the word "alias" immediately following them, please replace the word "database" with the word "instance". The documentation will be changed in the next update of these publications. 11.3 Syncpoint Manager (SPM) If you have both a DB2 server and a DDCS gateway on the same machine, then there are some coexistence considerations which affect the use of LU names and TP names between the inbound DRDA AS support, and the outbound DRDA2 AR support. 11.3.1 AIX SNA Server Coexistence Considerations This section of the README is referenced by the DDCS for AIX Installation and Configuration Guide in Appendix D (LU 6.2 Syncpoint Manager Considerations), section "AIX SNA Server Coexistence Considerations". In order to use a node which has the DDCS for AIX V2.3.1 Syncpoint Manager started as a DB2 for AIX V2.1.1 DRDA1 AS or as a database server for clients accessing via APPC you must do the following: On the DDCS for AIX Syncpoint Manager (SPM) Server Node: o You must install DB2 for AIX Server V2.1.1. o Ensure that there are no SNA Server/6000 LU 6.2 Transaction Program Name (TPN) definitions defined for the following transaction programs: - 07F6C4C2 (hex) - 07F6E2D5 (hex) - 06F2 (hex) - 06F2F0F1 (hex) - DB2INTERRUPT (alphanumeric) - DB2DRDA (alphanumeric) - TP name defined in the data base manager configuration parameter TPNAME. You can validate this by setting the SPM_NAME to the correct LU name and setting the DIAGLEVEL = 4 in the Database Manager Configuration. When you issue db2start to initiate the SPM you should NOT see the following message in the db2diag.log : common_communication sqlccsnaconnmgr_spm_listener29 Probe:29 DIA3002E Error encountered while trying to start "SPM" protocol support code from "ioctl(ALLOCATE_LISTEN)" was "OPTIONAL TPs NOT REGISTERED". NOTE: The above procedure assumes you have configured the Database Manager (DBM) configuration parameter TPNAME. It is permissible to leave this parameter unconfigured, but if you do so you will always get the DIA3002E message in db2diag.log. In this case, before attempting to validate your SPM configuration you may wish to temporarily set a dummy value for TPNAME. You can then reset TPNAME to the default value if you wish, using the following command: db2 update dbm cfg using tpname NULL On the Database Client Node: Ensure all clients use the LU specified on the SPM node in the Database Manager Configuration parameter SPM_NAME as their partner LU. 11.3.2 Prerequisite PTFs AIX SNA/Server 2.1 PTFs PTF U437491 (fixes for APAR IX50393) PTF U437840 (fixes for APAR IX51831) PTF U440157 (fixes for APAR IX54025) AIX SNA/Server 2.2 PTFs PTF U437857 (fixes for APAR IX50393) PTF U440318 (fixes for APAR IX51831) Fix for APAR IX54025 (PTF number not available at time of printing) AIX SNA/Server 3.1 PTFs PTF U440156 (fixes for APAR IX54025) All DB2/MVS V3.1 systems PTFs UN73393 and UN76673 (fixes for APARs PN67179 and PN70102) All DB2/MVS V4.1 systems PTF UN76674 (fixes for APAR PN70102) All DB2/400 V3.1 systems PTF SF26564 (fixes for APAR SA46917) APAR SA48935 (no PTF available at time of printing README) PTF SF26804 (fixes for OS/400 V3R1M0, product 5763SS1) 11.3.3 AIX/SNA Server Operational Considerations Terminating AIX/SNA server while the SPM is active causes the SPM to stop. Once AIX/SNA server is restarted, you must issue a db2stop and db2start on the DDCS Multi-User Gateway in order to allow the SPM to reinitialize. 11.4 Authentication Default For any system database directory entry that DDCS uses for establishing a connection, if the authentication parameter is not specified, DDCS will use a default authentication of SERVER. This default applies to the system database directory entry on the DDCS machine. In addition, this default applies to the system database directory entry at the remote client in a multi-user gateway DDCS environment. 11.5 Performance Troubleshooting If DDCS for AIX users are experiencing a long response time during large queries from DRDA AS hosts (that is, DB2 for MVS, SQL/DS, or DRDA AS for DB2 for AIX), the following areas should be examined for the possible cause of the performance problem: 1. For queries which result in returning large data blocks from the DRDA host (usually 32K of data and above), ensure that the database manager configuration parameter RQRIOBLK is set to 32767. This can be done using the Command Line Processor (CLP) as follows: db2 update database manager configuration using RQRIOBLK 32767 2. If VTAM is used in the connection to DRDA AS, look under "switched major node" configuration for the value of the PACING parameter. On the DDCS workstation, examine the communication setup of the "LU 6.2 Mode Profile" for IBMRDB mode definition. In this definition, ensure the value for the "Receive pacing window" parameter is less than or equal to the PACING value defined on VTAM. A common value for "Receive pacing window" on the DDCS workstation and "PACING" on VTAM is 8. 3. Ensure the maximum RU size defined in the IBMRDB mode definition is not less than 4K. 4. Consult with the VTAM administrator in your environment to ensure that VTAM is using "adaptive pacing" in LU-LU sessions with your DDCS workstation. 11.6 DDCS 2.3 Gateway Connection to DB2/MVS This section documents important additional information about setting up a working DDCS Gateway connection to DB2/MVS: o VTAM definitions required at your MVS host. o DB2/MVS configuration steps. This information is not presently provided in "DDCS Installation and Configuration" manuals. The sample presented here illustrates a DDCS for OS/2 scenario, but it can readily be used as a model to help you connect other variants of DDCS to DB2 for MVS. For example, if you are knowledgeable about IBM SNA Server for AIX, and you follow the instructions for setting up SNA Server profiles provided in "DDCS for AIX Installation and Customization Guide", the documentation in this section will help you complete a working connection between DDCS for AIX and DB2/MVS. You must use your own values for elements such as network name, LU name, etc., and we recommend that you use the planning worksheets provided in Appendix A of the "DDCS Installation and Configuration Guide" for your system. For further information about DRDA Connectivity, we recommend that you refer to "DRDA Connectivity Guide", SC26-4783. Also, if you will use the LU6.2 Syncpoint Manager (SPM) function of the DDCS Multi-User Gateway programs for OS/2 or AIX, it is particularly important to refer to the SPM appendix in your "DDCS Installation and Configuration Guide". The examples documented in this section do not include detailed steps required for SPM. 11.6.1 Summary of Steps In order to setup the connection, you must complete the following steps: 1. At your DDCS Gateway: a. Configure APPC. b. Configure Database Manager - see 11.6.5, "Cataloging DB2 Directory Entries at the Workstation". These steps are more completely documented in "Chapter 3: Configuring Your Network" of the "DDCS Installation and Configuration Guide" for your system. 2. At your DB2/MVS host: a. Configure VTAM - see 11.6.6, "Configuring VTAM". b. Configure DB2/MVS - see 11.6.8, "Configuring DB2/MVS". 11.6.2 Products used in this example The following products were used: o IBM PS/2 with Token-ring adapter o DB2 for OS/2 2.1 o DDCS for OS/2 2.3 o DB2/MVS 2.3 o OS/2 Warp 3.0 o IBM LAN Server 4.0 o IBM Communications Manager/2 Version 1.11 (check your "DDCS Installation and Configuration Guide" for the right level if you will work with SPM). 11.6.3 Network Element Names This example uses the following names: DDCS Gateway: - Network ID : CAIBMOML - Local Node Name : OMXRSA (PU name) - Local Node ID : 05D 30376 - LU Name : OMXRSA0A - LU Alias : OMXRSA0A HOST: - Network ID : CAIBMOML - Node Name : HOSTDB - LU Name : SFLU - LU Alias : SFLU - LAN Destination Address : 400011528905 (Tic Address in NCP-NTRI) MODE DEFINITION: - Mode Name : IBMRDB DB2/MVS: - Location : SAN_FRANCISCO SECURITY: - Security Type : Program - Authentication Type : DCS NOTE: In this scenario, both USERID and password were only checked on the Host. If you use Authentication SERVER, which is the default, then authentication will also take place at the Gateway. 11.6.4 Configuring SNA Communications Regardless of which variant of DDCS you are working with, at this point you should refer to "Chapter 3 Configuring your Network" and "Appendix A Worksheets", in your "DDCS Installation and Configuration Guide". When you have completed SNA communications configuration tasks, proceed to 11.6.5, "Cataloging DB2 Directory Entries at the Workstation". 11.6.5 Cataloging DB2 Directory Entries at the Workstation When you have configured your SNA subsystem, before you can to use the DDCS connection, you may also need to catalog the database, the workstation (node), and the DCS database at the DDCS workstation. 11.6.5.1 Cataloging the Workstation 1. From an OS/2 prompt, issue the following command: DB2 CATALOG APPC NODE HOSTMVS REMOTE SFLUNODE SECURITY PROGRAM The node name (HOSTMVS) can be any name, but it must match the entry in the system database directory. 2. If you have not previously logged on, you will be prompted for your USERID and Password. NOTES: a. The Node Name must be unique in the Node Directory b. The Node Name in the System Database Directory maps to the Node in the Node Directory. You can verify the Node directory information using the following command: DB2 LIST NODE DIRECTORY The output will look like this: Node Directory Number of entries in the directory = 1 Node 1 entry: Node name = HOSTMVS Comment = Protocol = APPC Symbolic destination name = SFLUNODE Security type = PROGRAM 11.6.5.2 Cataloging the Database 1. Use the following command: DB2 CATALOG DATABASE SFLUDB AS HOSTDB AT NODE HOSTMVS AUTHENTICATION DCS NOTES: a. The Database name in the System Database Directory maps to the Local Database Name in the DCS Directory. b. The Node Name in the System Database Directory maps to the Node Name in the Node Directory. c. The Alias is the name used with the CONNECT statement. You can verify the Database directory information using the following command: DB2 LIST DATABASE DIRECTORY The output will look like this: System Database Directory Number of entries in the directory = 1 Database 1 entry: Database alias = HOSTDB Database name = SFLUDB Node name = HOSTMVS Database release level = 6.00 Comment = Directory entry type = Remote Authentication = DCS 11.6.5.3 Cataloging the DCS Database 1. Use the following DCS Database Catalog command: DB2 CATALOG DCS DATABASE SFLUDB AS SAN_FRANCISCO NOTES: a. The local database name must be unique in the DCS directory. b. The database name in the System Database Directory maps to the Local database name in the DCS Directory. c. The target database name in the DCS directory maps to the DRDA AS location name (RDB_NAME). 11.6.5.4 Verify DCS Directory Information You can verify your DCS directory information using the following command: DB2 LIST DCS DIRECTORY The output will look like this: Database Connection Services (DCS) Directory Number of entries in the directory = 1 DCS 1 entry: Local database name = SFLUDB Target database name = SAN_FRANCISCO Application requestor name = DCS parameters = Comment = DCS directory release level = 0x0100 11.6.6 Configuring VTAM To configure the VTAM host, you must prepare the necessary definitions: 1. The APPL name for the DB2 subsystem (SFLU in these examples). 2. PU and LU definitions for the DDCS workstation (OMXRSA and OMXRSA0A respectively in these examples). 3. The log mode entry to be used for the connection (IBMRDB in these examples). Work with your VATM Administrator to determine the options to be used on your system. The following VTAM definitions correlate to parameters used elsewhere in this sample scenario. 11.6.7 VTAM DB2 APPL Definition Sample VTAM definition for the DB2 Application: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- DB2APPLS VBUILD TYPE=APPL SFLU APPL APPC=YES, X AUTH=(ACQ), X AUTOSES=1, X DLOGMOD=IBMRDB, X DMINWNL=512, X DMINWNR=512, X DSESSLIM=2048, X EAS=6000, X MODETAB=RDBMODES, X PARSESS=YES, X PRTCT=SFLU, X MODETAB=RDBMODES, X SECACPT=ALREADYV, X SRBEXIT=YES, X VERIFY=NONE, X VPACING=8 NOTES: 1. Continuations must begin in column 16. 2. If you will use SPM, you must also include: SYNCLVL=SYNCPT 11.6.7.1 VTAM DB2 PU and LU definitions Sample Switched Major Node Definition: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- SWITCHED MAJOR NODE DEFINITION FOR PU OMXRSA and INDEPENDENT LU OMXRSA0A LOC300 VBUILD TYPE=LOCAL OMXRSA ADDR=01, IDBLK=05D, IDNUM=30376, ANS=CONT, DISCNT=NO, X IRETRY=YES, ISTATUS=ACTIVE, MAXDATA=4302,MAXOUT=7, X MAXPATH=1,PUTYPE=2,SECNET=NO,MODETAB=RDBMODES X SSCPFM=USSSCS,PACING=0,VPACING=2 OMXRSA0A LOCADDR=000, MODETAB=RDBMODES, DLOGMODE=IBMRDB OTHERLU LOCADDR=002 11.6.7.2 Sample Log Mode Definition Sample Log Mode: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--- RDBMODES MODTAB IBMRDB MODEENT LOGMODE=IBMRDB, DRDA DEFAULT MODE * TYPE=0, NEGOTIABLE BIND * PSNDPAC=X'01', PRIMARY SEND PACING COUNT * SSNDPAC=X'01', SECONDARY SEND PACING COUNT * SRCVPAC=X'00', SECONDARY RECEIVE PACING COUNT * RUSIZES=X'8989', RUSIZES IN-4K OUT-4K * FMPROF=X'13', LU6.2 FM PROFILE * TSPROF=X'07', LU6.2 TS PROFILE * PRIPROT=X'B0', LU6.2 PRIMARY PROTOCOLS * SECPROT=X'B0', LU6.2 SECONDARY PROTOCOLS * COMPROT=X'D0B1', LU6.2 COMMON PROTOCOLS * PSERVIC=X'060200000000000000122F00' LU6.2 LU TYPE SNASVCMG MODEENT LOGMODE=SNASVCMG, DRDA DEFAULT MODE * PSNDPAC=X'00', PRIMARY SEND PACING COUNT * SSNDPAC=X'02', SECONDARY SEND PACING COUNT * SRCVPAC=X'00', SECONDARY RECEIVE PACING COUNT * RUSIZES=X'8585', RUSIZES IN-1K OUT-1K * FMPROF=X'13', LU6.2 FM PROFILE * TSPROF=X'07', LU6.2 TS PROFILE * PRIPROT=X'B0', LU6.2 PRIMARY PROTOCOLS * SECPROT=X'B0', LU6.2 SECONDARY PROTOCOLS * COMPROT=X'D0B1', LU6.2 COMMON PROTOCOLS * PSERVIC=X'060200000000000000000300' LU6.2 LU TYPE NOTE: You must define SNASVCMG when using APPC. 11.6.8 Configuring DB2/MVS Before you can use the DDCS connection, you need to configure DB2/MVS to allow connections remote requests. The example below suggests a simple configuration. Work with your DB2 Administrator to determine the options to be used on your DB2/MVS system. 11.6.8.1 Updating the SYSIBM.SYSUSERNAMES Table COLUMN EXAMPLE REMARK ====== ======= ====== Type I Authid (blank) LU Name OMXRSA0A Must match the DDCS/2 g/w's LU name NewAuthID (blank) Password (blank) USERNAMES types are: O (outbound translation), I (inbound translation), B (both inbound and outbound) and blank (no authorization ids are translated, and no password is sent to the server). You can use a DB2/MVS command such as the following to update this table: INSERT INTO SYSIBM.SYSUSERNAMES VALUES('I','JASI','OMXRSA0A',' ',' '); 11.6.8.2 Update the SYSIBM.SYSLUNAMES Table COLUMN EXAMPLE REMARK ====== ======= ====== LUNAME OMXRSA0A Must match the DDCS Gateway LU name SYSMODENAME IBMRDB USERSECURITY A ENCRYPTPSWDS N MODESELECT N USERNAMES I You can use a DB2/MVS command such as the following to update this table: INSERT INTO SYSIBM.SYSLUNAMES VALUES('OMXRSA0A',' ','A','N',' ','I'); 11.7 Connectivity Via DDCS (Thailand Users Only) Please note that currently only connectivity between DB2 for OS/2 and DB2 for AIX is supported. Connectivity between the following is not supported due to the Thai CCSID, 838, not being supported on the host: DB2 for OS/2 and DB2 for MVS via DDCS DB2 for OS/2 and DB2 for OS/400 via DDCS DB2 for OS/2 and DB2 for VM and VSE via DDCS DB2 for AIX and DB2 for MVS via DDCS DB2 for AIX and DB2 for OS/400 via DDCS DB2 for AIX and DB2 for VM and VSE via DDCS 11.8 DDCS User's Guide BIND Information On Page 20 of "DDCS User's Guide", S20H-4793-00, Figure 4 "Bind Files and Packages" contains information that has been superseded for DDCS Version 2.3.1. The respective "xxx" values for DB2 Version 2.1.1 and DDCS Version 2.3.1 are as follows: 5W0 DB2 Client Application Enabler for Windows Version 2.1.1 5D0 DB2 Client Application Enabler for OS/2 Version 2.1.1 5C0 DB2 Client Application Enabler for AIX Version 2.1.1, also for HP-UX and Solaris. For the complete updated text of this table please refer to the appropriate "Installing and Using Clients" manual for your system. 11.9 Using DDCS with Bull SNA on AIX 4.1 If Bull DPX/20 SNA/20 Server is installed prior to installing DDCS Multi-User Gateway Version 2.1.1 then DDCS is automatically configured to use Bull SNA, otherwise DDCS is configured to work with IBM SNA Server. To determine if Bull SNA is installed on your AIX 4.1 system issue the command: lslpp -l express.exsrv+dsk If Bull SNA is installed then you will see output similar to the following: Fileset Level State Description ---------------------------------------------------------------------- Path: /usr/lib/objrepos express.exsrv+dsk 2.1.3.0 COMMITTED EXPRESS SNA Server and Integrated Desktop If you install Bull SNA after installing DDCS Multi-User Gateway and you want DDCS to use Bull SNA instead of IBM SNA Server then logon as root and run the command: /usr/lpp/db2_02_01/cfg/db2cfgos 11.9.1 Installing Bull SNA Support with DB2/6000 Client or Gateway If you wish to install the Bull DPX/20 SNA/20 Server, then you must have the following: SOFTWARE: o AIX V4.1.4 o Express SNA Server V2.1.3 You will also need the Bull DPX/20 SNA/20 Server Configuration Guide. Note: The DB2/6000 Gateway when used with the Bull SNA server, cannot have inbound APPC connections from remote clients. It can only have outbound APPC connections to the host. 11.9.1.1 Configuring the Bull DPX/20 SNA/20 Server The following is a sample configuration: Client Controller Server (MVS) ----------------- ---------- ------------ Host name robocop LU 6.2 name SPIFNET.NYX1GW01 NYX NYM2DB2 Use the command express to configure the following: Config Express Default configuration for EXPRESS Node NYX1 SPIFNET.NYX1 (HOSTNAME=ROBOCOP) Indep. LUs 6.2 LUs Using All Stations LU NYX1 Control Point LU Link tok0.00001 Link (tok0) Station MVS To MVS from ROBOCOP LU NYX1GW01 To MVS from ROBOCOP LU Pair NYM2DB2 To MVS from ROBOCOP Mode IBMRDB IBMRDB Default values are used for fields not listed. Defining hardware: System (hostname) = robocop Adapter and Port = robocop.tok0 MAC Address = 400011529778 Defining SNA node: Name = NYX1 Description = SPIFNET.NYX1 (HOSTNAME=ROBOCOP) Network ID = SPIFNET Control Point = NYX1 XID Block = 05D XID ID = 29778 Defining token ring link: Name = tok0.00001 Description = Link (tok0) Connection Network name Network ID = SPIFNET Control Point = NYX Defining token ring station: Name = MVS Description = To MVS from ROBOCOP Remote MAC address = 400009451902 Remote Node name Network ID = SPIFNET Control Point = NYX Defining Local LU 6.2: Name = NYX1GW01 Description = To MVS from ROBOCOP Network ID = SPIFNET LU name = NYX1GW01 Defining Remote LU 6.2: Name = NYM2DB2 Description = To MVS from ROBOCOP Network ID = SPIFNET LU name = NYM2DB2 Remote Network ID = SPIFNET Remote Control Point = NYX Uninterpreted Name = NYM2DB2 Defining Mode: Name = IBMRDB Description = IBMRDB Class of service = #CONNECT Defining Symbolic Destination Info: Name = DB2CPIC Description = To MVS from ROBOCOP Partner LU = SPIFNET.NYM2DB2 Mode = IBMRDB Local LU = NYX1GW01 Partner TP = DB2DRDA =============================== 12. Sample Tools and Utilities A number of sample tools and utilities have been provided with this product for your convenience. These samples are provided "as-is" without any warranty of any kind. However, if you encounter an error, let us know. The sample tools and utilities, except where noted, can be found in the INSTHOME/sqllib/misc directory, where INSTHOME is the home directory of the instance owner, after an instance has been created. The following is a list of the sample tools and utilities provided: Name Description ---- ----------- db2batch DB2 Dynamic SQL Statement Processor (Described in the Administration Guide) db2bfd DB2 Bind file description tool (Described in the Application Programming Guide) db2cli DB2 Interactive CLI (executable) for design and prototyping in CLI (Located in sqllib/samples/cli and described in INTCLI.DOC in same directory.) db2evmon Formats event monitor file and named pipe output, and writes it to standard out. (Described in the Performance Monitor Guide) db2exfmt Formats the contents of the Explain tables. Given a database name and other qualifying information, this tool will, query the Explain tables for information and format the results. (Located in sqllib/misc) Information about this utility is available by typing "db2exfmt -help" on a command line. db2gov DB2 Governor utility (Located in sqllib/adm) Periodically invokes DB2 Snapshot Monitor to find out what applications are using the database. If any application is found that exceeds the limits specified in the Governor configuration files, that application is either terminated (FORCEd) or its priority is altered. Information about this utility is available by typing "db2gov -help" on a command line. db2expln DB2 SQL Explain Tool (Described in the Administration Guide) db2ipxad Returns the server's IPX/SPX** internetwork address for use by the user on a remote client machine who is cataloging an IPX/SPX node using direct addressing mode. (Mentioned in the Installing and Using OS/2 Clients and Installing and Using DB2 Clients for Windows manual) db2look DB2 statistics extraction tool (Described in the Administration Guide) db2tbst Takes a hexadecimal state value (as given from the "list tablespaces" command) and interprets the state. When you execute a DB2 command that leaves a tablespace in anything other than the normal state, using that tablespace may not be possible, but the cause of the problem is not always clear. The "list tablespaces" command indicates the state in the form of a hexadecimal string which must be interpreted based on a combination of state values found in sqlutil.h. Rather than perform this interpretation manually, the db2tbst applet may be used. For example, if you enter db2tbst 0x000c, the following output is generated (in English only): State = Quiesced Exclusive + Load Pending db2untag Displays a container tag's information so that the sysadm can check the "owning" database. (Described in this README file in the section entitled Dealing with Container in Use Error Message -294) dynexpln Example of how to explain dynamic SQL using db2expln (Described in the Administration Guide) explain.ddl Sample DDL to create Explain tables (Described in the Administration Guide) isql Creates dummy tables to allow access from DB2/VM using ISQL. (Described in the Installation and Operation Guide) lstaltsm Sample DDL to retrieve alerts for Snapshot Monitor (Located in sqllib/samples/mon) (Described in the Database Director Getting Started) sample.pv Sample performance variable profile for Snapshot Monitor (Located in sqllib/samples/mon) (Described in the Database Director Getting Started) sqldbsu Creates dummy tables to allow access from DB2/VM using SQLDBSU. (Described in the Installation and Operation Guide) storproc.ddl Sample DDL to create stored procedures pseudo_catalog DB2CLI.PROCEDURES (Described in the Call Level Interface Guide and Reference) storproc.dml Sample DML to insert entries into stored procedure pseudo_catalog DB2CLI.PROCEDURES (Described in the Call Level Interface Guide and Reference) vesampl.ddl Sample DDL to export snapshots for Visual Explain (Located in sqllib/samples/ve) (Described in the Database Director Getting Started) ======================================== 13. Limitations in Documented Functions This section identifies limitations that exist in the current release. 13.1 Transaction Manager Database Considerations When Distributed Unit of Work (DUOW) is used, DB2 for AIX and DDCS for AIX need to have a Transaction Manager (TM) database created. While this database is very much like any other DB2 database, there are certain restrictions and considerations that should be taken into account: o This is a system database for use by DB2. It is recommended that it not be used for storing any other data. o The name of this database should not be made known to the general user population. o This database should not be accessed by DRDA client systems. o This database should not be catalogued in the DCS directory. o When the DDCS for AIX product is installed without DB2 for AIX, DDCS License Terms prohibit the use of this database for storing user data. 13.2 Recursive SQL Incompatibility To correct a Version 2.1.0 memory overwrite problem, some recursive SQL statements which use CONCAT with LOB fields in their SELECT lists may no longer be valid. 13.3 Command and API Limitations The following limitation exists for version 2.1.1: o When attempting to restore from tape, the tape drive must be configured with the same block size as the tape drive used to create the backup image. So, the tape block factor (operating system parameter) should not be changed between backup and restore to/from tape. o The precompiler OPTLEVEL option indicates whether the precompiler is to optimize initialization of internal SQLDAs when host variables are used in SQL statements. The use of OPTLEVEL 1 to turn on SQLDA optimization has been disabled due to potential problems it may cause. This is done at the runtime level, so that existing applications precompiled with OPTLEVEL 1 do not have to be reprecompiled. However, no SQLDA optimization will take place. This is OPTLEVEL 0 behaviour. 13.3.1 Remote Administration Command The remote administration command LIST HISTORY ALL requires that the client attach to the node first. Without attaching to the node first, the command returns a 1427N (An instance attachment does not exist). 13.4 Call Level Interface Limitations 13.4.1 Limited Support for SQLDriverConnect on Motif Systems In the Call Level Interface Guide and Reference, in Chapter 5, "Functions", the SQLDriverConnect function is described. This function cannot display a dialog window in Motif (or Xwindows) environments in Version 2.1. (Motif is the GUI environment used by UNIX environments.) In Motif environments, the SQLDriverConnect function will ignore the hwindow and fDriverCompletion arguments, and always attempt to connect with the provided connection string. 13.5 WCHARTYPE CONVERT Precompile Option Limitation In the Application Programming Guide, in Chapter 10, "Programming in C and C++", under the heading "The WCHARTYPE Precompiler Option", the WCHARTYPE CONVERT precompile option is described. The WCHARTYPE CONVERT precompile option is not currently supported in programs executing from the DB2 V2.1 Windows client. In these cases the default (WCHARTYPE NOCONVERT) should be used. 13.6 Using DB2 with an XA-Compliant Transaction Manager When using Encina for transaction processing with DB2 through the TM-XA interface, please note the following: o Encina nested transactions are not currently supported by the DB2 XA interface. If possible, avoid their use. If their use cannot be avoided, make sure that SQL work is done in only one member of the Encina transaction family. o If a thread of control is associated with a particular transaction and intends to switch the association to a different transaction, it can do so only after resolving (committing or aborting) the transaction with which it is currently associated. 13.7 DB2 SNMP Subagent The function of the DB2 SNMP Subagent is not currently available on AIX. This function is currently documented in the Problem Determination Guide and in the Installation and Operation Guide. 13.8 Database Director Limitations The Database Director cannot be used to manage DRDA servers. The following 4 configuration parameters are not supported by the Database Director: adsm_password adsm_nodename adsm_owner dft_queryopt The main window of the Database Director has a 'Selected' menu in the menu bar. The 'Open as' item in this menu is disabled because it does not work properly. The identical function is available from the pop-up menus in the window. 13.8.1 Mount Container Under rare circumstances, it is possible that the DBA Utilities will not return from an attempt to mount a file system to a container. The problem only occurs if resources are sufficient for the creation of the file system, but insufficient for mounting. If you experience this problem, it is likely that you have marginal resources remaining in the volume group you are creating the file system on. To solve this problem, exit the Database Director and re-attempt the action using a volume group with more available resource. 13.8.2 Restriction for Visual Explain The following restrictions apply to Visual Explain: o Connectivity between AIX clients and OS/2 servers and OS/2 clients and AIX servers is not supported. You will not be able to invoke the access plan graph. o The client used to display an access plan graph must use the same code page as the database to which it is connected. o If you have created a snapshot in one code page, and are viewing the Access Plan graph for that snapshot in another code page, you will see unreadable characters for the names of database objects such as tables, columns, indices. o You can only display the Access Plan graph overview window once for every Access plan graph. The overview menu item is grayed out and the overview icon is disabled once the overview window is opened the first time. 13.8.2.1 Snapshot Monitor o There is no limit to the number of performance variables that can be displayed on performance graphs on AIX and OS/2. However, the AIX graph can differentiate between 63 performance variables before it starts reusing the same combinations of line colors and point markers. The limit for the OS/2 graph is 56 performance variables. However, if you plan to change either the scale or threshold of a displayed performance variable on a graph on either platform, limit the number of displayed performance variables to 20. This limit becomes evident when using the "Change" menu action from the "Performance variable" pull down. o If you monitor in a code page that differs from the code page of the database, no performance data will be returned for monitored tables and table spaces, unless their names have the same representation in both code pages. If you are using a DBCS version of the DB2 server, you will not be able to put DBCS labels and performance variable names (including the name, group name and description) in the variable profile of the Performance Monitor. Also, the IBM-supplied labels on the Performance Graph View will be displayed in English. The 2 labels are: x axis ... "Local time" y axis ... "Percent of SCALE" 13.8.2.2 Event Analyzer When analyzing a trace and connecting to a remote AIX database, to obtain static text using the -conn and -db options, you need to set the database manager authentication parameter to 'client'. If you have created an event monitor trace in one code page and analyze it in a different code page, you may see unreadable characters for the names of database objects such as packages, tables, and table spaces. 13.9 Online Backup and Load with COPY NO option At the end of a LOAD with the COPY NO option, the tablespace(s) are put into Backup Pending state. Before backing up the loaded tablespaces, all connections to the database must be terminated. This can be performed by issuing FORCE APPLICATION ALL and/or DEACTIVATE DATABASE. If an online backup is in progress, do not start a LOAD with the COPY NO option. ================== 14. Miscellaneous 14.1 DB2 Family Newsletter For the latest information about the DB2 family of products, you may subscribe to the "DB2 Family Newsletter" (English only). Simply send your request in a FAX to 1-905-316-4733, and include the following information: Name Company name Full mailing address Telephone number DB2 products you currently use ------------------------------------------------------------------- * are trademarks or registered trademarks of IBM Corporation. ** are trademarks or registered trademarks of their respective owners. ------------------------------------------------------------------- *********************************************************************** ** ** ** (c) COPYRIGHT INTERNATIONAL BUSINESS MACHINES CORPORATION 1996. ** ** ALL RIGHTS RESERVED. ** ** ** *********************************************************************** 2608 179596 2844 834096583 2379 200 644 7 fixinfo` ******************************************************************** Product Name : Database Server for AIX Version 4 Package Name : U443307 (04.01.0001.3000) Packaged By : Jennifer Gibbs Package Date : April 30, 1996 Build Level : gold960316 ******************************************************************** APAR # Defect Abstract ------- ------ ----------------------------------------------------- IX55458 39807 BACKUP WITHOUT PROMPTING fails when one device fills IX56212 41038 multiple connect deadlock because we need to revoke IX56308 41300 DB2/6000 AS ABENDS when executes SELECT with a lot o IX56408 41486 SQL2054N restoring V1 backup to V2 instance IX56523 41659 Select statement causes signal #11 in sqlisr2 IX56584 41767 SQL0804 is return when updating a table on DB2/MVS IX56633 41468 Missing +F sqlgmf and -ldb2gmf from db2mkrts.args fi IX56664 41477 create db, TEMPSPACE was created as SMS while specif IX56905 42348 DDCS Gateway looping 100% cpu with a certain select IX56928 42275 Activating db will causes exclusive connection to wo IX57031 42454 SQL1035N from connect after activate/backup IX57283 42909 Long field data corrupted when multiple processes wr JR08866 41023 DDCS returns +100 for 2nd fetch when 1st fetch retur JR08871 41093 CAE/WIN 2.1 generates a wrong qry for SQLTables for JR08880 41285 DB2/2 2.1.1 looking for APPC.DLL when Dos DB Req. tr JR08890 41478 logging to remote database using ddcs or appc return JR08891 41385 LOAD traps on OS/2 in sqlu_getfree JR08892 41851 sql1002 from call to sqlecatd in down-level binary a JR08893 40986 db2uexit indicator parameter should be 2 JR08895 41577 Engine crashes when using INTEGER on char column > 5 JR08898 41482 Add additional error checking and error mapping to s JR08901 41741 optimization level 5 can choose bad access plan JR08904 41777 The rexx api sqluimpr does not set the host varible JR08904 42395 fix code for the import API JR08906 41802 OS/2 applications using sqlestrd get -1246 when issu JR08912 41933 the rexx api sqluhops returns SQL2032 JR08915 41976 SQL0804N INSERTING BLOB from ODBC app JR08916 41981 Behaviour of sqlurllf API not correct in DB2/2. JR08917 42016 DB2NAME gets truncated when it is saved to DB2CLI.IN JR08919 42072 SQLUB_PARM_CHECK action for sqlubkup API 'breaks' DB JR08924 42210 Optimization level 5 takes too long JR08925 42239 LOAD REPLACE gets SQL0901N reason -2551 JR08932 42634 SQL30082 rc=0 mapped to SQL1403 for NON v2.1.1 CAE c JR08940 42523 SYS3175 in sqle_database_commit at CONNECT RESET 2608 182306 176890 834096576 2379 200 644 11 lpp.fixinfo` ******************************************************************** Product Name : Database Server for AIX Version 4 Package Name : U443307 (04.01.0001.3000) Packaged By : Jennifer Gibbs Package Date : April 30, 1996 Build Level : gold960316 ******************************************************************** APAR # Defect Abstract ------- ------ ----------------------------------------------------- IX55458 39807 BACKUP WITHOUT PROMPTING fails when one device fills IX56212 41038 multiple connect deadlock because we need to revoke IX56308 41300 DB2/6000 AS ABENDS when executes SELECT with a lot o IX56408 41486 SQL2054N restoring V1 backup to V2 instance IX56523 41659 Select statement causes signal #11 in sqlisr2 IX56584 41767 SQL0804 is return when updating a table on DB2/MVS IX56633 41468 Missing +F sqlgmf and -ldb2gmf from db2mkrts.args fi IX56664 41477 create db, TEMPSPACE was created as SMS while specif IX56905 42348 DDCS Gateway looping 100% cpu with a certain select IX56928 42275 Activating db will causes exclusive connection to wo IX57031 42454 SQL1035N from connect after activate/backup IX57283 42909 Long field data corrupted when multiple processes wr JR08866 41023 DDCS returns +100 for 2nd fetch when 1st fetch retur JR08871 41093 CAE/WIN 2.1 generates a wrong qry for SQLTables for JR08880 41285 DB2/2 2.1.1 looking for APPC.DLL when Dos DB Req. tr JR08890 41478 logging to remote database using ddcs or appc return JR08891 41385 LOAD traps on OS/2 in sqlu_getfree JR08892 41851 sql1002 from call to sqlecatd in down-level binary a JR08893 40986 db2uexit indicator parameter should be 2 JR08895 41577 Engine crashes when using INTEGER on char column > 5 JR08898 41482 Add additional error checking and error mapping to s JR08901 41741 optimization level 5 can choose bad access plan JR08904 41777 The rexx api sqluimpr does not set the host varible JR08904 42395 fix code for the import API JR08906 41802 OS/2 applications using sqlestrd get -1246 when issu JR08912 41933 the rexx api sqluhops returns SQL2032 JR08915 41976 SQL0804N INSERTING BLOB from ODBC app JR08916 41981 Behaviour of sqlurllf API not correct in DB2/2. JR08917 42016 DB2NAME gets truncated when it is saved to DB2CLI.IN JR08919 42072 SQLUB_PARM_CHECK action for sqlubkup API 'breaks' DB JR08924 42210 Optimization level 5 takes too long JR08925 42239 LOAD REPLACE gets SQL0901N reason -2551 JR08932 42634 SQL30082 rc=0 mapped to SQL1403 for NON v2.1.1 CAE c JR08940 42523 SYS3175 in sqle_database_commit at CONNECT RESET 5996 188400 179596 834096565 2379 200 644 7 lpp.doc` ======================================================================== Database Server for AIX Version 4 Service Package U443307 Installation Instructions Special Note to Non-English Customers ------------------------------------- If you are using a locale other than En_US/en_US, a new process has been put into place that affects the way you will install your Service Pack language option updates. We have removed the language updates from our Service Packs and have provided installp images (*.pkg files) instead. This was done in order to decrease the size of our updatep images allowing customers to obtain our Service Packs more easily via our ftp site. In this way, you will only be required to download the language updates that you need rather than all of them. The following chart illustrates what *.pkg files have been provided for each locale. Please ensure that you install these along with the U443307 update. ----------------------------------------- | locale | doc*.pkg | msg*.pkg | ps*.pkg | |--------|----------|----------|---------| | Fr_FR | y | y | y | | fr_FR | n | y | n | | De_DE | y | y | y | | de_DE | n | y | n | | Es_ES | y | y | y | | es_ES | n | y | n | | It_IT | y | y | y | | it_IT | n | y | n | | Ja_JP | y | y | n | | ja_JP | n | y | n | | ko_KR | y | y | y | | zh_CN | y | y | y | | pt_BR | y | y | y | ----------------------------------------- Also Note: If for any reason, you reject U443307, please ensure that you use your previous level of doc, msg, and ps files and NOT the updated versions which have a signature of 4.1.1.2. Installation Considerations --------------------------- U443307 requires the following to already be installed on the machine: 04.01.0001.0002 Initial release of Database Server for AIX Version 4 Use 'lslpp -l product_name' to list the level of Database Server on the machine; product_name should be the name of an installable option such as db2_02_01.db2.rte. For more detailed information regarding this update, please refer to the README located under /usr/lpp/db2_02_01/Readme/ after installation. refers to one of option> En_US, Fr_FR, Ja_JP, etc. Applying the PTF ---------------- For each instance: o Ensure that all applications that are executing in the instance have stopped. o Terminate all command line processor sessions (db2 terminate) o Ensure the instance has stopped (db2stop) o Stop the DB2 license daemon (db2licd end) After each of your instances have stopped, run `/usr/sbin/slibclean` to remove the old versions of the libraries from memory. For more information about slibclean, please refer to the man pages. Install the selected updates using either SMIT or installp. Place the db2_02_01.