ASNA DataGate® for SQL Server Reference Manual

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


Contents

  • 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

DataGate7

Library & file name length

10 characters

31 characters

31 characters

Members per file

0 ®*NoMax

Exactly 1

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

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 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 14.0 and higher 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":

  1. Change the logical file definition to allow duplicate keys.
  2. Create the logical file.
  3. Alter the SQL view with the "SCHEMABINDING" attribute.
  4. Add a Unique, Clustered Index to the SQL view (logical file).
  5. 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

   

 

- 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

Data 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