In this post, I will try to give a quick overview of setting up Berkeley DB to Sync with Mobile Server. It is important to note that we will be overriding the SQLite deployment.
Step 1: Building the Berkeley DB Here is the document to get you started.
Step 2: Rename Binaries
Once you have built the binaries (libdb_sql50.dll and dbsql.exe) rename those files to sqlite3.dll and sqlite3.exe.
Step 3: Copy File to the Mobile Server
Copy those files to the Mobile Server under the following path:
$ORACLE_HOME/mobile/server/admin/repository/setup/common/win32/sqlite/
Modify the following file:
$ORACLE_HOME/mobile/server/admin/repository/setup/dmc/common/sqlite_win32.inf
Add the following entry that is in bold lettering:
This is optional. The sqlite3.exe is equivalent to msql or sql*plus.
You should now be able to run the setup.exe for the SQLite Win32 and the client will be set up for Berkeley DB SQL API.
If you perform a sync with the SQLite client, your directory structure will look something like this:
08/26/2010 01:25 PM 41,984 default.db
08/26/2010 08:42 PM 67,584 fod.db
08/26/2010 08:42 PM 20,480 OSE$default.db
08/26/2010 08:42 PM 34,816 OSE$fod.db
By using the Berkeley DB SQL API, your structure will differ by having <database>-journal folders.
08/26/2010 09:44 PM 32,768 default.db
08/26/2010 09:44 PM <DIR> default.db-journal
08/26/2010 09:44 PM 32,768 fod.db
08/26/2010 09:44 PM <DIR> fod.db-journal
08/26/2010 09:44 PM 32,768 OSE$default.db
08/26/2010 09:44 PM <DIR> OSE$default.db-journal
08/26/2010 09:44 PM 32,768 OSE$fod.db
08/26/2010 09:44 PM <DIR> OSE$fod.db-journal
For a lot of the Oracle SQL Developers, some things you will need to brush up on while using the new SQLite environment. Lack of Stored Procedures… but since Berkeley DB source code is availble as open source, a developer should be able to add functions to be able to perform the same tasks. SQLite doesn’t support sequences in the same way Oracle supports sequences. So, to have the ability to sync your sequences to the client, you will need to performance the following sql statements to keep your client and server in sync for sequences.
To get the nextval of a sequence SELECT IFNULL((CUR_VALUE + INCREMENT_BY ), MIN_VALUE )
FROM C$SEQ_CLIENTS
WHERE NAME = 'MY_SEQ';
To update the sequence with the current value UPDATE C$SEQ_CLIENTS
SET CUR_VALUE = IFNULL((CUR_VALUE + INCREMENT_BY ), MIN_VALUE )
WHERE NAME = 'MY_SEQ';
Other differences are obvious ones like SQL functions. As I do a little bit more development with Berkeley DB, I will try to benchmark it against the SQLite flavour and the Oracle Lite database to see how everything stacks up.
I am headed to San Francisco again this year for Oracle OpenWorld. I will also be a co-speaker for the following session:
Session ID: S317033 Title: Oracle Berkeley DB: Enabling Your Mobile Data Strategy Abstract: Mobile data is everywhere. Deploying applications and updates, as well as collecting data from the field and synchronizing it with the Oracle Database server infrastructure, is everyone’s concern today in IT. Mobile devices, by their very nature, are easily damaged, lost, or stolen. Therefore, enabling secure, rapid mobile deployment and synchronization is critically important. By combining Oracle Berkeley DB 11g and Oracle Database Lite Mobile Server, you can easily link your mobile devices, users, applications, and data with the corporate infrastructure in a safe and reliable manner. This session will discuss several real-world use cases.
Speaker(s): Eric Jensen, Oracle, Principal Product Manager
Greg Rekounas, Rekounas.org, Oracle Mobile Consultant Event: JavaOne and Oracle Develop Stream(s): ORACLE DEVELOP, DEVELOP
I am quite exited about going this year. Oracle Mobile Server is in the process of changing the client database from Oracle Lite Database to the Oracle Berkeley Database. I will be testing out some of these features and hope to have a how-to post about getting your synchronization working with Berkeley DB soon.
Before you read too far into the note, this is note for the decommissioning or the Oracle Lite Database Client. The server, sync, and device manager along with the development tools are still being developed. The client database will be Oracle Berkeley DB with SQL support via SQLite. Basically, if you are on Webtogo or the Branch Office, these deployments will be in maintenance (which means no new development) .
Oracle Database Lite client sunsetting announcement [ID 1124003.1] Applies to:
Oracle Lite - Version: 10.0.0.0.1 to 10.3.0.3.0 - Release: 10.0 to 10.3
Information in this document applies to any platform. What is being announced?
We are announcing changes to the Oracle Database Lite product.
Some components will be shifted to maintenance mode, while others will continue to be actively developed and enhanced.
The Oracle Lite Client Database, as well as Branch Office and the Web-to-Go components, will be moved to maintenance mode.
The core underlying framework, including the Mobile Sync Client and Server, Device Manager and the Mobile Device Workbench, will continue to be actively developed.
Oracle Database Lite will be removed from the pricelist at the end of Q2FY11 (end of November 2010). What do you need to do?
The primary product Oracle recommends to replace the Lite Client Database is the Oracle Berkeley Database, which is a fast, highly scalable, embeddable, transactional database.
Berkeley DB 11.2.5.0.21 has been enhanced with SQL support (SQLite SQL), in addition to its traditional key/value store interface. This is for situations where the customer requires a lightweight embedded database, with the ability to synchronize with the Oracle Database.
In situations where a multiuser database server is required, such as in existing Branch Office configurations, Oracle Standard Edition One is the recommended alternative. However, these products do not overlap 100%, so some application changes may be required.
In cases where customers are using Web-to-Go on the client side to run JAVA Servlets, the customer will need to replace Web-to-Go with a Servlet container of their choosing. Oracle is not offering an official mobile Servlet container at this time.
Who to contact for more information?
Oracle Database Lite will continue to be supported by the Oracle Support Team. For release 10.3, Premier Support will continue until December 2012. After that, Extended Support will continue until the end of 2015.
During both the Premier and Extended Support phases, Oracle Sustaining Engineering will provide program updates, fixes, security alerts, and critical patch updates to Oracle Database Lite customers. As with all Oracle products, support is available indefinitely to customers who purchase Lifetime Support.
Those customers will continue to have access to Oracle’s repository of security alerts, patches, solutions, and other documentation. Lifetime Support will be available beyond 2015, for as long as necessary.
For more information regarding the Oracle Support Policy, please refer to the Technical Support Policies page:
http://www.oracle.com/support/policies.htm
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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!
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.
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.
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
He might be able to give you a hand with your performance testing.
Ever since I started working with Oracle Lite, I have worked with Win32, Webtogo, or OC4J applications. This week, I am picking up a 
