Wednesday, January 5, 2022

Retrieving the file and library name from a SQL alias

get name of file and library alias was built over

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:

 

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

4 comments:

  1. Simon, “multiple members file “ you keep teaching and we keep learning, multiple members file. A blast from the past. Great examples and read Thanks for sharing

    “access data from a file that contains multiple members”

    This push me back to the 90’s
    I Have not seen multiple members tables since, I really don’t remember or know??????

    ReplyDelete
    Replies
    1. I admit I do use them, but only for archive/history data.

      For example: I wrote a program to send data via SFTP to ADP for payroll. I kept a copy of each file I sent in a member in a multi member "history" file.

      That way if we needed to resend or check what data I could look at the member, which were all named as @YYMMDDSSS.

      There was another program that run daily to delete any member that was older than 6 months.

      Delete
  2. Looking at the scalar function definition, I think it's worth noting that the returned value is VarChar(128), not Char(10). An interesting and more complex example could result if the file were defined with both an SQL & System name, as you'd then have to use the table name to get the system table name from the catalog.

    ReplyDelete
    Replies
    1. I can get the long SQL names by using a statement like this
      (There is no code editor so it is going to look bad)

      SELECT TABLE_SCHEMA('ALIAS_1','QTEMP') AS "Alias lib",
      TABLE_NAME('ALIAS_1','QTEMP') AS "Alias name",
      TABLE_SCHEMA AS "Schema",
      TABLE_NAME AS "Table"
      FROM QSYS2.SYSTABLES
      WHERE TABLE_SCHEMA('ALIAS_1','QTEMP') = SYSTEM_TABLE_SCHEMA
      AND TABLE_NAME('ALIAS_1','QTEMP') = SYSTEM_TABLE_NAME ;

      Delete

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.