Monday, June 30, 2008

Automating DROP TABLE

If you're a fan of script files for CREATE TABLE and other SQL statements, like I am, you've probably written your fair share of DROP TABLE statements. Here's the format I like; it just drops the table, and if the table doesn't exist it ignores the exception and carries on with the CREATE TABLE:

BEGIN
DROP TABLE t;
EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t ( ...
One problem with that code is when some other connection is holding a schema lock on that table: the DROP TABLE fails because the table is in use, you don't see the error because of the empty EXCEPTION WHEN OTHERS THEN clause, and then the CREATE TABLE fails because the table exists.

This can happen quite frequently if you are developing web services that use HTTP sessions as described here. The problem is when you start an HTTP session, a long-lasting database connection is started, and that connection may have a schema lock on the table you're trying to drop.

These HTTP session connections may have no visible client process. Even if you shut down the browser, these connections persist; here is an example where service "s1" has started connection number 3:



The solution is to drop these HTTP session connections before executing the DROP TABLE. Here's the CREATE GLOBAL TEMPORARY TABLE from HTTP Sessions in SQL Anywhere with the additional code to do the DROP CONNECTION and DROP TABLE statements. The FOR loop gets the "@conn" connection number for each connection where the connection name is the same as the web service that started the connection, and the EXECUTE IMMEDIATE drops that connection.
FOR f_drop_conn AS c_drop_conn NO SCROLL CURSOR FOR
SELECT conn.Number AS @conn
FROM sa_conn_info() AS conn
WHERE conn.Name IN ( 's1', 's2' )
ORDER BY conn.Number
FOR READ ONLY
DO
EXECUTE IMMEDIATE
STRING ( 'DROP CONNECTION ', @conn );
END FOR;

BEGIN
DROP TABLE state;
EXCEPTION WHEN OTHERS THEN
END;

CREATE GLOBAL TEMPORARY TABLE state (
id INTEGER NOT NULL DEFAULT 1
PRIMARY KEY CHECK ( id = 1 ),
value VARCHAR ( 100 ) NOT NULL )
ON COMMIT PRESERVE ROWS;
Astute readers might ask "Why not call sa_locks to find any connections holding schema locks on the table you're trying to drop? That you wouldn't have to code all those web service names in the FOR loop."

The answer seems to be this: sa_locks doesn't show schema locks for a GLOBAL TEMPORARY TABLE in SQL Anywhere 11, at least not yet, so calling sa_conn_info seems to be the kludge of choice... if anyone has a better way, please let me know.

Saturday, June 28, 2008

Win Five Thousand Bucks at Techwave!

$5,000 is pretty good pay for three minutes work. Here's how to get it:

Step 1: Listen to this lovely lady...



Step 2: Enter the Sybase "How We Did It" Video Challenge here.

Step 3 (optional): Come to the Techwave conference.

Step 4: Collect your money!

You don't have to actually come to Techwave to get your money... but if you don't come, you won't be able to vote for yourself :)

Seriously, I know at least a dozen people who could win this, but my money is on these guys...



Or maybe these...

Friday, June 27, 2008

Keynote Bingo

There are two, two, two ways to play Buzzword Bingo at Techwave...



Method 1: Download Bruce Armstrong's PocketBuilder application here.





Method 2: Copy and paste this list...

24x7x365, actionable, alliance, Asia-focused, building blocks, business intelligence, business model, business transformation, collaborative innovation, consumer influenced, core business, core process, differentiating, driving growth, dynamic business, enterprise, extreme IT, forge, free flow, frontline, geared, global audience, global enterprise, go-to-market, information edge, information reach, information volume, infrastructure, kicking some butt, knowledge-based, landscape, level playing field, leverage investment, leveraging knowledge, market openness, market transparency, mission critical, mobilization, mobilizing, on the cusp, overheating, partnering, partnership, point of action, product portfolio, proof point, re-engineering, remote worker, robust relationship, single point solution, strategic, survived the curve, sweet spot, take the pulse, transform the enterprise, unwire, unwired, vision, visionary, wired edge, world of opportunity

...into this website.

Thursday, June 26, 2008

Techwave Picks

Here's a chronological list of all the SQL Anywhere sessions at this year's Techwave conference, with my personal picks marked.

For more information about the conference content, go here.

Tuesday 1:00 pm – 2:45 pm
Tuesday 3:00 pm – 5:30 pm
Wednesday 9:45 am – 12:00 pm
Wednesday 1:30 pm – 3:45 pm

These 9+ hour lectures over the first two days have been developed especially for TechWave by Sybase iAnywhere subject matter experts. The sessions include lab materials for the product releases being instructed when available. This intensive training allows you to return to your office with better skills than when you left. An incredibly high value proposition not available anywhere else. SQL Anywhere customers will have the option to attend education sessions in two categories, Data Management or Database Synchronization. Data Management sessions focus on data management and data exchange technologies that enable the rapid development and deployment of database-powered applications. The Data Synchronization session focus on extending information in corporate applications and enterprise systems to databases running in mission-critical frontline environments. Each of these tracks will include a first-hand look at the new features and capabilities of SQL Anywhere 11.

(My Pick) Improving Performance with SQL Anywhere

In this session we will discuss a range of performance topics for getting the most out of SQL Anywhere. We will illustrate new performance-related features of the Panorama release of SQL Anywhere, including a comprehensive description of materialized view support: definitions and motivation, benefits and penalties, design and creation issues, and maintenance techniques. We will describe in detail new functionality provided by the MERGE statement. In addition, we will describe useful diagnostic information provided by the server for pinpointing performance problems. Finally, we will describe a methodology for performance evaluation and capacity planning of SQL Anywhere database applications. We will discuss the merits of conducting systematic performance analyses, and will describe some common pitfalls of conducting performance evaluation tests which can lead to erroneous conclusions.

MobiLink Synchronization Fundamentals

This course provides an in-depth understanding of MobiLink synchronization technologies. Topics covered include distributed design concepts and techniques, maintaining uniqueness across distributed environments, handling data conflicts during synchronization, implementing security, and studying the behavior of the synchronization server and clients.



Wednesday 4:00 pm – 5:30 pm

(My Pick) Full Text Search in SQL Anywhere
Audience: IS/IT Management (I hope that's a mistake)
Exploit the new full text search capabilities in the SQL Anywhere Panorama release! This session will describe how full text indexes are built, maintenance, and how results can be combined with ordinary SQL search conditions in the same SQL query to provide a high-performance, integrated search solution for both structured and semi-structured data.

UltraLite and MobiLink - Performance Measurement & Optimization for your Mobile Solution
Audience: DBA, System Analyst, IS/IT Management, Corporate Management
This presentation will disclose lessons learned and best practices utilized by Accenture to develop large scale mobile
solutions with Sybase iAnywhere SQL Anywhere.



Thursday 8:30 am – 10:00 am

(My Pick) SQL Anywhere in a Web 2.0 World
Audience: Application Developer, DBA, System Analyst, Object Administrator, Consultant
The internet is a fast, dynamic space. The past year has seen the development of a number of new RIA (Rich Internet Application) technologies that promise a new generation of web applications. This session will examine these new technologies and explain how SQL Anywhere fits into the new web world. Examples will inlcude SQL Anywhere integration with Microsoft Silverlight, Adobe AIR , JavaFX, and Ajax using a service-based database access model.

Welcome to SQL Anywhere 11 (also Friday 1:00 pm, different description)
Audience: Application Developer
This presentation will focus on the new features delivered in the latest release of SQL Anywhere. Topics include the enhancements around performance, frontline analytics, data synchronization, and developer freedom. Also a discussion on the behavior changes and upgrade issues introduced in SQL Anywhere 11 and assist those users needing to migrate their existing databases and applications.

New MobiLink Features in SQL Anywhere 11 (also Friday 1:00 pm, same description)
Audience: Application Developer
This presentation will discuss new MobiLink synchronization features that are available in SQL Anywhere 11. Features discussed will include the Relay Server, SQL Passthrough, MobiLink Client API, end-to-end encryption, and synchronizing to a MySQL consolidated database. Also included will be a discussion on enhancements to the MobiLink server, such as the new non-blocking download acknowledgement feature, for improved efficiency and performance.



Thursday 10:30 am – 12:00 pm

Developing Database Applications for Windows Mobile using SQL Anywhere
Audience: Application Developer, DBA, System Analyst, IS/IT Management, Corporate Management, Other
Using code examples, the presentation offers an in-depth look at creating mobile database applications using leading development environments such as PocketBuilder and Visual Studio. Other topics presented include implementation best practices and handling security in mobile database applications.

Utilizing UltraLiteJ to Quickly Extend Existing SQL Anywhere Applications to BlackBerry Devices
Audience: Application Developer, System Analyst, Consultant
This session will provide methodology by which developers can extend their existing SQL Anywhere-based applications to BlackBerry devices quickly, easily, and cost-effectively by utilizing UltraLiteJ and MobiLink.

(My Pick) Offline Wikipedia with SQL Anywhere
Audience: Application Developer, Object Administrator, Consultant
Wikipedia, a popular open-content encyclopedia, is the largest and fastest growing general-reference resources on the internet. The entire wikipedia content is freely available for download, weighing in at approximately 15 gigabytes of text. While it is possible to create a complete, offline version of Wikipedia on a laptop, the lack of space and computing resources on a mobile device make it impractical on such platforms. This talk will examine a solution that allows users to keep a user-defined set of articles for offline use on a mobile device. This solution, based on SQL Anywhere, allows synchronization through services to a MySQL-based web application (MediaWiki). This talk will also explore the more general question of using a staging database to add synchronization to a web application that lacks it as a native feature.



Thursday 2:15 pm – 3:45 pm

Developing Applications for SQL Anywhere using Visual Studio
Audience: Application Developer, DBA, System Analyst, IS/IT Management, Corporate Management, Other
This session provides an overview of how to use Visual Studio to develop SQL Anywhere applications both for the desktop and for the web. The session will include an overview of ADO.NET, the data access API in the .NET Framework, and demonstrate SQL Anywhere’s support for the most recent features of Visual Studio 2008 including LINQ (Language Integrated Query) and the Entity Framework. A number of examples and demonstrations will be given including a demonstration of SQL Anywhere’s integration within the Visual Studio environment. As well, we will look at other new features of SQL Anywhere 11 that apply directly to the .NET platform.

Migrating to SQL Anywhere
Audience: Application Developer, System Analyst, Consultant, IS/IT Management
This session will provide some tips, tricks and techniques for migrating data and applications from other RDBMS vendors into a SQL Anywhere database so you can take advantage of SQL Anywhere’s capabilities in your next generation applications.

(My Pick) Leveraging Your Web Infrastructure to Create High Availability MobiLink Data Synchronization
Audience: Application Developer
Data synchronization has become an important part of many corporate information systems. Synchronizing high availability databases and setting up synchronization systems without a single point of failure are two challenges that organizations face as they bring synchronization systems to the 24/7 requirements of modern data systems. This presentation will outline architectural design considerations for leveraging existing web infrastructure to create a high availability data synchronization environment. Discussions will include synchronizing high availability consolidated and remote databases, removing single points of failure from the synchronization system, and the challenges of running 24/7 data synchronization environments. This will include discussions on the Relay server, MobiLink server farms, load balancing, and hot failover.



Friday 8:30 am – 10:00 am

(My Pick) Web Development with SQL Anywhere and PHP
Audience: DBA, System Analyst, Other
PHP is the most popular language for creating applications for the Web. SQL Anywhere has many features that make it ideal for Web applications. This talk will explore development and architecture of PHP Web applications using SQL Anywhere and deployed on IIS, Apache, or the SQL Anywhere HTTP server. We will talk about creating Web pages with dynamic content and using Web services and JavaScript with Ajax to create rich internet applications (RIAs). We will also discuss how SQL Anywhere technology can enable exciting new applications such as maintaining local copies of your Web applications or taking your Web applications off-line.

Synchronizing SQL Anywhere Databases in a .NET Environment
Audience: Application Developer, DBA, System Analyst, Object Administrator, Consultant, IS/IT Management
NET development technologies provide the ability to rapidly develop, mange, and deploy secure applications in a Windows environment. This provides easy integration of new solutions into existing infrastructure to meet growing business needs. SQL Anywhere technologies provide an array of tools and APIs to easily integrate MobiLink data synchronization in to your .NET environment. This presentation will provide development considerations when synchronizing a SQL Anywhere database in a .NET environment. Topics will include the new MobiLink client (dbmlsync) synchronization API, the MobiLink server API, object-based data flow using the Direct Row Handling API, writing .NET synchronization scripts, and server initiated synchronization.



Friday 10:30 am – 12:00 pm

Seamless Synchronization and other UltraLite Features
Audience: DBA, System Analyst, Consultant, IS/IT Management, Corporate Management
Wireless networks change many things for mobile applications, including the demands on data synchronization. No longer a batch process, data sync must now take place seamlessly behind the scenes without interrupting the user. This talk shows how to implement seamless synchronization with UltraLite databases. It also provides an overview of other new UltraLite features in SQL Anywhere 11.

Mobilizing ASE, Oracle and SQL Server Databases
Audience: Application Developer, System Analyst, Object Administrator, Consultant, IS/IT Management, Other
This presentation demonstrates how to mobilize data stored in existing enterprise backends using SQL Anywhere and MobiLink technology. Using the visual tools available in SQL Anywhere, the presentation shows the simplicity of defining and deploying data synchronization models, no matter where the enterprise data resides. The presentation illustrates data synchronization to Sybase ASE, Oracle, and Microsoft SQL Server by mobilizing the existing sample databases included with those products.



Friday 1:00 pm – 2:30 pm

Welcome to SQL Anywhere 11 (also Thursday 8:30 am, different description)
Audience: Application Developer
With any software release offering an abundance of new features, it’s often easy for a few to be overlooked. This presentation will focus specifically on the new features delivered in the latest release of SQL Anywhere - Version 11. Topics include a discussion of the enhancements around performance, frontline analytics, data synchronization, and developer freedom. The talk will include a discussion on the behavior changes and upgrade issues introduced in SQL Anywhere 11 and assist those users needing to migrate their existing databases and applications. A great introduction to SQL Anywhere 11 without having to read the documentation!


(My Pick) New MobiLink Features in SQL Anywhere 11 (also Thursday 8:30 am, same description)
Audience: Application Developer
This presentation will discuss new MobiLink synchronization features that are available in SQL Anywhere 11. Features discussed will include the Relay Server, SQL Passthrough, MobiLink Client API, end-to-end encryption, and synchronizing to a MySQL consolidated database. Also included will be a discussion on enhancements to the MobiLink server, such as the new non-blocking download acknowledgement feature, for improved efficiency and performance.

Wednesday, June 25, 2008

The Wars

Monday, June 23, 2008

IvanAnywhere - Episodes 3 and 4

I've been watching IvanAnywhere's home page when I should have been watching his Wikipedia page... I guess that's how I missed both of these:

Episode 3 - IvanAnywhere Meets Ivan

Episode 4 - IvanAnywhere's Performance Review
They just keep getting better and better!

PBL Peeper

In spite of its name, PBL Peeper (pronounced pibble peeper) is a serious developer's tool, a "best of breed niche product" if there ever was one.

So, if you don't work with PowerBuilder, read no further.

If you do work with PowerBuilder, download the new (free) version of PBL Peeper here.

I haven't tried the new version yet, but I do have 100% confidence in it... not just because I know Terry Voth but because I used two earlier versions on an assigment as "Special Master" to the United States District Court in Miami, in a civil case involving copyright infringment and a personal non-compete contract.

PBL Peeper has many uses. What I used it for was to export and flatten the code for tens of thousands of PowerBuilder objects, from hundreds of versions of two large competing applications, so I could use Compare-It! to view the differences.

The bottom line in the case was this: No copyright infringement, but the non-compete clause was violated from day one, so both parties came away happy... or unhappy, depending on your viewpoint.

And yes, Special Master is a real job title. Kinda like a judge, no light sabres.

Sunday, June 22, 2008

Click Flash Boom

It might be my imagination, but I heard a "click" in my office at the same time there was a flash outside, in the distance.

Then a split-second later came a soft distant boom.

I think the click was my UPS getting past a tiny glitch on the line, the flash was a transformer exploding somewhere nearby (hence the simultaneous glitch on the line), and the boom was the delayed sound from said transformer explosion.

The glitch was so tiny the UPS did not beep, the lights did not flicker, and the APC Powerchute software did not show it ("Note: Power problems of a very short duration are not recorded here.")

Yeah, it might be my imagination, but I like my story better than "Someone's playing Enemy At The Gates!"

Saturday, June 21, 2008

Agile Architechure?

I wonder what "Architechure" is?

This much is clear: "Best Practices" doesn't include "Check Spelling"...

Thursday, June 19, 2008

HTTP Sessions in SQL Anywhere

When you open a web page in a browser, even a simple one, more than one connection to the HTTP server is usually required. Sometimes there will be many dozens of connections: one for the page as a whole, one each image (even the teeny tiny ones), and if the web page is a FRAMESET there will be one connection for each FRAME.

Browsers and traditional HTTP servers do a really good job of handling these multiple connections in an efficient manner. But what about SQL Anywhere's built-in HTTP server? If you use web services inside the database to serve up all your web pages, plus all the images and other bits and pieces, will it be just as efficient?

The answer is yes, and if you use SQL Anywhere 10's HTTP session support, performance and functionality gets even better.

By default, SQL Anywhere starts a new database connection for each web service, and closes that connection when the web service ends. That has two implications: Lots and lots of very short database connections (thousands, millions), and no "memory" or persistent data carried from one web page to the next.

Once upon a time, online transaction systems were classified as conversational versus non-conversational. Today, non-conversational is called "stateless" meaning the server does not store anything (no "state") between one user interaction and the next. Non-conversational or stateless servers are fast and efficient, no storage is required for those thousands of users out there, no code is required to maintain that data. Yesterday CICS, today Apache, dumb as posts unless you work really hard.
The trouble with stateless servers is they're useless... if something even as simple as a password and user id is required, the user would have to provide it with every single interaction. You have to be able to pass something from one interaction to the next... you have to give your system some memory, you have to make your application conversational or "stateful".

With the SQL Anywhere HTTP server, with or without session support, you can pass data in three ways:
  1. as parameters in the URL

  2. in a cookie

  3. in the database
For example, the Foxhound database monitor uses a combination of 1 and 3: NEWID() is called to create a GUID that is passed in the URL from one web page to the next, and that GUID is used as the primary key to store and retrieve data that's unique to each continuing user conversation.

Here's a typical URL used by Foxhound, where "z1" is the name of the HTTP parameter carrying the GUID to the next web service:
http://localhost/foxhound?t=rroad_monitor_database1
&i1=N&z1=3ea32c17-1704-48d2-afa7-c0a6cf1d32e9
Here is a snippet from one of several Foxhound tables that has that GUID as primary key:
CREATE TABLE rroad_session_options (
session_id VARCHAR ( 36 ) NOT NULL,
integer_session_id INTEGER NOT NULL
DEFAULT AUTOINCREMENT UNIQUE,
sampling_id UNSIGNED INTEGER NULL,
list_refresh_count BIGINT NOT NULL DEFAULT 0,
display_refresh_count BIGINT NOT NULL DEFAULT 0,
...
PRIMARY KEY ( session_id ) );
Foxhound was originally developed before SQL Anywhere offered session support, and it still doesn't use that feature.

The question is, why bother?

There are several reasons why Foxhound and any other conversational SQL Anywhere web service application should use HTTP session support:
  1. Each successive web service will use the same database connection as the previous one in the same conversation; it won't have to start and stop a new connection. That's more than just an efficiency consideration, read on...

  2. Having one continuing connection means you can pass conversational "memory" from one web service to the next in connection-level (GLOBAL TEMPORARY) tables.

  3. Although you still need to create and store a "session id" and put it in a cookie or the URL, you don't have to use it anywhere else in your code. Your web services don't need to receive it as a parameter, and your SELECT statements don't need to specify it in the WHERE clauses.

  4. Because there are now fewer database connections used by web services, and they last longer, and it is possible to watch them and monitor them... say, in Foxhound itself.

  5. Other stuff, like session timeout, comes with it... more code you don't have to write.
Here is the code for two simple web services, s1 and s2, plus a GLOBAL TEMPORARY state table. Each of the web services calls a procedure, p1 and p2 respectively. The code is almost identical for each service/procedure pair, differing only where the digits "1" and "2" are used so you can tell what you're looking at in the browser.
--------------------------------------
-- One row per connection, id = 1 always.

CREATE GLOBAL TEMPORARY TABLE state (
id INTEGER NOT NULL DEFAULT 1
PRIMARY KEY CHECK ( id = 1 ),
value VARCHAR ( 100 ) NOT NULL )
ON COMMIT PRESERVE ROWS;

--------------------------------------
CREATE SERVICE s1
TYPE 'RAW' AUTHORIZATION OFF USER DBA
AS CALL p1();

--------------------------------------
CREATE PROCEDURE p1()
RESULT (
html_string LONG VARCHAR )
BEGIN

DECLARE @session_id VARCHAR ( 36 );
DECLARE @input_state VARCHAR ( 100 );

-- Use the SessionID, or create a new one.

SET @session_id = TRIM ( COALESCE (
CONNECTION_PROPERTY ( 'SessionID' ), '' ) );

IF @session_id = '' THEN
SET @session_id = NEWID();
CALL sa_set_http_option (
'SessionID', @session_id );
END IF;

-- Get the input state, if it exists.

SELECT state.value
INTO @input_state
FROM state;

-- Create or update the state.

INSERT state ( value )
ON EXISTING UPDATE
VALUES ( 'state set in s1 - p1' );

COMMIT; -- don't forget ON COMMIT PRESERVE ROWS

-- Send the HTML back to the browser.

CALL sa_set_http_header (
'Content-Type', 'text/html' );

SELECT STRING (
'<HTML><BODY><TITLE>s1 - p1</TITLE>',
'<PRE>',
'Service s1 - Procedure p1\x0d\x0a\x0d\x0a',
'Connection: ',
CONNECTION_PROPERTY ( 'Number' ),
'\x0d\x0a',
'@session_id: ',
COALESCE ( @session_id, '[NULL]' ),
'\x0d\x0a',
'@input_state: ',
COALESCE ( @input_state, '[NULL]' ),
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s1',
'?sessionid=', @session_id,
'">Go to service s1 - Procedure p1</A>',
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s2',
'?sessionid=', @session_id,
'">Go to service s2 - Procedure p2</A>',
'</PRE>',
'</BODY></HTML>' );

END; -- p1

--------------------------------------
CREATE SERVICE s2
TYPE 'RAW' AUTHORIZATION OFF USER DBA
AS CALL p2();

--------------------------------------
CREATE PROCEDURE p2()
RESULT (
html_string LONG VARCHAR )
BEGIN

DECLARE @session_id VARCHAR ( 36 );
DECLARE @input_state VARCHAR ( 100 );

-- Use the SessionID, or create a new one.

SET @session_id = TRIM ( COALESCE (
CONNECTION_PROPERTY ( 'SessionID' ), '' ) );

IF @session_id = '' THEN
SET @session_id = NEWID();
CALL sa_set_http_option (
'SessionID', @session_id );
END IF;

-- Get the input state, if it exists.

SELECT state.value
INTO @input_state
FROM state;

-- Create or update the state.

INSERT state ( value )
ON EXISTING UPDATE
VALUES ( 'state set in s2 - p2' );

COMMIT; -- don't forget ON COMMIT PRESERVE ROWS

-- Send the HTML back to the browser.

CALL sa_set_http_header (
'Content-Type', 'text/html' );

SELECT STRING (
'<HTML><BODY><TITLE>s2 - p2</TITLE>',
'<PRE>',
'Service s2 - Procedure p2\x0d\x0a\x0d\x0a',
'Connection: ',
CONNECTION_PROPERTY ( 'Number' ),
'\x0d\x0a',
'@session_id: ',
COALESCE ( @session_id, '[NULL]' ),
'\x0d\x0a',
'@input_state: ',
COALESCE ( @input_state, '[NULL]' ),
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s1',
'?sessionid=', @session_id,
'">Go to service s1 - Procedure p1</A>',
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s2',
'?sessionid=', @session_id,
'">Go to service s2 - Procedure p2</A>',
'</PRE>',
'</BODY></HTML>' );

END; -- p2
Figure 1 shows what service s1 displayed when it was launched with http://localhost/s1 in the browser:
  • The service s1 has started database connection number 3.

  • @session_id shows that procedure p1 has calculated a new GUID to use as session id.

  • @input_state was NULL because the "state" table didn't contain any row for connection 3 when service s1 started.
Figure 1: Service s1 at startup

There are exactly two critical pieces of code in each procedure p1 and p2. The first critical piece calls CONNECTION_PROPERTY to see if an HTTP SessionID already exists and has been passed to this service. If it doesn't exist (if @session_id is empty) then the procedure calls NEWID() to generate a new GUID, and sa_set_http_option is called to start a new session.
-- Use the SessionID, or create a new one.

SET @session_id = TRIM ( COALESCE (
CONNECTION_PROPERTY ( 'SessionID' ), '' ) );

IF @session_id = '' THEN
SET @session_id = NEWID();
CALL sa_set_http_option (
'SessionID', @session_id );
END IF;
The second critical piece of code passes ?sessionid= to the next service, via the URLs...
http://localhost/s1?sessionid=d27b7503-9051...
http://localhost/s2?sessionid=d27b7503-9051...
...in the links that are part of the HTML constructed by the big SELECT in procedure p1:
'<A HREF="http://localhost/s1',
'?sessionid=', @session_id,
...
'<A HREF="http://localhost/s2',
'?sessionid=', @session_id,
Here are four Notes, plus a Tip...

Note: The parameter value can be anything you want (a GUID is used here), but parameter name in the URL must be "sessionid". If you use a different parameter name then the CONNECTION_PROPERTY ( 'SessionID' ) call in the next service won't return the value.

Note: You can use a cookie instead of a parameter in the URL... if you want... it's somewhere in the docs.

Note: It's not enough to just call sa_set_http_option, or to just code ?sessionid= in the URL, you have to do both. At first glance that's a head-scratcher, doing both seems redundant, but it's not: The call to sa_set_http_option tells SQL Anywhere to start a new session, and the ?sessionid= in the URL tells SQL Anywhere *which* of many possible sessions is supposed to be used by the next service.

Note: The ?sessionid= parameter in the URL is for use by SQL Anywhere only. It is possible to name it in your CREATE SERVICE statement and pass it to your procedure...
CREATE SERVICE s1 
TYPE 'RAW' AUTHORIZATION OFF USER DBA
AS CALL p1 ( :sessionid );

CREATE PROCEDURE p1 (
IN @input_sessionid LONG VARCHAR )
...but you probably shouldn't do that. Do not rely on the value passed from the URL because the underlying session might have expired. Call CONNECTION_PROPERTY ( 'SessionID' ) instead; it will return an empty value if the session has expired, and your code will call NEWID() and sa_set_http_option to start a new session.

Tip: If you make a mistake and accidentally create a new session every time you run your web service, perhaps because of a coding error in the URL, you'll end up with a whole mess of long-lasting "orphan" connections to the database (see Figure 2). If you want to find out about this mistake earlier rather than later, run dbeng10.exe instead of dbsrv10.exe so you will get "503 Service Temporarily Unavailable" as soon as the personal server's limit of 10 connections is exhausted.

Figure 2: Orphan Connections

Figure 3 shows what service s2 displayed when "Go to service s2" was clicked in Figure 1:
  • A new service has started but database connection number 3 is still the one being used.

  • @session_id shows the GUID calculated by the previous service is still being used.

  • The @input_state value 'state set in s1 - p1' shows that the GLOBAL TEMPORARY TABLE has successfully carried data from the previous service forward to this one.
Figure 3: After "Go to service s2" was clicked on s1

Figure 4 shows what is displayed after the round trip back to service s1: the session id and the connection number are the same as before, and @input_state shows that data is still being passed from service to service.

Figure 4: After "Go to service s1" was clicked on s2

Figure 5 shows what happens when a fake session id is manually entered:
  • A new connection to the database has been started: number 4.

  • A new GUID has been calculated, meaning that a new HTTP session has been started... and the fake session id has been ignored.

  • The @input_state is NULL because the GLOBAL TEMPORARY TABLE does not carry data across connection changes.
Figure 5: Unknown session id is ignored

Figure 6 shows what happens when you click on one of the links in Figure 5: The new database connection is being used, so is the new session id, and the state table is again carrying data from one service to the next. In other words, the fake session id has had no effect whatsoever, it is as if the ?sessionid= had been left off the URL in Figure 5... except for the session id value, Figures 5 and 6 look exactly like Figures 1 and 3.

Figure 6: "Go to service" link fixes session id

Figure 7 shows the display from a verbose version of the two service/procedure pairs (scroll down for the code).

Figure 7: Verbose service

Here's what's different about the verbose version:
  • The new @input_sessionid parameter shows the ?sessionid= value that is passed from the URL to the procedure via the service's new :sessionid parameter. I know that earlier I said "don't do this", but it's OK as long as you do not trust the value. Here it is used just to determine if a new session is starting because of a (likely) timeout; see the @info variable below.

  • The new @init_SessionID variable shows what CONNECTION_PROPERTY ( 'SessionID' ) returned when first called.

  • The new @info variable (empty here) will show 'New session (new request)' when the service is first started, and 'New session (possible timeout)' when the ?sessionid= value passed from the URL has timed out.

  • http_session_timeout shows that the timeout period has been set to 1 minute for testing purposes, via SET TEMPORARY OPTION HTTP_SESSION_TIMEOUT = '1'.

  • SessionCreateTime and SessionLastTime are two useful CONNECTION_PROPERTY values; see the Help for more information.

  • The @counter variable displays a new column in the GLOBAL TEMPORARY TABLE state: how many times services have been executed in the current session.
Here's the code for the modified state table and the two verbose service/procedure pairs; you can use this code for your own experiments with HTTP sessions...
--------------------------------------
-- One row per connection, id = 1 always.

CREATE GLOBAL TEMPORARY TABLE state (
id INTEGER NOT NULL DEFAULT 1
PRIMARY KEY CHECK ( id = 1 ),
value VARCHAR ( 100 ) NOT NULL,
counter BIGINT NOT NULL DEFAULT 0 )
ON COMMIT PRESERVE ROWS;

--------------------------------------
CREATE SERVICE s1
TYPE 'RAW' AUTHORIZATION OFF USER DBA
AS CALL p1 ( :sessionid );

--------------------------------------
CREATE PROCEDURE p1 (
IN @input_sessionid LONG VARCHAR )
RESULT (
html_string LONG VARCHAR )
BEGIN

DECLARE @info VARCHAR ( 100 );
DECLARE @init_SessionID VARCHAR ( 36 );
DECLARE @session_id VARCHAR ( 36 );
DECLARE @input_state VARCHAR ( 100 );
DECLARE @counter BIGINT;

SET @info = ''; -- nothing interesting to report yet

-- Save the initial SessionID for display.

SET @init_SessionID =
CONNECTION_PROPERTY ( 'SessionID' );

-- Use the SessionID, or create a new one.

SET @session_id = TRIM ( COALESCE (
CONNECTION_PROPERTY ( 'SessionID' ), '' ) );

IF @session_id = '' THEN

SET @session_id = NEWID();
CALL sa_set_http_option (
'SessionID', @session_id );

-- Determine likely reason for the new session.

IF COALESCE ( @input_sessionid, '' ) = '' THEN
SET @info = 'New session (new request)';
ELSE
SET @info = 'New session (possible timeout)';
END IF;

END IF;

-- Set a really short timeout for testing purposes.

SET TEMPORARY OPTION HTTP_SESSION_TIMEOUT = '1';

-- Get the input state, if it exists.

SELECT state.value
INTO @input_state
FROM state;

-- Create or update the state.

INSERT state ( value )
ON EXISTING UPDATE
VALUES ( 'state set in s1 - p1' );

-- Update the service execution counter.

UPDATE state
SET state.counter = state.counter + 1;

COMMIT; -- don't forget ON COMMIT PRESERVE ROWS

-- Get the service execution counter.

SELECT state.counter
INTO @counter
FROM state;

-- Send the HTML back to the browser.

CALL sa_set_http_header (
'Content-Type', 'text/html' );

SELECT STRING (
'<HTML><BODY><TITLE>Verbose s1 - p1</TITLE>',
'<PRE>',
'Verbose Service s1 - Procedure p1\x0d\x0a\x0d\x0a',
'@input_sessionid: ',
COALESCE ( @input_sessionid, '[NULL]' ),
'\x0d\x0a',
'Connection: ',
CONNECTION_PROPERTY ( 'Number' ),
'\x0d\x0a',
'@init_SessionID: ',
COALESCE ( @init_SessionID, '[NULL]' ),
'\x0d\x0a',
'@session_id: ',
COALESCE ( @session_id, '[NULL]' ),
'\x0d\x0a',
'@info: ',
@info,
'\x0d\x0a',
'http_session_timeout: ',
CONNECTION_PROPERTY ( 'http_session_timeout' ),
'\x0d\x0a',
'SessionCreateTime: ',
CONNECTION_PROPERTY ( 'SessionCreateTime' ),
'\x0d\x0a',
'SessionLastTime: ',
CONNECTION_PROPERTY ( 'SessionLastTime' ),
'\x0d\x0a',
'@input_state: ',
COALESCE ( @input_state, '[NULL]' ),
'\x0d\x0a',
'@counter: ',
@counter,
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s1',
'?sessionid=', @session_id,
'">Go to service s1 - Procedure p1</A>',
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s2',
'?sessionid=', @session_id,
'">Go to service s2 - Procedure p2</A>',
'</PRE>',
'</BODY></HTML>' );

END; -- p1

--------------------------------------
CREATE SERVICE s2
TYPE 'RAW' AUTHORIZATION OFF USER DBA
AS CALL p2 ( :sessionid );

--------------------------------------
CREATE PROCEDURE p2 (
IN @input_sessionid LONG VARCHAR )
RESULT (
html_string LONG VARCHAR )
BEGIN

DECLARE @info VARCHAR ( 100 );
DECLARE @init_SessionID VARCHAR ( 36 );
DECLARE @session_id VARCHAR ( 36 );
DECLARE @input_state VARCHAR ( 100 );
DECLARE @counter BIGINT;

SET @info = ''; -- nothing interesting to report yet

-- Save the initial SessionID for display.

SET @init_SessionID =
CONNECTION_PROPERTY ( 'SessionID' );

-- Use the SessionID, or create a new one.

SET @session_id = TRIM ( COALESCE (
CONNECTION_PROPERTY ( 'SessionID' ), '' ) );

IF @session_id = '' THEN

SET @session_id = NEWID();
CALL sa_set_http_option (
'SessionID', @session_id );
SET TEMPORARY OPTION
HTTP_SESSION_TIMEOUT = '1';

-- Determine likely reason for the new session.

IF COALESCE ( @input_sessionid, '' ) = '' THEN
SET @info = 'New session (new request)';
ELSE
SET @info = 'New session (possible timeout)';
END IF;

END IF;

-- Set a really short timeout for testing purposes.

SET TEMPORARY OPTION HTTP_SESSION_TIMEOUT = '1';

-- Get the input state, if it exists.

SELECT state.value
INTO @input_state
FROM state;

-- Create or update the state.

INSERT state ( value )
ON EXISTING UPDATE
VALUES ( 'state set in s2 - p2' );

-- Update the service execution counter.

UPDATE state
SET state.counter = state.counter + 1;

COMMIT; -- don't forget ON COMMIT PRESERVE ROWS

-- Get the service execution counter.

SELECT state.counter
INTO @counter
FROM state;

-- Send the HTML back to the browser.

CALL sa_set_http_header (
'Content-Type', 'text/html' );

SELECT STRING (
'<HTML><BODY><TITLE>Verbose s2 - p2</TITLE>',
'<PRE>',
'Verbose Service s2 - Procedure p2\x0d\x0a\x0d\x0a',
'@input_sessionid: ',
COALESCE ( @input_sessionid, '[NULL]' ),
'\x0d\x0a',
'Connection: ',
CONNECTION_PROPERTY ( 'Number' ),
'\x0d\x0a',
'@init_SessionID: ',
COALESCE ( @init_SessionID, '[NULL]' ),
'\x0d\x0a',
'@session_id: ',
COALESCE ( @session_id, '[NULL]' ),
'\x0d\x0a',
'@info: ',
@info,
'\x0d\x0a',
'http_session_timeout: ',
CONNECTION_PROPERTY ( 'http_session_timeout' ),
'\x0d\x0a',
'SessionCreateTime: ',
CONNECTION_PROPERTY ( 'SessionCreateTime' ),
'\x0d\x0a',
'SessionLastTime: ',
CONNECTION_PROPERTY ( 'SessionLastTime' ),
'\x0d\x0a',
'@input_state: ',
COALESCE ( @input_state, '[NULL]' ),
'\x0d\x0a',
'@counter: ',
@counter,
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s1',
'?sessionid=', @session_id,
'">Go to service s1 - Procedure p1</A>',
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s2',
'?sessionid=', @session_id,
'">Go to service s2 - Procedure p2</A>',
'</PRE>',
'</BODY></HTML>' );

END; -- p2
-- [end]

Monday, June 16, 2008

IS NULL versus NOT EXISTS

Here is the pseudo-SQL for a fairly common query:

SELECT * 
FROM t1
WHERE there is no matching row in t2;
The "matching" relationship between tables t1 and t2 is probably a parent-child foreign key relationship like this:
CREATE TABLE t1 (
pk INTEGER NOT NULL PRIMARY KEY );

CREATE TABLE t2 (
pk INTEGER NOT NULL PRIMARY KEY,
fk INTEGER NOT NULL REFERENCES t1 ( pk ) );
One way to code the query uses NOT EXISTS to find which parent rows in t1 don't have any child rows in t2:
SELECT *
FROM t1
WHERE NOT EXISTS
( SELECT *
FROM t2
WHERE t2.fk = t1.pk )
ORDER BY t1.pk;
In other words, "SELECT each row in t1 WHERE NOT EXISTS ( a match between that row in t1 and any row in t2 )"

To me, that's easy to think of, easy to write, easy to read and easy to explain. SQL is a pretty funky language, but WHERE NOT EXISTS is far from the worst it has to offer.

There is another method that does move things further out on the Funky SQL Scale: LEFT OUTER JOIN and IS NULL...
SELECT t1.*
FROM t1 LEFT OUTER JOIN t2
ON t1.pk = t2.fk
WHERE t2.fk IS NULL
ORDER BY t1.pk;
Outer joins give lots of people lots of trouble, why would someone use LEFT OUTER JOIN if there was a simpler alternative?

And just how does it work, anyway?

Dealing with the second question first, the LEFT OUTER JOIN between t1 and t2 guarantees that each row in t1 that does not have matching counterpart in t2 will still be represented in the candidate result set. Also, that candidate row will have NULL values for all the t2 columns. So, when the clause WHERE t2.fk IS NULL is applied, the final result set is limited to just the rows where there is no match in t2.

For rows in t1 that do have matching rows in t2, the t2.fk column will not be NULL, so they are excluded.

Magic! But still... if you stumble upon that query in an application you might be hard pressed to figure out what it's for. Self-documenting code, it's not.

The answer to the first question, "why write it that way?", is the same as the answer to this question:
What is the most common reason people write incomprehensible code?
Answer: Performance! The IS NULL version might run a bit faster than NOT EXISTS!

Here is a diabolical example: big tables, lots of rows, COUNT(*), cold cache that's too small to begin with...
SELECT COUNT(*)
FROM enwiki_text
WHERE NOT EXISTS
( SELECT *
FROM enwiki_entry
WHERE enwiki_entry.from_line_number
= enwiki_text.line_number );
COUNT()
225,348,118
Execution time: 627 seconds

SELECT COUNT(*)
FROM enwiki_text LEFT OUTER JOIN enwiki_entry
ON enwiki_text.line_number
= enwiki_entry.from_line_number
WHERE enwiki_entry.from_line_number IS NULL;

COUNT()
225,348,118
Execution time: 537 seconds
Here's what the two plans look like, NOT EXISTS on the left and IS NULL on the right:


The parent table enwiki_text takes up 20.6G of space and contains 231,900,608 rows, while the child enwiki_entry "only" uses 17.2G to hold 6,552,490 rows:
CREATE TABLE enwiki_text ( 
line_number BIGINT NOT NULL DEFAULT autoincrement,
line_text LONG VARCHAR NOT NULL DEFAULT '',
CONSTRAINT ASA76 PRIMARY KEY CLUSTERED (
line_number ) );

CREATE TABLE enwiki_entry (
page_number BIGINT NOT NULL,
from_line_number BIGINT NOT NULL,
to_line_number BIGINT NOT NULL,
page_title VARCHAR ( 1000 ) NOT NULL,
page_id VARCHAR ( 100 ) NOT NULL,
page_text LONG VARCHAR NOT NULL,
CONSTRAINT ASA80 PRIMARY KEY CLUSTERED (
page_number ) );

ALTER TABLE DBA.enwiki_entry
ADD CONSTRAINT fk_from NOT NULL FOREIGN KEY (
from_line_number )
REFERENCES DBA.enwiki_text (
line_number )
ON UPDATE RESTRICT ON DELETE RESTRICT;
Conclusion? I still prefer NOT EXISTS, but I understand why folks might use LEFT OUTER JOIN and IS NULL... if it helps, and if it matters (it often doesn't).

Monday, June 9, 2008

Anil Goel on CLUSTERED

Anil Goel has posted a comment / rebuttal / explanation to the Multiple CLUSTERED Indexes item.

If you didn't read the original post, don't bother, just read Anil's comment.

Sunday, June 8, 2008

Top 10 Posts

Here are the top 10 "popular pages" in this blog, according to StatCounter:

1. OpenXML() Rocks!

2. How To Make SQL Anywhere Slow

3. Tip: Triggering an Audit Trail

4. Defragmenting The Temporary File

5. Unpublished MySQL FAQ ...this was Number One for far too long!

6. Today's Client Question: Temp File Size

7. Today's Client Question: SQL Anywhere vs ASE

8. SQL Anywhere "Panorama" Beta

9. CREATE EVENT ... TYPE DEADLOCK

10. The Evolution of a SELECT

Looks like actual code is popular, imagine that!

Oh, and about all those missing posts in May... no excuses, I was busy on far less important things like work, even vacation.

Saturday, June 7, 2008

Thursday, June 5, 2008

Multiple CLUSTERED Indexes

Q: Why can't I have more than one CLUSTERED index on the same table?

A: Because the rows in one table cannot be stored in more than one order at the same time.

Q: That's not what I asked. I asked why can't I define two indexes as CLUSTERED if they are both sorted in the same order as rows in the same table?

A: Why on earth would you want that? That's just stupid.

Q: Gee, thanks for the vote of confidence.

A: Well, give me an example.

Q: Here's one, containing 6.5 million rows:

CREATE TABLE enwiki_entry ( 
page_number BIGINT NOT NULL
PRIMARY KEY CLUSTERED,
from_line_number BIGINT NOT NULL,
to_line_number BIGINT NOT NULL,
page_title VARCHAR ( 1000 ) NOT NULL,
page_id VARCHAR ( 100 ) NOT NULL,
page_text LONG VARCHAR NOT NULL,
CONSTRAINT fk_page
NOT NULL FOREIGN KEY ( page_number )
REFERENCES enwiki_text_xref ( page_number ),
CONSTRAINT fk_from
NOT NULL FOREIGN KEY ( from_line_number )
REFERENCES enwiki_text ( line_number ),
CONSTRAINT fk_to
NOT NULL FOREIGN KEY ( from_line_number )
REFERENCES enwiki_text ( line_number ) );

The page_number primary key is an autoincrement 1, 2, 3 which exactly matches the row INSERT order.

The from_line_number and to_line_number columns are both foreign keys to another table which contains 200 million rows. Both are stored in monotonically increasing order that is also the same as the enwiki_entry row INSERT order.

All three columns are indexes, and all three are valid candidates for CLUSTERED. You can imagine, with row counts like that, the performance of some queries depends heavily on having the right CLUSTERED index.

A: You could use ALTER INDEX to move the CLUSTERED attribute from one index to another:
ALTER INDEX PRIMARY KEY         ON enwiki_entry NONCLUSTERED;
ALTER INDEX FOREIGN KEY fk_from ON enwiki_entry CLUSTERED;

ALTER INDEX FOREIGN KEY fk_from ON enwiki_entry NONCLUSTERED;
ALTER INDEX FOREIGN KEY fk_to ON enwiki_entry CLUSTERED;

ALTER INDEX FOREIGN KEY fk_to ON enwiki_entry NONCLUSTERED;
ALTER INDEX PRIMARY KEY ON enwiki_entry CLUSTERED;

Q: My point exactly... if I can use ALTER, why can't I just define them all as CLUSTERED?

With ALTER, the query optimizer will only see one index as CLUSTERED when analyzing a query, and the results would depend on which index I chose to make CLUSTERED.

With all three indexes defined as CLUSTERED, the optimizer could consider all three and do the choosing itself.

A: Are you saying the optimizer does a better job of picking indexes than you do?

Q: Pretty much. How about you?

Wednesday, June 4, 2008

"Stupendous performance"

You can't make this stuff up...

"What caught my attention was the database's stupendous performance. Even the most complex queries are solved almost instantaneously. The database returns so fast and it is so stable that we don't worry about it."
- Antonio Magno Lima Espechit, about why Embraer uses SQL Anywhere as their in-flight test engineering database manager
More: Sybase Customers Honored for Visionary Computing Projects That Promote Social and Economic Progress

Tuesday, June 3, 2008

The New Plan Viewer Rocks!

SQL Anywhere has had an XML-based "Graphical Plan" display in ISQL for many years, but Version 11 is introducing a separate "Plan Viewer" window with several improvements.

The big difference is you no longer have to go to the ISQL - Tools - Options - Plan tab to tell it "What kind of plan do you want?"... the Version 8, 9 and 10 Plan tabs are gone gone gone:



Now you can pick those options each time you ask for a plan. Here's what ISQL - SQL - Get Plan shows you in Version 11:



The Plan Viewer is an excellent example of productive dialog design, with "in your face" buttons instead of hidden popup / dropdown / flapdoodle menu options that are holdovers from IBM's "Common User Access" methodology of the 1980s.

Here's one quibble: Maybe putting the Get Plan button wayyyyy over on the right might not have been the most intuitive choice. But... that only confused me on a really wide screen, and only until I realized the button was there.

Another quibble is that the top "SQL" frame isn't resizable to see more (yet... maybe later).

There is a Hide SQL button at the bottom, and that's great, that should stay, it's way easier than drag - fumble - minimize.

Another "in your face" item is the Save button, a nice departure from the tiresome File pulldown.

But don't say "wizard"... the Plan Viewer is not a wizard... wizards are where ten windows replace one, with at most one useful button per window, plus Microsoft Bob graphics that do nothing to relieve the frustration.

The Plan Viewer is... well, it's exactly what it says it is, it's the Plan Viewer, all in one place.

Monday, June 2, 2008

Panorama Goes GA in July!

According to this press release, "SQL Anywhere 11 general availability is currently scheduled for July 2008"...

Sybase iAnywhere Quick to Support ADO.NET Entity Framework and LINQ
If you're quick, you can probably still get in on the Panorama beta.