Q

Implement variables in SQL when creating an alias

Use dynamic SQL to implement variables at runtime in order to build the CREATE ALIAS statement, or embed the PREPARE and EXECUTE statements in a high-level language.

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

Dig deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close