ASNA DataGate 15.0 for SQL Server©

DataGate for SQL Server Help

This document contains information and advice on using DataGate for SQL Server. The terms DataGate for SQL Server and DSS are used interchangably thoughout this guide.

What's New in 15.0?

DataGate for SQL Server 15.0 includes built-in Secure Socket Layer (SSL) protection, a new layer of defense between your data and hostile internet prescences. For details on how DataGate handles SSL, see the SSL for DataGate Guide and the SSL Deployment Guide.

What is DSS?

DataGate for SQL Server (DSS) is a client/server database middleware that connects Windows clients with MS SQL Server. The concepts surfaced by the DSS API are based on the IBM i database (DB2/400): ISAM style record level access. The client is built as a .NET assembly. The client and the server communicate via TCP/IP using its own protocol.

The server implementation for MS SQL Server runs as a multi-threaded Windows system service (dgServer.exe); this service executes on the same machine where the SQL Server instance is running. dgServer.exe is an unmanaged C++ program that uses SqlOleDB to access SQL Server data and schema.

Each DataGate Client connection results in two OLEDB session connections from the DataGate Server to SQL Server. These connections can stay open throughout the day doing select, update, delete and insert operations. The OLEDB connections close when the application closes the DataGate connection.

In order to satisfy the ISAM style requests, DSS makes heavy use of Scrollable Server Cursors. DSS associates a server cursor with each open file; it typically requests a single row at a time from the rowsets used in conjunction with the server cursor. Each application can maintain a large set of active server cursors. Record locks are maintained via row locks on the server cursor.

The mapping of OS/400 construct to SQL Server is as follows:

OS/400 constructSQL Server artifacts
Physical FileTable with an index. The Index is named as the Table name.
Logical File – SimpleView plus an index on the Table. The Index is named as the View name.
Logical File – JoinView (outer or inner join) plus an index on the first Table. The Index is named as the View name
Logical File - Multiformat Not supported
MemberIf a physical of simple logical file allows for multiple members, in addition to the file’s table (or view), each member also uses a table (or view)
Print fileTable with a single row containing the print file description
Data AreaTable with a single row
Library ListCollection of database names
QTEMPTemporary tables and view created on a designated database and associated with each OLEDB session
Record LockServer Cursor Lock
Object LockSQL Server ‘application lock’

DSS ‘decorates’ the Tables and Views with the following custom properties

PropertyObjectValue Usage
Access PathFile’s table or viewKey description: Key fields, direction and uniqueness
Text ???File or member's table or viewUses provided descriptions
FileOrMemberFile’s table or view*BOTH: Single Member File *FILE: Multimember File
Member’s table or viewMember’s parent file name
Wait for RecordFile’s table or viewSeconds to wait for a record if it is locked by some other application

In This Guide