IBMi (AS400) fans only ' How to access multimember physical file using SQL alias

#IBMiSample

Each IBMi user knows they run the most efficient system. All others just have too much money.

SQL does not have direct access to multiple-member physical files on IBM i. However, OS/400 provides a solution through the SQL `ALIAS` statement. By creating an alias for the specific member you want to access, you can work with that member using SQL.

Example Using the IBM i PUB400 Public Server

Let's walk through an example using the IBM i PUB400 public server. In this scenario, we have a physical file `QDDSLF` located in the library `VDOTEST1`, which contains multiple members:

In library VDOTEST1, file QDDSLF I have 8 members.

CLANA01L

CLANA02L

CLANA03L

CLANA04L

CLANA05L

CLANA06L

CNCON01L

OTORD01L

Suppose you want to access the member `CLANA03L` using SQL. To do this, follow these steps:

1. Create an Alias for the Member: 

   

   Open an SQL session using `STRSQL` and create an alias for the specific member:

   CREATE ALIAS A_CLANA03L FOR VDOTEST1.QDDSLF(CLANA03L);

  

 

This alias acts as a persistent object, allowing you to reference the specific member within SQL statements.

2. Access the Member via SQL:

   Once the alias is created, you can directly access the member with SQL:

   

   SELECT * FROM A_CLANA03L;

   

   This query allows you to work with the data in `CLANA03L` as if it were a standard table.

3. Remove the Alias:

   Remember, once you are done, it's a good practice to remove the alias to keep the system clean:

   

   DROP ALIAS VDOTEST1.A_CLANA03L;

   

   This command deletes the alias object.

Alternative Method Using QSYS2.IFS_READ

Thanks to the suggestion of my friend Don Anderson, there’s another way to access file members using `QSYS2.IFS_READ`. Here’s how you can use this method:

SELECT CAST(LINE AS CHAR(120))

FROM TABLE (

    QSYS2.IFS_READ(

        path_name => '/qsys.lib/vdotest1.lib/qddslf.file/clana03l.mbr'

    )

);

This approach reads the specified member using the Integrated File System (IFS) path. It's a versatile way to access the data directly, especially when dealing with large or complex files.

Summary

By using an alias or the `QSYS2.IFS_READ` function, you can effectively access and manipulate multiple-member physical files with SQL on IBM i. This method provides flexibility in handling specific data within these files.

Feel free to share your thoughts or ask questions in the comments below. Your feedback is always appreciated!

Thanks to the page https://www.ibm.com/support/pages/accessing-files-multiple-members-using-sql for providing helpful insights.