Myujth

I am fairly new to DB2 (and SQL in general) and I am having trouble finding an efficient method to DECODE columns

Currently, the database has a number of tables most of which have a significant number of their columns as numbers, these numbers correspond to a table with the real values. We are talking 9,500 different values (e.g '502=yes' or '1413= Graduate Student')

In any situation, I would just do WHERE clause and show where they are equal, but since there are 20-30 columns that need to be decoded per table, I can't really do this (that I know of).

Is there a way to effectively just display the corresponding value from the other table?

Example:

SELECT TEST_ID, DECODE(TEST_STATUS, 5111, 'Approved, 5112, 'In Progress') TEST_STATUS
FROM TEST_TABLE

The above works fine.......but I manually look up the numbers and review them to build the statements. As I mentioned, some tables have 20-30 columns that would need this AND some need DECODE statements that would be 12-15 conditions.

Is there anything that would allow me to do something simpler like:

SELECT TEST_ID, DECODE(TEST_STATUS = *TableWithCodeValues*) TEST_STATUS
FROM TEST_TABLE

EDIT: Also, to be more clear, I know I can do a ton of INNER JOINS, but I wasn't sure if there was a more efficient way than that.

From a logical point of view, I would consider splitting the lookup table into several domain/dimension tables. Not sure if that is possible to do for you, so I'll leave that part.

As mentioned in my comment I would stay away from using DECODE as described in your post. I would start by doing it as usual joins:

SELECT a.TEST_STATUS
 , b.TEST_STATUS_DESCRIPTION
 , a.ANOTHER_STATUS
 , c.ANOTHER_STATUS_DESCRIPTION
 , ...
FROM TEST_TABLE as a
JOIN TEST_STATUS_TABLE as b
 ON a.TEST_STATUS = b.TEST_STATUS
JOIN ANOTHER_STATUS_TABLE as c
 ON a.ANOTHER_STATUS = c.ANOTHER_STATUS
JOIN ...

If things are too slow there are a couple of things you can try:

• Create a statistical view that can help determine cardinalities from the joins (may help the optimizer creating a better plan):

https://www.ibm.com/support/knowledgecenter/sl/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0021713.html

• If your license admits you can experiment with Materialized Query Tables (MQT). Note that there is a penalty for modifications of the base tables, so if you have more of a OLTP workload, this is probably not a good idea:

https://www.ibm.com/developerworks/data/library/techarticle/dm-0509melnyk/index.html

A third option if your lookup table is fairly static is to cache the lookup table in the application. Read the TEST_TABLE from the database, and lookup descriptions in the application. Further improvements may be to add triggers that invalidate the cache when lookup table is modified.

If you don't want to do all these joins you could create yourself an own LOOKUP function.

create or replace function lookup(IN_ID INTEGER)
returns varchar(32) 
deterministic reads sql data 
begin atomic 
declare OUT_TEXT varchar(32);-- 
set OUT_TEXT=(select text from test.lookup where id=IN_ID);-- 
return OUT_TEXT;-- 
end;

With a table TEST.LOOKUP like

create table test.lookup(id integer, text varchar(32))

containing some id/text pairs this will return the text value corrseponding to an id .. if not found NULL.

With your mentioned 10k id/text pairs and an index on the ID field this shouldn't be a performance issue as such data amount should be easily be cached in the corresponding bufferpool.