Db2 Tools for zOS

Db2 Tools for z/OS

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Db2 zos ETL tool

    Posted Mon January 12, 2026 08:34 AM

    Hi 

    Does IBM offer a specific tool or utility that facilitates an end-to-end ETL process entirely within the Db2 for z/OS environment? specifically, I am looking for a solution that can efficiently unload data, apply custom transformations based on user requirements, and load the result into a separate Db2 for z/OS database.

    Regards,

    Shashank



    -------------------------------------------


  • 2.  RE: Db2 zos ETL tool

    Posted Mon January 12, 2026 09:22 AM

    I believe you want an 'into DB2' solution, the closer is use SELECT / LOAD and INCURSOR.

    You define an SQL cursor (EXEC SQL). The cursor executes a SELECT that: Reads the source table, applies all necessary transformations, returns the final format expected by the destination table.
    The LOAD utility consumes this cursor with the INCURSOR option The transformed data is loaded directly into the destination table





    ------------------------------
    Matheus Porsch
    Systems Administration - Kyndryl
    Brazil
    ------------------------------

    Original Message


  • 3.  RE: Db2 zos ETL tool

    Posted Tue January 13, 2026 12:30 AM
    Thanks Matheus  for your response. 

    I think you are referring to zOS Db2 loading the data by using the cross-loader function.

    Regards,
    Shashank 



    Original Message


  • 4.  RE: Db2 zos ETL tool

    Posted Tue January 13, 2026 08:02 AM

    Yeah, I do. You are welcome 



    ------------------------------
    Matheus Porsch
    Systems Administration - Kyndryl
    Brazil
    ------------------------------

    Original Message


  • 5.  RE: Db2 zos ETL tool

    Posted Wed January 14, 2026 11:10 PM

    Sadly no built in DB2 tool.   Ibm does have DataStage which is full blown ETL tool.   

    If you only wanting to relay on DB2 your options are limited.   

    opt 1 unload, use program to change the data, then load

    opt2 already discussed use a cross loader via one system to another using a cursor   .  Your select for the Cursor will need do any transformations which will limit what you can do   

    opt 3 Setup a DB2 Federate server here the transformation limits still remain but here you can use one statement that uses an sql insert or merge using a select from the other system 

    opt4  Create an advance stored procedure to pull data from one system then handle any transformations then insert, merge, remote load on the other system   

    opt5 use Java or preferably Python to do it all   

    Many of the above will require communication system table tables to be configured from one system to another

    Also pipelines can make many of the above run faster    

    my best advise would to buy and use Datastage and create new team that only specializes in ETL.  
     



    ------------------------------
    Douglas Partch
    CEO
    Database Nerds
    Omaha NE
    ------------------------------

    Original Message


  • 6.  RE: Db2 zos ETL tool

    Posted Fri March 13, 2026 01:02 PM

    So for an in-DB2 experience as you are asking, you have a couple of options where instead of doing ETL (Extract, Transform, & Load), you can switch to what is referred to as ELT (Extract Load & Transform).   With ELT, you are essentially performing an INSERT with a SUBSELECT where all of your data transformation is performed with the SUBSELECT whether it contain joins, unions, row filtering, column filtering, use of built-in functions and/or user defined functions.  

    You can even leverage "temporary tables" as a way of staging your transformation needs into multiple steps, especially if it makes it easier to manage and maintain.  An intermediate edition could even be used to feed multiple downstream steps for different targets with different purposes. 

    If you wish to make this process even more efficient, you can leverage the IBM DB2 Analytics Accelerator (IDAA) in performing this ELT processing by leveraging "Accelerator Only Tables" (AOTs).  This places all of the heavy lifting and transformation onto IFL engines in the accelerator.   All of this provides you with the secure single store benefits of keeping the data within the realm of your Db2 for z/OS subsystem or data sharing group.

    If you wish to do this ACROSS two different Db2 subsystems/groups you can also leverage the "Federation" capabilities of IDAA as well.  

    By keeping the data WITHIN your Db2 for z/OS environment, you dramatically improve the performance of your desired "ETL" processing by instead using "ELT" methods.

    Another option that is similar to using IDAA is to use Db2 Data Gate for z/OS as it can share many of the capabilities of IDAA when the target for Data Gate is DB2 WH, including Accelerator, and AOT capabilities.  

    In both cases of IDAA and Data Gate to Db2 WH, you also add in all of the additional built-in functions available in Db2 WH, even for queries originating thru Db2 for z/OS for both building your results as well as querying your final results. 



    ------------------------------
    Ed Lynch
    ------------------------------

    Original Message


Related Content