Summary: This document provides basic information about the extension that provides source code analysis support for SQL files.
- Support of DB2 for analysis.
- Removed limitation on Microsoft SQL Server and Sybase, when the body of a stored procedure/function is not defined in a "begin ... end block"
The SQL Analyzer (successor to the SQL Script extension) provides support for database technologies using the ANSI SQL-92 / 99 language. This extension uses the Universal Analyzer framework and is intended to analyse DDL, DML and SQL exports for a large variety of SQL variants:
- This extension provides source code analysis support for DDL and DML*.sql files using an over language of the various sql variants.
- This extension also accepts src and uaxDirectory files. Check here for more details about sqltablesize files.
In what situation should you install this extension?
- If you need to analyze PostgreSQL, MySQL, MariaDB, SQLite and DB2
- If your application contains schemas from database vendors not supported "out of the box" by CAST AIP (see http://doc.castsoftware.com, Supported Technologies for more information) but, which are compliant with ANSI SQL-92 / 99
- When you do not have access to the online dabatase to perform an extraction for use with CAST AIP and have instead been provided with DDL scripts
Transitioning from from the CAST AIP DB2 Analyzer to the SQL Analyzer extension
If you have been actively analyzing DB2 (z/OS or UDB) with the DB2 Analyzer (provided out-of-the-box in CAST AIP) you can transition to using the SQL Analyzer extension to analyze your DB2 source code. The process of transitioning is described in SQL Analyzer - To do transition from the CAST AIP Db2 Analyzer to the SQL Analyzer extension.
When transitioning from the DB2 Analyzer to the SQL Analyzer, links between Tables and Indexes, Foreign Keys, Primary Keys and Unique Keys will appear to be reversed when comparing the analysis results of the DB2 Analyzer and the SQL Analyzer. This is because the representation of links in the SQL Analyzer uses a different method (which is identical for all supported RDBMS) to the DB2 Analyzer.
Vendor compatibility matrix
|Up to version 9.5||Up to version 5.5||Up to version 10.0||Up to version 3.x||Up to version 11.1 (UDB) and 12 (zOS)|
Unofficial support refers to CAST AIP features or official CAST AIP extensions that provide specific capabilities that have not been officially validated or tested by CAST, therefore CAST cannot guarantee the results they produce. An example of a feature in this category is the capability built into the SQL Analyzer extension to analyze Oracle, Microsoft SQL Server and Sybase ASE source code: while the analysis will work, results are not guaranteed.
- Function Points (transactions): a green tick indicates that OMG Function Point counting and Transaction Risk Index are supported
- Quality and Sizing: a green tick indicates that CAST can measure size and that a minimum set of Quality Rules exist
Quality and Sizing
|CAST AIP release||Supported|
Supported DBMS servers used for CAST AIP schemas
This extension is compatible with the following DBMS servers used to host CAST AIP schemas:
|CAST AIP release||CSS2||Oracle||Microsoft|
|All supported releases|
|An installation of any compatible release of CAST AIP (see table above)|
Download and installation instructions
- The latest release status of this extension can be seen when downloading it from the CAST Extend server.
- Please see Known Limitations and Issues for information about an error that may occur when installing the extension if you have also already installed a very old and unsupported Universal Analyzer langage pack that conflicts with the SQL Analyzer.
Upgrade from the SQL Script extension
If you have previously used the SQL Script extension (com.castsoftware.sqlscript) on existing schemas, you should proceed as following:
- In CAST Server Manager use the Manage Extensions option on the CAST AIP schemas in which the SQL Script extension is installed
- Select Analyzer for SQL files and choose deactivate to remove the existing extension. No further actions are required.
Packaging, delivering and analyzing your source code
What results can you expect?
Once the analysis/snapshot generation has completed, you can view the results in the normal manner (for example via CAST Enlighten) - click to enlarge:
You can also use the CAST Management Studio option View Analysis Unit Content to see the objects that have been created following the analysis:
The following objects are displayed in CAST Enlighten:
|DDL Script File|
- Object identity is independent from the *.sql file the object comes from.
- Object identity depends on the Analysis Unit's identity. Therefore, using a new Analysis Unit or deleting and then recreating an Analysis Unit will change the object's identity and will result in added/removed objects in the subsequent analysis results.
- Typically a table will be identified by the Analysis Unit name, schema name and table name.
- When no schema can be determined, the analyzer considers that a schema named "DEFAULT" is used. But generally, identifiers are qualified in CREATE TABLE statements.
Table deletion and renaming
DROP TABLE syntax is supported for table objects within the same file. When creating a table through CREATE TABLE tableName (colName int, ...) followed by a DROP TABLE tableName, the table will not be recorded and thus will not be displayed in CAST Enlighten. Similarly, if a table is renamed with a RENAME TABLE statement (or ALTER TABLE RENAME TO as in SQLite and PostgreSQL), this change will be reflected in CAST Enlighten. Presently we consider case-insensitive names, i.e., objects named tableName, TABLEname are considered to be the same object.
Links are created for transaction and function point needs.
You can expect the following links on the DDL side within the same sql file:
- useSelect, useInsert, useUpdate, useDelete Links from Procedure / Function / Event to Table / View
- callLink from Procedure / Function / Event to Procedure / Function
- useSelect from View to Table / View used in the query of the view
- callLink from View to Function
- relyonLink from Index to the Table
- relyonLink from Index to the Column implied in the index
- referLink from :
- Table / Table Column to a Table / Table Column referenced in a Foreign Key
- from Synonym to Table / View / Function / Procedure / Package aliased by Synonym
- callLink to the correct Trigger where the tables is accessed in insert/update/delete
- example a Trigger declared as BEFORE INSERT on a table, any insert to that table will call the trigger...
You can expect the following links on the DML side :
- Links from SQL Script to Table provided as dependencies
- Links from client code to Table provided as dependencies
You can find a full list of rules delivered with this extension here:
Client Side Support : COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON
|Never use SQL queries with a cartesian product (1101000)|
|Never use SQL queries with a cartesian product on XXL Tables (1101002)|
|Avoid non-indexed SQL queries (1101004)|
|Avoid non-indexed XXL SQL queries (1101006)|
|Avoid non-SARGable queries (1101008)|
|Avoid NATURAL JOIN queries (1101010)|
|Specify column names instead of column numbers in ORDER BY clauses (1101012)|
|Avoid queries using old style join convention instead of ANSI-Standard joins (1101014)|
|Always define column names when inserting values (1101026)|
|Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries (1101028)|
Avoid exists and not exists independent clauses (1101032)
DISTINCT should not be used in SQL SELECT statements (1101034)
Use ANSI standard operators in SQL WHERE clauses (EMBEDDED SQL) (1101036)
Replace OR conditions testing equality on the same identifier in SQL WHERE clauses by an IN test condition (1101038)
Special Note about XXL/XXS support
See SQL Analyzer - working with XXL or XXS tables for more information.
Special notes about Quality Rules on client side
Some Quality Rules are calculated on SQL queries on client side with some limitations:
- Only Java queries found by inference engine are analysed, for example:
- Queries that generate dynamic links (eligible to DLM) are not analysed
- Queries to Hibernate or JPA objects or any other ORM are not analysed
- COBOL EXEC SQL queries are analysed
- SQL queries founded in python code
Known Limitations and Issues
If you encounter the following error in CAST Server Manager while installing the SQL Analyzer extension, please perform the workaround described here and then attempt the installation again. This error may occur if you have installed a very old and unsupported custom Universal Analyzer language pack that used the same metamodel type names as used in the official SQL Analyzer extension.
- All name resolving is considered as case insensitive : may produce wrong links on case insensitive platform 'playing with case' :
- 2 different tables with the same case insensitive name will be both called
- Procedure resolution does not handle overriding :
- when calling an overridden procedure, all overrides will be called
ALTER TABLE ... ADD ... syntax is supported. All other syntaxes, like ALTER TABLE ... DELETE .. or ALTER TABLE ... DROP ... or ALTER TABLE ... MODIFY ... etc. are not supported.
- Moving a table from one database/scheme to another is not supported through RENAME TABLEschema1.tableName1 TOschema2.tableName2.
- Sequences are not taken in account and that is not a limitation but a choice because they have no effect on transactions nor quality rules*
- Oracle synonyms on packages are not taken in account.
- For the QR 7156 Avoid Too Many Copy Pasted Artifacts, total values are displayed but no detail values.
- For the QR 1101012 Specify column names instead of column numbers in ORDER BY clauses, the case when a function that returns a number or a numeric variable is used in order by is not reported to violate the rule.