SAP SYBASE IQ:Deletando um DBFILE de uma DBSPACE
Suponha que seja necessário reduzir o tamanho total de uma DBSPACE no SAP IQ, por motivo de super dimencionamento .
O processo é relativamente simples, tomando alguns cuidados como um backup full da base de dados.
Suponha que exista a DBSPACE IQ_1DBSPACE com um tamanho total de 10GB com consumo de apenas 30%. É uma base que não crescerá muito é poderia ter o tamanho total de 5GB.
Pode-se checar o tamanho total da DBSPACE com a procedure:
sp_iqdbspace IQ_1DBSPACE;
Primeiro - verifique os DBFILES fazem parte da DBSPACE:
select convert(varchar(20), DBSpaceName), convert(varchar(20), DBFileName), convert(varchar(70), Path), DBFileSize, Usage, RWMode from sp_iqfile() where DBSpaceName = 'IQ_1DBSPACE';
DBSpaceName DBFileName Path DBFileSize Usage
---------------------------------------------------------------------------------------------------------------------------------
IQ_1DBSPACE IQ_1DBSPACE_001 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_001 1G 30
IQ_1DBSPACE IQ_1DBSPACE_002 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_002 1G 30
IQ_1DBSPACE IQ_1DBSPACE_003 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_003 1G 30
IQ_1DBSPACE IQ_1DBSPACE_004 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_004 1G 30
IQ_1DBSPACE IQ_1DBSPACE_005 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_005 1G 30
IQ_1DBSPACE IQ_1DBSPACE_006 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_006 1G 30
IQ_1DBSPACE IQ_1DBSPACE_007 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_007 1G 30
IQ_1DBSPACE IQ_1DBSPACE_008 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_008 1G 30
IQ_1DBSPACE IQ_1DBSPACE_009 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_009 1G 30
IQ_1DBSPACE IQ_1DBSPACE_010 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_010 1G 30
Neste caso acima, poderiam ser deletados os DBFILES IQ_1DBSPACE_006, IQ_1DBSPACE_007, IQ_1DBSPACE_008, IQ_1DBSPACE_009 e IQ_1DBSPACE_010
Executar um backup full da base:
backup database FULL to '/usr/sap/backup/backup.dmp';
Após o backup full, colocar os DBFILES em modo Read Only:
alter dbspace IQ_1DBSPACE ALTER FILE IQ_1DBSPACE_006 readonly;
alter dbspace IQ_1DBSPACE ALTER FILE IQ_1DBSPACE_007 readonly;
alter dbspace IQ_1DBSPACE ALTER FILE IQ_1DBSPACE_008 readonly;
alter dbspace IQ_1DBSPACE ALTER FILE IQ_1DBSPACE_009 readonly;
alter dbspace IQ_1DBSPACE ALTER FILE IQ_1DBSPACE_010 readonly;
Pode-se verificar se os DBFILES estão em Read Only na coluna RWMode:
select convert(varchar(20), DBSpaceName), convert(varchar(20), DBFileName), convert(varchar(70), Path), DBFileSize, Usage, RWMode from sp_iqfile() where DBSpaceName = 'IQ_1DBSPACE';
Se estiver Okay, iniciar a migração dos dados para os DBFILES de 001 à 005:
sp_iqemptyfile 'IQ_1DBSPACE_006';
sp_iqemptyfile 'IQ_1DBSPACE_007';
sp_iqemptyfile 'IQ_1DBSPACE_008';
sp_iqemptyfile 'IQ_1DBSPACE_009';
sp_iqemptyfile 'IQ_1DBSPACE_010';
Após esvaziar os DBFILES, conferir o status:
select convert(varchar(20), DBSpaceName), convert(varchar(20), DBFileName), convert(varchar(70), Path), DBFileSize, Usage, RWMode from sp_iqfile() where DBSpaceName = 'IQ_1DBSPACE';
DBSpaceName DBFileName Path DBFileSize Usage
---------------------------------------------------------------------------------------------------------------------------------
IQ_1DBSPACE IQ_1DBSPACE_001 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_001 1G 60
IQ_1DBSPACE IQ_1DBSPACE_002 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_002 1G 60
IQ_1DBSPACE IQ_1DBSPACE_003 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_003 1G 60
IQ_1DBSPACE IQ_1DBSPACE_004 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_004 1G 60
IQ_1DBSPACE IQ_1DBSPACE_005 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_005 1G 60
IQ_1DBSPACE IQ_1DBSPACE_006 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_006 1G 1
IQ_1DBSPACE IQ_1DBSPACE_007 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_007 1G 1
IQ_1DBSPACE IQ_1DBSPACE_008 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_008 1G 1
IQ_1DBSPACE IQ_1DBSPACE_009 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_009 1G 1
IQ_1DBSPACE IQ_1DBSPACE_010 /usr/sap/sybaseiq/sapdata/iq_1dbspace/dbs_iq_1dbspace_010 1G 1
Verificar se os DBFILES estão prontos para remoção na coluna OkToDrop:
select convert(varchar(20), DBSpaceName), convert(varchar(20), DBFileName), DBFileSize, Usage, RWMode, OkToDrop from sp_iqfile() where DBSpaceName = 'IQ_1DBSPACE';
DBSpaceName DBFileName DBFileSize Usage RWMode OkToDrop
--------------------------------------------------------------------------
IQ_1DBSPACE IQ_1DBSPACE_001 1G 60 RW N
IQ_1DBSPACE IQ_1DBSPACE_002 1G 60 RW N
IQ_1DBSPACE IQ_1DBSPACE_003 1G 60 RW N
IQ_1DBSPACE IQ_1DBSPACE_004 1G 60 RW N
IQ_1DBSPACE IQ_1DBSPACE_001 1G 60 RW N
IQ_1DBSPACE IQ_1DBSPACE_006 1G 1 RO Y
IQ_1DBSPACE IQ_1DBSPACE_007 1G 1 RO Y
IQ_1DBSPACE IQ_1DBSPACE_008 1G 1 RO Y
IQ_1DBSPACE IQ_1DBSPACE_009 1G 1 RO Y
IQ_1DBSPACE IQ_1DBSPACE_010 1G 1 RO Y
Apagar os DBFILES da DBSPACE:
ALTER DBSPACE IQ_1DBSPACE DROP FILE IQ_1DBSPACE_006;
ALTER DBSPACE IQ_1DBSPACE DROP FILE IQ_1DBSPACE_007;
ALTER DBSPACE IQ_1DBSPACE DROP FILE IQ_1DBSPACE_008;
ALTER DBSPACE IQ_1DBSPACE DROP FILE IQ_1DBSPACE_009;
ALTER DBSPACE IQ_1DBSPACE DROP FILE IQ_1DBSPACE_010;
Verificar a DBSPACE:
select convert(varchar(20), DBSpaceName), convert(varchar(20), DBFileName), DBFileSize, Usage, RWMode, OkToDrop from sp_iqfile() where DBSpaceName = 'IQ_1DBSPACE';
DBSpaceName DBFileName DBFileSize Usage RWMode OkToDrop
--------------------------------------------------------------------------
IQ_1DBSPACE IQ_1DBSPACE_001 1G 60 RW N
IQ_1DBSPACE IQ_1DBSPACE_002 1G 60 RW N
IQ_1DBSPACE IQ_1DBSPACE_003 1G 60 RW N
IQ_1DBSPACE IQ_1DBSPACE_004 1G 60 RW N
IQ_1DBSPACE IQ_1DBSPACE_001 1G 60 RW N
Pode-se ainda executar um check na base de dados após a manobra:
sp_iqcheckdb 'check database';
