[guow_99]

Hi,

It seems when using DB Explorer to execute Select statement, if the number of rows returned is greater than the preview limit defined in preference (max is 100), the locks aquired in Database (we are using DB2 V7.1 on Z/OS) are not released, even though auto commit is set to true. It seems the ResultSet and Statment are not closed until all the rows are retrieved by clicking on the “retrieve all rows” button.

This causes us problems because developers are unconsiously locking tables if they only use the preview mode to test the execution speed of their SQLs.

Can this behavior be changed? Or at least give us some options in preference so all the locks are released immediately after each SQL execution.

Thanks.

[Riyad Kalla]

I will pass this along to the DB team ASAP.

[Haris Peco]

guow_99,

MyEclipse close statement and result set after load preview rows.
You talk about sql editor queries, probably
‘Retrieve all rows’ is in Result view (when you make query from sql editor) and limit for this is 5000 and you can disable limit ever (it isn’t suggested, because all your rows will be fetched in memory and you can got ‘out of memory’ for big tables).We can’t close result set in this view, because user couldn’t load all rows – if we do it, other user will complain.
However, it’s very bad database features if some readers (select) lock something (other reader or ever other writers) , but some databases do that.
I advice you that you increase limit and suggest to your users that search big tables in preview only and use result view selective.I suppose that you have problems just with big tables.

Best regards

[guow_99]

Thanks for the quick reply.

Is this preview view a new feature of MyEclipse 5? We are using 4.1 currently.

However, even for Result View, I think it will be better if there is a preference that allow my Eclipse to close the RS and Statement after loading the preview rows and then run the query again when ‘retrieve all rows’ button is clicked. This way, there is no chance for a programmer to forget about the query at the end of the day and go home, not knowing he is locking the tables to prevent the batch run at night.

Thanks.

[Haris Peco]

We will discuss about your proposal, but you can set ‘row limit for SQL execution’ to less
than ‘row limit for table preview’ and RS will be closed (or you can set both limits to 100)

Thanks

[Author]

Posts