Re: [firebird-support] What makes a database file size so big?

[Rik van Kekem r...@graficalc.nl [firebird-support]]

On 29/03/16 11:44, Małgorzata Barchańska go...@fastdata.com.pl 
[firebird-support] wrote:
> I'm very curious - so what makes our database file so big? Sum of clear 
> metadata
> and raw data is about 2,4GB, and what with other 12GB?  Is anything we
> can do to shrink our big database file (drop useless indexes or
> something like that)?
You could try running your database through gstat.exe (in your 
bin-directory of Filebird, if you installed the development tools with 
it). You can learn a lot about the way the database-pages are filled per 
table.

You can find a manual for it here:
http://www.firebirdsql.org/pdfmanual/Firebird-gstat.pdf

Grtz,
Rik

Re: [firebird-support] What makes a database file size so big?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

download HQbird Standard trial (http://ib-aid.com/en/hqbird/) and 
analyze your database statistics with Database Analyst and database file 
DBInfo.


Database Analyst will give you an exact information of tables and 
indices sizes tab Tables -> sort on Size), their fragmentation due to 
versioning and blobs.
DBinfo will give you information about distribution of pages with 
numbers and percentage of page types.


Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com






Hi,
I use firebird 2.5 classic server on windows operating system.
Because our database file is quite big (18GB) and it starts to cause
(because of its size) some maintenance inconvenience we decided to try
to shrink it. After checking database statistics we deleted "useless"
records in tables that took the most space, we updated old useless blob
fields with null values. After backup and restore it turned out that new
database is only 3GB smaller than original. We checked the statistics
and according to results we have 2,3GB raw data in database file.
Restore only metadata creates database of 70MB size, restoring without
active indexes results with file 500MB smaller than original. I'm very
curious - so what makes our database file so big? Sum of clear metadata
and raw data is about 2,4GB, and what with other 12GB? Is anything we
can do to shrink our big database file (drop useless indexes or
something like that)?

Thank you in advance for your answer.

Best regards,
Małgorzata Barchańska

--
Pozdrawiam
Małgorzata Barchańska

Fast Data Sp. z o.o.
Aleja Zwycięstwa 96/98, 81-451 Gdynia
Tel.: (58) 732 72 01
http://www.fastdata.com.pl/
NIP : 5862214954,REGON : 220595046
KRS : 304310, Kapitał zakładowy 50 000 zł

[firebird-support] What makes a database file size so big?

[Małgorzata Barchańska go...@fastdata.com.pl [firebird-support]]

Hi,
I use firebird 2.5 classic server on windows operating system.
Because our database file is quite big (18GB) and it starts to cause 
(because of its size) some maintenance inconvenience we decided to try 
to shrink it. After checking database statistics we deleted "useless" 
records in tables that took the most space, we updated old useless blob 
fields with null values. After backup and restore it turned out that new 
database is only 3GB smaller than original. We checked the statistics  
and according to results we have 2,3GB raw data in database file. 
Restore only metadata creates database of 70MB size, restoring without  
active indexes results with file 500MB smaller than original. I'm very 
curious - so what makes our database file so big? Sum of clear metadata 
and raw data is about 2,4GB, and what with other 12GB?  Is anything we 
can do to shrink our big database file (drop useless indexes or 
something like that)?


Thank you in advance for your answer.


Best regards,
Małgorzata Barchańska


-- 
Pozdrawiam
Małgorzata Barchańska


Fast Data Sp. z o.o.
Aleja Zwycięstwa 96/98, 81-451 Gdynia
Tel.: (58) 732 72 01
http://www.fastdata.com.pl/
NIP : 5862214954,REGON : 220595046
KRS : 304310, Kapitał zakładowy 50 000 zł

Re: [firebird-support] How to add previous column value to current value?

[setysvar setys...@gmail.com [firebird-support]]

 >I need something like this
 >
 >Name   Date  Days betwen Previous date
 >--
 >Name1 date10
 >Name2 date2date2-date1
 >Name3 date3date3-date2

I agree with Thomas in that EXECUTE BLOCK is probably the best solution, 
but an (inferior) alternative is:

SELECT T1.Name, T1.MyDate, coalesce(T1.MyDate - (SELECT MAX(T2.MyDate) 
FROM MyTable T2 WHERE T1.MyDate > T2.MyDate), 0)
FROM MyTable T1

The main reason for me calling it inferior, is that it will be slow for 
large tables without additional selective WHERE criteria for T2.

HTH,
Set






++

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/

Re: [firebird-support] Listing Table of Database in Order of Dependency

[LtColRDSChauhan rdsc1...@gmail.com [firebird-support]]

On Mon, Mar 28, 2016 at 11:21 PM, setysvar setys...@gmail.com
[firebird-support]  wrote:

>
>
> >Hi,
>
> Hi Rajiv!
>
> >How can i list tables of a database in order of dependency ie
> >
> >TABLE_1 (no foreign key references)
> >TABLE_2 (foreign key references, if any, in TABLE_1 )
> >TABLE_3 (foreign key references, if any, in TABLE_1 / TABLE_2 )
> >TABLE_4 (foreign key references, if any, in TABLE_1 / TABLE_2 / TABLE_3)
>
> Ideally, this would be how you designed your database, i.e. have a
> forced order of your tables and refuse foreign keys pointing to any
> tables further down the list. I'm not certain whether or not things will
> want to line up as nicely as you want them in an existing database.
>
> Nevertheless, I suggest you create a new table:
>
> CREATE TABLE TABLES_ORDERED
> (
> ID INTEGER NOT NULL,
> RDB$RELATION_NAME CHAR( 31 ),
> CONSTRAINT PK_TABLES_ORDERED PRIMARY KEY ( ID )
> );
>
> CREATE GENERATOR TABLES_ORDERED_GEN;
> SET TERM ^^ ;
>
> CREATE TRIGGER TABLES_ORDERED_ID FOR TABLES_ORDERED ACTIVE BEFORE INSERT
> POSITION 0 AS
> begin
> if ( ( new.ID is null ) or ( new.ID = 0 ) ) then
> new.ID = gen_id( TABLES_ORDERED_GEN, 1 );
> end
> ^^
> SET TERM ; ^^
>
> commit;
>
> fill it with those tables that have no foreign key (start by running
> DELETE FROM TABLES_ORDERED if the table is not empty):
>
> INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME)
> SELECT RDB$RELATION_NAME FROM RDB$RELATIONS r
> WHERE RDB$SYSTEM_FLAG=0
> AND NOT EXISTS(SELECT * FROM RDB$INDICES i
> WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME
> AND i.RDB$FOREIGN_KEY IS NOT NULL);
>
> and then, repeatedly, try:
>
> INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME)
> SELECT RDB$RELATION_NAME FROM RDB$RELATIONS r
> WHERE RDB$SYSTEM_FLAG=0
> AND NOT EXISTS(SELECT * FROM TABLES_ORDERED o /*Ignore tables already
> inserted*/
> WHERE r.RDB$RELATION_NAME = o.RDB$RELATION_NAME)
> AND NOT EXISTS(SELECT * FROM RDB$INDICES i /*Only insert tables whose
> foreign key tables are inserted already*/
> JOIN RDB$INDICES i2 ON i.RDB$FOREIGN_KEY =
> i2.RDB$INDEX_NAME
> AND r.RDB$RELATION_NAME <>
> i2.RDB$RELATION_NAME /*Omit this line if you don't want to include
> tables pointing to themselves*/
> LEFT JOIN TABLES_ORDERED o ON i2.RDB$RELATION_NAME =
> o.RDB$RELATION_NAME
> WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME
> AND i.RDB$FOREIGN_KEY IS NOT NULL
> AND o.ID IS NULL);
>
> When you've done this enough times for no further table to be inserted,
>
you can get your ordered list by simply running:
>
> SELECT RDB$RELATION_NAME
> FROM TABLES_ORDERED
> ORDER BY ID
>
> If you find that the list lacks two or more tables, investigate those
> remaining tables - maybe you have some circular dependencies?
>
All Tables got added to  TABLES_ORDERED

>
> Note that I've never tried doing anything similar myself (I don't even
> think I've written a query that uses the RDB$FOREIGN_KEY before), and
> that there may well be errors in what I've written above.
>
> There were no errors, Set. Thanks a lot.
This helped !
Regards,
Rajiv

> HTH,
> Set
>

RE: [firebird-support] How to add previous column value to current value?

['Piotr Olszewski' piotr.olszew...@eskrzynka.pl [firebird-support]]

let's assume that I have 2.5.3

Pozdrawiam

Piotr Olszewski

-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Tuesday, March 29, 2016 11:45 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How to add previous column value to current
value?

29.03.2016 11:40, 'Piotr Olszewski' piotr.olszew...@eskrzynka.pl
[firebird-support] wrote:
> I need to get a date difference (days between) between rows.

   With Firebird 3.0 you can use window function LAG.

-- 
   WBR, SD.






++

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

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

++


Yahoo Groups Links

Re: [firebird-support] How to add previous column value to current value?

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

29.03.2016 11:40, 'Piotr Olszewski' piotr.olszew...@eskrzynka.pl 
[firebird-support] wrote:
> I need to get a date difference (days between) between rows.

   With Firebird 3.0 you can use window function LAG.

-- 
   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] How to add previous column value to current value?

['Piotr Olszewski' piotr.olszew...@eskrzynka.pl [firebird-support]]

Hi

 

 

I need to get a date difference (days between) between rows.

 

Let say i have:

 

Name   Date

---

Name1 date1

Name2 date2

Name3 date3

 

Order by date

 

I need something like this

 

Name   Date  Days betwen Previous date

--

Name1 date10

Name2 date2date2-date1

Name3 date3date3-date2

 

 

sql is generated dynamically and it's quite complicated so i can't use
procedure to do this.

 

 

 

Regards

 

Piotr Olszewski

 

Re: [firebird-support] Firebird database keeps randomly locking up.

[Michael Weissenbacher m...@dermichi.com [firebird-support]]

On 24.03.2016 12:36, Richard Cooke rich...@alcoeng.co.uk
[firebird-support] wrote:
> 
> 
> 
> I have a problem with a Firebird database randomly locking up completely
> for a period of time sometimes only a 30 seconds, sometimes 10 or 15
> minutes.
I would suspect either a virus scanner or the Windows System Protect
"Feature". Which file extension are you using for the datebase?
See: http://www.firebirdfaq.org/faq353/


> Version is 2.1.7
> Server is new running Windows Server 2012 R2.
> Only a smallish database with about 6 users.
> 
> We never had any problems when running on an old windows 2000 server.
> 
> firebird log file shows various errors, see below:
> 
> 
> ALCOSERVERThu Mar 24 11:14:17 2016
> INET/inet_error: send errno = 10054
> 
> 
> ALCOSERVERThu Mar 24 11:14:17 2016
> INET/inet_error: send errno = 10054
> 
> 
> ALCOSERVERThu Mar 24 11:14:17 2016
> INET/inet_error: send errno = 10054
> 
> 
> ALCOSERVERThu Mar 24 11:14:17 2016
> SERVER/process_packet: broken port, server exiting
> 
> 
> ALCOSERVERThu Mar 24 11:14:17 2016
> SERVER/process_packet: broken port, server exiting
> 
> 
> ALCOSERVERThu Mar 24 11:14:17 2016
> SERVER/process_packet: broken port, server exiting
> 
> 
> ALCOSERVERThu Mar 24 11:14:17 2016
> INET/inet_error: send errno = 10054
> 
> 
> ALCOSERVERThu Mar 24 11:14:17 2016
> SERVER/process_packet: broken port, server exiting
> 
> 
> ALCOSERVERThu Mar 24 11:14:36 2016
> INET/inet_error: read errno = 10054
> 
> Hope someone can help.
> thanks,
> Richard.
> 
> 
> 
>