on 08-21-2007 3:13 PM
Hi, I was wondering if there was any way to make it so XI can handle reading millions of entries from a DB2 query, thanks.
That largely depends upon the XI hardware sizing.
I hope by query u meant SQL query in sender JDBC adapter.
Regards,
Prateek
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
Hey Paul,
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..
Just a thought....but it is possible
Nilesh
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.
Paul,
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.
Regards
Bhavesh
Hey Paul,
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.
Just another thought...if possible.
Nilesh
User | Count |
---|---|
94 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.