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
(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.
Dig deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
To monitor members stuck within a physical file on AS/400, you can periodically use the display file description (DSPFD) command to create an output ...continue reading
Create a host variable of the where in statement on the fly with dynamic SQL.continue reading
IBM did not change the default of the reuse deleted files function to YES for physical files in order to minimize the impact on any existing ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.