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

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
    ACCEPTED ANSWER

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

    ‏2011-07-27T05:44:46Z  in response to LJuergens
    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
      ACCEPTED ANSWER

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

      ‏2011-07-27T08:22:12Z  in response to zxning
      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
        ACCEPTED ANSWER

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

        ‏2011-08-02T10:39:53Z  in response to LJuergens
        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
          ACCEPTED ANSWER

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

          ‏2011-08-02T10:59:14Z  in response to mengh
          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
            ACCEPTED ANSWER

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

            ‏2011-08-03T02:16:03Z  in response to LJuergens
            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
              ACCEPTED ANSWER

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

              ‏2011-08-04T06:54:27Z  in response to mengh
              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.