Yeah, I want to use a sender CC to run an SQL query that will return millions of records.
I was mostly wondering about whether there is a good way to handle something like this in XI, like a way to break it up so XI will take only 10000 records at a time.
The current system we have uses something called burst mode, where it takes 10000 entries at a time (one burst) and creates one IDoc for every burst.
It is possible to handle this situation using JDBC adapter and stored procedure...
You need to write a Stored procedure which will select "10000" records from database and update the status-flag of selected records.( Probably you need to create new column in database for status)
And your select statment would be select "N" records from database where status <> "X" ... "X" will be setup to status after selecting records from database.. so every time it will select next 10,000 records....
You can call Stored Procedure using JDBC Adapter..
We know we can do that, but I wanted to know if we could do this all from the XI side. I've been informed that the Database people do not want to change the table if possible.
Why not restrict the number of rows selected by the Select Query?
You can just use the Select query to say select N number of records every polling interval. A SQL expert should be able to give you the exact details but this is surely possible and the cleanest solution.
the problem with that is that you would then need to update the db2 somehow every time you selected those N number of records, so those N number of records wont be selected again on the next interval.
This would then get me back to the original problem of having to modify the DB tables in some way again.
In that case, would that not mean XI would be selecting the same data every polling interval.
IF you cannot change the data in the table, every pollinfg interval irrespective of tyhe size of the data you would end up getting the same data selected by the JDBC adapter.
If the DB team has restrictions on DB updations, then you might have to ask them to create a Staging table from which XI can read and update the data , else you will always end up with duplicates in XI.
This is really tricky scenario... there is one more option...but rather than JDBC adapter we can use File Adapter here...
SQL BCP Command allows you to create with "N"number of records file where you can specify starting row and ending row..and you can call this command in Stored Procedure ( which will keep a track on starting row number n endding row number).
You have to write a stored procedure(sp) for creating "n" number of files on application server / ftp server using SQL BCP command..( Writing SP would be tricky again)..
You can call stored procedure using script before calling File Adapter...which will create files from database and file adapter will just pickup.
Also then again you can write a script to delete this files after running file adapter.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.