Rows and Columns

Just a notice that we have recently published a new ebf for SQL Anywhere 9.0.2 (build 3924) that has been tested and is certified to run on Windows 7.

Download it from the Sybase Software Downloads site

Also, as a reminder, you can find all of the SQL Anywhere supported platforms information on the SQL Anywhere Supported Platforms page.

Thanks to Breck Carter’s SQL Anywhere blog, I found out about Google custom search and web elements.
As a result, I have created a custom search for SQL Anywhere technical content, from the online documentation, technical papers, blogs and newsgroups.
Give it a try and let me know what you think.

Innsbruck is the codename for the the upcoming version of SQL Anywhere. The beta registration page is now live. You can sign up here

Stay tuned to this blog and the SQL Anywhere homepage for further news and announcements related to the beta.

I have heard from customers that connecting to SQL Anywhere over JDBC can be difficult at times. In my investigations of this, I have found that this is almost always due to confusion over the classname to use to register the JDBC driver, and the URL’s to use to actually connect to the database. In stepping back, I can see how people might easily get confused based on the history of the JDBC driver. Here is my attempt to clarify things by following the history of the driver, starting with SQL Anywhere version 9.
[Read more →]

SQL Anywhere is a versatile database. One of its strengths is the breadth of operating systems on which SQL Anywhere will run. One of the more unique things about SQL Anywhere is that the (almost) full server can run on Windows CE. Our customers often take advantage of this fact and stretch the limits of their mobile devices. For example, many customers will throw a flash card in their device, and run big databases (for a mobile device anyway) on them with an application that runs as if it were on a desktop machine. This can lead to problems, aside from the obvious performance complications of running on a mobile device without considering the underlying hardware in the application design.
One of the more serious problems is that not all flash cards are created equally. As a result, we occasionally get reports from customers where after a few months of running their application, the flash card dies, or begins to corrupt their databases. Usually, this is because the flash card has reached its end of life limit in terms of writes – basically you can only write a specific block on a flash card so many times before it wears out and becomes unusable. The only resolution for this is to replace the card. Having to replace flash cards as often as every month is a headache one doesn’t want or need.
I had a conversation with a customer and some of the SQL Anywhere engineering team a few weeks ago, and we came up with some conclusions and suggestions that I think could be useful in at least extending the life of flash cards used heavily for database activity in mobile applications:

1. First, note that SQL Anywhere does not control where data is physically written on disk. The OS and disk controller/utilities manage this. With a flash card (especially a cheap one), the card is not likely to have much (if any) smarts on it in terms of controlling things like wear levelling (spreading writes evenly across the disk, which can extend the life of a card significantly).
2. Second, note that SQL Anywhere will first use existing free pages in the database file before growing the file to add new pages to use, which is typically what you want to keep the size of the database file from growing out of control and containing a lot of empty/wasted space.

The above 2 factors means that over time, if your application is adding/deleting lots of data, or updating the same small amounts of data repeatedly, you can wear your disk out quickly if the underlying writes are always sent to the same block on disk. Even updating just few bytes of data can be a problem, since the block size for writes to flash are generally large (8-128k). So, for example, a change of an 8 byte decimal value may cause a write of as much as 128k.

If you think you may be running into a problem like this with your applications (some vendors ship utilities that let you examine the wear pattern on a flash card, which would help identify this problem), there are a few things that may extend the life of the card:

1. Periodic defragmentation of the card and database file (or reorganize table) – this will move things around on the card, so pages that are often written may be moved to less wore portions of the card.
2. Periodic simple file copy of the database file – again this can level out the wear pattern on the card by moving the file to another location on the card.
3. If the card block size is large, consider increasing the page size of the database file so that more data is written with a single write – eg. if block size is 128k and database page size is 4k, writing 32k (8 pages) would require 8 separate writes of that same 128k. However, if page size is 32k, only a single write would occur to write that 32k (one page). Note that changing page size can affect both database size and performance, so you will want to test this before going to production with it.
4. Consider increasing the time between checkpoints, to decrease the frequency of card writes. Note that delaying checkpoints can increase recovery time.
5. Consider moving the log file to a different storage medium. Frequent writes to to the log definition page, or frequent, small transactions can cause lots of writes to the same blocks on the card.
6. Increase your transaction sizes – fewer commits will decrease the amount of disk writes.
7. Consider evaluating different manufacturers before recommending a flash card for your application – look closely at the card specifications. There are definitely different qualities of flash/SD cards.
8. Finally, depending on the form factor of the device, you might also consider moving to an SSD drive. These are solid state disks like flash cards, but have lifetimes that match traditional hard disk drives because they have lots of smarts to do things like wear-levelling for the disk. However, the price and form factor does reflect this.

I don’t have any hard numbers to validate the effectiveness of the above suggestions. Yet another thing for my todo list …

In my previous post I explained how one could create a webservice call from within SQL Anywhere to geocode addresses that might be stored in the database.
A somewhat less used, but nonetheless useful ability, is reverse geocoding. That is where, given a lattitude/longitude pair, we look up the address and other location information associated with those coordinates.
[Read more →]

Geocoding takes a regular address and transforms it into a lattitude/longitude location on the earth. It is a very useful activity if you have a database full of addresses and you want to start applying some basic geo-spatial functionality (such as calculating distances) or start doing some more in depth spatial application development.
To be able to geocode an address, you need to have access to a dataset which can effectively find the address you want on a map and return to you the co-ordinates. There are a few online services which can do this, one of which is Google. Google provides a web services api to the Google maps data.
[Read more →]

It has been a long process getting our software in shape and passing through all of the various hurdles that would allow East Port to build and offer secure business solutions based on SQL Anywhere, called E-Secure Anywhere. It has finally happened:
“E-Secure Anywhere can now be widely deployed in environments that require special security needs, such as government agencies.”

See the full press release here:
Sybase, East Port Joint Product Becomes First Business Database to Receive China’s Security Certificate

Previously, I talked about creating a tag cloud, aggregating tag information from multiple blogs.
In order to create/display the tag cloud on the Sybase blog homepage, we had to determine how to integrate the data and the CSS that was generated to display the tag cloud. After some discussion, we decided to feed the tag cloud data to the page using javascript.
[Read more →]

Now that the new blogs have been up for a while and everything seems stable, the Sybase blogging homepage is undergoing some changes. One of the things that was recently added is a tag cloud, which allows visitors to get a quick picture of the topics the Sybase bloggers are blogging about, and even click through to view the list of articles associated with a specific tag. There is no mandated tag set, so the tag cloud is quite large on the page in its full view, which is why there is a shortened view available by default which gives a sampling of the tags.

While the visual representation of the tag cloud was done by the Sybase Web Marketing team, the actual data comes from the blog content. Since I had already experimented and figured out how to generate RSS from the database, the only thing I really had to figure out was how to get the tag information out to the tag cloud.

I started by determining where the tag information was stored in the database:

SELECT name AS tag, "count";
FROM terms t, term_taxonomy tt
WHERE t.term_id = tt.term_id AND taxonomy = 'post_tag' AND "count" > 0;

This gave me the tag list for a single user. Since I created each blogger with their own set of tables in the same SQL Anywhere database, I simply had to union this query together once for each blogger, changing the table names each time. At some point in the future, I may make this more dynamic so it automatically picks up new bloggers, but for now it works fine:

SELECT name AS tag, count 
FROM wp_terms t, wp_term_taxonomy tt
WHERE t.term_id = tt.term_id AND taxonomy = 'post_tag' AND "count" > 0;
UNION ALL
SELECT name AS tag, count 
FROM wpuser1_terms t, wpuser1_term_taxonomy tt 
WHERE t.term_id = tt.term_id AND taxonomy = 'post_tag' AND "count" > 0;
...

As I said above, there is no standard tag list for the bloggers. This meant there was a risk of different bloggers using the same tag with different case and the tag would show up multiple times. In addition, I wanted to eliminate any tag that was only used once in total across all blogs, and return the combined count for each individual tag. I did this using a group by and having; clause. My final query looked something like this:

SELECT LOWER(tag) AS tag, sum(count) AS count 
FROM (SELECT name AS tag, count 
	FROM wp_terms t, wp_term_taxonomy tt
	WHERE t.term_id = tt.term_id AND taxonomy = 'post_tag' AND "count" > 0;
      UNION ALL
      SELECT name AS tag, count 
	FROM wpuser1_terms t, wpuser1_term_taxonomy tt 
	WHERE t.term_id = tt.term_id AND taxonomy = 'post_tag' AND "count" > 0;
      UNION ALL
      ...
     ) AS T1
GROUP BY tag 
HAVING count >  1 
ORDER BY SORTKEY(tag, 'nocase');

Notice the result set is sorted by tag. This made it easier to classify the tags into various categories for display purposes.

Now that I had the data, I had to put it into a format that made it easy/useful for the Sybase Web Team to display. I’ll talk about that in my next post.