Wednesday, November 30, 2011

New Properties in SQL Anywhere 12 and Beyond

SQL Anywhere keeps track of several hundred "properties" at runtime and it makes them all available through various procedure and function calls. For example, here's how you can display all the properties at all three levels (connection, database and server):

SELECT PropName, PropDescription, Value FROM sa_conn_properties() WHERE Number = @@SPID ORDER BY PropName;
SELECT PropName, PropDescription, Value FROM sa_db_properties() ORDER BY PropName; 
SELECT PropName, PropDescription, Value FROM sa_eng_properties() ORDER BY PropName;
With each new version (e.g., 12) and each new point release (12.0.1) comes new properties. What's not expected, however, is the addition of new properties when an EBF is published... but that's exactly what has happened at some point since 12.0.1 was published.

Here are the properties that didn't exist in the GA 12.0.1 release 12.0.1.3152 but do now exist in the latest EBF 12.0.1.3505:
PropName             PropDescription                                       Value

Connection...
LastCommitRedoPos    Redo position at end of last commit                   9409179824

Database...
LastCommitRedoPos    Redo position at end of last commit                   9409179824
LastSyncedRedoPos    Last redo position written and synchronized to disk   9409179824
LastWrittenRedoPos   Last redo position written to disk                    9409179824

Server...
LicenseKey           Number of licensed seats                              *****-*****-*****-*****-*****
The obvious question is, were these properties needed by Fuji? EBFs don't come with "What's New" lists so it's hard to say.

I'll bet the answer is "no" for "LicenseKey" (and we won't mention the hosed up interesting PropDescription).

For all those "RedoPos" properties, if Fuji needs that kind of stuff, it surely must be delving deep into the internals :)



For the record, here are all the properties that did not exist in the original GA 12.0.0 release but were included in the GA 12.0.1 release (in theory, some of these could have snuck in with an EBF, but let's pretend not):
Connection...
PropName,PropDescription,Value

ConnectedTime,Total time connections have been connected,86.047
java_class_path,Additional jars and directories added to the class path when launching the external java vm,
st_geometry_interpolation,Specifies options to use when converting from an ST_CircularString to an ST_LineString,
uuid_has_hyphens,Controls format for UUID values,On
UserDefinedCounterRate01,A rate counter that is set by the database application (counter 1),0
UserDefinedCounterRate02,A rate counter that is set by the database application (counter 2),0
UserDefinedCounterRate03,A rate counter that is set by the database application (counter 3),0
UserDefinedCounterRate04,A rate counter that is set by the database application (counter 4),0
UserDefinedCounterRate05,A rate counter that is set by the database application (counter 5),0
UserDefinedCounterRaw01,A counter that is set by the database application (counter 1),0
UserDefinedCounterRaw02,A counter that is set by the database application (counter 2),0
UserDefinedCounterRaw03,A counter that is set by the database application (counter 3),0
UserDefinedCounterRaw04,A counter that is set by the database application (counter 4),0
UserDefinedCounterRaw05,A counter that is set by the database application (counter 5),0

Database...
PropName,PropDescription,Value

ApproximateCPUTime,Approximate CPU time used,.2658304
BytesReceived,Bytes received by server,53138
BytesReceivedUncomp,Bytes received after decompression,53138
BytesSent,Bytes sent to client,60237
BytesSentUncomp,Bytes sent before compression,60237
CarverHeapPages,Cache pages used for carvers,0
ClientStmtCacheHits,Number of prepares not required because of the client statement cache,9
ClientStmtCacheMisses,Number of prepares in the client statement cache which were prepared again,0
Commit,Number of commit requests,50
ConnectedTime,Total time connections have been connected,126.6503625460778
Cursor,Declared cursors,8
CursorOpen,Open cursors,8
HeapsCarver,Number of heaps used for carvers,0
HeapsLocked,Number of relocatable heaps currently locked in cache,8
HeapsQuery,Number of heaps used for query processing (hash and sort operations),0
HeapsRelocatable,Number of relocatable heaps,44
PacketsReceived,Packets received by server,156
PacketsReceivedUncomp,Packets received after decompression,156
PacketsSent,Packets sent to client,156
PacketsSentUncomp,Packets sent before compression,156
PrepStmt,Prepared statements,17
QueryHeapPages,Cache pages used for query processing (hash and sort operations),0
QueryMemActiveCurr,The current number of requests actively using query memory,1
QueryMemGrantFailed,The total number of times any request waited for query memory and failed to get it,0
QueryMemGrantGranted,The number of pages currently granted to requests,65676
QueryMemGrantRequested,The total number of times any request attempted to acquire query memory,3
QueryMemGrantWaited,The total number of times any request waited for query memory,0
QueryMemGrantWaiting,The current number of requests waiting for query memory,0
ReqCountActive,Number of requests processed,0
ReqCountBlockContention,Number of times waited for atomic access,0
ReqCountBlockIO,Number of times waited for I/O to complete,126
ReqCountBlockLock,Number of times waited for a lock,0
ReqCountUnscheduled,Number of times waited for scheduling,0
ReqTimeActive,Time spent processing requests,.7556879435794214
ReqTimeBlockContention,Time spent waiting for atomic access,0
ReqTimeBlockIO,Time spent waiting for I/O to complete,.1078449914723799
ReqTimeBlockLock,Time spent waiting for a lock,0
ReqTimeUnscheduled,Time spent unscheduled,0
RequestsReceived,Requests received by server,154
Rlbk,Rollback requests handled,0
UserDefinedCounterRate01,A rate counter that is set by the database application (counter 1),0
UserDefinedCounterRate02,A rate counter that is set by the database application (counter 2),0
UserDefinedCounterRate03,A rate counter that is set by the database application (counter 3),0
UserDefinedCounterRate04,A rate counter that is set by the database application (counter 4),0
UserDefinedCounterRate05,A rate counter that is set by the database application (counter 5),0
UserDefinedCounterRaw01,A counter that is set by the database application (counter 1),0
UserDefinedCounterRaw02,A counter that is set by the database application (counter 2),0
UserDefinedCounterRaw03,A counter that is set by the database application (counter 3),0
UserDefinedCounterRaw04,A counter that is set by the database application (counter 4),0

Server...
PropName,PropDescription,Value

ApproximateCPUTime,Approximate CPU time used,.2971648
Commit,Number of commit requests,50
ConnectedTime,Total time connections have been connected,126.7005812191214
Cursor,Declared cursors,6
CursorOpen,Open cursors,6
PrepStmt,Prepared statements,17
ReqCountActive,Number of requests processed,0
ReqCountBlockContention,Number of times waited for atomic access,0
ReqCountBlockIO,Number of times waited for I/O to complete,126
ReqCountBlockLock,Number of times waited for a lock,0
ReqCountUnscheduled,Number of times waited for scheduling,0
ReqTimeActive,Time spent processing requests,.7711354376044999
ReqTimeBlockContention,Time spent waiting for atomic access,0
ReqTimeBlockIO,Time spent waiting for I/O to complete,.1078449914723799
ReqTimeBlockLock,Time spent waiting for a lock,0
ReqTimeUnscheduled,Time spent unscheduled,0
Rlbk,Rollback requests handled,0
UserDefinedCounterRate01,A rate counter that is set by the database application (counter 1),0
UserDefinedCounterRate02,A rate counter that is set by the database application (counter 2),0
UserDefinedCounterRate03,A rate counter that is set by the database application (counter 3),0
UserDefinedCounterRate04,A rate counter that is set by the database application (counter 4),0
UserDefinedCounterRate05,A rate counter that is set by the database application (counter 5),0
UserDefinedCounterRaw01,A counter that is set by the database application (counter 1),0
UserDefinedCounterRaw02,A counter that is set by the database application (counter 2),0
UserDefinedCounterRaw03,A counter that is set by the database application (counter 3),0
UserDefinedCounterRaw04,A counter that is set by the database application (counter 4),0
UserDefinedCounterRaw05,A counter that is set by the database application (counter 5),0


5 comments:

Anonymous said...

Just a little contradiction:

FWIW, EBF readmes do have a "New Features" list, with the appropriate description:


New Features
This section contains a description of new features added since the release of version 12.0.1.


Nevertheless, those new properties were not introduced there but in the "Bugfixes" chapter in the "SQL Anywhere - Server" section. The "LastXyzRedoPos" properties were added with build #3493. And "LicenseKey" seems undocumented there.

So w.r.t. these properties, I would agree with your subtle rant:)

Besides that, I would think these "LastXyzRedoPos" properties are not related to Fuji but to check whether changes are really stored permanently...


Best regards
Volker

John Smirnios said...

The RedoPos properties were added for Fuji and they are not all that mysterious. With the LastCommitRedoPos, for example, a connection on a read-only scale-out node and make modifications on the primary via a remote procedure call that reports back the commit position. The read-only node can then wait for that transaction to be replicated locally.

-john.

Anonymous said...

@Unknown John (S.):

The same old story - I'm just wild guessing, you got the code:)

Thanks again for the clarification.

Regards
Volker

John Smirnios said...

I don't just have the code for those properties, I wrote it :) BTW, the other properties can be used to monitor mirroring backlog: only data that has been written at the primary is expected to replicate to the copy nodes or mirror.

It's strange that my comment shows as posted by "Unknown" since I did register... I've tweaked my profile so maybe this one will work out better.

-john.

Ivan T. Bowman said...

A number of these changes were inspired by Fuji and did ship in 12.0.1 GA as they are generally useful.

The UserDefined* properties give one a way to tie in to the counter mechanisms the server uses; they are described more fully at DCX . The interface is pretty basic (lacking, for example, an ability to assign names to counter values), but it does offer some utility and we were able to fit it in 12.0.1.

A number of the other changes you identify are also inspired by Fuji and they are a consequence of adjustments to the scope of properties such as ApproximateCPUTime. Instead of applying only to individual connections, these counters are now aggregated at the database and server levels as the sum of the property for all connections on the database or server.