Hitting some limit under z/OS with DB2 Connect JDBC t4 driver.

Question

We have an application connecting to DB2 under z/OS and, after a while, there seems to be some resource limit being hit on the mainframe side. Since we're using BIRT, it seems the only control we have over the JDBC code is with stanzas in the URL itself. we don't have direct Java control over the connection or statements (except for the SQL itself of course) although it may be possible by using Javascript within the report design. So we can turn on debugging with something like:

jdbc:db2://machine.com:1234/INSTANCE:traceFile=c:/db2.txt;traceLevel=-1;

Eventually the application using JDBC will simply stop and no more data will be written to the log file. Doing a TSO NETSTAT on the mainframe shows about 50 sessions in ESTABLISHED state.

Now we know this is a problem on the mainframe side since, when it happens, no JDBC connection to that instance will work (from any client). At that point, we have to restart the database to continue.

I've googled quite a lot of stuff, some of which seems to indicate that you may need to commit queries before you close a session. It may be that the sessions may be being held open because there's something wrong in the BIRT close code (at least in terms of what DB2 expects).

Has anyone experienced anything like this before? How did you fix it (if at all)? Is there a way to solve it by using just the JDBC URL stanzas or Javascript code within the report design?

FWIW, we're using DB2 9.1 and BIRT 2.2.1.

Answer 1

This was actually solved in another forum, I'm copying the solution here for posterity.

It turns out there's a parameter called IDTHTOIN in the DSN6FAC section of the DB2 parameters assembly/link job (generally db2prefix.SDSNSAMP(DSNTIJUZ) though your setup may be different) which was set to zero in our case. This parameter is the IDLE TIME OUT for DDF threads and zero means "no timeout".

Setting this to 180 solved our problem. The threads that were holding locks were shut down if they hadn't had any activity in those three minutes. Setting it to less than 120 is not useful since the threads are only checked every two minutes anyway (in DB2 v9 at least).

You should also set CMTSTAT=INACTIVE to protect well-behaved threads (those that have released all their resource locks but are still holding the thread open).

Keep in mind this was okay for our particular problem since the threads were for reports. Their behavior was such that the opened a session, got the data for reporting, then didn't need the session any more. If you have long-running sessions, you need to be careful (although any session that holds locks for more than three minutes is suspect anyway).

You should edit the DSNTIJUZ member, run the job, then either recycle the DB2 instance or execute SET SYSPARM.

Thanks to the helpful bods at IBM Australia (West Perth Lab) for nutting this out for me.