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 Master

          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