読者です 読者をやめる 読者になる 読者になる

一所懸命に手抜きする

監査の仕事をしています。ITを利用し、良い意味で「手抜き」することは効率化と精度アップに役立つと思うんです。部下への引き継ぎのためにまずは諸々頭から引っ張り出そうとブログを始めました。

[ Main ]  [ 別館 ]
当ブログで取り上げられている事案やデータベース、人物等はすべてフィクションです。

ユーザー関数 GetASHeader()でAS400(DB2)から項目別名(日本語)をSQLで取得する。ヘッダを日本語に!

SQL AS400

※ RでなくてもSQLが使えれば SYSIBM.SYSCOLUMNS 等からの抽出部分は役立つと思います。

AS400のテーブルから項目別名を取得するのは難しい

AS400にも日本語対応の項目別名的なものがあるが取得するのは難しい

 AS400に下記テーブル(MASTER/OFFICE)があるとします。

S001 S002 S003 S004
1 10 札幌 11 札幌
2 20 東京 21 大手町
3 20 東京 22 秋葉原
4 20 東京 23 横浜

 項目名がS001,S002…となっていて可読性が低いですね。S002って何だっけなどと毎回調べることになります。
 AS400の DSPFMT コマンドでレコード設計書画面を表示すると、当社では下図のように項目名とは別に日本語で項目説明がなされています。これを取得してヘッダとして利用できれば良いのですが、AS400(iSeries)に用意されているデータ転送では項目名を取り込むことしかできないようです。

DSPFMTより抜粋例
項目名 桁数 属性 バイ 開始 終了 テキスト
S001 3 0 B 2 1 2 支社番号
S002 10 O 10 3 12 支社名
S003 3 0 B 2 13 14 支店番号
S004 10 O 10 15 24 支店名

ASからテキスト記述を取得するユーザー関数 GetASHeader()

 当社の場合にはデータベースの活用の便を考えてテキスト記述には可能な限り日本語で記入されています。この情報を取得すればヘッダに流用できるかもしれません。そのままでは使えなくてもSQL文を書く際にどの項目を抜き出すか考える助けにはなるはずです。そこでユーザー関数を作ってみました。

記述欄を取得するユーザー関数 GetASHeader()
GetASHeader<- function(.Tab,.FPath="",.Horizon=TRUE,.Disp=FALSE)
    # AS400からテーブル内の列名(e.g.S001)、列目別名(e.g.店舗名)を取得
    # RODBC,ユーザー関数 GetAS400()が必要。
    # .Tab     : AS400 ライブラリ名.テーブル名 
    # .FPath   : ヘッダを保存するPCファイルパス(空だと保存しない)
    # .Horizon : ヘッダを横に並べて書き出し(select * に最適)
    # .Disp    : ヘッダ情報などを画面に表示するか
{
                                            #ライブラリ名、テーブル名に分割
        .Lib<-toupper(unlist(strsplit(.Tab,"[.]"))[1]);
        .Tab<-toupper(unlist(strsplit(.Tab,"[.]"))[2]);
                                            #列名と列別名(テキスト記述)を取得
    oSQL<-paste0("SELECT  COLUMN_NAME,COLUMN_HEADING
  FROM SYSIBM.SYSCOLUMNS  WHERE TABLE_SCHEMA='",.Lib,"' and TABLE_NAME = '",.Tab,"'")
    # ユーザー関数 GetAS400() 必要
    .h<-GetAS400(oSQL)                     # SQLでテーブル情報取得
    .h$COLUMN_HEADING<-gsub(" ","",.h$COLUMN_HEADING)
    .h$COLUMN_HEADING<-gsub(" ","",.h$COLUMN_HEADING)
    if (.Horizon){ .header <- .h  }  else {  .header <- t(.h) }
    if(.Disp){                            # .Disp表示が必要(TRUE)なら
        print(oSQL)                     # SQLを表示 
        print(.header)}                     # ヘッダを表示
    if(.FPath!=""){ WriteCSV(.header,.FPath) } # 必要ならPCにヘッダ書き出し
    return(.header)
}

留意点

  1. ユーザー関数 GetAS400()を呼び出していますから、 GetAS400()RODBCが必要です。
  2. SYSIBM.SYSCOLUMNS の部分は自社環境に合わせてください。QSYS2など。
  3. エラートラップはしていませんので実用上はエラー処理を加えて下さい。
  4. 引数.Tabは REPORT.URI12 のようにAS400 ライブラリ名.テーブル名の形式で指定して下さい。
  5. .Horizon=TRUEだとヘッダを横に並べて書き出します。select * で全項目を取得する場合にはヘッダを置き換えるのが簡単です。

GetASHeader()を使ってみる

ユーザー関数 GetASHeader()で記述欄を取得する例
GetASHeader("MASTER.OFFICE","C:/Wrk/OfficeMaster.csv",TRUE,TRUE)
[1] "SELECT  COLUMN_NAME,COLUMN_HEADING\n
FROM SYSIBM.SYSCOLUMNS  WHERE TABLE_SCHEMA='MASTER' and TABLE_NAME = 'OFFICE'"
               1          2        3          4
COLUMN_NAME    "S001"     "S002"   "S003"     "S004"
COLUMN_HEADING "支社番号" "支社名" "支店番号" "支店名"
ユーザー関数 GetASHeader()で得られる情報(.Horizon=TRUE)
1 2 3 4
S001 S002 S003 S004
支社番号 支社名 支店番号 支店名

ヘッダを手動で置き換えてみる

 ユーザー関数 GetASHeader() はヘッダを全件抽出します。ということは、 select * で抜き出したデータのヘッダと一致します。.Horizon=TRUEでヘッダを抽出したのであればCSVの1行をCopy&Pasteで書き換えるだけでヘッダが置き換わります。

ASから通常得られるCSVの形
S001 S002 S003 S004 ←ここを置き換える
1 10 札幌 11 札幌
2 20 東京 21 大手町
3 20 東京 22 秋葉原
4 20 東京 23 横浜
GetASHeader()で取得したヘッダに手動で置き換えた
支社番号 支社名 支店番号 支店名
1 10 札幌 11 札幌
2 20 東京 21 大手町
3 20 東京 22 秋葉原
4 20 東京 23 横浜

が、項目を間引いて抽出した場合にはそのままヘッダを置き換えることはできません。

f:id:a_habakiri:20161229134725p:plain:w2

広告を非表示にする