Hi,
Is it possible to use stored procedures as a cursor input source? Further, can I pass parameters to the stored proc?
Thanks a lot for your help,
Stefan
Printable View
Hi,
Is it possible to use stored procedures as a cursor input source? Further, can I pass parameters to the stored proc?
Thanks a lot for your help,
Stefan
You can use any sql statement, so you can use functions in your select if you like.
As a side note, if you want to optimize it is in general much faster to define proper views and base your select on that. This gives the dba the proper hooks to optimize and it prevents you from doing something silly like writing a loop instead of a select.
Hi Iwein,
Thanks a lot for the reply. Can you please elaborare more on how to pass parameters to the stored proc. I am new to Spring Batch and based on the documentation I have seen (the project commiters should really focus more on writing documentation ) the query is specified in the configuration file. How am I going to be able to pass stored proc parameters from a shell script for example.
Use case scenario: The cursor input source (described as a stored proc) will receive a date as input parameter from a shell script, will select required data based on that input date and process each data item in a transaction.
Can I do this?
Again many thanks for your help,
Stefan
The real question you need to ask is "where does the imput parameter come from?" The approach we take is that input parameters are part of the identity of a job, so they have to be persisted with the job meta-data (as the JobIdentifier). If your parameter is a date then with m3 your best choice is the date field in ScheduledJobIdentifier. Make your input source / item reader StepContextAware and use that to pull down the JobExecution and its enclsuing JobInstance and JobIdentifier. Or see the BatchResourceFactoryBean for an alternative approach, which you could adapt to a custom FactoryBean that delivered runtime parameters without the business loginc having to know about the StepContext.
When you say 'cursor input source', are you referring to the JdbcCursorItemReader (InputSource) If so, I'm not sure I would advise using a stored procedure with it, although, it depends upon exactly what it is doing. Usually, when I've seen a stored procedure used, the entire batch job is the stored procedure, which is usually done for performance reasons. I could go into details as to why this might not be the best solution in certain scenarios if you like. Either way, I would create a custom ItemReader for your Stored Procedure.Quote:
Originally Posted by phanae
As a side note, do you have to use stored procedure's because of some type of policy, or was it a design decision for your particular job?
This seems like an overly convoluted way to do the very simple task of getting input parameters into a script.
We would have to write a custom ItemReader that mimicks the functionality of an existing ItemReader to be StepContextAware and to pass the parameters properly into the query.
Couldn't we add a simpler mechanism for grabbing job identifier info, eg. JobIdentifierAware (or, more user-friendly, InstanceParameterAware) to directly inject all of the relevant parameters directly, without having to deal with the step context.
JIRA Issue Added: http://jira.springframework.org/browse/BATCH-282
I'll leave discussions about how to inject step scoped data with the jira issue. However, I will say that regardless of the approach for obtaining your runtime parameters (JobInstanceProperties) You will need to code your own ItemReader implementation. The CursorItemReader is designed for just that, opening a cursor and processing each row, one at a time. I'm not sure how (or why) it would support anything more. With that being said, we have talked about creating a thin wrapped for Stored Procedures (it's a bit low priority right now though), but it would be more of a specialized implementation of a Tasklet than an ItemReader.
Yes, stored procs are a design decision from which I do not want to deviate.Quote:
Originally Posted by lucasward
What I basically want to do is to come up with a generic multithreaded item processor. For this particular system I want to be able through Spring Batch to call a controller stored proc which will return pages of item data to be processed. Each such data item will then be processed by a separate item handler running it's own thread. I am new to Spring batch but from what I read so far the framework seems to provide such features.
I do not want to do all the data item processing in one stored proc which results in a non-multithreading computation ( Dave Syer mentioned Spring framework itself provide such thread pool implementation which can be used in conjunction with Spring Batch)
Regards,
Stefan