topic Re: Runtime error from OCI on OpenVMS in Operating System - OpenVMS

Runtime error from OCI on OpenVMS

Glenn Wolf — Fri, 27 Feb 2009 00:01:45 GMT

Hello.

Environment: OpenVMS V8.3-1H1, Itanium (IA-64), Oracle Release 10.2.0.4.0

We have ported some C++ code from Oracle 8.17.3 to 10.2.0.4.0, and moved from the OpenVMS Alpha architecture to the Itanium architecture at the same time. Our code now compiles and links and runs, but while attempting to load data into the database, we get the following error traceback (no ORA- error is produced):

%SYSTEM-F-FLTINV_F, floating invalid fault, PC=000000000F0D7551, PS=0000001B
----End of Exception message
0 FFFFFFFF804017C2 FFFFFFFF804017C2
libclntsh lnxren lnxren 87529 0000000000000B11 000000000F0D7551
libclntsh ttcx2y ttcf2n 428454 0000000000007292 000000000DD35592
libclntsh ttcacs ttcacs 414366 0000000000001AB2 000000000DD276B2
libclntsh ttcdrv ttcdrv 1450241 00000000000051F2 000000000DD71692
libclntsh nioqwa nioqwa 159960 00000000000000B2 000000000D431EE2
libclntsh upiprv upirtrc 418201 00000000000022D2 000000000D066B72
libclntsh kpudfo kpuexes 403257 0000000000003F22 000000000CB07F22
libclntsh upiexf upiex0 402095 0000000000001C62 000000000D081742
libclntsh upiexf upiexn 401773 0000000000000092 000000000D07FB72
libclntsh oci ociexn 280403 0000000000004FC2 000000000CFCE212
TORRENT DATABASE exec #263 00000000000022F2 0000000000FA91B2
TORRENT TABLE flushCursor #1332 0000000000028332 0000000000FEF9F2
TORRENT TABLE flushInsertCursor #1348 0000000000023BF2 0000000000FEB2B2
TORRENT FILE load #819 000000000000A532 0000000000FFA3F2
TORRENT SCHEMA primeTables #395 0000000000007DC2 0000000000F9ABC2
TORRENT MAIN main #103 0000000000001112 0000000000F90412

Any help you can provide on resolving this error would be greatly appreciated. So far, the web has not provided any insight.

Thanks;

Glenn

Re: Runtime error from OCI on OpenVMS

Hein van den Heuvel — Fri, 27 Feb 2009 01:13:12 GMT

Well, the main error is:

"%SYSTEM-F-FLTINV_F, floating invalid fault"

I would try to figure out the SQL statement begin executed, and inspect that for BIND VARIABLES with floating type.
Where does that data come from? Is it valid floating point data in itanium (IEEE) context?.
The program is not juggling with floating points overlayed/redefined as appropriately sized, but in-appropriatedly typed integers is it now?

Since you can get at the code, I would check those potential floating point variables and try a simple print or even an 'add 1'. You may find the OCI is being lied to and is just an innocent messenger being told to operate on invalid data.

Maybe you can use the debugger and SET BREAK/EXCEPTION to figure out what data element cause the FLTINV ?

fwiw,

Hein.

Re: Runtime error from OCI on OpenVMS

Glenn Wolf — Wed, 04 Mar 2009 01:31:47 GMT

I have finally gotten to the debug point where I can get debug to show me the values that I'm sending on to Oracle. The debugger has no issues doing an ex/float of the BIND variables.

Would you consider that reasonable proof that the bytes I'm passing around are valid IEEE floating point format?

Thanks;

Glenn

Re: Runtime error from OCI on OpenVMS

Hein van den Heuvel — Wed, 04 Mar 2009 03:43:15 GMT

>> The debugger has no issues doing an ex/float of the BIND variables.
>> Would you consider that reasonable proof that the bytes I'm passing around are valid IEEE floating point format?

Actually, no. On the contrary.

You FORCED the debugger to 'pretend' the variable was an old style float, and it seemed to macth.

Now try examine /s_float or /x_float

More to the point, try something like:

DBG> show symb /type test
data TMP\main\test
atomic type, IEEE single precision floating point, size: 4 bytes

Did SET BREAK/EXCEP lead to anything useful?

Hein.

Re: Runtime error from OCI on OpenVMS

Hein van den Heuvel — Wed, 04 Mar 2009 04:05:30 GMT

How did you compile the program, notably did yo use a /FLOAT option?

Check out Metalink article 463949.1

It refers to:
http://download.oracle.com/docs/cd/B19306_01/server.102/b25416/ch6.htm#sthref391
:
6.3.1 Compiler Options Used to Compile Oracle Database 10g
Oracle Database 10g is compiled with as few deviations from the default C compiler options as possible
and with minimal use of pragma statements.

For the DEC C compiler on OpenVMS, the compilation options are as follows:

/DECC/NOSTANDARD/DEBUG=TRACE/PREFIX=ALL/GRAN=LONG/NAMES=AS_IS/
FLOAT=IEEE/MODE=DENORM/EXTERN=STRICT_REFDEF/NOANSI_ALIAS

If you compile the code with /DEBUG=TRACE, then line numbers in the modules are displayed, as required, in Oracle Database 10g stack trace listings.
:
6.3.2 Floating Point Format
Oracle Database 10g is compiled with the IEEE floating point format supported by the C compiler.
The conversion routines within Oracle Database 10g translate operating system-specific floating point numbers into Oracle Database 10g internal floating point representation.
This is a change from Oracle Database 9i release 2 for OpenVMS. If you had an application that depended on non-IEEE defaults, then you may need to recompile.

Hein.

Re: Runtime error from OCI on OpenVMS

Glenn Wolf — Wed, 04 Mar 2009 06:24:59 GMT

Hein:

It doesn't seem to matter how the debugger looks at them:

DBG> ex/float *valueP
*Field::copyValue\this->valueP: 0.145858885894793E-313
DBG> ex/s_float *valueP
*Field::copyValue\this->valueP: -0.4497490E-09
DBG> ex/x_float *valueP
*Field::copyValue\this->valueP: 0.191161202331074093779369080163257E-4955
DBG> ex/g_float *valueP
*Field::copyValue\this->valueP: 237.498901367188
DBG> ex/f_float *valueP
*Field::copyValue\this->valueP: 1.106933
DBG>

Of course, none of these values are "reasonable" for the application data.

The c++ compile command used for the application is "cxx/nowarn/define=(VMS,VMS,__WITHOOPS,_CPLUSPLUS_)/NOOPTIMIZE/STANDARD=ARM/prefix=all/names=as_is/float=g_float". So based on the quote you provide about Oracle 10 being compiled for ieee float format and that being different from Oracle 9, this sure looks like the cause.

This application is a "helper" application that reads the primary application's RMS ISAM DB, "normalizes" it into a relational schema, and stores it in the Oracle DB. The primary application is compiled /float=g_float, and stores g_floats in its database. So it looks like I need to read g_floats from the RMS files, and convert them into ieee float to be compatible with the oracle libraries on the way to the proprietary oracle floating point format. Do you know of c RTL routines to do such a conversion? I'll dig through the RTL manual in the morning.

Thanks a ton Hein! I've got about 1 week to make this work before I miss a deployment window for the new Itanium boxes, and if I miss it, these new boxes will sit around for 18 months until I get another downtime shot.

Thanks again!

Glenn

Re: Runtime error from OCI on OpenVMS

Wim Van den Wyngaert — Wed, 04 Mar 2009 10:03:23 GMT

http://h71000.www7.hp.com/doc/82final/5933/5933pro_001.html#5933cnvout_1

OTS$CNVOUT to convert to char, then
OTS$CVT_T_xxx to convert to ieee float

http://h71000.www7.hp.com/doc/82final/5933/5933pro_002.html#5933cvt_t_x

Wim

Re: Runtime error from OCI on OpenVMS

Hein van den Heuvel — Wed, 04 Mar 2009 12:16:36 GMT

Well, you first have to ask yourself how muxh or little work it would be to convert the main application to use IEEE. That's where it where it should. A main reason to want to be gfloat is file stored data, but that's not the case here, as the data is already stored in Oracle right? An other reason is exception (NaN) handling. I can not judge how much work that will be.
The application chunk suffering the exception does not care, as it is just a pass-thru layer to OCI.

If full conversion to IEEE is not feasible in the time remaining, then why not make the Oracle wrapper layer convert all floats to numbers. Oracle does not store them as floats any. If you check the OCI documentation for HOST VARIABLES you'll see CHAR merrily next to FLOAT for compatile with the DB data type: NUMBER(x,y).

I do not expect any preceivable performance or space savings from 'pre-converting'. And OCI performance will probably be much the same no matter which host variable type is used, but you can (should) confirm with Oracle what the 'best' host variable type is for NUMBER(x,y) ... other than INTEGER.

Some DCL or PERL script can probably automate the conversion injections, or 'generate' them.

Given the time line, I would urge you to try and engage last minute outside help. An other pair of eyes from someone who does not have the application itself to worry on and can focus just on this. Several folks in this conference come to mind, many others are out there. (Not myself,... I'm out consulting this week). Even if they end up not being able to solve the problem in the time remaining, at least you will know you did everything you could (wouda, couda shouda),

Regards,

Hein van den Heuvel ( at gmail dot com )
HvdH Performance Consulting.

Re: Runtime error from OCI on OpenVMS

Glenn Wolf — Tue, 17 Mar 2009 17:00:10 GMT

Thanks to all who responded. I've resolved the issue by sprintf'ing the G_FLOAT (actually, they are 4-byte F_FLOAT) values to a string, and putting the string in the bind variable. I'm not 100% satisfied with this approach, as some precision is lost.

I'd like to re-implement using CVT$CONVERT_FLOAT to convert directly from F_FLOAT to S_FLOAT, which will be simpler in the long run.

As to converting the app to use IEEE internally, I've got 8 installations around the world running 24x7, with hundreds of gigabytes of data files that would need to be converted. At this point, there's no compelling reason to take on this conversion exercise.

Thanks again!

Glenn