メニューボタン
IBMi海外記事2020.11.25

SQLでプログラム記述データを読み取る

Ted Holt 著

私は、プログラム記述データベース ファイルとは1988年にお別れしたと思っていました。私にとって最後のS/36のショップを退職して、S/38で仕事をし始めたときのことです。自分としてはそうしたつもりでしたが、完全に縁が切れたわけではなかったようです。時折、私はプログラム記述ファイルがあるシステムで作業することもあり、さらには、外部記述ファイルに、プログラム記述フィールドがあるケースさえあります。幸いにも、そして、これはScott Forstie氏のおかげなのですが、私はSQLがプログラム記述データを読み取ることができることを知りました。驚きの念を禁じ得ないといったところでしょうか。

「この記事は自分には当てはまらない。ウチのファイルは外部記述だから」と思われているかもしれません。その通りなのかもしれません。けれども、実はそうではないかもしれません。私は、外部記述ファイル内にプログラム記述データを数多く見掛けることがあるからです。

プログラム記述データがよく見られる場所として思い浮かぶのは、次の3つの場所です。

  • プログラム記述データは、プログラム記述ファイル内に見られます。これらのファイルは、通常、S/36およびその前身に遡ります。IBM i のショップの何パーセントが現在もS/36アプリケーションを稼働しているのかはまったく分かりませんが、それらがあることは分かっています。
  • プログラム記述データは、制御および構成ファイル内に見られます。複数のデータ タイプを格納する外部記述ファイルがショップにあることも非常によくあります。また、 OTLT(単一参照表) も、このカテゴリーに収まります。
  • プログラム記述データは、設計した目的とは異なるデータを格納するのに使用されるフィールド内に見られます。たとえば、ある企業が2番目の品目説明のために45バイトの文字フィールドがあるERPシステムをライセンス登録したとします。ところが、その企業はその2番目の品目説明を使用しませんでした。フィールドがない少数のデータ値を格納する必要はあったため、それらを2番目の品目説明に入れて、RPGプログラムでデータ構造を使用してそれらを定義したといったケースです。

埋め込まれたデータを読み取ることを可能にするSQL関数はINTERPRETです。フォーマットは次の通りです。

INTERPRET ( value AS data-type )

value引数はバイナリー値です。SQL用語で言えば、列はBINARY、VARBINARY、またはCHAR FOR BIT DATAとして定義される必要があります。私は表を作成するときにそれらのデータ タイプを使用したことはありませんが、それは問題ではありません。文字変数をバイナリーにキャストすることができるからです。

data-type引数は、データがどのようにして格納されるか、すなわちデータ タイプおよびサイズです。

2つの例をまとめてみました。プログラム記述データがある場合には、それらの例が参考になればと思います。

例1: プログラム記述ファイル

まずは、1つ目のケースです。在庫品目のロケーション(棚番地)を格納するファイルがあります。各レコードには、以下の情報が入っています。

From To Field Type Size
1 10 Item Char 10
11 13 Aisle Zoned 3,0
14 16 Bay Zoned 3,0
17 17 Level Char 1
18 21 QOH Packed 7,3

このファイルでは、ある倉庫ロケーション(Aisle(通路)、Bay(間口)、およびLevel(段))に、Item(品目)がいくつ(QOHまたは手持数量)保管されているかを確認することができます。パックおよびゾーン値に注目してください。

以下は、このデータを読み取るSELECTステートメントです。

select             substr(ItemLoc,  1, 10)                 as Item,
  interpret(binary(substr(ItemLoc, 11,  3)) as numeric(3)) as Aisle,
  interpret(binary(substr(ItemLoc, 14,  3)) as numeric(3)) as Bay,
                   substr(ItemLoc, 17,  1)                 as Level,
  interpret(binary(substr(ItemLoc, 18,  4)) as dec(7,3))   as QOH
  from itemloc

以下は、この照会の出力です。

Item Aisle Bay Level QOH
AA-101 95 12 C 45.000
AA-101 62 8 B 50.125
AA-101 104 25 E 2000.55
BC-728 14 6 A 0.000
DE-345 87 19 A -3.000

負の数になることがないゾーン10進数値の場合は、INTERPRETを使用することなく、部分文字列を取得するだけにすることもできます。この場合、返されるデータは文字となり、ゾーンではないことに注意してください。

select                  substr(ItemLoc,  1, 10)              as Item,
                        substr(ItemLoc, 11,  3)              as Aisle,
                        substr(ItemLoc, 14,  3)              as Bay,
                        substr(ItemLoc, 17,  1)              as Level,
       interpret(binary(substr(ItemLoc, 18, 4)) as dec(7,3)) as QOH
  from itemloc

例2: 制御ファイル

シンプルな制御または構成ファイルについて見てみましょう。

Field Type Size
Key Char 8
Sequence Packed 3,0
Record type Char 2
Entry-specific data Char 256

Key(キー)、Sequence(シーケンス)、およびRecord type(レコード タイプ)はすべて簡単にアクセスできますが、最後の列の値はそうではありません。そのような表には、多くのレコード タイプがあるのが普通ですが、この例では、3つで十分です。

  • CN: Company name(会社名)、1行
  • CP: Current financial period(現在の会計期)、1行
  • DV: Divisions(部署)、複数行

以下は、各レコード タイプのEntry-specific-data(項目固有データ)列のフォーマットです。

  • レコード タイプCN
    From To Type Size Field
    1 24 Char 24 Company name
  • レコード タイプCP
    From To Type Size Field
    1 2 Packed 3,0 Current period number
    3 7 Packed 8,0 Current period begin date
    8 12 Packed 8,0 Current period end date
  • レコード タイプDV
    From To Type Size Field
    1 4 Zoned 4,0 Division ID
    5 29 Char 25 Division name
    30 54 Char 25 Street address 1
    55 74 Char 20 City
    75 76 Char 2 State
    77 86 Char 10 Postal code (ZIP)

CNレコード タイプを読み取るのは簡単です。項目固有データにあるのは1つの値のみであり、その値は文字です。

select sequence, rectype, 
       substr(data, 1, 24) as CompanyName
  from control
 where RecType = 'CN'
Sequence RecType CompanyName
10 CN ACME Industries

CPフォーマットは、パック10進数のみです。

select sequence, rectype, 
       interpret(binary(substr(data, 1,  2)) as dec(3)) as CurrentPeriod,
       interpret(binary(substr(data, 3,  5)) as dec(8)) as BeginDate,
       interpret(binary(substr(data, 8,  5)) as dec(8)) as EndDate
  from control
 where RecType = 'CP'
Sequence RecType CurrentPeriod BeginDate EndDate
20 CP 9 20200901 20200930

DVレコード タイプには、文字データとゾーン データがあります。

select sequence, rectype, 
       interpret(binary(substr(data, 1, 4)) as numeric(4))
                            as DivisionID,
       substr(data,  5, 25) as DivisionName,
       substr(data, 30, 25) as Street,
       substr(data, 55, 20) as City,
       substr(data, 75,  2) as State,
       substr(data, 77, 10) as ZIP
  from control
 where RecType = 'DV'
Sequence RecType DivisionID DivisionName Street City State ZIP
30 DV 100 Widgets 101 Main St Lost Angeles KZ 12345
31 DV 1204 Doohickeys 999 Worse St New Yolk MQ 23456-9876
32 DV 1492 Thingamajigs 321 Easy St Last Vegas ZT 30405

理想的ではありませんが、十分に使えると思います。

あるいは、先日他界したチャーリー・ダニエルズを真似して言えば、「That's how you do it, son(こうやればいいんだよ)」でしょうか。

あわせて読みたい記事

PAGE TOP