alias tables

[Dave]

I frequently need to create alias names on AS400 to access physical
file members using SQL. Sometimes I forget to drop the alias names.
Is there a way I can find existing alias names on the AS400?

[Karl Hanson]

select system_table_name, system_table_schema,
base_table_schema, base_table_name, base_table_member
from qsys2.systables
where table_type = 'A'

http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/index.jsp?topic=/db2/rbafzcatsystbls.htm&tocNode=toc:rzahg/i5os/4/7/7/14/0/31/

(modify select column list to suite your needs)

--
Karl Hanson

[CRPence]

Since the implementation object for SQL ALIAS is the DDM *FILE, a
list of all DDM files will include them; WRKDDMF would show them,
but also include other DDM files. DSPFD for file attribute of *DDM
directed to an output file might include ALIAS details; I do not
recall. However the direct & quickest way is to use the SQL catalog
table; e.g. SYSTABLES & SYSTABLESDEP in QSYS2. I would generally
query QADBXREF in QSYS, but doing that or even creating a VIEW over
that file with that query requires *ALLOBJ special authority.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafysqlobjects.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstcatalog.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstcatalogtbls.htm

FWiW, I would recommend that if a created ALIAS is not intended
to remain permanently, then OVRDBF should be used instead. The
ALIAS, implemented as a permanent object, is intended for continued
[re]use; i.e. avoiding unnecessary CREATE\DROP activity. Of course
if the ALIAS is never created, then there is also never any reason
to delete\drop, so no more searching for what to delete.

Regards, Chuck

[Hal]

The bigger question is why are you dropping the aliases? In my case,
our student info system used the member as a year indicator, so to get
at data on a per-year basis I need multiple aliases per physical file
to use SQL to query out the data. Leaving them in place won't hurt
anything IMHO. I have a separate library defined for the purpose of
just holding special views and aliases.

To that end, I actually wrote a short java routine a few years ago
that lets me take an alias for 2009 and either update it so the
default alias without a year spec moves to 2010, or create a new 2010
alias, or both.......helps make the new fiscal/school year-opening
task a bit more manageable.

Chris