4 Using Heterogeneous Services Agents< /title> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <meta http-equiv="Content-Language" content="EN"> <m eta http-equiv="Content-Style-Type" content="text/css"> <meta http-equiv="Content-Script-Type" content="text/javascript"> <meta name= "robots" content="all" scheme="http://www.robotstxt.org/"> <meta name="doctitle" content="Oracle® Database Heterogeneous Connecti vity Administrator's Guide 10g Release 1 (10.1)"> <meta name="partno" content="B10764-01"> <meta name="docid" content="HETER"> <link rel="Start" href="../../index.htm" title="Home" type="text/html"> <link rel="Copyright" href="../../dcommon/html/cpyr.htm" title="Cop yright" type="text/html"> <link rel="Stylesheet" href="../../dcommon/css/doccd.css" title="Default" type="text/css"> <link rel="Conte nts" href="toc.htm" title="Contents" type="text/html"> <link rel="Index" href="index.htm" title="Index" type="text/html"> <link rel=" Prev" href="majfeat.htm" title="Previous" type="text/html"> <link rel="Next" href="multithr.htm" title="Next" type="text/html"> <link rel="alternate" href="../b10764.pdf" title="PDF version" type="application/pdf"> </head> <body> <div class="header"> <p><a name="top " id="top" href="#BEGIN">Skip Headers</a></p> <table summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" v align="top"><font color="#330099" face="Helvetica, Arial, sans-serif"><strong>Oracle® Database Heterogeneous Connectivity Adminis trator's Guide<br> 10<i>g</i> Release 1 (10.1)</strong><br> Part Number B10764-01</font></td> <td valign="bottom" align="right"> <ta ble summary="" cellspacing="0" cellpadding="0" width="270"> <tr> <td align="center" valign="top"><a href="../../index.htm"><img src=" ../../dcommon/gifs/prodicon.gif" alt="Go to Documentation Home" border="0"><br> <font size="-2">Home</font></a></td> <td align="cente r" valign="top"><a href="../../nav/portal_3.htm"><img src="../../dcommon/gifs/bookicon.gif" alt="Go to Book List" border="0"><br> <fo nt size="-2">Book List</font></a></td> <td align="center" valign="top"><a href="toc.htm"><img src="../../dcommon/gifs/conticon.gif" a lt="Go to Table of Contents" border="0"><br> <font size="-2">Contents</font></a></td> <td align="center" valign="top"><a href="index. htm"><img src="../../dcommon/gifs/indxicon.gif" alt="Go to Index" border="0"><br> <font size="-2">Index</font></a></td> <td align="ce nter" valign="top"><a href="../../mix$101/b12039/toc.htm"><img src="../../dcommon/gifs/mix.gif" alt="Go to Master Index" border="0">< br> <font size="-2">Master Index</font></a></td> <td align="center" valign="top"><a href="../../dcommon/html/feedback.htm"><img src="../../dcommon/gifs/feedback.gif" alt="Go to Feedback page" border="0"><br> <font size="-2">Feedback</font></a></td></tr></table> </td></tr></table> <hr> <table summary="layout table" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top "> <table summary="layout table" cellspacing="0" cellpadding="0" align="left" width="90"> <tr> <td align="center" valign="top"><a hre f="majfeat.htm"><img src="../../dcommon/gifs/larrow.gif" alt="Go to previous page" border="0"><br> <font size="-2">Previous</font></a ></td> <td align="center" valign="top"><a href="multithr.htm"><img src="../../dcommon/gifs/rarrow.gif" alt="Go to next page" border=" 0"><br> <font size="-2">Next</font></a></td></tr></table></td> <td align="right" valign="top"><a href="../b10764.pdf"><font size="-2" >View PDF</font></a></td></tr></table> <a name="BEGIN"></a></div> <div class="IND"> <a name="1007206"></a>  <h1 class="Title"><font face="Arial, Helvetica, sans-serif" color="#330099">4<br> <a name="HETER004">Using Heter ogeneous Services Agents</a></font></h1>  <a name="1005904"></a> <p class="BP">This chapter explains how to use Het erogeneous Services (HS) agents. It contains the following sections:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="100590 8"></a><a href="admin.htm#1005946">Setting Up Access to Non-Oracle Systems</a></li> <li class="LB1" type="disc"><a name="1005912"></a ><a href="admin.htm#1006095">Setting Initialization Parameters</a></li> <li class="LB1" type="disc"><a name="1005916"></a><a href="ad min.htm#1006151">Optimizing Data Transfers Using Bulk Fetch</a></li> <li class="LB1" type="disc"><a name="1005920"></a><a href="admin .htm#1006215">Registering Agents</a></li> <li class="LB1" type="disc"><a name="1005924"></a><a href="admin.htm#1006352">Oracle Databa se Server SQL Construct Processing</a></li> <li class="LB1" type="disc"><a name="1007468"></a><a href="admin.htm#1007427">Executing U ser-Defined Functions on a Non-Oracle Database</a></li> <li class="LB1" type="disc"><a name="1005928"></a><a href="admin.htm#1006432" >Using Synonyms</a></li> <li class="LB1" type="disc"><a name="1005931"></a><a href="admin.htm#1006559">Copying Data from the Oracle D atabase Server to the Non-Oracle Database System</a></li> <li class="LB1" type="disc"><a name="1005935"></a><a href="admin.htm#100662 1">Copying Data from the Non-Oracle Database System to the Oracle Database Server</a></li> <li class="LB1" type="disc"><a name="10059 40"></a><a href="admin.htm#1006659">Heterogeneous Services Data Dictionary Views</a></li> <li class="LB1" type="disc"><a name="100594 4"></a><a href="admin.htm#1006890">Using the Heterogeneous Services Dynamic Performance Views</a></li></ul> <a name="1005946"></a> <! --TOC=h1-"1005946"--> <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">Setting Up Access to Non-Oracle System s</font></h2>  <a name="1005948"></a> <p class="BP">This section explains the generic steps to configure access to a n on-Oracle system.</p> <a name="1007689"></a> <div align="center"> <table class="Note" border="0" width="80%" cellpadding="0" cellspa cing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="Note "> <td class="Note"> <hr> <a name="1005951"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH"><strong class="Bold">Not e</strong>:</strong></font> <a name="1005952"></a> <p class="NB">The instructions for configuring your agent may differ slightly fro m the following instructions. Please see the <em class="Italic">Installation and User's Guide</em> for your agent for more complete i nstallation information.</p> <hr></td></tr></table></div> <a name="1005954"></a> <p class="BP">The steps for setting up access to a non-Oracle system are:</p> <a name="1005962"></a> <p class="BP"><a href="admin.htm#1005983">Step 1: Configure Oracle Net Services to Access Heterogeneous Services Agents</a></p> <a name="1005966"></a> <p class="BP"><a href="admin.htm#1006041">Step 2: Create the Da tabase Link to the Non-Oracle System</a></p> <a name="1005970"></a> <p class="BP"><a href="admin.htm#1006049">Step 3: Test the Conne ction</a></p> <a name="1005983"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099" >Step 1: Configure Oracle Net Services to Access Heterogeneous Services Agents</font></h3>  <a name="1005985"></a> <p class="BP">To initiate a connection to the non-Oracle system, the Oracle server starts an agent process through the Oracle Net listen er. For the Oracle server to be able to connect to the agent, you must:</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value= "1"><a name="1005988"></a>Set up a Oracle Net service name for the agent that can be used by the Oracle server. The Oracle Net servic e name descriptor includes protocol-specific information needed to access the Oracle Net listener. The service name descriptor must i nclude the <code>(HS=OK)</code> clause to ensure the connection uses Oracle Heterogeneous Services. The description of this service n ame is defined in <code>tnsnames.ora</code>, the Oracle Names server, or in third-party name servers using the Oracle naming adapter. <p><a name="1005989"></a></p> <p class="BP1">The following is a sample entry for service name in the <code>tnsnames.ora</code> file :</p> <pre class="CE"><a name="1005990"></a> Sybase_sales= (DESCRIPTION= <a name="1005991"></a> (ADDRESS=( PROTOCOL=tcp) <a name="1005992"></a> (HOST=dlsun206) <a name="1005993"></a> (PORT=1521) <a name="1005994"></a> ) <a name="1005995"></a> (CONNECT_DATA = (SERVICE_NAME=SalesDB) <a name="1005996"></a> ) <a name="1005997"></a> (HS = OK) <a n ame="1005998"></a> ) <a name="1005999"></a> </pre> </li> <li class="LN1" type="1" value="2"><a name="1006000"></a>Se t up the listener on the gateway to listen for incoming request from the Oracle server and spawn Heterogeneous Services agents. Then, start the listener on the gateway machine. <p><a name="1006001"></a></p> <p class="BP1">The following is a sample entry for the lis tener in <code>listener.ora</code>:</p> <pre class="CE"><a name="1006002"></a> LISTENER = <a name="1006003"></a> (ADDRESS_LI ST = <a name="1006004"></a> (ADDRESS= (PROTOCOL=tcp) <a name="1006005"></a> (HOST = dlsun206) <a name="10 06006"></a> (PORT = 1521) <a name="1006007"></a> ) <a name="1006008"></a> ) <a name="1006009"></a> ... <a name="1006010"></a> SID_LIST_LISTENER = <a name="1006011"></a> (SID_LIST = <a name="1006012"></a> (SID_D ESC = (SID_NAME=SalesDB) <a name="1006013"></a> (ORACLE_HOME=/home/oracle/megabase/9.0.1) <a name="1006014"></a> (PROGRAM=tg4mb80) <a name="1006015"></a> (ENVS=LD_LIBRARY_PATH=<em class="Italic">non_orac le_system_lib_directory</em>) <a name="1006016"></a> ) <a name="1006017"></a> ) <a name="1006018"></a> </pre> <a name ="1006019"></a> <p class="BP1">The value associated with the <code>PROGRAM</code> keyword defines the name of the agent executable. T he full path of the directory which contains the DLL that is loaded by the Heterogeneous Services agent is specified by <code>LD_LIBR ARY_PATH</code>. Typically, you use <code>SID_NAME</code> to define the initialization parameter file for the agent.</p> <a name="100 6037"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="Th is is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1006022"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="1006023"></a> <u l class="NL"> <li class="NL" type="disc"><a name="1006029"></a><em class="Italic"><a class="xlinkSRC NETAG0133" href="../../network.1 01/b10775/advcfg.htm#NETAG0133">Oracle Net Services Administrator's Guide</a></em> for more information about configuring the Oracle Net listener and a net service name for heterogeneous connectivity</li> <li class="NL" type="disc"><a name="1006036"></a><a href="mul tithr.htm#1006042">"Administering Multithreaded Agents"</a><a href="multithr.htm#1006042"></a> for information about starting multith readed agents</li></ul></td></tr></table></div></li></ol> <a name="1006041"></a>  <h3 class="H2"><font face="A rial, Helvetica, sans-serif" color="#330099">Step 2: Create the Database Link to the Non-Oracle System</font></h3>  <a name="1006043"></a> <p class="BP">To create a database link to the non-Oracle system, use the <code>CREATE DATABASE LINK</code> stat ement. The service name that is used in the <code>USING</code> clause of the <code>CREATE DATABASE LINK</code> statement is the Oracl e Net service name.</p> <a name="1007527"></a> <p class="BP">Use quotes with the username and password to avoid differences in case- sensitive behavior between Oracle and non-Oracle databases. For example, enter a statement like the following:</p> <pre class="CE"><a name="1007420"></a>CREATE DATABASE LINK sales <a name="1007421"></a>CONNECT TO "sales1" <a name="1007422"></a>IDENTIFIED BY "Sales1" <a name="1007423"></a>USING 'Sybase_sales'; </pre> <a name="1006049"></a> <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">Step 3: Test the Connection</font></h3>  <a name="1006051"></a> <p class="BP">T o test the connection to the non-Oracle system, use the database link in a SQL or PL/SQL statement. If the non-Oracle system is a SQL -based database, you can execute a <code>SELECT</code> statement from an existing table or view using the database link. For example: </p> <pre class="CE"><a name="1006052"></a>SELECT * FROM product@sales <a name="1006053"></a>WHERE product_name like '%pencil%'; <a name="1006054"></a> </pre> <a name="1006055"></a> <p class="BP">When you try to access the non-Oracle system for the first time, the Heterogeneous Services agent uploads information into the Heterogeneous Services data dictionary. The uploaded information includes: </p> <a name="1006073"></a> <table summary="" class="RuleInformal" border="1" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class="Informal"> <th class="Informal" align="left" valign="bottom" scope="col"><a name="1006058"></a> <font f ace="Arial, Helvetica, sans-serif"><strong>Type of Data</strong></font></th> <th class="Informal" align="left" valign="bottom" scope= "col"><a name="1006060"></a> <font face="Arial, Helvetica, sans-serif"><strong>Explanation</strong></font></th></tr></thead> <tbody> <tr class="Informal" align="left" valign="top"> <td class="Informal"><a name="1006062"></a> <p class="TB">Capabilities of the non-Or acle system</p></td> <td class="Informal"><a name="1006064"></a> <p class="TB">For example, the agent specifies whether it can perfo rm a join, or a <code>GROUP BY</code>.</p></td></tr> <tr class="Informal" align="left" valign="top"> <td class="Informal"><a name="10 06066"></a> <p class="TB">SQL translation information</p></td> <td class="Informal"><a name="1006068"></a> <p class="TB">The agent specifies how to translate Oracle functions and operators into functions and operators of the non-Oracle system.</p></td></tr> <tr cl ass="Informal" align="left" valign="top"> <td class="Informal"><a name="1006070"></a> <p class="TB">Data dictionary translations</p> </td> <td class="Informal"><a name="1006072"></a> <p class="TB">To make the data dictionary information of the non-Oracle system ava ilable just as if it were an Oracle data dictionary, the agent specifies how to translate Oracle data dictionary tables into tables a nd views of the non-Oracle system.</p></td></tr></tbody></table> <a name="1006078"></a> <div align="center"> <table class="Note" bor der="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layo ut table to format a note"> <tr class="Note"> <td class="Note"> <hr> <a name="1006076"></a><font face="Arial, Helvetica, sans-serif"> <strong class="NH"><strong class="Bold">Note</strong>:</strong></font> <a name="1006077"></a> <p class="NB">Most agents upload infor mation into the Oracle data dictionary automatically the first time they are accessed. Some agent vendors may provide scripts, howeve r, that you must run on the Oracle server.</p> <hr></td></tr></table></div> <a name="1006092"></a> <div align="center"> <table class ="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title= "This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1006081"></a><font face="Arial, Helvet ica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="1006087"></a> <p class="NB"><a href="admin.htm#1006659">Heter ogeneous Services Data Dictionary Views</a><a href="admin.htm#1006659"></a> and <a href="apc.htm#634378">Appendix C, "Data Dicti onary Translation Support"</a></p></td></tr></table></div> <a name="1006095"></a>  <h2 class="H1"><font face=" Arial, Helvetica, sans-serif" color="#330099">Setting Initialization Parameters</font></h2>  <a name="1006102"></a> <p class="BP">As mentioned in <a href="tgvsgc.htm#1006031">"Configuring Heterogeneous Services"</a><a href="tgvsgc.htm#1006031"></a>, y ou can configure the gateway using initialization parameters. This is done by creating an initialization file and setting the desired parameters in this file</p> <a name="1006103"></a> <p class="BP">Heterogeneous Services initialization parameters are distinct from Oracle database server initialization parameters. Heterogeneous Services initialization parameters are set in the Heterogeneous Serv ices initialization file and not in the Oracle database server initialization parameter file (<code>init.ora</code> file). There is a Heterogeneous Services initialization file for each gateway instance.</p> <a name="1006104"></a>  <h3 class=" H2"><font face="Arial, Helvetica, sans-serif" color="#330099">Name and Location of Heterogeneous Services Initialization Parameter Fi le</font></h3>  <a name="1006105"></a> <p class="BP">The name of the file is <code>init</code><em class="Italic">sid</ em><code>.ora</code>, where <em class="Italic">sid</em> is the Oracle system identifier used for the gateway.</p> <a name="1006106">< /a> <p class="BP">In the case of Generic Connectivity, the Heterogeneous Services initialization file is located in the directory <c ode>$ORACLE_HOME/hs/admin</code>. In the case of Transparent Gateways it is located in the directory <code>$ORACLE_HOME/product_name/ admin</code> where <em class="Italic">product_name</em> is the name of the product. So, the Sybase gateway initialization file is loc ated in the directory <code>$ORACLE_HOME/tg4sybs/admin</code>.</p> <a name="1006107"></a>  <h3 class="H2"><fon t face="Arial, Helvetica, sans-serif" color="#330099">Syntax for Initialization Parameter Settings</font></h3>  <a name ="1006108"></a> <p class="BP">The initialization file contains a list of initialization parameter settings each of which should be o n a separate line. The syntax to set an initialization parameter is:</p> <pre class="CE"><a name="1006109"></a>[set] [private] <em cl ass="Italic">parameter</em> = parameter_<em class="Italic">value </em><a name="1006110"></a> </pre> <a name="1006111"></a> <p class= "BP">The set and private keywords are optional. If the set keyword is present then the variable will also be set in the environment. If the private keyword is present, the parameter will not be uploaded to the server. In general, it recommended that this keyword not be used - unless the initialization parameter value contains sensitive information (like a password) that should not be sent over th e network from gateway to Oracle server.</p> <a name="1006112"></a> <p class="BP">In the initialization parameter syntax, all keywor ds (<code>SET</code>, <code>PRIVATE</code> and <code>IFILE</code>) are case insensitive. Initialization parameter names and values ar e case sensitive. Most initialization parameters names are uppercase. String values for Heterogeneous Services parameters must be low ercase. Exceptions to this rule are explicitly noted.</p> <a name="1006113"></a> <p class="BP">Another initialization file can be in cluded in an Heterogeneous Services initialization file by using the <code>IFILE</code> directive. The syntax for this is:</p> <pre c lass="CE"><a name="1006114"></a>IFILE = <em class="Italic">path name for file to be included </em> </pre> <a name="1006115"></a> <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">Gateway Initialization Parameters</font> </h3>  <a name="1006116"></a> <p class="BP">Gateway initialization parameters can be divided into two groups. One is a set of generic initialization parameters that are common to all gateways and the other is a set of initialization parameters that ar e specific to individual gateways. The following list of generic initialization parameters are the only ones discussed in this docume nt:</p> <dl class="LS"> <dt class="LSF"><a name="1006117"></a><code>HS_CALL_NAME</code></dt> <dt class="LSF"><a name="1007520"></a><c ode>HS_COMMIT_POINT_STRENGTH</code></dt> <dt class="LSF"><a name="1006118"></a><code>HS_DB_DOMAIN</code></dt> <dt class="LSF"><a name ="1006119"></a><code>HS_DB_INTERNAL_NAME</code></dt> <dt class="LSF"><a name="1006120"></a><code>HS_DB_NAME</code></dt> <dt class="LS F"><a name="1006121"></a><code>HS_DESCRIBE_CACHE_HWM</code></dt> <dt class="LSF"><a name="1006122"></a><code>HS_FDS_CONNECT_INFO</cod e></dt> <dt class="LSF"><a name="1006123"></a><code>HS_FDS_DEFAULT_SCHEMA_NAME</code></dt> <dt class="LSF"><a name="1006124"></a><cod e>HS_FDS_SHAREABLE_NAME</code></dt> <dt class="LSF"><a name="1006125"></a><code>HS_FDS_TRACE_LEVEL</code></dt> <dt class="LSF"><a nam e="1006127"></a><code>HS_LANGUAGE</code></dt> <dt class="LSF"><a name="1006128"></a><code>HS_LONG_PIECE_TRANSFER_SIZE</code></dt> <dt class="LSF"><a name="1006129"></a><code>HS_NLS_DATE_FORMAT</code></dt> <dt class="LSF"><a name="1006130"></a><code>HS_NLS_DATE_LANGU AGE</code></dt> <dt class="LSF"><a name="1006131"></a><code>HS_NLS_NCHAR</code></dt> <dt class="LSF"><a name="1007329"></a><code>HS_N LS_NUMERIC_CHARACTERS</code></dt> <dt class="LSF"><a name="1006132"></a><code>HS_NLS_TIMESTAMP_FORMAT</code></dt> <dt class="LSF"><a name="1006133"></a><code>HS_NLS_TIMESTAMP_TZ_FORMAT</code></dt> <dt class="LSF"><a name="1006134"></a><code>HS_OPEN_CURSORS</code></d t> <dt class="LSF"><a name="1006135"></a><code>HS_ROWID_CACHE_SIZE</code></dt> <dt class="LSF"><a name="1006136"></a><code>HS_RPC_FET CH_REBLOCKING</code></dt> <dt class="LSF"><a name="1006137"></a><code>HS_RPC_FETCH_SIZE</code></dt> <dt class="LSF"><a name="1006138" ></a><code>HS_TIME_ZONE</code></dt> <dt class="LSF"><a name="1007326"></a></dt></dl> <a name="1006139"></a> <p class="BP">Do not use the <code>PRIVATE</code> keyword when setting any of these parameters. Doing that would prevent the parameter from being uploaded to the server and could cause errors in SQL processing. None of these parameters are required to be set in the environment, so the <cod e>SET</code> keyword need not be used.</p> <a name="1006149"></a> <div align="center"> <table class="NoteAlso" border="0" width="80% " cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1006142"></a><font face="Arial, Helvetica, sans-serif"><strong class="N H">See Also:</strong></font> <a name="1006143"></a> <ul class="NL"> <li class="NL" type="disc"><a name="1006146"></a><a href="apa.ht m#634377">Appendix A, "Heterogeneous Services Initialization Parameters"</a> for descriptions of the generic Heterogeneous Servi ces initialization parameters</li> <li class="NL" type="disc"><a name="1006148"></a>Individual gateway documentation for the list of initialization parameters specific to a gateway</li></ul></td></tr></table></div> <a name="1006151"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">Optimizing Data Transfers Using Bulk Fetch</font></h2>  <a name="1006154"></a> <p class="BP">When an application fetches data from a non-Oracle system using Heterogeneous Services, d ata is transferred:</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value="1"><a name="1006155"></a>From the non-Oracle system to the agent process</li> <li class="LN1" type="1" value="2"><a name="1006156"></a>From the agent process to the Oracle database ser ver</li> <li class="LN1" type="1" value="3"><a name="1006157"></a>From the Oracle database server to the application</li></ol> <a nam e="1006161"></a> <p class="BP">Oracle optimizes all three data transfers, as illustrated in <a href="admin.htm#1006163">Figure 4-1</a>.</p> <a name="1006163"></a> <h4 class="FT"><font face="Arial, Helvetica, sans-serif"><em>Figure 4-1 Optimizing Data Transfer s</em></font></h4> <a name="1006167"><img src="heter009.gif" alt="Text description of heter009.gif follows"></a> <p><a href="img_tex t/heter009.htm">Text description of the illustration heter009.gif</a></p> <a name="1006168"></a> <p class="BP">This section contains the following topics:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1006172"></a><a href="admin.htm#1006186">Using OCI, an Oracle Precompiler, or Another Tool for Array Fetches</a></li> <li class="LB1" type="disc"><a name="1006176"></a><a href="admin.ht m#1006191">Controlling the Array Fetch Between Oracle Database Server and Agent</a></li> <li class="LB1" type="disc"><a name="1006180 "></a><a href="admin.htm#1006198">Controlling the Array Fetch Between Agent and Non-Oracle Server</a></li> <li class="LB1" type="disc "><a name="1006184"></a><a href="admin.htm#1006205">Controlling the Reblocking of Array Fetches</a></li></ul> <a name="1006186"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">Using OCI, an Oracle Precompiler, or Another Tool for Array Fetches</font></h3>  <a name="1006188"></a> <p class="BP">You can optimize data transfers betw een your application and the Oracle server by using array fetches. See your application development tool documentation for informatio n about array fetching and how to specify the amount of data to be sent or each network round trip.</p> <a name="1006191"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">Controlling the Array Fetch Between Oracle Database Server and Agent</font></h3>  <a name="1006193"></a> <p class="BP">When Oracle retrieves data from a non-Ora cle system, the Heterogeneous Services initialization parameter <code>HS_RPC_FETCH_SIZE</code> defines the number of bytes sent for e ach fetch between the agent and the Oracle server. The agent fetches data from the non-Oracle system until one of the following occur s:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1006195"></a>It has accumulated the specified number of bytes to send ba ck to the Oracle database server.</li> <li class="LB1" type="disc"><a name="1006196"></a>The last row of the result set is fetched fr om the non-Oracle system.</li></ul> <a name="1006198"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans- serif" color="#330099">Controlling the Array Fetch Between Agent and Non-Oracle Server</font></h3>  <a name="1006201">< /a> <p class="BP">The initialization parameter <code>HS_FDS_FETCH_ROWS</code> determines the number of rows to be retrieved from a n on-Oracle system. Note that the array fetch must be supported by the agent. See your agent-specific documentation to ensure that your agent supports array fetching.</p> <a name="1006205"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans- serif" color="#330099">Controlling the Reblocking of Array Fetches</font></h3>  <a name="1006207"></a> <p class="BP">B y default, an agent fetches data from the non-Oracle system until it has enough data retrieved to send back to the server. That is, i t keeps going until the number of bytes fetched from the non-Oracle system is equal to or higher than the value of <code>HS_RPC_FETCH _SIZE</code>. In other words, the agent <em class="Italic">reblocks</em> the data between the agent and the Oracle database server in sizes defined by the value of <code>HS_RPC_FETCH_SIZE</code>.</p> <a name="1006209"></a> <p class="BP">When the non-Oracle system s upports array fetches, you can immediately send the data fetched from the non-Oracle system by the array fetch to the Oracle database server without waiting until the exact value of <code>HS_RPC_FETCH_SIZE</code> is reached. That is, you can stream the data from the non-Oracle system to the Oracle database server and disable reblocking by setting the value of initialization parameter <code>HS_RPC _FETCH_REBLOCKING</code> to <code>OFF</code>.</p> <a name="1006211"></a> <p class="BP">For example, assume that you set <code>HS_RPC _FETCH_SIZE</code> to 64 kilobytes (KB) and <code>HS_FDS_FETCH_ROWS</code> to 100 rows. Assume that each row is approximately 600 byt es in size, so that the 100 rows are approximately 60 KB. When <code>HS_RPC_FETCH_REBLOCKING</code> is set to <code>ON</code>, the ag ent starts fetching 100 rows from the non-Oracle system.</p> <a name="1006212"></a> <p class="BP">Because there is only 60 KB of dat a in the agent, the agent does not send the data back to the Oracle database server. Instead, the agent fetches the next 100 rows fro m the non-Oracle system. Because there is now 120 KB of data in the agent, the first 64 KB can be sent back to the Oracle database se rver.</p> <a name="1006213"></a> <p class="BP">Now there is 56 KB of data left in the agent. The agent fetches another 100 rows from the non-Oracle system before sending the next 64 KB of data to the Oracle database server. By setting the initialization parameter < code>HS_RPC_FETCH_REBLOCKING</code> to <code>OFF</code>, the first 100 rows are immediately sent back to the Oracle server.</p> <a na me="1006215"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">Registering Agents </font></h2>  <a name="1006217"></a> <p class="BP"><strong class="Bold">Registration</strong> is an operation through which Oracle stores information about an agent in the data dictionary. Agents do not have to be registered. If an agent is not regist ered, Oracle stores information about the agent in memory instead of in the data dictionary; when a session involving an agent termin ates, this information ceases to be available.</p> <a name="1006218"></a> <p class="BP"><strong class="Bold">Self-registration</stro ng> is an operation in which a database administrator sets an initialization parameter that lets the agent automatically upload infor mation into the data dictionary. In release 8.0 of the Oracle database server, an agent could determine whether to self-register. In Oracle9<em class="Italic">i</em> and later, self-registration occurs only when the <code>HS_AUTOREGISTER</code> initialization parame ter is set to <code>TRUE</code> (default).</p> <a name="1006223"></a> <div align="center"> <table class="Note" border="0" width="80% " cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="Note"> <td class="Note"> <hr> <a name="1006221"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH" >Note:</strong></font> <a name="1006222"></a> <p class="NB"><code>HS_AUTOREGISTER</code> is an Oracle initialization parameter that you set in the <code>init.ora</code> file; it is not a Heterogeneous Services initialization parameter that is set in the gateway ini tialization file.</p> <hr></td></tr></table></div> <a name="1006224"></a> <p class="BP">This section contains the following topics:< /p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1006228"></a><a href="admin.htm#1006234">Enabling Agent Self-Registration</ a></li> <li class="LB1" type="disc"><a name="1006232"></a><a href="admin.htm#1006345">Disabling Agent Self-Registration</a></li></ul> <a name="1006234"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">Enabling Age nt Self-Registration</font></h3>  <a name="1006236"></a> <p class="BP">To ensure correct operation over heterogeneous database links, agent self-registration automates updates to Heterogeneous Services configuration data that describe agents on remote hosts. Agent self-registration is the default behavior. If you do not want to use the agent self-registration feature, then set the initialization parameter <code>HS_AUTOREGISTER</code> to <code>FALSE</code>.</p> <a name="1006237"></a> <p class="BP">Both the serve r and the agent rely on three types of information to configure and control operation of the Heterogeneous Services connection. These three sets of information are collectively called <strong class="Bold">HS configuration data</strong>:</p> <a name="1006255"></a> < table summary="" class="RuleInformal" border="1" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class=" Informal"> <th class="Informal" align="left" valign="bottom" scope="col"><a name="1006240"></a> <font face="Arial, Helvetica, sans-se rif"><strong>Heterogeneous Services Configuration Data</strong></font></th> <th class="Informal" align="left" valign="bottom" scope=" col"><a name="1006242"></a> <font face="Arial, Helvetica, sans-serif"><strong>Description</strong></font></th></tr></thead> <tbody> < tr class="Informal" align="left" valign="top"> <td class="Informal"><a name="1006244"></a> <p class="TB">Heterogeneous Services init ialization parameters</p></td> <td class="Informal"><a name="1006246"></a> <p class="TB">Provide control over various connection-spe cific details of operation.</p></td></tr> <tr class="Informal" align="left" valign="top"> <td class="Informal"><a name="1006248"></a> <p class="TB">Capability definitions</p></td> <td class="Informal"><a name="1006250"></a> <p class="TB">Identify details like SQL language features supported by the non-Oracle data source.</p></td></tr> <tr class="Informal" align="left" valign="top"> <td class="I nformal"><a name="1006252"></a> <p class="TB">Data dictionary translations</p></td> <td class="Informal"><a name="1006254"></a> <p class="TB">Map references to Oracle data dictionary tables and views into equivalents specific to the non-Oracle data source.</p></td ></tr></tbody></table> <a name="1006266"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" ce llspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class= "NoteAlso"> <td class="NoteAlso"><a name="1006258"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong ></font> <a name="1006265"></a> <p class="NB"><a href="admin.htm#1006327">"Specifying HS_AUTOREGISTER"</a><a href="admin.htm#1006327 "></a></p></td></tr></table></div> <a name="1006267"></a>  <h4 class="H3"><font face="Arial, Helvetica, sans-s erif" color="#330099">Using Agent Self-Registration to Avoid Configuration Mismatches</font></h4>  <a name="1006269"></ a> <p class="BP">HS configuration data is stored in the Oracle database server's data dictionary. Because the agent is possibly remo te, and may therefore be administered separately, several circumstances can lead to configuration mismatches between servers and agen ts:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1006270"></a>An agent can be newly installed on a separate machine so t hat the server has no Heterogeneous Services data dictionary content to represent the agent's HS configuration data.</li> <li class=" LB1" type="disc"><a name="1006271"></a>A server can be newly installed and lack the necessary HS configuration data for existing agen ts and non-Oracle data stores.</li> <li class="LB1" type="disc"><a name="1006272"></a>A non-Oracle instance can be upgraded from an o lder version to a newer version, requiring modification of the HS configuration data.</li> <li class="LB1" type="disc"><a name="10062 76"></a>An Heterogeneous Services agent at a remote site can be upgraded to a new version or patched, requiring modification of the H S configuration data.</li> <li class="LB1" type="disc"><a name="1006277"></a>A database administrator (DBA) at the non-Oracle site ca n change the agent setup, possibly for tuning or testing purposes, in a manner which affects HS configuration data.</li></ul> <a name ="1006278"></a> <p class="BP">Agent self-registration permits successful operation of Heterogeneous Services in all these scenarios. Specifically, agent self-registration enhances interoperability between any Oracle database server and any Heterogeneous Services ag ent, if each is at least as recent as Version 8.0.3. The basic mechanism for this functionality is the ability to upload HS configura tion data from agents to servers.</p> <a name="1006279"></a> <p class="BP">Self-registration provides automatic updating of HS confi guration data residing in the Oracle database server data dictionary. This update ensures that the agent self-registration uploads ne ed to be done only once, on the initial use of a previously unregistered agent. Instance information is uploaded on each connection, not stored in the server data dictionary.</p> <a name="1006280"></a>  <h4 class="H3"><font face="Arial, Helvet ica, sans-serif" color="#330099">Understanding Agent Self-Registration</font></h4>  <a name="1006282"></a> <p class="B P">The Heterogeneous Services agent self-registration feature can:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1006283" ></a>Identify the agent and the non-Oracle data store to the Oracle database server</li> <li class="LB1" type="disc"><a name="1006284 "></a>Permit agents to define Heterogeneous Services initialization parameters for use both by the agent and connected Oracle servers </li> <li class="LB1" type="disc"><a name="1006285"></a>Upload capability definitions and data dictionary translations, if available, from an Heterogeneous Services agent during connection initialization <p><a name="1006290"></a></p> <div align="center"> <table cla ss="Note" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="T his is a layout table to format a note"> <tr class="Note"> <td class="Note"> <hr> <a name="1006288"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">Note:</strong></font> <a name="1006289"></a> <p class="NB">When both the server and the agent are re lease 8.1 or higher, the upload of class information occurs only when the class is undefined in the server data dictionary. Similarly , instance information is uploaded only if the instance is undefined in the server data dictionary.</p> <hr></td></tr></table></div>< /li></ul> <a name="1006291"></a> <p class="BP">The information required for agent self-registration is accessed in the server data d ictionary by using these agent-supplied names:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1006292"></a><code>FDS_CLASS </code></li> <li class="LB1" type="disc"><a name="1006293"></a><code>FDS_CLASS_VERSION</code> <p><a name="1006304"></a></p> <div ali gn="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1006296"></a> <font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="1006302"></a> <p class="NB"><a href= "admin.htm#1006659">"Heterogeneous Services Data Dictionary Views"</a><a href="admin.htm#1006659"></a> to learn how to use the Hetero geneous Services data dictionary views</p></td></tr></table></div></li></ul> <a name="1006307"></a>  <h5 class ="H4"><font face="Arial, Helvetica, sans-serif" color="#330099">FDS_CLASS and FDS_CLASS_VERSION</font></h5>  <a name="1 006308"></a> <p class="BP"><code>FDS_CLASS</code> and <code>FDS_CLASS_VERSION</code> are defined by Oracle or by third-party vendors for each individual Heterogeneous Services agent and version. Oracle Heterogeneous Services concatenates these names to form <code>F DS_CLASS_NAME</code>, which is used as a primary key to access class information in the server data dictionary.</p> <a name="1006309" ></a> <p class="BP"><code>FDS_CLASS</code> should specify the type of non-Oracle data store to be accessed and <code>FDS_CLASS_VERSI ON</code> should specify a version number for both the non-Oracle data store and the agent that connects to it. Note that when any co mponent of an agent changes, <code>FDS_CLASS_VERSION</code> must also change to uniquely identify the new release.</p> <a name="10063 14"></a> <div align="center"> <table class="Note" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="Note"> <td class="Note"> <hr> <a name="1 006312"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">Note:</strong></font> <a name="1006313"></a> <p class="NB"> This information is uploaded when you initialize each connection.</p> <hr></td></tr></table></div> <a name="1006316"></a>  <h5 class="H4"><font face="Arial, Helvetica, sans-serif" color="#330099">FDS_INST_NAME</font></h5>  <a nam e="1006317"></a> <p class="BP"><strong class="Bold">Instance-specific information</strong> can be stored in the server data dictiona ry. The instance name, <code>FDS_INST_NAME</code>, is configured by the DBA who administers the agent; how the DBA performs this conf iguration depends on the specific agent in use.</p> <a name="1006318"></a> <p class="BP">The Oracle database server uses <code>FDS_I NST_NAME</code> to look up instance-specific configuration information in its data dictionary. Oracle uses the value as a primary key for columns of the same name in these views:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1006319"></a><code>FDS_INST_I NIT</code></li> <li class="LB1" type="disc"><a name="1006320"></a><code>FDS_INST_CAPS</code></li> <li class="LB1" type="disc"><a name ="1006321"></a><code>FDS_INST_DD</code></li></ul> <a name="1006322"></a> <p class="BP">Server data dictionary accesses that use <cod e>FDS_INST_NAME</code> also use <code>FDS_CLASS_NAME</code> to uniquely identify configuration information rows. For example, if you port a database from class Sybase8.1.6 to class Sybase8.1.7, both databases can simultaneously operate with instance name <code>SCOTT </code> and use separate sets of configuration information.</p> <a name="1006323"></a> <p class="BP">Unlike class information, insta nce information is not automatically self-registered in the server data dictionary.</p> <ul class="LB1"> <li class="LB1" type="disc"> <a name="1006324"></a>If the server data dictionary contains instance information, it represents DBA-defined setup details which full y define the instance configuration. No instance information is uploaded from the agent to the server.</li> <li class="LB1" type="dis c"><a name="1006325"></a>If the server data dictionary contains no instance information, any instance information made available by a connected agent is uploaded to the server for use in that connection. The uploaded instance data is not stored in the server data di ctionary.</li></ul> <a name="1006327"></a>  <h4 class="H3"><font face="Arial, Helvetica, sans-serif" color="#3 30099">Specifying HS_AUTOREGISTER</font></h4>  <a name="1006329"></a> <p class="BP">The Oracle database server initial ization parameter <code>HS_AUTOREGISTER</code> enables or disables automatic self-registration of Heterogeneous Services agents. Note that this parameter is specified in the Oracle initialization parameter file, not the agent initialization file. For example, you ca n set the parameter as follows:</p> <pre class="CE"><a name="1006330"></a>HS_AUTOREGISTER = TRUE <a name="1006331"></a> </pre> <a na me="1006332"></a> <p class="BP">When set to <code>TRUE</code>, the agent uploads information describing a previously unknown agent cl ass or a new agent version into the server's data dictionary.</p> <a name="1006333"></a> <p class="BP">Oracle recommends that you us e the default value for this parameter (<code>TRUE</code>), which ensures that the server's data dictionary content always correctly represents definitions of class capabilities and data dictionary translations as used in Heterogeneous Services connections.</p> <a n ame="1006343"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" sum mary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="Note Also"><a name="1006336"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="1006342"> </a> <p class="TB"><em class="Italic"><a class="olinkSRC REFRN" href="../../server$101/b10755/toc.htm">Oracle Database Reference</a> </em> for a description of this parameter</p></td></tr></table></div> <a name="1006345"></a>  <h3 class="H2">< font face="Arial, Helvetica, sans-serif" color="#330099">Disabling Agent Self-Registration</font></h3>  <a name="100634 7"></a> <p class="BP">To disable agent self-registration, set the <code>HS_AUTOREGISTER</code> initialization parameter as follows:< /p> <pre class="CE"><a name="1006348"></a>HS_AUTOREGISTER = FALSE <a name="1006349"></a> </pre> <a name="1006350"></a> <p class="BP" >Disabling agent self-registration entails that agent information is not stored in the data dictionary. Consequently, the Heterogeneo us Services data dictionary views are not useful sources of information. Nevertheless, the Oracle server still requires information a bout the class and instance of each agent. If agent self-registration is disabled, the server stores this information in local memory .</p> <a name="1006352"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">Oracle Database Server SQL Construct Processing</font></h2>  <a name="1006353"></a> <p class="BP">The gateway rewrites SQL st atements when the statements need to be translated or post-processed.</p> <a name="1006357"></a> <p class="BP">For example, consider a program that requests the following from the non-Oracle database:</p> <pre class="CE"><a name="1006374"></a>SELECT "COL_A" FROM "t est"@remote_db <a name="1006381"></a> WHERE "COL_A" = INITCAP('jones'); <a name="1007625"></a> </pre> <a name="1006386"></a> <p c lass="BP">The non-Oracle database does not recognize <code>INITCAP</code>, so the Oracle database server does a table scan of <code>t est</code> and filters the results locally. The gateway rewrites the <code>SELECT</code> statement as follows:</p> <pre class="CE"><a name="1006402"></a>SELECT "COL_A" FROM "test"@remote_db; <a name="1006403"></a> </pre> <a name="1006404"></a> <p class="BP">The res ults of the query are sent to the gateway and are filtered by the Oracle database server.</p> <a name="1006405"></a> <p class="BP">C onsider the following <code>UPDATE</code> request:</p> <pre class="CE"><a name="1006421"></a>UPDATE "test"@remote_db WHERE "COL_A" = INITCAP('jones'); <a name="1006422"></a> </pre> <a name="1006423"></a> <p class="BP">In this case, the Oracle database server and th e gateway cannot compensate for the lack of support at the non-Oracle side, so an error is issued.</p> <a name="1006429"></a> <p cla ss="BP">If you are performing operations on large amounts of data stored in the non-Oracle database, keep in mind that some functions require data to be moved to the integrating Oracle database server before processing can occur.</p> <a name="1007427"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">Executing User-Defined Functions on a Non-Ora cle Database</font></h2>  <a name="1007430"></a> <p class="BP">You can execute user-defined functions in a remote non- Oracle database. For example:</p> <pre class="CE"><a name="1007475"></a>SELECT getdeptforemp@Remote_DB(7782) FROM dual; <a name="1007 478"></a> <a name="1007532"></a> </pre> <a name="1007431"></a> <p class="BP">In this example, the user issues a <code>SELECT</code> statement that executes a user-defined function in the remote database that returns department information for employee 7782.</p> <a name="1007432"></a> <p class="BP">When the remote function resides in an Oracle database, then the Oracle database automatically ens ures that the remote function does not update any database state (such as updating rows in a database or updating the PL/SQL package state). The gateway cannot verify this when the remote function resides in a non-Oracle database. Therefore, the user is responsible for ensuring that the user-defined functions do not update the state in any database. Ensuring no updates to the database is required to guarantee read consistency.</p> <a name="1007433"></a> <p class="BP">As a security measure, you must specify the functions that you want to execute remotely and their owners in the <code>HS_CALL_NAME</code> parameter in the gateway-specific initialization param eter file. For example:</p> <pre class="CE"><a name="1007434"></a>HS_CALL_NAME = "owner1.A1, owner2.A2, " <a name="1007457"></a> </pr e> <a name="1007435"></a> <p class="BP"><code>owner1</code> and <code>owner2</code> are the remote function owner names. <code>A1</c ode> and <code>A2</code> are the remote function names. You do not need to specify the remote function owner in the SQL statement. By default, the remote function needs to reside in the schema that the Transparent Gateway connects to. If this is not the case, then y ou must specify the owner of the remote function in the SQL statement.</p> <a name="1007437"></a> <p class="BP">Some other examples of executing user-defined remote functions are: as follows:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1007438"></a>A remote function in a subquery <p><a name="1007459"></a></p> <p class="BP1">The function uses the <code>employee_id</code> column dat a to retrieve the <code>department_id</code> from the <code>EMPLOYEES</code> table in the remote database. The outer query then deter mines all department numbers in the remote database that match the returned list.</p> <pre class="CE"><a name="1007439"></a> SELEC T * FROM departments@remotedb <a name="1007440"></a> WHERE department_id IN <a name="1007441"></a> (SELECT <a name="100 7442"></a> getdeptforemp@remotedb(employee_id) <a name="1007443"></a> FROM employees@remotedb); <a name="1007458">< /a> </pre> </li> <li class="LB1" type="disc"><a name="1007444"></a>Applying a local function to the result of a user-defined remote f unction <p><a name="1007504"></a></p> <p class="BP1">This query returns the maximum salary of all employees on the remote database.< /p> <pre class="CE"><a name="1007445"></a> SELECT max(getsalforemp@remotedb(employee_id)) <a name="1007446"></a> FROM employe es@remotedb; <a name="1007474"></a> </pre> </li> <li class="LB1" type="disc"><a name="1007447"></a>A DML statement <p><a name="1007 505"></a></p> <p class="BP1">The statement uses the output from a user-defined query in the remote database to update the salary colu mn with new salary information.</p> <pre class="CE"><a name="1007448"></a> UPDATE employee_history <a name="1007449"></a> SET salary = emp_changed_salary@remote_db; <a name="1007460"></a> </pre> </li></ul> <a name="1007450"></a> <p class="BP">In these exampl es, the Oracle database passes the function name and owner to the Transparent Gateway. The user-defined function is executed on the r emote database.</p> <a name="1007508"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cells pacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="No teAlso"> <td class="NoteAlso"><a name="1007511"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></ font> <a name="1007512"></a> <p class="NB"><a href="apa.htm#635654">"HS_CALL_NAME"</a><a href="apa.htm#635654"></a></p></td></tr></t able></div> <a name="1006432"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">U sing Synonyms</font></h2>  <a name="1006434"></a> <p class="BP">You can provide complete data location transparency an d network transparency by using the synonym feature of the Oracle database server. When a synonym is defined, you do not have to know the underlying table or network protocol. A synonym can be public, which means that all Oracle users can refer to the synonym. A syn onym can also be defined as private, which means every Oracle user must have a synonym defined to access the non-Oracle table.</p> <a name="1006438"></a> <p class="BP">The following statement creates a system wide synonym for the <code>emp</code> table in the schem a of user <code>ORACLE</code> in the Sybase database:</p> <pre class="CE"><a name="1006457"></a>CREATE PUBLIC SYNONYM emp FOR "ORACLE "."EMP"@SYBS; </pre> <a name="1006467"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cell spacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="N oteAlso"> <td class="NoteAlso"><a name="1006460"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong>< /font> <a name="1006466"></a> <p class="TB"><em class="Italic"><a class="olinkSRC ADMIN" href="../../server$101/b10739/toc.htm">Orac le Database Administrator's Guide</a></em> for information about synonyms</p></td></tr></table></div> <a name="1006468"></a>  <h4 class="H3"><font face="Arial, Helvetica, sans-serif" color="#330099">Example of a Distributed Query</font></h4> < !--/TOC=h3--> <a name="1007690"></a> <div align="center"> <table class="Note" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="Note"> <td c lass="Note"> <hr> <a name="1007699"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">Note:</strong></font> <a name="1 007700"></a> <p class="NB">Modify these examples for your environment. Do not try to execute them as they are written.</p> <hr></td> </tr></table></div> <a name="1006469"></a> <p class="BP">The following statement joins data between the Oracle database server, an I BM DB2 database, and a Sybase database:</p> <pre class="CE"><a name="1006479"></a>SELECT O.CUSTNAME, P.PROJNO, E.ENAME, SUM(E.RATE*P. "HOURS") <a name="1006489"></a> FROM ORDERS@DB2 O, EMP@ORACLE9 E, "PROJECTS"@SYBS P <a name="1006496"></a> WHERE O.PROJNO = P."PROJNO" <a name="1006503"></a> AND P."EMPNO" = E.EMPNO <a name="1006510"></a> GROUP BY O.CUSTNAME, P."PROJNO", E .ENAME; </pre> <a name="1006512"></a> <p class="BP">Through a combination of views and synonyms, using the following SQL statements, the process of distributed queries is transparent to the user:</p> <pre class="CE"><a name="1006513"></a>CREATE SYNONYM ORDERS FOR O RDERS@DB2; <a name="1007539"></a>CREATE SYNONYM PROJECTS FOR "PROJECTS"@SYBS; <a name="1007542"></a>CREATE VIEW DETAILS (CUSTNAME,PRO JNO,ENAME,SPEND) <a name="1007545"></a> AS <a name="1007548"></a> SELECT O.CUSTNAME, P."PROJNO", E.ENAME, SUM(E.RATE*P."HO URS") <a name="1007551"></a> SPEND <a name="1007554"></a> FROM ORDERS O, EMP E, PROJECTS P <a name="1007557"></a> WHER E O.PROJNO = P."PROJNO" <a name="1007560"></a> AND P."EMPNO" = E.EMPNO <a name="1007563"></a> GROUP BY O.CUSTNAME, P."PROJN O", E.ENAME; <a name="1007566"></a> </pre> <a name="1006553"></a> <p class="BP">Use the following SQL statement to retrieve informat ion from the data stores in one statement:</p> <pre class="CE"><a name="1006554"></a>SELECT * FROM DETAILS; <a name="1006555"></a> </ pre> <a name="1006556"></a> <p class="BP">The statement retrieves the following table:</p> <pre class="CE"><a name="1006557"></a>CUS TNAME PROJNO ENAME SPEND -------- ------ ----- ----- ABC Co. 1 Jones 400 ABC Co. 1 Smith 180 XYZ Inc. 2 Jones 400 XYZ Inc. 2 Smith 180 </pre> <a name="1006559"></a> <h2 class="H1"><fo nt face="Arial, Helvetica, sans-serif" color="#330099">Copying Data from the Oracle Database Server to the Non-Oracle Database System </font></h2>  <a name="1006562"></a> <p class="BP">In Oracle9<em class="Italic">i</em>, release 2 and later, Heterogen eous Services supports callback links. This enables SQL statements like the following to be executed:</p> <pre class="CE"><a name="10 07231"></a>INSERT INTO <em>table_name</em>@<em>dblink</em> SELECT <em>column_list</em> FROM <em>table_name</em>; <a name="1006567"></ a> </pre> <a name="1007234"></a> <p class="BP">Even though Heterogeneous Services supports the callback functionality, not all gatew ays have implemented it. If the gateway that you are using has not implemented this functionality, then the preceding <code>INSERT</c ode> statement returns the following error message:</p> <pre class="CE"><a name="1007236"></a>ORA-02025: All tables in the SQL statem ent must be at the remote database </pre> <a name="1007237"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1007240"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH ">See Also:</strong></font> <a name="1007241"></a> <p class="NB">Your gateway documentation for information about support for callba ck links</p></td></tr></table></div> <a name="1006568"></a> <p class="BP">For gateways that do not support callback links, you can u se the SQL*Plus <code>COPY</code> command. The syntax is as follows:</p> <pre class="CE"><a name="1007243"></a>COPY FROM <em>username /password</em>@<em>db_name - </em><a name="1007569"></a>INSERT <em>destination_table</em> - <a name="1007584"></a>USING <em>query; </ em> </pre> <a name="1007242"></a> <p class="BP">The following example selects all rows from the local Oracle <code>emp</code> table, inserts them into the <code>emp</code> table on the non-Oracle database, and commits the transaction:</p> <pre class="CE"><a name="1 006572"></a>COPY FROM SCOTT/TIGER@inst1 - <a name="1007583"></a> INSERT EMP@remote_db - <a name="1007577"></a> USING SELECT * FROM EMP; <a name="1007580"></a> </pre> <a name="1006576"></a> <p class="BP">The <code>COPY</code> command supports <code>APPEND</code>, <code>CREATE</code>, <code>INSERT</code>, and <code>REPLACE</code> options. However, <code>INSERT</code> is the only option supported when copying to non-Oracle. The SQL*Plus <code>COPY</code> command does not support copying to tables with lowercase table names. Us e the following PL/SQL syntax with lowercase table names:</p> <pre class="CE"><a name="1006580"></a>DECLARE <a name="1006581"></a><em class="Italic"> v1</em> <em class="Italic">oracle_table.column1</em>%TYPE; <a name="1006582"></a><em class="Italic"> v2</em> <em cla ss="Italic">oracle_table.column2</em>%TYPE; <a name="1006583"></a><em class="Italic"> v3</em> <em class="Italic">oracle_table.column3 </em>%TYPE; <a name="1006584"></a> . <a name="1006585"></a> . <a name="1006586"></a> . <a name="1006587"></a> CURSOR <em class="Italic">cursor_name</em> IS SELECT * FROM <em class="Italic">oracle_table</em>; <a name="1006588"></a>BEGIN <a name="1006589"> </a> OPEN <em class="Italic">cursor_name</em>; <a name="1006590"></a> LOOP <a name="1006591"></a> FETCH <em class="Italic ">cursor_name</em> INTO <em class="Italic">v1</em>, <em class="Italic">v2</em>, <em class="Italic">v3</em>, ... ; <a name="1006592">< /a> EXIT WHEN <em class="Italic">cursor_name</em>%NOTFOUND; <a name="1006593"></a> INSERT INTO <em class="Italic">destina tion_table</em> VALUES (<em class="Italic">v1</em>, <em class="Italic">v2</em>, <em class="Italic">v3</em>, ...); <a name="1006594">< /a> END LOOP; <a name="1006595"></a> <a name="1006596"></a> CLOSE <em class="Italic">cursor_name</em>; <a name="1006597"></a>EN D; <a name="1007587"></a>/ </pre> <a name="1006604"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpad ding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1006600"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Al so:</strong></font> <a name="1006603"></a> <p class="NB"><em class="Italic"><a class="olinkSRC SQPUG" href="../../server$101/b12170/ toc.htm">SQL*Plus User's Guide and Reference</a></em> for more information about the <code>COPY</code> command</p></td></tr></table>< /div> <a name="1006621"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">Copying Data from the Non-Oracle Database System to the Oracle Database Server</font></h2>  <a name="1006625"></a> <p class=" BP">The <code>CREATE TABLE</code> statement lets you copy data from a non-Oracle database to the Oracle database server. To create a table on the local database and insert rows from the non-Oracle table, use the following syntax:</p> <pre class="CE"><a name="1006630 "></a>CREATE TABLE <em class="Italic">table_name</em> AS <em class="Italic">query; </em><a name="1006631"></a> </pre> <a name="10076 34"></a> <p class="BP">The following example creates the table <code>emp</code> in the local Oracle database and inserts the rows fro m the <code>EMP</code> table of the non-Oracle database:</p> <pre class="CE"><a name="1007644"></a>CREATE TABLE table1 AS SELECT * FR OM "EMP"@remote_db; <a name="1007645"></a> </pre> <a name="1006647"></a> <p class="BP">Alternatively, you can use the SQL*Plus <code >COPY</code> command to copy data from the non-Oracle database to the Oracle database server.</p> <a name="1006657"></a> <div align= "center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1006653"></a><fo nt face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="1006656"></a> <p class="NB"><em class=" Italic"><a class="olinkSRC SQPUG" href="../../server$101/b12170/toc.htm">SQL*Plus User's Guide and Reference</a></em> for more inform ation about the <code>COPY</code> command</p></td></tr></table></div> <a name="1006659"></a>  <h2 class="H1">< font face="Arial, Helvetica, sans-serif" color="#330099">Heterogeneous Services Data Dictionary Views</font></h2>  <a n ame="1006661"></a> <p class="BP">You can use the Heterogeneous Services data dictionary views to access information about Heterogene ous Services. This section addresses the following topics:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1006665"></a><a href="admin.htm#1006683">Understanding the Types of Views</a></li> <li class="LB1" type="disc"><a name="1006669"></a><a href="admin.h tm#1006756">Understanding the Sources of Data Dictionary Information</a></li> <li class="LB1" type="disc"><a name="1006673"></a><a hr ef="admin.htm#1006770">Using the General Views</a></li> <li class="LB1" type="disc"><a name="1006677"></a><a href="admin.htm#1006799" >Using the Transaction Service Views</a></li> <li class="LB1" type="disc"><a name="1006681"></a><a href="admin.htm#1006842">Using the SQL Service Views</a></li></ul> <a name="1006683"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-ser if" color="#330099">Understanding the Types of Views</font></h3>  <a name="1006685"></a> <p class="BP">The Heterogeneo us Services data dictionary views, which all begin with the prefix <code>HS_</code>, can be divided into four main types:</p> <ul cla ss="LB1"> <li class="LB1" type="disc"><a name="1006686"></a>General views</li> <li class="LB1" type="disc"><a name="1006687"></a>View s used for the transaction service</li> <li class="LB1" type="disc"><a name="1006689"></a>Views used for the SQL service</li></ul> <a name="1006690"></a> <p class="BP">Most of the data dictionary views are defined for both classes and instances. Consequently, for m ost types of data there is a <code>*_CLASS</code> and an <code>*_INST</code> view.</p> <a name="1006753"></a> <h5 class="TTW"><a nam e="1006753"></a><a name="1006693"></a> <strong><font face="Arial, Helvetica, sans-serif"><em>Table 4-1 Data Dictionary Views for Hete rogeneous Services</em></font></strong></h5> <table summary="" class="RuleFormalWide" border="1" width="100%" cellpadding="3" cellspa cing="0" dir="ltr" title=""> <thead> <tr class="Formal"> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1006699 "></a> <font face="Arial, Helvetica, sans-serif"><strong>View</strong></font></th> <th class="Formal" align="left" valign="bottom" sc ope="col"><a name="1006701"></a> <font face="Arial, Helvetica, sans-serif"><strong>Type</strong></font></th> <th class="Formal" align ="left" valign="bottom" scope="col"><a name="1006703"></a> <font face="Arial, Helvetica, sans-serif"><strong>Identifies</strong></fon t></th></tr></thead> <tbody> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1006706"></a> <p class="TB">< code>HS_BASE_CAPS</code></p></td> <td class="Formal"><a name="1006708"></a> <p class="TB">SQL service</p></td> <td class="Formal"><a name="1006710"></a> <p class="TB">All capabilities supported by Heterogeneous Services</p></td></tr> <tr class="Formal" align="left " valign="top"> <td class="Formal"><a name="1006713"></a> <p class="TB"><code>HS_BASE_DD</code></p></td> <td class="Formal"><a name= "1006715"></a> <p class="TB">SQL service</p></td> <td class="Formal"><a name="1006717"></a> <p class="TB">All data dictionary trans lation table names supported by Heterogeneous Services</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal" ><a name="1006720"></a> <p class="TB"><code>HS_CLASS_CAPS</code></p></td> <td class="Formal"><a name="1006722"></a> <p class="TB">T ransaction service, SQL service</p></td> <td class="Formal"><a name="1006724"></a> <p class="TB">Capabilities for each class</p></td ></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1006727"></a> <p class="TB"><code>HS_CLASS_DD</code ></p></td> <td class="Formal"><a name="1006729"></a> <p class="TB">SQL service</p></td> <td class="Formal"><a name="1006731"></a> < p class="TB">Data dictionary translations for each class</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Forma l"><a name="1006734"></a> <p class="TB"><code>HS_CLASS_INIT</code></p></td> <td class="Formal"><a name="1006736"></a> <p class="TB" >General</p></td> <td class="Formal"><a name="1006738"></a> <p class="TB">Initialization parameters for each class</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1006741"></a> <p class="TB"><code>HS_FDS_CLASS</code></p></td > <td class="Formal"><a name="1006743"></a> <p class="TB">General</p></td> <td class="Formal"><a name="1006745"></a> <p class="TB"> Classes accessible from the Oracle server</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="100 6748"></a> <p class="TB"><code>HS_FDS_INST</code></p></td> <td class="Formal"><a name="1006750"></a> <p class="TB">General</p></td> <td class="Formal"><a name="1006752"></a> <p class="TB">Instances accessible from the Oracle server</p></td></tr></tbody></table> < a name="1006754"></a> <p class="BP">Like all Oracle data dictionary tables, these views are read-only. Do not change the content of any of the underlying tables.</p> <a name="1006756"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-se rif" color="#330099">Understanding the Sources of Data Dictionary Information</font></h3>  <a name="1006758"></a> <p c lass="BP">The values used for data dictionary content in any particular connection on a Heterogeneous Services database link can come from any of the following sources, in order of precedence:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1006759"></a>In stance information uploaded by the connected Heterogeneous Services agent at the start of the session. This information overrides cor responding content in the Oracle data dictionary, but is never stored into the Oracle data dictionary.</li> <li class="LB1" type="dis c"><a name="1006760"></a>Instance information stored in the Oracle data dictionary. This data overrides any corresponding content for the connected class.</li> <li class="LB1" type="disc"><a name="1006761"></a>Class information stored in the Oracle data dictionary.< /li></ul> <a name="1006762"></a> <p class="BP">If the Oracle database server runs with the <code>HS_AUTOREGISTER</code> server initi alization parameter set to <code>FALSE</code>, then no information is stored automatically in the Oracle data dictionary. The equival ent data is uploaded by the Heterogeneous Services agent on a connection-specific basis each time a connection is made, with any inst ance-specific information taking precedence over class information.</p> <a name="1006767"></a> <div align="center"> <table class="No te" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="Note"> <td class="Note"> <hr> <a name="1006765"></a><font face="Arial, Helvetica, sans- serif"><strong class="NH">Note:</strong></font> <a name="1006766"></a> <p class="NB">It is not possible to determine positively what capabilities and what data dictionary translations are in use for a given session due to the possibility that an agent can upload in stance information.</p> <hr></td></tr></table></div> <a name="1006768"></a> <p class="BP">You can determine the values of Heterogene ous Services initialization parameters by querying the <code>VALUE</code> column of the <code>V$HS_PARAMETER</code> view. Note that t he <code>VALUE</code> column of <code>V$HS_PARAMETER</code> truncates the actual initialization parameter value from a maximum of 255 characters to a maximum of 64 characters, and it truncates the parameter name from a maximum of 64 characters to a maximum of 30 cha racters.</p> <a name="1006770"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099"> Using the General Views</font></h3>  <a name="1006772"></a> <p class="BP">The views that are common for all services a re as follows:</p> <a name="1006787"></a> <table summary="" class="RuleInformal" border="1" width="100%" cellpadding="3" cellspacing ="0" dir="ltr" title=""> <thead> <tr class="Informal"> <th class="Informal" align="left" valign="bottom" scope="col"><a name="1006775 "></a> <font face="Arial, Helvetica, sans-serif"><strong>View</strong></font></th> <th class="Informal" align="left" valign="bottom" scope="col"><a name="1006777"></a> <font face="Arial, Helvetica, sans-serif"><strong>Contains</strong></font></th></tr></thead> <tbod y> <tr class="Informal" align="left" valign="top"> <td class="Informal"><a name="1006779"></a> <p class="TB"><code>HS_FDS_CLASS</cod e> <a name="1006780"></a></p> <p class="TB"><code>HS_FDS_INST</code></p></td> <td class="Informal"><a name="1006782"></a> <p class=" TB">Names of the instances and classes that are uploaded into the Oracle data dictionary</p></td></tr> <tr class="Informal" align="le ft" valign="top"> <td class="Informal"><a name="1006784"></a> <p class="TB"><code>HS_CLASS_INIT</code></p></td> <td class="Informal" ><a name="1006786"></a> <p class="TB">Information about the Heterogeneous Services initialization parameters</p></td></tr></tbody></ table> <a name="1006788"></a> <p class="BP">For example, you can access multiple Sybase gateways from an Oracle database server. Aft er accessing the gateways for the first time, the information uploaded into the Oracle database server could appear as follows:</p> < pre class="CE"><a name="1006789"></a>SQL> SELECT * FROM hs_fds_class; <a name="1006790"></a> <a name="1006791"></a>FDS_CLASS_NAME FDS_CLASS_COMMENTS FDS_CLASS_ID <a name="1006792"></a>--------------------- ------------------------------ ------ ------ <a name="1006793"></a>Sybase816 Uses Sybase driver, R1.1 1 <a name="1006794"></a>Sybase817 Uses Sybase driver, R1.2 21 <a name="1006795"></a> </pre> <a name="1006796"></a> <p class="BP">Two classes are u ploaded: a class that accesses Sybase816 and a class that accesses Sybase817. The data dictionary in the Oracle database server now c ontains capability information, SQL translations, and data dictionary translations for both Sybase816 and Sybase817.</p> <a name="100 6797"></a> <p class="BP">In addition to this information, the Oracle database server data dictionary also contains instance informat ion in the <code>HS_FDS_INST</code> view for each non-Oracle system instance that is accessed.</p> <a name="1006799"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">Using the Transaction Service Views</font></h3>  <a name="1006801"></a> <p class="BP">When a non-Oracle system is involved in a distributed transaction, the transact ion capabilities of the non-Oracle system and the agent control whether it can participate in distributed transactions. Transaction c apabilities are stored in the <code>HS_CLASS_CAPS</code> tables.</p> <a name="1006802"></a> <p class="BP">The ability of the non-Ora cle system and agent to support two-phase commit protocols is specified by the 2PC type capability, which can specify one of the foll owing five types:</p> <a name="1006828"></a> <table summary="" class="HRuleInformalWide" border="1" frame="HSIDES" rules="ROWS" widt h="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class="Informal"> <th class="Informal" align="left" valign=" bottom" scope="col"><a name="1006805"></a> <font face="Arial, Helvetica, sans-serif"><strong>Type</strong></font></th> <th class="Inf ormal" align="left" valign="bottom" scope="col"><a name="1006807"></a> <font face="Arial, Helvetica, sans-serif"><strong>Capability</ strong></font></th></tr></thead> <tbody> <tr class="Informal" align="left" valign="top"> <td class="Informal"><a name="1006809"></a> <p class="TB">Read-only (RO)</p></td> <td class="Informal"><a name="1006811"></a> <p class="TB">The non-Oracle system can only be q ueried with SQL <code>SELECT</code> statements. Procedure calls are not allowed because procedure calls are assumed to write data.</p ></td></tr> <tr class="Informal" align="left" valign="top"> <td class="Informal"><a name="1006813"></a> <p class="TB">Single-Site (S S)</p></td> <td class="Informal"><a name="1006815"></a> <p class="TB">The non-Oracle system can handle remote transactions but not d istributed transactions. That is, it cannot participate in the two-phase commit protocol.</p></td></tr> <tr class="Informal" align="l eft" valign="top"> <td class="Informal"><a name="1006817"></a> <p class="TB">Commit Confirm (CC)</p></td> <td class="Informal"><a na me="1006819"></a> <p class="TB">The non-Oracle system can participate in distributed transactions. It can participate in the server' s two-phase commit protocol but only as the Commit Point Site. That is, it cannot prepare data, but it can remember the outcome of a particular transaction if asked by the global coordinator.</p></td></tr> <tr class="Informal" align="left" valign="top"> <td class="I nformal"><a name="1006821"></a> <p class="TB">Two-Phase Commit</p></td> <td class="Informal"><a name="1006823"></a> <p class="TB">T he non-Oracle system can participate in distributed transactions. It can participate in the server's two-phase commit protocol, as a regular two-phase commit node, but not as a Commit Point Site. That is, it can prepare data, but it cannot remember the outcome of a particular transaction if asked to by the global coordinator.</p></td></tr> <tr class="Informal" align="left" valign="top"> <td class ="Informal"><a name="1006825"></a> <p class="TB">Two-Phase Commit Confirm</p></td> <td class="Informal"><a name="1006827"></a> <p c lass="TB">The non-Oracle system can participate in distributed transactions. It can participate in the server's two-phase commit prot ocol as a regular two-phase commit node or as the Commit Point Site. That is, it can prepare data and it can remember the outcome of a particular transaction if asked by the global coordinator.</p></td></tr></tbody></table> <a name="1006829"></a> <p class="BP">The transaction model supported by the driver and non-Oracle system can be queried from Heterogeneous Services' data dictionary view <cod e>HS_CLASS_CAPS</code>.</p> <a name="1006830"></a> <p class="BP">One of the capabilities is of the 2PC type:</p> <pre class="CE"><a name="1006831"></a>SELECT cap_description, translation <a name="1006832"></a>FROM hs_class_caps <a name="1006833"></a>WHERE cap_de scription LIKE '2PC%' <a name="1006834"></a>AND fds_class_name LIKE 'SYBASE%'; <a name="1006835"></a> <a name="1006836"></a>CAP_DE SCRIPTION TRANSLATION <a name="1006837"></a>---------------------------------------- ----------- <a name="10 06838"></a>2PC type (RO-SS-CC-PREP/2P-2PCC) CC <a name="1006839"></a> </pre> <a name="1006840"></a> <p class="BP">W hen the non-Oracle system and agent support distributed transactions, the non-Oracle system is treated like any other Oracle server. When a failure occurs during the two-phase commit protocol, the transaction is recovered automatically. If the failure persists, the in-doubt transaction may need to be manually overridden by the database administrator.</p> <a name="1006842"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">Using the SQL Service Views</font></h3>  <a name="1006844"></a> <p class="BP">Data dictionary views that are specific for the SQL service contain information about:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1006845"></a>SQL capabilities and SQL translations of the non-Oracle data source</l i> <li class="LB1" type="disc"><a name="1006846"></a>Data dictionary translations to map Oracle data dictionary views to the data dic tionary of the non-Oracle system <p><a name="1006851"></a></p> <div align="center"> <table class="Note" border="0" width="80%" cellp adding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note "> <tr class="Note"> <td class="Note"> <hr> <a name="1006849"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">Note:< /strong></font> <a name="1006850"></a> <p class="NB">This section describes only a portion of the SQL Service-related capabilities. Because you should never need to alter these settings for administrative purposes, these capabilities are not discussed here.</p> <hr ></td></tr></table></div></li></ul> <a name="1006852"></a>  <h4 class="H3"><font face="Arial, Helvetica, sans- serif" color="#330099">Using Views for Capabilities and Translations</font></h4>  <a name="1006854"></a> <p class="BP" >The <code>HS_*_CAPS</code> data dictionary tables contain information about the SQL capabilities of the non-Oracle data source and r equired SQL translations. These views specify whether the non-Oracle data store or the Oracle database server implements certain SQL language features. If a capability is turned off, then Oracle does not send any SQL statements to the non-Oracle data source that req uire this particular capability, but it still performs post-processing.</p> <a name="1006855"></a>  <h4 class= "H3"><font face="Arial, Helvetica, sans-serif" color="#330099">Using Views for Data Dictionary Translations</font></h4>  <h2 class="H1"><font fa ce="Arial, Helvetica, sans-serif" color="#330099">Using the Heterogeneous Services Dynamic Performance Views</font></h2>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">Deter mining Which Agents Are Running on a Host</font></h3>  <a name="1006903"></a> <p class="BP">The following view shows g eneration information about agents:</p> <a name="1006914"></a> <table summary="" class="RuleInformal" border="1" width="100%" cellpa dding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class="Informal"> <th class="Informal" align="left" valign="bottom" scope=" col"><a name="1006906"></a> <font face="Arial, Helvetica, sans-serif"><strong>View</strong></font></th> <th class="Informal" align="l eft" valign="bottom" scope="col"><a name="1006908"></a> <font face="Arial, Helvetica, sans-serif"><strong>Purpose</strong></font></th ></tr></thead> <tbody> <tr class="Informal" align="left" valign="top"> <td class="Informal"><a name="1006911"></a> <p class="TB"><co de>V$HS_AGENT</code></p></td> <td class="Informal"><a name="1006913"></a> <p class="TB">Identifies the set of Heterogeneous Services agents currently running on a given host, using one row for each agent process.</p></td></tr></tbody></table> <a name="1006916"></a> <p class="BP">Use this view to determine general information about the agents running on a specified host. The following table show s the most relevant columns (for a description of all the columns in the view, see <a class="olinkSRC REFRN" href="../../server$101/b 10755/toc.htm"><em class="Italic">Oracle Database Reference</em></a>):</p> <a name="1006955"></a> <h5 class="TTW"><a name="1006955"> </a><a name="1006924"></a> <strong><font face="Arial, Helvetica, sans-serif"><em>Table 4-2 V$HS_AGENT</em></font></strong></h5> <tabl e summary="" class="RuleFormalWide" border="1" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class="Fo rmal"> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1006928"></a> <font face="Arial, Helvetica, sans-serif">< strong>Column</strong></font></th> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1006930"></a> <font face="Ari al, Helvetica, sans-serif"><strong>Description</strong></font></th></tr></thead> <tbody> <tr class="Formal" align="left" valign="top" > <td class="Formal"><a name="1006932"></a> <p class="TB"><code>AGENT_ID</code></p></td> <td class="Formal"><a name="1006934"></a> <p class="TB">Oracle Net session identifier used for connections to agent (<code>listener.ora SID</code>)</p></td></tr> <tr class="Fo rmal" align="left" valign="top"> <td class="Formal"><a name="1006936"></a> <p class="TB"><code>MACHINE</code></p></td> <td class="Fo rmal"><a name="1006938"></a> <p class="TB">Operating system machine name</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1006940"></a> <p class="TB"><code>PROGRAM</code></p></td> <td class="Formal"><a name="1006942"></a> <p class="TB">Program name of agent</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1006944"></ a> <p class="TB"><code>AGENT_TYPE</code></p></td> <td class="Formal"><a name="1006946"></a> <p class="TB">Type of agent</p></td></t r> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1006948"></a> <p class="TB"><code>FDS_CLASS_ID</code></ p></td> <td class="Formal"><a name="1006950"></a> <p class="TB">The ID of the foreign data store class</p></td></tr> <tr class="Form al" align="left" valign="top"> <td class="Formal"><a name="1006952"></a> <p class="TB"><code>FDS_INST_ID</code></p></td> <td class=" Formal"><a name="1006954"></a> <p class="TB">The instance name of the foreign data store</p></td></tr></tbody></table> <a name="1006 957"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">Determining the Open Heter ogeneous Services Sessions</font></h3>  <a name="1006959"></a> <p class="BP">The following view shows which Heterogene ous Services sessions are open for the Oracle database server:</p> <a name="1006970"></a> <table summary="" class="RuleInformal" bor der="1" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class="Informal"> <th class="Informal" align="le ft" valign="bottom" scope="col"><a name="1006962"></a> <font face="Arial, Helvetica, sans-serif"><strong>View</strong></font></th> <t h class="Informal" align="left" valign="bottom" scope="col"><a name="1006964"></a> <font face="Arial, Helvetica, sans-serif"><strong> Purpose</strong></font></th></tr></thead> <tbody> <tr class="Informal" align="left" valign="top"> <td class="Informal"><a name="10069 67"></a> <p class="TB"><code>V$HS_SESSION</code></p></td> <td class="Informal"><a name="1006969"></a> <p class="TB">Lists the sessi ons for each agent, specifying the database link used.</p></td></tr></tbody></table> <a name="1006971"></a> <p class="BP">The follow ing table shows the most relevant columns (for an account of all the columns in the view, see <a class="olinkSRC REFRN" href="../../s erver$101/b10755/toc.htm"><em class="Italic">Oracle Database Reference</em></a>):</p> <a name="1007002"></a> <h5 class="TTW"><a name ="1007002"></a><a name="1006979"></a> <strong><font face="Arial, Helvetica, sans-serif"><em>Table 4-3 V$HS_SESSION</em></font></stron g></h5> <table summary="" class="RuleFormalWide" border="1" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class="Formal"> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1006983"></a> <font face="Arial, Helvetica, sans-serif"><strong>Column</strong></font></th> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1006985"></a> <f ont face="Arial, Helvetica, sans-serif"><strong>Description</strong></font></th></tr></thead> <tbody> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1006987"></a> <p class="TB"><code>HS_SESSION_ID</code></p></td> <td class="Formal"><a nam e="1006989"></a> <p class="TB">Unique Heterogeneous Services session identifier</p></td></tr> <tr class="Formal" align="left" valign ="top"> <td class="Formal"><a name="1006991"></a> <p class="TB"><code>AGENT_ID</code></p></td> <td class="Formal"><a name="1006993"> </a> <p class="TB">Oracle Net session identifier used for connections to agent (<code>listener.ora SID</code>)</p></td></tr> <tr cla ss="Formal" align="left" valign="top"> <td class="Formal"><a name="1006995"></a> <p class="TB"><code>DB_LINK</code></p></td> <td cla ss="Formal"><a name="1006997"></a> <p class="TB">Server database link name used to access the agent <code>NULL</code> means that no database link is used (for example, when using external procedures)</p></td></tr> <tr class="Formal" align="left" valign="top"> <td c lass="Formal"><a name="1006999"></a> <p class="TB"><code>DB_LINK_OWNER</code></p></td> <td class="Formal"><a name="1007001"></a> <p class="TB">Owner of the database link in <code>DB_LINK</code></p></td></tr></tbody></table> <a name="1007003"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">Determining the Heterogeneous Services Parameters</fo nt></h3>  <a name="1007004"></a> <p class="BP">The following view shows which Heterogeneous Services parameters are se t in the Oracle database server:</p> <a name="1007015"></a> <table summary="" class="RuleInformal" border="1" width="100%" cellpaddi ng="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class="Informal"> <th class="Informal" align="left" valign="bottom" scope="col "><a name="1007007"></a> <font face="Arial, Helvetica, sans-serif"><strong>View</strong></font></th> <th class="Informal" align="left " valign="bottom" scope="col"><a name="1007009"></a> <font face="Arial, Helvetica, sans-serif"><strong>Purpose</strong></font></th></ tr></thead> <tbody> <tr class="Informal" align="left" valign="top"> <td class="Informal"><a name="1007012"></a> <p class="TB"><code> V$HS_PARAMETER</code></p></td> <td class="Informal"><a name="1007014"></a> <p class="TB">Lists Heterogeneous Services parameters and values registered in the Oracle database server.</p></td></tr></tbody></table> <a name="1007016"></a> <p class="BP">The following t able shows the most relevant columns (for an account of all the columns in the view, see <a class="olinkSRC REFRN" href="../../server $101/b10755/toc.htm"><em class="Italic">Oracle Database Reference</em></a>):</p> <a name="1007043"></a> <h5 class="TTW"><a name="100 7043"></a><a name="1007024"></a> <strong><font face="Arial, Helvetica, sans-serif"><em>Table 4-4 V$HS_SESSION</em></font></strong></h 5> <table summary="" class="RuleFormalWide" border="1" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr c lass="Formal"> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1007028"></a> <font face="Arial, Helvetica, sans- serif"><strong>Column</strong></font></th> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1007030"></a> <font f ace="Arial, Helvetica, sans-serif"><strong>Description</strong></font></th></tr></thead> <tbody> <tr class="Formal" align="left" vali gn="top"> <td class="Formal"><a name="1007032"></a> <p class="TB"><code>HS_SESSION_ID</code></p></td> <td class="Formal"><a name="10 07034"></a> <p class="TB">Unique Heterogeneous Services session identifier</p></td></tr> <tr class="Formal" align="left" valign="top "> <td class="Formal"><a name="1007036"></a> <p class="TB"><code>PARAMETER</code></p></td> <td class="Formal"><a name="1007038"></a> <p class="TB">The name of the Heterogeneous Services parameter</p></td></tr> <tr class="Formal" align="left" valign="top"> <td clas s="Formal"><a name="1007040"></a> <p class="TB"><code>VALUE</code></p></td> <td class="Formal"><a name="1007042"></a> <p class="TB" >The value of the Heterogeneous Services parameter</p></td></tr></tbody></table> <a name="1007048"></a> <p class="BP">Information ab out the database link that was used for establishing the distributed connection, the startup time, and the set of initialization para meters used for the session is also available.</p> <a name="1007049"></a> <p class="BP">All of the runtime information is derived fr om dynamically updated tables. The Distributed Access Manager has a refresh capability available through the menu and toolbar that al lows users to rerun queries if necessary and update the data. When the data is refreshed, the tool verifies that the set of registere d agents remains the same. If it is not, the global view is updated.</p> <a name="1007056"></a> <div align="center"> <table class="N oteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="Th is is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1007052"></a><font face="Arial, Helvetica , sans-serif"><strong class="NH">See Also:</strong></font> <a name="1007055"></a> <p class="NB"><em class="Italic"><a class="olinkSR C OEMAG" href="../../dcommon/html/noexist.htm">Oracle Enterprise Manager Administrator's Guide</a></em> and online help for more info rmation about the Distributed Access Manager</p></td></tr></table></div></div> <div class="footer"> <hr> <table summary="" cellspacin g="0" cellpadding="0" width="100%"> <tr> <td style="width: 33%;" align="left"> <table summary="layout table" cellspacing="0" cellpadd ing="0" align="left" width="90"> <tr> <td align="center" valign="top"><a href="majfeat.htm"><img src="../../dcommon/gifs/larrow.gif" alt="Go to previous page" border="0"><br> <font size="-2">Previous</font></a></td> <td align="center" valign="top"><a href="multithr. htm"><img src="../../dcommon/gifs/rarrow.gif" alt="Go to next page" border="0"><br> <font size="-2">Next</font></a></td></tr></table> </td> <td style="width: 34%;" align="center"><img src="../../dcommon/gifs/oracle.gif" alt="Oracle"><br> <a href="../../dcommon/html/c pyr.htm"><font size="-2">Copyright © 2001, 2003 Oracle Corporation</font></a><br> <font size="-2">All Rights Reserved.</font></t d> <td style="width: 33%;" align="right"> <table summary="" cellspacing="0" cellpadding="0" width="270"> <tr> <td align="center" vali gn="top"><a href="../../index.htm"><img src="../../dcommon/gifs/prodicon.gif" alt="Go to Documentation Home" border="0"><br> <font si ze="-2">Home</font></a></td> <td align="center" valign="top"><a href="../../nav/portal_3.htm"><img src="../../dcommon/gifs/bookicon.g if" alt="Go to Book List" border="0"><br> <font size="-2">Book List</font></a></td> <td align="center" valign="top"><a href="toc.htm" ><img src="../../dcommon/gifs/conticon.gif" alt="Go to Table of Contents" border="0"><br> <font size="-2">Contents</font></a></td> <t d align="center" valign="top"><a href="index.htm"><img src="../../dcommon/gifs/indxicon.gif" alt="Go to Index" border="0"><br> <font size="-2">Index</font></a></td> <td align="center" valign="top"><a href="../../mix$101/b12039/toc.htm"><img src="../../dcommon/gifs/m ix.gif" alt="Go to Master Index" border="0"><br> <font size="-2">Master Index</font></a></td> <td align="center" valign="top"><a href="../../dcommon/html/feedback.htm"><img src="../../dcommon/gifs/feedback.gif" alt="Go to Feedback page" border="0"><br> <font si ze="-2">Feedback</font></a></td></tr></table></td></tr></table></div> </body> </html>