Re: [firebird-support] What is the Newest Firebird Release

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> At http://www.firebirdsql.org/en/downloads the available release of
> Firebird to download is 2.5.2.

2.5.2 Update 1 is the latest official/production-ready release.

> Somebody mentioned a 2.5.3 release. Is there a 2.5.3 release? I exist,
> where can I download it?

Only as a snapshot build.
http://www.firebirdsql.org/en/snapshot-builds/



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] TempCacheLimit in Super Classic

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> I would like to know if the TempCacheLimit parameter in SuperClassic is
> like Classic or like SuperServer? So if it's per client connection or
> it's for server process?

As in Classic, thus per connection.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Very long transaction start after deleting a big number of records.

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> On 25/07/2014 06:46, brucedickin...@wp.pl [firebird-support] wrote:
>>
>> Hi,
>>
>> 1. I've deleted 50 records from TABLE A. I've commited transaction.
>> 2. I made SELECT * FROM TABLE A. Transaction was starting for a very
>> long time.
>>
>> I just wanted to make sure that there is no way to prevent this
>> behaviour? I mean, I can not delay somehow  clearing of this garbage?
>>
>> As far as I understand this article:
>> http://www.firebirdsql.org/manual/gfix-housekeeping.html
>>
>>
>> there is no such possibility becasue:
>>
>>
>> "Whenever a subsequent transaction reaches garbage from a /committed/
>> transaction, that garbage is automatically cleared out."
>>
> So it appears. Essentially part of the cost of doing the deletion is
> palmed off onto some later user, who is faced with unpredictable delays
> in consequence. For example an overnight housekeeping job can result in
> the first end user transaction of the day taking orders of magnitude
> longer than usual (and timing out and crashing, if its timeouts are set
> to something reasonable).
>
> You can avoid this cost being passed onto the poor user who through no
> fault of his own is the first to follow the housekeeping by including
> the "SELECT * FROM TABLEA" as part of the cleanup job, thus putting the
> cost back with its owner. In other words you can't delay the garbage
> collection, but you can make it happen earlier so that it's predictable.

SELECT COUNT(*) might be better because all pages are read and only a 
single value is transferred to the client. And the whole only works as 
advertised when old record versions are no longer "interesting" to other 
concurrent transactions.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] using FSQL with embedded DB

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

>   Can FSQL http://mujweb.cz/iprenosil/interbase/fsql.htm connect to
> embedded db on client machine where firebird is not installed.

Yes. You simply have to make sure that FSQL is loading the embedded 
library and do not use a remote connect string, even not via 'localhost' 
on the same machine.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] unwanted triggers present

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hi,

> in my db some tables have strange triggers: 'CHECK_X' ACTIVE POSITION 1
> AFTER UPDATE or AFTER DELETE where the 'X' is a number. All these
> triggers are empty (seeing them with flamerobin).
>
> I tried to delete them but after this i have had to restore a backup
> because the db was malfunctioning.
>
> It was originally created with interbase and then moved to firebird.
>
> The DB works properly but these 'presences' are very odd.

This are internal/system triggers used by Firebird to implement 
field-based CHECK constraints.




-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hi,

> Hi guys,
>
> I'm looking for some ideas how to find what is causing this lock.
>
> I have 2 production databases that process multiple insert/update
> statements. There is one background process that does all these
> modifications 24h/day.
>
> The problem is that with one of these databases almost every morning
> when client tries to connect it runs series of inserts in one table. And
> during that process the insert is blocked by something.
>
> The other database experiences similar problem but there the lock is on
> another table that as I said earlier modified trough the whole day.
>
> If such a lock happens it locks all similar requests to these tables.
>
> I need to shutdown the main process and run Sweep in order to remove the
> leftover from all transactions executed after initially locked statement
> transaction.
>
> So I'm looking for ideas how to diagnose this problem? what I can do to
> pinpoint the source of the problem?
>
> I'm using Firebird 2.1.6 Classic on Linux.
>
> I have same databases on other production setups that does not have this
> problem. The difference there is that they don't have that background
> process running on these setups.

I would check your transaction settings for both, the non-background 
process requests and the background task. E.g. WAIT vs. NO WAIT, read 
requests using READ COMMITTED isolation level with NO REC VERSION etc.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] BLOB

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> I recorded the process of backup , if anybody would be so kind to help
> me, please take a look at :


> FireBirdBackup 
>   
> image 
>   
>   
> FireBirdBackup 
> This feature is not available right now. Please try again later.
>   
> View on youtu.be 
>   
> Preview by Yahoo
>
>
> the command line is :
>
> "C:\Program Files (x86)\Firebird\Firebird_2_5\bin\gbak.exe" -b -g -v
> -user SYSDBA -password masterkey xx.xx.xx.14:e:\fdb\gestoc_asolo.FDB
>   gestoc_asolo.fbk

Are you running gbak from your local client machine against a remote 
server/database and creating the backup file on your local machine? I If 
so, this is not a good idea from a performance perspective.

And even when doing that "locally" on the Firebird server, try to run 
the backup through the Services API (services manager) to by-pass the 
TCP stack.

For example:

gbak -b -g -v -service xx.xx.xx.14:service_mgr -user sysdba -password 
masterkey e:\fdb\gestoc_asolo.fdb e:\backup\gestoc_asolo.fbk




-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] BLOB

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

>   
> I run the backup automatically each night (Windows scheduled tasks), for
> a few of my customers, via the Internet.

Ok, even worse.

> And yes, I need the backup file locally, on my system.
>
> As I mentioned before, if the backup lasts for 10 minutes of which
> log_tables takes 9 , no problem at all, FireBird works like a charm for
> me (with Delphi XE5), I was just asking why this huge difference between
> backing up tables with or without BLOB tables.

Can you please try my previously posted gbak call, because this will 
force to run everything on the remote machine. Just to get an idea on 
how much this is faster.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] no permission for read-write access to database

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> I  have  one  customer  having  this  error message very often when he
> starts  software  after he boots up his computer which is as server as
> well
>
> "no permission for read-write accesstodatabase
> C:\DATA\CUSTOMERS.FDB"
>
> He runs the software on Windows 8.1 Professional Version.
>
> What could be a reason for this problem?

* Are we talking about Firebird Embedded or a real Firebird server process?
* If the later, have you tried using a TCP/IP connection, even locally?


As the error states, for whatever reason, it doesn't have read/write 
permission on the database file.

So, with Embedded, make sure that the user running the application has 
proper permissions on the folder/file. With a regular Firebird server 
setup, check if the Windows account used by the Firebird server Windows 
service has proper privileges.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.






++

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] Should autosweep be happening on that database?

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> I did some reconfiguration on a database yesterday, so I`m monitoring
> the logs for any issues and I noticed something strange :
>
>Mon Oct 27 16:54:25 2014
>  Sweep is started by SYSDBA
>  Database "orbis-aton"
>  OIT 21080, OAT 21081, OST 20456, Next 44177
>
> (yes, I know it`s a bad idea but all database connections are by SYSDBA)
>
> I don`t remember running a sweep (logs say I wasn`t even logged in at
> the time) and nobody else has access (or knowledge, for that matter) to
> run a sweep on that machine so I got curious.
> Logs say an unexpected sweep has been running basically every day
> (sometime between 5 and 10 pm, which is apparently when it hits the
> number of transactions). I have a manual sweep at 5:40am in cron and I
> checked - configuration is correct and runs as expected. I also
> double-checked gstat :
>
>
> Database header page information:
>  Flags   0
>  Checksum12345
>  Generation  51454
>  Page size   16384
>  ODS version 11.2
>  Oldest transaction  21357
>  Oldest active   50561
>  Oldest snapshot 49520
>  Next transaction51318
>  Bumped transaction  1
>  Sequence number 0
>  Next attachment ID  130
>  Implementation ID   24
>  Shadow count0
>  Page buffers0
>  Next header page0
>  Database dialect3
>  Creation date   Oct 26, 2014 12:28:26
>  Attributes  force write
>
>  Variable header data:
>  Sweep interval: 0
>  *END*
>
>
> Shouldn`t automatic sweep be disabled or have I misread the
> documentation? FB version is 2.5.3.

Yes, by setting the sweep interval to 0, automatic sweeping should be 
disabled. There is also: http://tracker.firebirdsql.org/browse/CORE-4100 
(marked as fixed in 2.5.3) but you are mentioning that you are using 
2.5.3, although I'm not entirely sure if CORE-4100 applies to sweep 
interval = 0 anyway.

You could use the Trace API to trace services API requests to get a 
clearer picture on what client application is causing the sweep in case 
it has been triggered manually.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Should autosweep be happening on that database?

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> I`m not sure if running a 24/7 trace is a good idea (database is under
> relatively heavy load). Anything else I could do to help?

It might be ok with a proper include filter at service level from a 
Trace API POV.

The following trace configuration should be fully dedicated to sweeping 
only:


   enabled true
   print_perf true
   log_sweep true
   exclude_filter %RDB$%
   time_threshold 0


   enabled true
   log_services true
   include_filter "Repair Database"



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

> btw I am noticing that the unexpected sweep is about 27000-3
> transactions after the scheduled one. I could send a log if that helps
> in any way. I set the interval to 10 and in a day or two I`ll know
> if it still runs by its own.
>
> 2014-10-27 21:46 GMT+02:00 Thomas Steinmaurer t...@iblogmanager.com
>  [firebird-support]
>  >:
>
> __
>
>  > I did some reconfiguration on a database yesterday, so I`m monitoring
>  > the logs for any issues and I noticed something strange :
>  >
>  > Mon Oct 27 16:54:25 2014
>  > Sweep is started by SYSDBA
>  > Database "orbis-aton"
>  > OIT 21080, OAT 21081, OST 20456, Next 44177
>  >
>  > (yes, I know it`s a bad idea but all database connections are by
> SYSDBA)
>  >
>  > I don`t remember running a sweep (logs say I wasn`t even logged in at
>  > the time) and nobody else has access (or knowledge, for that
> matter) to
>  > run a sweep on that machine so I got curious.
>  > Logs say an unexpected sweep has been running basically every day
>  > (sometime between 5 and 10 pm, which is apparently when it hits the
>  > number of transactions). I have a manual sweep at 5:40am in cron
> and I
>  > checked - configuration is correct and runs as expected. I also
>  > double-checked gstat :
>  >
>  >
>  > Database header page information:
>  > Flags 0
>  > Checksum 12345
>  > Generation 51454
>  > Page size 16384
>  > ODS version 11.2
>  > Oldest transaction 21357
>  > Oldest active 50561
>  > Oldest snapshot 49520
>  > Next transaction 51318
>  > Bumped transaction 1
>  > Sequence number 0
>  > Next attachment ID 130
>  > Implementation ID 24
>  > Shadow count 0
>  > Page buffers 0
>  > Next header page 0
>  > Database dialect 3
>  > Creation date Oct 26, 2014 12:28:26
>  > Attributes force write
>  >
>  > Variable header data:
>  > Sweep interval: 0
>  > *END*
>  >
>  >
>  > Shouldn`t automatic sweep be disabled or have I misread the
>  > documentation? FB version is 2.5.3.
>
> Yes, by setting the sweep interval to 0, automatic sweeping should be
> disabled. There is also:
> http://tracker.firebirdsql.org/browse/CORE-4100
> (marked as fixed in 2.5.3) but you are mentioning that you are using
> 2.5.3, although I'm not entirely sure if CORE-4100 applies to sweep
> interval = 0 anyway.
>
> You could use the Trace API to trace services API requests to get a
> clearer picture on what client application is causing the sweep in case
> it has been triggered manually.
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>
>
>
>
> 

Re: [firebird-support] Should autosweep be happening on that database?

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Well, I`ve done two things since the last post
>
> 1 I set the sweep interval to 10 (I assume I`m far from reaching
> that on a normal day)
> No change - Yesterday, another unexpected sweep at 5:08:03 pm
>
> 2 I started a trace session with the suggested config (changing only DB
> alias, as instructed - I don`t really have much experience with the
> trace API)
> Today a sweep happened at 5:08:56 (about 28000 transactions after the
> morning manual sweep). It`s suspiciously close as a time frame, but
> looking back at the last month or so - sweeps are distributed in the
> 5-10pm range, so I guess it could be a coincidence.
>
> Trace log ti this moment is as follows :
> Trace session ID 2 started
> 2014-10-28T18:06:59.8670 (9756:0x7f5e704849e0) TRACE_INIT
>  SESSION_2
>
>
> 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) TRACE_INIT
>  SESSION_2
>
>
> 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) ATTACH_SERVICE
>  service_mgr, (Service 0x7f7d2e4578d0, firebird, internal)
>
> 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) DETACH_SERVICE
>  service_mgr, (Service 0x7f7d2e4578d0, firebird, internal)
>
> 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) TRACE_FINI
>  SESSION_2
>
>
> 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) TRACE_INIT
>  SESSION_2
>
>
> 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) ATTACH_SERVICE
>  service_mgr, (Service 0x7fdb8611b8d0, firebird, internal)
>
> 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) DETACH_SERVICE
>  service_mgr, (Service 0x7fdb8611b8d0, firebird, internal)
>
> 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) TRACE_FINI
>  SESSION_2
> I assume those entries are from when I was poking around in the
> beginning. The trace session is still active but log says nothing about
> sweeping. Wasn`t it supposed to show at least the sweep by gfix (the one
> running from cron)? I`ll try fine tune it a bit on the weekend when most
> users are gone and won`t notice. If anyone has any suggestions I could
> test to help in the mean time - they`d be appreciated.

Can you show us the changed trace config and the exact gfix call in your 
cron job?



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


>
> 2014-10-27 22:41 GMT+02:00 Thomas Steinmaurer t...@iblogmanager.com
>  [firebird-support]
>  >:
>
> __
>
> > I`m not sure if running a 24/7 trace is a good idea (database is under
> > relatively heavy load). Anything else I could do to help?
>
> It might be ok with a proper include filter at service level from a
> Trace API POV.
>
> The following trace configuration should be fully dedicated to sweeping
> only:
>
> 
> enabled true
> print_perf true
> log_sweep true
> exclude_filter %RDB$%
> time_threshold 0
> 
> 
> enabled true
> log_services true
> include_filter "Repair Database"
> 
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>
> > btw I am noticing that the unexpected sweep is about 27000-3
> > transactions after the scheduled one. I could send a log if that helps
> > in any way. I set the interval to 10 and in a day or two I`ll know
> > if it still runs by its own.
> >
> > 2014-10-27 21:46 GMT+02:00 Thomas steinmaure...@iblogmanager.com 
> 
>  > >
> [firebird-support]
>  >  
>  >  >>:
>  >
>  > __
>
>
>  >
>  > > I did some reconfiguration on a database yesterday, so I`m
> monitoring
>  > > the logs for any issues and I noticed something strange :
>  > >
>  > > Mon Oct 27 16:54:25 2014
>  > > Sweep is started by SYSDBA
>  > > Database "orbis-aton"
>  > > OIT 21080, OAT 21081, OST 20456, Next 44177
>  > >
>  > > (yes, I know it`s a bad idea but all database connections are by
>  > SYSDBA)
>  > >
>  > > I don`t remember running a sweep (logs say I wasn`t even logged
> in at
>  > > the time) and nobody else has access (or knowledge, for that
>  > matter) to
>  > > run a sweep on that machine so I got curious.
>  > > Logs say an unexpected sweep has been running basically every day
>  > > (sometime between 5 and 10 pm, which is apparently when it hits the
>  > > number of transactions). I have a manual sweep at 5:40am in cron
>  > and I
>  > > checked - configuration is correct and runs as expected. I also
>  > > doub

Re: [firebird-support] Migration from Firebird 32 1.5.6 to Firebird 64 2.5.3

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> I want to replace my Firebird 32 1.5.6 with the last  Firebird 64 2.5.3,
> on 64b
>
> All my databases was created with 32b, 1.5.6 Firebird.
>
> After installed Firebird 64, if a try to connect to database.FDB a
> received an error
>
> "database./fdb is not a valid database/".

Beside the other recommendations, before switching from Firebird 32-bit 
to 64-bit, make sure that you aren't dependent on a UDF library which 
exists only as 32-bit version.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Configuring Firebird for high transaction through put

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Run stadistic for database
> - Look for index in de big tables. Look for Depth > 2. In this case
> up Page size one step, and run database statistical.

 >2 is quite conservative these days with the available hardware 
resources and even with a 16K page size you may reach that threshold 
with indexes on moderate sized tables rather quickly. Everything <=3 is 
just fine.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Should autosweep be happening on that database?

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Trace config :
>
> 
> enabled true
> print_perf true
> log_sweep true
> exclude_filter %RDB$%
> time_threshold 0
> 
> 
> enabled true
> log_services true
> include_filter "Repair Database"
> 

The database filter expects a regular expression and orbis-aton can't be 
parsed as a regular expression upon trace session start.

Either escape the minus symbol resulting in:

orbis\-aton

or use % as a wildcard, e.g.:

%orbis%

This should now give you appropriate sweep trace events for the database 
trace configured in the  section.

> Cron calls a simple bash script with the following command for each of
> the databases :
>
> /opt/firebird/bin/gfix -user sysdba -password masterkey -sweep orbis-aton

I see now that services tracing via the  section won't result 
in any trace data when executing the sweep through gfix, because this 
doesn't go through the services manager. And the new command-line tool 
fbsvcmgr doesn't seem to support the switch for sweeping.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.




>
>
> 2014-10-29 20:39 GMT+02:00 Thomas Steinmaurer t...@iblogmanager.com
>  [firebird-support]
>  >:
>
> __
>
>  > Well, I`ve done two things since the last post
>  >
>  > 1 I set the sweep interval to 10 (I assume I`m far from reaching
>  > that on a normal day)
>  > No change - Yesterday, another unexpected sweep at 5:08:03 pm
>  >
>  > 2 I started a trace session with the suggested config (changing
> only DB
>  > alias, as instructed - I don`t really have much experience with the
>  > trace API)
>  > Today a sweep happened at 5:08:56 (about 28000 transactions after the
>  > morning manual sweep). It`s suspiciously close as a time frame, but
>  > looking back at the last month or so - sweeps are distributed in the
>  > 5-10pm range, so I guess it could be a coincidence.
>  >
>  > Trace log ti this moment is as follows :
>  > Trace session ID 2 started
>  > 2014-10-28T18:06:59.8670 (9756:0x7f5e704849e0) TRACE_INIT
>  > SESSION_2
>  >
>  >
>  > 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) TRACE_INIT
>  > SESSION_2
>  >
>  >
>  > 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) ATTACH_SERVICE
>  > service_mgr, (Service 0x7f7d2e4578d0, firebird, internal)
>  >
>  > 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) DETACH_SERVICE
>  > service_mgr, (Service 0x7f7d2e4578d0, firebird, internal)
>  >
>  > 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) TRACE_FINI
>  > SESSION_2
>  >
>  >
>  > 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) TRACE_INIT
>  > SESSION_2
>  >
>  >
>  > 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) ATTACH_SERVICE
>  > service_mgr, (Service 0x7fdb8611b8d0, firebird, internal)
>  >
>  > 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) DETACH_SERVICE
>  > service_mgr, (Service 0x7fdb8611b8d0, firebird, internal)
>  >
>  > 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) TRACE_FINI
>  > SESSION_2
>  > I assume those entries are from when I was poking around in the
>  > beginning. The trace session is still active but log says nothing
> about
>  > sweeping. Wasn`t it supposed to show at least the sweep by gfix
> (the one
>  > running from cron)? I`ll try fine tune it a bit on the weekend
> when most
>  > users are gone and won`t notice. If anyone has any suggestions I
> could
>  > test to help in the mean time - they`d be appreciated.
>
> Can you show us the changed trace config and the exact gfix call in
> your
> cron job?
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>
> >
> > 2014-10-27 22:41 GMT+02:00 Thomas steinmaure...@iblogmanager.com 
> 
>  > >
> [firebird-support]
>  >  
>  >  >>:
>  >
>  > __
>  >
>  > > I`m not sure if running a 24/7 trace is a good idea (database
> is under
>  > > relatively heavy load). Anything else I could do to help?
>  >
>  > It might be ok with a proper include filter at service level from a
>  > Trace API POV.
>  >
>  > The following trace configuration should be fully dedicated to
> sweeping
>  > only:
>  >
>  > 
>  > enabled true
>  > print_perf true
>  > log_sweep true
>  > exclude_filter %RDB$%
>  > time_threshold 0
>  > 
>  > 

Re: [firebird-support] Performance of Firebird (Superserver vs Superclassic, etc.)

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hi Todd,

> Thought I would share some experience we have gained recently.
>
> We have an application consisting of a C++ Builder front end and
> Firebird 2.5 as the backend.  Running on Windows.  The database includes
> a ton of stored procedures with some fairly intricate calculations.
>
> A typical installation might have 45,000 records in the main table, with
> another 180 tables that can have a few records to a couple of hundred
> thousand of records in each of them.
>
> We had one main calculation routine that goes through the 45,000 records
> (one by one) and runs a bunch of stored procedures that populate once
> particular table in the database.  This process was taking approx. 30
> minutes for one client.
>
> We had an idea to change the calculation to divide the 45,000 records
> into smaller chunks and processing them at the same time in different
> threads.  We are using 10 threads for this operation.  This cut the
> processing time in half from 30 minutes to 15 minutes.
>
> Also, we had always run Firebird as Superserver.  We decide to install
> Firebird as Superclassic in order to take advantage of the multiple
> processors that most of our clients have on their servers.  The
> particular client above has 4 processors on his server.
>
> After changing the Firebird installation to Superclassic, the processing
> time went down to approx. 5 minutes.
>
> Needless to say, we are very happy with the results of this.  Still
> doing some testing before getting this out to our client base.

Thanks for sharing. So that's around 7ms per record. SuperClassic and 
Classic are known to scale much, much better in SMP environments. 
Firebird 3 will be (again) a game changer in that context.

Although I might be wrong as I don't use the installer package, but 
"unfortunately" SuperServer might be the default architecture being 
installed if not chosen otherwise.

But be careful when switching from SS to CS/SC, because this is a 
completely different playground from a configuration/tuning perspective, 
e.g. cause of the page cache being local per connection in CS/SC etc. My 
architecture comparison sheet might be useful.

http://www.firebirdsql.org/file/fb25_architecture_comparison.pdf


Good luck.

-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Should autosweep be happening on that database?

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Thanks. I`ve fixed the config and started a new trace session. Now we
> wait again :)

To shorten the time to wait to see if that works in general, you could 
simply play with a test database using a similar alias thus database filter.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


> 2014-10-29 21:31 GMT+02:00 Thomas Steinmaurer t...@iblogmanager.com
>  [firebird-support]
>  >:
>
> __
>
> > Trace config :
> >
> > 
> > enabled true
> > print_perf true
> > log_sweep true
> > exclude_filter %RDB$%
> > time_threshold 0
> > 
> > 
> > enabled true
> > log_services true
> > include_filter "Repair Database"
> > 
>
> The database filter expects a regular expression and orbis-aton
> can't be
> parsed as a regular expression upon trace session start.
>
> Either escape the minus symbol resulting in:
>
> orbis\-aton
>
> or use % as a wildcard, e.g.:
>
> %orbis%
>
> This should now give you appropriate sweep trace events for the
> database
> trace configured in the  section.
>
> > Cron calls a simple bash script with the following command for each of
> > the databases :
> >
> > /opt/firebird/bin/gfix -user sysdba -password masterkey -sweep 
> orbis-aton
>
> I see now that services tracing via the  section won't result
> in any trace data when executing the sweep through gfix, because this
> doesn't go through the services manager. And the new command-line tool
> fbsvcmgr doesn't seem to support the switch for sweeping.
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>
> >
> >
> > 2014-10-29 20:39 GMT+02:00 Thomas steinmaure...@iblogmanager.com 
> 
> > > 
> [firebird-support]
> >  
> >  >>:
> >
> > __
> >
>
>  > > Well, I`ve done two things since the last post
>  > >
>  > > 1 I set the sweep interval to 10 (I assume I`m far from
> reaching
>  > > that on a normal day)
>  > > No change - Yesterday, another unexpected sweep at 5:08:03 pm
>  > >
>  > > 2 I started a trace session with the suggested config (changing
>  > only DB
>  > > alias, as instructed - I don`t really have much experience with the
>  > > trace API)
>  > > Today a sweep happened at 5:08:56 (about 28000 transactions
> after the
>  > > morning manual sweep). It`s suspiciously close as a time frame, but
>  > > looking back at the last month or so - sweeps are distributed
> in the
>  > > 5-10pm range, so I guess it could be a coincidence.
>  > >
>  > > Trace log ti this moment is as follows :
>  > > Trace session ID 2 started
>  > > 2014-10-28T18:06:59.8670 (9756:0x7f5e704849e0) TRACE_INIT
>  > > SESSION_2
>  > >
>  > >
>  > > 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) TRACE_INIT
>  > > SESSION_2
>  > >
>  > >
>  > > 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) ATTACH_SERVICE
>  > > service_mgr, (Service 0x7f7d2e4578d0, firebird, internal)
>  > >
>  > > 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) DETACH_SERVICE
>  > > service_mgr, (Service 0x7f7d2e4578d0, firebird, internal)
>  > >
>  > > 2014-10-28T18:07:57.6530 (9768:0x7f7d2e4399e0) TRACE_FINI
>  > > SESSION_2
>  > >
>  > >
>  > > 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) TRACE_INIT
>  > > SESSION_2
>  > >
>  > >
>  > > 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) ATTACH_SERVICE
>  > > service_mgr, (Service 0x7fdb8611b8d0, firebird, internal)
>  > >
>  > > 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) DETACH_SERVICE
>  > > service_mgr, (Service 0x7fdb8611b8d0, firebird, internal)
>  > >
>  > > 2014-10-28T18:08:16.7590 (9779:0x7fdb860fd9e0) TRACE_FINI
>  > > SESSION_2
>  > > I assume those entries are from when I was poking around in the
>  > > beginning. The trace session is still active but log says nothing
>  > about
>  > > sweeping. Wasn`t it supposed to show at least the sweep by gfix
>  > (the one
>  > > running from cron)? I`ll try fine tune it a bit on the weekend
>  > when most
>  > > users are gone and won`t notice. If anyone has any suggestions I
>  > could
>  > > test to help in the mean time - they`d be appreciated.
>  >
>  > Can you show us the changed trace config and the exact gfi

Re: Odp: [firebird-support] Performance of Firebird (Superserver vs Superclassic, etc.)

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hi Todd,

> There are 46,527 records in the main table, but the calculation touches
> many other tables.  Here is a copy of the analysis of the main procedure
> from IB Expert:
>
> Query
> 
>
>
> Plan
> 
>
>
> Query Time
> 
> Prepare   : 156.00 ms
> Execute   : 16.00 ms
> Avg fetch time: 16.00 ms

How does that fit with your previously mentioned ~ 5 minutes?

> Memory
> 
> Current: 9,821,456
> Max: 9,951,376
> Buffers: 75

Default buffers value of 75, which is completely undersized these days. 
Depending on the page size of the database, e.g. 8K, this results in ~ 
600K RAM usage for the page cache per connection in SuperClassic (SC) 
and Classic (CS). Max memory usage looks like that the default 8MB for 
the temp cache limit per connection is in place, again for SC/CS.

Especially for GROUP/ORDER BY statements an increased temp cache might 
result in better performance, because statements with bigger memory 
requirements to fulfill in memory sorts won't spool onto disk.

> Operations
> 
> Read   : 2,083
> Writes : 3
> Fetches: 44,488
> Marks  : 13
>
>
> Enchanced Info:
> +---+---+---+-+-+-+-+--+--+--+
> |  Table Name   |  Records  |  Indexed  | Non-Indexed |
> Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
> |   |   Total   |   reads   |reads
> | | | |  |  |  |
> +---+---+---+-+-+-+-+--+--+--+
> |CODERATES  | 43591 | 7 |   0
> |   0 |   0 |   0 |0 |0 |0 |
> |CODES  | 18127 | 4 |   0
> |   0 |   0 |   0 |0 |0 |0 |
> |CODEVALIDATIONS| 88777 | 6 |   0
> |   0 |   0 |   0 |0 |0 |0 |
> |CODEVALRATES   | 38319 | 2 |   0
> |   0 |   0 |   0 |0 |0 |0 |
> |IMPROVEMENTHEADER  | 74804 | 6 |   0
> |   0 |   0 |   0 |0 |0 |0 |
> |INCOMEHEADER   |  9842 | 1 |   0
> |   0 |   0 |   0 |0 |0 |0 |
> |LOCATION_TABLEEXT  |  2450 | 3 |   0
> |   0 |   0 |   0 |0 |0 |0 |
> |MARKETLANDHEADER   | 35886 | 2 |   0
> |   0 |   0 |   0 |0 |0 |0 |
> |MUNICIPALITIES |15 | 9 |   0
> |   0 |   0 |   0 |0 |0 |0 |
> |PROPERTYHEADER | 46527 |10 |   0
> |   0 |   0 |   0 |0 |0 |0 |
> |TAXPASSOFF |603366 |78 |   0
> |   4 |   0 |   0 |0 |1 |0 |
> +---+---+---+-+-+-+-+--+--+--+

Looks again a bit strange when looking at Records Total vs. 
Index/Non-Indexed Reads.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Performance of Firebird (Superserver vs Superclassic, etc.)

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Hi Geoff,

> Helen Borrie hele...@iinet.net.au [firebird-support] wrote:
>> At 02:36 p.m. 30/10/2014, Geoff Worboys
>> ge...@telesiscomputing.com.au [firebird-support] wrote:
> 
>>>Doesn't that depend on how they implemented the separate threads?
>>>SS does/can use different threads for separate connections to the
>>>same database, can't it?  So if the threads operate over separate
>>>connections then wouldn't SS be able to use multiple CPUs?
>> Not in 2.5 (Dmitry will correct me if I'm wrong, I hope!),
>> but I'm fairly sure it's a Yes in Fb 3.  AFAIR, in 2.5,
>> multiple CPUs will be used for connections to multiple
>> databases if they are available. We're talking here about SS.
>> SC behaves the way you describe, as far as I understand.
> 
> Hmm... Yes, my mistake, you are correct (as if there was ever
> any doubt :-)
> http://www.firebirdsql.org/file/fb25_architecture_comparison.pdf

I once did a short video with a very simple/naive SMP capability test across SS 
and SC.
http://www.iblogmanager.com/download/demos/firebird/fb25_architectures_smp.html

Although with SS internally requests to the same database from different 
connections are serialized (at least that is what Dmitry confirmed to me in the 
past), SS isn't strictly bound to a single core for a single database. There 
are other threads which may occupy other cores a bit but to a much lesser 
degree as in SC/CS for a single database.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] What is wrong with transactions here?

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Hello,

>>> Hi!
>>> 
>>> I have some lines in firebird.log like this:
>>> ===
>>> cxv.servers.netTue Nov  4 11:37:38 2014
>>> Sweep is started by SWEEPER
>>> Database "SkyNetInt"
>>> OIT 1221095, OAT 1231335, OST 1231313, Next 139981575359934
>>> 
>>> 
>>> cxv.servers.netTue Nov  4 11:37:46 2014
>>> Sweep is finished
>>> Database "SkyNetInt"
>>> OIT 1248701, OAT 1248707, OST 1248707, Next 1248709
>>> 
>>> ===
>>
>>That's housekeeping for transactions. Depending on how applications are 
>>connect
>>to the database and how the applications are handling 
>>their connects, statements and commits - you will see a gap between the oldest
>>active and the next transaction.
> 
> I don't think this can be the reason for next transaction showing
> 139981575359934 originally, Marcus - that would require there to be about 1
> million transactions per second for five years of which one of the oldest is
> still active (I would expect this to be more than Firebird can handle and
> certainly more than enough to test the patience of any user of the database).

http://tracker.firebirdsql.org/browse/CORE-3978



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Change Write Mode

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> *UPDATE*
> I have changed batch, switch -online before change write mode.
> Actually the batch is:
> "%FB_PATH%\GSTAT.EXE" -h %DB1% >> %LOG_FILE%
> "%FB_PATH%\GFIX.EXE" -shut full -force 0 -user SYSDBA -password
> masterkey %DB1% >> %LOG_FILE%
> "%FB_PATH%\GFIX.EXE" -online normal -user SYSDBA -password masterkey
> %DB1% >> %LOG_FILE%
> "%FB_PATH%\GFIX.EXE" -write sync -user SYSDBA -password masterkey %DB1%
>  >> %LOG_FILE%
> "%FB_PATH%\GSTAT.EXE" -h %DB1% >> %LOG_FILE%
>
>  From log file it's all ok:
>
>  2014_11_13  3.30.50,84
> [...]
>  Next header page0
>  Database dialect3
>  Creation dateAug 19, 2014 21:56:08
>  Attributes
>
>  Variable header data:
>  Sweep interval:2
>  *END*
>
> 
> [...]
>  Page buffers0
>  Next header page0
>  Database dialect3
>  Creation dateAug 19, 2014 21:56:08
>  Attributes force write
>
>  Variable header data:
>  Sweep interval: 2
>  *END*
>
>
> But actually force write is off:
>
>  2014_11_13 10.45.33,75
> [...]
>  Implementation ID16
>  Shadow count0
>  Page buffers0
>  Next header page0
>  Database dialect3
>  Creation dateAug 19, 2014 21:56:08
>  Attributes <<
>
>  Variable header data:
>  Sweep interval:2
>  *END*
>
>
> How is possible? Any idea? Firebird versione is 2.5.3 on Windows
> Standard Server 2008.

What data access technology are you using?



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.






++

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] Change Write Mode

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> I have different software connected to db:
> 1. Delphi + IB_Object/IBO2. Web/WCF + NHibernate + Firebird ADO.NET3. Winforms
> + Firebird ADO.NET 4. DB Access (linked to firebird with external table) + 
> ODBC
> It is possible that a data connection change write mode?

Yes. In your IBO application, what is the value of TIB_connection.ForcedWrites? 
I vaguely remember a bug there in the connection design editor flipping the 
property explicitely to False although "Default" is the default and recommended 
value for that.

--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


> Il Giovedì 13 Novembre 2014 17:48, "Thomas Steinmaurer 
> t...@iblogmanager.com
> [firebird-support]"  ha scritto:
>   
> 
> > *UPDATE*
>> I have changed batch, switch -online before change write mode.
>> Actually the batch is:
>> "%FB_PATH%\GSTAT.EXE" -h %DB1% >> %LOG_FILE%
>> "%FB_PATH%\GFIX.EXE" -shut full -force 0 -user SYSDBA -password
>> masterkey %DB1% >> %LOG_FILE%
>> "%FB_PATH%\GFIX.EXE" -online normal -user SYSDBA -password masterkey
>> %DB1% >> %LOG_FILE%
>> "%FB_PATH%\GFIX.EXE" -write sync -user SYSDBA -password masterkey %DB1%
>>  >> %LOG_FILE%
>> "%FB_PATH%\GSTAT.EXE" -h %DB1% >> %LOG_FILE%
>>
>>  From log file it's all ok:
>>
>>  2014_11_13  3.30.50,84
>> [...]
>>      Next header page    0
>>      Database dialect    3
>>      Creation date        Aug 19, 2014 21:56:08
>>      Attributes
>>
>>      Variable header data:
>>      Sweep interval:        2
>>      *END*
>>
>> 
>> [...]
>>      Page buffers        0
>>      Next header page    0
>>      Database dialect    3
>>      Creation date        Aug 19, 2014 21:56:08
>>      Attributes force write
>>
>>      Variable header data:
>>      Sweep interval:    2
>>      *END*
>>
>>
>> But actually force write is off:
>>
>>  2014_11_13 10.45.33,75
>> [...]
>>      Implementation ID    16
>>      Shadow count        0
>>      Page buffers        0
>>      Next header page    0
>>      Database dialect    3
>>      Creation date        Aug 19, 2014 21:56:08
>>      Attributes <<<<<<<<<<<<<<
>>
>>      Variable header data:
>>      Sweep interval:        2
>>      *END*
>>
>>
>> How is possible? Any idea? Firebird versione is 2.5.3 on Windows
>> Standard Server 2008.
> 
> What data access technology are you using?
> 
> 
> 
> -- 
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
> 
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
> 
> 
> 
> 
> 
> 
> ++
> 
> 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
> 
> 
> 
> 
> 
>   

Re: [firebird-support] Backup/Restore Required ???

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Wondering if a Backup/Restore is requird for moving from 2.5.2 to 2.5.3?

Usually this is not needed when the ODS version hasn't been changed 
(between maintenance releases), but there is a special situation when 
coming from 2.5.1:
http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes253.html#notes-253

So, if your database was previously created with 2.5.2 or 2.5.0 then you 
are fine. If the database has been created with 2.5.1 then consider the 
warning in the link above.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.



> I have done it here without any issues so far, but thought I better ask
> the question.
>
> Todd Brasseur
> Compass Municipal Services Inc.
>
> 

Re: [firebird-support] Cross database update

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hello Christian,

> I am having problems regarding an update skript I want to write that
> fetsches data from table1 in db1 and writes the result into exisiting
> records in table2 in db2.
> To mange this task I am using firebird 2.1.5 and a tool called IBExpert
> with a script extension called ibeblock, that lets me specify different
> db connections within this script.
>
> Like this I a have created a second db with analysis data that holds
> aggregated amounts for all cases and other analytical data. This is
> queried in db1 and then written to db2, where the relevant tables have
> been cleared prior the insert. This process runs every night and takes
> about an hour to run through.
>
> I now want to write some parts of the data back to db1 to have some
> evaluation data available here.
>
> I have read that firebird 2.5 offers an additional "on external"
> function in an execute statement but this is still 2.1.
>
> Furthermore I have the problem that there are several triggers present
> in db1 which have to be activated or passed by. I found an article
> regarding rdb$get_context and rdb$get_context and therefore I prepared
> the triggers.
>
> So my script looks like this, but has no effect at all.
> It worked fine testwise with just one record update.
> There has to be some problem in the loop, but I don't know why:
>
> execute ibeblock
> as
> begin
>
> --This time the Analysis DB is the source
> FBSRC  = ibec_CreateConnection(__ctFirebird,'DBName="PATH to DB1";
> ClientLib=C:\WINDOWS\system32\fbclient.dll;
> user=XX; password=XX; names=ISO8859_1; sqldialect=3');
>
> -- and writes to the Test DB/Prod. DB
> FBDEST  = ibec_CreateConnection(__ctFirebird,'DBName="PATH to DB2";
> ClientLib=C:\WINDOWS\system32\fbclient.dll;
> user=XX; password=XX; names=ISO8859_1; sqldialect=3');
>
> ibec_UseConnection(FBSRC);
> ibec_UseConnection(FBDEST);
>
> use FBSRC;
> for select
>  (lrc.paid_claims_eur + lrc.paid_costs_eur -
> lrc.paid_recoveries_eur + lrc.paid_fees_eur +
>  lrc.os_claims_eur + lrc.os_costs_eur - lrc.os_recoveries_eur)
> as TCACCY,
>  (lrc.paid_claims_usd + lrc.paid_costs_usd -
> lrc.paid_recoveries_usd + lrc.paid_fees_usd +
>  lrc.os_claims_usd + lrc.os_costs_usd - lrc.os_recoveries_usd)
> as TCACCY2,
>  current_date, lrc.file_id
>  from loss_record_claims lrc
>  where lrc.file_id in (120966,120214) --testwise just two records
> into
>  :TCACCY, :TCACCY2, :DATEFILTER, :FILE_ID
> do
>begin
>
> use FBDEST;
>TRY
>  execute statement 'execute block as begin
> rdb$set_context(''USER_TRANSACTION'', ''bulkload'', ''1'');
>  update files f set f.ccy_total_claim_amount_net = :TCACCY,
> f.ccy2_total_claim_amount_net = :TCACCY2, f.date_filter = :DATEFILTER
>  where f.file_id = :FILE_ID;end';
>EXCEPT
>END
>  end
> commit;
> ibec_CloseConnection(FBSRC);
> ibec_CloseConnection(FBDEST);
> end
>
>
> I hope someone can help my. Transactionwise I got the info from the
> developer of IBExpert that by default, the script editor creates one
> transaction per connection. But I made tests without the trigger
> deactivation and created a trigger log and found out that there was one
> transaction per cycle of the loop.
> I think that this is wrong, but I am not sure.

This all is proprietary IBExpert stuff, thus I'm afraid the best support 
you can get is by contacting HK-Software (again).

Perhaps behind the scene is some sort of transaction magic, thus you 
might widen the context of the RDB$SET_CONTEXT call by using 
USER_SESSION instead of USER_TRANSACTION.

Another approach to dismiss triggers for such kind of bulk operations, 
similar to the "context" approach is to connect with a different user 
and check/handle the user name information inside the trigger.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Gbak Restore - SuperServer vs. SuperClassic

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Michael,

> Does GBak behave different on a Classic Server than on a SuperServer regarding
> restore speed?
> 
> 
> I ask this, because around 1 year ago I installed Firebird 2.5 SS on a Windows
> environment. 
> Here I did a restore of a 90 Gb DB.
> This took around 8 hours. 
> 
> 
> Since then we have switched from SS to Classic due to some performance issues.
> 
> 
> The setup runs good.
> 
> 
> Now I have 2 times tried to restore the dayly dump file on another folder on
> this very same system.
> Both times I have had to cancel this after 9+ hours where the restore was
> approximatly half way.
> 
> 
> I would have expected it to be a little more than 9 hours, since the DB is now
> 96 GB.

* What is the page buffers value at database level? (gstat -h)
* How does your gbak restore call look like?


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Re: Gbak Restore - SuperServer vs. SuperClassic

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Hello Michael,

> * What is the page buffers value at database level? (gstat -h)
> E:\DB\Kaufmann>"c:\Program Files\Firebird\Firebird_2_5\bin\gstat.exe"
> localhost:
> e:\db\Kaufmann\OCCEasyPos.FDB -h
> 
> 
> Database "e:\db\Kaufmann\OCCEasyPos.FDB"
> Database header page information:
> Flags   0
> Checksum12345
> Generation  101077117
> Page size   4096
> ODS version 11.2
> Oldest transaction  96356704
> Oldest active   96356705
> Oldest snapshot 96356705
> Next transaction96384172
> Bumped transaction  1
> Sequence number 0
> Next attachment ID  4930121
> Implementation ID   26
> Shadow count0
> Page buffers2048
> Next header page0
> Database dialect1
> Creation date   Nov 11, 2013 23:47:15
> Attributes  force write
> 
> 
> Variable header data:
> Sweep interval: 0
> *END*

Ok, so at least you are not running with a page buffers value of 0 which maps 
to a default value in firebird.conf of 75 for CS/SC.

Beside your gbak restore issue, your counters here show some interesting 
throughput numbers, when taking the creation date of the database into account:

~ 170 transactions / sec
~ 9 connections / sec

Especially opening 9 physical connections per second sounds a bit like a design 
flaw adding unnecessary overhead.

And a 96GB database with a page size of 4K might result in index depth > 3 for 
larger tables.


> E:\DB\Kaufmann>
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> * How does your gbak restore call look like?
> 
> GBak -C -User %1 -Pas %2 G:\FBDump\OCCEasyPos.fbk
> "LocalHost:F:\DB\Kaufmann\Uddannelse\OCCEasyPos.fdb" -v -rep -o
> -FIX_FSS_METADATA ISO8859_1 -fix_fss_data ISO8859_1 -y
> "G:\FBDump\RestoreEasyPos.txt"

- Any special reason that you use both -FIX* options everytime for the restore?
- Where is the restore process stuck? Probably while creating indexes?
- I would seriously look into running gbak for both, backup and restore through 
the Services Manager. Check out the -se option

Just some hints, but I don't have a real answer for your SS vs. SC issue.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Re: Gbak Restore - SuperServer vs. SuperClassic

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> Isnt the transaction per second wrongly calculated? 

Oops, sorry!!! I have been one year off by using 2014 instead of 2013.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.



> DB is created november 2013.
> Approcimatly 365 days ago.
> Transaction is at 96.356.705.
> 
> 
> Per day is approx: 263.990
> Per hour: 10.999
> Per minut: 183.
> Per second: 3
> 
> 
> 
> 
> 

Re: [firebird-support] Re: Gbak Restore - SuperServer vs. SuperClassic

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Hello Michael,

> Beside your gbak restore issue, your counters here show some interesting
> throughput numbers, when taking the creation date of the database into 
> account:
> 
> ~ 170 transactions / sec
> ~ 9 connections / sec
> 
> 
> 
> 
> We have a external connection from a WEBStore. 
> Whatever they do, they connect and disconnect every second, do a start
> transaction and commit. 
> I have a case with them, where I would like them to stop this, but so far
> without success.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> - Any special reason that you use both -FIX* options everytime for the 
> restore?
> Left over from restoring from 1.5 to 2.1 - havn't deleted them yet :)
> 
> 
> 
> 
> 
> - Where is the restore process stuck? Probably while creating indexes?
> No - both times I have seen this, its during ordinary restore and before 
> index.
> 
> I am still trying to find out why this is. 
> 
> 
> 
> 
> 
> - I would seriously look into running gbak for both, backup and restore 
> through
> the Services Manager. Check out the -se option
> 
> Hmm - whats this?

By using the Firebird services manager you basically tell the server to 
initiate and run the restore on the server which results in bypassing the TCP 
stack.

So, your original restore call:

GBak -C -User %1 -Pas %2 G:\FBDump\OCCEasyPos.fbk 
"LocalHost:F:\DB\Kaufmann\Uddannelse\OCCEasyPos.fdb" -v -rep -o 
-FIX_FSS_METADATA ISO8859_1 -fix_fss_data ISO8859_1 -y 
"G:\FBDump\RestoreEasyPos.txt"

Could be transformed to something like:

gbak -C -User %1 -Pas %2 -service localhost:service_mgr 
G:\FBDump\OCCEasyPos.fbk F:\DB\Kaufmann\Uddannelse\OCCEasyPos.fdb -v -o -y 
"G:\FBDump\RestoreEasyPos.txt"

So the difference is that you don't provide the host name (localhost) in the 
resulting database path but through the -service option. If both, the backup 
and the restored database file is on the same server, using the -service option 
is the fastest way. The same goes for a backup as well.

>From your origingal gbak restore call, I have also removed the -rep option, 
>because a faulty restore will trash the overwritten restored database and the 
>FIX* options as well.

Regards,
Thomas

Re: [firebird-support] Trace cause of performance drop

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hello Michael,

> I have a performance problem at a customer site.
>
> At this point I suspect one specific external user connection to cause
> the problems, but I can't pinpoint it so exactly just yet.
>
>
> But there is one table, where there is inserted records almost all the
> time.
>
> When the user connects, does whatever he has to (this will cause reads
> and some inserts into various tables).
>
> Then when the user completes the current routine, there will be updates
> of some 10-15 tables, which takes no time.
>
> Then an insert is done into one table. And the prepare of this insert
> can take up to 2 seconds. Normally it would be instantly.
>
>
> The entire database is around 96Gb
>
> Firebird is 2.5.
>
> Running Classic on Windows
>
>
> What will cause a prepare to become so slow?

What exact Firebird 2.5 version are you running? A vaguely remember an 
issue where prepare time can be quite long when the statement is 
referencing very large tables.

Btw, as you are on 2.5, you can simply use the Trace API to try to spot 
the slowest activities.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Cannot transliterate character between character sets for Firebird 2.1 database with WIN1257

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Hi,

> I am trying to migrate Firebird 1.5 dialect 1 charset NONE database to 
> Firebird
> 2.1 dialect 1 charset WIN1257 database, that still will be used by BDE.
> 
> 
> All is working. The only problem is that metadata (code of procedures or
> triggers) can not contain WIN1257 specific characters (with special marks).
> Such procedures compile and and can be executed but when they are viewed with
> IBExpert of Flamerobin, the exception 'Cannot transliterate character between
> character sets' is raised.
> 
> 
> We can avoid use of WIN1257 characters in metadata, it is sufficient that they
> are used in data (even BLOB fields handle them OK), but can this raise issues
> later (which is hard to predict now from our experience only)? What is the 
> best
> and fastest path to make such migration?

What was your process to switch from NONE to WIN1257. The only reliable way to 
do that is to create a new empty database with WIN1257 as default character set 
and re-create objects in the new database via a DDL script and then move data 
with a data pump utility.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Function - what is it called

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hello Michael,

> I am looking for a function which are able to add values from a table as
> records are fetched.

Cumulative sum?

> Something like this:
>
>
> ValueCalculated value
>
> 11
>
> 23
>
> 36
>
> 410
>
> 515
>
> The calculated value is just the sum of all previous values of VALUE.
>
>
>
> Whats the name of such function in english and Firebird?

Window function

> Does such a function even exist in Firebird 2.5?

No. Firebird 3.0 will have them.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] 64-bit server/32-bit application

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Is is possible to use the 64-bit Firebird server with a 32-bit application?

Sure.

> If so, is it recommended?  What are the pitfalls?

The only one is that a 32-bit application needs to use the 32-bit 
Firebird client library and not the one from a 64-bit Firebird server 
installation


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] 64-bit server/32-bit application

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> The 64 bit installer also installs the 32 but library iirc. So there
> should be no problem at all.

Yes, but people more and more use the ZIP distribution and AFAIK the 
32-bit libraries aren't included in the 64-bit package, thus the 
additional "reminder" ;-). At least not in 2.5.2. Haven't checked 2.5.3.


Thomas

Re: [firebird-support] Re: 64-bit server/32-bit application

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Thank you for the responses.
> The application and stored procedures utilize a UDF.dll. Does this need
> to change?  Does it need to be compiled as a 64-bit .dll for the server,
> but remain 32-bit on the clients?

The client application doesn't use the UDF library directly. The UDF 
library must match the bitness of the server.

The Firebird client library fbclient.dll/gds32.dll bitness must match 
the bitness of the client application, thus in your case 32-bit.

To summarize for a 64-bit Firebird server and a 32-bit client application:

* UDF Library => 64-bit
* Firebird client library => 32-bit



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Index Memory Usage

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Hi All, can anyone tell me (or point me in the direction of manuals) how
> i can calculate the amount of memory used by the indexes of a particular
> table?
> I have a table in which I store sha1 hashes (currently about 2 million
> records) as char(40) and its indexed, I'm wondering if the table would
> consume less memory if the hashes are stored as BIGINT

Use gstat -i to get the number of data pages occupied by an index 
multiplied by the page size of the database. This should give you a 
rough estimation on disk space used in KB for an index.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Awaiting Garbage Collector

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Hello,
> I'll try explain the situation in which we are in the shortest way
> possible...
>
> We're using a software in our company made by some people in Spain, the
> software supports 70 simultaneous users and we are experiencing slowness
> using the software so I started the investigation, and not later found
> that our database was increasing awaiting garbage transactions every
> second, as after 4 or 5 hours the number of awaiting transactions are up
> to 90.000. I've been reading about Firebird and its retaining legacy
> commit and performance degradation, but no one tells if 10.000 or 20.000
> or even 100.000 awaiting gc transactions could be the reason of the poor
> software performance. I have confirmation from the programmer that the
> component used to connect to firebird is DEVART IBDAC, with the
> AutoCommit flag set to TRUE. We have a Firebird 2.5 Classic Server,and
> also tried with Super Classic server architecture, but no difference, on
> a linux box, plenty of RAM and CPU and disk speed.

Ask your software vendor to fix their client transaction management. 
Using auto commit as default transaction management mechanism is 
Firebird's enemy number 1. Not only due to OAT not moving forward, you 
may also reach the 32-bit transaction id limit, depending on your load, 
in weeks/months until a backup/restore cycle is mandatory.

> I'm using Sintatica to monitor the database. Sinatica only tells the
> amount of transactions that are awaiting GC. I would like to know the
> amount of transactions awaiting gc per user or per connection, so I can
> know which client is piling up the most of transactions. Is there a
> system table or another software monitor that could tell me that
> information?

If the software is the only piece accessing the database, usually it 
does not gain any benefit to know which user/connection.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Awaiting Garbage Collector

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> At 08:09 a.m. 19/03/2015, Thomas Steinmaurer t...@iblogmanager.com
> [firebird-support] wrote:
> 
>>Ask your software vendor to fix their client transaction management. 
>>Using auto commit as default transaction management mechanism is 
>>Firebird's enemy number 1. Not only due to OAT not moving forward, you 
>>may also reach the 32-bit transaction id limit, depending on your load, 
>>in weeks/months until a backup/restore cycle is mandatory.
> 
> It is not AutoCommit, per se, that prevents the OAT from moving forward, but
> another transaction setting, COMMIT WITH RETAIN.

Yep. What I wanted to say is that AutoCommit of access components internally 
usually implies using commit retaining. Didn't make that clear enough in my 
reply.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


> Assuming your software is
> written with Delphi or a derivative, the setting is called CommitRetaining. 
> The hangup can be cleared if the software also periodically performs a hard
> COMMIT on the offending set.  This isn't something you can fix yourself, if 
> you
> don't have access to the source of the client code.  Thomas' comments stand
> true, regarding AutoCommit eating up the transaction count.
> 
> There may be another flaw in the transaction management: if a set is being 
> used
> for display and select but not for writing - such as in a drill-down scenario
> or a lookup -  that runs throughout the user session in a read/write
> transaction with ReadCommitted visibility, then this will hold the OAT back as
> well.  When such sets run in a read-only transaction, they do not cause the 
> OAT
> to get stuck.  Again, this is for the software vendor to fix.
> 
> 
> Helen Borrie, Support Consultant, IBPhoenix (Pacific)
> Author of "The Firebird Book" and "The Firebird Book Second Edition"
> http://www.firebird-books.net
> __ 
> 
> 
> 
> 
> Posted by: Helen Borrie 
> 
> 
> ++
> 
> 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
> 
> 
> 

Re: [firebird-support] Firebird 2.5 multi-core support

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Dave,

> I'm just trying to clarify the status of multiprocessor/multicore
> support in Firebird 2.5. I have found the architecture comparison page
> which starts to explain this.
>
>
> http://www.firebirdsql.org/manual/qsg25-appx-architectures.html
>
>
> 1. For Windows, we just need to update the CpuAffinityMask.
> 2. For Linux it states that CpuAffinityMask is ignored. Does that mean
> it will use multiple cores or not?
> 3. What about Mac OS X?

You have to use Classic or SuperClassic architecture for SMP support.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] garbage collect

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> great thanks
>
> to make sure I've got this right
>
> starting with the same database I can do either
>
> a) gbak with gc
>
> b) gbak without gc and gfix -sweep
>
> and I will have the same final result
>
>
> Extending this further, how can identify which tables contain (or did
> contain) the garbage.

Number of record versions per table in the gstat output.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] HP 200LX .gdb files?

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Larry,

> I am searching for a Macintosh OS X solution which supports .gdb
> database files from the Hewlett Packard HP 200LX Palmtop.
>
> I understand that Firebird can be installed on OS X, but need to know if
> there might still be support for the .gdb files produced by the 200LX.
>
> (I belong to an HPLX Support Group which has a couple of hundred members
> who are still using their HPLX devices on a regular basis.)
>
>
> I would appreciate any pointers to where I might find the relevant
> information.
>
>
> Thanks very much in advance!

I doubt this is a InterBase/Firebird database.

I guess you are more seeking for:
http://www.usinglinux.org/databases/p5-hp200lx-db.html


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Firebird Roadmap: v3.0 - what's next?

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> …many thanks; that’s what I was looking for! So there’s a great many work 
> under way, but it looks to me that v3 and v4 are currently more or less 
> optimizations / enhancements and no major changes coming up, right?

What are major changes from your perspective?

Fine-grained SMP with a shared page cache in Firebird 3 is IMHO a major 
change and long-awaited. And that's just only one new feature.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


>> Am 15.04.2015 um 09:25 schrieb Gabor Boros gaborbo...@yahoo.com 
>> [firebird-support] :
>>
>> 2015.04.15. 8:58 keltezéssel, Schwarze Consulting - Michael Schwarze
>> michael.schwa...@schwarze-consulting.de [firebird-support] írta:
>>> Hi,
>>>
>>> Just out of curiosity: what’s coming after Firebird v3.0? I checked the 
>>> upcoming roadmap [1] and was wondering whether there might be any NoSQL 
>>> ideas, etc. coming with a potential v4.0?
>>>
>>> Regards,
>>> Michael
>>
>> Hi,
>>
>> http://www.firebirdsql.org/file/community/conference-2014/pdf/01_firebird_devplanning.pdf
>>
>> Gabor
>>
>>
>
>
>
> 
> Posted by: Schwarze Consulting - Michael Schwarze 
> 
> 
>
> ++
>
> 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
>
>
>

Re: [firebird-support] Need Speed Optimization for SQL

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hi SET,

>> I tried as below way by adding Group By clause:
>>
>> with TMP(PK_JOB_ITEMS) as
>> (select distinct PK_JOB_ITEMS
>>from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
>>
>> select
>> Sum(cdi.QUANTITY) AS Delivered,
>> cdi.FK_JOB_ITEM
>>from tmp t
>> join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
>> join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = 
>> cdh.PK_DOCUMENT_HEADER
>> where cdh.DOCUMENT_TYPE = 1
>> group by cdi.FK_JOB_ITEM
>>
>> And sql is taking 1.893 seconds
>>
>> Any other way to make this more faster ? Please If possible
>
> If you have indexes (or if they are keys) for these three fields:
>
> CRM_JOB_ITEMS.FK_JOB
> CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
> CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
>
> then it will most likely be difficult to optimize this query any further 
> (sometimes having trigger generated summary tables can help, but that does 
> complicate things quite a bit and I've never seen it done when several tables 
> are involved, so I'd only consider this if this particular query was executed 
> frequently and was one of the most important queries in your system).
>
> Note that while I may be considered a Firebird SELECT expert, I am a novice 
> regarding other parts of Firebird (like configuration settings), so maybe 
> someone else can help you speed up your query a bit more.

Interesting to see that you are able to solve most/all of the query 
performance problems reported here by working around using CTEs. 
Shouldn't that ring a bell for Firebird developers in the optimizer 
area? ;-)

Regarding configuration settings for the thread creator:

* What is the result of gstat -h for the database?
* What exact version and Firebird architecture are you using?
* Number of concurrent connections?
* Available RAM?
* What's the TempCacheLimit entry in firebird.conf?
* What's the FileSystemCacheThreshold entry in firebird.conf?



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] large system slows over time

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> Hi
> 
> I have a system that is slowing down the longer it stays running and I'd
> like to know why.
> 
> The system is running firebird 2.1.5 on centos 5 with an average of 27
> transactions per second and has now been running for 112 days. The data
> throughput is unchanged, data is removed from the db as fast as its added
> so the database remains roughly the same size at 130Gb.
> 
> for example: on 1st Feb the backup took 4hr 35 min, last night took 9hrs 30
> min
> 
> Is this a known 2.1 issue (move to 2.5 is in the planning stages)?
> 
> Is there anything I can do to prevent or improve this situation?
> 
> Is there any evidence I can gather before I reboot the system which I
> expect (from past experience) will return the system to the better
> performance

How does your backup call look like?



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Can I run GSTAT over a local network?

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> Hi,
> 
> I am trying such command:
> 
> gstat.exe 10.10.3.2:/Base/2534/2534.FDB -p password -u user -d
> 
> But I am getting an error that specified patch could not be find. I am 
> guessing
> that I can not run gstat over a network?

Right. gstat is operating at physical file level and not through a database 
connection.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] FB 1.5 server sluggish after been up for awhile

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hello,

> we are still on FB 1.5 release, our live Database is about 50 GB. While
> using the freshly restored database, things are very fast in the
> beginning but they get slower and slower as weeks go by. We are always
> forced to do a backup/restore after 4 weeks or so.
>
> In the beginning we were thinking about the database itself. a Backup
> and restore would fix any speed issue but in recent days, I decided to
> stop and restart FB service without DB backup and restore. After doing
> so, everything was extremely fast as if we did a backup/restore.
>
> Do you think this is normal, every now and then a Firebird restart is
> needed?

That's usually related to bad client transaction management.

Once the database starts to become slow, run gstat -h on the database 
and check out the transaction counters and their gaps.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] How to determine Role of attached user?

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Hello Stefan,


> Hallo all,
> 
> when I call SELECT * FROM MON$ATTACHMENTS (logged in as the database
> owner), the MON$ROLE field always shows 'NONE', even when the user is
> logged in with a specific role.
> 
> Is there a way to find out the role another user (with a specifid
> attachment_id) is logged in?
> 
> Using Firebird 2.5.3.26778 on Windows 7 Pro.

AFAIR, the MON$ROLE field only provides a valid role name, when the user was a 
member (GRANT  TO USER ) of that role at connect time.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Backup & Restore NOT NULL Columns

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Hugo,

> Hello.
> 
> 1 - I created a column NOT NUL in a table (ALTER TABLE MYTABLE ADD 
> NEW_COLUMN INTEGER NOT NULL). There were already some records in the 
> table. FB didn´t raise any warning or exception.
> 2 - I created a backup file from MYDB.FDB using gbak.  FB didn´t raise 
> any warning or exception.
> 3- I tried to restore the backup file to a new MYDB.FDB and FB raises 
> and error: gbak: ERROR:validation error for column NAME, value "*** null 
> ***"
> 
> Let´s accept than the step 3 is coherent.
> 
> So, why can I execute step 1 without troubles?
> 
> Well, someone can tell me to use -no_validity to restore the database. 
> Ok, it works. But, the column that should be NOT NULL will change, 
> without warning, and I have to check my tables after restoring DB.
> 
> I am using FB 2.5 and I tried using FB 3.0 too.
> 
> Is it as I described or there is another way to treat this?

http://tracker.firebirdsql.org/browse/CORE-1748

See Dmitry's InterBase legacy comment.


I for one would simply reject adding new NOT NULL fields on a table with data 
if there is no DEFAULT clause provided.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Database restore speed with IBExpert and Gbak

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> Hello all,
> I came across something really strange as far as restoring a Firebird 
> database.
> if I use IBExpert(a tool from HK Software) it takes about 3 hours but if I use
> regular gbak using command line it takes 9 to 10 hours.
> I'm using Firebird 1.5. IBExpert vesion 2008.08.08
> I think I'm missing something here, any help would be appreciated,

IBExpert (usually like any other third-party tool) is running the backup 
through the Services API, which directly invokes/executes the backup on the 
server. Check out the -se switch of gbak. This should give you similar 
performance. Possible IBExpert also dismisses garbage collection on the source 
database during backup. The equivalent setting for gbak is the -g switch.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Database restore speed with IBExpert and Gbak

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Halim,

> Thank you for your reply.
> I just tested a GBAK restore using -se(rvice) switch on a 1 GB DB. It
> took about 8 minutes. Restoring the same database using IBExpert took
> about 3 minutes.
> I'm looking for a faster restore time because I want to automate the
> process using a batch file. Our DB is over 50 GB.

What is the size of table vs. index data?

Restore is basically limited by single core throughput and I've hardly 
seen restore being IO bound.

What you could try is to provide a much larger (temporary) page buffers 
value (which you have to reduce before the restored database is going to 
be used in production!) during the restore, which might help during 
index re-creation.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Database restore speed with IBExpert and Gbak

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hello Walter,

> Hello Thomas
>
> That seems an interesting idea. Can you explain it with more details?

For the restore process, gbak supports a -BU(FFERS) switch to override 
the database page buffer value. While page buffers tends to be rather 
small for Classic/SuperClassic hosted databases, you could try to 
increase that value by up to a factor of 100 through the -BU switch for 
the restore process.

This gives the restore connection a much higher Firebird page cache. But 
this setting is persisted in the header page after the restore, thus 
before going back to production, you have to reset to the original value.

I can't recall my exact test results from the past. There was also some 
sort of sweet spot where further increasing didn't help anymore, so run 
your own tests before applying that in your environment.

Possible further enhancements during index re-creation would be to 
re-create several indexes in parallel becoming more and more IO bound, 
especially with low latency storage. AFAIK InterBase added something 
like that in a recent version. Potentially Firebird has that on the 
roadmap as well.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


> Greetings.
>
> Walter.
>
>
> On Tue, May 26, 2015 at 12:25 PM, Thomas Steinmaurer t...@iblogmanager.com
>  [firebird-support]
>  > wrote:
>
> __
>
> Halim,
>
> > Thank you for your reply.
> > I just tested a GBAK restore using -se(rvice) switch on a 1 GB DB. It
> > took about 8 minutes. Restoring the same database using IBExpert took
> > about 3 minutes.
> > I'm looking for a faster restore time because I want to automate the
> > process using a batch file. Our DB is over 50 GB.
>
> What is the size of table vs. index data?
>
> Restore is basically limited by single core throughput and I've hardly
> seen restore being IO bound.
>
> What you could try is to provide a much larger (temporary) page buffers
> value (which you have to reduce before the restored database is
> going to
> be used in production!) during the restore, which might help during
> index re-creation.
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>
>
>
>
> 

Re: Odp: [firebird-support] Database restore speed with IBExpert and Gbak

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Hello Karol,

>>>Possible further enhancements during index re-creation would be to 
> re-create several indexes in parallel becoming more and more IO bound, 
> especially with low latency storage. >>>AFAIK InterBase added something 
> like that in a recent version. >>Potentially Firebird has that on the 
> roadmap as well. 
> 
> 
> yes, Interbase XE7 have this and restore speed is 2 times faster then in XE3
> but it is still 2 times slower then restore time on FB3 ;)
> 
> We have 5GB database and times looks like with the same restore settings
> buffers and others settings, and the same db structure and data:
> 
> IB XE3 - 93 minutes
> IB XE7 - 45 minutes
> FB 3- 26 minutes

>From a throughput perspective, this would mean:

IB XE3 => 0,918 MB/s
IB XE7 => 1,896 MB/s
FB 3 => 3,282 MB/s

To be honest, astonishing low numbers in 2015, for all three.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.



> Regards,
> Karol Bieniaszewski
> 
> 
> - Reply message -
> Od: "Thomas Steinmaurer t...@iblogmanager.com [firebird-support]"
> 
> Do: 
> Temat: [firebird-support] Database restore speed with IBExpert and Gbak
> Data: wt., maj 26, 2015 19:02
> Hello Walter,
> 
> 
> 
>> Hello Thomas
> 
>>
> 
>> That seems an interesting idea. Can you explain it with more details?
> 
> 
> 
> For the restore process, gbak supports a -BU(FFERS) switch to override 
> 
> the database page buffer value. While page buffers tends to be rather 
> 
> small for Classic/SuperClassic hosted databases, you could try to 
> 
> increase that value by up to a factor of 100 through the -BU switch for 
> 
> the restore process.
> 
> 
> 
> This gives the restore connection a much higher Firebird page cache. But 
> 
> this setting is persisted in the header page after the restore, thus 
> 
> before going back to production, you have to reset to the original value.
> 
> 
> 
> I can't recall my exact test results from the past. There was also some 
> 
> sort of sweet spot where further increasing didn't help anymore, so run 
> 
> your own tests before applying that in your environment.
> 
> 
> 
> Possible further enhancements during index re-creation would be to 
> 
> re-create several indexes in parallel becoming more and more IO bound, 
> 
> especially with low latency storage. AFAIK InterBase added something 
> 
> like that in a recent version. Potentially Firebird has that on the 
> 
> roadmap as well.
> 
> 
> 
> -- 
> 
> With regards,
> 
> Thomas Steinmaurer
> 
> http://www.upscene.com/
> 
> 
> 
> Professional Tools and Services for Firebird
> 
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
> 
> 
> 
>> Greetings.
> 
>>
> 
>> Walter.
> 
>>
> 
>>
> 
>> On Tue, May 26, 2015 at 12:25 PM, Thomas Steinmaurer t...@iblogmanager.com
> 
>> <mailto:t...@iblogmanager.com> [firebird-support]
> 
>>  
>> <mailto:firebird-support@yahoogroups.com>> wrote:
> 
>>
> 
>> __
> 
>>
> 
>> Halim,
> 
>>
> 
>> > Thank you for your reply.
> 
>> > I just tested a GBAK restore using -se(rvice) switch on a 1 GB DB. It
> 
>> > took about 8 minutes. Restoring the same database using IBExpert took
> 
>> > about 3 minutes.
> 
>> > I'm looking for a faster restore time because I want to automate the
> 
>> > process using a batch file. Our DB is over 50 GB.
> 
>>
> 
>> What is the size of table vs. index data?
> 
>>
> 
>> Restore is basically limited by single core throughput and I've hardly
> 
>> seen restore being IO bound.
> 
>>
> 
>> What you could try is to provide a much larger (temporary) page buffers
> 
>> value (which you have to reduce before the restored database is
> 
>> going to
> 
>> be used in production!) during the restore, which might help during
> 
>> index re-creation.
> 
>>
> 
>> --
> 
>> With regards,
> 
>> Thomas Steinmaurer
> 
>> http://www.upscene.com/
> 
>>
> 
>> Professional Tools and Services for Firebird
> 
>> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
> 
>>
> 
>>
> 
>>
> 
>>
> 
>> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 

Re: Odp: [firebird-support] Database restore speed with IBExpert and Gbak

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> ---In firebird-support@yahoogroups.com,  wrote :
> 
>> From a throughput perspective, this would mean:
> > 
>> IB XE3 => 0,918 MB/s
>> IB XE7 => 1,896 MB/s
>> FB 3 => 3,282 MB/s
> > 
>> To be honest, astonishing low numbers in 2015, for all three.
> 
>  To be even more honest, measure time of restore without building indices and
>  non-table metadata objects. Else your statement is about nothing, sorry

Ehm, just did some math on Karol's results. ;-)

And I guess his numbers present the entire restore time or did I miss something?


> Regards,
> Vlad
> 
> PS I don't say Firebird is able to restore at full speed of HDD sequential
> write, but your 
> conclusion is very far from truth. IMHO, of course.


Well, I haven't said that a Firebird restore is that slow in GENERAL as shown 
with the numbers above. It is in Karol's environment.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Memory usage excess in FBServer 2.5.4

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> I'm facing a problem where the firebird server v2.5.4 on win 2012 server in
> superserver mode is leaking memory. Currently I'm at ~5GB usage.
>
>
> Don't have any idea how to track down the problem.
> Thanks for any advice.

When you open the Windows Task Manager, what's the name of the Firebird 
process?



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Use ISQL to insert special german characters

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Niko,

'Parzival' parzival1...@gmx.at [firebird-support] schrieb am 02.06.2015 22:12:

> Hello all,
> 
> 
> 
> for updating some tables at customers I create SQL scripts and execute them
> with ISQL –i parameter. This works perfect unless I have to insert german
> special characters like äöüß.
> 
> 
> 
> The database is set to ISO8859_1. 
> 
> 
> 
> Even when I set the charset with – charset for the command line parameter of
> ISQL it still does not correctly write the ä etc. into the database.
> 
> 
> 
> Any experience on this subject?

Do we talk about running isql on Windows?

I guess this is a matter of changing to a proper code page in the CMD shell 
BEFORE running isql.


Try this in a DOS shell:

chcp (this gives you the currently active code page)
chcp 28591 (changes code page to ISO-8859-1, see also: 
https://msdn.microsoft.com/en-us/library/windows/desktop/dd317756%28v=vs.85%29.aspx)

isql-ch ISO8859_1

insert into t1 (...) values ('ä');
select * from t1;




--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: AW: AW: [firebird-support] Re: Memory usage excess / leak in FBServer 2.5.4

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Vlad,

> ---In firebird-support@yahoogroups.com,  wrote :
>  > Could you register it a tracker, please ?
>
>Done: http://tracker.firebirdsql.org/browse/CORE-4830

Is this SuperServer only or also SuperClassic?

Thanks!


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: AW: AW: [firebird-support] Re: Memory usage excess / leak in FBServer 2.5.4

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> ---In firebird-support@yahoogroups.com,  wrote :
>
>  > Is this SuperServer only or also SuperClassic?
>
> SuperServer only.

Thank you!

Thomas

Re: [firebird-support] Error While Executing Simple Inner Join SQL With Big database

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> I think the image posting is not allowed here to display.
> Below is the error message I see:
> Note I have 2.75 GB free disk space.
> *** IBPP::SQLException ***Context: Statement::FetchMessage: isc_dsql_fetch
> failed
> SQL Message : -901Unsuccessful execution caused by system error that does
> notpreclude successful execution of subsequent statements
> Engine Code : 335544675Enginne Message : sort errorNo free space found in
> temporary directoriesoperating system directive WriteFile failedThere is not
> enough space on the disk.

The error message makes it pretty clear. Right?

The Firebird server needs disk based temporary storage to process your 
statement and runs out of space. You may point temporary location configured in 
firebird.conf to a different destination.

Depending on the available RAM, you may also want to increase RAM usage for 
temp storage used by sorts, group by etc.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.



> On Monday, 8 June 2015 10:28 AM, "Robert martin r...@chreos.com
> [firebird-support]"  wrote:
>   
> 
> 
> Hi
> 
> I don't know what the error message is, you didn't include it! but it is 
> likely your join is wrong and returning one of every detail record for 
> every header (i.e. 78000 * 3000 records !)
> 
> Check your query and make sure the detail records are linked to the 
> master correctly (i.e. JOIN detail d ON d.ForeignkeyField = 
> m.primarykeyfield)
> 
> Post your SQL if you cant see the cause :)
> 
> Cheers
> Rob
> 
> 
> 
> On 8/06/2015 4:49 p.m., Vishal Tiwari vishuals...@yahoo.co.in 
> [firebird-support] wrote:
>> There is enough free disk space (2.75 GB) available.
>>
>>
>>
>> On Monday, 8 June 2015 10:08 AM, "Vishal Tiwari vishuals...@yahoo.co.in
>> [firebird-support]"  wrote:
>>
>>
>> Hi All,
>> I am running one SQL in that Master table has 78 thousand plus records
>> and detail table has 4 Laks and 3 thousand plus records. I have used
>> Inner Join on the primary key of Master table with the column in the
>> Detail Table.
>> When I execute with small size database, but with this database when I
>> run SQL using Flamerobin, I get below error.
>> Please help.
>>
>> With Best Regards.
>> Vishal
>>
>> [Non-text portions of this message have been removed]
>>
>>
> 
> 
> 
> 
> 
> 
> 
> ++
> 
> 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
> 
> 
> 
> 
> 
>  

Re: [firebird-support] Error While Executing Simple Inner Join SQL With Big database

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> I have 8 GB RAM, how much else does it need ?

Having 8GB RAM does not necessarily mean that Firebird is utilizing a vast 
majority of that.

Page buffers at database level or default in firebird.conf, TempCacheLimit in 
firebird.conf, etc. are the main configuration settings RAM usage wise.


>>> You may point temporary location configured in firebird.conf to a different
>>>destination.
> Could you help in, how do I configure to a different location using
> firebird.conf file ?

Check out the TempDirectories configuration parameter in firebird.conf

--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.



> So I could give a try.
> Thanks.  
> 
> 
> On Monday, 8 June 2015 11:44 AM, "'Thomas Steinmaurer' 
> t...@iblogmanager.com
> [firebird-support]"  wrote:
>   
> 
>     > I think the image posting is not allowed here to display.
>> Below is the error message I see:
>> Note I have 2.75 GB free disk space.
>> *** IBPP::SQLException ***Context: Statement::FetchMessage: isc_dsql_fetch
>> failed
>> SQL Message : -901Unsuccessful execution caused by system error that does
>> notpreclude successful execution of subsequent statements
>> Engine Code : 335544675Enginne Message : sort errorNo free space found in
>> temporary directoriesoperating system directive WriteFile failedThere is not
>> enough space on the disk.
> 
> The error message makes it pretty clear. Right?
> 
> The Firebird server needs disk based temporary storage to process your
> statement and runs out of space. You may point temporary location configured 
> in
> firebird.conf to a different destination.
> 
> Depending on the available RAM, you may also want to increase RAM usage for
> temp storage used by sorts, group by etc.
> 
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com
> 
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
> 
>> On Monday, 8 June 2015 10:28 AM, "Robert martin r...@chreos.com
>> [firebird-support]"  wrote:
>> 
>> 
>> 
>> Hi
>> 
>> I don't know what the error message is, you didn't include it! but it is 
>> likely your join is wrong and returning one of every detail record for 
>> every header (i.e. 78000 * 3000 records !)
>> 
>> Check your query and make sure the detail records are linked to the 
>> master correctly (i.e. JOIN detail d ON d.ForeignkeyField = 
>> m.primarykeyfield)
>> 
>> Post your SQL if you cant see the cause :)
>> 
>> Cheers
>> Rob
>> 
>> 
>> 
>> On 8/06/2015 4:49 p.m., Vishal Tiwari vishuals...@yahoo.co.in 
>> [firebird-support] wrote:
>>> There is enough free disk space (2.75 GB) available.
>>>
>>>
>>>
>>> On Monday, 8 June 2015 10:08 AM, "Vishal Tiwari vishuals...@yahoo.co.in
>>> [firebird-support]"  wrote:
>>>
>>>
>>> Hi All,
>>> I am running one SQL in that Master table has 78 thousand plus records
>>> and detail table has 4 Laks and 3 thousand plus records. I have used
>>> Inner Join on the primary key of Master table with the column in the
>>> Detail Table.
>>> When I execute with small size database, but with this database when I
>>> run SQL using Flamerobin, I get below error.
>>> Please help.
>>>
>>> With Best Regards.
>>> Vishal
>>>
>>> [Non-text portions of this message have been removed]
>>>
>>>
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> ++
>> 
>> 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
>> 
>> 
>> 
>> 
>> 
>> 
> 
>  #yiv8470420125 #yiv8470420125 -- #yiv8470420125ygrp-mkp {border:1px solid
>  #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8470420125
>  #yiv8470420125ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8470420125
>  #yiv8470420125ygrp-mkp #yiv8470420125hd
>  {color:#6

Re: [firebird-support] Error While Executing Simple Inner Join SQL With Big database

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] schrieb am 08.06.2015 
10:16:

> Hi Thomas Steinmaurer,
> I have given temporary directories path as below and SQL is now executing and
> showing the results as well.
> TempDirectories = c:\temp.  
> But there is one speed issue I am facing, if I take off some fields which is 
> of
> type "Blob sub_type 1" then sql execution takes 4 and half seconds, else it
> takes 56 seconds.
> Any idea how to optimize sql speed with such columns whose data type is "Blob
> sub_type 1"

By omitting unnecessary fields in the field list and/or reducing the result set.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


> Thanks. 
> 
> 
>     On Monday, 8 June 2015 12:51 PM, "'Thomas Steinmaurer' 
> t...@iblogmanager.com
> [firebird-support]"  wrote:
>   
> 
>     > I have 8 GB RAM, how much else does it need ?
> 
> Having 8GB RAM does not necessarily mean that Firebird is utilizing a vast
> majority of that.
> 
> Page buffers at database level or default in firebird.conf, TempCacheLimit in
> firebird.conf, etc. are the main configuration settings RAM usage wise.
> 
>>>> You may point temporary location configured in firebird.conf to a different
>>>>destination.
>> Could you help in, how do I configure to a different location using
>> firebird.conf file ?
> 
> Check out the TempDirectories configuration parameter in firebird.conf
> 
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com
> 
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
> 
>> So I could give a try.
>> Thanks.  
>> 
>> 
>> On Monday, 8 June 2015 11:44 AM, "'Thomas Steinmaurer' t...@iblogmanager.com
>> [firebird-support]"  wrote:
>> 
>> 
>>   > I think the image posting is not allowed here to display.
>>> Below is the error message I see:
>>> Note I have 2.75 GB free disk space.
>>> *** IBPP::SQLException ***Context: Statement::FetchMessage: isc_dsql_fetch
>>> failed
>>> SQL Message : -901Unsuccessful execution caused by system error that does
>>> notpreclude successful execution of subsequent statements
>>> Engine Code : 335544675Enginne Message : sort errorNo free space found in
>>> temporary directoriesoperating system directive WriteFile failedThere is not
>>> enough space on the disk.
>> 
>> The error message makes it pretty clear. Right?
>> 
>> The Firebird server needs disk based temporary storage to process your
>> statement and runs out of space. You may point temporary location configured
>> in
>> firebird.conf to a different destination.
>> 
>> Depending on the available RAM, you may also want to increase RAM usage for
>> temp storage used by sorts, group by etc.
>> 
>> --
>> With regards,
>> Thomas Steinmaurer
>> http://www.upscene.com
>> 
>> Professional Tools and Services for Firebird
>> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>> 
>>> On Monday, 8 June 2015 10:28 AM, "Robert martin r...@chreos.com
>>> [firebird-support]"  wrote:
>>> 
>>> 
>>> 
>>> Hi
>>> 
>>> I don't know what the error message is, you didn't include it! but it is 
>>> likely your join is wrong and returning one of every detail record for 
>>> every header (i.e. 78000 * 3000 records !)
>>> 
>>> Check your query and make sure the detail records are linked to the 
>>> master correctly (i.e. JOIN detail d ON d.ForeignkeyField = 
>>> m.primarykeyfield)
>>> 
>>> Post your SQL if you cant see the cause :)
>>> 
>>> Cheers
>>> Rob
>>> 
>>> 
>>> 
>>> On 8/06/2015 4:49 p.m., Vishal Tiwari vishuals...@yahoo.co.in 
>>> [firebird-support] wrote:
>>>> There is enough free disk space (2.75 GB) available.
>>>>
>>>>
>>>>
>>>> On Monday, 8 June 2015 10:08 AM, "Vishal Tiwari vishuals...@yahoo.co.in
>>>> [firebird-support]"  wrote:
>>>>
>>>>
>>>> Hi All,
>>>> I am running one SQL in that Master table has 78 thousand plus records
>>>> and detail table has 4 Laks and 3 thousand plus records. I have used
>>>> Inner Join on the primary key of Master table with the column in 

Re: AW: AW: AW: AW: [firebird-support] Re: Memory usage excess / leak in FBServer 2.5.4

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

>>
>>
>>   Thank you !
>>
>> Vlad
>>
>>
>> Vlad, does this issue affect classic and superclassic?

According to one of Vlad's previous message, only SuperServer.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Re: Firebird 2.5.4 - CentOS - mon$remote_address 0.0.0.0

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> Hello ,
> 
> So Dmitry suggested it , we have disabled IPV6 on our server CentOS 7 
> (Firebird
> 2.5.4 Classic server (I forgot to mention it in my first mail)).
> By following the centos FAQ :
> http://wiki.centos.org/FAQ/CentOS7#head-8984faf811faccca74c7bcdd74de7467f2fcd8ee
> Rebooting the server.
> But even after this modification the mon$remote_address is still  giving
> 0.0.0.0
> 
> Any ideas are welcome.

Is this a real remote connection from a remote client or an attachment locally 
on the server?



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.



> Marianne
> 
> De : firebird-support@yahoogroups.com 
> [mailto:firebird-support@yahoogroups.com]
> Envoyé : lundi 8 juin 2015 12:47
> À : firebird-support@yahoogroups.com
> Objet : [firebird-support] Re: Firebird 2.5.4 - CentOS - mon$remote_address
> 0.0.0.0
> 
> 
> 
> 08.06.2015 10:02, Marianne Castel wrote:
>>
>> We were working with Firebird 2.5.2 on Debian . At that time, in the
>> monitoring table MON$ATTACHEMENTS I could see the IP address of the
>> client connected to a database.
>>
>> Now our administrators have decided to work with CentOS and the last
>> available packages thus Firebird 2.5.4, and now in the databases on
>> that server in the table MON$ATTACHEMENTS table the field
>> mon$remote_address always contains : 0.0.0.0
>>
>> May be that the server is different too (network connection?) …
>>
>> Any idea why the IP address is no more reported ? How could we have the
>> IP address again in the monitoring table ?
> 
> Interesting. One possible explanation can be found here:
> 
> http://stackoverflow.com/questions/17220006/in-what-conditions-getpeername-returns-ipport-0-0-0-00
> 
> Firebird 2.x does not support IPv6 and always listens on a AF_INET (i.e.
> v4) socket, but maybe it could be related somehow. Have you tried to
> disable IPv6 on the new server box?
> 
> Dmitry
> 
> 

Re: [firebird-support] Re: Inssuficient rights for operation....

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> Hello. I used Firebird 1.5.6 on server on WIN7, a database with a role with
> full rights, and a user defined in ROLE, USER.
>   I have a problem. In the network with a application, on different client
>   station, login with the USER, all functions work OK.
>  
> From server or another WIN7 station, with USER login, a have one error, with
> insufficient sql rights for operation, no permission to read/select acces to
> TABLE. All different functions work OK. ROLE with GRANTS in all tables.
> 
> The solution from win7 is to give rights on USER for table CONFIG_BALANTA, the
> rights from the ROLE give this error
> 
>  
> WHY THE DIFFERENT functionality? Same user, same rights on database.

Is the user is providing the role name at connect time?


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Firebird 2.52 gbak fails to do a restore - error trigger (3)

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hello,

> We have backed up our databases daily for two years using Firebird 2.52
> Superserver and its associated gbak.  We now need to restore one of the
> databases and gbak fails to do the restore, giving the following error:
>

[snip gbak output]

> gbak: ERROR:action cancelled by trigger (3) to preserve data integrity
> gbak: ERROR:table/procedure has non-SQL security class defined
> gbak:Exiting before completion due to errors
>
>
> We have no procedures defined in or for our database.  It appears gbak
> has restored the database except for something at the end.  The database
> is working correctly every day.  Is this copy of the database backup toast?
>
>
> Should we be using a different backup tool like nbackup?  Can we use
> Interbase gbak to recreate the database?
>
>
> We are backing up our databases on Windows 7 using 2.52 Superserver.
> The databases are on a Linux Mint server.

I'm confused. The databases are hosted on Linux (InterBase or Firebird?) 
but you are running a backup from Win7?

You asked something similar > 2 years ago:
http://comments.gmane.org/gmane.comp.db.firebird.user/7145


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


> However, when we are trying
> to do the restore, we are restoring it to a directory on Windows 7
> because we don't trust restoring it over the currently running
> database.  So, the backup and restore are both on the same Windows 7
> computer using the same 2.52 Superserver.
>
>
> We need some data from the database from yesterday, so we just wanted to
> restore that long enough to pull the data.  The table we want, ON_ORDER,
> appears to have been restored but we cannot open the restored database
> to access it.
>
>
> Help?
>
>
> 
> Posted by: Jack Mason 
> 
>
> ++
>
> 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
>
>
>

Re: [firebird-support] Re: Firebird 2.5.4 - CentOS - mon$remote_address 0.0.0.0

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Thanks Steve for the information. I think we’ll try on a virtual machine
> the centOS versions with the different mode of firebird (classic,
> superserver, superclassic).
>
> To Thomas, yes that’s real remote connections from different remote
> servers (there is no local connections).

Ok, do they all use at least the Firebird 2.1 client library?


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


> *De :*firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> *Envoyé :* mercredi 10 juin 2015 08:32
> *À :* firebird-support@yahoogroups.com
> *Objet :* Re: [firebird-support] Re: Firebird 2.5.4 - CentOS -
> mon$remote_address 0.0.0.0
>
>
>  > Hello ,
>  >
>  > So Dmitry suggested it , we have disabled IPV6 on our server CentOS 7
> (Firebird
>  > 2.5.4 Classic server (I forgot to mention it in my first mail)).
>> By following the centos FAQ :
>>http://wiki.centos.org/FAQ/CentOS7#head-8984faf811faccca74c7bcdd74de7467f2fcd8ee
>> Rebooting the server.
>  > But even after this modification the mon$remote_address is still giving
>  > 0.0.0.0
>  >
>  > Any ideas are welcome.
>
> Is this a real remote connection from a remote client or an attachment
> locally on the server?
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com
>
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>
>> Marianne
>>
>> De :firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
>> Envoyé : lundi 8 juin 2015 12:47
>> À :firebird-support@yahoogroups.com 
>> Objet : [firebird-support] Re: Firebird 2.5.4 - CentOS - mon$remote_address
>> 0.0.0.0
>>
>>
>>
>> 08.06.2015 10:02, Marianne Castel wrote:
>>>
>>> We were working with Firebird 2.5.2 on Debian .At that time, in the
>  >> monitoring table MON$ATTACHEMENTS I could see the IP address of the
>  >> client connected to a database.
>  >>
>  >> Now our administrators have decided to work with CentOS and the last
>  >> available packages thus Firebird 2.5.4, and now in the databases on
>  >> that server in the table MON$ATTACHEMENTS table the field
>  >> mon$remote_address always contains : 0.0.0.0
>  >>
>  >> May be that the server is different too (network connection?) …
>  >>
>  >> Any idea why the IP address is no more reported ? How could we have the
>  >> IP address again in the monitoring table ?
>  >
>  > Interesting. One possible explanation can be found here:
>>
>>http://stackoverflow.com/questions/17220006/in-what-conditions-getpeername-returns-ipport-0-0-0-00
>>
>> Firebird 2.x does not support IPv6 and always listens on a AF_INET (i.e.
>> v4) socket, but maybe it could be related somehow.Have you tried to
>  > disable IPv6 on the new server box?
>  >
>  > Dmitry
>  >
>  >
>
>
>
> 

Re: [firebird-support] Firebird 2.52 gbak fails to do a restore - error trigger (3)

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> The databases reside on a Linux Mint server, but we back them up to a 
> Windows 7 system.   Both are running the same release of Firebird.  We 
> use Linux as the server because Linux is far more reliable than Windows 
> for everything, but it is the only Linux computer we use.  Everything 
> else is Windows because of other software we run.  So, to back up the 
> databases, we put the backups on a Windows 7 computer, blandly assuming 
> it is just a file that can be restored by the same program that backed 
> it up, gbak on Windows 7.

You mean a physical file copy from Linux to Windows while the database is in 
use?


> We had the same problem two years ago, and it never got resolved, it 
> just disappeared, so we assumed we had done something wrong and were no 
> longer doing whatever that was.

And you don't call a different gbak version by accident, e.g. due to a set PATH 
environment variable etc.?

Regards,
Thomas


> Jack
> 
> On 6/11/2015 3:02 AM, Thomas Steinmaurer t...@iblogmanager.com 
> [firebird-support] wrote:
>>
>> Hello,
>>
>> > We have backed up our databases daily for two years using Firebird 2.52
>> > Superserver and its associated gbak. We now need to restore one of the
>> > databases and gbak fails to do the restore, giving the following error:
>> >
>>
>> [snip gbak output]
>>
>> > gbak: ERROR:action cancelled by trigger (3) to preserve data integrity
>> > gbak: ERROR: table/procedure has non-SQL security class defined
>> > gbak:Exiting before completion due to errors
>> >
>> >
>> > We have no procedures defined in or for our database. It appears gbak
>> > has restored the database except for something at the end. The database
>> > is working correctly every day. Is this copy of the database backup 
>> toast?
>> >
>> >
>> > Should we be using a different backup tool like nbackup? Can we use
>> > Interbase gbak to recreate the database?
>> >
>> >
>> > We are backing up our databases on Windows 7 using 2.52 Superserver.
>> > The databases are on a Linux Mint server.
>>
>> I'm confused. The databases are hosted on Linux (InterBase or Firebird?)
>> but you are running a backup from Win7?
>>
>> You asked something similar > 2 years ago:
>> http://comments.gmane.org/gmane.comp.db.firebird.user/7145
>>
>> -- 
>> With regards,
>> Thomas Steinmaurer
>> http://www.upscene.com/
>>
>> Professional Tools and Services for Firebird
>> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>>
>> > However, when we are trying
>> > to do the restore, we are restoring it to a directory on Windows 7
>> > because we don't trust restoring it over the currently running
>> > database. So, the backup and restore are both on the same Windows 7
>> > computer using the same 2.52 Superserver.
>> >
>> >
>> > We need some data from the database from yesterday, so we just wanted to
>> > restore that long enough to pull the data. The table we want, ON_ORDER,
>> > appears to have been restored but we cannot open the restored database
>> > to access it.
>> >
>> >
>> > Help?
>> >
>> >
>> > 
>> > Posted by: Jack Mason 
>> > 
>> >
>> > ++
>> >
>> > 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
>> >
>> >
>> >
>>
>> 
> 
> -- 
> "Our Constitution was made only for a moral and religious people. It is 
> wholly inadequate to the government of any other." -- John Adams, Oct. 
> 11, 1798 "Where there is no vision, the people perish.." Prov 29:18
> 

Re: [firebird-support] Firebird 2.52 gbak fails to do a restore - error trigger (3)

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> The databases are backed up between midnight and 6 am when no one is 
> using them.  However, the databases are still open in multiple 
> programs.  The backup is:
> 
> C:\"program files"\firebird\firebird_2_5\bin\gbak -b -v -user SYSDBA 
> -pas masterkey 192.168.1.252:/bfl/smtbdb/customer 
> C:\backups\Buford\Thursday\customer.bak
> 
> and the restore is:
> 
> C:\"program files"\firebird\firebird_2_5\bin\gbak -R -v -user SYSDBA 
> -pas masterkey  C:\backups\Buford\Thursday\customer.bak 
> localhost:c:\temp\customer

What is the result of gstat -h on the production database?


Thomas


> Jack
> 
> 
> 
> On 6/11/2015 9:01 AM, 'Thomas Steinmaurer' t...@iblogmanager.com 
> [firebird-support] wrote:
>>
>>
>> > The databases reside on a Linux Mint server, but we back them up to a
>> > Windows 7 system. Both are running the same release of Firebird. We
>> > use Linux as the server because Linux is far more reliable than Windows
>> > for everything, but it is the only Linux computer we use. Everything
>> > else is Windows because of other software we run. So, to back up the
>> > databases, we put the backups on a Windows 7 computer, blandly assuming
>> > it is just a file that can be restored by the same program that backed
>> > it up, gbak on Windows 7.
>>
>> You mean a physical file copy from Linux to Windows while the database 
>> is in use?
>>
>> > We had the same problem two years ago, and it never got resolved, it
>> > just disappeared, so we assumed we had done something wrong and were no
>> > longer doing whatever that was.
>>
>> And you don't call a different gbak version by accident, e.g. due to a 
>> set PATH environment variable etc.?
>>
>> Regards,
>> Thomas
>>
>> > Jack
>> >
>> > On 6/11/2015 3:02 AM, Thomas Steinmaurer t...@iblogmanager.com
>> > [firebird-support] wrote:
>> >>
>> >> Hello,
>> >>
>> >> > We have backed up our databases daily for two years using 
>> Firebird 2.52
>> >> > Superserver and its associated gbak. We now need to restore one 
>> of the
>> >> > databases and gbak fails to do the restore, giving the following 
>> error:
>> >> >
>> >>
>> >> [snip gbak output]
>> >>
>> >> > gbak: ERROR:action cancelled by trigger (3) to preserve data 
>> integrity
>> >> > gbak: ERROR: table/procedure has non-SQL security class defined
>> >> > gbak:Exiting before completion due to errors
>> >> >
>> >> >
>> >> > We have no procedures defined in or for our database. It appears gbak
>> >> > has restored the database except for something at the end. The 
>> database
>> >> > is working correctly every day. Is this copy of the database backup
>> >> toast?
>> >> >
>> >> >
>> >> > Should we be using a different backup tool like nbackup? Can we use
>> >> > Interbase gbak to recreate the database?
>> >> >
>> >> >
>> >> > We are backing up our databases on Windows 7 using 2.52 Superserver.
>> >> > The databases are on a Linux Mint server.
>> >>
>> >> I'm confused. The databases are hosted on Linux (InterBase or 
>> Firebird?)
>> >> but you are running a backup from Win7?
>> >>
>> >> You asked something similar > 2 years ago:
>> >> http://comments.gmane.org/gmane.comp.db.firebird.user/7145
>> >>
>> >> --
>> >> With regards,
>> >> Thomas Steinmaurer
>> >> http://www.upscene.com/
>> >>
>> >> Professional Tools and Services for Firebird
>> >> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>> >>
>> >> > However, when we are trying
>> >> > to do the restore, we are restoring it to a directory on Windows 7
>> >> > because we don't trust restoring it over the currently running
>> >> > database. So, the backup and restore are both on the same Windows 7
>> >> > computer using the same 2.52 Superserver.
>> >> >
>> >> >
>> >> > We need some data from the database from yesterday, so we just 
>> wanted to
>> >> > restore that long enough to pull the data. The table we want, 
>> ON_ORDER,
>> >> > appears to have been restored but we canno

Re: [firebird-support] Firebird 2.52 gbak fails to do a restore - error trigger (3)

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> gstat is not included in the Firebird release I downloaded:
> 
> C:\Firebird>dir
>  Volume in drive C has no label.
>  Volume Serial Number is FA80-0C12
> 
>  Directory of C:\Firebird
> 
> 09/29/2014  02:25 PM  .
> 09/29/2014  02:25 PM  ..
> 09/29/2014  01:16 PM   105,717 Firebird-2.1-ErrorCodes.pdf
> 09/29/2014  01:06 PM 1,159,676 Firebird-2.5-LangRef-Update.pdf
> 09/29/2014  01:06 PM   245,583 Firebird-2.5-QuickStart.pdf
> 09/29/2014  01:00 PM10,467,361 Firebird-2.5.3.26778-0_Win32.zip
> 09/29/2014  01:01 PM12,259,685 Firebird-2.5.3.26778-0_x64.zip
> 09/29/2014  01:00 PM 6,973,448 Firebird-2.5.3.26778_0_Win32.exe
> 09/29/2014  12:58 PM10,282,876 Firebird-2.5.3.26778_0_x64.exe
> 09/29/2014  01:14 PM85,664 Firebird-fbmgr.pdf
> 09/29/2014  01:16 PM71,972 Firebird-File-Metadata-Security.pdf
> 09/29/2014  01:12 PM   133,557 Firebird-gbak.pdf
> 09/29/2014  01:15 PM90,111 Firebird-Generator-Guide.pdf
> 09/29/2014  01:13 PM   133,621 Firebird-gfix.pdf
> 09/29/2014  01:11 PM89,144 Firebird-gsec.pdf
> 09/29/2014  01:14 PM47,968 Firebird-gsplit.pdf
> 09/29/2014  01:13 PM90,958 Firebird-gstat.pdf
> 09/29/2014  01:11 PM   225,636 Firebird-isql.pdf
> 09/29/2014  01:12 PM   102,220 Firebird-nbackup.pdf
> 09/29/2014  01:15 PM   309,643 Firebird-Null-Guide.pdf
> 09/29/2014  01:14 PM39,907 Firebird-on-Ubuntu.pdf
> 09/29/2014  01:13 PM56,914 Firebird-shell-scripts.pdf
> 09/29/2014  01:15 PM   109,705 MSSQL-to-Firebird.pdf
> 09/29/2014  01:16 PM   195,585 Using-Firebird_(wip).pdf
>   22 File(s) 43,276,951 bytes
>2 Dir(s)  697,431,232,512 bytes free
> 
> C:\Firebird>gstat 192.168.1.252:/bfl/smtbdb/customer
> 'gstat' is not recognized as an internal or external command,
> operable program or batch file.

gstat is not a separate download but included in your Firebird installation, be 
it in the ZIP distribution or the installer. So, check out the Firebird\bin 
directory.

But this won't help you much, because gstat does not work over a regular 
database connection, but access the database at physical level, so you have to 
run that locally from your Linux machine.


> It has been 20+ years since I have done much with Unix.  If you can tell 
> me the directory Firebird is installed in on Linux, I will run the gstat 
> on the Linux server.  Otherwise, it will take me a while to locate 
> it which I will start now.

Don't know, but I guess fbstat as mentioned in your > 2 years old thread ;-): 
http://comments.gmane.org/gmane.comp.db.firebird.user/7145


Regards,
Thomas


> Thanks,
> Jack
> 
> 
> C:\Firebird>
> 
> On 6/11/2015 9:13 AM, 'Thomas Steinmaurer' t...@iblogmanager.com 
> [firebird-support] wrote:
>>
>>
>> > The databases are backed up between midnight and 6 am when no one is
>> > using them. However, the databases are still open in multiple
>> > programs. The backup is:
>> >
>> > C:\"program files"\firebird\firebird_2_5\bin\gbak -b -v -user SYSDBA
>> > -pas masterkey 192.168.1.252:/bfl/smtbdb/customer
>> > C:\backups\Buford\Thursday\customer.bak
>> >
>> > and the restore is:
>> >
>> > C:\"program files"\firebird\firebird_2_5\bin\gbak -R -v -user SYSDBA
>> > -pas masterkey C:\backups\Buford\Thursday\customer.bak
>> > localhost:c:\temp\customer
>>
>> What is the result of gstat -h on the production database?
>>
>> Thomas
>>
>> > Jack
>> >
>> >
>> >
>> > On 6/11/2015 9:01 AM, 'Thomas Steinmaurer' t...@iblogmanager.com
>> > [firebird-support] wrote:
>> >>
>> >>
>> >> > The databases reside on a Linux Mint server, but we back them up to a
>> >> > Windows 7 system. Both are running the same release of Firebird. We
>> >> > use Linux as the server because Linux is far more reliable than 
>> Windows
>> >> > for everything, but it is the only Linux computer we use. Everything
>> >> > else is Windows because of other software we run. So, to back up the
>> >> > databases, we put the backups on a Windows 7 computer, blandly 
>> assuming
>> >> > it is just a file that can be restored by the same program that 
>> backed
>> >> > it up, gbak on Windows 7.
>> >>
>> >> You mean a physical file copy from Linux to Windows while the database
>> >>

Re: [firebird-support] Issues migrating from FB 1.5 to 2.5

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

srotyli...@gmail.com [firebird-support] schrieb am 23.06.2015 14:31:

> Hi Karol, I think you are right on, I found this which is what I suspect is
> throwing the error in 2.5 (but not so in 1.5). I might have to go back to my
> old box running 1.5.  Do you know if it is possible to convert and downgrade a
> 2.5 *.fdb file to a 1.5 version ?
> Multiple Hits to Same Column Now Illegal
> It is no longer allowed to make multiple “hits” on the same column in an
> INSERT or UPDATE statement. Thus, a statement like
>   INSERT INTO T(A, B, A) ...
>  or
>   UPDATE T SET A = x, B = y, A = z
>  will be rejected in Firebird 2.n, even though it was tolerated in
>  InterBase and previous Firebird versions.

You may check out the "OldSetClauseSemantics" legacy configuration option in 
firebird.conf. Mind the note in the release notes that this configuration 
option might be removed in future releases.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] FB_INET_SERVER.EXE using all the memory

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> Hello!
> 
> 
> I have two databases in my Firebird 2.5.3
> 
> 
> The first is of 7.5GB and second is of 2.0GB
> 
> 
> My server has 4GB of Memory, and sometimes the service FB_INET_SERVER.EXE
> starts eating my memory from 1.5GB to 2.5GB-3GB of memory and my system slows
> down..
> 
> 
> Same concurrent users, what can i do?

* Is this Firebird 32 or 64-bit?
* What is the result of gstat -h for both databases?
* Did you make any changes in firebird.conf?



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Problem with FB database that freezes

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hi Eduardo,

[snip]

> Firebird.conf:
>
> -
>
> DefaultDbCachePages = 1024
>
> #FileSystemCacheThreshold = 65536 (commented out)
>
> #FileSystemCacheSize = 0 (commented out)
>
>
> Server environment:
>
> --
>
> CPU utilization: 11%
>
> Memory utilization: 11 GB (out of 32)
>
>
> Note.- Even when the DB performance is down, this values are in the same
> range or even lower. No swapping.
>
>
> gstat output (normal performance):
>
> -
>
> Database header page information:
>   Flags   0
>   Checksum  12345
>   Generation  19572161
>   Page size  16384
>   ODS version  11.2
>   Oldest transaction 18709808
>   Oldest active  18953295
>   Oldest snapshot  18851591
>   Next transaction 19520857
>   Bumped transaction 1
>   Sequence number  0
>   Next attachment ID 50438
>   Implementation ID 26
>   Shadow count  0
>   Page buffers  3000

Possibly a bit high for Classic, but might be ok. You might increase 
that to e.g. 2048 and spend more RAM on the temporary storage module for 
ordering/group by results etc.


>   Next header page 0
>   Database dialect 1
>   Creation date  Jul 7, 2015 7:00:57
>   Attributes  no reserve
>
>  Variable header data:
>   Database backup GUID: {BF8D26E0-970E-431A-7FAD-E2D9BDB2E4DA}
>   Sweep interval:  0
>   *END*

You have a long-running active transaction, cause the gap between Next 
Transaction - Oldest Active is quite high. As Sean mentioned, use the 
monitoring tables to identify the long-running process. As you are using 
FB TraceManager, it is a simple mouse-click via the context-menu in the 
parsed gstat output to locate the OAT in the monitoring tables.


> Note.- We seep the database manually each night.

Do transaction counters move on with that?



> fb_lock_print output (normal performance):
>
> 
>
> LOCK_HEADER BLOCK
>   Version: 145, Active owner:  0, Length: 28311552, Used: 27588104
>   Flags: 0x0001
>   Enqs: 69364533, Converts: 192066, Rejects:  36029, Blocks: 282250
>   Deadlock scans:  7, Deadlocks:  0, Scan interval:  10
>   Acquires: 77720068, Acquire blocks: 2159883, Spin count:   0
>   Mutex wait: 2.8%
>   Hash slots: 1009, Hash lengths (min/avg/max):   51/  66/  81
>   Remove node:  0, Insert queue:  0, Insert prior:  0
>   Owners (145): forward: 441288, backward:  98120
>   Free owners (11): forward: 24695928, backward: 23070064
>   Free locks (2963): forward:  22024, backward: 27499760
>   Free requests (42905): forward: 22145288, backward: 25253392
>   Lock Ordering: Enabled

You have very high hash lengths values (51-81) running with the default 
hash slots value of 1009. Increase it to at least 10009 or even higher 
in firebird.conf. It is recommended that the value is a prime number. 
AVG hash lengths should not be larger than ~ 20.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


> Firebird.log (IBMCASA is the server's host name)
>
> --
>
> The log is literally FULL of 10053 and 10054 error entries like the
> following:
>
>
> IBMCASA Thu Jul 23 10:27:27 2015
>   Unable to complete network request to host "IBMCASA".
>   Error writing data to the connection.
>
>
> IBMCASA Thu Jul 23 10:27:29 2015
>   Unable to complete network request to host "IBMCASA".
>   Error reading data from the connection.
>
>
> IBMCASA Thu Jul 23 10:27:30 2015
>   INET/inet_error: read errno = 10054
>
>
> According to the log, this errors seems to be happening every second or
> every few seconds/minutes, since March 8 2014 and until today even as
> I'm writing this. Each day, this errors stop at 11:49 PM when the last
> users stop working on the client apps, then they'll start again every
> morning at 6:00 AM when the first client apps connect to the database.
>
>
>
> 

Re: [firebird-support] Problem with FB database that freezes

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hi again,

> Hi Eduardo,
>
> [snip]
>
>> Firebird.conf:
>>
>> -
>>
>> DefaultDbCachePages = 1024
>>
>> #FileSystemCacheThreshold = 65536 (commented out)
>>
>> #FileSystemCacheSize = 0 (commented out)
>>
>>
>> Server environment:
>>
>> --
>>
>> CPU utilization: 11%
>>
>> Memory utilization: 11 GB (out of 32)
>>
>>
>> Note.- Even when the DB performance is down, this values are in the same
>> range or even lower. No swapping.
>>
>>
>> gstat output (normal performance):
>>
>> -
>>
>> Database header page information:
>>Flags   0
>>Checksum  12345
>>Generation  19572161
>>Page size  16384
>>ODS version  11.2
>>Oldest transaction 18709808
>>Oldest active  18953295
>>Oldest snapshot  18851591
>>Next transaction 19520857
>>Bumped transaction 1
>>Sequence number  0
>>Next attachment ID 50438
>>Implementation ID 26
>>Shadow count  0
>>Page buffers  3000
>
> Possibly a bit high for Classic, but might be ok. You might increase
> that to e.g. 2048 and spend more RAM on the temporary storage module for
> ordering/group by results etc.
>
>
>>Next header page 0
>>Database dialect 1
>>Creation date  Jul 7, 2015 7:00:57
>>Attributes  no reserve

Btw, it is not a good idea to run a read/write production database with 
the "no reserve" option.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Problem with FB database that freezes

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hi Eduardo,

> 
>
>  >As you are using
>  >FB TraceManager, it is a simple mouse-click via the context-menu in the
>  >parsed gstat output to locate the OAT in the monitoring tables.
>
>
> Thanks Thomas. I've located the OAT in the monitoring tables as you
> mention. Can you please elaborate a bit on how to find the client
> application or process that started this transaction? Is this something
> FBTM can help us with?

The transaction monitoring table has an attachment id which should be 
also available in the attachments monitoring table. This will give you 
the client/host, remote process name etc.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Multiple instances of firebird not working

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> I have an 3rd party application running firebird 1.5. 
> 
> 
> I want to install firebird 2.5 instance, with my application, without 
> affecting
> the other app. 

I did a video on that topic quite some time ago.
http://www.iblogmanager.com/download/demos/firebird/firebird_multiple_instances_windows.htm

I hope I didn't miss anything important at that time.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Connected users from MON$ATTACHMENT

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> Hello!
> 
> 
> May I use the system table "MON$ATTACHMENT" in order to get the connected
> users in moment?
> 
> 
> I already try to do it, but when a connection from a client (another
> machine) is ended abnormally (for example, turn off the machine or
> disconnect the cable of network), this connection still stay on system
> table "MON$ATTACHMENT".
> 
> 
> There are a way to "refresh" this system table?

Does it help when you query the monitoring tables in a new transaction context?


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] 2.5 architecture

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Hi
>
> We are moving to 64bit firebird 2.5 shortly and I'm looking at the
> architectures
>
> the first of these links seems to say that superserver is the only
> option, the second say otherwise
>
> can someone clarify please
>
> http://www.firebirdsql.org/file/fb25_architecture_comparison.pdf

IMHO, this one clearly states that there are 3 architectures in 2.5 
available.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] FB 2.5 Lock conflict on no wait transaction

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Hi Alexey,
>
> Thank you for posting. The full error text is :
>
> lock conflict on no wait transaction. deadlock. update conflicts with
> concurrent update. concurrent transaction number is 36094733
>
> lock conflict on no wait transaction. deadlock. update conflicts with
> concurrent update. concurrent transaction number is 3590088
>
> It repeats the same changing only the transaction number. This is
> happening with different databases in different servers.

At least two UPDATE statements are updating the same record(s) (and the 
first hasn't committed yet), and the latest gets this error due to the 
no wait transaction option.

The provided numeric transaction ID in combination with the 
MON$TRANSACTIONS table should help you to identify the transaction with 
the first, non-committed update statement.

-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] MS SQL Server - Linked Server setup for Firebird

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Hello Group - Has anyone documented steps of how to create a Linked Server to 
> Firebird in MS SQL Server?
> I'm running into issues and need help ASAP please. Much appreciated!

What issues?


> I'm not sure if screen shots work, but here's the setup.
>
> [cid:image003.jpg@01D0DF20.F6F9FBF0]

(Embedded) attachments won't work in this list in general.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.






++

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] 2.5 architecture

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Hi,
>
> At August 28, 2015, 10:59 AM, Nick Upson n...@telensa.com
> [firebird-support] wrote:
>
>
>   
>
>
>
> On 28 August 2015 at 14:43, Thomas Steinmaurer t...@iblogmanager.com
> [firebird-support]
>  > wrote:
>>http://www.firebirdsql.org/file/fb25_architecture_comparison.pdf
>
> IMHO, this one clearly states that there are 3 architectures in 2.5
> available.
>
> ​superserver & classic not applicable, embedded is sc, and sc itself => 1
>
>
>
> I think that the line that is labeled "Architecture" should probably
> read something else (i.e.: changes in architecture), or at the very
> least repeat its header.  But, all 3 architectures (superserver, classic
> and superclassic) are available in FB 2.5, which superclassic is new to
> 2.5.  And, embedded was changed from superserver to superclassic
> architecture.

Daniel and Nick, I agree, this is a bit confusing. ;-)

The "architecture" line was for Embedded only, as the (three) columns 
for the server edition already is the equally named architecture.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Using variables in ESQL select statement

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hi,

> I had a look at IB LogManager. It doesn't quite do what I want.
>
> The requirement we have is to log only fields that change, not all
> fields (which is what IB LogManager does). If I change fields 1, 4, and
> 7 then those are the only fields I want logged (and not fields 1, 2, 3,
> 4, 5, 6, and 7).

In case of an insert and update, IB LogManager only log old/new values 
for fields which are registered for logging and where the column value 
changed.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Using variables in ESQL select statement

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hi John,

> Thanks,Thomas
>
> That wasn't easily seem from the website - good to know.

Ah, ok. Thought you gave the trial a try. I would have considered that 
as a bug then.

> Do you have it
> available for Windows Server 2012, Server 2012 SP1, Server 2012 SP2, and
> Windows 10?

Running fine on Windows 7, thus Windows 10 and Server 2012 should be 
fine as well, as long as Data Execution Prevention isn't causing 
troubles upon start up.

> We'd still like to look at it as part of our database (rather than an
> add on) as we have a web interface that is used to view the changes.

IB LogManager is just a front-end to define the log triggers, view data 
etc. Thus, once set up, all log code is stored inside the database in 
triggers, so you don't have to have IB LogManager running in the background.

Log data is stored in regular tables inside the database, so you can 
build a WebUI based front-end on top of that tables if you wish.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

> John
>
> ------------
> *From:* "Thomas Steinmaurer t...@iblogmanager.com [firebird-support]"
> 
> *To:* firebird-support@yahoogroups.com
> *Sent:* Wednesday, September 23, 2015 11:36 AM
> *Subject:* Re: [firebird-support] Using variables in ESQL select statement
>
> Hi,
>
>  > I had a look at IB LogManager. It doesn't quite do what I want.
>  >
>  > The requirement we have is to log only fields that change, not all
>  > fields (which is what IB LogManager does). If I change fields 1, 4, and
>  > 7 then those are the only fields I want logged (and not fields 1, 2, 3,
>  > 4, 5, 6, and 7).
>
> In case of an insert and update, IB LogManager only log old/new values
> for fields which are registered for logging and where the column value
> changed.
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>
>
>
>
> 

Re: [firebird-support] Error while attempting to add a Primary key to a table that has no records

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> Hmmm,
> 
> 
> 
> I tried again this evening and it would allow me to add the primary key.  Very
> strange.  Since we moved to a new windows server 2012 R2 and upgraded to 
> v2.5.4
> firebird running Classic Super Server from v1.5.4 running Classic Server, 
> could
> any of that have anything to do with it?  Could the fact that everyone was
> logged into Firebird have anything to do with it?  The only user that would
> have ever used this table was logged out and gone home for the day or I would
> have never attempted to do it during the day.  There was still one user logged
> in that must of forgot to log out and it let me add the PK now?  

Any chance that at some point the new fields have not been declared as NOT 
NULL? I see they are in your final table DDL below, but perhaps they have been 
added as NULLable in a first try?



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.




> 
> 
> 
> Would be interested in anyone’s thoughts,
> 
> 
> 
> Mike
> 
> 
> 
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] 
> Sent: Thursday, October 01, 2015 4:04 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Error while attempting to add a Primary key to a
> table that has no records
> 
> 
> 
> Greetings,
> 
> Firebird v2.5.4
> 
> I use Database Workbench v 5 as my database development tool.
> 
> I have this table which is currently empty (0 rows).  It originally had a
> primary key on TRUST_STATEMENT_ID and ORIGINAL_PMT_ID
> 
> CREATE TABLE TRUST_STATEMENT_PMT_BACKOUT_CHK 
> 
> (
> 
>  TRUST_STATEMENT_ID  INTEGER NOT NULL,
> 
>  ORIGINAL_PMT_ID INTEGER NOT NULL,
> 
>  ORIGINAL_ACCT_IDINTEGER NOT NULL,
> 
>  ORIGINAL_CASE_ID   SMALLINT NOT NULL,
> 
>  ORIGINAL_DEBT_NO   SMALLINT NOT NULL,
> 
>  ORIGINAL_PMT_NOSMALLINT NOT NULL,
> 
>  ORIGINAL_PMT_DATE  DATE NOT NULL,
> 
>  ORIGINAL_PMT_AMTNUMERIC( 15, 2) NOT NULL,
> 
>  BACKOUT_PMT_ID  INTEGER,
> 
>  BACKOUT_PMT_NO SMALLINT,
> 
>  BACKOUT_PMT_DATE   DATE,
> 
>  BACKOUT_PMT_AMT NUMERIC( 15, 2)
> 
> );
> 
> I drop the PK so I could add 3 new fields (ORIGINAL_ACCT_ID, ORIGINAL_CASE_ID
> and ORIGINAL_DEBT_NO) to this table.
> 
> Now I’m trying to add a primary key on TRUST_STATEMENT_ID,  ORIGINAL_PMT_ID,
> ORIGINAL_ACCT_ID, ORIGINAL_CASE_ID and ORIGINAL_DEBT_NO
> 
> But I receive this error:
> 
> validation error for column ORIGINAL_ACCT_ID, value "*** null ***"
> 
> while executing:
> 
> ALTER TABLE TRUST_STATEMENT_PMT_BACKOUT_CHK 
> 
> ADD CONSTRAINT PK_TRUST_STATEMENT_PMT_BACK PRIMARY KEY (TRUST_STATEMENT_ID,
> ORIGINAL_PMT_ID, ORIGINAL_ACCT_ID, ORIGINAL_CASE_ID, ORIGINAL_DEBT_NO)
> 
> Why am I receiving this error and how do I fix it?
> 
> Mike
> 
> 
> 
> 
> 
> [Non-text portions of this message have been removed]
> 
> 
> 
> 
> Posted by: "stwizard" 
> 
> 
> ++
> 
> 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
> 
> 
> 

Re: [firebird-support] Data Type for Primary Key

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

Kevin Meyers kevinjmey...@gmail.com [firebird-support] schrieb am 06.10.2015 
22:36:

> I am wondering if any of you have every noticed any significant performance
> difference between using BIGINT versus INTEGER as an autoincremented
> primary key in Firebird. My idea is to use BIGINT and have virtually
> unlimited records ("set it and forget it" approach), but if I am going to
> incur a real performance hit, I would probably use INTEGER instead. I've
> done some modest testing and haven't noticed much difference, but I am
> wondering what you all have experienced in real world situations.

BIGINT is the way to go.

You will for sure have different areas where you can improve performance. The 
root cause for 90% of performance issues are usually SQL Tuning/Performance and 
the unterlaying physical data model (indexes etc.) related.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Optimize query for date ranges

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Niko,

> Hello all,
>
> i am running a simple query where the result contains 3 records. The
> query should provide all records in a specific week.
>
> SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND
> WOCHE = '2015-41';
>
> Some times I dont have a week but two dates:
>
> In this case – the very same number of records = 3 is the result set I
> see that the table Arbeitseinteilung gots 42 reads.
>
> SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND
> a.DATUM >= '05.10.2015' AND a.DATUM <= '11.10.2015';
>
> How can I improve the query?

Hmm, what is the execution time for both queries?

42 isn't a lot. This might also include accessing system tables ...


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.






++

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] invalid request BLR at offset 63 - functions (LTRIM,RTRIM,LRTRIM) not defined - module name/entrypoint not found

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> Hello Team,
> 
> A few days back, I posted a message regarding 'Firebird BLR 623 invalid
> request - LRTRIM is not defined -
> module name or  entrypoint could not be found'  when I ran some stored
> procedures that used either LTRIM
> or LRTRIM functions (occasionally,though  not always).
> 
> 
> Getting to the bottom of this problem, I found this:
> 
> 
> In my Firebird database, these are the UDF definitions:
> --
> DECLARE EXTERNAL FUNCTION LTRIM
>CSTRING(1000)
> RETURNS CSTRING(1000)
> *ENTRY_POINT 'lTrim' MODULE_NAME 'UDF';*
> 
> DECLARE EXTERNAL FUNCTION LRTRIM
>CSTRING(1000)
> RETURNS CSTRING(1000)
> *ENTRY_POINT 'lrTrim' MODULE_NAME 'UDF';*
> 
> DECLARE EXTERNAL FUNCTION RTRIM
>CSTRING(1)
> RETURNS CSTRING(1)
> *ENTRY_POINT 'rTrim' MODULE_NAME 'UDF';*
> 
> 
> I have a 64-bit cum 32-bit Firebird, both needed to work in different
> segments of Microsoft SSRS.
> 
> When I run these statements in IB Expert:
> 
> select ltrim ('  Waste no space   ')
> from rdb$database
> 
> select rtrim ('  Waste no space   ')
> from rdb$database
> 
> select lrtrim ('  Waste no space   ')
> from rdb$database
> 
> 
> Ocassionally (not always) I get the following error:
> 
> Invalid token.
> invalid request BLR at offset 63.
> function LRTRIM is not defined.
> module name or entrypoint could not be found.
> 
> --
> 
> 
> 
> After some researching, I found this:
> 
> In the 32-bit folder: C:\Program Files (x86)\Firebird\Firebird_2_5\udf , I
> find these 8 files:
> fbudf.dll,fbudf.sql,fbudf.txt,ib_udf.dll,ib_udf.sql,ib_udf2.sql,udf.dll,UDFPrici‌​ng.dll
> ;
> 
> But in the 64-bit folder: C:\Program Files\Firebird\Firebird_2_5\UDF , I
> find these 6 files:
> fbudf.dll,fbudf.sql,fbudf.txt,ib_udf.dll,ib_udf.sql,ib_udf2.sql
> 
> (I do NOT find the last 2 .dll files  - *udf.dll and UDFPricing.dll* - that
> are present in
> the 32-bit folder)

Both DLLs do not ship with the default Firebird distribution, thus must be 
custom UDF libraries.

> Will it be okay if I copy the udf.dll from the 32-bit folder and drop it in
> the 64-bit folder ?

No. UDF DLLs for Firebird 64-bit must be compiled as 64-bit DLLs.


> Will this be a solution? Or can I change the definition of the UDF itself -
> such as changing the
> module name to something instead of 'UDF'.

Either you put a 64-bit edition of the UDF libraries into the UDF directory of 
your Firebird 64-bit installation, or you have to move to a different 64-bit 
UDF library where the targeted function names are equally named (if you do not 
want to change your existing SQLs) there or check out the release notes of 
Firebird 2.1/2.5, because quite some functions have been added into the engine 
without the need to use a UDF library.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: AW: [firebird-support] Optimize query for date ranges

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Niko,

> the execution is currently not the problem, because the number of records is
> too small.
>
> I have the impression that the first query looks for DATUM >= x and the
> second one for DATUM <= y and finally delivers the number of records that is
> included in both result sets.
>
> My main interesst is to understand if I can improve my query or have to
> accept it. In the second case I will try to run as many queries as possible
> without dateranges.

To be honest, I wouldn't bother too much. Firebird should handle such 
simple statements with existing indexes fine.

What is the expected number of records in the result set for your date 
range queries?


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


> Niko
>
> -Ursprüngliche Nachricht-
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Montag, 12. Oktober 2015 17:31
> An: firebird-support@yahoogroups.com
> Betreff: Re: [firebird-support] Optimize query for date ranges
>
> Niko,
>
>> Hello all,
>>
>> i am running a simple query where the result contains 3 records. The
>> query should provide all records in a specific week.
>>
>> SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND
>> WOCHE = '2015-41';
>>
>> Some times I dont have a week but two dates:
>>
>> In this case – the very same number of records = 3 is the result set I
>> see that the table Arbeitseinteilung gots 42 reads.
>>
>> SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND
>> a.DATUM >= '05.10.2015' AND a.DATUM <= '11.10.2015';
>>
>> How can I improve the query?
>
> Hmm, what is the execution time for both queries?
>
> 42 isn't a lot. This might also include accessing system tables ...
>
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird FB TraceManager, IB LogManager,
> Database Health Check, Tuning etc.
>
>
> 
>
> 
>
> ++
>
> 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
>
>
>
>
>
> 
> Posted by: "Parzival" 
> 
>
> ++
>
> 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] How can I see which query within a stored procedure execution takes the longest time?

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

'Louis van Alphen' lo...@nucleo.co.za [firebird-support] schrieb am 14.10.2015 
10:09:

> I think a tool like FBTraceManager from Thomas (Upscene) can do that for you

Unfortunately not. The Trace API does not provide that level of granularity for 
PSQL code modules.

This has been a long discussion in firebird-devel in the past, people basically 
argued that the Trace API isn't the right place for that sort of granularity, 
but more appropriate for a debugging/profiling feature. I still believe in the 
Trace API being a good profiling tool though. ;-)



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

> 
> 
> 
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] 
> Sent: 13 October 2015 05:31 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] How can I see which query within a stored
> procedure execution takes the longest time?
> 
> 
> 
>  
> 
> Greetings All,
> 
> 
> 
> Firebird 2.5.4
> 
> I would like to know if there is any way that I can retrieve the execution
> time of each SQL SELECT or EXECUTE PROCEDURE within a stored procedure? 
> 
> Any tool to accomplish this?
> 
> Thanks,
> 
> Mike
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> [Non-text portions of this message have been removed]
> 
> 
> 
> 
> Posted by: "Louis van Alphen" 
> 
> 
> ++
> 
> 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
> 
> 
> 

Re: [firebird-support] Re: Holding a connection open forever

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> In response to your questions:
>
> What are the implications of holding a connection to the database open
> forever (and running millions and millions of tiny transactions flat out
> through that connection)?
>
> What difference would it make if we dropped the connection periodically?
>
> We had a similar issue with long running connections, and finally
> resorted to dropping the connection periodically.
>
> One thing I noticed in our gstat -h output was that the oldest
> transaction number was not moving forward. I could not never figure out
> why. We use auto commit and ODBC, not explicit transactions. Dropping
> the connection every few hours resolved our problem.

Auto commit usually used commit retaining which retains the physical 
transaction context thus your stuck transaction counters.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: ODP: [firebird-support] Re: Holding a connection open forever

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> you should identity this long running transaction. if you use fb2.5 than
> this is simple by mon$TRANSACTIONS table

MON$ tables are even in Firebird 2.1.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] How much percentage of a database space is a table contents taking.

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> Hi Robert, you can do this easily with a tool such as IBExpert. Just run 
> Services / Database Statistics and this is the result:
> 
> TableName SizeUsage%
> CUSTOMER  11,960,320  0.295
> PARAMFIL  16,384  1   0.000
> POSTCODES 3,903,012,864   96.335
> POSTCODE_MISSING  16,384  1   3.000
>   
> I've removed a lot more detail such as number of records, pages, fill 
> size etc.

Or low-level, for free, running gstat. ;-)



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] How much percentage of a database space is a table contents taking.

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

rgilland1...@gmail.com [firebird-support] schrieb am 27.11.2015 00:24:

> Hi,
> 
> 
> this is an output from ibexpert, no usage% column here either:
> 
> 
> Kind Regards,
> 
> 
> Robert.
> 
> 
> Database "OP.FDB"
> Database header page information:
> Flags   0
> Checksum12345
> Generation  28674
> Page size   8192
> ODS version 11.2
> Oldest transaction  25549
> Oldest active   28656
> Oldest snapshot 28656
> Next transaction28657
> Bumped transaction  1
> Sequence number 0
> Next attachment ID  161
> Implementation ID   16
> Shadow count0
> Page buffers0
> Next header page0
> Database dialect3
> Creation date   Nov 15, 2015 8:58:58
> Attributes  force write
> 
> 
> Variable header data:
> Sweep interval: 0
> *END*
> 
> 
> 
> 
> Database file sequence:
> File OP.FDB is the only file
> 
> 
> Analyzing database pages ...
> AS_AZN_TND_TML (419)
> Primary pointer page: 1869, Index root page: 1870
> Average record length: 0.00, total records: 0
> Average version length: 0.00, total versions: 0, max versions: 0
> Data pages: 0, data page slots: 0, average fill: 0%
> Fill distribution:
>  0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 0

Divide the value of "Data pages" in the per table gstat output by the database 
page size. This will give you an approximate value on space used on disk for 
the table. Approximate in a way as this does not include BLOB data. AFAIK, with 
Firebird 3 (ODS12), even BLOB data is counted here or displayed as a separate 
value. Can't remember exactly.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Re: How much percentage of a database space is a table contents taking.

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

rgilland1...@gmail.com [firebird-support] schrieb am 30.11.2015 05:10:

> Thanks Helen, 
> 
> I finally taught myself how to use isql.exe in order to access this
> information.
> What a strange tool.
> 
> 
> Then I found that both IBOConsole.exe and Upscene's Database Workbench show
> this information in Database Properties. And it is called "Allocated DB Pages"

That information is also available on the database header page, which can be 
extracted by gstat -h

So, basically, with gstat you get everything you need:

* DB total allocated pages
* Allocated pages per table
* Allocated pages per index (might be important contributors size-wise)


--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Re: How much percentage of a database space is a table contents taking.

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Monday, November 30, 2015, 7:40:50 PM, Thomas Steinmaurer wrote:
>
>> That information is also available on the database header page,
>> which can be extracted by gstat -h
>
> Unfortunately, that information is absent from gstat -h, hence my
> advice to use isql SHOW DATABASE.  I can't think why it is missing -
> even in Fb 3 where gstat -h output has actually been updated to
> display the new header info in ODS 12 databases.

Hmm, I really thought that I have seen this information on the header 
page, but I'm afraid I mixed that up with something else. I stand corrected.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.






++

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] Dialect 1 vs. 3: DATE to VARCHAR CAST results in different date/time format

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hello,

Executing the following in both, dialect 1 and 3:

select cast(current_timestamp as varchar(30))from rdb$database


results in a string with a different date/time format. At least on 
Windows with Firebird 2.5 64-bit.

Dialect 1: 7-DEC-2015 20:29:12.3820
Dialect 3: 2015-12-07 20:31:07.2280

Is this intended?

Thanks.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Dialect 1 vs. 3: DATE to VARCHAR CAST results in different date/time format

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> On 8-12-2015 09:52, Thomas Steinmaurer t...@iblogmanager.com
> [firebird-support] wrote:
>> Hello,
>>
>> Executing the following in both, dialect 1 and 3:
>>
>> select cast(current_timestamp as varchar(30))from rdb$database
>>
>>
>> results in a string with a different date/time format. At least on
>> Windows with Firebird 2.5 64-bit.
>>
>> Dialect 1: 7-DEC-2015 20:29:12.3820
>> Dialect 3: 2015-12-07 20:31:07.2280
>>
>> Is this intended?
>
> It is intended, the dialect 3 format conforms to the SQL standard to
> always use ISO-8601 format, while dialect 1 preserves backwards
> compatibility by using a locale specific conversion.
>
> See also http://ibphoenix.com/resources/documents/design/doc_169

Thanks Mark.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Firebird Download Malware

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

>> I downloaded FB 2.5.5 from firebirdsql and my Microsoft Essentials
>> detected it as a malware.
>
> I am facing the same problem.
> The following file is affected:
> http://sourceforge.net/projects/firebird/files/firebird-win64/2.5.5-Release/Firebird-2.5.5.26952_0_x64.exe/download
>
> This seems to be a false alarm though.
> According to Virustotal, 54 different scan engines say the file is
> clean:
> https://www.virustotal.com/de/file/e4ce70fd1be17b07fd26554ef322ee14f65fef8d398aaa707df115530aa0f410/analysis/
>
> Funnily, the Microsoft scan engine at Virustotal also considers it as
> clean.

Avira Antivir locally does not report anything suspicious.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.






++

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] Small Database with very very bad performance on LAN

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hello,

> Hallo,
> I have a small database (less 100MB)  that works on Firebird 2.5.5
> SS64bit over a machine with Win7 64bit with 4GB Ram, CPU Pentium G620.
>
> I seems to work very well in local but with the 3 clients on LAN works
> very bad. It is very very slow to open and navigate a simple table with
> less of 5.000 rows!
>
> What i can do?

You have to sort out where your current bottleneck is.

Can you pin-point it to a particular query or is performance in general 
bad? With Firebird 2.5, you have quite some monitoring amory, e.g. 
monitoring tables (tables starting with MON$...) or the Trace API.

Try to get a big picture with that tooling to see what's going, 
statement execution plans etc..., I/O statistics, e.g. is data being 
fetched from disk or served from the Firebird cache, which I doubt with 
your page buffers value below.

>
> I very novice in Firebird.
>
> The result of gstat is:
> Database header page information:
>   Flags   0
>   Checksum12345
>   Generation  26613
>   Page size   4096
>   ODS version 11.2
>   Oldest transaction  26603
>   Oldest active   26604
>   Oldest snapshot 26604
>   Next transaction26605
>   Bumped transaction  1
>   Sequence number 0
>   Next attachment ID  139
>   Implementation ID   26
>   Shadow count0
>   Page buffers0
>   Next header page0
>   Database dialect3
>   Creation date   Jan 4, 2016 15:44:10
>   Attributes  force write
>
>   Variable header data:
>   Sweep interval: 2
>   *END*

With SuperServer and a page size of 4096 and a page buffers set of 0, 
which gets overriden with a default value of 2048 from firebird.conf (if 
not specified otherwise), you end up with a Firebird page cache of 8MB 
(!) only for your database.

Increase "Page buffers" to e.g. 1 (e.g. with gfix command-line 
tool), then re-connect your client application and see if this helps.

There is other tuning stuff, but as a first step increasing the Firebird 
page cache + getting a big picture on your slow statements, their 
execution plan probably pointing to missing indexes ... is a good start.

Good luck.
-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.






++

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] Architecture

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hello Olaf,

> Hello,
>
> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to
> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit installation
> of Firebird too?

 From an operational POV, a main factor with moving from 32-bit to 
64-bit is if you are using (third-party) UDF libraries. You need them to 
be compiled with a 64-bit compiler if you want to run Firebird 64-bit.

Other than that, I guess it depends on your planned load, especially 
addressable RAM by the Firebird server process. If still intend to run 
with ClassicServer, then each connection spawns its own process, thus 
you probably won't hit the addressable RAM limit with Firebird 32-bit. 
That might be different with Firebird SuperClassic (32-bit), because 
this is a single process architecture.

I connect with odbc. Known Issues while migration,
> metadata etc. ?

The usual path is creating a gbak-based backup with Firebird 2.1 and 
restore it under Firebird 2.5. The resulting ODS will be 11.2 (you can 
check with gstat -h afterwards).

On the client side, make sure that you are using a version of the 
Firebird client library matching the server version. Bitness (32-bit vs. 
64-bit) of the client library is driven by your application and not by 
the Firebird server. So, if you are application is 32-bit (Delphi 
32-bit?) use a Firebird 32-bit client library and ODBC driver.

Hope this helps.

-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.






++

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: AW: [firebird-support] Architecture

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Thanks. For some Years, we have had in use the superserver, but since we
> have a quadcore CPU, we are using cs.
>
> UDFs can I replace with build in functions

I guess you can imagine that you first need to do that before backing up 
and restoring?

> Now, I have restored the database with 2.5, but.. Errors :( Now I have seen
> the new switches -fix_fss_d and m, but now I get another error, no character
> set were found.

* What is the character set of your database used when you have created 
the database in the past?
* What is the exact error message?
* How did you install Firebird 2.5?

Is there another way to migrate? With the scripts while
> running 2.1?

backup/restore via gbak is the preferred solution, because it also moves 
data. Of course, you still can create an empty Firebird 2.5 based 
database with a DDL script and then pump your data with a tool from
your source into the destination database.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.



>
> thanks
>
> -Ursprüngliche Nachricht-
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Freitag, 8. Januar 2016 16:37
> An: firebird-support@yahoogroups.com
> Betreff: Re: [firebird-support] Architecture
>
> Hello Olaf,
>
>> Hello,
>>
>> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to
>> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit installation
>> of Firebird too?
>
>   From an operational POV, a main factor with moving from 32-bit to 64-bit is
> if you are using (third-party) UDF libraries. You need them to be compiled
> with a 64-bit compiler if you want to run Firebird 64-bit.
>
> Other than that, I guess it depends on your planned load, especially
> addressable RAM by the Firebird server process. If still intend to run with
> ClassicServer, then each connection spawns its own process, thus you
> probably won't hit the addressable RAM limit with Firebird 32-bit.
> That might be different with Firebird SuperClassic (32-bit), because this is
> a single process architecture.
>
> I connect with odbc. Known Issues while migration,
>> metadata etc. ?
>
> The usual path is creating a gbak-based backup with Firebird 2.1 and restore
> it under Firebird 2.5. The resulting ODS will be 11.2 (you can check with
> gstat -h afterwards).
>
> On the client side, make sure that you are using a version of the Firebird
> client library matching the server version. Bitness (32-bit vs.
> 64-bit) of the client library is driven by your application and not by the
> Firebird server. So, if you are application is 32-bit (Delphi
> 32-bit?) use a Firebird 32-bit client library and ODBC driver.
>
> Hope this helps.
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird FB TraceManager, IB LogManager,
> Database Health Check, Tuning etc.
>
>
> 
>
> 
>
> ++
>
> 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
>
>
>
>
>
> 
> Posted by: "checkmail" 
> 
>
> ++
>
> 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

<

Re: AW: AW: [firebird-support] Architecture

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> now, the switches works fine, I had forget the Charset -fix_fss_d ISO8859_1
>
> It works fine, but there is another problem to fix. Now I get the error,
> that value exceeds the range for valid timestamps. How can I fix this issue?

* When does that happen during the restore?
* Does a backup *AND* restore with Firebird 2.1 works?


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

> I have installed fb as cs without superclassic under 32 Bit Win7 the 32 Bit
> Version
>
> -Ursprüngliche Nachricht-
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Freitag, 8. Januar 2016 16:51
> An: firebird-support@yahoogroups.com
> Betreff: Re: AW: [firebird-support] Architecture
>
>> Thanks. For some Years, we have had in use the superserver, but since
>> we have a quadcore CPU, we are using cs.
>>
>> UDFs can I replace with build in functions
>
> I guess you can imagine that you first need to do that before backing up and
> restoring?
>
>> Now, I have restored the database with 2.5, but.. Errors :( Now I have
>> seen the new switches -fix_fss_d and m, but now I get another error,
>> no character set were found.
>
> * What is the character set of your database used when you have created the
> database in the past?
> * What is the exact error message?
> * How did you install Firebird 2.5?
>
> Is there another way to migrate? With the scripts while
>> running 2.1?
>
> backup/restore via gbak is the preferred solution, because it also moves
> data. Of course, you still can create an empty Firebird 2.5 based database
> with a DDL script and then pump your data with a tool from your source into
> the destination database.
>
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird FB TraceManager, IB LogManager,
> Database Health Check, Tuning etc.
>
>
>
>>
>> thanks
>>
>> -Ursprüngliche Nachricht-
>> Von: firebird-support@yahoogroups.com
>> [mailto:firebird-support@yahoogroups.com]
>> Gesendet: Freitag, 8. Januar 2016 16:37
>> An: firebird-support@yahoogroups.com
>> Betreff: Re: [firebird-support] Architecture
>>
>> Hello Olaf,
>>
>>> Hello,
>>>
>>> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to
>>> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit
>>> installation of Firebird too?
>>
>>From an operational POV, a main factor with moving from 32-bit to
>> 64-bit is if you are using (third-party) UDF libraries. You need them
>> to be compiled with a 64-bit compiler if you want to run Firebird 64-bit.
>>
>> Other than that, I guess it depends on your planned load, especially
>> addressable RAM by the Firebird server process. If still intend to run
>> with ClassicServer, then each connection spawns its own process, thus
>> you probably won't hit the addressable RAM limit with Firebird 32-bit.
>> That might be different with Firebird SuperClassic (32-bit), because
>> this is a single process architecture.
>>
>> I connect with odbc. Known Issues while migration,
>>> metadata etc. ?
>>
>> The usual path is creating a gbak-based backup with Firebird 2.1 and
>> restore it under Firebird 2.5. The resulting ODS will be 11.2 (you can
>> check with gstat -h afterwards).
>>
>> On the client side, make sure that you are using a version of the
>> Firebird client library matching the server version. Bitness (32-bit vs.
>> 64-bit) of the client library is driven by your application and not by
>> the Firebird server. So, if you are application is 32-bit (Delphi
>> 32-bit?) use a Firebird 32-bit client library and ODBC driver.
>>
>> Hope this helps.
>>
>> --
>> With regards,
>> Thomas Steinmaurer
>> http://www.upscene.com/
>>
>> Professional Tools and Services for Firebird FB TraceManager, IB
>> LogManager, Database Health Check, Tuning etc.
>>
>>
>> 
>>
>> 
>>
>> ++
>>
>> 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
>>
>>
>>
>>
>>
>> 
>> Posted by: "checkmail" 
>> 
>>
>> ++
>>
>> 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/
>>
>> +

Re: AW: AW: [firebird-support] Architecture

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Olaf,

> now, the switches works fine, I had forget the Charset -fix_fss_d ISO8859_1
>
> It works fine, but there is another problem to fix. Now I get the error,
> that value exceeds the range for valid timestamps. How can I fix this issue?

Funny, you have been using 2.5 in 2013 already:
https://www.mail-archive.com/firebird-support@yahoogroups.com/msg05861.html

and ran into the same problem. ;-)

Check out Paul's blog: 
http://paulbeachsblog.blogspot.co.at/2011/07/firebird-v21-error-value-exceeds-range.html


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.



> I have installed fb as cs without superclassic under 32 Bit Win7 the 32 Bit
> Version
>
> -Ursprüngliche Nachricht-
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Freitag, 8. Januar 2016 16:51
> An: firebird-support@yahoogroups.com
> Betreff: Re: AW: [firebird-support] Architecture
>
>> Thanks. For some Years, we have had in use the superserver, but since
>> we have a quadcore CPU, we are using cs.
>>
>> UDFs can I replace with build in functions
>
> I guess you can imagine that you first need to do that before backing up and
> restoring?
>
>> Now, I have restored the database with 2.5, but.. Errors :( Now I have
>> seen the new switches -fix_fss_d and m, but now I get another error,
>> no character set were found.
>
> * What is the character set of your database used when you have created the
> database in the past?
> * What is the exact error message?
> * How did you install Firebird 2.5?
>
> Is there another way to migrate? With the scripts while
>> running 2.1?
>
> backup/restore via gbak is the preferred solution, because it also moves
> data. Of course, you still can create an empty Firebird 2.5 based database
> with a DDL script and then pump your data with a tool from your source into
> the destination database.
>
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird FB TraceManager, IB LogManager,
> Database Health Check, Tuning etc.
>
>
>
>>
>> thanks
>>
>> -Ursprüngliche Nachricht-
>> Von: firebird-support@yahoogroups.com
>> [mailto:firebird-support@yahoogroups.com]
>> Gesendet: Freitag, 8. Januar 2016 16:37
>> An: firebird-support@yahoogroups.com
>> Betreff: Re: [firebird-support] Architecture
>>
>> Hello Olaf,
>>
>>> Hello,
>>>
>>> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to
>>> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit
>>> installation of Firebird too?
>>
>>From an operational POV, a main factor with moving from 32-bit to
>> 64-bit is if you are using (third-party) UDF libraries. You need them
>> to be compiled with a 64-bit compiler if you want to run Firebird 64-bit.
>>
>> Other than that, I guess it depends on your planned load, especially
>> addressable RAM by the Firebird server process. If still intend to run
>> with ClassicServer, then each connection spawns its own process, thus
>> you probably won't hit the addressable RAM limit with Firebird 32-bit.
>> That might be different with Firebird SuperClassic (32-bit), because
>> this is a single process architecture.
>>
>> I connect with odbc. Known Issues while migration,
>>> metadata etc. ?
>>
>> The usual path is creating a gbak-based backup with Firebird 2.1 and
>> restore it under Firebird 2.5. The resulting ODS will be 11.2 (you can
>> check with gstat -h afterwards).
>>
>> On the client side, make sure that you are using a version of the
>> Firebird client library matching the server version. Bitness (32-bit vs.
>> 64-bit) of the client library is driven by your application and not by
>> the Firebird server. So, if you are application is 32-bit (Delphi
>> 32-bit?) use a Firebird 32-bit client library and ODBC driver.
>>
>> Hope this helps.
>>
>> --
>> With regards,
>> Thomas Steinmaurer
>> http://www.upscene.com/
>>
>> Professional Tools and Services for Firebird FB TraceManager, IB
>> LogManager, Database Health Check, Tuning etc.
>>
>>
>> 
>>
>> 
>>
>> ++
>>
>> 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
>>
>>
>>
>>
>>
>> 
>> Posted by: "checkmail" 
>> 
>>
>> ++
>>
>> Visit http://www.firebirdsql.org and click the Documentation item on
>> the main (top) menu.  Try FAQ and other lin