ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Windows上DB2 UDB脚本编制简介3

Windows上DB2 UDB脚本编制简介3

原创 Linux操作系统 作者:myfriend2010 时间:2019-06-29 19:12:08 0 删除 编辑
收集 DB2 系统信息

另一个可以用 Windows shell 和 DB2 命令迅速进行脚本编制的任务是相关系统、实例和数据库信息的收集。我们可以收集系统资源、DB2 注册表变量、实例和数据库配置参数以及数据库资源利用情况的快照。在开始对数据库系统作出更改之前,有这样一个脚本始终是很方便的。

清单 9. DB2 获取配置信息的脚本(db2getconf.bat)

@echo off
::---------------------------------------------------------------------------::
:: This is a Windows Shell Script to collect DB2 system information. The
:: information collected is stored in a file name with the following format:
:: ..YYYYMMDD.HHMMSS.rpt 
::---------------------------------------------------------------------------::
set script_name=%~n0
set script_log=%~n0.log
set script_ver=1.0
set DB2INSTANCE=DB2
set DB2DATABASE=SAMPLE

title %script_name% v%script_ver%
echo %script_name% v%script_ver% started on %date% at %time%

::---------------------------------------------------------------------------::
:: Set Script Date Variable
::---------------------------------------------------------------------------::
for /F "tokens=1-4 delims=/ " %%i in ('echo %date%') do (
    set script_date=%%l%%j%%k
    )

::---------------------------------------------------------------------------::
:: Set Script Time Variable
::---------------------------------------------------------------------------::
for /F "tokens=1-4 delims=:. " %%i in ('echo %time%') do (
    set script_time=%%i%%j%%k
    )

::---------------------------------------------------------------------------::
:: Set Script Report File
::---------------------------------------------------------------------------::
set script_rpt=%db2instance%.%db2database%.%script_date%.%script_time%.rpt

::---------------------------------------------------------------------------::
:: Collect DB2 Services Configuration (Requires Windows Resource Kit)
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% collecting DB2 Services configuration...
echo DB2 Services Configuration > %script_rpt%
srvinfo | find "DB2" >> %script_rpt% 2> %script_log%
if not %errorlevel% == 0 (
   echo ERROR: Unable to collect DB2 Services data, RC=%errorlevel%
   type %script_log%
   )

::---------------------------------------------------------------------------::
:: Collect DB2 Registry Configuration
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% collecting DB2 Registry configuration...
echo DB2 Registry Configuration >> %script_rpt%
db2set -all >> %script_rpt% 2> %script_log%
if not %errorlevel% == 0 (
   echo ERROR: Unable to collect DB2 Registry data, RC=%errorlevel%
   type %script_log%
   )

::---------------------------------------------------------------------------::
:: Collect DB2 Database Administration Server (DB2DAS00) Configuration
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% collecting DB2DAS00 instance configuration...
db2 get admin cfg >> %script_rpt% 2> %script_log%
if not %errorlevel% == 0 (
   echo ERROR: Unable to collect DB2DAS00 instance data, RC=%errorlevel%
   type %script_log%
   )

::---------------------------------------------------------------------------::
:: Collect DB2 Instance Configuration
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% collecting %DB2INSTANCE% instance 
configuration...
db2 get dbm cfg >> %script_rpt% 2> %script_log%
if not %errorlevel% == 0 (
   echo ERROR: Unable to collect %DB2INSTANCE% instance data, RC=%errorlevel%
   type %script_log%
   )

::---------------------------------------------------------------------------::
:: Collect DB2 Database Configuration
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% collecting %DB2DATABASE% database 
configuration...
db2 get db cfg for sample >> %script_rpt% 2> %script_log%
if not %errorlevel% == 0 (
   echo ERROR: Unable to collect %DB2DATABASE% database data, RC=%errorlevel%
   type %script_log%
   )

db2 connect to %DB2DATABASE%
if not %errorlevel% == 0 (
   echo ERROR: Unable to connect to %DB2DATABASE% database, RC=%errorlevel%
   type %script_log%
   )

db2 "select BUFFERPOOLID, BPNAME, NPAGES from SYSCAT.BUFFERPOOLS" >> 
%script_rpt% 2> %script_log%
if not %errorlevel% == 0 (
   echo ERROR: Unable to collect %DB2DATABASE% database bufferpool data, 
RC=%errorlevel%
   type %script_log%
   )

db2 list tablespaces show detail >> %script_rpt% 2> %script_log%
if not %errorlevel% == 0 (
   echo ERROR: Unable to collect %DB2DATABASE% database tablespace data, 
RC=%errorlevel%
   type %script_log%
   )

db2 connect reset
if not %errorlevel% == 0 (
   echo ERROR: Unable to reset connection from %DB2DATABASE% database, 
RC=%errorlevel%
   type %script_log%
   )

echo %script_name% v%script_ver% completed on %date% at %time%

您甚至可以将这些信息存储在数据库的表中,这样这些信息就与数据库备份镜像保存在一起。定期地维护这些系统信息的历史记录可以为以后进行容量规划提供极有价值的帮助。

DB2 安全性审计

现在让我们看一个 DB2 系统命令的较好示例,该命令在用脚本语言进行自动化时非常有用。DB2 审计工具是一个实用程序,可以利用它充分地增强 DB2 实例和数据库安全性审计。它由名为 db2audit.exe 的 DB2 系统命令完全管理和控制。该命令使您能够全面地实现非常严格的 DB2 UDB 安全性审计实践。您可以配置、启动和停止安全性审计,并可以从该工具清除和抽取审计数据。这个过程是非常耗时的日常工作,但很容易用脚本语言使其自动化。例如,在您配置并启动 DB2 审计工具之后,您也许希望创建并调度一个脚本以定期地清除审计数据并将审计数据抽取到报告文件中。

清单 10. DB2 获取审计报告脚本(db2getaudit.bat)

@echo off
::---------------------------------------------------------------------------::
:: This is a Windows Shell Script to collect DB2 Audit Facility information.
:: You must first start the DB2 Audit Facility (db2audit start) and optionally
:: configure scope and status (db2audit configure scope all status both). It
:: flushes and extracts the audit data to the default DB2 Audit Facility
:: directory (sqllibsecurity and then and renames the extracted
:: files to db2audit.YYYYDDMM.HHMMSS.rpt and prunes the DB2 Audit Log.
::---------------------------------------------------------------------------::
set script_name=%~n0
set script_log=%~n0.log
set script_ver=1.0
set DB2INSTANCE=DB2
set DB2DATABASE=SAMPLE

title %script_name% v%script_ver%
echo %script_name% v%script_ver% started on %date% at %time%

::---------------------------------------------------------------------------::
:: Set Script Date Variable
::---------------------------------------------------------------------------::
for /F "tokens=1-4 delims=/ " %%i in ('echo %date%') do (
    set script_date=%%l%%j%%k
    set prune_date=%%l%%j%%k
    )

::---------------------------------------------------------------------------::
:: Set Script Time Variable
::---------------------------------------------------------------------------::
for /F "tokens=1-4 delims=:. " %%i in ('echo %time%') do (
    set script_time=%%i%%j%%k
    set prune_hour=%%i
    )

::---------------------------------------------------------------------------::
:: Set DB2 Path Variable, DB2 Audit Path, and DB2 Audit Log
::---------------------------------------------------------------------------::
if not defined db2path (
   for /F %%i in ('db2set db2path') do (set db2path=%%i)
   echo %script_name% v%script_ver% set db2path to %db2path%
   )

set db2audit_path=%db2path%%db2instance%SECURITY
echo %script_name% v%script_ver% set db2audit_path to %db2audit_path%

set db2audit_file=%db2audit_path%db2audit.log
echo %script_name% v%script_ver% set db2audit_log to %db2audit_file%

::---------------------------------------------------------------------------::
:: Flush DB2 Audit Data to Log File 
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% flushing data to db2audit.log...
db2audit.exe flush 2> %script_log%
if not %errorlevel% == 0 (
   echo ERROR: Unable to flush db2 audit data to log, RC=%errorlevel%
   type %script_log%
   )

::---------------------------------------------------------------------------::
:: Extract DB2 Audit Data from Log File
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% extracting data from db2audit.log...
db2audit.exe extract 2> %script_log%
if not %errorlevel% == 0 (
   echo ERROR: Unable to extract db2 audit data from log, RC=%errorlevel%
   type %script_log%
   )

::---------------------------------------------------------------------------::
:: Rename DB2 Audit Out File (db2audit.YYYYMMDD.HHMMSS.rpt)
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% renaming db2audit.out file...
ren %db2audit_path%*.out *.%script_date%.%script_time%.rpt 2> %script_log%
if not %errorlevel% == 0 (
   echo ERROR: Unable to rename db2 audit out file, RC=%errorlevel%
   type %script_log%
   )

::---------------------------------------------------------------------------::
:: Prune DB2 Audit Log File
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% pruning db2audit.log file...
db2audit.exe prune date %prune_date%%prune_hour% 2> %script_log%
if not %errorlevel% == 0 (
   echo ERROR: Unable to prune db2 audit file, RC=%errorlevel%
   type %script_log%
   )

echo %script_name% v%script_ver% completed on %date% at %time%

然后可以将数据导入或装入到安全性审计数据库,该数据库可以通过触发器和/或存储过程将警报通知自动转发给相应的管理员。

结束语

在本文中,我们已经知道正确维护 DB2 UDB 系统所需的全部管理任务如何依赖于 DB2 管理 API,还知道了如何使用 DB2 命令行工具和 DB2 系统命令从命令行界面执行数据库管理任务。我们演示了如何在 Windows shell 脚本中组合 DB2 命令、DB2 系统命令和操作系统命令,以便使其中的许多任务自动化。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/694276/viewspace-51455/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-09-01

  • 博文量
    187
  • 访问量
    127777