VitalSoft Sponsor Message

June 2002

Boosting your e3000 productivity

Transforming TurboIMAGE Data to Oracle

By Bob Green

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.

TurboIMAGE Data Types

A data item definition has three parts: a sub-item-count, a datatype and a sub-item-length.

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, defined as follows:

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, two halfwords or four 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 four and U/X/Z types come in multiples of two. 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. The following data types 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

Now 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 uses to store the data. 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:

In Oracle 7:

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

VARCHAR2 for X/U fields up to 2,000 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.

In Oracle 8i and 9i:

CHAR can hold up to 2,000 characters.

VARCHAR2 can hold up to 4,000 characters.

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

An 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 at robelle.com/tips/st-export-oracle.html. 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 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.

So, suppose you had a COBOL program with a buffer like:

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.

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

• 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?

Suppose 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! Similar issues arise when migrating to other SQL databases, including mySQL.Copyright The 3000 NewsWire. All rights reserved.