Retrieving the file and library name from a SQL alias

I use SQL aliases a lot of the time to access data from a file that contains multiple members. When an alias has an undescriptive name like ALIAS1 which file was it built over?

Fortunately there are two scalar functions that allow me to retrieve that information from the alias:

• TABLE_NAME:  Name of the file that the alias is built over
• TABLE_SCHEMA:  The library in which file that the alias is built over is found

Before I get to use those scalar functions I need a file with multiple members and aliases built over those members.

Let me start with the file. You will not be surprised to find that it is called TESTFILE.

01 A R TESTFILER 02 A FLD1 100A VARLEN

I use the following command, CRTPF, to create my physical file.

01 CRTPF FILE(MYLIB/TESTFILE) 02 SRCFILE(MYLIB/DEVSRC) 03 SRCMBR(TESTFILE) 04 MAXMBRS(*NOMAX) 05 SIZE(*NOMAX)

Line 4: Notice that I have allowed for unlimited number of members.

I don't want to use the default member that was created when compiling the file. I am going to remove it:

RMVM FILE(TESTFILE) MBR(*ALL)

Then I am going to add new members to the file:

ADDPFM FILE(TESTFILE) MBR(FIRST) TEXT('First member') ADDPFM FILE(TESTFILE) MBR(SECOND) TEXT('Second member')

Why did I do this? Because I wanted my members to be called FIRST and SECOND.

I can check for the members by using the PDM command WRKMBRPDM:

WRKMBRPDM TESTFILE

Which gives me:

Work with Members Using PDM File . . . . . . TESTFILE Library . . . . MYLIB Opt Member Date Text FIRST DD/DD/DD First member SECOND DD/DD/DD Second member

Or I can get to the same information using the SQL view SYSPARTITIONSTAT:

01 SELECT SYSTEM_TABLE_SCHEMA AS "Library", 02 SYSTEM_TABLE_NAME AS "File", 03 SYSTEM_TABLE_MEMBER AS "Member" 04 FROM QSYS2.SYSPARTITIONSTAT 05 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 06 AND SYSTEM_TABLE_NAME = 'TESTFILE'

The results from this statement are:

Library File Member ------- -------- ------ MYLIB TESTFILE FIRST MYLIB TESTFILE SECOND

As I have a file with multiple members I can now build my aliases:

CREATE OR REPLACE ALIAS MYLIB.ALIAS_1 FOR MYLIB.TESTFILE(FIRST) ; CREATE OR REPLACE ALIAS MYLIB.ALIAS_2 FOR MYLIB.TESTFILE(SECOND) ;

I have deliberately given these vague names, ALIAS_1 and ALIAS_2. If I was doing this for real I would have used more descriptive names.

If I use the PDM command to work with objects, WRKOBJPDM…

WRKOBJPDM MYLIB ALIAS*

I can see that the SQL aliases were created as DDM files.

Work with Objects Using PDM Library . . . . . MYLIB Opt Object Type Attribute Text 5 ALIAS_1 *FILE DDMF ALIAS_2 *FILE DDMF

If I put a "5" next to one of the objects I can see the details for the object. This includes the library and file names that the alias was built over, and that the file is local.

Display Details of DDM File Local file: File . . . . . . . . . . . . . . . . : ALIAS_1 Library . . . . . . . . . . . . . : MYLIB Remote file . . . . . . . . . . . . : MYLIB/TESTFILE Remote location: Name or address . . . . . . . . . : *RDB Relational Data Base . . . . . . : *LOCAL

I can get to the same information in one "step" by using the two scalar functions I mentioned above. The two functions have the same parameters passed to them:

1. File name, required
2. Library name, optional

If I was using ACS's Run SQL Scripts I would simply use the following statement:

VALUES (TABLE_SCHEMA('ALIAS_1','MYLIB'),TABLE_NAME('ALIAS_1','MYLIB')) ;

Which gives me:

00001 00002 ------- ------- MYLIB TESTFILE

I could make the result a bit fancier with the result being: library_name/file_name.

VALUES RTRIM(TABLE_SCHEMA('ALIAS_1','MYLIB')) || '/' || TABLE_NAME('ALIAS_1','MYLIB') ;

Db2 for i interprets the double pipes ( || ) as the same as CONCATENATE. My result is:

00001 -------------- MYLIB/TESTFILE

If I wanted to use a Select statement to get that information I can just do:

01 SELECT TABLE_SCHEMA('ALIAS_1','MYLIB') AS "Library", 02 TABLE_NAME('ALIAS_1','MYLIB') AS "File" 03 FROM SYSIBM.SYSDUMMY1

Which gives me:

Library File ------- -------- MYLIB TESTFILE

If I needed to do this in RPG I would do the following:

01 **free 02 dcl-s Library char(10) ; 03 dcl-s File char(10) ; 04 dcl-s Alias char(10) inz('ALIAS_1') ; 05 exec sql SET :File = TABLE_NAME('ALIAS_1') ; 06 exec sql SET :Library = TABLE_SCHEMA(:Alias) ; 07 dsply ('File=' + File + ' Library=' + Library) ; 08 *inlr = *on ;

Line 1: In 2022 you should only be writing totally free RPG.

Lies 2 – 4: Here are the defintions for the variables I will be using in this program. Note that on line 4 I have initialized Alias with the name of the alias ALIAS_1.

Line 5: I am using the SQL Set statement to receive the file name from the TABLE_NAME function. I have only used the first parameter so it will retrieve the details for ALIAS_1 from the library list.

Line 6: Using Set again to retrieve the name of the library. This time I have used a variable name for the alias name.

Line 7: I am using the display operation code to display the contents of the variables File and Library.

After compiling this program I call it and the following is displayed:

DSPLY File=TESTFILE Library=MYLIB

 

You can learn more about this from the IBM website:

• TABLE_NAME
• TABLE_SCHEMA

 

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