☰

在 DB2 v10 上查询多个数据库

tag-icon • tag-icon linux bash shell-script db2
在 DB2 v10 上查询多个数据库

我正在编写一个审计脚本，该脚本首先在 Linux 服务器上收集信息，然后通过 db2 CLI 连接到 DB2 v10 实例以运行查询。它与服务器上的一个数据库完美配合。我创建了另一个数据库并运行了该脚本。现在我的结果是混合了两个数据库的响应。

我尝试通过 DB2 CLI 连接到第一个数据库并运行脚本，但结果仍然混乱。如何获取每个数据库的结果，或者在需要时清楚地指出哪个结果来自哪个数据库？我不会事先知道数据库名称，因为我是一名外部审计员。

下面的代码片段是我获取本地 Linux/AIX 服务器信息并开始连接到 DB2 后运行的：

echo "=============================="  >> $working_dir/$logfile ;

for i in `db2 list db directory | grep 'Database name' | awk '{print $4}'`;

do

echo "=============================="  >> $working_dir/$logfile ;
echo "Checking Database State"      >> $working_dir/$logfile ;
echo "==============================="  >> $working_dir/$logfile ;

state=$(db2 get db cfg for $i | grep 'HADR database role' | awk '{print $5}');

echo "Current state is $state"  >> $working_dir/$logfile ;

if [ $state = "STANDBY"  ]

then

echo "THIS DATABASE IS THE STANDBY, THIS OPERATION SHOULD BE PERFORMED ON THE PRIMARY"  >> $working_dir/$logfile

else
echo "========================="  >> $working_dir/$logfile ;
echo "CONNECTING TO DATABASE "    >> $working_dir/$logfile ;
echo "========================="  >> $working_dir/$logfile ;

db2  connect to $i  >> $working_dir/$logfile ;

echo "===================================="  >> $working_dir/$logfile ;
echo "5. NODE DIRECTORIES"                   >> $working_dir/$logfile ;
echo "===================================="  >> $working_dir/$logfile ;

db2 list node directory show detail  >>$working_dir/$logfile ;


echo "=============================="  >> $working_dir/$logfile ;
echo "6. DATABASES ON THIS SERVER"     >> $working_dir/$logfile ;
echo "=============================="  >> $working_dir/$logfile ;

db2 list db directory  >>$working_dir/$logfile ;

echo "===================================="  >> $working_dir/$logfile ;
echo "7. AUDIT PARAMETERS IN THE DATABASE"  >> $working_dir/$logfile ;
echo "===================================="  >> $working_dir/$logfile ;

db2audit describe  >> $working_dir/$logfile ;

echo "================================================"  >> $working_dir/$logfile ;
echo "8. CURRENT LEVEL OF INSTALLED DATABASE SOFTWARE"  >> $working_dir/$logfile ;
echo "================================================"  >> $working_dir/$logfile ;

db2level  >> $working_dir/$logfile ;

echo "================================================="  >> $working_dir/$logfile ;
echo "9. APPLICATIONS CURRENTLY ACCESSING THE DATABASE"  >> $working_dir/$logfile ;
echo "================================================="  >> $working_dir/$logfile ;

db2 list applications  >> $working_dir/$logfile ;

echo "===================================="  >> $working_dir/$logfile ;
echo  "10. DATABASE DBA-LEVEL ASSIGNMENTS"   >> $working_dir/$logfile ;
echo "===================================="  >> $working_dir/$logfile ;

db2 "select char(grantee,15) as grantee, char(granteetype,1) as type, char(dbadmauth,1) as dbadmin,
char(securityadmauth,1) as secadmin, char(sqladmauth,1) as sqladmin, char(dataaccessauth,1) as
 access, char(accessctrlauth,1) as accessctrl, char(wlmadmauth,1) as wlmadmin, char(loadauth,1) as load, 
char(createtabauth,1) as createtable, char(bindaddauth,1) as bindadd, char(connectauth,1) as connect, 
char(implschemaauth,1) as implschema, char(libraryadmauth,1) as libadmin from syscat.dbauth" order by grantee  >> $working_dir/$logfile ;

echo "=============================================="  >> $working_dir/$logfile ;
echo " 11. PASSTHROUGH ACCESS FROM OTHER DATABASES"  >> $working_dir/$logfile ;
echo "=============================================="  >> $working_dir/$logfile ;

db2 "select char(grantor,8) as grantor, char(grantortype,1) as type,
char (grantee,15) as grantee, char (granteetype,1) as grantee_type, char(servername,8) as servername
from SYSCAT.PASSTHRUAUTH"  >> $working_dir/$logfile ;

echo "============================================="  >> $working_dir/$logfile ;
echo " 12. ROLES AND MEMBERS IN THE DATABASE"  >> $working_dir/$logfile ;
echo "============================================="  >> $working_dir/$logfile ;

db2 "select char(grantor,8) as grantor, char(grantortype,1) as type,
char(grantee,8) as grantee, char(granteetype,1) as grantee_type, char(rolename,15) as role_name, char(admin,1) as admin from SYSCAT.ROLEAUTH" order by grantee  >> $working_dir/$logfile ;

echo "==============================================================="  >> $working_dir/$logfile ;
echo "13. DISTINCT OWNERS OF TABLES (Should Not Be Public or Users)"  >> $working_dir/$logfile ;
echo "==============================================================="  >> $working_dir/$logfile ;

db2 "select distinct owner from SYSCAT.TABLES"  >> $working_dir/$logfile ;

echo "=================================="  >> $working_dir/$logfile ;
echo "14. ACCESS LEVEL TO SYSTEM TABLES"  >> $working_dir/$logfile ;
echo "=================================="  >> $working_dir/$logfile ;

db2 "select char(grantor,8) as grantor, char(grantee,8) as grantee, char(ttname,33) as tablename, char(controlauth,1) as control, char(alterauth,1) as alter, char(deleteauth,1) as delete, char(insertauth,1) as insert, char(selectauth,1) as select, char(granteetype,1) as grantee_type from sysibm.systabauth 
where grantee not in ('DB2INST1')"  order by grantee  >> $working_dir/$logfile ; 

echo "====================="  >> $working_dir/$logfile ;
echo "15. LIST ALL SCHEMAS"  >> $working_dir/$logfile ;
echo "====================="  >> $working_dir/$logfile ;

db2 "select char(schemaname,15) as name, char(owner,10) as owner, char(auditpolicyname,12) as auditpolicy from syscat.schemata" >> $working_dir/$logfile ;

echo "======================="  >> $working_dir/$logfile ;
echo "16. ACCESS TO SCHEMAS"    >> $working_dir/$logfile ;
echo "======================="  >> $working_dir/$logfile ;

db2 "SELECT char(GRANTOR,8) as grantor, char(grantee,12) as grantee, char(granteetype,1) as type, 
char(schemaname, 10) as schema_name, char(alterinauth,1) as alter, char(createinauth,1) as create, 
char(dropinauth,1) as drop from syscat.schemaauth" order by grantee >> $working_dir/$logfile ;

echo "==================================="  >> $working_dir/$logfile ;
echo "17. DATABASE CONFIGURATION"           >> $working_dir/$logfile ;
echo "===================================="  >> $working_dir/$logfile ;

db2 get db cfg  >>$working_dir/$logfile ;

echo "==================================="  >> $working_dir/$logfile ;
echo "18. DATABASE MANAGER CONFIGURATION"   >> $working_dir/$logfile ;
echo "===================================="  >> $working_dir/$logfile ;

db2 get database manager configuration  >>$working_dir/$logfile ;

echo "=========================================="  >> $working_dir/$logfile ;
echo "19. PUBLIC ACCESS TO SYSTEM CATALOG VIEWS"   >> $working_dir/$logfile ;
echo "=========================================="  >> $working_dir/$logfile ;

db2 "select char(grantee,8) as grantee, char(ttname,30) as table from sysibm.systabauth where tcreator='SYSCAT' and grantee='PUBLIC'"  >>$working_dir/$logfile ;

echo "================================"  >> $working_dir/$logfile ;
echo "20. ACCESS TO SYSTEM TABLESPACE"   >> $working_dir/$logfile ;
echo "================================"  >> $working_dir/$logfile ;

db2 "select char(grantee,8) as grantee, char(tbspace,10) as tablespace from sysibm.systbspaceauth where grantee='PUBLIC'"  >>$working_dir/$logfile ;

echo "=============================="  >> $working_dir/$logfile ;
echo "21. USE OF SYSTEM TABLESPACE"    >> $working_dir/$logfile ;
echo "=============================="  >> $working_dir/$logfile ;

db2 "select char(tabschema,8)as tableschema, char(tabname,8) as tablename, char(tbspace,10) as tablespace from syscat.tables where tabschema not in ('ADMINISTRATOR','SYSIBM','SYSTOOLS') and tbspace in ('SYSCATSPACE','SYSTOOLSPACE','SYSTOOLSTMPSPACE','TEMPSPACE')"  >>$working_dir/$logfile ;



echo "++++++++++++++++++++++++++++++SCRIPT COMPLETED+++++++++++++++++++++++++++++"  >> $working_dir/$logfile ;

db2 terminate 


fi


echo "";

done

echo "Audit Ended `date`" >> $working_dir/$logfile

答案1

如果您只是想在报告（$logfile）中记录数据库名称，那么您的脚本中已经有数据库名称（在循环控制变量 $i 中）。

如果您想要一个 $logfile（包含所有数据库，这似乎是一个值得怀疑的决定），那么在成功连接数据库后以及断开/重置与该数据库的连接后，将页眉和页脚回显到 $logfile。数据库页眉和页脚之间的所有内容都将属于该数据库。

另一个选择是每个数据库有一个 $logfile，例如 $dbname.$logfile，这可能更简单，特别是数据库所有权属于组织中的不同组/职责。

答案2

我所采用的方法之一如下。

os_info=$h_name,$os_name,$os_mversion


# paragraph grep'ing is not possible in Linux, use Perl version
db2 list db directory | perl -00ne 'if ($_ =~ /Indirect/) {chomp($_); printf "%s\n",$_}' | grep -i alias|awk {'print $4'} | while read DB_NAME
do

    db2 connect to $DB_NAME >>/dev/null
    is_connected=$(db2 connect to $DB_NAME | awk {'print $1'} | head -1)
    #echo $is_connected
    if [ "$is_connected" = "SQL1776N" ]; then
            echo '-1, *HADR Secondary*' > xcvsfdgerwersdfs.dat
    else
            db2 "CALL GET_DBSIZE_INFO(?, ?, ?, -1)" | grep "Parameter Value" | head -2 | tail -1 | awk '{print $4}' > xcvsfdgerwersdfs.dat
    fi

    db_size=$(cat xcvsfdgerwersdfs.dat)
    echo $os_info,$db2_version,$DB2INSTANCE,$DB_NAME,$db_size

done

我在 Linux 和 AIX 上有数据库，因此我使用 Perl 代码片段来处理某些事情。我利用 DB2 目录来查找/识别可用的数据库并收集它们的信息。

相关内容