FC2ブログ

社内SEの徒然なる日記

これはCOBOLで書かれた特殊なプログラムなので高齢者しか読めず

■ 

ここ最近、政治の世界で賑わっている勤労統計問題の原因ですが、先日「COBOLプログラムのバグが原因」という主旨の衝撃の発言が飛び出して思わず「マジかよ」って苦笑いしてしまいました。

COBOLを扱える人間が少ないのでダブルチェックが出来なかったとか色々と言っていますが、まぁ、言い訳ですね。一般的な話として、どんな言語であったとしてもプログラムのダブルチェックなんて基本しませんし(そんなに暇じゃない)。

しっかし、高齢者しか読めないとか言われれると、COBOLバリバリ読み書きできる身としては「そうか、俺は高齢者だったのか・・・」って思っちゃいます。

まぁ、高齢者ではなくても中年のオッサンなのは事実なので、別に良いんですけどね(苦笑)

■ 後書き

とりあえず、悪いのはシステムってことにする良くある責任逃れでしょうね。もっとも、最近はこの言い訳も通じなくなってきたように感じますが。

個人的な思いとしては、特定の条件下ではCOBOLって異様に高い性能を出すし、他言語と比べて比較的プログラムが追いやすい。そして何よりもテキストファイル(主に固定長)を扱うのであれば、これ以上は無いって断言できるレベルなので、条件反射的に拒絶するのも違うのかなと。

もっとも、新規のプログラムをわざわざCOBOLで書こうとは思いませんがね。

最新の記事:http://harikofu.blog.fc2.com/
投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end — スポンサードリンク

PageTop

COBOLからJavaに書き換えたらCOBOLの偉大さを感じた今日この頃(後編)

■ メモリ不足

前回(COBOLからJavaに書き換えたらCOBOLの偉大さを感じた今日この頃(前編))、COBOLからJavaにプログラムを書き換えて、テストまで完了しました。

これで一安心と思ったのですが、念のために実際の運用で発生するだろうデータ量を用意して実行してみたのですが、ここから長い戦いが始まりました。

DWHの更新処理は日々実行されていて、基本的には前日の処理実行日時の後に登録、更新されたデータを対象にします。

そのため、月次処理などで一括フラグ更新などが走った後には大量のデータが対象になりえます。具体的には、20〜30万件程度です。

そして、Java側の処理は、こんな感じ。

① 基幹DBから対象データ抽出
② ①の抽出結果をメモリに蓄積
③ メモリに蓄積した1行を読込
④ ③でDWHの対象テーブルをPK検索
⑤ ④の抽出結果の有無で、UPDATE か INSERT

・・・結果、②のメモリに蓄積する処理で、10万件くらい蓄積した辺りでOutOfMemoryErrorが発生しました。

うん、そんな気はしてたんですよね。

■ 性能劣化

OS側の物理メモリに余裕はあるので、Javaに割り当てるメモリ量を増やすという手もあったのですが、キリがないのでメモリ量を少なくするようにプログラムを書き換えました。

① 基幹DBから対象データ抽出
② ①の抽出結果をメモリに蓄積
③ メモリに蓄積した1行を読込
② 抽出結果から1行読込
③ ②でDWHの対象テーブルをPK検索
④ ③の抽出結果の有無で、UPDATE か INSERT

メモリに蓄積する処理を省く改修です。これでメモリ使用量は激減します。

この修正でメモリ不足は発生しなくなったのですが、いま一つ性能が良くありません。1万件の処理を行うのに、5分くらい時間が掛かります。30万件処理するとなると、250分です。

遅延箇所を調べると、③④のOracleの辺りの処理が地味に遅いです。1件の処理時間は0.07秒とかなのですが、件数があると累積時間が結構なものになりました。

同じ処理をCOBOLでやった場合、15分程度で終わるので、ここは無理に書き換えなくても良いと思ったのですが、大誤算ですよ。

■ 後書き

しっかし、COBOLからOracleに接続する仕組み(Pro*COBOL)って結構優秀なんですね。COBOL自体がコンパイルが必要な言語っていう特性もあるせいか、この手のバッチ処理はCOBOLに一日の長があるようです。

単純にロジックごとJavaに移植しても、上手く行かないことってあるようです。まぁ、JavaもOracleの一連の処理をガリガリ作りこんで最適化すれば、結果は違うのかも知れませんが。

結局どうしたかって言うと、MERGE文を発行することにしました。基本的には項目の移送なので、実はMERGE文一発で終わるのです。

ロジック変えないことに拘るのであれば、PL/SQLを使うって方法も考えられたのですが、ここまで来ると元のロジックに拘ってもしょうがないですし、ゼロベースで見直すことにしたのです。

結果、数分で終わるようになりました。こんなことなら、最初からMERGE文を使えば良かったですよ。

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end --- スポンサードリンク

PageTop

OracleのSELECT文でORA-08103発生!(結局PG書き換えた・・・)

■ ORA-08103

とある処理で、数ヶ月に一度くらいの頻度でORA-08103(現在、指定したオブジェクトは存在しません)が発生します。

Oracleの仕様上では、発生するとしたらDDL文の発行が絡んでいるらしいです。つまり、SELECT文を発行した時に、同じタイミングで対象テーブルに何かをした(TRUNCATE TABLEやALTER TABLE、ALTER INDEX)ってことらしいのです。

ですが、間違いなく、そんなことはしていません。夜間にスケジュール実行する処理なので、他の処理と被ることは決してありません。

サポートには、あり得ないと回答したのですが信じてくれず、トレースとか仕込んで再現待ち、そして再現した時に採取した資料を提出した結果、やっぱり何もしていないと判明。

・・・何だか、少し前にも同じような話があった気がします。

■ Oracleのバグ?

どうも、Oracle12cのバグっぽくて、修正パッチはあるようなのです。ただ、それで本当に直るのかはやってみないと分からないと言う話です。

そもそも、Oracleのパッチってバグが直った影響で、さらにバグが発生するみたいなこともあり得るので、いま一つ信用できないし、パッチを当てる前にシステムバックアップを取ったりもしないと行けないので、いま一つ気が乗りません。

ってことで、結局プログラムを書き換えて対応することにしました。何だかね。

■ 後書き

問題のプログラムなのですが、DWH更新用のデータをCSV出力するプログラムです。

大きな流れは、基幹DBからデータを抽出、CSV出力。CSVファイルを読み込んで、DWHに登録&更新するっていう仕組みです。

間にCSVファイルを経由する関係上、問題のエラーが発生するSELECT文は、結構妙なことをやっています。

SELECT
KIKAN.AAA,
NVL(KIKAN.BBB,' '),
NVL(KIKAN.CCC,' '),
KIKAN.DDD,
KIKAN.EEE,
NVL(KIKAN.FFF ,' '),
KIKAN.GGG,
REPLACE(NVL(KIKAN.HHH,' '),',','.'),
REPLACE(NVL(KIKAN.HHH,' '),',','.'),






つまり、
①Null → 半角スペースに置換
② , → . に置換
です。

何せ、プログラム言語がCOBOLなので、csvファイルの扱いが難しくて色々と小細工が必要だったのです。

さらに、数千万件のデータが蓄積されたテーブルへフルテーブルスキャンを掛ける仕組みなので、十数分も時間が掛かります(テラ級のメモリにSSDの組合せでも、これだけ掛かる)。

Oracle12gになってから、ちょっと妙な作りをしていると、エラーが発生することがあるので困ってしまいます。

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end ---
スポンサードリンク

PageTop

SQL(Oracle)で按分計算(差額も考慮)する方法の確認(前編)

■ 按分計算

あるシステム開発で、支店別の支払金額から按分比率を算出して、その比率に応じて手数料を按分するという要件が出てきました。

こういう計算は、結構面倒な要素が出てくるのでプログラム側で計算した方が良いかもしれませんが、今回はSQL1回で計算してみます。

計算の前提環境です。

1.取引先に対する合計支払金額が期日別に存在する。
2.支払金額は支店別、期日別に確定している。
3.手数料は取引先別期日別の合計金額がある

はい、取引先別期日別に按分するんです。

Aテーブル(PrimaryKey:管理番号、支店、期日)
管理番号  支店  期日    金額
10000 10 2/10 10,000
10000 20 2/10 90,000
20000 10 5/15 50,000


Bテーブル(PrimaryKey:管理番号、期日、連番)
管理番号  期日  連番  手数料
10000 2/10 1 400
10000 2/10 2 200
10000 2/10 3 100
10000 2/10 4 300


管理番号というのは、1支払単位(支払日&支払先)を表す番号です。

AテーブルとBテーブルの関係は、管理番号と期日で結合すると1:N。なのでBテーブルにデータがある時は、必ずAテーブルにもデータが存在するとします。

■ SQL

まず、結果をどうぞ。
SELECT
Z.管理番号
,Z.支店
,Z.期日
,Z.按分金額
+
CASE WHEN Z.連番 = 1
THEN
Z.按分合計 - SUM (Z.按分金額)
OVER (PARTITION BY
Z.管理番号
,Z.期日
)
ELSE 0
END
AS 最終按分金額
FROM
(
SELECT
A.管理番号
,A.支店
,A.期日
,NVL(B.按分合計 ,0) AS 按分合計
,ROUND(
RATIO_TO_REPORT (A.金額)
OVER (PARTITION BY
A.管理番号
,A.期日
)
* NVL(B.按分合計, 0), 0) AS 按分金額
,ROW_NUMBER ()
OVER (PARTITION BY
A.管理番号
,A.期日
ORDER BY
A.金額 DESC
,A.支店 ASC
) AS 連番
FROM
A
,( SELECT
管理番号
,期日
,SUM(B.手数料) AS 按分合計
FROM
B
GROUP BY
管理番号
,期日
) B
WHERE
A.管理番号 = B.管理番号(+)
AND A.期日 = B.期日(+)
) Z


もちろん、事前に動かして検証したのですが、ブログ用に項目名を変えたり小細工したので、このSQL自身は動かしていません。もしかすると構文エラーとか発生するかもです。

次回は、このSQLの構成要素を一つずつ分解しながら解説してみようと思います。今回は、概要だけ書いときます。

■ 簡易解説

まず、Bテーブルを按分単位である管理番号、期日で集計した手数料の合計金額を作ります(按分合計)。これを内部テーブルとします(内部Bテーブル)。そして、内部Bテーブルと、Aテーブルを管理番号、期日で外部結合します。

Oracleの分析関数「RATIO_TO_REPORT」を使用して、Aテーブルの管理番号、期日を集計単位とした合計金額に対する、管理番号、期日、支店の金額の比率を取得します(按分比率)。

取得した按分比率と内部テーブルの按分合計を掛け算して、ROUND関数で小数点以下四捨五入します(按分金額)。

さらに、分析関数「ROW_NUMBER」を使用して、Aテーブルの管理番号、期日を集計単位として、金額の降順、支店の昇順に連番を採番します。

ここまでの結果から、AテーブルのPrimaryKey(管理番号、支店、期日)と、内部Bテーブルの按分合計、取得した按分金額、連番 で内部テーブルを作成します(内部Zテーブル)。

内部Bテーブルから取得した按分合計から、管理番号、期日を集計単位として按分金額の合計を減算します(按分差額)。

連番が1のデータに対してのみ、按分金額に按分差額 を加算します。連番が1以外のデータは、按分金額をそのまま使います(最終按分金額)。

内部Zテーブルから、AテーブルのPrimaryKey(管理番号、支店、期日)と、取得した最終按分金額を取得します。

■ 後書き

実際には、Aテーブルに対して按分した金額を更新するという案件でした。なので、UPDATE文はこうなるのかな?

UPDATE A
SET 金額 = 金額 -
(
SELECT NVL(ZZ.最終按分金額, 0)
FROM ( 内部Zテーブル ) ZZ
WHERE ZZ.管理番号 = A.管理番号
AND ZZ.支店 = A.支店
AND ZZ.期日 = A.期日
)


・・・これこそ、何もテストしてないけど、本当に動くかな?

次回:SQL(Oracle)で按分計算(差額も考慮)する方法の確認(中編)

最新の記事:http://harikofu.blog.fc2.com/
投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end — スポンサードリンク

PageTop

ExcelVBA&ADOでOracleからデータ抽出で文字化け(解決編)

■ キャラクタセット

Oracle関係で文字化けした時の基本は、サーバ側とクライアント側で文字コードが違っていないか確認する事らしいです。

まぁ、oo4oでは上手く行くので大丈夫だとは思いますが、念のために確認してみます。

まず、サーバ側を確認するため下記のSQL文を発行します。
SELECT  *  FROM  V$NLS_PARAMETERS
WHERE parameter = 'NLS_CHARACTERSET'


結果は「JA16SJIS」。
PARAMETER	VALUE
NLS_CHARACTERSET JA16SJIS


クライアント側は、レジストリの¥HKEY_LOCAL_MACHINE¥SOFT¥WARE¥ORACLE¥KEY_OraClient11g_home1 の、NLS_NANG の値を確認。

設定されていたのは、これです。

JAPANESE_JAPAN.JA16SJISTILDE


うん、やっぱり良さそうです。

■ プロバイダ

今度は、ADO側のプロバイダを確認します。

'ADODB.Connectionオブジェクト
Dim mAdoCn As Object

'ADOコネクションの作成
Set mAdoCn = CreateObject("ADODB.Connection")

'接続先データベースにOracleを指定
mAdoCn.Provider = "OraOLEDB.Oracle" 'Oracle純正


使っているのは、Oracle純正の「OraOLEDB.Oracle」。うっかりMicrosoft製を使ったとかって訳でもなさそうです。

■ 環境ってよりは・・・

こうなると、環境っていうよりはもっと小さなレベルで問題が起きていると考えた方が良い気がします。そもそも、文字コードのレベルで間違えているのなら、〜だけでなく、もっと色々な文字が化けるような気がします。

そういった観点で調べていくと、どうやら文字コード変換の色々な絡みで〜が?になってしまう問題にたどり着きました。

どうすれば良いかっていうと、VBA側で特定の文字コードを強制的に〜に置き換える方法が良いそうです。

こんな感じかな?



'読み取り専用でファイルを開きます。このファイルに書き込むことはできません。
Public Const ForReading As Integer = 1
'ファイルを開き、ファイルの末尾から書き込みます。
Public Const ForAppending As Integer = 8
'システムの既定値を使ってファイルを開きます。
Public Const TristateUseDefault As Double = -2
'Unicode としてファイルを開きます。
Public Const TristateTrue As Double = -1
'ASCII ファイルとしてファイルを開きます。
Public Const TristateFalse As Double = 0

Sub TEST()

'----- 省略 Start --------
'
' Oracleに接続して、SQL(SELECT)文を実行。
' 実行結果を、myRs(レコードセット)に設定する。
'
'----- 省略 End ----------


    'ファイルシステムオブジェクトの宣言
    Dim myFs As Object
    Set myFs = CreateObject("Scripting.FileSystemObject")
    Dim myOutFile As Object

    '出力ファイルの作成とオープン
    myFs.CreateTextFile ("D:¥CSVTEST.csv")
    Set myOutFile = myFs.OpenTextFile("D:¥CSVTEST.csv", ForAppending, False, TristateFalse)

    'データ全件出力
    Do Until myRs.EOF

        '出力
        'myOutFile.WriteLine (myRs.Fields.Item(0).Value)
        myOutFile.WriteLine (Replace(myRs.Fields.Item(i).Value, ChrW(12316), "〜"))

        'カーソルを次行に変更します。
        myRs.MoveNext
    Loop
End Sub


この辺り、文字コードの結構複雑な話があるようです。一通り目を通したのですが、他人に説明できるほど理解しきれていないので省略させて下さい。

■ 後書き

この現象、そういえば、昔何かの記事で読んだ気がします。当時は「へぇ、そうなんだ」くらいの気持ちで読み流したのですが、まさか自分が遭遇するとは思いませんでした。

さて、これで問題解決って言いたかったのですが、結局は接続方法をoo4oに戻すことにしました。

実際にデータを投入して動かしてみると、取得結果がNullだった時の扱いとか色々なことが、oo4oADOで微妙に動作が違うってことが分かってきて、少し腰を据えて取り掛からないとダメだって事が判明しました。

ま、いつか納得するものが出来たら記事にしましょうか。

問題編:ExcelVBA&ADOでOracleからデータ抽出で文字化け(問題編)

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end --- スポンサードリンク

PageTop