RE: SQL VOLATILE / NOT VOLATILE

1. Any file that has records, (rows), that get added/deleted on a
regular basis is considered Volatile.
2. If you create the file with reuse deleted records and your number of
records added/deleted remains consistent, I would not bother with a
RGZPFM.
3. If you have a history file that gets 17 million records purged every
year, should be reorganized after the purge, but then if you will be
adding back those 17 million records or more over the year, why bother.

Reorganize is really not necessary under DB2 unless some event causes a
lot of deleted records that will not be replaced. (Purge not running
for several periods then the catch up.) If you have a batch job that
will process a large number, or the entire file, on a regular basis,
then it would be faster to reorg that file into the order for that job.
(Re think the job would be better)


Chris Bipes
Director of Information Services
CrossCheck, Inc.

707.586.0551, ext. 1102
707.585.5700 FAX
chris.bipes@xxxxxxxxxxxxxxx
www.cross-check.com

Notice of Confidentiality: This e-mail, and any attachments thereto, is
intended only for use by the addressee(s) named herein and may contain
legally privileged and/or confidential information. If you are not the
intended recipient of this e-mail, you are hereby notified that any
dissemination, distribution or copying of this e-mail, and any
attachments thereto, is strictly prohibited. If you have received this
e-mail in error, please immediately notify me by e-mail (by replying to
this message) or telephone (noted above) and permanently delete the
original and any copy of any e-mail and any printout thereof. Thank you
for your cooperation with respect to this matter.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Tuesday, July 17, 2007 1:32 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL VOLATILE / NOT VOLATILE

All,

In reading about VOLATILE vs NOT VOLATILE, I have a couple of questions
for
you more seasoned SQLers:

1) As I understand it, VOLATILE refers to cardinality of rows or some
such.
I interpret VOLATILE to mean there will be a large number of rows
added/removed on a regular basis, as opposed to updating of existing
rows.
Do I understand this correctly?

2) Assuming I do understand this correctly, is a large number of rows an
_absolute_ number or a _relative_ number? For example, I have a file
that I
will soon convert to DDL (from DDS) that will rarely contain over 200
rows.
But in a typical day, however, 100 rows could be added while another 100
rows could be deleted. I reorg it every night. Should this be
considered
VOLATILE? Or because the table is so little, who cares?

3) How about things like history files? Thousands of records will be
added
daily, but none will be deleted. Deletions only take place once a
month/quarter/year or something like that. VOLATILE?

I see a lot of examples that say NOT VOLATILE, but not many that say
VOLATILE. I was actually wondering whether VOLATILE was one of those
features that is available, but no one ever uses. :)

Thanks.