Topic
3 replies Latest Post - ‏2013-10-25T18:23:39Z by krmilligan
Ajay Kulkarni
Ajay Kulkarni
19 Posts
ACCEPTED ANSWER

Pinned topic Views Options in Iseries Navigator

‏2013-10-25T01:02:09Z |

 

Hello DB2 for i experts.

Our IBM i shop has many (really many) SQL views on the DB2 for i, and we wanted to script down dropping of all these views and recreating them so that we can use them whenever we upgrade our ERP release on IBM i.

 

We thought we could do a "right click" on the "VIEWS" folder under "Schemas" folder in the navigator, but when we did that it showed DDS LF and views and so do a mass "generate sql" wont help. Is this normal that the VIEWS folder in navigator shows DDS LF and SQL View.

Is there any quick way to get the SQL scripts for all the views (SQL views)?

 

Regards

 

 

 
  • B.Hauser
    B.Hauser
    248 Posts
    ACCEPTED ANSWER

    Re: Views Options in Iseries Navigator

    ‏2013-10-25T05:45:10Z  in response to Ajay Kulkarni

    Have a look at the catalog views SYSVIEWS and SYSVIEWDEP.

    Just save the information temporarily into a table. Read the SQL-Statements for the view definition from the temporary file, build a dynamic SQL statement and recreate the view again.

    This program can be written in either an HLL or with pure SQL.

    Birgitta

     

    • Ajay Kulkarni
      Ajay Kulkarni
      19 Posts
      ACCEPTED ANSWER

      Re: Views Options in Iseries Navigator

      ‏2013-10-25T16:19:53Z  in response to B.Hauser

      Thanks Birgitta, 

      We did think if these views but we were missing LABLE on and GRANT ALTER REFERENCE and that is  the reason we thought the SQL generate in navigator gave us exactly what we wanted.Do you any other idea or a creative way of faking the navigator, we tried the filter option but since we really do not follow a naming convention for views we can not use the Filter option in generate SQL in the navigator.

      Even though DDS LF is kind of a view, but this kind of confuses me that when you click on "index" tab on navigator you only get SQL index (IX) where as you click on "views" you get DDS LF and SQL VW.

       

       

       

    • krmilligan
      krmilligan
      441 Posts
      ACCEPTED ANSWER

      Re: Views Options in Iseries Navigator

      ‏2013-10-25T18:23:39Z  in response to B.Hauser

      A non-keyed logical file is equivalent to an SQL view and a keyed logical file is really equivalent to an SQL View & Index, so that's why the LF objects show up in this folder.    If the LFs follow a naming convention, you might be able to Customize the Views view to exclude the LFs from being listed.

      You could always write your own Generate Views program by using the (QSQGNDDL) API which is interface used by IBM i Navigator for the Generate SQL task. 

      I guess i don't understand why you need to retrieve the source for your SQL views from DB2.  I'd highly recommend storing your CREATE VIEW statement source in a change management system like you would with program source code.