Home Contact Us Products About Us Fine Print Staff Partners
Suprtool Qedit Qedit for Windows Robelle
Daily Support Issues Disaster Top Ten Software Version Information Change Notices/Installation Information Submit Problem Report Change Notices/Installation Information How Do I? Business Hours Suprtool Code Generation
E-Mail: support@robelle.com Tel: 289.480.1060
Change Contact at your Site Other Updates to Your Account Dealing with CPU Upgrades New Customer Information Sign up for Direct Support Licensing Policies Request Suprtool demo/info Request Qedit demo/info Request Qedit for Windows Demo/Info
Business Hours
Fiserv/Spectrum New for Fiserv/Spectrum Users Amisys New for Amisys Users Ecometry New for Ecometry Users Education Applications
Historical Newsletter HP3000 Evolution Book Homesteading on the HP3000 Links and Info about MPE/iX HP 3000 Migration Links/Info Links and Info about HP-UX Product Manuals Suprtool Training Technical Papers Tutorials Technical Articles HP Encyclopedia Consultants Corner

Suprtool

Transforming TurboIMAGE Data to Oracle

By Bob Green, Robelle

First Published in the 3000 NewsWire

Recently we have had a number of customers contact our technical support team with questions and problems having to do with migrating TurboIMAGE data fields to Oracle. Since the two databases do not support the exact same datatypes, there are a number of cases where problems can arise.

The users goal is to convert the data without loss and with the least possible changes to the programs that access and update that data. These programs are often written in COBOL, but Fortran and C are also common.

The TurboIMAGE DataTypes

A data item definition has three parts:

sub-item-count   datatype   sub-item-length

A typical data item definition is J2 or 5X8.

Sub-item-count is an integer from 1 to 255 that denotes the number of sub-items within an item. If omitted, the sub-item-count equals one by default. A data item whose sub-item count is 1 is a simple item. If the sub-item count is greater than 1, it is a compound item. We might also call it an array or a table of values.

Sub-item-length is an integer from 1 to 255. It is the number of halfwords, bytes, or nibbles (depending on the type designator) in a sub-item. If omitted, it is equal to 1 by default.

Datatype is a letter code signifying the format of data stored in this field.

The type designators are E, I, J, K, P, R, U, X, and Z.

E = ieee floating point. sub-item length is in halfwords

I = signed integer, sub-item length is in halfwords

J = signed integer, but conforms to COBOL standards (i.e. s9999 has max value 9999). sub-item length is in halfwords

K = unsigned integer, no negative value. 1 halfword = 0-65K, 2 halfwords= 0-2 Billion, sub-item length is in halfwords

P = packed decimal, sub-item length is in nibbles, 2 to 28, with one digit used for the sign (note: TurboIMAGE will let you create a P48 or even larger, but COBOL will not process it).

R = classic HP 3000 floating point, old, 2 halfwords or 4 halfwords, you should probably not be using this type!

U = uppercase ASCII chars, sub-item length is in bytes.

X = any ASCII characters, sub-item length is in bytes.

Z = zoned decimal number. sub-item length is in bytes.

The size of the entire data item must be a multiple of halfwords (16 bits). Therefore, P types normally come in multiples of 4 and U/X/Z types come in multiples of 2. However, you actually multiply the sub-item-count by the sub-item-length to get the data item size, so 2X7 is valid.

TurboIMAGE Compatibility with Languages

Although TurboIMAGE does not place any restrictions on the reasonableness of item datatypes (i.e., you can define J25 if you wish) and does not validate values before inserting them into the database, most TurboIMAGE databases use only the data types that can be processed by the programming languages on the HP 3000.

Here are the data types that are supported in COBOL and Fortran:

Xn: Character, n bytes, define as Character in FORTRAN, X(n) in COBOL.

Un: Uppercase Character, n bytes, define as Character in Fortran, A(n) in COBOL.

E2: Floating-Point, 4 bytes, define as Real in Fortran, not supported in HP COBOL.

E4 Floating-point, 8 bytes, define as Double Precision in Fortran, not supported in HP COBOL.

I1/J1 Integer, 2 bytes, define as Integer*2 in Fortran, S9 to S9(4) Comp in COBOL.

I2/J2 Integer, 4 bytes, define as Integer*4 in Fortran, S9(5) to s9(9) Comp in COBOL.

I4/J4 Integer, 8 bytes, define as S9(10) to S9(18) Comp in COBOL, not supported in Fortran.

K1 Logical, 2 bytes, define as Logical in Fortran, not supported in COBOL

Zn Zoned-Decimal, n bytes, s(n) Display in COBOL, overpunched

P4 Packed-Decimal, 2 bytes, s9(3) Comp-3 in COBOL, not supported in Fortran.

P8 Packed-Decimal, 4 bytes, s9(7) Comp-3 in COBOL, not supported in Fortran.

Pn Packed-Decimal, n/2 bytes, s9(n-1) Comp-3 in COBOL, not supported in Fortran. Maximum N in HP COBOL is 19 (18 digits plus a sign).

Zn Numeric Display, n bytes, s9(n) Display in COBOL, with sign "overpunched" in the units position (unless you specify SIGN IS SEPARATE, then there are only n-1 digits in the value).

Converting to Oracle

What happens when you prepare to transform this TurboIMAGE data for Oracle on another platform, such as HP-UX? Are there any traps that you can fall into?

Oracle has internal datatypes and external datatypes. The internal datatypes are the format that Oracle actually stores the data in. However, these internal datatypes, especially the Number format, would not be recognized by any of the standard programming languages. For example, the Number format is described as follows in the Oracle manual:

"Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with one byte used to store the exponent and up to 20 bytes to store the mantissa. (However, there are only 38 digits of precision.) Oracle does not store leading and trailing zeros."

You cannot process such a number directly in Fortran, COBOL, or C.

Therefore, Oracle also has external datatypes, which are the formats that Oracle is willing to transform into for calling code written in languages that include C, COBOL, Fortran and Java.

Here is how you would use the Oracle datatypes:

Oracle 7

CHAR for X/U fields of up to 255 characters.

VARCHAR2 for X/U fields up to 2000 characters.

NUMBER for I/J/K/P/Z fields, up to 38 digits.

DATE for any field that contained a date value, such as SHIP-DATE X8, or DUE-DATE J2, or ORDER-DATE Z8. The Oracle date can also hold the time.

Oracle 8i and 9i:

CHAR can hold up to 2000 characters.

VARCHAR2 can hold up to 4000 characters.

So it appears that converting our datatypes to Oracle is straightforward.

Any easy way to migrate the data is to export it from TurboIMAGE into a Comma-Delimited file (with every value converted to ASCII characters). We have an application note on our web page that describes how one customer did this using Suprtool (see end of article for all web links). You will find the Item command handy for defining the decimal place in your TurboIMAGE fields.

You will want the export file to have an explicit "." in numeric values where appropriate, since Oracle understands decimal places and remembers them. With a Display format, you only need to include an explicit decimal point as in 1234.89. STExport does that automatically when properly configured (Item command in Suprtool and Decimal Period in STExport). SQL*Loader takes care of the alignment with the column definition e.g. NUMBER(12,2). If the file contains more decimals than the column definition, SQL*Loader rounds it up. For example, if you try to load 1234.5398 into NUMBER(12,2), the table will contain 1234.54. Negative values must have a leading sign (Sign Leading in STExport).

Converting Your Program Data Declarations

However, what about converting our code that is written in COBOL and Fortran? For that, you must understand the concept of External Datatypes.

An External Datatype is the way an Oracle data value is presented to a user program. Since COBOL and Fortran cannot understand the internal format in which Oracle stores numbers, Oracle allows you to specify how you would like the data returned to your program.

But, Oracle only supports the External Datatypes that are common to most computer platforms. Unusual data types are not supported.

For character type fields, you normally select CHAR unless you want null-terminated STRING for C.

For numeric fields, here are the Oracle 7 choices that make sense for fields converted from TurboIMAGE:

16-bit Integer: PIC S9(4) COMP in COBOL, Integer*2 in Fortran.

32-bit Integer, PIC S9(9) COMP in COBOL, Integer*4 in Fortran.

32-bit Float, PIC S9(n)V9(n) COMP-1, REAL*4 in Fortran.

64-bit Float, PIC S(n)V9(n) COMP-1 in COBOL, REAL*8 or Double Precision in Fortran.

Packed-decimal, PIC S(n)V9(n) COMP-3 in COBOL, not supported in Fortran.

Display, PIC S(n)V9(n) DISPLAY in COBOL, not supported in Fortran.

An Example

So, if you had a COBOL program with a buffer like this: 

    01 NEW-ORDER-LINE.
       05 ORDER-NUMBER PIC 9(8) COMP.
       05 CUSTOMER-NUMBER PIC 9(6) COMP.
       05 ITEM-NUMBER PIC 9(7) COMP-3.
       05 ORDER-QUANTITY 9(8) COMP.
       05 UNIT-PRICE S9(6)V9(2) COMP.
       05 EXT-PRICE S9(7)V9(2) COMP.

you could convert it to Oracle and use the exact same COBOL data area to process the Oracle data. Here is how:

ORDER-NUMBER and CUSTOMER-NUMBER are K2 in TurboIMAGE, which converts to Number as the Oracle internal datatype, with 32-bit Integer as the external datatype.

ITEM-NUMBER is P8 in TurboIMAGE, which converts to Number as the Oracle internal datatype, with Packed-decimal as the external datatype.

UNIT-PRICE and EXT-PRICE are J2 in TurboIMAGE, which converts to Number in Oracle, with 32-bit integer as the external datatype.

Excellent. Easy data transformation and no changes to data definitions of our program.

What Could Possibly Go Wrong?

What if EXT-PRICE looked like this: 

      05 EXT-PRICE S9(10)V9(2) COMP.

In TurboIMAGE, this would be a J4, which converts to Number as the Oracle internal datatype.

But Oracle does not have an external datatype of 64-bit integer!

So you will have to use Packed-Decimal as the external datatype.

Which means changing the COBOL definition to 

      05 EXT-PRICE S9(10)V9(2) COMP-3.

Now your programs are different and any files you create with this data will probably need new data definitions.

And the same conversion to packed will occur for any small Integer that has a decimal place, even if the total number of digits is less than 10.

This does not sound too bad, unless you want to keep the same source on MPE and UNIX. Or if you have hundreds of tasks that may process this data!

P.S. Similar issues arise when migrating to other SQL databases, including mySQL.

Web References

Oracle 7 documentation: http://www.kbs.twi.tudelft.nl/Documentation/Database/Oracle7/DOC/dcommon/oin/

Oracle 8 documentation: http://www-rohan.sdsu.edu/doc/oracle/

Oracle 9 documentation: http://www.dbresources.com/oramanuals.php (requires registration)]

TurboIMAGE Reference Manual: http://docs.hp.com/mpeix/onlinedocs/30391-90012/30391-90012.html

HP COBOL Manual: http://docs.hp.com/cgi-bin/doc3k/B3150090013.11820/1

Data Definitions in the HP COBOL Manual: http://docs.hp.com/cgi-bin/doc3k/B3150090013.11820/52

Suprtool User Manuals: http://robelle.com/library/manuals/

Preparing TurboIMAGE Data for SQL*Loader: http://robelle.com/tips/st-export-oracle.html

The Underground Oracle FAQ Site http://www.orafaq.com/

You now know how the TurboIMAGE datatypes transform to the Oracle datatypes. If you are migrating an application from MPE, consult our Migration Resource Page.

To learn more about exporting, including MS Access and Excel, read our tutorial, Sharing HP e3000 Data to the World.

You might also be interested in two Suprtool articles on exporting 3000 data to mySQL and exporting to Excel.

Or another article on exporting to XML, with an introduction to XML for newcovers.