Db2 Catalog AUTH Object Access by One Statement in a Static Program

Chuck Kosin

Db2 Catalog AUTH Object Access by One Statement in a Static Program

Hi,

I am wondering if others have encountered this.  In a statically bound program with many statements, for only one statement, Detector shows the Db2 Catalog AUTH objects are accessed.  See below.  For the other statements in this program, Detector shows no AUTH object access.  To make this more puzzling, on another member in the same data sharing group, Detector shows no AUTH object access for the very same program/statement. 

 

 

20.0    >   ------------- DETECTOR Plan SQL Display ------------ 19-05-10 07:23

Command ==>                                                     Scroll ==> CSR

                                                                   LINE 1 OF 39

DB2 SSID ==> DB3P           Planname ==> HUNO01            Program ==> WPS302 

Type     ==> PACKAGE        Collid   ==> HUNSP                                

Version  ==> 2018-10-22-16.56.27.907179                                       

Total/Avg => A                                                                 

                                                                              

Interval Time ==> 04:00                           Interval Elapsed ==> 03:23:08

-------------------------------------------------------------------------------

                                                                              

Q -View SQL text, T -View Tables/Indexes, E -Explain, D -View Detail          

                                                                               

   SQL_CALL  STMT#   SECT# SQL        TIMEPCT CPUPCT  INDB2_TIME   INDB2_CPU  

   --------  ------- ----- ---------- ------- ------- ------------ ------------

_  SELECT    0006382 00006       1809  19.36%  24.89% 00:00.000383 00:00.000176

_  INSERT    0007527 00019       1007  18.17%  10.70% 00:00.000646 00:00.000136

T  SELECT    0006862 00012        858   3.35%   8.45% 00:00.000140 00:00.000126

_  UPDATE    0006152 00003       1092  12.63%   7.42% 00:00.000414 00:00.000087

 

 

20.0   >   -------- Detector SQL Table Activity Display -------- 19-05-10 07:23

COMMAND ===>                                                    Scroll ==> CSR

                                                                    LINE 1 OF 5

Total/Avg => T  T/A           DB2 SSID ==> DB3P      PLANNAME      ==> HUNO01 

Program  ==> WPS302           Type     ==> PKGE      Statement     ==> 0006862

Ctoken   ==> 1AA3C8D6043F1AD4                        Section       ==> 00012  

Collid   ==> HUNSP                                   SQL Call Type ==> SELECT 

Version  ==> 2018-10-22-16.56.27.907179                                       

                                                                              

Interval Time ==> 04:00       Interval Elapsed ==> 03:23:30       Samp ==> 100%

-------------------------------------------------------------------------------

                                                                              

TABLE               INDEX    TB_SEQ_GP TB_IDX_GP TB_LNK_GP IS_GETP   IS_TBGETP

------------------  -------- --------- --------- --------- --------- ---------

SYSUSERAUTH         DSNAUH01                                     483       322

SYSUSERAUTH                          0       322         0                    

SYSRESAUTH          DSNAGH01                                    1288         0

SYSPACKAUTH         DSNKAX02                                    1288         0

PRODUCT             XPRDUCT0                                    1726         0

Michael Hannan

RE: Db2 Catalog AUTH Object Access by One Statement in a Static Program
(in response to Chuck Kosin)

Chuck,

Would be interesting to know the BIND/REBIND parameters, and see what sort of Auths are checked at run time. I am not a big expert on Auths so would have to read up.

The Getpages are different possibly unrelated to the number of times the SELECT was executed. I think it is possible that the Auth checking happened only once during the interval which could have been on Thread creation. It could be that that this SELECT was first one executed, if there is nothing special about the SQL requiring special auth checking. Many things could make the SQL in question, unusual, like temp tables, REOPT, remote object access etc. 

Would be worth knowing if the table access has any unusual Auths, different to other tables, that might not be checked at package BIND time.

As you can see I am guessing around, without having anything definite. Just things to consider.

I have certainly seen DB2 Catalog objects referenced to execute a Static SQL before, however usually that is for first execution, and not for all of the repeats of the same SQL which would be a high overhead.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Chuck Kosin

RE: Db2 Catalog AUTH Object Access by One Statement in a Static Program
(in response to Michael Hannan)

Okay, let’s rule out that AUTH getpages are incurred during the first execution, only.  Below are two consecutive Detector screen shots of the problem statement.  The interval started at 4:00 AM.  The first screen shots is 3:04:46 (HH:MM:SS) into this interval.  The second screen shot is 3:06:18 into this interval.  Easy to see the AUTH getpages increased.  If this was a first execution event, only, you would think the AUTH getpage counts would be the same in each screen shot.  In another post, I will get the other items of interest. 

 

20.0 > -------- Detector SQL Table Activity Display -------- 19-05-11 07:04
COMMAND ===> Scroll ==> CSR
LINE 1 OF 5
Total/Avg => T T/A DB2 SSID ==> DB3P PLANNAME ==> HUNO01
Program ==> WPS302 Type ==> PKGE Statement ==> 0006862
Ctoken ==> 1AA3C8D6043F1AD4 Section ==> 00012
Collid ==> HUNSP SQL Call Type ==> SELECT
Version ==> 2018-10-22-16.56.27.907179

Interval Time ==> 04:00 Interval Elapsed ==> 03:04:46 Samp ==> 100%
-------------------------------------------------------------------------------

TABLE INDEX TB_SEQ_GP TB_IDX_GP TB_LNK_GP IS_GETP IS_TBGETP
------------------ -------- --------- --------- --------- --------- ---------
SYSUSERAUTH DSNAUH01 87 58
SYSUSERAUTH 0 58 0
SYSRESAUTH DSNAGH01 232 0
SYSPACKAUTH DSNKAX02 232 0
PRODUCT XPRDUCT0 304 0
******************************* BOTTOM OF DATA ********************************


20.0 > -------- Detector SQL Table Activity Display -------- 19-05-11 07:06
COMMAND ===> Scroll ==> CSR
LINE 1 OF 5
Total/Avg => T T/A DB2 SSID ==> DB3P PLANNAME ==> HUNO01
Program ==> WPS302 Type ==> PKGE Statement ==> 0006862
Ctoken ==> 1AA3C8D6043F1AD4 Section ==> 00012
Collid ==> HUNSP SQL Call Type ==> SELECT
Version ==> 2018-10-22-16.56.27.907179

Interval Time ==> 04:00 Interval Elapsed ==> 03:06:18 Samp ==> 100%
-------------------------------------------------------------------------------

TABLE INDEX TB_SEQ_GP TB_IDX_GP TB_LNK_GP IS_GETP IS_TBGETP
------------------ -------- --------- --------- --------- --------- ---------
SYSUSERAUTH DSNAUH01 90 60
SYSUSERAUTH 0 60 0
SYSRESAUTH DSNAGH01 240 0
SYSPACKAUTH DSNKAX02 240 0
PRODUCT XPRDUCT0 322 0
******************************* BOTTOM OF DATA ********************************