Integrating SQL Databases

2000

Integrating SQL

Databases

Converting to a SQL database

Presented By: Mike King

Copyright © 2000 by Sage Canada Ltd. All rights reserved. No part of this publication may be reproduced, or transmitted in any form or by any means, electronic, mechanical, photocopied, recorded or other, without prior written consent of Sage Canada Ltd.

2

Presentation Overview

§

Review of ODBC interface

§

Using the PREFIX FILE

§

Record formatting options

§

Implementing different file types

§

Emulating non-normalized data files

§

The ProvideX INI file [ODBC] section

§

Optimization

This presentation is intended to assist dealers who may be interested in migrating their existing data files to a SQL-based database, such as Microsoft SQL Server, ORACLE, Informix, or Sybase.

The major points that will be covered are: • The ODBC interface

• Using a PREFIX file to simplify the implementation

• How to define different types of ProvideX style files to the ODBC/SQL interface

• Record formatting options that can be used to format the record layouts • Include packed data and binary data

• Definitions for Fixed length fields and Variable length fields • How to define Variant record formats for data files which have different record layouts. (These are referred to commonly as NON-NORMALIZED files.) • Review of the ODBC options available within the ProvideX INI files

The ODBC Interface

§

Common interface for a variety of

database systems

Ÿ

Microsoft SQL

Ÿ

ORACLE

Ÿ

INFORMIX

§

Based on SQL

§

Windows environment only

ODBC provides a standard interface for passing information between applications and a wide variety of databases. Using ODBC, ProvideX can interact with any database technology that has an ODBC driver by using a standard set of functions.

The interface is based on SQL. In order to interact with the database, ProvideX passes SQL commands to the ODBC administration system which passes them along to the individual database drivers, as required.

To use ODBC on a Windows PC, you must define a Data Source Name (DSN) which relates a logical DSN to its associated driver and configuration parameters.

For example, when connecting to Microsoft SQL Server, you need the name of the host system, the database name, an appropriate userid, and password. There are a variety of other parameters that can be specified as well. The ODBC DSN is used to define these values (host, database, etc.). The minimum that the ODBC DSN for SQL Server must contain is the name of the host system, since all of the other parameters can be specified at run time.

Primarily, ODBC is a Windows environment functionality. There are a variety of non-windows implementations of ODBC on the marketplace. Only the standard Microsoft Windows ODBC interface is standard with ProvideX. We have interfaces available by special request for other ODBC-like interfaces, such as UDBC from OpenLink and CLI from Informix.

4

The ODBC Interface

§

Direct access using the standard file

I/O directives and functions

§

ProvideX generates SQL directives

Ÿ

No need to learn SQL

• System Generates it Automatically

§

Eases migration of applications to

ODBC accessible Databases

ProvideX uses the standard file Input/Output directives and functions to interface with ODBC and database(s). Most of the standard ProvideX directives are supported fully within the ODBC interface and can be used with ODBC files, as if they are standard ProvideX files.

Supported Directives:

OPEN (…), CLOSE (…), READ (…), WRITE (…), REMOVE (…), Also supported, but requires the use of a PREFIX file:

LOCK(…), UNLOCK(…) Supported Functions:

KEY(…), KEL(…), KEF(…), KEP(…), KEC(…), RNO(…), IND(…) Supported Options:

KEY=, KNO=, IND=, RNO= Unsupported Directives/Functions:

ERASE, REFILE, PURGE, KEN(…) Functions that may be incompatible:

FIB(…), FIN(…), FID(…) (appear as ODBC files)

In order to ease the transition, ProvideX generates the SQL commands

automatically. This means that there are few application changes and little SQL training required for the most part. SQL must be used for file/table creation and deletion.

The OPEN directive is used to establish a logical connection between ProvideX and the database. The pathname and option string defines the database (DSN), the name of table to be accessed, and a variety of data formatting and processing options.

It is possible to open a logical connection to the Database only by not specifying any table name. In this case, the program can submit SQL

directives directly using the WRITE RECORD directive. Results returned from the database can be obtained using the READ directive, with each column that is returned separated by the SEP character.

Normally, the pathname contains a variety of file access and formatting options as well. These options are used to define information regarding how to access the table to ProvideX. Typically, this includes the definition of the key fields, record formatting characteristics, and the details regarding variant record processing for non-normalized files.

Additional options can provide information such as Userid and password, database qualifier name (database name within server), record locking characteristics, etc., to the ODBC connection.

Because the size of the string needed to pass this information to ProvideX can become quite long and, due to the fact that ProvideX limits file pathnames to a maximum of 255 characters, options can be specified in the OPT=<string> for the OPEN. Options provided in the OPT= string are not treated any differently than those options passed in the pathname.

Opening ODBC connections

§

OPEN path and OPT= string defines the

logical relationship to the Database table

Ÿ

Options are separated by semi-colons

Ÿ

Options can be either in the pathname or in the

OPT= string

• Use of the OPT= string avoids the 255 byte limitation for file pathnames

6

Typical OPEN

§

Example:

OPEN (n,OPT=“KEY=Cst_id; REC=Cst_id+Cst_name,Cst_owes”) “[odb]STARSYS; Customer”

Ÿ

Where:

• STARSYS is the DSN name for the database • Customer is the table name within the database • Cst_id, Cst_name, Cst_owes are fields from table

The above example shows an OPEN directive that: • Connects to the DSN STARSYS.

Note: Only System and User DSNs are supported by the ProvideX ODBC interface. File based DSNs are not supported.

• Establishes that the table to be accessed is called Customer. This must be a valid table in the database.

• Defines the primary key as the field CST_ID

• Defines a record layout consisting of columns CST_ID, CST_NAME, and CST_OWES.

The columns CST_ID and CST_NAME will be concatenated together to form the first field in the record, with the column CST_OWES as the second column.

For clarity, we suggest that only the database and table names be provided in the pathname, with the other options being specified in the OPT= string.

A number of errors can occur during the open. Some of the more common PVX error conditions are:

Error 10 - Invalid pathname An invalid combination of options has been specified.

Error 23 - Missing variable A column name not in the table has been specified.

Error 12 - File does not exist Could not locate the specified table in the database.

KEY= Columns This option defines the columns to be used as keys. Commas should separate each column name. The first KEY= option defines the primary key, the second defines the first alternate key (KNO=1), etc…

IND= Column If present, this option indicates that the ODBC connection is to be treated like an INDEXED file with the column specified containing the record index. The specified column must be numeric.

REC= Columns/Literals This option defines the record format. (See next slide) KEYDATA= Column This option is used to define a column that contains the

primary key but the column does NOT exist in the logical record data. (See Direct files)

RECDATA= Column This option is used to define a column that contains the complete record contents. It can be used when the data content has no fixed format.

STRIP/NOSTRIP Indicates that trailing spaces are to be stripped from the data by default.

OPEN options

§

Options that control data representation

§

Options used to define variant records

§

Options to control the ODBC interface

All options in the OPEN directive are separated by semi-colons. Unrecognized options are ignored.

The following table lists the options that control the data formatting and logical keys for the file:

8 The following table lists the options that control the actual ODBC interface:

ACCESS SQL_ACCESS_MODE READ SQL_MODE_READ_ONLY WRITE SQL_MODE_READ_WRITE

This option controls the type of file access required. The default setting is WRITE. AUTOCOMMIT SQL_AUTOCOMMIT OFF SQL_AUTOCOMMIT_OFF ON SQL_AUTOCOMMIT_ON

This option controls the auto commit functionality of the database driver. It is applicable only if the driver supports Transactions. CURSOR_USE SQL_ODBC_CURSORS DRIVER SQL_CUR_USE_DRIVER ODBC SQL_CUR_USE_ODBC IF_NEEDED SQL_CUR_USE_IF_NEEDED

This option controls the type of cursor to be used within the ODBC connection. The default setting is to use the specific driver’s own cursors. Specifying ODBC causes the ODBC interface to use the “Driver Managers” cursor library that may provide additional functionality not available within the database driver.

IF_NEEDED tells the system to use the specific database driver's own cursor functionality unless the additional functionality is requested specifically. ISOLATION SQL_TXN_ISOLATION UNCOMMITED SQL_TXN_READ_UNCOMMITTED COMMITED SQL_TXN_READ_COMMITTED REPEATABLE SQL_TXN_REPEATABLE_READ SERIAL SQL_TXN_SERIALIZABLE VERSIONING SQL_TXN_VERSIONING

This option controls the isolation that this connection will have relative to other processes on the same database. In particular, it controls Dirty reads (reading data that may be rolled back –‘D’), Non-repeatable reads (reading data after changed by other transactions – ‘R’), and Phantom reads (reading data newly added to file – ‘P’). The settings are:

UNCOMMITED - D, R, P possible

COMMITED – D possible, R & P not possible REPEATABLE – P possible, D & R not possible SERIAL – D, R, & P not possible

VERSIONING – D, R, & P not possible, but uses versioning as opposed to record locks

CONCURRENCY SQL_CONCURRENCY READONLY SQL_CONCUR_READ_ONLY LOCK SQL_CONCUR_LOCK OPT_VERSION SQL_CONCUR_ROWVER OPT_VALUE SQL_CONCUR_VALUES

This option controls the type of con-current access control/locking to be used. If

READONLY is specified, then the cursor is set to read only – no updates allowed. If LOCK is specified, then low level record locking is applied. OPT_VERSION causes optimistic locking with the database version control to be used. OPT_VALUE causes optimistic locking with comparing record/column values to be used. CURSOR_TYPE SQL_CURSOR_TYPE FORWARD SQL_CURSOR_FORWARD_ONLY STATIC SQL_CURSOR_STATIC KEYSET SQL_CURSOR_KEYSET_DRIVEN DYNAMIC SQL_CURSOR_DYNAMIC

This option defines the type of cursor that is to be used. FORWARD indicates that any result sets can be read in a forward only direction. STATIC indicates that the result set is static. KEYSET forces the cursor to use/maintain record keys in a KEYSET. DYNAMIC indicates that the cursor is effective in the current

ROWSET only. KEYSET_SIZE

SQL_KEYSET_SIZE

<value> This option defines the size of the KEYSET for use with the CURSOR.

MAXROWS

SQL_MAX_ROWS

<value> This option can be used to limit the number of rows/records returned.

ROWSET_SIZE

SQL_ROWSET_SIZE

<value> This option defines the size of the ROWSET used by the cursor.

The following table lists the options that control the actual ODBC interface:

TIMEOUT

SQL_QUERY_TIMEOUT

<value> This option defines the timeout value for any SQL operation (time before error 0 returned). USER <userid> This option specifies the userid to be used for

the database connection.

PSWD <password> This option specifies the password to be used. DB (or

QUALIFIER)

SQL_CURRENT_QUALIF ER

<databasename> This option can be used to qualify the specific database that you wish to use when using a driver to service multiple databases.

SHARE If specified, then the system will attempt to share the database connection with other logical connections to the same DSN. This option is available in the Professional version ONLY. UNIQUE This option forces ProvideX to not share this

connection with other logical connections to the same DSN.

PREPARE Y -or- N If set to Y, then ProvideX generates

parameterized SQL statements that are pre-compiled. This improves performance of files accessed by key.

TOP <value> If specified, then ProvideX uses the TOP clause in all SELECT statements, where possible. If this option exists, then the KEF and KEL functions issues a SELECT TOP 1 SQL statement, which improves system

performance. If the <value> is > 0, then PVX issues SELECT TOP <value> to reduce the data transferred.

EXTROPT <string> This option is used to control the format of the SELECTstatement used to process an

EXTRACT. By default, PVX generates a SELECT * FROM <table> FOR UPDATE WHERE … If specified, then <string> is substituted in place of FOR UPDATE. In addition, if the first character of <string> is $, then the remaining characters of <string> are placed at the end of the SELECT statement rather than after the file name. This allows for different variations of SQL to be supported. ORACLE Y / N This option indicates if the database uses

ORACLE SQL sequence. If ORACLE and TOP are used, then SELECT commands are

generated as SELECT * FROM (SELECT * FROM TABLE) WHERE ROWNUM < 1. MAS90DT Format DATE Fields as MAS90 style dates. FACTSDT Format DATE Fields as Facts style dates. TSQL <SQL statement> This option can be used to define a SQL

statement that is used to control what data the logical file returns. (See optimization tips.) EXEC_SPRNO If specified, then PVX attempts to issue an

EXEC SPRNO_tablename <rno> statement to read a record using its logical record number. If the stored procedure fails, then the system will revert back to standard RNO processing.

10

Defining the Record Layout

§

REC= option defines the record layout

Ÿ

Simple format is:

• REC= <fieldspec> { ,

-or-

+ } <fieldspec> …

• Fields separated by either a comma or plus sign

s If Comma-separated, then a field delimiter is inserted

s If Plus-separated, then the field is padded to full size and no separator is inserted

• <fieldspec> contains the name of the field and

optional format, length, and scale

• Literals may be included, if enclosed in

apostrophes

The REC= phrase is used to control the formatting of the data record as viewed by the ProvideX application. The format consists of a series of field descriptors and/or literals, each separated by either a comma or a plus sign. (Record type indicators can be present within the REC= phrase as well, but these will be discussed later in the presentation.)

If a comma is used to separate two entries, then the resultant output record will contain a field separator. If a plus sign is used, then the first field specified will be padded to its full length and be followed immediately by the second field.

For Example:

REC=CST_ID, CST_NAME, CST_ADDR

This results in a record with three fields, each separated by a field separator.

REC=CST_ID + CST_NAME + CST_ADDR

This results in a record consisting of the fields with each one padded to its full length with no intervening field separator. For example, if CST_ID is 6 characters long and CST_NAME and CST_ADDR are both 30, then the resultant record would be 67 characters long.

Defining the Record Layout (continued)

Any column name can be followed optionally by a colon and a format specification. This format specification consists of an optional data type followed by the field length and, if numeric, a decimal point followed by the number of decimal positions.

The possible data types are:

P - Packed (BIN) data H - Data is stored in HEX B - Data is a Binary field D - Field is a Date

Some examples would be:

CST_ID:7

CST_OWING:8.2 (8 digits with 2 decimal places)

CST_AMT:P4.2 (4 bytes containing BIN value scaled by 100)

CST_NAME:B30 (30 byte binary field)

It is desirable to include the field descriptions for all fields since it prevents ProvideX from having to read the table’s Data Dictionary to determine the field sizes and types.

The Hex and Binary values can be used to store non-printable and/or binary data which would cause problems otherwise when passed in a SQL statement.

Binary fields (type P) can be used to define numeric data that has been packed into a string using the BIN and DEC functions. If specified, the scale indicates the number of implied decimal places that the value contains.

Literals may be inserted within the record layout as well, in order to insert padding where a field or column is not presently used, but space has been reserved for it. Literals should be enclosed with apostrophes and separated by a comma or plus sign.

12

Non-Normalized Files

§

Allows different formats based on content

§

TYP= clause defines the field(s) used to

determine the record type.

§

A ‘?’ in the REC= clause defines the value

• Special masking options for the ? Value

s Period ‘.’ specifies any character

s [abc] indicates any of ‘abc’ (use ‘-’ for range as in [0-9])

s [ ] indicates end-of-field

s ^ indicates records that DON’T match

Non-normalized files are a critical aspect of a number of applications. These are files which use multiple record formats to contain the data used by the application. Applications developed by many Business Basic developers have a number these types of files. Unfortunately, this type of data structure is not supported in a normalized database, such as SQL Server.

In order to migrate these files to a SQL-based database, the table contents have to be normalized. The ProvideX ODBC interface has a mechanism built into it to convert data from a SQL normalized file to appear as a non-normalized file. The approach used is to define one column for each potential field which can exist in all the different record layouts within the table that contains the ProvideX data file contents. Then, we define to ProvideX the record layout to be used based upon the contents of one or more fields.

In order to accomplish this, ProvideX must know which fields/columns contain the data needed to determine the record format to be used. The column(s) are

declared in the TYP= option. If multiple columns are required, each column must be separated by a plus sign as in:

TYP=CST_ID+CST_TYPE

Then, define the values to be returned by the contents of the fields defined in the TYP= clause within the REC= clause. These values are identified by question marks followed by the contents to match against the fields.

Non-Normalized Files (continued)

The contents of the value to match can contain a number of special characters that are designed for simplified matching:

. (period) Matches any character

[abc] Matches any of the characters a. b, c

[0-9] Matches any of the characters between 0 and 9 [ ] Matches end-of-string / No data

^ If the first character is a ‘^’, then it indicates to select records that DON’T match the string following the ‘^’. Ie. ‘?^PROD’ selects anything that doesn’t match ‘PROD’.

The column names specified in the TYP= clause can be followed by a comma and a length specifier, if you only want the first ‘nnn’ columns used in the match. For example, the clause TYP=CST_ZIP,3 would only check the first three positions of the field CST_ZIP.

A typical example of a non-normalized file where each record has a three character prefix on the key field definition follows:

File: GCOMP (two record types “G/L” and “DPT”) All record: Prefix Key 3 char

Company Key 2 char

Id Key 10 char (Dept or Acct #) Name 35 char (Dept or G/L name)

“DPT” rec: Restriction_flag 1 char

“G/L” rec: Control_acct 1 char Stmt_A_Line 4 numeric Stmt_B_Line 4 numeric Subaccount_flag 1 char Inventory_acct 1 char Print_zero 1 char Definition: [odb]JONAS_DB;GL_Dept_master; KEY=Prefix,Company,Id; TYP=Prefix; REC=?”DPT”,Prefix,Company,Id,Name,Restriction_flag,?”G/L”, Prefix,Company,Id,Name,Control_acct,Stmt_A_Line,Stmt_B_Line, Subaccount_flag,Inventory_acct,Print_zero

14

Using the PREFIX FILE

§

PREFIX FILE is used to map existing

file OPENs into ODBC OPENs

Ÿ

Can contain pathname and options

Ÿ

Prefix file is a DIRECT file whose key is the

filename as given by the program

Ÿ

Record has two data fields

• 1st field contains actual pathname to use • 2nd field contains the OPT= string

By using the PREFIX FILE integrated into ProvideX, you can map existing file OPENs directly into ODBC opens. The PREFIX FILE consists of a keyed file where the key is the file name as it appears in the program. The data records consist of the actual pathname to use in the first field and the option list in the second field. You can insert “[ODB]DatabaseName;TableName” (using the actual values) into the first field of a PREFIX FILE and the options that you desire into the second field.

For example, in MAS200 the ARF terms code file is described as:

File: ARFABC.SOA

Fields: TermsCode 2char (concatenated with) Description 30char (concatenated with) DaysBeforeDue 3char (concatenated with) DueDateADayOfMonth 1char (concatenated with) DaysBeforeDiscDue 3char (concatenated with) DiscDateADayOfTheMonth 1char (concatenated with) MinDaysAllowedInvDue 3char (concatenated with) MinDaysAllowedDiscDue 3char (concatenated with) DiscountCalcMethod 1 char

DiscountPercentage numeric 19.7 Key: TermsCode

Using the Prefix File (continued)

KEYED “PFXFILE”,12 OPEN (1) “PFXFILE”

WRITE (1,KEY=“ARFABC.SOA”)

“[odb]MAS90MFK;ARF_TermsCodeMasterfile;DB=MAS_ABC;”, “KEY=TermsCode; REC=TermsCode:2+ Description:30+

DaysBeforeDue:3+ DueDateADayOfMonth:1+ DaysBeforeDiscDue:3+ DiscDateADayOfTheMonth:1+ MinDaysAllowedInvDue:3+

MinDaysAllowedDiscDue:3+ DiscountCalcMethod:1, DiscountPercentage:19.7”

CLOSE (1)

Generally, we suggest that the first field contains the “[odb]MAS…” DSN/Table declaration and the second field contains the record layout.

Once this record is created, you can enable your PREFIX FILE and access the SQL table in your application by OPENing the file “ARFABC.SOA”

->PREFIX FILE “PFXFILE” ->OPEN (1) “ARFABC.SOA” ->PRINT PTH(1)

[odb]MAS90MFK;ARF_TermsCodeMasterfile;DB=MAS_ABC; ->

You should create a PREFIX FILE with a record size large enough to accommodate the largest file definition string that your application requires. A Prefix file with a record size of 4000 bytes is not uncommon.

16

Handling File/Record Locks

§

File Lock functionality does not exist

Ÿ

ProvideX emulates using PREFIX FILE

§

Record locking provided by SQL Server

Ÿ

Does not lock records on same connection

• EXTRACT on one channel does not block access on the second channel

There is no equivalent functionality to File locking available directly within the SQL Server. To get around this issue, ProvideX uses a series of locks placed against the PREFIX file which emulates the LOCK / UNLOCK functionality.

When an application opens a ODBC connection to a database table using a PREFIX FILE entry, a lock byte within the PREFIX FILE is set based on the file entry and a process identifier. When a LOCK is issued, then all lock bytes associated with the file are set preventing further access to the file.

In order for this process to work, all applications must use the same PREFIX FILE and it must remain active as long as any ODBC file is open.

Record locking in Microsoft SQL Server and other SQL based systems is slightly different in that records EXTRACTed on one channel can be accessed on another, if they share a common database connection (i.e. Use the SHARE ODBC option).

Defining a KEYED file

§

You need to provide:

Ÿ

DSN name

Ÿ

Table name

Ÿ

Record format

• Optional, if each field matches the database

definition

Ÿ

Keys using the KEY= option

• First KEY= is KNO #0, Second is KNO #1, …

The most common file format within ProvideX applications is a KEYED (or DIRECT) file where the key for the record is contained in the record data. These types of files can be defined directly as tables with each data field defined as a single column in the database.

The KEY= clause can be used to define the field(s) that comprise the record key. If more than one key is required, then multiple KEY= clauses can be included for each alternate key required.

The following is an example of the definition for a KEYED file:

File: ICALPX

Fields: Company 2 char

Alpha_sort 10 char

Item_num 20 char

Stocked 1 char

Update_Inventory 1 char Key 1: Company, Item_num

Key 2: Company, Alpha_sort, Item_num

Key 3: Company, Stocked, Alpha_sort, Item_num

Key 4: Company, Update_Inventory, Alpha_sort, Item_num

Key 5: Company, Stocked, Update_inventory, Alpha_sort, Item_num

18

Defining a KEYED file (continued)

Definition: [odb]FACTS_DB;IC_Item_Alpha ;

REC=Company, Alpha_sort, Item_Num, Stocked, Update_Inventory; KEY=Company, Item_num; KEY=Company, Alpha_sort, Item_num; KEY=Company, Stocked, Alpha_sort, Item_num; KEY=Company, Update_Inventory, Alpha_sort, Item_num; KEY=Company, Stocked, Update_inventory, Alpha_sort, Item_num

This would declare a KEYED type of file with one primary and four alternate keys.

Defining an INDEXED file

§

You need to provide:

Ÿ

DSN name

Ÿ

Table name

Ÿ

Record format

Ÿ

Record INDEX field

• MUST be a numeric field in Database

• Should be 8 digits in length - no decimal points

While SQL does not support an INDEXED-style table structure typically, ProvideX can emulate this file structure using a numeric field within the table as the record index. This numeric field should contain a minimum of 8 digits with no decimal points.

The following is an example of the definition for an INDEXED file:

File: PODABC.SOA

Index field: IndexSql Numeric 8

Fields Rec#0: RecordsActivelyUsed Numeric 19.7 MaximumRecordsInFile Numeric 19.7 NextNewIndexAvailable Numeric 19.7 LastRemovedIndexRecord Numeric 19.7

Fields other: StdMessageLine1 50 char (concatenated with) StdMessageLine2 50 char

Definition: [odb]MAS90MFK;POD_POMessage;DB=MAS_ABC; IND=IndexSql:8;

TYP=IndexSql,1,8;

REC=?"00000000"+ RecordsActivelyUsed:19.7, MaximumRecordsInFile:19.7, NextNewIndexAvailable:19.7, LastRemovedIndexRecord:19.7+

?"..."+ StdMessageLine1:50+ StdMessageLine2:50+ ' '

20

Defining a DIRECT file

§

Basically the same as a KEYED file

Ÿ

If the

External Key

does not exist as

field(s) within the record data, then you

must specify the KEYDATA= clause

• KEYDATA= must specify a single field • This field is the primary key of the record • Field must NOT appear within the REC= fields

Most DIRECT files can be defined using the same format as a KEYED file. However, if the key for a DIRECT file is not contained within the data columns, then a special OPEN syntax is required. This requirement is due to the fact that ProvideX normally attempts to generate the record key from the contents of the record data columns.

The KEYDATA option is used to define the column name for the data that contains the record key. In many cases, it will be used when the key is not derived directly from the data in any logical manner or when the file is non-normalized and the key components vary from record type to record type. The column specified in the KEYDATA clause must not exist in the REC= clause.

Defining a Direct file (continued)

The following is an example of the definition for a Direct file whose key is not made up of column data:

File: ARWABC.SOA

Fields “F” rec: RecordNo 1char (concatenated with..) FromDivision 2 char (concatenated with..) FromCustomerNumber 7 char (concatenated with..) ToDivision 2 char (concatenated with..) ToCustomerNumber 7 char

Fields “T” rec: RecordNo 1char (concatenated with..) ToDivision 2 char (concatenated with..) ToCustomerNumber 7 char (concatenated with..) FromDivision 2 char (concatenated with..) FromCustomerNumber 7 char

Definition: [odb]MAS90MFK;ARW_GlobalCustRenEntry;DB=MAS_ABC; KEYDATA=KeySql:B10;

TYP=RecordNo,1,1;

REC=?"F"+RecordNo:1+ FromDivision:2+ FromCustomerNumber:7+ ToDivision:2+ ToCustomerNumber:7+

?"T"+RecordNo:1+ ToDivision:2+ ToCustomerNumber:7+ FromDivision:2+ FromCustomerNumber:7

Basically, the key for this file is the record as defined, thus the actual columns used to create the key, vary between the “T” and “F” records.

22

Defining a SORT file

§

You need to provide:

Ÿ

DSN and Table name

Ÿ

Record format

• Fields must be concatenated with the ‘+’

operator

Ÿ

Specify KEY=*

• This indicates that the KEY is the record

• No record data will exist logically

SORT files pose a different problem to the ProvideX interface, as there is no data record but just a key field.

To define a SORT file, the table definition should contain the columns that are required to construct the key with a REC= clause which defines the layout of the KEY. (Normally, this requires the use of the PLUS sign between all the fields.) Use a KEY=* option to indicate that the record itself is the key and that no record actually exists.

An example of an OPEN definition for this type of file follows:

File: ARCALX

Fields: Company 2 char

Alpha_Sort_Key 10 char

Customer_Num 10 char

Definition: [ODB]FACTS_DB;AR_Cust_Alpha ;

REC=Company+Alpha_Sort_key+Customer_Num ; KEY=*

ODBC Section in PVX.INI

§

All options can be set in [ODBC] section

Ÿ

Simplifies common settings

§

Additional option:

DEBUGIT = <string>

Ÿ

Appends <string> program name and

statement number to all SQL commands

• If using MS SQL Server, then set <string> to

--To avoid having to specify common ODBC settings, an ODBC section can be added to the PVX INI file. This section can contain any of the settings that you wish to use as a default during your session.

Settings established in the INI file can be overridden in the OPEN specifications.

MAS200 uses the following ODBC settings in their INI file:

EXTROPT=(UPDLOCK) UNIQUE=0 CURSORCLOSE=YES TOP=-1 CURSOR_TYPE=DYNAMIC TIMEOUT=5 ISOLATION=COMMITTED CONCURRENCY=OPT_VALUE

There is a special Debugging option available in the INI file only. If you include the option DEBUGIT = <string>, then, whenever a SQL command is generated and passed to the ODBC server, the value in <string> followed by the current program name and line number will be added to the end of the statement. Using a value of “--” changes the program information to be considered a comment by the Microsoft SQL server.

24

Optimization Tips

§

Performance can be improved signifigantly

Ÿ

Results vary drastically between applications

Ÿ

Some options to consider:

• PREPARE=Y option reduces SQL compilations

• TOP=nnn option reduces transfers for KEF/KEL

s Try TOP = - 1 is using MSSQL Server or ORACLE

A lot of performance issues depend very much on the application and how it

references the databases. However, there are a number of options that can be used to improve system throughput in some instances.

The PREPARE=Y option causes ProvideX to create a parameterized SELECT statement that can be compiled once and then re-used many times. Use of this technique can improve system throughput.

An example of the parameterized statement is:

SELECT <fields> FROM CUSTOMER WHERE CST_ID = ?

(A Question mark is used to denote the location of the parameter within the statement.)

ProvideX will create and pass this statement to the ODBC system on the first read using the primary key along with the location where the driver can find the value of the parameter field. Subsequent reads simply change the location of the parameter and have the driver re-execute the SQL statement. No further compilation of the SQL is required.

The TOP=nnn directive can be specified for any driver that supports the TOP= clause for the SELECT statement (Microsoft SQL server). If TOP is present, then the system will limit the number of records retrieved when issuing a SELECT. This minimizes the number of unneeded reads that the system will perform.

A value of -1 can be used to indicate that the ODBC driver supports this syntax. This can be extremely important if your application uses KEF or KEL functions frequently, since it ensures that a single read only is executed for these functions.

Optimization Tips

§

If you know SQL, try…

Ÿ

TSQL=stmt option allows server to pre-process

data and reduce traffic

Ÿ

EXEC_SPRNO

option with stored procedure

improves RNO speed

Ÿ

Directly issue SQL commands or use Stored

procedures

The ProvideX interface is designed to provide a fairly efficient but generic interface to any SQL- based database while minimizing the amount of code changes within your application. If you are willing to make application changes, then there are a number of additional ways that you can use SQL directly in order to improve performance.

The TSQL option allows you to create your own SQL command that will be used when issuing simple READ NEXT’s against a database. For example, if you wanted to get all the customers with an outstanding account balance and the file CSTFILE was defined in the PREFIX FILE, then you could specify:

OPEN (1, OPT=“TSQL=SELECT * FROM CUSTOMER WHERE CST_OWES>0”) “CSTFILE”

If this logical file was read sequentially, then it would return only those records which matched the criteria you specified. This would reduce the data transfer to your application effectively because the database server would filter out the records that you don’t need.

The existing logic, which checks CST_OWES for a non-zero value, could remain, but the application would see only the records which had a non-zero value.

26

Access by RNO (continued)

The ODBC does not have functionality that is equivalent to access by RNO. To accommodate this, ProvideX use a SELECT COUNT ( * ) WHERE KEY < “….” for the RNO function and a SELECT * followed by sucesive reads to position to a specific record to satisfy the RNO= functionality. While the RNO function is relativity fast, access by RNO= can be very slow.

The EXEC_SPRNO option can be used if your application makes extensive use of accessing the file using RNO. If specified, then ProvideX will issue the SQL stored procedure invocation command:

EXEC spRNOttttttttkkkk nnnn

Where:

tttttttt is the name of the Table

kkkk is either blank, if using the primary key, or an underscore followed by the key number

nnnn is the record number desired

For Example:

READ (1,RNO=100) -> EXEC spRNOCustomer 100 READ (1,RNO=123,KNO=3) -> EXEC spRNOCustomer_3 123

The Stored procedure should return all the columns as defined in the table.

The following is a typical Stored procedure:

CREATE PROCEDURE [spRNOCustomer] (@rno int) AS BEGIN

SET NOCOUNT ON

DECLARE @CustCode VARCHAR (6)

DECLARE TempCursor CURSOR DYNAMIC FOR

SELECT CustCode FROM Customer ORDER BY CustCode

OPEN TempCursor

Fetch Relative @rno from TempCursor INTO @CustCode CLOSE TempCursor

DEALLOCATE TempCursor

SELECT * FROM Customer WHERE CustCode = @CustCode

Review

Ÿ

Most applications can be migrated

• All basic ProvideX file types can be implemented

Ÿ

Minimal code impact for basic Implementation

performance

Ÿ

Biggest obstacle is logistics

• Database Creation, Deletion, and Installation • Data Dictionary is a MUST

28

End of Presentation