The Oracle Lite Blog

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.

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.

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.

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

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.

The last couple of weeks, for my own benefit, I have been looking at what products other then Oracle Lite do data synchronization between mobile devices and an enterprise database.  These products are:

• Sybase MobiLink
• IBM DB2 Everyplace
• Microsoft SQL Server Compact Edition (CE)
  

From those 3, MobiLink and Everyplace are capable of syncing with an Oracle database.  I looked at the documentation to MobiLink and it is pretty straight forward.  Everyplace’s documentation isn’t as clear as MobiLink’s.  CE’s doesn’t sync with an Oracle database, so I am not going to go into detail about that product.

Between MobiLink and DB2 Everyplace, I have decided, based on information I have read in the documentation and other users evaluation, that MobiLink stacks up the better of the two.  Now, what I plan on doing is setting up a small application, about 15-20 tables and synchronize those tables.  From that, I will hope to take away the following:

• Ease of development and complexity
• Performance and scalability
• Maintainability and schema evolution
• Troubleshooting and debugging

I will take that information and stack it up against Oracle Lite and compare those categories.  Look for a post on my findings soon.

I recently upgraded my WordPress and while I was at it, I decided to update my blog theme as well.  I change the images to be images from Oracle OpenWorld 2007 in San Francisco.  They were all pictures that I took.

Let me know what you think of the new look.

When you start designing an Oracle Lite solution, the very first thing you should think about after you determine that Oracle Lite is the path for your company, start thinking about performance. You are probably thinking, performance? This is Oracle Lite. I shouldn’t have to worry about performance tuning. Well, the truth of the matter is Oracle Lite, without the proper care for the SQL in the publication items, size of the tables being synchronized, and number of users your application is synchronizing, you could quickly find yourself in a heap of trouble and you will quickly be looking for an experienced DBA or an Oracle Lite expert to save you. When an Oracle Lite publication item is published, 5 tables are created on the back end database. They are:

• CVR$ - This table is the version table. This table maps back to your base table and indicates what DML operation and version has occurred on a record.
• CLG$ - This table is the log table. The first pass of the MGP compose process dirty rows in the version table are copied to log table. The version table rows are then cleared as being dirty.
• CEQ$ - This the the error queue table. Any rows that encounter errors during the apply phase are placed in the error table. The error message corresponding to the error is placed in the mobileadmin schema C$EQ table.
• CFM$ - This is the in queue. Rows that are dirty (new/updated/deleted) on the client are placed in this table during a replication session. They are stamped with the version number of the corresponding row on the CVR$PublicationItemName table. Once the have been applied to the base table, they are deleted.
• CMP$ - This is the out queue. You may also refer to these tables as the mapped tables. Rows that need to be sent to the client are placed in this queue. The rows in this table persist and provide a record of what has been sent to the client.

The culprit of your performance issues will be in your out queue. Tables in the out queue can grow to be quite large. 10 Million records. 100 Million records. All depends on how big your application is and how many users you have. With each user you add to your application, the map tables will grow by the number of records that user is associate to by the snapshot predicate. When your application has added enough users that these tables become large, you can start thinking about partition mapping.  No, partition mapping isn’t the same as partitioning a table.  What happens is a process that takes your CMP$PublicationItem table and divides it by a number you specify.  The partition mapping process then goes out and starts adding users to these new map tables.  So, if you have 100 users and you create a partition map to the 10th degree, you will have 10 Mapped tables with 10 users per mapped table.  So now each table is 1/10th (approximately) the original size of the publication mapped table.

If you have an experienced DBA on hand, he should have already pointed out to you that the SYNCSERVER table space was created on $ORACLE_HOME and you are quickly running out of disk space.  Once you have straightened out that mess, (Can be avoided by creating the SYNCSERVER table space prior to running the repwizard), you will notice that all tables and indexes are under the same table space.  If you are running ASM as your volume manager, this is not an issue as ASM will move your indexes and tables around your disk array evenly.  If you do not have a volume manager in place, ensure that you go through an exercise where you distribute your tables space, tables, and indexes around your disk array.  As mentioned, DBAs have probably already noticed this and have come knocking on your door asking you what you are doing.

Have you used CONSPERF to analyze your timing and explain plans for your MGP and SYNC?   Consperf will choose the best query with the best timing and not the best cost.  Don’t get hung up on the cost of queries, it is all about timing.  Have you analyzed your base queries for your publication items as well?  If these queries take more then a few seconds to start returning data, then you will have issues with MGP and SYNC.  Tune these by adding appropriate indexes and query rewriting.  Make sure, if possible to remove any sorting or grouping from your select statements.

The last item I will talk about is shared maps.  Shared maps are publication items where a group of users are sharing the exact same data.  This helps by MGP only having to go through those publication items by the number of groups associate to your shared map.  These snapshots have to be read-only and if there is a bound subset parameter, it has to be the same for every member of the group.  Shared maps are often used for look-up tables.

 This concludes part 1 and part 2 will be about performance tuning on the client.

Starting upgrading Webtogo to OC4J and here are my likes and dislikes so far:Likes:

• Full J2EE compliance
• Easier to develop and debug application code
• I can specify different JVM from the one that is installed last.

Dislikes:

• Customized workspace is not supported (Although, I think I can get around that)
• WTGJDBCDriver is doing some really, really weird things.
• Session does not contain OraUserProfile object.  So, even if wanted to build my own connection string and not use the WTGJDBCDriver, I can’t.  I have an SR open for this now.
• All the code is located on the file system and not embedded into webtogo.odb

I think once I get past the WTGJDBCDriver issue and the OraUserProfile issue, I think I will like the OC4J much better.  I will keep this post updated with my findings.

Received notice of my Oracle ACE Award last week. My ACE profile is up if you wanted to check it out here.

What is an Oracle ACE?

Oracle ACEs and Oracle ACE Directors are known for their strong credentials as Oracle community enthusiasts and advocates, with candidates nominated by anyone in the Oracle Technology and Applications communities. The baseline requirements are the same for both designations; however, Oracle ACE Directors work more closely and formally with Oracle in terms of their community activity.

Here is an example of how you deploy an application. I will follow it up with examples of how to add publication items to the application and adding users to the application as well.

The following command should be all on one line:
java -classpath .:$ORACLE_HOME/mobile/classes/consolidator.jar:
$ORACLE_HOME/mobile/server/bin/devmgr.jar:
$ORACLE_HOME/mobile/server/bin/webtogo.jar:
$ORACLE_HOME/jdbc/lib/ojdbc14.jar Deploy MOBILEADMIN MOBILEPWD localhost 1521 XE


import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import oracle.lite.sync.Consolidator;
import oracle.lite.sync.ConsolidatorException;
import oracle.lite.sync.ConsolidatorManager;

public class Deploy {
    private static Connection con = null;

    public static void main(String[] arg) throws ConsolidatorException,
                                                 SQLException {
        if (arg.length == 5) {
            ConsolidatorManager cm = new ConsolidatorManager();
            String PUBLICATION = “PUBLICATION”;

            // arguments are:
            // 0 - Mobile Schema Repository
            // 1 - Mobile Schema Repository Password
            // 2 - URL
            // 3 - Port
            // 4 - SID
            String MOBILE_SCHEMA = arg[0];
            String MOBILE_PASSWORD = arg[1];
            String JDBC_URL =
                “jdbc:oracle:thin:@” + arg[2] + “:” + arg[3] + “:” + arg[4];

            System.out.println(”JDBC URL = ” + JDBC_URL);
            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);

            // Alter the session if you are dealing with a multilingual application
            PreparedStatement stmt = null;
            stmt = con.prepareStatement(”alter session SET NLS_LENGTH_SEMANTICS=’BYTE’”);
            stmt.execute();

            try {
                cm.dropPublication(PUBLICATION);
            } catch (ConsolidatorException ce) {
                System.out.println(ce.getMessage());
            }

            cm.createPublication(PUBLICATION, Consolidator.DFLT_CREATOR_ID,
                                 PUBLICATION.toLowerCase() + “.%s”, null);

        } else {
            System.out.println(”Invalid parameters!”);
            System.out.println(”java -classpath :. Deploy mobileadmin mobilepass url port sid”);
        }
    }
}

Let me start by saying that this is not supported or will be supported by Oracle. If you are doing some R&D work and want to see how Oracle Lite will work, it will install on Oracle XE. And just in case you were still thinking of using this in your production system, get that out of your head now. This should not be used in a production system.

Is there anything special you need to do? Nope! Just install Oracle XE as per the Installer that it comes with. After Oracle XE is up and running, run the Oracle Lite setup.exe (Oracle Universal Installer) and say ‘YES’ the installing the repository. When asked about your database information, you should enter:
host = localhost
port = 1521
SID = XE

Start up your mobile server and that’s it. Now go and play.

The mobile system that I designed 2 1/2 years ago has been in production now for almost 6 months and as any new system that goes into production, changes are inevitable. When we designed the system, we had to come up with two applications that would be implemented both on a WebLogic/Sun ONE container and a Oracle Lite Webtogo container. So, we had to have a online solution that could facilitate 30,000 global users supporting 26 languages, and we had to have that same solution scaled down so that it would run on a laptop. Now, we didn’t want to have two development teams, one for a full blown J2EE application and a Servlet based application. Our J2EE application utilized EJBs while our disconnected application utilized singletons. The UI, Business layer, and DAO layer were the exact same between the two environment. A security and platform tags are used to isolate online only capabilities. Our delegate pattern handled the rest. When deploying the application, we would set a flag in a build properties file that would dictate what type of deployment we were doing and we would build an EAR for the online application and just do a regular compile for the Webtogo application. Some of the special things we did for the Webtogo application were that we copied only the JARs that we needed for Webtogo from WebLogic’s APP-INF/lib to Webtogo’s WEB-INF/lib. After that we compile the JSPs that we require for the offline application using Oracle’s JSP Compiler. Once that conversion was done, we would package the application with Oracle Lite’s Webtogo Packaging Wizard and deploy the mobile application via the Mobile Manager.

Now, had we had the option of using OC4J from the start, we could have used the EJBs instead of the singletons and the only thing that would have probably changed was how we were doing our JNDI calls (OC4J vs WebLogic). Other then that, everything else would remain the same. We have tried to stay true to not being dependent on a platforms APIs but I have been seeing WebLogic transactions manager popping up in places.

So, the question is do we migrate to the OC4J or keep using the Webtogo container? The major benefit to us would be that we could dictate which JVM the container would use. Currently, the Webtogo container uses the the last JVM that is installed on the Windows PC. Our application is compiled using JDK 1.4.2 _X. But most of the clients are using JRE 1.5. Now, where 1.4.2 is at end-of-life do we even worry about this? Does it affect us in any way? I think there is some deprecated code both from Struts and JDK perspective that we may be using but we should be able to isolate those.

Decisions, decisions, decisions…

If someone came up to us tomorrow and told us we had to use JSF, we would have no other choice but to upgrade. But since we don’t have anyone holding that gun to our heads, we won’t worry about that.

I was at Think NB a couple of weeks ago. Here is a picture of the OAO booth.

Greg Rekounas, OAOT Oracle Solutions Architect and Oracle Lite Technology Leader, featured at Oracle OpenWorld 2007

GREENBELT, MD – November 13, 2007 – OAO Technology Solutions, Inc.(OAOT) announced that its Oracle Practice Area, part of the Strategic Business Solutions line of business, was featured at Oracle’s OpenWorld 2007 in San Francisco, CA. Greg Rekounas, an Oracle solutions architect and ACE director nominee, spoke on the topic of Globalizing a Mobile Application with Oracle Lite. Oracle OpenWorld is the world’s largest event dedicated to helping enterprises understand and harness the power of information.

Mr. Rekounas’ presentation highlighted the successful application development and business process re-engineering for a mobile warranty management system for the world’s largest automobile manufacturer. OAOT consolidated and modernized over 100 disparate systems, provided 24/7 accessibility in both mobile web and offline modes, ensured scalability for up to 30,000 users and supported 26 different languages. The system deployment resulted in significant cost reductions for the customer, to include the initial development, deployment, operations, and maintenance and support costs.

“As a result of this project, OAOT created one of the largest implementations of Oracle Lite by designing and developing an enterprise-wide application to run on the customer’s existing end-user computing and infrastructure platform. Not only was this Oracle Lite’s largest implementation, it was also the automobile manufacturer’s largest and most visible IT project in its history,” stated Mr. Rekounas.

“OAOT solutions architects are recognized by Oracle product and support teams as industry leaders for their technical expertise,” said Duke Dickson, president, Strategic Business Solutions, and executive vice president, Business Development and Marketing for OAOT. “From database engineering to competitive marketplace analysis, OAOT is actually helping to shape Oracle Lite into what it is today.”

OAOT’s Oracle solutions expertise creates greater agility across enterprise applications to completely transform our customers’ business operations. Our proficiency in transition planning and portfolio management enables us to address the most complex business challenges and unique enterprise requirements through the integration of people, process and technology. For more information, visit www.oaot.com.

About OAO Technology Solutions, Inc.
OAOT (www.oaot.com) is a global leader in Managed IT Services and Solutions to Fortune 500 corporations, global outsourcers, and government agencies. The Company’s capabilities include: strategic business solutions, enterprise IT operations, and human capital management. Headquartered in Greenbelt, Maryland, the Company’s 2,000 employees work in over 200 locations throughout the world.

I started a blog with my Oracle OpenWorld account. I will probably be doing some double posting to that blog and this blog about my daily activities at OOW.

Here is the link:
Oracle OpenWorld - The Rekounas Version

Finally! Got my Oracle OpenWorld Connect Card today.

I have been invited to speak at Oracle Open World this year about Oracle Lite. My presentation information and schedule are as follows:

Session ID: S291373
Session Title: Globalizing a Mobile Application with Oracle Database Lite (Featuring J2EE Technology)
Track: INDUSTRIES; TECHNOLOGY; ORACLE DEVELOP; Automotive; Database; Java and Java Enterprise Edition; Database Application Development
Room: Continental Parlor 9
Date: 2007-11-12
Start Time: 15:15

I will be finalizing my presentation in the next few weeks and uploading the presentation.

So, in the last 2 years of working with Webtogo in a international, web farmed environment; here are all the issues I encountered:

• Oracle Lite did not work with UTF8… but does now
• The customized workspace likes the use of absolute paths… even though it’s suppose to be your customized workspace! The servlets are hard coded in many cases to refer to files in directory /userinfo
• Your Struts application can take a long time to load and take up a lot of your system resources on the client… read metalink note 412098.1
• In a international environment, you need to ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=BYTE before running the repwizard. Don’t forget to do this when you are (re)publishing your application too.
• If SQLCOMPATIBILITY is set to SQL92, your TO_NUMBER function doesn’t seem to work properly. You have to cast it cast(to_number('1210.73') as number(6,2))
• When a client hasn’t sync’d in a long time or a lot of data is being deleted from their client database, you might need to set MaxSharedMemSize = 256 in the polite.ini file in the section [All Databases]
• Consperf doesn’t display the correct cost in the explain plans. Oracle support is still looking at this.

In the last 2 years, I have opened 46 metalink support issues. 5 issues are still open.

A couple of weeks ago, I upgraded my client JRE from 1.4.2_03 to 1.4.2_13. When I did this, the Cipher encryption on my site started throwing an exception:

java.lang.NoClassDefFoundError at javax.crypto.Cipher.getInstance(DashoA6275)

So, I opened a metalink ticket and was able to resolve the issue by adding the following parameter to the webtogo.ora file on my client:

[WEBTOGO]
NO_URL_FACTORY=YES

I haven’t found any documentation on this parameter. But a fellow developer seems to have an idea. He wrote to me:

1. java.lang.ExceptionInInitializerError
at javax.crypto.Cipher.a(DashoA12275)
at javax.crypto.Cipher.getInstance(DashoA12275)
at com.gm.gwm.common.util.AesUtil.encrypt(AesUtil.java:17)
at Caused by: java.lang.SecurityException: Cannot set up certs for
…
2. trusted CAs
at javax.crypto.SunJCE_b.(DashoA12275)
…
3. Caused by: java.lang.IllegalStateException: Already connected
at java.net.URLConnection.setUseCaches(Unknown Source)
at sun.net.www.protocol.jar.JarURLConnection.setUseCaches

Exception #1…
The AES cipher blows up because can’t load the signed certificate authorities.

Exception #2…
The trusted CAs don’t load because setUseCaches() is being called after a connection.

Finally Exception #3…
JavaDoc…
public void setUseCaches(boolean usecaches)
Parameters:
usecaches - a boolean indicating whether or not to allow caching
Throws:
IllegalStateException - if already connected

We finally reach the bottom and realize that what is happening is that web-to-go is making a connection to the remote server and then using setUseCaches()… bad Oracle, bad boy!

My guess Greg, is that previous they were able to get away with this but then Sun fixed the issue after patch 03. I looked at the AES code and it looks OK and I even took a look at the cacerts (Certificate Authority signed certificates) to ensure they were valid.

1. Install either the Mobile Client or Mobile Development Kit.
2. Launch JDev.
3. Open the Connections Tab.
4. Right-click on Database and click New Database Connection.
5. Select Third Party JDBC Driver and click Next.
   
6. Enter your username and password. If it is a replicated database, username is SYSTEM.
   
7. For your url enter jdbc:polite:DSN.
   
8. You will also have to setup the Oracle Lite JDBC Driver. Click New and enter the following:
   Driver Class = oracle.lite.poljdbc.POLJDBCDriver
   Library = polite
   Classpath = C:\mobileclient\bin\olite40.jar
   
9. Click Ok and then Next and you will now be on the Test tab. Click Test and your connection should now be established.
   
10. You can now right-click on the database connection you created and launch a SQL Worksheet or navigate through the objects (tables, views, sequences, and synonyms) in the tree.

Update: Alternative method

1. Open C:\jdev1013\jdev\bin\jdev.conf
2. Modify the environment variables AddNativeCodePath and AddJavaLibFile:
   
#
# Oracle10g Lite Settings
# Enable these lines (and modify the directories)
# to suit your Oracle Lite installation
#
AddNativeCodePath C:/oracle/Mobile/Sdk/BIN
AddJavaLibFile C:/oracle/Mobile/Sdk/BIN/OLITE40.JAR

3. Start or restart JDeveloper.
4. Open the Connections Tab.
5. Right-click on Database and click New Database Connection.
6. Select Oracle Lite and click Next.
7. Enter the username and password and click Next.
8. Enter the datasource name (DSN) and click Next.
9. Click Test Connection. You should have a “Success!” message. Now click Finish