INFO: Differences between DataGate/400 for DB2/400 (DG400) and DataGate for SQL Server (DSS)

The information in this article applies to:

Windows
Acceler8DB, Release 5.0
DataGate for Windows and Desktop, Release 7.0, 7.1, and 7.2 Version 7.254 and lower
DataGate/400, Release 5.0 and Higher
DataGate/SQL (DSS), Release 5.0 and Higher
Microsoft SQL Server 2000
Development
Deployment

SUMMARY:

This article contains information regarding the Differences between DataGate for DB2/400 (DG400) and DataGate for SQL Server (DSS).

STATUS:

Current.

MORE INFORMATION:

NOTE: The information contained in this article is a "living" document. The contents contained in this preliminary document can be changed by ASNA at any time. Please make sure that you obtain the latest document for the most current information.

This purpose of this information is to help you plan for SQL Server 2000 support. Differences between DataGate for DB2/400 (DG400) and DataGate for SQL Server (DSS)

1. - Object Considerations

Item	DataGate/400	DSS	Acceler8DB Engine
Library & file name length	10 characters	31 characters	31
Members per file	0 ® *NoMax	Exactly 1	0 ® *NoMax
File types	Physical Simple logical Join logical Multiformat logical Print	Physical SqlLogical Simple logical Join logical Print	Physical Simple logical Join logical Multiformat logical Print
Max record length	32,000 bytes	8,060 bytes (not counting Text and Image fields which are not accessible yet by DSS)	32,000 bytes
Max number of records per member		2,147.483,646	2,147,483,646*
Library implemented as:	Library	Database	Illusion
Object text (description)	49 characters	49 characters	49 characters
Stored Procedures	Any AS/400 language	Programmed in SQL-Transact	None
Triggers	Any AS/400 language	Programmed in SQL-Transact	None
Field Reference File (FRF)	A physical file can refer to any number of FRF, which are any physical file in any library. However, DataGate/400 will report only those coming from the file stated in the DDS REF keyword.	Refers to the collection of ‘User Defined Data Types’, which is one per Database (i.e.: Library). This collection is surfaced via the special file ‘FieldRef’ which is the ONLY* file usable as a FRF.	A physical file can refer to only *ONE* FRF, which can be any physical file in any library.

* NOTE: For Max number of records per member, DataGate for Windows and Desktop Servers Release 7.2, Version 7.255 and higher support member/file sizes limit is 16 exabytes ( 2^4 * 2^60).

2. – Index (Keys) Considerations

Item	DataGate/400	DSS	Acceler8DB Engine
Indexed logical files per physical file		249	*NoMax
Logical field used as a key field must be based on a physical filed with the same name	No	Yes. Notice that this eliminates the possibility of using Renamed, Concatenated and Substringed fields as keys.	No
Maximum number of key fields per key		16	250
Maximum length of key in bytes	2,000	900	250

3. – Data Access Considerations

Item	DataGate/400	DSS	Acceler8DB Engine
Arrival Access:	Relative Record Number is used for Sequential and Random access.	Only Consecutive access is supported but there is no guaranteed order of retrieval unles the file is indexed. The only random operation allowed is SetLL and only when used with Start and End. No other kind of seeking (SetGT, CHAIN) is allowed.	Relative Record Number is used for Sequential and Random access.
Format Name	Given by file creator	Always ‘R’ followed by File Name. Note to AVR Users: The Format can be renamed in the DclDiskFile, using the RNMFMT keyword, by providing a new name, is not necessary to provide the existing Name in the RNMFMT. This allows the creation of single-source apps that can compile agaist DataGate/400 and DSS.	Given by File creator
Open Query File	Implemented with OpenQry.	Select expression is used as the WHERE clause of a SELECT. The key field list is used as the ORDER BY clause. The select expression is passed directly to the SQL analizer with no interpretation; the expression must follow valid SQL Server syntax. Pay special attention to uses of logical operators: use 'and' and 'or' not '&' and '\|'.	A temporary logical file is created using the select expression as a select/omit expression and they key field list to define the new key.

4. – Locking Considerations

Record Locking

DataGate/400

DB2/400 determines the type and duration of records locks depending on how the file was opened.

For read-only files, when a record is read, there is no lock requested on it, and if some other application has the record lock, the reading application does not block on the lock, that is, the record gets read in-spite of being locked by somebody else.

For files open for update, every time a record is read it is write-locked so that other updating applications can’t read it. The write lock is held until the record is updated or explicitly unlock by the application or when another record is read or positioned to.

DSS

DSS (using server cursors) also determines the locking characteristics bases on how the file is opened.

For read-only files DSS behaves like DataGate/400, that is, there are no locks neither placed nor considered on records being read.

The behavior of DSS when the file is opened for update is similar to DataGate/400 but with two significant differences: updating a record does not release the lock on the record and explicitly unlocking a record causes the ‘current record position’ to be lost. These differences bear the following considerations.

Item	DataGate/400	DSS
Unlock Record	Cursor position is unchanged	The file has no ‘current’ position after the Unlock
Update Record	The record just updated is released	The record just updated is kept locked
NoLock option on Read* operations	Supported but deprecated.	Unsupported. The better way to achieve this is to open the file twice, once for input only and the other for update. Where the read appears with the *NoLock option, the file should be substituted with the one open for input only. By doing this, the application can take advantage of network blocking yielding better performance.
Range operations	When the end of the range is reached, the file has no ‘current’ position.	When the end of the range is reached, the file has no ‘current’ position.
Hit EOF on a ReadE (P)	Lose Record position	Lose Record position
Other Operations like SetLL	Unlock Record	Unlock Record

Loops involving SetLL/SetGT and Read/ReadE/ReadPE should be recoded to use the Range operations.

The most damaging change, is the one requiring segments of code involving CHAIN-UPDATE combinations have to be studied and possible modified.

If the CHAIN-UPDATE happens in a tight loop, then at the end of the loop an UNLOCK should be issued to release the last record updated. Notice however that the record position will be lost after the UNLOCK.
If the CHAIN-UPDATE is sprinkled throughout the code, then each case has to be closely studied.

Object Locking:

Not implemented yet on DSS

5. - Field considerations

Item	DataGate/400	DSS	Acceler8DB Engine
Field name length	10 characters	31 characters	31
Types supported	Char ® CHAR Packed ® PACKED Zoned ® ZONED Binary ® BINARY Float ® FLOAT Integer ® INTEGER Date ® DATE Time ® TIME Timestamp ® TIMESTAMP Hex ® HEX DBCS ® DBCS Unicode ® DBCS Boolean ® *CHAR(1)	Char ® char Packed ® decimal Zoned ® numeric Binary ® numeric Float(4) ® float Float(8) ® real Integer(2) ® smallint Integer(4) ® int Date ® ASNA_DSS_DATE datetime=00:00:00* Time ® ASNA_DSS_TIME datetime=1899/12/30* Timestamp ® datetime Hex ® binary DBCS ® nchar Unicode ® nchar	Char ® CHAR Packed ® PACKED Zoned ® ZONED Binary ® BINARY Float ® FLOAT Integer ® INTEGER Date ® DATE Time ® TIME Timestamp ® TIMESTAMP Hex ® HEX DBCS ® DBCS Unicode ® DBCS Boolean ® *CHAR(1)
Allow Nulls	Yes	Yes	No
Variable Length Fields	Char Hex DBCS	Char ® varchar Hex ® varbinary DBCS ® varnchar	No
Date value range	0001-01-01 ® 9999-12-31	Datetime (ASNA_DSS_DATE): 1753-01-01 ® 9999-12-31 01-01-01 maps to 1753-01-01 Smalldatetime: 1900-01-01 ® 2079-06-06	0001-01-01 ® 9999-12-31
Decimal Number Storage	Packed (1 nibble per digit) Zoned (1 bye per digit) Binary: 1 – 4 digits = 2 bytes 5 – 9 digits = 4 bytes	Decimal \| Numeric: 1 – 9 digits = 4 + 1 bytes 10 – 19 digits = 8 + 1 bytes 20 – 29 digits = 12 + 1 bytes 30 – 38 digits = 16 + 1 bytes	Packed (1 nibble per digit) Zoned (1 bye per digit) Binary: 1 – 4 digits = 2 bytes 5 – 9 digits = 4 bytes
Date storage	1 byte per digit/character	Datetime 8 bytes ASNA_DSS_DATE 8 bytes SmallDatetime 4 bytes	1 byte per digit/character
Fields per file		1,024	32,000
Re-typing logical fields	Unrestricted	Logical fields who’s type differs from that of the corresponding physical field can not be updated	Unrestricted
Column Heading Definitions	Up to 3 31-characters	The 3 headings are concatenated into the MS Access CAPTION field	Up to 3 31-characters
Text Description	Up to 49 characters	Up to 49 characters	Up to 49 characters

Native SQL Server field interpretation:

Numerics

Date/Time

Char/Other

Float ® *Float (4)

Real ® *Float (8)

Int ® *Integer (4)

SmallInt ® *Integer (2)

TinyInt ® *Integer (2)

Decimal ® *Packed

BigInt ® *Zoned(19,0)

Money ® * Zoned(19,4)

Numeric ® *Zoned

SmallMoney ® * Zoned(9,4)

DateTime ® *Timestamp

SmallDateTime ® *Timestamp

Bit ® *Boolean

Char ® *Char

VarChar ® *Char (VarLen)

NChar ® *Unicode

NVarChar ® *Unicode (VarLen)

Binary ® *Hex

VarBinary ® *Hex (VarLen)

UniqueIdentifier ® *Hex (16)

The types Image, Text and NText are not supported. Fields of these types are hidden from the file definition. To ensure future application compatibility, you should not use files containing these fields, instead you should create logical files naming the individual fields that your application will manipulate, that way, if in a future release the fields ‘appear’, your application will not break.

6. – Join Considerations

Item

DataGate/400

DSS

Acceler8DB Engine

Supports Use Default for Joins by:

DDS Keyword JOINDFLT??

When a record is not found in the secondary file, logical fields whose base is that file will be populated with the default values specified in the physical file definition.

Creating a Left Outer Join instead of an Inner Join.

From SQL Docs:

LEFT JOIN or LEFT OUTER JOIN

The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

Yes

Supports ‘Join Duplicates By’

DDS Keyword JDUP

Not supported. Duplicate rows in the ‘secondary’ tables may be returned in random order.

Yes

7. – Calling Programs/Procedures Considerations

Item	DataGate/400	DSS	Acceler8DB Engine
Maximum Number of Parameters	36	2100	N/A
Maximum Length of Stored Procedure Name	N/A	31	N/A
Parameter Direction	Input, Output, *Both	Input, Both	N/A

X. – FAQ

Q. Will DataGate for SQL Server require SQL Server client licenses for end

Users or will a server license be enough?

A. Every user will have to be licensed. There are 2 way of achieving this, one is to buy individual Per-Seat licenses, the other is to buy a per-processor license. The following link will take you to the MS prices as of Aug. 2001:

http://www.microsoft.com/sql/howtobuy/pricing/default.asp. This is another good link with common licensing questions:

http://www.microsoft.com/catalog/display.asp?site=10145&subid=22&pg=4

Here is a breakpoint list of the 2 different license models for Standard Edition:

SQL Server 2000 Standard Edition - purchase processor license if:
1 processor machine: more than 24 users
2 processor machine: more than 53 users
4 processor machine: more than 112 users
8 processor machine: more than 229 users

Q. Will I have to get a DataGate for SQL Server license or is a SQL Server licenses all I need?

A. You will have to obtain DataGate license to access data stored in SQL Server.

Q. Which version(s) of SQL Server will be supported by DataGate for SQL

Server?

A. DataGate supports all editions of SQL Server 2000 (Enterprise, Standard, MSDE…).

Q. Is the Library List supported?

A. Yes.

Q. I have an ADB database with files in the root library, how can I get them into SQL Server?

A. Use the CopyLibrary option of Acceler8DB Database Manager, user ‘/’ for the Source library and a named library for the target.

Q. After copying my libraries to SQL Server, are there new files identical to the originals?

A. Almost but not quite. The main difference you will see is that most fields defined as BINARY in the original files would appear as being ZONED. The impact of this change in your AVR programs is only reflected when the field is part of a Data Structure, in which case, the compiler will protest the discrepancy in the definition of the field, i.e.: the program defines as binary but the file as zoned.

Q. What is MSDE ?

A. Go http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp for a description of MSDE. The Addendum 1 is attached in case the link moves.

Q. Given that DSS imposes the format name (as an ‘R’ followed by the file name), do I have to modify all of my file record formats on the AS/400 to match the file and how will this affect my AVR programs when referring to the old format name?

A. If you compile against ADB or DG400, you will be able to run against DSS even if your format names are different. However if you compile against DSS and your code refers to a format name which doesn't match the one in DSS, then you can either change your references to it (lots of work), or do a rename format in the DclDiskFile (little work).

RNMFMT has been enhanced to take a single parameter to indicate to AVR that this is the new name by which I will be referring to the ONLY format in this DclDiskFile. If you don't rename the format, then AVR will use whatever the database provided it with.

Q. How are the printer files going to work if we cannot have multiple record formats?

A. Print files are supported just like in ADB, that is, they can still be multi-format. It is only data files the ones that have to be single-format.

Addendum 1: MSDE

SQL Server 2000 Desktop Engine (MSDE 2000)

Posted: August 20, 2001

RELATED LINKS

	Integrating MSDE 2000 with Your Applications

	Embedding MSDE 2000 Setup into Custom Applications white paper

Overview

The SQL Server 2000 Desktop Engine (MSDE 2000) is a data engine built and based on core SQL Server technology. With support for single- and dual-processor desktop computers, MSDE 2000 is a reliable storage engine and query processor for desktop extensions of enterprise applications. The common technology base shared between SQL Server and MSDE 2000 enables developers to build applications that can scale seamlessly from portable computers to multiprocessor clusters.

Designed to run in the background, supporting transactional desktop applications, MSDE 2000 does not have its own user interface (UI) or tools. Users interact with MSDE 2000 through the application in which it is embedded. MSDE 2000 is packaged in a self-extracting archive for ease of distribution and embedding.

You can also build MSDE 2000 into an application that was built with Microsoft development tools, such as Microsoft Visual Studio® and Microsoft Office Developer Edition, and distributed royalty-free. This allows developers to build enterprise-class reliability and advanced database features into their desktop applications.

Portable and Compatible

MSDE 2000 is a royalty-free, redistributable database engine that is fully compatible with SQL Server. MSDE 2000 is designed to run on Microsoft Windows® 98, Windows Millennium Edition (Windows Me), Windows NT® Workstation version 4.0 (with Service Pack 5 or later), and Windows 2000 Professional as an embedded database for custom applications that require a local database engine .

MSDE 2000 is an attractive alternative to using the Microsoft Jet database, and is designed primarily to provide a low-cost option for developers who need a database server that can be easily distributed and installed with a value-added business solution. Because it is fully compatible with other editions of SQL Server, developers can easily target both SQL Server and MSDE 2000 with the same core code base. This provides a seamless upgrade path from MSDE 2000 to SQL Server if an application grows beyond the storage and scalability limits of MSDE 2000.

Product Availability

The Microsoft applications listed in the following table all include MSDE 2000 and use its data management and storage services.

Office XP Premium Edition	MSDE 2000 serves as an alternate storage medium for Office XP applications, such as Microsoft Access. MSDE 2000 supports databases up to 2 gigabytes (GB) in size. The graphical user interface (GUI) is limited to what is exposed through the UI in Access. MSDE 2000 includes Osql.exe, a utility you can use to enter Transact-SQL statements, system procedures, and script files. Office XP includes MSDE 2000 on its CD-ROM, in the MSDE 2000 folder. (When you install MSDE 2000, Microsoft Data Access Components [MDAC] version 2.6 is installed automatically. Test your applications for compatibility with MDAC 2.6. For more information, read the article "Microsoft SQL Server 2000 Desktop Engine Is Not Installed by Office XP Setup" in the Microsoft Knowledge Base.)
Visio 2000	MSDE 2000 serves as a data repository for its Visio Network Auto Discovery and Layout function. Information on devices on the network, including Layers 2 and 3, are stored in MSDE 2000.
Microsoft Application Center 2000	MSDE 2000 serves as a distributed data storage engine that stores status, activity, performance, and server health data. Installed as a named instance, MSDE 2000 isolates the monitoring of Application Center from other installations of SQL Server. This eliminates potential performance and security issues while running MSDE 2000 and SQL Server 2000 on the same computer.

You can use the Microsoft applications listed in the following table to build applications using MSDE 2000 as a data store. Redistribution rights and guidelines are defined in the end-user license agreement (EULA) of each product.

Office XP Developer Edition	MSDE 2000 run-time modules are included with Office XP Developer as part of the packaging tools for Access 2000 projects. Office Developer packaging tools intuitively select an instance of MSDE 2000 to install when the Access 2000 project is deployed.
MSDN® Universal Subscription	MSDE 2000 is packaged in MSDN Universal Subscriptions (as part of the SQL Server CD-ROMs) for the purpose of developing desktop applications that are fully compatible with SQL Server 2000. With MSDN Universal Subscription licensing, you can develop, use, and redistribute MSDE 2000 as part of the applications you build, provided that you comply with the distribution terms listed in the master EULA. To view the license agreement, visit MSDN. You will need client access licenses (CALs) or processor licenses to deploy MSDE 2000 applications that access SQL Server back-end data and resources.
SQL Server 2000: Standard Edition Enterprise Edition Developer Edition	MSDE 2000 redistribution rights are included in the EULA of these SQL Server products. MSDE 2000 is a local data engine that can be shared and used as a data storage for desktop applications. MSDE 2000 has no enforced user limit; however, shared use by five or fewer users is recommended. View the Server Processor EULA, Server License EULA, or Developer Edition EULA for more information.

Product Licensing

Licensing for MSDE 2000 is handled differently according to the application it is included in.

Office XP Premium Edition and Visio 2000. A single user license of MSDE 2000 is granted to licensed users of Office XP and Visio 2000.

Office XP Developer and MSDN Universal Subscription. Developers who build applications on MSDE 2000 have the right to freely redistribute MSDE 2000 as stated in the EULA that accompanies Office XP Developer and MSDN Universal Subscription.

SQL Server. For each MSDE 2000 deployment that connects to or accesses data resources of a SQL Server 6.5, SQL Server 7.0, or SQL Server 2000 database, appropriate licensing must be acquired, either by obtaining a SQL Server CAL for each MSDE 2000 device, or by obtaining SQL Server Processor licensing for the back-end SQL Server the MSDE 2000 devices attach to.

Technical Support

MSDE 2000 service packs are delivered simultaneously with and as part of the SQL Server service packs. You can also obtain service packs from a Microsoft Premier support contact. Visit the Microsoft Download Center to download the latest SQL Server service pack.

For more information on Microsoft Product Support Services (PSS), visit the Microsoft PSS Web site.

Supported Languages

MSDE 2000 is localized in the following languages:

§ Simplified Chinese

§ Traditional Chinese

§ French

§ German

§ Italian

§ Japanese

§ Korean

§ Spanish

Features and Capabilities

MSDE 2000 helps you build custom solutions by offering the following features.

Multi-instance support. MSDE 2000 supports up to 16 database server instances on a single computer.

Osql.exe. The osql utility is a Microsoft Win32® command prompt utility for improvised, interactive execution of Transact-SQL statements and scripts. With this utility, you can interactively enter Transact-SQL statements in a manner similar to working at the command prompt. The results are displayed in the command prompt window.

Replication. MSDE 2000 supports merge replication both as a publisher and as a subscriber, allowing you to keep copies of the same data on multiple sites, sometimes covering hundreds of sites. MSDE 2000 also supports other forms of replication, but only in a limited capacity. MSDE 2000 can participate in transactional replication and snapshot replication in a subscriber capacity only.

For more information on SQL Server replication, visit the MSDN Library.

Performance. MSDE 2000 is a local data engine that can be shared. It has a managed concurrency workload governor that limits up to five concurrent batch workloads for optimal performance. Commands and log entries are available to monitor instances where more than five workloads are executed concurrently, a situation that can cause slower performance even on well-tuned systems. As more batch workloads are submitted beyond the five-workload limit, the concurrency governor continues to slow down the system. These workloads are not dropped or lost; they are still processed, but in an increasingly degraded performance mode. If your solution must support more than five concurrent workloads, it is highly recommended that you migrate to SQL Server 2000 or SQL Server 2000 Enterprise Edition for optimal performance at this higher level of scalability.

Maximum database size. MSDE 2000 supports up to 2 GB per database. This limitation is per database, not per server. A single computer can support multiple MSDE 2000 instances, each with databases of up to 2 GB in capacity.

Data Transformation Services. MSDE 2000 is capable of running Data Transformation Services (DTS) packages. However, it cannot design DTS packages, because it is not equipped with a DTS Designer.

Remote administration. It is possible to administer MSDE 2000 both locally and remotely. However, MSDE 2000 cannot be remotely administered in a multi-server environment where transactions occur across servers.

Windows 98 limitations. Windows 98 does not provide support for the following features:

§ Windows NT authentication

§ Fiber mode scheduling

§ Asynchronous I/O

§ Read ahead

§ Performance Monitor counters

§ Scatter/Gather I/O

§ Named pipes server network library

§ Appletalk server or client network library

§ Banyan Vines server network library

System Requirements

To run MSDE 2000, your system must meet the following minimum requirements:

§ Computer with a Pentium 166-MHz or higher processor

§ Windows 98, Windows Me, Windows NT Workstation 4.0 with Service Pack 5 or later, Windows NT Server 4.0 with Service Pack 5 (SP5) or later, Windows NT version 4.0 Server Enterprise Edition with SP5 or later, Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows 2000 Datacenter Server operating system

§ Minimum of 32 megabytes (MB) of RAM. (64 MB or more is recommended. Additional memory may be required, depending on operating system requirements.)

§ 44 MB of available hard disk space

For More Information

Visit the Integrating MSDE with Your Applications page.

Further Reading on SQL:

MSDN SQL Server Limitations
Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days by Richard Waymire, Rick Sawtell; Paperback
Mastering SQLServer 2000
SQL Server 2000 Black Book: A Resource for Real World Database Solutions and Techniques
SQL Server 2000: A Beginner's Guide (With CD-ROM)

Keywords: news, tips, tricks, DSS, SQL, DataGate, differences, planning