Pentaho

 View Only

  • 1.  PDI keeping databases configuration in two places .ktr and .kdb files

    Posted 08-25-2022 05:34
    Hi,

    This thread is related to this thread Problems upgrading MSSQL's JDBC to 11.2 and how to set properly encrypt=false in the JDBC URL

    I want to upgrade MSSQL JDBC to the latest version 11.2 but since 10.2 there is a breaking change:
    Release notes - JDBC Driver for SQL Server



    Default encrypt to true BREAKING CHANGE TLS encryption is enabled by default.

    So I cannot just replace the old JDBC with the latest one as the connection are going to fail for old servers that do not support encryption.

    This can be solved by adding "encrypt=false" to the connection Options. The problem I see here is that PDI keeps the info in two places: in the .kdb files and in the .ktr files.

    When the .kdb is updated the following is added in the .kdb file

    <attributes>
    <attribute><code>EXTRA_OPTION_MSSQLNATIVE.encrypt</code><attribute>false</attribute></attribute>
    [...]
    </attributes>

    And if I open the database related step in the .ktr it updates it automatically by adding this:

    <attributes>
    <attribute>
    <code>EXTRA_OPTION_MSSQLNATIVE.encrypt</code>
    <attribute>false</attribute>
    </attribute>
    <attribute>

    Changing the .kdb is easy, the problem I see here is that in order to update the .ktr files I have to open all of them, open the related step and saving it ( a very time consuming, prone to error task). There is a XML replace option but it can be dangerous.

    Has anyone faced a similar challenge?
    Is it enough to just update the .kdb files? I mean .kdb will be default value and the options in the .ktr will be like specific values for it.

    Thanks for your time

    Best regards



    ------------------------------
    Juan Sierra Pons
    Systems Engineer
    Juan Sierra Pons
    ------------------------------



  • 2.  RE: PDI keeping databases configuration in two places .ktr and .kdb files

    Posted 09-30-2022 07:49
    Hi Juan,
    where is trans stored? if you use any type of repository (file, db or PUR), you can define shared.xml file with redefined DB connection. And after reimport all affected trans or jobs to repository.
    In Spoon, in context menu of DB connection is option "Share". File shared.xml is created in .kettle directory in your home.

    If you have trans stored in BA server, connection defined in trans is ignored and used definition on server selected by name of connection.

    ------------------------------
    Petr Prochazka
    Systems Engineer
    P.V.A. systems s.r.o.
    ------------------------------

    Original Message


  • 3.  RE: PDI keeping databases configuration in two places .ktr and .kdb files

    Posted 10-03-2022 01:18
    Hi @Petr Prochazka,

    I have all the jobs and trans stored in a file repository. Can you please give me more details about the "And after reimport all affected trans or jobs to repository." part?

    I don't fully get it

    Thanks for your time

    Best regards


    ​

    ------------------------------
    Juan Sierra Pons
    Systems Engineer
    Juan Sierra Pons
    ------------------------------

    Original Message


  • 4.  RE: PDI keeping databases configuration in two places .ktr and .kdb files

    Posted 10-03-2022 07:21
    For import to repository is script import.sh|.bat (for more info about parameters run without any parameter).

    So you have to export your trans|jobs to single repository file. This can do in Spoon, Tools -> Repository -> Export repository or Tools -> Repository -> Explore -> context menu on any directory and call Export.

    Copy or create shared.xml in user home who call import script. Content of file is for ex.:
    <?xml version="1.0" encoding="UTF-8"?>
<sharedobjects>
  <connection>
    <name>BOSS</name>
    <server/>
    <type>MSSQLNATIVE</type>
    <access>JNDI</access>
    <database>BOSS</database>
    <port>3050</port>
    <username/>
    <password>Encrypted </password>
    <servername/>
    <data_tablespace/>
    <index_tablespace/>
    <attributes>
      <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute>
      <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute>
      <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute>
      <attribute><code>MSSQL_DOUBLE_DECIMAL_SEPARATOR</code><attribute>N</attribute></attribute>
      <attribute><code>PORT_NUMBER</code><attribute>3050</attribute></attribute>
      <attribute><code>PRESERVE_RESERVED_WORD_CASE</code><attribute>N</attribute></attribute>
      <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute>
      <attribute><code>SUPPORTS_BOOLEAN_DATA_TYPE</code><attribute>N</attribute></attribute>
      <attribute><code>SUPPORTS_TIMESTAMP_DATA_TYPE</code><attribute>N</attribute></attribute>
      <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute>
    </attributes>
  </connection>

</sharedobjects>

    When call import, then is loaded this shared.xml file (or is not specified direct in trans) with connection definition and it is stored to all imported trans or jobs.

    I hope this information is useful. :-)



    ------------------------------
    Petr Prochazka
    Systems Engineer
    P.V.A. systems s.r.o.
    ------------------------------

    Original Message


Related Content