DbShell is a command-line client, separate from DbSchema. It is useful in Linux environments, where you have only command prompt access. It is also useful to manage clusters of databases and:
Before creating database connections, you have to download the JDBC drivers for the databases we are working with. For this execute in DbShell the command:
download driver <rdbms>
The 'rdbms' is one of database names from this list, case sensitive.
Example: download driver PostgreSQL or download driver MySql. The drivers will be downloaded in C:\Users\YourUser\.DbSchema\drivers. Alternatively, download the JDBC driver .jar files by yourself and place them in the /home/users/.DbSchema/drivers/<rdbms>
In the User Home Directory (C:\Users\YourUser or /home/YourUser ) DbShell is creating a folder .DbSchema/dbshell. with a file init.sql. DbShell is executing this script at startup. So is easy to define the database connections right in this file.
Example:
register driver PostgreSql org.postgresql.Driver jdbc:postgresql://<host>:<port>/<db> "port=5432"
connection pos1 PostgreSql "user=postgres password=secret host=localhost db=sakila"
register driver Oracle oracle.jdbc.OracleDriver jdbc:oracle:thin:@<host>:<port>:<db> "port=1521"
connection ora1 Oracle "user='sys as sysdba' password=secret host=188.21.11.1 db=prod"
The register driver command defines for each RDBMS (MySql,Postgres,Oracle...) the:
The connection command defines the connection by setting the real values for each URL parameter. The RDBMS from the register driver command should match the RDBMS from the connection command.
The complete list of known drivers is the same as for DbSchema.
This operation assumes that the queried tables exists on each of the connected databases. The connections 'pos1' and 'pos2' should be already defined.
connect pos1,pos2
SELECT * FROM sakila.city;
Create connection groups.
connection group production pos1,pos2
connect production
SELECT * FROM sakila.city;
Spool the result to a file.
connect production exclude my3
spool /tmp/result.csv
SELECT * FROM sakila.city;
spool off
vset spool.separator=|
vset format.date=YYYY-MM-dd'T'hh:mm:ss'Z'
vset format.timestamp=YYYY-MM-dd'T'hh:mm:ss'Z'
vset replace_variables=true
vset myid=6230
connect db4
execute export.sql
Where the export.sql script is :
spool /tmp/export_&myid.csv
select * from some_table where id=&myid;
connect pos1
transfer into_table from pos2,pos3 using
select id, firstname, lastname from persons;
The transfer is running on a configurable number of threads.
vset transfer.threads=4
Execute 'help' to see all configurable parameters.
There is one reader thread and multiple writer threads. Each writer thread is writing a bunch of rows, then it commits the transaction. The writer threads are writing between 500 and 100.000 records, depending on the number of transferred columns ( 500 for more than 50 columns, 100.000 for 1 column ).
Many database tasks require more logic than simple SQLs. We do this using Groovy, a pure Java scripting language with closures. Groovy code can be started with the keyword groovy and ends with the first line with only a slash '/'.
Everything which works in Java is working in Groovy, with these improvements:
connect db1
groovy
int days = 5
sql.eachRow( """
SELECT personid, firstname, lastname FROM persons p WHERE p.created < sysdate-${days} AND
EXISTS ( SELECT 1 FROM sport_club s WHERE s.personid=p.personid )
ORDER BY personid ASC
""".toString() ) { r ->
println "${r.personid} ${r.firstname} ${r.lastname}"
}
/
Groovy scripts receive two objects :
Updating or deleting large amounts of data may require splitting the operation into smaller chunks. This because the large operation may cause disk or memory issues and may lead to database locks.
groovy
import java.sql.ResultSet
sql.setResultSetHoldability( ResultSet.HOLD_CURSORS_OVER_COMMIT)
def i = 0
sql.eachRow( "SELECT personid FROM persons".toString() ){ r ->
sql.execute("UPDATE persons SET ocupation='t' WHERE personid=?".toString(), [ r.personid ])
i++
if ( i > 1000 ){
sql.commit()
i = 0
print "."
}
}
sql.commit()
Here is an deletion example:
groovy
List ids = new ArrayList()
sql.eachRow( 'SELECT id FROM large_table' ) { m ->
ids.add( m.id )
}
int cnt = 0;
for ( int id : ids ) {
sql.execute('DELETE FROM large_table WHERE id=?', [id] )
cnt++
if ( cnt > 1000) {
print '.'
sql.commit()
cnt = 0;
}
}
/
def cli = new CliBuilder(usage:'disk usage ')
cli.h(longOpt:'help', 'Show usage information and quit')
def options = cli.parse(args)
if (options.'help') {
cli.usage()
return
}
sql.execute("DROP TABLE IF EXISTS dbshell_disk_usage")
sql.execute("DROP TABLE IF EXISTS dbshell_disk_usage_logs")
sql.execute("CREATE TABLE dbshell_disk_usage( filesystem text, blocks bigint, used bigint, free bigint, percent text, mount_point text )")
sql.execute("CREATE TABLE dbshell_disk_usage_logs( blocks bigint, folder text )")
sql.execute("COPY dbshell_disk_usage FROM PROGRAM 'df -k | sed \"s/ */,/g\"' WITH ( FORMAT CSV, HEADER ) ")
sql.execute("UPDATE dbshell_disk_usage SET used=used/(1024*1024), free=free/(1024*1024), blocks=blocks/(1024*1024)")
String dbid = sql.firstRow( "SELECT current_database() as dbid").dbid
sql.execute("COPY dbshell_disk_usage_logs FROM PROGRAM 'du -m -s /data/${dbid}/pgsystem/pg_log | sed \"s/\\s\\s*/,/g\"' WITH ( FORMAT CSV ) ".toString())
sql.execute("COPY dbshell_disk_usage_logs FROM PROGRAM 'du -m -s /data/${dbid}/jobsystem/pg_log | sed \"s/\\s\\s*/,/g\"' WITH ( FORMAT CSV ) ".toString())
int logBlocks = sql.firstRow( "SELECT sum(blocks) as usage FROM dbshell_disk_usage_logs").usage
sql.eachRow( "SELECT * FROM dbshell_disk_usage"){ r->
if ( r.filesystem.startsWith( '/dev/mapper') ){
int freePercent = Math.round(r.free*100/(r.blocks))
String status = ( r.free < 30 && r.blocks > 1500 ) ? 'Very Low' : (( r.free < 50 && r.blocks > 1000) || r.free < 30 ) ? 'Low' : "Ok"
String logStatus = logBlocks > 1024 ? String.format( 'pg_logs %dM', logBlocks ) : ''
println String.format( '%10s Free %3d%% ( %dG out of %dG ) %s', status, freePercent, r.free, r.blocks, logStatus )
}
}
sql.execute("DROP TABLE dbshell_disk_usage_logs")
sql.execute("DROP TABLE dbshell_disk_usage")
There are cases when you have to manage large database clusters where you have primaries and standby databases. I used to manage a cluster where the primary database hostnames were like 'pos1.db', 'pos2.db', ... and standbys were 'pos1.sbydb', 'pos2.sbydb'...
For this I let this script to create the standby database connections:
groovy
import com.wisecoders.dbshell.connection.*
for ( int i=1;i <130; i++){
ConnectionFactory.createConnector( "pos${i}", "postgres", "user=postgres host=pos${i}.db db=sakila${i}" )
}
ConnectionFactory.addSuffix( new Suffix("sby"){
public Connector create( Connector con ){
String host = con.getHost();
if ( host.endsWith(".db") ) return duplicate( con, "host=" + host.replaceFirst(/\.db/, /.sbydb/))
return null
}
})
The script will receive a connection. If the connection is matching the name .db,
will duplicate the Connector by creating a new parameters string, where .db is replaced with .sbydb.
vset email.server.host=internal.mailserver
vset email.server.user=admin
vset email.server.password=secret
vset email.from=Admin
vset email.alert=admin@databaseconsole.com
vset cron.folder=/dbshell/cronscripts
On the machine where DbShell is running, add in /etc/crontab this line, fixing fist the path to DbShell
01 * * * * /usr/local/bin/dbshell -cron
And in /dbshell/cronscripts/ save the script files. Example:
customer_report.daily0-24hour.sql
customer_report2.monthly1hour.sql
customer_report3.1day_of_week1hour.sql
DbShell is checking the file names matching this pattern:
upload sftp://ftpusser@ftp.ftphost.com/intoFolder report.csv
For many reasons, you may need to store passwords outside the init.sql file, where the database connections are being defined. This would allow saving the init.sql file in GIT without making passwords public. Therefore is possible to store passwords in a separate file ~/.DbSchema/dbshell/pass.txt or ~/.pgpass (for compatibility with PostgreSQL standards). The file format is the same as the PostgreSQL pgpass
hostname:port:database:username:password
Here we set the password 'secret' for all hosts, port 1521 user 'postgres'.
*:1521:sakila:postgres:secret
The connection should specify password=<pgpass> in the connection parameters to make use of the password from the pass.txt or .pgpass file.
DbShell can be started with the following parameters: