Estude fácil! Tem muito documento disponível na Docsity
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
Prepare-se para as provas
Estude fácil! Tem muito documento disponível na Docsity
Prepare-se para as provas com trabalhos de outros alunos como você, aqui na Docsity
Encontra documentos específicos para os exames da tua universidade
Prepare-se com as videoaulas e exercícios resolvidos criados a partir da grade da sua Universidade
Responda perguntas de provas passadas e avalie sua preparação.
Ganhe pontos para baixar
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
Curso Básico de Desenvolvedor Mainframe - Módulo DB2
Tipologia: Notas de estudo
1 / 32
Esta página não é visível na pré-visualização
Não perca as partes importantes!
Curso: Desenvolvedor MainframeCurso: Desenvolvedor Mainframe OS/390/Lógica deOS/390/Lógica de Programação/CoboProgramação/Cobol/CICS/DB2l/CICS/DB M ó d u l o : D B 2M ó d u l o : D B 2
Curso: Desenvolvedor MainframeCurso: Desenvolvedor Mainframe OS/390/Lógica deOS/390/Lógica de Programação/CoboProgramação/Cobol/CICS/DB2l/CICS/DB M ó d u l o : D B 2M ó d u l o : D B 2
Curso: Desenvolvedor MainframeCurso: Desenvolvedor Mainframe OS/390/Lógica deOS/390/Lógica de Programação/CoboProgramação/Cobol/CICS/DB2l/CICS/DB M ó d u l o : D B 2M ó d u l o : D B 2
OS/390/Lógica de Programação/Cobol/CICS/DB M ó d u l o : D B 2 1 – INTRODUÇÃO. O DB2 é um banco de dados relacional usado exclusivamente na plataforma alta (IBM). Os primeiros bancos de dados adotados no Mainframe eram bancos de dados estruturados, onde o lay-out de seus registros era fixo, e todos os programas precisavam declará-los explicitamente. Qualquer alteração nestes lay-out requeriam a recodificação de todos os programas envolvidos com o banco de dados. Os bancos de dados relacionais se caracterizam por ter lay-out flexível, independente dos programas, alem do potencial criado pela linguagem SQL de formular pesquisas as mais diversas a qualquer momento. 1.1 - O MODELO RELACIONAL. Dos conceitos do modelo relacional retiramos as seguintes definições:
OS/390/Lógica de Programação/Cobol/CICS/DB M ó d u l o : D B 2 1.2.4.5 - REGRAS DE INTEGRIDADE O DB2 possui algumas regras de validação de seus dados que são definidos no momento da definição de suas tabelas. Estas validações independem dos programas que atualizam o banco de dados, e são executadas automaticamente quando se incluem alteram ou excluem dados das tabelas. As regras de integridade são: UNICIDADE. Esta regra define que uma ou varias colunas (KEYS), da tabela não podem repetir valores. É a regra usada para definir UNIQUE KEYS. INTEGRIDADE DE VALORES (CHECK CONSTRAINTS) Esta regra define valores permitidos para colunas de uma tabela. INTEGRIDADE REFERENCIAL A INTEGRIDADE REFERENCIAL define a validade de uma FOREIGN KEY:
OS/390/Lógica de Programação/Cobol/CICS/DB M ó d u l o : D B 2 É o formato para armazenar caracteres. São eles: CHAR Define colunas de caracteres com tamanho fixo com até 255 caracteres. Sintaxe: Exemplo: Nome-da-coluna CHAR(tamanho) CODCLI CHAR(6) VARCHAR Define colunas de caracteres com tamanho variável. Podem conter até 32704 caracteres, mas tamanhos acima de 255 tem restrições em alguns comandos SQL. Sintaxe: Nome-da-coluna VARCHAR(tamanho-maximo) Exemplo: NOMECLI VARCHAR(50) CLOB Este tipo (Character Large Object) é usado para armazenar grande volume de texto. Tem tamanho variável até 2147647 (2GB) bytes. Sintaxe Nome-da-coluna CLOB(tamanho-maximo) BLOB Este tipo (Binary Large Object) é usado para armazenar imagens, ou dados em formato binário em geral. Seu tamanho é variável até 2147483647 (2GB) bytes. Nome-da-coluna BLOB(tamanho-maximo) 1.2.5.2 FORMATOS NUMERICOS INTEGER Define colunas para conter números inteiros usando 31 bits mais sinal. Uma coluna INTEGER pode conter valores na faixa de –2147483648 até +2147483647. Sintaxe para definir uma coluna INTEGER: Exemplo: Nome-da-coluna INTEGER QUANTIDADE INTEGER SMALLINT Define colunas para conter números inteiros usando 15 bits mais sinal. Uma coluna SMALLINT pode conter valores na faixa de –32768 até +32767. Sintaxe: Exemplo: Nome-da-coluna SMALLINT IDADE SMALLINT
OS/390/Lógica de Programação/Cobol/CICS/DB M ó d u l o : D B 2 Exemplo: HORA-PARTIDA TIME TIMESTAMP O tipo TIMESTAMP define uma coluna com a data e hora do dia com precisão até microssegundos. O formato interno é um formato binário próprio do DB2 mas a coluna conterá IA, MES, ANO, HORA, MINUTO, SEGUNDOS e MICROSSEGUNDOS. O formato lido por um programa é função do comando utilizado para ler a coluna. Sintaxe para definir uma coluna TIMESTAMP: Nome-da-coluna TIMESTAMP Exemplo: DATA-CRIACAO TIMESTAMP 1.2.5.4 FORMATO ROWID O formato ROWID define uma coluna onde o DB2 gerará uma identificação única da linha que está sendo inserida na tabela (UNIQUE KEY). Pode ser usada em tabelas que não possuem UNIQUE KEY. Sintaxe para sua definição: Nome-da-coluna ROWID Exemplo: ID-MENSAGEM ROWID 1.2.6 CONSTANTES. No DB2 as constantes alfanuméricas devem ser colocadas entre aspas simples Ex.: ´JOSE´ As constantes numéricas são escritas sem as aspas. As constantes com decimais devem usar o ponto para separar as decimais. Ex.:
As constantes de DATA e HORA devem ser escritas no formato do exemplo abaixo: 14/02/ 16: 1.2.7 SPECIAL REGISTERS.
OS/390/Lógica de Programação/Cobol/CICS/DB M ó d u l o : D B 2 SPECIAL REGISTERS são variáveis internas do DB2 com informações do ambiente que os programas podem acessar e usar. A lista é extensa, e apresentamos abaixo uma relação das mais usadas: CURRENT DATE Data do sistema operacional no formato DATE CURRENT TIME Hora do sistema operacional no formato DATE CURRENT TIMESTAMP TIMESTAMP do sistema operacional no formato TIMESTAMP USER Usuário do sistema no formato CHAR(8)
OS/390/Lógica de Programação/Cobol/CICS/DB M ó d u l o : D B 2 O comando SQL para criar uma nova TABLE é o CREATE TABLE. Podem ser criadas tabelas com até 750 colunas e 16 terabytes de tamanho total. O comando define o nome da tabela seguido de uma serie de especificações de colunas como no exemplo: CREATE TABLE PRODUTO (CODIGO CHAR(6) UNIQUE, NOME VARCHAR(20) , PRECO DECIMAL(8,2) NOT NULL, DEPTOFABR CHAR(3), DATAFABR DATE DEFAULT) Neste exemplo o argumento UNIQUE que aparece em CODIGO indica que o valor desta coluna não pode se repetir nas linhas da tabela. O argumento NOT NULL da coluna PRECO indica que nas inclusões de novas linhas na tabela (inclusão de novos produtos no cadastro), o PRECO deve sempre ser informado. O argumento DEFAULT da coluna DATAFABR indica que nas inclusões de produtos, se a coluna DATAFABR não for informada, o DB2 preencherá esta coluna com o valor “default” para datas, que é a data corrente do sistema operacional. O argumento DEFAULT para colunas com tipo alfanumérico preenche o campo com espaços, e quando o tipo for numérico a coluna será preenchida com zeros. Outro exemplo: CREATE TABLE DEPTO (CODDEPTO CHAR(3) PRIMARY KEY NOT NULL, NOMEDEPTO CHAR(10) NOT NULL, ENDERDEPTO VARCHAR(60)) Neste exemplo a tabela DEPTO esta sendo criada com a chave primaria CODDEPTO. O comando CREATE pode ser usado também para criar índices para colunas definidas como UNIQUE ou PRIMARY KEY. Os índices aumentam a performance das pesquisas que usam esta coluna. Exemplo: CREATE UNIQUE INDEX IDXDEPTO ON DEPTO(CODDEPTO) 2.2.2 - ALTERANDO TABELAS. O comando SQL para alterar a estrutura de uma tabela é o ALTER TABLE. Este comando pode ser usado tanto para alterar a especificação das colunas já existentes como para transformar as colunas em PRIMARY KEY ou criar INDICES para colunas para aumentar a performance nas pesquisas. Exemplos: ALTER TABLE PRODUTO (PRIMARY KEY(CODIGO), FOREIGN KEY(DEPTOFABR) REFERENCES DEPTO ON DELETE SET NULL) Este exemplo transforma a coluna CODIGO (que já era UNIQUE) em PRIMARY KEY. Também transforma a coluna DEPTOFABR em chave estrangeira para a tabela DEPTO. Depois desta
OS/390/Lógica de Programação/Cobol/CICS/DB M ó d u l o : D B 2 alteração, uma linha de PRODUTO somente poderá ser incluída se houver na tabela DEPTO uma linha descrevendo o departamento onde este produto será fabricado. Também foi definida uma restrição para a exclusão da linha do departamento em DEPTO, especificando que se a linha DEPTO for excluída o DB2 deverá inserir NULL na coluna DEPTOFABR dos produtos relacionados. 2.2.3 - EXCLUINDO TABELAS. O comando para excluir uma tabela do DB2 é o DROP TABLE. Exemplo: DROP TABLE PRODUTO 2.2.4 - ACESSANDO E ATUALIZANDO TABELAS. Os comandos SQL que acessam e atualizam as tabelas serão os comandos que utilizaremos nos programas aplicativos. São 4 os comandos SQL usados neste grupo: SELECT INSERT UPDATE DELETE 2.2.4.1 - SELECT. O objetivo do comando SELECT é pesquisar dados dentro do DB2. Ele especifica o formato de uma tabela para conter o resultado da pesquisa e outros parâmetros para definir o processo de pesquisa. O formato básico do SELECT é SELECT colunas-ou-valores FROM tabela, tabela WHERE condição Em colunas-ou-valores é especificado o conjunto de colunas ou valores que deverão compor a tabela resultado da pesquisa. O caracter asterisco (*) pode ser usado em colunas-ou-valores para indicar que todas as colunas da tabela devem ser selecionadas. No argumento FROM indicam-se os nomes das tabelas de onde serão retiradas as colunas para formar a tabela resultado. No argumento WHERE (que é opcional) especificam-se os critérios de pesquisa. Exemplo: SELECT CODFUNC NOMEFUNC SALARIO FROM EMPREGADOS WHERE SALARIO > 1000 Neste exemplo a tabela resultado conterá uma relação com o código, nome e salário dos funcionários da tabela EMPREGADOS, para os empregados que ganham mais de 1000,00. SELECT * FROM EMPREGADOS Este exemplo lista todas as colunas de todos os empregados da tabela.
OS/390/Lógica de Programação/Cobol/CICS/DB M ó d u l o : D B 2 2.2.4.1.1 - ORDENAÇÃO DA TABELA RESULTADO. O DB2 pode ordenar as linhas na tabela resultado com a seguinte sintaxe: SELECT DISTINCT colunas-ou-valores FROM tabela, tabela WHERE condição ORDER BY colunas ASC Com esta sintaxe a tabela resultado será ordenada pelas colunas especificadas no argumento ORDER BY. O argumento DISTINCT é opcional, e quando especificado a tabela resultado não conterá linhas repetidas. O argumento ASC da clausula ORDER BY é opcional (pode ser DESC), e indica se a ordenação é crescente ou decrescente. 2.2.4.1.2 - AGRUPANDO LINHAS NO RESULTADO. A tabela resultado pode ter suas linhas agrupadas e resumidas segundo o valor de uma coluna especifica com a sintaxe: Com esta sintaxe, cada linha da tabela resultado será o resumo das linhas pesquisadas para cada valor das colunas especificadas em GROUP BY. O argumento HAVING é opcional, e se especificado conterá as condições de seleção de cada grupo gerado pelo GROUP BY. Na prática, o argumento HAVING é o WHERE do GROUP BY. SELECT colunas-ou-valores FROM tabela, tabela WHERE condição GROUP BY colunas HAVING condição Exemplos: SELECT DISTINCT NOME FROM FUNCIONARIOS ORDER BY NOME Lista os nomes dos funcionários em ordem alfabética. SELECT CARGO, SUM(SALARIO) FROM FUNCIONARIOS GROUP BY CARGO HAVING SUM(SALARIO) > 10000 Lista os cargos e respectiva soma de salários da tabela de funcionários somente para os cargos cuja soma de salários é superior a N$10000,
OS/390/Lógica de Programação/Cobol/CICS/DB M ó d u l o : D B 2 2.2.4.1.3 - BUILT-IN FUNCTIONS. O DB2 possui um conjunto de funções pre-programadas que podem ser usadas pelos comandos SQL. Os argumentos passados para as funções podem ser colunas de uma tabela ou expressões com constantes e variáveis não pertencentes ao DB2. Quando todos os argumentos não pertencem ao DB2, não existe uma tabela para preencher o argumento FROM do SELECT. Neste caso deve-se usar a pseudo-tabela SYSIBM.SYSDUMMY no argumento FROM. Exemplo de um SELECT para calcular a raiz quadrada de 25: SELECT SQRT(25) FROM SYSIBM.SYSDUMMY A lista de funções é extensa e as principais estão na tabela seguinte: AVG(DISTINCT colunas-ou-expressões) Obtém a media aritmética dos valores das colunas ou expressões. Se DISTINCT for usado, os valores repetidos serão desprezados. COUNT(DISTINCT colunas-ou- expressões) Obtém a quantidade de linhas selecionadas. Se DISTINCT for usado, as linhas repetidas não serão contadas. MAX(colunas-ou-expressões) Obtém o maior valor contido nas colunas ou expressões selecionadas. MIN(colunas-ou-expressões) Obtém o menor valor contido nas colunas ou expressões selecionadas. SUM(DISTINCT colunas-ou-expressões) Obtém a soma dos valores das colunas ou expressões selecionadas. Se DISTINCT for usado, os valores repetidos não serão somados. ABS(expressão) ABSVAL(expressão) Obtém o valor absoluto de uma expressão. CHAR(expressão) Converte o resultado de expressão para STRING. O resultado de expressão pode estar nos formatos: INTEGER ou SMALLINT DECIMAL REAL ou DOUBLE CHAR(date,formato) Para converter valores do formato DATETIME para formato CHAR coloca-se no argumento FORMATO o formato da data que deve ser um destes: ISO yyyy-mm-dd USA mm/dd/yyyy EUR dd.mm.yyyy JIS yyyy-mm-dd LOCAL conforme instalação. VALUE(expressão,expressão) COALESCE(expressão,expressão) Retorna o valor da primeira expressão cujo valor não for NULL. CONCAT(expressão,expressão) Retorna um STRING formado pela união das duas expressões DATE(expressão-ou-coluna) Devolve um valor DATE contido na expressão ou coluna DAY(expressão) Devolve o dia do mês se a expressão for do tipo DATE, ou a quantidade de dias se a expressão representar uma diferença de datas. DAYOFMONTH(expressão) Devolve o dia do mês (entre 1 e 31) retirado da expressão, que deve ser do tipo DATE DAYOFWEEK(expressão) DAYOFWEEK_ISO(expressão) Devolve o dia da semana contido na expressão que deve ser do tipo DATE. O dia da semana devolvido é um numero entre 1 e 7: Para DAYOFWEEK : 1=Domingo
OS/390/Lógica de Programação/Cobol/CICS/DB M ó d u l o : D B 2 Expressão deve ter formato TIME ou TIMESTAMP SMALLINT(expressão) Retorna o valor da expressão no formato SMALLINT SPACE(expressão) Produz um STRING no formato VARCHAR contendo espaços com o comprimento dado pelo valor de EXPRESSÃO. SQRT(expressão) Devolve a raiz quadrada do valor de EXPRESSÃO. O resultado tem formato DOUBLE. SUBSTR(string1,inicio,tamanho) Devolve um STRING extraído de STRING1 a partir de INICIO. O argumento TAMANHO é opcional, e indica o comprimento do resultado. Se TAMANHO for omitido, a extração se inicia em INICIO até o fim de STRING1. TIME(expressão) Converte EXPRESSÃO para o formato TIME. TIMESTAMP(expressão) Converte EXPRESSÃO para o formato TIMESTAMP. UCASE(string) UPPER(string) Converte STRING para maiúsculas. VARCHAR(expressão) Converte o resultado de EXPRESSÃO para VARCHAR. WEEK(expressão) Devolve o valor da SEMANA DO ANO (entre 1 e 54) contido na expressão. Expressão deve ter formato DATE ou TIMESTAMP YEAR(expressão) Devolve o valor do ANO contido na expressão. Expressão deve ter formato DATE ou TIMESTAMP Exercícios. Descreva a tabela resultado dos SELECT abaixo SELECT SUM(SALARIO), AVG(COMISSAO) FROM FUNCIONARIOS WHERE DEPTO = ‘A10’ SELECT MAX(SALARIO), MIN(SALARIO) FROM FUNCIONARIOS SELECT CARGO, SUM(SALARIO) FROM FUNCIONARIOS GROUP BY CARGO HAVING SUM(SALARIO) > 10000 SELECT SALARIO, DECIMAL(SALARIO,6,1), INTEGER(SALARIO), HEX(SALARIO), DIGITS(SALARIO) FROM FUNCIONARIOS SELECT NOME, LENGTH(NOME) FROM FUNCIONARIOS SELECT SUBSTR(NOME,1,3) FROM FUNCIONARIOS SELECT COUNT(*) FROM FUNCIONARIOS SELECT NOME, VALUE(COMISSAO,0) FROM FUNCIONARIOS WHERE COMISSAO IS NULL
OS/390/Lógica de Programação/Cobol/CICS/DB M ó d u l o : D B 2 SELECT NOME, CHAR(DT_NASC, EUR) FROM FUNCIONARIOS WHERE CODFUNC = 10 2.2.4.1.4 - JOIN O JOIN é o processo do SELECT de pesquisar dados de mais de uma tabela. A tabela resultado conterá valores provenientes de colunas das varias tabelas. Para que seja possível esta união das linhas de varias tabelas para compor uma linha da tabela resultado, é necessário que as linhas das varias tabelas tenham em comum uma coluna que tenha valores correspondentes. Para exemplificar, vamos usar 2 tabelas:FUNCIONARIOS e DEPARTAMENTO com as seguintes colunas: Tabela FUNCIONARIOS CODFUN NOMEFUN DEPTO SALARIO Tabela DEPARTAMENTO CODDEPTO NOMEDEPTO Queremos fazer uma relação de funcionários com o nome do departamento em que trabalham. A tabela resultado deste SELECT deverá conter CODFUN, NOMEFUN tirados da tabela FUNCIONARIOS e conter NOMEDEPTO tirado da tabela DEPARTAMENTO. Este SELECT deverá fazer um JOIN destas duas tabelas usando como colunas de ligação DEPTO e CODDEPTO. O SELECT será o seguinte: SELECT CODFUN, NOMEFUN, NOMEDEPTO FROM FUNCIONARIOS, DEPARTAMENTO WHERE FUNCIONARIOS.DEPTO = DEPARTAMENTO.CODDEPTO. Podemos colocar as seguintes regras para construir um JOIN: 1- As tabelas envolvidas precisam ter uma coluna com valores iguais. 2- A clausula WHERE do SELECT deve especificar esta correspondência entre colunas. 3- Não é necessário que as colunas do JOIN sejam ligadas como FOREIGN KEY. Esta declaração só tem finalidade para garantir a INTEGRIDADE RELACIONAL. 4- Note que na clausula WHERE as colunas estão prefixadas pelo nome da tabela. O esquema de prefixação (nome da tabela, nome da coluna separados por ponto) é usado aqui para definir o JOIN. O esquema de prefixação pode ser usado no entanto em qualquer local dos comandos SQL para resolver ambigüidades de nomes de colunas entre varias tabelas.
