![Cerrar [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Este artículo brinda orientación para el uso de las nuevas mejoras de DB2 9.7 que permiten realizar cambios online en esquemas de base de datos. Un “cambio online” significa que los objetos que se modifican se mantienen disponibles para accesos de lectura y escritura aún durante el proceso de cambio.
A continuación se detallan algunas de las nuevas capacidades específicas:
- Las columnas pueden renombrarse online usando la instrucción ALTER TABLE.
- Se agregó la opción OR REPLACE en varias instrucciones CREATE.
- Se agregó CREATE con soporte de errores para las vistas y las funciones SQL inline.
- Se amplió el soporte ALTER COLUMN SET DATA TYPE.
- Las tablas pueden modificarse o moverse online usando la rutina ADMIN_MOVE_TABLE.
Requisitos previos y de sistemas
Este artículo está dirigido a administradores de base de datos DB2. Se requiere una comprensión de los conceptos básicos de espacios de tablas, tablas y columnas.
Para seguir los ejemplos de este artículo, es necesario tener DB2 9.7 for Linux, UNIX and Windows. En la sección de Recursos encontrará un vínculo para descargar una versión de prueba sin cargo de DB2 9.7 for Linux, UNIX, and Windows.
Preparación para seguir los ejemplos
Para seguir los ejemplos que muestran las capacidades de cambio de esquemas online en DB2, primero deberá crear una base de datos de muestra que proporcionará la infraestructura necesaria. Los ejemplos usan la base de datos DB2 SAMPLE. Si usted todavía no ha creado la base de datos DB2 SAMPLE, hágalo siguiendo las instrucciones del artículo "The SAMPLE database" del Centro de Información de DB2 (encontrará el vínculo en la sección Recursos ).
Una vez que haya creado la base de datos SAMPLE, siga los siguientes pasos para crear la tabla y los datos a utilizar en los ejemplos:
- Use el siguiente comando para crear una tabla basada en el catálogo del sistema
que simula información de clientes:
Listado 1. Crear la tabla CUSTOMER_INFOCREATE TABLE CUSTOMER_INFO( customer_id INTEGER NOT NULL, first_name VARCHAR(128) NOT NULL, last_name VARCHAR(128), address_street VARCHAR(128), address_city VARCHAR(128), address_state VARCHAR(25), address_country VARCHAR(30), age VARCHAR(2), customer_type VARCHAR(10), CONSTRAINT customer_id_pk PRIMARY KEY (customer_id) ) IN USERSPACE1;
- Use el siguiente comando para poblar la tabla CUSTOMER_INFO con datos que
emplean información ficticia extraída del catálogo del sistema:
Listado 2. Poblar la tabla CUSTOMER_INFOINSERT INTO customer_info SELECT ROW_NUMBER() OVER () as customer_id , RTRIM(a.tabschema) as first_name, RTRIM(a.tabname) as last_name, CAST(a.colno AS VARCHAR(3)) || ' ' || RTRIM(a.colname) as address_street, RTRIM(a.tabname) as address_city, RTRIM(a.TYPENAME) as address_state, RTRIM(a.TABSCHEMA) as address_country, CAST(CAST(sysfun.rand()*50 AS INTEGER)+1 AS VARCHAR(2)) as age, CASE WHEN MOD(CAST(sysfun.rand()*50 AS INTEGER),3)=0 THEN 'New' ELSE 'Existing' END AS type FROM syscat.columns a ORDER BY sysfun.rand();
- En este último paso, cree una vista y una función sobre la tabla
CUSTOMER_INFO.
Recuerde respetar el orden de creación de los objetos. Debe crear la vista EXISTING_CUSTOMERS antes de crear la función FULL_NAME de la cual depende la vista. Esto es importante porque, de manera predeterminada, la instrucción CREATE OR REPLACE VIEW fallaría con un error SQL0440N.
Sin embargo, a partir de la versión 9.7 es posible configurar DB2 para permitir la creación de objetos con ciertos tipos de errores como la falta de objetos dependientes. Esta capacidad puede resultar útil para la creación, el diseño y la modificación de objetos de base de datos porque permite seguir un orden aleatorio de los comandos CREATE. También permite verificar la sintaxis de vistas y procedimientos nuevos sin crear los objetos de los que dependen.
Para activar esta característica, use el siguiente comando, mediante el cual cambiará el parámetro de configuración de base de datos dinámica AUTO_REVAL para que tenga un valor DEFERRED_FORCE.
Listado 3. Establecer el parámetro de configuración AUTO_REVALdb2 update db cfg usando AUTO_REVAL DEFERRED_FORCE
Después de cambiar este parámetro, al crear la vista EXISTING_CUSTOMER, recibirá una advertencia SQL20480W y, en principio, la vista se marcará como inválida. Sin embargo, si la función de la cual depende la vista efectivamente existe, la próxima vez que se use la vista, ésta se revalidará automáticamente.
Use los siguientes comandos para crear la función y la vista nuevas. Observe que los comandos emplean la nueva sintaxis CREATE OR REPLACE aplicable a funciones, procedimientos, vistas, módulos, alias, disparadores, variables y apodos. Como su nombre lo indica, esta sintaxis crea el objeto o, si este ya existe, lo reemplaza. En otras palabras, en casos de objetos existentes, esta sintaxis combina DROP y CREATE en un mismo comando y además conserva los privilegios existentes otorgados al objeto.
Listado 4. Crear una función y una vistaCREATE OR REPLACE VIEW existing_customers AS SELECT full_name(customer_id) AS full_name, address_city, address_state FROM customer_info WHERE customer_type='Existing'; CREATE OR REPLACE function full_name(p_customer_id INTEGER) RETURNS VARCHAR(100) return SELECT first_name || ', ' || last_name FROM customer_info WHERE customer_id=p_customer_id;
Realización de modificaciones online en la definición de tablas
DB2 9.7 proporciona dos importantes mejoras relacionadas con la modificación online de la definición de tablas:
- En primer lugar, es posible renombrar columnas online mientras la carga de trabajo se está ejecutando contra la tabla sin que los usuarios sufran interrupciones de ningún tipo.
- En segundo lugar, DB2 9.7 ha ampliado el soporte del cambio de los tipos de datos de columnas en tablas existentes.
El siguiente ejemplo muestra cómo renombrar una columna usando el comando ALTER TABLE mientras se mantiene una accesibilidad total a la tabla:
Listado 5. Renombrar la columna de ejemplo
ALTER TABLE customer_info RENAME COLUMN age TO customer_age ; |
La opción ALTER COLUMN SET DATA TYPE de la instrucción ALTER TABLE se ha ampliado para soportar todos los tipos compatibles. Por ejemplo, ahora puede modificar una columna definida con un tipo de datos INTEGER a un tipo de datos VARCHAR o puede cambiar el tipo de datos TIMESTAMP a DATE. Consulte el artículo "Casting between data types" del Centro de Información DB2 para obtener una lista completa de los tipos de datos compatibles (encontrará el vínculo en la sección de Recursos ).
Durante la operación ALTER TABLE con la opción ALTER COLUMN SET DATA TYPE, DB2 efectúa una validación total para verificar que los datos contenidos en la columna sean compatibles con el nuevo tipo de datos y que no exista truncamiento, desbordamiento o cualquier otro tipo de error. Los valores de columna predeterminados también se verifican para confirmar que cumplan con el nuevo tipo de datos. Si el tipo de columna y los contenidos de datos son compatibles, el tipo de datos se modificará con éxito. De lo contrario, el comando ALTER devolverá un error.
El siguiente ejemplo muestra cómo cambiar los tipos de datos de una columna llamada customer_age (edad del cliente) de VARCHAR(2) a SMALLINT:
Listado 6. Modificar el tipo de columna
ALTER TABLE customer_info ALTER COLUMN customer_age SET DATA TYPE SMALLINT ; |
En la mayoría de los casos, ALTER SET DATA TYPE requerirá la reorganización (reorg) de la tabla, ya que modifica el formato físico de las filas. Puede usar la rutina ADMIN_REVALIDATE_DB_OBJECTS para determinar automáticamente si la tabla requiere reorganización o no:
Listado 7. Revalidar la tabla
CALL ADMIN_REVALIDATE_DB_OBJECTS('TABLE', 'DB2INST1',
'CUSTOMER_INFO'); |
Si necesita proporcionar acceso de escritura a la base de datos mientras se realizan cambios en los tipos de datos de columnas, puede usar la rutina ADMIN_MOVE_TABLE descripta en la próxima sección.
La nueva rutina incorporada a DB2 9.7 denominada ADMIN_MOVE_TABLE permite mover automáticamente una tabla de un espacio de tablas a otro o cambiar la definición del tipo de datos de columnas manteniendo la accesibilidad total de los usuarios.
Internamente, ADMIN_MOVE_TABLE crea una copia de la tabla fuente. Los datos se copian usando ya sea INSERT FROM CURSOR (opción predeterminada) o LOAD si se establece la opción correspondiente. Mientras se mueve la tabla, la rutina ADMIN_MOVE_TABLE también crea una tabla de staging que registra todos los cambios realizados en la tabla fuente. Los cambios se registran a través de disparadores que se crean en la tabla fuente. Al finalizar la operación de mover, la rutina ADMIN_MOVE_TABLE rápidamente cierra la tabla fuente en modo exclusivo, registra las actualizaciones que se realizaron y substituye la tabla fuente por la tabla meta. La tabla fuente o bien se suprime o se conserva de acuerdo con los parámetros de ingreso ADMIN_MOVE_TABLE.
Además de cambiar la ubicación de un espacio de tablas en una tabla, la rutina ADMIN_MOVE_TABLE puede usarse para modificar el clustering multidimensional, las claves de partición, la partición por rangos y el tipo de datos de columnas. ADMIN_MOVE_TABLE mueve los disparadores y las vistas definidos en la tabla fuente a la tabla meta. Sin embargo, hasta la fecha no soporta el copiado de claves externas primarias ni secundarias. Por consiguiente, si una tabla fuente es una tabla primaria o secundaria dentro de una relación RI, deberá capturar la definición de las claves externas y recrearlas luego de haber movido la tabla.
En el siguiente ejemplo, una instrucción SQL captura información de claves externas y genera instrucciones DROP:
Listado 8. Comandos create y drop para claves externas
SELECT 'ALTER TABLE ' ||
RTRIM(a.tabschema)||'.'||RTRIM(a.tabname) || ' ADD CONSTRAINT '||a.constname|| '
FOREIGN KEY (' || fk_colnames || ') REFERENCES ' ||
RTRIM(reftabschema)||'.'||RTRIM(reftabname) || ' ('||pk_colnames||') ON DELETE ' ||
CASE deleterule WHEN 'A' THEN 'NO ACTION' WHEN 'C' THEN 'CASCADE' WHEN 'N' THEN 'SET
NULL' WHEN 'R' THEN 'RESTRICT' END || ' ON UPDATE ' || CASE updaterule WHEN 'A' THEN
'NO ACTION' WHEN 'R' THEN 'RESTRICT' END || CASE enforced WHEN 'Y' THEN ' ENFORCED '
WHEN 'N' THEN ' NOT ENFORCED ' END || ' QUERY OPTIMIZATION '|| CASE enablequeryopt
WHEN 'Y' THEN ' ENABLE ' WHEN 'N' THEN ' DISABLE 'END || ';' FROM syscat.references
a, syscat.tabconst b WHERE a.constname=b.constname; SELECT 'ALTER TABLE ' ||
RTRIM(tabschema)||'.'||RTRIM(tabname) || ' DROP FOREIGN KEY ' || constname || ';'
FROM syscat.references WHERE (tabschema='DB2INST1' AND tabname='CUSTOMER_INFO') OR
(reftabschema='DB2INST1' AND reftabname='CUSTOMER_INFO'); |
El siguiente ejemplo muestra cómo mover la tabla CUSTOMER_INFO a un nuevo espacio de tablas:
Listado 9. Mover una tabla con la rutina ADMIN_MOVE_TABLE
CREATE TABLESPACE new_ts; CALL
SYSPROC.ADMIN_MOVE_TABLE ('DB2INST1', 'CUSTOMER_INFO', 'NEW_TS', 'NEW_TS',
'NEW_TS', '', '', '', '', '', 'MOVE'); Result set 1 -------------- KEY VALUE
-------------------------------- --------------------------- AUTHID DB2INST1
CLEANUP_END 2009-06-11-13.24.45.438000 CLEANUP_START 2009-06-11-13.24.45.407000
COPY_END 2009-06-11-13.24.44.641000 COPY_OPTS OVER_INDEX,ARRAY_INSERT COPY_START
2009-06-11-13.24.43.829000 COPY_TOTAL_ROWS 6745 INDEX_CREATION_TOTAL_TIME 1
INDEXNAME CUSTOMER_ID_PK INDEXSCHEMA DB2INST1 INIT_END 2009-06-11-13.24.43.797000
INIT_START 2009-06-11-13.24.43.250000 REPLAY_END 2009-06-11-13.24.45.250000
REPLAY_START 2009-06-11-13.24.44.641000 REPLAY_TOTAL_ROWS 0 REPLAY_TOTAL_TIME 0
STATUS COMPLETE SWAP_END 2009-06-11-13.24.45.391000 SWAP_RETRIES 0 SWAP_START
2009-06-11-13.24.45.329000 VERSION 09.07.0000 21 record(s) selected. |
El próximo ejemplo es un poco más complejo. En este ejemplo se mueve la misma tabla CUSTOMER_INFO, pero se usa la opción LOAD en lugar de la opción INSERT FROM CURSOR, que es la opción predeterminada.
La opción LOAD resulta más rápida que la opción INSERT FROM CURSOR por varios factores:
- LOAD no registra los cambios en la tabla meta por informe.
- LOAD está altamente paralelizado para el procesamiento de CPU e I/O (entrada/salida).
- LOAD realiza escrituras de grandes bloques en la tabla meta. Esto proporciona mayores eficiencias de I/O.
Considere que esta operación no es recuperable, por lo tanto, si necesita recuperar una base de datos al momento puntual entre la operación de mover una tabla y la subsiguiente copia de seguridad, la tabla podría perderse. Para evitar la pérdida de datos, puede usar la opción KEEP. La opción KEEP obliga a la rutina ADMIN_MOVE_TABLE a conservar el original. Además, cuando usa LOAD como opción de copia, debe usar la opción FORCE. Este requisito de uso de la opción FORCE sirve como recordatorio de que LOAD es una operación no recuperable y de que, si desea disponer de la opción de recuperación, deberá crear una copia de seguridad.
Listado 10. Mover una tabla con la opción LOAD
CALL SYSPROC.ADMIN_MOVE_TABLE ('DB2INST1',
'CUSTOMER_INFO', 'NEW_TS', 'NEW_TS', 'NEW_TS', '', '', '', '', 'KEEP,
COPY_USE_LOAD, FORCE', 'MOVE' ) Result set 1 -------------- KEY VALUE
-------------------------------- ---------------------------- AUTHID DB2INST1
CLEANUP_END 2009-06-11-13.36.43.360000 CLEANUP_START 2009-06-11-13.36.43.297000
COPY_END 2009-06-11-13.36.42.704000 COPY_OPTS OVER_INDEX,LOAD,WITH_INDEXES
COPY_START 2009-06-11-13.36.40.563000 COPY_TOTAL_ROWS 6745 INDEX_CREATION_TOTAL_TIME
0 INDEXNAME CUSTOMER_ID_PK INDEXSCHEMA DB2INST1 INIT_END 2009-06-11-13.36.40.266000
INIT_START 2009-06-11-13.36.39.172000 ORIGINAL CUSTOMER_INFOAC61b#o REPLAY_END
2009-06-11-13.36.43.125000 REPLAY_START 2009-06-11-13.36.42.704000 REPLAY_TOTAL_ROWS
0 REPLAY_TOTAL_TIME 0 STATUS COMPLETE SWAP_END 2009-06-11-13.36.43.250000
SWAP_RETRIES 0 SWAP_START 2009-06-11-13.36.43.125000 VERSION 09.07.0000 22 record(s)
selected. |
El ejemplo del Listado 3 mueve la tabla mucho más rápido que la opción anterior y guarda la tabla original con el nombre CUSTOMER_INFOAC61b#o.
El siguiente ejemplo muestra cómo modificar el espacio de tablas no sólo a nivel de los datos de tablas, sino también a nivel de los índices y objetos grandes (LOBs). También muestra cómo cambiar las propiedades de la tabla. La tabla CUSTOMER_INFO conserva los índices y LOBs en el espacio de tablas USERSPACE1, mientras que los datos de tablas se mueven al espacio de tablas NEW_TS. El ejemplo convierte la tabla para usar Clustering Multidimensional (MDC) con la columna CUSTOMER_TYPE como clave de clustering y vuelve a cambiar el tipo de datos de la columna CUSTOMER_AGE a VARCHAR(2).
Listado 11. Cambiar las propiedades de tabla con la rutina ADMIN_MOVE_TABLE
CALL SYSPROC.ADMIN_MOVE_TABLE( 'DB2INST1', 'CUSTOMER_INFO', 'NEW_TS', 'USERSPACE1', 'USERSPACE1', 'CUSTOMER_TYPE' , '', '', 'customer_id INTEGER NOT NULL, first_name VARCHAR(128) NOT NULL, last_name VARCHAR(128), address_street VARCHAR(128), address_city VARCHAR(128), address_state VARCHAR(25), address_country VARCHAR(30), customer_age VARCHAR(2) , customer_type VARCHAR(10)', '', 'MOVE'); Result set 1 -------------- KEY VALUE -------------------------------- -------------------------------- AUTHID MAKSYMP CLEANUP_END 2009-06-11-14.30.04.438000 CLEANUP_START 2009-06-11-14.30.04.422000 COPY_END 2009-06-11-14.30.03.750000 COPY_OPTS OVER_INDEX,ARRAY_INSERT COPY_START 2009-06-11-14.30.03.219000 COPY_TOTAL_ROWS 6745 INDEX_CREATION_TOTAL_TIME 1 INDEXNAME CUSTOMER_ID_PK INDEXSCHEMA DB2INST1 INIT_END 2009-06-11-14.30.03.125000 INIT_START 2009-06-11-14.30.02.250000 PAR_COLDEF customer_id INTEGER NOT NULL, first_name VARCHAR(128) NOT NULL, last_name VARCHAR(128), address_street VARCHAR(128), address_ci REPLAY_END 2009-06-11-14.30.04.344000 REPLAY_START 2009-06-11-14.30.03.750000 REPLAY_TOTAL_ROWS 0 REPLAY_TOTAL_TIME 0 STATUS COMPLETE SWAP_END 2009-06-11-14.30.04.407000 SWAP_RETRIES 0 SWAP_START 2009-06-11-14.30.04.360000 VERSION 09.07.0000 |
Este artículo explicó:
- Cómo usar la sintaxis CREATE OR REPLACE.
- Cómo crear ciertos objetos de base de datos con errores.
- Cómo renombrar columnas y cambiar el tipo de datos de columnas.
- Cómo usar la rutina ADMIN_MOVE_TABLE para mover tablas y modificar las propiedades de tabla.
Las nuevas características de cambio de esquema online incorporadas a DB2 9.7 son de gran ayuda para que los administradores de bases de datos y desarrolladores de aplicaciones logren reducir el tiempo de inactividad de la base de datos asociado a las suspensiones temporales planificadas. Estas características también simplifican la gestión de los objetos de esquemas y mejoran la productividad general.
Aprender
- En el área
DB2 for Linux, UNIX, and Windows en developerWorks , obtenga los recursos que
necesita para incrementar sus habilidades en DB2 9.7.
- El artículo " The SAMPLE database " del Centro de Información de DB2 for Linux, UNIX, and
Windows, proporciona instrucciones para la creación de la base de datos SAMPLE usada
en los ejemplos de este artículo.
- El artículo " Casting between data types " del Centro de Información de DB2 for Linux,
UNIX, and Windows, proporciona una lista completa de los tipos de datos compatibles
soportados por la opción ALTER COLUMN SET DATA TYPE de la instrucción ALTER
TABLE.
- Obtenga información adicional acerca del uso de DB2
en el Centro
de Información de DB2 for Linux, UNIX, and Windows .
- Navegue en la librería
de tecnología en busca de libros acerca de éste y otros temas
técnicos.
Obtener los productos y tecnologías
- Descargue una versión de
prueba sin cargo de DB2 9.7 for
Linux, UNIX, and Windows .
Comentar
Maksym Petrenko forma parte del equipo DB2 Beta Enablement del Laboratorio de IBM en Toronto. Asiste a los usuarios pioneros para mudar sus aplicaciones al más moderno y superior código base DB2. Maksym trabaja sobre DB2 desde 2001 como Developer, Technical Support Analyst y Lab Services Consultant. Su experiencia abarca el soporte de clientes en la instalación, configuración, desarrollo de aplicaciones y problemas de rendimiento relacionados con Bases de Datos DB2 en plataformas Windows, Linux y UNIX. Maksym es DB2 Advanced Database Administrator y DB2 Application Developer certificado.
