Friday, August 26, 2016

Downgrading A Database From SQL Anywhere 17 to 16

Question: How do I switch to SQL Anywhere 16 after upgrading my database to version 17?

Answer: The bad news is, there's no dbunload -downgrade option.

The good news is, it might not be very difficult, at least according to a preliminary test using the SQL Anywhere 17 demo database:

  • Step 1. Start the current database with V17 dbsrv17.exe

  • Step 2. Unload the current database with V17 dbunload.exe

  • Step 3. Copy the V17 reload file to the V16 folder

  • Step 4. Manually edit the V16 reload file

  • Step 5. Create the new database with V16 dbinit.exe

  • Step 6. Start the new database with V16 dbsrv16.exe

  • Step 7. Open an ISQL session with V16 dbisql.com

  • Step 8. Run the edited V16 reload file in ISQL

  • Repeat as required, possibly starting at Step 4

"Can I try this at home?"

This demo does not address the following questions...
  1. "How do I undo the changes I had to make when upgrading to SQL Anywhere 17?"

  2. "Will the new objects (e.g., roles) cause problems in SQL Anywhere 16?"

  3. "Will the column statistics work properly?"

  4. "What about the MobiLink stuff?"

    "Have I forgotten anything? Oh, yeah..."

  5. "Will this work for my giant production database?"
Here's the batch file used for testing, followed by some comments and another batch file to clean up before starting over:
ECHO OFF
REM  ******************************** 
ECHO Step 0. Set up folders and files
C:
CD C:\
MD TEMP
CD C:\TEMP
MD data
CD C:\TEMP\data
MD V16
MD V17
COPY /B /V /Y "%SQLANYSAMP17%\demo.db" "C:\TEMP\data\V17\demo.db"
COPY /B /V /Y "%SQLANYSAMP17%\demo.log" "C:\TEMP\data\V17\demo.log"
PAUSE

REM  ******************************************************* 
ECHO Step 1. Start the current database with V17 dbsrv17.exe
"%SQLANY17%\bin64\dbspawn.exe"^
  -f "%SQLANY17%\bin64\dbsrv17.exe"^
  -n demo17^
  -o "C:\TEMP\data\V17\dbsrv17_log_demo17.txt"^
  "C:\TEMP\data\V17\demo.db"
PAUSE

REM  ********************************************************* 
ECHO Step 2. Unload the current database with V17 dbunload.exe
"%SQLANY17%\Bin64\dbunload.exe"^
  -c "ENG=demo17; DBN=demo; UID=dba; PWD=sql;"^
  -r "C:\TEMP\data\V17\reload17.sql"^
  -up^
  "C:\TEMP\data\V17\data17"
PAUSE

REM  ************************************************** 
ECHO Step 3. Copy the V17 reload file to the V16 folder
COPY /V /Y "C:\TEMP\data\V17\reload17.sql" "C:\TEMP\data\V16\reload16.sql"
PAUSE

REM  ***************************************** 
ECHO Step 4. Manually edit the V16 reload file
ECHO [edit reload16.sql]
PAUSE

REM  *************************************************** 
ECHO Step 5. Create the new database with V16 dbinit.exe
"%SQLANY16%\bin64\dbinit.exe"^
  -p 4096^
  "C:\TEMP\data\V16\demo.db"
PAUSE

REM  *************************************************** 
ECHO Step 6. Start the new database with V16 dbsrv16.exe
"%SQLANY16%\bin64\dbspawn.exe"^
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  -n demo16^
  -o "C:\TEMP\data\V16\dbsrV16_log_demo16.txt"^
  "C:\TEMP\data\V16\demo.db" 
PAUSE

REM  ************************************************ 
ECHO Step 7. Open an ISQL session with V16 dbisql.com
"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=demo16; DBN=demo; UID=dba; PWD=sql;"
PAUSE

REM  **********************************************
ECHO Step 8. Run the edited V16 reload file in ISQL
ECHO [copy, paste and run reload16.sql]
PAUSE

REM  *************************** 
ECHO Repeat as required
ECHO [you might be able to start at Step 4]
ECHO All done...
PAUSE
The dbunload -up option in Step 2 is new with SQL Anywhere 17:
New -up option for the Unload utility (dbunload) and 
   the Extract utility (dbxtract) 

This option allows the unloading of passwords. There are  
   behavior changes associated with its use. See UNLOAD  
   utility, and Extract Utility (dbxtract).

-up 

Unloads user passwords (hashed value). You do not need to 
   specify this option if you are performing an unload with  
   reload (-ac, -an, or -ar option).
If you don't specify dbunload -up in Step 2 the following text in the reload SQL file will stop Step 8 from working:
****************** WARNING *******************************
*                                                        *
* This file contains user definitions with removed       *
* password values.                                       *
* It should not be used to create a new database.        *
*                                                        *
****************** WARNING *******************************
If you want to avoid this error in Step 8:
Could not execute statement.

User ID 'SYS_RUN_PROFILER_ROLE' does not exist
SQLCODE=-140, ODBC 3 State="28000"
Line 157, column 1
You can continue executing or stop.

GRANT DELETE ANY TABLE TO "SYS_RUN_PROFILER_ROLE" WITH NO ADMIN OPTION
...you will have to remove these lines in Step 4:
GRANT DELETE ANY TABLE TO "SYS_RUN_PROFILER_ROLE" WITH NO ADMIN OPTION
go
...or you can just choose "Continue" after each error in Step 8 :)

The -p 4096 in Step 5 is the default... it's just a reminder that when you run the "Old School" method using separate dbunload and dbinit steps, you are on your own when it comes to dbinit options.

Here's the batch file to clean up after running the demo:
ECHO OFF
ECHO Are you sure you want to delete all the files?
PAUSE 

"%SQLANY16%\bin64\dbstop.exe"^
  -c "ENG=demo16; DBN=demo; UID=dba; PWD=sql;"^
  -y

"%SQLANY17%\bin64\dbstop.exe"^
  -c "ENG=demo17; DBN=demo; UID=dba; PWD=sql;"^
  -y

ECHO Wait until the databases are stopped, then
PAUSE 

RD /S /Q C:\TEMP\data\V16
RD /S /Q C:\TEMP\data\V17

ECHO All done...
PAUSE


Wednesday, June 8, 2016

Foxhound Version 4 Is Coming Later This Summer

Foxhound is a third-party database performance monitor for SAP® SQL Anywhere®.

Foxhound Version 3 has been around for a while, and Version 4 ... will ... may ... should be available later this summer :)

Here's a teeny-tiny excerpt from the full What's New:

  • Foxhound Version 4 will support target databases running on SQL Anywhere 17...

  • ...including SQL Anywhere 17.0.4. Foxhound 4 will also support target databases running on SQL Anywhere versions 5.5 through 16, but so does Foxhound 3 now.

  • Foxhound 4 itself will run on either SQL Anywhere 16 or 17.

  • When Foxhound 4 itself is running on SQL Anywhere 16 it will still handle a target database running on SQL Anywhere 17.



Foxhound 4 Will Be FREE! ( Some Restrictions Apply :)


If you're thinking you need a performance monitor for SQL Anywhere, one that actually works, don't hold back...

From this date forward, if you purchase Foxhound 3, you'll be able to upgrade to Version 4 at no charge as soon as it's available.

Dilbert.com 1996-04-21


Monday, June 6, 2016

SQL Anywhere 17.0.4 Is Now Available

SQL Anywhere 17.0.4 is a way cool upgrade to SQL Anywhere 17.

The announcement is on the forum here.

The What's New is in DCX here.

The download is located

  • somewhere on sap.com (see editor's note)

  • as an EBF called 17.0.4.2053

  • in the file SQLANYW170000P_8-71001031.ZIP

  • with the title SQL Anywhere 17.0 SP0 PL8 Build 2053

  • and the release date 02.06.2016.


Editor's Note: Management apologizes for the sarcastic comment "somewhere on sap.com".

Life's too short to keep track of where anything is on the SAP website, let alone keep up with link rot.


Saturday, May 28, 2016

Buy SQL Anywhere 17 on Amazon

It's hard to believe, but true: search amazon.com on "SQL Anywhere Edge" (without the quotes) and you see this:

  • Don't try this outside the US or UK... it won't work in the South Sudan, Yemen, Canada... ha, ha, the Canadian developers of SQL Anywhere aren't allowed to buy a copy :)

  • The original May 25 announcement is here.

  • The screenshot says "Jul 15, 2015" but if drill down to the product descriptions they say "Date first available at Amazon.com: May 11, 2016".

  • FWIW the screenshot shows the Vivaldi browser in action... every time I think "That doesn't work in Vivaldi!" it turns out "My mistake, lemme try again!" :)

  • I don't know if the SQL Anywhere Edge prices include an SAP S-user id which is necessary to gain access to EBFs and such-like. I'm guessing "No" because the product description doesn't say "Yes".

  • I don't know if the prices require an extra fee for "support", together with increasingly-shrill dunning emails if you don't pay the second-year renewal. I'm guessing "No" because it's a moot point without an S-user id.

...but hey! It's SQL Anywhere on Amazon, and that's way cool!