RE: [firebird-support] Re: How to "shape" indexes to avoid index depth > 3

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > the multi-segment indexes are to optimize commonly used queries.
> 
>In this case you can consider removing one or two fields from the index and
> see how much selectivity it lose and how much performance would be
> affected.


So, you are saying that having less fields reduces the index depth?  (I always 
thought the reverse)

Measuring performance impact will be difficult to do, I don't have details of 
all cases where one index may be used over another.

Reducing number of columns would mean more row reads, no?

FYI, our deployments use FB 2.5 Classic (we have a some installs with very 
large number of DB connections) so large FB page cache is not possible.


Sean

RE: [firebird-support] Re: How to "shape" indexes to avoid index depth > 3

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > All the FB articles/docs/postings that I have seen have said for
> > performance, index depth should be no more than 3.
> 
> This is not a hard requirement, 4 levels may be also OK. Are those multi-
> segment indices primary/unique keys or just for lookup purposes?

{Sorry for the delay in responding, was away on vacation}

Most of our PK are generator values, the multi-segment indexes are to optimize 
commonly used queries.


Sean

[firebird-support] How to "shape" indexes to avoid index depth > 3

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Hey all,

In my database I have some indexes with depth = 4 (db page size is already 
16KB).

The indexes are multi-segment, with the fields sequenced from least to most 
selective, and gstat reports a very low selectivity for the index (less than 
0.0001).

The indexed tables have between 100 million and 1500 million (yes, 1.5 Billion) 
rows.

All the FB articles/docs/postings that I have seen have said for performance, 
index depth should be no more than 3.

Any suggestions on how I can modify the indexes to get the indexes to that 
point?


Sean

RE: [firebird-support] Unable to finish backup with GBK (Firebird 1.5)

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Please read the “migrating” sections of v2.0, v2.1, v2.5 and v3.0 release notes.

Going from v1.5 to v3.0 will likely involve several steps --you won’t 
necessarily have to do each of those steps, but your reading will tell what 
steps will be involved.


Sean

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Wednesday, November 15, 2017 8:13 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Unable to finish backup with GBK (Firebird 1.5)




I have an old database running on Windows 7 PC with Firebird 1.5, working just 
fine for years (from 2005.).
Currently, FDB file is little larger than 700MB.

Now, I decided to update to FB3. Nothing simpler, I thought. Just gbak backup 
with FB1.5 and restore on FB3.

But, I have an issue which I cannot understand. My gbak doesn't report any 
error or failure (log file empty), but when run with -verbose, I just get:
gbak: 16 records written
gbak: 18 records written
gbak: 20 records written
gbak: 22 records written
gbak: 24 records written
gbak: 26 records written
gbak: 28 records wri

and it breaks - brings me back to terminal.
FBK file created is cca 200 MB, but cannot be restored - gbak restore (also on 
FB1.5, just checking the backup) asks for another volume etc. So I believe 
backup is broken.

What would be the path to successfull backup or at least finding the reason why 
it doesn't work as expected?
The app using the database works fine, there are no errors related to DB...

RE: [firebird-support] Firing event when deleting in MON$ATTACHMENTS

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> 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

What event is your application registered/listening for?

'DISCONNECT_USER]', or 'DISCONNECT_USER]'||:USR?


Sean

RE: [firebird-support] Firing event when deleting in MON$ATTACHMENTS

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

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

AFAIK, it is not possible to do what you want.

Once you have deleted the connection, all communication to the client is 
stopped.


Sean

RE: [firebird-support] "Invalid Cursor State"

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

It´s an executable procedure which returns a parameter. It´s not a selectable 
procedure.
Its the kind of SP you invoke like "execute procedure ... returning values ..." 
while selectable ones are usually called as "select ... from 
stored_procedure_name"
I´ve managed to fix my issue, anyway.

 What was the solution?  (Others might want to know)

RE: [firebird-support] "Invalid Cursor State"

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Dimitry:
I do it all the time ... (read output parameters from executable procedures, 
using "call executable_sp" by ODBC). No issues with other SPs.

 Unless I missed something, your SP is missing the required SUSPEND to make 
the SP selectable.


Regards



2017-10-17 15:38 GMT-03:00 Dimitry Sibiryakov 
s...@ibphoenix.com [firebird-support] 
>:
17.10.2017 20:12, Jorge Andres Brugger 
jorge.brug...@gmail.com [firebird-support] 
wrote:
> What am I doing wrong?

   You cannot fetch from non-select query.


--
   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] Hiding password step within innosetup

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Thanks Sean for your suggestion but which way do I create my own project ?

Get a copy of the project install script and make your own changes, or use the 
project script as a hint as to how you can include the steps into your own 
install kit.

By the pre-created security database  is that mean changing SYSDBA password as 
I done above ?

 No, it means installing Firebird on your machine, setting the SYSDBA 
password.

 Then copy the security2.fdb file into a location that will accessed to 
build your install kit, and include in your install script a command to place 
the file in the appropriate folder.

RE: [firebird-support] Hiding password step within innosetup

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> I need to know please if I can hide the password step when installing my app
> with Firebird 3 within innosetup.
> With FB 2 I can use the following parameter :
>  /SYSDBAPASSWORD,
>  /SP- /VERYSILENT,
>  /NOGDS32,
>  /NOCANCEL,
>  LOG
> 
> We can only assign password in the SYSDBA step and don't know if exists the
> possibilty of hiding the step.

Suggestion:

Don't use the project install kit, create your own, which ships a pre-created 
security database.

That way you would never expose the password


Sean

RE: [firebird-support] Converting to firebird 3

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> We have an application with roughly 20 000 lines of code and have been using
> firebird 2.5 since the start. We do experience performance problems with
> 30+ client connections and are thinking of moving to firebird 3.

You haven't provided any real details.

Are you using SuperServer or Classic engine?

What are you DB Page cache settings?

What is your DB page size?

Have you used gstat to check that you don't have long running transactions?

Have you used gstat to check the "depth" value of your indexes?  (should be 
less than 4)


Sean

RE: [firebird-support] Cannot join CTEs (using indices) containing FIRST/ROWS/OFFSET

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> As soon as i add the ROWS clause to a CTE FB cannot use indices available in
> the CTEs tables.
> Is this a bug, or as designed?
> 
> This:
> 
> with low as (
>   select igc.issue_id, ia.occasion
>   from issues_groups igc
>     left join issues_groups igb on (igb.issue_significant_id =
> igc.issue_significant_id)
>       left join issues_addressed ia on (ia.issue_id = igb.issue_id)
>   where ia."USER" = 'a'
>   --order by ia.occasion desc
>   --fetch first 1 row only
> )
> select *
> from issues i
>   left join low l on (l.issue_id = i.issue_id)
> 
> issues_groups, issues_addressed: Indexed reads
> 
> remove the comments and issues_groups will have non-indexed reads for
> the join l.issue_id to i.issue_id.

I believe you are incorrectly using CTE instead of simple calculated column, as 
in:


select 
  i.*,
  (
select 
   FIRST 1 ia.occasion
from issues_groups igc
   left join issues_groups igb on (igb.issue_significant_id = 
igc.issue_significant_id)
   left join issues_addressed ia on (ia.issue_id = igb.issue_id)
where
  igc.issue_id = i.issue_id
  and ia."USER" = 'a'
order by ia.occasion desc
) as occasion
from issues i


Sean

RE: [firebird-support] "Name longer than database column size." on already compiled SP

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> But, how that variable ended there? The SP is actually working, and I can get
> the DDL with 33 char lenght variable name. 

>Could be a previous FB bug?

Almost certainly.

The bad variable name could have existed for years, only a change to the DDL/SP 
text would have caused the text to be re-validated.

(database restores do not re-validate the SP or Trigger text, or calculate new 
BLR, they are restored untouched/as is)


Sean

RE: [firebird-support] "Name longer than database column size." on already compiled SP

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> I already have this SP in a DB (compiled, and using it a lot):

No object (including SP variable) name can be longer than 31 characters


Sean

RE: [firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Thank you for all the suggestions, I’ve changed the config as suggested,
> unfortunately the problem still happens.
> There seems to be little for me to investigate.
> The latest crashes all occurred when there was very little load on the server
> and on firebird.
> 
> All access is done by php which I’m inclined to think might be the culprit, 
> but
> there doesn’t seem to be any recognizable pattern on the errors.
> The windows event logs aren’t showing anything but the crash themselves.
> 
> I’m open to any suggestion on how I might track this problem.

Does your database have/use custom developed UDF's?

Have you tested them?  Could they be causing the failures?

Have you tried using the FBTrace functionality, to trace all database activity?


Sean

RE: [firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

>> From: firebird-support@yahoogroups.com [mailto:firebird-
>> supp...@yahoogroups.com]
>> Sent: sexta-feira, 18 de agosto de 2017 11:56
>> To: firebird-support@yahoogroups.com
>> Subject: RE: [firebird-support] Firebird 3 crashes ( terminated abnormally
>> (4294967295) )
>> 
>> > >What is the size of the database pages?
>> > >Have you checked the DB level Page Cache setting (the DB can have a
>> > different value from the config)?
>> > >How much RAM is available for the VM?
>> >
>> > Sean, theres 13gb of RAM, and no db with specific settings Page size
>> > is 16k
>> 
>> Ok, so a single database you are not "over subscribing" RAM.
>> 
>> 
>> > >*EACH DATABASE* will be consuming up to (5 * page size) on cache.
>> > >Why do you set DefaultDbCachePages so high The Firebird server
>> > >needs RAM for more things than just the cache.
>> >
>> > Helen, pages are high only because I copied the suggested values from
>> > the config at https://ib-aid.com/br/optimized-firebird-configuration/
>> > Since it is the first time we're using superserver we went for these
>> > recommended settings.
>> > Isn't the page cache shared in this mode on firebird 3 (5 pages for all
>> dbs) ?
>> 
>> No, *DB* Page Cache is per database.
>> 
>> How many databases are you hosting on the system?

> About 20 databases

First, please don't top post.

At 20 database, with each database having 50,000 pages allocated (needing 800 
MB each) you would need at least 16GB just for FB DB caches.

Change your default/config cache setting to 25,000 pages, if you need more for 
specific database(s) set the cache on a per database basis.


Sean

RE: [firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> >What is the size of the database pages?
> >Have you checked the DB level Page Cache setting (the DB can have a
> different value from the config)?
> >How much RAM is available for the VM?
> 
> Sean, theres 13gb of RAM, and no db with specific settings
> Page size is 16k

Ok, so a single database you are not "over subscribing" RAM.


> >*EACH DATABASE* will be consuming up to (5 * page size) on cache.
> >Why do you set DefaultDbCachePages so high The Firebird server
> >needs RAM for more things than just the cache.
> 
> Helen, pages are high only because I copied the suggested values from the
> config at https://ib-aid.com/br/optimized-firebird-configuration/
> Since it is the first time we're using superserver we went for these
> recommended settings.
> Isn't the page cache shared in this mode on firebird 3 (5 pages for all 
> dbs) ?

No, *DB* Page Cache is per database.

How many databases are you hosting on the system?

RE: [firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> I've been having quite a few crashes on a new server with not much
> databases or requests. terminated abnormally (4294967295)
> 
> It seems to be very random, there are not many different operations going
> on.
> 
> It's a google cloud Windows vm, I've checked for hardware / driver problems
> but it seems to be ok.
> 
> What actions can I take to try to mitigate this ? I've looked up all I could 
> on
> google but that was little help.
> Is it worthwhile to try to change cachepages or such?
> 
> 
> 
> Firebird 3 Super Conf :
> 
> ServerMode = Super
> DefaultDbCachePages = 5
> TempBlockSize = 2M
> TempCacheLimit = 364M

What is the size of the database pages?

Have you checked the DB level Page Cache setting (the DB can have a different 
value from the config)?

How much RAM is available for the VM?


Sean

RE: [firebird-support] Automation tools?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Kjell,

> We're in need of a tool where we can author sequences of SQL queries
> against FB and non-DB commands like file operations etc. I'd like to call them
> "batch runs" to perform recurring tasks.
> 
> Do you know of such tools or what that kind of tool would be called in
> general?
> 
> We need to be able to schedule some runs. Others need to be triggered by
> e.g. the appearance of a file in a local or FTP/SFTP folder. Yet others need 
> to
> be executed ad hoc, i.e. started manually.
> 
> We also need to be able to enter conditions/checks between each step, to
> check results, e.g. reasonable number of affected records, and bail out or at
> least send a warning e-mail message to me if something goes wrong or looks
> suspicious.

You could look at the IBExpert IBEScript tool, it has a lot of functionality 
that you could leverage, alone or together with some PowerShell scripts (forget 
batch files -- PS is much more powerful, though initially confusing).


Sean

RE: [firebird-support] nbackup : Error assessing database due a missing delta file

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

>There is a difference between "do not use it" and "do not use it with this
> version".

Unfortunately, in your later post you suggested that nbackup is never safe to 
use.

RE: [firebird-support] nbackup : Error assessing database due a missing delta file

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Hello Sean, I was replying to dimitry s. Have you read his response?
> 
> As I understood he was saying to not use nbackup, and if one person
> involved in any way to firebird says not to use it, then what make him think
> to others?

Let's just say that I find Dimitry S POV on many subject and his tone to be 
harsh, and they are not always appropriate.

Further, while he is associated with IBPhoenix, he does not speak for the 
Firebird project.

The opinion/voice that you should definitely be listening to is Dmitry Yemanov, 
he can speak for the project with authority.


Sean

RE: [firebird-support] nbackup : Error assessing database due a missing delta file

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Jesus Garcia,

> I only see three items pending, but anyway, why not remove Nbackup from
> firebird until it is safe to use?

As others have pointed out, the latest FB versions have an updated nbackup


> Why is there a tool that cannot be used in a safe way?

Q1: How do you *know* if a tool is safe in all possible situation/conditions?

A1: You test under all possible conditions?


Q2: Can all possible conditions be predicted/known in advance?

A2: Absolutely not!  That is why all software has bugs!


Sean

RE: [firebird-support] unpacking backup with firebird version 1.5 (windows 7)

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Robert,

> the restore operation is running now for some minutes.
> However I get very many (some thousand ??)  messages of the form:
> 
> gbak: do not recognize table attribute 0 -- continuing
> 
> does this point to a serious problem or can I safely ignore it?

You have a serious problem.



Sean

RE: [firebird-support] Compatibility questions: Current release versus 1.5.3.4870

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

My question (and I'm sorry if I'm asking this before doing any research on my 
own) is simply, how compatible is the 2.5 version with databases created with 
1.5.3?

 There is no easy answer to your question – it depends…

 Have you considered rolling back the Win10 update?

 The update should have create a pre-update System Restore point…

RE: [firebird-support] List of read only stored procedures.

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Will,

>    Is it possible to select a list of read only stored procedures from the
> database.
> 
>   I'd like to give my reporting engine access to stored procedures where they
> fit the following rules
> 
> 1.) Must be selectable
> 2.) Must have no input parameters.
> 3.) Must have one or more output parameters.
> 4.) Must not perform any database writes either directly or indirectly (via
> another procedure call.)
> 
> I have 1-3 sorted with the following SQL but I can't see if 4 is possible. Can
> anyone point me in the right direction please?

While I don't know if #4 is possible, I would suggest that even if it was ... 
depending on your parsing of the system tables to make such a determination is 
a very "brittle" approach.

If a future Firebird version changes the structure/definition of the system 
tables, you would be "locked in".

Instead, I would recommend that you adopt a naming convention (a specific 
prefix) for the SPs which would want to expose to your reporting engine.  A 
prefix like "Report_", "Report$", "RP_" or "RP$" would be my suggestion.

That approach has the advantage that you can directly control which SPs are 
exposed -- there could be other SPs which meet the criteria that you wouldn't 
want exposed.


Sean

RE: [firebird-support] RE: Firebird SuperClassic hangs

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Thank you for your suggestion. I know that our configuration is not optimal and 
I will change this. But my understanding was, that this is only a performance 
problem and not something that can cause Firebird to hang. Or is this a known 
behavior?

 The problem is in the definition of “hang”.

 Does Firebird hang, ie. plain stop?  No

 Can the deadlock scans and lock contention (see Mutex Wait) make Firebird 
appear to be hung/running really slow?  Yes


 FYI, we are considering changing from Classic to SuperClassic to eliminate 
the overhead (10-15%) that the external lock manager adds.  Like you, our 
databases can be very large (300GB) and have a large number of connection 
(sometimes 800).  This may be something that you also evaluate.

[firebird-support] RE: Firebird SuperClassic hangs

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Freddie,


LOCK_HEADER BLOCK
Version: 145, Active owner:  0, Length: 25165824, Used: 
24798216
Flags: 0x0001
Enqs: 17157097538, Converts: 22693011, Rejects: 12135777, 
Blocks: 93039760
Deadlock scans:289, Deadlocks:  1, Scan interval:  10
Acquires: 18346589405, Acquire blocks: 1203176111, Spin count:  
 0
Mutex wait: 6.6%
Hash slots: 1009, Hash lengths (min/avg/max):   47/  67/  93
Remove node:  0, Insert queue:  0, Insert prior:  0
Owners (151):   forward:  30704, backward: 21097392
Free owners (4): forward: 750216, backward: 24346456
Free locks (10120): forward:  28728, backward: 693760
Free requests (7877):forward: 8570432, backward: 1678000
Lock Ordering: Enabled

Increase the number of hash slots (at least x10) and increase the lock manager 
memory (x4 to x6) in your fbconfig. File


Sean

RE: [firebird-support]

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> My problem is the increase in RAM when I start using my application under
> Firebird 3 which is much more greedy, I switch from a usual 50mo
> consumption to 200mo and more.

Actually, your stats shows v2.5 using 174MB of RAM, whereas v3 uses 215MB

> fb 2.5 :
> • Memory
> Current: 174 393 144 Max : 175 580 408 Buffers: 2 048


> fb 3.0.3
> • Memory
> Current: 215 635 168 Max : 218 143 880 Buffers: 2 048

So, v3 is using more RAM, 41MB increase (24% more)

Which engine are you using?

What is your db page size?


I guess I don't think of that as a significant difference.  Our Delphi server 
application running using 16GB of RAM at one location, so 41MB doesn't even 
raise an eyebrow.


Sean

RE: {Disarmed} [firebird-support] Hint for maximaze FB performance, CPU Utilization - again.

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Each connection does get an single OS-thread only which can use no more
> than an single core at the same time. On an 6-core CPU this means an
> maximum of 16% utilization for total CPU.

That is true for pre-v3.x SuperServer builds, not true for v3+

Not, was never, true for Classic or SuperClassic builds...


> Firebird does not spread an single query onto several cores as e.g.
> Oracle can do.

True for all builds.  

Firebird does not, yet (it is a feature request), support query parallelization.


Sean

RE: [firebird-support] Deletion of rows from multiple tables takes a lot of time around 30mins

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Today one of our customer complained that removing clients from
> application is taking a lot of time around 30-40mins.  So I looked into their
> database whose DB size is around 340MB and found that our DBA has a called
> deletion triggers in main table. Triggers will delete all the client 
> information
> from related tables that 6 tables. Also, I checked the child tables from any
> additional triggers  but there were none.
> 
> Now, when I copied this database to my development environment deletion
> works fast takes around 5 mins. I also rechecked with their older databases
> also whose size is around 1GB. Found no slowness during deletion.
> 
> The question arises why this deletion process is taking so much of time at
> customer environment ?

Let's start with:

- Is the server also a Windows domain controller?
- Have you compared performance of customer/your storage using Crystal Disk 
Mark?
- What FB engine (Classic, SuperServer) are you running?
- What FB version are you running?
- If SuperServer what is the db page cache size?
- What is the db page size?


> Another thing I noticed was that FBServer was consuming 25% CPU usage
> during deletion.

That would not be surprising.


Sean

RE: [firebird-support] Improve remote query speed on Windows Server 2012 VPS

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

hi
Min=37ms Max=37ms Avg=37ms

 Does mean that but only selecting specific columns, your SQL executed 
faster?

 Also, are using v2.5+ FBClient library on remote client?

thanks


From: "'Leyne, Sean' 
s...@broadviewsoftware.com 
[firebird-support]" 
>
To: "firebird-support@yahoogroups.com" 
>
Sent: Friday, April 14, 2017 6:20 PM
Subject: RE: [firebird-support] Improve remote query speed on Windows Server 
2012 VPS




> select * from MYTABLE where id=200
>
> takes
>Total execution time: 5.388s
>PLAN (MYTABLE INDEX (PK_MYTABLE))
>
>   the same query executed local on the server(same db,server) takes  0.065s
>
> queries are done with Flamerobin,from IBExpert the same results
>
> How can I improve remote speed?

SELECT the columns that you actually need.

SELECT * is fine for debugging, but IMO a slackers approach for production code.


Sean

P.S. What is the latency/PING time of your connection to the remote server?

RE: [firebird-support] Improve remote query speed on Windows Server 2012 VPS

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> select * from MYTABLE where id=200
> 
> takes
>    Total execution time: 5.388s
>    PLAN (MYTABLE INDEX (PK_MYTABLE))
> 
>   the same query executed local on the server(same db,server) takes  0.065s
> 
> queries are done with Flamerobin,from IBExpert the same results
> 
> How can I improve remote speed?

SELECT the columns that you actually need.

SELECT * is fine for debugging, but IMO a slackers approach for production code.


Sean

P.S.What is the latency/PING time of your connection to the remote server?

RE: [firebird-support] Best firebird3 server mode for high usage and concurrency

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Hello
> Is SuperServer the best firebird3 if you have many large gdbs (about 100 of
> gdbs averaging 2gb each) and very high concurrency?

Define "very high concurrency".

For the entire server?  For specific databases?


Sean

RE: [firebird-support] FBTrace: What are "dyn requests"

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

SET,

> I don't know what dyn requests are, Sean, but reading a bit I do find dyn
> mentioned in the Firebird book (page 1028) as well as on page 72 of a
> presentation Pavel did at the Firebird conference 2014. First, the Firebird
> book:
> 
> A byte-encoded language for describing data definition statements.
> Firebird's DSQL subsystem parses DDL statements and passes them to a
> component that outputs DYN for interpretation by the Y valve, another
> subsystem that is responsible for updating the system tables.
> 
> Then Pavel:
> BLR & DYN
> You will never need those unless you're Firebird core developer or GPRE
> user.
> Note: BLR & DYN records could be quite long, so set max_blr_length &
> max_dyn_length trace options accordingly.
> 
> Hence, I figured that DYN is something that I - as a normal user - hopefully
> never need to know much about. Does this answer your question, or are you
> interested in a more technical answer from one of the developers?

So, DYN == DDL

OK

What is it with developers, why can't they make things obvious without need to 
dig very deep!

It would have taken no effort to have replace "dyn" with "ddl" and had a 
self-describing config file!

Thanks as always!


Sean

[firebird-support] FBTrace: What are "dyn requests"

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

FBTrace config supports the logging of "dyn requests",

# Put dyn requests execute records
log_dyn_requests false

# Print dyn requests or not
print_dyn false

There is, however, no explanation of what these types of requests are -I don't 
recall "dyn requests" getting much mention in my over 20 years of using FB/IB.

What are "dyn" requests?


Sean

RE: [firebird-support] Firebird Backups on Google Compute Engine

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> We are considering moving our systems to GCE and trying to figure the best
> approach to backup our many firebird databases.
> Firebird is not VSS aware so we can't use shadow copying.

Not completely true.

1- Firebird uses careful write order, so a VSS snapshot should be a valid 
database image -- though with pages containing uncommitted data.  So, which FB 
is not VSS aware, it co-exists with VSS quite nicely.

2- You can use nbackup to create a logical DB snapshot, which would be 
equivalent of a VSS snapshot.


> On our current
> host, we create backups of every single database with gbak daily - which
> consumes a lot of time and disk. We would like to improve that.
> Would the following procedure be enough to guarantee a safe differential
> backup? We are on windows :
> 1. connect to all databases and set them to backup mode (or offline mode)
> 2. stop the firebird service
> 3. unmount the drive containing the dbs
> 4. create a snapshot with gcloud
> 5. remount, restart service, put dbs online

Please clarify.

1- Are you moving your databases to run on GCE? 
2- Looking to store backups on gcloud?
3- Both?

If 1 or 3 you need to perform some disk benchmarking.

Unlike some databases, Firebird performance is significantly influenced by IO 
_latency_.

All clouds talk about provisioning IOPS, but few talk about what the latency is 
of each IOP.  You can get 10,000 IOPS if you have 10,000 processes each 
generated 1 IOPS, Firebird is (largely) a single process.  I know that there 
are differences between cloud provider (don't have first hand experience with 
GCE -- hoping soon)


Sean

RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > OWNER BLOCK 732776
> > Owner id: 114332029419524, type: 1, pending:  0
> > Process id:  26620 (Alive), thread id:   2348
> > Flags: 0x08   wake
> > Requests (431): forward: 732888, backward: 10111616
> > Blocks: *empty*
> > 732776 waits on nothing.
> >
> > Most of the entries show:
> >Flags: 0x08   wake
> >
> > Oddly, all of the entries show:
> >Blocks: *empty*
> 
> Nobody is waiting at the moment. This is why I suggested getting it
> periodically, hoping that some of the runs could step on somebody's wait.

Actually, I have a job running every minute.

What would be the signature of something that is/was waiting...  Perhaps as a 
REGEX that I can use ;-)  -- they are not my strong suit.


Sean

RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Dmitry,

> > Am I right to think that I need to create a process to run the command on a
> regular basis (every 5 secs?) to find what objects locks are waiting for?
> 
> Yes. But we're still guessing in the dark, the reason of the slowdown could be
> completely unrelated to the lock manager (e.g. bad plans, undesired access
> to MON$ tables inside triggers, whatever else).

Here are some more recent results:

LOCK_HEADER BLOCK
Version: 145, Active owner:  0, Length: 67108864, Used: 22695696
Flags: 0x0001
Enqs: 232862988, Converts: 2119345, Rejects: 251137, Blocks: 2802169
Deadlock scans: 20, Deadlocks:  0, Scan interval:  10
Acquires: 393659923, Acquire blocks: 185848452, Spin count:   0
Mutex wait: 47.2%
Hash slots: 90001, Hash lengths (min/avg/max):0/   0/   7
Remove node:  0, Insert queue:  0, Insert prior:  0
Owners (498):   forward: 732776, backward: 22433480
Free owners (8):forward: 22510432, backward: 12606880
Free locks (3604):  forward: 734616, backward: 5104832
Free requests (4539):   forward: 19996544, backward: 11915160
Lock Ordering: Enabled

The -o -w switches generated details such as:

OWNER BLOCK 732776
Owner id: 114332029419524, type: 1, pending:  0
Process id:  26620 (Alive), thread id:   2348
Flags: 0x08   wake
Requests (431): forward: 732888, backward: 10111616
Blocks: *empty*
732776 waits on nothing.

Most of the entries show:
   Flags: 0x08   wake

Oddly, all of the entries show:
   Blocks: *empty*

I would have expected at least some of the entries to have values associated 
with "Blocks".

What am I missing?


Sean

RE: [firebird-support] Does any Delphi component exist to access a FB database without DLLs?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Andrea,

I find your post contains contradictions.

> I am looking to write a mobile application.
> I don't want to use SQLite because I think it's crap.

OK


> Firebird seems just right, except that it comes with a massive DLL.

"Massive"?  Huh? 

The Firebird Client is ~1MB in size.

What do you define as "massive"?


> Because phones come in all sizes,I just can't be sure that it'll be
> downloaded if it's too big, so the first obvious saving would be the DLL.

My quick scan of my own Android phone finds that most apps (as compared to 
utilities) are 45-50 MB in size, with the Facebook app weighing in at 240MB in 
size.

So, to my mind, the size of the Firebird client is quite reasonable.


Separately, I am not aware that FireMonkey/Delphi can be deployed as mobile 
apps if they require external DLLs.  AFAIR, FireMonkey apps must be built from 
"native" code.


Sean

RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > Am I right to think that I need to create a process to run the command on a
> regular basis (every 5 secs?) to find what objects locks are waiting for?
> 
> Yes. But we're still guessing in the dark

 That's fine, that is at least a path to investigate...


>, the reason of the slowdown could be completely unrelated to the lock manager 
>(e.g. bad plans, 
> undesired access to MON$ tables inside triggers, whatever else).

 We only have 1 process which access MON$ tables, it is a job that runs 
every 5 minutes to report on long running SQL statements, none of our triggers 
reference MON$ structures.

 The only thing that we use often is GET/SET RDB$Context variables...


Sean

RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > I asked for -w, not -iw.
> 
> Or maybe -o -w, if just -w prints nothing.

FYI, -w prints the same basic details

-w -o prints some details -- reading Helen's book to understand what it is 
reporting.


Am I right to think that I need to create a process to run the command on a 
regular basis (every 5 secs?) to find what objects locks are waiting for?


Sean

RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Too many deadlock scans, it means long (> 10 sec) waiting for some locks.
> Regular (every few minutes) fb_lock_print -w output could probably shed
> some light...

Here are some initial results

C:\FIREBIRD\bin>fb_lock_print.exe -d x -iw 1 5000
01:08:29wait/s  reject/s timeout/s blckast/s  wakeup/s dlkscan/s 
deadlck/s
01:08:30 0 0 0 0 0 0
 0
01:08:31 0 0 0 0 0 0
 0
01:08:32 0 0 0 0 0 0
 0
01:08:33 0 0 0 0 0 0
 0
01:08:3465 5 06666 0
 0
01:08:35   10825 0   108   108 0
 0
01:08:36 0 0 0 0 0 0
 0
01:08:37 0 0 0 0 0 0
 0
01:08:3858 2 05858 0
 0
01:08:397120 07171 0
 0
01:08:40 0 0 0 0 0 0
 0
01:08:41 0 0 0 0 0 0
 0
01:08:42 0 0 0 0 0 0
 0
01:08:43 0 0 0 0 0 0
 0
01:08:4425 3 02125 0
 0
01:08:4524 8 02324 0
 0
01:08:46 0 0 0 0 0 0
 0


Sean

RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > We have a client with 320GB database (running FB CS v2.5)
> 
> Is it really so? FB does not support LockHashSlots more than 64K, it would
> truncate your 90001 down to 65521.

 Is the engine that smart to trim to an exact prime number?


> > Now, today, I checked the lock print values again, and got even worse
> > numbers!!!
> 
> The change didn't help, the reason is elsewhere. 21% vs 23% could be
> explained by e.g. printing the stats at 3PM and 5PM.

 The stats were printed at 3am.


> And we don't know what was the mutex wait ratio before they reported the 
> performance issues.

 True, not something that came to mind for us to be monitoring.


> 688 peak connections. Is this expected? Could the load be growing recently?

 Yes, that seems a reasonable value.

 Growth?  Can't really speak to that, the client system is accessible by a 
large number of users, their work activity has day of the week and seasonal 
variation.

 Curious, how would the number of owners (even free owners) impact the lock 
manager?

 Today's numbers are much worse, again.  But the activity load is 
substantially lower (based on the "Enqs" and "Acquires" values).

LOCK_HEADER BLOCK
Version: 145, Active owner:  0, Length: 67108864, Used: 5269256
Flags: 0x0001
Enqs: 202113295, Converts: 934229, Rejects:  96674, Blocks: 615455
Deadlock scans:103, Deadlocks:  0, Scan interval:  10
Acquires: 326947224, Acquire blocks: 186433223, Spin count:   0
Mutex wait: 57.0%
Hash slots: 90001, Hash lengths (min/avg/max):0/   0/   4
Remove node:  0, Insert queue:  0, Insert prior:  0
Owners (47):forward: 732776, backward: 2105384
Free owners (54):   forward: 3893360, backward: 4041872
Free locks (6339):  forward: 734616, backward: 2979632
Free requests (31952):  forward: 2283760, backward: 1299072
Lock Ordering: Enabled


> > Any suggestions on how I can improve the numbers?

 Would a larger/smaller page cache size have any impact?  Currently = 400

 Could reducing the cached pages increase lock operations performance?


Sean

[firebird-support] RE: High "Mutex wait" value, after increase in "Hash Slots" to 90001

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

I forgot to add, the disk subsystem that the database running on supports 
200,000+ 8KB IOPS, so slow disk performance is not likely.

> We have a client with 320GB database (running FB CS v2.5) reporting
> performance issues, while we are investigating possible application sources, I
> have been reviewing their Firebird config.
> 
> Yesterday, I ran fb_lock_print to check on the database and found a "Mutex
> wait" value of 20.9%, which I knew that a "bad thing".  So, I increased (by
> 50%) the "Hash slots" value from 60011 to 90001.
> 
> Now, today, I checked the lock print values again, and got even worse
> numbers!!!
> 
>     LOCK_HEADER BLOCK
>     Version: 145, Active owner:  0, Length: 67108864, Used: 
> 32723368
>     Flags: 0x0001
>     Enqs: 712175161, Converts: 9327661, Rejects: 1440802, Blocks:
> 15963765
>     Deadlock scans:  9, Deadlocks:  0, Scan interval:  10
>     Acquires: 1133127873, Acquire blocks: 256464312, Spin count:  
>  0
>     Mutex wait: 22.6%
>     Hash slots: 90001, Hash lengths (min/avg/max):    0/   0/   5
>     Remove node:  0, Insert queue:  0, Insert prior:  > 0
>     Owners (288):   forward: 732776, backward: 29775680
>     Free owners (400):  forward: 9817904, backward: 9548560
>     Free locks (28162): forward: 20344528, backward: 18378944
>     Free requests (293517): forward: 7727280, backward: 23127520
>     Lock Ordering: Enabled
> 
> 
> Any suggestions on how I can improve the numbers?
> 
> Thanks in advance
> 
> Sean
> 

[firebird-support] High "Mutex wait" value, after increase in "Hash Slots" to 90001

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

We have a client with 320GB database (running FB CS v2.5) reporting performance 
issues, while we are investigating possible application sources, I have been 
reviewing their Firebird config.

Yesterday, I ran fb_lock_print to check on the database and found a "Mutex 
wait" value of 20.9%, which I knew that a "bad thing".  So, I increased (by 
50%) the "Hash slots" value from 60011 to 90001.

Now, today, I checked the lock print values again, and got even worse numbers!!!

LOCK_HEADER BLOCK
Version: 145, Active owner:  0, Length: 67108864, Used: 
32723368
Flags: 0x0001
Enqs: 712175161, Converts: 9327661, Rejects: 1440802, Blocks: 
15963765
Deadlock scans:  9, Deadlocks:  0, Scan interval:  10
Acquires: 1133127873, Acquire blocks: 256464312, Spin count:   0
Mutex wait: 22.6%
Hash slots: 90001, Hash lengths (min/avg/max):0/   0/   5
Remove node:  0, Insert queue:  0, Insert prior:  0
Owners (288):   forward: 732776, backward: 29775680
Free owners (400):  forward: 9817904, backward: 9548560
Free locks (28162): forward: 20344528, backward: 18378944
Free requests (293517): forward: 7727280, backward: 23127520
Lock Ordering: Enabled


Any suggestions on how I can improve the numbers?

Thanks in advance

Sean

RE: [firebird-support] Firebird 2.5 classic performance issue on linux64

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Andreas,



this is the one thing I am getting when I am connecting to the database. I am 
not the one working productively on the system, so I can't really tell wether 
this has become faster or is still the same.

LOCK_HEADER BLOCK
Version: 145, Active owner:  0, Length: 7048576, Used: 540536
Flags: 0x0001
Enqs:   5031, Converts:113, Rejects:  8, Blocks: 11
Deadlock scans:  0, Deadlocks:  0, Scan interval:  10
Acquires:   7695, Acquire blocks:  3, Spin count:   0
Mutex wait: 0.0%
Hash slots: 30011, Hash lengths (min/avg/max):0/   0/   4
Remove node:  0, Insert queue:  0, Insert prior:  0
Owners (3):forward: 252920, backward: 490968
Free owners: *empty*
Free locks (5):forward: 254960, backward: 519480
Free requests (6):forward: 540344, backward: 403464
Lock Ordering: Enabled

This is what the fb_lock_print output looks like.

 Those numbers look to be very good.

 Q: Why are you running Classic server?  How many users/connections are 
there usually to the database?

 Perhaps SuperServer provide better performance - it would allow you to 
"blow up" the page cache size.

RE: [firebird-support] Firebird 2.5 classic performance issue on linux64

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Sean: fb_lock_print seems to have some trouble:
> 
> Unable to access lock table.
> operating system directive shmem_data->sh_mem_length_mapped is 0
> failed -Success
> 
> This is what I am getting from fb_lock_print -d filename.db0

You need to specific the full/local path to the database and as well as the 
database filename.

You really want to look at the fb_lock_print numbers when the database/server 
is under load.


Sean

RE: [firebird-support] Firebird 2.5 classic performance issue on linux64

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Andreas,

> - increase page size to 16kB (gbak -> and restore with new pagesize)
> - increased buffers for firebird to use up to 4GB of ram (256kB) via gfix

With Classic server you *need* to reduce the number of cached pages -- I would 
recommend a value < 500 pages
people keep telling me otherwise. Why would I not want firebird to fill up the 
cache with lots of pages? Am I misunderstanding firebirds buffer concept?

 There are 3 different ‘modes’ that Firebird can run in.

 “Classic” mode is for a large number of connections.  For that mode, the 
number of pages needs to be reduced.

 For “SuperServer” and “SuperClassic” modes the number of pages can be 
maximized.


why would it do that? It is entirely under our control when and if the server 
restarts or shuts down. I agree with the reasoning, I just don't see the 
scenario. Power outage would be the worst case scenario. And if that actually 
happened mid-transaction, we have nightly backups that would be good enough.


Forced Writes = ON is HIGHLY recommended in all production use-cases.
I realize that. The worst case scenario is acceptable to the client though.

 You must be in a unique environment.

 My clients/users would have my skin if I told them they had to redo all 
their work at 5pm, the end of a busy work day – just because I wanted to save 
“millisecond” for each write operation (given the presence of a RAID controller 
with battery cache) by using Forced Writes = Off and the power went out 
(someone bumped a power switch).



> After all of this didn't really seem to do much, we had to pull out the big 
> guns:

> Performance improved a bit, but still not the way we would like it to be.

You should look at the fb_lock_print details, it is possible that you are 
experience issue with the external lock manager.
Thanks for this information. I will check it out as soon as possible. Wasn't 
even aware there is a lock manager.

Andreas



Sean

RE: [firebird-support] Firebird 2.5 classic performance issue on linux64

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Andreas,

> - increase page size to 16kB (gbak -> and restore with new pagesize)
> - increased buffers for firebird to use up to 4GB of ram (256kB) via gfix

With Classic server you *need* to reduce the number of cached pages -- I would 
recommend a value < 500 pages


> - switchted to async writes because we have redundancy and data security
> on hardware level (also via gfix)

Hardware redundancy and security will not protect if the hosts/OS restarts or 
Firebird abends without warning.

Forced Writes = ON is HIGHLY recommended in all production use-cases.


> After all of this didn't really seem to do much, we had to pull out the big 
> guns:

> Performance improved a bit, but still not the way we would like it to be.

You should look at the fb_lock_print details, it is possible that you are 
experience issue with the external lock manager.


Sean

RE: [firebird-support] Is there an easy way for input rows from a script file?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Walter,

> If I connect to a database "Database1" using ISQL, I can write something like:
> 
> OUTPUT MyFile.DAT;
> SELECT * FROM MyTable;
> OUTPUT;
> 
> And all the rows of "MyTable" will go to the text file "MyFile.DAT". That's ok
> and works fine.
> 
> But now, I want to connect to "Database2", which also have a table called
> "MyTable" and with the same structure.
> 
> After that, I want to insert into "MyTable" (of "Database2") the rows
> contained in "MyFile.DAT"
> 
> How can I do such thing without a lot of effort writing an INSERT command in
> each line of "MyFile.DAT"?

The Firebird ISQL tool does not support such a feature.

You could create 2 output files and then use command line utility to combine 
results into single file.

There are some third-party tools which could allow you to script this process 
(I know that IBExpert supports it)


Sean

RE: [firebird-support] FIREBIRD PIVOT function or Matrix

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > There are times when I would love to be able to pivot data on the
> > server, to further process the data an SP.
> 
>SP cannot process queries with unknown number of fields. Neither client
> part, BTW.

Who said that the number of columns/fields was unknown?

I could want to deal with a pivot of a known/expected 'width' within my SP.


As for the client access, I agree that the depending on the pivot definition, 
the number of columns could be unknown.  

But this would be an argument to create API methods which would have the number 
of columns determined by dimensions of the result set, not fixed during the 
call.

This is not a valid reason to say that the pivot can't/should not be created on 
server.


Sean

RE: [firebird-support] Partial update

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > Is that the real issue, you want to eliminate the "prepare" stage?
> 
>"Prepare" does much more than just parsing of query and building plan. It
> also check for permissions and this stage cannot be cached.

Not if the cache was in the context of the current connection, so permissions 
could be checked just once.  Then the prepare would only be done for the first 
execution of each statement variant.

As for the PLAN, arguably this could be handled by a process which would clear 
cache statements whenever DDL was applied to the database -- in essence causing 
the next execution to re-prepare the statement.

Maybe I am just being selfish but it seems my suggestion is more along the 
lines of how other engines think of optimizing performance, rather than your 
"skip parameter" approach.  It also has the advantage of applying to all use 
cases, not just operations using native API.


Sean

RE: [firebird-support] Partial update

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

>If I use prepared statement like this "update table set field1=?, field2=?
> where field3=?" for performance reasons, is there a way to update only
> some fields and leave other untouched without overhead of building
> separate queries on the fly or selecting old values with different query?

If you are using UPDATE, then is it not reasonable to expect that you would 
know what the original values for of each field?

So, it should not be difficult to provide same in the UPDATE statement.

If not, what is the "real cost" (time) of preparing statement?



Must a statement with parameters be prepared before it can be executed via the 
client?


Cus, it seems that what you are asking the engine to maintain a cache of 
previously executed statements.

So if you execute any un-prepared statements, the engine would compare the SQL 
text against the cache list and re-use the previously prepared version, instead 
of preparing the statement a new -- thus bypassing the "prepare" stage.


Is that the real issue, you want to eliminate the "prepare" stage?


Sean

RE: [firebird-support] FIREBIRD PIVOT function or Matrix

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > Any suggestions on my DB design and query to display the values in matrix
> (pivot) format?
> 
>Simple client-side code can show you the matrix in any convenient form.
> There is no point to do that on server.

I disagree!

There are times when I would love to be able to pivot data on the server, to 
further process the data an SP.

Also, performing the pivot on the server could reduce the total amount of data 
transferred to the client.


Sean

RE: [firebird-support] Weird behaviour

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> A thread is trying to perform a SQL select, a client wants to display detailed
> information for a certain row.
> Basicly Select * from TABLE where PKEY = PKEY
> The thread acquires lock in our server, then works it's way through GDS32.dll
> (fbclient.dll) and then times out on the call to WinSock2 select (I guess it
> retries if no success, because it never returns).
> MON$STATEMENT shows a stalled SQL statement corresponding to the SQL
> statement above.

Please review the database "Sweep Interval" setting (IMO, it should be = 0) -- 
your query could be getting caught up with a database sweep.


Sean

RE: [firebird-support] Re: detect duplicate blobs, how to?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Richard,

> Sean
> Even SHA256 can’t eliminate all possibility of a duplicate. If you have files 
> of
> more than 256 bits in them, by the pigeon hole principle, there WILL be
> duplicates within the universe of all possible files. There HAS to be. The
> probability is very low (but not zero) if you keep you set of files below 
> 2^128
> members, but it is NOT 0.

I agree that there is a possibility, but it is all about understanding scale.

Consider, 2^128 is the same number space that is used to represent UUID and 
GUID values, and the changes of a collision is 2.71 x 10^18 
(https://en.wikipedia.org/wiki/Universally_unique_identifier )

Consider, the number of words ever spoken by human beings = 5 Exabytes (< 2^63) 
(http://highscalability.com/blog/2012/9/11/how-big-is-a-petabyte-exabyte-zettabyte-or-a-yottabyte.html),
 so what are the chances that more than 2^128 files have been created?

Consider, the probability that a rogue asteroid crashes on Earth *within the 
next second*, obliterating civilization-as-we-know-it is about 10^-15.  Which 
is 45 **orders of magnitude** more probable than the SHA-256 collision. 
(http://stackoverflow.com/questions/4014090/is-it-safe-to-ignore-the-possibility-of-sha-collisions-in-practice)

IMO, the changes of a collision are for all practical purposes 0.

But if you still think there is a chance, then use SHA512.


Sean

RE: [firebird-support] Is this a bug of Firebird?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Doug,

> Delphi's latest compiler provides a hint in these cases:
> 
> var
>   x: Int32;
> begin
>   try
>     x := 0;
>     x := 1;
>     writeln(x);
> end.
> 
> [dcc32 Hint] Project1.dpr(14): H2077 Value assigned to 'x' never used
> (Line 14 is the one assigning zero.)

In hindsight my post should have read more like:

- No compiler would treat the condition as an error.  A warning, Yes.  But not 
an error.

- There are plenty of DB tools (IBExpert for one) which perform their own 
"syntax checking" and report warnings (though I would need to test if IBE 
reports a warning for this case).

- AFAIK, the FB engine only reports errors.  So, a new set of functions/methods 
would need to be developed to report and handle warnings.


Sean

RE: [firebird-support] Is this a bug of Firebird?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Yes, but I can not know the value of the column X.ALU_NOMBRE
> 
> And the idea, of course, is know that value, that's why it appears in the FOR
> SELECT. If not, I can do nothing with X.ALU_NOMBRE

You are asking for the system to evaluate the *intent* of logic.

That is completely outside the purview of any application environment that I 
know.

The only thing that a system can check/enforce is the correctness of the code, 
not to check whether the developer has 2 brain cells.


Sean

RE: [firebird-support] Migrating a 2.5 server

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Luke

> I would like to add a sub-topic in this discussion: currently, I'm using the
> nbackup tool to run full/incremental backup. I'll have to update the Linux box
> from CentOS 5.7 (2011) to Ubuntu 16.04 (current LTS). Considering both has
> Firebird 2.5.x, is there any issue related to backup/restore the database file
> in this scenario that I need to know before proceeding with the server
> update?

Create a separate thread

Don't muddle an existing conversation to discuss something which is completely 
unrelated!!!



Sean

RE: [firebird-support] firebird schema changes during backup

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

>  On Mon, 23 Jan 2017 15:48:15 -0500 Dimitry Sibiryakov
> mailto:s...@ibphoenix.com [firebird-support]  supp...@yahoogroups.com> wrote 
> 
> Changing metadata is a bad idea in general.
> Continuous development is the worst thing that can happen to a database.
> 
> 
> And that is the traditional database's Achilles Heel because "change" is
> inevitable and rapid change is a fact of life today.
> 
> If Firebird were to bolster its support for dynamic changes it would become a
> much more attractive beast.

Firebird does support dynamic changes, you can change the database schema while 
users are connected.

Dmitry was referring more to the inherent risks of using a production database 
as a development platform -- schema changes need to be tested and carefully 
applied. 

In our case, we use strict SQL script numbering process along with a CI process 
that ensures that all scripts are checked/tested against exemplar database*0*s 
before a master script is created/applied.


Sean

RE: Re: [firebird-support] FB 2.5 migrate to 3.0

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

James,

Check that no other fbclient.dll or gds32.dll exist on you system…


Sean

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Monday, January 9, 2017 6:59 PM
To: firebird-support 
Subject: Re: Re: [firebird-support] FB 2.5 migrate to 3.0




Dear Helen,

Thanks for your answer. I think I got help from you also during last time 
migration test. :-)
I tested it with your advice but it still doesn't work.
I would like to test it with a fresh installation of FB 3.0 later.

I should make my description more clear for my steps.

0. First of all, all applications are running in Windows 7. FB x32 versions are 
used for testing.
1. Use gbak (FB 2.5) to backup Firebird 2.5 database file.
2. Use gbak (FB 3.0) to restore backuped FBK file to a new database file.
3. Use isql, to run: SQL> alter user sysdba set password 'masterkey';
respond: Database: myimport, User: SYSDBA
4. Open new database file in IBExpert with FB 3.0 client library 
(fbclient.dll), it works.
5. Run my own applicatoin with FB 3.0 client library which works with FB2.5 
client library, error message shows below:
[FireDAC][Phys][FB]Your user name and password are not defined. Ask your 
database administrator to set up a Firebird login.


Connection string inside my code

  object dbcMain: TFDConnection
Params.Strings = (
  'DriverID=FB'
  'User_Name=sysdba'
  'Password=masterkey')



Best regards,

James

From: Helen Borrie hele...@iinet.net.au 
[firebird-support]
Date: 2017-01-10 05:36
To: firebird-support
Subject: Re: [firebird-support] FB 2.5 migrate to 3.0


Saturday, January 7, 2017, 3:02:03 PM, James wrote:

> I am trying to migrate my database from 2.5 to 3.0 now.

> Now I show all my steps p;I have done for migration procedure.

> 1. Use gbak (FB 2.5) to backup Firebird 2.5 database file.
> 2. Use gbak (FB 3.0) to restore backuped FBK file to a new database file.
> 3. Use isql, to run: SQL> alter user sysdba set password 'masterkey';
> respond: Database: myimport, User: SYSDBA

This alters the user SYSDBA under the default UserManager, which is
SRP

> 4. Open new database file in IBExpert, it works.

IBExpert is apparently using the new client library. You don't say
what platform you are on but, if it is Windows, the client library is
fbclient.dll. On Linux, it is libfbclient.so.

> 5. Run my own applicatoin which works with FB2.5, error message shows below:
> [FireDAC][Phys][FB]Your user name and password are not defined. Ask
> your database administrator to set up a Firebird login.

If your application is connecting via the v.2.5 client library then it
doesn't know about SRP. You'll need to go back to isql and create the
SYSDBA user for the Legacy_UserManager plug-in, viz.,

CREATE USER sysdba PASSWORD 'masterke' USING PLUGIN Legacy_UserManager;

Also look at firebird.conf, to make sure that Legacy_UserManager is
available. This will probably mean changing from the default
settings, if you did not do that previously:

AuthServer = Srp, Legacy_Auth

UserManager = Legacy_UserManager

Don't forget to stop and restart the Firebird server to enable your
config changes.

On the other hand, if you want your application to use the new
security features, make sure that it is loading the FB3 client
library. If the app is 32-bit, you will need the 32-bit client, even
if your server is running 64-bit Firebird.

HB

[firebird-support] RE: info

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> We want to create table- and fieldnames (Firebird 3) in camelcase.

You can do that now, you can use whatever case you want.


> If we
> want to write a select statement we have to use quotes around a tablename.
> This is not userfriendly. Is there a way that we don't have to use the quotes
> in SQL-statements ?

By SQL standard Object names are case insensitive, so all objects are 
upper-cased.

There is an exception for objects created as "identifiers" which requires the 
use of double-quotes (another SQL standard) for all references to the Objects.


> We don't want to create our table- and fieldnames in lowercase or
> uppercase.

While the engine would return/display the object names in upper case, that does 
not stop you from using CamelCase in your SELECT statements, Triggers and SPs 
-- This is something that I do religiously, even after 20 years of using IB/FB.


Sean

RE: [firebird-support] Re: Why this procedure crashes server

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Geoff,

> I am curious as to why you are using dialect 1 on an FB3 database.
> 
> Back when Interbase 6 was released the 1999 language reference said:
> "Dialect 1 exists to ease the migration of legacy databases."

Did IB 6 actually get released?

My own memory has IB 6 in beta when Inprise open-sourced the entire thing...  
IB 6 was finally released but only sometime after Firebird project was started 
in 2000 and Inprise changed their mind about the whole open-source thing.


> More currently, the FirebirdSQL website ( http://firebirdsql.org/manual/isql-
> dialects.html ) says:
> 
> "Currently it is possible to create databases in Dialect 1 and 3 only, 
> however it
> is recommended that you use Dialect 3 exclusively, since Dialect 1 will
> eventually be deprecated. Dialect 2 cannot be used to create a database
> since it only serves to convert Dialect 1 to Dialect 3."  [the highlighting 
> is mine]


In our case, having started using IB v4.x, we have been locked-in to dialect 1 
and we are using v2.5 (looking at v3.x).

The most significant issue with dialect 3 is the "stupid" interpretation that 
Inprise made of the SQL standard when it comes for handling of precision in 
math operations -- our application had way too many processes that expected the 
dialect 1 handling/treatment that the cost of "re-tooling" and testing was much 
too high.

IMO, the dialect 3 math precision handling is so bad, that if I had to make a 
fresh choice today, I would still choose dialect 1.  So, personally, I don't 
blame anyone for making the same choice.

We are hoping that Firebird will adopt more "logical/appropriate" rules for 
dialect 3, there has been talk of same.  Until then, we will keep using dialect 
1 for as long as possible.


Sean

RE: [firebird-support] SS FB3.01 - Is it possible to set CPU utilization to max?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Anto,

> During project development/testing or database maintenance, such as
> backup/restore, mostly, there is only 1 connection to FB Server.
> 
> I have 6 core processors and during those process, cpu's utilization is
> about  16%. I knew, this is because there is only 1 connection to FB server, 2
> connections will raised cpu's utilization to 32% and so on.
> 
> Is it possible to force Firebird to use max. cpu utilization when there is 
> only 1
> connection?

You could use the Windows CPU Affinity mask to restrict the CPUs that the 
Firebird service/process can run on, and thereby limit the max CPU % that could 
be used.


Sean

RE: [firebird-support] Sequence

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

.. and can I order all active records completly?

(without a loop)

 Don't manage the records during your direct update, manage the order 
records via a AFTER UPDATE trigger...

 Using a "separator" in the priority range is good, you can use it to 
manage the scope of the changes required.  You would not need to update every 
row, just enough rows to establish the new sequence.

Update table set prio = 1 to n where prio is not null order by prio?

Von: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com]
Gesendet: Freitag, 2. Dezember 2016 10:44
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] Sequence


Okay,

I would simply change the prio new in steps of 10.

Also I can do the following:

Priority in steps of 1

ID 10 Prio 1
ID 20 Prio 2
ID 23 Prio 3
ID 11 Prio 4

Now the user can Set the ID 23 to Prio 2, how can I realize this without a loop?

Update table set prio = newprio(1) where id = 20;
Update table set prio = prio - 1 where prio < newprio;
Update table set prio = prio + 1 where prio > newprio;

..more simplier?



Von: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com]
Gesendet: Donnerstag, 1. Dezember 2016 18:47
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] Sequence



Olag,

> Before
> Record 1 prio 3
> Record 2 prio 10
> Record 3 prio 18
> Record 4 prio 20
> Record 5 prio 30
>
> The user set the record 4 to prio 15, I would like to do this:
>
> Record 1 from 3 to 10
> Record 2 from 10 to 20
> Record 3 from 20 (should 15, Destination between record 2 and record 3)) to
> 30
> Record 4 to 40
> Record 5 to 50

You example is confusing.

If a user can never change the position of Record 4 ahead of Record 3 (as is 
the case in your example) what purpose does priority serve.

If you had said that the outcome you wanted was:

Record 1 from 3 to 10
Record 2 from 10 to 20
Record 4 to 30 <-*
Record 3 from 20 (should 15, Destination between record 2 and record 3)) to 40 
<-*
Record 5 to 50

Then that would have made sense.

Please clarify, the problem domain does matter to the solution.

Sean

RE: [firebird-support] Sequence

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Olag,


> Before
> Record 1 prio 3
> Record 2 prio 10
> Record 3 prio 18
> Record 4 prio 20
> Record 5 prio 30
> 
> The user set the record 4 to prio 15, I would like to do this:
> 
> Record 1 from 3 to 10
> Record 2 from 10 to 20
> Record 3 from 20 (should 15, Destination between record 2 and record 3)) to
> 30
> Record 4 to 40
> Record 5 to 50

You example is confusing.

If a user can never change the position of Record 4 ahead of Record 3 (as is 
the case in your example) what purpose does priority serve.

If you had said that the outcome you wanted was:

Record 1 from 3 to 10
Record 2 from 10 to 20
Record 4 to 30  <-*
Record 3 from 20 (should 15, Destination between record 2 and record 3)) to 40 
<-*
Record 5 to 50

Then that would have made sense.

Please clarify, the problem domain does matter to the solution.


Sean

RE: [firebird-support] VIEW CREATES ON FB 2.5 BUT DO NOT CREATE ON FB 1.5

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Last row error
> 
> Invalid Token. Dynamic SQL Error. Invalid Command Data Type unknown


Try testing each SQL individually and then building up the SQL using different 
UNION combinations to see where the problem is.


Sean

RE: [firebird-support] Re: Best way to delete millions of rows

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > @Ann
> > I will run some test and see what happens if the guids are generated
> > in way where the last part varies. Like this:
> > 39db9ec6-178e-77b4-5d7b-d4e969b0cd98
> > 39db9ec6-178e-e4ba-54ed-92347a131663
> > 39db9ec6-178e-c95b-c709-a42e349410df
> 
> There would be a lot of advantage, for Firebird, in using this kind of guid?

Yes, Firebird indexes use prefix compression so the leading 14 chars of the 
above values would be stored under a single entry.

The reality is that UUID/GUID while "universally" unique do have parts which 
are unique to a source system, so they are common for all UUID/GUID from that 
system.


Sean

RE: [firebird-support] Digest Number 9396

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

The performance of the query may have been a function of the query approach 
itself, not the presence of the index

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Monday, November 21, 2016 1:19 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Digest Number 9396



I tested the query on a big database. It was faster if I disabled the index.


Tiberiu


---In 
firebird-support@yahoogroups.com, 
> wrote :

> 2. the use of index is deliberately blocked, I want to do it this way, my 
> query
> is faster.


"Blocked" why?


Sean

[firebird-support] tracker.firebirdsql.org and web.firebirdsql.org will be offline Tuesday November 22 from 20:00 to 24:00 EST

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Sean

RE: [firebird-support] Digest Number 9396

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> 1. I create those temporary tables for one user only, for one transaction 
> only,
> not visible for other users. I keep a track (log) of those tables and delete
> when I exit the program (drop table).

All of that is already supported by temporary tables -- each connection creates 
a **separate instance** of the GTT, so connections never see other data.

The GTT data is deleted/dropped when the Transaction commits (default) or when 
db connection is closed.


> 2. the use of index is deliberately blocked, I want to do it this way, my 
> query
> is faster.

"Blocked" why?


Sean

RE: [firebird-support] Digest Number 9396

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Dmitry,

> 21.11.2016 16:22, tiberiu_horv...@yahoo.com [firebird-support] wrote:
> > Hope this is all right now .
> 
>No. The idea to create temporary tables on the fly and then drops them is
> completely wrong for Firebird.

"Wrong" is really not the correct word for this case.

"Sub-optimal" or "inefficient" would be better.


Sean

RE: [firebird-support] Digest Number 9396

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> I do not declare domains.

But you could!  With just a little effort on your part.

Take a little time, do the following:

CREATE DOMAIN D_INTEGER AS Integer;
CREATE DOMAIN D_Varchar_20 AS VARCHAR( 20);

Then when you create your temporary tables just make 2 minor changes:

create global temporary table TEMP_112233  
(
  id : D_Integer,
  name : D_varchar_20
)

RE: [firebird-support] Optimizer request

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Still no changes.  This looks to me as a riddle.


> select orders.*
> from
>   (select partid from partners where partners.country = ‘Spain’) T
>   JOIN orders ON orders.partid = T.partid

Do you have an index on partners.country?

If not, how do you expect the system to optimize the search?  

The engine would need to perform a scan of the partners table...



Sean

RE: [firebird-support] Optimizer request

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Try:

select orders.*
from
  (select partid from partners where partners.country = ‘Spain’) T
  JOIN orders ON orders.partid = T.partid

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: September 9, 2016 12:06 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Optimizer request




Same result. No changes. Perfomance analyzer gives same result.

Djordje

From: mailto:firebird-support@yahoogroups.com
Sent: Friday, September 09, 2016 5:10 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Optimizer request


Never use IN (subselect). Change to

select * from orders where exists( select * from partners where partners.partid 
= orders.partid and partners.country = ‘Spain’)

2016-09-09 14:30 GMT+02:00 'Djordje Radovanovic' 
softsis...@sbb.rs [firebird-support] 
>:

I tried query with subquery in where clause and found big issue for this type 
of subquery.

for example:

select * from orders where orders.partid in (select partners.partid from 
partners where partners.country = ‘Spain’)

Perfomance Analysis returns me this

partners 687660 non index reads
orders  28657 index reads

If you analyze this result you’ll find that there is 687659 unnecessary non 
index reads. If developer of optimizer accept that all queries on the left side 
of where clouse has priority (and there is no way to be opposite) than we have 
big improvement in optimization.

Best regards,

Djordje Radovanovic

[firebird-support] RE: Substring and negative numbers

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Jeremy,

> This simple query:
> 
> SELECT left(c.addr_5, CHAR_LENGTH(c.addr_5)-3) as trimmed
> FROM vecusts c
> 
> 
> Produces this error

No error was provided/included.


Sean

RE: [firebird-support] gbak error

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Nick,

> it either happens on creation or a file is started and then removed very
> quickly

"then removed very quickly"??? 

Please elaborate.


Sean

RE: [firebird-support] gbak error

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> I'm running fb2.1 on centos5, recently a system has started throwing this
> error when asked to do a backup, the gbak parameters are unchanged,
> permissions all seem ok, any suggestions what I might check
> 
> gbak: ERROR:Unexpected I/O error while writing to backup file
> gbak:Exiting before completion due to errors

Check integrity of storage subsystem.


Sean

RE: [firebird-support] Help needed with grouping, GROUP BY and LIST()

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Mike,

What’s performance like?


Sean

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: August 28, 2016 5:25 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Help needed with grouping, GROUP BY and LIST()



Sean,


Have a look at my reply to your second question, about using CTE, that will be 
part of your answer for this problem as well.

FYI: LIST() doesn't observe ORDER BY...
Thanks to your hint and example CTE I was able to get my query fully working as 
follows. Sorry, there are a couple of name changes since the original question 
and I dispensed with the J_RESULT_GROUP table as a test can only belong to 0 or 
1 groups.

-- Create the CTE
with

-- CTE 1 is for a flow instance
FLOW_INSTANCE(FI_ID, F_NAME, F_PATH)
as
(
select jfi.ID, f.NAME, n1.NAME || ' to ' || n2.NAME
from J_FLOW_INSTANCE jfi
join FLOW f on f.ID = jfi.FLOW
join NODE n1 on n1.ID = jfi.SRC
join NODE n2 on n2.ID = jfi.DEST
),

-- CTE 2 is for a flow test
FLOW_TEST(FT_ID, FI_ID, F_NAME, F_PATH, FT_MODE, RES_CHAR, RES_PROP, TGROUP)
as
(
select jft.ID, fi.FI_ID, fi.F_NAME, fi.F_PATH, m.NAME, c.NAME, p.NAME, 
jft.TGROUP
from J_FLOW_TEST jft
join FLOW_INSTANCE fi on fi.FI_ID = jft.INSTANCE
join MODE m on m.ID = jft.MODE
join J_TEST_RESULT_CHARS jtrc on jtrc.TEST = jft.ID
join CHARACTERISTIC c on c.ID = jtrc.RESULT
join J_CHAR_PROPERTY jcp on jcp.CHARACTERISTIC = c.ID
join PROPERTY p on p.ID = jcp.PROPERTY
)

-- Now the main query, which is the union of those tests that do not belong to 
any group and the grouping of those that are in a group

-- 1) Find all test results that have not been added to a group 
(FLOW_TEST.TGROUP = null) and combine them by test ID
select ftr_ng.FT_ID as FT_ID,
ftr_ng.FI_ID as FI_ID,
ftr_ng.F_NAME as F_NAME,
ftr_ng.FT_MODE,
ftr_ng.F_PATH as FI_PATH,
list(distinct ftr_ng.RES_CHAR) as RES_CHARS,
list(distinct ftr_ng.RES_PROP) as RES_PROPS,
'No group' as T_GROUP
from FLOW_TEST ftr_ng
where ftr_ng.TGROUP is null
group by 1, 2, 3, 4, 5

union

-- 2) Find all the test results that are in a group and combine each group into 
a single row
select  list(distinct ftr_g.FT_ID) as FT_ID,
list(distinct ftr_g.FI_ID) as FI_ID,
list(distinct ftr_g.F_NAME) as F_NAME,
ftr_g.FT_MODE,
list(distinct ftr_g.F_PATH) as FI_PATH,
list(distinct ftr_g.RES_CHAR) as RES_CHARS,
list(distinct ftr_g.RES_PROP) as RES_PROPS,
ftr_g.TGROUP
from FLOW_TEST ftr_g
where ftr_g.TGROUP is not null
group by 4, 8

Thanks again,

Mike

RE: [firebird-support] After update trigger: the same values for new.somefiled and old.somefield

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

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

Patrick??

Please confirm the problem details.


Sean

RE: [firebird-support] After update trigger: the same values for new.somefiled and old.somefield

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Thomas,

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

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.


Sean

RE: [firebird-support] Number of Firebird Users

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> How many concurrent users may be created and connect to a FireBird
> database on such a Server ?
>
> Compaq Proliant ML 370 G1
> 
> Pentium III 667/133 Mhz 1.2GB RAM 256MB Cache

Without a sense of the load/activity which would be placed on the server it is 
impossible to know.

Also, details about OS and the storage system (IDE HDD, Raid x??) to be used 
are required.

IMO: You are really talking about a system with a very low performance profile. 
 After all the system would be 15-16 years old!!!


Sean

RE: [firebird-support] Help needed with grouping, GROUP BY and LIST()

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > Q: Did you mean to exclude the 2nd "HIGH_FLOW" characteristic value from
> > the summary?
>
> Yes, the same information should not be repeated within a single 'field'. The
> second "HIGH_FLOW" characteristic is only in the original result set because 
> it
> has 2 properties (> 10 litres / sec and > 50 gals / hour - sorry contrived I
> know!) via the J_CHAR_PROPERTY table.
> 
>> Q: Do you require the relationship/relative position between the
>> "characteristic" and "property" values be maintained in the summary result?
>
> It would be nice, but not essential. Currently the 'properties' of a
> 'characteristic' are not ordered to keep the example simple, but I could add a
> SEQ field to define the ordering and in that case it would be good for the
> LIST() to observe the order.

Have a look at my reply to your second question, about using CTE, that will be 
part of your answer for this problem as well.

FYI: LIST() doesn't observe ORDER BY...


Sean

RE: [firebird-support] Help needed with grouping, GROUP BY and LIST()

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Mike,

> Q2. How can I create a further table J_RESULT_GROUP and query for
> arbitrarily grouping results together where any distinct elements are LIST()ed
> whilst common elements are 'GROUP'ed (i.e. appear only once). For
> example if J_RESULT_GROUP had 2 rows:
> GROUP TEST
> 1    2
> 1    44
> It would give:
> TEST_ID    JFI_ID    F_NAME    N1_NAME    N2_NAME    MODE    CHARACTERIST
> IC    PROPERTY
> 2,44    2,3    FLOW_1, FLOW_2    NODE_C    NODE_D    MODE_1,
> MODE_2    LOW_FLOW    < 5 litres / sec

Look at using Common Table Expressions (CTE)

Here is a rough sample based on your details

WITH JFT_JRG (N1_ID, N2_ID, Group) AS (
SELECT
  SRC as N1_ID, DEST as N2_ID, jrg.Group, LIST( Test) as TestList
  FROM J_FLOW_TEST jft
JOIN J_FLOW_INSTANCE jfi ON jfi.ID = jft.INSTANCE
JOIN J_Result_Group jrg ON jrg.Test = jft.ID
  GROUP BY 1, 2, 3
  )
WITH JFT_JFI (N1_ID, N2_ID, Group) AS (
SELECT
  SRC as N1_ID, DEST as N2_ID, jrg.Group, LIST( jfi.ID) as JFIList
FROM J_FLOW_TEST jft
  JOIN J_FLOW_INSTANCE jfi ON jfi.ID = jft.INSTANCE
  JOIN J_Result_Group jrg ON jrg.Test = jft.ID
GROUP BY 1, 2, 3
  )
SELECT
  Jft_JRG.TestList,
  Jft_JFI.JFIList,
  ...
FROM (
SELECT
  DISTINCT SRC, DEST, jrg.Group
FROM J_FLOW_TEST jft
  JOIN J_FLOW_INSTANCE jfi ON jfi.ID = jft.INSTANCE
  JOIN J_Result_Group jrg ON jrg.Test = jft.ID
  ) jft_Set
  JOIN NODE n1 ON n1.ID = jft_Set.SRC
  JOIN NODE n2 ON n2.ID = jft_Set.DEST
  JOIN JFT_JRG ON N1_ID = jft_Set.SRC AND N2_ID = jft_Set.DEST AND Group = 
jft_Set.Group
  JOIN JFT_JFI ON N1_ID = jft_Set.SRC AND N2_ID = jft_Set.DEST AND Group = 
jft_Set.Group
  ...

RE: [firebird-support] Help needed with grouping, GROUP BY and LIST()

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Mike,

Some questions before I can give a suggestion:

> TEST_ID    JFI_ID    F_NAME    N1_NAME    N2_NAME    MODE    CHARACTERIST
> IC    PROPERTY
> 1    1    FLOW_1    NODE_A    NODE_B    MODE_1    HIGH FLOW    > 10 litres  /
> sec
> 1    1    FLOW_1    NODE_A    NODE_B    MODE_1    HIGH FLOW    > 50 gals /
> hour
> 1    1    FLOW_1    NODE_A    NODE_B    MODE_1    LOW LOSS    Pump current <
> 30
> 2    2    FLOW_2    NODE_C    NODE_D    MODE_1    LOW FLOW    < 5 litres / sec
> 44    3    FLOW_1    NODE_C    NODE_D    MODE_2    LOW FLOW    < 5 litres / 
> sec

> Q1. How can I group and LIST() to produce a single row for the first test case
> (FLOW_1 from NODE_A to NODE_B with MODE_1). The result would look
> something like this:
> 1    1    FLOW_1    NODE_A    NODE_B    MODE_1    HIGH_FLOW, LOW_LOSS    >
> 10 litres / sec, > 50 gals / hour, Pump current < 30

Q: Did you mean to exclude the 2nd "HIGH_FLOW" characteristic value from the 
summary?

Q: Do you require the relationship/relative position between the 
"characteristic" and "property" values be maintained in the summary result?


Sean

RE: [firebird-support] Delete causes server to hang

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Anyway, right now there is not enough information - to investigate it, run
> gstat -r -t < tablename > stat.txt

Additionally, use gstat and extract the header page stats.

This will allow for the OAT, OIT, and current transaction to be reviewed, and 
check the sweep interval setting.


Sean

RE: [firebird-support] Delete causes server to hang

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> On my website i have a tabel where i log some web requests - about
> 90.000/hour.
> 
> Every hour this SQL is executed in a stored procedure:
> delete from requests where created < dateadd(-7 day to
> current_timestamp);

Do you have an index on "created"?


Sean

RE: [firebird-support] SQL performance SS FB 3.0.1 vs SC FB 2.5.2

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Just found that on a particular case, SS FB 3.0.1 is about 4x slower than SC 
> FB
> 2.5.2.
> 
> Here is the query :
> ==
> select a.id,b.nm from tbl1 a, tbl2 b
> where a.id = b.id
> and a.id in (select c.id from tbl3 c)
> 
> If the query change to this :
> ==
> select a.id,b.nm from tbl1 a, tbl2 b, tbl3 c
> where a.id = b.id
> and a.id = c.id
> 
> SC FB 2.5.2 is about 1.3x faster than SS FB 3.0.1.

The queries are not really equivalent.

How about this query:

select a.id,b.nm 
from tbl1 a
  JOIN tbl2 b ON b.id = a.id
Where
  EXISTS( 
SELECT 1
FROM tbl3 c
WHERE
  c.id = a.id
  )

RE: [firebird-support] Cannot create simple table

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> >  It is only 27 characters in length!
> 
>Exactly. This is a magic number.

No, it is not!

I have plenty of triggers with length = 31, without any issue.

RE: [firebird-support] Cannot create simple table

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > Any ideas?
> 
>Shorten name of the PK_CUR_CASE_INV_NON_JUD_CNT constraint.

Why?  It is only 27 characters in length!

RE: [firebird-support] Cannot create simple table

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Trying to create a simple table and receive this error:
> 
> unsuccessful metadata update
> cannot create index PK_CUR_CASE_INV_NON_JUD_CNT
> while executing:
> ALTER TABLE CUR_CASE_INV_NON_JUD_CNTY ADD CONSTRAINT
> FK_CUR_CASE_INV_RPT3
>   FOREIGN KEY (REPORT_ID) REFERENCES CUR_CASE_INV_RPT
>   (REPORT_ID)
>   ON DELETE CASCADE
>   ON UPDATE NO ACTION
> 
> 
> Here is the DDL:
> 
> CREATE TABLE CUR_CASE_INV_NON_JUD_CNTY
> (
>   REPORT_ID  INTEGER NOT NULL,
>   FILING_COUNTY  VARCHAR(    54) NOT NULL COLLATE NONE,
>   NON_JUD_COUNT      INTEGER NOT NULL,
>   NON_JUD_AMT    NUMERIC( 15, 2) NOT NULL,
> CONSTRAINT PK_CUR_CASE_INV_NON_JUD_CNT PRIMARY KEY
> (REPORT_ID, FILING_COUNTY)
> );
> COMMENT ON TABLE CUR_CASE_INV_NON_JUD_CNTY IS '07.19.16 Stores
> the filing counties and non judgment data for use with the current case
> inventory stats report.';
> ALTER TABLE CUR_CASE_INV_NON_JUD_CNTY ADD CONSTRAINT
> FK_CUR_CASE_INV_RPT3
>   FOREIGN KEY (REPORT_ID) REFERENCES CUR_CASE_INV_RPT
>   (REPORT_ID)
>   ON DELETE CASCADE
>   ON UPDATE NO ACTION
> ;
> 
> Any ideas?

What tool are you using to execute the DDL?


Sean

RE: [firebird-support] Server reboot during Windows exe setup?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Is there a remote chance that this reboot has anything to do with the Firebird
> installation then went on at that very second? Can it be related to the
> installation of the Win32 runtimes that was going on at this time?

The Firebird install would only prompt for a reboot as appropriate, it would 
not perform a reboot itself.


Sean

RE: [firebird-support] Incorrect values within SQLDA structure SQLDA version expected between 1 and 1, found 0 when using Firebird 3.0 client with older (BDE) programa

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Am 14.07.2016 um 13:35 schrieb Dimitry Sibiryakov s...@ibphoenix.com
> [firebird-support]:
> > 14.07.2016 13:33, jonatan.laurit...@yahoo.dk [firebird-support] wrote:
> >> Is this bug in Firebird 3.0 client and is there workaround?
> >This is a bug in BDE and you must ask for workaround at Embarcadero
> support.
> The problem there would be that the BDE has reached its end of life for
> some years now...

Then you can't use FB v3

The project/engine functionality needs to continue to evolve and this can 
result in legacy technology no longer being supported.  

While the project does not deliberately make changes to foster 
incompatibilities, it is the responsibility of the third party connectivity 
providers to ensure that their solution supports the change that the project 
makes.


Sean

RE: [firebird-support] FB 32 bits x FB 64 bits

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Dear Friends.
> 
> One more time, a question around FirebirdSQL performance.
> 
> I ran the same application before compiled 32 bits and after compiled 64 bits.
> 
> The version compiled 64 bits ran  slowly  (much more) than 32 bits version.
> 
> I dont believe this should be expected.

Please review the list archives related to Windows 64 bit cache performance 
issues.

The problem is not Firebird as much as it is how Windows file cache is 
different with 64 bit versions.

Sean

RE: [firebird-support] Query with few reads is slower than query with many reads. Which query to choose?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> I am trying select contracts together with the contract lines. around 30.000
> contracts and around 800.000 contract lines.  And I have two queries that to
> essentially the same (each contract has at least one contract_line):

...

> All the reads of both queries are indexed reads.
> 
> The question is - which query to choose? I am afraid of many reads, because
> they can create load on hard disk and that can slow down things for other
> users. But from the other side - practice and low execution time shows that I
> should use the second query.
> 
> Is large number of indexed reads harmful?

Not in-and-of themselves, your first query suffers because of the high amount 
of random disk IO which your first query could generate.

In your testing, have you excluded the influence of FB and OS caches on your 
results?

If you run the first query, followed by the second, the results of the second 
query will benefit from the first query loading database pages into OS and FB 
caches.  This can significantly influence/improve the results.


Sean

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

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

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

The link is back up and running


Sean

RE: [firebird-support] Is Firebird 3 ready for Production?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> 27.05.2016 15:26, 'Leyne, Sean' s...@broadviewsoftware.com [firebird-
> support] wrote:
> > Avalerion???
> 
>Yes.

Sorry, I meant what is Avalerion???


Sean

RE: [firebird-support] Is Firebird 3 ready for Production?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

>Especially at version 5 which is on pre-release stage now in accompany with
> Avalerion.
>http://www.ibphoenix.com/ibpr_devel/

Avalerion???


Sean

RE: [firebird-support] Re: SELECT WITH LOCK, WAIT transaction, deadlock

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Dmitry,

> 28.04.2016 16:19, Gabor Boros wrote:
> 
> > 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."
> 
> This is true for two concurrent transactions but may fail for three or more
> concurrent transactions.

That looks like a bug.


Sean

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

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> 1) We have sweep set to 0. We do monitor the transaction gab and if
> needed, we interact. Any kind of automatic sweep under high load will kill
> the server :)

1- Do you run a manual sweep on a regular basis?


2- What is the CPU % like when Firebird "slows down"?


3- Have you tried using CrystalDiskMark to measure your disk IOPs?

My local workstation SSD results are:

---
CrystalDiskMark 5.0.2 x64 (C) 2007-2015 hiyohiyo
---
   Sequential Read (Q=  2,T= 4) :   564.293 MB/s
  Sequential Write (Q=  2,T= 4) :   535.874 MB/s
  Random Read 4KiB (Q=256,T= 1) :   227.652 MB/s [ 55579.1 IOPS]
 Random Write 4KiB (Q=256,T= 1) :   195.258 MB/s [ 47670.4 IOPS]
 Sequential Read (T= 1) :   545.480 MB/s
Sequential Write (T= 1) :   520.939 MB/s
   Random Read 4KiB (Q= 1,T= 1) :36.577 MB/s [  8929.9 IOPS]
  Random Write 4KiB (Q= 1,T= 1) :90.805 MB/s [ 22169.2 IOPS]


3- Have you tried to analyze/determine the long running SQLs being executed, to 
determine if wrong/no indexes are being used by queries or other optimizations 
that could be made?


Sean