Ich schreibe ein Prüfskript, das zuerst Informationen auf dem Linux-Server sammelt und dann über die DB2-Befehlszeile eine Verbindung zur DB2 v10-Instanz herstellt, um Abfragen auszuführen. Es funktioniert perfekt mit einer Datenbank auf dem Server. Ich habe eine weitere Datenbank erstellt und das Skript ausgeführt. Jetzt vermischen meine Ergebnisse Antworten aus beiden Datenbanken.
Ich habe versucht, über DB2 CLI eine Verbindung zur ersten Datenbank herzustellen und das Skript auszuführen, aber die Ergebnisse sind immer noch gemischt. Wie kann ich bei Bedarf Ergebnisse pro Datenbank erhalten oder auf andere Weise klar angeben, welches Ergebnis aus welcher Datenbank stammt? Ich werde die Datenbanknamen nicht im Voraus haben, da ich ein externer Prüfer bin.
Der folgende Codeausschnitt wird von mir ausgeführt, nachdem ich die Informationen zum lokalen Linux/AIX-Server erhalten habe und mit der Verbindung zu DB2 beginne:
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
Antwort1
Wenn Sie lediglich den Datenbanknamen in den Berichten ($logfile) aufzeichnen möchten, dann haben Sie den Datenbanknamen bereits in Ihrem Skript (in der Schleifensteuervariable $i).
Wenn Sie eine einzelne $logfile möchten (um alle Datenbanken einzuschließen, was eine fragwürdige Entscheidung zu sein scheint), dann geben Sie Kopf- und Fußzeilen nach erfolgreicher Datenbankverbindung und nach dem Trennen/Zurücksetzen der Verbindung mit dieser Datenbank an die $logfile zurück. Alles zwischen dem Datenbankkopf- und -fuß würde sich dann auf diese Datenbank beziehen.
Eine weitere Möglichkeit besteht darin, eine $logfile pro Datenbank zu haben, zum Beispiel $dbname.$logfile. Dies kann einfacher sein, insbesondere wenn die Datenbankeigentümerschaft bei unterschiedlichen Gruppen/Verantwortlichkeiten in der Organisation liegt.
Antwort2
Ich habe es unter anderem wie folgt versucht.
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
Ich habe Datenbanken unter Linux und AIX, daher verwende ich für bestimmte Dinge Perl-Snippets. Ich nutze den DB2-Katalog, um die verfügbaren Datenbanken zu finden/identifizieren und ihre Informationen zu sammeln.