I migrate a old database from ASA 7.0 Win to a new platform 12 Linux x32. The database uses an external library function implements DES encryption. Having dealt with the new interface of external calls, i made the .so library on Linux. But tests have shown that call external functions are now much more slowly than it did under the old interface. In my case, it is absolutely not acceptable. In operating mode, I need to call the DES encryption for 4-6 fields on a select of 100-300 thousand records. Make the function of encryption on the client side is also impossible, since "open" data should not leave the secure storage ( The question is whether there is at least some features or techniques to speed up the external call? Maybe I have something did not finish in the documentation or in implementation of the library. Maybe someone else has encountered this problem? A external procedure declaration in the database: ALTER PROCEDURE "DBA"."dll_Encrypt"( inout D char(16),in K char(16) ) no result set external name 'xp_des_encrypt@libsacrypt.so' language C_ESQL32 The function code is used in Select: ALTER FUNCTION "DBA"."Encrypt"(in D char(16),in K char(16)) returns char(16) begin call DBA.dll_Encrypt(D,K); return(D) end "C" function is based on the example of samples SA12 _VOID_ENTRY xp_des_encrypt( an_extfn_api *api, void *arg_handle ) { char *Data, *Key; an_extfn_value arg; an_extfn_value retval; if( !api->get_value( arg_handle, 1, &arg ) || (arg.data == NULL) ) { return; } else { Data = (char *)arg.data; } if( !api->get_value( arg_handle, 2, &arg ) || (arg.data == NULL) ) { return; } else { Key = (char *)arg.data; } // des_encrypt(Data, Key); /*The call is blocked for the tests*/ retval.type = DT_FIXCHAR; retval.data = Data; //Same as input for test retval.piece_len = retval.len.total_len = DES_LEN; api->set_value( arg_handle, 0, &retval, 0 ); return; }; asked 07 Oct '11, 08:43 morry Volker Barth |
Starting with v11, you can use an external environment to run your external procedures, i.e. to let them run with their own process. - However, for C/C++ based calls, you do not need to do so - you can still run external procedures in the database server's process. When omitting an external environment specifier in the CREATE PROCEDURE declaration, you will force the external library to be loaded and executed in the database server's process, which - by design - will be faster than starting an external process. Of course, this leaves the risk of damaging the database server's process memory when the external procedure is buggy. So the following should do to let your procedure run as with v7: ALTER PROCEDURE "DBA"."dll_Encrypt"( inout D char(16),in K char(16) ) no result set external name 'xp_des_encrypt@libsacrypt.so'; Some more info on this topic can be found in this FAQ. answered 07 Oct '11, 09:39 Volker Barth I tried this method. However, the function returns the original data. Then I read the documentation: Libraries written to the older interface, used in versions before version 7.0.x, are still supported, but in any new development, the new interface is recommended. Note that the new interface must be used for all Unix platforms and for all 64-bit platforms, including 64-bit Windows. After that I kept trying to work as before. But if you believe that the old call to work, then I will look for my bugs.
(07 Oct '11, 09:54)
morry
Replies hidden
1
There's a (very understandable) misunderstanding: The "older" interface refers to an API that was "old" even in v7. - That's a different topic than the question whether to use an external environment or not. You are using the "newer" interface when your DLL contains the extfn_use_new_api method, and that function returns not 0. You may look here for further clarification - though the words "external" are somewhat indifferent used there... However, I can confirm that you can use external C calls with the "newer" API as "in-process external calls".
(07 Oct '11, 10:30)
Volker Barth
Comment Text Removed
Thanks for your help. I totally take care of the 'native-call'. It is important to accurately specify the number of out parameters in the api-> set_value:) On "select" of a 250K records with encryption of one field and decrypt of one field difference in query execution time 2-4 sec (30sec all time), in favor of the ASA 7.0, but this is not critical.
(10 Oct '11, 10:30)
morry
1
Glad you got it working! If your code is calling the external function n thousand times per SELECT, I surely expect the external environment to perform very bad - the cited small penalty will obviously sum up to a noticeable amount:
(10 Oct '11, 10:44)
Volker Barth
|
Could you get rid of the encrypt call altogether by using the builtin ENCRYPT()/DECRYPT() functions introduced with v9?
If so, I guess they are much faster than an external call can be...
And the builtin AES cipher is definetely superior to DES:)
Unfortunately in this case, DES is required. External client should receive the data from this database is encrypted with DES.
BTW, if you use your external procedure as function, wouldn't it be easier to define it as such?
This design, as described by me, is historical:). You specify the optimum method of course and will be implemented, but for compatibility must be maintained and an old challenge.