Home

DB2 Connect User's Guide

1. Ref Description Sample Value Your Value TCP 7 Connection type must TCPIP TCPIP be TCPIP Table 10 Network Elements at the Host Ref Description Sample Value Your Value TCP 8 Host name MVSHOST TCP 9 Host IP address 9 21 152 100 TCP 10 Service name db2instlc TCP 11 Port number 446 446 TCP 12 LOCATION NAME NEW_YORK3 TCP 13 User ID TCP 14 Password Note a To obtain the host s IP address TCP 9 enter at the host b To obtain the port number TCP 11 look for DSNLQ041 in the DB2 master TSO NETSTAT HOME address space or system log Table 11 Network Elements at the DB2 Connect client and server Ref Description Sample Value Your Value TCP 18 Host name mcook02 TCP 19 IP address 9 21 27 179 TCP 20 Service name db2instlc TCP 21 Port number 446 446 Table 12 DB2 Directory Entries at the DB2 Connect server Ref Description Sample Value Your Value TCP 30 Node name MVSIPNOD TCP 31 Database name nyc3 TCP 32 Database alias mvsipdb1 TCP 33 DCS database name nyc3 3 Complete a copy of the worksheet example for each TCP IP host a Fill in the values to be used for the host name and IP address of the DB2 for z OS host TCP 8 and TCP 9 b Fill in the values to be used for the host name and IP address of the DB2 Connect workstation TCP 18 and TCP 19 c Determine the service name or port number to be used
2. Getting Started with GI13 2047 00 Yes April 2012 DB2 Installation and Administration on Linux and Windows Globalization Guide SC27 3878 00 Yes April 2012 Installing DB2 Servers GC27 3884 00 Yes April 2012 Installing IBM Data GC27 3883 00 No April 2012 Server Clients Message Reference SC27 3879 00 No April 2012 Volume 1 Message Reference SC27 3880 00 No April 2012 Volume 2 Net Search Extender SC27 3895 00 No April 2012 Administration and User s Guide Partitioning and SC27 3882 00 Yes April 2012 Clustering Guide pureXML Guide SC27 3892 00 Yes April 2012 Spatial Extender User s SC27 3894 00 No April 2012 Guide and Reference SQL Procedural SC27 3896 00 Yes April 2012 Languages Application Enablement and Support SQL Reference Volume 1 SC27 3885 00 Yes April 2012 SQL Reference Volume 2 SC27 3886 00 Yes April 2012 Text Search Guide SC27 3888 00 Yes April 2012 Troubleshooting and SC27 3889 00 Yes April 2012 Tuning Database Performance Upgrading to DB2 SC27 3881 00 Yes April 2012 Version 10 1 What s New for DB2 SC27 3890 00 Yes April 2012 Version 10 1 XQuery Reference SC27 3893 00 No April 2012 Table 30 DB2 Connect specific technical information Name Form Number Available in print Last updated DB2 Connect Installing SC27 3861 00 Yes April 2012 and Configuring DB2 Connect Personal Edition DB2 Connect Installing SC27 3862 00 Yes April 2012 and Configuring DB2 Co
3. Datacenter Edition 32 bit and 64 bit e Windows Server 2008 R2 Service Pack 2 or later Standard Edition 64 bit Enterprise Edition 64 bit Datacenter Edition 64 bit Software requirements e A browser is required to view online help Communication requirements e TCP IP is supported and supplied by the operating system Windows 64 bit considerations e 32 bit UDFs and stored procedures are supported Required user accounts for installation of DB2 Connect products Windows If you are installing a DB2 server product on Windows you require the following user accounts e An installation user account and e Optional one or more setup user accounts You can create these accounts during the installation A DB2 Administration Server DAS user account A DB2 instance user account You can also use the LocalSystem account for products other than DB2 Enterprise Server Edition The installation user account is the account of the user performing the installation The installation user account must be defined before running the DB2 Setup wizard The setup user accounts can be defined before installation or you can have the DB2 Setup wizard create them for you All user account names must adhere to your system naming rules and to DB2 User user ID and group naming rules If you use an installation user account that contains non English characters which are not specified in DB2 naming rules the DB2 installati
4. For Version 8 or later the CLI ensures that only the first N rows are returned to the application via the client Cursor Manager JDBC The user can invoke extra query block support for a query through the setMaxRows method Similar to the CLI ODBC enablement DB2 Connect will tag on the OPTIMIZE for N ROWS clause for a DB2 for z OS 6 x server DB2 Connect will also tag the FETCH FIRST N ROWS ONLY clause for a DB2 for z OS 7 1 or later server RFC 1323 Window scaling Window scaling is supported on all Windows Linux and UNIX platforms that support the RFC 1323 extensions for TCP IP You can enable this feature on DB2 for Windows Linux or UNIXusing the DB2 registry variable DB2SORCVBUF To turn window scaling on this registry variable should be set to any value above 64K For example on DB2 for Windows Linux or UNIX you can issue db2set DB2SORCVBUF 65537 The maximum send and receive buffer sizes are dependent on the specific operating system To ensure that buffer sizes configured have been accepted the user can set the database manager configuration parameter diaglevel to 4 informational and check the administration notification log file for messages For window scaling to take effect it must be enabled on both ends of a connection on both the workstation and the host either directly through the operating system TCP IP stack or indirectly through the DB2 database product For instance for DB2 for z OS window scaling c
5. It is recommended to set the SHMMAxX value to be equal to the amount of physical memory on your system However the minimum required on x86 systems is 268435456 256 MB and for 64 bit systems it is 1073741824 1 GB SHMALL is set to 8 GB by default 8388608 KB 8 GB If you have more physical memory than this and it is to be used for the DB2 database system then this parameter increases to approximately 90 of your computer s physical memory For instance if you have a computer system with 16 GB of memory to be used primarily for the DB2 database system then SHMALL should be set to 3774873 90 of 16 GB is 14 4 GB 14 4 GB is then divided by 4 KB which is the base page size The ipcs output has converted SHMALL into kilobytes The kernel requires this value as a number of pages If you are upgrading to DB2 Version 10 1 and you are not using the default SHMALL setting you must increase the SHMALL setting by an additional 4 GB This increase in memory is required by the fast communication manager FCM for additional buffers or channels e The next section covers the amount of semaphores available to the operating system The kernel parameter sem consists of 4 tokens SEMMSL SEMMNS SEMOPM and SEMMNI SEMMNS is the result of SEMMSL multiplied by SEMMNI The database manager requires that the number of arrays SEMMNI be increased as necessary Typically SEMMNI should be twice the maximum number of agents expected on the system multip
6. 1 Did the installation processing complete successfully e Were all the prerequisite software products available e Were the memory and disk space adequate e Was remote client support installed Copyright IBM Corp 1993 2012 161 e Was the installation of the communications software completed without any error conditions 2 For UNIX operating systems was an instance of the product created e As root did you create a user and a group to become the instance owner and SYSADM group 3 If applicable was the license information processed successfully e For UNIX operating systems did you edit the nodelock file and enter the password that IBM supplied 4 Were the IBM mainframe database server and workstation communications configured properly e There are three configurations that must be considered a The IBM mainframe database server configuration identifies the application requester to the server The IBM mainframe server database management system will have system catalog entries that will define the requester in terms of location network protocol and security b The DB2 Connect workstation configuration defines the client population to the server and the IBM mainframe server to the client c The client workstation configuration must have the name of the workstation and the communications protocol defined e Problem analysis for not making an initial connection includes verifying that PU physical unit names are
7. When you have obtained these parameters from your IBM Power Systems server enter your values into the worksheet that follows Table 8 Configuration parameters from IBM Power Systems Item Parameter Example Your value A 1 Local network name SPIFNET A 2 Local adapter address 400009451902 A 4 Hostname SYD2101A A 5 TCP IP port or service X 07F6C4C2 default name A 6 Relational database name NEW_YORK3 For more information refer to the DRDA Considerations section of the DB2 Server for VSE amp VM SQL Reference SC09 2989 Preparing DB2 for z OS for connections from DB2 Connect DB2 Connect gives remote system applications access to data on your DB2 for z OS system Before you begin If you anticipate that DB2 for z OS will participate in a multisite update transaction two phase commit then refer to the topic that discusses enabling multisite updates in the DB2 Connect User s Guide 76 DB2 Connect User s Guide About this task This topic provides instructions for establishing TCP IP network connections between DB2 Connect Server or DB2 Connect client and DB2 for z OS Procedure To prepare DB2 for z OS to receive connection requests from DB2 Connect you need to configure your protocol by e Configuring TCP IP for DB2 for z OS e Configuring DB2 for z OS on page 80 Host databases The term database is used throughout this document to describe
8. Chapter 1 DB2 Connect overview Key Concepts amp ay gale a Client and server connection options Functionality in DB2 features in DB2 Connect product editions Host databases DB2 Connect and SQL sidteinente DB2 Connect administration utilities InfoSphere Federation Server and DB2 Connect DB2 Connect scenarios DB2 Connect client access to ho i databases DB2 Connect server products as D servers DDD FN 7 DB2 Connect and transaction processing monitors 8 Chapter 2 Upgrading to the latest version of DB2 Connect Upgrade essentials for DB2 Connect Pre upgrade tasks for DB2 Connect servers Upgrading DB2 Connect servers Post upgrade tasks for DB2 Connect servers Chapter 3 Installing DB2 Connect server Supported DB2 Connect P languages Displaying the DB2 Setup wizard in your national language Linux and UNIX Language identifiers for running the DB2 Setup wizard in another language Changing the DB2 Connect product interface language Windows Changing the DB2 Connect interlac language Linux and UNIX i amp Conversion of character data Prerequisites i DB2 Connect disk andi memory requirements Java software support for DB2 Connect Preparing to install DB2 Connect for Linux on zSeries Kernel parameters ina and UNIX Modifying kernel parameters for DB2 C nnect HP UX Recommended kernel configuration parameters for DB2 Connect HP UX Modifying kernel parameters for DB2 C
9. IBM DB2 Connect 10 1 DB2 Connect User s Guide all IBM DB2 Connect 10 1 DB2 Connect User s Guide rr A z a DO eee ee ne ee eee ee eee Note Before using this information and the product it supports read the general information under Appendix B Notices on page 179 Edition Notice This document contains proprietary information of IBM It is provided under a license agreement and is protected by copyright law The information contained in this publication does not include any product warranties and any statements provided in this manual should not be interpreted as such You can order IBM publications online or through your local IBM representative e To order publications online go to the IBM Publications Center at http www ibm com shop publications order e To find your local IBM representative go to the IBM Directory of Worldwide Contacts at http www ibm com planetwide To order DB2 publications from DB2 Marketing and Sales in the United States or Canada call 1 800 IBM 4YOU 426 4968 When you send information to IBM you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you Copyright IBM Corporation 1993 2012 US Government Users Restricted Rights Use duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp Contents About this book
10. Before you begin Before you manually configure a TCP IP connection between DB2 Connect and a IBM mainframe database server ensure that e TCP IP is functional on the DB2 Connect server and IBM mainframe system e You have identified the following parameter values Hostname hostname or IP address ip_address Connection Service name svcename or Port number Protocol port_number tcp Target database name target_dbname Local database name local_dcsname Node name node_name Procedure To manually configure TCP IP communications between your DB2 Connect server and an IBM mainframe database 1 Configure TCP IP on the DB2 Connect server Refer to Configuring TCP IP for DB2 for z OS on page 77 2 Catalog the TCP IP node Refer to the CATALOG TCPIP TCPIP4 TCPIP6 NODE command topic in the Command Reference 3 Catalog the IBM mainframe database as a Database Connection Service DCS database Refer to the CATALOG DCS DATABASE command topic in the Command Reference 4 Catalog the IBM mainframe database Refer to the CATALOG DATABASE command topic in the Command Reference 5 Bind utilities and applications to the IBM mainframe database server Refer to Binding database utilities on DB2 Connect on page 93 6 Test the IBM mainframe connection Refer to the CONNECT Type 1 statement topic in the SQL Reference Volume 2 Results Note Due to the characteristics of the T
11. Commercial use You may reproduce distribute and display these publications solely within your enterprise provided that all proprietary notices are preserved You may not make derivative works of these publications or reproduce distribute or display these publications or any portion thereof outside your enterprise without the express consent of IBM 176 DB2 Connect User s Guide Rights Except as expressly granted in this permission no other permissions licenses or rights are granted either express or implied to the publications or any information data software or other intellectual property contained therein IBM reserves the right to withdraw the permissions granted herein whenever in its discretion the use of the publications is detrimental to its interest or as determined by IBM the above instructions are not being properly followed You may not download export or re export this information except in full compliance with all applicable laws and regulations including all United States export laws and regulations IBM MAKES NO GUARANTEE ABOUT THE CONTENT OF THESE PUBLICATIONS THE PUBLICATIONS ARE PROVIDED AS IS AND WITHOUT WARRANTY OF ANY KIND EITHER EXPRESSED OR IMPLIED INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY NON INFRINGEMENT AND FITNESS FOR A PARTICULAR PURPOSE IBM Trademarks IBM the IBM logo and ibm com are trademarks or registered trademarks of International Business Machines Corp
12. DRDA host access configuration 78 communications recovery 90 configuration DB2 Connect Personal Edition 55 DB2 Connect server products 32 host connections 6 TCP IP using CLP 83 configuration parameters agentpri 150 dir_cache 150 max_coordagents details 145 overview 143 MAXDARI_ 150 num_initagents 143 145 num_poolagents 143 145 numdb 150 rqrioblk 150 connection concentrator connection pooling comparison 149 DB2 Connect 150 overview 143 145 worker agents 145 connection pooling connection concentrator comparison 149 overview 143 connections DB2 Connect Enterprise Edition 7 DRDA hosts through communications server 78 hosts directly 6 IBM mainframe directly 6 pooling advantages 145 connection concentrators 145 overview 143 reestablishing DB2 Connect Enterprise Edition 7 direct to host 6 connectivity servers DB2 Connect Enterprise Edition 7 contention system resources 154 conversion character 24 95 host 158 core files problem determination 163 CPUs performance tools 137 CREATE IN COLLECTION NULLID authority 85 184 DB2 Connect User s Guide D D disconnect parameter 102 DAS DB2 administration server see DB2 administration server DAS 96 data representation access DB2 Connect 92 blocking 140 flows DB2 Connect 97 137 sources 99 transferring between hosts and workstations 88 performance 159 rates 137 159 data types CHAR 158 character 158 conversion performance effect 158 floating point host data
13. In addition to setting any connection attributes that you would set for a regular connection set the connection attribute SOQL_ATTR_USE_TRUSTED_CONTEXT to SQL_TRUE with a call to the SQLSetConnectAttr function rc SQLSetConnectAttr conn SQL_ATTR_USE_TRUSTED_CONTEXT SQL_TRUE SQL_IS_INTEGER s 2 Connect to the database as you would for a regular connection for example by calling the SQLConnect function Use the system authorization ID as the user name and its password as the password Be sure to check for errors and warnings especially those listed in table Table 20 Table 20 Errors indicating failure to create a trusted connection SQLCODE SQLSTATE Meaning SQL20360W 01679 The connection could not be established as a trusted connection It was established as a regular connection instead If no errors or warnings tell you differently then the connection is established and is an explicit trusted connection 3 Optional You can verify that an established connection is an explicit trusted connection by checking the value of the connection attribute SQL_ATTR_USE_TRUSTED_CONTEXT using the SQLGetConnectAttr function If it is set to SQL_TRUE the connection is an explicit trusted connection 4 When you are finished using the connection you must be very careful to explicitly disconnect it even if it is in a broken or disconnected state If you do not explicitly disconnect an explicit trusted connection some o
14. The listed levels and forward compatible later versions of the same levels are supported Because there are frequent SDK for Java fixes and updates not all levels and versions have been tested If your database application has problems that are related to the SDK for Java try the next available version of your SDK for Java at the given level Versions of SDK for Java other than IBM SDK are supported only for building and running stand alone Java applications For building and running new Java stored procedures and user defined functions only the IBM SDK for Java that is included with the DB2 Database for Linux UNIX and Windows product is supported For running Java stored procedures and user defined functions that were built by prior DB2 releases refer to Table 1 column Java Stored Procedures and User Defined Functions for details Table 3 DB2 Database for Linux UNIX and Windows supported levels of SDKs for Java Java applications using JDBC driver Java applications Java Stored db2java zip or using JDBC driver Procedures and User db2jcc jar db2jcc4 jar Defined Functions DB2 Graphical Tools AIX 1 4 2 to 7 7 1 4 2 to 7 N A HP UX for 1 4 2 to 6 6 1 4 2 to 6 N A Itanium based systems Linux on POWER 1 4 2 to 7 4 7A 1 4 2 to 7 N A Linux on x86 1 4 2 to 7 pee 1 4 2 to 7 5 to7 Linux on AMD64 and 1 4 2 to 7 4 va 1 4 2 to 7 N A Intel EM64T processors Linux on zSeries 1 4 2 to 7 Te 1
15. Where DBALIAS USERID and PASSWORD apply to the IBM mainframe database server ddcsmvs 1st is the bind list file for z OS and path represents the location of the bind list file For example drive sql1ib bnd applies to all Windows operating systems and INSTHOME sq11ib bnd applies to all Linux and UNIX operating systems where drive represents the logical drive where DB2 Connect was installed and INSTHOME represents the home directory of the DB2 Connect instance You can use the grant option of the bind command to grant EXECUTE privilege to PUBLIC or to a specified user name or group ID If you do not use the grant option of the bind command you must GRANT EXECUTE RUN individually To find out the package names for the bind files enter the following command ddcspkgn bindfile lst For example ddcspkgn ddcsmvs 1st might yield the following output Bind File Package Name f sqllib bnd db2ajgrt bnd SQLAB6D3 To determine these values for DB2 Connect execute the ddespkgn utility for example ddcspkgn ddcsmvs 1st Optionally this utility can be used to determine the package name of individual bind files for example ddcspkgn bindfile bnd Note a Using the bind option sqlerror continue is required however this option is automatically specified for you when you bind applications using the DB2 tools or the Command Line Processor CLP Specifying this option turns bind errors into warnings so that binding a file conta
16. all input and output character data is converted if the two machines have different code pages or CCSIDs For example if DB2 Connect is used to access data the following happens 1 DB2 Connect sends an SQL statement and input data to System z 2 DB2 for z OS converts the SQL statement and data to the host server s code page and then processes the data 3 DB2 for z OS sends the result back to the DB2 Connect server 4 DB2 Connect converts the result to the code page of the user s environment For bidirectional languages a number of special BiDi CCSIDS have been defined by IBM and are supported by DB2 Connect If the bidirectional attributes of the database server are different from those of the client you can use these special CCSIDS to manage the difference Refer to the supported territory codes and code pages topic for the supported conversions between code pages on the DB2 Connect and CCSIDs on the host or System i server System i and mainframe support for DB2 Connect Before you access DB2 data on System z or System i data servers by using DB2 Connect products ensure that the data server meets requirements DB2 Connect supports connectivity to the following mainframe and System i servers Chapter 5 Administering 95 Table 13 Supported mainframe and IBM i data servers Version Recommended maintenance levels DB2 for z OS Version 8 Version 9 and Version 10 See website for IBM z OS Consolidated Servic
17. and allows the installer to automatically find the installation images without prompting 3 Enter the db2setup command from the directory where the product image resides to start the DB2 Setup wizard After a few moments the IBM DB2 Setup Launchpad opens For multiple CD installations issue the db2setup command outside the mounted CD location with either a relative or absolute path name to ensure the DB2 Connect product CD can be unmounted as required From this window you can view the installation prerequisites and the release notes or you can proceed directly to the installation 4 Once you have initiated the installation proceed through the DB2 Setup wizard installation panels and make your selections Installation help is available to guide you through the DB2 Setup wizard Click Help to invoke the online help You can click Cancel at any time to exit the installation DB2 files will only be copied to your system once you have clicked Finish on the last DB2 Setup wizard installation panel Once completed the DB2 Connect Personal Edition product is installed using the opt 1IBM db2 Version 10 1 default installation path If you are installing on a system where this directory is already being used the DB2 Connect product installation path will have _xx added to it where xx are digits starting at 01 and increasing depending on how many DB2 copies you have installed You can also specify your own DB2 database product installation path
18. max_connections higher than the number of coordinating agents max_coordagents Connection pooling saves the cost of establishing a connection when one is no longer needed by a terminating application In other words one application has to disconnect before another one can reuse a pooled connection Alternatively the connection concentrator allows DB2 Connect to make a connection available to an application as soon as another application has finished a transaction and does not require that other application to disconnect In essence a database server connection and its associated host and DB2 Connect resources are used by an application only while it has an active transaction As soon as the transaction completes the connection and associated resources are available for use by any other application that needs to have a transaction executed In previous versions of DB2 Connect every active application had an Engine Dispatchable Unit EDU which managed the database connection as well as any application requests This EDU was typically referred to as the coordinator agent Each coordinator agent tracked the state or context of the application and EDU Each EDU takes a significant amount of memory when the number of connections increases and context switching between agents results in additional processing usage In the architecture mentioned previously there is a one to one relationship between connections and EDUs The connection concentr
19. types 106 user groups 69 SELECT statement application design 140 FOR FETCH ONLY on 140 updatable 140 SERVER authentication type DB2 Connect 134 SERVER_ENCRYPT authentication type DB2 Connect 134 SERVER_ENCRYPT_AES authentication type 134 SHOW DETAIL monitor option 122 SOCKS nodes mandatory environment variables 101 Solaris operating systems installation requirements DB2 Connect server products 46 61 installing DB2 Connect server products 44 installing DB2 Connect Personal Edition 58 modifying kernel parameters 32 mounting CDs or DVDs 47 61 SQL dynamic 140 static 140 SQL statements COMMIT 140 DB2 Connect 5 109 DESCRIBE 140 EXECUTE IMMEDIATE 140 FOR FETCH ONLY clause of SELECT 140 help displaying 172 PREPARE 140 ROLLBACK 140 SELECT 140 SQL_ATTR_ TRUSTED_CONTEXT_PASSWORD switching users on a trusted connection through CEI 131 TRUSTED_CONTEXT_USERID switching users on a trusted connection through CLI 131 USE_TRUSTED_CONTEXT creating trusted connection through CLI 130 SQL0965 error code 165 SQL0969 error code 165 SQL30020 error code 165 188 DB2 Connect User s Guide SQL30060 error code 165 SQL30061 error code 165 SQL30073 error code 165 SQL30081N error code 165 SQL30082 error code 165 SQL5043N error code 165 SQLCODE mapping 113 114 mapping file 114 SQLDA allocation size 140 SQLSTATE class codes 114 static SQL performance 140 processing effects 5 109 symbolic destination names case sensitivity 101 sync po
20. websites The materials at those websites are not part of the materials for this IBM product and use of those websites is at your own risk IBM may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you Licensees of this program who wish to have information about it for the purpose of enabling i the exchange of information between independently created programs and other programs including this one and ii the mutual use of the information that has been exchanged should contact IBM Canada Limited U59 3600 3600 Steeles Avenue East Markham Ontario L3R 9Z7 CANADA Such information may be available subject to appropriate terms and conditions including in some cases payment of a fee The licensed program described in this document and all licensed material available for it are provided by IBM under terms of the IBM Customer Agreement IBM International Program License Agreement or any equivalent agreement between us Any performance data contained herein was determined in a controlled environment Therefore the results obtained in other operating environments may vary significantly Some measurements may have been made on development level systems and there is no guarantee that these measurements will be the same on generally available systems Furthermore some measurements may have been estimated through extrapolation Actual results may vary Users of th
21. 4 Configuring 81 DB2 Sysplex exploitation In a typical scenario a DB2 Connect server server A would be in conversation with a Sysplex containing two DB2 for z OS servers servers B and C Sysplex server B Sysplex server C HOST_NAME MVSHOST HOST_NAME MVSHOST1 Suppose that in this scenario an application now issues db2 connect to aliasb user xxxxxxx using XXXXXXXX The connection to database MVSHOST is established Because Sysplex exploitation is enabled both for the DB2 Connect server and the DCS directory entry DB2 for z OS identifies the network addresses to DB2 Connect for each Sysplex participant MVSHOST and MVSHOST1 DRDA4 protocols and message flows are used to return this information Once an initial connection has been made the returned list of addresses is cached at the DB2 Connect workstation Once the initial CONNECT is issued for a TCP IP node then the IP addresses are returned Priority information used for load balancing and fault tolerance The list of addresses provided by DB2 for z OS also includes priority information including the number of connections for each network address The list is refreshed whenever a new connection is made by DB2 Connect This additional information is used for load balancing purposes as well as for fault tolerance Cached Address List used by DB2 Connect If the database connection to ALIASB fails then an error message SQL30081N is issued and the connection will be droppe
22. 4 2 to 7 N A Solaris operating 1 4 2 to 7 7 1 4 2 to 7 N A system Windows on x86 1 4 2 to 7 7 1 4 2 to 7 5to7 Chapter 3 Installing DB2 Connect server 27 Table 3 DB2 Database for Linux UNIX and Windows supported levels of SDKs for Java continued Java applications using JDBC driver Java applications Java Stored db2java zip or using JDBC driver Procedures and User db2jcc jar db2jcc4 jar Defined Functions DB2 Graphical Tools Windows on x64 for 1 4 2 to 7 7 1 4 2 to 7 5 to7 AMD64 and Intel EM64T processors Note if The same levels of the SDK for Java that are available from Hewlett Packard are supported for building and running stand alone client applications that run under the IBM Data Server Driver for JDBC and SQL The same levels of the SDK for Java that are available from Oracle are supported for building and running stand alone applications with the IBM Data Server Driver for JDBC and SQL However if you set the IBM Data Server Driver for JDBC and SQL property securityMechanism for a type of security that uses encryption the SDK for Java must support the type of encryption that you use For example the SDK for Java that you use might support 256 bit AES strong encryption but not 56 bit DES weak encryption You can specify the encryption algorithm by setting the IBM Data Server Driver for JDBC and SQLJ property encryptionAlgorithm To use 256 bit AES encryption set encryptionAlgorithm to 2 W
23. DB2 Connect license policy using the db2licm command To set your license policy issue the db21 icm command with the command parameters that are appropriate for the license Before you begin Before you set your license policy you need to know the product identifier To list the product identifier information enter the following command 68 DB2 Connect User s Guide db2licm 1 The product identifier is listed in the Product Identifier field About this task For DB2 Connect Enterprise Edition the license policy controls and monitors the number of users that can connect simultaneously to a DB2 Connect server For InfoSphere Replication Server or InfoSphere Federation Server the license policy controls and monitors the number of connectors to a data source that is not a part of DB2 Procedure To set your license policy Perform one of the following depending on the type of licenses that you purchased e If you purchased a InfoSphere Replication Server or InfoSphere Federation Server Concurrent Connector policy enter the following command db2licm c isrs concurrent or db2licm c isfs concurrent e If you purchased a DB2 Connect server Concurrent User policy enter the following command db2licm p db2consv concurrent Post installation tasks Adding your user ID to the DB2ADMNS and DB2USERS user groups Windows After successfully completing a DB2 installation you now have to add users to the DB2ADMNS or the D
24. Distributed Relational Database Architecture DRDA 97 Distributed Relational Database Architecture DRDA data access 97 DB2 Connect 97 overview 97 distributed requests overview 99 distributed units of work multisite updates 110 overview 97 servers supported 110 two phase commit 110 documentation overview 169 PDF files 169 printed 169 terms and conditions of use 176 DVDs mounting AIX 37 HP UX 41 Linux 44 58 Solaris 47 61 dynamic SQL performance techniques 140 processing effects 5 109 E error messages DB2 Connect 165 errors troubleshooting 161 examples connection concentrators 145 XA concentrators 145 EXECUTE IMMEDIATE statement application design 140 export utility transferring data between hosts and workstations 88 extra query blocks EXTRA BLOCKS SRV parameter 155 overview 155 Index 185 F federated databases distributed requests 99 fix packs applying 70 floating point data types conversion 158 FOR FETCH ONLY clause SELECT statement 140 FORCE command 122 Formatted Data Object Content Architecture FDOCA 97 G GET SNAPSHOT command overview 120 H hardware network performance 159 help SQL statements 172 host databases accessing using DB2 Connect Personal Edition 6 configuring TCP IP 83 connectivity high availability 157 load balancing 157 HP UX installing DB2 Connect servers 38 40 kernel configuration parameters modifying 29 recommended values 30 mounting media 41 IBM Data Server
25. Driver for JDBC and SQLJ levels for DB2 Connect versions 26 IBM i DB2 Connect 95 import utility transferring data between host and workstation 88 InfoSphere Federation Server overview 6 installation DB2 Connect for Linux on zSeries 28 DB2 Connect Personal Edition 55 65 DB2 Connect server products 32 DB2 Connect user accounts Windows 50 installing fix packs 70 INTEGER data type host data conversion 158 interface languages changing UNIX 24 Windows 23 overview 21 INTERRUPT_ENABLED disconnect parameter 102 186 DB2 Connect User s Guide J Java DB2 Connect product support 26 JDBC drivers details 26 K Kerberos authentication protocol DB2 Connect 134 OS 390 135 z OS 135 kernel configuration parameters HP UX db2osconf command 30 modifying 29 recommended 30 Linux 30 Solaris 32 L LANG environment variable setting 21 24 languages bidirectional support 24 95 DB2 Connect interface 21 DB2 interface 23 DB2 Setup wizard for language identifiers 22 license policies setting db2licm command 68 licenses registering db2licm command 68 84 Linux installing DB2 Connect on zSeries 28 DB2 Connect Personal Edition 55 DB2 Connect server products 41 43 modifying kernel parameters 30 mounting CDs 44 58 DVDs _ 44 58 removing DB2 Connect root 73 uninstalling DB2 Connect root 73 LIST DCS APPLICATIONS command output 122 LOCALDATE parameter 102 locales DB2 Connect interface languages 21 M
26. Edition on Linux using the DB2 Setup wizard 1 Change to the directory where the DVD is mounted cd db2dvd where db2dvd represents mount point of the DVD 2 If you downloaded the DB2 Connect product image you must decompress and untar the product file a Decompress the product file gzip d product tar gz where product is the name of the database product that you downloaded b Untar the product file tar xvf product tar c Change directory cd product disk1 Note If you downloaded a National Language Package untar it into the same directory This will create the subdirectories for example nlpack disk2 in the same directory and allows the installer to automatically find the installation images without prompting 3 Enter the db2setup command from the directory where the product image resides to start the DB2 Setup wizard After a few moments the IBM DB2 Setup Launchpad opens For multiple CD installations issue the db2setup command outside the mounted CD location with either a relative or absolute path name to ensure the DB2 Connect product CD can be unmounted as required From this window you can view the installation prerequisites and the release notes or you can proceed directly to the installation 4 Once you have initiated the installation proceed through the DB2 Setup wizard installation panels and make your selections Installation help is available to guide you through the DB2 Setup wizard Click Help to
27. Fix Pack 4 or later to connect to DB2 for i V7R1 See website for DB2 Server for VSE amp VM http www ibm com software data db2 vse vm Understanding the Administration Server The DB2 Administration Server DAS responds to requests from the DB2 Administration Tools The DB2 Administration Tools for example allow you to start stop and set database manager configuration parameters for servers The Administration Server is used to help users catalog databases on a client The DAS is available on all supported Linux Windows and UNIX operating systems as well as the System z z OS only operating systems An Administration Server must reside on each server that you want to administer and detect The Administration Server is automatically created and started for you The setup program creates the Administration Server on the instance owning machine and automatically starts it at boot time By default the DAS instance is DB2AS which is the default user ID that is created using the DB2 Setup wizard Important The DB2 Administration Server DAS has been deprecated in Version 9 7 and might be removed in a future release The DAS is not supported in DB2 pureScale environments Use software programs that use the Secure Shell protocol for remote administration For more information see DB2 administration server DAS has been deprecated at Distributed Relational Database Architecture Distributed Relational Database Architectur
28. German language a manual update will install German as well as update the existing Information Center features and languages However a manual update requires you to manually stop update and restart the Information Center The Information Center is unavailable during the entire update process In the automatic update process the Information Center incurs an outage to restart the Information Center after the update only This topic details the process for automatic updates For manual update instructions see the Manually updating the DB2 Information Center installed on your computer or intranet server topic Procedure To automatically update the DB2 Information Center installed on your computer or intranet server 1 On Linux operating systems a Navigate to the path where the Information Center is installed By default the DB2 Information Center is installed in the opt ibm db2ic V10 1 directory Navigate from the installation directory to the doc bin directory c Run the update ic script update ic 2 On Windows operating systems a Open a command window b Navigate to the path where the Information Center is installed By default the DB2 Information Center is installed in the lt Program Files gt IBM DB2 Information Center Version 10 1 directory where lt Program Files gt represents the location of the Program Files directory c Navigate from the installation directory to the doc bin directory d Run the upd
29. PHSS_37202 e PHKL 41481 e PHKL 42035 e PHKL_42335 e PHKL_41588 HP UX 11i v4 11 31 Software requirements e A browser is required to view online help e For details regarding known HP UX issues see www ibm com support docview wss amp uid swg21257602 Communication requirements You can use TCP IP e For TCP IP connectivity no additional software is required Note DB2 products installed on the HP UX operating system support long host names The length has been extended to 255 bytes in any combination of characters or digits To enable long host name support complete the following tasks 1 Turn on the kernel tunable parameter expanded_node_host_name Kctune expanded_node_host_name 1 2 Compile applications requiring long host name support with the D_HPUX_API_LEVEL 20040821 option Mounting CDs or DVDs for DB2 Connect HP UX To mount your DB2 database product CD or DVD on HP UX operating systems issue the mount command 40 DB2 Connect User s Guide Before you begin Depending on your system configuration you might need root user authority to mount discs Procedure To mount your DB2 database product CD or DVD on HP UX 1 Insert the CD or DVD in the drive 2 If necessary define a new directory as the mount point for the CD or DVD drive Define cdrom as the mount point using the mkdir cdrom command 3 If necessary identify the drive device file using the ioscan fnC disk command This co
30. Results National Language Packs can also be installed by running the db2setup command from the directory where the National Language Pack resides after a DB2 Connect product has been installed The installation logs db2setup 1og and db2setup err will be located by default in the tmp directory You can specify the location of the log files If you want your DB2 database product to have access to DB2 documentation either on your local computer or on another computer on your network then you must install the DB2 Information Center The DB2 Information Center contains documentation for the DB2 database and DB2 database related products See Installing the DB2 Information Center using the DB2 Setup wizard UNIX topic in Installing DB2 Servers Installation requirements for DB2 Connect Personal Edition Linux Before you install DB2 Connect Personal Edition on Linux operating systems ensure that the system you choose meets the necessary operating system hardware software and communications requirements To install DB2 Connect Personal Edition the following requirements must be met Chapter 3 Installing DB2 Connect server 57 Hardware requirements Your processor must be one of e x86 Intel Pentium Intel Xeon and AMD Athlon e x64 Intel EM64T and AMD64 Distribution requirements For the latest information about the supported Linux distributions point your browser to www ibm com db2 linux validate You might be r
31. Table 22 Errors indicating failure to set a password when switching users SQLCODE Meaning CLIO198E The attribute SQL_ATTR_TRUSTED_CONTEXT_USERID has not yet been set 3 Proceed as with a regular connection If you are using an XA transaction manager the user switch is attempted as part of the next request otherwise the user switch is attempted just before initiating the next function call that accesses the database SQLExecDirect for example In either case in addition 132 DB2 Connect User s Guide to the errors and warnings you would normally check for be sure to check for the errors listed in Table 23 The errors in Table 23 indicate that the user switch failed Table 23 Errors indicating failure to switch users SQLCODE Meaning SQL1046N The trusted context that allowed this trusted SQL30082N The password provided is not correct for the connection is not configured to allow switching to the authorization ID you are trying to switch to You will not be able to switch to that authorization ID until the trusted context is changed authorization ID you are switching to SQLO969N with a native error of 20361 There is some database level constraint that prevent you from switching to the user If the user switch fails the connection will be in an unconnected state until you successfully switch to another user You can switch users on a trusted connection in an unconnected state b
32. When db2schex completes you can proceed with the installation of your DB2 database product or if you have already installed DB2 database products or created databases you have to manually register the node and catalog the databases For more information see the Enabling LDAP support after DB2 installation is complete topic Non Administrator installation of DB2 Connect Windows There are some additional considerations when you install DB2 Connect on Windows operating systems using a non Administrator user account For a non Administrator s installation the account you are logged on as must belong to Power Users group Some information about DB2 Connect that must appear in the registry must be entered in the HKEY_CURRENT_USER folder in the registry Although many items will be stored under the HKEY_LOCAL_MACHINE folder in the registry for non Administrator installations of DB2 Connect the environment settings must be changed in HKEY_CURRENT_USER A member of the Windows Administrators group must configure the Windows elevated privileges settings to allow a non Administrator user account to perform an installation For example on a 64 bit operating system you must manually grant full permission on HKLM Software Wow6432Node before a 32 bit DB2 Connect Personal Edition product can be successfully installed Note If a non Administrator user account is going to do the product installation then the VS2010 runtime library must be inst
33. Windows operating systems provide a useful tool for monitoring the performance of your DB2 applications The Performance Monitor which is one of the Windows administrative tools displays a graphical representation of system performance You can choose a variety of system database and communications related items to monitor and map them together in a graphical representation For example the reports available through the GET SNAPSHOT FOR ALL DCS DATABASES or GET SNAPSHOT FOR ALL DCS APPLICATIONS commands can be graphed in real time using the monitor and compared directly with values such as CPU usage You can directly compare the effects of different settings on database or communications performance You can save your specialized configurations of settings in PMC files that you can later retrieve For example in the following figure several DB2 measures are being graphed against CPU usage The collection of values being charted was saved in the file db2chart pmc You can save as many PMC files as you like each reflecting a different cross section of system performance Copyright IBM Corp 1993 2012 119 lolx is File Action Yiew Favorites Window Help 18 x gt amg C Console Root System Monitor Ez g Performance Logs and Alerts 100 90 80 70 60 so HUNIMA HIA a 40 30 0 Minimum Duration fo Avg Disk Queue Length Ph
34. a relational database management system RDBMS Other systems with which DB2 Connect communicates might use the term database to describe a slightly different concept The DB2 Connect term database can also refer to System z DB2 for z OS A DB2 for z OS subsystem identified by its LOCATION NAME Use the z OS display ddf command to get the DB2 server location name domain name IP address and port A DB2 for z OS location is the unique name of a database server An application uses the location name to access a DB2 for z OS subsystem or a DB2 for z OS data sharing group A data sharing group enables applications on different DB2 subsystems to read from and write to the same data concurrently The application uses a DB2 data sharing group network address to access a DB2 data sharing location The accessed DB2 subsystem is transparent to the application Since DB2 for z OS supports multiple databases at the same DB2 location the location name is analogous to a Linux UNIX and Windows database alias name A database alias can be used to override the location or location alias name when accessing a location A location alias is another name for a location It is used to control which subsystems in a data sharing group are accessed by an application LOCATION NAME is also defined in the Boot Strap Data Set BSDS as well as the DSNLO04I message LOCATION location which is written when the Distributed Data Facility DDF is started LOCATION NA
35. active however is 1999 Each workstation client can stay connected when it is inactive its thread is placed on an inactive chain at each commit 154 DB2 Connect User s Guide The DSNZPARM parameters CMTSTAT CONDBAT and MAXDBAT affect thread processing For best performance set CMTSTAT to INACTIVE adjust CONDBAT to the maximum number of connected DBATs that provide good performance and MAXDBAT to the maximum acceptable number of active DBATs Increasing DB2 Connect data transfer rates In addition to blocking of rows for a query result set DB2 for z OS can also return multiple such query blocks in response to an OPEN or FETCH request to a remote client such as DB2 Connect Instead of the client repeatedly sending requests to the DB2 for z OS server requesting one block of row data at a time the client can now optionally request that the server send back some number of query blocks in addition to the one that it will always send back Such additional query blocks are called extra query blocks As such this new feature allows the client to minimize the number of network line turnarounds which constitute a major cost to network performance The decrease in the number of requests sent by the client to the server for query blocks translates into a significant performance boost This performance boost is due to the fact that switching between a send and receive is an expensive operation performance wise DB2 Connect can now exploit th
36. also have the appropriate National Language Packages Ensure that asynchronous I O has been enabled it must be enabled before your DB2 Connect server product can be successfully installed To locate DB2 database products already installed on your system use the db21s command Refer to the Listing DB2 products installed on your system Linux and UNIX topic in Installing DB2 Servers The DB2 Setup wizard is a graphical installer You must have X windows software capable of rendering a graphical user interface for the DB2 Setup wizard to run on your machine Ensure that the X windows server is running Ensure that you have properly exported your display For example export DISPLAY 9 26 163 144 0 If security software such as Lightweight Directory Access Protocol LDAP is used in your environment you must manually create required DB2 users before you start the DB2 Setup wizard Note Network Information Services NIS and Network Information Services Plus NIS features are deprecated starting with DB2 Version 9 1 Fix Pack 2 Chapter 3 Installing DB2 Connect server 33 Support for these features might be removed in a future release Lightweight Directory Access Protocol LDAP is the recommended solution for centralized user management services About this task The DB2 Installer program is a Java based installation tool that automates the installation and configuration of any DB2 database product If you prefer not to use
37. and configure the IBM data server client Use this workstation to test 11 connectivity from the IBM data server client to IBM mainframe database servers as well as to test applications that use this connectivity Use the CLP commands to connect the client to the IBM mainframe system through DB2 Connect 12 Install a IBM data server client on all end user workstations that will use applications that connect to IBM mainframe database servers 13 You are now ready to use DB2 Connect with all your applications Workstations that will be used for application development should have the IBM data server client installed 14 If you want to use your workstation to administer DB2 for z OS or DB2 Database for Linux UNIX and Windows install the IBM data server client Installing a DB2 Connect server product AIX To define your installation preferences and to install a DB2 Connect product on AIX use the DB2 Setup wizard Before you begin Before you begin your installation You can install DB2 Connect using either root or non root user authority Ensure that your system meets Disk and memory requirements Hardware and software requirements Refer to Installation requirements for DB2 Connect server products AIX on page 35 The DB2 database product DVD must be mounted on your system The DB2 Connect product image must be available If you are installing a non English version of a DB2 Connect product you must
38. applications in an IBM WebSphere MQ formerly known as IBM MOSeries environment WebSphere MQ can act as an XA compliant transaction manager coordinating any distributed two phase commit transactions When WebSphere MQ is acting as a transaction manager in this way and the data sources are from the DB2 family of products there are several configuration requirements Most of the configuration requirements in such a transaction manager environment are already documented elsewhere For example you must set the DB2 configuration parameter tp_mon_name to MQ at the DB2 runtime client However there is a configuration requirement that was missing The requirement is specific to DB2 Connect when connecting to data sources that are DB2 for z OS servers when using WebSphere MQ to coordinate distributed transactions involving DB2 for z OS and IBM DB2 for IBM i servers the DB2 Connect connection concentrator feature must be enabled at the gateway The connection concentrator is enabled when the value of the max_connections configuration parameter is greater than the value of the max_coordagents configuration parameter If you do not enable the connection concentrator unexpected transaction behavior will result If you are using the WebSphere MQ Transaction Manager and DB2 for z OS server the application must set the special registers for each local or global transaction DB2 Connect server tuning Various parameters in the database manag
39. client reroute with IBM Data Server feature redirects client applications from a failed server to an alternate server so the applications can continue their work with minimal interruption Seamless automatic client reroute for DB2 for z OS Sysplex is on by default and is recommended when WLB is enabled With this support applications that access DB2 for z OS Sysplex should use seamless automatic client reroute capabilities provided by the client and are not required to go through a DB2 Connect server For more information about this feature see the topic about automatic client reroute client side in the DB2 Information Center Outside of a DB2 Connect high availability environment the database being accessed is typically synchronized between the original DB2 server and the alternate DB2 server through one of various means such as High availability disaster recovery HADR or IBM PowerHA SystemMirror for AIX However in the case of the DB2 Connect server because there is no requirement for the synchronization of local databases you only need to ensure that both the original and alternate DB2 Connect servers have the target IBM mainframe database catalogued in such a way that it is accessible using an identical database alias Note In a DB2 Connect server environment an alternate DB2 Connect server can be specified to enable automatic rerouting between a client and the DB2 Connect server For rerouting to occur between the DB2 Connect cli
40. command You cannot manually install a DB2 database product using the operating system s native installation utility pkgadd Any existing scripts containing this native installation utility that you use to interface and query with DB2 installations will need to change Procedure To install a DB2 Connect server product such as DB2 Connect Enterprise Edition on the Solaris operating system using the DB2 Setup wizard 1 Change to the directory where the DVD is mounted cd db2dvd where db2dvd represents mount point of the DVD 2 If you downloaded the DB2 Connect product image you must decompress and untar the product file a Decompress the product file gzip d product tar gz where product is the name of the database product that you downloaded b Untar the product file tar xvf product tar c Change directory cd product diskl Note If you downloaded a National Language Package untar it into the same directory This will create the subdirectories for example nlpack disk2 in the same directory and allows the installer to automatically find the installation images without prompting 3 Enter the db2setup command from the directory where the product image resides to start the DB2 Setup wizard After a few moments the IBM DB2 Setup Launchpad opens For multiple CD installations issue the db2setup command outside the mounted CD location with either a relative or absolute path name to ensure the DB2 Connect product CD c
41. communication products On Linux and UNIX operating systems 2 GB of free space in the tmp directory is recommended Memory requirements Memory requirements are affected by the size and complexity of your database system the extent of database activity and the number of clients accessing your system At a minimum a DB2 database system requires 256 MB of RAM For a system running just a DB2 product and the DB2 GUI tools a minimum of 512 MB of RAM is required However 1 GB of RAM is recommended for improved performance These requirements do not include any additional memory requirements for other software that is running on your system For IBM data server client support these memory requirements are for a base of five concurrent client connections For every additional five client connections an additional 16 MB of RAM is required For DB2 server products the self tuning memory manager STMM simplifies the task of memory configuration by automatically setting values for several memory 1 DB2 products that run on HP UX Version 11i for Itanium based systems require a minimum of 512 MB of RAM Chapter 3 Installing DB2 Connect server 25 configuration parameters When enabled the memory tuner dynamically distributes available memory resources among several memory consumers including sort the package cache the lock list and buffer pools Paging space requirements DB2 requires paging also called swap to be enabled This con
42. conversion 158 INTEGER host data conversion 158 packed decimal 158 VARCHAR overview 158 zoned decimal 158 DATA_ENCRYPT authentication type 134 Database Connection Services DCS directory updating entries 100 values 102 database directories Database Connection Services DCS 100 multiple entries 107 node 100 updating 100 database requests grouping for performance 140 database system monitor overview 5 remote clients 119 databases aliases directory customization worksheet 106 system database directory 101 grouping requests 140 host 4 77 names DCS directory 102 directory customization worksheet 106 system database directory 101 performance tools 137 tuning 152 dates time zone support 102 DB2 administration server DAS overview 96 DB2 Connect 2 administration utilities 5 configuring IBM mainframe 112 IBM Power Systems 112 System z 112 connection concentrators 150 connectivity server scenarios 6 DB2 for VSE amp VM 80 DB2 Connect continued disk and memory requirements 25 Enterprise Edition connectivity servers 7 transaction processing monitors 8 XA compliant transaction managers 112 host support 6 92 95 IBM i connections 75 installing non Administrator 54 67 mainframe support 6 moving data 88 overview 1 92 Personal Edition configuring 55 installing Linux 55 57 installing overview 55 installing Solaris 58 installing Windows 62 64 server products configuring 32 installing AIX 33 35 installi
43. database products provide comprehensive support for multisite updates For example a banking transaction that involves the transfer of money from one account to another in a different database server In such a transaction it is critical that updates which implement debit operations on one account do not get committed unless updates required to process credits to the other account are committed as well The multisite update considerations apply when data representing these accounts is managed by two different database servers The multi site update support provided by DB2 database products is available for applications developed using regular SQL as well as for applications that use transaction processing monitors TP monitors that implement the X Open XA interface specification Examples of such TP monitors products include IBM TxSeries CICS IBM Message and Queuing Series IBM Component Broker Series IBM San Francisco Project as well as Microsoft Transaction Server MTS BEA Tuxedo and several others There are different setup requirements depending on whether native SQL multisite update or TP monitor multisite update is used XA connections using IBM Data Server Driver Package against a z OS server are supported However XA connections against a System i server are not supported For details see the topic about IBM data server driver restrictions 110 DB2 Connect User s Guide Both the native SQL and TP monitor multisite update prog
44. e An authorization policy for DB2 as a service in the Active Directory that is shared on a network and e A trust relationship between Kerberos Key Distribution Centers KDCs In the simplest scenario there is at least one KDC trust relationship to configure that is the one between the KDC controlling the client workstation and the IBM Power Systems or System z OS 390 Version 2 Release 10 or z OS Version 1 Release 2 provides Kerberos ticket processing through its RACF facility which allows the host to act as an UNIX KDC DB2 Connect provides as usual the router functionality in the 3 tier setting It does not assume any role in authentication when Kerberos security is used Instead it merely passes the client s security token to IBM DB2 for IBM i or to DB2 for z OS There is no need for the DB2 Connect gateway to be a member of the client or the host s Kerberos realm Downlevel compatibility Minimum requirements for Kerberos support in DB2 database products IBM data server client Version 8 DB2 Connect Version 8 DB2 for z OS Version 7 Chapter 8 Security 135 Authentication types supported with DB2 Connect server Certain combinations of authentication and security settings are supported with DB2 Connect Authentication types for TCP IP connections The TCP IP communication protocol does not support Authentication options at the network protocol layer The authentication type determines where authentication takes
45. encounters lock timeout or deadlock as a result of another branch within the same global transaction SQLCODE mapping Different IBM relational database products do not always produce the same SQLCODESs for similar errors Even when the SQLCODE is the same it might be accompanied by tokens that are specified differently The token list is passed in the SQLERRMC field of the SQLCA By default DB2 Connect maps SQLCODEs and tokens from each IBM mainframe database server to the appropriate DB2 SQLCODEs If you want to turn off SQLCODE mapping specify NOMAP in the parameter string of the DCS directory If you port an application directly from IBM mainframe database server such as DB2 for z OS you might want to turn off SQLCODE mapping This would let you use the application without changing the SQLCODEs that it references Turning off SQLCODE mapping If you port an application directly from a IBM mainframe database server such as DB2 for z OS you might want to turn off SQLCODE mapping This would let you use the application without changing the SQLCODEs that it references About this task If you want to turn off SQLCODE mapping specify NOMAP in the parameter string of the DCS directory If you port an application directly from a IBM mainframe database server such as DB2 for z OS you might want to turn off SQLCODE mapping This would let you use the application without changing the SQLCODEs that it references Chapter 5 Admini
46. gt gt LOCALDATE YYMMDD 0x0100 DB2 Connect and SQL statements DB2 Connect forwards SQL statements submitted by application programs to IBM mainframe database servers DB2 Connect can forward almost any valid SQL statement as well as the supported DB2 APIs application programming interfaces Chapter 5 Administering 109 e JDBC e SQLJ ADO NET e OLE DB ODBC e Perl PHP pureQuery e Python e Ruby e CLI Embedded SOL Embedded SQL support Two types of embedded SQL processing exist static SQL and dynamic SQL Static SQL minimizes the time required to execute an SQL statement by processing in advance Dynamic SQL is processed when the SQL statement is submitted to the IBM mainframe database server Dynamic SQL is more flexible but potentially slower The decision to use static or dynamic SQL is made by the application programmer Both types are supported by DB2 Connect Different IBM mainframe database servers implement SQL differently DB2 Connect fully supports the common IBM SQL as well as the DB2 for z OS DB2 Server for VM and VSE formerly SQL DS and IBM DB2 for IBM i implementations of SQL IBM SQL is strongly recommended for maintaining database independence Multisite Updates Multisite update also known as distributed unit of work DUOW and two phase commit is a function that enables your applications to update data in multiple remote database servers with guaranteed integrity DB2
47. gzip d product tar gz Chapter 3 Installing DB2 Connect server 59 where product is the name of the DB2 Connect product that you downloaded b Untar the product file tar xvf product tar c Change directory cd product disk1l Note If you downloaded a National Language Package untar it into the same directory This will create the subdirectories for example nlpack disk2 in the same directory and allows the installer to automatically find the installation images without prompting 3 Enter the db2setup command from the directory where the product image resides to start the DB2 Setup wizard For multiple CD installations issue the db2setup command outside the mounted CD location with either a relative or absolute path name to ensure the DB2 Connect product CD can be unmounted as required After a few moments the IBM DB2 Setup Launchpad opens From this window you can view the installation prerequisites and the release notes or you can proceed directly to the installation 4 Once you have initiated the installation proceed through the DB2 Setup wizard installation panels and make your selections Installation help is available to guide you through the DB2 Setup wizard Click Help to invoke the online help You can click Cancel at any time to end the installation DB2 files will only be copied to you system once you have clicked Finish on the last DB2 Setup wizard installation panel Once completed DB2 Connect Personal Editi
48. higher a non administrator can perform an installation but will be prompted for administrative credentials by the DB2 Setup wizard The user right Access this computer from the network is required for the installation user account The installation user ID must belong to the Domain Administrators group on the domain if the installation requires a domain account to be created or verified You may also use the built in LocalSystem account as your Service Logon account for all products except DB2 Enterprise Server Edition User rights granted by the DB2 installer The DB2 installation program does not grant the Debug Programs user right The DB2 installer grants the following user rights e Act as part of the operating system e Create token object e Lock pages in memory e Log on as a service e Increase quotas e Replace a process level token DB2 Administration Server DAS user account A local or domain user account is required for the DB2 Administration Server DAS Important The DB2 Administration Server DAS has been deprecated in Version 9 7 and might be removed in a future release The DAS is not supported in DB2 pureScale environments Use software programs that use the Secure Shell protocol for remote administration For more information see DB2 administration server DAS has been deprecated at If you are performing a response file installation you can also specify the Local System account in the response file
49. infocenter db2luw v9 For DB2 Version 8 topics go to the DB2 Information Center URL at http publib boulder ibm com infocenter db2luw v8 Updating the DB2 Information Center installed on your computer or intranet server A locally installed DB2 Information Center must be updated periodically Before you begin A DB2 Version 10 1 Information Center must already be installed For details see the Installing the DB2 Information Center using the DB2 Setup wizard topic in Installing DB2 Servers All prerequisites and restrictions that applied to installing the Information Center also apply to updating the Information Center 172 DB2 Connect User s Guide About this task An existing DB2 Information Center can be updated automatically or manually e Automatic updates update existing Information Center features and languages One benefit of automatic updates is that the Information Center is unavailable for a shorter time compared to during a manual update In addition automatic updates can be set to run as part of other batch jobs that run periodically e Manual updates can be used to update existing Information Center features and languages Automatic updates reduce the downtime during the update process however you must use the manual process when you want to add features or languages For example a local Information Center was originally installed with both English and French languages and now you want to also install the
50. invoke the online help You can click Cancel at any time to exit the installation DB2 files will only be copied to your system once you have clicked Finish on the last DB2 Setup 42 DB2 Connect User s Guide wizard installation panel Once completed the DB2 Connect server product is installed using the opt 1BM db2 V9 8 default installation path If you are installing on a system where this directory is already being used the DB2 Connect product installation path will have _xx added to it where xx are digits starting at 01 and increasing depending on how many DB2 copies you have installed You can also specify your own DB2 database product installation path Results National Language Packs can also be installed by running the db2setup command from the directory where the National Language Pack resides after a DB2 Connect product has been installed The installation logs db2setup 1log and db2setup err will be located by default in the tmp directory You can specify the location of the log files If you want your DB2 database product to have access to DB2 documentation either on your local computer or on another computer on your network then you must install the DB2 Information Center The DB2 Information Center contains documentation for the DB2 database and DB2 related products See the Installing the DB2 Information Center using the DB2 Setup wizard UNIX topic in Installing DB2 Servers Installation requirements fo
51. max_coordagents database manager configuration parameter details 145 overview 143 maxagents database manager configuration parameter deprecated 150 memory usage tools 137 Microsoft Windows applications 6 monitoring connections 119 Windows Performance Monitor 119 mounting CDs or DVDs AIX 37 HP UX 41 Linux 44 58 Solaris 47 61 moving data DB2 Connect 88 multisite updates distributed unit of work DUOW 110 enabling 110 sync point manager 111 N national language support NLS converting character data 24 95 displaying DB2 Setup wizard 21 networks data transfer rates 159 performance tools 137 tuning 152 nodes directories updating 100 values 101 names directory customization worksheet 106 node directory values 101 system database values 101 NOMAP parameter DCS directory parameters 113 SQL CODE mapping 102 turning off SQL mapping 113 NONE authentication types 136 NOT ATOMIC compound SQL application design 140 notices 179 NULLID 85 num_initagents database manager configuration parameter configuring idle agents pool 143 overview 145 num_poolagents database manager configuration parameter configuring idle agents pool 143 overview 145 numdb database manager configuration parameter DB2 Connect 150 O ODBC CLI ODBC application performance tuning 160 enabled applications 93 interfaces 6 P packages host database servers 85 System i database servers 85 packed decimal data type 158 paging block size 150 pa
52. of the DB2 database server There are also changes in physical design characteristics of databases and changes to security that also have an impact 3 If you obtained customized code page conversion tables from the DB2 support service for previous versions or releases copy all of the files for those tables from the DB20LD conv to DB2DIR conv where DB2OLD is the location of your previous supported version of DB2 Connect copy and DB2DIR is the location of your new DB2 Connect copy You do not need to copy standard code page conversion tables If you upgraded your existing supported DB2 Connect copy on Windows operating systems you can restore the customized code page conversion tables that you backed up as part of the pre upgrade tasks for DB2 Connect servers to the DB2PATH conv directory where DB2PATH is the location of your new DB2 Connect copy 4 If you are connecting to a DB2 for z OS server or a IBM DB2 for IBM i server where euro support is required set the DB2CONNECT_ENABLE_EURO_CODEPAGE registry variable to YES on all DB2 Connect clients and servers so that the current application code page is mapped to the equivalent coded character set ID CCSID that explicitly indicates support for the euro sign 5 Optional If you upgraded any databases in your DB2 Connect server and changed the log space setting as recommended in the pre upgrade tasks for DB2 Connect servers adjust the log space size Refer to the Adjusting the log space
53. or more remote databases With the IBM data server client you can remotely administer DB2 or DB2 Connect servers All applications must access a database through the IBM data server client A Java applet can access a remote database through a Java enabled browser DB2 Connect client using the IBM data client is supported on Linux UNIX and Windows operating systems Chapter 5 Administering 91 Accessing IBM mainframe DB2 data using DB2 Connect A DB2 Connect client or Server enables a IBM data server client on a LAN access to data that is stored on IBM mainframe systems In organizations with large amounts of data IBM DB2 for IBM i DB2 for z OS or DB2 Server for VM and VSE are commonly used to manage that data Applications that run on any of the supported platforms can work with this data transparently as if a local database server managed it A DB2 Connect client or Server is required for supporting applications which access IBM mainframe data and exploit transaction monitors as well as applications that are implemented as Java applets In addition you can use a wide range of off the shelf or custom developed database applications with DB2 Connect and its associated tools For example you can use DB2 Connect products with e Spreadsheets such as Microsoft Excel and Lotus 1 2 3 to analyze real time data without having the cost and complexity of data extract and import procedures e Decision support tools such as BusinessObjec
54. plans are supplied by application developers and are contained in bind files which are created during precompilation Binding is a process of processing these bind files by an IBM mainframe database server Because several of the utilities supplied with DB2 Connect are developed using embedded SQL they must be bound to an IBM mainframe database server before they can be used with that system If you do not use the DB2 Connect utilities and interfaces you do not have to bind them to each of your IBM mainframe database servers The lists of bind files required by these utilities are contained in the following files e ddcsmvs 1st for System z e ddcsvse st for VSE e ddcsvm 1st for VM e ddcs400 1st for IBM Power Systems Binding one of these lists of files to a database will bind each individual utility to that database If a DB2 Connect server product is installed the DB2 Connect utilities must be bound to each IBM mainframe database server before they can be used with that system Assuming the clients are at the same fix pack level you need to bind the utilities only once regardless of the number of client platforms involved For example if you have 10 Windows clients and 10 AIX clients connecting to DB2 for z OS via DB2 Connect Enterprise Edition on a Windows server perform one of the following steps e Bind ddcsmvs 1 st from one of the Windows clients e Bind ddcsmvs 1st from one of the AIX clients e Bind ddcsmvs 1st from the D
55. previous supported versions Upgrade any existing transaction manager and DB2 Connect federated databases by referring to the Upgrading databases topic in the Upgrading to DB2 Version 10 1 What to do next After upgrading the DB2 Connect server perform the recommended post upgrade tasks such as resetting the diagnostic error level adjusting log space size and rebinding packages and verifying that your upgrade was successful Refer to Chapter 2 Upgrading to DB2 Connect Version 10 1 17 Post upgrade tasks for DB2 Connect servers Post upgrade tasks for DB2 Connect servers After upgrading your DB2 Connect servers you should perform several post upgrade tasks to ensure that your DB2 Connect servers perform as expected and run at their optimum level Procedure Perform the following post upgrade tasks for DB2 servers that also apply to DB2 Connect servers 1 If you set the diaglevel database manager configuration parameter to 4 as recommended in the pre upgrade tasks for DB2 Connect servers reset this parameter to the value set before the upgrade 2 Manage changes in DB2 server behavior Refer to the Manage changes in DB2 server behavior topic in the Upgrading to DB2 Version 10 1 There are new registry variables new configuration parameters and new default values for registry variables and configuration parameters introduced in latest version or release of DB2 database products that can impact the behavior
56. processed In addition to an increase in processing capability a Sysplex can provide flexibility in mixing levels of hardware and software and in dynamically adding systems Sysplex also provides DB2 Connect server the means to try alternate members should a failure occur with one member The rerouting capability for Sysplex is a DB2 Connect feature DB2 Connect server support for Sysplex is enabled by default and so is the rerouting capability for Sysplex Sysplex support to a host database can be turned off by removing the SYSPLEX parameter from its DCS directory entry but the DCS entry itself should not be removed even if it has no other parameter specified With the automatic client reroute capability for Sysplex the default behavior is for a Sysplex enabled connection to try connecting again when there is a communication failure Special register values up until the last successful transaction not holding resources are replayed when DB2 Connect is connected to a DB2 for z OS server You can configure the exact automatic client reroute retry behavior including disablement by using the DB2_MAX_CLIENT_CONNRETRIES and DB2_CONNRETRIES INTERVAL registry variables The connection timeout registry variable is DB2TCP_CLIENT_CONTIMEOUT Considerations for System z SYSPLEX exploitation DB2 Connect provides load balancing and fault tolerance when routing connections to DB2 Sysplex When connected to a DB2 for z OS database server running in
57. refer to www ibm com support docview wss amp uid sw 21257606 DB2 database products support Solaris ZFS filesystems and Logical Domains LDoms 46 DB2 Connect User s Guide For details about virtualization technology supported by DB2 products see http www ibm com developerworks wikis display im DB2 Virtualization Support Software requirements e SUNWIibC software is required to install DB2 Connect on Solaris e A browser is required to view online help Communication requirements You can use TCP IP e For TCP IP connectivity no additional software is required e DB2 Connect is supported on Sun Cluster 2 2 if The protocol to the host is TCP IP Two phase commit is not used This restriction is relaxed if the user configures the SPM log to be on a shared disk this can be done through the spm_log_path database manager configuration parameter and the failover system has an identical TCP IP configuration the same host name IP address and so on Mounting CDs or DVDs for DB2 Connect Solaris If the CD ROM is not automatically mounted when you insert it into the drive on Solaris Operating System issue the mount command Before you begin If you are mounting the CD or DVD drive from a remote system using NFS the CD or DVD file system on the remote computer must be exported with root access Depending on your local system configuration you might also need root access on the local computer Procedure To mount th
58. reuse after the application owning the connection issues a disconnect request In many 2 tier client server applications users do not disconnect for the duration of the workday Likewise most application servers in multi tier applications establish database connections at server start up time and do not release these connections until the application server is shut down In these environments connection pooling will have little if any benefit However in web and client server environments where the frequency of connections and disconnections is higher than connection pooling will produce significant performance benefits The connection concentrator allocates host database resources only for the duration of an SQL transaction while keeping user applications active This allows for configurations where the number of DB2 threads and the resources they consume can be much smaller than if every application connection had its own thread When it comes to fail safe operation and load balancing of workload connection concentrator is clearly the right choice as it allows reallocation of work with every new transaction Alternatively connection pooling can only offer very limited balancing and only at connect time Chapter 9 Tuning 149 Connection pooling and connection concentrator should be used together although they address different issues Connection concentrator required with WebSphere MQ Transaction Manager and DB2 for z OS When running
59. servers preparation is required to address any issues that may exist Chapter 2 Upgrading to DB2 Connect Version 10 1 19 20 DB2 Connect User s Guide Chapter 3 Installing DB2 Connect server Supported DB2 Connect interface languages DB2 language support for DB2 interfaces can be categorized into server group languages and client group languages Server group languages will translate most messages help and DB2 graphical interface elements Client group languages will translate the IBM Data Server Runtime Client component which will include most messages and certain help documentation Server group languages include Brazilian Portuguese Czech Danish Finnish French German Italian Japanese Korean Norwegian Polish Russian Simplified Chinese Spanish Swedish and Traditional Chinese Client group languages include Arabic Bulgarian Croatian Dutch Greek Hebrew Hungarian Portuguese Romanian Slovak Slovenian and Turkish Do not confuse languages supported by the DB2 database product with languages supported by the DB2 interface Languages supported by the DB2 database product means the languages in which data can exist These languages are a superset of languages supported by the DB2 interface Displaying the DB2 Setup wizard in your national language Linux and UNIX The db2setup command queries the operating system to determine the existing language settings If the language setting of your operating sys
60. size in migrated databases topic in the Upgrading to DB2 Version 10 1 Ensure that the amount of log space that you allocate is adequate for your DB2 Connect server 6 Optional Back up your databases after the upgrade is complete Refer to the Backing up databases before upgrading topic in the Upgrading to DB2 Version 10 1 7 Optional If you have DB2 Connect federated databases review the Configuring federated systems after migration topic in IBM WebSphere Information Integration Migrating to Federation Version 9 to determine if you need to perform any tasks after you upgrade your federated databases 8 Verify that your DB2 Connect server upgrade was successful Test connections to all your cataloged databases The following example shows how to test a connection from the Command Line Processor CLP 18 DB2 Connect User s Guide db2 CONNECT TO DATABASE sample user mickey using mouse You need to specify a user and password when connecting to a remote database Ensure all connections are successful Also test your applications and tools to ensure that the DB2 Connect server is working as expected What to do next At this point you should resume all of your maintenance activities You should also remove any previously supported versions or releases of DB2 Connect copies that you no longer need Related tasks Pre upgrade tasks for DB2 Connect servers on page 15 To successfully upgrade your DB2 Connect
61. the DB2 Connect workstation On UNIX and Linux etc On Windows XP and Windows Server 2003 Usually SystemRoot system32 drivers etc where SystemRoot represents the Windows install path directory You might want to add the host information to a domain name server to avoid maintaining this file on multiple systems e The locations of the equivalent files at the target DB2 for z OS host e The TCP IP port number defined to DB2 for z OS Note The associated service name information is not exchanged between the DB2 Connect workstation and DB2 for z OS Port number 446 has been registered as the default for communication from a DB2 Connect workstation The TCP IP addresses and host names for both the host and the DB2 Connect workstation The LOCATION NAME of the DB2 for z OS database server e The user ID and password to be used when issuing CONNECT requests to the database at the IBM mainframe server Refer to your local network administrator and your DB2 for z OS administrator for help getting this information Use the tables that follow as a worksheet to plan each TCP IP connection between DB2 Connect and a host database server Table 9 User Information Ref Description Sample Value Your Value TCP 1 User name A D B User TCP 2 Contact info 123 456 7890 TCP 5 User ID ADBUSER TCP 6 Database type db2390 78 DB2 Connect User s Guide Table 9 User Information continued
62. the report if the System Monitor UOW switch was turned on during processing Otherwise Not Collected will be shown 126 DB2 Connect User s Guide Chapter 7 Developing database applications Running your own applications You can build and run DB2 applications with an IBM Data Server Client installed Various types of applications can access DB2 databases Applications developed using the IBM data server client that include embedded SQL APIs stored procedures user defined functions or calls to the CLI ODBC applications Java applications using the JDBC or SQLJ interfaces PHP applications Ruby or Ruby on Rails applications Perl applications Python applications On Windows operating systems the following routines or objects can also access DB2 databases ActiveX Data Objects ADO implemented in Microsoft Visual Basic and Microsoft Visual C Object Linking and Embedding OLE Automation Routines UDFs and Stored Procedures Object Linking and Embedding Database OLE DB table functions To run an application 1 2 Ensure the server is configured and running On the DB2 server ensure that the database manager is started on the database server to which the application program is connecting If it is not you must issue the db2start command at the server before starting the application Ensure that you can connect to the database that the application uses Bind the necessary files to support the data
63. the size of actual data does not vary much CHAR is more efficient because each VARCHAR field has a few bytes of length information which must be transmitted Network hardware The following considerations relate to the hardware speed of the network or transmission media network adapter or communication controller network topology network traffic and network reliability e Speed of the network or transmission media Performance improves with a faster transmission medium For example the following list shows some typical raw data transfer rates Channel to channel fiber optics 4 0 MB s 16 Mbps LAN 2 0 MB s Channel to channel regular 1 0 MB s 4 Mbps LAN 0 5 MB s High speed T1 carrier 1 544 Mbps 0 193 MB s Fast remote 56 Kbps phone line 0 007 MB s 19 6 Kbps modem 0 002 MB s 9600 bps modem 0 001 MB s The data transfer rate is limited by the slowest transmission medium in the path to the IBM mainframe database server e Network adapter or communication controller You should carefully plan the memory usage of the network adapter and communication controller In addition you should work with a network specialist to ensure that the controller has the capability to handle the extra traffic generated by DB2 Connect e Network topology If data crosses from LAN to LAN and from one network to another network consider the travel time Bridges routers and gateways will add to the elapsed time For example r
64. these databases Windows only If you obtained customized code page conversion tables from the DB2 support service you need to backup all of the files in the DB20LD conv directory where DB2OLD is the location of your existing DB2 Connect copy Upgrading your current version or release of DB2 Connect copy removes these tables because standard code page tables are contained in a new version or release DB2 Connect library You do not need to backup standard code page conversion tables Chapter 2 Upgrading to DB2 Connect Version 10 1 15 7 Optional Upgrade your DB2 Connect server in a test environment to identify upgrade issues and to verify that database applications and routines work as expected before upgrading your production environment 8 If the diaglevel database manager configuration parameter is set to 2 or less set it to 3 or higher before upgrading Refer to the Setting the diagnostic log file error capture level topic in the Troubleshooting and Tuning Database Performance to set this database manager configuration parameter In the latest version or release of DB2 Connect all significant upgrade events are logged in the db2diag log files when the diaglevel database manager configuration parameter is set to 3 default value or higher 9 Take the DB2 Connect server offline for upgrading For details refer to the Taking a DB2 server offline before upgrading topic in the Upgrading to DB2 Version 10 1 Upgrad
65. this utility you have two alternatives You can install a DB2 Connect product e Using the response file method e Manually using the db2setup command You cannot manually install a DB2 database product using the operating system s native installation utility SMIT Any existing scripts containing this native installation utility that you use to interface and query with DB2 installations will need to change Procedure To install a DB2 Connect server product such as DB2 Connect Enterprise Edition on AIX using the DB2 Setup wizard 1 Change to the directory where the DVD is mounted cd db2dvd where db2dvd represents mount point of the DVD 2 If you downloaded the DB2 Connect product image you must decompress and untar the product file a Decompress the product file gzip d product tar gz where product is the name of the database product that you downloaded b Untar the product file tar xvf product tar c Change directory cd product disk1l Note If you downloaded a National Language Package untar it into the same directory This will create the subdirectories for example nlpack disk2 in the same directory and allows the installer to automatically find the installation images without prompting 3 Enter the db2setup command from the directory where the product image resides to start the DB2 Setup wizard After a few moments the IBM DB2 Setup Launchpad opens For multiple CD installations issue the db2setup co
66. types supported with DB2 Connect server Stes 136 Chapter 9 Tuning 137 DB2 Connect performance considerations 137 Application design 140 Connection management 143 Connection pooling 143 Connection concentrator 145 Connection pooling and connection concentrator 149 Connection concentrator required with WebSphere MQ Transaction Manager and DB2 for z OS ny 2 amp amp se a 150 DB2 Connect server tuning 150 Host database tuning 4 152 Network tuning considerations 152 System resources contention 154 DB2 Connect performance troubleshooting 154 Tuning DB2 for z OS 154 iv DB2 Connect User s Guide Increasing DB2 Connect data transfer rates 155 Extra query block 155 RFC 1323 Window scaling gt 156 High availability and load balancing for host database connectivity a 157 Host data conversion 158 Data types for character data 158 Network hardware 159 CLI ODBC application performance tuning 160 Chapter 10 Troubleshooting 161 Troubleshooting DB2 Connect Server 161 Gathering relevant information 161 Initial connection is not successful 161 Problems encountered after an initial connection 162 Diagnostic tools 163 Chapter 11 Messages 165 Common DB2 Connect problems 165 Appendix A Overview of the DB2 technical information 169 DB2 technical library in hardcopy or PDF format 169 Displaying SQL state ad from the com
67. will be able to take advantage of pooled outbound connections The concentrator will accept connections via other communications protocols such as named pipes but you will not be able to use its XA concentration features with that connection 146 DB2 Connect User s Guide e For XA tightly coupled transaction support all applications that participate in the same XA transaction must use the same DB2 Connect server Instance to connect to the host e Only applications that close withhold resources such as withhold cursors on transaction boundaries can benefit from the concentrator Transactions that do not close withhold cursors will still go through but will be assigned a dedicated worker agent and hence will not be able to use the concentrator s full feature set e If you declare temporary tables they must be dropped explicitly at transaction or branch boundary Failure to drop the tables will turn off connection concentration but the application will continue to work e All applications participating in the same XA transaction must have the same CCSID and use the same user ID to make the connection e If an outbound connection was established to support two phase connection that connection s agent can only be used to support two phase connections Similarly agents established to support a one phase connection can only support one phase connections e The concentrator supports applications that use the IBM Data Server Driver for JDBC
68. you must also have the appropriate National Language Packages e To locate DB2 database products already installed on your system use the db21s command Refer to the Listing DB2 products installed on your system Linux and UNIX topic in Installing DB2 Servers e The DB2 Setup wizard is a graphical installer You must have X windows software capable of rendering a graphical user interface for the DB2 Setup wizard to run on your machine Ensure that the X windows server is running Ensure that you have properly exported your display For example export DISPLAY 9 26 163 144 0 e If security software such as Lightweight Directory Access Protocol LDAP is used in your environment you must manually create required DB2 users before you start the DB2 Setup wizard 44 DB2 Connect User s Guide Note Network Information Services NIS and Network Information Services Plus NIS features are deprecated starting with DB2 Version 9 1 Fix Pack 2 Support for these features might be removed in a future release Lightweight Directory Access Protocol LDAP is the recommended solution for centralized user management services About this task The DB2 Setup wizard is a Java based installation tool that automates the installation and configuration of any DB2 database products If you prefer not to use this utility you have two alternatives You can install a DB2 Connect product e Using the response file method e Manually using the db2setup
69. 1 165 gathering information 161 online information 176 performance 154 tutorials 176 trust relationships DB2 Connect 129 trusted connections CLI ODBC 130 DB2 Connect 129 switching users through CLI ODBC 131 trusted contexts CLI ODBC support 130 DB2 Connect support 129 tuning DB2 for z OS 154 host databases 152 networks 152 parameters agentpri 150 dir_cache 150 maxagents 150 MAXDARI 150 numdb 150 rqrioblk 150 tutorials list 175 problem determination 176 pureXML 175 troubleshooting 176 Tuxedo DB2 Connect Enterprise Edition 8 two phase commit enabling 110 resynch port used by TCP IP connections 101 U uninstallation DB2 Connect Windows 72 root installations 73 units of work distributed 110 overview 97 remote 98 UNIX changing DB2 Connect interface language 24 removing DB2 Connect root 73 uninstalling DB2 root 73 uninstalling DB2 Connect root 73 updates database directories 100 DB2 Information Center 172 174 upgrades DB2 Connect overview 13 14 procedure 16 user accounts DB2 Administration Server Windows 50 instance user Windows 50 required for installation Windows 50 65 user groups DB2ADMNS 69 DB2USERS 69 security 69 utilities binding 85 93 database system monitor 5 DB2 Connect administration 5 ddcspkgn 85 Pps process status 163 V VARCHAR data type overview 158 VTAM preparing z OS for connections from DB2 Connect 76 W WebSphere MQ DB2 Connect 150 window scaling RFC 132
70. 12 15 2001 10 28 24 596495 Most recent elapsed time to connect 0 950561 Most recent elapsed connection duration 0 000000 120 DB2 Connect User s Guide Host response time sec ms Last reset timestamp Number of SQL statements attempted Commit statements attempted Rollback statements attempted Failed statement operations Total number of gateway connections Current number of gateway connections Gateway conn waiting for host reply Gateway conn waiting for client request Gateway communication errors to host Timestamp of last communication error High water mark for gateway connections Rows selected Outbound bytes sent Outbound bytes received Wow ou OrZzorodrrcdcorn 0 000000 140 103 This report provides information about the database connections performance errors and throughput of SQL requests DB2 Monitor snapshots can be much more detailed in fact For instance if you issue the GET SNAPSHOT FOR ALL DCS APPLICATIONS command you will receive a report similar to the following one DCS Application Snapshot Client application ID Sequence number Authorization ID Application name Application handle Application status Status change time Client node Client release level Client platform Client protocol Client codepage Process ID of client application Client login ID Host application ID Sequence number Database alias at the gateway DCS database name Host database name Host release level Host CCSID Outboun
71. 2 Connect on the same machine for simplicity or on a separate machine to off load CPU cycles A DB2 Connect server enables multiple clients to connect to IBM mainframe data and can significantly reduce the effort that is required to establish and maintain access to enterprise data To connect to an IBM mainframe database server you require a licensed DB2 Connect product You cannot connect directly to an IBM mainframe Data Server using a IBM data server client 92 DB2 Connect User s Guide DB2 for VSE cae T 4 4 for IBM i DB2 Ss Power for z OS ystem 2 Systems Servers Application 1 Application 2 Application n Application Business Logic Figure 5 Transaction monitors working with DB2 Connect Binding database utilities on DB2 Connect You must bind the database utilities import export reorg the Command Line Processor and CLI bind files to each database before they can be used with that database About this task In a network environment if you are using multiple clients that run on different operating systems or are at different versions or service levels of DB2 you must bind the utilities once for each operating system and DB2 version combination Binding a utility creates a package which is an object that includes all of the information that is needed to process specific SQL statements from a single source file The bind files are grouped together in different 1st files in the
72. 3 Installing DB2 Connect server 63 To install DB2 Connect Personal Edition the following requirements must be met Operating system requirements One of e Windows XP Professional Edition 32 bit and 64 bit with Service Pack 3 or later e Windows 2003 with Service Pack 2 or later Standard Edition 32 bit and 64 bit Enterprise Edition 32 bit and 64 bit Datacenter Edition 32 bit and 64 bit e Windows Vista with Service Pack 2 or later Business Edition 32 bit and x64 Enterprise Edition 32 bit and x64 All Windows Vista service packs are supported e Windows 7 with Service Pack 1 or later Professional Edition 32 bit and x64 Enterprise Edition 32 bit and x64 e Windows Server 2008 with Service Pack 2 or later Standard Edition 32 bit and 64 bit Enterprise Edition 32 bit and 64 bit Datacenter Edition 32 bit and 64 bit e Windows Server 2008 R2 Standard Edition 64 bit Enterprise Edition 64 bit Datacenter Edition 64 bit All Windows Server 2008 R2 service packs are supported Hardware requirements e All Intel and AMD processors capable of running the supported Windows operating systems 32 bit and x64 based systems Software requirements e A browser is required to view online help Communication requirements e TCP IP is supported and supplied by the operating system Windows 64 bit considerations e SQL requests sent by remote 32 bit clients from earlier versi
73. 3 extensions 156 Windows default language setting 23 installing DB2 Connect with non Administrator access 54 67 DB2 Connect Personal Edition procedure 62 DB2 Connect server products procedure 48 Performance Monitor monitoring DB2 applications 119 uninstalling DB2 Connect 72 Index 189 Windows continued user accounts DB2 Connect Personal Edition installation 65 DB2 Connect product installation 50 Windows operating systems installing DB2 Connect Personal Edition requirements 64 DB2 Connect server products requirements 49 worksheets directory customization 106 X X Open distributed transaction processing DTP model overview 8 XA concentrator examples 145 resource managers 8 trusted connections 129 XA transaction managers connection concentrators 145 overview 8 Z z OS configuring DB2 database systems 80 zoned decimal data types 158 zSeries installing DB2 Connect for Linux 28 190 DB2 Connect User s Guide Printed in USA SC27 3863 00 apiny sasp yauu0 Zg FOL OsUUOD z404 Wal TUOLZEWNOJUL JULAS
74. 5 After installation establish the connection between DB2 Connect and your IBM mainframe database system DB2 Connect can locate and configure all TCP IP connections for you For details see the topic about validating IBM Data Server Driver Package Windows installation 6 Bind the programs and utilities provided with DB2 Connect to your IBM mainframe database Note This step is not required with IBM Data Server Driver Package DS Driver For larger client packages rebinding is required with each Fix Pack upgrade 7 Test the IBM mainframe connection 8 You are now ready to use DB2 Connect with all your applications Workstations that will be used for application development should have the IBM data server client installed 9 If you want to use this workstation to administer DB2 for z OS or DB2 Database for Linux UNIX and Windows servers install the IBM data server client Installing DB2 Connect Personal Edition Linux To define your installation preferences and to install a DB2 Connect Personal Edition product on Linux use the DB2 Setup wizard Installing IBM Data Server Driver Package DS Driver and then applying the DB2 Connect Personal Edition license is the preferred alternative to the process listed in the following section Refer to IBM data server client types for details Before you begin Before beginning your installation Chapter 3 Installing DB2 Connect server 55 e You can install DB2 Connect using e
75. B2 Connect server This example assumes that e All the clients are at the same service level If they are not then in addition you might need to bind from each client of a particular service level e The server is at the same service level as the clients If it is not then you need to bind from the server as well In addition to DB2 Connect utilities any other applications that use embedded SQL must also be bound to each database that you want them to work with An Copyright IBM Corp 1993 2012 85 application that is not bound will usually produce an SQLO805N error message when executed You might want to create an additional bind list file for all of your applications that need to be bound For each IBM mainframe database server that you are binding to perform the following steps 1 Make sure that you have sufficient authority for your IBM mainframe database server management system System VSE or 86 DB2 Connect User s Guide Zz The authorizations required are e SYSADM or e SYSCTRL or e BINDADD and CREATE IN COLLECTION NULLID Note The BINDADD and the CREATE IN COLLECTION NULLID privileges provide sufficient authority only when the packages do not already exist For example if you are creating them for the first time If the packages already exist and you are binding them again then the authority required to complete the task s depends on who did the original bind A If you did the original bind an
76. B2 database export and import utilities allow you to move data from an IBM mainframe server database to a file on the DB2 Connect workstation and the reverse You can then use the data with any other application or relational database management system that supports this export or import format For example you can export data from an IBM mainframe server database into a PC IXF file and then import it into a DB2 Database for Linux UNIX and Windows database You can perform export and import operations from a database client or from the DB2 Connect workstation Note 1 The data to be exported or imported must comply with the size and data type restrictions that are applicable to both databases 2 To improve import performance you can use compound queries Specify the compound file type modifier in the import utility to group a specified number of query statements into a block This can reduce network usage and improve response time With DB2 Connect export and import operations must meet the following conditions e The file type must be PC IXF e A target table with attributes that are compatible with the data must be created on the target server before you can import to it The db21ook utility can be used to get the attributes of the source table Import through DB2 Connect cannot create a table because INSERT is the only supported option If any of these conditions is not met the operation fails and an error message is return
77. B2USERS groups for users that need to run local DB2 applications and tools on the machine The DB2 installer creates two new groups You can either use a new name or accept the default names The default group names are DB2ZADMNS and DB2USERS This process is also applicable to instance based clients Before you begin e You must have installed a DB2 database product e You must have selected the Enable operating system security check box on the Enable operating system security for DB2 object panel during the installation of your DB2 database product Procedure To add users to the appropriate group 1 Click Start and select Run 2 Type lusrmgr mse and click OK 3 Select Local Users and Groups 4 Select Users Chapter 3 Installing DB2 Connect server 69 Select the user you want to add Click Properties Click the Member Of tab Click Add Select the appropriate group Click OK SCO PND 1 What to do next If you did the install and chose not to enable the new security feature you can still do so post install by running the db2extsec exe command Adding a user to a group takes effect the first time the user logs on after the user has been added For example if you add you user ID to the DB2ZADMNS group you need to log out and then log in again for this change to take effect Applying fix packs to DB2 Connect It is recommended that you keep your DB2 database environment running at the latest fix pack level to ensure pr
78. CODE mapping The default mapping file is imbedded in the DB2 Connect library a tailored mapping file must be read from disk which affects performance Host database tuning System performance will be affected by the performance of the IBM mainframe database server Different database management systems have different performance features SQL optimizers of different systems for example could behave differently with the same application Check your IBM mainframe database server system performance documentation for more information You might be able to improve performance by using the uncommitted read UR or no commit NC bind options where available to avoid journaling Note When using UR unjournaled data can only be read not updated and then only if blocking is set to ALL Depending on the application server and the lock granularity it provides the isolation level used for a query or application might have a significant effect on performance The database should have the appropriate level of normalization effective use of indexes and suitable allocation of database space Performance can also be affected by the data types that you use as described in the following sections Network tuning considerations The best way to improve overall performance in a distributed database environment is to eliminate delays from the network It is common for network administrators to consider a network to be more efficient if it collec
79. CP IP protocol TCP IP might not be immediately notified of a partner s failure on another IBM mainframe As a result a client application accessing a remote DB2 server using TCP IP or the corresponding agent at the server might sometimes appear to be hung The TCP IP SO_KEEPALIVE socket option is used to detect when there has been a failure and the TCP IP connection has been broken Registering a DB2 Connect license key using the db2licm command Use the db21icm command to apply the license entitlement certificate also referred to as registering a license key Chapter 4 Configuring 83 Before you begin To complete this task you must have the appropriate license file 1ic To connect to a z OS server or a System i server you must register a DB2 Connect license key Retrieve the license file from your Passport Advantage distribution for example db2conpe lic then copy the license file to the license directory under the directory where the driver was installed If you are using DB2 Connect Unlimited Edition for z OS then use a server based license key This one step will prevent the need for client based license keys For details see the topic about activating the license key for DB2 Connect Unlimited Edition for System z On Windows operating systems you must belong to the local Administrators or Power Users group to use the db2licm command with the a command parameter Procedure e On Windows operating systems regist
80. Castle Drive Armonk NY 10504 1785 U S A For license inquiries regarding double byte character set DBCS information contact the IBM Intellectual Property Department in your country or send inquiries in writing to Intellectual Property Licensing Legal and Intellectual Property Law IBM Japan Ltd 1623 14 Shimotsuruma Yamato shi Kanagawa 242 8502 Japan The following paragraph does not apply to the United Kingdom or any other country region where such provisions are inconsistent with local law INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION AS IS WITHOUT WARRANTY OF ANY KIND EITHER EXPRESS OR IMPLIED INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF NON INFRINGEMENT MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE Some states do not allow disclaimer of express or implied warranties in certain transactions therefore this statement may not apply to you This information could include technical inaccuracies or typographical errors Changes are periodically made to the information herein these changes will be incorporated in new editions of the publication IBM may make improvements changes or both in the product s and or the program s described in this publication at any time without notice Any references in this information to websites not owned by IBM are provided for convenience only and do not in any manner serve as an endorsement of those Copyright IBM Corp 1993 2012 179
81. Configuration diaglevel to 4 using the command db2 update dbm cfg using diaglevel 4 After stopping and restarting DB2 look in the db2diag log files to check that DB2 TCP IP communications have been started You should see output similar to the following one 2001 02 03 12 41 04 861119 Instance svtdbm2 Node 00 PID 86496 db2sysc Appid none common_communication sqlcctcp_start_listen Probe 80 DIA3000I TCPIP protocol support was successfully started SQL30081N with Protocol Specific Error Code 10032 Symptom SQL30081N A communication error has been detected Communication protocol being used TCP IP Communication API being used SOCKETS Location where the error was detected 9 21 85 159 Communication function detecting the error send Protocol specific error code s 10032 nyu my SQLSTATE 08001 Chapter 11 Messages 167 Solution This error message might be received when trying to disconnect from a machine where TCP IP communications have already failed Correct the problem with the TCP IP subsystem On most machines simply restarting the TCP IP protocol for the machine is the way to correct the problem Occasionally recycling the entire machine might be required SQL30082 RC 24 During CONNECT Symptom SQLCODE 30082 The username or the password supplied is incorrect Solution 168 DB2 Connect User s Guide Ensure that the correct password is provided on the CONNECT statement if necessary Pass
82. DB2 database product from your Windows operating system Only perform this task if you no longer require your existing DB2 instances and databases About this task If you are uninstalling the default DB2 copy and you have other DB2 copies on your system use the db2swtch command to choose a new default copy before you proceed with the uninstallation Also if your DB2 Administration Server DAS is running under the copy being removed move your DAS to a copy that is not being removed Otherwise re create the DAS using the db2admin create command after the uninstall and you reconfigure the DAS for some function to work 72 DB2 Connect User s Guide Procedure To remove your DB2 database product from Windows 1 Optional Drop all databases using the drop database command Be sure that you no longer need these databases If you drop your databases all of your data will be gone 2 Stop all DB2 processes and services This can be done through the Windows Services panel or by issuing the db2stop command If DB2 services and processes are not stopped before attempting to remove your DB2 database product you will receive a warning containing a list of processes and services that are holding DB2 DLLs in memory If you will use Add Remove Programs to remove your DB2 database product this step is optional 3 You have two options for removing your DB2 database product e Add Remove Programs Accessible through the Windows Control Panel use t
83. DED And here is sample output from this command when using the new option EXTENDED Chapter 6 Monitoring DB2 Connect server 125 List of DCS Applications Extended Report Client application ID 2002 91a 519 13 209 6bff fel4 4fbb 8196 060306214224 Sequence number 00001 Authorization ID NEWTON Trusted Authorization ID Application name db2cli exe Application handle 37 Application status Status change time waiting for request Not Collected Client node SAYYID Client release level SQL09000 Client platform NT Client protocol TCP IP Client codepage 1252 Process ID of client application 1192 Client login ID ISAYYID Host application ID G91A0D3A P982 060306214231 Sequence number 00001 Database alias at the gateway MDB DCS database name MDB Host database name MEXICO Host release level DSNQ8015 Host CCSID 1208 The application status field contains one of the following three values 1 connect pending outbound This means that the request to connect to a IBM mainframe database has been issued and DB2 Connect is waiting for the connection to be established 2 waiting for request This means that the connection with the IBM mainframe database has been established and that DB2 Connect is waiting for an SQL statement from the client application 3 waiting for reply This means that the SQL statement has been sent to the IBM mainframe database Also the status change time is only shown in
84. Driver Package DS Driver and then applying the DB2 Connect Personal Edition license is the preferred alternative to the process listed in the following section Refer to IBM data server client types for details Before you begin Before beginning your installation 58 DB2 Connect User s Guide You can install DB2 Connect using either root or non root user authority For more information about non root installation see Non root installation overview Linux and UNIX in Installing DB2 Servers Ensure that your system meets the installation memory and disk requirements The DB2 database product DVD must be mounted on your system The DB2 Connect product image must be available If you are installing a non English version of a DB2 Connect product you must also have the appropriate National Language Packages To locate DB2 database products already installed on your system use the db21s command Refer to the Listing DB2 products installed on your system Linux and UNIX topic in Installing DB2 Servers The DB2 Setup wizard is a graphical installer You must have X windows software capable of rendering a graphical user interface for the DB2 Setup wizard to run on your machine Ensure that the X windows server is running Ensure that you have properly exported your display For example export DISPLAY 9 26 163 144 0 If security software such as Lightweight Directory Access Protocol LDAP is used in your environment you mu
85. For details regarding known AIX issues see www ibm com support docview wss amp uid swg21165448 Communication requirements When using a communication protocol you have the following requirements e For TCP IP connectivity no additional software is required e For LDAP Lightweight Directory Access Protocol support you require an IBM SecureWay Directory Client V3 2 1 or later DB2 product installation on NFS Network File System The installation of DB2 products on NFS Network File System is not recommended Running DB2 products on NFS for example NFS mounting opt IBM db2 V10 1 and then running off code that was physically installed on a remote system requires several manual setup steps There are also a number of potential issues with setting up NFS for a DB2 server These include possible problems that involve e Performance impacted by network performance e Availability you are allowing a single point of failure e Licensing there is no checking done across machines e Diagnosing NFS errors can be difficult As mentioned the setup for NFS will require several manual actions including e Ensuring that the mount point preserve the install path e Permission must be controlled for example write permission should not be given to the mounting machine e DB2 registries have to be set up manually and maintained across all mounting machines 36 DB2 Connect User s Guide e The db21s command which lists installed DB2
86. For more details refer to the sample response files in the db2 windows samples directory The LocalSystem account is available for all products except DB2 Enterprise Server Edition and can be selected through the DB2 Setup wizard The DAS is a special DB2 administration service used to support the GUI tools and assist with administration tasks on local and remote DB2 servers Chapter 3 Installing DB2 Connect server 51 The DAS has an assigned user account that is used to log the DAS service on to the computer when the DAS service is started You can create the DAS user account before installing DB2 or you can have the DB2 Setup wizard create it for you If you want to have the DB2 Setup wizard create a new domain user account the user account you use to perform the installation must have authority to create domain user accounts The user account must belong to the Administrators group on the computer where you will perform the installation This account will be granted the following user rights e Act as part of the operating system e Debug programs e Create token object e Lock pages in memory e Log on as a service e Increase quotas adjust memory quotas for a process on Windows XP and Windows Server 2003 operating systems e Replace a process level token If extended security is enabled the DB2ADMNS group will have all these privileges You can add users to that group and you do not need to add these privileges explicitly Ho
87. LAVIV parms BIDI 62245 This tells DB2 Connect to override the DB2 host database CCSID of 424 with 62245 This override includes the following processing 1 DB2 Connect will connect to the DB2 host database using CCSID 62209 BiDi string type 10 2 DB2 Connect will perform BiDi layout transformation on data it is about to send to the DB2 host database from CCSID 62213 BiDi string type 5 to CCSID 62209 BiDi string type 10 3 DB2 Connect will perform BiDi layout transformation on data it receives from the DB2 host database from CCSID 62245 BiDi string type 10 to CCSID 62213 BiDi string type 5 108 DB2 Connect User s Guide Note 1 The environment variable or registry value DB2BIDI has to be set to YES in order for the BIDI parameter to take effect DB2BIDI must be set at the DB2 Connect workstation where the DCS database directory entry is catalogued For applications running on a client remote to a DB2 Connect server the DB2BIDI variable must be set at that client as well 2 If you would like DB2 Connect to perform layout transformation on data it is about to send to the DB2 host database even though you do not have to override its CCSID you still have to add the BIDI parameter in the DCS database directory PARMS field In this case the CCSID that you should provide would be the default DB2 host database CCSID 3 In some cases use of a bidirectional CCSID might cause the SQL query itself to be modified such th
88. M i system Procedure To set up the connection you need to know the following information 1 The local network name You can get this information by entering DSPNETA 2 The local adapter address You can get this information by entering the WRKLIND command in one of the following ways WRKLIND elan Lists Ethernet adapters WRKLIND trlan Lists token ring adapters WRKLIND a11 Lists all adapters 3 The hostname You can get this information by entering DSPNETA 4 The TCP IP port or service name The default is X 07 6DB X 07F6C4C2 The default is always used by DB2 for i If entering a hexadecimal number is not convenient an alias is QCNTEDDM 5 The relational database name You can get this information by entering DSPRDBDIRE This will display a list The line containing LOCAL in the Remote Location column identifies the RDBNAME which must be defined to the client If there is no LOCAL entry you can add one or use the system name obtained from the DSPNETA command on the server Copyright IBM Corp 1993 2012 75 Results Here is an example e Display Relational Database Directory Entries Position to a eoa e es Type options press Enter 5 Display details 6 Print details Relational Remote Option Database Location Text DLHX RCHAS2FA JORMT2FA JORMT2FA JORMT4FD JORMT4FD JOSNAR7B RCHASR7B RCHASR7B LOCAL on RCHASR7C RCHASR7C R7BDH3SNA RCH2PDH3 RCHASDH3 RCHASDH3 XN A
89. M mainframe database server supports SECURITY SOCKS do not specify SECURITY for a TCP IP node DCS Directory Parameters Table 15 DCS Directory Parameters Parameter Example Your value Database name DB2DB Target database name NEW_YORK3 Application requester Parameter string m OCALDATE YYMMDD System Database Directory Parameters Table 16 System Database Directory Parameters Parameter Example Your value Database name DB2DB Database alias NYC3 Node name DB2NODE Authentication SERVER Defining multiple entries for the same database For each database you must define at least one entry in each of the three directories node directory DCS directory and system database directory In some cases you might want to define more than one entry for the database For example you might want to turn off SQLCODE mapping for applications that were ported from the IBM mainframe database server but accept the default mapping for applications that were developed for the client server environment You would do this as follows e Define one entry in the node directory e Define two entries in the DCS directory with different database names For one entry specify NOMAP in the parameter string e Define two entries in the system database directory with different database aliases and the two database names that you specified in the DCS directory Both aliases
90. ME supports up to 8 alias location names allowing applications the ability to use different dbalias names to access a Version 8 z OS server IBM Power Systems Servers IBM DB2 for IBM i an integral part of the IBM i operating system Only one database can exist on an IBM Power Systems server unless the system is configured to use independent auxiliary storage pools Configuring TCP IP for DB2 for z OS To configure TCP IP communications between your DB2 Connect workstation and DB2 for z OS Version 8 or later you must first collect network details about the host database server Chapter 4 Configuring 77 Before you begin The instructions assume the following conditions You are connecting to a single host database server or location via TCP IP Multiple host connections will be handled in exactly the same way although the port number and service number required in each case might be different Use the group IP address to connect to a group location The target database resides on DB2 for z OS Version 8 or later All the necessary software prerequisites are installed DB2 clients have been set up as required Procedure iy Before you can use DB2 Connect over a TCP IP connection you must collect information about both the host database server and the DB2 Connect server For each host server that you are connecting to via TCP IP you must have the following information e The location of the TCP IP services and hosts files at
91. Minimum C runtime level requires the xlC rte 11 1 0 1 and xIlC AIX rte 11 1 0 1 or later filesets e To verify that it is a CHRP architecture system issue the command Iscfg and look for the following output Model Architecture chrp For POWER3 processor based systems first upgrade to POWER4 processor based systems before installing DB2 Version 10 1 POWER3 processor based systems are not supported in DB2 Version 10 1 Chapter 3 Installing DB2 Connect server 35 e 7In AIX 6 1 there are two types of Workload Partitions WPARs system WPARs and application WPARs DB2 installation is supported only on a system WPAR AIX 6 1 also supports the ability to encrypt a JFS2 file system or set of files Software requirements e Use the bosboot command to switch to the 64 bit kernel To switch to a 64 bit kernel you require root authority and should enter the following commands In sf usr lib boot unix_64 unix In sf usr lib boot unix_64 usr 1lib boot unix bosboot a shutdown Fr e For application development and runtime considerations see the topics in Supported programming languages and compilers for database application development e You can download the latest IBM C Runtime Environment Components for AIX from the IBM AIX XL C and C support website e One of the following browsers is required to view online help and to run First Steps db2fs Firefox 3 0 and later Google Chrome Safari 4 0 e
92. NNECTIONS 4 000 MAX_COORDAGENTS 1 000 NUM_POOLAGENTS 1 000 The concentrator will keep open up to 4 000 concurrent sessions even though the gateway is only managing 1 000 transactions at a time 2 In the previous example worker agents will constantly form and break associations to logical agents Those agents that are not idle might maintain a connection to the database but are not participating in any particular transaction hence they are available to any logical agent application that requests a connection The case of XA transactions is somewhat different For this example assume that a TP Monitor is being used with a DB2 Connect gateway and an System z or IBM Power Systems database When an application requests a connection 148 DB2 Connect User s Guide the concentrator will either turn an inactive agent over to serve that request or create a new worker agent Assume that the application requests an XA transaction An XID is created for this transaction and the worker agent is associated with it When the application s request has been serviced it issues an xa_end and detaches from the worker agent The worker agent remains associated with the XID of the transaction It can now only service requests for transactions with its associated XID At this time another application might make a request for a non XA transaction Even if there are no other available worker agents the agent associated with the XID will not be ma
93. PPLICATIONS To view the information provided by the monitor at the application level issue the DB2 LIST DCS APPLICATIONS command It returns the following information for a TCP IP connection DB2 Connect to DB2 for z OS Auth Id Application Name Appl Host Application Id Handle NEWTON db2cli exe 7 G91A0D3A P8BC 060306212019 NEWTON db2cli exe 25 9 26 13 61 65289 060306213816 NEWTON db2cli exe 20 2002 91a 519 13 209 6bff fel4 4fbb 7684 060306213741 Auth Id The authorization ID that was used to log on to the IBM mainframe database server This identifies who is running the application Application Name The name of the application running at the client as known to DB2 Connect Only the first 20 bytes after the last path separator are available Appl Handle The agent that is executing on the DB2 Connect workstation You can use this element to link the database system monitor information to other diagnostic information The agent ID is also required when using the FORCE USERS command or API Host Application ID One of the following items e The DRDA correlation token CRRTKN for unprotected conversations e The unit of work id UOWID for two phase connections protected by the DRDA 3 Syncpoint Manager as used over TCP IP connections This unique identifier is generated when the application connects to the IBM mainframe database server You can use this element in conjunction with the Application ID to correlate the client an
94. RT and is also provided in the DSNL0041 message TCPPORT portnumber when the Distributed Data Facility DDF is started The DISplay DDF command could also be used If accessing a z OS data sharing group the domain name should map to the DB2 group dynamic VIPA address This address routes to the least loaded DB2 member To access a specific member use the specific DB2 member dynamic VIPA address and turn off sysplex routing Each member DSNLO004I message displays the member specific domain name Note A second port used for two phase commit resynchronization operations over TCP IP connections can be assigned by the server For example the DB2 for z OS bootstrap data set assigns a port number RESPORT to be used for resynchronization for inbound connections to DB2 for z OS only No service name need be defined for this DCS directory values You can specify the following information in the DCS directory Database name A user defined nickname for the IBM mainframe database server Use the same database name in both the DCS Directory Parameters table and the System Database Directory Parameters table Format 1 8 single byte alphanumeric characters including the number sign at sign dollar sign and underscore _ It cannot begin with an underscore or a number Target database name 102 DB2 Connect User s Guide The database on the IBM mainframe database server system as follows System z A DB2 for z OS subsystem id
95. S command to get a list of indoubt transactions and to interactively resolve any indoubt transactions Optional If you have transaction manager databases perform the following pre upgrade tasks to prepare your databases for upgrading a Ensure that the database to be upgraded does not contain any indoubt transactions by using the LIST INDOUBT TRANSACTIONS command to get a list of indoubt transactions and to interactively resolve any indoubt transactions b Refer to the Verify that your databases are ready for upgrading topic in the Upgrading to DB2 Version 10 1 to identify and resolve any problems before the actual upgrade c Refer to the Backing up databases before upgrading topic in the Upgrading to DB2 Version 10 1 to be able to upgrade them to a new upgraded system or restore them in the original pre upgrade system d Review the disk space requirements topic in the Upgrading to DB2 Version 10 1 to ensure that you have enough free disk space temporary table space and log space for database upgrading and increase table space and log file sizes if necessary e Linux only Review the Changing raw devices to block devices Linux topic in the Upgrading to DB2 Version 10 1 Optional If you have DB2 Connect federated databases refer to the Preparing to migrate to federated systems topic in the IBM WebSphere Information Integration Migrating to Federation Version 9 for details on pre upgrade tasks for
96. SELECT COMMIT or ROLLBACK statement issue EXECUTE IMMEDIATE to execute the statement instead of the PREPARE and EXECUTE sequence e ODBC applications use dynamic SQL You might use the CLI ODBC static profiling feature to improve performance This feature allows you to capture and convert ODBC calls into static statements stored in a database package The actual performance you will get depends on the complexity of your application Other SQL considerations 142 DB2 Connect User s Guide Using the Command Line Processor CLP is in general slower than having dynamic SQL in the program because the CLP must parse the input before submitting the SQL to the database engine The CLP also formats data when it is received which might not be necessary for your application SQL statements in an interpreted language such as REXX are substantially slower than the same SQL statements in a compiled language such as C There are two types of CONNECT statement called type 1 and type 2 With type 2 connect connecting to a database puts the previous connection into a dormant state but does not drop it If you later switch to a dormant connection you avoid the processing usage of loading libraries and setting up internal data structures For this reason using type 2 connect might improve performance for applications that access more than one database Connection management Connection pooling DB2 Connect server products such as DB2 Conne
97. Server Driver Package The following table lists the installed SDK for Java levels for DB2 database products according to operating system platform Operating System Platform SDK for Java level AIX SDK 7 HP UX for Itanium based SDK 6 systems Linux on x86 SDK 7 Linux on AMD64 EM64T SDK 7 Linux on zSeries SDK 7 Linux on POWER SDK7 Solaris Operating System SDK 7 Windows x86 SDK 7 Windows x64 SDK 7 Note 1 The SDK for Java software can be downloaded from the developerWorks Web page at http www ibm com developerworks java jdk index html For a 26 DB2 Connect User s Guide list of the supported levels of the SDK for Java see the table later in this section entitled DB2 Database for Linux UNIX and Windows support for SDKs for Java Note For Windows operating system platforms use the IBM Development Package for Eclipse downloads 2 DB2 GUI tools only run on Linux on x86 Linux on AMD64 EM64T Windows x86 and Windows x64 3 On Windows x86 and Linux on x86 e the 32 bit SDK is installed e 32 bit applications and Java external routines are supported 4 On all supported platforms except Windows x86 and Linux on x86 e 32 bit applications are supported e 32 bit Java external routines are not supported e 64 bit applications and Java external routines are supported Supported Java application development software The following table lists the supported levels of the SDK for Java
98. Specify the file name of your new SQLCODE mapping file in the parameter string of the DCS Directory 3 Edit the new SQLCODE mapping file The file can contain the following special types of lines amp amp The logical beginning of the file All lines before the first occurrence of amp amp are considered free form comments and ignored If the file contains nothing after amp amp no SQLCODE mapping is performed You can also turn off SQLCODE mapping with the NOMAP parameter as described previously As the first character on a line indicates a comment W As the only character on a line indicates that warning flags should be remapped By default the original warning flags are passed The W must be uppercase All other lines after amp amp must be either blank or mapping statements in the following form input_code output_code token_list The input_code represents one of the following values sqlcode The SQLCODE from the IBM mainframe database server U All undefined negative SQLCODEs those not listed in this file are 114 DB2 Connect User s Guide ccnn mapped to the specified output_code If no output_code is specified on this line the original SQLCODE is used This character must be uppercase All undefined positive SQLCODEs those not listed in this file are mapped to the specified output_code If no output_code is specified on this line the original SQLCODE is used This character must be uppercase
99. The SQLSTATE class code from the IBM mainframe database server nn is one of the following values 00 Unqualified successful completion 01 Warning 02 No data 21 Cardinality violation 22 Data exception 23 Constraint violation 24 Invalid cursor state 26 Invalid SOL statement identifier 40 Transaction Rollback 42 Access violation 51 Invalid application state 55 Object not in prerequisite state 56 Miscellaneous SQL or Product Error 57 Resource not available or operator intervention 58 System error The specified output_code is used for all SQLCODEs with this class code that are not specified explicitly in the mapping file If no output_code is specified on this line the original SQLCODE is mapped to itself with no tokens copied over The characters cc must be lowercase If the same input_code appears more than once in the mapping file the first occurrence is used The output_code represents the output SQLCODE If no value is specified the original SQLCODE is used If you specify an output code you can also specify one of the following value s The input SQLCODE plus the product ID ARI DSN or QSQ will be put into the SQLCA message token field The original SQLCODE is returned as the only token This option is designed to handle undefined SQLCODEs with the exception of 965 and 969 If 965 or 969 is the output_code the token list returned in the SQLERRMC field of the SQLCA includes the original SQLCODE
100. You can use the following utilities to administer DB2 Connect servers The Command Line Processor CLP or CLPPlus You can use the CLP or CLPPlus to issue SQL statements against an IBM mainframe database server database The SQL statements are issued on the database that you specify Note CLPPlus for administration is available in the IBM data server driver package and does not require DB2 Connect server modules to be installed Replication tools to set up and administer all replication programs for Q replication and SQL replication These tools are the Replication Center the ASNCLP command line program and the Replication Alert Monitor tool The Replication Center is available only on Linux and Windows operating systems Import and export utilities You can use these utilities to load import and to export data to and from a file on a workstation or an IBM mainframe database server database You can then use these files to import data into databases spreadsheets and other applications running on your workstation The Event Viewer and the Performance Monitor If you are running a DB2 Connect server product you can use these tools Using the Event Viewer you can view exception events that DB2 Connect logs Using the Performance Monitor you can monitor and manage the performance of DB2 Connect servers either locally or remotely Chapter 1 DB2 Connect overview 5 e The database system monitor utility You can use this utility to monito
101. a This demand must be met in order for a business to compete with its competitors and maintain continued growth Many of today s web and spreadsheet applications require access to enterprise data A reliable fast and secure connection to IBM mainframe databases must be established This connection must be constantly available and be able to handle the high connection demands under critical load conditions How can this connection be built High availability scenario A company has several workstations and application servers running on Windows Linux and UNIX These machines require access to data residing on several IBM mainframe databases Applications running on these machines demand fast and reliable connections to the databases The entire system is connected by an Ethernet network using TCP IP Chapter 9 Tuning 157 DB2 for VSE for VM for IBM i Power Systems DB2 for System z Servers z OS gt Ethernet MERIR Windows AIX Linux Figure 11 Sample network scenario For workstations and application servers to access IBM mainframe databases you need a connectivity component as an intermediary This component must provide a highly available robust and fast connection to IBM mainframe databases It must also be scalable to anticipate for future growth in connection volume Use the related links from this topic to see details regarding a solution using DB2 Connect and the automatic client
102. a DB2 pureScale environment DB2 Connect will spread the workload amongst the different DB2 subsystems comprising the data sharing group based on the system load and health information provided by the Workload Manager WLM It uses the Distributor to route connections Use the group IP address to connect to a group location DB2 Connect receives a prioritized list of DB2 members from the WLM Each Sysplex returns weighted priority information for each connection address that has capacity to run the work This list is then used by DB2 Connect to handle the incoming CONNECT requests by distributing them among the DB2 members with the best capacity to run work For load balancing the list of Sysplex weighted priority information is obtained during each connection This list is also used when determining where to send each transaction Note System z Distributed Data Facility DDF configuration does not need to be changed to take advantage of the DB2 Connect Sysplex exploitation Refer to DB2 for z OS Data Sharing Planning and Administration guide DB2 Connect also provides fault tolerance by attempting to connect to an alternate sysplex machine in the event of a connection failure An error will only be returned to the application if all known connections have failed DB2 Connect is designed with a transport tool With Sysplex enabled DB2 Connect routes connections using a transport member and associates it with a logical connection Chapter
103. access the same database one with SQLCODE mapping and the other without SQLCODE mapping Handling BiDi data The following section applies to z OS servers only This feature must not be enabled for a IBM DB2 for IBM i server as full BiDi support is already provided The following BiDi attributes are required for correct handling of BiDi data on different platforms e Numeral shape ARABIC versus HINDI e Orientation RIGHT TO LEFT versus LEFT TO RIGHT e Shaping SHAPED versus UNSHAPED Chapter 5 Administering 107 e Symmetric swapping YES or NO e Text type LOGICAL versus VISUAL Since defaults on different platforms are not the same problems appear when DB2 data is sent from one platform to another For example Windows platforms use LOGICAL UNSHAPED data while z OS data is usually in SHAPED VISUAL format Therefore without any support for BiDi attributes data sent from DB2 for z OS to DB2 Connect on Windows displays incorrectly When data is exchanged between DB2 Connect and a database on a server it is usually the receiver that performs conversion on the incoming data The same convention would normally apply to BiDi layout transformation also which is in addition to the usual code page conversion However currently no host DB2 database product supports BiDi specific CCSIDs or BiDi layout transformation Therefore DB2 Connect has been enhanced with the optional ability to perform BiDi layout transformation on data it i
104. ady installed The Administrator does have the authority to uninstall and reinstall the product to get around this issue e A non administrator has installed DB2 Connect and then a second non Administrator attempts to install DB2 Connect on the same system In this scenario the installation will fail and return an error message that the user must be an Administrator to install the product e An Administrator has installed DB2 Connect and then a non Administrator attempts to install DB2 Connect on the same system In this scenario the install will fail and return an error message that the user must be an Administrator to install the product An Administrator always has the authority to uninstall or reinstall e Non Administrator users cannot uninstall a DB2 product Those non Administrator users on a Windows Vista and later operating system can uninstall a DB2 product Chapter 3 Installing DB2 Connect server 67 Maintaining license keys Registering a DB2 Connect license key using the db2licm command Use the db21icm command to apply the license entitlement certificate also referred to as registering a license key Before you begin To complete this task you must have the appropriate license file 1ic To connect to a z OS server or a System i server you must register a DB2 Connect license key Retrieve the license file from your Passport Advantage distribution for example db2conpe lic then copy the license file to
105. alled before attempting to install a DB2 product The VS2010 runtime library is needed on the operating system before the DB2 product can be installed The VS2010 runtime library is available from the Microsoft runtime library download website There are two choices choose vcredist_x86 exe for 32 bit systems or vcredist_x64 exe for 64 bit systems System shortcuts must be changed to user shortcuts for the non Administrator install Moreover since services are required to install any of the DB2 Connect products but cannot be created without administrative authority services that would be automatically started are run as processes when a non administrator installs The following scenarios are installation situations that you might encounter in an environment where both administrator and non administrator installations exist e A non Administrator has installed DB2 Connect and then an Administrator attempts to install DB2 Connect on the same system The Administrator will get a message that the product is already installed The Administrator does have the authority to uninstall and reinstall the product to get around this issue e A non administrator has installed DB2 Connect and then a second non Administrator attempts to install DB2 Connect on the same system In this scenario the installation will fail and return an error message that the user must be an Administrator to install the product e An Administrator has installed DB2 Connect a
106. an ESCON adapter with TCP IP the segment size should always be 4096 Finally for TCP IP networks the TCP Send and Receive buffer sizes should be set higher than 32768 A value of 65536 is generally best Note Establishing a connection from the gateway to the server outbound connection is much more expensive than establishing a connection from a client to the gateway inbound connection In an environment where thousands of clients frequently connect to and disconnect from the server through the gateway a substantial amount of processing time is spent establishing outbound connections DB2 Connect provides connection pooling over TCP IP When a client requests disconnection from the server the gateway drops the inbound connection with the client but keeps the outbound connection to the server in a pool When a new client comes into the gateway to request a connection the gateway provides an existing one from the pool thus reducing the overall connection time and saving the high CPU connect cost on the server A summary of network performance tuning methods is provided in Table 28 Table 28 Network performance tuning methods What to Look For Example Setting Notes Deliberate Delays Delay parameters on network devices Set to 0 Defaults are usually higher queue depth is 150 Buffers IOBUF parameter Set up to 3992 Particularly useful for ESCON or other channel adapter Buffers RUSIZE Op
107. an be unmounted as required From this window you can view the installation prerequisites and the release notes or you can proceed directly to the installation 4 Once you have initiated the installation proceed through the DB2 Setup wizard installation panels and make your selections Installation help is available to guide you through the DB2 Setup wizard Click Help to invoke the online help You can click Cancel at any time to exit the installation DB2 files will only be copied to your system once you have clicked Finish on the last DB2 Setup Chapter 3 Installing DB2 Connect server 45 wizard installation panel Once completed the DB2 Connect server product is installed using the opt 1BM db2 V9 8 default installation path If you are installing on a system where this directory is already being used the DB2 Connect product installation path will have _xx added to it where xx are digits starting at 01 and increasing depending on how many DB2 copies you have installed You can also specify your own DB2 database product installation path Results National Language Packs can also be installed by running the db2setup command from the directory where the National Language Pack resides after a DB2 Connect product has been installed The installation logs db2setup 1og and db2setup err will be located by default in the tmp directory You can specify the location of the log files If you want your DB2 database product to have acc
108. an currently only be activated through the operating system by setting TCPRCVBUFRSIZE to any value above 64K If you are using a remote IBM data server client to access an IBM mainframe DB2 database through a DB2 Connect server workstation you can enable window scaling on the client as well By the same token you can also enable window scaling between a remote IBM data server client and a workstation DB2 server when no IBM mainframe DB2 database is involved 156 DB2 Connect User s Guide While window scaling is designed to enhance network performance it is important to note that the expected network performance improvement does not always materialize Interaction among factors such as the frame size used for the ethernet or token ring LAN adapter the IP MTU size and other settings at routers throughout the communication link could even result in performance degradation once window scaling has been enabled Therefore by default window scaling is disabled with both the send and receive buffers set to 64K You should be prepared to assess the impact of turning on window scaling and perform any necessary adjustments to the network For an introduction to tuning the network for improved network performance refer to www networking ibm com nhd webnav nsf pages netdocs html High availability and load balancing for host database connectivity In today s information technology market there is a high demand for around the clock availability of dat
109. and DB2 LAN based databases when accessed via DB2 Connect are XA compliant RMs Therefore any TP monitor which has an XA compliant TM can use host IBM Power Systems and LAN based DB2 databases within business applications executing transactions Chapter 1 DB2 Connect overview 11 12 DB2 Connect User s Guide Chapter 2 Upgrading to the latest version of DB2 Connect Upgrading to a new version or release of DB2 Connect might require upgrading your environment components if you want them to run on the new release These components are DB2 Connect servers DB2 servers DB2 clients and database applications For example if you have an existing environment using an earlier version or release of DB2 Connect and you want to install the latest version or release of DB2 Connect then you can upgrade your DB2 Connect server and you might need to upgrade other components in your environment DB2 Connect servers supports the upgrading of DB2 Connect instances and any existing transaction manager and DB2 Connect federated databases created on previous versions of DB2 Connect servers The upgrade process consists of all the tasks that you need to perform to have your environment running successfully on a new release The upgrading of each of the components in your environment to the latest version or release of DB2 Connect requires that you perform different tasks e Upgrading DB2 Connect servers on page 16 involves upgrading your exist
110. and SQLJ and also Call Level Interface CLI applications that use dynamic SQL CLI applications should also not use KEEPDYNAMIC as the concentrator depends on statements being re prepared on each transaction boundary e Dynamic prepare requests from embedded dynamic SQL applications will be rejected Your applications should be altered so as to either use static SQL or to use the CLI for dynamic SQL statements e If the connection concentrator is ON the inbound request to the DB2 Connect server cannot use SSL However the outbound request to the target database server can use SSL If the connection concentrator is OFF both the inbound and the outbound requests can use SSL When working with DB2 Version 9 or Version 8 FixPak 13 or higher to enable DB2 Connect concentrator support requires IBM Power Systems Version 5 Release 4 PTF S123726 Otherwise only the XA portion of the connection concentrator is supported Activating the connection concentrator The database manager configuration parameter max_coordagents sets the maximum number of logical agents You can activate the concentrator feature by setting the value of max_connections to any number greater than the default The default value for max_connections is equivalent to the value of max_coordagents Because each application will have one logical agent max_connections actually controls the number of applications that can be connected to the database instance while max_coordagent
111. and deploy application solutions with smaller footprints for your business users DB2 Connect license files are required For more information about DB2 Connect client refer to Client and server connection options Key Concepts Client and server connection options Copyright IBM Corp 1993 2012 DB2 Connect server provides a single point of connectivity to numerous workstations supporting a variety of applications However it adds additional processing time to applications accessing DB2 for z OS data and increases the elapsed time of those applications Starting with DB2 Connect Version 8 and later DB2 Connect clients use the DRDA protocol natively to connect directly to DB2 for z OS and DB2 for IBM i Advantages of using DB2 Connect server DB2 Connect server is advantageous in the following situations e For two phase commits if you are using transaction managers that use a dual transport model e For Homogeneous Federation Advantages of using DB2 Connect client You can replace DB2 Connect server with DB2 Connect client choosing from among the various IBM data server drivers the IBM Data Server Runtime Client or the IBM Data Server Client DB2 Connect client and drivers offer functionality that is equivalent or superior to that of DB2 Connect server and includes the following other advantages e Enhanced Performance You can achieve better performance due to less network traffic and code paths DB2 Connect clients simp
112. ant to take advantage of project resource controls etc project consult your Solaris documentation Chapter 3 Installing DB2 Connect server 31 Before you begin You must have root authority to modify kernel parameters To use the db2osconf command you must first install the DB2 database system The db2osconf utility can only be run from DB2DIR bin where DB2DIR is the directory where you installed your DB2 database product You must restart your system after modifying kernel parameters Procedure To set a kernel parameter Add a line at the end of the etc system file as follows set parameter_name value For example to set the value of the msgsys msginfo_msgmax parameter add the following line to the end of the etc system file set msgsys msginfo_msgmax 65535 What to do next After updating the etc system file restart the system DB2 Connect server products installation and configuration overview Setting up a DB2 Connect server product such as DB2 Connect Enterprise Edition is a multi step process DB2 Connect server products are often installed with hundreds or thousands of clients connecting to IBM mainframe database servers For this reason it is recommended to use a test installation After the test configuration has proven stable you can use it as the template for an unattended installation of DB2 Connect and your clients across your organization The typical steps to installing and configuring a DB2 C
113. apply to DB2 Connect servers Upgrade support and restrictions for DB2 servers and clients also apply when you upgrade your DB2 Connect server e Review upgrade essentials for DB2 servers to determine additional changes that impact your upgrade and how to address any issues Refer to the Upgrade essentials for DB2 Servers topic in Upgrading to DB2 Version 10 1 e Review upgrade essentials for clients especially connectivity support between clients and DB2 servers Connections to the latest version or release of DB2 Connect servers from a client release two or more versions earlier are not supported Refer to the Upgrade essentials for clients topic in Upgrading to DB2 Version 10 1 e Review the need to opt for DB2 Connect client instead of DB2 Connect server to receive equivalent or superior function You can reduce complexity improve performance and deploy application solutions with smaller footprints For details see the topic about client server connection options Upgrade recommendations for DB2 Connect The last two versions of the clients can connect to the latest version or release of DB2 Connect servers The only restriction is that new features are not available to the clients from the previous versions and releases However it is not likely that you need access to these new features because your existing applications do not use them If you choose to upgrade your clients first you need to be aware that there
114. arches is freed With caching a shared directory cache is built during db2start processing and freed when DB2 stops This cache is used by all DB2 server processes db2agent Also a private application directory cache is built when an application issues its first connect to a database and freed when the application ends Each cache provides an image of the system database directory the database connection services directory and the node directory The cache reduces connect costs by eliminating directory file I O and minimizing directory searches If a cached directory is updated the changes are not immediately propagated to the caches If a directory entry is not found in a cache the original directory is searched Caching increases the private memory that is needed for the life of an application Without caching this memory is needed only when a directory lookup is processed Overall use of shared memory by DB2 increases slightly because directory information that is shared among database agents is moved to shared memory The size of the memory required for a cache depends on the number of entries defined in each directory NUMDB The behavior of DB2 Connect was unaffected by the NUMDB configuration parameter in previous versions however this changed starting with Version 8 This parameter indicates the maximum number of databases the clients can connect to through the DB2 Connect server More specifically the maximum number of diffe
115. are known limitations about the support for connectivity from a current version or release of the client to DB2 Connect servers from two versions ago Check the current version or release of the incompatibilities with previous releases see if these limitations apply to your application in order to take necessary actions Perform the pre and post upgrade tasks to ensure a successful upgrade 14 DB2 Connect User s Guide Pre upgrade tasks for DB2 Connect servers To successfully upgrade your DB2 Connect servers preparation is required to address any issues that may exist Procedure Perform the following pre upgrade tasks for DB2 servers that also apply to DB2 Connect servers 1 Review the Upgrade essentials for DB2 Connect on page 14 to identify the changes or restrictions that can affect your upgrade and learn how to address any issues before upgrading Refer to the Backing up DB2 server configuration and diagnostic information topic in Upgrading to DB2 Version 10 1 to have a record of your current configuration that you can compare with the configuration after the upgrade You can also use this information to create new instances or databases using the same configuration that you had before upgrading Optional If you enabled the Syncpoint Manager SPM functionality on your DB2 Connect server ensure that the DRDA sync point managers do not contain any indoubt transactions by using the LIST DRDA INDOUBT TRANSACTION
116. ases and DBMSs For example you can perform a UNION operation between a DB2 table and an Oracle view Supported DBMSs include members of the DB2 Family such as DB2 Database for Linux UNIX and Windows DB2 for z OS and DB2 for i and Oracle Multivendor support is available when using DB2 Connect in conjunction with InfoSphere Federation Server Distributed request provides location transparency for database objects If information in tables and views is moved references to that information called nicknames can be updated without any changes to applications that request the Chapter 5 Administering 99 information Distributed request also provides compensation for DBMSs that do not support all of the DB2 SQL dialect or certain optimization capabilities Operations that cannot be performed under such a DBMS such as recursive SQL are run under DB2 Connect Distributed request function in a semi autonomous manner For example DB2 queries containing references to Oracle objects can be submitted while Oracle applications are accessing the same server Distributed request does not monopolize or restrict access beyond integrity and locking constraints to Oracle or other DBMS objects Implementation of the distributed request function consists of a DB2 Connect instance a database that will serve as the federated database and one or more remote data sources The federated database contains catalog entries identifying data sources an
117. at a database connection has been reestablished following the communication failure The hostname or IP address and service name or port number are returned The IBM Data Server Client only returns the error for the original communications failure to the application if the reestablishment of the client communications is not possible to either the original or alternative server The following considerations involving alternate server connectivity in a DB2 Connect server environment should also be noted e When using a DB2 Connect server for providing access to an IBM mainframe database on behalf of both remote and local clients confusion can arise regarding alternate server connectivity information in a system database directory entry To minimize this confusion consider cataloging two entries in the system database directory to represent the same IBM mainframe database Catalog one entry for remote clients and catalog another for local clients e Any SYSPLEX information that is returned from a target DB2 for z OS server is kept only in cache at the DB2 Connect server Only one alternate server is written to disk When multiple alternates or multiple active servers exist the information is only maintained in memory and is lost when the process terminates Administering DB2 Connect systems Overview Access DB2 data from remote clients The IBM data server client provides a runtime environment that enables client applications to access one
118. at it is not recognized by the DB2 server Specifically you should try to avoid using IMPLICIT CONTEXTUAL and IMPLICIT RIGHT TO LEFT CCSIDs when a different string type can be used CONTEXTUAL CCSIDs can produce unpredictable results if the SQL query contains quoted strings Avoid using quoted strings in SQL statements and use host variables instead when possible If a specific bidirectional CCSID is causing problems which cannot be rectified by following these recommendations then you should set the environment variable or registry value DB2BIDI to NO Parameter string specifications The following examples show samples of DCS parameters each line is a set of parameters NOMAP u username sql1ib map dcs1new map D D gt INTERRUPT_ENABLED NOMAP D INTERRUPT_ENABLED SYSPLEX LOCALDATE YYMMDD Alternatively you can accept the defaults by not specifying a parameter string Note You must use the operating system escape character backslash when using CLP from the operating system s command line on UNIX systems because of the need to specify two pairs of double quotation marks when specifying the LOCALDATE mask in the parameter string For example db2 catalog dcs db x as y parms LOCALDATE YYMMDD This results in the following DCS directory entry DCS 1 entry Local database name X Target database name Y Application requestor name DCS parameters Comment DCS directory release level gt
119. at the row has not changed between the time the SELECT is completed and the UPDATE DELETE is issued If this level of concurrency is not important to your application an alternative is to use a DELETE or UPDATE with search criteria based on the values returned from a non updateable SELECT For read only SELECT specify FOR FETCH ONLY except under VM and VSE where it is not supported Static and dynamic SQL Use static SQL as much as possible It avoids runtime SQL section preparation and ambiguous cursors If dynamic SQL cannot be avoided you can do the following to minimize the network traffic and improve performance e If the statement is a SELECT and must be prepared perform PREPARE INTO SOLDA The SQLDA should be allocated to the full size needed for your settings If the maximum number of columns is x and is expected to stay that way allocate an SQLDA with x SOLVARs If the number of potential columns is uncertain and memory is not a problem use the maximum number of SQLVARs 256 If the SQLDA allocation is not big enough to store the returning SQLDA the program must issue another DESCRIBE with a big enough SQLDA to store the result again This would increase the network traffic Do not use the PREPARE and DESCRIBE sequence Using the PREPARE INTO statement provides better performance e Execute statically bound SQL COMMIT or ROLLBACK statements instead of dynamic COMMIT or ROLLBACK statements e If it is not a
120. ate ic bat file update ic bat Results The DB2 Information Center restarts automatically If updates were available the Information Center displays the new and updated topics If Information Center updates were not available a message is added to the log The log file is located in doc eclipse configuration directory The log file name is a randomly generated number For example 1239053440785 1og Appendix A Overview of the DB2 technical information 173 Manually updating the DB2 Information Center installed on your computer or intranet server If you have installed the DB2 Information Center locally you can obtain and install documentation updates from IBM About this task Updating your locally installed DB2 Information Center manually requires that you 1 Stop the DB2 Information Center on your computer and restart the Information Center in stand alone mode Running the Information Center in stand alone mode prevents other users on your network from accessing the Information Center and allows you to apply updates The Workstation version of the DB2 Information Center always runs in stand alone mode 2 Use the Update feature to see what updates are available If there are updates that you must install you can use the Update feature to obtain and install them Note If your environment requires installing the DB2 Information Center updates on a machine that is not connected to the internet mirror the update site to a l
121. ated approach to reducing resource consumption for very high volume OLTP On line Transaction Processing applications Connection concentrator takes the concept of an agent and splits it into two entities e Logical agent which represents an application connection e Coordinating agent which owns the DB2 connection and thread and executes application requests When a new application attempts a connection to the host it is assigned a logical agent To pass SQL to the database a coordinating agent is required and is assigned as soon as a new transaction is initiated The key to this architecture is the fact that the coordinating agent is e Disassociated from the logical agent e Returned to the pool when transaction completes due to a commit or rollback Another key feature is the method of assigning coordinating agents to new transactions in a DB2 pureScale environment DB2 Connect implements a sophisticated scheduling algorithm that uses System z Work Load Manager WLM information This information is used to distribute workload across members of a data sharing group according to criteria set up in WLM WLM is not only aware of the load on each member but also their availability This allows DB2 Connect to transparently relocate work away from failed or overloaded members to members that are up and underutilized DB2 Connect connection concentrator is activated Chapter 9 Tuning 145 when you set the number of maximum logical agents
122. ator however permits a many to one relationship between connections and EDUs That is the relationship of connections X to EDUs Y is now X gt Y The connection concentrator splits the agent into two entities a logical agent and a worker agent Logical agents represent an application but without reference to a particular EDU The logical agent contains all the information and control blocks required by an application If there are n applications connected to the server there will be logical agents on the server Worker agents are physical EDUs that execute application requests but which have no permanent attachment to any given application Worker agents associate with logical agents to perform transactions and at the transaction boundary end the association and return to the available pool An entity known as the dispatcher assigns worker agents to logical agents Limitations in the number of open file handles on certain computing platforms might result in more than one scheduler instance Restrictions for the connection concentrator There are a number of important restrictions to the use of the DB2 Connect server concentrator Review the following information in its entirety before attempting to use the connection concentrator on your system General restrictions e The concentrator relies on the TCP IP protocol to establish inbound connections from local and remote clients Only inbound connections using TCP IP or Local IPC
123. base application driver being used Run the application program Copyright IBM Corp 1993 2012 127 128 DB2 Connect User s Guide Chapter 8 Security Trusted connections through DB2 Connect Some DB2 database servers support trusted contexts A trusted context allows the database administrator to among other things define conditions under which a client application will be allowed to create a trusted connection A trusted connection is allowed to do things that a normal connection cannot There are two types of trusted connection implicit and explicit When you create a connection whether you get an explicit trusted connection an implicit trusted connection or a regular connection depends on whether you ask for a trusted connection and whether the connection meets the criteria defined in the trusted context on the server as summarized in Table 19 Table 19 What type of connections result from different combinations of actions The connection meets the The connection does not server s criteria for being meet the server s criteria for trusted being trusted You request that the Explicit trusted connection Regular connection and connection be trusted warning SQL20360W SQLSTATE 01679 is returned You do not request that the Implicit trusted connection Regular connection connection be trusted An implicit trusted connection is identical to a regular connection except that it grants temporary role p
124. base for all clients the alternate server location has to be specified at the DB2 Connect server side The alternate server is ignored if it is set at the client instance 90 DB2 Connect User s Guide For example suppose a IBM mainframe database is catalogued using a database alias of db1 at a DB2 Connect server 1 with a hostname of db2connl and a port number of 122 The database administrator would like to specify an alternate DB2 Connect server S2 at hostname db2conn2 with a port number of 123 Here is the command the database administrator would run at the DB2 Connect server S1 db2 update alternate server for database dbl using hostname db2conn2 port 123 After you have specified the alternate DB2 Connect server location for database alias dbl at DB2 Connect server S1 the alternate server location information is returned to the IBM Data Server Client as part of the connection process If communication between the IBM Data Server Client and the DB2 Connect server S1 is lost for any reason typically a communication error such as SQL code 30081 or SQL code 1224 the IBM Data Server Client will attempt to reconnect to db1 through either the original DB2 Connect server S1 or the alternate DB2 Connect server S2 alternating the attempts between the two servers The time interval between attempts starts off rapidly then gradually lengthens with each attempt Once a connection is successful the SOL code 30108 is returned to indicate th
125. bnd directory under the installation directory typically sq11ib for Windows Each file is specific to a server Chapter 5 Administering 93 Procedure e To bind the utilities and applications to the IBM mainframe database server connect to the IBM mainframe server and use the following example as a template connect to dbalias user userid using password bind path bnd ddcsmvs 1st blocking all sqlerror continue messages mvs msg grant public connect reset where path corresponds to the DB2PATH registry value e To bind database utilities to a DB2 database use the command line processor 1 Change to the bnd directory which is x sq 1ib bnd where x represents the drive where you installed DB2 2 To connect to the database enter the following commands in the Command Center or the Command Line Processor connect to database_alias where database_alias represents the alias of the database to which you want to connect 3 Enter the following commands in the Command Line Processor bind db2ubind 1lst messages bind msg grant public bind db2cli lst messages clibind msg grant public In this example bind msg and clibind msg are the output message files and EXECUTE and BINDADD privileges are granted to public 4 Reset the connection to the database by entering the following command connect reset Note 1 The db2ubind 1st file contains the list of bind bnd files required to create the packages for the da
126. ccount will also be granted these privileges If the install grants the privileges some of them will only be effective on first log on by the account that was granted the privileges or upon reboot Extending the Active Directory Schema for LDAP directory services Windows If you plan to use the Lightweight Directory Access Protocol LDAP directory server feature with Windows Server 2003 you have to extend the Active Directory schema to contain DB2 object classes and attribute definitions using the db2schex command About this task Extending the directory schema before installing DB2 database products and creating databases provide the following benefits e The default DB2 instance created during the installation is cataloged as a DB2 node in Active Directory provided that the installation user ID had sufficient privileges to write to Active Directory e Any databases created after installation is automatically cataloged into Active Directory Procedure To extend the directory schema 1 Log onto any machine that is part of the Windows domain with a Windows user account that has Schema Administration authority 2 Run the db2schex command from the installation DVD You can run this command without logging off and logging on again as follows runas user MyDomain Administrator x db2 Windows utilities db2schex exe where x represents the DVD drive letter Chapter 3 Installing DB2 Connect server 53 What to do next
127. cessing time This rule doesn t apply to the trusted context system authorization ID If the trusted context system authorization ID doesn t require authentication when you switch to it then it is not authenticated even if a password is provided Chapter 8 Security 133 DB2 Connect authentication considerations As a DB2 Connect administrator in cooperation with your System z or IBM Power Systems database administrator you can determine where user names and passwords are validated For example e At the client e At the System z or IBM Power Systems server e Single sign on and validation through a third party system Kerberos Note If the remote client has not specified an authentication type the client will default to SERVER_ENCRYPT If this type is not accepted by the server the client will attempt to try using an appropriate value returned from the server To help optimize performance always specify the authentication type at the client to avoid this extra network flow Starting with DB2 Connect Version 8 2 2 equivalent to Version 8 1 FixPak 9 the gateway is no longer a passive participant during authentication negotiation Instead the gateway takes an active role The authentication type specified in the database directory entry at the gateway overrides the authentication type cataloged at the client The client gateway and server must all specify compatible types If the cataloged authentication type at the gateway has n
128. complete and correct or verifying for TCP IP connections that the correct port number and hostname have been specified e Both the IBM mainframe server database administrator and the Network administrators have utilities available to diagnose problems 5 Do you have the level of authority required by the IBM mainframe server database management system to use the IBM mainframe server database e Consider the access authority of the user rules for table qualifiers the anticipated results 6 If you attempt to use the Command Line Processor CLP to issue SQL statements against a IBM mainframe database server are you unsuccessful e Did you follow the procedure to bind the CLP to the IBM mainframe database server If the checklist does not guide you to a resolution contact IBM Support Problems encountered after an initial connection If DB2 Connect can no longer connect successfully troubleshoot the problem by answering a set of questions that are structured into a checklist Answering the following questions can help you to identify the source of the connection problem 1 Are there any special or unusual operating circumstances e Is this a new application e Are new procedures being used e Are there recent changes that might be affecting the system For example have any of the software products or applications been changed since the application or scenario last ran successfully e For application programs what applicati
129. ct Enterprise Edition often provide database connections for thousands of simultaneous client requests Establishing and severing connections to the database server can be a very resource intensive process that adversely affects both database server and DB2 Connect server performance To reduce this processing usage DB2 Connect server products use connection pooling to maintain open connections to the database in a readily accessible pool This problem is especially evident in web environments where each visit to a web page can require building a new connection to the database server performing a query and terminating a connection Most applications based on web technologies execute large volume of short transactions A typical web transaction is executed as part of its own connection In other words executing a transaction means establishing a database connection and then terminating this connection only after a few SQL statements This process of establishing and tearing down a connection is very costly It involves creation of a DB2 Connect agent establishing a network connection between this agent and the DB2 server and creation of a DB2 thread on the server For longer running connections these costs are amortized over all of the transactions executed on this connection but for a typical web transaction these costs will typically exceed the cost of executing the transaction itself Connection pooling is a technique that allows reuse of an e
130. ct product on Solaris the following requirements must be met Table 7 Solaris installation requirements Operating System Hardware Solaris 10 Update 9 Solaris x64 Intel 64 or AMD64 e 64 bit kernel Solaris 10 Update 9 UltraSPARC or SPARC64 processors e 64 bit kernel 1 Support is only for the DB2 product to be installed on local zones Installation on the global zone is not supported by the DB2 product at this time Operating system requirements Recommended amp Security Patches needs to be obtained The J2SE Solaris Operating System Patch Clusters are also required The Fujitsu PRIMEPOWER patches for the Solaris operating system can be downloaded from FTSI at http download ftsi fujitsu com For an additional list of issues that can affect DB2 database systems on Solaris refer to www ibm com support docview wss amp uid sw 21257606 DB2 database products support Solaris ZFS filesystems and Logical Domains LDoms For details about virtualization technology supported by DB2 products see http www ibm com developerworks wikis display im DB2 Virtualization Support Software requirements e SUNWIibC software is required to install DB2 Connect on Solaris e A browser is required to view online help Communication requirements You can use TCP IP e For TCP IP connectivity no additional software is required e DB2 Connect is supported on Sun Cluster 2 2 if The protocol to the host is TCP IP T
131. ctory and run the help_end script help_end Note The help_end script contains the commands required to safely stop the processes that were started with the help_start script Do not use any other method to stop the help_start script 7 Restart the DB2 Information Center e On Windows click Start gt Control Panel gt Administrative Tools gt Services Then right click DB2 Information Center service and select Start e On Linux enter the following command etc init d db2icdvl start Results The updated DB2 Information Center displays the new and updated topics DB2 tutorials The DB2 tutorials help you learn about various aspects of DB2 database products Lessons provide step by step instructions Before you begin You can view the XHTML version of the tutorial from the Information Center at http publib boulder ibm com infocenter db2luw v10r1 Appendix A Overview of the DB2 technical information 175 Some lessons use sample data or code See the tutorial for a description of any prerequisites for its specific tasks DB2 tutorials To view the tutorial click the title pureXML in pureXML Guide Set up a DB2 database to store XML data and to perform basic operations with the native XML data store DB2 troubleshooting information A wide variety of troubleshooting and problem determination information is available to assist you in using DB2 database products DB2 documentation Troubleshootin
132. d If a further connection request is received for ALIASB DB2 Connect performs the following actions 1 It tries the highest priority server from the cached list of addresses based on the priority information that was returned by DB2 for z OS This strategy is always used by DB2 Connect and it is by this means that load balancing is achieved 2 If this connection attempt fails then the other addresses in the list are tried in descending order of priority as returned by DB2 for z OS This is how DB2 Connect exploits the Sysplex information to achieve fault tolerance 3 If all other attempts to connect fail then DB2 Connect will try the connection to ALIASB using the address contained in the cataloged node directory The db2pd command with the sysplex parameter db2pd sysplex can be used for retrieving information about servers associated with a Sysplex environment Configuration requirements for Sysplex Sysplex exploitation will not be used for a given database unless the DCS directory entry for that database contains Sysplex not case sensitive in the 6th positional parameter 82 DB2 Connect User s Guide Configuring connections to IBM mainframe database servers You can manually configure your TCP IP connection between a DB2 Connect server and a IBM mainframe database using the DB2 command line processor CLP For details on configuring connection using db2dsdriver cfg see the topic about db2dsdriver configuration file
133. d communication address Outbound communication protocol Inbound communication address First database connect timestamp Host response time sec ms Time spent on gateway processing Last reset timestamp Rows selected Number of SQL statements attempted Failed statement operations Commit statements Rollback statements Inbound bytes received Outbound bytes sent Outbound bytes received Inbound bytes sent Number of open cursors Application idle time UOW completion status Previous UOW completion timestamp 09150F74 B6A4 991215152824 0001 SMITH db2bp 1 waiting for request 12 15 2001 10 29 06 707086 sys143 SQL06010 AIX TCP IP 850 49074 smith G9150F74 B6A5 991215152825 0000 MVSDB DCSDB GILROY DSNOQ5012 500 9 21 21 92 5021 TCP IP 9 21 15 116 46756 12 15 2001 10 28 24 596495 0 000000 0 000000 bow wo wow bt ob ob wou Orono 404 140 103 287 0 1 minute and 32 seconds 12 15 2001 10 28 25 592631 Chapter 6 Monitoring DB2 Connect server 121 UOW start timestamp 12 15 2001 10 29 06 142790 UOW stop timestamp Elapsed time of last completed uow sec ms 0 034396 Execute Immediate 12 15 2001 10 29 06 142790 12 15 2001 10 29 06 707053 Most recent operation Most recent operation start timestamp Most recent operation stop timestamp Statement Execute Immediate Section number 203 Application creator NULLID Package name SQLC2C07 SQL compiler cost estimate in timerons 0 SQL com
134. d if that is not supported English Changing the interface language for a DB2 database product on Windows requires that you change the default language setting for your Windows operating system Procedure To change the DB2 database product interface language on Windows operating systems 1 Through the Control Panel select Regional and Language Options 2 On the Regional Options tab under Standards and formats select the appropriate language On Windows 2008 and Windows Vista or higher use the Formats tab for this step 3 On the Regional Options tab under Location select the location that corresponds to the appropriate language 4 On the Advanced tab under Language for non Unicode programs select the appropriate language On Windows 2008 and Windows Vista or higher on the Administrative tab under Language for non unicode programs click Change system locale and select the appropriate language You will then be asked to reboot click Cancel 5 On the Advanced tab under Default user account settings check the Apply all settings to the current user account and to the default user profile box On Windows 2008 and Windows Vista or higher on the Administrative tab under reserved accounts click Copy to reserved accounts and check the accounts that you want to copy the language settings to 6 You will be asked to reboot before these changes come into effect What to do next Refer to your operating system help for additional
135. d server parts of the application information LIST DCS APPLICATIONS SHOW DETAIL If the DB2 LIST DCS APPLICATIONS SHOW DETAIL command format is specified additional information is shown including Dy Auth Id Application Name Appl Client Application Id Handle NEWTON db2cli exe 37 2002 91a 519 13 209 6bff fel4 4fbb 8196 060306214224 Seq Client Client Client Client Host Application Id DB Alias Node Release Codepage 00001 MDB SAYYID SQLO9000 1252 G91A0D3A P982 060306214231 Seq Host DB Name Host Release 00001 MEXICO DSNO8015 J Chapter 6 Monitoring DB2 Connect server 123 Client Application ID Uniquely identifies the application connected to the DB2 Connect workstation There are different formats for the application ID which are dependent on the communication protocol between the client and the DB2 Connect workstation This value lets you correlate connections from clients to the DB2 Connect workstation and from the DB2 Connect workstation to the IBM mainframe database server Client Sequence no Seq The client sequence number is the transaction sequence number It is used to help correlate a transaction spread over different systems Client DB alias The alias of the database provided by the application to connect to the database This element can be used to identify the actual database that the application is accessing The mapping between this name and the database name could be done by using the database director
136. d their characteristics A data source consists of a DBMS and data Applications connect to the federated database just like any other DB2 database DB2 Connect federated database is not licensed for managing user data Its sole purpose is to contain information about data sources After a federated system is set up the information in data sources can be accessed as though it were in one large database Users and applications send queries to one federated database which then retrieves data from DB2 Family and Oracle systems as needed User and applications specify nicknames in queries these nicknames provide references to tables and views located in data sources From an end user perspective nicknames are similar to aliases Many factors can affect the performance of distributed requests The most critical factor is to ensure that accurate and up to date information about data sources and their objects is stored in the federated database global catalog This information is used by the DB2 optimizer and can affect decisions to push down operations for evaluation at data sources Updating database directories Before you begin Before updating these directories you should configure communications on the IBM mainframe database server and workstations About this task DB2 Connect uses the following directories to manage database connection information e system database directory which contains name node and authentication information f
137. d you are doing the bind again then having any of the previously listed authorities will allow you to complete the bind B If your original bind was done by someone else and you are doing the second bind then you will require either the SYSADM or the SYSCTRL authorities to complete the bind Having just the BINDADD and the CREATE IN COLLECTION NULLID authorities will not allow you to complete the bind It is still possible to create a package if you do not have either SYSADM or SYSCTRL privileges In this situation you would need the BIND privilege on each of the existing packages that you intend to replace VM The authorization required is DBA authority If you want to use the GRANT option on the bind command to avoid granting access to each DB2 Connect package individually the NULLID user ID must have the authority to grant authority to other users on the following tables e system syscatalog e system syscolumns e system sysindexes e system systabauth e system syskeycols e system syssynonyms e system syskeys e system syscolauth e system sysuserauth On the VSE or VM system you can issue grant select on table to nullid with grant option IBM Power Systems CHANGE authority or higher on the NULLID collection Issue commands similar to the following commands db2 connect to DBALIAS user USERID using PASSWORD db2 bind path ddcsmvs 1st blocking all sqlerror continue messages ddcsmvs msg grant public db2 connect reset
138. de available to the second application It is considered active The second application will have a new worker agent created for it When that second application completes its transaction its worker agent is released into the available pool Meanwhile other applications requesting the transaction associated with the first agent s XID can attach and detach from that agent which executes its dedicated XA transaction for them Any application requesting that particular transaction will be sent to this worker agent if it is free The worker agent will not be released back into the general pool until an application issues a transaction boundary call not xa_end For instance an application might end the transaction with xa_commit at which point the worker agent drops its association with the XID and returns to the available pool At this point any requesting application can use it for either another XA or a non XA transaction Connection pooling and connection concentrator While connection pooling and connection concentrator seem to have similarities they differ in their implementation and address different issues Connection pooling helps reduce the processing usage of database connections and handle connection volume Connection concentrator helps increase the scalability of your DB2 for z OS and DB2 Connect solution by optimizing the use of your host database servers When using connection pooling the connection is only available for
139. e DRDA is a set of protocols that permits multiple database systems which includes both IBM and not IBM systems as well as application programs to work together 96 DB2 Connect User s Guide Any combination of relational database management products that use DRDA can be connected to form a distributed relational database management system DRDA coordinates communication between systems by defining what must be exchanged and how it must be exchanged Unit of work A unit of work UOW is a single logical transaction It consists of a sequence of SQL statements in which either all of the operations are successfully performed or the sequence as a whole is considered unsuccessful Distributed unit of work A distributed unit of work DUOW also known as multisite update involves more than one database server within a unit of work A DUOW has the following characteristics e More than one database management server is updated per unit of work e The application directs the distribution of work and initiates commit e There might be multiple requests per unit of work e There is one database management server per request e Commitment is coordinated across multiple database servers DRDA and data access Although DRDA defines database communication protocols it does not define the programming interfaces or APIs that should be used by application programmers In general DRDA can be used by an application program to pass any requ
140. e CD or DVD on Solaris 1 Insert the CD or DVD into the drive 2 If the Volume Manager vold is running on your system the disc is automatically mounted as cdrom cd_label if the CD or DVD has a label or cdrom unnamed_cdrom if it is unlabeled If the Volume Manager is not running on your system complete the following steps to mount the CD or DVD a Determine the name of the device by entering the following command ls al dev sr awk print 11 This command returns the name of the CD or DVD device In this example the command returns the string dev dsk cOt6d0s2 b Enter the following commands to mount the CD or DVD mkdir p cdrom unnamed_cdrom mount F hsfs o ro dev dsk cQt6d0s2 cdrom unnamed_cdrom where dev dsk c0t6d0s2 represents the name of the device that was returned in the preceding step and cdrom unnamed_cdrom represents the CD or DVD mount directory 3 Log out Chapter 3 Installing DB2 Connect server 47 Results Your CD or DVD file system is now mounted View the contents of the CD or DVD by placing the disk in the drive and enter the ed cdrom command where cdrom is the mount point directory Installing a DB2 Connect server product Windows To install a DB2 Connect server product such as DB2 Connect Enterprise Edition on Windows operating systems use the DB2 Setup wizard Alternatively you can install DB2 Connect server products using the response file method Before you begin Be
141. e Test and the RSU http www ibm com servers eserver zseries zos servicetst In general install the most recent Recommended Service Upgrade RSU to avoid encountering problems that are caused by software defects that IBM has corrected DB2 for i formerly known as DB2 Universal Database for i5 OS V5R4 1113348 Informational APAR PTFs MF53402 and MF53403 See website for System i Preventative Service Planning http www ibm com servers eserver zseries zos servicetst DB2 for i V6R1 DB2 for i V7R1 DB2 Server for VM and VSE Version 7 and later PTFs S130564 S130588 5130611 S130620 5130621 SI30622 5130825 S130827 SI30920 SI30921 S131019 SI31101 I31125 SI31238 and S131480 See website for System i Preventative Service Planning http www 912 ibm com s_dir sline003 NSF GroupPTFs OpenView amp view GroupPTFs PTFs 5143890 S143864 5143863 S143817 S143807 5143806 S143805 S143804 5143803 SI43802 S143801 S143768 5143757 S143721 S143658 5143651 S143577 S143550 5143544 S143539 S143532 S143476 5143466 5143446 S143386 5143373 S143111 S143017 5143016 S142986 142954 S142947 5142928 S 42927 S142906 S142872 S142783 S142775 5142769 S142768 142745 SI42716 5142700 S142504 and S142492 See website for System i Preventative Service Planning http www 912 ibm com s_dir sline003 NSF GroupPTFs OpenView amp view GroupPTFs Important Use DB2 Connect V9 7
142. e installing a DB2 database system update your Linux kernel parameters The default values for particular kernel parameters on Linux are not sufficient when running a DB2 database system Before you begin You must have root user authority to modify kernel parameters Procedure To update kernel parameters on Red Hat and SUSE Linux 1 Run the ipcs 1 command 2 Analyze the output to determine if there are any necessary changes required for your system Comments have been added following the to show what the parameter names are ipcs 1 Shared Memory Limits max number of segments 4096 SHMMNI max seg size kbytes 32768 SHMMAX max total shared memory kbytes 8388608 SHMALL min seg size bytes 1 Semaphore Limits max number of arrays 1024 SEMMNI max semaphores per array 250 SEMMSL max semaphores system wide 256000 SEMMNS max ops per semop call 32 SEMOPM semaphore max value 32767 Messages Limits max queues system wide 1024 MSGMNI max size of message bytes 65536 MSGMAX default max size of queue bytes 65536 MSGMNB e Beginning with the first section on Shared Memory Limits SHMMAX and SHMALL are the parameters that need to be looked at SHMMAxX is the maximum size of a shared memory segment on a Linux system whereas SHMALL is the maximum allocation of shared memory pages on a system 30 DB2 Connect User s Guide
143. e is also an SQLCA returned usually containing a zero or positive sqlcode such as 12 or 802 If you use a customized mapping file at a DB2 Connect server such positive sqlcodes will not be mapped if they are contained in the customized mapping file and have customized mappings for example they are mapped to a different sqlcode or have customized token mappings It is important to emphasize that 1 Positive sqlcodes represent warnings as opposed to negative sqlcodes which indicate error conditions All the negative sqlcodes will always be mapped in all circumstances regardless of which mapping file is being used All the positive sqlcodes contained in the customized mapping file and mapped to themselves with no change will always be mapped as well Also those positive sqlcodes that are not contained in the customized mapping file at the DB2 Connect server will also always be mapped Chapter 5 Administering 103 104 DB2 Connect User s Guide 2 If you use the default mapping file or you connect to the host database directly the sqlcode mapping will always be performed for all sqlcodes This is the second positional parameter If it is specified the application will disconnect from the IBM mainframe database server database when one of the following SOLCODES is returned SQL30000N SQL30040N SQL30050N SQL30051 SQL30053 SQL30060N SQL30070N SQL30071N SQL30072 SQL30073 SQL30074N SQL30090N When the disconnect
144. e of a query rowset size for a cursor e The use of the OPTIMIZE for N ROWS clause on the select statement associated with a cursor e The use of the FETCH FIRST N ROWS ONLY clause on the select statement associated with a cursor DB2 Connect can enable extra query block support using different SQL APIs Embedded SQL Chapter 9 Tuning 155 e The user can invoke extra query block support for a query by specifying either the OPTIMIZE for N ROWS clause or the FETCH FIRST N ROWS ONLY clause or both on the select statement itself e With the OPTIMIZE for N ROWS clause DB2 for z OS will attempt to block the desired number of rows to return to DB2 Connect subject to the EXTRA BLOCKS SRV DDF installation parameter setting The application can choose to fetch beyond N rows as DB2 for z OS does not limit the total number of rows that could ultimately be returned for the query result set to N e The FETCH FIRST N ROWS ONLY clause works similarly except that the query result set is limited to N rows by DB2 for z OS Fetching beyond N rows would result in SQL code 100 end of data CLI ODBC e The user can invoke extra query block support for a query through its SQL_MAX_ROWS statement attribute e The FETCH FIRST N ROWS ONLY clause is used instead for a DB2 for z OS 7 1 or later server For Version 7 the query result set is limited to N rows by DB2 for z OS Fetching beyond N rows would result in SQL_NO_DATA_FOUND
145. eads all of your feedback but cannot respond to you directly Provide specific examples wherever possible so that we can better understand your concerns If you are providing feedback on a specific topic or help file include the topic title and URL Do not use this email address to contact DB2 Customer Support If you have a DB2 technical issue that the documentation does not resolve contact your local IBM service center for assistance DB2 technical library in hardcopy or PDF format The following tables describe the DB2 library available from the IBM Publications Center at www ibm com e business linkweb publications servlet pbi wss English and translated DB2 Version 10 1 manuals in PDF format can be downloaded from www ibm com support docview wss rs 71 amp uid sw 2700947 Although the tables identify books available in print the books might not be available in your country or region Copyright IBM Corp 1993 2012 169 The form number increases each time a manual is updated Ensure that you are reading the most recent version of the manuals as listed below Note The DB2 Information Center is updated more frequently than either the PDF or the hard copy books Table 29 DB2 technical information Name Form Number Available in print Last updated Administrative API Reference SC27 3864 00 Yes April 2012 Administrative Routines and Views SC27 3865 00 No April 2012 Call Level Int
146. eck fix pack prerequisites Perform the necessary tasks before installing a fix pack Choose a fix pack installation method and install the fix pack Perform the necessary tasks after installing the fix pack af oN Apply the appropriate DB2 database product license If a previously licensed copy of a DB2 database server product does not already exist on the machine a single server fix pack image can be used to install any of the DB2 database server products In this case the DB2 database product installed is treated as a try and buy license and will stop working after a 90 day trial period unless you upgrade the try and buy license What to do next Check the log file for any post installation steps or error messages and recommended actions For non root installations on Linux or UNIX root based features such as High Availability and operating system based authentication can be enabled using the db2rfe command If root based features were enabled after installing your DB2 database product you must rerun the db2rfe command each time a fix pack is applied in order to re enable those features If you have multiple DB2 copies on the same system those copies can be at different version and fix pack levels If you want to apply a fix pack to one or more DB2 copies you must install the fix pack on those DB2 copies one by one Uninstalling Uninstalling DB2 Connect Windows This task provides steps for completely removing your
147. ed 88 DB2 Connect User s Guide Note Index definitions are not stored on export or used on import If you export or import mixed data columns containing both single byte and double byte data consider the following considerations TM e On systems that store data in EBCDIC MVS System z IBM Power Systems VM and VSE shift out and shift in characters mark the start and the end of double byte data When you define column lengths for your database tables be sure to allow enough room for these characters e Variable length character columns are recommended unless the column data has a consistent pattern Procedure e To move data from a workstation to host or System i server database 1 Export the data from a DB2 table to a PC IXF file 2 Using the INSERT option import the PC IXF file into a compatible table in the host server database e To move data from a host server database to a workstation 1 Export the data from the host server database table to a PC IXF file 2 Import the PC IXF file into a DB2 table Example The following example illustrates how to move data from a workstation to a host or System i server database Export the data into an external IXF format by issuing the following command db2 export to staff ixf of ixf select from userid staff Issue the following command to establish a DRDA connection to the target DB2 database db2 connect to cbc664 user admin using xxx If it doesn t already e
148. ed cn Chinese Traditional tw Croatian hr Czech cz Danish dk Dutch nl English en Finnish fi French fr German de Greek el Hungarian hu Italian it Japanese ip Korean kr Norwegian no Polish pl Portuguese pt Romanian ro Russian ru Slovak sk Slovenian sl Spanish es Swedish se Turkish tr Changing the DB2 Connect product interface language Windows The DB2 interface language is the language that appears in messages help and graphical tool interfaces When installing your DB2 database product you have the option of installing support for one or more languages After installation you can change the DB2 interface language to one of the other installed interface languages 22 DB2 Connect User s Guide About this task Do not confuse languages supported by a DB2 database product with languages supported by the DB2 interface Languages supported by a DB2 database product means the languages in which data can exist These languages are a superset of languages supported by the DB2 interface The DB2 interface language you want to use must be installed on your system The DB2 database product interface languages are selected and installed when you install a DB2 database product using the DB2 Setup wizard If you change the interface language of a DB2 database product to a supported interface language that has not been installed the DB2 database product interface language will default to the operating system language first an
149. educing the number of bridges that are crossed reduces the number of hops required for each request The physical distance between nodes should also be considered Even if a message is transferred by satellite the transfer time is limited by the speed of light 3 10 8 m s and the round trip distance between the sender and receiver e Network traffic If the bandwidth of the network has been fully utilized both the response time and the data transfer rate for a single application will decrease Congestion can occur in the network when data accumulates at a particular part of the network for example at an old NCP with a very small buffer size Chapter 9 Tuning 159 e Network reliability If the error rate of the network is high the throughput of the network will decrease and this will cause poor performance because of data retransmission CLI ODBC application performance tuning CLI ODBC is an SQL application programming interface that can be called by your database applications CLI functions invoke DB2 stored procedures which in turn access the system catalog tables If CLI ODBC applications are encountering performance problems consider tuning their behaviour with CLI ODBC keywords Some applications use ODBC APIs to gather metadata information that is used in further processing The ten metadata API calls that can be made are e SQLTables e SOLColumns e SQLSpecialcolumns e SOLStatistics e SQLPrimarykeys e SQLFo
150. enarios that might apply to your particular needs or environment DB2 Connect client access to host databases DB2 Connect s basic feature is providing a direct connection to a host database from desktop applications running on your workstations IBM Data Server Driver Package with DB2 Connect license is the simplest way to provide this solution Each workstation that has a client package and DB2 Connect license installed can establish a direct TCP IP connection to DB2 for z OS IBM DB2 for IBM i and DB2 Database for Linux UNIX and Windows servers In addition applications can connect to and update multiple DB2 family databases in the same transaction with the complete data integrity provided by the two phase commit protocol Figure 1 on page 7 shows a direct connection to an IBM mainframe database server from a workstation with DB2 Connect Personal Edition installed 6 DB2 Connect User s Guide DB2 for VSE for IBM i Power DB2 f fA for z OS E System z Systems Servers H F TCP IP ao l IBM Data server client package with DB2 Connect license ODBC ADO NET pB2cu josc sais Emgegded z 2 w 2 a 2 lt Application 2 Application 3 Application 4 Application n Figure 1 Direct Connection Between DB2 Connect and an IBM mainframe database server Note 1 All IBM data server drivers provide the ability to perform workload balancing and seamless automatic client reroute features with
151. entified by its LOCATION NAME or one of the alias LOCATION names defined on the z OS server The LOCATION NAME can be determined by logging in to TSO and issuing the following SQL query using one of the available query tools select current server from sysibm sysdummy1 multiple LOCATION NAMEs are also defined in the Boot Strap Data Set BSDS as well as the DSNLO04I message LOCATION location which is written when the Distributed Data Facility DDF is started The DISplay DDF command could also be used If accessing a z OS data sharing group the domain name should map to the DB2 group dynamic VIPA address This address routes to the least loaded DB2 member To access a specific member use the specific DB2 member dynamic VIPA address and turn off sysplex routing Each member DSNLO004I message displays the member specific domain name VSE or VM The database name DBNAME IBM Power Systems The relational database name RDBNAME Other For Windows Linux and UNIX operating systems the database alias found in the database directory Parameter string If you want to change the defaults specify any or all the following parameters in the following order map file The name of an SQLCODE mapping file that overrides the default SQLCODE mapping To turn off SQLCODE mapping specify NOMAP Note When processing a query request the DRDA server returns data in the form of a set of rows that represent the result set With each row ther
152. ents or server products and a IBM mainframe database server the remote server must provide one or more alternate addresses for itself In the case of DB2 for z OS multiple addresses are known if the database is a Sysplex data sharing environment Rerouting capability for Sysplex can be configured between DB2 Connect and the host database server if Sysplex support is enabled The rerouting capability for Sysplex is a DB2 Connect feature that allows DB2 Connect to try the connection against other members of the Sysplex group following the loss of communication with the original member The alternate server does not need to be cataloged in the database directory to enable the reroute capability for Sysplex on DB2 Connect By default the reroute capability for Sysplex is enabled if Sysplex support is enabled In order for an IBM Data Server Client to have the ability to recover from a loss of communications to a DB2 Connect server using automatic client reroute an alternate DB2 Connect server location must be specified before the loss of communication occurs The UPDATE ALTERNATE SERVER FOR DATABASE command is used to define the alternate DB2 Connect server location for a particular IBM mainframe database The alternate hostname and port number is given as part of the command The location is stored in the system database directory file at the DB2 Connect server In order to ensure the alternate DB2 Connect server location specified applies to that data
153. equests a connection the DB2 Connect uses one from the existing pool Using the already present connection reduces the overall connection time as well as the high CPU connect cost on the host DB2 Connect agents can be in one two states idle or active An agent is active when it is executing work for an application Once this work is completed the agent goes into an idle state awaiting further work from the same or a different application All idle agents are kept together in what is known as the idle agent Chapter 9 Tuning 143 pool You can configure the size of this pool using the num_poolagents configuration parameter This parameter equals the maximum number of idle agents you want the system to maintain Setting this parameter to zero is equivalent to turning off the connection pooling feature The default for this configuration parameter is set to AUTOMATIC with a value of 100 By being set to AUTOMATIC DB2 Connect automatically manages the number of idle agents in the idle agent pool DB2 Connect does not establish connections to the database before receiving its first client request Alternatively you can fill the pool of idle agents before any clients make a request The pool can be filled on startup using the num_initagents configuration parameter This parameter determines how many idle agents should be created at start up time These idle agents initially will not have connections to the host database server When a client req
154. equired to update your kernel configuration parameters The kernel configuration parameters are set in etc sysct conf Refer to your operating system manual for information about setting and activating these parameters using the sysctl command Software requirements e A browser is required to view online help e An X Window System software capable of rendering a graphical user interface is required if you want to use the DB2 Setup wizard to install DB2 Connect or if you want to use any DB2 graphical tools Communication requirements For TCP IP connectivity no additional software is required Mounting the CD or DVD for DB2 Connect Linux To mount a CD ROM on Linux operating systems issue the mount command Before you begin Depending on your system configuration you might need root user authority to mount discs Procedure To mount the CD or DVD on Linux operating systems 1 Insert the CD or DVD in the drive and enter the following command mount t iso9660 o ro dev cdrom cdrom where cdrom represents the mount point of the CD or DVD 2 Log out Results Your CD or DVD file system is now mounted View the contents of the CD or DVD by placing the disc in the drive and enter the cd cdrom command where cdrom is the mount point directory Installing DB2 Connect Personal Edition Solaris To define installation preferences and to install DB2 Connect Personal Edition use the DB2 Setup wizard Installing IBM Data Server
155. er a DB2 license key by entering the following command db2instance_path bin db2licm a filename where db2instance_path is where the DB2 instance was created and filename is the full path name and file name for the license file that corresponds to the product or feature you have purchased e On Linux or UNIX operating systems register a DB2 license key by entering the following command INSTHOME sqllib adm db2licm a filename where INSTHOME represents the home directory of the instance owner and filename is the full path name and file name for the license file that corresponds to the product or feature you have purchased The db21icm command can also be found in the path where the DB2 database product is installed For example opt IBM db2 V10 1 adm on AIX HP UX or Solaris operating systems or opt ibm db2 V10 1 adm on Linux operating systems if you use the default installation directory 84 DB2 Connect User s Guide Chapter 5 Administering Binding applications and utilities DB2 Connect server Application programs developed using embedded SQL must be bound to each database with which they will operate For information about the binding requirements for the IBM data server package see the topic about DB2 CLI bind files and package names Binding should be performed once per application for each database During the bind process database access plans are stored for each SQL statement that will be executed These access
156. er configuration file can be used to tune DB2 Connect RQRIOBLK The RQRIOBLK parameter sets the maximum size of network I O blocks A larger block size might improve the performance of large requests The block size does not usually affect the response time for small requests such as a request for a single row of data A larger block size usually requires more memory on the DB2 Connect server This increases the size of the working set and might cause large amounts of paging on small workstations Use the default DRDA block size 32767 if it does not cause too much paging on executing your application Otherwise reduce the I O block size until there is no paging Once paging begins a noticeable degradation of performance will occur Use performance monitoring tools such as the vmstat tool for Linux and UNIX operating systems to determine whether paging is occurring on your system 150 DB2 Connect User s Guide DIR_CACHE The DIR_CACHE parameter determines whether directory information is cached With caching DIR_CACHE YES directory files are read and cached in memory to minimize the processing usage of creating the internal directory structure and reading the directory files every time a connection is established Without caching DIR_CACHE NO whenever you connect to a database the appropriate directory is read from a disk and then the search is performed After the requested entries are found all memory related to directory se
157. erface e Subtract the amount from the savings account and determine the new balance e Read the fee schedule to determine the transaction fee for a savings account with the given balance Subtract the transaction fee from the savings account e Add the amount of the transfer to the checking account e Commit the transaction unit of work Database UL Database client d A Database server Update gt HH Savings account Update gt HH Checking account Read gt H H Transaction fee Figure 7 Using a Single Database in a Transaction To set up such an application you must 1 Create the tables for the savings account checking account and transaction fee schedule in the same database 2 If physically remote set up the database server to use the appropriate communications protocol 3 If physically remote catalog the node and the database to identify the database on the database server 4 Precompile your application program to specify a type 1 connection that is specify CONNECT 1 on the PREP command Distributed requests A distributed request is a distributed database function that allows applications and users to submit SQL statements that reference two or more DBMSs or databases in a single statement For example a join between tables in two different DB2 for z OS subsystems DB2 Connect provides support for distributed requests across datab
158. erface Guide and Reference Volume 1 SC27 3866 00 Yes April 2012 Call Level Interface Guide and Reference Volume 2 SC27 3867 00 Yes April 2012 Command Reference SC27 3868 00 Yes April 2012 Database Administration Concepts and Configuration Reference SC27 3871 00 Yes April 2012 Data Movement Utilities Guide and Reference SC27 3869 00 Yes April 2012 Database Monitoring Guide and Reference SC27 3887 00 Yes April 2012 Data Recovery and High Availability Guide and Reference Database Security Guide SC27 3870 00 SC27 3872 00 Yes Yes April 2012 April 2012 DB2 Workload Management Guide and Reference SC27 3891 00 Yes April 2012 Developing ADO NET and OLE DB Applications SC27 3873 00 Yes April 2012 Developing Embedded SQL Applications SC27 3874 00 Yes April 2012 Developing Java Applications SC27 3875 00 Yes April 2012 Developing Perl PHP Python and Ruby on Rails Applications SC27 3876 00 April 2012 Developing User defined Routines SQL and External SC27 3877 00 Yes April 2012 Getting Started with Database Application Development GI13 2046 00 Yes April 2012 170 DB2 Connect User s Guide Table 29 DB2 technical information continued Name Form Number Available in print Last updated
159. es to access a Version 8 z OS server IBM Power Systems Servers IBM DB2 for IBM i an integral part of the IBM i operating system Only one database can exist on an IBM Power Systems server unless the system is configured to use independent auxiliary storage pools DB2 Connect and SQL statements DB2 Connect forwards SQL statements submitted by application programs to IBM mainframe database servers 4 DB2 Connect User s Guide DB2 Connect can forward almost any valid SQL statement as well as the supported DB2 APIs application programming interfaces JDBC SQL ADO NET OLE DB ODBC Perl PHP pureQuery Python Ruby CLI Embedded SQL Embedded SQL support Two types of embedded SQL processing exist static SQL and dynamic SQL Static SQL minimizes the time required to execute an SQL statement by processing in advance Dynamic SQL is processed when the SQL statement is submitted to the IBM mainframe database server Dynamic SQL is more flexible but potentially slower The decision to use static or dynamic SQL is made by the application programmer Both types are supported by DB2 Connect Different IBM mainframe database servers implement SQL differently DB2 Connect fully supports the common IBM SQL as well as the DB2 for z OS DB2 Server for VM and VSE formerly SQL DS and IBM DB2 for IBM i implementations of SQL IBM SQL is strongly recommended for maintaining database independence DB2 Connect administration utilities
160. eshooting includes narrowing the scope of the problem and investigating the possible causes The proper starting point is to gather the relevant information and determine what you know what data has not been gathered and what paths you can eliminate At a minimum answer the following questions e Has the initial connection been successful e Is the hardware functioning properly e Are the communication paths operational e Have there been any communication network changes that would make previous directory entries invalid e Has the database been started e Is the communication breakdown between one or more clients and the DB2 Connect Server gateway between the DB2 Connect gateway and the IBM mainframe database server or between the DB2 Connect Personal Edition and the IBM mainframe database server e What can you determine by the content of the message and the tokens returned in the message e Will using diagnostic tools such as db2trc db2pd or db2support provide any assistance at this time e Are other machines performing similar tasks working correctly e If this is a remote task is it successful if performed locally Initial connection is not successful If you have configured a new connection in DB2 Connect and cannot connect successfully troubleshoot the problem by answering a set of questions that are structured into a checklist Review the following questions and ensure that the installation steps were followed
161. ess to DB2 documentation either on your local computer or on another computer on your network then you must install the DB2 Information Center The DB2 Information Center contains documentation for the DB2 database and DB2 related products See the Installing the DB2 Information Center using the DB2 Setup wizard UNIX topic in Installing DB2 Servers Installation requirements for DB2 Connect products Solaris Before you install DB2 Connect products on the Solaris Operating System ensure that the system you choose meets the necessary operating system hardware software and communications requirements To install a DB2 Connect product on Solaris the following requirements must be met Table 6 Solaris installation requirements Operating System Hardware Solaris 10 Update 9 Solaris x64 Intel 64 or AMD64 e 64 bit kernel Solaris 10 Update 9 UltraSPARC or SPARC64 processors e 64 bit kernel 1 Support is only for the DB2 product to be installed on local zones Installation on the global zone is not supported by the DB2 product at this time Operating system requirements Recommended amp Security Patches needs to be obtained The J2SE Solaris Operating System Patch Clusters are also required The Fujitsu PRIMEPOWER patches for the Solaris operating system can be downloaded from FTSI at http download ftsi fujitsu com For an additional list of issues that can affect DB2 database systems on Solaris
162. est that a target DRDA server can execute All of the DRDA servers available today can execute SQL requests forwarded by an application program through DB2 Connect IBM provides application programmers with tools to generate SQL requests for the Windows UNIX and Linux operating systems These tools are part of the DB2 client The DB2 database manager supports several programming interfaces ADO NET JDBC SQLJ PHP Perl DBI embedded SQL DB2 Call Level Interface DB2 Call Level Interface and OLE DB These APIs can be used by programmers to build applications in a variety of programming languages DB2 Connect and DRDA DB2 Connect implements the DRDA architecture to reduce the cost and complexity of accessing data stored in IBM DB2 for IBM i DB2 for IBM Power Systems DB2 for z OS DB2 Server for VM and VSE and other DRDA compliant database servers By fully exploiting the DRDA architecture DB2 Connect offers a well performing low cost solution with the system management characteristics that customers demand In DRDA terminology an application requester AR is the code that handles the application end of a distributed connection The AR is the application that is requesting data DB2 Connect acts as an application requester on behalf of application programs which can be local to the DB2 Connect workstation or on a separate client remote to DB2 Connect An application server AS is the code that handles the database end of the connecti
163. f security checking that will be done For TCP IP nodes Chapter 5 Administering 101 SECURITY SOCKS is an option which specifies that the node will be SOCKS enabled in which case the SOCKS_NS and SOCKS_SERVER environment variables are mandatory and must be set to enable SOCKS TCP IP remote hostname or IP address When defining a TCP IP node either the remote TCP IP hostname or the remote TCP IP address If a hostname is specified then it must be resolved at the DB2 Connect workstation either through Domain Name Server DNS lookup or by an entry in the local TCP IP hosts file For DB2 for z OS remote hosts the hostname appears in the DSNLO041 message DOMAIN hostname when the Distributed Data Facility DDF is started The DISplay DDF command could also be used If accessing a z OS data sharing group the domain name should map to the DB2 group dynamic VIPA address This address routes to the least loaded DB2 member To access a specific member use the specific DB2 member dynamic VIPA address and turn off sysplex routing Each member DSNLO004I message displays the member specific domain name TCP IP service name or port number When defining a TCP IP node either the remote TCP IP service name or port number This must be defined to TCP IP at the remote host Port number 446 has been registered as the default port number for DRDA For DB2 for z OS remote hosts the port number is defined in the Boot Strap Data Set BSDS as PO
164. f the client instance is determined by the operating system where you install DB2 Connect Refer to the Support changes for 32 bit and 64 bit DB2 servers topic in the Upgrading to DB2 Version 10 1 for details e Additional upgrade restrictions for DB2 servers also apply to DB2 Connect servers Refer to the Upgrade restrictions for DB2 servers topic in the Upgrading to DB2 Version 10 1 Procedure To upgrade your DB2 Connect server 1 Export your connectivity configuration information for your existing supported DB2 Connect server to an export profile Use the db2cfexp tool to create a configuration profile db2cfexp cfg_profile backup This profile contains all of the instance configuration information including the database manager configuration and registry profile because the option backup is specified You can use this profile to re create your connectivity configuration if necessary 2 Install DB2 Connect by running the DB2 Setup wizard and selecting the option Install New on the Install a Product panel Refer to DB2 Connect server products installation and configuration overview on page 32 3 Upgrade your DB2 Connect instances using the db2iupgrade command Refer to the Upgrading instances topic in the Upgrading to DB2 Version 10 1 4 Upgrade any existing transaction manager and DB2 Connect federated databases You can also upgrade your databases by restoring a DB2 Connect backup from one of the two
165. f the resources used by the connection might not be released Results Note 1 Explicit trusted connections should not use CLIENT authentication This does not apply to implicit trusted connections 2 Applications using explicit trusted connections should only be run on secure computers which are password protected and accessible only to authorized personnel This does not apply to implicit trusted connections Switching users on a trusted connection through CLI You can switch users on an explicit trusted connection through the command line interface CLI For a description of what it means to switch users using a trusted connection see the topic in the related links Before you begin e The connection must have been successfully created as an explicit trusted connection e The explicit trusted connection must not be in a transaction e The trusted context that allowed the explicit trusted connection to be created must be configured to allow switching to the authorization ID you are switching to Chapter 8 Security 131 About this task The examples in these instructions use the C language and assume that conn is a pointer to a connected explicit trusted connection The variable rc is assumed to have a data type of SQLRETURN The variable newuser is assumed to be a pointer to a character string holding the authorization ID of the user you want to switch to The variable passwd is assumed to be a pointer to a character str
166. figuration is required to support various functions in DB2 which monitor or depend on knowledge of swap paging space utilization The actual amount of swap paging space required varies across systems and is not solely based on memory utilization by application software It is only strictly required by DB2 on the Solaris and HP platforms due to their use of early paging space allocation A reasonable minimum swap paging space configuration for most systems is 25 50 of RAM Solaris and HP systems with many small databases or multiple databases tuned by STMM might require a paging space configuration of 1 x RAM or higher These higher requirements are due to virtual memory pre allocated per database instance and retained virtual memory in the case of STMM tuning multiple databases Additional swap paging space might be wanted to provision for unanticipated memory overcommitment on a system Java software support for DB2 Connect You require the appropriate level of IBM Software Development Kit SDK for Java to use Java based tools and to create and run Java applications including stored procedures and user defined functions If the IBM SDK for Java is required by a component being installed and the SDK for Java is not already installed in that path the SDK for Java will be installed if you use either the DB2 Setup wizard or a response file to install the product The SDK for Java is not installed with IBM Data Server Runtime Client or IBM Data
167. followed by the product identifier followed by the original token list The character s must be lowercase Chapter 5 Administering 115 token list A list of tokens separated by commas Specify only a comma to skip a particular token For example the form t2 t4 means that the first and third output tokens are null Each token has the form of a number n optionally preceded by c optionally followed by c or i It is interpreted as follows c The data type of the token in this position is CHAR the default If c comes before n it refers to the input token if it comes after n it refers to the output token The character c must be lowercase i The data type of the token in this position is INTEGER If i comes after n it refers to the output token i should not come before n because IBM mainframe database server products support only CHAR tokens The character i must be lowercase n A number or numbers indicating which IBM mainframe database server tokens are used They are arranged in the order required for placement in the output SQLCA The number indicates the IBM mainframe database server token the arrangement indicates the order in which the tokens will be placed in the SQLCA For example the IBM mainframe database server might return two tokens 1 and 2 If you want token 2 to appear before token 1 in the output SQLCA specify 2 1 Multiple token numbers can be combined to form one CHAR output token by connecti
168. for the IBM mainframe database server This name will be used by any application program that accesses the database By default the value that you specify for Database name is used Format 1 8 single byte alphanumeric characters including the number sign at sign dollar sign and underscore _ It cannot begin with an underscore or a number Node name The same value that you wrote in the Node Directory Parameters table Authentication Specifies where the validation of the user s name and password will be made for connections originating from the DB2 Connect server The valid options are SERVER SERVER_ENCRYPT CLIENT KERBEROS SERVER_ENCRYPT_AES and DATA_ENCRYPT There is no support for the GSSPLUGIN authentication type in the system database directory Node directory values You can specify the following information in the node directory node name protocol security type TCP IP host name or IP address TCP IP service name or port number Node name A nickname for the IBM mainframe database server system on which the remote database resides This name is user defined Write the same node name in both the Node Directory Parameters table and the System Database Directory Parameters table Format 1 8 single byte alphanumeric characters including the number sign at sign dollar sign and underscore _ It cannot begin with an underscore or a number Protocol Must be TCP IP Security type The type o
169. for the connection TCP 10 or TCP 20 or TCP 11 or TCP 21 d Determine the LOCATION NAME of the DB2 for z OS database server to which you want to connect e Determine the values to be used for user ID and PASSWORD when connecting to the host database Chapter 4 Configuring 4 At your System z server a Verify the host address or the host name b Verify the port number or the service name c Update the services file with the correct port number and service name if necessary d Update the hosts file or the Domain Name Server used by the DB2 for z OS system with the host name and IP address of the DB2 Connect workstation if necessary e Ensure the new definitions are active before attempting to test the connection Refer to your host network administrator or change control staff if necessary f Check with the DB2 for z OS administrator that you have a valid user ID password and database LOCATION NAME g PING the DB2 Connect server using the correct port number if that option is supported by TCP IP on the host system For example ping remote_host_name p port_number Support for your System z server is available at http www ibm com servers eserver support zseries Configuring DB2 for z OS Before you can use DB2 Connect your DB2 for z OS Administrator must configure DB2 for z OS to permit connections from DB2 Connect workstations About this task This section indicates the minimum update
170. fore you launch the DB2 Setup wizard e Ensure that your system meets Disk and memory requirements Hardware distribution and software requirements Refer to Installation requirements for DB2 Connect server products Windows on page 49 e If you are planning to use LDAP you must extend the directory schema Refer to the Extending the Active Directory Schema for LDAP directory services Windows topic in Installing DB2 Servers e It is recommended that you use an Administrator account to perform the installation The Administrator account must belong to the local administrator s group on the Windows computer where you are installing your DB2 database product and should have the following advanced user rights Actas part of the operating system Create token object Increase quotas Replace a process level token You can perform the installation without advanced user rights but the setup program might be unable to validate accounts e If you want to install DB2 Connect with a non Administrator account refer to the topic Non Administrator installation of DB2 Connect Windows Procedure e To install a DB2 Connect server product such as DB2 Connect Enterprise Edition on Windows using the DB2 Setup wizard 1 Log on to the system as a user with administrator authority 2 Close all programs so the installation program can update files as required 3 Insert the DVD into the drive The auto run fea
171. g information can be found in the Troubleshooting and Tuning Database Performance or the Database fundamentals section of the DB2 Information Center which contains e Information about how to isolate and identify problems with DB2 diagnostic tools and utilities e Solutions to some of the most common problem e Advice to help solve other problems you might encounter with your DB2 database products IBM Support Portal See the IBM Support Portal if you are experiencing problems and want help finding possible causes and solutions The Technical Support site has links to the latest DB2 publications TechNotes Authorized Program Analysis Reports APARs or bug fixes fix packs and other resources You can search through this knowledge base to find possible solutions to your problems Access the IBM Support Portal at http www ibm com support entry portal Overview Software Information_Management DB2_for_Linux _ UNIX_and_Windows Terms and conditions Permissions for the use of these publications are granted subject to the following terms and conditions Applicability These terms and conditions are in addition to any terms of use for the IBM website Personal use You may reproduce these publications for your personal noncommercial use provided that all proprietary notices are preserved You may not distribute display or make derivative work of these publications or any portion thereof without the express consent of IBM
172. have more than one DB2 database product which includes at least one product that is not a Data Server Client or a DB2 database server installed in a single DB2 copy you must download and uncompress all of the corresponding product specific fix packs before starting the fix pack installation process e A universal fix pack The universal fix pack services installations where more than one DB2 database product has been installed The universal fix pack is not needed if the installed DB2 database products are only DB2 database server products or a Data Server Client In this case the single server image fix pack should be used On Linux or UNIX operating systems if national languages have been installed you also require a separate national language fix pack The national language fix pack can not be installed alone A universal or product specific fix pack must be applied at the same time and they must both be at the same fix pack level For example if you are applying a universal fix pack to non English DB2 database products on Linux or UNIX you must apply both the universal fix pack and the national language fix pack to update the DB2 database products Restrictions e A DB2 Version 10 1 fix pack can only be applied to DB2 Version 10 1 general availability GA or DB2 Version 10 1 fix pack copies e All DB2 instances DAS and applications related to the DB2 copy being updated must be stopped before installing a fix pack e Ina par
173. he Add Remove Programs window to remove your DB2 database product Refer to your operating system s help for more information about removing software products from your Windows operating system e db2unins command You can run the db2unins command from the DB2DIR bin directory to remove your DB2 database products features or languages Using this command you can uninstall multiple DB2 database products at the same time using the p parameter You can use a response file to uninstall DB2 database products features or languages using u parameter What to do next Unfortunately your DB2 database product cannot always be removed by using the Control Panel gt Add Remove Programs facility or using the db2unins p command or the db2unins u command The following uninstallation option must ONLY be attempted if the previous method fails To forcefully remove all DB2 copies from your Windows system run the db2unins f command This command will perform a brute force uninstallation of ALL DB2 copies on the system Everything except user data such as DB2 databases will be forcefully deleted Before running this command with the f parameter see the db2unins command for details Uninstalling DB2 Connect Linux and UNIX This task provides steps for removing a DB2 database product from your Linux or UNIX operating system About this task This task is not required to install a new version of a DB2 database product Each version of a DB2 da
174. hen you use 256 bit AES encryption with the SDK for Java from Oracle you might need to install the JCE Unlimited Strength Jurisdiction Policy File which is available from Oracle A minimum level of SDK for Java 1 4 2 SR6 is required for SUSE Linux Enterprise Server SLES 10 A minimum level of SDK for Java 1 4 2 SR7 is required for Red Hat Enterprise Linux RHEL 5 SDK for Java 6 support on Linux requires SDK for Java 6 SR3 or later If SDK for Java 6 SR2 or later is used set DB2LIBPATH java_home jre 1ib ppc64 Support for Java stored procedures and user defined functions built by IBM SDK for Java 1 4 2 was deprecated in Version 9 7 and might be removed in a future release IBM SDK for Java 1 4 2 has an End of Service date of September 2011 It is recommended to remove SDK for Java 1 4 2 dependency well before this date Removing this dependency can be done by rebuilding Java stored procedures and user defined functions with the SDK for Java included in DB2 Version 9 1 DB2 Version 9 5 DB2 Version 9 7 or DB2 Version 10 1 Preparing to install DB2 Connect for Linux on zSeries To install a DB2 database product on an IBM zSeries that is running Linux you must make the installation image accessible to the Linux operating system You can use FTP to send the installation image to the operating system or use an NFS mount to make the product DVD available to the operating system Before you begin You have already obtained your DB2 database
175. here to send each transaction 94 DB2 Connect User s Guide Note System z Distributed Data Facility DDF configuration does not need to be changed to take advantage of the DB2 Connect Sysplex exploitation Refer to DB2 for z OS Data Sharing Planning and Administration guide DB2 Connect also provides fault tolerance by attempting to connect to an alternate sysplex machine in the event of a connection failure An error will only be returned to the application if all known connections have failed DB2 Connect is designed with a transport tool With Sysplex enabled DB2 Connect routes connections using a transport member and associates it with a logical connection Conversion of character data When character data is transferred between machines it must be converted to a form that the receiving machine can use For example when data is transferred between a DB2 Connect server and a host or System i database server it is usually converted from a server code page to a host CCSID and vice versa If the two machines use different code pages or CCSIDs code points are mapped from one code page or CCSID to the other This conversion is always performed at the receiver Character data sent to a database consists of SQL statements and input data Character data sent from a database consists of output data Output data that is interpreted as bit data is not converted For example data from a column declared with the FOR BIT DATA clause Otherwise
176. hich the sample programs are written These examples have not been thoroughly tested under all conditions IBM therefore cannot guarantee or imply reliability serviceability or function of these programs The sample programs are provided AS IS without warranty of any kind IBM shall not be liable for any damages arising out of your use of the sample programs Each copy or any portion of these sample programs or any derivative work must include a copyright notice as follows your company name year Portions of this code are derived from IBM Corp Sample Programs Copyright IBM Corp _enter the year or years_ All rights reserved Trademarks IBM the IBM logo and ibm com are trademarks or registered trademarks of International Business Machines Corp registered in many jurisdictions worldwide Other product and service names might be trademarks of IBM or other companies A current list of IBM trademarks is available on the web at Copyright and trademark information at www ibm com legal copytrade shtml The following terms are trademarks or registered trademarks of other companies e Linux is a registered trademark of Linus Torvalds in the United States other countries or both e Java and all Java based trademarks and logos are trademarks or registered trademarks of Oracle its affiliates or both e UNIX is a registered trademark of The Open Group in the United States and other countries Intel Intel logo Inte
177. ich supports the SDLC or X 25 protocols e DB2 Connect and the communication subsystem A are usually located on the same system For the scope of this discussion it is assumed that the application is also on the same system Bottlenecks Transaction throughput is dependent on the slowest component in the system If you identify a performance bottleneck you can often alleviate the problem by changing configuration parameters allocating more resources to the problem component upgrading the component or adding a new component to offload some of the work You can use various tools to determine how much time a query spends in each component This will give you an idea of which components should be tuned or upgraded to improve performance For example if you determine that a query spends 60 of its time in the DB2 Connect machine you might want to tune DB2 Connect or if you have remote clients add another DB2 Connect machine to the network 138 DB2 Connect User s Guide Benchmarking Benchmarking compares performance in one environment with performance in another Benchmarking can begin by running the test application in a normal environment As a performance problem is narrowed down specialized test cases can be developed to limit the scope of the function that is tested and observed Benchmarking does not need to be complex Specialized test cases need not emulate an entire application in order to obtain valuable information Start w
178. ies at the client node and the database manager server node Client NNAME Node Identifies the node where the client application is executing The information varies according to the client protocol in use For a client connected via TCP IP this is the host name Client Product ID Client The product and version that is running on the client The client product IDs will be e SQL07010 for Version 7 1 of DB2 Universal Database and DB2 Connect products and their clients e SQL08010 for Version 8 1 of DB2 Universal Database and DB2 Connect products and their clients e SQL08020 for Version 8 2 of DB2 Universal Database and DB2 Connect products and their clients e SQL09120 for Version 9 1 of DB2 products DB2 Connect products and their clients Code Page ID The code page identifier at the node where the monitored application started You can use this information to ensure that data conversion is supported between the application code page and the database code page or for IBM mainframe database server databases the IBM mainframe database server CCSID If the application code page is different from that under which the database system monitor is running this code page element can help you to manually convert data that was passed from the application and displayed by the database system monitor For example you can use it to help translate the Application Name Outbound Sequence No This represents the outbound sequence nu
179. im No Yes pureQuery Runtime Label based access control No No LBAC Materialized query tables No Yes MQT Multidimensional clustering No Yes MDC tables Multi Temperature Storage No No Online reorganization No No DB2 pureScale No No pureXML storage No No Query parallelism No Yes Replication tools No Yes Scan Sharing No No Spatial Extender No Yes Time Travel Query Yes Yes Table partitioning No No Tivoli System Automation No Yes Workload management No Yes Chapter 1 DB2 Connect overview 3 Table 1 Functionality in DB2 Connect product editions continued DB2 Connect Personal Functionality Edition DB2 Connect server editions Note 1 IBM InfoSphere Optim Performance Manager Extended Edition is a follow on to Performance Expert IBM InfoSphere Optim Performance Manager Extended Edition helps optimize the performance and availability of mission critical databases and applications 2 Only DB2 Connect Unlimited Edition for System z and DB2 Connect Application Server Advanced Edition include IBM InfoSphere Optim pureQuery Runtime 3 Replication tools except the Replication Center are available on all supported operating systems The Replication Center is available only on Linux and Windows operating systems Host databases The term database is used throughout this document to describe a relational database management system RDBMS Other systems with which DB2 Connect communicates migh
180. inding 85 automatic client reroute details 90 setup 90 benchmarking performance 137 Copyright IBM Corp 1993 2012 bidirectional CCSID support BIDI parameter 102 language support 24 95 bind list DB2 Connect 85 BINDADD authority DB2 Connect 85 binding applications 85 authority 85 packages DB2 Connect 85 utilities DB2 Connect 85 93 block size DB2 Connect 150 blocking data 140 bootstrap data set BSDS parameters z OS 101 bottlenecks performance 137 transactions 137 C cached address list 82 call level interface CLI overview 160 trusted connections 129 CDs mounting AIX 37 HP UX 41 Linux 44 58 Solaris 47 61 CHAR data type details 158 character data representation architecture CDRA 97 character data types 158 client and server connections overview 1 client applications communication recovery 90 CLIENT authentication type DB2 Connect 134 client DB alias 122 clients overview 91 remote 91 code pages conversion exceptions 24 95 supported 21 coded character set identifier CCSID bidirectional languages 24 95 bidirectional support details 102 command line processor CLP performance 140 SQL statements 5 183 commands db2licm setting license policy 68 db2osconf determining kernel configuration parameter values 30 db2setup displaying DB2 Setup wizard in your national language 21 GET SNAPSHOT overview 120 COMMIT statement statically bound 140 communication protocols
181. ines have different code pages or CCSIDs 24 DB2 Connect User s Guide For example if DB2 Connect is used to access data the following happens 1 DB2 Connect sends an SQL statement and input data to System z 2 DB2 for z OS converts the SQL statement and data to the host server s code page and then processes the data 3 DB2 for z OS sends the result back to the DB2 Connect server 4 DB2 Connect converts the result to the code page of the user s environment For bidirectional languages a number of special BiDi CCSIDS have been defined by IBM and are supported by DB2 Connect If the bidirectional attributes of the database server are different from those of the client you can use these special CCSIDS to manage the difference Refer to the supported territory codes and code pages topic for the supported conversions between code pages on the DB2 Connect and CCSIDs on the host or System i server Prerequisites DB2 Connect disk and memory requirements Ensure that an appropriate amount of disk space is available for your DB2 Connect environment and allocate memory accordingly Disk requirements The disk space required for your product depends on the type of installation you choose and the type of file system you have The DB2 Setup wizard provides dynamic size estimates based on the components selected during a typical compact or custom installation Remember to include disk space for required databases software and
182. information about changing the default system language Changing the DB2 Connect interface language Linux and UNIX The interface language of the DB2 database product is the language that appears in messages help and graphical tool interfaces When installing your DB2 database product you have the option of installing support for one or more languages If at some time after installation you want to change the interface language to one of the other installed interface languages use the steps outlined in this task Chapter 3 Installing DB2 Connect server 23 Before you begin Do not confuse languages supported by the DB2 database product with languages supported by the DB2 interface Languages supported by the DB2 database product that is languages that data can exist in are a superset of languages supported by the DB2 interface Support for the DB2 interface language you want to use must be installed on your system DB2 interface language support is selected and installed when you install a DB2 database product using the DB2 Setup wizard If you change the interface language of the DB2 database product to a supported interface language that has not been installed the DB2 interface language will default to the operating system language If the operating system language is not supported English is used as the DB2 interface language DB2 interface language support is selected and installed when you install your DB2 database produc
183. ing instances any existing DB2 Connect federated databases and any existing transaction manager databases so that they can run in the latest version or release of DB2 Connect e Upgrading IBM Data Server client packages involves upgrading your client instances to keep the configuration of your existing IBM Data Server client packages Refer to the Clients upgrade topic in the Upgrading to DB2 Version 10 1 e Upgrading database applications involves testing them in the latest version or release of DB2 Connect and modifying them only when you need to support changes available in the latest version or release of DB2 Connect Review changes in existing functionality and discontinued and deprecated functionality for DB2 Connect in the What s New for DB2 Version 10 1 to determine the changes that could impact your database applications If your database applications connect to DB2 servers you might need to upgrade your database applications Refer to the Database applications and routines upgrade topic in the Upgrading to DB2 Version 10 1 e Consideration toward DB2 Connect client instead of DB2 Connect server to receive equivalent or superior function You can reduce complexity improve performance and deploy application solutions with smaller footprints For details see the topic about client server connection options The best approach to upgrading is to write an upgrade plan A strategy defines how to approach the upgrading of y
184. ing DB2 Connect servers The latest version or release of DB2 Connect servers supports the upgrade of DB2 Connect instances and any existing transaction manager and DB2 Connect federated databases created on the two previously supported versions of DB2 Connect servers Before you begin Before upgrading to the latest the version of DB2 Connect e Ensure that you have the proper operating system access Root user authority on UNIX Local Administrator on Windows e Ensure that you have SYSADM authority e Ensure that you meet the installation requirements for DB2 database products Refer to the Installation requirements for DB2 database products topic in the Installing DB2 Servers The requirements for Linux and UNIX operating systems have changed e Review the upgrade recommendations Refer to the Best practices for upgrading DB2 Servers topic in the Upgrading to DB2 Version 10 1 e Review the disk space requirements Refer to the Disk space requirements for DB2 Server upgrades topic in the Upgrading to DB2 Version 10 1 e Perform the pre upgrade tasks especially backing up your databases If you have DB2 Connect Version 7 you must upgrade to DB2 Connect Version 8 first Refer to the following topic for details http publib boulder ibm com infocenter db2luw v8 topic com ibm db2 udb doc conn t0008566 htm About this task Since DB2 Connect server products are host database connectivity servers the on
185. ing containing the password for that authorization ID Procedure 1 Call the SQLSetConnectAttr function to set the SQL_ATTR_TRUSTED_CONTEXT_USERID attribute Set it to the authorization ID you want to switch to rc SQLSetConnectAttr conn SQL_ATTR_TRUSTED_CONTEXT_USERID newuser SQL_NTS s Check for errors Be sure to check for errors and warnings especially those listed in table Table 21 Table 21 Errors indicating failure to set a new authorization ID when switching users SQLCODE Meaning CLIO106E The connection is not connected CLIO197E The connection is not a trusted connection CLI0124E There is a problem with the value provided Check that it is not null or not too long for example CLIO196E The connection is involved in a unit of work that prevents it from switching users To be able to switch users the connection must not be in a transaction Optional This step is optional unless the trusted context that allowed this trusted connection requires a password for the authorization ID you are switching to Call the SQLSetConnectAttr function to set the SQL_ATTR_TRUSTED_CONTEXT_PASSWORD attribute Set it to the password for the new authorization ID rc SQLSetConnectAttr conn SQL_ATTR_TRUSTED_ CONTEXT PASSWORD passwd SQL_NTS Ji Check for errors Be sure to check for errors and warnings both those listed in table Table 21 and those listed in table Table 22
186. ing systems You can connect to DB2 databases on the z OS IBM i VSE and VM operating systems and on IBM Power Systems hardware You can also connect to databases that you did not create by using IBM products if they are Distributed Relational Database Architecture DRDA compliant DB2 Connect is the industry leading solution integrating System z System i and other enterprise data with client server web mobile and service oriented architecture applications DB2 Connect delivers significant feature enhancements to improve programmer productivity provide a more robust infrastructure and enable the deployment of DB2 technology DB2 Connect has several product offerings e DB2 Connect Personal Edition e DB2 Connect Enterprise Edition e DB2 Connect Application Server Edition e DB2 Connect Unlimited Edition for System z e DB2 Connect Unlimited Edition for System i e IBM DB2 Connect Application Server Advanced Edition e IBM DB2 Connect Unlimited Advanced Edition for System z For detailed information about DB2 Connect product offerings see http www ibm com software data db2 db2connect It is strongly recommended that you use DB2 Connect client notably the IBM data server drivers and clients instead of the DB2 Connect server IBM data server drivers and clients provide the same connection and application development functionality as the DB2 Connect server However you can reduce complexity improve performance
187. ining errors can still result in the creation of a package In turn this allows one bind file to be used against multiple servers even when a particular server implementation might flag the SQL syntax of another to be invalid For this reason binding any of the list files ddcsxxx 1st against any particular IBM mainframe database server should be expected to produce some warnings b If you are connecting to a DB2 database through DB2 Connect use the bind list db2ubind 1st and do not specify sqlerror continue which is only valid when connecting to a IBM mainframe database server Also to connect to a DB2 database it is recommended that you use the DB2 clients provided with DB2 and not DB2 Connect Use similar statements to bind each application or list of applications If you have remote clients from a previous release of DB2 you might need to bind the utilities on these clients to DB2 Connect Chapter 5 Administering 87 Moving data with DB2 Connect If you are working in a complex environment in which you need to move data between a host database system and a workstation you can use DB2 Connect the gateway for data transfer between the host and the workstation About this task DB2 for z OS Database Server DBMS i Database table i DB2 Connect x DB2 client executing Import Export Figure 4 Import Export through DB2 Connect The D
188. int manager SPM configuration parameters default 112 scenarios 111 Sysplex configuration requirements 83 DB2 Connect support 81 fault tolerance 82 load balancing 82 parameter 102 priority information 82 support 80 System z 81 94 using 82 system database directory updating 100 values 101 System i database servers configuring TCP IP 83 DB2 Connect support 95 system resources contention 154 system status GET SNAPSHOT command 120 System z DB2 Connect support overview 95 T target databases names 102 106 TCP IP authentication scenarios 136 configuring host connections 78 host database servers 83 System i database servers 83 DB2 for z OS configuration 76 DOMAIN 101 host names 106 port numbers 106 remote host names 101 106 RESPORT 101 resynch port 101 RFC 1323 extensions 156 service names 101 TCPPORT 101 terms and conditions publications 176 territory codes page support 24 95 throughput transactions 137 time zones overview 102 tokens SQLCODEs 113 tools CPU usage 137 memory usage 137 performance 137 transaction processing monitors DB2 Connect 8 examples 8 multisite updates 110 OLTP 8 Tuxedo 8 transactions DB2 Connect Enterprise Edition 8 distributed 110 loosely coupled DB2 Connect 113 multisite updates 97 110 throughput DB2 Connect 137 transaction processing monitors 8 two phase commit 97 unit of work UOW 97 XA distributed applications 113 troubleshooting connections 161 162 DB2 Connect 16
189. is document should verify the applicable data for their specific environment Information concerning non IBM products was obtained from the suppliers of those products their published announcements or other publicly available sources IBM has not tested those products and cannot confirm the accuracy of performance compatibility or any other claims related to non IBM products Questions on the capabilities of non IBM products should be addressed to the suppliers of those products All statements regarding IBM s future direction or intent are subject to change or withdrawal without notice and represent goals and objectives only This information may contain examples of data and reports used in daily business operations To illustrate them as completely as possible the examples include the names of individuals companies brands and products All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental COPYRIGHT LICENSE This information contains sample application programs in source language which illustrate programming techniques on various operating platforms You may copy modify and distribute these sample programs in any form without payment to IBM for the purposes of developing using marketing or distributing application programs conforming to the application programming interface for the operating 180 DB2 Connect User s Guide platform for w
190. is performance enhancement by requesting extra query blocks from a DB2 for z OS server by default To fully take advantage of the return of extra query blocks each of which can be up to 32K bytes long for the preferred network protocol of TCP IP window scaling extensions have been enabled as architected under RFC 1323 in DB2 Connect This feature that allows TCP IP to dynamically adjust the send and receive window sizes to accommodate the potentially large amounts of data returned by way of the extra query blocks efficiently Extra query block Extra query block support on servers with DB2 for z OS Version 7 or later is configured via the EXTRA BLOCKS SRV parameter on the DB2 DDF installation panel This support is configured by way of controlling the maximum number of extra query blocks that DB2 can send back to a client for a request You can set this parameter to a value between 0 and 100 Setting the parameter value to 0 disables the return of extra query blocks The default value of 100 should always be used to get the most benefit out of this feature barring any idiosyncrasies in the network that would render this setting less than ideal On the client side where the application accesses DB2 for z OS either directly through a co located DB2 Connect installation or through a separate DB2 Connect server installation there are various means for activating the corresponding DB2 Connect support on a per cursor or statement basis e The us
191. ith simple measurements and increase the complexity only when warranted Characteristics of good benchmarks e Each test is repeatable e Each iteration of a test is started in the same system state e The hardware and software used for benchmarking matches your production environment e There are no functions or applications active in the system other than those being measured unless the scenario includes some other activity going on in the system Note Applications that are started use memory even when they are minimized or idle This could cause paging and skew the results of the benchmark Performance Tools The following tables list some of the tools that can help you measure system performance Because these tools themselves use system resources you might not want to have them active all the time Table 25 Performance tools for CPU and memory usage System Tool Description AIX vmstat time ps tprof Provide information about CPU or memory contention problems on the DB2 Connect workstation and remote clients HP UX vmstat time ps monitor and glance if available Windows Microsoft Performance Monitor Table 26 Performance tools for database activity System Tool Description All Database monitor Determines if the problem originates from the database System z IBM Tivoli OMEGAMON XE for DB2 Performance Monitor on z OS ASG TMON for DB2 ASG and CA Insight Performance Moni
192. ither root or non root authority For more information about the non root installation see Non root installation overview Linux and UNIX e Ensure that your system meets Disk and memory requirements Installation requirements for DB2 Connect Personal Edition Linux on page 57 e The DB2 database product DVD must be mounted on your system e The DB2 Connect product image must be available If you are installing a non English version of a DB2 Connect product you must also have the appropriate National Language Packages e To locate DB2 database products already installed on your system issue the db21s command e The DB2 Setup wizard is a graphical installer You must have X windows software capable of rendering a graphical user interface for the DB2 Setup wizard to run on your machine Ensure that the X windows server is running Ensure that you have properly exported your display For example export DISPLAY 9 26 163 144 0 e If security software such as Lightweight Directory Access Protocol LDAP is used in your environment you must manually create required DB2 users before you start the DB2 Setup wizard Refer to the Centralized user management considerations topic in Installing DB2 Servers before you begin Note Network Information Services NIS and Network Information Services Plus NIS features are deprecated starting with DB2 Version 9 1 Fix Pack 2 Support for these features might be removed in a futu
193. k adapters are examples of hardware resources Communication subsystems paging subsystems mbuf for AIX is an example of a software resource Data Flows Figure 10 on page 138 shows the path of data flowing between the IBM mainframe database server and the workstation through DB2 Connect Copyright IBM Corp 1993 2012 137 Application DB2 Connect DRDA Application Requester Communication Subsystem A Database Management System DRDA Application Server Communication Subsystem B Network Hardware Interface B Network Hardware Interface A Network Figure 10 Data Flows in DB2 Connect e The IBM mainframe database and part of communication subsystem B are usually running on the same system This system is made up of one or more CPUs main storage an I O subsystem DASD and an operating system Because other programs might share these components resource contention could cause performance problems e The network is composed of a combination of cables hubs communication lines switches and other communication controllers For example the network hardware interface B could be communication controllers such as 3745 or 3172 or a token ring adapter for an IBM Power Systems server There could be more than one transmission medium involved between network hardware interfaces A and B e Network hardware interface A could be token ring Ethernet other LAN adapter or an adapter wh
194. ket but depending on the server implementation there might or might not be an outstanding receive DB2 for z OS uses asynchronous socket calls and therefore is able to detect the loss of the connection and roll back any long running SQL statements that are in progress wnSXSPLEX This parameter the 6th positional parameter can be used to explicitly enable DB2 Connect SYSPLEX support for a particular database sm LOCALDATE value This parameter the seventh positional parameter is used to enable DB2 Connect date formatting support This is implemented using a date mask for the value as follows Suppose you issue the following CLP command line processor statements catalog TCPIP node nynode remote myhost server myport catalog dcs database nydbl as new_york catalog database nydb1l as newyork1 at node nynode authentication server The database alias newyork1 is to be used for accessing a host database without date transformation because no date mask has been specified However with the new date formatting support you can now use the following CLP commands In this case because the CLP is being used and the parameter string is itself being specified using double quotation marks the LOCALDATE value has to be specified inside two pairs of double quotation marks Note the use of the operating system escape character backslash to ensure that the double quotation marks are not stripped from the LOCALDATE specification catal
195. l Inside Intel Inside logo Celeron Intel SpeedStep Itanium and Pentium are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries Microsoft Windows Windows NT and the Windows logo are trademarks of Microsoft Corporation in the United States other countries or both Other company product or service names may be trademarks or service marks of others Appendix B Notices 181 182 DB2 Connect User s Guide Index Special characters amp amp SQLCODE mapping file 114 A about this book v access DB2 databases applicationsWindows operating systems 127 agentpri database manager configuration parameter 150 AIX CD mounting 37 DVD mounting 37 installing DB2 Connect server products 33 35 application development application design 140 IBM Data Server Driver Package 6 ODBC 6 application name monitor element 122 application requesters DRDA definition 97 parameters 106 application servers DRDA definition 97 applications binding 85 compound SQL 140 designing 140 ODBC 93 performance application design 140 stored procedures 140 AS target database name 102 ATOMIC compound SQL not supported in DB2 Connect 140 authentication 106 DB2 Connect 136 overview 134 REVOKE statement 136 system database directory 101 types CLIENT 134 DATA_ENCRYPT 134 default 134 KERBEROS 134 SERVER 134 SERVER_ENCRYPT 134 SERVER_ENCRYPT_AES 134 validation 134 authorities b
196. l tables in a DB2 database on Windows a DB2 for z OS database and a DB2 for i database all within a single transaction Multisite update and sync point manager for DB2 Connect server IBM mainframe database servers require DB2 Connect to participate in a distributed transaction originating from Linux Windows UNIX and web applications In addition many of the multisite update scenarios that involve IBM mainframe database servers require that the sync point manager SPM component be configured When a DB2 instance is created the DB2 SPM is automatically configured with default settings The need for SPM is dictated by the choice of protocol TCP IP and use of a TP monitor The following table provides a summary of scenarios that require the use of SPM The table also shows if DB2 Connect is required for any access to the IBM mainframe from Intel or UNIX machines For multisite updates the SPM component of DB2 Connect is required if you are using a TP monitor Chapter 5 Administering 111 Table 17 Multisite update scenarios that require SPM TCP IP Transaction Sync Point Manager Product Required IBM mainframe Processor Monitor Needed Choose One Database Supported Used Yes Yes DB2 Connect server DB2 for z OS V8 or product later DB2 Enterprise Server Edition with DB2 Connect license applied No No DB2 Connect DB2 for z OS V8 or Personal Edition later DB2 Connect server product DB2 Enterprise Server Edi
197. lied by the number of logical partitions on the database server computer plus the number of local application connections on the database server computer e The third section covers messages on the system MSGMNI affects the number of agents that can be started MSGMAX affects the size of the message that can be sent in a queue and MSGMNB affects the size of the queue MSGMAX should be change to 64 KB that is 65535 bytes and MSGMNB should be increased to 65535 3 To modify these kernel parameters edit the etc sysct1 conf file If this file does not exist create it The following lines are examples of what should be placed into the file kernel sem 250 256000 32 1024 Example shmmax for a 64 bit system kernel shmmax 1073741824 Example shmall for 90 percent of 16 GB memory kernel shmal 1 3774873 kernel msgmax 65535 kernel msgmnb 65535 kernel msgmni 2048 4 Run sysctl with p parameter to load in sysctl settings from the default file etc sysctl conf sysctl p 5 To make the changes effective after every reboot e SUSE Linux Make boot sysctl active e Red Hat The rc sysinit initialization script will read the etc sysct conf file automatically Modifying kernel parameters for DB2 Connect Solaris For the DB2 database system to operate properly it is recommended that you update your system s kernel configuration parameters You can use the db2osconf utility to suggest recommended kernel parameters If you w
198. lify network topology since a direct connection is established between the application server and DB2 z OS This will also eliminate network hop and DB2 Connect gateway routing Reduced resource consumption means hardware or software resources are not required for DB2 Connect server machines e Reduced footprint By replacing DB2 Connect server with DB2 Connect client you can reduce complexity and deploy application solutions with smaller footprints and achieve overall benefits e Improved availability Application access using IBM data server drivers or clients to DB2 for z OS data equals to or is superior to three tier configuration due to elimination of a point of failure e Improved monitoring A direct connection makes it easier to monitor application server or web application server traffic and behavior e Improved problem determination If an application experiences a performance problem the presence of the DB2 Connect server complicates the efforts to identify the source of the problem e Latest code levels You can obtain the latest code levels to exploit new server features and APIs Data support for some features such as new data types is easier to obtain If you replace DB2 Connect server with DB2 Connect client DB2 Connect license files are required In a DB2 Connect server configuration DB2 Connect entitlement is stored at the DB2 Connect server not at individual clients If you change to direct client connectivity you
199. ly databases that can exist within a DB2 Connect server instance are transaction manager databases and DB2 Connect federated databases The DB2 Connect transaction manager database stores transaction state information for DB2 coordinated transactions The sole purpose of DB2 Connect federated databases is to contain information about data sources 16 DB2 Connect User s Guide On Linux and UNIX operating systems you should manually upgrade your DB2 Connect instances after installing the latest version of DB2 Connect All the remote nodes and databases that you cataloged on the DB2 clients refer to these instances If you create a new instance again you will have to catalog nodes DCS databases and databases on the DB2 clients that existed in the instances from the previous version On Windows operating systems you have an option to automatically upgrade an existing supported DB2 Connect copy during installation Your DB2 Connect instances are automatically upgraded Alternatively you can install a new copy of the latest version of DB2 Connect and then manually upgrade your DB2 Connect instances This procedure describes how to upgrade by installing a new copy of the latest version of DB2 Connect and then upgrade instances and any existing databases To automatically upgrade an existing supported DB2 Connect copy on Windows refer to Upgrading a DB2 server Windows in the Upgrading to DB2 Version 10 1 Restrictions e The bit size o
200. m or use another name for your directory In this example dev cd0 is the device name b Enter the disc mount point directory in the MOUNT POINT window In this example the mount point directory is disc c In the Mount AUTOMATICALLY at system restart field select yes to enable automatic mounting of the file system d Click OK to close the window then click Cancel three times to exit SMIT 6 Mount the CD or DVD file system by entering the smit mountfs command 7 In the Mount a File System window a Enter the device name for this CD or DVD file system in the FILE SYSTEM name field In this example the device name is dev cd b Enter the disc mount point in the Directory over which to mount field In this example the mount point is disc c Enter cdrfs in the Type of Filesystem field To view the other kinds of file systems you can mount click List d In the Mount as READ ONLY system field select yes e Accept the remaining default values and click OK to close the window Chapter 3 Installing DB2 Connect server 37 Results Your CD or DVD file system is now mounted To view the contents of the CD or DVD place the disk in the drive and enter the cd disc command where disc is the disc mount point directory Installing a DB2 Connect server product HP UX To define your installation preferences and to install a DB2 Connect product on HP UX use the DB2 Setup wizard Before you begin Before you begin your ins
201. mand line processor J lt 172 Accessing different versions of the DB2 Information Center 172 Updating the DB2 nformation Center installed c on your computer or intranet server 172 Manually updating the DB2 Information Center installed on your computer or intranet server 174 DB2 tutorials g 175 DB2 troubleshooting a ee ee 176 Terms and conditions 176 Appendix B Notices 179 Index 183 About this book The DB2 Connect User s Guide provides all the information you need to learn about and use the DB2 Connect product DB2 Connect concepts are presented with typical scenario showing the relationships between DB2 Connect and the other parts of the network environment Considerations involving database directories security between systems multisite updates moving data and monitoring DB2 Connect are discussed How DB2 Connect supports high availability in your network environment is presented Ensuring good performance by DB2 Connect and across the network is introduced as are some topics concerned with troubleshooting possible problems Who should use this book System administrators database administrators and system communications specialists would all be interested in part or all of this book Copyright IBM Corp 1993 2012 v vi DB2 Connect User s Guide Chapter 1 DB2 Connect overview DB2 Connect provides connectivity to mainframe and midrange databases from Linux UNIX and Windows operat
202. mber It is used for correlating transactions on different systems Host Database Name The real name of the database to which the application is connected In the DCS directory this is the target database name 124 DB2 Connect User s Guide Host Product ID The product and version that is running on the server It is in the form PPPVVRRM where PPP Identifies the IBM mainframe database server product for example DSN for DB2 Universal Database for z OS and OS 390 ARI for DB2 Server for VSE amp VM or QSQ for IBM DB2 for IBM i VV Represents a two digit version number such as 08 RR Represents a two digit release number such as 01 M Represents a one character modification level 0 9 or A Z LIST DCS APPLICATIONS EXTENDED You can use the LIST DCS APPLICATIONS command with the option EXTENDED in order to generate an Extended Report The Extended Report lists all the fields that are listed when the SHOW DETAIL option is specified on the command plus nine new fields DCS application status Status change time Client platform Client protocol Host Coded Character Set Identifier CCSID Client login ID Process ID of client application Database alias at the gateway DCS database name While the existing command options list the fields horizontally one line per application the new option lists them vertically one field per line Here is the new syntax of the command LIST DCS APPLICATIONS SHOW DETAIL EXTEN
203. mmand lists all recognized CD or DVD drives and their associated device files The file name will be something similar to dev dsk c1t2d0 4 Mount the CD or DVD drive to the mount point directory mount F cdfs o rr dev dsk clt2d cdrom 5 Obtain a file listing to verify the mount using the 1s cdrom command 6 Log out Results Your CD or DVD file system is now mounted View the contents of the CD or DVD by placing it in the drive and enter the cd cdrom command where cdrom is the mount point directory Installing a DB2 Connect server product Linux To define your installation preferences and to install a DB2 Connect product on Linux use the DB2 Setup wizard Before you begin Before you begin your installation e You can install DB2 Connect using either root or non root user authority e Ensure that your system meets Disk and memory requirements Hardware distribution and software requirements Refer to Installation requirements for DB2 Connect server products Linux on page 43 e The DB2 database product DVD must be mounted on your system e The DB2 Connect product image must be available If you are installing a non English version of a DB2 Connect product you must also have the appropriate National Language Packages e To locate DB2 database products already installed on your system use the db21s command e The DB2 Setup wizard is a graphical installer You must have X windows software capable of
204. mmand outside the mounted CD location with either a relative or absolute path name to ensure the DB2 Connect product CD can be unmounted as required From this window you can view the installation prerequisites and the release notes or you can proceed directly to the installation 4 Once you have initiated the installation proceed through the DB2 Setup wizard installation panels and make your selections Installation help is available to guide you through the DB2 Setup wizard Click Help to invoke the online help You can click Cancel at any time to exit the installation DB2 files will only be copied to your system once you have clicked Finish on the last DB2 Setup wizard installation panel Once completed the DB2 Connect server product is installed using the opt 1BM db2 V9 8 default installation path 34 DB2 Connect User s Guide If you are installing on a system where this directory is already being used the DB2 Connect product installation path will have _xx added to it where xx are digits starting at 01 and increasing depending on how many DB2 copies you have installed You can also specify your own DB2 database product installation path Results National Language Packs can also be installed by running the db2setup command from the directory where the National Language Pack resides after a DB2 Connect product has been installed The installation logs db2setup 1og and db2setup err will be located by default in the tmp direct
205. must store the DB2 Connect entitlement at each client Functionality in DB2 features in DB2 Connect product editions Some functionality is available in only certain DB2 Connect product editions In some cases the functionality is associated with a particular DB2 feature The table indicates which functionality is included in a DB2 Connect product edition If the functionality is not applicable to the DB2 Connect products the value Not applicable is specified Table 1 Functionality in DB2 Connect product editions DB2 Connect Personal Functionality Edition DB2 Connect server editions Adaptive Compression No No Advanced Copy Service No Yes Compression backup No No Compression Data No No 2 DB2 Connect User s Guide Table 1 Functionality in DB2 Connect product editions continued DB2 Connect Personal Functionality Edition DB2 Connect server editions Compression Index No No Compression Temp table No No Compression XML No No Connection concentrator No Yes Continuous Data Ingest No No Database partitioning No No DB2 Governor No Yes Heterogeneous Federation No No High availability disaster No Yes recovery Homogeneous Federation No Yes Homogeneous Q Replication No No IBM Data Studio Version 3 1 Yes Yes IBM InfoSphere Optim No No Performance Manager Extended Edition IBM InfoSphere Opt
206. n is established or the number of attempts time out DB2 Connect and transaction processing monitors An application server permits a large number of users to execute applications using a minimum of system resources An application server can be extended to allow coordinated transactions to be invoked from applications executed by the application server This transaction coordination is generally known as a Transaction Processing TP monitor A TP monitor works in conjunction with an application server A transaction can be thought of as a routine event usually a request for service in running the day to day operations of an organization The orderly processing of transactions is the type of work for which TP monitors were designed 8 DB2 Connect User s Guide Transaction processing Every organization has rules and procedures that describe how it is supposed to operate The user applications which implement these rules can be called business logic The transactions these business applications execute are often referred to as Transaction Processing or Online Transaction Processing OLTP The key characteristics of commercial OLTP are Many Users It is common for transaction processing to be used by the majority of the people in an organization since so many people affect the current state of the business Repetitive Most interactions with the computer tend to be the same process executed over and over again For example enteri
207. n Center Version 10 1 directory where Program_Files represents the location of the Program Files directory c Navigate from the installation directory to the doc bin directory d Run the help_start bat file 174 DB2 Connect User s Guide help_start bat e On Linux a Navigate to the path where the Information Center is installed By default the DB2 Information Center is installed in the opt ibm db2ic V10 1 directory b Navigate from the installation directory to the doc bin directory c Run the help_start script help_start The systems default Web browser opens to display the stand alone Information Center 3 Click the Update button a JavaScript must be enabled in your browser On the right panel of the Information Center click Find Updates A list of updates for existing documentation displays 4 To initiate the installation process check that the selections you want to install then click Install Updates 5 After the installation process has completed click Finish 6 Stop the stand alone Information Center e On Windows navigate to the doc bin directory within the installation directory and run the help_end bat file help_end bat Note The help_end batch file contains the commands required to safely stop the processes that were started with the help_start batch file Do not use Ctrl C or any other method to stop help_start bat e On Linux navigate to the doc bin directory within the installation dire
208. nd hence the DB2 service needs to be running in the security context of a domain user An error will occur if you use a domain user account to perform a database operation against a DB2 service running with either a Local user account or a LocalSystem account You may also use the built in LocalSystem account to run the installation for all products except for DB2 Enterprise Server Edition You can create the DB2 instance user account before installing DB2 or you can have the DB2 Setup wizard create it for you If you want to have the DB2 Setup wizard create a new domain user account the user account you use to perform the installation must have authority to create domain user accounts This account will be granted the following user rights e Act as part of the operating system e Debug programs e Create token object e Increase quotas e Lock pages in memory e Log on as a service e Replace a process level token If extended security is enabled then the DB2ADMNS group will have all these privileges You can add users to that group and you do not need to add these privileges explicitly However the user still needs to be a member of the Local Administrators group The Debug programs privilege is only needed when DB2 group lookup is explicitly specified to use the access token If the user account is created by the install program the user account will be granted these privileges and if the user account already exists this a
209. nd then a non Administrator attempts to install DB2 Connect on the same system In this scenario the install will fail and return an error message that the user must be an Administrator to install the product An Administrator always has the authority to uninstall or reinstall 54 DB2 Connect User s Guide e Non Administrator users cannot uninstall a DB2 product Those non Administrator users on a Windows Vista and later operating system can uninstall a DB2 product Typical steps required to install and configure DB2 Connect Personal Edition Setting up DB2 Connect Personal Edition is a multi step process The typical steps required to install and configure DB2 Connect Personal Edition include verifying system prerequisites installing the DB2 Connect software testing the connection and binding programs and utilities Note If you have a computer with a IBM data server client installed you can activate DB2 Connect Personal Edition by registering your DB2 Connect Personal Edition license to that computer 1 Determine how you want to use DB2 Connect in your network 2 Verify that you have the correct hardware and software prerequisites on both your workstation and the IBM mainframe database server 3 Verify that your IBM mainframe database server is configured to accept connections from DB2 Connect 4 Install your DB2 Connect software You will use this workstation to configure and verify your IBM mainframe connections
210. necting to the DB2 Connect server still has a handle on the TCP IP port On each client machine that is connected to the DB2 Connect server enter the following commands db2 terminate db2stop SQL30020 Symptom SQL30020N Execution failed because of a Distributed Protocol Error that will affect the successful execution of subsequent commands and SQL statements Solutions Service should be contacted with this error Run the db2support command before contacting service SQL30060 Symptom SQL30060N lt authorization ID gt does not have the privilege to perform operation lt operation gt Solution When connecting to DB2 for z OS the Communications Database CDB tables have not been updated properly SQL30061 Symptom Connecting to the wrong IBM mainframe database server location no target database can be found Solution The wrong server database name might be specified in the DCS directory entry When this occurs SQLCODE 30061 is returned to the application Check the DB2 node database and DCS directory entries The target database name field in the DCS directory entry must correspond to the name of the database based on the platform For example for a DB2 for z OS database the name to be used should be the same as that used in the Boot Strap Data Set BSDS LOCATION locname field which is also provided in the DSNLO04I message LOCATION location when the Distributed Data Facility DDF is started 166 DB2 C
211. needed or if only column 1 and column 2 are needed Data blocking You should use data blocking if you expect large amounts of data from the server Blocking improves the use of the network bandwidth and reduces the CPU usage of both the IBM mainframe database server and the DB2 Connect server There is fixed amount of CPU and network usage for each message sent and received regardless of size Data blocking reduces the number of messages required for the same amount of data transfer With blocking the first row of data from a query will not be delivered to the application until the first block is received Blocking increases the retrieval time for the first row but improves the retrieval time for subsequent rows Another consideration is the amount of memory that is used The memory working set usually increases when blocking is turned on Within DB2 Connect you can control the amount of data that is transferred within each block To invoke blocking use the BLOCKING option of the prep or bind command Blocking is on if e The cursor is read only or e The cursor is ambiguous and blocking is specified during the prep or bind Note When using dynamic SQL the cursor is always ambiguous SQL statements with BLOCKING Updatable SELECT statements using UPDATE DELETE WHERE CURRENT OF statements are non blocking queries so you should use them only when absolutely necessary Chapter 9 Tuning 141 An updatable SELECT ensures th
212. nfiguring TCP IP for DB2 fot Z Os eas Configuring DB2 for z OS 80 Preparing DB2 for VSE amp VM for commections fom DB2 Connect oh Je 80 Sysplex support 80 DB2 Connect server Sysplex support 80 Configuring connections to IBM mainframe database servers 83 Registering a DB2 Connect license key using the db2licm command S 4 x 83 Chapter 5 Administering 85 Binding applications and utilities DB2 Gennes server A 85 Moving data with DB2 Connect 88 Automatic client reroute description and setup DB2 Connect server 90 Administering DB2 Connect systems 91 Overview 91 Distributed Relational Databas rchitechire 96 Updating database directories 100 DB2 Connect and SQL statements 109 Multisite Updates 110 SQLCODE mapping lt H13 iii Chapter 6 Monitoring DB2 Connect server gt ie he hs cas hE 119 Monitoring connections for remote clients 119 Monitoring performance using the Windows Performance Monitor 119 Using the GET SNAPSHOT commatds 120 DCS application status yo 2 8 122 Chapter 7 Developing database applications ae 127 Running your own applications 127 Chapter 8 Security 129 Trusted connections through DB2 Connect 129 Creating and terminating a trusted connection through CLI 130 Switching users on a trusted Connection through CLI lt 131 DB2 Connect authentic tiori consid rations 134 Kerberos support 135 Authentication
213. ng HP UX 38 40 installing Linux 41 43 installing overview 32 installing Solaris Operating System 44 46 61 installing Windows 48 49 post upgrade tasks 18 pre upgrade tasks 15 Sysplex support 80 81 System i support 6 overview 95 upgrading overview 13 14 procedure 16 Windows user accounts 65 zSeries support 95 DB2 for VM amp VSE preparing for connections from DB2 Connect 80 DB2 for z OS node directory values 101 updating system tables 80 DB2 Information Center updating 172 174 versions 172 DB2 Setup wizard language identifiers 22 DB2ADMNS group adding users 69 db2licm command registering licenses 68 84 setting license policy 68 db2osconf command determining kernel configuration parameter values 30 db2setup command language setting 21 DB2USERS user group adding users 69 DCS Database Connection Services directory see Database Connection Services DCS directory 102 dceslarimap file 114 dcsidsn map file 114 dcslqsq map file 114 ddcs400 Ist file 85 ddcsmvs Ist file 85 ddcsvm lst file 85 ddcsvse lst file 85 default language setting Windows 23 DESCRIBE statement compound SQL statements 140 performance with PREPARE statement 140 diagnostic information overview 163 dir_cache parameter 150 directories customizing 106 system database updating 100 values 101 directory cache support configuration parameter DB2 Connect tuning 150 directory schema extending Windows 53 66 Distributed Data Management DDM
214. ng an order or processing payments are used many times every day Short Interactions Most interactions that people in the organization have with the transaction processing system are short in duration Data Sharing Since data represents the state of the organization there can only be a single copy of the data Data Integrity The data must represent the current state of the organization and must be internally consistent For example every order must be associated with a customer record Low Cost Transaction Since the transaction processing represents a direct cost of doing business the cost of the system must be minimal DB2 Connect allows applications under the control of an application server running on Linux UNIX and Windows to execute transactions against remote LAN and IBM mainframe database servers and have these transactions coordinated by a TP monitor Chapter 1 DB2 Connect overview 9 non DB2 XA capable RM e g Oracle MQ file Jane Mike Select name Update Tom Sue from i f TP monitor SQL and XA zA Encina Tuxedo WebLogic TP monitor API flows Client Client Pee Client Figure 3 DB2 Connect support for TP monitors In Figure 3 the APIs as well as the connectivity mechanism between the application server and the back end database servers are provided by a DB2 Connect server product such as DB2 Connect Enterprise Edition Examples of transaction processing moni
215. ng them with periods Commas are used to separate output tokens If no token is specified before a comma no output token is included in the SQLCA for that position Any tokens occurring in the output SQLCA following the last specified token are mapped to a null token Example Figure 8 shows a sample SQLCODE mapping file amp amp 007 007 1 010 060 171 a 2 1204 204 c1 2c 16332 206 cli 30021 30021 clc c2c cc00 A 000 U 3 969 s s P 965 s Figure 8 An SQLCODE Mapping File 116 DB2 Connect User s Guide The following descriptions correspond to the matching line number in the previous figure 1 The SQLCODE is mapped from 007 to 007 The first input token received from the IBM mainframe database server is used as the first output token and it defaults to CHAR No other tokens are transferred The SQLCODE is mapped from 010 to 010 no output SQLCODE is specified No tokens are put into the output SQLCA The SQLCODE is mapped from 060 to 171 The first input token received from the IBM mainframe database server is discarded The second is used as the first token in the output SQLCA and it is CHAR There is no second token in the output SQLCA The SQLCODE is mapped from 204 to 204 The first and second tokens received from the IBM mainframe database server are CHAR These two input tokens are combined to form one CHAR output token which will be the fi
216. nlpack disk2 in the same directory and allows the installer to automatically find the installation images without prompting Enter the db2setup command from the directory where the product image resides to start the DB2 Setup wizard After a few moments the IBM DB2 Setup Launchpad opens For multiple CD installations issue the db2setup command outside the mounted CD location with either a relative or absolute path name to ensure the DB2 Connect product CD can be unmounted as required From this window you can view the installation prerequisites and the release notes or you can proceed directly to the installation Once you have initiated the installation proceed through the DB2 Setup wizard installation panels and make your selections Installation help is available to guide you through the DB2 Setup wizard Click Help to invoke the online help You can click Cancel at any time to exit the installation DB2 files will only be copied to your system once you have clicked Finish on the last DB2 Setup wizard installation panel Once completed the DB2 Connect server product is installed using the opt 1BM db2 V10 1 default installation path If you are installing on a system where this directory is already being used the DB2 Connect product installation path will have _xx added to it where xx are digits starting at 01 and increasing depending on how many DB2 copies you have installed You can also specify your own DB2 database product installa
217. nnect Servers DB2 Connect User s SC27 3863 00 Yes April 2012 Guide Appendix A Overview of the DB2 technical information Displaying SQL state help from the command line processor DB2 products return an SQLSTATE value for conditions that can be the result of an SQL statement SQLSTATE help explains the meanings of SQL states and SQL state class codes Procedure To start SQL state help open the command line processor and enter sqlstate or class code where sqlstate represents a valid five digit SQL state and class code represents the first two digits of the SQL state For example 08003 displays help for the 08003 SQL state and 08 displays help for the 08 class code Accessing different versions of the DB2 Information Center Documentation for other versions of DB2 products is found in separate information centers on ibm com About this task For DB2 Version 10 1 topics the DB2 Information Center URL is http publib boulder ibm com infocenter db2luw v10r1 For DB2 Version 9 8 topics the DB2 Information Center URL is http publib boulder ibm com infocenter db2luw v9r8 For DB2 Version 9 7 topics the DB2 Information Center URL is http publib boulder ibm com infocenter db2luw v91r7 For DB2 Version 9 5 topics the DB2 Information Center URL is http publib boulder ibm com infocenter db2luw v915 For DB2 Version 9 1 topics the DB2 Information Center URL is http publib boulder ibm com
218. nstallation 6 Once you have initiated the installation proceed by following the setup program s prompts Online help is available to guide you through the remaining steps Click Help to invoke the online help You can click Cancel at any time to end the installation A log file stores general information and error messages resulting from the install and uninstall activities The file name of the log follows the format DB2 Product Abrreviation Date Time log such as DB2 CLIENT 10 06 2006_17_23_42 1log By default the log file is located in the My Documents DB2L0G directory e To invoke the DB2 Setup wizard manually 1 Click Start and select the Run option 2 In the Open field enter the following command x setup i language where x represents your DVD drive language represents the territory code for your language for example EN for English 3 Click OK If you want your DB2 database product to have access to DB2 documentation either on your local computer or on another computer on your network then you must install the DB2 Information Center The DB2 Information Center contains documentation for DB2 database systems and related products Installation requirements for DB2 Connect Personal Edition Windows Before you install DB2 Connect Personal Edition on Windows operating systems ensure that the system you choose meets the necessary operating system hardware software and communications requirements Chapter
219. nt the CD or DVD mkdir p cdrom unnamed_cdrom mount F hsfs o ro dev dsk cQt6d0s2 cdrom unnamed_cdrom where dev dsk c0t6d0s2 represents the name of the device that was returned in the preceding step and cdrom unnamed_cdrom represents the CD or DVD mount directory 3 Log out Results Your CD or DVD file system is now mounted View the contents of the CD or DVD by placing the disk in the drive and enter the ed cdrom command where cdrom is the mount point directory Installing DB2 Connect Personal Edition Windows You can install DB2 Connect Personal Edition on Windows operating systems using the DB2 Setup wizard Installing IBM Data Server Driver Package DS Driver and then applying the DB2 Connect Personal Edition license is the preferred alternative to the process listed in the following section Refer to IBM data server client types for details Before you begin Before you launch the DB2 Setup wizard e Ensure that your system meets the following requirements Hardware and software requirements Disk and memory requirements e If you are installing on Windows and intend to use Lightweight Directory Access Protocol LDAP you must extend the directory schema e It is recommended that you use an Administrator account to perform the installation The Administrator account must belong to the local administrator s group on the Windows computer where you are installing your DB2 database product and should have the follo
220. ntry for example 2001 05 30 14 09 55 321092 Instance svtdbm5 Node 000 PID 10296 db2tcpcm Appid none common_communication sqlcctcpconnmgr_child Probe 46 DIA3205E Socket address 30090 configured in the TCP IP services file and required by the TCP IP server support is being used by another process Solution This warning is a symptom which signals that DB2 Connect acting as a server for remote clients is having trouble handling one or more client communication protocols These protocols can be TCP IP and others and Copyright IBM Corp 1993 2012 165 usually the message indicates that one of the communications protocols defined to DB2 Connect is not configured properly Often the cause might be that the DB2COMM profile variable is not defined or is defined incorrectly Generally the problem is the result of a mismatch between the DB2COMM variable and names defined in the database manager configuration for example svcename or nname One possible scenario is having a previously successful connection then getting the SQL5043 error message while none of the configuration has changed This could occur using the TCP IP protocol when the remote system abnormally terminates the connection for some reason When this happens a connection might still appear to exist on the client and it might become possible to restore the connection without further intervention by issuing the following commands Most likely one of the clients con
221. oblem free operation To install a fix pack successfully perform all of the necessary preinstallation and post installation tasks About this task A DB2 fix pack contains updates and fixes for problems Authorized Program Analysis Reports or APARs found during testing at IBM as well as fixes for problems reported by customers The APARLIST TXT file describes the fixes contained in each fix pack and it is available for download at ftp ftp software ibm com ps products db2 fixes english us aparlist Fix packs are cumulative This means that the latest fix pack for any given version of DB2 database contains all of the updates from previous fix packs for the same version of DB2 database The fix pack images available are e A single server image The single server image contains the new and updated code required for all DB2 database server products and the IBM Data Server Client If more than one DB2 database server product is installed in a single location the DB2 database server fix pack applies maintenance code updates to all the installed DB2 database server products The Data Server Client fix pack is contained within the one DB2 database server fix pack namely the fix pack that can service any one of the following database server products DB2 Enterprise Server Edition DB2 Workgroup Server Edition DB2 Express Edition DB2 Connect Enterprise Edition DB2 Connect Application Server Edition DB2 Connect Unlimited Edition for zSe
222. ocal file system by using a machine that is connected to the internet and has the DB2 Information Center installed If many users on your network will be installing the documentation updates you can reduce the time required for individuals to perform the updates by also mirroring the update site locally and creating a proxy for the update site If update packages are available use the Update feature to get the packages However the Update feature is only available in stand alone mode 3 Stop the stand alone Information Center and restart the DB2 Information Center on your computer Note On Windows 2008 Windows Vista and higher the commands listed later in this section must be run as an administrator To open a command prompt or graphical tool with full administrator privileges right click the shortcut and then select Run as administrator Procedure To update the DB2 Information Center installed on your computer or intranet server 1 Stop the DB2 Information Center e On Windows click Start gt Control Panel gt Administrative Tools gt Services Then right click DB2 Information Center service and select Stop e On Linux enter the following command etc init d db2icdv10 stop 2 Start the Information Center in stand alone mode e On Windows a Open a command window b Navigate to the path where the Information Center is installed By default the DB2 Information Center is installed in the Program_Files IBM DB2 Informatio
223. og dcs database nydb2 as new_york parms LOCALDATE YYYYMMDD catalog database nydb2 as newyork2 at node nynode authentication server The database alias newyork2 gives you access to the same host database but in addition it has a date format mask specified This example illustrates that the date format mask is specified using the keyword LOCALDATE and is the seventh positional parameter in the PARMS field of a DCS directory entry For the date mask to be valid ALL of the following conditions must be true 1 There can only be at most one sequence each of Y s M s and D s where Y is a year digit M is a month digit and D is a day digit 2 The maximum number of Y s in a sequence is 4 3 The maximum number of M s in a sequence is 2 4 The maximum number of D s in a sequence is 2 Chapter 5 Administering 105 For instance the following date format masks are all valid date masks YYyyMmDd Y M and D digits are case insensitive MM DD YYYY OK to have a mask longer than 10 bytes and to have characters other than Y M and D in the mask abcYY MM OK not to have a sequence of D s The following date format masks are all invalid date masks YYYYyMMDD invalid there are 5 Y s in a sequence YYYYMDDM invalid there are 2 sequences of M s If a date format mask is invalid no error will be issued It will just be ignored Just because a date mask is valid does not mean it will be used Date format
224. on DRDA also supports multi tier connections between an application requester and a server In this topology the server that an application requester connects to is an Chapter 5 Administering 97 application server but any other server further downstream is called a database server DS as it does not interact directly with the application requester In addition to highlight its role as neither the system where a database request originates nor the system that performs the database function for the request each application server or database server between an application requester and the final database server is also called an intermediate server The use of database servers and intermediate servers is supported by DB2 Connect Figure 6 shows the flow of data between the DB2 Connect workstation and the IBM mainframe server in the case where there are local clients only DB2 Connect Workstation Host or IBM i DB2 server DRDA Application Application Program Server DRDA Protocol DRDA Application Requester Database Management System Figure 6 Data flow between a DB2 Connect server and an IBM mainframe server To implement the connections between DRDA server database management systems and IBM data server clients DRDA uses the following architectures e Character Data Representation Architecture CDRA e Distributed Data Management Architecture DDM e Formatted Data Object Content Architecture FD OCA e Transmissi
225. on Control Protocol Internet Protocol TCP IP These architectures are used as building blocks The data streams which flow over the network are specified by the DRDA architecture which documents a data stream protocol supporting distributed relational database access A request is routed to the correct destination by means of directories that contain various types of communication information and the name of the DRDA server database being accessed Remote unit of work A remote unit of work lets a user or application program read or update data at one location per unit of work It supports access to one database within a unit of work While an application program can update several remote databases it can only access one database within a unit of work Remote unit of work has the following characteristics e Multiple requests SQL statements per unit of work are supported e Multiple cursors per unit of work are supported e Each unit of work can update only one database e The application program either commits or rolls back the unit of work In certain error circumstances the database server or DB2 Connect might roll back the unit of work 98 DB2 Connect User s Guide For example Figure 7 shows a database client running a funds transfer application that accesses a database containing checking and savings account tables as well as a transaction fee schedule The application must e Accept the amount to transfer from the user int
226. on is installed using the opt IBM db2 V10 1 default installation path If you are installing on a system where this directory is already being used the DB2 Connect product installation path will have _xx added to it where xx are digits starting at 01 and increasing depending on how many DB2 copies you have installed You can also specify your own DB2 Connect product installation path Results National Language Packs can also be installed by running the db2setup command from the directory where the National Language Pack resides after a DB2 Connect product has been installed The installation logs db2setup 1og and db2setup err will be located by default in the tmp directory You can specify the location of the log files If you want your DB2 database product to have access to DB2 documentation either on your local computer or on another computer on your network then you must install the DB2 Information Center The DB2 Information Center contains documentation for the DB2 database and DB2 related products See the Installing the DB2 Information Center using the DB2 Setup wizard UNIX topic in Installing DB2 Servers Installation requirements for DB2 Connect products Solaris Before you install DB2 Connect products on the Solaris Operating System ensure that the system you choose meets the necessary operating system hardware software and communications requirements 60 DB2 Connect User s Guide To install a DB2 Conne
227. on programming interface API was used to create the program 162 DB2 Connect User s Guide e Have other applications that use the software or communication APIs been run on the user s system e Has a fix pack recently been installed If the problem occurred when a user tried to use a feature that had not been used or loaded on their operating system since it was installed determine IBM s most recent fix pack and load it after installing the feature 2 Has this error occurred before e Are there any documented resolutions to previous error conditions e Who were the participants and can they provide insight into a possible course of action 3 Have you explored using communications software commands that return information about the network e TCP IP might have valuable information retrieved from using TCP IP commands and daemons 4 Is there information returned in the SQLCA SQL communication area that can be helpful e Problem handling procedures should include steps to examine the contents of the SQLCODE and SQLSTATE fields e SQLSTATEs allow application programmers to test for classes of errors that are common to the DB2 family of database products In a distributed relational database network this field might provide a common base 5 Was START DBM executed at the Server Additionally ensure that the DB2COMM environment variable is set correctly for clients accessing the server remotely 6 Are other machines perf
228. on requirements For TCP IP connectivity no additional software is required Mounting the CD or DVD for DB2 Connect Linux To mount a CD ROM on Linux operating systems issue the mount command Before you begin Depending on your system configuration you might need root user authority to mount discs Procedure To mount the CD or DVD on Linux operating systems 1 Insert the CD or DVD in the drive and enter the following command mount t iso9660 o ro dev cdrom cdrom where cdrom represents the mount point of the CD or DVD 2 Log out Results Your CD or DVD file system is now mounted View the contents of the CD or DVD by placing the disc in the drive and enter the cd cdrom command where cdrom is the mount point directory Installing a DB2 Connect server product Solaris To define your installation preferences and to install a DB2 Connect product on the Solaris Operating System use the DB2 Setup wizard Before you begin Before you begin your installation e You can install DB2 Connect using either root or non root user authority e Ensure that your system meets Disk and memory requirements Hardware distribution and software requirements Refer to Installation requirements for DB2 Connect server products AIX on page 35 e The DB2 database product DVD must be mounted on your system e The DB2 Connect product image must be available If you are installing a non English version of a DB2 Connect product
229. on will fail Extended security on Windows DB2 database products offer extended Windows security If the extended security feature is selected you must add the users who will administer or use the DB2 database product to either the DB2ADMNS or DB2USERS group as appropriate The DB2 installer creates these two new groups You can either specify a new name or accept the default names during installation To enable this security feature select the Enable operating system security check box on the Enable operating system security for DB2 objects panel during the DB2 installation Accept the default values for the DB2 Administrators Group field and the DB2 Users Group field The default group names are DB2ADMNS and DB2USERS If there is a conflict with existing group names you will be prompted 50 DB2 Connect User s Guide to change the group names If required you can specify your own group names DB2 server user accounts Installation user account A local or domain user account is required to perform the installation Normally the user account must belong to the Administrators group on the computer where you will perform the installation Alternatively a non Administrator user account can be used This alternative requires that a member of the Windows Administrators group first configure the Windows elevated privileges settings to allow a non Administrator user account to perform an installation On Windows 2008 and Windows Vista or
230. onnect Linux i Modifying Remei parameters for DB2 Connect Solaris DB2 Connect server products installation and configuration overview i Installing a DB2 Connect server product AD 13 14 15 16 18 21 sl 21 21 22 23 24 29 25 26 28 s29 29 30 30 sal 32 08 Installing a DB2 Connect server product HP UX 38 Copyright IBM Corp 1993 2012 Installing a DB2 Connect server product Linux 41 Installing a DB2 Connect server product Solaris 44 Installing a DB2 Connect server product Windows 48 Typical steps required a install and configure DB2 Connect Personal Edition 55 Installing DB2 Connect Personal Edition Linux 55 Installing DB2 Connect Personal Edition Solaris 58 Installing DB2 Connect Personal Edition Windows i 62 Maintaining license keys 68 Registering a DB2 Connect Dee key using the db2licm command 68 Setting the DB2 Connect license policy using the db2licm command i 68 Post installation tasks 69 Adding your user ID to the DB2ADMNS ad DB2USERS user groups Windows 69 Applying fix packs to DB2 Connect 70 Uninstalling n 12 Uninstalling DB2 Coniiect Windows D i es Uninstalling DB2 Connect Linux and UNIX 73 Chapter 4 Configuring 75 Preparing IBM DB2 for IBM i for connections eo DB2 Connect s75 Preparing DB2 for z Os or connactiana ow DB2 Connect p o 76 Host databases lt 77 Co
231. onnect User s Guide SERVER_ENCRYPT_AES The transferred user IDs and passwords are encrypted using an Advanced Encryption Standard AES encryption algorithm at the client and validated at the System z database server Kerberos authentication is unique in that the client does not pass a user ID and password directly to the server Instead Kerberos acts as a third party authentication mechanism The user enters an ID and password once at the client terminal and Kerberos validates this sign on After this Kerberos automatically and securely passes the user s authorization to any local and network services requested This means that the user does not need to re enter an ID and password to log into a remote DB2 server The single sign on capability provided by Kerberos authentication requires that both DB2 Connect and the database server that it is connecting to provide Kerberos support Note There is no support for the GSSPLUGIN authentication type Kerberos support The Kerberos authentication layer which handles the ticketing system is integrated into the Windows 2000 Active Directory mechanism The client and server sides of an application communicate with the Kerberos SSP Security Support Provider client and server modules The Security Support Provider Interface SSPI provides a high level interface to the Kerberos SSP and other security protocols Typical setup To configure DB2 database products with Kerberos authentication set up
232. onnect User s Guide The correct commands for a TCP IP node are db2 catalog tcpip node node_name remote host_name_or_address server port_no_or_service_name db2 catalog dcs database local_name as real_db_name db2 catalog database local_name as alias at node node_name authentication server To connect to the database you then issue db2 connect to alias user user_name using password SQL30081N with Return Code 79 Symptom SQL30081N A communication error has been detected Communication protocol being used TCP IP Communication API being used SOCKETS Location where the error was detected Communication function detecting the error connect Protocol specific error code s 79 x x SQLSTATE 08001 Solution s This error can occur in the case of a remote client failing to connect to a DB2 Connect server It can also occur when connecting from the DB2 Connect server to a IBM mainframe database server 1 The DB2COMM profile variable might be set incorrectly on the DB2 Connect server Check this For example the command db2set db2comm tcpip should appear in sq 1ib db2profile when running DB2 Enterprise Server Edition on AIX There might be a mismatch between the TCP IP service name and port number specifications at the IBM data server client and the DB2 Connect server Verify the entries in the TCP IP services files on both machines Check that DB2 is started on the DB2 Connect server Set the Database Manager
233. onnect server product are as follows 1 Determine how you want to use DB2 Connect in your network 2 Verify that you have the correct hardware and software prerequisites on both your workstation and the host database server 3 Verify that your IBM mainframe database server is configured to accept connections from DB2 Connect servers 4 Install your DB2 Connect software You will use this workstation to configure and verify your IBM mainframe connections Use the related links to find the details specific to the installation of a DB2 Connect server product on your operating system 5 After installation establish the connection between DB2 Connect and your IBM mainframe database system DB2 Connect can locate and configure all TCP IP connections for you You can use the DB2 command line processor CLP commands to configure IBM mainframe databases 6 Bind the programs and utilities provided with DB2 Connect to your IBM mainframe database 7 Test the connection Optional Enable the Multisite Update feature 32 DB2 Connect User s Guide 9 If you are planning to use WebSphere transaction monitors or your own application server software install these products or applications For information about installing WebSphere consult the documentation provided with these products as part of the DB2 Connect server product package For other products consult the installation documentation provided with the product 10 Install
234. ons are supported Features This edition of DB2 Connect is intended for personal workstation use and application connectivity Server or gateway functionality is not available For complete details about features provided in this edition visit http www ibm com software data db2 db2connect edition pe html This edition of DB2 Connect is not intended to enable application servers and should not be installed on such servers 64 DB2 Connect User s Guide Required user accounts for installation of DB2 Connect Personal Edition Windows If you are installing DB2 Connect Personal Edition product on Windows you require an installation user account The installation user account is the account of the user performing the installation The installation user account must be defined before running the DB2 Setup wizard The setup user accounts can be defined before installation or you can have the DB2 Setup wizard create them for you All user account names must adhere to your system naming rules and to DB2 naming rules If you use an installation user account that contains non English characters which are not specified in DB2 naming rules the DB2 installation will fail A local or domain user account is required to perform the installation Normally the user account must belong to the Administrators group on the computer where you will perform the installation Alternatively a non Administrator user account can be used This alternative
235. or Linux and UNIX environments INSTHOME sq 1ib db2dump m where INSTHOME is the home directory of the instance For supported Windows environments Chapter 10 Troubleshooting 163 If the DB2INSTPROF environment variable is not set then x SQLLIB DB2INSTANCE is used where x SQLLIB is the drive reference and the directory specified in the DB2PATH registry variable and the value of DB2INSTANCE has the name of the instance Note The directory does not have to be named SQLLIB If the DB2 registry variable DB2INSTPROF is set then x DB2INSTPROF DBZINSTANCE is used where x DB2INSTPROF is the path specified in the DB2ZINSTPROF registry variable and DB2INSTANCE is the name of the instance by default the value of DB2INSTDEF on Windows 32 bit operating systems e For Windows operating systems you can use the Event Viewer to view the administration notification log e The available diagnostic tools that can be used include db2trc db2pd db2support and db2diag e For Linux and UNIX operating systems the ps command which returns process status information about active processes to standard output e For UNIX operating systems the core file that is created in the current directory when severe errors occur It contains a memory image of the terminated process and can be used to determine what function caused the error 164 DB2 Connect User s Guide Chapter 11 Messages Common DB2 Connect problems There are common s
236. or every database that DB2 Connect accesses e node directory which contains network address and communication protocol information for every IBM mainframe database server that DB2 Connect accesses e database connection services DCS directory which contains information specific to IBM mainframe database server databases Database directories can be updated by cataloging your databases nodes or DCS directory 100 DB2 Connect User s Guide Procedure To update database directories 1 Collect database directory information using the directory customization worksheet Refer to Directory customization worksheet on page 106 2 Update the directories with information about remote database server machines by cataloging your databases nodes or DCS directory See Configuring connections to IBM mainframe database servers on page 83 for details on how to catalog databases nodes or DCS directory System database directory values A system database directory exists for each instance of the database manager and contains one entry for each database that has been cataloged for this instance In DB2 Connect products the system database directory contains information about the name alias node name and authentication type of each database You can specify the following information in the system database directory Database name The same value that you wrote in the DCS Directory Parameters table Database alias An alias
237. orming the same task able to connect to the server successfully The maximum number of clients attempting to connect to the server might have been reached If another client disconnects from the server is the client who was previously unable to connect now able to connect 7 Does the machine have the proper addressing Verify that the machine is unique in the network 8 When connecting remotely has the proper authority been granted to the client Connection to the instance might be successful but the authorization might not have been granted at the database or table level 9 Is this the first machine to connect to a remote database In distributed environments routers or bridges between networks might block communication between the client and the server For example when using TCP IP ensure that you can PING the remote host Diagnostic tools DB2 Connect provides diagnostic tools to troubleshoot problems You can also use the tools and diagnostic files provided with the operating system When you encounter a problem you can use the following troubleshooting information e All diagnostic data including dump files trap files error logs notification files and alert logs are found in the path specified by the diagnostic data directory path diagpath database manager configuration parameter If the value for this configuration parameter is null the diagnostic data is written to one of the following directories or folders F
238. ory You can specify the location of the log files If you want your DB2 database product to have access to DB2 documentation either on your local computer or on another computer on your network then you must install the DB2 Information Center The DB2 Information Center contains documentation for the DB2 database and DB2 related products See the Installing the DB2 Information Center using the DB2 Setup wizard UNIX topic in Installing DB2 Servers Installation requirements for DB2 Connect server products AIX Before you install DB2 Connect server products on AIX operating systems ensure that the system you choose meets the necessary operating system hardware software and communications requirements To install a DB2 Connect server product such as DB2 Connect Enterprise Edition the following requirements must be met Installation requirements Table 4 AIX installation requirements Operating System Hardware AIX Version 6 1 64 bit Common Hardware Reference 64 bit AIX kernel is required Platform CHRP architecture excluding based E AIX 6 1 Technology Level TL 6 and BQIVERS E toca tien Syste Service Pack SP 5 All processors that are capable of running e Minimum C runtime level requires the the supported AIX operating systems xlC rte 11 1 0 1 and xlC AIX rte 11 1 0 1 or later filesets AIX Version 7 1 e 64 bit AIX kernel is required e AIX 7 1 Technology Level TL 0 and Service Pack SP 3 e
239. ot been specified in the database directory entry SERVER authentication will be the default type requested of the server However negotiation will still take place between the client and server if the server does not support SERVER authentication This behavior is in contrast to the client which defaults to SERVER_ENCRYPT if an authentication type has not been specified The authentication type cataloged at the gateway is not used if DB2NODE or the SQL_CONNECT_NODE option of the Set Client API has been set at the client In these cases negotiation is still strictly between the client and the server The following authentication types are allowed with DB2 Connect CLIENT The user name and password are validated at the client DATA_ENCRYPT Provides the ability to encrypt user data during client server communications This authentication type is not supported on IBM Power Systems database server KERBEROS Enables the client to log into the server using Kerberos authentication instead of the traditional ID and password combination This authentication type requires that both the server and client be Kerberos enabled SERVER The user name and password are validated at the System z or IBM Power Systems server database SERVER_ENCRYPT As for SERVER authentication the user name and password are validated at the System z or IBM Power Systems database server but the transferred user IDs and passwords are encrypted at the client 134 DB2 C
240. our environment and gives you the outline for your upgrade plan The characteristics of your environment and the information in upgrade essentials especially the upgrade recommendations and restrictions can help you determine your strategy An upgrade plan should include the following upgrade details for each component e Upgrade prerequisites that indicate all the requirements that you need to meet before upgrading Copyright IBM Corp 1993 2012 13 e Pre upgrade tasks which describe all the preparation tasks that you need to perform before upgrading e Upgrade tasks which describe step by step the basic upgrade process for a component and how to upgrade environments with special characteristics e Post upgrade tasks which describe all the tasks that you need perform after upgrading to have your DB2 server running at the optimum level e Review the need to opt for DB2 Connect client instead of DB2 Connect server to receive equivalent or superior function You will find that pre upgrade tasks upgrading tasks and post upgrade tasks for DB2 Connect servers reference pre upgrade tasks upgrading tasks and post upgrade tasks for DB2 servers because they are exactly the same tasks Upgrade essentials for DB2 Connect If you are upgrading your clients to the latest version or release of DB2 Connect you need to consider the changes in support and resolve them before you upgrade Upgrade essentials for DB2 servers and clients also
241. out requiring DB2 Connect modules to be installed or configured DB2 Connect server products as connectivity servers A DB2 Connect Server is used to provide a single point of connectivity to numerous workstations supporting a variety of applications Figure 2 on page 8 illustrates IBM s solution for environments in which you want a DB2 client to make an indirect connection to an IBM mainframe database server through a DB2 Connect server product such as DB2 Connect Enterprise Edition Chapter 1 DB2 Connect overview 7 DB2 for VSE T i DB2 for IBM i DB2 Power for z OS System z Systems Servers DB2 Connect server Named Pipes TCP IP Z DB2 Client Figure 2 DB2 Connect Enterprise Edition If a TCP IP connection to the DB2 Connect server is lost the client will automatically attempt to reestablish the connection The client will first attempt to reestablish the connection to the original server If the connection is not reestablished the client will failover to an alternate DB2 Connect server The alternate server is specified on the server instance and its location is returned to the client during the connection If the connection to the alternate server is not reestablished the client will attempt to reestablish the connection to the original server The client will continue the attempts to reestablish the connection switching between the original server and the alternate server until the connectio
242. parameter D is not specified a disconnect will be performed only when the following SOLCODEs are returned SQL30020N SQL30021N SQL30041N SQL30061N SQL30081N For explanations of these codes refer to the Message Reference Note If DB2 Connect disconnects due to an error a rollback will be done automatically INTERRUPT_ENABLED This is the third positional parameter INTERRUPT_ENABLED only applies if the end server does not support interrupts If a server supports the DRDA interrupt flow DB2 Connect will simply pass the interrupt request on to the server If INTERRUPT_ENABLED is configured in the DCS directory at the DB2 Connect workstation and a client application issues an interrupt while connected to the IBM mainframe database server DB2 Connect will perform the interrupt by dropping the connection and rolling back the unit of work This interrupt behavior is supported on AIX and Windows The application will receive sqlcode 30081 indicating that the connection to the server has been terminated The application must then establish a new connection with the IBM Mainframe database server in order to process additional database requests On platforms other than AIX V5 2 and later and Windows DB2 Connect does not support the option of automatically disconnecting when an application using it receives an interrupt request Note This support works for TCP IP connections on any platforms The client might kill the soc
243. password are encrypted In scenario 4 a Kerberos ticket is obtained by the client from the Kerberos KDC The ticket is passed unaltered through DB2 Connect to the server where it is validated by the server Scenario 5 is the same as scenario 3 except that the user data is also encrypted and DATA_ENCRYPT does not support the IBM Power Systems database server Scenario 6 is the same as scenario 3 except that an Advanced Encryption Standard AES encryption algorithm is used Chapter 9 Tuning DB2 Connect performance considerations Performance is the way a computer system behaves given a particular workload It is affected by the available resources and how they are used and shared If you want to improve performance you must first decide what you mean by performance You can choose many different performance metrics including Response time The interval between the time that the application sends the database request and the time that the application receives a response Transaction throughput The number of units of work that can be completed per unit of time The unit of work could be simple like fetching and updating a row or complicated involving hundreds of SQL statements Data transfer rate The number of bytes of data transferred between the DB2 Connect application and the IBM mainframe database per unit of time Performance will be limited by the available hardware and software resources CPU memory and networ
244. piler cardinality estimate 0 12 15 2001 10 29 06 142790 12 15 2001 10 29 06 707053 Statement start timestamp Statement stop timestamp Host response time sec ms 1 101612 Elapsed time of last completed stmt sec ms 0 564263 Rows fetched 0 Time spent on gateway processing 0 013367 Inbound bytes received for statement 220 Outbound bytes sent for statement 130 Outbound bytes received for statement 49 Inbound bytes sent for statement 27 SQL statement text create table t12 coll int col2 char DCS application status The System Monitor provides three forms of the LIST DCS APPLICATIONS command as follows e LIST DCS APPLICATIONS e LIST DCS APPLICATIONS SHOW DETAIL e LIST DCS APPLICATIONS EXTENDED In the output that follows the format for the Host Application ID and Client Application ID can differ depending on the IBM mainframe database version and the TCP IP support level Table 18 Application ID format based on host version and TCP IP support level Scenario Application ID format Clients accessing G91A0D3A P8BC 060306212019 data servers with RDB Manager Level support less than 7 Clients accessing 9 26 13 61 65289 060306213816 data servers with RDB Manager level support 8 or greater over TCP IP v4 Clients accessing 2002 91a 519 13 209 6bff fe14 4fbb 7684 060306213741 data servers with RDB Manager level support 8 or greater over TCP IP v6 122 DB2 Connect User s Guide LIST DCS A
245. place Only the combinations shown in this table are supported by DB2 Connect The authentication setting is in the database directory entry at the DB2 Connect server Table 24 Valid Authentication Scenarios Scenario Authentication setting Validation 1 CLIENT Client 2 SERVER IBM mainframe database server 3 SERVER_ENCRYPT IBM mainframe database server 4 KERBEROS Kerberos security 5 DATA_ENCRYPT Host 6 SERVER_ENCRYPT_AES Host database server Discussion of Authentication types The following discussion applies to the connections described previously and listed in Table 24 Each scenario is described in more detail as follows 136 DB2 Connect User s Guide In scenario 1 the user name and password are validated only at the remote client For a local client the user name and password are validated only at the DB2 Connect server The user is expected to be authenticated at the location they sign on to The user ID is sent across the network but not the password Use this type of security only if all client workstations have adequate security facilities that can be trusted In scenario 2 the user name and password are validated at the IBM mainframe database server only The user ID and password is sent across the network from the remote client to the DB2 Connect server and from the DB2 Connect server to the IBM mainframe database server Scenario 3 is the same as scenario 2 except that the user ID and
246. product installation image Procedure Using FTP to access the installation image From the IBM zSeries computer running Linux 1 Enter the following command ftp yourserver com 28 DB2 Connect User s Guide where yourserver com represents the FTP server where the DB2 database product installation image resides 2 Enter your user ID and password 3 Enter the following commands bin get product_file where product_file represents the appropriate product package name Using the DB2 database product DVD over NFS to access the installation image 1 Mount the appropriate product DVD 2 Export the directory where you mounted the DVD For example if you mounted the DVD under db2dvd then export the db2dvd directory 3 On the IBM zSeries computer running Linux NFS mount this directory using the following command mount t nfs o ro nfsservername db2dvd local_directory_name where nfsservername represents the host name of the NFS server db2dvd represents the name of the directory being exported on the NFS server and local_directory_name represents the name of the local directory 4 From the IBM zSeries computer running Linux change to the directory where the DVD is mounted You can do this by entering the cd local_directory_name command where local_directory_name represents the mount point of your product DVD Kernel parameters Linux and UNIX Modifying kernel parameters for DB2 Connect HP UX For your DB2 databa
247. products and features must be set up and maintained properly if you need to detect DB2 products and features e More care is required when updating your DB2 product environment e More steps are required when cleaning up on the exporting machine and the mounting machine For detailed instructions see the Setting up DB2 for UNIX and Linux on NFS mounted file systems white paper in http www ibm com developerworks data library long dm 0609lee Mounting CDs or DVDs AIX To mount your DB2 database product CD or DVD on AIX operating systems use the System Management Interface Tool SMIT Before you begin Depending on your system configuration you might need to log on with root user authority to mount discs Procedure To mount the CD or DVD on AIX using SMIT perform the following steps 1 Insert the disc in the drive 2 Create a disc mount point by entering the mkdir p disc command where disc represents the CD or DVD mount point directory 3 Allocate a disc file system using SMIT by entering the smit storage command 4 After SMIT starts select File Systems gt Add Change Show Delete File Systems gt CDROM File Systems gt Add CDROM File System 5 In the Add a File System window a Enter a device name for your CD or DVD file system in the DEVICE Name field Device names for CD or DVD file systems must be unique If there is a duplicate device name you may need to delete a previously defined CD or DVD file syste
248. r DB2 Connect server products Linux Before you install DB2 Connect server products on Linux operating systems ensure that the system you choose meets the necessary operating system hardware software and communications requirements To install a DB2 Connect server product such as DB2 Connect Enterprise Edition the following requirements must be met Hardware requirements Your processor can be e x86 Intel Pentium Intel Xeon and AMD Athlon e x64 Intel EM64T and AMD64 e POWER any Power Systems Servers pSeries System i System p and POWER Systems that support Linux e System z formerly eServer zSeries Distribution requirements For the latest information about the supported Linux distributions point your browser to www ibm com db2 linux validate You might be required to update your kernel configuration parameters The kernel configuration parameters are set in etc sysctl conf See the Modifying kernel parameters Linux section of the DB2 Information Center Refer to your operating system manual for information about setting and activating these parameters using the sysctl command Software requirements e An X Window System software capable of rendering a graphical user interface is required if you want to use the DB2 Setup wizard to install DB2 Connect or if you want to use any DB2 graphical tools e A browser is required to view online help Chapter 3 Installing DB2 Connect server 43 Communicati
249. r system connections This function is available only when DB2 Connect is acting as server You can also use this utility to determine the source of an error You can correlate client applications with the corresponding jobs running on the IBM mainframe database server InfoSphere Federation Server and DB2 Connect InfoSphere Federation Server is a separate product offering that provides access to and integration of data across multivendor data sources while DB2 Connect enables you to leverage the large volumes of data located in existing host and midrange servers InfoSphereFederation Server helps integrate information by allowing a collection of data sources to be viewed and manipulated as if they were a single source It makes data source access completely transparent to the calling application InfoSphere Federation Server works in conjunction with DB2 Connect server products InfoSphere Federation Server provides native read and write access to the DB2 family of products Informix Oracle Sybase Teradata and Microsoft SQL Server databases InfoSphere Federation Server also provides read access to nonrelational and life sciences data sources such as Documentum IBM Lotus Extended Search table structured files and XML You can use it to formulate queries on data in a federated system DB2 Connect scenarios DB2 Connect can provide a variety of solutions to your IBM mainframe database access needs This topic outlines several sc
250. rameter strings commas 102 double commas 102 parameters directories 106 strings 108 SYSPLEX 102 performance application design 140 command line processor CLP impact 140 connection concentrator 149 connection pooling 149 DB2 Connect increasing transfer rates 155 overview 137 troubleshooting 154 network hardware 159 system resources 154 z OS 154 post upgrade tasks DB2 Connect servers 18 pre upgrade tasks DB2 Connect servers 15 predicates performance of logic 140 PREPARE statement application design 140 performance effect 140 problem determination connection 161 diagnostic tools overview 163 information available 176 post connection 162 tutorials 176 process status utility command 163 product availability and packaging 2 PROGRAM authentication type 136 ps command overview 163 Q query blocks increasing DB2 Connect data transfer rates 155 R references defining multiple database entries 107 remote units of work characteristics 98 example 98 overview 98 removing DB2 Connect root Linux 73 UNIX 73 resource access control facility RACF authentication 136 response times DB2 Connect 137 ROLLBACK statement statically bound 140 Index 187 rqrioblk configuration parameter tuning 150 run own application Data Server Client installed 127 S SAME authentication type 136 scenarios TCP IP security 136 SDKs product levels 26 security GRANT statement 136 Kerberos 135 node directory values 101 TCP IP 136
251. rams must be precompiled with the CONNECT 2 SYNCPOINT TWOPHASE options Both can use the SQL Connect statement to indicate which database they want to be used for the SQL statements that follow If there is no TP monitor to tell DB2 it is going to coordinate the transaction as indicated by DB2 receiving the xa_open calls from the TP monitor to establish a database connection then the DB2 software will be used to coordinate the transaction When using TP monitor multisite update the application must request commit or rollback by using the TP monitor s API for example CICS SYNCPOINT MTS SetAbort When using native SQL multisite update the normal SQL COMMIT and ROLLBACK must be used TP monitor multisite update can coordinate a transaction that accesses both DB2 resource managers and resource managers that are not part of DB2 such as Oracle Informix or SQLServer Native SQL multisite update is used with DB2 servers only For a multisite update transaction to work each of the databases participating in a distributed transaction must be capable of supporting a distributed unit of work DUOW Currently the following DB2 servers provided DUOW support that enabled them to participate in distributed transactions e DB2 for Linux UNIX and Windows Version 8 or later e DB2 for z OS Version 7 or later e IBM DB2 for IBM i A distributed transaction can update any mix of supported database servers For example your application can update severa
252. re release Lightweight Directory Access Protocol LDAP is the recommended solution for centralized user management services About this task The DB2 Installer program is a Java based installation tool that automates the installation and configuration of any DB2 database product If you prefer not to use this utility you have two alternatives You can install a DB2 Connect product e Using the response file method e Manually using the db2setup command You cannot manually install a DB2 database product using the operating system s native installation utility swinstall Any existing scripts containing this native installation utility that you use to interface and query with DB2 installations will need to change 38 DB2 Connect User s Guide Procedure To install a DB2 Connect server product such as DB2 Connect Enterprise Edition on HP UX using the DB2 Setup wizard 1 Change to the directory where the DVD is mounted cd db2dvd where db2dvd represents mount point of the DVD If you downloaded the DB2 Connect product image you must decompress and untar the product file a Decompress the product file gzip d product tar gz where product is the name of the database product that you downloaded b Untar the product file tar xvf product tar c Change directory cd product diskl Note If you downloaded a National Language Package untar it into the same directory This will create the subdirectories for example
253. re release Lightweight Directory Access Protocol LDAP is the recommended solution for centralized user management services About this task The DB2 Setup wizard is a Java based installation tool that automates the installation and configuration of any DB2 database products If you prefer not to use this utility you have two alternatives You can install a DB2 Connect Personal Edition product e Using the response file method e Manually using the db2setup command You cannot manually install a DB2 database product using the operating system s native installation utility rpm Any existing scripts containing this native installation utility that you use to interface and query with DB2 installations will need to change Procedure To install DB2 Connect Personal Edition on Linux using the DB2 Setup wizard 1 Change to the directory where the DVD is mounted cd db2dvd where db2dvd represents mount point of the DVD 2 If you downloaded the DB2 Connect product image you must decompress and untar the product file a Decompress the product file gzip d product tar gz 56 DB2 Connect User s Guide where product is the name of the database product that you downloaded b Untar the product file tar xvf product tar c Change directory cd product disk1l Note If you downloaded a National Language Package untar it into the same directory This will create the subdirectories for example nlpack disk2 in the same directory
254. registered in many jurisdictions worldwide Other product and service names might be trademarks of IBM or other companies A current list of IBM trademarks is available on the Web at www ibm com legal copytrade shtml Appendix A Overview of the DB2 technical information 177 178 DB2 Connect User s Guide Appendix B Notices This information was developed for products and services offered in the U S A Information about non IBM products is based on information available at the time of first publication of this document and is subject to change IBM may not offer the products services or features discussed in this document in other countries Consult your local IBM representative for information about the products and services currently available in your area Any reference to an IBM product program or service is not intended to state or imply that only that IBM product program or service may be used Any functionally equivalent product program or service that does not infringe any IBM intellectual property right may be used instead However it is the user s responsibility to evaluate and verify the operation of any non IBM product program or service IBM may have patents or pending patent applications covering subject matter described in this document The furnishing of this document does not grant you any license to these patents You can send license inquiries in writing to IBM Director of Licensing IBM Corporation North
255. reignkeys e SQLTablePrivileges e SQLColumnPrivileges e SOLProcedures e SOLProcedureColumns Certain CLI ODBC applications that use the metadata APIs listed previously might query all of the objects within the database For example an SQLTables call requests metadata for all the tables in the database On a large system such requests can result in a lot of network traffic take a considerable amount of time and consume a considerable amount of server resources Several CLI ODBC initialization keywords can be used to limit the amount of data that will be returned by the initial API calls during the information gathering stage after the database is first connected to These keywords can be set by 1 Manually editing the db2cli ini file 2 Updating the database CLI configuration using the DB2 Command Line Interface The keywords are e DBName e TableType e SchemaList e SysSchemae e GrantorList e GranteeList 160 DB2 Connect User s Guide Chapter 10 Troubleshooting Troubleshooting DB2 Connect Server The DB2 Connect environment involves multiple software hardware and communications products Troubleshooting is best approached by a process of elimination and refinement of the available data to arrive at a conclusion the location of the error After gathering the relevant information and based on your selection of the applicable topic proceed to the referenced section Gathering relevant information Troubl
256. rendering a graphical user interface for the DB2 Setup wizard to run on your machine Ensure that the X windows server is running Ensure that you have properly exported your display For example export DISPLAY 9 26 163 144 0 e If security software such as Lightweight Directory Access Protocol LDAP is used in your environment you must manually create required DB2 users before you start the DB2 Setup wizard Chapter 3 Installing DB2 Connect server 41 Note Network Information Services NIS and Network Information Services Plus NIS features are deprecated starting with DB2 Version 9 1 Fix Pack 2 Support for these features might be removed in a future release Lightweight Directory Access Protocol LDAP is the recommended solution for centralized user management services About this task The DB2 Setup wizard is a Java based installation tool that automates the installation and configuration of any DB2 database products If you prefer not to use this utility you have two alternatives You can install a DB2 Connect product e Using the response file method e Manually using the db2setup command You cannot manually install a DB2 database product using the operating system s native installation utility rpm Any existing scripts containing this native installation utility that you use to interface and query with DB2 installations will need to change Procedure To install a DB2 Connect server product such as DB2 Connect Enterprise
257. rent database aliases that can be catalogued on DB2 Connect server Other DB2 Connect parameters The AGENTPRI and MAXAGENTS are deprecated in Version 9 5 Commands to update the value for MAXAGENTS will continue to work so that existing applications are not broken but the values will be ignored The parameter name will not appear in any configuration lists In the past the total number of agents allowed to be created on a given DB2 partition was controlled through the MAXAGENTS configuration parameter You now have the ability to automate the configuration of agents By default NUM_POOLAGENTS will be set to AUTOMATIC with a value of 100 as the default Also by default MAX_COORDAGENTS will be set to AUTOMATIC with a value of 200 as the default Chapter 9 Tuning 151 To send accounting strings from your client applications to the DB2 Connect server use the API specific means for setting accounting information The API specific means perform faster than setting the DB2ACCOUNT environment variable IBM Data Server Driver for JDBC and SQLJ com ibm db2 jcc DB2BaseDataSource clientAccountingInformation property IBM Data Server Provider for NET DB2Connection ClientAccountingInformation property CLI ODBC ClientAcctStr CLI ODBC configuration keyword Embedded SQL C C and COBOL sqlesact function If you do not need a tailored SQLCODE mapping file you can improve performance by using the default SQLCODE mapping or turning off SQL
258. requires that a member of the Windows Administrators group first configure the Windows elevated privileges settings to allow a non Administrator user account to perform an installation For example on a 64 bit operating system you must manually grant full permission on HKLM Software Wow6432Node before DB2 Connect Personal Edition can be successfully installed On Windows Vista a non Administrator can perform an installation but will be prompted for administrative credentials by the DB2 Setup wizard The user right Access this computer from the network is required for the installation user account For domain accounts to verify user IDs the installation user ID must belong to the Domain Administrators group on the domain where the accounts are going to be created You can also use the built in Local System account to run the installation for all products User rights granted by the DB2 installer The DB2 installation program does not grant the Debug Programs user right The DB2 installer grants the following user rights e Act as part of the operating system e Create token object e Lock pages in memory e Log on as a service e Increase quotas e Replace a process level token Chapter 3 Installing DB2 Connect server 65 Extended security on Windows DB2 products offer extended Windows security You can install DB2 Connect Personal Edition with a user ID but unless that user ID belongs to either the DB2ADMNS or DB2USERS group
259. reroute feature Host data conversion When information is transferred between different environments such as Intel Windows IEEE Linux and UNIX operating systems System z VM VSE z OS IBM Power Systems IBM i numeric data types such as decimal integer floating point might need to be converted This conversion can affect performance The CPU cost of single byte character data conversion is generally less than that of numeric data conversion where data conversion is required The data conversion cost of DATE TIME TIMESTAMP is almost the same as that of single byte CHAR FLOATING point data conversion costs the most The application designer might want to take advantage of these facts when designing an application based on DB2 Connect If a database table has a column defined FOR BIT DATA the character data being transferred between the application and the database does not require any data conversion This can be used when you are archiving data on the IBM mainframe database server Data types for character data Character data can have either the CHAR or VARCHAR data type Which data type is more efficient depends on the typical length of data in the field e If the size of actual data varies significantly VARCHAR is more efficient because CHAR adds extra blank characters to fill the field These blank characters must be transmitted across the network like any other characters 158 DB2 Connect User s Guide e If
260. rge volumes of transactions Most web application servers now provide their own way of pooling database connections For example both Microsoft MTS COM and IBM WebSphere provide connection pooling Application pooling mechanisms implemented by these servers differ significantly from what is provided by the DB2 Connect servers Since application servers pool connections only for their own use they typically presume that user id password isolation levels and so on will be exactly the same for all connections Even more important application servers only pool connections initiated by the same process This means that connections from other machines users or processes are not pooled While these application server pooling techniques are effective for reusing connections established by the same instance of an application they are absolutely ineffective for pooling connections from multiple users servers and so on Connection pooling provided by the DB2 Connect servers is completely application machine and user independent Connections from multiple clients 144 DB2 Connect User s Guide application servers all with different user IDs can all reuse each other s connections resulting in a much better utilization of the pooled resources Which type of connection pooling is the right one to use Both Generally using both DB2 Connect connection pooling and Application Server connection pooling is a good strategy since they don t interfere
261. ries and DB2 Connect Unlimited Edition for i5 OS You can use the DB2 database server fix pack to upgrade a Data Server Client A single server image can also be used to install any of the DB2 database server products at a particular fix pack level with a DB2 try and buy license by default The single server fix pack image contains DB2 try and buy licenses for all DB2 server products When you select a new DB2 server product to install or a previously installed DB2 server product to update the try and buy licenses are installed The try and buy licenses do not affect any valid licenses already installed in the same DB2 installation path Regarding DB2 Connect server 70 DB2 Connect User s Guide products if you run the db2licm 1 command to query valid licenses the try and buy license for DB2 Connect server product might display as an invalid license However if you do not need to use the DB2 Connect functionality you can ignore the report To remove the try and buy license for DB2 Connect server use the db21 icm command e A fix pack for each of the other DB2 database products Use this fix pack only if you only have non server database products or add on products installed For example IBM Data Server Runtime Client Do not use this type of fix pack if the installed DB2 database products are only DB2 database server products or a Data Server Client Instead use the single server image fix pack For Windows platforms if you
262. rivileges to the user while they are using the connection The role privileges that are granted if any are specified in the trusted context that caused the connection to be trusted Implicit trusted connections can be created by any application that connects using DB2 Connect Implicit trusted connections are made and used in the same way that regular connections are made and used This means that no code changes are necessary for an existing application to take advantage of implicit trusted connections as long as the application connects through DB2 Connect An explicit trusted connection grants temporary role privileges to the user the same way that an implicit trusted connection does In addition an explicit trusted connection lets you change the authorization ID used when performing actions across that connection Changing the authorization ID on an explicit trusted connection is referred to as switching users The authorization IDs to which you can switch and whether a given authorization ID requires a password when switching to it are defined as part of the trusted context that allowed the trusted connection to be created User switching can significantly reduce the processing usage of sharing a connection among several users especially for user names that do not require a password because in that case the database server does not authenticate the authorization ID When using the feature however you must be very certain that Copyrigh
263. rst output token in the SQLCA The SQLCODE is mapped from 633 to 206 The first input token received from the IBM mainframe database server is CHAR It is converted to INTEGER and is used as the second token in the output SQLCA The first token in the output SQLCA is null as indicated by a comma The SQLCODE is mapped from 30021 to 30021 The first and second input tokens received from the IBM mainframe database server are CHAR and they are used as the first and second tokens in the output SQLCA All SQLCODEs in SQLCAs with SQLSTATEs in the 00 class will be mapped to SQLCODE 000 All undefined SQLCODEs are mapped to 969 This option should be used only if all mappable codes are listed including all those that are identical and require no mapping The s option indicates that the token list to be returned in the SQLERRMC field of the SQLCA includes the original SQLCODE followed by the product the error occurred in followed by the original token list If the U entry is not included all unlisted codes are passed without any mapping All undefined positive SQLCODEs are mapped to 965 This option should be used only if all mappable codes are listed including all those that are identical and require no mapping The s option indicates that the token list to be returned in the SQLERRMC field of the SQLCA includes the original SQLCODE followed by the product the warning occurred in followed by the original token list If the P entry i
264. s about to send to the server database in addition to data received from the server database For DB2 Connect to perform BiDi layout transformation on outgoing data to a server database the BiDi CCSID of the server database will have to be overridden This is accomplished through the use of the BIDI parameter in the PARMS field of the DCS database directory entry for the server database The use of this feature is best illustrated with an example Consider a Hebrew IBM data server client running CCSID 62213 BiDi string type 5 and you would like to access a DB2 host database running CCSID 424 BiDi string type 4 However you know that the data contained in the DB2 host database is instead based on CCSID 62245 Bibi string type 10 There are two problems in this situation The first is that the DB2 host database does not know the difference between the BiDi string types with CCSIDs 424 and 62245 The second problem is that the DB2 host database does not recognize the IBM data server client CCSID of 62213 It only supports CCSID 62209 Bibi string type 10 which is based on the same code page as CCSID 62213 You will need to make sure that data sent to the DB2 host database is in BiDi string type 6 format to begin with and also let DB2 Connect know that it has to perform BiDi layout transformation on data it receives from the DB2 host database You will use the following cataloging for the DB2 host database catalog dcs database nydb1 as TE
265. s controls the number of inbound connections that can be active at any time max_connections will take a numeric range from max_coordagents up to 64 000 The default number of logical agents is equal to max_coordagents Both max_connections and max_coordagents can be set to AUTOMATIC If max_connections is set to AUTOMATIC the number of connections can be increased beyond the base configured value If both max_connections and max_coordagents are set to AUTOMATIC max_connections can be increased beyond the base value and max_coordagents is automatically increased to maintain the concentration ratio between connections and the coordinator agents Chapter 9 Tuning 147 Several existing configuration parameters are used to configure agents These 8 parameters are as follows max_coordagents Maximum number of active coordinator agents num_poolagents Agents pool size The agent pool includes inactive agents and idle agents For improved performance num_poolagents should be configured to equal the average number of clients num_initagents Initial number of worker agents in the pool These will be idle agents XA transaction support The architecture of the connection concentrator allows DB2 Connect to provide tightly coupled XA transaction support to DB2 for z OS and IBM DB2 for IBM i The concentrator will associate a worker agent with a particular XA transaction single XID as it would for any other transaction However if the XA tran
266. s not included all unlisted positive codes are passed without any mapping Chapter 5 Administering 117 118 DB2 Connect User s Guide Chapter 6 Monitoring DB2 Connect server Monitoring connections for remote clients You can use the database system monitor with a DB2 Connect server product such as DB2 Connect Enterprise Edition to monitor the remote client connections To monitor clients that are local to the DB2 Connect server that are running on the server itself you will need to set the following variable db2set DB2CONNECT_IN_APP_PROCESS NO For example when an error occurs at the IBM mainframe system the system administrator can determine if the problem was on the DB2 Connect workstation The database system monitor correlates e The DRDA correlation token CRRTKN for unprotected conversations e The unit of work id UOWID for two phase connections protected by the DRDA 3 sync point manager as used over TCP IP connections e The DB2 Connect connection identifier the Application ID This information shows which DB2 Connect connection caused the problem which allows the system administrator to force the individual client application from the system without affecting the other clients using the DB2 Connect connection Listing the Status of Monitor Switches To list the status of monitor switches use the db2 get monitor switches command Monitoring performance using the Windows Performance Monitor
267. s required to permit a DB2 Connect client to make a connection to the DB2 for z OS database server For more detailed examples refer to the DB2 for z OS installation documentation http publib boulder ibm com infocenter imzic or refer to the DDF installation steps in the DB2 for z OS installation manual Preparing DB2 for VSE amp VM for connections from DB2 Connect About this task For information about how to set up DB2 Server for VM and VSE as an application server refer to the DRDA Considerations section of the DB2 Server for VSE amp VM SQL Reference SC09 2989 Sysplex support Applications can leverage Sysplex capabilities by either going through a middle tier DB2 Connect server or using client Sysplex support when available The client sysplex support is the preferred option as it provides better availability improved server utilization since it eliminates a point of failure transaction level balancing and seamless automatic client reroute whereas DB2 Connect server does not DB2 Connect server Sysplex support Sysplex permits the DB2 Connect server to seamlessly balance connections across different members of a data sharing group A Sysplex is a collection of System z servers that cooperate using hardware and software to process work 80 DB2 Connect User s Guide The Sysplex coordinates the cooperation by increasing the number of processors working together which increases the amount of work that can be
268. saction is ended by xa_end branch boundary the worker agent will not release itself into the general pool Instead the worker remains associated with that particular XA transaction When another application joins the same XA transaction the worker agent will be attached to that application Any transaction boundary call will return the agent to the pool For instance xa_prepare with read only xa_rollback xa_recover xa_forget xa_commit or any XA error that causes rollback will return the agent to the normal pool Xa_end itself only ends the transaction branch and this is not sufficient to end its association with the XID Examples of XA transaction support 1 Consider an environment where 4 000 or more concurrent connections are needed A web server that uses CGI applications or an office system with many desktop users can both exceed this requirement In these cases efficiency will usually require that DB2 Connect operate as a stand alone gateway that is the database and the DB2 Connect system are on separate machines The DB2 Connect server system might not be able to maintain 4 000 simultaneous open connections to the database machine In most cases the number of transactions occurring at any given moment will be considerably less than the number of concurrent connections The system administrator could then maximize the efficiency of the system by setting the database configuration parameters as follows MAX_CO
269. se product to perform properly on HP UX you might need to update your system s kernel configuration parameters If you update your kernel configuration parameter values you must restart your computer Before you begin You must have root user authority to modify kernel parameters Procedure To modify kernel parameters 1 w Enter the sam command to start the System Administration Manager SAM program Double click the Kernel Configuration icon Double click the Configurable Parameters icon Double click the parameter that you want to change and type the new value in the Formula Value field Click OK Repeat these steps for all of the kernel configuration parameters that you want to change When you are finished setting all of the kernel configuration parameters select Action gt Process New Kernel from the action menu bar Chapter 3 Installing DB2 Connect server 29 Results The HP UX operating system automatically restarts after you change the values for the kernel configuration parameters Recommended kernel configuration parameters for DB2 Connect HP UX For HP UX systems running a DB2 64 bit database system run the db2osconf command to suggest appropriate kernel configuration parameter values for your system The db2osconf utility can only be run from DB2DIR bin where DB2DIR is the directory where you installed your DB2 database product Modifying kernel parameters for DB2 Connect Linux Befor
270. se server you are connecting to is configured to allow it you can create an explicit trusted connection when connecting through CLI Before you begin This procedure assumes that you are not using an XA transaction manager If you are using an XA transaction manager you only need to make sure that the transaction manager is configured to set the configuration value TCTX to TRUE when it calls xa_open If that is done then any connection that can be an explicit trusted connection will be To verify that a connection is an explicit trusted connection see step 3 e The database that you are connecting to must support trusted contexts e A trusted context must be defined that will recognize the client as being trustable e You must know the system authorization ID that is specified in the trusted context The system authorization ID of a trusted connection is the authorization ID you provide to the server as a user name when creating the connection For your connection to be trusted by a particular trusted context the system authorization ID must be the one specified in that trusted context Ask your security administrator for a valid system authorization ID and the password for that ID About this task The examples in these instructions use the C language and assume that conn is a pointer to a valid but unconnected connection handle The variable rc is assumed to have a data type of SQLRETURN 130 DB2 Connect User s Guide Procedure 1
271. st manually create required DB2 users before you start the DB2 Setup wizard Refer to Centralized user management considerations in Installing DB2 Servers before you begin Note Network Information Services NIS and Network Information Services Plus NIS features are deprecated starting with DB2 Version 9 1 Fix Pack 2 Support for these features might be removed in a future release Lightweight Directory Access Protocol LDAP is the recommended solution for centralized user management services About this task The DB2 Setup wizard is a Java based installation tool that automates the installation and configuration of any DB2 database products If you prefer not to use this wizard you have two alternatives You can install a DB2 Connect Personal Edition product Using the response file method Manually using the db2setup command You cannot manually install a DB2 database product using the operating system s native installation utility pkgadd Any existing scripts containing this native installation utility that you use to interface and query with DB2 installations will need to change Procedure To install DB2 Connect Personal Edition on Solaris x64 using the DB2 Setup wizard ils Change to the directory where the DVD is mounted cd db2dvd where db2dvd represents the mount point of the DVD If you downloaded the DB2 Connect product image you must extract and untar the product file a Decompress the product file
272. stablished connection infrastructure for subsequent connections When a DB2 Connect instance is started a pool of coordinating agents is created When a connection request comes in an agent is assigned to this request The agent will connect to the DB2 server and a thread will be created in DB2 When the application issues a disconnect request the agent will not pass this request along to the DB2 server Instead the agent is put back in to the pool The agent in the pool still owns its connection to the DB2 server and the corresponding DB2 thread When another application issues a connect request this agent is assigned to this new application To insure secure operation user identity information is passed along to the DB2 thread which in turn performs user authentication DB2 Connect s connection pooling provides a significant performance improvement in such environments DB2 Connect maintains open connections to the database in an available pool When a client requests a connection it can be provided from this pool of ready connections Connection pooling significantly reduces the processing usage typically spent on opening and closing these connections Connection pooling is transparent to applications connecting to the host through DB2 Connect When an application requests disconnection from the host DB2 Connect drops the inbound connection with the application but keeps the outbound connection to the host in a pool When a new application r
273. stering 113 Note SQLCODE mapping can also be turned off using SQLCODEMAP CLI ODBC configuration keyword or SQL_ATTR_SQLCODEMAP connection attribute when used with DB2 CLI application programming interface API Tailoring the SQLCODE mapping By default DB2 Connect maps SQLCODEs and tokens from each IBM mainframe database server to the appropriate DB2 SQLCODEs You can tailor the SQLCODE mapping if you want to override the default SQLCODE mapping or if you are using a IBM mainframe database server that does not have SQLCODE mapping not an IBM database server About this task The following files are copies of the default SQLCODE mapping e dcsldsn map maps DB2 for z OS SQLCODEs e dcslari map maps DB2 Server for VM and VSE SQLCODEs e dcslqsq map maps IBM DB2 for IBM i SQLCODEs No mapping is required for DB2 on Linux or UNIX operating systems Each mapping file is an ASCII file which is created and edited using an ASCII editor At initial installation the file is stored in the map directory in the installation path Procedure If you want to create an SQLCODE mapping for a database server that is not an IBM database server or override the default SQLCODE mapping 1 Copy one of the dcsldsn map dcslari map or dcslqsq map files and use it as the basis for your new SQLCODE mapping file By copying the file rather than editing it directly you ensure that you can always refer to the original SQLCODE mapping if necessary 2
274. t IBM Corp 1993 2012 129 your application does not allow switching to an authorization ID without validating and authenticating that authorization ID Otherwise you are creating a security hole in your system Explicit trusted connections can be created and the user can be switched when connecting through DB2 Connect using CLI or JDBC including XA established connections Creating an explicit trusted connection and switching users requires setting special connection attributes This means that existing applications will need to be modified in order to take advantage of explicit trusted connections Other than the differences just mentioned you can use a trusted connection whether implicit or explicit the same way you would used a regular connection You must be certain however to explicitly disconnect an explicit trusted connection when you are done with it even if it is in a broken or disconnected state Otherwise resources used by the connection might not be released This is not a problem with implicit trusted connections Note 1 Explicit trusted connections should not use CLIENT authentication This does not apply to implicit trusted connections 2 Applications using explicit trusted connections should be run on secure machines which are password protected and accessible only to authorized personnel This does not apply to implicit trusted connections Creating and terminating a trusted connection through CLI If the databa
275. t configure the Windows elevated privileges settings to allow a non Administrator user account to perform an installation For example on a 64 bit operating system you must manually grant full permission on HKLM Software Wow6432Node before a 32 bit DB2 Connect Personal Edition product can be successfully installed Note If a non Administrator user account is going to do the product installation then the VS2010 runtime library must be installed before attempting to install a DB2 product The VS2010 runtime library is needed on the operating system before the DB2 product can be installed The VS2010 runtime library is available from the Microsoft runtime library download website There are two choices choose vcredist_x86 exe for 32 bit systems or vcredist_x64 exe for 64 bit systems System shortcuts must be changed to user shortcuts for the non Administrator install Moreover since services are required to install any of the DB2 Connect products but cannot be created without administrative authority services that would be automatically started are run as processes when a non administrator installs The following scenarios are installation situations that you might encounter in an environment where both administrator and non administrator installations exist e A non Administrator has installed DB2 Connect and then an Administrator attempts to install DB2 Connect on the same system The Administrator will get a message that the product is alre
276. t use the term database to describe a slightly different concept The DB2 Connect term database can also refer to System z DB2 for z OS A DB2 for z OS subsystem identified by its LOCATION NAME Use the z OS display ddf command to get the DB2 server location name domain name IP address and port A DB2 for z OS location is the unique name of a database server An application uses the location name to access a DB2 for z OS subsystem or a DB2 for z OS data sharing group A data sharing group enables applications on different DB2 subsystems to read from and write to the same data concurrently The application uses a DB2 data sharing group network address to access a DB2 data sharing location The accessed DB2 subsystem is transparent to the application Since DB2 for z OS supports multiple databases at the same DB2 location the location name is analogous to a Linux UNIX and Windows database alias name A database alias can be used to override the location or location alias name when accessing a location A location alias is another name for a location It is used to control which subsystems in a data sharing group are accessed by an application LOCATION NAME is also defined in the Boot Strap Data Set BSDS as well as the DSNLO04I message LOCATION location which is written when the Distributed Data Facility DDF is started LOCATION NAME supports up to 8 alias location names allowing applications the ability to use different dbalias nam
277. t using the DB2 Setup wizard or by using the National Language Package About this task To check which public locales are available in your system run the locale a command Procedure To change the DB2 interface language Set the LANG environment variable to the locale you want e For bourne sh korn ksh and bash shells LANG locale export LANG e For C shell setenv LANG locale For example to interface with the DB2 database product in French you must have the French language support installed and you must set the LANG environment variable to a French locale for example fr_FR Conversion of character data When character data is transferred between machines it must be converted to a form that the receiving machine can use For example when data is transferred between a DB2 Connect server and a host or System i database server it is usually converted from a server code page to a host CCSID and vice versa If the two machines use different code pages or CCSIDs code points are mapped from one code page or CCSID to the other This conversion is always performed at the receiver Character data sent to a database consists of SQL statements and input data Character data sent from a database consists of output data Output data that is interpreted as bit data is not converted For example data from a column declared with the FOR BIT DATA clause Otherwise all input and output character data is converted if the two mach
278. tabase product on Linux or UNIX has a different installation path and can therefore coexist on the same computer Note This task applies to DB2 database products that were installed with root user authority A separate topic explains how to uninstall DB2 database products that were installed as a non root user Chapter 3 Installing DB2 Connect server 73 Procedure To remove your DB2 database product 1 Optional Drop all databases You can drop databases using the DROP DATABASE command Database files remain intact on your file systems when you drop an instance without dropping databases first 2 Stop the DB2 Administration Server Refer to the Installing DB2 Servers manual 3 Remove the DB2 Administration Server or run the dasupdt command to update the DB2 Administration Server to another installation path To remove the DB2 Administration Server refer to the Installing DB2 Servers manual 4 Stop all DB2 instances Refer to the Installing DB2 Servers manual 5 Remove the DB2 instances or run the db2iupdt command to update the instances to another installation path To remove the DB2 instances refer to the Installing DB2 Servers manual 6 Remove the DB2 database products Refer to the Installing DB2 Servers manual 74 DB2 Connect User s Guide Chapter 4 Configuring Preparing IBM DB2 for IBM i for connections from DB2 Connect DB2 Connect gives remote system applications access to data on your IBM DB2 for IB
279. tabase utilities The db2cli 1st file contains the list of bind bnd files required to create packages for the CLI and the DB2 ODBC driver 2 Binding might take a few minutes to complete 3 If you have BINDADD authority the first time you use the CLI or ODBC driver the CLI packages will be bound automatically If the applications that you are using require binding to the database you can use the BIND command to perform the bind action Considerations for System z SYSPLEX exploitation DB2 Connect provides load balancing and fault tolerance when routing connections to DB2 Sysplex When connected to a DB2 for z OS database server running in a DB2 pureScale environment DB2 Connect will spread the workload amongst the different DB2 subsystems comprising the data sharing group based on the system load and health information provided by the Workload Manager WLM It uses the Distributor to route connections Use the group IP address to connect to a group location DB2 Connect receives a prioritized list of DB2 members from the WLM Each Sysplex returns weighted priority information for each connection address that has capacity to run the work This list is then used by DB2 Connect to handle the incoming CONNECT requests by distributing them among the DB2 members with the best capacity to run work For load balancing the list of Sysplex weighted priority information is obtained during each connection This list is also used when determining w
280. tallation e You can install DB2 Connect using either root or non root user authority e Ensure that your system meets Disk and memory requirements Hardware distribution and software requirements Refer to Installation requirements for DB2 Connect server products AIX on page 35 e The DB2 database product DVD must be mounted on your system e The DB2 Connect product image must be available If you are installing a non English version of a DB2 Connect product you must also have the appropriate National Language Packages e To locate DB2 database products already installed on your system use the db21s command Refer to the Listing DB2 products installed on your system Linux and UNIX topic in Installing DB2 Servers e The DB2 Setup wizard is a graphical installer You must have X windows software capable of rendering a graphical user interface for the DB2 Setup wizard to run on your machine Ensure that the X windows server is running Ensure that you have properly exported your display For example export DISPLAY 9 26 163 144 0 e If security software such as Lightweight Directory Access Protocol LDAP is used in your environment you must manually create required DB2 users before you start the DB2 Setup wizard Note Network Information Services NIS and Network Information Services Plus NIS features are deprecated starting with DB2 Version 9 1 Fix Pack 2 Support for these features might be removed in a futu
281. tation 3 If required modify the DB2 Connect sync point manager SPM default configuration parameters IBM host and System i Version 5 Release 3 and earlier database servers do not yet support the XA interface System i Version 5 Release 4 and following has full XA support 112 DB2 Connect User s Guide The SPM is a component of DB2 Connect which maps the XA two phase commit protocol into the two phase commit protocol used by IBM mainframe database servers By default the DB2 instance has predefined values for the SPM configuration parameters The most significant parameter is the database manager configuration parameter spm_name It defaults to a variant of the first seven characters of the TCP IP hostname 4 On DB2 for Linux UNIX and Windows set the DB2COMM registry variable to use TCPIP and set the svcename database manager configuration parameter to a TCP IP port number or service name DB2 Connect support for loosely coupled transactions The support within DB2 Connect for loosely coupled transactions is intended for users who implement XA distributed applications that access IBM DB2 for IBM i Version 5 Release 4 or later and DB2 for z OS Version 7 or later This support allows different branches of the same global transaction to share lock space on DB2 for z OS Support for loosely coupled transactions is intended for NET and COM applications This feature reduces the window where one branch of a distributed transaction
282. tem is supported by db2setup then that language will be used when displaying the DB2 Setup wizard If your system uses the same code pages but different locale names than those supported by the DB2 interface you can still see the translated db2setup by setting your LANG environment variable to the appropriate value by entering the following command bourne sh korn ksh and bash shells LANG locale export LANG C shell setenv LANG locale where locale is a locale supported by the DB2 interface Language identifiers for running the DB2 Setup wizard in another language If you want to run the DB2 Setup wizard in a language different from the default language on your computer you can start the DB2 Setup wizard manually specifying a language identifier The language must be available on the platform where you are running the installation Copyright IBM Corp 1993 2012 21 On Windows operating systems you can run setup exe with the i parameter to specify the two letter language code of the language the installation is to use On Linux and UNIX operating systems it is recommended that you set the LANG environment variable to display the DB2 Setup wizard in your national language Table 2 Language identifiers Language Language identifier Arabic available on Windows platforms ar only Brazilian Portuguese br Bulgarian bg Chinese Simplifi
283. that user ID won t be able to run any DB2 commands The DB2 installer creates these two new groups You can either specify a new name during a custom installation or accept the default names To enable this security feature select the Enable operating system security check box on the Enable operating system security for DB2 objects panel during the DB2 installation Accept the default values for the DB2 Administrators Group field and the DB2 Users Group field The default group names are DB2ADMNS and DB2USERS If there is a conflict with existing group names you will be prompted to change the group names If required you can specify your own values Extending the Active Directory Schema for LDAP directory services Windows If you plan to use the Lightweight Directory Access Protocol LDAP directory server feature with Windows Server 2003 you have to extend the Active Directory schema to contain DB2 object classes and attribute definitions using the db2schex command About this task Extending the directory schema before installing DB2 database products and creating databases provide the following benefits e The default DB2 instance created during the installation is cataloged as a DB2 node in Active Directory provided that the installation user ID had sufficient privileges to write to Active Directory e Any databases created after installation is automatically cataloged into Active Directory Procedure To extend the direc
284. the license directory under the directory where the driver was installed If you are using DB2 Connect Unlimited Edition for z OS then use a server based license key This one step will prevent the need for client based license keys For details see the topic about activating the license key for DB2 Connect Unlimited Edition for System z On Windows operating systems you must belong to the local Administrators or Power Users group to use the db2licm command with the a command parameter Procedure e On Windows operating systems register a DB2 license key by entering the following command db2instance_path bin db2licm a filename where db2instance_path is where the DB2 instance was created and filename is the full path name and file name for the license file that corresponds to the product or feature you have purchased e On Linux or UNIX operating systems register a DB2 license key by entering the following command INSTHOME sqllib adm db2licm a filename where INSTHOME represents the home directory of the instance owner and filename is the full path name and file name for the license file that corresponds to the product or feature you have purchased The db21icm command can also be found in the path where the DB2 database product is installed For example opt IBM db2 V10 1 adm on AIX HP UX or Solaris operating systems or opt ibm db2 V10 1 adm on Linux operating systems if you use the default installation directory Setting the
285. ther on your local computer or on another computer on your network then you must install the DB2 Information Center The DB2 Information Center contains documentation for the DB2 database and DB2 related products Installation requirements for DB2 Connect server products Windows Before you install DB2 Connect server products on Windows operating systems ensure that the system you choose meets the necessary operating system hardware software and communications requirements To install a DB2 Connect server product such as DB2 Connect Enterprise Edition the following requirements must be met Hardware requirements All Intel and AMD processors capable of running the supported Windows operating systems 32 bit and 64 bit Operating system requirements One of e Windows XP Professional Edition 32 bit and 64 bit with Service Pack 3 or later e Windows 2003 with Service Pack 2 or later Standard Edition 32 bit and 64 bit Enterprise Edition 32 bit and 64 bit Datacenter Edition 32 bit and 64 bit e Windows Vista Business Edition 32 bit and 64 bit Enterprise Edition 32 bit and 64 bit Ultimate Edition 32 bit and 64 bit e Windows 7 Service Pack 1 Professional Edition 32 bit and x64 Enterprise Edition 32 bit and x64 e Windows Server 2008 Service Pack 2 or later Chapter 3 Installing DB2 Connect server 49 Standard Edition 32 bit and 64 bit Enterprise Edition 32 bit and 64 bit
286. timum size is Setting RUSIZE and 4096 RQRIOBLK to same size might give the best performance Buffers Pacing VPACING PACING Use adaptive pacing and Mode Profiles where applicable should be set to 63 Adapter Settings Transmit Receive Recommended value Default is usually 32 TCP Settings Segment Sizes 1500 on Ethernet 4400 on token ring and FDDI ESCON adapters used for TCP IP should always be set to 4096 Chapter 9 Tuning 153 Table 28 Network performance tuning methods continued What to Look For Example Setting Notes TCP Settings Send Receive Space Should be 64K for Default is only 8192 Sizes both for Windows Can be set in the Windows registry System resources contention Performance could be degraded if many tasks in the system are contending for system resources Consider the following questions e Is the CPU saturated Consider upgrading the system reducing the system workload and tuning the system to reduce processing usage e Is the memory over committed Consider upgrading memory reducing system workload and tuning the system to reduce the memory working set e Is the communication adapter communication controller too busy Consider upgrading the network or pairing up token ring cards e Is one of the subsystems too busy and is this subsystem on the data path e Are any unnecessary processes or tasks running on the system The general rule is not
287. tion path Results National Language Packs can also be installed by running the db2setup command from the directory where the National Language Pack resides after a DB2 Connect product has been installed The installation logs db2setup 1og and db2setup err will be located by default in the tmp directory You can specify the location of the log files Chapter 3 Installing DB2 Connect server 39 If you want your DB2 database product to have access to DB2 documentation either on your local computer or on another computer on your network then you must install the DB2 Information Center The DB2 Information Center contains documentation for the DB2 database and DB2 related products See the Installing the DB2 Information Center using the DB2 Setup wizard UNIX topic in Installing DB2 Servers Installation requirements for DB2 Connect server products HP UX Before you install DB2 Connect server products on HP UX operating systems ensure that the system you choose meets the necessary operating system hardware software and communications requirements To install a DB2 Connect server product such as DB2 Connect Enterprise Edition on HP UX the following requirements must be met Note A 64 bit HP UX operating system is required to support DB2 Connect Installation requirements Table 5 HP UX installation requirements Operating System Hardware HP UX 11i v3 11 31 with Itanium based HP Integrity Series Systems
288. tion with DB2 Connect license applied Note A distributed transaction can update any mix of supported database servers For example your application can update several tables in a DB2 database on Windows a DB2 for z OS database and a IBM DB2 for IBM i database all within a single transaction Configuring DB2 Connect server with an XA compliant transaction manager This topic describes the configuration steps necessary to use IBM Power Systems and System z database servers within your TP monitor These steps are not required if you are using the IBM data server package through DB2 Connect client For details see the topic about IBM data server client types Before you begin You must have an operational TP monitor and have DB2 Connect installed as well as have configured and tested a connection to the IBM mainframe database server Procedure To configure DB2 Connect to use IBM Power Systems and System z database servers within your TP monitor perform the following steps 1 Configure the TP monitor so that it can access the DB2 XA Switch The DB2 XA Switch provides the TP monitor with the addresses of DB2 Connect s XA APIs Every TP monitor has a different way to do this 2 Configure the TP monitor with the XA_OPEN string of DB2 product Each TP monitor has its own way to do this For information about how to configure XA OPEN string of the DB2 product for use by the TP monitor refer to your TP monitor s documen
289. titioned database environment before installing the fix pack you must stop the database manager on all database partition servers You must install the fix pack on the instance owning database partition server and all other database partition servers All computers participating in the instance must be updated to the same fix pack level e On Linux or UNIX operating systems If you have DB2 database products on a Network File System NFS you must ensure the following applications are stopped completely before installing the fix pack all instances the DB2 administration server DAS interprocess communications IPC and applications on other machines using the same NFS mounted installation If the system commands fuser or Isof are not available the instal1FixPack command cannot detect loaded DB2 database files You must ensure no DB2 files are loaded and provide an override option to install the fix pack On UNIX the fuser command is required to check for loaded files On Linux either the fuser command or 1sof command is required Chapter 3 Installing DB2 Connect server 71 For details on the override option see the instal1FixPack command e On client applications after a fix pack has been applied to perform autobind of applications the user must have bind authority e Installation of a DB2 fix pack will not service IBM Data Studio Administration Console or IBM Data Studio Procedure To install a fix pack 1 Ch
290. to configure or start services unless they are used regularly since they will waste system resources e Do a few processes or tasks use most of the resource Can they be stopped Can their priorities be reduced Can they be refined so that they don t use as much resource DB2 Connect performance troubleshooting If DB2 Connect users are experiencing long response times during large queries from IBM mainframe servers there are some configuration settings that can help you troubleshoot the performance problem 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 IBM mainframe server 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 Ensure the maximum RU size defined in the IBMRDB mode definition is set to a suitable value It is recommended that the size is not less than 4K for connections using Token ring hardware For connections using Ethernet hardware note the maximum Ethernet frame size of 1536 bytes which might be a limiting factor Tuning DB2 for z OS You can optimize inactive thread processing in z OS In V5 you are allowed up to 25 000 concurrently connected clients In all cases the maximum number that can be concurrently
291. tor for DB2 for z OS Computer Associates International Inc Chapter 9 Tuning 139 Table 26 Performance tools for database activity continued System Tool Description Windows Microsoft Performance Monitor Table 27 Performance tools for network activity System Tool Description AIX netpmon Reports low level network statistics including TCP IP statistics such as the number of packet or frames received per second Network controller such as NetView Performance Reports utilization of 3745 Monitor communication control and VTIAM Linux and UNIX netstat Handles TCP IP traffic Application design When you create an application you can improve performance in several ways For example consider using compound SQL and stored procedures grouping related database requests into one database request refining the predicate logic implementing data blocking and tuning your dynamic SQL This section is also relevant for applications using embedded SQL Compound SQL and stored procedures For applications that send and receive many commands and replies network processing usage can be significant Compound SQL and stored procedures are two ways to reduce this processing usage If an application sends several SQL statements without intervening programming logic you can use compound SQL If you require programming logic within the group of SQL statements you can
292. tors The most common TP monitors on the market today are e IBM WebSphere Application Server e IBM WebSphere MQ IBM TxSeries CICS e BEA Tuxedo e BEA WebLogic e Microsoft Transaction Server MTS Remote IBM Power Systems System z and LAN database servers can be used within transactions coordinated by these TP monitors X Open Distributed Transaction Processing DTP model An application executing business logic might be required to update multiple resources within a single transaction For example a bank application which implements a transfer of money from one account to another could require debiting one database the from account and depositing to another database the to account It is also possible that different vendors provide these two databases For example one database is a DB2 for z OS and the other is an Oracle database Rather than have every TP monitor implement each database vendor s proprietary transaction interface a common transaction interface between a TP monitor and any resource 10 DB2 Connect User s Guide accessed by an application has been defined This interface is known as the XA Interface A TP monitor that uses the XA Interface is referred to as an XA compliant Transaction Manager TM An updatable resource that implements the XA interface is referred to as an XA compliant Resource Manager RM The previously listed TP monitors are all XA compliant TMs Remote host IBM Power Systems
293. tory schema 1 Log onto any machine that is part of the Windows domain with a Windows user account that has Schema Administration authority 2 Run the db2schex command from the installation DVD You can run this command without logging off and logging on again as follows runas user MyDomain Administrator x db2 Windows utilities db2schex exe where x represents the DVD drive letter What to do next When db2schex completes you can proceed with the installation of your DB2 database product or if you have already installed DB2 database products or created databases you have to manually register the node and catalog the databases For more information see the Enabling LDAP support after DB2 installation is complete topic 66 DB2 Connect User s Guide Non Administrator installation of DB2 Connect Windows There are some additional considerations when you install DB2 Connect on Windows operating systems using a non Administrator user account For a non Administrator s installation the account you are logged on as must belong to Power Users group Some information about DB2 Connect that must appear in the registry must be entered in the HKEY_CURRENT_USER folder in the registry Although many items will be stored under the HKEY_LOCAL_ MACHINE folder in the registry for non Administrator installations of DB2 Connect the environment settings must be changed in HKEY_CURRENT_USER A member of the Windows Administrators group mus
294. transformation based on a valid date mask will only be performed if ALL of the following conditions are true 1 There is no SQL error 2 The output is a date value in ISO like ISO and JIS format 3 The output data area is at least 10 bytes long This is the minimum size of an output data area in order for a data value to be stored there even if NO date format transformation is to be performed This requirement applies even if the date format mask ends up being shorter than 10 bytes 4 There is a valid date format mask specified in the DCS directory entry and this mask fits in the output data area mnunbIDI lt ccsid gt This parameter the ninth positional parameter is used to specify the Bidirectional BiDi CCSID to be used to override the default server database BiDi CCSID For example 5900000 2BIDI xyz where xyz represents the CCSID override Directory customization worksheet The directory customization worksheet shows the information that you need to collect You might find it convenient to make a copy of the worksheet and enter your system values Node Directory Parameters Table 14 Node Directory Parameters Parameter Example Your value Node name DB2NODE Remote hostname TCP IP node ZOSHOST Server TCP IP service name or port number db2instlc or 446 106 DB2 Connect User s Guide Note 1 The default TCP IP port number for DRDA is 446 2 Unless you know that the IB
295. ts Brio and Impromptu and Crystal Reports to provide real time information e Database products such as Lotus Approach and Microsoft Access e Development tools such as PowerSoft PowerBuilder Microsoft Visual Basic and Borland Delphi to create client server solutions A DB2 Connect server product such as DB2 Connect Enterprise Edition is most appropriate for the following environments e Federation e Transaction monitors such as BEA Tuxedo and BEA Weblogic See Figure 5 on page 93 DB2 Connect provides transparent access to IBM mainframe data through a standard architecture for managing distributed data This standard is known as Distributed Relational Database Architecture DRDA DRDA allows your applications to establish a fast connection to IBM mainframe databases without expensive IBM mainframe components or proprietary gateways Although DB2 Connect is often installed on an intermediate server machine it is recommended to connect an IBM data server client to an IBM mainframe database directly by installing the appropriate DB2 Client such as one of the IBM data server client or driver For more information about the DB2 Connect client see the topic about IBM data server client types DB2 Connect can also be installed on a Web server Transaction Processor TP monitor or other 3 tier application server machines with multiple local SQL application processes and threads In these cases you can choose to install DB
296. ts as much data as possible between transmissions This approach doesn t work for applications such as distributed databases because it builds delays into the network The end user doesn t see the efficiency of the network only the delays Most network devices have delay parameters and most of them default to values that are very bad for distributed databases To improve performance you should locate these parameters and if possible set them to zero In addition you should ensure that the buffer size on the device is large enough to prevent retransmits due 152 DB2 Connect User s Guide to lost data For instance UNIX systems typically have a Transmit or Receive queue depth default of 32 For better results set the queue depth to 150 A corresponding parameter on DLC settings is the Receive Depth which should also be 150 The IOBUF parameter is set too low at most sites It is usually set at 500 but experience has shown that a value of 3992 works best if you are moving large amounts of data especially for channel connections such as ESCON or 3172 On a LAN system the DLC or LLC transmit and receive window sizes can have a dramatic effect on performance The send value should be set to seven or more and for most configurations a receive value of four or less works best If you are running Ethernet you should set the TCP segment size to 1500 bytes On a token ring or FDDI network this value should be 4400 bytes and if you are using
297. ture automatically starts the DB2 Setup wizard The DB2 Setup wizard will determine the system language and launch the setup program for that language If you want to run the setup program in a different language or the setup program failed to autostart you can run the DB2 Setup wizard manually 4 The DB2 Launchpad opens From this window you can view the installation prerequisites and the release notes or you can proceed directly to the installation 5 Once you have initiated the installation proceed by following the setup program s prompts Online help is available to guide you through the remaining steps Click Help to invoke the online help You can click Cancel at any time to exit the installation 48 DB2 Connect User s Guide A log file stores general information and error messages resulting from the install and uninstall activities The file name of the log follows the format DB2 Product_Abrreviation Date_Time log such as DB2 CEE 10 06 2006_17_23_42 log By default the log file is located in the My Documents DB2L0G directory e To invoke the DB2 Setup wizard manually 1 Click Start and select the Run option 2 In the Open field enter the following command x setup i language where x represents your DVD drive language represents the territory code for your language for example EN for English 3 Click OK What to do next If you want your DB2 database product to have access to DB2 documentation ei
298. uests a connection to the host DB2 Connect will attempt to get an agent from among those in the pool that have a connection to the host database server If that fails it will try to find an available agent in the idle pool If the pool is empty DB2 Connect will create a new agent You can control the maximum number of agents that can be concurrently active using the max_coordagents configuration parameter Once this number is exceeded new connections will fail with error sqlcode SQL1226 This code means that the maximum number of concurrent outbound connections has been exceeded The default for this configuration parameter is set to AUTOMATIC with a value of 200 By being set to AUTOMATIC DB2 Connect automatically manages the number of coordinator agents The DB2 registry variable DB2CONNECT_IN_APP_PROCESS allows applications running on the same machine as a DB2 Connect server product to either have DB2 Connect run within the applications process default behavior or to have the application connect to the DB2 Connect server product and then have the host connection run within an agent For an application to use connection pooling the connections to the host must be made from within the DB2 Connect server product agents and thus DB2CONNECT_IN_APP_PROCESS must be set to NO DB2 Connect Connection Pooling versus Application Server Connection Pooling Connection pooling is a must for any web technologies based application that is to support la
299. use stored procedures All executable statements except the following statements can be contained within a Compound SQL statement CALL FETCH CLOSE OPEN Compound SQL Connect Prepare Release Describe Rollback Disconnect Set connection execute immediate Stored procedures help to reduce network traffic by placing program logic at the server You can commit automatically when exiting the procedure You can also return results sets which minimize application logic at the client Grouping requests 140 DB2 Connect User s Guide Grouping related database requests SQL statements into one database request can reduce the number of requests and responses transmitted across the network For example grouping the following statements SELECT COL1 COL2 COL5 COL6 FROM TABLEA WHERE ROW_ID 1 SELECT COL1 COL2 COL5 COL6 FROM TABLEA WHERE ROW _ID 2 into SELECT COL1 COL2 COL5 COL6 FROM TABLEA WHERE ROW _ID 1 OR ROW_ID 2 sends fewer requests across the network You can also use keywords such as IN and BETWEEN to reduce the number of rows returned In addition you can use WHERE IN and BETWEEN keywords on UPDATE and DELETE statements Predicate logic You can use predicate logic to request only the rows and columns that are needed This minimizes the network traffic and CPU usage for data transmission For example do not use the query SELECT FROM TABLEA if only the first row of TABLEA with ROW_ID 1 is really
300. ut cannot access the database server with it A connection in an unconnected state will remain in that state until you successfully switch users on it What to do next Note 1 Important Switching users without supplying a password bypasses the database server s authentication Your application must not allow a switch to an authorization ID without a password unless that application has already validated and authenticated that authorization ID To do otherwise creates a security hole Specifying a NULL value for the SQL_ATTR_TRUSTED_CONTEXT_USERID attribute is equivalent to specifying the trusted context system authorization ID the user id used when the explicit trusted connection was created When you successfully set the value of the SQL_ATTR_TRUSTED_CONTEXT_USERID connection attribute on an explicit trusted connection the connection is immediately reset The result of resetting is as if anew connection were created using the original connection attributes of that connection This reset happens even if the value you set the connection attribute to is the system authorization ID or NULL or the same value that the attribute currently holds If the SQL_ATTR_TRUSTED_CONTEXT_PASSWORD attribute is set the password will be authenticated during the switch user processing even if the trusted context that allowed the trusted connection doesn t require authentication on a switch user for that authorization ID This results in unnecessary pro
301. wever the user still needs to be a member of the Local Administrators group The Debug programs privilege is only needed when DB2 group lookup is explicitly specified to use the access token If the user account is created by the install program the user account will be granted these privileges and if the user account already exists this account will also be granted these privileges If the install grants the privileges some of them will only be effective on first log on by the account that was granted the privileges or upon reboot It is recommended that the DAS user have SYSADM authority on each of the DB2 database systems within your environment so that it can start or stop other instances if required By default any user that is part of the Administrators group has SYSADM authority DB2 instance user account 52 DB2 Connect User s Guide The user account must belong to the Administrators group on the computer where you will perform the installation A local or domain user account is required for the DB2 instance because the instance is run as a Windows service and the service will be executing in the security context of the user account When you use a domain user account to perform a database operation such as creating a database against a DB2 instance the DB2 service needs to access the domain to authenticate and search for the user s group membership By default a domain will only allow a domain user to query the domain a
302. wing advanced user rights Act as part of the operating system Create token object Increase quotas Replace a process level token 62 DB2 Connect User s Guide You can perform the installation without advanced user rights but the setup program might be unable to validate accounts e If you want to install DB2 Connect with a non Administrator account refer to the topic Non Administrator installation of DB2 Connect Windows Procedure e To install DB2 Connect Personal Edition using the DB2 Setup wizard 1 Log on to the system as a user with administrator authority 2 Close all programs so the installation program can update files as required 3 If you have a computer with a IBM data server client installed you can activate DB2 Connect Personal Edition by registering your DB2 Connect Personal Edition license to that computer To install DB2 Connect Personal Edition by running the setup program follow the remaining steps 4 Insert the DVD into the drive The auto run feature automatically starts the DB2 Setup wizard The DB2 Setup wizard will determine the system language and launch the setup program for that language If you want to run the setup program in a different language or the setup program failed to auto start you can run the DB2 Setup wizard manually 5 The DB2 Launchpad opens From this window you can view the installation prerequisites and the release notes or you can proceed directly to the i
303. with each other Even when application server connection pooling is enabled DB2 Connect connection pooling can provide connection reuse for multiple application servers as well as other clients using the DB2 Connect server Connection concentrator The connection concentrator reduces the resources required on DB2 for z OS database servers to support large numbers of workstation and web users This function can dramatically increase the scalability of your DB2 for z OS and DB2 Connect solution while also providing for fail safe operation and transaction level load balancing in DB2 for z OS data sharing environments The connection concentrator allows applications to stay connected without any resources being consumed on the DB2 host server You can have thousands of users active in applications and only have a few threads active on the DB2 host server DB2 Connect s connection concentrator technology allows DB2 Connect server products such as DB2 Connect Enterprise Edition to provide support to thousands of users simultaneously executing business transactions while drastically reducing resources required on the System z host or IBM Power Systems database servers It accomplishes this goal by concentrating the workload from all applications in a much smaller number of System z host or IBM Power Systems database server connections While this might seem similar to the connection pooling function described previously it is in fact a more sophistic
304. wo phase commit is not used This restriction is relaxed if the user configures the SPM log to be on a shared disk this can be done through the spm_log_path database manager configuration parameter and the failover system has an identical TCP IP configuration the same host name IP address and so on Mounting CDs or DVDs for DB2 Connect Solaris If the CD ROM is not automatically mounted when you insert it into the drive on Solaris Operating System issue the mount command Before you begin If you are mounting the CD or DVD drive from a remote system using NFS the CD or DVD file system on the remote computer must be exported with root access Depending on your local system configuration you might also need root access on the local computer Chapter 3 Installing DB2 Connect server 61 Procedure To mount the CD or DVD on Solaris 1 Insert the CD or DVD into the drive 2 If the Volume Manager vold is running on your system the disc is automatically mounted as cdrom cd_label if the CD or DVD has a label or cdrom unnamed_cdrom if it is unlabeled If the Volume Manager is not running on your system complete the following steps to mount the CD or DVD a Determine the name of the device by entering the following command ls al dev sr awk print 11 This command returns the name of the CD or DVD device In this example the command returns the string dev dsk cOt6d0s2 b Enter the following commands to mou
305. word not available to send to the target server database A password has to be sent from the IBM data server client to the target server database On certain platforms for example AIX the password can only be obtained if it is provided on the CONNECT statement Appendix A Overview of the DB2 technical information DB2 technical information is available in multiple formats that can be accessed in multiple ways DB2 technical information is available through the following tools and methods e DB2 Information Center Topics Task concept and reference topics Sample programs Tutorials e DB2 books PDF files downloadable PDF files from the DB2 PDF DVD printed books e Command line help Command help Message help Note The DB2 Information Center topics are updated more frequently than either the PDF or the hardcopy books To get the most current information install the documentation updates as they become available or refer to the DB2 Information Center at ibm com You can access additional DB2 technical information such as technotes white papers and IBM Redbooks publications online at ibm com Access the DB2 Information Management software library site at http www ibm com software data sw library Documentation feedback We value your feedback on the DB2 documentation If you have suggestions for how to improve the DB2 documentation send an email to db2docs ca ibm com The DB2 documentation team r
306. xit create the target table on the target DB2 database instance CREATE TABLE mydb staff ID SMALLINT NOT NULL NAME VARCHAR 9 DEPT SMALLINT JOB CHAR 5 YEARS SMALLINT SALARY DECIMAL 7 2 COMM DECIMAL 7 2 To import the data issue the following command db2 import from staff ixf of ixf insert into mydb staff Each row of data will be read from the file in IXF format and an SQL INSERT statement will be issued to insert the row into table mydb staff Single rows will continue to be inserted until all of the data has been moved to the target table What to do next Detailed information is available in Moving Data Across the DB2 Family an IBM Redbooks publication This Redbooks publication can be found at the following website www redbooks ibm com redbooks SG246905 Chapter 5 Administering 89 Automatic client reroute description and setup DB2 Connect server The main goal of the automatic client reroute feature is to enable an IBM Data Server Client application to recover from a loss of communications so that the application can continue its work with minimal interruption As the name suggests rerouting is central to the support of continuous operations But rerouting is only possible when there is an alternate location that is identified to the client connection Rerouting is not required if using the IBM data server client as a DB2 Connect client For details see the topic about IBM data server client types Automatic
307. ymptoms and solutions for connection problems that you can encounter when using DB2 Connect In each case you are provided with e A combination of a message number and a return code or protocol specific return code associated with that message Each message and return code combination has a separate heading and the headings are ordered by message number and then by return code e A symptom usually in the form of a sample message listing e A suggested solution indicating the probable cause of the error In some cases more than one suggested solution might be provided SQL0965 or SQL0969 Symptom Messages SQL0965 and SQL0969 can be issued with a number of different return codes from IBM DB2 for IBM i DB2 for z OS and DB2 Server for VM and VSE When you encounter either message you should look up the original SQL code in the documentation for the database server product issuing the message Solution The SQL code received from the IBM mainframe database cannot be translated Correct the problem based on the error code then resubmit the failing command SQL5043N Symptom Support for one or more communications protocols failed to start successfully However core database manager functionality started successfully Perhaps the TCP IP protocol is not started on the DB2 Connect server There might have been a successful client connection previously If diaglevel 4 then the db2diag log files might contain a similar e
308. ysicalDisk Local active databases DB2 is DB2 Database Manager WILEW2 Local connections DB2 wo DB2 Database Manager WWILEW2 Local connections executing in Database Manager DBZ wet DB2 Database Manager WILEW2 Maximum number of agents registered DB2 tet DB2 Database Manager WILEW2 Maximum number of agents waiting for a token DB2 wo DB2 Database Manager WILEW2 xl Figure 9 Performance Monitor To enable monitoring of local applications you will need to turn off the DB2CONNECT_IN_APP_PROCESS environment variable Using the GET SNAPSHOT commands The DB2 monitor maintains a running tally of valuable system information You can get a summary of system status at any time by issuing the GET SNAPSHOT command You can take monitor snapshots if you have SYSMAINT SYSCTRL or SYSADM authority for the database manager instance that you want to monitor There are five snapshot commands useful for monitoring DCS information They are e GET SNAPSHOT FOR ALL DCS DATABASES e GET SNAPSHOT FOR ALL DCS APPLICATIONS e GET SNAPSHOT FOR DCS APPLICATION e GET SNAPSHOT FOR DCS DATABASE ON db_alias e GET SNAPSHOT FOR DCS APPLICATIONS ON db_alias Each snapshot command will produce a detailed report about the area you requested For instance issuing the GET SNAPSHOT FOR DCS DATABASE ON DCSDB will produce the following report DCS Database Snapshot DCS database name DCSDB Host database name GILROY First database connect timestamp

DB2 Connect User's Guide

Contents

Download Pdf Manuals

Related Search

Related Contents