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;