Data Integration

Data Integration

Connect with experts and peers to elevate technical expertise, solve problems and share insights.

 View Only

  • 1.  Change connections dynamically in Jobs with odbc connector

    Posted Fri June 14, 2024 04:46 PM

    Hello everyone,
    I have some questions about how to use dynamic connectios in odbc connector if its possible and how on a parallel or secuential job in datastage.

    I have 1 parallel job that only insert and update rows in one table (passtrough)


    This parallel job in his structure is 
    one odbc connector as source (this source always is the same) and one target odbc connector, but i have 300 target with the same table but different connection.

    I dont want to duplicate the same job up to 300 jobs
    Also i dont want to add up to 300 odbc connector in 1 only one job.


    I want to know if its possible to use only one job and dynamically change the target connection and how to.
    In this scenario with just 1 job I can send the same information to 300 targets.

    Regards.



    ------------------------------
    Ricardo Lopez Pacheco
    ------------------------------


  • 2.  RE: Change connections dynamically in Jobs with odbc connector

    Posted Mon June 17, 2024 01:07 AM

    Hi Ricardo,

    I have not worked much with ODBC, but you should be able to parameterize Data source. Then have a parameter DATA_SOURCE in your job that you use in the Data source field of your connector. Build a DataStage sequence for you with a list loop, itterating your 300 Data Sources and calling the DataStage job in each itteration with the value form your list.



    ------------------------------
    Ralf Martin
    Principal Consultant
    infologistix GmbH
    Bregenz
    ------------------------------

    Original Message


  • 3.  RE: Change connections dynamically in Jobs with odbc connector

    Posted Mon June 17, 2024 02:07 AM

    Hi Ricardo, Ralf,

    for ODBC Connection you need for each Database a ODBC-Connection entry in the odbc.ini.

    Therefore I recommend a jdbc-Connection. Then you can loop through a list of connections and pass the connection string to the job. But jdbc connector has a much higher memory footprint as any other connection type.

    Regards,

    Udo



    ------------------------------
    Udo Neumann
    ------------------------------

    Original Message


  • 4.  RE: Change connections dynamically in Jobs with odbc connector

    Posted Mon June 17, 2024 05:08 AM
    Edited by Gerhard Paulus Mon June 17, 2024 05:12 AM

    Hi Ricardo,
    Udo and Ralf,

    I don't recommend jdbc-Connections if there are any other Connectors (written in C) possible like Database-native Connectors (i.e. Db2 / Oracle) or ODBC. In case ODBC every data source has to be configured in odbc.ini as Udo wrote.

    As Ralf has already wrote, you can parameterize the ODBC connection. The best way to do this is with parameter sets. When you start the job, you then simply select the corresponding value file of the parameter set for the database against which the job should run with this run.

    When you implemented this job with ParameterSets then you are able (if you want) to embed it into a parent sequence with some loop iterations as Ralf described in his first reply.

    Regards,
    Gerhard



    ------------------------------
    Gerhard Paulus
    ------------------------------

    Original Message


  • 5.  RE: Change connections dynamically in Jobs with odbc connector

    Posted Fri June 28, 2024 01:34 PM

    Hello Everyone

    Thank you ,Udo, Ralf and Gerhard for the reply.

    Ralf/Gerhard, I already have my parameter set with all connections.

    But ¿how can I run these jobs with loop interactions? without the execution being serial, because the way I do it that's how it happens and what I want is for the only job to be executed interacting between the 300 source data at the same time and not wait to release previous executions to finish so that start the new execution with the connection parameter.

    Regards



    ------------------------------
    Ricardo Lopez Pacheco
    ------------------------------

    Original Message


  • 6.  RE: Change connections dynamically in Jobs with odbc connector

    Posted Mon July 01, 2024 04:20 AM

    Hi Ricardo,

    based on the Parameter Files for each connection, stored in the directory "/opt/IBM/InformationServer/Server/Projects/<DS_PROJECT>/ParameterSets/<Parameterset Name>, I recommend creating a list of these files with different delimiter for the entries and groups. That could be done by a script outside Datastage.

    E.g. for the execution of 4 jobs in parallel:

    TIENDA_4102,TIENDA_4103,TIENDA_4104,TIENDA_4107;TIENDA_4102,TIENDA_4102,TIENDA_4102,TIENDA_4102;....

    In the sequence you could loop through the list with delimiter ";" and then splitt the parameter delimited by "," with field function.

    Similar to:

    Not nice but it works,

    Or you did it completely outside Datastage and execute the jobs from a script.

    Regards,
    Udo



    ------------------------------
    Udo Neumann
    ------------------------------

    Original Message


  • 7.  RE: Change connections dynamically in Jobs with odbc connector

    Posted Mon July 01, 2024 04:45 AM

    Hello Ricardo,

    If the extraction and  and transformations are the same, may be you can work with a dataset between the Stages Transformer and Load. You should save time.
    For the load, if you run the job through DataStage you will have to wait the end. So executions will be serial.
    To start the same Parallel Job simultaneously, you call it several times in the Sequencer Job or you work in command line.

    Regards



    ------------------------------
    Frederic Bargues
    ------------------------------

    Original Message


  • 8.  RE: Change connections dynamically in Jobs with odbc connector

    Posted Mon July 01, 2024 08:42 AM

    Hi Ricardo,

    Udos suggestion is good, I did something like this in a past project, being able to run 1,2,4,8,16 times in parallel if needed. If you really want to have all 300 instances of the job to run in parallel (and the size of your installation supports this), then your either have to build this manually in your sequence (i.e. you have the job activity pointing to your job 300 times on the canvas) or you build a loop and run a execute command activity which then calls dsjob followed by the Unix & to run in the background, but then you will have no easy control about your jobs being successfull or not. I would rather go for Udos aproach or build something outside of DataStage in an external scheduling tool (TWS, CTRL-M ...).



    ------------------------------
    Ralf Martin
    Principal Consultant
    infologistix GmbH
    Bregenz
    ------------------------------

    Original Message


  • 9.  RE: Change connections dynamically in Jobs with odbc connector

    Posted Tue March 25, 2025 09:31 AM

    Your requirement is to dynamically change the target connection in an ODBC Connector within a single DataStage job without duplicating it 300 times. This can be achieved using parameterized connections in DataStage. Here's how you can do it:

    Solution Approach

    1. Use Job Parameters for Connection Details:

      • Define job parameters for Database Name, Username, Password, and Server.
      • Example parameters:
        • #DB_NAME#
        • #DB_USER#
        • #DB_PASSWORD#
        • #DB_SERVER#

    2. Use a Control Table or File for Connection Info:

      • Create a control table or a file listing the 300 different target database connection details.
      • Each record should contain connection details for one target.

    3. Use a Loop to Process Multiple Connections:

      • Create a parent sequence job that:
        • Reads connection details from the control table.
        • Passes them as parameters to the parallel job.
        • Runs the job iteratively for each target.

    4. Modify the ODBC Connector to Use Parameters:

      • Instead of hardcoding the connection details in the target ODBC Connector, use job parameters:
        • Data source: #DB_NAME#
        • User: #DB_USER#
        • Password: #DB_PASSWORD#
        • Server: #DB_SERVER#

    Implementation Steps

    1. Create a Control Table/File

      DB_NAME | DB_USER | DB_PASSWORD | DB_SERVER
------- | ------- | ----------- | ---------
Target1 | user1   | pass1       | server1
Target2 | user2   | pass2       | server2
...
Target300 | user300 | pass300   | server300

    2. Create a Job Sequence

      • Add a loop stage to iterate over the control table.
      • Pass connection details to the parallel job dynamically.

    3. Configure the Parallel Job

      • Modify the ODBC target connector to use job parameters.

    4. Run the Sequence Job

      • It will execute the parallel job 300 times, each time with a different target connection.

    Alternative Approach

    • If your DataStage version supports runtime column propagation (RCP), you can use a multi-instance job to process multiple targets in parallel.

    Would you like a step-by-step guide for setting up the Job Sequence in DataStage?



    ------------------------------
    Zohaib Arshad
    ------------------------------

    Original Message


Related Content