Q

Create an alias to a multimember file 'dynamically'

I would like to create an alias to a multimember file 'dynamically'. I want to have a string passed as member in...

the format of yyyymm (for example 200407). I tried the following:

CREATE ALIAS XXXGL.TEST
FOR LIBA.FILEA
(SELECT SUBSTR(CAST(DATE(DAYS(CURDATE()) -15) AS
CHAR(20)),1,4) || SUBSTR(CAST(DATE(DAYS(CURDATE()) -15) AS
CHAR(20)),6,2) from LIBB.FILEB)
Unfortunately, it doesn?t work. Do you have workarounds?
To dynamically create an alias you need to prepare a CREATE ALIAS statement. Also, a member name cannot start with a numeric character. Here's an example using an SQL procedure that generates an alias dynamically for a generated member name that starts with 'M'

 
CREATE PROCEDURE aliastest() 
LANGUAGE SQL 
BEGIN 
DECLARE stmt1 VARCHAR(256); 
DECLARE mbrname VARCHAR(10); 

SET mbrname = (SELECT SUBSTR(CAST(DATE(DAYS(CURDATE()) -15) AS CHAR(20)),1,4) || 
              SUBSTR(CAST(DATE(DAYS(CURDATE()) -15)  AS CHAR(20)),6,2) from sysibm.sysdummy1); 

SET stmt1 = 'CREATE ALIAS XXXGL.TEST FOR LIBA.FILEA(M'|| TRIM(TRAILING FROM mbrname) ||')'; 

PREPARE s1 FROM stmt1; 
EXECUTE s1; 
END; 

CALL ALIASTEST(); 

==================================
MORE INFORMATION ON THIS TOPIC
==================================

Check out this Search400.com Featured Topic: Database issues resolved

Search400.com's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: Tips, tutorials and more.

This was last published in July 2004

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.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

I need to make a query against a physical file that has 30 members. For this I have chosen to create ALIAS, this means that in addition to create 30 aliases, I do SELECT 30 times too. Is there any way to iterate alias creation through a FOR statement dynamically?
Cancel

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close