http://www.sysaid.com/Sysforums/posts/list/112.page JForum - http://www.jforum.net
I read that there is no way to compact it, but I thought removing stuff would help.
]]> http://www.sysaid.com/Sysforums/posts/preList/5852/25107.page http://www.sysaid.com/Sysforums/posts/preList/5852/25107.page GMT
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.
]]> http://www.sysaid.com/Sysforums/posts/preList/5852/25124.page http://www.sysaid.com/Sysforums/posts/preList/5852/25124.page GMT
There are a couple of commands for Derby databases that seem to compact whatever table you want.:

[b]SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE[/b](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

[b]SYSCS_UTIL.SYSCS_COMPRESS_TABLE[/b] (IN SCHEMANAME VARCHAR, IN TABLENAME VARCHAR, IN SEQUENTIAL SMALLINT)
(http://db.apache.org/derby/docs/10.6/ref/rrefaltertablecompress.html)
[i]
You have to have Auto Commit on to make it work it seems.[/i]

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]]> http://www.sysaid.com/Sysforums/posts/preList/5852/25148.page http://www.sysaid.com/Sysforums/posts/preList/5852/25148.page GMT