ASNA DataGate® 8.2 for Microsoft® SQL Server 2000/2005


Installation Notes

Release Notes

Differences between DataGate, DataGate/400 and DataGate for SQL Server
Porting AVR applications to employ DataGate for SQL Server
AVR Programming Considerations

 

 

Installation Notes

Preparing to Install DataGate for SQL Server

Meet System Requirements

To run DataGate 8.2 for SQL Server and ASNA DataGate 8.2, you must have certain hardware and software installed on your computer.

The system requirements include:

Recommendations:

Review installation options

Before installing ASNA DataGate 8.2 for SQL Server, consider the following:

Installing DataGate 8.2 for SQL Server

DataGate for SQL Server is also installed with the AVR 8.2 and Windows Deployment installations.  However, to install DSS on a system that does not have AVR, you can install from the CD or from the Developer Network Downloads page. 

When installing DataGate for SQL Server, you should be signed on with Administrative Privileges to start the DataGate Service. If you are not signed on with Administrative Privileges, a Warning will display – stating that you need to have Administrative privileges to start the DataGate Service. Therefore, you may want to check with your Network Administrator prior to installing to ensure you have Administrative privileges.

To Install from ASNA Developer Network (DevNet)

  1. Go to http://devnet.asna.com.
  2. Click on Sign In link on the top right corner and sign in to your DevNet account, or select Create an Account on the left to create a new account.
  3. Once you are signed in, click on the Downloads link at the top.
  4. Scroll down and select the 8.2 product and version you wish to download.
  5. On the next screen, the list of products in the product suite selected will display for you to select from.
  6. Select the product to install. The Release Notes for that product will display.  In the right hand corner "Downloads" window, you will see the list of products available based on your sign in.  Select the icon of the product to install.  The installation exe will display for you to Run now, or Save to your system and install later.

To Install DataGate for SQL Server from CD

  1. Insert the ASNA .NET Product Suite CD into your CD drive.
  2. Click on ASNA DataGate 8.2 for Microsoft SQL Server 2000/2005.
  3. Click on the Install… button to begin the installation.
  4. After the installation has installed for a few minutes, the stored procedures will install.  A dialog will then display for you to respond to if you would like to select a SQL database name to be used as the special QTEMP library.  The default is set as disabled.  See Enabling SQL Database(s) to be used a QTEMP Library for further information. 
  5. Selecting Done or Install to the dialog below will complete the installation.  A dialog will display indicating that the installation or update is complete.

Enabling SQL Database(s) to be used as QTEMP Library

The DataGate for SQL Server dialog (Stored Procedures) displays during the installation of DataGate for SQL Server and when selecting the Stored Procedures option.  It allows you to select a SQL Server instance and connect to SQL Server.  You can set security information, as well as specify a SQL database to be used as a QTEMP library.

Note that after DSS is installed, you can access this dialog at any time from Start - Programs - ASNA .NET Product Suite - DataGate for SQL Server 8.2 - ASNA DataGate for SQL Server 8.2 Stored Procedures.

To Enable SQL Server Instance(s)

  1. Enter or select the following to the SQL Server instance dialog.

Licensing DataGate 8.2 for SQL Server

ASNA DataGate 8.2 for SQL Server must be properly licensed prior to using it. The steps to license DataGate for SQL Server is exactly the same as licensing AVR or DataGate Client.

Refer to the Registration Assistant help file (Regasist.chm) in C:\Program Files\Common Files\ASNA Shared for more information!

Starting DataGate Database Manager

After you have completed the installation procedure, you can start any of the products included with DataGate at any time.

  1. Start DataGate Database Manager by selecting Start – Programs - ASNA .NET Product Suite – DataGateDataGate Database Manager.
  2. For help on using any of the products, select Contents from the Help menu to display the On-line help file.

Uninstalling DataGate 8.2 for SQL Server

To Remove ASNA DataGate for SQL Server

  1. Select Start – Settings - Control Panel.
  2. Select Add/Remove Programs.
  3. Select ASNA DataGate 8.2 for SQL Server.
  4. Click the Add/Remove… icon.
  5. Click Yes to begin removing DataGate. Click No to return to the previous screen.

Troubleshooting - Error connecting to Microsoft SQL Server

SQL Server Service implements the SQL Server database engine. There is one SQL Server service for each instance of SQL Server running on the computer. ASNA DataGate for SQL Server uses standard Microsoft SQL Server client access facilities to access SQL Server instances. Verify that Microsoft client utilities can connect and access the desired SQL Server instance. When Microsoft client utilities are able to successfully connect with the SQL Server instance, so will ASNA DataGate for SQL Server.

Release Notes

SQL Server 2000/2005

ASNA DataGate 8.2 for SQL Server also supports SQL Server 2005 and SQL Server 2005 Express.

Licensing Changes

DataGate 8.2 for SQL Server employs a new License Manager that eliminates the duplicate user counts that were encountered in previous versions.

Creating ‘Files’ in SQL Server®

As part of the basic strategy to give SQL Server the semantics of DB2/400, DataGate for SQL Server (DSS) was built to permit as much manipulation as possible of the underlying objects directly, via native SQL commands. As a consequence of such strategy, it was decided that there would be no artificial structure imposed on the tables and views used to implement the concept of physical and logical files. Files were then implemented as follows:

When a simple logical file is created, it is possible to establish the underlying ‘Select’ in two ways:

In either case, the SCHEMABINDING option is not used; this permits the table to be altered, thus new columns can be added to an existing table without having to drop the views first. However, SQL Server does not reflect the new columns in the corresponding views (those with the ‘Select * ’ command) until such views are recreated.

This is a natural behavior of SQL Server views and DSS does not alter it. Notice also that DSS does not provide a way to add new fields to a file, so the procedure aforementioned has to be done using the Microsoft SQL Server Management Studio.

Back to Top

Differences between DataGate®, DataGate/400®.NET (DG/400) and DataGate for SQL Server® for .NET

Contents

Object Considerations

Item

DG/400

DSS .NET

DataGate

Library & file name length

10 characters

31 characters

31 characters

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

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

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

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 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 unlock by the application or when another record is read or positioned to.

DSS .NET

DSS for .NET (using server cursors) determines the locking characteristics based 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 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. 

Object Locking

Not implemented on DSS .NET.

Field Considerations

ITEM

DG/400

DSS.NET

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.  Fields of these types are hidden from the file definition.  You will be able to display the file definition in Database Manager 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 .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

Maximum Length of Stored Procedure Name

N/A

31

N/A

Parameter Direction

*Input, *Output, *Both

*Input, *Both

N/A

Porting AVR .NET Applications to Employ ASNA DataGate ® for SQL Server®for .NET

Introduction

ASNA's DataGate for SQL Server Classic (DSS for .NET) provides AVR programmers’ access to Microsoft SQL Server 2000 through native RPG file IO using familiar RPG operation codes such as CHAIN and SETLL. DSS for .NET allows you write one set of applications that works concurrently with both the iSeries and SQL Server databases.

You will not need to learn SQL to get effective access to SQL Server. The skills you use today for iSeries file IO will work exactly the same way for SQL Server via DSS for .NET. The same AVR programs that connect to SQL Server will seamlessly connect to the iSeries and ASNA’s DataGate engine.

DSS for .NET makes a SQL Server instance appear as a database similar to DataGate or iSeries. The characteristic features of DB2/400 materialize on SQL Server when seen through the eyes of DSS for .NET. With DSS for .NET, you can use the Library list, Physical and Logical files, even DB2 field types, like packed and zoned.

Back to Porting Applications Top

Some Restrictions Apply

Even though DSS for .NET tries to make SQL Server look like DB2/400, there are several features that cannot be implemented in a totally transparent fashion. The SQL Server 2000 database engine is of a different design and implementation than the iSeries and DataGate engines.

The intent of this document is to help you become aware of the items that will most likely affect your application and the process of using SQL Server as your database. Through it all, remember the goal you are seeking is to create applications that take advantage of the many features of SQL Server and to create applications that can run with either SQL Server or DB2/400 as the underlying database engine.

In the next sections we’ll deal with all of the issues that will need your attention, but the following items are probably the ones with the most impact for many of you.

No Multi-Format Logical Files:

DSS for .NET implements a physical file through the use of a native SQL Server table. A logical file is implemented through a native view.SQL Server Views are single formatted in nature, so there is no support for multi-format logical data files. You will have to eliminate any reference to multi-format logical files in your application.

Print files, although they are typically multi-formatted, are fully supported in DSS for .NET, however.

Single Member Files:

SQL Server doesn’t have the concept of members of a table/view. DSS for .NET makes it appear as though each file had one (and only one) member; the member name is exactly the same as the file name. For the member name, you can use the exact name or the special values *FIRST and *FILE. You should be careful when using the Copy Data and Copy Library tools of the DataGate Database Manager to copy a file from DataGate or iSeries, because they default to *SAME for the target member name; if the source member name was differently than the file name the copy will fail.

If your application depends on the existence of multiple members per file, you will want to re-architect it to provide an alternative method for handling the logic involving these files.

Logical Field Restrictions:

There are two restrictions on the usage of logical fields when they change the name or the type of their corresponding base physical field. When the field is retyped, most typically because the field is a concatenation or substring of the physical, then the field becomes read only. A logical field, whose name has changed from its physical base field, can’t be used as a key field in the logical file.

Unlocking Records:

This is probably the most demanding area of application adaptation between the differences of implementation between iSeries method of record locking and that of the SQL Server database engine. The problem arises in two areas: Using the *nolock keyword on the read operations and on the implementation of the UNLOCK command.

DSS for .NET uses SQL Server ‘Server Cursors’ to implement file access. When a file is opened for update, it is not possible to tell SQL Server to not lock the record on a read, so a read with *nolock has no effect for files opened for Update. There are two methods to resolve this problem:

  1. Declare a second instance of the file marked as input only and use it wherever the NoLock option would have been given on a read/chain.
  2. Retain the *nolock and follow the read/chain with an UNLOCK command. Be aware this method imposes some restrictions as stated in the next paragraph.

The UNLOCK command leaves the cursor in a no-position state, meaning you can’t perform a subsequent read (next/previous) without repositioning the file with a SETLL, SETGT or CHAIN.

Back to Porting Applications Top

The Process

In a nutshell, the process of upgrading your application is composed of the following steps:

  1. Get a decent size computer
  2. Move your data from the iSeries or DataGate Engine to SQL Server
  3. If necessary, modify your application to adapt to any new restrictions
  4. Optimize your application to take advantage of several AVR op-codes

1. Computer Size

SQL Server is very demanding on computer resources. We recommend you get a computer with at least the capacity of the cheapest machine you can get from Dell, but with twice the amount of memory. The machine doesn’t have to be a Dell computer, but use the Dell machine as a reference to configure your box.

It is highly recommend that you use Windows XP Professional and above or Windows 2000 workstation for your development machine.

Back to Porting Applications Top

2. Move Your Data

Create a Database Name:

The first step after installing DataGate for SQL Server on your machine is to create a Database Name that points to your SQL Server. You can use the Database Wizard or the Work with Database Names option of the Database menu of Database Manager.

Under typical SQL Server installations, there is only one instance of the server installed on the machine and that instance typically contains the following databases:

Each one of these databases will be presented through DSS for .NET as individual libraries. Since all databases are shown under a common database name, the "Label' parameter of a database name pointing to DataGate 8.2 for SQL Server must be "SQL".

The following figure shows a Database Name called Ontario pointing to the SQL Server engine in My Computer

You can use *DOMAIN for your user name or you can supply one of the login names recognized by SQL Server with its corresponding password.

Open the Database:

Test your Database Name by opening the database with Database Manager. You should see something like the following:

When you open a database for the first time, Database Manager prompts you for your viewing preferences for the database with a dialog like the following.

Notice in particular the checkbox Show system objects. It controls whether to or not to display a set of SQL Server objects called sysxxxx. For iSeries databases, this option shows or hides objects like QSYS.

Prepare Files and Copy Libraries:

The next step in moving your data is to prepare a library (or libraries) on the source machine with all the files you wish to copy to the target SQL Server.

After you have prepared the library, use the Copy Library option from the Tool menu to move the data.

You can use one of your existing libraries, but it may require some clean up before it is ready. To check your data, use one of the options below:

Visual Inspection Using a Checklist:

Use the following checklist to ensure a DataGate or iSeries data file will be successfully copied to SQL Server:

The file must have:

Logical files have extra restrictions. Make sure that the key fields are not substrings or concatenations of physical fields and that the name of the logical field is the same as that of its base field.

Issue the Copy Command and Note if there are any Problems

If you choose the second option, uncheck the Copy Data Records in the Copy Library dialog to speed up the process. Take note of any problem and after fixing it, delete the target library and issue the Copy Library command again, this time copying the data records. You can view the result of the copy in the Message Log window.

Copy Library:

Once you have modified your files to comply with the limitations stated above, you can use the Copy Library menu option.

All files stored under SQL Server must reside inside a ‘Library’ just like files on an iSeries. DataGate Engine has allowed files to be placed at the ‘root’ level of the database and under subfolders inside a library. If you have files in places other than first level libraries, you’ll have to adapt your application (or the library list) to point to the new locations.

When a physical file is copied to SQL Server, a table is created with the name of the physical file. For logical files, a view is created. In either case, if the file is keyed, an index is created on the table; for logical files the index is created on the primary base table.

When a view is encountered through DSS for .NET, it is reported as being a new kind of logical file, called SQL Logical. That is why when a logical file is copied, regardless of whether a logical file is a Simple Logical or a Join Logical, it will appear on the SQL Server database as a SQL Logical file.

Field Reference Files

Field Reference Files are used on the iSeries to collect definitions of named field types that are used in the creation of data files. SQL Server has a built-in data dictionary where user types can be defined. DSS for .NET surfaces this data dictionary through the special file *FieldRef. There is one *FieldRef file per library.

If you use a field reference file as part of your data file definitions, you should first import those fields into the data dictionary of the DSS for .NET library.

Follow these steps to populate the dictionary:

  1. Open the source database.
  2. Drag the field reference file to the Work Definition Area
  3. Open the target database.
  4. Create the target library.
  5. Drag the field reference file to the target database taking care of specifying *FieldRef as the file name to be created. DSS for .NET will notice the special name and add the fields to the data dictionary.

After you have populated the data dictionary, you can view the type definitions by clicking on the *FieldRef file in the target library.
If you don’t see the *FieldRef file in the library, ensure that the option to Show System Objects is checked is checked ON in the options dialog.

Back to Porting Applications Top

  3. Adapt the Application

Enable DataGate for SQL Server Warnings:

To assist in finding areas in code that will potentially cause an error, a new IDE option has been added to ASNA Visual RPG to give warnings for invalid operations under DSS for .NET. This option is located within the “Compiler” Tab of the Project >Settings menu option.

Select the Enable DataGate for SQL Server Warnings option so that a check mark appears in the check box and select OK.

Change your DCLDISKFILE Statements:

Depending upon your application, the following are some changes you will/may need to make to your DCLDISKFILE statements.

*Arrival processing:

Arrival sequence processing must be changed to indexed processing. SQL Server doesn’t have the concept of a relative record number (RRN).

The simplest method for handling this would be to employ the use of a key field in the file. If the file is being processed only in consecutive fashion (i.e. you don’t use random op-codes like SETLL or CHAIN on the file) then you can choose any field to be the key and specify RANDOM(*NO) in the DCLDISKFILE. If you do process the file randomly, then you will need to add a field to the file to simulate the RRN sequence. The easiest way to achieve this is by adding an identity field called RecNum of type integer.

Format names:

In DSS for .NET, the record format name is always “R” + Filename. If the record format name is anything else, and you’ve specified this record format name on I/O operations, a rename format will need to be specified on the DCLDISKFILE statement as follows. RNMFMT(OldFormatName) where OldFormatName is the name of the existing format.  No changes will need to be made to the actual I/O statements.

Query Files:

If your application makes use of the Open Query File capability of DataGate or iSeries, you will have to take care of a couple of things.

Check Format ID:

If an application is going to run against both DSS for .NET and DataGate 400, and your file currently contains binary fields, you will need to consider changing the field type.

Unlocking Records:

The behavior of DSS for .NET when the file is opened for update is similar to DG/400, but with two significant differences:

  1. Updating a record does not release the lock on the record.
  2. Explicitly unlocking a record causes the ‘current record position’ to be lost.

These differences bear the following considerations:

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 to be studied and possibly modified.

Back to Porting Applications Top

4. Optimize Your Applications with AVR Op-Codes

To optimize SQL Server processing and to enhance client/server performance with all supported database engines (iSeries/400, DataGate and SQL Server) with dynamic Network Blocking, AVR has 3 op-codes; SetRange, ReadRange and DeleteRange, as described below:

I/O op-codes provide better performance in two ways:

  1. When working with large SQL Server files, DSS for .NET is able to optimize the record set selection operation.
  2. Network Blocking becomes dynamic, in that records outside of the range are not transmitted to the client.

SetRange

Use the SETRANGE op-code in place of SETLL and SETGT when you’re doing SETLL/READE and SETGT/READPE loops.

ReadRange

Use the READRANGE op-code in place of the CHAIN op-code when you’re doing CHAIN/READE loops.

DeleteRange

Use the DELETERANGE in place of DELETE loops.

Back to Porting Applications

Error Handling

Strong consideration should be given to Visual RPG Error Indicators when performing I/O operations as they could be turned on now for reasons not present before.  For instance, when the Chain opcode returned an error, the cause was commonly assumed to be that the record was busy.  With DSS for .NET, error indicators can be turned on for various reasons such as the use of the *NOLOCK option on I/O operations. (See Locking Considerations in the DG400 vs. DSS for .NET document).

New Error Conditions under DSS for .NET:

The following situations will cause the error indicator to be set on.

  1. Arrival Random Access.

    When a file is opened for *Arrival access, commands such as Chain, Setll, etc, will cause an error condition.

  2. Use of the *NOLOCK option is not supported under DSS for .NET.

    Any I/O operations that make use of the *NOLOCK option when working with files opened for update will cause an error condition.

ASNA Visual RPG® for .NET Programming Considerations 

Contents

Error Handling

New Error Conditions

Range Opcodes

Considerations for Adapting Existing Applications to DSS for .NET

Not Implemented Yet

Other Considerations

Error Handling

Strong consideration should be given to Visual RPG Error Indicators when performing I/O operations as they could be turned on now for reasons not present before.  For instance, when the Chain opcode returned an error, the cause was commonly assumed to be that the record was busy.  With DSS for .NET, error indicators can be turned on for various reasons such as the use of the *NOLOCK option on I/O operations. (See Locking considerations in the DG400 vs. DSS for .NET document).

Back to Programming Considerations

New Error Conditions under DSS for .NET

The following situations will cause the error indicator to be set on.

  1. Arrival Random Access.

    When a file is opened for *Arrival access, commands such as Chain, Setll, etc, will cause an error condition.

  2. Use of the *NOLOCK option is not supported under DSS for .NET.

    Any I/O operations that make use of the *NOLOCK option when working with files opened for update will cause an error condition.

To assist in finding areas in code that will potentially cause an error, a new IDE option is added to give warnings for invalid operations for DSS for .NET. This option is located in the “Compiler” Tab of the Project >Settings menu option.

Range Opcodes

I/O op codes provide better performance when working with large files.

Considerations for Adapting Existing Applications to DSS for .NET

In DataGate for SQL Server, the record format name is always “R” + Filename. If the record format name is anything else, and you’ve specified this record format name on I/O operations, a rename format will need to be specified on the DCLDISKFILE statement as follows. RNMFMT(OldFormatName) where OldFormatName is the name of the existing format.  No changes will need to be made to the actual I/O statements.

The old syntax of RNMFMT(OldFormatName,NewFormatName) is not needed.

Other Considerations

Back to Top

   March 29, 2012       © 2007-2012. ASNA . All rights reserved.