The choice to move only changed rows would seem to be a very logical choice. It is not so easy to implement however, so a real need must usually be shown to justify the effort. The most common justification for a changed row transfer is that the amount of data in the underlying tables is just too great to move during off-hours.
Some requests are made to be able to replicate changes from one system to the other in real-time, using two-phase commits or other similar techniques. At the present time this is only possible from the SQL Server to the AS/400 using triggers on the SQL Server tables. Since triggers do not yet exist on the AS/400, this functionality is not yet available.
Even when such functionality is available, as it is partially today, the overhead involved makes its use dubious at best. In the case of using triggers on the SQL Server, each time a row changes, a login must be made on the AS/400 because this change transaction comes through as a separate session. Even if connection caching were in place, the overhead would still be prohibitive.
The use of journals on AS/400 tables is the best way to capture what changes have been made to the data. The technique which seems easiest and safest is to alternate between two sets of batch files and script files while switching the journal receiver back and fourth between two files as well. The technique basically involves switching the journal to a new receiver, use the DSPJRN command to write the just ended journal receiver into an outfile which is a previously defined table with all of the columns in the journal receiver and the data table included. This outfile is then used as the source of a regular Data Distributor transfer, with a SQL Server stored procedure the destination of that transfer.
The stored procedure is needed to branch on the journal code to perform an action on the SQL Server which matches the action taken on the AS/400. In brief, the SQL file would be similar to:
SP_EXECUTE_COMMAND 'CHGJRN JRN(MYJRN)......' SP_EXECUTE_COMMAND 'DSPJRN JRN(.........) OUTFILE(OUTFILEA)......' transfer to 'mysqlsvr sa *'; select jrncode,dcol1, dcol2,dcol3..... from mylib/outfilea; my_journsp ?,?,?,?,?,?,?....; go
Triggers on the tables in SQL Server can be used to either capture changes and send them to the AS/400 immediately, or place them into a temporary transaction table which is then transferred every five minutes, every hour, or however often desired.
As mentioned above, the use of the SP_SQLEXEC stored procedure to send an SQL statement to the Data Distributor as if it is a remote SQL Server will work, but the performance is a problem. In addition, this stored procedure has a limitation of not accepting SQL strings in excess of 255 bytes, which is very restrictive.
As a way of sending changes to the SQL Server to the AS/400 immediately and not having the limitation of a 255 byte SQL string, a remote procedure call (RPC) can be made from the SQL Server to execute a stored procedure on the AS/400. The ShowCase Server code has procedures included to define a stored procedure which will in turn invoke and high-level (RPG, COBOL, PL/1, C++, etc.) program, passing in the input parameters and returning a result set. When using this technique, each input parameter has the 255 byte limitation.
An example of where this might be selectively used would be a remote sales entry system. Field sales people are connecting to the Windows NT system using RAS and entering sales orders into the SQL Server. A transfer can be performed at the end of the day to copy all of the routine sales orders to the AS/400 for processing and shipment. If the salesman has a rush order which must be processed and shipped immediately, he can invoke a special process, through a trigger or through program control on the client PC, which will immediately send the data to the AS/400. Then, this process will start a stored procedure on the AS/400, which will in turn process the order, print the shipping labels and invoices, and complete all other processing for immediate shipment.