#447 DatabaseMetaData.getTables returns Systemtables

[Claus]

How To Test

1 truncate alle tables in Database Schema.

truncate table TABLENAME drop storage restart identity immediate;

2 While this operation is running, do a DatabaseMetaData.getTables:

metaData.getTables(null, schemeName, null, new String[] {"TABLE"});

3 Try to access all Tables which has been returned.
4 Exception occurs:

14:49:30.951 | 6480         | DEBUG | main                           | JdbcBasedTupleSchemeBuilder.java   :638   | reading primary key metadata for table <Q_AT000011>
14:49:32.675 | 8204         | ERROR | main                           | BaseBatchApplicationImpl.java      :1093  | error in batch app occured:
 java.lang.IllegalStateException: processing stopped
    at de.xxxx.util.Sys.newIllegalState(Sys.java:215)
    at de.xxxx.xxxxx.mig.xxx.jobs.BaseJob.process(BaseJob.java:93)
    at de.xxxx.xxxxx.mig.xxx.XxxxXxxx.process(XxxxXxxx.java:235)
    at de.xxxx.xxxxx.mig.xxx.XxxxXxxx.main(XxxxXxxx.java:225)
Caused by: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0204] Q_AT000011 der Art *FILE in SCHEMANAME nicht gefunden.
    at com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:948)
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:745)
    at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1790)
    at com.ibm.as400.access.AS400JDBCStatement.executeQuery(AS400JDBCStatement.java:2523)
    at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:114)

Reason
While retrieving the list of tables and iteration in our Java Code - Tables vanish from database.

Why?
Due to a truncate drop storage it seems that the System i is creating a Table to perform this truncate. This Tables have a name like Q_AT000000 - as we do not have a table Name like AT% i do not know how this name is generated.

select * 
from qsys2.systables
where table_schema = 'XXXXX'
and table_name like 'Q%';

Column name	Data
TABLE_NAME	Q_AT000000
TABLE_OWNER	GRPIT
TABLE_TYPE	T
COLUMN_COUNT	24
ROW_LENGTH	349
TABLE_TEXT	%V
LONG_COMMENT	<null></null>
TABLE_SCHEMA	schemaname
LAST_ALTERED_TIMESTAMP	2021-05-17 11:16:39.266
SYSTEM_TABLE_NAME	Q_AT000000
SYSTEM_TABLE_SCHEMA	schemaname
FILE_TYPE	D
BASE_TABLE_CATALOG	<null></null>
BASE_TABLE_SCHEMA	<null></null>
BASE_TABLE_NAME	<null></null>
BASE_TABLE_MEMBER	<null></null>
SYSTEM_TABLE	N
SELECT_OMIT	N
IS_INSERTABLE_INTO	YES
IASP_NUMBER	0
ENABLED	<null></null>
MAINTENANCE	<null></null>
REFRESH	<null></null>
REFRESH_TIME	<null></null>
MQT_DEFINITION	<null></null>
ISOLATION	<null></null>
PARTITION_TABLE	NO
TABLE_DEFINER	user
MQT_RESTORE_DEFERRED	<null></null>
ROUNDING_MODE	<null></null>
CONTROL	%V
TEMPORAL_TYPE	N

Solution
Due to
https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html
(getTables)

TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".

I would not expect a System Table Q% in option TABLE

Bugfix
Can you filter this Q-Tables and return them only if we use SYSTEM TABLE as Parameter?

[Claus]

I see there is an Option SYSTEM_TABLE in Result. This value is N.
Would it be better to create a Ticket at IBM? I guess this is used to return values as TABLES or SYSTEM TABLES?

[John Eberhard]

The problem is the Q_ table isn't a "SYSTEM TABLE", it's a user table, but a temporary user table created by the system during the truncation operation. Your program that is looking at the tables needs to handle the case where a table is dropped between the time of the getTables call and the time that the program attempts to access the table.