This appendix contains some SQL*Plus queries that are useful for calculating the total number of nodes in a configuration model, and the number of different types of nodes in a configuration model.
This appendix covers the following topics:
Use the Determine Number of Nodes by Type in a configuration model query to find out how many nodes of each type exist in a configuration model. The Numeric Identification of PS_NODE_TYPE table provides the numeric identification of the node types that need to be inserted in the query. For example, in the example that follows the table below, replace ps_node_type ID with 259 in the script to find out how many Component nodes exist in a configuration model.
PS_NODE_TYPE | Description |
---|---|
259 | Component |
261 | Feature |
262 | Option |
263 | Model Reference (BOM or Non-BOM) |
264 | Model Connector |
272 | Total |
273 | Resource |
436 | BOM Model Root node |
437 | BOM Option Class |
438 | BOM Standard Item |
Determine Number of Nodes by Type in a Configuration Model (not including Model References)
This query returns the number of nodes by type in a configuration model, but does not include nodes that belong to referenced Models. .
SQL> SELECT count(*) FROM cz_ps_nodes WHERE deleted_flag = '0' and ps_node_type = ps_node_type ID and devl_project_id = devl_project_id /
Determine Number of Nodes by Type in a Configuration Model (including Model References)
This query returns the number of nodes by type in a configuration model, including nodes that belong to referenced Models.
SQL> SELECT count(*) FROM cz_ps_nodes WHERE deleted_flag = '0' and ps_node_type = ps_node_type ID and devl_project_id in (select distinct component_id from cz_model_ref_expls where deleted_flag = '0' and model_id = devl_project_id) /
In the examples above, insert a number from the Numeric Identification of PS_NODE_TYPE table for the variable ps_node_type ID, and insert the configuration model ID for devl_project_id. To determine a configuration model's DEVL_PROJECT_ID, see the Determine DEVL_PROJECT_ID of a Configuration Model example.
The following examples show queries that use the 'in' and 'not in' functions to find out how many BOM and non-BOM nodes exist in a configuration model.
Determine Number of BOM Nodes in a Configuration Model (not including Model References)
SQL> SELECT count(*) FROM cz_ps_nodes WHERE deleted_flag = '0' and ps_node_type in (436,437,438) and devl_project_id = devl_project_id /
Determine Number of BOM Nodes in a Configuration Model (including Model References)
SQL> SELECT count(*) FROM cz_ps_nodes WHERE deleted_flag = '0' and ps_node_type in (436,437,438) and devl_project_id in (select distinct component_id from cz_model_ref_expls where deleted_flag = '0' and model_id = devl_project_id /
Determine Number of non-BOM Nodes in a Configuration Model (not including Model References)
SQL> SELECT count(*) FROM cz_ps_nodes WHERE deleted_flag = '0' and ps_node_type not in (436,437,438) and devl_project_id = devl_project_id /
Determine Number of non-BOM Nodes in a Configuration Model (including Model References)
SQL> SELECT count(*) FROM cz_ps_nodes WHERE deleted_flag = '0' and ps_node_type not in (436,437,438) and devl_project_id in (select distinct component_id from cz_model_ref_expls where deleted_flag = '0' and model_id = devl_project_id /
Determine DEVL_PROJECT_ID of a Configuration Model
SQL> SELECT devl_project_id, name FROM cz_devl_projects WHERE name like '%Sen%' and deleted_flag=’0’
In this example, replace Sen with part of your model's name. The query returns both the Model's devl_project_id and its name, as shown below:
DEVL_PROJECT_ID NAME 2020 Sentinel Custom Desktop(204 137)
Use the queries in this section to find out how many Features of a specific type exist in a configuration model. For each query, refer to the table below to determine the value to enter for the feature_type ID variable. Refer to Determine DEVL_PROJECT_ID of a Configuration Modelto determine your configuration model's devl_project_id.
FEATURE_TYPE | Description |
---|---|
0 | Option |
1 | Integer |
2 | Decimal |
3 | Boolean |
4 | Text |
Determine Number of Features by Type in a Configuration Model (not including Model References)
SQL> SELECT count(*) FROM cz_ps_nodes WHERE deleted_flag = '0' and ps_node_type = 261 and feature_type = feature_type ID and devl_project_id = devl_project_id /
Determine Number of Features by Type in a Configuration Model (including Model References)
SQL> SELECT count(*) FROM cz_ps_nodes WHERE deleted_flag = '0' and ps_node_type = 261 and feature_type = feature_type ID and devl_project_id in (select distinct component_id from cz_model_ref_expls where deleted_flag = '0' and model_id = devl_project_id /
Use the query below to find out how many rules of each type exist in a configuration model.
When executing the query, insert the model_id of one or more models where indicated in the example, and enter a value from the table below for rule_type ID.
RULE_TYPE | Description |
---|---|
21 | Logic Rule |
22 | Numeric Rule |
23 | Compatibility Rule |
24 | Compatibility Table |
25 | Func Preselect |
26 | Func Validate |
27 | Comparison Rule |
29 | Functional Companion |
30 | Design Chart |
33 | Runtime DISPLAY Condition |
34 | Runtime ENABLED Condition |
200 | Statement Rule |
300 | Configurator Extension Rule |
Determine Number of Rules by Type in a Configuration Model
select model_id, rule_type, count (distinct rule_id) as distinct_rules, count (*) as rule_instances_all_models from cz_model_ref_expls, cz_rules where cz_model_ref_expls.DELETED_FLAG = '0' and cz_model_ref_expls.component_id = cz_rules.devl_project_id and cz_rules.deleted_flag = '0' and ps_node_type != 264 and cz_rules.rule_type = rule_type ID and model_id in (list one or more models here, separated by commas) group by model_id, rule_type ;
There are two rule counts in the above example. The rule counts usually return the same amount but not always. The reason for this is that it is possible for the same child model to be referenced more than once. In this case, a rule is likely to be loaded and active once for each place its model is included in the overall root model. The data storage volume is indicated by the DISTINCT_RULES calculation; this counts each unique rule only once. The runtime memory and processing load will usually be better indicated by the RULE_INSTANCES_ALL_MODELS calculation.
The Determine Number of Rules by Relation in a Configuration Model example shows the query for determining the number of rules by relation type in a configuration model.
EXPR_RULE_TYPE | Description |
---|---|
1 | Requires |
2 | Implies |
3 | Excludes |
4 | Negates |
5 | Defaults |
6 | NumSelections |
8 | Contributes |
10 | Consumes |
Determine Number of Rules by Relation in a Configuration Model
SQL> SELECT count(*) FROM cz_rules WHERE rule_type = rule_type and expr_rule_type = expr_rule_type and devl_project_id in ( SELECT distinct ps_node_id FROM cz_ps_nodes WHERE deleted_flag = '0' and ps_node_type in(436,263) START WITH ps_node_id = devl_project_id CONNECT BY DECODE(PRIOR ps_node_type,263,PRIOR reference_id, PRIOR ps_node_id)= DECODE(PRIOR ps_node_type,263,ps_node_id,parent_id)) /
In the Determine Number of Rules by Relation in a Configuration Model example, insert the RULE_TYPE number shown in the Numeric Identification of RULE_TYPE table for the variable rule_type, insert the EXPR_RULE_TYPE number shown in the Numeric Identification of EXPR_RULE_TYPE table for the variable expr_rule_type, and insert the DEVL_PROJECT_ID of the configuration model for the variable devl_project_id (see the Determine DEVL_PROJECT_ID of a Configuration Model example).