PrepS and Exec statements

Database
Enthusiast

PrepS and Exec statements

Hi,

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 :)

 

Thanks,
ALOK.

9 REPLIES
Apprentice

Re: PrepS and Exec statements

Hi,

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.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: PrepS and Exec statements

Same issue seen for JDBC, looking for any option available disable prepare ?

Irshad Ahmed

Enthusiast

Re: PrepS and Exec statements

Thanks Dave.

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 ?

 

 

Enthusiast

Re: PrepS and Exec statements

Hey Irshad,

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.

Apprentice

Re: PrepS and Exec statements

Hi,

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?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: PrepS and Exec statements

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.

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#URL_PREP...

 

IMPORTANT NOTE:

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.

 

Enthusiast

Re: PrepS and Exec statements

Hi Dave,

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.

Enthusiast

Re: PrepS and Exec 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.

Apprentice

Re: PrepS and Exec statements

Hi,

It may be that you want to talk to TD support (open an incident).

 

However can I also put forward the following:

  1. PrepS is essentially parsing the request. There is an article (written by Carrie Ballinger - sorry you'll have to search for it) which talks about setting up a workload specifically for parsing. Yes, the separate request is still run but the PrepS runs very quickly - reducing time.
  2. If the request is parameterised (which in my experience many BOBJ reports will be) then the PrepS request will get cached immediately. This means that when the Exec comes in as an identical request there is no/minimal parsing. Essentially you only take the hit on parsing time once - either in the PrepS or Exec.

Maybe with this approach you can avoid the elapsed time overhead?

 

Just a thought.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com