Wednesday, August 10, 2022

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

find alias llibrary file member

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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.