The simplest migration scheme is to move all data tables from the AS/400 to the SQL Server, and to move them all at the same time. To do this, the amount of data must be fairly low or the hardware configuration robust enough to allow time for all data to be moved. If there is a larger amount of data but it is to all be transferred, alternating transfers can be used.
Since the Data Distributor will typically move between 30 and 280 MB. per hour, depending on hardware and system loads, it becomes fairly easy to determine if there is enough time to move all tables to the SQL Server on a daily basis. If so, and if no schema transformation is desired, this is the easiest transfer type to set up.
In this scenario, a master batch file will be started, usually during the nighttime when users are not logged in. This master file will in turn start individual jobs running ISQL sessions, which will perform the actual data transfers. A typical master file to transfer only three tables would look like this:
start trancust.bat start tranimas.bat start tranidet.bat exit
All this master file does is to start the three separate sessions which have their own batch files, and then exit. This master batch file is used so that the scheduler only has to start one file rather than three. The three individual batch files would be nearly identical, with TRANCUST.BAT looking like:
isql /Smysqlsvr /Usa /Psa /i\mypath\clrtable.sql isql /Sscodsnt /Uwww /Pbill /i\mypath\trancust.sql pause exit
This batch file merely invokes the character-mode version of ISQL twice. The first time will connect to the SQL Server and clear the table about to receive new data from the AS/400. The second ISQL invocation connects to the Data Distributor and execute the SQL instructions in the script file TRANCUST.SQL. This SQL script is the key to the entire transfer process. All data movement between the AS/400 and the SQL Server is caused by SQL statements in this script file. The TRANCUST.SQL file looks like:
transfer to 'mysqlsvr sa *'; select * from mydb/customer; bulk insert into ssdb..customer; go
To implement a series of transfers occurring on alternating nights requires two master batch files rather than the one shown above. Assuming that equal amounts of data are to be transferred on alternating nights, the mater batch files would invoke transfers to move roughly equal amounts of data.
These master batch files should use the START statement to invoke separate sessions so that the transfers are performed in parallel rather than waiting for the previous one to complete before starting the next. Because of the multi-threading enabled in the Data Distributor by the Windows NT operating system, the total elapsed time to run two transfers in parallel is less than the time to run them serially.
Database organization and layout can have a tremendous impact on query performance. Having indexes already created where needed is probably the most important single element to good query performance. Another performance factor is the amount of database normalization. For the best data integrity in an on-line transaction processing environment, it is generally best to have the data as highly normalized as possible. Unfortunately, highly normalized data generally gives the worst query performance.
For this reason, a transfer statement, including SQL which performs a join on the select side, will result in the combination of two tables into one.
It is not unusual for the queries being run against the SQL Server to summarize the data before outputting results. It would be very unusual for a decision support application to want or need to present any detailed data, since the volume of data is too high. Since the only data needed on the SQL Server is summary data, it makes sense to only transfer the summarized data.
An example might be found in a typical sales history file. If there are 10 entries per day per salesperson, if the data is summarized by day and salesperson, the volume of data to be transferred will be 10% of the original amount. For such a transfer, the SQL could be:
transfer to 'mysqlsvr sa *';
select slsmnid,division,invdate,SUM(invamount) from slshistory
group by slsmnid,division,invdate;
bulk insert into slssummary;
go