Network Database Protocol Internet Draft June 23, 1992 Expiration Date: December 23, 1992 Daisy Shen daisy@watson.ibm.com IBM T. J. Watson Research Center P. O. Box 218 Yorktown Heights, N.Y. 10598 TABLE OF CONTENTS _________________ Network Database Protocol by Daisy Shen . . . . . . . . . 1 Status of this Memo . . . . . . . . . . . . . . . . . . . 1 Abstract . . . . . . . . . . . . . . . . . . . . . . . . 1 Objectives . . . . . . . . . . . . . . . . . . . . . . . 1 Overview . . . . . . . . . . . . . . . . . . . . . . . . 2 Requirements . . . . . . . . . . . . . . . . . . . . . 2 Design . . . . . . . . . . . . . . . . . . . . . . . . . 3 The Client Machine . . . . . . . . . . . . . . . . . . 3 End-User Applications . . . . . . . . . . . . . . . . 4 NetDB Client . . . . . . . . . . . . . . . . . . . . 4 The RPC Client . . . . . . . . . . . . . . . . . . . 4 The Server Machine . . . . . . . . . . . . . . . . . . 5 The RPC Server . . . . . . . . . . . . . . . . . . . 5 The Portmap Manager Server . . . . . . . . . . . . . 6 The NetDB Server . . . . . . . . . . . . . . . . . . 6 Multiple Databases . . . . . . . . . . . . . . . . . 8 The DB Utility . . . . . . . . . . . . . . . . . . . 8 Database . . . . . . . . . . . . . . . . . . . . . . 9 Security . . . . . . . . . . . . . . . . . . . . . . . 9 The Main Control Block, NDBC . . . . . . . . . . . . 10 The Main Control Block, NDBC, is shown below: . . . 10 Implementation . . . . . . . . . . . . . . . . . . . 12 Operational Procedures . . . . . . . . . . . . . . . 12 Components in the Network DataBase Protocol . . . . . . 12 Relationship Among Portmap Manager Server and NetDB Serv- ers . . . . . . . . . . . . . . . . . . . . . . . . . 13 Table of Contents ii NETWORK DATABASE PROTOCOL BY DAISY SHEN _______________________________________ STATUS OF THIS MEMO ___________________ This document is an Internet Draft. Internet Drafts are working documents of the Internet Engineering Task Force (IETF), its Areas, and its Working Groups. Note that other groups may also distribute working documents as Internet Drafts. Internet Drafts are draft documents valid for a maximum of six months. Internet Drafts may be updated, replaced, or obsoleted by other documents at any time. It is not appropriate to use Internet Drafts as reference material or to cite them other than as a "working draft" or "work in progress." Please check the I-D abstract listing contained in each Internet Draft directory to learn the current status of this or any other Internet Draft. This memo provides information for the Internet community. It does not specify any Internet standard. Distribution of this memo is unlimited. Please send comments to ietf- ndb@ucdavis.edu. ABSTRACT ________ This memo defines a protocol for use with relational data- base systems in a TCP/IP based internet environment. This protocol is intended to make interoperability among differ- ent database systems possible. It is built on RPC (Remote Procedure Call) with a client/server type of relationship. This memo also defines the concept of Unit of Work. The work of Network Database is involved with Data Conversion, Security, I/O Buffer Management, and Transaction Management. They will be described one by one in this document. OBJECTIVES __________ The Network Database protocol has three objectives: 1. To allow any workstation/mainframe users and applica- tions to issue SQL statements interactively or imbed SQL statements within the application program, such as a SE- LECT statement, to access any database with any operat- ing system. 2. To distribute database host machine's applications to the requester machine which is an independent work- station or a mainframe. Some requests need a lot of CPU time or memory, but some don't. The Network Database will make the requester machine more capable of handling some of the work load for the host where the database resides, unless the request needs more CPU time and mem- ory than requester machines can handle. Therefore, a task can be distributed. Part of it is processed on the requester machine, and part of it is processed on the server machine where the database is. Network Database Protocol by Daisy Shen 1 3. Both the server and the client can be any workstations or mainframes. This is the prospect of interoperabi- lity. OVERVIEW ________ This is a model of a client/server type relationship. There are two parts involved. One is the client, and the other is the server. The client is the one who issues a request, and the server is the one who provides the service for the re- quest. The client machine can be called the requester ma- chine, and the server machine can be called the database machine. In spite of the differences among all hardware, operating systems, and databases, there should be a piece of software to make them all communicate among themselves on accessing databases. That is what Network Database is all about. Requirements ____________ The NDB protocol is straight forward; it uses the existing standard relational database systems. There is no need to change the design, structure or implementation of existing relational database systems. However, it does have some re- quirements. The requirements are listed below: TCP/IP be installed. NDB protocol uses TCP/IP as its communication's protocol. NDB builds client and server code on top of TCP/IP. TCP/IP has been standardized. RPC For both client and server machines, RPC should be installed. At the moment, there is no standard RPC implementation. However, NDB protocol is not restricted to any RPC implementation. Once RPC is standardized, NDB will then use the standard RPC. Before it happens, any implementation of RPC can be used by NDB. Security There must be a means for security. However, it is not restricted. The common one is Kerboros. If either client or server machine does not have Kerboros installed, password checking or other se- curity system should be used. Data Conversion Abstract Syntax Notation(ASN) with Basic En- coding Rules(BER) is an ISO standard for data rep- Network Database Protocol by Daisy Shen 2 resentation and data conversion. It has been used widely. NDB adopted this standard for its data representation and data conversion. If the implenetor does not want to use ASN/BER, he/she may use a similar format as ASN does. It is shown below: Format of data representation ------------------------------------- | Tag | Actual Value | |-----------------------------------| | Data type | Length | Value | ------------------------------------- Data Structure There is a control block passing between a client and a server. It contains required infor- mation for both client and server machine. The detailed information will be discussed later. Standard Relational Database Since NDB is a network database system, a relational database system is required. NDB can be used for all standard relational data- base systems. Although these relational database systems are standard, their implementation can be varied. SQL preprocessor All standard relational database systems have a standard procedure which is SQL pre- processor. Since each database has its own imple- mentation, each SQL preprocessor of its database system is implemented differently. All components listed below should be all portable except the DB Utility. DB Utility is a component which performs SQL preprocessing. Although implementing DB Util- ity varies somewhat, its basic functions, proce- dures, and structures should be the same. DESIGN ______ The Client Machine __________________ Clients can be any mainframe or any workstation where end users submit requests. The components of a client machine are an End-User application, a NetDB client, and an RPC cli- ent. Network Database Protocol by Daisy Shen 3 End-User Applications There are two kinds of end-user application. One is appli- cation programs that run on the client machine, and imbed SQL statements to access a remote database machine. The other is to issue SQL statements from the client machine interactively to access a remote database machine. The cli- ent machine runs the requests as its own tasks. Imbed SQL statements in an Application Program can write an application program such as C, COBOL, FORTRAN, PL/I, PASCAL...etc., and imbed SQL state- ments in it. SQL statements will be processed the same way as interactive SQL commands. Interactive SQL Commands An end user can type in a SQL com- mand directly from the client machine to query or access data from a database such as Oracle or Ingres. NetDB Client The NetDB Client receives requests from the end-user appli- cation, parses the input arguments, and sets up a Unit of Work by its status. It creates a thread of communication for the client. It packages the requests into a standard format which is called NDBC main control block, specifies the name of database to be connected, sets up an I/O buffer, and issues RPC calls to deliver the requests to the network. The RPC Client Although there is no industrial standard RPC, both SunMicrosystems' and APOLO/HP's implementations have been widely used. Many other vendors have ported one of these versions to their systems. If there will be a standard RPC, NetDB will use it. Otherwise, different implementations of RPC is not a major issue for the NetDB protocol. The RPC client performs a function call which is delivered to a re- mote machine; therefore, it is used by this protocol to de- liver the calls from the client machine to the server machine. The RPC Client handles all the complicated network management work such as creating a socket, connecting sock- ets, maintaining a thread, sending and receiving data through a socket...etc. All those above functions are transparent to rpc users and end users. Network Database Protocol by Daisy Shen 4 When the NetDB Client finishes packaging the request and is- sues a procedure call which sends the request to the server, it uses the RPC protocol. It is the module which manages RPC calls and delivers calls through TCP/IP to the destina- tion server machine. RPC uses the client/server model, and is designed for the support of network applications. Using RPC avoids the details of interfacing to the network, and provides network services without requiring any underlying network. The call is transparent. The caller is not ex- plicitly aware of RPC. The call is like a system call in C. It is independent of transport protocols. If RPC is running on top of TCP/IP; most of the work of reliable transport is done. But the application still needs time-outs and recon- nection to handle server crashes. If RPC is running on top of UDP, the application must implement its own retransmission and time-out policy. NetDB can run on top of TCP and UDP. It is one of the parameters that users have to specify. The default value is TCP. The Server Machine __________________ The server machine is a machine where the database system resides. The server performs all the SQL requests were sent from clients. Many issues have to be solved such as secu- rity, data conversion, data buffer management, transaction management...etc. There are four components involved in the RPC server. They are the RPC server, the Network DataBase server (NetDB server), the DataBase Utility (DB Utility), and the Database. The RPC Server The RPC server works hand in hand with the RPC client, and performs the other half of the functions of creating a socket, connecting sockets, maintaining a socket...etc. as described in the session of the RPC client. When the server machine receives an RPC from a client machine, its RPC server will interpret it, verify it, and distribute it to the NetDB server. The RPC Server process is dormant awaiting the arrival of a call message. When one arrives, the server process extracts the procedure's parameters, computes the results, sends a reply message, and awaits the next call message. Each RPC must communicate with a dedicated port. As part of its in- itialization, a server program calls its host's PORTMAP to create a portmap entry for its program and version number. A server machine is required to handle many clients. The Network Database Protocol by Daisy Shen 5 TCP server keeps state of each state for each open client's connection; therefore, the number of clients is limited by the machine resources. The UDP server does not keep any state about the client, it can handle unlimited clients. The Portmap Manager Server The Portmap Manager Server is a server which manages all the program numbers that NetDB servers use. It initializes a table which contains information of each NetDB server ma- chine's status. It is responsible for updating the status when it receives a request from its client. The Portmap Manager Server only has two kind of clients. One is NetDB clients and the other one is NetDB servers. The Portmap Manager Server does not need a well-known port. The port number is allocated by the portmapper during run time. The NetDB Server The NetDB server receives the main control block (NDBC) which was created by the NetDB Client and was passed from the NetDB Client to here. The NetDB server will verify all the items in the control block, and use the information to set up the transaction manager table. It also performs transaction management. The NetDB Server does not need a well-known port. The port number is allocated by the portmapper during run time. Transaction Manager Most databases perform single thread al- though there are a few databases that can perform multiple threads. It works well with one to one client/server re- lationship. NetDB wants to make a server machine work with multiple client machines. It causes a problem for the server to manage different requests from multiple clients simultaneously. Although clients send requests to the server machine simultaneously, the server has to process them one by one. In order for the server to manage multiple threads, the concept of a Unit of Work is created, and the server machine CONNECT as other user is used. The trans- action manager performs the function of managing Unit of Work. Unit Of Work(UOW) Unit of Work is a mark for the server to recognize the threads which came from clients. A client starts a UOW by issuing a begin command. The client's data- base machine's user id and and its password will be re- quested. The client has to type in both; however, the password will not be shown on the screen. After begin, the Network Database Protocol by Daisy Shen 6 client can issue as many SQL statements as it wants. The client issues an end command to end the UOW. Only the first request in UOW will be prompted for database machine's userid and its password. Both pieces of information will be stored after the first request and destroyed when the last request is issued. If a client only wants to send one SQL request, the client doesn't have to start a UOW. The client simply sends one request, and the server treats the request as an entire UOW. The database machine's userid and its password are required. Name Mapping For security, Kerberos must be used to authenticate that the client is really who he claims. For those database machines where Kerberos is not ready, the name mapping is necessary to the Network Database. Usually a database only recognizes userids on its machine; there- fore, mapping clients' machine ids and userids to the data- base host's userids has to be done. When the NetDB server receives a SQL request, before it hands the request to the database system it has to perform the name mapping. The function only needs to be done at the beginning of each Unit of Work. The first request of a Unit of Work has to append a password of its database host ma- chine's userid so that the NetDB server can do authentication. The NetDB server goes to the name mapping file/database to match the corresponding machine id and userid. It then is- sues a call to the database host to verify if the userid and the password are matched. If the verification is positive, the server continues to perform the request. Otherwise, the request is rejected. Data Conversion One of the advantages of RPC is that the RPC application does not have to do any data conversion. RPC uses its own data conversion routine to convert different data representations between different computer architec- tures. However, NetDB cannot take this advantage. The main reason is that to use RPC's data conversion, the application has to give the data type as one of the arguments for RPC to do the conversion when the call is made. In NDB's case, most of the time, the requester doesn't have the knowledge of the data type. Therefore, the NetDB client cannot pro- vide this information when the request is made. The NetDB server has to do data conversion for transferring data be- tween different machines. One of the ways is to use ASN.1. ASN.1 is Abstract Syntax Notation One (ASN.1). It defines the formats of the data exchanged by different machines. NetDB will use it as the data representation for data con- version between machines. The NetDB server packages the data back from database into ASN.1 format. Then NetDB can Network Database Protocol by Daisy Shen 7 do the data conversion according to ASN.1 format and its rules. Multiple Databases The NetDB server supports multiple databases on a host ma- chine so that the end user can specify which database to connect when he/she submits the request. The DB Utility A true implementation of an ANSI standard relational data- base system must have a set of SQL preprocess procedures for its application. The NetDB server is database's applica- tion. The preprocess procedure is unavoidable. The DB Utility performs these procedures. Each SQL statement has to go through the SQL preprocess. Although the implementa- tion varies among different machines, there is an ANSI standard to follow. Oracle, Ingres, Sybase, Informix...etc. all follow the ANSI standard. For instance: Oracle has a set of procedures named precompiler which contains functions such as open, connect, prepare, describe disconnect, close...etc. for processing either static or dynamic SQL statements. Other venders may call it preprocessor, but they all serve the same purpose. The DB Utility simplifies the database access process for applications and users. A general SQL preprocess procedures include CONNECT, PREPARE, DESCRIBE, OPEN, FETCH , CLOSE and DISCONNECT. DB Utility provides processing all these func- tions. DB Utility also provides I/O buffer management. One of the functions is CONNECT which allows application programs to connect to a database, and use it. It provides a 'Call' interface to the database connection services. Ap- plication programs can control the exact state of their con- nection to the database. It is restricted to a single task level. It is the DB Utility's responsibility to understand and manage the connection status of multiple tasks in single database system. Usually there are two ways to connect to a database system. One is explicit connection which uses the preprocess calls, such as CONNECT, OPEN, CLOSE, DISCONNECT. The other is im- plicit connection which doesn't use the above functions, but starts making SQL calls. In other words, if an SQL call oc- curs before the connection to the database has been estab- lished, the connection to the database is implicit; Network Database Protocol by Daisy Shen 8 otherwise, the connection is explicit. Most users will use the explicit connection services to maximize the flexibility and control. Internal Procedures of SQL Preprocess for a Select Statement ____________________________________________________________ CONNECT Establish a connection between the current address space and a database (if it is not already done), and establish the current task as a user of a da- tabase services. OPEN Establish the current task as a user of a database services(if it is not already done), and allocate database resources for SQL access(create a thread). SQL Calls SQL calls pass through the Language Interface by branching to the entry point. Usually it is a FETCH. CLOSE Deallocate database resources(terminates the thread), and remove the task as a user of database services if the connection was established by OPEN instead of CONNECT. DISCONNECT Remove the task as a user of a database services, and terminate the address space connection to a database if this is the last(or only remaining) task in the address in the address space estab- lished as a user of a database services. Database The database machine can be any ANSI standard relational da- tabase system, such as Oracle, Ingres, Sybase, Informix, IBM SQL/DS, IBM DB2, and the Dec's database. They process the valid SQL request delivered from the NetDB server, and re- turn the result back to the NetDB server. Security ________ There are two levels of security. First is the network se- curity. The Network verifies that the clients are author- ized to get through the network. Kerberos is a known security authentication system in Unix. Second is the data- base security. The database verifies that users are author- ized to access the database. The database system administrator authorizes the different attributes to the Network Database Protocol by Daisy Shen 9 different userids. For example: some users can read and write certain tables, but some users can only read them; some other users may have even more restrictions that they can only access certain fields of particular tables. That protects the database such as DB2 or SQL/DS. The NetDB re- lies on these two levels checking as its security system. The Main Control Block, NDBC ____________________________ The most important data structure in Network DataBase proto- col is the main control block which is called NDBC. The NetDB Client has to get a piece of storage for NDBC and its I/O buffer. The format of NDBC is shown below: The Main Control Block, NDBC, is shown below: Network Database Protocol by Daisy Shen 10 Dec(Hex) |-----------------------------------------------------------| 0( 0) | NDBREL (4 bytes) long int | NDBVER (4 bytes) long int | |-----------------------------|-----------------------------| 8( 8) | NDBCB (4 bytes) 'NDBC' | NDBSRC (4 bytes) long int | |-----------------------------|-----------------------------| 16(10) | NDBAPPL (4 bytes) long int | NDBSNAME (8 bytes) |-----------------------------------------------------------| 24(18) 'NETDBSRV' | NDBSFID (4 bytes) long int | |-----------------------------------------------------------| 32(20) | NDBCUID (4 bytes) long int | NDBSTMI (4 bytes) long int | |-----------------------------------------------------------| 40(28) | NDBCIPA (16 bytes) char 16 | |-----------------------------------------------------------| 48(30) | ex: 129.34.223.10 | |-----------------------------------------------------------| 56(38) | NDBCPSW (8 bytes) char 8 | |-----------------------------------------------------------| 64(40) | NDBDBNAM (8 bytes) char 8 | |-----------------------------------------------------------| 72(48) | NDBSTAT (4 bytes) long int | NDBUOW (2by)si| NDBRQDLN |-----------------------------------------------------------| 80(50) (4 bytes) long int | NDBRPDLN |-----------------------------------------------------------| 88(58) (4 bytes) long int | NDBRQD |-----------------------------------------------------------| 96(60) (4 bytes) pointer of the rq buffer | NDBRPD |-----------------------------------------------------------| 104(68) (4 bytes) pointer of the rp buffer | Request |-----------------------------------------------------------| 112(70) | Buffer ( variable length, v1) | | .... | |-----------------------------------------------------------| 112+v1 | Reply Buffer ( variable length, v2) | | ... | |-----------------------------------------------------------| NDBREL: (l int) Release number x: release x, where x is an integer NDBVER: (l int) Version number y: version y, where y is an integer NDBCB: (char 4) Self Identification. It is always 'NDBC'. NDBSRC: (l int) Server Return Code NDBAPPL: (l int) What kind of application, C, assembly, or others 1: C 2: Assembly 3: Others NDBSNAME: (char 8) The Server Name 'NETDBSRV' for NETwork DataBase Server NDBSFID: (l int) Service Function ID 1: begin, 2: end, 3: select NDBCUID: (l int) Client Uid on the database host's NDBSTMI: (l int) Server Transaction Manager's Index NDBCIPA: (char 16) Client IP address NDBCPSW: (char 8) Client's Password for the database host machine Only used in the first request of a UOW. Network Database Protocol by Daisy Shen 11 NDBDBNAM: (char 8) The name of th database that the end user wants to be connected. NDBSTAT: (l int) The status of the current thread 0: only one request in the thread 1: begin, 2: end, 3: in the middle NDBUOW: (s int) Number of Unit Of Work (Number of Thread) NDBRQDLN: (l int) The length of Request Data Buffer NDBRPDLN: (l int) The length of Reply Data Buffer NDBRQD: (char< >) Request Data Buffer, variable length NDBRPD: (char< >) Reply Data Buffer, variable length Implementation ______________ For more information about implementation of NetDB, please refer to Network Database Implementation Information Inter- net Draft. Operational Procedures ______________________ 1. Bring up the Portmap Manager Server 2. Bring up the NetDB Server. It can be replicated for multiple times if users need multiple NetDB servers. NetDB servers have to be brought up after the Portmap Manager Server has been brought up. 3. Once above server are all brought, end users can start submitting requests. COMPONENTS IN THE NETWORK DATABASE PROTOCOL ___________________________________________ Network Database Protocol by Daisy Shen 12 The Server Machine The Client Machine | | | DataBase Machine | | (Oracle, Ingres, | | Sybase...etc.) | | | | Mainframe / | | | | workstation | | DB Utility | | Application | | | | | | | | NetDB | | NetDB | | Client | | Server | | | | | | RPC | | | | Client | | RPC | | | | Server | | <> | TCP/IP Figure 1. Components in the Network DataBase Protocol RELATIONSHIP AMONG PORTMAP MANAGER SERVER AND NETDB SERVERS ___________________________________________________________ Network Database Protocol by Daisy Shen 13 1 PORTSRV 4 --------- 2 ------> |port | <------------- | ---- |manager| ---------- | | | |server | <- 3 | | | | --------- | with a v | Clients | | | | program Servers ----------- --- | 5 with a | | # ----------- | client1 | <----- program# | | | NDBSRV1 | ----------- <------\ | | 2 ----------- \ | --------------- \ ---------------|| \ 3 with a v| ----------- \ program# ----------- | client2 | \--------------->| NDBSRV2 | ----------- 6 connected ----------- . . . . . ----------- ----------- | NDBSRVn | | clientm | ----------- ----------- Steps: 1. Bring up the PORTSRV 2. NDBSRVx issues a request to PORTSRV to get a program number 3. PORTSRV updates its program status and returns a program number 4. A client issues a request to PORTSRV to get a program number of an available port 5. PORTSRV returns an available program number 6. When the client issues a request from now on, they are connected Network Database Protocol by Daisy Shen 14