Embed Size (px)
Citation preview
DB2 - CONCEITOS E UTILIZAÇÃO 19/11/98
DBMS RELACIONAL...................................................................................................................... 3DB2 é um DBMS Relacional............................................................................................................. 5OBJETOS DO DB2............................................................................................................................ 7ENTIDADE RELACIONAMENTO.................................................................................................10RELACIONAMENTO..................................................................................................................... 11LINGUAGEM SQL......................................................................................................................... 17
INSTRUÇÃO SELECT................................................................................................................ 18QUERY.................................................................................................................................... 21SELECT DE TODAS AS COLUNAS.......................................................................................23CONTROLE DE LINHAS........................................................................................................ 24NULIDADE............................................................................................................................. 26MÚTIPLAS CONDIÇÕES.......................................................................................................28IN............................................................................................................................................. 30PESQUISAS PARCIAIS........................................................................................................... 31MANIPULANDO TABELA RESULTANTE...........................................................................33ORDER BY.............................................................................................................................. 33SELECT DISTINCT................................................................................................................. 35SELECT DE VALORES CALCULADOS................................................................................37ORDER BY POR VALOR CALCULADO...............................................................................38SELECT AVANÇADO............................................................................................................40JOIN DE TABELAS................................................................................................................. 57INNER JOIN............................................................................................................................ 62OUTER JOIN........................................................................................................................... 63EXPRESSÕES CASE............................................................................................................... 64UNION..................................................................................................................................... 65SUBQUERY............................................................................................................................. 67
MANUTENÇÃO DE DADOS - INSERT....................................................................................72MANUTENÇÃO DE DADOS - UPDATE...................................................................................73MANUTENÇÃO DE DADOS - DELETE...................................................................................74
DATA DEFINITION LANGUAGE..................................................................................................75DDL............................................................................................................................................. 76STORAGE GROUP...................................................................................................................... 77DATABASE................................................................................................................................. 78TABLESPACE............................................................................................................................. 79TABLE........................................................................................................................................ 81ÍNDICE........................................................................................................................................ 83VIEW........................................................................................................................................... 84
DROP.............................................................................................................................................. 85SQL ESTÁTICO vs. SQL DINÂMICO............................................................................................86COMMIT / ROLLBACK................................................................................................................. 91BIND............................................................................................................................................... 92SEGURANÇA INTERNA DO DB2.................................................................................................97UTLITÁRIOS DO DB2.................................................................................................................... 99
SYSIBM.SYSUTILX.................................................................................................................. 100LOAD........................................................................................................................................ 101
LOAD – JCL.......................................................................................................................... 102FASES DO LOAD.................................................................................................................. 105
CHECK DATA........................................................................................................................... 109FASES DO CHECK DATA....................................................................................................110
CHECK INDEX......................................................................................................................... 112FASES DO CHECK INDEX...................................................................................................112
REORG...................................................................................................................................... 113FASES DO REORG............................................................................................................... 113
COPY......................................................................................................................................... 116MERGECOPY............................................................................................................................ 117RECOVERY.............................................................................................................................. 118QUIESCE................................................................................................................................... 120REPORT.................................................................................................................................... 120INDEX RECOVERY.................................................................................................................. 121
#595851
DB2 - CONCEITOS E UTILIZAÇÃO 19/11/98
RECOVERY PENDING............................................................................................................. 122MODIFY.................................................................................................................................... 123DIAGNOSE................................................................................................................................ 123STOSPACE................................................................................................................................ 123RUNSTATS............................................................................................................................... 124REPAIR..................................................................................................................................... 124DSNTIAUL................................................................................................................................ 125COMANDOS DB2..................................................................................................................... 126
#595852
DBMS RELACIONAL
QUANDO UM DBMS PODE SER CONSIDERADO RELACIONAL ??
1. Independência dos Dados2. Estrutura Tabular3. Operadores de Tabelas4. Uniformidade e Integridade dos Dados.
Notas:
DBMS - Significa DATA BASE MANAGEMENT SYSTEM, OU SISTEMA GERENCIADOR DE BANCO DE DADOS (SGBD).
Um DBMS Relacional exige:
Estrutura de Dados Tabular Os dados devem ser colocados em tabelas
Operadores de Tabelas Deve ter operadores que conseguem manipular dados em
tabelas
Uniformidade e Integridade dos Dados Possui Mecanismos que mantém a integridade dos dados que são adicionados ou modificados. O DBMS deve também ter mecanismo capazes de forçar uma uniformização dos dados que são nela armazenados. Por exemplo, podemos forçar o armazenamento dos dados sempre o formato numérico.
Independência dos Dados .Lógica das aplicações não devem se preocupar com a estrutura de armazenamento e a teoria de acesso aos dados.
Tabela Empregados
Matricula Nome Cargo Depto Salário
000010 Christiane D01 001 260000 000020 Karina R01 002 270010 000030 Júlio S01 003 300010 Linhas 000040 Ingrid P01 004 250500 000050 Juan P02 004 138005
Colunas
Notas :
Todos os dados e relacionamentos entre dados são representados por valores de campo. Pointers físicos não são utilizados.
Cada ocorrência da tabela é chamada de linha. Os atributos são chamados de colunas.
As Tabelas DB2 são definidas e manipuladas usando-se a linguagem SQL.
Todas as Tabelas são referenciadas pelo seu nome.
DB2 é um DBMS Relacional
SISTEMA DE GERENCIA DE BANCO DE DADOS
Integridade e Segurança de Dados Recuperação e Reinicio Integrados Definição Dinâmica Operação Contínua
Modelo de Dados Linguagem SQL Relacional - Os dados vistos - Definição
como Tabelas Manipulação e - Projeto mais fácil Controle de dados
AMBIENTE DB2
Notas:
DB2 pode ser acessado através do IMS, CICS, TSO e jobs BATCH simultaneamente.
Dados que estiverem em IMS/DM (Antigo IMS/DB) podem ser transferidos para o DB2 através do DPROP (Data Propagador) ou o DXT (Data Extract).
IMS TM CICS Qmf/Spffi BATCH
IMS DM DB2DPROP
DXT
OBJETOS DO DB2
DATA BASE
Tablespace1
Indice1 Tabela1
Tablespace2
Indice2 Tabela2
View X
Storage Group A Storage Group B
OBJETOS DO B2
Notas:
DATABASE Conjunto de tabelas, índices e tablespaces agrupados sob um critério administrativo.
TABLE (Tabelas)Conjunto de linhas com as mesmas colunas. Representam uma entidade de um modelo entidade-relacionamento.
TABLESPACE
Conjunto de datasets VSAM LDS (Linear Data Sets) que contem de uma ou mais tabelas. Suas paginas podem ser de 4K ou 32K. Existem três tipos de tablespaces:
SIMPLESAs páginas de seus data sets podem conter dados de uma ou mais tabelas.
SEGMENTADOÉ dividido em segmentos que são constituído de 4 a 64 páginas e sempre múltiplos de 4. Cada segmento só pode conter dados de uma tabela.
PARTICIONADORecomendado para tabelas gigantes. Pode ser dividido em até 64 partições. Cada partição pode ter no máximo 4Gbytes. Requer um índice CLUSTER que serve de filtro para determinar a distribuição das linhas entre as partições.
ÍNDICE Utilizado para agilizar a leitura de dados ou para garantir a unicidade dos dados.Existem três tipos de índices:
UNIQUEForça a unicidade dos dados através dos valores de uma coluna escolhida como chave primaria.
OBJETOS DO B2 (Continuação)
CLUSTERA seqüência física das linhas procura obedecer a seqüência das entradas do índice. Existem utilitários que reorganizam um tablespace através do índice CLUSTER de suas tabelas.
NORMAL
VIEW É uma tabela lógica que é um subconjunto de uma ou mais tabelas. Sua utilização m nada difere com a de uma tabela. Conforme o caso uma View pode ser somente para leitura
STORAGE GROUP É um conjunto de volumes(discos) onde estarão os tablespaces e índices. Os discos devem ser do mesmo device type.A utilização do Storage Group nos poupa ca codificação do AMS de VSAM para definir os data sets de um tablespace.
Os discos de um Storage Group não precisam ser dedicados ao DB2.
SYNONYM (Sinônimo)É um nome alternativo (Apelido) para uma tabela ou uma VIEW. Uma vez criado, a sua utilização é restrita a um AUTH-ID e válido a nível de um subsistema DB2.
ALIAS É um nome alternativo(Apelido) para uma tabela ou VIEW. Um alias é de uso compartilhado ao contrario do SYNONYM. É utilizado mais como apelido para uma tabela remota e é válido a nível de um subsistema DB2.
ENTIDADE RELACIONAMENTO
Modelo E R DB2
Notas:
Uma vez construído o modelo entidade relacionamento, passamos à fase de implementação em DB2.
Basicamente temos uma tabela para cada entidade do modelo. Existem casos (relacionamento m:m) em que precisaremos trabalhar com tabelas adicionais.
Para cada atributo de uma entidade temos uma coluna na tabela correspondente. Uma coluna tem algumas características tais como tipo de dado, para definir se o conteúdo é numérico ou alfanumérico.
A distribuição de tabelas entre os tablespace também deve seguir alguns critérios tais como tamanho das tabelas, afinidade entre as tabelas, etc.
RELACIONAMENTO
Matr Nome Depto EMPR
419 Maria A00003 Carlos Z13
Operador Relacional
DeptoCod DeptoNome DEPT
Z13 Reserva A00 Vendas
Notas:
Para mapearmos um relacionamento entre duas entidades de um modelo relacional precisamos conhecer o conceito de chaves.
Chave primária: Identifica uma ocorrência de uma entidade (Tabela) Chave estrangeira: Identifica uma associação entre as ocorrências de duas tabelas. Como o valor representado é chave primária na outra entidade, a chamamos de chave “Estrangeira”.
Conceito de chave primária e chave estrangeira estão implementados no DB2, mas a suas utilização não é obrigatória.
A chave estrangeira deve pertencer ao mesmo domínio da chave primária a qual esta associada. Ex.: Não poderíamos associar a coluna NOME de EMPR com a coluna DeptoCod da DEPT.
A associação lógica entre as ocorrências das entidades é feita através das chaves primárias e estrangeiras que serão argumento de um operador relacional.
Chave Primária
Chave Estrangeira
CarlosTrabalhana Reserva
TIPOS DE RELACIONAMETO
1 PARA MUITOS
Colocar a chave estrangeira aqui
EMPREGADO M : 1 DEPARTAMENTOCarlos ReservaPauloMaria Vendas
DEPTO DeptoCod DeptoNome
Z13 Reserva A00 Vendas : :
EMPREGADO
Matr Nome Dept
419 Maria A00 003 Carlos Z13 087 Paulo Z13 : : :
ChavePrimária
ChaveEstrangeira
TIPOS DE RELACIONAMENTO
Notas:
Em um relacionamento do tipo um para muitos, qualquer ocorrência de uma entidade pode estar associada à várias ocorrências da outra entidade, enquanto qualquer ocorrência desta outra deve estar associada à apenas uma ocorrência da outra.
Neste caso devemos colocar a chave estrangeira na tabela em que cada ocorrência só enxerga uma ocorrência da tabela oposta.
Caso contrário deveríamos colocar várias chaves estrangeiras na tabela oposta. No nosso exemplo, se um determinado departamento tiver 30 empregados, a tabela DEPT deveria ter no mínimo 30 chaves estrangeiras! Podemos também imaginar uma situação onde um departamento da companhia resolve triplicar o número de empregados.
TIPOS DE RELACIONAMENTO
MUITOS PARA MUITOS
EMPREGADO M : M PROJETOCarlos Ano 2.000PauloMaria Qualidade
Onde colocar a chave estrangeira ?
EMPREGADO EMP-PROJMatr Nome Mat Cod419 Maria 003 A2 PROJETO003 Carlos 087 A2 087 Paulo : : Pcod PNome
419 A2 A2 Projeto 2.000
: : QD Qualidade 419 QD
TABELA DE ASSOCIAÇÃO
Notas:
Em um relacionamento do tipo muitos para muitos, qualquer ocorrência de ambas as entidades pode estar associada à várias ocorrências da entidade oposta.
Neste caso, ao invés de se criar várias chaves estrangeiras, criamos uma tabela de associação que contém pelo menos duas chaves estrangeiras apontando para as chaves primárias das tabelas associadas.
Nada impede que uma tabela de associação tenha colunas que não sejam chaves estrangeiras.
Chave Primária
Chave Primária
Chave Estrangeira
Chave Estrangeira
INTEGRIDADE DE REFERENCIA
EMPR DEPT(Dependet) (Parent)
Matr Nome Depto Dcod Dnome419 Maria A00 A00 Vendas003 Carlos Z13 V75
Contabilidade : : : Z13 Reserva
: :
IntegridadeReferencial
Notas:
A Integridade de referência garante que qualquer valor apontado pela chave estrangeira tenha um valor correspondente na chave primária à qual está associada.
A Integridade de Referência é implementado no DB2 via DDL (Instruções de definição de objetos da linguagem SQL).
No DB2 a Integridade de Referência obedece às seguintes regras: Uma chave primária tem valor único e não nulo Uma chave estrangeira é nula ou tem valor correspondente na
chave primária.
Tanto na inserção como na atualização de linhas, o DB2 força a observação destas regras.
INTEGRIDADE DE REFERENCIA (Continuação).
Todas as tabelas associadas entre si via regra de Integridade de Referência formam um conjunto chamado de ESTRUTURA REFERENCIAL ou REFERENTIAL STRUCTURE.
Uma tabela com chave primária definida é chamada de Parent Table ou tabela Mãe.
Uma table com chave estrangeira definida é chamada de Dependent Table ou tabela Dependente.
Uma linha de uma Parent Table é chamada Parent Row ou linha Mãe se existir pelo menos uma linha na tabela dependente com chave estrangeira correspondente à sua chave primária.
Uma linha de uma Dependent Table é chamada de Dependent Row ou linha Dependente se existir uma linha Mãe com valor de chave primária correspondente à da sua chave estrangeira. Em outras palavras a chave estrangeira não deve ter valor nulo para ser uma linha dependente.
Na eliminação de uma linha da tabela mãe, pode ocorrer situações conforme a regra de deleção estabelecida pela chave estrangeira.
CASCADE : Regra onde todas as linhas dependentes são eliminadas juntamente com a mãe. SET NULL : Regra onde todas as chaves estrangeiras das linhas dependentes são atualizadas com valor nulo após a eliminação da mãe. RESTRICT : Regra onde a eliminação da linha mãe é impedida. Neste caso somente as linhas que não são mães podem ser eliminadas.
LINGUAGEM SQL
SQL
STRUCTUREDQUERY
LANGUAGE
SELECT CREATE GRANTUPDATE ALTER REVOKEDELETE DROP COMMITINSERT ROLLBACK
DML DDL CONTROL
Notas:
A Linguagem SQL pode ser separada em três grupos:
DMLData Manipulation Language, utilizado para fazer pesquisas e atualizações nas tabelas DB2.
DDLData Definition Language, utilizado para definir, alterar e eliminar objetos DB2.
CONTROL Utilizado para administrar a segurança dos recursos DB2.
INSTRUÇÃO SELECT
Não é procedural Conceito da teoria os conjuntos Fácil
Notas:
A instrução SELECT não é procedural. Em outras palavras não precisamos fornecer todos os procedimentos necessários para fazer uma pesquisa tais como:
1. Zere o contador2. Abra o arquivo3. Leia um registro4. Verifique se satisfaz o critério de pesquisa5. Incremente o contador6. etc.
Com a instrução SELECT basta dizer:
1. QUERO OS DADOS QUE SATISFAZEM A CONDIÇÃO X. SELECT trabalha com conjuntos e permite a execução de
operadores elementares que nos são familiares. Quem já não ouviu falar dos operadores união e intersecção? Com uma instrução SQL podemos implementar os seguintes
operadores: A B
UNIÃO A U B
A B INTERSECÇÃO A B
A B DIFERENÇA A - B
PRODUTO A X B = { (x, y) : x A e y B} CARTESIANO
TABELAS USADAS NOS EXEMPLOS
x.staff
ID NOME DEPT CARGO ANOS
SALARIO COMIS
10 SANTANA 20 GER 7 18357.50 -20 ORSINI 20 VENDAS 8 18171.25 612.4530 DANTAS 38 GER 5 17506.75 -40 O’BRIEN 38 VENDAS 6 18006.00 846.5550 SOUZA 15 GER 10 20659.80 -60 SUZUKI 38 VENDAS - 16808.30 650.2570 ALMEIDA 15 VENDAS 7 16502.83 1152.0
080 JAMES 20 ATEND - 13504.60 128.2090 KOONITZ 42 VENDAS 6 18001.75 1386.7
010
0PLOTZ 42 GER 7 18352.80 -
110
NGAN 15 ATEND 5 12508.20 206.60
120
NAUGHTON 38 ATEND - 12954.75 180.00
130
YAMAGUCHI 42 ATEND 6 10505.90 75.60
140
FRAYE 51 GER 6 21150.00 -
150
OLIVEIRA 51 VENDAS 6 19456.50 637.50
160
MOLINARI 10 GER 7 22959,20 -
170
KERMISCH 15 ATEND 4 12258.50 110.10
180
ABRAHAMS 38 ATEND 3 12009.75 236.50
190
SNEIDER 20 ATEND 8 14252.75 126.50
200
SCOUTTEN 42 ATEND - 11508.60 84.20
210
LU 10 GER 10 20010.00 -
220
SMITH 51 VENDAS 7 17654.50 922.80
230
LUNDQUIST 51 ATEND 3 13369.80 189.65
240
DANIELS 10 GER 5 19260.25 -
250
WHEELER 51 ATEND 6 14460.00 513.30
26 JONES 10 GER 12 21234.00 -
027
0LEA 66 GER 9 18555.50 -
280
WILSON 66 VENDAS 9 18674.50 811.50
290
PINHO 84 GER 10 19818.00 -
300
DAVIS 84 VENDAS 5 15454.50 806.10
310
GRAHAM 66 VENDAS 13 21000.00 200.30
320
GONZALES 66 VENDAS 4 16858.20 844.00
330
BURKE 66 ATEND 1 10988.00 55.50
340
VIEIRA 84 VENDAS 7 17844.00 1285.00
350
GAFNEY 84 ATEND 5 13030.50 188.00
TABELAS USADAS NOS EXEMPLOS (Continuação)
x.org
DEPTCOD
DEPTNOME GERENTE
DIVISÃO LOCAL
10 MATRIZ 160 CENTRO SÃO PAULO15 CAMPINAS 50 SUDESTE CAMPINAS20 RIO 10 SUDESTE RIO DE JANEIRO38 MINAS 30 SUDESTE B. HORIZONTE42 BAHIA 100 NORDESTE SALVADOR51 RECIFE 140 NORDESTE RECIFE66 RIO GRANDE 270 SUL PORTO ALEGRE84 PARANÁ 290 SUL CURITIBA
QUERY
Sequência obrigatória:
SELECT NOME, ANOS, SALARIO - Diz quais as colunas mostrar
FROM x.staff - Diz de quais tabelas
WHERE DEPT = 38 - Diz quais linhas selecionar
ORDER BY NOME - Diz como ordenar o resultado
Notas:
As cláusulas de uma instrução SELECT devem ser especificadas obedecendo à uma sequência.
As cláusulas estão grifadas na figura e não podem ser abreviadas.
No nosso exemplo estamos fazendo uma pesquisa na tabela chamada x.staff para gerar um relatório com o nome, tempo de casa e o salário de todos que trabalham no departamento de código “38”. O Resultado deve ser classificado pelo nome os funcionários.
Uma instrução SELECT pode ser escrita em formato livre desde que as cláusulas estejam obedecendo à sequência obrigatória, como descrito abaixo:
SELECT NOME, ANOS, SALARIO FROM x.staff WHERE DEPT = 38 ORDER BY NOME
SELECT ... FROM
SELECT algumas das colunas da tabela Lista as colunas desejadas na sequência de esquerda para a direita Vírgulas separam as colunas
FROM lista de tabelas Simples: EMP Qualificado: AUTHID.EMP
EXEMPLO
SELECT DEPTNOME, DEPTCODFROM x.org
Resultado:DEPTNOME DEPTCO
DMATRIZ 10CAMPINAS 15RIO 20MINAS 38: :
SELECT DE TODAS AS COLUNAS
Para selecionar todas as colunas de uma tabela na mesma sequência em que foram definidas:
SELECT * FROM x.org
Resultado:
DEPTCOD
DEPTNOME GERENTE
DIVISÃO LOCAL
10 MATRIZ 160 CENTRO SÃO PAULO15 CAMPINAS 50 SUDESTE CAMPINAS20 RIO 10 SUDESTE RIO DE JANEIRO38 MINAS 30 SUDESTE B. HORIZONTE
: : : : :
CONTROLE DE LINHAS
WHERE
Traz certas linhas Estabelece condições
Listar empregados do departamento 20
SELECT DEPT, NOME, CARGO, COMISFROM x.staffWHERE DEPT = 20
Resultado:
ID NOME DEPT CARGO ANOS
SALARIO COMIS
10 SANTANA 20 GER 7 18357.50 -20 ORSINI 20 VENDAS 8 18171.25 612.4580 JAMES 20 ATEND - 13504.60 128.2019
0SNEIDER 20 ATEND 8 14252.75 126.50
Selecionar linhas através de dado numérico
SELECT DEPT, NOME, CARGOFROM x.staffWHERE DEPT = 20
Selecionar linhas através de dado alfanumérico
SELECT NOME, CARGO, COMISFROM x.staffWHERE NOME = ‘SANTANA’
OPERADORES DE COMPARAÇÃO
= Igual <> or = Diferente
> Maior>= Maior ou Igual< Menor<= Menor ou Igual= Não Igual> Não Maior< Não Menor
SELECT MATR, COMISFROM x.staffWHERE COMIS >= 1000
SELECT NOME, DEPT, ANOSFROM x.staffWHERE CARGO <> ‘GER’
NULIDADE
Na inserção de linha, uma coluna que é:
NOT NULL O valor deve ser fornecido (ALLOW) NULL O valor pode ser omitido
Um valor NULO: Não é zeroNão é BrancoÉ Desconhecido
NOT NULL WITH DEFAULT
Uma coluna pode ser ‘Not Null With Default’.
Após a Versão 4 podemos atribuir um valor Default para a coluna quando à mesma é omitida na inclusão e se não atribuirmos este valor o sistema atribui como:
Zero se coluna numérica Branco se coluna definida como caracter Comprimento zero se coluna definida como caracter de tamanho variável.
SELEÇÃO DE NULOS
Valor nulo como critério de SELECT
SELECT NOME, CARGO, COMISFROM x.staffWHERE COMIS IS NULL
Resultado:
NOME CARGO
COMIS
SANTANA GER -DANTAS GER -SOUZA GER -PLOTZ GER -FRAYE GER -: : :
Valor não nulo como critério de SELECT
SELECT NOME, CARGO, COMISFROM x.staffWHERE COMIS IS NOT NULL
Resultado:
NOME CARGO COMISORSINI VENDAS 612.45O’BRIEN VENDAS 846.55SUZUKI VENDAS 650.25ALMEIDA VENDAS 1152.00JAMES ATEND 128.20KOONITZ VENDAS 1386.70NGAN ATEND 206.60NAUGHTON ATEND 180.00YAMAGUCHI ATEND 75.60OLIVEIRA VENDAS 637.50KERMISCH ATEND 110.10: : :
MÚTIPLAS CONDIÇÕES
Operadores múltiplos de condições: AND e OR Dadas duas condições:
CARGO = ‘VENDAS’ SALARIO < 17000
Para ter ambas satisfeitas: AND
WHERE CARGO ‘VENDAS’ AND SALARIO < 17000
Resultado:
NOME CARGO SALARIOSUZUKI VENDAS 16808.30ALMEIDA VENDAS 16502.83: : :
Para ter qualquer uma satisfeitas: OR
WHERE CARGO ‘VENDAS’ OR SALARIO < 17000
Resultado:
NOME CARGO SALARIOORSINI VENDAS 18171.25O’BRIEN VENDAS 18006.00SUZUKI VENDAS 16808.30ALMEIDA VENDAS 16502.83JAMES ATEND 13504.60: : :
MÚLTIPLAS CONDIÇÕES (Continuação)
WHERE (CARGO = ‘VENDAS’ AND COMIS > 1200) OR ANOS > 10
Resultados:
NOME CARGO ANOS COMISKOONITZ VENDAS 6 1386.70JONES GER 12 -GRAHAM VENDAS 13 200.30VIEIRA VENDAS 7 1285.00: : : :: : : :
Não equivale a:
WHERE CARGO = ‘VENDAS’ AND (COMIS > 1200 OR ANOS > 10)
Resultados:
NOME CARGO ANOS
COMIS
KOONITZ VENDAS 6 1386.70GRAHAM VENDAS 13 200.30VIEIRA VENDAS 7 1285.00
IN
Valor deve coincidir com algum elemento de uma lista
WHERE DEPT IN ( 38, 20, 42)
Equivale a múltiplos Ors para a mesma coluna:
WHERE DEPT = 38 OR DEPT = 20 OR DEPT = 42
Valor deve ser diferente de algum elemento da lista
WHERE DEPT NOT IN ( 38, 20, 42)
BETWEEN
Para selecionar um intervalo fechado de valores
SELECT NOME, CARGO, ANOSFROM x.staffWHERE ANOS BETWEEN 9 AND 11
Equivale a
SELECT NOME, CARGO, ANOS FROM x.staffWHERE ANOS >= 9 AND ANOS <=11
Resultado: NOME CARGO ANOSSOUZA GER 10LU GER 10LEA GER 9WILSON VENDAS 9PINHO GER 10
Para selecionar um intervalo diferente de valoresSELECT NOME, CARGO. ANOSFROM x.staff WHERE ANOS NOT BETWEEN 9 AND 11
PESQUISAS PARCIAIS
Para pesquisar um subconjunto de caracteres : LIKE
% Conjunto de caracteres quaisquer_ Um caracter qualquer
EXEMPLOS:
WHERE NOME LIKE ‘G%’ Inclui: GRAHAMGONZALESGAFNEY
WHERE NOME LIKE ‘%SON’ Inclui: WILSONJASON
WHERE NOME LIKE ‘%M%N%’Inclui: SAMANTAMOLINARI
WHERE NOME LIKE ‘_ _’ Inclui: LUWHERE NOME LIKE ‘_R%’ Inclui: FRAYE
GRAHAM
O operadores ESCAPE permite que seja utilizado outro caracter no lugar do ‘%’.
Exemplo: ... WHERE coluna LIKE ‘100+%%’ ESCAPE ‘+’
Notas:
No exemplo estamos procurando strings que começam por ‘100%’.
Primeiro ‘%’ é interpretado como um caracter comum pois é precedido pelo caracter ‘+’ que foi definido através do operador ESCAPE.
Segundo ‘%’ é interpretado como um conjunto de caracteres quaisquer pois não é precedido por nenhum caracter de ESCAPE.
Neste exemplo, quaisquer ocorrência do ‘+’ que não sejam ‘++’, ‘+%’ ou ‘+_’ provoca erro.
NEGAÇÃO
Para reverter o teste:
WHERE NOME NOT LIKE ‘G%’
Exclui: GRAHAM, GONZALES, GAFNEY.
WHERE ANOS NOT BETWEEN 9 AND 11
Exclui: ANOS de 9 a 11
USER
Registro Especial
Authorization ID do tempo de execução Comprimento fixo de 8 bytes Útil para query dependente de executor
SELECT * FROM SYSIBM.SYSTABLES WHERE OWNER = USER
MANIPULANDO TABELA RESULTANTE
Para classificar as linhas: ORDER BY
Para eliminar linhas duplicadas: SELECT DISTINCT
ORDER BY
Para classificar numa sequência especifica:
Lista classificada do pessoal do depto 84
SELECT NOME, CARGO, ANOS FROM x.staffWHERE DEPT = 84 ORDER BY NOME
Resultado:
NOME CARGO ANOSDAVIS VENDAS 5GAFNEY ATEND 5PINHO GER 10VIEIRA VENDAS 7
Notas:
Colunas classificadas devem ser referenciadas pelo SELECT
Nulos são considerados como valor mais alto.
Podemos especificar colunas para o ORDER BY fornecendo um número que indica a posição da mesma coluna na cláusula SELECT. O ORDER BY é o único que permite isto.
A cláusulas ORDER BY classifica uma tabela resultante. Logo deve ser SEMPRE a única e a Última a aparecer numa instrução SELECT.
ORDER BY (Continuação)
Para classificar pelas coluna(s) em ordem ASCendente (default) ou DESCendente.
SELECT NOME, CARGO, ANOSFROM x.staff WHERE DEPT = 84 ORDER BY CARGO, ANOS DESC
Resultado:NOME CARGO ANOSGAFNEY ATEND 5PINHO GER 10VIEIRA VENDAS 7DAVIS VENDAS 5
ORDER BY ANOS DESC, CARGO
Resultado:
NOME CARGO ANOSPINHO GER 10VIEIRA VENDAS 7GAFNEY ATEND 5DAVIS VENDAS 5
SELECT DISTINCT
SELECT DEPT FROM x.staffDEP
T20203838153810
.
.
Para eliminar linhas duplicadas:
SELECT DISTINCT DEPT FROM x.staffDEP
T20152038
.
Múltiplas colunas
SELECT DISTINCT DEPT, CARGO FROM x.staffDEPT CARGO
10 GER15 ATEND15 GER15 VENDAS20 ATEND20 GER20 VENDAS38 ATEND
: :SELECT DISTINCT (Continuação)
Notas:
A Palavra chave DISTINCT deve aparecer logo depois do SELECT.
Classificação ocorre pelas colunas da esquerda para a direita.
‘ORDER BY’ pode ser usada para controlar a classificação.
EXEMPLO:
SELECT DISTINCT DEPT, CARGOFROM x.staffORDER BY CARGO
DEPT CARGO15 ATEND20 ATEND38 ATEND. .. .10 GER15 GER20 GER. .. .. .15 VENDAS20 VENDAS. .. .. .
SELECT DE VALORES CALCULADOS
+ SOMA * MULTIPLICAÇÃO- SUBTRAÇÃO / DIVISÃO
Ganho total de cada pessoa do DEPT 20
SELECT ID, SALARIO, COMIS, SALARIO + COMIS AS TOTALFROM x.staffWHERE DEPT = 20
Resultado:
ID SALARIO COMIS TOTAL10 18357.50 - -20 18171.25 612.45 18783.7
080 13504.60 128.20 13632.8
0190 14252.75 126.50 14379.2
5
Pessoas cujo salário mais comissão excede R$ 20.000
SELECT NOME, SALARIO + COMIS AS TOTALFROM x.staffWHERE SALARIO + COMIS > 20000
Resultado:
NOME TOTALOLIVEIRA 20094.15GRAHAM 21200.30
ORDER BY POR VALOR CALCULADO
Exemplo 1:
SELECT NOME, SALARIO + COMISFROM x.staffWHERE SALARIO + COMIS > 20000ORDER BY 2 DESC
Resultado:
NOME TOTALGRAHAM 21200.30OLIVEIRA 20094.15
Exemplo 2:
SELECT CARGO, SALARIO + COMISFROM x.staffORDER BY CARGO, 2
OPERADORES DE CONCATENAÇÃO
string1 || string2 string1string2
‘ABC’ || ‘XYZ’ ‘ABCXYZ’
TABELAX ( colunas varchar : SNOM e NOME )
SNOM NOME MIDJONES JOHN PMARQUES FRANCO X. . .
SELECT SNOM || ‘,’ || NOME||‘ ‘ || MID || ‘.’FROM TABELAX
Resultado:JONES,JOHN P.MARQUES,FRANCO X.. . .
SELECT AVANÇADO
FUNÇÕES DE COLUNA
Transforma dados de uma coluna em um único valor.
Dados numéricos SUM Total dos valores de uma coluna AVG Média dos valores de uma coluna
Quaisquer tipos de dados MIN Menor valor de uma coluna MAX Maior valor de uma coluna COUNT Número de ocorrências
Notas:
Informações detalhadas não podem ser mostradas. Por exemplo, se for pedida a média salarial por departamento, os valores da coluna NOME não podem ser mostrados.
Nulos são ignorados nos cálculos.
SUM AVG MAX MIN
Exemplo:
SELECT SUM(SALARIO), AVG(SALARIO), MIN(COMIS), MAX(COMIS)
FROM x.staffWHERE DEPT = 66
Resultado:
86076.20 17215.24 55.50 844.00
Notas:
Geram um único valor a partir de um conjunto de valores de uma única coluna. O resultado não contém detalhes de linhas individuais. Podem ser pedidas mais de um função para a mesma coluna, conforme o exemplo. Nulos são excluídos. Precisão (parte inteira, casa decimal) deriva da coluna. Para a função AVG de uma coluna DECIMAL(p,s) o resultado terá precisão. Expressões do seguinte tipo também podem ser argumento de uma função de coluna.
Exemplo: AVG(SALARIO + COMIS)
COUNT
COUNT(*) Número de linhas que satisfazem a condição WHERE
COUNT(DISTINCT coluna) Número de valores diferentes na coluna Nulos não são contados.
Contar as pessoas cujo salário exceda $ 18.000 e contar os seus respectivos departamentos. Mostre a média salarial.
SELECT COUNT(DISTINCT DEPT), COUNT(*), AVG(SALARIO)FROM x.staffWHERE SALARIO > 18000
Resultado:
8 16 19604.190625
SELECT AVG(COMIS), SUM(COMIS) FROM x.staff
Resultado:
513.31 12319.45
LITERAIS
SELECT ‘MED:’ AVG(COMIS), ‘SOMA:’ SUM(COMIS)FROM x.staff
Resultado:
MED: 513.31
SOMA: 12319.45
Notas:
Literais podem ser mostradas no resultado de um SELECT São Particularmente úteis quando usadas com funções de coluna que não exibem cabeçalhos.
GROUP BY
Com o GROUP BY, a função de coluna calcula um valor para cada AGRUPAMENTO.
SELECT DEPT, SUM(SALARIO) as TOTSAL, SUM(COMIS) as TOTCOM
FROM x.staffWHERE CARGO <> ‘GER’GROUP BY DEPT
Resultado:
DEPT TOTSAL TOTCOM15 41269.53 1468,7020 45928.60 867.1538 59778.80 1913.30
: : :
Notas:
A Função desta cláusula é mostrar informações calculadas por grupo de dados. Note que o resultado é classificado. Isto porque o GROUP BY classifica a tabela numa fase intermediária para facilitar o cálculo para os grupos. A classificação se dá em ordem ascendente a menos que exista um índice definido para a coluna. Neste caso a classificação não é feita. A sequência obedecerá a sequência do índice que pode estar definida como sendo de ordem descendente.
GROUP BY ... ORDER BY
Podemos alterar a ordem de classificação
SELECT DEPT, SUM(SALARIO) AS TOTSAL,SUM(COMIS) AS TOTCOM
FROM x.staffWHERE CARGO <> ‘GER’GROUP BY DEPT ORDER BY 3
Resultado:DEPT TOTSAL TOTCOM
20 45928.60 867.15. . .
15 41269.53 1468.70. . .
38 59778.80 1913.30
Notas:
Podemos fazer GROUP BY sobre múltiplas colunas. Isto faz com que sejam formados subgrupos dentro de grupos.
GROUP BY DEPT, CARGO
Colunas GROUP BY não precisam ser referenciadas pelo SELECT.
Mas colunas referenciadas pelo SELECT que não sejam funçãoprecisam ser agrupadas pelo GROUP BY. Isto é um erro muito frequente.
Resultado contém uma linha sumário para cada grupo.
Todos os valores nulos são considerados como um grupo.
GROUP BY ... HAVING
Condições sobre grupos
SELECT . . . FROM . . . WHERE . . .
GROUP BY . . . FILTROSHAVING . . .
WHERE - Escolhe linhasHAVING - Escolhe grupos
SELECT DEPT, SUM(SALARIO) AS TOTSALFROM x.staffGROUP BY DEPT
DEPT TOTSAL10 83463.4515 61929.3320 64286.1038 77285.5542 58369.05
: :
SELECT DEPT, SUM(SALARIO) AS TOTSALFROM x.staffGROUP BY DEPTHAVING SUM(SALARIO) > 65000
DEPT TOTSAL10 83463.4538 77285.55
: :
EXEMPLOS DE HAVING(Continuação)
Média salarial por departamento. Considerar apenas os não-gerentes dos departamentos com mais de 3 pessoas sem cargo gerencial.
SELECT DEPT, AVG(SALARIO) AS MEDIASALFROM x.staffWHERE CARGO <> ‘GER’HAVING COUNT(*) > 3
Média salarial por departamento em ordem decrescente de média. Considerar apenas os não-gerentes dos departamentos compostos por pessoas com experiência mínima de 5 anos.
SELECT DEPT, AVG(SALARIO) AS MEDIASALFROM x.staffWHERE CARGO <> ‘GER’GROUP BY DEPTHAVING MIN(ANOS) >= 5ORDER BY 2 DESC
Notas:
Primeiro exemplo mostra que o SELECT e o HAVING podem usar funções diferentes. O Segundo exemplo ilustra que a tabela resultante pode ser classificada.
SUMÁRIO FUNÇÕES DE COLUNA
Funções de colunas só podem ser especificados em: SELECT HAVING
SELECT pode especificar somente: Função de Colunas Colunas especificadas no GROUP BY
HAVING pode especificar: Função de colunas sobre qualquer coluna contida numa tabela especificada na FROM.
Funções de Colunas não podem ser embutidas uma dentro da outra
FUNÇÕES ESCALARES
Transforma um único valor em outro valor.
Funções STRING Funções de conversão Funções DATE/TIME
Notas:
Ao contrário das Funções de colunas que transformam um conjunto de valores de uma coluna num único valor, as funções Escalares operam somente sobre um único valor. Funções escalares podem ser embutidas uma dentro da outra.
Exemplo: SUBSTR(SUBSTR(...)...) Funções de colunas podem ser argumento de uma função escalar.
Exemplo: MAX(SUBSTR(...))
SUBSTR(string, início, comprimento)
SELECT DEPTNOME, SUBSTR(DEPTNOME,1,4) as NOMEPARCFROM x.org
Resultado:
DEPTNOME NOMEPARC
MATRIZ MATRCAMPINAS CAMPRIO RIOMINAS MINABAHIA BAHI
. .
SELECT DEPTCOD, DIVISÃOFROM x.orgWHERE SUBSTR(DIVISÃO,4) = ‘ESTE’
Resultado:
DEPTCOD
DIVISÃO
15 SUDESTE20 SUDESTE38 SUDESTE
Notas:
Se o Comprimento não for especificado, a função pega todo o resto. Se a posição do início ultrapassar o final do string teremos erro. Se o início + comprimento ultrapassar o final do string teremos erro. Substring de nulo é nulo.
LENGTH
SELECT NOME, LENGTH(NOME) AS TAMNOME,CARGO, LENGTH(CARGO) AS TAMCAR
FROM x.staff
Resultado:
NOME TAMNOME
CARGO TAMCAR
SANTANA 7 GER 6ORSINI 6 VENDAS 6GONZALES 8 VENDAS 6
. . . .
Notas:
Fornece o tamanho de uma coluna do tipo caracter. Se o argumento é nulo, o resultado também será nulo.
VALUE
Dada uma lista de argumentos, mostra o primeiro não nulo
SELECT ID, COMIS, VALUE(COMIS, 0) FROM x.staff
Resultado: ID COMIS
10 - 0.0020 612.45 612.4530 - 0.00
. . .
Notas:
Dada uma lista de argumentos, esta função faz a varredura da esquerda para a direita até encontrar o primeiro valor não nulo. Todos os argumentos devem ter data types comparáveis (todos numéricos ou todos caracteres). Se todos argumentos forem nulos, o resultado será nulo.
FUNÇÕES DE CONVERSÃO
Converte um tipo de representação em outro.
DECIMAL, FLOAT, INTEGER Número - NúmeroDIGITS Número - AlfaHEX Hexadecimal - Alfa
SELECT DECIMAL(SAL,9,1), INTEGER(SAL)FROM EMPWHERE ID = 55
Se coluna SAL é (DEC (7,2)):
17506.75
17506.7 17506
Notas:
DECIMAL, FLOAT, INTEGER: Converte dados numéricos DIGITS: Representação alfanumérica de um valor numérico HEX: Representação hexadecimal de um argumento. Cada digito hexadecimal é representada por 2 caracteres do string resultante.
DADOS DATE/TIME
DATE, TIME, TIMESTAMP armazenados como decimal compactado sem sinal
Data Types Formato InternoDATE aaaammddTIME hhmmssTIMESTAMP aaaammddhhmmssnnn
nnn
Programas lidam só com FORMATO EXTERNO: string de caracteres
Formato
Formato TIME
Tamanho
Formato Date
Tamanho
ISO hh.mm.ss 8 bytes aaaa-mm-dd 10 bytesUSA hh.mm AM/PM 8 bytes mm/dd/aaaa 10 bytesEUR hh.mm.ss 8 bytes dd.mm.aaaa 10 bytesJIS hh:mm:ss 8 bytes aaaa.mm.dd 10 bytes
ARITMÉTICA DATE/TIME
Somente adição e subtração Pode usar MIN, MAX, COUNT Não pode usar SUM e AVG
DURACAO DATE/TIME
DURAÇÃO SIMPLES
Horário + ou - Horário Duração em hhmmss(packed decimal(8,0))
data + ou - data Duração em aaaammdd(packed decimal(8,0))
Duração Rotulada
Número ‘n’ seguida da palavra chave: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, MICROSECONDS
Somando/Subtraindo duraçãoDATACOL1 + 3 MONTHS Resulta em DataTEMPCOL1 - 30 MINUTES - 20 SECONDS Resulta em tempoTSTCOL + 2 DAYS - 3 HOURSTMSTCOL + 50000 MICROSECONDS
Subtraindo datas e temposDATACOL1 - DATACOL2DATACOL1 - ’25.12.1998’TEMPCOL1 - TEMPCOL2TEMPCOL1 - ’11.30.00’
EXEMPLOS ARITMÉTICA DATE/TIME
Tabela DT
PROJCOD DATAINI DATAFIM HRINI HRFIMAX001 31.01.1990 31.03.2005 00:00:00 15:30:00AX002 29.02.1988 31.08.1988 00:00:00 00:00:00AX003 01.02.1987 01.02.1987 16:00:00 23:41:59
SELECT PROJCOD, DATAINI + 1 YEAR AS DATAINI, DATAFIM - 2 MONTHS AS DATAFIM ...
Resultado:PROJCOD DATAINI DATAFIMAX001 31.01.1991 31.01.2005AX002 28.02.1989 30.06.1988AX003 01.02.1988 01.12.1986
SELECT PROJCOD, DATAFIM - DATAINI AS aaaammdd, HRFIM - HRINI AS hhmmss ...
Resultado:
PROJCOD aaaammdd
hhmmss
AX001 00150200 153000AX002 00000602 000000AX003 00000000 074159
FUNÇÕES ESCALARES DATE/TIME
CHAR: Controla formato externo de dados DATE/TIME
SELECT CHAR(HRFIM,ISO) AS HR_ISO, CHAR(HRFIM,USA) AS HR_USAFROM DT WHERE PROJCOD = ‘AX001’
Resultado:
HR_ISO HR_USA15.30.00 03:30 PM
DAY, MONTH, YEAR HOURS, MINUTE, SECOND MICROSECOND
Extrai parte de uma data, horário ou timestampResulta num inteiro binário.
SELECT DAY(DATAINI) AS DIA, MONTH(DATAINI) AS MÊS, YEAR(DATAINI) AS ANO FROM DTWHERE YEAR(DATAINI) > 1988
Resultado:DIA MÊS ANO
31 1 1990
FUNÇÕES ESCALARES DATE/TIME(Continuação)
DAYS Número de dias desde 31/01/0000
SELECT DATAFIM - DATAIN AS DATAS, DAYS(DATAFIM) - DAYS(DATAIN) AS NU_DIAS FROM DTWHERE PROJCOD = ‘AX001’
Resultado:
DATAS NU_DIAS00150228 5538
aaaammdd (n.º dias)
DATE, TIME
Extrai data ou horário de um timestamp.
CURRENT DATE
Data de hoje
CURRENT TIME
Horário do momento.
CURRENT TIMESTAMP
Data e horário do momento convertido para Timestamp.
JOIN DE TABELAS
Mesmo domínio
ID NOME DEPT
10 SANTANA 2020 ORSINI 2030 DANTAS 3840 O’BRIEN 3850 SOUZA 1560 SUZUKI 38 DEPTCO
DDEPTNOME
GERENTE
70 ALMEIDA 15 10 MATRIZ 16080 JAMES 20 15 CAMPINAS 5090 KOONITZ 42 20 RIO 10
100 PLOTZ 42 38 MINAS 30. . . 42 BAHIA 100
51 RECIFE 140
Esta técnica serve para obtermos informações que estão espalhadas em uma ou mais tabelas. Tabelas são relacionadas via dados comuns Join implementado pelas cláusulas FROM Resultado é um subconjunto do produto cartesiano das tabelas.
JOIN DE TABELAS(Continuação)
Listar o nome dos departamentos e seus respectivos gerentes.
SELECT DEPTNOME, NOMEFROM x.staff, x.orgWHERE GERENTE = ID
Resultado:DEPTNOME
NOME
RIO SANTANAMINAS DANTASCAMPINAS SOUZA. .
Listar o nome dos departamentos da divisão Sudeste e seus respectivos gerentes.
SELECT DEPTNOME, NOMEFROM x.staff, x.orgWHERE DIVISAO = ‘SUDESTE’ AND GERENTE = ID
ATENÇÃO:- Procure não fazer JOIN de tabelas sem a cláusula WHERE.
SELECT A1, B1 FROM TBLA, TBLB Produto Cartesiano.
A1 A2
A3
R1
R2 B1 B2 B3
A A X XB A Y YC B X
B YC XC Y
JOIN COM MAIS DE DUAS TABELAS
SELECT ID, NOME, DEPT, DEPTNOME, CODPEDIDOFROM x.staff, x.org, x.vendasWHERE DEPT = DEPTCOD AND ID=COD_RVENDA
Resultado:
ID NOME DEPT DEPTNOME
CODPEDIDO
20 ORSINI 20 RIO 345620 ORSINI 20 RIO 666720 ORSINI 20 RIO 358020 ORSINI 20 RIO 701040 O’BRIEN 38 MINAS 4567. . . . .
QUALIFICADORES
COD NOME DEPT
CARGO
COD DEPTNOME
GERENTE
10 SANTANA 20 GER 10 MATRIZ 16020 ORSINI 20 VENDA . . .. . . . 10 RIO 10
Listar o nome dos gerentes, e respectivos nome e código de departamentos.
SOLUÇÃO:
Qualificar com o nome da tabelaSELECT NOME, x.org.CODIGO, DEPTNOMEFROM x.staff, x.orgWHERE CARGO = ‘GER’ AND DEPT = x.org.CODIGO
Qualificador correlacionado
SELECT NOME, Z.CODIGO, DEPTNOMEFROM x.staff, x.org ZWHERE CARGO = ‘GER’ AND DEPT = Z.CODIGO
QUALIFICADORES
Notas:
Aqui neste exemplo temos um caso onde duas colunas de tabelas distintas tem o mesmo nome. A solução para distingui-las é qualificar o nome da coluna com o nome da tabela. Outra técnica é a utilização do qualificador correlacionado onde atribuímos um rótulo à tabela.No nosso caso exemplo a tabela x.org foi rotulada como “Z”. Assim a coluna Z.CODIGO é uma referência à coluna x.org.CODIGO.Esta rotulação não é permanente e vale somente durante a execução da instrução SELECT que faz uso da mesma.
JOIN DE UMA TABELA COM ELA MESMA
Liste os empregados que ganham mais que os seus gerentes
CONCEITUALMENTE:
1. Obter um empregado da VEMPRMATR NOME ... SALARI
O... GER
150 OLIVEIRA 19456.50 140
2. Obter informações do gerente da VEMPR
MATR NOME ... SALARIO ... GER140 SANTANA 18357.50 050
3. Comparar o salário dos dois
4. Se a condição é satisfeita, colocar a linha na tabela resultante e partir para o próximo empregado.
Solução:
SELECT E.NOME, E.SALARIO, G.NOME, G.SALARIOFROM VEMPR E, VEMPR G WHERE E.GER = G.MATR AND E.SALARIO > G.SALARIO
Resultado:NOME SALARIO NOME SALARIOOLIVEIRA 19456.50 SANTANA 18357.50
. . . .
. . . .
INNER JOIN
Uma outra maneira de codificar o JOIN entre duas tabelas
SELECT DEPTNOME, NOMEFROM x.staff INNER JOIN x.orgON GERENTE = ID
Resultado:DEPTNOME
NOME
RIO SANTANAMINAS DANTASCAMPINAS SOUZA. .
Nota:
Quando utilizamos a cláusulas JOIN deve mudar o predicado WHERE por ON.
TABELAS EXEMPLOS:EMPL DEPTOEMPNO NOME DEPTNOME CODEMP
10 HAAS SPIFFY COMPUTER 10150 ADAMSON DEVELOPMENT -
20 THOMPSON SOFTWARE 100250 SMITH ADMINISTRATION 700100 SPENSER BRANCH OFFICE -
70 PILASKI140 NATZ
OUTER JOIN
Seleciona todas as linhas de um INNER JOIN mais as linhas não selecionadas de ambas as tabelas.
RIGHT OUTER JOIN Seleciona as linhas da tabela à direita. LEFT OUTER JOINSeleciona as linhas da tabela à esquerda. FULL OUTER JOIN Seleciona as linhas de ambas as tabelas.
SELECT NOME, DEPTNOMEFROM EMPL RIGHT OUTER JOIN DEPTOON EMPNO = CODEMP
SELECT NOME, DEPTNOMEFROM EMPL LEFT OUTER JOIN DEPTOON EMPNO = CODEMP
Resultados: RIGHT OUTER JOIN LEFT OUTER JOINNOME DEPTNOME NOME DEPTNOMEHAAS SPIFFY COMPUTER HAAS SPIFFY COMPUTER- DEVELOPMENT ADAMSON -SPENSER SOFTWARE THOMPSON -PULASKI ADMINISTRATION SMITH -- BRANCH OFFICE SPENSER SOFTWARE
PULASKI ADMINISTRATIONNATZ -
EXPRESSÕES CASE
Em tempo de retorno do resultado da Query podemos atribuir valores a serem mostrados/exibidos a determinados retornos, o exemplo abaixo nos dará uma boa noção desta facilidade.
SELECT PARTNO, DESCRIPT, COLOR, CASE WHEN SIZE = ‘S’ THEN ‘Small’
WHEN SIZE = ‘M’ THEN ‘Medium’WHEN SIZE = ‘L’ THEN ‘Large’WHEN SIZE = ‘X’ THEN ‘Xlarge’ELSE ‘DESCONHECIDO’ END AS SIZE, QTY
FROM QTY <= REORDER_PT
Resultado: PARTNO DESCRIPT COLOR SIZE QTY12-234 SWEATER BLUE SMALL 1812-235 SWEATER RED MEDIUM 2214-276 JACKET GREEN DESCONHECIDO 9
Mais Expressões CASE
Usando na cláusulas WHERE
... WHERE (CASE WHEN SALARY = 0 THEN NULLELSE COMM/SALARY END) > .25
Usando em Funções de colunas
... MIN (CASE WHEN QTY > 0 THEN QTY ELSE NULL END)
UNION
UNION junta as tabelas resultado
SELECT A1, A4 FROM TBLAWHERE A2 = ‘Z’
Resultado
UNION
SELECT B3, B4 + B2 FROM TBLB WHERE B1 >= ‘N’
Resultado
Notas:
Esta técnica faz a união de uma ou mais tabelas resultado.Query complexas podem ser feitas usando o UNION.A Quantidade de colunas deve ser a mesma em todos os SELECTs.Os data types das colunas correspondentes devem ser iguais.O tamanho das colunas correspondentes podem ser diferentes. O operador UNION une as tabelas resultantes e elimina as linhas duplicadas, que implica num sort. operador UNION ALL também une tabelas mas não elimina linha duplicadas. Não chama o SORT portanto. Para os nomes das colunas no cabeçalho são adotadas as do primeiro bloco de SELECT.
UNION(Continuação)
A faixa de salário dos atendentes atingiria a faixa de salários do pessoal de vendas se fosse dado um aumento de 17% e 2% respectivamente ?
SELECT CARGO, SALARIO * 1.17 AS SALARIO FROM x.staff WHERE CARGO = ‘ATEND’UNIONSELECT CARGO, SALARIO * 1.02 FROM x.staff WHERE CARGO = ‘VENDAS’ORDER BY 2 DESC
Resultado: CARGO SALARIOVENDAS 21420.0000. .. .VENDAS 17144.4660ATEND 16918.2000VENDAS 16832.8866
SELECT NOME FROM X.CANDIDATOS WHERE ENDERECO LIKE ‘%SP’
UNION ALL UNION SELECT SOBRENOME FROM X.ENTREVISTADOSWHERE SIT = ‘REP’
Resultado:NOME NOMEJACOBS ARANTESMARQUES JACOBSARANTES MARQUESSILVA SILVAMARQUESJACOBS
Cabeçalho do primeiro SELECT
SUBQUERY
É um SELECT embutido dentro de um SELECT O resultado de uma subquery é usado pelo SELECT “EXTERNO”
Exemplo de SUBQUERY.
Listar empregados cujo departamento esteja subordinado ao departamento ‘A00’
SNOME DEPARTAMENTO
DCOD DSUPER
SELECT SNOME, DEPARTAMENTOFROM EMP WHERE DEPARTAMENTO IN (SELECT DCOD
FROM DEPTWHERE DSUPER = ‘A00’)
RESULTADO SOMENTE RESULTADO DA SUBQUERYDO SELECT DE NÍVEL RETORNADO PARA OMAIS ALTO NÍVEL SUPERIOR
Considerações SUBQUERY:1. Pode ser usada no WHERE ou no HAVING2. Pode ser encadeado3. A escolha do operador do SELECT externo depende da
quantidade de linhas do SUBQUERY.4. Deve estar a direita do operador na condição de seleção.5. Deve ser colocado entre parênteses.6. O SUBQUERY deve selecionar apenas um coluna.7. O SUBQUERY pode retornar uma ou mais linhas e isto acaba
determinando o operador a ser usado na condição de procurar.8. Não pode conter UNION, UNIN ALL ou ORDER BY.9. SUBQUERY conhecido como SUBSELECT.SUBQUERY DE UMA LINHA
Listar os funcionários com salário superior à média da companhia
SELECT NOME, SALARIO FROM x.staff WHERE SALARIO > (SELECT AVG(SALARIO)
FROM x.staff)
Resultado: NOME SALARIOSANTANA 18357.50ORSINI 18171.25
Mostrar o funcionário com o maior salário.
SELECT NOME, SALARIOFROM x.staff WHERE SALARIO = (SELECT MAX(SALARIO)
FROM x.staff)
Resultado:
NOME SALARIOMOLINARI 22959.20
SUBQUERY COM VÁRIAS LINHAS: ALL
Lista classificada dos funcionários com salário superior a toda e qualquer média salarial departamental.
Encontrar média salarial para cada departamento.(SELECT AVG(SALARIO) FROM x.staff GROUP BY DEPT)
Compare o salário de cada funcionário com a lista devolvida pela SUBQUERY.
SELECT NOME, SALARIO FROM x.staffWHERE SALARIO > ALL (SELECT AVG(SALARIO)
FROM x.staff GROUP BY DEPT)ORDER BY NOME
Resultado:
NOME SALARIOFRAYE 21150.00GRAHAM 21000.00JONES 21234.00MOLINARE 22959.20
Lista classificada dos funcionários com salário maior que a média salarial de qualquer departamento.
Encontrar a média salarial para cada departamento. Compare o salário e cada funcionário com a lista devolvida pela SUBQUERY.
SELECT NOME, SALARIO FROM x.staffWHERE SALARIO > ANY(SELECT AVG(SALARIO)
FROM x.staff GROUP BY DEPT)ORDER BY NOME
Resultado:
NOME SALARIODANIELS 19260.25DAVIS 15454.50EDWARDS 17844.00
IN
Lista de gerentes da divisão Sul
SELECT DEPT, NOME FROM x.staff WHERE ID = ANY (SELECT GERENTE FROM x.org
WHERE DIVISAO = ‘SUL’)
SUBQUERY Resulta numa lista de Gerentes da divisão SUL ‘IN’ equivale a um série de ‘OR’ ... = ‘ sobre uma coluna ‘=ANY’ pode substituir ‘IN’
SELECT DEPT, NOME FROM x.staff WHERE ID IN (SELECT GERENTE FROM x.org
WHERE DIVISAO = ‘SUL’)
HAVING COM SUBQUERY
Liste os departamentos cuja média salarial seja inferior à média da companhia. Ignore os gerentes. Mostre as médias e ordene pelas mesmas.
SELECT DEPT, AVG(SALARIO) FROM x.staffWHERE CARGO <> ‘GER’ GROUP BY DEPTHAVING AVG(SALARIO) < (SELECT AVG(SALARIO)
FROM x.staff WHERE CARGO <> ‘GER’)
ORDER BY 2 DESC
Resultado: DEPT
66 16880.17500000051 16235.20000000084 15443.00000000020 15309.530000000
SUBQUERY CORRELACIONADA
Listar os empregados com salário superior à média de seus respectivos departamento.
SELECT NOME, SALARIO FROM x.staff CVWHERE SALARIO > (SELECT AVG(SALARIO) FROM x.staff
WHERE DEPT = CV.DEPT)
Uma SUBQUERY correlacionada é executada a cada linha devolvida ao SELECT externo!
Listar os gerentes ganhando menos que $18.000 se existir pelo menos um gerentes com salário superior à $22.000.
SELECT ID, NOME, SALARIO, DEPT FROM x.staffWHERE CARGO = ‘GER’ AND SALARIO < 18000 AND EXISTS(SELECT * FROM x.staff
WHERE CARGO = ‘GER’ AND SALARIO > 22000)
ID NOME SALARIO DEPT30 MARENGHI 17506.75 38
Verdadeiro/Falso no SELECT internoDetermina se o SELECT externo será executado.
Notas:
Este é um operador que tem como argumento um SELECT. Esta SUBQUERY não devolve nenhuma tabela resultante. EXISTS devolve V ou F e como faz parte de uma condição de procura, determina uma eventual execução ou não do SELECT externo. EXISTS pode ser negado por um NOT.
MANUTENÇÃO DE DADOS - INSERT
Insert de uma linha
INSERT INTO PROJVALUES(‘MA2114’, ‘ ‘, ‘B01’, ‘ ‘, NULL, CURRENT DATE, NULL)
OU INSERT INTO PROJ(DEPT, PCODIGO, PNOME, RESP, DATAINI) VALUES(‘B01’, ‘MA2114’, ‘ ‘, ‘ ‘, CURRENT DATE)
Resultado: PCODIGO
PNOME DEPT RESP EQUIPE DATAINI DATAFIM
........... .............. ....... ........ ............. ................. ................OP2011 SUPORTE E21 320 001.00 25.12.1998 01.02.1999OP2012 APLICACAO E21 330 001.00 25.12.1998 01.02.1999OP2013 DB/DC E21 340 001.00 25.12.1998 01.02.1999MA2114 B01 ? 26.12.1998 ?
INSERT de múltiplas linhas
TESTPROJPCOD PROJNOME DEPT RESP EQUIPE DATAINI DATAFIM
INSERT INTO TESTPROJ SELECT * FROM PROJ WHERE DEPT LIKE ‘B%’
OR DEPT IN (‘E11’. ‘E21’, ‘E31’)
ResultadoPCOD PROJNOME DEPT RESP EQUIPE DATAINI DATAFIMPL2100 ...... B01 20 001.00 01.01.1987 15.09.1987OP1010 OPERAÇÃO E11 90 005.00 01.01.1986 01.05.1988OP2010 SUPORTE II E21 100 004.00 01.01.1982 01.02.1988OP2011 ..... E21 320 001.00 01.01.1982 01.02.1988OP2012 ..... E21 330 001.00 01.01.1982 01.02.1988OP2013 ..... E21 340 001.00 01.01.1982 01.02.1988
MANUTENÇÃO DE DADOS - UPDATE
Update de colunas
Antes:PCOD PROJNOME DEPT RESP EQUIPE DATAINI DATAFIMPL2100 ...... B01 20 001.00 01.01.1987 15.09.1987OP1010 OPERAÇÃO E11 90 005.00 01.01.1986 01.05.1988OP2010 SUPORTE II E21 100 004.00 01.01.1982 01.02.1988OP2011 ..... E21 320 001.00 01.01.1982 01.02.1988OP2012 ..... E21 330 001.00 01.01.1982 01.02.1988OP2013 ..... E21 340 001.00 01.01.1982 01.02.1988
UPDATE TESTPROJSET EQUIPE = EQUIPE*2, DATAFIM=NULL,
DATAINI=’02.01.1999’WHERE DEPT = ‘B01’
Depois:PCOD PROJNOME DEPT RESP EQUIPE DATAINI DATAFIMPL2100 ...... B01 20 002.00 02.01.1999 ?OP1010 OPERAÇÃO E11 90 005.00 01.01.1986 01.05.1988OP2010 SUPORTE II E21 100 004.00 01.01.1982 01.02.1988OP2011 ..... E21 320 001.00 01.01.1982 01.02.1988OP2012 ..... E21 330 001.00 01.01.1982 01.02.1988OP2013 ..... E21 340 001.00 01.01.1982 01.02.1988
Um subconjunto de linhas
UPDATE PESSOAL SET SALARIO = SALARIO + 300WHERE CARGO = ‘ATEND’
Todas as linhas:
UPDATE PESSOALSET ANOS = ANOS + 1
MANUTENÇÃO DE DADOS - DELETE
Antes: PCOD PROJNOME DEPT RESP EQUIPE DATAINI DATAFIMPL2100 ...... B01 20 002.00 02.01.1999 ?OP1010 OPERAÇÃO E11 90 005.00 01.01.1986 01.05.1988OP2010 SUPORTE II E21 100 004.00 01.01.1982 01.02.1988OP2011 ..... E21 320 001.00 01.01.1982 01.02.1988OP2012 ..... E21 330 001.00 01.01.1982 01.02.1988OP2013 ..... E21 340 001.00 01.01.1982 01.02.1988
DELETE FROM TESTPROJ WHERE DEPT = ‘B01’
Depois:PCOD PROJNOME DEPT RESP EQUIPE DATAINI DATAFIMOP1010 OPERAÇÃO E11 90 005.00 01.01.1986 01.05.1988OP2010 SUPORTE II E21 100 004.00 01.01.1982 01.02.1988OP2011 ..... E21 320 001.00 01.01.1982 01.02.1988OP2012 ..... E21 330 001.00 01.01.1982 01.02.1988OP2013 ..... E21 340 001.00 01.01.1982 01.02.1988
Eliminar todas as ocorrências de uma tabela
DELETE FROM TESTPROJ
Depois:PCOD PROJNOME DEPT RESP EQUIPE DATAINI DATAFIM
DDL
DATA DEFINITION LANGUAGE
DDL
A Data Definition Language(DDL) faz parte das instruções SQL e divide-se em três grupos.
1. CREATE Definição de objetos2. ALTER Alteração da definição dos objetos3. DROP Eliminação de objetos
A execução de DDL é permitido somente à auth-id’s devidamente autorizados.
O Criador (auth-id) do objeto é normalmente o proprietário (OWNER) do mesmo. Mas o DB2 permite que sejam criados objetos para outros auth-id, isto é, posso criar objetos das quais não serei o proprietário.
O proprietário de um objeto tem todos os privilégios sobre o mesmo.
A equipe de suporte (SYSADM, SYSCTRL, DBADM) ou proprietário do objeto é o responsável pela concessão de autorização para acesso e/ou uso dos objetos.
A execução das instruções ALTER ou DROP estão reservadas à equipe de suporte ou o proprietário dos objetos.
STORAGE GROUP
O nome de um STORAGE GROUP não deve ultrapassar 8 caracteres Podem ser especificados até 133 volumes do mesmo device type no parâmetro VOLUMES.
VCAT aponta para o catálogo ICF que qualificará os Datasets dos Tablespaces e dos Indexspaces.
Exemplos:
Para criação de um Storage Group
CREATE STOGROUP CURXSG01VOLUMES(PRD510)VCAT DES;
COMMIT;
Alteração de um Storage Group:
ALTER STOGROUP CURXSG01ADD VOLUMES(PRD512, PRD513) REMOVE VOLUMES(PRD510);
COMMIT;
ALTER STOGROUP CURXSG01VCAT CUR;
COMMIT;
DATABASE
O Database é uma definição lógica, onde podemos agrupar todos os Tablespaces, Tabelas, Índices com o mesmo critério de administração. Autorização necessária para Criação de um DATABASE Privilégio de CREATEDBAPrivilégio de CREATEDBCAutoridade de SYSADM ou SYSCTRL
CREATE DATABASE CURXSG01BUFFERPOOL BP0STOGROUP CURXSG01;
COMMIT;
Parâmetros do Create DATABASE
1. BUFFERPOOL Indica o Buffer pool default para os Tablespaces e Índices deste Database.
2. STOGROUP Indica o Stogroup default para todos os Objetos deste Database.
TABLESPACE
É uma VSAM LDS, onde serão definidas as tabelas.Existem 3 tipos de Tablespaces:
1. SIMPLES As páginas de seus datasets contém dados de uma ou mais tabelas. Aqui na CEF não utilizamos este tipo de Tablespace.
2. Segmentado É dividido em segmentos que variam de 4 a 64 páginas e sempre em múltiplos de 4. E cada segmento só pode conter ocorrências de uma única tabela, tamanho máximo deste tipo de tablespace é 2Gbytes.
3. Particionado Utilizada para tabelas com grande quantidade de dados, podendo chegar até 64Gbytes, assim distribuídos:
Até 16 Partições Cada partição com 4Gbytes de 17 a 32 Partições Cada partição com 2Gbytes de 33 a 64 Partições Cada partição com 1Gbytes
Exemplo de Criação de Tablespace SEGMENTADO.
CREATE TABLESPACE CURXTS01 IN CURXDB01USING STOGROUP CURXSG01PRIQTY 144SECQTY 48ERASE NOLOCKSIZE ANYBUFFERPOOL BP0CLOSE NOFREEPAGE 4PCTFREE 25SEGSIZE 8;
COMMIT;
ALTER TABLESPACE CURXDB01.CURXTS01PRIQTY 240USING STOGROUP CURXSG02;
COMMIT;
TABLESPACE
Parâmetros
PRIQTY, SECQTY - Área de alocação em Kbytes. LOCKSIZE - Tipo de Locking, as opções são:
Any - É o valor default , com este valor a decisão pelo Locking fica por conta do DB2.
Tablespace - Locking pelo tablespace Table - Locking pela Tabela PAGE - Locking por página ROW - Locking por Linha
ERASE - Especifica se o espaço ocupado pelo datasets devem ser preenchidos com zero quando forem eliminados (DROP). CLOSE - Especifica se o Dataset do tablespace deve ser fechado quando não esta sendo utilizado. FREEPAGE - Especifica o intervalo de páginas que devem existir entre as páginas livres quando um tablespace é carregado ou reorganizado. O intervalo pode variar de 0 á 255. Se o tablespace é segmentado, o número deve ser menor que o tamanho do segmento. PCTFREE - Especifica qual é a fração percentual das páginas que devem ser mantidas livres quando o tablespace é carregado ou reorganizado. SEGSIZE - Se especificado, determina que o tablespace será segmentado. O tamanho de um segmento é dado em quantidade de páginas, deve ser inteiro múltiplos de 4, variando de 4 á 64. NUMPARTS - Parâmetro mutuamente exclusivo como o SEGSIZE, quando especificado indica que o Tablespace será Particionado, é acompanhado de um número que indica a quantidade de partições. Este número pode variar de 1 á 64.
Se nem SEGSIZE e nem NUMPARTS forem especificados, o Tablespace será Simples.
TABLE
É um conjunto de linhas coma as mesmas colunas. Os atributos das colunas dever ser do tipo:
NOT NULL Preenchimento obrigatório NOT NULL WITH DEFAULT Quando não preenchido, assume o valor default NULL Preenchimento opcional
Definições para colunas numéricas: SMALLINT Inteiros, valor entre -32768 a 32767 INTEGER Inteiros, valor entre -2147483648 a 2147483648 DECIMAL (x,y) x=Dígitos e y=Decimais, até 31 Dígitos FLOAT Ponto Flutuante 5.4E-79 TO 7.2E+75
Smallint é uma halfword (2bytes)Integer é uma fullword(4bytes)Decimal armazena números em decimal compactado. A faixa de valores depende da quantidade de casas decimais. Definições para colunas alfanuméricas:
CHAR(x) String de tamanho fixo, máximo 254 caracteres VARCHAR(x) String de tamanho variável, onde x pode variar de 0 a 4096 caracter.
Uma página DB2 não é totalmente utilizada para os dados pois existe um espaço ocupado para informação de controle.
Definições para colunas DBCS: GRAPHIC(x) String de tamanho fixo, máximo 127 caracteres. VARGRAPHIC(x) String de tamanho variável, onde x pode variar de 0 a 2048 caracteres.
Double Byte Caracter são caracteres especiais como ideogramas asiáticos que requerem 2 bytes para serem identificados.
TABLE
CREATE TABLE SICUR.CURXTB01FUNCAO(CODIGO SMALLINT NOT NULL, DESC_FUNCAO CHAR(20) NOT NULL, DATA_INCLUSAO DATE NOT NULL, PRIMARY KEY (CODIGO))IN CURXDB01.CURXTS01;
CREATE TABLE SICUR.CURXTB02CONCEITOS( MATR CHAR(9) NOT NULL, ALUNO CHAR(30) NOT NULL. FUNCAO SMALLINT NOT NULL, NOTA SMALLINT , CLASSIFICACAO SMALLINT ,PRIMARY KEY( MATR , ALUNO ),FOREIGN KEY (FUNCAO) REFERENCES SICUR.CURXTB01FUNCAOON DELETE CASCADE)IN CURXDB01.CURXTS01; ALTER TABLE SICUR.CURXTB02CONCEITO
ADD DT_PROVA DATE ;COMMIT;
PRIMARY KEY Especifica a chave primária da tabela, sendo que a chave pode ser composta de até 64 colunas. O comprimento de uma chave primária não pode exceder 254 bytes. Apenas uma chave primária é permitida por tabela.
FOREIGN KEY Especifica a chave estrangeira da tabela. A definição de uma chave estrangeira deve ser igual tamanho e tipo de dados. A palavra chave REFERENCES identifica a tabela mãe (Parent table). A Palavra chave ON DELETE especifica a regra de deleção.
ÍNDICE
É utilizado para agilizar o acesso aos dados.
CREATE [ UNIQUE ] INDEX SICUR.CURXPX02 ON SICUR.CURXTB02CONCEITO(MATR ASC, ALUNO ASC)USING STOGROUP CURXSG02 PRIQTY 48SECQTY 48 ERASE NOBUFFERPOOL BP1 CLOSE YESFREEPAGE 4PCTFREE 15CLUSTER;
COMMIT;
ALTER INDEX CURXPX02 USING STOGROUP CURXPX02 PRIQTY 192CLOSE YES ;
COMMIT;
Parâmetros:
UNIQUE Palavra chave que indica que o índice não
permite duplicidade nas colunas compostas neste índice.
CLUSTER Indica que o dados devem estar clusterizado
pelo índice
VIEW
A View pode poder um subconjuto de uma ou mais tabelas.Uma view é um objeto lógico e assim, seus dados não ocupam espaço físico.
CREATE VIEW SIXXX.CURXVW01JUNCAO (CODIGO, FUNCAO, ALUNO)AS SELECT CODIGO, DESC_FUNCAO, ALUNO
FROM SICUR.CURXTB02CONCEITO INNER JOIN SICUR.CURXTB01FUNCAO
ON CODIGO = FUNCAO;COMMIT;
SYNONYM E ALIAS
CREATE SYNONYM FUNCAO FOR SICUR.CURXTB01FUNCAO;COMMIT;
CREATE ALIAS CONCEITO FROM SICUR.CURXTB02CONCEITO;COMMIT;
Diferença entre e o SYNONYM e ALIAS:
SYNONYM é restrita a um AUTH-ID.ALIAS é de uso compartilhado a todos.
DROP
Usado para eliminar qualquer objeto criado a partir de um comando CREATE no DB2.
DROP DATABASE CURXDB01Quando se elimina um Database, todos os objetos vinculados
à ele também são eliminados, tais como TABLESPACE, TABLE, INDEX, VIEW, ALIAS e SYNONYM.
DROP TABLESPACE CURXDB01.CURXTS01A sua eliminação acarreta a eliminação de Table, Index, View,
Alias, Synonym e Relacionamentos vinculadas a qualquer objeto deste Tablespace, deleta fisicamente do Disco.
DROP TABLE SICUR.CURXTB01FUNCAOA eliminação vem acompanhada da eliminação de Index,
View, Alias, Synonym e Relacionamento.
DROP INDEX SICUR.CURXPX01Elimina o Índice, inclusive o INDEXSPACE.
DROP VIEW SICUR.CURXVW01Elimina logicamente do DB2 o apontamento dos dados para a
VIEW.
DROP SYNONYM/ALIAS CONCEITOElimina Sinônimo/Alias
DROP STOGROUP CURXSG01Só podemos eliminar este objeto se o mesmo não estiver mais sendo referenciado por um outro objeto.
SQL ESTÁTICO vs. SQL DINÂMICO
ESTÁTICO.......................................................................................................... Instrução SQL CompletaExec SQL Fixo
Delete from DeptEnd-Exec...................................................... Estratégia de acesso ..................................................... pré-estabelecida......................................................
DINÂMICO.....................................................Var . Instrução SQL fornecida durante a execução. .....................................................Exec SQL Estratégia de acesso Execute Immediate : Var pré-estabelecida. End-Exec.....................................................
Delete from Dept
Notas:
A diferença entre o SQL estático e o dinâmico está no instante em que o plano é montado.
No estático o plano é montado antes da primeira execução da instrução.No SQL dinâmico a instrução é parcial ou totalmente desconhecida e o DB2 não tem como determinar previamente a estratégia de acesso, validar autorização, etc.Uma instrução onde o desconhecido é apenas o valor que está sendo comparado com uma coluna na cláusulas WHERE é candidata a estático. Exemplo: WHERE SALARIO = :SAL AND NOME LIKE :STR
PROGRAMAÇÃO EM SQL
Para podermos trabalhar com o SQL dentro de programas em linguagens tradicionais, devemos codificá-lo usando delimitadores especiais. Esta técnica é conhecida como SQL embutido e assemelha-se ao COMMAND LEVEL do CICS.
O SQL trabalha com tabelas e linhas. É uma linguagem desenvolvida para manipular conjuntos.
Já as linguagens tradicionais trabalham com arquivos e registros e não conseguem reconhecer tabelas e linhas.
Para simularmos um arquivo com seus registros existe o CURSOR do DB2. Assim o programa pode acessar a manipular linhas de uma tabela como se estivesse trabalhando com um arquivo comum.
As colunas não são manipuladas diretamente pelas linguagens tradicionais. Para contornar isto são utilizadas as chamadas variáveis HOST que “Hospedam” o conteúdo.
Instrução SQL entre delimitadores. EXEC SQL DELETE FROM DEPT END-EXEC
Os compilador das linguagens tradicionais não entende instruções SQL.
As instruções SQL devem ser embutidas dentro de um programa separados por delimitadores para que possam ser tratados antes de serem submetidas ao compilador.
Cada linguagem tem seu delimitador próprio para isolar as instruções SQL.O delimitador que indica o início de uma instrução SQL é comum á todas as linguagens.
VARIÁVEL HOST
As variáveis HOST são utilizadas para permuta de valores entre a linguagem SQL e a linguagem de programação.
Uma das possíveis utilizações da variável HOST é a de fornecer um valor a uma instrução AQL, tornando-a assim uma instrução genérica.
A referencia á variável HOST deve ser precedida pelo caracter “:”. Seu uso é facultativo mas recomenda-se sua utilização para evitar possibilidade de confusão. O uso da variável HOST é opcional. Seu uso é obrigatório somente para as instruções SELECT.
A variável HOST deve ser compatível com a coluna quanto ao seu data type e ao seu tamanho.
As variáveis HOST não devem ser utilizadas para referenciar nomes de tabelas ou de colunas.
Exemplo de Variáveis HOST:
EXEC SQL INSERT INTO EMPR (MATR, SOBRENOME) VALUES ( :MATR, :SNOME )
END-EXEC
EXEC SQL UPDATE EMPRSET SALARIO = SALARIO * :PERCENTWHERE CARGO = :CARGO
END-EXEC
EXEC SQL SELECT MATR, SOBRENOMEINTO :MATR, :SNOMEFROM EMPR WHERE CARGO = :CARGO
END-EXEC
PROCESSAMENTO DE MÚLTIPLAS LINHAS
Se o resultado de um Select pode ter múltiplas linhas, o programa deve trabalhar com um CURSOR, pois não tem como prever o tamanho da tabela resultante.
Quando o programa executa um OPEN de um CURSOR, o DB2 executa o SELECT e a tabela resultante fica presa, esperando pelo processamento.
O programa deve prover variáveis HOST para cada coluna do CURSOR.O programa executa a instrução SQL “FETCH”, para movimentar o CURSOR para o próximo registro. As variáveis HOST conterão os valores do registro (linha) apontado.
SELECT OPEN
MATR
SOBRENOME
30 SILVA290 SANTOS300 ALMEIDA
FETCHMAT
RSOBRENOME
30 SILVA290 SANTOS300 ALMEIDA
FETCHMAT
RSOBRENOME
30 SILVA290 SANTOS300 ALMEIDA
SELECT COM FETCH
Definição de um CURSOR
EXEC SQL DECALRE CUR001 CURSOR FOR
SELECT MATR, SOBRENOMEFROM EMPRWHERE DEPT = :DEPT
END-EXEC
Abrindo um CURSOR previamente definido.
EXEC SQL OPEN CUR001 END-EXEC
FETCH das linhas resultantes
EXEC SQL FETCH CUR001 INTO :MATR, :SNOM
END-EXEC
Fechando o CURSOR quando terminar a leitura dos dados.
EXEC SQL CLOSE CUR001 END-EXEC
OPEN posiciona o CURSOR antes da primeira linha da tabela resultante.
FETCH avança o CURSOR uma linha para frente.
Não é possível fazer o FETCH voltar o CURSOR.
CLOSE no CURSOR não implica em COMMIT.
COMMIT / ROLLBACK
COMMIT Indica término bem sucedido de uma unidade de trabalho Alteração dos dados serão gravadas. Todas os PAGE LOCKS serão liberados CURSOR fechado, exceto para CURSOR WITH HOLD
ROLLBACK Unidade de trabalho corrente é abandonado Atualização dos dados desde o último COMMIT são desfeitas Todos os PAGE LOCKS são liberados CURSOR fechado
BIND
Preparação de Programa
Código FonteSQL
Biblioteca deInclude
PRECOMPILAÇÃO
Código FonteModificado
DBRMBiblioteca de Usuário
Compilação BIND
Módulo Objeto
Linkedição
Módulo de Carga
Language Interface
Descrição da tabela
Autorização
DBRM
Plano/Package
Execução
Catálogo DB2
Diretório
BIND
Notas:
A preparação de programa é o nome que damos ao processo que torna um programa fonte em um módulo de carga executável. A preparação de um programa é constituído das seguintes fases: Precompilação Compilação e Linkedição BIND O precompilador pode ser executado com o DB2 fora. Mas sua função é gerar a partir de fonte dois data set’s selados com um Timestamp. Os data set’s gerados são:
DBRM (Database Request Module) Contém todas as instruções SQL embutidas no fonte.
Fonte Modificada fonte é copiado para este data set com modificações. As instruções SQL são transformadas em comentários e os CALLs para o DB2 são inseridos logo a seguir. Eventualmente códigos de definição de variáveis HOST são copiados a partir de uma biblioteca de INCLUDE (DCLGEN – book das tabelas)
O precompilador faz a checagem da sintaxe das instruções SQL e da validade das variáveis HOST utilizadas. Um relatório de execução com eventuais mensagens de erro também é gerado.
O compilador gera o módulo objeto a partir do fonte modificado pelo precompilador. O Timestamp é copiado do fonte.
BIND
O linkage editor gera o módulo de carga á partir do módulo da linguagem de interface do DB2. O Timestamp original é copiado do módulo Objeto.
O processador de BIND gera o Plano ou Package á partir do DBRM. O Timestamp é copiado do DBRM.
Um Plano ou Package contém a estratégia de acesso aos dados DB2 com informações do tipo:
Índices utilizados Estratégia de Locking Estratégia de execução do SQL E outros
A fase de compilação/Linkedição e a fase de BIND são executadas separadamente e podem ser executadas em qualquer ordem.
BIND
O propósito de BIND é a geração do Plano ou de um Package. Á partir do DBRM e do catálogo são executados os seguintes procedimentos:
Checagem das consistências das instruções SQL com os Objetos manipulados. São checados nomes, data type das colunas, etc.
Checagem da autorização necessária para a execução das instruções SQL.
Determinação da estratégia de acesso aos Objetos da manipulação, pelo otimizador do DB2.
Plano/Package é armazenado no diretório e uma cópia do DBRM é carregado no Catálogo.
BIND ADDUtilizado para criar um novo Plano/Package
BIND REPLACEUtilizado quando a própria instrução SQL foi alterada e
queremos refazer o Plano/Package existente.
REBINDUtilizado quando o SQL permanece inalterado mas queremos refletir uma modificação ocorrida no ambiente tais como criação/eliminação de índices ou atualizações das estatística do Catálogo.
BIND
BIND ADD REBIND BIND REPLACE
Adiciona Refaz Plano Refaz Plano Plano Novo (Ambiente alterado) (SQL Alterado)
OTIMIZADOR
DBRM CATÁLOGO DIRETÓRIO
DBRM
PLANOPACKAGE
SEGURANÇA INTERNA DO DB2
A segurança interna do DB2 pode ser ativada ou não durante a geração.
Quando existe uma solicitação de atividade, o DB2 verifica três fatores:
Auth-id - Quem fez a solicitação?Privilégios - O que se deseja fazer com o recurso?Recurso - Qual é o Objeto de manipulação?
Estas informações ficam armazenadas no catálogo.Auth-id representa um usuário ou um grupo de usuário e é informado ao DB2 através do monitor de transações.
TABELAS SELECTVIEW UPDATE [Lista de colunas]
INSERT
PLANOS BINDPACKAGES EXECUTE
STOGROUP BUFFERPOOL USETABLESPACE
SYSADMDB2 RECOVER
TRACE, etc..
DBADM DATABASE CREATETS
LOAD, etc..
SEGURANÇA INTERNA DO DB2
Recursos DB2
Podemos proteger:
OBJETOS Tabela, View
PLANOS/PACKAGES Programas, QMF, SPUFI
COMANDOS DB2 -Bind, -Display
UTILITÁRIS DB2 Load, Recover
RECURSOS DO SISTEMA Buffer, Storage Group
ATRIBUIÇÃO DE AUTORIDADE
Instrução SQL
+ + +
+ + +
Exemplo: GRANT SELECT ON EMPR TO PUBLIC REVOKE SELECT FROM EMPR FROM PUBLIC
GRANTPrivilégioSobreRecurso
TO AUTH-ID
REVOKEPrivilégioSobreRecurso
FROM AUTH-ID
UTLITÁRIOS DO DB2
Divididos em 3 grandes grupos:
ONLINE1. LOAD Carga de arquivo sequencial para uma tabela.2. CHECK INDEX Checa a consistência entre o Índex e os Dados.3. CHECK DATA Checa a integridade entre a Chave Primária e a Chave estrangeira.4. REORG Reorganiza um Tablespace, Partição de um Tablespace ou Índex.5. RUNSTATS Atualiza informações do catálogo sobre os Tablespaces e Indexes.6. STOSPACE Atualiza informações sobre a utilização em Disco do Storage Group.7. COPY Copia um Tablespace para um dataset Sequencial8. MERGECOPY Utilizado para juntar um ImageCopy FULL com outro Incremental.9. RECOVER Recuperação de um Tablespace, Partição ou Recuperação de Índex.10. QUIESCE Estabelece um ponto de sincronismo de uma lista de tablespaces.11. MODIFY Manutenção no catálogo DB2.12. REPORT Listas informações de Recovery para Tablespaces.13. REPAIR Função de reparo físico em Disco.
OFFLINE
1. CHANGE LOG INVENTORY Atualiza log datasets no BSDS.2. PRINT LOG MAP Listas dados de Log BSDS.
3. UTLITÁRIOS DO DB2(Continuação)
SERVICE AIDS
1. DSN1COPY Trata user data por data set2. DSN1PRNT Listas e formatas data set pages3. DSN1COMP Estimativas de espaços a ser arquivados(DB2)4. DSN1LOGP Lê o conteúdo da Log e formata para display.5. DSN1CHKR Verifica a Integridade dos diretórios e catálogo.6. DSNTIAUL Unload de tabelas para uma arquivo sequencial.7. DSNTEP2 Para emissão de SQL dinâmico.8. DSNTIAD Para emissão de SQL dinâmico exceto SELECT.
SYSIBM.SYSUTILX
Uma Tabela de Diretório DB2.
JOB UTILITY CHECKPOINTUTILID NOME NOME INFORMAÇÃO....
Coluna é inserida no Início do Utility.Coluna é atualizada em Pontos de Checkpoints e Parada de Utility.Coluna eliminada quando do termino do Utility.
IdentificadorÚnico
LOAD
Faz carga de arquivo sequencial para um Tablespace
Pode se carregar múltiplas tabelas em um mesmo TablespaceConversão de dados serão feitas.
Construir Índices definidos
Índice único não permite chaves duplicadas
Checagem Integridade
Cada chave estrangeira deve conter uma chave primária correspondente.
Pode ser inibida esta checagem.
Notas:
Os dados podem ser carregado de 2 maneiras:
1. Através do INSERT (SQL).2. Através do Utilitário LOAD.
Só não serão carregadas as tabelas:
Se a conversão dos dados forem diferentes do formato do Tablespace
Se os Índices únicos detectar violações.Caso haja relacionamento não existir a chave primária.
LOAD
Definição da Tabela:
CREATE TABLE PROD.EMPRESA(NOME_EMPRESA CHAR(6) NOT NULL, PRIMEIRO_NOME VARCHA(12) NOT NULL, INTERM_NOME CHAR(1) NOT NULL, ULTIMO_NOME VARCHAR(15) NOT NULL, FONE CHAR(4) ,SALARIO DECIMAL(8,2) ,DEPTO CHAR(3) ,PRIMARY KEY(NOME_EMPRESA),FOREIGN KEY(DEPTO)
REFERNCES DEPTON DELETE SET NULL)
IN TESTEDB1.TESTTSP1;
Exemplo do Registo para o LOAD:....+.....1....+....2....+....3....+....4....+....5....+....6....+....7000010 KARINA L BEZERRA A00 3978 650101 66 18 F 330814 52750
LOAD – JCL
//LOADTEST EXEC DSNUPROC,....//DSNUPROC.SYSREC DD DSN=ARQUIVO.ENTRADA ,DISP=SHR//DSNUPROC.SYSUT1 DD DSN=ARQUIVO.SYSUT1,DISP=( MOD,DELETE,CATLG),...//DSNUPROC.SORTOUT DD DSN=ARQUIVO.SORTOUT,DISP=(MOD,DELETE,CATLG),...//DSNUPROC.SYSERR DD DSN=ARQUIVO.SYSERR,DISP=(MOD,DELETE,CATLG),...//DSNUPROC.SYSMAP DD DSN=ARQIVO.SYSMAP,DISP=(MOD,DELETE,CATLG),...//DSNUPROC.SYSIN DD * LOAD DATA INTO TABLE PROD.EMPRESA WHEN (32:34) = ‘A00’ (NOME_EMPRESA POSITION (1:6), PRIMEIRO_NOME POSITION(8:16) CHAR, INTERM_NOME POSITION(19), ULTIMO_NOME POSITION(21:30) CHAR, DEPTO POSITION(32:34), FONE POSITION(36:39) NULLIF PHONE = ‘ ‘, SALARIO POSITION(65:69) DECIMAL EXTERNAL)
LOAD
Load Logging
O default é LOG YES, a vantagem de não ser necessário fazer ImageCopy após a carga. Mas deve ser usar com critérios:
Carregar uma tabela em um Tablespace compartilhadoCarga e em tabelas pequenasCarga com RESUME YES
Quando o volume de carga é grande, deve ser usar LOG NO, pois isto evita uma trabalho do DB2 para registrar cada registro na LOG, e consequentemente a LOG poderá ficar mais tempo em DISCO para um possível RECOVERY.
LOG YESCada página será registrada na LOG.
LOG NO Carga mais rápida Requer ImageCopy Modificação por SQL não permitida enquanto não fizer o ImageCopy - SQL error code –904
LOAD RESUME NO REPLACE
LOAD RESUME YES
LOAD RESUME NO(Cond. Code = 8)
TABLESPACE
LOAD
LOAD RESUME NOUsada somente quando a tabela estiver vazia, caso haja
algum registro na tabela ocorrerá abend com Return-Code 8.
LOAD RESUME YESOpção para adicionar novas ocorrências na tabela.
LOAD RESUME NO REPLACEOpção para carregar novas ocorrências na tabela eliminando
todas as ocorrências existentes dentro da tabelas.
A Variação do LOAD permite trabalhar a nível de Partição.
LOAD INTO TABLE tabelax PART x RESUME NO Carrega ocorrências em uma partição vazia
LOAD INTO TABLE tabelax PART x RESUME YESAdiciona novas ocorrências à partição.
LOAD INTO TABLE tabelax PART x RESUME YES REPLACECarrega ocorrências em uma única partição, eliminando
as ocorrências existente na partição.
Violação de Índice Único
Quando do LOAD, se inserido duas ou mais ocorrências com a mesma chave o DB2 descartas todas as ocorrências deste LOAD.
CHAVE ENTRADA CARGA NA TABELA DESCARTESA AB BC CD DB B
LOAD
Integridade Referencial
ENFORCES CONSTRAINTS é o default. Verifica se existe uma chave primária para a chave estrangeira. Pode se inibido com ENFORCE NO
Se encontrado Violação, a ocorrência é eliminada.
FASES DO LOAD
Dados de Entrada(SYSREC)
RELOAD
SaídaTabela
Sysut1
Syserr
Sysmap
Sysut1SORT
Sortout
SortoutSysut1 BUILD
Sysut1Syserr
Indexes
Sysut1Syserr IDEXVAL
SaídaTabelas
FASES DO LOAD
Dados entrada(SYSREC)
Sortout ENFORCES SaídaTabelas
Sysut1
ParentTables
Sysut1
SortoutSyserr
SortoutSyserr
DISCARD Sysdisc
Sysmap
SortoutSyserr
REPORT RelatórioSumário
FASES DO LOAD
RELOADEsta fase faz a carga do sequencial para a Tabela, Armazena
as chaves dos índices e chave estrangeira não indexados (Se ENFORCE CONSTRAINTS) na SYSUT1.
O DD SYSDISC conterá as ocorrências descartadas
Caso ocorra algum este requer:SYSERR - Qualquer erro será armazenado.
Se SYSERR não informado, o RELOAD abend antes de qualquer carga de dados.
Se descartes não requerido. Reload termina no primeiro error.
Se SYSMAP é necessário (Violação de chave Única ou Violação RI), informações da posição da ocorrência no SYSREC.
SORTSorteia o registros da SYSUT1 pelo Chave do índices, e
Chaves estrangeiras seguem a chave indexadas.
O SORT pode ser inibido em uma das condições:Não existe mais que um chave por tabelaO dados carregados já estão em sequência.Todas as chaves devem ser do mesmo tipo.
BUILDConstrói os Índice do SORTOUT e adiciona informações de
erros na SYSERR, e informações na SYSUT1 caso não ocorra erros.
INDEXVALDas informações recolhidas dos SYSERR/SYSUT1 são
removidas as ocorrências da tabelas.
FASES DO LOAD
ENFORCEDe acordo com o SORTOUT, e o relacionamento definido para
a tabela, esta fase faz a checagem para a violação da RI. A Checagem envolves a tabela mãe onde se verifica a chave estrangeira carregada.
Todas as ocorrências sem um ”PAI” serão removidos da tabela, e incluída na SYSDISC.
REPORT Listas um relatório de sumário contendo os erros.
Recomendações:
Pré processamento para remover os erros. Sorteia os dados de entradas na sequência da clusterização. Criar todos os índices antes de carregar os dados. Use LOG NO para evitar excessivo I/O na LOG Carregar primeiros as PARENT TABLES (Tabelas mães). Preferir sempre ENFORCE CONSTRAINTS. Usar BLKSIZE otimizado para SYSUT1, SORTOUT e SORTWWKxx
CHECK DATA
Checa todas as chaves estrangeiras de uma lista de tablespaces com os valores de chaves primárias correspondentes.
Checa todas as ocorrências dependentes, ou somente as adicionadas pelo LOAD.
Opcionalmente copia ocorrências ‘Órfã’ para uma tabela de exceção, e elimina a ocorrência da tabela ou não.
Tirar do Status de CHECK PENDING Nenhuma violação foi detectado, ou Erros são eliminados com a opção DELETE YES.
//DSNUPROC. SYSIN DD * CHECK DATA
TABLESPACE PRD.EMPRESATABLESPACE PRD.DEPTO
SCOPE PENDING ou ALL
FOR EXCEPTION IN PRD.EMPRESA USE prd.EmpresaXIN PRD.DEPTO USE prd.DeptoX
DELETEYES ou NÃO
EXCEPTIN n
Se SCOPE ALL checa todas as chaves estrangeiras de um tablespaces.Se SCOPE PENDING checa apenas as chaves estrangeiras adicionada pelo LOAD.DELETE YES deleta todas as ocorrências “’ÓRFÔ de um TablespaceEXCEPTIONS especifica os limite de exceção, quando excede este limite o CHECK termina.
FASES DO CHECK DATA
Repetida para Dependent Table (Tabela “FILHA”)
Dependent table SYSUT1
SYSUT1 SORTOUT
Sortout/Índice chave estrangeiraSYSERR
Índice chave Primária
Dependent/Descendent Tables Tabela Exceção
SYSERR Relatório de Sumário
SCANTAB
SORT
SCANTAB
REPORTCK
FASES DO CHECK DATA
1. SCANTABEsta fase extrai as chaves estrangeiras necessárias e grava no SYSUT1
2. SORTSorteia a entradas contidas no SYSUT1 pelo nome da constraints e o valor da chave estrangeira.
3. CHECKDATEsta fase produz um erro para cada chave estrangeira não encontrada.
4. REPORTCKSe DELETE YES especificado, este fase deleta as ocorrências, e copia para a tabela de Exceção. Produz também um Relatório de sumarização para cada tabela.
5. UTILTERMEsta fase termina o processo.
CHECK INDEX
O CHECK INDEX testa a consistência entre o dados e o Índices. O Check procura a inconsistência e emite uma mensagem de erro, descrevendo qual o problema.
Exemplos de SYSIN:
//DSNUPROC.SYSIN DD * CHECK INDEX NAME(prd.indice1, prd.index2) CHECK INDEX(prd.indice1 PART 3)CHECK INDEX(ALL) TABLESPACE PRD.EMPRESA
FASES DO CHECK INDEX
UNLOAD
SORT
CHECKINDEX
Índices Sysut1
Sysut1 Sysut1
Sysut1
Tabelas
RelatórioSumário
REORG
A fase de UNLOAD dos registros de uma tabela.Descarrega as ocorrências na sequência do Índice de clusterização
Fase de RELOADcarrega os dados de volta a tabela, respeitando os FREE PAGE, PCTFREE, etc.
FASE de SORTSorteia as chaves dos índices contidas no SYSUT1, gravando no SORTOUT.
Fase de BUILDReconstroí os Índices com a saída do SORT.
FASES DO REORG
Saída dos Dados
(SYSREC)Entrada dos Dados (SYSREC)
UNLOADTablespace
RELOAD
Tablespace
Sysut1
Sysut1 Sortout
Sortout Indexes
SORT
BUILD
REORG
UNLOAD CONTINUE
RESTART
UNLOAD PAUSE STOP
Edit/Field Field/Edit
UNLOAD ONLY TERMINATE LOAD REPLACEEdit/Field Validation/Field/Edit
Existem 3 opção de UNLOAD para o utilitário REORG.
1. Unload ContinueTodas as fases serão concluídas em uma execução2. Unload PauseO Utilitário é parado após o Unload do Tablespace ou Índices.O Utilitário deve ser reestartdo para completarO Edit and Field são invocados duranto o Unload e Reload Fases.3. Unload OnlyApós a fase de Unload é terminado o UtilidA saída pode ser usada como entrada do utilitário LOAD.
REORG
Exemplos de SYSIN://DSNUPROC.SYSIND DD * REORG TABLESPACE PRD.EMPRESA UNLOAD CONTINUE LOG NOREORG TABLESPACE PRD.EMPRESA UNLOAD PAUSE LOG NOREORG TABLESPACE PRD.EMPRESA UNLOAD ONLYREORG TABLESPACE PRD.EMPRESA PART 3REORG INDEX PRD.ÍNDICE1
COPY
Cria um Backup do Tablespace
COPY TABLESPACE CURXDB01.CURXTS01 FULL YES SHRLEVEL REFERENCE
Permite AtualizaçãoLeitura compartilhada
Se não quiser compartilhar acesso a base, emita o comando no DB2:
-START DB(CURXDB01) SPACE(CURXTS01) ACCESS(UT)
Assim somente utilitários podem agir sobre os Objetos.
Controle de Cópia Incremental
Altera Indicadores de Páginas
Altera bit no HEADER
O Copy Full e Incremental desliga o bit. Fazer sempre Image Copy FULL após REORG LOG NO ou LOAD
LOG NO. Se mais que 15% das páginas foram alteradas, opte por COPY
FULL. Especifique DISP=(MOD,CATLG) para permitir restart do
utilitário.
SPACEMAPPAGE
DATAPAGE
MERGECOPY
Image Copys Incremental Merge Image Copy Incremental
SYSIN: MERGECOPY TABLESPACE PRD.EMPRESA NEWCOPY NO
Incremental
Full Full Image Copy
SYSIN:MERGECOPY TABLESPACE PRD.EMPRESA NEWCOPY YES
NEWCOPY YES insere um apontamentos no catalogo DB2 como FULL Image Copy.
NEWCOPY NO atualiza os registros com Image Copy Incremental.
MERGECOPY
MERGECOPY
RECOVERY
TABLESPACERECOVER TABLESPACE CURXDB01.EMPRESA
TABLESPACE CURXDB01.DEPTO
DATASETRECOVER TABLESPACE CURXDB01.EMPRESA DSNUM 3
TABLESPACE CURXDB01.DEPTO DSNUM 2
Funções do Utilitário RECOVER
1. Inicialização2. Alocação Dinâmica se não existir apontamento no statements
DD3. Define os datasets do Tablespaces.4. Image Copy Merge e Restaura5. Aplica a log.
Índices e Tablespace DB2 podem ser copiados por este utilitário não DB2.
DB2 RECOVER aceita somente as seguintes entradas:Image Copy + LogImage Copy somenteLogs DB2 somente
RECOVERY
PONTOS DE RECOVERY
Permite restaurar ate:
Um especifico Full ou Incremental Image CopyRECOVER .... TOCOPY I.D15.CUR.DBP0TS01.G0001V00
Um especifico LOG RBA.RECOVER .... TORBA X’005E00123456’
22:00IMAGECOPYEMPRESA FULL YES
22:20
08:45
09:00
RUN PROGRAMXYZ
Descobre que programaAtualizou dados errados
Restaurar a partir do Último Image Copy
QUIESCE
Verifica restrições para os tablespaces especificados e cria um ponto de consistência para eles. Para cada tabespace, o Quiesce cria uma entrada na SYSCOPY com RBA válido para a recuperação.
QUIESCE TABLESPACE CURXDB01.CURXTS01TABLESPACE CURXDB01.CURXTS02
REPORT
Listas dados sobre Tablespaces. O relatório pode Ter 2 tipos de saídas: TABLESPACESET – Tablespace e tabelas que participam de
Relacionamento de Integridade Relacional (RI). RECOVERY - Informações de recuperação a partir do
catálogo, diretório e BSDS.
REPORT TABLESPACESET TABLESPACE CURXDB01.CURXTS01REPORT RECOVERY TABLESPACE CURXDB01.CURXTS01.
INDEX RECOVERY
Fase UNLOAD Fase SORT Fase BUILD
Index1
Arquivo de Trabalho Index2
Exemplos de SYSIN:
RECOVER INDEX(PRD.INDICE1, PRD.INDICE2)RECOVER INDEX (ALL) TABLESPACE PRDXDB01.EMPRESARECOVER INDEX(PRD.INDICE1 PART 2)
UNLOAD
Tablespace
Sysut1
SORT
Sysut1
BUILD
RECOVERY PENDING
Tablespace ou Index deve ser recuperado1. RECP significa que a partição de um Index ou Tablespace esta pendente2. PSRCP significa que a entrada de um Index não particionado esta com RECOVERY Pendente.
Acesso proibido exceto para:1. RECOVER2. LOAD REPLACE3. REPAIR4. MERGE COPY
-DISPLAY DB revela os status RECP/PSRCP
RECP/PSRCP é resetado por:1. RECOVER tablespace ou Index2. LOAD REPLACE3. REPAIR com SET NORCVRPEND4. START DB .... ACCESS(FORCE)
RECOVERY PENDING segue as seguintes condições:
Condição UTILITÁRIO FASE OBJETOS colocado em RECPTerm Util Recover TS -------- TABLESPACETerm Util Recover
IndexBUILD Índice ainda não construídos
Term Util Reorg/LoadRELOADSORTBUILD
Tablespace, Partições e ÍndicesTodos os ÍndicesÍndice ainda não construídos
Point in TimeTS Recovery
RecoverTablespace
-------- Todos os Índices
MODIFY
Elimina Image Copy desnecessários e log ranges do catálogo DB2, As entradas são da SYSCOPY e SYSLGRNG.Possível acesso concorrente durante o MODIFY com exceção do SYSCOPY e SYLGRNG.
Exemplo:
MODIFY RECOVERY TABLESPACE(CURXDB01.CURXTS01) DELETE DATE(19981101).
MODIFY RECOVERY TABLESPACE(CURXDB01.CURXTS01) DELETE AGE(90)
DELETE DATE é eliminados as ocorrências da data informada para trásDELETE AGE todos ocorrências com a quantidades de dias maior que a informadas serão eliminadas.
DIAGNOSE Ajuda no diagnóstico de problemas de utilitários.
Normalmente sua utilização é a pedido do SUPORTE IBM
STOSPACEColhe informação de espaço alocado para cada Storage Groups, Tablespaces e índices relacionados. A informação é registrada no catálogo DB2.
RUNSTATS
Varre um Tablespace ou índice para atualização no catálogo de utilização de espaço e eficiência do índices. Esses dados são utilizados pelo otimizador do DB2.O Runstats atualizadas as tabelas:
Estatística usada pelo OTIMIZADOR1. SYSTABLES2. SYSTABLESPACE3. SYSINDEXES4. SYSCOLUMNS5. SYSCOLDIST
Outras para informações do DBA1. SYSTABLEPART2. SYSINDEXPART3. SYSCOLDISTSTAS4. SYSCOLSTATS5. SYSINDEXSTATS6. SYSTABSTATS
REPAIRRepara dado do usuário, índices, catálogo, Space Map, etc.REPAIR deve ser usado com extrema cautela.
DSNTIAUL
Permite gerar um arquivo sequencial dos dados da tabelas, e um outro com as informações de onde inicia e termina os registros, alem das informações necessária para o LOAD.
Exemplo do JCL:
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSUDUMP DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSPUNCH DD DSN=P915537.FONTES.CAPXTS14,UNIT=SYSDA, // DISP=(,CATLG,DELETE),SPACE=(TRK,(50,10),RLSE) //SYSREC00 DD DSN=P915537.DADOS.CAPXTS14,UNIT=SYSDA, // DISP=(,CATLG,DELETE),SPACE=(CYL,(80,05),RLSE) //SYSTSIN DD * DSN SYSTEM(DBP0) RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB41) PARMS('SQL') - LIB('DB2P1.V4R1M0.RUNLIB.LOAD') //SYSIN DD * SELECT * FROM SICAP.CAPXTB14ENTRADA ;/*
COMANDOS DB2
Principais comandos DB2 -DIS DB(ABE*) SPACE(*) LIMIT(*)
Verifica os status de todos os Tablespace e Indexspace do DATABASE que comece com ABE.
-DIS DB(*) SPACE(*) LIMIT(*) RESTRICTVerifica todos os Tablespaces e Indexspace com alguma Restrição
-START DB2 / -STOP DB2Inicializa e encerra o DB2 (Comandos na console OS/390)
-DIS UTIL(*)Mostra todos os UTILID com seus respectivos STATUS
-TERM UTIL(IMAGCCP)Termina o UTILID IMAGCCP
-START DB(ABEXDB01) SPACE(*) ACCESS(RW) Libera todos Indexspace e Tablespaces para acesso de READ/WRITE
-START DB(ABEXDB01) SPACE(ABEXTS12,ABEXPX12) ACCESS(UT) Starta os Tablespace ABEXTS12 e o Indexspace ABEXPX12 somente para acesso de utilitários DB2.
-DIS DB(ABEXDB01) SPACE(*) LIMIT(*) LOCKS Mostra todos os Tablespaces e Indexspaces do Database ABEXDB01 e as respectivas alocação.
-DIS THREAD(*) Mostra todas as threads conectado ao DB2.
