More than 1 year has passed since last update.

Db2のOracle互換機能を使ってみた😃<10>　～NUMBERタイプの互換性(4)～

Last updated at 2023-03-02Posted at 2020-04-16

今回は「NUMBERデータ・タイプの互換性」に関しての最後の回になります。
ご紹介するのはDb2にそのまま移行できないNUMBER定義に関してですが、これらはNUMBERとしては特殊な使い方なのではと個人的には思いますので、あまり使われてはいないかもしれませんが、念のため触れさせていただきます。

■OracleのNUMBERとDb2のNUMBERの異なる点

その３：精度と位取り指定できる値の違い

Oracle互換のDb2では、Oracleと同じNUMBERの指定が可能です。
しかし、そのままの指定では移行ができないケースがあります。
実はOracle互換のDb2では、以下のNUMBER指定はサポートされないという制約があります。

31 を超える精度属性
精度属性におけるアスタリスク (*) の使用
精度属性より大きい位取りの属性
負の位取りの属性
これらはすべて、Db2のDECIMALとOracleのNUMBERで指定できる精度と位取りの範囲の違いに起因しています。
では、これらの制約はどういうものなのでしょうか。

1.「 31 を超える精度属性」

DECIMALとNUMBERの定義できる範囲の違い
Db2のDECIMALで指定できる精度と位取りは以下になります。
DECIMAL(p,s)
　精度p(1〜31桁)、位取りs(0〜p:省略時 0)

一方OracleのNUMBERの定義は、
NUMBER(p,s)
　　精度p(1〜38桁)、位取りs(-84〜127桁:省略時 0)
ですので、精度が32以上のNUMBERはマッピングできるDECIMALはないということになります

実際、Oracle互換のDb2で、NUMBER(32,0)というような定義をしようとしても、以下のエラーになってしまいます。

SQL0604N  列の長さ、列の精度、列の桁数の属性、特殊タイプ、構造化タイプ、配列型、
構造化タイプの属性、ルーチン、キャスト・ターゲット・タイプ、タイプ・マッピング、
またはグローバル変数 "NUMBER(32,0)" が無効です。  SQLSTATE=42611

そのため、32桁以上の精度のOracle NUMBERは、DECIMALにはマッピングできないので、Oracle互換のNUMBERではなく、DECFLOATとして明示的に定義する必要があります。

DECFLOATの定義範囲
ではDECFLOATはどの範囲の数値を表現できるのでしょうか。
Db2のDECFLOATには、有効数字の桁数の違いで、DECFLOAT(16)とDECFLOAT(34)があります。

DECFLOATの数値の表現は、9.999999999999999E+15のような指数で表現します。
この9.9999･･･9の数字部分が16桁か34桁かというのが１つの違いです。

2つめの違いは指数部分（E）の範囲の違いです。
DECFLOAT(16) は最大と最小指数が384と-383、DECFLOAT(34)は6144と-6143ですので、例えばDECFLOAT(34)の場合、正の値で言えば、
1.000000000000000000000000000000000E-6143
　～　9.999999999999999999999999999999999E+6144
というように、限りなく0に近い、または限りなく大きな値を表現することができます。
(参考資料：Db2 V11.1 Db2 SQL と XML の制限)
https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001029.html

Oracle NUMBERの精度は38桁が最大ですので、有効数字の桁数の観点ではDECFLOAT(34)でも足りないということになってしまいますが、NUMBER型は、
0と絶対値が1.0×10のマイナス130乗以上、1.0×10の126乗未満　の範囲にある正と負の固定小数点数を格納する（※）
ということですので、指数部分（E）の範囲はDECFLOATの -6143 ～ 6144 に対して NUMBERは-130 ～ 126 となり、最小・最大値の観点ではNUMBERよりDb2のDECFLOATの方が、格納できる数値の範囲は広いと言えます。
（※）(参考資料：Oracle SQL Language Reference 2.1 データ型)
https://docs.oracle.com/cd/E82638_01/sqlrf/Data-Types.html#GUID-F579F4B8-EF13-4CAF-9B06-03B076861C41

2.「精度属性におけるアスタリスク (*) の使用」

Oracle NUMBERの精度にアスタリスクを指定すると、これは位取りが固定されない定義になります。
つまり同じNUMBER(*)の列に1234.123も1234.12も挿入可能という事で、小数点以下の位取りは変動します。
ちなみにDb2では精度を指定せずDECIMALと定義すると、DECIMAL(5,0)と解釈されます。
Db2では小数点以下の位取りが固定でないDECIMALというのはありません。

3.「精度属性より大きい位取りの属性」

Oracle NUMBER は、精度の最大値の38に対して、位取りの最大値は128と精度以上の値が指定できます。
これはDb2のDECIMALに慣れた私からすると、いったいどういう意味なのかと思いましたが、例えばNUMBER(2,7)とすると、これは有効数字の桁数が2で、小数点以下が7桁という指定になるようです。
よって、小数点以下が8桁の0.00000123を挿入すると、小数点以下7桁になるように四捨五入され、格納されるデータは0.0000012になります。
また有効数字桁数は2桁なので、0.00001234や0.00000999のように小数点以下8桁めを四捨五入して7桁にした時に有効数字が3桁になる数字(0.0000123や0.0000100)は、挿入できません。

4.「負（マイナス）の位取りの属性」

Oracle NUMBERはマイナスの位取りも指定できます。
DECIMALの位取りは0～精度のpまでですので、マイナスの指定はできません。
NUMBERの位取りがマイナスというのは、小数点の左側、つまり整数部分にある桁数を表すということですので、整数部分の末尾の0の数と考えてよいです。

例えばNUMBER(6,-3)に111111を挿入すると、格納されるデータは111000に、また55555.5を挿入すると56000になるというように、実際のデータは整数部分の右からマイナスで指定された桁数が丸められるということになります。
このような法則を定義できる数値データ・タイプはDb2にはないので、Oracle NUMBERの特徴的な仕様といえるでしょう。

以上、Oracle互換のNUMBERの制約になっている定義というのは、具体的にどういう意味なのかをご紹介しました。

制約に合致するNUMBER定義がないかの事前確認は必要

上記でご紹介したNUMBERの定義を使用されているケースは実際は少ないかもしれません。
ただお使いのOracle上にこれらの定義がある場合、そのままの定義でDb2に移行することはできませんので、合致するものがないという事が確実でないなら、「ないという確認をすること」は必要です。
また万が一合致するものがあった場合は、どのように変更して移行するのかは、アプリケーション要件とあわせて検討する必要があります。

番外編

なお、上記制約中の「3．精度属性より大きい位取りの属性」と「4．負の位取りの属性」については、私にとっては、なかなか理解しがたいものであったのですが、自分なりの法則に当てはめてみたら理解できたので、それについては以下に投稿させていただきました。
ご興味がある方はご参照していただければと思います。
・Db2目線で見た Oracle NUMBERの謎の解明

「NUMBERタイプの互換性」まとめ

ここまで「NUMBERデータ・タイプの互換性」に関して4回に渡ってご紹介させていただきました。

Oracle互換のNUMBERの機能というのは、Oracleで使っていたNUMBERの指定をそのまま利用できるようにした機能ですので、OracleからDb2へ移行する際に、表のDDLの変更負荷を軽減できる便利な機能と言えます。

ただ内部的にはDb2の従来からあるDECIMALやDECFLOATとして扱われるので、その仕様や制約に従うものとなるということが重要となります。

そのため場合によってはそのままのNUMBER定義が使えないケースもありますので、制約に合致するものはないかの事前調査や、変更した方がいいものの洗い出しと対応の検討、またデータベースの構成パラメータの見直しなども必要となってくることをご理解いただけたなら幸いです。

ただ今までの経験からすると、実際にはこのような問題にはあたらずOracleからDb2へ移行されているお客様も多くいらっしゃるということを、お伝えしておきたいと思います。

おわり

Db2のOracle互換機能を使ってみた😃<10> ～NUMBERタイプの互換性(4)～