The Predictive Cloud Computing system used
DB2, a relational database to store sporting tournament state Our primary interface to
and from DB2 was through Java’s Persistence API. We also used Liquibase to update the
DB2 database schemas and dataI’m Aaron Baughman, a senior technical staff member with IBM sporting
and entertainment practice.Liquibase provides traceable trackable and repeatable versioning
of data and schema changes to a database. We created changesets and we stored them within
our source codes repository called Git. On my screen, you can see over on the left side,
all my package structures for my java project called persistence. Down in source main resources
here, I have my change logs and I also have my Liquibase.properties and this tells the
command line Liquibase how to connect to the appropriate database system. So, for example
here you can see within the Liquibase properties file I’ve defined my driver, which is a DB2
standard driver that’s managed by Maven. So I have to set the class path to tell Java,
or in this case Liquibase, where my Liquibase jar file is. See, here’s it’s in my home directory’a0
down into which is the standard repository location for Maven and I define the path all
the way to the called 3.63.75 jar And then issue the URL. In this is my JDBC connection
to DB2. And it’s through my local host port 6004, what I’ve done is I’ve tunneled into
the appropriate machine by using local host on port 6004. So, no, the database isn’t running
on my local host, but I just proxy all the way through to my tunnel which is double tunneled
into a green zone on the IBM network. And then, the database name is EI Blue database
then I issue the credentials which is the username EI Blue US and of course a password
which I’m not going to show in clear text. You store the different changes within change
sets. So the master database which is put within xml I define the database change law
here. And, I can include any files that have that have change sets that I want. And the
changesets can be changes to a schema, or it can be data type changes. You can have
end of seas. Any sorts of standard database configurations you can put here. So if I go
to my change log version 1.0. I can show you a couple change sets that we’ve defined. Here
we have the author, the ID of the change set and a comment. which describes what the change
set does. Here we’ve migrated over to a new DB2 and I want to add the 2015 master site
information which says, ok, this is the masters in 2015, the URL for the site, the latitudes
and longitudes for where the precise location of the Augusta golf club is and the tournament
scheduled start time and the preliminaries or in this case the practice rounds start
and stop time. Here I’m also adding in the 2015 Wimbledon site. We do this for every
single site and we want to capture all the changes that we’ve made. Here you can actually
see where we have updated existing rows within a table. So I’m looking for a USGA in year
2015 and I just want to update the preliminary start and end times. There aren’t any preliminaries
for USGA you just set it to the default 1970.So here is where I might want to delete rows
within a table called Reach. And this is my where clause. K. And instead of having all
different types of scripts like I have here, which is the old way that projects might do
it. We in turn would take any kind of SQL we might have, which might look like alternating
tables, inserting in the committee and put it over in change sets and this runs every
single time they issue a query or an update SQL or any other kind of Liquibase type command.So
one such command, open up my terminal here. As we can see here is that I have my change
log here in this directory, the change log directory, and then for my change log directory,
I then in turn only want to run just a couple of change sets. I don’t want to rerun a change
set every time. Now what’s nice about Liquibase is that it takes into account that forms.
So here, what I’m doing is I issue the Liquibase. So, I have to install Liquibase on my machine
and then I say where the change log file is and the one I want to run is the DB change
log master, the one I just showed you. that includes any other change log files that I
want to run. and then I can optionally define the defaults file that has all the configuration
that I need to connect Liquibase to a remote machine which includes the URL, the credentials,
the driver, and driver paths. So if I go ahead and run this and connect it all the way up
andI connect it through to my double tunnel which is called z10095 which is actually running
the database server. And it looks at an internal table which keeps track of which changesets
have already run. So, in this case it’s already ran all the different type of change sets
that I have, but because I issued the command update SQL then it’s going to show you the
SQL that would have actually run. Right, so here, what it shows you is the only one that
actually would have run would be the update change set set to lock so we know that we’re
updating the database so that no other Liquibase change command could run at the same time.
and it puts the time stamp of which we’re going to run. And then it goes ahead and alters
different tables and database change log. So we’re going to add a label and add that
which is deployment ID, but you’ll notice that none of the custom SQL that I put in
the change sets are going to be run and that’s because they already ran on that table and
it’s up to date. So if I were to add in another change set and rerun this update SQL then
we could see the actual SQL that it would run. You can optionally have Liquibase run
it for us, or you can manually run it via the command line And then over on DB2, this
is the actual log table that is created the first time you run Liquibase. So here I’m
describing the schema EIBlueUS databasechangelog and I can see all the IDs, filenames, authors,
all the date executed all the different types of metadata about the Liquibase ran and stored
in this table. This table is what is used to lock this database. It is used to make
sure we don’t rerun superfluous commands and that it only runs one time.In essence we found
in this project that Liquibase is key and fundamental for us to go ahead and manage
our database.

Tagged : # # # # # # #

Dennis Veasley

Leave a Reply

Your email address will not be published. Required fields are marked *