IBM i (旧AS400）の自己学習メモ　AS400保守・運用編０１　WRKQRYでデータ調査／データ移行

# 自己学習メモインデックス

[IBM i (旧AS400）の自己学習メモ　インデックス](https://qiita.com/You_name_is_YU/items/3964849e7803c8eaf767)

# 概要

AS400のシステムを保守・運用していると、ほぼ毎日のように利用しているコマンドがあります。
`WRKQRY`はSQL文を書かずに調査対象となるファイル（テーブル名）や、結合条件（ON句）、抽出条件（WHERE句）などを指定してデータ抽出を行うことができる機能です。
また、抽出したデータを画面に表示するだけでなく、保存先としてデータベースにすることで、例えば○○ライブラリにあるファイルの一部を抽出し、そのデータを××ライブラリに保存するということもできます。
本番環境から開発・テスト環境へのデータコピーやデータ移行にも使えます。
（もちろん、同一区画内にそれぞれのライブラリが無いと実現できませんが。。。）

# WRKQRYの使い方

## 新規Queryの作成

### WRKQRYコマンド実行

すでに定義済みのQueryを編集したり実行したりすることもできますが、まずは新規にQueryを作成する手順を示します。
以下のコマンドを実行します。

```shell
wrkqry
```

すると、以下の画面が表示されます。

- Option : `1`（Create）を指定
    - 編集したい場合は`2`
    - それ以外はあんまり使ったことないっす
- Query : ※新規作成する場合は空でOK（すでに作成済みのQuery定義を編集したりする場合にそのQuery名を指定します。）
- Library : ※新規作成する場合はLibraryはなんでもOK（すでに作成済みのQuery定義を編集したりする場合は、そのQuery定義ファイルが保存されているライブラリを指定します。）

<img width="1088" alt="スクリーンショット 2020-05-10 18.00.50.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/4e191d11-9416-e2d3-039f-dc1e0a7017ce.png">

Query定義画面が表示されます。
画面表示の時点で、いきなり`Specify file selections`のOptに`1`がセットされた状態となっています。
何はともあれ対象となるファイルを指定しなさいよということのようです。
<img width="1088" alt="スクリーンショット 2020-05-10 18.04.59.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/448adc74-4110-4c13-716d-b4df06badcc4.png">
ここからざっくり、どういった手順でデータ抽出（SELECT）するかというと

1. `Specify file selections`で調査対象のファイルを指定 **FROM句**
2. `Select records`で抽出条件を指定 **WHERE句**
3. `Select and sequence fields`で抽出対象の項目（カラム）を指定 **SELECT句**
4. `Select sort fields`でソート条件を指定 **ORDER BY句**
5. `F5`キー押下で実行→結果確認

という流れで利用していきます。

### Specify file selections 調査対象のファイルを指定 FROM句

`Specify file selections`のOptに`1`をセットしてEnterを押下
<img width="1088" alt="スクリーンショット 2020-05-10 18.19.58.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/39099012-c7a8-b460-098d-134c9d42af22.png">

基本的には`File`と`Library`を指定してEnterを２回押下すると、Query定義画面に戻ります。
１回目のEnterで`Format`の値がDDSファイルで定義されているレコード様式の名前に勝手に変換されます。

- File : MTOKISKP
- Library : YAMAGUCHI1
- Member : *FIRST
- Format : *FIRST

![as400-1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/bec296be-f5f8-21ed-a471-85f54b032640.gif)

### Select records 抽出条件を指定 WHERE句

`Select records`のOptに`1`をセットしてEnterを押下
<img width="1088" alt="スクリーンショット 2020-05-10 18.28.45.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/b89f65ce-2121-aeec-2b24-9f391c635e9c.png">

以下の条件を指定します。

- Field : 条件となる項目（カラム）名を指定
- Test : 画面上部に青文字で`Tests: EQ, NE, ...`とヒントが出ている比較演算子を表すキーワードを指定する
- Value : 比較演算子の右辺 なんらかの値または比較対象のカラム名を指定
- AND/OR : 何も指定しない場合デフォルトで`AND`となる。OR条件にしたい場合は`OR`と指定

|Testキーワード|対応するSQLの比較演算子|利用例|
|:--|:--|:--|
|EQ|=|TOKCD EQ '00001'|
|NE|<>|TOKCD NE '00001'|
|GT|>|TOKRMT GT 100|
|LT|<|TOKRMT LT 9000000|
|GE|>=|TOKRMT GE 100|
|LE|<=|TOKRMT LE 9000000|
|RANGE|between|TOKRMT RANGE 1000000 9000000|
|IS|is null|TOKKANA IS NULL|
|ISNOT|is not null|TOKKANA ISNOT NULL|
|LIST|in|TOKCD LIST '00001' '00002'|
|NLIST|not in|TOKCD NLIST '00001' '00002'|
|LIKE|like|TOKNM LIKE '%商店%'|
|NLIKE|not like|TOKNM NLIKE '%商店%'|

![as400-1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/5c469bbe-99e2-4c12-fde7-ceb248c1c18e.gif)

### Select and sequence fields　抽出対象の項目を指定　SELECT句

`Select and sequence fields`のOptに`1`を指定してEnter
<img width="1088" alt="スクリーンショット 2020-05-10 18.54.01.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/6092343f-7379-4ca2-b117-f73cdb6add17.png">

`Seq`の項目に抽出したい項目の順番を0〜9999で指定することで、フィールドがその順番で並びます。
全項目をそのまま取得したい場合は`F21`キーを押下して全選択してもいいですし、何もSeqを指定しない場合、デフォルトでは全選択と同じ意味となります。

![as400-1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/bd1afa15-feb1-4965-26ae-0ebe41209b31.gif)

## Select sort fields　ソート条件を指定 ORDER BY句

`Select sort fields`のOptに`1`を指定してEnter

<img width="1088" alt="スクリーンショット 2020-05-10 19.04.00.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/365a47c1-c400-16b9-9b43-4e40e946bbe0.png">

- Sort Prty : ソート条件に指定する項目に対してその優先順位を0-999で指定
- A/D : A＝Ascending（昇順）　D＝Descending（降順）を指定　何も指定しない（デフォルト）は昇順

![as400-1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/af714113-2719-651e-b1dd-5d1ed47ad5a6.gif)

### F5キーを押下して実行

上記までの設定を行えば準備はOKです。
F5キーを押下すると各設定に応じた結果が画面に表示されます。
表示された画面は 24x80の画面なので、横スクロールしたい場合はF19=Left F20=Rightのキーを利用してスクロールしてデータを確認してください。

![as400-1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/a2d6c86d-424f-bcb7-0bb3-9389f0a22368.gif)

ここまでが、標準的なWRKQRYでのデータ調査方法です。
途中指定しているORDER BY句の指定や、WHERE句の指定、SELECT句の指定などは、どの順番でおこなっても構いませんし、不要であれば指定しなくてもOKです。

## 取得結果の集計（SUM）

数値項目を合計したい場合があります。SQLではSUMやCOUNTなどの集計関数と、MINやMAXなどの集約関数が存在します。
WRKQRYではそれらと同等の機能を実現することが可能です。
それの実現方法を紹介します。

手順の概要は以下の通りです。
もちろん、事前に`Specify file selections`で調査対象のファイルを選択した状態である必要があります。

1. Select report summary functions 集計項目と集計関数／集約関数の条件を設定　**SUM/COUNT/MIN/MAX...etc**
2. Define report breaks 集計項目の集計単位条件を設定　**GROUP BY句**
3. Select output type and output form 明細行／集計行の表示切り替え設定

### Select report summary functions 集計項目と集計関数／集約関数の条件を設定　SUM/COUNT/MIN/MAX...etc

`Select report summary functions`のOptに`1`を指定してEnter
<img width="1088" alt="スクリーンショット 2020-05-11 7.23.25.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/b4bc08b1-0853-d9fc-a472-bcc32b480a89.png">

Optionsの項目　１列目〜５列目に、順番に画面上部に表示されている`Type Options`の任意の値を必要な項目に設定します。

- 1=Total : SUM
- 2=Average : AVG
- 3=Minimum : MIN
- 4=Maximum : MAX
- 5=Count : COUNT

![as400-1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/e44a5884-8d6f-731a-d2ad-41b8ddab0cd8.gif)


### Define report breaks 集計項目の集計単位条件を設定　GROUP BY句

`Define report breaks`のOptに`1`を指定してEnter
<img width="1088" alt="スクリーンショット 2020-05-11 7.27.24.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/b5725aff-0373-4535-828e-9eddaa9235cb.png">

集計項目の集計単位を`Break Level`に設定します。
その他の設定はデフォルトのままEnterを押下してQuery定義画面に戻ります。

![as400-1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/0fdf6285-b66d-1b61-8eae-cf09fffff07d.gif)


### Select output type and output form 明細行／集計行の表示切り替え設定

`Select output type and output form`のOptに`1`を指定してEnter
<img width="1088" alt="スクリーンショット 2020-05-11 7.31.29.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/83659101-1695-7e93-3d40-c805d7eb0cf3.png">

`Form of output`の設定によって集計結果の出力が、明細行を含んで出力してくれるか、合計行のみ出力するかを設定できます。

1:明細行を含む
<img width="1088" alt="スクリーンショット 2020-05-11 7.33.29.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/51a08a55-5c63-bfbd-052f-0733cf0ae5a7.png">

2:合計行のみ
<img width="1088" alt="スクリーンショット 2020-05-11 7.33.51.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/0e48128e-875f-7b0e-34a3-c64df4963f44.png">

## 取得結果をデータベースファイルへ保存

1. Select output type and output form　保存先をデータベースファイルに設定
2. F3キーを押下してQueryを実行して終了

### Select output type and output form 保存先をデータベースファイルに設定

`Select output type and output form`のOptに`1`を指定してEnter
`Output type`に`3`(Database file)を指定してEnter
<img width="1088" alt="スクリーンショット 2020-05-11 7.42.19.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/5e9c0de3-f10a-9e52-d1c3-61c572ab8868.png">

- File : Queryで取得した結果を保存したいファイル
- Library : 保存先のライブラリ
- Data in file : `2`(Replace file)

※Replace fileにしているのは、取得結果を保存先に置き換えるためです。
データを単純に保存先のファイルに追加したい場合などは、それに応じた指定をしてください。

<img width="1088" alt="スクリーンショット 2020-05-11 7.42.37.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/f0529088-f78f-7daf-54f7-d5ba4af3355c.png">

### F3キーを押下してQueryを実行して終了

- Save definition : `N`（Query定義を保存しない）
- Run option : `1`(Run interactively 終了時にQueryを実行して終了してくれる)

（Save definitionをYにすると、作ったQuery定義を名前を付けて指定したライブラリに保存することができます。）

<img width="1088" alt="スクリーンショット 2020-05-11 7.45.19.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/f40e4dfc-8731-5d95-7a55-5b472853a5b0.png">

以下の画面イメージのように、指定したファイルを置き換えたよ。というメッセージが表示されます。
この例では`YAMAGUCHI1/QQRYOUT`ファイルに実行結果が保存されています。

<img width="1088" alt="スクリーンショット 2020-05-11 7.47.38.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/246556/2a8b3037-a5ea-6bf0-cd99-d182bbabf1e4.png">

WRKQRYには副問合せのような機能が無いので、副問合せを使った複雑なQRYを実現したい場合は、
一度ファイルに実行結果を保存しておき、その結果をさらにWRKQRYの対象ファイルとして指定することで実現することが可能です。
（まぁ、私の場合はSQLを直接書いた方が圧倒的に早いし、より複雑なものも作れるので便利なのですが、
　5250の画面しか使えない場面があった時のためにこういった機能も使えるように練習中です。）