A Groovy time with Oracle and Hudson/Jenkins - poor mans batch

This is an example of a relatively simple solution to automating batch processes using tools at hand.  I hope someone else finds this useful.

The tools:

• Hudson/Jenkins with plugins
• Hudson Groovy Builder
• Time stamper
• Glassfish 3
• Groovy 
• Oracle
• Linux
• VMWare

You may ask why someone would do this?

• Leverage existing staff skills and tools
• Provides some security features (project based matrix security) to help provide limited access to a variety of staff
• Provides an easy to use web interface for running and checking job status
• Provide log of job output
• Relatively easy to implement disaster recovery
•  

 What are the downsides?

• Development/testing of Groovy jobs is challenging if not done in an IDE which supports Groovy or if leveraging Hudson specifics.  Making changes directly in a Hudson job and rerunning it is time consuming and error prone.
• Additional development challenges if using J2EE container/JNDI to provide DB connections.
• This is not a true enterprise scheduler so handling dependencies and other complicated work flows is difficult at best.
• This example was only intended to run on the master node - no attempt was made to support running jobs on remote nodes.
• This example puts the Groovy code directly in the job definition; it may be better to define the job as a script under source control and have Hudson/Jenkins check out the script for execution.  The trade offs need evaluation.

This doesn't cover every detail but will pull in a number of details which took some research and trial/error.  I am fairly new to Groovy so this was a bit of an experiment. As an experiment I try to demonstrate a Groovy variation on some other activities which I have not seen in Groovy along with some other more common items documented on other Groovy relates sites.

The starting point for this was a Linux server running as a VMWare guest and a recent Sun JDK installed.  Glassfish was then installed.  The Oracle ojdbc6.jar file was then added to the $GLASSFISH_HOME/glassfish/lib directory.  A copy of Groovy was installed/extracted on the server.  Follow Glassfish install instructions and then start server.  The Hudson/Jenkins war file was then deployed by copying to the Glassfish autodeploy directory. 

Next configure Glassfish; select "Enable Security", define the security realm and pick the Project-based Matrix Authorization Strategy.  Make sure you either initially give "Anonymous" full rights or add yourself with all rights before saving changes else you risk not being able to further configure things.
Next add the plugin "Hudson Groovy Builder plugin" and restart Glassfish (depending on versions of various things you may be able to simply tell Hudson/Jenkins to restart).  In the Hudson/Jenkins configuration page, add a Groovy installation and provide the path where you extracted Groovy.  


In the Glassfish admin console (or using the command line tools), define a connection pool named "MYDB" of resource type "javax.sql.DataSource" and DS classname of "oracle.jdbc.pool.OracleDataSource".  Set the pool size to something reasonable.  I provided an idle timeout of 300s and max wait time of 60000ms.  No transaction settings are selected. On the next conn pool tab, select "wrap jdbc objects" and "Pooling".  I have "validate at most once" set to 60s and a leak timeout of  600s.  The rest of the settings on the tab are defaults. On the "Additional properties" tab, I updated the list so only the properties "User", "Password" and "URL" exist and the URL is of the general form: jdbc:oracle:thin:@<server>:<port>:<service>.  If on the first tab you have "Ping" enabled - you can click the "Ping" button and should get a success message if the pool is configured close to correct (so the system can at least find and communicate with the DB).



Now create a JDBC Resource (i.e.associate pool to JNDI).  Fill in the name with "jdbc/mydb" and make sure the pool name is that of the pool you just created.  Status should be "enabled".



Let's create an example job now.  In Hudson/Jenkins, create a new job (pick free-style software project) -and call it something like GroovyBatchTest.  Configure the job; select "Enable project-based security" and add yourself/others with reasonable rights.  Pick "Build periodically" and set schedule to whatever is appropriate (uses cron style schedule definition). I also select "Add timestamps to the console output" which I find somewhat helpful in analyzing/debugging things.

Select Execute system Groovy Script, Groovy Command.   Please note this code was just hacked together as a demonstration only and has roots in a number of other blogs/websites - there is little original content on my part.  I will try to locate and reference some of the original websites which I pulled some of this general information from.  I will clean up the code when time permits.  In this example, I translated some stuff into Groovy which allows getting Oracle DBMS_OUTPUT.PUT_LINE data back via JDBC and also demonstrate some remote procedure calls and other Groovy/JDBC access methods. 

import javax.naming.*
import javax.sql.DataSource
import groovy.sql.Sql

class DbmsOutput
{
      private groovy.sql.Sql sql = null;
      public DbmsOutput(groovy.sql.Sql conn)
      { 
           sql = conn;
       }

       public void enable(int size)
       {
           sql.call("begin dbmss_output.enable(?); end;", [size]);
        }

       public void disable()
       {
             sql.call("begin dbms_output.disable; end;");
        }

        public String show()
        {
              def stmt = "declare "+
                   " l_line varchar2(255); " + 
                   " l_done number; " +
                    " l_buffer long; " +
              "begin " +
                "   loop " +
                 "      exit when length(l_buffer) + 255 > ? or l_done = 1; " +
                  "      dbms_output.get_line(l_line, l_done); " +
                  "      l_buffer := l_buffer || l_line || char(10);  " +
                 "   end loop; " +
                  " ? :=  l_done; " +
                  " ? := l_buffer; " +
               "end; " ;

              def done = 0;
              def dta = "";

               for (;;)
               {
                      sql.call(stmt, [32000, Sql.INTEGER, Sql.VARCHAR]) { d,txt ->
                           done = d;
                           dta = dta + txt + "\n";
                       }
                      if (done == 1)
                        {
                              break;
                        }

                       return dta;
                   }
              }
 
           DbmsOutput dbout = null
           groovy.sql.Sql sql = null

            try
             {
                     Context initCtx = InitialContext();
                      DataSource sqlDS = initCtx.lookup("jdbc/mydb");
                      sql = new groovy.sql.Sql(sqlDS);

                      def cur = sql.firstRow("select id, cnt from cur_process_tbl where ts = (select max(ts) from curr_process_tbl)")

                      dbout = new DbmsOutput(sql);
                       dbout.enable(4000);

                        sql.call("  begin " +
                                            "  mypkg.proc1(?, ?); " +
                                     "end;", [cur.id, cur.cnt])

                       curOutput = dbout.show()
                        println "data:" curOutput

                        dbout.disable()
                
                         sql.executeUpdate("update out_tbl set output = ? where id = ?", [curOutput, cur.id])
                    
                         sql.commit();
                  }
                  catch(Exception e)
                    {
                        if (sql != null)
                          sql.rollback()
                         println "error occurred:" + e
                   }
                 finally
                {
                         if (sql != null)
                              sql.close()
      }


In this example, my Oracle package "mypkg" with procedure "proc1(?, ?)" would contain DBMS_OUTPUT.PUT_LINE calls which produce data which is dumped in this example

If you now save the job and do a "build now", if all was done correctly you should see your DBMS_OUTPUT.PUT_LINE dumped to the console output of your jobs build-run.

Regarding disaster recovery(DR), there are a good number of implementation variations.  A simple way to handle a mostly warm setup where all DR resources are separate and distinct from production is to have a matching DR instance of the Hudson/Jenkins server preconfigured the same as production except for resources such as JDBC/JNDI resources and it should probably reference a DR source control server if one is in use. In the case were DR databases are also warm and distinct from production, you can preconfigure JDBC/JNDI resources in Glassfish to refer to the DR database.  If you keep job definitions in sync between production and DR (or put most of job in script in source control) and the job only acquires DB resources via JNDI then the unmodified job will work in both production and DR environments.  The main caveat is regarding jobs running on a schedule - if you don't want DR jobs running when no DR event occurred then you must disable jobs or alter the schedule.  Note that server virtualization and NAS storage were very helpful in handling some of the details.