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 first published in July 2004

Back to top