-->
 

Database Maintenance

12.      Database Maintenance

Upsizing a FoxPro Database

FoxPro has an upsizing wizard which does a good job of converting a FoxPro database onto SQL Server. The wizard attempts to move as much of the database information as possible onto the server including indexes, defaults, rules and relationships as well as the data itself. A series of reports are generated along with the SQL so that the process can be repeated and tweaked until successful.

It is left to the programmer to create the (parameterised) remote views in a new database container to begin the process of converting the underlying application. In theory the application will work by replacing the tables in the database container with remote views of the same name and using a shared synchronous connection with table buffering.

In practice however, as a minimum you will need to look at the following:

·                  Use shared connections for the remote view.

·                  Add parameters to the remote views to minimise the records returned. You may need to create several views on the same table for different access paths. Keep the fields to a minimum and take care with memo fields.

·                  Review the indexes on the SQL tables and remember that SQL tends to use a single index for selection optimisation. Try to create 'covered' indexes that contain all the fields used to select data.

·                  Be very careful with clustered indexes. The should represent the most frequently used ORDER BY criterion rather than the primary key and try to avoid using a sequence that is the same as the order of insertion (e.g. Invoice Number) for high transaction volume tables.

·                  Use table buffering for greater control over the timing of updates to the server.

·                  Define stored procedures on the database server for regularly called routines that update several records.

Visual FoxPro Upsizing Wizard

The Upsizing wizard is located in the Tools-Wizards-Upsizing menu option and is used to automatically upsize the tables defined in an existing database container.

An ODBC Driver should be defined on the workstation to access the SQL Database before the upsizing wizard is used. It is good practice to plan and create the SQL Database using the SQL Server utilities although the upsizing wizard will  let you create a new database. A SQL Database will usually be 1.5 times the size of the FoxPro database files.

The first few pages of the Upsizing wizard prompt for the following criteria:

·                  Database Container to be upsized.

·                  An ODBC DataSource that has already been setup on the workstation to point to the SQL Server. A valid login password may be required at this point.

·                  The selection of Tables to be upsized.

It is then possible to view all the fields for each table and alter the default field type specified for SQL Server. This is useful for using SQL Server User Defined Datatypes or to conform with standards that existing front-end application software may require.

Adding a Timestamp field will enable the Key and Timestamp updating option to be set for the remote view which improves performance when checking to see if other users have made changes to the record being edited..

The Identity column allows for automatic incrementing primary keys which will create new primary key values without the need for any application level logic.

Mapping Field Types with the Upsizing Wizard

Creating a Database  using the Upsizing Wizard

The upsizing options allow selection of the required components to update. The advanced options allow specification of clustered indexes but should be used with caution.

It is often easier to upsize several times without any data until the process is successful and then append the data into the remote views.

Upsizing Options

The final window allows for the generated SQL to be saved and the upsizing process to begin. The process may take considerable time if there is a lot of data.

A folder called UPSIZE is created and contains a project containing various tables and reports run to view any errors occurring during the upload. The project tables contain details of the upsizing process. Notice the one record table called SQL_UW that contains the SQL used to generate the schema on the database server.

A useful approach is to upsize the FoxPro database without any data and fix any errors before creating views and importing the data by opening the view and using the APPEND FROM command.

Some errors occur because features defined in the FoxPro Database Container are not available in SQL Server. A default value for a field may be determined by a User Defined Function in the local database container. This code may not be upsized onto SQL Server and an error occurs. Another common error is that a fieldname clashes with a reserved word in SQL Server.

Upsizing Error Report

Upsizing Considerations

The defaults used by the Upsizing wizard may not be the best to use in practice.  Some properties of the FoxPro database container may not move successfully into the server database because of differing functionality, and index design criteria may be different for FoxPro and SQL Server optimisation.

Upsized Table viewed in the SQL Enterprise Manager

Some considerations which may be useful when upsizing a FoxPro database are detailed below:

·                  Select the correct format for numeric fields to save on disk space.

·                  Wide text fields with values of different widths may be better stored as VARCHAR fields to save on disk space.

·                  Take care with memo fields which will automatically assign 2K of disk space for each record even if the memo is empty. Make sure Nulls are allowed for memos if a large proportion are empty.

·                  Some field defaults and validations may pass successfully over to SQL Server. Anything remotely complex will have to be rewritten as a Transact-SQL Trigger.

·                  Index design with SQL Server should favour more composite indexes designed around the most common queries rather than the FoxPro design of many independent indexes for each part of the query expression.

·                  Take care when assigning clustered indexes.

·                  Remember to update statistics after the data has been loaded, or the query optimiser will not function correctly.

·                  Referential integrity may be implemented easily in the database server.

·                  FoxPro stored procedures are not upsized and must be rewritten on the server or implemented as View record level validation.

·                  SQL Server cannot index on Bit (logical) fields only.

Data Manipulation Language

ODBC Supports a variety of data manipulation commands that conform to basic SQL standards. This allows for the initial definition of a table and the setting of indexes and primary key features.

ODBC 2.0 drivers have extended these definitions and the Microsoft ODBC Driver for SQL Server 2.5 supports a full set of Database Manipulation commands.

Not all ODBC Drivers will support the full set of data manipulation commands.

The Data Manipulation commands may be passed directly to the ODBC Driver using the SQLEXEC command. A connection handle is first defined with the SQLCONNECT function.

The SQLCONNECT command may be entered at the Command Window as follows. The result is printed on the screen and a -1 indicates there is a problem and the connection has not been made. You are prompted to log into SQL Server when the connection is made.

? SQLCONNECT( 'odbSales' )

 

The error message returned from the server if a SQL command fails may be viewed with the AERROR() command.

Several Connections may be made from one workstation to the server and the correct handle must be used in the SQLEXEC command. The SQLEXEC Command will also return a -1 in the event of failure and passes the command directly to the ODBC Driver.

An index may be created on a remote data source by sending the CREATE INDEX DML command to the driver with the SQL EXEC Command:

? SQLEXEC( 2, ;

   'CREATE INDEX quantity ON sorditem (quantity,unitprice)')

ODBC Supported Data Manipulation commands include the following SQL Statements with a syntax identical or extremely similar to a standard Visual FoxPro statement:

·                  CREATE TABLE

·                  CREATE INDEX

·                  ALTER TABLE

An easy way to determine the required SQL DML command is to create the required structure in Visual FoxPro and then use the Upsizing Wizard to generate the SQL Script only.

ODBC does not like the COLUMN keyword in the ALTER TABLE Command:

SQL Pass Through command can be used to pass statements directly to SQL Server to allow SQL Server specific functionality to be implemented.

This could be used to create a view for example:

? SQLEXEC( 2, [CREATE VIEW CustView AS SELECT * FROM customer WHERE company LIKE 'C%'] )

CREATE TABLE

CREATE TABLE [database.[owner].]table_name

(

   {col_name column_properties [constraint [constraint [...constraint]]]

   | [[,] constraint]}

      [[,] {next_col_name | next_constraint}...]

)

[ON segment_name]

The CREATE TABLE command can be used to create tables using the standard SQL functionality for defining fields but may also be extended to define constraints for the default and simple checks as well as primary and foreign keys and full referential integrity checking.

The following example is used to create the Titles table in the PUBS database and can be found in the INSTPUBS.SQL script. Note that scripts can easily be generated from existing databases to provide a view of the CREATE TABLE syntax.

CREATE TABLE titles

(

   title_id        tid

      CONSTRAINT UPKCL_titleidind PRIMARY KEY CLUSTERED,

   title   varchar(80)     NOT NULL,

   type    char(12)        NOT NULL

      DEFAULT ('UNDECIDED'),

   pub_id  char(4) NULL

      REFERENCES publishers(pub_id),

   price   money   NULL,

   advance money   NULL,

   royalty int     NULL,

   ytd_sales       int     NULL,

   notes   varchar(200)    NULL,

   pubdate datetime        NOT NULL

      DEFAULT (GETDATE())

ALTER TABLE

ALTER TABLE [database.[owner].]table_name

[WITH NOCHECK]

[ADD

   {col_name column_properties [column_constraints]

   | [[,] table_constraint]}

      [, {next_col_name | next_table_constraint}]...]

ALTER TABLE is very useful for adding or modifying the definition of existing Tables. It is particularly useful when writing upsizing programs as it performs many of the tasks that can be carried out using the SQL Enterprise Manager in a program.

CREATE INDEX

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name

   ON [[database.]owner.]table_name (column_name [, column_name]...)

[WITH

   [FILLFACTOR = x]

   [[,] IGNORE_DUP_KEY]

   [[,] {SORTED_DATA | SORTED_DATA_REORG}]

   [[,] {IGNORE_DUP_ROW | ALLOW_DUP_ROW}]]

[ON segment_name]

CREATE INDEX can also be used programmatically to create indexes without using the Enterprise Manager.

GENDBC.PRG

FoxPro ships with an excellent utility called GENDBC which creates SQL DML code from the current open database container and places the results in the specified program file. Much of the syntax is consistent with syntax that can be run on the server with a script or by using SQL pass through.

OPEN DATA tastrade

DO HOME() + 'tools\gendbc\gendbc' WITH 'c:\temp\fred'

MODIFY COMMAND c:\temp\fred

10/24/2014 2:41:33 AM