M416 Diagnostic And Debugging Tools in SQL Anywhere Studio

1
M416 Diagnostic And Debugging Tools in SQL
Anywhere Studio

• Chris Irie
• Development Manager
• iAnywhere Solutions
• Irie_at_sybase.com

2
Introduction

• Agenda
• Using the MESSAGE statement
• Using the Stored Procedure Debugger
• Using the Profiler
• Request-level logging
• Communications level debugging
• Version 9

3
MESSAGE Statement Debugging

• Add SQL MESSAGE statements to stored procedures
• MESSAGE message text TO CLIENT
• Pros
• Simple to use
• Cons
• Have to modify the source
• Message must be textual
• Changes the timing of the procedure

4
MESSAGE Statement Debugging

• Database Server With Message

5
MESSAGE Statement Debugging

• DBISQL With Message

6
MESSAGE Statement Debugging

• Using the PRINT statement
• PRINT format-string , arg-list
• Example
• DECLARE _at_var1 INT, _at_var2 INTSELECT _at_var1 3,
  _at_var2 5PRINT var11!, var22!', _at_var1,
  _at_var2
• Output
• var13, var25

7
MESSAGE Statement Debugging

• Copying Server Output To A File
• Use the o filename command line option for the
  database server to save messages to a file.
• E.g.
• dbeng8 o messages.txt asademo.db

8
ASA Debugger

• When Do You Need a Debugger?
• When you cant change the code
• When MESSAGE statements would give too much
  output
• When exploring legacy code

9
ASA Debugger

• What Can You Do With The Debugger?
• View source for procedures Java classes
• Plant breakpoints
• Single step (over, into, out)
• View variables
• View table data

10
ASA Debugger

• The Debugger User Interface

11
ASA Debugger

• The Procedures Window

12
ASA Debugger

• The Source Code Window

13
ASA Debugger

• Local Variables Window

14
ASA Debugger

• Connections Window

15
ASA Debugger

• Sample Stored Procedure
• procedure test( in _at_salary int )
• begin
• declare emp_fname char(64)
• declare emp_lname char(64)
• declare emp_salary int
• DECLARE err_notfound EXCEPTION FOR SQLSTATE
  '02000'
• declare myCursor cursor for
• select emp_fname, emp_lname, salary
• from employee where salary gt _at_salary
• declare local temporary table test_tmp ( name
  char(128), salary int )
•  
• -- Fill a temporary table with people who
  make more than given amount
• --
• open myCursor
• MyCursorLoop
• loop
• fetch next myCursor into emp_fname,
  emp_lname, emp_salary

16
ASA Debugger

• Setting A Breakpoint
• Choose a connection
• Choose a procedure
• Choose a line of code

17
ASA Debugger

• Setting A Breakpoint
• Tips
• Every time you set a breakpoint, make sure the
  right connection is selected
• Set breakpoints from the Source window. Dont use
  the Breakpoints window.

18
ASA Debugger

• Hitting A Breakpoint

19
ASA Debugger

• Hitting A Breakpoint
• Tip
• Dont connect to a production system with the
  Debugger.

20
ASA Debugger

• Single Stepping
• Toolbar buttons

Step Over
Step Into
Step Out
Go
21
ASA Debugger

• Disabling And Clearing Breakpoints
• Click the stop sign icon to disable a breakpoint
• Click again to remove it
• Use the Break/Clear All menu item to remove all
  breakpoints.

22
ASA Debugger

• Setting a Countdown Breakpoint
• Set a breakpoint for a specific connection
• Open the Breakpoints window
• Set the count

23
ASA Debugger

• Setting a Breakpoint With Condition
• Set a breakpoint for a specific connection
• Open the Breakpoints window
• Set the condition

24
ASA Debugger

• Setting a Conditional Breakpoint
• Breakpoints fire when count reaches zero
• Breakpoints fire when the SQL condition is true

25
ASA Debugger

• Modifying Variable Values
• Start editing by clicking a cell in the Value
  column and typing
• Complete editing by pressing ENTER
• Abort the change by pressing ESCAPE

26
ASA Debugger

• Viewing Table Data
• The only way to view temporary local tables
• Execute SELECT statement from the Query window

27
ASA Debugger

• Viewing The Server Message Log
• Messages are shown in the Output window

28
Profiling

• Measuring How Long a Procedure Takes
• MESSAGE statements (inaccurate)
• DBISQL (not enough information)
• Stored Procedure Profiler in Sybase Central-
  Accurate timing- Statistics are shown with the
  SQL code- Can profile procedures, functions,
  events, and triggers- Useful even if you dont
  know where to start

29
ASA Profiler

• How To Profile A Procedure
• Connect to database with Sybase Central
• Turn on profiling
• Call the procedure(s)
• Turn off profiling
• Study the results

30
ASA Profiler

• Profiler Results (Database Summary)

31
ASA Profiler

• Profiler Results (Procedure Summary)

32
ASA Profiler

• Profiling Tip
• Be careful when profiling SELECT statements or
  procedures that return result sets.
• SELECT countRows(1)
• SELECT countRows(2)
• SELECT countRows(3)
• Versus
• SET count countRows(1)
• SET count countRows(2)
• SET count countRows(3)

33
ASA Profiler

• Interpreting The Data
• Start with procedures where you unexpectedly
  spend the most time
• Look at procedures which are called frequently
• Look for procedures which are called too often
• Resetting The Profiling Information
• Profiling data is cumulative
• Reset profiling information between runs

34
Request-Level Logging

• What Is It?
• Low-level trace of application requests
• Done by the database server
• Creates a textual log file

35
Request-Level Logging

• Turning On Request-Level Logging
• Start the server with zr AllSQLNone option,
  or
• Call sa_server_option to set the
  request_level_logging optionSQL Shows primary
  requestsALL Shows all requests NONE Turns off
  logging
• Set the request_level_log_file server option

36
Request-Level Logging

• Setting The Log File
• Start the server with zo filename option, or
• Call sa_server_option to set the
  request_level_log_file optionE.g.
• CALL sa_server_option( request_level_log_file,
  c\\theServerLog.txt )

37
Request-Level Logging

• Tip
• Double up backslashes for file names in SQL
  strings
• CALL sa_server_option( request_level_log_file,
  c\\newLog.txt )
• c\\newLog.txt is not the same as
  c\newLog.txt!

38
Request-Level Logging

• Sample Output (Primary Requests)
• Output for select from customer where id108
• REQUEST conn 8997064 STMT_PREPARE
  "select from customer where id 108"
• DONE conn 8997064 STMT_PREPARE
  Stmt131089
• REQUEST conn 8997064 CURSOR_OPEN
  Stmt131089
• DONE conn 8997064 CURSOR_OPEN
  Crsr65554

39
Request-Level Logging

• Sample Output (All Requests)
• Output for select from customer where id108
• REQUEST conn 8997064 STMT_PREPARE
  "select from customer where id 108"
• DONE conn 8997064 STMT_PREPARE
  Stmt131085
• REQUEST conn 8997064 STMT_DESCRIBE_OUTPUT
  Stmt131085
• DONE conn 8997064 STMT_DESCRIBE_OUTPUT
  
• REQUEST conn 8997064 STMT_DESCRIBE_INPUT
  Stmt131085
• DONE conn 8997064 STMT_DESCRIBE_INPUT
  
• REQUEST conn 8997064 DO_NOTHING
• REQUEST conn 8997064 CURSOR_OPEN
  Stmt131085
• DONE conn 8997064 CURSOR_OPEN
  Crsr65550
• REQUEST conn 8997064 DO_NOTHING
• REQUEST conn 8997064 CURSOR_FETCH
  Crsr65550, Count1, Offset1 (relative)
• DONE conn 8997064 CURSOR_FETCH

40
Request-Level Logging

• Summarizing Results with sa_get_request_times
• A stored procedure
• Creates temporary table with statements and
  execution times, connection ID, and more

41
Request-Level Logging

• Results of Calling sa_get_request_times

42
Request-Level Logging

• Summarizing Results with sa_get_request_profile
• Groups similar statements and shows execution
  times
• Example
• SELECT FROM customer WHERE id101
• SELECT FROM customer WHERE id102
• SELECT FROM customer WHERE id103
• SELECT FROM employee WHERE emp_fname LIKE M
• SELECT FROM employee WHERE emp_fname LIKE B

43
Request-Level Logging
Results of Calling sa_get_request_profile
44
Request-Level Logging

• Summary
• Set request_level_logging option to SQL or ON
• Set the request_level_log_file option
• Execute your SQL statements
• Set the request_level_logging option to OFF
• Use sa_get_request_time or sa_get_request_profile
  to summarize the data.
• Tip
• Make sure you have a lot of disk space

45
Communications-Level Debugging

• What Is It?
• ODBC API tracing (Windows, client)- Traces every
  call to the ODBC layer- Must be using an ODBC
  driver (not jConnect)
• TDS tracing (server)- Must be using jConnect or
  OpenClient

46
Communications-Level Debugging

• ODBC API Tracing
• Open the ODBC Data Source Administrator
• Set tracing file
• Start tracing

47
Communications-Level Debugging

• ODBC API Tracing

48
Communications-Level Debugging

• ODBC API Tracing
• A Trace Extract, Part 1
• DBISQLG 648-348 ENTER SQLPrepare
• HSTMT 0B9B25B8
• UCHAR 0x08CECF38 -3
  "select from customer where id 101\ 0"
• SDWORD -3
•  
• DBISQLG 648-348 EXIT SQLPrepare with return
  code 0 (SQL_SUCCESS)
• HSTMT 0B9B25B8
• UCHAR 0x08CECF38 -3
  "select from customer where id 101\ 0"
• SDWORD -3

49
Communications-Level Debugging

• ODBC API Tracing
• A Trace Extract, Part 2
• DBISQLG 648-348 ENTER SQLGetData
• HSTMT 0B9B25B8
• UWORD 2
• SWORD 1 ltSQL_C_CHARgt
• PTR 0x08CECAD0
• SQLLEN 16
• SQLLEN 0x0C78FA3C
•  
• DBISQLG 648-348 EXIT SQLGetData with return
  code 0 (SQL_SUCCESS)
• HSTMT 0B9B25B8
• UWORD 2
• SWORD 1 ltSQL_C_CHARgt
• PTR 0x08CECAD0 8
  "Michaels"
• SQLLEN 16
• SQLLEN 0x0C78FA3C (8)

50
Communications-Level Debugging

• TDS Debugging
• For jConnect only
• Shows results data
• Turn on using the z server command line option

51
Communications-Level Debugging

• TDS Debugging
• TDS 1 dynamic statement 'dyn110' prepare
• TDS 1 dynamic prepare SQL 'select from
  customer where id110'
• TDS 1 cursor '__tds_cursor' permission updatable
• TDS 1 dynamic statement 'dyn110' describe output
• TDS 1 dynamic statement 'dyn110' describe input
• TDS 1 result set id int, fname char(15), lname
  char(20), address char(35), city char(20), state
  varchar(16), zip varchar(10), phone char(12),
  company_name varchar(35)
• TDS 1 request complete
•  
• TDS 1 cursor 'isql11' declare permission read
  only
• TDS 1 dynamic statement 'dyn110' used by dynamic
  cursor
• TDS 1 cursor 'isql11' permission read only
• TDS 1 cursor 'isql11' open
•  

52
Communications-Level Debugging

• TDS Debugging
• TDS 1 result set id int, fname char(15), lname
  char(20), address char(35), city char(20), state
  varchar(16), zip varchar(10), phone char(12),
  company_name varchar(35)
• TDS 1 request complete
• TDS 1 cursor 'isql11' info
• TDS 1 cursor 'isql11' inquire
• TDS 1 request complete
• TDS 1 cursor 'isql11' fetch
• TDS 1 result row 110, 'Michael', 'Agliori',
  '135 North Glebe Road', 'Oshawa', 'OH', '43216',
  '6145552496', 'The Pep Squad'
• TDS 1 request complete
• TDS 1 cursor 'isql11' fetch
• TDS 1 request complete

53
Communications-Level Debugging

• TDS Debugging
• Suppress TDS debugging using the
  SUPPRESS_TDS_DEBUGGING option
• Values are ON and OFF
• SET TEMPORARY OPTION SUPPRESS_TDS_DEBUGGING
  On
• SET TEMPORARY OPTION SUPPRESS_TDS_DEBUGGING
  Off
• (your SQL statements here)
• SET TEMPORARY OPTION SUPPRESS_TDS_DEBUGGING
  On

54
Review

• What Did We See Today?
• High Level Tools
• MESSAGE statement
• Debugger
• Profiler
• Low-Level Tools
• Request-level logging
• ODBC tracing
• TDS tracing

55
The Future

• Version 9.0
• Make it faster
• Make it simpler
• Integrate the Debugger into Sybase Central

56
The Future

• Artists Concept Of A New Debugger

57
Feedback

• Tell us what youd like to see in the next
  version
• Newsgroups
• sybase.public.sqlanywhere.product_futures
• sybase.public.sqlanywhere.general
• sybase.public.sqlanywhere.linux
• On forums.sybase.com

58
Feedback

• Come visit us in the Ask The Experts room
• Talk to real, live developers
• See real, live demos
• Take part in our usability study

59
Other Talks

• If you want to learn more about profiling and
• query optimization, you may be interested in the
• following sessions
•  
• M404 Adaptive Server Anywhere Performance
  Analysis From Beginning To End
• AM44 Adaptive Server Anywhere Performance And
  Tuning

60
Communications-Level Debugging

• QA