Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: is it possible to replay sql (insert, delete & update) using step(s)?

  1. #1
    Join Date
    Apr 2006
    Posts
    1

    Default is it possible to replay sql (insert, delete & update) using step(s)?

    Hi, I am trying (with no success) to execute SQL queries on a target DB. These queries
    come from the source DB (redo logs) and are coming from a SQL Select stmt. The intent is to redo these queries on the target DB. Is this something spoon can do?


    and second question, every steps open and close DB connections when they start/end.
    Is it possible to use one connection from multiple steps?



    Thangs guys



    stef

  2. #2
    Join Date
    Mar 2006
    Posts
    683

    Default RE: is it possible to replay sql (insert, delete & update) using step(s)?

    I'm not sure if I'll be any help on the first question.


    but on the second question, I'll answer it with a question and a statement.
    why would you only want to have only one connection open to the BD for the whole thing?
    Kettle starts processing other steps when it starts getting data from the step before it, so if it was only ever opening one connection then it would have to wait for the first query or step to finish before moving on and this could waste time and since time is money no need for that.



    Does that help at all?



    Have a nice day.



    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  3. #3
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: is it possible to replay sql (insert, delete & update) using step(s)?

    Hi Nic,


    Thanks for you reply.



    I am trying to read the redo log files and to do this I need to run multiple stored procedures before I can finally execute the Select statement that returns the DDL (insert, update and delete) . All of this need to happen using the same connection and in the right order.



    I tried to use the SQL Script for that but it makes spoon crash...
    Any idea?

  4. #4
    Join Date
    Mar 2006
    Posts
    683

    Default RE: is it possible to replay sql (insert, delete & update) using step(s)?

    well the SQl SCript would have been what I would suggest to try.


    are you sure it crashes?
    b/c when I run a SQL script my spoon locks up untill it's done doing it's thing. which at first lead me to think it crashed but it's really doing stuff, and the GUI isn't responding.
    if you for example on mysql ran show processlist you could see that something was going on though spoon looks as if it's not responding. You should have an idea of how it would take for your processdures to run, no? then I would give it that time plus a few minutes before calling it a crash.



    if you mean crash as in disappears that could be due to other things but if you keep an eye the CMD window it opens in windows (i'm assuming your using windows) then it'll let you know if any errors have accorded.



    from my experiences SQL Scripts would be just what you need.
    If you could expand on "CRASHES" that would help.



    I hope that might help.



    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  5. #5
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: is it possible to replay sql (insert, delete & update) using step(s)?

    OK, here is the exception when I execute the following sql script:


    call logminer.config_log_mining();
    select sql_redo from v$logmnr_contents where seg_owner = 'owner' and OPERATION_CODE < 5 and table_name != 'RDL_ANALYZE_AUDIT'



    Spoons completely crash...

    <hr />
    java.lang.StringIndexOutOfBoundsException: String index out of range: 163
    at java.lang.String.substring(Unknown Source)
    at be.ibridge.kettle.core.database.Database.execStatements(Database.java:1536)
    at be.ibridge.kettle.trans.step.sql.ExecSQL.init(ExecSQL.java:216)
    at be.ibridge.kettle.trans.Trans.prepareExecution(Trans.java:380)
    at be.ibridge.kettle.trans.Trans.execute(Trans.java:178)
    at be.ibridge.kettle.spoon.SpoonLog.startstop(SpoonLog.java:424)
    at be.ibridge.kettle.spoon.SpoonLog$5.widgetSelected(SpoonLog.java:320)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:90)
    at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
    at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:843)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3080)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2713)
    at be.ibridge.kettle.spoon.Spoon.readAndDispatch(Spoon.java:472)
    at be.ibridge.kettle.spoon.Spoon.main(Spoon.java:4486)

  6. #6
    Join Date
    Mar 2006
    Posts
    683

    Default RE: is it possible to replay sql (insert, delete & update) using step(s)?

    a couple questions.


    1. what version of kettle are you running? (assuming 2.2.2) (if so try grabbing the kettle.jar from http://www.javaforge.com/proj/doc.do?doc_id=3701 (if link doesn't work then hit up documents->development packages -> kettle.jar) you'll put that in the kettle/lib folder but rename your old kettle.jar to kettle2.2.2.jar or something for bak up)
    2. what db are you running on?
    3. have you tried running just the first one to see if you get an error?



    Thanks,



    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

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

    Default RE: is it possible to replay sql (insert, delete & update) using step(s)?

    Hi,

    Maybe these pieces of information can shed some light on a couple of issues:

    1) To avoid all kinds of bugs, race conditions & the like, we chose the safest solution: open new connections in each thread. This works fine for most problems (not waiting, etc.) but in the future (version 3.0 ETA 2006Q4) we will make an option to have a single connection per database.

    2) SQL Script is executed BEFORE any other step during the initialisation phase of the transformation (files, queries etc get opened) UNLESS you specify the "run for every row" option.

    3) SQL Script allows you to execute Insert/Update/Delete or DDL statements. It probably won&#39;t allow you to execute Direct Procedure Language statements etc. as the statements are separated by ; and there are usually multiple ; in a single program block.

    All the best,

    Matt

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.