Topic
  • 1 reply
  • Latest Post - ‏2016-04-01T20:25:14Z by Junius
ScottForstie
ScottForstie
12 Posts

Pinned topic Q&A - Using an ALIAS within a view

‏2013-03-01T03:29:38Z |
I get a lot of questions and thought I would periodically post the questions and my responses here, for the possible benefit of others.
Scott Forstie

Question:
When we use an alias in a view the view ends up being hard bound to the alias target. When the alias target changes, the view continues to refer to the original alias target. However, if we avoid using the view and simply have a query within our procedure, the query always finds the current alias target, which is the behavior we want. Why do views behave in this manner?

Response:
The simple explanation is that for views and logical files, object references are hard bound at build time. The technical reason for this hard binding at build time is to enable the database to be the most efficient and secure.

Avenues to consider:
1) Anytime the alias target changes, recreate the views. This is probably not an ideal choice, but CREATE OR REPLACE ALIAS and CREATE OR REPLACE VIEW could be used.
2) Reverse the dependency tree from VIEW ---> ALIAS ---> TABLE to ALIAS ---> VIEW ---> TABLE. This would imply creating multiple views and using the alias to seamlessly switch. This will work well, if you can tolerate the additional views.
3) Avoid the view altogether by having a query that references the alias similar to what might have been placed within the view definition. The query will react to any changes in the target of the alias.

The SQL Reference includes the following:

The effect of using an alias in an SQL statement is similar to that of text substitution. The alias, which must be defined before the SQL statement is executed, is replaced at statement preparation time by the qualified base table, partition of a table, view, or database file member name. For example, if PBIRD.SALES is an alias for DSPN014.DIST4_SALES_148, then at statement run time:

SELECT * FROM PBIRD.SALES
effectively becomes
SELECT * FROM DSPN014.DIST4_SALES_148
The effect of dropping an alias and recreating it to refer to another table depends on the statement that references the alias.

> SQL Data or SQL Data Change statements that refer to that alias will be implicitly rebound when they are next run.
> Indexes that reference the alias are not affected.
> Materialized query tables or views that reference the alias are not affected.

Related articles:
http://www.iprodeveloper.com/article/databasesql/sql-create-replace-improve-db2-object-management-699310
http://www.iprodeveloper.com/article/databasesql/improve-your-data-center-with-three-part-name-aliases--66225
  • Junius
    Junius
    21 Posts

    Re: Q&A - Using an ALIAS within a view

    ‏2016-04-01T20:25:14Z  

    Hello Scott,

    We need to create a view that will reference an alias that points to a multi-member file.  This view will be used by a third party product (Sequel Viewpoint) to product the report for the user. Based on your comments, we going to create the view using an RPG program which also creates the alias at the same time.  This process will run twice a year to pick up the newly created member.  When we try to compile the RPG program we get SQL7030 ("alias not allowed").

    Do you know why we are getting that error, since it seems that views can reference aliases (even though it's not the best idea)?   Also, the view is rather lengthy, a bunch of CTEs.

    We are on 7.1 with TR11.

    Best regards,

    Jay