What is the CAST Database Extractor?

Background information

In order to analyze SQL databases (Microsoft, Oracle and Sybase) that are part of an Application, AIP Console requires that the databases are delivered for analysis in "offline" file format. In other words, AIP Console does not connect to the database 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.

The "offline" files are generated by the CAST Database Extractor tool  - this tool will access the live RDBMS and extract and transform the required databases into file format - and it is these files that are fed into AIP Console.

How does the standalone database extractor work?

The standalone database extractor is a Java based tool which does not require installation. You can run it in GUI mode or via batch scripts in CLI mode. You can specify:

• the database(s) you want to extract
• the login you want to use to perform the extraction
• the location where you want to save the results

Once the database extractor has successfully completed, the results need to be fed into AIP Console for delivery. Subsequent analysis of the delivered databases in file format is then performed.

Where can I obtain the CAST Database Extractor?

You can download the extractor from CAST Extend.

Versions provided / supported Java JRE

The following database extractors are available for use

Supported RDBMS for extraction

The CAST Database Extractor can extract databases from any of the following RDBMS:

RDBMS type	Supported	Notes
Oracle		See list of supported RDBMS here. The CAST Database Extractor (for the analyzer embedded in CAST AIP) supports (by reference) the extraction of schemas on Oracle 18c and above in line with Oracle's updated release cycle, however the extractor will handle the schemas as Oracle 12c schemas and no new syntax or features introduced in these newer releases is supported.  Support for extracting schemas on Oracle ≥ 18c is provided in the extractor delivered in CAST AIP ≥ 8.3.16. Support for Case sensitive passwords (introduced in Oracle 12c R2) is provided in the extractor delivered in CAST AIP ≥ 8.3.16. The extraction of schemas on Oracle 8i is not supported.
Microsoft SQL Server		See list of supported RDBMS here. The CAST Database Extractor (for the analyzer embedded in CAST AIP) supports (by reference) the extraction of databases on Microsoft SQL Server 2016, 2017 and 2019, however the extractor will handle the databases as Microsoft SQL Server 2014 databases and no new syntax or features introduced in these newer releases is supported.  Support for extracting databases on Microsoft SQL Server 2016, 2017 and 2019 is provided in the extractor delivered in CAST AIP ≥ 8.3.25.
Sybase ASE		See list of supported RDBMS here. The CAST Database Extractor (for the analyzer embedded in CAST AIP) supports (by reference) the extraction of databases on Sybase ASE 16, however the extractor will handle the databases as Sybase ASE 15.x databases and no new syntax or features introduced in this newer release is supported.  Support for extracting database on Sybase ASE 16 is provided in the extractor delivered in CAST AIP ≥ 8.3.25.
Azure SQL		See list of supported RDBMS here. Support for extracting Azure SQL is provided in the CAST Database Extractor ≥ 2.3.31-funcrel.

Supported releases of CAST AIP for analysis of output data

The output of the CAST Database Extractor can be analyzed with any of the following releases of CAST AIP:

CAST AIP	Supported
8.3.x

Preloaded configuration file version numbers

The CAST Database Extractor contains multiple configuration files (one for each supported RDBMS) that contain the queries used to perform the data extraction. Below is a list the version numbers of each configuration file delivered in the current release of the CAST Database Extractor:

Prerequisites

To use the CAST Database Extractor, the following is required:

Java JRE

A Java JRE must be installed on the machine on which you want to run the CAST Database Extractor:

JRE version	Supported	Notes
≥ 1.6.x (32bit or 64bit)		Note that this is a minimum requirement - the CAST Database Extractor will work with more recent versions of the Java JRE. See also Versions provided / supported Java JRE above.

Supported Operating Systems

The CAST Database Extractor can be run form on the following Operating Systems:

Operating System	Supported	Supported by reference
Microsoft Windows 7 SP1 64-bit
Microsoft Windows 8 64-bit
Microsoft Windows 8.1 64-bit
Microsoft Windows 10 64-bit
Microsoft Windows 11 64-bit
Microsoft Windows Server 2008 R2 SP1 64-bit (Standard edition)
Microsoft Windows Server 2012 64-bit (Standard edition)
Microsoft Windows Server 2012 R2 64-bit (Standard edition)
Microsoft Windows Server 2019
Microsoft Windows Server 2022
Unix based (Linux/MacOS X)

Launching the CAST Database Extractor

The CAST Database Extractor is provided as an executable JAR file, which can be launched by double-clicking it. However when doing so you will likely encounter Java "out of memory" exceptions especially when extracting large or multiple databases/schemas. CAST therefore highly recommends that the executable JAR file is launched via a batch file containing the following commands:

java -Xmx1024M -jar D:\DBExtractor\CASTDBGUI.jar

The java -XMX1024M command allows you to configure the Maximum Java Heap Size of your JVM. Change the figure to match the number of MB you want to allocate to the process – i.e. increase the memory allocation until you no longer receive errors (CAST recommends starting at a minimum of 1024MB).

CAST Database Extractor GUI

Below are links to explanation of each section in the GUI:

• CAST Database Extractor - Source panel
• CAST Database Extractor - Options panel
• CAST Database Extractor - Target panel
• CAST Database Extractor - Advanced panel
• CAST Database Extractor - Expert panel
• CAST Database Extractor - Log panel
• CAST Database Extractor - Script panel

How to extract SQL source code from a live RDBMS

Extract/Initialize actions

The database extraction process is a two-step action:

• Extract > data is extracted from the required RDBMS and saved in an archive file with the extension .castextraction
• Initialize> the .castextraction archive file is transformed into UAXDIRECTORY, UAX and SRC files

These two steps can be run together, or separately in the CAST Database Extractor and shown by the menu option below (for the GUI):

Use the following information to help you decide how to proceed:

• In most circumstances, CAST recommends that you run the two steps together using the Extract + Initialize option
• If your source databases are large, you may want to run the Extract action first and then run the Initialize action later. The advantage of this is that you can view the log of the Extract action before you start the Initialize action.
• If you are extracting Microsoft SQL Server or Sybase databases
  • and you are using AIP Console to delivery and analyze your source code, you can deliver files that have been processed by the Extract step alone, or files that have been processed by the Extract + Initialize option. Both are accepted.
  • and you are using the legacy CAST Delivery Manager tool, you must run both the Extract and Initialize steps (whether together using the Extract + Initialize option or one after the other with the Extract and then the Initialize options) before you package the output with the CAST Delivery Manager Tool.
• If you are extracting Oracle schemas, then it is possible to run only the Extract action using the CAST Database Extractor and then complete the Initialize action using via AIP Console (this is automatically actioned) or via the legacy CAST Delivery Manager Tool (when configuring the package in the CAST Delivery Manager Tool, you should select the Reuse existing CAST extractor output):

How to run an extract/initialize with the GUI

Launch the CASTDBGUI.jar file using your batch file. The following will be displayed:

When launched, you need to:

• Configure the necessary fields in the Source panel to connect to your chosen RDBMS - make sure that you pay attention to the Database Parameters field which defines the schemas (Oracle) or databases (Microsoft SQL Server / Sybase ASE) you want to extract.
• Define the required location of the extract action output in the Options panel
• Define the required location of the initialize action output in the Target panel
• Click Run Application on the toolbar to commence the process.

Output

If the process is successful, you should see the following output:

• Extract action - output stored in a file called exportResults.castextraction as specified in the Extraction File Path field located in the Options panel.
• Initialize action - a set of files in the folder specified in the Target Folder Path in the Target panel. You should expect the following if you have extracted one schema/database:
  • one DatabaseExtraction.uaxdirectory file
  • various .uax files
  • various .src files

How to run an extract/initialize with the CLI

The CAST Database Extractor can be run in CLI mode should you wish to automate the extract/initialize process.

When using the command line, the process consists of a set of instructions and commands contained in a batch file (.bat) file. When the batch file is executed, the Java tool (CASTDBGUI.jar) is launched and the process begins. If you are extracting a large amount of data (i.e. multiple schemas/databases or one very large schema/database), you must use a batch file that contains specific Java Heap Space configuration commands (the -Xmx command), otherwise you will likely receive out of memory exceptions. Below is an example batch file for launching the .JAR tool.

Example batch files for Extract + Initialize option

REM ===============================
REM EXTRACT ACTION
REM ===============================
java
-Xmx1024M
-cp CASTDBGUI.jar com.castsoftware.extractor.cli.DatabaseExtractor
-vendor <vendor name>
-driver <driver name>
-url <JDBC URL>
-user <extraction user>
-password <extraction user password>
-parameters <database|schema>=<database/schemas for extraction>;minimal_access_mode=<dba|all>;skip_comments=<no|yes>;skip_modification_check_schema=<schemas not to test for modification>
-target <path to output location - no trailing backslash>
-l <path to extract log file>

REM ===============================
REM EXAMPLE 
REM java -Xmx1024M -cp CASTDBGUI.jar com.castsoftware.extractor.cli.DatabaseExtractor -vendor SQLServer -driver net.sourceforge.jtds.jdbc.Driver -url jdbc:jtds:sqlserver://dbserver:1433 -user sa -password mypassword -parameters database=MYDATABASE -target D:\DBExtractor\results -l D:\DBExtractor\output_extractor.txt
REM ===============================

REM ===============================
REM INITIALIZE ACTION
REM ===============================
java
-Xmx1024M
-cp CASTDBGUI.jar com.castsoftware.extractor.cli.Importer
-source <path to source location - no trailing backslash>
-target <path to output location - no trailing backslash>
-relativepaths
-l <path to initialize log file>

REM ===============================
REM EXAMPLE
REM java -Xmx1024M -cp CASTDBGUI.jar com.castsoftware.extractor.cli.Importer -source D:\DBExtractor\results -target D:\DBExtractor\initialize -relativepaths -l D:\DBExtractor\output_initialize.txt
REM ===============================

@echo off
REM SQLExtractor folder path
set EXTRACTOR_PATH=C:\Program Files\CAST\<version>\Extractors\SQLExtractor

REM Extract Parameters
REM ==================
REM -parameters parameter value. Note that the example below is set for Oracle Server only.
REM To extract MS SQL Server/Sybase ASE, use set "EXTRACT_PARAMETERS=database=<databases for extraction>"
set EXTRACT_PARAMETERS=schema=<schemas for extraction>;minimal_access_mode=<dba|all>;skip_comments=<no|yes>;skip_modification_check_schema=<schemas not to test for modification>
REM -l parameter value 
set EXTRACT_LOGFILEPATH=<path to extract log file>
REM -target parameter value, and Initialize -source parameter value  
set EXTRACT_TARGET=<path to output location - no trailing backslash>
REM -driver parameter value 
set EXTRACT_DRIVER=<driver name>
REM -url parameter value 
set EXTRACT_URL=<JDBC URL>
REM -user parameter value 
set EXTRACT_USER=<extraction user>
REM -password parameter value 
set EXTRACT_PASSWORD=<extraction user password>

REM Initialize Parameters
REM ==================
REM -l parameter value 
set INITIALIZE_LOGFILEPATH=<path to initialize log file>
REM -target parameter value 
set INITIALIZE_TARGET=<path to output location - no trailing backslash>

java -cp "%EXTRACTOR_PATH%\CASTDBGUI.jar" com.castsoftware.extractor.cli.DatabaseExtractor  -driver "%EXTRACT_DRIVER%" -url "%EXTRACT_URL%" -user "%EXTRACT_USER%" -password %EXTRACT_PASSWORD% -parameters "%EXTRACT_PARAMETERS%" -target "%EXTRACT_TARGET%" -l "%EXTRACT_LOGFILEPATH%"
SET RETURN_CODE=%ERRORLEVEL%

IF '%RETURN_CODE%' == '0' ( ECHO Extract Succeeded. Details in '%EXTRACT_LOGFILEPATH%' 
) ELSE ( ECHO Extract Failed with %RETURN_CODE%. Details in '%EXTRACT_LOGFILEPATH%'
    exit /B 1 ' Extract ERROR
 )

java -cp "%EXTRACTOR_PATH%\CASTDBGUI.jar" com.castsoftware.extractor.cli.Importer -target "%INITIALIZE_TARGET%" -source "%EXTRACT_TARGET%" -l "%INITIALIZE_LOGFILEPATH%"
SET RETURN_CODE=%ERRORLEVEL%
IF '%RETURN_CODE%' == '0' ( ECHO Initialize Succeeded. Details in '%INITIALIZE_LOGFILEPATH%' 
) ELSE ( ECHO Initialize Failed with %RETURN_CODE%. Details in '%INITIALIZE_LOGFILEPATH%'
    exit /B 2 ' Initialize ERROR
 )

exit /B 0

Output

If the extraction is successful, you should see the following output:

• Extract action - output stored in a file called exportResults.castextraction as specified in the -target parameter
• Initialize action - a set of files in the folder as specified in the -target parameter. You should expect the following if you have extracted one schema/database:
  • one DatabaseExtraction.uaxdirectory file
  • various .uax files
  • various .src files

Command line parameters

The following table lists all available commands for the CAST Database Extractor when run direct from the command line:

Extraction action

Parameter	Mandatory	Description
java or C:\path\to\java.exe		This command specifies the location of your JRE. You should not need to specify the path, however, if you do, replace the C:\path\to with the location of the Jave exe file. Note that if it is installed in a path containing spaces, you must surround the path with quotation marks.
-Xmx1024M		This is an optional but recommended parameter for the extract action that allows you to configure the Maximum Java Heap Size of your JVM. Change the figure to the number of MB you want to allocate.  This parameter is necessary if you are getting Out of memory exceptions for the Java Heap Space when you execute the batch file. Please increase the memory allocation until you no longer receive errors. Please see the JRE documentation for more information about this.
-cp D:\path\to\CASTDBGUI.jar com.castsoftware.extractor.cli.DatabaseExtractor		This parameter specifies the location of the CASTDBGUI.jar. Replace the D:\path\to with the location of the jar file.
-vendor <vendor name>		This parameter specifies the target RDBMS vendor for the extract action. See Source panel for more information.
-driver <driver name>		This parameter specifies the JDBC Driver that is appropriate for the vendor chosen with the -vendor command. See Source panel for more information.
-url <JDBC URL>		This parameter is used to specify the source server for connection purposes. Enter the JDBC Connection parameters for the vendor/driver chosen above. See Source panel for more information.
-user <extraction user>		This parameter tells the tool what login to use to access the source server. Enter a login with sufficient rights as explained in Required RDBMS rights for packaging a database.
-password <extraction user password>		This parameter tells the tool what password to use to access the source server (corresponds to the login specified above). Enter the correct password for the login specified above.
-parameters		<database|schema>=<database/schemas for extraction> This parameter specifies the schema/database you would like to extract: If you are extracting from an Oracle server, use the -parameters schema=<database/schemas for extraction> syntax If you are extracting from a Microsoft SQL Server or Sybase ASE server, use the -parameters database=<database/schemas for extraction> syntax See Source panel for more information.
		minimal_access_mode=<dba|all> Only available when running an extraction on an Oracle server. This option determines the access mode used for the extraction (DBA_* views or ALL_* views) and accepts either of the following values: dba (default) - this can improve performance. all See Source panel for more information about the option.
		stop_on_invalid_objects=<no|yes> Available in v. ≥ 2.3.40. Only available when running an extraction on an Oracle server. When enabled this option will force the extraction process to stop when an invalid object is encountered.
		skip_comments=<no|yes> Only available when running an extraction on an Oracle server. This option determines whether comments on tables, columns, views and materialized views will be extracted or not. Extracting comments (or not) has no impact on analysis results or Automatic Function Point values - they will either be visible or not in CAST Enlighten. Accepts either of the following values: No (default) Yes - this can improve performance. See Source panel for more information about the option.
		skip_modification_check_schema=<schemas not to test for modification> Only available when running an extraction on an Oracle server. If an object is compiled or modified when the extraction is run, the database extractor will detect this by default and messages to that effect will be displayed in the log file (see What happens if an object is compiled or modified when the extraction is run on an Oracle Server?). If you would prefer that this check is not performed during an extraction, then you can list the schemas (separated by commas) which should be excluded from the check.
		connection:oracle.net.encryption_client=, connection:oracle.net.encryption_types_client=, connection:oracle.net.crypto_checksum_client=, connection:oracle.net.crypto_checksum_types_client= 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 database extractor to connect to an Oracle Server which accepts only encrypted connections. The default values provided in the CAST Database Extractor 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 CAST Database Extractor to match those defined in the SQLNET.ORA file. If your target Oracle Server is configured to accept unencrypted connections, do not add these options Permitted values: 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 Database Extractor may not be fully compatible and CAST does not recommend using them unless you are forced to.
-target <path to output location - no trailing backslash>		This parameter specifies the location you would like the extract action to output the results to (the resulting file will be a ZIP format file with the .castextraction extension). See Options panel for more information.
-l log_file.txt		This parameter specifies an output file for logging purposes. Specify a .TXT file to contain the log of the entire extract action. You can name the file anything you like. If omitted, the output will be displayed in the command line window.
-platform <platform>		See Advanced panel for more information.
-logtime <log time>
-retry <connection retry>
-timeout (time out ms)
-secure
-zip <zip packaging>		See Expert panel for more information.
-format <export format>
-idcachefolder <internal id cache folder>
-multithreaded <multithreaded export>
-checktype <type to check>
-checkconfig <verbose configuration>
-abortonfatal <abort on fatal error>
-h		Use this command to display a list of available command line options - you may need to output the list to a text file.

Initialize action

Parameter	Mandatory	Description
java or C:\path\to\java.exe		This command specifies the location of your JRE. Note that if it is installed in a path containing spaces, you must surround the path with quotation marks.
-Xmx1024M		This is an optional but recommended parameter for the extract action that allows you to configure the Maximum Java Heap Size of your JVM. Change the figure to the number of MB you want to allocate.  This parameter is necessary if you are getting Out of memory exceptions for the Java Heap Space when you execute the batch file. Please increase the memory allocation until you no longer receive errors. Please see the JRE documentation for more information about this.
-cp D:\path\to\CASTDBGUI.jar com.castsoftware.extractor.cli.Importer		This parameter specifies the location of the CASTDBGUI.jar. Replace the D:\path\to with the location of the jar file.
-source <path to source location - no trailing backslash>		This parameter specifies the file created by the extract action (named with a .castextraction extension). Do not include the extension.
-target <path to output location - no trailing backslash>		This parameter specifies the folder you would like the files to output to. Note that this action will create a large number of files.
-relativepaths		This parameter will force the creation of relative paths (instead of absolute paths) in the resulting .UAX files that point to the location of the source code (this is used in the CAST dashboards and in CAST Enlighten). This allows you to move the results of the Initialize action and not lose the accompanying source code.
-dispatch		This option will force the results of the Initialize action to be placed in multiple folders within the location defined with the -target option. The uaxdirectory file will be placed at the root of the location defined with the -target option. CAST highly recommends using this option if you are planning to deliver a very large single schema or a high number of multiple schemas: the files generated by the deliver action will be spread over multiple folders, thus avoiding a situation where the host file system becomes overloaded the time taken to deliver the results of the extract action can be greatly reduced (anything up to ten times quicker) See Advanced panel for more information.
-l log_file.txt		This command specifies an output file for logging purposes. Specify a .TXT file to contain the log of the entire Initialize action. You can name the file anything you like. If omitted, the output will be displayed in the command line window.
-logtime <log time>		See Advanced panel for more information.
-platform <platform>
-zip <source zip packaging>		See Expert panel for more information.
-ziptarget <target zip packaging>
-unicode <unicode encoding>
-uaxcode <UAX code information generation>
-shortids <IDs compression in UAX file>
-checkconfig <verbose configuration>
-config <configuration file path override>
-h		Use this command to display a list of available command line options - you may need to output the list to a text file.
-report		This command simulates the import step, but without generating the UAX/SRC files. Instead the output is a single DatabaseExtraction.uaxreport file, identical to the DatabaseExtraction.uaxdirectory file generated in "normal" (i.e., non report) mode.

What happens if an object is compiled or modified when the extraction is run on an Oracle Server?

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!
Execution succeeded!

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
Execution failed!

Log messages

<CAST_AST_WithResolutionSymbolReference resolutionSymbol="DT.-1"/> (Oracle Server)

When attempting to run an analysis of Oracle Server source code that has been extracted with the standalone CAST Database Extractor, the analysis log may contain multiple warnings as follows:

Line XX: <CAST_AST_WithResolutionSymbolReference resolutionSymbol="DT.-1"/>

This issue exists due to the fact that some new built-in datatypes are not extracted with the standalone extractor to avoid breaking backward compatibility with CAST AIP versions older than 8.x. If this occurs then there are two workarounds available to you:

• Either use the CAST Delivery Manager Tool to complete the extraction of the required schemas

• Or, if you must use the standalone CAST Database Extractor, you can explicitly specify the configuration file to be used by the standalone CAST Database Extractor: OracleCASTDatabase.config, instead of the default CASTDatabase.config. This can be done in the GUI (Expert panel, Configuration File Path Override option) or in the CLI (-config option). This second workaround will only function when the output of the CAST Database Extractor is used with CAST AIP ≥ 8.x.

<CAST_AST_ANSISQL_ColumnDataTypeReference for identification group: DT on value: SYS.... (Oracle Server)

Resolution error type in: CAST_AST_ANSISQL_ColumnDataTypeReference for identification group: DT on value: SYS.AQ$_SIG_PROP

This error can safely be ignored: the message indicates that the datatype used to define a column of an extracted table cannot be found and in the example above the datatype belongs to the SYS schema. In all situations the SYS schema is never extracted by the CAST Database Extractor behavior

What to do with the output from the CAST Database Extractor

Once you have successfully run the CAST Database Extractor to extract the required schemas/databases, the next step is to deliver this output. See SQL - Prepare and deliver the source code for more information.