Dynamic ALIAS creation in iSeries

25 pts.
Tags:
iSeries SQL
Physical File
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 i have to create 30 aliases.
Is there any way to iterate alias creation through a FOR statement dynamically?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

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

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

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
  • philpl1jb

    What language are you using?

    Do these members always have the same names?

    Give examples.

    50,595 pointsBadges:
    report
  • GianDamico
    Hi,

    I'm trying to create a SQL Stored Procedure. Mi code is as follows:

    create procedure qrypfrdata(in in_lib char(10), 
                                in out_lib char(10))
    
    
    language sql 
    begin
    declare stmt char (1000);
    
    -- ===== Acquiring member names list from selected PF(QAPMSYSCPU) =============
    set stmt = 'CL:DSPFD FILE(' concat in_lib concat '/qapmsyscpu )
                   TYPE( *MBR ) OUTPUT(*OUTFILE) OUTFILE(' concat in_lib 
                   concat '/miembros)';
    prepare s1 from stmt;
    execute s1;
    
    
    -- ====== Preparing cursor statement ==========================================
    set stmt = 'select mbname from ' 
                   concat  in_lib concat 
                  '.miembros fetch first 3 row only'; /* First 3 rows for testing purposes*/
    prepare s2 from stmt;
    
    
    
    FOR v1  AS c1   cursor  for s2  /* Here I'm getting invalid symbol SQL error for s2 */
       DO
    
    	-- ========== Creating Alias for each PF member ==============
    	-- CPU
    	set stmt = 'create alias ' concat in_lib 
                           concat '.cpu for ' concat libreriain 
                           concat '.qapmsyscpu (' concat mbname concat ')';
    	prepare s3 from stmt;
    	execute s3;
    
    	-- JOBL
    	set stmt = 'create alias ' concat in_lib 
                        concat '.jobl for ' concat in_lib 
                        concat '.qapmjobl (' concat mbname concat ')';
    	prepare s4 from stmt;
    	execute s4;
    	
    	
    	-- ========== AS400CPUtilizE ================================================
    
    	 set comando = 'insert into ' concat out_lib 
                           concat '.cputiliz select 
    	               dec(dtetim),                                                   
    	               int(avg(intsec)),                                             
    	               sum(JBTCPU),                                                     
    	               cast(jbtype as CHAR(16) CCSID 37),  
    	               dec( jbprty),                             
    	               (select int(avg(sctnum))                                         
    	               from ' concat in_lib concat '.cpu                                        
    	               where dtetim = dec(A.dtetim)                                     
    	            group by dtetim order by dtetim)                                 
    	            from ' concat in_lib concat '.jobl A                                                                                                                      
    	            group by dtetim, jbtype, jbprty order by dtetim,
                        cast(jbtype as CHAR(16) CCSID 37)';
    	prepare s4 from comando;
    	execute s4;
    
    	-- =========== Removing created Alias ================================================
    	-- CPU
    	set stmt = 'drop alias '
                       concat in_lib concat '.cpu;
            prepare s5;
            execute s5;
    
    	-- JOBL
    	set stmt = 'drop alias '
                       concat in_lib concat '.jobl;
            prepare s6;
            execute s6;
    
    
    END FOR;
    END;             
    
    
    As you can see members do not have the same names allways.

    Thanks a lot.

    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

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

Thanks! We'll email you when relevant content is added and updated.

Following