Implement variables in SQL when creating an alias

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');

This was first published in March 2009

Back to top