<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN" "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd" [
<!ENTITY % CustomDTD SYSTEM "../../../../../../docbook/custom.dtd">
%CustomDTD;
]>
<chapter id="vdbs">
	<title>VDBs</title>
	<section>
		<title>VDB Definition</title>
		<para>A VDB or virtual database definition is contained in an XML file.  
		For .vdb archive files created in the design tool, this file is embedded in the archive and most field can be updated through tooling.  
		The XML schema for this file can be found in the teiid-docs/schema directory.</para>
		<example>
		<title>Example VDB XML</title>
        <programlisting role="XML" language="XML"><![CDATA[<vdb name="${vdb-name}" version="${vdb-version}">

	<!-- VDB properties -->
    <property name="UseConnectorMetadata" value="..." />
    ...

    <!-- define a model fragment for each data source -->
    <model name="${model-name}">
        
        <property name="..." value="..." />
        ...

        <source name="${source-name}" translator-name="${translator-name}" 

        connection-jndi-name="${deployed-jndi-name}">
        ...
    </model>
 
    <!-- create translator instances that override default properties -->

    <translator name="${translator-name}" type="${translator-type}" />
 
        <property name="..." value="..." /> 
        ...

    </translator> 
</vdb>]]></programlisting>
</example>

        <section>
            <title>VDB Element</title>
            <itemizedlist>
                <title>Attributes</title>
                <listitem>
                    <para>
                    <emphasis>name</emphasis>
                    </para>
                    <para>
                    The name of the VDB.  The VDB name 
                    referenced through the driver or datasource during the connection time.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>version</emphasis>
                    </para>
                    <para>
                    The version of the VDB (should be an 
                    positive integer).  This determines the deployed directory location 
                    (see Name), and provides an explicit versioning mechanism to the VDB 
                    name.
                    </para>
                </listitem>
            </itemizedlist>
            
            <itemizedlist>
                <title>Property Elements</title>
                <listitem>
                    <para>
                    <emphasis>UseConnectorMetadata</emphasis>
                    </para>
                    <para>
                    Setting to use connector 
                    supplied metadata. Can be "true" or "cached".  "true" will obtain 
                    metadata once for every launch of Teiid.   "cached" will save a file 
                    containing the metadata into the 
                    <filename><replaceable>PROFILE</replaceable>/data/teiid</filename> directory
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>query-timeout</emphasis>
                    </para>
                    <para>
                    Sets the default query timeout in milliseconds for queries executed against this VDB.  0 indicates that the server default query timeout should be used.  Defaults to 0.  Will have no effect if the server default query timeout is set to a lesser value.  Note that clients can still set their own timeouts that will be managed on the client side.
                    </para>
                </listitem>
            </itemizedlist>
            
        </section>
        
        <section>
            <title>Model Element</title>
            <itemizedlist>
                <title>Attributes</title>
                <listitem>
                    <para>
                    <emphasis>name</emphasis>
                    </para>
                    <para>
                    The name of the model is used as a 
                    top level schema name for all of the metadata imported from the 
                    connector.  The name should be unique among all Models in the VDB and 
                    should not contain the '.' character.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>version</emphasis>
                    </para>
                    <para>
                    The version of the VDB (should be an 
                    positive integer).  This determines the deployed directory location 
                    (see Name), and provides an explicit versioning mechanism to the VDB 
                    name.
                    </para>
                </listitem>
            </itemizedlist>
            
            <itemizedlist>
                <title>Source Element</title>
                <para>A source is a named binding of a translator and connection source to a model.</para>
                <listitem>
                    <para>
                    <emphasis>name</emphasis>
                    </para>
                    <para>
                    The name of the source to use for this 
                    model. This can be any name you like, but will typically be the same 
                    as the model name.  Having a name different than the model name is 
                    only useful in multi-source scenarios.	In multi-source, the source names under a given model must
                    be unique.  If you have the same source bound to multiple models it may have the same name
                    for each.  An exception will be raised if the same source name is used for different sources.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>translator-name</emphasis>
                    </para>
                    <para>
                    The name or type of the Teiid Translator to use.  Possible values include 
                    the built-in types (ws, file, ldap, oracle, sqlserver, db2, derby, etc.) 
                    and translators defined in the translators section. 
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>connection-jndi-name</emphasis>
                    </para>
                    <para>
                    The JNDI name of this source's connection factory. There should be a 
                    corresponding "-ds.xml" file that defines the connection factory in 
                    the JBoss AS.  Check out the  deploying VDB dependencies section for 
                    info. You also need to deploy these connection factories before you 
                    can deploy the VDB.	
                    </para>
                </listitem>
            </itemizedlist>
            
            <itemizedlist>
                <title>Property Elements</title>
                <listitem>
                    <para>
                    <emphasis>importer.&lt;propertyname&gt;</emphasis>
                    </para>
                    <para>
                    Property to be used by the connector importer for the model for purposes 
                    importing metadata.  See possible property name/values in the 
                    Translator specific section. Note that using these properties you 
                    can narrow or widen the data elements available for integration.
                    </para>
                </listitem>
            </itemizedlist>
            
        </section>
        <section>
            <title>Translator Element</title>
            <itemizedlist>
                <title>Attributes</title>
                <listitem>
                    <para>
                    <emphasis>name</emphasis>
                    </para>
                    <para>
                    The name of the the Translator.  Referenced by the source element.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>type</emphasis>
                    </para>
                    <para>
                    The base type of the Translator.  Can be one of the built-in types (ws, 
                    file, ldap, oracle, sqlserver, db2, derby, etc.).
                    </para>
                </listitem>
            </itemizedlist>
            
            <itemizedlist>
                <title>Property Elements</title>
                <listitem>
                    <para>
                    Set a value that overrides a translator default property.  See 
                    possible property name/values in the Translator specific section.
                    </para>
                </listitem>
            </itemizedlist>        
        </section>
	</section>

    <section id="dynamic_vdbs">
        <title>Dynamic VDBs</title>
        <para>
        Teiid integration is available via a "Dynamic VDB" without the need for Teiid Designer 
        tooling.  While this mode of operation does not yet allow for the creation of view 
        layers, the underlying sources can still be queried as if they are a single source.  See 
        the kit's "teiid-example/dynamicvdb-*" for working examples.
        </para>

        <para>
        To build a dynamic VDB, you'll need to create a 
        <filename><replaceable>SOME-NAME</replaceable>-vdb.xml</filename> file.  The XML file captures 
        information about the VDB, the sources it integrate, and preferences for importing metadata.
        </para>
        
        <note>
            <para>
            VDB name pattern must adhere to "-vdb.xml"  for the Teiid VDB deployer to 
            recognize this file as a dynamic VDB.
            </para>
        </note>
        
        <para>
        my-vdb.xml: (The vdb-deployer.xml schema for this file is available in the schema 
        folder under the docs with the Teiid distribution.)
        </para>
        
    </section>
    <section>
        <title>Multi-Source Models and VDB</title>
        <para>
        Multi-source models can be used to quickly access data in multiple sources with homogeneous metadata.
        When you have multiple instances of data that are using identical schema 
        (horizontal sharding), Teiid can help you aggregate data across all the 
        instances, using "multi-source" models. In this scenario, instead of 
        creating/importing a model for every data source, user needs to define one 
        source model that represents the schema and configure multiple data "sources" 
        underneath it. During runtime, when a query issued against this model, the query 
        engine analyzes the information and gathers the required data from all the 
        sources configured and aggregates the results and provides in a single result set.
        Since all sources utilize the same physical metadata, this feature is most appropriate for accessing the same source type with multiple instances.
        </para>
	
        <para>
        To mark a model as multi-source, the user needs to supply property called 
        <property>supports-multi-source-bindings</property>, in the "vdb.xml" file.  
        Also, the user needs to define multiple sources. Here is code example showing dynamic vdb with single model with 
        multiple sources defined.
        </para>
        
        <programlisting role="XML" language="XML"><![CDATA[<vdb name="vdbname" version="1">
    <model visible="true" type="PHYSICAL" name="Customers" path="/Test/Customers.xmi">
        <property name="supports-multi-source-bindings" value="true"/>
        <source name="chicago" 
            translator-name="oracle" connection-jndi-name="chicago-customers"/>
        <source name="newyork" 
            translator-name="oracle" connection-jndi-name="newyork-customers"/>
        <source name="la" 
            translator-name="oracle" connection-jndi-name="la-customers"/>
    </model>
</vdb>]]></programlisting>

        <para>
        In the above example, the VDB defined has single model called <literal>Customers</literal>, 
        that has multiple sources (<literal>chicago</literal>, <literal>newyork</literal>, 
        and <literal>la</literal>) that define different instances of data. Every 
        time a model is marked as "multi-source", the 
        runtime engine adds a additional column called "SOURCE_NAME" to every table in 
        that model. This column maps to the source's name from the XML. In 
        the above XML code that would be <literal>chicago</literal>, <literal>la</literal>, 
        <literal>newyork</literal>. This allows queries like the following:
        </para>
        
        <programlisting language="SQL"><![CDATA[select * from table where SOURCE_NAME = 'newyork'
update table column=value  where SOURCE_NAME='chicago'
delete from table where column = x and SOURCE_NAME='la'
insert into table (column, SOURCE_NAME) VALUES ('value', 'newyork')]]></programlisting>
      
        <para>
        Note that when user do not supply the "SOURCE_NAME" in the criteria, the command applies 
        to all the sources. If SOURCE_NAME supplied, the query is executed only aginst the source specified. 
        Another useful feature along with this feature is 
        "partial results" to skip unavailable sources if they are down.
        </para>

		<para>More complex partitioning scenarios, such as heterogeneous sources or list partitioning will require the use of a <xref linkend="partitioned_union"/>.</para>
        
        <note>
            <para>
            Currently the tooling support for managing the multi-source feature is 
            limited, so if you need to use this feature build the VDB as usual in 
            the Teiid Designer and then edit the "vdb.xml" file in the VDB archive 
            using a Text editor to add the additional sources as defined above. 
            You must deploy a separate data source for each source defined in the xml file.
            </para>
        </note>

        <note>
            <para>
            If you would like to use "SOURCE_NAME" in your transformations to control which sources are accessed or updated, 
            you would manually need to add this extra column on your view table in
            the Designer. This column will not be automatically added on the source table, when you import 
            the medata from source.
            
			It is important to understand that a column or IN procedure parameter named source_name in multi-source mode will always be treated as the explicit form of the multi-source source_name column
	 		and will no longer be treated as an actual physical column or procedure parameter.</para>
        </note>       
        
        <section>
	    	<title>Multi-source SELECTs</title>
	    	<para>A multi-source SELECT may use the source_name column anywhere a column reference is allowed.  As a final stage of planning, a source query will be generated against each source and each instance of the source_name column replaced by the appropriate value.
	    	If the resulting query still needs executed, it is sent to the source.  If the WHERE clause evaluates to always false, then the query is pruned from the result.  All results are then unioned together and returned as the full result.
	    	</para>
	    </section>
	    <section>
	    	<title>Multi-source INSERTs</title>
	    	<para>A multi-source INSERT may use the source_name column as an insert target column to specify which source should be targeted by the INSERT.  Only a INSERT using the VALUES clause is supported and the source_name column value must be a literal.  
	    	If the source_name column is not part of the INSERT column, then the INSERT will be issued against every source.  The sum of the update counts will be returned as the resultant update count.</para>
	    </section>
	    <section>
	    	<title>Multi-source UPDATEs</title>
	    	<para>A multi-source delete functions just like SELECT, however it is not possible to use the source_name column as a target column in the change set.   Any other usage of the source_name column will be the appropriate value for each source.  
	    	If the WHERE clause evaluates to always false, then no update will be issued to the source.  The sum of the update counts will be returned as the resultant update count.</para>	
	    </section>
	    <section>
	    	<title>Multi-source DELETEs</title>
	    	<para>A multi-source delete functions just like SELECT.  Any usage of the source_name column will be the appropriate value for each source.  
	    	If the WHERE clause evaluates to always false, then no delete will be issued to the source.  The sum of the update counts will be returned as the resultant update count.</para>
	    </section>
	    <section>
	    	<title>Multi-source Stored Procedures</title>
	    	<para>A physical stored procedures requires the manual addition of a string source_name parameter to allow for specifying which source the procedure is executed on.
	    	If the source_name parameter is not added or if named parameters are used and the source_name parameter is allowed to default to a null value, then the procedure will be executed on
	    	each source and the results unioned together.</para>
	    	<para>It is not possible to execute procedures that required to return IN/OUT, OUT, or RETURN parameters values on more than 1 source at a time.</para>
	    </section>
	    <section>
	    	<title>Additional Concerns</title>
	    	<para>When running under a transaction of in a mode that detects the need for a transaction and multiple updates are performed, an attempt will be made to enlist each source in the same XA transaction.</para>
	    </section> 
    </section>

</chapter>