Re: iDoctor & Full Opens (and how can i count full-opens without iDoctor?)

Thank you, Dawn ... by following your suggestions and playing with those
SQL statements
-- Monitor FULL OPEN DA QPFRDATA
create or replace alias qtemp.performancetoday for
QPFRDATA.QAPMJOBOS(Q238000002); // change this member!!!
-- Check user jobs RDEPEDRINI
select intnum, cast(jbname as char(15) ccsid 280) as JBNAME, cast(jbnbr
as char(15) ccsid 280) as JBNBR,
cast(jbuser as char(10) ccsid 280) as JBUSER, jblbo as SQLFullOpens, jbnus
as FullOpens, datetime, a.*
from qtemp.performancetoday a
where cast(jbuser as char(10) ccsid 280)='RDEPEDRINI'
order by datetime desc;

I finally found my problem with "full-opens" ... a single program with
*INLR=*on called and called a lot of times!
Changing *inRT=*on and resetting some variables and DSs, full-opens have
dropped by 95% and got a great performance enhancement too!

Thank you Dawn!



Il giorno mar 24 ago 2021 alle ore 13:56 Dawn May <dawnmayican@xxxxxxxxxxx>
ha scritto:

Full opens are tracked in the Collection Services file QAPMJOBOS.
There are 3 fields of interest:
JBLBO - SQL Full opens
JBLBS - SQL Psuedo opens
JBNUS - Full opens (non SQL and SQL)

The fields in the file are documented here:

https://www.ibm.com/docs/en/i/7.4?topic=data-collection-services-files-qapmjobos

iDoctor uses the Collection Services file to present the charts
summarizing full opens; sometimes I find looking at the raw data with my
own queries can help clear up what’s going on.


Also, take a look at the Database perspectives in the Performance Data
Investigator as well - similar data, but presented a bit different from
iDoctor and sometimes easier to interpret.
You do need to have the 5770-PT1 product installed for these perspectives
to be available.

I wrote about the database perspectives in these two articles:

https://www.itjungle.com/2021/06/21/guru-understanding-database-performance-using-the-performance-data-investigator-part-1/

https://www.itjungle.com/2021/06/28/guru-understanding-database-performance-using-the-performance-data-investigator-part-2/

Dawn

On Aug 24, 2021, at 6:57 AM, Roberto De Pedrini <

roberto.faq400@xxxxxxxxx> wrote:

I have a problem with a big number of "Full-Opens" that comes to light

with

an iDoctor analysis and I tried to solve it by moving from native i/o to
SQL i/o without achieving any improvement.

First of all: All the "ERP solution" don't access the database directly
but through some service programs with procedures like "GetRecFile01"

(see

below).



So I wrote some tests that read 1 million records and call decoding
procedures in different ways ... but I got completely different results:
tests count a few full-opens both in native and in SQL I/O, despite a
million records read. Standard programs count a lot of FullOpens both in
native and in SQL I/O!


If I run in debug mode programs (with SQL I/O) that count a lot of
"full-opens" I see a lot of SQL7911 ODP Reused ... why iDoctor count so
much "full-opens"?
I added this SQL option in my SRVPGMs too.
"exec sql set option closqlcsr = *endactgrp;"

I don't understand!

Question 2: How can I count native-full-opens without iDoctor so I can

try

and see easily with an SQL statement?
I tried STRDBMON and QMGTOOLS/STRPSC JOBWATCHER(Y) but I can't find those
numbers in logs files


---
Examples
---
-----
GetRecFile01 Record Access Procedure for FILE01 in a SRVPGM (with native
I/O "original")
...
FFILE0101L IF E K DISK USROPN
...
PGetRecFile01 B EXPORT
DGetRecFile01 PI n
D entryId CONST LIKE(FILE01Id)
D exitRecord LIKEREC(FILE01RKD)
IF NOT %OPEN(FILE0101L) ;
OPEN FILE0101L ;
ENDIF ;
CHAIN (entryId) FILE0101L exitRecord ;
IF NOT %FOUND ;
CLEAR exitRecord ;
ENDIF ;
RETURN %FOUND ;
P E

---
GetRecFile01NEW Record Access Procedure for FILE01 in a SRVPGM (with SQL
I/O)
...
dcl-ds FILE01DS extname('FILE010F') template qualified end-ds;
..
exec sql set option closqlcsr = *endactgrp;
..
Dcl-Proc GetRecFile01New EXPORT;
Dcl-PI GetRecFile01New Ind;
entryId CONST LIKE(FILE01DS.Id);
exitRecord LIKEDS(FILE01DS);
End-PI;
exec sql
select * into :exitRecord :null_ds
from FILE010F
where id=:entryid;
select;
when sqlcod=100;
clear exitRecord;
return *off;
when sqlcod<0;
EXEC SQL GET DIAGNOSTICS CONDITION 1 :ERR= MESSAGE_TEXT ;
DSPLY ('01-'+ERR);
clear exitRecord;
return *off;
ENDSL;
return *on;
end-proc;




--
--
Roberto De Pedrini - Faq400 Srl
http://blog.faq400.com/en *A new IBM i Blog from IBM i Italian

Community

Faq400*
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com