 |
| Bellacosa Mainframe e o laboratorio basico Db2 Spufi |
☕ Um Café no Bellacosa Mainframe
☕ Laboratório Básico — Explorando o Catálogo SYSIBM do Db2 for z/OS com SPUFI
1. Objetivo do laboratório
Neste laboratório vamos aprender a utilizar o SPUFI — SQL Processor Using File Input, disponível tradicionalmente através do ambiente Db2 Interactive (DB2I) no ISPF, para executar consultas SQL contra o catálogo do Db2 for z/OS.
Ao final você deverá compreender:
o que é SPUFI;
o que é o catálogo do Db2;
o que significa
SYSIBM;como descobrir tabelas existentes;
como descobrir as colunas de uma tabela;
como localizar índices;
como identificar diferentes tipos de objetos;
como filtrar resultados;
como interpretar resultados e erros básicos;
por que consultar o catálogo é uma habilidade importante para um programador COBOL/Db2.
Regra do laboratório: trabalharemos somente com
SELECT. Não precisamos modificar nenhum objeto ou dado.
2. Antes de começar: o que é SYSIBM?
Quando trabalhamos com uma aplicação comum podemos ter tabelas como:
CLIENTES
CONTAS
PRODUTOS
PEDIDOS
FUNCIONARIOS
Mas o próprio Db2 precisa guardar informações sobre aquilo que administra.
Por exemplo:
Quais tabelas existem?
Quem criou determinada tabela?
Quais colunas ela possui?
Qual é o tipo de cada coluna?
Quais índices existem?
Quais schemas existem?
Quais views existem?
Essas informações são chamadas de metadados.
Uma maneira simples de pensar nisso é:
DADOS
│
├── CLIENTES
├── CONTAS
├── PEDIDOS
└── PRODUTOS
METADADOS
│
└── informações sobre os dados e objetos
O catálogo do Db2 contém esses metadados.
Muitas tabelas fundamentais do catálogo estão sob o qualifier/schema:
SYSIBM
Encontraremos nomes conhecidos como:
SYSIBM.SYSTABLES
SYSIBM.SYSCOLUMNS
SYSIBM.SYSINDEXES
SYSIBM.SYSVIEWS
Portanto:
SYSIBM.SYSTABLES
│ │
│ └── tabela do catálogo
│
└────────── schema/qualifier
Você pode imaginar SYSIBM como uma enorme biblioteca administrativa onde o Db2 mantém informações sobre seu próprio universo.
3. Entrando no SPUFI
O caminho depende da instalação da empresa ou laboratório.
Pode ser algo semelhante a:
TSO/E
│
▼
ISPF
│
▼
DB
│
▼
DB2I
│
▼
SPUFI
Em algumas instalações as opções possuem números; em outras existem menus customizados.
Procure pela opção:
SPUFI
ou:
SQL Processor Using File Input
4. Preparando o SPUFI
O SPUFI trabalha fundamentalmente com um dataset contendo o SQL de entrada e outro para receber a saída.
Conceitualmente:
MEU.SQL
│
│ SELECT...
▼
SPUFI
│
▼
Db2
│
▼
resultado
│
▼
MEU.SQLOUT
Você poderá encontrar campos semelhantes a:
INPUT DATA SET NAME ===> USERID.SQL
OUTPUT DATA SET NAME ===> USERID.SQLOUT
EDIT INPUT ===> YES
EXECUTE ===> YES
BROWSE OUTPUT ===> YES
Os nomes e parâmetros exatos podem variar conforme o ambiente.
Utilize os datasets e configurações definidos pelo seu instrutor ou instalação.
5. Exercício 1 — Perguntando as horas ao Db2
Antes de investigar o catálogo, vamos verificar se conseguimos conversar com o Db2.
Execute:
SELECT CURRENT DATE,
CURRENT TIME
FROM SYSIBM.SYSDUMMY1;
O que está acontecendo?
SYSIBM.SYSDUMMY1 é uma pequena tabela especial tradicionalmente utilizada quando queremos executar expressões SQL que não dependem de uma tabela de aplicação.
Estamos pedindo:
CURRENT DATE
CURRENT TIME
O fluxo é:
SPUFI
│
│ SQL
▼
Db2
│
│ resultado
▼
SPUFI OUTPUT
Se obtivermos uma data e horário, nossa comunicação está funcionando.
6. Exercício 2 — Quem sou eu para o Db2?
Experimente:
SELECT CURRENT USER
FROM SYSIBM.SYSDUMMY1;
O resultado mostrará a identidade de autorização associada à execução.
Isso é importante porque o Db2 possui mecanismos próprios de autorização.
Dois usuários podem executar o mesmo SQL e obter comportamentos diferentes por causa das permissões concedidas.
Guarde esta ideia:
USUÁRIO
│
▼
AUTORIZAÇÃO
│
▼
OBJETO Db2
7. Exercício 3 — Conhecendo SYSIBM.SYSTABLES
Agora chegamos a uma das tabelas de catálogo mais úteis:
SYSIBM.SYSTABLES
Ela contém informações sobre tabelas e determinados objetos relacionados registrados no catálogo.
Não comece com:
SELECT *
FROM SYSIBM.SYSTABLES;
Em um ambiente grande isso pode produzir uma quantidade enorme de informação.
Vamos ser educados com o mainframe.
Execute:
SELECT CREATOR,
NAME,
TYPE
FROM SYSIBM.SYSTABLES
FETCH FIRST 20 ROWS ONLY;
Observe as colunas.
CREATOR
Indica o qualifier/creator associado ao objeto.
NAME
Nome do objeto.
TYPE
Indica seu tipo conforme os códigos definidos pelo catálogo daquela versão do Db2.
Não tente decorar imediatamente todos os valores possíveis de TYPE.
O importante neste momento é entender:
SYSTABLES
CREATOR NAME TYPE
-------- --------------- ----
SYSIBM SYSTABLES ...
SYSIBM SYSCOLUMNS ...
...
Você acabou de perguntar ao Db2:
"Quais objetos você conhece?"
8. Exercício 4 — Procurando as próprias tabelas SYSIBM
Agora vamos filtrar.
Execute:
SELECT CREATOR,
NAME,
TYPE
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'SYSIBM'
FETCH FIRST 30 ROWS ONLY;
Aqui aparece uma das regras fundamentais de SQL:
SELECT → o que quero
FROM → de onde
WHERE → quais registros quero
Visualmente:
SYSIBM.SYSTABLES
│
▼
WHERE CREATOR = 'SYSIBM'
│
▼
somente objetos SYSIBM
│
▼
SELECT CREATOR, NAME, TYPE
9. Exercício 5 — Procurando uma tabela pelo nome
Agora imagine que alguém diz:
"Existe uma tabela chamada SYSTABLES."
Você quer descobrir onde.
Execute:
SELECT CREATOR,
NAME,
TYPE
FROM SYSIBM.SYSTABLES
WHERE NAME = 'SYSTABLES';
Provavelmente encontrará:
SYSIBM SYSTABLES ...
Agora você conhece uma técnica extremamente importante.
Quando alguém disser:
"Procure a tabela XPTO."
Você pode consultar o catálogo.
10. Exercício 6 — Usando LIKE
Nem sempre sabemos o nome completo.
Talvez alguém diga:
"Era alguma coisa começando com SYS..."
Podemos utilizar:
SELECT CREATOR,
NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'SYSIBM'
AND NAME LIKE 'SYS%'
FETCH FIRST 30 ROWS ONLY;
O % funciona como wildcard para uma sequência de caracteres.
'SYS%'
pode localizar:
SYS...
SYSTABLES
SYSCOLUMNS
SYSINDEXES
...
Agora experimente:
SELECT CREATOR,
NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'SYSIBM'
AND NAME LIKE '%TABLE%'
FETCH FIRST 30 ROWS ONLY;
A diferença é importante:
'TABLE%' começa com TABLE
'%TABLE' termina com TABLE
'%TABLE%' contém TABLE
11. Exercício 7 — Conhecendo SYSIBM.SYSCOLUMNS
Encontramos uma tabela.
Agora queremos saber:
"Quais colunas ela possui?"
Para isso temos uma das estrelas do catálogo:
SYSIBM.SYSCOLUMNS
Execute:
SELECT TBCREATOR,
TBNAME,
NAME,
COLNO,
COLTYPE,
LENGTH
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'SYSIBM'
AND TBNAME = 'SYSTABLES'
ORDER BY COLNO;
Agora o Db2 está descrevendo uma de suas próprias tabelas.
Isso é quase recursivo:
SYSCOLUMNS
│
│ descreve
▼
SYSTABLES
│
│ que descreve
▼
objetos existentes no Db2
12. Entendendo o resultado
Observe alguns conceitos.
TBCREATOR
Creator/qualifier da tabela.
TBNAME
Nome da tabela.
NAME
Nome da coluna.
COLNO
Posição da coluna.
COLTYPE
Tipo de dado registrado no catálogo.
LENGTH
Comprimento associado à coluna conforme sua definição.
Assim você começa a descobrir a estrutura de uma tabela sem precisar encontrar primeiro o programa COBOL que a utiliza.
13. Exercício 8 — Conte quantas colunas SYSTABLES possui
Agora podemos usar uma função SQL:
SELECT COUNT(*)
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'SYSIBM'
AND TBNAME = 'SYSTABLES';
O Db2 contará os registros correspondentes.
Como existe normalmente uma entrada de catálogo para cada coluna da tabela, conseguimos responder:
"Quantas colunas existem nessa tabela?"
Isso demonstra uma coisa poderosa:
CATÁLOGO
+
SQL
=
INVESTIGAÇÃO
14. Exercício 9 — Encontrando índices
Tabelas podem possuir índices.
Vamos perguntar ao catálogo:
SELECT CREATOR,
NAME,
TBCREATOR,
TBNAME
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR = 'SYSIBM'
AND TBNAME = 'SYSTABLES';
Aqui estamos utilizando:
SYSIBM.SYSINDEXES
Essa tabela contém informações sobre índices registrados no catálogo.
A ideia conceitual é:
TABELA
│
├───────────────┐
│ │
▼ ▼
dados índices
│
▼
estruturas de acesso
Não pense que índice é simplesmente "uma tabela ordenada".
Índices são estruturas mantidas pelo Db2 que podem ser utilizadas para localizar dados de maneira eficiente e também podem participar da implementação de determinadas restrições de unicidade.
15. Exercício 10 — Procurando índices de uma tabela qualquer
Se o seu instrutor fornecer uma tabela de treinamento, por exemplo:
ALUNO.CLIENTES
poderíamos investigar:
SELECT CREATOR,
NAME,
TBCREATOR,
TBNAME
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR = 'ALUNO'
AND TBNAME = 'CLIENTES';
Assim começamos a montar uma ficha do objeto:
ALUNO.CLIENTES
│
├── colunas → SYSCOLUMNS
│
└── índices → SYSINDEXES
16. Exercício 11 — Vamos fazer uma pequena investigação
Imagine que recebemos apenas esta informação:
Existe alguma coisa chamada EMP?
Não sabemos se o nome completo é:
EMP
EMPLOYEE
EMPLOYEES
EMPRESA
EMPREGADO
Podemos investigar:
SELECT CREATOR,
NAME,
TYPE
FROM SYSIBM.SYSTABLES
WHERE NAME LIKE 'EMP%'
FETCH FIRST 50 ROWS ONLY;
Encontrou algo interessante?
Escolha um objeto ao qual você tenha acesso.
Suponha que encontramos:
DSN8C10.EMP
Não copie esse nome cegamente: objetos de exemplo disponíveis variam conforme a instalação.
Agora investigamos suas colunas:
SELECT NAME,
COLNO,
COLTYPE,
LENGTH
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'DSN8C10'
AND TBNAME = 'EMP'
ORDER BY COLNO;
Acabamos de fazer algo muito parecido com uma investigação real de aplicação.
17. Exercício 12 — Do geral para o específico
Observe a metodologia que estamos construindo.
Primeiro:
SELECT CREATOR,
NAME
FROM SYSIBM.SYSTABLES
WHERE NAME LIKE '%EMP%'
FETCH FIRST 50 ROWS ONLY;
Encontramos:
qualifier + tabela
Depois:
SELECT NAME,
COLTYPE,
LENGTH
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'QUALIFIER_ENCONTRADO'
AND TBNAME = 'TABELA_ENCONTRADA'
ORDER BY COLNO;
Depois:
SELECT CREATOR,
NAME
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR = 'QUALIFIER_ENCONTRADO'
AND TBNAME = 'TABELA_ENCONTRADA';
Nossa investigação virou:
Qual objeto existe?
│
▼
SYSTABLES
│
▼
Quais colunas possui?
│
▼
SYSCOLUMNS
│
▼
Quais índices possui?
│
▼
SYSINDEXES
18. Exercício 13 — ORDER BY
Vamos organizar os resultados.
SELECT TBCREATOR,
TBNAME,
NAME,
COLNO
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'SYSIBM'
AND TBNAME = 'SYSTABLES'
ORDER BY COLNO;
Sem ORDER BY, não devemos assumir uma ordem lógica simplesmente porque os resultados apareceram daquela maneira em uma execução.
Com:
ORDER BY COLNO
estamos explicitamente pedindo a ordenação.
Essa diferença é importantíssima.
Nunca programe pensando:
"Ontem o SELECT veio nessa ordem."
Se a ordem importa, declare-a.
19. Exercício 14 — Provocando um erro proposital
Erros também fazem parte do laboratório.
Execute propositalmente:
SELECT XPTO
FROM SYSIBM.SYSTABLES;
XPTO provavelmente não existe como coluna dessa tabela.
Observe cuidadosamente a saída do SPUFI.
Procure:
SQLCODE
SQLSTATE
mensagem Db2
A mensagem exata dependerá do problema e da versão.
A lição aqui não é decorar um número.
É desenvolver este reflexo:
SQL falhou
│
▼
não comece alterando tudo
│
▼
leia SQLCODE
│
▼
leia SQLSTATE
│
▼
leia mensagem
│
▼
identifique a causa
│
▼
corrija
Isso será fundamental quando chegarmos ao COBOL.
20. Exercício 15 — Procurando algo que não existe
Execute:
SELECT CREATOR,
NAME
FROM SYSIBM.SYSTABLES
WHERE NAME = 'TABELA_DO_HARRY_POTTER_999';
Supondo que ninguém tenha criado uma tabela com esse nome, teremos zero linhas.
E aqui existe uma lição importante:
SELECT executado corretamente
+
nenhuma linha encontrada
não significa necessariamente:
SQL quebrado
O comando pode estar perfeitamente correto.
Simplesmente não encontrou registros correspondentes.
Essa diferença será importantíssima quando estudarmos SQLCODE +100 em programas Db2.
21. Desafio — faça o papel de detetive do Db2
Agora tente resolver sem copiar imediatamente a resposta.
Sua missão é:
1. Encontrar cinco objetos cujo creator seja SYSIBM.
2. Escolher uma tabela do catálogo.
3. Descobrir quantas colunas ela possui.
4. Listar suas colunas em ordem.
5. Identificar os tipos dessas colunas.
6. Verificar se existem índices associados a ela.
7. Registrar os SQLs utilizados.
Sua investigação deverá seguir aproximadamente:
Db2
│
▼
SYSIBM.SYSTABLES
│
▼
achei a tabela
│
▼
SYSIBM.SYSCOLUMNS
│
▼
achei as colunas
│
▼
SYSIBM.SYSINDEXES
│
▼
achei os índices
22. O que realmente aprendemos?
Parece que fizemos apenas alguns SELECT.
Na realidade aprendemos uma parte fundamental da filosofia do Db2.
O banco não guarda somente:
CLIENTE = JOÃO
SALDO = 1000
Ele também precisa conhecer a estrutura do próprio ambiente:
Existe tabela CLIENTES?
Quem é seu creator?
Quais colunas possui?
Quais são seus tipos?
Quais índices estão associados?
Que outros objetos existem?
Esse é o papel do catálogo.
Podemos imaginar:
Db2
│
┌───────┴────────┐
│ │
▼ ▼
DADOS CATÁLOGO
│ │
▼ ▼
CLIENTES SYSTABLES
CONTAS SYSCOLUMNS
PEDIDOS SYSINDEXES
... ...
O catálogo é, portanto, um conjunto de informações que permite ao Db2 conhecer e administrar seu próprio ambiente.
23. Para que um programador COBOL precisa saber disso?
Imagine que você recebe um programa COBOL antigo contendo:
EXEC SQL
SELECT NOME,
SALDO
INTO :WS-NOME,
:WS-SALDO
FROM CLIENTES
WHERE ID_CLIENTE = :WS-ID
END-EXEC.
Você nunca viu a aplicação.
Imediatamente surgem perguntas:
Quem é CLIENTES?
Qual seu qualifier?
ID_CLIENTE é INTEGER?
NOME é CHAR ou VARCHAR?
SALDO é DECIMAL?
Existem índices?
Qual é a estrutura dessa tabela?
Agora você possui uma ferramenta para começar a investigação.
Programa COBOL
│
│ encontrou CLIENTES
▼
SPUFI
│
▼
SYSIBM.SYSTABLES
│
▼
SYSIBM.SYSCOLUMNS
│
▼
SYSIBM.SYSINDEXES
│
▼
começamos a compreender
a aplicação
É por isso que aprender o catálogo vale muito mais do que decorar meia dúzia de SELECT.
24. Cola de bolso do aluno
Guarde estes três nomes:
┌───────────────────────────────────────────┐
│ CATÁLOGO Db2 — BÁSICO │
├───────────────────────────────────────────┤
│ │
│ SYSIBM.SYSTABLES │
│ ↓ │
│ "Quais tabelas/objetos existem?" │
│ │
│ SYSIBM.SYSCOLUMNS │
│ ↓ │
│ "Quais são suas colunas?" │
│ │
│ SYSIBM.SYSINDEXES │
│ ↓ │
│ "Quais índices existem?" │
│ │
└───────────────────────────────────────────┘
E principalmente guarde a metodologia:
NÃO SEI
│
▼
CONSULTO O CATÁLOGO
│
▼
ENCONTRO O OBJETO
│
▼
INVESTIGO SUA ESTRUTURA
│
▼
ENTENDO AS RELAÇÕES
│
▼
VOLTO AO PROGRAMA COBOL
Essa é uma mudança importante na formação de um profissional de mainframe.
O iniciante pergunta:
"Qual é a tabela?"
O profissional aprende a perguntar ao próprio Db2.
☕ Fim do laboratório — e início da investigação.
