We are developing applications with SQL Anywhere 11.0.1 and PowerBuilder 11.5.1. Now we have a big issue with our database (see my previous question about inconsistent results). I have tested the same query with the newest SQL Anywhere Version 16 and can not reproduce the issue. So we plan to upgrade the database to version 16 from version 11.

Have anybody experience with upgrading the database from 11 to 16?

Are there any known compatibility issues between SQL Anywhere 16 and PowerBuilder 11.5.1?

Would you recommend to rebuild (unload/load) or to upgrade to SQL Anywhere 16?

What if we don't upgrade/rebuild the database, so that the database is backwards compatible? Do we have to expect problems if we just run the v11 database under the SQL Anyhwere 16 server?

asked 22 Jul '13, 11:27

sebyz's gravatar image

accept rate: 0%

edited 22 Jul '13, 11:47

I am not aware of any compatibility issues between SA16 and PB 11.5.1. That said, there is a reasonably large gap between when these products were released of roughly 4 years so there could be some issues - but those should be minor.

You should not have problems running an v11 database on an SA16 engine. There could be features however that are dependent on a newer file format. If the only reason for this move is to address the issue that you noted, a file format change may not be needed.

(22 Jul '13, 11:48) Chris Keating
Replies hidden

Thank you for the comment. Another question: Do we have to upgrade the ODBC-Client driver to Version 16 or is it possible to leave the ODBC-Client driver on Version 11 and connect to the SQL Anywhere Server Version 16? (On my machine it worked, it would be easier to upgrade if we don't have to upgrade every client station)

(22 Jul '13, 12:21) sebyz

You can leave the client software unchanged. That should minimize the overall effect on the client applications, if any.

(22 Jul '13, 14:19) Chris Keating

I can't answer the PB to V16 issue yet but I can say there was at least one issue that snagged us going from SA 11 to SA 12 - and this would affect any developer - not just Powerbuilder.

In SA11 and earlier versions database retrievals had a natural order to them - meaning the rows came back in the order of keys or sequence entered (cant remember which) when there was not a specific detailed order by or datawindow sort designated. In v12 and I assume V16 there is guaranteed no sequence at all and they return in very random fashion.

Is is therefore possible that some of your programs or reports that looked ordered in V11 will suddenly look very disordered. The cure to this is to inspect every Query to insure you specify the required order - either in the SQL (which can tie the system up a bit) or in the data windows. It took us quite a while to go thru many hundreds of data windows to clean this up.

permanent link

answered 23 Jul '13, 09:27

Glenn%20Barber's gravatar image

Glenn Barber
accept rate: 0%


It was always the case that server can return result set in any order including no order :). For me, only simplest queries return results in some kind of logical order.

(23 Jul '13, 09:39) Dmitri

Yes - but any kind of sort on the server has implications related to locking and performance in a Multi- user transaction environment. Powerbuilder has extremely fast client interfaces and fast internal data window sorts - so most of the time for anything not very large - we sort in the data window - however for journals and other transaction reports it was easy for the programmer to forget to assert a final sort as the transactions came out ordered in V11 and earlier.

(23 Jul '13, 09:50) Glenn Barber

It has always been the case that row order was not deterministic in the absence of an ORDER BY. From the SA10.0.1 docs, "(t)he only way to ensure that rows are returned in a particular order is to use ORDER BY. In the absence of an ORDER BY clause, SQL Anywhere returns rows in whatever order is most efficient. This means that the appearance of result sets may vary depending on when you last accessed the row and other factors." Similiar documentation will exist in most if not not all versions of SQL Anywhere.

In your case, you happened to get lucky that the results appeared to be returned in "a natural order" on a consistent basis - a different query or different set of operational conditions could have resulted in a different result order. My guess is that SA12 exposed this behaviour in your application because it is using different access plans than used in prior versions.

(23 Jul '13, 10:05) Chris Keating

"either in the SQL (which can tie the system up a bit)" - for a result set fit for human consumption (such as a visible DataWindow) it is surprising that a server-side ORDER BY will have any effect on performance. If true, it might be an early warning that the server is grossly under-sized.

On the other hand, if the result set is gargantuan, and always filtered on the client side... but even then, it would likely be the client-server communication time that is the bottleneck rather than the ORDER BY.

Unless, again, the server was feeble. The traditional arguments for moving functionality to the client side have faded over time, replaced with "it doesn't matter" in most cases.

As far as ordering being a behavior change between 11 and 12 is concerned, not true. You had a bug (no explicit ordering), it resulted in a symptom, you fixed it (good for you!)

Here's a bad analogy: You used a car as a boat, and it worked... the car floated (it can happen, see the video of the Japanese tsunami).

Then you traded in that car, and the new one sank. You survived! :)

(23 Jul '13, 11:16) Breck Carter
Replies hidden

It is the case that every upgrade exposes "bugs" - but nonetheless it is real work to be done - and better if forewarned. We were a little surprised by it.

From you comment it seems to imply that ordering a large query on the server that involves rows used by many concurrent users has much less impact on concurrent users than in the past. How much has that changed recently?

Nowadays - with application publishing, the client is on the server - so network overhead is less of an issue - however the application datawindow sometimes still needs to get a fairly significant set of rows to generate reports from (10K plus) - so I think in our case the client datawindow in-memory sorting might still be less intrusive in a multi-user environment.

(23 Jul '13, 11:44) Glenn Barber

AFAIK, long ago, v8 introduced a noticeably more randomized output for many queries, such as documented in its "Behaviour Changes" doc page:

Row ordering
A side-effect of improvements to query processing for version 8.0 is that row ordering is less deterministic. In the absence of an ORDER BY clause, Adaptive Server Anywhere returns rows in whatever order is most efficient. This means the appearance of result sets may vary depending on when you last accessed the row and other factors. The only way to ensure that rows are returned in a particular order is to use ORDER BY.

The LIST function is among those functions particularly affected by this change.

The cure has been the same, apparently: ORDER BY.

(23 Jul '13, 12:01) Volker Barth
showing 5 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 22 Jul '13, 11:27

question was seen: 2,503 times

last updated: 23 Jul '13, 12:02