[firebird-support] Remove non numeric characters

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

I want to remove non numeric characters from my values. I haven’t found a 
solution with regex. So I do it this way:

What I have: +49.511 1234 567
What I want: 4951112234567

select
'+49.511 1234 567' as WHAT_I_HAVE,
REPLACE(REPLACE(REPLACE(REPLACE('+49.511 1234 567', ' ', ''), '-', ''), '+', 
''), '.', '') as WHAT_I_WANT
FROM RDB$DATABASE

I find this solution very ugly. Is there a more suitable way to remove the non 
numeric values?

Regards
Martin

AW: [firebird-support] Re: Does gbak use WireCompression?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Vlad,

> gbak is usual client application, it needs no special actions to use 
> WireCompression. Just put zlib1.dll near the fbclient.dll and set 
> WireCompression = true at client side (using firebird.conf or DPB). Make sure 
> zlib1.dll is present at server side also.

Thank you. That’s the info I needed.

Sure, restore on the remote is much faster. But sometimes I am just lazy.

Regards
Martin

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Dienstag, 17. September 2019 08:58
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Re: Does gbak use WireCompression?



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

> I often perform restores on remote servers via WAN. Since this sometimes 
> takes a long time, I wonder if gbak > takes into account the parameter 
> WireCompression. What needs to be set as where?

  gbak is usual client application, it needs no special actions to use 
WireCompression. Just put zlib1.dll near the fbclient.dll and set 
WireCompression = true at client side (using firebird.conf or DPB). Make sure 
zlib1.dll is present at server side also.

  But much more efficient way to do restore via slow network is remote 
backup\restore (see CORE-2666).
Read doc\README.services_extension chapter (4) for details.

Regards,
Vlad

AW: [firebird-support] Does gbak use WireCompression?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Alexey,

here is an example:
gbak -c -v -rep /srv/backup/stage01.fbk 10.1.1.2:/srv/firebird/stage01.fdb 
-user sysdba -pass masterkey

Regards
Martin

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Montag, 16. September 2019 17:49
An: Nagy Szilveszter nagy_szilvesz...@yahoo.com [firebird-support] 

Betreff: Re: [firebird-support] Does gbak use WireCompression?


Hello Martin,

Can you please provide the exact command you are using to do such restore?

Regards,
Alexey Kovyazin
IBSurgeon


пн, 16 сент. 2019 г., 18:46 Köditz, Martin 
martin.koed...@it-syn.de [firebird-support] 
mailto:firebird-support@yahoogroups.com>>:

Hello everybody,

I often perform restores on remote servers via WAN. Since this sometimes takes 
a long time, I wonder if gbak takes into account the parameter WireCompression. 
What needs to be set as where?

Regards
Martin

AW: [firebird-support] Problems with database shadows

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

> Did you commit?

Yes, I did.

Martin

[firebird-support] Problems with database shadows

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

I'm trying to create a shadow file. But it doesn't work.

I have executed this command:
create shadow 1 '/srv/firebird/master.shd';

The file is created in file system. But the file size is zero. There is no 
entry in the table RDB$FILES.

Here is the isql output:

SQL> show database |grep -i shadow;
Database: /srv/firebird/syndesk_test02.fdb
Owner: SYSDBA
PAGE_SIZE 8192
Number of DB pages allocated = 29158
Number of DB pages used = 27879
Number of DB pages free = 1279
Sweep interval = 2
Forced Writes are ON
Transaction - oldest = 462
Transaction - oldest active = 463
Transaction - oldest snapshot = 463
Transaction - Next = 502
ODS = 12.0
Database not encrypted
Default Character set: UTF8
Linger: 120 seconds

Server version:
SQL> show version;
ISQL Version: LI-V3.0.3.32900 Firebird 3.0
Server version:
Firebird/Linux/AMD/Intel/x64 (access method), version "LI-V3.0.3.32900 Firebird 
3.0"
Firebird/Linux/AMD/Intel/x64 (remote server), version "LI-V3.0.3.32900 Firebird 
3.0/tcp (linux-w1cs)/P15"
Firebird/Linux/AMD/Intel/x64 (remote interface), version "LI-V3.0.3.32900 
Firebird 3.0/tcp (linux-w1cs)/P15"
on disk structure version 12.0

Is this a bug? What am I doing wrong?

Regards
Martin

AW: [firebird-support] Re: Trace not working

[Köditz, Martin martin.koed...@it-syn.de [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.

Regards,
Martin

AW: AW: [firebird-support] Re: Trace not working

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Vlad,

the trace works now. It just took a long time to start.

Thank you.
Martin

AW: [firebird-support] Re: Trace not working

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Vlad,

after correcting the config, I’m still unable to get a trace. Do you have any 
further hints?

Regards
Martin

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Samstag, 17. März 2018 23:11
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Re: Trace not working




  Hi, Martin
> I’m trying to trace a firebird database. I have following trace config:

>

> database = sd40.fdb

> {

> enabled = true

> log_connections = true

> log_transactions = true

> log_statements = true

> log_statement_prepare = true

> time_threshold = 0

> max_sql_length = 65536

> }

>

> I start the trace:

> fbtracemgr -se localhost/3050:service_mgr -user sysdba -password masterkey 
> -start -name "My Trace" -config sd40.trace.conf

>

> When I execute a query in Flamerobin I get this message (and no trace output):

> Trace session ID 1 started

> Error creating trace session for database "syndesk40.fdb":

> error while parsing trace configuration

> line 6: element "log_statements" is unknown

>

> Can someone show me the mistake?

  Just read error message ;) There is no "log_statements" parameter in trace 
configuration.
Look for all known parameters at fbtrace.conf supplied with Firebird.

Regards,
Vlad

[firebird-support] Trace not working

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

I'm trying to trace a firebird database. I have following trace config:

database = sd40.fdb
{
enabled = true
log_connections = true
log_transactions = true
log_statements = true
log_statement_prepare = true
time_threshold = 0
max_sql_length = 65536
}

I start the trace:
fbtracemgr -se localhost/3050:service_mgr -user sysdba -password masterkey 
-start -name "My Trace" -config sd40.trace.conf

When I execute a query in Flamerobin I get this message (and no trace output):
Trace session ID 1 started
Error creating trace session for database "syndesk40.fdb":
error while parsing trace configuration
line 6: element "log_statements" is unknown

Can someone show me the mistake?

Regards
Martin

[firebird-support] Problem with where clause

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

I think there is a quote missing at the end of ":TO_DATE";

Regards
Martin

Re: [firebird-support] Strange behaviour with char \'#\'

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

I found the cause. There was a trigger that acted as a validator and removed 
the strings.

Thank you all.
Martin

Re: [firebird-support] Strange behaviour with char \'#\'

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Well, I've tried with flamerobin and PHP ibase driver.

Then I have to examine the problem more closely.

Regards,
Martin

[firebird-support] Strange behaviour with char '#'

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

I stumbled upon the following problem:
I have a field of type varchar(7). When inserting the string '#ee' the 
field value remains zero.

Does the character '#' have a special role?

Regards,
Martin

[firebird-support] Strange behaviour on Linux

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Roy,

the x flag is needed for file execution and directory access. While r says you 
can read the directory x says you can access it.

Regards,
Martin

[firebird-support] Backup & restore without dump using service manager

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

is there a way to backup and restore a database using the service manager?

The documentation gives example without service manager:
tux> gbak -backup emptest stdout | gbak -replace stdin emptest_2

But that needs to open the database in exclusive mode.

I've tried the following code but got errors:
tux> gbak -b -user sysdba -password masterkey -se localhost/3050:service_mgr 
/srv/firebird/db01.fdb stdout | gbak -replace stdin /srv/firebird/test.fdb
tux> gbak: ERROR:expected backup description record
tux> gbak:Exiting before completion due to errors

Regards,
Martin

Re: [firebird-support] Always use BIGINT?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Ann,

what do you mean with aligment?

Regards
Martin

[firebird-support] Always use BIGINT?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

In 2014 I was at the FB conference in Prague. If I remember correctly, Ann 
Harrison told the audience that there are no advantages when using SMALLINT or 
INT versus BIGINT. But I don't remember the reason. Was it the block size of 
modern architecture? Maybe Ann can make it clear again? Or someone else. Would 
be great.

Thank you
Martin

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

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hello Joje,

please check the file system. We had a similar issue with btrfs on OpenSuse 
several months ago. After switching to ext4 everything went fine. Maybe it was 
a problem with the file system's journaling option.

Regards
Martin

[firebird-support] How to check for Garbace Collector

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

how can I check if garbage collection is running?

Regards
Martin

Re: [firebird-support] How to create random dates?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

That's it. Thank you Ivan.

[firebird-support] How to create random dates?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

does anybody know an elegant statement to create random dates? It would be 
perfect to define a range.

Regards
Martin

[firebird-support] understanding character sets and collation

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Andrew,

please have a look at the "Firebird 2.5 Language Reference".  There is a 
special section about character data types. Maybe this will help.
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes-chartypes.html

Regards
Martin

[firebird-support] Exists function

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

I think the answer is simple. Since EXISTS just checks for existence the 
results are equal. So after EXISTS fetches (finds) one row the result is true 
and no further records will be checked. So there is internally no difference in 
your queries.

Please correct me if I'm wrong.

Regards,
Martin

[firebird-support] How to change collation of a column?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

is there a way to alter a column's collation? Or have I to recreate the column?

Regards
Martin

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

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Thomas,

only from curiosity: what is your filesystem? I had a similar issue some time 
ago, when we used an OpenSuse with btrfs. After switching to ext4 everything 
was ok. I never investigated the problem deeper, but I think it could be the 
snapshot feature of btrfs that has slowed down the database. But just maybe.

Regards
Martin

[firebird-support] Re: When is cache cleared on SuperServer

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Thank you Vlad.

That is important to know, since I'm using PHP applications with non-persistent 
connections.

[firebird-support] When is cache cleared on SuperServer

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

if I remember right, SuperServer holds cached data as long as connections 
exist. So if the last user disconnects the FB cache is cleared. Is that correct?

When I connect with Flamerobin the table MON$ATTACHMENTS shows me two other 
attachments: CACHE WRITER and GARBAGE COLLECTOR. Does they still exist after 
last log out and will the cache be cleared? Do they influence the cache process?

Regards,
Martin

Re: [firebird-support] How to show current LINGER interval?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Thank you.

Martin

[firebird-support] How to show current LINGER interval?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

how can I see the current interval of Firebird's LINGER option?

Regards,
Martin

Re: [firebird-support] Server crash - guardian restarts permanently

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

I've updated to 3.0.1.32570 x64. Used the OpenSuse repositories.

Still the same problem. Seems there is something wrong with the system. But the 
syslog doesn't give any hints.

Regards,
Martin

[firebird-support] Server crash - guardian restarts permanently

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

one of my Firebird servers did crash today. Now the firebird service doesn't 
work anymore. In firebird.log I can see following output (two entries each 
second):

SDUD34-DB03 (Client)Thu Aug 25 13:28:20 2016
/usr/sbin/fbguard: guardian starting /usr/sbin/firebird



SDUD34-DB03 (Client)Thu Aug 25 13:28:20 2016
/usr/sbin/fbguard: /usr/sbin/firebird terminated abnormally (127)



SDUD34-DB03 (Client)Thu Aug 25 13:28:21 2016
/usr/sbin/fbguard: guardian starting /usr/sbin/firebird



SDUD34-DB03 (Client)Thu Aug 25 13:28:21 2016
/usr/sbin/fbguard: /usr/sbin/firebird terminated abnormally (127)



SDUD34-DB03 (Client)Thu Aug 25 13:28:22 2016
/usr/sbin/fbguard: guardian starting /usr/sbin/firebird



SDUD34-DB03 (Client)Thu Aug 25 13:28:22 2016
/usr/sbin/fbguard: /usr/sbin/firebird terminated abnormally (127)



SDUD34-DB03 (Client)Thu Aug 25 13:28:23 2016
/usr/sbin/fbguard: guardian starting /usr/sbin/firebird



SDUD34-DB03 (Client)Thu Aug 25 13:28:23 2016
/usr/sbin/fbguard: /usr/sbin/firebird terminated abnormally (127)

So what can I do?

Regards,
Martin

Re: [firebird-support] Server crashes sometimes

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Karol,

I've checked the database state. There is no corruption. Since I already use FB 
3.0.0 I will wait for the next official release.

But may be there is something forcing the server to crash. So I would like to 
report it to the developers. But what can I report without a trace? Some hints?

Regards
Martin

[firebird-support] Server crashes sometimes

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi all,

my Database isn't reachable for short times. This happens once in a few days. 
The firebird.log shows following messages:
linux-j4zt  Wed Aug 17 10:59:58 2016
/usr/sbin/fbguard: /usr/sbin/firebird terminated abnormally (-1)

linux-j4zt  Wed Aug 17 10:59:58 2016
/usr/sbin/fbguard: guardian starting /usr/sbin/firebird

During this period users get database unreachable messages.

Can I figure out why this happen? Is there a way to do a trace and to send it 
to bug tracker?
I'm using FB 3.0 x64 on OpenSuse Leap 14.1, Kernel 4.1.21-14-default.

Regards
Martin

[firebird-support] List connected Firebird databases

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

I want to know which databases are currently used by SuperServer. Is there a 
possibility to list all connected Firebird databases to the server? I know I 
can list the attachments within the database but is there a way to get the 
connections handled by the server?

Regards
Martin

Re: [firebird-support] Unable to backup production database with gbak and SuperServer 3

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Fabian,

I changed my command, but now I'm getting that error:
gbak -v -backup 127.0.0.1:prod.fdb backup/prod.fbk -user sysdba -password 
masterkey
gbak: ERROR:Your user name and password are not defined. Ask your database 
administrator to set up a Firebird login.
gbak:Exiting before completion due to errors

Am I doing something fundamental wrong? Password and user name are correct. No 
problems when connecting with Flamerobin.
Is gbak running in embedded mode per default? I thought it communicates with 
the running server engine.

Regards
Martin

[firebird-support] Unable to backup production database with gbak and SuperServer 3

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

I'm trying to backup my production db using gbak.
I'm running command:
Gbak -v prod.fdb prod.fbk

As result:
gbak: ERROR:I/O error during "lock" operation for file "prod.fdb"
gbak: ERROR:Database already opened with engine instance, incompatible with 
current
gbak:Exiting before completion due to errors

I'm using Firebird 3 (3.0.0.32483) on OpenSuse 13.2 x64
gbak:gbak version LI-V3.0.0.32483 Firebird 3.0

Is there a restriction with backup on SuperServer?

Regards
Martin

Martin Köditz
it & synergy GmbH

Re: [firebird-support] When do I need the FB 3 client libs?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Thank you Carlos.

Re: [firebird-support] When do I need the FB 3 client libs?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Thank you Mark.

> 3. Compression

Is compression default on or do I have to edit a config parameter?

Regards
Martin

[firebird-support] When do I need the FB 3 client libs?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

I wonder when to use the new client libs. For getting more concrete: I run a 
web server with apache using the FB 2.5 client libs to access the FB 3.0 RC 2 
server. Is that constellation OK or are there benefits by using the 3.0 client 
libs? Same for access via Flamerobin.

Regards
Martin

[firebird-support] Age as a string and it\'s parts as integer

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Bhavbhuti,

please have a look at Firebird's EXTRACT functions.

e.g.
select extract(year from CAST('1990-01-01' as Date)) from RDB$DATABASE;

returns 1990. Hope this helps.

Regards
Martin

[firebird-support] fbtracemgr

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Hugo,

use this and you will have everything you need:

enabled true
log_transactions true
log_procedure_start true
log_procedure_finish true
log_statement_prepare true
log_statement_start true
log_statement_finish true


Regards,
Martin

[firebird-support] Dynamic Memory Allocation

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Todd,

your description sounds like a problem with the concurrent connections. 
SuperServer works mostly sequential. So maybe there is a long taking query 
blocking the others. Maybe you should give a try to SuperClassic.

For more information see 
http://www.firebirdsql.org/manual/qsg25-appx-architectures.html

Regards
Martin

[firebird-support] FW: Scale-ability problems (PHP + firebird)

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Rudi,

we have a PHP & Firebird environment too. To Reduce the overhead problems we 
use some caching techniques. So the amount of connections for a new site 
request shrinks dramatically. On the other side we're trying to reduce the 
amount by rewriting / optimizing some core queries. The environment itself 
bases on the Zend Framework.

Hope this helps.

Regards
Martin

[firebird-support] What happens with open transactions on interruptions?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

I ask from pure curiosity. What happens on interruption or crash?

I'm using Flamerobin (and other tools) and doing several modifications. The 
transaction is still open. Now the connection breaks. What will the Firebird 
server do? Will the transaction still be open or will Firebird do a rollback?

Regards
Martin

Re: [firebird-support] Restore without constraints

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Mark,

I don't want to delete the column, just the constraint. So I can add a new PK. 
Since there are several FKs referencing to that PK, I have to delete the FKs 
first. That is the main problem. I'm just looking for a method to delete all 
constraints. After that I can cleanly add new ones.

Regards
Martin

Re: [firebird-support] Restore without constraints

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Rik,

thank you for the info. This helps a lot. The reason why I want to delete the 
constraints are wrong namings.

The process is following:

1.  Delete foreign keys

2.  Delete primary keys

3.  Create new keys

I think I can do that via execute blocks as you have shown.

Regards
Martin

RE: [firebird-support] Restore without constraints

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

I've done that. But the keys still exist and I'm not able to delete them while 
there are many dependencies. Maybe the only chance is to delete the foreign 
keys first and then the primary keys. Or is there a more graceful way?

Regards
Martin

[firebird-support] Restore without constraints

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,

is it possible to restore a database without constraints? Or is there another 
way to do this? I just want do delete each primary key in the db.

Regards
Martin

Re: Re: [firebird-support] AW: Performance comparision for primary keys - int vs. varchar

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Dear Ann,

thank you very much for the detailed reply.

Regards
Martin

[firebird-support] AW: Performance comparision for primary keys - int vs. varchar

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi,


I'm using integer IDs for primary keys in my tables. What if I would use 
varchar fields instead? Will I lose performance in that case, especially for 
big tables? Will joins still work as fast as they do for the integer column.


In my case I have a table TBL_BANK (
BANK_ID int,
BIC varchar(11),
...
)


So what will happen if I switch the PK to BIC in that case? I would change the 
FKs to BIC too. I don't know how the index keys are build and stored in 
firebird. So I cannot say this way is faster than the other one. But maybe 
someone else can do.


Regards
Martin