Compile options with SQL Pushdown
You can select run modes like ETL or ELT to specify how your data is processed during runtime.
The primary process that DataStage® uses is Extract, Transform, and Load (ETL), in which data is read into memory, processed, then written to a target. In DataStage, all jobs run in ETL mode by default. By selecting a different option under the Compile tab of your job settings, you can choose to run your job in an optimized mode using SQL Pushdown, which moves data processing work to the source or target databases.
When transformation logic is pushed down to the target database, the process follows Extract,
Load, and Transform (ELT). When transformation logic is pushed down to the source database, the
process follows Transform, Extract, and Load (TEL). In many cases, some transformation work will
still need to be done in between the Extract and Load steps, but as much work as possible is
performed on the source or target databases. Optimization is often useful when you need to transform
large data sets that are already in specific data sources or targets. The following options are
available for running your jobs:
- Extract, Transform, Load (ETL)
- This default mode extracts data from a source, transforms data in the chosen environment, and loads the processed data to a target.
- Pushdown to source
- This mode pushes transformation work down to the source database, performing as much work as possible before extraction. If the DataStage flow can be only partially converted to SQL, the remaining work is performed in the chosen environment.
- Pushdown to target
- This mode pushes transformation work down to the target database, performing as much work as possible after loading. The remaining work is performed in the chosen environment. When the analysis determines that the DataStage flow can be converted to SQL, ELT mode is used, and DataStage compiles the flow to SQL. When the analysis determines that the DataStage flow can be only partially converted to SQL, both ETL and ELT modes are used as needed. For more information, see ELT run mode in DataStage.