Get Data Moving 1
Results 1 to 10 of 10

Thread: Attunity Thin_ADO.NET1.1_Client to connect to DB2

  1. #1
    Betty Sullivan is offline Junior Member
    Join Date
    Sep 2006
    Posts
    12
    Rep Power
    0

    Attunity Thin_ADO.NET1.1_Client to connect to DB2

    I have been advised to use the singleClient server mode when using attunity ado.net to connect to db2 and let the .net manage the pooling. Does that mean I should set pooling=false on the connection string?

  2. #2
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    The ADO.Net connection pooling is determined based on the connection string used (see documentation) - something like "pooling=true;max pool size=10".

    >> I have been advised to use the singleClient server mode
    >> when using attunity ado.net to connect to db2 and let
    >> the .net manage the pooling. Does that mean I should set
    >> pooling=false on the connection string?

    AIS provides two different types of pools.

    Client side pools (of which the ADO.Net pool is an example) let a single client connection be reused across multiple client contexts. This reduces the number of client connections that need to be maintained and eliminate the high costs of establishing client connection (which include authentication against the daemon, acquisition of a server instance and loading metadata of tables, procedures and queries on the server).

    Server side pools (as defined in the daemon's workspace definition) keep servers up, hot and ready to accept incoming connection. Starting a new process on the server is typically a heavy and lengthy operation. By pre-startning servers and keeping released servers for later reuse, a much better response time can be given to new client connections. Server side pools are very important when dealing with unexpected rise in load - under load condition, starting new servers is much slower so having the servers pre-started means better ability to cope with load (that's provisioning).

    The workspace parameters that control server pooling are the nAvailableServer, minNAvailableServers, maxNAvailableServers and maxNActiveServers. The singleClient server mode can work with pre-started servers but it does not let servers be reused. If the pools are long term (days) and stable, then this mode is reasonable. However, if the pools are created and destroyed frequently then a reusable server mode is much better as it ensures that new pools can be created very quickly. Additionally, if you want to make sure servers are available when needed, even under very high load conditions, then the reusable mode is much better since under high load creating new servers may not be possible.

    /d
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    Betty Sullivan is offline Junior Member
    Join Date
    Sep 2006
    Posts
    12
    Rep Power
    0

    pooling

    My .NET code has transaction option set to required with pooling. It is using the Attunity connection string set with pooling=true. The workspace is set to reusable. We have experienced problems with the connection to DB2 not getting released. What I think you are saying is that we have our settings on both .NET and Attunity workspace okay, but we need to manage our .NET transactions better and dispose the transaction in order to release the connection to db2. What we were seeing is that it would use all the servers we had allocated because they were staying connected. You are not suggesting that we use singleClient which is different than what I was told by Attunity support on issue 12209

  4. #4
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    You are correct - I do not suggest using singleClient mode because the problem seems to be at the client.

    The idea of using connection pooling is that the client logic thinks it opens and closes connection but in fact the system underneath maintains the physical connections.

    The question would be what kind of application is this and why the connections are pooled.

    If it is a relatively short-lived client program then pooled connection are better not used - instead, just hang to the a single connection (or two if needed).

    If this is an IIS application, for example, then connection pool must be used but then one needs to carefully plan how many connections to hold in the pool (when not in use). When a connection is closed using the close() method, it simply returns to the pool. If some scenario results in a connection not being closed, then it will not be available for reuse and a new physical connection will be needed.

    What kind of application you have? Do you have a stand-alone example that shows the problem?
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  5. #5
    Betty Sullivan is offline Junior Member
    Join Date
    Sep 2006
    Posts
    12
    Rep Power
    0

    .NETcomponents

    Our application is actually several VB6 applications & 1 Powerbuider application calling .NET components on MTS server. The .NET components have to have the COM interop wrappers to make them available to the VB6 & Powerbuilder. We are also using Attunity on the MTS server for the mainframe to call the .NET components. We also have a couple of .NET console applications for batch processing.

  6. #6
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    Of those various components you mention, you need to specifically identify the one that you are running out of connections for.

    A code that is a part of a server (e.g. an MTS component) must never directly try to manipulate the pool. In particular the following call:

    AisConnection.ClearPool(dbCmd.Connection)
    has no place in a server component.

    The following two calls do the same - you could just close the connection.


    dbCmd.Connection.Close()
    dbCmd.Connection.Dispose()
    In general, a pooling problem exist if a connection is closed and then the next "New AisConnection(strConn)" creates a new physical connection rather than reuse the one just closed.

    Obviously the code:


    If (Not dbCmd.Connection Is Nothing) Then
    dbCmd.Connection = Nothing
    End If
    is needed or else the dbCmd will hold a reference to the logical (no physical) connection until garbage-collected (which is not a good practice) but regardless of that, the moment the connection was closed, it no longer holds the enderlying physical connection which goes back to the pool, available for a new logical connection request from the pool.
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  7. #7
    Betty Sullivan is offline Junior Member
    Join Date
    Sep 2006
    Posts
    12
    Rep Power
    0

    .NET TransactionScope & DB2

    The connections threads to db2 are not getting released. This actually can be initiated from VB6, .NET and even mainframe applications because we are trying to use the same .NET code to sync the db2 & sql tables.

    I have been reading about the .NET transaction scope and DB2 - this is dependent on the provider that we use - does the Attunity provider support transaction scope?

  8. #8
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    The ADO.NET1.1 provider does not support distributed transaction - only local transaction. Therefore, it does not support the transaction attributes (such as "Requires New Transaction", etc).

    If you must use transactions then you should use the OLEDB provider and then from .NET application use the ADO.NET provider for OLEDB. VB6 components can directly use the OLEDB provider using ADO.

    Note that to use the OLEDB provider, one must install the AIS kit for Windows. There is no need to define local binding - the connect string can specify a bindUrl option to directly bind to a remote AIS server.
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  9. #9
    Betty Sullivan is offline Junior Member
    Join Date
    Sep 2006
    Posts
    12
    Rep Power
    0

    OLEDB Provider

    Thanks for the information. I am confused. Does the ADO.NET 1.1 product use its own OLEDB provider to connect? What advantages do I gain from using the ado.net 1.1 client versus the oledb provider?

  10. #10
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    >> Does the ADO.NET 1.1 product use its own OLEDB provider to connect?

    No. The Attunity ADO.NET1.1 is a thin client (no need for the full AIS product installation). It does have a limitation in that it does not implement the distributed transaction interfaces.

    >> What advantages do I gain from using the [Attunity] ADO.NET 1.1 client versus the OLEDB provider?

    The ADO.NET provider is small and lightweight and is written entirely in C# managed code. If it satisfies the application requirements then it is a good interface to use.

    The Attunity OLEDB provider is the more mature interface (more than 10 years in the market) and it still has a fuller technology coverage including distributed transaction support, OLEDB chapters support and the availability of a local query processor that can join tables from multiple data sources in a single query. To use the Attunity OLEDB provider one installs the full AIS product. To use this provider from .NET applications, the Microsoft ADO.NET provider for OLEDB should be used.
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

Posting Permissions

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