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

·          Read the following topic on Enabling SQL Server Instance(s) to ensure that the target instance(s) for Microsoft SQL Server 2005 will be properly detected and the installation process will complete successfully.

·          Ensure that Microsoft for SQL Server has been installed, or install Microsoft for SQL Server.  Refer to Microsoft SQL Server documentation for further information.

·          Be sure the computer meets the system requirements for DataGate 8.0 for SQL Server.  For more information, see System Requirements.

·          Verify that the Microsoft SQL Server Service is started, and that the Auto-start service when OS starts is selected.   See Starting, Pausing, and Stopping SQL Server below for more information.

·          Review the Installation Instructions for installing DataGate 8.0 for SQL Server and be prepared to make the appropriate selections when running Setup.

·          Read the Release Notes section in this document, or on the ASNA Downloads site or DataGate for SQL Server for the latest on product issues, as well as known fixes, known bugs, etc.

Install Microsoft SQL Server or verify installation

The following includes the basic steps to install Microsoft SQL Server.  Please refer to the Microsoft SQL Server documentation for further information.

To install Microsoft SQL Server 2000

1.       Insert the Microsoft® SQL Server™ 2000 compact disc in your CD-ROM drive. If the compact disc does not automatically run, double-click Autorun.exe in the root directory of the compact disc.

2.       Select SQL Server 2000 Components.

3.       Select Install Database Server and setup prepares the SQL Server Installation Wizard.  At the Welcome screen, click Next.

4.       In the Computer Name dialog box, Local Computer is the default option and the local computer name appears in the edit box. Click Next.

For a remote installation, click Remote Computer. You can then type a computer name or click Browse to locate a remote computer.

If a cluster is detected, Virtual server is the default option.

5.       In the Installation Selection dialog box, click Create a new instance of SQL Server, or install Client Tools, and then click Next.

6.       Follow directions on the User Information, Software License Agreement and related screens.

7.       In the Installation Definition dialog box, click Server and Client Tools, and then click Next.

8.       In the Instance Name dialog box, if the Default check box is available, you can install either the default or a named instance. If the Default check box is not available, a default instance has already been installed, and you can install only a named instance.

·          To install the default instance, select the Default check box, and click Next.

·          To install a named instance, clear the Default check box, and type a new named instance in the Instance Name edit box. Click Next.

9.       In the Setup Type dialog box, click Typical or Minimum, and then click Next.

If you want to select components and subcomponents, change character set, network libraries or other settings, click Custom, and then click Next.

10.    In the Service Accounts dialog box, accept the default settings, enter your domain password, and then click Next.

11.    In the Authentication Mode dialog box, accept the default setting, and click Next.

12.    When you are finished specifying options, click Next in the Start Copying Files dialog box.

13.    In the Choose Licensing Mode dialog box, make selections according to your license agreement, and click Continue to begin the installation.

Click Help for information about licensing or see your system administrator.

14.    In the Setup Complete dialog box, click Yes, I want to restart my computer now, and then click Finish.
 

Starting, Pausing, and Stopping SQL Server

SQL Server Service Manager is used to start, stop, and pause the Microsoft® SQL Server™ 2000 components on the server.  These components run as services on Microsoft Windows NT® or Microsoft Windows® 2000 and above.

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.

SQL Server Service Manager is a taskbar application and follows the standard behavior of taskbar applications.  The task bar icon will display whether the Server is running , paused, or stopped . 

Important!!!!  There is a field within the SQL Server Service Manager that you need to select, or ensure that it is enabled.  It is called “Auto-start service when OS starts”.  Please ensure this option is selected, especially when installing AVR as well, as the system will reboot after installing AVR.  If this option is not selected, the SQL Server Service will be stopped, so when the installation process continues and tries to install DataGate for SQL Server, you will receive an error, and DataGate for SQL Server will not be installed. 

Note that this option is not selected by default.  You must select this option prior to installing DataGate for SQL Server (if installing AVR at the same time).

To Open, Start, Pause or Stop the SQL Server Manager

1.       After Microsoft SQL Server is installed, the SQL Service Manager icon for the local machine will display in the right hand corner of your taskbar.  To open SQL Server Service Manager, either double-click on the , or right-click on the icon to display a list of menu options.  The following dialog will display.

Enter or select the following to SQL Server Service Manager: 

Server:

The name of the local computer will display.  Clicking on the arrow to the right will display all of the SQL Servers that you have access to.

Services:

The SQL Server service will display.  Clicking on the arrow to the right will display the other SQL services you have access to, such as SQL Server Agent, and Distributed Transaction Coordinator.

Refresh services…

Select this button to refresh the services. 

Start/Continue

Selecting this button starts the SQL Server Service.  After it is started, the option will be dimmed, and the icon will display with a green start icon next to it, indicating the service is started.  Select this option when the service is either currently paused, or stopped. 

Pause

Select this button to temporarily pause the SQL Server Service.   To continue service, select the Start/Continue button.

Stop

Select this button to stop the SQL Server Service.   To start the service again, select the Start/Continue button.

Auto-start service when OS starts:

Select this option to ensure that the SQL Server Service automatically restarts after your system has been restarted. 

Note that this option is not selected by default.  You must select this option prior to installing DataGate for SQL Server (if installing AVR at the same time).

2.       To close SQL Server Service Manager, right-click the SQL Server Service Manager icon on the taskbar, and then select the Exit menu option.
 

Installing DataGate 8.0 for SQL Server

DataGate for SQL Server is easily installed by selecting the appropriate options from the CD Browser that will automatically display when the ASNA Product Suite CD is inserted in the CD-Rom drive.  An installation, a Setup program will decompress and install the files in the appropriate directories.

DataGate for SQL Server can be installed from either AVR 8.0 for .NET or Deployment options from the ASNA Product Suite. 

Installing AVR 8.0 will also automatically install Release 8.0 of DataGate.  However, to install DataGate for SQL Server, there are several options you must select to install (as shown in step 4 below). 

When installing DataGate for SQL Server, you should be signed on with Administrative Privileges to start the DataGate NT 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 NT Service.  Therefore, you may want to check with your Network Administrator prior to installing to ensure you have Administrative privileges. 

To Install DataGate for SQL Server from CD

1.       Insert the ASNA Product Suite CD into your CD drive.

·          If your CD drive has AutoPlay, a screen will automatically appear.  Select the options below to install DataGate for SQL Server. 

·          If you do not have AutoPlay installed on your CD drive, start Microsoft Windows and from the Start Menu, select Run, and enter the path:  E:\INSTALL.EXE, where ‘E’ is the CD-Rom drive.  Then select the options below to install DataGate for SQL Server.

2.       Select ASNA DataGate® 8.0 for Microsoft® SQL Server™ 2000/2005.

3.       Select the INSTALL button to install the product.

Note that you can also view the Readme file by selecting the READ ME button.

4. After the setup file is located, the Welcome screen will display, along with the License, Customer Information and Destination Folder screens.  Simply follow the instructions on the screen.  When you get to the Select Features screen, the following components will be selected/unselected by default.  See below for more information on each option.

By default, the DataGate For SQL Server and Client option will always be installed, and cannot be unselected.  This option will also automatically install the DataGate Engine and DataGate for SQL Server Help. 

The DataGate Client Utilities will be checked by default.  However, it can be unselected so it will not be installed.  The rest of the components are optional.

If you select Acceler8DB Sources, then notice that the DataGate Client Utilities will be selected (if not already selected), and the background of the checkbox will turn gray (indicating it is a mandatory installation).  You are unable to unselect DataGate Client Utilities unless you remove the selection for Acceler8DB Sources.

Note that a description for each component will display in the Description section in the middle of the dialog box. 

5.       After selecting Next > to the Select Features dialog, you are now ready to install DataGate for SQL Server.

6.       Select the Install button to continue with the installation.

7.       When the installation is complete, you will be prompted to reboot the system.  If DataGate Client Utilities is selected to be installed, then the installation process for DataGate Client Utilities will automatically display.  Follow the instructions on the screen.  The main screens that will occur are:

·          License Agreement

·          Registration

·          Directory locations and selecting which files to install
 

Licensing DataGate 8.0 for SQL Server

ASNA DataGate 8.0 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 for .NET 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 – DataGate – DataGate 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.0 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.0 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 Manager is used to start, stop, and pause the Microsoft® SQL Server™ 2000 components on the server.  These components run as services on Microsoft Windows NT® or Microsoft Windows® 2000 and above.

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.

SQL Server Service Manager is a taskbar application and follows the standard behavior of taskbar applications.  The task bar icon will display whether the Server is running , paused, or stopped . 

Important!!!!  There is a field within the SQL Server Service Manager that you need to select, or ensure that it is enabled.  It is called “Auto-start service when OS starts”.  Please ensure this option is selected, especially when installing AVR as well, as the system will reboot after installing AVR.  If this option is not selected, the SQL Server Service will be stopped, so when the installation process continues and tries to install DataGate for SQL Server, you will receive an error, and DataGate for SQL Server will not be installed. 

Note that this option is not selected by default.  You must select this option prior to installing DataGate for SQL Server (if installing AVR at the same time).

To Open, Start, Pause or Stop the SQL Server Manager

1.       After Microsoft SQL Server is installed, the SQL Service Manager icon for the local machine will display in the right hand corner of your taskbar.  To open SQL Server Service Manager, either double-click on the , or right-click on the icon to display a list of menu options.  The SQL Server Service Manager dialog will display.

Enter or select the following to SQL Server Service Manager: 

Server:

The name of the local computer will display.  Clicking on the arrow to the right will display all of the SQL Servers that you have access to.

Services:

The SQL Server service will display.  Clicking on the arrow to the right will display the other SQL services you have access to, such as SQL Server Agent, and Distributed Transaction Coordinator.

Refresh services…

Select this button to refresh the services. 

Start/Continue

Selecting this button starts the SQL Server Service.  After it is started, the option will be dimmed, and the icon will display with a green start icon next to it, indicating the service is started.  Select this option when the service is either currently paused, or stopped. 

Pause

Select this button to temporarily pause the SQL Server Service.   To continue service, select the Start/Continue button.

Stop

Select this button to stop the SQL Server Service.   To start the service again, select the Start/Continue button.

Auto-start service when OS starts:

Select this option to ensure that the SQL Server Service automatically restarts after your system has been restarted. 

Note that this option is not selected by default.  You must select this option prior to installing DataGate for SQL Server (if installing AVR at the same time).

2.       To close SQL Server Service Manager, right-click the SQL Server Service Manager icon on the taskbar, and then select the Exit menu option.

Back to Top

Release Notes 

SQL Server 2005

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

Licensing Changes

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

Connecting to SQL Server on Microsoft's XP Pro Service Pack 2

Installing Microsoft's XP Pro Service Pack 2, by default, enables the built-in Windows firewall settings which interfere with SQL Server connectivity. These settings, unless changed, won't let you connect to SQL Server 2000 (with either SQL Server's own Enterprise Manager or with ASNA's DataGate for SQL Server (DSS).

The Windows Firewall closes ports such as 445 that are used for file and printer sharing to prevent Internet computers from connecting to file and print shares on your computer or to other resources. When SQL Server is configured to listen for incoming client connections by using named pipes over a NetBIOS session, SQL Server communicates over TCP ports and these ports must be open. SQL Server clients that are trying to connect to SQL Server will be not be able to connect until SQL Server is set as an exception in Windows Firewall. To set SQL Server as an exception in Windows Firewall, use the steps that are listed here.'
 

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:

·          A Physical file is implemented as a Table.  An Indexed Physical file requires an Index with the same name as the Table.

·          A Simple Logical file is implemented as a View.  An Indexed Simple Logical file requires an Index with the same name as the View.

·          A Join Logical file is implemented as a View.  An Indexed Join Logical file requires an Index with the same name as the View.  This index marks which table is considered the ‘primary file’. 

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

·          Select individual columns from the table.

·          Select all columns via a ‘Select * from TableX’ by checking the ‘Clone base file fields in new format’ check box.

 

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 SQL Server’s Enterprise Manager.

Back to Top

Differences between DataGate^®, DataGate System i^®  and DataGate for SQL Server^®

Object Considerations

* NOTE:  For Max number of records per member, DataGate for Windows and Desktop Servers Release 8.0, Version 8.055 and higher support member/file sizes limit is 16 exabytes ( 2^4 * 2^60).

Index (Keys) Considerations

Data Access Considerations

Locking Considerations

Record Locking

DataGate System i

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

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

For read-only files DSS behaves like DataGate System i, 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 System i 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.

Loops involving SetLL/SetGT and Read/ReadE/ReadPE should be recoded 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. 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 .NET.

Field Considerations

Native SQL Server field interpretation:

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 won’t 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

Creating a Left Outer Join instead of an Inner Join.

From SQL Docs:

LEFT JOIN or LEFT OUTER JOIN

Calling Programs/Procedures Considerations

FAQ

Q. Will DataGate for SQL Server require SQL Server client licenses for end Users or will a server license be enough?

A. Every user will have to be licensed.  There are 2 ways of achieving this; one is to buy individual Per-Seat licenses, the other is to buy a per-processor license. The following link will take you to the MS prices as of Aug. 2001:

http://www.microsoft.com/sql/howtobuy/pricing/default.asp.  This is another good link with common licensing questions:

http://www.microsoft.com/catalog/display.asp?site=10145&subid=22&pg=4

Here is a breakpoint list of the 2 different license models for Standard Edition:

SQL Server 2000 Standard Edition - purchase processor license if:
1 processor machine: more than 24 users
2 processor machine: more than 53 users
4 processor machine: more than 112 users
8 processor machine: more than 229 users

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 a 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. DataGate supports all editions of SQL Server 2000 (Enterprise, Standard, MSDE…).

Q. Is the Library List supported?

A. Yes.

Q. I have an DataGate database with files in the root library, how can I get them into SQL Server?

A. Use the CopyLibrary option of DataGate Database Manager, user ‘/’ for the Source library and a named library for the target.

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 fields defined as BINARY in the original files will 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 defines as zoned.

Q. What is MSDE ?

A. Go to http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp for a description of MSDE.  The Addendum 1 is attached in case the link moves.

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 DataGate 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 .NET, 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 you 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 DataGate, that is, they can still be multi-format.  It is only the data files that have to be single-format. 

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

Introduction

ASNA's DataGate for SQL Server (DSS) 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 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.  The same AVR programs that connect to SQL Server will seamlessly connect to the iSeries and ASNA’s DataGate engine.

DSS 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.  With DSS, you can use the Library list, Physical and Logical files, even DB2 field types, like packed and zoned.
 

Some Restrictions Apply

Even though DSS 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 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, however.

Single Member Files:

SQL Server doesn’t have the concept of members of a table/view.  DSS 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.

No Qtemp:

Version 5.0 of DSS does not support the special library QTEMP.

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

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

One more thing, we highly recommend that you use Windows XP Professional or Windows 2000 workstation for your development machine.

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:

·          Master

·          Model

·          Msdb

·          Northwind

·          Pubs

·          Tempdb

Each one of these databases will be presented through DSS as individual libraries.  Since all databases are shown under a common database name, the "Label' parameter of a database name pointing to DataGate for SQL Server 8.0 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:

1.       You can do a visual inspection of the files in the library to verify/modify them to comply with the check list.

2.       Alternatively issue the copy command and wait for it to tell you whether there are any problems. 

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:

·          only one member

·          the member name the same as the file name

·          only one format

·          maximum of 16 key fields

·          maximum key length of 900 bytes

·          maximum of 1024 fields

·          maximum record length of 8060 bytes          

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

§   The string passed in the QrySelect must comply with the syntax of SQL Server, for example you should use the word ‘and’ not the symbol ‘&’.

§   If you are changing the order of the file by providing a value in the QryKeyFlds parameter, then you must also specify RANDOM(*NO) in the DCLDISKFILE to state that you will be accessing this file only consecutively.

Check Format ID:

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

Unlocking Records:

The behavior of DSS when the file is opened for update is similar to DataGate System i, 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. 

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

4.  Optimize Your Applications with New 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 three op-codes; SetRange, ReadRange and DeleteRange, as described below:

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

1.       When working with large SQL Server files, DSS 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.
 

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, 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 document).

New Error Conditions under DSS:

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.

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  

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, 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 document).

New Error Conditions under DSS

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.

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. This option is located in the “Compiler” Tab of the Project >Settings menu option.

New Range Opcodes

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

·          SetRange – Use the SetRange opcode in place of Setll when you’re doing Setll/Reade loops.

·          ReadRange – Use the ReadRange opcode in place of the Chain opcode when you’re doing Chain/Reade loops.

·          DeleteRange – Use the DeleteRange when performing delete loops. 
 

Considerations for Adapting Existing Applications to DSS

In DSS, 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.

·          Arrival sequence processing must be changed to indexed processing. The simplest method for handling this would be to employ the use of a key field in the file.

·          If you’re using multi-member files, you will need to provide an alternative method for handling the logic involving these files.

·          If your file’s record length is > 8060, you will need to consider changes necessary in order to make the record length smaller.

·          Be sure to include the range operations to enhance speed.

·          If you were using the *NOLOCK opcode for files opened for update, you will need to declare an *Input instance of the file for *NOLOCK access.

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

Other Considerations

·          It is recommended that you do not use ADBFEDIT to browse files that are not indexed because of the inability to scroll within the file.  These types of files can be viewed with SQL Server’s Enterprise Manager.