Home Services Partners Company
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

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Read next SAS ODBC Access to Salesforce, SQL Server and Marketo
Comments
Comments are disabled in preview mode.