Topic
  • 18 replies
  • Latest Post - ‏2009-11-06T04:49:22Z by NormWong
windward
windward
15 Posts

Pinned topic connecting to a DB2 instance over the network

‏2009-06-25T20:58:56Z |
Hi everyone, there is a db2 instance on a server in my local network, what is the syntax of the command i would want to use to connect to this db over the network ?

Also can anyone give me a summary of how the authentication for db2 works when connecting. I know that it uses the windows kerberos authentication, does it use this in addition to a standard username and pass ?

since when you connect to a db locally it asks for a username and pass.

OK thanks
  • windward
    windward
    15 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-06-29T20:12:37Z  
    anyone wanna help ??
  • windward
    windward
    15 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-06-30T16:42:57Z  
    ive tried looking around the web but i cannot get a definitive answer to my questions. Arent these paid support forums ?

    i figured that for any one reasonably familiar DB2 these would be quick questions to answer
  • RichBriddell
    RichBriddell
    16 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-06-30T20:50:15Z  
    When connecting 'locally' the syntax is simply "connect to alias" where alias is the one used in the catalog command. When connecting in this way an implicit authentication takes place and a password is not required. This assumes the database is cataloged locally, not as a TCPIP client.

    When connecting over a network you provide a user name and password "connect to alias user yourid". Authentication will take place using whatever method is configured for the server. By default this is generally OS authentication.
  • nbharatha
    nbharatha
    18 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-07-01T15:30:21Z  
    Hi

    There are many ways you can connect to DB2 database on a given server.
    Let me tell you the two commonly used methods and if you can give more details of your environment, I might help further.

    Assumptions
    DB2 database exists on a server named 'Cobra' , db2 instance name is 'db2inst1' and database name is 'sampledb'
    You have logged on your desktop/laptop

    Method 1
    1. In this method, I assume, you have a DB2 Client installed on your desktop
    2. On server 'cobra' login as db2inst1 and execute db2set DB2COMM=TCPIP
    3. On server,you need to assign a TCP/IP port in /etc/services and same has to be defined for SVCENAME in DBM CFG
    db2 update dbm cfg using svcename <port / alias name in /etc/services>
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.qb.client.doc/doc/t0005621.html

    4. Perform catalog of db2 node and database
    4.1 Start -> Run -> db2cmd
    4.2 db2 catalog tcpip node mynode remote cobra.full.domain.name server <50000/tcpip port>
    4.3 db2 catalog db sampledb as mydb at node mynode
    4.4 db2 terminate
    4.5 db2 connect to mydb user <valid ID on server cobra>
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.qb.client.doc/doc/t0005621.html

    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.sql.ref.doc/doc/r0024842.html

    Method 2. Programtically if you want to connect to db2 database from Java / .NET / Python / PHP you need to use appropiate libraries.
    Atleast for Java you need two files db2jcc.jar and db2jcc_license_cu.jar
    For more details visit
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.java.doc/doc/cjvjdcon.html?resultof=%22%74%79%70%65%22%20%22%34%22%20%22%63%6f%6e%6e%65%63%74%69%6f%6e%22%20%22%63%6f%6e%6e%65%63%74%22%20

    Let me know if you have any further issues.

    Naveen
  • windward
    windward
    15 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-07-16T16:17:07Z  
    • nbharatha
    • ‏2009-07-01T15:30:21Z
    Hi

    There are many ways you can connect to DB2 database on a given server.
    Let me tell you the two commonly used methods and if you can give more details of your environment, I might help further.

    Assumptions
    DB2 database exists on a server named 'Cobra' , db2 instance name is 'db2inst1' and database name is 'sampledb'
    You have logged on your desktop/laptop

    Method 1
    1. In this method, I assume, you have a DB2 Client installed on your desktop
    2. On server 'cobra' login as db2inst1 and execute db2set DB2COMM=TCPIP
    3. On server,you need to assign a TCP/IP port in /etc/services and same has to be defined for SVCENAME in DBM CFG
    db2 update dbm cfg using svcename <port / alias name in /etc/services>
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.qb.client.doc/doc/t0005621.html

    4. Perform catalog of db2 node and database
    4.1 Start -> Run -> db2cmd
    4.2 db2 catalog tcpip node mynode remote cobra.full.domain.name server <50000/tcpip port>
    4.3 db2 catalog db sampledb as mydb at node mynode
    4.4 db2 terminate
    4.5 db2 connect to mydb user <valid ID on server cobra>
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.qb.client.doc/doc/t0005621.html

    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.sql.ref.doc/doc/r0024842.html

    Method 2. Programtically if you want to connect to db2 database from Java / .NET / Python / PHP you need to use appropiate libraries.
    Atleast for Java you need two files db2jcc.jar and db2jcc_license_cu.jar
    For more details visit
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.java.doc/doc/cjvjdcon.html?resultof=%22%74%79%70%65%22%20%22%34%22%20%22%63%6f%6e%6e%65%63%74%69%6f%6e%22%20%22%63%6f%6e%6e%65%63%74%22%20

    Let me know if you have any further issues.

    Naveen
    ok i have done all of the steps you mentioned in Method 1.

    on my client machine that lives on the same network, i try to add the system using the configuration assistant.
    • When i add the server it correctly shows the DB2 instance.
    • i put in my username and pass, and then it will properly show all the Alias' and the DB names for them
    • then im not really sure what to do next, but since i dont see any other logical steps, i close the configuration assistant.
    • restart the Control Center, and then the new server i added will show up
    • when i try to expand it, the instances folder is empty

    Any insight on this problem ?
  • windward
    windward
    15 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-07-22T15:50:37Z  
    • windward
    • ‏2009-07-16T16:17:07Z
    ok i have done all of the steps you mentioned in Method 1.

    on my client machine that lives on the same network, i try to add the system using the configuration assistant.
    • When i add the server it correctly shows the DB2 instance.
    • i put in my username and pass, and then it will properly show all the Alias' and the DB names for them
    • then im not really sure what to do next, but since i dont see any other logical steps, i close the configuration assistant.
    • restart the Control Center, and then the new server i added will show up
    • when i try to expand it, the instances folder is empty

    Any insight on this problem ?
    ?? anyone ??

    i would think this would be one of the easier parts of setting up a database. I also understand that most people that install a database probably dont need to natively connect to it over the network, but in my case i do. Why is this so difficult ? ive been through everything many many times. I have researched all the errors i get and followed all of the directions, yet i still cannot get it to work ?

    Can someone at IBM give me some contact information so i can talk to them directly and get a solid answer about this problem ?
  • windward
    windward
    15 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-09-21T23:41:02Z  
    • windward
    • ‏2009-07-22T15:50:37Z
    ?? anyone ??

    i would think this would be one of the easier parts of setting up a database. I also understand that most people that install a database probably dont need to natively connect to it over the network, but in my case i do. Why is this so difficult ? ive been through everything many many times. I have researched all the errors i get and followed all of the directions, yet i still cannot get it to work ?

    Can someone at IBM give me some contact information so i can talk to them directly and get a solid answer about this problem ?
    I am now able to connect to the database over my network. But my original problem was being able to connect to it from a .NET application. I have the 9.5 client installed, i think i also need the ADO .NET connector for DB2. Where can i find this ?
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-09-22T02:29:36Z  
    • windward
    • ‏2009-09-21T23:41:02Z
    I am now able to connect to the database over my network. But my original problem was being able to connect to it from a .NET application. I have the 9.5 client installed, i think i also need the ADO .NET connector for DB2. Where can i find this ?
    hi,

    Have you change the default value of discover_inst or discovery in dbm cfg?
    they must be enabled
    And you should check the value of discovery in admin cfg ,too.

    thx.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-09-22T02:29:41Z  
    • windward
    • ‏2009-09-21T23:41:02Z
    I am now able to connect to the database over my network. But my original problem was being able to connect to it from a .NET application. I have the 9.5 client installed, i think i also need the ADO .NET connector for DB2. Where can i find this ?
    hi,

    Have you change the default value of discover_inst or discovery in dbm cfg?
    they must be enabled
    And you should check the value of discovery in admin cfg ,too.

    thx.
  • NormWong
    NormWong
    27 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-10-16T17:57:37Z  
    • windward
    • ‏2009-09-21T23:41:02Z
    I am now able to connect to the database over my network. But my original problem was being able to connect to it from a .NET application. I have the 9.5 client installed, i think i also need the ADO .NET connector for DB2. Where can i find this ?
    The .NET connector is part of the DB2 client.

    This site would be of use to you:
    http://www.ibm.com/developerworks/data/zones/vstudio/index.html?S_TACT=105AGX11&S_CMP=FP

    This tutorial will help you get going with .net and db2:
    http://www.ibm.com/developerworks/edu/dm-dw-dm-0605xia-i.html?S_TACT=105AGX11&S_CMP=FP

    (registration is free).
    Norm
  • windward
    windward
    15 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-11-03T01:57:53Z  
    • NormWong
    • ‏2009-10-16T17:57:37Z
    The .NET connector is part of the DB2 client.

    This site would be of use to you:
    http://www.ibm.com/developerworks/data/zones/vstudio/index.html?S_TACT=105AGX11&S_CMP=FP

    This tutorial will help you get going with .net and db2:
    http://www.ibm.com/developerworks/edu/dm-dw-dm-0605xia-i.html?S_TACT=105AGX11&S_CMP=FP

    (registration is free).
    Norm
    I tried to start the tutorial you referenced in the last post, but when i try to "Add new Connection" there is no option for "IBM DB2 Data Provider for .NET Framework 2.0".

    I have the client installed for 9.5.

    Someone please give me a download link for the ADO.NET connector.

    Keep in mind, Im trying to work with the db2 instance over the network, not on the local machine.
  • windward
    windward
    15 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-11-03T01:58:44Z  
    hi,

    Have you change the default value of discover_inst or discovery in dbm cfg?
    they must be enabled
    And you should check the value of discovery in admin cfg ,too.

    thx.
    "Have you change the default value of discover_inst or discovery in dbm cfg?
    they must be enabled
    And you should check the value of discovery in admin cfg ,too."

    yes i checked, they are both enabled.
  • NormWong
    NormWong
    27 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-11-05T04:40:13Z  
    • windward
    • ‏2009-11-03T01:57:53Z
    I tried to start the tutorial you referenced in the last post, but when i try to "Add new Connection" there is no option for "IBM DB2 Data Provider for .NET Framework 2.0".

    I have the client installed for 9.5.

    Someone please give me a download link for the ADO.NET connector.

    Keep in mind, Im trying to work with the db2 instance over the network, not on the local machine.
    From the DB2 Command line window, what do get when you enter:

    db2 list db directory
    db2 list node directory

    These are the two pieces of information you need to catalog on the client to connect to the server. The node holds the instance connection information such as the IP address and the port to connect to.

    The database will reference the node directory.

    If you see the database pointing to the correct node, then you should be able to connect with:

    db2 connect to <dbname> user <userid>
    Norm
  • windward
    windward
    15 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-11-05T21:17:28Z  
    • NormWong
    • ‏2009-11-05T04:40:13Z
    From the DB2 Command line window, what do get when you enter:

    db2 list db directory
    db2 list node directory

    These are the two pieces of information you need to catalog on the client to connect to the server. The node holds the instance connection information such as the IP address and the port to connect to.

    The database will reference the node directory.

    If you see the database pointing to the correct node, then you should be able to connect with:

    db2 connect to <dbname> user <userid>
    Norm
    -NormWong, i already tried that, and I was able to successfully connect to the database over the network using the DB2 CLI. But thats not my problem.

    In .NET i get this error, does anyone know whats going on ?

    System.Reflection.TargetInvocationException occurred
    Message="Exception has been thrown by the target of an invocation."
    Source="mscorlib"
    StackTrace:
    at System.RuntimeFieldHandle.GetValue(Object instance, RuntimeTypeHandle fieldType, RuntimeTypeHandle declaringType, Boolean& domainInitialized)
    at System.Reflection.RtFieldInfo.InternalGetValue(Object obj, Boolean doVisibilityCheck, Boolean doCheckConsistency)
    at System.Reflection.RtFieldInfo.GetValue(Object obj)
    at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow)
    at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName)
    at net.windward.utils.ado.WrProviderFactories.GetAllProviders() in C:\Dev\Hekili\Kailua\net\windward\utils\ado\WrProviderFactories.cs:line 51
    InnerException: System.TypeInitializationException
    Message="The type initializer for 'IBM.Data.DB2.DB2Factory' threw an exception."
    TypeName="IBM.Data.DB2.DB2Factory"
    InnerException: System.InvalidOperationException
    Message="SUCCESS - unable to allocate an environment handle."
    Source="IBM.Data.DB2.9.1.2"
    StackTrace:
    at IBM.Data.DB2.DB2ConnPool.h()
    at IBM.Data.DB2.DB2ConnPool..ctor()
    at IBM.Data.DB2.DB2Factory..ctor()
    at IBM.Data.DB2.DB2Factory..cctor()
    InnerException:

    Here is what i have in my machine.config file in my .NET directory

    <add name="IBM DB2 .NET Data Provider" invariant="IBM.Data.DB2" description="IBM DB2 Data Provider for .NET Framework 2.0" type="IBM.Data.DB2.DB2Factory, IBM.Data.DB2, Version=9.0.0.2, Culture=neutral, PublicKeyToken=
    <add name="IBM DB2 .NET Data Provider 9.1.2" invariant="IBM.Data.DB2.9.1.2" description="IBM DB2 Data Provider 9.1.2 for .NET Framework 2.0" type="IBM.Data.DB2.DB2Factory, IBM.Data.DB2.9.1.2, Version=9.1.2.2, Culture=neutral, PublicKeyToken=7c307b91aa13d208" />

    has anyone had any experience using these DB2 providers.
    And again, the tutorial that someone posted that uses Visual Studio 2005 did not work because the IBM DB2 provider did not show up as an option in the 'Add connection' step.

    Ive been trying to fix this problem for a loooong time. And im getting the same suggestions, i would think something like this would be really basic.
  • windward
    windward
    15 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-11-05T23:28:58Z  
    • windward
    • ‏2009-11-05T21:17:28Z
    -NormWong, i already tried that, and I was able to successfully connect to the database over the network using the DB2 CLI. But thats not my problem.

    In .NET i get this error, does anyone know whats going on ?

    System.Reflection.TargetInvocationException occurred
    Message="Exception has been thrown by the target of an invocation."
    Source="mscorlib"
    StackTrace:
    at System.RuntimeFieldHandle.GetValue(Object instance, RuntimeTypeHandle fieldType, RuntimeTypeHandle declaringType, Boolean& domainInitialized)
    at System.Reflection.RtFieldInfo.InternalGetValue(Object obj, Boolean doVisibilityCheck, Boolean doCheckConsistency)
    at System.Reflection.RtFieldInfo.GetValue(Object obj)
    at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow)
    at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName)
    at net.windward.utils.ado.WrProviderFactories.GetAllProviders() in C:\Dev\Hekili\Kailua\net\windward\utils\ado\WrProviderFactories.cs:line 51
    InnerException: System.TypeInitializationException
    Message="The type initializer for 'IBM.Data.DB2.DB2Factory' threw an exception."
    TypeName="IBM.Data.DB2.DB2Factory"
    InnerException: System.InvalidOperationException
    Message="SUCCESS - unable to allocate an environment handle."
    Source="IBM.Data.DB2.9.1.2"
    StackTrace:
    at IBM.Data.DB2.DB2ConnPool.h()
    at IBM.Data.DB2.DB2ConnPool..ctor()
    at IBM.Data.DB2.DB2Factory..ctor()
    at IBM.Data.DB2.DB2Factory..cctor()
    InnerException:

    Here is what i have in my machine.config file in my .NET directory

    <add name="IBM DB2 .NET Data Provider" invariant="IBM.Data.DB2" description="IBM DB2 Data Provider for .NET Framework 2.0" type="IBM.Data.DB2.DB2Factory, IBM.Data.DB2, Version=9.0.0.2, Culture=neutral, PublicKeyToken=
    <add name="IBM DB2 .NET Data Provider 9.1.2" invariant="IBM.Data.DB2.9.1.2" description="IBM DB2 Data Provider 9.1.2 for .NET Framework 2.0" type="IBM.Data.DB2.DB2Factory, IBM.Data.DB2.9.1.2, Version=9.1.2.2, Culture=neutral, PublicKeyToken=7c307b91aa13d208" />

    has anyone had any experience using these DB2 providers.
    And again, the tutorial that someone posted that uses Visual Studio 2005 did not work because the IBM DB2 provider did not show up as an option in the 'Add connection' step.

    Ive been trying to fix this problem for a loooong time. And im getting the same suggestions, i would think something like this would be really basic.
    Heres the code that im calling that causes the error
    ArrayList vendors = new ArrayList();
    ArrayList problems = new ArrayList();
    DataTable tbl = DbProviderFactories.GetFactoryClasses();
    foreach (DataRow row in tbl.Rows)
    {
    string providerClass = (string)row[2];
    try
    {
    DbProviderFactories.GetFactory(providerClass); // will throw exception if not configured properly
    vendors.Add(CreateProvider((string)row[0], row[1] as string, providerClass));
    }
    catch (Exception ex)
    {
    problems.Add(new BadProvider(providerClass, ex));
    }
    }
  • NormWong
    NormWong
    27 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-11-06T04:14:20Z  
    • windward
    • ‏2009-11-05T21:17:28Z
    -NormWong, i already tried that, and I was able to successfully connect to the database over the network using the DB2 CLI. But thats not my problem.

    In .NET i get this error, does anyone know whats going on ?

    System.Reflection.TargetInvocationException occurred
    Message="Exception has been thrown by the target of an invocation."
    Source="mscorlib"
    StackTrace:
    at System.RuntimeFieldHandle.GetValue(Object instance, RuntimeTypeHandle fieldType, RuntimeTypeHandle declaringType, Boolean& domainInitialized)
    at System.Reflection.RtFieldInfo.InternalGetValue(Object obj, Boolean doVisibilityCheck, Boolean doCheckConsistency)
    at System.Reflection.RtFieldInfo.GetValue(Object obj)
    at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow)
    at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName)
    at net.windward.utils.ado.WrProviderFactories.GetAllProviders() in C:\Dev\Hekili\Kailua\net\windward\utils\ado\WrProviderFactories.cs:line 51
    InnerException: System.TypeInitializationException
    Message="The type initializer for 'IBM.Data.DB2.DB2Factory' threw an exception."
    TypeName="IBM.Data.DB2.DB2Factory"
    InnerException: System.InvalidOperationException
    Message="SUCCESS - unable to allocate an environment handle."
    Source="IBM.Data.DB2.9.1.2"
    StackTrace:
    at IBM.Data.DB2.DB2ConnPool.h()
    at IBM.Data.DB2.DB2ConnPool..ctor()
    at IBM.Data.DB2.DB2Factory..ctor()
    at IBM.Data.DB2.DB2Factory..cctor()
    InnerException:

    Here is what i have in my machine.config file in my .NET directory

    <add name="IBM DB2 .NET Data Provider" invariant="IBM.Data.DB2" description="IBM DB2 Data Provider for .NET Framework 2.0" type="IBM.Data.DB2.DB2Factory, IBM.Data.DB2, Version=9.0.0.2, Culture=neutral, PublicKeyToken=
    <add name="IBM DB2 .NET Data Provider 9.1.2" invariant="IBM.Data.DB2.9.1.2" description="IBM DB2 Data Provider 9.1.2 for .NET Framework 2.0" type="IBM.Data.DB2.DB2Factory, IBM.Data.DB2.9.1.2, Version=9.1.2.2, Culture=neutral, PublicKeyToken=7c307b91aa13d208" />

    has anyone had any experience using these DB2 providers.
    And again, the tutorial that someone posted that uses Visual Studio 2005 did not work because the IBM DB2 provider did not show up as an option in the 'Add connection' step.

    Ive been trying to fix this problem for a loooong time. And im getting the same suggestions, i would think something like this would be really basic.
    Did you install the Visual Studio Add-in at the end of the DB2 Client install?
    http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.ms.doc/doc/c0010780.htm
    You should be able to manage your connections to DB2 from within Visual Studio.
    Norm
  • NormWong
    NormWong
    27 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-11-06T04:16:24Z  
    • NormWong
    • ‏2009-11-06T04:14:20Z
    Did you install the Visual Studio Add-in at the end of the DB2 Client install?
    http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.ms.doc/doc/c0010780.htm
    You should be able to manage your connections to DB2 from within Visual Studio.
    Norm
    This video may help:

    http://www.channeldb2.com/video/beginning-db2-net-development
  • NormWong
    NormWong
    27 Posts

    Re: connecting to a DB2 instance over the network

    ‏2009-11-06T04:49:22Z  
    • windward
    • ‏2009-11-05T23:28:58Z
    Heres the code that im calling that causes the error
    ArrayList vendors = new ArrayList();
    ArrayList problems = new ArrayList();
    DataTable tbl = DbProviderFactories.GetFactoryClasses();
    foreach (DataRow row in tbl.Rows)
    {
    string providerClass = (string)row[2];
    try
    {
    DbProviderFactories.GetFactory(providerClass); // will throw exception if not configured properly
    vendors.Add(CreateProvider((string)row[0], row[1] as string, providerClass));
    }
    catch (Exception ex)
    {
    problems.Add(new BadProvider(providerClass, ex));
    }
    }
    At the DB2 ExpressC download page for Windows x64, this is the second download file:
    DB2 9.7 Add-Ins for Microsoft Visual Studio
    db2exc_vsai_970_WIN_x86.zip (29 M)