Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 16 total |
Index Issues V3.30 - Question for users of DBISAM |
Mon, Aug 7 2006 7:29 PM | Permanent Link |
"Al VAs" | 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 |
Mon, Aug 7 2006 10:06 PM | Permanent Link |
"Robert" | "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 > |
Mon, Aug 7 2006 11:03 PM | Permanent Link |
Jeff Cook | "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 |
Mon, Aug 7 2006 11:18 PM | Permanent Link |
"Al VAs" | 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 > > > > |
Mon, Aug 7 2006 11:21 PM | Permanent Link |
Jeff Cook | 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.. |
Mon, Aug 7 2006 11:41 PM | Permanent Link |
"Al VAs" | 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.. > > |
Tue, Aug 8 2006 12:19 AM | Permanent Link |
Jeff Cook | "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 |
Tue, Aug 8 2006 1:17 AM | Permanent Link |
"Al VAs" | 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 > > > > |
Tue, Aug 8 2006 3:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Aug 8 2006 6:44 AM | Permanent Link |
"Robert" | "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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |