-
1. Re: Caching Virtual Stored Procedure Results
rareddy Nov 6, 2014 9:39 AM (in response to ranjith123)Ranjith,
May be add "resultSetCacheMode=true" on your JDBC URL. See Driver Connection - Teiid 8.9 (draft) - Project Documentation Editor
Ramesh..
-
2. Re: Caching Virtual Stored Procedure Results
shawkins Nov 6, 2014 9:59 AM (in response to rareddy)resultSetCacheMode is not necessary for procedure caching. It is for user level statements/result sets. If you enable debug logging you should see in the log statements related to cache usage. What are the parameter types to your procedure?
-
3. Re: Caching Virtual Stored Procedure Results
ranjith123 Nov 6, 2014 10:20 AM (in response to shawkins)There are two arguments and they are of String type
Thank you
RM
-
4. Re: Caching Virtual Stored Procedure Results
shawkins Nov 6, 2014 1:44 PM (in response to ranjith123)Yes, that should be eligible for caching. Have a look at the logs at a more detailed level.
-
5. Re: Caching Virtual Stored Procedure Results
ranjith123 Nov 6, 2014 9:11 PM (in response to shawkins)Hi Steven,
Here is what i see in the log
20:16:11,077 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #1) START USER COMMAND: startTime=2014-11-06 20:16:11.077 requestID=aDGUIxViDOsm.0 txID=null sessionID=aDGUIxViDOsm applicationName=JDBC principal=xxxxxx vdbName=ENTS_LDAPAuthN_v1_0_VDB vdbVersion=1 sql=call LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER(?);
20:16:11,077 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread aDGUIxViDOsm.0 with state NEW
20:16:11,077 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) aDGUIxViDOsm.0 Non-cachable command.
20:16:11,077 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) aDGUIxViDOsm.0 executing prepared call LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER(?);
20:16:11,702 DEBUG [org.teiid.PLANNER] (Worker0_QueryProcessorQueue0) [QueryAnnotation<Cached Procedure, MEDIUM,Procedure caching will not be used for LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER since the result set cache is disabled or the results/parameters cannot be cached.,null>]
20:16:11,702 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) ProcessTree for aDGUIxViDOsm.0 ProjectNode(1) output=[LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER."Message Samples"] [LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER."Message Samples"]
PlanExecutionNode(2) output=[LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER."Message Samples"]
Not sure what this means. The SP is working fine and i giving the result.
For your info my data source is LDAP and i am trying to get the User/Pwd for verification. And i have two parameter as input - UserId and Password
Thank you
RM
-
6. Re: Caching Virtual Stored Procedure Results
shawkins Nov 7, 2014 3:25 PM (in response to ranjith123)> 20:16:11,702 DEBUG [org.teiid.PLANNER] (Worker0_QueryProcessorQueue0) [QueryAnnotation<Cached Procedure, MEDIUM,Procedure caching will not be used for LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER since the result set cache is disabled or the results/parameters cannot be cached.,null>]
That confirms we're not caching, but unfortunately doesn't clarify for which reason. There possibilities are:
- the result set cache is disabled. By default it is enabled. There would need to be an enabled="false" setting on the resultset-cache config for it to be disabled.
- the parameters contain a lob type, which should not be the case since you indicated they are strings
- the procedure is performing an update. This is determined by looking at the update count metadata property. If it's greater than 0, then we assume an update is being performed and thus should not cache.
We'll clarify the logging so that it's clear which is occurring.
Also anything that is a query annotation information is attached to the plan, so they are available from SHOW PLAN without putting the server into mode into debug.
> 20:16:11,077 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) aDGUIxViDOsm.0 Non-cachable command.
That line Is referring to the user query level result set cache. If the result set cache mode is not set, then that is expected.
-
7. Re: Caching Virtual Stored Procedure Results
ranjith123 Nov 9, 2014 11:49 PM (in response to shawkins)Hi Steven,
Where do i set of check if result set cache is enabled or not It is in JBoss Admin Console >> Profile > Teiid > Query Engine >> Result Cache Tab ??
Please let me know where to check
Also pasting my SP
/*+ cache */
CREATE VIRTUAL PROCEDURE
BEGIN
DECLARE string VARIABLES.IN_userPassword = xpathvalue(LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER.USER_PROFILE_InputMsg, '/*:USER_PROFILE_Input/*:userPassword');
DECLARE string VARIABLES.IN_UID;
VARIABLES.IN_UID = xPathValue(LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER.USER_PROFILE_InputMsg, '/*:USER_PROFILE_Input/*:uid');
SELECT LDAP_USERView_OUT_View.USER_PROFILE_OutputView.USER_PROFILE_Output.USER_PROFILE_Output_Instance.uid FROM LDAP_USERView_OUT_View.USER_PROFILE_OutputView WHERE (LDAP_USERView_OUT_View.USER_PROFILE_OutputView.USER_PROFILE_Output.USER_PROFILE_Output_Instance.uid = VARIABLES.IN_UID) AND (LDAP_USERView_OUT_View.USER_PROFILE_OutputView.USER_PROFILE_Output.USER_PROFILE_Output_Instance.userPassword = VARIABLES.IN_userPassword);
END
I am calling this Procedure a web service call. Response is a XML with UserID. Does web service invocation has any bearing to caching ???
Thank you
RM
-
8. Re: Caching Virtual Stored Procedure Results
shawkins Nov 10, 2014 8:27 AM (in response to ranjith123)> Where do i set of check if result set cache is enabled or not It is in JBoss Admin Console >> Profile > Teiid > Query Engine >> Result Cache Tab ??
> Please let me know where to check
Unless you have disabled it, it should be enabled by default. I'm not sure if you can disable it from the admin console.
> I am calling this Procedure a web service call. Response is a XML with UserID. Does web service invocation has any bearing to caching ???
No it should not.
If you are using designer, can you look at the metadata properties for this procedure. What is the value for the update count?
-
9. Re: Caching Virtual Stored Procedure Results
rareddy Nov 10, 2014 9:27 AM (in response to shawkins)>> Where do i set of check if result set cache is enabled or not It is in JBoss Admin Console >> Profile > Teiid > Query Engine >> Result Cache Tab ??
>> Please let me know where to check
Yes, there should be "edit" box to change it.
-
10. Re: Re: Caching Virtual Stored Procedure Results
ranjith123 Nov 11, 2014 12:21 AM (in response to rareddy)Hi All,
Steven,
The Update Count meta data is AUTO. I changed this to ZERO and then re-deployed VDB and WS. But no luck on caching. The Cache Count hasn't increased.
Ramesh,
The ResultCache is by default set to true. So the flag is set, so not sure why it is not caching.
I am attaching the standalone-ha.xml which is the configuration used to run the JBoss Server. Not sure looking into this would help.
Also do i need to send the JBoss Studio projects if you need to take a look. ???
Thank you
RM
-
standalone-ha.xml 35.3 KB
-
-
11. Re: Re: Caching Virtual Stored Procedure Results
shawkins Nov 11, 2014 9:04 AM (in response to ranjith123)Presumably the cache is enabled.
Can you export the DDL for the procedure? That would be the AdminApi getSchema, or can you post a condensed version of the vdb?
-
12. Re: Caching Virtual Stored Procedure Results
ranjith123 Nov 11, 2014 2:27 PM (in response to shawkins)Hi Steven
Can do the export of VDB from Teiid Designer ???. I am not sure who to run the getSchema Admin API. Can this be done from Admin console ???
thank you
RM
-
13. Re: Caching Virtual Stored Procedure Results
rareddy Nov 11, 2014 2:50 PM (in response to ranjith123)From web-console application, you can click on the DDL button on the model to get the schema.
-
14. Re: Re: Caching Virtual Stored Procedure Results
ranjith123 Nov 18, 2014 1:59 PM (in response to rareddy)Hi Team,
Attaching the DDL for the Service.
Thank you
RM
-
CacheDVM_LDAPAuthNUserDDL.txt.zip 991 bytes
-