Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ Teradata Syntax Migration/ Migrating Views
Updated on 2024-12-09 GMT+08:00
View PDF
Share

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

1
2
3
 
CREATE VIEW DP_STEDW.MY_PARAM 
AS 
SELECT RUNDATE FROM  DP_STEDW.DATE_TBL   WHERE   dummy = 1;

Output:

1
2
3
4
5
 
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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
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

1
2
3
4
5
6
7
 
REPLACE VIEW DP_STEDW.MY_PARAM AS SELECT
          RUNDATE
     FROM
          DP_STEDW.DATE_TBL
     WHERE
          dummy = 1
;

Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE
OR REPLACE VIEW DP_STEDW.MY_PARAM AS (
     SELECT
               RUNDATE
          FROM
               DP_STEDW.DATE_TBL
          WHERE
               dummy = 1
)
;

Input - REPLACE RECURSIVE VIEW

1
2
3
4
5
6
 
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:

1
2
3
4
5
6
7
8
 
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

1
2
3
4
5
6
 
CV  mgr15 AS SEL *
FROM
    employee
WHERE
    manager_id = 15 WITH CHECK OPTION
;

Output (tdMigrateVIEWCHECKOPTION=True)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE
     OR REPLACE VIEW mgr15 AS (
          SELECT
                    *
               FROM
                    employee
               WHERE
                    manager_id = 15 /*WITH CHECK OPTION */
     )
;

Output (tdMigrateVIEWCHECKOPTION=False)

1
2
3
4
5
6
 
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

1
2
3
4
5
6
7
8
 
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:

1
2
3
4
5
6
7
8
 
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; /