Consulta mais de 1 Banco de dados no DB2 v10

Question

Estou escrevendo um script de auditoria que primeiro coleta informações no servidor Linux e, em seguida, conecta-se à instância do DB2 v10 via db2 CLI para executar consultas. Ele funciona perfeitamente com um banco de dados no servidor. Eu criei outro banco de dados e executei o script. Agora meus resultados estão misturando respostas de ambos os bancos de dados.

Eu tentei conectar-me ao primeiro banco de dados via DB2 CLI e executar o script, mas os resultados ainda estão misturados. Como posso obter resultados por banco de dados ou indicar claramente qual resultado é de qual banco de dados, quando necessário? Não terei nomes de bancos de dados antecipadamente porque sou um auditor externo.

O snippet abaixo é o que eu executo depois de obter as informações do servidor Linux / AIX local e começar a conectar-me ao 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

    

Answer 1

Se você simplesmente deseja registrar o nome do banco de dados nos relatórios ($ logfile), então você já tem o nome do banco de dados em seu script (na variável de controle de loop $ i).

Se você deseja um único $ logfile (para incluir todos os bancos de dados, o que parece ser uma decisão questionável), faça o eco dos cabeçalhos e rodapés para o $ logfile após a conexão do banco de dados bem-sucedida e após a desconexão / connect-reset desse banco de dados. Tudo entre o cabeçalho e o rodapé do banco de dados, então, pertenceria a esse banco de dados.

Outra opção é ter um $ logfile por banco de dados, por exemplo, $ dbname. $ logfile, que pode ser mais simples, especialmente de propriedade do banco de dados para diferentes grupos / responsabilidades na organização.

    

Answer 2

Uma das formas que fiz foi a seguinte.

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

Eu tenho bancos de dados no Linux e no AIX, então estou usando os trechos Perl para certas coisas. Aproveito o catálogo do DB2 para localizar / identificar os bancos de dados disponíveis e coletar suas informações.