Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\sqlsupport.12.08.html
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Create Cache (EXT)</title> <link rel="StyleSheet" href="css/sqlsupport.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="sqlsupport.12.01.html#114255">10 SQL Statements and Extensions for the Salesforce Driver</a> : Create Cache (EXT)</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="114255">Create Cache (EXT)</a></div> <div class="Body"><a name="144988">The Create Cache statement creates a cache that holds the data of a remote table. The </a>data is not loaded into the cache when the Create Cache statement is executed; the data is loaded the first time that the remote table is executed or when a Refresh Cache statement on the remote table is executed. An error is returned if the remote table specified does not exist.</div> <div class="Body"><a name="144989">NOTE: Caches on views are not supported.</a></div> <div class="gutter_text_right"><a name="144990">Grammar</a></div> <div class="syntax_first"><a name="144991">CREATE CACHE ON {</a><span class="EquationVariables">remote_table</span>}</div> <div class="syntax"><a name="144992"> [REFERENCING (</a><span class="EquationVariables">remote_table_ref</span>[,<span class="EquationVariables">remote_table_ref</span>]...)]</div> <div class="syntax"><a name="144993"> [REFRESH_INTERVAL {0 | -1 | </a><span class="EquationVariables">interval_value</span> [{M, H, D}]}]</div> <div class="syntax"><a name="144994"> [INITIAL_CHECK [{ONFIRSTCONNECT | FIRSTUSE | DEFAULT}]</a></div> <div class="syntax"><a name="144995"> [PERSIST {TEMPORARY | MEMORY | DISK | DEFAULT}]</a></div> <div class="syntax"><a name="144996"> [ENABLED {YES | TRUE | NO | FALSE}]</a></div> <div class="syntax"><a name="144997"> [CALL_LIMIT {0 | -1 | </a><span class="EquationVariables">max_calls</span><span class="Syntax">}</span>]</div> <div class="syntax"><a name="144998"> [FILTER (</a><span class="EquationVariables">expression</span>)]</div> <div class="Body"><a name="144999">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="145000">remote_table</a></span> is the name of the remote table from which data is to be cached on the client. The name of the cached table is the same as the name of the remote table. When the table name is specified in a query, the cached table is accessed, not the remote table. </div> <div class="Body"><a name="145001">The remote table name can be a two-part name: </a><span class="EquationVariables">schemaname.tablename</span>. When specifying a two-part name, the specified remote table must be defined in the specified schema, and you must have the privilege to create objects in the specified schema.</div> <div class="Body"><span class="Syntax"><a name="174014">REFERENCING</a></span> is an optional clause that specifies the name of the remote table(s) for which a relationship cache is to be created. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.08.html#174025', '');">â??Relational Cachesâ??</a></span> and <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.08.html#172873', '');">â??Referencing Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="Syntax"><a name="174007">REFRESH_INTERVAL</a></span> is an optional clause that specifies the length of time the data in the cached table can be used before being refreshed. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.08.html#172886', '');">â??Refresh Interval Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="Syntax"><a name="145883">INITIAL_CHECK</a></span> is an optional clause that specifies when the driver initially checks whether the data in the cache needs refreshed. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.08.html#172898', '');">â??Initial Check Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="Syntax"><a name="145887">PERSIST</a></span> is an optional clause that specifies the life span of the data in the cached table or view. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.08.html#172907', '');">â??Persist Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="Syntax"><a name="145891">ENABLED</a></span> is an optional clause that specifies whether the cache is enabled or disabled for use with SQL statements. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.08.html#172931', '');">â??Enabled Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="Syntax"><a name="145895">CALL_LIMIT</a></span> is an optional clause that specifies the maximum number of Web service calls that can be used to populate or refresh the cache. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.08.html#172945', '');">â??Call Limit Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="Syntax"><a name="145899">FILTER</a></span> is an optional clause that specifies a filter for the primary table to limit the number of rows that are cached in the primary table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.08.html#172960', '');">â??Filter Clauseâ??</a></span> for a complete explanation.</div> <div class="Body"><a name="145004">NOTE: If two or more optional clauses are specified, they must be specified in the order </a>shown in the grammar description.</div> <div class="head_2_no_TOC"><a name="174025">Relational Caches</a></div> <div class="Body"><a name="145005">If the Referencing clause is specified, the Create Cache statement creates a set of related </a>caches, one for each of the tables specified in the statement. This set of caches is referred to as a related or relational cache. The set of caches in a relational cache is treated as a single entity. They are refreshed, altered, and dropped as a unit. Any attributes specified in the Create Cache statement apply to the cache created for the primary table and to the caches created for all of the referenced tables specified.</div> <div class="Body"><a name="145006">A database session can have both standalone and relational caches defined, but only one </a>cache can be defined on a table. If a table is referenced in a relational cache definition, a standalone cache cannot be created on that table.</div> <div class="head_2_no_TOC"><a name="172873">Referencing Clause</a></div> <div class="Body"><a name="172874">The Referencing clause specifies the name of the remote table(s) for which a relationship </a>cache is to be created; it is optional. The specified remote table must be related to either the primary table being cached or one of the other specified related tables. The remote table name cannot include a schema name. The referenced tables must exist in the same schema as the primary table. </div> <div class="gutter_text_right"><a name="172878">Grammar</a></div> <div class="syntax_first"><a name="172879">REFERENCING (</a><span class="EquationVariables">remote_table_ref</span>[,<span class="EquationVariables">remote_table_ref</span>]...)]</div> <div class="Body"><a name="172880">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="172881">remote_table_ref</a></span> represents <span class="EquationVariables">remote_table</span><span class="Syntax">[.</span><span class="EquationVariables">foreign_key_name</span><span class="Syntax">]</span></div> <div class="Body"><span class="EquationVariables"><a name="172883">remote_table </a></span>specifies one or more tables related to the primary table that are to be cached in conjunction with the primary table.</div> <div class="Body"><span class="EquationVariables"><a name="172884">foreign_key_name</a></span> specifies the name of the foreign key relationship between the remote table and the primary table (or, optionally, another related table). If a foreign key name is not specified, the driver attempts to find a relationship between the remote table and one of the other tables specified in the relational cache. The driver first looks for a relationship to the primary table. If a relationship to the primary table does not exist, the driver then looks for a relationship to other referenced tables.</div> <div class="head_2_no_TOC"><a name="172886">Refresh Interval Clause</a></div> <div class="Body"><a name="172887">The Refresh Interval clause specifies the length of time the data in the cached table can be </a>used before being refreshed; it is optional. The driver maintains a timestamp of when the data in a table was last refreshed. When a cached table is used in a query, the driver checks if the current time is greater than the last refresh time plus the value of Refresh_Interval. If it is, the driver refreshes the data in the cached table before processing the query. </div> <div class="gutter_text_right"><a name="172888">Grammar</a></div> <div class="syntax_first"><a name="172889">[REFRESH_INTERVAL {0 | -1 | </a><span class="EquationVariables">interval_value</span> [{M, H, D}]}]</div> <div class="Body"><a name="172890">where:</a></div> <div class="Body"><span class="Syntax"><a name="256898">0</a></span> specifies that the cache is refreshed manually. You can use the Refresh Cache statement to refresh the cache manually. </div> <div class="Body"><span class="Syntax"><a name="172891">-1</a></span> resets the refresh interval to the default value of 12 hours.</div> <div class="Body"><span class="EquationVariables"><a name="172892">interval_value</a></span> is a positive integer that specifies the amount of time between refreshes. The default unit of time is hours (<span class="Syntax">H</span>). You can also specify <span class="Syntax">M</span> for minutes or <span class="Syntax">D</span> for days. For example, <span class="Syntax">60M</span> would set the time between refreshes to 60 minutes.</div> <div class="Body"><a name="172893">The default refresh interval is 12 hours.</a></div> <div class="head_2_no_TOC"><a name="172898">Initial Check Clause</a></div> <div class="Body"><a name="172899">The Initial Check clause specifies when the driver performs its initial check of the data in the </a>cache to determine whether it needs to be refreshed; it is optional.</div> <div class="gutter_text_right"><a name="172900">Grammar</a></div> <div class="syntax_first"><a name="172901">[INITIAL_CHECK [ONFIRSTCONNECT | FIRSTUSE | DEFAULT}]</a></div> <div class="Body"><a name="172902">where:</a></div> <div class="Body"><span class="Syntax"><a name="172903">ONFIRSTCONNECT</a></span> specifies that the initial check is performed the first time a connection for a user is established. Subsequently, it is performed each time the table or view is used. A driver session begins on the first connection for a user and the session is active as long as at least one connection is open for the user.</div> <div class="Body"><span class="Syntax"><a name="172904">FIRSTUSE</a></span> specifies that the initial check is performed the first time the table or view is used in a query. Subsequently, it is performed each time the table or view is used.</div> <div class="Body"><span class="Syntax"><a name="172905">DEFAULT</a></span> resets the value back to its default, which is <span class="Syntax">FIRSTUSE</span>.</div> <div class="head_2_no_TOC"><a name="172907">Persist Clause</a></div> <div class="Body"><a name="172908">The Persist clause specifies the life span of the data in the cached table or view; it is </a>optional.</div> <div class="gutter_text_right"><a name="172909">Grammar</a></div> <div class="syntax_first"><a name="172910">[PERSIST {TEMPORARY | MEMORY | DISK | DEFAULT}]</a></div> <div class="Body"><a name="172911">where:</a></div> <div class="Body"><span class="Syntax"><a name="172912">TEMPORARY</a></span> specifies that the data exists for the life of the driver session. When the driver session ends, the data is discarded. A driver session begins on the first connection for a user and the session is active if at least one connection is open for the user.</div> <div class="Body"><span class="Syntax"><a name="172913">MEMORY</a></span> specifies that the data exists beyond the life of the connection. While the connection is active, the cached data is stored in memory. When the connection is closed, the cached data is persisted to disk. If the connection ends abnormally, changes to the cached data may not be persisted to disk. This is the default.</div> <div class="Body"><span class="Syntax"><a name="172918">DISK</a></span> specifies that the data exists beyond the life of the connection. A portion of the cached data is stored in memory while the connection is active. If the size of the cached data exceeds the cache memory threshold, the remaining data is stored on disk. When the connection is closed, the portion of the cached data that is in memory is persisted to disk. If the connection ends abnormally, changes to the cached data held in memory may not be persisted to disk.</div> <div class="Body"><span class="Syntax"><a name="172924">DEFAULT</a></span> resets the <span class="Syntax">PERSIST</span> value back to its default, which is <span class="Syntax">MEMORY</span>.</div> <div class="Body"><a name="172925">WARNING: If you specify a value of </a><span class="Syntax">MEMORY</span> or <span class="Syntax">DISK</span> for the Persist clause, the remote data remains on the client past the lifetime of the application.</div> <div class="Body"><a name="203257">NOTE: You can design your application to force all cached data held in memory to be </a>persisted to disk at any time by using the <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.07.html#107598', '');">Checkpoint</a></span> statement.</div> <div class="head_2_no_TOC"><a name="172931">Enabled Clause</a></div> <div class="Body"><a name="172932">The Enabled clause specifies whether the cache is enabled or disabled for use with SQL </a>statements; it is optional. </div> <div class="gutter_text_right"><a name="172933">Grammar</a></div> <div class="syntax_first"><a name="172934">[ENABLED {YES | TRUE | NO | FALSE}]</a></div> <div class="Body"><a name="172935">where:</a></div> <div class="Body"><span class="Syntax"><a name="172936">YES</a></span> or <span class="Syntax">TRUE</span> specifies that the cache is enabled. When a cache is enabled, the driver accesses the cached data for the remote table or view when a query is executed.</div> <div class="Body"><a name="172937">The driver does not check whether the cache needs to be refreshed when the Alter Cache </a>statement is used to enable the cache. The check occurs the next time that the cache is accessed.</div> <div class="Body"><span class="Syntax"><a name="172938">NO</a></span> or <span class="Syntax">FALSE</span> specifies that the cache is disabled, which means that the driver accesses the data in the remote table or view rather than the cache when a query is executed. The driver does not update the cache when inserts, updates, and deletes are performed on a remote table or view. To use the cache, you must enable it.</div> <div class="Body"><a name="172939">All data in an existing cache is persisted on the client even when the cache is disabled, </a>except for the case where <span class="Syntax">PERSIST</span> is set to <span class="Syntax">TEMPORARY</span>.</div> <div class="Body"><a name="172940">The default is </a><span class="Syntax">TRUE</span>.</div> <div class="head_2_no_TOC"><a name="172945">Call Limit Clause</a></div> <div class="Body"><a name="172946">The Call Limit clause specifies the maximum number of Web service calls that can be used </a>to populate or refresh the cache; it is optional. </div> <div class="gutter_text_right"><a name="172947">Grammar</a></div> <div class="syntax_first"><a name="172948">[CALL_LIMIT {0 | -1 | </a><span class="EquationVariables">max_calls</span><span class="Syntax">}</span>]</div> <div class="Body"><a name="172949">where:</a></div> <div class="Body"><span class="Syntax"><a name="172950">0</a></span> specifies no call limit.</div> <div class="Body"><span class="Syntax"><a name="172951">-1</a></span> resets the call limit back to its default, which is 0 (no call limit).</div> <div class="Body"><span class="EquationVariables"><a name="172952">max_calls</a></span> is a positive integer that specifies the maximum number of Web service calls.</div> <div class="Body"><a name="172953">NOTE: The call limit for a cache is independent of the Stmt_Call_Limit set on a database </a>session. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.05.html#141699', '');">â??Alter Session Attributesâ??</a></span> for details.</div> <div class="Body"><a name="172957">If the call limit of a cache is exceeded during the population or refresh of the cache, the </a>cache is marked as partially initialized. At the next refresh opportunity, the driver attempts to complete the population or refresh of the cache. If the call limit (or other error) occurs during this second attempt, the cache becomes invalid and is disabled. All data in the cache is discarded after the second attempt to populate or refresh the cache fails. Before re-enabling the cache, consider altering the cache definition to allow more Web service calls or specify a more restrictive filter, or both.</div> <div class="Body"><a name="172958">The default value is </a><span class="Syntax">0</span>.</div> <div class="head_2_no_TOC"><a name="172960">Filter Clause</a></div> <div class="Body"><a name="172961">Filter is an optional clause that specifies a filter for the primary table to limit the number of </a>rows that are cached in the primary table. This clause is not supported for views.</div> <div class="gutter_text_right"><a name="172962">Grammar</a></div> <div class="syntax_first"><a name="172963">[FILTER (</a><span class="EquationVariables">expression</span>)]</div> <div class="Body"><a name="172966">where </a><span class="EquationVariables">expression</span> is any valid Where clause. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.23.html#150264', '');">â??Where Clauseâ??</a></span> for details. Do not include the Where keyword in the clause. The filter for an existing cache can be removed by specifying an empty string for the filter expression, for example, <span class="Syntax">FILTER()</span>.</div> <div class="Body"><a name="172968">The default value is that cached data is not filtered.</a></div> <div class="gutter_text_right"><a name="173284">Examples</a></div> <div class="hanging_label_head"><a name="145009">Example A</a></div> <div class="Body"><a name="145011">The Referencing clause allows multiple related tables to be cached as a single entity. The </a>following example creates a cache on the remote table account. The cache is populated with all accounts that had activity in 2010. Additionally, caches are created for the following remote tables: <span class="Syntax">opportunity</span>, <span class="Syntax">contact</span>, and <span class="Syntax">opportunitylineitem</span>. These caches are populated with the opportunities and contacts that are associated with the accounts stored in the accounts cache and the opportunity line items associated with the opportunities stored in the opportunity cache.</div> <div class="syntax_first"><a name="145012">CREATE CACHE ON account </a></div> <div class="syntax"><a name="145013"> REFERENCING (opportunity, contact, opportunitylineitem)</a></div> <div class="syntax"><a name="145014"> FILTER (lastactivitydate >= {d'2010-01-01'})</a></div> <div class="hanging_label_head"><a name="145084">Example B</a></div> <div class="Body"><a name="145085">The following example caches all rows of the account table with a refresh interval of </a>12 hours, checks whether data of the cached table needs to be refreshed on the first use, persists the data beyond the life of the connection, and stores the data in memory while the connection is active.</div> <div class="syntax_first"><a name="145086">CREATE CACHE ON account</a></div> <div class="hanging_label_head"><a name="145087">Example C</a></div> <div class="Body"><a name="146984">The following example caches all active accounts in the account table with a refresh interval </a>of 1 day, checks whether data of the cached table needs to be refreshed when the connection is established, and discards the data when the connection is closed.</div> <div class="syntax_first"><a name="145088">CREATE CACHE ON account REFRESH_INTERVAL 1d </a></div> <div class="syntax"><a name="145089"> INITIAL_CHECK ONFIRSTCONNECT </a></div> <div class="syntax"><a name="145090"> PERSIST TEMPORARY </a></div> <div class="syntax"><a name="145091"> FILTER(account.active = 'Yes')</a></div> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de