2 Replies Latest reply: Oct 10, 2011 1:23 PM by Ramesh Reddy RSS

Designer Changes that Impact Teiid Runtime

Van Halbert Apprentice

Starting a discussion to gather information related to the modeling activity and how it impacts Teiid runtime.    At some point, this will transcribed into a document for Designer.

 

What does impact runtime:

 

  • Pk/fk relationships are meaningful to optimization.  Starting in Teiid 7.3 we use the key relationship information to determine key preservation, which is used in update procedure logic, subquery optimization, etc
  • Virtual pk/fk relationships are useful for subquery optimization and inherently updatable views.  Other optimization paths will typically not consider virtual keys because the views layers can be removed.
  • Indexes/unique constraints are also used during optimization in costing dependent joins and cardinality determination
  • Cross source integrity could be enforced through Teiid by using updated procedures to check for necessary preconditions before an insert/update/delete is allowed to happen.  However unless all writers to both sources were actively maintaining integrity then additional compensation would be needed.
  • From a data-write enforcement and query planning perspective, teiid is looking for the appropriate equi-join predicates to be present.
  • Defining data types
    • In general only the run-time type is meaningful in terms of how data is returned/transformed.  Run-time types are the Teiid built-in types, such as boolean, byte, integer, etc.
    • Design-time types are used in two limited capacities at run-time.  They can be reported through the system schema and for output conversion when querying XML document models.  Designer allows for a broader set of types, known as design-time types, to be selected.  Design-time types include the built-in types, standard xsd types - like g:dateTime, and even types defined by imported xsds.  Any design-time type will be mapped to a sufficiently expressive run-time type.  Any design-time type facet information not reflected by the run-type, such a restriction to non-negative, will not have any run-time enforcement.
    • Nullability information is meaningful when determining allowable values for virtual procedure input parameters, default insert values, and costing.

 

 

 

 

What does not impact runtime, their only use is for metadata information:

 

  • PF/FK's directly between sources are not currently supported for optimization.
  • There is no runtime checking of the pk/fk data constraints.  For keys within the same source it's considered the source systems responsibility to maintain integrity.
  • Teiid does not currently look at the cardinalities (1-1, 1-many, many-1) of the relationship, just whether the pk to fk has been satisfied
  • Teiid does not enforce constraints (nullable, unique, pk/fk, etc.) on source tables/procedures, but rather rely on the source system.
  • 1. Re: Designer Changes that Impact Teiid Runtime
    Barry LaFond Expert

    There are 2 primary mechanisms for impacting Teiid Runtime and both involve the deployable VDB artifact. Indexed metadata and additional vdb.xml properties (for data role/permissions and translator)

     

    The following is a list of all properties that are added to the metadata index files for the model objects indexed by the Designer. These are defined in the RuntimeAdapter class. We still need to determine which properties in this list are actually consumed by the Teiid Server.

     

    TABLE

        String fullName

        String nameInSource

        int cardinality

        int tableType

        boolean isVirtual

        boolean isSystem

        boolean isMaterialized

        boolean supportsUpdate

        String primaryKeyID

        Collection foreignKeyIDs

        Collection indexIDs

        Collection uniqueKeyIDs

        Collection accessPatternIDs

        String materializedTableID

     

     

    COLUMN

        String fullName

        String nameInSource

        boolean isSelectable

        boolean isUpdatable

        int nullType

        boolean isAutoIncrementable

        boolean isCaseSensitive

        boolean isSigned

        boolean isCurrency

        boolean isFixedLength

        boolean isTranformationInputParameter

        int searchType

        int length

        int scale

        int precision

        int charOctetLength

        int radix

        int distinctValues

        int nullValues

        String minValue

        String maxValue

        String format

        String runtimeType

        String nativeType

        String datatypeObjectID

        String defaultValue

        int position

     

    PRIMARY KEY

        String fullName

        String nameInSource

        List columnIDs

        List foreignKeyIDs

     

    UNIQUE KEY

        String fullName

        String nameInSource

        List columnIDs

        List foreignKeyIDs

     

    INDEX

        String fullName

        String nameInSource

        List columnIDs

     

    ACCESS PATTERN

        String fullName

        String nameInSource

        List columnIDs

     

    RESULT SET

        String fullName

        String nameInSource

        List columnIDs

     

    FOREIGN KEY

        String fullName

        String nameInSource

        List columnIDs

        String uniqueKeyID

     

    DATATYPE

        String fullName

        String nameInSource

        int length

        int precisionLength

        int scale

        int radix

        boolean isSigned

        boolean isAutoIncrement

        boolean isCaseSensitive

        short type

        short searchType

        short nullType

        String javaClassName

        String runtimeTypeName

        String datatypeID

        String baseTypeID

        String primitiveTypeID

        short varietyType

        List varietyProps

     

    PROCEDURE

        String fullName

        String nameInSource

        boolean isFunction

        boolean isVirtual

        List parametersIDs

        String resultSetID

        int updateCount

     

    PROCEDURE PARAMETER

        String objectID

        String fullName

        String nameInSource

        String defaultValue

        String runtimeType

        String datatypeObjectID

        int length

        int radix

        int scale

        short nullType

        int precision

        int position

        String type

        boolean optional

     

    SQL TRANSFORMATION

        String virtualGroupName

        String transformedObjectID

        String transformationObjectID

        String transformationSql

        List bindings

        List schemaPaths

     

    VDB

        String fullName

        String nameInSource

        String version

        String identifier

        String description

        String producerName

        String producerVersion

        String provider

        String timeLastChanged

        String timeLastProduced

        List modelIDs

     

    ANNOTATION

        String fullName

        String nameInSource

        String description