Arquivos

Archive for the ‘DB2 / 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]

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!

Checando as Stored Procedures que foram alteradas no DB2

É muito comum a necessidade de checarmos se alguma Stored Procedure foi alterada no DB2. Isso pode ser facilmente feito utilizandoa tabela syscat.routines, veja os campos da mesma em:

db2 describe table syscat.routines

Para selecionar o nome das procedures que foram alteradas HOJE, por exemplo, utilize a seguinte sql:

db2 “select routinename from syscat.routines where DATE(CREATE_TIME)=DATE(CURRENT TIMESTAMP) order by routinename”

Enjoy!

Obtendo o corpo de uma Stored Procedure no DB2

É comum obtermos erros do DB2 em nossa aplicação JAVA retornando algo do tipo:

Caused by:
com.ibm.db2.jcc.am.oo: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=2304, COLNO=6, DRIVER=3.58.82

Em alguns casos, não temos muitos dados sobre a tabela, campo, etc… então é necessário investigar, para isso, algumas queryes podem nos ajudar, segue:

- Para ver o nome da tabela baseado no Table Id:

db2 “SELECT TABNAME FROM SYSCAT.TABLES WHERE TABLEID=2304″

- Para ver o campo ao qual se refere o erro:

SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME=’TVALDCCONDCVARVL’ AND COLNO=6

- E finalmente, se quiser ver o corpo da procedure, num caso no qual não tenha um cliente que possibilite instalado e só tem acesso via command line (para MACHOS):

db2 “select text from syscat.routines where routinename=’PUPDCONDCREGRA’” > PUPDCONDCREGRA.sql

No caso acima, usei o nome PUPDCONDCREGRA, substitua para o nome de sua proc.

Enjoy!

Criar Stored Procedure dinâmica no DB2

Eu tive a necessidade de escrever uma Stored Procedure dinâmica no DB2 que receberia uma instrução SQL, a executaria e me retornaria um ResultSet.

A mesma seria utilizada em uma tela de busca “genérica” no sistema, para, em tempo de homologação, permitir ao analista efetuar queries, uma vez que o banco de homologação só aceita a chamada de procs, é impossivel efetuar selects diretamente no mesmo.

Enfim, segue o corpo da Procedure, observando que JULIANO é o meu schema:

CREATE PROCEDURE JULIANO1.PSELEMPRRTINGRES (
       IN PAR_CAMPO1        VARCHAR (10000) — este é o parametro que passo para a proc
)
        SPECIFIC JULIANO1.PSELEMPRRTINGRES
        DYNAMIC RESULT SETS 1

P1:BEGIN

     DECLARE strSqlDn                        varchar(10000);
     DECLARE stm_procedimento                     STATEMENT;
     DECLARE cursorDn CURSOR WITH RETURN FOR stm_procedimento;

     SET strSqlDn = PAR_CAMPO1;

     PREPARE stm_procedimento FROM strSqlDn;
     OPEN cursorDn;

END P1

Para invocar a proc, basta chamar:

call JULIANO1.PSELEMPRRTINGRES(‘select * from clientes’);

No Java, eu invoco a Proc normalmente, jogando o resultado para um result set, faço um parse para desenhar a tabela no JSP e voa-lá!

Enjoy!

DB2: Criar um banco em uma pasta diferente no Windows

Eventualmente pode ser necessário criar um banco DB2 em uma pasta diferente da pasta na qual o DB2 foi instalado no Windows.

Se você tentar o comando, como por exemplo:

CREATE DATABASE julianom AUTOMATIC STORAGE YES  ON ‘E:\julianom’ DBPATH ON ‘E:\julianom’  ….

E o comando falhar dizendo que a pasta de destino não existe, execute os seguintes comandos:

db2set DB2_CREATE_DB_ON_PATH=YES
db2stop
db2start

A variável que setamos com o db2set permite a criação de bancos em pastas diferentes.

Enjoy.

CategoriasDB2 / Banco de dados Tags:,

db2diag.log – Alterando o nível de detalhamento do Log do DB2

O banco de dados DB2 armazena por padrão os seus logs no arquivo db2diag.log, encontrado no caminho /sqllib/db2dump. No windows é:

Windows XP and Windows 2003:
C:\Documents and Settings\All Users\Application Data\IBM\DB2\<DB2 copy name>\DB2

Windows Vista e posteriores:
C:\ProgramData\IBM\DB2\<db2 copy name>\DB2

Muitas vezes as pessoas querem que o banco detalhe mais ou menos as informações neste arquivo. Para alterarmos o nível de detalhamento, temos que alterar a variável do dbm diaglevel. Os valores possíveis são:

0 – Não loga NADA
1 – Somente erros severos
2 – Todos os erros
3 – Erros e avisos (warnings)
4 – Loga tudo (erros, avisos e informações)

Após definido o valor desejado, para alterar a variável, o comando é:

db2 update dbm cfg using DIAGLEVEL X

Onde X é o level desejado.

Para consultar o valor atual de suas configurações, o comando é:

db2 GET DATABASE MANAGER CONFIGURATION

Enjoy!

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!

Comandos úteis do DB2

Este post serve de referência para mim mesmo :-) São comandos muito utilizados do DB2 e eventualmente eu esqueço.

  • db2 \?
Get DB2 help.
  • db2 get instance
Return the active instance.
  • db2 list db directory
List the contents of the system db directory.
  • db2 create db <db name>
Create a database.
  • db2 connect to <db name>
Connect to a database.
  • db2 get connection state
Show current database being used.
  • db2 list active databases
Show activated databases.
  • db2 disconnect <db name>
or
db2 connect reset
Disconnect a database.
  • db2 drop database <db name>
Delete the database contents and all log files, uncatalog the database, and delete the database subdirectory.
  • db2 list tablespaces
List tablespaces associated with the current database.
  • db2 list tables
List tables associated with the current database.
  • db2 describe table <name>
Display columns of a table.
  • db2 get dbm cfg
Show database manager configuration.
  • db2 update dbm cfg using <parameter> <value>
Update database manager configuration.
  • db2 reset dbm cfg
Restore default configuration for database manager (might need a restart).
  • db2 get db cfg [for <db name>]
Show configuration of a specific database.
  • db2 update db cfg [for <db name>] using <parameter> <value>
Update configuration of a specific database.
  • db2 reset db cfg [for <db name>]
Restore default configuration for a specific database (might need a restart).
  • db2 <sql statement>
Execute SQL statement.

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.

 

Seguir

Obtenha todo post novo entregue na sua caixa de entrada.

Junte-se a 149 outros seguidores