Scott/Tiger を DB2/400 で

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions」という本を読んでたら、Oracle と言えばの Scott データベースの作り方が載っていたので、おもわず DB2/400 にポーティングを試みてしまいました。

本の内容についてはこちらに紹介があります。
また、スクリプトのダウンロードはこちらからになってます。


emp/dept テーブル作成用スクリプト (Oracle 用)

とりあえず、これが名高い emp テーブルと dept テーブルの作成スクリプトになっています。(もちろん Oracle 用)

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7, 2),
 COMM NUMBER(7, 2),
 DEPTNO NUMBER(2)
);

INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
 DNAME VARCHAR2(14),
 LOC VARCHAR2(13)
);

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

alter table emp add constraint emp_pk primary key(empno);
alter table dept add constraint dept_pk primary key(deptno);
alter table emp add constraint emp_fk_dept 
                                 foreign key(deptno) references dept;
alter table emp add constraint emp_fk_emp foreign key(mgr) references emp;

修正点

結局、以下の三点を直しました。

まず、NUMBER について。これは DECIMAL でも SMALLINT でもよかったのかもしれませんが、置き換えるのに元の文字をかなり使える、という観点だけから NUMERIC にしています。このあたり、何か調べてわかったら追記しようと思っています。

NUMBER(4) → NUMERIC(4)

VARCHAR はいいでしょう。

VARCHAR2 → VARCHAR

TO_DATE 関数については、ちょっと大きい修正になります。

TO_DATE('17-DEC-1980', 'DD-MON-YYYY') → '1980-12-17'

DATE で指定されている HIREDATE カラムについては二つの修正点があります。

まず、TO_DATE 関数については Oracle 固有のもので SQL 標準にはありません。
たとえば、「知っておきたいデータベース移行術「Oracle→DB2編」」といったサイトに、この Oracle の TO_DATE 関数は「置き換えできない、置き換えが難しい関数」として載っています。

"なお、DB2 V8にはTIMESTAMP型を文字列に変換するTO_CHAR関数、文字列をTIMESTAMP型に変換するTO_DATE関数があります。フォーマット形式は、「YYYY-MM-DD HH24:MM:SS」のみです。多様な変換形式を持つOracleの同名の関数とDB2 V8の関数を1:1で置き換えるのは困難なことです。移行の必要がある場合は、UDFを作成して対応してください。"

また、そもそも DATE というデータ型については、インフォメーション・センターに

"データ・タイプが DATE (日付)、TIME (時刻)、または TIMESTAMP (タイム・スタンプ) である値は内部形式で表されますが、SQL ユーザーは、この内部形式を意識する必要はありません。"

と書いてあり、そもそも、一定の型にはめてデータを格納しなくてはいけないようなものではありません。

つまり、DATE というデータ・タイプであれば、'DD-MON-YYYY' とか 'YYYY-MM-DD' とかいった個別の日付形式ではなく、取り出すときに形式をあわせればいいような一種共通な内部形式で格納されている、ということです。
要するに、INSERT でも SELECT でもその時に必要な形式で取り出せばいいわけです。

日付の形式については、以下さらにインフォメーション・センターからの記述を引用します。

"結果のストリングの形式は、ステートメントを作成するときに効力を持っているデフォルトの日付形式およびデフォルトの時刻形式によって異なります。デフォルトの日付および時刻の形式は、日付形式 (DATFMT)、日付区切り文字 (DATSEP)、時刻形式 (TIMFMT)、および時刻区切り文字 (TIMSEP) パラメーターに基づいて設定されます。"
"データベース・マネージャーは、次のいずれかの形式のストリングを日付として認識します。

表 5. 日付のストリング表現で使用する形式
形式の名前 省略形 日付形式
ANSI/ISO SQL 標準日付形式 (-) - DATE 'yyyy-mm-dd' DATE '1987-10-12'
国際標準化機構 (*ISO) ISO 'yyyy-mm-dd' '1987-10-12'
IBM USA 標準 (*USA) 'mm/dd/yyyy' '10/12/1987'
IBM 欧州標準 (*EUR) EUR 'dd.mm.yyyy' '12.10.1987'
日本工業規格の西暦 (*JIS) JIS 'yyyy-mm-dd' '1987-10-12'
不定様式の年間通算日 - 'yyyyddd' '1987285'
年間通算日形式 (*JUL) - 'yy/ddd' '87/285'
月、日、年 (*MDY) - 'mm/dd/yy' '10/12/87'
日、月、年 (*DMY) - 'dd/mm/yy' '12/10/87'
年、月、日 (*YMD) - 'yy/mm/dd' '87/12/10'

"

こちらもインフォメーション・センターからです。

"デフォルトの日付形式は、以下のインターフェースを使用して指定できます。
表 6. デフォルト日付形式インターフェース
SQL インターフェース 指定
組み込み SQL DATFMT および DATSEP パラメーターは、SQL プログラム作成 (CRTSQLxxx) コマンドに指定することができます。 SQL を組み込むプログラム・ソースに DATFMT および DATSEP パラメーターを指定するためには、SET OPTION ステートメントを使用することもできます。

(CRTSQLxxx コマンドの詳細については、「組み込み SQL プログラミング」を参照。)
対話式 SQL および SQL 実行ステートメント SQL 開始 (STRSQL) コマンドで DATFMT および DATSEP パラメーターを指定するか、セッション属性を変更します。あるいは、SQL 実行 (RUNSQLSTM) ステートメントで DATFMT および DATSEP パラメーターを使用します。

(STRSQL コマンドと RUNSQLSTM コマンドの詳細については、「SQL プログラミング」を参照。)
サーバー上の呼び出しレベル・インターフェース (CLI) SQL_ATTR_DATE_FMT および SQL_ATTR_DATE_SEP 環境変数または接続変数。

(CLI の詳細については、「DB2 UDB for iSeries SQL 呼び出しレベル・インターフェース」を参照。)
IBM Developer Kit for Java を使用したサーバーの JDBC または SQLJ 「日付形式 (Date Format)」および「日付区切り記号 (Date Separator)」接続プロパティー。

(JDBC および SQLJ の詳細については、iSeries Information Center の IBM Developer Kit for Java トピックを参照。)
iSeries Access Family ODBC ドライバーを使用したクライアントの ODBC ODBC セットアップでの「アドバンスト・サーバー・オプション (Advanced Server Options)」の中の「日付形式 (Date Format)」および「日付区切り記号 (Date Separator)」。

(ODBC の詳細については、iSeries Information Center の iSeries? Access カテゴリーを参照。)
IBM Toolbox for Java を使用したクライアントの JDBC JDBC セットアップの中の「形式 (Format)」。

(ODBC の詳細については、iSeries Information Center の iSeries Access カテゴリーを参照。)

(IBM Toolbox for Java の詳細については、iSeries Information Center の IBM Toolbox for Java トピックを参照。)

"

emp/dept テーブル作成用スクリプト (DB2/400 用)

こちらは、SCOTT というスキーマを作成するところから始めた、DB2/400 上に実際に作成するのに使ったスクリプトです。
iSeries ナビゲーターの「SQL スクリプトの実行」から実行できます。

create schema scott;

set schema scott;

CREATE TABLE EMP
(EMPNO NUMeRic(4) NOT NULL,
 ENAME VARCHAR(10),
 JOB VARCHAR(9),
 MGR NUMERic(4),
 HIREDATE DATE,
 SAL NUMERic(7, 2),
 COMM NUMERic(7, 2),
 DEPTNO NUMERic(2)
);

INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902,
'1980-12-17',  800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698,
'1981-2-20', 1600,  300, 30);
INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698,
'1981-2-22', 1250,  500, 30);
INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839,
'1981-4-2',  2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698,
'1981-9-28', 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839,
'1981-5-1',  2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839,
'1981-6-9',  2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566,
'1982-12-9', 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL,
'1981-11-17', 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698,
'1981-9-8',  1500,    0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788,
'1983-1-12', 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698,
'1981-12-3',   950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566,
'1981-12-3',  3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782,
'1982-1-23', 1300, NULL, 10);

CREATE TABLE DEPT
(DEPTNO NUMERic(2),
 DNAME VARCHAR(14),
 LOC VARCHAR(13)
);

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

select * from dept;

alter table emp add constraint emp_pk primary key(empno);
alter table dept add constraint dept_pk primary key(deptno);
alter table emp add constraint emp_fk_dept 
                                 foreign key(deptno) references dept;
alter table emp add constraint emp_fk_emp foreign key(mgr) references emp;

実行

実行時のログはこんなかんじでした。
ちなみに使用した i5/OS は V5R4 です。

> create schema scott

ステートメントは正常に実行されました。 (24578 ms)

> set schema scott

ステートメントは正常に実行されました。 (609 ms)

> CREATE TABLE EMP (EMPNO NUMeRic(4) NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR NUMERic(4), HIREDATE DATE, SAL NUMERic(7, 2), COMM NUMERic(7, 2), DEPTNO NUMERic(2) )

ステートメントは正常に実行されました。 (1703 ms)

> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (2172 ms)

> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-2-20', 1600, 300, 30)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (797 ms)

> INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (578 ms)

> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, NULL, 20)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (625 ms)

> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (687 ms)

> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (469 ms)

> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (719 ms)

> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-9', 3000, NULL, 20)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (766 ms)

> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (625 ms)

> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (641 ms)

> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983-1-12', 1100, NULL, 20)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (1093 ms)

> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (594 ms)

> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (703 ms)

> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10)

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (656 ms)

> CREATE TABLE DEPT (DEPTNO NUMERic(2), DNAME VARCHAR(14), LOC VARCHAR(13) )

ステートメントは正常に実行されました。 (1703 ms)

> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK')

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (656 ms)

> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS')

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (500 ms)

> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO')

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (640 ms)

> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON')

このステートメントによって 1 行に影響がありました

ステートメントは正常に実行されました。 (625 ms)

> alter table emp add constraint emp_pk primary key(empno)

ステートメントは正常に実行されました。 (2359 ms)

> alter table dept add constraint dept_pk primary key(deptno)

ステートメントは正常に実行されました。 (2328 ms)

> alter table emp add constraint emp_fk_dept foreign key(deptno) references dept

ステートメントは正常に実行されました。 (1687 ms)

> alter table emp add constraint emp_fk_emp foreign key(mgr) references emp

ステートメントは正常に実行されました。 (1422 ms)

ちゃんとできてます。

もう一回やり直すような時は、そのやり直すポイントによりますが、以下の SQL 文を使って制約をはずす必要がある場合があります。ご参考までに。

alter table emp drop constraint emp_pk ;
alter table dept drop constraint dept_pk;

その他

ちなみに「Cost-based Oracle Fundamentals」という本も面白そうです。オプティマイザーについての本ですね。

[Top Pageに戻る]

Ads by TOK2