About cookies on this site Our websites require some cookies to function properly (required). In addition, other cookies may be used with your consent to analyze site usage, improve the user experience and for advertising. For more information, please review your options. By visiting our website, you agree to our processing of information as described in IBM’sprivacy statement. To provide a smooth navigation, your cookie preferences will be shared across the IBM web domains listed here.
Last updated: Feb 11, 2025
The tables in this section show nodes representing data-mining operations that support SQL pushback. If a node doesn't appear in these tables, it doesn't support SQL pushback.
Nodes supporting SQL generation | Notes |
---|---|
Select | Supports generation only if SQL generation for the select expression itself is supported. If any fields have nulls, SQL generation does not give the same results for discard as are given in native SPSS Modeler. |
Sample | Simple sampling supports SQL generation to varying degrees depending on the database. |
Aggregate | SQL generation support for aggregation depends on the data storage type. |
RFM Aggregate | Supports generation except if saving the date of the second or third most recent
transactions, or if only including recent transactions. However, including recent transactions does
work if the function is pushed back. |
Sort | |
Merge | No SQL generated for merge by order. Merge by key with full or partial outer join is only supported if the database/driver supports it. Non-matching input fields can be renamed by means of a Filter node, or the Filter settings of an import node. Supports SQL generation for merge by condition. For all
types of merge, |
Append | Supports generation if inputs are unsorted. SQL optimization is only possible when your inputs have the same number of columns. |
Distinct | A Distinct node with the (default) mode Create a composite record for each group selected doesn't support SQL optimization. |
Mode | Sample | Max size | Seed | Db2 for z/OS | Db2 for OS/400 | Db2 for Win/UNIX | Oracle | SQL Server | Teradata |
---|---|---|---|---|---|---|---|---|---|
Include | First | n/a | Y | Y | Y | Y | Y | Y | |
1-in-n | off | Y | Y | Y | Y | Y | |||
max | Y | Y | Y | Y | Y | ||||
Random % | off | off | Y | Y | Y | Y | |||
on | Y | Y | Y | ||||||
max | off | Y | Y | Y | Y | ||||
on | Y | Y | Y | ||||||
Discard | First | off | Y | ||||||
max | Y | ||||||||
1-in-n | off | Y | Y | Y | Y | Y | |||
max | Y | Y | Y | Y | Y | ||||
Random % | off | off | Y | Y | Y | Y | |||
on | Y | Y | Y | ||||||
max | off | Y | Y | Y | Y | ||||
on | Y | Y | Y |
Storage | Sum | Mean | Min | Max | SDev | Median | Count | Variance | Percentile |
---|---|---|---|---|---|---|---|---|---|
Integer | Y | Y | Y | Y | Y | Y* | Y | Y | Y* |
Real | Y | Y | Y | Y | Y | Y* | Y | Y | Y* |
Date | Y | Y | Y* | Y | Y* | ||||
Time | Y | Y | Y* | Y | Y* | ||||
Timestamp | Y | Y | Y* | Y | Y* | ||||
String | Y | Y | Y* | Y | Y* |
* Median and Percentile are supported on Oracle.
Nodes supporting SQL generation | Notes |
---|---|
Type | Supports SQL generation if the Type node is instantiated and no or
type checking is specified. |
Filter | |
Derive | Supports SQL generation if SQL generated for the derive expression is supported (see expressions later on this page). |
Ensemble | Supports SQL generation for Continuous targets. For other targets, supports generation only if the Highest confidence wins ensemble method is used. |
Filler | Supports SQL generation if the SQL generated for the derive expression is supported. |
Anonymize | Supports SQL generation for Continuous targets, and partial SQL generation for Nominal and Flag targets. |
Reclassify | |
Binning | Supports SQL generation if the Tiles (equal count) binning method is used and the Read from Bin Values tab if available option is selected. Due to differences in the way that bin boundaries are calculated (this is caused by the nature of the distribution of data in bin fields), you might see differences in the binning output when comparing normal flow execution results and SQL pushback results. To avoid this, use the Record count tiling method, and either Add to next or Keep in current tiles to obtain the closest match between the two methods of flow execution. |
RFM Analysis | Supports SQL generation if the Read from Bin Values tab if available option is selected, but downstream nodes will not support it. |
Partition | Supports SQL generation to assign records to partitions. |
Set To Flag | |
Restructure |
Nodes supporting SQL generation | Notes |
---|---|
Distribution | |
Web | |
Evaluation |
For some models, SQL for the model nugget can be generated, pushing back the model scoring stage to the database. The main use of this feature is not to improve performance, but to allow flows containing these nuggets to have their full SQL pushed back. See Generating SQL from model nuggets for more information.
Model nuggets supporting SQL generation | Notes |
---|---|
C&R Tree | Supports SQL generation for the single tree option, but not for the boosting, bagging, or large dataset options. |
QUEST | |
CHAID | |
C5.0 | |
Decision List | |
Linear | Supports SQL generation for the standard model option, but not for the boosting, bagging, or large dataset options. |
Neural Net | Supports SQL generation for the standard model option (Multilayer Perceptron only), but not for the boosting, bagging, or large dataset options. |
PCA/Factor | |
Logistic | Supports SQL generation for Multinomial procedure but not Binomial. For Multinomial, generation isn't supported when confidences are selected, unless the target type is Flag. |
Generated Rulesets | |
Auto Classifier | If a User Defined Function (UDF) scoring adapter is enabled, these nuggets support SQL pushback. Also, if either SQL generation for Continuous targets, or the Highest confidence wins ensemble method are used, these nuggets support further pushback downstream. |
Auto Numeric |
Nodes supporting SQL generation | Notes |
---|---|
Table | Supports generation if SQL generation is supported for highlight expression. |
Matrix | Supports generation except if All numerics is selected for the Fields option. |
Analysis | Supports generation, depending on the options selected. |
Transform | |
Statistics | Supports generation if the Correlate option isn't used. |
Report | |
Set Globals |
Was the topic helpful?
0/1000