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.
CREATE PROCEDURE alias_test(in mname char(10))
declare stmt_text varchar(256);
set stmt_text = 'CREATE ALIAS DBLIB/AM1 FOR DBLIB/FILE1(' || mname || ')';
prepare s1 from stmt_text;
This was first published in March 2009