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.