VDB Initializing Envrionmental Variable

[akero01]

I'm trying to initialize an environmental variable in a vdb so that a view(s) has access to it when it is launched.  The variable's value is defined by making an sql select.  The idea is to launch this select before launching a view. I've seen things like EXECUTE IMMEDIATELY, etc. but being somewhat new to sql land, wasn't sure how or if I can do this in an 8.1 vdb.  Any help would be appreciated.

[rareddy]

Can you create temp table and use the variable form it?

[akero01]

Not quite sure what you mean.  Do you have a ddl-type sample?

[rareddy]

See https://docs.jboss.org/author/display/TEIID/Temp+Tables

[akero01]

Thanks - I'll see what we can do with this.

[akero01]

Tried putting CREATE LOCAL TEMP TABLE in the virtural ddl area where I do the CREATE VIEW but doesn't seem to like it - some other place to put it?

[rareddy]

You can use them in the procedures or execute them directly from JDBC Connection. Can you explain clearly what your usecase is, then may be we can give a better way to do this.

Ramesh..

[akero01]

Here's a sample of a view I want to create and what I need:

***********************

<model name="viewTest" type="VIRTUAL" visible="true">

    <description> </description>

    <property name="prop1" value="prop1Value"/>

    <metadata type="DDL"><![CDATA[

    CREATE VIEW SRC_view1

    (

      LAST_UPDATED_DATE timestamp

     )

     AS select

       PRTASK.LAST_UPDATED_DATE AS LAST_UPDATED_DATE

     FROM

        PRTASK

    WHERE

        PRTASK.LAST_UPDATED_DATE >= '${DM_LAST_LOAD_DATE}'

    ]]></metadata>

  </model>

*****************************

The variable DM_LAST_LOAD_DATE's value needs to be loaded in from a seperate sql select before the view is loaded (hopefully only once and not for each record).  Hope this makes sense on what I need to do.

[rareddy]

Bob,

Lets explore some possibilities

1) What is the need to set the ${DM_LAST_LOAD_DATE} in the transformation? If you are concerned that user may not supply that criteria you can place an "ACCESS PATTERN" such that user query must supply a criteria.

2) Have a physical table in your source, and then join the your view with that table.

3) Teiid supports a function called "ENV", which can read a system property that is set. This function by default turned off, but you can change the configuration to enable it. The draw back with this solution is you can only work with one DM_LAST_LOAD_DATE for the duration of server life.

my preference is 1, otherwise 2. There may be other solutions, these are the ones I can think of.

Ramesh..

[shawkins]

Bob,

If you use a subquery it will be executed 1 per view access, and not for each record (unless it is correlated and even then we have a small cache so that we don't reexecute for common correlation values).  Using a non-pushdown subquery subquery will for planning purposes be treated as a literal and not inhibit pushdown. 

In addition to what Ramesh mentioned, you can also use the lookup function - https://docs.jboss.org/author/display/TEIID/Code+Table+Caching which will cache the results of a single invokation. 

If you want to truly statically set the value, then that would probably require a custom MetadataRepository to preprocess your SQL before passing it off to Teiid.  By default the DDL repository simply parses the supplied DDL.  You can have a look at https://docs.jboss.org/author/display/TEIID/Custom+Metadata+Repository to see if a custom repository may be of interest.  However we have no setter currently for the MetadataFactory.rawMetadata, so after your preprocessing you'd have to manually call into the parser rather than just chaining to the DDL MetadataRepository.  I'll address that though.

Steve

[akero01]

Thanks - I will check your suggestions out.

[akero01]

Appears the lookup function will work for what we need - thanks for both your help.

[shawkins]

Glad to hear that it's a good workaround.  Also, https://issues.jboss.org/browse/TEIID-2231 covers allowing the MetadataFactory to better facilitate DDL preprocessing.