Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search
Chapter 14 Remote Stored Procedures and Host-Resident requests [Table of Contents] Appendix B Quick Reference

Access Service User's Guide DirectConnect

[-] Appendix A Backward compatibility with MDI Database Gateways

Chapter 15

Backward compatibility with MDI Database Gateways

This appendix includes information about the differences between DirectConnect and the MDI Database Gateway. Topics covered are:

For specific information about the MDI Database Gateway, refer to MDI Database Gateway Installation and Administration Guide and MDI Database Gateway User's Guide for your target database and platform.

Backward compatibility is provided only as an interim measure to allow you to upgrade your client applications to the syntax supported in the access service version. We strongly encourage you to update your client applications to the new syntax. To use backward compatible features in the access service, you must enable the GatewayCompatible property, which is described in this chapter.

General information

The access service provides compatibility for customer applications written to use previous releases of MDI Database Gateways. To ensure compatibility with earlier Database Gateways, the access service:

DirectConnect provides compatible functions to the MDI Database Gateway for MVS, Versions 2.03.02 and 2.05, with the exceptions described in this appendix.

InfoHub

DirectConnect Access Service does not provide InfoHub access; however, InfoHub can be accessed through TRS.

Configuration considerations

Following are configuration differences between the access service and the MDI Database Gateway.

Allocate property

The access service Allocate property does not support chunking. If you issue a set statement to change the Allocate property to CHUNK, the access service returns an error.

ApplicationValidationFile property

The access service continues to support application validation via the ApplicationValidationFile property. We recommend using the Service Name Redirection capability instead of application validation. If you choose to use both Service Name Redirection and application validation, Service Name Redirection takes precedence.

Code Sets

All MDI Database Gateway code set alphanumeric characters and most punctuation characters map correctly to access service code sets. We recommend using the access service code sets whenever possible.

The MDI Database Gateway EBCDIC code set contains some nonstandard extended character mappings. If you have applications and data that depend on one or more of the unusual gateway control codes or extended character mappings, set the access service client and target code set properties as follows: 

DefaultClientCodeset=819
DefaultTargetCodeset=gwebcdic

For detailed information about code set properties, see "DefaultClientCodeset" and "DefaultTargetCodeset" .

Convert Table Communication parameter

The access service does not support the MDI Database Gateway Convert Table parameter for ASCII-EBCDIC translation.

Disable Binary Support parameter

The access service BinaryResults and GraphicResults datatype conversion properties replace the MDI Database Gateway Disable Binary Support parameter.

GatewayCompatible property

To use MDI Database Gateway-compatible settings, set the access service GatewayCompatible property value to yes. After you change your client applications to access service settings, set the GatewayCompatible property to no to improve performance.

Huffman compression

DirectConnect does not provide the ability to apply Huffman compression to data.

Summary messages

The access service does not support the MDI Database Gateway Summary Messages configuration parameter, which sends a result set summarizing SQL processing results to the client application.

Verbose Error messages arameter

The access service SendWarningMessages client interaction property replaces the MDI Database Gateway Verbose error messages parameter.

Version property

To customize an MDI Database Gateway-compatible version string for use by your client applications, configure the access service Version property. For more information, see "Version" .

Global variables and set statements

Following are differences between the access service and the MDI Database Gateway global variables and set statements.

COBOL and IXF support

DirectConnect does not convert data to COBOL or IXF . The access service returns an error message if a client application issues the command: 

SET CONVERT ALL [COBOL | IXF]

For backward compatibility, the access service supports the following command: 

SET CONVERT ALL [STD | CHAR]

The access service processes the SET CONVERT ALL command the same way it processes the following access service command: 

set AllResults [autoconvert | char]

Global variable and set statement comparisons

The access service supports MDI Database Gateway global variables and set statements by processing them as shown in the following table.

Corresponding global variables and setstatements

MDI Database Gateway Global Variable and set Statement

Corresponding Access Service Global Variable and set Statement

SELECT @@ALLOCATE

SET ALLOCATE {Connect | REQUEST | CHUNK}

select @@Allocate

set Allocate {connect | request}

CHUNK is not supported in the access service.

SELECT @@AS_VERSION

select @@MainframeVersion

SELECT @@BULK_COMMIT_COUNT

SET BULK_COMMIT_COUNT number

select @@BulkCommitCount

set BulkCommitCount integer

SELECT @@CLOSE_ON_ENDTRAN (only returns YES or NO)

SET CLOSE_ON_ENDTRAN {YES | ON | NO | OFF}

select @@CloseOnEndtran

set CloseOnEndtran {on | off}

SELECT @@ConnectIONS

No set statement

select @@Connections

No set statement.

No global variable

SET CONVERT ALL {STD | CHAR | COBOL | IXF}

select @@AllResults

set AllResults {autoconvert | char}

STD = autoconvert (default) The access service does not support SET CONVERT ALL {COBOL | IXF}

SELECT @@CONVERT_SMALLINT

SET CONVERT SMALLINT [=] {STD |CHAR}

select @@Int2Results

set Int2Results {smallint | char}

STD = smallint (default)

SELECT @@CONVERT_INTEGER

SET CONVERT INTEGER [=] {STD | CHAR}

select @@Int4Results

set Int4Results {int | char}

STD = int (default)

SELECT @@CONVERT_CHAR

SET CONVERT CHAR [=] {STD | CHAR}

Has no effect in the access service.

SELECT @@CONVERT_VARCHAR

SET CONVERT VARCHAR [=] {STD | CHAR}

Has no effect in the access service

SELECT @@CONVERT_LVARCHAR

SET CONVERT LVARCHAR [=] {STD | CHAR}

Has no effect in the access service.

SELECT @@CONVERT_FLOAT

SET CONVERT FLOAT [=] {STD | CHAR | FLOAT | REAL}

select @@FloatResults

set FloatResults {float | char | real}

STD = float (default)

SELECT @@CONVERT_DECIMAL

SET CONVERT DECIMAL [=] {STD | CHAR | FLOAT | MONEY | BCD | REAL | SMALLMONEY}

select @@DecimalResults

set DecimalResults {autoconvert | char | float | money | bcd | real | money4}

STD = autoconvert (default) SMALLMONEY = money4

SELECT @@CONVERT_DATE

SET CONVERT DATE [=] {STD | ISO | USA | EUR | SMALLDATETIME}

select @@DateResults

set DateResults {datetime | char_iso | char_usa | char_eur | datetime4}

STD = datetime (default) ISO = char_iso USA = char_usa EUR = char_eur SMALLDATETIME = datetime4

SELECT @@CONVERT_TIME

SET CONVERT TIME [=] {STD | ISO | USA | EUR | SMALLDATETIME}

select @@TimeResults

set TimeResults {datetime | char_iso | char_usa | char_eur | datetime4}

STD = datetime (default) ISO = char_iso USA = char_usa EUR = char_eur SMALLDATETIME = datetime4

SELECT @@CONVERT_TIMESTAMP

SET CONVERT TIMESTAMP [=] {STD | CHAR | SMALLDATETIME}

select @@DateTimeResults

set DateTimeResults {datetime | char_iso | char_usa | char_eur | datetime4}

STD = datetime (default) CHAR = char_iso SMALLDATETIME = datetime4

SELECT @@CSP_CATQUALIFIER

SET CSP_CATQUALIFIER qualifier

select @@CSPCatalogQualifier

set CSPCatalogQualfier qualifier

SELECT @@CSP_CATQUALIFIER_SET

No set statement

No global variable. Always returns YES.

SELECT @@CSP_DBNAME

SET CSP_DBNAME {NULL | dbname}

select @@CSPDBName

set CSPDBName {NULL | dbname}

SELECT @@CSP_EXCLUDENONAUTH

SET CSP_EXCLUDENONAUTH {NO | YES}

select @@CSPExclusions

set CSPExclusions nonauth

Specifies whether CSP result sets exclude nonauthorized tables. YES excludes nonauthorized tables.

SELECT @@CSP_INCLUDEPUBLIC

SET CSP_INCLUDEPUBLIC {NO | YES}

select @@CSPExclusions

set CSPExclusions nonauthpublic

Specifies whether CSP result sets include public tables. YES includes public tables.

SELECT @@CSP_INCLUDEALIAS

SET CSP_INCLUDEALIAS {NO | YES}

select @@CSPIncludeAlias

set CSPIncludeAlias {no | yes}

SELECT @@CSP_INCLUDESYNONYM

SET CSP_INCLUDESYNONYM {NO | YES}

select @@CSPIncludeSynonym

set CSPIncludeSynonym {no | yes}

SELECT @@CSP_INCLUDESYSTEM

SET CSP_INCLUDESYSTEM {NO | YES}

select @@CSPIncludeSystem

set CSPIncludeSystem {no | yes}

SELECT @@CSP_INCLUDETABLE

SET CSP_INCLUDETABLE {NO | YES}

select @@CSPIncludeTable

set CSPIncludeTable {no | yes}

SELECT @@CSP_INCLUDEVIEW

SET CSP_INCLUDEVIEW {NO | YES}

select @@CSPIncludeView

set CSPIncludeView {no | yes}

SELECT @@CSP_INCLUDE_SET

No set statement

No global variable. Always returns YES.

SELECT @@CSP_QUALBYDBNAME

SET CSP_QUALBYDBNAME {NO | YES}

select @@CSPQualByDBName

set CSPQualByDBName {no | yes}

SELECT @@CSP_QUALBYDBNAME_SET

No set statement

No global variable. Always returns YES.

SELECT @@CSP_XAUTH

SET CSP_XAUTH {NONE | NONAUTH | NONAUTH_INCL_PUBLIC | BY_USER}

select @@CSPExclusions

set CSPExclusions {none | nonauth | nonauthpublic | user}

SELECT @@CSP_XAUTH_SET

No set statement

No global variable. Always returns YES.

SELECT @@DECIMAL_SEP_IS_COMMA

No set statement

select @@ClientDecimalSeparator

SELECT @@DEFAULTED_ROWCOUNT

No set statement

select @@DefaultedRowCount

SELECT @@ERROR

No set statement

select @@Error

SELECT @@LONG_TRANS

SET LONG_TRANS {NO | YES}

select @@TransactionMode

Specifies whether the TransactionMode property is set to long. YES sets the property to long.

The access service set statement is: set TransactionMode {long | short}

SELECT @@MAX_Connections

No set statement

select @@MaxSvcConnections

SELECT @@NOEXEC (Always returns YES or NO. The default is YES.)

SET NOEXEC {YES | ON | NO | OFF}

select @@noexec

set noexec {on | off}

SELECT @@PREVIEW_COUNT

SET ROWCOUNT number

select @@MaxRowsReturned

set MaxRowsReturned integer

SELECT @@REJECTED_ROWCOUNT

No set statement

select @@RejectedRowCount

SELECT @@RMT_ERROR

No set statement

select @@TargetError

SELECT @@RESULT_SIZE

SET RESULTSIZE number

select @@MaxResultSize

set MaxResultSize integer

SELECT @@ROWCOUNT

No set statement

select @@Rowcount

SELECT @@STOP_CONDITION

SET STOP_CONDITION {ERROR | WARNING | NONE}

select @@StopCondition

set StopCondition {error | warning | none}

No global variable.

SET STOP_ON_ERROR {YES | NO}

Specifies whether the StopCondition property is set to error. YES means the property is set to error. NO means it is set to none.

SELECT @@TEXTSIZE

SET TEXTSIZE number

select @@TextSize

set TextSize number

SELECT @@TRAN_SUPPORTED

Always returns PRGM.

SELECT @@TRANSFER_BATCH

SET TRANSFER_BATCH number

select @@TransferBatch

set TransferBatch integer

SELECT @@TRANSFER_ERROR_COUNT

SET TRANSFER_ERROR_COUNT number

select @@TransferErrorCount

set TransferErrorCount integer

SELECT @@TRANSFORM

SET TRANSFORM {DB2 | TSQL0 | TSQL1 | TSQL2}

select @@SQLTransformation

set SQLTransformation {db2 |tsql0 | tsql1 | tsql2 | passthrough | sybase}

The access service maps db2 and tsql0 to access service passthrough mode. The access service supports tsql1 and tsql2 modes but does not map these modes to any access service mode.

SELECT @@VERBOSE

SET VERBOSE {NO | YES}

select @@SendWarningMessages

set SendWarningMessages {no | yes}

The access service SendWarningMessages global variable and set statement are not an exact match with the MDI Database Gateway VERBOSE global variable and set statement.

SELECT @@VERSION

No set statement

select @@version

For descriptions of access service global variables and set statements, see Chapter 3, "Querying and setting operating values"

SQL Transformation modes

DirectConnect provides two new SQL transformation modes to replace the MDI Database Gateway transformation modes:

For client applications written for the MDI Database Gateway transformation default setting of TSQL1, you need to change that setting to either Passthrough mode or Sybase mode. For configuration information, see "SQLTransformation" . For descriptions of Passthrough and Sybase modes, see "SQL Transformation modes" .

Client applications can still issue set statements containing the MDI Database Gateway values of DB2 , TSQL0 , TSQL1 , and TSQL2. These settings are mapped to the new modes as follows:

Warning!

The default SQL transformation mode for MDI Database Gateway is TSQL1.

The MDI Database Gateway modes are supported for backward compatibility only. We recommend that you use the new modes in any new application development and convert existing applications to the new transformation modes and syntax.

Remote Procedure Calls (RPCs)

You can continue to use pcsql in place of the dcon keyword when you execute a SQL language statement as a SQL Server RPC. To execute a SQL language statement to DirectConnect through a SQL Server RPC, use the following syntax (shown here using ISQL): 

C:> isql -Ssqlserver -Uuser -Ppassword
1> execute directconnect...dcon "select * from user.authors"
2> go

where:

The DirectConnect RPC event handler is sensitive to several key RPC names. In this case, the RPC keyword dcon is a special name that replaces pcsql for previous versions of the MDI Database Gateway.

For backward compatibility with Net- Gateway, the access service recognizes syrt in place of the dcon keyword.

We recommend that you use the dcon keyword.

The following are guidelines for RPCs that will allow you to operate within the processing limitation of 255 characters:

The following is an example of an SQL statement that provides sample code necessary for RPCs. The example parses the SQL statements into appropriate 255 character segments and stores these segments in the appropriate variables. The pcsql or dcon keyword, in the execute command, will concatenate the variables into a single statement: 

Create procedure x as
Begin
 Declare @ temp1 varchar (255)
 Declare @ temp2 varchar (255)
 ......
 .......
 .......
 Select temp1 = SQL text
 Select temp2 = SQL text
 .......
 .......
 .......
 Execute Servername ... pcsql or dcon @ temp1, temp2, ......
 end

Transfer functionality

Always use the transfer from command from DB2 when you transfer data between:

Using transfer from guarantees native datatype mapping and returns the proper datatype result set.

If you are now using DBCS, note that binary data is returned differently for these client applicatio ns.

Common Stored Procedures

This section describes how access service stored procedure support differs from MDI Database Gateway stored procedure support.

Parameters

For CSPs and system procedures, an access service supports both positional and named parameters, but not in the same statement.

The parameter must be preceded by an "at" sign (@). For MDI Database Gateway compatibility, an access service supports parameter names that are preceded by either an "at" sign (@) or an ampersand (&).

The MDI Database Gateway automatically converts parameter values that are not in quotes to uppercase letters. The access service does not automatically convert parameters to uppercase letters.

Syntax

For MDI Database Gateway compatibility, the access service also supports the following syntax: 

use procedure procedure_name
parm1, parm2, . . .

where:

We recommend using the access service syntax (see "Syntax" ).

Catalog Stored Procedures

The access service sp_tables result set can return more rows and data than the MDI Database Gateway sp_tables result set. The access service can query both the system catalog and the collection catalog.

System procedures

For MDI Database Gateway compatibility, the access service accepts the name mdi_groups and makes a call to the sp_groups system procedure.

Client Services Applications (CSAs)

In the MDI Database Gateway, the Client Services for CICS feature allowed a CSA to act as a client to certain LAN services, using an APPC connection and the MDI Database Gateway Access Server (DGACCSRV), a component of the MDI Database Gateway that resided on the LAN.

The following figure shows the MDI Database Gateway environment and how a CSA on the mainframe can act as a client to a SQL Server on the LAN.

Figure 15-1: CSAs using MDI Database Gateway Access Serverraster

In DirectConnect, a CSA can act as a client using Open ClientConnect protocol on the mainframe and the Mainframe Client Connect (MCC) component on the LAN.

The following figure shows the DirectConnect environment:

Figure 15-2: CSAs using Open ClientConnect protocolraster

For information about Mainframe Client Connect, see the DirectConnect Transaction Router Service User's Guide.

Remote Stored Procedures (RSPs)

The access service supports RSPs. However, the access service does not pass IXF records to RSPs.

Chapter 14 Remote Stored Procedures and Host-Resident requests [Table of Contents] Appendix B Quick Reference