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.
This topic has been locked.
6 replies Latest Post - 2011-08-04T06:54:27Z by LJuergens
Pinned topic Connection to Source DB on AS400 does not get closed after ETL is finished
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2011-08-04T06:54:27Z at 2011-08-04T06:54:27Z by LJuergens
zxning 2700002RFJ20 PostsACCEPTED ANSWER
Re: Connection to Source DB on AS400 does not get closed after ETL is finished2011-07-27T05:44:46Z in response to LJuergensHi 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.
Re: Connection to Source DB on AS400 does not get closed after ETL is finished2011-07-27T08:22:12Z in response to zxningHi,
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 270001HW823 PostsACCEPTED ANSWER
Re: Connection to Source DB on AS400 does not get closed after ETL is finished2011-08-02T10:39:53Z in response to LJuergensHi 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?
Re: Connection to Source DB on AS400 does not get closed after ETL is finished2011-08-02T10:59:14Z in response to menghHi,
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 270001HW823 PostsACCEPTED ANSWER
Re: Connection to Source DB on AS400 does not get closed after ETL is finished2011-08-03T02:16:03Z in response to LJuergensDo 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.
Re: Connection to Source DB on AS400 does not get closed after ETL is finished2011-08-04T06:54:27Z in response to menghHi,
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.