Elegant groovy, using groovy + windows execution plan to achieve dynamic runstatusdb2 Database

Three-day Dragon Boat Festival holiday, I learned jruby and groovy. At last I thought groovy was easy to use. Using groovy is a bit like writing basic, and there are no more rules like Java. During development, code is used to describe the execution logic, rather than writing a large number of classes that comply with Java "patterns" to implement certain logic functions.

Groovy gives me the feeling that there are two words "elegant". In terms of coding, the Java workload is very tedious and groovy encapsulation is quite successful. Maybe the essence of AOP is here.

Every month, I perform the runstatus operation on the DB2 database, back up the statements, upload the statements to the target machine, and execute them. Every month, the statements to be executed every month change, because tables are constantly changing. This part of work is very mechanical. It used to be like writing something automatically. Unfortunately, I didn't do it... I just learned groovy and used it to implement it.

The following shows the groovy program db2runstatus. Groovy.

 

Import groovy. SQL. sqlimport Java. text. simpledateformat import Telnet. telnetsample // generate the script file def user = "User Name" def pass = "password" def schema = This. ARGs [0] def bartdateformat = new simpledateformat ("yyyy-Mmmm-dd-eeee"); def filepath = "D: /database maintenance/runstats/"// This directory requires that Def filedir = filepath + bartdateformat be created first. format (new date () def filename = filedir + "/" + "$ {schema }. SQL "def linesp ="/N "def fileobjectdir = new file (filedir) // No directory First, create the directory if (! Fileobjectdir. exists () fileobjectdir. mkdir () def SQL = SQL. newinstance ("JDBC: DB2: // 10.154.144.38: 50000/hndpdb", "$ {user}", "$ {pass}", "com. IBM. db2.jcc. db2driver ") file1 = new file (filename) // delete an existing file first if (file1.exists () file1.delete () SQL. eachrow ("select rtrim (tabschema) | '. '| tabname as table from syscat. tables where tabschema = '$ {schema}' ", {file1 <" runstats on table $ {It. table} on key columns with distribution on all columns and sampled detailed indexes all allow write access $ {linesp} "file1 <" reorg table $ {It. table} allow read access $ {linesp} "file1 <" reorg indexes all for table $ {It. table} allow read access $ {linesp} "file1 <" runstats on table $ {It. table} on key columns with distribution on all columns and sampled detailed indexes all allow write access $ {linesp} "}) SQL. close () // upload the script file def ftpname = filedir + "/" + "$ {schema }. FTP "ftpfile = new file (ftpname) // delete an existing file if (ftpfile. exists () ftpfile. delete () // generate the FTP script ftpfile <"" Open 10.154.144.40 $ {user }$ {pass} CD deep/tmppromptdelete $ {schema }. sqlput $ {filename} bye "// run the script def command =" ftp-S: $ {ftpname} "def proc = command.exe cute () Proc. waitfor () println "output: $ {Proc. in. text} "println" error message: $ {Proc. err. text} "// remotely execute sqltelnetsample Telnet = new telnetsample (" 10.154.144.40 "," $ {user} "," $ {pass} ") Telnet. sendcommand ("CD deep/tmp"); Telnet. sendcommand ("DB2 connect to hndpdb user $ {user} using $ {pass}"); Telnet. sendcommand ("nohup DB2-VF $ {schema }. SQL &");

 

In just 60 lines of code, the task from backup to FTP is implemented, Hoho ~ The execution method is also very simple. In CLI mode, groovy db2runstatus. Groovy [Table mode name] assumes that I want to execute the runstatus ==> groovy db2runstatus. Groovy ODS

In this way, a folder of the current date will be generated under the D:/database maintenance/runstats/directory, and a ODS will be generated under the folder. SQL file and ODS. after the FTP file is run separately and the FTP statement is run again, it will automatically telnet to the target machine and mount and execute it with nohup (here I log on to Aix, so I use nohup for reference by other systems)

Then write the complete call method into a bat file run. bat.

groovy DB2Runstatus.groovy ODS groovy DB2Runstatus.groovy DW groovy DB2Runstatus.groovy WI 

Configure it to the Windows execution plan and then click OK.

The Groovy environment must be JDK> = 1.5 and the environment variable % grrovy_home % is configured.