what’s new for db2 clients and drivers? lots! · pdf filewhat’s new for db2...

© 2010 IBM Corporation

1

What’s new for DB2 Clients and Drivers?

LOTS!

Paul [email protected]

December 2015

V4

V17

mailto:[email protected]


2

Information concerning non-IBM products was obtained from the suppliers of those products, their published announcements, or other publicly available sources. IBM has not tested

those products and cannot confirm the accuracy of performance, compatibility, or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products

should be addressed to the suppliers of those products.

The information on the new products is intended to outline our

general product direction and it should not be relied on in making a

purchasing decision. The information on the new products is for

informational purposes only and may not be incorporated into any

contract. The information on the new products is not a commitment,

promise, or legal obligation to deliver any material, code or

functionality. The development, release, and timing of any features

or functionality described for our products remains at our sole

discretion.

This information may contain examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples include the names of

individuals, companies, brands, and products. All of these names are fictitious, and any similarity to the names and addresses used by an actual business enterprise is entirely

coincidental.

Trademarks The following terms are trademarks or registered trademarks of other companies and have been used in at least one of the pages of the presentation:

The following terms are trademarks of International Business Machines Corporation in the United States, other countries, or both: DB2 Universal Database, eServer, FlashCopy, IBM,

IMS, iSeries, Tivoli, z/OS, zSeries, Guardium, IBM Smart Analytics Optimizer, Data Encryption Tool for IMS and DB2 Databases, DB2 Administration Tool / DB2 Object Compare for

z/OS, DB2 Audit Management Expert for z/OS, DB2 Automation Tool for z/OS, DB2 Bind Manager for z/OS, DB2 Change Accumulation Tool for z/OS, DB2 Cloning Tool for z/OS, DB2

High Performance Unload for z/OS, DB2 Log Analysis Tool for z/OS, DB2 Object Restore for z/OS, DB2 Path Checker for z/OS, DB2 Query Management Facility for z/OS, DB2 Query

Monitor for z/OS, DB2 Recovery Expert for z/OS, DB2 SQL Performance Analyzer for z/OS, DB2 Table Editor for z/OS , DB2 Utilities Enhancement Tool for z/OS, DB2 Utilities Suite for

z/OS, InfoSphere Change Data Capture, InfoSphere Data Event Publisher, InfoSphere Replication Server, Optim Data Growth Solution for z/OS, Optim Development Studio, Optim

pureQuery Runtime, Optim Query Workload Tuner, Optim Test Data Management Solution for z/OS, Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS

EMC and TimeFinder are trademarks of EMC Corporation

Hitachi is a traademark of Hitchi Ltd

Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both.

Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

Other company, product, or service names may be trademarks or service marks of others.

Disclaimer/Trademarks


3

Learning Objectives

Title: What’s new for DB2 Clients and Drivers?

Abstract: IBM’s strategy is to remove the reliance on the DB2 Connect Gateways and

replace DB2 Connect with the IBM Data Server Driver or Clients. While DB2 Connect

licenses (in the form of DB2 Connect license files) are still required, you can replace

DB2 Connect Gateways with the IBM Data Server Driver or Clients and receive

equivalent or superior function. In addition, you can reduce complexity, improve

performance, and deploy application solutions with smaller footprints for your business

users. IBM has also simplified the configuration of clients and drivers by replacing the

database directory with a single XML configuration file.

Objective: The purpose of this presentation is to review changes and enhancements in

creating connections to IBM’s relational databases.

Audience: Architects, Database Administrators, DB2 Connect Support Staff and,

Developers.

Duration: 75 Minutes


4

What’s new for DB2 Clients and Drivers?Agenda Highlights

IBM’s Strategy Is To Remove The Reliance On The DB2

Connect Gateways

IBM Data Server Clients and Drivers

IBM Data Server Driver Package (ds driver)

Documentation and References


5


6

InfoSphere Optim Configuration Manager – (OCM)

Client Management

Identify connections to DB2

Track client/driver versions and parameters

Monitor changes to client and database

Correct client connect parameters

Explore subystems, database, instances and more

Server Management

Redirect connections to different databases

Correct workload balancing and

Performance Management

Change connection parameters

Direct connections to different profiles for:

RLF, DB2 10 Connection Profiles or WLM Service Classes

Correct improperly classified connections

Direct problem applications to penalty boxes

Paul thinks of this as “Super DB2 Connect”...


7

Gaining Insights – Data Tools Runtime Client

• The Vision…

• Aware client (managed client) exchanges information and processing instructions with the

management servers – The very smart driver

Application Environment

DB2 for z/OS

IBM Data

Server Driver

OMPE

Server

DTRC

Client

DB2 for LUWOPM

Server

OCM

Server

pQ

Server


8

DB2 Client and Driver – Some Highlights

Most recent fix packs DB2 10.5 FP6, 10.1 FP5 and 9.7 FP11

DB2 10.5 FP6 – Maintenance release for DB2 LUW

DB2 Version 10.5 Fix Pack 5 adds native database encryption to

the DB2 database server. Note: Several driver versions exclude

support for this feature

DB2 10.5 FP 4 –

Support for Windows 8 and 2012 R2

Visual Studio 2013

DB2 10.1 FP 2 or 9.7 FP6 - DB2 z/OS 11 Driver/Client/Connect

Support

DB2 10.1 FP 2 - Visual Studio 2012

DB2 9.7 FP 6 – SSL with Auth. Client

DB2 9.1 FP 1 – DB2 z/OS 10 Driver/Client/Connect


9


IBM’s Strategy Is To Remove The Reliance On The

DB2 Connect Gateways

–Review of Simplified Database Connections





10

DB2 Connect overview

DB2 Connect provides fast and robust connectivity to IBM® mainframe databases for applications running on Linux, UNIX, and Windows operating systems.

DB2® for i, DB2 for z/OS®, and DB2 Server for VM and VSE continue to be the database systems of choice for managing critical data for the largest organizations in the world. There is a great demand to integrate this data with applications running on Linux, UNIX, and Windows operating systems.

DB2 Connect has a number of DB2 Connect server products that are several connection solutions. A DB2 Connect server is a server that concentrates and manages connections from multiple desktop clients and Web applications to DB2 database servers running on mainframes or IBM Power Systems™ servers. You can replace DB2 Connect server with DB2 Connect client and receive equivalent or superior function. In addition, you can reduce complexity, improve performance, and deploy application solutions with smaller footprints for your business users.

DB2 z/OS Lab Strategic Goal:Avoid the use of DB2 Connect Gateways

Source: lnformationCenter DB2 V10.5 LUWhttp://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.wn.doc/doc/c0023092.html?lang=en


11

IBM Data Server Driver and Clients as requesters: DB2 Connect has several connection solutions, including DB2 Connect Personal Edition and

a number of DB2 Connect server products. A DB2 Connect server is a server that

concentrates and manages connections from multiple desktop clients and Web applications to

DB2 database servers running on mainframes or IBM Power Systems™ servers. It is

strongly recommended that you use a DB2 Connect client, notably the IBM data server

drivers and clients, instead of the DB2 Connect server. IBM data server drivers and

clients provide the same connection and application development functionality as the

DB2 Connect server. However, you can reduce complexity, improve performance, and

deploy application solutions with smaller footprints for your business users. DB2

Connect license files are required.

With DB2 for LUW Version 9.5 FixPack 3 and later you can implement the DRDA AR

functions for your distributed applications with varied degrees of granularity. Instead of the

current function and large footprint of the DB2 Connect Server, you can choose from the IBM

Data Server Drivers, the IBM Data Server Runtime Client, and the IBM Data Server Client.

The IBM Data Server Drivers include:

– IBM Data Server Driver Package

– IBM Data Server Driver for JDBC and SQLJ

DB2 z/OS Lab Strategic Goal:Avoid the use of DB2 Connect Gateways

Source: lnformationCenter DB2 V9.7 LUWhttp://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.qb.dbconn.doc/doc/r0059046.html


12

DB2 z/OS Lab Strategic Goal:Elimination of DB2 Connect Gateways… Elimination of DB2 Connect Gateways Provides:

– Simplifies Network Topology

– Application server direct connection to DB2 z/OS

– Lowers DB2 Total Cost of Computing (TCO)

– No other DBMS uses gateways

– Improves Performance

– Elimination of network hop and DB2 Connect Gateway routing

– Improves Availability

– DB2 Connect Gateway clustering eliminated

– Simplifies Code Maintenance

– Update code of clients/drivers only and no coordination of gateway code changes

– DB2 database base features are enabled in the driver and not gateways

– To support a new version of DB2 requires updating the driver and gateways Just Add DB2 Connect License File to Client or Driver or use db2connectactivate utility Be careful when considering the elimination of DB2 Connect Gateways that you don’t violate

current DB2 Connect licensing agreement. If your company has any other version than DB2

Connect Unlimited Edition, I would suggest contacting your IBM Sales Specialist to verify

compliance

Multi-transport models such as Tuxedo, WebLogic, and Encina still require a middle-tier DB2

Connect Server.

No gateway to blame for

Performance issues

Aka “We have a DB2 Connect

Problem”


13

DB2 Client and Driver – Some HighlightsCentralized License V9.7 FP3

The db2connectactivate utility activates the license for DB2 Connect™

Unlimited Edition for System z® on any DB2® for z/OS® subsystem that

you want to access directly.


14

DB2 Client and Driver – Some HighlightsThe db2connectactivate utility (Continued)

Documentation available at:http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.licensing.doc/doc/r0057377.html

Included with IBM DB2 Connect 9.7 Unlimited Edition for System z Quick Start

and Activation download entry from Passport Advantage

The above command creates the routine SYSIBM.DB2CON_LIC in the DB2 subsystem


15

To make direct connections(Don’t forget direct connections depend on the appropriate DB2 Connect license)

Add the DB2 Connect license using the db2connectactivate utility to the DB2

z/OS subsystem

Place the DB2 Connect Java license db2jcc_license_cisuz.jar to the

classpath for Java

Use the db2licm utility to add the DB2 Connect license to the client

db2consv_zs.lic – Example: DB2 Connect Unlimited Ed. License


16


IBM’s Strategy Is To Remove The Reliance On The DB2 Connect

Gateways


–Review of Clients and Drivers Available

–Deployment Strategies

–Review of Client Setup




17

IBM Clients and Drivers – DB2 V10.1 LUW

IBM data server client and driver typesThe following lists the IBM data server clients and drivers:

– IBM Data Server Driver Package(DS driver)

– IBM Data Server Driver Package - Without Encryption

– IBM Data Server Driver for JDBC and SQLJ

– IBM Data Server Driver for ODBC and CLI

– IBM Data Server Driver for ODBC and CLI - Without Encryption

– IBM Data Server Runtime Client

– IBM Data Server Client

You can add DB2 Connect™ capability to any client or driver.

The IBM Data Server Driver Package is a lightweight deployment solution that provides runtime support for applications without the need to install the Data Server Runtime Client or Data Server Client. This driver has a small footprint and is designed to be redistributed by independent software vendors (ISVs) and to be used for application distribution in mass deployment scenarios that are typical of large enterprises.

It is also recommended that the IBM Data Server Driver Package be installed first. It can then be configured to work in conjunction with DB2 Connect™.

In addition, a separate product, DB2 Connect Personal Edition, includes all the functionality of IBM Data Server Client plus the capability to connect to midrange and mainframe databases.

Source DB2 V10.1 LUW Information Center:http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0023452.html


18

IBM Clients and Drivers

IBM DB2 Driver for JDBC and SQLJ (Aka the Java Common Client (JCC)/Universal Driver/Type-4 driver) is a 9 MB fully redistributable client provides JDBC and SQLJ applications access to DB2 data servers. If you are connecting to a DB2 for System i or DB2 for System z data server, you are still required to purchase the DB2 Connect product.

IBM Data Server Driver Package (aka - ds driver): is a 70MB lightweight deployment solution for Windows applications. For applications using ODBC, CLI, .NET, OLE DB, PHP, Ruby, JDBC, or SQLJ, use . IBM Informix Dynamic Server support for .NET, PHP, and Ruby. Combines

The IBM Data Server Runtime Client: is approximately 184MB in size and provides a means for applications to be executed against remote DB2 databases. GUI tools are not shipped with the IBM Data Server Runtime Client. Support for common database access interfaces: JDBC, ADO.NET, OLE DB, ODBC, DB2 Command Line Interface (CLI), PHP, and Ruby. This includes drivers and capabilities to define data sources. IBM Informix Dynamic Server support for PHP, Ruby, .NET and JDBC

The IBM Data Server Client: is a 360MB collection of client application drivers and tools for DB2 and Informix IDS data servers. The set of application drivers for DB2 include: embedded SQL, ODBC/CLI, JDBC/SQLJ, OLEDB, .NET, PHP, Perl, and Ruby. Data access and administration tools are providing including: DB2 Control Center (Windows and Linux) and DB2 Command Line Processor (CLP). The set of application drivers for Informix IDS v11.10, or later, include: JDBC, .NET, PHP, and Ruby. The IBM Database add-ins for Visual Studio 2005 are provided to simplify data access for .NET applications for both DB2 and IDS data servers.

Notes:

– DB2 Control Center and other Client GUI tools are deprecated in DB2 LUW 9.7

– DB2 10 for z/OS Announcement Memo states that DB2 Control Center is not supported

– Use IBM Data Studio and Optim Development Studio (Included with DB2 Connect) for workstation GUI tools – These include the Java Type-4 driver (no-charge) and do not need client/driver.

– IBM Data Server Driver for ODBC, CLI - Included on download site but is intended for ISV applications only


19

Choosing the right Client/Driver

There is a functional overlap

Should balance functionality with footprint


20

Client Deployment Strategies Majority of client workstations are runtime oriented

– End user workstations

– App servers Runtime deployment IBM Data Server Driver

– IBM Data Server Driver is highly recommended - Lightest runtime focused client package

– Common client configuration across multiple levels

– No migration to move to newer levels - No database directory Developer deployment

– Essentially runtime plus tooling

– Start with ds driver

– Add Tooling package:

– Data Studio for Java

– Microsoft Visual Studio Add-Ins for .NET

(IBM Data Sever Package includes ADO.Net provider) Administrator deployment

– Using CLP for administration - Investigate use of CLP Plus

– Use Data Server Runtime Client DBA and All others

– Use Data Server Client Reminder: The larger the client package, the more that is included, the more that needs to be

rebound

Large

MB

Small

MB


21

DB2 V9.7, 10.1 and 10.5 Clients and fix packs:http://www.ibm.com/support/docview.wss?uid=swg27007053

Documentation can be downloaded from: http://www.ibm.com/support/docview.wss?uid=swg27038430

http://www.ibm.com/support/docview.wss?rs=71&uid=swg27009474


22

So… What would I see at the download site?current client and driver names

Notice the Runtime Client and Client are still available

IBM Data Server Driver Package


23

1.

DB2 Client and DB2 Connect connection Setup

Excel

IBM Client

Web

Application server

IBM Data Server Client

DB2 Connect

Gateway servers

IBM Client

**Web Application server With DB2 Connect or

Type-4 Java Driver or Data Server Driver

IBM Client

Internet Explorer

DB2 Connect PE

3.

2.

DB2 z/OS

Subsystem Name: DB2T

DNS: my.sysz.com

Port: 446

Location Name: SYSZDB2T

DB2 Client:

DNS: my.db2conn.com

Port: 50000


DB2 Connect/PE:

DNS: my.sysz.com

Port: 446


DB2 Connect:

DNS: my.sysz.com

Port: 446



24

Examples of manually cataloging client and gateway connections – Using DB2 Client Catalog commands

DB2 client catalog statements:

– catalog tcpip node ndcdb205 remote my.db2conn.com server 50000

ostype os390

– catalog db ndcdb205 at node ndcdb205

DB2 gateway catalog statements:

– catalog tcpip node ndcdb205 remote my.sysz.com server 446 ostype

os390

– catalog db ndcdb205 at node ndcdb205

– catalog dcs database ndcdb205 as ndcdb205

(Note: the dcs connection identifies this as a “host” connection)


25

Catalog commands:

Node entry:

– Nick name for: communication protocol, and network location

DB entry:

– Database name and alias database name if two database have

the same name

– Authentication - SERVER, SERVER_ENCRYPT, CLIENT,

KERBEROS, and DATA_ENCRYPT

DCS (aka host) entry:

– Identify remote database as z/OS, OS/400, VSE or VM

– Additional parameters:

– SQLCODE map-file, disconnect processing, INTERRUPT_ENABLED, SYSPLEX, LOCALDATE format, Bidirectional CCSID


26

Configuration Assistant - GUI to setup connections

In addition to configuring database and testing connections, connection profiles can be created too. Connection profiles are useful for replication of client setup. Note: ADO.Net

provider is not include in the “Test Connection” tool.

Test connections with any of the drivers…

Not available in 10.1 and 10.5 anymore….

See “db2cli writecfg “for another option


27

ODBC Data Source Administrator

The Microsoft ODBD Data Source Administrator tool can update the db2cli.ini file with DB2 properties


28


IBM’s Strategy Is To Remove The Reliance On The DB2 Connect

Gateways



–Background and Installation

–New tools:

–db2cli.ini, db2dsdriver.cfg

–db2dsdcfgfill, db2cli registerdsn, db2cli validate and

CLPPlus

–SysPlex Setup



29

IBM Data Server Driver Package IBM Data Server Driver Package is a lightweight deployment solution that provides runtime support for

applications using ODBC, CLI, .NET, OLE DB, PHP, Ruby, JDBC, or SQLJ without the need of installing Data Server Runtime Client or Data Server Client. This driver has a small footprint and is designed to be redistributed by independent software vendors (ISVs), and to be used for application distribution in mass deployment scenarios typical of large enterprises.

The IBM Data Server Driver Package capabilities include:

– The DB2 Command Line Processor Plus (CLPPlus) for dynamically creating, editing, and running SQL statements and scripts.

– Support for applications that use ODBC, CLI, PHP, or Ruby to access databases.

– Support for client applications and applets that are written in Java using JDBC, and for embedded SQL for Java (SQLJ).

– IBM Informix® Dynamic Server support for .NET, PHP, and Ruby.

– Support for running embedded SQL applications. No precompiler or bind capabilities are provided.

– Application header files to rebuild the PHP, Ruby, Python, and Perl drivers. The Python and Perl drivers are not available in IBM Data Server Driver Package; however, you can download and build these drivers using the header files.

– Support for DB2 Interactive Call Level Interface (db2cli).

– Support for the for DRDA® traces (db2drdat). On Windows® operating systems, IBM Data Server Driver Package also provides support for

applications that use .NET or OLE DB to access databases. In addition, this is available as an installable image, and merge modules are available to allow you to easily embed the driver in a Windows Installer-based installation.


30

IBM Data Server Driver Package DB2 LUW 9.5

Installation/Configuration ODBC, CLI, OLE-DB and.NET or open source (Perl, PHP)

No database directory

The db2dsdriver.cfg file is XML based,

A schema file – dsdriver.xsd, is shipped to allow client side validation of the

file

The db2dsdcfgfill command copies the existing database directory information

from either the existing IBM Data Server Client or IBM Data Server Runtime

Client into the db2dsdriver.cfg configuration file

No migration of the database directory to move to newer levels

Add valid DB2 Connect license key(s) to the directory:

C:\Program Files\IBM\IBM DATA SERVER DRIVER\license


31


Installation/Configuration (continued): Connection information is recorded in either:

– Text file: db2cli.ini

– XML file: db2dsdriver.cfg

– db2cli.ini has priority over db2dsdriver.cfg

• db2cli registerdsn utility will register all the Data Sources available in the

db2cli.ini file or the db2dsdriver.cfg file. The data sources can be registered

either as a System Data Source or as a User Data Source (See IBM DB2

Dirver Package section for more details on this utility).

• db2cli registerdsn -add -alldsn –system

• db2cli validate utility wil validate and tests the configuration files of: db2cli.ini

and db2dsdriver.cfg

• Command Line Processor Plus (CLPPlus) provides a command line user

interface that you can use to connect databases and to define, edit, and run

statements, scripts, and commands


32


Installation/Configuration (continued):• To migrate from the Runtime Client or Client to the IBM Data Server Driver

Package

• Uninstall Runtime Client or Client

• Install the IBM Data Server Driver Package (Response file available)

• Note: Microsoft Global Assembly Cache (GAC) Automatically Updated During Installation

• Include the database connections in the db2dsdriver.cfg (db2cli.ini

optional)

• Include the DB2 Connect license for direct connections to DB2 z/OS

• Optional: Run “db2cli registerdsn” to update ODBC Data Source

Administrator for ODBC applications

• For DB2 CLI applications see Information Center:


33

Data Server Driver Package Installation

New Directory

SQLLIB is not used with Data Server Driver Package


34

Data Server Driver Package – DB2 Connect License

Place the desired DB2 keys in the license directory

–db2consv_zs.lic – Example: DB2 Connect Unlimited Ed. License

–db2jcc_license_cisuz.jar – Example Java Driver License

(Don’t forget to add to classpath for Java)

No db2licm utility! Also Remember 9.7 FP3 - The db2connectactivate utility activates the license

for DB2 Connect™ Unlimited Edition for System z® on any DB2® for z/OS®

subsystem that you want to access directly.


35

Data Server Driver PackageNew location for driver information

Location:

Pre. Win. 7: C:\Documents and Settings\All Users\Application Data\IBM\DB2\IBMDBCL1\cfg

Win. 7 and later: C:\ProgramData\IBM\DB2\DB2COPY1\cfg

No DB2 Client Directory

Connection information stored in files:

–db2cli.ini or db2dsdriver.cfg


36

Sample db2cli.ini file

Information on db2cli.ini file setup:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.do

c/doc/c0007882.html

Information on db2cli.ini parameters see: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/

doc/r0007964.html


37

Sample db2dsdriver.cfg provided – XML FileAlias Section –

Allows more than

one reference to

a database

Database Section –

Allows only one

reference to a database

provides additional

parameters

Global Parameter Section –

Default parameters for all

defined databases


38

Sample db2dsdriver.cfg provided –Data Studio XML Designer


39

My db2dsdriver.cfg

Alias

Database/

Location

Name DSName IP/Port

Additional

DB Parameters

For NDC05

Two

names

for

NDC05


40

db2dsdriver.xsd XML Schema Provided


41

IBM Data Server Driver Configuration Parameters Available

IBM data server driver configuration keywords db2dsdriver.cfg See:

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.swg.im.dbclient.confi

g.doc/doc/c0054698.html?cp=SSEPGG_10.5.0%2F2-0-2-5-7-0-7&lang=en


42

db2dsdcfgfill Tool to create db2dsdriver.cfg file from your existing DB2 Client or DB2 Runtime setup

Tool is not included with the drivers

Good starting point to create your XML configuration point


43

db2dsdcfgfill continueddb2dsdcfgfill -i db2 -o c:\

db2dsdriver.cfg


44

db2cli registerdsn feature

registerdsn

– Specifies the db2cli register DSN mode. Use this command parameter to register an ODBC data source name.

– -add dsn_name

– Adds a system or user ODBC data source to the Microsoft ODBC Data Source Administrator. The dsn_name variable specifies a single data source to be added or all data sources present in the db2cli.ini and db2dsdriver.cfg file may be added using the -alldsn parameter. The value of dsn_name must be the data source name and not the DBALIAS name. The data source name must be present in the db2cli.ini or db2dsdriver.cfg file.

– -alldsn

– Specify this parameter to register all the data sources available in the db2cli.ini and db2dsdriver.cfg file. This parameter must be used with the -add parameter.

– -user

– Specifies to register a user ODBC data source. This is the default if no parameter is specified.

– -system

– Specifies to register a system ODBC Data Source

– -remove dsn_name

– Removes a system or user ODBC Data Source from the Microsoft ODBC Data Source Administrator.

– -list

– List all the system or user IBM Data Server ODBC Data Sources registered in the Microsoft ODBC Data Source Administrator.

– -help

– Displays help information related to the db2cli register DSN mode.


45

db2cli registerdsn… db2cli registerdsn -add -alldsn -system

The –system parameter approximates the

DB2 cli catalog command that registers

ODBC data sources


46

db2cli32.exe - Windows 7 32-bit and 64-bit Support

Customers that are migrating to Windows 7 seem to be adopting the 64-bit O/S implementation, which supports both 32-bit and 64-bit applications. Starting with DB2 Version 9.7 Fix Pack 4, you can use the db2cli32 command and the db2cli command, if you are using 32-bit and 64-bit ODBC applications in Windows 7 64-Bit O/S. For example:

db2cli registerdsn -add sampledsn –systemdb2cli32 registerdsn -add sampledsn –system

When using the DS “Driver Package” the db2dsdriver.cfg file will be used as input for the db2cli32 command. This will allow registering of the database located in the db2dsdriver.cfg for both 32-bit and 64-bit ODBC database connections.

When using the DS “Client” – the normal catalog ODBC command will register 64-Bit ODBC database connections. Use the db2cli32 command to register 32-Bit ODBC database connections. The db2cli32 command will search for cataloged databases in the DB2 Client instance and the db2dsdriver.cfg file.


47

db2cli32.exe - Windows 7 32-bit and 64-bit Support...

Creating odbc dsn to the alias

Create a system dsn for dsn alias sampledsn using the following command:

You can test the newly created ODBC dsn using Microsoft applications like MS Excel.

Procedure

1. Launch odbc administrator tool from Control Panel->Administrative Tools-> Data Sources (ODBC) for 64 bit binary.

For 32-bit binary in a 64-bit machine, launch odbc administrator tool from System

Drive:\windows\SysWOW64\odbcad32.exe. Example: c:\windows\syswow64\odbcad32.exe

2. The list of user data sources is displayed, by default. Click System DSN tab.

3. Select the newly created dsn (sampledsn in our example) and click Configure.

4. Provide user name, password and click Connect. The message "Connection tested successfully" is displayed.

5. Now launch Microsoft applications like MS Excel and use the newly created dsn. To do the same, bring up MS

excel. For example,

1. Launch MS Excel 2003.

2. Go to Data->Import External Data-> New Database Query. The list of odbc dsn's is displayed in a list box.

Note: A 32-bit excel application will show only 32-bit dsn's in the list box and 64-bit excel will show only 64-bit

dsn's in the list box.

3. Select the dsn that (sampledsn in our example) you want to connect to, and provide the login details.

4. The list of tables is displayed in the database.


48

db2cli validate feature

Validates and tests the db2cli.ini or db2dsdriver.cfg files environment configuration.

– -dsn dsn_name

– Specify the data source name to validate.

– -database database:server:port

– Specify the database name, server and port number to validate. The information must

be specified using a colon to separate the database name, server and port number.

For example, TESTDB:dbserver.example.com:19677

– -connect

– db2cli validate will connect to a specified data source name or database and output

information about the connection attempt.

– -user username - Specifies the username to use for the connection.

– -passwd password - Specifies the password to use for the connection.

– -f OUTPUT_FILE

– Specify an optional output file for the results of the db2cli command.

– -help

– Displays help information related to the db2cli validate mode.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.swg.im.dbclient.install.doc/doc/r0058814.html

For more details see InfoCenter link below:


49

db2cli validate

Command: db2cli validate

Can be used to:

–Check the syntax of your db2dsdriver.cfg

–Check the driver version


50

db2cli validate

Can be used to:

–Check the parameters

being used by a

connection entry

–Check the ODBC Data

Source Administrator

parameters being used

too

Command: db2cli validate – dsn data_source_name


51

db2cli writecfg

• Updates the db2dsdriver.cfg configuration file.

• The db2cli writecfg command does not verify the syntax or

validate the value of the specified parameter.

db2cli writecfg add –dsn alias2 –database name2 -host server1.net1.com -port 50001

<dsncollection>

<dsn alias="alias1" name="name1" host="server1.net1.com" port="50001">

<parameter name="DisableAutoCommit" value="TRUE"/>

</dsn>

<dsn alias="alias2" name="name2" host="server1.net1.com" port="50001"/>

</dsncollection>

db2cli writecfg add –database name2 –host server1.net1.com -port 50001 -parameters “LockTimeout=20;

KeepAliveTimeout=20000”

<databases>

<database name="name1" host="server1.net1.com" port="50001">

<parameter name="CurrentSchema" value="OWNER1"/>

</database>

<database name="name2" host="server1.net1.com" port="50001">

parameter name=" LockTimeout" value="20"/>

parameter name="KeepAliveTimeout" value="20000"/>

</database>

</databases>


52

Order of precedence for obtaining database information


53

CLPPlus

DB2 Command Line Processor Plus (CLPPlus) for dynamically

creating, editing, and running SQL statements and scripts.

Supports aliases in db2dsdriver.cfg file.

Note: CLPPLUS is not installed automatically


54

Sample of a CLPPlus connection –- IBM Data Server Driver Package – Java Direct Connection- Replacement for Command Window and Command Line Processor


55

CLI/.NET Driver

Logical

Connection

3

Logical

Connection

1

Logical

Connection

2

disconnect

at commit/rollback

pooled connections

to DB2 server

Transport

1

Transport

2Thread

3

Thread

1

Thread

2

C Process

Sysplex Workload Balancing using the IBM Data Server Driver

DB2 Group

DB2 forz/OS

Note: CLP does not support Sysplex WLB

CF

DB2 forz/OS


56

IBM Data Server Driver Package PropertiesProperties for fine-tuning Sysplex workload balancing for direct connections from non-Java applications to DB2 for z/OS


57

IBM Data Server Driver Package Properties…

Properties to enable Sysplex workload balancing for connections from non-Java applications to DB2 for z/OS


58

IBM and Continues Improvement for Microsoft .Net…

If the failure occurs on the first SQL operation in a transaction, and seamless failover is enabled (and applicable), the client can replay the failed SQL operation as part of Automatic Client Reroute (ACR) processing. If the connection is successful, no error is reported to the application.

If ACR is enabled, an application that encounters a connectivity failure is routed to another member of the Sysplex. If seamless failover is disabled or not applicable, the error SQL30108N is returned to the application.

To perform ACR, the client must use a TCP/IP connection and have a DB2® Connect™ license. The following Version 9.5 Fix Pack 3 or later clients provide support for ACR:

IBM® Data Server Client, IBM Data Server Runtime Client, IBM Data Server Driver Package

Automatic client reroute settings in the db2dsdriver.cfg configuration file enableAcr: Specifies whether the ACR feature is on (true) or off (false). This setting defaults to

true when transaction-level load balancing is enabled (the enableWLB setting is true). If ACR is disabled, any other settings for ACR are ignored.

enableSeamlessAcr: Specifies whether the ACR with seamless failover feature is on (true) or off (false). If ACR is enabled and the target server is DB2 for z/OS, seamless ACR is enabled by default. If the target server is not DB2 for z/OS, this setting is ignored, and seamless ACR is not supported.


59

Sample WLB Configuration (part of db2dsdriver.cfg)

<databases><database name="STLEC1" host="9.30.30.5" port="446">

<WLB><parameter name="enableWLB" value="true"/><parameter name="maxTransports" value="100"/><parameter name="maxTransportIdleTime" value="600"/><parameter name="maxTransportWaitTime" value="30"/><parameter name="maxRefreshInterval" value="30"/>

</WLB><ACR>

<parameter name="enableACR" value="true"/><parameter name="enableSeamlessACR" value="true"/>

</ACR></database>

</databases>


60

60

InfoSphere Optim Configuration Manager – (OCM)

Client Management

Identify connections to DB2

Track client/driver versions and parameters

Monitor changes to client and database

Correct client connect parameters

Explore subystems, database, instances and more

Server Management

Redirect connections to different databases

Correct workload balancing and

Performance Management

Change connection parameters

Direct connections to different profiles for:

RLF, DB2 10 Connection Profiles or WLM Service Classes

Correct improperly classified connections

Direct problem applications to penalty boxes

Paul thinks of this as “Super DB2 Connect”...


61


IBM’s Strategy Is To Remove The Reliance On The DB2

Connect Gateways


–Review of Clients and Drivers Available

–Client Deployment Strategies




62

DB2 Client Monitoring and Tuning

DB2 Connect User’s Guide – 10.5

– Chapter 6 – Monitoring DB2 Connect Server

– Chapter 9 - Tuning

– Chapter 10 - Trouble Shooting DB2 Connect

DB2 9 for z/OS: Distributed Functions

(Redbook SG24-6952)

– Chapter 7 Performance analysis

– Chapter 8 Problem determination


63

DB2 9 for z/OS: Distributed FunctionsMy favorite and a MUST have!

DB2® for z/OS® Distributed Data Facility (DDF) is a built-in component that provides the connectivity to and from other servers or clients over the network. DDF is a full-function DRDA-compliant transaction monitor which, equipped with thread pooling and connection management, can support very large networks. Different z/OS workload management priorities can be assigned to different, user-specified classes of DDF-routed application work.

In this IBM® Redbooks® publication, we describe how to set up your DDF environment and how to deploy the DDF capabilities in different configurations, including how to develop applications that access distributed databases.

www.redbooks.ibm.com/abstracts/sg246952.html


64

Questions?


65