DataGate for IBM i and DataGate for SQL Server differences

Published: October 1, 2019

1. Object Considerations

Item	DataGate/400	DSS	Acceler8DB Engine
Library & file name length	10 characters	31 characters	31
Members per file	0 *NoMax	0 *NoMax	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.

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 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 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. See the release notes for your specific version of DataGate for SQL Server.

Q. Is the Library List supported?

A. Yes.

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. 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.

Category: Document

Tags: DataGate