set hsqldb.cache_file_scale

Help
G Foster
2012-12-27
2014-01-19
  • G Foster

    G Foster - 2012-12-27

    Hi!  I am new to java and hsqldb looking into a performance problem with applications that have not been supported internally.  The application creates a properties file for each user that runs the application in C:\Documents and Settings\.  I see the property hsqldb.cache_file_scale=1 and from what I have read I should set this at 8.  But I am very unclear as to how to set it.  I have seen the instructions to run the shutdown script and then issue a command to set property.  But don't know where to start.  I also found a scripthsqldb_create.sql file contained in a jar file in the path C:\DIW\DIW_signed\com\IRI\diw\db\hsqldb where the table is created.  Could I issue the set command here? I have downloaded IntelliJIDEA to help me look at the files I am working with to help to begin to support the applications.  If I can set the property in the sql file can I then just resave the jar file and put it back in the original directory do distributed files?
    Thanks in advance for your assistance.
    geefos

     
  • Fred Toussi

    Fred Toussi - 2012-12-27

    This property is not related to performance. When the property is 1, It limits the size of the .data file to 2GB. If your files are much smaller than this, then there is no need to change the property.

    You can set this property only before any data is inserted into tables.

    There may be other issues related to performance. For example CHECKPOINT DEFRAG or SHUTDOWN COMPACTshould be executed periodically (after hundreds of thousands of updates) to clear up the wasted space in the .data file.

     
  • G Foster

    G Foster - 2012-12-27

    Hi!  Thanks for your response.  I received the error message 'S1000 Data file size limit is reached'. And after some searching on the internet thought this property, cache_file_scale, was a probable cause of the issue.  The diwdb.data file from the last file I was trying to load is at 1.99 GB where it hung and I eventually killed the process.  I have been thrown into this problem with no java or hsqldb experience.  They are still trying to locate the original source code and development environment  for me.  Can I work with just the distribution files that are used to run the application to troubleshoot and make changes?  What do I need to be able to access the .data file and run commands against it?  At present I have only downloaded and IDE.  What other tools should I get?   Thanks!

     
  • Fred Toussi

    Fred Toussi - 2012-12-27

    Your diagnostic is correct. The size limit has been reached.

    Read the Guide for your version of HSQLDB (which is probably 1.8.0) on how to open a file: database and the DatabaseManager GUI program.

    After closing your app, you should open the database using the HSQLDB jar that created it. The jar contains the DatabaseManager GUI program, which you can use. You then perform SHUTDOWN SCRIPT, then edit the .properties file and change the property value to 8. After saving, open the database again, then SHUTDOWN.

    You can then restart the app.

     
  • G Foster

    G Foster - 2012-12-27

    Thanks!  I am in the process of downloading JDK and Netbeans IDE.  I tried changing the jar file that contained the create table statement.  As I kind of expected, when I copied the jar file back into the path it couldn't launch the app because the jar that was changed wasn't signed.  I changed the hsqldb.jar to hsqldb.zip and extracted the files to have a look.  The only properties files there are remarks and messages i.e. webserver.properties which contains 3 types of responses, org_hsqldb_databaseclassloader.properties, data-type-create-parameters.properties.  Would the file(s) that were used to create this hsqldb.jar be found in the development build files?  The only properties files that I have found so far that contain parameter values are the ones found under the docs & settings\<user>.  But I haven't been able to find out yet how the files are generated either the first or subsequent times. Your correct in the version.  I will have a good read through the guide you suggested and run through some tutorials too.  Thanks!

     
  • Fred Toussi

    Fred Toussi - 2012-12-27

    Because you are not familiar with Java, you are taking unnecessary measures, such as renaming the HSQLDB jar and looking inside it. Also completely wrong steps such as changing the contents of the other jar file.

    The Java environment is already installed on the computer. If not,  install a JDK. Then try the "java" command on the command line with arguments that inclued the names of DatabaseManager and hsqldb.jar. You should find this in the Guide.

     
  • Fred Toussi

    Fred Toussi - 2012-12-27

    The .properties file that I mentioned belongs to your database. In some directory, there is a set of files with the same name and different extensions, including .properties and .data. see the Guide.

     
  • G Foster

    G Foster - 2012-12-27

    Ok, thanks.  I've been having a look at the document now.   So are you saying that I do not need any files from the development environment and I should be able to troubleshoot and make changes/enhancements and provide ongoing support just from the distribution files?

     
  • Fred Toussi

    Fred Toussi - 2012-12-27

    I did not say that.

    You should be able to resolve the current problem by completing the steps I mentioned. For other issues, you will probably need to change the code, which does need an IDE and knowlege of Java.

     
  • G Foster

    G Foster - 2012-12-27

    OK, Sorry I was putting too many resolutions into one arena.  Thanks for your time on all these questions. On the server where the app is. in the C:/Documents and Settings\<user> dir for those who I'm assuming have run the tool , each has the files diw.properties, diwdb.backup, diwdb.data, diwdb.properties and diwdb.script.  So I was assuming that there was some 'master' set of files somewhere that these were populated from for  each user.  And I had tried just to change the properties file for my user and then run the program which I didn't think would hold but was worth a shot.

     
  • Fred Toussi

    Fred Toussi - 2012-12-27

    As you have discovered, each user has their own database. Thes databases are not linked to one another.

    Do not modify the .properties file except as part of the steps I mentioned. If you change the property when there is a .data file present, the database will not open and may even get corrupted.

     
  • G Foster

    G Foster - 2012-12-28

    For my userid, then could I also delete the database files and then they would be recreated when I ran the application the next time.  Would there also be a master set of these database files or just the parameters or properties within the code to create them for each user?

     
  • G Foster

    G Foster - 2012-12-28

    Making some progress.  I have the HSQL Database Manager GUI open.  Now trying to get a connection..

     
  • G Foster

    G Foster - 2012-12-28

    Another question.  The only .data files I find are in the individual user directories.  So would it be that there is no master database to connect to?  I've tried to connect to the database in my user directory using the HSQL Database Manager GUI but getting no luck there.  Any suggestions?

     
  • G Foster

    G Foster - 2012-12-31

    I have downloaded hsqldb software on my laptop. I was able to launch the HSQL Database Manager Gui and see properties listed in the left pane.  But in the application I am troubleshooting if I go to the directories on the server where I find the hsqldb.jar and issue the same command as shown below I get the errors displayed.  Any suggestions on how I can get to the database of the application?

    C:\DIW-V2>java -cp hsqldb.jar org.hsqldb.util.DatabaseManagerSwing
    Failed to load preferences.  Proceeding with defaults:

    Exception occurred during event dispatching:
    java.lang.NoClassDefFoundError: java/sql/Savepoint
            at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
            at org.hsqldb.jdbcDriver.connect(Unknown Source)
            at java.sql.DriverManager.getConnection(Unknown Source)
            at java.sql.DriverManager.getConnection(Unknown Source)
            at org.hsqldb.util.ConnectionDialogSwing.createConnection(Unknown Source
    )
            at org.hsqldb.util.ConnectionDialogSwing.actionPerformed(Unknown Source)

            at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
            at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknow
    n Source)
            at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
            at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
            at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Sour
    ce)
            at java.awt.Component.processMouseEvent(Unknown Source)
            at java.awt.Component.processEvent(Unknown Source)
            at java.awt.Container.processEvent(Unknown Source)
            at java.awt.Component.dispatchEventImpl(Unknown Source)
            at java.awt.Container.dispatchEventImpl(Unknown Source)
            at java.awt.Component.dispatchEvent(Unknown Source)
            at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
            at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
            at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
            at java.awt.Container.dispatchEventImpl(Unknown Source)
            at java.awt.Window.dispatchEventImpl(Unknown Source)
            at java.awt.Component.dispatchEvent(Unknown Source)
            at java.awt.EventQueue.dispatchEvent(Unknown Source)
            at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)

            at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
            at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
            at java.awt.EventDispatchThread.run(Unknown Source)

     

Log in to post a comment.