Re: [firebird-support] Re: order by takes too long

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

> Hello,
> 
> ok, I guess I need to create a DESC index on TIMESTAMP of MY_TABLE.
> I could verify this speeds it up from 1 minute to 1 second.
> This works as long as MY_TABLE is a real table.
> But now MY_TABLE is actually a View.
> 
> Can I created a index on a view with firebird?

No, but an index on the underlying table should be used by the optimizer.

Check the execution plan of your query accessing the view.


-- 
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] Unique Constraints and NULLs

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

Hello,

> Are NULLs considered values for unique constraints on a table?

No. NULL is not evaluated as part of a unique constraint.

What you could do is to declare your column as NOT NULL and provide a 
DEFAULT value/clause for your column, using a value which your 
application interprets as unknown / unset etc ...

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

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

> 
> e.g.
> 
> CREATE TABLE blah (
>col1 INT NOT NULL,
>col2 INT,
> 
>CONSTRAINT unq_blah UNIQUE(col1, col2)
> );
> 
> INSERT INTO blah (col1, col2) (123, 321);
> INSERT INTO blah (col1, col2) (123, 999); /* okay */
> INSERT INTO blah (col1, col2) (123, 321); /* error */
> 
> INSERT INTO blah (col1, col2) (123, NULL);
> INSERT INTO blah (col1, col2) (123, NULL); /* okay? error? */
> 
> I'd like that NULL example to be an error, does the UNIQUE constraint do
> that, or do I have to use a CHECK constraint? If it does require the
> CHECK, are there any performance tips for that setup?
> 
> Scott
> 
> 
> 
> 
> Posted by: Scott Morgan 
> 
> 
> ++
> 
> 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] Page cache size in Firebird 3.0.4

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

> I am running SS FB 3.0.4 x64 in Windows 10 x64 with 16GB RAM.
> 
> Database properties
> 
> ODS Version 12
> Page size 16384
> Pages 437728
> Size on disk 6.68GB
> Page buffers 10240
> Read only false
> 
> Settings
> 
> Dialect 3
> Default character set NONE
> Sweep interval 2
> Forced writes
> 
> Database alias in databases.conf file
> =
> my_dba = C:\Dba\my_dba.fdb
> {
>      FileSystemCacheThreshold = 2M
>      LockMemSize = 16M
>      LockHashSlots = 30011
>      TempCacheLimit = 2048M
>      TempBlockSize = 2M
>      DefaultDbCachePages = 65536
> }
> 
> I ran a query with flamerobin, this is the statistic result :
> =
> 46220441 fetches, 17965 marks, 172471 reads, 82 writes.
> 8847 inserts, 0 updates, 0 deletes, 15730299 index, 8847 seq.
> Delta memory: 4206064 bytes.
> G_TMP: 8847 inserts.
> Total execution time: 52.434s
> Script execution finished.
> 
> On Windows Task Manager, Firebird Server took 236MB RAM, max.
> 
> If not mistaken, Page Cache Size should be
> Page Size x DefaultDbCachePages = 16384 x 65536 = ~ 1GB
> 
> Why Firebird server only took 236 MB RAM ? Is this normal or something 
> missed here ?

DefaultDbCachePages (65536 in your case) is only used if Page buffers at 
database level is set to 0. Yours is set to 10240.

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.

Re: [firebird-support] Re: Issue with large table in FB 1.5 and 2.5

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

Hi Mark,

> On 30-5-2019 16:14, Thomas Steinmaurer t...@iblogmanager.com
> [firebird-support] wrote:
>>> Also one further question  Do later versions of Firebird (ie. 3 or
>>> 4) have any performance increase for cooperative garbage collection at
>>> all?  Would I expect to see any performance improvement by any newer
>>> version, or different server implementation here?
>>
>> Likely TRUNCATE TABLE ... in Firebird 4.0 comes to rescue here:
> 
> Truncate table (CORE-2479) isn't implemented yet, so it is unsure this
> will land in Firebird 4.

Oh, right. Thought it is in Firebird 4 Alpha already. Sorry.



-- 
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: Issue with large table in FB 1.5 and 2.5

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

> Also one further question  Do later versions of Firebird (ie. 3 or 
> 4) have any performance increase for cooperative garbage collection at 
> all?  Would I expect to see any performance improvement by any newer 
> version, or different server implementation here?

Likely TRUNCATE TABLE ... in Firebird 4.0 comes to rescue here:

a) No need to drop/create dependent objects like SPs etc. in the DROP 
TABLE use case
b) Won't suffer from garbage collection as with deleted data from the table.


-- 
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] Recursive Relationship on Dictionary Table

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

Hi,

> Hi,
> 
> 
> Is this list allowed to post SQL queries ou links? Anyway if someone is 
> interested and can help me, I did a recent post on SO which has been a 
> great challenge to me, although it seems simple at first sight:
> 
> 
> https://stackoverflow.com/questions/56220744/recursive-relationship-on-dictionary-table

If your question is, if Firebird supports recursive queries via Common 
Table Expressions (CTE), then the answer is yes. Since Firebird 2.1.

https://firebirdsql.org/refdocs/langrefupd21-select.html#langrefupd21-select-cte



-- 
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.1 transaction question

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

> I have a program that uses Firebird 2.1. Most installation use the
> embedded version so I prefer to stick with 2.1 since it does all I need.
> 
> I have a question about transactions: if I start a transaction, I add or
> modify some data, then the program gets an exception and it crashes,
> what happens to the transaction?
> 
> I was believing that if the program terminates without calling commit or
> rollback, then the transaction is automatically rolled back.
> 
> Now a customer had a problem that can be explained if the transaction is
> not rolled back, but the data changes remained in the database even if
> commit was not called because the program crashed before calling it.
> 
> Can anybody explain if changed data in this situation remains in the
> database of is it rolled back?

Data changes, which did not get acknowledged by either COMMIT or COMMIT 
RETAINING should never get permanently persisted.

Any chance that you are using your client access components in auto 
commit mode?


-- 
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] Get client-IP-Adress

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

Hello Josef,


> Hi,
> 
> 
> with our client software installed on a terminal server we like to get the
> IP-Adress of the "real" client-computer connected to the database  (the one 
> the
> user sits in front of).
> 
> 
> With Mon$Remote_Address from Mon$Attachments (using FB 2.5.8) we get the
> IP-Adress of the Citrix-machine.
> 
> 
> Is there a way to get the IP-Adress of the client-machine.
> Is there a difference/improvement using FB 3?

One work-around that comes to mind is using context variables. E.g. upon client 
application startup, set the client IP address as a USER_SESSION context 
variable, which then is available in context of the client connection / user 
session in e.g. triggers 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] Trailing spaces behaviour

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

Alex,

> Hi All,
> 
> I have found strange behaviour concerning trailing spaces - following 
> strings are equal:
> 
> 'ABC   ' = 'ABC'
> 
> You can try the following query:
> 
> /select/
> /  case when 'ABC   ' = 'ABC' then 1 else 0 end,/
> /  case when cast('ABC   ' as varchar(10)) = cast('ABC' as varchar(10)) 
> then 1 else 0 end,/
> /  char_length('ABC   '),/
> /  char_length('ABC')/
> /from/
> /  rdb$database/
> 
> This query was tested on FB v.1.5.6, 2.5.8 & 3.0.3 - while char length 
> (or strlen on 1.5) is different, the strings are always equal.
> 
> Is this by design :)

Additionally to Dimitry's reply.

If you need a reliable way for comparison and want to treat trailing 
spaces as different, check out the IS [NOT] DISTINCT FROM clause.

https://firebirdsql.org/refdocs/langrefupd21-distinct.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: AW: [firebird-support] Re: Trace not working

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

>>  Hmm... what do you mean ?
> 
>>  There should be almost instant message about trace started with trace ID,
>> but then messages will appear only when some events happens in database.
> 
> Well, I started the trace and the system prompted the message about trace 
> start
> its ID. But there was no output. Now I realized, that there is a difference
> between the alias and the absolute database path in config.
> 
> I used the alias name in config and the absolute path in my application. When 
> I
> switched the application to the alias name I was able to get the correct trace
> outputs. Is there a documentation about that? I couldn’t find anything about
> it.

'database' in your trace configuration is basically a regex, thus without any 
wildcards, this results in an exact match being necessary to be picked up then 
by the database filter in the trace component on the server side.



--
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] Moving DB from 32 bits to 64 bits

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

> I am only using some functions from the ib_udf.dll which is installed in 
> the \Firebird\Firebird_3_0\UDF  and have built a little functions 
> calling functions of that dll there is no problem I suppose ?

If you are talking about a PSQL-based stored function (new feature in 
3.0), then you are right, this won't be a problem.


-- 
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] Firing event when deleting in MON$ATTACHMENTS

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

Hi,

> Yes in my delphi application I registered the event name
> I tried POST_EVENT in a stored procedure but without DELETE FROM 
> MON$ATTACHMENTS statement
> sometimes it is fired sometimes not. I didn't understand why
> 
> 
> When I write the event name like this :
> 
> 1-POST_EVENT 'DISCONNECT_USER';
> It is fired
> 
> 2-POST_EVENT 'DISCONNECT_USER]'||:USR; (I splitt the event name using 
> "]" in the client in order to retrieve the event name part and the 
> username part)
> It is not fired
> 
> And when I back to 1 when it worked it is not fired at all

Did you commit the transaction executing POST_EVENT in all scenarios? 
Events are delivered to the listening client upon transaction commit.


-- 
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] Firing event when deleting in MON$ATTACHMENTS

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

> Hello,
> 
> I am deleting user connected with:
> DELETE FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = :ID_CONNECTION;
> 
> I need to use POST_EVENT in order to notify user that he has been disconnected
> but don't found a trigger for MON$ATTACHMENTS to do it.  Can you please help 
> me
> ? 
> Thanks

As Sean has mentioned, when you "delete" the attachment, your "communication 
channel" back to the user is basically gone.

What you could do is to:
* Run a stored procedure with the POST_EVENT
* Wait X minutes
* Execute the DELETE FROM MON$ATTACHMENTS statement


The main problem here is that your "administration connection" need to post an 
event isolated to the targeted user, e.g. by including something unique for the 
user in the event name and the client application has proper events registered.



--
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] Background auditing of Firebird database transactions

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

Hi,

> Hi fellow Firebird users,
> 
> Background:
> 
> * Current Firebird version is 2.5
> 
> * Windows OS could be anything...
> 
> * We are using C# with FirebirdClient ADO.NET Data Provider
> 
> * The software we write extracts and monitors the Firebird database of
> a third party application.
> 
> * We do a major extract of data from the Firebird database, then
> attempt to monitor for changes.
> 
> Comment:
> It appears to be very difficult for us to monitor the Firebird database for
> changes. We have started to look into the Trace functionality introduced in
> Firebird 2.5 to monitor for changes. This has helped, as we can relatively
> easily detect what tables have changed from the Trace output. But this 
> involves
> a lots of string parsing, which is really not nice.
> 
> We have just started to look into other software like FbTraceManager to help
> us, which appears to be a great tool for debugging issue, but I can't work out
> if there is a way to integrate FbTraceManager into an existing application to
> programmatically access the structured trace data.
> 
> I've come across some slideshows on Auditing (different from Trace?) that make
> it sound like there is other functionality there, but maybe not documented?
> 
> Question:
> Is there any way to detect table and row level changes, that could be
> integrated into an existing C# application? Identifying the changed row IDs in
> a table that has changed would be ideal...

If you are interested in what e.g. other DBMS products name "Change Data 
Capture", to audit/monitor data changes, then a trigger-based solution might be 
an appropriate mechanism. This might be a bit hard to do with the Trace 
functionality, which is great for debugging, getting the TOP X slowest queries 
etc.

IBLogManager is a third-party product for the trigger-based approach:
http://www.upscene.com/database_auditing/firebird_and_interbase/

Of course, it is not too difficult to roll your own ...




--
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] Restore performance (gbak service_mgr) whilst activating indices

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

> Hello Group,
> 
> Sorry if this double posts, I received an error when Sending.
> 
> I was provided with a largish (~27GB) Firebird 2.5 backup file (gbak)
> yesterday. I have been restoring this on Windows 2012R2 running 2.5.6 Classic
> using gbak with the -service_mgr switch. This particular VM only has a single
> CPU core but it is connected to the SAN and isn't doing anything else. I am
> remote desktop'd into the machine.
> 
> I have been tracking the restore through several mechanisms:
> * -v switch of gbak
> * How large the file is on disk
> * CPU utilisation (Resource Monitor)
> * Disk I/O (Resource Monitor)
> 
> The first 40GB or so of the restored FDB took about an hour or so. The I/O for
> fb_inet_server.exe was consistently in the 30MB/s range (usually about 10 read
> from the fbk and 20 write to the fdb file)
> The next 10 GB took 10 hours. Now obviously when it gets to "activating and
> creating deferred index xyz" it slows down, but I cannot see where the
> bottleneck is. I have seen extended periods of about 1MB/s reads corresponding
> with 5% CPU utilisation whilst activating some of those indices. I copied an
> unrelated 20GB file in the same folder and it happily copied at 50MB/s so 
> there
> is definitely capacity that isn't being used.
> 
> I can see that when it activates indices on larger tables it is creating temp
> files and these are at least being written to at 8+ MB/s.
> 
> Although I get that activating the indices is going to be slower than 
> restoring
> the data, I was expecting to see it either CPU and/or disk bound at any moment
> in time. (Plenty of headroom for memory and network utilisation is very low).

The restore process is bound to a single physical core. I've seen something 
similar in the past, where basically no resource (CPU, disk I/O - throughput + 
IOPS) being exhausted, thus not bound to available hardware. Do you have an 
Antivirus solution running affecting the restored Firebird database and/or 
temporary created files during restore?

You may also vote for:
http://tracker.firebirdsql.org/browse/CORE-2992



--
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] FreeAdhocUDF and Firebird 3 not working

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

Hi,

> I have been using Firebird 2.5.7 64 bit on Windows 7 for sometime, also 
> using FreeAdhocUDF. I just installed Firebird 3.0.2.32703 64 bit and now 
> FreeAdhocUDF is no longer loading.
> 
> I see that there is a support ticket in place for this exact bug, but so 
> far no resolution. This is a major problem as I will have to roll back 
> to 2.5 unless I find a work around.
> 
> Has any one found a resolution for this problem?  I mainly use the F_DVL 
> function to get around null values, so I guess I can use a case 
> statement (a lot of work though). It would be nice to get FreeAdhocUDF 
> working again, of course.

Have you tried to contact the creators of FreeAdhocUDF?

This is basically a third-party UDF library not being maintained by the 
Firebird project.



-- 
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: Sort error No free space found in temporary...

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

Hello Alexey,

> Hi,
> 
> I remember I answered incorrectly that time :)
> 
> TempCacheLimit is always per server instance.

Makes sense. Thanks for the clarification. :-)



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

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



> For Classic it means it is allocated per each connection (since each 
> connection has own server process), for SuperClassic and SuperServer 
> temp space will be shared between connections, since there is the single 
> server process.
> 
> Regards,
> Alexey Kovyazin
> IBSurgeon
> www.ib-aid.com
> 
> 
> 
> 
> On 02.09.2017 20:46, Thomas Steinmaurer t...@iblogmanager.com 
> [firebird-support] wrote:
>>
>> Hi Thomas,
>>
>> > From what i can read on the internet TempCacheLimit, is per server
>> > process, and not per attachment.
>>
>> In SuperServer architecture, right, but if I remember correctly, you
>> have mentioned in one of your first posts that you are using
>> SuperClassic, right?
>>
>> > http://firebird.1100200.n4.nabble.com/TempCacheLimit-td4309475.html
>>
>> Talking about SuperServer.
>>
>> > 
>> https://www.mail-archive.com/firebird-support@yahoogroups.com/msg10199.html
>>
>> Talking about SuperClassic with answers that it is per connection.
>>
>> >
>> >
>> > I believe I have found the statements that caused the problem, first 
>> one
>> > was executede about 400/second and contained a union, that was replaced
>> > by union all in the hope that would eliminate the need for temp cache.
>>
>> UNION vs. UNION ALL. UNION is removing duplicates, thus some sort of
>> applying DISTINCT with first sorting to remove duplicates is applied.
>>
>> DISTINCT, sorting, group by (internally first doing a sort) will
>> internally produce temporary data.
>>
>> > The second statement is a really nasty dynamically build common table
>> > expression containing upwards of 10 union.. Here we are in the process
>> > of moving this search to Elastisearch.
>> >
>> > We just purchased FB TraceManager at our company to weed performance
>> > issues  - Thanks for a great product : )
>>
>> Great to hear. 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] Re: Sort error No free space found in temporary...

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

Hi Thomas,

>  From what i can read on the internet TempCacheLimit, is per server 
> process, and not per attachment.

In SuperServer architecture, right, but if I remember correctly, you 
have mentioned in one of your first posts that you are using 
SuperClassic, right?

> http://firebird.1100200.n4.nabble.com/TempCacheLimit-td4309475.html

Talking about SuperServer.

> https://www.mail-archive.com/firebird-support@yahoogroups.com/msg10199.html

Talking about SuperClassic with answers that it is per connection.

> 
> 
> I believe I have found the statements that caused the problem, first one 
> was executede about 400/second and contained a union, that was replaced 
> by union all in the hope that would eliminate the need for temp cache.

UNION vs. UNION ALL. UNION is removing duplicates, thus some sort of 
applying DISTINCT with first sorting to remove duplicates is applied.

DISTINCT, sorting, group by (internally first doing a sort) will 
internally produce temporary data.

> The second statement is a really nasty dynamically build common table 
> expression containing upwards of 10 union.. Here we are in the process 
> of moving this search to Elastisearch.
> 
> We just purchased FB TraceManager at our company to weed performance 
> issues  - Thanks for a great product : )

Great to hear. 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] Re: Sort error No free space found in temporary...

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

Hi,

> Thanks for the advise Thomas I will increase the value.
> After we changed TempCacheLimit to 567108864, we saw the problem once 
> under heavy load - for a very sourt perio this time.

Increased TempCacheLimit value as a side-effect of serving more temp 
stuff through RAM instead of spilling onto disk, thus somehow makes 
sense to see the too many open files less frequent then.

> Is it correct 
> understood that once TempCacheLimit was reached Firebird used /tmp/ 
> until the proccess reached the max open files?

Sounds reasonable, yes.

> Regarding TempBlockSize and TempCacheLimit do you or any other know of a 
> way to monitor if the configured value is too low or the limit is about 
> to be reached?

No, but I became a bit rusty regarding Firebird lately. :-)

> Since the server has plenty of RAM i was thinking about configuring 
> TempCacheLimit to 2, 4 or 6 Gb, if this has no negative effect?

As you have mentioned that you are using SuperClassic, mind that 
TempCacheLimit is maximum memory for temp stuff PER client connections 
in this architecture, thus if configured too high with a high number of 
concurrent connections, you may end up in exhausting host RAM, resulting 
in swapping to disk if hitting boundaries.

> Likewise if there is no negative effect, is there anything to gain from 
> increasing TempBlockSize from 2 megabytes to perhaps 4 or 6?

I doubt you will see a real difference.

Beside that, perhaps you are able to pin the SQL statement producing the 
temp data load e.g. by using the Trace API.


-- 
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: Sort error No free space found in temporary...

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

> Hey Thomas
> Thanks for the answer. The command output is this:
> Max cpu time              unlimited            unlimited            seconds
> Max file size             unlimited            unlimited            bytes
> Max data size             unlimited            unlimited            bytes
> Max stack size            8388608              unlimited            bytes
> Max core file size        0                    unlimited            bytes
> Max resident set          unlimited            unlimited            bytes
> Max processes             514866               514866   
> processes
> Max open files            4096                 4096                 files
> Max locked memory         65536                65536                bytes
> Max address space         unlimited            unlimited            bytes
> Max file locks            unlimited            unlimited            locks
> Max pending signals       514866               514866               signals
> Max msgqueue size         819200               819200               bytes
> Max nice priority         0                    0
> Max realtime priority     0                    0
> Max realtime timeout      unlimited            unlimited            us
> 
> Max Open Files seems a bit low, is that the reason?

Very likely, so try to increase the Max open files limit and restart the 
Firebird process.

Increasing the limits might be a bit tricky, cause it may differ across 
Linux distros (to have new limits being persistent) and even more 
interestingly, how processes are started (e.g. via user based SSH 
session vs. started as systemd service etc.)

Try to Google for your Linux distro.

So, best check if new limits have been applied is always done via:

sudo cat /proc//limits

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.

Re: [firebird-support] Sort error No free space found in temporary...

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

Hello Thomas,


> Hey
> The other day we had a major slowdown on a database - one server with a single
> database. Some of our queries received this error:
> Sort error No free space found in temporary directories operating system
> directive open failed
> All queries that received this error used union in some way.
> 
> 
> The Firebird log is shows a lot of errors at the same time. 
> Cannot dump the monitoring data operating system directive open failed Too 
> many
> open files
> And
> Cannot initialize the shared memory region operating system directive open
> failed Too many open files

"Too many open files" usually means that the Firebird process isn't allowed to 
acquire more file handles from the OS.

What is the output of?

sudo cat /proc//limits


> 
> 
> After this the following changes was made to the config
> TempBlockSize = 2048576(changed from default value)
> TempCacheLimit = 567108864(changed from default value)

With TempCacheLimit, you basically tell Firebird how much RAM it is allowed to 
use for temporary data structure caused by sorting, group by etc. before 
spilling temporary stuff out onto disk.


> (I took the values from the firebird_25_superclassic_64bit.conf from ib-aid)
> 
> 
> However today same thing happened once again. Does anyone know the solution to
> this? 
> 
> 
> TempDirectories in config is set to default, so I guess /tmp/firebird i used,
> this disk has about 12Gb free space at the time of writing. Could all this
> space have been consumed and causing the errors?

If TempDirectories is commented out (thus not explicitly set), Firebird will 
use whatever the OS returns as disk location for temporary data.


Do you monitor disk usage somehow to correlate e.g. increased disk usage with 
error message above?

> 
> 
> A few weeks back we moved the database from a Windows Server 2008R2 to CentOs 
> to accommodate an increased load, on the database, before this we hand never
> seen this kind of error. The windows server used a default firebird config 
> file
> except for DefaultDbCachePages that was configured to 1024.
> 
> 
> Firebird: SuperClassic 2.5.7
> Server: Virtual server, 16 cores, 128Gb of Ram, Multi disk SSD SAN. 




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

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

Hello,

> Hello,
> 
> Is there a way to ask a database if someone is performing a nbackup?
> 
> If it's in "the physical backup mode".
> 
> I'm always paranoid if there is a power failure during a backup and it 
> gets stuck in that mode and I would like it to recover from such situation.
> We have many hundreds of installations and it's difficult to monitor all.
> 
> Firebird 2.5 on Windows

Perhaps MON$DATABASE is delivering with the MON$BACKUP_STATE field?
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref-appx05-mondb.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] Table Size

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

Ed,

FB TraceManager parses gstat output and give you table/index size (MB), 
percentage of total database size etc. but actually does not have 
charting in that area.

Check out screens here:
http://www.upscene.com/fb_tracemanager/media

* Table Stats Monitoring, and
* Index Stats Monitoring


-- 
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 a number of growing databases on my server and would like to
> occasionally see how much space each table is taking in the
> database--there may be some log files that could be cleaned up now and
> then. Many of my tables include blob fields, so this becomes different
> than just doing record count times the amount of data in a record.
>
> Does anyone have any tools that they can recommend that could help me
> easily manage this?
>
> much appreciated,
>
> Ed Dressel
>
>
> 

Re: [firebird-support] reset sysdba password without loose existing users

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

> Hi all, 
> 
> I need more help to archive this task.
> 
> 
> I just copied the file flembed.dll and others in my directory(bin)
> now i can connect to my database,  with gsec or isql
> 
> 
> but when i try to change de sysdba password in commande line, i got this
> message :
> 
> 
> SQL> alter user sysdba password 'mypassword' 
> Statement failed, SQLSTATE = 0A000
> feature is not supported
> 
> 
> Maybe i missed somthing, thanks for your help

Are you sure that you are connecting via the embedded DLL and not a regular 
Firebird server process?

I have seen several environments where there have been Firebird Windows 
Services running in parallel and a faulty connect string pretty much resulted 
in using the wrong Firebird server. So, for your case, make sure that no other 
Firebird server is running, e.g. explicitly stopping them in the Windows 
services panel, in case you are on Windows.



--
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 non-SYSDBA user can see connections by other users?

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

> SYSDBA users can see other connected users using monitoring tables but is it
> possible to implement such feature for non-SYSDBA users? Are there
> event/triggers that act uppon connecting and disconnecting and which can 
> insert
> usual database records. Triggers no MON$ tables are not suitable because MON$
> tables are populated only during query time.

The following users get the full picture when querying monitoring tables:

* Database owner
* SYSDBA
* Users as a member of the special per database RDB$ADMIN role. Role needs to 
be specified 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] change /tmp/firebird lock dir default value

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

> Hi,
> 
> 
> I’am running multiples instances of Firebird CS 2.5 x64. But all instances
> uses the same “lock dir”. How I can change the "lock dir” default value
> "/tmp/firebird”?
> I will appreciate any help.

To my understanding, this can get dangerous when you start to separate shared 
resources (e.g. lock tables) into different directories per instance. Once you 
start to access a database from several SC/CS instances, you might ask for 
corruption. See also: http://tracker.firebirdsql.org/browse/CORE-3268

Destination for the the temporary data (sort files etc.) can be configured per 
instance though. Check out 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: AW: [firebird-support] High number of NULL attachments

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

> fbclient.dll is bundled with the client application.

If I remember correctly, you need a client library 2.1+ to get that information 
popping up in MON$ATTACHMENTS


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

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



> 
> -Tom
> 
> On Tue, October 4, 2016 10:14 am, 'Gregor Kobler' g...@gksoft.ch
> [firebird-support] wrote:
>> Does the Client uses the right fbclient.dll or GDS32.dll?
>>
>>
>>
>>
>>
>> Best Regards
>>
>>
>> Gregor
>>
>>
>>
>>
>>
>> Von: firebird-support@yahoogroups.com
>> [mailto:firebird-support@yahoogroups.com]
>> Gesendet: Dienstag, 4. Oktober 2016 08:41
>> An: firebird-support@yahoogroups.com
>> Betreff: [firebird-support] High number of NULL attachments
>>
>>
>>
>>
>>
>>
>>
>>
>> Hi all.
>>
>>
>> I am running a FB 2.5.3 server to which a delphi application connects. We
>> usually have 20-30 concurrent connections.
>> I have inspected the MON$ATTACHMENTS table and noticed many of the records
>> have NULL values in MON$REMOTE_PID and MON$REMOTE_PROCESS but not in
>> MON$REMOTE_ADDRESS.
>>
>>
>> Does that mean that the connection to the server is still open even if the
>> application isn't? How can I mitigate that?
>> Thanks.
>>
>>
>> -Tom
>>
>>
>>
>>
>>
>>
>>
> 
> 
> 
> 
> 
> Posted by: "Tom" 
> 
> 
> ++
> 
> 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] Can merge return AffecterRows?

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

> hi,I am Nobuo Watanabe.
> I use FireBird 2.5.6 and FireBird2.5.3.
> 
> Firebird2.5Language Reference about 'merge command' says:
> Currently, the ROW_COUNT variable returns the value 1, even if more than one
> record is modified or inserted. For details and progress, refer to Tracker
> ticket CORE-4400 .
> 
> I use ISQL,and set count on;
> When I use 'merge' on ISQL2.5.3,then return 1 always.
> but when I use 'merge' on ISQL2.5.6,then affected row count return.
> 
> Is this right count?or is there any condition to get right row_count?

Sounds a bit like http://tracker.firebirdsql.org/browse/CORE-4817 fixed in 
2.5.6 and 3.0


--
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 3 performances

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

> Hello,
> 
> 
> I am testing Firebird 3 in order to upgrade an application from Firebird 2.5
> and I would like to know if there are known cases of Firebird 3 performances 
> that are worse than Firebird 2.5. During my tests, it seems that bulk
> insertions take longer in Firebird 3.

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



--
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] After update trigger: the same values for new.somefiled and old.somefield

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

Hello Sean,

>>> For some reason they both have the new value and I can't figure out why.
>>> If I didn't misread all the articles, I've found, completely, then
>>> they should be different, shouldn't they?
>>>
>>>
>>> I'm using Firebird 2.5.2.26539.
>>
>> Any chance that another trigger is changing the value?
>
> I was thinking that myself, but Patrick said that it was the "old" value 
> which was being changed to the "new" value.

Right. Perhaps NEW is changed to OLD?

>
> Another trigger (a BEFORE UPDATE trigger), in a single update 
> operation/chain, can only change the "new" value, nothing should be able to 
> change the "old" value.


-- 
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] After update trigger: the same values for new.somefiled and old.somefield

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

Hi,

> shouldn't the values of new.somefield and old.somefield be different in
> an after update trigger, when the value of that field has definitely
> changed?
>
>
> For some reason they both have the new value and I can't figure out why.
> If I didn't misread all the articles, I've found, completely, then they
> should be different, shouldn't they?
>
>
> I'm using Firebird 2.5.2.26539.

Any chance that another trigger is changing the value?

You might use the Trace API to find out what's going on ...

-- 
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] Transaction in limbo

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

> Hi
> 
> I have a database, which have had to instances where there have been
> Transactions in limbo.
> I know how to fix this. 
> 
> I am on the other hand unsure why they appear.
> What can cause this?
> What should/could I check, so I can eliminate this from occuring. 

Something went wrong in a distributed transaction spanning multiple databases. 
Prominent examples are e.g. replication tools.



--
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 3 - Adding anew User

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

Hello Fabian,

> Sorted after reading "Initializing the Security Database" from
> http://www.firebirdsql.org/file/documentation/release_notes/Firebird-3.0.0-ReleaseNotes.pdf
> at page 116. I think there is a small error on the documentation, where it 
> says
> " isql -user sysdba employee", the sentence works probably on Linux but on Ms
> WIn 2012 Server R2 it requires the password to run.

You need to shutdown every running regular Firebird server process, otherwise 
you won't end up in an Firebird 3.0 Embedded connection, which is needed if you 
want to initialize your 3.0 security database in SRP mode.

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

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



> 
> Cheers,
> Fabian
> 
>  - Original Message - 
>  From: fabia...@itbizolutions.com.au [firebird-support] 
>  To: firebird-support@yahoogroups.com 
>  Sent: Monday, June 13, 2016 4:34 AM
>  Subject: [firebird-support] FB 3 - Adding anew User
> 
> 
> 
> 
> 
>  Hi All,
> 
>  I have configured the FB 3 server to only accept SRP connections and need to
>  add a new User to the security DB, I read GSEC is not the way, and I have
>  tried with isql but for some reason I am not having luck, I am trying the
>  following:
> 
> 
> 
>  I created a script into a file as follows:
> 
>  SET SQL DIALECT 3;
>  SET NAMES UTF8;
>  SET CLIENTLIB 'C:\Temp\FB32Bits\fbclient.dll';   (this dll is 32 bits dll, 
> but
>  the server is 64 bits, and I use the 32 bits dll because IBexpert is 32 bits)
> 
>  CONNECT '127.0.0.1/3050:My_New_DB_Configured_Properly_in_Databases.conf' USER
>  'SYSDBA' PASSWORD 'NewPassNotMasterkey';
> 
>  CREATE USER NUCLEUSCRM PASSWORD 'WhateverPassword'   ;
> 
>  COMMIT work ;
> 
> 
>  The above fails when executed from IBExpert script executive, the error is:
> 
>  "Unsuccessful execution caused by a system error that precludes successful
>  execution of subsequent statements.
>  Your user name and password are not defined. Ask your database administrator
>  to set up a Firebird login."
> 
>  Its funny because IBexpert is connecting fine to the DB, and the following
>  config is in place:
> 
>  AuthServer = Srp
>  AuthClient = Srp
>  UserManager = Srp
> 
> 
>  So the only way to connect is with the new security model, and IBExpert is
>  connecting when restoring the DB, but when running script into the DB
>  something does not work. Any ideas?
> 
> 
>   
>  Cheers,
>  Fabian
> 
> 
> 
> 
> 
> 
>  

Re: [firebird-support] Re: Max RAM for Database Cache in SS FB 3.0 64bit is to small?

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

> 'Thomas Steinmaurer' t...@iblogmanager.com [firebird-support] a écrit :
>> trsk...@yahoo.com [firebird-support] schrieb am 02.06.2016 06:44:
>>
>>> Where I can download v3.0.1 ?
>>
>> 3.0.1 hasn't been released yet, but snapshot builds are available here:
>> http://www.firebirdsql.org/en/snapshot-builds/
>>
> 
> the link is dead 

Works here. I get the entry page regarding snapshot builds for all branches and 
Windows/Linux. Anyway ... ;-)

> http://web.firebirdsql.org/download/snapshot_builds/win/3.0


Thomas

Re: [firebird-support] Re: Max RAM for Database Cache in SS FB 3.0 64bit is to small?

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

trsk...@yahoo.com [firebird-support] schrieb am 02.06.2016 06:44:

> Where I can download v3.0.1 ?

3.0.1 hasn't been released yet, but snapshot builds are available here:
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] Re: FB 3.0, got problem when add field to Table with existing data

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

>> What value must it have? Add a default clause to this statement. Or add 
> > the field as nullable, populate with data and then alter to NOT NULL.
> 
> I have tried to add field as nullable, then tried to alter to not null.
> 
> UPDATE TMP_RPT SET ID1 = '5'; Commit;
> (This statement is OK)
> 
> 
> UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = 1
> WHERE RDB$FIELD_NAME = 'ID1' AND RDB$RELATION_NAME = 'TMP_RPT';
> (This statement raised an error, FB 3.0 doesn't allow SysDBA user to change
> System table)

Firebird 3 rejects direct system tables modification.

Use the supported ALTER DDL statement for managing NULLability of existing 
columns/domains.

ALTER TABLE  ALTER  { DROP | SET } [NOT] NULL


See Firebird 3 release notes p.68.



--
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] download link for fb 3 embedded?

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

> 25.05.2016 12:23, korkl...@yahoo.it [firebird-support] wrote:
>> where are the embedded packages for fb3?
> 
>   Not exists anymore. Read Release Notes about changes in server architecture.

While this is true, that people should first read the release notes, IMHO we 
should make the transition for Embedded users more clear/prominent/present on 
the website, e.g. in the download area for 3.0 packages. Due to a missing 
downloadable Embedded package, a clear statement on what is needed for an 
Embedded deployment out of the regular server distribution might help as well. 
Perhaps in a dedicated chapter of the release notes (possibly I haven't read 
the release notes carefully myself *g*). I know Carlos' migration guide deals 
with that, but IMHO the release notes should make that clear as well.

The current situation is a bit confusing for Embedded users.

Just my thoughts.


--
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 unavailable

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

Hello Bill,

> Used IB 10 years ago.  Porting an SQL Anywhere app to FB 2.5.  Going well
> until I try to connect a client to the database - "database unavailable."  I
> have shared the DB folder on my Windows 7 machine, can browse to it from a
> client machine (Win 10) and see Full Control permissions.

Don't put your database into a shared folder. The clients must not have direct 
physical access to the database file, because they can play around taking 
physical copies then, worst case corrupting the database.

Only the Firebird process needs physical access to the database file locally. 
Clients need to use a TCP/IP connect string then, e.g. 
myhost:c:\data\database.fdb, where c:\data\database.fdb is the location on the 
machine running the Firebird process. Or even better, use database aliases, 
configurable in a Firebird configuration file called 'aliases.conf', which 
allows you to define a mapping from a custom name to a physical location. E.g.:

mydb = c:\data\database.fdb

The custom name 'mydb' should then be used in the connect string, e.g. with 
myhost:mydb

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.



 I have disabled
> my Trend AV on the Win7 server as well as put incoming and outgoing rules in
> the firewall for FBserver.exe.
> 
> 
> 
> I am using the ODBC client to connect via ADO which works fine on the Win7
> server.  I've run out of things to try.  Any suggestion would be greatly
> appreciated.
> 
> 
> 
> Thanks,
> 
> 
> 
> Bill Skelton
> 
> 
> 
> Landmark Data Systems, Inc.
> 
> 2 Old River Place, Ste L
> 
> Jackson, MS  39202
> 
> 601-362-0303
> 
> 
> 
> 
> 
> [Non-text portions of this message have been removed]
> 
> 
> 
> 
> Posted by: "Bill Skelton" 
> 
> 
> ++
> 
> 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] IN OUT PARAMETERS

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

> In firebird is there an option to declare a parameter in a stored procedure
> as an IN/OUT. (like ORACLE server has)
> http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/parameter_declaration.
> htm
> 
> 
> 
> 
> 
> I want to send a parameter to a procedure, do some operation with it, and
> return his new value.

No, not with a single parameter. PSQL requires an explicit list of return 
parameters, of course with different parameter names as the input parameters.

The language reference is available here:
http://www.firebirdsql.org/refdocs/langrefupd25-ddl-procedure.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] Why does "IF (NOT(EXISTS(SELECT 1..." not work as expected?

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

> Hello 'stwizard',
> 
> Thursday, May 12, 2016, 12:46:25 AM, you wrote:
> 
>> Here is my simple Stored Procedure.  It simply looks for any
>> address in the ADDRESS table that starts with ‘0 ‘  as in “0 SE
>> ADAMS ST” and COUNT(*) how many time it might have been used in
>> PER_ADDRESS and if COUNT() > 0 delete the links from the PER_ADDRESS table.
> 
>>  
> 
>> The next line verifies that there is no remaining links in the
>> PER_ADDRESS table and then deletes the record from the ADDRESS table.
> 
>>  
> 
>> My problem is that even though ADDR_ID 347006 does not exist in the
>> PER_ADDRESS table, the “IF (NOT(EXISTS(SELECT 1..” line thinks there
>> is and skips the deletion of the record f  orm the ADDRESS table.  
> 
> 
>> What might I be doing wrong?
> 
> First of all, this is an executable SP, not intended to return a
> result set, so get rid of the RETURNS parameters and declare variables
> instead. While executable SPs *can* return a single-row result set,
> with your for-loop, the only result you would get back would be the
> values from the final iteration of the loop.  But that's not the
> reason for your unexpected results.
> 
> In your INTO clause, you are missing the colon (:) markers
> that are needed when variables or parameters are referred to in a DSQL
> statement.  I seem to recall that they are optional in v.2.5 (not
> sure) but that would not be a reason for me to omit them.
> 
> Your problem is your variables.  With both local variables and
> parameters that you are using like variables, you need to
> 
> (1) Initialise your variables before starting the loop (they start out
> as NULL)
> and
> (2) Re-initialise them at the end of the loop (otherwise, until the
> next time the loop gets a "hit", the variables retain the values that
> existed after the last "hit").
> 
> Also, once you fix those problems, I see no point in adding extra cost
> by revisiting the PER_ADDRESS table to verify the non-existence of
> the records you just deleted.  If the delete had failed, you would
> already be in an exception condition and would have jumped past that
> block to the last END statement.
> 
> You don't actually need ADDRESS1 in your working set, either, since
> you don't do anything with it.
> 
> With the correct initialisation/re-initialisation, you already have
> the value of ADDR_ID at that point:  either a genuine ID (if the
> current ADDRESS format matches the condition) or the dummy value from the
> initialisation (if no invalid addresses were found for that ADDR_ID in
> PER_ADDRESS).
> 
> CREATE PROCEDURE P_CLEAN_ADDR
> AS
>  DECLARE  ADDR_ID Integer = -1;
>  /* DECLARE ADDRESS VarChar(50) = ''; not needed */
>  DECLARE PER_ADDR_CNT SmallInt = -1;
> 
> begin
>  FOR SELECT A.ADDR_ID,
> /* A.ADDRESS1, not needed */
> (SELECT COUNT(*) FROM PER_ADDRESS PA
>  WHERE PA.ADDR_ID = A.ADDR_ID) AS PER_ADDR_CNT
> 
> FROM ADDRESS A
> WHERE ADDRESS1 STARTING WITH '0 '
> INTO :ADDR_ID, /* :ADDRESS, */ :PER_ADDR_CNT
>   DO
>   BEGIN
>   IF (PER_ADDR_CNT > 0) THEN
>   begin
> DELETE FROM PER_ADDRESS WHERE ADDR_ID = :ADDR_ID;
> DELETE FROM ADDRESS WHERE ADDR_ID = :ADDR_ID;
>   end
>   -- re-initialise variables
>   ADDR_ID = -1;
>   /* ADDRESS = ''; */
>   PER_ADDR_CNT = -1;
>   END
> end ^^

Very exhaustive and a lot of useful advices ... ;-)

He also could simply have a cascading delete foreign key constraint on 
PER_ADDRESS referencing ADDRESS.



--
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] Simply bad, new is not always better. FB3 and ODBC

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

[snip] 

>> Also the post_event behaves different than the old firebird, the
>> never changed mfc (c++) application gets it sometimes, in many cases
>> not. Only the version of firebird is changed.
> 
> Post_event was not changed in Firebird 3.  Are you sure that your
> customer's applications are loading the correct version of
> fbclient.dll?  This question is applicable to your other problems,
> too.

Looks a bit like: http://tracker.firebirdsql.org/browse/CORE-5210



--
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] EF6 - Why extra index created?

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

> I try the ordinary EF CodeFirst test with latest packages for new FB3.
> 
> 
>   
>targetFramework="net452" />
>targetFramework="net452" />
> 
> 
> -
> 
> public class Blog
>   {
> public int  id{ get; set; }
> public string   name  { get; set; }
> public DateTime created   { get; set; }
> public string   comment   { get; set; }
> 
> public virtual List posts { get; set; }
>   }
> 
>   public class Post
>   {
> public int id { get; set; }
> public string content { get; set; }
> 
> public int blogId { get; set; }
> 
> public virtual Blog blog { get; set; }
>   }
> 
> 
> 
>   public TestContext()
> : base(connection, true)
> {
> 
> }
> 
> public DbSet Blog { get; set; }
> public DbSet Posts { get; set; }
>   }
> 
> ...
> 
> Database.SetInitializer(new MigrateDatabaseToLatestVersion Configuration>());
> 
> -
> 
> This code creates this table definition:
> 
> CREATE TABLE "Posts"
> (
>   "id" Integer NOT NULL,
>   "content" Blob sub_type 1,
>   "blogId" Integer NOT NULL,
>   CONSTRAINT "PK_Posts" PRIMARY KEY ("id")
> );
> ALTER TABLE "Posts" ADD CONSTRAINT "FK_Posts_Blogs_blogId"
>   FOREIGN KEY ("blogId") REFERENCES "Blogs" ("id") ON DELETE CASCADE;
> CREATE INDEX "IX_Posts_blogId" ON "Posts" ("blogId");
> GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
>  ON "Posts" TO  SYSDBA WITH GRANT OPTION;
> 
> 
> 
> BUT: why is created a "IX_Posts_blogId" index when the 
> "FK_Posts_Blogs_blogId" index created for FK?

There are databases out there, e.g. MS SQL Server, which do not create an index 
for FK constraints behind the scene.

I'm not really into the .NET world or EF in particular, but I wonder if the 
code responsible for creating the DDL is something in the Firebird .NET 
provider or EF 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.

Re: [firebird-support] Fast Slow Stored procedure

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

Hi Jack,

> Hi Thomas
>
> I did a trace as you suggested.
> The slow SP shows these 3 consecutive statements showing a 20 min gap of
> no activity
>
> 2016-05-04T14:25:30.5020 (3268:061B51F8) EXECUTE_PROCEDURE_START
> C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_45, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
> C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:8304
> (TRA_382, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)
>
> Procedure CLEAR_BUILD_DXS:
> param0 = integer, "0"
>
>
> 2016-05-04T14:45:39.7880 (3268:061B51F8) PREPARE_STATEMENT
> C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_45, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
> C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:8304
> (TRA_382, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)
>
> Statement 605:
> ---
> Insert into TEMP_IMPORT_DXS (DXS_NO, ABS_NO, DB,  DW, VALUE1, USED,
> SPACE, UOP, EOL)
> select DXS_NO, ABS_NO, DB, DW, VALUE1, USED, SPACE, UOP, EOL
> from IMPORT_DXS
> ^^^
>
> The fast SP there is a only a small gap
>
> 2016-05-05T07:24:05.3290 (3268:061B51F8) EXECUTE_PROCEDURE_START
> C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_52, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
> C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:5636
> (TRA_417, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)
>
> Procedure CLEAR_BUILD_DXS:
> param0 = integer, "0"
>
>
> 2016-05-05T07:24:13.4540 (3268:061B51F8) PREPARE_STATEMENT
> C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_52, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
> C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:5636
> (TRA_417, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)
>
> Statement 605:
> ---
> Insert into TEMP_IMPORT_DXS (DXS_NO, ABS_NO, DB,  DW, VALUE1, USED,
> SPACE, UOP, EOL)
> select DXS_NO, ABS_NO, DB, DW, VALUE1, USED, SPACE, UOP, EOL
> from IMPORT_DXS
> ^^^
>
> Can you make any suggestion as to what happened for 20 min

No, sorry. You either have removed parts of the trace or you need a 
different trace configuration, particular the various *_FINISH entries, 
which usually print out the execution 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] Fast Slow Stored procedure

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

> What are the possible reason for a SP that normally runs in approx 20 seconds
> then Occasionally takes 15 minutes, (or a method of trying to find the cause 
> of
> the problem).
> 
> 
> 
> Firebird Details
> Server Version: WI-V2.5.5.26952 Firebird 2.5 32 bit Superserver,
> CpuAffinityMask = 1 in firebird.conf

A few ideas:
* Some sort of lock contention at transaction level caused by concurrent 
read/write requests on the same (range) of records running transaction in WAIT 
and/or NO RECORD_VERSION mode
* Firebird 2.5 Superserver is basically single CPU/core bound for a single 
database environment, but IMHO not an explanation for that large difference
* Watch out typical response time influencers like execution plan, I/O fetches, 
records fetched etc ...
* Query the monitoring tables when your SP is stalled and/or run a continuous 
trace through the Trace API



--
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 3.0 and GDS32.DLL

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

Hello Michael,

> Hi
> 
> I have succesfully installed Firebird 3.0 on my home computer. 
> I can use our program. We have 3 main programs. Two are using IBX component 
> and
> the last uses IBObjects.
> On my local mashine it runs smoothly.
> 
> I have then installed Firebird 3.0 alongside Firebird 2.5 and 2.1 on a 
> server. 
> Using Database Workbench I can connect to all 3 types of databases. No 
> problem.
> 
> 
> But the moment I try to start any program, I get this instantly:
> 
> Exception EIBClientError in module EasyPOSSalg.EXE at 001ED40.
> Interbase library gds32.dll not found in the path. PLease install Interbase to
> use this functionality.
> 
> I have located gds32.dll in my Windows\System32 folder. 
> I have tried taking FBClient.DLL from FB3.0 and copy and rename to same 
> folder.
> 
> I have tried taking the gds32.dll from my local machine. 
> All ends with the same. 
> 
> The gds32.dll has a size of 1.739.776. Same as FBClient.DLL
> 
> What am I missing here?

Possibly a bit-ness mismatch e.g. Firebird 64-bit client library vs. your 
32-bit client application?


--
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: Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems

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

Hi,

> This is the Query plan from SS FB 3.0
>
> PLAN (GET_STCK NATURAL)
>
> and here is the Query plan from SC FB 2.5.2
>
> PLAN (G_T3 NATURAL)(M_TT INDEX (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A
> INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (TM_D), A INDEX (T2_T1), C INDEX
> (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F
> INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))(M_TT INDEX
> (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B
> INDEX (T1_M_DL), A INDEX (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX
> (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G
> INDEX (RDB$PRIMARY22)))(M_TT INDEX (RDB$PRIMARY2))(A INDEX
> (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (T1_M_DL), A INDEX
> (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX
> (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))SORT
> ((G_T3 NATURAL))
>
> This could be a big problem, all queries/stored procedures/triggers must
> be test again for its performance.

Are you sure that you didn't deactivate indexes during the restore?


-- 
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] SELECT WITH LOCK, WAIT transaction, deadlock

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

Hi,

> 2016. 04. 28. 21:22 keltezéssel, Ann Harrison aharri...@ibphoenix.com 
> [firebird-support] írta:
>> Okay...  What's the requirement that keeps you from using generators?
> 
> I need number generators which give back sequence numbers without 
> skipping and repeating. But don't know how many needed. The number of 
> needed generators depend on the customers(X Ltd., Y Ltd, ...). This is 
> why I want to store the generators in a table.
> 
>> Do you intend to grab the number in a transaction that does other work?
> 
> The transaction do other work before grab the number and after grab the 
> number. The whole thing is in a (one) stored procedure. (Works like a 
> charm except deadlocks.)
> 
>> What is the concurrency requirement?
> 
> If I understand your question correctly... I don't think will be high 
> load but I test is because not want to see deadlock in production.
> 
>> What you're thinking about doing
>> will serialize the generation of identifiers and all other actions of
>> those transactions.  I'm not totally convinced that using
>> ReadCommitted/wait will create an auditable series of numbers in the
>> case where a transaction fails.
> 
> For testing purposes the test app insert the generated numbers into a 
> one field table which have a unique constraint. I not see any error 
> except the deadlocks.

I don't see deadlocks with the following test case.

1) Create some tables and a stored procedure doing the access + increment

CREATE TABLE GENERATOR_TABLE 
(
  ID   BIGINT NOT NULL
);
CREATE TABLE GENERATOR_TABLE_LOG 
(
  IDBIGINT NOT NULL,
  ID_NEWBIGINT NOT NULL,
  ID_DIFF   BIGINT NOT NULL
);

ALTER TABLE GENERATOR_TABLE ADD CONSTRAINT PK_GENERATOR_TABLE PRIMARY KEY
  (ID);

ALTER TABLE GENERATOR_TABLE_LOG ADD CONSTRAINT PK_GENERATOR_TABLE_LOG PRIMARY 
KEY
  (ID);
  
SET TERM ^^ ;
CREATE PROCEDURE P_INCREMENT returns (
  ID BigInt) AS
BEGIN
  SUSPEND;
END ^^
SET TERM ; ^^
SET TERM ^^ ;
ALTER PROCEDURE P_INCREMENT returns (
  ID BigInt) AS 
declare i bigint;
declare id_new bigint;
begin
  i = 1;
  while (i <= 1) do
  begin 
in autonomous transaction do
begin
  select id from generator_table with lock into :id;
  id_new = id + 1;
  insert into generator_table_log (id, id_new, id_diff) values (:id, 
:id_new, :id_new - :id);
  update GENERATOR_TABLE set id = :id_new where id = :id;
  i = i + 1;
end
  end
  suspend;
end ^^
SET TERM ; ^^

COMMIT;

INSERT INTO GENERATOR_TABLE (ID) VALUES (0);
COMMIT;


The autonomous transaction stuff is used to split up the look into smaller 
chunks from a perspective of the other isql sessions, otherwise conccurent 
transactions will wait, until the entire SP is finished from the isql session.


2) Fire up e.g. 5 isql sessions and execute the following concurrently:

SET TRANSACTION READ WRITE ISOLATION LEVEL READ COMMITTED RECORD_VERSION WAIT;
EXECUTE PROCEDURE P_INCREMENT;


3) Result

SQL> select count(*), min(id_new), max(id_new) from generator_table_log;

COUNT   MIN   MAX
= = =
5 1 5


No gaps, no deadlock messages, no pk/unique violation in the log table etc ...

It is crucial that accessing the central generator table is tunneled through a 
single access point. The SP in our case. Any chance that anything else is 
accessing the table in your scenario resulting in the deadlock / write update 
conflict?



--
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] SELECT WITH LOCK, WAIT transaction, deadlock

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

> 2016. 04. 28. 14:55 keltezéssel, Dimitry Sibiryakov s...@ibphoenix.com
> [firebird-support] írta:
>> 28.04.2016 13:41, Gabor Boros gaborbo...@yahoo.com [firebird-support] wrote:
>>> The message is:
>>> deadlock
>>> update conflicts with concurrent update
>>> concurrent transaction number is ...
>>
>> That's an expected behavior.
>
> Are you sure?
>
> In the language reference for "isc_tpb_read_committed + isc_tpb_wait" I
> see this: "Update conflict exceptions can never be raised by an explicit
> lock statement in this TPB mode."
>
> http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-with-lock

You have mentioned that you are using Read Committed without record 
version. What happens if you are running Read Committed WITH record version?



-- 
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] FB 3.0, how to get IDENTITY of last inserted row

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

Hello Zoran,

>
> In FB 2.5 I am using
>
> insert into MyTable (col1, col2) values(val1, val2) returning Id
>
> where Id is autoinc column (sequence generator + before insert trigger).
>
> How do I do it in FB 3.0? It doesn’t like “returning Id”. It displays
> this error: SQLDA error. Wrong number of parameters (expected 1, got 0)
>
> I have Id defined as IDENTITY. Can I get INDENTITY for the last inserted
> row without using SELECT GEN_ID(generator-name, 0) AS ID FROM RDB$DATABASE ?
>
> I am trying this with Firebird Maestro 15.11.0.1 (supports FB 3) on Win
> Server 2012 R2. With FB 2.5 all works as expected.

The following works fine for me with FB3 (server + client library):

create table t1 (
   id integer generated by default as identity primary key
   , fname varchar(15)
);

commit;

In isql:

C:\Firebird\Firebird_300_4050_x86>isql -user sysdba -password masterkey 
fb3test.fdb
Database: fb3test.fdb, User: SYSDBA
SQL> insert into t1 (fname) values ('hugo') returning id;

   ID

   15


So, in general it works. ;-)


-- 
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] conect flamerobin with Firebird3

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

> Create a firebird 3 bd in another user who is not sysdba
> and create a role called sysdba without permission
> and when I try to connect from flamerobin you give me the following error
> message
> 
> *** IBPP::SQLException ***
> Context: Database::Connect
> Message: isc_attach_database failed
> 
> SQL Message : -902
> Unsuccessful execution caused by a system error that precludes successful
> execution of subsequent statements
> 
> Engine Code: 335544745
> Engine Message :
> Your login SYSDBA is same as one of the SQL role name. Ask your database
> administrator to set up a valid Firebird login.
> unknown ISC error 335545081
> 
> As I can overcome this error?

Are you sure that FlameRobin picks up the FB 3 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.

Re: [firebird-support] How to get FB 3.0 x64 work as embeded version?

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

Hello James,

> After FB 3.0 released, I am trying to immigrate my DB application to this new
> version.
> 
> 
> My DB app is running with embeded version before, so now I am still trying to
> get it work under embeded mode.
> I tried FB 3.0 x32, it works as embedded version well after I created a new
> user SYSDBA (as in FB 2.5).
> But for x64 package, I got error like this:
> 
> 
> Error message:
> [FireDAC][Phys][FB]-314. Cannot load vendor library [fbclient.dll or
> fbembed.dll]. fbclient.dll has unsupported architecture [x64]. Required [x86].

This looks to me like you are running/compiling your client application as 
32-bit trying to load fbclient.dll 64-bit, which obviously does not work.

The bitness of your client application and the Firebird client/embedded DLL 
must match.

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

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



> The specified module could not be found.
> Hint: check it is in the PATH or application EXE directories, and has x86
> bitness.
> 
> 
> What I have done (same as x32 package):
> 1. Unzip FB3.0 x64 zip file to a folder.
> 2. Run isql to create a new user SYSDBA with password masterkey.
> 3. Copy my DB application to this folder.
> 4. Run this application and got error.
> 
> 
> OS: Windows 7 x64 Ultimate English
> FB: No firebird server was installed as service.
> DB: FDB file is upgraded to new format v12.0.
> 
> 
> 
> 
> Best regards,
> 
> 
> James

Re: [firebird-support] Backup with admin rights is prohibited?

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

> 2016. 04. 22. 15:57 keltezéssel, Gabor Boros gaborbo...@yahoo.com 
> [firebird-support] írta:
>> 2016. 04. 22. 15:37 keltezéssel, Dimitry Sibiryakov s...@ibphoenix.com
>> [firebird-support] írta:
>>> 22.04.2016 15:34, Gabor Boros gaborbo...@yahoo.com [firebird-support] wrote:
 Now tried to do a backup (gbak -B -T
 -USER MYADMIN MYDB /home/user/MYDB.bak) and gbak say "You must be either
 SYSDBA or owner of the database".
>>>
>>>You forgot to mention role ADMIN in the command line.
>>
>> With "gbak -B -T -USER MYADMIN -ROLE RDB$ADMIN MYDB /home/user/MYDB.bak"
>> got the same error.
> 
> Other idea?

Honestly, IMHO, if the user MYADMIN is a member of the RDB$ADMIN role in MYDB, 
then your gbak call should work.


--
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 performance slowdown

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

Hi Neil,

> We are explicitly starting and committing our own transactions rather than
> relying on any auto-commit behaviour.
>
> We are running Classic (sorry I should have told you that already).
>
> I'll get our devs to checkout the MON$ tables and see what additional info
> can be gleaned from them.
>
> A current gstat -h output is this below, we experienced the slowdown a
> little while ago tonight.
>
> Database header page information:
>   Flags   0
>   Checksum12345
>   Generation  2240486
>   Page size   8192
>   ODS version 11.2
>   Oldest transaction  1616642
>   Oldest active   2143185
>   Oldest snapshot 2143185
>   Next transaction2168081
>   Bumped transaction  1
>   Sequence number 0
>   Next attachment ID  72398
>   Implementation ID   26
>   Shadow count0
>   Page buffers0
>   Next header page0
>   Database dialect3
>   Creation date   Mar 26, 2016 10:35:08
>   Attributes  force write
>
>  Variable header data:
>   Sweep interval: 0
>   *END*

The oldest transaction (limbo or a larger transaction got rolled back) 
is way behind compared to oldest active and oldest snapshot. This can 
cause performance issues, because upon starting a new transaction, the 
transaction holds a private copy of the so-called TIP (transaction 
inventory page), which gets bigger and bigger with an increasing gap of 
the transaction counters and out-dated record versions won't get garbage 
collected as well.

Your sweep task in the night does not seem to be able to do its job, 
because otherwise the oldest transaction should move forward. Ideally 
you schedule the sweep in a maintenance window ideally with zero 
connections or at least low load.

Another reason why the oldest transaction can get stuck is a (failed) 
distributed transaction (aka 2PC). This needs manual intervention 
through gfix to resolve such a transaction either as committed or rolled 
back. Any chance to you are using something which spans a single 
transaction across several databases? E.g. replication comes to my mind.



-- 
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] Installing Firebird 3.0 alongside Firebird 2.5 and 2.1

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

> Michael,
> 
>> I have a testserver currently running both Firebird 2.1 and 2.5.
>> I need this to support and test various situations with different firebird
>> versions.
>> 
>> Now I would like to install Firebird 3.0 manually as well, so I can have 2.1,
>> 2.5 and 3.0 on the same server.
>> 2.1 listens on port 3050, 2.5 listens on 3060 and I have set 3.0 to listen on
>> 3070.
>> 
>> So far so good. 
>> I then executed the install_service.bat Firebird_3_0
>> I can now see its running in process manager. 
>> 
>> But I'm having problems connecting to the server.
>> Using Database Workbench to create a server entry I instantly get
>> 
>> connection rejected by remote interface
>> 
>> Sinse security is handled quite differently in Firebird 3.0 I suspect I am
>> missing something here. 
>> 
>> Could someone either give me a step-by-step instructions to install Firebird
>> 3.0 manually or point me to places where I can read some about it.
>> 
>> 
>> On a difference machine I did run the installer, and I did get Firebird 3.0 
>> up
>> and running. But I need it on this server alongside the other firebird
>> installations
> 
> For test purposes I tend to use Firebird 32-bit, because most/all of my client
> applications are 32-bit, thus I can easily point the client application to the
> targeted 32-bit fbclient.dll.
> 
> Step-by-step:
> 
> * Extract the FB 3 ZIP distribution
> * Change the tcp port to 3070 in firebird.conf
> * The security database is not initialized when installing from ZIP, thus we
> need a few additional steps
> - We need an embedded connection with Firebird 3, thus stop ALL Firebird
> processes including 2.1, 2.5 etc.
> - Go to FB 3 install directory, and type:
> 
> isql -u user sysdba employee

Of course:

isql -u sysdba employee


Sorry.


Thomas

Re: [firebird-support] Installing Firebird 3.0 alongside Firebird 2.5 and 2.1

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

Michael,

> I have a testserver currently running both Firebird 2.1 and 2.5.
> I need this to support and test various situations with different firebird
> versions.
> 
> Now I would like to install Firebird 3.0 manually as well, so I can have 2.1,
> 2.5 and 3.0 on the same server.
> 2.1 listens on port 3050, 2.5 listens on 3060 and I have set 3.0 to listen on
> 3070.
> 
> So far so good. 
> I then executed the install_service.bat Firebird_3_0
> I can now see its running in process manager. 
> 
> But I'm having problems connecting to the server.
> Using Database Workbench to create a server entry I instantly get
> 
> connection rejected by remote interface
> 
> Sinse security is handled quite differently in Firebird 3.0 I suspect I am
> missing something here. 
> 
> Could someone either give me a step-by-step instructions to install Firebird
> 3.0 manually or point me to places where I can read some about it.
> 
> 
> On a difference machine I did run the installer, and I did get Firebird 3.0 up
> and running. But I need it on this server alongside the other firebird
> installations

For test purposes I tend to use Firebird 32-bit, because most/all of my client 
applications are 32-bit, thus I can easily point the client application to the 
targeted 32-bit fbclient.dll.

Step-by-step:

* Extract the FB 3 ZIP distribution
* Change the tcp port to 3070 in firebird.conf
* The security database is not initialized when installing from ZIP, thus we 
need a few additional steps
- We need an embedded connection with Firebird 3, thus stop ALL Firebird 
processes including 2.1, 2.5 etc.
- Go to FB 3 install directory, and type:

isql -u user sysdba employee

In isql:

create user sysdba password 'yoursecretpwd';
commit;
exit;

Your security database has now 1 user SYSDBA with the new SRP 
security/authentication in place

* Being still in FB 3 install directory, now install the FB 3 service (adapt 
service name properly. I tend to include the version, port and bitness in the 
service name):

install_service.bat Firebird_300_3070_x86

* Start all other Firebird processes (2.1, 2.5 etc.) again

* The new SRP authentication makes the usage of the 3.0 fbclient.dll mandatory! 
I guess in your case, DBW was simply using an older FB client library. So, in 
Database Workbench, upon server registration, explicitely provide the path to 
32-bit fbclient.dll of Firebird 3.


You could also run the Firebird 3.0 server in some sort of legacy 
authentication mode where you can use an older fbclient.dll, but personally, 
for test purposes, I would like to try out the new stuff. ;-)

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.




> 
> Thank you
> Michael
> 

Re: [firebird-support] Database performance slowdown

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

Neil,

> We are investigating an issue with one of our systems where it gradually
> slows down during the course of a day.
>
> We have our  epos system that we write ourselves using Delphi XE2 and
> the IBX components.

How does your transaction model/layer look like in your client 
application with IBX?

If you are not starting an explicit transaction yourself, IBX is running 
in "auto commit" mode, wrapping each statement into it's own transaction 
context and commit the statement with a commit retaining, which 
basically preserves the physical transaction context and makes your 
transaction counters (OIT/OAT/OST) stuck.

Any chance to run gstat -h when your experience such a response time drop?


> We have a group of tables that are updated during the end of sale save
> process.
>
> Typically this whole save process takes milliseconds, but on one
> particular site we see it taking several seconds and occasionally taking
> up to 35 seconds.
>
> We have done some digging  into the behaviour of Firebird, we are
> currently using v2.5, and it appears that Firebird needs to check the
> entire chain of back versions when reading a record from the database.
> Is this the cooperative Garbage Collection process running?

If you are running Classic or SuperClassic architecture, then yes. In 
these architectures, each statement is also doing garbage collection in 
its execution context.


> It seems like it would be logical for a lookup to only search back
> records until it finds the record that is supposed to be visible to it,
> but it appears to go all the way back.

Visible from a transaction context. Beside the transaction ID, also the 
used isolation level needs to be taken into account. With using commit 
retaining behind the scenes, e.g. due to auto commit, you basically end 
up in accumulating back record versions.


> This causes us a slowdown if there are a lot of back versions still
> active, and flags up data as corrupted if something untoward has
> happened to them, even if the current committed version of the record
> seems to be valid.
>
> Can anyone confirm this behaviour and any way to work around it?

To be sure, if the slowdown for a single statement is driven by 
cooperative garbage collection, you can use the Trace API and its 
detailed statistics output (something like "backouts", "purges", 
"expunges") per statement. The MON$RECORD_STATS monitoring table is 
another source for that information, if your statement is still 
available through MON$ 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] Re: Connecting to Firebird database from two or more pc with same username

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

> On Mon, Apr 18, 2016 at 10:03 AM, 'Thomas Steinmaurer' t...@iblogmanager.com
> [firebird-support] <firebird-support@yahoogroups.com> wrote:
> 
>> > On Thu, 14 Apr 2016 12:28:41 +0200 (CEST), 'Thomas Steinmaurer'
>> > t...@iblogmanager.com [firebird-support] <firebird-support@yahoogroups.com
>> >
>> > wrote:
>> >>
>> >>
>> >> Yep. If one want to have some sort of client login/user available on
>> >> the server-side, which might be totally different to the Firebird
>> >> user used upon connect time, a common approach is to put that login
>> >> name into a context variable via RDB$SET_CONTEXT(...) and query that
>> >> in context of the connection with RDB$GET_CONTEXT(...). Needless to
>>
> 
> 
>> > Virgo Pärna wrote:
> 
>>
>> >   Unless I'm misunderstanding it, it is not possible to read
>> > context variable from another connection?
>>
>> No. There are two "namespaces" USER_SESSION and USER_TRANSACTION which
>> also define the visibility of context variable values.
>>
>> Check out:
>> http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-set_context.html
> 
> 
> Maybe I'm confused, but I think Virgo is asking about two different
> connections using the same
> user credentials, not a an application server with a single connection to
> the database and multiple
> connections from the client to the application.  Different connections have
> different USER_SESSION
> namespaces even if they use the same user credentials, I think.

Context variables values are separated by physical connection/transaction and 
not credentials used at connect time.


Regards,
Thomas

Re: [firebird-support] Re: Connecting to Firebird database from two or more pc with same username

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

> On Thu, 14 Apr 2016 12:28:41 +0200 (CEST), 'Thomas Steinmaurer'
> t...@iblogmanager.com [firebird-support] <firebird-support@yahoogroups.com>
> wrote:
>>
>>
>> Yep. If one want to have some sort of client login/user available on
>> the server-side, which might be totally different to the Firebird
>> user used upon connect time, a common approach is to put that login
>> name into a context variable via RDB$SET_CONTEXT(...) and query that
>> in context of the connection with RDB$GET_CONTEXT(...). Needless to
> 
>   Unless I'm misunderstanding it, it is not possible to read
> context variable from another connection?

No. There are two "namespaces" USER_SESSION and USER_TRANSACTION which also 
define the visibility of context variable values.

Check out: 
http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-set_context.html


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

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


> 
> -- 
> Virgo Pärna 
> virgo.pa...@mail.ee
> 
> 
> 
> 
> Posted by: Virgo =?UTF-8?Q?P=C3=A4rna?= <virgo.pa...@mail.ee>
> 
> 
> ++
> 
> 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: AW: [firebird-support] How to interprete gstat-results

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

Hi Olaf,

> Database "D:\INTERBASE\IFLS.GDB"
> 
> Database header page information:
> 
>Flags   0
> 
>Generation  2939162
> 
>System Change Number0
> 
>Page size   4096

You might consider increasing to 8K, especially when the tree depth of an index 
starts to become > 3. To be checked with gstat -i.

>ODS version 12.0

Indeed, the database was created with Firebird 3. ;-)

>Oldest transaction  2893677

The "oldest interesting" transaction (OIT) in a state other than committed, 
e.g. also rolled back. There are no record versions for transactions < 2893677 
available (cause they have been garbage collected), thus one could say why the 
marker is called "interesting".

>Oldest active   2929833

The oldest transaction currently active/running. This is the lowest transaction 
ID which should be visible in MON$TRANSACTIONS.

>Oldest snapshot 2878316

This is the oldest transaction started in snapshot mode. Hope I remembered that 
correctly.

>Next transaction2938824

Transaction id for the next new transaction.
 
>Sequence number 0
> 
>Next attachment ID  87910
> 
>Implementation  HW=Intel/i386 little-endian OS=Windows
> CC=MSVC
> 
>Shadow count0
> 
>Page buffers0

The default value in firebird.conf is used for the size of the page cache of 
the database. Might be too small, depending on your use case.


>Next header page0
> 
>Database dialect3
> 
>Creation date   Mar 7, 2016 18:33:37
> 
>Attributes  force write
> 
> 
> 
>Variable header data:
> 
>Sweep interval: 5000

That's not a good idea. Because if the difference of OIT and OST (oldest 
snapshot) reaches that threshold, a sweep will kick in, visits all records in 
the database and removes out-dated record versions which are not interesting 
for other transactions anymore.

Regarding OIT/OAT movement/gaps etc., commit retaining is one of your #1 
enemies. Usually used behind the scene by drivers with auto commit = ON.



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

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


> 
>*END*
> 
> 
> 
> 
> 
> 
> 
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] 
> Gesendet: Donnerstag, 14. April 2016 14:03
> An: firebird-support@yahoogroups.com
> Betreff: Re: [firebird-support] How to interprete gstat-results
> 
> 
> 
>  
> 
>> Hello @ll,
>> 
>> 
>> 
>> we are using firebird 3.0 RC2 and have some questions about open
>> transactions. (Clients connect over odbc) The gstat output gives 7
>> transactions between the oldest and the current transaction, if we look
> into
>> the monitoring table, we see only 10 transactions. The "oldest one" we
>> cannot find in this table. What is the oldest transaction and which roll
>> does the garbage collector play? If we have many open transactions, the
>> performance is bad. What role plays the attachments? What is the best way
> to
>> check the performance bottleneck in such cases?
> 
> Please post an entire gstat -h 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] How to interprete gstat-results

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

> Hello @ll,
> 
> 
> 
> we are using firebird 3.0 RC2 and have some questions about open
> transactions. (Clients connect over odbc) The gstat output gives 7
> transactions between the oldest and the current transaction, if we look into
> the monitoring table, we see only 10 transactions. The "oldest one" we
> cannot find in this table. What is the oldest transaction and which roll
> does the garbage collector play? If we have many open transactions, the
> performance is bad. What role plays the attachments? What is the best way to
> check the performance bottleneck in such cases?

Please post an entire gstat -h 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] Connecting to Firebird database from two or more pc with same username

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

> On 14/04/2016 08:26, 'Thomas Steinmaurer' t...@iblogmanager.com 
> [firebird-support] wrote:
>>
>> With a single user you basically can't distinguish people on the 
>> server side through CURRENT_USER etc.
>>
> We use one Firebird user for the web server, and then one each for each 
> of several batch processes, so if we poke around in the database we can 
> see at least which part of the system any problematic (eg very long 
> running) user sessions or transactions or queries belong to.

Yep. If one want to have some sort of client login/user available on the 
server-side, which might be totally different to the Firebird user used upon 
connect time, a common approach is to put that login name into a context 
variable via RDB$SET_CONTEXT(...) and query that in context of the connection 
with RDB$GET_CONTEXT(...). Needless to say, for pooled connections in a 
middleware, one needs to take care of updating the context variable value which 
each connection aquisition. 


--
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] Connecting to Firebird database from two or more pc with same username

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

> Hello,
> 
> Il 14/04/2016 09.26, 'Thomas Steinmaurer' t...@iblogmanager.com 
> [firebird-support] ha scritto:
>>
>> I guess you mean with the same Firebird user name? Or are you using 
>> Windows trusted authentication?
>>
> 
> Oops, sorry. I mean the same Firebird username.
> I notice that in an LAN with three users, all users was authenticated 
> with the same Firebird username at the same time!
> I did not notice any malfunction, but I doubt that it is not safe and is 
> the bearer of impending problems on the data.
> 
>> In general this is fine of course, even many people are using SYSDBA 
>> for regular connects, although this is not a good idea from a security 
>> perspective.
>>
> 
> Ok but, as you say, and if I understand correctly, it is safe and 
> Firebird is imperishable its data, even if it gets the data from 
> different PCs at the same time and with the same FB user name. It's right?

>From a pure Firebird POV, yes.


--
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] Connecting to Firebird database from two or more pc with same username

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

> Hello,
>   If a user try to connect from two or more PC with same username, 
> Firebird accepts the connection but is safe without problems or is best 
> to avoid like the pest?

I guess you mean with the same Firebird user name? Or are you using Windows 
trusted authentication?

In general this is fine of course, even many people are using SYSDBA for 
regular connects, although this is not a good idea from a security perspective.

With a single user you basically can't distinguish people on the server side 
through CURRENT_USER etc.


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

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



> 
> Thanks.
> -- 
> 
> Luigi Siciliano
> --
> 
> 
> 
> 
> 
> 
> 
> ++
> 
> 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] monitoring Table

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

Hello Olaf,

'Checkmail' check_m...@satron.de [firebird-support] schrieb am 13.04.2016 14:38:

> Hello,
> 
> 
> 
> I've a problem with our database, firebird 3.0 RC2, some Clients (C++,
> Microsoft Access over Firebird ODBC). There are 7 open transactions. But
> in the monitoring Table, I cannot see the one who is the oldest was shown.
> Why?

How/where do you identify the oldest transaction?


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

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



> 
> 
> 
> INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
> MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
> MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
> MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)
> 
>  VALUES (2834893, 85000, 1, '2016-04-13 14:34:31',
> 2834893, 2774214, 2771000, 2, 0, 0, 0, 1, 201);
> 
> INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
> MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
> MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
> MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)
> 
>  VALUES (2208191, 68114, 0, '2016-04-07 05:03:44',
> 2208191, 2768721, 2771000, 2, -1, 1, 0, 1, 319);
> 
> INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
> MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
> MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
> MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)
> 
>  VALUES (2833276, 83617, 1, '2016-04-13 07:00:29',
> 2771000, 2768721, 2769594, 2, -1, 0, 0, 1, 1267);
> 
> INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
> MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
> MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
> MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)
> 
>  VALUES (2834878, 84239, 1, '2016-04-13 10:38:25',
> 2792941, 2768721, 2771000, 2, -1, 0, 0, 1, 1677);
> 
> INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
> MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
> MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
> MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)
> 
>  VALUES (2834800, 84681, 1, '2016-04-13 14:21:39',
> 2832348, 2768721, 2771000, 2, -1, 0, 0, 1, 1933);
> 
> INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
> MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
> MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
> MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)
> 
>  VALUES (2833230, 84857, 1, '2016-04-13 14:25:23',
> 2833010, 2768721, 2771000, 2, -1, 0, 0, 1, 2196);
> 
> INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
> MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
> MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
> MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)
> 
>  VALUES (2829258, 84866, 1, '2016-04-13 14:06:50',
> 2829258, 2768721, 2771000, 2, -1, 0, 0, 1, 2293);
> 
> INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
> MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
> MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
> MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)
> 
>  VALUES (2834822, 84891, 1, '2016-04-13 14:13:14',
> 2829911, 2768721, 2771000, 2, -1, 0, 0, 1, 2358);
> 
> INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
> MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
> MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
> MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)
> 
>  VALUES (2833355, 84977, 1, '2016-04-13 14:27:03',
> 2833355, 2768721, 2771000, 2, -1, 0, 0, 1, 2450);
> 
> INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
> MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
> MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
> MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)
> 
>  VALUES (2833491, 84980, 1, '2016-04-13 14:28:18',
> 2833491, 2768721, 2771000, 2, -1, 0, 0, 1, 2527);
> 
> INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
> MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
> MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
> MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)
> 
>  VALUES (2834880, 85006, 1, '2016-04-13 14:34:02',
> 2834880, 2774214, 2771000, 2, -1, 0, 0, 1, 2664);
> 
> 
> 
> COMMIT WORK;
> 
> 
> 
> Thank you
> 
> 
> 
> Best 

Re: [firebird-support] Cannot restore 32GB DB - fails at end, unusable. Any ideas please.

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

> There is 77GB free out of 148GB at the minute and it has restored 9GB so
> far, final size ~33GB so on the surface looks adequate unless very large
> temp files are created when the indexes are reactivated?

Yes. This may happen, depending on the index size. Are you sure that 
temp space is actually pointing to the disk with 77G free space?

* What is the value for the TEMP/TMP environment variable (in case you 
are on Windows)?
* Any manual changes for TempDirectories 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] Cannot restore 32GB DB - fails at end, unusable. Any ideas please.

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

Hi!

> gbak:activating and creating deferred index K_MERCURY_EMAIL
>
> gbak:cannot commit index K_MERCURY_EMAIL
>
> gbak: ERROR:operating system directive CreateFile failed

Any chance that you are running out of disk where your TEMP directory is 
located?


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

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



> gbak: ERROR:The system cannot find the path specified.
>
> Database restore canceled 21:17:19 due to IBPP exception:
>
>
> *** IBPP::SQLException ***
>
> Context: ServiceImpl::Wait
>
> Message: isc_service_query failed
>
>
> SQL Message : -901
>
> Unsuccessful execution caused by system error that does not preclude
> successful execution of subsequent statements
>
>
> Engine Code: 335544342
>
> Engine Message :
>
> action cancelled by trigger (3) to preserve data integrity
>
> Cannot deactivate index used by a PRIMARY/UNIQUE constraint
>
>
>
>
> 

Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware

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

Hi Patrick,

> Hi Thomas, nice to get a response from you. We already met in ~2010 in Linz at
> your office :)
> (ex. SEM GmbH, later Playmonitor GmbH)

I know. XING (Big Brother) is watching you. Nice to see that you are still 
running with Firebird. ;-)


> First, sorry for posting a mixed state of informations. The config settings i
> postet are the current settings.
> But the Lock-Table-Header was from last saturday (day of total system crash) -
> we changed Hash Slot Value since than, but it didn't work. New Table looks
> like:
> 
> 
> LOCK_HEADER BLOCK
> Version: 16, Active owner:  0, Length: 134247728, Used: 55790260
> Semmask: 0x0, Flags: 0x0001
> Enqs: 1806423519, Converts: 4553851, Rejects: 5134185, Blocks: 56585419
> Deadlock scans: 82, Deadlocks:  0, Scan interval:  10
> Acquires: 2058846891, Acquire blocks: 321584126, Spin count:   0
> Mutex wait: 15.6%
> Hash slots: 20011, Hash lengths (min/avg/max):0/   7/  18
> Remove node:  0, Insert queue:  0, Insert prior:  0
> Owners (297): forward: 385160, backward: 38086352
> Free owners (43): forward: 52978748, backward: 20505128
> Free locks (41802): forward: 180712, backward: 3620136
> Free requests (-1097572396): forward: 46948676, backward: 13681252
> Lock Ordering: Enabled
> 
> 
> The Min/Avg/Max hash lengths look better now, but as you mentioned the Mutex
> wait is worring us too.
> We have 2 direct questions about that.
> 
> 
> 1) What are the negative effects of increasing Hash-Slots (too high)?

It somehow defines the initial size of a hash table which is used for lock(ed) 
object lookup by a key (= hash value), ideally with constant O(1) run-time 
complexity. If the hash table is too small, due to a too small value for hash 
slots, it starts to degenerate into a linked/linear list per hash slot. Worst 
case resulting in O(n) complexity for lookups. The above 20011 setting shows an 
AVG hash length which looks fine.

As you might know, Classic having a dedicated process per connection model 
somehow needs a (global) mechanism to synchronize/protect shared data 
structures across these processes via IPC. This is what the lock manager and 
the lock table is used for.

> 2) As far as we know, we can't influence Mutex wait directly (it's just
> informational). But do you think that's the reason the underlying hardware is
> not utilized?

I don't think you are disk IO bound. Means, I'm not convinced that faster IO 
will help. Somehow backed by the high mutex wait. Under normal operations you 
see 100-500 IOPS with some room for further increase as shown in the 1700 IOPS 
backup use case. Don't know how random disk IO is in this two scenarios. Any 
chance to run some sort of disk IO benchmarks or do you already know your upper 
limits for your SAN IOPS wise?

> 
> 
> We do consider to upgrade to 2.5, but had our eyes on FB 3 over the last year,
> waiting for it to get ready.
> With 2.5.x we tested around a long time now, but never found a real reason to
> upgrade - since it's a reasonable amount of work for us. When you say it
> improves the lock contention, this sound pretty good. But again the question,
> do you think lock contention is limiting our system?

Dmitry, Vlad etc. will correct me (in case he is following the thread), but I 
recall that in 2.5, especially in SuperClassic being multi-threaded per worker 
process compared to Classic, now also allows specific(?) lock manager 
operations in parallel to regular request processing. In general I remember a 
mentioned improvement of ~25% in a TPC-C style workload with SuperClassic 
compared to Classic.

> 
> 
> First and foremost, we would really like to find the bottleneck. We just don't
> have the know-how to imagine something like "Fb 2.1 Engine is limiting us
> because of ..." and without that knowledge it's hard to take actions like
> upgrading to 2.5.
> 
> 
> We'll try to collect information about the garbage we create :) We do run
> "Sinatica Monitoring" on the server, which shows us "Awaiting Gargabe
> Collection" Transactions. Is that the information you'r looking for?

I'm not familiar with Sinatica. Perhaps the periodic MON$ queries (how frequent 
are they executed by Sinatica?) also produce some sort of overhead, cause each 
MON$ table query in context of a new physical transaction results in a stable 
view of current activity. Possibly not neglectable with > 400 connections.

The most easiest way to get insights on your record garbage is, e.g.:

* Run gstat -r
* Run a tool from IBSurgeon (can't recall the name, Alexey?)
* Run a tool from Upscene (FB TraceManager)

> 
> Maybe to avoid confusion, we don't have normal "Spikes" .. the system just
> starts to slow down and this state remains until the server-load is gone 
> (after
> midnight, when software is not used anymore).



--
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.1] Firebird engine seems to slow down on high load without utilizing hardware

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

> Thomas,
>>
>> I doubt, Firebird is IO-bound (limited by disk IO).
>>
> 
> Sorry, I don't understand your comment, can you please clarify what you 
> mean?

I think, disk IO isn't the limiting factor in that 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] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware

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

> Hi Patrick,
> 
> If you say that problem occurred recently, I would suggest you to check 
> SAN disks health.
> 
> However, these values
> 
>>Average system IOPS under load read: 100
> >Average system IOPS under load write: 550
> >Backup Restore IOPS read: 1700
> >Backup Restore IOPS write: 250
> 
> are really, really low.
> 1700 IOPS for the database with 4k page means 6.8Mb/sec (in case of 
> random reads).
> 
> I suggest to install a single SSD drive and check how it will work.
> SSD IOPS looks like
>   Random Read 4KB (QD=32) :   283.050 MB/s [ 69104.0 IOPS]
>   Random Write 4KB (QD=32) :   213.837 MB/s [ 52206.2 IOPS]
> 
> 
> From our optimization practice we found that if you need to optimize 
> only the single instance of the database, the most cost effective way is 
> to upgrade to SSD first, and only then fix other problems.

I doubt, Firebird is IO-bound (limited by disk IO).



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

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


>> hi,
>>
>> recently we had some strange performance issues with our Firebird DB 
>> server.
>>
>> On high load, our server started to slow down. Select and update SQL 
>> query times did go up by more than 500% on average,
>>
>> but reaching unreasonable high execution times at worst case. (several 
>> minutes instead of < 1sec)
>>
>>
>> OIT/OAT/Next Transaction statistics was within 1000 the hole time
>>
>> We were not able to messure any hardware limiting factor. Indeed, this 
>> system was running with only 8 cores at about 70% CPU usage on max. load.
>>
>> We decided that this may be our problem since we experienced a similar 
>> problem at about 80% CPU load in the past.
>>
>> So we upgraded the hardware. As expected, the CPU-load dropped to ~35% 
>> usage on max. load scenario.
>>
>> But this did not solve the problem.
>>
>> Same story for the harddisk system. The usage is not even near it's 
>> max capacity.
>>
>>
>> We also can't see any impact on the harddisk.
>>
>> We'r kind of stuck with our ideas, because we have no idea what could 
>> be a potential bottleneck to the system.
>>
>> Since the hardware doesn't show a limit, there have to be anything 
>> else - most likely firebird engine related that's limiting our system.
>>
>> We would be very grateful if anyone can give us hints where we can 
>> search further.
>>
>> Or someone has similar experiences to share with us.
>>
>>
>>
>> Operating System: Windows Server 2003
>>
>> Firebird: 2.1.5 Classic
>>
>> Dedicated database server (VMWare)
>>
>>
>> CPU: 16 cores, each 2.4 GHz
>>
>> RAM: 32 GB
>>
>> About 14GB are used from OS and firebird processes under max load.
>>
>> HDD: SAN Storage System
>>
>>
>> Average system IOPS under load read: 100
>>
>> Average system IOPS under load write: 550
>>
>> Backup Restore IOPS read: 1700
>>
>> Backup Restore IOPS write: 250
>>
>> SAN IPOS Limit (max): 3000
>>
>>
>> Firebird Config Settings, based on defaults
>>
>> DefaultDbCachePages = 1024
>>
>> LockMemSize = 134247728
>>
>> LockHashSlots = 20011
>>
>> Database
>>
>> size: about 45 GB
>>
>> 450 to 550 concurrent connections
>>
>> Daily average of 65 transactions / second (peak should be higher)
>>
>>
>> FB_LOCK_PRINT (without any params) while system was slowing down (~4 
>> days uptime).
>>
>> I have to note, Firebird was not able to print the complete output 
>> (stats was not cropped by me)
>>
>>
>> LOCK_HEADER BLOCK
>>
>> Version: 16, Active owner:  0, Length: 134247728, Used: 82169316
>>
>> Semmask: 0x0, Flags: 0x0001
>>
>> Enqs: 4211018659, Converts: 10050437, Rejects: 9115488, Blocks: 105409192
>>
>> Deadlock scans:   1049, Deadlocks:  0, Scan interval:  10
>>
>> Acquires: 4723416170, Acquire blocks: 640857597, Spin count:   0
>>
>> Mutex wait: 13.6%
>>
>> Hash slots: 15077, Hash lengths (min/avg/max):3/  12/  25
>>
>> Remove node:  0, Insert queue: 36, Insert prior: 74815332
>>
>> Owners (456):forward: 131316, backward: 14899392
>>
>> Free owners (9):forward: 39711576, backward: 49867232
>>
>> Free locks (42409):forward: 65924212, backward: 23319052
>>
>>
>> With best Regards,
>>
>>
>> Patrick Friessnegg
>>
>> Synesc GmbH
>>
>> 
> 
> 

Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware

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

Patrick,

> recently we had some strange performance issues with our Firebird DB server.
>
> On high load, our server started to slow down. Select and update SQL
> query times did go up by more than 500% on average,
>
> but reaching unreasonable high execution times at worst case. (several
> minutes instead of < 1sec)
>
>
> OIT/OAT/Next Transaction statistics was within 1000 the hole time
>
> We were not able to messure any hardware limiting factor. Indeed, this
> system was running with only 8 cores at about 70% CPU usage on max. load.
>
> We decided that this may be our problem since we experienced a similar
> problem at about 80% CPU load in the past.
>
> So we upgraded the hardware. As expected, the CPU-load dropped to ~35%
> usage on max. load scenario.
>
> But this did not solve the problem.
>
> Same story for the harddisk system. The usage is not even near it's max
> capacity.
>
>
> We also can't see any impact on the harddisk.
>
> We'r kind of stuck with our ideas, because we have no idea what could be
> a potential bottleneck to the system.
>
> Since the hardware doesn't show a limit, there have to be anything else
> - most likely firebird engine related that's limiting our system.
>
> We would be very grateful if anyone can give us hints where we can
> search further.
>
> Or someone has similar experiences to share with us.
>
>
>
> Operating System: Windows Server 2003
>
> Firebird: 2.1.5 Classic
>
> Dedicated database server (VMWare)
>
>
> CPU: 16 cores, each 2.4 GHz
>
> RAM: 32 GB
>
> About 14GB are used from OS and firebird processes under max load.
>
> HDD: SAN Storage System
>
>
> Average system IOPS under load read: 100
>
> Average system IOPS under load write: 550
>
> Backup Restore IOPS read: 1700
>
> Backup Restore IOPS write: 250
>
> SAN IPOS Limit (max): 3000
>
>
> Firebird Config Settings, based on defaults
>
> DefaultDbCachePages = 1024
>
> LockMemSize = 134247728
>
> LockHashSlots = 20011
>
> Database
>
> size: about 45 GB
>
> 450 to 550 concurrent connections
>
> Daily average of 65 transactions / second (peak should be higher)
>
>
> FB_LOCK_PRINT (without any params) while system was slowing down (~4
> days uptime).
>
> I have to note, Firebird was not able to print the complete output
> (stats was not cropped by me)
>
>
> LOCK_HEADER BLOCK
>
> Version: 16, Active owner:  0, Length: 134247728, Used: 82169316
>
> Semmask: 0x0, Flags: 0x0001
>
> Enqs: 4211018659, Converts: 10050437, Rejects: 9115488, Blocks: 105409192
>
> Deadlock scans:   1049, Deadlocks:  0, Scan interval:  10
>
> Acquires: 4723416170, Acquire blocks: 640857597, Spin count:   0
>
> Mutex wait: 13.6%
>
> Hash slots: 15077, Hash lengths (min/avg/max):3/  12/  25
>
> Remove node:  0, Insert queue: 36, Insert prior: 74815332
>
> Owners (456):forward: 131316, backward: 14899392
>
> Free owners (9):forward: 39711576, backward: 49867232
>
> Free locks (42409):forward: 65924212, backward: 23319052

fb_lock_print is reporting a hash slots value of 15077 but you show a 
setting of 20011. Mutex wait looks high to me.

Some ideas:
* Increase the hash slots value to 30011
* Get a picture on how many garbage (record versions) you create. AFAIR 
it is the -r switch of gstat which gives you that information. Sudden 
spikes in the statement response time could be related to co-operative 
garbage collection in Classic/SuperClassic, where basically the 
statement synchronously removes garbage of out-dated record versions
* Consider upgrading to 2.5. 2.1.7 is end-of-life and 2.5 improved in 
the area of lock contention in Classic/SuperClassic substantially.


-- 
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 can I prevent that my database can be opened with another security.fdb.

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

> Dear all,
> If blocking SYSDBA from connecting the database is the goal, than ones can
> create a role with the name SYSDBA.Is there any minus about this?

AFAIR, this needs to be done with a direct system table insertion in RDB$ROLES 
with 2.5 and earlier. I don't know if this has changed in Firebird 3, but for 
sure, Firebird 3 will reject direct system table modifications at all.


--
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 can I prevent that my database can be opened with another security.fdb.

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

Hi,

> Dear all,
> Though I don't have the same question as Roland, information below by Virna
> Constantin (costel...@yahoo.com) is very useful.Thanks every one.
> Regards,Tjioe
> 
> Suggestion
> event studies "on connect"
> 1. an application with minimum rights connects to the database
> 1 '. in table "mon$attachmnts" field "MON$REMOTE_PROCESS" will record the
> application name
> 2.  in the trigger event "on connect" if the user connects SysAdmin, verifies
> the application name in "MON$REMOTE_PROCESS"

Just FYI: In practice it is possible to suppress database triggers (DPB: 
isc_dpb_no_db_triggers) when connecting as the database owner or as SYSDBA. 
E.g. isql has a special command-line option "-nodbtriggers" to bypass database 
triggers at connect time to e.g. modify/drop the trigger afterwards.

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

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

Re: !!***SPAM***!! Re: [firebird-support] Firebird V3RC2 Primary Key question

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

> Thanks for instructions. It does work.
>
>
>
> Is there a way to use created case insensitive collation as database wide 
> default? It would be easier for me not to specify collation for each and 
> every index definition.

http://www.firebirdsql.org/refdocs/langrefupd25-ddl-charset.html

This does not affect existing character fields though.


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

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


>
>
>
> Thanks.
>
>
>
> From: firebird-support@yahoogroups.com 
> [mailto:firebird-support@yahoogroups.com]
> Sent: Friday, April 8, 2016 10:36 AM
> To: firebird-support@yahoogroups.com
> Subject: !!***SPAM***!! Re: [firebird-support] Firebird V3RC2 Primary Key 
> question
>
>
>
>
>
> On 7-4-2016 23:01, Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr
> [firebird-support] wrote:
>> Would you be so kind to provide an example SQL which shows how to do
>> primary key with a case insensitive collation?
>
> 1. Register the WIN1254_UNICODE collation (it is defined in fbintl.conf,
> but not included by default in Firebird):
>
> create collation WIN1254_UNICODE for win1254
>
> 2. Derive a case insensitive collation from WIN1254_UNICODE:
>
> create collation win1254_ci for win1254 from win1254_unicode case
> insensitive
>
> 3. Create table
>
> create table win1254test (
> stringkey varchar(30) character set win1254 primary key collate
> win1254_ci
> )
>
> 4. Test it:
>
> INSERT INTO win1254test(stringkey) VALUES('EK'); -- succeeds
> INSERT INTO win1254test(stringkey) VALUES('ek'); -- fails
> INSERT INTO win1254test(stringkey) VALUES('Ek'); -- fails
> INSERT INTO win1254test(stringkey) VALUES('eK'); -- fails
>
> I hope this helps,
> Mark
>

Re: [firebird-support] Find rarely used or unused indices

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

Hi,

> What is the best way to go about to find out about this?
>
> I have several projects that are using the FB database and I would like
> to cleanup and remove all the indices that are just taking up space and
> thus indirectly reduces performance?

It is not that easy to clearly define the usefulness of an index. 
Usually indexes with a low selectivity (high number of duplicates) is a 
questionable candidate for a good index, but it depends from a query 
perspective.

Imagine you have an index with 1 million nodes pointing to records but 
with only 2 unique values (e.g. BOOLEAN), one row only with a value of 0 
and the rest with a value of 1. If you query the one row with value 0, 
the index will be definitely useful, but for querying 99 records 
with value 1, it is better to scan the entire table instead of the 
additional "hop" via the index to locate the row. Histograms 
(distribution among unique values) will help the optimizer there to 
choose a better execution plan. AFAIK histograms are planned for Firebird 4.

To get an overview across all your indices in your database from various 
perspectives (size, index depth, selectivity, duplicates etc.), you can 
run the gstat command-line tool and process and interpret the raw output.

But as Alexey has pointed out, there is tool support out there. HQbird 
is one. Upscene's FB TraceManager 
(http://www.upscene.com/fb_tracemanager/) is another product which can 
help you in that area. To get a first impression how this may look lie, 
have a look on that screen:
http://www.upscene.com/fb_tracemanager/images/products/fbtm/fbtm_ss_databasestatistics_index_monitoring.png

In combination with the live tracing functionality including execution 
plans of executed statements etc., you may find suspicious things.



-- 
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] Disable cache at all - future question

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

> Hi,
> is this possible to disable cache at all?
> I mean that all go from and to “HDD” without RAM between.

Checkout the FileSystemCacheThreshold configuration parameter in 
firebird.conf. Set it to 0 and also use a small page buffers value.

Another area of RAM usage is for temporary data used by grouping, 
sorting etc. If you also want to (almost) disable that area, watch out 
the TempCacheLimit 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.

Re: [firebird-support] Temporarily disable constraints

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

Hi,

> Is it possible to temporarily disable a FK constraint?  I have found a
> couple of requests for this feature dated 2009 but these still show as
> open.  Did this functionality ever get implemented ?

No. As Alan said, currently the only way is to dropping and re-creating.


-- 
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] Ascending and Descending Index

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

> Hi.
> 
> Is it possible to create an index like
> 
> CREATE INDEX MYINDEX_IDX1
> ON MYTABLE (COLUMN1 ASCENDING, COLUMN2 DESCENDING )

No. You can't define ordering per index segment.


--
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] How write a query with a progressive sum field

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

> Il 07/03/2016 11.32, Dimitry Sibiryakov s...@ibphoenix.com
> [firebird-support] ha scritto:
>> sum(saldo) over (order by data)
>
> Thank You for your response but I can't do: sum(SALDO) because SALDO is
> not a field of my table.
>
> I badly explained myself: I have a table with only this fields:
>
> DATA DOCUMENTO_ID   NUMERO   SERIE  CARICO   SCARICO
>
>
> I need add SALDO fields in the result of the query to get a progressive
> sum of CARICO - SCARICO.

Although not that flexible as an ad-hoc query, but perhaps your 
particular use case can be achieved with a selectable stored procedure ...

I'm not good in writing PSQL out of my mind at the moment, but execute 
your SQL (without the SUM aggregate field) in a stored procedure, 
iterate over the result set, remember the computation for the previous 
row in a local variable and use that for the addition with the current 
row and call SUSPEND for each result set iteration.



-- 
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] How write a query with a progressive sum field

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

> 07.03.2016 12:55, 'Thomas Steinmaurer' t...@iblogmanager.com 
> [firebird-support] wrote:
>> Needless to say, 3.0 is in release candidate state. Not sure if Luigi wants 
>> to run 3.0 in
>> production yet. ;-)
>
> Nothing prevents him from installing of an additional server for report 
> purposes and
> setting up replication to it from production one.


Yeah. Sure. ;-)



-- 
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] How write a query with a progressive sum field

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

> 07.03.2016 12:29, Luigi Siciliano luigi...@tiscalinet.it [firebird-support]
> wrote:
>> Ok, I understand but, "over" is unknow in firebird 2.5.5.
> 
>   Upgrade to 3.0.

Needless to say, 3.0 is in release candidate state. Not sure if Luigi wants to 
run 3.0 in production yet. ;-)



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

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


> -- 
>   WBR, SD.
> 
> 
> 
> 
> 
> 
> ++
> 
> 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] How to optimize queries on field state

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

> I have a state machine (workflow) implemented on a table a couple of
> million records and growing.
> I have crated an index on the "state" field. The possible states are
> very few (5 to 10). Obviously selectivity index is terrible. As I can
> optimize the query?

Hard to say without seeing an actual query ...

Is the state field the only one used to filter 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.

Re: [firebird-support] CPU Affinity

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

Hi!

> Dear Friends.
> 
> FirebirdSQL: 2.5.5  64bits
> Operating System: Windows 2008 R2 Enterprise 64bits
> Server: Dell Poweredge R620
>  32GB
>  2 processors E5-2609 2.40 GHz
>  8 Cores
> 
> To define CPU Affinity in firebird.conf I set CPUAffinity to 255, in a 
> way to use all the 8 cores.

Which Firebird server architecture are you using? CPU affinity is only for 
SuperServer and the question is why do you bother with SuperServer on a 
multi-core machine when there is Classic or SuperClassic available?



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

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


> Does it make sense or could be better to use less cores, like core 
> 5,6,7,8 or 3,4,5,6?
> 
> -- 
> 
> 
> Atenciosamente,
> 
> Hugo Eyng
> 
> 
> 
> 
> 
> 
> 
> ++
> 
> 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] When to use Firebird 3 Classic

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

> On 17-1-2016 00:07, Köditz, Martin martin.koed...@it-syn.de
> [firebird-support] wrote:
>> is there still a reason to use the Classic mode with Firebird 3? Since
>> there is real SMP support with SuperServer I don’t think there can be
>> advantages with ClassicServer.
>
> The main advantage of course is that if one connection does something
> (hit a bug in the engine or in a UDF) that crashes the process, that it
> doesn't bring down all connections, but just that one connection.

The number one reason why Classic hasn't been vanished yet, cause big 
customers with critical environments are still opting for Classic.


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

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

> Hallo,
>
> Il 08/01/2016 16.22, Tim Ward t...@telensa.com [firebird-support] ha scritto:
>> What are your queries, query plans, query statistics?
>
> The queries are similar to: SELECT * FROM TABLE.
>
> Investigating for query plans, statistics, Buffers as suggested by
> Thomas, I discovered the origin of the problem:
> FB is innocent :) the guilty is me because I use ZEOSLib to connect to
> FB and I used the property "SortedFields" instead of using the clause
> "ORDER BY" in SQL query thinking that is more flexible on a Delphi RAD
> development like Delphi :(

A great use case for the Firebird Trace API to investigate what's up 
behind the scene of ZEOS. ;-)



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

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

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

> Yes, Thomas,
>
> it woks with 2.1. In the release notes of 2.5.X is written:
>
> Adding a value to a timestamp earlier than '16.11.1858 00:00:01' would throw
> the error "value exceeds the range for valid timestamp".
>
> But where can I search 200 Tables...

Read Paul's blog on using -v for the restore etc ...


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

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


> -Ursprüngliche Nachricht-
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Freitag, 8. Januar 2016 17:07
> An: firebird-support@yahoogroups.com
> Betreff: Re: AW: AW: [firebird-support] Architecture
>
>> 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.
>>>
>>>
>>> 
>>>
>>> 
>>>
>>> 

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: [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/

[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] 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/

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