Optimizing SQL Anywhere performance over a WAN

Connecting an ODBC enabled application to an SQL Anywhere Server or SQL Anywhere Standalone Engine.

Document:

　　Client/Server Environment:

　　First: Start the SA Server application: See "I" below. Second: Test Outside of ODBC. Start SA Client Application. See "II" below. Third: Test using Embedded SQL utility: ISQL. See "III" below. Fourth: Close down SA Client and SA ISQL and use ODBC setup. See "IV" below. Last: Start ODBC enabled application.

　　Standalone Environment:

　　First: Start SA Standalone Engine application: See "V" below. Second: Test Outside of ODBC. Run Embedded SQL utility: ISQL. See "III" below. Third: Close down SA ISQL and use ODBC setup. See "IV" below. Last: Start ODBC enabled application.

　　I. SQL Anywhere Server

　　See OnLineHelp (SA NetWork Guide):"Running the database server executable" and "Network communications parameters"

　　Starting SQL Anywhere Server Application from:

　　A) a command line - Enter: ...\dbsrv50?.??? -x <protocol> -n <SA server name> ...\full\path\to\<abc.db> -n <db1> ...\full\path\to\<xyz.db> -n <db2> Example (WinNT/Win95/DOS/OS2/QNX): C:\sqlany50\win32\dbsrv50.exe -x tcpip -n naMe c:\sqlany50\sademo.db -n jack d:\sqlany50\cash.db -n jill Example (Win3.x): C:\sqlany50\win\dbsrv50w.exe -x tcpip -n naMe d:\sqlany50\sademo.db -n jack d:\sqlany50\cash.db -n jill Example (Novell Netware 3.x/4.x): Load sys:\system\dbsrv50.nlm -x tcpip -n naMe sys:\system\dbsample\sademo.db -n jack sys:\system\cash\cash.db -n jill If server starts and loads database, continue with SA Client; otherwise determine why not. See OnLineHelp ( SA NetWork Guide): "Troubleshooting" 

　　B) WinNT Service - Enter: . Run ...\dbsvmn50.exe . Select 'new'. . Select Service Type...: 'Network Server' . Enter service name: <SA service name> {not PC server name} . Enter dbsrv50 parameters: . -x tcpip -n naMe c:\sqlany50\sademo.db -n jack d:\sqlany50\cash.db -n jill Path for executable: C:\sqlany50\win32\dbsrv50.exe If server starts and loads database, continue with SA Client ; otherwise determine why not. See OnLineHelp (SA NetWork Guide): "Running the database server as an NT service"

　　II. SQL Anywhere Client

　　See OnLineHelp (SA NetWork Guide): "Running the SQL Anywhere Client"

　　Starting SQL Anywhere Client Application from:

　　a command line - Enter: ...\dbclien?.exe -x <protocol>{<Optional: Network communications parameters>} <SA server name> Example (WinNT/Win95/DOS/OS2): c:\sqlany50\dbclient.exe -x tcpip{dobroadcast=no,host=<server IP>myip=<client IP>} naMe c:\sqlany50\dbclient.exe -x tcpip{dobroadcast=no,host=123.321.213.312;myip=321.123. 312.213} naMe If 'client' finds 'server', continue with SA ISQL; otherwise determine why not. See OnLineHelp (SA NetWork Guide): "Troubleshooting" and Technical Document 42396.

　　III. SQL Anywhere ISQL

　　Run ISQL/W from a command line:

　　...\isql.exe -c "uid=<xxx>pwd=<xxx>" Example (WinNT/Win95): C:\SQLANY50\win32\isql.exe -c "uid=dba;pwd=sql" In ISQL command window, enter: select * from <table_owner.table_name> execute - If data is retrieved, shut down SA Client and SA ISQL and continue with ODBC; otherwise determine why not. See OnLineHelp (SA User's Guide): "The ISQL utility"

　　IV. SQL Anywhere and ODBC

　　See OnLineHelp (SA User's Guide): "Adding an ODBC data source" and ODBC 'Help' CB on ODBC Administrator utility

　　Configure ODBC:

　　If all of the above works successfully then close 'isql' and close 'dbclient'. Setup the following ODBC configuration, by running either ...\ODBCADM.EXE (16bit) or ...\ODBCAD32.EXE (32bit). Example (WinNT/Win95): ...\odbc32ad.exe A. Click the 'add' CB. B. Double click the 'Sybase SQL Anywhere driver 5.0'. C. In the 'SQL Anywhere ODBC configuration' dialog box, enter the following information: 

　　1. Data Source Name: <dsn> 
　　2. Description: - optional- 
　　3. User ID: <uid> - May be entered elsewhere for security purposes. 
　　4. Password: <pwd> - May be entered elsewhere for security purposes. 
　　5. Server name: naMe - From SA Server or Standalone Engine command line. 
　　6. Database Name: jack or jill - From SA Server or Standalone Engine command line 
　　7. Standalone only - Database File: c:\sqlany50\sademo.db. 
　　8. Server only - Click 'Network' RB then, Click 'Custom' RB 
　　9. Standalone only - Click 'Local' RB then, Click 'Custom' RB 
　　10. Click 'Options' CB. 
　　11. Server only - Verify that 'start command window' shows the correct fully qualified path to dbclien50.exe. Complete the information so that the finalcommand looks like: ...\dbclien?.exe -x <protocol>{<Optional: Network communications parameters>} Example (WinNT/Win95): c:\sqlany50\dbclient.exe -x tcpip{dobroadcast=no,host=<server IP>myip= <client IP>} c:\sqlany50\dbclient.exe -x tcpip{dobroadcast=no,host=123.321.213.312;myip= 321.123.312.213} 
　　12. Standalone only -Verify that 'start command window' shows the correct fully qualified path to dbeng50?.exe. Complete the information so that the finalcommand looks like: ...\dbeng50?.??? -n <SA engine name> ...\full\path\to\<abc.db> -n <db1> ...\full\ path\to\<xyz.db> -n <db2> Example (WinNT/Win95/DOS): C:\sqlany50\win32\dbeng50.exe -n naMe c:\sqlany50\sademo.db -n jack d:\sqlany50\cash.db -n jill 
　　13. Click 'OK' CB until out of ODBC administrator (usually 3 times) D. Make any necessary adjustments to the ODBC enabled application, then run it.

　　V. SQL Anywhere Standalone Engine

　　See OnLineHelp (SA User's Guide): "The database engine"

1. Starting SQL Anywhere Standalone Application from:

　　a command line - Enter: ...\dbeng50?.??? -n <SA server name> ...\full\path\to\<abc.db> -n <db1> ...\full\path\to\<xyz.db> -n <db2> Example (WinNT/Win95/DOS): C:\sqlany50\win32\dbeng50.exe -n naMe c:\sqlany50\sademo.db -n jack d:\sqlany50\cash.db -n jill Example (Win3.x): C:\sqlany50\win\dbeng50w.exe -n naMe d:\sqlany50\sademo.db -n jack d:\sqlany50\cash.db -n jill

2. Run ISQL/W from a command line:

　　See III above.

3. Configure ODBC:

　　See IV above. 

　　NOTE: These are basic examples. Please see ODBCADM.EXE / ODBCAD32.EXE OnLineHelp and SQL Anywhere OnLineHelp for detailed information