| Author |
Message |
![[Post New]](/Sysforums/templates/default/images/icon_minipost_new.gif) 12/11/2010 11:57:18
|
venco
SysAider
Joined: 23/03/2010
Messages: 3
Offline
|
In order to reduce the size of the derby database, I have been going thru and removing attachments and/or SR's that we no longer need. After doing this and removing quite a large chunk of attachments, the size of the Derby database is still the same. Shouldn't the size of the database reduce if we delete a bunh of stuff?
I read that there is no way to compact it, but I thought removing stuff would help.
|
|
|
13/11/2010 19:18:52
|
SBIT
Super SysAider
Joined: 03/06/2009
Messages: 78
Location: Calgary
Offline
|
The behaviour you have described is fairly typical of database files. When you delete contents of the database file, the db file itself remains the same size.
In Microsoft databases the process is called a 'compact and repair'. This would essential flush out that blank space that the database file is currently storing and reduce its overall size. I didn't turn up any tools from a quick google that can perform this on a Derby database, but I am sure they exist.
How big is your database?
What is your concern with the size of the database.. i.e. Are you running out of disk space or do you feel its size could be the cause of a performance issue?
If you feel Derby is not for you, SysAid is able to run on an SQL back end, however I haven't seen any instructions on how to set this up. It might be a process that Ilient needs to be involved in to convert your database file.
|
|
|
15/11/2010 11:36:07
|
savardm
SysAider
Joined: 11/06/2008
Messages: 13
Offline
|
I would love to run on SQL but that isn't an option at this time. After posting I did some tests and found the following:
There are a couple of commands for Derby databases that seem to compact whatever table you want.:
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(IN SCHEMANAME VARCHAR,IN TABLENAME VARCHAR,IN PURGE_ROWS SMALLINT,IN DEFRAGMENT_ROWS SMALLINT, IN TRUNCATE_END SMALLINT )
(http://db.apache.org/derby/docs/10.6/ref/rrefproceduresinplacecompress.html)
and
SYSCS_UTIL.SYSCS_COMPRESS_TABLE (IN SCHEMANAME VARCHAR, IN TABLENAME VARCHAR, IN SEQUENTIAL SMALLINT)
(http://db.apache.org/derby/docs/10.6/ref/rrefaltertablecompress.html)
You have to have Auto Commit on to make it work it seems.
I ran one of these on my sysaid database with all the proper parameters using a third party SQL program (like RazorSQL) after removing a bunch of the attachments I didn't need. It reduced the size by 700 MB or so which is just what I expected. Then I needed to test it to see if SysAid would still work and it seemed to work just find. All the attachments were removed from the SR's I wanted but the SR was still there.
I was able to query the SysAid database to find the largest attachments by using a query like this:
SELECT id, Length(file_content), file_name FROM SYSDBA.SERVICE_REQ_FILES;
This showed me the SR#, the filename and the file size so I could sort by size and grab the largest ones.
So I suppose that is one way to do it. I didn't have the guts to try it on my real database, but that may be coming. 
Matt
|
|
|
![[Up]](/Sysforums/templates/default/images/icon_up.gif){kind=icon}
![[WWW]](/Sysforums/templates/default/images/icon_www.gif){kind=icon}
