On DB2 for i, Search for Column, return table names in list form

Question

I'm still a bit of a noob, so pardon if this question is a bit obvious. I did search for an answer but either couldn't understand how the answers I found applied, or simply couldn't find an answer.

I have a massive database housed on a DB2 for i server which I'm accessing using SQL through SQLExplorer (based on Squirrel SQL). The tables are very poorly documented and the first order of business is figuring out how to find my way around.

I want to write a simple query that does this:

1) Allows me to search the entire database looking for tables that include a column called "Remarks" (which contains field descriptions).

2) I then want it to search that column for a keyword.

3) I want a table returned that includes the names of the tables that include that keyword (just the name, I can look up the table alphabetically later and look inside if I need to.)

I need this search to be super lightweight, and I'm hoping the concept I describe will achieve that. Anything that eats up a lot of resources will likely anger the sys admin for the server.

Just to show I have tried (and that I am a complete noob), here's what I've got so far.

SELECT *
FROM <dbname>
WHERE Remarks LIKE '<keyword>'

Feel free to mock, I told you I'm an idiot :-).

Any help? Perhaps at least a push in the right direction?

PS - I can't seem to find a search function in SQLExplorer, if someone knows if I can perhaps use a simple search or filter to accomplish this same goal...that would be great.

Solution 1

You can query the system catalog to identify the tables:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
FROM QSYS2.SYSCOLUMNS WHERE UPPER(DBILFL) = 'REMARKS'

And then query each table individually:

SELECT * FROM TABLE_SCHEMA.TABLE_NAME WHERE Remarks LIKE '%<keyword>%'

See the LIKE predicate for details of the pattern expression.

Solution 2

Normally i use something like this

SELECT TABLE_SCHEMA, TABLE_NAME
       ,COLUMN_NAME,SYSTEM_COLUMN_NAME,COLUMN_HEADING          
       ,DATA_TYPE, "LENGTH",NUMERIC_SCALE
FROM QSYS2.SYSCOLUMNS
WHERE UPPER(COLUMN_NAME) LIKE '%REMARK%' 

@JamesA, i'm at V6R1, by default, normal user are not authorized to object QADBIFLD in QSYS

Solution 3

Generally, many if not most IBM i shops (especially those that use RPG) stick to 10 (or less) character schema names & table names, and have a 10 (or less) character names for 'system' column names, even if longer column names are also provided. Column text generally describes each field.

SELECT SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME
      ,SYSTEM_COLUMN_NAME,         
      ,DATA_TYPE, "LENGTH",NUMERIC_SCALE
      ,CHAR(COLUMN_TEXT) 
FROM QSYS2.SYSCOLUMNS
WHERE UPPER(COLUMN_NAME) LIKE '%REMARK%'