idadb.exists_table_or_view function could fail with deadlock in Db2 warehouse on Cloud

[cfkoh]

idadb.exists_table_or_view could fail with deadlock; if another user create a table but never commit.

1. Python code:
   db_tables=idadb1.show_tables(show_all=False)
   Actual SQL in backend:
   SELECT distinct TABSCHEMA, TABNAME, OWNER, TYPE from SYSCAT.TABLES WHERE (OWNERTYPE = 'U')AND(TABSCHEMA= 'DASH5421') ORDER BY "TABSCHEMA","TABNAME"

2. Python code:
   db_tables=idadb1.show_tables(show_all=True)
   Actual SQL in backend:
   SELECT distinct TABSCHEMA, TABNAME, OWNER, TYPE from SYSCAT.TABLES WHERE (OWNERTYPE = 'U') ORDER BY "TABSCHEMA","TABNAME"

In dashdb-entry-yp-syd01-01.services.au-syd.bluemix.net ; which is a multi-tenants, multi-users environment. There is another application , did a create table and never commit. Hence the case #2 above is causing dead lock, since it could not complete.

Here will be the errors from Python program:

Traceback (most recent call last):

File "/root/python_envs/ph-stack/local/lib/python2.7/site-packages/ibmdbpy/base.py", line 505, in exists_table
return self._exists(tablename,['T'])
File "/root/python_envs/ph-stack/local/lib/python2.7/site-packages/ibmdbpy/base.py", line 1507, in _exists
tablelist = self.show_tables(show_all=True)
File "/root/python_envs/ph-stack/local/lib/python2.7/site-packages/ibmdbpy/base.py", line 384, in show_tables
cache = self._retrieve_cache("cache_show_tables")
File "/root/python_envs/ph-stack/local/lib/python2.7/site-packages/ibmdbpy/base.py", line 2086, in _retrieve_cache
self._check_connection()
File "/root/python_envs/ph-stack/local/lib/python2.7/site-packages/ibmdbpy/base.py", line 2076, in _check_connection
raise IdaDataBaseError("The connection is closed")
IdaDataBaseError: ibmdbpy::IdaDataBaseError: The connection is closed

This query should work better:
SELECT distinct TABSCHEMA, TABNAME, OWNER, TYPE from SYSCAT.TABLES WHERE (OWNERTYPE = 'U') ORDER BY "TABSCHEMA","TABNAME" with UR;

In actual scenario, we have to get into the system and abort the user who is causing the deadlock (create table but did not commit). But this could happen again, putting it here to see of code fix is possible ?

from,
cfkoh@au1.ibm.com

[cfkoh]

Here is the scenario when this happened:

0: jdbc:db2://dashdb-entry-yp-syd01-01.servic> SELECT distinct TABSCHEMA, TABNAME, OWNER, TYPE from SYSCAT.TABLES WHERE (OWNERTYPE = 'U') ORDER BY "TABSCHEMA","TABNAME";
Error: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68, DRIVER=3.70.4 (state=40001,code=-911)