Topic
  • 4 replies
  • Latest Post - ‏2010-02-16T16:27:38Z by SystemAdmin
SystemAdmin
SystemAdmin
532 Posts

Pinned topic Do I need to release and initilize the Data Source builder manually?

‏2010-02-12T22:27:19Z |
Do I need to release and initilize the Data Source builder manually?

I have an application which every morning is giving me sale connection errors
or connection reset errors with oracle the first time that I enter it.

After that the application runs pretty much smoothly.

Now I see that portlet factory has the following methods for data sources:

acquireConnection
initialize
releaseConnection

Now I always thought that the connections were managed automatically, but
would any of these methods help me with my connection problems?

Thank you,
Updated on 2010-02-16T16:27:38Z at 2010-02-16T16:27:38Z by SystemAdmin
  • kevintap
    kevintap
    41 Posts

    Re: Do I need to release and initilize the Data Source builder manually?

    ‏2010-02-13T03:50:22Z  
    The best way that I've found to deal with stale connection issues as you describe is to let the App Server handle it for you.

    The WAS admin console varies a bit by version, but in the Data Source properties for each data source, there is a option to pre-test connections. This configures the App Server to execute a SQL statement against the data source (usually something like SELECT 1 FROM SYSIBM.SYSDUMMY1) prior to it handing that connection to the application. This virtually ensures that the application (portlet) will always have a good, tested connection to the data source. (I say virtually here, since there is no real guarantee that the connection won't go bad between the App Server's test and when the application gets the connection, but this is a very small window.)
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: Do I need to release and initilize the Data Source builder manually?

    ‏2010-02-13T05:23:01Z  
    • kevintap
    • ‏2010-02-13T03:50:22Z
    The best way that I've found to deal with stale connection issues as you describe is to let the App Server handle it for you.

    The WAS admin console varies a bit by version, but in the Data Source properties for each data source, there is a option to pre-test connections. This configures the App Server to execute a SQL statement against the data source (usually something like SELECT 1 FROM SYSIBM.SYSDUMMY1) prior to it handing that connection to the application. This virtually ensures that the application (portlet) will always have a good, tested connection to the data source. (I say virtually here, since there is no real guarantee that the connection won't go bad between the App Server's test and when the application gets the connection, but this is a very small window.)
    Hi thank you for your feedback.

    Its funny I had also though about performing some trivial query
    to make sure that the connections were fine. But I'll be sure to
    look into enabling the pre test on the data sources.

    So I guess that I wasn't totally wrong in regards to the WPF
    all the connection stuff is handled for me.

    thanks again
  • DGawron
    DGawron
    251 Posts

    Re: Do I need to release and initilize the Data Source builder manually?

    ‏2010-02-16T16:00:47Z  
    • kevintap
    • ‏2010-02-13T03:50:22Z
    The best way that I've found to deal with stale connection issues as you describe is to let the App Server handle it for you.

    The WAS admin console varies a bit by version, but in the Data Source properties for each data source, there is a option to pre-test connections. This configures the App Server to execute a SQL statement against the data source (usually something like SELECT 1 FROM SYSIBM.SYSDUMMY1) prior to it handing that connection to the application. This virtually ensures that the application (portlet) will always have a good, tested connection to the data source. (I say virtually here, since there is no real guarantee that the connection won't go bad between the App Server's test and when the application gets the connection, but this is a very small window.)
    The idle connection timeout in your DB is less than the idle timeout in the app server. What you want to do is set the idle connection timeout used by the app server to be less than the one used by the DB. That way the app server will reap and close unused connections before the DB has a chance to invalidate them on its end. This should be more efficient than executing test queries each time a connection is acquired.

    As an aside, you should always implement error handling for any back-end access. These automted techniques (the one Kevin suggested and the one described above) can only take you so far. Your app still needs to be written to handle exceptional cases such as the DB not even being available.
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: Do I need to release and initilize the Data Source builder manually?

    ‏2010-02-16T16:27:38Z  
    • DGawron
    • ‏2010-02-16T16:00:47Z
    The idle connection timeout in your DB is less than the idle timeout in the app server. What you want to do is set the idle connection timeout used by the app server to be less than the one used by the DB. That way the app server will reap and close unused connections before the DB has a chance to invalidate them on its end. This should be more efficient than executing test queries each time a connection is acquired.

    As an aside, you should always implement error handling for any back-end access. These automted techniques (the one Kevin suggested and the one described above) can only take you so far. Your app still needs to be written to handle exceptional cases such as the DB not even being available.
    Currently I just have a default error handler but I could handle database specific errors separately.

    And thanks I'll look into the timeout issue.