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 has no direct access to IBM i multiple-member physical file but OS/400 support an SQL alias statement.
Therefore, I need to create an alias for the member I want to access with SQL.
I give an example using the IBM i PUB400 public server.
In library VDOTEST1, file QDDSLF I have 8 members.
CLANA01L
CLANA02L
CLANA03L
CLANA04L
CLANA05L
CLANA06L
CNCON01L
OTORD01L
I want to access member CLANA03L via SQL.
I type STRSQL (Enter) then I create the ALIAS:
create alias A_CLANA03L for VDOTEST1.QDDSLF(CLANA03L)
The alias is a persistent object. The ALIAS object has attribute DDMF.
Now I can access the member with SQL:
Don't forget to remove the ALIAS with a "DROP" command:
DROP ALIAS myLibrary/myAlias
That’s it.
Thanks to the suggestion of my friend Don Anderson I add this way:
SELECT CAST(LINE AS CHAR(120))
FROM TABLE ( qsys2.ifs_read(path_name =>
'/qsys.lib/vdotest1.lib/qddslf.file/clana03l.mbr'))
Thanks to the page https://www.ibm.com/support/pages/accessing-files-multiple-members-using-sql .
I wish I could learn like this every day.
ReplyDeleteThanks for the recognition Aldo! Cheers :) Don
ReplyDeleteGood content.
ReplyDelete