DB2 System Command Explanation
1 db2admin DB2 Administration Server
2 db2adutl Work with TSM Archived Images
3 db2advis DB2 Index Advisor
4 db2audit Audit Facility Administrator Tool
5 db2atld Autoload
6 db2batch Benchmark Tool
7 db2bfd Bind File Description Tool
8 db2cap CLI/ODBC Static Package Binding Tool
9 db2cc Start Control Center
10 db2cdbcr Create Control Database for Data Warehouse
11 db2cfexp Connectivity Configuration Export Tool
12 db2cfimp Connectivity Configuration Import Tool
13 db2cidmg Remote Database Migration Tool
14 db2ckbkp Check Backup
15 db2ckmig Database Pre-migration Tool
16 db2ckrst Check Incremental Restore Image Sequence
17 db2cli DB2 Interactive CLI
18 db2cmd Open DB2 Command Window
19 db2dclgn Declaration Generator
20 db2drdat DRDA Trace
21 db2empfa Enable Multi-page File Allocation
22 db2eva Event Analyzer
23 db2evmon Event Monitor Productivity Tool
24 db2exfmt Explain Table Format Tool
25 db2expln DB2 SQL Explain Tool
26 db2flsn Find Log Sequence Number
27 db2fs First Steps
28 db2gncol Update Generated Column Values
29 db2gov DB2 Governor
30 db2govlg DB2 Governor Log Query
31 db2icrt Create Instance
32 db2idrop Remove Instance
33 db2ilist List Instances
34 db2imigr Migrate Instance
35 db2inidb Initialize a Mirrored Database
36 db2ipxad Get IPX/SPX Internetwork Address
37 db2iupdt Update Instances
38 db2ldcfg Configure LDAP Environment
39 db2licm License Management Tool
40 db2look DB2 Statistics and DDL Extraction Tool
41 db2move Database Movement Tool
42 db2mscs Set up Windows NT Failover Utility
43 db2nchg Change Database Partition Server Configuration
44 db2ncrt Add Database Partition Server to an Instance
45 db2ndrop Drop Database Partition Server from an Instance
46 db2perfc Reset Database Performance Values
47 db2perfi Performance Counters Registration Utility
48 db2perfr Performance Monitor Registration Tool
49 db2profc DB2 SQLJ Profile Customizer
50 db2profp DB2 SQLJ Profile Printer
51 db2rbind Rebind all Packages
52 Db2relocatedb Relocate Database
53 db2sampl Create Sample Database
54 db2set DB2 Profile Registry Command
55 db2sql92 SQL92 Compliant SQL Statement Processor
56 db2start Start DB2
57 db2stop Stop DB2
58 db2support Problem Analysis and Environment Collection Tool
59 db2sync Start DB2 Synchronizer
60 db2tbst Get Tablespace State
61 db2trc Trace
62 db2uiddl Prepare Unique Index Conversion to V5 Semantics
63 db2untag Release Container Tag
db2level 查看DB2的版本号
Installation
Program Temporary Fix (PTF), use db2iupdt to update all db2 instances.
use dasiupdt to update Administration Server.
db2iupdt -u db2inst1 db2inst1
Post Installation Environmental Checking
db2icrt Create instance
db2ilist List instance
db2idrop Drop instance
set db2instance set which instance applies to the current session
System -> Instance -> Database -> Nodegroup -> Table Space -> Table, Index, Log data
Node -> Database Manager (Inst1) -> Database1 -> TableSpace A -> Table1
Environment Variables session env
Instance-Level profile registry
Global-Level profile registry
db2set
update dbm cfg for dbname using config_keywork=value
Administrative Server:
dasicrt ASName (Unix) dasidrop ASName (Unix)
db2admin create (intel) db2admin drop (intel)
db2admin start
db2admin stop
db2set -g DB2ADMINSERVER
db2stop To stop the instance
Create Database
Create Database db_name on path Allias allias Using codeset codeset
Collate Using System/Identity Catalog TableSpace..User TableSpace
Temporary Tablespace .. With "comment string"
Managed by System Using ‘container string’/ Database Using ..
SMS: OS Directory usually used for system tablespaces Grows/Shinks on demand Ease of Admin
DMS: Database Manager File or Device User tablespaces Preallocated Best performance
DMS: able to distribute one table across multiple table spaces
DMS: table space data types: Regular, temporary and long
1 extent = 32 pages
DFT_Extent_SZ defined at db level; Extentsize defined at table space level
Create Table Space
Create Regular/Long/[System/user]Temporary Tablespace PageSize 4096(k) Managed by System…/Database
Extentsize k/m/g Prefetchsize k/mg Bufferpool overhead Transferrate
Unix:
Create tablespace enterprise managed by system using (‘/database/firstcontain’, ‘/database/secondcontain’, ‘/database/thirdcontain’) prefetchsize 32
Create tablespace humanres managed by database using (device ‘/dev/rdata1′ 1024, device ‘/dev/rdata2′ 1024) extentsize 8 prefetchsize 16
Intel:
Create tablespace enterprise managed by system using (‘e:\database\firstcontain’, ‘e:\database\secondcontain’, ‘e:\database\thirdcontain’) prefetchsize 32
create long tablespace longtbs managed by database using (device ‘\\.\physicaldrive1′ 1024) extentsize 32 prefetchsize 16
create tablespace indextbs managed by database using (file ‘c:\database\index.tbs’ 1000) prefetchsize 32
db2 list tablespaces
db2 list tablespaces show detail
db2 list tablespace containers for X
db2 list tablespace containers for x show detail
alter tablespace add db_container_clause / Extend /Resize database_container_clause /refetchsize /Bufferpool /Overhead .. /Transferrate
select tbspace tbspaceid,definer, tbspacetype, datatype, extentsize from syscat.tablespaces order by 1, 2, 3
Create table
Create table emp_photo in humanres index in indextbs long in longtbs
select tabname, tableid, tbspaceid, tabschema, type from syscat.tables order by 1 , 2, 3
create table artists
(artno smallint not null,
name varchar(50) with default ‘abc’,
classification char(1) not null,
bio clob(100k) logged,
picture blob(10M0 not logged compact)
in dms 01
index in dms02
long in dms03;
create table t1 (c1 char(10), c2 smallint)
data capture None/Changes changes indicate extra info to be logged.F93
Create Bufferpool
Sysctrl or SysAdm authority
Create Bufferpool bp_index size 125
alter tablespace indextbs bufferpool bp_index
Change Directory Entries
Uncatalog database ourdb
Catalog database .. As alias on drive / At node
list database directory
Connect to Database
connect to connect reset terminate
db2dbdft
Create schema
create schema Payroll authorization Keith
connect to musicdb user keith
select * from employee
set current schema = ‘Payroll’
select * from employee
Large Object
blob clob dbclob
Identity
Create table Mytab
(col1 int not null generated always as identity (start with 100, increment by 5),
col2 double,
col3 int);
insert into mytab(col2, col3) values (5.6, 3);
values (identity_val_local()) /* Return Identity value */
Declare Temporary tables
declare global temporary table t1
like real_t1
on commit delete rows
not logged
in usr_temp_ts;
insert into session.t1
select * from real_t1 where deptno=: mydept;
table drops when connection ends.
Create Index
create unique index itemno on albums (itemno)
create index item on stock (itemno) cluster
create unique index empidx on employee (empno) include (lastname, firstname)
Create View
connect to testdb
create view empsalary
as select empno, empname, salary
from payroll, personnel
where empno = empnumb and salary > 30000.00
Create alias
create alias singers for artists
Foreign Key
create table department (dept char(3) not null, deptname char(20) not null, constraint dept_name unique (deptname), primary key(dept)) in dms99
create table Employee (empno char(6) not null, name char(30), wkdept char(3) not null, constraint dept foreign key (wkdept) references department on delete restrict) in dms99
Unique Key Considerations
Create table Pay.Emptab
(empno smallint not null primary key,
name char(20),
driverlic char(17) not null,
constraint driv_uniq unique(driverLic))
in tbsp1
Check constraints
Create table Speed_limits
(route_num smallint,
canada_sl integer not null,
uS_SL integer not null check (US_SL <= 65))
alter table speed_limits
add constraint speed65
check (us_sl <=65)
Trigger
create trigger reorder
after update
of qty on stock
referencing new as n
for each row mode db2sql
when (n.qty <= 5)
insert into recorder values (n.itemno, current timestamp)
User defined type
create distinct type kph as integer with comparisons
create distinct type mph as integer with comparisons
create table speed_limits
(route_num smallint,
canada_sl kph not null,
us_sl mph not null)
select route_num from speed_limits where canada_sl > kph(80)
select route_num from speed_limits where canada_sl> us_sl fails
User defined Function
Create function kph_to_mph(kph)
returns mph
language C
external Name
create function "+" (mph, mph)
returns mph
source "+" (integer, integer)
not fenced
Import and Export
db2 connect to musicdb
db2 export to artexprt of ixf messages artmsg select artno, name from artists
db2 import from artexprt of ixf messages artmsg create/insert/insert_update/replace/replace_create into artists
db2 import from artexprt of ixf
message artmsg create into artists {(column_list)]
in tbs
index in indtbs
long in longtbs
Load faster/load into tabales only/not logged/ trigger not fired/unique constraints enforced, RI and check constraints via Set Integrity
Backup and Recovery
Logging: Circular/archival
db2 list history all for musicdb
Backup database User Using pwd tablespace Online use tsm open sessions to
BACKUP DATABASE DWCTRLDB TO E:\\DB2LOG WITH 2 BUFFERS BUFFER 1024
Restore database continue/abort user using pwd TableSpace online /history file online use tsm /From to .
Rollforward database to isotime/end of logs
RESTORE DATABASE DWCTRLDB FROM E:\\DB2LOG TAKEN AT 20010715100252 WITH 2 BUFFERS BUFFER 1024 WITHOUT PROMPTING
Backup Tablespace
BACKUP DATABASE DWCTRLDB TABLESPACE USERSPACE1 TO E:\\DB2LOG WITH 2 BUFFERS BUFFER 1024
Monitoring
diaglevel 0-4
diagpath
Get monitor switches
Update monitor switches using . On/off
reset monitor all /for db
Get snapshot for db/all databases
create event monitor georgepay for statements
where appl_name = ‘Payroll’ and auth_id = ‘George’
write to file ‘/monitors/georgepay’
maxfiles 25
maxfilesize 1024
nonblocked
append
db2enmon
db2trc on
db2trc dump trace.bin
db2trc off
db2trc fmt trace.bin trace.fmt
vi trace.fmt
db2 ? Sqlnnnn
db2 describe indexes for table
db2 describe table
db2 list active databases
db2 list applications for database db_alias [show detail]
db2 force application [all|(application-handle)]
db2stop force
Concurrency
Row lock Min table lock
share is
update ix
exclusive ix
weak exclusive ix
next key share is
next key exclusive ix
next key weak exclusive ix
Application
exec sql declare c1 cursor for
select empno, lastname from xyzco.templ
where wrkdept = :dept;
strcpy(dept, ‘D21′);
exec sql connect to eddb;
exec sql open c1;
exec sql fetch c1 into :hempno, :hlastname;
while (sqlca.sqlcode == 0) {
exec sql fetch c1 into :hempno, :hlastname;
}
exec sql close c1;
exec sql connect reset;
Application performance
db2 update db cfg for dba using DFT_QUERYOPT n
db2 set current query optimization= n
db2 prep pgml.sqc QueryOPT n
db2optimization= n
Runstats on table
Reorgchk reorg table
reort table
bind/rebind
Security
SysAdm
sysctrl
sysmaint
dbadm
Grant /Revoke
Grant alll on table to with grant option
GRANT DBADM,CREATETAB,BINDADD,CREATE_NOT_FENCED,IMPLICIT_SCHEMA,LOAD ON DATABASE TO USER DB2USER2
DataType
CHARACTER, VARCHAR, BLOB, CLOB, DBCLOB, GRAPHIC, or VARGRAPHIC
Import and Export
CLP
db2 export to staff.ixf of ixf select * from userid.staff
db2 export to myfile.ixf of ixf messages msgs.txt select * form staff
db2 export to myfile.del of del lobs to mylogs lobfile lobs1, lobs2 modified by lobsinfile select * from emp
DB2LOOK: used to capture the original table definitions and to generate the corresponding data definition language.
DB2BATCH: used to monitor the performance characteristics and execution duration of SQL statement.
db2 import from stafftab.ixf of ixf insert into userid.staff
Load
db2 load from stafftab.ixf of ixf messages staff.msgs insert into userid.staff copy yes use tsm data buffer 4000
DB2LOADREC registry variable is used to identify the file with the load copy location information.
DB2ATLD AutoLoader
Business Rules
Not Null
Unique
PK
FK
Check constraint
Version recovery
Roll-forward recovery
Two database configuration parameters allow you to change where archived logs are stored:
newlogpath
userexit
To determine which log extents in the database log path directory are archived logs, check the value of the loghead database configuration para.
Those logs with sequence numbers less than loghead are archived logs and can be moved.
Create table NOT LOGGED INITIALLY
db2move to move tables from one OS to another.
If restart db fails, look in the db2diag.log file for a list of TS that are currently damaged.
Can restart db if put damaged TS to DROP PENDING TABLESPACES option
REORGCHK
db2iauto -on Instname //To automatically start instance after each system restart.
db2iauto -off Instname
set db2instance = instname //set the current instance name
db2set db2instdef=instname -g //put on global registry the defalt inst name
db2 get instance
set db2instance
db2ilist
db2icrt -u fenceduserid instname
set db2instance=instname
db2start
Profile registries:
1. The DB2 Instance Level Profile Registry.
2. The DB2 Global Level Profile Registry.
3. The DB2 Instance Node Level Profile Registry.
4. The DB2 Instance Profile Registry.
DB2 configurations check order:
1. Environment variables set with the set command. (or the export command on Unix)
2. Registry values set with the instance node level profile (using the db2set -I commandwith a node number)
3. Registry values set with the db2set command.
4. Registry values set with the instance profile (using the db2set -I command)
5. Registry values set with the global profile (using the db2set -g command)
db2set -lr //list all
db2set -all
db2set -ul // set at user level
db2set -ul username
DB2 Security
NT: SYSADM authority granted to administrators group.
set the registry variable DB2_GRP_LOOKUP=local and add the domain account to the local admin group.
Update database manager configuration file to specify a new group.
db2stop
db2 update dbm cfg using sysadm_group global_group
db2start
Unix: Group for fenced user account must be created, any user ids that use fenced udfs or SPs must be a member of this group.
Rules:
on unix, names must be in lower case.
on oS/2, names must be in upper case.
on Windows platforms, names can be any case.
Authentication type:
Server
server_encrypt
client
DCS
DCS_Encrypt
DCE
DCE_SERVER_ENCRYPT
KERBEROS
KRB_SERVER_ENCRYPT
Parameters controls access to the instance:
authentication
sysadm_group
trust_allclnts
trust_clntauth
sysctrl+group
sysmaint_group
If you locked yourself out of the db2 system, use the fail-safe option to override the usual db2 security checks.
Unix: instance owner
NT: user in administrators group
OS/2: a UPM administrator
SYSADM
DBADM
SYSCTRL
SYSMAINT
syscat.dbauth
syscat.tabauth
syscat.colauth
syscat.packageauth
syscat.indexauth
syscat.schemaauth
syscat.passthruauth
Isolation level:
Repeatable Read
Read Stability
Cursor Stability
Uncommitted Read
Select Isolcation from syscat.packages
Change isolation level
Lock Mode:
Intent None
Intent Share
Next Key Share
Share
Intent Exclusive
Share with Intent Exclusive
Update
Next Key Exclusive
Next Key Weak Exclusive
Exclusive
Weak Exclusive
Superxclusive
select * from employee order by salary desc Fetch first 100 rows only
optimize for 20 rows
Create procedure not fenced
DB2 activate database dbname
db2 deactivate database dbname
Manage parallelism
intra_parallel
max_querydegree
dft_degree
RUNSTATS to collect statistics
syscat.tables
sysstat.tables
syscat.columns
sysstat.columns
syscat.indexes
sysstat.indexes
syscat.coldist
sysstat.coldist
syscat.functions
sysstat.functions
Modeling production databases
db2look -h
SQLDBCON: DB configuration file
SQLLOGCTL.LFH: Track all database logs
SQLLOGDIR/Syyyyyyyy.log: Log file
SQLSPCS.1: Table space info
SQLSPCS.2: Backup of table space info file
SQLBP.1: Buffer Pool
SQLBP.2: Backup of Buffer Pool.
DB2RHIST.ASC: Track historical info.
DB2RHIST.BAK: Backup of track historical info.
syscat.dbauth
syscat.bufferpools
syscat.colauth
syscat.datatypes
syscat.indexauth
syscat.indexes
syscat.schemata
syscat.procedures
syscat.tables
syscat.tablespaces
一些实验脚本:
catalog database Test as TestDB at node db2inst3
create bufferpool bufferpool1 size 10000 pagesize 4096
select * from syscat.bufferpools
create database test
catalog tablespace managed by system using …
user tablespace managed by database using …
temporary tablespace managed by system using …
create tablespace test1 managed by system using (‘/home/db2inst1/ts’)
create tablespace test1 managed by database using
(file’/home/db2inst1/test_ts’ 1000)
smitty storage
create a new logical volume db2ts2
chown db22 db2ts2
create tablespace test1 managed by database using (device ‘/dev/rdb2ts2′
1000)
alter tablesapce test_dev resize (device ‘/dev/rdatabase2′ 2000)
alter tablesapce test_dev extend (device ‘/dev/rdatabase2′ 1000)
mkuser db2inst3
passwd db2inst3 training
/usr/lpp/db2_07_01/instance/db2icrt -u db2f2 db22
su – db22
db2ilist
db2start
db2 get instance
db2
list database directory
create database db22
/home/db22/db22/NODE0000/SQL00001
/home/db22/db22/NODE0000/sqldbdir
list database directory on /home/db22
->database directory: SQL00001
list tablespace
list tablespace containers for 0
->/home/db22/db22/NODE0000/sql00001/sqlt0000.0
su – db22
mkdir tbs
cd ts
mkdir systs
mkdir tempts
create database test \
catalog tablespace managed by system using \
(‘/home/db22/ts/systs’) \
user tablespace managed by database using \
(file ‘/home/db22/ts/user.dat’ 1000) \
temporary tablespace managed by system using \
(‘/home/db22/ts/tempts’) \
DataType:
smallint
integer
bigint
decimal
character 1-254
varchar 1-32672
long varchar 32700
For bit data
blob (k,m,g)
clob (k,m,g)
dbclob (k,m,g)
graphic
vargraphic
long vargraphic
date
time
timestamp
10M as the criteria
Not Logged
compact
Max columns in a table: 500
list tables
describe table employee
create table employee \
(empno char(6) not null primary key, \
FirstName varchar(12) not null, \
LastName varchar(12) not null, \
photo blob(10m) not null not logged), \
in usertablespace2
primary key (deptno)
foreign key FKEmp1 (workdept)
-> Foreign key name can’t be like FK_EM_Dept
references department (deptname) on delete no action
constraint chkprov check (province in (ON, NS, BC))
c3 generated always as (c1 + c2),
c4 generated always as (case when c1> c2 then 1 else null end))
create user temporary tablespace usrtbsp managed by database \
using (file ‘/home/db2inst8/usr_tbsp’ 1000)
declare global temporary table glb_temp \
like employee on commit delete rows \
not logged in usrtbsp
describe table glb_temp
create table t2 (cl1 int, \
col2 double \
col3 int not null generated always as identity \
(start with 100, increment by 5)
select * from syscat.tables
create tablespace index_ts managed by database using \
(file ‘/home/db2inst8/index_ts.dat’ 1000)
create tablespace photo_ts managed by database using \
(file ‘/home/db2inst8/photo_ts.dat’ 1000)
create table emp_photo \
(empno char(6) not null, \
photo_format varchar(10) not null, \
picture blob(100k) \
in userspace1 \
index in index_ts \
long in photo_ts)
alter table t2 add cl4 char(6)
alter table t2 alter column col4 set data type varchar(20)
– if defined as char(20), then can’t be modified.
alter table t2 add constraint col_u unique (col1, col2)
alter table t2 alter column col1 not null – wrong
alter table project add constraint proj_key primary key (projno)
alter table emp_act add constraint act_key primary key (empno, projno,
actno)
alter table projects drop constraint proj_key
alter table emp_act add constraint act_emp_ref \
foreign key (empno) \
references employee \
on delete cascade
rename table t1 to t2
drop table t2
create trigger new_hire after insert on employee for each row mode db2sql\
update company_stats set nbemp=nbemp + 1
syscat.triggers
stty erase ^
create view myview as select * from employee
create view emp_info (emp_lname, emp_fname, emp_job) \
as select lastname, firstnme, job from empolyee
syscat.views
create alias myemp for employee
drop alias myemp
select * from syscat.tables
create index indx_emp on employee (lastname asc)
create tablespace indx_ts managed by database \
using (file ‘/home/db2inst1/indx_ts.dat’ 1000)
create unique index ph_indx on employee (phoneno desc) in indx_ts
?
SECURITY
********************************************************************
Authentication:
Server
server encrypted
client trusted
client untrusted
DCS (connect server)
DCS_Server_Encrypt
Kerberos
to setup autentication method:
modify database configuration
get database manager configuration
get dbm cfg file: db2systm
"Database manager authentication"
update dbm cfg using authentication client
Authorities:
SYSADM ***** highest authority // instance, databases, access data
SYSCTRL **** //instance, databases, no access data
SYSMAINT *** //backup restore database
DBADM **** second highest on a database
LOAD * export import data to databases
within a database:
SYSADM
DBADM –SYSCTRL /* Parallel authority
LOAD –SYSMAINT
Instance level:
SYSADM
SYSCTRL /SYSMAINT
Load DBADM
cat /etc/group
add new user to the group
Change adm group:
mkgroup db2test
cat /etc/group
su – db2inst2
db2
get dbm cfg
search for primary group
update dbm cfg using sysadm_group db2test
Details:
SYSADM: migrate db; change dbm cfg; grant dbadm/Load
SYSCTRL: create db, drop db, create tbs, drop tbs;stop user connection;
backup/restore db
SYSMAINT: backup/restore db, start/stop instances
The above three has to change cfg file to grant priviledge
SYSADM & DBADM are administrative privilege
SYSCTRL & SYSMAINT are system privilege
db2 get database manager configuration
db2stop force
PRIVILEDGE:
Database priviledge:
createtab
bindadd
connect
implicit schema
use tablespace
load
DB object:
ownership / individual priviledge
control … on table have alter; delete; insert; select; update; index;
references
… on index
… on view have delete; insert; select; update; all
… on package grant bind; execute
… on tbs grant use;
… on schema have createin; alterin; dropin
export db2instance db2inst2
?
mkuser pgrp=db2bk db2bk
connect to sample
grant dbadm on database to user db2user2
if there is no group callled db2user2
grant dbadm on database to db2user2
grant load on database to user db2user2
revoke dbadm on database from user db2user2
revoke connect on database from user db2user3
grant select on employee to user db2user3 with grant option
grant bindadd on database to user db2user3
grant connect on database to public
grant createtab on database to group dbusers
grant createtab on database to group dbusers with grant option (Errro!)
connect to sample user db2user3 using db2user3
grant select on staff to user db2user3
grant control on staff to user db2user3
grant use of tablespace indx_ts to db2user4 with grant option
grant contrl on employee to db2users
grant all on employee to public
Dept Admin (Manager) … SysAdm
security Admin … SysAdm(instance level) DBAdm (DB level)
DBA … SysAdm/SysCtrl
Sys Operator … SysMaint
Programmer … connect, bindadd, createtab
DB user … connect, select, update, delete,insert
syscat.dbauth
syscat.tbauth
syscat.colauth
syscat.packageauth
syscat.indexauth
syscat.schemaauth
select distinct grantee, granteetype, ‘Database’ from syscat.dbauth
granteetype = G with grant option
= U default
select distinct grantee, granteetype from syscat.tabauth where tabname =
‘Employee’
Database Audit
Audit facility is setup at instance level.
Audit Categories:
1. Audit
2. Authorization checking
3. Object maintenance
4. Security maintenance
5. System administration
6. User validation
7. Operation content
db2Audit Start (default is stopped)
db2Audit Stop
db2Audit Configure [Scope {all | checking | objmaint | secmaint | sysadmin | validate | context}]
[status { both | success | failure}]
db2audit configure scope all status both
db2audit configure scope checking, objmaint status success
db2Audit describe
db2Audit flush — write all content from cache to db2audit.log
db2Audit extract file … category audit… — extract info from db2audit.log to db2audit.out
format: text file
delimited ascii format
audit.del
checking.del
objmaint.del
secmaint.del
sysmaint.del
validate.del
context.del
db2audit extract file audit.txt category context database sample
db2audit extract delasc delimiter !
db2Audit prune all — delete db2audit log.
db2audit prune date 2001090409
$db2home/SQLLIB/SECURITY/db2audit.cfg
$db2home/SQLLIB/SECURITY/db2audit.log
Grant Privilege:
Grant … on Database … to …
Bindadd : To create packages
Connect : Access db
CreateTab : To create base tables
Create_NOT_Fenced : Ro register functions that execute in the dbm’s process
Implicit_schema : To implicitly create a schema
DBADM : has all privileges
LOAD : must have other table-level privileges in order to insert/replace
Grant control on index … to … : to drop the index
Grant BInd on package | Program … to … : Rebind
Control : Rebuild, drop or execute the package, and extend package privileges to other users.
Execute | Run : execute the package
Grant alterin on Schema … to … : To alter or comment on all objects in the schema.
CreateIN : To create objects in the schema.
DropIN : To drop all objects in the schema.
Grant passthru on server … to … : To access and use a specified data source in pass-through mode.
Grant All on Table … to … with grant option : all except control privilege that grantor has
Alter view_name : add columns, create/drop PK, FK.
Control nickname : All the privilege, drop base table, view or nickname. Grant to other.
Delete
Index
Insert
References : Create and drop FK referencing the table
Select
UPdate
Note: 1. alter, index, references are not applicable to views.
2. Delete, insert, select and update are not applicable to nicknames.
Grant use of tablespace … to … : To specify or default to the tbs when creating table.