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 first 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.

1 comment

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