物理ファイルをDDSで作るのではなく、SQLのCREATE TABLEで作る。
ひたすら物理ファイル畑でやってきた人にとっては、なんとなく怪しい匂いが香る。
なので実験してみることにしました。
作ったファイルは、
1 |
DSPFMT ファイル名 * |
でフィールド情報を確認する。
前置き
DSPFMT内で変わったところ
項目は複数あるけど、変化があるところは少しのようです。
空値可能
YならNULLを許す、NならNULLを許さない
フィールド属性
B = 2進データ(INT)
P = パック10進データ(DECIMAL)
S = ゾーン10進データ(NUMERIC)
A = 1バイト文字(CHAR)
O = 2バイト文字(VARCHAR)
L = 日付データ
T = 時刻データ
Z = タイムスタンプ(TIMESTAMP)
パックはサイズ小さめ、ゾーンは桁数ぶん容量をキッチリ使う印象。
可変長フィールド
ブランク、N、Yの3つ。
桁数
桁数は桁数
バイト数
バイト数はバイト数
数値型
INT
整数、INTEGER(インテジャー)長精度整数。
指定はINTもしくはINTEGERのみ。INT(50)のように桁数は指定できない。
なぜなら値が-2147483648から+2147483647と決まっているから。
INTで指定
- フィールド属性 B
- 桁数 9
- バイト数 4
- 可変長フィールド ブランク
SMALLINT
短精度整数。SMALLのINT。
指定はSMALLINTのみ。SMALLINT(50)のように桁数は指定できない。
値の幅が-32768から+32767と決まっているから。
INTで指定
- フィールド属性 B
- 桁数 4
- バイト数 2
- 可変長フィールド ブランク
BIGINT
64ビット整数。
指定はBIGINTのみ。BIGINT(50)のように桁数は指定できない。
INTやSMALLINTと同様に値の幅が決まっているから。
INTで指定
- フィールド属性 B
- 桁数 18
- バイト数 8
- 可変長フィールド ブランク
INT系に関して、物理ファイルとテーブルの違いで違和感があったので別記事です。
DEC
小数、DECIMAL(デシマル)
書き方指定は、DECIMAL(桁数,小数点数)もしくはDECIMAL(桁数,小数点数)
DECIMALはDECと省略して書いてもよい。
DECだけでもよいが、その場合は桁数とバイト数は自動割り当て。
フィールド属性はパックのPになる。ちなみにNUMERICはゾーンのS。
ただしIBMのサイトには整数にDECIMALを使ってはいけないと書かれている。
DECだけで指定
- フィールド属性 P
- 桁数 5
- バイト数 3
- 可変長フィールド ブランク
- 少数以下の桁数 0
DEC(8)で指定
- フィールド属性 P
- 桁数 8
- バイト数 5
- 可変長フィールド ブランク
- 少数以下の桁数 0
DEC(8,1)で指定
- フィールド属性 P
- 桁数 8
- バイト数 5
- 可変長フィールド ブランク
- 少数以下の桁数 1
DEC(8,2)で指定
- フィールド属性 P
- 桁数 8
- バイト数 5
- 可変長フィールド ブランク
- 少数以下の桁数 2
NUMERIC
IBMのマニュアルにはDECIMALまたはNUMERICと書いてあり同じような扱い。
でもNUMERICで指定した場合はSのゾーンになるみたい。
ちなみにDECIMALはPで、INTEGERはB。
指定はNUMERICもしくはNUMERIC(桁数)。略した書き方はなし。
NUMERICだけで指定
- フィールド属性 S
- 桁数 5
- バイト数 5
- 可変長フィールド ブランク
- 少数以下の桁数 0
NUMERIC(10)で指定
- フィールド属性 S
- 桁数 10
- バイト数 10
- 可変長フィールド ブランク
- 少数以下の桁数 0
文字列型
CHAR
文字列、CHARACTER(キャラクター)
キャラと呼ぶが、チャーと呼ぶ人もいる。
指定はCHAR(桁数)、CHARだけでもよい。
CHARは固定長フィールド。可変長はVARCHAR。
CHARだけで指定
- フィールド属性 A
- 桁数 1
- バイト数 1
- 可変長フィールド N
CHAR(8)で指定
- フィールド属性 O
- 桁数 8
- バイト数 8
- 可変長フィールド N
VARCHAR
文字列、VARIABLE CHARACTER(バーキャラ)
定められていない文字列という意味。
指定はVARCHAR(桁数)のみ。VARCHARだけの指定は不可。
VARCHARは可変長フィールド。固定長はCHAR。
VARCHAR(8)で指定
- フィールド属性 O
- 桁数 10
- バイト数 10
- 可変長フィールド Y
8桁+シフト文字で計算するみたい。
VARCHAR(200)だと桁数もバイト数も202になる。
日付・時刻・タイムスタンプ型
私の職場ではこの型は使ったことがありません。
ただ下記にあるレコードを追加したときに自動でタイムスタンプを取りたいので、今回トライしてみました。
尚、IBMiの先生に、日付はやっぱりDATE型に入れた方がいいのか尋ねたところ、数値の方が扱いやすいと言ってました。
SUBSTRで切ろうとしてもCHARやTO_CHARで文字列に変換しないといけないし、そもそもRPGで値を代入できるのかすら不明。
TIMESTAMP
日付と時刻ですね。桁数の指定は必要ありません。
指定は単なるTIMESTAMPです。
- フィールド属性 Z
- 桁数 26
- バイト数 26
- 可変長フィールド ブランク
その他指定
PRIMARY KEY
主キーの指定ですね。空値可能はNになります。
指定の仕方はこんな感じ
1 2 3 4 |
CREATE TABLE HOGELIB.HOGEFILE ( ID INT PRIMARY KEY ); |
NOT NULL
NULL値を許さない指定。
これを付与すると空値可能がNになる。付与しないと空値可能がYになる。
AS400の場合はNULLは厳禁なので必ず全てのカラムにこれを付ける方がよさそう。
もちろんPRIMARY KEYを付与した場合は空値可能はNになる。
指定の仕方はこんな感じ。
1 2 3 4 |
CREATE TABLE HOGELIB.HOGEFILE ( HOGE VARCHAR(100) NOT NULL ); |
DEFAULT
カラムを指定せずINSERTした場合にデフォルト値として入れたい値があるときに使う。
DEFAULTに後に何を指定するのかは、型に合っていればいいみたい。
NOT NULLと一緒に使うことが多そう。
1 |
INFIND TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00.000000', |
レコード様式名の設定
AS400/IBMi特有のアレです。
指定の仕方はこんな感じ。
1 2 3 4 5 |
CREATE TABLE HOGELIB.HOGEFILE ( 略.. ) RCDFMT HOGERCD; |
インクリメント(増分)の設定
MySQLなどではよくやる、主にIDに使う連番。
1 2 3 4 |
CREATE TABLE HOGELIB.HOGEFILE ( HOGEID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) ); |
ID連番ならPRIMARY KEYにすると思うが、PRIMARY KEY指定は無くても通る。
自動連番なので空値可能は必ずNになる。
インクリメント(増分)の初期化
上記を使ってID連番をセットすると、DELETEを使って全レコードクリアして、心機一転再スタートしようと思っても、IDは1からスタートされない。
これを再度1から始まるようにリセットする命令。
1 |
ALTER TABLE HOGELIB.HOGEFILE ALTER HOGEID RESTART WITH 1; |
自動で更新日付が入るカラムを作る
いわゆるupdated_atですね。
1 2 3 4 |
CREATE TABLE HOGELIB.HOGEFILE ( INFDAT TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP ); |
自動で作成日付が入るカラムを作る
通常だとできないそうなので、トリガーを使ってやるそうです。
WEBINFOという名前のテーブルで作るとこんな感じ。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE HOGELIB.WEBINFO ( INFID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), INFMSG VARCHAR(100) NOT NULL, INFIND TIMESTAMP, INFUPD TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP ); -- CREATE TRIGGER HOGELIB.TRIGGER_WEBINFO AFTER INSERT ON HOGELIB.WEBINFO REFERENCING NEW INSERTED_ROW FOR EACH ROW UPDATE HOGELIB.WEBINFO SET INFIND = INSERTED_ROW.INFUPD WHERE INFID = INSERTED_ROW.INFID; |
前述したとおりAS400のカラムは全てNOT NULLの方がいいのかもと思って、NOT NULLにすると、INSERT時に何も入れないとエラーで落ちる。
これはトリガーがINSERTのAFTERに発火しているからみたい。なので、
1 |
INFIND TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00.000000', |
のようにするといいのかも。
ちなみに作ったトリガーはファイルを消す(DROP)と自動でトリガーも消えました。
さらにUPDDTAで修正をかけてもトリガーはかかりました。
作ったトリガーの一覧を見る方法はこちらの別記事にて。
見えない列を作る
見えないと怖いけど、更新日付を裏で持っておくなどの用途かも。
もしかして普段使っているファイルに、あなたの知らないカラムが隠されているかも・・・。
1 2 3 4 |
CREATE TABLE HOGELIB.HOGEFILE ( INFDAT TIMESTAMP NOT NULL IMPLICITLY HIDDENGENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP ); |