database and SQL coding tools

see also:

• information technology and computer programming
• Delphi computer programming tool - main index

• in general each of these tools can be purchased for a single database platform (eg. MS SQL Server) or for all supported platforms
• these use JDBC database drivers and are based on Java technology
• NB. in SQL Server platform, SQL Server Express is NOT supported, only Microsoft SQL Server 2005, 2008, 2012

• includes 3 or 4 Pro versions of either:
  • DB Artisan
  • DB Optimizer
  • Rapid SQL
  • DB Change Manager

• online store - SQL Server only version: $AU1040 in 2014
  • states it includes the PRO versions of:
    • DB Optimizer
    • Rapid SQL
    • DB Change Manager

• database profiling
  • profile chart shows the CPU, I/O, and other wait activity over the course of the session. Zoom in/out functionality available. (Wait categories vary by platform.)
  • Detailed information on the profiled SQL and wait categories, broken down by SQL statements, events, and sessions.
• load testing
  • sampling to identify and diagnose performance bottlenecks and problematic SQL without agents or placing a significant load on the target database
  • SQL stress testing simulates a number of parallel users and executions over a specific period of time or execution cycle.
  • Continuously profile an entire data source within a configurable span of time
• reporting

• DEV features plus:
  • advanced profiling (Oracle only)
  • SQL tuning
    • SQL tuning wizard automatically suggests solutions
    • visual SQL tuning diagram displays indexes and constraints on tables and views, as well as the joins used in a SQL statement such as Cartesian joins, implied Cartesian joins and many-to-many relationships, with table statistics
    • color-coded Index Analysis feature shows indexes that are used (green), not used (blue), or missing (orange) and offers indexing recommendations for optimum performance

• SQL & Object Editing/Scripting
  • Code templates eliminates the need to memorize and type SQL syntax
  • Code folding, code collapse/roll-up, syntax coloring, hot key assignments, configurable auto replace of objects, bind variable support, selective statement execution
  • SQL code assist
  • real-time SQL syntax validation
  • Visual Query Building tools
• Schema Development
• Version Control
  • Collaborate with your team with version control, reverse-engineering, and source code repository capabilities

• as for standard edition, plus adds:
  • SQL Debugging
    • features basic execution, line-by-line execution, breakpoint support, and other common debugging features.
    • Debug Java, step seamlessly into SQL (i.e. stored procedure) and back into Java again – true system-wide, round-trip debugging
  • Code Analyst
    • Performs detailed response time analysis on the execution of stored procedures and functions
  • PL/SQL Profiler (Oracle only)
    • Captures metrics of various PL/SQL programmable objects on Oracle 8.1.5 and higher.

• does not support SQL Server Express
• Profiler running on a live SQL Server v9.00.4035 server:
  • after ~30secs of the profiler running, the session is interrupted with error The profile session was stopped due to some connectivity issues. Incorrect syntax near '.'.
  • SQL Text in profile just displays UNKNOWN - perhaps inadequate log on privileges or…issues with the profiler and SQL text collection which has been improved from earlier versions by querying the database cache more often and more intelligently thereby reducing the occurrences of UNKNOWN statements.
  • profiler seems to run fine on a SQL server v. 9.00.3042 where there are no omplex queries being run and so no results are being displayed

• Schema compare/synchronize
  • Capture and compare schema differences between one or more archived or live databases
  • Generate alter scripts to sync selected objects or restore to a prior state. Handles object dependencies, and preserves data.
  • Automatically synchronize the target to match the source once there is a successful comparison. Promote schema changes from development, to test and finally to production.
  • Ensure database synchronization by using a live-to-live database compare as a basis for obtaining a list of changed objects, and a change script to roll the changes forward. Capture a snapshot of the target before you roll changes forward, so that you can roll back a schema change if necessary.
  • Reveal and report on changes to packaged application databases resulting from vendor patches or customization. Uncover abnormalities in the database that result from improper change, which might impact production performance or functionality. Notify yourself or your colleagues when a particular portion of the database has changed: for example tables for data modelers, stored procedures for developers, etc.
• Data compare/synchronize
  • Compare and synchronize data either inside of one database or between two different databases
• Source code control integration

• DEV version plus:
  • Auditing
    • Use custom or live configurations to create standards. Audit database configurations to check for security liabilities and performance standards
    • Identify and report on changes to the database at or between points in time, or to particular sensitive objects: ad hoc or on a regular basis. Audit database configurations against established standards to minimize the vulnerability of your databases.
  • Data Masking
    • Ensure accurate testing while complying with international privacy laws
    • Shuffle or randomize columns in a data compare with an auto-synchronization job. De-identify data in the production environment and quickly migrate the masked, yet realistic and fully functional, data into Development, Test and QA environments.

• PRO version plus:
  • Command-line APIs for scheduling