Database Link User`s Manual
Contents
1. Only the SYS user or a user with the CREATE DATABASE LINK system privilege can create a Database Link object PUBLIC PRIVATE Specifies the Database Link PUBLIC PRIVATE attribute Specify PUBLIC to make the created Database Link object available to all users or PRIVATE to make the Database Link object available only to the creator If this clause is omitted the Database Link object defaults to PRIVATE dblink name This sets the name of the Database Link object to be created dsn This is used to specify the name of the ODBC DSN Data Source Name Because Database Link uses ODBC in order to access the remote server an ODBC driver must be installed Additionally the details about the DSN on the remote server must be specified in the ODBC environment file For more information please refer to 3 2 2 How to Configure odbc ini user id password This is the ID and password of the database user on the remote server However If a user ID and pass word have been set in odbc ini those settings will take priority over the ones specified in this state ment Therefore if you want Database Link to actually use the user ID and password specified in the CREATE DATABASE LINK statement do not specify a different user ID or password in the odbc ini file Note also that the user specified here must have been granted permission for the relevant target objects on the remote node when accessing them via Database Link Otherwise errors rel2. 11 22 S ONE toad E 11 2 3 Data Types Supported by Database LINK ssessssssessssecssssscessecsssssscessecsssssscsssesscassecsssssscsecscsesecsssececsesecsesecscseseeassecess 12 3 Configuration and Operation of Database eee eee eee eee ee eese eee eee ee eese eee tete eee eese 13 Howto hun Database 14 s T Datapase Linie PrOCe eode detenta dert Cn lv b Ades ati 14 3 1 2 Supported Communication Protocols eee esee eene entente stets 15 SWAT D T 16 3 2 1 ODBC Driver Manager Installation eee 16 322 Howto Configure OdDC ID inen ocn cec PR E 16 3 34 ctivaung AICI MINKE S santo tet 18 4 Database Link Related SQL Statements eaa Ree Poe a AERE PRAE EUREN e ORE RR VENTA PAN 19 4 ICREATE DATABASE EINK cupiin testa ua ian as nd bit 20 A kl arr ry opu dd c Du EE 20 A causes E NT 20 g ME MEM 20 4 2 DROP DATABASE LINK 22 AL LE LI E MI M LU C A d 22 a2
3. In the main text new terms and emphasized words appear in ital ics In statement definitions values that you are to specify appear in italics Indicate program elements set by the user such as table names col umn names file names etc Keywords and all elements pro vided by the system appear in upper case VARCHAR size VARIABLE FIXED ENABLE DISABLE COMPILE ENABLE DISABLE COMPILE ENABLE DISABLE COMPILE iSQL gt select e lastname from employees E LASTNAME Davenport Kobain 20 rows selected EXEC pl 1 acc NUMBER 11 2 SELECT FROM table name CONNECT userID password SELECT e lastname FROM employ ees DESC SYSTEM SYS_INDEX For additional technical information consult the following manuals Administration Getting Started Administration Administrators Manual Administration Replication Manual Application Development SQL Reference Application Development ODBC Reference V Preface About This Manual Application Development Spatial SQL Reference Application Development Application Program Interface Users Manual Tools iSQL Users Manual Message Error Message Reference Manuals PDF and HTML in Korean and English are available at the Altibase Customer Support http support 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
4. P xe EB c bed cess usb eee a Eod e a e RR EG 29 Data Dictionary related to Database LINK sssessssessssssessssescsssessssesscsssecsssecsesesecsesescsssecsssesscsesscassesscsesecsssessesescessesscaesecess 29 TEN 29 PEE FOP VAC VS 30 Preface About This Manual About This Manual This manual contains information to help you understand Database Link concepts and use Database Link We trust that this manual will be of great help This preface contains the following Who This Manual is For Targeted Software How This Manual is Structured Documentation Conventions Related Reading e On line Manuals e Altibase Welcomes Your Comments Who This Manual is For This manual is intended for the following ALTIBASE HDB users Database Administrators Performance Managers Database Users Application Developers Technical Assistance Team In order to fully understand the contents of this manual we recommend that you have the following background knowledge A working knowledge of your computer your operating system and the utilities provided with your operating system 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 Targeted Software This manual assumes that your database server is ALTIBAS
5. SQL Statements and Data 2 1 Accessible Remote Schema Objects 2 1 Accessible Remote Schema Objects The following table lists the remote schema objects that can be accessed using Database Link Table 2 1 Remote Schema Objects Accessible using Database Link fe vey Stored Procedure No but stored procedures on the local server can access the remote server s schema The supported objects are described in slightly greater detail below The table is the most fundamental database schema object and is the place where records are stored Database Link s essential function is to realize interoperability between disparate tables so it follows that most Database Link functionality pertains to tables Views can join one or more tables so that they appear to users as a single logical table Although views can be used as structural elements in other views the actual substructure of any view ulti mately comprises a combination of base tables The basic structural elements of views are tables which are supported by DB Link but because views themselves are perceived as logical tables they are also supported by DB Link Database Link cannot be used to access stored procedures that are defined on the remote server However stored procedures that are defined on the local server can use Database Link to access tables and views in the remote server s schema Stored procedures used in this way cannot contain ROWTYPE variable declarations based on
6. corrections or clarification that you would find useful Please 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 For immediate assistance regarding technical issues please contact the Altibase Customer Support http support altibase com Thank you We appreciate your feedback and suggestions Database Link User s Manual yi 1 Introduction to Database Link This chapter explains what Database Link is explains relevant concepts and lists the features pro vided with Database Link It contains the following sections 1 1 What is Database Link 1 2 Benefits of Database Link 1 3 Restrictions 1 Introduction to Database Link 1 1 What is Database Link 1 1 What is Database Link This section introduces some concepts and terminology that will be of help in developing a concep tual understanding of Database Link This section briefly introduces the following topics 1 1 1 Concepts 1 1 2 Terminology 1 1 3 How to Implement Database Link 1 1 4 System Environment Database Link is a technology for linking to database servers that meet the following criteria so that users can integrate material provided on different servers whenever they wish The database servers to be connected should meet the following criteria The database servers should have a logical relationship between them The
7. tables on the remote server Additionally they cannot be used with cursors Database Link User s Manual 10 2 2 SQL Statements Supported by Database Link 2 2 SQL Statements Supported by Database Link This section explains which SQL statements are supported by Database Link N J Database Link cannot be used to execute DDL Data Definition Language on the remote server 2 DC F ai Ze 4 Database Link cannot be used to execute DCL Data Control Language on the remote server The exception is the COMMIT and ROLLBACK statements which are useful in the following way When a SELECT statement is executed on a remote server using Database Link a transaction is initi ated This transaction can maintain a lock on objects on the remote server which can cause other transactions on the remote server e g DROP TABLE to fail The COMMIT and ROLLBACK statements can be used to explicitly release such a lock The COMMIT and ROLLBACK statements are function ally identical to each other when used in this way ALTIBASE HDB supports the use of Database Link to perform SELECT operations on the remote server but not INSERT UPDATE or DELETE operations Support for SELECT statements extends to everything including selection join subquery aggregation set and view In addition Database Link can be used in a SELECT query nested inside a DDL DML statement SELECT FOR UPDATE statements make changes to data just l
8. 2 Prerequisite Only the sys user can execute this statement and only when running in sysdba mode 4 4 3 Description Application AltiLinker ALTIBASE DBMS When a user connects to a server a session is created on the server If the user access a remote server using Database Link in this session an additional session is created for use with Database Link and AltiLinker Then when the user s session is closed the Database Link session is closed along with it However after the user finishes working with Database Link if the user session is not closed but remains open to perform other tasks the Database Link session will unnecessarily remain open In such cases close the Database Link session using the ALTER SESSION CLOSE DATABASE LINK state ment That is the ALTER SESSION CLOSE DATABASE LINK is used to close a Database Link session that is being kept alive by a user session not the user session itself 4 4 4 Example 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 Database Link User s Manual 24 4 4 ALTER SESSION iSQL sysdba ALTER SESSION CLOSE DATABASE LINK Alter success 25 Database Link Related SQL Statements 4 5 SELECT 4 5 SELECT At present Database Link supports only SELECT operations not operations that change data such as INSERT UPD
9. ATE and DELETE operations This section discusses the use of the SELECT statement To use Database Link in a SELECT query use the location descriptor in the FROM clause The location descriptor comprises the ampersand character and the name of the Database Link object Specify the name of the object i e table or view to query on the remote server before the location descriptor If the name of the owner of the object is different from the name of the user with which the connection to the remote database was established specify the owner name before the object name separated by a period 7 character Example 1 SELECT FROM empelinkl The WHERE clause is used in the same way as when using a SELECT statement to query objects on the local server If you need to specify an object on the remote server as part of a WHERE condition do not use the location descriptor directly in the WHERE clause Instead specify an alias for the object and location descriptor e g objectalinkname in the FROM clause and then use the alias in the WHERE clause Example 1 Retrieve rows for which the value I1 is greater than 100 from table T1 on a remote server which is indicated by link1 SELECT FROM T1Gl1ink1 WHERE I1 gt 100 Example 2 Retrieve the names of all the employees who work in the Quality Assurance Depart ment from table emp a remote server indicated by link1 and the employees table on the local server SEL
10. Altibase Application Development Database Link User s Manual Release 6 1 1 February 4 2013 AALTIBASE Altibase Application Development Database Link User s Manual Release 6 1 1 Copyright 2001 2010 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 South Korea Telephone 82 2 2082 1000 Fax 82 2 2082 1099 Homepage http www altibase com Contents i did ERE TE i VAIN ANS NNI RN SR Uo Nr MR cR Mateo aue ua Da d li Who Mab alis FOR trea tete tetra a ton ir o trm a ea en aha b cher o n ER ii Gea td eT CD RAE uu eT ad RR IT ii Flow This Manual is asset tia te ien Utere ted ti a Dp dte ER ii DOCUMENTATION GOMVCIUONS s rad eons see Go ub D M I ieee tes iii Related Nd tU V a vi Altibase Welcomes Your CO IASI ES cei eget d In etai Dno ses re da s eli ad rop p
11. DES O ELON am Iq ebore eV dna 22 A 213 RESTICH O osien 22 ADA EXAMP T OUT 22 Z3 AELTER DATABASE ONKER ien dedi a M ends Qi eng tei naan 23 49 1 DY UNL AN tee UL I D ED M A UM fi 23 Ludis t T 23 I E E ana 23 E uncos itin bends ACTU t 23 SESSIONS senec E DM MA ERE ME 24 AA Ll S VIDEO MI ME 24 A pluie 24 4 1 3 DESP UO Le ccsse e rr 24 Zo Go g al eer eRe DEAR UA Moon E Me MO LES Oe 24 gt ELECT eee ree ee eee 26 S dE SUELE 26 LS cast a tc totes oec 26 459 Other SELECT Statement Features non Es E Qui sa ete te tp dU EAT US 27 EXEC hEMQTE PIDE bores itt bete bt a cadet neve v 28 A oS ROG UIC CONS asec iot dei ecdesiae 28 hav ended eR A nue p LA DR BEY 28 AppendixA Property and Data eese eee ee eese ee sese ss sss PPP e 29 Properties related to Database
12. Descriptor 3 M Meta Table 29 P Performance View 30 Properties related to Database Link 29 R Remote Server 2 5 Scalability 8 31 Index
13. E HDB server Version 6 How This Manual is Structured Database Link User s Manual il About This Manual This manual covers the following topics Chapter1 Introduction to Database Link This chapter introduces and provides an overview of Database Link Chapter2 Supported Objects SQL Statements and Data Types This chapter covers the objects SQL commands and data types supported by Database Link Chapter3 Configuration and Operation of Database Link This chapter discusses how to set up the Database Link environment and how to start up Data base Link Chapter4 Database Link Related SOL Statements This chapter lists all of the features provided by Database Link and describes how to create and drop a Database Link instance Appendix A Property and Data Dictionary This appendix lists all of the properties and the data dictionary related to Database Link This section explains the following documentation conventions These conventions make it easier to glean information from the ALTIBASE HDB manuals 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 a command and so forth Elements Meaning The command starts A syntax element which is not a com plete command starts with an arrow Rese
14. ECT e firstname e lastname FROM SELECT eno e firstname e lastname FROM employees UNION ALL SELECT eno e firstname e lastname FROM empiGlinkl v1 departments WHERE vl dno departments dno AND departments dname QUALITY ASSURANCE DEPT Database Link User s Manual 26 4 5 SELECT Database Link supports the use of joins subqueries set operators and aggregate functions when executing SELECT statements Additionally Database Link can be used in SELECT subqueries that are part of DDL or DML statements Example 1 gt Retrieve unique values from the i7 column of table T7 on a remote server referenced by link1 SELECT DISTINCT i1 FROM T1Glink1 Example 2 Retrieve the number and average age of the employees in each department by joining two tables on the remote server Disregard any negative department IDs SELECT ti dept id COUNT FROM t 1 1 t1 t deptelink1 t2 WHERE tl dept id t2 dept id GROUP BY tl dept id HAVING tl dept 19 gt 0 Example 3 Join tables t member and t dept on a remote server referred to as link1 to retrieve the name and age of the three employees under age 30 with the highest IDs along with the total age of all employees SELECT 11 tl age SELECT SUM age FROM t_member link1l 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 ORDER BY tl1 member id DESC LIMIT 3 Examp
15. ated to privileges will occur Example lt Query1 gt user1 user are the ID and password with which to connect to a database on remote server for which the DSN is altibase odbc A user creates a Database Link object with the name link1 which only s he can use 1501 gt CREATE PRIVATE DATABASE LINK link1 Database Link User s Manual 20 4 1 CREATE DATABASE LINK WITH ODBC altibase odbc CONNECT TO userl IDENTIFIED BY userl Query2 user1 user1 are the ID and password with which to connect to a database on a remote server for which the DSN is altibase odbc A user creates a Database Link object with the name link2 which all users can use iSQL CREATE PUBLIC DATABASE LINK link2 WITH ODBC altibase odbc CONNECT TO userl IDENTIFIED BY userl 21 Database Link Related SQL Statements 4 2 DROP DATABASE LINK 4 2 DROP DATABASE LINK To drop a Database Link object use the DROP DATABASE LINK statement 4 2 1 Syntax DROP PUBLIC PRIVATE DATABASE LINK dblink name 4 2 2 Description Only the SYS user or a user with the DROP DATABASE LINK system privilege can drop a Database Link object dblink name This specifies the name of the Database Link object to be dropped 4 2 3 Restriction A Database Link object that is currently in use cannot be dropped A Database Link object can be dropped only if no queries that use it are being executed If a Database Link object is dropped while queries are being executed an
16. database servers should be connected to a computer communication network The database servers should be physically separated Logical relationship means that the data structures on the servers should be consistent and that the user should have sufficient privileges to perform the desired operations In the case where it is desired to unite disparate data spread across multiple servers the user must be able to provide a centralized system for this purpose The requirement that the database servers be connected to a computer communication network means that they must be linked to each other via hardware and software This could range from intranet nodes on the same subnet to being located some distance from one another via a WAN or the Internet Being physically separated means that the main purpose of the servers is to provide data service that is to say the database servers must be constructed such that they can be operated indepen dently of one another This includes everything from hardware located in close proximity to com pletely independent sites Local Server This is the database server that creates and uses a Database Link object The local server reformats a user s queries sends them to a remote server for execution and reformats the received results to match the original query ALTIBASE HDB is the only local server that is capable of running Database Link Remote Server This is the destination database server that receiv
17. edure Figure 3 1 Database Link Operating Procedure Application sending DB Link Queries for Execution ALTIBASE DBMS DBMS AltiLinker Native QP Connection ODBC Driver Manager sm d Rem ote DBMS ODBC Driver Master Server Hemote Server 1 The user sends a query containing locator information to the local server 2 The query processor on the local server parses the query and analyzes the location descriptor The query to be sent to the remote server designated according to the location descriptor is then rewritten and an execution plan is made If the storage manager is requested to provide a table scan according to this plan the storage manager sends a request for a remote server table scan to AltiLinker and waits for the result Communication with AltiLinker uses a native connection module 3 AltiLinker passes the query to the relevant remote server for execution AltiLinker accesses and interacts with the remote server using ODBC Therefore an ODBC driver manager must be installed on the local server and an ODBC driver must be installed on the remote server 4 The remote server processes the query as requested by AltiLinker and then sends the query results via ODBC The amount of data that is returned will vary depending on the format of the query which was rewritten by the query processor on the local serv
18. er 5 AltiLinker receives the query results from the remote server and then sends them the local server one record at a time The local server converts the data from the ODBC data type into Database Link User s Manual 14 3 1 How to Run Database Link the ALTIBASE HDB data type and passes them from the storage manager to the query proces sor After the query processor collates and filters the data sent from the storage manager the resultant data corresponding to the original queries are returned Steps 2 and 5 in the preceding section pertain to the communication between the local server and AltiLinker The type of native connection to use is chosen by appropriately setting the value of the LINKER LINK TYPE property If LINKER LINK TYPE is set to 0 TCP is used for communication whereas if it is set to 1 a Unix domain socket is used Note that only TCP is supported for use with Windows Setting LINKER LINK TYPE to 2 specifies that IPC Inter Process Communication is to be used How ever at present only TCP and Unix domain are supported so this setting is reserved for future use Only ODBC is currently supported for communication in steps 3 and 4 above For more detailed information please refer to 3 2 Configuration 15 Configuration and Operation of Database Link 3 2 Configuration 3 2 Configuration This section provides instructions on configuration It covers the following topics 3 2 1 ODBC Driver Manager Installa
19. error will occur 4 2 4 Example lt Query1 gt Drop a private Database Link object named dblink1 iSQL DROP DATABASE LINK dblink1 Query2 Drop a public Database Link object named dblink1 iSQL DROP PUBLIC DATABASE LINK dblinkl1 Database Link User s Manual 22 4 3 ALTER DATABASE LINKER 4 3 ALTER DATABASE LINKER To start or stop AltiLinker use the ALTER DATABASE LINKER statement 4 3 1 Syntax ALTER DATABASE LINKER START ALTER DATABASE LINKER STOP 4 3 2 Prerequisite Only the sys user can execute this statement and only when running in sysdba mode 4 3 3 Description START This starts AltiLinker AltiLinker must not already be running STOP This stops AltiLinker However no transaction that uses Database Link objects can exist when Data base Link is stopped If transactions that use Database Link objects exist execution of this statement will fail 4 3 4 Example 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 23 Database Link Related SQL Statements 4 4 ALTER SESSION 4 4 ALTER SESSION To terminate a Database Link session use the ALTER SESSION statement 4 4 1 Syntax ALTER SESSION CLOSE DATABASE LINK 4 4
20. erties in SALTIBASE HOME conf altibase properties must be appropriately set First set DBLINK ENABLE to 1 in order to activate AltiLinker Then set LINKER PORT NO to the port number to be used when sending or receiving data LINKER PORT NO must be set when LINKER LINK TYPE is TCP After setting the properties when ALTIBASE HDB is started up AltiLinker starts up together with ALTIBASE HDB For more detailed information about Database Link properties please refer to the General Reference Database Link User s Manual 18 4 Database Link Related SQL Statements This chapter explains how to use the SQL statements that are provided for controlling Database Link as well as the use of the SELECT statement in detail It includes explanations of the following state ments 4 1 CREATE DATABASE LINK 4 2 DROP DATABASE LINK 4 4 ALTER SESSION 4 5 SELECT 4 6 The EXEC REMOTE Hint 19 Database Link Related SQL Statements 4 1 CREATE DATABASE LINK 4 1 CREATE DATABASE LINK To create a Database Link object use the CREATE DATABASE LINK statement A Database Link object can have only one remote server as its target m S ZU we _ 2 DW BR CH A o n O n u y B8 e OS Zw CREATE PUBLIC PRIVATE DATABASE LINK dblink name WITH ODBC dsn CONNECT TO user id IDENTIFIED BY password xd o 1 n P B e di Ee Vas lt A
21. es on vi 1 INTFOGUCTION TO Database Link EOTS EDINE OST CVSO 1 Isl Whatis Database ORE EUN 2 12 once o tsi toti ee EAE edu en e c RU 2 KTZ UUM INO LOGY ee 2 113 HOW to Implement Database LINK sua e enc tette o trit ELI EE E te RUE RN YET ros to ve 3 A 5ysterEBDVIFOD De DE a 4 1 2 BEMERUS OF Database t bt eun eU fad Mop be S NUR p NW Odin 5 IX Seam eek cere ee cease atte hace 5 1 2 2 ol gi alls coerente te d eodein atus ir enter oto ta eee Sere ote bs 6 EAEE o1 DT A E A M I M E Lot Mn MA MU 8 T24 TAN VPA Vell ARUN Men T dant 8 2 Supported Objects SQL Statements and Data Types ccscrssrcssssssscscccccccccccccccccccssssssssscccsccccccsscsscssccsesssssceeecs 9 21 Accessible Remote Schema Nee R tv iA 10 PME NIE Io me EN 10 PLU CN 10 2 13 Stored ProCeQUf esce vong is See ru usua Peau ves essere 10 2 2 SQL Statements Supported by Database Link eee e eee ee eere etertn ente rtn es tesis 11 2 2 ME 11 722 BY Bl seta n Ne INN LI MM EE
22. es requests from the local server via Database Link Database Link User s Manual 2 1 1 1 What is Database Link and sends the results back to the local server ALTIBASE HDB or any relational database that supports ODBC can be used as the remote server AltiLinker A separate process exists to intermediate the data exchange between Database Link and the remote server This process is called AltiLinker When the DBLINK_ENABLE property is set to 1 AltiLinker starts up when the server starts up and shuts down when the server shuts down However the user can use the ALTER DATABASE LINKER statement to manually shut down or restart AltiLinker Location Descriptor The location descriptor must be used in order to use Database Link in queries The location descrip tor consists of an ampersand character between the object name and the link The Database Link location descriptor is used only in the FROM clause of a SELECT SQL statement SELECT FROM empelinkl Database Link provides a link between independently operated DBMSs which can be of different types 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 Because the AltiLinker process is created with the same name as the ALTIBASE HDB server pro cess the number of processes with the name altibase increases by 1 f
23. ike INSERT UPDATE and DELETE statements and thus are not supported for use with Database Link 11 Supported Objects SQL Statements and Data 2 3 Data Types Supported by Database Link 2 3 Data Types Supported by Database Link Because Database Link uses the ODBC interface only the standard data types supported by ODBC are supported for use with Database Link The following table shows which of the data types defined within ALTIBASE HDB are supported for use with Database Link Table 2 2 Data Types for Database Link ALTIBASE HDB Data Type pate Supported jew SX 9 _ pow mar 9 _ 9 _ fom sxwwm _ SERA 9 wane sew some aos ee Database Link User s Manual 12 3 Configuration and Operation of Database Link Database Link uses ODBC to access remote servers The explanations in this chapter will be made on the basis of an open source Unix ODBC environment However the particulars of each ODBC Driver Manager installation can vary depending on the circumstances and server configuration This chapter comprises the following sections 3 1 How to Run Database Link 3 2 Configuration 3 3 Activating AltiLinker 13 Configuration and Operation of Database Link 3 1 How to Run Database Link 3 1 How to Run Database Link 3 1 1 Database Link Proc
24. le 4 Retrieve the names and ages from table t2 on a remote server referred to as link1 and insert them into table t1 on the local server INSERT INTO t1 SELECT name age FROM t2 linkl1 27 Database Link Related SQL Statements 4 6 The EXEC REMOTE Hint 4 6 The EXEC REMOTE Hint Use the EXEC REMOTE hint to pass a query or subquery a remote server so that the query can be processed and executed directly on the remote server Setting the AUTO REMOTE EXEC property in the altibase properties file to 1 achieves the same result All of the objects referenced in the query must reside on the remote server The query can only reference a single Database Link object The query must not contain any references to stored procedures or sequences The query must not contain any references to host variables In the case of a subquery the subquery must not reference any columns objects or aliases defined outside of the subquery If any of the above conditions is false the EXEC REMOTE hint will be ignored When a SELECT query contains subquery the EXEC REMOTE hint can be used in either the main query or the subquery If the EXEC REMOTE hint is located in the main query it will apply to the entire query including the subquery If the EXEC REMOTE hint is located in the subquery it will apply only to the subquery This means that there is no need to use the EXEC REMOTE hint in the subquery when using it in the main query It also
25. means that it is impossible to execute a subquery on the local server when the main query is to be processed on the remote server Example 1 Retrieve names from a table called employees on a remote server referred to as link1 All query processing including the removal of duplicate values will be performed on the remote server SELECT EXEC REMOTE DISTINCT name FROM employeeselink1 Example 2 gt Return everything from table 77 on a remote server referred to as link1 but only if the sum of the values in column 2 in table 72 on the server is greater than 5 for all rows in which 7 equals 3 In this case the main query and the subquery are processed together on the remote server SELECT EXEC REMOTE FROM T1 link1 WHERE 5 lt SELECT EXEC REMOTE SUM I2 FROM T2 link1l WHERE I1 3 Example 3 The query is the same as in Example 2 but the subquery is first passed to the remote server and executed The condition is then evaluated on the local server and is used to determine whether to retrieve data from table 77 on the remote server SELECT FROM 1 11 1 WHERE 5 lt SELECT EXEC REMOTE SUM I2 FROM T201ink1 WHERE I1 3 Database Link User s Manual 28 Properties related to Database Link Appendix A Property and Data Dictionary Properties related to Database Link In order to use Database Link it will be necessary to set some of the properties in the altibase prop erties file app
26. nk 1 2 Benefits of Database Link Database link realizes the following benefits 1 2 1 Convenience 1 2 2 Efficiency 1 2 3 Scalability 1 2 4 High Availability 1 2 1 Convenience Database Link realizes transparency with respect to the location of remote servers In other words the client application does not need to know the location of the original data used to generate the end result Figure 1 3 An Application Accessing Respective Remote Servers Product Information Manufacturing Information For example assume that product information is stored in Site1 and manufacturing information is stored separately in Site2 as in Figure 1 4 When information about the manufacture of a certain product is desired the client application gets product information from Site1 and manufacturing information from Site2 respectively and then combines the data from Site1 and Site2 to construct the required information 5 Introduction to Database Link 1 2 Benefits of Database Link Figure 1 4 An Application Accessing Servers via Database Link Database Link Customer Information of A Company Customer Information of A s Cooperation Partner In contrast when queries are executed on a local server that is part of a system in which Database Link is deployed the local server displays final results sent from the remote server In other words even though the data are physically distributed acros
27. operating system reaches the limits of its pro cessing capability 1 2 4 High Availability Even if a failure occurs in part of a system running Database Link the rest can continue to function Figure 1 7 illustrates a Database Link system in which product information is stored in Site1 manu facturing information is stored in Site2 and shipping information is stored in Site3 In a system struc tured in this way if some problem afflicts Site 3 there would still be no problem fulfilling requests that pertain only to manufacturing or product information Figure 1 7 Availablity Even in the Event of Partial Failure Application Product Information i 2 Master Sever xw Manufacturing Information Site3 Shipping Information of Product O O Database Link User s Manual 8 1 3 Restrictions 1 3 Restrictions Database Link can be established for following database systems ALTIBASE HDB version 4 3 9 or higher Oracle Database 10g or higher 9 Introduction to Database Link 1 3 Restrictions Database Link User s Manual 10 2 Supported Objects SQL Statements and Data Types This chapter covers the remote schema objects SOL commands and data types that are supported for use with Database Link It includes the following sections 2 1 Accessible Remote Schema Objects 2 2 SQL Statements Supported by Database Link 2 3 Data Types Supported by Database Link 9 Supported Objects
28. or each Altilinker process that is in operation 3 Introduction to Database Link 1 1 What is Database Link Figure 1 1 illustrates the overall process of implementing Database Link When the user executes a query after checking whether a table corresponding to the specified destination table exists on the remote server the results are retrieved from the remote server and the final query results are returned Segregated servers should provide independent operating environments in order to implement Database Link In other words the system should be designed so that queries that pertain to data on respective servers and are stored on a local server are capable of being executed even in the absence of Database Link For example if information about a manufacturing process or a productis stored on one server the system should be designed so that processing can take place without involving other servers when a transaction related to product information occurs The following figure roughly illustrates the Database Link operating environment A large number of sites or servers provide independent operating environments and are interconnected through a computer communication network Each of them has its own database Queries can be sent to other sites and servers for execution through the computer communication network Figure 1 2 Operating Environment of Database Link Database Link User s Manual 4 1 2 Benefits of Database Li
29. ropriately The properties that pertain to Database Link are listed below For more details please refer to the General Reference AUTO REMOTE EXEC DBLINK ENABLE 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 the current state of Database Link using the meta table and performance views listed below For more detailed information about these and other meta tables and performance views please refer to the General Reference SYS_DATABASE_LINKS_ 29 Property and Data Dictionary Data Dictionary related to Database Link VSDBLINK REMOTE STATEMENT INFO VSDBLINK REMOTE TRANSACTION INFO VSDBLINK TRANSACTION INFO VSLINKER STATUS Database Link User s Manual 30 Index A SQL Statements Supported by Database Link 11 Accessible Remote Schema Object 10 Stored Procedures 10 ALTER DATABASE LINKER START 23 ALTER DATABASE LINKER STOP 23 T ALTER SESSION CLOSE DATABASE LINK 24 Table 10 AltiLinker 3 V B View 10 Benifits of Database Link 5 C Convenience 5 CREATE DATABASE LINK 20 D Data Types Supported by Database Link 12 Database Link 2 Database Link Communication 15 Database Link Procedure 14 DCL 11 DDL 11 DML 11 DROP DATABASE LINK 22 E Efficiency 6 EXEC REMOTE hint 28 H High Availability 8 L Local Server 2 Location
30. rt 20300 NLS USE US7ASCII Database mydb FetchBufferSize 64 ReadOnly no In the above example when the DSN called altibase_odbc is created the information about the tar get server to be integrated using Database Link is specified At this time either libaltibase odbc 64bit ul32 so or libaltibase odbc 64bit ul64 so in the SALTIBASE_HOME lib directory can be cho sen as the driver when ALTIBASE HDB is used as the remote database The reason for this is that the sizes of SOLLEN and SQLULEN vary depending on the kind of 64 bit ODBC manager ALTIBASE HDB versions 5 1 5 28 and above support the use of the ALTIBASE HDB 64 bit Unix ODBC driver with both 32 bit and 64 bit 5 64 bit server and client packages include the following two drivers Database Link User s Manual 16 3 2 Configuration ALTIBASE HDB 64 bit unix odbc driver libaltibase odbc 64bit ul64 so SOLLEN is 64bit libaltibase odbc 64bit ul32 so SOLLEN is 32bit The driver should be selected depending on the ODBC manager that is installed according to the following table Table 3 1 ODBC Manager 64 bit odbc manager SOLLEN unix odbc 64 bit 2 2 12 64 bit DBUILD REAL 64 BIT MODE unix odbc 64 bit 2 2 13 BUILD LEGACY 64 BIT MODE 17 Configuration and Operation of Database Link 3 3 Activating AltiLinker 3 3 Activating AltiLinker AltiLinker must be activated in order to use Database Link To achieve this the Database Link related prop
31. rved word The command continues on the next line A syntax element which is not a complete command terminates with this sym bol lii Preface About This Manual The command continues from the previous line A syntax ele ment which is not a complete command starts with this sym bol i A mandatory field comprising multiple options One and only one of the options must be specified An optional field comprising multiple options Only one of the options may be specified An optional field comprising multiple options More than one of the options may be specified A comma must precede every repetition This manual uses the following standard set of conventions to introduce new terms illustrate screen displays describe command syntax and so forth Database Link User s Manual IV About This Manual Indicates an optional field Other Symbols Italics Lower Case Let ters Upper Case Let ters Indicates a mandatory field for which one or more items must be selected A delimiter between optional or mandatory arguments Repetition of the previous argu ment Indicates another iteration of the preceding parameter Indicates that some example code has been omitted Symbols other than those shown above
32. s multiple servers or sites they can be accessed as though they were stored in one logical server e p i hn When large amounts of data are centralized in a single location QoS Quality of Service can t be guaranteed because large numbers of simultaneous transactions can have a negative effect on per formance Database Link User s Manual 1 2 Benefits of Database Link Figure 1 5 Single Server Environment ce AE E RE pS TIME 7 4 25 Material Management Customer Information HMM me Figures L However when data that are not strongly interrelated are distributed across multiple servers the workload of serving the data in tables is shared Furthermore the data in respective tables can be combined using Database Link In other words because largely unrelated transactions take place independently on different servers any performance degradation is mitigated and system resources such as CPU memory and I O are used more efficiently Figure 1 6 Distributed Data Environment AP2 Material Management Hemote DB1 Master DB Sales Figures Hemote DB2 AP3 7 Introduction to Database Link 1 2 Benefits of Database Link 1 2 3 Scalability Database Link has more flexible scalability than centralized systems because when using Database Link more servers can be flexibly installed when the
33. tion 3 2 2 How to Configure odbc ini Database Link uses ODBC to enable a local server to access a remote server The ALTIBASE HDB ODBC driver is included in the installation package To use it an ODBC driver manager must addi tionally be installed in the system Because ALTIBASE HDB doesn t provide an ODBC driver manager it must be installed separately The Unix ODBC driver manager is one open source product that is available for use in Unix and Linux environments You can find it at http www unixodbc org Other common ODBC driver managers are Data Direct and EasySoft Windows provides its own ODBC driver manager The ODBC environ ment provides a common interface for data access via the ODBC driver manager However the sys tem construction can vary depending on the specific driver manager that is used Users must add the library path of the installed ODBC driver manager to the environment variable that points at the library paths of the account that is used to run ALTIBASE HDB and must create an odbc ini file in the home directory for the ALTIBASE HDB account or in the etc directory in the direc tory where the ODBC driver manager is installed The following DSN must be added to odbc ini More than one DSN can be added to 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 Po
Download Pdf Manuals
Related Search