1

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.
2

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 from SYSCAT.VIEWS, see below.

db2 => DROP TABLE DB2INST1.TEMP_T
DB20000I  The SQL command completed successfully.
db2 => SELECT SUBSTR(VIEWNAME,1,32) AS TABLE, VALID FROM SYSCAT.VIEWS WHERE VIEWNAME='TEMP_V'

TABLE                            VALID
-------------------------------- -----
TEMP_V                           N

  1 record(s) selected.

db2 => CREATE TABLE DB2INST1.TEMP_T ( COL_A  CHAR(5) )
DB20000I  The SQL command completed successfully.
db2 => SELECT SUBSTR(VIEWNAME,1,32) AS TABLE, VALID FROM SYSCAT.VIEWS WHERE VIEWNAME='TEMP_V'

TABLE                            VALID
-------------------------------- -----
TEMP_V                           N

  1 record(s) selected.

db2 => SELECT * FROM DB2INST1.TEMP_V

COL_A
-----

  0 record(s) selected.

db2 => SELECT SUBSTR(VIEWNAME,1,32) AS TABLE, VALID FROM SYSCAT.VIEWS WHERE VIEWNAME='TEMP_V'

TABLE                            VALID
-------------------------------- -----
TEMP_V                           Y

  1 record(s) selected.
  • SYSCAT.VIEWS seems to be exactly what I need. Thank you! – Sergiu Tot Jan 28 '16 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.