Database Specific information

SQL Server

Database Generic Information

  • Setup Issues 
    :  recommended to Change workspace when upgrading versions:

    Missing Icons - ie "Load Editor" icon missing change location of workspace, eclipse uses the icon layout saved in the workspace

    Workspace      - default :
                                          C:\Documents and Settings\[user]\dboptimizer\workspace 

                             [user] is the current user on the machine. (on windows)

                            Change  with:
                                                  “File->Switch Workspace->Other…”

    When changing workspace, you can add your old  SQL Project to the new workspace by
        right clicking in the SQL Project tab
        choose, "new" 
        uncheck "use default location"
        browse to the directory of your old workspace/SQLproject

  • Opening Saved Files  - various way
      •  windows: drag file from Windows Explorer into DB Optimizer
      •  File -> Open File
      •  SQL Project Tab, click on files from SQL Project directory
      •  SQL Project Tab , right click, create new SQL Project, add new directory (uncheck use default workspace            

                 NOTE:  Auto Commit is on by default, ie DML commits  after every statement unless turned off 
                               Turn off with 
                                                   "Window->Preferences->SQL Development->SQL Execution-> uncheck your platform(s)

    Data Source Explorer 

    SYSTEM objects are filtered out  by default. 
               Change with this preference in
                "Window -> Preferences -> SQL Filtering" , Example

    Objects are limited to 1000 per node (object type). 
                 Change with:  Example of changing this preference

              Refreshing the cache
    The datasource explorer caches definitions for  table, view, user etc. The definitions are used to proof read SQL and mark missing objects. If a new object has been created, outside of DB Optimizer, since it's been running, then it will be missing from the cache and flagged as non-existent in the SQL Editor. This can be fixed by refreshing the cache:

                      Window -> Preferences -> SQL Development - > Data source Indexing -> Start Indexing

                     NOTE: only objects checked in
                                                Window -> Preferences -> SQL Development - > Data source Indexing 
                               will ever be refreshed
                     NOTE: when ever DDL is run inside of DBO, the cache will be refreshed automatically to reflect any changes
                                if running DDL from some other tool, the DBO won't know to sync it's cache
    You can  manually refresh a node in the data source explorer (for example to refresh tables only)
                        Right click on the table node in data source explorer , choose "refresh"

    You can clear Data Cache to improve performance after running a long time and there is  alot of cache from multiple datasources.
                             Window -> Preferences -> SQL Development - > Data source Indexing -> Clear Index

    Can only profile one data source at at time, though tuning can be run concurrently. (though you can start up a second DB Optimizer using a different workspace and profile a second data source)
    Profiler .oar files are not compatible between 1.0 and 1.5. In order to read both types, 1.0 and 1.5, both versions of DBO must be installed.
    Profiler .oar files can only be open with same version of database up to the two major numbers like Oracle 10.2 OAR can't be open with Oracle 10.1

                        dboptimizer.exe profile ds:<data source> duration:< minutes> tofile:<file.oar> <overwrite>
                        dboptimizer.exe tune ds:<data source> sqlfile:<path to sql file> tofile:<tuning job save file.tun> <overwrite>

        Running command line from Performance Center
    see:  Performance Center issues for command line

                licenses are kept in  C:\Documents and Settings\All Users\Application Data\Embarcadero

    Hacking OAR file to change database platform and or version

                 Installed Documentation is kept in C:\Program Files\DB Optimizer 1.5\plugins\
                 Product Documentation

    Related External Links

                 SQL Best Practices !  - Educational Humor
                 DBO Stuff