RE: DB2 SQL, SYSCAT, SYSIBM, etc

["Metz, Zak" <Zak_Metz@xxxxxx>]

The equivalent would be:
 
Select TABLE_NAME
From SYSIBM.SQLTABLES
Where TABLE_SCEM=?
and ?=? (I'm not sure which "type" field is relevant)
Order By TABLE_NAME
 
If your collection was created using the SQL "create collection" statement, it 
will have (amongst others) a SYSTABLES table. The equivalent query there would 
be:
 
Select TABLE_NAME
From collection.SYSTABLES
Where TABLE_SCHEMA=?
and ?=? (Might it be "FILE_TYPE"?)
Order By TABLE_NAME
 
You can always go straight to the based-on physical for this logical, 
QSYS.QADBXREF, but the field names all change again.
 
As far as why they table and column names are different from what your 
expecting, I know not, but it kinda looks like someone may have assigned 
aliases to shorten the SQL standard column names.
 
Regards,
Zak Metz

        -----Original Message----- 
        From: Urbanek, Marty [mailto:Marty_Urbanek@xxxxxxxxxxxx] 
        Sent: Thu 1/8/2004 1:33 PM 
        To: 'midrange-l@xxxxxxxxxxxx' 
        Cc: 
        Subject: DB2 SQL, SYSCAT, SYSIBM, etc
        
        

        We have some SQL that runs a query via JDBC such as:
        
        SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = ? AND TYPE = ? 
ORDER BY
        TABNAME
        
        The ? marks are replaced before running it.
        
        This works on other DB2s but does not appear to work on iSeries V5R2.
        
        After searching the midrange-l archives, V5R2 info center, and 
newsgroups, I
        am still confused. I see some things that imply that SYSCAT.TABLES 
should
        work on iSeries, but others that imply that (at least) syntax changes 
will
        be necessary, such as:
        
        SYSCAT -> SYSIBM
        TABNAME -> TABLE_NAME
        TABSCHEMA -> TABLE_SCHEMA
        
        Am I understanding this correctly? Is there an easier way? If we change 
the
        query syntax for iSeries, will there still be other subtle differences 
that
        will cause problems?
        
        THANKS in advance!
        -Marty
        _______________________________________________
        This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
        To post a message email: MIDRANGE-L@xxxxxxxxxxxx
        To subscribe, unsubscribe, or change list options,
        visit: http://lists.midrange.com/mailman/listinfo/midrange-l
        or email: MIDRANGE-L-request@xxxxxxxxxxxx
        Before posting, please take a moment to review the archives
        at http://archive.midrange.com/midrange-l. 
  
NOTICE: This E-mail may contain confidential information. If you are not 
the addressee or the intended recipient please do not read this E-mail 
and please immediately delete this e-mail message and any attachments 
from your workstation or network mail system. If you are the addressee 
or the intended recipient and you save or print a copy of this E-mail, 
please place it in an appropriate file, depending on whether 
confidential information is contained in the message.