system table to keep track of dependent views?
The poster (IDS 11.70) wanted to list the tables/objects each view depends on. Art Kagel first suggested pattern-matching sysviews.viewtext, but Andrew Ford noted this breaks when a table name is split across viewtext rows (the column holds 64-byte chunks). Doug Lawry pointed to the sysdepend system table, joined twice to systables via btabid/dtabid, which handles that correctly. However, the poster then showed that sysdepend misses a table referenced only in a subquery in the select list of a UNION ALL branch; Art agreed this looks like a parser gap and advised opening an IBM support case. No fix for that last issue is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, I am trying to find the list of dependent objects used in the views. So can someone share the query or procedure which will list all the dependent objects used in the body of the views? Or if there exists any system table, then kindly let me know. I am using IDS 11.70 FC7. Thanks. Regards, Khan
The only way I know of is to search the VIEWTEXT column for your tablename: To find views referencing a single table: select tabname::char(20) as viewname from systables st, sysviews sv where st.tabid = sv.tabid and viewtext matches '*[, .]sometable ,]*'; Ex: > select tabname::char(20) as viewname from systables st, sysviews sv where st.tabid = sv.tabid and viewtext matches '*[, .]sysindexes[ ,]*'; viewname sysindexes To find all such references: > select st1.tabname::char(20) as viewname, st2.tabname as references > from systables st1, sysviews sv, systables st2 > where st1.tabid = sv.tabid and viewtext matches '*[, .]'||st2.tabname||'[ ,]*' > and st1.tabtype = 'V' > and st2.tabtype != 'V'; viewname sysdomains references sysxtdtypes viewname sysindexes references sysindices Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Sep 18, 2013 at 10:06 AM, O KHAN <theultimateboy@hotmail.com> wrote: > Hi All, > > I am trying to find the list of dependent objects used in the views. > > So can someone share the query or procedure which will list all the > dependent > objects used in the body of the views? > > Or if there exists any system table, then kindly let me know. I am using > IDS > 11.70 FC7. > > Thanks. > > Regards, > Khan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3dfba8b9c1004e6a9c0ad
You could run into a problem when a table name is split over two lines of viewtext like it is in sysdomains tabid 70 seqno 0 viewtext create view "informix".sysdomains (id,owner,name,type) as select tabid 70 seqno 1 viewtext x0.extended_id ,x0.owner ,x0.name ,x0.type from "informix".sysx tabid 70 seqno 2 viewtext tdtypes x0 where (x0.domain = 'D' ) ; Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, September 18, 2013 10:04 AM To: ids@iiug.org Subject: Re: system table to keep track of dependent views? [31449] The only way I know of is to search the VIEWTEXT column for your tablename: To find views referencing a single table: select tabname::char(20) as viewname from systables st, sysviews sv where st.tabid = sv.tabid and viewtext matches '*[, .]sometable ,]*'; Ex: > select tabname::char(20) as viewname from systables st, sysviews sv > where st.tabid = sv.tabid and viewtext matches '*[, .]sysindexes[ ,]*'; viewname sysindexes To find all such references: > select st1.tabname::char(20) as viewname, st2.tabname as references > from systables st1, sysviews sv, systables st2 where st1.tabid = > sv.tabid and viewtext matches '*[, .]'||st2.tabname||'[ ,]*' > and st1.tabtype = 'V' > and st2.tabtype != 'V'; viewname sysdomains references sysxtdtypes viewname sysindexes references sysindices Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Sep 18, 2013 at 10:06 AM, O KHAN <theultimateboy@hotmail.com> wrote: > Hi All, > > I am trying to find the list of dependent objects used in the views. > > So can someone share the query or procedure which will list all the > dependent objects used in the body of the views? > > Or if there exists any system table, then kindly let me know. I am > using IDS > 11.70 FC7. > > Thanks. > > Regards, > Khan > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3dfba8b9c1004e6a9c0ad **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Good point! I didn't run into that one testing this because I'm running v12.10 and the viewtext column was expanded from 64bytes to 256bytes without documentation so my sysdomains was found just fine. The table description in the SQL Reference for version 12.10.xC1 still shows it as CHAR(64). It seems to have been corrected in the online InfoCenter for 12.10 but I haven't downloaded the PDF manuals yet to check them. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Sep 18, 2013 at 11:20 AM, Andrew Ford <andrew@informix-dba.com>wrote: > You could run into a problem when a table name is split over two lines of > viewtext like it is in sysdomains > > tabid 70 > seqno 0 > viewtext create view "informix".sysdomains (id,owner,name,type) as select > > tabid 70 > seqno 1 > viewtext x0.extended_id ,x0.owner ,x0.name ,x0.type from "informix".sysx > > tabid 70 > seqno 2 > viewtext tdtypes x0 where (x0.domain = 'D' ) ; > > Andrew > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Wednesday, September 18, 2013 10:04 AM > To: ids@iiug.org > Subject: Re: system table to keep track of dependent views? [31449] > > The only way I know of is to search the VIEWTEXT column for your tablename: > > To find views referencing a single table: > > select tabname::char(20) as viewname from systables st, sysviews sv where > st.tabid = sv.tabid and viewtext matches '*[, .]sometable ,]*'; > > Ex: > > > select tabname::char(20) as viewname from systables st, sysviews sv > > where > st.tabid = sv.tabid and viewtext matches '*[, .]sysindexes[ ,]*'; > > viewname > > sysindexes > > To find all such references: > > > select st1.tabname::char(20) as viewname, st2.tabname as references > > from systables st1, sysviews sv, systables st2 where st1.tabid = > > sv.tabid and viewtext matches '*[, .]'||st2.tabname||'[ > ,]*' > > and st1.tabtype = 'V' > > and st2.tabtype != 'V'; > > viewname sysdomains > references sysxtdtypes > > viewname sysindexes > references sysindices > > Art > > Art S. Kagel, Principal Consultant > > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or > by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Wed, Sep 18, 2013 at 10:06 AM, O KHAN <theultimateboy@hotmail.com> > wrote: > > > Hi All, > > > > I am trying to find the list of dependent objects used in the views. > > > > So can someone share the query or procedure which will list all the > > dependent objects used in the body of the views? > > > > Or if there exists any system table, then kindly let me know. I am > > using IDS > > 11.70 FC7. > > > > Thanks. > > > > Regards, > > Khan > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c3dfba8b9c1004e6a9c0ad > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0160bab0d4d9a204e6aae890
My version to list all:
SELECT t.tabname AS object_name, v.tabname AS view_name
FROM sysdepend, systables AS t, systables AS v
WHERE btabid > 99 -- ignore system tables
AND t.tabid = btabid
AND v.tabid = dtabid
ORDER BY 1, 2;
See also JL on Stack Overflow (Google "informix sysdepend").
Regards,
Doug Lawry
Hi,
As highlighted by Andrew in the early reply, these queries do not work if the
table name used in the view is splitted across two rows.
Here is an example which i have created using the IDS 11.70 FC4 to test your
query and it failed.
CREATE view "informix".v_test_12345678 (tabid,tabname,tabtype) as SELECT
x0.tabid, x0.tabname, x0.tabtype from "informix".systables x0 WHERE tabid =
100;
So, the only way left here is to check it using the dbschema.
I am still wondering what is the purpose of sysdepend table, if it is not
working correctly in this case?
Thanks.
Regards,
Khan
Sysdepend actually should be working, as Doug pointed out.
select b.tabname as viewname, c.tabname as dependent
from sysdepend as a, systables as b, systables as c
where a.dtabid = b.tabid
and a.btabid = c.tabid;
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Mon, Sep 23, 2013 at 11:20 AM, O KHAN <theultimateboy@hotmail.com> wrote:
> Hi,
>
> As highlighted by Andrew in the early reply, these queries do not work if
> the
> table name used in the view is splitted across two rows.
>
> Here is an example which i have created using the IDS 11.70 FC4 to test
> your
> query and it failed.
>
> CREATE view "informix".v_test_12345678 (tabid,tabname,tabtype) as SELECT
> x0.tabid, x0.tabname, x0.tabtype from "informix".systables x0 WHERE tabid =
> 100;
>
> So, the only way left here is to check it using the dbschema.
>
> I am still wondering what is the purpose of sysdepend table, if it is not
> working correctly in this case?
>
> Thanks.
>
> Regards,
> Khan
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c379ec88723704e716e373
Dear Art/Doug,
The query shared by Doug Lawry, which is based on sysdepend table fails in the
following scenario:
I have created a view, in which I am executing a query in the select clause to
pull as column. And I am also using the union all to pull data from another
identical query. See the example for details:
Example:
create view v_customer_details (cust_number,first_name,last_name, company,
state)as
SELECT customer_num,fname,lname,company, "TEST" state
FROM customer
union all
SELECT customer_num,fname,lname,company,
(SELECT sname from state WHERE code = a.state) state
FROM customer a;
-- sysdepend query:
SELECT unique t.tabname[1,30] AS object_name, v.tabname[1,30] AS view_name
FROM sysdepend, systables AS t, systables AS v
WHERE t.tabid = btabid
AND v.tabid = dtabid
and v.tabname = 'v_customer_details'
ORDER BY 1, 2;
-- results
object_name view_name
customer v_customer_details
Problem:
Although, this view is based on two tables (i.e. customer & state), but
sysdepend is only showing one dependency, which is wrong.
If I remove the union all, then it shows the correct result. Hence, it
indicates a limitation in the IDS engine 11.70 FC7.
Please share your thoughts on this scenario. I will be looking forward for
your response.
Regards,
Omer Khan
Dear Doug,
The query shared by Doug Lawry, which is based on sysdepend table fails in the
following scenario:
I have created a view, in which I am executing a query in the select clause to
pull as column. And I am also using the union all to pull data from another
identical query. See the example for details:
Example:
create view v_customer_details (cust_number,first_name,last_name, company,
state)as
SELECT customer_num,fname,lname,company, "TEST" state
FROM customer
union all
SELECT customer_num,fname,lname,company,
(SELECT sname from state WHERE code = a.state) state
FROM customer a;
-- sysdepend query:
SELECT unique t.tabname[1,30] AS object_name, v.tabname[1,30] AS view_name
FROM sysdepend, systables AS t, systables AS v
WHERE t.tabid = btabid
AND v.tabid = dtabid
and v.tabname = 'v_customer_details'
ORDER BY 1, 2;
-- results
object_name view_name
customer v_customer_details
Problem:
Although, this view is based on two tables (i.e. customer & state), but
sysdepend is only showing one dependency, which is wrong.
If I remove the union all, then it shows the correct result. Hence, it
indicates a limitation in the IDS engine 11.70 FC7.
Please share your thoughts on this scenario. I will be looking forward for
your response.
Regards,
Omer Khan
Looks like the engine's parser for the dependency is missing the state
table because it is only mentioned in a subquery in the projection clause.
I would open a support case with IBM. Don't know that this isn't
intentional, but ...
BTW, you could rewrite the view definition like the following and it would
probably perform better (the engine may fold the subquery into the outer
query, but not necessarily):
create view v_customer_details (cust_number,first_name,last_name, company,
state)as
SELECT customer_num,fname,lname,company, "TEST" state
FROM customer
union all
SELECT customer_num,fname,lname,company, b.state
(SELECT sname from state WHERE code = a.state) state
FROM customer a, state b
WHERE a.state = b.state;
I do know this was a test, but I gather it comes from some real view in
your environment.
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Sep 25, 2013 at 3:50 PM, O KHAN <theultimateboy@hotmail.com> wrote:
> Dear Art/Doug,
>
> The query shared by Doug Lawry, which is based on sysdepend table fails in
> the
> following scenario:
>
> I have created a view, in which I am executing a query in the select
> clause to
> pull as column. And I am also using the union all to pull data from
> another
> identical query. See the example for details:
>
> Example:
> create view v_customer_details (cust_number,first_name,last_name, company,
> state)> as
> SELECT customer_num,fname,lname,company, "TEST" state
> FROM customer
> union all
> SELECT customer_num,fname,lname,company,
> (SELECT sname from state WHERE code = a.state) state
> FROM customer a;>
> -- sysdepend query:
> SELECT unique t.tabname[1,30] AS object_name, v.tabname[1,30] AS view_name
> FROM sysdepend, systables AS t, systables AS v
> WHERE t.tabid = btabid
> AND v.tabid = dtabid
> and v.tabname = 'v_customer_details'
> ORDER BY 1, 2;>
> -- results
> object_name view_name
> customer v_customer_details
>
> Problem:
> Although, this view is based on two tables (i.e. customer & state), but
> sysdepend is only showing one dependency, which is wrong.
> If I remove the union all, then it shows the correct result. Hence, it
> indicates a limitation in the IDS engine 11.70 FC7.
>
> Please share your thoughts on this scenario. I will be looking forward for
> your response.
>
> Regards,
> Omer Khan
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c365b634744f04e73c713f