Home > Blogs > VMware vFabric Blog > Author Archives: Pas Apicella

Author Archives: Pas Apicella

Hyperic monitoring its own Oracle 11g r2 repository

Below are some screen shots of what Hyperic 4.6.5 shows in regards to monitoring Oracle 11g RDBMS running on a VM running Redhat 5.7 guest OS. The database it's monitoring is the actual repository database being used by Hyperic itself. The screen shots below show the main dashboard with the Oracle 11g resource overall status as well as viewing a DB writer process for the running instance itself.

Img1

Img2

The plugin to monitor the Oracle instance is provided as one of the 80+ plugins available with Hyperic out of the box. Once the HQ agent discovers the Oracle instance we are left with simply providing the config to a DB user which has access to all data dictionary tables. The easiest way to do that is to ensure the user you connect wih has the following granted.

grant select any dictionary to {user};

To me given I own the VM / Oracle instance so I will use the system DBA user as the user to query the data dictionary as shown below. Once setup your good to go.

Img3

For more information on vFabric Hyperic use the link below.

http://pubs.vmware.com/vfabric5/index.jsp?topic=/com.vmware.vfabric.hyperic.4.6/vFabric_Hyperic_4.6.html

How to offload data from an existing data store and load it into SQLFire

vFabric SQLFire is a memory-optimized distributed SQL database delivering dynamic scalability and high performance for data-intensive modern applications. In this demo we load the Australian Football Leagure 2012 Fixture data into an SQLFire distributed in memory database.

A common requirement for using SQLFire is offloading data from an existing data store and loading it into SQLFire. This example show how you can load data into a SQLFire distributed system using Spring Batch from a CSV file and then how to present the data from a simple Spring MVC application. Finally the data is persisted to each SQLFire member to ensure we only need to load the data once and it will always be retained upon restarts of the distributed system.

Demo Setup

1. Download the zip file below and extract onto your file system. The demo scripts are written for a unix environment and tested on a MAC. Please convert the SH files to BAT files if on windows. The ANT commands will work whether your on windows or unix.

afl2012.zip

Note: The downloaded zip provides the JAR files for spring core/batch so there is no need to download those.

2. Ensure you have the JDK 6 in your path. It should work with JDK 1.5 but was never tested this JDK 1.5

[Mon Jan 30 09:55:39 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ java -version
java version "1.6.0_26"
Java(TM) SE Runtime Environment (build 1.6.0_26-b03-383-11A511)
Java HotSpot(TM) 64-Bit Server VM (build 20.1-b02-383, mixed mode)

3. Ensure Apache Ant is installed.

[Mon Jan 30 09:55:33 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ ant -version
Apache Ant(TM) version 1.8.2 compiled on June 3 2011

4. Verify you have SQLFire installed as shown below.

[Tue Feb 07 12:56:56 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101 ] $ d
total 8
drwxr-xr-x  10 papicella  staff  340 27 Jan 08:34 docs/
drwxr-xr-x   4 papicella  staff  136 27 Jan 08:34 bin/
drwxr-xr-x   6 papicella  staff  204 27 Jan 08:34 ../
drwxr-xr-x  15 papicella  staff  510 27 Jan 08:34 quickstart/
drwxr-xr-x  19 papicella  staff  646 27 Jan 08:34 lib/
drwxr-xr-x   7 papicella  staff  238 27 Jan 08:35 examples/
-rw-r–r–   1 papicella  staff  167 27 Jan 08:46 key.txt
drwxr-xr-x   9 papicella  staff  306 27 Jan 20:34 ./
drwxr-xr-x   7 papicella  staff  238  5 Feb 20:38 pasdemos/

Running the Demo

1. Ensure you have a clean environment by running "ant clean" as shown below.

[Mon Jan 30 09:59:12 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ ant clean
Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/build.xml

clean:

BUILD SUCCESSFUL
Total time: 0 seconds

2. Edit setup.sh to ensure you have placed SQLFire into your path as shown below.

export PATH=/Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/bin:$PATH

export CUR_DIR=`pwd`

export CLASSPATH=

3. Setup your environment by sourcing setup.sh as shown below.

[Mon Jan 30 10:14:05 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ . ./setup.sh

4. Run the "init" target to setup the required directories.

[Mon Jan 30 10:19:29 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ ant init
Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/build.xml

init:
    [mkdir] Created dir: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/server1
    [mkdir] Created dir: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/server2
    [mkdir] Created dir: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/classes
     [copy] Copying 1 file to /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/server1
     [copy] Copying 1 file to /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/server2

BUILD SUCCESSFUL
Total time: 0 seconds

5. Package the application using the "package" target as shown below.

[Mon Jan 30 10:24:30 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ant package
Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/build.xml

init:

compile:
    [javac] Compiling 5 source files to /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/classes
     [copy] Copying 1 file to /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/classes
     [copy] Copying 1 file to /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/classes

package:
      [jar] Building jar: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/lib/sqlfire-afl2012.jar

BUILD SUCCESSFUL
Total time: 0 seconds

6. Start 2 SQLFire membors as follows. These members are using a multicast port for discovery instead of a locator.

[Mon Jan 30 10:27:14 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ./start-servers.sh
Starting SQLFire Server using multicast for peer discovery: 239.192.81.1[12333]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1528]
Starting SQLFire Server using multicast for peer discovery: 239.192.81.1[12333]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1527]
SQLFire Server pid: 657 status: running
Logs generated in /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/server1/sqlfserver.log
SQLFire Server pid: 658 status: running
  Distributed system now has 2 members.
  Other members: 10.117.85.24(657:datastore)<v0>:13038/49735
Logs generated in /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/server2/sqlfserver.log

7. At this point lets ensure we can connect from a client using the script "test-connect.sh" if all went well it should show today's date.

[Mon Jan 30 10:29:01 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ./test-connect.sh
sqlf version 10.4
sqlf> sqlf> 1        
———-
2012-01-30

1 row selected
sqlf>
all done..

8. Lets view our 2 SQLFire members using the script "view-members.sh". This will show you the PID for each member in the distributed system

[Mon Jan 30 10:38:33 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ./view-members.sh
sqlf version 10.4
sqlf> sqlf> > Id                                              |NetServers                    |PID       
——————————————————————————————-
10.117.85.24(657)<v0>:13038/49735               |localhost/127.0.0.1[1527]     |657       
10.117.85.24(658)<v1>:22550/49734               |localhost/127.0.0.1[1528]     |658       

2 rows selected
sqlf>
all done..

9. Create the schema data running "create-schema-data.sh" as shown below. This will create the necessary tables and populate some of those with some known SQL data.

[Mon Jan 30 10:33:09 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ./create-schema-data.sh
……..
sqlf>
all done..

10. At this point we need to load a comma sperated CSV file into a temporary table using spring batch. This is all the 2012 fixture data which we will then massage into the real tables once it's loaded. Run then target "run-loadfixture" as shown below.

[Mon Jan 30 10:41:00 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ant run-loadfixture
Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/build.xml

init:

compile:

package:

run-loadfixture:
     [echo] Running load fixture with jvm args : -server -showversion -Xms512m -Xmx512m
     [java] java version "1.6.0_26"
     [java] Java(TM) SE Runtime Environment (build 1.6.0_26-b03-383-11A511)
     [java] Java HotSpot(TM) 64-Bit Server VM (build 20.1-b02-383, mixed mode)
     [java]
     [java] Jan 30, 2012 10:41:04 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh
     [java] INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@b32e13d: startup date [Mon Jan 30 10:41:04 EST 2012]; root of context hierarchy
     [java] Jan 30, 2012 10:41:04 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
     [java] INFO: Loading XML bean definitions from class path resource [application-config.xml]
     [java] Jan 30, 2012 10:41:04 AM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
     [java] INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@5e0feb48: defining beans [sqlfireUcpDataSource,fixtureDataReader]; root of factory hierarchy
     [java] Jan 30, 2012 10:41:05 AM vmware.au.spring.sqlfire.afl.LoadFixtureData run
     [java] INFO: Loaded 414 fixture_data records into sqlfire

BUILD SUCCESSFUL
Total time: 2 seconds

11. Now we massage the fixture data loaded from CSV into the real application table called "games". We do this using the ant target "run-loadgames" as shown below.

[Mon Jan 30 10:44:49 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ant run-loadgames
Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/build.xml

init:

compile:

package:

run-loadgames:
     [echo] Running load into games with jvm args : -server -showversion -Xms512m -Xmx512m
     [java] java version "1.6.0_26"
     [java] Java(TM) SE Runtime Environment (build 1.6.0_26-b03-383-11A511)
     [java] Java HotSpot(TM) 64-Bit Server VM (build 20.1-b02-383, mixed mode)
     [java]
     [java] Jan 30, 2012 10:46:05 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh
     [java] INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@5afec107: startup date [Mon Jan 30 10:46:05 EST 2012]; root of context hierarchy
     [java] Jan 30, 2012 10:46:05 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
     [java] INFO: Loading XML bean definitions from class path resource [application-config.xml]
     [java] Jan 30, 2012 10:46:06 AM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
     [java] INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@5e0feb48: defining beans [sqlfireUcpDataSource,fixtureDataReader]; root of factory hierarchy
     [java] round=1, team=Richmond, id=1, opponent=Carlton, id=4, venue=MCG, id=1
     [java] round=1, team=Western Bulldogs, id=16, opponent=West Coast Eagles, id=15, venue=Etihad Stadium, id=3
…..
     [java] round=23, team=Hawthorn, id=9, opponent=West Coast Eagles, id=15, venue=MCG, id=1
     [java] Inserted 198 Games…

BUILD SUCCESSFUL
Total time: 5 seconds

Viewing the Data

Below are 3 different ways to view the data in the distributed system. They are as follows.

  1. Using the sqlf command line client
  2. Using DbVisualizer (RDBMS Graphical Tool)
  3. Spring MVC WebUI

1. Using the sqlf command line client

Here we simply connect using the command line client provided with SQLFire.

1.1. Type in the following : sqlf
1.2. Connect using : connect client 'localhost:1527';
1.3. Query the current list of teams using : select * from teams;

[Mon Jan 30 10:50:35 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ sqlf
sqlf version 10.4
sqlf> connect client 'localhost:1527';
sqlf> select * from teams;
TEAM_ID    |NAME           |LONG_NAME                     |WEB_SITE                                         
————————————————————————————————————-
18         |Giants         |GWS GIANTS                    |http://www.gwsgiants.com.au/                     
1          |Tigers         |Richmond                      |http://richmondfc.com.au/                        
16         |Bulldogs       |Western Bulldogs              |http://westernbulldogs.com.au/                   
12         |Power          |Port Adelaide                 |http://portadelaidefc.com.au/                    
5          |Pies           |Collingwood                   |http://collingwoodfc.com.au/                     
17         |Suns           |Gold Coast Suns               |http://goldcoastfc.com.au/                       
4          |Blues          |Carlton                       |http://carltonfc.com.au/                         
2          |Crows          |Adelaide Crows                |http://afc.com.au/                               
10         |Roos           |North Melbourne               |http://kangaroosfc.com.au/                       
13         |Saints         |St. Kilda                     |http://saints.com.au/                            
8          |Cats           |Geelong Cats                  |http://gfc.com.au/                               
14         |Swans          |Sydney Swans                  |http://sydneyswans.com.au/                       
15        
|Eagles         |West Coast Eagles             |http://westcoasteagles.com.au/                   
11         |Demons         |Melbourne                     |http://demons.com.au/                            
7          |Dockers        |Fremantle                     |http://fremantlefc.com.au/                       
9          |Hawks          |Hawthorn                      |http://hawthornfc.com.au/                        
3          |Lions          |Brisbane Lions                |http://lions.com.au/                             
6          |Bombers        |Essendon                      |http://bombersfc.com.au/                         

18 rows selected

 

2. Using DbVisualizer (RDBMS Graphical Tool)

2.1. Setup DbVisualizer as shown in the BLOG entry below. This describes how to load the SQLFire client driver required to make a connection to our distributed system.

http://theblasfrompas.blogspot.com/2012/01/using-dbvisualizer-to-connect-to.html

2.2. Issue a query as follows to view games for "Richmond".

select g.round_ID, h.long_name "Home Team", a.long_name "Away Team", g.game_date, substr(v.name, 1, 20) "Venue", g.tv_network
from games g, teams h, teams a, venues v
where  (g.HOME_TEAM_ID = 1 or g.away_team_id = 1)
and g.home_team_id = h.team_id
and g.away_team_id = a.team_id
and g.venue_id = v.venue_id
order by g.round_id

Query Results

Img1

3. Spring MVC WebUI

It is assumed you already have a tcServer instance created and running, in this example the tcServer is named "sqlfire-demo"

3.1 Download the WAR file below onto your file system and unzip the file to reveal the WAR file.

application.zip

3.2. Create an instance as shown below.

[Mon Jan 30 12:10:42 papicella@:~/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE ] $ ./tcruntime-instance.sh create sqlfire-demo
Creating instance 'sqlfire-demo' …
  Using separate layout
  Creating bin/setenv.sh
  Applying template 'base'
    Copying template's contents
    Applying fragment 'context-fragment.xml' to 'conf/context.xml'
    Applying fragment 'server-fragment.xml' to 'conf/server.xml'
    Applying fragment 'web-fragment.xml' to 'conf/web.xml'
    Applying fragment 'tomcat-users-fragment.xml' to 'conf/tomcat-users.xml'
    Applying fragment 'catalina-fragment.properties' to 'conf/catalina.properties'
  Applying template 'base-tomcat-7'
    Copying template's contents
    Applying fragment 'server-fragment.xml' to 'conf/server.xml'
    Applying fragment 'web-fragment.xml' to 'conf/web.xml'
    Applying fragment 'catalina-fragment.properties' to 'conf/catalina.properties'
  Applying template 'bio'
    Copying template's contents
    Applying fragment 'server-fragment.xml' to 'conf/server.xml'
  Configuring instance 'sqlfire-demo' to use Tomcat version 7.0.23.A.RELEASE
  Setting permissions
Instance created
Connector summary
  Port: 8080   Type: Blocking IO   Secure: false

3.3.  Start the instance as shown below.

[Mon Jan 30 12:31:44 papicella@:~/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE ] $ ./tcruntime-ctl.sh sqlfire-demo start
INFO Instance name:      sqlfire-demo
INFO Script directory:   /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE
INFO tc Runtime location:/Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE
INFO Instance base:      /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE
INFO Binary dir:         /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/tomcat-7.0.23.A.RELEASE
INFO Runtime version:    7.0.23.A.RELEASE
INFO Script version:     2.6.3.RELEASE
Using CATALINA_BASE:   /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/sqlfire-demo
Using CATALINA_HOME:   /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/tomcat-7.0.23.A.RELEASE
Using CATALINA_TMPDIR: /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/sqlfire-demo/temp
Using JRE_HOME:        /System/Library/Frameworks/JavaVM.framework/Versions/CurrentJDK/Home
Using CLASSPATH:       /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/tomcat-7.0.23.A.RELEASE/bin/bootstrap.jar:/Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/tomcat-7.0.23.A.RELEASE/bin/tomcat-juli.jar
Using CATALINA_PID:    /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/sqlfire-demo/logs/tcserver.pid

3.4. Drop The WAR file into $CATALINA_HOME/sqlfire-demo/webapps directory. It should explode the contents automatically and deploy the application.

[Mon Jan 30 12:32:57 papicella@:~/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/sqlfire-demo/webapps ] $ d
total 8592
drwx——   6 papicella  staff      204 30 Jan 12:12 ROOT/
drwx——  12 papicella  staff      408 30 Jan 12:12 ../
-rw-r–r–   1 papicella  staff  4396232 30 Jan 12:38 sqlfire-afl.war
drwxr-xr-x   7 papicella  staff      238 30 Jan 12:39 sqlfire-afl/
drwx——   5 papicella  staff      170 30 Jan 12:39 ./

3.5 Access the application as follows.

http://localhost:8080/sqlfire-afl/index.html

Img2

3.6 Click on "Show all teams" link
3.7. Click on "Tigers" link to show Richmond's 2012 games for the AFL 2012 season.

Img3

Exploring The Setup

– If you wish to stop the SQLFire distributed cluster use the script "stop-all.sh" as shown below.

[Mon Jan 30 12:52:54 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ ./stop-all.sh
Connecting to distributed system: mcast=/239.192.81.1:12333
Successfully shut down 2 members

– In this howto the data which is stored in the distributed system is also persisted locally to each SQLFire server using "server1" and "server2" directories referenced in the start-servers.sh script . This enables it to be rehydrated across cluster restarts to avoid having to load the data back into the distributed system. This is achieved using SQL as follows when we create the tables.

create diskstore STORE1;

CREATE TABLE TEAMS
(team_id int generated always as identity NOT NULL CONSTRAINT teams_pk_team_id primary key,
name VARCHAR (15),
long_name VARCHAR (30),
web_site VARCHAR (50))
persistent 'STORE1' REPLICATE
;

Learn More

For more information on vFabric SQLFire use the following link.