Db2

Db2 の CLP（コマンド・ライン・プロセッサー）モード

Posted on by keeper

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が、自動コミットしない
keeper