 |
 |
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: |
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 |
Calling Programs/Procedures Considerations
|
Item |
DG/400 |
DSS .NET |
DataGate |
|
Maximum Number of Parameters |
36 |
1024 |
N/A |
|
Parameter Direction |
*Input, *Output, *Both |
*Input, *Both |
N/A |
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 a 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 (
Q. Is the Library
List supported?
A. Yes.
Q. I have an
DataGate
database with files in the root library, how can I get them into SQL Server?
A. Use the CopyLibrary option of DataGate 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 fields defined as
BINARY in the original files will 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 defines as zoned.
Q. What is MSDE ?
A. Go to 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
for .NET 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 DataGate or DG400, you
will be able to run against DSS for .NET even if your format names are different. However if you compile against DSS for .NET and your
code refers to a format name which doesn't match the one in DSS .NET, 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 you 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
DataGate,
that is, they can still be multi-format.
It is only the data files that have
to be single-format.
|
04/29/2007
© 2007.
Amalgamated Software of
|