15
Programa de Capacitação Profissional 2010 Página 1 de 15 SPDATA Serviço de Processamento de Dados LTDA Treinamento de Banco de Dados Firebird Romilton Gonçalves Dias Instrutor Objetivo: Qualificação dos Profissionais para Gerenciamento e Manutenção do Banco de Dados Firebird Objetivos Específicos: 1. Versão do Firebird utilizada no sistema SGH e projeto de homologação para Firebird 2.1 ....................... Pag 2 2. Novidades da versão 2.1 do Firebird .......................................................................................................... Pag 2 3. Diferença de SuperServer e ClassicServer (quando usar e como migrar) .................................................. Pag 4 4. Instalação do Firebird nas estações Windows XP, Vista e Seven em Servidores Windows e Linux ........... Pag 4 5. Ferramentas Nativas Gbak, Gfix, Gstat, Gsec e isql .................................................................................... Pag 5 6. Configurações e cuidados para melhor desempenho ................................................................................ Pag 9 7. Estrutura do banco de dados do sistema SGH ........................................................................................... Pag 9 8. Tipos de Erros e Corrupções ...................................................................................................................... Pag 9 9. Correção do banco de dados corrompido .................................................................................................. Pag 10 10. Ferramentas (isql, Iboconsole, Ibexpert,Putty, Grep ) ............................................................................... Pag 10 11. Comandos Básicos Linux ………………………………………………………………………………………………………………………. Pag 11 12. Formas de configurações do back-up Automático (Linux e Windows) ...................................................... Pag 11 13. Restaurar banco de dados completo ou parcial ......................................................................................... Pag 12 14. Comandos SQL DML (Consultas, update, delete, insert) ............................................................................ Pag 13 15. Integração do Sistema SGH entre os módulos ........................................................................................... Pag 15 Contagem, 27 de Setembro de 2010

Apostila - Banco de Dados Firebird

Embed Size (px)

Citation preview

Page 1: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 1 de 15

SPDATA Serviço de Processamento de Dados LTDA

Treinamento de Banco de Dados Firebird

Romilton Gonçalves Dias Instrutor

Objetivo: Qualificação dos Profissionais para Gerenciamento e Manutenção do Banco de Dados Firebird

Objetivos Específicos:

1. Versão do Firebird utilizada no sistema SGH e projeto de homologação para Firebird 2.1 ....................... Pag 2 2. Novidades da versão 2.1 do Firebird .......................................................................................................... Pag 2 3. Diferença de SuperServer e ClassicServer (quando usar e como migrar) .................................................. Pag 4 4. Instalação do Firebird nas estações Windows XP, Vista e Seven em Servidores Windows e Linux ........... Pag 4 5. Ferramentas Nativas Gbak, Gfix, Gstat, Gsec e isql .................................................................................... Pag 5 6. Configurações e cuidados para melhor desempenho ................................................................................ Pag 9 7. Estrutura do banco de dados do sistema SGH ........................................................................................... Pag 9 8. Tipos de Erros e Corrupções ...................................................................................................................... Pag 9 9. Correção do banco de dados corrompido .................................................................................................. Pag 10 10. Ferramentas (isql, Iboconsole, Ibexpert,Putty, Grep ) ............................................................................... Pag 10 11. Comandos Básicos Linux ………………………………………………………………………………………………………………………. Pag 11 12. Formas de configurações do back-up Automático (Linux e Windows) ...................................................... Pag 11 13. Restaurar banco de dados completo ou parcial ......................................................................................... Pag 12 14. Comandos SQL DML (Consultas, update, delete, insert) ............................................................................ Pag 13 15. Integração do Sistema SGH entre os módulos ........................................................................................... Pag 15

Contagem, 27 de Setembro de 2010

Page 2: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 2 de 15

1 - Versão do Firebird utilizada no sistema SGH e projeto de homologação para Firebird 2.1

Atualmente a versão padrão homologada pela equipe de desenvolvimento da SPdata é Firebird 1.5 que está com a sua última versão Firebird-1.5.6.5026-0 e disponível para download no site oficial www.firebirdsql.org.

O Processo de homologação para versão do Firebird 2.1 está em fase final e será disponibilizado nas próximas versões

2 - Principais novidades do Firebird 2.1

Monitoramento de banco de dados Talvez este seja o recurso mais aguardado para esta versão do Firebird. Aqueles que sempre quiseram saber o que se passava dentro de um banco de dados em um determinado momento, agora poderão extrair essa informação através das tabelas virtuais de monitoramento! Note que, para ter acesso a esse recurso, sua base de dados deve estar com a ODS na versão 11.1. A recuperação das informações de monitoramento é feita através de selects normais, executados nas tabelas de monitoramento. É importante saber que o acesso à informação é feito com a transação associada ao select de monitoramento no isolamento “Consistency”. Com isso, para atualizar as informações recuperadas (refresh), é necessário fechar a transação atual e executar o select novamente, abrindo uma nova transação. Isso garante que as informações de tabelas de monitoramento inter-relacionadas (mestre-detalhe) estarão sempre consistentes. A consulta completa das informações de monitoramento só pode ser realizada pelo owner do banco de dados, ou pelo SYSDBA. Usuários “normais” só têm acesso às informações relacionadas à sua própria conexão com o banco. As tabelas de monitoramento são: MON$DATABASE, MON$ATTACHMENTS, MON$TRANSACTIONS, MON$TRANSACTIONS, MON$STATEMENTS, MON$CALL_STACK, MON$IO_STATS, MON$RECORD_STATS. Existem softwares no Mercado que fazem este monitoramento de modo gráfico um deles é o Sinatica que pode ser baixado do site oficial www.sinatica.com.br

Page 3: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 3 de 15

Cancelando comandos Através das tabelas de monitoramento, é possível cancelar queries ou comandos que estão sendo executados. Para isso, basta abrir uma conexão com o banco de dados e dar um delete na tabela mon$statements, apagando o registro que representa o comando que você quer cancelar. O comando abaixo cancela todos os comandos que estão sendo executados no banco de dados, e que não estejam associados à conexão do SYSDBA (que está executando o cancelamento): delete from mon$statements where mon$attachment_id <> current_connection

Otimização do protocolo de comunicação O Firebird, quando utilizado no modelo “duas camadas” (cliente <-> servidor) em redes de baixa velocidade (ex: Internet), se mostra bastante lento. O culpado, neste caso, é o protocolo de comunicação responsável pela conversa entre a aplicação cliente e o servidor Firebird, que não foi originalmente escrito para ser usado em redes com banda limitada. A solução definitiva para este “problema” virá em alguma versão futura do Firebird, com a criação de um novo protocolo de comunicação. No entanto, isso quebrará a compatibilidade de comunicação com servidores antigos. No Firebird 2.1, algumas rotinas do atual protocolo de comunicação foram otimizadas, de forma a gerar menos “conversa” entre o cliente e o servidor, sem perder a compatibilidade com as versões anteriores do Firebird. Em alguns casos, um ganho de performance de cerca de 40% pôde ser observado nas conexões via internet. Para ter total proveito das otimizações realizadas, é necessário que a conexão esteja usando o fbclient.dll da versão 2.1, e que o servidor também seja 2.1 (ou superior). Nota: Drivers que acessam diretamente o servidor, sem passar pela fbclient.dll (ex: JayBird e .NET Provider) deverão ter seus códigos atualizados para que tirem proveito das otimizações.

Pré-alocação de espaço no disco Sabemos que o arquivo de um banco de dados Firebird cresce de acordo com a necessidade. O Firebird 2.1 introduz a possibilidade de pré-alocar espaço, diminuindo a fragmentação do arquivo, o que causa um impacto positivo na performance de leitura do disco. No momento, este recurso está disponível apenas para a versão Windows. O novo parâmetro “DatabaseGrowthIncrement” pode ser configurado no firebird.conf, e determina o limite máximo (em bytes) que poderá ser pré-alocado na requisição de alocação de espaço para o banco de dados. O padrão é 128MB. Configurar esse parâmetro com o valor zero desativa o recurso de pré-alocação. Nota: Em situações muito específicas, havia a possibilidade de corrupção de um BD, caso a partição onde ele estivesse armazenado ficasse cheia. Para solucionar este problema, o Firebird 2.1, no momento de alocação de espaço, faz com que as páginas criadas sejam imediatamente gravadas no disco. Isso significa que o Firebird está realizando uma operação de escrita adicional em relação ao modelo anterior, o que, em algumas situações, pode afetar negativamente o desempenho.

Backup Incremental Desde a versão 2.0 o Firebird foi incorporado o backup incremental com o aplicativo Nbackup. O nbackup é um novo utilitário de backup que vem junto com o Firebird 2.1 e oferece funcionalidades inexistentes no gbak, ferramenta de backup pré-existente no Firebird, porém, não substitui esse último. Ambos os programas têm as suas vantagens e desvantagens e irão ainda coexistir provavelmente por algum tempo. Neste artigo, aprenda tudo sobre esse poderoso utilitário. Sintaxe C:\banco> nbackup -B 0 sghdados.001 backnivel0_1-set-2010.nbk C:\banco> nbackup -B 1 sghdados.001 backnivel1_1-set-2010.nbk C:\banco> nbackup -B 2 sghdados.001 backnivel2_1-set-2010.nbk

Page 4: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 4 de 15

3 - Diferença de SuperServer e ClassicServer (quando usar e como migrar) Super Server e Classic Server O Firebird possui duas versões: a versão Super Server a versão Classic Server. Entre as duas a diferença está na forma como está estruturada o gerenciamento das conexões dos clientes e processamento.

1. Na versão Super Server os recursos (valores recuperados do disco, consultas feitas) são compartilhados por todos os clientes conectados. Super vem de supervisor Ou seja somente um processo rodando no servidor para todos usuários.

(fbserver) Caso pane terá de Parar o processo e todos os usuários! 2. Já na versão Classic Server, cada cliente tem sua própria “instância do banco de dados” (fb_inet_server) e

comunicam-se entre si através de um gerenciador de transações (fb lock mgr). Ou Seja um processo no servidor para cada conexão no banco de dados caso de pane pode idenificar.

Super Server ou Classic Server Qual é Melhor? Nenhuma das duas versões é melhor ou pior, cada uma adequa-se melhor a cada caso ou seja, necessita de análise. Para determinadas situações a versão Super Server é mais recomendada (Servidor com pouca memória RAM, número médio ou pequeno de clientes conectados, Servidor uniprocessado). Em outras situações a versão Classic Server sai-se melhor (Muita memória RAM disponível, Servidores multiprocessados muitas conexões simultâneas). A vantagem da versão Classic Server é tratar os usuários de forma independente, Uma desvantagem da mesma é o consumo elevado da memória RAM (mas caso o server tenha muita memória RAM isto deixa de ser uma desvantagem). Para migrar de Super Server para Classic Server no Linux precisa alterar os buffers de páginas de dentro da pasta /opt/firebird/bin rodar o comando gfix -b 75 /work/banco/sghdados.001 e o xinet.conf alterando a quantidade de instâncias, o arquivo de configuração fica dentro da pasta /etc instances = 3000 cps = 50 100

No caso do Windows alterar somente os buffers de páfinas com o comando gfix -b 75 -user sysdba -pass senha sghdados.001 (lembrando que o arquivo gfix fica na pasta arquivos de programas e pode ser copiado para dentro da pasta banco. 4 - Instalação do Firebird nas estações Windows XP, Vista e Seven

Instalação Firefird Windows Baixe o arquivo Firebird-1.5.6.5026-0-Win32 http://www.firebirdsql.org Escolha a arquitetura Super server ou Classic e marque a opção copiar Biblioteca para pasta de system Como defaul a senha do sysdba é masterkey Para alterar via gráfico Pode ser no Ibosonsole Na opção usuário e digite Por cima da senha existente e grave No windows fica C:\Arquivos de programas\Firebird\Firebird_1_5

ATENÇÃO Windows Vista e Seven Se você estiver instalando em Windows Vista ou Seven, o instalador opção para instalar o Control Panel applet deve ser desativado para evitar ter que quebrar o Painel de Controle do seu sistema Windows.

Page 5: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 5 de 15

Instalação Firebird no Linux Se você baixou o Firebird em .rpm copie o mesmo para o servidor Linux e digite rpm -Uvh FirebirdSS-1.5.6.5026-0.nptl.i686.RPM Para desinstalar rpm -e FirebirdSS-1.5.6.5026-0.nptl.i686 Se você baixou o Firebird em .tar Para instalar a versão nptl super Server checar se o servidor suporta getconf GNU_LIBPTHREAD_VERSION tar -xzf FirebirdSS-1.5.6.5026-0.nptl.i686 cd / FirebirdSS-1.5.6.5026-0.nptl.i686 ./install.sh Vai pedir a senha sysdba escolha e de enter Geralmente o Firebird é instalado na pasta opt/firebird Para a versão Classic Server configurar o xinet.d aumentar as instâncias algumas distribuições do Linux necessitam de instalar libstdc++ No caso do Linux os pacotes Super Server e Classic server são separados o Classic Server FirebirdCS-1.5.5.4926-0.i686 tema diferença pelas letras CS 5 - Ferramentas Nativas Gbak, Gfix, Gstat, Gsec e isql Gbak: Esta ferramenta é responsável por duas operações no banco de dados: efetuar o backup e restaurar o banco de dados a partir de um backup (restore). Back-up Opções do Gbak para Back-up são -b Back-up -v visualizar o processo -l ignora as limbos -se utiliza api de serviço -g ignora a coleta de lixo -ig ignora erros na base (recomendado realizar somente apos o gfix) -pass password do banco -user sysdba -z mostra a versão do firebird No linux o comando fica da seguinte forma /opt/firebird/bin/./gbak -b -v -l -g -user sysdba -pass masterkey -se localhost:service_mgr /work/banco/sghdados.001 /work/banco/001.fbk No Windows fica na pasta Arquivos de programas/firebird/bin/ Você pode copiar o gbak.exe para a pasta banco e executar CMD gbak -b -v -l -g -user sysdba -pass masterkey -se localhost:service_mgr sghdados.001 001.fbk Restore Opções do Gbak para restore -c cria uma nova base -r substitui o arquivo do banco -i inativa os índices -use preenche todo o espaço da página(não usar) -p define o tamanho da pagina -z mostra a versão do firebird -v visualiza o processo - user sysdba -pass senha do banco No linux o comando fica da seguinte forma /opt/firebird/bin/./gbak -c -v -p 8192 -user sysdba -pass masterkey -se localhost:service_mgr /work/banco/001.fbk /work/banco/sghdados.002 (segurança) De preferência reiniciar o server ou tirar as permissões de gravação do banco anterior chmod 000 sghdados.001 e dar permissões ao sghdados.002 chmod 777 sghdados.002 No Windows fica na pasta Arquivos de programas/firebird/bin/ Você pode copiar o gbak.exe para a pasta banco e executar CMD gbak -c -v -p 8192 -user sysdba -pass masterkey 001.fbk sghdados.002 Obs: (no caso de fazer o backup via linha de comando no Windows ou Linux incluir a opção -se localhost:service_mgr para chamar a API de serviço do firebird porque quando não coloca ele usa o protocolo TCPIP que é mais lento por isso que o back-up e restore via linha de comando demora mais do que no iboconsole ibexpert e outras ferramentas que usam a API de serviço) isto somente quando for o superserver porque o classic Server já o faz.

Page 6: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 6 de 15

Back-up No Iboconsole

Restore no Iboconsole

Para que fazer Back-up Um Backup e Restore irá fazer com que: - As páginas (“pages”) na Base de Dados utilizadas pelas tabelas e índices sejam alocadas de forma contígua; - Os Índices sejam recalculados. A árvore de índices seja reconstruída. - A selectividade dos índices seja recalculada para todos os índices. - As páginas TIP (“transaction information pages” – páginas que contém toda a informação sobre as transações) que contém transações antigas (old transactions) e transações correntes (“current transactions”) sejam inicializadas e os contadores das transações sejam reiniciados. - No caso de corrupção após rodar o gfix para criar uma nova estrutura. - Todas as páginas de dados que não são usadas sejam eliminadas. - Nenhuma versão antiga (“back version”), “delta” ou registo esteja presente. - Nenhuma página de índice de ‘garbage’ (lixo) esteja presente. - Todas as tarefas são executadas por um sweep numa conexão única

Page 7: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 7 de 15

Qual Tamanho de página usar? Existem quatro coisas relacionadas ao tamanho de página: profundidade dos índices, tamanho do cache do BD, registros por página e tamanho do cluster de disco. A resposta mais simples é usar 4096, 8192 ou 16384. (4k, 8k ou 16k) A estrutura básica dos índices é uma árvore B-TREE, portanto a profundidade de um índice depende do tamanho da página. Páginas pequenas (1K-2K) vão produzir árvores de quatro níveis sem precisar de muita quantidade de dados (ex: algumas centenas de milhares de chaves do tipo inteiro). É recomendado que a profundidade de um índice nunca passe de 3 (três). Cache do banco o tamanho do cache no Firebird é determinado multiplicando-se o valor do buffer pelo tamanho da página devemos ter o cuidados para não consumir toda memória RAM e um cuidado maior com o Classic Server que não compartilha memória ou seja cada conexão multiplica a quantidade do buffers x tamanho da página. Gfix O GFix é a ferramenta de manutenção de bancos de dados Firebird. Através dela é possível executar uma série de operações de manutenção no banco de dados visando corrigi-lo tanto para continuar a usá-lo, quanto para executar um posterior backup. Operações de manutenção de performance também são feitas através desta ferramenta. A sintaxe básica do GFIX é: ./gfix -v -f -user sysdba -pass masterkey /work/banco/sghdados.001 As Opções são: -a acivate (ativa uma shadow) -b buffers (define a quantidade de paginas ficarão armazenadas em buffers) -c commit (comita uma limbo) -f full (avalia toda a base) -force (força um shutdow) -h housekeeping (intervalo do sweep) -i (ignora erros) -list (lista limbos) -m (prepara banco corrompido para backup e restore) -online (coloca o banco online) -pass (senha master) -sweep (realiza limpeza e move odt) -shut (para o serviço) -use (usar todo espaço das novas pagina) -user sysdba -v (valida toda a estrutura do banco) -w (muda escrita do banco -z (mostra a versão do Firebird) Sintaxe gfix –v –f –user sysdba –pass masterkey /work/banco/sghdados.001 Gstat Esta ferramenta tem a função de mostrar a estatística do banco de dados -a dados e índices -d paginas de dados -i índices -l log da pagina -t nome das tabelas -h cabeçalho da pagina -user sysdba -pass senha do banco -s relacionamento -z versão -all todas as informações do banco Sintaxe gstat –h /work/banco/sghdados.001 Gsec Esta ferramenta tem a função de gerenciar o usuário No linux cd /opt/firebird/bin No Windows c:\Arquivos de programas\Firebird\Firebird_1_5\bin Para modificar a senha digite gsec -user sysdba -password masterkey <Enter> GSEC>> modi sysdba -pw hmcspd <Enter> GSEC>> quit <Enter> Pronto sua nova senha é hmcspd No caso do linux pode também acessar o servidor pelo putty Execute :na pasta /opt/firebird/bin/ ./changeDBAPassword.sh Digite a senha ATIGA e tecle enter. Digite a senha NOVA e tecle enter.

Page 8: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 8 de 15

O que é Forced-Writes? O termo forced-writes vem do inglês e significa forçar-escritas. Vamos pensar no que acontece quando executamos um comando qualquer que adiciona, altera ou apaga dados do banco de dados. Por exemplo: insert into clientes(codigo, nome) values (1, "João") Forced-Writes Ligado O comportamento padrão do Firebird é gravar estes dados imediatamente no disco. Isto é o Forced-Writes. O Firebird grava tudo no disco assim que recebe o comando. Desta forma depende diretamente do desempenho do HD

Forced-Writes Desligado Quando o Forced-Writes está desligado, o Firebird não grava tudo imediatamente no disco. Ao invés disto, ele deixa estas páginas em memória por um tempo. Durante este tempo ele acumula em memória outras páginas que também deverão ser escritas no disco. Por exemplo, se logo em seguida você emitir outro comando: insert into clientes(codigo, nome) values (2, "Maria”) Estas novas páginas não vão diretamente para o disco e sim para a memória, junto com as páginas geradas pelo primeiro comando. Quando esta memória estiver completa ou quando o tempo de permanência nela expirar, todas as páginas serão escritas no disco de uma só vez isto torna a escrita mais eficiente e muito mais rápida.

Page 9: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 9 de 15

6 - Cuidados para bom funcionamento e melhor desempenho 1-Utilizar a versão FirebirdSS-1.5.6.5026-0.nptl.i686, back-up na versão atual e restore na 1.5.6 versões Classic Server devem ser avaliadas antes de migrar. 2-Verificar a necessidade de realizar back-up restore (ler o Gstat) 3-Tamanho de página de 4096 acima qualquer uma atende perfeitamente. 4-Sempre que der um shutdown no banco coloca-lo online 5-Colocar páginas em buffers para agilizar consultas (cuidado com RAM) no Classic Server não passar de 75 6-Mudar a escrita utilizando recursos do server (analisar necessidade) 7-Manter o banco fazendo reserva de espaço na página 8- Copiar a fbclient.dll da instalação para as estações c:\spdatai\ 9- No caso de servidor Windows verificar Fragmentação do disco 10- Desabilitar o sweep interval e programar em horário mais tranqüilo (se desabilitar o sweep é obrigatório programar algum horário, o sweep tem um papel fundamental no desempenho do banco de dados) 11- Não utilizar shadows caso necessite de bom desempenho 12- Servidor dedicado ao banco de dados (quando possível) 13- Instalar o Firebird nas estações 14- Nunca Copie o Banco de dados em USO (Ctr C Ctr V) 15- Back-up somente via gbak os outros fazem cópia 16- Não realizar back-up e restore na estação e copiar para o Linux 17- Implantar uma estrutura correta Super Server ou Classic Server 7 - Estrutura do banco de dados do sistema SGH Conhecendo a estrutura do SGHDADOS. As duas letras referem ao módulo do Sistema exemplo CC Centro cirúrgico, CO Contabilidade, CR Central de Regulação, CT Controle Orçamentário, DI Diversos, FA Faturamento Ambulatório, FC Faturamento de Convênios, FH Faturamento de AIH, FI AIH antiga, FP Folha de Pagamento, GC Pregão, GE Gestão de Estoque, GP Patrimônio, MF Financeiro, PE Ponto eletrônico, PF Plano de Saúde, PR Prontuário Eletrônico, PS Plano de Saúde, RE Recepção externa, RI Recepção Interna, RP RPA, SC SAC, SI SADT (Exames), SH Hemoterapia, ST Segurança do Trabalho, TB Tabelas, VS Vigilância Sanitária, ZN Zoonóses. 8 - Tipos de Erros e Corrupções linuxserver (Client) Thu May 18 14:42:47 2006 /opt/firebird/bin/fbguard: guardian starting bin/fbserver 14 Esta mensagem indica que o banco de dados foi reiniciado. Ela ocorre tanto quando o Firebird é reiniciado quando também quando ocorre algum problema grave com o banco de dados e ele é finalizado. Neste caso o guardião (fbguard) reinicia o processo do banco de dados. No caso do Firebird Classic, isto não ocorre, pois a cada conexão de um usuário uma nova instância do banco de dados é inicializada. gbak: restoring data for table GLANSAI gbak: ERROR: validation error for column ID_GELANSAI, value "** null **" gbak: ERROR: warning -- record could not be restored gbak: Exiting before completion due to errors Indica que um campo possui um valor nulo (NULL) quando não pode possuir. Neste caso,temos que procurar os campos ID_GELANSAI na tabela GELANSAI que possuem NULL como valor. Neste caso o SQL seria: SELECT * FROM GELANSAI WHERE ID_GELANSAI IS NULL; E informar um valor para este campo nos registros achados; UPDATE GELANSAI SET IDGELANSAI=0 WHERE IS NULL

Page 10: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 10 de 15

Como encontrar duplicidade SELECT ANO, MES, CDC, DATA, ITEM, DOC FROM GEKONCDC GROUP BY ANO, MES, CDC, DATA, ITEM, DOC HAVING COUNT(DOC)>1 Assim que localizar a duplicidade avalie junto ao responsável pelo setor qual deverá ser excluida, exclua a duplicidade e ai sim realiza o processo de back-up e restore. Caso não aparecer a duplicidade no select devemos tomar outra medida que é parar o sistema e exclur a PK realizar o back-up e restore e rodar novamente o select se ainda sim não encontrar crie novamente a PK com os devidos campos. O que compõe o Group By são os campos pertencentes ao PK caso necessitar utilizar em outra tabela é só substituir os referidos campos para os que tiverem na PK da tabela. 9 - Correção do banco de dados corrompido Caso ocorra do banco de dados corromper, o procedimento correto é solicitar que todos saiam do sistema e garantir que não acessaram desabilitando o sistema e tomando os devidos cuidados. Neste caso devemos rodas os seguintes comandos de dentro da pasta opt/firebird/bin ./gfix -v -f user sysdba -pass masterkey /work/banco/sghdados.001 ./gfix -m -f -i user sysdba -pass masterkey /work/banco/sghdados.001 ./gbak -b -v -l -g -ig -user sysdba -pass masterkey -se localhost:service_mgr /work/banco/sghdados.001 /work/banco/001.fbk ./gbak -c -v -p 8192 -user sysdba -pass masterkey -se localhost:service_mgr /work/banco/001.fbk /work/banco/sghdados.002 No caso do Windows não precisa colocar ./e o caminho é c: d: ou ip do servidor:\banco\sghdados.001 e tem de ser rodado de dentro da pasta C:\Arquivos de programas\Firebird\Firebird_1_5\bin ou copiar o gfix para pasta banco e rodar por lá 10 - Ferramentas de manipulação ISQL O isql é uma ferramenta nativa de acesso ao banco de dados sem modo gráfico que acompanha na instalação do firebird com ele você pode manipular os dados e a estrutura do banco com comandos DDL e DML, existem ainda os comandos nativos como: Comando SHOW: Se você pensa que para listar todas as tabelas do banco de dados é necessário digitar um comando DML enorme, envolvendo as tabelas de sistema e tudo mais, a resposta é não! Digite SHOW TABELES; e pronto! você tem uma lista de todas as tabelas de seu banco, para listar uma especifica basta informa o nome show table ricadint;. Esse comando funciona também para triggers, views, procedures, indices, index, a própria database, version, system, roles, domains, exceptions, filter, functions, generators e grants; OBS: Comandos do tipo "show" são rodados sobre transações do tipo READ COMMITED Grep Utilitário usado para localizar nome ou parte de um arquivo Em nossa empresa utilizamos para encontrar nome de campos em scripts Exemplo dentro da pasta scripts executar o comando grep -i nome do campo *.sql Putty PuTTy é um cliente elaborado para protocolos SSH, Telnet e Rlogin. Com ele é possível logar-se a um computador multi- usuário através de outro, estabelecendo assim uma conexão remota entre cliente e servidor. Por exemplo, você inicia o programa e conecta-se a outra, desta forma uma nova janela será aberta para o envio de comandos e afins entre as duas máquinas conectadas. É possível trabalhar com um sistema Unix a partir de seu PC com este aplicativo. Usado para acessar servidores Linux

Page 11: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 11 de 15

11 - Comandos básicos Linux ls -l (lista), cp (copia), mv (move), rm (deleta), mkdir (cria diretório), vi (edita arquivo), cat (edita conteudo), kill (mata processo), find (pesquisa), grep (localizar), cd /(navegar), time (calcula o tempo), fuser (lista processo), htop (lista processos gráfico), top, ps aux (lista processos), netstat (estatística rede), nbtstat (identifica estação), vmstat (estatística da memoria virtual), df (verifica espaço livre), free, (status da memória) date (data do servidor), chmod (define permissões do arquivo, ping (verifica conexão), arp (exibe tabela arp). vmstat -n 1 50 (vai contar 50 processo a cada um segundo e trazer informãção da memória ram, processador, leitura no disco… ps aux | grep fb_inet_server| wc –l (vai contar quantos processos existem com este nome rodando no servidor se você trocar de fb_inet_server para :3050 trará todos os processos que utilizam a porta 3050 tambem pode ser o netstat no lugar do ps aux htop, top (trazem informações de uso dos processadores e memória) date (mostra a data e hora do servidor) date mmddhhmmaaaa Onde: mm = Mês dd = Dia hh = Hora mm = Minutos aa = Ano (2001 = 20) aa = Ultimas duas casa do Ano (2001 = 01) # /sbin/clock –w time ./gbak -b -v -user sysdba -pass masterkey /caminho do banco mostrará o tempo real que levou para fazer o back-up 12 - Formas de configurações do back-up Automático (Linux e Windows)

Você pode criar um script e programar para rodar no servidor No caso do Linux você pode usar o crontab Sintaxe Crontab -e vai abrir uma console e a linha de comando pode ficar 00 06,12,18,00 * * * /banco/scriptdebackup.sh - 00 significa os minutos 06,12,18,00 são as horas que o backup será realizado * * * todos os dias semanas e meses No corpo do script você pode colocar da seguinte forma date > /var/log/gbak_d.log echo echo "Iniciando o Backup do Banco de Dados." echo cd / /opt/firebird/bin/gbak -b -g -l -se localhost:service_mgr /work/banco/sghdados.001 /work/backup/sghd_$(date +%d-%m-%Y-%H.%M).gbk -user sysdba -password masterkey >> /var/log/gbak_d.log echo # echo date >> /var/log/gbak_d.log echo "Fim do Backup." # echo date >> /var/log/gbak_l.log cd /work/backup gzip *.gbk

Page 12: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 12 de 15

No caso do Windows você pode usar o agendador de tarefas e criar um arquivo.bat com os comandos do gbak ou utilizar ferramentas existentes como GBAK Scheduler

13 - Restaurar banco de dados completo ou parcial Para restaura um banco de dados você irá precisar que o backup tenha sido feito em uma versão igual ou inferior do Firebird exemplo backup na versão 1.5 e restore na a.5 em diante, o backup necessita que tenha sido feito de modo transportável default do Firebird caso não tenha sido feito ele só restaur ano próprio servidor que foi feito backup Sintaxe ./gbak -c -v -p 8192 -user sysdba -pass masterkey -se localhost:service_mgr /work/banco/001.fbk /work/banco/sghdados.002 Ou pelo Iboconsole conforme já mostrado acima

Caso presisar voltar apenas uma tabela ou um registro, você irá precisar utilizar uma ferramenta IBexpert que será falado em outra edição mais aprofundado. Após registrado o banco de dados, abra o editor de SQL F12 e selecione a tabela ou o registro que necessita restaura

Page 13: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 13 de 15

14 - Comandos SQL DML (Consultas, update, delete, insert) Linguagem de manipulação dos dados (DML) -Linguagem que define os comandos de manipulação e operação dos dados (comandos de consulta e atualização dos dados dos arquivos). 1 - Comandos DML Objetivo: 1.1- Insert Incluir um novo registro em uma tabela do Banco de Dados. 1.2- Update Atualiza os dados de um ou um grupo de registros em uma tabela do Banco de Dados. 1.3- Delete Deletar um ou um grupo de registros em uma tabela do Banco de Dados. 1.4- Select Selecionar um conjunto de registros em uma ou mais tabelas que atenda a uma determinada condição definida pelo comando. Algumas funções utilizadas no comando Select. a) COUNT(*) Retorna a quantidade de registros existentes no campo especificado. Quando a opção * é utilizada o resultado é a quantidade de registros existentes. Quando é referenciado o nome de um campo retorna a quantidade de valores existentes na coluna. b) SUM (Valor) Retorna a soma dos valores existentes no campo especificado. c) AVG (Valor) Retorna a média dos valores existentes no campo especificado. d) MAX (Campo) Retorna o maior valor existente no campo especificado. e) MIN (Campo) Retorna o menor valor existente no campo especificado f) DISTINCT Quando a opção DISTINCT é utilizada são consideradas apenas os diferentes valores existentes no campo g) Where A cláusula WHERE é usada para extrair apenas os registros que cumprir um determinado critério. h) and A cláusula and é usada após o where para agregar critérios na consulta. i) Group by agrupa o resultado em subconjuntos que possuem valores correspondentes em uma ou mais colunas. J) Order by ordena o resultado pelo campo que é determinado como default é crescente, mas pode ser desc k) Between O operador BETWEEN serve para indicar um determinado intervalo de valores dentro de uma query l) Like Com este operador, podemos comparar cadeias de caracteres utilizando padrões de comparação para um ou mais caracteres. Normalmente, o caracter percentual (%) substitui um ou mais caracteres e sublinha (_) substitui um caracter Junções Tipos de Joins: LEFT JOIN, RIGHT JOIN, INNER JOIN e FULL JOIN LEFT JOIN: Como podemos observar, e a própria sintaxe indica, essa cláusula trabalha com os dados da tabela "Esqueda" como sendo os dados principais, ou seja, de acordo com o exemplo abaixo, o LEFT JOIN mostrará o que esta na geitens (esquerda), podendo trabalhar também com qualquer outro dado da gevalmem com a mesma chave encontrada na geitens select a.cod,a.nome,a.estoque,b.item,b.qtde,b.lote from geitens a left join gevalmem b on a.cod=b.item where a.estoque <> (select sum(b.qtde) from gevalmem b where a.cod=b.item) and b.qtde>0 and a.classif = 2 order by a.cod O RIGHT JOIN retorna o que estiver na Tabela1 e Tabela2 com a mesma chave, e sendo o inverso do LEFT JOIN a tabela principal se torna a tabela da " Direita ", gevalmem select a.cod,a.nome,a.estoque,b.item,b.qtde,b.lote from geitens a right join gevalmem b on a.cod=b.item where a.estoque <> (select sum(b.qtde) from gevalmem b where a.cod=b.item) and b.qtde>0 and a.classif = 2 order by a.cod O INNER JOIN nos retorna apenas o que esta na geitens e gevalmem com a mesma chave. Já O FULL JOIN retorna o que estiver na Tabela1 e Tabela2 levando em conta o seu significado FULL, ou seja, completo. Portanto O FULL JOIN retorna tudo o que há nas Tabelas selecionadas Exemplos de comandos SQL Select * from ricadpac (lista todos os registros da tabela de pacientes) Select nome,mae,nasc, from ricadpac (lista somente nome, mae e data de nascimento da tabela de pacientes)

Page 14: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 14 de 15

Select nome,mae,nasc from ricadpac where nasc >’01.01.2008’ (lista somente quem nasceu de 01/01/2008 em diante) Select nome,mae,nasc from ricadpac where nasc >’01.01.2008’ and nome like ‘Maria%’ (lista somente quem nasceu de 01/01/2008 em diante e que o nome começe com Maria) Select nome,mae,nasc from ricadpac where nasc >’01.01.2008’ and nome like ‘Maria%’ and pront in (select pront from ricadate) (lista somente quem nasceu de 01/01/2008 em diante e que o nome inicie com Maria e tenha um registro gerado na tabela de atendimentos) Select count(*) from ricadpac (conta todos os registros da tabela) Select distinct(cidade) from ricadpac (lista todas as cidades do cadastro de pacientes trazendo apenas uma de cada) Exemplos Práticos do Dia a Dia Soma valor de saída de um item do estoque select sum(vlrmed)as soma_do_item from gelansai where item =19 Update por intervalo update tbtabthm set tipoato=15 where tab=37 and cod between ' 28010000' and ' 28160002' Saber quantidade de contas do ano de 2008 select mespro, anopro,con as convenio, tipo_cta, count (cod)as conta from fcctaext where fechada = ‘A' and anopro=2008 and alta between '01.01.2008' and '31.12.2008' group by mespro, anopro, con, tipo_cta order by mespro Guias digitadas na recepção diferente do Faturamento select a.reg,a.conv,a.segurado,a.senha,a.dt_ate,a.guia as guia_atendimento,b.guia as guia_faturamento from recadate a left join fcctaext b on a.reg=b.cod where a.conv >3 and a.guia <> b.guia order by dt_ate desc Matrículas digitadas na recepção diferente do Faturamento select a.reg,a.conv,c.nome "Paciente",a.dt_ate,a.usuario,a.matricula "Matricula de atendimento", b.matricula "Matricula de faturamento",data_emi,usu_emi "Usuário Faturamento" from recadate a inner join fcctaext b on a.reg=b.cod and a.mespro=b.mespro and a.anopro=b.anopro inner join ricadpac c on a.pront=c.pront where a.conv >2 and a.matricula <> b.matricula and tipo_cta='E' and b.anopro=2009 and data_emi<>'30.12.1899' order by dt_ate desc Saber quais prontuários não tem atendimentos ou internação: select a.dtcad, a.pront as prontuario, a.nome,b.reg as internacao, c.reg as atendimento, d.reg as atendimento_SADT from ricadpac a left join ricadint b on a.pront=b.pront and a.tipopac=b.tipopac and a.otmu=b.otmu left join recadate c on a.pront=c.pront and a.tipopac=b.tipopac and a.otmu=c.otmu left join sicadate d on a.pront=d.pront where b.reg is null and c.reg is null and d.reg is null order by a.dtcad desc Motivo de alta diferente do faturamento select a.reg,a.conv,a.entrada,a.alta,a.motivo "Interna",c.nome,a.useralt,b.motivo "Faturamento",d.nome,usu_emi from ricadint a inner join fcctaext b on a.reg=b.cod and a.anopro=b.anopro and a.mespro=b.mespro inner join tbmotalt c on a.motivo=c.cod inner join tbmotalt d on b.motivo=d.cod where b.anopro=2009 and a.motivo<>b.motivo order by reg Procedimentos mais usados na internação select count(procto) TOTAL,procto,NOME_PROC from fclanext where anopro=2009 and tipo_cta= 'I'and tipo=2 group by procto,NOME_PROC order by 1 desc Cep fora da tabela do SUS e paciente atendido conv 1 select pront,nome,mae from ricadpac where cep not in (select cep from tbsuscep) and pront in (select pront from ricadint where conv = 1) order by pront

Page 15: Apostila - Banco de Dados Firebird

Programa de Capacitação Profissional 2010

Página 15 de 15

Update em uma tabela buscando informações de outra update tbrefuni u set u.codref=(select ref_tiss from tbmatmed where tab=99 and cod= (select bras from tbmatmed where tab=u.tab and cod=u.cod)) where u.tab=2 Tempo médio de entrega das contas select con "Convênio",avg(cast(cast(data_apres as timestamp) - alta as integer)) "Tempo médio de entrega das contas" from FCCTAEXT where anopro=2009 and data_apres is not null and data_apres <> '30.12.1899' group by con 15 - Integração entre os módulos do sistema