DB2 Version 9.7 for Linux, UNIX, and Windows

Validating IBM Data Server Driver Package (Windows) installation

In DB2® Version 9.7 Fix Pack 3a and later fix packs, you can validate installation of the IBM® Data Server Driver Package for the most popular interfaces, such as CLI/ODBC, ADO.NET and Visual Studio.

You can download the IBM Data Server Driver Package by using the following link: https://www-304.ibm.com/support/docview.wss?rs=4020&uid=swg27016878.

After installing IBM Data Server Driver Package, you can validate the installation for CLI / ODBC using the db2cli validate command.

Use the succeeding steps to validate your installation:
  1. Create an alias in the db2dsdriver.cfg file. You can then populate the configuration file with DSN alias name, database name, hostname, and the port to which connection is to be made. In DB2 Version 9.7 Fix Pack 4 and later fix packs, you can obtain the db2dsdriver.cfg path for IBM Data Server Driver Package on Windows by running the db2cli validate command to show the db2dsdriver.cfg file location.
  2. Validate the DSN alias with db2cli application.
  3. Create an ODBC DSN to the alias.
  4. Test the ODBC DSN with MS Excel.

Sample db2dsdriver.cfg

The following sample db2dsdriver.cfg file is configured with DSN alias as sampledsn, database name as sample, hostname as samplehost.domain.com and port as 19766.

<configuration>    
  <dsncollection>       
   <dsn alias="sampledsn" name="sample" host="samplehost.domain.com" port="19766"/>          
  </dsncollection>     
  <databases>       
   <database name="sample" host="samplehost.domain.com" port="19766">                
   </database>          
  </databases> 
</configuration>

List of client packages installed

In Version 9.7 Fix Pack 6 and later IBM data server clients (the IBM Data Server Client or the IBM Data Server Runtime Client), you can use the db2cli validate command to list the installed DB2 client packages on a Windows operating system. When you issue the db2cli validate command from the IBM data server clients, the list is limited to theIBM data server clients that are installed on the Windows operating system. To list IBM Data Server Driver for ODBC and CLI packages and the IBM data server client packages that are installed on a Windows operating system, you must issue the db2cli validate command from the IBM Data Server Driver for ODBC and CLI installation. The list indicates the current copy name as [C] and the default copy name as [D].

The following example shows the results of running the db2cli validate command: 
C:\CLIDRIVER\bin>db2cli validate
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

  ---------------------------------------------------------------------
  [ CLI Driver Version   : 09.07.0000 ]
  [ Informational Tokens : "DB2 v9.7.XXX.XXX","nXXXXXX","IPXXXXX","Fixpack  6" ]

  [ CLI Driver Type      : IBM Data Server Driver for ODBC and CLI ]

  IBM Data Server client packages on the current workstation:

  Copyname       Version     Installed Location
  ---------------------------------------------------------------------
  C_CLIDRIVER[C] 09.07.0600  C:\CLIDRIVER
  DB2COPY1[D]    09.07.0500  E:\SQLLIB
  DB2COPY2       09.01.0700  C:\Program Files\IBM\SQLLIB_01\
  DB2COPY3       09.07.0301  C:\Program Files\IBM\SQLLIB\
  DSCOPY         09.07.0500  C:\IBM\IBM DATA SERVER DRIVER\
  ---------------------------------------------------------------------

db2dsdriver.cfg Schema Validation :
Success: The schema validation operation completed successfully.
The configuration file C:\Documents and Settings\All Users\Application Data\IBM\
DB2\C_CLIDRIVER\cfg\db2dsdriver.cfg is valid

The validation completed.
On a 64-bit Windows system, both db2cli32.exe and db2cli.exe have same output.

Location of the db2dsdriver.cfg file

The location of the db2dsdriver.cfg file differs between IBM Data Server Client and IBM Data Server Driver.

  • In IBM Data Server Clients: %instance_path%/cfg
  • In DB2 Servers: %installation_path%/cfg

You can find the location of the db2dsdriver.cfg file by using the db2cli command with the validate parameter. The following example shows the use of the dummy DSN argument: 

>db2cli.exe validate -dsn dummy
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
  ---------------------------------------------------------------------------
  [ CLI Driver Version   : 09.07.0000 ]
  [ Informational Tokens : "DB2 v9.7.0.5","s111017","IP23292","Fixpack  5" ]
  [ CLI Driver Type      : IBM Data Server Driver For ODBC and CLI ]
  ---------------------------------------------------------------------------


db2dsdriver.cfg Schema Validation :
Success: The schema validation operation completed successfully.
The configuration file ../cfg/db2dsdriver.cfg is valid

Note: The validation operation utility could not find the 
configuration file named db2cli.ini.
The file is searched at ../cfg/db2cli.ini


db2dsdriver.cfg Validation :
  ---------------------------------------------------------------------------
  [ DB2DSDRIVER_CFG_PATH env var : unset ]
  [ db2dsdriver.cfg Path         : ../cfg/db2dsdriver.cfg ]
  ---------------------------------------------------------------------------
  [ Valid keywords used for DSN : alias1 ]
    Keyword                                      Value
    --------------------------------------------------
    DATABASE                                     name1
    HOSTNAME                                     server1.net1.com
    PORT                                         50001
    CURRENTSCHEMA                                OWNER1

  [ Parameters used for WLB ]
    Parameter                                    Value
    --------------------------------------------------
    enableWLB                                    true
    maxTransports                                50

  [ Parameters used for ACR ]
    Parameter                                    Value
    --------------------------------------------------
    enableACR                                    true

  [ Keywords UNKNOWN for DSN : alias1 ]
    Keyword                                      Value
    --------------------------------------------------
    GlobalParam                                  Value
  ---------------------------------------------------------------------------

The validation completed.

Validating DSN alias

You can issue the db2cli validate -dsn sampledsn command to validate the DSN alias sampledsn that is configured in the db2dsdriver.cfg file. If the entries are correct, validation is successful with the following output.

The following example shows the command output :
$ db2cli validate -dsn alias1       
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
  ---------------------------------------------------------------------------
  [ CLI Driver Version   : 09.07.0000 ]
  [ Informational Tokens : "DB2 v9.7.600.340","n120XXX","IP23322","Fixpack  6" ]
  [ CLI Driver Type      : IBM Data Server Client ]
  [ db2diag.log Path     : C:\Documents and Settings\All Users\Application Data\IBM\DB2\db2build\DB5\db2diag.log ]
  ---------------------------------------------------------------------------

  IBM Data Server Client packages on the current workstation :

  Copyname      Version     Installed Location
  ---------------------------------------------------------------------------
  db2build[C]   09.07.0006  E:\sqllib

  IBMDBCL1[D]   09.07.0004  C:\Program Files\IBM\IBM DATA SERVER DRIVER

  ---------------------------------------------------------------------------

db2dsdriver.cfg Schema Validation :
Success: The schema validation operation completed successfully.
The configuration file C:\Documents and Settings\All Users\Application Data\IBM\DB2\db2build\cfg\db2dsdriver.cfg is valid

Note: The validation operation utility could not find the configuration file named db2cli.ini.
The file is searched at C:\Documents and Settings\All Users\Application Data\IBM\DB2\db2build\cfg\db2cli.ini


db2dsdriver.cfg Validation :
  ---------------------------------------------------------------------------
  [ DB2DSDRIVER_CFG_PATH env var : unset ]
  [ db2dsdriver.cfg Path         : C:\Documents and Settings\All Users\Application Data\IBM\DB2\db2build\cfg\db2dsdriver.cfg ]
  ---------------------------------------------------------------------------
  [ List of Keywords used for DSN : alias1 ]
    Keywords                          Valid For      Value
    ----------------------------------------------------------
    DATABASE                          CLI,.NET,ESQL  STLEC1
    HOSTNAME                          CLI,.NET,ESQL  INEC002.svldev.svl.ibm.com
    PORT                              CLI,.NET,ESQL  446
    CURRENTSCHEMA                     CLI,.NET       **DUPLICATE**
    DISABLEAUTOCOMMIT                 CLI            FALSE
    COMMPROTOCOL                      CLI            TCPIP
    COMMITONEOF                       CLI,.NET       TRUE
    COLUMNWISEMRI                     CLI            TRUE
    DISABLEPOOLING                    .NET           TRUE
    csmbuffersize                     **UNKNOWN**
    XMLDeclaration                    **UNKNOWN**
  [ Parameters used for WLB ]
    Parameter                                        Value
    ----------------------------------------------------------
    enableWLB                                        true
    maxTransports                                    50

  [ Parameters used for ACR ]
    Parameter                                        Value
    ----------------------------------------------------------
    enableACR                                        true
    enableSeamlessACR                                true

    Alternate Server List :
    Name - S1  Hostname - jcc.svl.ibm.com  Port - 446
    Name - S2  Hostname - host1_placeholder  Port - 446
    Name - S3  Hostname - host2_placeholder  Port - 446

    Affinity List :
    Name - list1  Serverorder - S1,S2,S3
    Name - list2  Serverorder - S2,S1,S3
  ---------------------------------------------------------------------------

The validation completed.
In DB2 Version 9.7 Fix Pack 5 and later fix packs, the db2cli validate command output includes the DB2 copy name on Windows operating systems as shown in the following example:
>db2cli validate -dsn sampledsn
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
---------------------------------------------------------------------------
[ CLI Driver Version   : 09.07.0000 ]
[ Informational Tokens : "DB2 v9.7.500.582","s110619","IP23287","Fixpack 5" ]

[ CLI Driver Type      : IBM Data Server Driver Package ]
[ CLI Copy Name        : IBMDBCL1 ]
---------------------------------------------------------------------------

db2dsdriver.cfg Schema Validation :
Warning: The schema validation operation completed successfully.
The following data source name was not found in the db2cli.ini file: "sampledsn".
The file is at C:\Documents and Settings\All Users\Application Data\IBM\DB2\IBMDBCL1\cfg\db2cli.ini


db2dsdriver.cfg Validation :
-----------------------------------------------------------------------------------
[ DB2DSDRIVER_CFG_PATH env var	: unset ]
[ db2dsdriver.cfg Path              : C:\Documents and Settings\All Users\Application Data\IBM\DB2\IBMDBCL1\cfg\db2dsdriver.cfg ]
-----------------------------------------------------------------------------------
[ Keywords used by CLI for DSN     	: sampledsn ]
  Keyword                           Value
------------------------------------------
  DATABASE                          sample
  HOSTNAME                          samplehost.domain.com
  PORT                              19766
------------------------------------------

The validation completed.
If you are a DB2 Connect™ customer but you are not using the server-based license key or a DB2 Connect server, you might get this message from your connection:
[IBM][CLI Driver] SQL1598N  An attempt to connect to the database server 
failed because of a licensing problem.  SQLSTATE=42968

If you are using DB2 Connect Unlimited Edition for z/OS®, you can use a server-based license key. This one step prevents the need for client-based license keys. For details, see the topic about activating the license key for DB2 Connect Unlimited Edition for System z®.

If you are unable to use the solution, take the DB2 Connect license key from the DB2 Connect Edition that you have purchased (eg: db2conpe.lic) and type it in the C:\Program Files\IBM\IBM DATA SERVER DRIVER\license license directory, underneath the installation location for the IBM Data Server Driver Package.

After the db2dsdriver.cfg file is populated with the correct database connection information, register the DSN alias with the ODBC driver manager as a datasource. You can make the data source available to all users of the system (a system data source), or only to the current user (a user data source).

Testing connectivity for CLI driver

To test the CLI connection that uses DSN and database section entries in the db2dsdriver.cfg file, issue db2cli validate -dsn dsnName -connect -user userID -passwd password, as shown in the following example:

>db2cli validate -dsn sample -connect -user userID -passwd password
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
  ---------------------------------------------------------------------------
  [ CLI Driver Version  : 09.07.0000 ]
  [ Informational Tokens : "DB2 v9.7.500.683","s110928","IP23286","Fixpack  5" ]
  [ CLI Driver Type      : IBM Data Server Driver Package ]
  [ CLI Copy Name        : IBMDBCL1 ]
  ---------------------------------------------------------------------------


db2dsdriver.cfg Schema Validation :
Success: The schema validation operation completed successfully.
The configuration file C:\Documents and Settings\All Users\Application Data\IBM\DB2\IBMDBCL1\cfg\db2dsdriver.cfg is valid

Note: The validation operation utility could not find the 
configuration file named db2cli.ini.
The file is searched at C:\Documents and Settings\All Users\Application Data\IBM\DB2\IBMDBCL1\cfg\db2cli.ini


db2dsdriver.cfg Validation :
  ---------------------------------------------------------------------------
  [ DB2DSDRIVER_CFG_PATH env var : unset ]
  [ db2dsdriver.cfg Path        : C:\Documents and Settings\All Users\Application Data\IBM\DB2\IBMDBCL1\cfg\db2dsdriver.cfg ]
  ---------------------------------------------------------------------------
  [ Valid keywords used for DSN : sample ]
    Keyword                                      Value
    --------------------------------------------------
    DATABASE                                    STLEC1
    HOSTNAME                                    TOREC48.ibm.com
    PORT                                        446
  ---------------------------------------------------------------------------
Connection Section :  
---------------------------------------------------------------------------  
Connecting to: sample  
Connect Status: success  
End Connection Section  
--------------------------------------------------------------------------- 
The validation completed.

Validating CLPPlus

To verify that CLPPlus works properly, you can connect to the DSN alias sampledsn that is defined in the db2dsdriver.cfg file. Follow these steps:
  1. At the operating system prompt, type the clpplus command with username and dsn-alias parameters to start CLPPlus.
  2. Enter the password that is associated with the user name that you provided.
A successful connection indicates that CLPPlus works properly. The following example output shows the two-step verification and successful connection:
C:\>clpplus db2admin@sampledsn
CLPPlus: Version 1.4
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.

Enter password: **********

Database Connection Information :
---------------------------------
Hostname = samplehost.domain.com
Database server = DB2/NT  SQL09074
SQL authorization ID = db2admin
Local database alias = SAMPLEDSN
Port = 19766

Creating an ODBC DSN to the alias

Create a system data source name (DSN) for DSN alias sampledsn by using the db2cli registerdsn -add sampledsn -system command.

In DB2 Version 9.7 Fix Pack 4 and later fix packs, use the db2cli32 command, instead of the db2cli command, if you are using a 32-bit IBM Data Server Driver along with the 64-bit installer in a 64-bit Windows computer, as follows:
db2cli32 registerdsn -add sampledsn -system

Testing ODBC DSN with MS Excel

You can test the newly created ODBC DSN by using Microsoft applications, such as MS Excel.

Procedure

  1. Start the ODBC administrator tool from Control Panel->Administrative Tools-> Data Sources (ODBC) for 64 bit binary. For a 32-bit binary in a 64-bit machine, start the ODBC administrator tool from System Drive:\windows\SysWOW64\odbcad32.exe. For example: c:\windows\syswow64\ odbcad32.exe
  2. The list of user data sources is displayed, by default. Click the System DSN tab.
  3. Select the newly created DSN (sampledsn in our example) and click Configure.
  4. Enter your user name and password, and click Connect. The message "Connection tested successfully" is displayed.
  5. Start a Microsoft application and use the newly created DSN. For example,
    1. Start MS Excel.
    2. Go to Data->Import External Data-> New Database Query. The list of ODBC DSNs is displayed in a list box.
      Note: A 32-bit Excel application shows only 32-bit DSN's in the list box and 64-bit Excel shows only 64-bit DSNs in the list box.
    3. Select the DSN (sampledsn in our example) that you want to connect to, and provide the login details. The list of tables is displayed in the database.

Testing connectivity for ADO.NET drivers

You can verify that the IBM Data Server Driver is installed correctly for ADO.NET and is fully operational by running the testconn20.exe utility. Use the -dtc command option to verify the XA transaction support setup. To verify the runtime build with .NET Framework 4.0, you can use testconn40.exe.
C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin>testconn20 -dtc 
"database=sampledsn;uid=username;pwd=password"
adding MSDTC step

Step 1: Printing version info
        .NET Framework version: 2.0.50727.3615
        64-bit
        DB2 .NET provider version: 9.0.0.2
        DB2 .NET file version: 9.7.3.2
        Capability bits: ALLDEFINED
        Build: 20101113
        Factory for invariant name IBM.Data.DB2 verified
        Factory for invariant name IBM.Data.Informix verified
        IDS.NET from DbFactory is Common IDS.NET
        VSAI is not installed properly
        Elapsed: 1.2969165

Step 2: Validating db2dsdriver.cfg against db2dsdriver.xsd schema file
        C:\ProgramData\IBM\DB2\IBMDBCL1\cfg\db2dsdriver.cfg against 
C:\ProgramData\IBM\DB2\IBMDBCL1\cfg\db2dsdriver.xsd
        Elapsed: 0

Step 3: Connecting using "database=sampledsn;uid=username;pwd=password"
        Server type and version: DB2/NT 09.07.0003
        Elapsed: 2.8594665

Step 4: Selecting rows from SYSIBM.SYSTABLES to validate existence of packages
   SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 5 rows only
        Elapsed: 0.3281355

Step 5: Calling GetSchema for tables to validate existence of schema functions
        Elapsed: 0.906279

Step 6: Creating XA connection
        DB2TransactionScope: Connection Closed.
        Elapsed: 3.2657295


Test passed.
You can ignore the VSAI is not installed properly error. This error is displayed because VSAI is only available in 32-bit, and is not detected by a 64-bit testconn20. The 32-bit version of testconn20 should properly report VSAI information.
If you want to test connectivity to a particular server without adding an alias to the db2dsdriver.cfg file, you can specify full connectivity information in the connection string. 
C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin>testconn20 -dtc 
"database=sample;server=samplehost.domain.com:19766;uid=username;pwd=password"
If you want to test connectivity for your 32-bit applications that are running in a 64-bit environment, you can use the 32-bit version of the testconn20 utility.
C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin>testconn20_32 -dtc 
"database=sampledsn;uid=username;pwd=password" adding MSDTC step

Step 1: Printing version info
        .NET Framework version: 2.0.50727.3615
        DB2 .NET provider version: 9.0.0.2
        DB2 .NET file version: 9.7.3.2
        Capability bits: ALLDEFINED
        Build: 20101113
        Factory for invariant name IBM.Data.DB2 verified
        Factory for invariant name IBM.Data.Informix verified
        IDS.NET from DbFactory is Common IDS.NET
        VSAI assembly version: 9.1.0.0
        VSAI file version: 9.7.3.1012
        Elapsed: 1.0000192

Step 2: Validating db2dsdriver.cfg against db2dsdriver.xsd schema file
        C:\ProgramData\IBM\DB2\IBMDBCL1\cfg\db2dsdriver.cfg against 
C:\ProgramData\IBM\DB2\IBMDBCL1\cfg\db2dsdriver.xsd
        Elapsed: 0

Step 3: Connecting using "database=sampledsn;uid=username;pwd=password"
        Server type and version: DB2/NT 09.07.0003
        Elapsed: 2.8594665

Step 4: Selecting rows from SYSIBM.SYSTABLES to validate existence of packages
   SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 5 rows only
        Elapsed: 0.3281355

Step 5: Calling GetSchema for tables to validate existence of schema functions
        Elapsed: 0.906279

Step 6: Creating XA connection
        DB2TransactionScope: Connection Closed.
        Elapsed: 3.2657295


Test passed.

Creating connection in Server Explorer with the IBM Database Add-ins for Visual Studio

After installing IBM Database Add-ins for Visual Studio, you can create a connection in Server Explorer by performing the following steps:
  1. Start Visual Studio.
  2. Right-click the Data Connections node in the Server Explorer and click "Add Connection..."
  3. In the Add Connection dialog, select IBM DB2 and Informix® Data Provider for .Net data source.
  4. Click the database drop-down list to list the aliases defined in the db2dsdriver.cfg file. Choose an alias from the drop-down list or type the alias name in the database name field.
  5. Select sampledsn, enter your user name and password and click Test Connection. A message box is displayed to indicate that the test connection passed. Close the message box.
  6. Click OK in the connection dialog. The connection is now created in the server explorer.
Parent topic: IBM Data Server Drivers
Related reference:
testconn command