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

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