I work on something and I need to check some views. I use this code:
SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_T%'
To check if it works I:
- added a table,
- created a view of "SELECT * FROM TEMP_T",
- altered the table by adding a new column,
- and check the status again.
From what I understand (did a bit of research) this should invalidate the view, but it didn't. I also removed the initial column of the table (after creating the view), but the view still appears to be valid. In the end I even droped the table, but the view still is in STATUS='N'.
Can you guys help me understand why is this happening? I assume it's a bug in DB2, but as I'm new to DB I'd like to hear some other opinions.
Here is the log:
CREATE TABLE DB2INST1.TEMP_T ( COL_A CHAR(5) )
DB20000I The SQL command completed successfully.
CREATE VIEW DB2INST1.TEMP_V AS SELECT * FROM DB2INST1.TEMP_T
DB20000I The SQL command completed successfully.
SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_%'
TABLE SCHEMA STATUS TYPE
-------------------------------- -------------------------------- ------ ----
TEMP_T DB2INST1 N T
TEMP_V DB2INST1 N V
2 record(s) selected.
ALTER TABLE DB2INST1.TEMP_T ADD COLUMN COL_B VARCHAR(20)
DB20000I The SQL command completed successfully.
ALTER TABLE DB2INST1.TEMP_T DROP COLUMN COL_A
DB20000I The SQL command completed successfully.
REORG TABLE DB2INST1.TEMP_T
DB20000I The REORG command completed successfully.
SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_%'
TABLE SCHEMA STATUS TYPE
-------------------------------- -------------------------------- ------ ----
TEMP_T DB2INST1 N T
TEMP_V DB2INST1 N V
2 record(s) selected.
DROP TABLE DB2INST1.TEMP_T
DB20000I The SQL command completed successfully.
SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_%'
TABLE SCHEMA STATUS TYPE
-------------------------------- -------------------------------- ------ ----
TEMP_V DB2INST1 N V
1 record(s) selected.
Best Answer
As for the view being valid after dropping a column: The view is defined as
SELECT *
so the view is still valid.Regarding the validity of the view after dropping the table just use field
VALID
fromSYSCAT.VIEWS
, see below.