I need to pull some records from a database table using JDBC adapter to webMethods for processing.I am able to do so but the actual requirement is that I need to pull only the 10% of the total records and the remaining 90% records in the second iteration.For example,if the table has a column named “Identity Number” with 10000 records,I need to extract only 1000 in my first attempt and the remaining 9000 in the second attempt.
Please let me know if there is any built-in service or any other possible way for doing that.
There is no built-in for this purpose…basically you have to design the adapter service query (cusotm/dynamicSQL) to query and access the whole recordset in a loop step logic based on the ($iteration count) etc.and process the records that you wanted but this might cause performance issues.or build your query such a way it returns only first 10% records (update a flag column/processed=Y) and remaining records update flag processed=N) and during the next run pull the remaining 90% records(where flag processsed = N and update the flag processed=Y tat the end to avoid duplicates…
Thanks a lot for the reply. But the requirements are such that I can not update any column in the source database.I have to pull the data and push into some other application. That application requires 10% of the data in first iteration and the remaining data in the second iteration.Later I have to schedule the service that will pick up any additional data added to the source database.
Your requirements don’t seem to make a lot of sense. What is the reasoning behind the 10% 90% stuff? As RMG said you would have to keep some sort of indicator or what has been processed or not. Doesn’t have to be in the database but it has to be somewhere otherwise how would you know which “10%” you got?
I would go back to the source of the requirement and challenge it a bit.
Actually I am trying to do this to avoid any performance issues arising due to huge volume of data(approx 0.5 million) during the process.
Can anybody please suggest anyother solution to handle this senario using JDBC adapter.
My requirment is to move huge volume of data from one oracle database to another oracle DB using the webMethods(approximately 0.5 Million) only selected rows after validation. Inorder to avoid performances issues I am looking for the suggestions on this.
I see now, try looking at the JDBC BasicNotification feature (See the JDBC Adapter documentation). It will do what you are looking for.
If the rows are validated one at the time then you can just use the Insert or Update notification. Otherwise the BasicNotification will work. All will let you throttle the total amount retrieved from the buffer table.
If it is a one time thing, you could always just export the data via Oracle tools and import into the other database table.
Yes i agree with Markg and BasicNotification would solve the issue…Normally for this kind of job bulk imports (insert/update) best tool would be via sql client like TOAD or DBVisualizer etc…
Thanks for the suggestion.I thought of using Custom DML as below to extract small no. of records and then process and again extract records in second iteration and so on.
select *
from
(select a.*, rownum rnum
from
(select query…) a
where rownum <= 5000
)
where rnum >= 1;
It perfectly works in TOAD.Please let me know how to use this Custom Query while using JDBC adapter in webMethods.
when you create the adapter service choose customSQL template and define the same way as you did for selectSQL with JDBC FieldTypes inputs/outputs signature and pass the params for the rownum etc…to fetch the resultset.
How about the basic notification as suggested above…
I have never worked on flat file before. But now I am facing problem while developing code for one flat file data source.
In the 'Flat File Definition" I have selected “Delimiter” option since the sample data structure is as below.
But in “Flat File Structure” I have to put start position and end position which will not work since the fields don’t have a constant length.Please suggest how to work with these.Also for the separators I am facing problem since only one character is allowed.I have read the “BuiltInServices” guide…could not find any solution.
As RMG said you would have to keep some sort of indicator or what has been processed or not. Doesn’t have to be in the database but it has to be somewhere otherwise how would you know which “10%” you got?