What is Database Link? - ALTIBASE Customer Support
Contents
1. This manual uses the following standard set of conventions to introduce new terms illustrate screen displays describe command syntax and so forth o o the aaa ene VARCHAR size FIXED VARCHAR size FIXED VARIABLE Displays the mandatory fields Specifiesa ENABLE DISABLE COMPILE field that requires selection of more than one item Argument indicating optional or manda ENABLE DISABLE COMPILE tory fields ENABLE DISABLE COMPILE Database Link Users Manual iV About This Manual Repetition of the previous argument SQL SELECT ename FROM Specifies the omission of the example employee codes HSCHOI 20 rows selected Other symbols Other Symbols EXEC p1 1 acc NUMBER 1 1 2 Italics Within text new terms and emphasized ELECT FROM table name words appear in italics Within syntax dia CONNECT userlD password grams values that you are to specify appear in italics Lower Case Let Program elements provided by the user SELECT ename FROM ters such as table names column names file employee names etc Upper Case Let All elements provided by the system or DESC SYSTEM SYS INDICES ters keywords appear in uppercase letter For additional technical information consult the following manuals ALTIBASE Administration Starting User s Manual ALTIBASE Administration Administrator s Manual ALTIBASE Administratio2. ALTIBASE doesn t support INSERT UPDATE and DELETE operations currently but grants only SELECT operation when you use database link This section discusses operations and features using SELECT statements You should set location descriptor in FROM clause of SELECT statements to use database link Loca tion descriptor is comprised of and link name Object name of location descriptor should exist in remote server Object can contain user name You can use table and view as destination objects supporting data base link in ALTIBASE Example 1 SELECT FROM empelinkl You should use WHERE clause when setting condition for object in remote server to use database link When using WHERE clause you shouldn t set location descriptor in WHERE clause You should give alias to destination object in FROM clause and use alias in WHERE clause Example 1 Local server searches for rows whose value is greater than 100 in 11 on T1 table of remote server that link1 indicates SELECT FROM T1Glink1 WHERE I1 gt 100 Example 2 Local server searches for names of employees that work in rnd department They should be saved in both emp1 of local server and emp2 of remote server that link1 indicates SELECT emp name FROM SELECT emp no emp name FROM empl UNION ALL SELECT emp no emp name FROM emp2 linkl v1 dept WHERE vl emp no dept emp no AND dept dept name rnd You can use options such as selection join subquery aggreg
3. be the existing AltiLinker LINKER STOP This stops to run AltiLinker However no transaction should exist using database link when terminat ing database link Otherwise you fail to execute this statement SESSION CLOSE Application AltiLinker ALTIBASE DBMS You can close only database link with this statement When you connect to server session is created there If you run database in this session session allocated for you creates other session for database link to connect to AltiLinker And when session is closed session for database link is also closed together If you access a database link in a session then the link remains open until you close the session At this time you can close a database link session with ALTER SESSION CLOSE DATABASE LINK state ment In other words you can t end entire session allocated for you with SESSION CLOSE statement but close session for database link 23 How to Use Database Link Extra Features of Database Link isql u sys p manager sysdba Altibase Client Query utility Copyright 2000 ALTIBASE Corporation or its subsidiaries All Rights Reserved ISOL CONNECTION UNIX SERVER 127 0 0 1 PORT NO 20300 isQL sysdba ALTER DATABASE LINKER STOP Alter success iSQL sysdba ALTER DATABASE LINKER START Alter success iSQL sysdba ALTER SESSION CLOSE DATABASE LINK Alter success Database Link Users Manual 24 SELECT Statements SELECT Statements
4. ALTIBASE Application Development Database Link Users Manual release 5 3 3 LT ALTIBASE PERFORMANCE SOLUTIONS ALTIBASE Application Development Database Link User s Manual Release 5 3 3 Copyright 2001 2009 Altibase Corporation All rights reserved This manual contains proprietary information of Altibase Corporation it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright patent and other intellectual property law Reverse engineering of the soft ware is prohibited All trademarks registered or otherwise are the property of their respective owners Altibase Corporation 10F Daerung PostTower ll 182 13 Guro dong Guro gu Seoul 152 847 Korea Telephone 82 2 2082 1000 Fax 82 2 2082 1099 E mail support altibase com www http www altibase com Contents POT AGG MR ME i About This Maniak es a a EN dd t devo aas aqna ADM via Cree eee a NR ree rae li T introd ction to Database LINK 5 i eerte eaae Ee S EN RR ER OGg PET OM ANS seevcceacsssssscceabsaswessadacsessdsessessessescdassueveasssccedseccstsssoacs 1 Whatis Database LINK asses cect ses iss ae e SNO ta rated A dea ened eas oases FR IRR RR 2 Features or Database EINK eios an dE RD DEDERIT O cosas dO ddibdbam tn aco ptas mita Ed t 5 2 Objects SQL Commands and Data Types for Database Link eee e eee e eee ee ee eee eee eee eese eese ee esses esses esoos 9 Objects Tor datab
5. ASE to library environment variable of account operated in ALTIBASE and creates odbc ini in home directory for ALTIBASE account or etc directory where ODBC driver manager is installed You should add DSN when creating odbc ini and can set several DSNs in odbc ini as follows ODBC Data Sources altibase odbc Altibase ODBC Driver altibase odbc Driver home altibase altibase home lib libaltibase odbc 64bit ul32 so ServerType Altibase Server 192 168 3 62 Port 20300 NLS USE US7ASCII Database mydb FetchBufferSize 64 ReadOnly no If you create altibase_odbc as DSN information of destination server with using database link are also specified At this time you can choose libaltibase_odbc 64bit ul32 so or libaltibase_odbc 64bit ul64 so in SALTIBASE_HOME lib as a driver if using ALTIBASE as a remote database Because sizes of SQLLEN and SQLULEN vary depending on types of 64bit ODBC managers ALTIBASE version 5 1 5 28 or later supports ALTIABSE 64bit unixODBC driver with both SOLLEN 32bit and 64bit 64bit server client packages contain 2 drivers as follows Altibase 64 bit unix odbc driver libaltibase odbc 64bit ul64 so SOLLEN is 64bit libaltibase odbc 64bit ul32 so SOLLEN is 32bit You can select a driver depending on ODBC manager as the following table Database Link Users Manual 16 Configuration Table 3 1 ODBC Manager 64 bit odbc manager SOLLEN unix odbc 64bit 2 2 12 64bit DBUILD REAL 64 BI
6. C Driver Master Server Remote Server 1 You should send queries containing locator to local server 2 Query processor parses these queries and analyzes locator in local server And then query pro cessor regenerates queries to send to remote server as a result of analyzing locator and plans 3 If query processor asks storage manager to use a table scan according to the plans storage manager sends a request of a table scan in remote server to AltiLinker and waits the result At this time native connections are built for communication between storage manager and AltiL inker 4 AltiLinker passes the query from storage manager to remote server for execution With ODBC AltiLinker can access and interact with remote server Therefore ODBC driver manager should be installed in local server and ODBC driver should be installed in remote server respectively 5 Remote server executes queries as requested from AltiLinker and then passes the query result via ODBC Data size of result can be different depending on the format of queries regenerated by query processor 6 Thequery result is returned to AltiLinker from remote server and then AltiLinker sends each record of the query result to local server Local server converts the data from ODBC format into Database Link Users Manual 14 How to Run Database Link a format expected by local server and passes them from storage manager to query processor 7 Query processor returns data aft
7. EMOTE DISTINCT name FROM employee link1 Example 2 If the sum total of values of I2 in rows whose value is 3 in 11 on T2 table of remote server that link1 indicates are greater than 5 local server searches for rows on T1 table in remote server SELECT EXEC REMOTE FROM T1 link1 WHERE 5 lt SELECT EXEC REMOTE SUM I2 FROM T2 link1l WHERE I1 3 Example 3 This example has same queries as Example 2 except hint Subqueries judge truth or false of conditional clause with results returned by remote server that executes entire queries And then local server searches for data in T1 by using last results in remote server again SELECT FROM T1Glinkl1 WHERE 5 lt SELECT EXEC REMOTE SUM I2 FROM T2G01ink1 WHERE I1 3 27 How to Use Database Link EXEC REMOTE Hint Database Link Users Manual 28 Property related to Database Link Appendix A Property and Data Dictionary Property related to Database Link You should modify ALTIBASE property files to have the perfect ones for using database link Proper ties related to database link are as follows For more details see Starting User s Manual AUTO REMOTE EXEC DBLINK ENABLE DBLINK QUERY TIMEOUT LINKER CONNECT TIMEOUT LINKER LINK TYPE LINKER PORT NO LINKER RECEIVE TIMEOUT LINKER THREAD COUNT LINKER THREAD SLEEP TIME MAX DBLINK COUNT REMOTE SERVER CONNECT TIMEOUT Data Dictionary related to Database Link You can check curren
8. T MODE BUILD LEGACY 64 BIT MODE 17 Configuration and Operation of Database Link Procedure for Running Database Link Procedure for Running Database Link Linker Activation AltiLinker should be activated to use Altibase database link To achieve this you should set the val ues of database link properties in SALTIBASE HOME conf altibase properties First you should start to specify DBLINK ENABLE as 1 to activate linker and then specify LINKER PORT NO as port number used when databases send or receive data At this time if LINKER LINK TYPE is TCP you should set LINKER PORT NO If you start up ALTIBASE after specifying properties Linker runs with ALTIBASE together For details about properties related to database link see properties related to database link section in Starting User s Manual Database Link Users Manual 18 Z How to Use Database Link Database link creation its drop and extra features are covered in this chapter 19 How to Use Database Link CREATE DATABASE LINK CREATE DATABASE LINK You can create database link with CREATE DATABASE LINK statement for only one remote server CREATE PUBLIC PRIVATE DATABASE LINK dblink name WITH ODBC dsn CONNECT TO user id IDENTIFIED BY password To create a database link you must have the CREATE DATABASE LINK system privilege and user s SYS privilege PUBLIC PRIVATE You should specify database link to create as PUBLIC or PRIVATE You can specify PUBLIC t
9. ack to a local server such as ALTIBASE You can also use ALTIBASE or relational databases as a remote server Relational database should be connected to local server with using ODBC e AltiLinker AltiLinker is the process that sends receives data to from remote server when you use data base link If database link is active AltiLinker does the same as remote server For example when remote server is started up AltiLinker starts running and when server is Database Link Users Manual 2 What is Database Link shut down AltiLinker stops running However you can manually terminate or restart up AltiLinker with ALTER DATABASE LINKER statement Location Descriptor Location descriptor should be used in queries to set database link and is specified as between names of object and link Location descriptor is used only in FROM clause SELECT FROM emp link1 A database link is a connection between two physical database servers that allow a client to access them as one logical database Figure 1 1 How to Implement Database Link SELECT FROM table 1 dblink1 Server2 222 112 1 236 Database Link table 1 dblink1 gt table 1 222 112 1 236 Figure 1 1 illustrates how to implement database link in outline If you execute queries local server sends queries to remote server after checking if remote server has their destination table And then remote server sends t
10. ase LINK seii dete E E bi vei NOV eia t ON ETUR 10 SOL Commands Tor Database Elnk iia eti toieon ert ttet ertet A er ers Eve O ine EE ERA EUR CE PE He ERE eser 11 Data Types for Database ID Kee oe IG RUE RE E REIR DEMAIN UE cH A Fee eet Ren Rose vau p tee tances ok D eU 12 3 Configuration and Operation of Database Link ecce ee eee eee eee ee eee eese eese ee eee eee e eee tete tete eese esses eee enooe 13 How to Run Database LINK spe sen ood RSS dne praep a Hb en er ua e dV AIR an aes 14 CS IAT NATION gosh arae iba tdv fei te aao tutta ru feta eun bs ti M b T astu 16 Procedure Tor Runnind Database LINK nacti tte ven dre br eie hU cie M pO HR I Ue ER CUN eR 18 LM Howto Use Database LINK queer 19 CREATE DA TABASE LINK ce AE o EN e A E S 20 DROP DATABASE EINK ceea e O a AE N 22 Extra Features Of Database Milan annaa n a RWRTUE dIR DR CUN UR D RENTEN UR UR en Mid 23 SELECT statemen eet tunt teme enu a 25 EXEC REMO TEJTM BE cna d mant et dvi ton S A Lehre det Oe duse ee ace sn tic ene 27 AppendixA Property and Data Dictionary eee ee ee eee ee eee eee eaae eee sess esas sso esee eee eee eese eese sess asse ese eese tete eoe 29 Property related to Database EInie os im i p PER d a Tate tb n es Rp vas emite EE 29 Data Dictionary related to Database EIBK us edere iret toti ee eov vet ioi eee ordi res Lv Pte eese S 29 Preface About This Manual About This Manual This manual contai
11. ation set and view in SELECT state ment You can also use subselect statements to run database link 25 How to Use Database Link SELECT Statements Example 1 Local server searches for i1 columns in T1 of local server that link1 indicates without repeating same columns SELECT DISTINCT i1 FROM T1 link1 Example 2 You can get result containing departments of all employees in local server when join ingt member with t dept in remote server that link1 indicates If the value of department ID is 0 or greater local server estimates the number of employees and their average age in each department SELECT tl dept id COUNT AVG age SUM age FROM t memberGlink1 t1 t deptelink1 t2 WHERE tl dept id t2 dept id GROUP BY tl dept id HAVING tl dept id 0 Example 3 You can get result containing all employees in local server when joining t member with t dept in remote server that link1 indicates If employees are more than 30 years old local server choose 3 employees The value of their ID should be biggest in descending numeric order Last result in local server is expressed in their names age and the total sum of age of all employees SELECT tl name tl age SELECT SUM age FROM t memberGalink1 sum FROM t memberGlink1 t1 SELECT dept name dept id FROM t deptelink1 t2 WHERE tl dept id t2 dept id AND tl age lt 30 AND 10 gt SELECT count FROM t deptelinkl ORDER BY tl1 member id DESC LIMIT 3 Example 4 L
12. database link The following table displays what shema objects are existed to support database link Table 2 1 Schema Objects for Database Link fe vey L NEN MN NEN MN LONE EN L NEM MMMMMMM L EE EN A table is most basic schema object organized in database and stores records physically Database links are a foundation for inter operability between separated tables View can join and simplify multiple tables into a single virtual table Views can be joined to create new one and made up of base tables using database link So view is also object using database link because a view is a virtual table defined on base tables Stored procedure is a database procedure executing several SOL statements at once with input out put and input output arguements depending on condition specified in body Database link is used in a stored procedure except ROWTYPE variable declaration and cursor Database Link Users Manual 10 SQL Commands for Database Link SQL Commands for Database Link DDL Local server can t execute DDL Data Definition Language in remote server by using database link DCL Local server can t execute DCL Data Control Language in remote server by using database link DML ALTIBASE doesn t support operations such as INSERT UPDATE and DELETE but SELECT operation for using database link A SELECT statement can contain all clauses such as selection join subquery aggregation set and view You can use database li
13. er pulling together and filtering data sent from storage man ager Data should be suited for the result of queries executed by you 2 and 5 above this section discuss the communication between local server and AltiLinker You can choose the type of native connections depending on LINKER LINK TYPE If LINKER LINK TYPE is set to 0 TCP is used If LINKER LINK TYPE is specified as 1 Unix domain is used If LINKTER LINK TYPE is set to 2 IPC is used Only ODBC is supported currently for communication in 3 and 4 gt For details see Configuration 15 Configuration and Operation of Database Link Configuration Configuration A database link is a connection between local and remote servers that allow a client to access them with ODBC Altibase ODBC driver should be added to package To use this ODBC driver manager should be additionally installed in the system because ALTIBASE doesn t provide ODBC driver man ager UnixODBC driver manager is supported as an open source product on Unix and Linux You can find the release at http www unixodbc org Other ODBC driver managers are Data Direct Eye Soft and so on Windows has its own driver ODBC provides a common interface for applications to access data from database management sys tems Access to database is managed by ODBC driver manager So the driver manager probably can vary depending on specific database ALTIBASE adds library path of ODBC driver manager installed in ALTIB
14. heir results back to local server 3 Introduction to Database Link What is Database Link ystem Environment Segregated servers should provide independent operating environment to implement database link In other words queries should be perfectly suitable for each servers to execute them For example if Site1 stores information about the manufacturing process or a product local server should be designed to execute transaction without undergoing a process of other systems when transaction related to product information occurs The following figure roughly illustrates operating environment of database link Multiple sites or servers provide independent operating environment and are interconnected with computer com munication network Each of them has its own database Queries can be sent to other sites and serv ers for execution through computer communication network Figure 1 2 Operating Environment of Database Link Database Link Users Manual 4 Features of Database Link Features of Database Link Database link has following features Convenience Efficiency Scalability High Availability Convenience Database link can provide transparency in location of remote servers In other words user applica tion does not need to know the location of data used for last result Figure 1 3 Application s Access to Each Remote Servers Product Information Manufacturing Information For examp
15. ibes how to create and drop database link Appendix A Property and Data Dictionary This appendix lists all kinds of properties and data dictionaries related to database link This section offers documentation conventions as follows They make it easier to gather information from Altibase manuals e Command Line Conventions Typographical Conventions This section defines and illustrates the format of commands that are available in Altibase products These commands have their own conventions which might include alternative forms of a com mand required and optional parts of the command and so forth The command starts The syntax element which is not a com plete command starts with an arrow Reserved word The command continues in the next line The syntax element which is not a complete command terminates with this sym bol End of a statement Mandatory SELECT The command continues from the previous line The syntax element which is not a complete command starts with this symbol iii Preface About This Manual Optional NOT 1 Mandatory field with optional items Only one field must be provided ADD DROP Optional field with optional item ASC DESC Optional Multiple fields are allowed The comma must be in front of every repetition
16. is time you should create database link whose name is link2 used for all users in the system iSQL CREATE PUBLIC DATABASE LINK link2 WITH ODBC altibase odbc CONNECT TO userl IDENTIFIED BY userl 21 How to Use Database Link DROP DATABASE LINK DROP DATABASE LINK You can drop database link with DROP DATABASE LINK statement Syntax DROP PUBLIC PRIVATE DATABASE LINK dblink name Description You should be SYS user and have the DROP DATABASE LINK system privilege to drop database link dblink name This sets the name of database link to be dropped Restriction You can t drop database link while using it You can drop database link only if you don t execute que ries for it If you drop database link while executing queries errors occur Example lt Query1 gt user1 should drop database link specified as PRIVATE The name of database link is dblink1 iSQL DROP DATABASE LINK dblink1 Query2 user should drop database link specified as PUBLIC The name of database link is dblink1 iSQL DROP PUBLIC DATABASE LINK dblink1 Database Link Users Manual 27 Extra Features of Database Link Extra Features of Database Link Syntax ALTER DATABASE LINKER START ALTER DATABASE LINKER STOP ALTER SESSION CLOSE DATABASE LINK Prerequisite You can use extra features of database link in sysdba mode with user s SYS privilege Description LINKER START This starts to run AltiLinker However there shouldn t
17. le assume that product information is stored in Site1 and manufacturing information is stored in Site2 respectively as Figure 1 4 If you want to know manufacturing information on cer tain product user application gets product information in Site1 and manufacturing information in Site2 respectively and then joins data in Site1 and Site2 You can get required information 5 Introduction to Database Link Features of Database Link Figure 1 4 Application s Access with Database Link DB Link Customer Information of A Company Customer Information of A s Cooperation Partner However if executing queries in local server of system using database link local server displays final results sent by remote server In other words even though data are distributed in multiple servers or sites physically you can use them as if they are stored in one server logically Efficiency If mass data are centralized QoS can t be guaranteed because multiple transactions can interrupt each other when they occur Figure 1 5 The Environment of Single Server d 2 p p Ir oat de AAA qe ca E LE EIE 2 Ma UT ps ag Sy E lg HE SUNS EUM Material Customer Management m Figures L Database Link Users Manual 6 Features of Database Link However if you distribute data that are seldom joined logically to multiple servers table workloads are also provisioned across them You can join data in each
18. n Replication User s Manual ALTIBASE Application Development SQL User s Manual ALTIBASE Application Development ODBC User s Manual ALTIBASE Application Development Spatial SOL User s Manual ALTIBASE Application Development Application Program Interface User s Manual ALTIBASE Tools iSQL User s Manual ALTIBASE Message Error Message Reference V Preface About This Manual Manuals PDF and HTML in Korean and English are available at Altibase Technical Center http atc altibase com Please let us know what you like or dislike about our manuals To help us with future versions of our manuals please tell us about any corrections or classifications that you would find useful Include the following information The name and version of the manual that you are using Any comments that you have about the manual Your name address and phone number Write to us at the following electronic mail address support altibase com When you need an immediate assistance regarding technical issues please contact Altibase Techni cal Center Thank you We appreciate your feedback and suggestions Database Link Users Manual vi l Introduction to Database Link This chapter describes database link concepts such as what it is and also lists features that database link provides Introduction to Database Link What is Database Link What is Database Link A database link can be a pointer that defines a one way communication path f
19. nk with a SELECT query nested inside DDL DML statement 11 Objects SOL Commands and Data Types for Data Data Types for Database Link Data Types for Database Link Only standard data types in ODBC are supported because you can access database via ODBC inter face when using database link The following table shows supported or unsupported data types defined by ALTIABSE Table 2 2 Data Types for Database Link mw mm 9 wow SRM 9 pow mwemw 9 fase mxwom 9 feo mma 9 pum memo 9 pem xta 9 fom wkRM 9 mem mmHm 9 wem sxww 6 frome sxwwmc 9 wa SEA 9 mar mmm 9 m some owns ee m0 E Database Link Users Manual 12 Configuration and Opera tion of Database Link Database link supported by ALTIBASE uses ODBC to access from local server to remote server This chapter assumes that you already configure unixODBC as an open source product because ODBC driver manager can be differently installed depending on situation and configuration in the system 13 Configuration and Operation of Database Link How to Run Database Link How to Run Database Link Database Link Procedure Figure 3 1 Procedure of Running Database Link Application sending DB Link Queries for Execution ALTIBASE DBMS DBMS AltiLinker QP r TT gnnection ODBC Driver Manager SM Rem ote DBMS ODB
20. ns information to help you understand the concepts of database link and use database link This manual is for ALTIBASE users as follows Database Administrators Performance Managers Database Users Application Program Developers Technical Assistance Team This manual assumes that you have the following background A working knowledge of your computer your operating system and the utilities that your operating system provides Some experience working with relational databases or exposure to database concepts Some experience with computer programming Some experience with database server administration operating system administration or network administration This manual assumes that your database server is ALTIBASE server Version 5 3 1 This manual covers the following topics Chapter 1 Introduction to Database Link This chapter presents an introduction to and overview of database link such as what database link is Chapter 2 Objects SQL Commands and Data Types for Database Link This chapter covers objects SOL commands and data types for database link Chapter 3 Setting up Database Link Environment and Starting up Database Link This chapter discusses how to set up database link environment and how to start up database link Database Link Users Manual li About This Manual Chapter 4 Working with Database Link This chapter lists all kinds of features that database link provides and descr
21. o create a public database link available to all users If you omit this clause the database link is private and is available only to you dblink name This sets the name of database link to be created dsn ODBC driver should be installed in the server because database link enables you to access to remote server with ODBC And DSN for remote server should be also specified in ODBC environment file For details see How to Configure odbc ini user id password This sets user id and its password required to access data on a database in remote server However if odbc ini has information of user and password this setting has a higher priority than that specified by this statement Therefore ODBC setting is not recommended when you use CREATE DATABASE LINK statement However the user should be granted to access destination objects when connecting to server with using database link Otherwise errors occur related to privileges lt Query1 gt user is specified as both user id and its password for database link in remote database whose DSN is altibase odbc At this time user should create database link whose name is link1 used only for him iSQL CREATE PRIVATE DATABASE LINK link1 WITH ODBC altibase odbc CONNECT TO userl IDENTIFIED BY userl Database Link Users Manual 20 CREATE DATABASE LINK lt Query2 gt user is specified as both user id and its password for database link in remote database whose DSN is altibase odbc At th
22. ocal server searches for name and age in t2 of remote server that link1 indicates and inputs them in t1 of local server INSERT INTO t1 SELECT name age FROM t2 linkl1 Database Link Users Manual 26 EXEC REMOTE Hint EXEC REMOTE Hint Using EXEC REMOTE hint enables local server to send and execute queries to remote server If you use EXEC REMOTE hint local server can work JOIN in remote server with database link and receive only result set with columns from different tables And this is also possible for queries if you specify AUTO REMOTE EXEC as 1 You can use EXEC REMOTE hint if target objects exist in same remote server when executing que ries However you can t use EXEC REMOTE hint considering the following Subqueries contain objects of local server Database link names of objects are different Subqueries contain a stored procedure or sequence Host variable is used in subqueries Subqueries reference external column Even though subqueries meet one of criterions above EXEC REMOTE hint is ignored If outer and inner queries use EXEC REMOTE hint EXEC REMOTE hint works in outer query and is ignored in inner query In other words if outer query doesn t use EXEC REMOTE hint EXEC REMOTE hint can work in inner query Example 1 Local server searches for the name on employee table in remote server that link indi cates and then executes entire queries Only result is returned in local server SELECT EXEC R
23. rom an Altibase server to another database server when you want to access data on a remote database These database servers should meet the following conditions Database servers should have mutually logical relationship Database servers should be connected to computer communication network Database servers should be separated locally Their logical relationship means that there is a correlation of data between servers and you should be given the privileges you need to perform your operations and can also centralize data segregated to different databases Database servers connected to computer communication network means that they are linked each other via hardware and software These hardware and software cover from intranet or subnet to WAN or internet Database servers can be on the same subnet Database servers separated locally means that they as data service provider are designed to run independently and cover from short range hardware platforms to individual sites Local Server A database is the subject that creates and uses database link Local server sends queries to remote server for execution Remote server executes them and send their results back to local server Only ALTIBASE as a local server is available to run database link Remote Server A database is the destination where local server sends queries for execution Remote server executes them sent from a local server such as ALTIBASE and send their results b
24. t state of database link with data dictionary and performance view as follows For more details see Administrator s Manual Data Dictionary SYS_DATABASE_LINKS_ Performance View VSDBLINK REMOTE STATEMENT INFO 29 Property and Data Dictionary Data Dictionary related to Database Link VSDBLINK REMOTE TRANSACTION INFO VSDBLINK TRANSACTION INFO VSLINKER STATUS Database Link User s Manual 30 Index A SQL Commands for Database Link 11 AltiLinker 2 Stored Procedure 10 C T Convenience 5 Table 10 CREATE DATABASE LINK 20 V D View 10 Data Dictionary 29 Data Types for Database Link 12 Database Link 2 Database Link Communication 15 Database Link Procedure 14 DBLINK ENABLE 18 DCL 11 DDL 11 DML 11 DROP DATABASE LINK 22 E Efficiency 6 EXEC REMOTE 27 F Features of Database Link 5 H High Availability 7 How to Run Database Link 14 L Local Server 2 Location Descriptor a 3 O Objects for Database Link 10 P Performance View 29 Property related to Database Link 29 R Remote Server 2 S Scalability 7 Schema Objects 10 3l
25. table with using database link In other words transactions interrupt each other less and less So you can use system resource such as CPU memory and IO because transaction can occur in each server independently Figure 1 6 Data Distribution Environment AP2 AP1 Material Management Customer Information Master DB Hemote DB1 Sales Figures Hemote DB2 AP3 Database link has more flexible scalability than centralized system because if using database link you can install more servers even though running system reaches the limits of its process Even though failure occurs in some of all systems running database link the others can continue to perform work Figure 1 7 illustrates database link system where Site1 stores product information Site2 contains manufacturing information and Site3 has shipping information 7 Introduction to Database Link Features of Database Link Figure 1 7 Available Service even with Failure in some Servers Application P Product Information v O lt gt Master Server x Manufacturing Information Site3 Shipping Information of Product O O Database Link Users Manual 8 2 Objects SQL Commands and Data Types for Database Link 9Objects SOL Commands and Data Types for Database Objects for Database Link Objects for Database Link This section covers what objects are existed in ALTIBASE to support
Download Pdf Manuals
Related Search