Technotes

ファイルにSQLを記載し、実行する（バッチモード）

ファイル作成

[db2inst1@localhost ~]$ vi file1.sql

-- テーブルが存在する場合はdrop
drop table if exists test;

-- create table
create table test (id int not null primary key,
                   col1 varchar(100));
-- insert
insert into test values (1, '1回目');
insert into test values (2, '2回目');

-- select
select * from test;

表記方法

• 改行はOK
• SQL文の最後はセミコロン「;」
• コメントはハイフン2つ「–」で始める

ファイル実行

[db2inst1@localhost ~]$ db2 -tvf file1.sql
drop table if exists test
DB20000I  The SQL command completed successfully.

create table test (id int not null primary key, col1 varchar(100))
DB20000I  The SQL command completed successfully.

insert into test values (1, '1回目')
DB20000I  The SQL command completed successfully.

insert into test values (2, '2回目')
DB20000I  The SQL command completed successfully.

select * from test

ID          COL1                                                                                                
----------- -----------------------------------------------------------
          1 1回目                                                                                               
          2 2回目                                                                                               

  2 record(s) selected.

コマンド説明

• -t: 終端文字の指定。デフォルトはセミコロン「;」
• -v: 入力コマンドを標準出力にエコーする
• -f: 入力ファイルを指定

CLP対話モード

CLP環境を起動して、CLPのプロンプト（行頭にdb2 =>）が出た状態でコマンドを実行する。

[db2inst1@localhost ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 11.5.9.0

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => 

対話モードでのDB接続

db2 => connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.9.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2 => 

対話モードでのOSコマンド実行

db2 => !date
Thu Aug 15 03:56:14 JST 2024
db2 => 

対話モードでの複数行入力

改行したい箇所に「\」を入れる。

db2 => select * from \
db2 (cont.) => test

ID          COL1                                                                                                
----------- ---------------------------------------------------
          1 １行目                                                                                               
          2 ２行目                                                                                              

  2 record(s) selected.

db2 => 

対話モードの終了

db2 => quit
DB20000I  The QUIT command completed successfully.

CLPコマンドモード

OSコマンドラインから、db2コマンドを先頭につけて実行する。

[db2inst1@localhost ~]$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.9.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

[db2inst1@localhost ~]$ db2 "create table test (id int not null primary key, col1 varchar(100))"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 +c "insert into test values (1, '1行目')"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 commit
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "insert into test values (2, '２行目')"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 +c "insert into test values (3, '３行目')"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 rollback
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "select * from test"

ID          COL1                                                                                                
----------- -------------------------------------------------
          1 1行目                                                                                               
          2 ２行目                                                                                              

  2 record(s) selected.

[db2inst1@localhost ~]$ db2 "update test set col1='１行目' where id=1"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "select * from test"

ID          COL1                                                                                                
----------- ---------------------------------------------------
          1 １行目                                                                                              
          2 ２行目                                                                                              

  2 record(s) selected.

CLPモードでのコミット

• CLPはデフォルトで自動コミットモード（実行したSQLは即座にコミットされる）
• 複数のSQLを実行し、まとめてコミットしたい場合は、コミットしたくないSQLの前に、+cのオプションをつける
• -cが、自動コミット
• +cが、自動コミットしない

停止

[db2inst1@localhost ~]$ db2 terminate
DB20000I  The TERMINATE command completed successfully.
[db2inst1@localhost ~]$ db2 deactivate db sample
DB20000I  The DEACTIVATE DATABASE command completed successfully.
[db2inst1@localhost ~]$ db2stop
08/07/2024 16:36:03     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

起動

[db2inst1@localhost ~]$ db2start
08/07/2024 16:36:53     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

接続

[db2inst1@localhost ~]$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.9.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

SQL実行

[db2inst1@localhost ~]$ db2 "select count(*) from customer"

1          
-----------
          6

  1 record(s) selected.