DB2 V8 NFM - Why is this simple UPDATE statement using the work files

Infodemic B.V. Douwe van Sluis

DB2 V8 NFM - Why is this simple UPDATE statement using the work files
The statement UPDATE TABLE SET COL=COL*1000; ends with a -904 for the work
files.

But why is this statement using any work files.

I understand the -904 and how to fix this statement, but why is this update
not done only using the bufferpool for the tablespace.



Vriendelijke groet,
Douwe van Sluis, Infodemic B.V.





---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: DB2 V8 NFM - Why is this simple UPDATE statement using the work files
(in response to Infodemic B.V. Douwe van Sluis)
Hi

Do you have the explain-output for the statement and the cardinality for
the table and column COL?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Infodemic B.V. Douwe van Sluis

Re: DB2 V8 NFM - Why is this simple UPDATE statement using the work files
(in response to Walter Janißen)
The (Visual) EXPLAIN output shows a tablespace-scan with sequential
prefetch. Cardinality of the COL is very high (COL is actually a weigth in
kilo's, which needs to become grams). Rowcount is about 2 M.

Vriendelijke groet,
Douwe van Sluis, Infodemic B.V.


-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Walter Jani ßen
Verzonden: woensdag 22 november 2006 11:13
Aan: [login to unmask email]
Onderwerp: Re: [DB2-L] DB2 V8 NFM - Why is this simple UPDATE statement
using the work files

Hi

Do you have the explain-output for the statement and the cardinality for
the table and column COL?

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Moschelli Mauro

Re: DB2 V8 NFM - Why is this simple UPDATE statement using the work files
(in response to Infodemic B.V. Douwe van Sluis)
Are there any triggers defined on the table? Transition variables for the trigger are stored in work files.




Mauro Moschelli
SanPaoloIMI S.p.A.


IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Douwe van Sluis, Infodemic B.V.
Sent: Tuesday, November 21, 2006 10:15 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 V8 NFM - Why is this simple UPDATE statement using the work files



The statement UPDATE TABLE SET COL=COL*1000; ends with a -904 for the work files.

But why is this statement using any work files.

I understand the -904 and how to fix this statement, but why is this update not done only using the bufferpool for the tablespace.



Vriendelijke groet,
Douwe van Sluis, Infodemic B.V.




--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Il contenuto e gli allegati di questo messaggio sono strettamente
confidenziali, e ne sono vietati la diffusione e l'uso non autorizzato.

Le opinioni ivi eventualmente espresse sono quelle dell'autore: di
conseguenza il messaggio non costituisce impegno contrattuale tra
il Gruppo Sanpaolo ed il destinatario, e la banca non assume alcuna
responsabilita' riguardo ai contenuti del testo e dei relativi allegati,
ne' per eventuali intercettazioni, modifiche o danneggiamenti.

Qualora il presente messaggio Le fosse pervenuto per errore, Le saremmo
grati se lo distruggesse e, via e-mail, ce ne comunicasse l' errata
ricezione all'indirizzo [login to unmask email]


This e-mail (and any attachment(s)) is strictly confidential and for use
only by intended recipient(s). Any opinions therein expressed are those
of the author. Therefore its content doesn't represent any commitment
between Sanpaolo Group and the recipient(s) and no liability or
responsibility is accepted by Sanpaolo Group for the above mentioned
content.

Sanpaolo IMI S.p.A. is a Bank authorised by Banca d'Italia; Sanpaolo IMI
S.p.A., London Branch, is regulated by the Financial Services Authority
for the conduct of investment business in the UK.

If you are not an intended recipient(s), please notify
[login to unmask email] promptly and destroy this message.

Infodemic B.V. Douwe van Sluis

Re: DB2 V8 NFM - Why is this simple UPDATE statement using the work files
(in response to Moschelli Mauro)
We run RUNSTATS and there was no use of the work files. I am still puzzled
why the work files ever got used.

The table is not using any triggers.



Vriendelijke groet,
Douwe van Sluis, Infodemic B.V.


-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens Douwe
van Sluis, Infodemic B.V.
Verzonden: dinsdag 21 november 2006 22:15
Aan: [login to unmask email]
Onderwerp: [DB2-L] DB2 V8 NFM - Why is this simple UPDATE statement using
the work files



The statement UPDATE TABLE SET COL=COL*1000; ends with a -904 for the work
files.

But why is this statement using any work files.

I understand the -904 and how to fix this statement, but why is this update
not done only using the bufferpool for the tablespace.



Vriendelijke groet,
Douwe van Sluis, Infodemic B.V.




----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Marcel Harleman

Re: DB2 V8 NFM - Why is this simple UPDATE statement using the work files
(in response to Infodemic B.V. Douwe van Sluis)
Hi Douwe,

from the SQL Reference (UPDATE statement):

"Result table. After the base table is updated, the row is
re-evaluated and updated in the temporary result table. At this time,
it is possible that the positioned update changed the data such that
the row does not qualify the search condition, in which case the row
is marked as an update hole for subsequent FETCH operations."

This remark is made in the context of "Rules for positioned UPDATE
with a SENSITIVE STATIC scrollable cursor". I'm not sure whether
you're using DSNTEP2 or DSNTEP4 or something else. If you're using
DSNTEP4 (which is using multi row logic) then there might be a clue in
this text, since it's talking about a "temporary result table". Maybe
DSNTEP4 is "rewriting" the statement to use positioned updates and
multi row logic. And you're updating the column that's being used in
the WHERE clause ...

Just a thought. Enjoy the puzzle -:)

Regards,

Marcel.


>We run RUNSTATS and there was no use of the work files. I am still puzzled
>why the work files ever got used.
>
>The table is not using any triggers.
>
>
>
>Vriendelijke groet,
>Douwe van Sluis, Infodemic B.V.
>
>
>-----Oorspronkelijk bericht-----
>Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens Douwe
>van Sluis, Infodemic B.V.
>Verzonden: dinsdag 21 november 2006 22:15
>Aan: [login to unmask email]
>Onderwerp: [DB2-L] DB2 V8 NFM - Why is this simple UPDATE statement using
>the work files
>
>
>
>The statement UPDATE TABLE SET COL=COL*1000; ends with a -904 for the work
>files.
>
>But why is this statement using any work files.
>
>I understand the -904 and how to fix this statement, but why is this update
>not done only using the bufferpool for the tablespace.
>
>
>
>Vriendelijke groet,
>Douwe van Sluis, Infodemic B.V.
>
>
>
>
>----------------------------------------------------------------------------
>----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
>select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm