Accessing DB Optimizer's Raw Data via SQL or Excel

From DB Optimizer
Jump to: navigation, search

HSQL DB Access

The format of the raw data might change in future releases and writing code that depends on the raw data is not supported.

The contents of a profiling session can be accessed with DB Optimizer via a JDBC connection and they can be exported to a CSV file to be imported into another database or Excel. To access the contents of a profiling session. 1. Find the ".oar" file from the profiling session 2. change the extension from ".oar" to ".zip" 3. unzip the file into a new directory say C:\hsql 4. open DB Optimizer and choose "add datasource" 5. for connection type , choose "Generic JDBC" 6. If "JDBC driver:" is empty and there is nothing in the pulldown list, then

a. download attached file "" and unzip it into a permanent directory, like c:\program files\jdbc
b. hit "Edit" button
c. hit "Add..." button in new popup
d. brows to the location you extracted into
e. choose both
i. hsqldb.jar
ii. hsqlfunctions.jar
f. "Driver class:" should say "org.hsqldb.jdbcDriver"
g. fill out "Name:" with something like "jdbcdriver"
h. hit "OK"
i. choose "jdbcdriver" (ie the name you filled out in step g)

7. for "Connection URL:" fill it in with the form

where you replace "C:\zzz_hsql2\" with the location you unziped the files form the OAR file in step 3, for example

8. fill in username with "SA", password is empty 9. hit "Finish" Now you have a database connection to the contents of the OAR file, ie the profiling session. You can access all the raw data via SQL or export it to CSV and import to Excel

Main table is PUBLIC.SESSION It contains the sampled data.

Session table.PNG PUBLIC.SQL_STAT will have SQL execution statistics

Right click in the data area to save data to a CSV file

More Advanced