Pages

Wednesday, July 24, 2024

Now possible to build SQL Alias over last member

The SQL Create Alias statement was enhanced in the latest round of Technology Refreshes, IBM i 7.5 TR4 and IBM i 7.4 TR10. Previously when you created a SQL Alias you had to give a specific member in the DDS Physical file. Now I can use *LAST to denote the most recent member in the Physical file, and what is really cool is if I add another member to the file the Alias will now use that member!

This will be clearer when I show my examples.

First I need a DDS physical file. I called this one MULTIMBR, and it is found in my library MYLIB.

 A          R RMULTIMBR
 A            FIELD        100A

I compiled the above DDS Physical file with the following command:

01  CRTPF FILE(MYLIB/MULTIMBR) SRCFILE(MYLIB/DEVSRC) MAXMBRS(*NOMAX)

Notice that the maximum number of members parameter is *NOMAX.

I can check that I have the file set to no maximum members and, at present, it only contains one member by using the SYSFILES View:

01  SELECT SYSTEM_TABLE_SCHEMA AS "Library",
02         SYSTEM_TABLE_NAME AS "File",
03         MAXIMUM_MEMBERS AS "Max members",
04         NUMBER_MEMBERS AS "No. members"
05    FROM QSYS2.SYSFILES
06   WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB'
07     AND SYSTEM_TABLE_NAME = 'MULTIMBR'

There is only one row of results returned:

Library  File      Max members  No. members
-------  --------  -----------  -----------
MYLIB    MULTIMBR  <NULL>                 1

The null in the Maximum members columns means that it is set to *NOMAX. The file only has one member.

I am going to create an Alias over just the first member in the file:

01  CREATE OR REPLACE ALIAS QTEMP.FIRST_MBR 
02                    FOR MYLIB.MULTIMBR (MULTIMBR)

The Alias is created in the library QTEMP with the name FIRST_MBR, line 1. The Alias references the file MULTIMBR and the member of the same name.

I can check that the Alias is built the way I desire by using the following statement:

01  SELECT SYSTEM_TABLE_SCHEMA,SYSTEM_TABLE_NAME,MEMBER_NAME
02    FROM TABLE(SYSPROC.BASE_TABLE('QTEMP','FIRST_MBR'))

I use the BASE_TABLE Table function and it returns the following:

SYSTEM_TABLE_SCHEMA  SYSTEM_TABLE_NAME  MEMBER_NAME
-------------------  -----------------  -----------
MYLIB                MULTIMBR           MULTIMBR

To help me track that this is the first member I wanted to insert a row into the Member saying that it is:

01  INSERT INTO QTEMP.FIRST_MBR VALUES('First member') ;

02  SELECT * FROM QTEMP.FIRST_MBR ;

The result from the second SQL statement is:

FIELD
--------------------------
First member

Next, I create another Alias, this one uses the *LAST in place of the member's name:

01  CREATE OR REPLACE ALIAS QTEMP.LAST_MBR FOR MYLIB.MULTIMBR (*LAST)

I can use the BASE_TABLE Table function to check for the member this Alias uses:

01  SELECT SYSTEM_TABLE_SCHEMA,SYSTEM_TABLE_NAME,MEMBER_NAME
02  FROM TABLE(SYSPROC.BASE_TABLE('MYLIB','LAST_MBR'))

This time I am disappointed as the Member name is null.

SYSTEM_TABLE_SCHEMA  SYSTEM_TABLE_NAME  MEMBER_NAME
-------------------  -----------------  -----------
MYLIB                MULTIMBR           <NULL>

I can check that this Alis is using the first member by using the following SQL statement to retrieve the contents of the member:

01  SELECT * FROM QTEMP.LAST_MBR

The result shows that the Alias is looking in the first member.

FIELD
--------------------------
First member

Now I can add a second member:

01  ADDPFM FILE(MYLIB/MULTIMBR) MBR(SECOND)

I can use SYSFILES to show that there are now two members in the file:

01  SELECT SYSTEM_TABLE_SCHEMA AS "Library",
02         SYSTEM_TABLE_NAME AS "File",
03         NUMBER_MEMBERS AS "No. members"
04    FROM QSYS2.SYSFILES
05   WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB'
06     AND SYSTEM_TABLE_NAME = 'MULTIMBR'

And the result shows that there are now two members:

Library  File      No. members
-------  --------  -----------
MYLIB    MULTIMBR            2

To prove that the Alias now is "pointing" to the second member let me insert some text into it:

01  INSERT INTO QTEMP.LAST_MBR VALUES('Second member') ;

02  SELECT * FROM QTEMP.LAST_MBR ;

The last member contains the following:

FIELD
--------------------------
Second member

I use the FIRST_MBR Alias to make sure that has not been changed:

01  SELECT * FROM QTEMP.FIRST_MBR

And I see it has not.

FIELD
--------------------------
First member

I can double check my results are what I expect using the Display Physical File Member command, DSPPFM:

DSPPFM FILE(MYLIB/MULTIMBR) MBR(*FIRST)

                         Display Physical File Member
File . . . . . . :   MULTIMBR            Library  . . . . :   MYLIB
Member . . . . . :   MULTIMBR            Record . . . . . :   1

*...+....1....+....2....+....3....+....4....+....5....+....6....+..
First member
                          ****** END OF DATA ******

That is what I expect to see in the first member.

And what is in the second member?

DSPPFM FILE(MYLIB/MULTIMBR) MBR(*LAST)

                         Display Physical File Member
File . . . . . . :   MULTIMBR            Library  . . . . :   MYLIB
Member . . . . . :   SECOND              Record . . . . . :   1

*...+....1....+....2....+....3....+....4....+....5....+....6....+..
Second member
                          ****** END OF DATA ******

This is what I expect to be in there.

I think this enhancement to the Alias statement is something I will use the future. It will make my work with archive and history files so much simpler.

 

You can learn more about the changes to Create Alias SQL statement from the IBM website here.

 

This article was written for IBM i 7.5 TR4 and 7.4 TR10.

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.