Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Documentation about the Logging functionality and possiblities

  1. #1
    Join Date
    Feb 2010
    Posts
    10

    Default Documentation about the Logging functionality and possiblities

    Hi All,

    sorry, that i ask this, is somewhere a Documentation about the Logging Functionality and how the best practice is. Because i should log when a transformation starts, succesfully end and so on. What i saw is in the settings the logging tab , but there i not found how i get the time in.

    I searched here for hours and was propably to stupid to find the solution ;-),

    thanks a lot for your help,

    kind regards
    Alex

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    If you use a Transform (or Job) Log in a Database, then the Start and End times are automatically logged.

    Go to Transformation Settings, Logging Tab, enter a connection, and then a table name. Click the SQL button to see what fields will be in the log table.

    An Example:
    Code:
    CREATE TABLE TransformLog
    (
      ID_BATCH INT
    , TRANSNAME VARCHAR(255)
    , STATUS VARCHAR(15)
    , LINES_READ BIGINT
    , LINES_WRITTEN BIGINT
    , LINES_UPDATED BIGINT
    , LINES_INPUT BIGINT
    , LINES_OUTPUT BIGINT
    , ERRORS BIGINT
    , STARTDATE DATETIME
    , ENDDATE DATETIME
    , LOGDATE DATETIME
    , DEPDATE DATETIME
    , REPLAYDATE DATETIME
    )
    ;

    Hope that helps!
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  3. #3
    Join Date
    Feb 2010
    Posts
    10

    Default

    Hi Gutlez,

    thanks a lot for this information, i used it, but i still have some problems. first i converted the datetime (MSSQL) to timestamp / Varchar2 (oracle). Then i wanna join all this logging tables, but without any success. I have a doall job whiche starts other transformations and jobs. So i enabled all the loggings in this jobs and transformations, but now i dont see how i can join this diffrent tables. I tried but failed everytime. i tried to join job, job entry, transformations an step together but its not working. I wanna create a view with all dies informations, because we create a Jobmonitor that collect all informations during the job is running...

    kind regards
    Alex

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    So your log table is populating then?

    If it is, then you can write your reports based off the data in the table.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  5. #5
    Join Date
    Feb 2010
    Posts
    10

    Default

    Yes its populating, sometimes i have a problem with the datatype date, because wanna store the hole thing like datetime in MSSQL but i have oracle, then i change the datatype to varchar2(50), but sometimes i get an error that he cant convert string to date, but it seems to me, that he wanna check tke latest logdate and then i cant convert from string to date... but more problematic is that i cant see how i get the recursion into the select statement, because i wanna creat a Report like JOB / STATUS / START / END / TRANSFORMATION / START / END / LINES_READ / LINES_WRITTEN / LINES REJECTED / STATUS. I have some ideas and try this first. But probably you can help me with one problem, why the startdate ist the 1.1.1900 and not the correct startdate and time?

    Thanks a lot for ur help and sorry that i came late with the answer....thanks so much

  6. #6
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    I don't recall, however I think the first time it writes to the log DB, it goes from 1/1/1900 to <RunTime-1stRun>
    Next time it runs, the data is for <RunTime-1stRun> to <RunTime-2ndRun> etc.

    For the LINES_ columns, you need to tell your transforms / jobs which step to use to record those lines (ie. LINES_READ from the pull from DB may be different from LINES_READ from the DB Write)

    When you go to write your report, you convert the date/time to whatever you need for your report.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Please note that version 4.x contains descriptions for each column as well as the "Lineage and Auditing" chapter of our "Pentaho Kettle Solution" book.

  8. #8
    Join Date
    Feb 2010
    Posts
    10

    Default

    Hi i allready ordered the book ;-), but the Pentaho Kettle Solution book i can order in November, i found nowhere where i can have this book earlier, then i ordered this book
    Pentaho 3.2 Data Integration: Beginner's

    I hope i get some informations about logging too....
    Last edited by aljechin1973; 09-29-2010 at 05:56 AM.

  9. #9
    Join Date
    Feb 2010
    Posts
    10

    Default

    hm strange, because i allways tought that the startdate ist the startdate when the Job or Transformation starts or not?

    If i use the sql button in the logtab then he use the date datatype for the startdate, but with this datatype he will truncate the time and i havent a MSSQL DB, because there you can use the datatype datetime and in oracle you dont have this. But i check what i can do...

  10. #10
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Amazon already delivered the copies that I pre-ordered so I strongly doubt you have to wait until November. Whatever may be, thanks for the support!

    There is also older but relevant information available from the wiki on the subject of Auditing and operation metadata.

    I allways tought that the startdate ist the startdate when the Job or Transformation starts or not?
    No, it's the start of the validity date range that you can use for incremental loading. See the wiki page linked above or the description in the 4.x logging table screens.

  11. #11
    Join Date
    Feb 2010
    Posts
    10

    Default

    Thanks a lot, i asked again my book store and they told me it would be earlier, i hope so ;-), but no problem.
    Thanks a lot for your help and i will check thie site and let you know whats happen...

    kind regards

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.