SSSSSSSSS
SSS
SSSSSSS
SSSSSSS
SSS
SSSSSSSSS
QQQQQQQ
QQ QQ
QQ QQ
QQ QQ
QQ QQQ
QQQQQQ QQ
LL
LL
LL
LL
LLLLLLLL
LLLLLLLLL
//
//
//
//
//
//
DDDDDDD
DDDDDDDD
DD DD
DD DD
DD DD
DDDDDDDD
BBBBBBB
BBBBBBBB
BB BB
BBBBBBBB
BB BB
BBBBBBBB
222222222
222
2222222
2222222
222
Begoña Ramos Barrios 222222222
I
INDICE
________
CONCEPTOS.
Conceptos ………………………………………… 1
Tablas …………………………………………… 1
Tipos de datos que puede contener una columna de una tabla 2
Indices ………………………………………….. 2
MECANISMOS DE SEGURIDAD DE LOS DATOS.
Vistas …………………………………………… 2
Racf (Resource Access Control Facility) ……………… 3
Passwords de ficheros Vsam ………………………… 3
Autorizacion ……………………………………… 3
MECANISMOS DE INTEGRIDAD DE LOS DATOS.
Concurrencia ……………………………………… 3
Recuperacion de datos ……………………………… 3
- Unidad de recuperacion …………………………. 3
Ayudas a la programacion …………………………… 4
- Spufi ………………………………………… 4
- Paneles para preparacion de programas ……………. 4
- Paneles para mantenimiento de planes …………….. 4
- DCLGEN (Declarations Generator) …………………. 4
- Online Help …………………………………… 5
ESTRUCTURA DE UN PROGRAMA QUE OPERA EN UN ENTORNO CICS.
Estructura de un programa que opera en un entorno cics … 5
Preparacion del programa para la ejecucion …………… 5
- Traslacion ……………………………………. 5
- Precompilacion ………………………………… 6
- Compilacion y link-edicion ……………………… 6
- Binding ………………………………………. 6
INSTRUCCIONES S.Q.L.
Instrucciones basicas ……………………………… 7
Clausula Where ……………………………………. 8
Select into ………………………………………. 8
Update …………………………………………… 9
Delete …………………………………………… 9
Insert …………………………………………… 10
Insercion a la vez de varias filas de una tabla en otra .. 10
Funciones del cursor ………………………………. 11
Declare cursor ……………………………………. 11
Open …………………………………………….. 12
Fetch ……………………………………………. 12
II
Whenever not found ………………………………… 13
Update …………………………………………… 13
Delete …………………………………………… 14
Close ……………………………………………. 14
Opciones de la instruccion select …………………… 15
- Operaciones aritmeticas ………………………… 15
- Funciones Built-in …………………………….. 16
- Avg ………………………………………….. 16
- Max ………………………………………….. 16
- Min ………………………………………….. 16
- Sum ………………………………………….. 16
- Count ………………………………………… 16
- Clausulas Group by, Having y Order by ……………. 17
- Group by ……………………………………… 17
- Having ……………………………………….. 18
- Order by ……………………………………… 18
- Union ………………………………………… 19
Seleccion de datos de varias tablas en una fila (join) … 20
Opciones de las condiciones de busqueda ……………… 21
- Between … And ……………………………….. 21
- In …………………………………………… 21
- Like …………………………………………. 21
- Is null ………………………………………. 22
VISTAS.
Concepto …………………………………………. 22
Proceso de una vista ………………………………. 23
Subquery …………………………………………. 24
Formas de incluir una subquery en una clausula where o
having …………………………………………… 24
- Inmediatamente despues de un operador de comparacion.. 24
- Despues de un operador de comparacion seguido de all
o any ………………………………………….. 25
- Despues de In, para indicar que el valor de la expre-
sion debe estar entre los valores devueltos por la sub-
query ………………………………………….. 25
- Despues de Exists, verificacion de que puede ser en-
contrada una fila con la condicion de la subquery …… 26
Subquery correlativa ………………………………. 26
Otros ejemplos ……………………………………. 26
- Con una SELECT ………………………………… 26
- Con una UPDATE ………………………………… 27
- Con una DELETE ………………………………… 27
SPUFI.
Crear y probar instrucciones SQL ……………………. 27
Creacion de una tabla ……………………………… 28
Creacion de una vista ……………………………… 28
Creacion de una tabla ……………………………… 28
Crear un nombre alternativo o sinonimo para una tabla o
vista y dar de baja ese sinonimo ……………………. 28
III
PANELES.
Panel Principal …………………………………… 28
- Especificar un fichero de entrada ……………….. 28
- Especificar un fichero de salida ………………… 28
- Especificar las opciones de proceso ……………… 29
Panel de opciones por defecto ………………………. 29
- Especificar otras opciones de proceso ……………. 29
- Definir caracteristicas del fichero de salida …….. 29
- Especificar defectos para el formato de salida ……. 30
Panel Commit/Rollback ……………………………… 30
Restricciones …………………………………….. 30
CODIFICACION DE INSTRUCCIONES SQL EN UN PROGRAMA COBOL.
Definir un area de comunicacion llamada SQLCA ………… 31
Describir cada tabla o vista a la que acceda el programa . 31
Codificar instrucciones SQL ………………………… 32
- Variables y estructuras host ……………………. 32
- Variables indicador ……………………………. 33
Manejo de codigos de retorno de errores : LA SQLCA ……. 33
Manejo de condiciones excepcionales: Instruccion Whenever. 35
- Tres condiciones que se pueden especificar ……….. 36
- Dos acciones que se pueden especificar …………… 36
Sugerencias para la codificacion de instrucciones SQL …. 36
- Facilitar el uso de indices por el DB2 …………… 36
- Facilitar la seleccion de datos de dos o mas tablas .. 36
Notas sobre DB2 / SQL ……………………………… 37
APENDICE I.
Ejemplos de instrucciones sql en cobol II ……………. 37
- Distinct ……………………………………… 37
- Count ………………………………………… 38
- Sum ………………………………………….. 38
- Union ………………………………………… 39
- Between (not between) ………………………….. 40
- In (not in) …………………………………… 40
- Like (not like) ……………………………….. 40
- Group by ……………………………………… 40
- Max ………………………………………….. 41
- Min ………………………………………….. 41
- Having ……………………………………….. 41
RECOMENDACIONES PARA EL DISEÑO DE APLICACIONES EN DB2.
Administracion de datos ……………………………. 42
Administracion de base de datos …………………….. 42
Administracion del sistema DB2 ……………………… 43
Programador del sistema DB2 ………………………… 43
Operacion del sistema DB2 ………………………….. 44
Analisis de aplicaciones …………………………… 44
Programacion de aplicaciones ……………………….. 45
IV
Soporte de centro de informacion ……………………. 45
Representantes de los usuarios ……………………… 46
Macro actividades a desarrollar en un proyecto ……….. 46
- Planificacion …………………………………. 46
- Hw/sw instal. …………………………………. 46
- Diseño y desarrollo aplicacion ………………….. 46
- Operacion y recuperacion ……………………….. 47
- Gestion Rdto. …………………………………. 47
PLANIFICACION.
Gestion del proyecto ………………………………. 47
Seleccion de la aplicacion piloto …………………… 47
Definicion de estandares …………………………… 48
Identificar los recursos a proteger …………………. 48
DISEÑO Y DESARROLLO DE LA APLICACION.
Establecer el entorno de desarrollo de la aplicacion ….. 48
Analisis de aplicaciones …………………………… 49
Diseño de Base de Datos ……………………………. 49
Desarrollo y prueba de programas ……………………. 49
Paso de desarrollo a produccion …………………….. 50
DISEÑO DE BASES DE DATOS.
Introduccion ……………………………………… 51
DISEÑO LOGICO DE BASES DE DATOS.
Analisis de datos …………………………………. 51
Normalizacion …………………………………….. 52
Integridad referencial …………………………….. 53
INTEGRIDAD DE DATOS.
Un adecuado diseño de base de datos …………………. 53
- Analisis total de los datos …………………….. 53
- Normalizacion de los datos ……………………… 53
Un adecuado diseño de modulos, codificacion y pruebas …. 54
RENDIMIENTO Y CONCURRENCIA EN BASE DE DATOS.
Concurrencia ……………………………………… 55
Trabajo realizado por el DB2 para el SQL …………….. 55
Diseño fisico de bases de datos …………………….. 56
Seleccion de indices ………………………………. 60
1
CONCEPTOS
_________
El DB2 es un sistema de manejo de bases de datos basado en un modelo
relacional de datos. Rueda bajo sistema operativo MVS/SP como un sub-
sistema de este:
+————-+
| TERMINAL |
| CICS/OS/VS |
+————-+
|
+———|—————————————————+
| v |
| +————+ MVS/SD |
| | CICS/OS/VS |————-+ Sistema Operativo |
| +————+ | |
| | | |
| v v |
| +————+ +——-+ +———–+ |
| | IMS/VS | | DB2 |<——- | TSO y | |
| | DB | +——-+ | BATCH | |
| +————+ | +———–+ |
+———|——————|——————————–+
| |
v v
+———-+ +———–+
+———-+ | +———-+ |
| Bases de | | | Bases de | |
| Datos |–+ | Datos |—-+
| IMS/VS | | DB2 |
| DL/I | | |
+———-+ +———-+
TABLAS
______
Los datos en las bases de datos DB2 estan disponibles para un programa
de aplicacion como un conjunto de tablas. Las tablas son estructuras de
datos de dos dimensiones, compuestas de FILAS y COLUMNAS.
Un nombre de tabla esta formado por un identificador de la persona que
creo la tabla, y de un nombre descriptivo de esta, separados por punto.
2
TIPOS DE DATOS QUE PUEDE CONTENER UNA COLUMNA DE UNA TABLA
__________________________________________________________
. Cadenas de caracteres EBCDIC:
CHAR - Cadenas de longitud fija
VARCHAR - Cadenas de longitud variable
. Datos numericos:
SMALLINT - Enteros binarios de media palabra
INTEGER - Enteros binarios de una palabra
DECIMAL - Numeros decimales
FLOAT - Numeros de coma flotante
. Cadenas de caracteres graficos:
GRAFHIC - DBCS (Double Byte Character Data) de longitud fija
VARGRAPHIC - DBCS de longitud variable
INDICES
_______
El DB2 proporciona dos mecanismos de acceso a tablas, acceso secuencial
y acceso directo mediante indices. El indice debe ser unico.
Estan basados en los valores de los datos de una o mas columnas, y son
mantenidos automaticamente por el DB2 cuando se produce un cambio en
los datos contenidos en la tabla. Un programa no se refiere a los indi-
ces, sino que el DB2 selecciona el indice para acceder a los datos que
el programa requiere.
El DB2 nunca usa como indice una columna que va a ser actualizada, o
una columna que esta siendo comparada con otra de la misma fila.
MECANISMOS DE SEGURIDAD DE LOS DATOS
____________________________________
VISTAS
______
Permiten a un usuario acceder solo a ciertas filas o columnas de una
tabla.
3
RACF (Resource Access Control Facility)
_______________________________________
Proporciona control de acceso al DB2 por usuarios o aplicaciones no au-
torizados, a los ficheros vsam que contienen las bases de datos, a un
dispositivo de acceso directo, etc.
PASSWORDS DE FICHEROS VSAM
__________________________
Se usan en ficheros no protegidos con RACF.
AUTORIZACION
____________
Mediante instrucciones GRANT y REVOKE de identificadores de usuarios.
MECANISMOS DE INTEGRIDAD DE LOS DATOS
_____________________________________
CONCURRENCIA
____________
El DB2 permite que mas de un programa acceda a los mismos datos al
mismo tiempo.
La concurrencia es controlada mediante LOCKS. Consisten en asociar un
recurso DB2 con un programa, de forma que esta asociacion afecta a como
pueden acceder otros programas al mismo recurso. Existen varios tipos:
S (Share), U (Update), X (Exclusive), etc.
Ningun programa podra acceder a los datos que hayan sido cambiados por
otro programa, pero que todavia no esten en la base.
RECUPERACION DE DATOS
_____________________
Unidad de recuperacion
______________________
Proceso que ocurre entre dos puntos de COMMIT. Es una secuencia de
acciones que necesita ser completada para que cualquiera de las accio-
4
nes individuales que la forman pueda considerarse como acabada.
Ejemplo:
Una transaccion que transfiere fondos de una cuenta A a otra B, debera
primero sustraer la cantidad a transferir de la cuenta A, y luego sumar
a la cuenta B. Cuando se terminen ambas acciones, y no antes, los datos
de ambas cuentas seran consistentes.
Una unidad de recuperacion se señala como completa mediante un COMMIT o
Synchronization (Sync) Point, de las siguientes formas:
1. Implicitamente al final de una transaccion: EXEC CICS RETURN
2. Explicitamente en puntos de la transaccion: EXEC CICS SYNCPOINT
3. Implicitamente : EXEC DLI TERM
4. Implicitamente en un programa BATCH-DLI : EXEC DLI CHKP
Si ocurre una accion y otra no, se dice que la base de datos ha perdido
su integridad o consistencia. Para lograr que los datos recuperen el
valor que tenian antes de comenzar la unidad de recuperacion, se usa la
opcion ROLLBACK para el comando SYNCPOINT.
AYUDAS A LA PROGRAMACION
________________________
Spufi:
_____ Permite usar el SQL sin tener que codificar un programa completo
mediante instrucciones que pueden ser ejecutadas y mostradas en
el terminal.
Paneles para preparacion de programas:
_____________________________________ Permiten la precompilacion, com-
pilacion, link-edicion y binding de un programa. Solo se necesi-
ta especificar el nombre del fichero que contiene las instruccio-
nes fuente.
Paneles para mantenimiento de planes:
____________________________________ Con las opciones BIND, REBIND, y
FREE. Permiten crear, cambiar o borrar el plan de cualquier pro-
grama DB2.
DCLGEN (Declarations Generator):
_______________________________ Usa la informacion del catalogo DB2
para:
. Crear definiciones de las estructuras de tablas o vistas,
que van a ser guardadas en librerias de Copys para su pos-
5
terior inclusion en los programas.
. Producir instrucciones SQL DECLARE TABLE
ONLINE HELP:
___________ Proporciona ayuda durante la ejecucion del spufi.
ESTRUCTURA DE UN PROGRAMA QUE OPERA EN UN ENTORNO CICS
______________________________________________________
+———————————————–+
| APLICACION |
| |
| 1 - Area de datos <———–+ |
| 2 - SQLCA | |
| | |
| +–> 3 - Entrada al programa | |
| | 4 - Tratar mensaje de entrada | |
| | 5 - Proceso | |
| | 6 - Instrucciones SQL —+ | |
| | 7 - Mensaje de salida | | |
| | +– 8 - Terminacion | | |
| | | | | |
+—|–|—————————-|—-|——+
| | | |
| | v |
+——–|————–+ +———–+ —–> +—–+
| ENTRY | CICS/OS/VS | | CICS/OS/VS| | DB2 |
| v | +———–+ <—– +—–+
| EXIT |
+———————–+
1- DB2 pone los valores de las columnas recuperadas en las variables
del programa.
2- Cada programa que accede a datos DB2 debe tener un area de datos
llamada SQLCA (SQL Comunication Area), usada por el DB2 para decir
al programa si la ejecucion de la ultima instruccion SQL ha sido
correcta, mediante un codigo de retorno , que se puede examinar en
los campos SQLCODE y SQLWARN0 (deben ser cero y blanco).
PREPARACION DEL PROGRAMA PARA LA EJECUCION
__________________________________________
a. Traslacion de las instrucciones EXEC por el CICS/OS/VS.
__________
6
b. Precompilacion del programa.
______________
El precompilador busca errores de sintaxis, y prepara cada instru-
ccion SQL para compilacion o ensamblaje, dando como resultado un
conjunto de instrucciones fuente (modulo fuente).
Ademas, el DB2 crea un DBRM (Data Base Request Module), que sera
usado en el paso d, y contiene informacion acerca de cada instru-
ccion SQL precompilada.
c. Compilacion ( o ensamblaje) y link-edicion.
___________ ____________
Cada modulo fuente es compilado o ensamblado para dar lugar a un
modulo de carga. Al link-editar, los modulos de carga se enlazan
en un unico modulo objeto.
d. Relacionar el programa y los datos DB2 (binding).
_______
Se relaciona el programa (el DBRM que representa las peticiones
SQL hechas por el programa) y las tablas y vistas DB2 que se quie-
ren procesar. Esto da lugar a un plan de aplicacion, el cual es
almacenado por el DB2 y usado cuando se ejecute el programa.
El proceso de binding incluye los siguientes pasos:
. Examinar la validez de los nombres de columnas, tablas y
vistas utilizados en el programa
. Verificar que la persona esta autorizada a realizar las
operaciones de acceso especificadas por las instrucciones
SQL del programa
. Seleccionar los caminos de acceso a los datos DB2 que el
programa quiere procesar
Cuando se va a hacer bind de una aplicacion se puede especificar:
- Isolation level:
. RR el lock afecta a todas las filas que toca el programa
. CS el lock afecta a todas las filas a las que el programa
esta accediendo actualmente
Se puede hacer lock del espacio de tabla entero que contiene la ta-
bla que se quiere proteger mediante una instruccion lock table:
. Para permitir a otras aplicaciones recuperar, pero no ac-
tualizar, borrar o insertar filas se usa
EXEC SQL
LOCK TABLE nombre_de_tabla
IN SHARE MODE
END-EXEC
7
. Para impedir que otras aplicaciones accedan a la tabla de
cualquier forma se usa
EXEC SQL
LOCK TABLE nombre_de_tabla
IN EXCLUSIVE MODE
END-EXEC
- Resource adquisition time
. USE, por defecto
indica que se quieren adquirir los locks cuando los espa-
cios de tablas asociados sean accedidos por primera vez
. ALLOCATE
indica que se quieren adquirir los locks cuando se aloca
el plan de aplicacion
- Resource release time
. COMMIT, por defecto
indica que se quieren liberar los locks en cada punto de
commit
. DEALLOCATE
indica que se quieren retener los locks hasta que termine
el programa
- Explain pa h selection
. YES
se pide al DB2 informacion acerca de las decisiones que
esta tomando en el establecimiento de caminos de acceso
a los datos
. NO
Si cambian las caracteristicas de la tabla o vista a la que accede
el programa, el plan de aplicacion ya no es valido, y el programa
no puede ser ejecutado. Cuando se intenta hacerlo, el DB2 automa-
ticamente hace un rebind intentando crear un nuevo plan.
INSTRUCCIONES S.Q.L.
____________________
INSTRUCCIONES BASICAS
______________________
Las instrucciones en lenguaje S.Q.L. (Structured Query Language) permi-
ten comunicar peticiones de acceso a datos al DB2. Es usado por el ad-
ministrador de la base de datos para crear y cambiar definiciones de
8
datos, por el administrador del sistema en cuanto a autorizaciones a
usuarios de los datos, y por el programador de aplicaciones para recu-
perar y actualizar datos.
La primera clausula de una instruccion SQL dice al DB2 que operacion se
quiere realizar.
Los nombres de variables van precedidos de ‘:’.
CLAUSULA WHERE
______________
Especifica una condicion de busqueda que, identificara la fila o filas
que se quieren recuperar, borrar o actualizar. La condicion de busqueda
puede estar formada por uno o varios predicados separados por AND y OR.
El DB2 primero evalua las clausulas NOT, luego AND y por ultimo OR. Se
puede cambiar el orden de evaluacion por medio de parentesis, que son
examinados primero.
Ejemplos:
. WHERE NOT WORKDEPT = ‘C01′
. WHERE EMPNO = :EMPID
. WHERE JOBCODE + EDUCLVL > 70
. WHERE SEX = ‘V’ AND (DEPT = ‘C1′ OR DEPT = ‘C2′)
SELECT INTO
___________
Funcion
_______
Recuperar una fila especifica.
Formato
_______
EXEC SQL
SELECT nombres_de_las_columnas que nos interesan
INTO nombres_de_vbles usadas para contener los datos recuperados
FROM nombre_de_la_tabla o vista que contiene los datos
WHERE condicion_de_busqueda
END-EXEC
Se pueden especificar hasta 300 columnas en la clausula SELECT. Si se
quieren recuperar todas las columnas, en el mismo orden en que aparecen
en la fila, se pondra un asterisco, en lugar del nombre de las columnas.
el valor de la primera columna especificada se guardara en la primera
9
variable especificada en la clausula INTO, el de la segunda en la se-
gunda, etc.
Si ninguna fila satisface la condicion, el DB2 devuelve un codigo de
NOT FOUND (SQLCODE=100).
Si la clausula WHERE permitiera recuperar los valores de las columnas
de dos o mas filas, DB2 devuelve un codigo de error en el SQLCODE y no
recupera nada.
Si varias filas cumplieran la condicion, se usara una instruccion
DECLARE CURSOR para seleccionar las filas, seguida de una instruccion
FETCH para mover los valores de las columnas a las variables una fila
cada vez.
UPDATE
______
Funcion
_______
Cambiar el valor de una o mas columnas en cada fila que satisface la
condicion de busqueda de la clausula WHERE. Tambien se puede usar para
borrar un valor de una fila , cambiando el valor de la columna a NULL.
Formato
_______
EXEC SQL
UPDATE nombre_de_la_tabla o vista
SET columnas que se quieren actualizar = nuevo valor
WHERE condicion_de_busqueda
END-EXEC
El nuevo valor especificado para una columna puede ser el nombre de
otra columna de la misma fila, una constante, una variable, una expre-
sion aritmetica, un valor nulo, etc.
Si se omite la clausula WHERE, el DB2 actualiza cada fila de la tabla
o vista con los valores dados.
DELETE
______
Funcion
_______
Quitar filas enteras de una tabla, no columnas especificas.
10
Formato
_______
EXEC SQL
DELETE
FROM nombre_de_la_tabla o vista
WHERE condicion_de_busqueda
END-EXEC
Si se omite la clausula WHERE, se borraran todas las filas de la ta-
bla o vista. Para borrar la definicion de una tabla, ademas de su
contenido, se usa la instruccion DROP.
INSERT
______
Funcion
_______
Añadir nuevas filas a una tabla o vista, se puede:
. Especificar los valores de las columnas que se quieren insertar
. Incluir una instruccion SELECT en la INSERT para decir al DB2 que
los datos para la nueva fila estan contenidas en otra tabla o vista
Formato
_______
EXEC SQL
INSERT
INTO nombre_de_la_tabla o vista (nombres de columnas)
VALUES (valor_de_cada_columna especificada en la clausula INTO)
END-EXEC
Si se especifican menos nombres de columnas de los que hay en la fila,
el DB2 les asigna valores por defecto.
Si se intenta insertar una fila que duplica otra existente en la tabla:
. Si la tabla tiene un indice unico, la fila no es insertada, y el
DB2 proporciona un SQLCODE -803
. Si la tabla no tiene un indice unico, la fila es insertada sin error
INSERCION A LA VEZ DE VARIAS FILAS DE UNA TABLA EN OTRA TABLA
_____________________________________________________________
Ejemplo:
Se crea una tabla llamada EMPTIME con las columnas EMPNUMBER,
PROYNUMBER, STARTDATE y ENDDATE, y se usa la INSERT para llenarla.
11
EXEC SQL
INSERT INTO USERA.EMPTIME
(EMPNUMBER, PROYNUMBER, STARTDATE, ENDDATE)
SELECT EMPNO, PROYNO, COMIENZO, FIN
FROM DSN.EMP
END-EXEC
En una SELECT embebida en una INSERT no se puede poner UNION ni ORDER BY.
El numero de columnas de la SELECT debe ser el mismo de la INSERT. Los
datos que se seleccionan deben ser compatibles con las columnas en las
que se van a insertar. Para las columnas que no se especifiquen se in-
sertaran los valores por defecto establecidos cuando se creo la tabla.
Cuando se inserta una fila en una vista, si esta no contiene todas las
columnas de la tabla base, el DB2 inserta en ellas valores por defecto.
FUNCIONES DEL CURSOR
____________________
El cursor permite a un programa recuperar un conjunto de filas, y luego
procesar una fila cada vez.
El DB2 construye una ‘tabla de resultados’ para guardar todas las filas
recuperadas al ejecutar una instruccion SELECT, y utiliza el cursor para
hacerlas disponibles al programa. Un cursor identifica la fila actual
de la tabla de resultados, que el programa puede recuperar secuencial-
mente hasta que alcanza el fin de los datos ( SQLCODE=100, NOT FOUND).
Un programa puede utilizar varios cursores, para cada uno de ellos se
utilizan las siguientes instrucciones:
DECLARE CURSOR
______________
&nbs