DB2 (AS400/ i Series) column value description

Question

I found a query online which helps me get all the column names along with the column description, which I have pasted below.

Select
SYSTEM_TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, varchar(COLUMN_TEXT, 50) As COLUMN_DESC
From QSYS2.syscolumns
WHERE TABLE_NAME = 'xxxxxx' AND SYSTEM_TABLE_SCHEMA = 'yyyyyy'

Are there a query that gives the column value description? I am attaching a sample output desired below. Column [Item Code] has three values: A,B,C -> I want the corresponding description for those values.

Desired Output

Answer 1

It depends on the structure of your data. SQL does not know the description of the code values. That is data contained in your database. So if you have a table that contains the description of the codes, then you you can get that with a join. If you do not have such a table, then you cannot get that information. Here is an example of how this might work for you.

create table master (
  id       integer primary key,
  name     varchar(128) not null,
  code     varchar(10) not null);

create table codes (
  id           varchar(10) primary key,
  description  varchar(128) not null);

insert into master 
  values (1, 'test1', 'A'),
         (2, 'test2', 'B'),
         (3, 'test3', 'C'),
         (4, 'test4', 'A'),
         (5, 'test5', 'B');

insert into codes
  values ('A', 'Code 1'),
         ('B', 'Code 2'),
         ('C', 'Code 3');

SELECT master.id, master.name, master.code, codes.description
FROM master 
JOIN codes on master.code = codes.id;

|ID|NAME |CODE|DESCRIPTION|
|--|-----|----|-----------|
|1 |test1|A   |Code 1     |
|2 |test2|B   |Code 2     |
|3 |test3|C   |Code 3     |
|4 |test4|A   |Code 1     |
|5 |test5|B   |Code 2     |

Answer 2

If your database is properly built, then there should be a referential (aka foreign key) constraint defined between your table 'XXXXX' and the 'codes' table jmarkmurphy provided an example of.

There are various system catalogs which will show that constraint.

IBM i

• qsys2.syscst
• qsys2.syscstcol
• qsys2.syscstdep
• qsys2.syskeycst

ODBC/JDBC

• sysibm.SQLFOREIGNKEYS

ANS/ISO

• qsys2.REFERENTIAL_CONSTRAINTS

Unfortunately, many legacy applications on Db2 for i don't have such constraints defined.

It's also possible that the "codes" table doesn't exist either. And that the description is simply hard coded in various programs.