Check this box if you wish to retain the functionality of the original Access application utilizing a client-server architecture. When checked, Scriptoria does the following:
Query Conversion:
1) New DB2 tables are created for QueryDefs that reference parameters. These tables contain columns associated with each parameter and a USERNAME column to support multi-user concurrent access.
2) When converting Access QueryDefs, DB2 views are created for all queries that return result sets.
DB2 views created from parameter QueryDefs include the associated parameter table in the FROM clause and MS-Access parameter calls are replaced with associated column references from the parameter table.
3) DB2 Procedures are created from all Action QueryDefs (Insert, Update, Delete, MakeTable).
4) VBA functions are generated for parameter QueryDefs that are used to implement parameter queries.
Front-End Generation:
When generating new front-end applications that operate with DB2, Scriptoria does the following:
1) Creates MS-Access ODBC links to corresponding DB2 tables and views.
2) Create new pass-through queries for Access QueryDefs when corresponding DB2 views exist. See Client-Server Disposition Options.
3) When Access QueryDefs reference parameters and there exists a corresponding DB2 view, a new QueryDef is created that
a. references the linked DB2 view
b. specifies Form and/or user supplied parameters required by the original QueryDef
c. calls the associated VBA function passing the supplied parameter
4) Since Order By clauses are not permitted in DB2 views, sorting is performed by the corresponding generated Access QueryDef.
The generated QueryDef exhibits exactly the same behaviour and functionality as the original, but the SQL is processed on the DB2 server.
When the Retain Access Front-End box is not checked:
1) DB2 procedures are created from Access selection QueryDefs that reference parameters, specify Order By clauses, or utilize the Access TopValues feature (DB2 Fetch n Rows). DB2 views are created for all other selection QueryDefs.
2) DB2 procedures are created for all Action queries.
In order to replicate the behavior of Access parameter queries that operate with DB2 Views, Scriptoria needs to create VBA (Visual/Access Basic) functions. An Access module must be specified to contain these functions. Although any module will work, creating a new module for the parameter query interface functions is recommended.
To enforce company specific IT standard naming conventions, enter the text that is prefix each parameter table name. You can use the following form to assign any valid DB2 table name to associated parameter tables.
Press the button to display the parameter table list and associated DB2 names and table spaces.
The target tables of Access MakeTable queries can be created as permanent or session temporary DB2 tables. Session temporary tables enable concurrent usage without users interfering with each others work.
Note that you must specify a User Temporary Table Space when using session temporary tables.
A regular DB2 table is generated when this option is set. It is assumed that some mechanism is used to ensure users do not inadvertently recreate the table.
Access MakeTable and Append QueryDefs save result-sets to external MS-Access databases when the "Another Database" option is specified. Although this is an appropriate technique for Access development, it is sometimes a sign that the Access database should be re-engineered before converting to DB2.
For example, an application that was originally designed for use by a single company may create new Access databases for each company that purchases the product. This application may also be used by some organizations to track multiple companies. Creating multiple copies of the same tables in different Access databases violates the first rule of relational database design which is to minimize data redundancy. A new design that consolidates all companies into a single database is better suited for conversion to DB2.
In some cases, however, like when storing daily and historical data for instance, the original Access design may be appropriate for conversion by separating daily and historical data into different schemas. To accomplish this you must first convert the external target databases to DB2 specifying different Owner/Schema names for the tables in each external database. You may then open the database that contains the MakeTable and Append queries that create the external tables and use the Assign Schema Names to External Tables form to assign the Owner/Schema names previously specified for the corresponding external databases.
After assigning Owner/Schema names, the DB2 procedures generated by Scriptoria will specify for data to be selected from tables in the current schema for insertion into the tables residing in the schemas you assigned.
Some modifications to the applications that use VBA code to link to the external databases will also be required since ODBC linked objects cannot be refreshed in the same manner as linked Access tables. Some procedure changes may also be required when used in conjunction with Global Temporary tables.
Scriptoria creates DB2 session temporary tables to implement variable column Crosstab queries using the maximum number of columns allowed by either Access or DB2; 250 and 1000 respectively. The default is Access (250). If the DB2 maximum is chosen, the global temporary table cannot be viewed using the Scriptoria Object Viewer since it exceeds the maximum field limit. The pass-through query that implements the variable column Crosstab will operate as long as there are less than 250 fixed plus variable columns.
Note that the first row of the last (max) column contains the select statement provides the final summary required for implementation of the variable Crosstab query. When using Access as the front-end, Scriptoria automatically creates a pass-through query that fully implements the variable Crosstab query.
Choose a user temporary table space from the list large enough to accommodate any session temporary tables generated from MakeTable queries or for tables with very large row size.
Use the Control Center to create a new buffer pool and user temporary table space. The buffer pool is used to set the page size for the table space, which must be increased to 4K or larger to accommodate your session temporary tables.