Query Conversion Overview

In the following discussion, Access queries that return data result sets (Select, Union, and Crosstab) are referred to as "Selection" queries. Access MakeTable, Append, Delete, and Update queries are termed "Action" queries and do not return any data.

By default, Scriptoria implements Access queries using the linked tables they include. Although linked QueryDefs are updatable, network and database performance will usually improve, sometimes dramatically, by creating Access pass-through queries based on DB2 views. Pass-through queries however, are not updatable.  

Select, Union, and Crosstab Queries

MS-Access "Selection" queries are converted to DB2 Views or procedures. Views are created for all selection queries when the "Retain Access Front-End" option is chosen; when not retaining the Access  front-end, DB2 Procedures are created for QueryDefs when statements, like Order By are encountered are the query contains parameters. Views are generated for all other selection QueryDefs.

Crosstab queries that return a variable number of columns; i.e., those that do not supply Crosstab Column Headings. DB2 procedures are generated to implement these run-time dependant queries. A minimal amount of manual effort is required to incorporate run-time dependant Crosstabs into Access front-end applications.

MakeTable, Append, Delete, and Update Queries

Converting Action queries to execute on the DB2 server improves performance and operational control.

Action queries are converted to DB2 Procedures.

MakeTable and Append queries may be optionally implemented using DB2 SESSION TEMPORARY TABLES. The DB2 session temporary tables are created from Access tables that are generated by running the MakeTable query. The DB2 session temporary tables are local to each user session. This means that each concurrent user has their own copy of the table, thereby preventing users from interfering with each others work.

A DB2 stored procedure is generated that implements the logic of Access Append and MakeTable queries and populates the session temporary table with data from other tables.

Bulk Updates

After converting MakeTable and Append queries to DB2 permanent table, it may be desirable to create a script with calls to the converted procedures which can be scheduled to run overnight. It may also be appropriate to include Delete and Update queries in the script depending on your application.

The Client-Server page of the Create DB2 Application form contains an option to replace the original Access Action queries with pass-through queries that call the converted DB2 procedure.  There is also an option that instructs Scriptoria to remove MakeTable and Append queries from the converted application.

Scriptoria Assigned Names when Generating Applications

When Scriptoria links DB2 tables and creates pass-through queries by appending a suffix to the end of Access names as follows: