Friday, April 4, 2014

Example: CREATE EXISTING Proxy Table On HANA

Question: How do I create a SQL Anywhere remote access proxy table for an actual table on HANA?

Answer: The following code was tested on SQL Anywhere 16.0.0.1823, HANA 1.00.68, and the HDBODBC 1.00.70 ODBC driver.

Here's a command that lets you use SQL Anywhere 16's ISQL utility with HANA:

"%SQLANY16%\bin64\dbisql"^
  -c "DRIVER=HDBODBC; HOST=breckcarterhana.vm.cld.sr:30015; USER=SYSTEM; PASSWORD=manager;"^
  -hana
Note: There is nothing at all about that command line that's the same with a SQL Anywhere database:
  • The DRIVER is different,

  • the HOST is different (in this case pointing to a HANA "vanity URL" and port on Cloudshare),

  • the USER and PASSWORD keywords are different, as are the values (the HANA equivalent of DBA/sql), and

  • the special dbisql -hana option is different.
You can use a DSN instead of the DRIVER and HOST, but this is a DSN-free article.

Here's a simple table on HANA:
CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

INSERT INTO t VALUES ( 1, 2 );
INSERT INTO t VALUES ( 2, 2 );
COMMIT;

SELECT * FROM SYSTEM.t ORDER BY pkey;

PKEY,DATA
1,2
2,2


Here are the commands to create a SQL Anywhere 16 database:
"%SQLANY16%\Bin64\dbinit.exe"^
  ddd16.db

"%SQLANY16%\Bin64\dbspawn.exe"^
  -f "%SQLANY16%\Bin64\dbeng16.exe"^
  -o dbeng16_ddd16_log.txt^
  ddd16.db 

"%SQLANY16%\Bin64\dbisql"^
  -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql; CON=ddd16"

Here are the SQL statements to set up a proxy table on SQL Anyhere:
CREATE SERVER HANA_server CLASS 'HANAODBC' 
   USING 'DRIVER=HDBODBC; ServerNode=breckcarterhana.vm.cld.sr:30015;';

CREATE EXTERNLOGIN DBA 
   TO HANA_server 
   REMOTE LOGIN "SYSTEM" IDENTIFIED BY 'manager';

CREATE EXISTING TABLE proxy_t 
   AT 'HANA_server..SYSTEM.T';

SELECT * FROM proxy_t ORDER BY proxy_t.pkey;

PKEY,DATA
1,2
2,2
  • The USING clause is written in HANA-speak (ServerNode versus HOST) but otherwise it looks just like the DSN-less connection string in the earlier dbisql command.

  • The CREATE EXTERNLOGIN statement points at the SYSTEM user on HANA.

  • The CREATE EXISTING TABLE statement points at the SYSTEM.T table on HANA (note the uppercase T; more on this later).


Tip: If you get an error like the following, try switching from the 32-bit version of SQL Anywhere to the 64-bit version, or vice versa, or download the "other bit-ness" version of the HANA ODBC driver.
Could not execute statement.
Unable to connect to server 'HANA_server': [Microsoft][ODBC Driver
Manager] The specified DSN contains an architecture mismatch between the
Driver and Application
SQLCODE=-656, ODBC 3 State="HY000"

Tip: If you get an error like the following, try changing the table name to upper case in the CREATE EXISTING ... AT clause; e.g., from this 'HANA_server..SYSTEM.t' to this 'HANA_server..SYSTEM.T'.
There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Server 'HANA_server': [SAP AG][LIBODBCHDB DLL][HDBODBC] Base table or
view not found;259 invalid table name:  Could not find table/view t in
schema SYSTEM: line 1 col 42 (at pos 41)
SQLCODE=-660, ODBC 3 State="HY000"

You shouldn't HAVE to make that change, and by the time you read this maybe you don't. Here's the explanation:
  • Identifiers in HANA are case-insensitive unless they are enclosed in "double quotes" in which case they become case sensitive.

  • Table names in HANA are converted to upper case when they are stored in the system catalog.

  • If you code AT 'HANA_server..SYSTEM.t' SQL Anywhere currently sends the SELECT to HANA like this: SELECT t1."PKEY" , t1."DATA" FROM SYSTEM."t" t1
. . . and voila! "invalid table name: Could not find table/view t"


No comments: