Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2013
    Posts
    8

    Unanswered: db2dsdriver.cfg questions

    Hi All,

    I'm an Oracle guy living in a DB2 world, at the moment, and I've been asked to install the IBM Data Server Drivers 9.7 (32 and 64) on a Windows environment.

    I've successfully done this and I've made an ODBC connection to a test DB. There are a few things I'm not really clear on though and I'd like to understand them better before I call this project complete.

    1. For ODBC or JDBC connections, should I use the db2dsdriver.cfg or the db2cli.ini ? Could someone help me with examples of when I should use cfg versus ini? I've validated connectivity with "db2cli -validate" but what I've done may not be "best practices."

    2. In Windows, when you launch ODBC Data Source Administrator and you create a System DSN, I select the correct DB2 driver and I'm presented with 3 boxes: a Data Source Name text field, a Data Alias pulldown, and a Description field. How do I get the Data Alias to populate? In Oracle this would populate via the tnsnames.ora. I haven't found out how to do this in DB2. I apologize if this is something simple I'm overlooking. Be kind ;-)

    3. Could someone help me with best configuration practices for the db2ddsdriver.cfg? I'm looking at the IBM Docs (IBM DB2 9.7 Information Center for Linux, UNIX, and Windows) and I don't understand the difference between the "dsncollection" and "databases" collection -- do I need both? what are best practices for populating this file? I keep reading and re-reading the definitions IBM has provided for both and I find myself going cross eyed. The example is (or maybe I am) too simple to understand.

    Thanks in advance!

    Regards,
    Rich
    Last edited by rcevans; 03-20-13 at 13:13. Reason: added 9.7 version

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rcevans View Post

    1. For ODBC or JDBC connections, should I use the db2dsdriver.cfg or the db2cli.ini ? Could someone help me with examples of when I should use cfg versus ini? I've validated connectivity with "db2cli -validate" but what I've done may not be "best practices."
    db2cli.ini is not used for JDBC clients, only for CLI/ODBC (which is essentially the same thing) clients. My suggestion would be to use db2dsdriver.cfg for data source configuration. You may still need to use db2cli.ini for certain configuration options if your ODBC applications require them.

    Quote Originally Posted by rcevans View Post
    2. In Windows, when you launch ODBC Data Source Administrator and you create a System DSN, I select the correct DB2 driver and I'm presented with 3 boxes: a Data Source Name text field, a Data Alias pulldown, and a Description field. How do I get the Data Alias to populate?
    I don't have a Windows machine handy to verify this, but my guess would be that the pull-down menu will be populated from the dsncollection element in db2dsdriver.cfg

    Quote Originally Posted by rcevans View Post
    I don't understand the difference between the "dsncollection" and "databases" collection -- do I need both?
    The dsncollection element contains a list of data source names for use by the ODBC/CLI driver. What identifies a database in DB2 is the server hostname, port, and the actual database name. Each dsn element names the data source for a particular database.

    In the databases element you can set specific configuration parameters for each database connection, when necessary. I would venture to guess that you don't need to create the individual database elements in it, unless you need to specify certain parameters, but the databases section itself may be required. Again, I have not verified that.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2013
    Posts
    8
    @n_i: Thank you very much for the prompt and thorough reply! I too thought the dnscollection element would populate the Data Alias pulldown but so far no luck. It still could, I'm not ruling out user error yet ;-)

    @sathyaram_s: Thank you for the prompt reply and the link. I'll review it and add it to my bookmarks.
    Last edited by rcevans; 03-20-13 at 16:34. Reason: clarified my response.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Ok, so I tested this out of curiousity. Windows 7 64 bit, DB2 9.7 fix pack 7 runtime client installation.

    The list of aliases in the IBM ODBC driver configuration comes from the CLI catalog (refer to the CATALOG NODE and CATALOG DATABASE commands in the manual). This is the traditional way of storing the connection information for the DB2 CLI clients.

    If you choose to use db2dsdriver.cfg instead (which might be a good idea if you use both ODBC/CLI and JDBC clients, since they both can obtain data source definitions from the file) you can use db2cli registerdsn command (e.g. db2cli registerdsn -add -alldsn) to automatically register the data sources with the Windows ODBC driver manager.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Mar 2013
    Posts
    8
    Quote Originally Posted by n_i View Post
    db2cli.ini is not used for JDBC clients, only for CLI/ODBC (which is essentially the same thing) clients. My suggestion would be to use db2dsdriver.cfg for data source configuration. You may still need to use db2cli.ini for certain configuration options if your ODBC applications require them.



    I don't have a Windows machine handy to verify this, but my guess would be that the pull-down menu will be populated from the dsncollection element in db2dsdriver.cfg



    The dsncollection element contains a list of data source names for use by the ODBC/CLI driver. What identifies a database in DB2 is the server hostname, port, and the actual database name. Each dsn element names the data source for a particular database.

    In the databases element you can set specific configuration parameters for each database connection, when necessary. I would venture to guess that you don't need to create the individual database elements in it, unless you need to specify certain parameters, but the databases section itself may be required. Again, I have not verified that.
    Quote Originally Posted by n_i View Post
    Ok, so I tested this out of curiousity. Windows 7 64 bit, DB2 9.7 fix pack 7 runtime client installation.

    The list of aliases in the IBM ODBC driver configuration comes from the CLI catalog (refer to the CATALOG NODE and CATALOG DATABASE commands in the manual). This is the traditional way of storing the connection information for the DB2 CLI clients.

    If you choose to use db2dsdriver.cfg instead (which might be a good idea if you use both ODBC/CLI and JDBC clients, since they both can obtain data source definitions from the file) you can use db2cli registerdsn command (e.g. db2cli registerdsn -add -alldsn) to automatically register the data sources with the Windows ODBC driver manager.
    Thanks again. I hate to drop an error message on you here but I can't find a solution on Google or on this forum. I executed what you provided and it seems like it should work just fine. Unfortunately I'm getting an error that says "Error: ODBC_ERROR_COMPONENT_NOT_FOUND" for each of the DSNs.

    Below is an example of the db2dsdriver.cfg with one of our DB2 systems.

    Here is my paired down db2dsdriver.cfg:

    Code:
    <configuration>    
      <dsncollection>       
       <dsn alias="U_TOOLS" name="TOOLSDB" host="aixudb2" port="50000"/>
      </dsncollection>     
    </configuration>
    In the first step I validate the DSN and connectivity:

    Code:
     C:\Users\db2user>db2cli validate -dsn U_TOOLS -connect -user *** -passwd ***
    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.700.552","s121002","IP23368","Fixpack  7" ]
      [ CLI Driver Type     : IBM Data Server Driver Package ]
      [ db2diag.log Path    : C:\ProgramData\IBM\DB2\C_PROGRA~2_IBM_IBMDAT~1\db2diag.log ]
      ---------------------------------------------------------------------------
    
      IBM Data Server Client packages on the current workstation :
    
      Copyname                     Version     Installed Location
      ---------------------------------------------------------------------------
      C_PROGRA~2_IBM_IBMDAT~1[C]   09.07.0007  C:\PROGRA~2\IBM\IBMDAT~1
      IBMDBCL1[D]                  09.07.0007  C:\Program Files\IBM\IBM DATA SERVER DRIVER 64
    
      ---------------------------------------------------------------------------
    
    db2dsdriver.cfg Schema Validation :
    Success: The schema validation operation completed successfully.
    The configuration file C:\PROGRA~2\IBM\IBMDAT~1\cfg\db2dsdriver.cfg is valid
    
    db2cli.ini Validation :
    Note: The validation operation utility could not find the
    configuration file named db2cli.ini.
    The file is searched at C:\ProgramData\IBM\DB2\C_PROGRA~2_IBM_IBMDAT~1\cfg\db2cli.ini
    
    
    db2dsdriver.cfg Validation :
      ---------------------------------------------------------------------------
      [ DB2DSDRIVER_CFG_PATH env var : unset ]
      [ db2dsdriver.cfg Path         : C:\PROGRA~2\IBM\IBMDAT~1\cfg\db2dsdriver.cfg ]
      ---------------------------------------------------------------------------
      [ List of keywords used for DSN : U_TOOLS ]
        Keywords                  Valid For     Value
        -------------------------------------------------------
        DATABASE                  CLI,.NET,ESQL TOOLSDB
        HOSTNAME                  CLI,.NET,ESQL aixudb2
        PORT                      CLI,.NET,ESQL 50000
      ---------------------------------------------------------------------------
    
    Connection Section :
      ---------------------------------------------------------------------------
      Connecting to: U_TOOLS
      Connect Status: success
      End Connection Section
    
    C:\ProgramData\IBM\DB2\C_PROGRA~2_IBM_IBMDAT~1\cfg\
    
    The validation completed.
    In this step, I try to add the DSN by name:

    Code:
    C:\Users\db2user>db2cli registerdsn -add -dsn U_TOOLS
    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.
    
    DSN                                 Remarks
    ---------------------------------------------------------------------------
    U_TOOLS                          -  Error: ODBC_ERROR_COMPONENT_NOT_FOUND
    I get the same error with the alldsn switch:

    Code:
    C:\Users\db2user>db2cli registerdsn -add -alldsn
    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.
    
    DSN                                 Remarks
    ---------------------------------------------------------------------------
    U_TOOLS                          -  Error: ODBC_ERROR_COMPONENT_NOT_FOUND
    Any thoughts? Again, thanks for your outstanding response! If I need to open up a Service Request (or IBM equivalent name) with IBM please let me know and I'll do so.

    Regards,
    Rich
    Last edited by rcevans; 03-21-13 at 17:57. Reason: wordsmithing

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Which DB2 client edition did you install? Does the IBM DB2 ODBC driver show in the list of available drivers in the Windows driver manager? Also, this probably requires you to run this as Administrator...
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Mar 2013
    Posts
    8
    Quote Originally Posted by n_i View Post
    Which DB2 client edition did you install? Does the IBM DB2 ODBC driver show in the list of available drivers in the Windows driver manager? Also, this probably requires you to run this as Administrator...
    Thanks,

    I installed the IBM Data Server Driver Package 9.7 FP 7, #6 on the list:

    IBM Support: Fix Central

    We chose this because it looks like it supports everything:

    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

    I can make my own Windows "Data Source" ODBC connection successfully -- there is nothing in that Database Alias dropdown but if I click Add I can create one. It works as long as I use the exact name in the db2dsdriver.cfg.

    I thought about the privilege issue so I opened up a command prompt as administrator (right mouse click, run as administrator) and I received the same error.

    I can take screenshots and pass them along if you'd like.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Looks like a botched installation. May be you should contact IBM support. Like I said, I tried the runtime client package on my Windows 7 VM and it seems to work.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Mar 2013
    Posts
    8
    Quote Originally Posted by n_i View Post
    Looks like a botched installation. May be you should contact IBM support. Like I said, I tried the runtime client package on my Windows 7 VM and it seems to work.
    Thanks, I'll do that. I really appreciate your support!

  11. #11
    Join Date
    Mar 2013
    Posts
    8
    Although they do not show up in the "Database Alias" pulldown they are registered as User DSNs if I modify the command from db2cli to db2cli32, per the fine print @ IBM DB2 9.7 Information Center for Linux, UNIX, and Windows:

    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...

    Code:
    C:\Users\db2user>db2cli32 registerdsn -add -alldsn
    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.
    
    DSN                                 Remarks
    ---------------------------------------------------------------------------
    U_TOOLS                          -  Successfully added
    What's interesting is this is a 64-bit Driver package on a 64-bit system. I reverted to a VM snapshot and only installed the 64-bit package this time.

    I'll keep plugging away and if I have anything helpful I'll post it here for posterity.
    Last edited by rcevans; 03-21-13 at 17:57. Reason: wordsmithing

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •