Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    10

    Unanswered: Validating DB2 views

    1. drop view...
    2. create view ...

    Is this the only way a view can be validated against
    a change in underlying table. (for Db2 8.1)

    Thanks

    nic

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Yes

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2004
    Posts
    448
    correct me if I am wrong.It also done implicitily by database,only if the defination matches the new table defination

    regards,

    mujeeb

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Thanks for pointing out Mujeeb ..

    Looks like, even if the definition of the view is different, if you create a view with the name of an invalid view, it is replaced.

    So, for the original question,

    Just issue a CREATE VIEW Statement, and DB2 will replace the invalid view.


    HTH

    Sathyaram

    [dba01][/dba01]$db2 "create table t1(i int,j int)"
    DB20000I The SQL command completed successfully.
    [dba01][/dba01]$db2 "create view v1 as select i,j from t1"
    DB20000I The SQL command completed successfully.
    [dba01][/dba01]$db2 drop table t1
    DB20000I The SQL command completed successfully.
    [dba01][/dba01]$db2 -x "select viewname,valid from syscat.views where viewname='V1'"
    V1 X
    [dba01][/dba01]$db2 "create table t1(i int,j int)"
    DB20000I The SQL command completed successfully.
    [dba01][/dba01]$db2 "create view v1 as select i from t1"
    SQL0280W View, trigger or summary table "DBA01.V1" has replaced an existing
    inoperative view, trigger or summary table. SQLSTATE=01595
    [dba01][/dba01]$
    [dba01][/dba01]$db2 -x "select viewname,valid from syscat.views where viewname='V1'"
    V1 Y
    [dba01][/dba01]$
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2004
    Posts
    10
    Thank you all.

  6. #6
    Join Date
    Aug 2004
    Posts
    1

    recompiling invalid views

    Hello all,
    Can anybody help me with this scenario.
    We have nested views..A view dependent on another and the nested views
    are around 15-20.

    Thats correct there are many views dependent on each other.
    I made a change to one of the base view and I know the dependency tree too.there are around 15 views dependent on the view I changed.
    when the change was made, all the views in the dependency tree became invalid objects is there any way to recompile (views) automaically like in oracle I know there is a way to do so..How do we do in DB2?

    Thanks in advance..
    lets.
    lets_form_group@yahoo.com

  7. #7
    Join Date
    Aug 2004
    Posts
    1

    recompiling invalid views

    Hello all,
    Can anybody help me with this scenario.
    We have nested views..A view dependent on another and the nested views
    are around 15-20.

    Thats correct there are many views dependent on each other.
    I made a change to one of the base view and I know the dependency tree too.there are around 15 views dependent on the view I changed.
    when the change was made, all the views in the dependency tree became invalid objects is there any way to recompile (views) automaically like in oracle I know there is a way to do so..How do we do in DB2?

    Thanks in advance..
    lets.
    lets_form_group@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •