Re: [firebird-support] RECREATE Procedure issue.

[Joje j...@codework-solutions.com [firebird-support]]

I am executing this query using my .net application and it is not 
throwing any error. If there was a error then it would log that error 
and will display it to the user.


I executed this query in my third party SQL Manager and it throws an error
/Invalid token.//
//Dynamic SQL Error.//
//SQL error code = -104.//
//Unexpected end of command - line 30, column 36.//
//
/line number 30 has this statement "DECLARE VARIABLE URL_COUNT INTEGER;"

But the thing to note is that executing same query with ALTER will work. 
As this query was big and important in order to avoid any mistake i 
thought it was better to use RECREATE rather than ALTER. As recreate 
drops previous procedure and creates a new one.


If there is an dependency then firebird should have sent an error to 
application. Here there was no error as such.



Regarding documentation
On 17-03-2016 23:59, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] wrote:


On 2016-03-17 15:49, Joje j...@codework-solutions.com
[firebird-support] wrote:
> Hi All,
>  I am facing a issue with 'RECREATE' procedure.
>
> Problem:
> I have a stored procedure that uses 'RECREATE Procedure', this
> procedure does not execute properly when executed in EMS SQL manager
> for IB and Firebird.
> Dont know why... :( every time i execute it. Procedure execution
> stuck as declaration variable whenever using 'RECREATE',

Could you explain what happens: do you get an error, does it hang, or
does it do nothing?

> however when
> I use 'ALTER' procedure gets executed without any error. There is no
> documentation provided in firebird site about 'RECREATE'

But there is:
http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-procedure.html#fblangref25-ddl-proc-recreate

> Could some tell me whether they have faced issues with 'RECREATE' or
> have fixed this kind of issue.
>
> Thanks in Advance would appreciate your help in this matter.

You might want to use CREATE OR ALTER PROCEDURE instead
http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-procedure.html#fblangref25-ddl-proc-crtoralter

The difference is that recreate drops and then creates the procedure,
while CREATE OR ALTER creates a procedure if it doesn't exist or
otherwise alters the procedure.

Mark

Re: [firebird-support] RECREATE Procedure issue.

[Joje j...@codework-solutions.com [firebird-support]]

I am using firebird 2.5.5 version. As my application is .net application 
I am using Firebirdclient.dll to interact with firebird database. Also I 
am executing the query inside my .net application.
There are many queries in my .net application, only this 'RECREATE' 
query fails to execute while others get executed successfully.


Later when I ran this query in my third party SQL Manager it failed to 
execute in it.

 The error shown in SQL manager was
"Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Unexpected end of command - line 30, column 36."

at declaration statement _"DECLARE VARIABLE COUNT INTEGER;"_


NOTE: When same query executed with 'ALTER', it got executed successfully.



On 17-03-2016 23:48, Helen Borrie hele...@iinet.net.au 
[firebird-support] wrote:
1) You are using a very old version of Firebird that does not support 
RECREATE PROCEDURE 

[firebird-support] RECREATE Procedure issue.

[Joje j...@codework-solutions.com [firebird-support]]

Hi All,
  I am facing a issue with 'RECREATE' procedure.


Problem:
I have a stored procedure that uses '*RECREATE *Procedure', this 
procedure does not execute properly when executed in EMS SQL manager for 
IB and Firebird.
Dont know why... :( every time i execute it. Procedure execution 
stuck as declaration variable whenever using '*RECREATE*', however when 
I use '*ALTER*' procedure gets executed without any error. There is no 
documentation provided in firebird site about 'RECREATE'


Could some tell me whether they have faced issues with 'RECREATE' or 
have fixed this kind of issue.


Thanks in Advance would appreciate your help in this matter.

[firebird-support] Block size exceeds implementation restriction exception for varchar(31000)

['Joje' j...@codework-solutions.com [firebird-support]]

Hi All,

 

I want to alter an existing stored procedure with varchar parameters having
length of 31000. Whenever I try to execute this query I get an exception of 

"Invalid token.

Invalid request BLR at offset 102.

Implementation limit exceeded.

Block size exceeds implementation restriction."

 

How to remove this restriction..is there any way ?

 

 

 

RE: [firebird-support] Re: Block size exceeds implementation restriction exception for varchar(31000)

['Joje' j...@codework-solutions.com [firebird-support]]

Thanx Jez.

 

I will have to modify the stored procedure.

 

Thank you once again. 

 

 

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 08 September 2016 16:41
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Block size exceeds implementation
restriction exception for varchar(31000)

 

  

Firebird has limitation on both IN clauses and parameter lists for stored
procedures.

Going down the route of trying to get around this can then cause errors with
the command text limit.

 

It is always best to redesign your stored procedure or query to join on a
table that has the particular parameters or ID stored. This is also much
faster.

 

Jez

[firebird-support] Problem in executing query using 'IN' statement.

['Joje' j...@codework-solutions.com [firebird-support]]

Hi All,

 

I have a query in which I check whether an NAME _IDS of type varchar is in
another table with NAME _ID column having data type as int.   Also, ID_1
have ID's separated by comma's example '1,2,3,4,5'.

 

Below is the query which I am trying to execute 

 

SELECT  Tb2. _ID,Tb1.DET_ID,COUNT(Tb3. _Name) NAME_COUNT 

  FROM  TABLE_1 Tb1  

  JOIN  TABLE_2 Tb2 ON Tb2.DET_ID= Tb1.DET_ID 

  JOIN  Table_3 Tb3 ON Tb3. _ID= Tb1. _ID 

 AND CAST(Tb3.Time AS DATE)=CURRENT_DATE   

 AND Tb3.NAME _IDS IN  (CAST(Tb2.NAME_ID AS VARCHAR(250))) /*Here
Tb3.NAME _IDS is varchar & Tb2.NAME _IDS is int so casted into varchar. This
line gives wrong output */

  GROUP BY Tb1.DET_ID, Tb1. NAME _ID, Tb1. _ID, Tb3._NAME

NOTE: Tb3.NAME _IDS could have multiple ids in form '1,2,23,15'

 

Problem I am facing is that when Tb3.NAME _IDS contains multiple IDs in it
then result is not shown leading to wrong result. Is there any solution to
solve this query. I tried using 'STARTING WITH', 'CONTAINING','LIKE'  but no
output.

RE: [firebird-support] Problem in executing query using 'IN' statement.

['Joje' j...@codework-solutions.com [firebird-support]]

 

Hi Setysvar,

 

Thank you for helping out. But I am unable to run it on my "SQL manager" on
executing query I am getting 'Invalid Token' error. Below is error that is
shown

  Invalid token.

Dynamic SQL Error.

SQL error code = -104.

Token unknown - line 5, column 61.

','.

 

Also, could you tell me how does ','||ColName ||',' syntax works. I am
seeing it for the first time. 

Thank you. 

 

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 16 September 2016 22:23
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Problem in executing query using 'IN'
statement.

 

  

Den 16.09.2016 15:34, skrev 'Joje' j...@codework-solutions.com
  [firebird-support]:

I have a query in which I check whether an NAME _IDS of type varchar is in
another table with NAME _ID column having data type as int.   Also, ID_1
have ID's separated by comma's example '1,2,3,4,5'.

Below is the query which I am trying to execute 

SELECT  Tb2. _ID,Tb1.DET_ID,COUNT(Tb3. _Name) NAME_COUNT 

  FROM  TABLE_1 Tb1  

  JOIN  TABLE_2 Tb2 ON Tb2.DET_ID= Tb1.DET_ID 

  JOIN  Table_3 Tb3 ON Tb3. _ID= Tb1. _ID 

 AND CAST(Tb3.Time AS DATE)=CURRENT_DATE   

 AND Tb3.NAME _IDS IN  (CAST(Tb2.NAME_ID AS VARCHAR(250))) /*Here
Tb3.NAME _IDS is varchar & Tb2.NAME _IDS is int so casted into varchar. This
line gives wrong output */

  GROUP BY Tb1.DET_ID, Tb1. NAME _ID, Tb1. _ID, Tb3._NAME

NOTE: Tb3.NAME _IDS could have multiple ids in form '1,2,23,15'

 Problem I am facing is that when Tb3.NAME _IDS contains multiple IDs in it
then result is not shown leading to wrong result. Is there any solution to
solve this query. I tried using 'STARTING WITH', 'CONTAINING','LIKE'  but no
output.

SELECT  Tb2._ID, Tb1.DET_ID, COUNT(Tb3._Name) NAME_COUNT 
/* It always confuses me when I see things like COUNT(Tb3._Name), myself I
always use either COUNT(*) or COUNT(DISTINCT Tb3._Name), though I think your
syntax is correct if you want to do the equivalent of COUNT(*), but skip
nulls */
FROM  TABLE_1 Tb1  
JOIN  TABLE_2 Tb2 ON Tb2.DET_ID = Tb1.DET_ID
JOIN  Table_3 Tb3 ON Tb3._ID= Tb1._ID
  AND CAST(Tb3.Time AS DATE)= CURRENT_DATE   
  AND ','||Tb2.NAME_ID||',' in ','||Tb3.NAME_IDS||',' 
/* Tb2 and Tb3 the other way around, and add commas to make sure things
matches exactly (i.e. match even if the first or last in NAME_IDS and not
match 1 to 11 etc) */
GROUP BY Tb2._ID, Tb1.DET_ID 
/*It makes no sense to group by anything but the non-grouped fields that you
select, most likely you would get an error with your original GROUP BY*/

HTH,
Set

[firebird-support] Unable to execute large text inside stored procedures.

['Joje' j...@codework-solutions.com [firebird-support]]

Hello,

 

I have a stored procedure that takes  a parameter(a1) that has  length
excess of 25k. Inside this stored procedure I have declared a variable with
a query , when this parameter(a1) combined with variable total length
exceeds 32k and  FB returns as error of concatenation. 

 

Below is error returned by stored procedure 

   "Concatenation overflow. Resulting string cannot exceed 32K in length."

 

I used BLOB SUB_TYPE 1 for parameter(a1) and variable in stored procedure.
Then also I am getting this same error.

 

Thank you. 

[firebird-support] Unable to upgrade database to firebird 3.0

['Joje' j...@codework-solutions.com [firebird-support]]

Hello All,

   Recently I upgraded my Firebird from 2.5 to 3.0 and I am using 'EMS
SQL Manager Interbase/Firebird'  to connect and work with database.  Now
whenever I connect existing database or register a new database I get
following error 

 Unsuccessful execution caused by system error that does not preclude
successful execution of subsequent statements.

Error loading plugin Engine12.

Module C:\Program Files\Firebird\Firebird_3_0\plugins/Engine12 exists but
cannot be loaded.

 

SQL Code: -901

IB Error Number: 335545004

 

   I visited EMS website and found there latest version supports
upto Firebird 2.5 . Could someone suggest another method to connect to
database. 

 

Thanks in advance.

 

With Regards,

Joje T. George

 

 

RE: [firebird-support] Unable to upgrade database to firebird 3.0

['Joje' j...@codework-solutions.com [firebird-support]]

Thanks it worked now I am able to connect to database. And I have upgraded to 
FB 3.0.1 !!

Another thing  did anyone faced ODS version issue. Like I got this issue of 
database ODS ver. 11.5 is lower than 

Current ODS ver 12.0. If so then how could we solve this problem ?

 

Thank you.

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 26 October 2016 19:16
To: firebird-support@yahoogroups.com
Subject: AW: [firebird-support] Unable to upgrade database to firebird 3.0

 

  

I forgot.. may you have also to update to version 3.0.1!

 

Von: firebird-support@yahoogroups.com  
 [mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 26. Oktober 2016 15:33
An: firebird-support@yahoogroups.com  
Betreff: [firebird-support] Unable to upgrade database to firebird 3.0

 

  

Hello All,

   Recently I upgraded my Firebird from 2.5 to 3.0 and I am using ‘EMS SQL 
Manager Interbase/Firebird’  to connect and work with database.  Now whenever I 
connect existing database or register a new database I get following error 

 Unsuccessful execution caused by system error that does not preclude 
successful execution of subsequent statements.

Error loading plugin Engine12.

Module C:\Program Files\Firebird\Firebird_3_0\plugins/Engine12 exists but 
cannot be loaded.

 

SQL Code: -901

IB Error Number: 335545004

 

   I visited EMS website and found there latest version supports 
upto Firebird 2.5 . Could someone suggest another method to connect to 
database. 

 

Thanks in advance.

 

With Regards,

Joje T. George

 

 

[firebird-support] Unable to fine registry entry of Firebird 3.0

['Joje' j...@codework-solutions.com [firebird-support]]

Hi All,

 

I am unable to find registry entry of firebird 3.0. In previous versions it
was easy to find registry entry of firebird but now I am not able to see it
in registry. 
 

   The reason of looking for registry entry is I want to know the path where
firebird is installed. 

 

Thank you. 

RE: [firebird-support] Unable to fine registry entry of Firebird 3.0

['Joje' j...@codework-solutions.com [firebird-support]]

Thanks got it..!


Forgot to use 'instreg.exe -I' in command line :P :P :P 




 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 31 October 2016 14:36
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Unable to fine registry entry of Firebird 3.0

 

  

Hi All,

 

I am unable to find registry entry of firebird 3.0. In previous versions it
was easy to find registry entry of firebird but now I am not able to see it
in registry. 
 

   The reason of looking for registry entry is I want to know the path where
firebird is installed. 

 

Thank you. 





[Non-text portions of this message have been removed]

RE: [firebird-support] Re: Fwd: Firebird database corrupt issue. Due to I/O error

['Joje' j...@codework-solutions.com [firebird-support]]

Thanks. 

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Sunday, February 19, 2017 1:48 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Fwd: Firebird database corrupt issue. Due to 
I/O error

 

  

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

 

> I am facing a DB corruption issue on firebird database. Everytime my 
> application tries to open connection or perform any DML activities, 
> randomly i get the following error.

  So, everytime or randomly ?

> ERR::I/O error during "CreateFile (open)" operation for file "database"

> Error while trying to open file; FirebirdSql.Data.FirebirdClient: 

 - The error shown above is coming from my .NET application whenever it tries 
to open the FB database.  My application uses ‘FirebirdSql.Data.FirebirdClient’ 
to

communicate with Firebird.  Is there way to check error logs from Firebird 
server?

  This is a part of error message, provide full text, everytime (not randomly)

> I seeing this issue more frequently now a days. Tried this same database on 
> another pc still getting this error.

> Could someone help me.

  Sure. If\when you provide full description of what happens. Including 
Firebird version and OS name for start.

 

-  Thank you :). Firebird 2.5.4 version, OS is windows 8.1 64-bit.  

 



Regards,
Vlad

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

['Joje' j...@codework-solutions.com [firebird-support]]

Hello,

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 ?  

 

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

 

 

Thanks in advance. 

 

With Regards,

Joje 

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

['Joje' j...@codework-solutions.com [firebird-support]]

Hi martin, 

 

Thanks for help but firebird database is in Windows server 2008 OS.  

 

 

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 18 April 2017 16:54
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Deletion of rows from multiple tables takes a
lot of time around 30mins

 

  

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

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

['Joje' j...@codework-solutions.com [firebird-support]]

Hi Karol,

 

I will look into how the delete query plan works on customer environment and 
will revert back. 

 

Thanks 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 18 April 2017 20:16
To: firebird-support@yahoogroups.com
Subject: Odp: [firebird-support] Deletion of rows from multiple tables takes a 
lot of time around 30mins

 

  

Hi,

For me 5 minutes looks also very long.
Look how delete query plan looks like

Regards,
Karol Bieniaszewski

- Reply message -
Od: "'Joje' j...@codework-solutions.com 
  [firebird-support]" 
mailto:firebird-support@yahoogroups.com> >
Do: mailto:firebird-support@yahoogroups.com> 
>
Temat: [firebird-support] Deletion of rows from multiple tables takes a lot of 
time around 30mins
Data: wt., kwi 18, 2017 08:17


  

Hello,

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 ?  

  /o>

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

 

 

Thanks in advance. 

 

With Regards,

Joje 

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

['Joje' j...@codework-solutions.com [firebird-support]]

Hi Sean,

 

I will look into it and will revert-back with additional details. 

 

Thanks.

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 18 April 2017 20:13
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Deletion of rows from multiple tables takes
a lot of time around 30mins

 

  



> 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] Deletion of rows from multiple tables takes a lot of time around 30mins

['Joje' j...@codework-solutions.com [firebird-support]]

Hi All, 

 

At last found the root cause for this issue basically it was database
design issue where DBA used delete triggers in one of the tables let’s say
‘TableA’ that deletes it related rows from another table ‘TableB’. Another
thing is are both are transaction tables containing large number of rows. 

 

TableA had 4,073 rows that where to be deleted, for each row deleted in
TableA delete trigger was fired to  TableB.  This logic was ok until I found
that foreign key ID used for linking ‘TableA’ with ‘TableB’ is varchar
column. 

 

Then I tested the time duration taken for fetching results on TableB

1.   Queried on integer column of TableB -> Got result in 1ms

2.   Queried on varchar column of TableB -> Got result in 2secs.

 

For testing I removed delete trigger from TableA, called delete stored
procedure, got result in 16ms as compared to 30-40mins.  That’s a dramatic
improvement. 

 

Since, this DB is already live I am planning to remove this trigger and
create stored procedures that will delete from TableB. 

 

Earlier, the DB that I tested this same didn’t had any rows in TableA.
Hence, deletion was working fast.

 

Thanks for your help.

 

With regards, 

Joje T. George

 

 

 

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 19 April 2017 10:18
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Deletion of rows from multiple tables takes
a lot of time around 30mins

 

  

Hi Karol,

 

I will look into how the delete query plan works on customer environment
and will revert back. 

 

Thanks 

 

From: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com] 
Sent: 18 April 2017 20: 16
To: firebird-support@yahoogroups.com
 
Subject: Odp: [firebird-support] Deletion of rows from multiple tables
takes a lot of time around 30mins

 

  

Hi,

For me 5 minutes looks also very long.
Look how delete query plan looks like

Regards,
Karol Bieniaszewski

- Reply message -
Od: "'Joje' j...@codework-solutions.com
  [firebird-support]"
mailto:firebird-support@yahoogroups.com>
>
Do: mailto:firebird-support@yahoogroups.com> >
Temat: [firebird-support] Deletion of rows from multiple tables takes a lot
of time around 30mins
Data: wt., kwi 18, 2017 08:17


  

Hello,

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 ?  

  /o>

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

 

 

Thanks in advance. 

 

With Regards,

Joje 





[Non-text portions of this message have been removed]

[firebird-support] Performing bulk update taking time.

['Joje' j...@codework-solutions.com [firebird-support]]

Hello everyone,

 

Is there anyway using which we could perform bulk update in firebird. 

 I have two tables A & B. Both tables are referenced by a varchar(100) AR_ID
column.  

-  Total number of rows in each table is around 1 million. Now in
order to get faster result I created a bigint column ref_A_ID in Table B
that is referenced to A_ID column of table A.

-  Now, to reference existing data in table B. I want to update
column ref_A_ID of table B with value of A_ID of table A for proper linking
to table A.

-  I created a query for updating table B is as below 

 

-  EXECUTE BLOCK 

-   AS 

-   DECLARE VARIABLE REF_A_ID BIGINT;

-   DECLARE VARIABLE AR_ID VARCHAR(200);

-   DECLARE VARIABLE _ID BIGINT;

-   

-   BEGIN

-   FORSELECT   A. AR_ID,   A.A_ID,   A. _ID  

-FROM  TABLE  A 

-INNER  JOIN   TABLE  B   ON   B. AR_ID   =  A.
AR_ID

-INTO :AR_ID,: REF_A_ID,:_ID 

-  DO 

-BEGIN

-  UPDATE   TABLEB 

-  SETB. REF_A_ID=: REF_A_ID 

-  WHERE   ( B. REF_A_IDISNULL  AND  B. _ID
= :_ID)  AND   (B. AR_ID = :AR_ID);

-END 

-   END

 

-  Is there a way through which this query can be further optimised
as this query taking time for completion more than 3 -4 hours.  Could I use
batch based update in this query.?

Thanks. 

 

 

With Regards,

Joje 

 

  

[firebird-support] Deadlock error from firebird during midnight

['Joje' j...@codework-solutions.com [firebird-support]]

Hi All,

 

During midnight my service performs a calculation on firebird database. But
I am receiving errors while fetching data from firebird database 

1.   "ERR::-2147467259: Deadlock concurrent transaction number is
5744401"

2.   ERR::Error reading data from the connection.;
FirebirdSql.Data.FirebirdClient

3.   ERR::Timeout exceeded.; FirebirdSql.Data.FirebirdClient:  

ERR::-2146233079: Connection must be valid and open

   at FirebirdSql.Data.FirebirdClient.FbCommand.CheckCommand()

   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteScalar()

 

Interesting part is this error occurs only at midnight from 12 to 2 AM.
Thereafter, everything goes smoothly without any error. I tried replicating
this on my test machine but no success. 

 

On analysing the pattern I think at midnight some CPU intensive applications
like (Machine backup or AV scanning etc.) might be running on server
machine. 

 

I would like to know does any CPU intensive application running on FB
installed machine can cause above errors while interacting with FB database.

 

Thanks.

RE: [firebird-support] Deadlock error from firebird during midnight

['Joje' j...@codework-solutions.com [firebird-support]]

Hi All,

 

In my case there is no large data to be analysed, only a timer that checks 
whether it is midnight or not. If it is midnight then update a single row in 
database this is done using stored procedure. 

 

Confusing part is below error occurs only between midnight that is from 12 to 2 
AM. During this time stored procedure executed will return deadlock error or 
timeout exception.

 

1.   “ERR::-2147467259: Deadlock concurrent transaction number is 5744401”

2.   ERR::Error reading data from the connection.; 
FirebirdSql.Data.FirebirdClient

3.   ERR::Timeout exceeded.; FirebirdSql.Data.FirebirdClient:  

ERR::-2146233079: Connection must be valid and open

   at FirebirdSql.Data.FirebirdClient.FbCommand.CheckCommand()

   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteScalar()

 

I don’t think it has to do anything with FB as it this same stored procedures 
work fine during day time without any issues. 

 

Thank you. 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 03 December 2017 06:08 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Deadlock error from firebird during midnight

 

  

 

 

On Sun, Dec 3, 2017 at 5:04 PM, DougC d...@moosemail.net 
  [firebird-support] 
mailto:firebird-support@yahoogroups.com> > 
wrote:

  

Please, let us know what the cause ended up being and how you resolved the 
problem!

 

Alright, I will explain the issue in detail.

1. I have a .NET application of mine deployed since about last 7 yrs with about 
90 tables in the firebird database.  Very routinely, as per customer’s 
requirement, I need to carry out changes to database structure [adding / 
renaming / removing – field(s) OR table(s)]. After doing such updates to my 
database structure I copy data to the new design database, through a .NET 
application.

2. Recently after about 7 yrs, I started to get exception “Error reading data 
from the connection” during transfer of data for a table which now had grown to 
about 13,26,000 records. For this table I was using FbDataAdapter to copy data 
in batches of 50,000 records.

3. I observed that every time the exception was raised after copying 12,50,000. 
So from the source, I deleted the already copied 12,50,000 records and resumed 
to copy the remaining 76,000. This too failed.

4. Later all my attempts as under failed:

(a) using FbDataReader in place of FbDataAdapter.

(b) changing connecting string elements like Connection Timeout, Connection 
lifetime, Pooling.

(c) Copying after backup-restore of both source and destination database.

(d) Changing machines.

 

5.  So I wrote a stored procedure with inputs from Dimitry which copied records 
using execute Statement On External database. This stored procedure I executed 
through FbCommand's ExecuteNonQuery. This worked fine.

6. Now on Dimitry’s advice I changed the FbTransaction from:

 

FbTransaction tx = con.BeginTransaction();

 

To:

 

FbTransaction tx = con.BeginTransaction(

new FbTransactionOptions() {TransactionBehavior = 

FbTransactionBehavior.Concurrency | 

FbTransactionBehavior.NoWait});

 

And found that FbDataAdapter also started to work correct.

 

7. The option at Para 5 is much faster. In case you need more details / code I 
will please share.





Regards,

Rajiv 

 

 On Sat, 02 Dec 2017 23:08:49 -0500 LtColRDSChauhan  
 rdsc1...@gmail.com [firebird-support] < 
 firebird-support@yahoogroups.com> 
wrote 

 

  Thanks a lot, Dimitry !

This issue of mine, for which I had posted for help on the 
firebird-net-provider group, is now resolved.

 

 

 

[firebird-support] Firebird database corrupt with wrong page type error on table

['Joje' j...@codework-solutions.com [firebird-support]]

Hello everyone,

 

During inserting data to a table I received an error on my application log
"database file appears corrupt (DB Path) wrong page type page 232333 is of
wrong type (expected 5, found 7)". 

 

On looking in FB log I got following  :

 

DEVMACHINE (Server)  Thu Dec 21 16:45:23 2017

Database: DB

Data page 232331 (sequence 168514) is confused in table
TABLENAME (159)

 

DEVMACHINE (Server)  Thu Dec 21 16:45:23 2017

Database: DB

Page 232333 wrong type (expected 5 encountered 7)

 

DEVMACHINE (Server)  Thu Dec 21 16:45:23 2017

Database: DB

Data page 232333 (sequence 168515) is confused in table
TABLENAME (159)

 

DEVMACHINE (Server)  Thu Dec 21 16:45:24 2017

Database: DB

Page 232317 is an orphan

 

 

This is the first time I am seeing this error and don't know what has caused
it. Is there any precaution to avoid such kind of errors..?

 

This database is around 3 years old and database maintenance activity is not
done on it.  Also, the said table has 5.4 million records in it. 

 

 

Thank you in advance :)  

[firebird-support] Auto-increment generating old ID's for insertion

['Joje' j...@codework-solutions.com [firebird-support]]

Hello,

 

I have issue where auto-increment of ID column is generating older ID's that
is already used in table. 

 

Currently, my transaction table has around 16 million rows and ID of last
inserted row is 16,594,027.  While the generator ID its 16,593,965.  As you
can see the generator is generating ID's that is already present in table
which causes violation of PRIMARY or UNIQUE KEY constraint "INTEG_105" error
on that table. 

 

I managed to fix this issue, recently I am facing it frequently on this
transaction table, majority of the tables had number of records are more
than 2 million or there is connectivity issues with DB.

 

Now, I am wondering is this related to Firebird DB or is there any other
issue. 

 

Thank you !!

RE: [firebird-support] Auto-increment generating old ID's for insertion

['Joje' j...@codework-solutions.com [firebird-support]]

Hi Mark,

 

Below is details of database properties 

 

Database header page information: 

Version:   WI-V2.5.4.26856 Firebird 2.5

Flags  0 

Checksum   12345 

Generation 941170 

Page size 4096 

ODS version   11.2 

Oldest transaction   941159 

Oldest active  941160 

Oldest snapshot   941160 

Next transaction  941161 

Bumped transaction   1 

Sequence number  0 

Next attachment ID56 

Implementation ID  26 

Shadow count   0 

Page buffers  0 

Next header page   0 

Database dialect   3 

Attributes   force write 

Variable header data: 

Sweep interval:2 

Allocated pages:   603479

 

*END*

 

On the affected table that is transaction table(‘trnTable’), I am using 
triggers to generate ID’s. Below I am pasting queries for generating the unique 
IDs 

 

Trigger that calls generator is fired on insertion:

CREATE TRIGGER 'BI_ID' FOR ‘trnTable’

ACTIVE BEFORE 

  INSERT

POSITION 0

AS

BEGIN

  IF (NEW._ID IS NULL) THEN

  NEW._ID = GEN_ID(_ID_GEN, 1);

END

 

Generator: 

CREATE GENERATOR _ID_GEN;

SET GENERATOR _ID_GEN TO 16594028

 

 

 

Thank you. 

 

With Regards,

Joje T. George

 

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com] 
Sent: 02 April 2018 09:27 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Auto-increment generating old ID's for insertion

 

  

On 2-4-2018 14:11, 'Joje' j...@codework-solutions.com 
  [firebird-support] 
wrote:
> I have issue where auto-increment of ID column is generating older ID’s 
> that is already used in table.
> 
> Currently, my transaction table has around 16 million rows and *ID of 
> last inserted row is 16,594,027*.  While the *generator ID its 
> 16,593,965*.  As you can see the generator is generating ID’s that is 
> already present in table which causes *violation of PRIMARY or UNIQUE 
> KEY constraint "INTEG_105"*error on that table.
> 
> I managed to fix this issue, recently I am facing it frequently on this 
> transaction table, majority of the tables had number of recor ds are 
> more than 2 million or there is connectivity issues with DB.
> 
> Now, I am wondering is this related to Firebird DB or is there any other 
> issue.
Which version of Firebird are you using (full version please)?

Do you have code that generates its own IDs? Do you have code that tries 
to manipulate the value using `GEN_ID(, -1)` (or another 
negative value), `ALTER SEQUENCE seq_name RESTART WITH new_val` or `SET 
GENERATOR seq_name TO new_val`? Do you allow your code to specify its 
own IDs instead of using the sequence?

It could be a bug in Firebird, but we would really need to see a 
reproducible case.

Mark
-- 
Mark Rotteveel

RE: [firebird-support] Auto-increment generating old ID's for insertion

['Joje' j...@codework-solutions.com [firebird-support]]

Hi Lester,

ID is generated using normal FB method. Only recently, I have started facing
this issue frequently on 'trntable' in larger databases where records are
excess of 10 million or so in 'trntable'. 

I think generator is getting reset due to some other external factors. If it
would have been an FB issue then it would have been replicated on somewhere
else also.
 

-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 02 April 2018 06:12 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Auto-increment generating old ID's for
insertion

On 02/04/18 13:11, 'Joje' j...@codework-solutions.com [firebird-support]
wrote:
> I have issue where auto-increment of ID column is generating older 
> ID’s that is already used in table.
> 
> Currently, my transaction table has around 16 million rows and *ID of 
> last inserted row is 16,594,027*.  While the *generator ID its 
> 16,593,965*.  As you can see the generator is generating ID’s that is 
> already present in table which causes *violation of PRIMARY or UNIQUE 
> KEY constraint "INTEG_105"*error on that table.
> 
> I managed to fix this issue, recently I am facing it frequently on 
> this transaction table, majority of the tables had number of records 
> are more than 2 million or there is connectivity issues with DB.
> 
> Now, I am wondering is this related to Firebird DB or is there any 
> other issue.

Assuming that the ID is generated via the normal Firebird method, then there
is a generator linked with the auto-increment. If the generator has been
reset for some reason this would explain the current problem. 
Simply setting the generator above the current highest value will fix this.
However we do need to check out just how the new value is generated as well..

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk






++

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

[firebird-support] Firebird database executing DML queries very slowly.

['Joje' j...@codework-solutions.com [firebird-support]]

Hello,

 

I am facing an issue on my client end where Firebird database is very slow
in executing DML queries. I ran the customer database on my machine, it was
working smoothly without any lag. This same database copied on customer
machine gets very slow in executing DML queries. I replaced the firebird
database with a fresh one, it worked without any lag for few days and
thereafter slowed down. 

 

Quite, similar to this another customer faced an issue in FB database, where
generator reset was getting stuck on an transaction table due to this no DML
queries was getting executed. This same DB copied on our machine had no
issue on performing DML on same transaction table.

 

This is the first time I am facing this issue, I doubt on their environment
as both the databases where working fine till few weeks back.

 

 

 

Thank you.

 

With Regards,

Joe

 

RE: [firebird-support] Firebird database executing DML queries very slowly.

['Joje' j...@codework-solutions.com [firebird-support]]

Thanks, I will try this command on customer end will upload the results.

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com] 
Sent: Friday, January 25, 2019 20:32
To: firebird-support@yahoogroups.com
Subject: ODP: [firebird-support] Firebird database executing DML queries very 
slowly.

 

  

Hi.

 

When you gain slowdows 

Run

gstat -h

 

and put here results.

Problem probably is because of some long running transaction.

 

Regards,

Karol Bieniaszewski

RE: [firebird-support] Firebird database executing DML queries very slowly.

['Joje' j...@codework-solutions.com [firebird-support]]

Ran the command and got the header information from the client. Below is 
database header page information of the affected database :  



Flags  0  

Checksum   12345  

Generation 9101445  

Page size 4096  

ODS version   11.2  

Oldest transaction   8991282  

Oldest active  9100701  

Oldest snapshot   9100701  

Next transaction  9100702  

Bumped transaction   1  

Sequence number  0  

Next attachment ID24698  

Implementation ID  26  

Shadow count   0  

Page buffers  0  

Next header page   0  

Database dialect   3  

Attributes   force write  

Variable header data:  

Sweep interval:2  

*END*  

 

The difference between the oldest transaction and newest transaction is large.  

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com] 
Sent: Friday, January 25, 2019 20:32
To: firebird-support@yahoogroups.com
Subject: ODP: [firebird-support] Firebird database executing DML queries very 
slowly.

 

  

Hi.

 

When you gain slowdows 

Run

gstat -h

 

and put here results.

Problem probably is because of some long running transaction.

 

Regards,

Karol Bieniaszewski

RE: [firebird-support] Firebird database executing DML queries very slowly.

['Joje' j...@codework-solutions.com [firebird-support]]

Hi , 

 

This issue was replicated only on client end while I copied the DB on our end 
it was working with any slowness. 

I took the remote of client DB , performed validation check on DB & backup - 
restore operation on DB. 

After doing this its working normally now. 

 

Thanks for help. :)

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com] 
Sent: Tuesday, January 29, 2019 17:21
To: firebird-support@yahoogroups.com
Subject: ODP: [firebird-support] Firebird database executing DML queries very 
slowly.

 

  

Hi

 

Is this from problematic time with connected clients?

If yes than i do no see problems. 

You must provide more details about issue.

What exact comand is slow? What are the query plan and query sats?

Did you tested HDD isues?

…

 

Regards,

Karol Bieniaszewski

[firebird-support] String conversion to timestamp fails during insertion in 12 hour format

[joje j...@codework-solutions.com [firebird-support]]

Hello,

I have a table with a column of TIMESTAMP data type.
I could not insert into this column if I use CAST('1/31/2018 08:09:30 
PM' AS TIMESTAMP)
on execution of the query it fails.
Later if I change the datetime to 24 hour format conversion is 
successful.

CAST('1/31/2018 08:09:30 PM' AS TIMESTAMP) --> insertion fails (SQL 
Error code -413)

CAST('1/31/2018 20:09:30' AS TIMESTAMP) --> Successful insertion

Why is it conversion fails in 12 hour format while successful in 24 hour 
format?

Do note that my system datetime is 12 hours format.

Thank you.