SQL Server y DB2

agosto 27

2012Recopilacion de Restauración Integridad Concurrencia Seguridad de SQL Server y DB2

1

Contenido SQL....................................................................................................................................................... 3

Información general sobre restauración y recuperación (SQL Server) ............................................... 3

RESTORE (Transact-SQL).................................................................................................................. 3

--Para restaurar una base de datos entera de un respaldo completo ........................................ 4

- Para llevar a cabo el primer paso de la secuencia de restauración inicial de una restauración

por etapas: .................................................................................................................................. 4

--Para recuperar Archivos especificos o grupos de archivos ...................................................... 4

--Para restaurar Paginas Especificas ............................................................................................ 5

--Para restaurar un registro de transacciones ............................................................................. 5

--Para revertir una base de datos a una captura de una base de datos ..................................... 5

Modelo de recuperación en SQL Server (recovery model) ............................................................. 6

Introducción a la integridad de los datos ............................................................................................ 8

Asegurar la integridad de los datos ................................................................................................. 8

Tipos de Dato .................................................................................................................................. 8

Tipos de integridad de datos ........................................................................................................... 9

Integridad de entidad ...................................................................................................................... 9

Integridad de dominio ..................................................................................................................... 9

Integridad referencial .......................................................................................................................... 9

Integridad definida por el usuario ................................................................................................. 10

Introducción a las restricciones de integridad .............................................................................. 10

Restricciones PRIMARY KEY........................................................................................................... 11

Restricciones UNIQUE ................................................................................................................... 13

Crear restricciones UNIQUE .......................................................................................................... 14

Restricciones FOREIGN KEY ........................................................................................................... 14

Una tabla puede tener múltiples restricciones FOREIGN KEY. ..................................................... 14

Crear restricciones FOREIGN KEY .................................................................................................. 15

Deshabilitar restricciones FOREIGN KEY ....................................................................................... 16

Restricciones CHECK ...................................................................................................................... 17

Crear restricciones CHECK ............................................................................................................. 17

Deshabilitar restricciones CHECK .................................................................................................. 19

Transacciones en Transact SQL ......................................................................................................... 20

2

Transact SQL se ejecutan como un único bloque, es decir, si falla una operación, fallan todas. ..... 20

Especificar el tipo de almacenamiento en archivo mediante bcp (SQL Server) ............................... 21

Requisitos para restaurar las copias de seguridad del registro de transacciones ........................ 21

Niveles de aislamiento en SQL-SERVER ......................................................................................... 22

Ver el registro de errores de SQL Server ........................................................................................... 23

Seguridad de la plataforma y de la red ......................................................................................... 23

Seguridad física ............................................................................................................................. 23

SEGURIDAD ................................................................................................................................... 25

Recursos de los bloqueos .............................................................................................................. 26

Tipos de bloqueo en SQL Server ................................................................................................... 26

Bloqueos compartidos .................................................................................................................. 26

Bloqueos de actualización ............................................................................................................. 26

Bloqueos exclusivos ...................................................................................................................... 27

Bloqueos de intención ................................................................................................................... 27

Bloqueos de esquema ................................................................................................................... 27

Compatibilidad de bloqueos ......................................................................................................... 27

Bloqueos de índices ....................................................................................................................... 28

RECUPERACION ................................................................................................................................. 28

Utilización de registros (Anotación Cronológica o Logging) ............................................................. 28

Backup ............................................................................................................................................... 30

Tipos de Recuperación ...................................................................................................................... 32

Restore .............................................................................................................................................. 32

Seguridad (Protección) .................................................................................................................. 34

Autenticación .................................................................................................................................... 34

Lightweight Directory Access Protocol (LDAP). ................................................................................. 34

Kerberos. ........................................................................................................................................... 34

Autorización ...................................................................................................................................... 35

Los contextos fiables ......................................................................................................................... 35

Auditoría ........................................................................................................................................ 35

Control de acceso a fila y columna. ................................................................................................... 35

Label-Based Access Control............................................................................................................... 36

Cifrado ............................................................................................................................................... 36

3

CONSISTENCIA ................................................................................................................................... 38

Bloqueo Mortal o Deadlock, causas y detección .............................................................................. 38

INTEGRIDAD ...................................................................................................................................... 39

Bibliografía ........................................................................................................................................ 45

Bibliografía ........................................................................................................................................ 45

SQL

Información general sobre restauración y recuperación (SQL Server)

La función de restauración y recuperación de SQL Server permite restaurar los datos de las copias de seguridad de toda una base de datos, un archivo de datos o una página de datos, tal y como se describe a continuación:

La base de datos (una restauración de la base de datos completa)

Se restaura y recupera toda la base de datos, que permanece sin conexión durante las operaciones de restauración y recuperación.

El archivo de datos (una restauración de archivos)

Se restaura y recupera un archivo de datos o conjunto de archivos. Durante la restauración de un archivo, los grupos de archivo que incluyen los archivos se dejan sin conexión de forma automática mientras dure el proceso de restauración. Cualquier intento de obtener acceso a un grupo de archivos sin conexión genera un error.

La página de datos (una restauración de páginas)

Con el modelo de recuperación completa o el modelo de recuperación optimizado para cargas masivas de registros, puede restaurar bases de datos individuales. Las restauraciones de páginas pueden utilizarse con cualquier base de datos, independientemente del número de grupos de archivos.

Las copias de seguridad y restauración de SQL Server funcionan en todos los sistemas operativos admitidos, tanto si se trata de sistemas de 64 o de 32 bits.

RESTORE (Transact-SQL)

Restaura copias de seguridad realizadas con el comando BACKUP. Este comando le permite realizar los siguientes escenarios de restauración:

4

Restaurar una base de datos completa a partir de una copia de seguridad completa de la base de datos (restauración completa).

Restaurar parte de una base de datos (restauración parcial). Restaurar archivos o grupos de archivos en una base de datos (restauración de archivos). Restaurar páginas específicas en una base de datos (restauración de páginas). Restaurar un registro de transacciones en una base de datos (restauración del registro de

transacciones). Revertir una base de datos al punto temporal capturado por una instantánea de base de

datos.

--Para restaurar una base de datos entera de un respaldo completo

RESTORE DATABASE { database_name | @database_name_var } [ FROM <backup_device> [ ,...n ] ] [ WITH { [ RECOVERY | NORECOVERY | STANDBY = {standby_file_name | @standby_file_name_var } ] | , <general_WITH_options> [ ,...n ] | , <replication_WITH_option> | , <change_data_capture_WITH_option> | , <FILESTREAM_WITH_option> | , <service_broker_WITH options> | , <point_in_time_WITH_options—RESTORE_DATABASE> } [ ,...n ] ] [;]

- Para llevar a cabo el primer paso de la secuencia de restauración inicial de una restauración por

etapas:

RESTORE DATABASE { database_name | @database_name_var } <files_or_filegroups> [ ,...n ] [ FROM <backup_device> [ ,...n ] ] WITH PARTIAL, NORECOVERY [ , <general_WITH_options> [ ,...n ] | , <point_in_time_WITH_options—RESTORE_DATABASE> ] [ ,...n ] [;]

--Para recuperar Archivos especificos o grupos de archivos

RESTORE DATABASE { database_name | @database_name_var }

5

<file_or_filegroup> [ ,...n ] [ FROM <backup_device> [ ,...n ] ] WITH { [ RECOVERY | NORECOVERY ] [ , <general_WITH_options> [ ,...n ] ] } [ ,...n ] [;]

--Para restaurar Paginas Especificas

RESTORE DATABASE { database_name | @database_name_var } PAGE = 'file:page [ ,...n ]' [ , <file_or_filegroups> ] [ ,...n ] [ FROM <backup_device> [ ,...n ] ] WITH NORECOVERY [ , <general_WITH_options> [ ,...n ] ] [;]

--Para restaurar un registro de transacciones

RESTORE LOG { database_name | @database_name_var } [ <file_or_filegroup_or_pages> [ ,...n ] ] [ FROM <backup_device> [ ,...n ] ] [ WITH { [ RECOVERY | NORECOVERY | STANDBY = {standby_file_name | @standby_file_name_var } ] | , <general_WITH_options> [ ,...n ] | , <replication_WITH_option> | , <point_in_time_WITH_options—RESTORE_LOG> } [ ,...n ] ] [;]

--Para revertir una base de datos a una captura de una base de datos

RESTORE DATABASE { database_name | @database_name_var } FROM DATABASE_SNAPSHOT = database_snapshot_name <backup_device>::= { { logical_backup_device_name | @logical_backup_device_name_var } | { DISK | TAPE } = { 'physical_backup_device_name' |

6

@physical_backup_device_name_var } } <files_or_filegroups>::= { FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var } | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } | READ_WRITE_FILEGROUPS } <general_WITH_options> [ ,...n ]::=

Modelo de recuperación en SQL Server (recovery model) Existen dos modos de recuperación:

El modo de recuperacion Simple

Configura SQL Server para que escriba lo minimo en el log de transacciones. De esta forma, el Log

no crece mucho y las transacciones que van al disco se eliminan automaticamente del Log.

El modo de recuperacion Completa

Escribe todas las transacciones en el log, y estas no se eliminan automaticamente. El log crece

indefinidamente hasta que alcanza su tamano limite (si un tamano limite ha sido especificado en

el servidor) o hasta que se llene el disco. Como todas las transacciones estan escritas en el log de

transacciones, se pueden hacer backups del log para que en caso de desastre, se pueda recuperar

la base de datos hasta el momento del fallo. Cuando hace un backup del log, SQL Server elimina las

transaccinoes innecesarias (que ya han sido movidas al disco) y libera el espacio del log.

Por eso, si tienes un db en modo de recuperacion Completa, es importante que hagas backups del

log para asi prevenir que crezca innecesariamente, colapsando el disco y rovocando la parada del

servicio por falta de recursos.

Ambos modelos tienen sus ventajas.

El modelo simple es ideal para bases de datos que no reciben muchos cambios y cuya

recuperacion completa no es critica. Cuando se recupera la base de datos, solo se puede recuperar

datos hasta el momento del ultimo backup. La ventaja es la simplicidad y el ahorro de recursos.

En el modelo Completo, la recuperacion es posible hasta el momento del error, si se tienen

backups del log, y lo primero que se hace despues de error es hacer un ultimo backup del log para

capturar las ultimas transacciones. La desventaja, consumo de recursos y de espacio en disco. Este

modelo es imperativo si la base de datos es critica.

Truncar LOG y reducir su tamaño

Si el LOG se descontrola en BD en modo FULL sobre las que no se hace backup del LOG, por

ejemplo, para truncar el log y reducir su tamaño se usan los comandos:

7

“CHECKPOINT”: para hacer commit de las transacciones pendientes que puedan quedar en el LOG

sin grabar a disco aún.

“TRUNCATE LOG” o “BACKUP LOG minombredebasededatos WITH TRUNCATE_ONLY”: para

eliminar la información del LOG, sin hacer backup de ella.

“DBCC SHRINKFILE (minombredebasededatos_Log, 100)”: para reducir el tamaño del dispositivo

de LOG a 100 MB.

Crear alertas en SQL Server con automatización del procedimiento:

Se pueden crear alertas en el SQL Server para que cuando se dé el caso de que el dispositivo LOG

de una cierta BD supere lox X kb de tamaño, se ejecute un script de mantenimiento que haga las

operaciones ateriores por nosotros y envíe un email de aviso al operador del sistema.

8

Introducción a la integridad de los datos

Las tablas en una base de datos SQL Server pueden incluir diferentes tipos de propiedades para asegurar la integridad de los datos. Estas propiedades incluyen: tipos de dato, definiciones NOT NULL, definiciones DEFAULT, propiedades IDENTITY, restricciones, reglas, desencadenadores e índices. A continuación se presenta una introducción de todos estos tipos de integridad de datos soportados por SQL Server. Además, se discutirán los diferentes tipos de integridad de datos, incluyendo integridad de entidad, integridad de dominio, integridad referencial e integridad definida por el usuario.

Asegurar la integridad de los datos

SQL Server soporta varios métodos para asegurar la integridad de los datos, que incluyen: tipos de dato, definiciones NOT NULL, definiciones DEFAULT, propiedades IDENTITY, restricciones, reglas, desencadenadores e índices. Ya se han visto algunos de estos métodos. Un breve resumen de ellos es incluido aquí a fin de mostrar una visión comprehensiva de los distintos modos de asegurar la integridad de los datos. Algunas de esta propiedades de la tablas, tales como las definiciones NOT NULL y DEFAULT, son a veces consideradas tipos de restricciones. Para los propósitos de este Kit, sin embargo, son tratadas de forma separada.

Tipos de Dato

Un tipo de dato es un atributo que especifica el tipo de dato (carácter, entero, binario, etc.) que puede ser almacenado en una columna, parámetro o variable. SQL Server provee de un conjunto de tipos de dato, aún cuando se pueden crear tipos de dato definidos por el usuario que se crean sobre la base de tipos de dato provisto por el SQL Server. Los tipos de dato provistos por el sistema definen todos los tipos de dato que se pueden usar en SQL Server. Los tipos de dato pueden ser utilizados para asegurar la integridad de los datos porque los datos ingresados o modificados deben cumplir con el tipo de dato especificado para el objeto correspondiente. Por ejemplo, no se puede almacenar el nombre de alguien en una columna con un tipo de dato datetime, ya que esta columna solo aceptará valores válidos de fecha y hora.

9

Tipos de integridad de datos

SQL Server soporta cuatro tipos de integridad de datos: integridad de entidad, integridad de dominio, integridad referencial e integridad definida por el usuario.

Integridad de entidad

La integridad de entidad define una fila como una única instancia de una entidad para una tabla en particular. La integridad de entidad asegura la integridad de la columna de identificación o la clave primaria de una tabla ( a través de índices, estricciones UNIQUE, restricciones PRIMARY KEY, o propiedades IDENTITY).

Integridad de dominio

La integridad de dominio es la validación de las entradas en una determinada columna. Se puede asegurar la integridad de dominio restringiendo el tipo (a través de tipos de datos), el formato (a través de las restricciones CHECK y de las reglas), o el rango de valores posibles ( a través de restricciones FOREIGN KEY, restricciones CHECK, definiciones DEFAULT, definiciones NOT NULL, y reglas)

Integridad referencial

La integridad referencial preserva las relaciones definidas entre tablas, cuando se entran, modifican o borran registros. En SQL Server, la integridad referencial esta basada en interrelaciones entre claves ajenas y claves primarias o entre claves ajenas y claves únicas (a través de la restricciones FOREIGN KEY y CHECK). La integridad referencial asegura que los valores de las claves son consistentes a través de distintas tablas. Tal consistencia requiere que no existan referencia a valores inexistentes y que, si un valor clave cambia, todas las referencias cambien consistentemente a lo largo de la base de datos.

Cuando se fuerza la integridad referencial, SQL Server previene a los usuarios de realizar lo siguiente:

· Agregar registros a una tabla relacionada si no hay registros asociados en la correspondiente tabla primaria.

· Cambiar valores en la tabla primaria que resulten en registros huérfanos en las tablas relacionadas.

· Borrar registros desde una tabla primaria si existen registros relacionados en la tabla ajena.

Por ejemplo, con las tablas Ventas y Títulos en la base de datos Pubs, la integridad referencial está basada sobre las relaciones entre la clave ajena (tit_ID) en la tabla ventas y la clave primaria (tit_ID) en la tabla Titulos, como se muestra en la Figura.

10

Figura 1: Integridad referencial entre la tabla Ventas y la tabla Titulos

Integridad definida por el usuario

La integridad definida por el usuario permite definir reglas de negocios específicas que no caigan dentro de alguna de las categorías anteriores. Todas las categorías soportan integridad definida por el usuario (todas las restricciones a nivel columna y a nivel tabla en el comando CREATE TABLE, procedimientos almacenados y desencadenadores)

Implementar restricciones de integridad

Una restricción es una propiedad asignada a una tabla o a una columna que previene que datos inválidos sean grabados en la o las columnas especificadas. Por ejemplo, una restricción UNIQUE o PRIMARY KEY previene de inserciones de valores que dupliquen un valor existente, mientras que las restricciones CHECK previenen de inserciones que no igualen una condición de búsqueda, y una restricción FOREIGN KEY asegura la consistencia de la relación entre dos tablas.

Introducción a las restricciones de integridad

La restricciones permiten definir la forma en que SQL Server automáticamente asegurará la integridad de la base de datos. Las restricciones definen reglas en base a los valores permitidos en las columnas y son los mecanismos estándar para asegurar la integridad. Se deberían usar restricciones en vez de desencadenadores, procedimientos almacenados, valores por defecto o reglas.

11

Las restricciones pueden ser restricciones de columnas o de tablas:

· Una restricción de columna es especificada como parte de la definición de la columna y se aplica solo a esta columna.

· Una restricción de tabla es declarada independientemente de las definiciones de la columna y se puede aplicar a mas de una columna en la tabla.

Las restricciones de tabla deben ser usadas cuando mas de una columna se incluye en la formulación de la condición. Por ejemplo, si una tabla tiene dos o mas columnas en la clave primaria, se debe usar una restricción de tabla para incluirlas a todas en la clave primaria. Supongamos una tabla que registra eventos que suceden en una computadora de una fábrica. Dicha tabla registra eventos de diferente tipo que pueden suceder al mismo tiempo, pero no pueden suceder dos eventos del mismo tipo al mismo tiempo. Esta regla puede ser forzada incluyendo a ambas columnas; tipos de eventos y tiempo, en una clave primaria de dos columnas, como se muestra en el siguiente comando CREATE TABLE:

CREATE TABLE Procesos ( TipoEvento int, TiempoEvento datetime, LugarEvento char(50), DescripEvento char(1024), CONSTRAINT event_key PRIMARY KEY (TipoEvento, TiempoEvento) )

SQL Server soporta cuatro clases principales de restricciones: PRIMARY KEY, UNIQUE, FOREIGN KEY y CHECK.

Restricciones PRIMARY KEY

Una tabla usualmente tiene una columna (o una combinación de columnas) que identifica unívocamente cada fila de la tabla. Esta columna (o columnas) son llamadas “clave primaria” de la tabla y aseguran la integridad de la entidad de la tabla. Se puede crear una clave primaria usando la restricción PRIMARY KEY cuando se crea o modifica la tabla.

Una tabla puede tener solo una restricción PRIMARY KEY, y ninguna columna que participa de la clave primaria puede aceptar nulos. Cuando se especifica una restricción PRIMARY KEY para una tabla, SQL Server 2000 asegura la unicidad de los datos creando un índice principal para las columnas de la clave primaria. Este índice permite, además, un acceso rápido a las filas cuando la clave primaria se usa para formular consultas.

Si se define la restricción PRIMARY KEY para mas de una columna, los valores se pueden duplicar para una columna, pero cada combinación de valores para todas las columnas de la clave principal de una fila debe ser única para toda la tabla. La figura muestra como las columnas Autor_ID y

12

Titulo_ID de la tabla TituloAutor forman una restricción PRIMARY KEY, la que asegura que las combinaciones Autor_ID Titulo_ID son únicas.

Figura 2: La clave primaria de la tabla TituloAutor en la base de datos pubs

Crear restricciones PRIMARY KEY

Se pueden crear restricciones PRIMARY KEY utilizando uno de los siguientes métodos:

· Crear la restricción cuando se crea la tabla

· Agregar la restricción a una tabla ya existente, siempre que no exista otra restricción PRIMARY KEY para esa tabla.

Se puede modificar o eliminar una restricción PRIMARY KEY después que ha sido creada.

Por ejemplo se podría querer que la restricción PRIMARY KEY de la tabla referencie a otras columnas, o querer cambiar el orden de las columnas, nombre de índice, agrupamiento o factor de llenado definido con un restricción PRIMARY KEY.

El siguiente comando CREATE TABLE crea la tabla Tabla1 y define la columna Col1 como clave primaria:

CREATE TABLE Tabla1 ( Col1 int PRIMARY KEY, Col2 varchar(30) )

Se puede definir la misma restricción utilizando la definición a nivel de tabla:

CREATE TABLE Tabla1 (Col1 int, Col2 varchar(30), CONSTRAINT tabla_pk PRIMARY KEY (Col1) )

13

Se puede usar el comando ALTER TABLE para agregar una restricción PRIMARY KEY a una tabla existente:

ALTER TABLE Tabla1 ADD CONSTRAINT tabla_pk PRIMARY KEY (Col1)

Cuando una restricción PRIMARY KEY se agrega a una columna (o columnas) existente en un tabla, SQL Server 2000 controla los datos ya existentes en las columnas para asegurar que se cumplen las siguientes reglas:

· No hay valores nulos

· No hay valores duplicados

Si se agrega una restricción PRIMARY KEY a una columna que tiene valores nulos o duplicados, SQL Server emite un mensaje de error y no agrega la restricción.

SQL Server automáticamente crea un índice único para asegurar la unicidad de los valores de la restricción PRIMARY KEY. Si no existe un índice agrupado ( o no se especifica un índice no-agrupado) se crea un índice único y no agrupado para asegurar la restricción PRIMARY KEY (ver índices mas adelante en este módulo).

Restricciones UNIQUE

Se pueden usar las restricciones UNIQUE para asegurar que no sean entrados valores duplicados en columnas específicas que no participan de la clave primaria. Aunque tanto la restricción PRIMARY KEY como la restricción UNIQUE aseguran unicidad, se debería usar UNIQUE en vez de PRIMARY KEY en los siguientes casos:

· Si una columna ( o combinación de columnas) no son la clave primaria. Se pueden definir muchas restricciones UNIQUE para una tabla, muientras que solo una restricción PRIMARY KEY,

· Si la columna permite valores nulos. Las restricciones UNIQUE permiten que se las defina para aceptar valores nulos, mientra que las restricciones PRIMARY KEY no lo permiten.

Una restricción UNIQUE pueden ser referenciadas por una restricción FOREIGN KEY.

14

Crear restricciones UNIQUE

Se pueden crear restricciones UNIQUE en el mismo modo que se crean restricciones PRIMARY KEY:

· Creando la restricción al momento de crear la tabla ( como parte de la definición de la tabla)

· Agregando la restricción a una tabla existente, previendo que la o las columnas comprendidas en la restricción UNIQUE contengan solo valores no duplicados o valores nulos. Una tabla puede aceptar múltiples restricciones UNIQUE.

Se pueden usar los mismos comandos Transact-SQL para crear restricciones UNIQUE que los utilizados para crear restricciones PRIMARY KEY. Simplemente reemplace las palabras PRIMARY KEY por UNIQUE. Al igual que con las restricciones PRIMARY KEY las restricciones UNIQUE pueden ser modificadas o eliminadas una vez creadas.

Cuando se agrega una restricción UNIQUE a una columna (o columnas) existente en la tabla, SQL Server (por defecto) controla los datos existentes en las columnas para asegurar que todos los valores, excepto los nulos, son únicos. Si se agrega una restricción UNIQUE a una columna que tienen valores no nulos duplicados, SQL Server genera un mensaje de error y no agrega la restricción.

SQL Server automáticamente crea un índice UNIQUE para asegurar la unicidad requerida por la restricción UNIQUE. Por lo que, si se intenta ingresar un nueva fila con valores duplicados para la columna (o combinación de columnas) especificada se genera una mensaje de error diciendo que ha sido violada la restricción UNIQUE y no se agrega la fila a tabla. Si no se especifica un índice agrupado, se creará un índice no-agrupado por defecto cuando se crea una restricción UNIQUE.

Restricciones FOREIGN KEY

Una clave ajena es una columna o combinación de columnas usadas para establecer y asegurar una conexión entre dos tablas. Al agregar una columna (o columnas) a una de las tablas y definir estas columnas con una restricción FOREIGN KEY se crea una conexión entre dos tablas. Las columnas tendrán únicamente valores que se encuentren en las columnas de la clave primaria de la segunda tabla.

Una tabla puede tener múltiples restricciones FOREIGN KEY.

Por ejemplo, la tabla Titulos en la base de datos Pubs tiene una conexión a la tabla Editores al haber una relación lógica entre autores y editores.

La columna Pub_ID en la tabla Titulos concuerda con la columna de clave principal en la tabla Editores, como muestra la Figura. La columna Pub_ID en la tabla Titulos es la clave ajena asociada la tabla Editores.

15

Se puede crear una clave ajena definiendo una restricción FOREIGN KEY cuando se crea o modifica una tabla. Además de a una PRIMARY KEY, una clave ajena puede referenciar a una restricción UNIQUE en otra tabla.

Una restricción FOREIGN KEY puede contener valores nulos; sin embargo, si cualquier columna de una restricción FOREIGN KEY compuesta contiene valores nulos, la verificación de la restricción FOREIGN KEY será omitida.

Una restricción FOREIGN KEY puede referenciar columnas en tablas de la misma base de datos o dentro de la misma tablas (tablas auto-referenciadas).

Crear restricciones FOREIGN KEY

Se pueden crear restricciones FOEREIGN KEY utilizando alguno de los siguientes métodos:

· Creando la restricción cuando se crea la tabla (como parte de la definición de la tabla).

· Agregando la restricción a una tabla existente, indicando que la restricción FOREING KEY esta conectada a una restricción PRIMARY KEY existente o a una restricción UNIQUE.

Se puede modificar o eliminar una restricción FOREIGN KEY una vez que esta ha sido creada.

Por ejemplo, se podría querer que la tabla de clave ajena referencie a otras columnas. No se puede cambiar la longitud de una columna definida con un restricción FOREIGN KEY.

Para modificar una restricción FOREIGN KEY utilizando Transact-SQL, se debe primero eliminar la restricción FOREIGN KEY anterior y luego recrearla con su nueva definición.

16

El siguiente comando CREATE TABLE crea la tabla Tabla1 y define la columna Col2 con una restricción FOREIGN KEY que apunta a la columna Empleado_ID que es clave primaria de la tabla Empleados.

CREATE TABLE Tabla1 (Col1 int PRIMARY KEY, Col2 int REFERENCES Empleados(Empleado_ID) )

Se puede definir, además la misma restricción usando la restricción FOREIGN KEY a nivel de tabla:

CREATE TABLE Tabla1 ( Col1 int PRIMARY KEY, Col2 int, CONSTRAIT col2_fk FOREIGN KEY (Col2) REFERENCES Empleados(Empleado_ID) )

Se puede usar el comando ALTER TABLE para agregar una restricción FOREIGN KEY a una tabla existente:

ALTER TABLE Tabla1 ADD CONSTRAIT col2_fk FOREIGN KEY (Col2) REFERENCES Empleados(Empleado_ID)

Cuando se agrega una restricción FOEREING KEY a una columna (o columnas) existentes en un tabla, SQL Server 2000 (por defecto) controla los datos existentes en las columnas para asegurar que todos los valores, excepto los nulos, existen en las columnas referenciadas por las restricciones PRIMARY KEY o UNIQUE. Se puede configurar al SQL Server para que no realice este control y obligarlo a agregar la nueva restricción sin fijarse en los datos previos, esto puede ser útil cuando se quiere que la restricción funcione solo de aquí en adelante.

De todos modos, se deberá ser cuidadoso cuando se agregan restricciones sin controlar la consistencia de los datos previos dado que se pueden provocar inconsistencias no deseadas.

Deshabilitar restricciones FOREIGN KEY

Se pueden deshabilitar restricciones FOREIGN KEY preexistentes cuando se realicen alguna de las siguientes acciones:

· Al ejecutar los comandos INSERT y UPDATE: Deshabilite una restricción FOREIGN KEY durante un comando INSERT o UPDATE si el dato nuevo violará la restricción o si la restricción se debe aplicar solo a datos ya existentes en la tabla. Deshabilitar restricciones permite que los datos sean modificados sin que sean validados por las restricciones.

17

· Al implementar procesos de replicación: Deshabilite una restricción FOREIGN KEY durante el proceso de replicación si la restricción es específica de la base de datos fuente. Cuando se replica una tabla, los datos y la definición de la tabla son copiados desde una base de datos fuente a una base de datos destino. Estas bases de datos están generalmente (pero no necesariamente) sobre servidores separados. Si las restricciones FOREIGN KEY específicas de la base de datos fuente no están deshabilitadas, estas podrían innecesariamente prevenir que nuevos datos sean ingresados en la base de datos destino.

Restricciones CHECK

Las restricciones CHECK aseguran la integridad de dominio al limitar los valores que son aceptados para una columna. Son similares a lar restricciones FOREIGN KEY en que ambas controlan los valores que son puestos en una columna. La diferencia está en cómo se determina cuales son valores válidos. Las restricciones FOREIGN KEY toman los valores válidos de otra tabla, mientras que las restricciones CHECK determinan los valores válidos evaluando una expresión lógica que no se basa en datos de otra columna. Por ejemplo, es posible limitar el rango de valores para una columna Salario creando una restricción CHECK que permita solamente datos dentro del rango de $15.000 a $100.000. Este capacidad evita el ingreso de salarios fuera del rango normal de salarios de la compañía.

Se puede crear una restricción CHECK con una expresión lógica (Booleana) que retorne TRUE (verdadero) o FALSE (falso) basada en operadores lógicos. Para permitir solamente datos que se encuentren dentro del rango de $15.000 a $100.000, la expresión lógica será como la siguiente:

Salario >= 15000 AND Salario <= 100000

Se puede aplicar múltiples restricciones CHECK para una sola columna. Las restricciones son evaluadas en el orden en que han sido creadas. Además, se puede aplicar una misma restricción CHECK a múltiples columnas creando la restricción a nivel de tabla. Por ejemplo, se puede usar una restricción CHECK para múltiples columnas para confirmar que cualquier fila con la columna País igual a USA tenga valor para la columna Estado que sea una cadena de dos caracteres. Esta posibilidad permite que múltiples condiciones sean controladas en un lugar.

Crear restricciones CHECK

Se pueden crear restricciones usando uno de los siguientes métodos:

· Creando la restricción cuando se crea la tabla (como parte de las definiciones de la tabla)

· Agregando la restricción a una tabla existente.

Se puede modificar o eliminar una restricción CHECK una vez que ha sido creada. Por ejemplo, se puede modificar la expresión usada por la restricción CHECK sobre una columna en la tabla.

Para modificar una restricción CHECK primero se debe eliminar la antigua restricción y luego recrearla con su nueva definición.

18

El siguiente comando CREATE TABLE crea una tabla Tabla1 y define la columna Col2 con un restricción CHECK que limita los valores que puede tomar la columna al rango comprendido entre 0 y 100.

CREATE TABLE Tabla1 ( Col1 int PRIMARY KEY, Col2 int CONSTRAIT monto_limite CHECK (Col2 BETWEN 0 AND 100), Col3 varchar(30) )

También se puede definir la misma restricción usando restricción CHECK a nivel tabla:

CREATE TABLE Tabla1 ( Col1 int PRIMARY KEY, Col2 int , Col3 varchar(30), CONSTRAIT monto_limite CHECK (Col2 BETWEN 0 AND 100) )

Se puede utilizar el comando ALTER TABLE para agregar una retricción CHECK a una tabla existente:

ALTER TABLE Tabla1 ADD CONSTRAIT monto_limite CHECK (Col2 BETWEN 0 AND 100)

Cuando se agrega una restricción CHECK a una tabla existente, la restricción CHECK puede aplicarse solo a los datos nuevos o también a los datos existentes. Por defecto la restricción CHECK se aplica a los datos existentes tanto como a los nuevos datos.

La opción de aplicar la restricción a los nuevos datos solamente es útil cuando las reglas de negocios requieren que la restricción se aplique de ahora en más.

Por ejemplo, una vieja restricción podría requerir códigos postales restringidos a 5 caracteres siendo los mismos aún válidos mientras que los nuevos códigos que se ingresen deberán tener nueve caracteres. Por lo que solo los datos nuevos deberían ser controlados para verificar que cumplen con la restricción.

Sin embargo, se debe ser cuidadoso cuando se agregan restricciones sin controlar los datos existentes, porque esta acción saltea los controles de SQL Server 2000 que aseguran la integridad para los datos de la tabla.

19

Deshabilitar restricciones CHECK

Se pueden deshabilitar restricciones CHECK preexistentes cuando se realicen alguna de las siguientes acciones:

· Al ejecutar los comandos INSERT y UPDATE: Deshabilite una restricción CHECK durante un comando INSERT o UPDATE si el dato nuevo violará la restricción o si la restricción se debe aplicar solo a datos ya existentes en la tabla. Deshabilitar restricciones permite que los datos sean modificados sin que sean validados por las restricciones.

Al implementar procesos de replicación: Deshabilite una restricción CHECK durante el proceso de

replicación si la restricción es específica de la base de datos fuente. Cuando se replica una tabla,

los datos y la definición de la tabla son copiados desde una base de datos fuente a una base de

datos destino. Estas bases de datos están generalmente (pero no necesariamente) sobre

servidores separados. Si las restricciones CHECK específicas de la base de datos fuente no están

deshabilitadas, estas podrían innecesariamente prevenir que nuevos datos sean ingresados en la

base de datos destino.

20

Transacciones en Transact SQL

Una transacción es un conjunto de operaciones que van a ser tratadas como una única unidad.

Transact SQL se ejecutan como un único bloque, es decir, si falla una operación, fallan todas.

Si una transacción tiene éxito, todas las modificaciones de los datos realizadas durante la transacción se confirman y se convierten en una parte permanente de la base de datos. Si una transacción encuentra errores y debe cancelarse o revertirse, se borran todas las modificaciones de los datos.

SQL Server funciona por defecto con Transacciones de confirmación automática es decir, cada

instrucción individual es una transacción y se confirma automáticamente.

Estas transacciones deben cumplir 4 propiedades fundamentales comúnmente conocidas como ACID:

Atomicidad

Coherencia

Asilamiento

Durabilidad

Por ejemplo una sentencia como esta:

UPDATE Products SET UnitPrice=20 WHERE ProductName =’Chai’

Esta es una transacción ‘autocommit’, una transacción autocompletada.

Cuando enviamos esta sentencia al SQL Server se escribe en el fichero de transacciones lo que va a ocurrir y a continuación realiza los cambios necesarios en la base de datos. Si hay algún tipo de problema al hacer esta operación el SQL Server puede leer en el fichero de transacciones lo que se estaba haciendo y si es necesario puede devolver la base de datos al estado en el que se encontraba antes de recibir la sentencia.

21

Especificar el tipo de almacenamiento en archivo mediante bcp (SQL Server)

El tipo de almacenamiento de archivo describe cómo se almacenan los datos en el archivo de datos. Los datos se pueden exportar a un archivo de datos como su tipo de tabla de base de datos correspondiente (formato nativo), como su representación en caracteres (formato de caracteres)

o como cualquier tipo de datos que admita la conversión implícita; por ejemplo, si copia datos smallint como datos int. Los tipos de datos definidos por el usuario se exportan como sus tipos base correspondientes.

Requisitos para restaurar las copias de seguridad del registro de

transacciones

Para aplicar una copia de seguridad del registro de transacciones, deben cumplirse los requisitos siguientes:

Suficientes copias de seguridad de registros para una secuencia de restauración: debe tener suficientes copias de seguridad de entradas de registro para poder completar una secuencia de restauración. Las copias de seguridad de registros necesarias, incluida la copia del final del registro si es necesaria, deben estar disponibles antes de iniciar la secuencia de restauración.

Orden de restauración correcto: primero debe restaurarse la copia de seguridad diferencial de la base de datos o la copia de seguridad completa inmediatamente anterior de la base de datos. A continuación, todos los registros de transacciones creados después de esa copia de seguridad completa o diferencial de la base de datos deben restaurarse en orden cronológico. Si se pierde o se daña una copia de seguridad del registro de transacciones en esta cadena de registros, solo puede restaurar los registros de transacciones anteriores al registro de transacciones que falta.

La base de datos no se ha recuperado todavía: no se puede recuperar la base de datos hasta que se haya aplicado el registro de transacciones final. Si recupera la base de datos después de restaurar una de las copias de seguridad intermedias del registro de transacciones, anterior al final de la cadena de registros, no podrá restaurar la base de datos más allá de ese punto sin reiniciar toda la secuencia de restauración, empezando por la copia de seguridad completa de la base de datos

22

Niveles de aislamiento en

SQL-SERVER

El tratamiento de los errores es uno de los puntos clave de cualquier aplicación bien terminada. Las transacciones, como concepto son un requisito imprescindible en cualquier aplicación sería. Recordemos la máxima A.C.I.D, es decir cualquier operación dentro de una base de datos tiene que cumplir A, Atomicidad, dentro de una transacción las operaciones que se realizan deben poder considerarse como una sola. C. Consistencia, cualquier operación que sea validada o cancelada no puede dejar datos inconsistentes (por ejemplo violando reglas de integridad referencial). I (Isolation en inglés) Aislamiento, El gestor de la base de datos debe aislar los datos 'sucios' para evitar que otros usuarios usen información no confirmada o validada. D. Durabilidad, los datos confirmados no pueden perderse. Estas reglas son las que definen cualquier base de datos seria; su comprensión y entendimiento lo que hace que podamos desarrollar grandes aplicaciones con altos niveles de concurrencia y buenos tiempos de respuesta. Fijémonos en la I, (Isolation). ¿Que significa el aislamiento? En concepto lo que nos dice es que una operación que se ejecuta dentro de una transacción debe ocultar sus datos o al menos garantizar que estos permanecen aislados con respecto a otras transacciones de otros usuarios. Esto podría suponer un número de bloqueos muy grande y por tanto una deficiente escalabilidad de la solución. De forma que tendremos que optar por la solución de compromiso que, garantizándonos en la medida que necesitemos el aislamiento, permita también un gran nivel de concurrencia.

Esto puede implementarse de varias formas, de hecho ANSI tiene una definición de los niveles de aislamiento, en donde se indica de forma más clara que significa aislar. Los niveles de aislamiento definidos por ANSI en la definición SQL-92 son cuatro y son los siguientes: Lecturas no confirmadas Lecturas confirmadas Lecturas repetibles Nivel de aislamiento Serializable Tabla de comportamientos Un caso particular

Lecturas No Confirmadas Lecturas no confirmadas, nivel más bajo de aislamiento solamente protege de lecturas de datos físicamente dañados. Es el que produce mayor nivel de concurrencia (nunca hay un bloqueo), pero también el que no garantiza en absoluto la coherencia de los datos. Veámoslo con un ejemplo; Juan, director comercial de una firma está pensando en incrementar los precios de ciertos artículos en un 50%, por eso ejecuta una instrucción en la que incrementa el precio de todos los artículos en ese 50%, El precio de una camiseta que costaba 30,00 € pasa a ser en este instante de

23

45,00 €, aunque como no lo tiene muy claro todavía no lo ha confirmado. En ese instante Luis, comercial de la misma compañía, crea una factura a su cliente en la que le vende la camiseta de 30,00 € a 45,.00 €. Después, nuestro indeciso Juan deshace la transacción esperando a que el gerente vuelva de vacaciones para cambiar los precios, mientras que Luis confirma el pedido. El resultado, Luis ha vendido una camiseta a un precio que nunca ha existido en la base de datos.

Ver el registro de errores de SQL Server

Consulte el registro de errores de SQL Server para asegurarse de que los procesos se han completado correctamente; por ejemplo, operaciones de copias de seguridad y restauración, comandos de un proceso por lotes u otras scripts o procesos. Esto puede resultar útil para detectar áreas con problemas actuales o posibles, incluidos mensajes de recuperación automática (especialmente si se ha detenido y reiniciado una instancia de SQL Server), mensajes del kernel, etc.

Para ver el registro de errores de SQL Server, utilice SQL Server Management Studio o cualquier procesador de texto. De forma predeterminada, el registro de errores está ubicado en los archivos Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG y ERRORLOG.n.

Cada vez que se inicia una instancia de SQL Server se crea un registro de errores, pero se puede utilizar el procedimiento almacenado del sistema sp_cycle_errorlog para reciclar archivos de registro de errores sin tener que reiniciar la instancia de SQL Server. Normalmente, SQL Server conserva copias de seguridad de los últimos seis registros y asigna a la copia de seguridad del registro más reciente la extensión .1, a la segunda más reciente la extensión .2 y así sucesivamente. El registro de errores actual no tiene ninguna extensión.

Seguridad de la plataforma y de la red

La plataforma de SQL Server incluye el hardware físico y los sistemas de redes que conectan los clientes con los servidores de base de datos, así como los archivos binarios que se utilizan para procesar solicitudes de base de datos.

Seguridad física

Las recomendaciones de seguridad física limitan de forma estricta el acceso al servidor físico y a los componentes de hardware. Por ejemplo, use salas cerradas de acceso restringido para el hardware de servidor de base de datos y los dispositivos de red. Además, limite el acceso a los medios de copia de seguridad almacenándolos en una ubicación segura fuera de las instalaciones.

La implementación de la seguridad de la red física comienza por mantener a los usuarios no autorizados fuera de la red. En el siguiente gráfico se incluye más información sobre la seguridad de la red.

24

SQL Server nos proporciona distintos mecanismos de seguridad a través de la autenticación al sistema de base de datos y la autenticación a la base de datos en particular. Así un usuario solo puede acceder a los datos únicamente si se encuentra autenticado en el sistema de base de datos y autenticado en la base de datos a la cual quiere acceder.

Nuestra aplicación podría hacer uso de este mecanismo para implementar la seguridad.

Pero hay una cuestión interesante que debemos tener en cuenta al momento de diseñarla. Una de ellas es definir que usuario utilizaremos en el momento de establecer la conexión con la base de datos. Podríamos utilizar el usuario administrador (sa) de bases de datos, algo muy común en desarrolladores principiantes, pero muy peligroso pues estaríamos revelando la contraseña de administrador de base de datos tanto a los programadores como a los usuarios comunes en el caso de que se utilice un login en la aplicación.

Además, si bien podemos restringir el acceso a los datos del lado de la aplicación al

programar solo la visualización de parte de la información de nuestra organización, no lo hacemos del lado de la base de datos, pues con el analizador de consulta que posee SQL Server 2000, o simplemente con un aplicación que permita el acceso a datos mediante ODBC como Microsoft Excel, es posible acceder a cualquier tabla de nuestra base de datos, más aun conociendo la contraseña de administrador y con la consiguiente posibilidad de causar daños tanto accidentales como intencionados a ésta.

Otra desventaja que posee la cuenta de administrador (sa) es una mayor vulnerabilidad a

ataques por fuerza bruta para descubrir su contraseña. Una solución a esto es que definamos usuarios de base de datos, únicamente con los

permisos requeridos para su desempeño y permitamos que cada usuario inicie una sesión en el servidor a través de la aplicación.

Puede ampliar la imágen haciendo click

Por ejemplo creamos el Usuario ‘Juan’ con la contraseña ‘Clave’:

25

sp_addlogin 'Juan', 'Clave' GO Le damos acceso a la base de datos Northwind: USE Northwind GO sp_grantdbaccess 'Juan' GO

SEGURIDAD

Cada versión de SQL Server incluye diferentes características de seguridad, al igual que cada versión de Windows, y las versiones posteriores cuentan con funcionalidad mejorada con respecto a las anteriores.

Es importante comprender que las características de seguridad no pueden garantizar por sí solas una aplicación de base de datos segura.

Algunas aplicaciones que son locales en cuanto al ámbito pueden necesitar una seguridad mínima, en tanto que otras aplicaciones locales o las aplicaciones implementadas en Internet pueden precisar medidas estrictas de seguridad y supervisión y evaluación continuas.

La evaluación de las amenazas en las primeras fases del ciclo de desarrollo permite reducir al mínimo los posibles daños cuando se detecte una vulnerabilidad.

Una estrategia de defensa exhaustiva, con niveles superpuestos de seguridad, es la mejor manera de enfrentarse a las amenazas a la seguridad.SQL Server proporciona una arquitectura de seguridad diseñada para permitir a los administradores de bases de datos y desarrolladores crear aplicaciones de base de datos seguras y contrarrestar las amenazas. En cada versión de SQL Server se han introducido mejoras a las versiones anteriores con nuevas características y funcionalidades.

Cada aplicación tiene requisitos de seguridad propios. Los desarrolladores tienen que saber cuál es la combinación de características y funcionalidades más apropiada para contrarrestar las amenazas conocidas, así como anticiparse a las que puedan ir apareciendo en el futuro.

Una instancia de SQL Server contiene un conjunto jerárquico de entidades, empezando por el servidor. Cada servidor contiene varias bases de datos y, a su vez, cada base de datos contiene un conjunto de objetos susceptibles de ser protegidos. Cada SQL Server que se protege tiene permisos asociados que se pueden conceder a una entidad de seguridad, la cual a su vez puede ser un elemento único, un grupo o un proceso al que se le otorga permiso de acceso a SQL Server.

26

La arquitectura de seguridad de SQL Server administra el acceso a entidades protegidas mediante autenticación y autorización.

La autenticación es el proceso de inicio de sesión en SQL Server por el que una entidad de seguridad solicita el acceso mediante el envío de credenciales que el servidor evalúa. La autenticación establece la identidad del usuario o proceso que se autentica.

La autorización es el proceso con el que se determinan los recursos susceptibles de protegerse a los que tiene acceso una entidad de seguridad, así como las operaciones que les están permitidas a dichos recursos.

Recursos de los bloqueos

El establecimiento de bloqueos es automático en SQL Server. El gestor de bloqueos es el

encargado de decidir el tipo y la granularidad del bloqueo.

Los bloqueos se pueden producir tanto a nivel de página de datos como a nivel de página de

índice. El gestor de bloqueo puede decidir bloquear los recursos: tabla, extensión, página, fila y

rango de clave.

La granularidad del bloqueo incide de forma directa sobre la concurrencia y el rendimiento del

sistema. El optimizador de consultas decide la granularidad del bloqueo automáticamente. No

obstante, SQL Server proporciona extensiones de T-SQL que proporcionan un mecanismo para

controlar la granularidad de los bloqueos.

Tipos de bloqueo en SQL Server

El procedimiento almacenado “sp_lock” proporciona los bloqueos activos en el sistema gestor de

bases de datos. También ofrecen información sobre bloqueos el administrador corporativo, el

monitor de rendimiento, el administrador de memoria y el objeto de SQL Server bloqueos.

Dependiendo del tipo de transacción, el gestor de bloqueos establece distintos tipos de bloqueos.

Bloqueos compartidos

Se establecen para operaciones de sólo lectura, por ejemplo “select”.

Varias transacciones pueden mantener varios bloqueos compartidos simultáneamente sobre el

mismo recurso ya que dichas transacciones no interfieren entre sí.

Si una transacción intenta modificar un dato de un recurso sobre el que se ha establecido un

bloqueo compartido, se bloqueará hasta que todos los bloqueos compartidos se liberen.

Un bloqueo compartido se libera en cuanto se finaliza la operación de lectura de datos, es decir,

no permanece a lo largo de la transacción. Existen cláusulas de las que se hablará más adelante

para modificar este comportamiento.

Bloqueos de actualización

Los bloqueos de actualización se usan para instrucciones que modifican datos.

27

Cuando una transacción intenta actualizar una fila primero ha de leerla para asegurarse de que la

fila en cuestión es realmente la que se quiere modificar. En este momento establece un bloqueo

compartido. Una vez se ha asegurado que es la fila correcta procederá a modificarla, para lo que

necesita establecer un bloqueo exclusivo que será liberado al terminar la transacción. Los

bloqueos de actualización se emplean como bloqueo intermedio entre los dos anteriores para

evitar interbloqueos.

Bloqueos exclusivos

Se concede un bloqueo de este tipo a una transacción cuando ésta está lista para modificar los

datos. Un bloqueo exclusivo sobre un recurso asegura que ninguna otra transacción pueda

interferir las acciones llevadas a cabo por la primera transacción sobre los recursos bloqueados.

SQL Server libera el bloqueo al finalizar la transacción.

Bloqueos de intención

El bloqueo de intención es un mecanismo que utilizan las transacciones para declarar su intención

de obtener un bloqueo compartido, de actualización o exclusivo sobre un recurso.

No establece ningún bloqueo en si, excepto por el hecho de que otra transacción no podrá

adquirir un bloqueo de actualización sobre un recurso sobre el que se haya establecido un

bloqueo de intención.

Bloqueos de esquema

Se utilizan para mantener la integridad estructural de las tablas SQL Server. A diferencia de otros

bloqueos, que proporcionan aislamiento para los datos, los bloqueos de esquema proporcionan

aislamiento para el esquema de objetos de la base de datos.

Compatibilidad de bloqueos

En la siguiente lista se muestra la compatibilidad entre los distintos tipos de bloqueo. Para cada

bloqueo que puede llegar a estar activo sobre una conexión al sistema gestor de bases de datos

SQL Server se lista qué tipos de bloqueo pueden solicitarse concurrentemente (por ejemplo, si

existe un bloqueo de modificación de esquema no puede solicitarse ningún otro tipo de bloqueo,

tal y como muestra el último elemento de esta lista).

De intención compartido: de intención compartido, compartido, de actualización, de intención exclusivo, compartido de intención exclusivo y de estabilidad de esquema.

Compartido: de intención compartido, compartido, de actualización y de estabilidad de esquema.

De actualización: de intención compartido, compartido y de estabilidad de esquema. De intención exclusiva: de intención compartido, de intención exclusiva y de estabilidad de

esquema. Compartido de actualización exclusivo: de intención compartida y de estabilidad de

esquema. Exclusivo: estabilidad de esquema. Estabilidad de esquema: de intención compartido, compartido, de actualización, de

intención exclusivo, compartido de intención exclusivo y de estabilidad de esquema.

28

Modificación de esquema: ningún bloqueo es compatible con el bloque de modificación de esquema.

Bloqueos de índices

De forma similar a los bloqueos de páginas de datos, SQL Server gestiona los bloqueos de las

páginas de índices internamente.

Con el comando “sp_indexoption” se puede modificar el gestor permitiendo o no bloqueos a nivel

de filas.

Se recomienda no realizar modificaciones sobre el comportamiento por defecto del gestor de

bloqueos en lo referente a las páginas de índices ya que suele hacer buenas elecciones.

DB2

RECUPERACION

En prevención de que los datos se pierdan o borren por error o accidente conviene sacar copias de

los mismos con frecuencia. O utilizar programas ajenos al DBMS de copias de discos.

Dentro de las utilidades de recuperación de DB2 se tienen:

Utilización de registros (Anotación

Cronológica o Logging)

29

Se utiliza el comando COMMIT (similar a SAVE) para registrar los cambios en los datos. Estos se

guardan en archivos de anotación cronológica (logs), que se almacenan en discos o cintas.

Visión general en forma gráfica desde el punto de vista de anotación cronológica:

En la figura anterior se pueden apreciar los archivos de anotación cronológica (LOGS) y el espacio

de tabla (unidad de almacenamiento de las BD para administrar los objetos), en este caso, ambos

se guardan en el mismo disco (no recomendable). Cuando se realiza una transacción, ésta requiere

de datos que son extraídos hacia la agrupación de memoria intermedia (memoria física), y los

cambios se realizan aquí, los valores anteriores y nuevos se almacenan en LOGS. Si se ejecuta un

COMMIT estos valores se guardan inmediatamente, se puede utilizar el parámetro

“chngpgs_tresh” para especificar que se desea alcanzar el límite de páginas modificadas (páginas

sucias) sin que sean exteriorizadas (guardados en disco).

Tipos de Logs

Existen dos tipos de anotación cronológica:

LOGS primarios: están predefinidos en el disco y el número de ellos disponible se

determina mediante el parámetro LOGPRIMARY.

LOGS secundarios: son definidos dinámicamente y para determinar el número máximo

disponible de éstos, se utiliza el parámetro LOGSECOND. Se puede determinar que sea

infinito (ad infinitum) mediante LOGSECONF-1, pero no se recomiendo porque podría

llenar todo el espacio en disco.

Tipos de Logging

Existen dos tipos de anotación cronológica:

30

Anotación cronológica circular: se activa por default al crear la BD. Se tiene el siguiente

ejemplo:

Hay tres LOGS primarias (LOGPRIMARY es 3), a medida una transacción se procesa, LOG

primaria P1 comienza a llenarse, y luego sigue P2. Si ocurre un COMMIT, los datos son

exteriorizados, entonces P1 y P2 pueden ser reutilizados, ya que la información que tienen

ya no es necesaria para una recuperación de una caída.

Anotación cronológica con archivo: también se llama anotación cronológica retenida, a

diferencia de la circular donde los LOGS se reutilizan, en esta se archivan, ya sea en línea o

fuera de línea. Las que están en línea se mantienen junto al LOG activo, y las fuera de línea

se almacenan en otros dispositivos.

Backup

Copia de los datos para almacenarlos en un medio diferente en caso de falla o daño a los

originales.

El comando para realizar una copia de seguridad mediante el comando BACKUP. El directorio debe

existir antes de realizar la operación. Si no se especifica que el comando es en modo en de línea

(ONLINE – otros usuarios pueden estar conectados y ejecutar operaciones sobre la base de datos

al mismo tiempo en que el comando se ejecuta) por default se refiere a un comando en modo

31

fuera de línea (ningún otro usuario puede estar conectado a la BD mientras este comando se

ejecuta).

Comando:

Donde:

BACKUP es el comando para la copia de seguridad

DATABASE <nombre> para el nombre de la BD

[TO <directorio>] se especifica el lugar donde se guarda la copia de seguridad (debe existir

previamente)

Ejemplo:

Además hay que asegurarse de que no hay conexiones a la BD en el momento de ejecutar el

BACKUP, ya que se recibirá un mensaje de error, ya que un respaldo fuera de línea no puede

ejecutarse mientras haya conexiones. Para ver si las hay, se ejecuta el comando:

Cuando se termina de hacer el respaldo, se genera de manera física un archivo con el siguiente

formato:

Donde:

Alias: es el nombre del respaldo.

Tipo: si es 0 es un respaldo completo, si es 3 es un respaldo de espacio de tabla, si es 4

significa que es un respaldo generado por una utilidad de respaldo.

Instancia: Esta determinado por el número de secuencia.

32

Nodo: si es siempre NODE0000 es para BD no particionadas, de lo contrario es un respaldo

para BD particionadas.

Nodo del catálogo: CATN0000, sucede lo mismo para BD no particionadas.

El resto es para la fecha de creación y el número de ésta.

Programas de utilidad:

o COPY: máximo 4 copias de tablas, índices y conjuntos de datos:

FIC: copia todo lo anterior

IIC: copia los últimos cambios desde la copia anterior

o MERGECOPY: sirve para unificar copias, como para crear una copia completa a

partir de incrementales.

o BACKUP SYSTEM: copia los volúmenes en los que reside la configuración de DB2.

o RECOVER: recupera los datos al estado actual o a un punto anterior mediante una

copia.

o REBUILLD INDEX: Construye índices de las tablas a las que hace referencia.

Tipos de Recuperación

Recuperación de caída o reinicio: Al volver a iniciar la máquina y DB2 se ejecutará

automáticamente el comando RESTART DATABASE, utilizando LOGS para recuperar la

consistencia de la BD, se reharán o desharán las transacciones, pero solo lo que se ha

confirmado lo que no, volverá hacia atrás.

Recuperación de versión o imagen: se utiliza una imagen de respaldo para poner la BD en

el momento en el que se hizo el BACKUP. Las transacciones ejecutadas después de ese

momento se perderán.

Recuperación en avance: además de utilizar el BACKUP, se utilizan LOGS, mediante el

comando ROLLFORWARD para recuperar hasta un punto en específico a la BD.

Restore

Para recuperar una BD desde una imagen de respaldo se usa el comando RESTORE. Su forma es:

Donde:

RESTORE: es el comando de restauración.

33

DATABASE <nombre_de_la_base_de_datos>: es el nombre de la BD.

[from <directorio>]: se especifica la ruta del respaldo.

[taken at <indeicación_de_fecha_y_hora>]: se especifica el año, mes, día, hora, minuto,

segundo y secuencia como se muestra en el final de la siguiente imagen.

Otras opciones con Backup y Restore

Respaldar una base de datos en una instancia de 32-bit y recuperarla en una de 64-bit

Recuperar sobre una base de datos existente

Usar recuperación redirigida para recuperar en un sistema con un número diferente de

discos a los especificados en la imagen de respaldo

Respaldar o recuperar a nivel de espacio de tabla en vez de la base de datos completa

Realizar respaldos delta e incrementales; los respaldos delta registran solo los cambios

entre respaldos, mientras que los incrementales registran todos los cambios y los

acumulan en cada imagen de respaldo.

34

Seguridad (Protección)

Autenticación

Antes de conectarse a una instancia de DB2, o conectarse a una base de datos DB2, los usuarios deben autenticarse. Autenticación es el proceso de validar que los usuarios son quienes dicen ser. Puede configurar DB2 para autenticar usuarios a través del sistema operativo, a través de un Lightweight Directory Access Protocol (LDAP), o por medio de Kerberos. Además, DB2 soporta la autenticación de plug-ins, para que tenga la flexibilidad necesaria para configurar DB2 para aproximarse más a sus necesidades de autenticación específicos.

Lightweight Directory Access Protocol

(LDAP).

Protocolo de aplicación sobre TCP/IP que permite el acceso a un servicio de directorio; estos directorios almacenan información referente a elementos de redes de PC, usuarios o documentos. Habitualmente, almacena la información de autenticación (usuario y contraseña) y es utilizado para autenticarse aunque es posible almacenar otra información (datos de contacto del usuario, ubicación de diversos recursos de la red, permisos, certificados, etc). A manera de síntesis, LDAP es un protocolo de acceso unificado a un conjunto de información sobre una red. Un cliente inicia una sesión de LDAP conectándose a un servidor LDAP, por defecto en el puerto TCP 389. El cliente luego envía una petición de operación al servidor, y el servidor envía respuestas. Con algunas excepciones, el cliente no necesita esperar una respuesta antes de enviar la siguiente petición, y el servidor puede responder en cualquier orden. El cliente puede requerir las siguientes operaciones: Start TLS — usar la extensión Transport Layer Security (TLS) LDAPv3 para una conexión segura Bind — autenticarse y especificar una versión del protocolo LDAP Search — buscar y obtener entradas de directorio Compare — probar si una entrada nombrada contiene un valor de atributo dado Add — Añadir una nueva entrada Delete — Borrar una entrada Modify — Modificar una entrada Modify Distinguished Name (DN) — Modificar o renombrar una entrada Abandon — abortar una petición previa Extended Operation — operación genérica usada para definir otras operaciones Unbind — cerrar la conexión (no es el inverso de Bind) Además, el servidor puede enviar "notificaciones no solicitadas" que no son respuestas a ninguna petición, por ejemplo antes de que se termine el tiempo de conexión.

Kerberos.

35

Kerberos se basa en el Protocolo de Needham-Schroeder. Usa un tercero de confianza, denominado "centro de distribución de claves" (KDC, por sus siglas en inglés: Key Distribution Center), el cual consiste de dos partes lógicas separadas: un "servidor de autenticación" (AS o Authentication Server) y un "servidor emisor de tiquets" (TGS o Ticket Granting Server). Kerberos trabaja sobre la base de "tickets", los cuales sirven para demostrar la identidad de los usuarios.

Kerberos mantiene una base de datos de claves secretas; cada entidad en la red —sea cliente o servidor— comparte una clave secreta conocida únicamente por él y Kerberos. El conocimiento de esta clave sirve para probar la identidad de la entidad. Para una comunicación entre dos entidades, Kerberos genera una clave de sesión, la cual pueden usar para asegurar sus interacciones.

Autorización

Cuando un usuario se autentica, DB2 realiza una comprobación de autorización. La autorización es el proceso en el que el gestor de base de datos DB2 verifica que el usuario está autorizado para realizar ciertas operaciones sobre los datos o recursos específicos. Los usuarios pueden recibir privilegios específicos a una fuente de datos determinada o dar funciones predefinidas conocidas como autoridades. DB2 también le permite crear sus propios roles de base de datos para agrupar varios privilegios juntos para que pueda conceder a la vez a un usuario o grupo.

Los contextos fiables

Contextos fiables proporcionan un método para construir más rápido y aplicaciones de tres niveles más seguros. Los contextos fiables abordar muchas preocupaciones de seguridad en el modelo de aplicación de tres niveles, como la pérdida de la identidad del usuario, la responsabilidad del usuario, y la concesión de privilegios innecesarios para acceder a cierta información. En DB2 contextos de confianza, la identidad del usuario se envía a DB2 dentro de una conexión de confianza con fines de auditoría y autorización. Además, los contextos fiables permiten controlar cuando algunos usuarios pueden ejercer los privilegios otorgados a los mismos. Por ejemplo, puede utilizar los contextos fiables para limitar a un usuario para conectarse a la base de datos de determinadas direcciones IP.

Auditoría

DB2 incluye un recurso de comprobación que le permite controlar el acceso a datos y proporciona la información necesaria para su posterior análisis. La auditoría puede ayudar a descubrir el acceso no deseado, desconocido, e inaceptable para los datos, así como mantener los registros de la historia de las actividades en el sistema de base de datos.

Control de acceso a fila y columna.

DB2 incluye Control de acceso a fila y columna que es un grano fino de seguridad. Puede utilizar la fila y la columna de control de acceso para restringir las filas y ocultar las columnas que ve el

36

usuario. El control de acceso es transparente para el usuario, que no son conscientes de la existencia de las filas no autorizadas.

Label-Based Access Control

Para el control de acceso de datos final, DB2 incluye Etiqueta de control de acceso basado en funciones (LBAC). LBAC proporciona varios niveles de seguridad para la gestión de datos clasificados. Una vez que las normas de LBAC se han definido, el control de acceso a datos está gestionado por DB2 y es completamente transparente para el usuario.

Cifrado

Puede evitar que los hackers puedan ver sus datos mientras está siendo transmitida a través de la red mediante mecanismos de cifrado de DB2. DB2 da soporte al cifrado de los ID de usuario, la contraseña y los datos mientras están en tránsito. También puede utilizar el DB2 Secure Socket Layer (SSL) para cifrar su capacidad de cliente a servidor de comunicaciones con el estado de tecnología de cifrado.

DB2 tiene unos sistemas de autorización que permiten proteger cualquier objeto de la base de datos del acceso o manipulación indebida.

Métodos:

Vista local: Expresión genérica que se corresponde con algún subconjunto de la DB que puede ser manipulado por hasta un grupo de usuarios. El o los usuarios no tienen acceso a ningún dato que no sea parte de su vista local. DB2 soporta el concepto de vista local mediante el comando CREATE VIEW.

Aplicación de diferentes niveles de seguridad que consta de (1) identificar quién es el usuario, (2) qué objeto se referencia y (3) qué proceso puede ejecutarse respecto al objeto especificado. DB2 soporta la sentencia GRANT y REVOKE que se utilizan para conceder y, a continuación, revocar privilegios de la DB.

37

CREATE VIEW:

CREATE VIEW CISC AS

SELECT CNOMBRE, CON, CRED, CTARIFA,

CDESCP

FROM CURSO

WHERE CDEPT = ‘CIS’

Nombre de la vista: CISC.

Columnas de izq. a der.

De la tabla CURSO.

Sólo si el campo CDEPT=‘CIS’

GRANT:

GRANT priv_1, priv_2, …

ON objeto

TO usuario_1, usuaio_2,…

Asignación de privilegios

(acciones).

Objeto: tabla o vista.

TO: Usuarios a quienes se

les asignan los privilegios.

REVOKE:

REVOKE priv_1, priv_2, …

ON objeto

FROM usuario_1, usuaio_2,…

Asignación de privilegios

(acciones).

Objeto: tabla o vista.

FROM: Usuarios a quienes

se les revocan los

privilegios.

38

CONSISTENCIA

Bloqueo Mortal o Deadlock, causas y

detección

Un bloqueo mortal o deadlock ocurre cuando dos o más aplicaciones conectadas a la misma base de datos esperan indefinidamente por un recurso. La espera nunca es resuelta porque cada aplicación está esperando un recurso que la otra necesita. En la mayoría de los casos los Deadlocks son un problema de diseño de la aplicación.

A está sosteniendo el cereal raisin bran y no lo soltará hasta que él tenga la leche. En la otra mano,

el usuario B está sosteniendo la leche, y no la dejará hasta que tenga el cereal raisin bran. Por lo

tanto, tenemos una situación de bloqueo mortal o deadlock.

Para simular una situación de deadlock en DB2, siga estos pasos:

1. Abra dos ventanas de comandos de DB2 (las cuales serán llamadas “CLP1” y “CLP2”,

respectivamente) representando dos diferentes aplicaciones que se conectan a la base de

datos.

2. Desde CLP1 ejecute los comandos: db2 connect to sampledb2 +c update employee set firstnme = 'Mary' where empno = '000050' Primero nos conectamos a la base de datos SAMPLE, y luego ejecutamos una sentencia UPDATE en la fila con “empno = 50000” en la tabla employee. La opción “+c” en la sentencia indica que nosotros no queremos que la ventana de comandos deDB2 automáticamente ejecute un COMMIT luego de esta sentencia. Esto lo hacemos deliberadamente porque queremos mantener el bloqueo.

3. Desde CLP2 ejecute los comandos: db2 connect to sampledb2 + c update employee set firstnme = 'Tom' where empno = '000030'En la ventana CLP2, que representa la segunda aplicación, estamos conectando a la base de datos SAMPLE, pero estamos actualizando otra fila en la tabla employee.

4. Desde CLP1 ejecute:db2 +c select firstnme from employee where empno = '000030'Después de presionar Enter para ejecutar la sentencia SELECT, puede parecer que el SELECT se queda en un estado de no respuesta. Realmente no lo está, si no que está esperando por el liberamiento de un bloqueo exclusivo (o exclusive lock) que fue tomado por el CLP2 en esa fila en el paso 3. En este punto, si LOCKTIMEOUT ha sido dejado con su valor predeterminado de -1, la aplicación CLP1 podría esperar eternamente.

5. DesdeCLP2 ejecute:db2 +c select firstnme from employee where empno = '000050'Al ejecutar la sentencia SELECT, estamos creando un deadlock. Esta sentenciaSELECT también parecerá quedarse en un estado de no respuesta, porque está esperando por el liberamiento del bloque exclusivo (o exclusive lock) que fue tomadopor CLP1 en esta fila en el paso 2.En este ejemplo de deadlock, DB2 verifica el valor del parámetro de configuración DLCHKTIME en la base de datos. Este parámetro fija el intervalo de tiempo

39

para verificar si un deadlock ocurrió. Por ejemplo, si este parámetro es fijado a 10 segundos, DB2verificará cada 10 segundos si un deadlock ha ocurrido. Si un deadlock ha ocurrido, DB2usará un algoritmo interno para determinar cuál de las dos transacciones podría ser rolledback (deshecha), y cuál debería continuar. Si usted está experimentando numerosos deadlocks, usted debería examinar sus transacciones existentes y ver si una reestructuración es posible.

INTEGRIDAD

El DB2 incluye características de Integridad, asegurando la protección de los datos aún en caso de que los sistemas sufran un colapso, y de Seguridad permitiendo realizar respaldos en línea, sin que esto afecte la disponibilidad de acceso a los datos por parte de los usuarios.

El objetivo en cuanto a la integridad es proteger la base de datos contra operaciones que introduzcan inconsistencias en los datos, por eso hablamos de integridad en el sentido de corrección, validez o precisión de los datos de la base. El subsistema de integridad de un SGBD debe, por tanto, detectar y corregir, en la medida de lo posible, las operaciones incorrectas. Existen dos tipos de operaciones que pueden atentar contra la integridad de los datos que son las operaciones semánticamente inconsistentes y las interferencias debidas a accesos concurrentes.

Las restricciones son usadas por DB2 para Linux, UNIX y Windows (DB2 LUW) con el fin de hacer cumplir reglas para los datos. A continuación se describen los siguientes tipos de restricciones:

NOT NULL (NO NULA)

Unique (de clave única)

Primary key (Clave primaria)

Foreign key (Clave externa)

Table check (Control de tablas)

Existe otro tipo de restricción conocida como restricción de información. A diferencia de los cinco tipos de restricciones que se enumeran anteriormente, la restricción de información no la impone el administrador de la base de datos, sino que puede ser usada por el compilador de SQL para mejorar el rendimiento de las consultas.

Se pueden definir una o más restricciones a DB2 cuando se crea una tabla nueva, o se pueden definir algunas de ellas alterando la tabla en un momento posterior. La instrucción CREATE TABLE (CREAR TABLA) es una instrucción muy compleja. De hecho, es tan complejo que si bien se usa apenas una pequeña fracción de sus opciones para las definiciones de las restricciones, estas opciones pueden ser bastante complejas cuando aparecen en un diagrama de sintaxis.

40

41

La gestión de restricciones puede resultar más simple y más conveniente cuando se realiza por medio del

DB2 Control Center.

Las restricciones que usted cree son manejadas como cualquier otro objeto de la base de datos. Tienen un nombre, un esquema asociado (ID del creador), y en algunos casos, pueden abandonarse (eliminarse).

Información de las restricciones en el catálogo de la base de datos

Vista del catálogo Ver columna

Descripción Ejemplo de consulta

SYSCAT.CHECKS Contiene una fila para cada restricción de verificación de tabla

db2 select constname, tabname, text from syscat.checks

SYSCAT.COLCHECKS

Contiene una fila para cada columna a la que hace referencia una restricción de verificación de tabla

db2 select constname, tabname, colname, usage from syscat.colchecks

SYSCAT.COLUMNS NULLS Indica si una columna es anulable (Y) o no (N)

db2 select tabname, colname, nulls from syscat.columns where tabschema = 'DELSVT' and nulls = 'N'

SYSCAT.CONSTDEP Incluye una fila para cada dependencia de una restricción u otro objeto

db2 select constname, tabname, btype, bname from syscat.constdep

SYSCAT.INDEXES Incluye una fila para cada índice.

db2 select tabname, uniquerule, made_unique, system_required from syscat.indexes where tabschema = 'DELSVT'

SYSCAT.KEYCOLUSE

Incluye una fila para cada columna que participa en una clave definida por una restricción de clave única primaria, o claves externas

db2 select constname, tabname, colname, colseq from syscat.keycoluse

SYSCAT.REFERENCES Incluye una fila para cada restricción de referencia

db2 select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references

SYSCAT.TABCONST

Incluye una fila por cada restricción de clave única (U), clave primaria (P), clave externa (F), o verificación de tabla (K)

db2 select constname, tabname, type from syscat.tabconst

SYSCAT.TABLES PARENTS Cantidad de tablas principales db2 "select tabname, parents

42

(parent) de esta tabla (cantidad de restricciones de referencia de las cuales depende esta tabla)

from syscat.tables where parents > 0"

SYSCAT.TABLES CHILDREN

Cantidad de tablas secundarias (children) de esta tabla (cantidad de restricciones de referencia para las cuales esta tabla es una tabla principal)

db2 "select tabname, children from syscat.tables where children > 0"

SYSCAT.TABLES SELFREFS

Cantidad de restricciones autorreferenciales para la tabla (cantidad de restricciones de referencia en las cuales la tabla es principal y secundaria)

db2 "select tabname, selfrefs from syscat.tables where selfrefs > 0"

SYSCAT.TABLES KEYUNIQUE Cantidad de restricciones de únicas (que no sean de clave primaria) definidas en la tabla

db2 "select tabname, keyunique from syscat.tables where keyunique > 0"

SYSCAT.TABLES CHECKCOUNT Cantidad de restricciones de verificación definidas en la tabla

db2 "select tabname, checkcount from syscat.tables where checkcount > 0"

"No puede no ser nada": La restricción NOT NULL

La restricción NOT NULL evita que se agreguen valores nulos a una columna. Así se asegura que la columna tenga un valor con sentido para cada fila de la tabla. Por ejemplo, la definición de la tabla EMPLOYEE (EMPLEADOS) de la base de datos SAMPLE (MUESTRA) incluye LASTNAME VARCHAR(15) NOT NULL, lo cual asegura que cada fila contenga el apellido de un empleado.

"Sólo para claves únicas ": la restricción de las claves únicas

La restricción de claves únicas evita que un valor aparezca más de una vez dentro de una columna particular en la tabla. Además, evita que aparezca un conjunto de valores más de una vez dentro de un conjunto determinado de columnas. Las columnas a las que se hace referencia en una restricción de claves únicas se deben definir como NOT NULL. La restricción de claves únicas se puede definir en la instrucción CREATE TABLE usando la cláusula UNIQUE, o en una instrucción ALTER TABLE.

Cómo crear una restricción de única

db2 create table org_temp ( deptnumb smallint not null, deptname

varchar(14), manager smallint, division varchar(10), location varchar(13) not null ) db2 alter table org_temp add unique(location) db2 insert into org_temp values (10, 'Head Office', 160, 'Corporate', 'New York') DB20000I

The SQL command completed successfully. db2 insert into org_temp values (15, 'New

England', 50, 'Eastern', 'New York') DB21034E The command was processed as an

SQL statement because it was not a valid Command Line Processor command. During SQL

processing it returned: SQL0803N One or more values in the INSERT statement, UPDATE

statement, or foreign key update caused by a DELETE statement are not valid because

the primary key, unique constraint or unique index identified by "1" constrains

table "DELSVT.ORG_TEMP" from having duplicate values for the index key.

SQLSTATE=23505 La restricción de única ayuda a asegurar la integridad de los datos evitando la duplicación no intencional.

43

"Somos los número uno": restricción de clave primaria

La restricción de clave primaria asegura que todos los valores de la columna o el conjunto de columnas que forman la clave primaria de una tabla sean únicos. La clave primaria se usa para identificar las filas específicas de la tabla. Una tabla no puede tener más de una clave primaria, pero puede contar con varias claves únicas. La restricción de clave primaria es un caso especial de restricción de clave única, que se pone en funcionamiento a través de un índice primario. Las columnas a las que se hace referencia en una restricción de clave primaria se deben definir como NOT NULL. La restricción de clave primaria puede definirse en la instrucción CREATE TABLE usando la cláusula PRIMARY KEY, o en una instrucción ALTER TABLE. A continuación muestra cómo se crea una restricción de clave primaria. La columna ID de la tabla STAFF no es anulable, y puede contener una restricción de clave primaria definida en ella.

Cómo crear una restricción de clave primaria

db2 alter table staff add primary key (id)

"Todo es relativo": restricción de clave externa

La restricción de clave externa es a veces conocida como restricción referencial. La integridad referencial se define como el estado de una base de datos en la cual todos los valores de claves externas son válidos. Una clave externa es una columna o un conjunto de columnas en una tabla cuyos valores deben corresponder por lo menos a un valor de clave primaria o de clave única de una fila en su tabla principal. Simplemente significa que si una columna (C2) de una tabla (T2) posee valores que corresponden a los valores de una columna (C1) de otra tabla (T1), y C1 es la columna de la calve primaria de T1, entonces C2 es una columna de clave externa en T2. La tabla que incluye la clave principal (clave primaria o clave única) se denomina tabla principal, y la tabla que contiene la clave externa se denomina tabla dependiente. Ejemplo: La tabla PROJECT (PROYECTO) de la base de datos SAMPLE tiene una columna denominada RESPEMP. Los valores de esta columna representan la cantidad de empleados que son responsables por cada uno de los proyectos mencionados en la tabla. RESPEMP no es anulable. Debido a que esta columna corresponde a la columna EMPNO de la tabla EMPLOYEE, y a que EMPNO es ahora la clave primaria de la tabla EMPLOYEE, RESPEMP se puede definir como una clave externa en la tabla PROJECT. Esto asegura que las futuras eliminaciones que se realicen en la tabla EMPLOYEE no dejarán a la tabla PROJECT con empleados responsables inexistentes.

Cómo crear una restricción de clave externa

db2 alter table projectadd foreign

44

key(respemp)referencesemployee on delete cascade

Opciones de restricción referencial

Si esta cláusula fue especificada cuando se creó la restricción referencial…

Entonces éste es el resultado

RESTRICT o NO ACTION No se eliminan filas

SET NULL Cada columna anulable de la clave externa se fija en nula

CASCADE La operación de eliminación pasa a las tablas dependientes de la tabla principal, que se consideran conectadas por eliminación con la tabla principal.

"Controle y vuelva a controlar": restricción de control de tabla

Una restricción de control de tabla aplica restricciones definidas a datos que se agregan a una tabla. Por ejemplo, una restricción de control de tabla puede garantizar que la extensión telefónica de un empleado tenga exactamente cuatro dígitos cada vez que se agreguen o actualicen extensiones telefónicas en la tabla EMPLOYEE. Las restricciones de control de tabla se pueden definir en la instrucción CREATE TABLE usando la cláusula CHECK, o en una instrucción ALTER TABLE, como se muestra:

Cómo crear una restricción de control de tabla

db2 alter table employee add constraint phoneno_length

check(length(rtrim(phoneno)) = 4)

Las restricciones de control de tabla pueden activarse o desactivarse usando la instrucción SET INTEGRITY.

Cómo usar la instrucción SET INTEGRITY para diferir controles de

restricciones.

db2 update employee set phoneno = '123' where empno = '000100' db2 set integrity for employee off db2 alter table employee add constraint

phoneno_length check (length(rtrim(phoneno)) = 4)

db2 create table empl_except like

employee db2 set integrity for employee immediate checked for exception in employee

use empl_except SQL3602W Check data processing found constraint violations and moved

them to exception tables. SQLSTATE=01603 db2 select empno, lastname, workdept,

phoneno from empl_except EMPNO LASTNAME WORKDEPT PHONENO ------ ---------------

45

-------- ------- 000100 SPENSER E21 123 1 record(s) selected.

Conclusión:

Los diversos tipos de restricciones soportadas por DB2 para Linux, UNIX y Windows, son la restricción NOT NULL, la restricción de clave única, la restricción de clave primaria, la restricción de clave externa (referencial) y las restricciones de control de tabla. DB2 usa las restricciones para aplicar reglas de negocios para los datos y para ayudar a preservar la integridad de la base de

datos.

Bibliografía

IBM. (2006). Data Recovery and High Availability Guide and Reference. Canada, United States:

IBM.

IBM. Introducción a DB2 para z/OS.

J. Palmisano, S. (s.f.). IBM. Recuperado el Agosto de 2012, de

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.sq

lref%2Frcmit.htm

Rivero Cornelio, E. Bases de Datos Relacionales: Diseño Físico (Orientado al DB2). Madrid, España:

Comillas.

SQL Max. (2008). Recuperado el 2012 de Agosto de 22, de SQL MAX:

http://www.sqlmax.com/centro/ModuloII_4.asp?MX=

Manso, G. (2008). Prevencion de ataques de inyeccion SQL en PHP.

Por Fett, B. F. (2005). Esquemas de recuperacion en SQL Server. México.

Stanek, W. R. (2007). SQL Server 2005. México D.F.: Mc Graw Hill.