The Oracle Lite Blog

The Rekounas Blog about Oracle's Mobile Enterprise Application Platform

The Oracle Lite Blog header image 1

Oracle ADF and Mobile Server for BlackBerry

April 8th, 2010 · 4 Comments *

Here is a demo of a ADF Mobile Broweser and ADF Mobile Client. In the demo, they use Oracle Mobile Server for synchronization with a SQLite database.

Share

→ 4 CommentsTags: Mobile ADF · SQLite

Gaining performance out of Queue-Based refresh

March 9th, 2010 · 3 Comments *

We have all been there.  Users start synching, MGP kicks in, some junior SQL developer has written a poorly executing batch job (yeah, I am blaming the junior PL/SQL guy) and all of the sudden, everything comes to a halt. Your online community can’t perform any transactions, your syncing users are just sitting there twiddling their thumbs, support staff is getting all kinds of alerts and calls, the poorly written PL/SQL batch job that the junior developer wrote isn’t finishing, and management is coming to you looking for answers because this is the 3rd time in the last month that this scenario has happened… the perfect storm!

What do you do?  You could tune the existing queries for your publication items to improve sync and MGP performance.  Of course, there is always that poorly written and timed PL/SQL that the junior guy wrote that is inserting/deleting 1 million records in one of your core tables that you are synchronizing to your application, and just when you notice this happening along with syncing or MGP, you notice transactions in C$ALL_CLIENT_ITEMS is blocking all kinds of sessions.  Oracle recommends setting the following in webtogo.ora

SKIP_INQ_CHK_BFR_COMPOSE=NO
DO_APPLY_BFR_COMPOSE=NO

I prefer going to queue based synchronization for situations like this.  Why?   The first things is that it removes triggers on those tables that are usually affected by the blocking locks.  Secondly, if you have bulk operations on a table, the MGP LOG process can get bogged down with logging all the transactions and you often see deadlocks when this happens not to mention blocking locks if the deadlock doesn’t cancel out the MGP process quickly enough.

I am assuming if you are reading this, you are probably familiar with the Mobile Workbench and have created your publication items via that mechanism and felt really good about the fact that you don’t have to write all kinds of stored procedures to perform your synchronizations.  Well, roll up your sleeves, because you will have to write some PL/SQL to get a queue-based refresh working.  In the mobile development kit, there is a tutorial on how to go about creating a queue-based item (Location %ORACLE_HOME%\Mobile\Sdk\samples\Sync\win32\QBasedPI\)

There is a readme.txt in that folder.  I am not going to repost the tutorial, but I will talk about the aspects you should be aware of.  The tutorial basically give a complete-refresh example. This would not be the best example for most applications as there are also bi-directional publication items that you need to worry about.  The real piece of the code you should be concentrating on is the DOWNLOAD_INIT within the package.  This is your download logic.  Your logic should contain the following logic:

  • handles physical deletes
  • handles inserts
  • handles update
  • handles logical deletes

Their example only handles inserts.  If you want your publication item to perform incremental syncs, you will need to have a mechanism in that table that indicates records that need to be updated, inserted, or deleted. Here is a template of the mechanism I use.  In our tables, we have an AUDIT_DATE field.  A timestamp or date field is really useful in determining if a record has been updated.  Of course, you may need a trigger on the table or trust those junior developers to have code in place to update that AUDIT_DATE field.

The physical delete:
DELETE CTM$CTM$<TABLE_NAME>
WHERE CLID$$CS = CLIENTID AND DMLTYPE$$ = 'D';

The insert logic:
INSERT INTO CTM$<TABLE_NAME>(CLID$$, FIELDS1, FIELDS2, DMLTYPE$$, TRANSID$$)
SELECT CLIENTID, FIELDS1, FIELDS2, 'I', CURR_TRAN FROM <TABLE_NAME>
WHERE NOT EXISTS (SELECT 'X' FROM CTM$<TABLE_NAME>
WHERE <MATCH PRIMARY KEYS HERE>)

The update logic:
UPDATE CTM$<TABLE_NAME>

The logical delete:
UPDATE CTM$<TABLE_NAME>
SET TRANID$$ = CURR_TRAN,
DMLTYPE$$ = 'D'
WHERE AUDIT_DATE < TO_CHAR( ADD_MONTHS( SYSDATE, -6 )) AND CLID$$CS = CLIENTID;

Now, in my example, I just keep 6 months of data on the client. At the end of the day, you can decide all aspects of synchronization. Customize the synchronization to fit your business needs.

Share

→ 3 CommentsTags: Oracle Lite

How to create an auto increment field in Oracle Lite

October 24th, 2009 · 10 Comments *

I bet you thought you could make an auto increment field in Oracle Lite the same way you make it in Oracle right? Well, you can’t do it the same way because Oracle Lite does not support PL/SQL. It does however support C, C++, C#, and Java stored procedures. We will review creating auto-increment fields in both the Enterprise and the Lite databases.

Lets review the way you create a auto-increment field in Oracle:

//Step 1:
CREATE TABLE TEST_TABLE (
COL1 NUMBER,
COL2 VARCHAR2(20),
CONSTRAINT TEST_TABLE_PK PRIMARY KEY (COL1)
);


//Step 2:
CREATE SEQUENCE TEST_SEQ
START WITH 1
INCREMENT BY 1;


//Step 3:
CREATE TRIGGER TEST_TRIGGER
BEFORE INSERT ON TEST_TABLE
FOR EACH ROW
BEGIN
SELECT TEST_SEQ.NEXTVAL INTO :NEW.COL1 FROM DUAL;
END;

And we are done.

Now here are the steps for doing it in Oracle Lite:

//Step 1: Create and Compile the Java Code.  Call the Java file JavaTrigger.java.  You may also have to compile using JDK 1.4 as even Oracle 10g is known not to support 1.5 yet.
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JavaTrigger {
   public static void autoIncrement(Connection conn, int newNum[]) throws SQLException {
      try {
         Statement stmt = null;
         ResultSet rs = null;
         stmt = conn.createStatement();
         rs = stmt.executeQuery("SELECT TEST_SEQ.NEXTVAL FROM DUAL");
         if (rs.next()) {
            newNum[0] = rs.getInt(1);
         }
      } catch (SQLException e) {
         System.err.println(e.getMessage());
      }
   }
}


//Step 2: Load the file into the database
CREATE JAVA CLASS USING BFILE ('C:\OracleLiteJava\StoredProcedure\Sample\classes\','JavaTrigger.class');


//Step 3: Create a Stored Procedure in the database
CREATE OR REPLACE PROCEDURE AUTO_INCREMENT(NEW_ID IN OUT INT) AS LANGUAGE JAVA NAME 'JavaTrigger.autoIncrement(java.sql.Connection, int[])';


//Step 4:
CREATE OR REPLACE TRIGGER TEST_TABLE_INSERT_TRIG BEFORE INSERT ON TEST_TABLE FOR EACH ROW AUTO_INCREMENT( NEW.COL1);


//Working Example:
SQL> INSERT INTO TEST_TABLE( COL2) VALUES ('GREG');
1 row(s) created
SQL> DIR TEST_TABLE;
COL1 | COL2
-----+-----
6340 | GREG

For more documentation on Stored Procedures in Oracle Lite, check out the online documentation here.

Share

→ 10 CommentsTags: Oracle Lite

Oracle OpenWorld 2009 Live Stream

October 13th, 2009 · Add a comment *

I couldn’t get to San Fransisco this year for OpenWorld… but I can always watch it via video stream.

Share

→ Add a commentTags: Oracle OpenWorld

What is a Oracle Lite developer made of?

September 21st, 2009 · Add a comment *

So, you are starting an IT project and there is a need for a disconnected component and you require Oracle Lite for your project.  What do you do?  Most people seem to dump the responsibility on the DBA.  Probably not the best move.  Not because they can’t handle the technology, but because they are often far too busy on their day-to-day responsibilities to deal with learning Oracle Lite and properly implementing it into your project.  How about the .Net or Java guy/gal?  Maybe, if they have extensive Oracle SQL tuning experience.  As a project lead you head to your recruiting department and you ask for them to find someone with Oracle Lite experience.  Don’t be surprised they don’t find a lot of people with Oracle Lite skills or to the level of experience your project may require.  Then, how do you go about interviewing this person and identifying if they are the right candidate.

How do you find the right person.

  • Obviously ask them how many projects they have worked on with Oracle Lite or how many years they have working with the product.  Standard question for any technology.
  • Ask them the size and scope of the project (users/publications/publication items).
  • What technologies within Oracle Lite did they use?  Win32, WinCE, OC4J, Webtogo, Symbian, etc.
  • For the more advanced questions, you could ask them about the polite.ini and the webtogo.ora files and what are their functions.
  • Asked them if they have used Partition Maps, Shared Maps, Cached Publication Items.  If they have used these, then there is a good chance that they have worked on a system with considerable amounts of data and that they probably have performed query tuning.
  • Ask them if they have used CONSPERF and what is it’s function.
  • If they have used Oracle Application Server (OAS), then ask them if they have set up a farm and to describe the process.  You may want to ask them what is the function of OPMN, but it’s not as important if they don’t know.  This question will demonstrate their expertise with OAS (But I think Oracle Lite will be using Weblogic starting 10.3.0.3 release for the server).

If you do have an IT project that requires Oracle Lite as your solution, finding a good Oracle Lite resource with experience will be difficult but rewarding.

Share

→ Add a commentTags: Oracle Lite

Testing Concurrent Synchronization in Oracle Lite

May 11th, 2009 · Add a comment *

So, you are all ready to go. Your application has gone through some final testing and all that is really left is to perform some minor performance tweaks here and there and clean up some bugs. Then someone in the rooms asks a question, “How will performance be during concurrent synchronizations? How will it affect the rest of the database?” So now you are probably sweating a bit because you really haven’t thought about it. An individual synchronizing isn’t too bad, but have you thought about 5, 10, 15, 50? Maybe not. And even if you have thought about it, how do you test this? I have been involved in many releases and this is always a bit of a challenge. You may be thinking of using something like LoadRunner, but this doesn’t work well. The next thing to do is to grab 10 – 20 devices, install the Mobile Client and rapidly click sync on each device to get the number of concurrent syncs your are testing your system for. Not the sexiest solution in the world, but it works. The other solution I use is a Java/perl solution Oracle development passed along to me a few years ago. You install the software on one machine. You create the users TEST(1…N) on the server and assign them to the application and set their data subset paraments. You then run a perl script to set up the TEST users on your machine. Finally you just the Java code to simulate the concurrent syncs. This worked very well, but for some reason, all syncs were Complete Refresh. So the test was worst case scenario.

Here is a picture of a test scenario we had set up. We had 10 laptops and they were synchronizing Webtgo-OC4J and Win32. They were all going through the same router and I think 10 out of 10 succeeded.

10 Laptops performing concurrent synchronization.

Your third option for testing concurrency is by contacting an Oracle Lite guru 😉 He might be able to give you a hand with your performance testing.

Share

→ Add a commentTags: Oracle Lite

Patching Oracle Lite in Unix

March 31st, 2009 · Add a comment *

Ran into an issue today that I almost forgot about. It was during patching an Mobile Server on a Unix platform. The issue that I ran into was the following:

1. Stopped the server
2. Installed the patch
3. Tried to log into mobile manager
4. Got the following error:
WTG-10118: Mobile Server is not connected to the Repository
WTG-10117: Please contact your system administrator

To find the problem I went to the readme of the patch that stated that the web.xml file might get changed during the install. So, I opened the file and the fix is the following:

Find your web.xml file


<ORACLE_HOME>/mobile/server/admin/repository/webtogo/WEB-INF/web.xml
or
<ORACLE_HOME>/j2ee/mobileserver/applications/mobileserver/webtogo/WEB-INF/web.xml
or
<ORACLE_HOME>/mobile_oc4j/j2ee/mobileserver/applications/mobileserver/webtogo/WEB-INF/web.xml

Modify the line in the file from:


<param-value>%ORACLE_HOME%\mobile\server\bin\webtogo.ora</param-value>

to

<param-value>/export/home/oramobile/mobile/server/bin/webtogo.ora</param-value>

The README.TXT tells me to go into my ./patch_storage/6630384/….. directory.  But I couldn’t find it.  So, if you find yourself in the same situation that I was in, the solution has been posted.

Share

→ Add a commentTags: Oracle Lite

Setting up a Mobile Server for size and performance

December 15th, 2008 · 5 Comments *

I set up my Nth Mobile Server/Oracle Application Server configuration this past weekend, and here is a list of modifications that I have made to my configuration files.

In the webtogo.ora file:
[WEBTOGO]
DISPLAY_CLIENT_DATA_SIZE=NO
ADMIN_JDBC_URL={changed this to use a RAC URL. Not required in 10.3.0.2 of the product as the installer handles this}
THIN_JDBC_URL={ditto for this URL}

The DISPLAY_CLIENT_DATA_SIZE is meant for OC4J and Webtogo application. If you are not using the customized workspace, Webtogo connects to the remote server and it attempts to determine the number of rows you will be downloading. This is useless, and time consuming. I turn it off.

[CONSOLIDATOR]
RESUME_CLIENT_TIMEOUT=900
MAX_THREADS=8

The RESUME_CLIENT_TIMEOUT variable sets the socket timeout to the number of seconds you specify. Useful in cases where you are having performance issues and you do not want the user to get a network disconnect error.

MAX_THREADS is the number of threads that will concurrently run a MGP cycle. First try 1.5*CPU on the database server. Then back it down to 1*CPU if there are performance issues.

Over to device configuration for Win32, Webtogo, and OC4J:
[SYNC]
TIMEOUT_MAX=900
DEBUG=1
AUTO_COMMIT_COUNT=3000

[All Databases]
MaxSharedMemSize=256

I am not sure if TIMEOUT_MAX is in 10.3.0.2 of the product, but this was around to fix a bug in previous versions. Very similar to the RESUME_CLIENT_TIMEOUT. I set them both to the same value.

DEBUG enables debug of every sync. Very useful in development, but it may cause the overall performance on an initial sync to slow down, especially if you are syncing a large amount of data.

AUTO_COMMIT_COUNT helps in the processing phase. Again, if you are syncing a large amount of data and you are running into memory issues, this is the parameter you want to set to get around those issues. You don’t want to set this too low and you don’t want to set it too high. I usually play with 1000, 3000, or 5000.

If you are running into memory issues due to large initial syncs or large delete/insert scenarios, chances are you will need to set MaxSharedMemSize=256. This controls how many commands can be placed in shared memory, the larger the area, the more commands. Just take not that you will probably have to use AUTO_COMMIT_COUNT if you are using this variable.

Oracle Application Server configuration:
Not too many things here other than changing ports in httpd.conf and changing the following in opmn.xml
< data id=”java-options” value=”
-server -Djava.security.policy=$ORACLE_HOME/j2ee/mobileserver/config/java2.policy -Djava.awt.headless=true
-Xms2048M -Xmx2048M“/>

I always set Xms(Initial Java Heap Size) and Xmx(Maximum Java Heap Size) to be the same. In this case I set them to use 2GB.

Before I do my initial sync on a new server, I make sure these are always set. The other thing you may want to think about doing for your test servers are set them up with the same number of users your production servers will have. Even though you may not be able to test concurrent syncs within your environment, you will be able to see how MGP and your database handle the consolidation.

Share

→ 5 CommentsTags: Java · Oracle Lite

Mentioned in Oracle Magazine Peer-to-Peer

November 5th, 2008 · 2 Comments *

If you have picked up your November/December 2008 issue of Oracle Magazine, turn to page 28 and there is a brief interview with me. Here is the interview if you don’t have the magazine:

Which new features in Oracle technologies are you currently finding most valuable? Oracle Database Lite’s automatic sync allows high reliability in mobile applications, while at the same time keeping users working instead of interrupting their current applications to retrieve new applications. Prior releases relied on manual synchronization, and for an organization this caused issues because users’ data could be stale if they waited too long between syncs. Now, an organization can set thresholds around data modified, network connectivity, and even low battery power to sync information.

Name one unique use of Oracle products at your company. We’ve created one of the largest implementations of Oracle Database Lite. We’re synchronizing more than 350 tables, 700 indexes, 60 sequences, and a full-blown OC4J application to field users’ laptop.

What would you like to see Oracle, as a company, do more of? Oracle has many great technologies. What I find most customers have a hard time with is visualizing the integration of those technologies into their businesses. More in-depth case studies of solutions requiring multiple products would help.

Here is a link to the article.

Share

→ 2 CommentsTags: Oracle · Oracle Lite

Think NB 2008

October 24th, 2008 · Add a comment *

I will be attending Think NB again this year with OAOT as an exhibitor. I will be stationed at booth #122.

The event will be at the Saint John Trade and Convention Centre October 29-30.
Visitor schedule is posted here.

If all goes well this week, I will be demoing Oracle Lite on my new Samsung ACE SmartPhone.

Share

→ Add a commentTags: Oracle Lite

Oracle Lite on a Smartphone

October 12th, 2008 · 4 Comments *

Ever since I started working with Oracle Lite, I have worked with Win32, Webtogo, or OC4J applications. This week, I am picking up a Samsung ACE World Edition Smartphone and I plan on putting some demo and sample applications on the phone to get my feet wet from a different perspective. Working on a small device is obviously different from a resource rich laptop, so I want to get the feel for working with different constraints. Also, need to freshen up my .Net skills. So, in the end, I will be killing 3 birds with one stone. I will keep my progress posted.

Share

→ 4 CommentsTags: Oracle Lite

Pictures and Video from Oracle OpenWorld 2008

October 12th, 2008 · Add a comment *

Charles Phillips talks to Michael Phelps.

Intel had a cool display. Ever watch the Minority Report?

Now, I am not a fan of country music, but my colleague likes country, so we went to the Alan Jackson concert on appreciation night.

Share

→ Add a commentTags: Oracle OpenWorld

Oracle OpenWorld 2008 – Day 3 & 4

September 25th, 2008 · Add a comment *

A few more busy days that I ended up going to less sessions, had more business lunches, and spoke to people about Oracle Lite. There is a lot of interesting things happening in the embedded space and Oracle Lite will be playing a role in that along with TimesTen and Berkley DB. I was given the opportunity to hand out a Oracle Lite case study at the Oracle Lite demo grounds, and for those of you that are interested, you can download the case study here.

Got some great swag from the exhibit halls and seen some great demos. Went to the Ace Dinner on Tuesday night at Le Char French Bistro and I highly recommend the Fillet of Beef.

Day 4 was more of the same and topped off by the appreciation event. This year it was held at Treasure Island. The headline acts were Seal, UB40, Alan Jackson, and Elvis Costello. My colleague is an Allen Jackson fan so we stuck with that show. It was a pretty good show, and for me to say that about country music takes a lot. To give you an idea of where my music tastes are, I currently have Metallica’s Death Magnetic CD playing in my car.

I also attending a focus group on BEA WebLogic developers moving towards JDeveloper. I was part of this focus group because I am a WebLogic developer as well. BEA developers really like the Eclipse IDE, and just to sell them on JDeveloper will be a hard task. But some of the things I have seen in the newest JDeveloper release should ease their minds. I haven’t downloaded the new product yet, but one of the things I had seen in the survey that I filled out was more focus on team development. They seem to be working on an internal IM client to JDeveloper and appealed to me because often times you have different developers choosing to use their own IM client and you end up having 4 different IMs running on your machine. This would really simplify that.

Unfortunately, my laptop doesn’t have an SD card reader and I forgot to bring my SD card reader from home, thus I can’t upload and photos or videos. When I get back home, I will upload and post them.

I get home on late Thursday night, spend a weekend with my family, and then head to Michigan for customer meetings next week. Now to pack for the 5 hour flight back to Toronto followed by a 2 hour flight back to New Brunswick. Looking forward to doing this again next year.

Share

→ Add a commentTags: Oracle OpenWorld

Oracle OpenWorld 2008 – Day 2

September 23rd, 2008 · 1 Comment *

Pretty hectic day again. First thing was the keynotes with Charles Phillips. Highlights from the show was Olympic gold medalist Michael Phelps was on site and the demo of the new Beehive collaboration suite. Not yet available for download, but I can see this product as a standard in any business in the years to come.

Other then that, I went to a couple of Oracle Lite sessions. One was with CEO of Toumaz in the UK Keith Errey on digital health care. The other session was an overview session of Oracle Lite.

Day 3, I will be spending more time at the Demo Grounds today and I will be attending an ACE dinner tonight.

There is no shortage off food, coffee, soft drinks, t-shirts, and beer at this years event. Fantastic!

Share

→ 1 CommentTags: Oracle OpenWorld

Oracle OpenWorld 2008 – Day 1

September 21st, 2008 · 1 Comment *

Woke up bright and early here in San Francisco. 4:00 am to be exact. The jet leg is working as expected. The flight was a bumpy ride, other then 10 minutes of feeling like I was on a ride at the amusement park, it wasn’t bad. Watched Iron Man on the flight. Anyhow, to OpenWorld stuff. Went to 3 sessions today. 1st session was on Oracle Fusion Middleware by speaker George Trujillo. Very good session that was cut short because of time. Even though I am an Oracle Lite developer, I am also a J2EE developer and I can see SOA playing a bigger part in my career path. So, this was a very good session for tying into SOA and BPEL. Second session was one that I stumbled across and it was on XMLDB by Marco Gralike from AMIS. This could be a technology that I can see growing, but at this time, I can’t see me using it in the near future. The last session this afternoon was by the popular Tom Kyte from Ask Tom. The session was on effective schema design. You think you know stuff, and then you spend a hour with Tom and you realize, you don’t know enough. Great presentation!

Last session today is with James Carville and Mary Matalin. I have seen them on CNN and know they are political analysts, but am not too familiar with them.

If I am not too sleepy tonight, I will check out the kick-off session at 7:30, which translates to 12:30 am Atlantic time.

Share

→ 1 CommentTags: Oracle OpenWorld

Creating publication items using the Oracle Lite API

September 19th, 2008 · 3 Comments *

If you have been doing Oracle Lite development, chances are you have been either using the Packaging Wizard or the Mobile Workbench to create your applications. In some cases, you have had to use the APIs to do partition mapping or altering the publication items (I will show those in another post). I on the other hand use the APIs. Why do I do this? I started doing this because the new application I was working on was estimated to have 150+ tables. In my previous experience of using the packaging wizard, publishing over a VPN line was time consuming and prone to problems. So, when I started a new project where I knew there would be a lot of tables with a very good probability that the database model would be changing quite frequently, I decided to create my own code using the APIs to manage the repository. That repository today is over 360 tables. I am now able to use that code that I wrote for one application, and use it to maintain other applications as well. It is reusable and reliable. Reliable enough that I can have a 3rd party deploy the application without the use of the Oracle Lite SDK. The only information I need to know is “what tables are changing and what are the details of the change.” That little information requires 2 minutes or less of work from my schedule.

Here is the sample of how to create a publication item and add it to your publication. In the sample, it will also show you how to add a regular index to the publication item.


import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import oracle.lite.sync.ConsolidatorException;
import oracle.lite.sync.ConsolidatorManager;
public class Sample {
    public static void main(String arg[]) throws ConsolidatorException, 
                                                 SQLException {
        Connection con = null;                                                 
        ConsolidatorManager cm = new ConsolidatorManager();
        String MOBILE_SCHEMA   = "mobileadmin";
        String MOBILE_PASSWORD = "password";
        String JDBC_URL        = "jdbc:oracle:thin:@localhost:1521:XE";
        String PUBLICATION     = "LITE";                         
        String PUB_ITEM        = "WTGPI_MYTABLE";
        String STORE_NAME      = "MYTABLE";
        String OWNER           = "MASTER";
        try {
            DriverManager.registerDriver((Driver)(Class.forName("oracle.jdbc.OracleDriver").newInstance()));
            try {
                con = DriverManager.getConnection(JDBC_URL, MOBILE_SCHEMA, MOBILE_PASSWORD);
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
        cm.openConnection(con);
        PreparedStatement stmt = null;
        // If you are using a multilingual environment, this will need to be set.
        stmt = con.prepareStatement("ALTER SESSION SET NLS_LENGTH_SEMANTICS='BYTE'"); 
        stmt.execute();
        // attempt to drop the publication item in the case that it already exists
        try {
            cm.dropPublicationItem(PUB_ITEM);
        } catch (ConsolidatorException ce){
            System.out.println("Drop failed: Publication Item does not exist!");
        }
        cm.createPublicationItem(
           PUB_ITEM,                // publication item name
           OWNER,                   // owner
           STORE_NAME,              // store
           "F",                     // refresh mode (F - Fast Refresh, C - Complete Refresh)
           "SELECT USER_ID, COL1, COL2, COL3 FROM MASTER.MYTABLE WHERE USER_ID = :USER_ID",  // select stmt
           null,                    // callback package owner
           null);                   // callback package name
        cm.addPublicationItem(
            PUBLICATION,
            PUB_ITEM,
            null, 

            "",                     // To make read-only, this value will be IUD
            "S",                    // Conflict rule (S - Server wins, C - Client Wins)
            null, 
            1,
            null,
            true, 
            false,                  // Shared map.  Set to true for shared map and make pub item read only hint: IUD"
            null);
        try {                                
            cm.createPublicationItemIndex(  "COL123_IDX",       // index name
                                            "WTGPI_MYTABLE",    // Publication item
                                            "I",                // I - Regular Index, U - Unique, P - Primary Key
                                            "COL1, COL2, COL3"  // Columns included in the index.
                                        );
            // Note that you should not have to create a primary key index as the
            // createPublicationItem method will handle this.                                        
        } catch (ConsolidatorException ce){
            System.out.println("Add publication item index failed: " + ce.getMessage());
        }
        cm.resetCache();
    }
}

To compile the code, use the following:
javac -classpath %ORACLE_HOME%/mobile/classes/consolidator.jar;%ORACLE_HOME%/jdbc/lib/ojdbc14.jar;. Sample.java

Run the code using the following:
java -classpath %ORACLE_HOME%/mobile/classes/consolidator.jar;%ORACLE_HOME%/jdbc/lib/ojdbc14.jar;. Sample

Assumptions:
You have the Oracle Lite SDK installed and you have an Java JDK 1.4 or higher installed.

Share

→ 3 CommentsTags: Java · Oracle Lite

Where to find me at Oracle OpenWorld 2008

August 28th, 2008 · Add a comment *

If you plan on attending Oracle OpenWorld 2008 in San Francisco this year, I will be at the Oracle Lite booth in the Oracle DEMOgrounds. More details as they become available.

**Update: Location Information
Venue: Moscone Center Exhibition Hall – Database Demo Grounds
Station ID: L05

DEMOground Floor Plan

Share

→ Add a commentTags: Oracle Lite · Oracle OpenWorld

Oracle OpenWorld 2008 Session Suggested

June 13th, 2008 · Add a comment *

I have posted a suggestion at Oracle Mix for an Oracle Lite session on sizing and performance tuning. If you plan on going to Oracle OpenWorld San Francisco 2008 and are interested in this topic, go to Performance Tuning and Sizing Oracle Lite for a Global Enterprise Application and cast your vote.

I am waiting for my registration confirmation code and once I have that, I will be posting methods to contact me and my planned schedule at this years event. If you plan on being at the event this year and would like to meet up, just contact me and through my contact form and we can plan ahead.

Share

→ Add a commentTags: Oracle Lite · Oracle OpenWorld

Oracle Lite versus MobiLink

June 5th, 2008 · 11 Comments *

As the mobility market is increasing in popularity more companies are recognizing the value of pushing the current boundaries of enterprise applications to extend the reach into mobile computing. With the extension of the enterprise to mobile devices the strategy and technology used to support them has met the growing demand but offered some interesting new challenges along the way. There are many products on the market today that can be used for deploying mobile applications. I have been asked recently to compare two such products and offer a perspective. The two products evaluated which also happen to be leaders in this space; Oracle Lite and Sybase’s MobiLink. First let’s look at some of the core elements that are required of these products to baseline the evaluation approach.

Global Features MobiLink Features Oracle Lite
Database Synchronization Bi-directional database synchronization Bi-directional database replication
Synchronization Options Synchronize over TCP/IP, HTTP, HTTPS, Palm Hotsync, ActiveSync HTTP, HTTPS, HotSync
Data Synchronization Subset data by rows and columns Sync done by rows and subset parameters. Columns are defined for sync.
Execution of Data Synchronization File Transfer DDL Scripts
Synchronization Control Server-initiated synchronization Automatic Sync, Server Initiated
Conflict Resolution Programmable conflict resolution in SQL, .NET or Java Customizing Synchronization Conflict Resolution Outcomes
Set Synchronization Priority Priority-based synchronization Priority-based replication
Encryption Data encryption Database encryption

In comparing these products there were a couple of features that did seem to be product specific. Some things that Oracle Lite has that enhanced the synchronization solution are:

  • Device Manager for application updates and and Oracle Lite related patches for each client. This feature allows an application to update and deploy their application enhancements without having to build or use a third-party deployment mechanism.
  • Webtogo and OC4J for web based and J2EE applications. This feature enables an enterprise application to be deployed to a client’s laptop without having the additional requirement of managing a third-party J2EE server container and HTTP server.

One distinct difference between these two pieces software is MobiLink is session-based replication vs. Oracle Lite uses asynchronous replication. Both of these have their advantages and disadvantages.

  Oracle Lite MobiLink
Advantages: Incremental downloads are fast because the data has been composed on the consolidation database. Consolidation is performed when the user performs synchronization.
Disadvantages: Server changes need to be replicated to the users out queue by the background MGP process. Concurrent user synchronizing can cause issues with blocking locks and synchronization performance.

Environment Baseline
In order to create an environment to equally test the two products I have set up a small application syncing 16 tables for both Oracle Lite and MobiLink. The database assumption will replicate the solution to an Oracle database as this appears to be a fairly common type of configuration in my encounters., To keep the anaylsis straight forward the key attributes that will be measured are:

  • Performance
  • Development
  • Maintenance
  • Troubleshooting

MobiLink Publication
To get a feel for the MobiLink product, I created a small synchronization model synchronizing 16 tables. These tables ranged from having a few records to one table syncing 140,000+ records. The development environment for an experienced SQL developer is pretty easy to follow. You can customize various events in the synchronization process to suit your needs. Once you have set up all your business rules and synchronization package, you deploy your model and the software creates scripts for the MobiLink server, a client database (distributed using batch files), and the SQL for your consolidated database.

Oracle Lite Publication
I also created an application in Oracle Lite using same tables as MobiLink to get a good baseline for performance and used the Mobile Workbench for development. If you have read my blog, you know that I use the Java APIs for development and the reason that I have chosen this path is that the APIs give me something that I can quickly reuse on any Mobile Server environment along with added flexibility in deployment options. With the Workbench, you first create the publication, then you create the publication items, you add your publication items to the publication, and finally you deploy your publication.

Results

Performance
I had an assumption that Oracle Lite would be faster for synchronization performance, the only thing that I thought might be quicker in favour of MobiLink was the fact that it synched via TCP/IP. The test was to do an initial synchronization of 16 tables. MobiLink remote databases are already installed on the client, so they have a head start. But even with that head start, Oracle Lite was much faster then MobiLink. Oracle Lite synchronized 157,131 records in 13 seconds. The same amount of data took MobiLink 1 minute and 38 seconds.

The most common challenge for most new users of Oracle Lite is the sizing and background performance of Message Generator Processor (MGP). Sizing of the enterprise database for Oracle Lite accordingly, most people run into issues with space and IO waits. This is probably the most common factor that user run into with Oracle Lite. Typically back to my assumption replicated to an Oracle database. If you have Oracle DBAs already on staff, those skills can be leveraged to assist in the Oracle Lite optimization.

Development
Mobilink offers an advantage to very novice users and appears to have the better development environment. This is due to the fact that all the customization can be performed with modification to some simple SQL scripts. Based on your skill sets and strategy this might be an approach however it has some drawbacks. The most significant one is you have to go through these scripts to update various events with your new customized SQL each time and update or deployment is required. This might be ok for very small (30 – 40 tables) environments, but in true enterprise environments this could become more difficult exponentially.

Oracle offers a different approach to this same type of synchronization event. They achieve this in a similar manner by allowing you to adjust the SQL needed for replication but they build the deployment packages for you. These packages can be tailored or customized if necessary but in my experience it has not been necessary. The one benefit I did find with Oracle Lite is using the API’s for advanced tasks. The API’s allowed me to create integration scripts that could be reused and ended up making the deployments much easier to manage.

Schema Evolution
Schema evolution is the changes that happen to a database during the life cycle of the application. Tables can be added, modified or deleted during the life of an application. Schema evolution is usually a significant challenge for most mobile systems because getting a schema changes to the client without disrupting the users is extremely difficult.

Oracle Lite holds a clear advantage in schema evolution. In Oracle Lite, to make changes to a table in you application:

1. Stop Mobile Server.
2. Change the Oracle database schema (add/drop column).
3. Create a Java program to call the ConsolidatorManager API AlterPublicationItem().
4. Start Mobile Server
5. Execute sync from the client
6. The change to the table will now be available on the client.

Typically you should not have to stop/start the server, you can just reset the metadata cache, but it is good practice to shut down the server so no one attempts synchronization while you are performing the change.

For MobiLink’s schema evolution, I am going to send you to Dr. Dobbs Portal. Apparently to achieve the same function you have to build something that will do this and it does not appear to be as easy in the long term as Oracle Lite.

Along with schema evolution, comes referential integrity. How does MobiLink handle this? Once again I will send you to Dr. Dobbs Portal. Oracle Lite uses table weights. It applies change to the tables in the order that the publication items are assigned a weight. So, all the number 1 items get applied first and the number 2 items are second, and so on. Three methods for applying weights are manually determining your FK constraints per publication item, use the API method assignWeights(), and finally, you can run the code that I have in this post to determine your weights.

Troubleshooting and Debugging
The better your resources are for troubleshooting issues, the less time it takes to resolve issues. I haven’t worked with the MobiLink product enough to identify how good the tools are for troubleshooting. I can however comment on Oracle Lite’s troubleshooting resources. Since I have started working with the product, I would have to say Oracle Lite’s documentation is probably the biggest improvement Oracle has made to the product. Starting with the 10g R1 release, Oracle has added tracing to the following:
GLOBAL
SYNC
MGP
MGPAPPLY
MGPCOMPOSE

In those 5 areas of the synchronization life cycle, I can set debugging to 6 different levels (mandatory, warning, normal, info, config, finest, and all). From that, I can select 6 categories as well (general, SQL, timing, data, resume, function, and all). Then finally, I can set this for all users or I can simply specify a list of users. I can do this on the fly through Mobile Manager. Gathering the debug results, I can quickly resolve the issue combined with the schema evolution, fixes have hardly any impact to the user base.

Oracle Lite has also added debugging to the client and SQL tracing of all SQL statements that are executed on the client. For the client, this is as simple as changing this variable in the polite.ini to OLITE_SQL_TRACE=YES. With the Device Manager, I can specify that I want to add some debug information for a particular user. I can then get the user to perform the action that is causing them grief, and with the Device Manager I can then pull the debug file from their device.

Administration
MobiLinks doesn’t have a user administration interface. You have to create your own and hook it into their application. So, on top of developing all the code to do your synchronization, you are going to have to spend some time identifying ways to administer the users. Oracle Lite has the Mobile Manager. If you are familiar with Oracle OEM tools, this follows the same conventions. From Mobile Manager, you can monitor syncs, get debug logs, monitor MGP, add and remove users, add users to applications (did I mention one mobile server can have many publications). Training support staff for trouble shooting is easy with Mobile Manager.

The Mobile Manager can be deployed standalone or on top of Oracle Application Server. Oracle Lite is an n-tier based enterprise solution. If you require the use of the APIs to create your users, here is a sample:
MobileResourceManager rm = new MobileResourceManager(con);
rm.createUser("USERNAME", "PASSWORD", "DISPLAYED NAME", "U");
ConsolidatorManager cm = new ConsolidatorManager();
cm.openConnection(con);
cm.setSubscriptionParameter("PUBLICATION_NAME", "USERNAME", "BIND_VARIABLE", "'bind value'");
cm.instantiateSubscription("PUBLICATION_NAME", "USERNAME");

Conclusion
If you fall under the following category, then you should be choosing Oracle Lite as your mobile solution:
1. Already using Oracle Enterprise Database.
2. Already have an application using Oracle Enterprise Database that has a business requirement to go mobile.
3. Have a J2EE or .Net application that you need to port to a mobile environment.
4. Required synchronization of high volumes of data and high volume of tables and database objects.

Reasons to choose MobiLink are:
1. Your enterprise database is something other then Oracle.
2. Lots of resources on the net. Web casts and other information available.

Final thoughts
MobiLink seems to be a good product and fulfills many areas of mobile environment. However, MobiLink does not seem to have an enterprise approach to mobile computing.. The MobiLink product requires a fair amount of coding before you have users up and running. Oracle Lite seamlessly integrates with Oracle and Oracle Application Server giving you an n-tier enterprise solution for your business. Over time the current draw back of Oracle Lite such as lack of documentation will improve. The technology appears to be solid and is applying the lessons Oracle has learned in the Enterprise. Looking at the total cost of ownership it appears at a high level that Oracle Lite could have a distinct advantage of a lower cost over the life of a mobile enterprise application.

Authors:
Greg Rekounas
Solution Architect
OAO Technology Solutions, Inc.

Cameron Chehreh
Chief Technology Officer
OAO Technology Solutions, Inc.
CIO Connection

Share

→ 11 CommentsTags: Oracle Lite

Is performance tuning important in Oracle Lite Part 2

May 31st, 2008 · 1 Comment *

Performance is often overlooked on a mobile database. Users are generally only dealing with thousands of rows instead of millions of rows, so not a lot of thought is spent on tuning a client database. I will show you a scenario where querying a table with 3600 hundred records can cause a fair amount of grief to a users device.

First, we create our table:
CREATE TABLE HIERARCHY
(
LEVEL_ID NUMBER ,
CHILD_ID VARCHAR2(40),
PARENT_ID VARCHAR(40)
);

Then we populate this recursive table with data.

Using msql, we will execute the following:
SQL> set timing on;
SQL> SELECT count(1)
FROM HIERARCHY START
WITH LEVEL_ID = 1
1> 2> 3> CONNECT BY PRIOR CHILD_ID = PARENT_ID;
COUNT(1)
--------
3681

1 row(s) returned

Elapsed: 0:0:18.343

Here is a screen shot of what my CPU was doing while I executed the code:

Figure 1

This small query consumed a fair amount of resources for something that is going against 3681 rows. This example uses the very powerful CONNECT BY clause. In many field based applications, where data is distributed via some type of organizational hierarchy, these types of queries are quite common. Imagine what the user would experience on their laptop or hand held device if something like this wasn’t addressed. Most users would attribute it to “my computer is running slow.” Something like this could even go unnoticed and over time, become a huge waste of time for users causing loss of productivity resulting in loss of revenue.

Executing Explain Plans in Oracle Lite
Here is what you need to do to execute explain plans in Oracle Lite:
Step 1: Using msql, connect to your Oracle Lite database.
Step 2: Execute the following create table statement:
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_owner varchar2(30),
object_name varchar2(30),
id int,
parent_id int,
Position int,
cost int,
cardinality int,
text varchar(4096));

commit;

Note: In Oracle Lite, it is important to execute commits after DDL operations as well DML operations.

Step 3: Execute your explain plan:
EXPLAIN PLAN FOR SELECT LPAD(LEVEL_ID,LENGTH(TO_CHAR(LEVEL_ID))+LEVEL*2,' '), CHILD_ID , PARENT_ID
FROM HIERARCHY
START WITH LEVEL_ID = 1
CONNECT BY PRIOR CHILD_ID = PARENT_ID;

Step 4: Your explain plan information is stored in the plan_table:

Figure 2

You can see from the explain plan that the query is doing two full table scans on the HIERARCHY table. Even though this is just one table, the CONNECT BY clause behind the scenes joins HIERARCHY to itself and since no indexes exist, full table scans are performed on both joins (the CHILD_ID and PARENT_ID).

To improve performance here, we will add two indexes, one for CHILD_ID and one for PARENT_ID.
CREATE INDEX IDX_HIERARCHY_1 ON HIERARCHY(CHILD_ID);
CREATE INDEX IDX_HIERARCHY_2 ON HIERARCHY(PARENT_ID);

Now, lets see if the performance of the query improved:
SQL> SELECT count(1)
FROM HIERARCHY START
WITH LEVEL_ID = 1
1> 2> 3> CONNECT BY PRIOR CHILD_ID = PARENT_ID;
COUNT(1)
--------
3681

1 row(s) returned

Elapsed: 0:0:0.63

The query has gone from just over 18 seconds to under 1 second. Lets take a look at the new explain plan:

You can see from the explain plan that we have been reduced to one full table scan (can’t do without this because of the nature of the recursive relationship) and we now replaced the full table scan with an ACCESS BY ROWID scan on IDX_HIERARCHY_2. We would still need IDX_HIERARCHY_1 down the road because users will most likely be accessing this table by CLIENT_ID. So, this is a safe guard.

Don’t underestimate performance tuning. Don’t just rely on your DBA or your users to tell you something is slow. Have all your developers spend some time learning how to execute explain plans and SQL performance tuning techniques so that your application is better prepared for the real world.

Share

→ 1 CommentTags: Oracle Lite