Count Records from JDBC Sender

[Former Member]

Hi Experts,

I have a requirement to process 80,000 records once a month from MS SQL Server to BW.

After tuning of server and assigning the XBTM queue for the scenario didnt help much and brings down the J2EE engine everytime.

Now i am thinking of processing 5000 records at a time in a BPM (in a while loop). But i somehow need to trap the overall number of records which is not always 80k.

set rowcount 5000 SELECT * table_name where XIProcessed is NULL (SELECT COUNT(*) from table_name)

and then sending an UPDATE of XIProcessed flag to get the next 5000 rows in the JDBC sender.

I somehow need to get the total number of records and the COUNT function will not help as i am getting 5000 rows at a time and not all.

Thanks

[Former Member]

Thanks for your replies.

I need the count of all records to check in the BPM whether i have fetched all the records.

[Former Member]

is there a specific reason you need the overall number e.g. like you need to forward it to BW or is it just to make sure you got all records where XIProcessedFlag value is unprocessed? Because if you have that flag and you do a proper update statement in the JDBC Sender, you can be sure, you'll get all records into XI.

Regards

Christine

[Former Member]

Hi Siva,

why do you attempt counting in the BPM and adding all this overhead? Can't you just do that on the Database directly? In Oracle this is fairly easy as you can use the pseudo column rownum to limit your results. A search for rownum and SQL Server shows, that it is not as easy on SQL Server but there are still fairly simple ways to do this.

Some sources:

http://blogs.x2line.com/al/articles/173.aspx

http://www.databasejournal.com/features/mssql/article.php/10894_2244821_2

http://www.mcse.ms/archive94-2005-3-1502710.html

From a performance standpoint i'm sure, it will work a lot better than any BPM into which you put 80000 records.

Regards

Christine

[Former Member]

Hi,

One round about way would be writing a stored procedure fetching 5000 records at a time or less then that.

If you are updating a flag saying these records are sent then you don't even need a BPM.

Regards

Vijaya