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 |
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) |
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 unless 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 against 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 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. |
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 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
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 that 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.