DB2 9.7: Cambio de esquemas online

Minimice las suspensiones temporales planificadas al realizar cambios en los objetos de base de datos

IBM® DB2® 9.7 presenta mejoras que permiten realizar cambios en los objetos de base de datos manteniendo una accesibilidad total de los usuarios de la base de datos a dichos objetos durante su modificación. Estas mejoras permiten que los administradores de base de datos realicen cambios fundamentales en esquemas de base de datos sobre la marcha sin que los usuarios sufran interrupciones de ningún tipo. Este artículo incluye ejemplos que muestran cómo usar el comando ALTER TABLE para renombrar columnas y cambiar los tipos de datos de las columnas. Además se incluyen ejemplos que muestran cómo usar la rutina ADMIN_MOVE_TABLE para mover y modificar tablas manteniendo la accesibilidad.

Maksym Petrenko, DB2 Open Database Technologies, IBM

Maksym Petrenko photoMaksym 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.



22-12-2009

Generalidades

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:

  1. 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_INFO
    CREATE 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;
  2. 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_INFO
    INSERT 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();
  3. 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_REVAL
    db2 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 vista
    CREATE 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.


Mover una tabla online

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

Conclusión

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.

Recursos

Aprender

Obtener los productos y tecnologías

Comentar

Comentarios

developerWorks: Ingrese

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


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


¿Olvidó su Password?
Cambie su Password

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

 


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

Toda la información enviada es segura.

Elija su nombre para mostrar



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

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

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

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

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

 


Toda la información enviada es segura.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=90
Zone=Information mgmt
ArticleID=458417
ArticleTitle=DB2 9.7: Cambio de esquemas online
publish-date=12222009