We recently went through our databases and updated some kernel fields lengths. This we now find out makes basically all our views invalid. How do we force recompiling of all our views?

asked 26 Mar '15, 02:00

LoganTennis's gravatar image

LoganTennis
1217712
accept rate: 100%

What SQL Anywhere version do you use? AFAIK, starting with v10, the database engine tracks view dependencies and therefore should automatically recompile all dependent views -cf. that doc topic for v12:

Dependencies and schema-altering changes

For older versions, AFAIK there was no RECOMPILE clause in the ALTER VIEW statement, so you would have to use ALTER VIEW ... AS with the full select statement.

(26 Mar '15, 04:47) Volker Barth

The dbunload utility uses sa_recompile_views. While it is not officially recommended to use this system procedure outside of the reload.sql script it may be helpful to test your changes and to find views that were negatively impacted by your schema changes.

permanent link

answered 26 Mar '15, 11:03

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.2k33106
accept rate: 32%

Hi Logan, in my application I put a piece of code where i found all invalid view and then put them to enable doing 2 or 3 test to consider nested view. Here an extract:

TO FIND INVALID VIEW:

select table_name from SYS.SYSTABLE, SYS.SYSOBJECT where SYS.SYSTABLE.OBJECT_ID = SYS.SYSOBJECT.OBJECT_ID and SYS.SYSOBJECT.STATUS = 2 and table_type = 'VIEW' and table_name > '' ORDER BY table_name

THEN : ROUND 1 FOR EACH ls_sql = "ALTER VIEW dba." + ls_table + " ENABLE " execute immediate :ls_sql

ROUND 2 FOR EACH ls_sql = "ALTER VIEW dba." + ls_table + " ENABLE " execute immediate :ls_sql

ROUND 3 FOR EACH ls_sql = "ALTER VIEW dba." + ls_table + " ENABLE " execute immediate :ls_sql

permanent link

answered 26 Mar '15, 05:31

Giorgio%20Papagno's gravatar image

Giorgio Papagno
3062410
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25

question asked: 26 Mar '15, 02:00

question was seen: 314 times

last updated: 26 Mar '15, 11:03