SELECT or INSERT might fail when run in same logical unit of work as DCL statement

The SELECT or INSERT statement might fail with [SQLCODE=-5105] [SQLSTATE=58040] when it runs in the same logical unit of work as a DCL (GRANT / REVOKE) statement.

Symptoms

You might receive the following errors:


DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL5105N  The statement failed because a Big SQL component encountered an 
error.  Component receiving the error: "SCHEDULER".  Component returning the 
error: "SCHEDULER".  Log entry identifier: "[SCL-0-19cb855]".  Reason: "".  
SQLSTATE=58040

You might see the following type of error in the db2diag.log:


PID     : 4073802              TID : 140252958156544 PROC : db2sysc 0
INSTANCE: xxxxxxxx             NODE : 000            DB   : BLUDB
APPHDL  : 0-94                 APPID: 127.0.0.1.55004.230509173831
AUTHID  : HIVE                 HOSTNAME: xxxxxxx-sert7-x86
EDUID   : 162                  EDUNAME: db2agent (BLUDB) 0
FUNCTION: DB2 UDB, base sys utilities, sqeAgent::AgentBreathingPoint, probe:16
CALLSTCK: (Static functions may not be resolved correctly, as they are resolved to the nearest symbol)
  [0] 0x00007F8F57973756 _ZN8sqeAgent19AgentBreathingPointEi + 0x2A6
  [1] 0x00007F8F5A415C30 _Z13sqlplWaitOnWPP9sqeBsuEduP14SQLP_LOCK_INFOP8SQLP_LRBP15SQLP_LTRN_CHAINbbb + 0x1730
  [2] 0x00007F8F5A400673 _Z24sqlplMakeNewRequestNonSDP9sqeBsuEduP14SQLP_LOCK_INFOP11SQLP_TENTRYP8SQLP_LRBS6_P15SQLP_LTRN_CHAINbbb + 0x4C3
  [3] 0x00007F8F5A1F683A _Z7sqlplrqP9sqeBsuEduP14SQLP_LOCK_INFO + 0x254A
  [4] 0x00007F8F5187456D _Z13sqldLockTableP8sqeAgentP14SQLP_LOCK_INFOjti + 0x11D
  [5] 0x00007F8F5197EBDD _Z12sqldScanOpenP8sqeAgentP14SQLD_SCANINFO1P14SQLD_SCANINFO2PPv + 0x19AD
  [6] 0x00007F8F5B0CDACE _ZN16sqlrlCatalogScan4openEv + 0x49E
  [7] 0x00007F8F5B2A77A3 _Z29sqlrlBuildRoleListScanCatalogP8sqlrr_cbPhscjP13SQLO_MEM_POOLjjP16sqlrlCatalogScanS5_P10sqlr_rolesPj + 0x343
  [8] 0x00007F8F5B288AF2 _Z34sqlrlcFetchRolesForAuthidsFromDiskP8sqlrr_cbPP19sqlrlcRolesRequiredPmS4_ + 0x112
  [9] 0x00007F8F5B290F4E _Z25sqlrlcAuthidsFindRolelistP8sqlrr_cbP11sqlr_aainfomP13SQLO_MEM_POOLPP19sqlrlcRolesRequiredPmS8_m + 0xADE
  [10] 0x00007F8F5B2916F1 _Z31sqlrlcRequestRolelistsForAuthidP8sqlrr_cbP11sqlr_aainfobbb + 0x241
  [11] 0x00007F8F5A62716C _Z15sqlrr_appl_initP8sqeAgentP5sqlcaj + 0x3F1C
  [12] 0x00007F8F57985444 _ZN14sqeApplication20InitEngineComponentsEcP8sqeAgentP8SQLE_BWAP5sqlcaP22SQLESRSU_STATUS_VECTORc + 0x884
  [13] 0x00007F8F57988FA1 _ZN14sqeApplication13AppStartUsingEP8SQLE_BWAP8sqeAgentccP5sqlcaPc + 0x11A1
  [14] 0x00007F8F5798F57B _Z22sqleProcessConnectTypeP14db2UCinterfaceP8SQLE_BWAP8sqeAgentP5sqlcaP22SQLELOST_STATUS_VECTOR + 0x8B
  [15] 0x00007F8F57990B7B _ZN14sqeApplication13AppLocalStartEP14db2UCinterface + 0x7BB

2023-05-09-10.39.01.497799-420 I404715E599           LEVEL: Error
PID     : 4073802              TID : 140252958156544 PROC : db2sysc 0
INSTANCE: xxxxxxxx             NODE : 000            DB   : BLUDB
APPHDL  : 0-94                 APPID: 127.0.0.1.55004.230509173831
AUTHID  : HIVE                 HOSTNAME: xxxxxxx-sert7-x86
EDUID   : 162                  EDUNAME: db2agent (BLUDB) 0
FUNCTION: DB2 UDB, catcache support, sqlrlcFetchRolesForAuthidsFromDisk, probe:100
RETCODE : ZRC=0x80100003=-2146435069=SQLP_LINT "Interrupt from application"
          DIA8003C The interrupt  has been received.

You might see the following type of error in the bigsql-sched.log on the scheduler host:


bigsql-sched.log:2023-05-07T22:16:49,334 
ERROR com.ibm.biginsights.bigsql.scheduler.server.cache.DescriptorTableCache 
[TThreadPoolServer WorkerProcess-%d] : [SCL-0-7f9ca7b32] Failed crash recovery for table: schemaName=myschema tableName=test.

Causes

The suspected cause of these issues is lock contention between the Db2 catalog processing and the required access to the local Hive Metastore used to store metadata for Datalake tables.

Resolving the problem

It is recommended that DDL and DCL statements be run in a separate logical unit of work as DML statements. Adding a COMMIT statement after the DCL statements prior to executing DML statements resolves the issue.