Get Column Description from iSeries v7r1 export to Excel or Access

Question

In my mind, getting something this simple should not be as difficult as it appears to be!

The column names are very cryptic, but I can SEE the descriptions of those columns using iSeries Navigator (or is it "Aggravator"?)

Here's a screenshot of the table definition. (Cropped out the connection info to preserve security ) I want the values from the "Text" column, and I'm hoping to use these values from several different iSeries tables in an Access 2010 app. If it were just this one table, I would likely just type them in by hand.

I have been able to get most of information (less the info from this "Text" column as stated above) using the following SQL statement that I found online. (I'm no longer sure where ... I have looked at so many ... and find most of it extremely noob-confusing!)

SELECT COLUMNS.TABLE_CATALOG, COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME, COLUMNS.COLUMN_NAME, COLUMNS.ORDINAL_POSITION, COLUMNS.COLUMN_DEFAULT, COLUMNS.IS_NULLABLE, COLUMNS.DATA_TYPE, COLUMNS.CHARACTER_MAXIMUM_LENGTH, COLUMNS.CHARACTER_OCTET_LENGTH, COLUMNS.NUMERIC_PRECISION, COLUMNS.NUMERIC_PRECISION_RADIX, COLUMNS.NUMERIC_SCALE, COLUMNS.DATETIME_PRECISION, COLUMNS.INTERVAL_TYPE, COLUMNS.INTERVAL_PRECISION, COLUMNS.CHARACTER_SET_CATALOG, COLUMNS.CHARACTER_SET_SCHEMA, COLUMNS.CHARACTER_SET_NAME, COLUMNS.COLLATION_CATALOG, COLUMNS.COLLATION_SCHEMA, COLUMNS.COLLATION_NAME, COLUMNS.DOMAIN_CATALOG, COLUMNS.DOMAIN_SCHEMA, COLUMNS.DOMAIN_NAME, COLUMNS.UDT_CATALOG, COLUMNS.UDT_SCHEMA, COLUMNS.UDT_NAME, COLUMNS.SCOPE_CATALOG, COLUMNS.SCOPE_SCHEMA, COLUMNS.SCOPE_NAME, COLUMNS.MAXIMUM_CARDINALITY, COLUMNS.DTD_IDENTIFIER, COLUMNS.IS_SELF_REFERENCING FROM SYSIBM.COLUMNS COLUMNS WHERE (COLUMNS.TABLE_NAME='IVITMMS')

Can somebody help me out here, please? TIA, Don

Answer 1

It was a 2-part solution:

1. the PassThru Query
   SELECT substr(COLUMN_NAME,1,18) AS ColumnName , substr(TABLE_NAME,1,18) AS TableName, substr(TABLE_SCHEMA,1,18) AS Schema, COLUMN_TEXT FROM qsys2.syscolumns WHERE TABLE_SCHEMA = 'BLMYPRDDTA'

2. build a SELECT QUERY using the In() operator to filter the list of tables and their columns
   SELECT qryPassThroughSYSCOLUMNS.*, qryPassThroughSYSCOLUMNS.TABLENAME FROM qryPassThroughSYSCOLUMNS WHERE (((qryPassThroughSYSCOLUMNS.TABLENAME) In ("COMASTR","COWOREF")));