Bind File using Oracle

[Migrated content. Thread originally posted on 29 August 2011]

Hi All,

I know that using IBM DB2 we can create bind file during compiling program. I want to know that whether we can do same for oracle. If yes, Can you point me to documentation.

I am accessing Oracle via ODBC connection.

Thanks
Cheeta

A bind file is specific to IBM DB2.
ODBC and Oracle in general provide no support for such a thing.

What exact functionality of a bind file are you looking for?

[Migrated content. Thread originally posted on 29 August 2011]

Hi All,

I know that using IBM DB2 we can create bind file during compiling program. I want to know that whether we can do same for oracle. If yes, Can you point me to documentation.

I am accessing Oracle via ODBC connection.

Thanks
Cheeta

Hi Chris,

Thanks for replying i am looking for the ways where i can improve performance of COBOL code accessing Oracle via ODBC. In DB2 in Bind contain the access path , do we have similar thing in oracle.

Currently i am using dbman=odbc and Targetdb=OCI directive.

Or it will be better to ask what are the other ways where i can improve Performance.


Cheeta

[Migrated content. Thread originally posted on 29 August 2011]

Hi All,

I know that using IBM DB2 we can create bind file during compiling program. I want to know that whether we can do same for oracle. If yes, Can you point me to documentation.

I am accessing Oracle via ODBC connection.

Thanks
Cheeta

I got the following information from development:

You might want to try using the straight ODBC driver for Oracle instead of going through OCI.
You could then use the BEHAVIOR directive to tweek performance.

OCI was really a stopgap at a time when 64 bit ODBC drivers weren’t available.

The only way to replicate DB2 binding with Oracle (ie access plan prepared ahead of time) is to use stored procedures, though this doesn’t always provide as much of an advantage as you might expect since database vendors have worked hard to improve performance of dynamic SQL.

Other techniques to improve performance generally, aside from BEHAVIOR and its sub-directives, include using array fetches and array inserts, and the STMTCACHE directive.

For Oracle, it may be worth comparing OpenESQL against Pro*COBOL. OpenESQL provides uniform behavior across industry standard APIs, so is a good choice for applications that target multiple database vendors or where there is a need to use ESQL where the database vendor doesn’t support ESQL (ADO.NET, for example). Micro Focus also provides tools to assist ESQL development based on OpenESQL such as the OpenESQL Assistant and HCOSS.

For applications wanting to maximize performance for a single database vendor, then it’s worth looking at the vendor’s ESQL support as it may be able to exploit optimizations not available through the industry standard APIs. Where an industry standard API has to be used, then it may be worth investigating 3rd party drivers/providers. One way to track down 3rd party support is to look at the lists for ODBC, JDBC and ADO.NET providers at http://sqlsummit.com/DataAcce.htm

[Migrated content. Thread originally posted on 29 August 2011]

Hi All,

I know that using IBM DB2 we can create bind file during compiling program. I want to know that whether we can do same for oracle. If yes, Can you point me to documentation.

I am accessing Oracle via ODBC connection.

Thanks
Cheeta

Thanks Chris !!

It Helps a lot. Let me try what you have said and will come back for more!!

[Migrated content. Thread originally posted on 29 August 2011]

Hi All,

I know that using IBM DB2 we can create bind file during compiling program. I want to know that whether we can do same for oracle. If yes, Can you point me to documentation.

I am accessing Oracle via ODBC connection.

Thanks
Cheeta

Hi Chris..
Thanks for help ,
My program performance increased drastically when i Moved from ODBC to OCI and Changed the STMTCACHE=20000
I have following question in mind..
a) does OCI is better than ODBC(32) bit or they are equal capable ?
b) are there any other compiler directive for esql with which I can improve the performance ( other STMTCACHE and JIT) ?
c) what would be the optimal value of STMTCACHE can increased to 2000 to 2000000 , will have it any effect?

[Migrated content. Thread originally posted on 29 August 2011]

Hi All,

I know that using IBM DB2 we can create bind file during compiling program. I want to know that whether we can do same for oracle. If yes, Can you point me to documentation.

I am accessing Oracle via ODBC connection.

Thanks
Cheeta

Is your following statement correct?
"performance increased drastically when I moved from ODBC to OCI"

or did you move from OCI to ODBC?

The BEHAVIOR directive can help with performance.

Please see the following articles in our knowledgebase for some pointers:
Even though they may reference SQL Server as database the information is mostly relavant to all ODBC sources.

Optimizing SQL performance in mainframe migrations

Understanding how Micro Focus OpenESQL uses Microsoft SQL Server

Also for STMTCACHE I received the following explanation from development:

The setting of STMTCACHE refers to the number of prepared statements that the runtime will keep alive. The cache is managed on a least recently used basis. A statement can be recycled if it is not an open cursor.

At one time we had problems with Oracle running out of resources so we introduced STMTCACHE to restrict the amount of server resources a client app could tie up. OpenESQL always uses prepare execute rather than direct execution so that any statement executed multiple times will execute faster on the 2nd and subsequent executions.

Only statements that cause server side execution are cached (ie open cursor, insert, delete, etc are cached since they cause execution of a SQL statement at the server, but fetch, commit, etc are not since they map to API calls or local action only).

We've seen STMTCACHE yielding benefits with settings up to 300 in large batch jobs. The optimum setting depends on the application, so it’s something you need to experiment with. Increased values may speed up execution of an app but will tie up server memory so may limit overall throughput.

[Migrated content. Thread originally posted on 29 August 2011]

Hi All,

I know that using IBM DB2 we can create bind file during compiling program. I want to know that whether we can do same for oracle. If yes, Can you point me to documentation.

I am accessing Oracle via ODBC connection.

Thanks
Cheeta

Thanks Chris, That really helped.

Yes performance is enhanced when i moved from ODBC to OCI

Is that mean i was doing something wrong during ODBC test.

ODBC V/S OCI which should have fetched better results ??

Thanks
Cheeta

[Migrated content. Thread originally posted on 29 August 2011]

Hi All,

I know that using IBM DB2 we can create bind file during compiling program. I want to know that whether we can do same for oracle. If yes, Can you point me to documentation.

I am accessing Oracle via ODBC connection.

Thanks
Cheeta

Sorry from your previous statements I assumed that you were already using OCI and were looking at testing ODBC:

"Currently i am using dbman=odbc and Targetdb=OCI directive"

Performance with ODBC is largely dictated by the actual ODBC driver that you are using and can vary widely between different vendors.