 |
 |
Differences between DataGate®, DataGate/400® .NET (DG/400) and DataGate for SQL Server® for .NET
Contents
Calling
Programs/Procedures Considerations
|
Item |
DG/400 |
DSS for |
DataGate |
|
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 |
Physical SqlLogical Simple logical Join logical |
Physical Simple logical Join logical Multiformat logical |
|
Max record length |
32,000 bytes |
8,060 bytes (Not counting Text and Image fields which are not accessible yet by DSS .NET). |
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, DG/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).
Index (Keys) Considerations
|
Item |
DG/400 |
DSS .NET |
DataGate |
|
Indexed logical files per physical file |
|
249 |
*NoMax |
|
Logical field used as a key field must be based on a physical field 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 |
|
Item |
DG/400 |
DSS .NET |
DataGate |
|
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 unless the file is indexed. The only random operation allowed is SetLL and this is 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 against DG/400 and DSS .NET. |
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 analyzer 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 the key field list to define the new key. |
Record Locking
DG/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 .NET
DSS for .NET (using server cursors) also determines the locking characteristics bases on how the file is opened.
For read-only files DSS for .NET behaves like DG/400, that is, there are no locks neither placed nor considered on records being read.
The behavior of DSS for .NET when the file is opened for update is similar to DG/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 |
DG/400 |
DSS .NET |
|
|
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 demanding change is the one requiring segments of code involving CHAIN-UPDATE. Combinations have to be studied and possibly 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 .NET.
|
Item |
DG/400 |
DSS .NET |
DataGate |
|
Field name length |
10 characters |
31 characters |
31 characters |
|
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 .NET_DATE datetime= Time ® *ASNA_DSS .NET_TIME datetime=1899/12/30 Timestamp ® datetime Hex ® binary DBCS ® nchar Unicode ® nchar Boolean ® Bit |
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 |
|
Datetime (ASNA_DSS .NET_DATE): 1753-01-01 ® 9999-12-31 Smalldatetime: 1900-01-01 ® 2079-06-06 |
|
|
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 .NET_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 cannot 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) |
|
Item |
DG/400 |
DSS .NET |
DataGate |
|
Supports Use Default for Joins by: |