SQL on AS400 will automatically use * FIRST member in a file. So can we access the other members? The answer is NO. There are ways to access other members. This can be resolved by creating SQL ALIAS. By creating SQL Alias we can create a reference file of type DDMF (Distribute Data Management File) which is assigned to the member of a file.
Create Alias:
CREATE ALIAS <lib_name>/<alias_name> FOR <src_lib>/<src_file>(<src_member>)
Contoh: CREATE ALIAS SOOLDAT/MYALIAS FOR SOOLDAT/TESTFILE (TESTFILE)
Create alias MYALIAS in the SOOLDAT library with a member reference TESTFILE from the TESTFILE file in the SOOLDAT library. Try using the Select iteration:
SELECT * FROM SOOLDAT/MYALIAS
Note: DDMF file are actually just pointers to other file. All activities related to data changes will have an impact on the main file.
Delete Alias:
DROP ALIAS <lib_name>/<alias_name>
Contoh: DROP ALIAS SOOLDAT/MYALIAS