☰

在 DB2 v10 上查詢超過 1 個資料庫

tag-icon • tag-icon linux bash shell-script db2
在 DB2 v10 上查詢超過 1 個資料庫

我正在編寫一個審核腳本，它首先收集 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 目錄來尋找/識別可用的資料庫並收集它們的資訊。

相關內容