William: I’ve used the same basic approach Derek describes with good success in the past. It works well to manage the server-side load because all data routes through a single client. The only caution I’d throw out is that you can expect a few of the records being exported (<1% in my experience) to fail somewhere along the export/import process.
The slight variation I used was to embed a prompt in the macro for the Query Bar selection criteria, and would break the exports into chunks by specifying a criterion along the lines of ‘1’ LIKE “%01”. The advantage to this approach is that you can export all the static data in the time leading up to cut-over by adding date qualifications, and then immediately before the new system go-live, you can export any data that’s been modified in the last N weeks/days/hours. Just My $0.02, --Phil From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Derek Berube Sent: Thursday, November 12, 2009 23:12 To: [email protected] Subject: Re: Need to export 12,000,000 or so records... ** I do not know if you would be interested in going a little old school, but I recently had a situation where I needed to export a bunch of data from a production server environment to a reporting server environment. And by a bunch, I mean over 200 million records from six different forms. In this particular environment we use DSO to replicate data from the production server environment to the reporting server environment. Unfortunately, over the past two years there have been occurrences where the reporting application server has gone unavailable and although DSO was configured to retry indefinitely, it didn't. We are turning up an archiving process in the production server environment, but before we started removing data from production we wanted to ensure we had copies in the reporting server environment. Some quick record counts against these forms in the production and reporting server environments revealed that on average, each of these forms had about 20% more data in production then they did in reporting. The "holes" are in different times in each of the different forms. We're running ARS 7.1 patch 7. There are all kinds of ways to address this situation. We could use DSO; however, we are already using DSO to replicate about 800k new and modified records to the reporting server a day based upon current system activity. Given that we've already been told by BMC that we're using DSO in a manner other than it was intended (moving data - go figure), we decided that we wouldn't burden it with pushing 200+ million rows of data. As a general rule, Migrator works very well for us when it comes to moving data. However, it doesn't work so well when you try to tell it to move millions of records in a form. We could migrate data using search criteria, but it would take several thousand "iterations" to queue up that many iterations of Migrator. My first challenge was getting the data out of production, so I decided to leverage good old-fashioned, reliable command line tools. Using the AR System User Tool, I recorded a macro for each of the forms. The macro would: 1. Search for a list of records based whose 'Request ID' field value fell within a specified range. 2. Export all the data the selected records to an .ARX file. I specified $StartId$, $StopId$, and $OutputFile$ parameters in the macro. I wrote a Windows batch file which took two parameters - the $StartId$ and the $StopId$. Some code inside the batch file "built" the value for the $OutputFile$ macro parameter (e.g., INC000000000001_INC000000050000.ARX). For purposes of this discussion, let's say I called this batch file ExportData.cmd. Using SQL developer, I ran a quick query to get the minimum and maximum 'Request ID' values for each of the forms. I then used a little Java program to write another batch file similar to the following: @ECHO OFF CALL ExportData.cmd INC000000000001 INC000000050000 CALL ExportData.cmd INC000000050001 INC000000100000 CALL ExportData.cmd INC000000100001 INC000000150000 CALL ExportData.cmd INC000000150001 INC000000200000 CALL ExportData.cmd INC000000200001 INC000000250000 ... When this program was run, it resulted in .ARX files with about 50,000 records per file. I saved this batch file as BatchExportData.cmd. To import the data, I copied BatchExportData.cmd over to BatchImportData.cmd. Using some Search and Replace code, I changed it to look something like this: @ECHO OFF CALL ImportData.cmd INC000000000001_INC000000050000.ARX CALL ImportData.cmd INC000000050001_INC000000100000.ARX CALL ImportData.cmd INC000000100001_INC000000150000.ARX CALL ImportData.cmd INC000000150001_INC000000200000.ARX CALL ImportData.cmd INC000000200001_INC000000250000.ARX I actually used the ARS 7.5 version of the Import tool because the exports were taken with the 7.5 User Tool. The process of exporting the data took several days. I actually chained them together because I did not want to hammer the production application server environment. The end of each of the BatchExportData.cmd batch files would call the BatchExportData.cmd file for the next form. I had setup a directory-per-form to store the exported data as well as the batch files. I'm still in the process of deleting data from the six forms in the production server environment. Again, this is being done through macros and batch files. I'm simultaneously importing the data to the reporting server environment in another set of batch files. I imagine that the import process is going to take quite some time to execute. When all was said and done, I probably spent about a day writing and testing all of the batch files. It was tedious and low-tech, but it works well and does not require much babysitting. Hope this helps. Derek Derek Berube <[email protected]<mailto:[email protected]>> President/CEO Wildstar Technologies, LLC. (404) 444-5283 - Mobile http://www.wildstartech.com/ AIM Handle: BerubeDB Google Talk: [email protected]<mailto:[email protected]> MSN Messenger: [email protected]<mailto:[email protected]> Yahoo! Messenger: derekberube On Thu, Nov 12, 2009 at 10:28 PM, William Rentfrow <[email protected]<mailto:[email protected]>> wrote: ** Unfortunately...no DSO :) William Rentfrow Principal Consultant, StrataCom Inc. [email protected]<mailto:[email protected]> O 715-592-5185 C 715-410-8056 ________________________________ From: Action Request System discussion list(ARSList) [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Doug Blair Sent: Thursday, November 12, 2009 7:35 PM To: [email protected]<mailto:[email protected]> Subject: Re: Need to export 12,000,000 or so records... ** Bill, Is DSO available? That will work across server versions, allow some field mapping, and (the reason I like it) allow you to keep the data current as the old server is updated before your cutover. Don't do it all in one batch, of course.... if you can log into both servers from the same user tool you could write some workflow which would copy them one at a time too. Fast, no. Cheap, yes.... Doug On Nov 12, 2009, at 3:50 PM, William Rentfrow wrote: ** I have roughly 12,000,000 records I need to export to ARX format from CSS 5.x to do a data migration to IM 7.03. The breakdown is roughly something like this: 3,000,000 archived SPRT:Issue records 1,000,000 Person records 6,000,000 Interaction records 2,000,000 misc related records (active SPRT:Issue records, etc). Since I'd really prefer to NOT do this through the WUT in 100,000 segments I'm throwing this out there for ideas. Changing the format this late in the game is a show-stopper. And we can't use Migrator either. I have found one utility that is called "fast export" but there is no documentation for it and I've found no way to get it to work. So….ideas? Thanks in advance…. William Rentfrow Principal Consultant, StrataCom Inc. [email protected]<mailto:[email protected]> O 715-592-5185 C 715-410-8056 _Platinum Sponsor: [email protected]<mailto:[email protected]> ARSlist: "Where the Answers Are"_ Doug -- Doug Blair [email protected]<mailto:[email protected]> +1 224-558-5462 200 North Arlington Heights Road Arlington Heights, Illinois 60004 Error! Filename not specified. _Platinum Sponsor: [email protected]<mailto:[email protected]> ARSlist: "Where the Answers Are"_ _Platinum Sponsor: [email protected]<mailto:[email protected]> ARSlist: "Where the Answers Are"_ _Platinum Sponsor: [email protected] ARSlist: "Where the Answers Are"_
