Wednesday, July 9, 2014

Synchronization with HANA

This article seeks to prove the following bold claim:

It's easy to set up MobiLink to synchronize a SQL Anywhere database with HANA.
Yes, it IS easy, but not easy like walking to the kitchen and back, more like walking to work. In other words, there are a LOT of little steps, each one of them easy, but there's still a lot of them.
[...continued]


Monday, July 7, 2014

Hurry! Book d-code now! (or not)

The Conference-Formerly-Known-As-TechEd is now called d-code and it's scheduled for October 20–24 in Las Vegas... and Berlin in November, Bangalore in December.

For the Early Bird bargain of US $2,495 you get four (4) hours of SQL Anywhere and MobiLink...

DMM106 Introduction to SAP SQL Anywhere
Jason Hinsperger
SAP SQL Anywhere can be used as an effective companion to your current enterprise database to build mobile and embedded applications. This session includes an introduction to SAP SQL Anywhere and provides information on the compatibilities and differences with other SAP data management solutions including SAP HANA, SAP IQ, and SAP ASE.

DMM116 From Raspberry Pi to SAP HANA – SAP SQL Anywhere and the Internet of Things
Tom Slee
SAP HANA provides real-time analytics on data collected from large-scale networks in “Internet of Things” deployments. New single-board computers such as the popular Raspberry Pi provide easy and affordable ways to acquire data. This session explains how SAP SQL Anywhere links SAP HANA with a myriad of devices, with a focus on predictive maintenance.

DMM114 Synchronizing Data in SAP HANA Using SAP SQL Anywhere
Jason Hinsperger
By enabling mobile and remote applications to synchronize data with SAP HANA, companies gain a significant competitive advantage as decisions can be made using real-time available information. Learn how to design and implement a solution that acquires data from a wide range of applications and synchronizes that data to SAP HANA.

EA111 Enabling High Availability for the SAP BusinessObjects CMS Repository
Jose Ramos
High availability (HA) is vital for enterprise applications to ensure users do not experience costly downtime when a system fails. SAP SQL Anywhere is used as the embedded Central Management Server (CMS) database in SAP BusinessObjects. This session introduces you to SAP SQL Anywhere's HA functionality and implementing HA for the CMS repository.



OK, so maybe $2,495 is a bit [cough] steep for half a day with Jason, Tom and Jose...

...but wait!

A little bird told me there's going to be another SQL Anywhere Technical Summit just like there was two years ago.

Friday, July 4, 2014

Latest SQL Anywhere Updates: 16 for Linux, 11/12/16 for Windows

Current builds for the active platforms...

HP-UX     16.0.0.1824 (EBF 22791 SP11)        23 Apr 2014
 Itanium  12.0.1.4086 (EBF 22823 SP71)        24 Apr 2014
          11.0.1.3113 (EBF 22590 SP100)       18 Mar 2014     End of Life 31 May 2014

IBM AIX   16.0.0.1824 (EBF 22867 SP11)        25 Apr 2014   
          12.0.1.4086 (EBF 22955 SP71)        02 May 2014
          11.0.1.3113 (EBF 22660 SP100)       01 Apr 2014     End of Life 31 May 2014

Linux     16.0.0.1911 (EBF 23040 SP13)    *** 22 May 2014 ***
          On Demand 1.0.4613 (SP3)            13 Sep 2013
          12.0.1.4110 (EBF 23039 SP74)     *  21 May 2014  *
          12.0.1 Chinese,                     16 Apr 2013
                 Japanese Docs (Eclipse)      16 Apr 2013
          11.0.1.3113 (EBF 22587 SP100)       18 Mar 2014     End of Life 31 May 2014

Mac OS    16.0.0.1894 (EBF 22965 SP12)        09 May 2014
          12.0.1.4106 (EBF 22966 SP73)        12 May 2014
          11.0.1.2449 Update                  29 Jun 2010     End of Life 31 May 2014

Solaris   16.0.0.1911 (EBF 22980 SP13)     *  16 May 2014  *
 SPARC    12.0.1.4086 (EBF 22824 SP71)        28 Apr 2014
          11.0.1.3113 (EBF 22661 SP100)       01 Apr 2014>    End of Life 31 May 2014

Solaris   16.0.0.1824 (EBF 22792 SP11)        23 Apr 2014
 x64      12.0.1.4086 (EBF 22825 SP71)        28 Apr 2014
          11.0.1.3113 (EBF 22591 SP100)       18 Mar 2014     End of Life 31 May 2014

Windows   16.0.0.1915 (EBF 23041 SP14)    *** 22 May 2014 ***
          On Demand 1.0.4613 (SP3)            13 Sep 2013
          12.0.1.4124 (EBF 23182 SP75)    *** 26 Jun 2014 ***
          12.0.1 French,                      25 Sep 2012
                 English,                     25 Sep 2012
                 German,                      25 Sep 2012
                 Chinese,                     28 Mar 2013
                 Japanese Docs (HTML/PDF)     28 Mar 2013
          11.0.1.3158 (EBF 23111 SP101)   *** 26 Jun 2014 *** End of Life 31 May 2014 

Other Stuff...

 Older Updates

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

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

 Buy SQL Anywhere 

 Developer Edition... 
   [16.0] [12.0.1(?)] [11.0.1]

 Download the Educational Edition 

 Supported Platforms...
   SQL Anywhere 
   Linux 
   OnDemand

 ODBC Drivers for MobiLink

The 3 asterisks "***" mark items have appeared on the Sybase website since the previous version of this page.

The single asterisks "*" mark items that first appeared on 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.

  • 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.

Saturday, June 14, 2014

Getting Started With TRANTEST

TRANTEST is a high-speed database performance testing utility delivered in the box with SQL Anywhere since version 8.

Wait! What?

It's not your fault you've never heard of TRANTEST. That's because the phrase "delivered in the box" isn't accurate.

First of all, there is no "box", and even if there was, TRANTEST would be "hidden in the box"... you'd have to turn the box upside down and bang on the side and it might fall out.

Keyword "might".

The other reason you've never heard of TRANTEST is that almost nobody has written about it.

... and the reason for that is probably because almost nobody can figure out how to use it. The original author, sure, maybe one or two other people... but the docs are thin and the code is [cough] inscrutable ...

... which might explain why there have been no enhancements in 13 years :)

Enough whining!


This article is at least ten years overdue. That's about when I first tried to make TRANTEST work, and failed, and tried and failed again and again every few years.

This time, it's different.

This time, I was motivated.

This time, I was looking for more test cases to run against Foxhound, including a low-overhead alternative to a fat PowerBuilder application previously used to simulate heavy workloads.

So this time, I got TRANTEST to work (yay!) using Mark Culp's post as a starting point.

TRANTEST Step-By-Step


Here's a simple step-by-step demonstration of TRANTEST:

Step 1. Create a working folder to hold all the TRANTEST and related files.

This step is optional, but it simplifies test setup and protects the original TRANTEST files from accidental damage.

Step 2. Copy all the TRANTEST files ...
from where they are stored in your SQL Anywhere 16 setup
C:\Users\Public\Documents\SQL Anywhere 16\Samples\SQLAnywhere\PerformanceTransaction
to your working folder.

Tip: Be sure to have a look at the readme.txt file, but don't rely on it to explain much everything... and ignore the ancient exhortations like "Don't run with too many threads".

Step 3. Create and start an empty SQL Anywhere 16 database to use for the demonstration.

"%SQLANY16%\bin64\dbinit.exe"^
  ddd16.db

"%SQLANY16%\bin64\dbspawn.exe"^
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  -o dbsrv16_log_ddd16.txt^
  -os 10M^
  ddd16.db 

Step 4. Apply the trantabs.sql script to the target database.

TRANTEST needs its own tables on the target database, and this is the script that creates them:

"%SQLANY16%\bin64\dbisql"^
  -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql;"^
  READ ENCODING Cp1252 trantabs.sql

Step 5. Create the custom procedures to be executed by TRANTEST.

In this simple demonstration, TRANTEST will use three different threads to call three different procedures simultaneously and repeatedly:

CREATE TABLE t (
   pkey         INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   inserted_by  VARCHAR ( 10 ) NOT NULL,
   inserted_at  TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP );

CREATE PROCEDURE p1()
BEGIN
   INSERT t ( inserted_by ) VALUES ( 'p1' );
   COMMIT;
END;

CREATE PROCEDURE p2()
BEGIN
   INSERT t ( inserted_by ) VALUES ( 'p2' );
   COMMIT;
END;

CREATE PROCEDURE p3()
BEGIN
   INSERT t ( inserted_by ) VALUES ( 'p3' );
   COMMIT;
END;

Step 6. Create the TRANTEST -f script to call the custom procedures.

In this simple demonstration, TRANTEST will substitute the thread numbers 1, 2, 3 for the string {thread} in the following script file simple_test.sql, thus generating calls to P1, P2 and P3:

call P{thread}()

Step 7. Run TRANTEST.

As with most SQL Anywhere utilities, default values have been carefully chosen for most options and it's better to accept those defaults if you're not exactly sure what they're for. In TRANTEST's case, this means only 4 command-line switches need explicit values:

TRANTEST Command-Line Switches                  Default / Required 
----------------------------------------------  ------------------
-a <api>  API to use (ESQL or ODBC)             required          
-c <str>  connection string                     required
-d        display per-thread statistics         do not display
-f <file> SQL script file                       required
-g <num>  thread group id                       0
-i <num>  isolation level                       0
-k <num>  commit after 'num' transactions       0
-l <num>  limit for test in secs                300
-m <num>  mean time between transactions for    0 
          each thread (ms)
-n <list> number of threads to run at each      required
          iteration (e.g. -n 1,5,10,20)
-o <file> output results to file                no output
-p <num>  number of machines (thread groups)    0
-r <num>  display refresh rate (seconds)        5
-t <str>  name of test to run                   empty 
-u <num>  maximum desired response time (secs)  2
-w <num>  warm-up time (secs)                   0 
-x        prepare and drop on each execution    prepare once

Here's what the TRANTEST command looks like for this simple demonstration:

trantest^
  -a esql^
  -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql; CON=TRANTEST;"^
  -f simple_test.sql^
  -n 3

PAUSE

Here's the output when both TRANTEST and the SQL Anywhere 16 engine were run on an Intel Core i7 laptop (8 processors):



Tip: Don't specify TRANTEST -p if you're only using one computer. Otherwise, you'll see nothing but endless "Waiting for other machines..." messages.

Tip: Press Ctrl+C to stop TRANTEST, and use a PAUSE command to keep the TRANTEST window from immediately disappearing when you to press Ctrl+C.

Step 8. Create a "heavy load" version of the custom procedure to be executed by TRANTEST.

You don't need 1000 procedures to make TRANTEST use 1000 connections, you can use a single procedure and have TRANTEST pass the thread number as a parameter:

CREATE PROCEDURE p ( thread_number VARCHAR ( 10 ) )
BEGIN
   INSERT t ( inserted_by ) VALUES ( thread_number );
   COMMIT;
END;

Step 9. Create the "heavy load" version of the TRANTEST -f script.

In the following file heavy_load.sql, TRANTEST uses the thread numbers 1 to 1000 to generate different parameter values rather than different procedure names:

call p ( '{thread}' )

Step 10. Run TRANTEST again.

In this heavy load test, the -m 10 switch adds a 10 millisecond delay between repetitions, and the -n 1000 switch requests 1000 connections to the target database:

trantest^
  -a esql^
  -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql; CON=TRANTEST;"^
  -f heavy_load.sql^
  -m 10^
  -n 1000

PAUSE

Here's what the TRANTEST output looked like:



Here's what the test looked like in Foxhound:



Here are some observations:
  • Throughput increased dramatically during the test as SQL Anywhere increased the Max Req (MultiProgrammingLevel property) from 8 to 38. TRANTEST shows throughput as T/sec, and Foxhound shows it as Req, Commits, Bytes (Req, Commit, BytesReceived and BytesSent properties).

  • The sudden drop in #Trans from 24,627 to 6 in the TRANTEST output probably coincided with a checkpoint, but it's difficult to prove because TRANTEST doesn't show date and time.

  • It's a bad idea to run everything on one computer (SQL Anywhere, TRANTEST and Foxhound), especially with 1000 busy connections. The Interval column shows that Foxhound was unable to gather data in a timely fashion (i.e., every 10 seconds), and the Sample Times column shows why: Foxhound needed up to 43.5 seconds to gather a single sample.

  • A bad idea sometimes makes a good stress test, which is the whole point of this exercise :)
Calvin and Hobbes November 26, 1986