[firebird-support] Antivirus Exclusions

2016-04-05 Thread tcanfiel...@hotmail.com [firebird-support] 
I'm trying to set the exclusions for one of our servers and I see Firebird 
running.
 I don't have any experience with this program and I was hoping someone on the 
forum could shed some light on the needed exclusions.
 

 Any help would be greatly appreciated.
 

 Thanks Tim

[firebird-support] install firebird 3 superclasic in linux

2016-04-05 Thread 'Ismael L. Donis Garcia' sli...@citricos.co.cu [firebird-support] 
as I can install firebird 3 superclasic in linux?
I want to try it on devuan

Best Regards

| ISMAEL |

Re: [firebird-support] lock conflict deadlock

2016-04-05 Thread Alexey Kovyazin a...@ib-aid.com [firebird-support] 

Hi Sergio,

Definitely there should transaction which produces conflict, probably 
your query does not catch it.


You can track it with Monlogger - download HQbird, install it and 
register as trial (14 days), then connect with MonLogger to your 
database and at the moment of error message click get Snapshot.


MonLogger saves all data from MON% tables for the investigation in the 
folder, and on the tab Transaction it highlights with red color those 
transactions which are potentially conflict, so you can review which 
application runs it.


Regards,
Alexey Kovyazin
IBSurgeon










Hello! I'm having a problem which I don't undestand. I'm getting a 
deadlock error in my app, but when I see the active transactions (I 
attach the select I'm using) I see all read-only transactions active.


So, the question is: How can I get a deadlock error when I have just 
read-only transactions active?



To solve the problem, I have to stop/restart Firebird.


Thanks!!


Sergio


---


select

ma.mon$attachment_id,

mt.mon$transaction_id,

ma.mon$server_pid,

ma.mon$state,

ma.mon$attachment_name,

ma.mon$user


from

mon$transactions mt

left join mon$attachments ma on ma.mon$attachment_id = 
mt.mon$attachment_id



order by mt.mon$transaction_id

Re: [firebird-support] lock conflict deadlock

2016-04-05 Thread shg_siste...@yahoo.com.ar [firebird-support] 
sorry, I just copied a part of the select in the message. I do include that 
flag in the actual select.  

 Here it is the complete select. I'm sending '-1' in "in_trans"
 
 

 select
 ma.mon$attachment_id,
 mt.mon$transaction_id,
 ma.mon$server_pid,
 ma.mon$state,
 ma.mon$attachment_name,
 ma.mon$user,
 ma.mon$role,
 ma.mon$remote_protocol,
 ma.mon$remote_address,
 ma.mon$remote_pid,
 ma.mon$character_set_id,
 ma.mon$timestamp,
 ma.mon$garbage_collection,
 ma.mon$remote_process,
 ma.mon$stat_id,
 mt.mon$read_only
 

 from
 mon$transactions mt
 left join mon$attachments ma on ma.mon$attachment_id = 
mt.mon$attachment_id
 

 where (mt.mon$transaction_id = :in_trans) or (:in_trans = -1)
 

 order by mt.mon$transaction_id

Re: [firebird-support] lock conflict deadlock

2016-04-05 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Hi,
 
chow do you check that transaction is readonly?
in your select this field is not included
 
regards,
karol Bieniaszewski
 
 
W dniu 2016-04-05 15:29:11 użytkownik shg_siste...@yahoo.com.ar 
[firebird-support]  napisał:
 
Hello! I'm having a problem which I don't undestand. I'm getting a deadlock 
error in my app, but when I see the active transactions (I attach the select 
I'm using) I see all read-only transactions active.
So, the question is: How can I get a deadlock error when I have just read-only 
transactions active?
 
To solve the problem, I have to stop/restart Firebird.
 
Thanks!!
 
Sergio
 
---
 
select
        ma.mon$attachment_id,
        mt.mon$transaction_id,
        ma.mon$server_pid,
        ma.mon$state,
        ma.mon$attachment_name,
        ma.mon$user
 
    from
        mon$transactions mt
        left join mon$attachments ma on ma.mon$attachment _id = 
mt.mon$attachment_id
 
    order by mt.mon$transaction_id
 
 

 

[firebird-support] lock conflict deadlock

2016-04-05 Thread shg_siste...@yahoo.com.ar [firebird-support] 
Hello! I'm having a problem which I don't undestand. I'm getting a deadlock 
error in my app, but when I see the active transactions (I attach the select 
I'm using) I see all read-only transactions active.

So, the question is: How can I get a deadlock error when I have just read-only 
transactions active?
 

 To solve the problem, I have to stop/restart Firebird.
 

 Thanks!!
 

 Sergio
 

 ---
 

 select
 ma.mon$attachment_id,
 mt.mon$transaction_id,
 ma.mon$server_pid,
 ma.mon$state,
 ma.mon$attachment_name,
 ma.mon$user
 

 from
 mon$transactions mt
 left join mon$attachments ma on ma.mon$attachment_id = 
mt.mon$attachment_id
 

 order by mt.mon$transaction_id

Re: [firebird-support] RE: unsubscribe

2016-04-05 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2016-04-04 9:08, Hasnain Najafi hasnain.naj...@boots.co.uk 
[firebird-support] wrote:
> unsubscribe
>
> Regards,
>
> HARDWARE REFRESH TEAM

To unsubscribe send a mail to 
firebird-support-unsubscr...@yahoogroups.com with subject: Unsubscribe

Mark

Re: [firebird-support] Timestamp conversion issue

2016-04-05 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2016-04-05 10:47, Alex Castillo acr_k...@yahoo.com 
[firebird-support] wrote:
> Hello everyone,
>
> I'm having some issues with a stored procedure. This is a legacy
> development, so the time was stored in a varchar field and the date 
> in
> a timestamp field.
>
> The issue stills happens after the upgrade from 2.1.5 to 2.1.7 on
> linux open suse 11.4 x64, classic engine. However, this is not
> happening on my development machine using Windows XP. Even on my
> development environment I've restored the database to 2.1.5 using
> classic and super, but the issue is not being reproduced there, It
> seems to be a linux specific issue. In both cases I'm using 
> FlameRobin
> as client.

Could you show a sample of the time values? As far as I know Firebird 
use platform specific time conversion functions. This means that 
non-SQL-standard formats can fail or succeed depending on the platform 
(and maybe regional settings).

Mark

Re: [firebird-support] Re: Update big table and nbackup benefit or not?

2016-04-05 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Karol,
if you worry about database size after update, i could suggest to split huge 
update
by few smaller, each in own transaction and garbage collect between passes.
I.e. something like
update t set ... where id between 0 and N;
commit;
select count(*) from t where id between 0 and N;
commit;
update t set ... where id between N+1 and 2*N;
commit;
select count(*) from t where id between N+1 and 2*N;
commit;
...
Regards,
Vlad
._,___
Hi,
 
good concept -split updates and collect unused pages.
I will try.
 
thanks Vlad
 
regards,
Karol Bieniaszewski
 
 

Re: Re: [firebird-support] Update big table and nbackup benefit or not?

2016-04-05 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 


W dniu 2016-04-05 12:45:34 użytkownik Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support]  napisał:
> 05.04.2016 12:43, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
> wrote:
> > but what happens at merge stage (delta merging) when i unlock database?
> > page will be overriden or new one will be created and old will be marked as 
> > empty?
> 
>Old pages will be overwritten, new ones will be added. No difference.
> 
> -- 
>WBR, SD.
> 


Ok now i understand how delta work 
then i see that only benefit will be when page have some free space 
but this make no difference in both scenarios 1 and 2

thanks for the info


regards,
Karol Bieniaszewski

Re: [firebird-support] Update big table and nbackup benefit or not?

2016-04-05 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
05.04.2016 12:43, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
wrote:
> but what happens at merge stage (delta merging) when i unlock database?
> page will be overriden or new one will be created and old will be marked as 
> empty?

   Old pages will be overwritten, new ones will be added. No difference.

-- 
   WBR, SD.






++

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

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

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Re: Update big table and nbackup benefit or not?

2016-04-05 Thread hv...@users.sourceforge.net [firebird-support] 
Karol,

if you worry about database size after update, i could suggest to split huge 
update 
by few smaller, each in own transaction and garbage collect between passes.
I.e. something like

update t set ... where id between 0 and N;
commit;
select count(*) from t where id between 0 and N;
commit;

 update t set ... where id between N+1 and 2*N;
commit;
select count(*) from t where id between N+1 and 2*N;
commit;
...

Regards,
Vlad

Re: [firebird-support] Update big table and nbackup benefit or not?

2016-04-05 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
05.04.2016 10:21, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
wrote:
> what is the answer for this A or B?

   None of them. Full pages are written to delta file, so you'll have database 
in exactly 
the same state as per scenario 1.

-- 
   WBR, SD.






++

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

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

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Timestamp conversion issue

2016-04-05 Thread Alex Castillo acr_k...@yahoo.com [firebird-support] 



Hello everyone,
I'm having some issues with a stored procedure. This is a legacy development, 
so the time was stored in a varchar field and the date in a timestamp field.
The issue stills happens after the upgrade from 2.1.5 to 2.1.7 on linux open 
suse 11.4 x64, classic engine. However, this is not happening on my development 
machine using Windows XP. Even on my development environment I've restored the 
database to 2.1.5 using classic and super, but the issue is not being 
reproduced there, It seems to be a linux specific issue. In both cases I'm 
using FlameRobin as client.

Any help will be appreciated.
Thanks in advance.

Here is the SP:

SET TERM ^ ;
CREATE PROCEDURE PA_ASIGNA_CASETAS
--RETURNS (LOG VARCHAR(255))
AS
DECLARE VARIABLE ID INTEGER;
DECLARE VARIABLE UNIDAD VARCHAR(255);
DECLARE VARIABLE FECHACRUCE DATE;
DECLARE VARIABLE HORACRUCE TIME;
DECLARE VARIABLE FECHAHORACRUCE TIMESTAMP;
DECLARE VARIABLE PLAZACOBRO VARCHAR(255);
DECLARE VARIABLE IMPORTETOTAL DECIMAL(18,2); 
DECLARE VARIABLE IMPORTEFACTURADO DECIMAL(18,2); 
DECLARE VARIABLE FECHAHORACARGA TIMESTAMP;
DECLARE VARIABLE STATUS CHAR(1);
DECLARE VARIABLE FOLIO INTEGER;
DECLARE VARIABLE SUCURSAL SMALLINT;
DECLARE VARIABLE PARTIDA SMALLINT;
DECLARE VARIABLE CUENTA SMALLINT;
DECLARE VARIABLE IDCASETA SMALLINT;
DECLARE VARIABLE PARTIDACASETA SMALLINT;
BEGIN
    -- PRIMERO RECORRE PARA ENCONTRAR LAS CASETAS QUE EXISTEN DENTRO DE UN 
CONTRATO
    FOR SELECT  A.ID, A.UNIDAD, A.FECHAHORACRUCE, A.PLAZACOBRO, A.IMPORTETOTAL, 
A.IMPORTEFACTURADO, A.STATUS
    FROM STGEXCELIAVE A
    WHERE   A.STATUS = 'P'
    INTO :ID, :UNIDAD, :FECHAHORACRUCE, :PLAZACOBRO, :IMPORTETOTAL, 
:IMPORTEFACTURADO, 
    :STATUS
    DO 
    BEGIN
    --LOG = CAST(ID AS VARCHAR(10)) || ' ' || UNIDAD || ' ' || 
CAST(FECHAHORACRUCE AS VARCHAR(40));
    --SUSPEND;
    FOR SELECT  FIRST 1 C.FOLIO, C.SUCURSAL, V.PARTIDA 
    FROM    (
    SELECT  X.FOLIO, X.SUCURSAL, (CAST(X.FECHAINI AS DATE) + 
CAST(X.HORAINI AS TIME)) FECHAHORAINI,
    (CAST(X.FECHAFIN AS DATE) + CAST(X.HORAFIN AS 
TIME)) FECHAHORAFIN, X.ESTADO
    FROM (
    SELECT * FROM    CONTRATOS
    WHERE   NOT HORAFIN LIKE '%a.m.%' AND NOT HORAFIN 
LIKE '%p.m.%'
    AND NOT HORAINI LIKE '%a.m.%' AND NOT HORAINI LIKE 
'%p.m.%'
    ) AS X
    ) AS C JOIN
    VIAJES V ON C.SUCURSAL = V.SUCURSAL AND C.FOLIO = V.FOLIO JOIN
    UNIDADES U ON V.UNIDAD = U.UNIDAD
    WHERE   C.ESTADO <> 'C' AND C.FECHAHORAINI <= :FECHAHORACRUCE AND 
C.FECHAHORAFIN >= :FECHAHORACRUCE
    AND TRIM(U.NOMUNIDAD) = :UNIDAD    
    INTO    :FOLIO, :SUCURSAL, :PARTIDA
    DO 
    BEGIN
    --LOG = 'FOLIO ' || CAST(FOLIO AS VARCHAR(10)) || ' SUCURSAL ' || 
CAST(SUCURSAL AS VARCHAR(10)) || ' PARTIDA ' || CAST(PARTIDA AS VARCHAR(10));
    --SUSPEND;
    
    SELECT  COUNT(*) FROM CASETAS WHERE TRIM(DESCRIPCION) = :PLAZACOBRO
    INTO    :CUENTA;
    
    IF (CUENTA > 0) THEN  -- LA CASETA EXISTE
    BEGIN
    SELECT  CASETA FROM CASETAS WHERE TRIM(DESCRIPCION) = 
:PLAZACOBRO
    INTO :CUENTA;
    END
    ELSE -- LA CASETA NO EXISTE
    BEGIN
    SELECT  COALESCE(MAX(CASETA),0) + 1 FROM CASETAS INTO :CUENTA;
    
    INSERT INTO CASETAS (CASETA, DESCRIPCION, PRECIO, PRECIOIAVE, 
PRECIOCAMIONETA, PRECIOIAVECAMIONETA)
    VALUES (:CUENTA, :PLAZACOBRO, :IMPORTEFACTURADO, 
:IMPORTEFACTURADO, :IMPORTEFACTURADO, :IMPORTEFACTURADO);
    END
    
    
    
    -- INSERTA EL RECORD EN EL CONTRATO
    SELECT COALESCE(MAX(PARTIDACASETA),0) + 1 
    FROM CASETASCONTRATO 
    WHERE SUCURSAL = :SUCURSAL AND FOLIO = :FOLIO AND PARTIDA = :PARTIDA
    INTO :PARTIDACASETA;
    
    INSERT INTO CASETASCONTRATO (FOLIO, SUCURSAL, PARTIDA, 
PARTIDACASETA, IMPORTE, ESIAVE, FECHAHORA, 
    NUMERO, TIPO, CASETA)
    VALUES (:FOLIO, :SUCURSAL, :PARTIDA, :PARTIDACASETA, 
:IMPORTEFACTURADO, 'S', :FECHAHORACRUCE,
    0, 'I', :CUENTA);
    
    --LOG = 'UPDATING ' || CAST(ID AS VARCHAR(10)) || ' ' || UNIDAD || 
' WITH STATUS = A';
    --SUSPEND;
    
    UPDATE STGEXCELIAVE
    SET STATUS = 'A',
    FOLIO = :FOLIO,
    SUCURSAL = :SUCURSAL
    WHERE ID = :ID;
    END
    END
    
    -- SEGUNDA OPORTUNIDAD BUSCA CASETAS QUE EXISTEN 24 HORAS ANTES Y DESPUES
    FOR SELECT  A.ID, A.UNIDAD, A.FECHAHORACRUCE, A.PLAZACOBRO, A.IMPORTETOTAL, 
A.IMPORTEFACTURADO, A.STATUS
    FROM STGEXCELIAVE A
    WHERE   A.STATUS = 'P'
    INTO :ID, :UNIDAD, :FECHAHORACRUCE, :PLAZACOBRO,

Re: Re: [firebird-support] Update big table and nbackup benefit or not?

2016-04-05 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Hi,
 
thanks Tim for warning - i know fragmenting records are bad
but in my case this probably not happen because this is ppmxl database with 
numbers only 
 
but do you know what will be answer A or B?
If no one answers I'll have to test yourself ;-)
 
regards,
Karol Bieniaszewski
 
 
If the update makes the records longer (after run-length encoding) it's even 
more fun, as you might get fragmentation (of records across pages) and access 
times can then increase by a very large factor indeed, even to the extent of 
completely crippling the performance of an entire application.
On 05/04/2016 09:21, liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support] wrote:
 
Hi,
 
i must update big table 100 GB
and as we know when we do update then new record version will be created.
scenarion 1:
 
1. Table size 100GB (db size 200GB)
2. Update field in all records generate 100GB new record versions
3. table size after is 200GB (db size 300GB)
4. sweep remove 100GB and mark pages as free (table size 100 GB but database 
still 300GB)
5. backup and restre bring db to it previous size (db size 200GB)
 
but what happen when i do this?
 
scenarion 2:
1. Table size 100GB (db size 200GB)
2. i lock database with nbackup -L 
3. Update field in all records generate 100GB delta file (db size 200GB)
4. table size in db is 100GB and delta is 100GB (db size 200GB)
5. i unlock database nbackup -U
 
A. table will be 100GB and no free pages? (db size will be 200GB and no need to 
bacup and restore process)
B. table will be 100GB and in db will be 100GB free pages? (db size will be 
300GB and i need to bakup and restore?)
 
what is the answer for this A or B?
 
regards,
Karol Bieniaszewski
 
-- Tim Ward
__._,_.__

 

Re: [firebird-support] Update big table and nbackup benefit or not?

2016-04-05 Thread Tim Ward t...@telensa.com [firebird-support] 
If the update makes the records longer (after run-length encoding) it's 
even more fun, as you might get fragmentation (of records across pages) 
and access times can then increase by a very large factor indeed, even 
to the extent of completely crippling the performance of an entire 
application.


On 05/04/2016 09:21, liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support] wrote:

Hi,
i must update big table 100 GB
and as we know when we do update then new record version will be created.
scenarion 1:
1. Table size 100GB (db size 200GB)
2. Update field in all records generate 100GB new record versions
3. table size after is 200GB (db size 300GB)
4. sweep remove 100GB and mark pages as free (table size 100 GB but 
database still 300GB)

5. backup and restre bring db to it previous size (db size 200GB)
but what happen when i do this?
scenarion 2:
1. Table size 100GB (db size 200GB)
2. i lock database with nbackup -L
3. Update field in all records generate 100GB delta file (db size 200GB)
4. table size in db is 100GB and delta is 100GB (db size 200GB)
5. i unlock database nbackup -U
A. table will be 100GB and no free pages? (db size will be 200GB and 
no need to bacup and restore process)
B. table will be 100GB and in db will be 100GB free pages? (db size 
will be 300GB and i need to bakup and restore?)

what is the answer for this A or B?
regards,
Karol Bieniaszewski




--
Tim Ward

[firebird-support] Update big table and nbackup benefit or not?

2016-04-05 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Hi,
 
i must update big table 100 GB
and as we know when we do update then new record version will be created.
scenarion 1:
 
1. Table size 100GB (db size 200GB)
2. Update field in all records generate 100GB new record versions
3. table size after is 200GB (db size 300GB)
4. sweep remove 100GB and mark pages as free (table size 100 GB but database 
still 300GB)
5. backup and restre bring db to it previous size (db size 200GB)
 
but what happen when i do this?
 
scenarion 2:
1. Table size 100GB (db size 200GB)
2. i lock database with nbackup -L 
3. Update field in all records generate 100GB delta file (db size 200GB)
4. table size in db is 100GB and delta is 100GB (db size 200GB)
5. i unlock database nbackup -U
 
A. table will be 100GB and no free pages? (db size will be 200GB and no need to 
bacup and restore process)
B. table will be 100GB and in db will be 100GB free pages? (db size will be 
300GB and i need to bakup and restore?)
 
what is the answer for this A or B?
 
regards,
Karol Bieniaszewski
 

Re: [firebird-support] db corruption

2016-04-05 Thread Rik van Kekem r...@graficalc.nl [firebird-support] 
'Andrew Zenz' and...@aimsoftware.com.au [firebird-support] wrote:
> FYI FirstAID scanned the database and identified 2 tables as having
> corruption.  I attempted a data pump with DBW excluding those 2 tables
> but it failed on several other tables, tables that FirstAID claims are
> clean.

The tables on which it failed during the data pump... did they have 
foreign keys pointing to the 2 corrupt tables?? If they do, that's 
probably the reason it failed. If not... what exactly was the error?

I've fixed a few DB's in the past manually but it's tedious work (and 
you might loose some records due to corrupt db-pages).

Before pumping over the data from the corrupt DB you need to restore 
that DB to a working DB (backup/restore cycle). The DB will be marked as 
offline in case of errors but you can reach the data with a single 
instance of your DB-manager of choice.

Then you need to remove the records in those tables which depend on the 
tables you left out. Or you need to NULL the foreign keys if your 
DB-design allows it. After that you should be able to pump the adjusted 
tables.

(I even repaired some DB's in place after which I could bring them 
online again. And after a backup/restore cycle they were ok to use)

Regards,
Rik






++

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

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

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/