Eliminar el atributo ‘IDENTITY’ de una columna preservando su contenido.

29 septiembre \29\UTC 2016

Las columnas IDENTITY son un mecanismo que sirve para generar claves subrogadas en Teradata. Es un método análogo -aunque completamente diferente- a las secuencias (sequences) en Oracle.

Debido a la naturaleza paralela de la arquitectura de Teradata, un elemento como una secuencia -que serializa por definición- es prácticamente impensable.

En cambio, el funcionamiento de las columnas IDENTITY es ‘AMP-local’ (y en consecuencia, paralelo) y funciona reservando un ‘buffer‘ de números para cada AMP, que se irán usando en las operaciones correspondientes. Esto es un punto importante: una secuencia es, claro, “secuencial”: y dará valores sucesivos para operaciones (generalmente INSERTs) sucesivas. Una columna IDENTITY garantiza valores únicos, pero no necesariamente sucesivos (es más, muy probablemente no lo serán).

Otra diferencia importante es que, mientras las secuencias Oracle son objetos independientes, las IDENTITY columns se circunscriben a la tabla donde fueron definidas.

Hay bastantes cosas incómodas respecto a las IDENTITY columns. Una de ellas, y no la menos importante, es la imposibilidad de ejecutar limpiamente un “Backup/Copy/Restore” con arcmain de una tabla que contenga una de ellas.

Así que: ¿Qué pasa si queremos eliminar la parte IDENTITY de una columna de una tabla? Hay sitios donde se recomienda agregar una columna a la tabla con el mismo tipo de la columna IDENTITY, hacer un UPDATE con los datos de ésta y luego eliminarla. No está mal, pero si la columna IDENTITY es el PRIMARY INDEX estamos jodidos. También se puede recurrir al viejo truco de CREATE TABLE + INSERT…SELECT + RENAME TABLE, pero asi estamos creando una tabla nueva (Nuevo object ID en la base de datos, GRANTS y estadísticas perdidos sin remisión…)

¿Habrá alguna forma más sencilla? Veamos.

 BTEQ -- Enter your SQL request or BTEQ command: 
CREATE MULTISET TABLE PRUEBA_ID(
   ID_N INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , 
   C_TXT VARCHAR(25));


 *** Table has been created. 
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command: 
INSERT INTO PRUEBA_ID VALUES (NULL, 'UNO');


 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command: 
INSERT INTO PRUEBA_ID VALUES(NULL, 'DOS');


 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command: 
SELECT * FROM PRUEBA_ID;


 *** Query completed. 2 rows found. 2 columns returned. 
 *** Total elapsed time was 1 second.

       ID_N  C_TXT
-----------  -------------------------
          1  UNO
          2  DOS

Aquí tenemos nuestra tabla de prueba con su columna IDENTITY que además es el PRIMARY INDEX.

¿Qué pasa si intentamos quitar el atributo IDENTITY redefiniendo la columna sin él?

 BTEQ -- Enter your SQL request or BTEQ command: 
ALTER TABLE PRUEBA_ID ADD ID_N INTEGER NOT NULL;


 *** Table has been modified. 
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command: 
INSERT INTO PRUEBA_ID VALUES(NULL, 'DOS');


 *** Insert completed. One row added. 
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command: 
SELECT * FROM PRUEBA_ID;


 *** Query completed. 3 rows found. 2 columns returned. 
 *** Total elapsed time was 1 second.

       ID_N  C_TXT
-----------  -------------------------
      10001  DOS
          1  UNO
          2  DOS

Pues que no hemos hecho nada.

¿Y si intentamos simplemente modificar el atributo de ‘ALWAYS’ a ‘BY DEFAULT’?

 BTEQ -- Enter your SQL request or BTEQ command: 
ALTER TABLE PRUEBA_ID ADD ID_N INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1);


ALTER TABLE PRUEBA_ID ADD ID_N INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1);
                                                                                                               $
 *** Failure 3706 Syntax error: Cannot add new Identity Column option.
                Statement# 1, Info =112 
 *** Total elapsed time was 1 second.

Agua. No se puede modificar.

Afortunadamente hay una opción enterrada en las profundidades de los manuales de documentación de Teradata:

BTEQ -- Enter your SQL request or BTEQ command: 
ALTER TABLE PRUEBA_ID DROP ID_N IDENTITY;                      


 *** Table has been modified. 
 *** Total elapsed time was 1 second.

Ahora, si intentamos un INSERT con NULL:

BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO PRUEBA_ID VALUES(NULL, 'DOS');

*** Failure 3811 Column 'ID_N' is NOT NULL. Give the column a value.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

Y la columna se comporta como una columna más:

BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO PRUEBA_ID VALUES(3,'TRES');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

BTEQ -- Enter your SQL request or BTEQ command:
SELECT * FROM PRUEBA_ID ;

*** Query completed. 4 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

ID_N C_TXT
----------- -------------------------
10001 DOS
3 TRES
1 UNO
2 DOS

Por último, vemos el DDL de la tabla, que lo confirma:

BTEQ -- Enter your SQL request or BTEQ command:
SHOW TABLE PRUEBA_ID ;

*** Text of DDL statement returned.
*** Total elapsed time was 1 second.

----------------------------------------------------------------------------------------------------
CREATE MULTISET TABLE CARLOS.PRUEBA_ID ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ID_N INTEGER NOT NULL,
C_TXT VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( ID_N );

BTEQ -- Enter your SQL request or BTEQ command:

En resumen, el atributo IDENTITY ha desaparecido, aunque la columna y su contenido permanecen.

Eso sí: no hay marcha atrás. Una vez eliminado el atributo IDENTITY no se puede volver a agregar a la columna con un ALTER:

 
BTEQ -- Enter your SQL request or BTEQ command:
ALTER TABLE CARLOS.PRUEBA_ID
ADD ID_N INTEGER NOT NULL 
      GENERATED ALWAYS AS IDENTITY (NO CYCLE START WITH 10);               

ADD ID_N INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (NO CYCLE START WITH 10);
                                                                      
 *** Failure 3706 Syntax error: Cannot add new Identity Column option.
                Statement# 1, Info =116
 *** Total elapsed time was 1 second.

No obstante, hemos conseguido eliminar el atributo IDENTITY manteniendo la tabla ‘intacta’, con sus GRANTs y estadísticas.

Saludos.

Carlos.

Anuncios

Moving forward…

8 julio \08\UTC 2016

De “retailer” a banca. De Teradata 14.10 a Teradata 15.10.

Lots of fun to come…

Saludos.

Carlos.


“Untranslatable character” y EF BF BD

9 junio \09\UTC 2016

Resulta que te pasan un fichero para cargar en tu tabla en Teradata. Resulta que el fichero es de unos 2GB (y decides cargarlo con fastload). Resulta que el fichero es de texto delimitado tipo csv (comma separated values). Resulta que el fichero es UTF-8 (porque el sistema origen utiliza UTF-8). Resulta que tus tablas son LATIN (aunque no debería ser un problema, porque los caracteres que van a llegar en el fichero deberían ser sólo caracteres occidentales).

Lo normal.

Te pones a ello y utilizas fastload -c UTF8 (también podrías haber usado SET SESSION CHARSET).

Y entonces aparecen un monton de filas en la tabla de error 1 con el código 6706 (“The string contains an untranslatable character“).

Mierda.

Y ahí empieza lo bueno…

Una investigación sobre los datos problemáticos nos llevan a descubrir que la inmensa mayoría presentan cadenas de texto que contienen los tres bytes EF BF BD.

¿Y qué significan esos tres bytes? Pues significan que el sistema origen debería haber cargado datos UTF-8 exclusivamente, pero en realidad no fue así (al menos para una parte de ellos).

Los tres bytes constituyen la codificación UTF-8 del UNICODE U+FFFD, “REPLACEMENT CHARACTER”, y no son otra cosa que el rombito negro con una pequeña interrogación dentro que se puede ver surfeando la web cuando la tabla de códigos de la página HTML no se corresponde con la que espera el navegador.

Es la forma de decir “Eh, que estos bytes que me mandas no son UTF-8 (la codificación de los caracteres tiene unas reglas específicas en cuanto a bytes y sus bits). No sé lo que es, así que pongo el simbolito (“<?>”) en su lugar.”

Es algo parecido a lo que hacen muchas aplicaciones cuando reciben caracteres que no pueden representar con la tabla de códigos que están utilizando y los sustituyen por una interrogación (sql*plus lo hace así, por ejemplo).

Evidentemente, no existe ningún símbolo LATIN correspondiente al “REPLACEMENT CHARACTER”, por lo que fastload deriva los registros del fichero que lo contienen a la tabla de error 1 con el código 6706, ya que no lo puede traducir.

sed at rescue

Bueno, pues sabiendo que los tres bytes corresponden al símbolo que se utiliza como sustitución de un caracter erróneo, ¿por qué no hacemos nosotros el tarbajo previo y lo sustituímos por una interrogación simple (3F en hexadecimal), que sí se podrá traducir a LATIN?

OK. Recurrimos a sed y su funcionalidad de sustitución de códigos hexadecimales:

$ > sed "s/\xEF\xBF\xBD/\x3F/g" file1.csv > file2.csv

A partir de ahí, utilizando file2.csv conseguimos cargar los datos sin pérdida de información, y la tabla de error 1 está vacía al final del proceso.

Saludos.

Carlos.


Historial de comandos en bteq (y sql*plus) con las flechas del teclado en Linux.

8 junio \08\UTC 2016

La funcionalidad de utilizar las flechas del teclado para navegar por la historia de las sentencias ejecutadas en bteq (y en sql*plus) cuando trabajamos en entornos Windows hace que cuando nos pasamos a entornos Linux nos pase siempre esto:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT DATE;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

    Date
--------
16/06/08

 BTEQ -- Enter your SQL request or BTEQ command:
^[[A

¡Arrrggg! Y siempre nos pasa. Una y otra vez.

Pero hay una solución: utilizar rlwrap, que es un ‘readline wrapper‘. Basta con buscarlo (por ejemplo aquí) e instalarlo.

La instalación es simple: descomprimir el ‘tarball‘ (.tar.gz) en un directorio de trabajo y ejecutar:

./configure 
make install

Yo lo hago como ‘root’ para evitar problemas (“no se puede crear el directorio /usr/local/share/man”).

¡Ojo! Hay que tener instaladas las librerías GNU readline y readline-devel para que el instalador no termine con error.

Una vez hecho esto, lo que suelo hacer es modificar los lanzadores de bteq y sql*plus que tengo en el escritorio sustituyendo las llamadas simples “bteq” y “sqlplus /nolog” por “rlwrap bteq” y “rlwrap sqlplus /nolog”.

A partir de ese momento tengo mis utilidades SQL de línea de comando favoritas funcionando exactamente igual que en Windows y puedo navegar por la historia de los comandos ejecutados con “flecha arriba” y “flecha abajo”.

Saludos.

Carlos.


Do you think you have a problem, mr. DBA?

3 junio \03\UTC 2016

Blocked

Veinte sesiones bloqueadas (una de ellas durante casi doce horas y media), una sesión activa ejecutando una ‘query‘ de casi tres horas y tres cuartos, y tres sesiones ‘delayed‘.

Saludos.

Carlos.


‘Bug’ en CREATE TABLE AS SELECT + USI.

1 junio \01\UTC 2016

Mi compañero F. y yo hemos encontrado un comportamiento extraño en Teradata que tiene toda la pinta de ser un ‘bug‘.

Al crear una tabla con CREATE TABLE AS SELECT que además tiene un USI (Unique Secondary Index) se produce la eliminación sin errores de las filas que son duplicados de dicho USI sin que se produzca un error (sé lo que estás pensando, pero esto ocurre aunque la tabla sea MULTISET).

Pero esto sólo ocurre en determinadas circunstancias: el USI contiene una columna generada en un CASE con funciones y otra que es concatenación de varias columnas, y además es el NUPI (Non-Unique Primary Index) de la tabla.

Aquí está el caso de prueba:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT * FROM DBC.DBCINFO;

 *** Query completed. 3 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

InfoKey                        InfoData
------------------------------ -------------------------
RELEASE                        14.10.06.06
LANGUAGE SUPPORT MODE          Standard
VERSION                        14.10.06.06
 

 BTEQ -- Enter your SQL request or BTEQ command:
SHOW TABLE TEST_CASE_SOURCE;

 *** Text of DDL statement returned.
 *** Total elapsed time was 1 second.

--------------------------------------------------------------------
CREATE MULTISET TABLE TEST_CASE_SOURCE ,
    NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
    (
      COLUMN00 CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COLUMN01 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COLUMN02 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COLUMN03 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COLUMN04 CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COLUMN_N VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( COLUMN04 );


 BTEQ -- Enter your SQL request or BTEQ command:
SELECT COUNT(1) FROM TEST_CASE_SOURCE;


 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

  Count(1)
-----------
    3736103

Creamos la tabla nueva con las condiciones descritas arriba:

 BTEQ -- Enter your SQL request or BTEQ command:
DROP TABLE TEST_CASE_DEST ;

*** Failure 3807 Object 'TEST_CASE_DEST' does not exist.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
CREATE MULTISET TABLE TEST_CASE_DEST
AS
(
  SELECT
    COLUMN00,
  (COLUMN01||COLUMN02||COLUMN03||'0'||COLUMN04) (CHAR(16)) AS COLUMNPI,
  CASE
      WHEN COLUMN_N IS NULL THEN CAST('000' AS CHAR(3))
      ELSE TD_SYSFNLIB.LPAD(TRIM(COLUMN_N),3,'0')
  END AS COLUMN_N
  FROM TEST_CASE_SOURCE
)WITH DATA
PRIMARY INDEX ( COLUMNPI )
UNIQUE INDEX(COLUMNPI, COLUMN_N, COLUMN00)
;


 *** Table has been created.
 *** Total elapsed time was 7 seconds.

Podemos ver que la tabla se ha creado sin errores, pero:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT COUNT(1) FROM TEST_CASE_DEST;


 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

  Count(1)
-----------
    3735548

La tabla creada tiene menos filas que la tabla original (3735548 en vez de 3736103).

La cosa cambia si la tabla es creada como NOPI (NO Primary Index):

 BTEQ -- Enter your SQL request or BTEQ command:
DROP TABLE TEST_CASE_DEST ;


 *** Table has been dropped.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
CREATE MULTISET TABLE TEST_CASE_DEST
AS
(
  SELECT
    COLUMN00,
  (COLUMN01||COLUMN02||COLUMN03||'0'||COLUMN04) (CHAR(16)) AS COLUMNPI,
  CASE
      WHEN COLUMN_N IS NULL THEN CAST('000' AS CHAR(3))
      ELSE TD_SYSFNLIB.LPAD(TRIM(COLUMN_N),3,'0')
  END AS COLUMN_N
  FROM TEST_CASE_SOURCE
)WITH DATA
NO PRIMARY INDEX
UNIQUE INDEX(COLUMNPI, COLUMN_N, COLUMN00)
;

*** Failure 2803 Secondary index uniqueness violation in target table.
                Statement# 1, Info =0
 *** Total elapsed time was 6 seconds.

Aquí el error esperado sí se produce.

Pero hay más: Si creamos la misma tabla con CREATE TABLE AS SELECT … WITH NO DATA y hacemos luego el INSERT … SELECT el error aparece también:

 BTEQ -- Enter your SQL request or BTEQ command:
DROP TABLE TEST_CASE_DEST ;

*** Failure 3807 Object 'TEST_CASE_DEST' does not exist.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.  


 BTEQ -- Enter your SQL request or BTEQ command:
CREATE MULTISET TABLE TEST_CASE_DEST
AS
(
  SELECT
    COLUMN00,
  (COLUMN01||COLUMN02||COLUMN03||'0'||COLUMN04) (CHAR(16)) AS COLUMNPI,
  CASE
      WHEN COLUMN_N IS NULL THEN CAST('000' AS CHAR(3))
      ELSE TD_SYSFNLIB.LPAD(TRIM(COLUMN_N),3,'0')
  END AS COLUMN_N
  FROM TEST_CASE_SOURCE
)WITH NO DATA

Es decir, la misma SELECT utilizada en la creación de la tabla -que funcionó sin errores en el primer caso- utilizada ahora con WITH NO DATA y vuelta a utilizar en un INSERT … SELECT posterior vuelve a dar el error esperado (que es el funcionamiento correcto).

Saludos.

Carlos.


De registros, campos, filas y columnas…

15 abril \15\UTC 2016

Los que llaman “registros” y “campos” a las filas y columnas de las tablas de una base de datos son como los que llaman “reina” a la dama en ajedrez.

Columns are not fields. Rows are not records. Tables are not files.” – Joe Celko

Saludos.

Carlos.