[z/OS] Isolation Level for MQTs

Ruediger Kurtz

[z/OS] Isolation Level for MQTs
Hi all,

I am trying to create an MQT and try to specify an isolation level, but so far I have failed every time.

Here's the syntax for the MQT:

CREATE TABLE ....
(colname_01, ..., colname_nn)
as
(SELECT colname_01, ... , colname_nn
from tab_1 a
join tab_2 b
on a.colname_1 = b.colname_1
where .... )
data initially deferred
refresh deferred
maintained by system
disable query optimization;

Now where do I put a "WITH UR" clause" ?

Somewhere in the book I it says "The isolation level for the fullselect is the isolation level of the materialized query table recorded when CREATE TABLE or ALTER TABLE was issued."

Any idas are more than welcome.

Best regards

Ruediger



Rüdiger Kurtz
Abteilung Informatik Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44147
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de

________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Werner Strohmayr.
Vorstand: Dr. Wolfgang Weiler (Sprecher), Wolfgang Flaßhoff, Stefan Gronbach, Klaus-Jürgen Heitmann, Dr. Hans Olav Herøy, Jörn Sandig.
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________



_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Daniel Luksetich

Re: [z/OS] Isolation Level for MQTs
(in response to Ruediger Kurtz)
My memory on this one is weak, but try running the statement in SPUFI and setting the isolation level there.

Dan





Daniel L Luksetich

IBM Information Champion

IBM Certified Database Administrator - DB2 10 for z/OS

IBM Certified System Administrator - DB2 9 for z/OS

IBM Certified Solutions Expert - DB2 Universal Database V7.1 Database Administration for UNIX, Windows, and OS/2

IBM Certified Solutions Expert - DB2 UDB V7.1 Family Application Development

IBM Certified Advanced Technical Expert - DB2 Data Replication



Vice President of Global Database Operations

YL&A, Inc.

Database Performance Professionals

http://www.ylassoc.com

http://www.db2expert.com

http://www-01.ibm.com/software/data/champion/profiles/luksetich.html





From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Kurtz, Rüdiger
Sent: Thursday, February 03, 2011 3:24 AM
To: [login to unmask email]
Subject: [SPAM] [z/OS] Isolation Level for MQTs



Hi all,

I am trying to create an MQT and try to specify an isolation level, but so far I have failed every time.

Here's the syntax for the MQT:

CREATE TABLE ....
(colname_01, ..., colname_nn)
as
(SELECT colname_01, ... , colname_nn
from tab_1 a
join tab_2 b
on a.colname_1 = b.colname_1
where .... )
data initially deferred
refresh deferred
maintained by system
disable query optimization;

Now where do I put a "WITH UR" clause" ?

Somewhere in the book I it says "The isolation level for the fullselect is the isolation level of the materialized query table recorded when CREATE TABLE or ALTER TABLE was issued."

Any idas are more than welcome.

Best regards

Ruediger



Rüdiger Kurtz
Abteilung Informatik Betrieb


HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44147
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de

_____

HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Werner Strohmayr.
Vorstand: Dr. Wolfgang Weiler (Sprecher), Wolfgang Flaßhoff, Stefan Gronbach, Klaus-Jürgen Heitmann, Dr. Hans Olav Herøy, Jörn Sandig.

_____

Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.

_____





_____

< http://www-01.ibm.com/software/data/db2/zos/db2-10/ > Introducing IBM® DB2® 10 for z/OS

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.872 / Virus Database: 271.1.1/3414 - Release Date: 02/02/11 13:34:00


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Chris Tee

Re: [z/OS] Isolation Level for MQTs
(in response to Daniel Luksetich)

Rüdiger

Looking at the syntax in the SQL Reference, an MQT is created via AS(fullselect) and fullselect cannot have an isolation specified. The isolation clause can only be specified on a select statement.

Chris



Date: Thu, 3 Feb 2011 10:23:42 +0100
From: [login to unmask email]
Subject: [DB2-L] [z/OS] Isolation Level for MQTs
To: [login to unmask email]





Hi all,

I am trying to create an MQT and try to specify an isolation level, but so far I have failed every time.

Here's the syntax for the MQT:

CREATE TABLE ....
(colname_01, ..., colname_nn)
as
(SELECT colname_01, ... , colname_nn
from tab_1 a
join tab_2 b
on a.colname_1 = b.colname_1
where .... )
data initially deferred
refresh deferred
maintained by system
disable query optimization;

Now where do I put a "WITH UR" clause" ?

Somewhere in the book I it says "The isolation level for the fullselect is the isolation level of the materialized query table recorded when CREATE TABLE or ALTER TABLE was issued."

Any idas are more than welcome.

Best regards

Ruediger


Rüdiger Kurtz
Abteilung Informatik Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44147
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de


HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Werner Strohmayr.
Vorstand: Dr. Wolfgang Weiler (Sprecher), Wolfgang Flaßhoff, Stefan Gronbach, Klaus-Jürgen Heitmann, Dr. Hans Olav Herøy, Jörn Sandig.


Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.













The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.
_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv