Download Materiales\UBADB2

Bases de Datos DB2
Conceptos Básicos
gkaminker@velocom.com.ar
Temario:
1- DISEÑO LOGICO
2- DISEÑO FISICO / OBJETOS DB2
3- LENGUAJE SQL ESTATICO / DINAMICO
4- AREAS DE MEMORIA (SORT/DB BUFFER)
5- PROTECCION DE DATOS /U. DE TRABAJO
6- BACKUP / RECOVERY / STATISTICS
7- SEGURIDAD DE DATOS
8- OPTIMIZER
9- CONECTIVIDAD
DISEÑO LOGICO
 No a atributos repetitivos
 La dependencia sobre la totalidad del identificador
 No a las dependencias transitivas
Tablespaces: archivos en disco donde se almacenan
las tablas
Tablas: Donde se guardan los datos de las
entidades que resultan del diseño lógico/físico. RI
Indices: Permiten acceso directo a los datos U/C
Views: Permiten acceder a parte de la info
Logs: Archivos con info para recuperar datos
Triggers: Se disparan ante actualización de datos
Stored Procedures: Programas usados en entorno
C/S. Lenguajes: SQL PL, Java, Cobol, C, etc.
Lenguaje de acceso: SQL estándar
DML: SELECT, INSERT, UPDATE, DELETE
DDL: CREATE, ALTER, DROP
DCL: GRANT, REVOKE
SQL Estático
Diversos lenguajes (Cobol, C, Assembler, PL1, REXX)
pueden contener sentencias SQL, con delimitadores.
El Precompilador separa al programa en dos partes:
una contiene el SQL (p.ej. Select nombre, dirección
from cliente where nrocliente = :hv) llamada DBRM y
otra sin el SQL con solamente el lenguaje y llamados
al DBMS.
El DBRM pasa por el proceso de BIND donde
interviene el optimizador que decide el mejor camino
de acceso a los datos, generando un PACKAGE, con
secciones correspondientes a cada sentencia.
El resto del programa se prepara en la forma usual.
SQL Dinámico
Sentencias Prepare, Execute permiten tomar un área
de entrada que contenga al SQL e interpretarlo y
ejecutarlo directamente.
Mayor flexibilidad para ejecutar cualquier SQL
El usuario que ejecuta debe tener autoridad sobre las
tablas
Para uso repetitivo conviene, desde el punto de vista
performance, embeber en programa
Areas de Memoria
DB POOLS
Contienen bloques de Tablespaces e Indices
Al definir un TS o Indice se asigna a un BP
EDM POOL Para guardar packages o DBDs
SORT POOL Para resolver algunos SORTS
RID POOL Para accesos por múltiples índices
LOCKING
Cuando un dato es actualizado, no es visible
para el resto de los usuarios, hasta tanto se
llegue a COMMIT.
La excepción a esto son los pedidos de
lectura aceptando datos comprometidos (UR).
El sistema detecta timeouts y deadlocks, y los
resuelve.
Es posible hacer un backup, e incluso
reorganizar datos con actualización
concurrente.
Algunos utilitarios DB2
COPY (Full o Incremental)
RECOVER (COPY + logs)
REORG Según índice cluster
LOAD
RUNSTATS
Seguridad de Datos
GRANT sobre Tablas, views, packages.
SQL estático permite acceso a tablas sólo a
través de la lógica del programa.
Sólo usuarios autorizados pueden acceder a
tablas/views de forma directa.
Sólo usuarios
programas.
autorizados
pueden
ejecutar
Optimizador
Sistema experto basado en estadísticas,
resuelve el camino de acceso a los datos para
asegurar el menor consumo de recursos (CPU,
I/O).
Cluster ratio, filter factor, cardinalidad, valores
máximos y mínimos por columna, valores más
frecuentes son considerados para determinar
acceso por índice, índex-only, evitar sorts,
barrido de datos, acceso por múltiples índices
(casos AND y OR).
CONECTIVIDAD
 Unidad de Trabajo Remota
Connect to DataSource y ejecución de sentencias SQL sobre esa
instancia
 Unidad de Trabajo Distribuida
Connect to DataSource1, ejecución de sentencias SQL, connect to
DataSouce2, ejecución de sentencias SQL, connect to DataSource3,
ejecución de sentencias SQL, COMMIT o ROLLBACK.
AREAS LOGICAS Y ARCHIVOS FISICOS






Las areas logicas se extienden a traves de los arcivos fisicos.
Durante la creacion de la Base de Datos se crea por default un Tablespace llamado
SYSTEM y un datafile asociado al mismo con Ubicación, Nombre y Tamaño
especificados.
Durante la creación tambien se generan automaticamente dos usuarios SYS y
SYSTEM con caracteristicas de DBA, A traves de estos usuarios se realizan las
operaciones de mantenimiento, creacion de nuevos tablespaces, usuarios,
esquemas, etc.
Se pueden agregar dinamicamente datafiles y aumentarlos de tamaño.
Los Rollback segments, contienen la informacion de transacciones en curso que no
hayan sido confirmadas con COMMIT o dehechas con ROLLBACK. Los segmentos de
Rollback se usan concurrentemente por varias transacciones. Funcionan como un
buffer circular con varias extensiones, Pueden estar ONLINE OFFLINE INVALID,
NEEDS RECOVERY o PARTLY AVAILABLE.
Redo Logs, Llevan registro de todas las transacciones que se ejecutan sobre la base
de datos para poder reconstruir la informacion en caso de falla. La instrucción
COMMIT, no termina su ejecución hasta que no se completa la escritura de estos
archivos. Podria decirse que hasta tanto se ejecuta una instrucción COMMIT, la
información de las transacciones realizadas se almacena en los Rollback
Segments,Una vez ejecutado el COMMIT, está información pasa a estar en los Redo
Logs.
MANEJO DE DATOS
EXPORT
Genera un archivo binario conteniendo información de los objetos seleccionados.
Registra tanto datos como estructuras.
Usos:
Respaldo del contenido de la base de datos
Llevar datos de una base a otra
Replicar estructuras en bases de datos diferentes
Reconstruir y defragmentar bases de datos
Reordenar datafiles
Opciones:
Se puedenn especificar en linea de comandos o a traves de un archivo de
parametros.
IMPORT
Incporpora a una base de datos la información exportada con el comando EXPORT
Opciones:
Se puedenn especificar en linea de comandos o a traves de un archivo de
parametros.
MANEJO DE DATOS
Opciones del comando EXPORT
BUFFER=bytes Especifica el tamaño del buffer de copia (en bytes) usado por el utilitario. Si el
valor es cero, se recuperan las filas de a una.
COMPRESS=[Y o N] Este parámetro indica cómo se tratará la extensión inicial.
“Y” - Se incluirá toda la información en una única extensión.
“N” - Se utilizarán los parámetros vigentes para la cláusula storage.
El valor por defecto es “Y”.
CONSISTENT=[Y o N] Indica si se espera o no a que la información que se está exportando sea
confirmada. Esta opción es muy costosa en tiempo
El valor por defecto es “N”.
CONSTRAINTS=[Y o N] Define si se exportan las restricciones de las tablas. Por defecto
siempre se exportan
FILE=nombre_archivo Especifica el nombre del archivo de salida donde quedará la información
FULL=[Y o N] Este parámetro controla la exportación total o parcial de la base de datos. En caso
de responder “N”, habrá que enumerar los objetos a exportar
datos. El valor por defecto es “N”.
MANEJO DE DATOS
Opciones del comando EXPORT (Cont.)
GRANTS=[Y o N] Permite indicar si se exportaran los permisos (grants) de cada usuario sobre
los objetos que son exportados.
El valor por defecto es “N”
INDEXES=[Y o N] Este parámetro especifica si se exportaran los índices
El valor por defecto es “Y”.
ROWS=[Y o N] Se utiliza para exportar todos los datos de las tablas o solo la estructura de los
objetos
“Y” - Exportar Estructuras y datos
“N” - Solo exportar estructuras de los objetos
OWNER=usuarios Permite enumerar los esquemas que se exportarán, en caso de ser mas de
uno, se separan con coma.
TABLES=tablas Permite enumerar las tablas que se van a exportar. Este parámetro solo es
aplicable cuando se exporta un solo esquema
MANEJO DE DATOS
Opciones del comando IMPORT
FROMUSER=usuario Indica importar solo los objetos del esquema especificado como usuario
TOUSER=usuario Fuerza un esquema (indicado como usuario) donde importar los objetos
IGNORE=[Y o N] Indica si se desea ignorar los errores que pudieran ocurrir durante la
importación
El valor por defecto es “N”
OWNER=usuarios Permite enumerar los esquemas que se exportarán, en caso de ser mas de
uno, se separan con coma.
TABLES=tablas Permite enumerar las tablas que se van a importar
OBJETOS FUNDAMENTALES DE UNA BASE
Los objetos fundamentales de una base de datos son:
 Tablas
 Vistas
 Sinonimos
 Indices
 Secuencias
OBJETOS FUNDAMENTALES DE UNA BASE
Tablas
Una tabla se crea en un segmento. Este segmento posee una o más extensiones. Si la tabla
crece hasta alcanzar el tamaño máximo de una extensión, entonces se crea un segmento
nuevo para esa tabla. Las extensiones crecen de la manera en que se definieron cuando se
creó la tabla, dentro de la cláusula STORAGE. Cuando la cláusula anterior no se define para
una tabla, se utilizan los parámetros por defecto definidos dentro del tablespace donde
reside. Si tampoco existen estos, se utilizan los parámetros del sistema. Las opciones de la
clausula STORAGE son las siguientes:
INITIAL: Tamaño de la extension inicial en bytes
NEXT:
Tamaño de la segunda extension
PCTINCREASE: Tamaño de las extensiones posteriores a la segunda en porcentaje
respecto de la segunda extension.
El valor 0 (cero) indica que todas las extensiones tendrán el mismo tamaño que la
segunda
MINEXTENTS: Cuántas extensiones se crean al mismo tiempo de la tabla
MAXEXTENTS: Máxima cantidad de extensiones que podrá tener la tabla
OBJETOS FUNDAMENTALES DE UNA BASE
Tablas (Cont.)
PCTFREE y PCTUSED - Especifican condiciones de almacenamiento estan en relación con la
volatilidad de los datos y cómo gestionar mejor el espacio asignado a cada extensión
PCTFREE: Fija el porcentaje de espacio que se reservará en cada bloque de datos de una tabla
para futuras actualizaciones de los registros del mismo bloque. El valor que se asigne al
parámetro esta relacionado con la frecuencia de updates que se harán a la tabla.
Valores recomendados:
· Tablas con muchas actualizaciones que no necesariamente hagan crecer el registro: alrededor
de 10.
·Tablas en que se incrementa el tamaño de las filas frecuentemente: alrededor de 20
· Tablas sin actualizaciones o con baja frecuencia es de las mismas: del orden de 5
PCTUSED: Este parámetro está relacionado con la frecuencia de inserciones que se hacen en
una tabla. Determina el mínimo porcentaje de espacio usado que será mantenido para cada
bloque de datos, antes de crear el próximo segmento.
En función de esta frecuencia se sugieren los siguientes valores:
·
Alta: Alrededor de 40.
·
Alta con muchas actualizaciónes: alrededor de 60.
·
Baja:u en torno a 60
OBJETOS FUNDAMENTALES DE UNA BASE
Tablas (Cont.)
Tablas particionadas
La caracteristica distintiva de las tablas particionadas es cómo se va a almacenar la información
físicamente.
En el momento de crearlas, se puede elegir qué rangos de datos van a quedar almacenados en
un tablespace u otro.
La ventaja de esta organización es que al poder distribuir los datos entre los tablespaces y estos
entre los datafiles, se puede determinar donde estará esa información, con las consiguientes
posibilidades de optimización a saber:
-
Segmentos de datos más pequeños: influye en forma directa en el rendimiento de las
búsquedas ya que cada partición es tratada como si fuera una tabla diferente.
Indices más pequeños: con la partición por rangos es posible crear índices individuales para
cada partición.
Respaldo más rápido: ya que los datos se encuentran en segmentos separados, Se puede
paralelizar el procedimiento de respaldo
OBJETOS FUNDAMENTALES DE UNA BASE
Vistas
Una vista es una ventana dentro de una tabla. Permite aislar el uso de la información
protegiendola y facilitar operaciones que requieran de un modo especial de acceso a las
mismas.
Se forman de la misma manera en que se realizan las selecciónes de registros (instrucción
SELECT) sobre una o mas tablas, tambien facilitan el mantenimiento, al poderse modificar
las condiciones de selección sin afectar a la estructura de datos retornada
OBJETOS FUNDAMENTALES DE UNA BASE
Indices
Los indices permiten acceso mas rapido a los datos contenidos en una tabla. Son
independientes. Inmediatamente luego de creado el índice, Oracle comienza a mantenerlo
de acuerdo a las inserciones, actualizaciones y eliminaciones de registros de la tabla en la
cual se ha implementado.
Existen tres tipos de índices cuya naturaleza depende de la forma en que se crean a saber:
Indice único funciona como una clave primaria, obligando a que el valor de la columna
indexada no se repita
Indice no único, no impone la restricción antes descriptaIndice compuesto agrupa varias columnas de la tabla. Es importante el orden en que se
ponen las columnas al crear el índice; la columna más referenciada debe ser puesta en
primer lugar y así sucesivamente.
Cuando se crea un índice también se crea un segmento de datos para guardarlo el espacio de
almacenamiento usado se ve afectado por la cláusula storage que tiene la misma estructura
que la explicada para las tablas
OBJETOS FUNDAMENTALES DE UNA BASE
Indices (cont)
Consideraciones para el uso de indices:
-
Indexar solo tablas cuando las consultas no accedan a mas del 5% de las filas de una tabla.
No indexar tablas que son actualizadas frecuentemente
Las consultas con condiciones (WHERE) muy complejas generalmente no usan indices
Reglas para elegir columnas a indexar:
Eleqir aquellas que se utilizan con mayor frecuencia en las cláusulas WHERE de las
consultas.
No indexar columnas o grupos de columnas en que se repitan muchos vaores
Indexe las columnas que sirven para unir una tabla con otras (join en las consultas).
Sintaxis de creación de índices:
CREATE INDEX nombre_indice ON [esquema.]nombre_tabla (columna1 [, columna2, ...])
TABLESPACE nombre_tablespace ;
Al igual que las tablas, los indices tambien pueden ser particionados
OBJETOS FUNDAMENTALES DE UNA BASE
Sinonimos
Los sinónimos son objetos del sistema que apuntan a otros objetos. implementan alias de tablas,
vistas, secuencias o unidades de programa. Se utilizan para proteger u ocultar al usuario
final ciertos detalles del objeto al que apuntan
Los sinónimos pueden ser públicos o privados. Los primeros son aquellos que residen en el
esquema PUBLIC y son vistos por todos los usuarios de la misma base de datos. Los
sinónimos privados se crean dentro del esquema de un usuario en particular y sólo son
visibles para quienes él de acceso
Sintaxis de creación de sinónimos:
CREATE [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]objeto
OBJETOS FUNDAMENTALES DE UNA BASE
Sinonimos
Devuelven numeros de acuerdo a un orden definido en su creación, garantizando su
irrepetibilidad
En la creación de una secuencia, se deben indicar, como mínimo, el valor de partida (valor
mínimo) y el incremento.
La sintaxis de creación de una secuencia es la siguiente:
CREATE SEQUENCE [schema.]nombre opciones
Las opciones pueden ser:
INCREMENT BY numero,
START WITH numero
MAXVALUE numero | NOMAXVALUE
MINVALUE numero | NOMINVALUE
CYCLE | NOCYCLE
CACHE numero | NOCACHE
ORDER | NOORDER
ADMINISTRACION DE USUARIOS
Usuario: Tiene asociada información especifica como nombre, esquema,
password
Rol: Utilizado para asignar privilegios a los usuarios
Perfil: Denota la cantidad de recursos del sistema que se permite consumir
a un usuario o grupo de ellos.
ADMINISTRACION DE USUARIOS
Creación de Usuarios
Cuando se da de alta a un usuario como mínimo, debe indicarse el nombre
y el password de la cuenta (esquema) que se está creando. Se asigna
un espacio físico al nuevo esquema dentro de la base de datos con los
parámetros por defecto.
CREATE USER nombre_usuario
IDENTIFIED BY password
[DEFAULT TABLESPACE nombre_tablespace]
[TEMPORARY TABLESPACE nombre_tablespace]
[QUOTA [número, K o M o UNLIMITED] ON nombre_tablespace1]
[, QUOTA [número, K o M o UNLIMITED] ON nombre_tablespace2]
[PROFILE nombre_perfil]
[PASSWORD EXPIRE]
[ACCOUNT LOCK o ACCOUNT UNLOCK]
ADMINISTRACION DE USUARIOS
Modificación de Usuarios
En la modificación de usuarios, todos los parámetros que fueron
establecidos en el instante de la creación pueden cambiarse
ALTER USER nombre_usuario
IDENTIFIED BY password
[DEFAULT TABLESPACE nombre_tablespace]
[TEMPORARY TABLESPACE nombre_tablespace]
[QUOTA [número, K o M o UNLIMITED] ON nombre_tablespace1]
[, QUOTA [número, K o M o UNLIMITED] ON nombre_tablespace2]
[PROFILE nombre_perfil]
[PASSWORD EXPIRE]
[ACCOUNT LOCK o ACCOUNT UNLOCK]
ADMINISTRACION DE USUARIOS
Eliminación de Usuarios
Para eliminar un usuario se utiliza la siguiente instrucción:
DROP USER nombre_usuario [CASCADE]
La opción Cascade es obligatoria cuando el usuario posee objetos en su
esquema (tablas, vistas, etc.) para borrarlos junto con él.
Sin esta opción, no se puede eliminar un usuario con objetos.
ADMINISTRACION DE USUARIOS
Mantenimiento de Perfiles
Los perfiles se usan para limitar las posibilidades de los usuarios del
sistema de base de datos. Tipicamente se establecen tres tipos de
usuarios
• Administradores: Tienen acceso a recursos ilimitados
Desarrolladores: Múmero ilimitado de sesiones Restricciónes en
utilización de la CPU
• Otros.
CREATE PROFILE y ALTER PROFILE
Los recursos que se puede administrar para un perfil son:
SESSIONS_PER_USER, CPU_PER_SESSION, CPU_PER_CALL CONNECT_TIME,
IDLE_TIME LOGICAL_READS_PER_SESSION, LOGICAL_READS_PER_CALL
COMPOSITE_LIMIT, PRIVATE_SGA
Con las siguientes posibilidades: UNLIMITED | DEFAULT | cantidad
ADMINISTRACION DE USUARIOS
Creación y asignación de Roles, control Privilegios
Los Roles son la forma más segura y rápida de asignar recursos a los
grupos de usuarios
CREATE ROLE nombre_rol NOT IDENTIFIED o IDENTIFIED BY password
Para asignar y eliminar roles y privilegios a un usuario se usan los
siguientes comandos:
Grant: Otorga privilegios a un rol (o a un usuario cualquiera) o también
asigna un rol a un usuario.
GRANT Rol o Privilegio1, Rol o privilegio 2 TO Usuario o Rol;
Revoke: Elimina privilegios otorgados previamente a un rol (o a un usuario).
REVOQUE Rol o Privilegio 1, Rol o privilegio 2 FROM Usuario o Rol;
ADMINISTRACION DE USUARIOS
Creación y asignación de Roles, control Privilegios (Cont.)
Hay algunas opcionas adicionales para administrar Roles y Privilegios
GRANT nombre_rol o nombre_privilegio [, nombre_rol o nombre_privilegio]
TO nombre_usuario o nombre_rol o PUBLIC [, nombre_usuario o nombre_rol]
[WITH ADMIN OPTION]
Si los privilegios se otorgan a PUBLIC, significa que tidis los usuarios gozaran
del mismo.
Si los privilegios se otorgan con la cláusula “with admin option” esto quiere
decir que los usuarios que reciben los privilegios pueden a su vez
otorgarlos a otros.