how can I clean DB totally for use it as template?

Hello!

After deletion of all rows from DB, the size of bases folder has several hundred MB (the folder which contain the biggest data is ".config") in spite that of DB is absolutely empty, end all tables have no data. how can I clean DB totally (cos I have to use this one base as template)

 

many thanks

 

Igor

This has to be done directly in Derby. Details can be found here:
http://db.apache.org/derby/docs/10.10/adminguide/ />(look for the " Reclaiming unused space" section right at the bottom).

For connecting directly to the Derby DB look here:
https://docs.chemaxon.com/display/instantjchem/Using+the+database+explorer

 

Tim

Tim,

DB disconnected, path next: d:\123 but obtain error: cannot establish a connection to jdbc:derby:d:\123 using org.apache.derby.jdbc.EmbeddedDriver (Database 'd:\123' not found.)

thru the "File/open_DB" this DB opens absolutely normally

Igor

Hi Igor,

as Tim mentioned above, you have to perform the action manually inside of the Derby database itself. I have tested the approach myself and it was working fine. 

For easier understanding of what is needed to be done, follow the following steps:

1. Get the database URL. This can be done by opening Schema Editor (double-click the schema) and navigate to Schema -> Connection, where you will find JDBC URL field that will contain something like "jdbc:derby:/Users/david/IJCProjects/derbyEmptySpace/.config/localdb/db" Copy this database URL


2. Disconnect from the database (this is needed as Derby permits only one connection to database at a time)


3. In the Menu bar, click Window -> Other -> Services


4. Go to the Services window (similar to Projects or Lists and Queries windows)


5. Under Drivers, right-click Derby and select "Connect using..."


6. In the wizard, replace the content of JDBC URL field by the database URL you got from step 1 and click through the wizard


7. Right click the newly added connection and connect if not connected already (if you get connection errors, check that you are disconnected from the schema - see step 2)


8. Right click the connection again and select "Execute command..."


9. Write your SQL there and run it

 

The SQL that you need will depend on the table you need to "clean up" (the table name in the sql will change according to your needs). There are 2 SQL queries that are described in the link Tim sent. The first one is the following:

SELECT * FROM TABLE(SYSCS_DIAG.SPACE_TABLE('APP', 'YOURTABLENAMEHERE')) AS T

This one is purely for diagnostics and can serve for you to estimate how much space extra is the database table taking. 

 

The second one is the one that will perform the actual clean up:

call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'YOURTABLENAMEHERE', 0);

This SQL command will call a method on the table that will unlock the unused space. If you are however low on system resources, you can change the SQL command to run in sequential mode, which is less memory and disk demanding, but will run slower. 

call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'YOURTABLENAMEHERE', 1);

I hope this explains all the steps better.

 

David

igorlab wrote:

Tim, DB disconnected, path next: d:\123 but obtain error: cannot establish a connection to jdbc:derby:d:\123 using org.apache.derby.jdbc.EmbeddedDriver (Database 'd:\123' not found.) thru the "File/open_DB" this DB opens absolutely normally Igor

See step 2 of my previous comment's walkthrough. Derby can not connect more than 1 connection at once. You need to disconnect from the schema in IJC first. Then you can connect with Database Explorer

 

David

David, great thanks!

now I'll try again, but previously I also disconnect the schema...

Igor

Igor,

other possibility that you get connection error is that the database URL contains a typo. It is easy to misspell it. I would recommend to double check that the database URL is really looking like this:

jdbc:derby:/Users/david/IJCProjects/derbyEmptySpace/.config/localdb/db

What can happen is that the wizard "prefills" the JDBC URL field with the "jdbc:derby:" already and if you forget to delete this, before pasting the actual url itself, you will get something like this:

jdbc:derby:jdbc:derby:/Users/david/IJCProjects/derbyEmptySpace/.config/localdb/db

This is wrong and will not connect. Other than that, I recommend also to make sure that the url does not have the ";schema_upgrade=true" option as that one could be also the one interfering with proper connection establishing.

 

David

David, THANK YOU. THAT’S VERY KIND OF YOU!

After restarting all works ok!

Hello

I've tried to compress empty DB by sql statement  "call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'YOURTABLENAMEHERE', 0);" but size of DB stayed same. size decreases only if I delete Indexes of tables, but not much. now size more then 200 mb after all manipulations...

advise something please

Hello Igor,

I sent you a mail to an email address registered with your account.

I am awaiting your answer there.

Best regards,

Martin