This is odd. I created this SP to automate loading spooled files to send to another system. Yes, I know there are other ways, but this should have worked.  It does work when I call the SP from Run Scripts--loading all the spooled file copies to QSPOOLSQL. When I try from the STRSQL command line or RUNSQL from the main command line, the procedure drops the old copy of QSPOOLSQL and creates a new one.  It does not load the spooled files to the members as it does using Run Scripts. Thoughts? 

ACS: CALL MYLIB.LOAD_SPOOLED_FILES ('MYOUTQ','USERDATA','MYPRINTF','06')

Command line: RUNSQL SQL('CALL MYLIB.LOAD_SPOOLED_FILES (''MYOUTQ'',''USERDATA'', ''MYPRINTF'',''06'')') COMMIT(*NC)

create or replace procedure Load_Spooled_Files(    
        IN in_Out_Queue char(10) DEFAULT ' ',      
        IN in_User_Data CHAR(10) DEFAULT ' ', 
        in in_Spool_File CHAR(10) DEFAULT ' ', 
        IN in_Months char(02) DEFAULT '12'
)                                         

language SQL                                               
result sets 1                                              
SPECIFIC Load_Spooled_Files
set option DBGVIEW = *SOURCE                               
                                                           
-- Procedure Logic ----------------------------------------
begin              

declare wk_Out_Queue char(10);           
declare wk_User_Data char(10);   
declare wk_Spool_File char(10);   
declare wk_Months decimal(02,0); 

declare SpooledFileList cursor with return for        
select user_data,spooled_file_name, file_number,'M' ||RANK() OVER(ORDER BY create_timestamp DESC) AS "Rank",
QSYS2.QCMDEXC('CPYSPLF FILE(' || trim(spooled_file_name) ||') TOFILE(MYLIB/QSPOOLSQL) JOB(' || Trim(job_name) ||') SPLNBR('|| trim(file_number) ||') TOMBR('|| 'M' ||RANK() OVER(ORDER BY create_timestamp DESC)||')  CTLCHAR(*FCFC)  ') Pass
  From QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC OQ  
 where (OUTPUT_QUEUE_NAME, user_data,spooled_file_name) = (wk_Out_Queue,wk_User_Data,wk_Spool_File) 
 and date(Create_Timestamp) >= current date - wk_Months Months
 order by create_timestamp desc  
  ; 

SET wk_Out_Queue = upper(Trim(in_Out_Queue));
SET wk_Months = CAST(Trim(in_Months) AS decimal(2,0)); 
SET wk_User_Data = Trim(in_User_Data); 
SET wk_Spool_File = Trim(in_Spool_File); 

DROP TABLE IF EXISTS MYLIB.QSPOOLSQL; 
call QSYS2.QCMDEXC('CRTPF FILE(MYLIB/QSPOOLSQL) RCDLEN(198) TEXT(''Copied Spooled File for '||trim(wk_User_Data) || ' '') MAXMBRS(100) SIZE(*NOMAX)'); 

 -- Run the query           
 open SpooledFileList;      
                            
end                                     

have you tried putting it into debug to see where it fails ?  There are differences in how SQL is handled/processed when running via  STRSQL/RUNSQL  vs ACS RUN Scripts. One 'annoying' difference is that an SQL statement with a semicolon won't run via RUNSQL but runs fine elsewhere. This is a very minor difference but I wonder if your proc isn't running for a similar reason. Try putting it into DEBUG first and step thru and see where it fails. 

This is odd. I created this SP to automate loading spooled files to send to another system. Yes, I know there are other ways, but this should have worked.  It does work when I call the SP from Run Scripts--loading all the spooled file copies to QSPOOLSQL. When I try from the STRSQL command line or RUNSQL from the main command line, the procedure drops the old copy of QSPOOLSQL and creates a new one.  It does not load the spooled files to the members as it does using Run Scripts. Thoughts? 

ACS: CALL MYLIB.LOAD_SPOOLED_FILES ('MYOUTQ','USERDATA','MYPRINTF','06')

Command line: RUNSQL SQL('CALL MYLIB.LOAD_SPOOLED_FILES (''MYOUTQ'',''USERDATA'', ''MYPRINTF'',''06'')') COMMIT(*NC)

create or replace procedure Load_Spooled_Files(    
        IN in_Out_Queue char(10) DEFAULT ' ',      
        IN in_User_Data CHAR(10) DEFAULT ' ', 
        in in_Spool_File CHAR(10) DEFAULT ' ', 
        IN in_Months char(02) DEFAULT '12'
)                                         

language SQL                                               
result sets 1                                              
SPECIFIC Load_Spooled_Files
set option DBGVIEW = *SOURCE                               
                                                           
-- Procedure Logic ----------------------------------------
begin              

declare wk_Out_Queue char(10);           
declare wk_User_Data char(10);   
declare wk_Spool_File char(10);   
declare wk_Months decimal(02,0); 

declare SpooledFileList cursor with return for        
select user_data,spooled_file_name, file_number,'M' ||RANK() OVER(ORDER BY create_timestamp DESC) AS "Rank",
QSYS2.QCMDEXC('CPYSPLF FILE(' || trim(spooled_file_name) ||') TOFILE(MYLIB/QSPOOLSQL) JOB(' || Trim(job_name) ||') SPLNBR('|| trim(file_number) ||') TOMBR('|| 'M' ||RANK() OVER(ORDER BY create_timestamp DESC)||')  CTLCHAR(*FCFC)  ') Pass
  From QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC OQ  
 where (OUTPUT_QUEUE_NAME, user_data,spooled_file_name) = (wk_Out_Queue,wk_User_Data,wk_Spool_File) 
 and date(Create_Timestamp) >= current date - wk_Months Months
 order by create_timestamp desc  
  ; 

SET wk_Out_Queue = upper(Trim(in_Out_Queue));
SET wk_Months = CAST(Trim(in_Months) AS decimal(2,0)); 
SET wk_User_Data = Trim(in_User_Data); 
SET wk_Spool_File = Trim(in_Spool_File); 

DROP TABLE IF EXISTS MYLIB.QSPOOLSQL; 
call QSYS2.QCMDEXC('CRTPF FILE(MYLIB/QSPOOLSQL) RCDLEN(198) TEXT(''Copied Spooled File for '||trim(wk_User_Data) || ' '') MAXMBRS(100) SIZE(*NOMAX)'); 

 -- Run the query           
 open SpooledFileList;      
                            
end                                     

@Rich Malloy, yes, I put it in DEBUG.  It does not "fail" as such. It simply does not run the QCMDEXEC part of the process.  Could be a limit in the SQL allowed from CLLE is bleeding over.  I will tear it down and create an RPGLE to do the work if the other solution does not work. 

have you tried putting it into debug to see where it fails ?  There are differences in how SQL is handled/processed when running via  STRSQL/RUNSQL  vs ACS RUN Scripts. One 'annoying' difference is that an SQL statement with a semicolon won't run via RUNSQL but runs fine elsewhere. This is a very minor difference but I wonder if your proc isn't running for a similar reason. Try putting it into DEBUG first and step thru and see where it fails. 

This is odd. I created this SP to automate loading spooled files to send to another system. Yes, I know there are other ways, but this should have worked.  It does work when I call the SP from Run Scripts--loading all the spooled file copies to QSPOOLSQL. When I try from the STRSQL command line or RUNSQL from the main command line, the procedure drops the old copy of QSPOOLSQL and creates a new one.  It does not load the spooled files to the members as it does using Run Scripts. Thoughts? 

ACS: CALL MYLIB.LOAD_SPOOLED_FILES ('MYOUTQ','USERDATA','MYPRINTF','06')

Command line: RUNSQL SQL('CALL MYLIB.LOAD_SPOOLED_FILES (''MYOUTQ'',''USERDATA'', ''MYPRINTF'',''06'')') COMMIT(*NC)

create or replace procedure Load_Spooled_Files(    
        IN in_Out_Queue char(10) DEFAULT ' ',      
        IN in_User_Data CHAR(10) DEFAULT ' ', 
        in in_Spool_File CHAR(10) DEFAULT ' ', 
        IN in_Months char(02) DEFAULT '12'
)                                         

language SQL                                               
result sets 1                                              
SPECIFIC Load_Spooled_Files
set option DBGVIEW = *SOURCE                               
                                                           
-- Procedure Logic ----------------------------------------
begin              

declare wk_Out_Queue char(10);           
declare wk_User_Data char(10);   
declare wk_Spool_File char(10);   
declare wk_Months decimal(02,0); 

declare SpooledFileList cursor with return for        
select user_data,spooled_file_name, file_number,'M' ||RANK() OVER(ORDER BY create_timestamp DESC) AS "Rank",
QSYS2.QCMDEXC('CPYSPLF FILE(' || trim(spooled_file_name) ||') TOFILE(MYLIB/QSPOOLSQL) JOB(' || Trim(job_name) ||') SPLNBR('|| trim(file_number) ||') TOMBR('|| 'M' ||RANK() OVER(ORDER BY create_timestamp DESC)||')  CTLCHAR(*FCFC)  ') Pass
  From QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC OQ  
 where (OUTPUT_QUEUE_NAME, user_data,spooled_file_name) = (wk_Out_Queue,wk_User_Data,wk_Spool_File) 
 and date(Create_Timestamp) >= current date - wk_Months Months
 order by create_timestamp desc  
  ; 

SET wk_Out_Queue = upper(Trim(in_Out_Queue));
SET wk_Months = CAST(Trim(in_Months) AS decimal(2,0)); 
SET wk_User_Data = Trim(in_User_Data); 
SET wk_Spool_File = Trim(in_Spool_File); 

DROP TABLE IF EXISTS MYLIB.QSPOOLSQL; 
call QSYS2.QCMDEXC('CRTPF FILE(MYLIB/QSPOOLSQL) RCDLEN(198) TEXT(''Copied Spooled File for '||trim(wk_User_Data) || ' '') MAXMBRS(100) SIZE(*NOMAX)'); 

 -- Run the query           
 open SpooledFileList;      
                            
end                                     

This is odd. I created this SP to automate loading spooled files to send to another system. Yes, I know there are other ways, but this should have worked.  It does work when I call the SP from Run Scripts--loading all the spooled file copies to QSPOOLSQL. When I try from the STRSQL command line or RUNSQL from the main command line, the procedure drops the old copy of QSPOOLSQL and creates a new one.  It does not load the spooled files to the members as it does using Run Scripts. Thoughts? 

ACS: CALL MYLIB.LOAD_SPOOLED_FILES ('MYOUTQ','USERDATA','MYPRINTF','06')

Command line: RUNSQL SQL('CALL MYLIB.LOAD_SPOOLED_FILES (''MYOUTQ'',''USERDATA'', ''MYPRINTF'',''06'')') COMMIT(*NC)

create or replace procedure Load_Spooled_Files(    
        IN in_Out_Queue char(10) DEFAULT ' ',      
        IN in_User_Data CHAR(10) DEFAULT ' ', 
        in in_Spool_File CHAR(10) DEFAULT ' ', 
        IN in_Months char(02) DEFAULT '12'
)                                         

language SQL                                               
result sets 1                                              
SPECIFIC Load_Spooled_Files
set option DBGVIEW = *SOURCE                               
                                                           
-- Procedure Logic ----------------------------------------
begin              

declare wk_Out_Queue char(10);           
declare wk_User_Data char(10);   
declare wk_Spool_File char(10);   
declare wk_Months decimal(02,0); 

declare SpooledFileList cursor with return for        
select user_data,spooled_file_name, file_number,'M' ||RANK() OVER(ORDER BY create_timestamp DESC) AS "Rank",
QSYS2.QCMDEXC('CPYSPLF FILE(' || trim(spooled_file_name) ||') TOFILE(MYLIB/QSPOOLSQL) JOB(' || Trim(job_name) ||') SPLNBR('|| trim(file_number) ||') TOMBR('|| 'M' ||RANK() OVER(ORDER BY create_timestamp DESC)||')  CTLCHAR(*FCFC)  ') Pass
  From QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC OQ  
 where (OUTPUT_QUEUE_NAME, user_data,spooled_file_name) = (wk_Out_Queue,wk_User_Data,wk_Spool_File) 
 and date(Create_Timestamp) >= current date - wk_Months Months
 order by create_timestamp desc  
  ; 

SET wk_Out_Queue = upper(Trim(in_Out_Queue));
SET wk_Months = CAST(Trim(in_Months) AS decimal(2,0)); 
SET wk_User_Data = Trim(in_User_Data); 
SET wk_Spool_File = Trim(in_Spool_File); 

DROP TABLE IF EXISTS MYLIB.QSPOOLSQL; 
call QSYS2.QCMDEXC('CRTPF FILE(MYLIB/QSPOOLSQL) RCDLEN(198) TEXT(''Copied Spooled File for '||trim(wk_User_Data) || ' '') MAXMBRS(100) SIZE(*NOMAX)'); 

 -- Run the query           
 open SpooledFileList;      
                            
end                                     

This is odd. I created this SP to automate loading spooled files to send to another system. Yes, I know there are other ways, but this should have worked.  It does work when I call the SP from Run Scripts--loading all the spooled file copies to QSPOOLSQL. When I try from the STRSQL command line or RUNSQL from the main command line, the procedure drops the old copy of QSPOOLSQL and creates a new one.  It does not load the spooled files to the members as it does using Run Scripts. Thoughts? 

ACS: CALL MYLIB.LOAD_SPOOLED_FILES ('MYOUTQ','USERDATA','MYPRINTF','06')

Command line: RUNSQL SQL('CALL MYLIB.LOAD_SPOOLED_FILES (''MYOUTQ'',''USERDATA'', ''MYPRINTF'',''06'')') COMMIT(*NC)

create or replace procedure Load_Spooled_Files(    
        IN in_Out_Queue char(10) DEFAULT ' ',      
        IN in_User_Data CHAR(10) DEFAULT ' ', 
        in in_Spool_File CHAR(10) DEFAULT ' ', 
        IN in_Months char(02) DEFAULT '12'
)                                         

language SQL                                               
result sets 1                                              
SPECIFIC Load_Spooled_Files
set option DBGVIEW = *SOURCE                               
                                                           
-- Procedure Logic ----------------------------------------
begin              

declare wk_Out_Queue char(10);           
declare wk_User_Data char(10);   
declare wk_Spool_File char(10);   
declare wk_Months decimal(02,0); 

declare SpooledFileList cursor with return for        
select user_data,spooled_file_name, file_number,'M' ||RANK() OVER(ORDER BY create_timestamp DESC) AS "Rank",
QSYS2.QCMDEXC('CPYSPLF FILE(' || trim(spooled_file_name) ||') TOFILE(MYLIB/QSPOOLSQL) JOB(' || Trim(job_name) ||') SPLNBR('|| trim(file_number) ||') TOMBR('|| 'M' ||RANK() OVER(ORDER BY create_timestamp DESC)||')  CTLCHAR(*FCFC)  ') Pass
  From QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC OQ  
 where (OUTPUT_QUEUE_NAME, user_data,spooled_file_name) = (wk_Out_Queue,wk_User_Data,wk_Spool_File) 
 and date(Create_Timestamp) >= current date - wk_Months Months
 order by create_timestamp desc  
  ; 

SET wk_Out_Queue = upper(Trim(in_Out_Queue));
SET wk_Months = CAST(Trim(in_Months) AS decimal(2,0)); 
SET wk_User_Data = Trim(in_User_Data); 
SET wk_Spool_File = Trim(in_Spool_File); 

DROP TABLE IF EXISTS MYLIB.QSPOOLSQL; 
call QSYS2.QCMDEXC('CRTPF FILE(MYLIB/QSPOOLSQL) RCDLEN(198) TEXT(''Copied Spooled File for '||trim(wk_User_Data) || ' '') MAXMBRS(100) SIZE(*NOMAX)'); 

 -- Run the query           
 open SpooledFileList;      
                            
end                                     

Dear Davis

Since the cause of your problem is the returned result set of your SELECT statement, you have a choice of eliminating the returned result set by writing the result into a table instead. This is done by wrapping your SELECT within CREATE OR REPLACE TABLE...  AS ( SELECT ...)  or INSERT INTO ... VALUE ( SELECT ....).   You need another table to keep this result.  

This is odd. I created this SP to automate loading spooled files to send to another system. Yes, I know there are other ways, but this should have worked.  It does work when I call the SP from Run Scripts--loading all the spooled file copies to QSPOOLSQL. When I try from the STRSQL command line or RUNSQL from the main command line, the procedure drops the old copy of QSPOOLSQL and creates a new one.  It does not load the spooled files to the members as it does using Run Scripts. Thoughts? 

ACS: CALL MYLIB.LOAD_SPOOLED_FILES ('MYOUTQ','USERDATA','MYPRINTF','06')

Command line: RUNSQL SQL('CALL MYLIB.LOAD_SPOOLED_FILES (''MYOUTQ'',''USERDATA'', ''MYPRINTF'',''06'')') COMMIT(*NC)

create or replace procedure Load_Spooled_Files(    
        IN in_Out_Queue char(10) DEFAULT ' ',      
        IN in_User_Data CHAR(10) DEFAULT ' ', 
        in in_Spool_File CHAR(10) DEFAULT ' ', 
        IN in_Months char(02) DEFAULT '12'
)                                         

language SQL                                               
result sets 1                                              
SPECIFIC Load_Spooled_Files
set option DBGVIEW = *SOURCE                               
                                                           
-- Procedure Logic ----------------------------------------
begin              

declare wk_Out_Queue char(10);           
declare wk_User_Data char(10);   
declare wk_Spool_File char(10);   
declare wk_Months decimal(02,0); 

declare SpooledFileList cursor with return for        
select user_data,spooled_file_name, file_number,'M' ||RANK() OVER(ORDER BY create_timestamp DESC) AS "Rank",
QSYS2.QCMDEXC('CPYSPLF FILE(' || trim(spooled_file_name) ||') TOFILE(MYLIB/QSPOOLSQL) JOB(' || Trim(job_name) ||') SPLNBR('|| trim(file_number) ||') TOMBR('|| 'M' ||RANK() OVER(ORDER BY create_timestamp DESC)||')  CTLCHAR(*FCFC)  ') Pass
  From QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC OQ  
 where (OUTPUT_QUEUE_NAME, user_data,spooled_file_name) = (wk_Out_Queue,wk_User_Data,wk_Spool_File) 
 and date(Create_Timestamp) >= current date - wk_Months Months
 order by create_timestamp desc  
  ; 

SET wk_Out_Queue = upper(Trim(in_Out_Queue));
SET wk_Months = CAST(Trim(in_Months) AS decimal(2,0)); 
SET wk_User_Data = Trim(in_User_Data); 
SET wk_Spool_File = Trim(in_Spool_File); 

DROP TABLE IF EXISTS MYLIB.QSPOOLSQL; 
call QSYS2.QCMDEXC('CRTPF FILE(MYLIB/QSPOOLSQL) RCDLEN(198) TEXT(''Copied Spooled File for '||trim(wk_User_Data) || ' '') MAXMBRS(100) SIZE(*NOMAX)'); 

 -- Run the query           
 open SpooledFileList;      
                            
end