DB2 SQL脚本批量执行

转贴自 http://www.cnblogs.com/Isabella/articles/1289760.html

DB2 SQL脚本批量执行

环境：

Windows系统

DB2客户端或者服务端 

一、准备工作

运行db2cmd或者db2cw打开DB2命令行处理器

进入SQL脚本存放目录

用db2 connect to <dbname> user <username> using <password> 命令连接数据库

用db2 set current schema <schema_name> 设置当前的SCHEMA，这个SCHEMA可以是未创建的，DB2会自动创建。 

二、执行不同类型的SQL

1、在命令行执行简单SQL命令

db2 <SQL语句内容> 

2、在命令行执行SQL脚本文件

db2 -td; -cf <SQL脚本文件路径> -l <输出日志文件路径> 

说明：-td; 指定语句结束标志。因为一般SQL都是以分号结尾，所以就写成“-td;”。

      -l <输出日志文件路径>  是可选的。 

3、在命令行执行DB2 存储过程文件

db2 -td@ -f <SQL过程文件路径> -l <输出日志文件路径> 

说明：-td; 指定语句结束标志。因为一般SQL过程都是以@结尾，所以就写成“-td@”。

      -l <输出日志文件路径>  是可选的。 

三、写批处理脚本

批处理脚本无非就是把命令写在一起，保存为bat文件，执行即可。

在写批处理之前，先回顾一下DB2命令选项： 

C:"IBM"SQLLIB"BIN>db2 ? options

db2 [option ...] [db2-command | sql-statement |

    [? [phrase | message | sqlstate | class-code]]]

option：-a、-c、-d、-e{c|s}、-finfile、-i、-lhistfile、-m、-n、-o、

        -p、-q、-rreport、-s、-t、-td;、-v、-w、-x 和 -zoutputfile。

 

 选项    描述                                      缺省设置

 ------  ----------------------------------------  ---------------

   -a    显示 SQLCA                                OFF

   -c    自动落实                                  ON

   -d    检索并显示 XML 声明                       OFF

   -e    显示 SQLCODE/SQLSTATE                     OFF

   -f    读取输入文件                              OFF

   -i    显示 XML 数据并带有缩进                   OFF

   -l    将命令记录到历史记录文件中                OFF

   -m    显示受影响的行数                          OFF

   -n    除去换行字符                              OFF

   -o    显示输出                                  ON

   -p    显示 db2 交互式提示符                     ON

   -q    保留空格和换行符                          OFF

   -r    将输出报告保存到文件                      OFF

   -s    在命令出错时停止执行                      OFF

   -t    设置语句终止字符                          OFF

   -v    回传当前命令                              OFF

   -w    显示 FETCH/SELECT 警告消息                ON

   -x    不打印列标题                              OFF

   -z    将所有输出保存到输出文件                  OFF 

注意：

 使用 DB2OPTIONS 环境变量定制选项缺省值。

 紧跟选项字母后的减号（-）使该选项关闭。

 使用 UPDATE COMMAND OPTIONS 更改选项设置（以交互式或

 文件输入方式）。 

根据命令参数创建批处理文件xxx.bat： 

rem     -- 连接数据库

db2 connect to <dbname> user <username> using <password> 

rem     -- 设置SCHEMA

db2 set current schema <schema_name> 

rem     -- 执行SQL脚本列表

db2 -td; -cf <SQL脚本文件路径> -l <输出日志文件路径>

 

rem     -- 执行SQL过程列表

db2 -td@ -f <SQL过程文件路径> -l <输出日志文件路径> 

注意：<SQL过程文件路径>可以是绝对路径也可以是相对路径。 

四、执行批处理

运行db2cmd或者db2cw打开DB2命令行处理器。

如果<SQL过程文件路径>和<SQL脚本文件路径>为绝对路径，则可以直接命令行执行 xxx 

如果<SQL过程文件路径>和<SQL脚本文件路径>为相对路径，则需要进入适当的目录，一边能在此目录下找到批处理，然后执行 xxx

# # # # # # # # #

DB2 HOW TOs

Start an instance

As an instance owner on the host running db2, issue the following command

$ db2start

Stopping the instance

$ db2stop

Connect to the database as instance owner

$ db2

as a user of the database:

$source ~instance/sqllib/db2cshrc (csh users)

$ . ~instance/sqllib/db2profile (sh users)

$ db2 connect to databasename

Create a table

$ db2-> create table employee

(ID SMALLINT NOT NULL,

NAME VARCHAR(9),

DEPT SMALLINT CHECK (DEPT BETWEEN 10 AND 100),

JOB CHAR(5) CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')),

HIREDATE DATE,

SALARY DECIMAL(7,2),

COMM DECIMAL(7,2),

PRIMARY KEY (ID),

CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) > 1986 OR SALARY > 40500) )

A simple version:

db2-> create table employee ( Empno smallint, Name varchar(30))

Create a schema

If auser has SYSADM or DBADM authority, then the user can create a schemawith any valid name. When a database is created, IMPLICIT_SCHEMAauthority is granted to PUBLIC (that is, to all users). The followingexample creates a schema for an individual user with the authorizationID 'joe'

CREATE SCHEMA joeschma AUTHORIZATION joe

Create an alias

The following SQL statement creates an alias WORKERS for the EMPLOYEE table:

CREATE ALIAS WORKERS FOR EMPLOYEE

You donot require special authority to create an alias, unless the alias isin a schema other than the one owned by your current authorization ID,in which case DBADM authority is required.

Create an Index:

Thephysical storage of rows in a base table is not ordered. When a row isinserted, it is placed in the most convenient storage location that canaccommodate it. When searching for rows of a table that meet aparticular selection condition and the table has no indexes, the entiretable is scanned. An index optimizes data retrieval without performinga lengthy sequential search. The following SQL statement creates a

non-unique index called LNAME from the LASTNAME column on the EMPLOYEE table, sorted in ascending order:

CREATE INDEX LNAME ON EMPLOYEE (LASTNAME ASC)

The following SQL statement creates a unique index on the phone number column:

CREATE UNIQUE INDEX PH ON EMPLOYEE (PHONENO DESC)

Drop a database:

Db2 drop database sample

Alter tablespace

Addinga Container to a DMS Table Space You can increase the size of a DMStable space (that is, one created with the MANAGED BY DATABASE clause)by adding one or more containers to the table

space.The following example illustrates how to add two new device containers(each with 40 000 pages) to a table space on a UNIX-based system:

ALTER TABLESPACE RESOURCE

ADD (DEVICE '/dev/rhd9' 10000,

DEVICE '/dev/rhd10' 10000)

The following SQL statement drops the table space ACCOUNTING:

DROP TABLESPACE ACCOUNTING

You canreuse the containers in an empty table space by dropping the tablespace but you must COMMIT the DROP TABLESPACE command, or have hadAUTOCOMMIT on, before attempting to reuse the containers. The followingSQL statement creates a new temporary table space called TEMPSPACE2:

CREATE TEMPORARY TABLESPACE TEMPSPACE2 MANAGED BY SYSTEM USING ('d')

OnceTEMPSPACE2 is created, you can then drop the original temporary tablespace TEMPSPACE1 with the command: DROP TABLESPACE TEMPSPACE1

Add Columns to an Existing Table

When anew column is added to an existing table, only the table description inthe system catalog is modified, so access time to the table is notaffected immediately. Existing records are not physically altered

untilthey are modified using an UPDATE statement. When retrieving anexisting row from the table, a null or default value is provided forthe new column, depending on how the new column was defined. Columnsthat are added after a table is created cannot be defined as NOT NULL:they must be defined as either NOT NULL WITH DEFAULT or as nullable.Columns can be added with an SQL statement. The following statementuses the ALTER TABLE statement to add three columns to the EMPLOYEEtable:

ALTER TABLE EMPLOYEE

ADD MIDINIT CHAR(1) NOT NULL WITH DEFAULT

ADD HIREDATE DATE

ADD WORKDEPT CHAR(3)

GrantPermissions by Users

The following example grants SELECT privileges on the EMPLOYEE table to the user HERON:

GRANT SELECT ON EMPLOYEE TO USER HERON

The following example grants SELECT privileges on the EMPLOYEE table to the group HERON:

GRANT SELECT ON EMPLOYEE TO GROUP HERON

GRANT SELECT,UPDATE ON TABLE STAFF TO GROUP PERSONNL

If aprivilege has been granted to both a user and a group with the samename, you must specify the GROUP or USER keyword when revoking theprivilege. The following example revokes the SELECT privilege on theEMPLOYEE table from the user HERON:

REVOKE SELECT ON EMPLOYEE FROM USER HERON

To Check what permissions you have within the database

SELECT * FROM SYSCAT.DBAUTH WHERE GRANTEE = USER AND GRANTEETYPE = 'U'

SELECT * FROM SYSCAT.COLAUTH WHERE GRANTOR = USER

At aminimum, you should consider restricting access to the SYSCAT.DBAUTH,SYSCAT.TABAUTH, SYSCAT.PACKAGEAUTH, SYSCAT.INDEXAUTH, SYSCAT.COLAUTH,and SYSCAT.SCHEMAAUTH catalog views. This would prevent information onuser privileges, which could be used to target an authorization namefor break-in, becoming available to everyone with access to thedatabase. The following statement makes the view available to everyauthorization name:

GRANT SELECT ON TABLE MYSELECTS TO PUBLIC

And finally, remember to revoke SELECT privilege on the base table:

REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC

Delete Records from a table

db2-> delete from employee where empno = '001'

db2-> delete from employee

The first example will delete only the records with emplno field = 001 The second example deletes all the records

Import Command

Requiresone of the following options: sysadm, dbadm, control privileges on eachparticipating table or view, insert or select privilege, example:

db2->import from testfile of del insert into workemployee

where testfile contains the following information 1090,Emp1086,96613.57,55,Secretary,8,1983-8-14

or your alternative is from the command line:

db2 " import from 'testfile' of del insert into workemployee"

db2 < test.sql where test.sql contains the following line:

db2 import from test file of del insert into workemployee

Load Command:

Requires the following auithority: sysadm, dbadm, or load authority on the database:

example: db2 "load from 'testfile' of del insert into workemployee"

You may have to specify the full path of testfile in single quotes

Authorization Level:

One of the following:

sysadm

dbadm

load authority on the database and

INSERT privilege on the table when the load utility is invoked in INSERT mode, TERMINATE mode

(to terminate a previous load insert operation), or RESTART mode (to restart a previous load insert

operation)

INSERT and DELETE privilege on the table when the load utility is invoked in REPLACE mode,

TERMINATE mode (to terminate a previous load replace operation), or RESTART mode (to restart a

previous load replace operation)

INSERT privilege on the exception table, if such a table is used as part of the load operation.

Caveat:

If youare performing a load operation and you CTRL-C out of it, thetablespace is left in a load pending state. The only way to get out ofit is to reload the data with a terminate statement

First to view tablestate:

Db2 list tablespaces show detail will display the tablespace is in a load pending state.

Db2tbst <tablespace state>

Here is the original query

Db2 "load from '/usr/seela/a.del' of del insert into A";

If you break out of the load illegally (ctrl-c), the tablespace is left load pending.

To correct:

Db2 "load form '/usr/seela/a.del' of del terminate into A";

This will return the table to it's original state and roll back the entries that you started loading.

If you try to reset the tablespace with quiesce, it will not work . It's an integrety issue

DB2BATCH- command

ReadsSQL statements from either a flat file or standard input, dynamicallyprepares and describes the statements and returns an answer set:Authorization: sysadmin .and Required Connection -None..eg

db2batch -d databasename -f filename -a userid/passwd -r outfile

DB2expln - DB2 SQL Explain Tool

Describesthe access plan selection for static SQL statements in packages thatare stored in the DB2 common server systems catalog. Given the databasename, package name ,package creator abd section

number the tool interprets and describes the information in these catalogs.

DB2exfmt - Explain Table Format Tool

DB2icrt - Create an instance

DB2idrop - Dropan instance

DB2ilist - List instances

DB2imigr - Migrate instances

DB2iupdt - Update instances

Db2licm - Installs licenses file for product ;

db2licm -a db2entr.lic

DB2look - DB2 Statistics Extraction Tool

Generatesthe updates statements required to make the catalog statistics of atest database match those of a production. It is advantageous to have atest system contain asubset of your production system's data.

Thistool queries the system catalogs of a database and outputs a tablespacen table index, and column information about each table in that databaseAuthorization: Select privelege on system catalogs Required

Connection - None. Syntax

db2look -d databasename -u creator -t Tname -s -g -a -p -o

Fname -e -m -c -r -h

where-s : generate a postscript file, -g a graph , -a for all users in thedatabase, -t limits output to a particular tablename, -p plain textformat , -m runs program in mimic mode, examples:

db2look -d db2res -o output will write stats for tables created in db

db2res in latex format

db2look -p -a -d db2res -o output - will write stats in plain text format

DB2 -list tablespaces show detail

displays the following information as an example:

Tablespaces for Current Database

Tablespace ID = 0

Name = SYSCATSPACE

Type = System managed space

Contents = Any data

State = 0x0000

Detailed explanation:

Normal

Total pages = 2925

Useable pages = 2925

Used pages = 2925

Free pages = Not applicable

High water mark (pages) = Not applicable

Page size (bytes) = 4096

Extent size (pages) = 32

Prefetch size (pages) = 32

Number of containers = 1

db2tbst - Get tablespace state.

Authorization- none , Required connection none, syntax db2tbst tabpespace-state:Thestate value is part of the output of list tablespaces example

db2tbst 0X0000 returns state normal

db2tbst 2 where 2 indicates tablespace id 2 will also work

DB2dbdft - environment variable

Definingthis environment variable with the database you want to connect toautomatically connects you to the database . example setenv db2dbdftsample will allow you to connect to sample by default.

CLP - Command Line Processor Invocation:

db2starts the command line processor. The clp is used to execute databaseutilities, sql statements and online help. It offers a variety ofcommand options and can be started in :

1. interactive mode : db2->

2. command mode where each command is prefixed by db2

3. batch mode which uses the -f file input option

Update the configuration in the database :

Db2 =>update db cfg for sample using maxappls 60

MAXFILOP = 64 2 - 9150

db2 => update db cfg for sample using maxappls 160

db2 => update db cfg for sample using AVG_APPLS 4

db2 =>update db cfg for sample using MAXFILOP 256

can see updated parameters from client

tcpip ..... not started up properly Check the DB2COMM variable if it it is set

db2set DB2COMM <enter>

How to terminate the database if processes are still attached:

db2 force applications all

db2stop

db2start

db2 connect to dbname (locally)

How to trace logs withing the db2diag.log file:

Connections to db fails:

Movethe db2diag.log from the sqllib/db2dump directory to some other workingdirectory ( mv db2diag.log <some other working directory )

db2 update dbm cfg using diaglevel 4

db2stop

db2start

db2trc on -l 8000000 -e 10

db2 connect to dbname (locally)

db2trc dump 01876.trc

db2trc flw 01876.trc 01876.flw

db2trc fmt 01876.trc 01876.fmt

db2trc off

Import data from ascii file to database

db2 " import from inp.data of del insert into test"

db2 "load from '/cs/home/tech1/seela/inp.data' of del insert into seela.seela"

db2 < test.sql

Revoke permissions from the database from public:

db2 => create database GO3421

DB20000I The CREATE DATABASE command completed successfully.

Now I want to revoke connect, createtab bindadd on database from public

On server: db2 => revoke connect , createtab, bindadd on database from public

Now on client, as techstu, I tried to connect to go3421

db2 => connect to go3421

SQL1060N User "TECHSTU " does not have the CONNECT privilege. SQLSTATE=08004

Now I have to grant connect privilege to group ugrad

On server:

db2 => grant connect, createtab on database to group ugrad

DB20000I The SQL command completed successfully.

Tested on client I can connect successfully.

Now on the client, I can connect as a student, list tables but not select. I

can still describe tables

To prevent this:

On server

revoke select on table syscat.columns from public

Now on client, I cannot describe but also on my tables.

db2 => revoke select on table syscat.columns from public

DB20000I The SQL command completed successfully.

db2 => grant select on table syscat.columns to group ugrad

On server:

db2 => revoke select on table syscat.indexes from public

DB20000I The SQL command completed successfully.

select * from syscat.dbauth will display all the privileges for

dbadm authority:

DBADMAUTH CREATETABAUTH BINDADDAUTH CONNECTAUTH

NOFENCEAUTH IMPLSCHEMAAUTH LOAD AUTH

select

TABNAME,DELETEAUTH,INSERTAUTH,SELECTAUTH from

syscat.tabauth

grant connect, createtab

grant connect, createtab on database to user techstu

to group ugrad

Instance Level Authority

db2 get dbm cfg

db2 get admin cfg

db2 get db cfg

CLP using filename on the command line

Db2 -f filename.clp

The -f option directs the clp to accept input from file.

Db2 +c -v +t infile .. The option can be prefixed by a + sign or turned on by a letter with a -sign

+c is turned off, -v turned on and -f turned on

c is for commit, v for verbose and f for filename

-t termination character is set to semicolon