表创建

CREATE TABLE 
    "testdb".person
    (
        id      integer NOT NULL PRIMARY KEY,
        name    varchar(16) NOT NULL,
        age     integer NOT NULL,
        address varchar(100)
    )

查询表数量

select count(*)
from QSYS2.SYSTABLES
where lower(TABLE_SCHEMA) = lower('testdb');

查询表中数据量

select NUMBER_ROWS
from QSYS2.SYSTABLESTAT
where lower(TABLE_SCHEMA) = lower('testdb')
  and lower(TABLE_NAME) = lower('person2');

查询表基本元数据

select TABLE_NAME,
       case
           when TABLE_TYPE = 'A' then 'Alias'
           when TABLE_TYPE = 'L' then 'Logical file'
           when TABLE_TYPE = 'M' then 'Materialized query table'
           when TABLE_TYPE = 'P' then 'Physical file'
           when TABLE_TYPE = 'T' then 'Table'
           when TABLE_TYPE = 'V' then 'View'
           else 'Unknow'
           end,
       LONG_COMMENT
from QSYS2.SYSTABLES
where lower(TABLE_SCHEMA) = lower('testdb')
  and lower(TABLE_NAME) like lower('%p%')
offset 1 rows fetch next 1 rows only

查询列数量

select count(*)
from QSYS2.SYSCOLUMNS
where lower(TABLE_SCHEMA) = lower('testdb')
  and lower(TABLE_NAME) like lower('%p%')

查询列基本元数据

select c.COLUMN_NAME,
       c.DATA_TYPE,
       k.COLUMN_NAME,
       c.IS_NULLABLE,
       c.LONG_COMMENT
from QSYS2.SYSCOLUMNS c
         left join QSYS2.SYSKEYCST k on c.TABLE_NAME = k.TABLE_NAME and c.TABLE_SCHEMA = k.TABLE_SCHEMA and c.COLUMN_NAME = k.COLUMN_NAME
where lower(c.TABLE_SCHEMA) = lower('testdb')
  and lower(c.TABLE_NAME) like lower('%p%')