Migrating Views¶
CREATE VIEW (short key CV) is used together with SELECT to create a view.
The keyword VIEW is supported by both Teradata and GaussDB(DWS), but the SELECT statements are enclosed in double quotation marks during the migration. For details, see the following figures.
Use the tdMigrateVIEWCHECKOPTIO.... configuration parameter to configure migration of views containing the WITH CHECK OPTION keyword. If tdmigrateVIEWCHECKOPTION is set to false, the tool will skip migration of the query and will log a message.
If the CREATE VIEW includes the LOCK keyword, then the VIEW query will be migrated based on the value of tdMigrateLOCKoption.
Input - CREATE VIEW
CREATE VIEW DP_STEDW.MY_PARAM
AS
SELECT RUNDATE FROM DP_STEDW.DATE_TBL WHERE dummy = 1;
Output:
CREATE OR REPLACE VIEW DP_STEDW.MY_PARAM
AS
SELECT RUNDATE
FROM DP_STEDW.DATE_TBL
WHERE dummy = 1;
Input: CREATE VIEW WITH FORCE KEYWORD
CREATE
OR REPLACE FORCE VIEW IS2010_APP_INFO (
APP_ID, APP_SHORTNAME, APP_CHNAME,
APP_ENNAME
) AS
select
t.app_id,
t.app_shortname,
t.app_chname,
t.app_enname
from
newdrms.seas_app_info t
WHERE
t.app_status <> '2';
Output:
CREATE
OR REPLACE
/*FORCE*/
VIEW IS2010_APP_INFO (
APP_ID,
APP_SHORTNAME,
APP_CHNAME,
APP_ENNAME ) AS
SELECT
t.app_id,
t.app_shortname,
t.app_chname,
t.app_enname
FROM
newdrms.seas_app_info t
WHERE
t.app_status <> '2';
REPLACE VIEW¶
In Teradata, the REPLACE VIEW statement is used to create a view or rebuild the existing view. DSC converts the REPLACE VIEW statement to the CREATE OR REPLACE VIEW statement that is compatible with GaussDB(DWS).
Input - REPLACE VIEW
REPLACE VIEW DP_STEDW.MY_PARAM AS SELECT
RUNDATE
FROM
DP_STEDW.DATE_TBL
WHERE
dummy = 1
;
Output:
CREATE
OR REPLACE VIEW DP_STEDW.MY_PARAM AS (
SELECT
RUNDATE
FROM
DP_STEDW.DATE_TBL
WHERE
dummy = 1
)
;
Input - REPLACE RECURSIVE VIEW
Replace RECURSIVE VIEW reachable_from (
emp_id,emp_name,DEPTH)
AS (
SELECT root.emp_id,root.emp_name,0 AS DEPTH
FROM emp AS root
WHERE root.mgr_id IS NULL);
Output:
CREATE OR REPLACE VIEW reachable_from AS (
WITH RECURSIVE reachable_from (
emp_id,emp_name,DEPTH)
AS (
SELECT root.emp_id,root.emp_name,0 AS DEPTH
FROM emp AS root
WHERE root.mgr_id IS NULL
) SELECT * FROM reachable_from);
REPLACE FUNCTION¶
Input:
REPLACE FUNCTION up_load1.RPT_016_BUS_DATE()
RETURNS DATE
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN DATE'2017-08-22';
Output:
CREATE OR REPLACE FUNCTION up_load1.RPT_016_BUS_DATE()
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
SECURITY DEFINER
AS
$$
SELECT CAST('2017-08-20' AS DATE)
$$
;
CHECK OPTION¶
Use the tdMigrateVIEWCHECKOPTIO.... configuration parameter to configure migration of views containing the WITH CHECK OPTION keyword.
If a view with CHECK OPTION is present in the source, then the CHECK OPTION is commented from the target database.
Input - VIEW with CHECK OPTION
CV mgr15 AS SEL *
FROM
employee
WHERE
manager_id = 15 WITH CHECK OPTION
;
Output (tdMigrateVIEWCHECKOPTION=True)
CREATE
OR REPLACE VIEW mgr15 AS (
SELECT
*
FROM
employee
WHERE
manager_id = 15 /*WITH CHECK OPTION */
)
;
Output (tdMigrateVIEWCHECKOPTION=False)
CV mgr15 AS SEL *
FROM
employee
WHERE
manager_id = 15 WITH CHECK OPTION
;
VIEW WITH RECURSIVE¶
GaussDB(DWS) does not support the Teradata keyword RECURSIVE VIEW. Therefore the keyword is replaced with VIEW WITH RECURSIVE keyword as shown in the following figures.
Figure 1 Input view-CREATE RECURSIVE VIEW¶
Figure 2 Output view¶
VIEW WITH ACCESS LOCK¶
Use the tdMigrateLOCKOption configuration parameter to configure migration of query containing the LOCK keyword. If tdMigrateLOCKOption is set to false, the tool will skip migration of the query and will log a message.
Input - VIEW with ACCESS LOCK
CREATE OR REPLACE VIEW DP_SVMEDW.S_LCR_909_001_LCRLOAN
AS
LOCK TABLE DP_STEDW.S_LCR_909_001_LCRLOAN FOR ACCESS FOR ACCESS
( SELECT RUN_ID, PRODUCT_ID, CURRENCY
, CASHFLOW, ENTITY, LCR
, TIME_BUCKET, MT, Ctl_Id
, File_Id, Business_Date
FROM DP_STEDW.S_LCR_909_001_LCRLOAN ) ;
Output:
CREATE OR REPLACE VIEW DP_SVMEDW.S_LCR_909_001_LCRLOAN
AS
/* LOCK TABLE DP_STEDW.S_LCR_909_001_LCRLOAN FOR ACCESS */
( SELECT RUN_ID, PRODUCT_ID, CURRENCY
, CASHFLOW, ENTITY, LCR
, TIME_BUCKET, MT, Ctl_Id
, File_Id, Business_Date
FROM DP_STEDW.S_LCR_909_001_LCRLOAN ) ;
dbc.columnsV
Input:
SELECT A.ColumnName
AS V_COLS ,A.columnname || ' ' ||CASE WHEN columnType in ('CF','CV') THEN CASE WHEN columnType='CV' THEN 'VAR' ELSE '' END||'CHAR('||TRIM(columnlength (INT))|| ') CHARACTER SET LATIN'|| CASE WHEN UpperCaseFlag='N' THEN ' NOT' ELSE ''
END || ' CASESPECIFIC'
WHEN columnType='DA' THEN 'DATE'
WHEN columnType='TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits)||')' WHEN columnType='AT' THEN 'TIME('|| TRIM(DecimalFractionalDigits)||')' WHEN columnType='I' THEN 'INTEGER'
WHEN columnType='I1' THEN 'BYTEINT'
WHEN columnType='I2' THEN 'SMALLINT'
WHEN columnType='I8' THEN 'BIGINT'
WHEN columnType='D' THEN 'DECIMAL('||TRIM(DecimalTotalDigits)||','||TRIM(DecimalFractionalDigits)||')' ELSE 'Unknown'
END||CASE WHEN Nullable='Y'
THEN '' ELSE ' NOT NULL' END||'0A'XC
AS V_ColT
,D.ColumnName
AS V_PICol -- Obtain the primary index of the target table.
FROM dbc.columnsV A LEFT JOIN dbc.IndicesV B ON A.columnName = B.columnName AND B.IndexType IN ('Q','P') AND B.DatabaseName = '${V_TDDLDB}' AND B.tablename='${TARGET_TABLE}' WHERE A.databasename='${V_TDDLDB}' AND A.tablename = '${TARGET_TABLE}' AND A.columnname NOT IN ( 'ETL_JOB_NAME' ,'ETL_TX_DATE' ,
'ETL_PROC_DATE'
)ORDER BY A.columnid;
Output:
D DECLARE lv_mig_V_COLS TEXT; lv_mig_V_ColT TEXT; lv_mig_V_PICol TEXT; BEGIN SELECT STRING_AGG(A.ColumnName, ',') , STRING_AGG(A.columnname || ' ' ||CASE WHEN columnType in ('CF','CV') THEN CASE WHEN columnType='CV' THEN 'VAR' ELSE '' END||'CHAR('||TRIM(mig_td_ext.mig_fn_castasint(columnlength))|| ') /*CHARACTER SET LATIN*/'|| CASE WHEN UpperCaseFlag='N' THEN ' NOT' ELSE '' END || ' /*CASESPECIFIC*/' WHEN columnType='DA' THEN 'DATE' WHEN columnType='TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits)||')' WHEN columnType='AT' THEN 'TIME('|| TRIM(DecimalFractionalDigits)||')' WHEN columnType='I' THEN 'INTEGER' WHEN columnType='I1' THEN 'BYTEINT' WHEN columnType='I2' THEN 'SMALLINT' WHEN columnType='I8' THEN 'BIGINT' WHEN columnType='D' THEN 'DECIMAL('||TRIM(DecimalTotalDigits)||','||TRIM(DecimalFractionalDigits)||')' ELSE 'Unknown' END||CASE WHEN Nullable='Y' THEN '' ELSE ' NOT NULL' END||E'\x0A', ',') , STRING_AGG(B.ColumnName, ',') INTO lv_mig_V_COLS, lv_mig_V_ColT, lv_mig_V_PICol FROM mig_td_ext.vw_td_dbc_columnsV A LEFT JOIN mig_td_ext.vw_td_dbc_IndicesV B ON A.columnName = B.columnName AND B.IndexType IN ('Q','P') AND B.DatabaseName = 'public' AND B.tablename='emp2' WHERE A.databasename='public' AND A.tablename = 'emp2'; -- ORDER BY A.columnid; END; /