OBJECT_DEPENDENCIES
system viewOBJECT_DEPENDENCIES
system view is limited by the fact that it only reports objects - that is, base tables or information views - but not the columns contained therein.CDATA
column of the _SYS_REPO.ACTIVE_OBJECT
system table, and we can query it by package name, object name and object suffix (which is basically the extension of the file containing the definition that is stored in the repository):SELECT CDATA
FROM _SYS_REPO.ACTIVE_OBJECT
WHERE PACKAGE_ID = 'my.package.name'
AND OBJECT_NAME = 'CA_MY_CALCULATION_VIEW'
AND OBJECT_SUFFIX = 'calculationview'
_SYS_REPO.ACTIVE_OBJECT
can be joined to OBJECT_DEPENDENCIES
to discover the objects on which the information view depends:SELECT od.BASE_SCHEMA_NAME
, od.BASE_OBJECT_NAME
, od.BASE_OBJECT_TYPE
FROM _SYS_REPO.ACTIVE_OBJECT ao
INNER JOIN OBJECT_DEPENDENCIES od
ON '_SYS_BIC' = od.DEPENDENT_SCHEMA_NAME
AND ao.PACKAGE_ID||'/'||ao.OBJECT_NAME = od.DEPENDENT_OBJECT_NAME
AND 'VIEW' = od.DEPENDENT_OBJECT_TYPE
WHERE ao.PACKAGE_ID = 'my.package.name'
AND ao.OBJECT_NAME = 'CA_MY_CALCULATION_VIEW'
AND ao.OBJECT_SUFFIX = 'calculationview'
OBJECT_DEPENDENCIES
reports all dependencies, not just direct dependencies)OBJECT_DEPENDENCIES
:SELECT ao.PACKAGE_ID
, ao.OBJECT_NAME
, ao.OBJECT_SUFFIX
, ao.CDATA
FROM object_dependencies od
INNER JOIN _SYS_REPO.ACTIVE_OBJECT ao
ON SUBSTR_BEFORE(od.base_object_name, '/') = ao.package_id
AND SUBSTR_AFTER(od.base_object_name, '/') = ao.object_name
AND ao.object_suffix in (
'analyticview'
, 'attributeview'
, 'calculationview'
)
WHERE od.DEPENDENT_SCHEMA_NAME = '_SYS_BIC'
AND od.DEPENDENT_OBJECT_NAME = 'my.package.name/CA_MY_CALCULATION_VIEW'
AND od.DEPENDENT_OBJECT_TYPE = 'VIEW'
OBJECT_DEPENDENCIES
fails at reporting dependencies between analytic views and the attribute views they use. To capture those dependencies, you need to query _SYS_REPO.ACTIVE_OBJECTCROSSREF
.p_parse_xml
p_parse_xml
. Here is its signature:create PROCEDURE p_parse_xml (
-- XML string to parse
p_xml nclob
-- Parse tree is returned as a table variable
, out p_dom table (
-- unique id of the node
node_id int
-- id of the parent node
, parent_node_id int
-- dom node type constant: 1=element, 2=attribute, 3=text, 4=cdata,
-- 5=entityref, 6=entity, 7=processing instruction,
-- 8=comment, 9=document, 10=document type,
-- 11=document fragment, 12=notation
, node_type tinyint
-- dom node name: tagname for element, attribute name for attribute,
-- target for processing instruction,
-- document type name for document type,
-- "#text" for text and cdata, "#comment" for comment,
-- "#document" for document, "#document-fragment" for document fragment.
, node_name nvarchar(64)
-- dom node value: text for text, comment, and cdata nodes, data for processing instruction node, null otherwise.
, node_value nclob
-- raw token from the parser
, token_text nclob
-- character position of token
, pos int
-- lenght of token.
, len int
)
-- flag whether to strip text nodes that only contain whitespace from the parse tree
, p_strip_empty_text tinyint default 1
)
p_parse_xml
procedure depends on p_decode_xml_entities
,call p_parse_xml(
'<parent-element attribute1="value1">
<child-element attribute2="value2" attribute3="value3">
text-content1
</child-element>
<child-element att="bla">
text-content2
</child-element>
</parent-element>', ?);
+---------+----------------+-----------+----------------+-----------------------------+-------------------------------------------------------------+-----+-----+
| NODE_ID | PARENT_NODE_ID | NODE_TYPE | NODE_NAME | NODE_VALUE | TOKEN_TEXT | POS | LEN |
+---------+----------------+-----------+----------------+-----------------------------+-------------------------------------------------------------+-----+-----+
| 0 | ? | 9 | #document | ? | ? | 1 | 221 |
| 1 | 0 | 1 | parent-element | ? | <parent-element attribute1=\"value1\"> | 1 | 36 |
| 2 | 1 | 2 | attribute1 | value1 | attribute1=\"value1\" | 2 | 20 |
| 3 | 1 | 1 | child-element | ? | <child-element attribute2=\"value2\" attribute3=\"value3\"> | 41 | 55 |
| 4 | 3 | 2 | attribute2 | value2 | attribute2=\"value2\" | 42 | 20 |
| 5 | 3 | 2 | attribute3 | value3 | attribute3=\"value3\" | 62 | 20 |
| 6 | 3 | 3 | #text | text-content1 | text-content1 | 96 | 23 |
| 7 | 1 | 1 | child-element | ? | <child-element att=\"bla\"> | 139 | 25 |
| 8 | 7 | 2 | att | bla | att=\"bla\" | 140 | 10 |
| 9 | 7 | 3 | #text | text-content2 | text-content2 | 164 | 23 |
+---------+----------------+-----------+----------------+-----------------------------+-------------------------------------------------------------+-----+-----+
NODE_TYPE
column tells us what kind of node we're dealing with. Values in this column conform to the w3c standard document object model (DOM) enumeration of node type values.NODE_ID
is the unique identifier of the node while PARENT_NODE_ID
points to whatever node is considered the parent node of the current node.NODE_ID=3
has the element node with NODE_ID=1
as parent.<child-element>
and <parent-element>
elements in the document.p_parse_xml
does, mainly to keep the result table as simple as possible.NODE_NAME
column is a further characterization of what kind of node we're realing with. For most node types, the node name is a constant value which is essentially a friendly name for the node type.For example, document nodes (NODE_TYPE=9
always have #document
as NODE_NAME
, and text nodes (NODE_TYPE=3
) always have #text
as NODE_NAME
.NODE_TYPE
is 1
and 2
respectively), the NODE_NAME
is not constant. Rather, their node name conveys information about the meaning of the node and its contents. In other words, element and attribute names are metadata.NODE_VALUE
column contains actual data. For element and document nodes, it is alway NULL. For attributes, the NODE_VALUE
column contains the attribute value, and for text nodes, it is the text content.POS
lists the position where the current element was found; the LEN
column keeps track of the length of current item as it appears in the doucment. Typically you won't need these columns, except maybe for debugging purposes. The TOKEN_TEXT
column is also here mostly for debugging purposes.<keyMapping>
-elements like this:<keyMapping schemaName="...database schema..." columnObjectName="...table name..." columnName="...column name..."/>
p_parse_xml
and captured its result in a table variable called tab_dom
, we can run a query like this to obtain all <keyMapping>
elements:select keyMapping.*
from :tab_dom keyMapping
where keyMapping.node_type = 1 -- get us all elements
and keyMapping.node_name = 'keyMapping' -- with the tagname 'keyMappping'
<keyMapping>
elements. You might recall that in the p_parse_xml
result, attribute nodes have NODE_TYPE=2
appear as childnode of their respective element. So, we can extract all attributes of all <keyMapping>
elements with a self-join like this:select keyMapping_attributes.*
from :tab_dom keyMapping
inner join :tab_dom keyMapping_attributes
on keyMapping.node_id = keyMapping_attributes.parent_node_id -- find all nodes that have the keymapping element node as parent
and 2 = keyMapping_attributes.node_type -- but only if their node type indicates they are attribute nodes
where keyMapping.node_type = 1
and keyMapping.node_name = 'keyMapping'
schemaName
, columnObjectName
and columnName
, we should put a further restriction on the NODE_NAME
of these attribute nodes. Also note that this query will potentially give us multiple rows per <keyMapping>
-element (in fact, just as many as there are attributes). Since we'd like to have just one row for each <keyMapping>
-element having the values of its schemaName
, columnObjectName
and columnName
attributes in separate columns, we should rewrite this query so that each attribute gets its own self-join.select keyMapping_schemaName.node_value as schema_name
, keyMapping_columnObjectName.node_value as table_name
, keyMapping_columnName.node_value as column_name
from :tab_dom keyMapping
inner join :tab_dom keyMapping_schemaName -- get the attribute called 'schemaName'
on keyMapping.node_id = keyMapping_schemaName.parent_node_id
and 2 = keyMapping_schemaName.node_type
and 'schemaName' = keyMapping_schemaName.node_name
inner join :tab_dom keyMapping_columnObjectName -- get the attribute called 'columnObjectName'
on keyMapping.node_id = keyMapping_columnObjectName.parent_node_id
and 2 = keyMapping_columnObjectName.node_type
and 'columnObjectName' = keyMapping_columnObjectName.node_name
inner join :tab_dom keyMapping_columnName -- get the attribute called 'columnName'
on keyMapping.node_id = keyMapping_columnName.parent_node_id
and 2 = keyMapping_columnName.node_type
and 'columnName' = keyMapping_columnName.node_name
where keyMapping.node_type = 1
and keyMapping.node_name = 'keyMapping'
<Calculation:scenario ...>
...
<dataSources>
...
<DataSource id="...some id used to refer to this datasource..." type="DATA_BASE_TABLE">
...
<columnObject schemaName="...db schema name..." columnObjectName="...table name..."/>
...
</DataSource>
...
</dataSources>
...
<calculationViews>
...
<calculationView>
...
<input node="#...id of a DataSource element...">
...
<mapping source="...name of a column used as input..." ... >
...
</input>
...
</calculationView>
...
</calculationViews>
...
</Calculation:scenario>
<DataSource>
-elements having a type
-attribute with the value "DATA_BASE_TABLE"
.type
-attribute.To obtain the schema and table name of the base table, find the <columnObject>
-childelement of the <DataSource>
-element.schemaName
and columnObjectName
-attributes respectively contain the database schema and table name of the base table.The <DataSource>
-elements have an id
attribute, and its value is used as unique identifier to refer to this data source.<calulationView>
-elements.<calulationView>
-elements contain one or more <input>
-child elements, each of which represents a data stream that is used as input for the transformation step.The <input>
-elements have a node
-attribute.node
-attribute is the value of the id
-attribute of whatever element it refers to, prefixed by a hash-sign (#
).Note that this is a general technique to reference elements within the same XML document.<DataSource>
-element is used,<DataSource>
-element's code>id-attribute in the value of their code>node-attribute.<DataSource>
-element, we can find out which columns from the data source are used by looking for <mapping>
-child elements.The <mapping>
-elements have a source
-attribute, which holds the column-name.select distinct
ds_co_schemaName.node_value schema_name
, ds_co_columnObjectName.node_value table_name
, ds_usage_mapping_source.node_value column_name
--
-- ds: DataSource elements (Note the WHERE clause)
--
from :tab_dom ds
--
-- ds_type: demand that the value of the type-attribute of the DataSource elements equal 'DATA_BASE_TABLE'
-- this ensures we're only looking at base tables.
--
inner join :tab_dom ds_type
on ds.node_id = ds_type.parent_node_id
and 2 = ds_type.node_type
and 'type' = ds_type.node_name
and 'DATA_BASE_TABLE' = cast(ds_type.node_value as varchar(128))
--
-- ds_co: get the columnObject childelement of the DataSource element.
-- Also, get the schemaName and columnObjectName attributes of that columnObject-element.
--
inner join :tab_dom ds_co
on ds.node_id = ds_co.parent_node_id
and 1 = ds_co.node_type
and 'columnObject' = ds_co.node_name
inner join :tab_dom ds_co_schemaName
on ds_co.node_id = ds_co_schemaName.parent_node_id
and 2 = ds_co_schemaName.node_type
and 'schemaName' = ds_co_schemaName.node_name
inner join :tab_dom ds_co_columnObjectName
on ds_co.node_id = ds_co_columnObjectName.parent_node_id
and 2 = ds_co_columnObjectName.node_type
and 'columnObjectName' = ds_co_columnObjectName.node_name
--
-- ds_id: get the id-attribute of the DataSource element.
--
inner join :tab_dom ds_id
on ds.node_id = ds_id.parent_node_id
and 2 = ds_id.node_type
and 'id' = ds_id.node_name
--
-- ds_usage: find any attributes that refer to the id of the DataSource
--
inner join :tab_dom ds_usage
on 'node' = ds_usage.node_name
and 2 = ds_usage.node_type
and '#'||ds_id.node_value = cast(ds_usage.node_value as nvarchar(128))
--
-- ds_mapping: find any mapping child elements of the node that references the DataSource
--
inner join :tab_dom ds_usage_mapping
on 'mapping' = ds_usage_mapping.node_name
and 1 = ds_usage_mapping.node_type
and ds_usage.node_id = ds_usage_mapping.parent_node_id
--
-- ds_mapping_source: get the source of the mapping elements. These are our base column names.
--
inner join :tab_dom ds_usage_mapping_source
on 'source' = ds_usage_mapping_source.node_name
and 2 = ds_usage_mapping_source.node_type
and ds_usage_mapping.node_id = ds_usage_mapping_source.parent_node_id
where ds.node_type = 1
and ds.node_name = 'DataSource'
OBJECT_DEPENDENCIES
, but that you need to query _SYS_REPO.ACTIVE_OBJECTCROSSREF
to find out which Attribute views are used by Analytic views._SYS_REPO.ACTIVE_OBJECT
table.p_get_view_basecols
stored procedure.create PROCEDURE p_get_view_basecols (
-- package name pattern. Used to match packages containing analytic, attribute or calculation views. Can contain LIKE wildcards.
p_package_id nvarchar(255)
-- object name pattern. Used to match name of analytic, attribute or calculation views. Can contain LIKE wildcards.
, p_object_name nvarchar(255) default '%'
-- object suffix pattern. Can be used to specify the type of view. Can contain LIKE wildcards.
, p_object_suffix nvarchar(255) default '%'
-- flag to indicate whether to recursively analyze analytic, attribute or calculation views on which the view to be analyzed depends.
-- 0 means only look at the given view, 1 means also look at underlying views.
, p_recursive tinyint default 1
-- result table: base columns on which the specified view(s) depends.
, out p_cols table (
-- schema name of the referenced base column
schema_name nvarchar(128)
-- table name of the referenced base column
, table_name nvarchar(128)
-- column name of the referenced base column
, column_name nvarchar(128)
-- list of view names that depend on the base column
, views nclob
)
)
call p_get_view_basecols(
-- look in our application package (and its subpackages)
'our.application.package.%'
-- consider all information views
, '%'
-- consider all types of information views
, '%'
-- consider also information views upon which our information views depend
, 1
-- put the results into our output table
, ?
);
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
9 | |
7 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 |