Transitioning from the CAST AIP Oracle Analyzer to the SQL Analyzer extension

Summary: This page provides instructions for those that would like transition from using the “out-of-the box” Oracle Analyzer provided in CAST AIP to the SQL Analyzer extension. 

Important !

• Don’t change analyzed sources, keep analyzing src files. 
• GUID of equivalent objects are migrated during transition. 
• Checksum are not migrated and that will impact transactions.
• When you have multiple Oracle analysis units (extractions) in the same application, you should merge them in a single one!

A new report is generated during Run extensions after snapshot step, only for CAIP >= 8.3.

Read more about equivalent objects/rules/links at: SQL Analyzer - Mapping between SQL analyzers.

Introduction

If you have been actively analyzing Oracle files using Oracle Analyzer (provided out-of-the-box in CAST AIP) you can do transition using the SQL Analyzer extension to analyze your Oracle source code. The process of transition is described in this page. 

Step by step transition

Step 1: Backup CAST AIP triplet, Delivery/Deployment folders

To backup the CAST AIP schemas use the CSSBackup tool (located in the CSSAdmin folder in the CAST AIP installation folder) use the following scripts adapted to your environment.

Example

Backup CAST AIP schemas

CSSBackup.exe -schema ora_local -password CastAIP -file c:\temp\ORA\ora_local.cssdmp -log c:\temp\ORA\ora_local.log
CSSBackup.exe -schema ora_central -password CastAIP -file c:\temp\ORA\ora_central.cssdmp -log c:\temp\ORA\ora_central.log
CSSBackup.exe -schema ora_measure -password CastAIP -file c:\temp\ORA\ora_measure.cssdmp -log c:\temp\ORA\\ora_mngt.log
CSSBackup.exe -schema ora_mngt -password CastAIP -file c:\temp\ORA\ora_mngt.cssdmp -log c:\temp\ORA\\ora_mngt.log

Then  ZIP the Delivery and Deployment folders for easy restoration.

Step 2: Install SQL Analyzer 3.4.0-funcrel and then load Assessment Model

Install SQL Analyzer 3.4.0-funcrel.  If you expect to have Access link, please install latest version of datacolumnaccess extension, at the same time when you install SQL Analyzer.

And then load Assessment Model, e.g.: 

Step 3: Add a new UA SQL AU, on exactly the same sources

Add a new package on the same sources. You should clone the sources, replace the Oracle Package with a Files based package, on the cloned sources. Deliver and Deploy your new package. Add a new UA, and choose SQL for the AU.

Add dependencies between your new AU and client AU, if needed.

Migration log

The transition is executed during the Before Application Step, and is logged in BeforeApplicationExtensions castlog file. See attached example BeforeApplicationExtensions.castlog.

Step by step example

• Copy Oracle previous deployment folder

• Add new version

• Copy source code and update name and Release Date

• Remove Oracle Package

• Add new Package

• Paste the Oracle previous copied deployment folder in the Root folder field

• Then Deliver and Quit

• Accept Delivery and use it as current version

• Add new Universal Analysis Unit, then, choose SQL ans Universal language and add sources

• Change dependency

Dependencies example when application have more than one technology

Step 4 : Take a new snapshot and check results

Take a new snapshot and then check results in AED. Refer to SQL Analyzer - Mapping between SQL analyzers, to understand variations on equivalent rules.

Step by step example

• Take a new snapshot

• Check transition in the file BeforeApplicationExtensions castlog. Please note this step could take time, from seconds to half of hour, depending on number of objects you have in the previous analysis result.

See attached BeforeApplicationExtensions castlog file for the example below : BeforeApplicationExtensions.castlog.

• Check the Metrics Report, generated after the snapshot

After the snapshot, in the Run extensions after snapshot step, a csv report will be systematically generated, please have a look in the attached example: SQLAnalyzerMetricsReport.csv, and the log file: AfterSnapshotExtensions.castlog.  You will see: the number of the previous metric ID and the current one, the name of the current metric and also the number of violations and  the total of scanned objects before and now. 

The report covers all metrics, not only the SQL metrics. And it is generated in LISA folder.

Why we have 2 Metrics Ids in report ?

Sometimes the same Metric is implemented on both analyzers, but we may have different IDs or None. 

PreviousMetricId at None means the Metric is a new one and exists only on SQL Analyzer.

CurrentMetricId at None means the Metric exists only on Oracle Analyzer.

When PreviousMetricId is the exacly the same as CurrentMetricId that means we have exactly the same Metric on the both analyzers, otherwise we have an equivalent Metric on SQL Analyzer.

Example:

PreviousMetricId;CurrentMetricId;CurrentMetricName;PreviousResult[NumberOfViolations, Total];CurrentResult[NumberOfViolations, Total]
None;1101026;‘Always define column names when inserting values’;[0.0, 0.0];[0.0, 0.0]
7344;7344;‘Avoid “SELECT *” queries’;[5.0, 36.0];[5.0, 36.0]
7806;1101018;‘Avoid Artifacts with Group By’;[1.0, 27.0];[1.0, 27.0]
7766;7766;‘Avoid Artifacts with High Cyclomatic Complexity’;[7.0, 57.0];[7.0, 57.0]
1020000;1020000;‘Avoid Artifacts with High Cyclomatic Complexity (HTML5/Javascript)’;[0.0, 43.0];[0.0, 43.0]
7768;7768;‘Avoid Artifacts with High Depth of Code’;[3.0, 57.0];[3.0, 57.0]
1020090;1020090;‘Avoid Artifacts with High Depth of Code’;[0.0, 43.0];[0.0, 43.0]
7130;7130;‘Avoid Artifacts with High Depth of Nested Subqueries’;[0.0, 36.0];[0.0, 36.0]
7772;7772;‘Avoid Artifacts with High Essential Complexity’;[7.0, 57.0];[7.0, 57.0]
7776;7776;‘Avoid Artifacts with High Fan-In’;[1.0, 161.0];[1.0, 161.0]
7778;7778;‘Avoid Artifacts with High Fan-Out’;[2.0, 161.0];[2.0, 161.0]
7774;7774;‘Avoid Artifacts with High Integration Complexity’;[3.0, 57.0];[3.0, 57.0]
7828;7828;‘Avoid Artifacts with High RAW SQL Complexity’;[2.0, 36.0];[2.0, 36.0]
7808;7808;‘Avoid Artifacts with SQL statement including subqueries’;[16.0, 36.0];[16.0, 36.0]
7810;None;‘Avoid Artifacts with a Complex SELECT Clause’;[6.0, 27.0];[6.0, 27.0]
7126;7126;‘Avoid Artifacts with high Commented-out Code Lines/Code Lines ratio’;[0.0, 161.0];[0.0, 161.0]
7784;7784;‘Avoid Artifacts with lines longer than X characters’;[34.0, 161.0];[34.0, 161.0]
1020088;1020088;‘Avoid Artifacts with lines longer than X characters’;[2.0, 43.0];[2.0, 43.0]
7822;1101030;‘Avoid Artifacts with queries on more than 4 Tables’;[3.0, 27.0];[3.0, 27.0]
7770;1101016;‘Avoid Artifacts with too many parameters’;[2.0, 21.0];[2.0, 21.0]
1020076;1020076;‘Avoid Artifacts with too many parameters (Javascript)’;[0.0, 43.0];[0.0, 43.0]
7790;1101084;‘Avoid Cursors inside a loop’;[0.0, 27.0];[0.0, 27.0]
1020086;1020086;‘Avoid Functions having a very low Comment/Code ratio’;[40.0, 43.0];[40.0, 43.0]
None;1101010;‘Avoid NATURAL JOIN queries’;[0.0, 0.0];[0.0, 0.0]
1598;None;‘Avoid Rule HINT /*+ rule */ or –+ rule in PL/SQL code’;[0.0, 36.0];[0.0, 36.0]

………………………;