Select might fail when run after a concurrent inserts

The SELECT statement might fail with SQL5105 or SQL58040 error when there is has been a concurrent insert into the Hive Datalake table.

Symptoms

The following errors may be returned:

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: "FRONT-END".  
Log entry identifier: "[SCL-0-1fff4db1c]".  Reason: "Failed to create partition: col_".
SQLSTATE=58040

The following error may be returned on the host:

Caused by: com.ibm.biginsights.common.exceptions.CatalogException: Failed to create partition: col_date_55_3_0=2023-11-01/col_varc_60_3_1=c/col_inte_66_3_2=112943379
        at com.thirdparty.cimp.catalog.HdfsTable.createPartitionAsync(HdfsTable.java:1127) ~[bigsql-thrift.jar:?]
        at com.thirdparty.cimp.catalog.HdfsTable$PartitionCreationTask.lambda$call$0(HdfsTable.java:2593) ~[bigsql-thrift.jar:?]
...
Caused by: java.io.FileNotFoundException: No such file or directory: s3a://db2whocp7odf/greentea/datalake/greentea/10092024/GT_DL/TABLE_DL136/col_date_55_3_0=2023-11-01/col_varc_60_3_1=c/col_inte_66_3_2=112943379

Causes

When concurrent INSERT statements are executed on the same table at the same time, a timing issue can occur where the HMS contains some partition information, but the corresponding file/directory does not exist on the file system.

Resolving the problem

Avoid running concurrent INSERT statements on the same table. If the SELECT statement continues to fail, the incorrect partitioning information will need to be removed from the HMS.