On 05 Apr 2013 15:16, Vern Hamberg wrote:
I see that there seems to be based-on information in the file
description of a view.
Parsing the FROM clause of the VIEW definition itself is not a
trivial task like it is for parsing the list of FROM files in a *QRYDFN
object exported to a source member using RTVQMQRY. The based-on TABLEs
could be in a CTE, an NTE, in a reference VIEW, or in a subquery each of
which may reference those others.
But there is none of that in SYSVIEWS, nor in QADBXREF, the table
named in the view, SYSVIEWS.
The information is available in the SYSVIEWDEP catalog VIEW which is
based on the QADBFDEP which has the /file dependency/ information.
Others had already mentioned that, so I snipped that part of the
original message in my earlier reply about using a UDTF or stored
procedure [for getting FLOAT scale]. I am not sure of the status of
available of that VIEW on other databases however.
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcatalog.htm
"...
# _i IBM i catalog tables and views i_
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcatalogtbls.htm
The IBM i catalog includes the views and tables in the QSYS2 schema
displayed in this section.
# _i ODBC and JDBC catalog views i_
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcatalogodbc.htm
The catalog includes the views and tables in the SYSIBM library
displayed in this section.
# _i ANS and ISO catalog views i_
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcatalogans.htm
There are two versions of some of the ANS and ISO catalog views. The
version documented is the normal set of ANS and ISO views. A second set
of views have names that are limited to no more than 18 characters and
other than the view names are not documented in this book.
..."
So it seems only the DSPFD kind of thing will get this information.
As Chuck said, write a UDF or UDTF even.
The UDTF or stored procedure recommendation that I made was about a
different issue for which the requirement was merely a "way of getting
that value via JDBC"; i.e. getting the FLOAT scale.
Having to create a routine may not entirely meet the requirement
stated for this VIEW dependency\based-on issue, because the means to
accomplish the task must be "pure JDBC, and DBMS-neutral" manner
according to the OP. Deploying the necessary routines to establish a SP
or UDTF on each DBMS to be accessed, likely will not qualify as a "pure
JDBC" implementation for the OP :-( because each DBMS would likely have
its own implementation. On the IBM i each of the DSPFD, QDBRTVFD API,
or a query of the SYSVIEWDEP catalog VIEW would suffice to implement
such a routine, but what of other non-IBM databases that are unlikely to
have SYSVIEWDEP and perhaps not offer anything else that gives that
information?
Regards, Chuck
James H. H. Lampert on Fri, 05 Apr 2013 18:01:52 -0400 (EDT) wrote:
<<SNIP>>
Which brings me back to the question of how to find a view's based-on
table(s), in a way that's pure JDBC, and DBMS-neutral.
midrange.com
