Apostila Do Curso - DB2

Curso: Desenvolvedor Mainframe

Curso: Desenvolvedor Mainframe

OS/390 / Lógica de Programação / Cobol / CICS / DB2

OS/390 / Lógica de Programação / Cobol / CICS / DB2

Módulo: DB2

Módulo: DB2

1 - Introdução 1 - Introdução 1.1 – O Modelo Relacional 1.1 – O Modelo Relacional 1.2 - ELEMENTOS DO DB2. 1.2 - ELEMENTOS DO DB2. 1.2. 1.2.1 – TA1 – TA BELAS.BELAS. 1.2.

1.2.2 – COLU2 – COLU NAS.NAS. 1.2.

1.2.3 - LINH3 - LINH AA 1.2.4 - KEY

1.2.4 - KEY 1.2.

1.2.4.1 - UNI4.1 - UNI QUE KEYQUE KEY 1.2.

1.2.4.2 - 4.2 - PRIPRI MARY MARY KEYKEY 1.2.4.

1.2.4.3 - PAR3 - PAR ENT KEYENT KEY 1.2.4.

1.2.4.4 – FOREI4 – FOREI GN KEYGN KEY 1.2.4.

1.2.4.5 - REGRAS DE 5 - REGRAS DE ININ TEGRIDATEGRIDA DEDE 1.2.

1.2.5 - Tipos de Dado5 - Tipos de Dado ss 1.2.

1.2.5.1 FORMATO STRI5.1 FORMATO STRI NG.NG.

1.2.5.2 FORMATOS NUMERICOS 1.2.5.2 FORMATOS NUMERICOS 1.2.5.3 FORMATOS DATETIME 1.2.5.3 FORMATOS DATETIME 1.2.

1.2.5.4 FORMATO ROW5.4 FORMATO ROW IDID 1.2.

1.2.6 6 CONSTANTESCONSTANTES 1.2.

1.2.7 SPECIA7 SPECIA L REGISTERS.L REGISTERS. 2 Linguagem

2 Linguagem SQLSQL

2.1 Ambientes para uso do SQL 2.1 Ambientes para uso do SQL 2.2 COMANDOS SQL

2.2 COMANDOS SQL 2.2.

2.2.1 - CRIAN1 - CRIAN DO TABELASDO TABELAS 2.2.2

2.2.2 - ALTERAN- ALTERAN DO TABELAS.DO TABELAS. 2.2.3

2.2.3 - EXC- EXCLUILUI NDO TABELANDO TABELA S.S. 2.2.4

2.2.4 - ACES- ACESSANDO E ATUALSANDO E ATUAL IZANDO TABIZANDO TAB ELASELAS 2.2.4.1 - SELECT.

2.2.4.1 - SELECT. 2.2.4.

2.2.4.1.1 - O1.1 - ORDENARDENA ÇÃO DA TABELA ÇÃO DA TABELA RESULTADORESULTADO 2.2.

2.2.4.1.4.1.2 - 2 - AGRUPAGRUP ANDO LIANDO LI NHANHA S S NO RESULTADO.NO RESULTADO. 2.2.

2.2.4.1.4.1.3 - Built-in F3 - Built-in F unctiounctio nsns 2.2.

2.2.4.1.4.1.4 - J4 - J OINOIN 2.2.

2.2.4.1.4.1.5 - Sub 5 - Sub QueriesQueries 2.2. 2.2.4.2 - IN4.2 - IN SERTSERT 2.2. 2.2.4.3 - UPDAT4.3 - UPDAT EE 2.2.4.4 - 2.2.4.4 - DELETDELET EE 3 - Programando em Cobol 3 - Programando em Cobol 3.1 - Introdução 3.1 - Introdução 3.

3.1.1 – Formato dos 1.1 – Formato dos comandoscomandos 3.

3.1.2 – Host 1.2 – Host VariablesVariables 3.1.

3.1.2.1 - 2.1 - II ndicatondicato rr 3.

3.1.3 - Teste de erros nos com1.3 - Teste de erros nos com andosandos 3.1.3.1 – SQLCODE

3.1.3.1 – SQLCODE 3.1.

3.1.3.2 - WHEN3.2 - WHEN EVEREVER

3.2 - Acessando tabelas com o Cobol 3.2 - Acessando tabelas com o Cobol

3.2.

3.2.1 - ACESS1 - ACESSANDO 1 ANDO 1 LILI NHA NHA DA TABELA.DA TABELA. 3.2.

1 - Introdução 1 - Introdução 1.1 – O Modelo Relacional 1.1 – O Modelo Relacional 1.2 - ELEMENTOS DO DB2. 1.2 - ELEMENTOS DO DB2. 1.2. 1.2.1 – TA1 – TA BELAS.BELAS. 1.2.

1.2.2 – COLU2 – COLU NAS.NAS. 1.2.

1.2.3 - LINH3 - LINH AA 1.2.4 - KEY

1.2.4 - KEY 1.2.

1.2.4.1 - UNI4.1 - UNI QUE KEYQUE KEY 1.2.

1.2.4.2 - 4.2 - PRIPRI MARY MARY KEYKEY 1.2.4.

1.2.4.3 - PAR3 - PAR ENT KEYENT KEY 1.2.4.

1.2.4.4 – FOREI4 – FOREI GN KEYGN KEY 1.2.4.

1.2.4.5 - REGRAS DE 5 - REGRAS DE ININ TEGRIDATEGRIDA DEDE 1.2.

1.2.5 - Tipos de Dado5 - Tipos de Dado ss 1.2.

1.2.5.1 FORMATO STRI5.1 FORMATO STRI NG.NG.

1.2.5.2 FORMATOS NUMERICOS 1.2.5.2 FORMATOS NUMERICOS 1.2.5.3 FORMATOS DATETIME 1.2.5.3 FORMATOS DATETIME 1.2.

1.2.5.4 FORMATO ROW5.4 FORMATO ROW IDID 1.2.

1.2.6 6 CONSTANTESCONSTANTES 1.2.

1.2.7 SPECIA7 SPECIA L REGISTERS.L REGISTERS. 2 Linguagem

2 Linguagem SQLSQL

2.1 Ambientes para uso do SQL 2.1 Ambientes para uso do SQL 2.2 COMANDOS SQL

2.2 COMANDOS SQL 2.2.

2.2.1 - CRIAN1 - CRIAN DO TABELASDO TABELAS 2.2.2

2.2.2 - ALTERAN- ALTERAN DO TABELAS.DO TABELAS. 2.2.3

2.2.3 - EXC- EXCLUILUI NDO TABELANDO TABELA S.S. 2.2.4

2.2.4 - ACES- ACESSANDO E ATUALSANDO E ATUAL IZANDO TABIZANDO TAB ELASELAS 2.2.4.1 - SELECT.

2.2.4.1 - SELECT. 2.2.4.

2.2.4.1.1 - O1.1 - ORDENARDENA ÇÃO DA TABELA ÇÃO DA TABELA RESULTADORESULTADO 2.2.

2.2.4.1.4.1.2 - 2 - AGRUPAGRUP ANDO LIANDO LI NHANHA S S NO RESULTADO.NO RESULTADO. 2.2.

2.2.4.1.4.1.3 - Built-in F3 - Built-in F unctiounctio nsns 2.2.

2.2.4.1.4.1.4 - J4 - J OINOIN 2.2.

2.2.4.1.4.1.5 - Sub 5 - Sub QueriesQueries 2.2. 2.2.4.2 - IN4.2 - IN SERTSERT 2.2. 2.2.4.3 - UPDAT4.3 - UPDAT EE 2.2.4.4 - 2.2.4.4 - DELETDELET EE 3 - Programando em Cobol 3 - Programando em Cobol 3.1 - Introdução 3.1 - Introdução 3.

3.1.1 – Formato dos 1.1 – Formato dos comandoscomandos 3.

3.1.2 – Host 1.2 – Host VariablesVariables 3.1.

3.1.2.1 - 2.1 - II ndicatondicato rr 3.

3.1.3 - Teste de erros nos com1.3 - Teste de erros nos com andosandos 3.1.3.1 – SQLCODE

3.1.3.1 – SQLCODE 3.1.

3.1.3.2 - WHEN3.2 - WHEN EVEREVER

3.2 - Acessando tabelas com o Cobol 3.2 - Acessando tabelas com o Cobol

3.2.

3.2.1 - ACESS1 - ACESSANDO 1 ANDO 1 LILI NHA NHA DA TABELA.DA TABELA. 3.2.

3.2.2 - Acessando mais 2 - Acessando mais de uma de uma linha da tabela.linha da tabela. 3.2.2.1 - C

3.2.

3.2.2.2 - S2.2 - Scrollabcrollab le cursole curso rr 3.3 - Incluindo linhas na tabela

3.3 - Incluindo linhas na tabela 3.4 - Atualizando linhas da tabela 3.4 - Atualizando linhas da tabela

3.

3.4.1 - Atualização 4.1 - Atualização posicionadaposicionada 3.5 - Excluindo linhas da tabela 3.5 - Excluindo linhas da tabela

3.5.

3.5.1 - 1 - Exclusão poExclusão po sicionsicion adaada

4 – CATALOGO DB2 4 – CATALOGO DB2

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: - Banco de Dados .

É formado por um conjunto de TABELAS. O lay-out das tabelas é mantido pelo proprio Banco de Dados.

- Cada TABELA é formada por um conjunto de COLUNAS. O nome e formato das COLUNAS é mantido pelo Banco de Dados.

- Relacionamentos.

O relacionamento entre as TABELAS é representado pelos VALORES contidos nestas TABELAS.

- Pesquisa.

Para se efetuar uma pesquisa nas TABELAS especifica-se uma TABELA RESULTADO (Result Table). O DB2 pesquisa o conteúdo de suas TABELAS e monta a Result Table conforme a especificação da pesquisa.

1.2 - ELEMENTOS DO DB2. 1.2.1 – TABELAS.

– São estruturas lógicas mantidas pelo DB2 para representar uma entidade. Ex.: TABELA FUNCIONARIOS.

1.2.2 – COLUNAS.

– é cada uma das informações que compõem uma tabela. Ex.: TABELA  FUNCIONARIOS COLUNAS CODIGO NOME ENDEREÇO DATA DE ADMISSÃO SALARIO 1.2.3 - LINHA

– É cada conjunto de valores das colunas formam uma instancia da entidade. Ex.:

TABELA FUNCIONARIO

COLUNAS CODIGO NOME ENDEREÇO DATA ADM SALARIO LINHA 1 000010 JOÃO R. APA 25/02/01 1500,00 LINHA 2 000005 PEDRO R TUIM 10/10/02 1200,00

1.2.4 - KEY

(Chave) – Conjunto de colunas selecionadas para identificar as linhas da tabela. As chaves podem identificar linhas dentro da própria tabela ou linhas de outra tabela (Foreign Keys). O DB2 aceita os seguintes tipos de KEY:

1.2.4.1 - UNIQUE KEY

Chave que identifica de maneira inequívoca uma linha da tabela. Não existe mais

de uma linha da tabela com a mesma UNIQUE KEY. A UNIQUE KEY tem as seguintes propriedades:

Nota 1 Cliente XX Nota 2

- A UNIQUE KEY não pode ter NULL (não pode estar vazia). - É necessário criar um UNIQUE INDEX para a UNIQUE KEY. - Uma tabela pode ter mais de um UNIQUE KEY.

1.2.4.2 - PRIMARY KEY

É a UNIQUE KEY usada na definição da tabela. Suas propriedades são: - A tabela só pode ter uma PRIMARY KEY.

- A PRIMARY KEY é opcional, isto é, as tabelas não precisam ter PRIMARY KEY.

1.2.4.3 - PARENT KEY

É uma PRIMARY KEY ou UNIQUE KEY de uma TABELA MESTRE (tabela mestre é a tabela da posição (1) da relação 1:N do MER). O valor da PARENT KEY será replicado nas FOREIGN KEY(chave estrangeira) das tabelas relacionadas (lado (N) da relação 1:N)

1.2.4.4 – FOREIGN KEY

É uma KEY em uma TABELA RELACIONADA (lado N na relação 1:N) que identifica uma linha da TABELA MESTRE. As propriedades da FOREIGN KEY são:

- Uma tabela pode ter zero, uma ou varias FOREIGN KEY.

- Dependendo das regras de INTEGRIDADE REFERENCIAL (SET NULL), o valor da FOREIGN KEY pode ser NULL.

Exemplo de tabelas relacionadas.

Clientes Relação 1:N Notas Fiscais

Conteúdo das tabelas

Tabela Cliente Notas Fiscais

CODCLI NUMNOTA ENDERECO CODCLIN CEP CODPROD Foreign Key VALOR Nota 3

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:

- Se uma FOREIGN KEY não contem NULL, seu valor deve ser o da PARENT KEY da TABELA MESTRE.

- A INTEGRIDADE REFERENCIAL deve ser definida na criação da tabela.

- O DB2 testa a INTEGRIDADE REFERENCIAL nos comandos de inclusão, alteração e exclusão dos programas conforme abaixo:

INTEGRIDADE NA INCLUSÃO.

Se uma linha tiver uma coluna FOREIGN KEY com valor NOT NULL, deve haver uma linha na PARENT TABLE com este valor na PARENT KEY.

INTEGRIDADE NA ALTERAÇÃO.

Se houver alteração em uma FOREIGN KEY, deve haver na PARENT TABLE uma PARENT KEY com o novo valor da FOREIGN KEY.

INTEGRIDADE NA EXCLUSÃO.

A exclusão de linhas com FOREIGN KEY não tem restrição. Se uma linha tiver uma coluna com PARENT KEY, uma das 3 regras de exclusão abaixo deve ser definida na criação da tabela:

- RESTRICT – a exclusão não será efetuada se houver uma TABELA DEPENDENTE com uma FOREIGN KEY com este valor.

- CASCADE –O DB2 fará a exclusão automática de todas as linhas das TABELAS DEPENDENTES com este valor na FOREIGN KEY. Se estas exclusões tiverem sucesso, a linha da PARENT TABLE será excluída.

- SET NULL – As FOREIGN KEY nas TABELAS DEPENDENTES serão alteradas para NULL.

1.2.5 - TIPOS DE DADOS.

Os formatos dos dados nas colunas de uma tabela DB2 se dividem em 4 grandes grupos: STRING

NUMERIC DATETIME ROWID

É 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

DECIMAL

O tipo DECIMAL define colunas com números reais (contendo parte inteira e decimais). O DB2 aceita colunas DECIMAL com até 31 algarismos. O formato para definir este tipo é:

Nome-da-coluna

DECIMAL(total-de-algarismos,decimais)

Exemplo: Para definir a coluna PRECO para 6 algarismos inteiros e 2 decimais (valores até 9999,99):

PRECO DECIMAL(8,2)

REAL

O tipo REAL define colunas com números no formato PONTO FLUTUANTE. O DB2 usa 32 bits para este formato, e consegue controlar valores na faixa de –7.2E+75 até 7.2E75. Sintaxe:

Nome-da-coluna

REAL

DOUBLE

O tipo DOUBLE define colunas com números no formato PONTO FLUTUANTE. O DB2 usa 64 bits para este formato, e consegue controlar valores na faixa de –7.2E+75 até 7.2E75 com precisão maior que o tipo REAL. Sintaxe:

Nome-da-coluna

DOUBLE

1.2.5.3 FORMATOS DATETIME

Os formatos DATETIME definem formatos para armazenar datas e hora. Os valores no formato DATETIME podem fazer parte de expressões aritmeticas, isto é, pode-se somar duas variaveis com formato DATETIME.

São 3 os tipos de formato DATETIME: DATE

O tipo DATE define uma coluna com datas. O formato interno é um formato binário próprio do DB2, mas o conteúdo da coluna conterá DIA, MES e ANO de uma data. O formato lido por um programa (Ex.:DDMMAAAA) é função do comando utilizado para ler a coluna.

Sintaxe para definir uma coluna DATE:

Exemplo:

Nome-da-coluna

DATE

DATA-NASCIMENTO DATE

TIME

O tipo TIME define uma coluna com a hora do dia. O formato interno é um formato binário próprio do DB2, mas o conteúdo da coluna conterá HORA, MINUTO e SEGUNDOS. O formato lido por um programa (Ex.:HHMMSS) é função do comando utilizado para ler a coluna.

Sintaxe para definir uma coluna TIME:

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.:

14.55

As constantes de DATA e HORA devem ser escritas no formato do exemplo abaixo: 14/02/2005

16:35

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)

2 - LINGUAGEM SQL.

O SQL é a linguagem usada para acessar quase todos os bancos de dados relacionais atuais. No caso do DB2, os comandos SQL podem ser incluídos nos programas escritos em Cobol, Assembler, C, C++, PL/I, PROLOG, JAVA e outras.

Os comandos SQL podem ser incluídos nos programas em um dos modos abaixo:

STATIC SQL – Neste modo os comandos SQL são codificados dentro do programa fonte. Os comandos são pré-compilados e portanto são fixos no programa.

DYNAMIC SQL – Neste modo os comandos são informados ao programa em tempo de execução. O comando é passado em forma de texto para o DB2, para ser resolvido e executado.

ODBC – Neste caso os comandos são passados pelo programa para um utilitário chamado DB2 OPEN DATABASE CONNECTIVITY(ODBC). Este utilitário é o encarregado de interpretar o comando e executá-lo junto ao DB2. A ODBC só pode ser usada em programas C.

JDBC – É a ODBC para programas em JAVA. A linguagem JAVA tem também o componente SQLJ que é voltado para tratamento de SQL ESTATICOS.

2.1 - AMBIENTES PARA USO DO SQL.

Os comandos SQL podem ser submetidos para o DB2 pelos seguintes produtos: QMF (QUERY MANAGEMENT FACILITY)

Este é um produto da IBM para submeter os comandos SQL para o DB2 e visualizar os resultados.

TSO.

O TSO tem uma opção dentro do menu DB2 chamado SPUFI. O SPUFI tem um menu onde o usuário especifica um dataset (pode ser um particionado) contendo o texto do comando SQL a ser executado, e um dataset para receber o resultado do comando. Usando o SPUFI o usuário pode facilmente executar quase todos os comandos SQL.

PROGRAMAS.

Os comandos SQL podem ser codificados dentro de programas escritos em varias linguagens, incluindo o Cobol.

2.2 - COMANDOS SQL.

Os comandos SQL se dividem em 2 grandes grupos: DDL – DATA DEFINITION LANGUAGE.

Neste primeiro grupo estão os comandos para criar e alterar a estrutura das tabelas do DB2. Este grupo de comandos é usado geralmente pelo DBA ou pessoal de suporte.

DML – DATA MANIPULATION LANGUAGE

Neste segundo grupo estão os comandos para pesquisar e manipular os valores existentes nas tabelas do DB2. Este grupo de comandos compõe o material usado em programação dos aplicativos usando DB2.

2.2.1 - CRIANDO TABELAS.

Os comandos SQL para criar e alterar tabelas não são usados em programação de aplicativos. Informamos abaixo sua sintaxe somente para completar os conhecimentos sobre a linguagem SQL.

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

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 *

Os critérios de pesquisa do argumento WHERE envolvem comparações entre valores de colunas, variáveis, expressões aritméticas ou constantes empregando os operadores da tabela:

Operador Descrição = != <>

> !> >= < !< <=

Igual Não igual Diferente Maior Não maior Maior ou igual Menor Não menor Menor ou igual BETWEEN O valor deve estar entre os dois limites

Ex.: IDADE BETWEEN 1 AND 9

IN O valor deve ser um dos fornecidos na lista Ex.: IDADE IN (1, 3, 5, 7, 9)

LIKE O valor deve ser semelhante ao indicado. No literal indicado pode-se usar % para representar um conjunto de caracteres, ou _ para representar a posição de um caracter.

Ex.: NOME LIKE ‘ALBERTO’

NOME LIKE ‘AL%’ Nomes iniciando com AL NOME LIKE ‘%O’ Nomes Terminando com O NOME LIKE ‘%L%’ Nomes que contenham L NOME LIKE ‘___E%’ Nome com E na posição 4.

O operador NOT pode ser usado junto com quarquer dos operadores acima. Exemplo:

IDADE NOT IN (1,3,5,7) Exercícios.

Descreva o resultado dos comandos: SELECT CODIGO NOME FROM FUNCIONARIOS

SELECT * FROM FUNCIONARIOS SELECT CODIGO NOME

FROM FUNCIONARIOS WHERE CARGO = ‘VENDAS’ SELECT NOME SALARIO FROM FUNCIONARIOS WHERE CARGO = ‘VENDAS’

AND SALARIO > 1000 SELECT NOME SALARIO FROM FUNCIONARIOS WHERE SALARIO BETWEEN

500 AND 2000

SELECT CODIGO NOME FROM FUNCIONARIOS WHERE CARGO IN

(‘VENDAS’,’COBRANCA’) SELECT NOME

FROM FUNCIONARIOS WHERE NOME LIKE

‘MARIA%’

SELECT NOME SALARIO FROM FUNCIONARIOS WHERE SALARIO * 12 >

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,00

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.SYSDUMMY1 no argumento FROM. Exemplo de um SELECT para calcular a raiz quadrada de 25:

SELECT SQRT(25)

FROM SYSIBM.SYSDUMMY1

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 forusado, 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 forNULL. 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_ISO : 1 = Segunda feira

DAYOFYEAR(expressão) Devolve o dia do ano (numero entre 1 e 366) contido na expressão que deve ser do tipo DATE.

DAYS(expressão) Devolve um numero inteiro representando o numero de dias transcorridos entre o valor DATE da expressão e o dia 01/01/0001.

DECIMAL(expressão,n1,n2) DEC(expressão,n1,n2)

Transforma o valor de expressão em valor no formato

DECIMAL, contendo um total de n1 algarismos e n2 decimais. DIGITS(expressão) Retorna o valor da expressão com os algarismos no formato de um STRING de caracteres. A expressão deve estar no formato INTEGER, SMALLINT ou DECIMAL.

DOUBLE(expressão)

FLOAT(expressão) Retorna o valor da expressão ou coluna no formato DOUBLE. HEX(expressão) Retorna um STRING de dígitos hexadecimais com o valor da

expressão.

HOUR(expressão) Retorna o valor da HORA contido na expressão. Expressão deve ter formato TIME ou TIMESTAMP.

INSERT(string1,inicio,tamanho,string2) Insere STRING2 em STRING1 a partir da posição INICIO apagando TAMANHO bytes de STRING1.

INTEGER(expressão)

INT(expressão) Retorna o valor da expressão no formato INTEGER LCASE(string)

LOWER(string) Converte os caracteres de STRING para minúsculos.

LEFT(string1,tamanho) Retorna um STRING com o comprimento TAMANHO retirado do inicio de STRING1

LENGTH(expressão) Retorna o numero de caracteres do valor da expressão (ou coluna)

LOCATE(string1,string2,inicio) Pesquisa a posição de STRING1 dentro de STRING2. INICIO é opcional e especifica a posição do caracter em STRING2 onde a pesquisa inicia.

LTRIM(string1) Remove SPACES do inicio de STRING1

MICROSECOND(expressão) Devolve o valor dos MICROSSEGUNDOS contidos na expressão. Expressão precisa ter formato TIME ou TIMESTAMP.

MINUTE(expressão) Devolve o valor dos MINUTOS contidos na expressão. Expressão deve ter formato TIME ou TIMESTAMP

MOD(expressão1,expressão2) Esta função devolve o resto da divisão de EXPRESSÃO1 por EXPRESSÃO2

MONTH(expressão) Devolve o valor do MES contido na expressão. Expressão deve ter formato DATE ou TIMESTAMP

POWER(expressão1,expressão2) Calcula o valor de EXPRESSÃO1 elevada a potencia indicada em EXPRESSÃO2.

REPEAT(string1,quantidade) Devolve um STRING formado pela repetição de STRING1 vezes QUANTIDADE.

REPLACE(string1,string2,string3) Substitui todas as ocorrências de STRING2 por STRING3 dentro de STRING1

RIGHT(string,tamanho) Devolve os últimos TAMANHO caracteres de STRING RTRIM(string) Remove os espaços do fim de STRING.

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

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.

2.2.4.1.5 - SUB-QUERIES.

O processo de SUB-QUERIES acontece quando os elementos de comparação da clausula WHERE de um SELECT são resultado de um segundo SELECT. Neste caso usamos um encadeamento de SELECTS, o que configura o uso de SUB-QUERIES.

Exemplo: - Queremos uma lista dos funcionários do departamento P1 cujo salário é menor que a media dos salários do departamento P2. Temos aqui o SELECT principal que produz a lista de funcionários do departamento P1, e o SELECT auxiliar que calcula a media dos salários do departamento P2. O SELECT completo com a SUB-QUERY fica:

SELECT NOME SALARIO FROM FUNCIONARIOS

WHERE CODDEPTO = `P1` AND SALARIO < (

SELECT AVG(SALARIO) FROM FUNCIONARIOS WHERE CODDEPTO = `P2`)

No exemplo acima, a QUERY produziu somente uma linha. Nos casos em que a SUB-QUERY produz mais de uma linha, existem operandos no SELECT para decidir o campo de ação da condição do WHERE. Estes operandos são os do quadro:

ALL A condição do WHERE deve ser satisfeita para TODAS as linhas da SUB-QUERY.

IN ANY SOME

A condição do WHERE deve ser satisfeita pelo menos por UMA linha da SUB-QUERY

EXISTS O WHERE é verdadeiro se a SUB-QUERY tiver pelo menos UMA linha. Exemplos:. Indique o resultado destas QUERIES:

SELECT NOME SALARIO FROM FUNCIONARIOS

WHERE CODDEPTO = `P1` AND SALARIO > ALL (

SELECT SALARIO FROM FUNCIONARIOS WHERE CODDEPTO = `P2`) SELECT NOME SALARIO

FROM FUNCIONARIOS

WHERE CODDEPTO = `P1` AND SALARIO > ANY (

SELECT SALARIO FROM FUNCIONARIOS WHERE CODDEPTO = `P2`) SELECT NOME SALARIO

FROM FUNCIONARIOS WHERE CODDEPTO = ‘P1’ AND EXISTS( SELECT CODPROJ FROM FUNCIONARIOS WHERE CODEPTO = `P1`)

2.2.4.2 - INSERT

O comando INSERT é usado para incluir novas linhas em uma tabela. Sua sintaxe :

Este comando SQL define o nome da tabela onde a linha vai ser incluída, em seguida dentro de parênteses, uma serie de colunas para as quais vão ser informados os valores, e apos o argumento VALUES, os valores de cada coluna da serie acima.

INSERT INTO nome-tabela

(coluna, coluna ...)

VALUES(valor, valor ....)

Quando se inclui uma nova linha em uma tabela, não é necessário fornecer valores para todas as colunas da linha. Somente as colunas definidas como NOT NULL devem receber obrigatoriamente valores. As colunas que não recebem valores na inclusão da linha permanecem como colunas inexistentes na linha (valor = NULL) até que uma alteração crie esta coluna.

A serie de colunas informada no comando INSERT pode ser informada em qualquer ordem, isto é, as colunas no INSERT não precisam estar na ordem de sua criação na tabela.

Nos casos em que o INSERT preenche valores para todas as colunas da tabela, a seqüência de colunas pode ser omitida, e o comando se simplifica como:

INSERT INTO tabela

VALUES (valor, valor ....)

Neste caso terá de ser informado os valores de todas as colunas na seqüência em que as colunas foram definidas na tabela.

Exemplo de comando INSERT:

INSERT INTO EMPREGADOS

(CODEMP, NOMEEMP, SALARIO)

VALUES(‘P4550’,’JOSE DA SILVA’,1325.50)

2.2.4.3 - UPDATE

O comando UPDATE é usado para alterar valores em uma ou mais linhas de uma tabela. O comando enumera as colunas e seus novos valores.

Há duas sintaxes para este comando, porem as duas são equivalentes. Primeiro formato:

UPDATE nome-da-tabela

SET coluna=valor, coluna=valor, ...

WHERE condição

Exemplo:

UPDATE EMPREGADOS

SET SALARIO=SALARIO * 1.10, BONUS=NULL, COMISSAO=10.00 WHERE CODDEPTO = `P10`

UPDATE nome-da-tabela

SET (coluna, coluna, ...)

=(valor, valor, ...)

WHERE condição

Exemplo:

UPDATE EMPREGADOS

SET(SALARIO, BONUS, COMISSAO) = (SALARIO * 1.10, NULL, 10.00) WHERE CODDEPTO = `P10`

2.2.4.4 - DELETE

O comando DELETE exclui linhas inteiras de uma tabela selecionadas pela clausula WHERE. Se a clausula WHERE não for especificada em um comando DELETE, todas as linhas da tabela serão apagadas.

Sintaxe:

DELETE FROM nome-da-tabela

WHERE condição

Exemplo:

DELETE FROM EMPREGADOS WHERE CODEMP = `525252` 3 - PROGRAMANDO EM COBOL 3.1 - INTRODUÇÃO

3.1.1 - FORMATO DOS COMANDOS.

Para codificar um comando SQL em Cobol é suficiente colocar o comando como foi exposto nos paragrafos anteriores entre as palavras EXEC SQL e END-EXEC como no modelo:

EXEC SQL

Comando

END-EXEC

3.1.2 – HOST VARIABLES.

Em um programa Cobol os comandos SQL precisam trocar ou comparar valores entre as colunas do banco de dados e as variaveis do programa. Por exemplo, em um SELECT o DB2 precisará mover dados de suas colunas para as variaveis do Cobol.

Quando esta troca de informações é realizada, é necessário haver uma compatibilidade completa entre o formato do dado nas colunas do DB2 e na variavel do Cobol, pois ao contrario do Cobol que ajusta o formato dos dados nas trocas, o DB2 nas faz nenhuma conversão de formato nestas operações.

As variaveis Cobol usadas dentro dos comandos SQL recebem o nome de HOST VA RIABLES. Qualquer variavel definida no Cobol pode ser usada como HOST VARIABLE, desde que seu formato seja completamente compativel (tamanho, PICTURE, decimais etc) com a coluna da tabela com a qual troca dados.

Para resolver os problemas de compatibilidade, para cada tabela usada no programa define-se na WORKING-STORAGE uma area de acesso (equivalente a FD do Cobol), contendo todas as colunas da tabela com o formato conveniente. O DB2 possui um utilitário para produzir estas areas de comunicação, chamado DCLGEN. O DCLGEN pode ser acionado pelo TSO.

A area produzida pelo DCLGEN é declarada no programa Cobol com o seguinte comando

Mostramos abaixo um exemplo de declaração da tabela DBADB2.PRODUTO produzida pelo DCLGEN:

EXEC SQL

INCLUDE nome-da-area

END-EXEC.

EXEC SQL DECLARE DBADB2.PRODUTO TABLE ( CODIGO CHAR(6) NOT NULL,

NOME VARCHAR(20) NOT NULL, QUANTIDADE INTEGER,

PRUNIT DECIMAL(5, 2) ) END-EXEC.

****************************************************************** * COBOL DECLARATION FOR TABLE DBADB2.PRODUTO * ******************************************************************

01 DCLPRODUTO.

10 CODIGO PIC X(6). 10 NOME.

49 NOME-LEN PIC S9(4) USAGE COMP. 49 NOME-TEXT PIC X(20).

10 QUANTIDADE PIC S9(9) USAGE COMP. 10 PRUNIT PIC S9(3)V9(2) USAGE COMP-3.

****************************************************************** * INDICATOR VARIABLE STRUCTURE *

***************************************************************** 01 IPRODUTO.

10 INDSTRUC PIC S9(4) USAGE COMP OCCURS 4 TIMES.

****************************************************************** * THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION *

* IS 4 *

******************************************************************

Todos os comandos SQL devem portanto usar HOST VARIABLES contidos dentro destas areas produzidas pelo DCLGEN. No exemplo acima estas variaveis estão na area DCLPRODUTO.

Quando usadas nos comando SQL as HOST VARIABLES devem sempre estar precedidas pelo caracter dois pontos(:). Se o compilador de comandos SQL encontra o (:) antes de uma palavra, ela é interpretada como HOST VARIABLE, caso contrario é interpretada como nome de coluna do DB2.

3.1.2.1 - INDICATORS.

INDICATORS são variaveis do programa Cobol usadas nos comandos SQL para: - Informar o comprimento real dos valores contidos em colunas VARCHAR. - Informar se uma coluna contem NULL.

- Informar erros de conversão entre o valor das colunas e as HOST VARIABLES.

Os INDICATORS devem ser variaveis com formato S9(4) COMP, e quando necessario deve haver um INDICATOR para cada coluna da tabela.

Na area de declaração da tabela, o DCLGEN ja preve um conjunto de INDICATORS. No exemplo da tabela DBADB2.PRODUTO mostrada no paragrafo anterior, a variavel NOME-LEN é o INDICATOR da variavel NOME, que corresponde a uma coluna VARCHAR. Para as outras colunas que não são VARCHAR, existe uma tabela de INDICATORS (a tabela INDSTRUCT), com 1 INDICATOR para cada coluna:

INDICATOR COLUNA INDSTRUCT(1) CODIGO INDSTRUCT(2) NOME

INDSTRUCT(3) QUANTIDADE INDSTRUCT(4) PRUNIT

3.1.3 - TESTE DE ERRO NOS COMANDOS. 3.1.3.1 - SQLCODE

O DB2 utiliza uma variavel de nome SQLCODE para retornar o STATUS de todos os comandos SQL. A variavel SQLCODE esta contida na area SQLCA que deve ser declarada na WORKING-STORAGE dos programas com a sintaxe:

EXEC SQL

INCLUDE SQLCA

END-EXEC.

Os valores a serem testados no SQLCODE são

SQLCODE = 0 Execução sem erro.

SQLCODE < 0 Comando não foi executado. O codigo do erro é o valor do SQLCODE.

SQLCODE > 0 O comando foi executado, com um WARNING dado pelo valor do SQLCODE.

Os codigos de erro dados pelo SQLCODE podem ser encontrados no manual de erros da IBM (DB2 MESSAGES AND CODES).

É necessário testar o código de retorno para todos os comandos SQL executáveis (Os comandos DECLARE não são executáveis).

Alguns valores importantes do SQLCODE:

SQLCODE = 100 Linha pesquisada não existe

SQLCODE = -803 Linha com esta PRIMARY KEY já existe

3.1.3.2 - WHENEVER

A clausula WHENEVER do DB2 pode ser usada para substituir os testes do SQLCODE, e tem um funcionamente identico ao comando HANDLE CONDITION do CICS, isto é, ela define uma ação no programa para onde a execução continuará quando ocorrer o erro apontado no WHENEVER. O WHENEVER no entanto somente intercepta 3 condições de erro:

SQLWARNING Intercepta WARNINGS nos comandos SQLERROR Intercepta qualquer erro nos comandos

SQL NOT FOUND Itercepta a condição ‘valores não entrados’ na tabela. Tambem so podem ser programadas 2 ações para o processar os erros interceptados: GO TO paragrafo A execução se desvia para o paragrafo. CONTINUE A execução continua na proxima sentença.

Como resultado das definições acima, a sintaxe do WHENEVER pode ser uma das seguintes:

EXEC SQL

WHENEVER condição

GO TO paragrafo

END-EXEC

EXEC SQL

WHENEVER condição

CONTINUE

END-EXEC

WHENEVER NOT FOUND GO TO NÃO-EXISTE END-EXEC

3.2 - ACESSANDO TABELAS COM O COBOL.

Para se ler dados de uma tabela em um programa Cobol o comando básico usado é o SELECT visto nos itens anteriores deste manual. O SELECT precisa somente ser ampliado para definir as HOST VARIABLES que devem receber os dados.

Existem dois processos de leitura no DB2: o primeiro aplica-se quando o SELECT devolve somente uma linha da tabela, e o segundo processo quando a tabela resultado do SELECT contem mais de uma linha.

3.2.1 - ACESSANDO 1 LINHA DA TABELA.

Quando a tabela resultado do SELECT contem somente uma linha, o parâmetro INTO acrescentado ao SELECT é suficiente para definir as HOST VARIABLES de leitura. A sintaxe deste SELECT é:

A variavel do parametro INTO deve ser uma HOST VARIABLE (portanto precedida por dois pontos). Alem disso, se a coluna pesquisa for do tipo VARCHAR ou a sua definição permitir valores NULL, é necessario acrescentar um INDICATOR na HOST VARIABLE respectiva. Este INDICATOR receberá um dos seguintes valores apos a execução do SELECT:

EXEC SQL

SELECT coluna, coluna, ...

INTO variavel, variavel, ....

FROM tabela

WHERE condição

END-EXEC

INDICATOR = -1 Coluna contem NULL INDICATOR = -2 Erro na conversão de valores INDICATOR = 0 Coluna contem valores normais

INDICATOR > 0 Comprimento original dos dados que foram truncados no momento da carga na HOST VARIABLE ou comprimento de um VARCHAR

Para se codificar o INDICATOR, basta coloca-lo apos a HOST VARIABLE, precedido ou não pela palavra INDICATOR.

Sintaxes:

:variavel :indicator, :variavel ...

Ou

:variavel INDICATOR :indicator, :variavel...

Exemplo:

MOVE `000001` TO CODIGO. EXEC SQL

INTO :NOME INDICATOR :INDNOME, :SALARIO WHERE CODEMP = :CODIGO

END-EXEC.

Este exemplo lê o nome e salário do empregado 000001. Note que as variáveis NOME, SALARIO e CODIGO que estão precedidas por (:) são as HOST VARIABLES, e devem estar incluídas na área do INCLUDE da declaração da tabela EMPREGADO na WORKING-STORAGE.

Também a HOST VARIABLE NOME esta acompanhada pelo seu INDICATOR INDNOME. OBS.: Se na definição da HOST VARIABLE, houver um sub-nivel da variavel para servir como INDICATOR, o INDICATOR pode ser omitido na clausula INTO. Exemplo:

WORKING-STORAGE SECTION. ...

10 NOME.

49 NOME-TEXT PIC X(20).

49 NOME-LEN PIC S9(4) COMP. PROCEDURE DIVISION.

...

EXEC SQL

SELECT NOME, SALARIO INTO :NOME, :SALARIO WHERE CODDEP = :DEPTO END-EXEC.

Se o comando SELECT estiver selecionando todas as colunas da tabela (SELECT *), o argumento INTO pode apontar para o nivel 01 das HOST VARIABLES como no exemplo:

EXEC SQL SELECT *

INTO :DCLPRODUTO FROM DBADB2.PRODUTO END-EXEC.

3.2.2 - ACESSANDO MAIS DE UMA LINHA DA TABELA.

Quando o SELECT monta uma tabela resultado com mais de uma linha, a linha resultado que deve carregar as HOST VARIABLES na clausula INTO fica indefinida. Se a lógica da aplicação estiver interessada na primeira linha da tabela resultado, a inclusão do parâmetro FETCH FIRST ROW ONLY seleciona esta linha, e o comando abaixo pode ser aplicado:

Exemplo:

MOVE `000001` TO CODIGO.

EXEC SQL

SELECT coluna, coluna, ...

INTO variavel, variavel, ....

FROM tabela

WHERE condição

FETCH FIRST ROW ONLY

END-EXEC

SELECT NOMEEMP, SALARIO INTO :NOME, :SALARIO

WHERE CODEMP > :CODIGO FETCH FIRST ROW ONLY END-EXEC.

3.2.2.1 - CURSOR.

Quando o comando SELECT cria a tabela resultado com mais de uma linha, e o programa precisa ler todas estas linhas, é necessário usar o componente CURSOR.

O CURSOR é um componente (objeto) criado dentro do programa Cobol através de um comando, contendo a definição da QUERY (SELECT). É usado para executar o SELECT e em seguida é usado para ler cada uma das linhas da tabela resultado.

Um programa pode construir vários CURSOR, porem cada um deles deve ter um nome diferente. O roteiro para operar o cursor é:

- Declarar o CURSOR, definindo seu nome e o comando SELECT

- Abrir o cursor com o comando OPEN. Neste instante o SELECT é executado. - Executar uma serie de comandos FETCH para ler cada uma das linhas resultado. - Fechar o CURSOR, para o DB2 liberar a tabela resultado.

Sintaxe da declaração do cursor:

Sintaxe do FETCH

EXEC SQL

DECLARE nome-do-cursor CURSOR FOR

Comando SELECT

END-EXEC

Sintaxe do OPEN CURSOR

EXEC SQL

FETCH nome-do-cursor

INTO :variável, :variável, ...

END-EXEC

EXEC SQL

OPEN nome-do-cursor CURSOR

END-EXEC

Sintaxe do CLOSE CURSOR

EXEC SQL

CLOSE nome-do-cursor CURSOR

END-EXEC

Exemplo de um trecho de programa usando CURSOR: EXEC SQL

DECLARE LER-EMP CURSOR FOR SELECT CODEMP NOMEMP

FROM FUNCIONARIOS WHERE CODDEPTO = `P10` END-EXEC.

EXEC SQL

OPEN LER-EMP CURSOR END-EXEC.

IF SQLCODE < 0

GO TO ERRO-SQL. LEITURA.

EXEC SQL

FETCH LER-EMP CURSOR

INTO :CODIGO, :NOME INDICATOR :INDNOME END-EXEC.

IF SQLCODE = +100 EXEC SQL

CLOSE LER-EMP CURSOR END-EXEC

GO TO CONTINUAR. IF SQLCODE < 0

GO TO ERRO-SQL.

DISPLAY `FUNCIONARIO ` CODIGO ` ` NOME GO TO LEITURA.

CONTINUAR.

3.2.2.2 - SCROLLABLE CURSOR

No item anterior a tabela resultado foi lida pelo CURSOR seqüencialmente. Após lida a ultima linha (SQLCODE = 100), o cursor foi fechado e a pesquisa encerrada.

Existe uma opção de CURSOR chamada SCROLLABLE CURSOR onde a pesquisa das linhas na tabela resultado é aleatória. Esta técnica porem alem de ser mais complexa e pouco usada, exige a criação pelo setor do suporte de arquivos temporários para suportar a tabela resultado, e não será estudada neste curso.

3.3 - INCLUINDO LINHAS NA TABELA.

A sintaxe do comando para incluir linhas em uma tabela é a mesma vista nos conceitos de SQL, somente acrescentando que os argumentos informados em VALUES podem ser constantes ou HOST VARIABLES.

Se alguma das colunas do comando INSERT for do tipo VARCHAR, o INDICATOR precisa ser carregado com o comprimento real do valor da coluna. Ex.:

EXEC SQL

INSERT INTO tabela

(coluna, coluna, ...)

VALUES(valor, valor, ...)

END-EXEC

30

EXEC SQL

INSERT INTO PRODUTO (CODIGO, NOME)

VALUES(:CODIGO, :NOME) END-EXEC.

3.4 - ATUALIZANDO LINHAS DA TABELA.

A sintaxe do comando para atualizar linhas em uma tabela é a mesma vista no comando UPDATE de SQL, somente acrescentando que os argumentos informados em VALUES podem ser constantes ou HOST VARIABLES.

Há dois formatos para o comando UPDATE:

EXEC SQL

UPDATE tabela

SET(coluna, coluna, ...)

=(valor, valor, ...)

WHERE condição

END-EXEC

Se alguma das colunas do comando UPDATE for do tipo VARCHAR, o INDICATOR precisa ser carregado com o comprimento real do valor da coluna. Ex.:

EXEC SQL

UPDATE tabela

SET coluna=valor, coluna=valor, ....

WHERE condição

END-EXEC

MOVE 10 TO NOME-LEN EXEC SQL

UPDATE PRODUTO SET NOME = :NOME

WHERE CODIGO = :CODIGO END-EXEC.

3.4.1 - ATUALIZAÇÃO POSICIONADA.

Este tipo de atualização ocorre quando a atualização é feita em uma linha da RESULT TABLE que foi posicionada por um comando FETCH de um CURSOR. Neste caso a clausula WHERE do UPDATE não define a condição de seleção da linha, mas declara que está usando a posição corrente do CURSOR.

Para usar uma atualização posicionada, a declaração do CURSOR precisa definir que ele será usado para atualizações, preferivelmente citando a coluna que vai ser atualizada com a sintaxe seguinte:

EXEC SQL

DECLARE nome-do-cursor CURSOR FOR

SELECT linhas

FROM tabela

WHERE condição

Se a lógica do programa não souber antecipadamente a coluna a ser atualizada numa atualização posicionada, o CURSOR pode ser declarado para atualização generica com a sintaxe:

Quando o cursor foi declarado para atualização com as sintaxes acima, após cada FETCH o programa pode atualizar a linha lida com a sintaxe abaixo:

EXEC SQL

DECLARE nome-do-cursor CURSOR FOR

SELECT linhas

FROM tabela

WHERE condição

FOR UPDATE

END-EXEC

EXEC SQL

UPDATE tabela

SET coluna=valor, coluna=valor, ....

WHERE CURRENT OF CURSOR

END-EXEC

OBS.: Se o SELECT do CURSOR contiver a clausula ORDER BY, a tecnica de atualização posicionada somente poderá ser usada se o CUSOR for declarado SCROLLABLE.

3.5 - EXCLUINDO LINHAS DA TABELA

A sintaxe do comando para excluir linhas em uma tabela é a mesma vista no comando DELETE de SQL.

Sintaxe para o comando:

EXEC SQL

DELETE FROM tabela

WHERE condição

END-EXEC

Exemplo:

EXEC SQL

DELETE FROM PRODUTO WHERE CODIGO = :CODIGO