sap ase learning session 3 connectivity tools for sap ase

By R.N. Sangwan-www.rnsangwan.com

To Learn or Teach visit www.theskillpedia.com

Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com 1

• Interface file

• Interface File Entry Components

• The sql.ini file (Windows only)

• Editing the sql.ini file

• Using isql to Connect to the Service

• LDAP server

• Configuring libtcl.cfg for LDAP

• Adding a Server to the Directory Service

• ASE-Open Clients

• ISQL Client

Visit www.theskillpedia.com to learn or teach. By:

www.rnsangwan.com 2

• Sybase uses an interface file, this file lists the server and port of every known server. The file

looks like this

• ServerNamemaster tcp ether ServerName 5000

query tcp ether ServerName 5000

• It is located in $SYBASE directory


www.rnsangwan.com 3

• Server programs such as data servers, Replication Servers, and Replication Agents are

registered in an interfaces file (sql.ini in Windows) or an LDAP server.

• Generally, one interfaces file at each site contains entries for all of the local and remote

Replication Servers and data servers.

• The entry for each server includes its unique name and the network information that other

servers and client programs need to connect to it.

• Use a text editor to maintain your interfaces file

• If you are using network-based security (applicable for Replication Server), use the directory

services of your network security mechanism to register Replication Servers, Adaptive

Servers, and gateway software.


www.rnsangwan.com 4

• ID Server

• Replication Server

• RSSD (Replication Server System Database) Adaptive Server, SQL

Anywhere for this Replication Server

• ERSSD Replication Agent if a route is to be created from the current site

• Data servers with databases managed by this Replication Server

• Backup Server to back up Adaptive Server databases, including RSSDs

• Replication Servers at other sites that manage databases containing

primary data that is replicated to this site.

• Replication Servers at other sites with subscriptions for primary data

maintained at this site

• Other Replication Servers to which this Replication Server has a route

with no intermediate Replication Servers.


www.rnsangwan.com 5

• A file name sql.ini must be present in the C:\<install_dir>\DCO-16_0 directory.

• To modify the sql.ini file for a new server, use the dsedit utility that is supplied with Open

Client and Open Server.

• Using dsedit, enter the new server name, the TCP/IP host and port number.


www.rnsangwan.com 6

• If you do not use dsedit to edit the sql.ini file, you must run the ECDA related execution

scripts to change the local version of sql.ini (<install_dir>\DC-16_0\connectivity\ini\sql.ini) to

the global version.

• The global versions are at:

For ECDA Option for ODBC, in:

<install_dir>\DC-16_0\ini\sql.ini

For ECDA Option for Oracle, in:

<install_dir>\DC-16_0\ini\sql.ini


www.rnsangwan.com 7

• A master sql.ini file contains entries for all SAP ASE servers on the network,

and you can use it with every server and client connected to the network.

• By distributing copies of a master sql.ini file, you can ensure that all SAP

ASE products on the network interact.

• To maintain consistency in the sql.ini files on a network, make the changes to

one version of the file, then copy that file to the rest of the computers on the

network.

• Use Windows File Replication to copy the file to many computers.


www.rnsangwan.com 8

If you are using isql on the same machine, you must add a access

service entry to the interfaces (for UNIX) or sql.ini (for Windows) file

that points to the access service you are testing.

To verify the access service configuration using isql

• UNIX client machine : use dsedit to create an interfaces file entry for the

access service.

• Windows client machine : use dsedit to create a sql.ini file entry for the

access service.

Be sure to enter the access service name exactly as you defined it in

the configuration file.

Set the environment variables from a command line as appropriate for

your platform:

For UNIX : DC_SYBASE.csh or DC_SYBASE.sh

For Windows: DC_SYBASE.bat


www.rnsangwan.com 9

Syntax

isql -SServiceName -Uuserid -Ppassword where:

• ServiceName is the name of the access service exactly as you defined it in

the interfaces or sql.ini file.

• userid is a valid user ID for the target database.

• password is a valid password for the user ID in the target database.

• If the connection is successful, a 1> prompt appears.

If you cannot connect, the access service might not be running. Start the access

service, edit the configuration file, and then verify that the Enable at

Startup parameter is set to Yes.

This starts the access service automatically when the server is started.


www.rnsangwan.com 10

At the 1> prompt, query a table in the target database by entering a select statement and

pressing Return.

At the 2> prompt, enter:go

Press Return.

The query should run and return a result set, followed by a >1 prompt.

To exit isql, enter the following at the 1> prompt:

exit

Press Return.

A regular operating system command appears.



• An LDAP server provides global directory services for sharing component information such

as server names and connection properties.

• LDAP directory services allow components to look up directory information in a network-

based system.

• Any type of LDAP service or gateway is an LDAP server. An LDAP driver calls LDAP client

libraries to establish connections to an LDAP server.

• The LDAP driver and client libraries define the communication protocol and content of

messages exchanged between clients and servers. LDAP runs directly over the

Transmission Control Protocol (TCP).



• When the LDAP driver connects to the LDAP server, the server establishes the connection

based on one of two authentication models:

• Anonymous access –for read-only privileges, or

• User name and password access – to access Replication Server.

• Replication Server uses the access information as an extension to the LDAP URL. Access

information is taken from this file:• $SYBASE/$SYBASE_OCS/config/libtcl.cfg

• %SYBASE%\$SYBASE_OCS%\ini\libtcl.cfg (Windows 2000, 2003)

• Replication Server uses Open Client/Server libraries to connect to LDAP servers and Open

Client/Server configurations and procedures to set up and maintain LDAP services.



• Use the libtcl.cfg file to specify an LDAP server name, port number, DIT base, user name,

and password to connect to an LDAP server.

• When you specify an LDAP server in the libtcl.cfg file, the server information becomes

accessible only from the LDAP server; the interfaces file is ignored.

• Open Client and Open Server applications that use the -I option at start-up override the

libtcl.cfg file and use the interfaces file.



Configure the libtcl.cfg file to use directory services, using any standard ASCII text editor:

• Remove the semicolon (;) comment markers from the beginning of the LDAP URL lines in the libtcl.cfg

file under the [DIRECTORY] entry.

• Add the LDAP URL under the [DIRECTORY] entry ensuring that the LDAP URL is on a single line.

ldap=libsybdldap.dll

ldap://host:port/ditbase??scope??

bindname=username?password

Example:

[DIRECTORY]

ldap=libsybdldap.dll

ldap://server.example.com:11389/dc=sybase,dc=com??one??

bindname=cn=Manager,dc=sybase,dc=com?secret

On Windows x64, the .dll file is called libsybdldap64.dll.



• Verify that the appropriate environment variable points to the required third-party libraries.

• The Netscape LDAP SDK libraries are in %SYBASE%\%SYBASE_OCS%\dll.

• The Windows PATH environment variable must include this directory.



• Use dsedit utility to add a server to the directory service.

1. From Windows, select Start > Programs > Sybase > Connectivity > Open Client Directory

Service Editor.

2. Select LDAP from the list of servers, and click OK.

3. Click Add New Server Entry, and enter:• Server name

• Security mechanism – (Optional) there is a list of security mechanism OIDs in %SYBASE%\ini\objectid.dat.

4. Click Add New Network Transport, and:• Select the transport type.

• Enter the host name.

• Enter the port number.

5. Click OK twice to exit the dsedit utility.



Interface (API) between client systems and Sybase servers is Open

Client. It has two forms:

Runtime

◦ It is a collection of libraries, also called as dlls on W32 platforms,

that connects client applications to Sybase and Microsoft

servers.

◦ It can also be used to connect to any server that implements the

Tabular Data Streams (TDS) protocol.

◦ Even if we are running isql on exactly the same machine as ASE

itself, communication will still need Open Client.

◦ The protocol implementation will decide whether to take a

physical network to establish connection.

Development

◦ This has all the libraries from the runtime version and header

files, other files, library files etc, that developers require to build

client apps that are able to connect to Sybase servers.

18


www.rnsangwan.com

CT-lib - CT-lib is a completely re-written version of Open Client that

was released in the early '90s.

TDS Protocol – The name of the protocol, that is used to connect

Sybase clients with Sybase servers is Tabular Data Streams or TDS.

19


www.rnsangwan.com

Interactive SQL parser to Adaptive Server.

This utility is in $SYBASE/$SYBASE_OCS/bin.

ISQL can be run in non-interactive "batch" modee.g.

isql -Usa -P sybase -SASE1 -i t1.sql >abc.txt

20


www.rnsangwan.com



• To use isql interactively, give the command isql at command prompt.

The isql program accepts SQL commands and sends them to

Adaptive Server.

• The results are formatted and then printed on standard output.

• Exit isql with quit or exit.

• Terminate a command by typing a line beginning with “go” or another

command terminator, if the -c parameter is used.

• You can follow the command terminator with an integer to specify how

many times to run the command.

For example, to execute this command 100 times, type:

select x = 1

go 100

The results display once at the end of execution.

To Learn or Teach the Skill you

know, Visit www.theskillpedia.com



http://www.theskillpedia.com/

sap ase learning session 3 connectivity tools for sap ase

Education