Re: [firebird-support] Speed difference 2.5.3 - 2.5.7
Svein and Karol (not Livius, sorry), no it's not a secret. I have a complete turnkey, in separate FDB, since I need it to create some C# examples for students on different machines anyway, and if you want I can share it. But - before you're wasting precious time: I already know, why the plan is different; once Karol got me on the way, the rest was easy to find, even for me :-) It's got nothing to do with sub releases! If you want to know more, I need to be a bit verbose about the scenario in the following description. I am now puzzling over why this happens, one step earlier in the process, on rolling out the scenario to the different machines. The databases should be completely identical, being restored from the same master backup, to overwrite existing FDB. For some reason, though, on very few machines, the resulting restored FDB is not identical to the master. The problematic difference is that in the "fast" FDB I changed 1 ID-Field from Char(6) to BigInt and recreated the primary key index on it. The other JOINed column is of type BigInt anyway, so that sufficiently explains the great gain in speed. That change is in several restored FDBs, but not in 2 of them, which just coincidentally also had 2.5.3 running instead of 2.5.7. Some sort of corruption must be in there or going on, which can affect parts/pages of a restore (or a backup). I remember having different such cases before, never on production machines, always in testing environments. Once I also posted one problem here, but found that it must be something specific with my environment, not with Firebird at all. Both problematic machines are running an outdated version of Win-10 and I am ready to believe that there is the culprit somewhere. The problem is: it is not reliably reproducible. I ran the same steps over and over again, overwriting an older version of the FBD with the same backup. Close to 9/10 cases it works as desired, but sometimes after the restore the resulting FDB has only some of the last changes, but that one column is still of the old type. I am sure it's only my environment; nobody knows what has been done to those 2 or 3 machines (if it's only those). I will probably just reset them. But if you have need of a standalone example of why IDs meant for JOINing best should be of the same type *OR* - if the type can't be normalized (e.g. for legacy reasons) - one can use a selectable storedproc instead of a simple SQL-Statement to boost speed, you're welcome! :-) I'll then translate german identifiers to english and send a download link, database is 17 MByte. André = Ihre Nachricht: > > > > Almost seems like the PI_T_INVOICES is lacking or not active. > Either that or that the part of it being used for the query in > question has lousy selectivity. It is not unusual for the optimizer > to think that two PLANs are almost equally good and then sometimes > make a terrible choice, but I'm baffled by your good and bad plan > being very similar excepting that one of them uses a query and the > other doesn't. Is the query itself a secret or can you share it here? > Set > Den man. 12. nov. 2018 kl. 14:45 skrev André Knappstein > knappst...@beta-eigenheim.de [firebird-support] > : > Livius, > thank you. > > And: cool! You obviously have hit *something*. > I learned before on different groups and seminars, that when comparing > different execution times you better start by looking at the plans > only... I usually don't :-) > > So, the question needs to be changed from: > "Was there such a change between subreleases" > > to: > "what have I done or omitted - unknowingly - to get different plans?" > > I don't manipulate query plans, in fact I never touch them, or any > settings related to them because in my case surely the risk of > breaking more than I would be fixing is substantial :-) > > But now when I look at them, they are different on the fast vs. the > slow machines!!! > > Here is what I get for the query: > > fast execution: > PLAN JOIN (JOIN (I INDEX (PI_T_INVOICES), R2 INDEX (I_T_RESULTS)), K > INDEX (I_T_KEYS) > PLAN (R1 NATURAL) > > slow execution: > PLAN MERGE (SORT (JOIN (I NATURAL, K INDEX (I_T_KEYS))), SORT (R2 > INDEX (I_T_RESULTS))) > PLAN (R1 NATURAL) > > Query and database are identical, both created from the same > backup. > > But I have an idea which I will check later this afternoon. It's all > probably just something on 2 or 3 of my systems. > > I'll report bac
Re: [firebird-support] Speed difference 2.5.3 - 2.5.7
Livius, thank you. And: cool! You obviously have hit *something*. I learned before on different groups and seminars, that when comparing different execution times you better start by looking at the plans... only... I usually don't :-) So, the question needs to be changed from: "Was there such a change between subreleases" to: "what have I done or omitted - unknowingly - to get different plans?" I don't manipulate query plans, in fact I never touch them, or any settings related to them because in my case surely the risk of breaking more than I would be fixing is substantial :-) But now when I look at them, they are different on the fast vs. the slow machines!!! Here is what I get for the query: fast execution: PLAN JOIN (JOIN (I INDEX (PI_T_INVOICES), R2 INDEX (I_T_RESULTS)), K INDEX (I_T_KEYS) PLAN (R1 NATURAL) slow execution: PLAN MERGE (SORT (JOIN (I NATURAL, K INDEX (I_T_KEYS))), SORT (R2 INDEX (I_T_RESULTS))) PLAN (R1 NATURAL) Query and database are identical, both created from the same backup. But I have an idea which I will check later this afternoon. It's all probably just something on 2 or 3 of my systems. I'll report back! thanks, André > > Probably difference in query plans. > Show query plan for both server versions > Regards,Karol Bieniaszewski > ---- Oryginalna wiadomość > Od: "André Knappstein knappst...@beta-eigenheim.de > [firebird-support]" > Data: 12.11.2018 12:01 (GMT+01:00) > Do: firebird-support@yahoogroups.com > Temat: [firebird-support] Speed difference 2.5.3 - 2.5.7 > Hello all, > > for a local conference, Iampreparing a session for a > Firebird beginners' group, to demonstrate the basics of Stored > Procedures and their usage from different clients. > > One scenario is about transforming a *slightly* complex sql query into > a selectable stored proc which will yield the same result. > > I stumbled over something strange: > Query and SP are running with similar same speed on 2.5.7, which I > have in production systems. But query is *MUCH* slower than stored > proc on different(!) 2.5.3 systems (more rarely used machines, I > failed to upgrade so far). > > 2.5.7. systems: ~ 3 seconds for Query and SP > 2.5.3. systems: ~ 3 seconds for SP, 15 minutes(!) for Query > always tested from a fresh restored database with starting results > normalized to 0,00. > > It does not look like a difference in the machines' hardware or even > the used antivirus or OS can cause this big a difference. > > Just out of curiosity - because all production systems should be > updated to at least 2.5.7 anyway: has there been such an > improvement between sub releases? > > I checked the change logs: > https://www.firebirdsql.org/file/documentation/release_notes/Firebird-2.5..8-ReleaseNotes.pdf > and checked all between 2.5.3 and 2.5.8, but found nothing that > matches this experience. > > Query is pretty much straigthforward; one nested subquery to update > outer stream, no UDF or other built-in functions > > best regards, > André > > >
[firebird-support] Speed difference 2.5.3 - 2.5.7
Hello all, for a local conference, Iampreparing a session for a Firebird beginners' group, to demonstrate the basics of Stored Procedures and their usage from different clients. One scenario is about transforming a *slightly* complex sql query into a selectable stored proc which will yield the same result. I stumbled over something strange: Query and SP are running with similar same speed on 2.5.7, which I have in production systems. But query is *MUCH* slower than stored proc on different(!) 2.5.3 systems (more rarely used machines, I failed to upgrade so far). 2.5.7. systems: ~ 3 seconds for Query and SP 2.5.3. systems: ~ 3 seconds for SP, 15 minutes(!) for Query always tested from a fresh restored database with starting results normalized to 0,00. It does not look like a difference in the machines' hardware or even the used antivirus or OS can cause this big a difference. Just out of curiosity - because all production systems should be updated to at least 2.5.7 anyway: has there been such an improvement between sub releases? I checked the change logs: https://www.firebirdsql.org/file/documentation/release_notes/Firebird-2.5.8-ReleaseNotes.pdf and checked all between 2.5.3 and 2.5.8, but found nothing that matches this experience. Query is pretty much straigthforward; one nested subquery to update outer stream, no UDF or other built-in functions best regards, André
Re: [firebird-support] gback NULLed columns
> I would first rule out the possibility that the change was not > committed. Both DDL and gbak are transactional, so not having committed > the table change would result in that change not being part of the gbak > backup. Thank you, Mark. So, this is not usual and somehow I should be concerned, or at least irritated. I am pretty sure that the table change has been committed, but of course I did not check it, specifically. I only checked that there was no old transaction in the statistics. I was adding the columns using IBExpert, then also created and ran several Stored Procs. All the while, the database was in production and the users were inserting/updating/deleting records; I tested all actions and scripts on a dummy database with current data before, so I thought that for an exception shutting down database from production was not necessary. I backed up the database several times in a series of several days, did tests with the locally restored backups and only much later noticed that the new columns were missing in the local backup - all the new records were in, though. Server and clients are 2.5.3.26778. Gap between OT and NT was less than 100 when I checked (for 50+ concurrent users) and OT and OAT constantly moving. Is there something else I can check short of trying to reproduce this? Note: I am off to holiday in 2 days, so probably can only react on your advice in a couple of weeks. best thanks in advance! André
[firebird-support] gback NULLed columns
If it is what I think it is then it is probably widely known, just not to me :-) I would like to get certain, though, because I do have some respect for things happening and looking strange (might always be an early sign of some corrution) so here goes: Do new columns - without having inserted anything to them and without any default value nor being part of any constraint - not make it into the next backup? I have the - for me - strange situation that I added 2 columns to a production database, left them completely NULLed, created backup, restored to local backup-database; the local backup then contained all the latest records of the tables in question, but not the new columns. I tried several times, no changes, then I added a single value in each of the columns and they got restored to the local test database. Thanks in advance for confirmations/warnings! André mit freundlichen Grüßen, ppa. André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: andre.knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: Bad surprise on performance
Thank you very much for the advice, Hannes. I did not think of this too well. But of course this sounds very logical. I will re-configure the server to match the most popular database page size (I have 6 different databases). I will first consult different papers I have from conferences and some available other documentation, and then decide what will be a good page size for my scenario after the migration. thanks, André = Ihre Nachricht: > Guten Tag André Knappstein knappst...@beta-eigenheim.de [firebird-support], > am Samstag, 3. Januar 2015 um 10:45 schrieben Sie: >> Stripe size is 64K, btw. > much too big , best size for a stripset ist same as DB Page Size > Strip Set 64Kb means that the controller will read and write Blocks of 64KB > each I/O operation > with a page size of 4 KB 60 KB are moved around with no use , > wasting more then 90 percent of the disk read/write bandwidth > (disk caches will reduce the impact somewhat) > my LSI Controller allows me a stripeset as small as 16kb , so it set my page > size to match
Re: [firebird-support] Re: Bad surprise on performance
>> Operations (new Server) >> >> Read : 2.247 >> Writes : 5.960 >> Fetches: 2.476.240 >> Marks : 807.922 >> >> Operations (old Server) >> >> Read : 8.516 >> Writes : 6.084 >> Fetches: 1.602.243 >> Marks : 582.584 > These numbers suggest that either databases contain different data or > you're comparing SS vs CS, possibly with some concurrent activity. Absolutely the same data in both databases, both definitely on CS. Table specifically created for testing with absolutely the same records and subsequent identical DML commands and commit intervals. Will repeat the same test later, now with re-configured RAID cache. Right now my wonderful wife is already tapping her foot to signal the start of a shopping tour. And, trust me, this can be more dangerous than an inefficient FB server ;-) ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: Bad surprise on performance
Thank you Dmitry. Seems I found the solution to the real problem parallel to your posting of the message (BBU failure report resetting cache parameters). The difference on old hardware (2.9 - 3.2) seconds probably is due to inaccuracy in the very simple test methods. And, yes, nobody was currently working on the old hardware, but there still are some 400 fb_inet processes active, which probably added to the inaccuracy. = Ihre Nachricht: > 03.01.2015 03:08, André Knappstein wrote: >> >> I created a test table on both old and new server to play with updates >> and inserts (~ 150.000 records) >> Performance with 2.5.3 x64 on Win2008 is constantly >> changing, but at best its some 8 seconds and worst even 2 minutes! >> Performance with 1.5.4 x86 on Win2003 is always about the same, and >> always 2.9 - 3.2 seconds. > Are servers (especially the new one) loaded when you perform your tests? > Execution time can sometimes vary in SS due to the GC policy but it > cannot happen for CS, unless there are other queries running at the same > time. >> Query >> >> update X3058000 x >> set >>x.p3058_004n = (x.p3058_004n * 2) >> >> Operations (new Server) >> >> Read : 2.247 >> Writes : 5.960 >> Fetches: 2.476.240 >> Marks : 807.922 >> >> Operations (old Server) >> >> Read : 8.516 >> Writes : 6.084 >> Fetches: 1.602.243 >> Marks : 582.584 > These numbers suggest that either databases contain different data or > you're comparing SS vs CS, possibly with some concurrent activity. > Dmitry > > > ++ > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > ++ > > Yahoo Groups Links ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: Bad surprise on performance
> Also it is good to know some raid settings such as stripe\block size, write > cache mode, > presence of BBU (battery for internal cache). Vlad Khorsun! You are my man! :-) I hope you like the beer I was bringing in Siegburg, because you are sure in for more! I took your advice on re-checking cache parameters, and especially the BBU and found something interesting when reading the event logs: That b**ch of a Raid controller reported some error yesterday and - automatically, without so much of asking me first - reset all caching params to "highest security", which meant: write through, no adaptive read-ahead, and a cache size of ZERO!!! I changed that back again and did some tests and now, after only a few tests, it looks like some operations take as long as on the old hardware, but most complex operations with random updates/inserts are running 4 times faster than on the old system, and some 100 times faster than with the cache not operating correctly. I thought it's a LSI, but it's a FTS (Fujitsu Technology Solutions) with 512MB Cache (which sounds pretty much to me) and so far I did not find any option to turn off that behaviour of the BBU. Will have a discussion with the vendor... I don't want the BBU to automatically set these things for me on any probably not even serious exception. Stripe size is 64K, btw. Thanks again, will do some more tests but looks like I can finish migration to 2.5 next week.
Re: [firebird-support] Re: Bad surprise on performance
Hello Vlad, It is sure Raid 0 with 3 * 600 GB Toshiba SAS. I only installed the server today. I did not use the LSI Raid BIOS for configuration but the "server view" installation kit from the producer. So I will have to look up stripe size and BBU status. Next week I will also apply a benchmark test and then write results here.Iwill also try on other hardware. I have 2 other new servers for other uses but so far still idle, so I can play a bit. I wanted to drop you a message anyway, because if I understood correctly in Prague you do not have too many different hardware systems for testing, somaybe if you have a test database and a prepared benchmark I can run some tests with FB 3. > > Hi, André ! >> [Old] >> Server 2003 x86 no service packs >> Xeon with 4 GB RAM >> Classic 1.5.4 (x86, of course) >> Raid 0 on 2 * 500 GB SAS (though this is from memory, I should look it >> up...) >> >> [new] >> Server 2008 R2 x64 SP1 >> Xeon with 8 GB RAM (I will shortly add +8) >> Classic 2.5.3 x64 >> Raid 0 on 3 * 600 GB SAS > > Raid 0 ? Are you sure ? >> In comparison to what I read from others, my databases are small. >> Biggest database is some 1 GB only. > Agree, it is small database. >> Bad performance on updates and inserts and extremely bad performance >> on committing a big number of record changes (~ 150.000 updates). > > It points us to the issues with writes or with random writes. It could be > interesting > to see results of disk IO benchmark (any you like which able to test random > writes). > Also it is good to know some raid settings such as stripe\block size, write > cache mode, > presence of BBU (battery for internal cache). > Regards, > Vlad > > > >
Re: [firebird-support] Bad surprise on performance
Yes, the old system is running. It *is* the current production system of our company and it looks like I'll be glad if it holds on a bit longer :-) Don't know the Crystal Disk Mark. Will check this out for sure! Was trying to get some clue from Process Explorer (the former SysInternals one). Maybe I am lucky and it is just the Raid controller. Thanks! = Ihre Nachricht: >> Thank you for the prompt answer, Sean. >> But, no, I have read about this specific one. >> And though I am sure I did not fully understand about it yet, since my >> biggest database is only 1 GByte I *thought* this would not be a >> problem for me. > Do you still have access to old server/hardware. > If so, I would start by confirming that the problem is not with > hardware, by running disk benchmark like Crystal Disk Mark (my > personal fav, nice and simple), MS SQLIO or IOMeter. > Once you have eliminated hardware, then you can move on to OS > configuration/settings then Firebird config. > Sean > > Posted by: "Leyne, Sean" > > ++ > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > ++ > > Yahoo Groups Links ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Bad surprise on performance
Fascinating... I created a test table on both old and new server to play with updates and inserts (~ 150.000 records) Performance with 2.5.3 x64 on Win2008 is constantly changing, but at best its some 8 seconds and worst even 2 minutes! Performance with 1.5.4 x86 on Win2003 is always about the same, and always 2.9 - 3.2 seconds. My next steps will be: - get some sleep - reduce example to a database with only this one testtable - install 2.5.3 on 2 other new servers (different RAIDs) and test there - test also with 2.5.3 CS x86, 2.5.3 SS x64/x86, 1.5.4 CS x86 - fire myself before presenting bill for new server to my boss unless I find a solution If somebody *KNOWS* for sure that Firebird just won't work on Server2008, or if someone knows for sure that it does, please drop a note. Query update X3058000 x set x.p3058_004n = (x.p3058_004n * 2) Plan PLAN (X NATURAL) Query Time (new Server)) Prepare : 0,00 ms Execute : 30.781,00 ms Avg fetch time: 0,00 ms Query Time (old Server) Prepare : 15,00 ms Execute : 3.110,00 ms Avg fetch time: 0,00 ms Memory (new Server) Current: 10.412.912 Max: 10.504.672 Buffers: 2.048 Memory (old Server) Current: 5.887.792 Max: 6.158.624 Buffers: 2.048 Operations (new Server) Read : 2.247 Writes : 5.960 Fetches: 2.476.240 Marks : 807.922 Operations (old Server) Read : 8.516 Writes : 6.084 Fetches: 1.602.243 Marks : 582.584 mit freundlichen Grüßen, ppa. André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 = Ihre Nachricht: > Hey! Happy new year to all! > For me, it should have started with something great, new... > FINALLY! The migration from FB 1.5 to 2.5 (trust me, I had reasons not > to do it earlier). But now the old appications are all gone, and I can > migrate. > Seems there will be a delay. I migrated the database from old hardware > running 1.5.4 to new hardware running 2.5.3. Backup/Restore is about 4 > times faster. Wow! > But normal working is - at least - 50% slower sometimes worse. > I already scanned the newsgroups and noticed that other users had the > same problem, but none of them seems to have found a good solution. > For me - being only a part time wannabe administrator - I really don't > have a clue where to start looking. > Here are some details of the 2 systems; if anybody can point me into a > direction where to look first it would be most welcome. > [Old] > Server 2003 x86 no service packs > Xeon with 4 GB RAM > Classic 1.5.4 (x86, of course) > Raid 0 on 2 * 500 GB SAS (though this is from memory, I should look it > up...) > [new] > Server 2008 R2 x64 SP1 > Xeon with 8 GB RAM (I will shortly add +8) > Classic 2.5.3 x64 > Raid 0 on 3 * 600 GB SAS > In comparison to what I read from others, my databases are small. > Biggest database is some 1 GB only. > Bad performance on updates and inserts and extremely bad performance > on committing a big number of record changes (~ 150.000 updates). > Database has 4096 Page Size and 2048 buffers (which sounds like > setting for SS but I specifically set it higher to use more RAM > according to a hint from experts). > While testing the configuration, nobody is in the network. > Firebird runs on dedicated server (just windows and Firebird). > where to look? > mit freundlichen Grüßen, > ppa. André Knappstein > EDV und Controlling > ~~ > beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH > Hafenweg 4 > 59192 Bergkamen-Rünthe > Telefon: +49 2389 9240 140 > Telefax: +49 2389 9240 150 > e-mail: knappst...@beta-eigenheim.de > Amtsgericht Hamm Nr. B 420 > Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus > USt-IDNr.: DE 125215402 > > > ++ > Visit http://www.firebirdsql.org and click the Document
Re: [firebird-support] Bad surprise on performance
Thank you for the prompt answer, Sean. But, no, I have read about this specific one. And though I am sure I did not fully understand about it yet, since my biggest database is only 1 GByte I *thought* this would not be a problem for me. = Ihre Nachricht: >> Seems there will be a delay. I migrated the database from old hardware >> running 1.5.4 to new hardware running 2.5.3. Backup/Restore is about 4 >> times faster. Wow! >> But normal working is - at least - 50% slower sometimes worse. >> [Old] >> Server 2003 x86 no service packs >> Xeon with 4 GB RAM >> Classic 1.5.4 (x86, of course) >> Raid 0 on 2 * 500 GB SAS (though this is from memory, I should look it >> up...) >> >> [new] >> Server 2008 R2 x64 SP1 >> Xeon with 8 GB RAM (I will shortly add +8) Classic 2.5.3 x64 Raid 0 on 3 * >> 600 >> GB SAS > Let me guess, you database is larger than 8GB (or 16GB in size)? > The problem is not with Firebird... > Your problem is the infamous Windows 64bit File Cache causes excessive Page > File usage. > There are a large number of posts on this subject in this forum but also on > the web. > Sean > > Posted by: "Leyne, Sean" > > ++ > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > ++ > > Yahoo Groups Links ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Bad surprise on performance
Hey! Happy new year to all! For me, it should have started with something great, new... FINALLY! The migration from FB 1.5 to 2.5 (trust me, I had reasons not to do it earlier). But now the old appications are all gone, and I can migrate. Seems there will be a delay. I migrated the database from old hardware running 1.5.4 to new hardware running 2.5.3. Backup/Restore is about 4 times faster. Wow! But normal working is - at least - 50% slower sometimes worse. I already scanned the newsgroups and noticed that other users had the same problem, but none of them seems to have found a good solution. For me - being only a part time wannabe administrator - I really don't have a clue where to start looking. Here are some details of the 2 systems; if anybody can point me into a direction where to look first it would be most welcome. [Old] Server 2003 x86 no service packs Xeon with 4 GB RAM Classic 1.5.4 (x86, of course) Raid 0 on 2 * 500 GB SAS (though this is from memory, I should look it up...) [new] Server 2008 R2 x64 SP1 Xeon with 8 GB RAM (I will shortly add +8) Classic 2.5.3 x64 Raid 0 on 3 * 600 GB SAS In comparison to what I read from others, my databases are small. Biggest database is some 1 GB only. Bad performance on updates and inserts and extremely bad performance on committing a big number of record changes (~ 150.000 updates). Database has 4096 Page Size and 2048 buffers (which sounds like setting for SS but I specifically set it higher to use more RAM according to a hint from experts). While testing the configuration, nobody is in the network. Firebird runs on dedicated server (just windows and Firebird). where to look? mit freundlichen Grüßen, ppa. André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: Unable to create unique constraint
> Maybe you did some tinkering with the metadata? sure I did, I created that new table on the database, while the database was in use by different users. Usually I am not doing this, but migration writes its own book, sometimes :-) So, even if none of the existing connections can be using that table, after a metadata change I should expect this or similar problem before disconnecting all and probably do a backup/restore? I'll keep that in mind, hopefully it won't happen too often :-) Thanks, André
Re: [firebird-support] Unable to create unique constraint
> Oops, I forgot index problems could lead to there being NULL or > duplicates in the PK field, so change the above query to: >select > T.F1+0, > T.F2+0, > count(*) >from > T >group by > 1, 2 >having > count(*) > 1 SET and Thomas, thanks for the answer. The above select statement was only to make sure that no duplicates are still in the table. The table is a completely new creation, and nobody else could have been using it besides me, although the database itself is the original production database used by some 50 concurrent users. I disconnected and reconnected, even did a backup (to "visit" each record) and explicit sweep and made sure noone else was connected to the database late in the evening. No success. Today anyway, the problem dissolved into nothingness. I could suddenly create the UnC quite fine. Go figure... Thank you anyway, guys.
[firebird-support] Unable to create unique constraint
Hi Gang! While preparing the final steps to migrate from dBase/FB 1.5 to C#/FB 2.5 I need to create a unique constraint for the old databases, and need some help from someone still familiar with 1.5. Not using uniques too often, so it's probably something I am doing basically wrong, but I can't create the following unique constraint: alter table T add constraint UNQ1_T unique (F1, F2) using index UC_T I receive the following error: Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values. attempt to store duplicate value (visible to active transactions) in unique index "@1". I checked that at this moment the table should be "clean" from duplicates by using the following statement: select T.F1, T.F2, count(T.ID) from T group by 1, 2 having count(T.ID) > 1 The result after the first run was a bunch of rows which would have violated the unique. I cleared them off using a "For select..." in a stored proc and subsequent runs yield only empty rowsets. So the table should be ready for the unique shouldn't it? Can somebody help me to see the forest with all these trees around, please? What else could stop a unique like this? If I make a backup of the database and restore it to a FB 2.5 server, the unique is created just fine. The old server is Classic Engine, if that should matter. Thanks in advance! mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Firebird Tour 2013
What I still want to say: _EXTRA_ _ordinary_ _good_ _sessions_!!! Thank you, guys! André ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: How to pass a where clause to a stored procedure
> I got a test Execute Statement to work without any arguments and then found > this:- > http://www.firebirdsql.org/refdocs/langrefupd15-psql-execstat.html > They say "The argument string cannot contain any parameters." which > is basically what i wanted to do. ie pass a string containing the arguments. I don't understand too good where your problem is. The following is a simplified example which works fine for me with 1 variable fieldname and a variable value to form the where constraint. It is quite easy to extend this model to use more than one fieldname-value pairs, and also it is possible to make the condition [<, >, <=, >=...] variable instead of static. I am excited that this thing works so nice, and in fact it did already in FB 1.5, but I only use this model rarely, where I indeed wantthat a user can "click-create" a fieldlist or a where-constraint,andthe number of potentially resulting different queries is very very high. And I only do so, because I can make sure that *only* my application is executing that stored proc. create procedure SP_EXEC_WITH_ARGUMENTS ( IP_FIELDNAME varchar(10), IP_VALUE decimal(15,2)) returns ( OP_001Z varchar(10), OP_002N decimal(15,2), OP_003N decimal(15,2), OP_004N decimal(15,2)) as declare variable V_WHERECLAUSE varchar(512); declare variable V_COMMAND varchar(1024); declare variable V_001Z varchar(10); declare variable V_002N decimal(15,2); declare variable V_003N decimal(15,2); declare variable V_004N decimal(15,2); begin V_WhereClause = 'Where '||F_LRTrim(:IP_FieldName)||' >= '||F_LRTrim(:IP_Value); V_Command = 'Select Field_1, Field_2, Field_3, Field_4 from Table_1 '||:V_WhereClause; For execute Statement :V_Command into :V_001Z, :V_002N, :V_003N, :V_004N do begin OP_001Z = :V_001Z; OP_002N = :V_002N; OP_003N = :V_003N; OP_004N = :V_004N; suspend; end end
Re: [firebird-support] How to pass a where clause to a stored procedure
use "execute statement", but only if you really *must* do it this way. Restrict the use of this to the most necessary cases, because you are actually paying by loss of control for that ... "comfort". > Hi all > I'm wondering whether its possible to pass a Where clause to a stored > procedure in Firebird 2.5 > In the past I have passed parameters to stored procedures but it > would be extremly useful to be able to build the Where clause at the > front end and then pass it to the stored procedure. > Heres an example:- > rather than comparing all fields:- > for select acct.id, acct.acc_name, acct.acc_number, > sum( accmstt.debit_amount ) sum_of_debit_amount, sum( > accmstt.credit_amount ) sum_of_credit_amount > from account_mthly_sum_totals_table accmstt inner join > accounts_table acct on (accmstt.account_table_id = acct.id) > where (account_type_table_id between 1 and 2) and > (payment_terms_strict_days between 0 and 999) and > (payment_terms_eom_days between 0 and 999) and > (payment_terms_notes like '%') and > (credit_limit between 0 and 999) > group by acct.id, acct.acc_name, acct.acc_number > order by acct.acc_number > into :op_id, :op_acc_name, :op_acc_number, > :op_debit, :op_credit do > begin > suspend; > end > the actual search may be just a simple:- > for select acct.id, acct.acc_name, acct.acc_number, > sum( accmstt.debit_amount ) sum_of_debit_amount, sum( > accmstt.credit_amount ) sum_of_credit_amount > from account_mthly_sum_totals_table accmstt inner join > accounts_table acct on (accmstt.account_table_id = acct.id) > where (account_type_table_id between 1 and 2) > group by acct.id, acct.acc_name, acct.acc_number > order by acct.acc_number > into :op_id, :op_acc_name, :op_acc_number, > :op_debit, :op_credit do > begin > suspend; > end > as the filters are optional to the user. > I was hoping to do something like:- > for select acct.id, acct.acc_name, acct.acc_number, > sum( accmstt.debit_amount ) sum_of_debit_amount, sum( > accmstt.credit_amount ) sum_of_credit_amount > from account_mthly_sum_totals_table accmstt inner join > accounts_table acct on (accmstt.account_table_id = acct.id) > where :ip_where_clause > group by acct.id, acct.acc_name, acct.acc_number > order by acct.acc_number > into :op_id, :op_acc_name, :op_acc_number, > :op_debit, :op_credit do > begin > suspend; > end > and the :ip_where_clause is the input value for the where clause. > tia > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] Advice on the following problem
In addition to all the more useful advices you already got: check if indeed your connection string is saying ".FBD" instead of ".FDB". If it does and in your posting is not just a typo, double check if you indeed changed the default suffix from fdb to fbd. While your at it, also check if the slashes in your connection string have the correct orientation. # > HI all, > I have a problem that I cannot figure out. > I have 3 Windows 2000 machines and 1 Windows XP Pro machine > The XP Pro machine is in the back office and its job is to hold the > databases > My Windows 2000 machines are front of house BUT they cannot access the XP > Database > my Connection string is "backofficepc:c:/System Database/SysDatabase.FBD" > > And I keep getting the following error message > open database "backofficepc:c:/System Database/SysDatabase.FBD" failed > so does anyone have any advice as to how I can get the windows 2000 machines > to see and Access the XP Databases > I have the following set-up >> Same User name & Passwords on all machines (posuser) >> ALL the windows 2000 machines can read and write etc to the database on > the "master" terminal (This has been set to replace the back office PC for > now so the client can at least use his system) > I look forward to the advice from all you experts > [Non-text portions of this message have been removed] > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Classic vs Superserver
I'm probably not good enough, technically, to contribute too much here, but nevertheless I would like to share my experiences. We are running Classic (still 1.5! in the process of migrating to 2.5) for many years now. Serving 6 databases concurrently used by some 50 users and 3 different applications. Database sizes are not too large, up to 1 GB, biggest queries are processing some 200.000 records, some StoredProcs are processing some 4-5 million records. That's up to 6*50*3 == 900 concurrent processes on the CS (yes, plus the mother process, plus 2 for gbak at times, plus a couple of zombies). A realistic average is some 300 concurrent processes at any time. Still using 1.5, my options of hunting down the regular slowdowns is quite limited in comparison with the wonderful things possible today. But I went through this often enough, using SysMon tools on the server (Windows Server 2003). Almost always, the cause for a slowdown is when 1 or 2 users are running complex reports. At this time, only 1 or 2 users are using up to 80% of CPU (50% max per process), and are causing thousand times as many disk writes as all the other 298 processes together. But while the queries of these 2 also run quite slow, the effect on all the other connections is much more drastic. You might say that the server is just not idle enough to serve 298 other connections in a decent way, because of just those 1 or 2. The effects are worse if the database has not been backup/restored for some time (2 months), and/or if "the GAP" is unusually big. I successfully managed to improve the situation over the years - Revised all read-only queries to indeed only run read-only instead of read/write - Close connections as soon as possible - avoid Rollbacks as often as possible - rethinking and redesigning the transaction management in the applications In addition, using new technologies for the applications made it possible to reduce overall serverload dramatically (e.g. ADO.net). I changed one long-running report only recently and now it runs for 40 seconds instead of 10 minutes. The reduced time is not only good for the person running the report, but also for the server and thereby for all other users. Somewhere along the way, I *also* tried to change architecture to SuperServer, but I can assure you that this was only making things much worse. Today, we only very rarely still have problems with slowdowns. And I even have the database sweeping at normal intervals. What gives? If you want to analyse what's going on and what you need to do to improve it, don't just look at the queries which are slow, but most of all at those which are running concurrently! Re-thinking transaction management and application technology might bring much much more speed than tweaking any of the server parameters. >>>>we sometimes get queries, that normally complete in reasonable time, taking >>>>many times as long >>> >>> This sounds like you are running into the automation database sweep, >>> what is the sweep interval for the database? >> >>Turned off. > Hmm, do the queries suddenly or gradually drop performancewise? If > gradually, it could be a problem with one or more long-running > transactions. If suddenly (and repeatedly) it could be > a) that Firebird (incorrectly) thinks another plan would be better > b) running under a WAIT transaction if you're trying to update > something that (an)other person(s) has changed but not committed yet > These are at least a few possibilities, there could be others. > Set > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] Return PK Value
> I would like to learn the primary key value of successful SQL > Insert command. see "Returning". I think it was introduced with 2.1 or 2.5, definitely not with 1.5.x Suppose you don't include an ID in the statement, but only "other fields" and have the ID created by a Trigger. Insert into TableXYZ ( OtherField_1, OtherField_2 ) Values ( value_1, value_2 ) RETURNING IDField ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Transaction waits
> "insert into TABLE_B (ID, TABLE_A_ID) values (1, 5);" > Why does the second transaction wait? I might be thinking too simple, but for me this looks obvious. If the first transaction translates to "Update Table_A set ID = 5 where ID = 5", it does not make too much sense, but should not affect the "child" table. In this case, I suppose that Firebird server is not prepared for nonsense commands but expects all updates to be real updates (changing indeed the ID to another value). But any attempted change of the ID to another value *must* stop the second transaction, because otherwise it would be next to impossible to keep database consistency. The foreign key relation would be corrupted! So, I guess what you are asking is: why is the parser not intelligent enough to ignore my attempt to update the ID with the same value? If I had a vote on this, I would like to keep it as it is. Otherwise the parser would have to check this on every update which surely is some overhead; to be paid by each and every update transaction. ciao, André ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Re: gbak trigger(3) error
> ... and since the Interbase format is supported by Firebird I think I have an idea on why you have problems. From where and when is the information that "Interbase format is supported by Firebird"?
Re: [firebird-support] Historic tables design
> Nevertheless, troubles arise if you have to query the historic > table, making joins with look up tables wich reside in the main > database. A valid point of course, but not a problem in my case. I don't have that many lookup tables I'd need to be duplicated. Often enough I even don't need a duplicate lookup source, because my applications are the only accessors to the database, and so I can easily run a lookup against a datasource bound to a different connection (working with ADO.net, all the really interesting stuff happens in local RAM anyway).
Re: [firebird-support] Historic tables design
Aldo, I don't know if my approach is a good approach, but it is good enough for me: I keep a separate database for that purpose, to make use of the different access patterns. Like you say, in comparison with the "current" main database, there is much less access to these tables. And what is left usually is a read-only access as well. Records never get deleted, never get updated, only inserted, and I can't remember even one transaction that needed to be rolled back. I have more information for each record, though (WHEN was it replaced by a new version, WHO replaced it, and WHY has it been replaced) I have different strategies for moving the records from CURRENT to HISTORY. If the overall process does not take too long, I move them at once. If history records must be created because of batch updates, I only tag them, and the moving is done as part of a scheduled housekeeping. > Hi, > it is common in some business rules that a table has a companion > table of historic records. The historic table has the same structure > than the active records table, but is less accessed than the active > table. On the other hand, the historic table grows steadly, ending up > with far more records than the active one ( records deleted from the > active table go to the historic one ). > My question is the following: should both tables be merged into a > single table, with an additional field marking historic records ? Will > the queries on this table be slower than having them separated ? > I know that an index taking into account that additional field > would speed up the queries but, on the other hand, such an index would > be a very poorly selective one, because the additional field would only > have two values ( 0 and 1 ) like any boolean field. > Thanks for any advice. > Aldo > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] Re: gds32 cohexistance of different firebird installations
> This is why i have this problems. When i have to debug old programs > i use fb1.5, 2.0 2.1 and 2.5. Everytime I change FB I must remember > to chenge the gds32.dll in system dir. > To change the running FB server i've made me a tool tht stops the > running fb and starts that one that i need.. I would automate this also with > gds.. Maybe I don't get exactly what you are doing, but... ...if you stop the server anyway (means that some batch process is going on, and that this batch process *knows* which is the "one that you need") why don't you just copy the correct GDS over the one in the system dir?
Re: [firebird-support] Re: [OT] Firebird in LibreOffice!?? ;)
Great news! Thanks for sharing the info because I really lost everthing about Open/Libre Office a bit out of sight > --- In firebird-support@yahoogroups.com, Reinier Olislagers > wrote: >> >> Noticed this LibreOffice issue tracker item >> https://bugs.freedesktop.org/show_bug.cgi?id=51780 >> >> ... where people want to replace the existing embedded Base database >> with Firebird embedded. >> >> Hope it gets done - would probably be a nice boost to the Firebird name >> from a PR perspective as well as make it easier to work with >> Base+Firebird... > Sorry to dig this old thread but is done :) and we can test it from > LibreOffice 4.2 > Firebird has now been integrated into LibreOffice > http://www.ahunt.org/2013/07/firebird-now-in-master/ > This will give an estimate of > 25 Mil potential users if we > consider 2011 numbers > https://en.wikipedia.org/wiki/LibreOffice#Users_and_deployments > And for the easy of use part: > Firebird will be the Base default database engine > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] Unable to complete network request to host "192.168.3.25"
> Unable to complete network request to host "192.168.3.25" > Error reading data from the connection. I would take a copy of OmniPeek, Wireshark or similar and check network connectivity. In the few cases where I saw this, it always have been problems with switches or firewalls. Once it was a broken RJ-45 connector.
Re: [firebird-support] Re: gds32 cohexistance of different firebird installations
Martin, which version of BDE? If you did not do too many "native driver" specific things, you can change the alias to use ODBC instead of the native Interbase topology. I know many cases where this works very fine. I can personally confirm this works with ODBC driver 2.00.1.152 on Firebird 1.5.x servers, and I know some people who changed from native Interbase to Firebird 2.1 and 2.5 without problems. Maybe it is worth a try? Along with each and every ODBC DSN you could use a separate copy of fbclient.dll. > Thanks for your answer. I know this, but I have some old programs > build in delphi 2 with BDE.. so I can't use fbclient.dll. > I have this problem only on my develop workstation, where I need to > change the running FB version many times a day... > Is it possible to set something (a registry key, a config file, > some other trick) to tell windows where to look for this dll? > Is it possible to move this dll to an other folder removing it from > windows\system\ without malfunctions? > Thank's Martin > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] Re: Using stored procedure results in the WHERE clause
> If I use a COMPUTED column, any time I use SELECT * FROM > OPS_STOP_REC that column is going to be evaluated. This would end up > doing a lot of I/O in those cases where I don't care about the value > of the computed column. I'd bet that if you can afford to be careless about specifying the column list exactly, and thereby causing already a lot of overhead in the datastream and buffers, the tiny little bit of computing the column should not matter. But that's just an uneducated guess, since I am not in too deep in Firebird technics. As an alternative, you can put the functionality you want into one stored procedure (using, for example, a For... Select... into...). You can make extra use of some "execute statement" here and there to generalize the procedure and call the same procedure for different purposes. Extra overhead as well, but only due when actually querying the data. best regards, André
Re: [firebird-support] Primary Key Initial Value
> In creating a new table, how is primary key set to begin at a value other > than 0 or 1? "begin" suggests that you plan on some form of auto-incrementing a value for the primary key field? If so, I'd say you'll need a generator anyway. You can set this generator to any (valid) value you like, but you should always be aware that especially setting it to a lower value than before can mean a lot of trouble. Here's the syntax for Firebird 2.5 ALTER SEQUENCE RESTART WITH
Re: [firebird-support] Re: Storing headings and paragraphs in a Firebird database
I have an extra column for the position within one and the same hiararchy. > Can I ask, do you have a separate 'sort' field so the paragraphs > come out in the right order or are the ID's ordered to support this?
Re: [firebird-support] Re: Storing headings and paragraphs in a Firebird database
> Could I ask how you have implemented the recursion - i.e. in the > client, using a CTE or with a stored procedure? In this particular case I am not using a recursion in SQL. I have another scenario where sales items are grouped and the groups' hierarchy can be changed dynamically by many concurrent users, and there I have used stored procedure in FB to find all items belonging to all n levels of subgroups of a certain group. Anyway I do favour fetching only those records the user currently can process, or is effectively looking at. Just received Helen's newbook and the first page I opened (464 in Vol. 2) says something about "who can process 200.000 rows at once anyway?". Exactly what I am trying to tell all my old dBase companions, but now that Helen writes it they'll probably start believing me :-) I am using nested "foreach..." loops in C#/ADO.net to open all sub-items of any given parent item. I am sure that Delphi/C++ et al will have the same options. This virtually means I have one query sent to the FB server for each hierarchy level, constrained to paragraphs having the current paragraph as parent (WHERE IDParent = :IDThisParagraph...). The TreeItems/Nodes have a custom property "IDParagraph", and so in the adjacent "Details" screen I can load all details for one paragraph, move it up and down the hierarchy, delete, modify it, add new paragraphs etc. I keep a local storage for details already fetched from the database, so they won't be fetched again unless they have been changed by another user. I don't exactly know, by the way, what a CTE is. Something similar as a VIEW in Firebird, I guess. If you need to implement as much as possible in SQL, it is probably a good idea to check Global Temp Tables in FB 2.x upwards. > The reason for asking is that AFAIK the ordering in the result set > (from a CTE at least) is not defined. Therefore you could end up with:
Re: [firebird-support] Storing headings and paragraphs in a Firebird database
I have realized a solution for a similar problem, but since I am more a power user than a database professional I can't tell if this is really a good solution. I can only tell that it works nice for our company (some 50 concurrent users, documents usually consist of some 25 Headings with some 1 - 15 sub paragraphs). >From scratch, basically I do have 3 tables: T_Index: - IDParagraph (BigInt) - IDParent (BigInt) // Uplink to parent paragraph, NULL if on 1st level - IDDocument (BigInt) // The "document" as such - IDType (SmallInt) // e.g. Heading, sub heading, title... - IDStatus (SmallInt) // e.g. confirmed, pending... T_Content - IDParagraph (BigInt) - TXParagraph (BLOB Type 1/80) T_HyperLinks - IDHyperlink (BigInt) // For further annotations in other tables... - IDParagraph (BigInt) // Source paragraph for a hyper link - IDTarget(BigInt) // Target paragraph for a hyper link I do - of course - have more tables in the system. For example I have commentson the hyperlinks, qualifications of hyperlinks, modification protocol (who changed what and when...), some tables for results of parsing (to find keywords in a range of documents) etc. Also you can have keywords pointing to documents, documents to documents, paragraphs to documents... Processing the hierarchy within one document is very easy using a recursion. I usually use a TreeView component to display to the user. I hope I did not misunderstand what your question was :-) best regards, André > This is probably a question for Stack Overflow, but I would like a > Firebird specific answer so asking here. > I would like to store simple documents in a Firebird database as an > ordered collection of paragraphs as follows: > Document 1 Record 1 ->> 1. Heading 1 Record 2 ->> 1.1 Heading 2 Record 3 ->> 1.1.1 Heading 3 Record 4 ->> 1.1.1.1 Paragraph bla bla bla Record 5 ->> 1.1.1.2 Paragraph bla bla bla Record 6 ->> 1.1.2 Heading 3 Record 7 ->> 1.1.2.1 Paragraph bla bla bla Record 8 ->> 1.1.2.2 Paragraph bla bla bla Record 9 ->> 2. Heading 1 Record 10 ->> 2.1 Heading 2 > ... > Document 2 Record n ->> 1. Heading 1 Record n + 1 ->> 1.1 Heading 2 > and so on. > I would like concurrent access to different paragraphs of the > document and each record can link to paragraph(s) (records) in > another 'document'. Therefore I want to move away from creating the > document in XML and storing it in a BLOB. > I think CTE is out because I can't determine the order of siblings > and nested sets are not good for doing a lot of inserts (as is likely when > writing documents). > Probably the obvious way is to use path enumeration, but I guess > this is a relatively common problem (although googling doesn't find > many results) and wondered if anyone has a good solution? > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] Re: Aliases and Windows 7
That should be "not even when you are administrator and run it", excuse the wrong wording, please. You can of course right-click/run as administrator, but then you can't open the file directly but first need to "File|open" and navigate to it, which to me is more complicated than just saving elsewhere and copying later. You can of course also change registry so that Notepad always runs as administrator, but that simply defeats an otherwise useful security feature. > On Fri, 31 May 2013 12:10:27 +0200, André Knappstein > wrote: >> >> It is probably good to know that Notepad.exe does never have the >> permission to write into a protected directory, not even when it is >> run as administrator. >> > Since when? Running Notepad with "Run as Administrator" works just fine > for > editing files under "C:\Program Files" on Windows 7 and Windows Vista. You > just > need to open file in Notepad after starting Notepad with "Run as > Administrator". mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] Aliases and Windows 7
> 2. Fix aliases.conf *in your Firebird host root directory*: entries should > look like this: > XFILES = C:\DATI\FB\XFILES.FDB In addition, since Walter wrote he does not have too much experience with W7 and upwards, you will usually use Notepad.exe to edit the *.conf files. It is probably good to know that Notepad.exe does never have the permission to write into a protected directory, not even when it is run as administrator. If you can edit, but not save such a config file, just save it somewhere else, and then use copy+paste to replace the original with the new version of the file. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Case statement in PSQL
Holy muzzle! How could I miss that there is a *paper* version of the book? I somehow kept some of Helen's statements in my mind about how difficult it would be to organize chapters and all in such a way that it'd make sense to do a paper version. I could perfectly follow this argumentation, but still would have liked to have a paper version for personal reasons. I have the DVD from IBPhoenix, but never had a chance to open a single page. I just need real paper from time to time to get a break from all the too many hours I spend staring into an electronic display. I'm probably getting too old for this e-book stuff. Thanks a million, I'll be off to buy 3 certain books right now > Using version 2.5.2 > Is the case statement available in PSQL - specifically stored procedure. > Thanks in advance > Cam > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++ > Yahoo! Groups Links
Re: [firebird-support] Save decimal data type
Francisco, did you carefully read all of my message and the examples I posted? Sorry to ask, but I don't have the impression you did. What you experience probably is working as designed. At least probably "as designed by Microsoft" (for .net providers). _IF_ you are using the FbParameters collection correctly, declaring the FbParameters["@MyParamName"].Type as "Decimal", _AND_ provide a decimal value to the FbParameters["@MyParamName"].Value, everything will work fine, I am quite sure. Your example does not contain *any* parameter (it simply misses any "@", which is required to introduce a parameter in .net). Please try again. If it does not work, please post a more complete part of your code, and please better use the list "Firebird-net-provider" to not bother the non-net users. I am sure that memberst of that list will be able to help you very quickly! best regards, André > Thanks André; > I've declarated the FbConnection, and FbCommand. > Everything works fine with connection and Command. > If I send a Query like this: > SQLString = "INSERT INTO MyTable(CANT) VALUES(MyAmount)" > considering the Field CANT as String, and the same thing for MyAmount, > FireBird has no problem to save the data. > the problem is, when CANT is Decimal data type, or Numeric, or Float. > Any one? > How to save a decimal data type in Firebird from an app?? > doesn't matter if the programming languaje is other than VB.Net > I need to know how to save a decimal data into firebird like this: 25.80 > thanks > > Francisco José Scheffer > > De: André Knappstein > Para: Francisco José Scheffer > Enviado: miércoles, 29 de mayo de 2013 10:35 > Asunto: Re: [firebird-support] Save decimal data type > > > Francisco, > to me this does not look like proper code for .net. > Personally I am using C#, but I know quite some of VB.net. > You need: > - a Command object (of Type FbCommand) > - a Parameter object (of Typ FbParameter) > look at the following links for further guidance to VB.net in this > regard: > http://www.dreamincode.net/forums/topic/165141-sql-parameters-from-vbnet/ > http://idealprogrammer.com/net-languages/code-samples/vbnet-sql-parameters-source-code-insert-statement/ > http://www.java2s.com/Code/VB/Database-ADO.net/PassParameterintoSQLcommand.htm > Have fun! >> thanks Ann; >> Here is some code; >> Declaration >> Dim MyAmount as Double /* Double - 64 bit floating-point number */ >> Dim SQLString as String >> MyAmount = 25.80 >> SQLString = "INSERT INTO MyTable(CANT) VALUES(MyAmount)" >> In Firebird, the field CANT is: (I've tried Numeric, Decimal and FLOAT also) >> With Decimal was 18,4 >> Numeric was 12,2 >> When Firebird save the data, it does as 2580, not 25.80; >> Is it perhaps a problem on my Windows regional settings? >> thanks for help!! >> Francisco José Scheffer >> >> De: Ann Harrison >> Para: firebird-support@yahoogroups.com >> Enviado: martes, 28 de mayo de 2013 17:36 >> Asunto: Re: [firebird-support] Save decimal data type >> On Tue, May 28, 2013 at 10:26 AM, franchessko wrote: >>> Hi, I'm a new firebird user. I joined here to ask for help. >>> I develop in DotNet Technologies, and I have this problem: >>> >>> >>> And the problem is: >>> --- >>> When I send a Query with a decimal data type like '25.80' -without quotes- >>> the data is saved in firebird like this 2580, >>> (the database field was created as Decimal 18,4) >>> >> Without seeing the actual queries you used, I'm guessing about why you see >> what you see, but I can tell you what Firebird is doing. When you store >> 25.80 in a column that's defined as decimal 18,4, Firebird stores the >> value as in integer, which in this case would be 258000 (two hundred fifty >> eight thousand). The scale is part of the field definition. Firebird uses >> the scale when doing comparisons, so 258000 scale 4 is more than 257 >> scale 5 and less than 260 scale 1. All that is fine, internally. >> However, the SQL numeric and decimal types are based on data types used in >> COBOL. Those data types are not generally supported in more modern >> languages. When you retrieve a decimal or numeric value with a non-zero >> scale, you should probably ask for it as a floating point number or a >> string. You'll get better information from the Firebird .NET driver list >> o
Re: [firebird-support] Save decimal data type
Francisco, to me this does not look like proper code for .net. Personally I am using C#, but I know quite some of VB.net. You need: - a Command object (of Type FbCommand) - a Parameter object (of Typ FbParameter) look at the following links for further guidance to VB.net in this regard: http://www.dreamincode.net/forums/topic/165141-sql-parameters-from-vbnet/ http://idealprogrammer.com/net-languages/code-samples/vbnet-sql-parameters-source-code-insert-statement/ http://www.java2s.com/Code/VB/Database-ADO.net/PassParameterintoSQLcommand.htm Have fun! > thanks Ann; > Here is some code; > Declaration > Dim MyAmount as Double /* Double - 64 bit floating-point number */ > Dim SQLString as String > MyAmount = 25.80 > SQLString = "INSERT INTO MyTable(CANT) VALUES(MyAmount)" > In Firebird, the field CANT is: (I've tried Numeric, Decimal and FLOAT also) > With Decimal was 18,4 > Numeric was 12,2 > When Firebird save the data, it does as 2580, not 25.80; > Is it perhaps a problem on my Windows regional settings? > thanks for help!! > Francisco José Scheffer > > De: Ann Harrison > Para: firebird-support@yahoogroups.com > Enviado: martes, 28 de mayo de 2013 17:36 > Asunto: Re: [firebird-support] Save decimal data type > On Tue, May 28, 2013 at 10:26 AM, franchessko wrote: >> Hi, I'm a new firebird user. I joined here to ask for help. >> I develop in DotNet Technologies, and I have this problem: >> >> >> And the problem is: >> --- >> When I send a Query with a decimal data type like '25.80' -without quotes- >> the data is saved in firebird like this 2580, >> (the database field was created as Decimal 18,4) >> > Without seeing the actual queries you used, I'm guessing about why you see > what you see, but I can tell you what Firebird is doing. When you store > 25.80 in a column that's defined as decimal 18,4, Firebird stores the > value as in integer, which in this case would be 258000 (two hundred fifty > eight thousand). The scale is part of the field definition. Firebird uses > the scale when doing comparisons, so 258000 scale 4 is more than 257 > scale 5 and less than 260 scale 1. All that is fine, internally. > However, the SQL numeric and decimal types are based on data types used in > COBOL. Those data types are not generally supported in more modern > languages. When you retrieve a decimal or numeric value with a non-zero > scale, you should probably ask for it as a floating point number or a > string. You'll get better information from the Firebird .NET driver list > on exactly how to push decimal and numeric values through that interface. >> I tried sending the same number, but with the comma '25,80' but firebird >> says: "Dynamic SQL Error. SQL Error code = -804. Count of read-write >> columns does not equal count of values" >> > Firebird does not use the locale to choose the separator between the > integral and decimal portions of a number, so it's reading 25,80 as two > values 25 and 80. > Good luck, > Ann > [Non-text portions of this message have been removed] > > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] Firebird 1.5.6 on Windows Server 2012 - unavailable database
> The system was working on Windows XP. I am thinking there might be ACL issues > but I'm unsure. Are you aware that on XP it was still possible to use named pipes for connection, while in Vista and higher (including Server2012) you *must* use TCP/IP and need to qualify an IP-Adress (alternatively "localhost")? This at least is the solution in some 90% of all cases where other Firebird users are asking me.
Re: [firebird-support] Re: Bug-Fix from 2.1 to 2.5 in updating current row values?
Thanks, Dmitry. He knows and is already at work in changing the PSQL. The problem was that he noticed the problem only after a massive rollout. > 11.04.2013 16:14, André Knappstein wrote: >> great! >> Thanks Dmitry! >> My friend Jaime will be more than happy that this conf setting will >> buy him some time to change the PSQL instead of doing a downgrade to >> 2.1 > He will have to change his PSQL anyway once FB3 is released and he > decides to upgrade. This config option is not available there. So it > might make sense to do that sooner rather than later. > Dmitry > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Re: Bug-Fix from 2.1 to 2.5 in updating current row values?
great! Thanks Dmitry! My friend Jaime will be more than happy that this conf setting will buy him some time to change the PSQL instead of doing a downgrade to 2.1 > 11.04.2013 15:51, André Knappstein wrote: >> >> AmI right in telling him that what was working for him in 2.1 >> was something like bad design on his side, supported by something that >> can be called a bug in Firebird which was now fixed or a flaw which >> now has been improved? >> >> What really is important: >> Does he have any other choice but to re-check his 1.200 stored >> procedures and replace the field-value identifiers by the input >> parameter identifiers? > Read firebird.conf re. the OldSetClauseSemantics setting. > Dmitry > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Bug-Fix from 2.1 to 2.5 in updating current row values?
Hi Gang! I am asking this on behalf of a friend who is intensively using Firebird. He recently switched from 2.1.x to 2.5.x because I recommended him to do so. As it turns out, he was constantly doing the following: UPDATE Invoices SET AmtVal = :nAmtVal TotTax = AmtVal * Tax TotVal = AmtVal + TotTax WHERE InvNo = :cInvNo; which was working fine for him in 2.1.x In 2.5.x he is getting wrong results/row values, unless he changes his approach to the following: UPDATE Invoices SET AmtVal = :nAmtVal TotTax = :nAmtVal * Tax TotVal = :nAmtVal + TotTax WHERE InvNo = :cInvNo; Personally I'd never had even tried the first approach, so I can only guess that up to 2.1 it was possible to read a value just updated in the same transaction which is no longer possible in 2.5.x. AmI right in telling him that what was working for him in 2.1 was something like bad design on his side, supported by something that can be called a bug in Firebird which was now fixed or a flaw which now has been improved? What really is important: Does he have any other choice but to re-check his 1.200 stored procedures and replace the field-value identifiers by the input parameter identifiers? thanks André ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] gbak speed
AK>> I don't use Services API nor -g in that case. > by some reason or "never thought about it"? > I wonder, why not to use -g option. did not know about it when I installed the system. Now the system is in a "don't touch it while it runs" mode. I have been spending some 8 years learning C#, writing new apps in C# and at the same time replacing the existing apps (dBase and Delphi). And I only have some 25% of my time for all IT-stuff, including maintenance and 1st level support. You can't imagine how glad and happy I am that all these years Firebird was an extremely reliable and stable "partner" for me and my job. This year I will update all the database stuff and also try to consider all the good advice I collected all the years by lurking this mailing list :-) ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] gbak speed
> Hello, André! > Thursday, April 11, 2013, 2:42:01 PM, you wrote: >>> How does this compare with the experience of others? AK>> Hmmm. A quick calculation shows: AK>> My biggest database is 1/74 of the size of yours. AK>> I need ~5 Minutes to backup a freshly restored database, backup takes AK>> longer if the restore is longer ago (up to 6.5 minutes). > too slow. at my old desktop with Athlon 64 x2 5200 and 3 SATA II drives > backup of 2.7GB database from one drive to another took from 3.5 to 5 > minutes, depending on protocol used. > The fastest way to do backup is to use Services API, > i.e. -se option of gbak. And, to use -g option of course. I don't use Services API nor -g in that case. I simply leave everything as is; am in the process of upgrading all and everything to current hardware, OS and FB 2.5.2. That old database is FB 1.5.6 CS with some 200 open connections all the time and 80% of RAM + 30% CPU constantly in use - which might explain. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] gbak speed
> How does this compare with the experience of others? Hmmm. A quick calculation shows: My biggest database is 1/74 of the size of yours. I need ~5 Minutes to backup a freshly restored database, backup takes longer if the restore is longer ago (up to 6.5 minutes). 5 * 74 = 370 minutes == 6 hours and 10 minutes. certainly not any academical method to do a comparison, but surprisingly close to your result :-) Mine is on hardware of 2005, RAID, Dual Xeon 1GHz, Server2003 R2 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Re: Best Firebird Platform for our Environment
Bill, we have been running what in our understanding was a "huge" mixed dBase/Delphi 7 application until 2006. DBFs until 2004, then switched to Firebird for data, then replaced (nearly) all code by C#/.net. When the performance of an application on a well equipped Classic Server is worse or even much worse than on a weak SuperServer which also runs RDP sessions, then definitely something is wrong in the total application setup. >From 2004 to 2006 we have been using dBase/BDE to access a ClassicServer 1.5.2 on a 4GB XEON. Even when the CPU runs at 70% and there virtually is no RAM left, any query sent from a 2012 client machine still runs much faster on the CS than on a locally installed SuperServer. There are people who can help you improving your application setup, to get a huge performance boost, but it most likely requires some changes in code and attitude. The weakest among many weak points in using dBase/BDE as frontend for SQL databases is the lack of a good datagrid. dBase/BDE requires constant read/write connections and a lot of silly indexes to work smoothly. I think this stuff does not really belong here, but there is a specialized Newsgroup for this: news.dBase.com, group "SQL-Servers". You don't need a login and password there. You will meet several people who are using dBase/BDE/Firebird. You will get step-by-step help there to improve the Firebird experience. > Thanks for the information. In regards to the Windows 2003 Server > running RDP, it is running Firebird Superserver 2.1.3 32-bit. What I > have noticed is it only uses 1 CPU and it is running at 60-100% > utilization. In regards to the dbase Plus programs, there are some > dbase files used in conjunction with the firebird files. We have > experimented with a Windows 2008 Server, 64-bit with 8gb RAM, dual > E5430 XEON processors and Firebird 2.5.2 64bit. Firebird utilized > the multiple CPUs and the Utilization never went above 19%, but the > performance was worse. We also encountered frequent not-responding > messages during program execution. The screen goes white. There is > no certain pattern to the not responding messages and in some cases > the program ends normally, but in a few cases, the program ends > without completing. If you run the same programs from a workstation > with firebird 2.5.2 client-only install, they run properly, but run > much slower. The test workstation is an Intel I5-661 with 4gb of memory and > Windows 7 32-bit. > --- In firebird-support@yahoogroups.com, André Knappstein > wrote: >> >> If you have enough know-how to manage all the basic linux stuff, then >> go with a linux system. If you don't then better use windows. >> >> But something else needs your attention (or mine *g*). >> Are you using StoredProc objects in dBPlus 2.6 to deal with stored >> procedures in Firebird? >> If so you should be warned, there is an undocumented bug in dBase >> that - only on XEON systems - leads to exceptions in the dBase >> StoredProc object. The bug persists in the freshly released dBase plus >> 8. >> >> If your application experience is fine with RDP sessions, then keep >> it. Never change something you are satisfied with! >> If you will go real client/server, you might get a huge improvement, >> becausea lot of workload is transmitted to the quite >> potential workstations. Depending on how complex your GUI stuff is, >> this can render a huge performance gain for the server, which in turn >> can much better use the power for the database stuff. >> But if your implementation is not too clean, you also might >> experience the opposite! >> 2.6.2 still is firmly tied to the BDE, which means you need a special >> approach to use dBase as front end for *any* SQL server backend. >> >> So far you only needed changes in pixels to go over the wire. >> Full client/server would also mean that the data you operate on will >> have to go over the wire. >> You better make sure that data is as small as you really need. >> >> Typical dbase/BDE behaviour is: create many many many indexes with the >> poorest of selectivity *and* doing a full table select as often as >> possible. This will break your neck in client/server. >> >> >> >> > We are trying to determine the best platform for Firebird SQL >> > 2.5.2. Our firebird databases are 40-60gb in size and are being >> > accessed by dbase plus 2.6.2 programs. Currently we are running on >> > Windows 2003 Server with dual Intel XEON E5430 quad-core processors >> > and 8gb RAM. This server is being accessed
Re: [firebird-support] Best Firebird Platform for our Environment
If you have enough know-how to manage all the basic linux stuff, then go with a linux system. If you don't then better use windows. But something else needs your attention (or mine *g*). Are you using StoredProc objects in dBPlus 2.6 to deal with stored procedures in Firebird? If so you should be warned, there is an undocumented bug in dBase that - only on XEON systems - leads to exceptions in the dBase StoredProc object. The bug persists in the freshly released dBase plus 8. If your application experience is fine with RDP sessions, then keep it. Never change something you are satisfied with! If you will go real client/server, you might get a huge improvement, becausea lot of workload is transmitted to the quite potential workstations. Depending on how complex your GUI stuff is, this can render a huge performance gain for the server, which in turn can much better use the power for the database stuff. But if your implementation is not too clean, you also might experience the opposite! 2.6.2 still is firmly tied to the BDE, which means you need a special approach to use dBase as front end for *any* SQL server backend. So far you only needed changes in pixels to go over the wire. Full client/server would also mean that the data you operate on will have to go over the wire. You better make sure that data is as small as you really need. Typical dbase/BDE behaviour is: create many many many indexes with the poorest of selectivity *and* doing a full table select as often as possible. This will break your neck in client/server. > We are trying to determine the best platform for Firebird SQL > 2.5.2. Our firebird databases are 40-60gb in size and are being > accessed by dbase plus 2.6.2 programs. Currently we are running on > Windows 2003 Server with dual Intel XEON E5430 quad-core processors > and 8gb RAM. This server is being accessed with Windows Remote > Desktop. We are in the process of replacing this server with a new > server using 2 Intel XEON E5-2640 processors and 16gb of RAM. We are > trying to determine whether we should go with Windows or Linux. We > also want to consider client/server versus the current model we are > using. We would use Intel I7-3770K processors with 8gb of ram for > workstations. > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] How to do a join delete with Flamerobin V 0.9.2.1851
> instead I resorted to execute block, like this (in general terms, not > adapted to your specific case): And, yet another option. I don't know if it's good or bad, but I do this frequently and have no problems with performance or results. If this is not a run-once command, but most likely is feature needed frequently, consider a stored proc. You can create a For... Select...into... Begin...End cursor and then delete all results for that cursor in the "Begin... End" part. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: SV: [firebird-support] ODBC queries with parameters failing
> As a suggestion, if any devs look at this, it would be nice if the ODBC > driver came with the embedded client built in, along with a way to > create a new DB easily. It already has the ability to do a lot of other > DB housekeeping. This would make it way easier for casual use. For my personal taste, pumping even more things into the ODBC driver would be like PostGres'sing it. Please don't! I like it as simple, functional and *stable* as it is! I usually created 5 databases plus DSNs for 32 and 64 Bit on a machine before a PostGres user finished even reading all the options he has in the driver! To create databases, look up the literature about which commands to use or use one of the available free management tools. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Need some direction on combining values into one
> I have a table (see below) where I want to combine the two totals > below into the 0304KZ6 total so that I can end up with something like the > second example. > current 0304KZ6 6786.11 > 0304KZ6RR 3750.41 > desired 0304KZ6 10536.52 > 0304KZ6RR 0.00 Am I correctly assuming that these 2 fields are only an example, and you probable have more of such constructions? Then I'd first try to change the database layout. The above structure does not seem to be optimal if you need such results more often. Then, if you can't change the database, you can try a client approach (calculate the total of the 2 fields by a routine in your client application) if you can provide for proper isolation (not having one field updated while another is read). If all of this does not work, consider "execute statement" in PSQL within a StoredProcedure. It helps me quite well in a similar situation, even though the experts seem to be quite reluctant about "execute statement".
Re: [firebird-support] Re: Handling large imports while system is in production
> Back in 1994 I was able to insert 1000 rows/secs into a IB database from an > DBF file. The 1994 DBF format probably still was Level 3. Even Level 4 would not have been much different from a plain text file, and you would not need any type of middleware to read them into whateveryouimagine. It all became much more complicated with Level 5, which requires either ODBC or the dreaded BDE. Nothing is really fast (in today's terms) whenever one of them (or both! yuk!) are involved. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Performance issue
> Checkout the releasenotes of 2.5 on the filesystem cache: > http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes252.html#rnfb25-fbconf-fscache Did so before, and did now again. Did not clearly understand if it is important for Win200_8_x64. Is that important for "(Microsoft Server 2003 host with SP1) and higher" (including Server2008, Server2012) or "(Microsoft Server 2003 host with SP1 and higher)" (including W2K3 SP1 - SPn)? If a machine has enough RAM (e.g. users * databases * pagesize * pagebuffers * 2^x), is it eligible to say "FSCThreshold can always be set to 0"? thanks, André
Re: [firebird-support] Re: Handling large imports while system is in production
> Exporting the .DBF to a delimited text file and then importing using an > external table would be faster? Walter, a consultant I know has made very good experience with this. He was regularly importing DBF tables (level 5 and 7) via ODBC. I encouraged him to try exporting them to CSV first, then to integrate them as external tables. He reported a massive speed gain (2 minutes vs. 2 hours). It's definitely worth a try! ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Performance issue
> The basic issues is that, by default wants to cache all disk pages > and with a 64 bit OS it think it has a huge amount of RAM. So once > all physical RAM has been consumed Windows starts to read/write to > the disk page file, which causes an excessive amount of disk IO and thus > slows the system. Sorry for sort-of sidejumping this. Am I still correct in thinking that all these fixes are applied to 2.5.2 and that I can quite safely install a 2.5.2 CS on an otherwise "naked" Win2008R2x64 without expecting trouble? I have a consulting session next week where I want to convince the audience to use FB instead of MSSQL and would not enjoy to get tar-and-feathered afterwards :-) thanks, André ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Firebird and RAID
> My opinion is simple. If > Firebird DB at disk, attached not to RAID - fast > and > Firebird DB at dis, attached to the RAID - slow > then problem is with raid controller configuration. > "Other applications" can use disk less then Firebird, or > only read, so, the IO of that applications can be much less than FB's. 100%!!! We had big problems with LSI Raid controllers between 2007 and 2009, which unfortunately turned up only long after installing the systems. They were "standard" on Fujitsu Siemens server gear (TX/RX 100/200). We did not have to create arrays; errors were turning up also when RC was just "between" network and hard disk. It turned out that in some situations, when number of reads(!) from the RC increased, they go extremely slow. We did not receive timeouts, buta simple query could suddenly take 2 minutes. Browsing a list of files on a share took up to 10 minutes. All the time CPU and HD activity was near 0%. Shutting down and restarting the server helped (but of course this was no real solution). We got FSC to replace some controllers, which showed the same behaviour as before. We could not afford further discussions, so we decided to take out the RCs completely on those machines - did not need them anyway - and all was fine. Since then we specifically order server gear for certain services - including database servers - without any RAID controller. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Re: SUSPEND
You did not write, which server version you are using and on which hardware. I have one intensively used ClassicServer 1.5.6 (...yeah... *am* in the process of migrating to 2.5.2) running on a dedicated machine with Windows2003 OS. 4 GB Ram only, which seems ridiculously little today. I have about 50 concurrent users accessing 6 different databases on that server with a total of some 500 tables and constand read/write access. Some client processes are very extensive and are running complex stored procedures for several minutes. Each connection uses a maximum of 50% CPU. There was nothing I had to configure (which probably is, why it is working so good). All the rest of the CPU is still available for all the other connections and they can work fine. Maybe you need a similar setup of Firebird architecture and OS/hardware? > Hi Mark, > This is an EXECUTEable proc, returning nothing, so I should not use SUSPEND. > Do (or anyone) have a suggestion as to how I can tell the stored > proc to not bring my server to it's knees for 3 full minutes? > I want FB to be given high(est) level thread priority in the OS, > except for this one procedure, I'd like FB to rather "mozy along", > and serve other user's requests first and get back to this thread when it can. > Thanks. > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] Doubt with trigger before insert
> ... > where nfi.registro_id=new.registro_id assuming that "new.registro_id" is indeed a new ID (not already existing in the NFI table), I would expect to get an empty resultset like you actually do. The Trigger is "BEFORE" insert and is operating on the table "Notasfiscaisitens" (NFI) which means that the "new.registro_ID" is not yet inserted into that relation. Therefore, no record matches the WHERE condition in your case and you get NULLs. best regards, André ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] unavailable database
> after executing all possible combinations of "gfix -m", "gfix -v" > still reports "Number of record level errors: 1", and "gbak -b -g > -i -v ..." still does not get over "starting transaction". I don't have any idea what happened, but suddenly the backup *did* work. This completely toils my understanding of why - after a clean reboot of the server and all users disconnected - a command would at first not work, but then suddenly would when just trying often enough. So, I made a backup, restored that to a new database, and this one seems to work so far. I hope there are no hidden surprises. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] unavailable database
Oh, I expressed that wrong, I think. What I really meant was "(so, no problems with distributed files) and also non with limbo transactions. I am not using shadows nor 2-phase transactions. > On Wed, 9 Jan 2013 15:19:43 +0100, André Knappstein > wrote: >> - database only has 1 file (no limbo problems) > A single database file can have limbo transactions, I assume you are > referring to shadow problems instead. > Mark > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] unavailable database
> How would I best go about trying to fix this? I forgot some things: - of course I brought the database online again after backup first failed. - users are logging in with explicit usernames and roles. nobody and no software is using SYSDBA besides me - database only has 1 file (no limbo problems) gfix -v -full reports "Number of record level errors:1" so does "gfix -m ... ", "gfix -m -i ...", "gfix -m -full -i ..." after executing all possible combinations of "gfix -m", "gfix -v" still reports "Number of record level errors: 1", and "gbak -b -g -i -v ..." still does not get over "starting transaction". ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] unavailable database
In case somebody's just reading by and has a clue, don't hold back :-) For the first time since working with Firebird I'm probably about losing data. Here's what I did to successfully break my precious: - shutdown -f while 1 one user who told he's not working was in fact still working with the database - starting a backup which was expected to run some 7 minutes - terminated the process after some 30 minutes (no CPU usage of gBak.exe for more than 20 minutes) Now an ODBC connection test works fine. Connecting via Jiri's .net provider times out (CPU 50% for 2++ minutes) Connecting via IBExpert times out (CPU 50% for 2++ minutes) >From 6 databases on that server, only 1 is making trouble. I made a file copy of the offending file to C:\Current.fdb. gfix -v -n C:\Current.fdb results in "unavailable database" (user&pw supplied...) gfix -v -n localhost:C:\Current.fdb results in a new child process fb_inet_server.exe at 50% for a short time (~2 seconds), a new blank line in the command window. gbak -b -v C:\Current.fdb C:\Current.fbk results in "unavailable database" gbak -b -v localhost:C:\Current.fdb C:\Current.fbk a new process gbak.exe with constantly 0%, a new file "C:\Current.fbk" with 0 bytes, a new child process fb_inet_server.exe at 50% for a loong time, some update to the database, because c:\current.fdb gets a new file-timestamp. ouput says: "readied database... " "creating file ... " "starting transaction" nothing more happens. How would I best go about trying to fix this? I have a good backup which is 4 hours old. There are worse things in the world than having to revert back to that, but if possible I'd try to restore this one. FB 1.5 Classic Server on W2K3 TIA André ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Looking for a reporting tool.
Just curious: why not remain with Crystal Reports, especially if it seems to be doing all you need? It works very very fine for me. > Good morning! > I am looking for a reporting tool that can run against Firebird and > produce reports that are user executed if possible and can have > parameters entered in similar to the way Crystal Reports would work. > We are a small company so if there are any free reporting tools available, > that would be great!! > Thank you very much for any help/direction that you can provide. > Jim
Re: [firebird-support] Permission denied
Are all clients connecting using the same protocol? And the same adress resolution? Can, at the same time you are being denied the permissions, other users (re-)connect and work without problems (== is this only a problem when you are trying to access via SYSDBA or OWNER? > I forgot to mention it is classic server 2.1. >> >> Your database is accessed through the Firebird server: does Firebird >> server have access rights to the database file? >> > It seems that cannot be the problem because after the system is rebooted > everything works fine. The isql program can access the database and > remote programs can also access the database. The database is not > frequently accesses, maybe once or twice a day at the most. Then a batch > job runs gbak locally to backup the database. It will work for a week or > two. Suddenly the backup fail with the Permission denied error. No > permissions have change for the database file but suddenly the database > cannot be accesses by the local user. > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] Application users and FireBird users
> However, there is also a pseudo-role named PUBLIC that you can grant > rights to and all users get the privileges granted to PUBLIC. plus there was a (bug/feature?) in 1.5 where any user who logged in with lower case username but with the correct password has been granted PUBLIC rights. SYSDBA had full privileges sysdba hat PUBLIC privileges ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Application users and FireBird users
Role privileges == additional privileges. No Role == no additional privileges. > On 06/12/2012 07:11 pm, Doug Chamberlin wrote: >> Don't forget you also have to change the application to have each user >> login using the new role. > Will do. Thanks, this is interesting, all this is new terrain for me. > Does it mean that a single user can login using various roles that may > have been setup? What does the default blank role for sysdba mean? > That if the data-entry user if they login without a role will have the > same powers as sysdba? > Please advise > Thanks and regards > Bhavbhuti > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Application users and FireBird users
Yes, that basically is how it works; at least for me :-) I do assign new users to a given role, sometimes to more than one role. This can be done using DML. The administrator on site (which happens to be myself in my case *g*) only has to set up the client logic (for example creating some new DSNs per user if ODBC is used) > Thanks Andre for your views. I think I am getting an idea on how I might > go about your suggestion to RL_Writers, which I can create on the > development database copy. This role then can be taken to the production > database via the utility that manages other DML operations. > The users that are already there or a newly created then can rely on > this RL_Writers without me worrying on the production copy of who the > end users might be. Please correct me if I have not followed your > suggestion. > Let me see how this goes. > Kind regards > Bhavbhuti > On 03/12/2012 07:06 pm, André Knappstein wrote: >> >> It's not possible the way you describe it. You can't have any group >> of users, which are not SYSDBA, automatically privileged for DML as >> soon as a relation is created or its metadata was changed. >> >> You need to explicitely "grant" the privileges. >> >> For this purpose though, you can easily set up *one* role, for example >> "RL_Writers". You still need to create all users, and assign them to >> that role. Whenever you create a new relation, all you need is one >> more command "grant all on to RL_Writers". >> >> have fun! >> André >> >> > Hi all >> >> > Presently I do not create users in FireBird and rely on the sysdba >> > login to auto update data structures and user data-entry from the >> > application. I know this is not good but had to get things forward. >> >> > I need a couple of types of users in FireBird. One, are the >> > data-entry users. They should be able to add, edit, delete and >> > query records via the application. What kind of a FireBird user is >> > this? They need to get access to all the tables and fields >> > automatically as and when created/updated for data purposes. >> >> > Another, is the sysdba. This user is required when the app is >> > updated and the DML are executed via the utility to update >> > structures. Also required for connecting to the database for >> > updating the Remote Views to work on the data in FireBird in the >> > application. Password to this sysdba is a concern too, I am fine to >> > just change it and not create a full admin on the production >> > database but am open to suggestions but am looking towards a lot of >> > automation using DML via a utility just for this purpose. >> >> > Ideas and advise please. >> >> > Thanks and regards >> > Bhavbhuti >> >> mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Application users and FireBird users
It's not possible the way you describe it. You can't have any group of users, which are not SYSDBA, automatically privileged for DML as soon as a relation is created or its metadata was changed. You need to explicitely "grant" the privileges. For this purpose though, you can easily set up *one* role, for example "RL_Writers". You still need to create all users, and assign them to that role. Whenever you create a new relation, all you need is one more command "grant all on to RL_Writers". have fun! André > Hi all > Presently I do not create users in FireBird and rely on the sysdba > login to auto update data structures and user data-entry from the > application. I know this is not good but had to get things forward. > I need a couple of types of users in FireBird. One, are the > data-entry users. They should be able to add, edit, delete and > query records via the application. What kind of a FireBird user is > this? They need to get access to all the tables and fields > automatically as and when created/updated for data purposes. > Another, is the sysdba. This user is required when the app is > updated and the DML are executed via the utility to update > structures. Also required for connecting to the database for > updating the Remote Views to work on the data in FireBird in the > application. Password to this sysdba is a concern too, I am fine to > just change it and not create a full admin on the production > database but am open to suggestions but am looking towards a lot of > automation using DML via a utility just for this purpose. > Ideas and advise please. > Thanks and regards > Bhavbhuti
Re: [firebird-support] size specification either missing or incorrect
Hmmmh... how embarrassing. Looks like I got it solved. First of all, replacing "-pas" by "-pass" did not solve the problem. Today I got quite distressed because not any order of the options, with our without the -y switch, did work. I always received the error message. To make a long story short, it seems that it somehow has to do with how I copied the command into the win-7 console (cmd). I found that copying the command into the console was leading to the problems, while completely typing it myself did not. I went as far as analysing the copied command sequence for hidden special characters or weird transcriptions of blanks; nada. I give up. For now, I will just type each restore command sequence into the console. ASAP I will try to create something pre-prepared using Jiri's provider >> -pas). This error is occurring because of confusion with -pa[ge_size]. > I will try this ASAP. However, the help on gbak.exe should then be > updated. It reads "-PAS(SWORD) Firebird password. (gbak.exe -? on > 2.5.2 32-Bit). have fun, André
Re: [firebird-support] Deadlock when 2 session update the same 01 row
Thank you for the time to explain so well. I always appreciate your detailed explanations on such topics, be they historical or technical. Now it's clear why this exception is filed under "deadlock" at times. > On Wed, Nov 28, 2012 at 9:42 AM, André Knappstein < > knappst...@beta-eigenheim.de> wrote: >> FWIW there seems to be a related question in the .net list. >> Jiri confirmed that the default is "NoWait". This makes my >> misunderstanding of the term "deadlock" complete, because in NoWait I >> usually get an exception right off, which is all but a deadlock in the >> way I used to understand it. >> > There's a some history and a bit of theory here. > The history is that in the early 1980's DEC had two groups writing > relational databases. One group used traditional record-lock based > concurrency control and one used MVCC. Guess which one Jim Starkey led, > and for extra points, how there happened to be two projects. At the time > DEC was pushing VAX clusters, which were groups of separate machines that > shared storage through a thing called the HSC. Part of the VAX Cluster > architecture was a distributed lock manager. Very clever bit of software, > which made locking possible in shared nothing, but locks were both slow and > available in limited quantities. Anyway, that's why a non-lock based > solution was attractive. > Having competing projects has some benefits - easy to do performance > testing side by side, and develops some real identification with the > project. Releasing two relational databases and making customers choose > between them was less attractive, so a rule emerged. The interfaces had to > be identical. Not just the API, but the system tables, and the error codes > - everything had to be consistent, so you could take an application running > against one database, change the logical name that lead to the database > shared library and magically you'd be running the other database. And, of > course, a common backup format so you back up one and restore as the other. > Error codes were a real trial. Some errors only occur with record-locking > system (e.g. out of locks) and some only with MVCC - update conflict. The > two project leaders and I met with a mediator, David Hartzband - he has a > doctorate in philosophy from Heidelberg (the one in Germany), is a database > expert, and worked as a bouncer at the Buckets of Blood bar outside the > gates of a steel mill in either Pittsburg PA or Youngstown Ohio. I forget > which. A man who could be very convincing. Errors were divided into > groups by the way a program could deal with them. Codes common to both > system and the top code for a group of similar codes became primary error > codes. Codes that were specific to a database were secondary codes. > That's how Update Conflict got to be a secondary code under Deadlock. > The theory is that if you encounter an update conflict, you're going to see > that same conflict if you retry your transaction before the other > transaction commits. That's pretty much live-lock: try, fail, try again, > fail again, use all the system resources and don't get anywhere. That's > why the original default was WAIT. If you wait, then there's a chance your > next attempt will succeed. Yes, it's a problem if the other guy is running > long update transactions (why would he do that?) and you've got a user > waiting for an answer. Lots of interfaces use NO_WAIT as the default. > Good luck, > Ann > [Non-text portions of this message have been removed] > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++ > Yahoo! Groups Links
Re: [firebird-support] Deadlock when 2 session update the same 01 row
FWIW there seems to be a related question in the .net list. Jiri confirmed that the default is "NoWait". This makes my misunderstanding of the term "deadlock" complete, because in NoWait I usually get an exception right off, which is all but a deadlock in the way I used to understand it. Anyway, to change configuration from NoWait to Wait would most obviously require "... = FbTransactionBehaviour.Wait;" >> t_behaviour = FbTransactionBehavior.NoWait; > should that be t_behaviour = FbTransactionBehavior.Wait? have fun! André
Re: [firebird-support] Deadlock when 2 session update the same 01 row
>> FbTransactionBehavior t_behaviour; >> FbTransactionOptions t_options; >> >> t_behaviour = FbTransactionBehavior.NoWait; >> t_options.TransactionBehavior = t_behaviour; >> >> FbConnection conn = new FbConnection(); >> conn.BeginTransaction(t_options); >> > should that be t_behaviour = FbTransactionBehavior.Wait? Depends on the transaction it should be applied to, A or B :-) Seriously, I thought that "Wait" is default and that this default leads to a behaviour the OP wanted to change. It is not clear for me, what exactly is meant by "deadlock". Personally, if I had this problem, I'd probably want to have "NoWait", raise an exception, catch and deal with it. have fun! André
Re: [firebird-support] Deadlock when 2 session update the same 01 row
Somewhere along this: FbTransactionBehavior t_behaviour; FbTransactionOptions t_options; t_behaviour = FbTransactionBehavior.NoWait; t_options.TransactionBehavior = t_behaviour; FbConnection conn = new FbConnection(); conn.BeginTransaction(t_options); have fun! André > Thanks for your reply, > I am using VS2005 (FirebirdSqk,Data.FirebirdClient), how can I change > behavior of the Database the same as in Oracle? > > Best Regards, > > From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær > Sent: Tuesday, November 27, 2012 4:07 PM > To: 'firebird-support@yahoogroups.com' > Subject: RE: [firebird-support] Deadlock when 2 session update the same 01 > row > > >>I am newbie to Firebird, I am working on Oracle and porting a small module > to Firebird. We >>face with very strang problem: >> >>When session A and B update a row in a table at the same time. If A goes > first, and while A >>does not commit or rollback yet, B will update the same row. In Oracle, B > will be blocked >>until A commit or rollback without any error. But In Firebird, B will get > Deadlock >>immediately. >> >>Can I config FB to do exactly like Oracle does? Or, It is a bug? > Hi and welcome, Nguyen! > No, it is no bug. This is called 'lock resolution' and belongs to your > transactions (strictly speaking, this is a lock conflict, not a deadlock). > So, you could have isc_tpb_nowait on transaction A and isc_tpb_wait on > transaction B and then transaction A would behave like you report your > system is doing, whilst transaction B would behave as you would like it to > behave. > How to set these things depend on what kind of components you use. Using IB > Objects, I would simply set TIB_Transaction.LockWait to true (though I've > never actually done this myself), other components will have different ways > of doing this. > HTH, > Set > [Non-text portions of this message have been removed] > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++ > Yahoo! Groups Links
Re: [firebird-support] size specification either missing or incorrect
thanks, Helen. > The minimum abbreviation for the -password switch is -pass (not > -pas). This error is occurring because of confusion with -pa[ge_size]. I will try this ASAP. However, the help on gbak.exe should then be updated. It reads "-PAS(SWORD) Firebird password. (gbak.exe -? on 2.5.2 32-Bit). And still it is strange that this error is raised sometimes, and sometimes not. Using the same gbak - a couple of times on the very same backup in the very same location. Strange things around my databases tend to make me nervous ;-) > In older versions, it worked best to place the -user and -password > parameters at the end for a restore. I'm not aware that this still holds for > 2.x versions, though. I'll give it a try, definitely. > I guess you remembered that you'll get an error if you try to > output the -verify text to a file name that already exists? yes. Existing X_.txt files have been erased before. ->>> > By the way, if you are using the gbak instructions in Ch. 39 of The > Firebird Book Second Edition that was released on the IBPhoenix DVD > #21 in August, you should know that two lots of Errata relating to > that chapter have been fixed since then. You can refresh your book > at the IBPhoenix web site. <<--- > ./heLen Did not have the time for all of this, so far. I still need to get the original (== buy the DVD?). I will probably only read it though, when I have my new tablet. I think I am too conservative for reading a really interesting book on a usual screen. Thanks for all your work! Without the first edition of the book I'd definitely have been lost! best regards, André
[firebird-support] size specification either missing or incorrect
Hi Gang, preparing finally for the leap of faith and switch from 1.5.6 to 2.5.2 on all applications and databases. So far everything is still nice. I followed documentations and postings on the -Fix_FSS_M switch. I never made too wide use of UDFs, so I don't expect too many problems there. No I found the following: On occasions (not regularly, but quite often) I get the "size specification either missing or incorrect...". I checked old postings but did not find something for my case. I am not specifying file sizes. Each database consists of only 1 file. I have the impression that the order of parameters in gbak.exe can influence the quota of errors, but can't lay a hand on it. For example, the following mostly works, but not always: gbak -rep Z:\Local\Daten\BU_SFiles Z:\Local\Daten\SFiles -user SYSDBA -pas masterkey –y Z:\Local\Daten\X_SFiles.txt -v -FIX_FSS_M WIN1252 while the next one sometimes works, but in most cases not: gbak -rep Z:\Local\Daten\BU_SFiles Z:\Local\Daten\SFiles -user SYSDBA -pas masterkey -v -FIX_FSS_M WIN1252 –y Z:\Local\Daten\X_SFiles.txt note please, that I only change the order of parameters. Also, this only happens when restoring from 1.5.x backup to 2.5.2 server. Restoring from functional 2.5.2 backups never fails with any error. One thing I can definitely say is that in several 100 occasions, gbak never fails if I drop the -y option completely. While this is not critical for deciding if I will upgrade or not, I'd like to know if this is just a hint on something more serious not working with my upgrade plan. Thanks, André ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Is windows better than Linux for Firebird 2.5 64 bit?
That's a welcome thread and I would like to jump it, because I have the sameinterest/question. I don't want to spam the support list, so please, what's the best place to ask for available (paid) services around Firebird/Linux combinations? We are talking about a middle-sized company here. 50 seats. > Hello, trskopo! > Tuesday, November 6, 2012, 10:05:00 AM, you wrote: t>> Hi all, t>> I am considering moving from windows to linux, but not sure if it is a good idea. t>> Any one can give me a hints about this matter? t>> Is it Linux better than windows on the same hardware, regarding performance/security/etc? t>> And if it's better, what distro should I choose and what file system for database file? > 1. Windows can be better or slower than Linux on same hardware, > depends on hardware and existing drivers for hardware for Linux and > Windows. Difference can be up to 30% of performance. > 2. You can choose linux distro by your preferrence. If you don't know > any Linux well, then, you will have troubles. > 3. file system performance on linux, and performance of linux itsef > depends on lot of factors - journaling, barrier, kernel, and hardware > drivers. > Thus, the only choice for you is to test it by yourself. mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Re: sp case statement
> This is just a simplified example. There are multiple input > parameters and the where clause will get more complex. Do I have to > live with having the stored procedure check every record in my example? First, check if what you want to do can be done by other means, supposedly by changing the logic in the application. As long as you only give a simplified example, where A matches A, B matches B, C matches C only you can decide if this is possible. Then, if no change in the logic behind is possible, check out "execute statement". Only recently I heard from the experts that one should not make too wide use of it. I have in fact only 2 or 3 places where I am using it, where I was mapping old data structures from another database into my Firebird databases. I have too little time to rewrite the structure and "execute statement" is working fine for me. "execute statement" was already available in 1.5.x André
Re: [firebird-support] Can't attach to password database and other problems with existing FB install
Once I saw this or similar, after a local administrator was playing too much with the built-in privileges for a folder. A folder has been created with *only* Administrator and explicit user groups. Built-in groups and built-in users had been deleted or restricted out of paranoia. The users "system", "local service" and "network service" also had been deleted. Then the privileges of that folder have been copied to all child folders. The letter D:\ in your posting suggests it is on a separate partition. Make sure that the typical built-in users do have the appropriate privileges and that nobody was moving folders around without also correcting the references. > Dear All, > My program has been working without problems on the computer of one > of my users. He is running Windows XP with FB 2.1.1. > Something has happened, and now we're getting the following error message: > "-902: can't attach to password database". > The firebird service is running and seemingly everything is intact in the FB > install directory. > So I thought about removing and reinstalling Firebird. > When I ran the uninstaller I got the following error message from > the Innosetup uinstaller: (message translated from my language) > Setup doesn't have rights to access (or can't access) > D:\Firebird_2_1\uninst001.dat. Error code: 5 > Uninst001.dat is there and I can freely rename or work with the > Firebird folder, so it doesn't seem to me that something else would be > locking the files or folder. > Any ideas on what may be going on? > Thanks! > [Non-text portions of this message have been removed] > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Win8 - ODBC Administrator stopped working
Ah! Thank you. I recently changed the reply-to entry. For the next few weeks it is probably better to receive duplicates. My client automatically filters double entries. What will I have to do - later - to kick out one adress? And about ODBC... I am ODBC user, but my understanding of the details are quite limited. I am also a regular voter, but quite often I don't understand politicians ;-) > At 01:40 AM 13/10/2012, André Knappstein wrote: >>I am posting this *here* for 2 reasons: >>1. obviously I got myself out of the membership of the ODBC >>devel list somehow > No, you tried to post using andre.knappstein@, which was not > subscribed. (You were subscribed under knappstein@). I approved > your waiting message and subscribed andre.knappstein. You now have 2 > subscriptions so will probably see duplicated messages. I can set > one to No Mail if you contact me and let me know which one. >>2. The ODBC list is for developers only while my request is a support >>question, is it not? :-) > With ODBC, it's not quite the case. ODBC users are assumed to > understand what ODBC is all about so, if problems occur with the > Firebird drivers, you can share them with the other developers on > that list and figure out whether it's possibly a bug. The > coordinator, Alexander Potapchenko, also watches the list. > You seem to have a platform compatibility problem here that has no relevance > in firebird-support. > ^ heLen ^ > > ++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Win8 - ODBC Administrator stopped working
I am posting this *here* for 2 reasons: 1. obviously I got myself out of the membership of the ODBC devel list somehow 2. The ODBC list is for developers only while my request is a support question, is it not? :-) Installed the latest driver 2.0.1.152 on Win-8 64-Bit. Trying to add a 64-Bit DSN (from \System32\ODBCAD32.exe) I will receive the message "ODBC administrator stopped working". For some reason the form to enter connection parameters can not be displayed. Trying to add a 32-Bit DSN (from \SysWoW64\ODBCAD32.exe) everything is fine. But I can (of course?) only specify the 32-Bit fbclient to be used, which then will not work in the x64 edition of our application. I don't have an idea where to look and for what. Does somebody have an idea what I can try next? It must be somehow related to something which I had installed on each Win-7 station, but which maybe is missing on a new Win-8 station: VC++ runtimes are installed: - 2005 (x86) - 2008 (x86 and x64) - 2008 SP1 (x86 and x64) - 2010 (x86 and x64) Thanks! André ~~ mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
