CREATE ALIAS DBLIB/AM1 FOR DBLIB/FILE(M1)
Can M1 be a variable?
I have file with hundreds of members, their names are based on dates.
Editor's note: We received a previous question concerning this topic: selecting a PF with multiple members from 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.
The CREATE ALIAS statement does not support variables for the member name. However, you can use dynamic SQL to use variables at runtime to build the desired CREATE ALIAS statement. Here's an example using an SQL stored procedure. The PREPARE and EXECUTE statements could also be embedded in a high-level language like RPG.
CREATE PROCEDURE alias_test(in mname char(10)) language sql begin declare stmt_text varchar(256); set stmt_text = 'CREATE ALIAS DBLIB/AM1 FOR DBLIB/FILE1(' || mname || ')'; prepare s1 from stmt_text; execute s1; END; CALL alias_test('MBR112008');
Related Q&A from Kent Milligan
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ...continue reading
Create a host variable of the where in statement on the fly with dynamic SQL.continue reading
To monitor members stuck within a physical file on AS/400, you can periodically use the display file description (DSPFD) command to create an output ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.