Fragen Sie mehr als eine Datenbank in DB2 v10 ab

360
Eigen

Ich schreibe ein Prüfskript, das zuerst Informationen auf dem Linux-Server sammelt und dann über die DB2-CLI eine Verbindung zur DB2 v10-Instanz herstellt, um Abfragen auszuführen. Es funktioniert perfekt mit einer Datenbank auf dem Server. Ich habe eine andere Datenbank erstellt und das Skript ausgeführt. Jetzt mischen 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 Ergebnisse pro Datenbank erhalten oder auf andere Weise deutlich machen, welches Ergebnis wann von welcher Datenbank stammt? Ich habe vorher keine Datenbanknamen, da ich ein externer Prüfer bin.

Das folgende Snippet ist das, was ich ausgeführt habe, nachdem ich Informationen über lokale Linux / AIX-Server erhalten habe und eine Verbindung zu DB2 hergestellt habe:

echo "==============================" >> $working_dir/$logfile ;  for i in `db2 list db directory | grep 'Database name' | awk ''`;  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 '');  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
0

2 Antworten auf die Frage

0
mao

Wenn Sie einfach den Datenbanknamen in den Berichten ($ logfile) aufzeichnen möchten, haben Sie den Datenbanknamen bereits in Ihrem Skript (in der Steuerungsvariablen $ i).

Wenn Sie eine einzige $ logfile (alle Datenbanken enthalten möchten, was eine fragwürdige Entscheidung darstellt), dann geben Sie die Kopf- und Fußzeilen der $ logfile nach erfolgreicher Datenbankverbindung und nach dem Trennen der Verbindung von dieser Datenbank wieder ein. Alles zwischen dem db-Header und der Fußzeile würde dann zu dieser Datenbank gehören.

Eine weitere Option besteht darin, eine $ log-Datei pro Datenbank zu haben, beispielsweise $ dbname. $ Logfile, die einfacher sein kann, insbesondere bei Datenbankbesitzern, die für verschiedene Gruppen / Verantwortlichkeiten in der Organisation gelten.

0
gvphubli.blogspot.com

Eine der Möglichkeiten, die ich gemacht habe, ist wie folgt.

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/) ' | 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 '' > 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 Perl-Snippets für bestimmte Dinge. Ich nutze den DB2-Katalog, um die verfügbaren DBs zu finden / identifizieren und deren Informationen zu sammeln.

Verwandte Probleme