AS400/IBMi CREATE TABLEで作られるカラム

物理ファイルをDDSで作るのではなく、SQLのCREATE TABLEで作る。

ひたすら物理ファイル畑でやってきた人にとっては、なんとなく怪しい匂いが香る。

なので実験してみることにしました。

作ったファイルは、

DSPFMT ファイル名 *

1	DSPFMT ファイル名 *

でフィールド情報を確認する。

前置き

DSPFMT内で変わったところ

項目は複数あるけど、変化があるところは少しのようです。

空値可能

YならNULLを許す、NならNULLを許さない

フィールド属性

B = ２進データ(INT)
P = パック10進データ(DECIMAL)
S = ゾーン10進データ(NUMERIC)
A = １バイト文字（CHAR）
O = ２バイト文字（VARCHAR)
L = 日付データ
T = 時刻データ
Z = タイムスタンプ(TIMESTAMP)

パックはサイズ小さめ、ゾーンは桁数ぶん容量をキッチリ使う印象。

可変長フィールド

ブランク、N、Yの３つ。

桁数

桁数は桁数

バイト数

バイト数はバイト数

数値型

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系に関して、物理ファイルとテーブルの違いで違和感があったので別記事です。

AS400/IBMi 物理ファイルとテーブル INTEGER系で違和感

SQLを使って物理ファイルではなくテーブルとしてファイルを作る。その中でINT系で違和感があったので試した実験。 INT系なら何でもいいと思うが、今回はSMALLINTでカラムを作り、ここにSQLを使ってINSERT ...

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

８桁＋シフト文字で計算するみたい。

VARCHAR(200)だと桁数もバイト数も202になる。

日付・時刻・タイムスタンプ型

私の職場ではこの型は使ったことがありません。

ただ下記にあるレコードを追加したときに自動でタイムスタンプを取りたいので、今回トライしてみました。

尚、IBMiの先生に、日付はやっぱりDATE型に入れた方がいいのか尋ねたところ、数値の方が扱いやすいと言ってました。

SUBSTRで切ろうとしてもCHARやTO_CHARで文字列に変換しないといけないし、そもそもRPGで値を代入できるのかすら不明。

TIMESTAMP

日付と時刻ですね。桁数の指定は必要ありません。

指定は単なるTIMESTAMPです。

フィールド属性 Z
桁数 26
バイト数 26
可変長フィールドブランク

その他指定

PRIMARY KEY

主キーの指定です。TR1を主キーにしています。

空値可能はNになります。

ちなみに、これは列レベル制約の構文と言います。

CREATE TABLE HOGELIB.HOGEFILE
(
TR1 CHAR(4) PRIMARY KEY,
MAIL VARCHAR(50)
);

CREATE TABLE HOGELIB.HOGEFILE

(

TR1 CHAR(4) PRIMARY KEY,

MAIL VARCHAR(50)

);

PRIMARY KEY（複数の列で）

対して複数の列でPRIMARY KEYを指定には表レベル制約の構文で書く必要があります。

各行にPRIMARY KEYを書くだけだとエラーになります。

以下TR1とTR2をPRIMARY KEYにする場合はこう書きます。

CREATE TABLE HOGELIB.HOGEFILE 
( 
TR1 CHAR(4),
TR2 CHAR(2),
MAIL VARCHAR(50),
PRIMARY KEY(TR1,TR2)
);

CREATE TABLE HOGELIB.HOGEFILE

(

TR1 CHAR(4),

TR2 CHAR(2),

MAIL VARCHAR(50),

PRIMARY KEY(TR1,TR2)

);

NOT NULL

NULL値を許さない指定。

これを付与すると空値可能がNになる。付与しないと空値可能がYになる。

AS400の場合はNULLは厳禁なので必ず全てのカラムにこれを付ける方がよさそう。

もちろんPRIMARY KEYを付与した場合は空値可能はNになる。

指定の仕方はこんな感じ。

CREATE TABLE HOGELIB.HOGEFILE
(
HOGE VARCHAR(100) NOT NULL
);

CREATE TABLE HOGELIB.HOGEFILE

(

HOGE VARCHAR(100) NOT NULL

);

DEFAULT

カラムを指定せずINSERTした場合にデフォルト値として入れたい値があるときに使う。

DEFAULTに後に何を指定するのかは、型に合っていればいいみたい。

NOT NULLと一緒に使うことが多そう。

INFIND TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00.000000',

1	INFIND TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00.000000',

レコード様式名の設定

AS400/IBMi特有のアレです。

指定の仕方はこんな感じ。

CREATE TABLE HOGELIB.HOGEFILE
(
略..
)
RCDFMT HOGERCD;

CREATE TABLE HOGELIB.HOGEFILE

(

略..

)

RCDFMT HOGERCD;

インクリメント（増分）の設定

MySQLなどではよくやる、主にIDに使う連番。

CREATE TABLE HOGELIB.HOGEFILE
(
HOGEID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
);

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は１からスタートされない。

これを再度１から始まるようにリセットする命令。

ALTER TABLE HOGELIB.HOGEFILE ALTER HOGEID RESTART WITH 1;

1	ALTER TABLE HOGELIB.HOGEFILE ALTER HOGEID RESTART WITH 1;

自動で更新日付が入るカラムを作る

いわゆるupdated_atですね。

CREATE TABLE HOGELIB.HOGEFILE
(
INFDAT TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
);

CREATE TABLE HOGELIB.HOGEFILE

(

INFDAT TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

);

自動で作成日付が入るカラムを作る

通常だとできないそうなので、トリガーを使ってやるそうです。

WEBINFOという名前のテーブルで作るとこんな感じ。

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;

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に発火しているからみたい。なので、

INFIND TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00.000000',

1	INFIND TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00.000000',

のようにするといいのかも。

ちなみに作ったトリガーはファイルを消す(DROP)と自動でトリガーも消えました。

さらにUPDDTAで修正をかけてもトリガーはかかりました。

尚PRIMARY KEYを複数行で作った場合は、最後のWHERE文も下記例のようにその主キーと同じ項目ぶんANDで指定してあげればいい。

WHERE TR1 = INSERTED_ROW.TR1 AND TR2 = INSERTED_ROW.TR2;

1	WHERE TR1 = INSERTED_ROW.TR1 AND TR2 = INSERTED_ROW.TR2;

作ったトリガーの一覧を見る方法はこちらの別記事にて。

AS400/IBMi 作ったトリガーの一覧を取得する

トリガーを作ったのはいいけど、どんなトリガーを作ったのか解らなくなり管理できなくなっては困るので、その見方。ちなみに、作ったトリガーはファイルを消す(DROP)すると消えます。実際に消えました。印刷で取得 PRTTRG...

見えない列を作る

見えないと怖いけど、更新日付を裏で持っておくなどの用途かも。

もしかして普段使っているファイルに、あなたの知らないカラムが隠されているかも・・・。

CREATE TABLE HOGELIB.HOGEFILE
(
INFDAT TIMESTAMP NOT NULL IMPLICITLY HIDDENGENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
);

CREATE TABLE HOGELIB.HOGEFILE

(

INFDAT TIMESTAMP NOT NULL IMPLICITLY HIDDENGENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

);