Version IDS 10:

Application throws SQL -458 and -ISAM 12204 right at the point when it
tries to fetch a row from the cursor. My question is how could it fail
at this point, FETCH and selects are not even written to the logical
logs.

On Feb 15, 5:17 pm, mohitanch...@gmail.com wrote:
> Version IDS 10:
>
> Application throws SQL -458 and -ISAM 12204 right at the point when it
> tries to fetch a row from the cursor. My question is how could it fail
> at this point, FETCH and selects are not even written to the logical
> logs.

I am modifying my post to ask a related question:

1. Application throws SQL -458 and -ISAM 12204 right at the point when
it
tries to fetch a row from the cursor. My question is how could it fail
at this point, FETCH and selects are not even written to the logical
logs.

2. What happens with following configuration:
- 100 logical log files with high watermark value of 50/50
- There are 2 db processes running
- One process is already using 40 logical files
So based on above criteria:
A. Would informix declare long transaction for second process by
computing
logical log % = (number of logs in use by second
process) / (100-40) . If this % value is above high water mark value
then declare it as long transaction or would it compute (number of
logs in use by second process)/100. I am trying to understand what
happens when a transaction is declared as long transaction when there
are other processes also using logical logs.

3. Also, I am trying to understand how the values are calculated by
Informix because I think these logical logs are shared between the
processes. How would it know or calculate ? Is it based on number of
bytes being used in the logical logs ?

I might sound confusing as I am trying to put different thoughts that
I have at this point. Please let me know if I can clarify my question.

mohitanchlia@gmail.com wrote:

> On Feb 15, 5:17 pm, mohitanch...@gmail.com wrote:
>> Version IDS 10:
>>
>> Application throws SQL -458 and -ISAM 12204 right at the point when it
>> tries to fetch a row from the cursor. My question is how could it fail
>> at this point, FETCH and selects are not even written to the logical
>> logs.

You need to get yourself some training - please.

> I am modifying my post to ask a related question:
>
> 1. Application throws SQL -458 and -ISAM 12204 right at the point when
> it
> tries to fetch a row from the cursor. My question is how could it fail
> at this point, FETCH and selects are not even written to the logical
> logs.

It will fail if its BEGIN WORK record is far enough back in the logical
logs that it now makes this transaction into a long transaction.

> 2. What happens with following configuration:
> - 100 logical log files with high watermark value of 50/50

Do you mean LTXHWM and LTXEHWM are both set to 50? That would not be
all that sensible.

> - There are 2 db processes running
> - One process is already using 40 logical files
> So based on above criteria:
> A. Would informix declare long transaction for second process by
> computing
> logical log % = (number of logs in use by second
> process) / (100-40) . If this % value is above high water mark value
> then declare it as long transaction or would it compute (number of
> logs in use by second process)/100. I am trying to understand what
> happens when a transaction is declared as long transaction when there
> are other processes also using logical logs.

The logical logs are used by all sessions - it doesn't matter which
process is using up the space. If the oldest active BEGIN WORK is in
too old a logical log, then that transaction is declared to be the LTX
and is rolled back, writing CLRs (compensation log records) to record
that the TX is undone.

> 3. Also, I am trying to understand how the values are calculated by
> Informix because I think these logical logs are shared between the
> processes. How would it know or calculate ? Is it based on number of
> bytes being used in the logical logs ?

No - the calculation is based on the number of logical logs. So, with
100 logs (of the same size) each log is 1% of the space. When the
oldest TX starts 50 logs back, at the switch to log 51, the oldest TX is
declared to be an LTX. If you had fewer logs, the switching is less
accurate.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/

publictimestamp.org/ptb/PTB-2533 whirlpool 2008-02-16 03:00:08
D7715B97926246C040263094AEAA2A242CCB22273023B8F0D5 E7EB5591EC103DF2076A
B91EB383896C92D82240A5BA7B3DF0C93E28BB73B52AC7726C 2C1ADED

On Feb 15, 9:38*pm, Jonathan Leffler <jleff...@earthlink.net> wrote:
> mohitanch...@gmail.com wrote:
> > On Feb 15, 5:17 pm, mohitanch...@gmail.com wrote:
> >> Version IDS 10:
>
> >> Application throws SQL -458 and -ISAM 12204 right at the point when it
> >> tries to fetch a row from the cursor. My question is how could it fail
> >> at this point, FETCH and selects are not even written to the logical
> >> logs.
>
> You need to get yourself some training - please.
>
> > I am modifying my post to ask a related question:
>
> > 1. Application throws SQL -458 and -ISAM 12204 right at the point when
> > it
> > tries to fetch a row from the cursor. My question is how could it fail
> > at this point, FETCH and selects are not even written to the logical
> > logs.
>
> It will fail if its BEGIN WORK record is far enough back in the logical
> logs that it now makes this transaction into a long transaction.
>
> > 2. What happens with following configuration:
> > * * * *- 100 logical log files with high watermark value of 50/50
>
> Do you mean LTXHWM and LTXEHWM are both set to 50? *That would not be
> all that sensible.
>
> > * * * *- There are 2 db processes running
> > * * * *- One process is already using 40 logical files
> > * * * So based on above criteria:
> > * * * A. Wouldinformixdeclare long transaction for second process by
> > computing
> > * * * * * * * *logical log % = (number of logs in use by second
> > process) / (100-40) . If this % value is above high water mark value
> > then declare it as long transaction or would it compute (number of
> > logs in use by second process)/100. I am trying to understand what
> > happens when a transaction is declared as long transaction when there
> > are other processes also using logical logs.
>
> The logical logs are used by all sessions - it doesn't matter which
> process is using up the space. *If the oldest active BEGIN WORK is in
> too old a logical log, then that transaction is declared to be the LTX
> and is rolled back, writing CLRs (compensation log records) to record
> that the TX is undone.
>
> > 3. Also, I am trying to understand how the values are calculated by
> >Informixbecause I think these logical logs are shared between the
> > processes. How would it know or calculate ? Is it based on number of
> > bytes being used in the logical logs ?
>
> No - the calculation is based on the number of logical logs. *So, with
> 100 logs (of the same size) each log is 1% of the space. *When the
> oldest TX starts 50 logs back, at the switch to log 51, the oldest TX is
> declared to be an LTX. *If you had fewer logs, the switching is less
> accurate.
>
> --
> Jonathan Leffler * * * * * * * * * #include <disclaimer.h>
> Email: jleff...@earthlink.net, jleff...@us.ibm.com
> Guardian of DBD::Informixv2007.0914 --http://dbi.perl.org/
>
> publictimestamp.org/ptb/PTB-2533 whirlpool 2008-02-16 03:00:08
> D7715B97926246C040263094AEAA2A242CCB22273023B8F0D5 E7EB5591EC103DF2076A
> B91EB383896C92D82240A5BA7B3DF0C93E28BB73B52AC7726C 2C1ADED

Thanks for the explanation. I looked at the IBM site and didn't find
good explanation and examples online. I also tried looking at other
sources but those are mostly copy and paste from IBM's site. If you
have any resource where I can read more about it in detail would be
helpful.

I have one more question:

When DYNAMIC_LOG is set 2, does it starts to add more logical logs
dynamically when next logical log in the sequence has open
transaction. Does this become active only during roll back and next
logical log has an open transaction or could it also become active if
all the logical logs need backup ?

mohitanchlia@gmail.com wrote:
> On Feb 15, 9:38 pm, Jonathan Leffler <jleff...@earthlink.net> wrote:
>> mohitanch...@gmail.com wrote:
>>> On Feb 15, 5:17 pm, mohitanch...@gmail.com wrote:
>>>> Version IDS 10:
>>>> Application throws SQL -458 and -ISAM 12204 right at the point when it
>>>> tries to fetch a row from the cursor. My question is how could it fail
>>>> at this point, FETCH and selects are not even written to the logical
>>>> logs.
>> You need to get yourself some training - please.
>>
>>> I am modifying my post to ask a related question:
>>> 1. Application throws SQL -458 and -ISAM 12204 right at the point when
>>> it
>>> tries to fetch a row from the cursor. My question is how could it fail
>>> at this point, FETCH and selects are not even written to the logical
>>> logs.
>> It will fail if its BEGIN WORK record is far enough back in the logical
>> logs that it now makes this transaction into a long transaction.
>>
>>> 2. What happens with following configuration:
>>> - 100 logical log files with high watermark value of 50/50
>> Do you mean LTXHWM and LTXEHWM are both set to 50? That would not be
>> all that sensible.
>>
>>> - There are 2 db processes running
>>> - One process is already using 40 logical files
>>> So based on above criteria:
>>> A. Wouldinformixdeclare long transaction for second process by
>>> computing
>>> logical log % = (number of logs in use by second
>>> process) / (100-40) . If this % value is above high water mark value
>>> then declare it as long transaction or would it compute (number of
>>> logs in use by second process)/100. I am trying to understand what
>>> happens when a transaction is declared as long transaction when there
>>> are other processes also using logical logs.
>> The logical logs are used by all sessions - it doesn't matter which
>> process is using up the space. If the oldest active BEGIN WORK is in
>> too old a logical log, then that transaction is declared to be the LTX
>> and is rolled back, writing CLRs (compensation log records) to record
>> that the TX is undone.
>>
>>> 3. Also, I am trying to understand how the values are calculated by
>>> Informixbecause I think these logical logs are shared between the
>>> processes. How would it know or calculate ? Is it based on number of
>>> bytes being used in the logical logs ?
>> No - the calculation is based on the number of logical logs. So, with
>> 100 logs (of the same size) each log is 1% of the space. When the
>> oldest TX starts 50 logs back, at the switch to log 51, the oldest TX is
>> declared to be an LTX. If you had fewer logs, the switching is less
>> accurate.
>>
>> --
>> Jonathan Leffler #include <disclaimer.h>
>> Email: jleff...@earthlink.net, jleff...@us.ibm.com
>> Guardian of DBD::Informixv2007.0914 --http://dbi.perl.org/
>>
>> publictimestamp.org/ptb/PTB-2533 whirlpool 2008-02-16 03:00:08
>> D7715B97926246C040263094AEAA2A242CCB22273023B8F0D5 E7EB5591EC103DF2076A
>> B91EB383896C92D82240A5BA7B3DF0C93E28BB73B52AC7726C 2C1ADED
>
> Thanks for the explanation. I looked at the IBM site and didn't find
> good explanation and examples online. I also tried looking at other
> sources but those are mostly copy and paste from IBM's site. If you
> have any resource where I can read more about it in detail would be
> helpful.
>
> I have one more question:
>
> When DYNAMIC_LOG is set 2, does it starts to add more logical logs
> dynamically when next logical log in the sequence has open
> transaction. Does this become active only during roll back and next
> logical log has an open transaction or could it also become active if
> all the logical logs need backup ?

The adding of more logs will only be triggered when a LTX is detected.
At that point it will starting adding logs to the dbspace that the last
logical log was created in. If that dbspace fills then they will be
written to rootdbs (in my experience) until rootdbs fills. The engine
will keep adding logical logs until the transaction completes or you run
out of dbspace.

Paul Watson
Tel: +1 913-400-2620
Mob: +1 913-387-7529
Web: www.oninit.com

Failure is not as frightening as regret.
If you want to improve, be content to be thought foolish and stupid.

> I have one more question:
>
> When DYNAMIC_LOG is set 2, does it starts to add more logical logs
> dynamically when next logical log in the sequence has open
> transaction. Does this become active only during roll back and next
> logical log has an open transaction or could it also become active if
> all the logical logs need backup ?


It is used only to avoid database server hangs from a long transaction
rollback.

Nilesh.