Front cover DB2 Deployment Guide Learn to deploy DB2 Data Servers and Clients Automate DB2 mass deployment with scripts Deploy DB2 with applications Whei-Jen Chen Jian TJ Tang Carsten Block John Chun ibm.
International Technical Support Organization DB2 Deployment Guide October 2008 SG24-7653-00
Note: Before using this information and the product it supports, read the information in “Notices” on page vii. First Edition (October 2008) This edition applies to DB2 for Linux, UNIX, and Windows Version 9.5. © Copyright International Business Machines Corporation 2008. All rights reserved. Note to U.S. Government Users Restricted Rights -- Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
Contents Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viii Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix The team that wrote this book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Acknowledgements . . . . . . . . .
2.3.2 DB2 license . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 2.3.3 Creating the deployment script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 2.3.4 Windows deployment scripts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 2.4 Fix pack deployment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 2.4.1 Fix pack overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.5.1 IBM IBM_DB gem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 4.5.2 Installation of IBM_DB gem. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 4.5.3 Creating a sample Ruby application . . . . . . . . . . . . . . . . . . . . . . . . 183 4.5.4 Deploying a Ruby application with the DB2 drivers . . . . . . . . . . . . 183 4.5.5 Help and support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188 4.6 Python . . . . . . . . . . .
A.1 A.2 A.3 A.4 A.5 C/C++ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 PHP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262 Ruby . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263 Python. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264 Perl . . . . . . . . . . . . . . . . . .
Notices This information was developed for products and services offered in the U.S.A. IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative for information on 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.
Trademarks IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both. These and other IBM trademarked terms are marked on their first occurrence in this information with the appropriate symbol (® or ™), indicating US registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries.
Preface DB2® provides various installation methods as well as features and tools to deploy a large number of clients and servers. Database administrators, application developers, and application architects have a number of available options when deploying DB2 9.5 for Linux®, UNIX®, and Windows® (DB2 for LUW). Focusing on the DB2 V9.5 deployment methodology, this IBM® Redbooks® publication provides general guidance and serves as a reference resource for DB2 based solution deployment.
focuses on DB2 MPP system and performance tuning in complex environments. He also has a job role in a leading critical situation support team for large customers. Prior to joining IBM, he was an application developer providing data warehouse/BI solutions to customers. He is a Certified DB2 Application Developer and Database Administrator. Carsten Block is a Senior IT Specialist with IBM Global Services in Denmark.
Helmut Riegler IBM Austria Yvonne Lyon, Sangam Racherla, Emma Jacobs International Technical Support Organization, San Jose Center Become a published author Join us for a two- to six-week residency program! Help write a book dealing with specific products or solutions, while getting hands-on experience with leading-edge technologies. You will have the opportunity to team with IBM technical professionals, Business Partners, and Clients.
xii DB2 Deployment Guide
1 Chapter 1. Introduction to DB2 deployment In this chapter, we introduce various aspects of DB2 9.5 for Linux, UNIX, and Windows deployment. We present various DB2 9.5 product offerings and items to consider during deployment planning. We discuss the following topics: Various product offerings from DB2 9.5 for Linux, UNIX, and Windows Key items to consider during deployment planning © Copyright IBM Corp. 2008. All rights reserved.
1.1 DB2 deployment overview Database administrators, application developers, and application architects have a number of available options when deploying DB2 9.5 for Linux, UNIX, and Windows. Planning the deployment of DB2 based solutions among various operating system platforms and DB2 products might appear be a complex endeavor. In order to assist with deployment planning, this book provides general guidance and serves as a reference resource for DB2 based solution deployment. DB2 V9.
1.2 DB2 9.5 for UNIX, Linux, and Windows products One of the first things to consider during deployment planning is to choose the right DB2 product for your enterprise. The DB2 product is available on Windows 2000/2003/XP/Vista, Linux, AIX®, Hewlett-Packard’s HP-UX, Sun™ Microsystems’ Solaris™, OS/400®, i5/OS®, VSE/VM, and z/OS®. The SQL API is common to all platforms for DB2 products, allowing data to be accessed freely across various platforms. The DB2 9.
DB2 Workgroup Server Edition Designed for midsize businesses, it has all of the functionality of DB2 Express plus an optional Query Optimization Feature. It has more extensive licensing and a resource usage limit of 480 Processor Value Unit (PVU) and memory limit of 16 GB. This product is available on AIX, Windows, Linux, HP-UX, and Solaris. DB2 Enterprise Server Edition Scalable to handle large volume transaction processing, it is designed for large businesses.
administration and client/server configuration. Note that GUI tools are available for Windows on x86 32 bit, Windows on x64 (EM64T/AMD64), Linux on x86/ EM64T/AMD64. It also contains tools to assist with application development and contains bind files. The application development tools include application header files, pre-compilers, bind utilities, and sample codes. It was previously known as DB2 Administration Client or DB2 Client. IBM Data Server Driver for ODBC, CLI, and .NET New to DB2 9.
Any SQLJ or JDBC application that accesses i5/OS using IBM Data Server Driver for JDBC and SQLJ type 4 connectivity, must ensure that the OS/400 operating system supports the Unicode UTF-8 encoding scheme. Downloadable DB2 products and components are listed in Table 1-1. Table 1-1 Downloadable DB2 products and components 6 DB2 Products and components Download IBM DB2 9.5 Express-C https://www14.software.ibm.com/webapp/iwm/web/ preLogin.
1.2.3 DB2 standalone and connect products DB2 also provides various flexibility and options in its product offering. The DB2 connect products provide connectivity to host databases, while the DB2 personal edition provides standalone database for local use. DB2 Personal Edition This is a restricted desktop product with a full functional database, without Query Patroller, TSA, Geodetic Performance Expert, and Heterogeneous Federation.
1.2.4 Other DB2 products DB2 products are also available in different packaged bundles for specific enterprise requirements. IBM Database Enterprise Developer Edition (DEDE) 9.5 The IBM Database Enterprise Developer Edition is not an actual product but is a product bundle. It allows the application developer to deign, build, and prototype applications for deployment on various IBM Information Management clients or servers. This edition is for test or development use only, not for production use.
InfoSphere Warehouse (as of 9.5.1) InfoSphere™ Warehouse was called DB2 Warehouse Edition (DWE) in 9.5.0. It is another product bundle that includes DB2 9.5 Enterprise Edition with the Data Partitioning Feature (DPF) as well as other tools and features.
DB2 Enterprise and DB2 Connect Enterprise DB2 Workgroup DB2 Express and DB2 Express-C DB2 Personal DB2 Connect Personal 64bit SPARC Yes Yes No No No 64bit x64 Yes Yes Yes2 No Yes2 Platforms Solaris 1 2 These products can be installed on given platforms but they are not supported for production use (only for development and test purposes) As of DB2 9.5 Fix pack 1 1.
1.3.1 New environment versus environment with existing DB2 installation Unless given deployment is to take place in a brand new environment, it is likely that DB2 client product or servers are already in place. As such, you should take full inventory of all the DB2 clients and server currently in place as well as the users and applications that utilize them. If the DB2 product is currently installed in an environment where deployment is to take place, consider the compatibility of the various DB2 versions.
Table 1-3 outlines supported combinations of client and server versions. Table 1-3 Supported client /server matrix DB2 Clients Version 8 32-bit Server UNIX, Windows, Linux Version 8 32-bit Server UNIX, Windows, Linux Version 9 Server UNIX, Windows, Linux Version 9.5 Server UNIX, Windows, Linux Version 9.5 Yes Yes Yes Yes Version 9.
Client/server relationship If DB2 servers are taking part in workload balancing, then determining the correct number of client requests and workload would be essential to allocating sufficient resource for a given environment. When deploying an application that interacts with the DB2 database, one of the key decisions that have to be made is whether or not it should reside locally with the DB2 server. As with any decision, there are pros and cons that must be weighed for each specific scenario.
Table 1-5 illustrates the software requirements for DB2 9.5. Table 1-5 Software requirement for DB2 9.5 DB2 Servers and IBM data server clients AIX Version 5.3 64-bit AIX kernel required AIX 5.3 Technology Level (TL) 6 and Service Pack (SP) 2 plus APAR IZ03063 Minimum C++ runtime level requires the xlC.rte 9.0.0.1 and xlC.aix50.rte 9.0.0.1 filesets. These filesets are included in the August 2007 IBM C++ Runtime Environment Components for AIX package.
DB2 Servers and IBM data server clients SUSE® Linux Enterprise Server (SLES) 9 Service Pack 3 SUSE Linux Enterprise Server (SLES) 10 Service Pack 1 Package requirements: libaio compat-libstdc++ (Not for POWER) pdksh openssh rsh-server nfs-utils Windows XP Professional (32-bit and x64) Windows Vista® (32-bit and x64) IBM Data Server Provider for .NET client applications and CLR server-side procedures require .NET 1.1 SP1 or .NET 2.
DB2 Servers and IBM data server clients Solaris 10 HP-UX DB2 Connect Servers UltraSPARC: – 64- bit kernel – If raw devices are used, patch 125100-07 Solaris x64: – 64- bit kernel – Patch 118855-33 – If raw devices are used, patch 125101-07 HP-UX 11iv2 (11.23.0505) with: – May 2005 Base Quality (QPKBASE) bundle – May 2005 Applications Quality (QPKAPPS) bundle HP-UX 11iv3 (11.
1.3.3 DB2 product considerations As discussed in 1.2, “DB2 9.5 for UNIX, Linux, and Windows products” on page 3, DB2 is available is different editions and packages. All DB2 editions and products share the same code base but are available with different features, licensing, and different system limits. Table 1-6 summaries various DB2 for LUW editions.
Express-C and Express-C FTL DB2 Express edition DB2 Workgroup Server Edition DB2 Enterprise Server Edition Connection Concentrator No No No Yes Table Partitioning No No No Yes Governor No No No Yes Homogenous Federation No Yes with Homogeneous Federation Feature option Yes with Homogeneous Federation Feature option Yes with Homogeneous Federation Feature option Compression: Row Level or Backup No No No Yes with Storage Optimization Feature option Query Patroller No No No Yes
In addition to differences in features, each edition also sets different system limits. As shown in Table 1-6 on page 17, each edition has different CPU or PVU limits in addition to memory utilization limits set for DB2 operation. Therefore, when planning the DB2 server deployment, the planner must gather information regarding the potential workload. Generalized workload capabilities of DB2 server products are shown in Figure 1-1.
Figure 1-2 shows the DB2 9.5 product decision flow. Start DB2 Connect Personal Edition Require connection to host database(s)? Need to accept remote requests? e.g.
An Authorized User is defined as one and only one individual with a specific identity within or outside your organization. Each Authorized User has a unique specific identity and ID, which cannot be shared or transferred unless there is a change in employment status. An ID can establish one or more connections to the program and count as a single Authorized User.
For further details and the most updated PVU table, refer to: http://www.ibm.com/software/lotus/passportadvantage/ pvu_licensing_for_customers.html The DB2 Personal edition and DB2 Personal Connection editions are licensed per client device, and DB2 Express-C FTL is licensed per server. There is no licensing cost associated with DB2 Express-C edition as well as DB2 clients outlined in 1.2.2, “DB2 clients and drivers” on page 4.
The non-root/non-Administrator might not be ideal for all because it poses some limitations. Before planning to deploy the DB2 product as a non-root/ non-Administrator user, restrictions and requirements associated with non-root/ non-Administrator installation should be fully considered.
Configuration Assistant and Control Center are not available on non-root installation. Ability for the db2governor to increase priority is not supported. Agent priority set by Work Load Manager (WLM) in a DB2 services class in a non-root DB2 instance will not respected. Automatic starting of non-root DB2 instances at system reboot is not supported. Sending alert notifications, running script or task action on alert occurrences in Health monitor are not supported in non-root installation.
Requirements These are the requirements of non-root installation on Windows platforms: Non-Administrator user ID must belong to Power Users group. VS2005 runtime library must be installed before attempting to install a DB2 product. Limitations These are the limitations of non-root installation on Windows platforms: Services that would be automatically started are run as processes in non-Administrator installs. In DB2 Connect, some environment settings must be changed in HKEY_CURRENT_USER.
1.3.6 Configuration considerations Decision to create DB2 instance(s) and Database Administration Server (DAS) instance should be fully explored during the deployment planning. Creating DB2 instance(s) during deployment can ease the database configuration process and also ensures consistent environment creation. If deployment includes instance creation, we recommend DB2 product to be installed using the response file.
There are various methods for installing DB2 products. Table 1-8 provides a summary of various installation methods. Table 1-8 Installation methods Installation method Windows Linux or UNIX DB2 Setup wizard Yes Yes Response file installation Yes Yes db2_install command No Yes Payload file deployment No Yes These DB2 installation methods are further discussed in Chapter 2, “DB2 server deployment” on page 29 and Chapter 3, “DB2 client deployment” on page 89.
28 DB2 Deployment Guide
2 Chapter 2. DB2 server deployment In this chapter, we cover DB2 server deployment considerations and steps involved in the deployment process. In general, the DB2 server deployment takes place prior to the deployment of DB2 clients or DB2 applications. We present various tools from the DB2 and operating systems that facilitate deployment tasks and provide various customization capabilities.
2.1 Server deployment planning DB2 server deployment planning should take place at the same time that the application is designed or when a DB2 based solution is first being planned. There are numerous factors that you have to take into consideration. Selection of DB2 server editions must be based on the application requirements. For those environments where multiple machines are involved, more detailed planning is required if different hardware types are used.
When using the DB2 Setup installation wizard on Linux and UNIX platforms, instance user, fenced user, and administration server user can be created during the installation process. You do not have to create them in advance. Because response file installation uses the DB2 Setup wizard as well, the situation is the same. But if you are using another installation method, such as db2_install, user account creation has to be done manually.
Administration server user DB2 Administration server (DAS) provides support for DB2 GUI tools and other administrative tasks, such as Control Center, Configuration Assistant, and Task Center. Each physical operating system can have only one DAS created. Windows security For Windows environments, an instance owner user and an administration server user are required for a typical DB2 server deployment. DB2 products provides extended Windows security features since the DB2 Version 8.2.
There are some limitations of the non-root installation of which you should be aware. For DB2 server products, some limitations of a non-root installation are as follows: DB2 Administration Server and related commands are not available: dascrt, dasdrop, daslist, dasmigr, and dasupdt. DB2 Control Center and DB2 Configuration Assistant are not available. The agent priority set with Work Load Manager (WLM) in a DB2 service class in a non-root DB2 instance is ignored and no SQLCODE is returned.
On Windows Vista, the non-Administrator account still is prompted for administrative credentials by the DB2 Setup Wizard. So the Administrator’s intervention in the installation is still required. Take the following limitations into considerations before starting the non-Administrator installation: Non-Administrator users can only install fix packs, add-on products, or upgrade DB2 as long as prior installations or upgrades were also performed by the same non-Administrator user.
Therefore, it might be necessary to consider remote database connectivity/ catalog during the DB2 server deployment planning if you have the following configurations: The applications running on the same machine as the DB2 server has to access a remote database. Database federation is enabled and other databases have to be accessed by the local server through a nickname. High Availability Disaster Recovery (HADR) is set up.
Figure 2-1 DB2 Configuration Assistance To export the connectivity information, select from the menu Configure → Export Profile. There are three options for your choice: All, Database Connections, and Customize. You can choose to export all of the entries or only some of them. For example, if we choose Database Connections, the output file generated by DB2 Configuration Assistant looks similar to the one shown in Example 2-1.
[DB>ZAIRE01:ITSODB] Dir_entry_type=REMOTE Authentication=NOTSPEC DBName=ITSODB [DB>!LOCAL:SAMPLE] Dir_entry_type=INDIRECT Drive=/home/db2inst1/db2home DBName=SAMPLE db2cfexp and db2cfimp The export and import of configuration profile can also be performed from the command line using the DB2 utilities db2cfexp and db2cfimp. The db2cfexp is used to do export, while db2cfimp is used to do import.
This command is used to export configuration profile: db2cfexp cf.exp template This command is used to import a configuration profile: db2cfimp cf.exp The file generated by db2cfexp is very similar to the one generated by the DB2 Configuration Assistant. Note: If there are connectivity entries (database or node) existing with the same name, they will be replaced with the new definitions imported by db2cfimp.
The generated sample.ddl is shown in Example 2-2. When using this script to configure a new database, remember to change the database name if it is different from the old one. Example 2-2 db2look output ------- This CLP file was created using DB2LOOK Version 9.5 Timestamp: Thu 15 Mar 2007 02:47:52 AM PDT Database Name: SAMPLE Database Manager Version: DB2/LINUXX8664 Version 9.5.
A partitioned database is a database created across multiple database partitions. Each database partition has its own processes, memory, and data storage. These resources are not shared between partitions. Queries submitted to the DB2 database are distributed to each database partition and processed in parallel. If there are multiple processors in each database partition node, internal parallelism can be used to achieve better performance.
Other file system technologies that provide concurrent access to a common set of files can also be used in creating the instance home directory in a DB2 partitioned database system. The IBM General Parallel File System™ (GPFS™) is an example. It is a high performance scalable file management solution available on both AIX and Linux. For more information about GPFS, refer to: http://www.ibm.com/systems/clusters/software/gpfs/index.
Communication settings DB2 partitions communicate with each other through TCP/IP. During the planning, ensure that there are consecutive ports with same number available on each participating machine. The number of the available ports must be equal to or greater than the number of participating partitions plus two. Suppose that you set up a partitioned database system comprising six partitions. Then you have to reserve at least eight ports on each participating computer.
DB2 provides a number of deployment methods for various requirements: DB2 Setup Wizard db2_install (Linux and UNIX only) Response file Payload file (Linux and UNIX only) 2.2.1 DB2 Setup wizard This interactive step-by-step installation wizard is probably the most commonly used method for deploying a DB2 server to one or only a few machines. On UNIX and Linux, this wizard is built on Java technology and is invoked through the command db2setup. On Windows, the corresponding command is setup.
Figure 2-2 DB2setup launchpad 3. In the menu, clicking Install a Product brings you the introduction of the installation image. A DB2 server installation image can contain multiple products. For instance, the DB2 9.5 Enterprise Server Edition installation image includes: – DB2 Enterprise Server Edition – IBM Data Server Client Version 9.5 – IBM Data Server Runtime Client Version 9.5 4. For each product, choose Install New to start a new installation.
– Compact: Compact mode does not include the following components that are included in a Typical mode installation: • • • • • DB2 data source support DB2 LDAP support DB2 Instance setup wizard First steps Sample database source – Custom Custom mode allows experienced users to select specific components to install. 6. With the wizard, you can create a DB2 instance as well as the Administration Server. It prompts you to define the group and user names for the instance. See Figure 2-3.
Figure 2-4 Summary of the installation settings 8. A log file is created by the DB2 Setup wizard at same time. It is used to log the installations steps that DB2 Setup wizard has undergone, and its results. The default path of the log file is /tmp/db2setup.log for root installation. Log file of a non-root installation is /tmp/db2setup_userID.log, where user ID is the user account that executes the installation. On Windows, the installation log file can be located in the path, “My Documents\DB2LOG\”.
Note that db2_install is only available on UNIX and Linux platforms. Some people prefer this method because it bypasses the configuration performed by DB2 Setup and allows you to configure DB2 your preferred way in the first place. Though not interactive as DB2 Setup, db2_install will prompt the user for the required information if no options are specified.
Enter "help" to redisplay product names. Enter "quit" to exit. *********************************************************** 3. For the installation directory, to deploy DB2 to a different location other than the default, enter yes followed with Enter and then input the target path. To use the default, input no and it will go directly to the last question. 4. For the question, Specify one of the following keywords to install DB2 products, choose the product that you want to install.
The response file installation is supported on UNIX, Linux, and Windows. The command to start a response file deployment is a command of the DB2 Setup wizard with different command line options. It is db2setup on UNIX/Linux system and setup on Windows. When using the DB2 Setup wizard to generate the response file: The response file can be specified by choosing options in the step, Select the installation action.
Creating a response file using the DB2 Setup wizard You can create a response file using the DB2 Setup wizard. This is a recommended method to create a response file. To deploy DB2 and generate a response file at the same time using the DB2 Setup wizard, select the option, Install DB2 Enterprise Server Edition Version 9.5 on this computer and save my settings in a response file, from the window, Select installation, response file creation, or both.
The db2rspgn utility automatically creates an instance configuration profile for all instances on current system. However, you can also specify a configuration profile for select instances. The configuration profile gets saved to the same path as that of the response file. The db2rspgn utility can be invoked from the DB2 Command Line Processor window: db2rspgn –d [-i ] Where: -d : The destination directory for a response file and any instance files.
The configuration profile file can also be generated on its own through the Connectivity Configuration Export (db2cfexp) tool command. If the response file was not created using the db2rspgn tool and if you wish to include a database profile in your deployment, generate the DB2 profile using the db2cfexp tool and include the DB2.CLIENT_IMPORT_PROFILE keyword with the configuration profile name.
3. Specify the instance information: When the desired components are selected, scroll down in the sample response file to the instance section to specify the information for creating DB2 instance. Here we define two instances, db2inst6 and db2inst7, with respective instance owning user and fence user. We create a local database ITSO under instance db2inst6, and catalog a remote database ITSOD for db2inst7. A DB2 Administration Server db2das is also defined.
COMP = INSTANCE_SETUP_SUPPORT COMP = LDAP_EXPLOITATION COMP = DB2_SAMPLE_DATABASE COMP = SQL_PROCEDURES COMP = REPL_CLIENT COMP = JAVA_SUPPORT COMP = BASE_DB2_ENGINE COMP = CONTROL_CENTER COMP = JDK COMP = DB2_DATA_SOURCE_SUPPORT COMP = CONNECT_SUPPORT COMP = BASE_CLIENT COMP = TEXT_SEARCH COMP = FIRST_STEPS COMP = APPLICATION_DEVELOPMENT_TOOLS *----------------------------------------------* Das properties *----------------------------------------------DAS_USERNAME = db2das DAS_PASSWORD = DAS_GR
inst2.FENCED_USERNAME = db2fenc7 inst2.FENCED_GROUP_NAME = db2fadm7 inst2.FENCED_HOME_DIRECTORY = /home/db2fenc7 inst2.FENCED_PASSWORD = *---------------------------------------------* Database section *---------------------------------------------DATABASE = db01 db01.INSTANCE = inst1 db01.DATABASE_NAME = ITSO db01.LOCATION = LOCAL db01.ALIAS = ITSO db01.PATH = /tmp/db2rsp/db01 DATABASE = db02 db02.INSTANCE = inst2 db02.DATABASE_NAME = ITSODB db02.LOCATION = REMOTE db02.ALIAS = ITSODB db02.
One of the useful functions presented in the sample program is a way to read progress information and general message from the DB2 installer, and report process back to the user. The sample program only installs a DB2 product using a response file. You can add your customized application deployment codes into it, or just use this sample program as one of the steps in your deployment script. These are the steps to build and deploy using the sample program written in C language: 1.
During the execution of InstallTester, you can see how original progress information and general messages are processed.
2.3 Mass deployment of DB2 server using a script It is not uncommon for an enterprise that requires deploying the DB2 server to many systems. In this section, we discuss an approach to implement a mass deployment of the DB2 server. We use a deployment script to automate the installation process as much as we can to save on deployment time and reduce the chances of error that might introduced in a manual process. You also can use db2setup along with a response file to implement a mass DB2 server deployment.
2.3.1 Setup of SSH and NFS Before we can start the mass deployment, a secure and convenient authentication channel must be set up to allow us to issue commands with proper privileges to the remote machines. Installing SSH on managed machines For AIX platforms, OpenSSH and its prerequisites might not be installed by default. Since in our case, ssh is required as the remote shell tool, OpenSSH has to be installed and configured.
Example 2-10 Accept the authentication key the first time accessing a machine using ssh $ ssh root@baltic The authenticity of host 'baltic (9.43.86.48)' can't be established. RSA key fingerprint is 48:56:a4:d9:27:25:12:0f:b3:11:5a:60:52:7a:25:e0. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'baltic,9.43.86.48' (RSA) to the list of known hosts.
Here we use Digital Signature Algorithm (DSA) as the key type. You can use RSA instead. The corresponding command is shown in Example 2-13. Example 2-13 Generate a public and private key pair using RSA # cd ~/.ssh # ssh-keygen -t rsa -f ~/.ssh/id_rsa -N "" The option -N "" informs the command ssh-keygen to use an empty passphrase when generating the key files. A passphrase is like a password for the key file. Option -f specifies the file name for a key file.
Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'baltic,9.43.86.48' (RSA) to the list of known hosts. root@baltic's password: For RSA encryption, the command is almost the same as DSA, except for the name of the public key file. cat ~/.ssh/id_rsa.pub | ssh root@baltic 'cat >> ~/.ssh/authorized_keys' 4. Now we have successfully enabled automatic login on the remote machine Baltic.
NFS configuration In a mass deployment scenario, copying the installation image to each target machine is not a convenient method. It is time consuming and requires extra temporary space to be allocated on each machine to keep the image. A better approach is to keep the installation image in a file server and make it available to other systems. We keep the installation image on Zaire and make the image available to others through Network File System (NFS).
2.3.3 Creating the deployment script The SSH and NFS should be configured prior to implementing the DB2 server deployment. These are basis for running an automatic deployment across the network without any user interaction. Our deployment script performs the following tasks: Parse the command line options and perform basic syntax checking. Go through the hosts one by one and perform installation in sequence. The host names are read in from the command line.
Script Start Parse command line options Do basic synatx check on options chksyntax () Empty Read next hostname Script End Try ssh connection Failure Mount NFS on remote machine mountnfs () Perform response file deployment Perform db2_install deployment resp_dply () db2install_dply () Unmount NFS on remote machine umountnfs () Figure 2-7 Logic of the mass deployment script Example 2-18 shows our sample deployment script db2srv_install.
# command-line syntax syntax() { echo " db2srv_install -D|-R -N -H hostA,hostB,...
{ set ${setopts?} ${RCMD} umount "$LOCALNFS" rc=$? # if unmount fails, try once more if [ $rc != 0 ]; then echo "Unmount failed on machine $host. Will try again 2 seconds later." sleep 2 echo "Trying unmount again..." ${RCMD} umount "$LOCALNFS" rc=$ if [ $rc != 0 ]; then echo "Umount failed twice on machine $host. You need to do it manually.
${SILENT:+"-n"}" "${TRC:+"-t $TRC"}` ${RCMD} $LOCALNFS/db2_install $INSTALL_PARAM rc=$? fi fi return $rc } # main program # parse command line options case $# in 0) syntax exit 1;; *) while getopts "RDN:H:r:b:f:p:c:L:l:nt:i:" OPT do case $OPT in R) MOD="R$MOD" ;; D) MOD="D$MOD" ;; # NFS mount path, and host list N) NFSPATH=$OPTARG ;; H) HOSTLIST=`echo "$OPTARG"|sed 's/,/ /g'`;; # command params for response file r) RESFILE=$OPTARG ;; # command params for db2_install b) INSTPATH=$OPTARG ;; f) SAMP=$OPTARG ;;
do # populate command and do basic testing to ensure ssh can work. RCMD="ssh $host" ${RCMD} hostname 2> /dev/null|egrep -i '^'"$host"'$' > /dev/null 2>&1 rc=$? if [ $rc != 0 ]; then # if ssh fails, target host is ignored. echo " ============================ Error: Machine $host could not be connected successfully. Please check. It will be ignored in this deployment.
Option -D or -R determines which deployment method is to be used, D for db2_install and R for response file. Option -N specifies the NFS path which has been exported on the file server machine Zaire. In our case, it is /tmp/v95ga/ese. This directory contains the DB2 server image, db2_install, and db2setup as shown in Example 2-19.
The following example demonstrates the use of the deployment script: db2srv_install -D -N /tmp/v95ga/ese -H baltic,banda -n -p ese -b /opt/IBM/db2/ V9.5 -l /tmp/mass_db2dply.log This command deploys DB2 Enterprise Server Edition to machines Baltic and Banda with installation location /opt/IBM/db2/V9.5 specified. A log file /tmp/ mass_db2dply.log is generated for each machine during the deployment.
For more information see the DB2 installation log at "/tmp/ mass_db2dply.log". --------------Deployment finished on machine banda. Deployment finished. Example 2-22 shows an output of the script performing a DB2 installation using a response file. Example 2-22 Mass deployment using response file mensa:/tmp/tj01 # ./db2srv_install.sh -R -N /tmp/v95ga/ese -H baltic,banda -r db2ese.rsp ============================ Starting deployment on machine baltic using response file...
Example 2-23 DB2 copies existing on baltic # db2ls -------------------------------------------------------------------------/opt/IBM/db2/V9.1 9.1.0.3 3 /opt/IBM/db2/V9.5 9.5.0.1 1 The following command is used to execute the deployment script against Baltic to install DB2 in the /opt/IBM/db2/V9.5 path: db2srv_install.sh -D -N /tmp/v95ga/ese -H baltic -n -p ese -b /opt/ibm/db2/ V9.5 -l /tmp/mass_db2dply.
The general procedure for deploying multiple DB2 servers is as follows: 1. Create a response file: – Modify a sample response file or manually create one. – Use the DB2 Setup wizard to generate a response file. – Use the response file generator (db2rspgn). 2. If the response file was not created using the db2rspgn tool and if you wish to include a database profile in your deployment, generate a DB2 profile using the db2cfexp tool and include the DB2.
The script then performs silent installation using the provided response file and unmaps the DB2 installation image. As mentioned earlier, it might be prudent to leave given mapped drive intact. Execution of this script will result in the output shown in Example 2-26. Example 2-26 Sample Windows deployment script output C:\>dir *.bat Volume in drive C has no label. Volume Serial Number is DCDC-8C9E Directory of C:\ 07/25/2007 06/12/2008 01:17 PM 0 AUTOEXEC.BAT 11:25 AM 209 DB2Deploy.
2.4.1 Fix pack overview A fix pack is a package that contains updates and fixes for reported problems (also known as Authorized Program Analysis Reports, or “APARs”) with the DB2 product. Each fix pack contains an APARLIST.TXT file, which lists the fixes it contains. Fix packs are cumulative. This means that you can just install the latest fix pack, which contains all of the updates from previous fix packs for the same DB2 version.
DB2 fix pack for non-server products: This type of fix pack can only be used if you do not have DB2 server installed, and just other DB2 client or add-on products have to be updated. It cannot be used to update a DB2 server product. DB2 universal fix pack (available only on UNIX and Linux): This fix pack should be used when there are more than one DB2 product installed, for example, if you have DB2 Enterprise Server Edition and WebSphere Federation Server installed.
Deployment method Various methods are available for a fix pack deployment, as summarized here: UNIX and Linux: – Install the fix pack as a new DB2 database product. – Update an existing DB2 database product. Windows: On a Windows platform, only the db2setup installation command is available.
is located. The temporary path for the fix pack image is /tmp/v95fp1/ese as shown in Figure 2-8. It is exported to every other machine using NFS. Zaire NFS Server DB2 image /tm p/v95fp1/ese netw ork Baltic NFS Client Banda NFS Client Figure 2-8 Network topology of mass fix pack deployment Just as for with mass server deployment, you have to ensure that SSH and NFS are installed. For steps to configure SSH and NFS, refer to 2.3.1, “Setup of SSH and NFS” on page 59.
Note: Our deployment script only checks for DB2 instance processes and not all associated DB2 processes. Also, it does not detect shared libraries in the memory. Therefore we recommend that you ensure that all the DB2 processes have been stopped prior to running our script. The fix pack installer installFixPack also detects if all DB2 processes have been stopped. For related information, refer to the section “Stopping all DB2 processes before deployment” on page 77.
In the fix pack deployment script: Option -N specifies the NFS path that has been exported on the file server. In our case, it is /tmp/v95fp1/ese. This directory contains the DB2 fix pack image and installFixPack. Option -H specifies all of the machine names where DB2 fix packs are to be deployed. Each name is separated by a comma (,) with no space between names and before and after the comma. Option -c specifies the location of DB2 National Language Pack.
# -b base_install_path -c image_location # -f level -f db2lib -f NOTSAMP -f install|update # -l log_file -t trace_file # ############################################################## setopts="${setopts:-+x}" set ${setopts?} # clean variables unset CMDOPTS FOPT # command-line syntax syntax() { echo " db2fp_install -N NFSpath -H hostA,hostB,...
umountnfs() { set ${setopts?} ${RCMD} umount "$LOCALNFS" rc=$? # if unmount fails, try once more if [ $rc != 0 ]; then echo "Unmount failed on machine $host. Will try again 2 seconds later." sleep 2 echo "Trying unmount again..." ${RCMD} umount "$LOCALNFS" rc=$ if [ $rc != 0 ]; then echo "Umount failed twice on machine $host. You need to do it manually." fi fi } # check in memory if DB2 instance processes still exist.
return 0 } # deploy DB2 fixpack using installFixPack db2fp_dply() { set ${setopts?} # check if $INSTPATH exists ${RCMD} test -d $INSTPATH rc=$? if [ $rc != 0 ]; then printf "Specified path %s does not exist.\n" $INSTPATH rc=1 else # verify input base path exists in remote machine ${RCMD} "su - root -c db2ls"|egrep '^'"$INSTPATH"' ' > /dev/null 2>&1 rc=$? if [ $rc != 0 ]; then echo "No DB2 installation found under $INSTPATH." echo "This machine is ignored.
f) FOPT="$FOPT""-f $OPTARG " ;; # force options can be combined l) LOGFILE=$OPTARG ;; t) TRC=$OPTARG ;; \?) syntax && exit 1;; esac done ;; esac # set variables BASEHOST=`hostname` LOCALNFS="/db2nfs.$$" BMSG1=" BMSG2=" EMSG1=" Starting fixpack deployment on machine %s using %s...\n" Messages returned from %s:\n ---------------\n" ---------------\n Fixpack deployment finished on machine %s.
fi done echo echo "Deployment finished." We use the following command to start the mass deployment of the DB2 fix pack against the remote machines, Baltic and Banda: db2fp_install -N /tmp/v95fp1/ese -H baltic,banda -b /opt/IBM/db2/V9.5 -c nlpack -l /tmp/db2fp_dply.log Example 2-28 shows the screen output. Example 2-28 Performing the mass deployment of DB2 fix pack # ./db2fp_install -N /tmp/v95fp1/ese -H baltic,banda -b /opt/IBM/db2/V9.5 -c nlpack -l / tmp/db2fp_dply.
On each target machine, you can use the db2ls command to list all of the installed DB2 copies. Example 2-29 shows that our DB2 copy under /opt/IBM/ db2/V9.5 has been upgraded to fix pack 1. Example 2-29 List the installed DB2 copies # db2ls Install Path Level Fix Pack Special Install Number ---------------------------------------------------------------------------/opt/IBM/db2/V9.5 9.5.0.1 1 Chapter 2.
88 DB2 Deployment Guide
3 Chapter 3. DB2 client deployment In this chapter, we discuss various DB2 client deployment methods for various platforms. We discuss the following topics: Client deployment planning IBM Data Server Client, IBM Data Server Runtime Client, and IBM Data Server Driver for ODBC, CLI, and .NET – Client instance on the DB2 server – The db2iprune command line utility – Mass deployment of the IBM data server client product Thin Client deployment © Copyright IBM Corp. 2008. All rights reserved.
3.1 Client deployment planning Client deployment planning is a task to consider at the time when the application is designed and architected. Based on the requirements of the application, you decide on which client to use and how it is configured. The design considerations should include details such as footprint and setup consistency. 3.1.1 Select the right client type Figure 3-1 illustrates this topic as seen from an application point of view.
? Application Application DB2 Server A Driver C Application DB2 Server DB2 Client B Figure 3-1 How to connect our application to the DB2 server 3.1.2 Footprint The size of the footprint can also play a role in the client selection. The footprint increases with the complexity and number of features of the product. The DB2 Data Server Driver has the smallest footprint, and the DB2 Data Server Client has the largest footprint. In between is the DB2 Data Server Runtime Client.
A smaller install image with only the required features and language options can be obtained by using the command line tool db2iprune. This tool creates a new and tailored install image based on the input provided. We show how to use db2iprune in 3.2.3, “Reducing the installation image” on page 97. Note: db2iprune is a command line utility for Windows only. The feature is not available on Linux and UNIX. 3.1.
To read more about db2cli.ini and keyword option, use the DB2 Information Center: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv .cli.doc/doc/c0007882.html Note: The initialization file is NOT a part of the configuration profile. You have to handle them separately. 3.1.5 Compatibility You also have to consider compatibility issues in the deployment planning.
3.1.6 Licensing The IBM Data Server Runtime Client and the IBM Data Server Driver can be distributed without any licensing. These products are free, while the IBM Data Server Driver require a license. 3.1.7 How to deploy the DB2 client Another important decision is how you want to deploy the DB2 client. There are several methods we can choose from, and each of them has its pros and cons. Each methods is described thoroughly in this chapter. 3.
For further details regarding installing multiple DB2 copies, refer to the following URLs in the DB2 Information Center: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.d b2.luw.admin.dbobj.doc/doc/r0024057.html http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.d b2.luw.qb.client.doc/doc/t0007315.html The default copy name of the Data Server Driver for ODBC, CLI, and .
DB2 Setup wizard The DB2 Setup wizard can be executed in a language other than the default system language by manually invoking the DB2 Setup wizard and specifying a language code. For example, the setup –i fr command runs the DB2 Setup wizard in French on Windows (the db2setup –i fr command runs the DB2 Setup wizard in French in Linux and UNIX). For the Data Server Runtime Client or Data Server Driver for ODBC, CLI, and .NET, there are separate install images for each language.
Note: After deployment, bind the CLI and DB2 utility package using the following commands: db2 bind @db2cli.lst blocking all grant public db2 bind @db2ubind.lst blocking all grant public Bind has to be done only once from any one client of the same DB2 level. You do not have to perform bind with every client (of the same DB2 level) to a server during deployment. 3.2.
-p Specifies the full path to the root directory of the source installation image. This directory contains setup.exe, and is the root directory of the DB2 installation DVD. -o Specifies the full path to where the new DB2 pruned image is copied. Make sure that you have write access to this directory. The input file for db2iprune contains a full list of removable features and has the .prn extension.
3. Use any of the installation methods discussed earlier to install and maintain a pruned DB2 installation image. Considerations for a pruned DB2 installation Consider the following precautions if you plan to prune a DB2 installation: DB Setup wizard installation: – With the TYPICAL installation, the regular TYPICAL components for that product are installed without the components removed by the db2iprune command.
3.2.4 Mass deployment of IBM data server client product We have discussed general deployment methods for the DB2 server product in 2.2, “DB2 server deployment methods” on page 42 and these methods are also applicable to the IBM data server client. The general types of deployment methods involve either push deployment or pull deployment.
For further details regarding Microsoft’s Systems Management Server, refer to the following URL: http://www.microsoft.com/technet/downloads/sms.mspx To install DB2 products using SMS/SCCM, perform these steps: 1. Import the DB2 install file into SMS/SCCM. 2. Create the SMS/SCCM package on the SMS/SCCM server. 3. Distribute the DB2 installation package across your network.
6. Click Next to continue from the window, Welcome to the Create Package from Definition Wizard, as shown in Figure 3-3. Figure 3-3 Welcome screen 7. On the window, Create Package from Definition Wizard (Figure 3-4), click Browse to search for the DB2 package definition file (.pdf).
8. Select the appropriate .pdf file from the DB2 installation image. These images are located in the db2\Windows\samples\ path. In this example we are installing the DB2 Data Server Client. Select db2client.pdf as shown in Figure 3-5, then click Open. Figure 3-5 Selecting DB2 package definition 9. You will be returned to the window, Create Package from Definition Wizard. The DB2 product you have selected should now appear in the Package definition box.
10.From the Source Files window, select Create a compressed version of the source as shown in Figure 3-7. The default is “This package does not contain any files”. Click Next to continue. Figure 3-7 Source Files window 11.From the Source Directory window, you can either specify a local drive or a network path for the IBM Data Server client installation image. In our case, it is located in the local drive.
12.From the window, Create Package from Definition Wizard, select Finish to complete the Create Package from Definition Wizard as shown in Figure 3-9. Figure 3-9 Completing the Create Package from Definition Wizard 13.You will be returned to the window, Create Package from Definition Wizard, as shown in Figure 3-10. Figure 3-10 Create Package from Definition Wizard Chapter 3.
Distributing DB2 installation packages to clients using the Microsoft SMS Once the DB2 installation package has been created in Microsoft SMS, it is now available for distribution across your SMS network. Next we list the general steps for distributing the DB2 installation package across a SMS network: 1. From the window, Create Package from Definition Wizard, right-click Packages and select All Tasks → Distribute Software as shown in Figure 3-11.
2. Click Next on the window, Welcome to the Distribute Software Wizard, as shown in Figure 3-12, to proceed. Figure 3-12 Welcome to the Distribute Software Wizard 3. Select the radio button, Select an existing package, and select the DB2 product you want to deploy from the Packages window as shown in Figure 3-13. Click Next to continue. Figure 3-13 Selecting an existing package for distribution Chapter 3.
4. Select the distribution points for the DB2 package from the Distribution Software Wizard. All available distribution points will be shown in the panel. We have chosen only distribution point LEAD as shown in Figure 3-14. Click Next to continue. Figure 3-14 Selecting distribution points 5. Ensure that the radio button for advertising a program is set to Yes in the Advertise a Program window as shown in Figure 3-15. This should be the default. Click Next to continue.
6. Select a program to be advertised to members of SMS distribution from the Select a Program to Advertise window as shown in Figure 3-16. Click Next to continue. Figure 3-16 Select a Program to Advertise window 7. Select either an existing collection of machines where you want to advertise and install the DB2 program selected, or create a new collection of machines as shown in Figure 3-17. Click Next to continue. Figure 3-17 Advertisement Target window Chapter 3.
8. You can specify name to identify the advertisement in the Name field and add an optional comment from the Advertisement Name window as shown in Figure 3-18. Click Next to continue. Figure 3-18 Advertisement Name window 9. Select whether the advertisement should apply to subcollections or not from the Advertise to Subcollections window as shown in Figure 3-19. Click Next to continue.
10.Specify when you want the program to be advertised and installed, on the Advertisement Schedule window, as shown in Figure 3-20. Click Next to continue. Figure 3-20 Advertisement Schedule window 11.Specify whether this program deployment should be mandatory for your SMS clients or not, on the Assign Program window, as shown in Figure 3-21. Click Next to continue. Figure 3-21 Assign Program window Chapter 3.
12.Now the window, Completing the Distribute Package Wizard, will appear and you can click Finish to advertise the program to SMS Clients (Figure 3-22). Figure 3-22 Completing the Distribute Software Wizard window Packaging IBM data server client product using Microsoft SCCM Perform the following steps to import DB2 install files, and package the IBM data server client product using Microsoft System Center Configuration Manager 2007: 1.
Figure 3-23 Configuration Manager Console 6. Click Next to continue from the window, Welcome to the Create Package from Definition Wizard (Figure 3-24). Chapter 3.
Figure 3-24 Welcome to the Create Package from Definition Wizard window 7. From the window, Create Package from Definition (Figure 3-25), click Browse to search for the DB2 package definition file. Figure 3-25 Create Package from Definition Wizard window 8. Select the appropriate .pdf file from the DB2 installation image. These images are located in the db2\Windows\Samples\ path.
In this example we are installing DB2 Data Server Client. Select db2client.pdf as shown in Figure 3-26, then click Open. Figure 3-26 Selecting.pdf file 9. You will be returned to the window, Create Package from Definition Wizard. The DB2 product you have selected should now appear in the Package definition box. In our scenario “IBM Data Server Client” is shown in the Package definition box as in Figure 3-27. Click Next to continue. Figure 3-27 Create Package from Definition Wizard window Chapter 3.
10.From the Source Files window, select Create a compressed version of the source files as shown in Figure 3-28. Click Next to continue.
11.From the Source Directory window, you can either specify a local drive or a network path for the IBM Data Server Client installation image. In our case, it is located in the local drive. Click the Browse button to find the image or specify the IBM Data Server Client installation image path as shown in Figure 3-29. Click Next to continue. Figure 3-29 Source Directory window Chapter 3.
12.From the window, Create Package from Definition Wizard, select Finish to complete the Create Package from Definition Wizard as shown in Figure 3-30.
13.You will be returned to the window, Create Package from Definition Wizard (Figure 3-31). Figure 3-31 Create Package from Definition Wizard window Distributing DB2 install packages using the Microsoft SCCM After the DB2 installation package has been created in Microsoft SCCM, it is now available for distribution across your SCCM network. Here we list the general steps for distributing the DB2 installation package across an SCCM network: 1.
Figure 3-32 Starting software distribution 2. Click Next on the window, Welcome to the Distribute Software Wizard, as shown in Figure 3-33, to proceed. Figure 3-33 Welcome to the Distribute Software Wizard window 3. Select the radio button, Select an existing package, and select the DB2 product you want to deploy from the Packages window, as shown in Figure 3-34. Click Next to continue.
Figure 3-34 Packages window 4. Select the distribution points for the DB2 package from the Distribution Software Wizard. We have chosen only the distribution point LOCHNESE as shown in Figure 3-35. Click Next to continue. Figure 3-35 Distribution Points window Chapter 3.
5. Ensure that the radio button for Yes is selected for the advertising program for the package listed on the Advertise Program window, as shown in Figure 3-36. This should be the default. Click Next to continue.
6. Select the Setup program to be advertised to members of the SCCM distribution from the window, Select a Program to Advertise, as shown in Figure 3-37. Click Next to continue. Figure 3-37 Select a Program to Advertise window Chapter 3.
7. Select either an existing collection of machines where you want to advertise and install the DB2 program selected, or create a new collection of machines as shown in Figure 3-38. Click Next to continue.
8. You can specify a name to identify the advertisement in the Name field and add an optional comment from the Advertisement Name window, as shown in Figure 3-39. Click Next to continue. Figure 3-39 Advertisement Name window Chapter 3.
9. Select whether the advertisement should apply to subcollections or not from the window, Advertise to Subcollections, as shown in Figure 3-40. Click Next to continue.
10.Specify when you want the program to be advertised and installed on the window, Advertisement Schedule, as shown in Figure 3-41. Click Next to continue. Figure 3-41 Advertisement Schedule window Chapter 3.
11.Specify whether this program deployment should be mandatory for your SCCM clients or not on the window, Assign Program, as shown in Figure 3-42. Click Next to continue.
12.Now the window, Completing the Distribute Package Wizard, will be displayed. You can click Finish to advertise the program to SCCM Clients. See Figure 3-43. Figure 3-43 Distribute Package Wizard window After clicking the Next button, you should see the message, The Distribute Software Wizard completed successfully, as shown in Figure 3-44. Chapter 3.
Figure 3-44 Completion of Distribute Software Wizard IBM data server client deployment on Linux and UNIX A common method for deploying the IBM data server client is through use of a script, which is executed on the target machine. This is referred to as push deployment and has been discussed in 2.2, “DB2 server deployment methods” on page 42.
3.3 Thin Client deployment The Thin Client is an alternative method for leveraging an IBM data server client, which is available for the Windows 32-bit environment. The thin client topology involves the IBM data server client code being installed on the code server, rather than on each client workstation. Each thin client workstation only has a minimal amount of code and configuration is required. In a thin client, IBM data server client code is dynamically loaded from the code server as required.
Installing IBM Data Server Client or DB2 Connect Personal Edition on the code server The installation steps for installing IBM Data Server client or DB2 Connect Personal Edition on the code server is similar to installing any other DB2 product. However, you must select Custom installation type and from the Select the Features to install Windows, select Server Support, and then select Thin Client Code server as shown in Figure 3-45.
4. Click the Sharing tab. 5. Click the radio button, Share this folder. 6. In the Share Name field, enter a share name that is eight characters or fewer. For example, enter NTCODESV. 7. Provide read access to the code directory to all thin client users: a. Click Permissions. The Share Permissions window opens. b. In the Group or Users Name list, highlight the Everyone group.
Mapping a network drive from each thin client to the code server To map a network drive from the thin client, perform the following steps: 1. Launch Windows Explorer. 2. On the Tools menu, click Map Network Drive. 3. In the Drive list, select the drive to which you want to map the location of the code server. 4. In the Folder field, specify the location of the share as follows: \\computer_name\share_name Where: computer_name represents the computer name of the code server.
/S Specifies the share name of the code server where you installed the DB2 product. This parameter is necessary only if you did not map a persistent network drive. This parameter is mandatory on Windows XP and Windows Server 2003 operating systems. Here is a sample command for thnsetup: x:\thnsetup\thnsetup /P x: /U x:\thnsetup\test.rsp /M machineName Upon completion of the thnsetup command, the db2.
136 DB2 Deployment Guide
4 Chapter 4. Deploying applications with DB2 In this chapter, we discuss deploying various applications with DB2 drivers. We introduce deployment on Java, C/C++, PHP, Ruby, Python, Perl, and .NET. We focus on how to access a DB2 database from various applications without installing a full DB2 Client. We introduce DB2 products that facilitate this functionality and ways to deploy them with your application. © Copyright IBM Corp. 2008. All rights reserved.
4.1 Introduction to application deployment package The application deployment package, in the context of this book, consists of your application and a DB2 product that is required to access DB2 databases. It is used to facilitate the deployment process of your application with a DB2 product. DB2 offers various products that can be used to create the application deployment package. Many of these products can be re-distributed with your application.
IBM Data Server Driver for JDBC and SQLJ license files for connection to DB2 for z/OS and DB2 for i5/OS: License file db2jcc_license_cisuz.jar can be found in the sqllib\java directory for Windows systems, or the sqllib/java directory for UNIX or Linux systems. These files have to be included in the CLASSPATH. Note: License files are not required for connection to the DB2 Database Server for Linux, UNIX, and Windows.
Installation procedure You can obtain the IBM Data Server Driver for JDBC and SQLJ from the DB2 product CD or download it from the IBM Web site at: http://www.ibm.com/software/data/db2/ad/deploy.html Note: If you want to use Type 2 connectivity with your application, DB2 Data Server Runtime client or another full DB2 product will be required. Remember that this downloaded driver can be used for Type 4 mode only. To install the downloaded driver on the client machine: 1.
sqlj.zip and sqlj4.zip: These file contain classes that are required to prepare SQLJ applications for execution under the IBM Data Server Driver for JDBC and SQLJ. DB2 includes sqlj.zip in the CLASSPATH during the installation process. This file is for applications that use JDBC 3.0 and earlier functions. You have to manually include sqlj4.zip in the CLASSPATH to use JDBC 4.0 and earlier functions. Do not include both.
b. Update the database manager configuration file with the appropriate TCP/IP service name. The service name is specified in the services file located at /etc/services on Linux and UNIX systems and at c:\WINDOWS\system32\drivers\etc\services on Windows systems. update dbm cfg using SVCENAME TCP/IP-service-name Note: The port number used for applets and SQLJ programs must be the same as the TCP/IP SVCENAME number used in the database configuration file. c.
You might want to redirect the output to a file for easier viewing. The JDK_PATH field appears near the beginning of the output. Date and time format: If you plan to call the SQL procedures that are on the DB2 servers from a Java program, and the date and time format that is associated with the territory code of the database servers is not the USA format, take the following actions: a.
Apart from runtime support for DB2 CLI API and ODBC API, this driver offers runtime support for Transaction API. It also provides database connectivity, Lightweight Directory Access Protocol (LDAP) Database Directory support, as well as tracing, logging, and diagnostic support. IBM Data Server Driver for ODBC and CLI and IBM Data Server Driver for ODBC, CLI, and .NET are provided to facilitate DB2 application deployment.
Download DB2 Driver for ODBC and CLI for Linux and UNIX from the following IBM Web site: https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg -idsdoc11 Download DB2 Driver for ODBC, CLI, and .NET for Windows from the following IBM Web site: https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg -swg-idsdocn11 Note: For Windows, you can also obtain IBM Data Server Driver for ODBC, CLI, and .
Installing IBM Data Server Driver for ODBC and CLI To install IBM Data Server Client for ODBC and CLI, perform these steps: 1. Uncompress the compressed file to a directory on the target machine. 2. Create the directory $HOME/db2_cli_odbc_driver, where the driver will be installed. 3. Untar the tar archive with directory structure preserved using the -xvf option. 4. Ensure that db2dump and the db2 directories are writable.
Configuring IBM Data Server Driver for ODBC and CLI You must configure the driver and application runtime environment before your application can use the driver successfully. Perform the following steps: Configure aspects of driver behavior such as data source name, performance options, connection options, and user name by updating the db2cli.ini initialization file. There is no support for CLP, you must update db2cli.ini file manually.
Type of variable Variable names Miscellaneous variables DB2CLIINIPATH DB2_ENABLE_LDAP DB2LDAP_BASEDN DB2LDAP_CLIENT_PROVIDER DB2LDAPHOST DB2LDAP_KEEP_CONNECTION DB2LDAP_SEARCH_SCOPE DB2NOEXITLIST Diagnostic variables DB2_DIAGPATH Connection variables AUTHENTICATION PROTOCOL PWDPLUGIN KRBPLUGIN ALTHOSTNAME ALTPORT INSTANCE BIDI – Set the DB2_CLI_DRIVER_INSTALL_PATH: Set the DB2 environment variable DB2_CLI_DRIVER_INSTALL_PATH to the directory where the driver is installed: export DB2_CLI_DRIVER_INSTA
– Set the system environment variables: Set the system environment variable LIBPATH on AIX or LD_LIBRARY_PATH on the Linux, UNIX, and Windows systems to the directory where the driver is installed.
Register with Microsoft driver manager: If your ODBC applications use Microsoft ODBC driver manager, you must register the driver with Microsoft driver manager. Run the db2oreg1.exe utility: db2oreg1.exe -i License requirements IBM Data Server Driver for ODBC and CLI and IBM Data Server Driver for ODBC, CLI, and .NET can be redistributed with your application.
To access DB2, Java uses either dynamic SQL or static SQL. Dynamic SQL is supported by the JDBC standard, Java database connectivity, which basically is a Java implementation of ODBC. Static SQL is supported by SQLJ. While JDBC is an open standard that can interact with most database management systems, SQLJ is specific to DB2.
Deployment procedure for a Java application The procedure for deploying a Java application is simple and straightforward. These are the steps involved: 1. Package the application along with the IBM Data Server Driver. Include the Java runtime environment if required. 2. Copy the files to the target environment. 3. Make sure that the path and classpath are set up correctly.
Example 4-3 The command file, jmigrate.cmd, used to start the Java application // Step 1 : Setting up the path setlocal set path=jre1.6.0_05\bin // Step 2 : Start the application java -cp "itso.jar;db2\db2jcc.jar" ibm.itso.sg247653.MigrateExecuter %1 %2 compare %3 %4 Step 1: Setting up the path: We set the path variable to point to the directory where the Java runtime environment is located. The initial line setlocal is just for ensuring that any settings within the command file are local to this file.
4.3 Deploying C/C++ applications C and C++ are probably two of the most popular and well-known programming languages. They are general purpose programming languages developed in the 1970’s. The prevalence of these two languages still continues today on almost all operating systems even though Java and other languages have achieved great success. DB2 provides various sets of APIs for C/C++ programmer for their choices.
DB2 CLI. In addition, some DB2 specific extensions have been added to DB2 CLI to facilitate programming with DB2 features. It conforms to ODBC 3.51. Comparison of CLI and ODBC The roles played by the DB2 CLI and ODBC driver might change due to the differences in application environments. Figure 4-1 illustrates the roles played by DB2 CLI and ODBC driver in different environments.
When DB2 CLI driver works without the ODBC driver manager, it supports a subset of the functions provided by the ODBC driver. For a detailed description of the supported functions, visit the DB2 Information Center: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv .cli.doc/doc/c0000670.html The name of the isolation levels in a DB2 environment differs from the ODBC specifications. Table 4-4 maps the isolation levels in DB2 to ODBC.
Note: A DSN-less connection means connecting to a data source without a system level DSN or file DSN created. The connection string is used instead of a system level DSN or file DSN to keep the data source connectivity information. The CLI function SQLConnect() does not support a connection string. That is why we use SQLDriverConnect() in our sample application. An introduction to the three types of connection functions in CLI programming can be found at the DB2 Information center: http://publib.boulder.
4.3.3 Considerations for deployment of CLI and ODBC applications For scenarios where the IBM Data Server Client or Runtime Client has already been installed, deploying a C/C++ application is straightforward. You only have to install the application on the target system and then catalog locally the database to which the application is going to connect. During runtime, application directly reads connectivity information from the local catalog.
For a complete introduction to these connectivity configurations, visit the DB2 Information Center: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv .cli.doc/doc/t0024166.html 4.3.4 Deploying a CLI application along with ODBC CLI driver The general tasks for deploying a CLI application with the ODBC CLI driver are as follows: Prepare the redistributable driver files. Prepare the application executable file. Prepare the deployment package.
3. Ensure that the ODBC CLI driver file is ready. It could be downloaded from the IBM Web site. See also 4.1.2, “IBM Data Server Driver for ODBC, CLI, and .NET, and IBM Data Server Driver for ODBC and CLI” on page 143. Grant the necessary read permission to the driver file and ensure that the user account you are using can access it without any problems. 4. Create a script file named bldpkg under the working folder. It is used to help us build the package. Example 4-6 contains the complete coding.
r) REDIS_LIST=$OPTARG ;; o) OUTPUT_FILE=$OPTARG ;; ?) echo "invalid command line option $*" syntax exit 1;; esac done ;; esac # temp file FLIST="filelist.$$" # build command line for different file format echo $CLIDRV_FILE|egrep -i '\.gz *$' > /dev/null if [ "$?" == 0 ]; then # for .tar.gz CMD="gunzip -c $CLIDRV_FILE" else echo $CLIDRV_FILE|egrep -i '\.Z *$' > /dev/null if [ "$?" == 0 ]; then # for .tar.
There are three command line options for the script. – Option -d specifies the path of the DB2 Driver file for ODBC and CLI. It normally has a suffix tar.gz or tar.Z. – Option -r specifies the file name of the redistributable file list. In our case, it is redist.txt. – Option -o specifies the name of the output file. 5. In Example 4-7 we execute the script to generate a redistributable package. We issue it from the working folder followed with the file name of the ODBC CLI driver, redist.
Another approach is to compile and link manually from the command line. The options used for compile and link vary depending on the platforms. A complete reference can be found at: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv .cli.doc/doc/t0007141.html In our demonstration, we use the manual method and build the application from the command line. We use gcc, which is a popular compiler on many platforms and sometimes is included in the default installation.
Example 4-10 A failed connect test using itso_cliapp db2inst1@mensa:~/work> ./itso_cliapp mensa 50000 itso db2inst1 wrongassword Connecting to the database itso ... Failed to connect to the database itso. SQLSTATE = 08001 SQLCODE = -30082 Message: [IBM][CLI Driver] SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID").
4. The purpose of the script app_install is to copy an application file and extract CLI driver files to the specified path, and to perform application environment configurations after the deployment. Example 4-12 shows the script code. The script app_install has two command line options: – The option -p specifies the path on the target machine indicating where the application is to be deployed. – The option -r indicates that we want the script to configure the system variables for us.
case $OPT in p) INSTPATH=$OPTARG mkdir -p $INSTPATH ;; r) REGVAR=Y ;; ?) echo "invalid command line option $*" syntax exit 1 ;; esac done ;; esac # verify the ODBC driver files and application files are ready # and then start the deployment TESTPATH=`echo $0|egrep '^/'` if [ -z $TESTPATH ]; then dirname `pwd`/$0|read CURPATH else CURPATH=`dirname $0` fi cd $CURPATH if [ ! -d $DIR_DRV ] || [ ! -d $DIR_APP ]; then echo " ODBC CLI driver or Application directory not existing.\n Abort.
echo " System variables registered. Please re-login to have the settings be effective." ;; *) echo " You choose not registering system variable. It could be finished later by adding following lines to your user profile: export LIBPATH=$LIBPATH:$ODBCLIBPATH export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ODBCLIBPATH" if [ `uname` == "AIX" ]; then echo " export DB2_CLI_DRIVER_INSTALL_PATH=$DB2_CLI_DRIVER_INSTALL_PATH:$ODBCLIBPATH" fi ;; esac printf "\n Deployment finished.\n" 5.
In our example, there is no special configuration required for our application. Therefore, no application specific configuration steps are included in the deployment script. We move the application package app_installer.tar.gz to target machine lepus using scp. You can choose another method you prefer, such as ftp. Then we deploy it using a local user prodapp as shown in Example 4-14.
Example 4-15 Verify the connection after deployment prodapp@lepus:~> cd /home/prodapp/cliapp/bin prodapp@lepus:~/cliapp/bin> ./itso_cliapp mensa 50000 itso db2inst1 password Connecting to database itso ... Connected to database itso. Disconnecting from database itso... 4.3.5 Embedded SQL and Administrative API For embedded SQL applications that must have the target database registered in catalog directory, IBM Data Server Client, or IBM Data Server Runtime Client is required.
Object handling was re-written in PHP version 5, and from version 5 onwards, PHP also supports various OOP components, such as interfaces, abstract classes. There is one such interface named PHP Data Objects (PDO), which defines a lightweight abstraction layer to access databases from PHP. So, you can use the same methods and functions to query and fetch data from all databases that implement the PDO specification. IBM provides two drivers, PDO_IBM and IBM_DB2, for you to access IBM DB2 databases. 4.4.
Prerequisites Before installing IBM_DB2 or PDO_IBM, you will require the following software. 4.4.4 PHP PHP version 5 or greater is required for PDO_IBM and PHP version 4 or greater is required for IBM_DB2. Most Linux and UNIX systems come with PHP installed, but you might want to install a local copy of PHP on which you have full control. You can download the latest PHP from: http://www.php.net/downloads.php For Windows, the binaries are also available on the same page.
In our example, we place these files in the directory /home/user1/phpdrivers. 2. Create php.ini file: Change to the lib directory where the PHP is installed. For example: cd /opt/www/php/lib Create a php.ini file if there is not one already present: touch php.ini vi php.ini 3. Add extension path and extension name in the php.ini file: Example 4-16 shows the lines that have to be added to the php.ini file. Add only the extension you will use. For example, if you are going to use ibm_db2_xx.
The PHP drivers are open source for you to build and install. The building DB2 PHP drivers will generate ibm_db2.so and pdo_ibm.so files. Once these files are generated, you can follow steps 2 to 4 to install drivers. The download sites and driver built instructions can be found in the following URLs: Download the source for IBM_DB2: http://pecl.php.net/package/ibm_db2 Download the source for PDO_IBM from: http://pecl.php.net/package/pdo_ibm Build instructions: http://in2.php.net/manual/en/install.
This displays all extensions of PHP dynamically. Check for ibm_db2 and pdo_ibm in the output. Second way: For the Windows environment, you can also download the latest driver files (with extension dll) from the Internet: Download latest IBM_DB2 from: http://pecl4win.php.net/ext.php/php_ibm_db2.dll Download latest PDO_IBM from: http://pecl4win.php.net/ext.php/php_pdo_ibm.dll Once you have finished downloading DB2 PHP drivers, copy them to the PHP extension directory and update the php.
In order to run the application, create a directory and save the application as itso_phpapp.php. Example 4-18 shows how to run the application in detail. The example demonstrates a successful connection to a remote uncataloged database. Example 4-18 A successful connection using itso_phpapp.php itsouser@ubuntu:~/redbook$ php itso_phpapp.php mensa 50001 test db2inst1 password Trying to establish connection... Connection succeeded. Closing connection.. Connection closed.
Preparing DB2 PHP driver and redistributable DB2 ODBC and CLI driver files You can obtain the DB2 PHP driver file from any of the method described in “Installation procedure on Linux and UNIX” on page 171. For this example, we have built the ibm_db2 driver and taken the driver file named ibm_db2.so. In the target system where the PHP has been installed, you can just register the IBM PHP driver. We place the PHP driver file ibm_db2.so under php_deploy/phpdriver.
# -r specify to configure system variable for ODBC and CLI driver # # example: php_app_install -p /home/db2app/myapp -r" # ########################################################################## #set -x # Define variables DIR_DRV=odbcdrv DIR_APP=bin DIR_PHP=phpdriver unset REGVAR # directory for odbc and cli driver files # directory for applications # directory for driver file # command-line syntax syntax() { echo " php_app_install -p -r -p -r specify the location where application and O
cd $CURPATH if [ ! -d $DIR_DRV ] || [ ! -d $DIR_APP ] || [ ! -d $DIR_PHP ] ; then echo " ODBC CLI driver, Application or driver directory not existing.\n Abort." exit 1 fi # deploy application and php driver cd $INSTPATH cp -R "$CURPATH/$DIR_APP" . cp -R "$CURPATH/$DIR_PHP" . # Install the php driver PHPPATH=`which php` FILENAME=`dirname $PHPPATH|sed 's/\/[^/]*$//'`"/lib/php.ini" echo "extension_dir="$INSTPATH/$DIR_PHP >> $FILENAME echo "extension=ibm_db2.
fi ;; esac printf "\n Deployment finished.\n" We placed the deployment script under our deployment directory /php_deploy. Example 4-21 shows the contents of the php_deploy directory that now has all the files required for the deployment. Example 4-21 Contents of php_deploy directory itsouser@ubuntu:~/php_deploy$ ls -Rl .
Deploying the DB2 PHP application The deployment process for the DB2 PHP application involves transfer of the deployment package to the target system, uncompressing/extracting files in the package, and then executing the deployment script. See Example 4-23 for the steps involved in DB2 PHP application deployment. Example 4-23 Deploying using script php_app_install $ whoami php_dep $ gunzip -c php_app_installer.tar.
4.5 Ruby Ruby is a dynamic, general purpose, object-oriented scripting language that has Perl-like syntax and Smalltalk-like features. The style of programming is similar to Lisp, Perl, Python, CLU, and Dylan. Ruby supports different programming paradigms such as procedural, reflection, and object-oriented. Ruby on Rails (RoR), also known as Rails, is a Web application development framework written in Ruby.
Uncompress the download file, change to the newly created directory, and run the following command to install it on Linux, UNIX, and Windows: ruby setup.rb Rails: You can install Rails from the command line if your system has Internet access by using the following command on Linux, UNIX, and Windows: gem install rails --include-dependencies Otherwise, download the latest Rails from: http://rubyforge.org/frs/?group_id=307 If you are installing Rails manually, install activerecord as well.
4.5.3 Creating a sample Ruby application In order to demonstrate Ruby application deployment, we have a sample application named itso_rubyapp.rb that reads the connectivity information from the command line and establishes connection to a database. The database can be both local or remote. If the connection fails, the application returns an error message. The complete code is shown in A.3, “Ruby” on page 263 and is available for download.
Preparing IBM_DB gem and redistributable DB2 ODBC and CLI driver files You can obtain the IBM_DB2 gem file from any of the methods described in “Installation procedure for Linux, UNIX, and Windows” on page 182. For this example, we have taken the gem file named ibm_db-0.9.0.gem from IBM Data Server Client. We have assumed that Ruby, RubyGems, and Rails are installed on the target system and the user has sufficient read and write permissions to install IBM_DB gem.
# ########################################################################## #set -x # Define variables DIR_DRV=odbcdrv # directory for odbc and cli driver files DIR_APP=bin # directory for applications DIR_GEM=gem # directory for gem file unset REGVAR # command-line syntax syntax() { echo " ruby_app_install -p -r -p -r specify the location where application and ODBC lib files will be deployed specify to configure system variable for ODBC and CLI driver example: ruby_app_install -p /home/db2
echo " ODBC CLI driver, Application, or gem directory not existing.\n Abort." exit 1 fi # deploy applications and ODBC CLI driver to specified path cd $INSTPATH cp -R "$CURPATH/$DIR_APP" . # Install the driver. cd $CURPATH/$DIR_GEM gem install ibm_db --ignore-dependencies cd $INSTPATH mkdir -p "$DIR_DRV" cd $DIR_DRV for file in "$CURPATH/$DIR_DRV/*.tar.
We placed the deployment script under our deployment directory /ruby_deploy. Example 4-28 shows the contents of the ruby_deploy directory, which now has all the files for deployment. Example 4-28 Contents of ruby_deployment directory itsouser@ubuntu:~/ruby_deploy$ .
Example 4-30 Deploying using script ruby_app_install $ whoami ruby_dep $ gunzip -c ruby_app_installer.tar.gz |tar -xf $ ls -l total 7480 drwxrwxr-x 2 ruby_dep test01 4096 2008-06-27 20:41 drwxrwxr-x 3 ruby_dep test01 4096 2008-06-27 21:33 drwxrwxr-x 2 ruby_dep test01 4096 2008-06-27 20:41 -rwxr-xr-x 1 ruby_dep test01 2973 2008-06-27 14:21 -rw-rw-r-- 1 ruby_dep test01 7630289 2008-06-27 21:31 $ ./ruby_app_install -p /home/ruby_dep/ruby_app -r Successfully installed ibm_db-0.9.
4.6 Python Python (also known as CPython) is a general purpose, high level scripting language well suited for rapid application development. It is influenced by languages such as C, Perl, Haskell, and Java. Python supports different programming paradigms such as procedural, object-oriented, aspect-oriented, metaprogramming and functional programming. SQLAlchemy is an open source Python SQL toolkit and object-relational mapper (ORM) that gives application developer the full flexibility and power of SQL.
4.6.3 IBM_DB_SA adaptor IBM_DB_SA is an adaptor IBM provides for SQLAlchemy. The IBM_DB_SA adapter provides SQLAlchemy interface to IBM Data Servers that conforms to the SQL Alchemy 0.4.0 specification. IBM_DB_SA internally calls IBM_DB_DBI to connect to DB2 databases. This also is an open source product and is written in Python. 4.6.4 Installation of IBM Python drivers You can install both IBM_DB driver and IBM_DB_DBI wrapper simultaneously in one process. You can install IBM_DB_SA adaptor separately.
Note: On Linux and UNIX systems, setuptools has dependencies on the zlib-bin and zlib1g-dev packages. These are generally installed on systems; so ensure that you have them installed. Otherwise, install them using the following commands: sudo apt-get install zlib-bin sudo apt-get install zlib1g-dev You can also search for these packages and install them manually. IBM Data Server Driver for CLI support: All Python extensions communicate to DB2 using CLI.
Note: Internet access is required for this method of installation to work. You can also download the appropriate eggs from: http://code.google.com/p/ibm-db/downloads/list Copy these to your system, change to the directory where you have copied the egg, and issue the following command to install: easy_install You can download the latest source code of IBM_DB driver and IBM_DB_DBI wrapper from: http://pypi.python.
Closing connection... Connection closed. If there is any incorrect information, the connection will fail and an error message is returned as shown in Example 4-33. Example 4-33 Failed connection using itso_pyapp.py itsouser@ubuntu:~/redbook$ python itso_pyapp.py mensa 50001 test db2inst1 wrongpassword Trying to establish connection... Traceback (most recent call last): File "itso_pyapp.py", line 38, in main(sys.argv[1:]) File "itso_pyapp.py", line 31, in main conn = ibm_db.
We have assumed that Python is installed on the deployment machine, and that the user has sufficient read and write permissions to install setuptools and driver eggs. We place the egg files setuptools-0.6c7-py2.5.egg and ibm_db-0.2.9-py2.5-linux-x86_64.egg under python_deploy/gem. You also have to prepare the DB2 driver files. We discuss how to prepare the redistributable driver files in “Preparing the redistributable driver files” on page 159.
DIR_EGG=egg unset REGVAR # directory for egg file # command-line syntax syntax() { echo " py_app_install -p -r -p -r specify the location where application and ODBC lib files will be deployed specify to configure system variable for ODBC and CLI driver example: py_app_install -p /home/db2app/myapp -r" } # main program # process command-line options case $# in 0) syntax exit 1;; *) while getopts "p:r" OPT; do case $OPT in p) INSTPATH=$OPTARG mkdir -p $INSTPATH ;; r) REGVAR=Y ;; ?) echo "inv
# Install the setuptools and egg. cd $CURPATH/$DIR_EGG sh setuptools*.egg easy_install ibm_db*.egg cd $INSTPATH mkdir -p "$DIR_DRV" cd $DIR_DRV for file in "$CURPATH/$DIR_DRV/*.tar.gz" do gunzip -c $file|tar -xf done # register system variable ODBCLIBPATH=`find $INSTPATH/$DIR_DRV -type d -name lib` case $REGVAR in Y) echo " # The following lines have been added by py_app_install script export LIBPATH=$LIBPATH:$ODBCLIBPATH export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ODBCLIBPATH " >> ~/.
Example 4-35 Contents of python_deploy directory itsouser@ubuntu:~/python_deploy$ ls .: total 16 drwxr-xr-x 2 itsouser itsouser 4096 drwxr-xr-x 2 itsouser itsouser 4096 drwxr-xr-x 2 itsouser itsouser 4096 -rwxr-xr-x 1 itsouser itsouser 2978 -Rl 2008-06-28 2008-06-28 2008-06-28 2008-06-28 17:52 21:29 17:52 11:25 bin egg odbcdrv py_app_install ./bin: total 4 -rw-r--r-- 1 itsouser itsouser 1164 2008-06-28 17:52 itso_pyapp.py ./egg: total 716 -rw-rw-r-- 1 itsouser itsouser 400192 2008-06-28 21:29 ibm_db-0.
Example 4-37 Deploying using script py_app_install $ whoami python_dep $ gunzip -c py_app_installer.tar.gz | tar -xf $ ls -l total 7872 drwxr-xr-x 2 python_dep test01 4096 2008-06-28 17:52 bin drwxr-xr-x 2 python_dep test01 4096 2008-06-28 21:29 egg drwxr-xr-x 2 python_dep test01 4096 2008-06-28 17:52 odbcdrv -rwxr-xr-x 1 python_dep test01 2978 2008-06-28 11:25 py_app_install -rw-rw-r-- 1 python_dep test01 8032077 2008-06-28 21:35 py_app_installer.tar.gz $ $ .
Example 4-38 Verifying deployed Python application $ cd /home/python_dep/py_app/bin $ python itso_pyapp.py mensa 50001 test db2inst1 password Trying to establish connection... Is connection active? : True Closing connection... Connection closed. 4.6.7 Help and support You can seek assistance related to IBM_DB, IBM_DB_DBI, or IBM_DB_SA from following forum http://groups.google.com/group/ibm_db 4.7 Perl Perl stands for Practical Extraction and Report Language.
4.7.2 Installation of IBM Perl driver First we discuss about prerequisites to installing IBM Perl driver (DBD::DB2) and then we show how to install DBD::DB2. Prerequisites to installing DBD::DB2 Before installing DBD::DB2, you require the following software: Perl version 5.6 or greater: Perl is required to install DBD::DB2. Most Linux and UNIX systems come with Perl installed, but you might want to install a local copy of Perl on which you have full control.
If you are using some other Perl distribution on Windows, you have to install DBI manually. Download and uncompress the latest DBI from: http://search.cpan.org/author/TIMB/DBI/ Example 4-40 shows how to install DBI for Windows for Perl distribution other than ActiveState. The example assumes that you have uncompressed the compressed file into a directory and you are currently in that directory. Example 4-40 Installing DBI for Windows for Perl distribution other than ActiveState perl Makefile.
Installation procedure for Windows If you are using ActiveState Perl distribution (version 5.8 or greater) on Windows, you can install DBI with the help of ppm: ppm install http://theoryx5.uwinnipeg.ca/ppms/DBD-DB2.
If there is any incorrect information, the connection will fail and suitable error message will be thrown as shown in Example 4-43. Example 4-43 Failed connection using itso_perlapp.pl itsouser@ubuntu:~/redbook$ perl itso_perlapp.pl mensa 50001 test db2inst1 wrongpassword Trying to establish connection... Database connection not made: [IBM][CLI Driver] SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001 4.7.
Preparing the Perl application package This step is to identify all the Perl application files. In real life, the application is usually under some type of library control system and can be gathered easily. In our example, we only have a simple Perl application file itso_perlapp.pl for demonstration purpose. We place it under /perl_deploy/bin. Preparing the deployment package To automate the application deployment as much as we can, we create a deployment script perl_app_install.
# main program # process command-line options case $# in 0) syntax exit 1;; *) while getopts "p:s:" OPT; do case $OPT in p) INSTPATH=$OPTARG mkdir -p $INSTPATH ;; s) DB2PATH=$OPTARG export DB2_HOME=$DB2PATH ;; ?) echo "invalid command line option $*" syntax exit 1 ;; esac done ;; esac # verify the ODBC driver files and application files are ready # and then start the deployment TESTPATH=`echo $0|egrep '^/'` if [ -z $TESTPATH ]; then dirname `pwd`/$0|read CURPATH else CURPATH=`dirname $0` fi cd $CURPATH if [
# install dbd::db2 cd $INSTPATH/$DIR_DRV/DBD* perl Makefile.PL make make install printf "\n Deployment finished.\n" We placed the deployment script under our deployment directory /perl_deploy. Example 4-45 shows the contents of perl_deploy directory that now has all the files to be deployed. The packaging shown here is just an example where we have put each type of files in different directories. You can of course decide on any packaging you consider good.
Deploying the DB2 Perl application The deployment process for the DB2 Perl application involves transfer of the deployment package to the target system, uncompressing/extracting files in the package, and then executing the deployment script. See Example 4-23 for an excerpt of a sample output. Example 4-47 Deployment using script perl_app_install $ whoami perl_dep $ gunzip -c perl_app_installer.tar.
The Perl application is now deployed. You can see the subdirectories in the specified location. The Perl application is placed in the bin subdirectory of the specified location. You can proceed the application testing. Example 4-48 shows that our application, itso_perl_papp.pl, ran on the target system connecting to a remote database successfully. Example 4-48 Verifying deployed Perl application $ cd /home/perl_dep/perl_deploy/bin $ perl itso_perlapp.
Remark: For those familiar with Java and J2EE, there are a lot of similarities between the J2EE and .NET. The execution environment in J2EE is the Java Virtual Machine, which correspond to the Common Language Runtime in .NET. The .NET specifications correspond to the J2EE specifications. .NET supports a large variety of programming languages, but C# (pronounced C-sharp) is considered to be the pendant of Java in the .NET framework. Prerequisites Before you can access DB2 from your .
The test application, testconn20.exe, comes with the IBM Data Server Clients and the IBM Data Server Driver. We use this .NET application as the sample application to be deployed. testconn20.exe is very useful to verify a correct installation and can be a help in case of errors. Note: Add -dtc to the end of the call to testconn20.exe to verify distributed transaction handling with Microsoft Distributed Transaction Manager (MSDTC). Deploying our application consist of two steps.
Example 4-51 Call to testconn20 and the resulting output c:>testconn20 "database=itso; server=9.43.86.48:50000; user id=db2inst1; password=***" Step 1: Printing version info .NET Framework version: 2.0.50727.832 DB2 .NET provider version: 9.0.0.2, file version: 9.5.1.2 Capability bits: ALLDEFINED Build: 20080328 Factory for invairant name IBM.Data.DB2 verified Elapsed: 1,8226208 Step 2: Connecting using "database=itso; server=9.43.86.
212 DB2 Deployment Guide
5 Chapter 5. Deploying pre-configured databases In this chapter we describe how to deploy the pre-configured databases with your application. We present two different ways to create the database, by using DDL statements or by using a backup image. We take a look at different ways to populate the database and explain how to upgrade an already existing database. Throughout the chapter we show how to do these tasks in two different execution environments, which are shell scripts and Java applications.
5.1 Introduction From a high level view of deploying a pre-configured database, there are four tasks to perform: Create the database. Create the database layout — buffer pools, table spaces, and so on. Create the database objects — tables, views, stored procedures, and so on. Populate tables with data. We can do these tasks in many different ways. One way is simply to restore a backup image — then it all will be in place at once.
Flexibility: Upgrading an existing database might not be as straightforward as it appears: – Keeping data in an existing database is difficult, especially if table layout has changed. – Redirected restore might be required if the storage device on the target system is different from those defined in the backup image. Using scripts Using scripts is the most flexible method and gives you total control with the deployment process.
In Figure 5-1, we depict the ITSODB database. ITSO_BP bufferpool ITSO_TS2 ITSO_TS1 tablespace tablespace PK Function PK FK DEPT table EMPLOYEE table Stored procedure Check constraint STAFF table Alias ADEFUSR View summarytable trigger Index Figure 5-1 The sample database ITSODB You can download the DDLs used to create the ITSODB from the IBM Redbooks Web site. Refer to Appendix B, “Additional material” on page 267 for the download instructions. 5.
5.2.1 Collecting information about the database To deploy a pre-configured database to a new system, the tasks include these: Create the database. Create the database layout. Create the database objects. Database creation The database can be created by executing the CREATE DATABASE command through the Command Line Processor (CLP) or by using one of the DB2 APIs. Note: In this chapter we use Java as a programming language. DB2 does not provide APIs for Java. DB2 provides APIs for C/C++ and COBOL.
ITSO_BP ITSO_BP ITSO_TS1 DEPT EMPLOYEE ITSO_TS1 ITSO_TS2 STAFF ADEFUSR ITSO1.ts DEPT EMPLOYEE ITSO_TS2 STAFF ADEFUSR ITSO2.ts ITSO1.ts ITSO2.ts Figure 5-2 Two different table space mapping for ITSODB Example 5-2 shows the database layout DDLs of ITSODB. Note that we drop the default user tablespace userspace1, which is created implicitly when we create the database using the CREATE DATABASE command. We remove the table space because it is not used.
Because the storage layouts on the UNIX system and Windows are different, the file reference syntax of the DB2 table space definition is different for Windows and UNIX. See Example 5-3. Note that the only difference is the reference to the storage system. If you are deploying the pre-configured database from a UNIX-based system to a Windows-based system or vise versa, make sure that the DDL is modified.
ADD CONSTRAINT "PK_DEPARTMENT" PRIMARY KEY ("DEPTNO"); -- DDL Statements for index CREATE INDEX "ITSO"."XDEPT2" ON "ITSO"."DEPARTMENT" ("MGRNO" ASC) ALLOW REVERSE SCANS; -- DDL Statements for alias CREATE ALIAS "ITSO"."DEPT" FOR "ITSO"."DEPARTMENT"; Dependencies between database objects When creating the DDL statements, you should be aware of object dependencies and arrange the DDL statements in the proper sequence. If object B depends on object A, then object A must be created before object B.
These are the most common areas requiring your attention: Remove obsolete buffer pools and table spaces: When a database is created using the CREATE DATABASE command, a set of default buffer pools and table spaces are created. If these default objects are obsolete, you should explicitly add commands to remove them.
// Issue this statement to put the table in the right integrity mode SET INTEGRITY FOR itso.adefusr ALLOW NO ACCESS IMMEDIATE CHECKED Regardless of these required adjustments, the output from db2look gives us an excellent starting point for the final set of DDL statements 5.2.2 Using a shell script The DDL statements required to create the database layout and database objects are collected in one file. In our case, it is itso.ddl.
or all the statements are executed within the same unit of work. If executing all the statements in one unit of work is preferred, add -c- as an option. For example: db2 -c- -f itsodb.ddl The CLP also has options to direct the output and log the messages. We show how to obtain a complete history in a log file, and how to run the CLP in silent or non-silent mode. -l filename This option tells the command line processor to log commands and statements executed in a history file.
Note: The return code is available when the CLP has processed the entire file, and the return code is a “logical or” of the return codes from each statement. If some statements returned 2, other statements returned 1 and the rest of the statements were successful (return code 0) then the return code for the entire file is 3. Invocation of the CLP is identical on Windows and UNIX. The only difference between Windows and UNIX is how the return code is checked.
exit 8;; esac Example A: In this example we check explicitly for return code 4, which corresponds to a DB2 error or an SQL error. Remember that the return code from the command line processor is a logical or of the return codes from each statement in the file. This means that the return code other than 4, such as warning 6, will not be caught. Example B: This example checks for a return code greater than or equal to 4. In this case we will capture the DB2 error or SQL error along with any warnings.
Note: The statement if errorlevel == number evaluates to true if the return code is equal to or greater than the number specified, which mean that you have to check the values in descending order. Setting an exit code in the script is useful if this script will be invoked from another script or from an application. In this case we must decide whether the command shell where the script is executed should be terminated or not. We use the /b option on the exit code to specify this.
The complete Windows example Example 5-9 shows the full script used to create our ITSO sample database. All DB2 commands are logged in the file createdb.log. Example 5-9 The complete windows script for generating the database @REM ------------------------------------------@REM Step 1 @REM ------------------------------------------setlocal set DB2CLP=**$$** @REM ------------------------------------------@REM Step 2 @REM ------------------------------------------call createdb.
db2 -o- -l createdb.log connect reset exit /b 0 Step 1: We set up the DB2 environment by setting the environment variable DB2CLP to **$$**. The first statement, setlocal, ensures that the environment variables set in the script are local to the script. Step 2: We call another Windows script that creates the database. If any error occurs, we exit directly with the exit code from the sub-script. Step 3: If the database is created, we connect to it.
Creating the database from Java Since there is no call interface for Java, we use a script to generate the database. The script is named createdb.cmd — see 5.6.2, “Shell scripts” on page 252 for details. Example 5-10 shows how to execute a script from a Java application.
Note: Output from our script becomes input to the process object. This is why the output is retrieved as getInputStream(). Step 3: To get hold of the exit code, we have to wait for the process to end. If we do not call proc.waitFor() the script is still executed, but we will not be able to check the exit code. Creating database layout and database objects from Java From a Java point of view, there is no difference in executing an SQL statement or a DDL statement.
finally { // Step 5 stmt.close(); con.close(); } Step 1: First of all we retrieve a connection to the database and from the connection we get a statement object. How to get a connection to the database can either be seen in the downloadable sample program or at the DB2 Infocenter. Step 2: We simply loop through the list of statements and execute them one by one. Step 3: If all the statements are executed successfully, we commit the work.
Target directories: If the backup image contains restrictions to specific directories, these directories must exist on the target system. Otherwise, performing a redirected restore is required. Different platforms: Cross platform restore is not supported. You have to prepare a separate backup image for each platform because the backup image from a Windows system cannot be restored to a UNIX system. You also must have different images for 32-bit and 64-bit versions of the different operation systems.
In our example we have the SQL statements in a set of files, where each statement is separated by a default delimiter, a semicolon. There is no difference in executing a set of DDL statements or a set of SQL statements. This means that the shell scripts are similar to the shell scripts in 5.2, “Deploying a database using scripts” on page 216, and the Java application can be reused.
@REM ----------------------------------------------------------@REM Step 4.b @REM ----------------------------------------------------------:success db2 -o- -l populatedb.log connect reset exit /b 0 Step 1: Connect to the database. The user ID and password are passed to the script as the input parameters. Step 2: Once connected, we execute each of the files containing the SQL statements to populate our database. Notice that we use the command line processor option -c- that turn off the autocommit.
5.4.2 Using DB2 utilities Using the DB2 utilities, import, export, and load, to populate the database allows you to move data from a source database to a target database. Data in the source database is exported into a set of files. These files are moved to the target environment and imported or loaded to the target database. This approach has the advantage of being platform independent.
For a detailed description of the export utility, refer to the DB2 Infocenter: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.d b2.luw.admin.dm.doc/doc/c0004587.html Importing data You can use either the import utility or the load utility to move the exported data into the target database. The import utility is based upon the SQL statements and all the integrities and constraints are obtained during import.
Because our tables are empty, we choose the insert mode. In Example 5-15 we show the statements to populate our sample database using the IXF files exported in the previous section. Example 5-15 Import statements used to populate our sample database import from "dept.ixf" of ixf messages "dept.msg" insert into itso.department; import from "emp.ixf" of ixf messages "emp.msg" insert into itso.employee; import from "staff.ixf" of ixf messages "staff.msg" insert into itso.
load from "staff.ixf" of ixf messages "staff.msg" insert into itso.staff copy no indexing mode autoselect; set integrity for itso.employee allow no access immediate checked; set integrity for itso.adefusr allow no access immediate checked; Because referential integrities are not checked during load, you can load the tables in any order you like. However, the set integrity commands must be issued in the right order. It must be done until no tables in the database are in check pending mode.
Exporting data with db2move By default, db2move exports all the user tables and is always using the IXF file format. The outcome of an export by db2move is the outcome of the underlying calls to the export utility and a text file db2move.lst, which contains the mapping between the tables and the export files. Example 5-17 shows how to export our sample database using the db2move utility.
5.5 Updating an existing installation It is not uncommon that the changes on the database object definitions are required due the application requirements changed. Frequently, the changes are taken place at the test system first then deployed to the production system. The data in the production system sure should be preserved.
3 Script ITSODB2 1 ITSODB 1 Delta New Config 2 Current Config Compare Figure 5-3 Identify and apply changes Get the configurations for the two databases. Compare the two configurations and create the set of DDL and SQL statements that will migrate the target database. Apply the delta script to the target database. Important: Backing up the target system is very important for this task.
5.5.2 Updating table objects When it comes to updating the table objects, certain complications arise. Because tables contain information that has to be preserved, you cannot just drop and recreate them. These are some of the complications: Changing a table might increase the row size to an extent that it will no longer fit in the existing table space.
We focus our discussion on the complex changes here. Using the stored procedure altobj DB2 provides us with a stored procedure, named altobjc, to alter table definitions. altobj is a very powerful tool and can be used in most cases. altobj parses an input create table statement serving as the target data definition language for the existing table that is to be altered.
years smallint , salary decimal (7, 2) , comm decimal (7, 2)) IN itso2', -1, ? ); Note that if the newly added column is added in the middle of the table and the table already has data, altobj might fail in loading the data, or the data loaded is incorrect. Use the STAFF table shown in Example 5-20 as an example, if the JOB is a newly added column. When altobj loads the data, it brings the data from the old table and loads it on a column-by-column basic.
Figure 5-4 illustrates the changes we want to apply to the table itso.staff in our sample database. We add the AWARDS column and change the data type of column DEPT from smallint to integer. New Configuration Current Configuration Delta Figure 5-4 Alter the table itso.staff in the sample database Because we change the data type of the DEPT column, we cannot use the alter table command.
DROP TABLE "ITSO"."STAFF"; RENAME TABLE "ITSO"."SHADOWTABLE" to "STAFF"; ALTER TABLE "ITSO"."STAFF" ADD CONSTRAINT "C_JOB" CHECK ("JOB" IN ( 'Mgr', 'Clerk', 'Sales')); CREATE TRIGGER do_not_del_sales NO CASCADE BEFORE DELETE ON itso.staff REFERENCING OLD AS oldstaff FOR EACH ROW MODE DB2SQL WHEN(oldstaff.job = 'Sales') BEGIN ATOMIC SIGNAL SQLSTATE '75000' ('Sales staff cannot be deleted... see the DO_NOT_DEL_SALES trigger.');-END; 5.5.
Table 5-4 DB2 metadata for some of the database objects Database object DB2 metadata Tables sysibm.systables, syscat.tables Columns sysibm.syscolumns, syscat.columns Views sysibm.sysviews, syscat.views Stored procedures sysibm.procedures, syscat.procedures Functions syscat.functions, syscat.functions Check constraints syscat.checks Triggers sysibm.systriggers Indexes syscat.indexes, syscat.
Comparing two database configurations In our sample Java application, both the source and target database configurations are contained in Java classes. We assume that we do not have access to the source database when we run our Java application on the target environment. To simplify our application, the source configuration is hardcoded. The target configuration is retrieved dynamically. The main approach is a table to table comparison.
AND AND AND AND r.routinetype = 'F' d.btype = 'T' d.bschema = 'ITSO' d.bname = 'EMPLOYEE' // Stored procedures dependencies SELECT r.routinename FROM syscat.routines r, syscat.routinedep rd, syscat.packagedep pd WHERE r.specificname = rd.routinename AND rd.bschema = pd.pkgschema AND rd.bname = pd.pkgname AND r.routinetype = 'P' AND rd.btype = 'K' AND pd.btype = 'T' AND pd.bschema = 'ITSO' AND pd.bname = 'EMPLOYEE'; Our sample application only covers checking constraints and triggers.
2 1 4 ITSODB 3 Figure 5-5 Overview of the Java application : The main class in the application is the MigrateMainController. The first action is to retrieve the current database configuration. This is done by issuing a set of queries against the DB2 metadata in ITSODB. In the figure only the TableInfoQuery is shown. Other queries are left out to keep the figure simple. The MigrateMainController has now access to both the new and the current configuration which is shipped with the controller.
information about a column, the TriggerInfo class to obtain the information about a trigger, and so on. Furthermore, the metadata information for a given database object is added up in the class representing it. For instance, the TableInfo class consists of a list of columns, a list of check constraints, and so on. Figure 5-6 show the class diagram used in our application.
5.6.1 Scripts Table 5-6 lists the scripts used in the examples in this chapter. Table 5-6 Scripts containing DDL and SQL statements Script name Description itsodb.ddl Contain DDL and SQL statements to create the database layout and database object for our sample database ITSODB. itsodb2.ddl Contain DDL and SQL statements to create the database layout and database objects for the modified sample database ITSODB2. export.
Table 5-7 Supportive shell scripts Script name Description exe.cmd Execute another shell script and terminate the execution environment with the return code returned by the invoked shell script. The shell script to be executed must be given as the argument. Example: c:>exe createdb.cmd exe_shell.cmd Execute another shell script and print out the return code from the invoked shell script to the console. The shell script to be executed must be given as the argument. Example: c:>exe_shell createdb.
Script name Description exportdb.cmd Export data from the ITSODB database to IXF files. It connects to the ITSODB database and then execute the script export.sql. Example: c:>exportdb.cmd Log file: export.log importdb.cmd Import data into the ITSODB database from a set of IXF files using the DB2 import utility. It connects to the ITSODB database and execute the script import.sql. Example: c:>importdb.cmd Log file: importdb.log 254 loaddb.
5.6.3 Java applications Table 5-9 lists the Java applications. All Java applications are packaged into the Java archive itso.jar. When starting a Java application, we have to make sure that all used classes are available to the runtime. This is done by setting up the classpath. We must therefore add itso.jar to the classpath. The DDLExecuter and the MigrateExecuter connect to DB2 from Java, so for these applications we add also add db2jcc.jar and db2jcc_license_cu.jar to the classpath.
Application name Description MigrateExecuter Based on two database configurations this application can perform a set of migration related actions. It compares the source database configuration, which is the configuration of the ITSO2 database, with the target database configuration which is retrieved dynamically from the database given as an argument to the application. Possible actions are these: Print Print out both database configurations to stdout.
Table 5-10 Shell scripts to start Java applications Shell script Description jscript.cmd Starts the Java application ScriptExecuter. The script to be executed must be given as an argument. The script given as the argument will be executed through the exe.cmd support script to ensure correct handling of the return code. Example: c:>jscript populatedb.cmd jstmt.cmd Starts the Java application DDLExecuter. The name of the file containing SQL and /or DDL statements must be given as a parameter.
258 DB2 Deployment Guide
A Appendix A. Sample applications This appendix provides the application codes of various languages used in demonstrating the DB2 application deployment as well as deploying pre-configured database. A.1 C/C++ Example A-1 shows the application used to demonstrate the deployment of the DB2 C/C++ application. Example: A-1 Sample CLI application /**************************************************************************** ** ** A sample CLI application which makes DSN-less connection to database.
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv .cli.doc/doc/t0007141.html ** ** For example, on Linux x86_64 env: ** gcc -o itso_cliapp -m64 -I/home/db2inst1/sqllib/include \ ** -L/home/db2inst1/sqllib/lib64 -ldb2 itso_cliapp.c ** ****************************************************************************/ #include #include #include #include #include #include #include
/* get connection information from command line arguments */ strcpy(ip, argv[1]); strcpy(port, argv[2]); strcpy(dbname, argv[3]); strcpy(user, argv[4]); strcpy(passwd, argv[5]); /* populate the connection string */ sprintf((char *)connStr, "Database=%s; Protocol=tcpip; Hostname=%s; Servicename=%s; Uid=%s; Pwd=%s", dbname, ip, port, user, passwd); /* allocate an environment handle */ cliRC = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); if (cliRC != SQL_SUCCESS) { printf("\n ERROR while allocating
/* disconnect from the database */ printf("\n Disconnecting from the database %s...\n", dbname); cliRC = SQLDisconnect(hdbc); } /* free connection handle & environment handle */ cliRC = SQLFreeHandle(SQL_HANDLE_DBC, hdbc); cliRC = SQLFreeHandle(SQL_HANDLE_ENV, henv); } A.2 PHP Example A-2 shows the application used to demonstrate the deployment of the DB2 PHP application. Example: A-2 Sample PHP application
$conn = db2_connect($dsn, '', ''); if ($conn) { echo "Connection succeeded.\n"; echo "Closing connection...\n"; db2_close($conn); echo "Connection closed.\n"; } else { echo db2_conn_errormsg(), "\n"; echo "Connection failed.\n"; } ?> A.3 Ruby Example A-3 shows the application used to demonstrate the deployment of the DB2 PHP application. Example: A-3 Sample Ruby application # # # # # # # # # A sample Ruby application. Use this program to test connection to a database.
";PROTOCOL=TCPIP" << \ ";UID=" << ARGV[3] << \ ";PWD=" << ARGV[4] puts "Trying to establish connection..." conn = IBM_DB::connect( dsn, "", "" ) if conn puts "Is connection active? : #{IBM_DB::active(conn)}" puts "Closing connection..." IBM_DB::close(conn) puts "Connection closed." else puts IBM_DB::conn_errormsg() end end A.4 Python Example A-4 shows the application used to demonstrate the deployment of the DB2 Python application. Example: A-4 Sample Python application """ A sample python application.
# Creating dsn from command line arguments. dsn = "HOSTNAME=" + argv[0] + \ ";PORT=" + argv[1] + \ ";DATABASE=" + argv[2] + \ ";PROTOCOL=TCPIP" + \ ";UID=" + argv[3] + \ ";PWD=" + argv[4] print "Trying to establish connection..." conn = ibm_db.connect( dsn, "", "" ) print "Is connection active? : ", ibm_db.active(conn) print "Closing connection..." ibm_db.close(conn) print "Connection closed." if __name__ == "__main__": main(sys.argv[1:]) A.
# Creating dsn from command line arguments. $dsn = "HOSTNAME=" . $ARGV[0] . ";PORT=" . $ARGV[1] . ";DATABASE=" . $ARGV[2] . ";PROTOCOL=TCPIP" . ";UID=" . $ARGV[3] . ";PWD=" . $ARGV[4]; print "Trying to establish connection...\n"; $dbh = DBI->connect("dbi:DB2:$dsn", "", "", {PrintError => 0} ) || die "Database connection not made: $DBI::errstr"; print "Connection successful.\n"; print "Closing connection...\n"; $dbh->disconnect(); print "Connection closed.
B Appendix B. Additional material This book refers to additional material that can be downloaded from the Internet as described below. Locating the Web material The Web material associated with this book is available in softcopy on the Internet from the IBM Redbooks Web server. Point your Web browser at: ftp://www.redbooks.ibm.com/redbooks/SG247653 Alternatively, you can go to the IBM Redbooks Web site at: ibm.
Using the Web material The additional Web material that accompanies this book includes the following files: File name Description DeplyDB2Server.zip Zipped Scripts for deploying DB2 Server Java.zip Zipped Java sample code C_Sample.zip Zipped C sample code dotNet.zip Zipped .Net sample code Pre-configuredDB.
Related publications The publications listed in this section are considered particularly suitable for a more detailed discussion of the topics covered in this book. IBM Redbooks For information about ordering these publications, see “How to get Redbooks” on page 273. Other publications These publications are also relevant as further information sources: IBM - DB2 9.
Getting Started with DB2 Installation and Administration on Linux and Windows, GC23-5857 Internationalization Guide, SC23-5858 Message Reference, Volume 1, GI11-7855 Message Reference, Volume 2, GI11-7856 Migration Guide, GC23-5859 Net Search Extender Administration and User's Guide, SC23-8509 Partitioning and Clustering Guide, SC23-5860 Query Patroller Administration and User's Guide, SC23-8507 Quick Beginnings for IBM Data Server Clients, GC23-5863 Quick Beginnings for DB2 Servers, GC2
Call Level Interface Guide and Reference, Volume 2, SC10-4225 Command Reference, SC10-4226 Data Movement Utilities Guide and Reference, SC10-4227 Data Recovery and High Availability Guide and Reference, SC10-4228 Developing ADO.
Quick Beginnings for DB2 Connect Personal Edition, GC10-4244 Quick Beginnings for DB2 Connect Servers, GC10-4243 Online resources These Web sites are also relevant as further information sources: DB2 DB2 Information Center http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/ Database and Information Management home page http://www.ibm.com/software/data/ DB2 Technical Support http://www.ibm.com/software/data/db2/support/db2_9/ DB2 Product Family Library http://www.ibm.
DB2 Perl Database Interface http://www.ibm.com/software/data/db2/perl Comprehensive Perl Archive Network http://www.cpan.org http://www.cpan.org/modules/by-category/07_Database_Interfaces/DBI Apache HTTP Server Project http://httpd.apache.org Perl.apache.org http://perl.apache.org/docs/1.0/guide/ PHP scripting language http://www.php.net/ IBM Tivoli System Automation for Multiplatforms http://publib.boulder.ibm.com/tividd/td/ITSAFL/SC33-8272-01/en_US/PDF/HALBA U01.
274 DB2 Deployment Guide
Index Symbols .pdf file 103 .
CREATE DATABASE command 217 create package 102 cursor stability 156 custom installation 99 D DAS 23 dascrt 23, 33 dasdrop 23, 33 daslist 23, 33 dasmigr 23, 33 dasupdt 23, 33 data definition language 214 data partitioning feature 9 data server client 94 database administration 12 database configuration process 26 database directory 92 database layout 214, 218 database manager configuration 26, 34 database manager configurations parameter 2 database object 214, 220 database partition 40 database partition no
E elevated privileges installation 33 embedded SQL application 169 embedded SQL statement 154 embedding client 5 encoding scheme 6 encryption 62 end of support 16 enhancements 16 enterprise edition 9 enterprise environment 10 environment setting 25 environment variable 142, 225 environment variable db2clp 225 error handling 223 errorlevel environment variable 226 executable file 97 exit code 226, 229–230 external program 229 external transaction manager 93 F feature addition 16 federation 35 fenced user 30
K key type 61 keyword 26, 98 KRBPLUGIN 148 L LAN connection 131 LANG keyword 98 language code 96 language compiler 154 language option 91 large object 170 large scale deployment 91 large volume transaction 4 ld_library_path 149 LDAP cache 144 libpath 149 license 3 license consideration 10 license scheme 7 life cycle 16 lightweight 5 lightweight deployment solution 143 local clients 7 local database 7 local drive 104, 117 local use 7 log file 134 logical storage unit 217 M mass deployment 2, 91 memory 3 Mi
processor rating 21 processor technology 21 processor value unit 3, 10, 21 PROD keyword 98 product bundle 8 product consideration 10 product image 98 production workload 34 profile registry variable 2 program temporary fix 139 programming language 150 protocol 26, 148 protocol information 37 prototype 8 pruned image 98 PTF 139 public key 31 public key files 61 pull deployment 100 pureXML 3 push deployment 100, 130 PVU 3, 10, 21 PWDPLUGIN 148 Q quality assurance 34 R read stability 156 Redbooks Web site 27
source file 116 SQL statement 4, 223 SQLCODE 156 SQLSTATE 156 ssh 41 sshd 100, 130 standalone database 7 standard output 64 static SQL 151 storage device 217 storage layouts 219 Stored procedure 215 stored procedure 5, 31, 139, 170 subcollection 110 subscription 3 summary table 215, 221 syntax 150 system administer 25 system environment variable 149 system error 226 system reboot 24 system registry 22 system variables 165 system-based authentication 24 T table 215 table space 214–215, 217 tar archive 146 t
DB2 Deployment Guide (0.5” spine) 0.475”<->0.
Back cover ® DB2 Deployment Guide ® Learn to deploy DB2 Data Servers and Clients Automate DB2 mass deployment with scripts Deploy DB2 with applications DB2 provides various installation methods as well as features and tools to deploy a large number of clients and servers. Database administrators, application developers, and application architects have a number of available options when deploying DB2 9.5 for Linux, UNIX, and Windows (DB2 for LUW).
