QueryDef's that require Access to process data locally can result in network bottlenecks since Access must request that DB2 transfer all rows of table data over the network. Queries that execute on the DB2 server only return data that results from the query's execution, minimizing network traffic. In addition, DB2 performance tuning methods can be applied to objects that have been converted from MS-Access to DB2.
Microsoft Access "Make Table" and "Append" queries are prime candidates for translation to DB2 Procedures. These queries perform table operations that append data to target tables from other DB2 tables.
With Scriptoria, target tables may reside on the DB2 server or in the Access database. Which to choose depends on how the application utilizes the tables.
For example, you may wish to keep target tables in MS-Access if the data will be used repeatedly in subsequent reports that sort, select and group the same data, in various ways. This technique will likely result in less work for the DB2 server and less network. traffic as well
With Scriptoria, you can choose to create DB2 Session Temporary Tables as target tables, or persistent ones. Session temporary tables ensure that users executing the same query do not interfere with each other, since each user gets their own copy of the table. Session temporary table data is also automatically removed when the session is terminated.
After migrating to DB2, all tables, other than the target table, involved in MakeTable and Append operations reside on the DB2 server and are most efficiently performed by the server.
Reporting applications are also great candidates for query translation to DB2 Views. One of the drawbacks of creating DB2 views from Access "Select" queries is that the DB2 view may not be updatable. This is of no consequence to reporting applications.
Access queries that employ non-standard Access built-in functions, like IIf(), Switch, or Val() in WHERE, and HAVING statements, can greatly impede post-migration performance; the Access Jet "Query Optimizer" must request that all table data be retrieved for local processing to evaluate queries containing these functions. Since Scriptoria converts built-in functions to equivalent DB2 functions, greatly improved performance can be achieved by converting Access queries containing special built-in functions to DB2.
Queries are the foundation of most reports and embody the logic of the application. When replacing the MS-Access front-end application, significant investments in Access Query development can be preserved and new re-development averted by using Scriptoria to generate DB2 objects.