Hi,

This is a question for users of DBISAM.

We are using V3.3 of DBISAM and are having tremendous issues with corrupting
indexes across a number of our larger clients.  Just want to ask if ANY of
you out there have experienced indexing issues in the past, what they were,
and how you might have resolved them.

We dont have anywhere to start at the moment, and understand it could
possibly be a programming issue, but without any clues as to what to look
for, we are a bit lost.

Errors occur on a number of tables, but the most obvious is the table that
gets used and edited the most.  It can have thousands of records (probably
no more than 5000) and has maybe 11 indexes.  Usually errors arent obvious,
only when users complain that they are not getting the desired results when
doing filters and so on.

Any help appreciated

Thanks

Alex

"Al VAs" <deleteprefix_alex@favour.com.au> wrote in message
news:0AA5B92B-87F0-4AAF-8D5B-79DE58F36C1B@news.elevatesoft.com...
> Hi,
>
> This is a question for users of DBISAM.
>
> We are using V3.3 of DBISAM and are having tremendous issues with
> corrupting indexes across a number of our larger clients.  Just want to
> ask if ANY of you out there have experienced indexing issues in the past,
> what they were, and how you might have resolved them.
>
> We dont have anywhere to start at the moment, and understand it could
> possibly be a programming issue, but without any clues as to what to look
> for, we are a bit lost.
>
> Errors occur on a number of tables, but the most obvious is the table that
> gets used and edited the most.  It can have thousands of records (probably
> no more than 5000) and has maybe 11 indexes.  Usually errors arent
> obvious, only when users complain that they are not getting the desired
> results when doing filters and so on.
>

I'm experiencing the same thing, after making the mistake of upgrading to
3.30. The errors seem to be fairly random, and seem to occur when you are
trying to find a record. Today, for example, and 8710 error on a locate ate
about 4 hours of support time, in addition to aggravating my customer, who
does not seem to understand that my options are limited. According to the
documentation an 8710 is related to a gotocurrent, not a locate. Hard to
know where to go next, especially since the errors appear to be random.

Doing a repair on the table fixes the problem, until the next time. It's a
nightmare.

Robert

> Any help appreciated
>
> Thanks
>
> Alex
>

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote on Mon, 7 Aug 2006 22:03:03 -0400
>
>I'm experiencing the same thing, after making the mistake of upgrading to
>3.30. The errors seem to be fairly random, and seem to occur when you are
>trying to find a record. Today, for example, and 8710 error on a locate ate
>about 4 hours of support time, in addition to aggravating my customer, who
>does not seem to understand that my options are limited. According to the
>documentation an 8710 is related to a gotocurrent, not a locate. Hard to
>know where to go next, especially since the errors appear to be random.
>
>Doing a repair on the table fixes the problem, until the next time. It's a
>nightmare.
>

Interesting - I've been using v3.30 for months at 60 odd sites without this problem occurring at all.  (cross fingers, toes, knees, eyes ...)


Most of my sites are file-server, but 3 or 4 of the bigger ones are C/S with quite large tables - 100's of 1000's of records.  I don't have a large number of indices on my tables - 5 at the most and that happens to be on my biggest table - so that might be the reason that I'm OK (touching wood everywhere).

No help at all, I guess!

Jeff

--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Jeff,

With your biggest table, what is the quantity of changes being made.  Is it
big because of time or are there many additions/changes amongst multiple
users?  Just trying to ascertain your scenario as a comparison.

Robert,

I think you might be right.  We have been using DBISAM for one of our other
products for a long time, but it was always on V3.23.  Saying that it is
nowhere near the volume of entries as this particular product which we have
only converted a few months ago.  Seems though that the increase in
incidence could be related to moving it to V3.30. We recently updated to
3.30 which is supposed to resolve a critical error, but seems to have
created a myriad of indexing issues.  Are you using client/server by the way
Robert?   Also why did you move to V3.30?  Where did you move from?  One
option I guess is to move back to an older version.

Unfortunately the only other option we have is to upgrade to V4 as DBISAM
support do not have the time to evaluate this further.  Might have to begin
thinking about another database since the upgrade requires SQL changes,
ReportBuilder SQL changes and therefore a full testing schedule.  Really not
in a happy mood right now, especially since interest in our product has
taken off.

Alex


"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:51749662-0459-4401-B112-ED094DD2B7D9@news.elevatesoft.com...
> "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote on Mon, 7 Aug 2006
> 22:03:03 -0400
>>
>>I'm experiencing the same thing, after making the mistake of upgrading to
>>3.30. The errors seem to be fairly random, and seem to occur when you are
>>trying to find a record. Today, for example, and 8710 error on a locate
>>ate
>>about 4 hours of support time, in addition to aggravating my customer, who
>>does not seem to understand that my options are limited. According to the
>>documentation an 8710 is related to a gotocurrent, not a locate. Hard to
>>know where to go next, especially since the errors appear to be random.
>>
>>Doing a repair on the table fixes the problem, until the next time. It's a
>>nightmare.
>>
>
> Interesting - I've been using v3.30 for months at 60 odd sites without
> this problem occurring at all. (cross fingers, toes, knees, eyes ...)
>
>
> Most of my sites are file-server, but 3 or 4 of the bigger ones are C/S
> with quite large tables - 100's of 1000's of records. I don't have a large
> number of indices on my tables - 5 at the most and that happens to be on
> my biggest table - so that might be the reason that I'm OK (touching wood
> everywhere).
>
> No help at all, I guess!
>
> Jeff
>
> --
> Jeff Cook
> Aspect Systems Ltd
> Phone: +64-9-424 5388
> Skype: jeffcooknz
> www.aspect.co.nz
>
>
>
>

Jeff Cook <jeffc@aspect.co.nz> wrote on Tue, 8 Aug 2006 14:59:37 +1200

> I don't have a large number of indices on my tables - 5 at the most and that happens to be on my biggest table

. . . which is also the busiest table - the main transaction table with lots of insert and edits..

Hi,

Does anyone use pessimistic locking on their tables?   We do, very important
for this table, maybe that is an issue?  Also Jeff would you be kind enough
to list the definition of your indexes?  Are they multi-field, compressions
etc?

Thanks

Alex

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:1D65E026-1F38-419B-AA60-C6C448FD373C@news.elevatesoft.com...
> Jeff Cook <jeffc@aspect.co.nz> wrote on Tue, 8 Aug 2006 14:59:37 +1200
>
>> I don't have a large number of indices on my tables - 5 at the most and
>> that happens to be on my biggest table
>
> . . which is also the busiest table - the main transaction table with lots
> of insert and edits..
>
>

"Al VAs" <deleteprefix_alex@favour.com.au> wrote on Tue, 8 Aug 2006 13:38:03 +1000

>Hi,
>
>Does anyone use pessimistic locking on their tables? We do, very important
>for this table, maybe that is an issue? Also Jeff would you be kind enough
>to list the definition of your indexes? Are they multi-field, compressions
>etc?
>
>Thanks
>
>Alex

Alex


SQL to create the table beneath my signature.  

In answer to your other questions - biggest table getting hit with 5,000+ inserts per month - multiple users.  Many of the records are imported from bank files, so they hit the table in a big lump.  Each insert can have a cascading effect of causing 0-20+ edits of records in the same table.  (This is because we maintain a running balance in real time for each landlord and recalculate the balance for the current period after each insert or edit - however this does not affect any key fields, so hopefully DBISAM doesn't even look at the indices.)

Pessimistic locking?  Duh ... that's the Windows environment isn't it?  Showing ignorance here!  If so, then it will be set to whatever the network man likes at each of our 60 odd sites and we have had no input as to what it should be.

Cheers

Jeff
==================
DROP TABLE IF EXISTS "landlordtrans";

CREATE TABLE IF NOT EXISTS "landlordtrans"
(
  "LandlordCode" VARCHAR(4) NOT NULL,
  "Period" INTEGER,
  "Sequence" VARCHAR(2) DEFAULT 10,
  "TransDate" DATE,
  "TransNo" VARCHAR(6),
  "PropertyCode" VARCHAR(4),
  "TransSeq" INTEGER DEFAULT 0,
  "TransType" VARCHAR(8),
  "Net" MONEY NOT NULL DEFAULT 0,
  "Amount" MONEY NOT NULL DEFAULT 0,
  "FeeOverride" BOOLEAN DEFAULT False,
  "Rent" MONEY NOT NULL DEFAULT 0,
  "ManagementFee" MONEY NOT NULL DEFAULT 0,
  "GST" MONEY NOT NULL DEFAULT 0,
  "Debt" MONEY NOT NULL DEFAULT 0,
  "Balance" MONEY NOT NULL DEFAULT 0,
  "PaymentType" VARCHAR(4),
  "Reference" VARCHAR(20),
  "AccountNo" VARCHAR(19),
  "Payee" VARCHAR(20),
  "TransText" VARCHAR(30),
  "TransMemo" MEMO,
  "TenantCode" VARCHAR(4),
  "Confirmed" BOOLEAN DEFAULT False,
  "Session" INTEGER DEFAULT 0,
  "CashAmount" MONEY NOT NULL DEFAULT 0,
  "ChqAmount" MONEY NOT NULL DEFAULT 0,
  "TransferAmount" MONEY NOT NULL DEFAULT 0,
  "TransferType" VARCHAR(6),
  "Bank" VARCHAR(8),
  "Branch" VARCHAR(16),
  "ChequeNo" VARCHAR(6),
  "PayeeReference" VARCHAR(12),
  "PayeeAnalysis" VARCHAR(12),
  "PayeeParticulars" VARCHAR(12),
PRIMARY KEY ("LandlordCode","Period","Sequence","TransDate","TransNo","PropertyCode","TransSeq") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
);

CREATE INDEX IF NOT EXISTS "LSessionNo" ON "landlordtrans" ("Session","TransNo") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "LTransNoIndex" ON "landlordtrans" ("TransNo","TransSeq") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "LTransTypeIndex" ON "landlordtrans" ("Period","TransType") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "LPropertyIndex" ON "landlordtrans" ("PropertyCode","TransDate") COMPRESS FULL;
--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Appreciate your time Jeff,

Thanks for that

Alex

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:C1A20076-5211-40BE-998E-D6659BF6B996@news.elevatesoft.com...
> "Al VAs" <deleteprefix_alex@favour.com.au> wrote on Tue, 8 Aug 2006
> 13:38:03 +1000
>
>>Hi,
>>
>>Does anyone use pessimistic locking on their tables? We do, very important
>>for this table, maybe that is an issue? Also Jeff would you be kind enough
>>to list the definition of your indexes? Are they multi-field, compressions
>>etc?
>>
>>Thanks
>>
>>Alex
>
> Alex
>
>
> SQL to create the table beneath my signature.
>
> In answer to your other questions - biggest table getting hit with 5,000+
> inserts per month - multiple users. Many of the records are imported from
> bank files, so they hit the table in a big lump. Each insert can have a
> cascading effect of causing 0-20+ edits of records in the same table.
> (This is because we maintain a running balance in real time for each
> landlord and recalculate the balance for the current period after each
> insert or edit - however this does not affect any key fields, so hopefully
> DBISAM doesn't even look at the indices.)
>
> Pessimistic locking? Duh ... that's the Windows environment isn't it?
> Showing ignorance here! If so, then it will be set to whatever the network
> man likes at each of our 60 odd sites and we have had no input as to what
> it should be.
>
> Cheers
>
> Jeff
> ==================
> DROP TABLE IF EXISTS "landlordtrans";
>
> CREATE TABLE IF NOT EXISTS "landlordtrans"
> (
> "LandlordCode" VARCHAR(4) NOT NULL,
> "Period" INTEGER,
> "Sequence" VARCHAR(2) DEFAULT 10,
> "TransDate" DATE,
> "TransNo" VARCHAR(6),
> "PropertyCode" VARCHAR(4),
> "TransSeq" INTEGER DEFAULT 0,
> "TransType" VARCHAR(8),
> "Net" MONEY NOT NULL DEFAULT 0,
> "Amount" MONEY NOT NULL DEFAULT 0,
> "FeeOverride" BOOLEAN DEFAULT False,
> "Rent" MONEY NOT NULL DEFAULT 0,
> "ManagementFee" MONEY NOT NULL DEFAULT 0,
> "GST" MONEY NOT NULL DEFAULT 0,
> "Debt" MONEY NOT NULL DEFAULT 0,
> "Balance" MONEY NOT NULL DEFAULT 0,
> "PaymentType" VARCHAR(4),
> "Reference" VARCHAR(20),
> "AccountNo" VARCHAR(19),
> "Payee" VARCHAR(20),
> "TransText" VARCHAR(30),
> "TransMemo" MEMO,
> "TenantCode" VARCHAR(4),
> "Confirmed" BOOLEAN DEFAULT False,
> "Session" INTEGER DEFAULT 0,
> "CashAmount" MONEY NOT NULL DEFAULT 0,
> "ChqAmount" MONEY NOT NULL DEFAULT 0,
> "TransferAmount" MONEY NOT NULL DEFAULT 0,
> "TransferType" VARCHAR(6),
> "Bank" VARCHAR(8),
> "Branch" VARCHAR(16),
> "ChequeNo" VARCHAR(6),
> "PayeeReference" VARCHAR(12),
> "PayeeAnalysis" VARCHAR(12),
> "PayeeParticulars" VARCHAR(12),
> PRIMARY KEY
> ("LandlordCode","Period","Sequence","TransDate","TransNo","PropertyCode","TransSeq")
> COMPRESS FULL
> LANGUAGE "ANSI Standard" SORT "Default Order"
> );
>
> CREATE INDEX IF NOT EXISTS "LSessionNo" ON "landlordtrans"
> ("Session","TransNo") COMPRESS FULL;
> CREATE INDEX IF NOT EXISTS "LTransNoIndex" ON "landlordtrans"
> ("TransNo","TransSeq") COMPRESS FULL;
> CREATE INDEX IF NOT EXISTS "LTransTypeIndex" ON "landlordtrans"
> ("Period","TransType") COMPRESS FULL;
> CREATE INDEX IF NOT EXISTS "LPropertyIndex" ON "landlordtrans"
> ("PropertyCode","TransDate") COMPRESS FULL;
> --
> Jeff Cook
> Aspect Systems Ltd
> Phone: +64-9-424 5388
> Skype: jeffcooknz
> www.aspect.co.nz
>
>
>
>

Al


What happens if you add a flushbuffers after each post. I know it will slow things down but it might make things work.

Also, this is going from memory, and probably not relevant, when I was running TMaN under V3 I had to reduce the number of background threads down to 1 from 4.

Memory says that was pre 3.30 and it didn't exactly "cure" the problem just meant that things were running slow enough the system could keep up.

Roy Lambert

"Al VAs" <deleteprefix_alex@favour.com.au> wrote in message
news:2EC3A906-5E65-47A1-B545-8F656FD8E753@news.elevatesoft.com...
>
> Robert,
>
> I think you might be right.  We have been using DBISAM for one of our
> other products for a long time, but it was always on V3.23.  Saying that
> it is nowhere near the volume of entries as this particular product which
> we have only converted a few months ago.  Seems though that the increase
> in incidence could be related to moving it to V3.30. We recently updated
> to 3.30 which is supposed to resolve a critical error, but seems to have
> created a myriad of indexing issues.  Are you using client/server by the
> way Robert?   Also why did you move to V3.30?  Where did you move from?
> One option I guess is to move back to an older version.
>

I'm not using C/S. Upgraded from 3.27 to 3.30, and honestly did not think it
was a big deal, more a matter of tidying up and moving to the most current
release of the database. Run into some SQL differences, but fixed those and
released the programs. Then the s... hit the fan.

Problems seem to center in table updates within transactions. My
applications do a lot of in memory processing, then start a transaction and
update all disk tables. This of course could be happening in several
terminals at the same time, hitting the same tables. The transactions don't
fail, all the updates are apparently good, but then doing other processing
we find that the index is corrupted.

Robert