1 / 74

EM413 Using Adaptive Server Anywhere’s Remote Data Access Feature

EM413 Using Adaptive Server Anywhere’s Remote Data Access Feature. Mark Culp Software Development Manager iAnywhere Solutions mark.culp@ianywhere.com. Enable you to access remote data from your ASA database Show you how to migrate your data to ASA

shepry
Download Presentation

EM413 Using Adaptive Server Anywhere’s Remote Data Access Feature

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. EM413Using Adaptive Server Anywhere’s Remote Data Access Feature • Mark Culp • Software Development Manager • iAnywhere Solutions • mark.culp@ianywhere.com

  2. Enable you to access remote data from your ASA database Show you how to migrate your data to ASA Help you improve your cross database join performance What should you not do when using ASA's remote data access feature Goals of this presentation

  3. What is “Remote Data Access” ? Basics – How to get started What if it doesn't work? – Some helpful hints Data migration tools Cross database joins Limitations – What to avoid Questions Contents

  4. What is “Remote Data Access” ?

  5. Allows an ASA server to access data from a remote source as if it were local Remote data may be : iAnywhere ASA, Sybase ASE Oracle, IBM DB2, MS SQL Server Other ODBC data sources E.g. MS Access, MS Excel What is “Remote Data Access” ?

  6. Cross database joins Maintain real time access to remote data Integration to other projects and other data sources Migration of legacy data Quickly set up a remote ASA database for Mobilink Why use “Remote Data Access” ?

  7. Remote Server ASA Server ASA jConnect Client Comm. Query Execution Engine Remote Store ODBC ASE ASA Store Other What is … - Architecture

  8. Available on Win32 platforms: 95,98,ME,NT,2000 UNIX platforms (since ASA 7.0.2): Linux and SPARC Solaris Netware (since ASA 7.0.2) What is … - Platforms

  9. Remote Data Access capability is also known as: OMNI - OmniConnect (from ASE 10.5) CIS - Component Integration Services Proxy Tables What is … – Also known as …

  10. Basics – How to get started

  11. Basics • Three steps to accessing remote data • Define your remote server • Map your external login • Create your remote proxy tables Then use the proxy tables in the same way you would use any other table in your query

  12. ASA needs to know: What you will call the remote server How to connect to the remote server The capabilities of the remote server Basics – Step 1:Define your remote server SQL statement: create server <server_name> class <connection_class> using <remote_location>

  13. Connection class: JDBC (jConnect) classes: asajdbc, asejdbc ODBC classes: asaodbc, aseodbc – ASA and ASE db2odbc – IBM DB2 mssodbc – MS SQL Server oraodbc – Oracle server (version 8.0 or later) odbc – any other ODBC data source Step 1 – Create server … class

  14. Remote location (JDBC): <machine_name>:<port> [ /<db_name> ] Location appended to JDBC URL specification: 'jdbc.sybase.TDS.' /<db_name> only required if more than one database loaded on remote server Step 1 – Create server … location (JDBC)

  15. Example 1 (using jConnect): create server DemoServerViaJconnect class 'asajdbc' using 'localhost:2638/asademo' Remote server: ASA server: asademo DemoServer ViaJconnect asajdbc localhost :2638 /asademo jConnect Step 1 – Create server … example (JDBC)

  16. Remote location (ODBC): <DSN_name> [ ; <other_options> ] DSN (data source name) normally specifies parameters on how to find remote server Driver name Driver specific parameters: E.g. ServerName Connection is in context of the ASA server Step 1 – Create server … location (ODBC)

  17. Example 2 (using an ODBC DSN): create server DemoServer class 'asaodbc' using 'demoDSN' Remote server: ASA server: ODBC Driver Manager asaodbc demoDSN asademo DemoServer ODBC Driver Eng= asademo Step 1 – Create server … location (ODBC) Note: You can use dbdsn to create ASA DSNs: dbdsn –w demoDSN –c “eng=asademo”

  18. Example 3 (connect to an Excel Spreadsheet): create server ExcelData class 'odbc' using 'ExcelDataDSN' Note 'Read Only' Check Box Step 1 – Create server … location (ODBC)

  19. Example 4 (no ODBC DSN): create server DemoServerNoDSN class 'asaodbc' using 'driver=Adaptive Server Anywhere 8.0; ServerName=asademo' Remote server: ASA server: ODBC Driver Manager asaodbc Driver=… asademo DemoServer NoDSN ODBC Driver Server Name= asademo Step 1 – Create server … location (ODBC)

  20. Remote location (UNIX ODBC): <DSN_name> [ ; driver=<driver_path> ] There is no standard ODBC driver manager on UNIX, so ASA needs to know where to find the driver (manager) that you want to use. You must specify a driver-path either: In your location string, or In your file DSN specification Step 1 – Create server … location (UNIX)

  21. Example 5 (UNIX - no driver manager): create server DemoServerDirect class 'asaodbc' using 'demoDSN' Contents of ~/.odbc.ini: [demoDSN] driver=/opt/sybase/SYBSsa8/lib/dbodbc8_r.so servername=asademo Step 1 – Create server … location (UNIX)

  22. Example 5 continued (UNIX - no driver manager): Since no "driver=" found in location string, ASA looks for location of driver in the DSN Remote server: ASA server: asademo DemoServer Direct ODBC Driver asaodbc demoDSN dbodbc8_r.so Step 1 – Create server … location (UNIX)

  23. Example 6 (UNIX - with driver manager): create server DemoServerUsingManager class 'asaodbc' using 'driver=<manager_path>; DSN=demoDSN' Contents of ~/.odbc.ini: [demoDSN] driver=/opt/sybase/SYBSsa8/lib/dbodbc8_r.so servername=asademo Step 1 – Create server … location (UNIX)

  24. Example 6 continued (with driver manager): ASA loads ODBC driver manager ODBC driver manager loads ODBC driver Remote server: ASA server: asademo DemoServer UsingManager ODBC Driver Manager ODBC Driver asaodbc .odbc.ini driver=… Server Name= asademo dbodbc8_r.so Step 1 – Create server … location (UNIX)

  25. ASA may need to know: User id and password to use when connecting to the remote server Basics – Step 2:Map your external login SQL statement: create externlogin <login_name> to <remote_server> remote login <remote_user> [ identified by <remote_password> ]

  26. By default, ASA uses the names and passwords of its clients whenever it connects to a remote server on behalf of those clients. CREATE EXTERNLOGIN statement assigns an alternate login name and password to be used when communicating with a remote server Step 2 – When to create externlogin

  27. Example 7: create externlogin dba to DemoServer remote login dba identified by sql Step 2 – Map your external login

  28. ASA needs to know how to make the mapping from a local proxy table to its remote data: If the remote table already exists, use the CREATE EXISTING statement. If the remote table is to be created, use the CREATE … AT statement. Basics – Step 3:Create proxy tables

  29. To map an existing remote table: SQL Statement: create existing table [<owner>.]<table_name> [ ( <column_definitions> ) ] at <location_clause>; This statement defines the proxy table for an existing table on the remote server. Step 3 – Create existing table

  30. location_clause specifies where to locate the remote table, and is of the form: '<server>;<database>;<owner>;<table>' where: server - name of server as known by current database from create server command database - depends on type of remote server owner - owner of remote table table – name of the remote table Step 3 – Location clause

  31. Example 8: create existing table dba.p_employee at 'DemoServer;;dba;employee'; Step 3 – Create existing example ASA local database: DemoServer: proxy table: remote table: dba. p_employee dba. employee

  32. To create a new remote table: SQL Statement: create table [<owner>.]<table_name> ( <column_definitions> ) at <location_clause>; This statement creates a new table on the remote server, and also defines the proxy table for that table. Step 3 – Create new table

  33. Example 9: create table p_employee_sheet ( emp_id numeric(9,0), emp_fname varchar(30) ) at 'ExcelData;;;employee_sheet' Step 3 – Create new example Copy data into the remote table using: insert into p_employee_sheet select emp_id, emp_fname from p_employee

  34. Some Helpful Hints

  35. To drop a proxy table, use: SQL Statement: drop table [<owner>.]<table_name>; If the proxy table was created using CREATE EXISTING, only the proxy table is dropped If the proxy table was created using CREATE … AT statement, then the proxy table and the remote table are both dropped. Hints – Dropping proxy tables

  36. Example 10: drop table p_employee Since this proxy table was created using CREATE EXISTING, this statement drops the proxy table only; the remote table is not affected. drop table p_employee_sheet Since this proxy table was created using CREATE TABLE … AT, this statement drops the proxy table, AND will also drop the remote table at 'ExcelData;;;employee_sheet' Hints – Dropping proxy tables example

  37. To send a statement directly to the remote server, use: SQL statement: forward to <server_name>; <remote_statement(s)> forward to; The remote_statements text is sent to the remote server as given Text is terminated by the "forward" and "to" tokens Remote Data Access passthru mode

  38. select * from sys.sysservers Hints – Useful system tablessys.sysservers – list of remote servers

  39. select table_name, remote_location, existing_obj from systable where remote_location is not NULL; Hints – Useful system tablessys.systable – list of proxy tables

  40. sp_remote_tables server_name [, table_name ] [, table_owner ] [, table_qualifier ] [, with_table_type ] Lists tables available at remote server sp_remote_columns server_name, table_name [, owner ] [, database] Lists columns in remote table Hints – Useful built-in procedures

  41. You diagnose remote data access problems by using the cis_option connection property SQL statement: set temporary option cis_option = 7; ASA will display messages in console log. Set cis_option value to 0 (zero) to turn off. Remote Data Access trace CIS option

  42. Example 11: set temporary option cis_option=7; select * from p_employee; Trace CIS option example

  43. Data Migration Tools

  44. You can import remote data using the sa_migrate set of stored procedures Available in ASA 7.0.2, 7.0.3 and 8.0 Source data may be from one of: iAnywhere ASA Sybase ASE Oracle, IBM DB2, MS SQL Server, MS Access Data migration tools - Availability

  45. Basic methodology: create base table(s) and proxy table(s) copy data from remote table(s) create foreign keys ASA local database: Remote data source: base table proxy table remote table Data migration tools - Basics

  46. To migrate entire remote database in one step, use stored procedure: dbo.sa_migrate( IN local_table_owner VARCHAR(128), IN server_name VARCHAR(128), IN table_name VARCHAR(128) DEFAULT NULL, IN owner_name VARCHAR(128) DEFAULT NULL, IN database_name VARCHAR(128) DEFAULT NULL, IN migrate_data BIT DEFAULT 1, IN drop_proxy_tables BIT DEFAULT 1 ) Migration – Single step process

  47. Example 12: call dbo.sa_migrate( 'dba', 'DemoServer', 'Customer', 'dba' ) This migrates the customer table definition and data from DemoServer to my database by: creating customer table in my database creating customer_et proxy table copying the customer data dropping the customer_et proxy table Migration – Single step example

  48. Migration – Single step example results

  49. To have more control over what is migrated, use: sa_migrate_create_remote_table_list( … ) sa_migrate_create_tables( … ) sa_migrate_data( … ) sa_migrate_create_remote_fks_list( … ) sa_migrate_create_fks( … ) sa_migrate_drop_proxy_tables( … ) Migration – Multi-step process

  50. 1. Create a list of tables to migrate using dbo.sa_migrate_create_remote_table_list( IN server_name varchar(128), IN table_name varchar(128) default NULL, IN owner_name varchar(128) default NULL, IN database_name varchar(128) default NULL ) this populates the dbo.migrate_remote_table_list table delete any rows that you do not want Migration – Step 1: create table list

More Related