In-Memory Downloading from SQL Server (3)

This is Part 3 of an article written in March 2010 with the title "Downloading Data into a SQL Anywhere In-Memory Database from Microsoft SQL Server".

Part 1 is here.

Part 2 is here.

---

Technique 3: Download Via Proxy Table

Remote data access was introduced in SQL Anywhere version 6 over 10 years ago. This feature allows you to create the schema for a table inside a SQL Anywhere database without actually storing the data inside that database. This is called a "proxy table" because it represents data that is actually stored in some other location: an Excel spreadsheet, an Oracle or DB2 database, a text file, or, in the case of this article, a SQL Server database.

Proxy tables are surprisingly powerful and easy to use; all you need is ODBC access to the actual data, and a few lines of SQL code, and you're all set. Figure 18 shows all the code necessary for SQL Anywhere to connect to SQL Server, create a proxy table that represents the remote table mss_source, and run an INSERT SELECT to copy all the data to the local (real, non-proxy) table sa_target.

Figure 18: Download Via Proxy Table
Context: SQL Anywhere

CREATE SERVER mss 
CLASS 'MSSODBC' 
USING 'DSN=main_BRECK-PC';

CREATE EXTERNLOGIN DBA  
TO mss  
REMOTE LOGIN "sa" 
IDENTIFIED BY 'j68Fje9#fyu489';

CREATE EXISTING TABLE proxy_mss_source  
AT 'mss.main.dbo.mss_source';

INSERT sa_target
SELECT *
FROM proxy_mss_source;

Line 1 in Figure 18 creates a remote server schema object in the SQL Anywhere database and gives it the local name mss.

Line 2 specifies the access path or server "class" to be used. Currently there are 12 different classes available in SQL Anywhere, ranging from vanilla ODBC class for Excel, text files, etc., to Oracle-via-ODBC and Sybase ASE-via-JDBC. In this case, the MSS-via-ODBC class MSSODBC is the best choice.

Line 3 specifies the ODBC DSN to be used. It is possible to specify a DSN-less connection by putting ODBC driver information in the USING clause, but in this case the DSN shown in Figure 19 is used.

Lines 5 through 8 define the "external login" path that SQL Anywhere will use behind the scenes to connect to SQL Server: the local user id DBA will be used to connect to the remote server earlier given the name mss, with the SQL Server user id and password given by the REMOTE LOGIN and IDENTIFIED BY clauses.

Lines 10 begins the definition of the proxy table named proxy_mss_source. The EXISTING clause specifies that the real table already exists on SQL Server so it doesn't need to be created over there, just the proxy table here on SQL Anywhere.

The AT clause on line 11 identifies the real table on SQL Server. The first parameter, the remote server name mss, identifies the CREATE SERVER access path and by implication the CREATE EXTERNLOGIN login path. The remaining three parameters name the SQL Server database main, the table owner dbo, and the real table mss_source.

Lines 13 through 15 show how all the rows from mss_source may be retrieved via the proxy_mss_source table and inserted into the real SQL Anywhere table called sa_target. Like the previous two techniques (LOAD TABLE and MobiLink), the INSERT in Figure 18 runs as a single transaction, gathering and then releasing 1.9 million row locks in SQL Anywhere.

Figure 19 shows the user DSN that is stored in the registry on the laptop computer. Note that except for being stored in the registry of a different computer, this DSN is identical to the one used for MobiLink in the previous section; see Figure 14.

Figure 19: ODBC User DSN for Proxy Table
Context: SQL Server

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\main_BRECK-PC]
"Driver"="C:\\Windows\\system32\\sqlncli10.dll"
"Server"="BRECK-PC\\TSUNAMI"
"Database"="main"
"LastUser"="sa"

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources]
"main_BRECK-PC"="SQL Server Native Client 10.0"

-- [to be continued] --