DB2审计配置测试
正在加载中...
资料简介：
DB2审计配置测试环境：AIX 5.3DB2版本：DB2 V9.1 DPF 8个分区
1.
设置审计记录缓冲区大小参数此参数默认为0，为同步方式将审计记录写入磁盘，在较忙的数据库上会对性能有影响。
[p680:/db2home/db2inst1/sqllib/security]db2_all "db2 update dbm cfg using AUDIT_BUF_SZ 2048"
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
验证参数修改
[p680:/db2home/db2inst1/sqllib/security]db2_all "db2 get dbm cfg |grep -i audit_buf_sz"
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
2.
配置审计的事件类别
如下配置对对象维护和操作上下文成功和失败的操作都审计。
[p680:/db2home/db2inst1/sqllib/security]db2audit configure scope objmaint,context status both errortype audit
AUD0000I
Operation succeeded.
查看审计配置
[p680:/db2home/db2inst1/sqllib/security]db2audit describe
DB2 AUDIT SETTINGS:
Audit active: "FALSE "
Log errors: "TRUE "
Log success: "TRUE "
Log audit events: "FALSE "
Log checking events: "FALSE "
Log object maintenance events: "TRUE "
Log security maintenance events: "FALSE "
Log system administrator events: "FALSE "
Log validate events: "FALSE "
Log context events: "TRUE "
Return SQLCA on audit error: "TRUE "
AUD0000I
Operation succeeded.
3.
启动审计开关
[p680:/db2home/db2inst1/sqllib/security]db2audit start
AUD0000I
Operation succeeded.
下面贴的格式有点乱，附上word文档：
[ 本帖最后由 fengsh 于 2009-7-29 14:37 编辑 ]
1.
设置审计记录缓冲区大小参数此参数默认为0，为同步方式将审计记录写入磁盘，在较忙的数据库上会对性能有影响。
[p680:/db2home/db2inst1/sqllib/security]db2_all "db2 update dbm cfg using AUDIT_BUF_SZ 2048"
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
DB20000I
The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
p680: db2 update dbm cfg ... completed ok
验证参数修改
[p680:/db2home/db2inst1/sqllib/security]db2_all "db2 get dbm cfg |grep -i audit_buf_sz"
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
Audit buffer size (4KB)
(AUDIT_BUF_SZ) = 2048
p680: db2 get dbm cfg |grep ... completed ok
2.
配置审计的事件类别
如下配置对对象维护和操作上下文成功和失败的操作都审计。
[p680:/db2home/db2inst1/sqllib/security]db2audit configure scope objmaint,context status both errortype audit
AUD0000I
Operation succeeded.
查看审计配置
[p680:/db2home/db2inst1/sqllib/security]db2audit describe
DB2 AUDIT SETTINGS:
Audit active: "FALSE "
Log errors: "TRUE "
Log success: "TRUE "
Log audit events: "FALSE "
Log checking events: "FALSE "
Log object maintenance events: "TRUE "
Log security maintenance events: "FALSE "
Log system administrator events: "FALSE "
Log validate events: "FALSE "
Log context events: "TRUE "
Return SQLCA on audit error: "TRUE "
AUD0000I
Operation succeeded.
3.
启动审计开关
[p680:/db2home/db2inst1/sqllib/security]db2audit start
AUD0000I
Operation succeeded.
下面贴的格式有点乱，附上word文档：
[ 本帖最后由 fengsh 于 2009-7-29 14:37 编辑 ]
2009-07-08
页数9
浏览4672
下载77
已下载用户的评价7.23分
您还未下载该资料，不能发表评价；
查看我的 待评价资源
查看我的 待评价资源
没用
5. 停止审计活动 [p680:/db2home/db2inst1/sqllib/security]db2audit stop AUD0000I Operation succeeded. 6. 创建审计数据文件 将审计记录从db2audit.log抽取到文件 [p680:/db2home/db2inst1/sqllib/security]db2audit extract file db2audit.out AUD0000I Operation succeeded. 7. 查看审计记录文件 会看到操作上下文类别的审计事件，会有如下事件 [p680:/db2home/db2inst1/sqllib/security]more db2audit.out timestamp=2009-06-16-11.18.56.390653;category=CONTEXT;audit event=DETACH; event correlator=2; userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031856;application name=db2audit; timestamp=2009-06-16-11.22.42.195915;category=CONTEXT;audit event=EXECUTE_IMMEDIATE; event correlator=29; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=203;text=create table fengsh like emp in USERSPACE1; timestamp=2009-06-16-11.22.42.882360;category=OBJMAINT;audit event=CREATE_OBJECT; event correlator=29;event status=0; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=0;object schema=DB2INST1;object name=FENGSH;object type=TABLE; timestamp=2009-06-16-11.23.48.021273;category=CONTEXT;audit event=EXECUTE_IMMEDIATE; event correlator=30; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=203;text=insert into fengsh select * from emp; timestamp=2009-06-16-11.24.30.292076;category=CONTEXT;audit event=EXECUTE_IMMEDIATE; event correlator=36; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=203;text=delete from fengsh where workdept='D11'; timestamp=2009-06-16-11.24.34.906942;category=CONTEXT;audit event=PREPARE; event correlator=37; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=201;text=select * from fengsh; timestamp=2009-06-16-11.25.12.061202;category=CONTEXT;audit event=EXECUTE_IMMEDIATE; event correlator=41; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=203;text=update fengsh set workdept='D11' where workdept='E11'; timestamp=2009-06-16-11.25.12.079082;category=CONTEXT;audit event=COMMIT; event correlator=41; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=0; timestamp=2009-06-16-11.25.15.135084;category=CONTEXT;audit event=PREPARE; event correlator=42; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=201;text=select * from fengsh; 8. 创建定界ASCII格式审计数据文件，用于装入表中 将审计记录从db2audit.log移到定界ASCII文件 [p680:/db2home/db2inst1/sqllib/security]db2audit extract delasc category context status success AUD0000I Operation succeeded. 9. 创建操作上下文事件审计表 [p680:/db2home/db2inst1/sqllib/security]vi tab.sql CREATE TABLE CONTEXT (TIMESTAMP CHAR(26), CATEGORY CHAR(8), EVENT VARCHAR(32), CORRELATOR INTEGER, DATABASE CHAR(8), USERID VARCHAR(1024), AUTHID VARCHAR(128), NODENUM SMALLINT, COORDNUM SMALLINT, APPID VARCHAR(255), APPNAME VARCHAR(1024), PKGSCHEMA VARCHAR(128), PKGNAME VARCHAR(128), PKGSECNUM SMALLINT, STMTTEXT CLOB(2M), PKGVER VARCHAR(64)); ~ [p680:/db2home/db2inst1/sqllib/security]db2 -tvf tab.sql 10. 将DB2审计数据装入表中 [p680:/db2home/db2inst1/sqllib/security]db2 load from context.del of del insert into context Agent Type Node SQL Code Result ______________________________________________________________________________ LOAD 000 +00003107 Success. ______________________________________________________________________________ LOAD 001 +00003107 Success. ______________________________________________________________________________ LOAD 002 +00003107 Success. ______________________________________________________________________________ LOAD 003 +00003107 Success. ______________________________________________________________________________ LOAD 004 +00003107 Success. ______________________________________________________________________________ LOAD 005 +00003107 Success. ______________________________________________________________________________ LOAD 006 +00003107 Success. ______________________________________________________________________________ LOAD 007 +00003107 Success. ______________________________________________________________________________ PARTITION 001 +00000000 Success. ______________________________________________________________________________ PRE_PARTITION 000 +00000000 Success. ______________________________________________________________________________ RESULTS: 8 of 8 LOADs completed successfully. ______________________________________________________________________________ Summary of Partitioning Agents: Rows Read = 507 Rows Rejected = 0 Rows Partitioned = 507 Summary of LOAD Agents: Number of rows read = 507 Number of rows skipped = 0 Number of rows loaded = 507 Number of rows rejected = 0 Number of rows deleted = 0 Number of rows committed = 507 SQL3107W There is at least one warning message in the message file. 11. 从表中查询DB2审计数据 [p680:/db2home/db2inst1/sqllib/security]db2 "select * from db2inst1.context" 其他事件审计方式一样，详细参考信息中心或“《管理指南－实施》审计DB2数据库的活动”章节
5. 停止审计活动 [p680:/db2home/db2inst1/sqllib/security]db2audit stop AUD0000I Operation succeeded. 6. 创建审计数据文件 将审计记录从db2audit.log抽取到文件 [p680:/db2home/db2inst1/sqllib/security]db2audit extract file db2audit.out AUD0000I Operation succeeded. 7. 查看审计记录文件 会看到操作上下文类别的审计事件，会有如下事件 [p680:/db2home/db2inst1/sqllib/security]more db2audit.out timestamp=2009-06-16-11.18.56.390653;category=CONTEXT;audit event=DETACH; event correlator=2; userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031856;application name=db2audit; timestamp=2009-06-16-11.22.42.195915;category=CONTEXT;audit event=EXECUTE_IMMEDIATE; event correlator=29; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=203;text=create table fengsh like emp in USERSPACE1; timestamp=2009-06-16-11.22.42.882360;category=OBJMAINT;audit event=CREATE_OBJECT; event correlator=29;event status=0; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=0;object schema=DB2INST1;object name=FENGSH;object type=TABLE; timestamp=2009-06-16-11.23.48.021273;category=CONTEXT;audit event=EXECUTE_IMMEDIATE; event correlator=30; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=203;text=insert into fengsh select * from emp; timestamp=2009-06-16-11.24.30.292076;category=CONTEXT;audit event=EXECUTE_IMMEDIATE; event correlator=36; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=203;text=delete from fengsh where workdept='D11'; timestamp=2009-06-16-11.24.34.906942;category=CONTEXT;audit event=PREPARE; event correlator=37; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=201;text=select * from fengsh; timestamp=2009-06-16-11.25.12.061202;category=CONTEXT;audit event=EXECUTE_IMMEDIATE; event correlator=41; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=203;text=update fengsh set workdept='D11' where workdept='E11'; timestamp=2009-06-16-11.25.12.079082;category=CONTEXT;audit event=COMMIT; event correlator=41; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=0; timestamp=2009-06-16-11.25.15.135084;category=CONTEXT;audit event=PREPARE; event correlator=42; database=SAMPLE;userid=db2inst1;authid=DB2INST1; origin node=0;coordinator node=0; application id=*N0.db2inst1.090616031905;application name=db2bp; package schema=NULLID;package name=SQLC2F0A; package section=201;text=select * from fengsh; 8. 创建定界ASCII格式审计数据文件，用于装入表中 将审计记录从db2audit.log移到定界ASCII文件 [p680:/db2home/db2inst1/sqllib/security]db2audit extract delasc category context status success AUD0000I Operation succeeded. 9. 创建操作上下文事件审计表 [p680:/db2home/db2inst1/sqllib/security]vi tab.sql CREATE TABLE CONTEXT (TIMESTAMP CHAR(26), CATEGORY CHAR(8), EVENT VARCHAR(32), CORRELATOR INTEGER, DATABASE CHAR(8), USERID VARCHAR(1024), AUTHID VARCHAR(128), NODENUM SMALLINT, COORDNUM SMALLINT, APPID VARCHAR(255), APPNAME VARCHAR(1024), PKGSCHEMA VARCHAR(128), PKGNAME VARCHAR(128), PKGSECNUM SMALLINT, STMTTEXT CLOB(2M), PKGVER VARCHAR(64)); ~ [p680:/db2home/db2inst1/sqllib/security]db2 -tvf tab.sql 10. 将DB2审计数据装入表中 [p680:/db2home/db2inst1/sqllib/security]db2 load from context.del of del insert into context Agent Type Node SQL Code Result ______________________________________________________________________________ LOAD 000 +00003107 Success. ______________________________________________________________________________ LOAD 001 +00003107 Success. ______________________________________________________________________________ LOAD 002 +00003107 Success. ______________________________________________________________________________ LOAD 003 +00003107 Success. ______________________________________________________________________________ LOAD 004 +00003107 Success. ______________________________________________________________________________ LOAD 005 +00003107 Success. ______________________________________________________________________________ LOAD 006 +00003107 Success. ______________________________________________________________________________ LOAD 007 +00003107 Success. ______________________________________________________________________________ PARTITION 001 +00000000 Success. ______________________________________________________________________________ PRE_PARTITION 000 +00000000 Success. ______________________________________________________________________________ RESULTS: 8 of 8 LOADs completed successfully. ______________________________________________________________________________ Summary of Partitioning Agents: Rows Read = 507 Rows Rejected = 0 Rows Partitioned = 507 Summary of LOAD Agents: Number of rows read = 507 Number of rows skipped = 0 Number of rows loaded = 507 Number of rows rejected = 0 Number of rows deleted = 0 Number of rows committed = 507 SQL3107W There is at least one warning message in the message file. 11. 从表中查询DB2审计数据 [p680:/db2home/db2inst1/sqllib/security]db2 "select * from db2inst1.context" 其他事件审计方式一样，详细参考信息中心或“《管理指南－实施》审计DB2数据库的活动”章节
没用
[b][size=12pt][font=Times New Roman][color=#000000]4． [/color][/font][/size][/b][color=#000000][b][font=宋体][size=12pt]对数据库做简单操作，新建对象，进行[/size][/font][/b][b][size=12pt][font=Times New Roman]DML[/font][/size][/b][b][font=宋体][size=12pt]操作测试[/size][/font][/b][b][size=12pt][/size][/b][/color] [color=#000000][b][font=宋体][size=12pt]连库[/size][/font][/b][b][size=12pt][/size][/b][/color] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 connect to sample[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman] Database Connection Information[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman] Database server = DB2/AIX64 9.1.4[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman] SQL authorization ID = DB2INST1[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman] Local database alias = SAMPLE[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [color=#000000][b][font=宋体][size=12pt]建表[/size][/font][/b][b][size=12pt][/size][/b][/color] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "create table fengsh like emp"[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]DB21034E The command was processed as an SQL statement because it was not a [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]valid Command Line Processor command. During SQL processing it returned:[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]SQL0290N Table space access is not allowed. SQLSTATE=55039[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "create table fengsh like emp in USERSPACE1" [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]DB20000I The SQL command completed successfully.[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [color=#000000][b][font=宋体][size=12pt]插入数据到表中[/size][/font][/b][b][size=12pt][/size][/b][/color] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "insert into fengsh select * from emp"[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]DB20000I The SQL command completed successfully.[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "select * from fengsh"[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman]EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000150 BRUCE ADAMSON D11 4510 2002-02-12 DESIGNER 16 M 1977-05-17 55280.00 500.00 2022.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000270 MARIA L PEREZ D21 9001 2006-09-30 CLERK 15 F 2003-05-26 37380.00 500.00 2190.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000050 JOHN B GEYER E01 6789 1979-08-17 MANAGER 16 M 1955-09-15 80175.00 800.00 3214.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000160 ELIZABETH R PIANKA D11 3782 2006-10-11 DESIGNER 17 F 1980-04-12 62250.00 400.00 1780.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000260 SYBIL P JOHNSON D21 8953 2005-09-11 CLERK 16 F 1976-10-05 47250.00 300.00 1380.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000300 PHILIP X SMITH E11 2095 2002-06-19 OPERATOR 14 M 1976-10-27 37750.00 400.00 1420.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200140 KIM N NATZ C01 1793 2006-12-15 ANALYST 18 F 1976-01-19 68420.00 600.00 2274.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200240 ROBERT M MONTEVERDE D21 3780 2004-12-05 CLERK 17 M 1984-03-31 37760.00 600.00 2301.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000090 EILEEN W HENDERSON E11 5498 2000-08-15 MANAGER 16 F 1971-05-15 89750.00 600.00 2380.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000110 VINCENZO G LUCCHESSI A00 3490 [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]…………………………[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]…………………………[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000250 DANIEL S SMITH D21 0961 1999-10-30 CLERK 15 M 1969-11-12 49180.00 400.00 1534.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200010 DIAN J HEMMINGER A00 3978 1995-01-01 SALESREP 18 F 1973-08-14 46500.00 1000.00 4220.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200120 GREG ORLANDO A00 2167 2002-05-05 CLERK 14 M 1972-10-18 39250.00 600.00 2340.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200330 HELENA WONG E21 2103 2006-02-23 FIELDREP 14 F 1971-07-18 35370.00 500.00 2030.00[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman] 42 record(s) selected.[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [color=#000000][b][font=宋体][size=12pt]删除表中记录[/size][/font][/b][b][size=12pt][/size][/b][/color] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "delete from fengsh where workdept='D11'" [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]DB20000I The SQL command completed successfully.[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [color=#000000][b][font=宋体][size=12pt]更新表记录[/size][/font][/b][b][size=12pt][/size][/b][/color] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "update fengsh set workdept='D11' where workdept='E11'"[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]DB20000I The SQL command completed successfully.[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "select * from fengsh" [/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman]EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000020 MICHAEL L THOMPSON B01 3476 2003-10-10 MANAGER 18 M 1978-02-02 94250.00 800.00 3300.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000130 DELORES M QUINTANA C01 4578 2001-07-28 ANALYST 16 F 1955-09-15 73800.00 500.00 1904.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000240 SALVATORE M MARINO D21 3780 2004-12-05 CLERK 17 M 2002-03-31 48760.00 600.00 2301.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200340 ROY R ALONZO E21 5698 1997-07-05 FIELDREP 16 M 1956-05-17 31840.00 500.00 1907.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000270 MARIA L PEREZ D21 9001 2006-09-30 CLERK 15 F 2003-05-26 37380.00 500.00 2190.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000090 EILEEN W HENDERSON D11 5498 2000-08-15 MANAGER 16 F 1971-05-15 89750.00 [/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman]…………………………[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]…………………………[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman]000280 ETHEL R SCHNEIDER D11 8997 1997-03-24 OPERATOR 17 F 1976-03-28 36250.00 500.00 2100.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000320 RAMLAL V MEHTA E21 9990 1995-07-07 FIELDREP 16 M 1962-08-11 39950.00 400.00 1596.00[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman] 31 record(s) selected.[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [[i] 本帖最后由 fengsh 于 2009-7-8 18:09 编辑 [/i]]
[b][size=12pt][font=Times New Roman][color=#000000]4． [/color][/font][/size][/b][color=#000000][b][font=宋体][size=12pt]对数据库做简单操作，新建对象，进行[/size][/font][/b][b][size=12pt][font=Times New Roman]DML[/font][/size][/b][b][font=宋体][size=12pt]操作测试[/size][/font][/b][b][size=12pt][/size][/b][/color] [color=#000000][b][font=宋体][size=12pt]连库[/size][/font][/b][b][size=12pt][/size][/b][/color] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 connect to sample[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman] Database Connection Information[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman] Database server = DB2/AIX64 9.1.4[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman] SQL authorization ID = DB2INST1[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman] Local database alias = SAMPLE[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [color=#000000][b][font=宋体][size=12pt]建表[/size][/font][/b][b][size=12pt][/size][/b][/color] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "create table fengsh like emp"[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]DB21034E The command was processed as an SQL statement because it was not a [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]valid Command Line Processor command. During SQL processing it returned:[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]SQL0290N Table space access is not allowed. SQLSTATE=55039[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "create table fengsh like emp in USERSPACE1" [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]DB20000I The SQL command completed successfully.[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [color=#000000][b][font=宋体][size=12pt]插入数据到表中[/size][/font][/b][b][size=12pt][/size][/b][/color] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "insert into fengsh select * from emp"[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]DB20000I The SQL command completed successfully.[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "select * from fengsh"[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman]EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000150 BRUCE ADAMSON D11 4510 2002-02-12 DESIGNER 16 M 1977-05-17 55280.00 500.00 2022.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000270 MARIA L PEREZ D21 9001 2006-09-30 CLERK 15 F 2003-05-26 37380.00 500.00 2190.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000050 JOHN B GEYER E01 6789 1979-08-17 MANAGER 16 M 1955-09-15 80175.00 800.00 3214.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000160 ELIZABETH R PIANKA D11 3782 2006-10-11 DESIGNER 17 F 1980-04-12 62250.00 400.00 1780.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000260 SYBIL P JOHNSON D21 8953 2005-09-11 CLERK 16 F 1976-10-05 47250.00 300.00 1380.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000300 PHILIP X SMITH E11 2095 2002-06-19 OPERATOR 14 M 1976-10-27 37750.00 400.00 1420.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200140 KIM N NATZ C01 1793 2006-12-15 ANALYST 18 F 1976-01-19 68420.00 600.00 2274.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200240 ROBERT M MONTEVERDE D21 3780 2004-12-05 CLERK 17 M 1984-03-31 37760.00 600.00 2301.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000090 EILEEN W HENDERSON E11 5498 2000-08-15 MANAGER 16 F 1971-05-15 89750.00 600.00 2380.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000110 VINCENZO G LUCCHESSI A00 3490 [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]…………………………[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]…………………………[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000250 DANIEL S SMITH D21 0961 1999-10-30 CLERK 15 M 1969-11-12 49180.00 400.00 1534.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200010 DIAN J HEMMINGER A00 3978 1995-01-01 SALESREP 18 F 1973-08-14 46500.00 1000.00 4220.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200120 GREG ORLANDO A00 2167 2002-05-05 CLERK 14 M 1972-10-18 39250.00 600.00 2340.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200330 HELENA WONG E21 2103 2006-02-23 FIELDREP 14 F 1971-07-18 35370.00 500.00 2030.00[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman] 42 record(s) selected.[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [color=#000000][b][font=宋体][size=12pt]删除表中记录[/size][/font][/b][b][size=12pt][/size][/b][/color] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "delete from fengsh where workdept='D11'" [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]DB20000I The SQL command completed successfully.[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [color=#000000][b][font=宋体][size=12pt]更新表记录[/size][/font][/b][b][size=12pt][/size][/b][/color] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "update fengsh set workdept='D11' where workdept='E11'"[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]DB20000I The SQL command completed successfully.[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman][p680:/db2home/db2inst1/sqllib/security]db2 "select * from fengsh" [/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman]EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM [/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000020 MICHAEL L THOMPSON B01 3476 2003-10-10 MANAGER 18 M 1978-02-02 94250.00 800.00 3300.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000130 DELORES M QUINTANA C01 4578 2001-07-28 ANALYST 16 F 1955-09-15 73800.00 500.00 1904.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000240 SALVATORE M MARINO D21 3780 2004-12-05 CLERK 17 M 2002-03-31 48760.00 600.00 2301.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]200340 ROY R ALONZO E21 5698 1997-07-05 FIELDREP 16 M 1956-05-17 31840.00 500.00 1907.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000270 MARIA L PEREZ D21 9001 2006-09-30 CLERK 15 F 2003-05-26 37380.00 500.00 2190.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000090 EILEEN W HENDERSON D11 5498 2000-08-15 MANAGER 16 F 1971-05-15 89750.00 [/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman]…………………………[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]…………………………[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman]000280 ETHEL R SCHNEIDER D11 8997 1997-03-24 OPERATOR 17 F 1976-03-28 36250.00 500.00 2100.00[/font][/color][/size] [size=12pt][color=#000000][font=Times New Roman]000320 RAMLAL V MEHTA E21 9990 1995-07-07 FIELDREP 16 M 1962-08-11 39950.00 400.00 1596.00[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [size=12pt][color=#000000][font=Times New Roman] 31 record(s) selected.[/font][/color][/size] [size=12pt][font=Times New Roman][color=#000000] [/color][/font][/size] [[i] 本帖最后由 fengsh 于 2009-7-8 18:09 编辑 [/i]]