Solved

How to drop invalid views in DB2?

Posted on 2014-01-13
2
1,101 Views
Last Modified: 2014-01-18
Can anyone assist with situation when DB2 view has "valid=N", so after issue 'drop' command?
-
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "My_view.Name" is an undefined name.  SQLSTATE=42704.
Looking for the way that doesn't require recreate parent to this table.
Thank you
0
Comment
Question by:sevior
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 250 total points
ID: 39777886
Hi sevior,

Breaking this down:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:


Just informative.  Nothing to read there.  :)


SQL0204N  "My_view.Name" is an undefined name.  SQLSTATE=42704.

In this message, "My_view" is actually the schema name (though case should not be an issue) and "Name" is the view name.  It sounds like you're expecting something else.


Kent
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 250 total points
ID: 39790510
Hi!

This command will list all views that are invalid
db2 "select viewschema.viewname from syscat.views where valid = 'N'"

This command will drop all views that are invalid
db2 -x "select 'db2 -v drop view '|| rtrim(viewschema) || '.\' || '\"' || rtrim(viewname)|| '\' || '\"' ||'' from syscat.views where valid = 'N'"

Modify these commands to your needs (if you want to say drop a specific view ).

Regards,
    Tomas Helgi
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question