Top-Level Code Units Report

The Top-Level Code Units report provides a detailed overview of the Top-Level Code Units present in the source code.

What is a Top-Level Code Unit?

A Code Unit, as the name suggests, is the most atomic, standalone executable element. In most cases, these are statements, but they also include script files as well because those are executed as a single element.

Some Code Units can be nested inside other Code Units. When there is no other Code Unit above it in a hierarchy of Units, it's called a Top-Level Code Unit.

What is an out-of-scope Code Unit?

Out-of-scope Top-Level Code Units are Code Units that are out of the conversion scope of SnowConvert. Because of this, these code units are not considered when calculating the conversion rate. Each of these code units will not have a conversion rate (it will appear as N/A).

If the input code includes only out-of-scope Code Units, then the Lines of Code conversion rate of the entire migration will be 0%.

The following CREATE TRIGGER is considered an out-of-scope Code Unit.

Code Sample

CREATE OR REPLACE TRIGGER my_trigger
    AFTER 
    UPDATE
    ON my_table
    FOR EACH ROW
BEGIN
   NULL;
END;

Examples of Top-Level Code Units

In the following section, we can see some examples of Top-Level Code Units.

Queries

In the following example, we have here a single SELECT statement. This statement is a single Top-Level Code Unit.

Code Sample

SELECT * FROM table1;

In this example, we have a nested SELECT statement nested inside another SELECT statement. The entire query counts as a single Top-Level Code Unit.

Code Sample

SELECT * FROM (SELECT * FROM table1);

Objects

Objects created with a DDL count as a single Top-Level Code Unit, even if it contains other Code Units inside it.

The following statement creates a view with a query. In this case, the entire CREATE VIEW counts as a single Top-Level Code Unit.

Code Sample

CREATE VIEW view1 AS SELECT * FROM table1;

The following CREATE PROCEDURE statement counts as a single Top-Level Code Unit even if it contains multiple statements inside it.

Code Sample

CREATE PROCEDURE procedure1
AS
BEGIN
    DELETE FROM table1;
END;

Commands

Independent commands in an SQL file are considered Top-Level Code Units.

A COMMIT statement counts as a single Top-Level Code Unit.

Code Sample

COMMIT;

Package Bodies in Oracle

A package can define multiple elements inside its body. The package body is considered the Top-Level Code Unit because those elements cannot be created individually without creating the entire package body. Elements or code units inside a package body will not count as Top-Level Code Units.

The following code will be reported as a single CREATE PACKAGE BODY Code Unit.

Code Sample

CREATE PACKAGE package_body1 IS
    FUNCTION function1
    RETURN VARCHAR
    IS
    BEGIN
        RETURN 'HELLO'';
    END;
END;

Teradata Script files

Teradata Script files like BTEQ or TPUMP are executed as standalone code units. Because of this, the entire file is considered a single Top-Level Code Unit. Other possible code units inside these files will not count as Top-Level Code Units.

The following BTEQ script file will be reported as a single BTEQ Top-Level Code Unit.

Code Sample

script_file.btq

.LOGON e/fml,notebook
.COMPILE FILE = example.spl;
COMMIT;
CALL samplesp1 (8888, pAmount);
.LOGOFF

Transact SQL batches with GOTO

Each statement of Transact-SQL can be executed independently. In most cases, each of these statements is considered a Top-Level Code Unit. However, when there is a batch that contains a GOTO statement to a label inside the same batch, the statements of the batch cannot be executed independently without ensuring that they work properly. Because of this, statements that are in a batch with a GOTO statement will not count as Top-Level Code Units, only the batch.

The following code example will be reported as a single GOTO/LABEL Code Unit:

Code Sample

DECLARE @Counter int;  
SET @Counter = 1;  
WHILE @Counter < 10  
BEGIN   
    SELECT @Counter  
    SET @Counter = @Counter + 1  
    IF @Counter = 4 GOTO Branch_One
    IF @Counter = 5 GOTO Branch_Two  
END  
Branch_One:  
    SELECT 'Jumping To Branch One.'  
    GOTO Branch_Three;
Branch_Two:  
    SELECT 'Jumping To Branch Two.'  
Branch_Three:  
    SELECT 'Jumping To Branch Three.';
GO

How is the Code Unit methodology represented in other reports?

The Code Unit methodology is also represented in other reports. This section explains how these values are shown or are related to other reports.

Issues Report

Issues Report

Each row of the Issues Report has some information about the Code Unit that is being impacted by the issue. The columns related to Code Units are the following:

• Code Unit Database: This is the Database of the Top-Level Code Unit where the issue was found. It only applies to Code Units that are objects.

• Code Unit Schema: This is the Schema of the Top-Level Code Unit where the issue was found. It only applies to Code Units that are objects.

• Code Unit Package: This is the Package of the Top-Level Code Unit where the issue was found. It only applies to Code Units that are objects.

• Code Unit Name: This is the name Top-Level Code Unit where the issue was found. It only applies to named Code Units like objects. This name is not qualified by database, schema, or package.

• Code Unit ID: This is the ID of the Top-Level Code Unit where the issue was found. This name has the name qualified and will add a number for code units with repeated names.

• Code Unit: This is the type of the Top-Level Code Unit where the issue was found.

• Code Unit Size: This is the size of the Top-Level Code Unit where the issue was found.

Object References Report and Missing Objects Report

Object References ReportMissing Objects Report

Each row of the Object References report has information about the Top-Level Code Unit that was referencing another element. These referenced elements may not be Top-Level, so those other values may not be included in the Top-Level Code Units report.

Similarly to the Object References report, the Missing Objects Report has information about the Top-Level Code Unit that was referencing an element that could not be found in the code.

• Caller Code Unit: This is the type of the Top-Level Code Unit that is referencing another element.

• Caller Code Unit Database: This is the database of the Top-Level Code Unit that is referencing another element.

• Caller Code Unit Schema: This is the schema of the Top-Level Code Unit that is referencing another element.

• Caller Code Unit Name: This is the name of the Top-Level Code Unit that is referencing another element.

• Caller Code Unit Full Name: This is the full name of the Top-Level Code Unit that is referencing another element.

Information in the Top-Level Code Units Report

Column	Description
Partition Key	The unique identifier of the conversion.
File Type	The type of the file that the Code Unit is in. (SQL, BTEQ, etc...)
Category	The broader class or type each code unit belongs to.
Code Unit	The type of Code Unit that this element belongs to.
Code Unit Name	The name of the Code Unit if it has one such as tables or procedures. It will be N/A for elements without a name like DMLs.
File Name	The name of the file in which the object is located. Uses the relative path starting from the input directory.
Line Number	The line number inside the file where the code unit is located.
Lines of Code	The total lines of code that the code unit has.
EWI Count	The amount of EWIs found within the code unit. You can learn more about EWIs here.
FDM Count	The amount of FDMs found within the code unit. You can learn more about FDMs here.
PRF Count	The amount of PRFs found within the code unit. You can learn more about PRFs here.
Highest EWI Severity	The highest EWI severity found within the code unit. The severity order is the following: N/A (when there are not any EWIs) Low Medium High Critical
UDFs Used	The names of all the user defined functions found within the code unit. The name of the UDFs used are separated by a pipe if there is more than one.
EWI	The codes of all the EWIs found within the code unit. These codes are separated by pipes and do not include repeated codes.
FDM	The codes of all the FDMs found within the code unit. These codes are separated by pipes and do not include repeated codes.
PRF	The codes of all the PRFs found within the code unit. These codes are separated by pipes and do not include repeated codes.
Conversion Status	The final status of the conversion of the code unit. The possible conversion statuses are: NotSupported: When the Code Unit has a 0% conversion rate. Partial: When the conversion rate of the Code Unit is between 0% and 100%. Success: When the Code Unit conversion rate is 100%.
LoC Conversion Percentage	The conversion percentage is based on Lines of Code. A single line of code may have supported and unsupported fragments depending on how the input code was formatted. In these cases, the entire line is considered as not supported.

Example

Assume that the following CREATE TABLE in ORACLE SQL is located in its file called table_example.sql.

Oracle Input

IN -> Oracle_01.sql

CREATE TABLE my_table (
  my_column DATE DEFAULT TO_DATE(CURRENT_DATE, 'J'),
  NOT A VALID COLUMN
);

Snowflake Output

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE my_table (
   my_column TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/ DEFAULT PUBLIC.JULIAN_TO_GREGORIAN_DATE_UDF(CURRENT_DATE(), 'J')
--                                                                                                                                                                          ,
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '3' COLUMN '3' OF THE SOURCE CODE STARTING AT 'NOT'. EXPECTED 'Column Definition' GRAMMAR. LAST MATCHING TOKEN WAS ',' ON LINE '2' COLUMN '52'. FAILED TOKEN WAS 'NOT' ON LINE '3' COLUMN '3'. CODE '15'. **
--  NOT A VALID COLUMN
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
 ;

The Top-Level Code Units report will have a single entry of the previously shown table.

Here are all the values that would be reported in the entry of this CREATE TABLE statement:

• The Partition Key value will depend on migration so the value here will vary.

• The File Type will be SQL because it was migrated on a file with the .sql extension.

• The Category will be TABLE because the CREATE TABLE statement is part of the TABLE Code Unit Category.

• The Code Unit itself will be CREATE TABLE.

• The File Name where this code unit was found would be table_example.sql.

• Assuming that the CREATE TABLE statement is at the beginning of the file, the Line Number will be 1.

• The Lines of Code number would be 4.

• The EWI Count column will report 1 because the output code has one parsing EWI.

• The FDM Count column will report 1 because the output code has an FDM issue related to data types.

• The PRF Count will report 0 because there are no PRF issues present in the output code.

• The Highest EWI Severity in this case would be "Critical" because this is the severity of the parsing EWI of the example. The other one has a "Low" severity.

• The UDFs Used column will be JULIAN_TO_GREGORIAN_DATE_UDF because this custom User Defined Function was added to convert the TO_DATE function of the input code.

• The EWI column will report "SSC-EWI-0001" because this was one of EWIs added in the output code.

• The FDM column will report "SSC-FDM-OR0042" because this was one of FDMs added in the output code.

• The PRF column will report "N/A" because there are no PRF issues present in the output code.

• The Conversion Status will be "Partial" because only some fragments of this Code Unit were able to be migrated without EWIs.

• The LoC Conversion Percentage is 50% because out of 4 lines, only 2 were converted successfully.