Ask the Expert

Selecting a PF with multiple members from SQL

How can I select a physical file (PF) with multiple members on AS/400 from Windows SQL?
The simplest technique is to create an alias using SQL for each member that you want to access with SQL. Here's an example of creating aliases.
CREATE DBLIB/AM1 FOR DBLIB/FILE(M1)
CREATE DBLIB/AM2 FOR DBLIB/FILE(M2)

Once the SQL ALIAS is created, any SQL interface can reference the alias name just like a table name.

The SQL alias object requires no maintenance, so creating the aliases should be a one-time operation. There's no overhead leaving the alias objects around.

Editor's note: We received a follow-up question to this question and answer:

First: The right statement is:
Create ALIAS DBLIB/AM1 FOR DBLIB/FILE(M1)

Second: You wrote, "Once the SQL ALIAS is created, any SQL interface can reference the alias name just like a table name." But if I have to use information from this member with information from other files, normaly I use: Create view myView as... That is not possible with alias. Is there another way to use a member in a view?

Kent responded with this:

You are correct, I did omit the alias keyword from my example. And an alias object cannot be referenced in a view definition.

One way to encapsulate an alias reference and then embed into a view definition is create a user-defined table function (UDTF): 

CREATE FUNCTION aliasudtf() 

RETURNS  TABLE(col2 char(6), col3  int) 
LANGUAGE SQL 
NO EXTERNAL ACTION 
DETERMINISTIC 
DISALLOW PARALLEL 
BEGIN 
Return ( 
select c2,c3 from alias1 
 union all 
select c2,c3 from alias2); 
END;

Here's an example of how to reference a UDTF on a SELECT statement. UDTF references are allowed when creating views: 

   SELECT col2 FROM TABLE( aliasudtf() )  x;

This was first published in October 2008

Back to top