Ask the Expert

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: