SELECT * FROM INFORMATION SCHEMA on V5R3M0

Question

I have access to a client's V7R1 AS/400 and I can run queries like SELECT NAME FROM INFORMATION_SCEMA.TABLES and

SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    TYPE_NAME, 
    COLUMN_SIZE, 
    COLUMN_TEXT
FROM "SYSIBM"."SQLCOLUMNS"
WHERE
    TABLE_SCHEM = 'MYLIB' 
    AND TABLE_NAME = 'SOMEPF'

Just fine on that. I am QSECOFR¹ on that system.

However, on a free greenscreen account on PUB1.DE running V5R3M0, I cannot run that command from either STRSQL or from the db2 command inside of QSH. I tried the alternative format of INFORMATION_SCHEMA/TABLES I've seen around as well. I get the following errors from STRSQL:

> SELECT name FROM INFORMATION_SCHEMA/TABLES
  TABLES in QSYS2 type *FILE not found.     
> SELECT name FROM INFORMATION_SCHEMA.TABLES
  Qualified object name TABLES not valid.   

Is this a permissions problem, a syntax difference between V5 and V7 or something else?

---

^{1 - Operating System Security Officer user profile}

Answer 1

You are experiencing the difference between "system naming convention" and "SQL naming convention" modes. Originally, system naming mode required a slash character / as the delimiter when qualifying with a schema name, while SQL naming mode used a period . delimiter. An update to v7.1 now allows either delimiter when using system naming. V5R3 is no longer supported, nor updated. On a green screen session you can specify STRSQL NAMING(*SYS). Or specify system naming convention on your JDBC/ODBC connection string settings.

There are other functional differences between the two modes. See here and here. Chief among them is that system naming mode allows use of the library list, as long as you don't specify a CURRENT SCHEMA. But you can specify a current library, with the CHGCURLIB command in CL, or in SQL with CALL QCMDEXC('CHGCURLIB MYLIB',15) (where 15 is the length of this sample command)

DB2 for i catalog views may be found in QSYS2.

SELECT COLNO
      ,SYSTEM_COLUMN_NAME   
      ,DATA_TYPE 
      ,coalesce(PRECISION,LENGTH)        as len
      ,smallint(SCALE)                   as dp
      ,STORAGE                           as bytes
      ,varchar(COLUMN_TEXT,50)           as fldtext  
      ,varchar(substr(LABEL, 1,20),20)   as hdg1
      ,varchar(substr(LABEL,21,20),20)   as hdg2
      ,varchar(substr(LABEL,41,20),20)   as hdg2
      ,CCSID
      ,IS_NULLABLE
FROM QSYS2/SYSCOLUMNS
WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 
  AND SYSTEM_TABLE_NAME   = 'SOMEPF'

Most IBM i shops used 10-character names (operating system rules) and typically did not use 128-character names allowed by DB2, so generally SYSTEM_COLUMN_NAME, SYSTEM_TABLE_NAME, and SYSTEM_SCHEMA_NAME will be sufficient.

The V5R3 SQL Reference is available as a PDF, as Dave Jones mentioned.

Answer 2

I'm no expert on DB2 for iSeries (my only experience with it is helping others online!), so this may be entirely wrong...

I think support for the standard INFORMATION_SCHEMA was only added recently. As in DB2 for LUW (which I'm rather more familiar with), INFORMATION_SCHEMA is probably just a relatively thin set of views over the "real" system catalog which on DB2 for iSeries used to be in the QSYS2 schema (in DB2 for LUW it's in SYSCAT, and in DB2 for z/OS it's in SYSIBM).

It seems the DB2 for iSeries V5Rx docs are no longer online but if it's anything like the DB2 for z/OS catalog you'll find the information you want in QSYS2.SYSTABLES (which you may have to query as QSYS2/SYSTABLES depending on the interface you're using). From my admittedly rusty memory you'll want something like:

SELECT TABLE_OWNER, TABLE_NAME FROM FROM QSYS2.SYSTABLES

Where TABLE_OWNER is the schema, and TABLE_NAME is the name. You should find various other useful views in the catalog like SYSVIEWS, SYSCOLUMNS, SYSTRIGGERS, etc. It may be worth downloading the PDFs of the old DB2 for iSeries docs which still seem to be available. I suspect the info on the system catalog structure will be in the "SQL Reference" one.