Arquivos

Archive for the ‘Banco de dados’ Category

Select com IF / CASE no SQL Server

Sempre tenho tal necessidade, segue um exemplo para posterior consulta:

SELECT COLUMN_NAME as Nome, DATA_TYPE as Tipo,
CASE
WHEN Character_Maximum_length is null and DATA_TYPE = ‘int’
THEN 10
WHEN Character_Maximum_length is null and DATA_TYPE = ‘decimal’
THEN 20
ELSE Character_Maximum_length
END as Tamanho,
Is_Nullable as NULLS
FROM [INFORMATION_SCHEMA].[COLUMNS]

Set NOCOUNT em Stored Procedures

Quando invocamos uma query, normalmente temos um output como:
Message:
(11 row(s) affected)

Muitas vezes temos a necessidade de inibir a mensagem retornada, seja para poder efetuar uma tratativa adequada as informações de output ou seja para ganhar um mísero desempenho – veja, você estará deixando de trafegar esta mensagem em sua rede!

Para desabilitar este recurso, basta em sua Stored Procedure incluir a linha:
SET NOCOUNT ON

Enjoy!

Consultando dicionário de dados no SQL Server

A seguinte query é muito útil quando é necessário consultar o dicionário de dados no SQL Server:

SELECT COLUMN_NAME as Nome, DATA_TYPE as Tipo, Character_Maximum_length as Tamanho, Is_Nullable as NULLS
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME = ‘COD_USUARIO’

[]s

Enjoy!

Formatando campos no DB2

É comum a necessidade de formatarmos campos em selects. Um exemplo boçal é o CNPJ. É muito menos custoso formatar na sua query do que na sua aplicação (java por exemplo)… Então, segue um exemplo de como formatar um CNPJ.

Supondo que seu campo CNPJ tenha o nome CCNPJEMPR, e o conteúdo do mesmo seja:

086547761002422

E você queira retornar assim:

086.547.761/0024-22

Basta utilizar na sua query:

SELECT
SUBSTR(CAST(RIGHT(CONCAT('000000000',CAST(empresa.CCNPJEMPR AS VARCHAR(15))),15) AS VARCHAR(15)),1,3) || '.' ||
SUBSTR(CAST(RIGHT(CONCAT('000000000',CAST(empresa.CCNPJEMPR AS VARCHAR(15))),15) AS VARCHAR(15)),4,3) || '.' ||
SUBSTR(CAST(RIGHT(CONCAT('000000000',CAST(empresa.CCNPJEMPR AS VARCHAR(15))),15) AS VARCHAR(15)),6,3) || '/' ||
SUBSTR(CAST(RIGHT(CONCAT('000000000',CAST(empresa.CCNPJEMPR AS VARCHAR(15))),15) AS VARCHAR(15)),9,4) || '-' ||
SUBSTR(CAST(RIGHT(CONCAT('000000000',CAST(empresa.CCNPJEMPR AS VARCHAR(15))),15) AS VARCHAR(15)),14,2) AS CNPJ_FORMATADO
FROM
CLIENTES;

A função SUBSTR irá pegar os “pedaços” do campo, repare na primeira linha, que especifico 1,3, isso quer dizer, que ela deve pegar 3 caracteres a partir da primeira posição do campo, no exemplo 086.
Onde especifico 9,4, quero dizer que devo pegar 4 caracteres a partir da posição 9, no exemplo: 0024

Por fim, utilizo CAST, RIGHT e CONCATS para formatar o campo, e em casos que tenha 0 (ZEROS) a esquerda, que os mesmos sejam considerados.

Enjoy!

Debugando Stored Procedures

fevereiro 17, 2012 3 comentários

É muito comum a necessidade de Debugarmos Stored Procedures, seja de DB2, Oracle, MySql, etc. Poucas pessoas sabem que o RAD (Rational Application Developer) tem uma funcionalidade perfeitamente funcional para isso.

Criei um passo a passo que visa auxiliar as pessoas que precisarem fazer o mesmo, segue:

Passos a passo

1- Abrir a perspectiva DATA.

2- Criar um projeto do tipo Data Development.

3- De o nome que preferir.

4- Crie uma conexão com seu Banco de Dados.

5- Entre com as informações do seu banco de dados e após criado, o selecione na lista Connections.

6- Você verá seu projeto criado em Data Project Explorer.

7- Para debugar uma SP (Stored Procedure), clique com o botão direito na pasta Stored Procedures e vá em NEW – Stored Procedure.

8- De o nome que seja o mesmo da procedure que pretende debugar, deixe as opções marcadas como padrão e na ultima tela, NÃO marque Deploy on Finish e MARQUE Enable Debugging.

9- Copie o corpo de sua proc ja criada e cole sobre a proc que o RAD criou.

10- Clique com o botão direito na Procedure e vá em Deploy.

11- Na primeira tela, deixe as opções padrão marcadas.

12- Na próxima tela deixe marcado enable debugging e clique em finish.

13- Você deve ver uma mensagem de sucesso.

14- Clique com o botão direito na Proc e vá em DEBUG, deixe as opções padrão e clique Debug.

15- A primeira vez que roda, costuma ser meio lento.

16- O RAD vai pedir os parametros da PROC caso aplicável, informe-os e clique em OK.

17- O debug tem início e segue o mesmo padrão de aplicações Java.

    • Para ir linha a linha → F6
    • Para marcar um breakpoint → Duplo clique no início da linha
    • Para avançar o Debug até o próximo breakpoint → F8

18- Você pode inspecionar o valor das variáveis em Variables.

19- Poderá tambem ver os resultados em SQL Results.

É isso, qualquer dúvida, poste comentário aqui que eu respondo. :-D

Enjoy!

CategoriasBanco de dados, JAVA

Diferença entre os comandos CONNECT e ATTACH no DB2

fevereiro 23, 2011 1 comentário

Muitos novos usuários tem dúvidas sobre os comandos CONNECT e ATTACH no DB2.

Para falar a diferença, primeiro tenho que dar um conceito: O DB2 trabalha com Instancias, uma Instancia pode ter N bancos de dados. Em um computador, posso ter N Instancias, cada uma com N bancos de dados.

Dito isso, posso definir:

  • Attach é utilizado em uma applicação que precisa trabalhar a nivel de instância, por exemplo, alterando parametros da instância.
  • Connect é utilizado em aplicações que vão trabalhar com um banco de dados em específico.

Para poder conectar em uma instância do DB2, é necesário setarmos a variável de instancia DB2INSTANCE (export DB2INSTANCE=banana), e então utilizar o comando:

db2 attach to banana

Agora, supondo que eu tenha um banco de dados chamado abacaxi, e quero me conectar nele, o comando é:

db2 connect to abacaxi

Enjoy!

Guia de estudos DB2 – Prova 731 – Parte 6

fevereiro 15, 2011 1 comentário

Dando continuidade ao meu Guia de Estudos para o exame 731 (DBA DB2), falaremos aqui sobre Alta disponibilidade, backup e recuperação. Desconsidere erros de português, este guia foi escrito conforme eu estudava, então o foco era aprender, e não escrever bem. Vamos lá:

5.1 Conceitos de recovery
Um DBA deve ter em mente que um banco de dados não deve para jamais, porém alguns fatores podem levar a isso, tais fatores podem ser:
- Falha no sistema: Falta de energia, problema de hardware, sistema operacional, etc. São externos ao banco de dados.
- Problemas nas transações: Usuário podem inadvertidamente corromper seus dados.
- Problemas de mídia: Um disco pode dar problema e tornar os dados inutilizáveis.
- Desastre: O local onde os servidores estão podem pegar fogo, ser inundado, etc.
Nestes casos, um DBA deve ter uma tática de recuperação eficiente, que garanta que os dados vão voltar de forma consistente o mais rapidamente possivel. O DB2 oferece basicamente 3 técnicas de recovery:
- Crash Recovery: basicamente desfaz as alterações que não sofreram commit ainda.
- Version Recovery: recupera o banco de dados baseado em arquivos que foram gerados com o comando backup. É importante dizer que todas as alterações feitas em banco após o backup, serão perdidas, vamos supor que seu backup foi feito a meia noite e a pane ocorreu ao meio dia, fazendo a recuperação utilizando os arquivos do backup, você perderá tudo que aconteceu após a meia noite.
- Rollforward recovery: Implementa a recuperação “Version Recovery” porém aplica as alterações efetuadas após o backup utilizando logs.

5.2 Logs
Podemos dizer que os logs armazenam os dados das transações que ocorrerem no banco de dados, são armazenados em discos, fitas, etc.
É importante ter em mente que os sistemas gestores de banco de dados mantem a maior parte da informação possível em memória RAM, visto que a mesma é mais rápida que o disco, de tempos em tesmpos, as transações são efetivadas, ou seja, gravadas em banco. Essa operação é chamada de externalização.
Enquanto os dados não são gravados em banco, eles permanecem em logs residentes na memória RAM do servidor.
Não existe uma relação entre commit/rollback e a efetiva gravação dos dados em disco.
Os tipos de logs do DB2 são:
- Active logs: Contém informações sobre as transações que ainda não sofreram commit ou rollback ou ainda das que sofreram mas não foram efetivamente gravadas em disco.
- Online Archive Logs: Contém os dados das transações commitadas E externalizadas. Estão no mesmo diretório que os Active Logs.
- Offline archive logs: São os Online Archive Logs, que foram removidos do diretório dos Active Logs e estão em algum outro local (um backup, etc).
As maneiras para se gerencias os logs são:
- Circular Logging: É o modo padrão do DB2. Ele vai rotacionado os logs, vamos supor que temos 4 arquivos de log, o DB2 começa utilizando o log1, quando ele lotar vai para o log2, e assim até chegar no log4. Quando o log4 lotar, ele volta para o log1.
Supondo que temos transações ativas no log1 ainda, o DB2 não pode escrever nele, então, o DB2 cria uma nova fila de logs secundária e passa a rotacionar a mesma até as transações do log1 na fila primária forem efetivadas. Esteja atento que o numéro de filas que podem ser criadas pode ser limitado, então transações muito grandes sem efetivação podem estourar o log do banco de dados.
- Archival Logging: Ao invés de sobrescrever os logs, como é feito com o circular, neste modo o DB2 vai criando novos arquivos. Assim que as transações de um log são externalizadas, ele é movido de diretório e vira um Offline Archive Log.

5.3 Backup
Temos dois tipos de backup:
- Online : durante o procedimento de backup, a base de dados continua oferecendo acesso aos usuários.
- Offline : Durante o procedimento de backup, a base de dados interrompe o acesso aos usuários.

Um backup de sua base de dados consiste de uma cópia completa da mesma, inclusive table spaces, configurações, etc. Para efetuar backup o usuário deve ter o privilégio de SYSADM, SYSCTRL ou SYSMAINT.

O comando para efetuar um backup é: backup :-) , sua sintaxe é:
BACKUP DATABASE database-alias [USER username [USING password]]
[TABLESPACE (tblspace-name [ {,tblspace-name} ... ])] [ONLINE]
[INCREMENTAL [DELTA]] [USE {TSM | XBSA} [OPEN num-sess SESSIONS]
[OPTIONS {options-string | options-filename}] | TO dir/dev
[ {,dir/dev} ... ] | LOAD lib-name [OPEN num-sess SESSIONS]
[OPTIONS {options-string | options-filename}]]
[WITH num-buff BUFFERS] [BUFFER buffer-size] [PARALLELISM n]
[COMPRESS [COMPRLIB lib-name [EXCLUDE]] [COMPROPTS options-string]]
[UTIL_IMPACT_PRIORITY [priority]] [{INCLUDE | EXCLUDE} LOGS] [WITHOUT
PROMPTING]

Um exemplo simples:
BACKUP DATABASE sample
TO d:\mybackups

O comando backup por padrão deixa a base de dados offline. Se quiser efetuar um backup online, você deve especificar no comando com a palabra ONLINE, ex:
BACKUP DATABASE sample
ONLINE
TO /dev/rdir1, /dev/rdir2

5.4 Backup do tablespace
Basicamente é a copia do tablespace para um local em disco, a sintaxe do comando é:
BACKUP DATABASE sample
TABLESPACE ( syscatspace, userspace1, userspace2 )
ONLINE
TO /db2tbsp/backup1, /db2tbsp/backup2

5.5 Backup incremental
Faz o backup de todos os dados que foram alterados desde o ultimo backup full da base efetuado com sucesso.
5.6 Backup incremental em Delta
Faz o backup de todos os dados que foram alterados desde o ultimo backup full ou incremental efetuado com sucesso.

—-

5.7 Recovery
O utilitário do DB2 para evetuar a recuperação da base é o RESTORE. Ele recebe como parametro um arquivo e faz a recuperacao em uma base de dados ja existente ou ainda cria uma nova.
Um exemplo de sua sintaxe é:
RESTORE DATABASE sample
FROM C:\DBBACKUP

O restore só é efetuado em modo offline.

Podemos efetuar o restore de uma base de dados por completou ou somente de uma tablespace. Para efetuar uma recuperação de uma tablespace o comando é:
RESTORE DATABASE sample
TABLESPACE ( mytblspace1 )

5.8 Restore incremental
É o mesmo que o restore, porém, utiliza os arquivos de backups incrementais feitos. Temos a opção de fazer manualmente, iniciando sempre com o ultimo arquivo de restore incremental ou podemos fazer automaticamente como no exemplo:
restore db mydb incremental automatic taken at (Fri)

5.9 Redirected restore
Suponha que você está fazendo um restore de uma base de dados, porém o container (Arquivo em disco) de uma table space não existe mais, foi deletado por exemplo! Você vai obter um erro.
O Redirected restore é uma maneira de se fazer um restore em passos que nos permitam criar as estruturas faltantes.

5.10 Rollforward
Permite que você recupere sua base de dados até um ponto específico, uma determinada hora em determinado dia, utilizando os arquivos de log da base.
Um exemplo é:
ROLLFORWARD DATABASE sample TO timestamp AND COMPLETE
No lugar de timestamp, você deveria especificar a data/hora.

Um rollforward também pode ser feito com tablespaces como no exemplo:
ROLLFORWARD DATABASE sample
TO END OF LOGS AND COMPLETE
TABLESPACE ( userspace1 ) ONLINE

5.11 Recover
Combina as técnicas de RESTORE e ROLLFORWARD.

5.12 Database Rebuild
É uma função do restore que cria uma nova base de dados utilizando um conjunto de imagens de backup. Pode-se recriar a base toda ou somente determinadas tablespaces.

5.13 Rebuild de índices
Quando efectuamos a recuperação de alguma base, é comum quebrar alguns índices, assim, eles ficarão marcados como inválidos. O parâmetro INDEXREC indica que o DB2 vai tentar refazer os índices após a recuperação do DB.

Voltar ao índice do Guia de estudos.

 

Instalando e configurando o PostgreSQL 8.4 no Ubuntu

Instalando e configurando o PostgreSQL 8.4 no UbuntuNo meu ambiente de desenvolvimento, eu sempre utilizo dois bancos: o Postgres e o DB2. Basicamente devido ao fato de minha instalação de DB2 ser pesada pois tenho várias configurações de Data Warehouse e um banco muito carregado, o que torna o banco mais pesado para meu simples desktop. Então, para debugar meus softwares, vou com meu postgres levinho mesmo.

Minha idéia aqui é mostrar como instalar e configurar o PostgreSQL 8.4 no Ubuntu 9.04. As configurações são as mesmas para a instalação em Windows, a única diferença obvia é que você irá ter que ir ao site do postgres e baixar o Installer do Windows.

Vamos lá, iniciamos a instalação com o comando:

$ sudo apt-get install postgresql-8.4 postgresql-client-8.4

Recomendo também instalar o pgAdmin, que é uma ferramenta para administrar o postgres:
$ sudo apt-get install pgadmin3 pgadmin3-data

Algo que aconteceu comigo na migração para o Ubuntu 9.04, foi que tive que remover o Postgres 8.3 (apt-get purge postgresql-8.3) para conseguir iniciar o 8.4 corretamente. Se você concluir meus passos aqui e mesmo assim não conseguir conectar no Postgres, recebendo algum erro, provavelmente terá que dar o purge. (talvez com mais tempo de pesquisa eu poderia descobrir qual era o problema… se alguem passar por isso me diga please).

O próximo passo é setar uma senha para o usuário postgres com os seguintes comandos:

sudo su postgres -c psql postgres
ALTER USER postgres WITH PASSWORD ‘password’;
\q

O primeiro comando chama o utilitário psql com o usuário postgres e conecta no postgres especificamente no database postgres.
O segundo comando altera a senha do usuário postgres.
O terceiro comando finaliza o psql.
Note que a palavra password deve ser substituida pela password que você desejar.

Feito a instalação e mudança de senha do usuário postgres, você estará apto a desenvolver um trabalho no seu computador conectando normalmente ao postgres, porém, se a idéia é disponibilizar o acesso ao banco para receber conexões de outras máquinas, você vai ter que alterar dois arquivos para isso. Para isso, vá para o diretório /etc/postgres/8.4/main

Edite o arquivo postgresql.conf

Na linha listen_addresses, troque o localhost por *, ficando a linha assim:

listen_addresses = ‘*’

Dessa forma seu postgres vai “escutar” não só conexoes provenientes da sua própria máquina.

A próxima configuração no mesmo arquivo é habilitar a encriptação de passwords, para fazer isso descomente a linha abaixo simplesmente removendo o # da frente dela:

password_encryption = on

Finalmente a próxima configuração é no arquivo pg_hba.conf. Neste arquivo você consegue restringir o acesso ao seu banco de dados por IP. Normalmente queremos liberar o acesso para todos os IPs em uma faixa, no meu exeplo, quero liberar para todas as máquinas da rede 10.5.2.*, então eu adiciono a seguinte linha no meu pg_hba.conf:

host all all 10.5.2.0 255.255.0.0 md5

Feito isso, basta reiniciar o postgres com o comando:

sudo /etc/init.d/postgresql-8.4 restart

Enjoy!

Reference Cards

Encontrei uma página muito boa com uma coleção de reference cards, dentre eles sobre JAVA, XML e SQL!

Eis o link: http://www.digilife.be/quickreferences/quickrefs.htm

Enjoy!

CategoriasBanco de dados, JAVA, SQL

Clausulas SQL IN, Having, Constains e Like

Muitas pessoas não conhecem ou não fazem uso de clausulas SQL que podem realmente salvar um dia! Perguntas sobre tais clausulas também são constantemente feitas em entrevistas de empregos. Neste post pretendo mostras brevemente o IN, Having, Constains e Like.

IN

Determina se um valor especificado corresponde a qualquer valor em uma sub-consulta ou uma lista. Exemplos:
SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.Title IN (‘Design Engineer’, ‘Tool Designer’, ‘Marketing Assistant’);

Exemplo utilizando o resultado de outra query:
SELECT FirstName, LastName
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE EmployeeID IN
(SELECT SalesPersonID
FROM Sales.SalesPerson
WHERE SalesQuota > 250000);

HAVING

Especifica um critério de pesquisa para um grupo ou um agregado. HAVING pode ser usado somente com a instrução SELECT. HAVING é usado normalmente em uma cláusula GROUP BY. Quando GROUP BY não é usado, HAVING se comporta como uma cláusula WHERE. Exemplo:
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
ORDER BY SalesOrderID ;

CONTAINS

É um predicado usado em uma cláusula WHERE para pesquisar colunas que contêm tipos de dados baseados em caracteres para obter correspondências precisas ou difusas (menos precisas) para palavras e frases únicas, a proximidade de palavras com uma determinada distância entre si ou correspondências ponderadas. Exemplo:
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
AND CONTAINS(Name, ‘Mountain’);

LIKE

Determina se uma cadeia de caracteres específica corresponde a um padrão especificado. Exemplo:

SELECT *
FROM t
WHERE col1 LIKE ‘% da Silva’

Enjoy!

Seguir

Obtenha todo post novo entregue na sua caixa de entrada.

Junte-se a 149 outros seguidores