Best kept secret on troubleshooting ODBC, JDBC, ADO.NET data access and performance

Best kept secret on troubleshooting ODBC, JDBC, ADO.NET data access and performance

July 03, 2012 0 Comments

DataDirect has developed the most robust set of diagnostic features in standards based drivers that rival any application out there.  This is because organizations choose DataDirect to meet demanding SLAs and run mission critical applications, and our Support/R&D teams have seen every kind of issue you can imagine.  One example is for an issue reported from a massive multi threaded application environment that could not be captured with standard tracing enabled since timings changed.  DataDirect R&D responded by building different levels of tracing to avoid impacting the application's timing.  This is one of many examples of diagnostic innovation across our product lines.

So how does this best kept secret help non-DataDirect shops?

You can leverage the advanced diagnostic capabilities of DataDirect Connect drivers with a 15 day trial to troubleshoot issues with standard's based ODBC, JDBC or ADO.NET applications.  Standard tracing in non-DataDirect drivers is no longer suitable for demanding environments built for bigdata, bulkdata, and clouds.

Do you care if we use your drivers just for troubleshooting purposes?

Nope. It's cool with our EULA and if anyone asks, tell them Jesse Davis suggested it. Everyone likes Jesse!

What are some cases to use enhanced DataDirect Diagnostics

  • Find optimal value for tunable connection properties such as connection pool sizes
  • Find out how much time is spent in the driver versus database
  • Log the database protocol communication over the wire
  • See SQL query being generated by ORM, development framework or other abstraction layer
  • Answer questions for DBAs or system administrators for infrastructure planning
  • See what data types are being used to bind parameter markers for efficient query plans
  • Check for objects not getting closed
  • Capture diagnostics without changing timing of application
  • Capture circular logging to avoid running out of disk space

Getting started:

1- Download a free 15 day trial of any or all ODBC, JDBC, ADO.NET drivers for diagnostic purposes. 2- Configure your data source or connection url to connect using our drivers. 3- Refer to the user's guide for details on how enable tracing.

ODBC Tracing Tracking calls with JDBC SPY ADO.NET troubleshooting

Below are example snippets of logs that can be generated to include additional information including timestamps.

ODBC trace captured with enhanced tracing library 2011-03-02 15:27:19.000300 ppid=0:pid=4668:e44: 123c:e44 ENTER SQLFetch HSTMT 0x000000000031F080

2011-03-02 15:27:20.000503 ppid=0:pid=4668:e44: 123c:e44 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 0x000000000031F080

2011-03-02 15:27:20.000519 ppid=0:pid=4668:e44: 123c:e44 ENTER SQLFetch HSTMT 0x000000000031F080

2011-03-02 15:27:20.000519 ppid=0:pid=4668:e44: 123c:e44 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 0x000000000031F080

2011-03-02 15:27:20.000519 ppid=0:pid=4668:e44: 123c:e44 ENTER SQLFetch HSTMT 0x000000000031F080

2011-03-02 15:27:20.000519 ppid=0:pid=4668:e44: 123c:e44 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 0x000000000031F080

 JDBC trace snippet captured with DataDirect Spy spy(2012/04/09 12:01:52.964) PreparedStatement[4].addBatch() spy(2012/04/09 12:01:52.964) OK

spy(2012/04/09 12:01:52.964) PreparedStatement[4].executeBatch() spy(2012/04/09 12:02:08.546) OK

Built in Database Protocol Logs (over the wire) Send: 32 bytes from 10.30.110.137:10362 to 10.30.12.72:5000 (TCP data) 0F 01 00 20-00 00 01 00-21 00 00 00-13 00 73 65 ··· ····!·····se ················ 6C 65 63 74-20 40 40 6E-63 68 61 72-73 69 7A 65 lect @@ncharsize %·Ž·· >Ž€/··¥:·

Read: 47 bytes from 10.30.12.72:5000 to 10.30.110.137:10362 (TCP data) 04 01 00 2F-00 00 00 00-61 00 00 00-12 00 01 00 ···/····a······· ········/······· 00 00 00 00-00 00 00 30-00 00 00 05-26 01 00 AE ·······0····&··® ················ 00 01 00 D1-01 01 FD 00-10 00 02 00-00 00 01 ···Ñ··ý········ ···J···········

Send: 34 bytes from 10.30.110.137:10362 to 10.30.12.72:5000 (TCP data) 0F 01 00 22-00 00 01 00-21 00 00 00-15 00 73 65 ···"····!·····se ················ 6C 65 63 74-20 40 40 6D-61 78 70 61-67 65 73 69 lect @@maxpagesi %·Ž·· _/·ì/··¥ 7A 65 ze :·

Run-time analytics and statistics available with ADO.NET

PerfMon support for visual runtime analytics Analyzing Performance with Connection Statistics

How do you guys analyze the logs?

Once you generate logs, you can contact us for additional insight. I have developed a set of standard based drivers to analyze the logs for unique data access intelligence that can be plugged into your favorite BI tool!  For example, we can calculate what percentage of time is spent in the database versus application to isolate performance bottlenecks.

It's not a secret any longer, so leave a comment on what DataDirect diagnostics can do for you:

Sumit Sakar

Sumit Sarkar

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.

Comments
Comments are disabled in preview mode.
Topics
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

More From Progress
232x131_resourceimage
Getting Ahead of the Hybrid Data Curve
Read More
 
570x321__Top 7 Considerations Before Choosing a Chatbot for Your Enterprise
Top 7 Considerations Before Choosing a Chatbot for Your Enterprise
Read More
 
232-132_How to Simplify Application Usage & Deployment with Microapps
How to Simplify Application Usage & Deployment with Microapps
Read More