Using QUSRJOBI to retrieve Library list vs SQL.
We will be adding additional examples of how SQL is taking over, where once only API’s could do the work.
Simplifying code and helping to build a stronger developer, with the tools required to be successful.
QUSRJOBI information –Format JOBI0700 is used to retrieve Library list information.
QUSRJOBI – Example(s) from the Forum: Code400 – Forum Post
Scott Klement example: Scott Klement APi writeup
From IBM: QUSRJOBI information
Code Sample:
[cc lang=”sql”]
==>
d SkipLibs s 3 0
d Current s 10
//
// External Calls
//
d $GetLibList PR ExtPgm(‘QUSRJOBI’)
d RcvVar 32767 Options(*VarSize)
d LengthRcv 10I 0 Const
d FormatName 8A Const
d QualJobName 26 Const
d InternalJobId 16 Const
d Error_ds 15
//
// Data Structures
//
d ds_job10700 Ds 32767
d BYTRTN 10i 0
d BYTVAL 10i 0
d QJOBNM 9 34
d JOBNAM 9 18
d USRNAM 19 28
d JOBNBR 29 34
d INTID 35 50
d JOBSTS 51 60
d JOBTYP 61 61
d SUBTYP 62 62
d RESRV 63 64
d SYSLIB 10i 0
d PRDLIB 10i 0
d CURLIB 10i 0
d USRLIB 10i 0
d LIB1 81 336
d ds_Error Ds 15
d BytesProvided 10I 0 inz(%size(ds_Error))
d BytesAvail 10I 0
d ErrorId 7
/Free
//————————————————
// C a l c u l a t i o n S p e c s
//————————————————
$getLibList(ds_job10700:%Size(ds_job10700):’JOBI0700′
:’*’ : *Blanks : ds_Error);
SkipLibs = (SysLib + PrdLib)*11;
Current = %trim(%subst(LIB1:SkipLibs + 1:10));
if curlib = *zeros;
current = ‘*NONE’;
endif;
Dsply Current;
*inlr = *on;
/end-free
//——————————————————–
[/cc]
The Retrieve Job Information (QUSRJOBI) API retrieves specific information about a job.
Now for the SQL
[cc lang=”php”]
select * from qsys2.library_List_info
[/cc]
This will return in a list format as (for example):
ORDINAL_POSITION SCHEMA_NAME
1 QSYSSLIC
2 QSYS
3 QSYS2
4 QUSRSYS
5 QHLPSYS
6 QGPL
7 etc…
To place into an RPG Program would be as simple as:
[cc lang=”sql”]
H dftactgrp( *no ) OPTION(*NODEBUGIO) BndDir( ‘QC2LE’ )
F********************************************************************
F* Program Name — LIBRARYLIST
F* Written By Programmers.io Date Written — 03.17.2020
F********************************************************************
F* List all modifications to this program below in the
F* format of initials, date & description of your change.
F*
F* Initials Date Description
F* x.x.x. xx/xx/xx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
F********************************************************************
F* *-* PROGRAM DESCRIPTION *-*
F*
F* This program will:
F*
F********************************************************************
*
* Defined variables
*
d i s 10i 0 inz
d MaxItemLines s 10i 0 inz(200)
d MyOutput s 20 inz
d reply s 1 inz
d RowCount s 10i 0 inz
*—————————————————————–
* program status dataarea
*—————————————————————–
d PgmSts sds
d P1User 254 263
d @PGM *PROC
d @JOB 244 253
*
d C1 ds Dim(200) Qualified
d sequenceNumber…
d 10i 0
d schemaName 10a
exec sql set option commit=*none,datfmt=*iso,
closqlcsr=*ENDMOD;
*inlr = *on;
exec SQL
declare c1 scroll cursor for
select * from qsys2.library_List_info
for read only;
exec SQL open c1;
exec sql fetch first from C1 for :MaxItemLines rows into :C1;
exec sql get diagnostics :RowCount = ROW_COUNT;
DoW RowCount <> 0;
For i = 1 to RowCount;
myOutput = %trim(%char(c1(i).sequenceNumber)) + ‘_’ +
%trim(c1(i).schemaName);
dsply myOutput;
EndFor;
exec sql fetch next from C1 for :MaxItemLines rows into :C1;
exec sql get diagnostics :RowCount = ROW_COUNT;
EndDo;
exec sql close C1;
//********************************************************************
/End-Free
[/cc]
Download:
Retrieve library list within RPGLE using SQL
We would prefer that you turn this into an external procedure and create a parameter list as actions dictate.