1 In the case of the memory process, in the customer-server architecture, the application running on the client is accessed by SQL, each SQL statement is separately, one sentence is sent from the client to the server. Request, then the database server returns the result one to the application. However, there are some applications, there are many server-side database processing activities, and less interaction with users. In this case, the processing activity of the database is done on the database server in the form of a storage process. It can greatly reduce the transmission traffic online and improve application performance. The concept of the storage process is shown below. Using the application of the storage process has the following advantages: A: Reduce the online transmission traffic; B: Improve the performance of the server handle the dense application; C: to access the characteristics of the database server; D: easy to maintain; 2 Program structure of the stored procedure A complete application of the storage process consists of two parts: First, the storage process itself, it is stored on the database server side and the database server is running; the other is the client application called by the stored procedure, it is on the client Run. The client application runs in two different process spaces, respectively, and there are different functional divisions. The main features of the client application are: a: Off data structure and main variable definition, assign and initialize storage space; B: Connect with the database; C: Call the stored procedure D: Committed by the SQL CALL statement D: Commit And rollback (Note: Unless the distributed work unit, the server-side stored procedure can also complete the transaction submission and rollback); E: Execute the Connect RESET statement; the main function of the server-side storage process is: A: Receive The client application is transmitted by the SQLCA and SQLDA and other database structures; B: Run on the database server as the same transaction as the client application; C: Return to the client application to SQLCA and other output data; 3 client application customers Several steps must be performed before using the Call statement to call the stored procedure. First, you must connect the database, declaration, assignment, and initialize the SQLDA structure or host variable. Tip: Do not assign these variable structures on the database server. The database management system will automatically assign the desired storage based on the structure in the client application. The client application must ensure that a database connection has been established before calling the stored procedure, otherwise an error will occur. 3.1 Call statement The client application can send a stored procedure call request to the server via the CALL statement. Call's grammatical format as follows: Call {(Main Variable 1, ..., Main Variable N) | USING DESCRIPTION Descriptor Name} The function of the Call statement is to transfer the call parameters from the client to the server side by a SQLDA data structure. After the stored procedure is executed, the output result is returned to the client application by the same SQLDA. 1. Provisions about the name of the called stored procedure 1) In the client application, the name of the stored process being called can only be given directly, or it can be given by a primary variable. 2) If the name of the stored procedure called is given directly, it must be a usual identifier (Identifier) and the length cannot exceed 254 bytes. Since the usual identifier cannot contain spaces or other special characters, if a space or other special character must be included in the name of the called stored procedure, it can only be used to give the name.
3) If the name of the called stored procedure is given by the primary variable, the main variable used must be a string type variable with a length attribute, and its length cannot exceed 254 bytes. 4) The name of the stored procedure to be called can be used in the following formats: Proname: Indicates a function library to be named Proname and execute a function called Proname; proname! FuncName: Indicates that you want to put it named Proname The stored procedure function library and executes a function called FuncName; Note: Follow the default method, on the Unix-based platform, the database management system will find isolated (FENCED) stored procedure functions in the installation / sqllib / function directory. Library. On the Intel platform, the search location of the stored procedure function library is illustrated by the operating system environment variable libpath. For unfounded stored procedure functions, its search location is Insthome / SQLLIB / Unfenced or SQLLIB / DLL / UNFENCED (Intel-based system), respectively. Absolute path: If / home / user1 / procname! FuncName, means that the stored procedure function library named Proname is loaded in the / home / user1 directory and executes a function called FUNCNAME; 2. Parameter passing through the host variable transfer process The following is an example of program example of calling parameters passing through a host variable transmission; car host_var1 [15]; float host_var2; short ind_var2; long host_var3; short ind_var3; char procname [254] = "myproc"; EXEC SQL End DecalRe section; host_var1, "new data"); host_var2 = 17.6; ind_var2 = 0; Ind_var2 = 0; exec SQL Call: procname (: host_var1,: host_var2: ind_var2,: host_var3: ind_var3); as above Demographic segment, when the stored procedure is called, the calling process can be transferred to the stored procedure through the host variable. The calling parameters of the stored procedure can be divided into only input only, only output only, and both input and output. If the call parameter is not explicitly illustrated as an input only or only output only output (OUTPUT ONLY), the Call statement processes its default to the calling parameters of both input and output. The input and output type of the calling parameter is determined by the corresponding indicator variable.
For only input (Input Only), the value of the corresponding indicator variable should be 0 (eg, Host_VAR2 in the above block); the value of the corresponding indicator variable for only output "OUTPUT ONLY) Should be -1 (for example, host_var3 in the above block); 3. The following is a program example of the process calling parameters through the SQLDA delivery process: struct sqlda * inout_sqlda = (Struct SQLDA *) Malloc (Sqldasize 3); long host_var3; short ind_var3 = -1; short inde_var2 = 0; inout_sqlda-> sqln = 3; inout_sqlda-> sqld = 3; inout_sqlda-> sqlvar [0] .sqltype = SQL_TYPE_CSTR; inout_sqlda-> sqlvar [0 ] .sqllen = 16; inout_sqlda-> sqlvar [0] .sqldata = (char *) malloc (inout_sqlda-> sqlvar [0] .sqllen); struct (inout_sqlda-> sqlvar [0] .sqlData, "new data) ; inout_sqlda-> sqlvar [1] .sqltype = SQL_TYPE_NFLTYPE = SQL_TYPE_NFLOAT; inout_sqlda-> sqlvar [1] .sqllen = sizeof (float); inout_sqlda-> sqlvar [1] .sqldata = (char *) malloc (inout_sqlda-> sqlvar [1 ] .sqllen); * (float *) inout_sqlda-> sqlvar [1] .sqldata = 17.6; inout_sqlda-> sqlvar [1] .sqlind = & ind_var2; inout_sqlda-> sqlvar [2] .sqltype = SQL_TYPE_NINTEGER; inout_sqlda-> sqlvar [2]. Sqllen = SizeOf (long); inout_sqlda-> sqlvar [2] .sqlData = (char *) & host_var3; inout_sqlda-> sqlvar [1] .sqlind = & ind_var3; Exec SQL Call myproc using descriptor: * inout_sqlda; as shown above, when the stored procedure is called, the calling process can be transferred to the storage process via SQLDA. 3.2 Creating a stored procedure The following is an example of creating a stored procedure with the Create Procedure statement: Create Procedure MyProc (INOT HOST1 Char (15), In Host2 Double, Out Host3 Integer External Name '/ Home / User1 / Myfn! Fn1'Language CParameter Style DB2DARI1) The role of the Create Procedure statement is to register a new stored procedure to the database server; 2) MyProc is the specified name of the stored procedure. The client application can call the corresponding stored procedure in the Call statement; 3) INOUT HOST1 CHAR (15) indicates a call parameter required during the stored procedure. The parameter input / output type inout indicates that the parameter can provide both input information to the stored procedure, or the information returned from the stored procedure.
Parameter Input Output Type IN indicates that the corresponding parameter is only used for input only; the parameter input / output type OUT indicates that the corresponding parameter is only used for output only, in the same mode, does not allow the definition name, parameter The number and data types are exactly the same stored procedure; 5) External name indicates the name of the program code segment written by the user to implement the defined stored procedure; 6) Language C is not available in the CREATE Procedure statement Less, its role is to specify the language interface agreement of the stored procedure. Another option of the clause is Language Java; 7) The role of Parameter Style is to explain to store procedure delivery parameters and reception from stored procedures. DB2DARI indicates that the parameter delivery appointment used by the stored procedure will be compatible with the function calls and connects to the C language; Parameter Style DB2DARI must be used with Language C. DB2General indicates that the parameter delivery of the stored procedure will be compatible with the Java language function call and connection; Parameter Style DB2General must be used with Language Java; 4 The server-side stored procedure 4.1 Implement the function of the stored procedure defined in UDB, The implementation code of the stored procedure can generally apply some programming language (such as C, C or Java, etc.) to write. The following is taken as an example in C language, indicating matters that should be noted when the implementation code of the stored procedure is written. SQL_API_RC SQL_API_FNmyproc (void * reserved1, void * reserved2, struct sqlda * inout_sqlda, struct sqlca * ca) {/ * no connecttion related statements * // * runs in background * // * no command to terminate current process *. // * (no exit, _exit, or at exit) * // * if duow no commit or rollback * / return (ret_value);} 1) SQL_API_RC and SQL_API_FN started with 2 predefined macros, the purpose is to Portability; 2) The statement related to the database connection cannot occur in the function body of the stored procedure, that is, the statement such as Connect, Connect Reset, Create Database, DROP DATABASE, ALTER DATABASE, BACKUP, RESTORE, ROLLFORWORD; 3) Since the stored procedure is only running in the background, the write screen (eg, Printf) is not allowed, but the write file (FPRINTF); 4) The function of the stored procedure is just a program routine called by the database management system, so When the execution is completed, the control should always be invoked to call its function, and the current process should not be terminated, that is, exit (), _ exit () such a function call; 5) If the stored procedure The database connection type of the client application is duow, and the statement of the stop transaction cannot be issued during the storage process, namely the dynamic or static commit, Rollback is not allowed.
4.2 stored procedure parameter transfer SQL_API_RC SQL_API_FNmyproc (void * reserved1, void * reserved2, struct sqlda * inout_sqlda, struct sqlca * ca) {struct sqlca sqlca; / * for local use * // * use input data in SQLDA * // * Do Not Change Sqld, SqlType, or Sqllen * // * Do Not Change Pointer for SqlData or Sqlind * // * Return Data In SqlData (and SQLIND) * / Memcpy (CA, & SQLCA, SIZEOF (Struct Sqlca)); ... 1 The stored procedure will receive the input parameters from the client application through the input variables in SQLDA, and then return the output to the client application through the output variables in SQLDA. Since the original value of SQLDA domain is set by the client application before the process call, the function of the stored procedure should not change the value of SQLD, SQLTYPE, SQLLEN and other domains in SQLDA. In addition, although the function of the stored procedure can change the value of the variable pointed to the pointer in SQLDATA and SQLIND, it should not change the pointers contained in SqlData and SQLIND. Note: Variables in SQLDA can be both input variables and output variables. 2) Before the function body of the stored procedure is returned, the information in the local SQLCA should be explicitly copied to the SQLCA parameter of the stored procedure. 4.3 stored procedure return value SQL_API_RC SQL_API_FNmyproc (void * reserved1, void * reserved2, struct sqlda * inout_sqlda, struct sqlca * ca) {/ * processing * // * in this application, the second SQLVAR fieldis used to determine if the client intends tocall the server procedure again, A value of 0means no further calls * / if ((* float *) inout_sqlda-> sqlvar [1] .sqlda = 0!) return (SQLZ_HOLD_PROC) elsereturn (SQL_DISCONNECT_PROC);.} 1) should be particularly Note that the return value of the stored procedure will not return to the client application at all. The role of the return value here is to enable the database management system to determine whether the stored procedure is released from memory when the stored procedure is terminated. 2) The stored procedure can return the following 2 values to the database management system: -sql_disconnect_proc: The meaning is to tell the database management system When all information is passed to the client, it can release (or uninstall) the stored procedure and its data storage; SQL_HOLD_PROC: The meaning is that the database management system remains in memory, which will guarantee that the library function called when the client issues a DARI call to the stored procedure is already in memory, thus Improve system performance. 3) If the client only calls the stored procedure once, you should return SQL_Disconnect_proc; 4) If the client needs to call multiple times, then you should return SQL_HOLD_PROC, so that the stored procedure will not be unloaded; 5) If the stored procedure SQL_HOLD_PROC acts as a return value, then it should be used as a return value as SQL_DISCONNECT_PROC, so that the stored procedure is released from memory after the last call.
