IBM DB2 User Manual

ibm.com/redbooksDB2 Deployment GuideWhei-Jen ChenJian TJ TangCarsten BlockJohn ChunLearn to deploy DB2 Data Servers and ClientsAutomate DB2 mass depl

viii DB2 Deployment GuideTrademarksIBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business Machines Corporat

86 DB2 Deployment Guide fidoneechoecho "Deployment finished."We use the following command to start the mass deployment of the DB2 fix pack

Chapter 2. DB2 server deployment 87On each target machine, you can use the db2ls command to list all of the installed DB2 copies. Example 2-29 shows

88 DB2 Deployment Guide

© Copyright IBM Corp. 2008. All rights reserved. 89Chapter 3. DB2 client deploymentIn this chapter, we discuss various DB2 client deployment methods f

90 DB2 Deployment Guide3.1 Client deployment planningClient deployment planning is a task to consider at the time when the application is designed a

Chapter 3. DB2 client deployment 91Figure 3-1 How to connect our application to the DB2 server3.1.2 FootprintThe size of the footprint can also p

92 DB2 Deployment GuideA smaller install image with only the required features and language options can be obtained by using the command line tool db

Chapter 3. DB2 client deployment 93To read more about db2cli.ini and keyword option, use the DB2 Information Center:http://publib.boulder.ibm.com/in

94 DB2 Deployment Guide3.1.6 LicensingThe IBM Data Server Runtime Client and the IBM Data Server Driver can be distributed without any licensing. Th

Chapter 3. DB2 client deployment 95For further details regarding installing multiple DB2 copies, refer to the following URLs in the DB2 Information

© Copyright IBM Corp. 2008. All rights reserved. ixPrefaceDB2® provides various installation methods as well as features and tools to deploy a large n

96 DB2 Deployment GuideDB2 Setup wizardThe DB2 Setup wizard can be executed in a language other than the default system language by manually invoking

Chapter 3. DB2 client deployment 973.2.2 Client instance on the DB2 serverIf the machine already has a DB2 server product installed, it is not nece

98 DB2 Deployment Guide-p <root directory path>Specifies the full path to the root directory of the source installation image. This directory c

Chapter 3. DB2 client deployment 993. Use any of the installation methods discussed earlier to install and maintain a pruned DB2 installation image.

100 DB2 Deployment Guide3.2.4 Mass deployment of IBM data server client productWe have discussed general deployment methods for the DB2 server produ

Chapter 3. DB2 client deployment 101For further details regarding Microsoft’s Systems Management Server, refer to the following URL:http://www.micro

102 DB2 Deployment Guide6. Click Next to continue from the window, Welcome to the Create Package from Definition Wizard, as shown in Figure 3-3.Figur

Chapter 3. DB2 client deployment 1038. Select the appropriate .pdf file from the DB2 installation image. These images are located in the db2\Windows

104 DB2 Deployment Guide10.From the Source Files window, select Create a compressed version of the source as shown in Figure 3-7. The default is “Thi

Chapter 3. DB2 client deployment 10512.From the window, Create Package from Definition Wizard, select Finish to complete the Create Package from Def

x DB2 Deployment Guidefocuses on DB2 MPP system and performance tuning in complex environments. He also has a job role in a leading critical situatio

106 DB2 Deployment GuideDistributing DB2 installation packages to clients using the Microsoft SMSOnce the DB2 installation package has been created i

Chapter 3. DB2 client deployment 1072. Click Next on the window, Welcome to the Distribute Software Wizard, as shown in Figure 3-12, to proceed.Figu

108 DB2 Deployment Guide4. Select the distribution points for the DB2 package from the Distribution Software Wizard. All available distribution point

Chapter 3. DB2 client deployment 1096. Select a program to be advertised to members of SMS distribution from the Select a Program to Advertise windo

110 DB2 Deployment Guide8. You can specify name to identify the advertisement in the Name field and add an optional comment from the Advertisement Na

Chapter 3. DB2 client deployment 11110.Specify when you want the program to be advertised and installed, on the Advertisement Schedule window, as sh

112 DB2 Deployment Guide12.Now the window, Completing the Distribute Package Wizard, will appear and you can click Finish to advertise the program to

Chapter 3. DB2 client deployment 113Figure 3-23 Configuration Manager Console6. Click Next to continue from the window, Welcome to the Create Pack

114 DB2 Deployment GuideFigure 3-24 Welcome to the Create Package from Definition Wizard window7. From the window, Create Package from Definition (

Chapter 3. DB2 client deployment 115In this example we are installing DB2 Data Server Client. Select db2client.pdf as shown in Figure 3-26, then cli

Preface xiHelmut RieglerIBM AustriaYvonne Lyon, Sangam Racherla, Emma JacobsInternational Technical Support Organization, San Jose CenterBecome a pu

116 DB2 Deployment Guide10.From the Source Files window, select Create a compressed version of the source files as shown in Figure 3-28. Click Next t

Chapter 3. DB2 client deployment 11711.From the Source Directory window, you can either specify a local drive or a network path for the IBM Data Ser

118 DB2 Deployment Guide12.From the window, Create Package from Definition Wizard, select Finish to complete the Create Package from Definition Wizar

Chapter 3. DB2 client deployment 11913.You will be returned to the window, Create Package from Definition Wizard (Figure 3-31).Figure 3-31 Create

120 DB2 Deployment GuideFigure 3-32 Starting software distribution2. Click Next on the window, Welcome to the Distribute Software Wizard, as shown

Chapter 3. DB2 client deployment 121Figure 3-34 Packages window4. Select the distribution points for the DB2 package from the Distribution Softwar

122 DB2 Deployment Guide5. Ensure that the radio button for Yes is selected for the advertising program for the package listed on the Advertise Progr

Chapter 3. DB2 client deployment 1236. Select the Setup program to be advertised to members of the SCCM distribution from the window, Select a Progr

124 DB2 Deployment Guide7. Select either an existing collection of machines where you want to advertise and install the DB2 program selected, or crea

Chapter 3. DB2 client deployment 1258. You can specify a name to identify the advertisement in the Name field and add an optional comment from the A

xii DB2 Deployment Guide

126 DB2 Deployment Guide9. Select whether the advertisement should apply to subcollections or not from the window, Advertise to Subcollections, as sh

Chapter 3. DB2 client deployment 12710.Specify when you want the program to be advertised and installed on the window, Advertisement Schedule, as sh

128 DB2 Deployment Guide11.Specify whether this program deployment should be mandatory for your SCCM clients or not on the window, Assign Program, as

Chapter 3. DB2 client deployment 12912.Now the window, Completing the Distribute Package Wizard, will be displayed. You can click Finish to advertis

130 DB2 Deployment GuideFigure 3-44 Completion of Distribute Software WizardIBM data server client deployment on Linux and UNIXA common method for

Chapter 3. DB2 client deployment 1313.3 Thin Client deploymentThe Thin Client is an alternative method for leveraging an IBM data server client, wh

132 DB2 Deployment GuideInstalling IBM Data Server Client or DB2 Connect Personal Edition on the code serverThe installation steps for installing IBM

Chapter 3. DB2 client deployment 1334. Click the Sharing tab. 5. Click the radio button, Share this folder. 6. In the Share Name field, enter a shar

134 DB2 Deployment GuideMapping a network drive from each thin client to the code serverTo map a network drive from the thin client, perform the foll

Chapter 3. DB2 client deployment 135/S Specifies the share name of the code server where you installed the DB2 product. This parameter is necessary

© Copyright IBM Corp. 2008. All rights reserved. 1Chapter 1. Introduction to DB2 deploymentIn this chapter, we introduce various aspects of DB2 9.5 fo

136 DB2 Deployment Guide

© Copyright IBM Corp. 2008. All rights reserved. 137Chapter 4. Deploying applications with DB2In this chapter, we discuss deploying various applicatio

138 DB2 Deployment Guide4.1 Introduction to application deployment packageThe application deployment package, in the context of this book, consists

Chapter 4. Deploying applications with DB2 139򐂰 IBM Data Server Driver for JDBC and SQLJ license files for connection to DB2 for z/OS and DB2 for i5

140 DB2 Deployment GuideInstallation procedureYou can obtain the IBM Data Server Driver for JDBC and SQLJ from the DB2 product CD or download it from

Chapter 4. Deploying applications with DB2 141򐂰 sqlj.zip and sqlj4.zip:These file contain classes that are required to prepare SQLJ applications for

142 DB2 Deployment Guideb. Update the database manager configuration file with the appropriate TCP/IP service name. The service name is specified in

Chapter 4. Deploying applications with DB2 143You might want to redirect the output to a file for easier viewing. The JDK_PATH field appears near th

144 DB2 Deployment GuideApart from runtime support for DB2 CLI API and ODBC API, this driver offers runtime support for Transaction API. It also prov

Chapter 4. Deploying applications with DB2 145Download DB2 Driver for ODBC and CLI for Linux and UNIX from the following IBM Web site:https://www14.

2 DB2 Deployment Guide1.1 DB2 deployment overviewDatabase administrators, application developers, and application architects have a number of availa

146 DB2 Deployment GuideInstalling IBM Data Server Driver for ODBC and CLITo install IBM Data Server Client for ODBC and CLI, perform these steps:1.

Chapter 4. Deploying applications with DB2 147Configuring IBM Data Server Driver for ODBC and CLIYou must configure the driver and application runti

148 DB2 Deployment Guide– Set the DB2_CLI_DRIVER_INSTALL_PATH:Set the DB2 environment variable DB2_CLI_DRIVER_INSTALL_PATH to the directory where the

Chapter 4. Deploying applications with DB2 149– Set the system environment variables:Set the system environment variable LIBPATH on AIX or LD_LIBRAR

150 DB2 Deployment Guide򐂰 Register with Microsoft driver manager:If your ODBC applications use Microsoft ODBC driver manager, you must register the d

Chapter 4. Deploying applications with DB2 151To access DB2, Java uses either dynamic SQL or static SQL. Dynamic SQL is supported by the JDBC standa

152 DB2 Deployment GuideDeployment procedure for a Java applicationThe procedure for deploying a Java application is simple and straightforward. Thes

Chapter 4. Deploying applications with DB2 153Example 4-3 The command file, jmigrate.cmd, used to start the Java application// Step 1 : Setting up

154 DB2 Deployment Guide4.3 Deploying C/C++ applicationsC and C++ are probably two of the most popular and well-known programming languages. They ar

Chapter 4. Deploying applications with DB2 155DB2 CLI. In addition, some DB2 specific extensions have been added to DB2 CLI to facilitate programmin

Chapter 1. Introduction to DB2 deployment 31.2 DB2 9.5 for UNIX, Linux, and Windows productsOne of the first things to consider during deployment p

156 DB2 Deployment GuideWhen DB2 CLI driver works without the ODBC driver manager, it supports a subset of the functions provided by the ODBC driver.

Chapter 4. Deploying applications with DB2 157Once the connection succeeds, the application outputs a message to the screen. Then it releases the co

158 DB2 Deployment Guide4.3.3 Considerations for deployment of CLI and ODBC applicationsFor scenarios where the IBM Data Server Client or Runtime Cl

Chapter 4. Deploying applications with DB2 159For a complete introduction to these connectivity configurations, visit the DB2 Information Center:htt

160 DB2 Deployment Guide3. 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 Ser

Chapter 4. Deploying applications with DB2 161 r) REDIS_LIST=$OPTARG ;; o) OUTPUT_FILE=$OPTARG ;; ?) echo "invalid command

162 DB2 Deployment GuideThere are three command line options for the script.– Option -d specifies the path of the DB2 Driver file for ODBC and CLI. I

Chapter 4. Deploying applications with DB2 163Another approach is to compile and link manually from the command line. The options used for compile a

164 DB2 Deployment GuideExample 4-10 A failed connect test using itso_cliappdb2inst1@mensa:~/work> ./itso_cliapp mensa 50000 itso db2inst1 wrong

Chapter 4. Deploying applications with DB2 1654. The purpose of the script app_install is to copy an application file and extract CLI driver files t

4 DB2 Deployment GuideDB2 Workgroup Server EditionDesigned for midsize businesses, it has all of the functionality of DB2 Express plus an optional Qu

166 DB2 Deployment Guide case $OPT in p) INSTPATH=$OPTARG mkdir -p $INSTPATH ;; r) REGVAR=Y ;; ?) echo "invalid comman

Chapter 4. Deploying applications with DB2 167 echo " System variables registered. Please re-login to have the settings be effectiv

168 DB2 Deployment GuideIn our example, there is no special configuration required for our application. Therefore, no application specific configurat

Chapter 4. Deploying applications with DB2 169Example 4-15 Verify the connection after deploymentprodapp@lepus:~> cd /home/prodapp/cliapp/binpr

170 DB2 Deployment GuideObject handling was re-written in PHP version 5, and from version 5 onwards, PHP also supports various OOP components, such a

Chapter 4. Deploying applications with DB2 171PrerequisitesBefore installing IBM_DB2 or PDO_IBM, you will require the following software.4.4.4 PHPP

172 DB2 Deployment GuideIn our example, we place these files in the directory /home/user1/phpdrivers.2. Create php.ini file:Change to the lib directo

Chapter 4. Deploying applications with DB2 173The PHP drivers are open source for you to build and install. The building DB2 PHP drivers will genera

174 DB2 Deployment GuideThis displays all extensions of PHP dynamically. Check for ibm_db2 and pdo_ibm in the output.Second way: For the Windows envi

Chapter 4. Deploying applications with DB2 175In order to run the application, create a directory and save the application as itso_phpapp.php. Examp

Chapter 1. Introduction to DB2 deployment 5administration and client/server configuration. Note that GUI tools are available for Windows on x86 32 b

176 DB2 Deployment GuidePreparing DB2 PHP driver and redistributable DB2 ODBC and CLI driver filesYou can obtain the DB2 PHP driver file from any of

Chapter 4. Deploying applications with DB2 177# -r specify to configure system variable for ODBC and CLI driver## example: php_app_install -p /hom

178 DB2 Deployment Guidecd $CURPATHif [ ! -d $DIR_DRV ] || [ ! -d $DIR_APP ] || [ ! -d $DIR_PHP ] ; then echo " ODBC CLI driver, Application or

Chapter 4. Deploying applications with DB2 179 fi ;;esacprintf "\n Deployment finished.\n"We placed the deployment script under our

180 DB2 Deployment GuideDeploying the DB2 PHP applicationThe deployment process for the DB2 PHP application involves transfer of the deployment packa

Chapter 4. Deploying applications with DB2 1814.5 RubyRuby is a dynamic, general purpose, object-oriented scripting language that has Perl-like syn

182 DB2 Deployment GuideUncompress the download file, change to the newly created directory, and run the following command to install it on Linux, UN

Chapter 4. Deploying applications with DB2 1834.5.3 Creating a sample Ruby applicationIn order to demonstrate Ruby application deployment, we have

184 DB2 Deployment GuidePreparing IBM_DB gem and redistributable DB2 ODBC and CLI driver filesYou can obtain the IBM_DB2 gem file from any of the met

Chapter 4. Deploying applications with DB2 185############################################################################set -x# Define variablesDI

6 DB2 Deployment Guide򐂰 Any SQLJ or JDBC application that accesses i5/OS using IBM Data Server Driver for JDBC and SQLJ type 4 connectivity, must ens

186 DB2 Deployment Guide echo " ODBC CLI driver, Application, or gem directory not existing.\n Abort." exit 1fi# deploy applications and

Chapter 4. Deploying applications with DB2 187We placed the deployment script under our deployment directory /ruby_deploy. Example 4-28 shows the co

188 DB2 Deployment GuideExample 4-30 Deploying using script ruby_app_install$ whoamiruby_dep$ gunzip -c ruby_app_installer.tar.gz |tar -xf -$ ls -l

Chapter 4. Deploying applications with DB2 1894.6 PythonPython (also known as CPython) is a general purpose, high level scripting language well sui

190 DB2 Deployment Guide4.6.3 IBM_DB_SA adaptorIBM_DB_SA is an adaptor IBM provides for SQLAlchemy. The IBM_DB_SA adapter provides SQLAlchemy interf

Chapter 4. Deploying applications with DB2 191򐂰 IBM Data Server Driver for CLI support:All Python extensions communicate to DB2 using CLI. If either

192 DB2 Deployment GuideYou can download the latest source code of IBM_DB driver and IBM_DB_DBI wrapper from:http://pypi.python.org/pypi/ibm_db/If yo

Chapter 4. Deploying applications with DB2 193Closing connection...Connection closed.If there is any incorrect information, the connection will fail

194 DB2 Deployment GuideWe have assumed that Python is installed on the deployment machine, and that the user has sufficient read and write permissio

Chapter 4. Deploying applications with DB2 195DIR_EGG=egg # directory for egg fileunset REGVAR# command-line syntaxsyntax(){echo " py_app

Chapter 1. Introduction to DB2 deployment 71.2.3 DB2 standalone and connect productsDB2 also provides various flexibility and options in its produc

196 DB2 Deployment Guide# Install the setuptools and egg.cd $CURPATH/$DIR_EGGsh setuptools*.eggeasy_install ibm_db*.eggcd $INSTPATHmkdir -p "$DI

Chapter 4. Deploying applications with DB2 197Example 4-35 Contents of python_deploy directoryitsouser@ubuntu:~/python_deploy$ ls -Rl.:total 16drw

198 DB2 Deployment GuideExample 4-37 Deploying using script py_app_install$ whoamipython_dep$ gunzip -c py_app_installer.tar.gz | tar -xf -$ ls -lt

Chapter 4. Deploying applications with DB2 199Example 4-38 Verifying deployed Python application$ cd /home/python_dep/py_app/bin$ python itso_pyap

200 DB2 Deployment Guide4.7.2 Installation of IBM Perl driverFirst we discuss about prerequisites to installing IBM Perl driver (DBD::DB2) and then

Chapter 4. Deploying applications with DB2 201If you are using some other Perl distribution on Windows, you have to install DBI manually. Download a

202 DB2 Deployment GuideInstallation procedure for WindowsIf you are using ActiveState Perl distribution (version 5.8 or greater) on Windows, you can

Chapter 4. Deploying applications with DB2 203If there is any incorrect information, the connection will fail and suitable error message will be thr

204 DB2 Deployment GuidePreparing the Perl application packageThis step is to identify all the Perl application files. In real life, the application

Chapter 4. Deploying applications with DB2 205# main program# process command-line optionscase $# in 0) syntax exit 1;;*) while getopts "

8 DB2 Deployment Guide1.2.4 Other DB2 productsDB2 products are also available in different packaged bundles for specific enterprise requirements.IBM

206 DB2 Deployment Guide# install dbd::db2cd $INSTPATH/$DIR_DRV/DBD*perl Makefile.PLmakemake installprintf "\n Deployment finished.\n"We pl

Chapter 4. Deploying applications with DB2 207Deploying the DB2 Perl applicationThe deployment process for the DB2 Perl application involves transfe

208 DB2 Deployment GuideThe Perl application is now deployed. You can see the subdirectories in the specified location. The Perl application is place

Chapter 4. Deploying applications with DB2 209PrerequisitesBefore you can access DB2 from your .NET application, these prerequisites must be in plac

210 DB2 Deployment GuideThe test application, testconn20.exe, comes with the IBM Data Server Clients and the IBM Data Server Driver. We use this .NET

Chapter 4. Deploying applications with DB2 211Example 4-51 Call to testconn20 and the resulting outputc:>testconn20 "database=itso; server

212 DB2 Deployment Guide

© Copyright IBM Corp. 2008. All rights reserved. 213Chapter 5. Deploying pre-configured databasesIn this chapter we describe how to deploy the pre-con

214 DB2 Deployment Guide5.1 IntroductionFrom a high level view of deploying a pre-configured database, there are four tasks to perform:򐂰 Create the

Chapter 5. Deploying pre-configured databases 215򐂰 Flexibility: Upgrading an existing database might not be as straightforward as it appears:– Keepi

Chapter 1. Introduction to DB2 deployment 9InfoSphere Warehouse (as of 9.5.1)InfoSphere™ Warehouse was called DB2 Warehouse Edition (DWE) in 9.5.0.

216 DB2 Deployment GuideIn Figure 5-1, we depict the ITSODB database.Figure 5-1 The sample database ITSODBYou can download the DDLs used to create

Chapter 5. Deploying pre-configured databases 2175.2.1 Collecting information about the databaseTo deploy a pre-configured database to a new system

218 DB2 Deployment GuideFigure 5-2 Two different table space mapping for ITSODBExample 5-2 shows the database layout DDLs of ITSODB. Note that we d

Chapter 5. Deploying pre-configured databases 219Because the storage layouts on the UNIX system and Windows are different, the file reference syntax

220 DB2 Deployment GuideADD CONSTRAINT "PK_DEPARTMENT" PRIMARY KEY("DEPTNO");-- DDL Statements for indexCREATE INDEX "ITSO&q

Chapter 5. Deploying pre-configured databases 221These are the most common areas requiring your attention:򐂰 Remove obsolete buffer pools and table s

222 DB2 Deployment Guide// Issue this statement to put the table in the right integrity modeSET INTEGRITY FOR itso.adefusr ALLOW NO ACCESS IMMEDIATE

Chapter 5. Deploying pre-configured databases 223or all the statements are executed within the same unit of work. If executing all the statements in

224 DB2 Deployment GuideInvocation of the CLP is identical on Windows and UNIX. The only difference between Windows and UNIX is how the return code i

Chapter 5. Deploying pre-configured databases 225exit 8;;esac򐂰 Example A:In this example we check explicitly for return code 4, which corresponds to

10 DB2 Deployment Guide1 These products can be installed on given platforms but they are not supported for production use (only for development and t

226 DB2 Deployment GuideSetting an exit code in the script is useful if this script will be invoked from another script or from an application. In th

Chapter 5. Deploying pre-configured databases 227The complete Windows exampleExample 5-9 shows the full script used to create our ITSO sample databa

228 DB2 Deployment Guidedb2 -o- -l createdb.log connect resetexit /b 0򐂰 Step 1:We set up the DB2 environment by setting the environment variable DB2C

Chapter 5. Deploying pre-configured databases 229Creating the database from JavaSince there is no call interface for Java, we use a script to genera

230 DB2 Deployment Guide򐂰 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

Chapter 5. Deploying pre-configured databases 231finally {// Step 5 stmt.close();con.close();}򐂰 Step 1:First of all we retrieve a connection to the

232 DB2 Deployment Guide򐂰 Target directories:If the backup image contains restrictions to specific directories, these directories must exist on the t

Chapter 5. Deploying pre-configured databases 233In our example we have the SQL statements in a set of files, where each statement is separated by a

234 DB2 Deployment Guide@REM -----------------------------------------------------------@REM Step 4.b@REM -------------------------------------------

Chapter 5. Deploying pre-configured databases 2355.4.2 Using DB2 utilitiesUsing the DB2 utilities, import, export, and load, to populate the databa

Chapter 1. Introduction to DB2 deployment 111.3.1 New environment versus environment with existing DB2 installationUnless given deployment is to ta

236 DB2 Deployment GuideFor a detailed description of the export utility, refer to the DB2 Infocenter:http://publib.boulder.ibm.com/infocenter/db2luw

Chapter 5. Deploying pre-configured databases 237Because our tables are empty, we choose the insert mode. In Example 5-15 we show the statements to

238 DB2 Deployment Guideload from "staff.ixf" of ixf messages "staff.msg" insert into itso.staff copy no indexing mode autoselect

Chapter 5. Deploying pre-configured databases 239Exporting data with db2moveBy default, db2move exports all the user tables and is always using the

240 DB2 Deployment Guide5.5 Updating an existing installationIt is not uncommon that the changes on the database object definitions are required due

Chapter 5. Deploying pre-configured databases 241Figure 5-3 Identify and apply changes򐂰 Get the configurations for the two databases.򐂰 Compare t

242 DB2 Deployment Guide5.5.2 Updating table objectsWhen it comes to updating the table objects, certain complications arise. Because tables contain

Chapter 5. Deploying pre-configured databases 243We focus our discussion on the complex changes here.Using the stored procedure altobjDB2 provides u

244 DB2 Deployment Guideyears smallint , salary decimal (7, 2) , comm decimal (7, 2)) IN itso2',-1, ? );Note that if the newly added column is a

Chapter 5. Deploying pre-configured databases 245Figure 5-4 illustrates the changes we want to apply to the table itso.staff in our sample database.

12 DB2 Deployment GuideTable 1-3 outlines supported combinations of client and server versions.Table 1-3 Supported client /server matrixNote that w

246 DB2 Deployment GuideDROP TABLE "ITSO"."STAFF";RENAME TABLE "ITSO"."SHADOWTABLE" to "STAFF";ALTE

Chapter 5. Deploying pre-configured databases 247Table 5-4 DB2 metadata for some of the database objects򐂰 Database object dependencies?Table 5-5 l

248 DB2 Deployment GuideComparing two database configurationsIn our sample Java application, both the source and target database configurations are c

Chapter 5. Deploying pre-configured databases 249 AND r.routinetype = 'F' AND d.btype = 'T' AND d.bschema = 'ITSO'

250 DB2 Deployment GuideFigure 5-5 Overview of the Java application򐂰 : The main class in the application is the MigrateMainController. The first ac

Chapter 5. Deploying pre-configured databases 251information about a column, the TriggerInfo class to obtain the information about a trigger, and so

252 DB2 Deployment Guide5.6.1 ScriptsTable 5-6 lists the scripts used in the examples in this chapter.Table 5-6 Scripts containing DDL and SQL sta

Chapter 5. Deploying pre-configured databases 253Table 5-7 Supportive shell scriptsNone of the shell scripts take any arguments. All the scripts s

254 DB2 Deployment Guideexportdb.cmd Export data from the ITSODB database to IXF files. It connects to the ITSODB database and then execute the scrip

Chapter 5. Deploying pre-configured databases 2555.6.3 Java applicationsTable 5-9 lists the Java applications. All Java applications are packaged i

Chapter 1. Introduction to DB2 deployment 13Client/server relationshipIf DB2 servers are taking part in workload balancing, then determining the cor

256 DB2 Deployment GuideEach Java application has a corresponding shell script used to set up the environment and start the execution of the applicat

Chapter 5. Deploying pre-configured databases 257Table 5-10 Shell scripts to start Java applicationsShell script Descriptionjscript.cmd Starts the

258 DB2 Deployment Guide

© Copyright IBM Corp. 2008. All rights reserved. 259Appendix A. Sample applications This appendix provides the application codes of various languages

260 DB2 Deployment Guide** http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.cli.doc/doc/t0007141.html** ** For example

Appendix A. Sample applications 261 /* get connection information from command line arguments */strcpy(ip, argv[1]);strcpy(port, argv[2]);str

262 DB2 Deployment Guide /* disconnect from the database */ printf("\n Disconnecting from the database %s...\n", dbname); cliRC =

Appendix A. Sample applications 263$conn = db2_connect($dsn, '', '');if ($conn) {echo "Connection succeeded.\n";echo &

264 DB2 Deployment Guide ";PROTOCOL=TCPIP" << \ ";UID=" << ARGV[3] << \ ";PWD=&

Appendix A. Sample applications 265# Creating dsn from command line arguments. dsn = "HOSTNAME=" + argv[0] + \ ";PORT=&qu

14 DB2 Deployment GuideTable 1-5 illustrates the software requirements for DB2 9.5.Table 1-5 Software requirement for DB2 9.5DB2 Servers and IBM da

266 DB2 Deployment Guide# Creating dsn from command line arguments.$dsn = "HOSTNAME=" . $ARGV[0] . ";PORT=" . $ARGV[1] .

© Copyright IBM Corp. 2008. All rights reserved. 267Appendix B. Additional materialThis book refers to additional material that can be downloaded from

268 DB2 Deployment GuideUsing the Web materialThe additional Web material that accompanies this book includes the following files:File name Descripti

© Copyright IBM Corp. 2008. All rights reserved. 269Related publicationsThe publications listed in this section are considered particularly suitable f

270 DB2 Deployment Guide򐂰 Getting Started with DB2 Installation and Administration on Linux and Windows, GC23-5857򐂰 Internationalization Guide, SC23-

Related publications 271򐂰 Call Level Interface Guide and Reference, Volume 2, SC10-4225򐂰 Command Reference, SC10-4226򐂰 Data Movement Utilities Guide

272 DB2 Deployment Guide򐂰 Quick Beginnings for DB2 Connect Personal Edition, GC10-4244򐂰 Quick Beginnings for DB2 Connect Servers, GC10-4243Online res

Related publications 273򐂰 DB2 Perl Database Interfacehttp://www.ibm.com/software/data/db2/perl򐂰 Comprehensive Perl Archive Networkhttp://www.cpan.or

274 DB2 Deployment Guide

© Copyright IBM Corp. 2008. All rights reserved. 275IndexSymbols.pdf file 103.prn extension 98Numerics32-bit 21464-bit 214Aabstract class 1

Chapter 1. Introduction to DB2 deployment 15SUSE® Linux Enterprise Server (SLES) 9 Service Pack 3 򐂰 Base Kernel level of 2.6.5򐂰 Libraries glibc-2.3.

276 DB2 Deployment GuideCREATE DATABASE command 217create package 102cursor stability 156custom installation 99DDAS 23dascrt 23, 33dasdro

Index 277Eelevated privileges installation 33embedded SQL application 169embedded SQL statement 154embedding client 5encoding scheme 6encr

278 DB2 Deployment GuideKkey type 61keyword 26, 98KRBPLUGIN 148LLAN connection 131LANG keyword 98language code 96language compiler 154l

Index 279processor rating 21processor technology 21processor value unit 3, 10, 21PROD keyword 98product bundle 8product consideration 10

280 DB2 Deployment Guidesource file 116SQL statement 4, 223SQLCODE 156SQLSTATE 156ssh 41sshd 100, 130standalone database 7standard outp

(0.5” spine)0.475”<->0.875”250 <-> 459 pagesDB2 Deployment Guide

®SG24-7653-00 ISBN 0738431745INTERNATIONAL TECHNICALSUPPORTORGANIZATIONBUILDING TECHNICALINFORMATION BASED ONPRACTICAL EXPERIENCE IBM Redbooks are dev

DB2 Deployment GuideOctober 2008International Technical Support OrganizationSG24-7653-00

16 DB2 Deployment GuideInformation regarding DB2 9.5 installation requirements can be found in:http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/i

Chapter 1. Introduction to DB2 deployment 171.3.3 DB2 product considerationsAs discussed in 1.2, “DB2 9.5 for UNIX, Linux, and Windows products” on

18 DB2 Deployment GuideConnection ConcentratorNo No No YesTable Partitioning No No No YesGovernor No No No YesHomogenous FederationNo Yes with Homoge

Chapter 1. Introduction to DB2 deployment 19In addition to differences in features, each edition also sets different system limits. As shown in Tabl

20 DB2 Deployment GuideFigure 1-2 shows the DB2 9.5 product decision flow.Figure 1-2 DB2 9.5 product selection chart1.3.4 License considerationsDB

Chapter 1. Introduction to DB2 deployment 21An Authorized User is defined as one and only one individual with a specific identity within or outside

22 DB2 Deployment GuideFor further details and the most updated PVU table, refer to:http://www.ibm.com/software/lotus/passportadvantage/pvu_licensing

Chapter 1. Introduction to DB2 deployment 23The non-root/non-Administrator might not be ideal for all because it poses some limitations. Before plan

24 DB2 Deployment Guide򐂰 Configuration Assistant and Control Center are not available on non-root installation.򐂰 Ability for the db2governor to incre

Chapter 1. Introduction to DB2 deployment 25RequirementsThese are the requirements of non-root installation on Windows platforms:򐂰 Non-Administrator

© Copyright International Business Machines Corporation 2008. All rights reserved.Note to U.S. Government Users Restricted Rights -- Use, duplication

26 DB2 Deployment Guide1.3.6 Configuration considerationsDecision to create DB2 instance(s) and Database Administration Server (DAS) instance should

Chapter 1. Introduction to DB2 deployment 27There are various methods for installing DB2 products. Table 1-8 provides a summary of various installat

28 DB2 Deployment Guide

© Copyright IBM Corp. 2008. All rights reserved. 29Chapter 2. DB2 server deploymentIn this chapter, we cover DB2 server deployment considerations and

30 DB2 Deployment Guide2.1 Server deployment planningDB2 server deployment planning should take place at the same time that the application is desig

Chapter 2. DB2 server deployment 31When using the DB2 Setup installation wizard on Linux and UNIX platforms, instance user, fenced user, and adminis

32 DB2 Deployment GuideAdministration server userDB2 Administration server (DAS) provides support for DB2 GUI tools and other administrative tasks, s

Chapter 2. DB2 server deployment 33There are some limitations of the non-root installation of which you should be aware. For DB2 server products, so

34 DB2 Deployment GuideOn Windows Vista, the non-Administrator account still is prompted for administrative credentials by the DB2 Setup Wizard. So t

Chapter 2. DB2 server deployment 35Therefore, it might be necessary to consider remote database connectivity/catalog during the DB2 server deploymen

© Copyright IBM Corp. 2008. All rights reserved. iiiContentsNotices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

36 DB2 Deployment GuideFigure 2-1 DB2 Configuration AssistanceTo export the connectivity information, select from the menu Configure → Export Profi

Chapter 2. DB2 server deployment 37[DB>ZAIRE01:ITSODB]Dir_entry_type=REMOTEAuthentication=NOTSPECDBName=ITSODB[DB>!LOCAL:SAMPLE]Dir_entry_type

38 DB2 Deployment GuideThis command is used to export configuration profile:db2cfexp cf.exp templateThis command is used to import a configuration pr

Chapter 2. DB2 server deployment 39The generated sample.ddl is shown in Example 2-2. When using this script to configure a new database, remember to

40 DB2 Deployment GuideA partitioned database is a database created across multiple database partitions. Each database partition has its own processe

Chapter 2. DB2 server deployment 41Other file system technologies that provide concurrent access to a common set of files can also be used in creati

42 DB2 Deployment GuideCommunication settingsDB2 partitions communicate with each other through TCP/IP. During the planning, ensure that there are co

Chapter 2. DB2 server deployment 43DB2 provides a number of deployment methods for various requirements:򐂰 DB2 Setup Wizard򐂰 db2_install (Linux and U

44 DB2 Deployment GuideFigure 2-2 DB2setup launchpad3. In the menu, clicking Install a Product brings you the introduction of the installation imag

Chapter 2. DB2 server deployment 45– Compact:Compact mode does not include the following components that are included in a Typical mode installation

iv DB2 Deployment Guide2.3.2 DB2 license . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632.3.3 Creating t

46 DB2 Deployment GuideFigure 2-4 Summary of the installation settings8. A log file is created by the DB2 Setup wizard at same time. It is used to

Chapter 2. DB2 server deployment 47Note that db2_install is only available on UNIX and Linux platforms. Some people prefer this method because it by

48 DB2 Deployment GuideEnter "help" to redisplay product names.Enter "quit" to exit.*********************************************

Chapter 2. DB2 server deployment 49The response file installation is supported on UNIX, Linux, and Windows. The command to start a response file dep

50 DB2 Deployment GuideCreating a response file using the DB2 Setup wizardYou can create a response file using the DB2 Setup wizard. This is a recomm

Chapter 2. DB2 server deployment 51The db2rspgn utility automatically creates an instance configuration profile for all instances on current system.

52 DB2 Deployment GuideThe configuration profile file can also be generated on its own through the Connectivity Configuration Export (db2cfexp) tool

Chapter 2. DB2 server deployment 533. Specify the instance information:When the desired components are selected, scroll down in the sample response

54 DB2 Deployment GuideCOMP = INSTANCE_SETUP_SUPPORTCOMP = LDAP_EXPLOITATIONCOMP = DB2_SAMPLE_DATABASECOMP =

Chapter 2. DB2 server deployment 55inst2.FENCED_USERNAME = db2fenc7inst2.FENCED_GROUP_NAME = db2fadm7inst2.FENCED_HOME_DIRECTORY = /home/d

Contents v4.5.1 IBM IBM_DB gem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1814.5.2 Installation of IBM_DB gem. .

56 DB2 Deployment GuideOne of the useful functions presented in the sample program is a way to read progress information and general message from the

Chapter 2. DB2 server deployment 57During the execution of InstallTester, you can see how original progress information and general messages are pro

58 DB2 Deployment Guide2.3 Mass deployment of DB2 server using a scriptIt is not uncommon for an enterprise that requires deploying the DB2 server t

Chapter 2. DB2 server deployment 592.3.1 Setup of SSH and NFSBefore we can start the mass deployment, a secure and convenient authentication channe

60 DB2 Deployment GuideExample 2-10 Accept the authentication key the first time accessing a machine using ssh$ ssh root@balticThe authenticity of

Chapter 2. DB2 server deployment 61Here we use Digital Signature Algorithm (DSA) as the key type. You can use RSA instead. The corresponding command

62 DB2 Deployment GuideAre you sure you want to continue connecting (yes/no)? yesWarning: Permanently added 'baltic,9.43.86.48' (RSA) to th

Chapter 2. DB2 server deployment 63NFS configurationIn a mass deployment scenario, copying the installation image to each target machine is not a co

64 DB2 Deployment Guide2.3.3 Creating the deployment scriptThe SSH and NFS should be configured prior to implementing the DB2 server deployment. The

Chapter 2. DB2 server deployment 65Figure 2-7 Logic of the mass deployment scriptExample 2-18 shows our sample deployment script db2srv_install.Ex

vi DB2 Deployment GuideA.1 C/C++ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259A.2 PHP.

66 DB2 Deployment Guide# command-line syntaxsyntax(){echo " db2srv_install -D|-R -N <NFSpath> -H hostA,hostB,...,hostN -r

Chapter 2. DB2 server deployment 67{set ${setopts?}${RCMD} umount "$LOCALNFS"rc=$?# if unmount fails, try once moreif [ $rc != 0 ]; then

68 DB2 Deployment Guide ${SILENT:+"-n"}" "${TRC:+"-t $TRC"}` ${RCMD} $LOCALNFS/db2_install $INSTALL_PAR

Chapter 2. DB2 server deployment 69do # populate command and do basic testing to ensure ssh can work. RCMD="ssh $host" ${RCMD} hostname

70 DB2 Deployment Guide򐂰 Option -D or -R determines which deployment method is to be used, D for db2_install and R for response file.򐂰 Option -N spec

Chapter 2. DB2 server deployment 71The following example demonstrates the use of the deployment script:db2srv_install -D -N /tmp/v95ga/ese -H baltic

72 DB2 Deployment GuideFor more information see the DB2 installation log at "/tmp/mass_db2dply.log". --------------- Deployment finished

Chapter 2. DB2 server deployment 73Example 2-23 DB2 copies existing on baltic# db2ls--------------------------------------------------------------

74 DB2 Deployment GuideThe general procedure for deploying multiple DB2 servers is as follows:1. Create a response file:– Modify a sample response fi

Chapter 2. DB2 server deployment 75The script then performs silent installation using the provided response file and unmaps the DB2 installation ima

© Copyright IBM Corp. 2008. All rights reserved. viiNoticesThis information was developed for products and services offered in the U.S.A. IBM may not

76 DB2 Deployment Guide2.4.1 Fix pack overviewA fix pack is a package that contains updates and fixes for reported problems (also known as Authorize

Chapter 2. DB2 server deployment 77򐂰 DB2 fix pack for non-server products:This type of fix pack can only be used if you do not have DB2 server insta

78 DB2 Deployment GuideDeployment methodVarious methods are available for a fix pack deployment, as summarized here:򐂰 UNIX and Linux:– Install the fi

Chapter 2. DB2 server deployment 79is located. The temporary path for the fix pack image is /tmp/v95fp1/ese as shown in Figure 2-8. It is exported t

80 DB2 Deployment Guide򐂰 After deployment has finished on one machine, unmount NFS and go to the next target host.Figure 2-9 shows the script logic f

Chapter 2. DB2 server deployment 81In the fix pack deployment script:򐂰 Option -N specifies the NFS path that has been exported on the file server. I

82 DB2 Deployment Guide# -b base_install_path -c image_location# -f level -f db2lib -f NOTSAMP -f install|update#

Chapter 2. DB2 server deployment 83umountnfs(){set ${setopts?}${RCMD} umount "$LOCALNFS"rc=$?# if unmount fails, try once moreif [ $rc !=

84 DB2 Deployment Guidereturn 0}# deploy DB2 fixpack using installFixPackdb2fp_dply(){set ${setopts?}# check if $INSTPATH exists${RCMD} test -d $INST

Chapter 2. DB2 server deployment 85 f) FOPT="$FOPT""-f $OPTARG " ;; # force options can be combined l) LOGFILE=$