Preparación para el examen 730 Fundamentos DB2 9, Parte 5: Trabajando con objetos DB2

Este tutorial trata sobre tipos de datos, tablas, vistas e índices según lo definido por DB2 9. Explica las características de estos objetos, cómo crearlos y manipularlos usando Structured Query Language (SQL), y cómo pueden ser usados en una aplicación. Este tutorial es el quinto de una serie de siete tutoriales que usted puede utilizar para prepararse para el Examen de Certificación 730 en Fundamentos DB2 9.

Hana Curtis, IBM Certified Solutions Expert, IBM

Hana Curtis es miembro líder del equipo DB2 Functional Verification Testing en el Laboratorio de Software de IBM Toronto. Anteriormente ella fue miembro del equipo de Integración DB2, trabajando con DB2 y WebSphere, consultora de base de datos que trabajó con los Asociados de Negocios IBM para activar sus primeras aplicaciones en DB2, y miembro del equipo de desarrollo DB2. Hana es una de las autoras del libro: DB2 SQL Procedural Language for Linux, UNIX, and Windows (Prentice Hall, 2003). Ella cuanta con las siguientes certificaciones: IBM Certified Solutions Expert: DB2 UDB V8.1 Database Administration for UNIX, Windows, and OS/2, IBM Certified Solutions Expert: DB2 UDB V8.1 Family Application Development, IBM Certified Specialist: DB2 V8.1 User.



13-02-2012

Antes de comenzar

Sobre esta serie

¿Está pensando en obtener la certificación en fundamentos DB2 (Examen 730)? Si es así, ha llegado al lugar correcto. Esta serie de siete tutoriales de preparación para la certificación DB2 cubre todo lo básico (los temas que necesitará entender antes de leer la primera pregunta del examen). Incluso si usted no está pensando en obtener la certificación justo ahora, este conjunto de tutoriales es un excelente lugar para comenzar a saber qué hay de nuevo en DB2 9.

Acerca de este tutorial

El material de esta tutorial principalmente cubre los objetivos de la Sección 5 del examen, titulada "Trabajando con objetos DB2." Usted puede ver estos objetivos en: http://www-03.ibm.com/certify/tests/obj730.shtml.

Los temas que se cubren en este tutorial incluyen:

  • Una descripción de los tipos de datos integrados que proporciona el DB2, y cuáles es apropiado utilizar cuando se define una tabla. (Para un tratamiento diferente de los tipos de datos, vea el cuarto tutorial de esta serie).
  • Una introducción a tipos de datos avanzados.
  • Tablas, vistas e índices.
  • Una explicación de los varios tipos de limitaciones y sus usos.

Objetivos

Después de completar este tutorial, usted deberá estar en capacidad de:

  • Entienda los tipos de datos y los tipos de datos avanzados
  • Cree tablas, vistas e índices en una base de datos DB2
  • Entienda los recursos y el uso de limitaciones únicas, restricciones de integridad referencial, y restricciones de verificación de tabla
  • Use las vistas para restringir el acceso a los datos
  • Comprenda los recursos e índices

Requisitos de sistema

Usted no necesita tener una copia DB2 para completar este tutorial. Sin embargo, si usted desea, puede descargar la versión de prueba gratuita de IBM DB2 9 para trabajar junto con este tutorial.


Tipos de datos

El DB2 proporciona una selección rica y flexible de tipos de datos. El DB2 incluye tipos de datos básicos como INTEGER, CHAR y DATE. También incluye facilidades para crear tipos de datos definidos por usuario (UDTs) de manera que usted crear tipos de datos complejos y no tradicionales adecuados a los complejos entornos de programación de hoy. La selección de qué tipo usar en una situación dada depende del tipo y rango de la información que haya organizada en la columna.

Existen cuatro categorías de tipos de datos integrados: numéricos, cadena de caracteres, fecha y hora, y XML.

Los tipos de datos definidos por usuario se categorizan como: distintivos, estructurados y de referencia.

Tipos de datos numéricos

Existen tres categorías de tipos de datos numéricos, como se diagrama en la figura anterior. Estos tipos varían en cuanto al rango y la precisión de los datos numéricos que pueden almacenar.

  • Integer: SMALLINT, INTEGER y BIGINT se utilizan para almacenar números enteros. Por ejemplo, un inventario podría definirse como INTEGER. SMALLINT puede almacenar enteros desde -32,768 hasta 32,767 en 2 bytes. INTEGER puede almacenar enteros desde -2,147,483,648 hasta 2,147,483,647 en 4 bytes. BIGINT puede almacenar enteros desde -9,223,372,036,854,775,808 hasta 9,223,372,036,854,775,807 en 8 bytes.
  • Decimal: DECIMAL se utiliza para almacenar números con partes de fracciones. Para definir este tipo de datos, especifique una precisión (p), que indica el número total de dígitos, y una escala (s), que indica el número de dígitos a la derecha del signo decimal. Una columna definida por DECIMAL(10,2) que contiene valores de datos puede contener valores hasta 99999999.99 dólares. La cantidad de almacenamiento requerido en la base de datos depende de la precisión y es calculada por la fórmula p/2 +1. Así, DECIMAL(10,2) requerirá 10/2 + 1 o 6 bytes.
  • Punto flotante: REAL y DOUBLE se utilizan para almacenar aproximaciones de números. Por ejemplo, medidas científicas muy grandes o muy pequeñas pueden definirse como REAL. REAL se puede definir con una longitud entre 1 y 24 dígitos y requiere 4 bytes de almacenamiento. DOUBLE puede definirse con una longitud entre 25 y 53 dígitos y requiere 8 bytes de almacenamiento. FLOAT puede usarse como sinónimo de REAL o de DOUBLE.

Tipos de datos de cadenas de caracteres

El DB2 proporciona varios tipos de datos para el almacenamiento de datos o cadenas de caracteres, como se diagramó en la anterior figura. Seleccione un tipo de datos con base en el tamaño de la cadena de caracteres que vaya a almacenar y a qué datos habrá en la secuencia.

Los siguientes tipos de datos se utilizan para almacenar secuencias de caracteres byte individual:

  • CHAR o CHARACTER se utiliza para secuencias de caracteres de longitud arreglada hasta 254 bytes. Por ejemplo, si un fabricante puede asignar un identificador a una parte con una longitud específica de ocho caracteres, y por lo tanto almacenar ese identificador en la base de datos como una columna de CHAR(8).
  • VARCHAR se utiliza para almacenar secuencias de caracteres de longitud variable. Por ejemplo, un fabricante puede manejar numerosas partes con identificadores de diferentes longitudes y por lo tanto almacenar esos identificadores como una columna. VARCHAR(100). La máxima longitud de una columna VARCHAR es 32,672 bytes. En la base de datos, los datos VARCHAR solo toman el espacio que requieran.

Los siguientes tipos de datos se utilizan para almacenar secuencias de caracteres byte doble:

  • GRAPHIC se utiliza para almacenar secuencias de caracteres de longitud arreglada de doble byte. La máxima longitud de una columna GRAPHIC es 127 caracteres.
  • VARGRAPHIC se utiliza para almacenar secuencias de caracteres de longitud variable de doble byte. La máxima longitud de una columna VARGRAPHIC es de 16,336 caracteres

El DB2 también proporciona tipos de datos para almacenar secuencias de datos muy extensas. Todos los tipos de datos de la cadena larga de caracteres a tienen características similares. Primero, los datos no son almacenados físicamente en la base de datos, lo cual significa que se requiere procesamiento adicional para acceder a estos datos. Los tipos de datos extensos pueden definirse hasta 2GB de longitud. No obstante, solo se usa el espacio requerido efectivamente. Los tipos de datos extensos son:

  • LONG VARCHAR
  • CLOB (character large object)
  • LONG VARGRAPHIC
  • DBCLOB (double-byte character large object)
  • BLOB (binary large object)

Tipos de datos de fecha y hora

El DB2 proporciona tres tipos de datos para almacenar fechas y horas:

  • DATE
  • TIME
  • TIMESTAMP

Los valores se estos tipos de datos son almacenados en la base de datos en un formato interno; no obstante, otras aplicaciones pueden manipularlos como secuencias. Cuando este tipo de datos es recuperado, se representa como una cadena de caracteres. Encierre el valor entre comillas cuando esté actualizando este tipo de datos.

El DB2 proporciona funciones integradas para manipular valores de fecha y hora. Por ejemplo, usted puede determinar el día de la semana de un valor de fecha usando las funciones DAYOFWEEK o DAYNAME .Use la función DAYS para calcular cuántos días haya entre dos fechas. El DB2 también proporciona registros especiales para generar la fecha, hora y marca de tiempo actuales con base en el reloj time-of-day. Por ejemplo:CURRENT DATE retorna una secuencia de la cadena de caracteres que representa la fecha actual en el sistema.

El formato de los valores de fecha y hora depende del código de país de la base de datos, el cual se especifica cuando se crea la base de datos. Existen varios formatos disponibles: ISO, USA, EUR y JIS. Por ejemplo si su base de datos está usando el formato de USA, el formato de los valores de fecha sería mm/dd/yyyy. Usted puede cambiar el formato usando la opción DATETIME del comando BIND cuando esté creando su aplicación.

Hay un formato individual para el tipo de datos TIMESTAMP. La representación de la cadena de caracteres es yyyy-mm-dd-hh.mm.ss.nnnnnn.

Tipos de datos XML

El DB2 proporciona los tipos de datos XML para almacenar documentos XML bien formados.

Los valores en las columnas XML son almacenados en una representación interna diferente a los tipos de datos de cadena de caracteres. Para almacenar datos XML en una columna de tipo de datos XML, transforme los datos usando la función XMLPARSE. Un valor de datos de tipo XML puede transformarse en un valor de cadena de caracteres serializada, representante del documento XML que usa la función XMLSERIALIZE. DB2 proporciona muchas otras funciones integradas para manipular tipos de datos XML.

Tipos de datos definidos por usuario

El DB2 le permite definir tipos de datos que se ajusten a su aplicación. Hay tres tipos de datos definidos por usuario:

  • Distintivos definidos por usuario: Definen un nuevo tipo de datos basado en un tipo integrado. Este nuevo tipo tiene los mismos recursos del tipo integrado, pero usted lo puede usar para asegurarse de que solo se comparen valores del mismo tipo. Por ejemplo, usted puede definir un tipo dólar canadiense (CANDOL) y un tipo dólar de EE.UU. (USADOL) ambos basados en DECIMAL(10,2). Ambos tipos están basados en el mismo tipo integrado, pero usted no podrá compararlos a menos que se aplique una función de conversión. Los siguientes enunciados CREATE TYPE crean los UDT CANDOL y USADOL:
    CREATE DISTINCT TYPE CANDOL AS DECIMAL(10,2) WITH COMPARISONS
    CREATE DISTINCT TYPE USADOL AS DECIMAL(10,2) WITH COMPARISONS

    DB2 genera funciones automáticamente para efectuar el casting entre el tipo de base y el tipo distintivo, y los operadores de comparación para comparar instancias de distinto tipo. Los siguientes enunciados muestran cómo crear una tabla con una columna de tipo CANDOL y luego insertar datos dentro de la tabla usando la función de casting CANDOL:

    CREATE TABLE ITEMs (ITEMID CHAR(5), PRICE CANDOL )
    INSERT INTO ITEMs VALUES('ABC11',CANDOL(30.50) )
  • Estructurado definido por usuario: Cree un tipo que consiste en varias columnas de tipos integrados. Luego use este tipo estructurado cuando esté creando una tabla. Por ejemplo, usted puede crear un tipo estructurado llamado ADDRESS que contenga datos para número de calle, nombre de calle, ciudad, y demás. Luego usted puede usar este tipo cuando defina otras tablas, como empleados y proveedores, porque los mismos datos son requeridos para ambos. Además, los tipos estructurados pueden tener sub-tipos en una estructura jerárquica. Esto le permite almacenar objetos que pertenezcan a la jerarquía de la base de datos.
  • Referencia definida por usuario: Cuando se están usando tipos estructurados, usted puede definir referencias a filas en otra tabla usando tipos de referencia. Estas referencias parecen similares a las restricciones referenciales; sin embargo, estas no fuerzan relaciones entre las tablas. Las referencias en las tablas le permiten especificar consultas de una forma diferente.

Los tipos estructurados y referenciados definidos por usuario son un tópico avanzado, la información presentada aquí sirve únicamente como una introducción a estos tipos.

Extensores DB2

Los Extensores DB2 proporcionan soporte para tipos de datos complejos y no tradicionales. Estos se empaquetan de forma separada al código del servidor DB2 y se instalan en el servidor en cada base de datos que usa el tipo de datos.

Hay bastantes Extensores DB2 disponibles de IBM y de proveedores de software independientes. Los primeros cuatro extensores proporcionados por IBM son para almacenar datos de audio, video, imágenes y texto. Por ejemplo, use el DB2 Text Extender para almacenar una imagen de una tapa de libro y el texto de un libro. Ahora, hay muchos otros extensores disponibles. El DB2 Spatial Extender puede usarse para almacenar y analizar datos espaciales y el XML Extender para administrar documentos XML.

Los extensores DB2 se implementan usando los recursos de los tipos y funciones definidas por usuario (UDFs). Cada extensor incluye uno o más UDT, UDF para que funcionen con el UDT, interfaces de programación de aplicaciones (API) específicas, y tal vez otras herramientas. Por ejemplo, el DB2 Image Extender incluye:

  • El DB2IMAGE UDT
  • UDF para insertar y recuperar de una columna DB2IMAGE
  • API para buscar con base en características de imágenes

Antes de usar estos tipos de datos, instale el soporte de extensor en la base de datos. El proceso de instalación para cada extensor define los UDT y UDF requeridos en la base de datos. En cuanto usted haya hecho esto, podrá utilizar los UDT cuando esté definiendo una tabla y los UDF cuando esté trabajando con datos. (Para más sobre extensores DB2, vea el primer tutorial de esta serie.)

Los tipos de datos XML, algo nuevo en DB2 9, proporciona recursos avanzados para el manejo de documentos XML. Las aplicaciones y los datos que usen el XML Extender deben ser migradas para que usen el soporte XML nativo en DB2.


Tablas

Todos los datos son almacenados en tablas de la base de datos. Una tabla en una o más columnas de varios tipos de datos. Los datos se almacenan en filas o registros.

Las tablas se definen usando el enunciado SQL CREATE TABLE . El DB2 también proporciona una herramienta GUI, el DB2 Control Center, para crear tablas, la cual crea tablas con base en la información que usted especifique. Esta también genera el enunciado SQL CREATE TABLE , el cual puede ser usado en un script o programa de aplicación más adelante.

Una base de datos es un conjunto de tablas, llamadas tablas de catálogo de sistema, que contienen informaciones sobre todos los objetos de la base de datos. El DB2 proporciona vistas para las tablas de catálogo del sistema base. La vista de catálogo SYSCAT.TABLES contiene una fila para cada tabla definida en la base de datos. SYSCAT.COLUMNS contiene una fila para cada columna de cada tabla de la base de datos. Observe las vistas del catálogo usando enunciados SELECT , tal como en cualquier otra tabla de la base de datos; no obstante, usted no puede modificar los datos usando enunciados INSERT, UPDATE ni DELETE . Las tablas se actualizan automáticamente como resultado de los enunciados de definición de datos (DDL), como CREATE, y otras operaciones, como RUNSTATS.

Creando una tabla

Use el enunciado SQL CREATE TABLE para definir una tabla en la base de datos. El siguiente enunciado crea una tabla simple llamada BOOKS, que contiene tres columnas:

CREATE TABLE BOOKS ( BOOKID INTEGER, 
                     BOOKNAME VARCHAR(100), 
                     ISBN CHAR(10) )

Usted también puede usar el enunciado SQL CREATE TABLE para crear una tabla que sea como otra tabla o vista de la base de datos:

CREATE TABLE MYBOOKS LIKE BOOKS

Este enunciado crea una tabla con las mismas columnas que la tabla o vista original. Las columnas de la nueva tabla tienen los mismos nombres, tipos de datos y atributos de nulidad que las columnas del antiguo. Usted también puede especificar cláusulas que copien otros atributos, como predeterminados de columna, y atributos de identidad.

Existen muchas opciones disponibles para el enunciado CREATE TABLE (las cuales se presentan en las siguientes secciones a medida que se introduzcan nuevos conceptos). Los detalles del enunciado SQL CREATE TABLE se pueden encontrar en la Referencia SQL (vea Recursos ).

En cuanto usted ha creado una tabla, existen variedad de formas para llenarla con datos. El enunciado INSERT le permite insertar una fila o varias filas de datos en la tabla. El DB2 también proporciona herramientas para insertar grandes cantidades de datos desde un archivo. El recurso IMPORT inserta filas usando enunciados INSERT . Este está diseñado para cargar pequeñas cantidades de datos en la base de datos. LA herramienta LOAD, La cual tiene por objeto cargar grandes volúmenes de datos, inserta filas directamente en las páginas de datos de la base de datos, y es mucho más rápida que la herramienta IMPORT.

Almacenando tablas en la base de datos

Las tablas se almacenan en la base de datos usando espacio de tabla. Los espacios de tabla tienen espacio físico asignado. Cree el espacio de tabla antes de crear la tabla.

Cuando cree una tabla, permita al DB2 ubicar la tabla en un espacio de tabla predeterminado, o especifique el espacio de tabla en el que usted desea que la tabla resida. El siguiente enunciado CREATE TABLE ubica la tabla BOOKS en el espacio de tabla BOOKINFO:

CREATE TABLE BOOKS ( BOOKID INTEGER, 
                     BOOKNAME VARCHAR(100), 
                     ISBN CHAR(10) ) 
             IN BOOKINFO

Aunque los espacio de tabla no se cubren en detalle aquí, es importante entender que definir apropiadamente los espacios de tabla tiene efecto en el desempeño y facilidad de mantenimiento de la base de datos. Para más información sobre los espacios de tabla, consulte el segundo tutorial de esta serie.

Alterando una tabla

Use el enunciado SQL ALTER TABLE para cambiar las características de una tabla. Por ejemplo, usted puede añadir o descargar:

  • Una columna
  • Una clave primaria
  • Una o más restricciones referenciales únicas
  • Una o más restricciones de verificación

El siguiente enunciado añade una columna llamada BOOKTYPE a la tabla BOOKS:

ALTER TABLE BOOKS ADD BOOKTYPE CHAR(1)

Usted también puede cambiar las características de columnas específicas de una tabla:

  • Los atributos de identidad de una columna
  • La longitud de una cadena de caracteres de la columna
  • El tipo de datos de una columna
  • La nulidad de una columna
  • La restricción de una columna

Existen restricciones para la alteración de columnas:

  • Cuando se altera la longitud de una columna de cadenas de caracteres, usted puede aumentar la longitud.
  • Cuando esté alterando el tipo de datos de una columna, el nuevo tipo de datos debe ser compatible con el tipo de datos existente. Po ejemplo, usted puede convertir columnas CHAR en columnas VARCHAR, pero no puede convertirlas a columnas GRAPH ni numéricas. Las columnas numéricas pueden convertirse en cualquier tipo de datos numéricos, siempre y cuando el nuevo tipo de datos sea lo suficientemente extenso como para contener los valores. Por ejemplo convertir una columna INTEGER en BIGINT, pero una columna DECIMAL(10,2) no se puede convertir en SMALLINT.
  • Las cadena de caracteres de longitud fija se pueden convertir en cadena de caracteres de longitud variable, y las cadenas de caracteres de longitud variables se pueden convertir en cadena de caracteres de longitud arreglada. Por ejemplo, una CHAR(100) puede convertirse en VARCHAR(150). Existen restricciones similares para secuencias gráficas de longitud variable.

El siguiente enunciado cambia el tipo de datos DATATYPE de la columna BOOKNAME de VARCHAR(100) a VARCHAR(200) y cambia la nulidad de la columna ISBN a NOT NULL:

ALTER TABLE BOOKS ALTER BOOKNAME SET DATA TYPE VARCHAR(200) ALTER ISBN SET NOT NULL

Ciertas características de una tabla no se pueden cambiar. Por ejemplo, usted no puede cambiar el espacio de tabla en el que reside la tabla, ni el orden de las columnas, ni cambiar el tipo de datos de algunas columnas. Para cambiar características como estas, guarde los datos de tabla, descargue la tabla y vuélvala a crear.

Descargando una tabla

El enunciado DROP TABLE remueve una tabla de la base de datos, eliminando los datos y la definición de tabla. Si hay índices o restricciones definidos en la tabla, estos también se descargan.

El siguiente enunciado DROP TABLE elimina la tabla BOOKS de la base de datos:

DROP TABLE BOOKS

Opciones de columna NOT NULL, DEFAULT y GENERATED

Las columnas de una tabla se especifican en el enunciado CREATE TABLE por un nombre de columna y tipo de datos. Las columnas pueden tener cláusulas adicionales especificadas que restrinjan los datos de la columna.

De forma predeterminada, una columna permite valores nulos. Si usted no desea permitir valores nulos, especifique la cláusula NOT NULL para la columna. Especifique el valor predeterminado usando la cláusula WITH DEFAULT y un valor predeterminado. El siguiente enunciado CREATE TABLE crea una tabla llamada BOOKS, donde la columna BOOKID no permite valores nulos y el valor predeterminado de BOOKNAME es TBD:

CREATE TABLE BOOKS ( BOOKID INTEGER NOT NULL, 
                     BOOKNAME VARCHAR(100) WITH DEFAULT 'TBD', 
                     ISBN CHAR(10) )

En la tabla BOOKS, BOOKID es un número único asignado a cada libro. En lugar de que la aplicación tenga que generar un identificador, usted puede especificar que DB2 genere una BOOKID usando la cláusula GENERATED ALWAYS AS IDENTITY :

CREATE TABLE BOOKS ( BOOKID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
                                             (START WITH 1, INCREMENT BY 1), 
                     BOOKNAME VARCHAR(100) WITH DEFAULT 'TBD', 
                     ISBN CHAR(10) )

GENERATED ALWAYS AS IDENTITY hace que se genere una BOOKID para cada registro. El primer valor generado es 1 y los valores siguientes son generados incrementando el valor anterior en 1.

Use también la opción GENERATED ALWAYS para hacer que el DB2 calcule el valor de una columna automáticamente. El siguiente ejemplo define una tabla llamada AUTHORS, con columnas FICTIONBOOKS y NONFICTIONBOOKS que contienen conteos de libros de ficción y de los que no son de ficción, respectivamente. La columna TOTALBOOKS es calculada añadiendo las columnas FICTIONBOOKS y NONFICTIONBOOKS:

CREATE TABLE AUTHORS (AUTHORID INTEGER NOT NULL PRIMARY KEY,
                      LNAME VARCHAR(100),
                      FNAME VARCHAR(100),
                      FICTIONBOOKS INTEGER,
                      NONFICTIONBOOKS INTEGER,
                      TOTALBOOKS INTEGER GENERATED ALWAYS 
                                 AS (FICTIONBOOKS + NONFICTIONBOOKS) )

Restricciones

El DB2 proporciona varias formas para controlar qué datos pueden almacenarse en una columna. Estos recursos son llamados restricciones o reglas que el gestor de la base de datos asigna a una columna de datos o conjunto de columnas.

El DB2 proporciona tres tipos de restricciones: únicas, de integridad referencial y de verificación de tabla.

Las siguientes secciones proporcionan descripciones e talladas de cada tipo de restricción.

Restricciones únicas

Las restricciones únicas se utilizan para asegurar que los valores de una columna sean únicos. Las restricciones únicas pueden ser definidas para una o más columnas. Cada columna incluida en la restricción única debe definirse como NOT NULL.

Las restricciones única pueden definirse como la restricción PRIMARY KEY o UNIQUE . Estas se definen cuando se crea una tabla, como parte del enunciado SQL CREATE TABLE , o se añaden después de la creación de tabla usando el enunciado ALTER TABLE .

¿Cuando define usted una PRIMARY KEY y cuándo define una clave UNIQUE ? Esto depende de la naturaleza de los datos. En el ejemplo anterior, la tabla BOOKS tenía una columna BOOKID que se usaba únicamente para identificar un libro. Este valor también es usado en otras tablas que contienen información relacionada con este libro. En este caso, usted definirá BOOKID como la clave primaria. DB2 solo permite definir una clave primaria para una tabla.

La columna de número ISBN necesita ser única pero no es un valor que esté referenciado de otra forma en la base de datos. En este caso, la columna ISBN se define como UNIQUE:

CREATE TABLE BOOKS (BOOKID INTEGER NOT NULL PRIMARY KEY,
                    BOOKNAME VARCHAR(100),
                    ISBN CHAR(10) NOT NULL CONSTRAINT BOOKSISBN UNIQUE )

La palabra clave CONSTRAINT le permite especificar el nombre para la restricción. En este ejemplo, el nombre de la restricción única es BOOKSISBN. Use este nombre en el enunciado ALTER TABLE si desea eliminar la restricción específica.

DB2 le permite definir solo una clave primaria sobre una tabla; no obstante, usted puede definir múltiples restricciones.

Siempre que defina una restricción PRIMARY KEY o UNIQUE para una columna, el DB2 crea un índice único para imponer el caracter único de la columna. El DB2 no le permite crear más de un índice único definido en las mismas columnas. Por lo tanto, usted debe definir una restricción PRIMARY KEY y UNIQUE para las mismas columnas. Por ejemplo, los dos enunciados siguientes contra la tabla BOOKS fallan porque ya existe una PRIMARY KEY:

ALTER TABLE BOOKS ADD  CONSTRAINT UNIQUE (BOOKID)
CREATE UNIQUE INDEX IBOOKS ON BOOKS (BOOKID)

Restricciones de integridad referencial

Las restricciones de integridad referencial se usan para definir relaciones entre tablas y para asegurar que estas relaciones permanezcan válidas. Suponga que usted tiene una tabla que contiene información sobre autores y otra tabla que tiene información que lista los libros que esos autores han escrito. Existe una relación entre la tabla BOOKS y la tabla AUTHORS (cada libro tiene un autor y ese autor debe existir en la tabla de autores). Cada autor tiene un identificador único almacenado en la columna AUTHORID. AUTHORID se usa en la tabla BOOKS para identificar al autor de cada libro. Para definir esta relación, defina la columna AUTHORID de la tabla AUTHORS como la clave primaria y luego defina una clave extranjera para la tabla BOOKS, para establecer la relación con la columna AUTHORID de la tabla AUTHORS:

CREATE TABLE AUTHORS (AUTHORID INTEGER NOT NULL PRIMARY KEY,
                      LNAME VARCHAR(100),
                      FNAME VARCHAR(100))
CREATE TABLE BOOKS (BOOKID INTEGER NOT NULL PRIMARY KEY,
                    BOOKNAME VARCHAR(100),
                    ISBN CHAR(10),
                    AUTHORID INTEGER REFERENCES AUTHORS)

La tabla que tiene una lave primaria que se relaciona con otra tabla (aquí es AUTHOR) es llamada una tabla madre. La tabla con la cual la tabla madre se relaciona (aquí es BOOKS) es llamada una tabla dependiente. Usted puede definir más de una tabla dependiente para una sola tabla madre.

Usted también puede definir relaciones entre filas de la misma tabla. En tal caso, una tabla madre y las tablas dependientes son la misma tabla.

Cuando usted define restricciones referenciales para un conjunto de tablas, el DB2 refuerza las reglas de integridad referencial en esas tablas cuando se efectúan operaciones contra ellas:

  • El DB2 se asegura que solo se inserten datos válidos en las columnas donde se han definido restricciones de integridad referencial. Esto significa que usted siempre debe tener una fila en la tabla madre con un valor clave que sea igual al valor clave extranjero de la fila que usted está insertando en la tabla dependiente. Por ejemplo, si un nuevo libro es insertado en la tabla BOOKS con AUTHORID 437, entonces ya debe existir una fila en la tabla AUTHORS donde AUTHORID sea 437.
  • El DB2 también establece reglas cuando se eliminen de una tabla madre filas que tengan filas dependientes en una tabla dependiente. La acción que toma el DB2 depende de la regla de eliminación definida en la tabla. Hay cuatro reglas que se pueden especificar: RESTRICT, NO ACTION, CASCADE y SET NULL.
    • Si se especifican RESTRICT o NO ACTION, el DB2 no permite que se elimine la fila madre. Las filas en tablas dependientes deben eliminarse antes de eliminar la fila en la tabla madre. Esto es lo predeterminado, así que esta regla se aplica a las tablas AUTHOR y BOOKS como se ha definido hasta ahora.
    • Si se especifica CASCADE, entonces eliminar una fila de la tabla madre también elimina automáticamente las filas dependientes en todas las tablas dependientes.
    • Si se especifica SET NULL, entonces la fila madre es eliminada de la tabla madre y el valor clave extranjero de las filas dependientes de configura como null (si lo permite).
  • Cuando se están actualizando valores clave en la tabla madre, hay dos reglas que se pueden especificar: RESTRICT y NO ACTION. RESTRICT no permite que un valor clave sea actualizado si hay filas dependientes en una tabla dependiente. NO ACTION hace que la operación de actualización de un valor clave madre sea rechazada si, al final de la actualización, hay filas dependientes en una tabla dependiente que no tenga una clave madre en la tabla madre.

Restricciones de verificación de tabla

Las restricciones de verificación de tabla se utilizan para verificar datos de columna que no violen reglas definidas para la columna y para restringir valores de ciertas columnas de la tabla. El DB2 se asegura de que la restricción no sea violada durante las inserciones t las actualizaciones.

Suponga que usted añade una columna a la tabla BOOKS para un tipo de libro, y los valores que usted desea permitir son F (ficción) y N (no ficción). Usted puede añadir una columna BOOKTYPE con una restricción de verificación como sigue:

ALTER TABLE BOOKS ADD BOOKTYPE CHAR(1) CHECK (BOOKTYPE IN ('F','N') )

Usted puede definir restricciones de verificación cuando crea la tabla, o añadirlas después usando el enunciado SQL ALTER TABLE . Usted puede modificar las restricciones de verificación descartándolas y luego volviéndolas a crear usando el enunciado SQL ALTER TABLE .


Vistas

Las vistas permiten que diferentes usuarios o aplicaciones busquen los mismos datos de formas diferentes. Esto no solo facilita el acceso a los datos, sino que también puede usarse para restringir cuáles filas y columnas ven o actualizan los usuarios.

Por ejemplo, suponga que una compañía tiene una tabla que contiene información sobre sus empleados. Un gestor necesita ver la información de dirección, número telefónico y salario sobre sus empleados únicamente, mientras que una aplicación de directorio necesita ver a todos los empleados de la compañía junto con sus direcciones y números telefónicos, pero no sus salarios. Usted puede crear una vista que muestre toda la información de los empleados de un departamento específico y otra que solo muestre el nombre, la dirección y el número telefónico de todos los empleados.

Para el usuario, la vista se ve solo como una tabla. Excepto por la definición de vista, una vista no consume espacio de la base de datos; los datos presentados en una vista son derivados de otra tabla. Usted puede crear una vista en una tabla (o tablas) existente(s), en otra vista, o alguna combinación de ambas. Una vista definida en otra vista es llamada una vista anidada.

Usted puede definir una vista con nombres de columna que sean diferentes a los nombres de columna correspondientes de la tabla base. También puede definir vistas que verifiquen, para ver si los datos insertados o actualizados están dentro de las condiciones de la vista.

La lista de vistas definidas en la base de datos se almacena en la tabla de catálogo de sistema SYSIBM.SYSVIEWS, la cual también tiene una vista definida en ella llamada SYSCAT.VIEWS. El catálogo de sistema también tiene una SYSCAT.VIEWDEP la cual, para cada vista definida en la base de datos, tiene una fila para cada vista o tabla que depende de esa vista. Además, cada vista tiene una entrada en SYSIBM.SYSTABLES y entradas en SYSIBM.SYSCOLUMNS porque las vistas pueden ser usadas simplemente como tablas.

Creando una vista

El enunciado SQL CREATE VIEW se utiliza para definir una vista. Un enunciado SELECT se utiliza para especificar cuáles filas y columnas se presentan en la vista.

Por ejemplo, imagina que usted desea crear una vista que muestre solo los libros que no sean de ficción de nuestra tabla BOOKS:

CREATE VIEW NONFICTIONBOOKS AS 
       SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'

Después de que usted define esta vista, hay entradas para ella en SYSCAT.VIEWS, SYSCAT.VIEWDEP y SYSCAT.TABLES.

Para definir los nombres de columna en la vista que son diferentes a los de la tabla base, usted puede especificarlos en el enunciado CREATE VIEW . El siguiente enunciado crea una vista MYBOOKVIEW que contiene dos columnas: TITLE, la cual representa la columna BOOKNAME, y TYPE, que representa la columna BOOKTYPE.

CREATE VIEW MYBOOKVIEW (TITLE,TYPE) AS 
       SELECT BOOKNAME,BOOKTYPE FROM BOOKS

El enunciado SQL DROP VIEW se utiliza para descartar una vista de la base de datos. Si usted elimina una tabla u otra vista en la que se base una vista, la vista permanecerá definida en la base de datos, pero no quedará operativa. La columna VALID de SYSCAT.VIEWS indica si una vista es válida (Y) o no (X). Incluso si usted recrea la tabla base, la vista huérfana permanece inválida; usted también debe recrearla.

Usted puede eliminar la vista NONFICTIONBOOKS de la base de datos:

DROP VIEW NONFICTIONBOOKS

Usted no puede modificar una vista; para cambiar la definición de vista, elimínela y vuélvala a crear. Use el enunciado ALTER VIEW proporcionado únicamente para modificar tipos de referencias.

Vistas de solo lectura y actualizables

Cuando usted crea una vista, puede definirla como una vista de solo lectura o como una vista actualizable . El enunciado SELECT de una vista determina si la vista es de solo lectura o si es actualizable. Generalmente, si las filas de una vista pueden correlacionarse con las filas de una tabla de base, entonces la vista se puede actualizar. Por ejemplo, la vista NONFICTIONBOOKS, como usted la definió en el ejemplo anterior, es actualizable porque cada fila de la vista es una fila de la tabla base.

Las reglas para crear vistas actualizables son complejas y dependen de la definición de la consulta. Por ejemplo las vistas que usen característicasVALUES, DISTINCT o JOIN no son actualizables. Usted puede determinar fácilmente si una vista es actualizable al observar la columna READONLY de SYSCAT.VIEWS: Y significa que es de solo lectura, y N significa que no.

Las reglas detalladas para la creación de vistas actualizables están documentadas en la Referencia DB2 SQL (vea Recursos).

La vista NONFICTIONBOOKS definida previamente solo incluye las filas donde el BOOKTYPE es N. S i usted inserta en la vista una fila donde el BOOKTYPE sea F, el DB2 inserta la fila en la tabla base BOOKS. No obstante, si luego usted selecciona de la vista, la fila recientemente insertada no se podrá mediante la vista. Si usted no desea permitir a un usuario insertar filas que estén por fuera del alcance de la vista, usted puede definir la vista con la opción de verificación. Definir una vista usando WITH CHECK OPTION le dice el DB2 que verifique los enunciados usando la vista que satisfaga las condiciones de la vista.

El siguiente enunciado define una vista usando WITH CHECK OPTION:

CREATE VIEW NONFICTIONBOOKS AS 
       SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'
       WITH CHECK OPTION

Esta vista todavía limita al usuario para que solo pueda ver libros que no sean de ficción; además, evita que el usuario inserte filas que no tengan un valor de N en la columna BOOKTYPE y de actualizar el valor de la columna BOOKTYPE de las filas existentes a un valor diferente a N. Por lo tanto, los siguientes enunciados ya no se permitirán:

INSERT INTO NONFICTIONBOOKS VALUES (...,'F'); 
UPDATE NONFICTIONBOOKS SET BOOKTYPE = 'F' WHERE BOOKID = 111

Vistas anidadas con opción de verificación

Cuando se definen vistas anidadas, la opción de verificación puede usarse para restringir operaciones. Sin embargo, hay otras cláusulas que usted puede especificar para definir cómo se heredan las restricciones. La opción de verificación puede definirse como CASCADED o como LOCAL. CASCADED es el predeterminado si no se especifica palabra clave. Diversos escenarios posibles explican las diferencias entre el comportamiento de CASCADED y LOCAL.

Cuando se crea una vista WITH CASCADED CHECK OPTION, todos los enunciados ejecutados contra la vista deben satisfacer las condiciones de la vista y todas las vistas subyacentes (incluso si esas vistas no fueron definidas con la opción de verificación). Suponga que la vista NONFICTIONBOOKS es creada sin la opción de verificación y que usted también crea una vista NONFICTIONBOOKS1 basada en la vista NONFICTIONBOOKS usando la palabra clave CASCADED:

CREATE VIEW NONFICTIONBOOKS AS 
       SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'
CREATE VIEW NONFICTIONBOOKS1 AS 
       SELECT * FROM NONFICTIONBOOKS WHERE BOOKID > 100
       WITH CASCADED CHECK OPTION

Los siguientes enunciados INSERT no se permitirían porque no satisfacen las condiciones de por lo menos una de las vistas:

INSERT INTO NONFICTIONBOOKS1 VALUES( 10,..,'N')
INSERT INTO NONFICTIONBOOKS1 VALUES(120,..,'F')
INSERT INTO NONFICTIONBOOKS1 VALUES( 10,..,'F')

No obstante, el siguiente enunciado INSERT si sería permitido porque satisface las condiciones de ambas vistas:

INSERT INTO NONFICTIONBOOKS1 VALUES(120,...,'N')

Luego, suponga que usted crea una vista NONFICTIONBOOKS2 basada en la vista NONFICTIONBOOKS usando WITH LOCAL CHECK OPTION. Ahora, los enunciados ejecutados contra la vista solo necesitan satisfacer condiciones de vistas que tengan especificada la opción de verificación:

CREATE VIEW NONFICTIONBOOKS AS 
       SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'
CREATE VIEW NONFICTIONBOOKS2 AS 
       SELECT * FROM NONFICTIONBOOKS WHERE BOOKID > 100
       WITH LOCAL CHECK OPTION

En este caso, los siguientes enunciados INSERT no serían permitidos porque no satisfacen la condición BOOKID > 100 de la vista NONFICTIONBOOKS2:

INSERT INTO NONFICTIONBOOKS2 VALUES(10,..,'N')
INSERT INTO NONFICTIONBOOKS2 VALUES(10,..,'F')

No obstante, el siguiente enunciado INSERT si se permitirían, incluso aunque el valor N no satisfaga la condición BOOKTYPE = 'N' de la vista NONFICTIONBOOKS:

INSERT INTO NONFICTIONBOOKS2 VALUES(120,..,'N')
INSERT INTO NONFICTIONBOOKS2 VALUES(120,..,'F')

Índices

Un índice es una lista ordenada de los calores clave de una columna o columnas de una tabla. Existen dos razones por las cuales usted crearía un índice:

  • Para asegurar que los valores de una columna o columnas son únicos.
  • Para mejorar el desempeño de las consultas contra la tabla. El optimizador DB2 usa índices para mejorar el desempeño cuando efectúa consultas o para presentar los resultados de una consulta en la orden del índice.

Los índices pueden definirse como únicos o no únicos. Los índices no únicos permiten la duplicación de valores clave; los índices únicos solo permiten una ocurrencia de un valor clave en la lista. Los índices únicos permiten que un valor nulo individual esté presente. Sin embargo, un segundo valor nulo causará un duplicado y por lo tanto no estará permitido.

Los índices se crean usando el enunciado SQL CREATE INDEX . Los índices son creados implícitamente como soporte de una restricción PRIMARY KEY o UNIQUE . Cuando se crea un índice único, se verifica que los datos clave sean únicos y la operación falla si se encuentran duplicados.

Los índices son creados como ascendentes, descendentes o bidireccionales. La opción que usted seleccione depende de cómo acceda la aplicación a los datos.

Creación de índices

En el ejemplo, usted tiene una clave primaria en la columna BOOKID. Con frecuencia, los usuarios llevan a cabo búsquedas en el título del libro, por lo que sería apropiado un índice en BOOKNAME. El siguiente enunciado crea un índice ascendente no único en la columna BOOKNAME:

CREATE INDEX IBOOKNAME ON BOOKS (BOOKNAME)

El nombre del índice, IBOOKNAME, es utilizado para crear y eliminar el índice. Aparte de eso, el nombre no es usado en consultas ni actualizaciones a la tabla.

De forma predeterminada, los índices se crean en orden ascendente, pero usted también puede crear índices en orden descendente. Usted puede incluso especificar órdenes diferentes para las columnas del índice. El siguiente enunciado define un índice en las columnas AUTHORID y BOOKNAME. Los valores de la columna AUTHORID son ordenados de forma descendente y los valores de la columna BOOKNAME se ordenan de forma ascendente dentro de la mismaAUTHORID:

CREATE INDEX I2BOOKNAME ON BOOKS (AUTHOID DESC, BOOKNAME ASC)

Cuando se crea un índice en una base de datos, las claves se almacenan en el orden especificado. El índice ayuda a mejorar el desempeño de las consultas que requieren los datos en el orden especificado. Un índice ascendente también se utiliza para determinar el resultado de la función de columna MIN ; un índice descendente se usa para determinar el resultado de la función de columna MAX . Si la aplicación requiere que los datos también sean ordenados en la secuencia opuesta, el DB2 permite la creación de un índice bidireccional. Un índice bidireccional elimina la necesidad de crear un índice en el orden inverso, y elimina la necesidad de que el optimizador clasifique los datos en orden inverso. Este también permite la recuperación eficiente de los valores de función MIN y MAX . Para crear un índice bidireccional, especifique la opción ALLOW REVERSE SCANS en el enunciado CREATE INDEX :

CREATE INDEX BIBOOKNAME ON BOOKS (BOOKNAME) ALLOW REVERSE SCANS

El DB2 no le permite crear múltiples índices con la misma definición. Esto se aplica incluso a índices que usted cree de forma implícita para soportar una clave primaria o una restricción única. Como la tabla BOOKS ya tiene definida una clave primaria en la columna BOOKID, intentar crear un índice en la columna BOOKID falla.

La creación de un índice puede tardar bastante. El DB2 lee cada fila para extraer las claves, ordena esas claves, y luego escribe la lista en la base de datos. Si la tabla es grande, entonces se usa un espacio de tabla temporal para ordenar las claves.

El índice se almacena en un espacio de tabla. Si su tabla reside en un espacio de tabla administrado por base de datos, usted tiene la opción de separar los índices en un espacio de tabla separado. Defina esto cuando cree la tabla, usando la cláusula INDEXES IN . La ubicación de los índices de una tabla se establece cuando se crea la tabla y no se puede cambiar a menos que la tabla se elimine y se vuelva a crear.

El DB2 también proporciona el enunciado SQL DROP INDEX para eliminar un índice de la base de datos. No existe forma para modificar un índice. Si usted necesita cambiar un índice (para agregar otra columna a la clave, por ejemplo) usted necesitará eliminarlo y recrearlo.

Índices de agrupación en clúster

Usted puede crear un índice en cada tabla como el índice de agrupación en clúster. Un índice de agrupación en clúster es útil cuando los datos de tabla están referenciados a menudo en un orden en particular. El índice de agrupación en clúster define el orden en el que los datos son almacenados en la base de datos. Durante las inserciones, el DB2 intenta ubicar las filas nuevas cerca a filas con claves similares. Luego, durante las consultas que requieran datos de la secuencia de índice de agrupación en clúster, los datos podrán recuperarse más rápido.

Para crear un índice como índice de agrupación en clúster, especifique la cláusula CLUSTER en el enunciado CREATE INDEX :

CREATE INDEX IAUTHBKNAME ON BOOKS (AUTHORID,BOOKNAME) CLUSTER

Este enunciado define un índice en las columnas AUTHORID y BOOKNAME como el índice de agrupación en clúster. Este índice mejorará el desempeño de las consultas escritas para listar autores y todos los libros que hayan escrito.

Usando columnas incluidas en índices

Cuando usted está creando una columna, tiene la opción de incluir datos de columna adicionales que estén almacenados con la clave, pero esto en realidad no es parte de la clave en sí y no está almacenado. La principal razón para incluir columnas adicionales en un índice es mejorar el desempeño de ciertas consultas: con estos datos ya disponibles en la página de índice, el DB2 no necesita acceder a la página de datos para capturarlos. Las columnas incluidas solo pueden ser definidas por índices únicos. No obstante, las columnas incluidas no se consideran cuando se está haciendo cumplir el carácter único del índice.

Suponga que a menudo usted necesita obtener una lista de nombres de libros ordenados por BOOKID. La consulta se vería así:

SELECT BOOKID,BOOKNAME FROM BOOK ORDER BY BOOKID

Cree un índice que podría mejorar el desempeño:

CREATE UNIQUE INDEX IBOOKID ON BOOKS (BOOKID) INCLUDE(BOOKNAME)

Como resultado, todos los datos requeridos por el resultado de la consulta están presentes en el índice y no se necesita recuperar ninguna página de datos.

Entonces, ¿por qué no simplemente incluir todos los datos en los índices? Primero que todo, esto requeriría más espacio físico en la base de datos porque los datos de tabla básicamente serían duplicados en el índice. Segundo, todas las copias de los datos necesitarían ser actualizadas siempre que se actualice el valor de los datos, y esta sería una sobrecarga para una base de datos donde ocurran muchas actualizaciones.

¿Qué índices debo crear?

Considere lo siguiente cuando esté creando índices:

  • Como los índices son una lista permanente de valores clave, estos requieren espacio en la base de datos. La creación de muchos índices requiere espacio adicional en su base de datos. La cantidad de espacio requerido está determinada por la extensión de las columnas clave. El DB2 proporciona una herramienta para ayudarle a estimar el tamaño de un índice.
  • Los índices son copias adicionales de los valores, así que deben ser actualizados si los datos de la tabla son actualizados. Si los datos de tabla son actualizados frecuentemente, considere el impacto adicional que los índices tendrían sobre el desempeño de las actualizaciones.
  • Los índices mejoran significativamente el desempeño de las consultas cuando se definen para las columnas apropiadas.

El DB2 proporciona una herramienta llamada el Index Advisor para ayudarle a determinar cuáles índices definir. El Index Advisor le permite especificar la carga de trabajo que se ejecuta contra una tabla, y recomienda los índices a ser creados en la tabla.


Resumen

Resumen

Este tutorial se diseñó para que usted se familiarice con los recursos de tipos de datos, tablas, restricciones, vistas e índices definidos en DB2. Este también le mostró como usar los enunciados CREATE, ALTER y DROP para administra estos objetos. Se proporcionaron ejemplos para que usted pueda intentar de forma práctica el uso de estos objetos en un entorno controlado.

La Parte 6: Concurrencia de datos, le introduce al concepto de consistencia de datos y a los diferentes mecanismos que utiliza el DB2 para mantener la consistencia de base de datos en entornos tanto individuales como multi-usuario.

Para seguir esta serie, añada un marcador a esta página, Tutoriales de preparación para el examen 730 DBA DB2 9 DBA.

Recursos

Aprender

Obtener los productos y tecnologías

  • Una versión de prueba del DB2 9 está disponible para descarga.
  • Descargue DB2 Express-C, una versión gratuita del DB2 Express Edition para la comunidad que ofrece los mismos recursos de datos principales que el DB2 Express Edition y proporciona una base sólida para construir e implementar aplicaciones.

Comentarios

developerWorks: Ingrese

Los campos obligatorios están marcados con un asterisco (*).


¿Necesita un IBM ID?
¿Olvidó su IBM ID?


¿Olvidó su Password?
Cambie su Password

Al hacer clic en Enviar, usted está de acuerdo con los términos y condiciones de developerWorks.

 


La primera vez que inicie sesión en developerWorks, se creará un perfil para usted. La información en su propio perfil (nombre, país/región y nombre de la empresa) se muestra al público y acompañará a cualquier contenido que publique, a menos que opte por la opción de ocultar el nombre de su empresa. Puede actualizar su cuenta de IBM en cualquier momento.

Toda la información enviada es segura.

Elija su nombre para mostrar



La primera vez que inicia sesión en developerWorks se crea un perfil para usted, teniendo que elegir un nombre para mostrar en el mismo. Este nombre acompañará el contenido que usted publique en developerWorks.

Por favor elija un nombre de 3 - 31 caracteres. Su nombre de usuario debe ser único en la comunidad developerWorks y debe ser distinto a su dirección de email por motivos de privacidad.

Los campos obligatorios están marcados con un asterisco (*).

(Por favor elija un nombre de 3 - 31 caracteres.)

Al hacer clic en Enviar, usted está de acuerdo con los términos y condiciones de developerWorks.

 


Toda la información enviada es segura.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=90
Zone=Information mgmt
ArticleID=792296
ArticleTitle=Preparación para el examen 730 Fundamentos DB2 9, Parte 5: Trabajando con objetos DB2
publish-date=02132012