cancel
Showing results for 
Search instead for 
Did you mean: 

DB2 on zOS: Can't get list of tables

Former Member
0 Kudos

Hi all,

I'm using JDBC driver jars: db2jcc.jar and db2jcc_license_cisuz.jar that we use with our other Java apps.

When going to the SQL Queries Detail page (or the Fixed Query Details page) I wait a while, then get this error message:

com.sap.xmii.Illuminator.logging.LHException: SQL error has occurred on query Invalid operation: result set closed

They see this on the mainframe, which I think is a timeout, although I don't know if it is at the same time:

DSNL027I  | SERVER DISTRIBUTED AGENT WITH  419
           LUWID=GA3E1093.GE9F.011BE704E603=19606
           THREAD-INFO=USER:mii-server:user:db2jccSAPEngine_Application_Thre
           RECEIVED ABEND=04E
           FOR REASON=00D3003B

Any ideas what is going wrong?

--Amy Smith

--Haworth

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Can't seem to get anywhere with this... DB2 guys are no help. Sigh. Is working so am closing this issue on my list. Thanks for the suggestions!

--Amy Smith

jcgood25
Active Contributor
0 Kudos

Amy,

To rule out a timeout specific to the WB, try running a URL such as this /XMII/Illuminator?Server=XXXX&Mode=TableList (where XXXX is the name of your Data Server).

Assuming that your DB2 server may have a very large combination of schemas / tables you might try limiting the connection string to a single schema, such as "jdbc:as400://mySys2/mySchema" (reference: http://publib.boulder.ibm.com/infocenter/iadthelp/v7r0/index.jsp?topic=/com.ibm.etools.iseries.toolb...

Regards,

Jeremy

Former Member
0 Kudos

Thanks Jeremy,

I get the same error message using the URL so it is not the WorkBench.

Thanks for the connection string hints. We are working with zOS and it is a different DB2 product than AS400 with a different connect string. You'd think they were the same, but no. I tried a couple of params to no avail. currentSchema and cliSchema

I am giving up for now as the fixed queries are working, but am thinking it will pop up again.

Thanks again,

--Amy Smith

--Haworth

0 Kudos

Hi Amy,

Apparently you are experiencing a thread timeout while the query script is running in DB2 land. At least that is what all the google results are pointing to. Try googling 'ABEND=04E REASON=00D3003B' for more information. You can try to adjust the timeout for the data server in MII. It is in Data Services - Data Servers. If your query is going to take a long time to execute in DB2, you may want to think about how to improve its performance on the database side (custom view, stored procedure, user function, etc.).

Good luck,

Mike

Former Member
0 Kudos

Thanks Mike,

It is actually the retrieval of the schema that is taking so long. I had to enter a fixed query, which runs quite quickly.

I looked for timeouts on the data source and only found these two:

1. This seems to imply how long it waits while trying to get a connection, and it doesn't give unit of measure! Ours defaulted to 30.

WaitTime:  Amount of time SAP xMII waits for a connection before it fails.

2. What constitues an "used connection" Is a connection assigned to a session? Ours defaulted to 15 even though the help says 30 or greater!

TimeOut : Length of time (in minutes) that elapses before an unused connection is returned to the pool.
Enter a value equal to or greater than 30.

Neither could I find a timeout on the query itself.

Could you give me any more insight?

Thanks again,

Amy Smith

Haworth

0 Kudos

Hi Amy,

First things first, what version are you using?

There is no timeout on the query template, only on the data source. However, having said that, you can dynamically adjust the timeout from a transaction which contains the SQL Query action block. If you are working from a webpage, you should also be able to set it using javascript. I have had to do this numerous times over my Lighthammer/xMII/MII years. So I would try that first and see what your results are.

Another thought is to have the DB2 UserID permissions limited to certain tables in the database. Eliminate access to the system tables might help with the schema loading. It has been a while since I worked with DB2, so I am not sure how to tailor the permissions. Maybe your DBA could make some suggestions. He or she might have some other thoughts along those lines.

Good luck,

Mike