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

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 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 unles 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 agaist 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 analizer 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 they 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 possible 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 which 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.




Por favor, inicie sesión o cree una cuenta para enviar comentarios.