Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Transformations and database sessions

  1. #1
    Join Date
    Jul 2007
    Posts
    247

    Default Transformations and database sessions

    Hi everybody,

    I have two transformations which use the same database connections. The first transformation calls a db procedure which sets a global pl/sql variable, the second transformation calls another procedure which uses the global variable. This will of course only work if both procedures use the same session

    The problem is that both transformations open a db session on their own and the second procedure doesn't get any values from the variable.

    Is it possible to establish a kind of "persistent" connection both procedures could use?

    I already tried to use connection pooling but I wasn't able to reduce the pool to less than 5 connections, so the pool is used but both procedures still use different sessions.

    Any idea anyone?

    The only workaround I see at the moment is to write a kind of "wrapper" procedure which call proc1 and then proc2 and to call this wrapper procedure in an own kettle transformation. This could work but isn't really a preferred way because there are some more procedures which use this global variable, meaning I would have to write a lot of wrapper procedures.

    Thx in advance,
    Ben

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    No a persistent connection is not possible at the moment between different transformation. It's possible between steps in the same transformation.

    Regards,
    Sven

  3. #3
    Join Date
    Jul 2007
    Posts
    247

    Default

    Thx for the reply.

    First, how does one set up a persistent connection between steps? Is this done automatically if all steps use the same db connection?

    Second, could you please verify if it's possible to change the initial pool size? Whenever I enter a different number than 5 the value is accepted but not saved. Monitoring the database does also show that five sessions are opened by spoon when using connection pooling.

    Thx again,
    Ben

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    You switch "unique connections" in the transformation options and all steps in a single transformation will use the same connection.

    For the pool size... 5 is probably a hardcoded minimum somewhere... a pool doesn't make sense if you only have 1 connection in the pool (well except maybe for the case for which you want to abuse it for )

    Regards,
    Sven

  5. #5
    Join Date
    Jul 2007
    Posts
    247

    Default

    You switch "unique connections" in the transformation options and all steps in a single transformation will use the same connection.
    Sorry, didn't see that. I didn't expect it on the misc tab.

    For the pool size... 5 is probably a hardcoded minimum somewhere... a pool doesn't make sense if you only have 1 connection in the pool (well except maybe for the case for which you want to abuse it for )
    Hmm, OK. Sounds at least somehow reasonable. However, IMHO a hardcoded (and in this case even editable) parameter doesn't make much sense. If you for example wanted to increase the inital size to let's say 10, you wouldn't be able to, because it's overruled every time... But even a smaller value would be imaginable (when used in combination with a higher value for the maximum pool size) and therefore should be available/settable via the dialog.

    Do you see any chance this parameter to made "really" editable?

    Additional question: will PDI ever support persistent connections across transformations?

    Thx in advance,
    Ben

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    The initial pool size resetting to 5 will be fixed in 2.5.2/3.0.0-RC1 ... it was a bug.

    For the global connection, I doubt they will be implemented. What you want to do with them is probably not "best practice" ... For the moment I don't see an intuitive way to build it in, you didn't even find the unique connections.

    Regards,
    Sven

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.