The information below is valid only for the legacy PL/SQL and T-SQL analyzers embedded in CAST AIP
. See SQL - Available extensions
for information about SQL related extensions.
Summary: This section describes how to prepare and deliver the source code of your SQL database.
Preparation - Source code discovery
Discovery is a process that is actioned during the packaging process whereby CAST will attempt to automatically identify projects within your application using a set of predefined rules. Discoverers are currently embedded in CAST AIP:
You should read the relevant documentation for each discoverer (provided in the link above) to understand how the source code will be handled.
When the Package action is complete, you can view the projects that have been identified in the Package Content tab. When a successfully Packaged application is subsequently deployed in the CAST Management Studio, an Analysis Unit will be created for each project that has been identified during the Discovery process and is not excluded by a rule or filter.
Using CAST AIP Console
AIP Console expects either a ZIP/archive file or source code located in a folder configured in AIP Console. You should include in the ZIP/source code folder all the output from the CAST Database Extractor, i.e:
AIP Console supports this file type as follows:
- for extractions of PL/SQL (Oracle Server) schemas in all AIP Console releases
- for extractions T-SQL (Microsoft SQL Server, Sybase ASE and Azure SQL) in AIP Console ≥ 1.22
When a .castextraction file is delivered to AIP Console, it will be transformed automatically into the required .uaxDirectory/.uax./src files during the source code delivery process. Technically the following occurs:
- For each .castextraction file AIP Console checks if there is no equivalent .uaxdirectory for the same extraction (in the delivery)
- If YES, then the .castextraction file is removed from the list of extraction files and the .uaxDirectory is used instead.
- If NO, then AIP Console checks the type of .castexraction file that has been delivered.
- If a PL/SQL .castextraction file has been delivered, then no immediate preprocessing is necessary and the list of extraction files is not changed. The transformation to .uaxDirectory is actioned when the version is set as the current version.
- If a T-SQL .castextraction file has been delivered, then this will undergo a preprocessing action:
- AIP Console cleans up the preprocessed folder on the AIP Node (located in %PROGRAMDATA%\CAST\AipConsole\AipNode\upload\preprocessed)
- Folder structure is created within the preprocessed folder to support multiple .castextraction files if necessary
- The delivered .castextraction file is transformed into uaxDirectory format
- The .castextraction file is replaced by the new uaxdirectory format files in the list of extraction files.
With regard to T-SQL .castextraction files:
- If using AIP Core ≤ 8.3.25, the creation of invalid or unsupported .castextraction files was allowed. As such, if (during a new source code delivery) AIP Console finds an existing package containing an invalid or unsupported .castextraction file , a popup stating that the package is invalid will be displayed. This invalid package should be should be removed using the - this will then allow the new .castextraction file to be processed.
|Supported for both PL/SQL (Oracle Server) schemas and T-SQL (Microsoft SQL Server/Sybase ASE/Azure SQL) databases.|
CAST highly recommends placing the files in a folder dedicated to your extraction. If you are using a ZIP/archive file, zip the folders in the "temp" folder - but do not zip the "temp" folder itself, nor create any intermediary folders:
How do I add a source code package to my delivery
See How do I add a source code package to my delivery.
What you should package?
When creating packages to discover and extract your SQL application you should create them as listed below:
In order to analyze SQL databases/schemas (Microsoft, Oracle and Sybase) that are part of an Application, the analyzer embedded in CAST AIP requires that the databases/schemas are delivered for analysis in "offline" file format. In other words, CAST AIP does not connect to the database/schema during the analysis and instead the "offline" files are analyzed. This delivery/analysis method provides a distinct performance boost and allows the delivery to be performed by a dedicated DBA.
Although the CAST Delivery Manager Tool already includes a specific "extractor" to support the extraction of SQL databases/schemas, in practice the use of the CAST Delivery Manager Tool in certain environments may not be possible due to security constraints or other obstacles. As a direct response to this, CAST provides a standalone Database Extractor that has been decoupled from the CAST Delivery Manager Tool. This extractor is identical to the extractor provided in the CAST Delivery Manager Tool, but it can be run without the need to download and install the CAST Delivery Manager Tool.
- the target schemas/databases are extracted and packaged by the CAST Delivery Manager Tool (via a JDBC connection) into files that can be analyzed by the SQL Analyzer embedded in CAST AIP.
- the target schemas/databases are extracted and packaged by the standalone CAST Database Extractor into files that are then fed into the CAST Delivery Manager Tool and can be subsequently analyzed by the SQL Analyzer embedded in CAST AIP.
Use the Oracle Server / MS SQL Server / Sybase options in the CAST Delivery Manager Tool:
Click to enlarge
Then choose to use the database extractor embedded in CAST AIP:
Fill in the connection details for your database:
Enter the internet host name (or machine name) of the machine on which the database is installed so that the CAST Delivery Manager Tool can access via a JDBC connection
Oracle Server only. Enter the port number on which your database runs. The default value for Oracle Server is 1521.
Configure how the Delivery Manager Tool should access your host::
- Oracle: Service or SID
- Microsoft SQL Server/Sybase ASE: Port (Microsoft SQL Server = 1433, Sybase ASE = 12500) or Instance Name.
When connecting to a "pluggable database" (for example in Oracle 12c and above) you MUST use this Service option - the SID option will not function.
Used to configure the User name for the target database for extraction purposes. Please refer to SQL - Required RDBMS rights for packaging a database using the legacy CAST Delivery Manager Tool for more information about the user name and password you should use.
|Password||Used to configure the password (in encrypted format) that corresponds to your User name configured above.|
This option enables you to force the CAST Delivery Manager Tool to save the database access credentials you have entered above. Choosing an option or not has no impact on the analysis (i.e. the analyzer can still run an analysis). However, if you are creating subsequent Versions using the same SVN repository, you can choose to store the password in which case you will not need to re-enter it.
There are two save options:
- Local > The credentials are saved in the user's local workspace on the current machine. Choose this option if you do not want the password to be available to other Delivery Managers.
- Server > The credentials are saved locally (as above) and are also synchronized back to the CAST AIC Portal (i.e. the Source Code Delivery Folder). Choose this option if you want the password to be available to other Delivery Managers.
Only visible when an Oracle Server has been selected. These options are aimed at extraction of schemas hosted on Oracle Server 11 / 12 that require encrypted connections.
These four options allow you to configure the extractor to connect to an Oracle Server which accepts only encrypted connections. The default values provided in the DMT are the default settings used on the Oracle Server side, therefore if your target Oracle Server uses a custom/specific encryption policy, you should change the options in the DMT to match those defined in the SQLNET.ORA file.
If your target Oracle Server is configured to accept unencrypted connections, please manually remove all options from all four fields.
- connection:oracle.net.encryption_client: REJECTED, ACCEPTED, REQUESTED, REQUIRED
- connection:oracle.net.encryption_types_client: AES256, AES192, AES128
- connection:oracle.net.crypto_checksum_client: REJECTED, ACCEPTED, REQUESTED, REQUIRED
- connection:oracle.net.crypto_checksum_types_client: SHA512, SHA384, SHA256, SHA1, MD5
Note that while the possibility to accept encrypted was introduced in Oracle Server 10g, the default settings available in the CAST Delivery Manager Tool may not be fully compatible and CAST recommends removing the four default values and to use them only if absolutely required.
Then, choose the schema(s) you want to include in the package. There are two methods of doing this:
- Either browse the database and choose those you require (we will do this as it also means you can check that the connection details you entered are correct)
- Or enter the schema name(s) manually
- The chosen schema (CASTPUBS in this example) will now be listed in the table:
Note that if you intend to extract and analyze multiple schemas and you expect to be able to view inter schema links after the analysis, please ensure that all schema/databases from the same instance are included in the same CAST Delivery Manager package.
If you have already used the standalone CAST Database extractor
Use the Oracle Server / MS SQL Server / Sybase options in the CAST Delivery Manager Tool:
Click to enlarge
Then choose the reuse existing output options so that you can feed in the results of the CAST Database Extractor.
Note that there is an additional option Reuse existing extractor output
, however, this should only be used when you have used the "Extract
" only option in the CAST Database Extractor
Fill in the details of your standalone CAST Database Extractor output:
|Server Version File||Use this option to select either the .castextraction or the root .uaxdirectory file depending on the output you have from the CAST Database Extractor.|
|Schemas/databases to extract|
Use this option to select the schemas/databases you want to extract:
- Click the button to browse a list of schemas/databases in the .castextraction or the .uaxdirectory file - schemas/databases that were previously extracted will be offered.
- Click the button if you know the name of the schema/database you want to package. A dialog box will be displayed enabling you to enter the name of the schema/database. You can only enter one schema/database name at a time using this option - to enter multiple schemas/databases, either repeat this action, or use the option above. Please ensure that you input the name of the schema/database in the correct case (i.e. upper or lower) as this field is case sensitive - remember that the vast majority of Oracle schemas are in upper case.
Click OK when you have selected the items you require.
When using a .castextraction file:
- this option is not mandatory. If you do not explicitly select a schema using the option or the option, ALL schemas available in the .castextraction file will be automatically selected for packaging.
When using a .uaxdirectory file:
- This option is mandatory and you must explicitly select a schema either using the option or the option for packaging. Therefore, if you want to select all schemas in the selected .uaxdirectory then you need to specifically choose them using this option.
When using either .castextraction file or .uaxdirectory file:
When packaging Version N+1 where Version N was packaged with the "SQL - Prepare and deliver the source code#live" option, then you MUST ensure that you explicitly select the "PUBLIC" schema for packaging as well as the other schema(s) you require in Version N+1. This is because when a "live" packaging is completed, objects belonging to the PUBLIC schema will also be automatically packaged when they are used by objects in other schemas that are selected for packaging. Omitting the PUBLIC schema in Version N+1 will mean that there will be a difference in the packaging results between Version N and N+1. When using a .castextraction file, this can be achieved by not selecting anything since doing so will ensure ALL schemas are packaged, but this may also include schemas you do not want.
How do I package the Version?
See How do I package the Version for more information.
If an object is compiled during the Package action, then a warning is raised in the log and the extraction will successfully complete with the message Execution succeeded (this means that the .castextraction file will be generated and can be used without any further issue: compilation does not modify the object scripts). Example from the log:
2018-01-08 13:37:33.467 - Some objects from 'Schema A' have been compiled during the extraction: initial 'Last Compilation Date' is '2018-01-08 13:36:57.0', new 'Last Compilation Date' is '2018-01-08 13:37:13.0'
2018-01-08 13:37:33.470 - Disconnected!
If an object is modified during the Package action, then an error is raised in the log and the extraction will fail with the message Execution failed (this means that the .castextraction file will be generated but must not be used: a modification will change the object scripts and the schema will therefore be inconsistent and the Package action must be re-run). Example from the log:
2018-01-08 13:39:27.742 - Invariance error #-10: 'Schema A' has been modified during the extraction: initial 'Last Modification Date' is '2017-07-17:11:22:08', new 'Last Modification Date' is '2018-01-08:13:39:09'
2018-01-08 13:39:27.743 - Some objects from 'Schema A' have been compiled during the extraction: initial 'Last Compilation Date' is '2018-01-08 13:37:13.0', new 'Last Compilation Date' is '2018-01-08 13:39:09.0'
2018-01-08 13:39:27.760 - Disconnected!
Error executing: Extraction -driver oracle.jdbc.OracleDriver -url jdbc:oracle:thin:@server:1521:TEST -user A -password **** -parameters schema=A;minimal_access_mode=all -target C:\TEST\exportResults => -1
How do I fine-tune my Version ?
See How do I fine-tune my Version for more information.
How do I deliver the Version for analysis?
See How do I deliver the Version for analysis for more information.
See Validate and Accept the Delivery for more information.