Appendix

Appendix A: Data Type Mapping

The query below counts all data types used in Teradata (grouped by the database) and shows you the corresponding data type in Exasol. With this query, you can easily find data types which are not supported early in your migration journey. 

SELECT
t.DatabaseName, ColumnType,
CASE
WHEN ColumnType = 'A1' THEN 'ARRAY'
WHEN ColumnType = 'AN' THEN 'ARRAY'
WHEN ColumnType = 'AT' THEN 'TIME'
WHEN ColumnType = 'BF' THEN 'BYTE'
WHEN ColumnType = 'BO' THEN 'BLOB'
WHEN ColumnType = 'BV' THEN 'VARBYTE'
WHEN ColumnType = 'CF' THEN 'CHAR'
WHEN ColumnType = 'CO' THEN 'CLOB'
WHEN ColumnType = 'CV' THEN 'VARCHAR'
WHEN ColumnType = 'D' THEN 'DECIMAL'
WHEN ColumnType = 'DA' THEN 'DATE'
WHEN ColumnType = 'DH' THEN 'INTERVAL DAY TO HOUR'
WHEN ColumnType = 'DM' THEN 'INTERVAL DAY TO MINUTE'
WHEN ColumnType = 'DS' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'DT' THEN 'DATASET'
WHEN ColumnType = 'DY' THEN 'INTERVAL DAY'
WHEN ColumnType = 'F' THEN 'FLOAT'
WHEN ColumnType = 'HM' THEN 'INTERVAL HOUR TO MINUTE'
WHEN ColumnType = 'HR' THEN 'INTERVAL HOUR'
WHEN ColumnType = 'HS' THEN 'INTERVAL HOUR TO SECOND'
WHEN ColumnType = 'I1' THEN 'BYTEINT'
WHEN ColumnType = 'I2' THEN 'SMALLINT'
WHEN ColumnType = 'I8' THEN 'BIGINT'
WHEN ColumnType = 'I' THEN 'INTEGER'
WHEN ColumnType = 'JN' THEN 'JSON'
WHEN ColumnType = 'MI' THEN 'INTERVAL MINUTE'
WHEN ColumnType = 'MO' THEN 'INTERVAL MONTH'
WHEN ColumnType = 'MS' THEN 'INTERVAL MINUTE TO SECOND'
WHEN ColumnType = 'N' THEN 'NUMBER'
WHEN ColumnType = 'PD' THEN 'PERIOD(DATE)'
WHEN ColumnType = 'PM' THEN 'PERIOD(TIMESTAMP WITH TIME ZONE)'
WHEN ColumnType = 'PS' THEN 'PERIOD(TIMESTAMP)'
WHEN ColumnType = 'PT' THEN 'PERIOD(TIME)'
WHEN ColumnType = 'PZ' THEN 'PERIOD(TIME WITH TIME ZONE)'
WHEN ColumnType = 'SC' THEN 'INTERVAL SECOND'
WHEN ColumnType = 'SZ' THEN 'TIMESTAMP WITH TIME ZONE'
WHEN ColumnType = 'TS' THEN 'TIMESTAMP'
WHEN ColumnType = 'TZ' THEN 'TIME WITH TIME ZONE'
WHEN ColumnType = 'UT' THEN 'UDT'
WHEN ColumnType = 'XM' THEN 'XML'
WHEN ColumnType = 'YM' THEN 'INTERVAL YEAR TO MONTH'
WHEN ColumnType = 'YR' THEN 'INTERVAL YEAR'
END AS Teradata_Data_Type,
CASE
WHEN ColumnType = 'A1' THEN 'VARCHAR(64000)'
WHEN ColumnType = 'AN' THEN 'VARCHAR(64000)'
WHEN ColumnType = 'AT' THEN 'TIMESTAMP'
WHEN ColumnType = 'BF' THEN 'NOT SUPPORTED'
WHEN ColumnType = 'BO' THEN 'NOT SUPPORTED'
WHEN ColumnType = 'BV' THEN 'NOT SUPPORTED'
WHEN ColumnType = 'CF' THEN 'CHAR'
WHEN ColumnType = 'CO' THEN 'VARCHAR(2000000)'
WHEN ColumnType = 'CV' THEN 'VARCHAR'
WHEN ColumnType = 'D' THEN 'DECIMAL'
WHEN ColumnType = 'DA' THEN 'DATE'
WHEN ColumnType = 'DH' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'DM' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'DS' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'DT' THEN 'NOT SUPPORTED'
WHEN ColumnType = 'DY' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'F' THEN 'DOUBLE PRECISION'
WHEN ColumnType = 'HM' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'HR' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'HS' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'I1' THEN 'DECIMAL(9)'
WHEN ColumnType = 'I2' THEN 'DECIMAL(9)'
WHEN ColumnType = 'I8' THEN 'DECIMAL(19)'
WHEN ColumnType = 'I' THEN 'DECIMAL(10)'
WHEN ColumnType = 'JN' THEN 'VARCHAR(2000000)'
WHEN ColumnType = 'MI' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'MO' THEN 'INTERVAL YEAR  TO MONTH'
WHEN ColumnType = 'MS' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'N' THEN 'DOUBLE PRECISION'
WHEN ColumnType = 'PD' THEN 'DATE,DATE'
WHEN ColumnType = 'PM' THEN 'TIMESTAMP,TIMESTAMP'
WHEN ColumnType = 'PS' THEN 'TIMESTAMP,TIMESTAMP'
WHEN ColumnType = 'PT' THEN 'TIMESTAMP,TIMESTAMP'
WHEN ColumnType = 'PZ' THEN 'TIMESTAMP,TIMESTAMP'
WHEN ColumnType = 'SC' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'SZ' THEN 'TIMESTAMP'
WHEN ColumnType = 'TS' THEN 'TIMESTAMP'
WHEN ColumnType = 'TZ' THEN 'TIMETAMP'
WHEN ColumnType = 'UT' THEN 'Simple distinct UDTs can be transformed'
WHEN ColumnType = 'XM' THEN 'VARCHAR(2000000)'
WHEN ColumnType = 'YM' THEN 'INTERVAL YEAR TO MONTH'
WHEN ColumnType = 'YR' THEN 'INTERVAL YEAR TO MONTH'
END AS Exasol_Data_Type,
COUNT(*) AS Data_Type_Count
FROM DBC.ColumnsV c
JOIN DBC.TablesV t on 
c.databaseName=t.DatabaseName AND 
c.TableName=t.TableName AND 
TableKind='T'
WHERE 
--t.DatabaseName  IN ('AdventureWorksDW')  --filter for a specific database
--and 
t.DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
    'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
    'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB', 
    'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB',  'TDStats',
    'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
    'tdwm',  'SQLJ', 'TD_SYSFNLIB',  'SYSSPATIAL')
GROUP BY 1,2
ORDER BY 1,2;

The example output of the script as shown below:

Appendix B: Extract View Texts from Teradata

With the following query, you can extract all view texts from Teradata and calculate the length of the view text. The length of the view is a simple indicator to estimate the complexity of each view and, therefore, the effort required for migration.

SELECT  DataBaseName,
        TableName as ViewName,
        CreatorName,
        CreateTimeStamp as Created,
        RequestText as Definition,
        length(RequestText) View_length
FROM    DBC.TablesV
WHERE   TableKind = 'V'
AND DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
    'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
    'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB', 
    'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB',  'TDStats',
    'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
    'tdwm',  'SQLJ', 'TD_SYSFNLIB',  'SYSSPATIAL')
ORDER BY    DatabaseName,
            TableName;

Appendix C: Overview of Teradata Functions

The query below returns all User Defined Functions and metadata of the Teradata database:

SELECT  C.DatabaseName,
        FunctionName,
        NumParameters,
        ParameterDataTypes,
        CASE SrcFileLanguage
            WHEN 'S' THEN 'SQL'
            WHEN 'C' THEN 'C'
            WHEN 'P' THEN 'C++'
            WHEN 'J' THEN 'JAVA'
            WHEN 'A' THEN 'SAS'
            END AS FunctionLanguage,
        CASE FunctionType
            WHEN 'A' THEN 'Aggregate'
            WHEN 'B' THEN 'Aggregate and statistical'
            WHEN 'C' THEN 'Contract function'
            WHEN 'F' THEN 'Scalar'
            WHEN 'H' THEN 'User-defined method'
            WHEN 'I' THEN 'Internal type method'
            WHEN 'L' THEN 'Table operator'
            WHEN 'R' THEN 'Table'
            WHEN 'S' THEN 'Statistical'
            END,
        ColumnType AS ReturnType
FROM    DBC.FunctionsV T
LEFT JOIN   DBC.ColumnsV C
ON  C.DatabaseName = T.DatabaseName
AND C.TableName = T.SpecificName
AND ColumnName = 'RETURN0'
WHERE   T.DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
        'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
        'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB',
        'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 'TDStats',
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
ORDER BY    C.DatabaseName,
            FunctionName;  

Appendix D: Overview of Teradata Stored Procedures

SELECT  T.DatabaseName,
        T.TableName AS ProcedureName,
        CASE T.TableKind
            WHEN 'P' THEN 'Stored Procedure'
            WHEN 'E' THEN 'External Stored Procedure'
            end as ProcedureKind,
        T.CreateTimeStamp,
        TRIM(TRAILING ',' 
            FROM (XMLAGG(CASE SPParameterType
                        WHEN 'I' THEN 'IN '
                        WHEN 'O' THEN 'OUT '
                        WHEN 'B' THEN 'IN/OUT ' END
                    || C.ColumnName || ' '
                    || ColumnType || ','))(varchar(255))) as Parameters
FROM    DBC.TablesV T
LEFT JOIN   DBC.ColumnsV C
ON C.DatabaseName = T.DatabaseName
AND C.TableName = T.TableName
WHERE T.TableKind in ('P', 'E')
AND T.DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
    'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
    'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB',
    'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 'TDStats',
    'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
    'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
GROUP BY    T.DatabaseName,
            T.TableName,
            T.CreateTimeStamp,
            T.TableKind
ORDER BY    T.DatabaseName,
            ProcedureName;  

Appendix E: Overview of Users and Roles

SELECT  UserName,
        CreatorName,
        DefaultDatabase,
        CreateTimeStamp,
        TRIM(TRAILING ',' FROM
            XMLAGG(R.RoleName || ',')(VARCHAR(255))) AS Roles
FROM DBC.UsersV U
LEFT JOIN DBC.RoleMembersV R
ON  U.Username = R.Grantee
WHERE   U.Username NOT IN ('TDPUSER', 'Crashdumps', 'tdwm', 'DBC',
        'LockLogShredder', 'TDMaps', 'Sys_Calendar', 'SysAdmin', 
        'SystemFe', 'External_AP')
GROUP BY    UserName,
            CreatorName,
            DefaultDatabase,
            CreateTimeStamp
ORDER BY U.Username;