z/OS Funny of the Week - DB2 12

Roy Boxwell

z/OS Funny of the Week - DB2 12
Just saw this and had to chuckle...

DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
-------- ---------- -------- ----------- ----------- -------
DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
TABLE SYSIBM.SYSDUMMY1
INDEX
COLUMN IBMREQD


I really think a RUNSTATS is desperately needed there don't you? LoL

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

Nadir Doctor

z/OS Funny of the Week - DB2 12
(in response to Roy Boxwell)
Ah, Roy! - is this a battle with ASCII/Unicode? :-)


Best Regards,
Nadir

On Tue, Jan 10, 2017 at 1:03 AM, Boxwell, Roy <[login to unmask email]> wrote:

> Just saw this and had to chuckle…
>
>
>
> DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
>
> -------- ---------- -------- ----------- ----------- -------
>
> DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
>
> TABLE SYSIBM.SYSDUMMY1
>
> INDEX
>
> COLUMN IBMREQD
>
>
>
>
>
> I really think a RUNSTATS is desperately needed there don’t you? LoL
>
>
>
> *Roy Boxwell*
>
> SOFTWARE ENGINEERING GMBH and SEGUS Inc.
> -Product Development-
>
>
> *Heinrichstrasse 83-85 40239 Duesseldorf/Germany*
> * Tel. *
>
> *+49 (0)211 96149-675 <+49%20211%2096149675> Fax +49 (0)211 96149-32
> <+49%20211%209614932> Email: **[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, Bettina Schubert*
>
>
>
> -----End Original Message-----
>

Walter Jani&#223;en

AW: z/OS Funny of the Week - DB2 12
(in response to Roy Boxwell)
Hi Roy

But fortunately STALE does not create a runstats-profile. LoL

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 08:04
An: [login to unmask email]
Betreff: [DB2-L] - z/OS Funny of the Week - DB2 12

Just saw this and had to chuckle...

DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
-------- ---------- -------- ----------- ----------- -------
DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
TABLE SYSIBM.SYSDUMMY1
INDEX
COLUMN IBMREQD


I really think a RUNSTATS is desperately needed there don't you? LoL

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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


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

  • image001.png (2.6k)

Roy Boxwell

z/OS Funny of the Week - DB2 12
(in response to Walter Janißen)
Thankfully! Interestingly I selected all catalog data for this table and get a TS difference

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DBNAME TSNAME CREATOR NAME TB-STATSTIME TS-STATSTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DSNDB06 SYSEBCDC SYSIBM SYSDUMMY1 2016-07-25-15.11.03.116822 2016-07-25-15.11.03.116822
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
NAME COLNO STATSTIME COLCARDF LEN NULLS COLTYPE
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
IBMREQD 1 2016-06-29-08.37.55.613995 +0.1000000000000000E+01 1 N CHAR

The Column data has for some reason a different TS as opposed to the TB and TS. I will fire off a corrective RUNSTATS right now...

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:54 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

Hi Roy

But fortunately STALE does not create a runstats-profile. LoL

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 08:04
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - z/OS Funny of the Week - DB2 12

Just saw this and had to chuckle...

DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
-------- ---------- -------- ----------- ----------- -------
DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
TABLE SYSIBM.SYSDUMMY1
INDEX
COLUMN IBMREQD


I really think a RUNSTATS is desperately needed there don't you? LoL

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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


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

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

Walter Jani&#223;en

AW: z/OS Funny of the Week - DB2 12
(in response to Roy Boxwell)
So you didn't run a TABLE(ALL) stats.

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 09:02
An: [login to unmask email]
Betreff: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Thankfully! Interestingly I selected all catalog data for this table and get a TS difference

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DBNAME TSNAME CREATOR NAME TB-STATSTIME TS-STATSTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DSNDB06 SYSEBCDC SYSIBM SYSDUMMY1 2016-07-25-15.11.03.116822 2016-07-25-15.11.03.116822
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
NAME COLNO STATSTIME COLCARDF LEN NULLS COLTYPE
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
IBMREQD 1 2016-06-29-08.37.55.613995 +0.1000000000000000E+01 1 N CHAR

The Column data has for some reason a different TS as opposed to the TB and TS. I will fire off a corrective RUNSTATS right now...

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:54 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

Hi Roy

But fortunately STALE does not create a runstats-profile. LoL

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 08:04
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - z/OS Funny of the Week - DB2 12

Just saw this and had to chuckle...

DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
-------- ---------- -------- ----------- ----------- -------
DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
TABLE SYSIBM.SYSDUMMY1
INDEX
COLUMN IBMREQD


I really think a RUNSTATS is desperately needed there don't you? LoL

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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


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

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

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

  • image001.png (2.6k)

Roy Boxwell

z/OS Funny of the Week - DB2 12
(in response to Walter Janißen)
Oddly enough -no! One quick "proper" stats and all has gone...

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 9:24 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

So you didn't run a TABLE(ALL) stats.

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 09:02
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Thankfully! Interestingly I selected all catalog data for this table and get a TS difference

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DBNAME TSNAME CREATOR NAME TB-STATSTIME TS-STATSTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DSNDB06 SYSEBCDC SYSIBM SYSDUMMY1 2016-07-25-15.11.03.116822 2016-07-25-15.11.03.116822
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
NAME COLNO STATSTIME COLCARDF LEN NULLS COLTYPE
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
IBMREQD 1 2016-06-29-08.37.55.613995 +0.1000000000000000E+01 1 N CHAR

The Column data has for some reason a different TS as opposed to the TB and TS. I will fire off a corrective RUNSTATS right now...

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:54 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

Hi Roy

But fortunately STALE does not create a runstats-profile. LoL

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 08:04
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - z/OS Funny of the Week - DB2 12

Just saw this and had to chuckle...

DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
-------- ---------- -------- ----------- ----------- -------
DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
TABLE SYSIBM.SYSDUMMY1
INDEX
COLUMN IBMREQD


I really think a RUNSTATS is desperately needed there don't you? LoL

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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


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

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

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

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

Joe Geller

RE: z/OS Funny of the Week - DB2 12
(in response to Roy Boxwell)

Roy,

You'd better make sure all of your access paths have not changed because of the Runstats.

Joe

In Reply to Roy Boxwell:

Oddly enough -no! One quick "proper" stats and all has gone...

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 9:24 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

So you didn't run a TABLE(ALL) stats.

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 09:02
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Thankfully! Interestingly I selected all catalog data for this table and get a TS difference

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DBNAME TSNAME CREATOR NAME TB-STATSTIME TS-STATSTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DSNDB06 SYSEBCDC SYSIBM SYSDUMMY1 2016-07-25-15.11.03.116822 2016-07-25-15.11.03.116822
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
NAME COLNO STATSTIME COLCARDF LEN NULLS COLTYPE
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
IBMREQD 1 2016-06-29-08.37.55.613995 +0.1000000000000000E+01 1 N CHAR

The Column data has for some reason a different TS as opposed to the TB and TS. I will fire off a corrective RUNSTATS right now...

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:54 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

Hi Roy

But fortunately STALE does not create a runstats-profile. LoL

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 08:04
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - z/OS Funny of the Week - DB2 12

Just saw this and had to chuckle...

DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
-------- ---------- -------- ----------- ----------- -------
DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
TABLE SYSIBM.SYSDUMMY1
INDEX
COLUMN IBMREQD


I really think a RUNSTATS is desperately needed there don't you? LoL

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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


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

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

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

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

Roy Boxwell

z/OS Funny of the Week - DB2 12
(in response to Joe Geller)
Every darn one went to “Matching Index with one column”… even though there is no index – funny that!

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Joe Geller [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 2:56 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12


Roy,

You'd better make sure all of your access paths have not changed because of the Runstats.

Joe

In Reply to Roy Boxwell:
Oddly enough -no! One quick "proper" stats and all has gone...

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 9:24 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

So you didn't run a TABLE(ALL) stats.

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]><mailto:[login to unmask email]%3e>

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 09:02
An: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Betreff: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Thankfully! Interestingly I selected all catalog data for this table and get a TS difference

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DBNAME TSNAME CREATOR NAME TB-STATSTIME TS-STATSTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DSNDB06 SYSEBCDC SYSIBM SYSDUMMY1 2016-07-25-15.11.03.116822 2016-07-25-15.11.03.116822
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
NAME COLNO STATSTIME COLCARDF LEN NULLS COLTYPE
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
IBMREQD 1 2016-06-29-08.37.55.613995 +0.1000000000000000E+01 1 N CHAR

The Column data has for some reason a different TS as opposed to the TB and TS. I will fire off a corrective RUNSTATS right now...

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:54 AM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

Hi Roy

But fortunately STALE does not create a runstats-profile. LoL

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]><mailto:[login to unmask email]%3e>

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 08:04
An: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Betreff: [DB2-L] - z/OS Funny of the Week - DB2 12

Just saw this and had to chuckle...

DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
-------- ---------- -------- ----------- ----------- -------
DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
TABLE SYSIBM.SYSDUMMY1
INDEX
COLUMN IBMREQD


I really think a RUNSTATS is desperately needed there don't you? LoL

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

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


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

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

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

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

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

Daniel Luksetich

z/OS Funny of the Week - DB2 12
(in response to Roy Boxwell)
If I am not mistaken I believe that access to this “object” was “internalized” sometime ago. Maybe in 10, maybe in 11. The only thing that is external is a lock.
Cheers,
Dan

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:02 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Every darn one went to “Matching Index with one column”… even though there is no index – funny that!

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, Bettina Schubert

From: Joe Geller [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 2:56 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Roy,
You'd better make sure all of your access paths have not changed because of the Runstats.
Joe

In Reply to Roy Boxwell:
Oddly enough -no! One quick "proper" stats and all has gone...

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: [login to unmask email]<mailto:[login to unmask email] <mailto:[login to unmask email]%3e> >
http://www.seg.dehttp://www.seg.de <http://www.seg.dehttp/www.seg.de>

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 9:24 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

So you didn't run a TABLE(ALL) stats.

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] <mailto:[login to unmask email]%3e> >

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 09:02
An: [login to unmask email]<mailto:[login to unmask email] <mailto:[login to unmask email]%3e> >
Betreff: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Thankfully! Interestingly I selected all catalog data for this table and get a TS difference

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DBNAME TSNAME CREATOR NAME TB-STATSTIME TS-STATSTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DSNDB06 SYSEBCDC SYSIBM SYSDUMMY1 2016-07-25-15.11.03.116822 2016-07-25-15.11.03.116822
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
NAME COLNO STATSTIME COLCARDF LEN NULLS COLTYPE
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
IBMREQD 1 2016-06-29-08.37.55.613995 +0.1000000000000000E+01 1 N CHAR

The Column data has for some reason a different TS as opposed to the TB and TS. I will fire off a corrective RUNSTATS right now...

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: [login to unmask email]<mailto:[login to unmask email] <mailto:[login to unmask email]%3e> >
http://www.seg.dehttp://www.seg.de <http://www.seg.dehttp/www.seg.de>

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:54 AM
To: [login to unmask email]<mailto:[login to unmask email] <mailto:[login to unmask email]%3e> >
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

Hi Roy

But fortunately STALE does not create a runstats-profile. LoL

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] <mailto:[login to unmask email]%3e> >

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 08:04
An: [login to unmask email]<mailto:[login to unmask email] <mailto:[login to unmask email]%3e> >
Betreff: [DB2-L] - z/OS Funny of the Week - DB2 12

Just saw this and had to chuckle...

DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
-------- ---------- -------- ----------- ----------- -------
DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
TABLE SYSIBM.SYSDUMMY1
INDEX
COLUMN IBMREQD


I really think a RUNSTATS is desperately needed there don't you? LoL

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: [login to unmask email]<mailto:[login to unmask email] <mailto:[login to unmask email]%3e> >
http://www.seg.dehttp://www.seg.de <http://www.seg.dehttp/www.seg.de>

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


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

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

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

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

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

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

Roy Boxwell

z/OS Funny of the Week - DB2 12
(in response to Daniel Luksetich)
Someone should tell the SYSSTATFEEDBACK Daemon… ☺

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Wednesday, January 11, 2017 1:34 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

If I am not mistaken I believe that access to this “object” was “internalized” sometime ago. Maybe in 10, maybe in 11. The only thing that is external is a lock.
Cheers,
Dan

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:02 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Every darn one went to “Matching Index with one column”… even though there is no index – funny that!

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Joe Geller [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 2:56 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12


Roy,

You'd better make sure all of your access paths have not changed because of the Runstats.

Joe

In Reply to Roy Boxwell:
Oddly enough -no! One quick "proper" stats and all has gone...

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 9:24 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

So you didn't run a TABLE(ALL) stats.

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]><mailto:[login to unmask email]%3e>

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 09:02
An: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Betreff: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Thankfully! Interestingly I selected all catalog data for this table and get a TS difference

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DBNAME TSNAME CREATOR NAME TB-STATSTIME TS-STATSTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DSNDB06 SYSEBCDC SYSIBM SYSDUMMY1 2016-07-25-15.11.03.116822 2016-07-25-15.11.03.116822
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
NAME COLNO STATSTIME COLCARDF LEN NULLS COLTYPE
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
IBMREQD 1 2016-06-29-08.37.55.613995 +0.1000000000000000E+01 1 N CHAR

The Column data has for some reason a different TS as opposed to the TB and TS. I will fire off a corrective RUNSTATS right now...

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:54 AM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

Hi Roy

But fortunately STALE does not create a runstats-profile. LoL

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]><mailto:[login to unmask email]%3e>

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 08:04
An: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Betreff: [DB2-L] - z/OS Funny of the Week - DB2 12

Just saw this and had to chuckle...

DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
-------- ---------- -------- ----------- ----------- -------
DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
TABLE SYSIBM.SYSDUMMY1
INDEX
COLUMN IBMREQD


I really think a RUNSTATS is desperately needed there don't you? LoL

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

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


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

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

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

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

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

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

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

Walter Jani&#223;en

AW: z/OS Funny of the Week - DB2 12
(in response to Roy Boxwell)
Hi Roy

I can also tell something odd. Runstats cannot handle COLGROUP-statistics for columns larger then 2,000 bytes, nevertheless the optimizer will generate these recommendations and stores them into the profile. The solution: runstats ignores these entries.

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Mittwoch, 11. Januar 2017 15:44
An: [login to unmask email]
Betreff: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Someone should tell the SYSSTATFEEDBACK Daemon… ☺

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Wednesday, January 11, 2017 1:34 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

If I am not mistaken I believe that access to this “object” was “internalized” sometime ago. Maybe in 10, maybe in 11. The only thing that is external is a lock.
Cheers,
Dan

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:02 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Every darn one went to “Matching Index with one column”… even though there is no index – funny that!

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Joe Geller [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 2:56 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12


Roy,

You'd better make sure all of your access paths have not changed because of the Runstats.

Joe

In Reply to Roy Boxwell:
Oddly enough -no! One quick "proper" stats and all has gone...

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 9:24 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

So you didn't run a TABLE(ALL) stats.

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]><mailto:[login to unmask email]%3e>

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 09:02
An: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Betreff: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Thankfully! Interestingly I selected all catalog data for this table and get a TS difference

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DBNAME TSNAME CREATOR NAME TB-STATSTIME TS-STATSTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DSNDB06 SYSEBCDC SYSIBM SYSDUMMY1 2016-07-25-15.11.03.116822 2016-07-25-15.11.03.116822
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
NAME COLNO STATSTIME COLCARDF LEN NULLS COLTYPE
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
IBMREQD 1 2016-06-29-08.37.55.613995 +0.1000000000000000E+01 1 N CHAR

The Column data has for some reason a different TS as opposed to the TB and TS. I will fire off a corrective RUNSTATS right now...

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:54 AM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

Hi Roy

But fortunately STALE does not create a runstats-profile. LoL

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]><mailto:[login to unmask email]%3e>

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 08:04
An: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Betreff: [DB2-L] - z/OS Funny of the Week - DB2 12

Just saw this and had to chuckle...

DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
-------- ---------- -------- ----------- ----------- -------
DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
TABLE SYSIBM.SYSDUMMY1
INDEX
COLUMN IBMREQD


I really think a RUNSTATS is desperately needed there don't you? LoL

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

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


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

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

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

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

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

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

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

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

  • image001.png (2.6k)

Terry Purcell

RE: AW: z/OS Funny of the Week - DB2 12
(in response to Walter Janißen)

2 distinct comments based upon Walter & Roy's topics:

To Walter's comment about the 2000 bytes - true - and there is a fix coming on this, because we did see situations where the optimizer was recommending statistics to be collected that RUNSTATS can't collect. Tolerance of frequencies for > 2000 byte columns is being enhanced also - because you can have data skew which is itself a short length (for example, a NULL or blank) and RUNSTATS could collect those and optimizer use them - even though the column itself is longer than 2000 bytes. So we are changing RUNSTATS behavior here to allow frequency collection of skewed values that are less than 2000 bytes, when the column itself is > 2000 bytes.

2nd - to Roy's original point. The optimizer has always treated SYSDUMMY1 just like any other table. If you don't have stats, we think it is the default size (which is 10,000 rows). It looks like in your situation that you had stats on the table, but not on the column - so if you had a query with a WHERE clause that included IBMREQD, we would think it qualified the default (1/25 for equals), and this would drive the "STALE" recommendation - which as Walter said, wont create a profile because default stats would have collected this.

I can certainly understand that it would make sense that we simply recognize internally that SYSDUMMY1 is a 1 row table (and column has colcardf=1) regardless of what the statistics say.

Your input is always valuable. But I am also pleased that we are able to offer amusement where possible.

Regards

Terry Purcell



In Reply to Walter Janißen:

Hi Roy

I can also tell something odd. Runstats cannot handle COLGROUP-statistics for columns larger then 2,000 bytes, nevertheless the optimizer will generate these recommendations and stores them into the profile. The solution: runstats ignores these entries.

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Mittwoch, 11. Januar 2017 15:44
An: [login to unmask email]
Betreff: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Someone should tell the SYSSTATFEEDBACK Daemon… ☺

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Wednesday, January 11, 2017 1:34 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

If I am not mistaken I believe that access to this “object” was “internalized” sometime ago. Maybe in 10, maybe in 11. The only thing that is external is a lock.
Cheers,
Dan

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:02 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Every darn one went to “Matching Index with one column”… even though there is no index – funny that!

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

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

From: Joe Geller [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 2:56 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: z/OS Funny of the Week - DB2 12


Roy,

You'd better make sure all of your access paths have not changed because of the Runstats.

Joe

In Reply to Roy Boxwell:
Oddly enough -no! One quick "proper" stats and all has gone...

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 9:24 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

So you didn't run a TABLE(ALL) stats.

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]><mailto:[login to unmask email]%3e>

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 09:02
An: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Betreff: [DB2-L] - RE: z/OS Funny of the Week - DB2 12

Thankfully! Interestingly I selected all catalog data for this table and get a TS difference

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DBNAME TSNAME CREATOR NAME TB-STATSTIME TS-STATSTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
DSNDB06 SYSEBCDC SYSIBM SYSDUMMY1 2016-07-25-15.11.03.116822 2016-07-25-15.11.03.116822
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
NAME COLNO STATSTIME COLCARDF LEN NULLS COLTYPE
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----
IBMREQD 1 2016-06-29-08.37.55.613995 +0.1000000000000000E+01 1 N CHAR

The Column data has for some reason a different TS as opposed to the TB and TS. I will fire off a corrective RUNSTATS right now...

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

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

From: Walter Janißen [mailto:[login to unmask email]
Sent: Tuesday, January 10, 2017 8:54 AM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - AW: z/OS Funny of the Week - DB2 12

Hi Roy

But fortunately STALE does not create a runstats-profile. LoL

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]><mailto:[login to unmask email]%3e>

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: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Dienstag, 10. Januar 2017 08:04
An: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Betreff: [DB2-L] - z/OS Funny of the Week - DB2 12

Just saw this and had to chuckle...

DATABASE TABLESPACE REASON TYPE LAST UPDATE NBR COL
-------- ---------- -------- ----------- ----------- -------
DSNDB06 SYSEBCDC STALE CARDINALITY 2017-01-06 1
TABLE SYSIBM.SYSDUMMY1
INDEX
COLUMN IBMREQD


I really think a RUNSTATS is desperately needed there don't you? LoL

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: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>