why lsn is in a constant range even in circular logging database? Why not allow to reset lsn after certain activities?

Harishkumar .Pathangay

why lsn is in a constant range even in circular logging database? Why not allow to reset lsn after certain activities?

Hi, the question is why lsn is in a constant range. Even in circular logging database can run of lsn space. Why the requirement to have a static range. Why not allow to reset lsn after certain activities. Like after taking a backup of circular logged database there is no necessary to individually identify transactions. The entire backup is there. Why not allow to reset lsn and continue database transactions. Thanks. Harish p

Roy Boxwell

why lsn is in a constant range even in circular logging database? Why not allow to reset lsn after c
(in response to Harishkumar .Pathangay)
Well you can of course do this... Use DSN1COPY/REPAIR and reset all of your data... However, why bother? Are you worried that the LRSN will run out on you?

I like an always changing RBA/LRSN as it makes life so easy.



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, Ulf Heinrich



From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Monday, September 10, 2018 9:12 AM
To: [login to unmask email]
Subject: [DB2-L] - why lsn is in a constant range even in circular logging database? Why not allow to reset lsn after certain activities?



Hi, the question is why lsn is in a constant range. Even in circular logging database can run of lsn space. Why the requirement to have a static range. Why not allow to reset lsn after certain activities. Like after taking a backup of circular logged database there is no necessary to individually identify transactions. The entire backup is there. Why not allow to reset lsn and continue database transactions. Thanks. Harish p



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

Attachments

  • smime.p7s (5.1k)

Harishkumar .Pathangay

why lsn is in a constant range even in circular loggingdatabase? Why not allow to reset lsn after ce
(in response to Roy Boxwell)
I am extremely sorry. I did not mention it as LUW platform. Not on Mainframes or AS/400 platform.
I missed to provide that information. My Bad.
I understand that running out of LSN is a rare occurrence. But what is the issue in allowing to reset when expired or when you are nearing the last 1000 LSNs.

Thanks,
Harish P

Sent from Mail for Windows 10

From: Boxwell, Roy
Sent: 10 September 2018 13:17
To: [login to unmask email]
Subject: [DB2-L] - RE: why lsn is in a constant range even in circular loggingdatabase? Why not allow to reset lsn after certain activities?

Well you can of course do this... Use DSN1COPY/REPAIR and reset all of your data... However, why bother? Are you worried that the LRSN will run out on you?
I like an always changing RBA/LRSN as it makes life so easy.

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]
http://www.seg.de

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

From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Monday, September 10, 2018 9:12 AM
To: [login to unmask email]
Subject: [DB2-L] - why lsn is in a constant range even in circular logging database? Why not allow to reset lsn after certain activities?

Hi, the question is why lsn is in a constant range. Even in circular logging database can run of lsn space. Why the requirement to have a static range. Why not allow to reset lsn after certain activities. Like after taking a backup of circular logged database there is no necessary to individually identify transactions. The entire backup is there. Why not allow to reset lsn and continue database transactions. Thanks. Harish p


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Faster data refresh is here! The long waits and babysitting of unload/load jobs is over. Contact
ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data provisioning.See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


Attachments

  • 96ACED03DD6949CBBDEF855B25EE5666.png (<1k)

Ian Bjorhovde

why lsn is in a constant range even in circular logging database? Why not allow to reset lsn after c
(in response to Harishkumar .Pathangay)
Harish,

Are you actually worried about running out of LSNs? With an 8-byte LSN
(Db2 LUW has used this since 9.5), you'd have to write 18.4 exabytes of log
data before you run out of LSNs. As I wrote in a blog post a while ago, to
put this into perspective, if your database were writing to the transaction
log at an unimaginable, constant rate of 500 MB/s – 128,000 log
pages/second – it would take over 1,115 years to reach the LSN limit.



On Mon, Sep 10, 2018 at 12:12 AM Harishkumar .Pathangay <
[login to unmask email]> wrote:

> Hi, the question is why lsn is in a constant range. Even in circular
> logging database can run of lsn space. Why the requirement to have a static
> range. Why not allow to reset lsn after certain activities. Like after
> taking a backup of circular logged database there is no necessary to
> individually identify transactions. The entire backup is there. Why not
> allow to reset lsn and continue database transactions. Thanks. Harish p
>
> -----End Original Message-----
>

Harishkumar .Pathangay

why lsn is in a constant range even in circular logging database? Why not allow to reset lsn after c
(in response to Ian Bjorhovde)
Hi Ian,
It is not the fear of running out of LSNs but the flexibility to reset in a
worst case event.
You need to export and import entire database on a table to table basis.
Really painful and stressful thing to do. Allow me to reset at least when
it is in the last range may be like the last 1G of LSN space or so.
Why it is rigid? Any thoughts on that?

thanks,
harish pathangay


On Tue, Sep 11, 2018 at 12:46 AM Ian Bjorhovde <[login to unmask email]> wrote:

> Harish,
>
> Are you actually worried about running out of LSNs? With an 8-byte LSN
> (Db2 LUW has used this since 9.5), you'd have to write 18.4 exabytes of log
> data before you run out of LSNs. As I wrote in a blog post a while ago, to
> put this into perspective, if your database were writing to the transaction
> log at an unimaginable, constant rate of 500 MB/s – 128,000 log
> pages/second – it would take over 1,115 years to reach the LSN limit.
>
>
>
> On Mon, Sep 10, 2018 at 12:12 AM Harishkumar .Pathangay <
> [login to unmask email]> wrote:
>
>> Hi, the question is why lsn is in a constant range. Even in circular
>> logging database can run of lsn space. Why the requirement to have a static
>> range. Why not allow to reset lsn after certain activities. Like after
>> taking a backup of circular logged database there is no necessary to
>> individually identify transactions. The entire backup is there. Why not
>> allow to reset lsn and continue database transactions. Thanks. Harish p
>>
>> -----End Original Message-----
>


--
Thanks,
Harish P

Jeff Goss

RE: why lsn is in a constant range even in circular logging database? Why not allow to reset lsn after c
(in response to Harishkumar .Pathangay)



In Reply to Harishkumar .Pathangay:

Hi Ian,
It is not the fear of running out of LSNs but the flexibility to reset in a
worst case event.
You need to export and import entire database on a table to table basis.
Really painful and stressful thing to do. Allow me to reset at least when
it is in the last range may be like the last 1G of LSN space or so.
Why it is rigid? Any thoughts on that?
--
Thanks,
Harish P

Harish, to reset the LSNs, we would have to go over every type of object in the database that has LSN stamps, most notably the lifeLSNs on many database objects.  We did in fact have a reset tool written for the pre-v95 6 byte LSN databases for customers that were approaching that limit.  I do not believe this has been kept up to date since the move to 8 byte LSNs, but it was also a very time consuming operation.  Even then I think there were only a couple of customers that hit the old 6 byte limit - one purely on the number of transaction they had against the database, and one other that did not ACTIVATE their database and ended up with a large number of truncated log files (causing wasted LSN space) from connect reset/connect/connect reset cycles. That's one good tip - always activate your database explicitly to avoid these scenarios.

Without a full reset tool of some kind even in a non-recoverable circular logging only database that there would be issues if the database was ever brought down unexpectedly and thus left in an inconsistent state.  The lifeLSNs are generally used to avoid REDO actions which don't pertain to the current life of the object.  So during the forward phase of crash recovery we might avoid replaying an action, and then UNDO could fail.  Say for instance that a row that was inserted during runtime never hit disk although the log record did.  UNDO then tries to delete that row, and ends up failing crash recovery.  Also, at any point you can convert your circular logging database into a recoverable database, and at that point you could have any number of objects existing in the future wrt new log records you are writing (without a new version of the reset tool being written and used that is). 

Also I'm not quite clear what you mean by "It is not the fear of running out of LSNs but the flexibility to reset in a
worst case event."  It sounds like the worst case event is running out of log records, no?

Thanks,

Jeff

Harishkumar .Pathangay

why lsn is in a constant range even in circular logging database? Why not allow to reset lsn after c
(in response to Jeff Goss)
Hi Jeff,
Thanks very much for detailed explanation. sincerely speaking LSNs are much
more than i thought.
the way i understood LSN was it is not a log record identifier but log
space identifier. For example, i did a test case where One Insert Log
Record roughly 3K size in row length consumed 3 LSN values[monitor current
lsn before and after]. After this test case only i was starting to think
that it is not that much unrealistic to hit that limit when i compare with
just number of log records. Even a delete log record can consume 5 LSN
values [size dependent not log records dependent]. It can even introduce a
limit on database growth as well. I am learning by test cases only.
Information on LSNs are pretty scarce. Please let me know if my
understanding is correct. after reading your reply i am starting to think
on why database needs to operate on a finite amount of LSN space. What is
the purpose of starting with a finite LSN space?

As an Administrator i am also wondering that should i need to have a 40,000
feet view.
I am a child in the universe of db2 like gazing the stars. I am now worried
about the resolution of telescope that i should be looking through the sky.

Very helpful inputs, again thanks for taking the time and responding to my
queries patiently.

thanks,
harish pathnagay

On Tue, Sep 11, 2018 at 11:49 PM Jeff Goss <[login to unmask email]> wrote:

>
>
> In Reply to Harishkumar .Pathangay:
>
> Hi Ian,
> It is not the fear of running out of LSNs but the flexibility to reset in a
> worst case event.
> You need to export and import entire database on a table to table basis.
> Really painful and stressful thing to do. Allow me to reset at least when
> it is in the last range may be like the last 1G of LSN space or so.
> Why it is rigid? Any thoughts on that?
> --
> Thanks,
> Harish P
>
> Harish, to reset the LSNs, we would have to go over every type of object
> in the database that has LSN stamps, most notably the lifeLSNs on many
> database objects. We did in fact have a reset tool written for the pre-v95
> 6 byte LSN databases for customers that were approaching that limit. I do
> not believe this has been kept up to date since the move to 8 byte LSNs,
> but it was also a very time consuming operation. Even then I think there
> were only a couple of customers that hit the old 6 byte limit - one purely
> on the number of transaction they had against the database, and one other
> that did not ACTIVATE their database and ended up with a large number of
> truncated log files (causing wasted LSN space) from connect
> reset/connect/connect reset cycles. That's one good tip - always activate
> your database explicitly to avoid these scenarios.
>
> Without a full reset tool of some kind even in a non-recoverable circular
> logging only database that there would be issues if the database was ever
> brought down unexpectedly and thus left in an inconsistent state. The
> lifeLSNs are generally used to avoid REDO actions which don't pertain to
> the current life of the object. So during the forward phase of crash
> recovery we might avoid replaying an action, and then UNDO could fail. Say
> for instance that a row that was inserted during runtime never hit disk
> although the log record did. UNDO then tries to delete that row, and ends
> up failing crash recovery. Also, at any point you can convert your
> circular logging database into a recoverable database, and at that point
> you could have any number of objects existing in the future wrt new log
> records you are writing (without a new version of the reset tool being
> written and used that is).
>
> Also I'm not quite clear what you mean by "It is not the fear of running
> out of LSNs but the flexibility to reset in a
> worst case event." It sounds like the worst case event is running out of
> log records, no?
>
> Thanks,
>
> Jeff
>
> -----End Original Message-----
>


--
Thanks,
Harish P

Harishkumar .Pathangay

RE: why lsn is in a constant range even in circular logging database? Why not allow to reset lsn after c
(in response to Harishkumar .Pathangay)

Hi Jeff,

Have they changed behavior of LSN in 11.1 and later releases.

In 9.7, if I import one record with 1MB blob object, it consumes 1MB of LSN Space - the hexadecimal for 1MB, even though it is a single record.

In 11.1, if I import the same record [1 MB blob object], it consumes only 62 LSN hexadecimal, but LSO will be of 1MB.

Also, connect/disconnect recycles without explicit activation of database is not consuming LSN values.

these are significant changes, but I am not able to find any documentation in info center highlighting such changes.

my test cases definitely are valid, I did like 10 times with same results.

Any Help on that ? I am thinking the way in which the LSN are consumed is drastically changed between releases 9.7 and 11.1. Any documentation pointing to that for assertion will be of great help. 

thanks,

Harish Pathangay

Harishkumar .Pathangay

RE: why lsn is in a constant range even in circular logging database? Why not allow to reset lsn after c
(in response to Harishkumar .Pathangay)

please consider the thread closed.

thanks for the help.

thanks again,

harish pathangay