Monday, April 29, 2013

Impersonation: Feature or Crime?

SQL Anywhere contains a little-known feature called SETUSER which "[a]llows a database administrator to assume the identity of another authorized user on the same connection".

Here's an example... First, the DBA creates an important table containing an inserted_by column for audit trail purposes, plus two other user ids:

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql;CON=ddd12-DBA"

CREATE TABLE DBA.important ( 
   pkey         INTEGER          NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data         VARCHAR ( 30 )   NOT NULL,
   inserted_by  VARCHAR ( 128 )  NOT NULL DEFAULT CURRENT USER,
   inserted_at  TIMESTAMP        NOT NULL DEFAULT CURRENT TIMESTAMP );

GRANT CONNECT TO EvilDoer IDENTIFIED BY '6kfiwn3gk';
GRANT DBA TO EvilDoer;

GRANT CONNECT TO InnocentVictim IDENTIFIED BY 'nlr9bk6j';
GRANT DELETE, INSERT, SELECT, UPDATE ON DBA.important TO InnocentVictim;
Second, the EvilDoer administrator runs a SETUSER statement to masquerade as InnocentVictim, then inserts a row in the important table:
"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=EvilDoer;PWD=6kfiwn3gk;CON=ddd12-EvilDoer"

SELECT CURRENT USER AS "Before";

SETUSER InnocentVictim; -- start masquerading

SELECT CURRENT USER AS "During";
INSERT DBA.important ( data ) VALUES ( 'im in ur base killin ur d00dz' );
COMMIT;

SETUSER; -- remove mask

SELECT CURRENT USER AS "After";
SELECT * FROM DBA.important;
The four SELECT statements show how SETUSER changes the effective CURRENT USER value to and from 'InnocentVictim', and how the row in the important table looks like it was inserted by InnocentVictim:
Before
-------------- 
EvilDoer

During
-------------- 
InnocentVictim

After
-------------- 
EvilDoer

pkey  data                            inserted_by     inserted_at
----  ------------------------------  --------------  -----------------------
1     im in ur base killin ur d00dz   InnocentVictim  2013-04-28 09:21:40.591

No bias here! <g>

The names "EvilDoer" and "InnocentVictim" were chosen to emphasize a security loophole created by SETUSER: the ability of one single user to impersonate another user without knowing that user's password, and without requiring the collusion of anyone else.

Permissions aren't the issue here; both EvilDoer and InnocentVictim can INSERT anything they want in the important table.

The problem is that EvilDoer can insert a row and pretend it was InnocentVictim that did it... the audit trail is now worthless because the CURRENT USER special value is no longer guaranteed to return the current user.

Why does SETUSER exist at all?

Historically speaking, the stated purposes for SETUSER include the following:
  • Creating objects You can use SETUSER to create a database object that is to be owned by another user.

  • Permissions checking By acting as another user, with their permissions and group memberships, a database administrator can test the permissions and name resolution of queries, procedures, views, and so on.

  • Providing a safer environment for administrators The database administrator has permission to perform any action in the database. If you want to ensure that you do not accidentally perform an unintended action, you can use SETUSER to switch to a different user ID with fewer permissions.
Those are pretty feeble reasons:
  • CREATE TABLE can specify the owner without having to use SETUSER,

  • one person can have multiple user ids with different privileges, without having to impersonate a different person, and

  • the CONNECT USING statement is almost as easy to use as SETUSER when switching to a different user id, and safer too because it requires a password.

We report, you decide

In SQL Anywhere Version 12, the Help doesn't promote SETUSER other than providing the basic syntax topic, and it doesn't use the word "impersonation" at all, at least not in the context of SETUSER.

All that has changed in Version 16; "Impersonation" is now a huge topic, with a whole new reason for SETUSER to exist:
"Suppose a data entry clerk, JSmithClerk, is having difficulties performing an operation in the database. He is talking with PJonesIT in the IT department, and PJonesIT decides to impersonate JSmithClerk to observe and troubleshoot the problems that JSmithClerk is experiencing. In order for PJonesIT to impersonate JSmithClerk:" and so on ...
plus a whole new set of rules and regulations governing SETUSER.

In other words, SETUSER has been released from its cage (documentation obscurity in Version 12 and earlier) and its days as a "little-known feature" may be numbered.

One argument in favor of SETUSER goes like this: "DBAs can do anything, even directly UPDATE the inserted_by column, so SETUSER is no big thing."

That's true in Version 12 and earlier, SETUSER can only be used by DBAs. In version 16, however, the new role-based access control (RBAC) security model separates the SET USER privilege from other administrator privileges. By using the new GRANT SET USER statement, plus a view to limit direct access to the inserted_by column, the EvilDoer no longer has to be a DBA. The following example shows how EvilDoer can be set up as an ordinary user who can still impersonate another user:
CREATE VIEW DBA.Vimportant AS
SELECT pkey,
       data
  FROM DBA.important;
 
GRANT CONNECT TO EvilDoer IDENTIFIED BY '6kfiwn3gk';
GRANT SET USER TO EvilDoer;
GRANT DELETE, INSERT, UPDATE ON DBA.Vimportant TO EvilDoer;
GRANT SELECT                 ON DBA.important  TO EvilDoer;

GRANT CONNECT TO InnocentVictim IDENTIFIED BY 'nlr9bk6j';
GRANT DELETE, INSERT, UPDATE ON DBA.Vimportant TO InnocentVictim;
GRANT SELECT                 ON DBA.important  TO InnocentVictim;
Another argument in defence of SETUSER goes like this: "The transaction log shows all the SETUSER statements so EvilDoer can't hide from the authorities."

That's true, but it raises the bar (or lowers the bar, if you prefer limbo dancing to pole vaulting) on preserving evidence... first you have to save all the transaction log files, then you have to guarantee you're not missing any, and then you have to plow through them all.

Here's a little story about searching transaction logs: In 2006 I was engaged as a "Special Master" by the Southern District Court of Florida, Miami Division, in the copyright infringement suit "Dunn and Dunn Data Systems v. EncomPos Software LLC". Part of my job was to answer the questions "what did they do and when did they do it", and that involved searching through endless backup copies of SQL Anywhere transaction logs from multiple databases... let's just say I had to repeatedly remind myself of the Consultant's Golden Rule: If They Want To Pay Me THIS Much Money To Pick Cigarette Butts Out Of The Urinals, I'll Do It With A Smile. The point is, searching for evidence in transaction logs is a lot harder than querying a application-level audit trail.

"How do I get rid of SETUSER?"

You can't, not in SQL Anywhere Version 12 and earlier. Somebody has to be DBA, and that person can use SETUSER, so that person can impersonate anyone.

In Version 16, however, there might be a way to create a database where even the administrators can't use SETUSER.

Or maybe not... REVOKE SET USER doesn't seem to have any effect after GRANT DBA:
GRANT CONNECT TO DBA2 IDENTIFIED BY 'h5lfhg86jd';
GRANT DBA TO DBA2;
REVOKE SET USER FROM DBA2;
In other words, DBA2 can still execute SETUSER... so much for brute force.

Three possible explanations come to mind:
  • I'm missing something, or

  • GRANT DBA is old-school, and there's something in the miles and miles of new rules and regulations explaining why REVOKE SET USER only works after you have drunk the Kool-Aid fully committed to the new security model, or

  • it's a bug.

Dilbert.com 2010-03-14

Friday, April 26, 2013

Yet Another (sigh) SQL Anywhere Q&A Forum

Did you know that for a brief period of time, there were not one, not two, but three official question-and-answer forums devoted exclusively to SQL Anywhere?

  1. the now-defunct NNTP newsgroups,

  2. the excellent SQLAnywhere-Forum.sybase.com website, and

  3. the brand-new "SAP Sybase SQL Anywhere Discussions (Forum)".
The good news is, the NNTP newsgroups are finally gone... after several years of inaction by Sybase it took a takeover by SAP to make it happen.

The bad news is, once again there are two different forums to choose from when deciding where to ask a question about SQL Anywhere (2 and 3 above).

The good news is, the new SAP forum appears to be indexed by Google (whew! it didn't have to be the case).

The bad news is, the whole SAP Community Network website has an awkward, cluttered and confusing design... and in particular the SAP SQL Anywhere forum is clunky and hard to use when compared with SQLAnywhere-Forum.sybase.com.

Enough windmills!


The SAP Community Network website can't help the way it is: it's a all-encompassing corporate creation with no place for best-of-breed solutions like the software running SQLAnywhere-Forum.sybase.com. Sybase almost created one of those (the Sybase Developer Network) but decided not to waste spend the money.

To put it another way, if SQLAnywhere-Forum.sybase.com is replaced by the SAP forum, it's not the end of the world... having one place to ask questions might be a good thing.

On the other hand, if SQL Anywhere is put through some kind of committee meat-grinder, THAT'S the end of the world.

Dilbert 2009-10-09


Monday, April 22, 2013

Latest SQL Anywhere EBFs: 12.0.1 Linux Docs, 11.0.1 Windows

Current builds for the active platforms...

HP-UX     12.0.1.3798 EBF                    29 Oct 2012
 Itanium  11.0.1.2958 EBF                    08 Apr 2013

IBM AIX   12.0.1.3798 EBF                    24 Oct 2012
          11.0.1.2958 EBF                    08 Apr 2013

Linux     16.0.0.1324 GA                     05 Mar 2013
          12.0.1.3873 EBF                    05 Apr 2013
          12.0.1 Chinese Docs (Eclipse), *** 16 Apr 2013 ***
                 Japanese Docs           *** 16 Apr 2013 ***
          11.0.1.2958 EBF                    08 Apr 2013

Mac OS    12.0.1.3871 EBF                    05 Apr 2013
          11.0.1.2449 EBF                    29 Jun 2010

Solaris   12.0.1.3798 EBF                    24 Oct 2012
 SPARC    11.0.1.2958 EBF                    08 Apr 2013

Solaris   12.0.1.3798 EBF                    29 Oct 2012
 x64      11.0.1.2958 EBF                    08 Apr 2013

Windows   16.0.0.1324 GA                     05 Mar 2013
          12.0.1.3873 EBF                    08 Apr 2013
          12.0.1 French Docs (HTML/PDF),     25 Sep 2012
                 English Docs,               25 Sep 2012
                 German Docs,                25 Sep 2012
                 Chinese Docs,               28 Mar 2013
                 Japanese Docs               28 Mar 2013
          11.0.1.2960 EBF                *** 16 Apr 2013 ***

Other Stuff...

 Older EBFs

 Free support! Q&A forum
   ...or, call Tech Support

 SQL Anywhere...
   ...Sybase home page 
   ...SAP home page 
   ...SAP Developer Center 

 Buy SQL Anywhere 

 Developer Edition... 
   [16.0] [12.0.1] [11.0.1]

 Download the...
   Educational Edition 
   Web Edition 

 Supported Platforms...
   SQL Anywhere 
   Linux 
   OnDemand

 ODBC Drivers for MobiLink

The three asterisks "***" show which items have appeared on the website since the previous version of this page.
  • Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1 and 16.0) are shown here.

  • Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released.


Friday, April 19, 2013

It's Official! 16 on 16

All you folks already using SQL Anywhere 16, you can come out of hiding now: SAP made it officially available on April 16.

Here's the word from Walldorf...

SAP Gives Customers Remote Access to Data-Driven Applications to Extend the Power of Real-Time Data

April 16, 2013 | SAP - Database Technology

New Capabilities in SAP® Sybase® SQL Anywhere® 16 Suite Extend SAP HANA® Platform Data to and from Satellite Server and Mobile Environments

WALLDORF, Germany - SAP AG (NYSE: SAP) today announced the availability of the SAP® Sybase® SQL Anywhere® 16 suite, the latest release of data management and synchronization technology. As a key element of and satellite server within the SAP® Real-Time Data Platform, the suite helps extend business transactions to remote office locations. It enables the rapid development and deployment of database-powered applications in embedded, software-as-a-service (SaaS), remote and mobile environments. Powerful, autonomic data management and data synchronization with enterprise-wide applications allows data collection and aggregation from the "Internet of Things " into the SAP Real-Time Data Platform.

"SAP continues to advance its real-time database capabilities by integrating key Sybase database technologies such as SAP Sybase SQL Anywhere into its comprehensive enterprise information platform," said Carl Olofson, research vice president for application development and deployment, IDC. "The new version of SAP Sybase SQL Anywhere includes key improvements such as expanded query optimization for faster response times, which makes access of enterprise data on a mobile platform more practical, and security enhancements for protection of critical business data, which helps put business managers at ease regarding the distribution of sensitive data to mobile devices. These technologies work in combination with solutions such as SAP HANA decreasing administrative complexity, allowing SAP to enable practical, easy to use enterprise data delivery to users anywhere, any time."

The SAP Sybase SQL Anywhere suite and data management solutions from SAP are built to make server, desktop, embedded and mobile applications as reliable, secure and available as the applications that run the data centers of traditional enterprises. The suite provides organizations with easily available remote access to data-driven applications at the edge of the enterprise and the ability to synchronize changes and updates to maintain consistency with enterprise data. SAP Sybase SQL Anywhere is widely used by solution providers and enterprises as a low-administration, highly reliable embedded database of choice for solutions running in embedded, mobile and satellite environments. Paired with the SAP HANA® platform, SAP Sybase SQL Anywhere now offers application flexibility to the point of communication and richer customer interactions whenever and wherever they need to occur.

CleanPoint has developed an elegant monitoring and control system that enables homeowners to manage their electricity and appliance usage in conjunction with new Smart grid systems. The CleanPoint solution is a user friendly application appliance powered by SQL Anywhere using data collected from the homeowner's own electricity usage.

"In building our solution, CleanPoint realized that that a big data high velocity solution is the only way to analyze and process the staggering amounts of data that the Smart grid produces with speed, security and integrity," said Bruce Johansson, joint managing director, CleanPoint "Our extensive evaluation showed that SAP Sybase SQL Anywhere 16 has an unmatched ability to integrate with what we assessed is the leading "Big Data solution for utilities, SAP HANA to deliver the results our clients require to monitor and adjust their electricity usage in real-time. SAP Sybase SQL Anywhere 16 and SAP HANA handle inevitable network failures with ease. The SAP systems reconnect and synchronize seamlessly, there is no loss of data, integrity is maintained without compromise, improving the efficiency of the power grid - delivering consumers a big win by improving the value of their power bill."

"SAP Sybase SQL Anywhere 16 provides the latest and very critical enterprise distributed database capabilities in delivering on our vision for a comprehensive real-time data platform," said Irfan Khan, chief technology officer and senior vice president, Database & Technology, SAP AG. "Through integration with SAP HANA, SAP Sybase SQL Anywhere 16 allows users and applications in distributed environments to have real-time interactions with virtually any source, and then further explore and analyze that data through SAP HANA. As a result of joint innovation and expertise across our worldwide engineering team, SAP HANA and SAP Sybase SQL Anywhere offer rich applications and consumer interactions from the data center to mobile and satellite server locations."

Innovations and enhancements in SAP Sybase SQL Anywhere 16 include:

24x7 operations: Database mirroring robustness and elasticity improvements, on-the-fly server processor allocation and more non-blocking operations to improve concurrent application availability

Security: A new role-based security model for enhanced control and granularity of privileged user operations, support for lightweight directory access protocol (LDAP) user authentication and additional features for password change control

Performance: Expanded options for query optimization to better exploit multi-CPU servers and intra-query parallelism

Developer productivity: Support for open data protocol (OData), enhanced event tracing for production system troubleshooting and expanded SQL language support including ROW and ARRAY data types

Data synchronization: Support for bi-directional sync of SAP HANA to thousands of mobile users, new synchronization testing tools and intuitive sync setup and deployment wizards

The developer edition of SAP Sybase SQL Anywhere can be downloaded for evaluation here. To learn more, visit the SAP Sybase SQL Anywhere Developer Center on SAP Community Network.

For more information, visit the SAP Newsroom.

About SAP

As market leader in enterprise application software, SAP helps companies of all sizes and industries run better. From back office to boardroom, warehouse to storefront, desktop to mobile device – SAP empowers people and organizations to work together more efficiently and use business insight more effectively to stay ahead of the competition. SAP applications and services enable more than 197,000 customers (includes customers from the acquisition of SuccessFactors) to operate profitably, adapt continuously, and grow sustainably. For more information, visit www.sap.com.

Any statements contained in this document that are not historical facts are forward-looking statements as defined in the U.S. Private Securities Litigation Reform Act of 1995. Words such as "anticipate," "believe," "estimate," "expect," "forecast," "intend," "may," "plan," "project," "predict," "should" and "will" and similar expressions as they relate to SAP are intended to identify such forward-looking statements. SAP undertakes no obligation to publicly update or revise any forward-looking statements. All forward-looking statements are subject to various risks and uncertainties that could cause actual results to differ materially from expectations. The factors that could affect SAP's future financial results are discussed more fully in SAP's filings with the U.S. Securities and Exchange Commission ("SEC"), including SAP's most recent Annual Report on Form 20-F filed with the SEC. Readers are cautioned not to place undue reliance on these forward-looking statements, which speak only as of their dates.

©2013 SAP AG. All rights reserved.
SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Please see http://www.sap.com/corporate-en/legal/copyright/index.epx#trademark for additional trademark information and notices.

Note to Editors:
To preview and download broadcast-standard stock footage and press photos digitally, please visit www.sap.com/photos. On this platform, you can find high resolution material for your media channels. To view video stories on diverse topics, visit www.sap-tv.com. From this site, you can embed videos into your own Web pages, share video via email links, and subscribe to RSS feeds from SAP TV.

Follow SAP on Twitter at @sapnews and @sustainableSAP.

For customers interested in learning more about SAP products:
Global Customer Center: +49 180 534-34-24
United States Only: 1 (800) 872-1SAP (1-800-872-1727)

For more information, press only:
Jeff Neal, +1 (925) 336-6203, jeff.neal@sap.com, PDT
SAP Press Office, +49 (6227) 7-46315, CET; +1 (610) 661-3200, EDT; press@sap.com

Dilbert.com


Wednesday, April 17, 2013

Product Suggestion: Google-Searchable Docs

Some SQL Anywhere technical documents posted on the sap.com website aren't showing up in Google searches, and they should.

The likely reason is that these documents are located in separate PDF blobs deep down in sap.com documentation database, so deep the Google indexing process doesn't find them.

Example 1

Mobilizing an Oracle 11g Database
http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/80c79a94-1b24-3010-f7a0-f299dc3b47e1?QuickLink=index&overridelayout=true&57844619551223
This document dates back to May 2011, and it originally appeared on sybase.com. When you do the following Google search
"must have permission for the GV_$TRANSACTION"
the original sybase.com copy shows up
Mobilizing an Oracle 11g Database
http://www.sybase.com/files/Technical_Documents/ias_wp_MobilizeOracle11g.pdf
but not the one on sap.com.

As time goes on, it is very likely that all technical documentation on sybase.com will be copied to sap.com and deleted from sybase.com... but for now, Google can still find this one.

Example 2

Top 10 Cool New Features In SAP Sybase SQL Anywhere 16
http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/90b97407-9281-3010-92bf-edb97ae0bb72?QuickLink=index&overridelayout=true&58321360934470
This exists only on sap.com, and as such it is invisible to a Google search like this:
"skipping over one version number per year for three years"

Suggestion

Instead of burying these documents down inside a documentation database that is inaccessible to Google search, they should be stored in a location Google can find... either a simple web page, or a database that Google can index.

For example, the following document is stored as an ordinary "DOC-xxxxx" web page that Google can find:
SAP Sybase SQL Anywhere Supported Platforms and Engineering Support Status
http://scn.sap.com/docs/DOC-35654
In this case, a Google search works just fine:
"The tables below summarize the platforms supported by SAP Sybase SQL Anywhere"
In fact, the hidden documents ARE represented as simple "DOC-xxxxx" web pages but those pages are stubs; the actual content is buried one level further down (or eight levels further down if you count all the "/" slashes in the urls).

For example, here's the DOC-xxxxx page for the Top 10 article:
Top 10 Cool New Features in SAP Sybase SQL Anywhere 16
http://scn.sap.com/docs/DOC-40056
As you can see, the DOC-xxxxx page is just a stub, and the actual content is down inside the hidden page:

What's the point?

It may not be important to have this particular page searchable by Google, but that's not the point.

The point is, it IS important for MOST SQL Anywhere technical documents to be Google-searchable, and the trend favoring stealth over visibility should be reversed.


Monday, April 15, 2013

Latest SQL Anywhere EBFs

Current builds for the active platforms...

HP-UX     12.0.1.3798 EBF           29 Oct 2012
 Itanium  11.0.1.2958 EBF       *** 08 Apr 2013 ***

IBM AIX   12.0.1.3798 EBF           24 Oct 2012
          11.0.1.2958 EBF       *** 08 Apr 2013 ***

Linux     16.0.0.1324 GA            05 Mar 2013
          12.0.1.3873 EBF           05 Apr 2013
          11.0.1.2958 EBF       *** 08 Apr 2013 ***

Mac OS    12.0.1.3871 EBF           05 Apr 2013
          11.0.1.2449 EBF           29 Jun 2010

Solaris   12.0.1.3798 EBF           24 Oct 2012
 SPARC    11.0.1.2958 EBF       *** 08 Apr 2013 ***

Solaris   12.0.1.3798 EBF           29 Oct 2012
 x64      11.0.1.2958 EBF       *** 08 Apr 2013 ***

Windows   16.0.0.1324 GA            05 Mar 2013
          12.0.1.3873 EBF       *** 08 Apr 2013 ***
          12.0.1 French Docs,       25 Sep 2012
                 English Docs,      25 Sep 2012
                 German Docs,       25 Sep 2012
                 Chinese Docs,      28 Mar 2013
                 Japanese Docs      28 Mar 2013
          11.0.1.2913 EBF           21 Dec 2012

Other Stuff...

 Older EBFs

 Free support! Q&A forum
   ...or, call Tech Support

 SQL Anywhere...
   ...Sybase home page 
   ...SAP home page 
   ...SAP Developer Center 

 Buy SQL Anywhere 

 Developer Edition... 
   [16.0] [12.0.1] [11.0.1]

 Download the...
   Educational Edition 
   Web Edition 

 Supported Platforms...
   SQL Anywhere 
   Linux 
   OnDemand

 ODBC Drivers for MobiLink

The three asterisks "***" show which Express Bug Fixes (EBFs) and GA builds have appeared on the website since the previous version of this page.
  • Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1 and 16.0) are shown here.

  • Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released.


Friday, April 12, 2013

Logging Server Messages

SQL Anywhere engines display startup, progress and diagnostic text messages in the server window. Historically, the server window was called the "database console" so the messages are sometimes called the "console log".



The server messages may also include custom debugging and other output written by MESSAGE TO CONSOLE statements, and the command line option -o filespec.txt can be specified on the server command line to save all the messages to a text file:

"%SQLANY16%\bin64\dbsrv16.exe"^
  -o dbsrv16_log_ddd16.txt^
  ddd16.db 

MESSAGE 'Hello, world!' TO CONSOLE;

I. 04/08 09:07:11. SQL Anywhere Network Server Version 16.0.0.1324 I. 04/08 09:07:11. Developer edition, not licensed for deployment.
I. 04/08 09:07:11.  I. 04/08 09:07:11. Copyright © 2013 SAP AG or an SAP affiliate company.
...
I. 04/08 09:07:11. Automatic tuning of multiprogramming level is enabled
I. 04/08 09:07:11. Starting database "ddd16" (C:\temp\ddd16.db) at Mon Apr 08 2013 09:07
I. 04/08 09:07:11. Performance warning: Database file "C:\temp\ddd16.db" consists of 15 disk fragments
I. 04/08 09:07:11. Transaction log: ddd16.log
I. 04/08 09:07:11. Starting checkpoint of "ddd16" (ddd16.db) at Mon Apr 08 2013 09:07
I. 04/08 09:07:12. Finished checkpoint of "ddd16" (ddd16.db) at Mon Apr 08 2013 09:07
I. 04/08 09:07:12. Database "ddd16" (ddd16.db) started at Mon Apr 08 2013 09:07
I. 04/08 09:07:12. Database server started at Mon Apr 08 2013 09:07
I. 04/08 09:07:12. Trying to start SharedMemory link ...
I. 04/08 09:07:12.     SharedMemory link started successfully
I. 04/08 09:07:12. Trying to start TCPIP link ...
I. 04/08 09:07:12. Starting on port 2638
I. 04/08 09:07:17.     TCPIP link started successfully
I. 04/08 09:07:17. Now accepting requests
I. 04/08 09:08:30. Hello, world!
If you want to preserve a permanent record of these server messages, it is possible to gather them up at runtime and save them in the database. Unlike external files which can get lost and confused over time, data in the database is protected from inconsistencies by the ACID rules for database transactions, as well as being protected from loss by the regular database backup process (you do have a regular database backup process, right? ...riiight? :)

The sa_server_messages() system procedure makes the "gather them up at runtime" process possible, and some custom SQL takes care of the "save them in the database" part.

Here's a table designed to save all the msg_* columns returned by calls to sa_server_messages():
CREATE TABLE server_messages (
   primary_key    BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   msg_id         UNSIGNED BIGINT NOT NULL,
   msg_text       LONG VARCHAR NOT NULL,
   msg_time       TIMESTAMP NOT NULL,
   msg_severity   VARCHAR ( 255 ) NOT NULL,
   msg_category   VARCHAR ( 255 ) NOT NULL,
   msg_database   VARCHAR ( 255 ) NOT NULL,
   UNIQUE ( msg_id, msg_time ) );
The primary_key column has been added to guarantee ordering in the face of these facts:
  • msg_id starts over at zero every time the server is restarted,

  • it is conceivable that msg_time may repeat for rapid-fire messages, and

  • msg_time values may fall back by one hour (and thus overlap if not repeat) when the autumn Daylight Saving Time change occurs.
At this point a bigger question arises: "If sa_server_messages() has access to all the server messages, why bother to save them separately? Why not just call sa_server_messages() whenever you want to query the messages?"

The answer is, only recent server messages are available to sa_server_messages(), so they must be repeatedly retrieved and saved in a separate table if you want to keep them longer. It is possible to define what "recent" means by setting the MessageCategoryLimit option but no matter what value you pick it may be possible for a flood of message output to cause data loss before you get around to calling sa_server_messages().

There are other advantages to using a permanent table:
  • It's easier to replicate server messages to the consolidated database using SQL Remote or MobiLink if an ordinary table is involved, and

  • different rules can be used for purging old data; e.g., delete by msg_time, delete by msg_category, delete uninteresting messages, and so on.
But the big reason is this: If the server's restarted, sa_server_messages() starts over from scratch... all the old messages are gone, gone, gone.

The following code uses a SQL Anywhere EVENT triggered once a minute to capture fresh messages. The code ignores the problem of inter-call message loss by assuming you can either increase the MessageCategoryLimit option and/or change the EVERY 1 MINUTES clause to something more frequent.
CREATE EVENT save_server_messages 
SCHEDULE START TIME '00:00' EVERY 1 MINUTES
HANDLER BEGIN

DECLARE @most_recent_msg_time   TIMESTAMP DEFAULT '1900-01-01';

-- Determine the approximate starting point for fresh messages.

SELECT TOP 1 msg_time 
  INTO @most_recent_msg_time
  FROM server_messages
 ORDER BY primary_key DESC;

-- Copy fresh messages.

INSERT server_messages (
       msg_id,
       msg_text,
       msg_time,
       msg_severity,
       msg_category,
       msg_database )
SELECT msg_id, 
       COALESCE ( msg_text, '' ),
       msg_time,
       msg_severity,
       msg_category,
       COALESCE ( msg_database, '' )
  FROM sa_server_messages()
 WHERE msg_time >= DATEADD ( hour, - 1, @most_recent_msg_time )
   AND NOT EXISTS  
          ( SELECT *
              FROM server_messages
             WHERE server_messages.msg_id   = sa_server_messages.msg_id
               AND server_messages.msg_time = sa_server_messages.msg_time ) 
 ORDER BY msg_id;

COMMIT;

END;
  • The SELECT starting on line 9 determines an approximate starting point for copying fresh messages. It's an approximate value because Daylight Saving Time causes the clock-on-the-wall time to be set earlier by one hour each autumn, and messages recorded during that hour must be treated as fresh messages even though they may have msg_time values earlier than @most_recent_msg_time.

  • The INSERT SELECT starting on line 16 copies and saves all the fresh messages returned by sa_server_messages().

  • The COALESCE calls make life easier later on: no worries about NULL values when writing queries.

  • The WHERE msg_time >= DATEADD predicate eliminates messages older than 1 hour.

  • The NOT EXISTS predicate eliminates more recent messages that have already been copied. ON EXISTING SKIP is often a good alternative to this kind of NOT EXISTS, but not this time: ON EXISTING SKIP allows new DEFAULT AUTOINCREMENT values to be calculated and then discarded when the primary key value collides; it also requires a different primary key for the server_messages table... something other than a DEFAULT AUTOINCREMENT column (yes, this has been tested, and yes, ON EXISTING SKIP does work as long as you're OK with big gaps in the DEFAULT AUTOINCREMENT values).

  • The ORDER BY msg_id clause makes sure that new DEFAULT AUTOINCREMENT values of the server_message.primary_key column are calculated in the same order as the messages were created. By definition, all rows returned by a single call to sa_server_messages() have monotonically increasing values of msg_id even though msg_id starts over again when the server is restarted. Put another way, sa_server_messages.msg_id works as a primary key for all the messages produced by a single execution of the SQL Anywhere server, which is all that sa_server_messages() returns.
Flaw: It is conceivable that the SQL Anywhere server could be shut down and restarted around the time the clock is set backward for Daylight Saving Time, causing the same values of msg_id and msg_time to be used for two different messages, and that the WHERE clause could cause one of those messages to be incorrectly discarded. The solution is left as an exercise for the person who cares reader :)

Here's what the table looks like...




Wednesday, April 10, 2013

The Newsgroups Are Gone

The old NNTP newsgroups for SQL Anywhere and other Sybase products have been shut down... they are gone, kaput, no more, expired, extinct.



The old content has been moved to a read-only website; if you go there, then drill down to SQL Anywhere, then drill down again (for example) to General Discussion, you get here:



Yup... all thirty-three thousand questions and one hundred thousand responses... page one, page two, page three hundred and thirty eight.

But hey! You can "Sort by: Response Posting Date", whee! That moves... some stuff... around a little bit.

No "Search" field though. And if you want to look for something in more than one SQL Anywhere forum, you have to scroll through... each... list... separately.

Google Search does work, but as far as Google is concerned, http://nntp-archive.sybase.com is one single repository. That's different from the old Google Groups where single newsgroups and groups of like-named newsgroups could be searched apart from all the others.

For example, if you do a Google Search for "insert" on the new archive website

"insert" site:http://nntp-archive.sybase.com
you get 9950 hits, whereas if you search the SQL Anywhere newsgroups via Google Search on Google Groups,
"insert" group:sybase.public.sqlanywhere.*
you get a more specific list of 6430 hits. The difference is that ASE and other newsgroups are included in the first result set but not the second.

The problem gets worse if you search on terms that aren't database-specific in themselves. For example, if you want to see all the discussions involving Java or PowerBuilder applications that are using SQL Anywhere databases, here's what Google Search returns:
Google Search                                          Hits
--------------------------------------------------     ----

"java" site:http://nntp-archive.sybase.com            36100
"java" group:sybase.public.sqlanywhere.*               3650

"powerbuilder" site:http://nntp-archive.sybase.com   107000
"powerbuilder" group:sybase.public.sqlanywhere.*       3360
Sadly, even Google Groups is not a reliable source for newsgroup searches, but it is certainly better than the new website... and now that the underlying Sybase NNTP server is gone the data may also disappear from Google Groups.

Once upon a time, promises were made to import old NNTP discussions into the new SQL Anywhere Forum. Those promises have not yet been fulfilled, but there's still hope.



Correction: It IS possible to narrow the focus of Google searches on the new website.

Rather than trying to specify the newsgroup name in the "site:" parameter, simply add part or all of the newsgroup name as an ordinary search term.

For example, add sybase.public.sqlanywhere.general, or sybase.public.sqlanywhere for all the SQL Anywhere newsgroups.
Google Search                                                                           Hits
--------------------------------------------------                                      ----

"java" site:http://nntp-archive.sybase.com                                              36200
"java" sybase.public.sqlanywhere site:http://nntp-archive.sybase.com                    17900
"java" sybase.public.sqlanywhere.general site:http://nntp-archive.sybase.com            10900

"powerbuilder" site:http://nntp-archive.sybase.com                                     103000
"powerbuilder" sybase.public.sqlanywhere site:http://nntp-archive.sybase.com            23700
"powerbuilder" sybase.public.sqlanywhere.general site:http://nntp-archive.sybase.com    12100
Clearly, this shows that the new website is better than Google Groups because it contains more data as well as allowing focussed searches... thanks to Jason Hinsperger for pointing this out.


Monday, April 8, 2013

Latest SQL Anywhere EBFs and Docs

Current builds for the active platforms...

HP-UX     12.0.1.3798 EBF           29 Oct 2012
 Itanium  11.0.1.2879 EBF           31 Oct 2012

IBM AIX   12.0.1.3798 EBF           24 Oct 2012
          11.0.1.2879 EBF           29 Oct 2012

Linux     16.0.0.1324 GA            05 Mar 2013
          12.0.1.3873 EBF       *** 05 Apr 2013 ***
          11.0.1.2913 EBF           19 Dec 2012

Mac OS    12.0.1.3871 EBF       *** 05 Apr 2013 ***
          11.0.1.2449 EBF           29 Jun 2010

Solaris   12.0.1.3798 EBF           24 Oct 2012
 SPARC    11.0.1.2913 EBF (SA)      19 Dec 2012

Solaris   12.0.1.3798 EBF           29 Oct 2012
 x64      11.0.1.2879 EBF           29 Oct 2012

Windows   16.0.0.1324 GA            05 Mar 2013
          12.0.1.3867 EBF           22 Mar 2013
          12.0.1 French Docs,       25 Sep 2012
                 English Docs,      25 Sep 2012
                 German Docs,       25 Sep 2012
                 Chinese Docs,  *** 28 Mar 2013 ***
                 Japanese Docs  *** 28 Mar 2013 ***
          11.0.1.2913 EBF           21 Dec 2012

Other Stuff...

 Older EBFs

 Free support! Q&A forum
   ...or, call Tech Support

 SQL Anywhere...
   ...Sybase home page 
   ...SAP home page 
   ...SAP Developer Center 

 Buy SQL Anywhere 

 Developer Edition... 
   [16.0] [12.0.1] [11.0.1]

 Download the...
   Educational Edition 
   Web Edition 

 Supported Platforms...
   SQL Anywhere 
   Linux 
   OnDemand

 ODBC Drivers for MobiLink

The three asterisks "***" show which Express Bug Fixes (EBFs) and GA builds have appeared on the website since the previous version of this page.
  • Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1 and 16.0) are shown here.

  • Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released.


Tuesday, April 2, 2013

It's just an expression

Rather than use a formal notation to present the SQL syntax used in SQL Anywhere, the Help has always used an informal, flexible "user-friendly" notation.

Sadly, with informality comes imprecision, which leads to confusion, in one case involving the syntax for "expression":

Expressions

An expression is a statement that can be evaluated to return values.
expression:
   case-expression
  | constant
  | [correlation-name.]column-name
  | - expression
  | expression operator expression
  | ( expression )
  | function-name ( expression, ... )
  | if-expression
  | special value
  | ( subquery )
  | variable-name
  | sequence-expression
The problem is, SQL Anywhere implements more than one form of expression depending on the context, and the Help only describes one form. In particular, the definition of "expression" includes "( subquery )", and the syntax for the CALL statement allows for expressions as parameters, leading one to believe that subqueries can be passed as CALL parameters:
CALL statement

Invokes a procedure.
[variable = ] CALL procedure-name ( [ expression, ... ] )
Unfortunately, in SQL Anywhere 12 and earlier, such was not the case:
CALL sa_db_info ( ( SELECT 0 FROM DUMMY ) );

Could not execute statement.
Syntax error near ')' on line 1
SQLCODE=-131, ODBC 3 State="42000"
Line 1, column 1
The good news, SQL Anywhere 16 has caught up with the Help in this respect; subqueries are now allowed in CALL statements:
CALL sa_db_info ( ( SELECT 0 FROM DUMMY ) );

Number,Alias,File,ConnCount,PageSize,LogName
0,'ddd16','C:\\projects\\$SA_templates\\ddd16.db',2,4096,'C:\\projects\\$SA_templates\\ddd16.log'