3 Replies Latest reply on May 27, 2019 8:29 AM by Nico Heinze

    Time stamp with Time Zone: Precision length becomes 0

    Prasenjit Patra New Member

      Hi Informatica Support Team,

       

      1. A. Issue:

      a) The below table is created at source Oracle 11.2.0.10 DB

      CREATE TABLE SRC_TZ(TZ_SRC_COL TIMESTAMP WITH TIME ZONE);

       

      b) ODBC is created using Data Direct 7.1 Oracle Protocol and
      registry entry is verified. Refer screen print#1.

      c) After import the precision of the TIMESTAMP WITH TIME ZONE
      became 0.

      1. B. Analysis:

      We checked the below URL

      https://kb.informatica.com/whitepapers/1/Pages/14714.aspx

       

      We confirmed the source definition of TIMESTAMP WITH
      TIMEZONE is becoming zero not Varchar(36). Please let us know what setup needs
      to be done at ODBC

       

      Refer screen print#2

       

       

      1. C. Pain Points:
        We have TIMESTAMP WITH TIMEZONE fields at 90 tables and the definition is
        getting modified very frequently. It is very difficult to update the precision
        manually every time.

       

       

      Please share an ODBC parameter or Informatica setting which
      will set the Precision length of varchar2(50).

       

      Thanks,

      Prasenjit Patra

        • 1. Re: Time stamp with Time Zone: Precision length becomes 0
          Nico Heinze Guru

          First Informatica software doesn't support TIMESTAMP WITH TIME ZONE at all, so you better change all those attributes to Timestamp anyway.

          Second you can do that automatically once you have imported all those tables.

          Export them all to a XML file and use some code (may be Perl, Python, Basic, a PowerCenter mapping, Java, C... whatever you like or can handle) to look for those TIMESTAMP attributes with a length of 0; change the length in the XML file to 29,9; then re-import those source/target definitions into the PowerCenter repository.

           

          Granted, that's only an idea, I haven't tried it myself yet. But I do know that quite a few people had to follow similar approaches in order to adapt VARCHAR/VARCHAR2 attributes to appropriate lengths when using Unicode strings.

           

          Regards,

          Nico

          • 2. Re: Time stamp with Time Zone: Precision length becomes 0
            Prasenjit Patra New Member

            Hi Nico,

            Exporting all the impacted tables in an XML,finding and replacing all VARCHAR(0) to greater precision VARCHAR column and again re-importing the tables in informatica is basically a manual work.

            To avoid manual work, as per your suggestion, we can use some code (may be Perl, Python etc) to look for those TIMESTAMP attributes with a length of 0.

            But, we are in search of a better way out where we can use any ODBC parameter for converting TSWTZ column directly to VARCHAR(64) or with greater precision VARCHAR column.

            Could you please suggest the smae

            Thanks,

            Prasenjit

            • 3. Re: Time stamp with Time Zone: Precision length becomes 0
              Nico Heinze Guru

              Unfortunately I cannot. My suggestion was meant as kind of a "workaround" in case no one else can give a better answer to your question (and I still hope someone will do). Of course it would be better to avoid this scripting work, no question.

               

              Good luck and regards,

              Nico