ASNA DataGate® for SQL Server Reference Manual

Differences Between DataGate, DataGate® for IBM i, and DataGate® for SQL Server

Object Considerations
Index (Keys) Considerations
Data Access Considerations
Locking Considerations
Field Considerations
Native SQL Server Field Interpretation
Join Considerations
Calling Programs/Procedures Considerations

Object Considerations

Item	DG/400	DSS	DataGate
Library & file name length	10 characters	31 characters	31 characters
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 that are not accessible yet by DSS).	32,000 bytes
Max number of records per member		Limit defined by SQL Server instance	`2,147,483,646*`
Library implemented as:	Library	Database	Internal construct
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 FRF, which can be any physical file in any library.

Note: For Max number of records per member, DataGate® for Windows and Desktop Servers support member/file sizes limit of 16 exabytes ( 2^4 * 2^60).

Index (Keys) Considerations

Item	DG/400	DSS	DataGate
Indexed logical files per physical file		249	`*NoMax`
Imposing 'uniqueness' via select/omit rules in logical files	Supported	Not directly supported. See work-around note below.	Supported
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 Sub-stringed fields as keys.	No
Maximum number of key fields per key		16	250
Maximum length of key in bytes	2,000	900	250

Work-around:

As a work-around to support "Imposing 'uniqueness' via select/omit rules in logical files":

Change the logical file definition to allow duplicate keys.
Create the logical file.
Alter the SQL view with the "SCHEMABINDING" attribute.
Add a Unique, Clustered Index to the SQL view (logical file).
Make sure the "ARITHABORT" SET option is on/true for the database.

Data Access Considerations

Item	DG/400	DSS	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 (see Note below)	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, it 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.	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.

Note - Multi-format logical files are not supported on SQL Server. The migrated code is normalized for SQL Server especially when I/O commands target single-format record format names instead of the file name but this does not change the application's behavior when accessing files on the IBM i.

If the Rename keyword is present in the legacy file description, the migrated RNMFMT keyword will contain the legacy New Format Name. If the Rename keyword is not present in the legacy file description, the migrated RNMFMT keyword will contain the files Externally Described Record Format name.

Locking Considerations

Record Locking

DG/400

DG/400 determines the type and duration of record 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 is 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 cannot read it. The write lock is held until the record is updated or explicitly unlocked by the application or when another record is read or positioned to.

DSS

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

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

The behavior of DSS 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
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 re-coded 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. Note 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

For DSS, object locking is implemented only for Data Area objects.

Field Considerations

ITEM	DG/400	DSS	DataGate
Field Name Length	10 Characters	31 Characters	31 Characters
Supported Types
- Char	`*CHAR`	char	`*CHAR`
- Packed	`*PACKED`	decimal	`*PACKED`
- Zoned	`*ZONED`	numeric	`*ZONED`
- Binary	`*BINARY`	numeric	`*BINARY`
- Float	`*FLOAT`	Float(4): float Float(8): real	`*FLOAT`
- Integer	`*INTEGER`	Integer(2): smallint Integer(4): int	`*INTEGER`
- Date	`*DATE`	`ASNA_DSS.DATE` datetime: 00:00:00	`*DATE`
- Time	`*TIME`	`ASNA_DSS.TIME` `datetime: 1899/12/30`	`*TIME`
- Timestamp	`*TIMESTAMP`	datetime	`*TIMESTAMP`
- Hex	`*HEX`	binary	`*HEX`
- DBCS	`*DBCS`	nchar	`*DBCS`
- Unicode	`*DBCS`	nchar	`*DBCS`
- Boolean	`*CHAR(1)`	Bit	`*CHAR(1)`
Allows Nulls	Yes	Yes	No
Variable length Field	Char Hex Dbcs	varchar varbinary varnchar	No
Date value range	0001-01-01 to 9999-12-31	Datetime: 1753-01-01 to 9999-12-31 (0001-01-01 maps to 1753-01-01) Smalldatetime: 1900-01-01 to 2079-06-06	00001-01-01 to 9999-12-31
Decimal number storage	Packed: 1 nibble per digit Zoned: 1 byte per digit Binary 1 - 4 digits: 2 bytes Binary 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 byte per digit Binary 1 - 4 digits: 2 bytes Binary 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 whose 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)	DateTime	*Timestamp	Bit	*Boolean
Real	*Float (8)	SmallDateTime	*Timestamp	Char	*Char
Int	*Integer (4)			VarChar	*Char (VarLen)
SmallInt	*Integer (2)			NChar	*Unicode
TinyInt	*Integer (2)			NVarChar	*Unicode (VarLen)
Decimal	*Packed			Binary	*Hex
BigInt	*Zoned (19,0)			VarBinary	*Hex (VarLen)
Money	*Zoned (19,4)			UniqueIdentifier	*Hex (16)
Numeric	*Zoned
SmallMoney	*Zoned (9,4)

The types Image, Text, and NText are not supported; nor are their replacements VARBINARY(MAX), VARCHAR(MAX), and NVARCHAR(MAX). Fields of these types are hidden from the file definition. You will be able to display the file definition in DataGate® Studio but will not be able to open the file. To ensure future application compatibility, you should not use files containing these field types. 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.

Join Considerations

Item

DG/400

DSS

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	DataGate
Maximum Number of Parameters	36	1024	N/A
Maximum Length of Stored Procedure Name	N/A	31	N/A
Parameter Direction	Input, Output, *Both	Input, Both	N/A

ASNA DataGate® for SQL Server 16.0 Reference Guide