DB2 SQL Show Long and Short Table Names?

2
votes

I've started creating descriptive table names in DB2 and have found DB2 has a short name and a long name for each table.

EmployeePlan has a short name of Emplo00001

I would like to be able to display both names from a sql statement. Here's my existing SQL -- is there a table I can join to in order to get the short name?

select * --table_name, system_column_name, column_text, Type_Name, column_Size, *
from sysibm.SQLColumns
where table_schem IN ('LAWMOD9T', 'LIBDDS')
and upper(table_name) IN ('EMPLOYEEPLAN')
ORDER BY system_column_name

And thank you, Darius X for answering my question so quickly. Here's my final query:

SELECT b.system_table_name as ShortName,
       a.table_name, a.system_column_name, a.column_text, 
       a.type_name, a.column_size
FROM sysibm.SQLColumns a
INNER JOIN qsys2.systables b
ON a.table_name = b.table_name
AND a.table_schem = b.table_schema
WHERE UPPER(a.table_schem) IN ('LAWMOD9T', 'LIBDDS')
AND UPPER(a.table_name) IN ('EMPLOYEEPLAN')
ORDER BY a.table_schem, a.table_Name, a.ordinal_position
2
sqlnamingdb2-400

2 Answers

3
votes

There may be more than one way, but if you run this query:

select *
from qsys2.systables
where table_schema IN ('LAWMOD9T', 'LIBDDS')

You'll see that SYSTEM_TABLE_NAME is one of the columns. So, you can join to qys2.systables using the schema and "long" table name.

0
votes

You can add too the short name filter too

select 
TABLE_NAME 
from QSYS2.SYSTABLES 
where table_schema = 'SchemaName' AND SYSTEM_TABLE_NAME = 'SystemName';