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]

QUSRJOBI – Retrieve Library List
QUSRJOBI – Retrieve Library List

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:

Library List using SQL
Library List using SQL

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.

 

 

 

SQL Vs. QUSRJOBI (Retrieving Library List)
Tagged on: