There's a half-price sale going on in the SAP store... until December 31 almost all of the SQL Anywhere SKUs have been marked down by 50%:
Monday, September 30, 2013
SQL Anywhere Half-Price Sale
Friday, September 27, 2013
Product Suggestion: More ISQL SET Option Commands
ISQL already HAS most of what I was going to suggest: ISQL commands to change option settings on the fly, as part of a batch of commands being processed by ISQL.
Did you know that?
Here's an example:
SET OPTION TRUNCATION_LENGTH = 70;
SELECT PROPERTY ( 'RememberLastStatement' );
SET OPTION TRUNCATION_LENGTH = 50;
SELECT PROPERTY ( 'RememberLastStatement' );
SET OPTION TRUNCATION_LENGTH = 30;
SELECT PROPERTY ( 'RememberLastStatement' );
PROPERTY('RememberLastStatement')
----------------------------------------------------------------------
Yes
(1 rows)
PROPERTY('RememberLastStatement')
--------------------------------------------------
Yes
(1 rows)
PROPERTY('RememberLastStatemen
------------------------------
Yes
(1 rows)
|
There's one option missing, however; there is no SET OPTION command to dynamically choose Tools - Options - SQL Anywhere - Results - Style - Text, you have to use the GUI:
Maybe a SET OPTION statement isn't appropriate here, maybe a dbisql command line option is better... it doesn't matter... what matters is the ability to change the option in a batch file without having to clickety-clack through the GUI whenever one or the other styles is more appropriate.
Wednesday, September 25, 2013
SAP TechEd 2013 Registration Fee Goes Up at 5:01 PM PDT This Friday
The registration fee for SAP TechEd 2013 in Las Vegas on October 21–25 goes up after this Friday: from US $2,695 to $2,795.
Monday, September 23, 2013
Latest SQL Anywhere Updates: 12.0.1.3958 for Mac OS
The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.
- Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1, 16.0 and On Demand) are shown here.
- The "EBF 21788 SP60" numbers are the new SAP-specific codes associated with the build numbers "12.0.1.3894".
- Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.
Friday, September 20, 2013
Force Balance a + b + c = 100
The requirement is to display three integer percentage values a, b and c, where a + b + c always equals 100.
The values a and b come from table t, and c is calculated as the remainder 100 - a - b.
The problem is, the values of a and b may be too large, causing a + b to exceed 100. In this case the values of a and/or b must be lowered so that a + b = 100 (thus making c = 0).
Here are nine combinations of a and b, where 4 combinations are OK and five combinations exhibit the problem:
The trick is, how should the new "lowered" a and/or b values be calculated?
CREATE TABLE t ( pkey INTEGER PRIMARY KEY, a INTEGER, b INTEGER ); INSERT t VALUES ( 1, 0, 0 ); INSERT t VALUES ( 2, 10, 10 ); INSERT t VALUES ( 3, 100, 0 ); INSERT t VALUES ( 4, 0, 100 ); INSERT t VALUES ( 5, 55, 55 ); INSERT t VALUES ( 6, 200, 0 ); INSERT t VALUES ( 7, 0, 200 ); INSERT t VALUES ( 8, 90, 70 ); INSERT t VALUES ( 9, 150, 50 ); COMMIT; SELECT pkey, a, b, 100 - a - b AS c FROM t ORDER BY pkey; pkey a b c ----------- ----------- ----------- ----------- 1 0 0 100 2 10 10 80 3 100 0 0 4 0 100 0 5 55 55 -10 6 200 0 -100 7 0 200 -100 8 90 70 -60 9 150 50 -100
What's your solution? (don't peek!)
This is a real-world problem taken from the development of a new feature in Foxhound Version 3: The "Busy Wait Idle %" column will show the relative amount of time each connection has spent doing work, waiting for resources, and sitting idle with nothing to do.
Busy + Wait + Idle must add up to 100 because, well, this is the real world, but the SQL Anywhere performance properties used to calculate Busy and Wait aren't always [cough] in step... sometimes Busy + Wait exceeds 100, hence the need to "pull them down" so Busy + Wait = 100.
One way is to recalculate a and b as percentages of ( a + b ):
This process is sometimes called "force balance" where a column of rounded numbers are fiddled, er, adjusted so they add up to a known total (like 100%) rather than displaying some lame explanation about "rounding errors"... everyone knows that rounded numbers are inherently imprecise, so as long as the adjustment is made in a sensible manner it's sometimes better than displaying numbers that don't add up.
SELECT pkey, IF t.a + t.b <= 100 THEN t.a ELSE CAST ( ( t.a * 100 ) / ( t.a + t.b ) AS SMALLINT ) END IF AS a, IF t.a + t.b <= 100 THEN t.b ELSE 100 - a END IF AS b, 100 - a - b AS c FROM t ORDER BY pkey; pkey a b c ----------- ----------- ----------- ----------- 1 0 0 100 2 10 10 80 3 100 0 0 4 0 100 0 5 50 50 0 6 100 0 0 7 0 100 0 8 56 44 0 9 75 25 0
The force balance process often requires row-by-row processing; in the example shown here, it's nice to be able to embed the calculations in a query.
Monday, September 16, 2013
Latest SQL Anywhere Updates: V16 for Linux, Mac and Windows, 11 for Linux
The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.
- Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1, 16.0 and On Demand) are shown here.
- The "EBF 21788 SP60" numbers are the new SAP-specific codes associated with the build numbers "12.0.1.3894".
- Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.
Monday, September 9, 2013
Implementing YEARDIFF
Question: How do I compute the number of complete years between two timestamps?
Answer: Call DATEDIFF YEAR and if you don't like the answer, subtract 1!
That's not as silly as it sounds. DATEDIFF has two huge advantages over a start-from-scratch-and-do-it-yourself approach:
- DATEDIFF takes care of those pesky leap years and the funky divisible-by-4-vs-100-vs-400 rule (2000 and 2400 are leap years, while 1800, 1900, 2100, 2200, 2300 and 2500 are NOT leap years), and
- DATEDIFF works... it's already been tested by bazillions of developers and end users.
But wait!
There's another function you can call to determine if DATEDIFF got it right or not: DATEADD.Yes, DATEADD... take the number of years returned by DATEDIFF and call DATEADD to add it to the first timestamp (assuming the first timestamp is the smaller one). If the result is less than or equal to the second timestamp, then DATEDIFF returned the right number. If not, then DATEADD's answer is too high by 1... hence the "subtract 1" in the answer above.
Here's the code, followed by a test using timestamps that are different by only one microsecond, as well as one year boundary:
CREATE FUNCTION YEARDIFF (
IN @timestamp1 TIMESTAMP,
IN @timestamp2 TIMESTAMP )
RETURNS SMALLINT
DETERMINISTIC
BEGIN
DECLARE @lesser_timestamp TIMESTAMP;
DECLARE @greater_timestamp TIMESTAMP;
DECLARE @sign SMALLINT;
DECLARE @yeardiff SMALLINT;
IF @timestamp1 IS NULL OR @timestamp1 IS NULL THEN
RETURN NULL;
END IF;
IF @timestamp1 = @timestamp2 THEN
RETURN 0;
END IF;
IF @timestamp1 < @timestamp2 THEN
SET @sign = 1;
SET @lesser_timestamp = @timestamp1;
SET @greater_timestamp = @timestamp2;
ELSE
SET @sign = -1;
SET @lesser_timestamp = @timestamp2;
SET @greater_timestamp = @timestamp1;
END IF;
SET @yeardiff = DATEDIFF ( YEAR, @lesser_timestamp, @greater_timestamp );
IF DATEADD ( YEAR, @yeardiff, @lesser_timestamp ) > @greater_timestamp THEN
RETURN @sign * ( @yeardiff - 1 );
ELSE
RETURN @sign * @yeardiff;
END IF;
END;
SELECT DATEDIFF ( YEAR, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS DATEDIFF_YEAR_1,
YEARDIFF ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS YEARDIFF_1,
DATEDIFF ( YEAR, '2012-01-01 00:00:00.0000000', '2011-12-31 23:59:59.9999999' ) AS DATEDIFF_YEAR_2,
YEARDIFF ( '2012-01-01 00:00:00.0000000', '2011-12-31 23:59:59.9999999' ) AS YEARDIFF_2;
DATEDIFF_YEAR_1 YEARDIFF_1 DATEDIFF_YEAR_2 YEARDIFF_2
--------------- ---------- --------------- ----------
1 0 -1 0
SELECT DATEDIFF ( YEAR, '2010-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS DATEDIFF_YEAR_3,
YEARDIFF ( '2010-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS YEARDIFF_3,
DATEDIFF ( YEAR, '2012-01-01 00:00:00.0000000', '2010-12-31 23:59:59.9999999' ) AS DATEDIFF_YEAR_4,
YEARDIFF ( '2012-01-01 00:00:00.0000000', '2010-12-31 23:59:59.9999999' ) AS YEARDIFF_4;
DATEDIFF_YEAR_3 YEARDIFF_3 DATEDIFF_YEAR_4 YEARDIFF_4
--------------- ---------- --------------- ----------
2 1 -2 -1
|
Here's another test that how a straightforward call to DATEDIFF flubs a simple age calculation but gets it right when YEARDIFF handles the call to DATEDIFF:
SELECT DATEDIFF ( YEAR, '2012-07-27', '2013-07-28' ) AS correct_age,
DATEDIFF ( YEAR, '2012-07-29', '2013-07-28' ) AS incorrect_age;
correct_age incorrect_age
----------- -------------
1 1
SELECT YEARDIFF ( '2012-07-27', '2013-07-28' ) AS correct_age_1,
YEARDIFF ( '2012-07-29', '2013-07-28' ) AS correct_age_2;
correct_age_1 correct_age_2
------------- -------------
1 0
|
Wednesday, September 4, 2013
Beware DATEDIFF Alternatives
Previously on . . . The story began with Documenting DATEDIFF and continued with three episodes about the use and abuse of DATEDIFF in this blog, in Foxhound and in the Help.
Now the story turns to seven other SQL Anywhere functions that can be used instead of DATEDIFF to compute the difference between two timestamps.
Here's a comparison of how these seven functions stack up against DATEDIFF when applied to the same two timestamp values that are exactly one microsecond apart:
SELECT 'DATEDIFF' AS "function",
DATEDIFF ( year, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year,
DATEDIFF ( month, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month,
DATEDIFF ( day, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS day,
DATEDIFF ( week, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS week,
DATEDIFF ( hour, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS hour,
DATEDIFF ( minute, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS minute,
DATEDIFF ( second, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS second
UNION ALL
SELECT 'YEARS, etc',
YEARS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year,
MONTHS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month,
DAYS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS day,
WEEKS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS week,
HOURS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS hour,
MINUTES ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS minute,
SECONDS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS second;
function year month day week hour minute second
-------- ---- ----- --- ---- ---- ------ ------
DATEDIFF 1 1 1 1 0 1 0
YEARS, etc 1 1 1 0 0 1 1
|
Here's what the Help has to say about these seven alternatives to DATEDIFF, and how the Help stacks up against testing:
| What The Help Says | What Testing Says | |
| YEARS | The value of YEARS is computed by counting the number of first days of the year between the two dates. | True. YEARS returns the number of year boundaries between the two parameters, just like DATEDIFF YEAR. |
| MONTHS | If you pass two TIMESTAMP values to the MONTHS function, the function returns the integer number of months between them. | False. MONTHS returns the number of month boundaries between the two parameters, just like DATEDIFF MONTH. |
| DAYS | If you pass two TIMESTAMP values to the DAYS function, the function returns the integer number of days between them. | False. DAYS returns the number of day boundaries between the two parameters, just like DATEDIFF DAY. |
| WEEKS | Given two dates (Syntax 2), the WEEKS function returns the number of weeks between them. The WEEKS function is similar to the DATEDIFF function, however the method used to calculate the number of weeks between two dates is not the same and can return a different result. The return value for WEEKS is determined by dividing the number of days between the two dates by seven, and then rounding down. However, DATEDIFF uses number of week boundaries in its computation. This can cause the values returned from the two functions to be different. For example, if the first date is a Friday and the second date is the following Monday, the WEEKS function returns a difference of 0, but the DATEDIFF function returns a difference of 1. While neither method is better than the other, you should consider the difference when choosing between WEEKS and DATEDIFF. | True. This is the best Help of all these functions, DATEDIFF included... it gives a clear explanation of the difference between counting units (weeks, etc.) and counting boundaries (week changes, etc). |
| HOURS | If you pass two TIMESTAMP values to the HOURS function, the function returns the integer number of hours between them. | True. Both HOURS and DATEDIFF HOUR return the number of hours, not the number of hour boundaries. |
| MINUTES | If you pass two TIMESTAMP values to the MINUTES function, the function returns the integer number of minutes between them. | False. MINUTES returns the number of minute boundaries between the two parameters, just like DATEDIFF MINUTE. |
| SECONDS | If you pass two TIMESTAMP values to the SECONDS function, the function returns the integer number of seconds between them as a SIGNED BIGINT value. | False. SECONDS returns the number of second boundaries between the two parameters. This is different from DATEDIFF SECOND which returns the number of seconds rather than the number of boundaries. |
So . . .
WEEKS makes a nice (and nicely documented) alternative to DATEDIFF WEEK, but the rest of them?Monday, September 2, 2013
DATEDIFF Mistakes Case Study 3: The Help
Previously on . . . The story began with Documenting DATEDIFF and continued with two episodes about the use and abuse of DATEDIFF in this blog and in Foxhound.
Now the story turns to DATEDIFF examples published in the SQL Anywhere 16 Help.
The Scoring System
- FAIL means the DATEDIFF usage is badly flawed; it shouldn't have been coded that way, and it should be fixed.
- LUCKY means the DATEDIFF usage may be flawed but it doesn't matter given the data values involved.
- OK means the DATEDIFF usage is OK given the data types involved.
The Scores
Example 1 is OK: begin_poll event
Let's ignore the obvious problems with this code (the "Syntax error near DISTINCT", the unknown nature of the tables in the FROM clause, and so on) and assume that "datediff( hour, last_status_change, now()" refers to a valid TIMESTAMP value in last_status_change.
This example creates a push request for a Notifier named Notifier A. It uses a SQL statement that inserts rows into a table named PushRequest. Each row in this table represents a message to send to an address. The WHERE clause determines which push requests are inserted into the PushRequest table.
To use the ml_add_property system procedure with a SQL Anywhere consolidated database, run the following command:
ml_add_property( 'SIS', 'Notifier(Notifier A)', 'begin_poll', 'INSERT INTO PushRequest (gateway, mluser, subject, content) SELECT ''MyGateway'', DISTINCT mluser, ''sync'', stream_param FROM MLUserExtra, mluser_union, Dealer WHERE MLUserExtra.mluser = mluser_union.name AND (push_sync_status = ''waiting for request'' OR datediff( hour, last_status_change, now() ) > 12 ) AND ( mluser_union.publication_name is NULL OR mluser_union.publication_name =''FullSync'' ) AND Dealer.last_modified > mluser_union.last_sync_time' );
Guess what? Assuming that "> 12" means "thirteen full hours or more" then DATEDIFF HOUR works OK because unlike other forms of DATEDIFF it returns the number of HOUR units between the two timestamps, not the number of HOUR boundaries.
Here's proof using two timestamps that have 13 hour boundaries between them but differ by an interval of only 12:00:00.000001 (which DATEDIFF HOUR truncates to 12):
BEGIN DECLARE @last_status_change TIMESTAMP DEFAULT ( '2013 09 02 00:59:59.999999' ); DECLARE @now TIMESTAMP DEFAULT ( '2013 09 02 13:00:00.000000' ); SELECT DATEDIFF ( HOUR, @last_status_change, @now ); END; DATEDIFF(hour,@last_status_change,@now) 12
Example 2 is a FAIL: Trigger conditions for events
Assuming Notification.last_notification is a TIMESTAMP column, what does "DATEDIFF( minute, last_notification, current timestamp )" return?
Notify an administrator of a possible attempt to break into the database:
CREATE EVENT SecurityCheck TYPE ConnectFailed HANDLER BEGIN DECLARE num_failures INT; DECLARE mins INT; INSERT INTO FailedConnections( log_time ) VALUES ( CURRENT TIMESTAMP ); SELECT COUNT( * ) INTO num_failures FROM FailedConnections WHERE log_time >= DATEADD( minute, -5, current timestamp ); IF( num_failures >= 3 ) THEN SELECT DATEDIFF( minute, last_notification, current timestamp ) INTO mins FROM Notification; IF( mins > 30 ) THEN UPDATE Notification SET last_notification = current timestamp; CALL xp_sendmail( recipient='DBAdmin', subject='Security Check', "message"= 'over 3 failed connections in last 5 minutes' ) END IF END IF END;
Answer: It returns the number of MINUTE boundaries between the two timestamp values. This usage must be marked as a FAIL because DATEDIFF MINUTE can return two different answers for exactly the same interval depending on the number of MINUTE boundaries between the two timestamps.
BEGIN DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 00:59:59.999999' ); DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.000000' ); SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp ); END; BEGIN DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 01:00:00.000000' ); DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.000001' ); SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp ); END; DATEDIFF(minute,@last_notification,@current_timestamp) 31 DATEDIFF(minute,@last_notification,@current_timestamp) 30DATEDIFF MINUTE can also return a smaller answer for a longer interval. Here's proof; the first DATEDIFF MINUTE call returns 31 for an interval of 00:30:00.000001 while the second returns 30 for an interval of 00:30:00.999999:
BEGIN DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 00:59:59.999999' ); DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.000000' ); SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp ); END; BEGIN DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 01:00:00.000000' ); DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.999999' ); SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp ); END; DATEDIFF(minute,@last_notification,@current_timestamp) 31 DATEDIFF(minute,@last_notification,@current_timestamp) 30
Example 3 is OK: #hook_dict table
This usage scores OK for two reasons:
The following sample sp_hook_dbmlsync_delay procedure illustrates the use of in/out parameters in the #hook_dict table. The procedure allows synchronization only outside a scheduled down time of the MobiLink system between 18:00 and 19:00.
CREATE PROCEDURE sp_hook_dbmlsync_delay() BEGIN DECLARE delay_val integer; SET delay_val=DATEDIFF( second, CURRENT TIME, '19:00'); IF (delay_val>0 AND delay_val<3600) THEN UPDATE #hook_dict SET value=delay_val WHERE name='delay duration'; END IF; END
- The code's more precise than the specs so an error doesn't matter; i.e., the specifications refer to hours "between 18:00 and 19:00" while the code calculates seconds "DATEDIFF( second, CURRENT TIME, '19:00')".
- ...and besides, DATEDIFF SECOND returns the number of full seconds between two timestamps rather than the number of second boundaries; i.e., it's the other DATEDIFF rarity along with with DATEDIFF HOUR.
Example 4 is OK: sp_hook_dbmlsync_abort
This one looks like a FAIL until you realize that down_time_start contains a constant value '19:00:00.000000'. That means it doesn't matter whether DATEDIFF HOUR is calculated using hours (it's not) or hour boundaries (it is), it's all the same.
The following procedure prevents synchronization during a scheduled maintenance hour between 19:00 and 20:00 each day.
CREATE PROCEDURE sp_hook_dbmlsync_abort() BEGIN DECLARE down_time_start TIME; DECLARE is_down_time VARCHAR(128); SET down_time_start='19:00'; IF datediff( hour,down_time_start,now(*) ) < 1 THEN set is_down_time='true'; ELSE SET is_down_time='false'; END IF; UPDATE #hook_dict SET value = is_down_time WHERE name = 'abort synchronization' END;
In other words, this usage is OK.
Example 5 is OK: sa_performance_diagnostics system procedure
This code's OK for the same reasons Example 3 is OK: For the connections of interest the difference between LoginTime and CURRENT TIMESTAMP grows very large very quickly compared with the size of any error introduced by DATEDIFF, and besides, DATEDIFF SECOND is calculated in seconds rather than second boundaries.
You can execute the following query to identify connections that have spent a long time waiting for database server requests to complete.
SELECT Number, Name, CAST( DATEDIFF( second, LoginTime, CURRENT TIMESTAMP ) AS DOUBLE ) AS T, IF T <> 0 THEN (ReqTimeActive / T) ELSE NULL ENDIF AS PercentActive FROM sa_performance_diagnostics() WHERE T > 0 AND PercentActive > 10.0 ORDER BY PercentActive DESC;
Example 6 is OK: sa_performance_diagnostics system procedure
Same thing again, same as Examples 3 and 5: This code is OK. A purist might use DATEDIFF MILLISECOND (Foxhound does) but with a predicate like "ReqTime > 60.0" it's not really necessary.
Find all requests that are currently executing, and have been executing for more than 60 seconds:
SELECT Number, Name, CAST( DATEDIFF( second, LastReqTime, CURRENT TIMESTAMP ) AS DOUBLE ) AS ReqTime FROM sa_performance_diagnostics() WHERE ReqStatus <> 'IDLE' AND ReqTime > 60.0 ORDER BY ReqTime DESC;
Example 7 is a FAIL: Text index refresh types
Let's ignore all the issues with this code and assume the DATEDIFF call was really written like this:
You can define your own strategy for refreshing MANUAL REFRESH text indexes. In the following example, all MANUAL REFRESH text indexes are refreshed using a refresh interval that is passed as an argument, and rules that are similar to those used for AUTO REFRESH text indexes.
CREATE PROCEDURE refresh_manual_text_indexes( refresh_interval UNSIGNED INT ) BEGIN FOR lp1 AS c1 CURSOR FOR SELECT ts.* FROM SYS.SYSTEXTIDX ti JOIN sa_text_index_stats( ) ts ON ( ts.index_id = ti.index_id ) WHERE ti.refresh_type = 1 -- manual refresh indexes only DO BEGIN IF last_refresh_utc IS null OR cast(pending_length as float) / ( IF doc_length=0 THEN NULL ELSE doc_length ENDIF) > 0.2 OR DATEDIFF( MINUTE, CURRENT UTC TIMESTAMP, last_refresh_utc ) > refresh_interval THEN EXECUTE IMMEDIATE 'REFRESH TEXT INDEX ' || text-index-name || ' ON "' || table-owner || '"."' || table-name || '"'; END IF; END; END FOR; END;
DATEDIFF( MINUTE, last_refresh, CURRENT TIMESTAMP ) > refresh_intervalThis code is a FAIL for the same reasons as the code in Example 2:
- DATEDIFF MINUTE can return two different answers for exactly the same interval depending on the number of MINUTE boundaries between the two timestamps, and
- DATEDIFF MINUTE can also return a smaller answer for a longer interval.
The Final Score: 5 OK, 2 FAIL
Some might argue one or two OK scores were really just LUCKY, but it's the two FAILs that prove the point: DATEDIFF is difficult.Monday, August 26, 2013
DATEDIFF Mistakes Case Study 2: Foxhound
An early design decision is reflected in the code throughout Foxhound: All time intervals are calculated in milliseconds, even long intervals measured in seconds, hours or even days. As a result, even though DATEDIFF ( MILLISECOND, x, y ) is often called with TIMESTAMP values for x and y, and even though that can result in an error of (almost) one whole millisecond in the value returned by DATEDIFF, it doesn't matter: Foxhound only displays intervals to the nearest 0.1 second, so an error of 0.001 second doesn't affect the result.
Previously on . . . Documenting DATEDIFF described how the peculiar workings of SQL Anywhere's DATEDIFF() function can lead to coding errors, and DATEDIFF Mistakes Case Study: This Blog was a bug hunt for those errors in some sample code.
Now the bug hunt continues, this time in production code for an application that depends heavily on DATEDIFF(): the Foxhound performance monitor.
Here's an example; the internal Foxhound function rroad_f_msecs_as_d_h_m_s() takes a BIGINT value in milliseconds and formats it to return a string like '1h 16m 56s' or '9.9s'
Only small values (less than one hour) are shown to the nearest tenth of a second, like '9.9s', and large values are only shown to the nearest second like '1h 16m 56s', so DATEDIFF errors of one millisecond or less don't matter.
Foxhound is awash in DATEDIFF MILLISECOND calls, and they all share the same characteristic: The errors don't matter because the code doesn't care about milliseconds. Foxhound isn't a financial application that cares about the pennies, it isn't even an execution profiler that cares about how long a single statement takes to execute; it is a performance monitor that gathers samples every 10 seconds.
Are there any DATEDIFF something-other-than-MILLISECOND calls in Foxhound?
Yes, there are a few. This one checks a user input datetime for validity:
A @FOXHOUND3UPGRADE_timestamp value more than 100,000 days in the future is ignored because it is "way too big". Since DATEDIFF DAY counts the number of day boundaries between the two timestamps, the return value could be wrong by (almost) one entire day, which means a value only 99,999 days in the future could be incorrectly rejected.
Yeah, that's a bug... one that's not going to be fixed, or even documented in the Foxhound FAQ, but a bug nonetheless... the Foxhound Development Team promises to do better!
Here's another example; DATEDIFF DAY is called calculate how many days are left before the current rental period expires:
In this case, the second and third arguments to DATEDIFF are only precise to the nearest DAY, so counting the number of day boundaries is the same as counting the number of days, and DATEDIFF DAY returns the right answer.
What's the score?
Here's how DATEDIFF usage is scored according to the previous Case Study:- FAIL means the DATEDIFF usage is badly flawed; it shouldn't have been coded that way, and it should be fixed.
- LUCKY means the DATEDIFF usage may be flawed but it doesn't matter given the data values involved.
- OK means the DATEDIFF usage is OK given the data types involved.
This bug hunt didn't turn up anything worth changing... like all bug hunts it did find some stuff in need of fixing, just not DATEDIFF :)
Monday, August 19, 2013
Latest SQL Anywhere Updates: Windows 12.0.1.3942
The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.
- Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1 and 16.0) are shown here.
- Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.
Wednesday, August 14, 2013
The WaybackGlennMachine
Once upon a time, there were two regularly-published blogs devoted to SQL Anywhere: this one, and a better one written by Glenn Paulley. Before leaving SAP on July 31, 2012, Glenn had published over 150 posts of a technical nature as well as many announcements and other posts.
Sadly, as part of the transition to the SAP Community Network, Glenn's blog and four others (Chris Kleisath, Eric Farrar, Jason Hinsperger and Tom Slee/Philippe Bertrand) were suddenly wiped from existence, taking with them over 300 posts containing valuable technical information.
A handful of Glenn's posts have been republished on the SAP Community Network, but it's clearly not a high priority task.
The Internet Midden To The Rescue!
The good news is, you can still find most of Glenn's posts in The Wayback Machine. It helps if you know what you're looking for, and the Technical Documents page on this blog can help as follows:
- Scroll through the Technical Documents page looking for "Glenn Paulley" entries marked "Blog: ..."
- When you find one you like, click right mouse - Copy link address on the title:
- Open up The Wayback Machine and paste the link address into the "Take Me Back" field:
- Click on "Take Me Back" button to see a calendar of all the different times Glenn's post was archived.
- Click on any of the calendar entries for that post; it doesn't matter which one because Glenn wasn't in the habit of changing old posts.
- Here's what you'll see, an almost-fully-functional copy of the original post:
Sure, it's a kludge...
Some of Glenn's posts are missing, and some of the embedded objects too, and some of his links have rotted.
And no, Google search doesn't reach into The Wayback Machine.
But... it's better than the alternative:
Monday, August 12, 2013
Characteristic Errors, Revision 4
UPDATE: See the latest version of this article here.
Back in June this list had 35 entries, now it has 42...
[click here to see the new entries]
A characteristic error is an error that is so easy to make that it appears you are being actively encouraged to make it by the very nature of the computer program you are using.
For example, sending an email without the attachment is a characteristic error of all email programs.
...except Gmail. Gmail warns you about missing attachments... Gmail is magic!Here are some errors that are characteristic of SQL in general, SQL Anywhere in particular, and some companion programs.
- SQL: Seeing too little data, or no data at all, because a predicate in the WHERE clause effectively turned your OUTER JOIN into an INNER JOIN.
- SQL: Seeing too much data because a missing predicate effectively turned your INNER JOIN into a CROSS JOIN.
- SQL: Getting the wrong COUNT() or SUM() because you forgot to code WHERE ... IS NOT NULL, or you *did* code it when you shouldn't have.
- SQL: Getting the wrong answer because you forgot that, in general, NULL values [cough] suck.
- SQL Anywhere: Not seeing MESSAGE output because you forgot to run SET TEMPORARY OPTION DEBUG_MESSAGES = 'ON';
- SQL Anywhere: Not seeing any data because you forgot ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL.
- SQL Anywhere: Coding ENDIF where END IF was required, or vice versa (before Version 11).
- SQL Anywhere: Connecting to the wrong server because you forgot DOBROAD=NONE (before Version 12).
- SQL Anywhere: Forgetting the asterisk in SELECT TOP 10 FROM ...
- SQL Anywhere: Coding IF NOT VAREXISTS ( 'x' ) THEN ... instead of IF VAREXISTS ( 'x' ) = 0 THEN ...
- SQL Anywhere: Coding the wrong magic numbers 1, 2, 3, ... in the get_value() and set_value() calls in an EXTERNAL C DLL function.
- SQL Anywhere: Getting proxy table ODBC errors because the engine's running as a service and you've set up a User DSN instead of System DSN.
- SQL Anywhere: Getting file-related errors because the file specifications are relative to the server rather than the client.
- SQL Anywhere: Getting file-related errors because the engine's running as a service without the necessary permissions.
- SQL Anywhere: Coding CREATE TRIGGER IF NOT EXISTS instead of CREATE OR REPLACE TRIGGER, or vice versa for CREATE TABLE (in 11.0.1 or later).
- SQL Anywhere: Getting integer arithmetic when you wanted fractional parts because you forgot to CAST.
- Stored procedure debugger: Setting it to watch a specific user id other than the one you're using to test your code.
- Sybase Central: Setting it to display objects for owner names other than the one you're interested in.
- Copy and paste: Forgetting to edit after pasting; e.g., Copy and paste SET @continue = 'Y' into the body of a WHILE loop and then forgetting to change it to 'N'.
- MobiLink: Forgetting to call ml_add_column for any of the columns you're trying to synchronize, thus guaranteeing yourself a "Sassen Frassen Fricken Fracken!" moment when you run the first test.
- MobiLink: Forgetting to call ml_add_[various] with the NULL parameter to delete old ml_[whatever] rows, thus ending up with thousands of orphan system table rows in the consolidated database.
- OLAP Windowing: Coding the wrong combination of ASC and DESC in an inner OVER ORDER BY clause and the outer SELECT ORDER BY: different when they should be the same, the same when they should be different, or some other variation of "wrong combination"...
SELECT older_sample_set.sample_set_number INTO @20_older_sample_set_number FROM ( SELECT TOP 20 ROW_NUMBER() OVER ( ORDER BY rroad_sample_set.sample_set_number ASC ) AS scrolling_row_number, rroad_sample_set.sample_set_number AS sample_set_number FROM rroad_sample_set WHERE rroad_sample_set.sampling_id = @sampling_id AND rroad_sample_set.sample_set_number < @sample_set_number ORDER BY rroad_sample_set.sample_set_number DESC ) AS older_sample_set WHERE older_sample_set.scrolling_row_number = 20; - MobiLink: Forgetting to call ml_add_column() when trying to use named parameters instead of "?" in versions 10 and 11 MobiLink scripts, resulting in a "What the ... ? Sassen Frassen Fricken Fracken!" moment during the first test (thank you, Jeff Albion).
- SQL: Omitting a PRIMARY KEY column from the WHERE clause, thus turning a singleton SELECT (or DELETE!) into something rather more enthusiastic than expected (thank you, Ron Hiner).
- HTTP web services: Leaving an & in the code when a ? is required, and vice versa, when editing service URLs; e.g., 'HTTP://localhost:12345/web_service&service_parm2=!parm2'
- SQL Anywhere: Forgetting that not all functions look like functions: SELECT CAST ( CURRENT TIMESTAMP, VARCHAR )
- Batch file: Trailing spaces on SET commands; e.g., SELECT CAST ( xp_getenv ( 'DEBUG_MESSAGES' ) AS VARCHAR ) returns 'OFF ' instead of 'OFF' after SET DEBUG_MESSAGES=OFF
- Forum: Clicking Reply on the main Question or Answer entry instead of the comment you wanted.
- SQL Anywhere: Forgetting to run dblog to tell the database file where the log is now, after moving the database and log files to a different folder (thank you, Justin Willey).
- SQL Anywhere: Having to look up WAIT in the Help ... every ... single ... time, to be reminded that's it's WAITFOR, not WAIT.
- SQL: Forgetting to check the SELECT against the GROUP BY, resulting in "Function or column reference to ... must also appear in a GROUP BY" (thank you, Glenn Paulley).
- SQL: Coding too much in the GROUP BY (like, say, the primary key) so every group contains but a single row (thank you, Glenn Paulley).
- Design: Forgetting to accomodate or prevent loops in a tree structure, resulting in a tree traversal process that pegs the CPU at 100%... forever (thank you, Ove B).
- MobiLink: Unwittingly using a variety of user ids when running sync*.sql, updating MobiLink scripts and running the MobiLink server, resulting in inexplicable inconsistencies.
- MobiLink: Accidentally creating multiple script versions and then getting them crossed up when updating MobiLink scripts and running the MobiLink client.
New entries... - SQL Anywhere: Forgetting to run the 32-bit version of SQL Anywhere when working with Excel proxy tables.
- ODBC Administrator: Running the 64-bit version (huh?) of odbcad32.exe (say what?) when you need 32-bit version at C:\WINDOWS\SysWOW64\odbcad32.exe (oh, fer #*@&!!!)
- ODBC Administrator: Forgetting to click OK ... twice ... to actually save your new ODBC DSN after celebrating your success with Test Connection.
- ODBC Administrator: Setting up an ODBC DSN on the wrong computer: "It goes with the client!" ... but sometimes it's not obvious where the client is actually located.
- Security: Forgetting which Windows user id you're using on which system, then spending too much time with Windows menus, firewall software and Google searches before the "Doh!" moment.
- SQL: Getting an exception that is not only completely inexplicable, but absolutely impossible for the statement that raised it... until you think to look inside the triggers.
- SQL Anywhere: Getting an exception because a FOR loop variable has a scope conflict with a column name, or worse, NOT getting an exception, just a wrong result.
Friday, August 9, 2013
DATEDIFF Mistakes Case Study: This Blog
After writing about problems with DATEDIFF it seems natural to look for examples, and there's no more exciting place to start throwing stones than inside one's own glass house!
And so we have . . .
Dogfooding DATEDIFF
. . . a critical look at the use and abuse of SQL Anywhere's DATEDIFF function in this blog.In other words, a Bug Hunt, with each example of DATEDIFF ranked as follows:
- FAIL means the DATEDIFF usage is badly flawed; it shouldn't have been coded that way, and it should be fixed.
- LUCKY means the DATEDIFF usage may be flawed but it doesn't matter given the data values involved.
- OK means the DATEDIFF usage is OK given the data types involved.
Example 1 is OK: Let's play "Gotcha!" - Round Two
All the examples share one characteristic in common: The second and third DATEDIFF arguments (the date/time values) are no more precise than the first argument (the unit name). In other words, DAY is used on dates with no time component, and SECOND is used on timestamps that don't have fractional seconds.
As a result, they all give correct results.
Well, the first 3 do, and the last one would have worked if the third argument hadn't been out of range (the whole point behind the article :).
Example 2 is OK: Let's play "Gotcha!" - Round Three
No DATEDIFF problems here at all, for the same reason: All the timestamps are precise only to the second.
Example 3 is LUCKY: Intra-Procedure Parallelism
There are a couple of problems with these DATEDIFF calls:
First, the calls may return values that are incorrect by up to one millisecond because DATEDIFF MILLISECOND returns the number of millisecond boundaries between the two timestamps.
... DECLARE @start TIMESTAMP; DECLARE @start_step_1 TIMESTAMP; DECLARE @start_step_2 TIMESTAMP; ... SET @start = CURRENT TIMESTAMP; SET @start_step_1 = CURRENT TIMESTAMP; ... MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start_step_1, CURRENT TIMESTAMP ) AS DECIMAL ( 11, 2 ) ) / 1000.0, ' seconds to perform step 1' ) TO CONSOLE; ... SET @start_step_2 = CURRENT TIMESTAMP; ... MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start_step_2, CURRENT TIMESTAMP ) AS DECIMAL ( 11, 2 ) ) / 1000.0, ' seconds to perform step 2' ) TO CONSOLE; MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start, CURRENT TIMESTAMP ) AS DECIMAL ( 11, 2 ) ) / 1000.0, ' seconds to perform both steps' ) TO CONSOLE; ... 10.1680000 seconds to perform step 1 19.9700000 seconds to perform step 2 30.1440000 seconds to perform both steps
Second, since DATEDIFF MILLISECOND returns a BIGINT, the CAST is singularly pointless, possibly dangerous: You can't magically add two digits of precision to an integer, and DECIMAL ( 11, 2 ) isn't big enough for a BIGINT:
But wait! There's no way this code will run long enough overflow a DECIMAL ( 11, 2 ), and the CAST is there to force decimal rather than integer division. The division by 1000.0 indicates that the user is interested in seconds, rather than milliseconds.
In other words, DATEDIFF MILLISECOND is being used to increase accuracy, not decrease it... an error of one millisecond is OK whereas DATEDIFF SECOND might have an error of a whole second.
The code's not wrong, but it is a bit sloppy: The output shows 7 digits of precision to the right of the decimal point whereas the actual values are only precise to the second digit. An outer CAST AS DECIMAL ( 11, 2 ) call could be used to show this... or call to ROUND().
Example 4 is OK: Today's Tip: Counting Days of the Week
The code is OK because "number of day boundaries" is the same as "number of days" when you're talking about dates with no time component:
Even the explanation in the article is OK, in this particular case: "The DATEDIFF ( ... ) call returns the number of days between the two dates."
Example 5 is a FAIL: I'm lonely! signed, Your Database
This code uses DATEDIFF SECOND on full-precision timestamps, so it can return a value that may be up to one second too large because it counts the number of second boundaries between the two timestamps, not the number of seconds difference.
In this example, that means a repeated email may be sent in 19 seconds rather than 20:
... DECLARE @email_sent_at TIMESTAMP; DECLARE @current_timestamp TIMESTAMP DEFAULT CURRENT TIMESTAMP; ... DECLARE @email_repeat_threshold_in_seconds BIGINT DEFAULT 20; ... SELECT email_sent_at INTO @email_sent_at FROM lonely; IF DATEDIFF ( SECOND, @email_sent_at, @current_timestamp ) >= @email_repeat_threshold_in_seconds THEN ...
Maybe a 5% error doesn't matter, or even a 10% error (if the DEFAULT was 10 instead of 20), but that's not the point... the author (me) didn't realize the implications of using DATEDIFF SECOND on precise timestamp values.
Not knowing is not good, not in this business.
Example 6 is OK: Everything Looks Like a Database
This one is ok, a DATEDIFF DAY on two dates that don't contain time components:
... DECLARE @from_date DATE; DECLARE @to_date DATE; SET @from_date = '2009-02-01'; SET @to_date = '2009-12-31'; ... DATEDIFF ( DAY, @from_date, @to_date ) ) ) ...
Example 7 is LUCKY: Capturing the Server Console Log
Here's an example of a DATEDIFF call that can return values that are too large by up to one millisecond:
With these particular values one second is an error of 0.3% to 0.08%... which is probably OK.
... DECLARE LOCAL TEMPORARY TABLE checkpoint_record ( checkpoint_starting TIMESTAMP NOT NULL PRIMARY KEY, checkpoint_finished TIMESTAMP ) NOT TRANSACTIONAL; ... SELECT *, DATEDIFF ( MILLISECOND, checkpoint_record.checkpoint_starting, checkpoint_record.checkpoint_finished ) AS msec FROM checkpoint_record ORDER BY checkpoint_record.checkpoint_starting; ... checkpoint_starting checkpoint_finished msec 2011-01-30 05:11:32.000 2011-01-30 05:11:32.281 281 2011-01-30 05:31:33.453 2011-01-30 05:31:33.937 484 2011-01-30 05:51:35.046 2011-01-30 05:51:35.515 469 2011-01-30 06:11:36.640 2011-01-30 06:11:37.078 438 2011-01-30 06:31:38.234 2011-01-30 06:31:38.781 547 2011-01-30 06:51:39.937 2011-01-30 06:51:41.125 1188
The point, however, is the same as before: One shouldn't use DATEDIFF MILLISECOND if one wants millisecond accuracy.
Example 8 is OK: Great Moments In History: Housing Bubble
The code in this post uses DATE values with no time components, so DATEDIFF DAY works just fine:
It could be said the scoring is too lenient: Given the ignorance factor all the OK scores should really be marked as LUCKY. And maybe, all the LUCKYs should be FAIL.
Wednesday, August 7, 2013
TechEd 2013 Session Catalog - Updated
25 sessions have recently appeared in the MOB (Mobile) track in the TechEd 2013 Session Catalog, but none of them mention SQL Anywhere or MobiLink in either the title or description.
Here are all the new MOB titles...
SELECT COUNT ( DISTINCT Session_ID ) FROM SessionDownload2 WHERE Session_ID LIKE 'MOB%' AND ( Title LIKE '%MobiLink%' OR Title LIKE '%SQL Anywhere%' OR Description LIKE '%MobiLink%' OR Description LIKE '%SQL Anywhere%' ); COUNT ----- 0
Here's the list of sessions that DO touch on SQL Anywhere and MobiLink:
RDP868 is new on this list, replacing EA269 (Everything You Need to Know to Use SAP Crystal Reports) which is still being offered but the description no longer mentions SQL Anywhere or MobiLink.
So, the count is still 12... is that enough to get you to Las Vegas?
Are you interested in HANA?
SELECT COUNT ( DISTINCT Session_ID ) FROM SessionDownload2 WHERE Title LIKE '%HANA%' OR Description LIKE '%HANA%'; COUNT ----- 229
Subscribe to:
Posts (Atom)
