How to Manage All of Your Databases From a Single Client: DbmsClient
In this article, see how to manage all of your databases from a single client, DbmsClient.
Join the DZone community and get the full member experience.Join For Free
If you ever worked with multiple databases, you know that querying them all at the same time is a common situation. This can happen if you have the same table over multiple databases or if you want to transfer data between databases. The scenario in which you want to schedule these tasks is also very plausible.
Doing this without any helping tool can be very frustrating. If you schedule a task and have an error in your query, you will only find out when you return to the machine. I have encountered many scenarios like the ones mentioned above and found a free tool that provided me the help I needed.
DbmsClient is a free product of Wise Coders that helped me manage multiple databases from a single place. This tool was especially useful for scheduled tasks. If one of them crashes,
DbmsClient sends the error via email so you can take action and solve the problem in time.
Another common scenario is when you want to send tasks to your databases that cannot be solved using SQL only. In this case, you might want to use scripts in Python or Groovy, to execute simple logic. This scenario can be also solved using DbmsClient.
Let’s see what other features DbmsClient has to offer:
1. Define Connections
When you open the program, DbmsClient executes the init.sql file. From here, you can define your database connections:
The tool can automatically download the driver for any database if you add the command:
Where ‘rdbms’ has to be replaced with a database name from the list. Note that the command is case sensitive.
2. Query Multiple Databases
As mentioned in the beginning, you can query multiple databases at the same time. In order to do this, you have to make sure that the tool is connected to every database you want to query.
For often-used databases, you can create a connection group that includes them. For example:
Also, you can spool the result from a specific query to a file:
3. Transfer Data Between Multiple Tables
You can transfer data into the currently connected database from the specified databases using a reader query (executed on the from databases). The column names should match between the source and the target. There can be multiple source databases.
4. Write Python/Groovy Scripts
Many database tasks require more logic than simple SQL and in DbmsClient you can execute scripts using JPython or Groovy libraries. The code can be started with the keywords python or groovy and ended with only a slash ‘/’.
5. Chunk Database Update
You can update or delete large amounts of information by splitting them into smaller chunks. This avoids database locks caused by disk or memory issues. The documentation provides examples of how it can be done using Python or Groovy.
6. Create Custom Commands
In DbmsClient, you can define your commands and execute them on multiple databases. Find more details in the documentation.
7. Write Cronjob Scripts
Using DbmsClient you can execute database scripts on a regular basis with a minimum resolution of one hour. If a script is failing, an email will be sent to the configured emails. The scripts can be SQL or Groovy as described before.
DbmsClient is developed by Wise Coders, a company that has a long history in providing great tools for database design and management such as DbSchema.
Opinions expressed by DZone contributors are their own.