-
1. Re: VDB Initializing Envrionmental Variable
rareddy Sep 13, 2012 10:26 AM (in response to akero01)Can you create temp table and use the variable form it?
-
2. Re: VDB Initializing Envrionmental Variable
akero01 Sep 13, 2012 10:31 AM (in response to rareddy)Not quite sure what you mean. Do you have a ddl-type sample?
-
4. Re: VDB Initializing Envrionmental Variable
akero01 Sep 13, 2012 11:20 AM (in response to rareddy)Thanks - I'll see what we can do with this.
-
5. Re: VDB Initializing Envrionmental Variable
akero01 Sep 13, 2012 12:17 PM (in response to rareddy)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?
-
6. Re: VDB Initializing Envrionmental Variable
rareddy Sep 13, 2012 1:03 PM (in response to akero01)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..
-
7. Re: VDB Initializing Envrionmental Variable
akero01 Sep 13, 2012 1:23 PM (in response to rareddy)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.
-
8. Re: VDB Initializing Envrionmental Variable
rareddy Sep 13, 2012 10:18 PM (in response to akero01)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..
-
9. Re: VDB Initializing Envrionmental Variable
shawkins Sep 14, 2012 8:31 AM (in response to rareddy)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
-
10. Re: VDB Initializing Envrionmental Variable
akero01 Sep 16, 2012 3:25 PM (in response to shawkins)Thanks - I will check your suggestions out.
-
11. Re: VDB Initializing Envrionmental Variable
akero01 Sep 17, 2012 11:10 AM (in response to akero01)Appears the lookup function will work for what we need - thanks for both your help.
-
12. Re: VDB Initializing Envrionmental Variable
shawkins Sep 27, 2012 7:06 AM (in response to akero01)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.