Topic
  • 8 replies
  • Latest Post - ‏2014-02-26T15:55:19Z by NickLawrence
mkoni
mkoni
39 Posts

Pinned topic SQL Views auto deleted

‏2014-02-24T13:09:58Z |

We have a lot of complex SQL Views for using in WebQuery. Some Views are Views on Views.

If a table (or a view with a view over it) is deleted and recreated, the view is also deleted and I have lost all the SQL statements in the view.

Is there any way to avoid that the view will be deleted if the table is removed ?

(maybe the view will be set to inactive or gives an error message if it will be used).

 

I use only System i Navigator (I can'nt programming under green screen).

  • NickLawrence
    NickLawrence
    69 Posts
    ACCEPTED ANSWER

    Re: SQL Views auto deleted

    ‏2014-02-26T15:55:19Z  
    • mkoni
    • ‏2014-02-26T07:50:58Z

    1) it must not be from IBM, maybe any user here has  a self-written command and can provide me (hope so) ;-)

    2)  yes, it could be that the SQL is too long for QSYS2.SYSVIEWS therefore the NUL value. SYSIBM.VIEWS has the value '*POINTER' in the VIEW_DEFINITION field.

    ISQL cannot display CLOB or DBCLOB values, and so it replaces the data with *POINTER.

    I recommend navigator run sql scripts...ISQL is generally obsolete.  If you must use ISQL, you can cast the column to a shorter data type to look at the data.

  • NickLawrence
    NickLawrence
    69 Posts

    Re: SQL Views auto deleted

    ‏2014-02-24T21:00:57Z  

    In DB2 for i 7.1, the CREATE VIEW statement includes an "OR REPLACE" clause that you can use to replace a view. The privileges that were granted to the view are not affected, and the views built over this view are recreated (if possible).

    http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzhcview.htm

     

    "CREATE OR REPLACE TABLE" is not currently supported, although you could do an ALTER TABLE to modify an existing table without dropping all of the views and indexes.

    Updated on 2014-02-24T21:03:17Z at 2014-02-24T21:03:17Z by NickLawrence
  • mkoni
    mkoni
    39 Posts

    Re: SQL Views auto deleted

    ‏2014-02-25T10:08:31Z  

    In DB2 for i 7.1, the CREATE VIEW statement includes an "OR REPLACE" clause that you can use to replace a view. The privileges that were granted to the view are not affected, and the views built over this view are recreated (if possible).

    http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzhcview.htm

     

    "CREATE OR REPLACE TABLE" is not currently supported, although you could do an ALTER TABLE to modify an existing table without dropping all of the views and indexes.

    nick, thx for your answer.

    we create a lot of views to build a mini datawarehouse in webquery. during development it is sometimes necessary to delete a view or table and modify and recreate it. at this time all corresponding views were also deleted, I hoped I can avoid this in any way. I assume you mean I cann't do this.

    But I can use the generate SQL in navigator - is there an existing CL Command to generate SQL's from views or tables ? A CL Command that is ready to use and not for self programming using the api, because I've no experience in programming on i Series?

  • B.Hauser
    B.Hauser
    281 Posts

    Re: SQL Views auto deleted

    ‏2014-02-25T14:29:27Z  
    • mkoni
    • ‏2014-02-25T10:08:31Z

    nick, thx for your answer.

    we create a lot of views to build a mini datawarehouse in webquery. during development it is sometimes necessary to delete a view or table and modify and recreate it. at this time all corresponding views were also deleted, I hoped I can avoid this in any way. I assume you mean I cann't do this.

    But I can use the generate SQL in navigator - is there an existing CL Command to generate SQL's from views or tables ? A CL Command that is ready to use and not for self programming using the api, because I've no experience in programming on i Series?

    You may check the QSQGNDDL API (Generate Data Definition Language).

    AFAIL this API is used in IBM i Navigator if you execute the GENERATE SQL command.

    You may also retrieve the SQL code from the catalog view SYSVIEWS located within the QSYS2 schema. If you need to find out the dependencies between the view you may access the SYSVIEWDEP view also located within the QSYS2 schema.

    But as soon as you deleted the base table or view, neither the API nor the catalog views will return any information about the views.

    In either way I'd warmly suggest to store the SQL codes for all your views.

    We also use IBM i Navigator for generating database objects. But after having created them for the first time, we do a reverse engineering (GENERATE SQL) and store the SQL statement in a source physical file member (my manager does not want those information stored somewhere in the IFS or on any PC). Then we delete the SQL objects again and recreate them based on the stored source member with the RUNSQLSTM command.

    Birgitta

  • mkoni
    mkoni
    39 Posts

    Re: SQL Views auto deleted

    ‏2014-02-25T14:50:43Z  
    • B.Hauser
    • ‏2014-02-25T14:29:27Z

    You may check the QSQGNDDL API (Generate Data Definition Language).

    AFAIL this API is used in IBM i Navigator if you execute the GENERATE SQL command.

    You may also retrieve the SQL code from the catalog view SYSVIEWS located within the QSYS2 schema. If you need to find out the dependencies between the view you may access the SYSVIEWDEP view also located within the QSYS2 schema.

    But as soon as you deleted the base table or view, neither the API nor the catalog views will return any information about the views.

    In either way I'd warmly suggest to store the SQL codes for all your views.

    We also use IBM i Navigator for generating database objects. But after having created them for the first time, we do a reverse engineering (GENERATE SQL) and store the SQL statement in a source physical file member (my manager does not want those information stored somewhere in the IFS or on any PC). Then we delete the SQL objects again and recreate them based on the stored source member with the RUNSQLSTM command.

    Birgitta

    birgitta, thx for your answer

    I have some (not all) views where in the VIEW_DEFINITION field in table SYSVIEWS is NULL, but using IBM i Navigator - generate SQL I get the correct SQL. I don't know why but it is so.

    do you know a native CL Command for generate SQL, because we have a lot of Views so it take a lot of time to do this manually in navigator?   I found only source codes for QSQGNDDL which has to modified but I cann't do this myself.

    Updated on 2014-02-25T15:12:31Z at 2014-02-25T15:12:31Z by mkoni
  • NickLawrence
    NickLawrence
    69 Posts

    Re: SQL Views auto deleted

    ‏2014-02-25T16:28:46Z  
    • mkoni
    • ‏2014-02-25T14:50:43Z

    birgitta, thx for your answer

    I have some (not all) views where in the VIEW_DEFINITION field in table SYSVIEWS is NULL, but using IBM i Navigator - generate SQL I get the correct SQL. I don't know why but it is so.

    do you know a native CL Command for generate SQL, because we have a lot of Views so it take a lot of time to do this manually in navigator?   I found only source codes for QSQGNDDL which has to modified but I cann't do this myself.

    1) There is no IBM supplied CL command to invoke the GNDDLSQL API

     

    2) QSYS.SYSVIEWS has a length constraint on the definition text than can be contained in the result column, if this length is exceeded - a null value is returned.  You could try SYSIBM.VIEWS, and see if that gives you more information..

  • mkoni
    mkoni
    39 Posts

    Re: SQL Views auto deleted

    ‏2014-02-26T07:50:58Z  

    1) There is no IBM supplied CL command to invoke the GNDDLSQL API

     

    2) QSYS.SYSVIEWS has a length constraint on the definition text than can be contained in the result column, if this length is exceeded - a null value is returned.  You could try SYSIBM.VIEWS, and see if that gives you more information..

    1) it must not be from IBM, maybe any user here has  a self-written command and can provide me (hope so) ;-)

    2)  yes, it could be that the SQL is too long for QSYS2.SYSVIEWS therefore the NUL value. SYSIBM.VIEWS has the value '*POINTER' in the VIEW_DEFINITION field.

  • NickLawrence
    NickLawrence
    69 Posts

    Re: SQL Views auto deleted

    ‏2014-02-26T15:55:18Z  
    • mkoni
    • ‏2014-02-26T07:50:58Z

    1) it must not be from IBM, maybe any user here has  a self-written command and can provide me (hope so) ;-)

    2)  yes, it could be that the SQL is too long for QSYS2.SYSVIEWS therefore the NUL value. SYSIBM.VIEWS has the value '*POINTER' in the VIEW_DEFINITION field.

    STRSQL cannot display CLOB or DBCLOB values, and so it replaces the data with *POINTER.

    I recommend navigator's Run Sql Scripts...ISQL is generally obsolete and doesn't get enhanced.  If you must use STRSQL from green-screen, you can cast the column to a shorter data type to look at the data.

    Updated on 2014-02-26T15:56:31Z at 2014-02-26T15:56:31Z by NickLawrence
  • NickLawrence
    NickLawrence
    69 Posts

    Re: SQL Views auto deleted

    ‏2014-02-26T15:55:19Z  
    • mkoni
    • ‏2014-02-26T07:50:58Z

    1) it must not be from IBM, maybe any user here has  a self-written command and can provide me (hope so) ;-)

    2)  yes, it could be that the SQL is too long for QSYS2.SYSVIEWS therefore the NUL value. SYSIBM.VIEWS has the value '*POINTER' in the VIEW_DEFINITION field.

    ISQL cannot display CLOB or DBCLOB values, and so it replaces the data with *POINTER.

    I recommend navigator run sql scripts...ISQL is generally obsolete.  If you must use ISQL, you can cast the column to a shorter data type to look at the data.