A better way to find which file an Alias was built for

Earlier this year I described a way I could retrieve the schema/library and table/file that a SQL Alias had been built over. over the last few months I have discovered several other ways that will allow me to easily get to that information, and even the member too.

I tend to use SQL Alias for coping with SQL's inability to easily handle multi member files. I could use the Override Database command, OVRDBF, too but I prefer the ease of using the Alias as I can create, use, and then delete the alias all within the same program.

Before I start showing examples, I am going to need to create an Alias, ALIAS_3. Here I am going to create an Alias that will based on the source file DEVSRC, in my library MYLIB, and the source member TESTRPG:

CREATE OR REPLACE ALIAS MYLIB.ALIAS_3 FOR MYLIB.DEVSRC (TESTRPG)

I always like to use the CREATE OR REPLACE to make sure if there is an Alias called ALIAS_3 in my library the existing Alias will be replaced. Notice that the format for the library, file, and member is:

<library>.<file> (<member>)

Using the method I described in my previous post I can retrieve the based on library and file name with the following statement:

VALUES TABLE_SCHEMA('ALIAS_3','MYLIB'),TABLE_NAME('ALIAS_3','MYLIB')

I have used the TABLE_SCHEMA and TABLE_NAME scalar functions here. I have passed to them both two parameters:

1. Name of the Alias
2. Name of the library the Alias is in

The results are:

00001 ------- MYLIB DEVSRC

This gives me some of the information I desire, but not the file member the Alias is based upon.

There is a Table function that gives me this information, BASE_TABLE, that resides in the SYSPROC library. I need to pass it the following parameters:

1. Library, or schema, name
2. Alias name

For the Alias I created earlier my SQL statement would be:

SELECT * FROM TABLE(SYSPROC.BASE_TABLE('MYLIB','ALIAS_3'))

The Table function just returns one row of results:

SYSTEM_ SYSTEM_ BASESCHEMA BASENAME TABLE_SCHEMA TABLE_NAME MEMBER_NAME RDBNAME ---------- -------- ------------ ---------- ----------- ------- MYLIB DEVSRC MYLIB DEVSRC TESTRPG <NULL>

The returned columns are:

• BASESCHEMA:  SQL (long) schema name
• BASENAME:  SQL (long) Table name
• SYSTEM_TABLE_SCHEMA:  System (short) library name
• SYSTEM_TABLE_NAME:  System (short) file name
• MEMBER_NAME:  Member name
• RDBNAME:  If the Alias connected to a remote partition (database) the remote partitions name would be here. If it is a local Alias, then this column will be null

This Table function is ideal if I needed to find the results for one Alias. If there were many Alias I can go to another place to get my results, the View SYSTABLES.

The statement I would use s:

01 SELECT TABLE_SCHEMA,TABLE_NAME,SYSTEM_TABLE_SCHEMA,SYSTEM_TABLE_NAME, 02 BASE_TABLE_SCHEMA,BASE_TABLE_NAME,BASE_TABLE_MEMBER 03 FROM QSYS2.SYSTABLES 04 WHERE TABLE_TYPE = 'A' 05 AND SYSTEM_TABLE_SCHEMA = 'MYLIB'

Lines 1 and 2: I think the names of the columns explain their contents.

Line 4: This is the critical line. Alias will show "A" in the Table Type column, therefore, by selecting on "A" I will get a list of all the Alias in my library.

The results are:

SYSTEM_ SYSTEM_ BASE_ BASE_ BASE_ TABLE_ TABLE_ TABLE_ TABLE_ TABLE_ TABLE_ TABLE_ SCHEMA NAME SCHEMA NAME SCHEMA NAME MEMBER ------ ------- ------- ------- ------- -------- -------- MYLIB ALIAS_1 MYLIB ALIAS_1 MYLIB DEVSRC TESTFILE MYLIB ALIAS_2 MYLIB ALIAS_2 MYLIB TESTFILE SECOND MYLIB ALIAS_3 MYLIB ALIAS_3 MYLIB DEVSRC TESTRPG

My results show that there are three Alias in my library, and which files and members they are based upon.

 

You can learn more about the BASE_TABLE Table function from the IBM website here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.