Network Database Implementation Information 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 Implementation by Daisy Shen . . . . . . 1 Network Database Implementation by Daisy Shen . . . . . . 1 Network Database Implementation by Daisy Shen . . . . . . 1 Network Database Implementation by Daisy Shen . . . . . . 1 Status of this Document . . . . . . . . . . . . . . . . . 1 Abstract . . . . . . . . . . . . . . . . . . . . . . . . 1 The Main Control Block, NDBC . . . . . . . . . . . . . . 1 The Main Control Block, NDBC, is shown below: . . . . . 1 The Client Machine . . . . . . . . . . . . . . . . . . . 3 End-User Applications . . . . . . . . . . . . . . . . . 3 Preparation for Generating the Client . . . . . . . . . 4 Use RPCGEN . . . . . . . . . . . . . . . . . . . . . 4 Write Your Own . . . . . . . . . . . . . . . . . . . 6 NetDB Client . . . . . . . . . . . . . . . . . . . . . 6 The RPC Client . . . . . . . . . . . . . . . . . . . . 7 The Server Machine . . . . . . . . . . . . . . . . . . . 7 The RPC Server . . . . . . . . . . . . . . . . . . . . 7 The NetDB Server . . . . . . . . . . . . . . . . . . . 8 The DB Utility . . . . . . . . . . . . . . . . . . . . 9 Database . . . . . . . . . . . . . . . . . . . . . . 10 Multiple Servers and Multiple Clients . . . . . . . . 10 An Example of Running Multiple NDB Servers . . . . 11 An Example of Issuing SQL Commands From a Remote Machine 13 A Prototype of Network DataBase Protocol . . . . . . . 13 Restrictions . . . . . . . . . . . . . . . . . . . . 13 Future . . . . . . . . . . . . . . . . . . . . . . . . 14 Table of Contents ii NETWORK DATABASE IMPLEMENTATION BY DAISY SHEN _____________________________________________ STATUS OF THIS DOCUMENT _______________________ 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 document provides information for the Internet commu- nity. It does not specify any Internet standard. Distrib- ution of this document is unlimited. Please send comments to ietf-ndb@ucdavis.edu. ABSTRACT ________ This document provides the implementation information of Network Database Protocol. The protocol is defined in the document of Network Database Protocol for use with rela- tional database systems in a TCP/IP based Internet environ- ment. This document will show readers some data structures and examples, and provide as much detail information as pos- sible. This document will go over each component one by one. THE MAIN CONTROL BLOCK, NDBC ____________________________ The format of NDBC is defined in Network Database Protocol Internet Draft. It is used by both NDB server and client. The Main Control Block, NDBC, is shown below: _____________________________________________ Network Database Implementation by Daisy Shen 1 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 Implementation by Daisy Shen 2 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 THE CLIENT MACHINE __________________ End-User Applications _____________________ According to Network Database Protocol, the end user can be on any machine and submit a SQL request to access a database which is on the network. This section describes how an end user uses this protocol. There are two ways to use Network Database System. One is used interactively, and the other is in an application program. Interactive SQL Commands After a user logs in to a system, the user can simply type a command ndbclnt on the command line. ndbclnt is the NetDB's client mod- ule name. The syntax of the command ndbclnt is ndbclnt machine_id "a_sql_statement" where ____________ ___________________ machine_id is the host machine on which the data- ___________ base resides, and "a_sql_statement" is any stand- _________________ ard sql statement such as select * from table1 select * from table1 Imbed SQL statements in an Application Program Any applica- tion program can imbed a sql statement to access a remote database on the network. The following is an example on how to code it in a C program. The Network Database Protocol can be applied to any common languages such as C, PASCAL, PL1, COBOL, FORTRAN...etc. Network Database Implementation by Daisy Shen 3 main(argc, argv) int argc; char *argv[]; { /* Any Other C statements */ printf("It is about to issue a sql statement\n"); ndbclnt("yktvmv", "select * from table1"); printf("It is back from remote database\n"); /* Any Other C statements */ exit(0); } Preparation for Generating the Client _____________________________________ There are two ways to generate client code. One uses RPCGEN and the other is created by programmers. Use RPCGEN Provide a .x file Readers can follow NDBC format to create a .x file such as ndb.x. It is shown as below: Network Database Implementation by Daisy Shen 4 typedef string chr16<17>; struct NDBC { long int ndbrel; /* release number */ long int ndbver; /* release number and version number */ chr4 ndbcb; /* self identification it is always 'NDBC'*/ long int ndbsrc; /* server return code */ long int ndbappl; /* the type of application */ chr8 ndbsname; /* the server name */ long int ndbsfid; /* service function id */ long int ndbcuid; /* client uid */ long int ndbstmi; /* server transaction manager's index */ chr16 ndbcipa; /* client ip address */ chr8 ndbcvmid; /* client's vm id */ chr8 ndbcpsw; /* client's VM password */ chr8 ndbdbnam; /* The database name to be connected */ long int ndbstat; /* The status of the current thread */ short int ndbuow; /* unit of work number(thread number) */ long int ndbrqdln; /* the length of the request data buffer */ long int ndbrpdln; /* the length of the reply data buffer */ string ndbrqd< >; /* the pointer of the request data buffer */ string ndbrpd< >; /* the pointer of the reply data buffer */ } ; program NETDB_PROG{ version NETDB_VERS { NDBC NETDB_MSG(NDBC) = 1; /* procedure number = 1 */ } = 1; /* version number = 1 */ } = ndbpnum; /* program number = ndbpnum */ struct SSCB { short who; /* 1: server or 2: client */ chr8 smid; /* The server machine id */ chr8 suid; /* The server vm user id */ u_long prognum; /* The program number */ u_long portnum; /* The port number */ short status; /* The status of db server machine */ } ; program PORTS_PROG{ version PORTS_VERS { SSCB PORTS_MSG(SSCB) = 1; /* procedure number = 1 */ } = 1; /* version number = 1 */ } = 0x20000050; /* program number = 0x20000050 */ Issue RPCGEN The syntax is rpcgen ndb.x Network Database Implementation by Daisy Shen 5 The implementer can use RPCGEN to generate files to perform this function. RPCGEN is an RPC preprocessor. An RPC ap- plication programmer simply follows RPC's rules and provides a .x file, and the RPC preprocessor RPCGEN will generate three files. They are ndb_clnt.c, ndb_xdr.c and ndb_srv.c. The former two will be used by the NetDB client and the later two will be used by the NetDB server. Write Your Own A system programmer with a good RPC knowledge can implement it efficiently. Follow the rules of ndb_clnt.c, ndb_xdr.c and ndb_srv.c to write your own NetDB client. NetDB Client ____________ The NetDB Client gets control from the End-User Application that is the c function call ndbclnt , parses the input argu- ments which are described as in the End-User Applications' section. They are machine_id and a_sql_statement . NetDB __________ ________________ determines what kind of calls to issue to the server, and packages the request into the main control block, and sets up I/O buffers. The NetDB Client calls client_create to set up a thread for the communication. It then delivers the call to the RPC Client, and waits for results. The call goes through the network to the RPC Server. The NetDB Client is responsible for collecting the client's userid on the database machine and its password. Both pieces of information will be passed by the main control block, NDBC, which is defined in Network Database Protocol Internet Draft. The NetDB Client has to get a piece of storage for NDBC and its I/O buffer. Following are functions used in NetDB Client. They are based on Sun Microsystems's RPC. Readers can refer to Net- work Programming Guide from Sun Microsystems for details. clnt_create This is an RPC call to create the client handle. This call is required. clnt_pcreateerror This call prints error messages when the clnt_create call has an error. It is required only when NetDB wants to print the error messages. clnt_control Network Database Implementation by Daisy Shen 6 This call is not required. It is used only when the NetDB client wants to set a timeout value other than de- fault timeout. By default RPC sets 25 seconds for time- out clnt_destroy The call always deallocates the space associated with the client handle which was created earlier. Before the client goes away, this call should be issued. It is re- quired. clnt_perror This call is not required. It is used when the NetDB client wants to print error messages. ports_msg_1 This is an external function which is the entry of the Portmap Manager server. The arguments passed will be defined in .x file. netdb_msg_1 This is an external function which is the entry of the NetDB server. It is defined in ndb_clnt.c. The RPC Client ______________ The RPC Client means ndb_clnt.c and ndb_xdr.c. It contains two parts. One issues a clnt_call which is the lowest layer of RPC. It is the actual client call. The other is handl- ing data conversion between two machines. The later one contains all the functions for different data types that both the client and the server will use. For instance: xdr_chr4, xdr_chr8, xdr_int, xdr_long... etc. The RPC Client is the last stop on the client machine. Once NDBC leaves here, it travels through the network and gets to the server machine. THE SERVER MACHINE __________________ The RPC Server ______________ The first stop in the server machine is the RPC server. The major functions in the RPC server are create a tcp or udp service, register to the portmap, send and reply. The RPC Server also provides the same data conversion as the client Network Database Implementation by Daisy Shen 7 machine does. The implementer can program it more effi- ciently; however, running RPCGEN will get the same result. The third file that RPCGEN generates is _svc.c file which contains all the functions described above. The RPC Server uses the same file _xdr.c to do data conversion. The following is a list of RPC functions calls which will be called in the RPC server. pmap_unset This function call erases the entry for RUSERSPROG from the portmapper's tables. svcudp_create This function call gets a UDP handle. svctcp_create This function call gets a TCP handle. svc_register This function registers its port number with the local portmapper service. svc_run This function call processes RPC requests. svc_sendreply This function call serializes the results and returns them to the RPC caller of the user service routine. svcerr_noproc This function call handles errors when an error occurs. :svc_getargs This function call takes an SVCXPRT handle which is the XDR routine, and points to where the input is to be placed as arguments. svcerr_systemerr When a service call was made and an error occurs, this function call should be called. svc_freeargs This function call deallocates the memory which was al- located when svc_getargs was called. The NetDB Server ________________ The entry for NetDB server is netdb_msg_1. Once the call leaves the RPC server, the NetDB server gets control. The first thing is to verify all items in NDBC are valid. The Network Database Implementation by Daisy Shen 8 transaction manager will check the status, manages the UOW and update the information related to each request. Finally before it calls the DB Utility, it sets up the I/O buffer. When the server machine does not have Kerberos authentication system, before verifying the userid and pass- word, the function of name mapping has to be performed. Kerberos is used to authenticate that the client is really who he claims. The name mapping is to map the client's machine_is and uid to its database host machine_id and user_id. A name mapping file or a database has to be cre- ated. It contains every userid on the database machine and its corresponding client machine's ip address and uid. The format is shown below: ------------------------------------------------------- | client machine | server database machine | |-------------------------|---------------------------| | ip address | uid | userid | machine id | |---------------|---------|----------|----------------| |129.34.223.10 | 2 | DAISY | COM1 | |---------------|---------|----------|----------------| |129.34.223.10 | 4 | PIANO | COM2 | |---------------|---------|----------|----------------| |129.34.223.11 | 11 | DCSHEN | COM1 | |---------------|---------|----------|----------------| |129.34.2.17 | 53 | DAISY | COM2 | |---------------|---------|----------|----------------| |129.34.2.17 | 121 | RER | COM3 | |---------------|---------|----------|----------------| . . . . . |---------------|---------|----------|-----------| |129.34.222.43 | 223 | RYNIKER | COM3 | -------------------------------------------------- The DB Utility ______________ The NetDB has the first level security checking by using password and userid. If Kerberos is available, Kerberos can do the authentication. If Kerberos is not available, any kind of authentication can perform this level of security, such as password checking. This level of security is re- quired by NDB. The DB Utility performs the set of SQL pre- process, and performs the second level of security checking. It depends on the database machine to implement the security checking on this level. Some relational databases can run multiple threads at a time, some cannot, although all imple- Network Database Implementation by Daisy Shen 9 mentations are standard relational databases. For those systems who can run multiple threads, DB Utility simply sub- mits the request to the database to verify the user's attri- bution. For those systems who cannot run multiple threads, the DB utility has to implement a set of procedures to per- form the job. There are several scenarios, but one example is shown here. The user is connected to the database with a password The procedure for these cases is: CONNECT userid IDENTIFIED BY password _______ _________ Issue the request was passed from the client COMMIT WORK RELEASE Issuing CONNECT to make the server act as if it were the end user. Issuing COMMIT WORK RELEASE will let the server back to itself. Once the data is returned from the database system, the DB Utility has to parse the machine's internal representation to ASN.1's representation field by field, and packages them to a format that the client understands. The control then traces all the way back from where it came from. Database ________ The database machine is where databases reside. It can be any ANSI standard database system. For instance, DB2 is a database residing on MVS, and SQL/DS is a database residing on VM. MVS and VM are database machines. Database systems do not have to change anything to support the Network DataBase protocol. The database system performs its normal functions that is processing the real SQL request delivered from the NetDB server, and return the result back to the NetDB server. Multiple Servers and Multiple Clients _____________________________________ NDB protocol serves multiple clients. When a database is designed for multiple threads, the implementation of NDB doesn't have to do anything more. NDB can let the database manage multiple users; however, not all databases offer the multiple-thread function. The implementation of NDB needs to provide multiple clients. One approach that this draft Network Database Implementation by Daisy Shen 10 suggests is to set up multiple NDB servers to connect to one database and apply the above scenarios to it. Each NDB server will have a different port number. NDB server needs a manager to manage the status of each port and distribute them to the client when the client asks for an available NDB server. An Example of Running Multiple NDB Servers To run multiple NDB servers, there are at least three VM ma- chines involved. They are PORTSRV, NDBSRV1, and NDBSRV2. If you want to install more servers, you can name them NDBSRV3, etc. PORTSRV: is a service machine that runs the port manager server. It should be always up and running. Be sure to bring it up before doing anything with NDB. The port manager server is activated in this service machine with com- mand PORTSRVS. This service machine may be AUTOLOGged or logged on at a terminal. The port manager server machine only accepts requests from either a NDB server machine or an NDB client machine. NDBSRV1 and NDBSRV2: are service machines for NDB servers. However, they are PORTSRVS's client. Before the NDB server is up, the server machine issues a request to PORTSRVS to get an available port number, and gives PORTSRVS enough informa- tion to update the status of the port. Once the port number is assigned, the NDB server will be brought up using the as- signed port number. The command for bringing up the NDB server is PORTCLNT machine_id userid ndbsrv where machine_id is the server machine id such as yktvmv and userid is the server user id such as NDBSRV1 or NDBSRV2. Network Database Implementation by Daisy Shen 11 Fig: 1 PORTSRV 4 ----------- 2 ------> | port | <---------------- | ---- | manager | ------------- | | | | server | <- 3 | | | | ----------- | with a v | Clients | | | | program# Servers ----------- ----- | 4 with a | | ----------- | client1 | <------- program# | | | NDBSRV1 | ----------- <--------\ | | 2 ----------- \ | ------------------ \ ------------------|| \ 3 with a v| ----------- \ program# ----------- | client2 | \-------------------> | NDBSRV2 | ----------- 6 connected ----------- . . . . . . . . ----------- . | NDBSRVn | . ----------- . . ----------- | clientm | ----------- Steps: Steps: Steps: Steps: 1. Bring up the PORTSRV 2. NDBSRVx issues a request to PORTSRV to get a port number 3. PORTSRV updates its port status and returns a port num- ber 4. A client issues a request to PORTSRV to get a port num- ber of an available port 5. PORTSRV returns an available port number Network Database Implementation by Daisy Shen 12 6. When the client issues a request from now on, they are connected AN EXAMPLE OF ISSUING SQL COMMANDS FROM A REMOTE MACHINE ________________________________________________________ A user issues a SQL commands from a remote machine. The syntax is defined in the section of End-User Applications. Once the end user is in the command line, he/she can do the example it was shown as the definition showed, or as follow- ing: ndbc machineid begin select * from table1 select * uid.table where col = something . . . end A PROTOTYPE OF NETWORK DATABASE PROTOCOL ________________________________________ There is a prototype for Network DataBase Protocol that dem- onstrates the protocol is feasible. IBM RISC/6000 or Sun Microsystems <----> IBM VM The client machine is either IBM RISC/6000 or Sun Microsys- tems workstation, and the server machine is IBM VM with SQL/DS. Restrictions ____________ There are some restrictions on this prototype Query statements only Limited SQL data types SQL has many data types. However, the most frequently used are integers and charac- ters. For the first prototype, NetDB performs data type INT, SMALLINT, CHAR, VARCHR, DECIMAL, and FLOAT. They can be with nulls or without nulls. Network Database Implementation by Daisy Shen 13 FUTURE ______ The first two prototypes will be improved and the third pro- totype is on the way. I encourage all database vendors can follow the Network DataBase protocol to implement a system so that the interoperability of network database can be re- alized. Network Database Implementation by Daisy Shen 14