I am not sure if I am posting on right forum.
There is BO universe and a Teradata database on which the query gets executed.
Everytime the query executes, query hits the database couple of time, once in PrepS and the one in Exec (RequestMode).
I want to get rid of "PrepS" request mode. Any idea how it could be achieved? Let me know if I am clear with my question.
Looking for quick answer :)
I'm not sure if you can.
I think this might be the ODBC driver (assuming that is what is being used by BOBJ as an api) issuing the PrepS to get data types etc.
Try looking in the ODBC dsn properties. I've just looked on my Windows system/ODBC administrator and the Teradata ODBC driver has a 'Disable Prepare' option. That might be worth investigating.
That option does not work :(
We have BO universe from where the connections are made to Teradata database using the connection string in ODBC.ini file.
End users (through application) enters the parameters based on their requirements to get the report -> it connects to the BO universe and triggers a query in Teradata thru the connection string and returns the results.
Now, when it triggers the query, there are couple of queries which are shown in the dbql tables in request mode which are PrepS and Exec. We need to disable the PerpS.
Now, to get rid of this, we need to make some changes in the ODBC.ini file I suppose.
Nothing is working.
Is there another way we can achive it ?
We faced similar issue with JDBC and we were able to solve it.
You need to change the TMODE=ANSI to TMODE=Teradata in the JDBC connection string and restart the BO server to make the change effective.
Example JDBC Entry: dashboard.teradata.url=jdbc:teradata://xxxxx.xxxxx.global/TMODE=TERADATA,CHARSET=UTF8
Try it. hope that would help.
Sorry but I'm not clear. Have you tried changes to the odbc.ini file?
If you have and you're still seeing both queries in DBQL then it may be worth looking on BOBJ forums and/or talking to SAp themselves.
Also (I meant to ask you earlier) why do you want to get rid of the PrepS request? Is this causing you problems?
Regarding this statement: "We faced similar issue with JDBC and we were able to solve it. You need to change the TMODE=ANSI to TMODE=Teradata in the JDBC connection string and restart the BO server to make the change effective."
The Teradata JDBC Driver's TMODE connection parameter does not affect whether a Prepare operation is performed when a PreparedStatement or CallableStatement is created. The TMODE connection parameter affects the behavior of several features, but not this one.
Instead, the Teradata JDBC Driver's PREP_SUPPORT=OFF connection parameter will turn off the Prepare operation.
A prepare operation is required for the proper functioning of many Teradata JDBC Driver features. Turning off Prepare operations is NOT recommended for production use, and is only intended for troubleshooting performance issues in a test environment.
My guess is that the original poster wants to turn off Prepare operations because they seem to be slow. Our recommendation is to open an incident with Teradata customer service and troubleshoot the root cause of the performance problem, rather than turning off Prepare operations.
We are observing the PrepS statements are taking time which is causing the delay in the Reports.
We can save and get much better performance if we are able to turn off the PrepS statements.
Thansk Tom for clearing the thoughts on JDBC settings.
Yes, we are facing some lag in the reports as PrepS statements taking some time to cause the delay and thinking that we might achieve better performance by disabling the oprion for Prepare.
It may be that you want to talk to TD support (open an incident).
However can I also put forward the following:
Maybe with this approach you can avoid the elapsed time overhead?
Just a thought.