[firebird-support] Parametrized queries and execute statement - dynamic parameter list

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
 
the real question is in point 4
 
tables
CREATE TABLE TABLEX
(FIELD_ID INTEGER
);
 
CREATE TABLE TABLEY
(
ID INTEGER,
AAA INTEGER
);
 
CREATE TABLE TABLEZ
(
ID INTEGER,
BBB INTEGER
);
 
few thinks:
1. If i do something like this
SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC(T.FIELD_ID) P) FROM TABLEX T
 
and TABLEX have 100 records then
 
procedure like
 
SET TERM ^ ;
ALTER PROCEDURE MY_PROC_CALC(ID INTEGER) RETURNS(SUM_AAA INTEGER)
AS
BEGIN
  SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID INTO :SUM_AAA;
  SUSPEND;
END^
SET TERM ; ^
 
prepare statement "SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID" only once? for 
whole
"SELECT MY_PROC_CALC(T.FIELD_ID) FROM TABLEX T"
or it prepare query 100 times for every record?
I do not see entry in MON$STATEMENTS during execution - then i do not know how 
this work
 
2. But if i change proc to use execute statement
 
SET TERM ^ ;
CREATE PROCEDURE MY_PROC_CALC_EXEC(ID INTEGER) RETURNS(SUM_AAA INTEGER)
AS
DECLARE VARIABLE VAR_SQL VARCHAR(1000);
BEGIN
  VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=' || :ID;
  EXECUTE STATEMENT VAR_SQL INTO :SUM_AAA;
  SUSPEND;
END^
SET TERM ; ^
 
and run query 
SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC_EXEC(T.FIELD_ID) P) FROM TABLEX T
 
then it prepare query for every record - not ok :(
and in MON$STATEMENTS it occure many times
 
3. when i change proc to use execute statement with parameters
 
SET TERM ^ ;
CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER)
AS
DECLARE VARIABLE VAR_SQL VARCHAR(1000);
BEGIN
  VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID';
  EXECUTE STATEMENT (VAR_SQL) (ID := :ID) INTO :SUM_AAA;
  SUSPEND;
END^
SET TERM ; ^
 
and run query
SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC_EXEC_PARAM(T.FIELD_ID) P) FROM 
TABLEX T
 
than select is prepared only once - ok :)
 
4. but how to change this sample code to use paramaters and do not prepare 
statement on every record?
 
SET TERM ^ ;
CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER)
AS
DECLARE VARIABLE VAR_SQL VARCHAR(1000);
DECLARE VARIABLE VAR_BBB INTEGER;
DECLARE VARIABLE VAR_NR INTEGER;
BEGIN
  VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID ';
  VAR_NR = 1;
  FOR SELECT 
  BBB 
  FROM 
  TABLEZ 
  WHERE ID=:ID
  INTO :VAR_BBB
  DO
    BEGIN  
   /* DO SOME CALCULATION ON VAR_BBB */
   
   VAR_SQL = VAR_SQL || ' OR ID=:ID' || VAR_NR; 
        How to add here parameter to list of parameters for below execute 
statement?
    END
 
  EXECUTE STATEMENT (VAR_SQL) (ID := :ID,  ID1, ID2, ID3 ...) <- How to 
specify parameters dynamically?
  INTO :SUM_AAA;
  
  SUSPEND;
END^
SET TERM ; ^
 
 
Is this somehow possible? Or should i add this to the Firebird bug tracker as 
new feature request?
This is important from performance point of view.
 
 
regards,
Karol Bieniaszewski
 
 
 
 
 
 
 
 
 
 
 

RE: [firebird-support] How to CAST float to integer with error?

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

>Hello,
>is it possible by using CAST or in any other way to get database error when 
>casting such number to integer?
>SELECT CAST('13.245' AS INTEGER) FROM RDB$DATABASE
>This gives 13 but instead I would like to get an error because precision is 
>lost.
>
>However, I would like to NOT get an error when doing this:
>SELECT CAST('13.000' AS INTEGER) FROM RDB$DATABASE
>Becasue here precision is not lost.
>
>Is it possible to obtain such behaviour in easy way or do I have to use 
>regular expressions?

Not quite what you're asking for, Bruce, but 

SELECT CAST(13.245 AS INTEGER) FROM RDB$DATABASE
WHERE CAST(13.245 AS INTEGER) * 1000 = cast(13.245 * 1000 as Integer)

does return any rows, whereas this statement does:

SELECT CAST(13.000 AS INTEGER) FROM RDB$DATABASE
WHERE CAST(13.000 AS INTEGER) * 1000 = cast(13.000 * 1000 as Integer)

HTH,
Set

[firebird-support] Re: Firebird 2.5x on Windows 2012 with BitLocker Encrypted HDD, RAID 1

[map...@gmail.com [firebird-support]]

Encryption is at volume level and done by a TPM chip so the overhead should be 
low  

 here are some numbers for io 
 

 Developers: How does BitLocker affect performance? 
http://stackoverflow.com/questions/2762844/developers-how-does-bitlocker-affect-performance
 
 
 
http://stackoverflow.com/questions/2762844/developers-how-does-bitlocker-affect-performance
 
 
 Developers: How does BitLocker affect performa... 
http://stackoverflow.com/questions/2762844/developers-how-does-bitlocker-affect-performance
 I'm an ASP.NET / C# developer. I use VS2010 all the time. I am thinking of 
enabling BitLocker on my laptop to protect the contents, but I am concerne...
 
 
 
 View on stackoverflow.com 
http://stackoverflow.com/questions/2762844/developers-how-does-bitlocker-affect-performance
 
 Preview by Yahoo 
 
 
  

 

 


 
 

 

RE: [firebird-support] Parametrized queries and execute statement - dynamic parameter list

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

>3. when i change proc to use execute statement with parameters
> 
>SET TERM ^ ;
>CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER)
>AS
>DECLARE VARIABLE VAR_SQL VARCHAR(1000);
>BEGIN
>  VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID';
>  EXECUTE STATEMENT (VAR_SQL) (ID := :ID) INTO :SUM_AAA;
>  SUSPEND;
>END^
>SET TERM ; ^
> 
>and run query
>SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC_EXEC_PARAM(T.FIELD_ID) P) FROM 
>TABLEX T
> 
>than select is prepared only once - ok :)

I'm impressed if Firebird understands that VAR_SQL doesn't change between each 
iteration so that preparing once is possible!

>4. but how to change this sample code to use paramaters and do not prepare 
>statement on every record?
> 
>SET TERM ^ ;
>CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER)
>AS
>DECLARE VARIABLE VAR_SQL VARCHAR(1000);
>DECLARE VARIABLE VAR_BBB INTEGER;
>DECLARE VARIABLE VAR_NR INTEGER;
>BEGIN
>  VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID ';
>  VAR_NR = 1;
>  FOR SELECT 
>  BBB 
>  FROM 
>  TABLEZ 
>  WHERE ID=:ID
>  INTO :VAR_BBB
>  DO
>    BEGIN  
>   /* DO SOME CALCULATION ON VAR_BBB */
>   
>   VAR_SQL = VAR_SQL || ' OR ID=:ID' || VAR_NR; 
>        How to add here parameter to list of parameters for below execute 
>statement?
>    END
> 
>  EXECUTE STATEMENT (VAR_SQL) (ID := :ID,  ID1, ID2, ID3 ...) <- How to 
>specify parameters dynamically?
>  INTO :SUM_AAA;
>  
>  SUSPEND;
>END^
>SET TERM ; ^
> 
> 
>Is this somehow possible? Or should i add this to the Firebird bug tracker as 
>new feature request?
>This is important from performance point of view.
 
Here you change the SQL for each iteration, hence I cannot see how it would be 
possible to prepare only once. What you could try, is to use a temporary table, 
change your statement to:

SELECT SUM(Y.AAA) FROM TABLEY Y
JOIN TMP_TABLE T ON Y.ID = T.ID

and in your loop do

INSERT INTO TMP_TABLE(ID) VALUES(:VAR_NR)

HTH,
Set

Re: [firebird-support] How to CAST float to integer with error?

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
 
this is simple with "case" or "iif"
 
this run ok
SELECT CASE WHEN CAST('123.00' AS INTEGER)<>CAST('123.00' AS NUMERIC(18, 3)) 
THEN CAST('PRECISION LOST' AS INTEGER) ELSE CAST('123.456' AS INTEGER) END FROM 
RDB$DATABASE
 
this run with an error "conversion error from string "PRECISION LOST FOR 
123.456""
 
SELECT CASE WHEN CAST('123.456' AS INTEGER)<>CAST('123.456' AS NUMERIC(18, 3)) 
THEN CAST('PRECISION LOST FOR 123.456' AS INTEGER) ELSE CAST('123.456' AS 
INTEGER) END FROM RDB$DATABASE
 
regards,
Karol Bieniaszewski
 
 
W dniu 2015-05-25 12:04:09 użytkownik brucedickin...@wp.pl [firebird-support] 
 napisał:
 
Hello,
is it possible by using CAST or in any other way to get database error when 
casting such number to integer?
SELECT CAST('13.245' AS INTEGER) FROM RDB$DATABASE
This gives 13 but instead I would like to get an error because precision is 
lost.
However, I would like to NOT get an error when doing this:
SELECT CAST('13.000' AS INTEGER) FROM RDB$DATABASE
Becasue here precision is not lost.
Is it possible to obtain such behaviour in easy way or do I have to use regular 
expressions?
Best regards.
 

 
 

Re: RE: [firebird-support] Parametrized queries and execute statement - dynamic parameter list

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

>>Here you change the SQL for each iteration, hence I cannot see how it would 
>>be possible to prepare only once. What you could try, is to use a temporary 
>>table, change your statement to: 
 
Simple if after "iteration" query look like
 
SELECT * FROM TABLE WHERE ID=?
SELECT * FROM TABLE WHERE ID=? OR ID=?
SELECT * FROM TABLE WHERE ID=? OR ID=? OR ID=?
 
then i got limited number of possible queries e.g 3 or 5 ...
 
 
>>SELECT SUM(Y.AAA) FROM TABLEY Y 
>>JOIN TMP_TABLE T ON Y.ID = T.ID 
>>and in your loop do 
>>INSERT INTO TMP_TABLE(ID) VALUES(:VAR_NR) 
>>HTH, 
>>Set 
 
Temp table is good hint but in my reall case it is difficult because of 
changing number of columns and its types
I suppose that specify dynamic list of parameter values in current FB 
implementation is not supported or may be it is?
 
regards,
Karol Bieniaszewski
 
 

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

[Stefan Heymann li...@stefanheymann.de [firebird-support]]

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

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

Thanks. That - of course - did the trick. Always a good idea to grant
a role to a user that wants to use it ;-)

Regards

Stefan

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

[halim1...@hotmail.com [firebird-support]]

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

 Thank you,
 -Halim

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

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

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

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


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

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

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

[halim1...@hotmail.com [firebird-support]]

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

 Thank you for your help,
 -Halim
 

 

 

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

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

Halim,

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

What is the size of table vs. index data?

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

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


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

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

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

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Thomas

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

Greetings.

Walter.


On Tue, May 26, 2015 at 12:25 PM, Thomas Steinmaurer t...@iblogmanager.com
[firebird-support]  wrote:

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

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

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

Hello Walter,

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

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

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

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

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


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

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


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

Re: [firebird-support] Advice requested on design pattern

[Mike Ro miker...@gmail.com [firebird-support]]

On 20/05/15 21:15, Alexandre Benson Smith ibl...@thorsoftware.com.br 
[firebird-support] wrote:



In this way wouldn't the SP have the same problem that was mentioned 
by Louis, namely that only the index from the first table in the 
query would be used?




I did not read it in full details...

But the case of select on views be able to use the index on the first 
table I believe that's because of using of outer joins, with inner 
joins that would not be the case...



I am very sorry for my slow response, but thank you for explaining this.

I don't know anything about how Firebird treats indexes in selectable 
SP, so I have some learning to do in this area.

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

[Hugo Eyng hugoe...@msn.com [firebird-support]]

Hello Thomas.

I saw Dimtry´s comment.

About the DEFAULT CLAUSE, even provided, it acts only on new rows and 
not on preexisting rows. Is this true?


Hugo


On 26/05/2015 02:53, 'Thomas Steinmaurer' t...@iblogmanager.com 
[firebird-support] wrote:


Hugo,

> Hello.
>
> 1 - I created a column NOT NUL in a table (ALTER TABLE MYTABLE ADD
> NEW_COLUMN INTEGER NOT NULL). There were already some records in the
> table. FB didn´t raise any warning or exception.
> 2 - I created a backup file from MYDB.FDB using gbak. FB didn´t raise
> any warning or exception.
> 3- I tried to restore the backup file to a new MYDB.FDB and FB raises
> and error: gbak: ERROR:validation error for column NAME, value "*** 
null

> ***"
>
> Let´s accept than the step 3 is coherent.
>
> So, why can I execute step 1 without troubles?
>
> Well, someone can tell me to use -no_validity to restore the database.
> Ok, it works. But, the column that should be NOT NULL will change,
> without warning, and I have to check my tables after restoring DB.
>
> I am using FB 2.5 and I tried using FB 3.0 too.
>
> Is it as I described or there is another way to treat this?

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

See Dmitry's InterBase legacy comment.

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


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

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




--


Atenciosamente,

Hugo Eyng

Re: [firebird-support] Advice requested on design pattern

[Mike Ro miker...@gmail.com [firebird-support]]

Once again thank you. I am very sorry it has taken me so long to reply.

On 20/05/15 21:19, Andrea Raimondi andrea.raimo...@gmail.com 
[firebird-support] wrote:


1) Re the separate database: nobody said you shouild do it from your 
own. I'd do it in the client, once the original data has been inserted.
If I understand correctly it will become basically a logging database. 
That could be fine as I have a data access layer anyway but I would need 
to make sure that a audit log is only written if a transaction is 
successfully committed and not rolled back.

3) How many blobs *per user* are you going to have?

Maximum 1000, typically 50 - 100.
6) The only real remaining problem is encryptuion - if you want that 
for some of your data. Provided that I would strongly discouragre that
especially on Firebird because of the eaae of peeking inside the 
DB which equates roughly to no ecryption, I would go for something
slightly off the ordinary: what you can do is encrypt your data in 
the app and then apply it to your tables. There are several strategies you
could employ, but I think you first need to determine whether 
that's what you really want.


Up until now I wasn't really worried about encrypting the data, however 
the more that I look into this the more it seems like an almost necessity.


If I understand correctly the downside of encrypting data in the app is 
obviously that Firebird can't index the contents. I have been looking at 
Firebird 3 in this regard and it seems to be possible, at least to 
include reasonably obfuscated data using a closed source (my own or 
third party) plug-in.


Thank you again for your advice!

[firebird-support] New to firebird Mac and lost

[johnpayton...@btinternet.com [firebird-support]]

Have downloaded Firebird SS2.5.4 and new user was set up but can't access any 
commands from either my or Firebird user.
 have tried as suggested
 export FIREBIRD_HOME=/Library/FRameworks/Firebird.framework/resources
 export PATH$PATH:$FIREBIRD_HOME/bin
 I have used Pascal and Microsoft Access before but no unix experience before I 
converted from Windows to Mac.
 Have set up Lazarus and getting to know that.
 Any simple suggestions to get me started would be welcome
 John

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

[Hugo Eyng hugoe...@msn.com [firebird-support]]

Hello Dimitry.

I thought I was testing in FB 3.0, but I wasn´t. I am sorry.

I tried FB 3.0 now and _*it really raises*_ and error at step 1. Then I 
use DEFAULT clause and it works like I expected.


Thanks for your answer.

Hugo


On 26/05/2015 03:08, Dmitry Yemanov dim...@users.sourceforge.net 
[firebird-support] wrote:

25.05.2015 17:43, Hugo Eyng hugoe...@msn.com wrote:

1 - I created a column NOT NUL in a table (ALTER TABLE MYTABLE ADD
NEW_COLUMN INTEGER NOT NULL). There were already some records in the
table. FB didn´t raise any warning or exception.
2 - I created a backup file from MYDB.FDB using gbak.  FB didn´t raise
any warning or exception.
3- I tried to restore the backup file to a new MYDB.FDB and FB raises
and error: gbak: ERROR:validation error for column NAME, value "*** null
***"

Let´s accept than the step 3 is coherent.

So, why can I execute step 1 without troubles?

I am using FB 2.5 and I tried using FB 3.0 too.

Did you really try FB 3.0? It should raise an error at step 1.


Dmitry








++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links







--


Atenciosamente,

Hugo Eyng

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

[Hugo Eyng hugoe...@msn.com [firebird-support]]

Hello Thomas.

Please see my answer to Dimitry.

Thanks for your answer.

Hugo


On 26/05/2015 02:53, 'Thomas Steinmaurer' t...@iblogmanager.com 
[firebird-support] wrote:


Hugo,

> Hello.
>
> 1 - I created a column NOT NUL in a table (ALTER TABLE MYTABLE ADD
> NEW_COLUMN INTEGER NOT NULL). There were already some records in the
> table. FB didn´t raise any warning or exception.
> 2 - I created a backup file from MYDB.FDB using gbak. FB didn´t raise
> any warning or exception.
> 3- I tried to restore the backup file to a new MYDB.FDB and FB raises
> and error: gbak: ERROR:validation error for column NAME, value "*** 
null

> ***"
>
> Let´s accept than the step 3 is coherent.
>
> So, why can I execute step 1 without troubles?
>
> Well, someone can tell me to use -no_validity to restore the database.
> Ok, it works. But, the column that should be NOT NULL will change,
> without warning, and I have to check my tables after restoring DB.
>
> I am using FB 2.5 and I tried using FB 3.0 too.
>
> Is it as I described or there is another way to treat this?

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

See Dmitry's InterBase legacy comment.

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


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

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




--


Atenciosamente,

Hugo Eyng

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

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much, Thomas. Very useful.

Greetings.

Walter.


On Tue, May 26, 2015 at 1:02 PM, Thomas Steinmaurer t...@iblogmanager.com
[firebird-support]  wrote:

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

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

[halim1...@hotmail.com [firebird-support]]

Thank you for sharing this knowledge with us. Very good things to learn. 

 -Halim

Re: [firebird-support] New to firebird Mac and lost

[Helen Borrie hele...@iinet.net.au [firebird-support]]

At 04:47 a.m. 27/05/2015, johnpayton...@btinternet.com [firebird-support] wrote:


>Have downloaded Firebird SS2.5.4 and new user was set up but can't access any 
>commands from either my or Firebird user.

Neither will give you access to a Firebird server.  To get you started, you 
need a client application, e.g., isql, a database (to keep it simple) and a 
user name and password that are known to the Firebird server.


>have tried as suggested
>
>export FIREBIRD_HOME=/Library/FRameworks/Firebird.framework/resources
>
>export PATH$PATH:$FIREBIRD_HOME/bin

The Mac build is done according to Apple rules.  I don't have a Mac so I'm 
guessing locations based on the standard POSIX setup.

So, go to /Library/Frameworks/Firebird.framework/resources and see whether 
there is a file there called SYSDBA.password.  
-- if so, open that file using a text editor and copy the password that was 
generated for the SYSDBA during installation
-- if the file is not there, then my guess is that the Mac build has simply 
provided the world-known password for the SYSDBA, masterke

There is a sample database in $FIREBIRD_HOME/examples/empbuild/EMPLOYEE.FDB

To avoid volumes of typing in the shell, go to 
/Library/Frameworks/Firebird.framework/resources and open aliases.conf in your 
text editor.
Create an alias for the employee database as follows:
emp = 
/Library/Frameworks/Firebird.framework/resources/examples/empbuild/employee.fdb
and press the Enter (Return?) key once.  Save the file.

To log in to the Fb server and open this database, go to $FIREBIRD_HOME/bin and 
type
./isql localhost:emp -user SYSDBA -password whatever
("whatever" being either the password you copied from the file, or masterke).  
Passwords are case-sensitive.

Keep watching, as someone is likely to chip in with more exact info about the 
locations of things in the Mac installation.

>I have used Pascal and Microsoft Access before but no unix experience before I 
>converted from Windows to Mac.

Firebird is nothing like Microsoft Access.  Tools excepted, you write your own 
user interface for Firebird, in your programming language of choice and 
communicate with the database server through the API.  You'll need an 
appropriate language driver (wrappers for the API functions) and a knowledge of 
standard SQL.  BTW, Access's pseudo-SQL won't work in Firebird.

>Have set up Lazarus and getting to know that.

Probably wise to join the Lazarus boards and find out what people are using as 
a Firebird driver for your preferred language.


>Any simple suggestions to get me started would be welcome

The most obvious starting point would be to study the document 
Firebird-2.5-QuickStart.pdf, which should be in 
/Library/Frameworks/Firebird.framework/resources/doc.  If it isn't there, you 
can download a copy from http://www.firebirdsql.org/en/reference-manuals/ or 
read it there online.

You might also find this useful, if you prefer a graphical interface tool:
http://sourceforge.net/projects/flamerobin/files/flamerobin/0.9.2/flamerobin-0.9.2-osx.dmg.gz/download


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__ 

[firebird-support] Re: How to CAST float to integer with error?

[brucedickin...@wp.pl [firebird-support]]

Thank you guys for your ideas, I will look onto them.

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

['liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support]]

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


yes, Interbase XE7 have this and restore speed is 2 times faster then in XE3 
but it is still 2 times slower then restore time on FB3 ;)

We have 5GB database and times looks like with the same restore settings 
buffers and others settings, and the same db structure and data:

IB XE3 - 93 minutes
IB XE7 - 45 minutes
FB 3- 26 minutes

Regards,
Karol Bieniaszewski


- Reply message -
Od: "Thomas Steinmaurer t...@iblogmanager.com [firebird-support]" 

Do: 
Temat: [firebird-support] Database restore speed with IBExpert and Gbak
Data: wt., maj 26, 2015 19:02
Hello Walter,



> Hello Thomas

>

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



For the restore process, gbak supports a -BU(FFERS) switch to override 

the database page buffer value. While page buffers tends to be rather 

small for Classic/SuperClassic hosted databases, you could try to 

increase that value by up to a factor of 100 through the -BU switch for 

the restore process.



This gives the restore connection a much higher Firebird page cache. But 

this setting is persisted in the header page after the restore, thus 

before going back to production, you have to reset to the original value.



I can't recall my exact test results from the past. There was also some 

sort of sweet spot where further increasing didn't help anymore, so run 

your own tests before applying that in your environment.



Possible further enhancements during index re-creation would be to 

re-create several indexes in parallel becoming more and more IO bound, 

especially with low latency storage. AFAIK InterBase added something 

like that in a recent version. Potentially Firebird has that on the 

roadmap as well.



-- 

With regards,

Thomas Steinmaurer

http://www.upscene.com/



Professional Tools and Services for Firebird

FB TraceManager, IB LogManager, Database Health Check, Tuning etc.



> Greetings.

>

> Walter.

>

>

> On Tue, May 26, 2015 at 12:25 PM, Thomas Steinmaurer t...@iblogmanager.com

>  [firebird-support]

>  > wrote:

>

> __

>

> Halim,

>

> > Thank you for your reply.

> > I just tested a GBAK restore using -se(rvice) switch on a 1 GB DB. It

> > took about 8 minutes. Restoring the same database using IBExpert took

> > about 3 minutes.

> > I'm looking for a faster restore time because I want to automate the

> > process using a batch file. Our DB is over 50 GB.

>

> What is the size of table vs. index data?

>

> Restore is basically limited by single core throughput and I've hardly

> seen restore being IO bound.

>

> What you could try is to provide a much larger (temporary) page buffers

> value (which you have to reduce before the restored database is

> going to

> be used in production!) during the restore, which might help during

> index re-creation.

>

> --

> With regards,

> Thomas Steinmaurer

> http://www.upscene.com/

>

> Professional Tools and Services for Firebird

> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

>

>

>

>

> 

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

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

Hello Karol,

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

>From a throughput perspective, this would mean:

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

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


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

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



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