Fundamentos DB2

Qu es una tabla

Objetivos:Conocer los servicios proporcionados por un administrador de la base de datos (DBA) Definir que es una RELACIN Conocer el lenguaje usado para dialogar con el administrador de la base de datos relacional (RDBM) Conocer las caractersticas asignadas a cada columna Conocer los tipos de los datos que pueden asignarse un la columna

Usando bases de datos relacionalesEMPNO LASTNAME -----------------BOONE BROWN NICHOLSON ESTUARDO QUINTANA ALLEN HERZIGOVA MICHAELS JACKSON LENNON MCVIE FIRSTNME ----------------DANIEL JAMES JACK MARIA ROSA IRVING EVA SALLY MICHAEL JOHN CHRISTINE IN. ----K K S A M W T K B W R DEPT --------------WILDLIFE MUSIC MOVIES ADMIN THEATRE PRODUCT ANATOMY SPORTS BABIES POETRY NEWSPAPERS PHONE -------------123-4503 123-4506 123-8543 111-7543 101-0021 321-8743 906-0900 231-2314 214-7623 123-5123 123-8532

DB2 UDB es un sistema de administracin de bases de datos relacionales que incluye los siguientes servicios:Logging Seguridad Optimizacin Locking Recuperacin Integridad de datos

Relacin = TABLAEMPLEADOS.SOLTEROS EMPLEADAS.SOLTERASLASTNAME LASTNAME -----------------ABREU BLANCO OVIEDO PARDO PATIO VILLA FIRSTNME -------------------SEBASTIAN CUAUHTEMOC FRANCISCO PAVEL CRISTIAN GERMAN -----------------CASTRO CONDE GUARDIA HERZIGOVA RUBIO BLANCO BROWN CAMPOS GARCIA LOPEZ PARDO FIRSTNME -------------------VERONICA NINEL MARIBEL EVA PAULINA CUAUHTEMOC MELVIN JORGE LUIS JOSE LUIS PAVEL STATUS ------------SOLTERA SOLTERA SOLTERA SOLTERA SOLTERA SOLTERO SOLTERO SOLTERO SOLTERO SOLTERO SOLTERO

Una tabla es un arreglo de datos acomodados en columnas y renglones Una base de datos relacional es un conjunto de tablas relacionadas

DB2 provee de servicios tales como:Concurrencia: Permitir a varios usuarios accesar elmismo dato al mismo tiempo en modo de lectura.

Integridad: Si un rengln esta siendo modificado por unusuario, ese rengln estar accesible para hasta que el proceso de modificacin termine.

Seguridad: Otorga a los usuarios permisos de lectura ymodificacin.

Creacin de tablasCmo creo una base de datos? Para crear tablas en DB2, debemos proveer la siguiente informacinEl nombre de la tabla y una descripcin de la informacin que contendr la tabla como: Nombre de la columna Descripcin de los datos de la columna/Longitud de la columna Caracterstica nula (NULL)

HASTA LLEGAR A

Tabla Creada(Ejemplo) PERSONAL.EMPRESAEMPNO LASTNAME -----------------BOONE BROWN NICHOLSON ESTUARDO QUINTANA ALLEN HERZIGOVA MICHAELS JACKSON LENNON MCVIE FIRSTNME -------------------DANIEL JAMES JACK MARIA ROSA IRVING EVA SALLY MICHAEL JOHN CHRISTINE IN. -------------------K K S A M W T K B W R DEPT --------------WILDLIFE MUSIC MOVIES ADMIN THEATRE PRODUCT ANATOMY SPORTS BABIES POETRY NEWSPAPERS PHONE -------------123-4503 123-4506 123-8543 111-7543 101-0021 321-8743 906-0900 231-2314 214-7623 123-5123 123-8532

Creacin de tablasExisten reglas relacionadas a la creacin?

Convenciones de nombres para las tablas y las columnas El primer carcter debe ser alfabtico (A-Z), $, Q, #. El primer carcter no puede ser numrico ni guiones bajos. Los caracteres subsecuentes pueden ser alfabticos, numricos, $, @, #, _

Creacin de tablasAlgunas ms?

Tipos de datos en las columnasTipo carcter CHAR(X) Longitud fija (1-254) VARCHAR(X) Longitud variable (1-4K) Tipo numrico SMALLINT 0 a +/- 32K INTEGER 0 a +/- 2 billones DEC(X,Y) hasta 31 dgitos. Tipo fecha/tiempo DATE TIME TIMESTAMP

Creacin de tablasCaracterstica Nula NOT NULL La columna siempre debe tener un valor NOT NULL WITH DEFAULT La columna debe tener siempre un valor. En el caso de que el usuario no lo proporcione, DB2 lo har NULLABLE La columna puede estar marcada como Valor desconocido

Creacin de tablas desde un editor

CREATE TABLE TELEVISORA.EMPLEADOS (APELLIDO CHAR(15) NOT NULL , NOMBRE CHAR(15) , EDAD SMALLINT(2) NOT NULL , DIRECCION CHAR(150) NOT NULL WITH DEFAULT ,

Area de descripcin de columnas

)

Creacin de tablasCmo acceso informacin de modo directo? Llave nica Tipos Simple, cuando solo involucra una columna Compuesta, cuando involucra mas de una columna Caractersticas Contiene valores nicos Pueden existir mas de una llave nica en una tabla Llave primaria Solo puede haber una por tabla Todas las columnas deben ser NOT NULL

Creacin de tablasEnunciado CREATE TABLECREATE TABLE PAIS (CODIGO_PAIS CHAR(6) NOT NULL , NOMBRE_PAIS CHAR(30) NOT NULL, EXTENSION INT, NUM_PROVINCIAS INT, PRIMARY KEY (CODIGO_PAIS) )

DBA

Creacin de tablasQu es una llave fornea?

Llave forneaColumna o conjunto de columnas que contienen valores de llaves nicas de tablas. Las llaves forneas se utilizan para crear relaciones entre nuestras tablas de la base de datos.

Creacin de tablas

Ejemplo de Create

CREATE TABLE CIUDADES (CIUDAD_ID CHAR(6) NOT NULL , NOMBRE_CIUDAD CHAR(20) NOT NULL , CODIGO_PAIS CHAR(6) NOT NULL , KMS_A_LA SMALLINT NOT NULL , POBLACION INT , HEMISFERIO CHAR(1) , INGRESO_PROMEDIO DECIMAL(9,2) , ULTIMO_CENSO DATE , ULTIMA_ACTUALIZACION TIMESTAMP NOT NULL WITH DEFAULT , DESCRIP_CIUDAD VARCHAR(300) , PRIMARY KEY (CIUDAD_ID) , FOREIGN KEY (CODIGO_PAIS) REFERENCES PAIS )

Creacin de tablasQu relacin tiene una llave primaria con una fornea?

CIUDADESLlave primaria

Llave fornea

CIUDAD-ID NOMBRE_CIUDAD CODIGO_PAIS KMS A LA

PAISESLlave primaria

Codigo Pais Nombre_Pais Extension NUM_Provincia

EMPLEADOSLlave primaria PKNUM_EMPLEADO 80910 80930 80950 APELLIDO ESPINOSA ROCHA GARCIA NOMBRE JUAN MANUEL ERNESTO

Llave nica UKDEPARTAMENTO 12 81 15 RFC EIZJ701209 ROHM680624 GAPE759618 SUELDO 18000.00 15000.00 7000.00

DEPARTAMENTOPKDEPARTAMENTO 12 81 15

Llave Fornea FKNOMBRE_DEPARTAMENTO GERENTE COMPRAS 80524 PERSONAL 80873 OPERACIONES 80102

BENEFICIOS

PKIMSS SAR 520.00 480.00 260.00

FKRFC 800.00 EIZJ701209 750.00 ROHM680624 450.00 GAPE759618

Cmo se modifica la informacin?

Te explico

Proceso de modificacin de datosEsta autorizado para accesar? Se pasa el usuario y contrasea a DB2

Administrador de aplicaciones (puede manejarsu propio esquema de seguridad)

DB2 UDBDB2 valida los permisos del usuario

Aplicaciones

Bases de datos

(1)El usuario se conecta al DB2 e introduce su identificacion de usuario y password

DB2 UDB

(2) DB2 valida si el usuario esta autorizado a realizar cambios en la base de datos

Catalogo

Bases de datos

(3) DB2 aplica un LOCK (bloqueo) sobre los datos que se modificarn y los manipula en sus buffers

Despus de modificar los datos(7) DB2 envia mensajes de xito o falla al usuario

DB2 UDB(2) DB2 modifica los datos en su buffer. (5) DB2 modifica los datos en la tabla (6) Se quita el bloqueo de escritura de los datos.

(1) DB2 escribe una copia en el LOG de los datos sin modificaciones (3) DB2 escribe una copia en el LOG de los datos modificados (4) COMMIT

LOG(registro de eventos)

Bases de datos

Quin me ayudar para la ejecucion de mis programas y consultas productivas?

Te explico

Tareas y responsabilidadesAdministrador de sistemas Instalacin del producto DB2 Creacin de las bases de datos Definir el esquema de seguridad (privilegios) para los usuarios que accesan la base de datos Monitoreo del funcionamiento del DB2 Administrador de la base de datos Creacion de tablas, vistas, indices y demas objetos en la base de datos Definir el esquema de seguridad para modificacin de objetos Preparar las aplicaciones que interactuaran con DB2 Responsable de ejecutar las utileras de DB2 Programador Creacin de ambientes de prueba para nuevas aplicaciones Creacin de SQLs prototipo Codificacin de programas, procedimientos almacenados, SQL, etc Preparar sus programas para ser ejecutados

Tareas y responsabilidadesUsuario final Ejecutar aplicaciones Modificar objetos (si tiene la autoridad) Generar reportes Realizar consultas Operador Realizar respaldos de bases de datos Realizar cargas de base de datos Restaurar bases de datos

Ahora usted puede:Describir los servicios proporcionados por un administrador de bases de datos Definir una RELACIN Nombrar el lenguaje usado para dialogar con el administrador de la base de datos relacional (RDBM) Reconocer las caractersticas asignadas a cada columna Identificar los varios tipos de datos que pueden asignarse a una columna

Usuarios Finales trabajando con DB2

Objetivos:Conocer los varios mtodos para interactuar con DB2 Conocer los mltiples mtodos para generar una declaracin SQL Conocer varias formas para agregar, remover o cambiar renglones en una tabla Conocer las formas para leer datos y generar reportes

ALTER GRANT

CREATE SELECTUn usuario final es toda persona que utiliza declaraciones de SQL, ya sea ejecutando programas que utilizan declaraciones de SQL o que escribe sus propios SQL

DROP UPDATE

SQL DELETE

INSERT REVOKE

Declaracin de SQLUna declaracin de SQL esta compuesta de clausulas.

SELECT ... FROM ... WHERE ... INSERT ... INTO ... VALUES ... UPDATE ... SET WHERE ...

? (No me quedo claro )

Te explico

SELECT (selecciona una lista de elementos) 1 - X elementos (nombres decolumnas, expresiones, literales, funciones de columna, funciones escalares)

FROM (tabla/ vista) 1 - X WHERE (pruebas, condiciones, predicados) 1 - X GROUP BY (subagrupa el grupo de renglones que cumplen con lascondiciones de la clausula WHERE)

HAVING (pruebas, condiciones, predicados - en grupos) ORDER BY (clasifica los renglones del resultado)

La declaracion SQL SELECT se usa con cuatro clausulas:

SELECT FROM WHERE SORT BYUsando estas clausulas, el resultado ser el despliegue de aquellos renglonesque cumplan con los requisitos de los filtros definidos por las clausulas

SELECT * FROM Q.STAFFEsta ltima declaracin dara como resultado el despliegue de todos los renglones de la tabla

Descripcin de la tabla Q.STAFFColumn Name ID NOMBRE DEPTO PUESTO ANTIGEDAD SALARIO COMMID 10 20 30 40 50 60 70 80 90 100 NOMBRE GOMEZ GONZALEZ JIMENEZ SALGADO MARTINEZ NARCISO HERNANDEZ BARRIENTOS LOPEZ SANCHEZ

Data Type SMALLINT VARCHAR SMALLINT CHAR SMALLINT DECIMAL DECIMALDEPTO 20 20 38 38 15 38 15 20 42 42 PUESTO MGR SALES MGR SALES MGR SALES SALES CLERCK SALES Mgr

Length 9 5 (7,2) (7,2)ANTIGEDAD 7 8 5 6 10 7 6 7

Nulls NO YES YES YES YES YES YESSALARIO $18,357.00 $18,171.00 $17,506.00 $18,006.00 $20,659.00 $16,808.00 $16,502.00 $13,504.00 $18,001.00 $18,352.00 COMM $612.00 $846.55 $650.00 $1,152.00 $128.00 $1,386.00 $206.00

Descripcin de la tabla Q.ORGColumn Name NUMDEPTO NOMDEPTO GERENTE DIVISION LOCALIDAD Data Type SMALLINT VARCHAR SMALLINT VARCHAR VARCHARGERENTE 160 50 10 30 100 140 270 290

Length 14 10 13DIVISION CORPORATIVO CENTRO METRO METRO CENTRO BAJIO PACIFICO NORTE

Nulls NO YES YES YES YESLOCALIDAD MEXICO MEXICO MEXICO MEXICO MEXICO GUANAJUATO SONORA NUEVOLEON

NUMDEPTO NOMDEPTO 10 15 20 38 42 51 66 84 CORPORATIVO COYOACAN SATELITE CUAUTITLAN NARVARTE GUANAJUATO HERMOSILLO MONTERREY

Ejemplo de cmo seleccionar renglones especificos con SELECT SELECT PUESTO, NOMBRE FROM Q. STAFF WHERE DEPT = 20Tabla ConceptualID NOMBRE 10 GOMEZ 20 GONZALEZ 80 BARRIENTOS DEPTO PUESTO 20 MGR 20 SALES 20 CLERCK ANTIGEDAD 7 8 SALARIO $18,357.00 $18,171.00 $13,504.00 COMM $612.00 $1,386.00

El resultado esPUESTO MGR SALES CLERCK NOMBRE GOMEZ GONZALEZ BARRIENTOS

Comparaciones Nmericas Se utilizan los siguientes formatos de datos nmericosWHERE DEPTO = 20 AND COMM = 612.00 AND (SALARIO = 18171.00 OR SALARY = 18000) -- (entero) -- (con punto decimal pt.) -- (sin coma) -- (igual a 18000.00)

y las siguientes alternativas de comparacinOPERADORES ( >, , =, SALARY) VALORES CALCULADOS VALUE (COMM/ 12)

SELECT * FROM Q. STAFF WHERE (( SALARY + COMM) > 18000) AND JOB NOT LIKE 'M%

ID 20 40 70 90

NOMBRE GONZALEZ SALGADO HERNANDEZ LOPEZ

DEPTO 20 38 15 42

PUESTO SALES SALES SALES SALES

ANTIGEDAD 8 6 7 6

SALARIO $18,171.00 $18,006.00 $16,502.00 $18,001.00

COMM $612.00 $846.55 $128.00 -

FuncionesFUNCTION_ NAME( argumento) Funciones de ColumnaSUM AVG MIN MAX COUNT(*) COUNT( DISTINCT nombre_de_columna ) Este tipo de funcion genera a la salida un solo rengln con el resultado

Funciones escalaresEste tipo de funciones genera un valor por cada rengln de la seleccin.

Funciones de columnaSELECT SUM( SALARIO) AS SUM_ SALARIO, COUNT(*) AS CUENTA FROM Q. STAFF WHERE DEPTO = 20COUNT(*) cuenta todos los renglones que cumplen con la clausula WHERE. SUM(SALARIO) suma los valores de la columna SALARIO. DB2 crearia la siguiente tabla conceptal de este enunciado:ID NOMBRE 10 GOMEZ 20 GONZALEZ 80 BARRIENTOS DEPTO PUESTO 20 MGR 20 SALES 20 CLERCK ANTIGEDAD 7 8 SALARIO $18,357.00 $18,171.00 $13,504.00 COMM $612.00 $1,386.00

y el resultado sera un rengln de total por definicin

SUM(SALARIO)

CUENTA

50032.00

3

Funciones escalares SELECT DIGITS( NUMDEPTO) AS C_ DEPTO FROM Q. ORGNUMDEPTO NOMDEPTO 10 15 20 38 42 51 66 84 CORPORATIVO COYOACAN SATELITE CUAUTITLAN NARVARTE GUANAJUATO HERMOSILLO MONTERREY GERENTE 160 50 10 30 100 140 270 290 DIVISION CORPORATIVO CENTRO METRO METRO CENTRO BAJIO PACIFICO NORTE LOCALIDAD MEXICO MEXICO MEXICO MEXICO MEXICO GUANAJUATO SONORA NUEVOLEONC_DEPTO

Se genera un valor por rengln por definicin hecha

00010 00015 00020 00038 00042 00051 00066 00084

Otras funciones escalares son: MONTH DAY DAYS COALESCE SUBSTR DATE DECIMAL DIGITS ROUND TRIM STRIP NULLIF

La funcin escalar UPPER permite realizar busquedas sin importar si los caracteres estan en mnusculas o myusculas.

SELECT ID, NOMBRE, DEPTO, PUESTO, SALARIO FROM Q. STAFF WHERE UPPER( PUESTO) = 'MGRID 10 30 50 100 NOMBRE GOMEZ JIMENEZ MARTINEZ SANCHEZ DEPTO 20 38 15 42 PUESTO MGR MGR MGR Mgr SALARIO $18,357.00 $17,506.00 $20,659.00 $18,352.00 COMM $650.00 $206.00

Ejecucin conceptual de SELECTSELECT DEPTO, PUESTO, AVG( SAL) FROM EMPLEADO WHERE PUESTO G' GROUP BY DEPTO, PUESTO HAVING AVG( SAL) > 28000 ORDER BY 3 DESCLa tabla EMPLEADO es la siguientePUESTO V G V M V G V M V G V V M V V SAL 31000 32000 30000 27000 33000 31000 32000 28000 30000 33000 31000 35000 27000 29000 29000 DEPTO AZU ROJ AZU VER VER AZU ROJ VER ROJ VER ROJ VER AZU ROJ AZU

Ejecucin conceptual de SELECT -(2)Al aplicar WHERE PUESTO GPUESTO V V M V V M V V V M V V SAL 31000 30000 27000 33000 32000 28000 30000 31000 35000 27000 29000 29000 DEPTO AZU AZU VER VER ROJ VER ROJ ROJ VER AZU ROJ AZU

Al aplicar GROUP BY DEPTO, PUESTOPUESTO M V V V V V V V M M V V SAL 27000 31000 30000 29000 32000 30000 31000 29000 27000 28000 33000 35000 DEPTO AZU AZU AZU AZU ROJ ROJ ROJ ROJ VER VER VER VER

Ejecucin conceptual de SELECT -(3)Al aplicar HAVING AVG( SAL) > 28000PUESTO V V V V V V V V V SAL 31000 30000 29000 32000 30000 31000 29000 33000 35000 DEPTO AZU AZU AZU ROJ ROJ ROJ ROJ VER VER

Al aplicar SELECT DEPTO, PUESTO, AVG( SAL)PUESTO V V V DEPTO AZU ROJ VER AVG(SAL) 30000 30500 34000

Recuerde, AVG es funcin de tabla

Al aplicar ORDER BY 3 DESCPUESTO V V V DEPTO VER ROJ AZU AVG(SAL) 34000 30500 30000

SELECT algo AS nuevo_nombre, SELECT expresin AS nombre multiparte" SELECT DEPTO AS DEPARTAMENTO, SALARIO + COMM AS INGRESOS FROM EMPLEADO En este ejemplo, la palabra clave AS se utiliza para cambiar nombres de columnas El resultado sera algo como:DEPARTAMENTO VER ROJ AZU INGRESOS 48000 58000 64000

RECUERDELa recuperacin de datos de multiples tablas se implementa con la clausula FROM The result table can contain columns from any table named in the FROM clause Para relacionar tablas por medio de las columnas que tienen datos comunes, podemos usar: El predicado "JOIN" La clausula WHERE La clausula ON clause, que es una extensin de la clusula FROM

EJEMPLO CON LA CLAUSULA FROMEMPLEADOSEMPNO 000010 000150 000020 000250 000100 000070 200140 APELLIDO HERNANDEZ AVILA TORRES SANCHEZ SALAZAR PEREZ NAVARRO

DEPARTAMENTOSNOMDEPTO COMPUTO DESARROLLO SOFTWARE ADMINISTRACION SUCURSAL2 NUMGR 000010 000100 000070 -

SELECT APELLIDO, NOMDEPTO FROM EMPLEADO, DEPARTAMENTOS WHERE EMPNO = NUMMGRAPELLIDO HERNANDEZ SALAZAR PEREZ NOMDEPTO COMPUTO SOFTWARE ADMINISTRACION

EJEMPLO DE INNER JOINEMPLEADOSEMPNO 000010 000150 000020 000250 000100 000070 200140 APELLIDO HERNANDEZ AVILA TORRES SANCHEZ SALAZAR PEREZ NAVARRO

DEPARTAMENTOSNOMDEPTO COMPUTO DESARROLLO SOFTWARE ADMINISTRACION SUCURSAL2 NUMGR 000010 000100 000070 -

SELECT LASTNAME, DEPTNAME FROM EMPL INNER JOIN DEPT ON EMPNO = MGRNOAPELLIDO HERNANDEZ SALAZAR PEREZ NOMDEPTO COMPUTO SOFTWARE ADMINISTRACION

Un caso especial es el INNER JOIN +(tambien llamado OUTER JOIN), ya que el resultado incluiria tambien todos aquelos renglones que no cumplan con las condiciones del SELECT

OUTER JOIN A LA DERECHASELECT APELLIDO, NUMDEPTO FROM EMPLEADO RIGHT OUTER JOIN DEPARTAMENTOS APELLIDO NOMDEPTO ON EMPNO = NUMMGR HERNANDEZ COMPUTOSALAZAR SALAZAR DESARROLLO SOFTWARE ADMINISTRACION SUCURSAL2

Cada rengln de la tabla derecha (EMPLEADOS) que no tenga una correspondencia para la tabla izquierda se llena con el carcter NULL (-)

OUTER JOIN A LA IZQUIERDASELECT LASTNAME, DEPTNAME FROM EMPL LEFT OUTER JOIN DEPT ON EMPNO = MGRNOAPELLIDO HERNANDEZ AVILA TORRES SANCHEZ SALAZAR PEREZ NAVARRO NOMDEPTO COMPUTO SOFTWARE ADMINISTRACION -

Cada rengln de la tabla izquierda (DEPARTAMENTOS) que no tenga una correspondencia para la tabla izquierda se llena con el carcter NULL (-)

Expresiones CASELas expresiones CASE nos permiten manejar una logica IF/THEN SELECT PARTNO, DESCRIPT, COLOR, CASE WHEN SIZE= 'S' THEN 'Small' WHEN SIZE= 'M' THEN 'Medium' WHEN SIZE= 'L' THEN 'Large' WHEN SIZE= 'X' THEN 'Xlarge' ELSE 'Unknown' END AS SIZE, QTY FROM CATALOG WHERE QTY