CALL QUSRJOBI from SQL procedure returns blanks

Question

When the qusrjobi API is called from an SQL procedure, both the return data and return error output parameters are returned as blanks.

declare     vData char(2000) default ' ' ;      
declare     vDataLx int  default 0 ;            
declare     vFmtName char(8) default ' ' ;      
declare     vJob char(26) default ' ' ;         
declare     vJobId char(16) default ' ' ;       
declare     VRESETFLAG char(1) default ' ' ;    
declare     vErr char(2000) default ' ' ;       
declare     VJOBNAME char(10) default ' ' ;     

set         vDataLx = 2000 ;                                        
set         vFmtName = 'JOBI0100' ;                                 
set         vJob = '*' ;                                            
set         vJobId = ' ' ;                                          
set         vErr = x'000007D0' ;                                    
SET         VRESETFLAG = '0' ;                                      
                                                                   
call        qusrjobi( vData, vDataLx, vFmtName, vJob, vJobId,       
                      vErr, VRESETFLAG ) ;                          
SET         VJOBNAME = SUBSTR(VDATA,9,10) ;                         
SET         VERRMSG = SUBSTR(VERR,9,80) ;                        
CALL        SYSTOOLS.LPRINTF( 'QUSRJOBI. JOBNAME:' || VJOBNAME    
                              || ' ERRMSG:' || RTRIM(VERRMSG)) ; 

The same SQL procedure code can successfully call an RPG program with the same parameters, where the RPG in turn calls qusrjobi and returns the results.

set         vDataLx = 2000 ;                                     
set         vFmtName = 'JOBI0100' ;                              
set         vJob = '*' ;                                         
set         vJobId = ' ' ;                                       
set         vErr = x'000007D0' ;                                 
SET         VRESETFLAG = '0' ;                                   
call        TESTRPG( vData, vDataLx, vFmtName, vJob, vJobId,     
                      vErr, VRESETFLAG ) ;                       
SET         VJOBNAME = SUBSTR(VDATA,9,10) ;                      
CALL        SYSTOOLS.LPRINTF( 'TESTRPG. JOBNAME:' || VJOBNAME ) ;

Here is the SQL procedure being called from qcmd:

> call  testsql                         
  TESTRPG. JOBNAME:STEVE28    ERRMSG:   
  QUSRJOBI. JOBNAME:           ERRMSG:  

the RPG program has the same set of parameters as the API:

h option(*SrcStmt)                                                   
                                                                     
** ------------------------ testrpg -----------------------------    
d testrpg         pr                  extpgm('TESTRPG')              
d outData                     2000a                                  
d inDataLx                      10i 0 const                          
d inFmtName                      8a   const                          
d inJOb                         26a   const                          
d inJobId                       16a   const                          
d inErr                       2000a                                  
d inResetFlag                    1a                                  
                                                                     
** ---------------------- pr_qusrJobi -------------------------------
** QusrJobi - retrieve job information                               
dpr_qusrjobi      pr                  extpgm('QUSRJOBI')             
d OutRcv                      2000a                                  
d InRcvLx                       10i 0 const                          
d InFmtName                      8a   const                          
d InJob                         26a   const                          
d InJobId                       16a   const                          
d OutError                    2000a                                  
d InReset                        1a   const                 
                                                            
** ------------------------ testrpg ------------------------
d testrpg         pi                                        
d outData                     2000a                         
d inDataLx                      10i 0 const                 
d inFmtName                      8a   const                 
d inJOb                         26a   const                 
d inJobId                       16a   const                 
d inErr                       2000a                         
d inResetFlag                    1a                         
                                                            
 /free                                                      
      pr_qusrjobi( outData: inDataLx: inFmtName:            
                   inJob: inJobId: inErr: inResetFlag ) ;   
                                                            
      *inlr = '1' ;                                         
      return      ;                                         
 /end-free                                                  

Why would this SQL procedure not be able to successfully call the qusrjobi api when it can call an RPG program with the same set of parameters?

Here is the full code of the SQL procedure:

CREATE or replace PROCEDURE testsql(                           
 )                                                             
LANGUAGE           SQL                                         
SPECIFIC    testsql                                            
SET OPTION  DATFMT = *ISO,   DLYPRP = *YES, DBGVIEW = *SOURCE, 
            USRPRF = *OWNER, DYNUSRPRF = *OWNER, COMMIT = *CHG 
BEGIN                                                          
declare     sqlCode int DEFAULT 0 ;                            
declare          vSqlCode decimal(5,0) ;                       
declare          vSqlState char(5) ;                           
declare          vErrText varchar(2000) ;                      
declare          sqlState char(5) ;                            
declare     vData char(2000) default ' ' ;                     
declare     vDataLx int  default 0 ;                           
declare     vFmtName char(8) default ' ' ;                     
declare     vJob char(26) default ' ' ;                        
declare     vJobId char(16) default ' ' ;                      
declare     VRESETFLAG char(1) default ' ' ;                   
declare     vErr char(2000) default ' ' ;                      
declare     VJOBNAME char(10) default ' ' ;                    
declare     VERRMSG char(80) default ' ' ;                     
                                                               
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION                      
begin                                                            
SET     vSqlCode  = SQLCODE ;                                    
SET     vSqlState = SQLstate ;                                   
get     diagnostics exception 1 vErrText = message_text ;        
CALL    SYSTOOLS.LPRINTF( 'ERROR:' || VERRTEXT ) ;               
end ;                                                            
                                                                 
set         vDataLx = 2000 ;                                     
set         vFmtName = 'JOBI0100' ;                              
set         vJob = '*' ;                                         
set         vJobId = ' ' ;                                       
set         vErr = x'000007D0' ;                                 
SET         VRESETFLAG = '0' ;                                   
call        TESTRPG( vData, vDataLx, vFmtName, vJob, vJobId,     
                      vErr, VRESETFLAG ) ;                       
SET         VJOBNAME = SUBSTR(VDATA,9,10) ;                      
SET         VERRMSG = SUBSTR(VERR,9,80) ;                        
CALL        SYSTOOLS.LPRINTF( 'TESTRPG. JOBNAME:' || VJOBNAME    
                              || ' ERRMSG:' || RTRIM(VERRMSG)) ; 
                                                                 
set         vDataLx = 2000 ;                                     
set         vFmtName = 'JOBI0100' ;                              
set         vJob = '*' ;                                         
set         vJobId = ' ' ;                                       
set         vErr = x'000007D0' ;                                 
SET         VRESETFLAG = '0' ;                                   
call        qusrjobi( vData, vDataLx, vFmtName, vJob, vJobId,    
                      vErr, VRESETFLAG ) ;                       
SET         VJOBNAME = SUBSTR(VDATA,9,10) ;                      
SET         VERRMSG = SUBSTR(VERR,9,80) ;                        
CALL        SYSTOOLS.LPRINTF( 'QUSRJOBI. JOBNAME:' || VJOBNAME   
                              || ' ERRMSG:' || RTRIM(VERRMSG)) ; 
                                                                 
END                                                              

Answer 1

My first thought...I would never attempt to call a system API directly from SQL, it's much easier to deal with the required data structures in a language such as RPG that can handle them.

My Second thought, you need to define QUSRJOBI as an external stored proc.

While every *PGM on the IBM i can be called as a stored proc, when done so implicitly as you have done, there are certain assumptions made by the DB. Including I believe that all the parms are input parms.

However, even after defining

create or replace procedure GetJobInfo (
     out buffer char(100)
    , in bufLen int
    , in formatName char(8)
    , in qualJobName char(26)
    , in internalJobId char(16)
--    , inout errCode char(20)
--    , in reset char(1)
    )
parameter style general
program type main
external name QUSRJOBI
;

I couldn't get this to work call GetJobInfo(?,100,'JOBI0100','*', ' ');

Still just getting blanks back in the buffer.

Lastly, the difficulties in dealing with system APIs is the very reason IBM has started to provide SQL Services that wrap them. As the other answer mentions, JOB_INFO() and ACTIVE_JOB_INFO() are probably better choices. And as my comment mentions, the key to using them with good performance is to include appropriate filters.

If all you looking for is the job_type of the current job, then

SELECT job_type 
FROM TABLE (QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER=>'*'
                                 , DETAILED_INFO => 'NONE') ) X;

Is all you need.

Answer 2

I don't have an answer for your question, but, I think, you can use this instead. It worked nicely for me!

 exec sql declare jobs_cursor cursor for                               
        select x.job_name_short, x.job_user, x.job_number,             
              case                                                     
                when x.job_type in('BCH', 'BCI', 'EVK')                
                  then 'B' else 'I'                                    
              end,                                                     
              y.job_status                                             
       from table(qsys2.job_info(job_status_filter => trim(:job_type), 
                                 job_user_filter => trim(:user_name))) x  
       join table(qsys2.active_job_info()) y                           
               on x.job_name=y.job_name                                
       for read only ; 

There's no need to use the api anymore.

Active_Job_Info
Job_Info