Editing Schema Connection Settings
You can edit the connection settings for an Instant JChem Schema. This allows you to change the database connection details and remove any stored usernames and passwords so that different ones can be used next time you login. This is only really useful for remote databases as all settings for a local database are usually implicitly defined.
You can only edit the connection settings when you are disconnected from the Schema. You can disconnect from a Schema by right clicking in its node in the project explorer and choosing 'Close connection...'.
When disconnected from the Schema right click on its node in the project explorer and select 'Connection settings...'. A dialog will open that lets you edit the settings.
In this dialog you can edit the connection details and specify that you do not want to store your usernames and passwords. You will be prompted for those that are not stored next time you connect. This allows you to change a username of password that might have been stored. You can also specify whether you want to be connected automatically when IJC starts.
Disabling the save password checkbox when connecting to IJC schema
If you have configured a security template to be used with IJC schema, you will be asked for login credentials every logon unless you want to store them. However for the security reason you might want to prohibit the password to be stored in IJC permanently. It can be configured in the *.ijs schema configuration file which is located in IJCProjects/YourProject/.config/YourSchemaName.ijs . If you include the following line in the file, it will prevent the IJC password to be remembered next time. Note that it does not affect passwords used for connecting to a database.
<entry key="disable.ijc.password"/>Configuring the connection pool for a schema
Usage and access patterns are different across organisations, hence the ability to configure IJC connection pool settings to achieve the most performant outcome for all users is explained. This configuration is supported in Derby, MySQL and Oracle but since Oracle and MySQL are multi-user then this aspect should also be considered. The connection pool settings defined in GenericObjectPool are overridden by the IJC application and are also user configurable by editing the client side *.ijs file for each schema. Configuration of schema settings is stored in IJCProjects/YourProject/.config/YourSchemaName.ijs . Custom connection pool settings are added manually to this file as new lines. You can potentially make data retrieval faster if you properly specify the behaviour of concurrent connections.
An explanation of each parameter you can set is given here.
The following properties are available to be set for each IJC schema. The values listed are the defaults set by IJC:
<entry key="custom.connection.pool">true</entry><entry key="custom.connection.pool.maxActive">6</entry><entry key="custom.connection.pool.whenExhaustedAction">1</entry><entry key="custom.connection.pool.maxWait">10000</entry><entry key="custom.connection.pool.maxIdle">1</entry><entry key="custom.connection.pool.minIdle">0</entry><entry key="custom.connection.pool.testOnBorrow">true</entry><entry key="custom.connection.pool.testOnReturn">true</entry><entry key="custom.connection.pool.timeBetweenEvictionRunsMillis">20000</entry><entry key="custom.connection.pool.numTestsPerEvictionRun">2</entry><entry key="custom.connection.pool.minEvictableIdleTimeMillis">120000</entry><entry key="custom.connection.pool.testWhileIdle">true</entry><entry key="custom.connection.pool.softMinEvictableIdleTimeMillis">-1</entry><entry key="custom.connection.pool.lifo">true</entry>A Schema's connection life cycle and it's tuning
It is advised to understand some of the key parameters involved which will most affect the overall performance. These parameters will apply to each IJC schema (configurable in .ijs), and affects how connections are obtained from the database. Note, there is usually a global physical limit on the number of RDBMS connections possible. This value might notionally be considered to be approaching (users * projects * operations). Please consult the vendor RDBMS documents for setting global limits. If you are a large organisation then it is advised to maximise the available connections. Overall, these settings are dependent upon these factors as well as local performance requirements and are thus always a "trade off".
The basic life cycle for a schema's interaction with connection pooling can be explained in terms of the key parameters involved. These parameters are either in units of time (ms) or Connection objects (Integer). This is not an exact science but is a trade off between the global available connections and the number of (users * projects * operations) in your organisation.
-
The connection pool is periodically checked (defined by A), for the number of Idle (and by deduction) Active connections that it has allocated to it's associated schema(s).
-
If any connections in the pool have been idle for a specified period (defined by B) then they become evicted and recycled back to the RDBMS. In conjunction with this continual cyclic check, further connection parameters are defined that can then govern overall performance.
-
The maximum open active connections ceiling limit (defined by C) has an internal IJC default setting of 5. This setting needs to be large enough not to block any requirement for parallelisation of operations (SQL) but not so big as to exhaust the globally available connections. So this can be increased but beware of global settings.
-
The maximum allowed open idle connections ceiling limit (defined by D) has an internal default setting of 5. If this is set too low then a possible large overhead of obtaining many new connections from the RDBMS to the pool is incurred rather than re-use already open but idle connection for new operations in the pool (SQL).
-
Minimum allowed idle connection (E). Internal default is 1. If set too large then potential global risk of running out of connections, by inhibiting legitimate connection recycle when usage is genuinely low. At least some (one) could always be available to avoid having to obtain a new one at any one time. Conversely, also consider the maximum global setting for open number of connections at the RDBMS level can influence results. If you have a really large number of users then maybe this should be set to zero.
Some examples and suggested behaviour
For example if we decide to reduce the pool size to 2 and prevent any idle connection being left in the pool we can add the following three elements to the ijs file. This is not likely to perform particularly well.
<entry key="custom.connection.pool">true</entry><entry key="custom.connection.pool.maxActive">2</entry><entry key="custom.connection.pool.maxIdle">0</entry>Another sample which shows that we create a pool with two connections and the eviction is disabled so the connection will permanently stay alive in the connection pool:
<entry key="custom.connection.pool">true</entry><entry key="custom.connection.pool.maxActive">2</entry><entry key="custom.connection.pool.minEvictableIdleTimeMillis">-1</entry>Another sample can be a more complex config like having a maximum of 8 connections in the pool but evict them quickly after they become idle. This allows the users that uses the 5.9+ on a multicore machine to benefit from parallelism in data retrieval when using charts, reports and personal server.
<entry key="custom.connection.pool">true</entry><entry key="custom.connection.pool.maxActive">8</entry><entry key="custom.connection.pool.timeBetweenEvictionRunsMillis">5000</entry><entry key="custom.connection.pool.minEvictableIdleTimeMillis">30000</entry>Also note that reducing the size of the pool will generally make the application slower. From IJC 5.9 parallel data retrieval for charts and other widgets will make use of many available connections.