how to find the base table of an ALIAS ?

esrraju · New User Joined: 19 Feb 2007 Posts: 9 Location: India

Srihari Gonugunta · Posted: Wed Nov 28, 2007 4:03 pm

Please try this out
SELECT NAME,TBNAME FROM SYSIBM.SYSTABLES WHERE NAME='Alias name' AND TYPE=’A’;

esrraju · New User Joined: 19 Feb 2007 Posts: 9 Location: India

Thanks for your response.

This query will give you the Alias name itself, but I want to know that base table on which this Alias is built.

murmohk1 · Posted: Wed Nov 28, 2007 4:42 pm

Try SYSCAT.TABLES.

esrraju · New User Joined: 19 Feb 2007 Posts: 9 Location: India

When I try this query select * from SYSCAT.TABLES ..It says SYSCAT.TABLES could not be found. Please ADVISE.

Srihari Gonugunta · Posted: Wed Nov 28, 2007 5:05 pm

Murali...I too read somewhere that we can use SYSCAT.TABLES for that. But I tried to do a select on this table and ended up with SQLCODE = -204, ERROR: SYSCAT.TABLES IS AN UNDEFINED NAME.

Raju, Generating the DDL for the alias, will give the Create statement of that alias from which we can check the base table name..I am just trying to check for such a JCL. Will let you know, in case I find it.

enrico-sorichetti · Posted: Wed Nov 28, 2007 5:32 pm

what about looking at " SYSIBM.SYSALIASES"

Srihari Gonugunta · Posted: Wed Nov 28, 2007 6:00 pm

there is no such table, I beleive. I tried it out only to get another -204.