PDA

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



kettle_stef
04-27-2006, 08:49 AM
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

guzaldon
04-27-2006, 09:09 AM
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

kettle_anonymous
04-27-2006, 10:52 AM
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?

guzaldon
04-27-2006, 11:08 AM
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

kettle_anonymous
04-27-2006, 11:32 AM
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)

guzaldon
04-27-2006, 11:44 AM
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

MattCasters
04-28-2006, 02:52 AM
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