Topic
  • 6 replies
  • Latest Post - ‏2011-08-04T06:54:27Z by LJuergens
LJuergens
LJuergens
14 Posts

Pinned topic Connection to Source DB on AS400 does not get closed after ETL is finished

‏2011-07-22T07:10:09Z |
We are running a couple of ETL jobs created in Infosphere Design Studio 9.7.2 against an AS400 Database. These jobs are using only a sql query opperator to select the data and write the data into the reporting database DB2 9.7.2 on a Windows 2003 server.

When the ETL is scheduled in WebSphere in many cases the connection to source database does not get closed and the user stays connected.

This is causing issues when the MQT like tables on the AS 400 are refreshed.

Is there any possibility to close the connection to the AS400 at the end of the ETL job, possibly with the respective session ID used on the AS400?

The reason why SQL opperators are beeing used is that the required data collection is quite complex and we were looking for a quick translation of existing sql extraction jobs to a reliable scheduling tool.
Updated on 2011-08-04T06:54:27Z at 2011-08-04T06:54:27Z by LJuergens
  • zxning
    zxning
    20 Posts

    Re: Connection to Source DB on AS400 does not get closed after ETL is finished

    ‏2011-07-27T05:44:46Z  
    Hi LJuergens:

    Currently we do not close the connection, and keep it in memory, to avoid re-creation when needed a second time. We think it should not be a problem. You mentioned that your MQT like tables has issues if detected live connections. Can you describe what your problem is here in more detail? We may deal with this specific case for you based on your problem.
  • LJuergens
    LJuergens
    14 Posts

    Re: Connection to Source DB on AS400 does not get closed after ETL is finished

    ‏2011-07-27T08:22:12Z  
    • zxning
    • ‏2011-07-27T05:44:46Z
    Hi LJuergens:

    Currently we do not close the connection, and keep it in memory, to avoid re-creation when needed a second time. We think it should not be a problem. You mentioned that your MQT like tables has issues if detected live connections. Can you describe what your problem is here in more detail? We may deal with this specific case for you based on your problem.
    Hi,
    according to the AS440 DBAs we have that with an open connection the MQT like objects occasionally can not be refreshed.
    Therefore I was hoping to get a recomendation for a scritp at the end of the ETL job to close the connection to the source DB.
  • mengh
    mengh
    3 Posts

    Re: Connection to Source DB on AS400 does not get closed after ETL is finished

    ‏2011-08-02T10:39:53Z  
    • LJuergens
    • ‏2011-07-27T08:22:12Z
    Hi,
    according to the AS440 DBAs we have that with an open connection the MQT like objects occasionally can not be refreshed.
    Therefore I was hoping to get a recomendation for a scritp at the end of the ETL job to close the connection to the source DB.
    Hi LJuergens,

    In order to help us to narrow down the root cause, could you provide the information that,
    1. What kind of refresh configuration for these MQTs?
    2. When MQTs can't be refeshed, do you once try refresh manually?

    Meng
  • LJuergens
    LJuergens
    14 Posts

    Re: Connection to Source DB on AS400 does not get closed after ETL is finished

    ‏2011-08-02T10:59:14Z  
    • mengh
    • ‏2011-08-02T10:39:53Z
    Hi LJuergens,

    In order to help us to narrow down the root cause, could you provide the information that,
    1. What kind of refresh configuration for these MQTs?
    2. When MQTs can't be refeshed, do you once try refresh manually?

    Meng
    Hi,
    I catually do have no detailed information about the processes on the AS400.

    However, in the meantime we have found the following.

    The time reproted for the opening on of the connection on the AS400 is totally wrong, for whatever reason.

    My AS400 Admin complained about connections beeing open and not closed with a timestamp of than office hours. This timestamp lead to the assumption that the connection is related to ETL jobs started at the particular time as we have sevreal job running at around the times in question.

    When I asked the AS400 Amdin the oher day to close the connection established at 06:30 AM, I actually lost the connection in my SQL query tool which I use during development to validate data. The AS400 user for the connection is the same as the one as the ELT user.

    Therefore we have to assume that the issue is not ETL related.
  • mengh
    mengh
    3 Posts

    Re: Connection to Source DB on AS400 does not get closed after ETL is finished

    ‏2011-08-03T02:16:03Z  
    • LJuergens
    • ‏2011-08-02T10:59:14Z
    Hi,
    I catually do have no detailed information about the processes on the AS400.

    However, in the meantime we have found the following.

    The time reproted for the opening on of the connection on the AS400 is totally wrong, for whatever reason.

    My AS400 Admin complained about connections beeing open and not closed with a timestamp of than office hours. This timestamp lead to the assumption that the connection is related to ETL jobs started at the particular time as we have sevreal job running at around the times in question.

    When I asked the AS400 Amdin the oher day to close the connection established at 06:30 AM, I actually lost the connection in my SQL query tool which I use during development to validate data. The AS400 user for the connection is the same as the one as the ELT user.

    Therefore we have to assume that the issue is not ETL related.
    Do you meaning that, after some investigation, you found,
    the connection that will affect MQTs refresh isn't ELT related? I am not clear about the timestample and 6:30AM you mentioned here. Let us know if you still need our investigation.
  • LJuergens
    LJuergens
    14 Posts

    Re: Connection to Source DB on AS400 does not get closed after ETL is finished

    ‏2011-08-04T06:54:27Z  
    • mengh
    • ‏2011-08-03T02:16:03Z
    Do you meaning that, after some investigation, you found,
    the connection that will affect MQTs refresh isn't ELT related? I am not clear about the timestample and 6:30AM you mentioned here. Let us know if you still need our investigation.
    Hi,
    the connection is at least not ETl Schedule related. It is related to connections opened during development.

    06:30 is the timestamp of the connection the AS400 management console is displaying. However, we have to assume that even though I am in the same timezone as the AS400 it does have a wrong setting regarding the system time.

    So, in essence you can stop your investigation.