Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1375
  • Last Modified:

How to fetch large data in batches

Good day.

I have a VB.Net application (.Net Framework 1.1) which fetches data from a DB2 for iSeries database and then writes the fetched data to an SQL Server 2000. I used HitSoftware's Ritmo for iSeries to connect to DB2. The approach I did is to use ADO.Net DataSet.GetXml() which output I pass to a stored procedure for processing (INSERT).

This works ok except when the DataSet is so large I actually get the out of memory exception.

Here are my other challenges:
- I need to know how I can fetch large data in batches using ADO.Net. SQL Server's DTS for example can fetch large data 1000 records at a time. I want to know if this is possible with ADO.Net.
- I need to achieve "all or nothing" which means if there are 50,000 records, I can fetch 1000 at a time untill all 50,000 records are fetched and processed. If I encounter a problem along the way, I can rollback on my SQL Server 2000.
- I need to achieve all these without requiring changes to DB2.

If this is not possible using ADO.Net, does anyone know of something (API or component) I can use in my VB.Net app?

Thanks.
0
etmendz
Asked:
etmendz
  • 10
  • 6
  • 2
1 Solution
 
amyhxuCommented:
I think executing a DTS package may help you solve the problem. The following links give you examples of executing DTS package in VB .Net.
http://sqldev.net/dts/ExecutePackage.htm#Visual%20Basic
http://support.microsoft.com/kb/321525/EN-US/
0
 
etmendzAuthor Commented:
I can't use DTS. Other ways?
0
 
cyberdevil67Commented:
Yes you can page the data...

Now you can do this with the datagrid by setting the properties to allow paging and Page size, or you could write your own functions to do this. The code can be found by doing a search through the msdn for

Paging through a query result

Hope that helps.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
etmendzAuthor Commented:
I am not querying data for display. I am querying data to "replicate" from DB2 direct to an SQL Server. So the datagrid is out of the question, unless... I can use the datagrid without the UI stuffs... Hmmm... you do have an idea thrown in there...

Note that my application is an executable application. Not web. No UI. Just a background process run on schedule. So the questions now are:

- Can I use datagrid (I understand Web.DataGrid has pagination and not Form.DataGrid) in an executable application that has no UI (the executable application has no Form)? If yes, how?
- Can I load paged datagrid data into a dataset so I can simply GetXml() (I want to reuse the stored procedure that INSERTs XML ntext parameter)?

Thanks.
0
 
etmendzAuthor Commented:
A follow up...

If I do go the datagrid paging path, do I really solve the out of memory problem? From some of the codes I see, the datagrid is (re)loaded via dataset. Which means, the datagrid is paging on a dataset. The dataset, therefore, might actually contain the entire query result in memory already.

Hmmm...
0
 
cyberdevil67Commented:
Not if you do you own custom next previous buttons to retrieve the paging..

The idea of setting it to custom pagin is so that you can write your own handling methods for this...
0
 
etmendzAuthor Commented:
So how do I do it (without the buttons of course)? Remember, my VB.Net app is an executable no UI project... I'd really appreciate some lead... Thanks.
0
 
cyberdevil67Commented:
Without UI would make it hard....

So if your not using a UI how do you planb on doing it then?
0
 
etmendzAuthor Commented:
As I've mentioned, I already have a "replication" executable application that already works except that I get the out of memory error when data is very large. So what I need to know is how to fetch data in batches say with ADO.Net or API or component (note that I can't use SQL Server's DTS). Please refer to my question/initial entries in this thread.

I don't want to add any UI to the application.

Any other suggestions?
0
 
cyberdevil67Commented:
Ok then use the pagin scenario and at the end of it do what you need to do then return to get the next page query till there are no more pages left.
0
 
etmendzAuthor Commented:
cyberdevil67, I would appreciate it if you can read my question first before you comment on this thread. You obviously do not understand what I need and you are absolutely wasting my time. I appreciate your efforts but I don't think your lead is going anywhere. I'd rather that you prove your ideas within the context of my requirements first. Thank you.
0
 
cyberdevil67Commented:
Ok

I understand what your doing, however when you fill a dataset with a dataset from a conection you obviously return all records which is causing you problems.

1) Find out how many records there are, to find out how many pages you need.
2) Retrieve x amount then do you prcocessing
3) Do we have any more pages of data.
4) Repeat steps 2-3 until all paged queries have been returned.

If you did a search on MSDN for paging query results you can apply this to your code, if you refuse to acknlowedge this or see how you do it then I can't help you anymore.

I use the above method to return 1000 records at a time from a database that has over 200,000 records in them and this method works for me.
0
 
etmendzAuthor Commented:
Thank you. I really hope there are other ways.

My replication application replicates more than 10 different tables from more than 5 countries. Your suggestion would require me to add COUNT queries for all of them (with the same filters and conditions as the actual query). Two calls for the price of memory.

Another problem is that the DB2 source tables may have char keys and even compound char keys. I also have tables where the keys are foreign keys only -- one to many. How do I make sure that I am really getting the next records and not repeating the set I got earlier? DB2 SQL is pretty limited and I'm not sure I know how to create dyanmic ROWIDs which I can use as row/page markers (and if DB2 SQL can support it).

Sigh...
0
 
cyberdevil67Commented:
Well I guess the answer not always evident.

Sometimes you need to do extra work, to create a solution to save extra time. Anyway the thing is that the method I suggested would have worked, but it looks like your query will not.. And that is the problem, your query.

Sorry I can't help you any more, I am not a DB2 guru and I suspect that if you can't use a query to do what your looking for then it will not work.

Another Solution would be to invest in a SQL Compare or SQL Diff program and replicate them that way, not sure if they work on DB2 or not though.

Sorry I couldn't help you more here.
0
 
etmendzAuthor Commented:
The pagination approach described in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconPagingThroughQueryResult.asp might work indeed but I need a DB2 query technique to dynamically generate ROWIDs.

I think PL/SQL supports it.

This http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133 describes an approach with T-SQL.

I wonder how with DB2...

I'll leave this open for a while in case there are more ideas...
0
 
amyhxuCommented:
Why can't you use DTS? One of your database is SQL, then you can create a DTS package in SQL.
0
 
etmendzAuthor Commented:
The reason we moved up to using VB.Net application is because it was difficult for us to maintain DTSs for more than 5 countries with more than 10 tables each. Because DTS mapping requires live connection to the source database, updating the queries for the many countries and tables is a slow and time consuming process (if you've ever experience editing DTS packages that connects to remote database servers across the Asia Pacific region, you'll know what I mean).

Also, the countries may use different languages (Chinese, Korean, Thai, English, etc.). To retain the characters correctly, DTS requires us to stage replicated data to collated tables before we actually move them to the target database.

VB.Net allowed us to maintain the replication parameters easier and faster using XML configuration files. VB.Net also removed the staging process -- no collation needed; just make sure the target field is nvarchar...

That's why I can't use DTS. This VB.Net application is suppose to replace the DTS approach.

:-)
0
 
etmendzAuthor Commented:
cyberdevil67,
That's it. No more takers. It's a good help. Not complete but you took the time and gave some leads... Thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 10
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now