DB2 Native Stored Procedures Processing

Govind Chettiar

DB2 Native Stored Procedures Processing

Hi, I am trying to get my feet with DB2 Native Stored Procs on zOS. We are on DB2 zOS v10. I am following the instructions in the redbook "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond".

In the section on Native Stored procedures the book says " With DB2 V9, you can easily use SPUFI,
DSNTEP2, or DSNTEP4 to process your SQL procedures. "

I am assuming this means that these will "create" the Stored Procedure catalog object?  That I can then CALL from a client.

Continuing, later in the section there is a sample JCL 

"//DSNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT) 
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB9A)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP91) +
LIB('DSN910.RUNLIB.LOAD') +
PARMS('/SQLFORMAT(SQLPL),SQLTERM(#)')
END
//*
//SYSIN DD *
CREATE PROCEDURE MEDIAN_RESULT_SET (OUT MEDIANSALARY DECIMAL(7,2))
VERSION MEDIAN_V1
LANGUAGE SQL
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE V_NUMRECORDS INTEGER DEFAULT 1;
DECLARE V_COUNTER INTEGER DEFAULT 0;
DECLARE C1 CURSOR FOR
SELECT SALARY FROM STAFF ORDER BY SALARY;
DECLARE C2 CURSOR WITH RETURN FOR
SELECT NAME, JOB, SALARY
FROM STAFF
WHERE SALARY > MEDIANSALARY
ORDER BY SALARY;
DECLARE EXIT HANDLER FOR NOT FOUND
SET MEDIANSALARY = 0;
SELECT COUNT(*) INTO V_NUMRECORDS FROM STAFF;
OPEN C1;
WHILE V_COUNTER < (V_NUMRECORDS / 2 + 1) DO
FETCH C1 INTO MEDIANSALARY;
SET V_COUNTER = V_COUNTER + 1;
END WHILE;
CLOSE C1;
OPEN C2;
END#
/* "

I submitted this job making the necessary changes for DB2 subsystem and the loadlib but keep getting the error

DSNT499I  ERROR IN INPUT PARAMETER LIST

Could it be anything to do with the version of DSNTEP2 I am using? I don't provide a loadmodule library for this, relying on whatever is in the LINKLIST.

 

Thanks

 

Kai Stroh

RE: DB2 Native Stored Procedures Processing
(in response to Govind Chettiar)

The sample JCL works for me in Db2 V9 and V10 (and also 11 and 12). Message DSNT499I is issued by DSNTEP2 if it does not like what you put in the PARM(…) string, so maybe you actually have a very old version of DSNTEP2 in your DSN910.RUNLIB.LOAD.

Try running it without the SQLFORMAT parameter. This parameter will cause DSNTEP2 to insert line feeds after each record before a statement is passed to Db2. It's nice when you need to debug a stored procedure because you will see the line number, but otherwise it makes no difference. So try to use this:

PARMS('SQLTERM(#)')

I also removed the leading slash because that separates the LE parameters from the application parameters and it is not really needed here. If the above PARM string does not work either, you can remove the PARM(…) specification entirely (and also the trailing continuation character "+" in the record before that), and then add the following as the very first record in SYSIN:

--#SET TERMINATOR #

This is an alternative way to tell DSNTEP2 that SQL statements end at the "#" character instead of the semicolon.

Since DSNTEP2 is a sample program, you could try to rebuild it using the jobs DSNTEJ1P and DSNTEJ1L from your SDSNSAMP library. More information is available here:

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/apsg/src/tpc/db2z_db2prodaidsamps.html

Hope this helps

Kai

 

--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5.
Learn how the Test Data Management Field Guide can help you to improve your own process.

David Baldon

DB2 Native Stored Procedures Processing
(in response to Govind Chettiar)
Firstly I would try and find a V10 version of the Redbook and follow it as it will have been adjusted for any differences between V9 and V10.

Interesting that you’re using (anyway it looks like you are) a V9 version of TEP2 on V10 DB2 system. Have you tried using the V10 version of DSNTEP2? Do you get the same error?

You state “I don't provide a loadmodule library for this, relying on whatever is in the LINKLIST”. But, by providing the RUN and LIB parameters you are telling the CP where to find the DSNTEP2 program.

Have you looked at the explanation for message DSNT499I? It has some DSNTEP2 specific information in it so It’s probably worth reading if you haven’t already done so.

...David

From: Govind Chettiar [mailto:[login to unmask email]
Sent: Thursday, August 30, 2018 4:14 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 Native Stored Procedures Processing


Hi, I am trying to get my feet with DB2 Native Stored Procs on zOS. We are on DB2 zOS v10. I am following the instructions in the redbook "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond".

In the section on Native Stored procedures the book says " With DB2 V9, you can easily use SPUFI,
DSNTEP2, or DSNTEP4 to process your SQL procedures. "

I am assuming this means that these will "create" the Stored Procedure catalog object? That I can then CALL from a client.

Continuing, later in the section there is a sample JCL

"//DSNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB9A)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP91) +
LIB('DSN910.RUNLIB.LOAD') +
PARMS('/SQLFORMAT(SQLPL),SQLTERM(#)')
END
//*
//SYSIN DD *
CREATE PROCEDURE MEDIAN_RESULT_SET (OUT MEDIANSALARY DECIMAL(7,2))
VERSION MEDIAN_V1
LANGUAGE SQL
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE V_NUMRECORDS INTEGER DEFAULT 1;
DECLARE V_COUNTER INTEGER DEFAULT 0;
DECLARE C1 CURSOR FOR
SELECT SALARY FROM STAFF ORDER BY SALARY;
DECLARE C2 CURSOR WITH RETURN FOR
SELECT NAME, JOB, SALARY
FROM STAFF
WHERE SALARY > MEDIANSALARY
ORDER BY SALARY;
DECLARE EXIT HANDLER FOR NOT FOUND
SET MEDIANSALARY = 0;
SELECT COUNT(*) INTO V_NUMRECORDS FROM STAFF;
OPEN C1;
WHILE V_COUNTER < (V_NUMRECORDS / 2 + 1) DO
FETCH C1 INTO MEDIANSALARY;
SET V_COUNTER = V_COUNTER + 1;
END WHILE;
CLOSE C1;
OPEN C2;
END#
/* "

I submitted this job making the necessary changes for DB2 subsystem and the loadlib but keep getting the error

DSNT499I ERROR IN INPUT PARAMETER LIST

Could it be anything to do with the version of DSNTEP2 I am using? I don't provide a loadmodule library for this, relying on whatever is in the LINKLIST.



Thanks



-----End Original Message-----

Myron Miller

DB2 Native Stored Procedures Processing
(in response to Govind Chettiar)
why go thru that effort and not use Data Studio. Allows for easy execution of SPs in most environments, including full debugging capabilities - better than the old infotest. Can easy execute versions and test data with minimal to nil mainframe knowledge.

To be honest, I hardly use DSNTEP2/4 ever since i have Data Studio. Why, DS is way more powerful and capable and easier to setup.

Thanks Myron W. Miller

________________________________
From: Govind Chettiar <[login to unmask email]>
Sent: Thursday, August 30, 2018 5:14 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 Native Stored Procedures Processing


Hi, I am trying to get my feet with DB2 Native Stored Procs on zOS. We are on DB2 zOS v10. I am following the instructions in the redbook "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond".

In the section on Native Stored procedures the book says " With DB2 V9, you can easily use SPUFI,
DSNTEP2, or DSNTEP4 to process your SQL procedures. "

I am assuming this means that these will "create" the Stored Procedure catalog object? That I can then CALL from a client.

Continuing, later in the section there is a sample JCL

"//DSNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB9A)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP91) +
LIB('DSN910.RUNLIB.LOAD') +
PARMS('/SQLFORMAT(SQLPL),SQLTERM(#)')
END
//*
//SYSIN DD *
CREATE PROCEDURE MEDIAN_RESULT_SET (OUT MEDIANSALARY DECIMAL(7,2))
VERSION MEDIAN_V1
LANGUAGE SQL
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE V_NUMRECORDS INTEGER DEFAULT 1;
DECLARE V_COUNTER INTEGER DEFAULT 0;
DECLARE C1 CURSOR FOR
SELECT SALARY FROM STAFF ORDER BY SALARY;
DECLARE C2 CURSOR WITH RETURN FOR
SELECT NAME, JOB, SALARY
FROM STAFF
WHERE SALARY > MEDIANSALARY
ORDER BY SALARY;
DECLARE EXIT HANDLER FOR NOT FOUND
SET MEDIANSALARY = 0;
SELECT COUNT(*) INTO V_NUMRECORDS FROM STAFF;
OPEN C1;
WHILE V_COUNTER < (V_NUMRECORDS / 2 + 1) DO
FETCH C1 INTO MEDIANSALARY;
SET V_COUNTER = V_COUNTER + 1;
END WHILE;
CLOSE C1;
OPEN C2;
END#
/* "

I submitted this job making the necessary changes for DB2 subsystem and the loadlib but keep getting the error

DSNT499I ERROR IN INPUT PARAMETER LIST

Could it be anything to do with the version of DSNTEP2 I am using? I don't provide a loadmodule library for this, relying on whatever is in the LINKLIST.



Thanks



-----End Original Message-----

Govind Chettiar

RE: DB2 Native Stored Procedures Processing
(in response to Kai Stroh)

Hello Kai,
REmoving the SQLFORMAT parm was all it took to get it working! Thanks.

Don't be sidetracked by this statement in my posting

LIB('DSN910.RUNLIB.LOAD') + 

I pasted the JCL as I found it in the redbook but when I executed it I had removed the LIB statement, so the DSNTEP2 that executed was the one on the LINKLIST which I confirmed with our SYSPROG was the one that corresponds to DB2 v 10 (our version).

Since we are a Cobol shop, we don't have a PL/I compiler to build my own DSNTEP2!

Govind Chettiar

RE: DB2 Native Stored Procedures Processing
(in response to Myron Miller)

Sure, I will look in to this alternative too. Thanks

Govind Chettiar

RE: DB2 Native Stored Procedures Processing
(in response to David Baldon)

Hi,

Thanks for the response. As I explained in my reply to Kai, I had removed the LIB statement from the JCL when I executed it. Sorry for the distraction it caused. I did look at DSNT499I explanation. It spoke about ALIGN and MIXED and stuff like that which didn't apply to me.

Govind Chettiar

RE: DB2 Native Stored Procedures Processing
(in response to Govind Chettiar)

Hi all, 

Got a follow up question as pertains to the bind. So for now I am continuing to CREATE my SP in DSNTEP2. The process does an automatic bind of the SQL in the SP.   I have used QUALIFIER to specify what SCHEMA I want to go against. However I find that I encounter -551 messages that I, (i.e. my TSO Userid under which the job is submitted) don't have SELECT authority on the tables used in the SP. But I do have the SELECT privileges, the DBAs have added me to a GROUP that has the AUTHs granted to it.  I can execute the SELECT from SPUFI or select using DBeaver or whatever.
Is there some aspect I am missing?
Thanks

James Campbell

DB2 Native Stored Procedures Processing
(in response to Govind Chettiar)
The aspect that you are missing is the same as the recent discussion about bind owners and
bindagent.

While you have a secondary auth-id granting you access to the tables at the time you issue
your dynamic SQL, there is no guarantee that your access will exist at some time in the
future. As the secondary auth-id is derived from a group, how is Db2 to know if you are
disconnected from the group - losing your access. And hence the SP's access? How, then,
is Db2 to do is cascade revoke / invalidation?

Read the SQL Reference carefully where it talks about privileges required to access tables in
the SP. " the privilege set is the set of privileges that are held by the SQL authorization ID of
the process". Note that that is a single id - not a set of secondary auth-ids.

If the group has the required privileges you will need to
SET CURRENT SQLID = '<group>'#
before creating the procedure.

If you are then disconnected from the group, the SP will continue to have the group's
privileges.

The reason that roles are different is the Db2 can know when you lose the role. Hence can
get out is scythe.

James Campbell

On 6 Sep 2018 at 10:48, Govind Chettiar wrote:

>
> Hi all, 
> Got a follow up question as pertains to the bind. So for now I am continuing to CREATE my SP in
> DSNTEP2. The process does an automatic bind of the SQL in the SP.   I have used QUALIFIER
> to specify what SCHEMA I want to go against. However I find that I encounter -551 messages
> that I, (i.e. my TSO Userid under which the job is submitted) don't have SELECT authority on the
> tables used in the SP. But I do have the SELECT privileges, the DBAs have added me to a
> GROUP that has the AUTHs granted to it.  I can execute the SELECT from SPUFI or select using
> DBeaver or whatever.
> Is there some aspect I am missing?
> Thanks
>


---
This email has been checked for viruses by AVG.
https://www.avg.com

Govind Chettiar

RE: DB2 Native Stored Procedures Processing
(in response to James Campbell)

Thanks James. I did the approach you recommended of setting SQLID (at the beginning before the CREATE statement) to the GROUP ID that my TSO ID is a member of (what you refer to as secondary id, which I guess is the correct terminology)

SET CURRENT SQLID='SECID'#
CREATE PROCEDURE MEDIAN_RESULT_SEY (IN UPDATESAL DECIMAL(7,2),
OUT MEDIANSALARY DECIMAL(7,2))
VERSION MEDIAN_V1
LANGUAGE SQL
QUALIFIER TABQUAL
MODIFIES SQL DATA
DYNAMIC RESULT SETS 1
ALLOW DEBUG MODE
BEGIN

Now the SP is created in the schema with the same name as this secondary id.


I don't have a problem testing this from the DB2 Administration Tool product or DBeaver but I want to CALL it from a Cobol program. The program also directly SELECTs from some tables which belong to the same schema as the tables used in the Stored proc, ie TABQUAL. When I bind this Cobol program there is a conflict because the qualifier for the SP is not the same as the qualifier for the tables. i.e. if I use TABQUAL as the qualifier in the Bind, then I get -440 on the SP, if I use SECID as the qualifier I get -204 on the tables.

Is there some workaround for this situation?

Thanks!

James Campbell

DB2 Native Stored Procedures Processing
(in response to Govind Chettiar)
Why not CREATE PROCEDURE TABQUAL.MEDIAN_RESULT_SEY
?

James Campbell


On 7 Sep 2018 at 12:53, Govind Chettiar wrote:

>
> Thanks James. I did the approach you recommended of setting SQLID (at the beginning before
> the CREATE statement) to the GROUP ID that my TSO ID is a member of (what you refer to as
> secondary id, which I guess is the correct terminology)
>
> SET CURRENT SQLID='SECID'#
> CREATE PROCEDURE MEDIAN_RESULT_SEY (IN UPDATESAL DECIMAL(7,2),
> OUT MEDIANSALARY DECIMAL(7,2))
> VERSION MEDIAN_V1
> LANGUAGE SQL
> QUALIFIER TABQUAL
> MODIFIES SQL DATA
> DYNAMIC RESULT SETS 1
> ALLOW DEBUG MODE
> BEGIN
>
> Now the SP is created in the schema with the same name as this secondary id.
>
> I don't have a problem testing this from the DB2 Administration Tool product or DBeaver but I
> want to CALL it from a Cobol program. The program also directly SELECTs from some tables
> which belong to the same schema as the tables used in the Stored proc, ie TABQUAL. When I
> bind this Cobol program there is a conflict because the qualifier for the SP is not the same as the
> qualifier for the tables. i.e. if I use TABQUAL as the qualifier in the Bind, then I get -440 on the SP,
> if I use SECID as the qualifier I get -204 on the tables.
>
> Is there some workaround for this situation?
>
> Thanks!
>
>


---
This email has been checked for viruses by AVG.
https://www.avg.com

Govind Chettiar

RE: DB2 Native Stored Procedures Processing
(in response to James Campbell)

Hi James, 

Thanks for sticking with me. I tried this option but got an SQLCODE -567

PAGE 1
***INPUT STATEMENT:
SET CURRENT SQLID='SECID'#
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
- - - - - - - - - - - - - - - - 5 Line(s) not Displayed
PAGE 1
***INPUT STATEMENT:
CREATE PROCEDURE TABQUAL.MEDIAN_RESULT_SEY (IN UPDATESAL DECIMAL(7,2),
- - - - - - - - - - - - - - - - 30 Line(s) not Displayed
SQLERROR ON CREATE COMMAND, EXECUTE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -567, ERROR: REBIND AUTHORIZATION ERROR USING
SECID AUTHORITY PACKAGE = SUBSYS.TABQUAL.MEDIAN_RESULT_SEY.-
(MEDIAN_V1) PRIVILEGE = CREATE IN
DSNT418I SQLSTATE = 42501 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNTBSPL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 235 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000000EB' X'00000000' X'00000000' X'FFFFFFFF

If I don't have that TABQUAL. then it builds fine.


Btw the DBA gave me a workaround- he suggested to use the PATH(SECID) in the BIND statement. SO I have QUALIFIER(TABQUAL) and PATH(SECID) in the BIND and it works fine.

Horacio Villa

Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to James Campbell)
Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to
be loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa


Horacio Villa

Checking for completion on DB2 Admin Scheduler
(in response to Horacio Villa)
Hi,

I'm generating 10 Runstats jobs to be run in parallel and running them
from DB2 Admin Scheduler (DB2 11 for z/OS).
My problem is that this 10 jobs are generated in the same member, and I
can't check for completion of each one, just for the 1st.
I've tried many things using my imagination with no luck.
Does anyone who has gone thru the same thing have a solution?
Thanks,
Horacio Villa


James Campbell

DB2 Native Stored Procedures Processing
(in response to Govind Chettiar)
There comes a point at which I would expect you to be able to read the description of an
sqlcode. I can add nothing to the description of sqlcode -567.

James Campbell

On 10 Sep 2018 at 9:04, Govind Chettiar wrote:

>
> Hi James, 
> Thanks for sticking with me. I tried this option but got an SQLCODE -567
>
> PAGE 1
> ***INPUT STATEMENT:
> SET CURRENT SQLID='SECID'#
> RESULT OF SQL STATEMENT:
> DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
> - - - - - - - - - - - - - - - - 5 Line(s) not Displayed
> PAGE 1
> ***INPUT STATEMENT:
> CREATE
> PROCEDURE TABQUAL.MEDIAN_RESULT_SEY (IN UPDATESAL DECIMAL(7,2),
> - - - - - - - - - - - - - - - - 30 Line(s) not Displayed
> SQLERROR ON CREATE COMMAND, EXECUTE FUNCTION
> RESULT OF SQL STATEMENT:
> DSNT408I SQLCODE = -567, ERROR: REBIND AUTHORIZATION
> ERROR USING
> SECID AUTHORITY PACKAGE = SUBSYS.TABQUAL.MEDIAN_RESULT_SEY.-
> (MEDIAN_V1) PRIVILEGE = CREATE IN
> DSNT418I SQLSTATE = 42501 SQLSTATE RETURN CODE
> DSNT415I SQLERRP = DSNTBSPL SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD
> = 235 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
> DSNT416I SQLERRD = X'000000EB' X'00000000' X'00000000' X'FFFFFFFF
>
> If I don't have that TABQUAL. then it builds fine.
>
> Btw the DBA gave me a workaround- he suggested to use the PATH(SECID) in the BIND
> statement. SO I have QUALIFIER(TABQUAL) and PATH(SECID) in the BIND and it works fine.
>

---
This email has been checked for viruses by AVG.
https://www.avg.com

Roy Boxwell

Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Horacio Villa)
Not that I am aware of... you get a timestamp and thats it! I am not even
aware of an IFCID that reports it... RFE Perhaps??



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Horacio Villa [mailto:[login to unmask email]
Sent: Monday, September 10, 2018 7:38 PM
To: [login to unmask email]
Subject: [DB2-L] - Runstats from SYSIBM.SYSSTATFEEDBACK



Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to be
loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

Michael Hannan

RE: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Horacio Villa)

In Reply to Horacio Villa:

Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to
be loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa

Horatio,

Some Stats are used very heavily by so many Statements, e.g. CARDF and NPAGESF, so if joined two tables and those Stats had very different age and mismatched what Realtime Stats says, the Stats could affect the join table sequence, and be reported as Stale. I am not yet clear if Realtime Stats are consulted as well or not for Feedback purposes.

Other high usage Stats should be: NLEVELS, DATAREPEATFACTORF, maybe CLUSTERRATIOF, NLEAF,  FULLKEYCARDF (or is it just the same as CARDF if in Sync or at time collected), Type C Freq Value Stats (KEYCARD), and COLCARDF. I would not be too concerned about which SQLs need these Stats. Just accept they are needed. If the values get to be very wrong consider the impact to cost calculations and Filter Factors. I do know that data Datarepeatfactorf was added to better calculate the estimated cost of a table scan using the index but is not used for Index Only paths. NLEVELS clearly affects index probe costs. KEYCARD is used to estimate filtering for multi-column index equals match.COLCARDF is used for FFs on both Equals and range predicates (in both cases often  too optimistic but the later is worse).

Notice that Index reorg only affects NLEVELS and NLEAF above so small affect on access paths unless NLEVELs changes , and yet the long  index scan performs better after Reorg. Would I collect Index Stats after Index Reorg? Don't care really, unless NLevels or NLEAF mismatches realtime Stats and is too low. Leave pessimistic values to allow for future degradation after the reorg is no big deal to me. Hence I would rather capture Stats before the Reorg, than after.

There are other Stats that are used in very specific circumstances such as HIGH2KEY, LOW2KEY, Freq Val Type F Stats, Histogram Stats, normally for predicates with literals/dynamic/Reopt, CARDF, HIGH and LOW for a Partition  in SYSTABSTATS, etc. and others specific to Parallel paths.

So could determine for Static SQL which Stats are needed for the more specific ones using DSN_PREDICAT_TABLE. I did not yet write the SQL to do it, but have thought about suggesting additional Stats collection based on processing that table, and also specifying which Stats are unlikely to be useful, such as type F and H COLDIST Stats on COLGROUP with too many columns (where the maximum matched columns  does not come close), and LEAST type Stats probably not useful unless specific value appears in the SQL frequently.

I like to know the highest two or 3 Freq Values for all skewed single columns if ever used in a predicate. I may enhance this thought for the new DB2ZAI, where more top frequencies may be useful. Automatic count collection of top frequencies seems a good idea. I don't believe it matters if these Stats are not collected really often, unless something dramatic has changed. This is where I am quite happy with old Stats in preference to Stats missing.

Some FFs are so far off, e.g. range predicates on Host variables, it matters very little if COLCARDF is up to date or not. So if a range predicate with host var caused a Statsfeedback to say the COLCARDF data was Stale, I would have to  laugh. I don't know that that happens, as have NOT looked at the Feedback deeply enough.

Trying to know everything about Stats Feedback would be time consuming, but you could run a lot of experiments with executing access paths and seeing what Feedbacks were obtained. Would waste too much time. Best just understand how DB2 might utilise certain Stats. Stats affecting access paths are generally published, and then can deduce which ones could be used for a specific SQL. 

Sites with very thorough Stats collection, might have very little need for Stats Feedback. We survived without it for years. Sites with weak Stats collection and important stuff missing or very wrong, need something to give them a boot up the ass to get some reasonable Stats in  there.

Important Stats Feedback is about to change with DB2ZAI. It cannot do its best without the right special Stats in place. I like the sound of it a lot. Suddenly there could be need for more Histogram Stats despite hostvars in range predicates, as HIGH2KEY and LOW2KEY will be too crude for uneven distributions. Hopefully something will detect the non uniform nature of the data value range.. We all probably know that EFFECTIVE_TO_DATE (or timestamp)  columns have a very strange distribution of values for high values or Nulls.

Can DB2 already use a Histogram Stat for predicate? (I did not test it):
AND EFF_TO_DTE > CURRENT DATE

Did anyone collect Histogram Stats for that sort of column? I imagine Freq Value Stats would be more common at some sites for that.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Sep 24, 2018 - 09:03 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 24, 2018 - 09:06 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 24, 2018 - 09:12 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 24, 2018 - 09:16 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 24, 2018 - 09:20 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 24, 2018 - 09:50 AM (Europe/Berlin)

Walter Jani&#223;en

AW: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Michael Hannan)
Hi

One comment: For static SQL you can get all the feedback given for these if you have to corresponding DSN_STAT_FEEDBACK-table. You then might see duplicates.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Michael Hannan <[login to unmask email]>
Gesendet: Montag, 24. September 2018 08:44
An: [login to unmask email]
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK


In Reply to Horacio Villa:
Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to
be loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa

Horatio,

Some Stats are used very heavily by so many Statements, e.g. CARDF and NPAGESF, so if joined two tables and those Stats had very different age and mismatched what Realtime Stats says, the Stats could affect the join table sequence. I am not yet clear if Realtime Stats are consulted as well or not.

Other high usage Stats should be: NLEVELS, DATAREPEATFACTORF, maybe CLUSTERRATIOF, NLEAF, Type C Freq Value Stats (KEYCARD), and COLCARDF. I would not be too concerned about which SQLs need these Stats. Just accept they are needed. If the values get to be very wrong consider the impact to cost calculations and Filter Factors.

There are other Stats that are used in very specific circumstances such as HIGH2KEY, LOW2KEY, Freq Val Type F Stats, Histogram Stats, CARDF for a Partition in SYSTABSTATS, etc. and others specific to Parallel paths.

So could determine for Static SQL which Stats are needed for the more specific ones using DSN_PREDICAT_TABLE. I did not yet write the SQL to do it, but have thought about suggesting additional Stats collection based on processing that table, and also specifying which Stats are unlikely to be useful, such as type F and H COLDIST Stats on COLGROUP with too many columns (where the maximum matched columns does not come close), and LEAST type Stats probably not useful unless specific value appears in the SQL frequently.

I like to know the highest two or 3 Freq Values for all skewed single columns if ever used in a predicate. I may enhance this thought for the new DB2ZAI, where more top frequencies may be useful. Automatic count collection of top frequencies seems a good idea. I don't believe it matters if these Stats are not collected really often, unless something dramatic has changed. This is where I am quite happy with old Stats in preference to Stats missing.

Some FFs are so far off, e.g. range predicates on Host variables, it matters very little if COLCARDF is up to date or not. So if a range predicate with host var caused a Statsfeedback to say the COLCARDF data was Stale, I would have to laugh. I don't know that that happens, as have looked at the Feedback deeply enough.

Trying to know everything about Stats Feedback would be time consuming, but you could run a lot of experiments with executing access paths and seeing what Feedbacks were obtained. Would waste too much time. Best just understand how DB2 might utilise certain Stats. Stats affecting access paths are generally published, and then can deduce which ones could be used for a specific SQL.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Steen Rasmussen

Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Michael Hannan)
Hey Michael – that is on my wishlist too – to see the PACKAGE causing the statements being fed into SYSSTATFEEDBACK. I can however see the challenge since what should happen if the ROW already does exist in SYSSTATFEEDBACK and another package would cause an identical row to be inserted – then you either need to have denormalized/repeating rows – or a child table. The same goes for the DSN_STMT_FEEDBACK table since you can execute multiple explains and each explain could cause identical rows.

Steen

From: Michael Hannan <[login to unmask email]>
Sent: Monday, September 24, 2018 2:44 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

In Reply to Horacio Villa:
Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to
be loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa

Horatio,

Some Stats are used very heavily by so many Statements, e.g. CARDF and NPAGESF, so if joined two tables and those Stats had very different age and mismatched what Realtime Stats says, the Stats could affect the join table sequence. I am not yet clear if Realtime Stats are consulted as well or not.

Other high usage Stats should be: NLEVELS, DATAREPEATFACTORF, maybe CLUSTERRATIOF, NLEAF, Type C Freq Value Stats (KEYCARD), and COLCARDF. I would not be too concerned about which SQLs need these Stats. Just accept they are needed. If the values get to be very wrong consider the impact to cost calculations and Filter Factors.

There are other Stats that are used in very specific circumstances such as HIGH2KEY, LOW2KEY, Freq Val Type F Stats, Histogram Stats, CARDF for a Partition in SYSTABSTATS, etc. and others specific to Parallel paths.

So could determine for Static SQL which Stats are needed for the more specific ones using DSN_PREDICAT_TABLE. I did not yet write the SQL to do it, but have thought about suggesting additional Stats collection based on processing that table, and also specifying which Stats are unlikely to be useful, such as type F and H COLDIST Stats on COLGROUP with too many columns (where the maximum matched columns does not come close), and LEAST type Stats probably not useful unless specific value appears in the SQL frequently.

I like to know the highest two or 3 Freq Values for all skewed single columns if ever used in a predicate. I may enhance this thought for the new DB2ZAI, where more top frequencies may be useful. Automatic count collection of top frequencies seems a good idea. I don't believe it matters if these Stats are not collected really often, unless something dramatic has changed. This is where I am quite happy with old Stats in preference to Stats missing.

Some FFs are so far off, e.g. range predicates on Host variables, it matters very little if COLCARDF is up to date or not. So if a range predicate with host var caused a Statsfeedback to say the COLCARDF data was Stale, I would have to laugh. I don't know that that happens, as have looked at the Feedback deeply enough.

Trying to know everything about Stats Feedback would be time consuming, but you could run a lot of experiments with executing access paths and seeing what Feedbacks were obtained. Would waste too much time. Best just understand how DB2 might utilise certain Stats. Stats affecting access paths are generally published, and then can deduce which ones could be used for a specific SQL.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

Walter Jani&#223;en

AW: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Steen Rasmussen)
Hi Steen

Each explain or bind will cause identical rows, if the recommendations has still to be issued, because the appropriate stats are still not present.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]>
Gesendet: Montag, 24. September 2018 15:12
An: [login to unmask email]
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Hey Michael – that is on my wishlist too – to see the PACKAGE causing the statements being fed into SYSSTATFEEDBACK. I can however see the challenge since what should happen if the ROW already does exist in SYSSTATFEEDBACK and another package would cause an identical row to be inserted – then you either need to have denormalized/repeating rows – or a child table. The same goes for the DSN_STMT_FEEDBACK table since you can execute multiple explains and each explain could cause identical rows.

Steen

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, September 24, 2018 2:44 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

In Reply to Horacio Villa:
Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to
be loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa

Horatio,

Some Stats are used very heavily by so many Statements, e.g. CARDF and NPAGESF, so if joined two tables and those Stats had very different age and mismatched what Realtime Stats says, the Stats could affect the join table sequence. I am not yet clear if Realtime Stats are consulted as well or not.

Other high usage Stats should be: NLEVELS, DATAREPEATFACTORF, maybe CLUSTERRATIOF, NLEAF, Type C Freq Value Stats (KEYCARD), and COLCARDF. I would not be too concerned about which SQLs need these Stats. Just accept they are needed. If the values get to be very wrong consider the impact to cost calculations and Filter Factors.

There are other Stats that are used in very specific circumstances such as HIGH2KEY, LOW2KEY, Freq Val Type F Stats, Histogram Stats, CARDF for a Partition in SYSTABSTATS, etc. and others specific to Parallel paths.

So could determine for Static SQL which Stats are needed for the more specific ones using DSN_PREDICAT_TABLE. I did not yet write the SQL to do it, but have thought about suggesting additional Stats collection based on processing that table, and also specifying which Stats are unlikely to be useful, such as type F and H COLDIST Stats on COLGROUP with too many columns (where the maximum matched columns does not come close), and LEAST type Stats probably not useful unless specific value appears in the SQL frequently.

I like to know the highest two or 3 Freq Values for all skewed single columns if ever used in a predicate. I may enhance this thought for the new DB2ZAI, where more top frequencies may be useful. Automatic count collection of top frequencies seems a good idea. I don't believe it matters if these Stats are not collected really often, unless something dramatic has changed. This is where I am quite happy with old Stats in preference to Stats missing.

Some FFs are so far off, e.g. range predicates on Host variables, it matters very little if COLCARDF is up to date or not. So if a range predicate with host var caused a Statsfeedback to say the COLCARDF data was Stale, I would have to laugh. I don't know that that happens, as have looked at the Feedback deeply enough.

Trying to know everything about Stats Feedback would be time consuming, but you could run a lot of experiments with executing access paths and seeing what Feedbacks were obtained. Would waste too much time. Best just understand how DB2 might utilise certain Stats. Stats affecting access paths are generally published, and then can deduce which ones could be used for a specific SQL.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Steen Rasmussen

Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Walter Janißen)
Yes Walter – what Michael Hannan was looking for was a way to identify WHAT caused the rows to be inserted into the two various FEEDBACK tables (the one for BIND/REBIND/PREPARE and the one for EXPLAIN). There is no way to identify the statement/package causing the data to be inserted.

Steen

From: Walter Janißen <[login to unmask email]>
Sent: Monday, September 24, 2018 9:18 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Hi Steen

Each explain or bind will cause identical rows, if the recommendations has still to be issued, because the appropriate stats are still not present.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]<mailto:[login to unmask email]>>
Gesendet: Montag, 24. September 2018 15:12
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Hey Michael – that is on my wishlist too – to see the PACKAGE causing the statements being fed into SYSSTATFEEDBACK. I can however see the challenge since what should happen if the ROW already does exist in SYSSTATFEEDBACK and another package would cause an identical row to be inserted – then you either need to have denormalized/repeating rows – or a child table. The same goes for the DSN_STMT_FEEDBACK table since you can execute multiple explains and each explain could cause identical rows.

Steen

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, September 24, 2018 2:44 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

In Reply to Horacio Villa:
Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to
be loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa

Horatio,

Some Stats are used very heavily by so many Statements, e.g. CARDF and NPAGESF, so if joined two tables and those Stats had very different age and mismatched what Realtime Stats says, the Stats could affect the join table sequence. I am not yet clear if Realtime Stats are consulted as well or not.

Other high usage Stats should be: NLEVELS, DATAREPEATFACTORF, maybe CLUSTERRATIOF, NLEAF, Type C Freq Value Stats (KEYCARD), and COLCARDF. I would not be too concerned about which SQLs need these Stats. Just accept they are needed. If the values get to be very wrong consider the impact to cost calculations and Filter Factors.

There are other Stats that are used in very specific circumstances such as HIGH2KEY, LOW2KEY, Freq Val Type F Stats, Histogram Stats, CARDF for a Partition in SYSTABSTATS, etc. and others specific to Parallel paths.

So could determine for Static SQL which Stats are needed for the more specific ones using DSN_PREDICAT_TABLE. I did not yet write the SQL to do it, but have thought about suggesting additional Stats collection based on processing that table, and also specifying which Stats are unlikely to be useful, such as type F and H COLDIST Stats on COLGROUP with too many columns (where the maximum matched columns does not come close), and LEAST type Stats probably not useful unless specific value appears in the SQL frequently.

I like to know the highest two or 3 Freq Values for all skewed single columns if ever used in a predicate. I may enhance this thought for the new DB2ZAI, where more top frequencies may be useful. Automatic count collection of top frequencies seems a good idea. I don't believe it matters if these Stats are not collected really often, unless something dramatic has changed. This is where I am quite happy with old Stats in preference to Stats missing.

Some FFs are so far off, e.g. range predicates on Host variables, it matters very little if COLCARDF is up to date or not. So if a range predicate with host var caused a Statsfeedback to say the COLCARDF data was Stale, I would have to laugh. I don't know that that happens, as have looked at the Feedback deeply enough.

Trying to know everything about Stats Feedback would be time consuming, but you could run a lot of experiments with executing access paths and seeing what Feedbacks were obtained. Would waste too much time. Best just understand how DB2 might utilise certain Stats. Stats affecting access paths are generally published, and then can deduce which ones could be used for a specific SQL.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----

________________________________
Attachment Links: image001.png (3 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D9594&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=seADGInOmHzYFWDMS5tdx9iyRyiV9A00COWPQYqBMy4&s=ioERtFF19j8Yn9kXOVGyuPxsRqaIY8ASM6Hh9QBbglk&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_st_-3Fpost-3D187163-26anc-3Dp187163-23p187163&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=seADGInOmHzYFWDMS5tdx9iyRyiV9A00COWPQYqBMy4&s=lA02Pk9IKNKaNu3mbSG33F7eShaN5bufY5nn8Q5aRIM&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=seADGInOmHzYFWDMS5tdx9iyRyiV9A00COWPQYqBMy4&s=4-RVByWhWze7_W-S6EY1zFRXvhXsbgZ9KgOrxm9Cs5o&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=seADGInOmHzYFWDMS5tdx9iyRyiV9A00COWPQYqBMy4&s=5K5kHztNaJvUpGeVPRGPHBjzj-TJJ03FSLgzwfZkIPE&e=

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

Faster data refresh is here! The long waits and babysitting of unload/load jobs is over. Contact
ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data provisioning.See
http://www.ESAIGroup.com/idug https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=seADGInOmHzYFWDMS5tdx9iyRyiV9A00COWPQYqBMy4&s=KvGIEJheD4-Es8C9wtaoTq0HaFxVnPZsTwSqT6JBFPQ&e=

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=seADGInOmHzYFWDMS5tdx9iyRyiV9A00COWPQYqBMy4&s=YiVnMt33cbEW3j2MTioghBDLyDuZVk9sgHRSdNln6gQ&e=

________________________________

Phil Grainger

Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Walter Janißen)
And the more rows there are indicating the SAME “missing” stats indicates some priority of fixing the statistics

________________________________

Phil Grainger

Principal Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]





From: Walter Janißen [mailto:[login to unmask email]
Sent: 24 September 2018 14:18
To: [login to unmask email]
Subject: [DB2-L] - AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Hi Steen

Each explain or bind will cause identical rows, if the recommendations has still to be issued, because the appropriate stats are still not present.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]<mailto:[login to unmask email]>>
Gesendet: Montag, 24. September 2018 15:12
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Hey Michael – that is on my wishlist too – to see the PACKAGE causing the statements being fed into SYSSTATFEEDBACK. I can however see the challenge since what should happen if the ROW already does exist in SYSSTATFEEDBACK and another package would cause an identical row to be inserted – then you either need to have denormalized/repeating rows – or a child table. The same goes for the DSN_STMT_FEEDBACK table since you can execute multiple explains and each explain could cause identical rows.

Steen

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, September 24, 2018 2:44 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

In Reply to Horacio Villa:
Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to
be loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa

Horatio,

Some Stats are used very heavily by so many Statements, e.g. CARDF and NPAGESF, so if joined two tables and those Stats had very different age and mismatched what Realtime Stats says, the Stats could affect the join table sequence. I am not yet clear if Realtime Stats are consulted as well or not.

Other high usage Stats should be: NLEVELS, DATAREPEATFACTORF, maybe CLUSTERRATIOF, NLEAF, Type C Freq Value Stats (KEYCARD), and COLCARDF. I would not be too concerned about which SQLs need these Stats. Just accept they are needed. If the values get to be very wrong consider the impact to cost calculations and Filter Factors.

There are other Stats that are used in very specific circumstances such as HIGH2KEY, LOW2KEY, Freq Val Type F Stats, Histogram Stats, CARDF for a Partition in SYSTABSTATS, etc. and others specific to Parallel paths.

So could determine for Static SQL which Stats are needed for the more specific ones using DSN_PREDICAT_TABLE. I did not yet write the SQL to do it, but have thought about suggesting additional Stats collection based on processing that table, and also specifying which Stats are unlikely to be useful, such as type F and H COLDIST Stats on COLGROUP with too many columns (where the maximum matched columns does not come close), and LEAST type Stats probably not useful unless specific value appears in the SQL frequently.

I like to know the highest two or 3 Freq Values for all skewed single columns if ever used in a predicate. I may enhance this thought for the new DB2ZAI, where more top frequencies may be useful. Automatic count collection of top frequencies seems a good idea. I don't believe it matters if these Stats are not collected really often, unless something dramatic has changed. This is where I am quite happy with old Stats in preference to Stats missing.

Some FFs are so far off, e.g. range predicates on Host variables, it matters very little if COLCARDF is up to date or not. So if a range predicate with host var caused a Statsfeedback to say the COLCARDF data was Stale, I would have to laugh. I don't know that that happens, as have looked at the Feedback deeply enough.

Trying to know everything about Stats Feedback would be time consuming, but you could run a lot of experiments with executing access paths and seeing what Feedbacks were obtained. Would waste too much time. Best just understand how DB2 might utilise certain Stats. Stats affecting access paths are generally published, and then can deduce which ones could be used for a specific SQL.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----

________________________________
Attachment Links: image001.png (3 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D9594&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=44LDUSVmT3XPTgqL6yzY3DaI9Et59zkCgPsg2HrsnPI&s=14v5FhbZ9TliYA0DMrqhm9FftVG5AwwJ-44-WtDj2K0&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_st_-3Fpost-3D187163-26anc-3Dp187163-23p187163&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=44LDUSVmT3XPTgqL6yzY3DaI9Et59zkCgPsg2HrsnPI&s=amGOP4QgIeFZXl_ERX06Pc3Ldw4o-Pi0-YScmvx1w1Q&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=44LDUSVmT3XPTgqL6yzY3DaI9Et59zkCgPsg2HrsnPI&s=VB6nBH5dh3-TG--NFrJqLs0lTeafBjQzKwKPjSX_pPI&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=44LDUSVmT3XPTgqL6yzY3DaI9Et59zkCgPsg2HrsnPI&s=6skS0-3C2w7jDPA1YpMThuWNj8Ev63beA4R5tIIuSxw&e=

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

Faster data refresh is here! The long waits and babysitting of unload/load jobs is over. Contact
ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data provisioning.See
http://www.ESAIGroup.com/idug https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=44LDUSVmT3XPTgqL6yzY3DaI9Et59zkCgPsg2HrsnPI&s=XdNmHkoA-mM5Fs5LyVdIZw6_CySMXpmhiOTuPiN8j0U&e=

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=44LDUSVmT3XPTgqL6yzY3DaI9Et59zkCgPsg2HrsnPI&s=BtjN_Hr_z8m834TcZTjIecgTCdmirtWqSaxI1AP02kc&e=

________________________________
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (9.3k)

Walter Jani&#223;en

AW: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Steen Rasmussen)
Hi Steen

I am still not sure, if I get you right. If you bind a package and if you get recommendations and if DSN_STAT_FEEDBACK exists, then you can see, which statement generated which recommendation. In both tables you have the QUERYNO, PROGNAME, VERSION and EXPLAIN_TIME, which you can use to join with. And TYPE tells you, why this recommendation was issued.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]>
Gesendet: Montag, 24. September 2018 15:25
An: [login to unmask email]
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Yes Walter – what Michael Hannan was looking for was a way to identify WHAT caused the rows to be inserted into the two various FEEDBACK tables (the one for BIND/REBIND/PREPARE and the one for EXPLAIN). There is no way to identify the statement/package causing the data to be inserted.

Steen

From: Walter Janißen <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, September 24, 2018 9:18 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Hi Steen

Each explain or bind will cause identical rows, if the recommendations has still to be issued, because the appropriate stats are still not present.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]<mailto:[login to unmask email]>>
Gesendet: Montag, 24. September 2018 15:12
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Hey Michael – that is on my wishlist too – to see the PACKAGE causing the statements being fed into SYSSTATFEEDBACK. I can however see the challenge since what should happen if the ROW already does exist in SYSSTATFEEDBACK and another package would cause an identical row to be inserted – then you either need to have denormalized/repeating rows – or a child table. The same goes for the DSN_STMT_FEEDBACK table since you can execute multiple explains and each explain could cause identical rows.

Steen

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, September 24, 2018 2:44 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

In Reply to Horacio Villa:
Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to
be loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa

Horatio,

Some Stats are used very heavily by so many Statements, e.g. CARDF and NPAGESF, so if joined two tables and those Stats had very different age and mismatched what Realtime Stats says, the Stats could affect the join table sequence. I am not yet clear if Realtime Stats are consulted as well or not.

Other high usage Stats should be: NLEVELS, DATAREPEATFACTORF, maybe CLUSTERRATIOF, NLEAF, Type C Freq Value Stats (KEYCARD), and COLCARDF. I would not be too concerned about which SQLs need these Stats. Just accept they are needed. If the values get to be very wrong consider the impact to cost calculations and Filter Factors.

There are other Stats that are used in very specific circumstances such as HIGH2KEY, LOW2KEY, Freq Val Type F Stats, Histogram Stats, CARDF for a Partition in SYSTABSTATS, etc. and others specific to Parallel paths.

So could determine for Static SQL which Stats are needed for the more specific ones using DSN_PREDICAT_TABLE. I did not yet write the SQL to do it, but have thought about suggesting additional Stats collection based on processing that table, and also specifying which Stats are unlikely to be useful, such as type F and H COLDIST Stats on COLGROUP with too many columns (where the maximum matched columns does not come close), and LEAST type Stats probably not useful unless specific value appears in the SQL frequently.

I like to know the highest two or 3 Freq Values for all skewed single columns if ever used in a predicate. I may enhance this thought for the new DB2ZAI, where more top frequencies may be useful. Automatic count collection of top frequencies seems a good idea. I don't believe it matters if these Stats are not collected really often, unless something dramatic has changed. This is where I am quite happy with old Stats in preference to Stats missing.

Some FFs are so far off, e.g. range predicates on Host variables, it matters very little if COLCARDF is up to date or not. So if a range predicate with host var caused a Statsfeedback to say the COLCARDF data was Stale, I would have to laugh. I don't know that that happens, as have looked at the Feedback deeply enough.

Trying to know everything about Stats Feedback would be time consuming, but you could run a lot of experiments with executing access paths and seeing what Feedbacks were obtained. Would waste too much time. Best just understand how DB2 might utilise certain Stats. Stats affecting access paths are generally published, and then can deduce which ones could be used for a specific SQL.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Steen Rasmussen

Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Walter Janißen)
Maybe I am blind, but the queryno, progname etc. are only available in DSN_STAT_FEEDBACK from explain and not in SYSSTATFEEDBACK from BIND/REBIND/PREPARE.

Steen

From: Walter Janißen <[login to unmask email]>
Sent: Monday, September 24, 2018 10:13 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Hi Steen

I am still not sure, if I get you right. If you bind a package and if you get recommendations and if DSN_STAT_FEEDBACK exists, then you can see, which statement generated which recommendation. In both tables you have the QUERYNO, PROGNAME, VERSION and EXPLAIN_TIME, which you can use to join with. And TYPE tells you, why this recommendation was issued.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]<mailto:[login to unmask email]>>
Gesendet: Montag, 24. September 2018 15:25
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Yes Walter – what Michael Hannan was looking for was a way to identify WHAT caused the rows to be inserted into the two various FEEDBACK tables (the one for BIND/REBIND/PREPARE and the one for EXPLAIN). There is no way to identify the statement/package causing the data to be inserted.

Steen

From: Walter Janißen <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, September 24, 2018 9:18 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Hi Steen

Each explain or bind will cause identical rows, if the recommendations has still to be issued, because the appropriate stats are still not present.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]<mailto:[login to unmask email]>>
Gesendet: Montag, 24. September 2018 15:12
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Hey Michael – that is on my wishlist too – to see the PACKAGE causing the statements being fed into SYSSTATFEEDBACK. I can however see the challenge since what should happen if the ROW already does exist in SYSSTATFEEDBACK and another package would cause an identical row to be inserted – then you either need to have denormalized/repeating rows – or a child table. The same goes for the DSN_STMT_FEEDBACK table since you can execute multiple explains and each explain could cause identical rows.

Steen

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, September 24, 2018 2:44 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

In Reply to Horacio Villa:
Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to
be loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa

Horatio,

Some Stats are used very heavily by so many Statements, e.g. CARDF and NPAGESF, so if joined two tables and those Stats had very different age and mismatched what Realtime Stats says, the Stats could affect the join table sequence. I am not yet clear if Realtime Stats are consulted as well or not.

Other high usage Stats should be: NLEVELS, DATAREPEATFACTORF, maybe CLUSTERRATIOF, NLEAF, Type C Freq Value Stats (KEYCARD), and COLCARDF. I would not be too concerned about which SQLs need these Stats. Just accept they are needed. If the values get to be very wrong consider the impact to cost calculations and Filter Factors.

There are other Stats that are used in very specific circumstances such as HIGH2KEY, LOW2KEY, Freq Val Type F Stats, Histogram Stats, CARDF for a Partition in SYSTABSTATS, etc. and others specific to Parallel paths.

So could determine for Static SQL which Stats are needed for the more specific ones using DSN_PREDICAT_TABLE. I did not yet write the SQL to do it, but have thought about suggesting additional Stats collection based on processing that table, and also specifying which Stats are unlikely to be useful, such as type F and H COLDIST Stats on COLGROUP with too many columns (where the maximum matched columns does not come close), and LEAST type Stats probably not useful unless specific value appears in the SQL frequently.

I like to know the highest two or 3 Freq Values for all skewed single columns if ever used in a predicate. I may enhance this thought for the new DB2ZAI, where more top frequencies may be useful. Automatic count collection of top frequencies seems a good idea. I don't believe it matters if these Stats are not collected really often, unless something dramatic has changed. This is where I am quite happy with old Stats in preference to Stats missing.

Some FFs are so far off, e.g. range predicates on Host variables, it matters very little if COLCARDF is up to date or not. So if a range predicate with host var caused a Statsfeedback to say the COLCARDF data was Stale, I would have to laugh. I don't know that that happens, as have looked at the Feedback deeply enough.

Trying to know everything about Stats Feedback would be time consuming, but you could run a lot of experiments with executing access paths and seeing what Feedbacks were obtained. Would waste too much time. Best just understand how DB2 might utilise certain Stats. Stats affecting access paths are generally published, and then can deduce which ones could be used for a specific SQL.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

________________________________
Attachment Links: image001.png (3 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D9597&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=aOMh_xEtHZWb8rStI39YRZ8pZ7e-Rr6ecfWUJT3sw1U&s=yYzgJMz7Xt45rULED7xjVBV28iMfR6jTBuU977FN_Gw&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_st_-3Fpost-3D187166-26anc-3Dp187166-23p187166&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=aOMh_xEtHZWb8rStI39YRZ8pZ7e-Rr6ecfWUJT3sw1U&s=g30Ml4_Nc2rM779OaWM9W8svbqkA3OKmhYBZmt2bIOA&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=aOMh_xEtHZWb8rStI39YRZ8pZ7e-Rr6ecfWUJT3sw1U&s=JIkkfYEJAcsfyWuzfZjKjt4DBeVCVN2O-SXxUd_X4v8&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=aOMh_xEtHZWb8rStI39YRZ8pZ7e-Rr6ecfWUJT3sw1U&s=1wOjdNMzlixB7lTjL1-5Fcdg9BmaIgd6PcmoBS8BooQ&e=

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

Faster data refresh is here! The long waits and babysitting of unload/load jobs is over. Contact
ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data provisioning.See
http://www.ESAIGroup.com/idug https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=aOMh_xEtHZWb8rStI39YRZ8pZ7e-Rr6ecfWUJT3sw1U&s=nmJJ1_pHmSaHo0cHbs39Dm2OYuZvqj46KYLtYbAq8ZA&e=

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=aOMh_xEtHZWb8rStI39YRZ8pZ7e-Rr6ecfWUJT3sw1U&s=SBiaUGJXo_Xh2fZHU0lHH5RUKNQe5OoqWjA8k5c1XOo&e=

________________________________

Walter Jani&#223;en

AW: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Steen Rasmussen)
o.k. I am only talking about DSN_STAT_FEEDBACK. In SYSIBM.SYSSTATFEEDBACK a recommendation is stored only once. Then you can figure out, which statement has generated this recommendation, if it is originated from static SQL. But once you ran the right runstats, the information in SYSSTATFEEDBACK is lost and then it is very difficult to figure out, which statement that recommendation came from.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]>
Gesendet: Montag, 24. September 2018 19:16
An: [login to unmask email]
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Maybe I am blind, but the queryno, progname etc. are only available in DSN_STAT_FEEDBACK from explain and not in SYSSTATFEEDBACK from BIND/REBIND/PREPARE.

Steen

From: Walter Janißen <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, September 24, 2018 10:13 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Hi Steen

I am still not sure, if I get you right. If you bind a package and if you get recommendations and if DSN_STAT_FEEDBACK exists, then you can see, which statement generated which recommendation. In both tables you have the QUERYNO, PROGNAME, VERSION and EXPLAIN_TIME, which you can use to join with. And TYPE tells you, why this recommendation was issued.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]<mailto:[login to unmask email]>>
Gesendet: Montag, 24. September 2018 15:25
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Yes Walter – what Michael Hannan was looking for was a way to identify WHAT caused the rows to be inserted into the two various FEEDBACK tables (the one for BIND/REBIND/PREPARE and the one for EXPLAIN). There is no way to identify the statement/package causing the data to be inserted.

Steen

From: Walter Janißen <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, September 24, 2018 9:18 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Hi Steen

Each explain or bind will cause identical rows, if the recommendations has still to be issued, because the appropriate stats are still not present.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]<mailto:[login to unmask email]>>
Gesendet: Montag, 24. September 2018 15:12
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Hey Michael – that is on my wishlist too – to see the PACKAGE causing the statements being fed into SYSSTATFEEDBACK. I can however see the challenge since what should happen if the ROW already does exist in SYSSTATFEEDBACK and another package would cause an identical row to be inserted – then you either need to have denormalized/repeating rows – or a child table. The same goes for the DSN_STMT_FEEDBACK table since you can execute multiple explains and each explain could cause identical rows.

Steen

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, September 24, 2018 2:44 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

In Reply to Horacio Villa:
Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to
be loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa

Horatio,

Some Stats are used very heavily by so many Statements, e.g. CARDF and NPAGESF, so if joined two tables and those Stats had very different age and mismatched what Realtime Stats says, the Stats could affect the join table sequence. I am not yet clear if Realtime Stats are consulted as well or not.

Other high usage Stats should be: NLEVELS, DATAREPEATFACTORF, maybe CLUSTERRATIOF, NLEAF, Type C Freq Value Stats (KEYCARD), and COLCARDF. I would not be too concerned about which SQLs need these Stats. Just accept they are needed. If the values get to be very wrong consider the impact to cost calculations and Filter Factors.

There are other Stats that are used in very specific circumstances such as HIGH2KEY, LOW2KEY, Freq Val Type F Stats, Histogram Stats, CARDF for a Partition in SYSTABSTATS, etc. and others specific to Parallel paths.

So could determine for Static SQL which Stats are needed for the more specific ones using DSN_PREDICAT_TABLE. I did not yet write the SQL to do it, but have thought about suggesting additional Stats collection based on processing that table, and also specifying which Stats are unlikely to be useful, such as type F and H COLDIST Stats on COLGROUP with too many columns (where the maximum matched columns does not come close), and LEAST type Stats probably not useful unless specific value appears in the SQL frequently.

I like to know the highest two or 3 Freq Values for all skewed single columns if ever used in a predicate. I may enhance this thought for the new DB2ZAI, where more top frequencies may be useful. Automatic count collection of top frequencies seems a good idea. I don't believe it matters if these Stats are not collected really often, unless something dramatic has changed. This is where I am quite happy with old Stats in preference to Stats missing.

Some FFs are so far off, e.g. range predicates on Host variables, it matters very little if COLCARDF is up to date or not. So if a range predicate with host var caused a Statsfeedback to say the COLCARDF data was Stale, I would have to laugh. I don't know that that happens, as have looked at the Feedback deeply enough.

Trying to know everything about Stats Feedback would be time consuming, but you could run a lot of experiments with executing access paths and seeing what Feedbacks were obtained. Would waste too much time. Best just understand how DB2 might utilise certain Stats. Stats affecting access paths are generally published, and then can deduce which ones could be used for a specific SQL.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Horacio Villa

AW: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Walter Janißen)
Walter, you said "In SYSIBM.SYSSTATFEEDBACK a recommendation is stored
only once. Then you can figure out, which statement has generated this
recommendation, if it is originated from static SQL.".
My original question was "how can I figure out which statement has
generated this recommendation". I can't really figure out, even if it
comes from static SQL, something I don't know.

Horacio Villa


Walter Jani&#223;en

AW: AW: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Horacio Villa)
Hi Horacio

You can join DSN_STAT_FEEDBACK and SYSSTATFEEDBACK using TBCREATOR, TBNAME, IXCREATOR, IXNAME, COLNAME, NUMCOLUMNS, COLGROUPCOLNO, TYPE, DBNAME, TSNAME, REASON. Then you will get the QUERYNO, PACAKGE, VERSION and EXPLAIN_TIME

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Horacio Villa <[login to unmask email]>
Gesendet: Dienstag, 25. September 2018 19:47
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Walter, you said "In SYSIBM.SYSSTATFEEDBACK a recommendation is stored only once. Then you can figure out, which statement has generated this recommendation, if it is originated from static SQL.".
My original question was "how can I figure out which statement has generated this recommendation". I can't really figure out, even if it comes from static SQL, something I don't know.

Horacio Villa

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Horacio Villa

AW: AW: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Walter Janißen)
Walter,

your solution would be very nice, but....
the columns you mention are not available in DB2 11 for
SYSIBM.SYSSTATFEEDBACK (as was well commented by Michael Hannan), which is
my environment.
Horacio




Michael Hannan

RE: AW: AW: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Horacio Villa)

In Reply to Horacio Villa:

Walter,

your solution would be very nice, but....
the columns you mention are not available in DB2 11 for
SYSIBM.SYSSTATFEEDBACK (as was well commented by Michael Hannan), which is
my environment.
Horacio

Did I? I don't think so. I think what I waffled about has been just a little extrapolated along the way. LOL Steen seemed to think I wanted  various things, but actually maybe to only look into Stats Feed back in more depth some time. So far was a bit bored by it, since experienced tuners mostly know what Stats we need to get reasonable access paths, perhaps not in all cases with the latest Optimizer enhancements. Things do change. 

I just found some corrupt SYSCOLDIST Stats in a DB2 Catalog, dating back to 1998, for the SYSIBM Unicode tables. The Stats were still showing COLVALUEs in EBCDIC (multi-column) and reflected the very old column lengths (what version was that ? LOL V8 gave long names I think. Did Catlg go Unicode that early? I forget), and I was wondering how come my SYSCOLDIST report was spitting out crap. I guess STATSFEEDBACK can identify this stuff, but have not checked. The site probably did not use it. Other good Freq Val Stats were populated (for just 1 column), so in this case very easy to fix, by just deleting the old junk rows still there.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Walter Jani&#223;en

AW: AW: AW: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Horacio Villa)
Horacio

Which columns aren't available in V11. I can find any in my DB2 V11 SQL Manual.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Horacio Villa <[login to unmask email]>
Gesendet: Donnerstag, 27. September 2018 20:05
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Walter,

your solution would be very nice, but....
the columns you mention are not available in DB2 11 for SYSIBM.SYSSTATFEEDBACK (as was well commented by Michael Hannan), which is my environment.
Horacio



-----End Original Message-----
Attachments

  • image001.png (2.6k)

Horacio Villa

AW: AW: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Michael Hannan)
Well, I'm wrong then and it wasn't you, but someone was.
And still, the columns are not in SYSIBM.SYSSTATFEEDBACK.

Horacio



From: Michael Hannan <[login to unmask email]>
To: [login to unmask email]
Date: 28/09/2018 03:24
Subject: [DB2-L] - RE: AW: AW: Runstats from SYSIBM.SYSSTATFEEDBACK



In Reply to Horacio Villa:
Walter,

your solution would be very nice, but....
the columns you mention are not available in DB2 11 for
SYSIBM.SYSSTATFEEDBACK (as was well commented by Michael Hannan), which is

my environment.
Horacio

Did I? I don't think so. I think what I waffled about has been just a
little extrapolated along the way. LOL Steen seemed to think I wanted
various things, but actually maybe to only look into Stats Feed back in
more depth some time. So far was a bit bored by it, since experienced
tuners mostly know what Stats we need to get reasonable access paths,
perhaps not in all cases with the latest Optimizer enhancements. Things do
change.
I just found some corrupt SYSCOLDIST Stats in a DB2 Catalog, dating back
to 1998, for the SYSIBM Unicode tables. The Stats were still showing
COLVALUEs in EBCDIC (multi-column) and reflected the very old column
lengths (what version was that ? LOL V8 gave long names I think. Did Catlg
go Unicode that early? I forget), and I was wondering how come my
SYSCOLDIST report was spitting out crap. I guess STATSFEEDBACK can
identify this stuff, but have not checked. The site probably did not use
it. Other good Freq Val Stats were populated (for just 1 column), so in
this case very easy to fix, by just deleting the old junk rows still
there.
Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd


Site Links: View post online View mailing list online Start new thread
via email Unsubscribe from this mailing list Manage your subscription


This email has been sent to: [login to unmask email]
Faster data refresh is here! The long waits and babysitting of unload/load
jobs is over. Contact
ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand
test/QA data provisioning.See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2




Horacio Villa

AW: AW: AW: Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Walter Janißen)
Hi Walter,

I'm sorry, my fault.....I missinterpreted your reply.
Finding the appropriate DSN_STATS_FEEDBACK table that must work.
Thanks, and my apologies

Horacio



From: "Walter Jani&#223;en" <[login to unmask email]>
To: [login to unmask email]
Date: 28/09/2018 08:06
Subject: [DB2-L] - AW: AW: AW: Runstats from SYSIBM.SYSSTATFEEDBACK



Horacio

Which columns aren?t available in V11. I can find any in my DB2 V11 SQL
Manual.

Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Horacio Villa <[login to unmask email]>
Gesendet: Donnerstag, 27. September 2018 20:05
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Walter,

your solution would be very nice, but....
the columns you mention are not available in DB2 11 for
SYSIBM.SYSSTATFEEDBACK (as was well commented by Michael Hannan), which is
my environment.
Horacio

-----End Original Message-----


Attachment Links: image001.png (3 k)
Site Links: View post online View mailing list online Start new thread
via email Unsubscribe from this mailing list Manage your subscription


This email has been sent to: [login to unmask email]
Faster data refresh is here! The long waits and babysitting of unload/load
jobs is over. Contact
ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand
test/QA data provisioning.See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2




Horacio Villa

Runstats from SYSIBM.SYSSTATFEEDBACK
(in response to Steen Rasmussen)
Walter (and Michael),

these two mails were the source of my confusion.
From (my bad) memory I thought Michael was who said the columns were not
in SYSSTATFEEDBACK.
But the join you sent is really a solution.
Thanks,
Horacio



From: "Rasmussen, Steen" <[login to unmask email]>
To: "[login to unmask email]" <[login to unmask email]>
Date: 24/09/2018 14:17
Subject: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK



Maybe I am blind, but the queryno, progname etc. are only available in
DSN_STAT_FEEDBACK from explain and not in SYSSTATFEEDBACK from
BIND/REBIND/PREPARE.

Steen

From: Walter Janißen <[login to unmask email]>
Sent: Monday, September 24, 2018 10:13 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Hi Steen

I am still not sure, if I get you right. If you bind a package and if you
get recommendations and if DSN_STAT_FEEDBACK exists, then you can see,
which statement generated which recommendation. In both tables you have
the QUERYNO, PROGNAME, VERSION and EXPLAIN_TIME, which you can use to join
with. And TYPE tells you, why this recommendation was issued.

Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]>
Gesendet: Montag, 24. September 2018 15:25
An: [login to unmask email]
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Yes Walter ? what Michael Hannan was looking for was a way to identify
WHAT caused the rows to be inserted into the two various FEEDBACK tables
(the one for BIND/REBIND/PREPARE and the one for EXPLAIN). There is no way
to identify the statement/package causing the data to be inserted.

Steen

From: Walter Janißen <[login to unmask email]>
Sent: Monday, September 24, 2018 9:18 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: Runstats from SYSIBM.SYSSTATFEEDBACK

Hi Steen

Each explain or bind will cause identical rows, if the recommendations has
still to be issued, because the appropriate stats are still not present.

Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rasmussen, Steen <[login to unmask email]>
Gesendet: Montag, 24. September 2018 15:12
An: [login to unmask email]
Betreff: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

Hey Michael ? that is on my wishlist too ? to see the PACKAGE causing the
statements being fed into SYSSTATFEEDBACK. I can however see the challenge
since what should happen if the ROW already does exist in SYSSTATFEEDBACK
and another package would cause an identical row to be inserted ? then you
either need to have denormalized/repeating rows ? or a child table. The
same goes for the DSN_STMT_FEEDBACK table since you can execute multiple
explains and each explain could cause identical rows.

Steen

From: Michael Hannan <[login to unmask email]>
Sent: Monday, September 24, 2018 2:44 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats from SYSIBM.SYSSTATFEEDBACK

CAUTION: This email originated from outside of CA. Do not click links or
open attachments unless you recognize the sender and know the content is
safe.
In Reply to Horacio Villa:
Hi,

we have DB2 11 for z/OS.
I wonder if it is possible to identify the statements that cause rows to
be loaded in SYSIBM.SYSSTATFEEDBACK.

Thanks,
Horacio Villa
Horatio,
Some Stats are used very heavily by so many Statements, e.g. CARDF and
NPAGESF, so if joined two tables and those Stats had very different age
and mismatched what Realtime Stats says, the Stats could affect the join
table sequence. I am not yet clear if Realtime Stats are consulted as well
or not.
Other high usage Stats should be: NLEVELS, DATAREPEATFACTORF, maybe
CLUSTERRATIOF, NLEAF, Type C Freq Value Stats (KEYCARD), and COLCARDF. I
would not be too concerned about which SQLs need these Stats. Just accept
they are needed. If the values get to be very wrong consider the impact to
cost calculations and Filter Factors.
There are other Stats that are used in very specific circumstances such as
HIGH2KEY, LOW2KEY, Freq Val Type F Stats, Histogram Stats, CARDF for a
Partition in SYSTABSTATS, etc. and others specific to Parallel paths.
So could determine for Static SQL which Stats are needed for the more
specific ones using DSN_PREDICAT_TABLE. I did not yet write the SQL to do
it, but have thought about suggesting additional Stats collection based on
processing that table, and also specifying which Stats are unlikely to be
useful, such as type F and H COLDIST Stats on COLGROUP with too many
columns (where the maximum matched columns does not come close), and
LEAST type Stats probably not useful unless specific value appears in the
SQL frequently.
I like to know the highest two or 3 Freq Values for all skewed single
columns if ever used in a predicate. I may enhance this thought for the
new DB2ZAI, where more top frequencies may be useful. Automatic count
collection of top frequencies seems a good idea. I don't believe it
matters if these Stats are not collected really often, unless something
dramatic has changed. This is where I am quite happy with old Stats in
preference to Stats missing.
Some FFs are so far off, e.g. range predicates on Host variables, it
matters very little if COLCARDF is up to date or not. So if a range
predicate with host var caused a Statsfeedback to say the COLCARDF data
was Stale, I would have to laugh. I don't know that that happens, as have
looked at the Feedback deeply enough.
Trying to know everything about Stats Feedback would be time consuming,
but you could run a lot of experiments with executing access paths and
seeing what Feedbacks were obtained. Would waste too much time. Best just
understand how DB2 might utilise certain Stats. Stats affecting access
paths are generally published, and then can deduce which ones could be
used for a specific SQL.
Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----


Site Links: View post online View mailing list online Start new thread
via email Unsubscribe from this mailing list Manage your subscription


This email has been sent to: [login to unmask email]
Faster data refresh is here! The long waits and babysitting of unload/load
jobs is over. Contact
ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand
test/QA data provisioning.See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2