DB2 Logging the update when views is used

Mike Sherazi

DB2 Logging the update when views is used
Hi everyone,
If I have a table with column A1, A2, A3, A4. I created 2 views. View
1 has column A2, A3 in this order. View 2 has A3, A2 in this order. If
the a program is updating the table using view1 or view2 but only
updating the value of A2. My question is, Is DB2 in both cases will log
the value of A2 and A3. Thanks in advance.



Francis C - CNF Leblanc

Re: DB2 Logging the update when views is used
(in response to Mike Sherazi)
The use of the views is irrelevant. DB2 will look at the table, and only
log the bytes being changed. There was quite a bit of recent discussion
regarding DB2 logging. You should be able to locate it in the archives.



Best of luck.



-----Original Message-----
From: Mike Sherazi [mailto:[login to unmask email]
Sent: Wednesday, December 18, 2002 3:50 PM
To: [login to unmask email]
Subject: DB2 Logging the update when views is used



Hi everyone,
If I have a table with column A1, A2, A3, A4. I created 2 views. View 1
has column A2, A3 in this order. View 2 has A3, A2 in this order. If the a
program is updating the table using view1 or view2 but only updating the
value of A2. My question is, Is DB2 in both cases will log the value of A2
and A3. Thanks in advance.



Deborah Morris

DB2 Logging
(in response to Francis C - CNF Leblanc)
Does anyone know whether you can turn DB2 logging off at the execution of a
DB2 Cobol batch program?

We have a conversion program that will insert some 9,000,000 + rows of data
to DB2 and we don't want
to worry about sizing the logs larger or possibly impacting our current
production environment.

We know that we could write these records to a flat file and load them to
DB2 using the DB2 load utility
with the "log no" option, but we were hoping to get this processing achieved
with the cobol batch insert
process that has already been written.

Thank you in advance for any help you can give me with this.

Deborah A. Morris
Data Base Analyst/Programmer Specialist
UNC Health Care System
(919) 966-2197 (voice)



Mae Bruce

Re: DB2 Logging
(in response to Deborah Morris)
How about coding a commit in the program to happen every so many records?

Mae Bruce
State of Missouri
OA/Division of Information Services

-----Original Message-----
From: Morris, Deborah [mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Thursday, December 19, 2002 2:04 PM
To: [login to unmask email]
Subject: DB2 Logging


Does anyone know whether you can turn DB2 logging off at the execution of a
DB2 Cobol batch program?

We have a conversion program that will insert some 9,000,000 + rows of data
to DB2 and we don't want to worry about sizing the logs larger or possibly
impacting our current production environment.

We know that we could write these records to a flat file and load them to
DB2 using the DB2 load utility with the "log no" option, but we were hoping
to get this processing achieved with the cobol batch insert process that has
already been written.

Thank you in advance for any help you can give me with this.

Deborah A. Morris
Data Base Analyst/Programmer Specialist
UNC Health Care System
(919) 966-2197 (voice)


John Spiewak

Re: DB2 Logging
(in response to Mae Bruce)
Not sure what version you are using, but look into the "NOT LOGGED
INITIAL" option for tables. This prevent logging until the first commit
occurs. This must be defined at table create and can be turned on again
using the "ALTER TABLE" statement.

John Spiewak

R & D Consultant / Database Engineer

TradePower

(215) 542-8300 X 1-5774

(800) 289-8771

[login to unmask email]

www.tradepower.com



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Morris, Deborah
Sent: Thursday, December 19, 2002 3:04 PM
To: [login to unmask email]
Subject: DB2 Logging


Does anyone know whether you can turn DB2 logging off at the execution
of a DB2 Cobol batch program?

We have a conversion program that will insert some 9,000,000 + rows of
data to DB2 and we don't want to worry about sizing the logs larger or
possibly impacting our current production environment.

We know that we could write these records to a flat file and load them
to DB2 using the DB2 load utility with the "log no" option, but we were
hoping to get this processing achieved with the cobol batch insert
process that has already been written.

Thank you in advance for any help you can give me with this.

Deborah A. Morris
Data Base Analyst/Programmer Specialist
UNC Health Care System
(919) 966-2197 (voice)



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Deborah Morris

Re: DB2 Logging
(in response to John Spiewak)
THe program does a commit after every 50 inserts. How does the commit
change the logging process?

-----Original Message-----
From: Bruce, Mae [mailto:[login to unmask email]
Sent: Thursday, December 19, 2002 3:18 PM
To: [login to unmask email]
Subject: Re: DB2 Logging



How about coding a commit in the program to happen every so many records?

Mae Bruce
State of Missouri
OA/Division of Information Services

-----Original Message-----
From: Morris, Deborah [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Thursday, December 19, 2002 2:04 PM
To: [login to unmask email]
Subject: DB2 Logging


Does anyone know whether you can turn DB2 logging off at the execution of a
DB2 Cobol batch program?

We have a conversion program that will insert some 9,000,000 + rows of data
to DB2 and we don't want to worry about sizing the logs larger or possibly
impacting our current production environment.

We know that we could write these records to a flat file and load them to
DB2 using the DB2 load utility with the "log no" option, but we were hoping
to get this processing achieved with the cobol batch insert process that has
already been written.

Thank you in advance for any help you can give me with this.

Deborah A. Morris
Data Base Analyst/Programmer Specialist
UNC Health Care System
(919) 966-2197 (voice)


Andy Lankester

Re: DB2 Logging
(in response to Deborah Morris)
Short answer - NO for DB2 on the mainframe. But add it to the RQ list.

Andy Lankester
CDB Software

<plug>
We do have a VERY efficient LOAD. See www.cdbsoftware.com
</plug>

> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On Behalf Of Morris, Deborah
> Sent: 19 December 2002 20:04
> To: [login to unmask email]
> Subject: DB2 Logging
>
>
> Does anyone know whether you can turn DB2 logging off at the
> execution of a DB2 Cobol batch program?
>
> We have a conversion program that will insert some 9,000,000
> + rows of data to DB2 and we don't want to worry about sizing
> the logs larger or possibly impacting our current production
> environment.
>
> We know that we could write these records to a flat file and
> load them to DB2 using the DB2 load utility with the "log no"
> option, but we were hoping to get this processing achieved
> with the cobol batch insert process that has already been written.
>
> Thank you in advance for any help you can give me with this.
>
> Deborah A. Morris
> Data Base Analyst/Programmer Specialist
> UNC Health Care System
> (919) 966-2197 (voice)
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://listserv.ylassoc.com. The owners of the > list can be
>
>



Eric Pearson

Re: DB2 Logging
(in response to Andy Lankester)
If you are referring to
DB2 for OS/390 and Z/0S
at any version up to and including
Version 7, logging of INSERT/DELETE/UPDATE is not optional.

Regards,
Eric Pearson
NS ITO DB2 support


-----Original Message-----
From: Morris, Deborah [mailto:[login to unmask email]
Sent: Thursday, December 19, 2002 3:04 PM
To: [login to unmask email]
Subject: DB2 Logging


Does anyone know whether you can turn DB2 logging off at the execution of a
DB2 Cobol batch program?

We have a conversion program that will insert some 9,000,000 + rows of data
to DB2 and we don't want
to worry about sizing the logs larger or possibly impacting our current
production environment.

We know that we could write these records to a flat file and load them to
DB2 using the DB2 load utility
with the "log no" option, but we were hoping to get this processing achieved
with the cobol batch insert
process that has already been written.

Thank you in advance for any help you can give me with this.

Deborah A. Morris
Data Base Analyst/Programmer Specialist
UNC Health Care System
(919) 966-2197 (voice)








Rohn Solecki

Re: DB2 Logging
(in response to Mae Bruce)
Short answer - NO.

Long answer, check the archives. The question was discussed in the last
couple of months. In summary, DB2/IBM is very concerned about data
integrity and data recovery. Allowing program update with LOG NO defeats
that. Using utility currently is only way to get LOG NO, and you still
have to create a full IC (or trick DB2 with something like START
...ACCESS(FORCE), which is not a good idea in production).




"Morris, Deborah"
<[login to unmask email] To: [login to unmask email]
.EDU> cc:
Sent by: DB2 Data Subject: DB2 Logging
Base Discussion
List
<[login to unmask email]
LASSOC.COM>


2002/12/19 02:03
PM
Please respond to
DB2 Data Base
Discussion List






Does anyone know whether you can turn DB2 logging off at the execution of a
DB2 Cobol batch program?

We have a conversion program that will insert some 9,000,000 + rows of data
to DB2 and we don't want
to worry about sizing the logs larger or possibly impacting our current
production environment.

We know that we could write these records to a flat file and load them to
DB2 using the DB2 load utility
with the "log no" option, but we were hoping to get this processing
achieved
with the cobol batch insert
process that has already been written.

Thank you in advance for any help you can give me with this.

Deborah A. Morris
Data Base Analyst/Programmer Specialist
UNC Health Care System
(919) 966-2197 (voice)



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Mae Bruce

Re: DB2 Logging
(in response to Eric Pearson)
It is still logging the inserts, but what I thought you were worrying about
was with infrequent commits on large insert/update activity, your active log
may fill up and not be able to switch (because you have uncommitted
transactions on this log) and you didn't want to have to make your log big
enough to handle all of these inserts.

Mae Bruce
State of Missouri
OA/Division of Information Services

-----Original Message-----
From: Morris, Deborah [mailto:[login to unmask email]
Sent: Thursday, December 19, 2002 2:39 PM
To: 'DB2 Data Base Discussion List'
Cc: 'Bruce, Mae'
Subject: RE: DB2 Logging


THe program does a commit after every 50 inserts. How does the commit
change the logging process?

-----Original Message-----
From: Bruce, Mae [mailto:[login to unmask email]
Sent: Thursday, December 19, 2002 3:18 PM
To: [login to unmask email]
Subject: Re: DB2 Logging



How about coding a commit in the program to happen every so many records?

Mae Bruce
State of Missouri
OA/Division of Information Services

-----Original Message-----
From: Morris, Deborah [mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Thursday, December 19, 2002 2:04 PM
To: [login to unmask email]
Subject: DB2 Logging


Does anyone know whether you can turn DB2 logging off at the execution of a
DB2 Cobol batch program?

We have a conversion program that will insert some 9,000,000 + rows of data
to DB2 and we don't want to worry about sizing the logs larger or possibly
impacting our current production environment.

We know that we could write these records to a flat file and load them to
DB2 using the DB2 load utility with the "log no" option, but we were hoping
to get this processing achieved with the cobol batch insert process that has
already been written.

Thank you in advance for any help you can give me with this.

Deborah A. Morris
Data Base Analyst/Programmer Specialist
UNC Health Care System
(919) 966-2197 (voice)


David S Waugh

Re: DB2 Logging
(in response to Rohn Solecki)
Deborah:

The short answer is "No", at least in current versions of DB2.

There was a rather lengthy discussion of this topic in Feb 2001, which ended with a good commentary from Roger Miller:
Item# Date Time Subject
022720 01/02/17 16:00 Re: logging

an excerpt from which is:
"My first question for people who want to turn off the log is, "Why?".
Another way of phrasing the question is telling me the objective, rather
than a shorthand for a specific technique. If the objective is to save
space on the log and archiving, there are a number of techniques for
doing that. If the objective is to improve overall performance or elapsed time by 10%, turning off logging generally cannot do that."

There were lots of other good comments from a variety of people on the list, which should give you a pretty good idea of the pros and cons of logging vs. not logging.

My own opinion (for what it's worth) is that while it would be nice to be able to use an already-written program to do these 9,000,000+ inserts (particularly if it does other nice things like providing an audit trail), LOAD LOG NO would be much, much, much more efficient for this and much less of an impact on your production subsystem (excepting the concurrency issues on the tablespace you're loading, of course).

HTH

David S. Waugh, NCW
DSW Consulting & Services


---------- "Morris, Deborah" <[login to unmask email]> writes:

From: "Morris, Deborah" <[login to unmask email]>
To: [login to unmask email]
Subject: DB2 Logging
Date: Thu, 19 Dec 2002 15:03:54 -0500

Does anyone know whether you can turn DB2 logging off at the execution of a DB2 Cobol batch program?

We have a conversion program that will insert some 9,000,000 + rows of data to DB2 and we don't want to worry about sizing the logs larger or possibly impacting our current production environment.

We know that we could write these records to a flat file and load them to DB2 using the DB2 load utility with the "log no" option, but we were hoping to get this processing achieved with the cobol batch insert
process that has already been written.

Thank you in advance for any help you can give me with this.

Deborah A. Morris
Data Base Analyst/Programmer Specialist
UNC Health Care System
(919) 966-2197 (voice)






Gary Fahrlander

Re: DB2 Logging
(in response to David S Waugh)
You could just use LOAD DATA REPLACE LOG NO NOCOPYPEND INTO TABLE
tablename.

Data is loaded, no logging, no copy pending flag.

works for me but I recommend you read the utility guide first.



Max Scarpa

Re: DB2 Logging
(in response to Gary Fahrlander)
Hi Deborah

I'd consider an utility for high number of insert or some other trick as
DB2 log/archive structure could be highly stressed if you do it via SQL (of
course if your DASD/TAPE availability is HUGE this is not a problem). I
think it's not so complicated.

For DB2 for OS/390 & Z/OS mainframe you can't avoid logging. I still have
the traces of Roger's (Miller) lashes on my back when I asked him to have
the possibility to avoid logging via SQL instruction (say via NOLOGGING
instruction).
Jim Teng was a little bit possibilist in may, he said they are thinking, in
a general revision of V Next, to try to avoid, under some circumstances,
logging, as ORACLE (sic!) do.

Let's wait V next, hoping mainframe (and mainframers) won't be extinct when
V Next (or X-files ?) will be available......

Max Scarpa
Seduced by the Dark Side of IT (very gorgeous side, however)



Roger Miller

Re: DB2 Logging
(in response to Max Scarpa)
I think I take many more lashes than I give. If the customer need is
really high, then use LOAD. If there is a long field, you can make the
column into a LOB with LOG NO and avoid the logging. Trying to use a LOB
with no logging to improve performance might make the problem worse. See
the discussion of LOB performance in the LOB red book, SG24-6571. One of
the complaints we get about LOBs is the difficulty of recovery without a
log. That's the price.

The big difference with COMMIT is not much without a log. You can't
recover anyway, and since there is no recovery, then there is no
concurrency. Maybe it's ok for a one time event, but if you want real
performance and very little log, then LOAD. If it's not worth performing
fast without logging, then logging must not be much of an issue.

One of the common misconceptions is that logging is a big difference in
performance. The maximum logging rate with dual logging on fast devices
today is around 13 megabytes a second (ESS with FICON). If you need faster
numbers, then striping can be performed. See the V7 Performance Topics and
V7 Selected Performance Topics redbooks (SG24-6129 & SG24-6894) for
measurements. If those rows are 100 bytes each, then there is a couple of
minutes of IO. There are some situations where it is possible (many
concurrent insert streams, with slow log disks), but this type of situation
would probably be less than a 5% difference.

Roger Miller



Edward Long

Re: DB2 Logging
(in response to Roger Miller)
Keep in mind that the IBM Load utility only supports
LOBS of 32K or less. More properly then it should be
lower case lOB.

If, like most lobs, yours grow past 32K, your
condemned to SQL.

--- Roger Miller <[login to unmask email]> wrote:
> I think I take many more lashes than I give. If the
> customer need is
> really high, then use LOAD. If there is a long
> field, you can make the
> column into a LOB with LOG NO and avoid the logging.
> Trying to use a LOB
> with no logging to improve performance might make
> the problem worse. See
> the discussion of LOB performance in the LOB red
> book, SG24-6571. One of
> the complaints we get about LOBs is the difficulty
> of recovery without a
> log. That's the price.
>
> The big difference with COMMIT is not much without a
> log. You can't
> recover anyway, and since there is no recovery, then
> there is no
> concurrency. Maybe it's ok for a one time event,
> but if you want real
> performance and very little log, then LOAD. If it's
> not worth performing
> fast without logging, then logging must not be much
> of an issue.
>
> One of the common misconceptions is that logging is
> a big difference in
> performance. The maximum logging rate with dual
> logging on fast devices
> today is around 13 megabytes a second (ESS with
> FICON). If you need faster
> numbers, then striping can be performed. See the V7
> Performance Topics and
> V7 Selected Performance Topics redbooks (SG24-6129 &
> SG24-6894) for
> measurements. If those rows are 100 bytes each,
> then there is a couple of
> minutes of IO. There are some situations where it
> is possible (many
> concurrent insert streams, with slow log disks), but
> this type of situation
> would probably be less than a 5% difference.
>
> Roger Miller
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://listserv.ylassoc.com. The owners of the list
> can be reached at
[login to unmask email]


=====
Edward Long