{{announcement.body}}
{{announcement.title}}

How to Manage All of Your Databases From a Single Client: DbmsClient

DZone 's Guide to

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.

· Database Zone ·
Free Resource

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:

Java
 




xxxxxxxxxx
1


 
1
register driver PostgreSql org.postgresql.Driver jdbc:postgresql://<host>:<port>/<db> "port=5432"
2
 
          
3
connection pos1 PostgreSql "user=postgres password=secret host=localhost db=sakila".



The tool can automatically download the driver for any database if you add the command:

Java
 




xxxxxxxxxx
1


1
download driver <rdbms>



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:

SQL
 




xxxxxxxxxx
1


1
connection group production pos1,pos2
2
connect production
3
SELECT * FROM sakila.city;



Also, you can spool the result from a specific query to a file: 

SQL
 




xxxxxxxxxx
1


 
1
connect production exclude my3
2
spool /tmp/result.csv
3
SELECT * FROM sakila.city;
4
spool off



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.

SQL
 




xxxxxxxxxx
1


1
connect pos1
2
transfer into_table from pos2,pos3 using
3
select id, firstname, lastname from persons;



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 ‘/’.

Python example:

Python
 




xxxxxxxxxx
1
14


 
1
connect db1
2
 
          
3
python
4
statement = sql.createStatement()
5
res = statement.executeQuery("select * from some_table")
6
meta_data = res.getMetaData()
7
col_count = meta_data.getColumnCount()
8
col_names = [str(meta_data.getColumnName(i + 1)) for i in range(col_count)]
9
print "Column count:", col_count, "names:", col_names
10
while res.next():
11
    values = [str(res.getObject(i + 1)) for i in range(col_count)]
12
    print dict(zip(col_names, values))
13
/



Groovy example:

Groovy
 




xxxxxxxxxx
1
13


1
connect db1
2
 
          
3
groovy
4
int days = 5
5
sql.eachRow( """
6
    SELECT personid, firstname, lastname FROM persons p WHERE p.created < sysdate-${days} AND
7
      EXISTS ( SELECT 1 FROM sport_club s WHERE s.personid=p.personid )
8
      ORDER BY personid ASC
9
    """.toString() ) { r ->
10
    println "${r.personid} ${r.firstname} ${r.lastname}"
11
}
12
/



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.

Topics:
database ,database administration ,database administration and management ,database applications ,database connection ,database management ,database monitoring tools ,database optimization ,dbms ,tutorial

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}