Query to select all tables with specific last column Ibm Db2 z/os

Goal: I need to create a query to select all tables with specific last column Ibm Db2 z/os

So I know I need to change my where clause for this but pretty much I want to select all the tables in my schema that have the last column as BATCH_ID. I’ve tried a length based where clause but can’t figure it out also I know MySQL has an ordinal position feature but that’s not in IBM DB2 z/Os as far as I’ve seen. Any help would be appreciated.

select c.tabschema as schema_name,
       c.tabname as table_name
from syscat.columns c
inner join syscat.tables t on 
      t.tabschema = c.tabschema and t.tabname = c.tabname
where c.colname = 'BATCH_ID' AND c.tabschema = 'WRIT5P1'
AND c.tabname not like 'OLD%'
and t.type = 'T'
order by schema_name, table_name;

​x
 
select c.tabschema as schema_name,       c.tabname as table_namefrom syscat.columns cinner join syscat.tables t on       t.tabschema = c.tabschema and t.tabname = c.tabnamewhere c.colname = 'BATCH_ID' AND c.tabschema = 'WRIT5P1'AND c.tabname not like 'OLD%'and t.type = 'T'order by schema_name, table_name; ​

Answer

SYSIBM.SYSTABLES
SYSIBM.SYSCOLUMNS

SELECT T.NAME AS TABLE_NAME
FROM SYSIBM.SYSTABLES T
JOIN SYSIBM.SYSCOLUMNS C ON C.TBNAME = T.NAME AND C.TBCREATOR = T.CREATOR
WHERE T.TYPE = 'T' AND T.CREATOR = 'WRIT5P1'
AND T.NAME NOT LIKE 'OLD%'
AND C.NAME = 'BATCH_ID'
AND C.COLNO = T.COLCOUNT

 
SELECT T.NAME AS TABLE_NAMEFROM SYSIBM.SYSTABLES TJOIN SYSIBM.SYSCOLUMNS C ON C.TBNAME = T.NAME AND C.TBCREATOR = T.CREATORWHERE T.TYPE = 'T' AND T.CREATOR = 'WRIT5P1'AND T.NAME NOT LIKE 'OLD%'AND C.NAME = 'BATCH_ID'AND C.COLNO = T.COLCOUNT​

I don’t remember if C.COLNO starts from 0 or 1. Edit the query accordingly, if it starts from 0.

Advertisement

Answer