Manual Básico DB2

download Manual Básico DB2

of 44

Transcript of Manual Básico DB2

Manual Bsico DB2.txt

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 222222222

I INDICE ________ CEPTOS. Conceptos ................................................ Tablas ................................................... Tipos de datos que puede contener una columna de una tabla Indices .................................................. ANISMOS DE SEGURIDAD DE LOS DATOS. Vistas ................................................... Racf (Resource Access Control Facility) .................. Passwords de ficheros Vsam .............................. Autorizacion ............................................. ANISMOS DE INTEGRIDAD DE LOS DATOS. Concurrencia ............................................. Recuperacion de datos .................................... - Unidad de recuperacion ............................... Ayudas a la programacion ................................. - Spufi ................................................ - Paneles para preparacion de programas ................ - Paneles para mantenimiento de planes ................. - DCLGEN (Declarations Generator) ...................... - Online Help .......................................... RUCTURA DE UN PROGRAMA QUE OPERA EN UN ENTORNO CICS. Estructura de un programa que opera en un entorno cics ... Preparacion del programa para la ejecucion ............... - Traslacion ........................................... Pgina 1 5 5 5 3 3 3 4 4 4 4 4 5 2 3 3 3 1 1 2 2

Manual Bsico DB2.txt - Precompilacion ....................................... 6 - Compilacion y link-edicion ........................... 6 - Binding .............................................. 6 TRUCCIONES S.Q.L. Instrucciones basicas .................................... Clausula Where ........................................... Select into .............................................. Update ................................................... Delete ................................................... Insert ................................................... Insercion a la vez de varias filas de una tabla en otra .. Funciones del cursor ..................................... Declare cursor ........................................... Open ..................................................... Fetch .................................................... 7 8 8 9 9 10 10 11 11 12 12

II Whenever not found ....................................... Update ................................................... Delete ................................................... Close .................................................... Opciones de la instruccion select ........................ - Operaciones aritmeticas .............................. - Funciones Built-in ................................... - Avg .................................................. - Max .................................................. - Min .................................................. - Sum .................................................. - Count ................................................ - Clausulas Group by, Having y Order by ................ - Group by ............................................. - Having ............................................... - Order by ............................................. - Union ................................................ Seleccion de datos de varias tablas en una fila (join) ... Opciones de las condiciones de busqueda .................. - Between ... And ...................................... - In ................................................... - Like ................................................. - Is null .............................................. TAS. Concepto ................................................. Proceso de una vista ..................................... Subquery ................................................. Formas de incluir una subquery en una clausula where o having ................................................... - Inmediatamente despues de un operador de comparacion.. - Despues de un operador de comparacion seguido de all o any .................................................. - Despues de In, para indicar que el valor de la expresion debe estar entre los valores devueltos por la subquery .................................................. - Despues de Exists, verificacion de que puede ser encontrada una fila con la condicion de la subquery ...... Subquery correlativa ..................................... Otros ejemplos ........................................... - Con una SELECT ....................................... - Con una UPDATE ....................................... - Con una DELETE ....................................... FI. Crear y probar instrucciones SQL ......................... Creacion de una tabla .................................... Creacion de una vista .................................... Creacion de una tabla .................................... Crear un nombre alternativo o sinonimo para una tabla o vista y dar de baja ese sinonimo ......................... 27 28 28 28 28 22 23 24 24 24 25 25 26 26 26 26 27 27 13 13 14 14 15 15 16 16 16 16 16 16 17 17 18 18 19 20 21 21 21 21 22

III ELES. Panel Principal .......................................... - Especificar un fichero de entrada .................... - Especificar un fichero de salida ..................... - Especificar las opciones de proceso .................. Panel de opciones por defecto ............................ - Especificar otras opciones de proceso ................ - Definir caracteristicas del fichero de salida ........ - Especificar defectos para el formato de salida ....... Panel Commit/Rollback .................................... Restricciones ............................................ Pgina 2 28 28 28 29 29 29 29 30 30 30

Manual Bsico DB2.txt IFICACION DE INSTRUCCIONES SQL EN UN PROGRAMA COBOL. Definir un area de comunicacion llamada SQLCA ............ Describir cada tabla o vista a la que acceda el programa . Codificar instrucciones SQL .............................. - Variables y estructuras host ......................... - Variables indicador .................................. Manejo de codigos de retorno de errores : LA SQLCA ....... Manejo de condiciones excepcionales: Instruccion Whenever. - Tres condiciones que se pueden especificar ........... - Dos acciones que se pueden especificar ............... Sugerencias para la codificacion de instrucciones SQL .... - Facilitar el uso de indices por el DB2 ............... - Facilitar la seleccion de datos de dos o mas tablas .. Notas sobre DB2 / SQL .................................... NDICE I. Ejemplos de instrucciones sql en cobol II ................ - Distinct ............................................. - Count ................................................ - Sum .................................................. - Union ................................................ - Between (not between) ................................ - In (not in) .......................................... - Like (not like) ...................................... - Group by ............................................. - Max .................................................. - Min .................................................. - Having ............................................... OMENDACIONES PARA EL DISEO DE APLICACIONES EN DB2. Administracion de datos .................................. Administracion de base de datos .......................... Administracion del sistema DB2 ........................... Programador del sistema DB2 .............................. Operacion del sistema DB2 ................................ Analisis de aplicaciones ................................. Programacion de aplicaciones ............................. 42 42 43 43 44 44 45 37 37 38 38 39 40 40 40 40 41 41 41 31 31 32 32 33 33 35 36 36 36 36 36 37

IV Soporte de centro de informacion ......................... Representantes de los usuarios ........................... Macro actividades a desarrollar en un proyecto ........... - Planificacion ........................................ - Hw/sw instal. ........................................ - Diseo y desarrollo aplicacion ....................... - Operacion y recuperacion ............................. - Gestion Rdto. ........................................ NIFICACION. Gestion del proyecto ..................................... Seleccion de la aplicacion piloto ........................ Definicion de estandares ................................. Identificar los recursos a proteger ...................... EO Y DESARROLLO DE LA APLICACION. Establecer el entorno de desarrollo de la aplicacion ..... Analisis de aplicaciones ................................. Diseo de Base de Datos .................................. Desarrollo y prueba de programas ......................... Paso de desarrollo a produccion .......................... EO DE BASES DE DATOS. Introduccion ............................................. 51 EO LOGICO DE BASES DE DATOS. Analisis de datos ........................................ 51 Normalizacion ............................................ 52 Integridad referencial ................................... 53 EGRIDAD DE DATOS. Un adecuado diseo de base de datos ...................... - Analisis total de los datos .......................... - Normalizacion de los datos ........................... Un adecuado diseo de modulos, codificacion y pruebas .... DIMIENTO Y CONCURRENCIA EN BASE DE DATOS. Concurrencia ............................................. Trabajo realizado por el DB2 para el SQL ................. Diseo fisico de bases de datos .......................... Seleccion de indices ..................................... Pgina 3 55 55 56 60 53 53 53 54 48 49 49 49 50 47 47 48 48 45 46 46 46 46 46 47 47

Manual Bsico DB2.txt

1 CONCEPTOS _________ El DB2 es un sistema de manejo de bases de datos basado en un mode relacional de datos. Rueda bajo sistema operativo MVS/SP como un s sistema de este:

+-------------+ TERMINAL CICS/OS/VS +-------------+ +---------------------------------------------------------- v +------------+ MVS/SD CICS/OS/VS -------------+ Sistema Operativo +------------+ v v +------------+ +-------+ +-----------+ IMS/VS DB2 ENTRY CICS/OS/VS CICS/OS/VS v +-----------+ 70 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 recupera 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 quieren recuperar todas las columnas, en el mismo orden en la fila, se pondra un asterisco, en lugar del nombre el valor de la primera columna especificada se guardara SELECT. Si en que apa de las col en la prim

9 variable especificada en la clausula INTO, el de la segunda en la gunda, etc. Si ninguna fila satisface la condicion, el DB2 devuelve un codigo NOT FOUND (SQLCODE=100). Si la clausula WHERE permitiera recuperar los valores de las colum de dos o mas filas, DB2 devuelve un codigo de error en el SQLCODE recupera nada. Pgina 8

Manual Bsico DB2.txt Si varias filas cumplieran la condicion, se usara una instruccion DECLARE CURSOR para seleccionar las filas, seguida de una instrucc FETCH para mover los valores de las columnas a las variables una f cada vez.

UPDATE ______ Funcion _______ Cambiar el valor de una o mas columnas en cada fila que satisface condicion de busqueda de la clausula WHERE. Tambien se puede usar borrar un valor de una fila , cambiando el valor de la columna a N 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 d otra columna de la misma fila, una constante, una variable, una ex sion aritmetica, un valor nulo, etc. Si se omite la clausula WHERE, el DB2 actualiza cada fila de la ta 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 t bla o vista. Para borrar la definicion de una tabla, ademas de su contenido, se usa la instruccion DROP.

INSERT ______ Funcion _______ Aadir nuevas filas a una tabla o vista, se puede: . Especificar los valores de las columnas que se quieren insert . Incluir una instruccion SELECT en la INSERT para decir al DB2 los datos para la nueva fila estan contenidas en otra tabla o Formato _______ EXEC SQL INSERT INTO nombre_de_la_tabla o vista (nombres de columnas) VALUES (valor_de_cada_columna especificada en la clausula INT END-EXEC Si se especifican menos nombres de columnas de los que hay en la f el DB2 les asigna valores por defecto. Si se intenta insertar una fila que duplica otra existente en la t . Si la tabla tiene un indice unico, la fila no es insertada, y DB2 proporciona un SQLCODE -803 . Si la tabla no tiene un indice unico, la fila es insertada sin

INSERCION A LA VEZ DE VARIAS FILAS DE UNA TABLA EN OTRA TABLA Pgina 9

Manual Bsico DB2.txt _____________________________________________________________ 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 OR El numero de columnas de la SELECT debe ser el mismo de la INSERT. datos que se seleccionan deben ser compatibles con las columnas en que se van a insertar. Para las columnas que no se especifiquen se sertaran los valores por defecto establecidos cuando se creo la ta Cuando se inserta una fila en una vista, si esta no contiene todas columnas de la tabla base, el DB2 inserta en ellas valores por def

FUNCIONES DEL CURSOR ____________________ El cursor permite a un programa recuperar un conjunto de filas, y procesar una fila cada vez. El DB2 construye una 'tabla de resultados' para guardar todas las recuperadas al ejecutar una instruccion SELECT, y utiliza el curso hacerlas disponibles al programa. Un cursor identifica la fila act de la tabla de resultados, que el programa puede recuperar secuenc mente hasta que alcanza el fin de los datos ( SQLCODE=100, NOT FOU Un programa puede utilizar varios cursores, para cada uno de ellos utilizan las siguientes instrucciones:

DECLARE CURSOR ______________ Funcion _______ Se usa para definir e identificar un conjunto de filas que van a s accedidas con un cursor. Formato _______ EXEC SQL DECLARE nombre_del_cursor CURSOR FOR SELECT columna 1, columna 2 ..... FROM nombre_de_la_tabla WHERE columna 1 = condicion_de_busqueda FOR UPDATE OF columna 2 ...(columnas de cada fila que se quieren actualizar) END-EXEC

12 La instruccion DECLARE CURSOR nombra un cursor. La instruccion SEL define un conjunto de filas, que formaran la tabla de resultados. Es posible actualizar una columna de la tabla identificada aunque sea parte de la tabla de resultados (que no haya sido nombrada en instruccion SELECT), nombrandola en la clausula FOR UPDATE OF. Cuando DB2 evalua una instruccion SELECT, puede ocurrir que varias satisfagan la condicion de busqueda, y que algunas de ellas esten dadas. Para especificar que no se desean, se codificara: SELECT DISTINCT columna 1, columna 2, .....

OPEN ____ Funcion _______ Le dice al DB2 que se esta preparado para procesar la primera fila la tabla de resultados. Formato _______ Pgina 10

Manual Bsico DB2.txt EXEC SQL OPEN END-EXEC nombre_del_cursor

FETCH _____ Funcion _______ Mover a las variables del programa el contenido de la fila selecci Formato _______ EXEC SQL FETCH INTO END-EXEC nombre_del_cursor variable 1, variable 2 ....

Cuando se utiliza la instruccion FETCH, el DB2 usa el cursor para tar a la siquiente fila de la tabla de resultados.

13 WHENEVER NOT FOUND __________________ Funcion _______ Para detectar que ya no tenemos mas filas para procesar se puede: . Examinar si el SQLCODE tiene valor 100, esto ocurre cuando una truccion FETCH ha recuperado la ultima fila de la tabla de res dos y se da otra FETCH. Ejemplo: IF SQLCODE = 100 GO TO NO-DAT . Codificar la instruccion WHENEVER NOT FOUND bifurcando a otra del programa. Formato _______ EXEC SQL WHENEVER NOT FOUND GO TO END-EXEC direccion_simbolica

UPDATE ______ Funcion _______ Una vez que se ha recuperado la fila actual, se puede actualizar e con UPDATE. Formato _______ EXEC SQL UPDATE nombre_de_la_tabla SET columna 1 = valor , columna 2 = valor .... WHERE CURRENT OF nombre_del_cursor END-EXEC Cada columna que se quiere actualizar debe haber sido nombrada pre mente en la clausula FOR UPDATE OF de la instruccion DECLARE CURSO La clausula WHERE identifica el cursor que apunta a la fila que va ser actualizada. Despues de actualizar una fila, la posicion del c permanece en esa fila hasta que se utilice una instruccion FETCH p la siguiente fila. Con la instruccion UPDATE se actualizan varias filas con una sola truccion SQL, mientras que UPDATE ... WHERE CURRENT OF, permite ob una copia de la fila, examinarla, y entonces, actualizarla.

14 DELETE ______ Funcion _______ Pgina 11

Manual Bsico DB2.txt Una vez que se ha recuperado la fila actual, se puede borrar con D Formato _______ EXEC SQL DELETE FROM WHERE CURRENT OF END-EXEC nombre_de_la_tabla nombre_del_cursor

La clausula WHERE identifica el cursor que apunta a la fila que va borrada. Despues de borrar una fila, no se puede actualizar o borr utilizando ese cursor, hasta que se de una FETCH para la siguiente Con la instruccion DELETE se borran varias filas con una sola inst ccion SQL, mientras que DELETE ... WHERE CURRENT OF, permite obten una copia de la fila, examinarla, y entonces, borrarla.

CLOSE _____ Funcion _______ Cerrar el cursor una vez que se ha terminado de procesar las filas la tabla de resultados si se quiere volver a utilizarlo. El DB2 ci el cursor automaticamente cuando termina el programa. Formato _______ EXEC SQL CLOSE END-EXEC nombre_del_cursor

Si el programa completa una unidad de recuperacion todos los curso abiertos son cerrados automaticamente por el DB2. Si el cursor se ve a abrir, el proceso comenzara al principio de la tabla de resul y se tendra que restablecer la posicion del cursor. Ejemplo: Supongamos que se quieren actualizar los datos de los empleados de tabla DSN.EMPL dando un SYNCPOINT cada 100 filas.

15 1. Declarar el cursor como conjunto de filas de EMPNO > UPD-EMP EXEC SQL DECLARE EMPLEADO CURSOR FOR SELECT EMPNO, SALARY FROM DSN.EMPL WHERE EMPNO > :UPD-EMP ORDER BY EMPNO END-EXEC (valor actual del limite mas bajo)

2. Abrir el cursor y mover los datos de la tabla de resultados fila cada vez, de forma que el limite mas bajo de la tabla s tualiza en UPD-EMP. EXEC SQL OPEN EMPLEADO END-EXEC : : : : EXEC SQL FETCH INTO END-EXEC EMPLEADO :UPD-EMP, :UPD-SAL

3. Borrar una fila EXEC SQL DELETE FROM DSN.EMPL WHERE EMNO = :UPD-EMP END-EXEC 4. Al abrir el cursor de nuevo, la condicion de busqueda de la procesara la DSN.EMPL basandose en el valor actual de UPD-EM

OPCIONES DE LA INSTRUCCION SELECT _________________________________

OPERACIONES ARITMETICAS _______________________ La instruccion SELECT se puede usar para realizar operaciones arit cas con los datos antes de que sean proporcionados al programa. Es consigue mediante operadores aritmeticos en la lista de columnas. SELECT JOBCODE + 10 . . . Los operadores aritmeticos se pueden usar con columnas de datos de Pgina 12

Manual Bsico DB2.txt dos como INTEGER, SMALLINT, DECIMAL, o FLOAT, con constantes numer y variables. El valor de la expresion aritmetica se asigna a una variable, pero reemplaza al valor de la columna de la tabla o vista.

16 FUNCIONES BUILT-IN __________________ Permiten obtener informacion acerca de las filas que satisfacen la dicion de busqueda. Se puede incluir mas de una en cada instruccio LECT. La funcion BUILT-IN se aplica a cada fila que satisface la c cion de busqueda, ignorando los valores nulos.

AVG ___ Promedio de los valores de una o mas columnas que contengan datos ricos.

MAX ___ Maximo valor de una o mas columnas.

MIN ___ Minimo valor de una o mas columnas.

SUM ___ Suma del valor de una o mas columnas.

COUNT _____ . Numero de filas que satisfacen la condicion de busqueda: EXEC SQL SELECT COUNT(*) INTO EMP-COUNT FROM DSN-EMP WHERE JOBCODE >= 50 END-EXEC

17 . Numero de valores unicos de una columna en particular: EXEC SQL SELECT COUNT (DISTINCT DEPTNO) INTO DST-COUNT FROM DSN-EMP END-EXEC

CLAUSULAS GROUP BY, HAVING Y ORDER BY _____________________________________ No se pueden especificar en una instruccion SELECT dentro de la DE CURSOR, cuando se pretenda actualizar una columna o borrar una fil

GROUP BY ________ Divide las filas que satisfacen la condicion de busqueda en grupos gun una o mas columnas especificadas en esta clausula. El resultad la aplicacion de una funcion BUILT-IN, no sera ya un valor unico, Pgina 13

Manual Bsico DB2.txt tantos valores como grupos. Si hay valores nulos en una columna es ficada en GROUP BY, el DB2 los considera en un grupo por separado. Ejemplo: Calculo del salario medio por departamento el resultado sera una por departamento al no especificar WHERE, se procesan todas las fi EXEC SQL DECLARE XYZ CURSOR FOR SELECT WORKDEPT, AVG(SALARY) FROM DSN-EMP GROUP BY WORKDEPT END-EXEC ....................................... EXEC SQL FETCH XYZ INTO :WORK-DEPT, :AVG-SALARY END-EXEC Es posible agrupar por mas de una columna. Ejemplo: Calculo del salario medio por departamento y por sexo. El resultad seran dos filas por departamento (una por cada sexo), para las cua se calculara el promedio.

18 EXEC SQL DECLARE XYZ CURSOR FOR SELECT WORKDEPT, SEX, AVG(SALARY) FROM DSN-EMP GROUP BY WORKDEPT, SEX END-EXEC ....................................... EXEC SQL FETCH XYZ INTO :WORK-DEPT, :SEX, :AVG-SALARY END-EXEC

HAVING ______ Se usa seguida de la clausula GROUP BY, para especificar que solo desean los grupos que satisfagan una condicion. Se codifica como la clausula WHERE, pudiendo contener predicados m ples, la clausula DISTINCT, HAVING NOT, y funciones BUILT-IN. Ejemplo: Calculo del salario medio de las mujeres por departamento, pero so para los departamentos en los que todos los empleados poseen un ni de educacion igual o superior a 16. EXEC SQL DECLARE XYZ CURSOR FOR SELECT WORKDEPT, AVG(SALARY), MIN(EDUCL FROM DSN-EMP WHERE SEX = 'F' GROUP BY WORKDEPT HAVING MIN(EDUCLVL) >= 16 END-EXEC ....................................... EXEC SQL FETCH XYZ INTO :WORK-DEPT, :AVG-SALARY, :MIN-EDUC END-EXEC

ORDER BY ________ Indica el orden en el que se quieren recuperar las filas, el cual especifica mediante el nombre de la columna o columnas, o un numer (ORDER BY 3 especifica que se quiere ordenar por la tercera column de la tabla de resultados) cuando en la SELECT no se ha especifica un nombre de columna sino una expresion aritmetida, funcion BUILTetc.

19 Todas las columnas de la claurula ORDER BY se han de haber especif en la SELECT. Por defecto se toma orden ascendente. Si se encuentran valores nul Pgina 14

Manual Bsico DB2.txt son tratados como los mas altos. Los caracteres graficos se ordena en secuencia EBCDIC. Se pueden especificar secuencias de orden secundarias. Ejemplo: Recuperar el nombre y numero de departamento de las mujeres emplea ordenado por numero de departamento descendente, y dentro de c departamento por nombre de empleado. EXEC SQL DECLARE XYZ CURSOR FOR SELECT EMPNAME, WORKDEPT FROM DSN-EMP WHERE SEX = 'F' ORDER BY WORKDEPT DESC, EMPNAME END-EXEC ....................................... EXEC SQL FETCH XYZ INTO :PGM-NAME, :WORK-DEPT END-EXEC

UNION _____ Permite combinar dos o mas instrucciones SELECT para formar una un tabla de resultados, eliminando las filas duplicadas. Se usa para clar valores de varias tablas, pero el conjunto de filas seleccion de una tabla es aadido al conjunto de filas seleccionado de otra. Los tipos de datos y longitudes de las columnas nombradas en la in ccion SELECT deben ser identicos. Por ejemplo, SELECT A, B UNION X la columna A debe ser identica a la X, y la columna B a la Y. Para especificar las columnas por las que se quiere ordenar se deb usar numeros, y nunca nombres de columnas. Para identificar de que instruccion SELECT procede cada fila, se i ye una constante al final de la lista de cada SELECT. SELECT A, B, 'A1' UNION X, Y, 'B1' El DB2 devolvera la constante correspondiente a cada instruccion S en la ultima columna de la fila.

20 Ejemplo: EXEC SQL DECLARE XYZ CURSOR FOR SELECT EMPNO FROM DSN.EMP1 WHERE WORKDEPT = :WORK-DEPT UNION SELECT DISTINCT EMPNO FROM DSN-EMP2 WHERE PROJNO = :NUM-PROY END-EXEC ....................................... EXEC SQL FETCH XYZ INTO :EMP-NUMBER END-EXEC

SELECCION DE DATOS DE VARIAS TABLAS EN UNA FILA (JOIN) ______________________________________________________ Es posible formar una fila de la tabla de resultados para la cual parte de las columnas procedieran de una tabla, y otra parte de ot Si coinciden los nombres de columna de dos tablas, habra que calif los, poniendo como prefijo el nombre de la tabla. En la instruccion SELECT se listan los nombres de las columnas que quieren. Si en su lugar se coloca un *, el DB2 devuelve una fila f da por todas las columnas de la primera fila, unido a todas las de segunda. Si no se especifica la clausula WHERE cada fila de la primera tabl concatenada a cada fila de la segunda tabla para formar una tabla resultados, cuyo numero de filas sera el producto del numero de fi de cada tabla. Ejemplo: A=3 B=2 AB=6 Este metodo se puede usar para crear una vista, pero esta no puede Pgina 15

Manual Bsico DB2.txt procesada mediante instrucciones UPDATE, DELETE o INSERT. Es posible unir una tabla o vista consigo misma, y hasta 7 tablas. Ei se usa la clausula GROUP BY en la definicion de una vista, la v no puede ser unida con otra tabla. Ejemplo: Queremos recuperar las columnas EMPNO y LASTNAME de la tabla DSN.E y las columnas ACTNUM y EMPTIME de la DSN.EMP2.

21 EXEC SQL DECLARE XYZ CURSOR FOR SELECT EMP1.EMPNO, LASTNAME, ACTNUM, EMPTI FROM DSN.EMP1, DSN-EMP2 WHERE EMP1.EMPNO = EMP2.EMPNO END-EXEC ....................................... EXEC SQL FETCH XYZ INTO :EMP-NUMBER, :NAME, :ACTIV, :TIME END-EXEC

OPCIONES DE LAS CONDICIONES DE BUSQUEDA _______________________________________ Todas ellas se pueden codificar con NOT.

BETWEEN ... AND _______________ Especifica que la condicion debe ser satisfecha por cualquier valo este comprendido entre los dos valores especificados. WHERE EDUCLVL BETWEEN 13 AND 16

IN __ Indica que se esta interesado en las filas para las que el valor d columna especificada esta entre los valores que se listan. WHERE WORKDEPT IN ('A00', 'C01', 'E02')

LIKE ____ Indica que se esta interesado en las filas para las que el valor d columna especificada es similar al que se proporciona. El grado de recido es determinado por dos caracteres especiales usados en la c que se incluye en la condicion de busqueda:

22 . Un '_' para cualquier caracter simple . Un '%' para una cadena de caracteres. A no ser que el % prece la cadena de caracteres, la cadena buscada puede comenzar en quier parte de la columna Se usan cuando no se conocen todos los caracteres de una columna o importa su contenido. Ejemplo: . Para encontrar todos los empleados que viven en San Jose, debemo tar seguros de que San Jose no es parte del nombre de otra ciuda porque el DB2 nos devolveria tambien esa fila. WHERE TOWN LIKE '%SAN JOSE%' . Para encontrar todos los empleados que viven en ciudades que com den por SAN. WHERE TOWN LIKE 'SAN%'

Pgina 16

Manual Bsico DB2.txt IS NULL _______ Indica que se esta interesado en las filas para las que el valor d columna especificada es nulo. WHERE CODIGO IS NULL Para indicar que se esta interesado en las filas para las que el v de la columna especificada es blancos. WHERE CODIGO = ' '

VISTAS ______

CONCEPTO ________ Una vista no contiene datos. Es una definicion almacenada de un co to de filas y columnas. Cuando un programa accede a los datos defi por una vista, el DB2 procesa la definicion de la vista. El result es un conjunto de filas a las que el programa puede acceder median instrucciones SQL.

23 Una vista es como una ventana a traves de la cual acceder solo a los datos que necesita. Las vistas cambiablemente con las tablas cuando se recuperan varse de una o mas tablas. Puede tener nombres de de los nombres de las tablas. un programa pued se pueden usar i datos, y pueden columnas diferen

Las funciones de una vista son: - Seguridad: No permitir el acceso de algunos usuarios a todos lo datos de una tabla. - Hacer disponible a una aplicacion un subconjunto de una tabla, lo cual es mas facil codificar instrucciones SQL ya que solo se las columnas y filas de una tabla que se necesitan. Una vista basada en una tabla de empleados puede contener filas para un determinado departamento. - Resumir datos de una tabla y hacerlos disponibles: . . . . La suma de valores de una columna El maximo valor de una columna El promedio de valores de una columna El resultado de una expresion aritmetica con una o m columnas

Cuando se actualizan los datos de una vista, se actualizan los dat de la tabla de la que se deriva, y, al contrario, si los datos de tabla cambian, los datos accesibles a traves de vistas basadas en tabla tambien cambian.

PROCESO DE UNA VISTA ____________________ Una vista se puede crear via SPUFI. Para insertar una fila en una tabla por medio de una vista, la fil debe tener un valor para cada columna de la tabla que no posee un valor por defecto. Si se crea una vista usando funciones BUILT-IN o las clausulas DIS o GROUP BY, solamente es posible usar la instruccion SELECT para e Si una columna de una vista es definida por medio de una expresion metica, no se podra actualizar la columna o insertar filas en la v Si se crea una vista especificando 'WITH CHECK OPTION', todas las ciones y actualizaciones contra la vista son chequeadas antes para probar que la fila esta conforme con la definicion de la vista. Si la vista es resultado de un JOIN, los datos de esta pueden ser seleccionados, pero no se puede insertar, borrar o actualizar.

24 No se puede crear un indice para una vista. Pgina 17

Manual Bsico DB2.txt Si la instruccion SELECT usada para crear una vista contiene un * lugar de una lista de columnas, y mas tarde alguien aade otra col a la tabla en la cual se basa la vista, la nueva columna no aparec en la vista a menos que la vista sea creada de nuevo.

SUBQUERY ________ En una SELECT anidada en otra SELECT la primera OUTER-LEVEL, y la interna SUBQUERY. se llama SELECT

Cuando se usa una SUBQUERY el DB2 la evalua, y sustituye el valor resulta de ella en la clausula WHERE o HAVING de nivel superior, y puede existir una SUBQUERY dentro de otra, hasta cinco, e incluso, de haber varias condiciones de busqueda conectadas por operadores cionales, cada una con SUBQUERYS. La instruccion SELECT de una SUBQUERY solo puede especificar 1 col 1 funcion BUILT-IN o 1 expresion aritmetica. Una SUBQUERY no puede contener una clausula ORDER BY. Cuando la instruccion de nivel superior es otra SELECT: . La SUBQUERY puede basarse en la misma tabla o vista o en otra . Se puede usar una SUBQUERY en una instruccion WHERE incluso s SELECT es parte de una DECLARE CURSOR, INSERT o CREATE VIEW. Cuando la instruccion de nivel superior es UPDATE o DELETE la SUBQ no puede basarse en la misma tabla o vista.

FORMAS DE INCLUIR UNA SUBQUERY EN UNA CLAUSULA WHERE O HAVING _____________________________________________________________

1) Inmediatamente despues de un operador de comparacion ____________________________________________________ EXEC SQL DECLARE XYZ CURSOR FOR SELECT EMPNO, LASTNAME, JOBCODE FROM DSN.EMP1 WHERE EDUCLEVEL > (SELECT AVG (EDUCLEVEL) FROM DSN.EMP2 END-EXEC

25 ....................................... EXEC SQL FETCH XYZ INTO :EMP-NUMBER, :NAME, :CODE END-EXEC

2) Despues de un operador de comparacion seguido de: ________________________________________________ . ALL, para indicar que el valor proporcionado debe ser compara una determinada forma con todos los valores que devuelva la S RY. Ej: WHERE EXPRESION > ALL SUBQUERY para satisfacer la WHERE, el valor de la expresion de ser mayor que todos los valores devueltos por la SUBQ Si la SUBQUERY no selecciona ningun valor la condicio satisfecha. . ANY, para indicar que el valor proporcionado debe ser compara una determinada forma con cualquiera de los valores que devue la SUBQUERY. Ej: WHERE EXPRESION > ANY SUBQUERY

3) Despues de IN, para indicar que el valor de la expresion debe e _______________________________________________________________ entre los valores devueltos por la SUBQUERY. ___________________________________________ Ejemplo: EXEC SQL DECLARE XYZ CURSOR FOR Pgina 18

Manual Bsico DB2.txt SELECT EMPNO, LASTNAME, JOBCODE FROM DSN.EMP1 WHERE EMPNO IN (SELECT DISTINCT EMPNO FROM DSN.EMP2 WHERE PROYNO = 'M1') END-EXEC ....................................... EXEC SQL FETCH XYZ INTO :EMP-NUMBER, :NAME, :CODE END-EXEC

26 4) Despues de EXISTS para verificar que al menos puede ser encontr _______________________________________________________________ una fila que cumpla la condicion de la SUBQUERY. _______________________________________________ Ejemplo: WHERE EXISTS SUBQUERY No es necesario especificar nombres de columna en la SELECT, sin ya que la SUBQUERY no va a devolver datos.

SUBQUERY CORRELATIVA ____________________ En una SUBQUERY normal, el DB2 ejecuta la SUBQUERY una vez, sustit el resultado en la parte derecha de la condicion de busqueda, y ev la SELECT de nivel superior segun el valor de la condicion de busq Una SUBQUERY correlativa se ejecuta una vez para cada fila de la t o vista mencionada en la SELECT de nivel superior, de forma que se lua la SUBQUERY para cada fila. Se usa para calcular algun valor que puede ser diferente para cada Es igual que una normal excepto porque despues de especificar el n de la tabla en la clausula FROM, proporcionamos un nombre de corre cion, variable que representa la fila actual. Para especificar la lacion se prefija el nombre de columna con un nombre de correlacio

OTROS EJEMPLOS ______________ Con UPDATE o SELECT, la SUBQUERY y la instruccion de nivel super deben referirse a diferente tabla. En una INSERT, ni la SUBQUERY ni la SELECT de nivel superior den de la INSERT, pueden basarse en la misma tabla en la que se inse

. Con una SELECT ______________ Esta SELECT recuperara las columnas especificadas para cada empl cuyo JOBCODE es superior a la media de su departamento.

27 EXEC SQL DECLARE XYZ CURSOR FOR SELECT EMPNO, LASTNAME, WORKDEPT, JOBCODE FROM DSN.EMP ESTAFILA WHERE JOBCODE > (SELECT AVG (JOBCODE) FROM DSN.EMP WHERE WORKDEPT = ESTAFILA.WORKDE END-EXEC ....................................... EXEC SQL FETCH XYZ INTO :EMP-NUMBER, :NAME, :DEPT, :CODE END-EXEC

. Con una UPDATE ______________ EXEC SQL Pgina 19

Manual Bsico DB2.txt UPDATE DSN.EMP1 ESTAFILA SET DATE = 9999 WHERE 840901 > (SELECT MAX (ENDATE) FROM DSN.EMP2 WHERE PROJNO = ESTAFILA.PROJNO) END-EXEC

. Con una DELETE ______________ EXEC SQL DELETE FROM DSN.EMP1 ESTAFILA WHERE NON EXISTS (SELECT * FROM DSN.EMP2 WHERE PROJNO = ESTAFILA.PROJNO) END-EXEC

SPUFI _____ SPUFI (SQL PROCESSOR USING FILE INPUT) permite: - Crear y probar instrucciones SQL antes de incluirlas en un progr ________________________________

28 - Creacion de una tabla _____________________ CREATE TABLE TEMPL (EMPNO CHAR(6) NOT NULL, WORKDEPT CHAR(3) NOT NULL, JOBCODE DECIMAL(3) ) IN TESTTAB.DSN77EMP; - Creacion de una vista _____________________ La instruccion CREATE VIEW se usa para establecer la definicion vista. La vista se crea cada vez que es referenciada por una ins ccion SQL. CREATE VIEW EMPDATA AS SELECT * FROM DSN.EMP WHERE WORKDEPT = 'C1' - Crear un nombre alternativo o sinonimo para una tabla o vista _____________________________________________________________ CREATE SYNONIM MITABLA FOR DSN.TABEMPL y dar de baja ese sinonimo. ___________________________ DROP SYNONIM MITABLA

PANELES _______ 1) Panel Principal _______________ . Especificar un fichero de entrada, _________________________________ que contiene las instrucc SQL que se quieren ejecutar, cuya longitud de registro sera . Especificar un fichero de salida _________________________________, que recoge cada instrucci el resultado de la ejecucion, que seran los datos recuperado una SELECT, o un SQLCODE en otras. Tambien incluye estadisti

29 . Especificar las opciones de proceso Pgina 20

Manual Bsico DB2.txt ___________________________________ (YES por defecto) - CHANGE DEFAULTS: Poniendo YES se accede al panel de opcio por defecto. - EDIT INPUT: Editar o no el fichero de entrada. - EXECUTE: Ejecutar o no las instrucciones del fichero de e - AUTOCOMMIT: Dar o no un punto de COMMIT. - BROWSE OUTPUT: Displayar o no el fichero de salida. 2) Panel de opciones por defecto. _____________________________ . Especificar otras opciones de proceso _____________________________________ - ISOLATION LEVEL: Por defecto RR. RR (REPEATABLE READ), garantiza que los valores de la de datos leidos o cambiados por SPUFI no pueden se biados por otro programa hasta que SPUFI alcance u to de COMMIT. CS (CURSOR STABILITY), garantiza que ningun programa p modificar los datos de una fila en la cual SPUFI t posicionado un cursor. Esto proporciona maxima con rrencia. - MAX SELECT LINES: Por defecto 250. Maximo numero de filas que devolvera una SELECT. . Definir caracteristicas del fichero de salida _____________________________________________ - RECORD LENGTH: Por defecto 4092. No puede ser menor que la del fichero de entrada. - BLOCK SIZE: Por defecto 4096. - RECORD FORMAT: Por defecto VB. - DEVICE TYPE: Por defecto SYSDA.

30 . Especificar defectos para el formato de salida ______________________________________________ - MAX NUMERIC FIELD: Por defecto 20. Anchura maxima para campos numericos. - MAX CHAR FIELD: Por defecto 80. Anchura maxima para campos de caracteres. - COLUMN HEADING: Por defecto NAMES. NAMES LABELS ANY BOTH

3) PANEL COMMIT/ROLLBACK _____________________ Si en el panel principal se especifica NO para AUTOCOMMIT, una que se completa la ejecucion se displaya este panel, en el que indicara una de estas opciones: . COMMIT: Salvar los cambios en la base de datos. . ROLLBACK : Borrar los cambios en la base de datos. . DEFER

RESTRICCIONES _____________ Se pueden usar todas las instrucciones excepto: CLOSE EXECUTE OPEN DECLARE FETCH PREPARE DESCRIBE INCLUDE WHENEVER Pgina 21

Manual Bsico DB2.txt Las opciones no soportadas por spufi son: . INTO en la SELECT . WHERE CURRENT en la UPDATE y DELETE

31 CODIFICACION DE INSTRUCCIONES SQL EN UN PROGRAMA COBOL ______________________________________________________ 1) Definir un area de comunicacion llamada SQLCA _____________________________________________ Es un area de datos en la cual el DB2 devuelve informacion acer del resultado de la ejecucion de cada instruccion SQL. WORKING-STORAGE SECTION EXEC SQL INCLUDE SQLCA END-EXEC Con esta instruccion el precompilador incluira: 01 SQLCA. 05 SQLCAID PIC X(8). 05 SQLCABC PIC S9(9) COMP. 05 SQLCODE PIC S9(9) COMP. 05 SQLERRM. 49 SQLERRML PIC S9(4) COMP. 49 SQLERRMC PIC X(70). 05 SQLERRP PIC X(8). 05 SQLERRD OCCURS 6 TIMES PIC S9(9) COMP. 05 SQLWARN. 10 SQLWARN0 PIC X(1). 10 SQLWARN1 PIC X(1). 10 SQLWARN2 PIC X(1). 10 SQLWARN3 PIC X(1). 10 SQLWARN4 PIC X(1). 10 SQLWARN5 PIC X(1). 10 SQLWARN6 PIC X(1). 10 SQLWARN7 PIC X(1). 05 SQLEXT PIC X(8).

2) Describir cada tabla o vista a la que acceda el programa ________________________________________________________ Esto puede hacerse de dos formas: . Codificando una instruccion DECLARE en la data division, espe cando nombre de la tabla y listando cada columna y su tipo de EXEC SQL DECLARE DSN.TDEPT TABLE (DEPNUM CHAR(3) NOT NULL, DEPNOM VARCHAR(36) NOT NULL) END-EXEC

32 . Previamente a la precompilacion con el DCLGEN. Para incluir en el programa las declaraciones producidas por EXEC SQL INCLUDE nombre_del_miembro END-EXEC

3) Codificar instrucciones SQL ___________________________ Cada instruccion comienza con EXEC SQL y termina con END-EXEC. En lugar de instrucciones COPY se codificara INCLUDE. No se pueden usar nombres de variables que comiencen por SQL, n nombres de entradas externas o de plan que comiencen por DSN, y que estan reservados para el DB2. No se pueden usar constantes figurativas como ZERO o SPACE en i cciones SQL.

VARIABLES Y ESTRUCTURAS HOST ____________________________ Pgina 22

Manual Bsico DB2.txt Las variables HOST son campos de datos, definidos en el program la WORKING STORAGE o en la LINKAGE SECTION, y especificados en opcion INTO de una instruccion SELECT o FETCH, en los cuales se los valores de los datos recuperados por el DB2. Las variables se codifican en las instrucciones SQL precedidas por dos puntos Una estructura HOST es un conjunto de variables HOST definidas DATA DIVISION del programa. Puede tener un maximo de dos nivele cepto la declaracion de una cadena de caracteres de longitud va que requiere un numero de nivel 49. Para identificar un campo d de una estructura en una instruccion SQL se codificara el nombr estructura seguido de un punto y el nombre del campo. La parte entera de un numero nunca es truncada, si un numero no en una variable o en una columna, se trunca la parte decimal si ne y si no da un error. La declaracion de variables HOST se hace en niveles 01 o 77 (la nivel 77 no pueden ser usadas en instrucciones SQL). Una cadena de caracteres de longitud variable debe tener un niv 01 a 48, y contener dos campos elementales a nivel 49: . Campo de longitud PIC S9(4) COMP. . Campo de valor PIC X(80). (Como maximo).

33 VARIABLES INDICADOR ___________________ Una variable indicador es un entero de media palabra. La variable indicador se especifica precedida de dos puntos inm tamente despues de la variable HOST. Ejemplo: EXEC SQL SELECT TELFNUM INTO :TELEF:INDNULL FROM DSN.TABEMPL WHERE EMPNO = :EMPID END-EXEC Se usa para:

. Indicar si ha sido asignado un valor nulo a su variable asociad _______________________________________________________________ Si el valor de la columna que se esta recuperando es nulo el DB ne un valor negativo en la variable indicador ( si no se usara daria un error), y el valor de la variable HOST no se alterara. . Verificar que una cadena de caracteres recuperada no ha sido tr _______________________________________________________________ Si la variable indicador contiene un entero positivo, este espe la longitud original de la cadena. . Colocar un valor nulo en una columna ____________________________________ Cuando se procesa una instruccion UPDATE el DB2 chequea la vari indicador. Si esta contiene un valor negativo, el valor de la c na se pondra como nulo. Si contiene un valor mayor que -1 la va ble asociada contiene un valor para la columna.

4) MANEJO DE CODIGOS DE RETORNO DE ERRORES : LA SQLCA __________________________________________________

SQLCAID _______ Cadena de caracteres (long 8) que identifica la SQLCA (valor 'S

34 SQLCABC _______ Entero de una palabra que especifica la longitud de la SQLCA. S lor es siempre 136 (X'88'). Pgina 23

Manual Bsico DB2.txt

SQLCODE _______ Es un entero de una palabra. Puede tener los siguientes valores ZERO la instruccion se ha ejecutado correctamente -n ha ocurrido un error +n la instruccion se ha ejecutado correctamente pero ha ocurrido una condicion excepcional +100 no existen datos para procesar

SQLERRM _______ Cadena de caracteres de longitud variable (maximo 70) que descr una condicion de error.

SQLERRD _______ SQLERRD(3) indica el numero de filas insertadas, actualizadas o borradas en una tabla.

SQLWARN0 ________ Si es blanco, el resto de las variables SQLWARN tambien lo es. otra forma sera W, y al menos otra SQLWARN sera tambien W.

35 SQLWARN1 ________ Si es W al menos el valor de una columna ha sido truncado al al narlo en una variable HOST.

SQLWARN2 ________ Si es W al menos un valor nulo ha sido eliminado de el conjunto argumentos de una funcion.

SQLWARN3 ________ Si es W el numero de variables HOST especificado en una instruc SQL es distINTO del numero de columnas en la tabla o vista.

SQLWARN4 ________ Si es W una UPDATE o DELETE dinamica no incluye una clausula WH

5) MANEJO DE CONDICIONES EXCEPCIONALES: INSTRUCCION WHENEVER _________________________________________________________ Hace que el DB2 chequee la SQLCA y continue procesando el progr o bifurque a otra area del programa si encuentra un error. Pued tablecerse mas de una WHENEVER para una misma condicion a lo la del programa, de forma que una WHENEVER afecta a todas las inst cciones SQL siguientes hasta que se encuentre otra. Ejemplo: EXEC SQL WHENEVER CONDICION ACCION END-EXEC

Pgina 24

Manual Bsico DB2.txt

36 Se pueden especificar tres condiciones: ______________________________________ . SQLWARNING indica que se quiere hacer cuando SQLWARN0 = W o SQ contiene un valor positivo distinto de 100. . SQLERROR indica que se quiere hacer cuando SQLCODE < 0 . NOT FOUND indica que se quiere hacer cuando el DB2 no puede en trar una fila que satisfaga la instruccion SQL o cuando no hay filas para hacer una fetch (SQLCODE=100)

Se pueden especificar dos acciones: __________________________________ . CONTINUE el programa continua su ejecucion. . GO TO el programa bifurca a otra area del programa, cuyo nom bre ira precedido de dos puntos.

SUGERENCIAS PARA LA CODIFICACION DE INSTRUCCIONES SQL _____________________________________________________ Para facilitar el uso de indices por el DB2 ___________________________________________ . Usar BETWEEN en lugar de >= AND