Databases
Table of contents
JMap Server manages database connections by placing them in pools. The pools contain a certain number of open database connections. These connections are shared with the entire system and used according to database access needs.
All the database connection pools (hereafter called databases for simplicity) are centrally managed using JMap Admin. Databases are typically used by JMap Server to read spatial data stored in tables and to access descriptive data related to spatial data. Once databases are configured in JMap Admin, they can be used from other sections and for various purposes in the administration process.
The Databases section in JMap Admin is used to display a table of existing databases and to show their statuses.
System Database
JMap has a System database that contains JMap Server’s configurations and geometry tables. This database is mandatory and generally should not be modified. It cannot be deleted and should only be manipulated by advanced users. See section JMap Server System Database for more information.
Creating Databases
To start creating a new database connection, press the Create button in the page listing the databases and follow the required steps.
| Identification | |
|---|---|
| Name | Enter a name for the new database. The name must be unique. |
| Description | Optionally enter a description for the new database. The description can only be viewed by administrators in JMap Admin. |
| Connection parameters | |
|---|---|
| Driver | Select the driver for the database system you wish to connect to.You can add new drivers by creating new configuration files in the JMAP_HOME/conf/db directory on the server. |
| Host | (Depending on the selected driver) The name or TCP/IP address of the database server. |
| Port | (Depending on the selected driver) The TCP/IP port of the database server. |
| Database | (Depending on the selected driver) The name of the database to access, as defined in the database server. |
| Folder | (Depending on the selected driver) The path to the database folder. |
| Extra parameters | In some rare cases, database systems may require additional parameters. They can be entered in this field. |
| Username | Enter a username for the database connection. The authentication of domain users is not supported; you must make sure your database allows database authentication. |
| Password | Enter a password for the database connection. |
| Connections | |
|---|---|
| Number of connections | Enter the initial size of the pool of connections. This determines how many connections to the database will be kept open. |
| Maximum connections | Enter the maximum number of connections allowed for this pool. It must be equal to or greater than the initial number of connections. If the pool needs to be increased, new database connections will be created automatically until the maximum value is reached. After a certain period of time, additional connections are automatically closed, and the connection pool returns to its initial size.The maximum value can be disabled by unselecting the check box, allowing the pool to increase without restriction. |
Advanced parameters
The following advanced parameters usually don’t need to be modified.
| Parameters | |
|---|---|
| Additional parameters | In some rare cases, database systems may require additional parameters. They can be entered in this field. |
| Validation query | JMap uses a query validation mechanism to validate database connections. This query is used to test the connection each time a query needs to be executed. If the validation query fails, which typically means that the connection to the database is broken, JMap Server will try to establish a new connection automatically. This mechanism ensures the database connections are always working. The query must be valid and very quick to execute. In order to reduce the impact on performance, make sure you minimize the number of returned records (zero is perfect) and use indexed fields in the WHERE clause. Default queries are provided. They normally don’t need to be changed. |
| Connection timeout | The connection inactivity timeout is used to close and reopen connections that are inactive for a long period. This mechanism ensures that the database system does not close inactive connections. Make sure that this value is lesser than the connection timeout of your database system. A default value of 2 hours is appropriate most of the time. |
| Connection wait timeout | Sets the wait timeout value for database operations. This parameter can be lowered to prevent slowdowns or deadlocks in situations where the database server is taking too long to respond, or increased to allow longer wait periods so that queries with long processing time can be successfully executed. The default value is 60 sec. Enable this option to specify a different value for this connection. |
| Connection type | Specify the connection type to create. Generic connections are all created with the same user information (username and password) defined earlier. From the perspective of the database system, it is as if the same user performed all queries. This type of connection is used most of the time. Identified connections are created on the fly for each user connected to JMap Server. The same connection is reused during the session for each user. From the perspective of the database management system, each query is done by the user who is connected to JMap Server. This connection mode is useful in environments where security is managed at the database level. In order for the identified connection mode to work, it is required that JMap and the database system share the same list of users. This can be the case when the Oracle user manager module is used to manage users. |
Configuring and Managing Databases
When you click on the name of a database in the Database section, the Database configuration interface displays.
This interface has buttons to Edit, Reinitialize, Deactivate or Delete the database. The 
button allows you to access the SQL Console and Permissions sections.
Several subsections display information on the database. The parameter values indicated were defined when the database connection was created. Refer to Creating Databases for the details of each parameter. These parameters can be modified by clicking on Edit.
General information
This subsection displays the name, Id (internal JMap identifier), a description, and the database owner.
Database connection pool
This subsection displays the details of the connection pool. The parameters indicated are the following: Initial size, Maximum size, Pool usage, Pool usage peak, Connection timeout, and Connection wait timeout.
Database connection
This subsection displays the details of the database connection. The parameters indicated are the following: Status, Database (indicates the type), Driver, Connection string, Username, Validation query, and Connection type.
References
This subsection shows all the resources using the data from the database. The information in this section is presented hierarchically and displays the spatial data sources as well as the projects with the layers, reports, and forms, all showing the attributes used. This can be useful to view the resources that would be affected by changes made to the database.
Reinitializing databases
Reinitializing a database closes all open connections and creates new ones. This can be useful to force a reconnection to a database system.
Deactivating databases
Deactivating a database closes connections to the database without deleting its configuration. JMap Server can no longer query the database. Deactivating is useful when the database stops responding and causes delays in the local server.
Deleting databases
Deleting a database deletes the connection configuration for the database system. The data contained in the database is not affected at all.
Database statuses
Each database has a status. The status indicates the condition of the database connection. The following table describes the possible statuses of a database.
| Statuses | |
|---|---|
| Error | The database connections are broken. The database cannot be used until the error is resolved and the connections are reopened. Reinitializing the database might correct the problem. You can view a description of the error by clicking on the word Error displayed in red. |
| Inactive | The database connections are closed but they are configured. JMap can no longer query the database. |
| Connected | The database connections were created successfully and are ready to be used. |
SQL Console
JMap Admin provides a generic SQL console that can be used to view database structures, execute SQL queries, inspect the content of tables, test for query execution speed, etc. All configured databases are accessible through this console.
When performing an SQL query, the query execution is subject to security permissions granted to the user connected to the database. See Creating Databases section for more information about specifying the user for database connections.
| SQL console | |
|---|---|
| Database | Select the database to use. |
| View structure | Click on  to open a window that allows you to navigate in the structure of the database. You can see the schemas, the tables and views, and information about each field of a table or view. |
| Max. rows | When executing a SELECT SQL query, you can enter a value to limit the number of returned rows. |
| Auto commit | If performing SQL transactions (e.g. INSERT, UPDATE), select this option to validate the transactions automatically (SQL COMMIT command). Otherwise, you will need to perform the validations manually. |
| Test for speed only | When executing an SQL query, select this option to repeat the query a certain number of times and display the execution times. |
| SQL Query | Enter the SQL query to execute. The result will be displayed in a table. |
Query results are displayed in a new window. The results of the INSERT, UPDATE, and DELETE requests are grouped in the same tab. The results of SELECT type queries appear in separate tabs.