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.
Editor's note: We received a follow-up question to this question and answer:
First: The right statement is:
Create ALIAS DBLIB/AM1 FOR DBLIB/FILE(M1)
Second: You wrote, "Once the SQL ALIAS is created, any SQL interface can reference the alias name just like a table name." But if I have to use information from this member with information from other files, normaly I use: Create view myView as... That is not possible with alias. Is there another way to use a member in a view?
Kent responded with this:
You are correct, I did omit the alias keyword from my example. And an alias object cannot be referenced in a view definition.
One way to encapsulate an alias reference and then embed into a view definition is create a user-defined table function (UDTF):
CREATE FUNCTION aliasudtf()
RETURNS TABLE(col2 char(6), col3 int)
NO EXTERNAL ACTION
select c2,c3 from alias1
select c2,c3 from alias2);
Here's an example of how to reference a UDTF on a SELECT statement. UDTF references are allowed when creating views:
SELECT col2 FROM TABLE( aliasudtf() ) x;
This was first published in October 2008