SQL Anywhere: October 2011

Monday, October 31, 2011

A New Appreciation

Updating the Foxhound FAQ has given me a new appreciation for the folks who labor on the SQL Anywhere docs... and why, just maybe why, they don't ship new docs with every steenking EBF!

Because it's hard, that's why!

It's hard keeping up with the changes.

The new stuff, fine, that's hard too, but it's interesting, and the docs for that have got a guaranteed audience.

But all those changes? Something minor affecting how the product looks or behaves, and suddenly the docs need changing... who knows where? ...maybe everywhere!

Or worse, some name gets changed, not affecting the product at all, just the documentation!

Worse still, some internal name gets changed, internal to the documentation itself, not visible to anyone except the doc writer who has to fix the resulting errors.

Ah, it could be worse...

...the developers could be in charge of the docs.

Friday, October 28, 2011

The fRiDaY File - Don't dress up as a zombie in Wyoming or New Jersey!

Just in time for Halloween! The Zombie Survivability Map!

...in Wyoming they'll just laugh at you, in New Jersey you might scare people so badly there's no telling what will happen to you:

Intriguing

A "To Do" list is a frightening thing because it's always growing in size, never shrinking, and always changing shape as priorities shift.

For example, consider what happens to this stub-of-a-list when the forecast starts calling for rain:

Mow lawn.
Fix roof.
Buy milk.
Figure out Fuji.

Or, when you pour the last of the milk on your cereal: priorities change, and so does the To Do list.

So... what would it take to move "Figure out Fuji" further up on the list?

Maybe this...

"All of the computing resources that make up Fuji are constantly monitoring their own performance, and using web services to communicate that to all the other computing resources."
from Is Fuji Really a Cloud? by Eric Farrar

Is that unique to Fuji, or is it built in to the SQL Anywhere database server, or is it a moot point?

And what does "constantly monitoring their own performance" actually mean?

Like the title says: Intriguing.

Thursday, October 27, 2011

The Thursday Quote - Anil Dash

It's not long until we get "21 Ubuntu Install Tips That Will Drive Him Crazy In Bed!"
It's Always August by Anil Dash, from A Blog About Making Culture August 31, 2006

Until recently I'd never heard the term "listicle" but in retrospect I've certainly . . . been guilty . . . of writing them.

In fact, my most popular blog post is essentially a listicle-without-numbers (Unpublished MySQL FAQ), and this 1999 cult hit probably qualifies as a listicle as well: Silverstream By The Numbers.

What's a Listicle?

The Wikipedia definition provides the basics:

"It's so easy you wonder why everyone doesn't do it until you realize that now it's all they do: Come up with an idea ("Top 10 Worst [X]") on the L train ride to the office that morning, [and] slap together 10 (or 25, or 100) cultural artifacts ripe for the kind of snarky working over that won't actually tax you at all as a writer/thinker."

But Anil Dash's 2006 blog post is even more fun to read; here's an excerpt:

If you've never worked in the publishing or media industries, you might not know that August is officially the month where everyone basically phones it in. Back in New York, people would speak of going to The Hamptons so often that it's been verbed into "Hamptoning" and used as a generic term for going on vacation. While bigwigs and editors are away cavorting, a makeshift army of interns, temps, and recent college grads generally takes over. These kids usually don't have much experience, and newspaper editors don't want to have to do any hard work during the dog days, so the end result is that you get a combination of lazy writing and some really crappy journalism.

What kind of crappy journalism? Listicles! "Best Of"s. Special Theme Issues. And all of these pieces are topped by blaring, or alarmist, or horribly-punned headlines. You might notice that the other time of year this happens is around the end of the year or at New Year's, when Christmas and the other December holidays conspire to leave major media outlets virtually unstaffed. Then, you get year-end wrapups or another round of Best Ofs.

Indeed, as former White House Chief of Staff Andrew Card famously told the New York Times, "From a marketing point of view, you don't introduce new products in August."

So, then, why is it always August in the "look at me!" part of the blogosphere? Because the people who are blogging for an audience of thousands, or for hundreds of thousands, are prone to a lot of those same tendencies. Digg and delicious and the rest are littered with Top 10s and geek equivalents of Cosmo coverlines. It's not long until we get "21 Ubuntu Install Tips That Will Drive Him Crazy In Bed!"

So what does that mean for the future? No more listicles?

No "Top 10 Cool New Features In SQL Anywhere 16"?

Maybe not... every one of those listicles was enormously hard to write, and since a listicle is supposed to be cheesy easy then clearly there's something wrong here... :)

Next week: Phillip G. Armour

Wednesday, October 26, 2011

Latest SQL Anywhere EBFs

The three asterisks "***" show what's new since October 14. Only EBFs for the three fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.

Current builds for the active platforms...

HP-UX Itanium    12.0.1.3469 EBF ***             14 Oct 2011 ***
                 11.0.1.2664 EBF                 24 Aug 2011
                 10.0.1.3777 EBF                 14 Oct 2008

IBM AIX          12.0.1.3469 EBF ***             20 Oct 2011 ***
                 11.0.1.2637 EBF                 26 Jul 2011

Linux x86/x64    12.0.1.3457 EBF                 12 Oct 2011
                 11.0.1.2645 EBF                 29 Jul 2011
                 10.0.1.4239 EBF                 17 Jun 2011

Mac OS           12.0.1.3352 EBF                 26 May 2011
                 11.0.1.2449 EBF                 29 Jun 2010
                 10.0.1.4042 EBF                 01 Oct 2010

Solaris SPARC    12.0.1.3469 EBF ***             20 Oct 2011 ***
                 11.0.1.2632 EBF                 07 Jul 2011
                 10.0.1.4239 EBF                 17 Jun 2011

Windows x86/x64  12.0.1.3457 EBF                 12 Oct 2011
                 11.0.1.2686 EBF ***             13 Oct 2011 ***
                 10.0.1.4239 EBF                 17 Jun 2011

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Solaris x64      11.0.1.2645 EBF                 05 Aug 2011
                 10.0.1.3870 EBF                 01 Apr 2009

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

Monday, October 24, 2011

What's Cool, What's New in Foxhound 1.2

Foxhound is a browser-based health, availability and performance monitor for SQL Anywhere that displays everything you need to know at a glance.

Foxhound 1.2 is the next version, and a "Release Candidate" build is now available... this is just like a "Beta" except there's no "beta testing expiry date".

To request a copy, send me an email at breck.carter@gmail.com.

What's Cool (in Foxhound 1.1)		What's New (in Foxhound 1.2)
Foxhound Alerts tell you if something bad is happening: can't connect, slow response, blocked connections, high CPU, I/O bottleneck, and 24 other symptoms.		Now you can use Gmail to send the Alerts as well as receive them: smtp.gmail.com via SSL and port 587.
Foxhound can monitor up to 100 databases.		Now it's easier to manage large numbers of Monitor sessions: The hard-to-find "Alerts Criteria" page has been redesigned and elevated as the "Monitor Options" page. The Default Settings can be directly edited on the Monitor Options page, separate from the options for any particular target database. The Force Default Settings On All Targets button on the Monitor Options page lets you publish new settings to all the targets. The Manage Multiple Monitor Sessions feature lets you specify batches of target databases, and the Start and Stop All Sessions buttons let you turn sampling on and off for each batch.
The Monitor tab on the Foxhound Menu page shows all the target databases at a glance.		The Monitor tab is now easier to use, and more informative: The Disable Refresh and Enable Refresh buttons make it easier to scroll down to look at something. The lists of targets and outstanding Alerts have been vertically squished so you don't have to scroll down so far when you've got lot of targets. More "at a glance" columns have been added: Active Alerts count, with a link down into the Alerts list Conns Waiting Req Locks Held, Blocked Conns CPU Time Temp Space Disk Reads, Writes The Start All Sampling and Stop All Sampling buttons let you turn sampling on and off for all the targets.
The History page shows all the details for an individual database at a glance. You can see what happened overnight, yesterday, as far back as you want, even the connections, and the connections can be sorted on any of the columns, like "CPU Time" and "Locks Held". You can also see all the blocked connections: Who's blocked? What are they waiting for? How long have they been waiting? Who's holding the locks? What kind of locks are they? What tables? What rows? How long has it been since they last did a commit?		Scrolling through gigabytes of data used to be slowwwww, like continental drift, now it's faster. New scroll amounts have been added (500 samples and 3 hours): « Newest « 500 « 100 « 20 « 1 sample 1 sample » 20 » 100 » 500 » Oldest » « Month « Week « Day « 3 Hours « 1 Hour 1 Hour » 3 Hours » Day » Week » Month » The "Freeze Frame Heights" button lets you resize and freeze the History page frames so they don't reset ("Grrr!") every time you scroll.
It's your data: Foxhound lets you run adhoc queries.		New views have been added for adhoc reporting. A separate read-only "Adhoc Schema" database lets you see what the views and underlying tables look like. New connection-level columns have been added to make queries easier to write: LockRowID BIGINT NULL blocked_connection_count BIGINT NOT NULL DEFAULT 0 current_req_status VARCHAR ( 100 ) NOT NULL DEFAULT '' cache_satisfaction DECIMAL ( 30, 0 ) NOT NULL DEFAULT 0.0 time_connected BIGINT NOT NULL DEFAULT 0 total_waits BIGINT NOT NULL DEFAULT 0 waiting_time DECIMAL ( 30, 6 ) NOT NULL DEFAULT 0.0 transaction_running_time BIGINT NOT NULL DEFAULT 0 time_since_last_request BIGINT NOT NULL DEFAULT 0 index_satisfaction DECIMAL ( 30, 0 ) NOT NULL DEFAULT 0.0 Except for LockRowID, all the new columns contain values that Foxhound used to calculate only when the data was displayed on the Monitor and History pages; now these values are calculated once and stored permanently.
Foxhound automatically copies and upgrades all of your data when you install a new version. The upgrade process handles all the schema differences between the old and new versions of the Foxhound database, no matter what the old and new versions are (well, as long as the new version isn't older than the old version :).		The data upgrade process now runs faster, even though it has to work harder in version 1.2 to add all those new columns. You also have control over how much data to upgrade: The default is FOXHOUND1UPGRADE = ALL to copy all the data from your old Foxhound database to the new one. If you choose FOXHOUND1UPGRADE = OPTIONS during the installation process, Foxhound will copy everything except the Monitor samples. When you start Foxhound again, all the old sample data will be gone but sampling will start again right away (assuming it was running before you installed the new version). If you want to save the Monitor samples recorded since a particular date, specify FOXHOUND1UPGRADE = yyyymmmdd. To save the samples recorded during the past n days, use FOXHOUND1UPGRADE = n. What's really cool about the new upgrade process is you can use it to purge, shrink and reorganize the Foxhound database: just reinstall the same version of Foxhound with FOXHOUND1UPGRADE = yyyymmmdd or FOXHOUND1UPGRADE = n.
Foxhound supports target databases running all versions of SQL Anywhere from 5.5 through 12.0.1.		Good news, bad news: Foxhound still supports all those old target databases, back to 5.5.05.2787. Foxhound itself now runs on SQL Anywhere Version 12.0.1. Foxhound will use the 64-bit version of SQL Anywhere 12.0.1 if it is installed. Previously, Foxhound ran only on the 32-bit version of SQL Anywhere 11.0.1. You can force Foxhound to use the 32-bit version of SQL Anywhere by setting the FOXHOUND1BIN environment variable to Bin32... sometimes, it's just easier to do that than to try to figure out what's going wrong with ODBC and a legacy target database. Bad news: Foxhound requires SQL Anywhere 12.0.1.3298 or later to run. And it still only runs on Windows. But none of that affects your target database: as long as Foxhound can establish an ODBC connection to your database, it can monitor the performance and display the schema.

More "What's New"...

The look-and-feel of the Foxhound Options page has changed to match the new improved Monitor Options window.
A single global "Enable Emails" setting has been added to the Foxhound Options page in case you want to turn off an accidental "alert storm".

New shortcuts have been added:

start - All Programs 
   Foxhound1
      Start Foxhound via Chrome
      Tools 
         Backup Foxhound Transaction Log
         Start Foxhound via Chrome - debug

Exception handling has been improved to allow Foxhound to continue operating after receiving bad data from a target database.
The thirty-day Evaluation period can now be extended: just get a Evaluation Renewal key from breck.carter@gmail.com.

Friday, October 21, 2011

The fRiDaY File - Will there be tshirts?

Set UPDATE With COMMIT EVERY n ROWS

Just for fun, have a look at the endless Oracle arguments about how to update millions of rows in a table.

Then ask yourself, "Am I lucky or what?" to have the UPDATE COMMIT EVERY clause in SQL Anywhere.

UPDATE statement

Modifies existing rows in database tables.

Syntax 1

UPDATE [ row-limitation ] table-list ] 
   SET set-item, ...
   [ FROM table-expression [,...] ]
   [ WHERE search-condition ]
   [ ORDER BY expression [ ASC | DESC ] , ... ]
   [ OPTION( query-hint, ... ) ]
   [ COMMIT EVERY rowcount-expression ROWS ]

table-list : 
   table-name [,...]  

table-name : 
   [ owner.]table-name [ [ AS ] correlation-name ]
   | [ owner.]view-name [ [ AS ] correlation-name ]
   | derived-table

derived-table : 
   ( select-statement ) 
   [ AS ] correlation-name [ ( column-name [,... ] ) ]

table-expression : 
   A full table expression that can include joins. See FROM clause.

row-limitation :
   FIRST
   | TOP { ALL | limit-expression } [ START AT startat-expression ]

limit-expression : simple-expression

startat-expression : simple-expression

simple-expression :
   integer
   | variable
   | ( simple-expression )
   | ( simple-expression { + | - | * } simple-expression )

set-item :
   [ correlation-name.]column-name = { expression | DEFAULT }
   | [owner-name.]table-name.column-name = { expression | DEFAULT }
   | @variable-name = expression

query-hint :
   MATERIALIZED VIEW OPTIMIZATION option-value
   | FORCE OPTIMIZATION
   | FORCE NO OPTIMIZATION
   | option-name = option-value

table-name : 
   [ owner.]base-table-name
   | temporary-table-name
   | derived-table-name
   | [ owner.]view-name

option-name : identifier

option-value : hostvar (indicator allowed), string, identifier, or number

rowcount-expression : simple-expression

Yeah, dreams are great, aren't they?

Thursday, October 20, 2011

The Thursday Quote - Linus Torvalds

"C++ is a horrible language. It's made more horrible by the fact that a lot of substandard programmers use it, to the point where it's much much easier to generate total and utter crap with it."
Re: Convert builin-mailinfo.c to use The Better String Library by Linus Torvalds, September 6, 2007

My, my, my... rants have come a long way (downhill!) since Edsger Dijkstra's famous letter to the editor Go To Considered Harmful back in 1968. When Dijkstra was done with the Go To, it was done... to code a Go To nowadays is to commit a heresy.

But Linus Torvalds versus C++? I doubt he convinced a single person to give up C++ in favor of C with this feeble screed.

Look, I detest C++ myself, and overblown object frameworks, but there's not much here to work with.

You be the judge, here's Torvald's whole rant edited for tender ears; read it here, or read the unretouched version here, then read a real rant.

From: Linus Torvalds  torvalds at linux-foundation.org 
Subject: Re: [RFC] Convert builin-mailinfo.c to use The Better String Library.
Newsgroups: gmane.comp.version-control.git
Date: 2007-09-06 17:50:28 GMT

On Wed, 5 Sep 2007, Dmitry Kakurin wrote:
> 
> When I first looked at Git source code two things struck me as odd:
> 1. Pure C as opposed to C++. No idea why. Please don't talk about portability,
> it's BS.

*YOU* are full of bull[redacted].

C++ is a horrible language. It's made more horrible by the fact that a lot 
of substandard programmers use it, to the point where it's much much 
easier to generate total and utter crap with it. Quite frankly, even if 
the choice of C were to do *nothing* but keep the C++ programmers out, 
that in itself would be a huge reason to use C.

In other words: the choice of C is the only sane choice. I know Miles 
Bader jokingly said "to [redacted] you off", but it's actually true. I've come 
to the conclusion that any programmer that would prefer the project to be 
in C++ over C is likely a programmer that I really *would* prefer to [redacted] 
off, so that he doesn't come and screw up any project I'm involved with.

C++ leads to really really bad design choices. You invariably start using 
the "nice" library features of the language like STL and Boost and other 
total and utter crap, that may "help" you program, but causes:

 - infinite amounts of pain when they don't work (and anybody who tells me 
   that STL and especially Boost are stable and portable is just so full 
   of BS that it's not even funny)

 - inefficient abstracted programming models where two years down the road 
   you notice that some abstraction wasn't very efficient, but now all 
   your code depends on all the nice object models around it, and you 
   cannot fix it without rewriting your app.

In other words, the only way to do good, efficient, and system-level and 
portable C++ ends up to limit yourself to all the things that are 
basically available in C. And limiting your project to C means that people 
don't screw that up, and also means that you get a lot of programmers that 
do actually understand low-level issues and don't screw things up with any 
idiotic "object model" crap.

So I'm sorry, but for something like git, where efficiency was a primary 
objective, the "advantages" of C++ is just a huge mistake. The fact that 
we also [redacted] off people who cannot see that is just a big additional 
advantage.

If you want a VCS that is written in C++, go play with Monotone. Really. 
They use a "real database". They use "nice object-oriented libraries". 
They use "nice C++ abstractions". And quite frankly, as a result of all 
these design decisions that sound so appealing to some CS people, the end 
result is a horrible and unmaintainable mess.

But I'm sure you'd like it more than git.

   Linus

Next week: Anil Dash

Wednesday, October 19, 2011

Set UPDATE With TOP START AT and COMMIT

Monday's article about Set UPDATE Versus WHERE CURRENT OF Cursor showed that a set-oriented UPDATE statement runs a lot faster than a row-oriented FOR loop, but it also asked the question

What about the commits?

This article shows how the TOP and START AT clauses can be used to break one giant set-oriented UPDATE statement into smaller subsets with COMMIT statements in between.

This technique

still runs a lot faster than the FOR loop (19 minutes instead of 75 minutes in one test),
doesn't run too much slower than a single UPDATE (19 minutes versus 15 minutes for the single UPDATE), and
can be used for large numbers of rows without blowing up the rollback log or gathering up endless row locks.

The following example looks a lot like the earlier article Using LockCount To Display UPDATE Progress; in fact, it uses the same funky technique to display progress messages rather than the simpler method shown in Set UPDATE Versus WHERE CURRENT OF Cursor.

Here's the code, with an explanation afterwards:

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

DECLARE @rroad_group_2_property_pivot_row_count   BIGINT;
DECLARE @start_at_row_count                       BIGINT; 
DECLARE @continue_updating                        VARCHAR ( 1 );
DECLARE @remaining_row_count                      BIGINT; 
DECLARE @top_limit_row_count                      BIGINT;
DECLARE @batch_count                              BIGINT;
DECLARE @batch_counter                            BIGINT;
DECLARE @batch_description                        LONG VARCHAR;
 
CHECKPOINT;
 
SELECT SYSTAB.count
  INTO @rroad_group_2_property_pivot_row_count
  FROM SYSTAB
 WHERE SYSTAB.table_name = 'rroad_group_2_property_pivot';
 
SET @start_at_row_count = 1;
 
IF @start_at_row_count <= @rroad_group_2_property_pivot_row_count THEN
 
   SET @continue_updating   = 'Y';
   SET @remaining_row_count = @rroad_group_2_property_pivot_row_count;
   SET @top_limit_row_count = LESSER ( @remaining_row_count, 1000000 );
   SET @batch_count         = CAST ( CEILING ( CAST ( @rroad_group_2_property_pivot_row_count AS DOUBLE ) / CAST ( 1000000 AS DOUBLE ) ) AS BIGINT );
   SET @batch_counter       = 1;
 
ELSE
 
   SET @continue_updating = 'N';
 
END IF;
 
WHILE @continue_updating = 'Y' LOOP
 
   INSERT rroad_progress_messages_requested ( requested_by_connection_id ) VALUES ( @@SPID );
 
   COMMIT;
 
   IF @batch_count > 1 THEN
 
      MESSAGE STRING (
         CURRENT TIMESTAMP,
         ' DATA UPGRADE: Updating Foxhound Monitor connection-level sample rows, batch ',
         rroad_f_number_with_commas ( @batch_counter ),
         ' of ',
         rroad_f_number_with_commas ( @batch_count ),
         ' of ',
         rroad_f_number_with_commas ( @rroad_group_2_property_pivot_row_count ),
         ' total rows.' ) TO CONSOLE;
 
      SET @batch_description = STRING (
         ' in batch ',
         rroad_f_number_with_commas ( @batch_counter ),
         ' of ',
         rroad_f_number_with_commas ( @batch_count ),
         ' of ',
         rroad_f_number_with_commas ( @rroad_group_2_property_pivot_row_count ),
         ' total rows' );
 
   ELSE
 
      SET @batch_description = '';
 
   END IF;
 
   TRIGGER EVENT rroad_display_progress_messages (
      @row_count_string  = STRING ( @top_limit_row_count ),
      @batch_description = @batch_description );
 
   UPDATE TOP @top_limit_row_count
          START AT @start_at_row_count
          rroad_group_2_property_pivot
             INNER JOIN rroad_sample_set
                ON rroad_sample_set.sample_set_number = rroad_group_2_property_pivot.sample_set_number
             LEFT OUTER JOIN ( SELECT rroad_group_2_property_pivot.sample_set_number AS sample_set_number,
                                      rroad_group_2_property_pivot.BlockedOn         AS blocking_connection_number,
                                      COUNT(*)                                       AS blocked_connection_count
                                 FROM rroad_group_2_property_pivot
                                WHERE rroad_group_2_property_pivot.BlockedOn > 0
                                GROUP BY rroad_group_2_property_pivot.sample_set_number,
                                         rroad_group_2_property_pivot.BlockedOn          ) AS blocking_connection
                ON blocking_connection.sample_set_number          = rroad_group_2_property_pivot.sample_set_number
               AND blocking_connection.blocking_connection_number = rroad_group_2_property_pivot.connection_number
 
      SET rroad_group_2_property_pivot.blocked_connection_count
                = COALESCE ( blocking_connection.blocked_connection_count, 0 ),
 
          rroad_group_2_property_pivot.current_req_status
                = IF TRIM ( COALESCE ( rroad_group_2_property_pivot.ReqStatus, '' ) ) = ''
                     THEN '-'
                     ELSE CASE TRIM ( rroad_group_2_property_pivot.ReqStatus )
                             WHEN 'Idle'              THEN 'Idle'
                             WHEN 'Unscheduled'       THEN 'Waiting for thread'
                             WHEN 'BlockedIO'         THEN 'Waiting for I/O'
                             WHEN 'BlockedContention' THEN 'Waiting for shared resource'
                             WHEN 'BlockedLock'       THEN 'Blocked by lock'
                             WHEN 'Executing'         THEN 'Executing'
                             ELSE TRIM ( rroad_group_2_property_pivot.ReqStatus )
                          END
                  ENDIF,
 
          rroad_group_2_property_pivot.cache_satisfaction
                = LESSER ( GREATER (
                     COALESCE (
                        CAST ( ROUND ( CAST ( rroad_group_2_property_pivot.CacheHits AS DECIMAL ( 30, 6 ) )
                                          / CAST ( GREATER ( 1, rroad_group_2_property_pivot.CacheRead ) AS DECIMAL ( 30, 6 ) )
                                          * 100.0,
                                       0 )
                               AS DECIMAL ( 30, 0 ) ),
                        0.0 ),
                     0.0 ), 100.0 ), -- limit the cache_satisfaction to between 0 and 100
 
          rroad_group_2_property_pivot.time_connected
                = IF TRIM ( COALESCE ( rroad_group_2_property_pivot.LoginTime, '' ) ) = ''
                     THEN 0
                     ELSE DATEDIFF ( MILLISECOND,
                             rroad_f_dateadd_msec ( 3,
                                                    rroad_group_2_property_pivot.LoginTime ),
                             rroad_sample_set.sample_finished_at ) -- also called "sample_recorded_at"
                  ENDIF,
 
          rroad_group_2_property_pivot.total_waits
                = COALESCE ( rroad_group_2_property_pivot.ReqCountBlockContention, 0 )
                     + COALESCE ( rroad_group_2_property_pivot.ReqCountBlockIO, 0 )
                     + COALESCE ( rroad_group_2_property_pivot.ReqCountBlockLock, 0 )
                     + COALESCE ( rroad_group_2_property_pivot.ReqCountUnscheduled, 0 ),
 
          rroad_group_2_property_pivot.waiting_time
                = COALESCE ( rroad_group_2_property_pivot.ReqTimeBlockContention, 0.0 )
                     + COALESCE ( rroad_group_2_property_pivot.ReqTimeBlockIO, 0.0 )
                     + COALESCE ( rroad_group_2_property_pivot.ReqTimeBlockLock, 0.0 )
                     + COALESCE ( rroad_group_2_property_pivot.ReqTimeUnscheduled, 0.0 ),
 
          rroad_group_2_property_pivot.transaction_running_time
                = IF TRIM ( COALESCE ( rroad_group_2_property_pivot.TransactionStartTime, '' ) ) = ''
                     THEN 0
                     ELSE DATEDIFF ( MILLISECOND,
                             rroad_f_dateadd_msec ( rroad_sample_set.datediff_msec_between_target_and_local,
                                                    rroad_group_2_property_pivot.TransactionStartTime ),
                             rroad_sample_set.sample_finished_at ) -- also called "sample_recorded_at"
                  ENDIF,
 
          rroad_group_2_property_pivot.time_since_last_request
                = IF TRIM ( COALESCE ( rroad_group_2_property_pivot.LastReqTime, '' ) ) = ''
                     THEN 0
                     ELSE DATEDIFF ( MILLISECOND,
                             rroad_f_dateadd_msec ( rroad_sample_set.datediff_msec_between_target_and_local,
                                                    rroad_group_2_property_pivot.LastReqTime ),
                             rroad_sample_set.sample_finished_at ) -- also called "sample_recorded_at"
                  ENDIF,
 
          rroad_group_2_property_pivot.index_satisfaction
                = LESSER ( GREATER (
                     COALESCE (
                        CAST ( ROUND ( CAST ( rroad_group_2_property_pivot.IndLookup
                                                 - LESSER ( rroad_group_2_property_pivot.IndLookup, rroad_group_2_property_pivot.FullCompare )
                                              AS DECIMAL ( 30, 6 ) )
                                          / CAST ( GREATER ( 1, rroad_group_2_property_pivot.IndLookup )
                                                   AS DECIMAL ( 30, 6 ) )
                                          * 100.0,
                                       0 )
                               AS DECIMAL ( 30, 0 ) ),
                        0.0 ),
                     0.0 ), 100.0 )  -- limit the index_satisfaction to between 0 and 100
 
    ORDER BY rroad_group_2_property_pivot.sample_set_number,
          rroad_group_2_property_pivot.connection_number;
 
   COMMIT;
 
   DELETE rroad_progress_messages_requested
    WHERE requested_by_connection_id = @@spid;
 
   COMMIT;
 
   SET @start_at_row_count = @start_at_row_count + 1000000;
 
   IF @start_at_row_count <= @rroad_group_2_property_pivot_row_count THEN
 
      SET @continue_updating   = 'Y';
      SET @remaining_row_count = @remaining_row_count - @top_limit_row_count;
      SET @top_limit_row_count = LESSER ( @remaining_row_count, 1000000 );
      SET @batch_counter       = @batch_counter + 1;
 
   ELSE
 
      SET @continue_updating = 'N';
 
   END IF;
 
END LOOP; -- WHILE @continue_updating = 'Y' LOOP

The initialization logic on lines 17 through 31 fills in all the new variables used to how the rows are divided into batches (subsets) of one million rows each.
The WHILE loop starting on line 33 makes one pass for each batch of rows.
The INSERT on line 35 comes from the technique shown in Using LockCount To Display UPDATE Progress.
The SET @batch_description statement on lines 51 through 58 fills a new string that will be passed to the event which produces the progress messages; more on this later.
The TRIGGER EVENT on lines 66 through 68 fires up the event that produces the progress messages; code for this event is shown later.
The UPDATE on lines 70 through 167 looks just like the original UPDATE except for three new clauses: TOP, START AT and ORDER BY. Sadly, it may be the addition of the ORDER BY which makes this technique somewhat slower than the big-bang UPDATE, but an ORDER BY really is necessary if you're going to use TOP and START AT in this fashion (the Help says that without an ORDER BY the result is "non-deterministic" which is ANSI for "here be dragons!")
The COMMIT on line 169 is why we're going to all this trouble: using set-oriented UPDATE statements for speed, COMMIT for safety.
The DELETE starting on line 171 is more progress message stuff; see Using LockCount To Display UPDATE Progress.
The code on lines 176 through 189 determines what the next batch will be, if there is one. Yes, this is ugly code... yes, it is error prone... yes, it did have errors... OK, can we move on now?

Here are some excerpts from the database console log; note that the "Done:" messages from the event sometimes appear out-of-order with the "DATA UPGRADE:" messages from the caller; this is not surprising since they're running on separate connections:

I. 10/11 06:47:58. 2011-10-11 06:47:58.181 DATA UPGRADE: Updating Foxhound Monitor connection-level sample rows, batch 1 of 7 of 6,133,762 total rows.
I. 10/11 06:48:54. 1% updated (17,304 rows of 1,000,000 in batch 1 of 7 of 6,133,762 total rows)
I. 10/11 06:48:55. 2% updated (25,727 rows of 1,000,000 in batch 1 of 7 of 6,133,762 total rows)
I. 10/11 06:48:57. 3% updated (33,621 rows of 1,000,000 in batch 1 of 7 of 6,133,762 total rows)
I. 10/11 06:48:58. 4% updated (41,298 rows of 1,000,000 in batch 1 of 7 of 6,133,762 total rows)
I. 10/11 06:49:00. 5% updated (56,670 rows of 1,000,000 in batch 1 of 7 of 6,133,762 total rows)
...
I. 10/11 06:51:38. 95% updated (953,906 rows of 1,000,000 in batch 1 of 7 of 6,133,762 total rows)
I. 10/11 06:51:39. 96% updated (961,683 rows of 1,000,000 in batch 1 of 7 of 6,133,762 total rows)
I. 10/11 06:51:40. 97% updated (970,114 rows of 1,000,000 in batch 1 of 7 of 6,133,762 total rows)
I. 10/11 06:51:42. 98% updated (980,956 rows of 1,000,000 in batch 1 of 7 of 6,133,762 total rows)
I. 10/11 06:51:45. 99% updated (993,439 rows of 1,000,000 in batch 1 of 7 of 6,133,762 total rows)
I. 10/11 06:51:47. 100% updated (1,000,000 rows of 1,000,000 in batch 1 of 7 of 6,133,762 total rows)
I. 10/11 06:51:48. 2011-10-11 06:51:48.010 DATA UPGRADE: Updating Foxhound Monitor connection-level sample rows, batch 2 of 7 of 6,133,762 total rows.
I. 10/11 06:51:48. Done: 1,000,000 rows
I. 10/11 06:52:13. 1% updated (10,397 rows of 1,000,000 in batch 2 of 7 of 6,133,762 total rows)
...
I. 10/11 06:54:32. 100% updated (1,000,000 rows of 1,000,000 in batch 2 of 7 of 6,133,762 total rows)
I. 10/11 06:54:33. 2011-10-11 06:54:33.559 DATA UPGRADE: Updating Foxhound Monitor connection-level sample rows, batch 3 of 7 of 6,133,762 total rows.
I. 10/11 06:54:33. Done: 1,000,000 rows
I. 10/11 06:54:52. 1% updated (13,873 rows of 1,000,000 in batch 3 of 7 of 6,133,762 total rows)
...
I. 10/11 06:58:42. 100% updated (1,000,000 rows of 1,000,000 in batch 3 of 7 of 6,133,762 total rows)
I. 10/11 06:58:44. 76% committed (762,137 rows of 1,000,000)
I. 10/11 06:58:44. 2011-10-11 06:58:44.277 DATA UPGRADE: Updating Foxhound Monitor connection-level sample rows, batch 4 of 7 of 6,133,762 total rows.
I. 10/11 06:58:45. Done: 1,000,000 rows
I. 10/11 06:58:59. 1% updated (12,331 rows of 1,000,000 in batch 4 of 7 of 6,133,762 total rows)
...
I. 10/11 07:01:07. 100% updated (1,000,000 rows of 1,000,000 in batch 4 of 7 of 6,133,762 total rows)
I. 10/11 07:01:08. Done: 1,000,000 rows
I. 10/11 07:01:08. 2011-10-11 07:01:08.647 DATA UPGRADE: Updating Foxhound Monitor connection-level sample rows, batch 5 of 7 of 6,133,762 total rows.
I. 10/11 07:01:23. 1% updated (13,758 rows of 1,000,000 in batch 5 of 7 of 6,133,762 total rows)
...
I. 10/11 07:04:06. 100% updated (1,000,000 rows of 1,000,000 in batch 5 of 7 of 6,133,762 total rows)
I. 10/11 07:04:07. Done: 1,000,000 rows
I. 10/11 07:04:07. 2011-10-11 07:04:07.801 DATA UPGRADE: Updating Foxhound Monitor connection-level sample rows, batch 6 of 7 of 6,133,762 total rows.
I. 10/11 07:04:22. 1% updated (10,361 rows of 1,000,000 in batch 6 of 7 of 6,133,762 total rows)
...
I. 10/11 07:06:28. 100% updated (1,000,000 rows of 1,000,000 in batch 6 of 7 of 6,133,762 total rows)
I. 10/11 07:06:29. 2011-10-11 07:06:29.496 DATA UPGRADE: Updating Foxhound Monitor connection-level sample rows, batch 7 of 7 of 6,133,762 total rows.
I. 10/11 07:06:29. Done: 1,000,000 rows
I. 10/11 07:06:41. 3% updated (4,496 rows of 133,762 in batch 7 of 7 of 6,133,762 total rows)
I. 10/11 07:06:42. 10% updated (13,546 rows of 133,762 in batch 7 of 7 of 6,133,762 total rows)
...
I. 10/11 07:06:55. 93% updated (124,676 rows of 133,762 in batch 7 of 7 of 6,133,762 total rows)
I. 10/11 07:06:56. 100% updated (133,762 rows of 133,762 in batch 7 of 7 of 6,133,762 total rows)

Here's the code for the event from Using LockCount To Display UPDATE Progress after being modified to use the new @batch_description parameter.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

CREATE EVENT rroad_display_progress_messages
HANDLER BEGIN
 
DECLARE @calling_connection_id                                INTEGER;
DECLARE @commit_percent                                       BIGINT;
DECLARE @continuing                                           VARCHAR ( 1 );
DECLARE @previous_commit_message_displayed_at_loop_counter    BIGINT;
DECLARE @previous_update_message_displayed_at_loop_counter    BIGINT;
DECLARE @lock_count                                           BIGINT;
DECLARE @lock_count_has_increased_at_least_once               VARCHAR ( 1 );
DECLARE @loop_counter                                         BIGINT;
DECLARE @previous_commit_percent                              BIGINT;
DECLARE @previous_lock_count                                  BIGINT;
DECLARE @previous_update_percent                              BIGINT;
DECLARE @row_count                                            BIGINT;
DECLARE @update_percent                                       BIGINT;
DECLARE @batch_description                                    LONG VARCHAR;
 
SET @continuing = 'Y'; -- until proven otherwise
 
SET @calling_connection_id = CAST ( COALESCE ( EVENT_PARAMETER ( 'ConnectionID' ), '0' ) AS INTEGER );
 
IF @calling_connection_id <= 0 THEN
   SET @continuing = 'N'; -- the calling connection id is out of range
END IF;
 
IF @continuing = 'Y' THEN
   SET @row_count = CAST ( COALESCE ( EVENT_PARAMETER ( '@row_count_string' ), '0' ) AS BIGINT );
   IF @row_count <= 0 THEN
      SET @continuing = 'N'; -- the row count is out of range
   END IF;
END IF;
 
IF @continuing = 'Y' THEN
   SET @batch_description = COALESCE ( EVENT_PARAMETER ( '@batch_description' ), '' ); -- optional, may be empty or omitted
END IF;
 
SET @lock_count_has_increased_at_least_once = 'N';
SET @previous_update_message_displayed_at_loop_counter = 0;
SET @previous_commit_message_displayed_at_loop_counter = 0;
SET @previous_lock_count     = 0;
SET @previous_update_percent = 0;
SET @previous_commit_percent = 0;
SET @loop_counter            = 0;
 
WHILE @continuing = 'Y' LOOP
 
   SET @loop_counter = @loop_counter + 1;
 
   SET @lock_count = CONNECTION_PROPERTY ( 'LockCount', @calling_connection_id );
 
   IF @lock_count IS NULL THEN
      SET @continuing = 'N'; -- the calling connection no longer exist
   END IF;
 
   IF @continuing = 'Y' THEN
      IF NOT EXISTS ( SELECT * 
                        FROM rroad_progress_messages_requested
                       WHERE requested_by_connection_id = @calling_connection_id ) THEN
         SET @continuing = 'N'; -- the caller no longer wants progress messages displayed
      END IF;
   END IF;
 
   IF @continuing = 'Y' THEN
 
      CASE
 
         WHEN @lock_count > 0
          AND @lock_count > @previous_lock_count THEN
 
            SET @lock_count_has_increased_at_least_once = 'Y';
 
            SET @update_percent = CAST (
               (   CAST ( @lock_count AS DECIMAL ( 11, 2 ) ) 
                 / CAST ( @row_count  AS DECIMAL ( 11, 2 ) ) ) * 100.00
               AS BIGINT );
 
            IF @update_percent > @previous_update_percent
            OR (     @update_percent > 0
                 AND @update_percent = @previous_update_percent
                 AND @loop_counter >= @previous_update_message_displayed_at_loop_counter + 9
                 AND MOD ( @loop_counter, 10 ) = 0  ) THEN
 
               MESSAGE STRING (  
                  @update_percent,  
                  '% updated (', 
                  rroad_f_number_with_commas ( @lock_count ),  
                  ' rows of ',  
                  rroad_f_number_with_commas ( @row_count ), 
                  @batch_description, 
                  ')' ) TO CONSOLE;
 
               SET @previous_update_message_displayed_at_loop_counter = @loop_counter;
 
            END IF;
                
            SET @previous_update_percent = @update_percent;
 
         WHEN @lock_count > 0
          AND @lock_count < @previous_lock_count
          AND @lock_count_has_increased_at_least_once = 'Y' THEN
 
            SET @commit_percent = CAST (
               (   ( CAST ( @row_count  AS DECIMAL ( 11, 2 ) ) - CAST ( @lock_count AS DECIMAL ( 11, 2 ) ) ) 
                 / CAST ( @row_count  AS DECIMAL ( 11, 2 ) ) ) * 100.00
               AS BIGINT );
 
            IF @commit_percent > @previous_commit_percent
            OR (     @commit_percent > 0
                 AND @commit_percent = @previous_commit_percent
                 AND @loop_counter >= @previous_commit_message_displayed_at_loop_counter + 9
                 AND MOD ( @loop_counter, 10 ) = 0  ) THEN
 
               MESSAGE STRING ( 
                  @commit_percent, 
                  '% committed (', 
                  rroad_f_number_with_commas ( @row_count - @lock_count ), 
                  ' rows of ', 
                  rroad_f_number_with_commas ( @row_count ), 
                  ')' ) TO CONSOLE;
 
               SET @previous_commit_message_displayed_at_loop_counter = @loop_counter;
 
            END IF;
 
            SET @previous_commit_percent = @commit_percent;
 
         WHEN @lock_count = 0
          AND @lock_count_has_increased_at_least_once = 'Y' THEN
 
            MESSAGE STRING ( 'Done: ', rroad_f_number_with_commas ( @row_count ), ' rows' ) TO CONSOLE;
 
            SET @continuing = 'N'; 
 
         ELSE
            -- No action.
 
      END CASE;
 
   END IF;
 
   IF @continuing = 'Y' THEN
      SET @previous_lock_count = @lock_count;
      WAITFOR DELAY '00:00:01';
   END IF;
 
END LOOP;
 
END;

Tip: With a batch size of five million rows the test ran in 18 minutes, down from 19 minutes for a one-million-row batch size, so there is room for experimentation.

Monday, October 17, 2011

Set UPDATE Versus WHERE CURRENT OF Cursor

Question: What is the upper limit on UPDATE?

How many rows can the technique described in Using LockCount To Display UPDATE Progress be used for?

Answer: It depends!

At some point, surely, a set-oriented UPDATE statement affecting millions of rows will fall over in a heap because too many row locks are being held.

Epiphany Time: What about LOCK TABLE? (that ... did ... not ... occur ... to ... me ... until ... this ... moment).
But never mind, let's just pretend that LOCK TABLE can't be used in this particular case (even ... though ... it ... can ... sigh).
Yeah, sure, that's it... let's talk about updating millions of rows but not the whole table, and the whole table can't be locked because other people are using it! (that's my story now, ok?)

Then there's the rollback log... surely a set-oriented UPDATE can't go on forever, can it, bloating up the database file?

Yup, that's where the "rollback log" goes, at the end of the database file. Don't get the rollback log confused with the transaction log which is really a "redo log", the exact opposite of a "rollback log".

OK, there's a limit

Let's accept there's a limit.

Maybe we don't see the dreaded "User 'another user' has the row in 'xxx' locked" message much any more, but let's just accept that we can't let a set-oriented UPDATE run on forever without doing a COMMIT.

What about UPDATE WHERE CURRENT OF Cursor?

A cursor FOR loop does require a bit of effort to split the original set-oriented UPDATE into separate SELECT and UPDATE WHERE CURRENT OF cursor statements, but it has several advantages:

A FOR loop will let you issue a COMMIT every once in a while, say, after a million rows, or five million, whatever,
which in turn allows the FOR loop to update an unlimited number of rows, plus
it's a lot easier to display progress messages than the funky technique described in Using LockCount To Display UPDATE Progress.

Before...

Here's a real world "before" example, a set-oriented UPDATE with a single COMMIT before it was changed into a FOR loop:

UPDATE rroad_group_2_property_pivot
          INNER JOIN rroad_sample_set
             ON rroad_sample_set.sample_set_number = rroad_group_2_property_pivot.sample_set_number
          LEFT OUTER JOIN ( SELECT rroad_group_2_property_pivot.sample_set_number AS sample_set_number,
                                   rroad_group_2_property_pivot.BlockedOn         AS blocking_connection_number,
                                   COUNT(*)                                       AS blocked_connection_count
                              FROM rroad_group_2_property_pivot
                             WHERE rroad_group_2_property_pivot.BlockedOn > 0
                             GROUP BY rroad_group_2_property_pivot.sample_set_number,
                                      rroad_group_2_property_pivot.BlockedOn          ) AS blocking_connection
             ON blocking_connection.sample_set_number          = rroad_group_2_property_pivot.sample_set_number
            AND blocking_connection.blocking_connection_number = rroad_group_2_property_pivot.connection_number
 
   SET rroad_group_2_property_pivot.blocked_connection_count
             = COALESCE ( blocking_connection.blocked_connection_count, 0 ),
 
       rroad_group_2_property_pivot.current_req_status
             = IF TRIM ( COALESCE ( rroad_group_2_property_pivot.ReqStatus, '' ) ) = ''
                  THEN '-'
                  ELSE CASE TRIM ( rroad_group_2_property_pivot.ReqStatus )
                          WHEN 'Idle'              THEN 'Idle'
                          WHEN 'Unscheduled'       THEN 'Waiting for thread'
                          WHEN 'BlockedIO'         THEN 'Waiting for I/O'
                          WHEN 'BlockedContention' THEN 'Waiting for shared resource'
                          WHEN 'BlockedLock'       THEN 'Blocked by lock'
                          WHEN 'Executing'         THEN 'Executing'
                          ELSE TRIM ( rroad_group_2_property_pivot.ReqStatus )
                       END
               ENDIF,
 
       rroad_group_2_property_pivot.cache_satisfaction
             = LESSER ( GREATER (
                  COALESCE (
                     CAST ( ROUND ( CAST ( rroad_group_2_property_pivot.CacheHits AS DECIMAL ( 30, 6 ) )
                                       / CAST ( GREATER ( 1, rroad_group_2_property_pivot.CacheRead ) AS DECIMAL ( 30, 6 ) )
                                       * 100.0,
                                    0 )
                            AS DECIMAL ( 30, 0 ) ),
                     0.0 ),
                  0.0 ), 100.0 ), -- limit the cache_satisfaction to between 0 and 100
 
       rroad_group_2_property_pivot.time_connected
             = IF TRIM ( COALESCE ( rroad_group_2_property_pivot.LoginTime, '' ) ) = ''
                  THEN 0
                  ELSE DATEDIFF ( MILLISECOND,
                          rroad_f_dateadd_msec ( 3,
                                                 rroad_group_2_property_pivot.LoginTime ),
                          rroad_sample_set.sample_finished_at ) -- also called "sample_recorded_at"
               ENDIF,
 
       rroad_group_2_property_pivot.total_waits
             = COALESCE ( rroad_group_2_property_pivot.ReqCountBlockContention, 0 )
                  + COALESCE ( rroad_group_2_property_pivot.ReqCountBlockIO, 0 )
                  + COALESCE ( rroad_group_2_property_pivot.ReqCountBlockLock, 0 )
                  + COALESCE ( rroad_group_2_property_pivot.ReqCountUnscheduled, 0 ),
 
       rroad_group_2_property_pivot.waiting_time
             = COALESCE ( rroad_group_2_property_pivot.ReqTimeBlockContention, 0.0 )
                  + COALESCE ( rroad_group_2_property_pivot.ReqTimeBlockIO, 0.0 )
                  + COALESCE ( rroad_group_2_property_pivot.ReqTimeBlockLock, 0.0 )
                  + COALESCE ( rroad_group_2_property_pivot.ReqTimeUnscheduled, 0.0 ),
 
       rroad_group_2_property_pivot.transaction_running_time
             = IF TRIM ( COALESCE ( rroad_group_2_property_pivot.TransactionStartTime, '' ) ) = ''
                  THEN 0
                  ELSE DATEDIFF ( MILLISECOND,
                          rroad_f_dateadd_msec ( rroad_sample_set.datediff_msec_between_target_and_local,
                                                 rroad_group_2_property_pivot.TransactionStartTime ),
                          rroad_sample_set.sample_finished_at ) -- also called "sample_recorded_at"
               ENDIF,
 
       rroad_group_2_property_pivot.time_since_last_request
             = IF TRIM ( COALESCE ( rroad_group_2_property_pivot.LastReqTime, '' ) ) = ''
                  THEN 0
                  ELSE DATEDIFF ( MILLISECOND,
                          rroad_f_dateadd_msec ( rroad_sample_set.datediff_msec_between_target_and_local,
                                                 rroad_group_2_property_pivot.LastReqTime ),
                          rroad_sample_set.sample_finished_at ) -- also called "sample_recorded_at"
               ENDIF,
 
       rroad_group_2_property_pivot.index_satisfaction
             = LESSER ( GREATER (
                  COALESCE (
                     CAST ( ROUND ( CAST ( rroad_group_2_property_pivot.IndLookup
                                              - LESSER ( rroad_group_2_property_pivot.IndLookup, rroad_group_2_property_pivot.FullCompare )
                                           AS DECIMAL ( 30, 6 ) )
                                       / CAST ( GREATER ( 1, rroad_group_2_property_pivot.IndLookup )
                                                AS DECIMAL ( 30, 6 ) )
                                       * 100.0,
                                    0 )
                            AS DECIMAL ( 30, 0 ) ),
                     0.0 ),
                  0.0 ), 100.0 );  -- limit the index_satisfaction to between 0 and 100
 
COMMIT;

...and After

Here's the same update after it was changed into a FOR loop as follows:

The CHECKPOINT and SELECT SYSTAB.count statements on lines 4 through 9 are extras, added to facilitate the "Row x of y updated" MESSAGE statement on line 141.
The SELECT on lines 14 through 32 was formed by taking every table_name.column_name referenced in the original UPDATE SET clause and turning it into a local FOR loop variable using the AS @column_name alias specification.
The FROM clause on lines 33 through 44 was copied directly from the original set-oriented UPDATE statement.
The UPDATE SET clause on lines 52 through 131 was edited to replace every table_name.column_name with the corresponding @column_name defined in the SELECT.
The IF statement on lines 135 through 148 issues a COMMIT for every one million rows updated, as well as producing a "Row x of y updated" MESSAGE. The code for the function rroad_f_number_with_commas() isn't shown here, but all it does is insert commas in big numbers like 9,999,999.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

DECLARE @rroad_group_2_property_pivot_row_count      BIGINT;
DECLARE @fetch_counter                               BIGINT;
 
CHECKPOINT;
 
SELECT SYSTAB.count
  INTO @rroad_group_2_property_pivot_row_count
  FROM SYSTAB
 WHERE SYSTAB.table_name = 'rroad_group_2_property_pivot';
 
SET @fetch_counter = 0;
 
FOR f_fetch AS c_fetch NO SCROLL CURSOR FOR
SELECT blocking_connection.blocked_connection_count              AS @blocked_connection_count,
       rroad_group_2_property_pivot.ReqStatus                    AS @ReqStatus,
       rroad_group_2_property_pivot.CacheHits                    AS @CacheHits,
       rroad_group_2_property_pivot.CacheRead                    AS @CacheRead,
       rroad_group_2_property_pivot.LoginTime                    AS @LoginTime,
       rroad_sample_set.sample_finished_at                       AS @sample_finished_at,
       rroad_group_2_property_pivot.ReqCountBlockContention      AS @ReqCountBlockContention,
       rroad_group_2_property_pivot.ReqCountBlockIO              AS @ReqCountBlockIO,
       rroad_group_2_property_pivot.ReqCountBlockLock            AS @ReqCountBlockLock,
       rroad_group_2_property_pivot.ReqCountUnscheduled          AS @ReqCountUnscheduled,
       rroad_group_2_property_pivot.ReqTimeBlockContention       AS @ReqTimeBlockContention,
       rroad_group_2_property_pivot.ReqTimeBlockIO               AS @ReqTimeBlockIO,
       rroad_group_2_property_pivot.ReqTimeBlockLock             AS @ReqTimeBlockLock,
       rroad_group_2_property_pivot.ReqTimeUnscheduled           AS @ReqTimeUnscheduled,
       rroad_group_2_property_pivot.TransactionStartTime         AS @TransactionStartTime,
       rroad_sample_set.datediff_msec_between_target_and_local   AS @datediff_msec_between_target_and_local,
       rroad_group_2_property_pivot.LastReqTime                  AS @LastReqTime,
       rroad_group_2_property_pivot.IndLookup                    AS @IndLookup,
       rroad_group_2_property_pivot.FullCompare                  AS @FullCompare
  FROM rroad_group_2_property_pivot
             INNER JOIN rroad_sample_set
                ON rroad_sample_set.sample_set_number = rroad_group_2_property_pivot.sample_set_number
             LEFT OUTER JOIN ( SELECT rroad_group_2_property_pivot.sample_set_number AS sample_set_number,
                                      rroad_group_2_property_pivot.BlockedOn         AS blocking_connection_number,
                                      COUNT(*)                                       AS blocked_connection_count
                                 FROM rroad_group_2_property_pivot
                                WHERE rroad_group_2_property_pivot.BlockedOn > 0
                                GROUP BY rroad_group_2_property_pivot.sample_set_number,
                                         rroad_group_2_property_pivot.BlockedOn          ) AS blocking_connection
                ON blocking_connection.sample_set_number          = rroad_group_2_property_pivot.sample_set_number
               AND blocking_connection.blocking_connection_number = rroad_group_2_property_pivot.connection_number
FOR UPDATE
DO
 
   SET @fetch_counter = @fetch_counter + 1;
 
   UPDATE rroad_group_2_property_pivot
 
      SET rroad_group_2_property_pivot.blocked_connection_count
                = COALESCE ( @blocked_connection_count, 0 ),
 
          rroad_group_2_property_pivot.current_req_status
                = IF TRIM ( COALESCE ( @ReqStatus, '' ) ) = ''
                     THEN '-'
                     ELSE CASE TRIM ( @ReqStatus )
                             WHEN 'Idle'              THEN 'Idle'
                             WHEN 'Unscheduled'       THEN 'Waiting for thread'
                             WHEN 'BlockedIO'         THEN 'Waiting for I/O'
                             WHEN 'BlockedContention' THEN 'Waiting for shared resource'
                             WHEN 'BlockedLock'       THEN 'Blocked by lock'
                             WHEN 'Executing'         THEN 'Executing'
                             ELSE TRIM ( @ReqStatus )
                          END
                  ENDIF,
 
          rroad_group_2_property_pivot.cache_satisfaction
                = LESSER ( GREATER (
                     COALESCE (
                        CAST ( ROUND ( CAST ( @CacheHits AS DECIMAL ( 30, 6 ) )
                                          / CAST ( GREATER ( 1, @CacheRead ) AS DECIMAL ( 30, 6 ) )
                                          * 100.0,
                                       0 )
                               AS DECIMAL ( 30, 0 ) ),
                        0.0 ),
                     0.0 ), 100.0 ), -- limit the cache_satisfaction to between 0 and 100
 
          rroad_group_2_property_pivot.time_connected
                = IF TRIM ( COALESCE ( @LoginTime, '' ) ) = ''
                     THEN 0
                     ELSE DATEDIFF ( MILLISECOND,
                             rroad_f_dateadd_msec ( 3,
                                                    @LoginTime ),
                             @sample_finished_at ) -- also called "sample_recorded_at"
                  ENDIF,
 
          rroad_group_2_property_pivot.total_waits
                = COALESCE ( @ReqCountBlockContention, 0 )
                     + COALESCE ( @ReqCountBlockIO, 0 )
                     + COALESCE ( @ReqCountBlockLock, 0 )
                     + COALESCE ( @ReqCountUnscheduled, 0 ),
 
          rroad_group_2_property_pivot.waiting_time
                = COALESCE ( @ReqTimeBlockContention, 0.0 )
                     + COALESCE ( @ReqTimeBlockIO, 0.0 )
                     + COALESCE ( @ReqTimeBlockLock, 0.0 )
                     + COALESCE ( @ReqTimeUnscheduled, 0.0 ),
 
          rroad_group_2_property_pivot.transaction_running_time
                = IF TRIM ( COALESCE ( @TransactionStartTime, '' ) ) = ''
                     THEN 0
                     ELSE DATEDIFF ( MILLISECOND,
                             rroad_f_dateadd_msec ( @datediff_msec_between_target_and_local,
                                                    @TransactionStartTime ),
                             @sample_finished_at ) -- also called "sample_recorded_at"
                  ENDIF,
 
          rroad_group_2_property_pivot.time_since_last_request
                = IF TRIM ( COALESCE ( @LastReqTime, '' ) ) = ''
                     THEN 0
                     ELSE DATEDIFF ( MILLISECOND,
                             rroad_f_dateadd_msec ( @datediff_msec_between_target_and_local,
                                                    @LastReqTime ),
                             @sample_finished_at ) -- also called "sample_recorded_at"
                  ENDIF,
 
          rroad_group_2_property_pivot.index_satisfaction
                = LESSER ( GREATER (
                     COALESCE (
                        CAST ( ROUND ( CAST ( @IndLookup
                                                 - LESSER ( @IndLookup, @FullCompare )
                                              AS DECIMAL ( 30, 6 ) )
                                          / CAST ( GREATER ( 1, @IndLookup )
                                                   AS DECIMAL ( 30, 6 ) )
                                          * 100.0,
                                       0 )
                               AS DECIMAL ( 30, 0 ) ),
                        0.0 ),
                     0.0 ), 100.0 )  -- limit the index_satisfaction to between 0 and 100
 
    WHERE CURRENT OF c_fetch;
 
   IF @fetch_counter = 1
   OR MOD ( @fetch_counter, 1000000 ) = 0
   OR @fetch_counter = @rroad_group_2_property_pivot_row_count THEN
 
      COMMIT;
 
      MESSAGE STRING (
         'Row ',
         rroad_f_number_with_commas ( @fetch_counter ),
         ' of ',
         rroad_f_number_with_commas ( @rroad_group_2_property_pivot_row_count ),
         ' updated.' ) TO CONSOLE;
 
   END IF;
 
END FOR;
 
COMMIT;

Here's what the FOR loop displayed for a test that began at 16:01:48:

I. 10/11 16:04:08. Row 1 of 6,133,762 updated.
I. 10/11 16:13:20. Row 1,000,000 of 6,133,762 updated.
I. 10/11 16:22:42. Row 2,000,000 of 6,133,762 updated.
I. 10/11 16:31:00. Row 3,000,000 of 6,133,762 updated.
I. 10/11 16:48:38. Row 4,000,000 of 6,133,762 updated.
I. 10/11 17:01:57. Row 5,000,000 of 6,133,762 updated.
I. 10/11 17:15:44. Row 6,000,000 of 6,133,762 updated.
I. 10/11 17:16:51. Row 6,133,762 of 6,133,762 updated.

The time between 16:01:48 and 16:04:08 was taken up by the FOR SELECT before the first row was fetched, and the rest of the time was spent doing the updates.

Total time for the UPDATE WHERE CURRENT OF cursor test...

75 minutes

And for the set-oriented UPDATE?

15 minutes!

Safe to say, cursor loops suck when it comes to performance... and this cursor loop was inside a stored procedure!

What would a client-server fetch loop have been like, with every row being schlepped across the Great ODBC Divide? or JDBC? Or, heaven forfend, LINQ?

Set-oriented UPDATE rules!

But what about the limit? What about the commits? More on this later...

Friday, October 14, 2011

The fRiDaY File - Fuji pricing is yet to be determined

Latest SQL Anywhere EBF: 12.0.1.3457... The Fuji EBF!

The three asterisks "***" show what's new since September 21. Only EBFs for the three fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.

Current builds for the active platforms...

Linux x86/x64    12.0.1.3457 EBF ***             12 Oct 2011 ***
                 11.0.1.2645 EBF                 29 Jul 2011
                 10.0.1.4239 EBF                 17 Jun 2011

Mac OS           12.0.1.3352 EBF                 26 May 2011
                 11.0.1.2449 EBF                 29 Jun 2010
                 10.0.1.4042 EBF                 01 Oct 2010

Windows x86/x64  12.0.1.3457 EBF ***             12 Oct 2011 ***
                 11.0.1.2680 EBF ***             04 Oct 2011 ***
                 10.0.1.4239 EBF                 17 Jun 2011

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX Itanium    11.0.1.2664 EBF                 24 Aug 2011    
                 10.0.1.3777 EBF                 14 Oct 2008

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

IBM AIX          12.0.1 GA Upgrade from 12.0.0   15 Mar 2011
                 11.0.1.2637 EBF                 26 Jul 2011

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Solaris SPARC    12.0.1 GA Upgrade from 12.0.0   15 Mar 2011
                 11.0.1.2632 EBF                 07 Jul 2011
                 10.0.1.4239 EBF                 17 Jun 2011

Solaris x64      11.0.1.2645 EBF                 05 Aug 2011
                 10.0.1.3870 EBF                 01 Apr 2009

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

Thursday, October 13, 2011

The Thursday Quote - Barbara Liskov, Valerie Barr

"... the ingredients that have to be in place in order to get an ah hah moment. You have to be working on a problem, but also have to be able to have "off time" so that the brain can work on the back burner."

Barbara Liskov as reported by Valerie Barr in Barbara Liskov keynote - Grace Hopper Conference, from BLOG@CACM October 2, 2010

There is no "wider context" today because that quote stands on its own, but Valerie Barr did report something else Barbara Liskov said that's worth repeating:

After providing the background information, Liskov talked about her technical work which ultimately led to the Turing Award. Much of her work was motivated by interest in program methodology and the questions of how programs should be designed and how programs should be structured. So after receiving the Turing Award, she went back and reread the old literature, discovering anew that there is great material in old papers and that her students were unaware of it. So she is now pointing people to these papers and encouraging people to read them.

For example, three key papers she cited are:

Edsger Dijkstra, Go To Considered Harmful, Communications of the ACM, Vol. 11, No. 3, March 1968, pp. 147-148

Niklaus Wirth, Program Development by Stepwise Refinement, Communications of the ACM, Vol. 14, No. 4, April 1971, pp. 221-227

David Parnas, Information Distribution Aspects of Design Methodology, IFIP Congress, 1971

Next week: Linus Torvalds

Wednesday, October 12, 2011

DocCommentXchange 4.1

Apparently, the DocCommentXchange website (DCX) now works to deliver SQL Anywhere documentation to the iPhone (yeah, seriously... but wait, there's more).

DCX now works on the iPad

...which makes a whole lot more sense:

What really makes sense, however, are the other improvements, like the new "Scope:" button which lets you pick which "books" you're interested in:

Set scope

That's something you don't get in the regular Compiled HTML Help that comes with SQL Anywhere.

Another feature you don't get in the regular Help is the fact that keyword matches on the Index tab jump to subtopics down inside a main topic; the regular Help just opens up the topic at the top and you have to scroll down yourself.

Jumping directly to subtopics might not be new in DCX, but it's new to me... I just noticed it.

Here are some other features which are new to DocCommentXchange 4.1:

"Print this topic" versus "Print this topic and all 258 subtopics",
"Change to German", French, Japanese, Chinese, and
"Share this page on Twitter", Facebook and email.

The behavior of the left-hand pane is also supposed to be improved.

Besides speed (it will always be faster), the only remaining advantage that the regular Help has over DCX might be the ability to highlight Search words in the topic text. This is especially useful when trying to find something in those long What's New feature lists.

...and a little bird told me it might - No promises! - be coming soon to DCX!

About that speed thing...

The DCX website is pretty fast now, well within the usable range...

...but iPhone? Twitter? It must be an age thing.

Monday, October 10, 2011

Fix one bug, find another

Gosh, I hate testing.

Sometimes it's more fun to think of it as a "Bug Hunt" like in Robert A. Heinlein's Starship Troopers, or better yet, James Cameron's Aliens:

Hudson: Is this gonna be a standup fight, sir, or another bughunt?
Gorman: All we know is that there's still no contact with the colony, and that a xenomorph may be involved.
Frost: Excuse me sir, a-a what?
Gorman: A xenomorph.
Hicks: It's a bughunt.

But the fun wears thin after a while as you turn up new bugs faster than you can fix old ones.

So now you know

Now you know why there hasn't been a new version of Foxhound yet.

And why there isn't a real blog post today, just this "fluffy puppy" post... because some bugs are worth talking about but not any of these.

Friday, October 7, 2011

The fRiDaY File - Multi-Tenant Architecture

Fuji Watch: There it is again

There it is again, that bold claim about how the "SQL Anywhere OnDemand Edition will allow a small team to host tens-of-thousands of separate, isolated databases".

Not hundreds of databases, not thousands of databases, but tens-of-thousands. One database per customer of your very, very, VERY successful Software-as-a-Service company.

YOUR company. Running YOUR software on THEIR database (the customer's own copy of YOUR database) somewhere out in YOUR piece of the cloud.

But wait, there's more!

Suppose YOUR customer wants to keep using YOUR software but wants to move THEIR database inside the walls of THEIR own building (Try THAT with Oracle's Database Cloud Service or Salesforce.com's Database-as-a-Service).

With ~~Fuji~~ the SQL Anywhere OnDemand Edition, each customer's data is stored inside a separate database.db file. That's one file per customer, which can be moved from the Linux box in the cloud to the Windows server in their building.

Or from Windows to Linux, it doesn't matter, SQL Anywhere database files are binary portable across platforms, even between big-endian and little-endian hardware (which, seriously, I'm not kidding, has something to do with how you eat soft-boiled eggs).

And even if they leave their database with you, it's still a physically-separate database, so it's easy to show an auditor that THEIR data is completely separate from OTHER PEOPLE's data, that "multi-tenant" doesn't mean you've got your own bedroom but have to share the bathroom and kitchen...

"Ewwww! What's THAT in the sink?!? Who's THAT sleeping on the couch?"

But first...

...but first: There's that bold claim again, that "tens-of-thousands of databases" claim.

Which brings up the question,

Will it work?

Not for five databases, not for a hundred... but for tens-of-thousands.

Way too early to say, it isn't even Beta yet. While we're waiting, however, there's a new webcast coming up:

Webcast: Introducing Sybase's New Cloud Data Management Solution

Discover Sybase’s new cloud data management solution SQL Anywhere OnDemand Edition, code-named "Fuji", designed specifically for the needs of the ISV market. Many of the recent cloud data platforms have been focused on end-users and providing "infinite" scalability of a single database. SQL Anywhere OnDemand Edition is different. This new data management technology is focused on the ISV and providing scalability for the number of databases in the system.

Come learn how SQL Anywhere OnDemand Edition will allow a small team to host tens-of-thousands of separate, isolated databases in a secure, flexible, and manageable multi-tenant environment.

Date: Wednesday, October 19th 2011

Times: 10am & 2pm EDT / 3pm & 7pm GMT / 4pm & 8pm CET

Duration: 1 hour

Register

Thursday, October 6, 2011

The Thursday Quote - Srikanth Nadhamuni, Vince Beiser

"Technology doesn't scale that elegantly. The problems you have at 100 million are different from problems you have at 500 million."
Srikanth Nadhamuni, Head of Technology for Aadhaar at the Unique Identification (UID) Authority of India, as quoted in Massive Biometric Project Gives Millions of Indians an ID by Vince Beiser, from Wired magazine September 2011

So you think your job is hard?

You feel good at the end of the day because you fixed that bug, got those to-do items done, gave as good as you got at the daily status meeting?

Well, give a thought to Srikanth Nadhamuni, whose goal is to "issue identification numbers linked to the fingerprints and iris scans of every single person in India" as Head of Technology for the Aadhaar project.

That's 1,200,000,000 people, give or take, as Vince Beiser explains in his article about Aadhaar; here's an excerpt to give a wider context:

The unprecedented scale of Aadhaar's data will make managing it extraordinarily difficult. One of Nadhamuni's most important tasks is de-duplication, ensuring that each record in the database is matched to one and only one person. That's crucial to keep scammers from enrolling multiple times under different names to double-dip on their benefits. To guard against that, the agency needs to check all 10 fingers and both irises of each person against those of everyone else. In a few years, when the database contains 600 million people and is taking in 1 million more per day, Nadhamuni says, they'll need to run about 14 billion matches per second. "That's enormous," he says.

Coping with that load takes more than just adding extra servers. Even Nadhamuni isn't sure how big the ultimate server farm will be. He isn't even totally sure how to work it yet. "Technology doesn't scale that elegantly," he says. "The problems you have at 100 million are different from problems you have at 500 million." And Aadhaar won't know what those problems are until they show up. As the system grows, different components slow down in different ways. There might be programming flaws that delay each request by an amount too tiny to notice when you're running a small number of queries—but when you get into the millions, those tiny delays add up to a major issue. When the system was first activated, Nadhamuni says, he and his team were querying their database, created with the ubiquitous software MySQL, about 5,000 times a day and getting answers back in a fraction of a second. But when they leaped up to 20,000 queries, the lag time rose dramatically. The engineers eventually figured out that they needed to run more copies of MySQL in parallel; software, not hardware, was the bottleneck. "It's like you've got a car with a Hyundai engine, and up to 30 miles per hour it does fine," Nadhamuni says. "But when you go faster, the nuts and bolts fall off and you go, whoa, I need a Ferrari engine. But for us, it's not like there are a dozen engines and we can just pick the fastest one. We are building these engines as we go along."

Next week: Barbara Liskov, Valerie Barr

Wednesday, October 5, 2011

Let's play "Gotcha!" - New Season

Welcome to a new season of "Gotcha!", the self-testing quiz game for developers who work with SQL Anywhere.

No warmup question this time, and the rules have changed...

You will be shown one example and asked two skill-testing questions about it:
1. What did the author want this code to do?
2. What was the "Gotcha!" moment? In other words, what did the code actually do?
For bonus points,
1. Why?

One other rule is still the same, though...

No computers!

You have to answer all the questions without looking anything up, and without running any actual tests. This is a test of your SQL Anywhere knowledge, not your prowess at Googling the docs.

Memo to the Alphas: Question 1 is intended to bell curve you folks out of competition. Not the Deltas or the Gammas... even you Gammas will get Question 1 right if you work at it. But you Alphas and Betas don't see how Questions 1 and 2 can possibly have different answers, do you? "That's what the code does, how could the author expect anything else?"... it's why you guys never score 100% on exams, you litigate the questions :)

OK, here we go!

Question 1: What did the author want this code to do?

CREATE PROCEDURE p() RESULT ( c VARCHAR ( 100 ) )
BEGIN
   COMMIT;
   SELECT 'Hello, world' AS c;
END;
 
BEGIN
   DECLARE LOCAL TEMPORARY TABLE t ( c VARCHAR ( 100 ) );
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;

Take your time, think about it...

OK, here's the answer, here's what the author wanted to see from the SELECT * FROM t:

CREATE PROCEDURE p() RESULT ( c VARCHAR ( 100 ) )
BEGIN
   COMMIT;
   SELECT 'Hello, world' AS c;
END;
 
BEGIN
   DECLARE LOCAL TEMPORARY TABLE t ( c VARCHAR ( 100 ) );
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;
 
c
'Hello, world'

Question 2: What was the "Gotcha!" moment?

In other words, what did the code actually do?

Let's have another look at the code...

CREATE PROCEDURE p() RESULT ( c VARCHAR ( 100 ) )
BEGIN
   COMMIT;
   SELECT 'Hello, world' AS c;
END;
 
BEGIN
   DECLARE LOCAL TEMPORARY TABLE t ( c VARCHAR ( 100 ) );
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;

Think about it...

Gotcha!

CREATE PROCEDURE p() RESULT ( c VARCHAR ( 100 ) )
BEGIN
   COMMIT;
   SELECT 'Hello, world' AS c;
END;
 
BEGIN
   DECLARE LOCAL TEMPORARY TABLE t ( c VARCHAR ( 100 ) );
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;
 
Could not execute statement.
COMMIT/ROLLBACK not allowed within atomic operation
SQLCODE=-267, ODBC 3 State="42000"

For bonus points: Why?

Hint: What you see is what you get... there are no triggers involved. Heck, there are no real tables involved at all... the code you see here was the only thing run on a brand-new SQL Anywhere 12.0.1 database.

And just in case you're jonesing for the docs, here's another hint: By default BEGIN blocks are not atomic, which means neither are procedure calls.

Plus, it doesn't help if table t is declared NOT TRANSACTIONAL. To drive those points home, this code gives the same Gotcha! result:

CREATE PROCEDURE p()
BEGIN NOT ATOMIC
   COMMIT;
   SELECT 'Hello, world' AS c;
END;
 
BEGIN NOT ATOMIC
   DECLARE LOCAL TEMPORARY TABLE t ( c  LONG VARCHAR ) NOT TRANSACTIONAL;
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;
 
Could not execute statement.
COMMIT/ROLLBACK not allowed within atomic operation
SQLCODE=-267, ODBC 3 State="42000"

Stumped? Take your time...

You want another hint?

OK: If you take out the COMMIT, it does what the author wanted it to do...

CREATE PROCEDURE p() RESULT ( c VARCHAR ( 100 ) )
BEGIN
   SELECT 'Hello, world' AS c;
END;
 
BEGIN
   DECLARE LOCAL TEMPORARY TABLE t ( c VARCHAR ( 100 ) );
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;
 
c
'Hello, world'

And no, it's not a bug, it's a feature! ...it's supposed to work this way!

Time's Up!

Thank you for playing "Gotcha!"

See you next time, good night everyone!

Monday, October 3, 2011

Dump and Continue

Question: How do I capture information about a SQL exception inside a stored procedure and then roll back and continue processing?

A short (and incomplete) answer can be found here:

Here's a longer answer:

Use an extra BEGIN END block to isolate the SQL code that may raise an exception that is to be handled.
Use an EXCEPTION handler inside that BEGIN block to get control when the exception occurs.
Use SELECT INTO to capture the values of SQLCODE, SQLSTATE and ERRORMSG().
Use SELECT * to capture all the data from a row in a table without requiring maintenance effort when the schema changes.
Use UNLOAD SELECT TO VARIABLE to dump the row, including binary data, into a single readable character string.
Use TRIGGER EVENT to start a separate process with a separate connection which in turn will allow a separate database transaction.
Use EVENT_PARAMETER to pass all the captured data to the event process.
Use INSERT and COMMIT inside the EVENT process to save the captured data to the database.
Use ROLLBACK in the EXCEPTION handler to roll back the failing transaction.
Let the EXCEPTION handler end normally (no RESIGNAL statement) to continue processing.

Sounds like a lot of work, doesn't it?

Well, yes, there IS some work involved

"Dump and Continue" is harder than "Crap Out and Stop". You know what I'm talking about: Crap Out and Stop means don't do anything, just let the exception bubble up into the application and throw a Java traceback in the user's face.

Or worse, do less than nothing, don't even check for an error back in the application, just continue processing with a partially complete transaction, maybe commit, maybe roll back... there's an infinity of Good, Bad and Ugly things applications can do when stuff happens.

But why would anyone want "Dump and Continue"?

With some applications, continuing with the next step or task or operation is more important than stopping to ask the user what to do when a problem occurs. Some applications don't even have users in the normal sense: they aren't interactive but instead run in the background or as scheduled tasks. Their users might want to investigate later on what went wrong with the 1% that didn't get done, but they sure don't want the other 99% to get held up by the 1%.

Other applications ~~go way out where the buses don't run~~ are further out on the continuum that leads to nonstop processing. These applications run in the foreground and they've got interactive users, all right, but it's a special kind of user, one who absolutely positively relies on the application recovering immediately from whatever glitch just happened and pressing on with the job at hand. They don't care about the Dump option, just Continue, and in their world, the world of airline pilots and heart surgeons, "Stop" means "Die".

But enough with the proselytizing, this isn't an article about life-critical applications, it's about Dump and Continue, so

Show me the code!

First, here's a procedure with a problem; can you tell me what that problem is?

"No, wait, last week was Quiz Week, let's just get on with it!"

OK, OK, here it is...

CREATE PROCEDURE p()
BEGIN
 
DECLARE @sqlcode                 INTEGER;
DECLARE @sqlstate                VARCHAR ( 5 );
DECLARE @errormsg                VARCHAR ( 32767 );
DECLARE @exception_dump          LONG VARCHAR;
 
FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR
SELECT pkey  AS @pkey,
       data1 AS @data1
  FROM t
FOR READ ONLY
DO
 
   UPDATE t SET counter = counter + 1 WHERE pkey = @pkey;
 
   BEGIN
 
      UPDATE t SET data2 = @data1 + 1 WHERE pkey = @pkey;
 
   EXCEPTION WHEN OTHERS THEN
 
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
 
      ROLLBACK;
 
      UNLOAD SELECT 'Table t', * FROM t WHERE pkey = @pkey
        INTO VARIABLE @exception_dump;
 
      CALL record_exception ( 
         STRING ( 'SQLCODE = ', @sqlcode, 
                  ', SQLSTATE = ', @sqlstate, 
                  ', ERRORMSG() = ', @errormsg ),
         @exception_dump );
 
   END;
 
   COMMIT;
 
END FOR;
 
END;

The FOR loop on lines 9 through 42 steps through each row in table t, performing two UPDATE statements and a COMMIT (lines 16, 20 and 40). In other words, each row in t is updated twice as a single transaction.

Of course nobody in the real world would use two UPDATE statements where one would do the job, but that's not the point... the point is to demonstrate how one multi-update transaction can be rolled back without affecting the other transactions.

The UPDATE on line 20 has been singled out for special exception handling. That is done with the BEGIN statement on line 18, the corresponding END on line 38 and the EXCEPTION handler on lines 22 through 36. Only exceptions that occur in the UPDATE on line 20 will be handled by the EXCEPTION handler on line 22; any exceptions that occur outside the inner BEGIN block (say, a problem in the UPDATE on line 16) will be bubbled up to the caller because the procedure p doesn't have an EXCEPTION handler for its BEGIN block on lines 2 through 44.

The SELECT on lines 24 and 25 captures the SQLCODE, SQLSTATE and ERRORMSG() values as they stood when the exception was raised. These values must be captured as soon as the EXCEPTION handler starts executing, using a single SELECT INTO statement so the values aren't changed by other statements.

The ROLLBACK on line 27 backs out all the changes made during the current transaction. It some situations, it's important to get the ROLLBACK done right away, like it's done here. In other cases, the ROLLBACK is done later so that more diagnostic information can be gathered; an example of that is shown later in this article. Whether a ROLLBACK is needed at all depends on the application; the question at the top of this article asked for one, so it's here.

The UNLOAD on lines 29 and 30 is the "Dump" part of "Dump and Continue"... it captures an image of the row in t but instead of writing it to a text file like UNLOAD usually does, it puts the data into a single local LONG VARCHAR variable using the new INTO VARIABLE clause.

OK, so it was introduced in SQL Anywhere 11 which makes it "relatively new"... but UNLOAD INTO VARIABLE is still new to lots of us :)

The CALL on lines 32 through 36 passes two strings to the record_exception procedure that will be shown later. The first string contains diagnostic data about the exception and the second is the dump.

The code between line 36 (the CALL) and line 38 (the END) is the secret behind the "Continue" part of "Dump and Continue".

What code? No code... that's the point, the exception handler does nothing else before the END, there's no RETURN statement, no RESIGNAL, nothing. In SQL Anywhere an exception handler does only what you tell it to do, and once it finishes what you've told it to do, if you haven't told it to go somewhere else, it carries on with the next statement after the END.

And the next statement after the END is the COMMIT on line 40... which does nothing in the case of an exception because the handler did a ROLLBACK.

After the COMMIT comes the END FOR on line 42, which means the FOR loop keeps on running until table t runs out of rows, no matter how many times the EXCEPTION handler on line 22 gets control.

Here's the code for the record_exception procedure that p-the-procedure-with-a-problem calls from the inner EXCEPTION handler:

CREATE PROCEDURE record_exception ( 
   IN @diagnostic_text LONG VARCHAR,
   IN @exception_dump  LONG VARCHAR DEFAULT '' )
BEGIN
 
DECLARE @exception_diagnostic  LONG VARCHAR;
DECLARE @sqlcode               INTEGER;
DECLARE @sqlstate              VARCHAR ( 5 );
DECLARE @errormsg              VARCHAR ( 32767 );
 
SET @exception_diagnostic = STRING ( 
   CURRENT TIMESTAMP, 
   ' ',  
   CONNECTION_PROPERTY ( 'Number' ),  
   ' ',  
   @diagnostic_text );
 
IF COALESCE ( @exception_dump, '' ) <> '' THEN
 
   TRIGGER EVENT record_exception ( 
      @p_exception_diagnostic = @exception_diagnostic,
      @p_exception_dump       = @exception_dump );
 
ELSE 
 
   TRIGGER EVENT record_exception ( 
      @p_exception_diagnostic = @exception_diagnostic );
 
END IF;
 
EXCEPTION WHEN OTHERS THEN
 
   SELECT SQLCODE, SQLSTATE, ERRORMSG() 
     INTO @sqlcode, @sqlstate, @errormsg;
 
   MESSAGE STRING ( 
      CURRENT TIMESTAMP, 
      ' EXCEPTION in record_exception: ',
      ', SQLCODE = ', @sqlcode,  
      ', SQLSTATE = ', @sqlstate,  
      ', ERRORMSG() = ', @errormsg ) 
      TO CONSOLE;
 
END;

The SET on lines 11 through 16 adds more data to the @diagnostic_text passed from the caller. If other common data is important to diagnosing exceptions in your application, this is where you can capture it so you don't have to include it everywhere this procedure is called; e.g., the reference to CURRENT TIMESTAMP and the call to CONNECTION_PROPERTY() shown here.

The IF THEN ELSE on lines 18 through 29 handles the fact that the input parameter @exception_dump is optional, both for this procedure and for the event it triggers.

The two TRIGGER EVENT statements starting on lines 20 and 26 use the special "trigger-parameter-name = expression" syntax that is required to pass string values to the event. Just strings, but that's OK since pretty much everything can be converted to a string.

The EXCEPTION handler on lines 31 through 42 handles any exception that might occur in the record_exception procedure itself, as follows:

capture the SQLCODE, SQLSTATE and ERRORMSG() values,
use MESSAGE TO CONSOLE to write a diagnostic message to the database server console log, and
carry on as if nothing bad happened; i.e., return to the caller.

In other words, it's a dumbed-down version of "Dump and Continue"... the last thing any application needs is for an error in the error handler to get in the way.

Here's the code for the EVENT that does the actual work of recording the exception, plus the two tables it uses:

CREATE TABLE exception_diagnostic (
   exception_id          BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   exception_diagnostic  LONG VARCHAR NOT NULL );
 
CREATE TABLE exception_dump (
   exception_id          BIGINT NOT NULL,
   dump_id               BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   dump_inserted_at      TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
   dump                  LONG VARCHAR NOT NULL ); 
 
CREATE EVENT record_exception
HANDLER BEGIN
 
DECLARE @p_exception_diagnostic  LONG VARCHAR;
DECLARE @p_exception_dump        LONG VARCHAR;
DECLARE @sqlcode                 INTEGER;
DECLARE @sqlstate                VARCHAR ( 5 );
DECLARE @errormsg                VARCHAR ( 32767 );
 
SET @p_exception_diagnostic = COALESCE ( EVENT_PARAMETER ( '@p_exception_diagnostic' ), '[NULL]' );
 
INSERT exception_diagnostic ( exception_diagnostic ) VALUES ( @p_exception_diagnostic );
 
COMMIT;
 
SET @p_exception_dump = COALESCE ( EVENT_PARAMETER ( '@p_exception_dump' ), '' );
 
IF @p_exception_dump <> '' THEN
 
   INSERT exception_dump ( exception_id, dump ) VALUES ( @@IDENTITY, @p_exception_dump );
 
   COMMIT;
 
END IF;
 
EXCEPTION WHEN OTHERS THEN
 
   SELECT SQLCODE, SQLSTATE, ERRORMSG() 
     INTO @sqlcode, @sqlstate, @errormsg;
 
   MESSAGE STRING ( 
      CURRENT TIMESTAMP, 
      ' EXCEPTION in event record_exception: ',
      ', SQLCODE = ', @sqlcode,  
      ', SQLSTATE = ', @sqlstate,  
      ', ERRORMSG() = ', @errormsg ) 
      TO CONSOLE;
 
END;

The CREATE TABLE statements on lines 1 through 9 define separate tables for the diagnostic and dump strings. Using two tables instead of one makes it possible to create more than one dump for a single exception, and makes it easy to independently delete data from one or the other table after, say, a diagnostic runaway... those might not be compelling reasons but they're good enough for government work :)

The SET statement on line 20 uses the funky EVENT_PARAMETER function make up for the fact that the CREATE EVENT statement doesn't have a parameter list like CREATE PROCEDURE. The result is that the local variable @p_exception_diagnostic gets the first string passed via the TRIGGER EVENT shown earlier.

The INSERT on line 22 takes that string and shoves it into the first table, and the COMMIT makes sure that row sticks around no matter what happens later... another reason for two tables, perhaps.

The SET on line 26 copies the second string parameter into a local variable, and if there is something in it the INSERT and COMMIT on lines 30 and 32 shoves that string into the second table and commits it.

The important thing about this event, and the ONLY reason it exists, is so the COMMIT statements on lines 24 and 32 run on a separate connection from the rest of the application.

Does it work?

Yes, it works, and here's how...

First, so you don't have to scroll up and down so much, here's the procedure-with-a-problem again, followed by some data for it to use and some code to call it and to show what happened:

CREATE PROCEDURE p()
BEGIN
 
DECLARE @sqlcode                 INTEGER;
DECLARE @sqlstate                VARCHAR ( 5 );
DECLARE @errormsg                VARCHAR ( 32767 );
DECLARE @exception_dump          LONG VARCHAR;
 
FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR
SELECT pkey  AS @pkey,
       data1 AS @data1
  FROM t
FOR READ ONLY
DO
 
   UPDATE t SET counter = counter + 1 WHERE pkey = @pkey;
 
   BEGIN
 
      UPDATE t SET data2 = @data1 + 1 WHERE pkey = @pkey;
 
   EXCEPTION WHEN OTHERS THEN
 
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
 
      ROLLBACK;
 
      UNLOAD SELECT 'Table t', * FROM t WHERE pkey = @pkey
        INTO VARIABLE @exception_dump;
 
      CALL record_exception ( 
         STRING ( 'SQLCODE = ', @sqlcode, 
                  ', SQLSTATE = ', @sqlstate, 
                  ', ERRORMSG() = ', @errormsg ),
         @exception_dump );
 
   END;
 
   COMMIT;
 
END FOR;
 
EXCEPTION WHEN OTHERS THEN
 
   SELECT SQLCODE, SQLSTATE, ERRORMSG() 
     INTO @sqlcode, @sqlstate, @errormsg;
 
   MESSAGE STRING ( 
      CURRENT TIMESTAMP, 
      ' EXCEPTION in procedure p: ',
      ', SQLCODE = ', @sqlcode,  
      ', SQLSTATE = ', @sqlstate,  
      ', ERRORMSG() = ', @errormsg ) 
      TO CONSOLE;
 
END;
 
CREATE TABLE t (
   pkey       INTEGER NOT NULL PRIMARY KEY,
   counter    INTEGER NOT NULL,
   data1      INTEGER NOT NULL,
   data2      BIGINT NOT NULL );
 
INSERT t VALUES ( 1, 0, 3857592,    0 );
INSERT t VALUES ( 2, 0, 2147483647, 0 );
INSERT t VALUES ( 3, 0, 54296,      0 );
COMMIT;
 
CALL p();
SELECT * FROM t ORDER BY pkey;
SELECT * FROM exception_diagnostic ORDER BY exception_id;
SELECT * FROM exception_dump ORDER BY dump_id;

The CALL on line 70 runs to completion even though there's an exception halfway through the FOR loop.

How do you know that?

Because it's shown by the three SELECT statements on lines 71 through 73:

pkey,counter,data1,data2
1,1,3857592,3857593
2,0,2147483647,0
3,1,54296,54297
 
exception_id,exception_diagnostic
1,2011-10-02 13:09:00.943 2 SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 2147483647 + 1 out of range for destination
 
exception_id,dump_id,dump_inserted_at,dump
1,1,'2011-10-02 13:09:00.945','Table t',2,0,2147483647,0\x0d\x0a

The error message on line 7 shows that one of the updates failed with the dreaded "Value out of range for destination".

The dump on line 10 shows that the error occurred on row 2 in table t.

The result set on lines 2 through 4 shows that all the changes for row 2 were rolled back, but the changes for rows 1 and 2 were made successfully.

Here's what the exception_dump table looks like if the UNLOAD is moved before the ROLLBACK; it shows the row as stood after the first UPDATE but before the ROLLBACK (the counter column contains 1 instead of zero):

1 2	`exception_id,dump_id,dump_inserted_at,dump` `1,1,'2011-10-02 13:09:48.564','Table t',2,1,2147483647,0\x0d\x0a`

You can code pretty much what you want inside the EXCEPTION handler; here's an example that uses UNLOAD with APPEND ON to capture before-and-after-ROLLBACK images of the row in table t:

EXCEPTION WHEN OTHERS THEN
 
   SELECT SQLCODE, SQLSTATE, ERRORMSG() 
     INTO @sqlcode, @sqlstate, @errormsg;
 
   UNLOAD SELECT 'Table t before ROLLBACK', * FROM t WHERE pkey = @pkey
     INTO VARIABLE @exception_dump;
 
   ROLLBACK;
 
   UNLOAD SELECT 'Table t after ROLLBACK', * FROM t WHERE pkey = @pkey
     INTO VARIABLE @exception_dump APPEND ON;
 
   CALL record_exception ( 
      STRING ( 'SQLCODE = ', @sqlcode, 
               ', SQLSTATE = ', @sqlstate, 
               ', ERRORMSG() = ', @errormsg ),
      @exception_dump );
 
END;

Here's what the dump looks like with the two row images, with counter = 1 before the ROLLBACK and zero afterwards:

1 2	`exception_id,dump_id,dump_inserted_at,dump` `1,1,'2011-10-02 11:17:45.347','Table t before ROLLBACK',2,1,2147483647,0\x0d\x0a'Table t after ROLLBACK',2,0,2147483647,0\x0d\x0a`

Pages

Monday, October 31, 2011

Because it's hard, that's why!

Ah, it could be worse...

Friday, October 28, 2011

Maybe this...

Thursday, October 27, 2011

What's a Listicle?

Wednesday, October 26, 2011

...and for the other platforms:

Monday, October 24, 2011

Friday, October 21, 2011

Yeah, dreams are great, aren't they?

Thursday, October 20, 2011

Wednesday, October 19, 2011

What about the commits?

Monday, October 17, 2011

OK, there's a limit

What about UPDATE WHERE CURRENT OF Cursor?

Before...

...and After

75 minutes

15 minutes!

Set-oriented UPDATE rules!

Friday, October 14, 2011

...and for the other platforms:

Thursday, October 13, 2011

Wednesday, October 12, 2011

DCX now works on the iPad

About that speed thing...

Monday, October 10, 2011

So now you know

Friday, October 7, 2011

But wait, there's more!

But first...

Will it work?

Thursday, October 6, 2011

Wednesday, October 5, 2011

No computers!

OK, here we go!

Question 2: What was the "Gotcha!" moment?

Gotcha!

For bonus points: Why?

Time's Up!

Monday, October 3, 2011

Well, yes, there IS some work involved

But why would anyone want "Dump and Continue"?

Show me the code!

Does it work?

How do you know that?

Categories