NUMBERs, JOINs y ORs…

2 marzo \02\UTC 2017

Desde la versión 14, Teradata implementa el tipo “Oracle” NUMBER. Y lo hace a su imagen y semejanza: con el almacenamiento en forma de exponente y mantisa. El motivo es, principalmente, facilitar las migraciones desde Bases de Datos Oracle a Teradata (de las que he visto unas cuantas y he participado en otras). Anteriormente, al no disponer Teradata de un tipo análogo al NUMBER de Oracle, había que elegir entre los numéricos “nativos”, con las dificultades que ello conlleva y la propensión a errores por elecciones incorrectas.

El tipo en sí presenta ciertas ventajas y varias particularidades, como el espacio de almacenamiento variable (de 2 a 18 bytes), la posibilidad de variar escala y precisión sin modificar las filas o la precisión de 18 dígitos (o incluso más) en las operaciones con ellos. Por otra parte, es un tipo en coma flotante (floating point) que puede no ser todo lo exacto que necesitamos y puede llevar a pequeñas incorrecciones en los cálculos.

Así que, en teoría, no hay ningún problema para utilizar NUMBER(n, m) en Teradata tal como lo hacemos en Oracle, donde es el tipo numérico por excelencia.

Pero a veces las cosas no son lo que parecen:

 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                        15.10.02.01
VERSION                        15.10.02.03
LANGUAGE SUPPORT MODE          Standard

 BTEQ -- Enter your SQL request or BTEQ command:


CREATE MULTISET TABLE TESTDB.TEST1 (
   ID_C CHAR(2) NOT NULL,
   ID_N NUMBER
)
PRIMARY INDEX (ID_C);


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


 BTEQ -- Enter your SQL request or BTEQ command:


INSERT INTO TESTDB.TEST1 VALUES ('98', 999999999999998);


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


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO TESTDB.TEST1 VALUES ('99', 999999999999999);


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


 BTEQ -- Enter your SQL request or BTEQ command:


CREATE MULTISET TABLE TESTDB.TEST2 (
   ID_C CHAR(2) NOT NULL,
   ID_N NUMBER(15, 0),
   C_TXT CHAR(6)
)
PRIMARY INDEX (ID_C);


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


 BTEQ -- Enter your SQL request or BTEQ command:


INSERT INTO TESTDB.TEST2 VALUES ('98', 999999999999998, 'OCHO');


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


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO TESTDB.TEST2 VALUES ('99', 999999999999999, 'NUEVE');


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


 BTEQ -- Enter your SQL request or BTEQ command:


SELECT c.C_TXT
      ,a.ID_C
      ,a.ID_N
  FROM ( SELECT ID_C
               ,ID_N
          FROM TESTDB.TEST1
         WHERE ( ID_C = '98' AND
                 ID_N = 999999999999998 )
            OR ( ID_C = '99' AND
                 ID_N = 999999999999999 )
       ) a
  LEFT JOIN TESTDB.TEST2 c
         ON ( a.ID_C = c.ID_C
          AND a.ID_N = c.ID_N )
;


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

C_TXT   ID_C                                      ID_N
------  ----  ----------------------------------------
(null)  99                             999999999999999
OCHO    98                             999999999999998

 BTEQ -- Enter your SQL request or BTEQ command:


SELECT c.C_TXT
      ,a.ID_C
      ,a.ID_N
  FROM ( SELECT ID_C
               ,ID_N
          FROM TESTDB.TEST1
         WHERE ( ID_C = '99' AND
                 ID_N = 999999999999999 )
            OR ( ID_C = '98' AND
                 ID_N = 999999999999998 )
       ) a
  LEFT JOIN TESTDB.TEST2 c
         ON ( a.ID_C = c.ID_C
          AND a.ID_N = c.ID_N )
;


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

C_TXT   ID_C                                      ID_N
------  ----  ----------------------------------------
(null)  98                             999999999999998
NUEVE   99                             999999999999999

 BTEQ -- Enter your SQL request or BTEQ command:

Como podemos ver, la misma query da resultados diferentes sólo con cambiar el orden de los filtros en la parte WHERE … OR de la subselect “a”.

¡Pero eso es imposible! Todos sabemos que el ‘OR’ es conmutativo.

Además, ambos resultados son erróneos.

¿Y qué puede causar este comportamiento? Pues, presumiblemente, los resultados intermedios de la subselect “a” varían en cuanto al tipo elegido según lleguen las primeras filas como 999999999999998 ó 999999999999999. El tipo definido para ID_N en TEST1 es NUMBER, sin especificar escala ni precisión, mientras que en TEST2 es NUMBER(15, 0). Aunque externamente “NUMBER es NUMBER”, en algún lugar se producen diferencias que hacen que el JOIN no funcione como se espera.

Por eso, si estamos en lo cierto, al hacer un CAST explícito sobre las columnas NUMBER en la subselect todo debería funcionar OK:

SELECT c.C_TXT
      ,a.ID_C
      ,a.ID_N
  FROM ( SELECT ID_C
               ,ID_N (NUMBER(15,0))  --CAST!!
          FROM TESTDB.TEST1
         WHERE ( ID_C = '98' AND
                 ID_N = 999999999999998 )
            OR ( ID_C = '99' AND
                 ID_N = 999999999999999 )
       ) a
  LEFT JOIN TESTDB.TEST2 c
         ON ( a.ID_C = c.ID_C
          AND a.ID_N = c.ID_N )
;


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

C_TXT   ID_C              ID_N
------  ----  ----------------
NUEVE   99     999999999999999
OCHO    98     999999999999998

 BTEQ -- Enter your SQL request or BTEQ command:


SELECT c.C_TXT
      ,a.ID_C
      ,a.ID_N
  FROM ( SELECT ID_C
               ,ID_N (NUMBER(15,0))  --CAST!!
          FROM TESTDB.TEST1
         WHERE ( ID_C = '99' AND
                 ID_N = 999999999999999 )
            OR ( ID_C = '98' AND
                 ID_N = 999999999999998 )
       ) a
  LEFT JOIN TESTDB.TEST2 c
         ON ( a.ID_C = c.ID_C
          AND a.ID_N = c.ID_N )
;


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

C_TXT   ID_C              ID_N
------  ----  ----------------
NUEVE   99     999999999999999
OCHO    98     999999999999998

 BTEQ -- Enter your SQL request or BTEQ command:

En efecto, vemos que con CAST explícito todo vuelve a la normalidad y la SELECT da el resultado esperado (y correcto esta vez).

Claro que esto no deja de ser un “workaround“. Supongo que alguien tiene trabajo en Rancho Bernardo.

Por otra parte, siempre se debería especificar escala y precisión en los tipos NUMBER (en Oracle también).

Saludos.

Carlos.


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.


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.


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.


Ejecutando DML’s y Stored Procedures en Oracle con TPT.

6 abril \06\UTC 2016

TPT, mediante su ODBC OPERATOR, permite acceder a otras bases de datos (Oracle, SqlServer…) generalmente para extraer datos y moverlos y normalmente para cargarlos en Teradata.

ODBC OPERATOR es un operador de tipo PRODUCER. Esto es, lee datos de una fuente (ODBC) y los escribe en un “data stream” para que los utilice un CONSUMER. Los operadores así conectados deben compartir un mismo SCHEMA. Así, se puede mediante un ODBC OPERATOR leer el contenido de una tabla Oracle y cargarlo en una tabla Teradata sin necesidad de conjugar exportaciones a fichero (o pipe) desde sqlplus con cargas posteriores a Teradata (con Fastload, Multiload, TPump, bteq o incluso TPT).

El procedimiento es bastante sencillo y se pueden encontrar muchos ejemplos descriptivos de cómo hacerlo. Hay que indicar que Teradata sólo soporta el funcionamiento de los ODBC OPERATORs utilizando los “drivers” ODBC de DataDirect. Hay quien afirma haber conseguido que funcionen usando “drivers” de Oracle o MicroSoft, pero yo sólo tuve éxito mediante los citados “drivers” de DataDirect.

Por supuesto, el primer paso es tener los “drivers” instalados y el segundo es configurar un origen de datos ODBC (en nuestro caso a Oracle 11gR2).

Una vez hecho eso se pueden efectuar pruebas de extracción de datos Oracle mediante el ODBC OPERATOR sólo para verificar conectividades y asegurarse de que todo funciona correctamente.

Pero ahora viene lo bueno: ¿Podríamos ejecutar mediante TPT sentencias DML (INSERT, UPDATE, DELETE) o incluso ejecutar procedimientos almacenados (stored procedures)?

La respuesta corta es NO. Pero la respuesta larga es SÍ.

En efecto, el ODBC OPERATOR sólo permite sentencias SELECT y, como es un PRODUCER, necesita de un CONSUMER que lea datos extraídos, por lo que, en teoría, no es posible ejecutar DMLs.

Pero la respuesta larga es SÍ. ¿Cómo? Pues haciendo un poco de trampa.

Lo que viene a continuación es un ejemplo válido sólo a efectos didácticos, y únicamente pretende mostrar cómo se podría conseguir el objetivo buscado y quizá indicar un camino de inicio para procesos más complejos.

Nos vamos a valer del hecho de que Oracle permite definir funciones con código subyacente. Si conseguimos incluir una llamada a una función de este tipo tendremos la puerta abierta para ejecutar DMLs y SPs:

Así pues si en Oracle generamos una función algo así como:

CREATE OR REPLACE FUNCTION CARLOS.PRUEBA_INSERT_3(iDesde NUMBER, 
   iHasta NUMBER)
RETURN NUMBER
AS
BEGIN
   FOR i IN iDesde..iHasta LOOP
      INSERT INTO CARLOS.PRUEBA03(ID_N, C_TXT) VALUES (i, TO_CHAR(i));
   END LOOP;
   COMMIT;
   RETURN(0);
EXCEPTION
   WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE (SQLERRM);   
      RETURN(-1);
END;

Entonces, podremos definir un TPT job que llame a dicha función en un SELECT. Como necesitamos un CONSUMER para recoger el resultado del SELECT, utilizaremos un simple $FILE_WRITER que escriba el retorno de la función en un fichero:

DEFINE JOB ODBC_DML
DESCRIPTION 'ODBC DML'
(
   DEFINE SCHEMA Oracle_Return_Schema (
      id_n  VARCHAR(11)                
   );

   DEFINE OPERATOR ODBC01
   DESCRIPTION 'Teradata Parallel Transporter ODBC Operator'
   TYPE ODBC
   SCHEMA Oracle_Return_Schema
   ATTRIBUTES (
      VARCHAR DSNName = 'ORA11G_CENTOS6DD',  /* DataDirect ODBC Driver */
      VARCHAR UserName = @UserName,
      VARCHAR UserPassword = @UserPassword,
      VARCHAR TraceLevel = 'All',
      VARCHAR TruncateData = 'Yes',
      VARCHAR SelectStmt = 'SELECT CARLOS.PRUEBA_INSERT_3(1,3) FROM DUAL;'
   );

   DEFINE OPERATOR FILE_WRITER_1
   TYPE DATACONNECTOR CONSUMER
   SCHEMA * 
   ATTRIBUTES (
      VARCHAR FileName = 'Prueba_Insert_Oracle.csv',
      VARCHAR Format = 'DELIMITED',
      VARCHAR TextDelimiter=';',
      VARCHAR IndicatorMode = 'N',
      VARCHAR OpenMode = 'Write'
   );

   STEP EXPORTAR (   
      APPLY TO OPERATOR (FILE_WRITER_1)
         SELECT * FROM OPERATOR (ODBC01);
   );
   
);

Esto debería valer. Pero primero, vamos a verificar el funcionamiento de la función con sqlplus:

CARLOS@ORA11GR2> SET SERVEROUTPUT ON;
CARLOS@ORA11GR2> SELECT CARLOS.PRUEBA_INSERT_3 FROM DUAL;

PRUEBA_INSERT_3
---------------
             -1

ORA-14551: cannot perform a DML operation inside a query
CARLOS@ORA11GR2>     

¡Es verdad! ¡No podemos hacer DML en un SELECT! Pero lo podemos soslayar utilizando una transacción autónoma (PRAGMA AUTONOMOUS_TRANSACTION). Esto, por supuesto, tiene importantes efectos en el proceso, pero nos permite hacer:

CARLOS@ORA11GR2> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION CARLOS.PRUEBA_INSERT_3(iDesde NUMBER, 
  2     iHasta NUMBER)
  3  RETURN NUMBER
  4  AS
  5  PRAGMA AUTONOMOUS_TRANSACTION;
  6  BEGIN
  7   FOR i IN iDesde..iHasta LOOP
  8      INSERT INTO CARLOS.PRUEBA03(ID_N, C_TXT) VALUES (i, TO_CHAR(i));
  9   END LOOP;
 10   COMMIT;
 11   RETURN(0);
 12  EXCEPTION
 13   WHEN OTHERS THEN
 14      RETURN(-1);
 15* END;
CARLOS@ORA11GR2> /

Function created.

CARLOS@ORA11GR2> SELECT PRUEBA_INSERT_3(1, 3) FROM DUAL;

PRUEBA_INSERT_3(1,3)
--------------------
                   0

CARLOS@ORA11GR2> SELECT * FROM PRUEBA03;

      ID_N C_TXT
---------- ----------
         1 1
         2 2
         3 3

CARLOS@ORA11GR2> 

Y ahora podemos ejecutar el TPT (sustituyendo ‘SELECT CARLOS.PRUEBA_INSERT_3(1,3) FROM DUAL;’ por ‘SELECT CARLOS.PRUEBA_INSERT_3(4,6) FROM DUAL;’ ):

C:\Carlos\TPT\TPT_Oracle_To_Teradata>tbuild -f Prueba_Insert_Oracle.tpt
Teradata Parallel Transporter Version 14.10.00.11 64-Bit
Job log: C:\Program Files\Teradata\client\14.10\Teradata Parallel Transporter/logs/carlos-57.out
Job id is carlos-57, running on CARLOS03
Teradata Parallel Transporter DataConnector Operator Version 14.10.00.11
FILE_WRITER_1: Instance 1 directing private log report to 'dtacop-carlos-6672-1'.
FILE_WRITER_1: DataConnector Consumer operator Instances: 1
Teradata Parallel Transporter ODBC Operator Version 14.10.00.11
ODBC01: private log not specified
FILE_WRITER_1: ECI operator ID: 'FILE_WRITER_1-6672'
FILE_WRITER_1: Operator instance 1 processing file 'C:\Trazas\Prueba_Insert_Oracle.csv'.
ODBC01: connecting sessions
ODBC01: sending SELECT request
ODBC01: data retrieval complete
ODBC01: Total Rows Exported:  1
FILE_WRITER_1: Total files processed: 1.
ODBC01: disconnecting sessions
ODBC01: Total processor time used = '0.0312002 Second(s)'
ODBC01: Start : Wed Apr 06 19:26:48 2016
ODBC01: End   : Wed Apr 06 19:26:53 2016
Job step EXPORTAR completed successfully
Job carlos completed successfully
Job start: Wed Apr 06 19:26:45 2016
Job end:   Wed Apr 06 19:26:53 2016

C:\Carlos\TPT\TPT_Oracle_To_Teradata>

Y en Oracle:

CARLOS@ORA11GR2> SELECT * FROM PRUEBA03;

      ID_N C_TXT
---------- ----------
         1 1
         2 2
         3 3
         4 4
         5 5
         6 6

6 rows selected.

CARLOS@ORA11GR2> 

Con lo que hemos conseguido ejecutar un INSERT en Oracle… haciendo trampas, claro.

Pero lo bueno es que podríamos ejecutar un procedimiento almacenado también. Basta con crear una función envoltorio que llame a un procedimiento almacenado:

CREATE OR REPLACE PROCEDURE CARLOS.PRUEBA_INSERT_4_SP( iDesde NUMBER, 
   iHasta NUMBER)
AS
BEGIN
   FOR i IN iDesde..iHasta LOOP
      INSERT INTO CARLOS.PRUEBA03(ID_N, C_TXT) VALUES (i, TO_CHAR(i));
   END LOOP;
END;


CREATE OR REPLACE FUNCTION CARLOS.PRUEBA_INSERT_4_FN( iDesde NUMBER, 
   iHasta NUMBER)
RETURN NUMBER
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   CARLOS.PRUEBA_INSERT_4_SP( iDesde, iHasta);
   COMMIT;
   RETURN(0);
EXCEPTION
   WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
      RETURN(-1);
END;

Si modificamos el “SelectStmt” del script de TPT:

VARCHAR SelectStmt = 'SELECT CARLOS.PRUEBA_INSERT_4_FN (7,20) FROM DUAL;'

Y ejecutamos:

C:\Carlos\TPT\TPT_Oracle_To_Teradata>tbuild -f Prueba_Insert_Oracle.tpt
Teradata Parallel Transporter Version 14.10.00.11 64-Bit
Job log: C:\Program Files\Teradata\client\14.10\Teradata Parallel Transporter/logs/carlos-58.out
Job id is carlos-58, running on CARLOS03
Teradata Parallel Transporter DataConnector Operator Version 14.10.00.11
FILE_WRITER_1: Instance 1 directing private log report to 'dtacop-carlos-6892-1'.
FILE_WRITER_1: DataConnector Consumer operator Instances: 1
Teradata Parallel Transporter ODBC Operator Version 14.10.00.11
ODBC01: private log not specified
FILE_WRITER_1: ECI operator ID: 'FILE_WRITER_1-6892'
FILE_WRITER_1: Operator instance 1 processing file 'C:\Trazas\Prueba_Insert_Oracle.csv'.
ODBC01: connecting sessions
ODBC01: sending SELECT request
ODBC01: data retrieval complete
ODBC01: Total Rows Exported:  1
FILE_WRITER_1: Total files processed: 1.
ODBC01: disconnecting sessions
ODBC01: Total processor time used = '0.0156001 Second(s)'
ODBC01: Start : Wed Apr 06 19:45:35 2016
ODBC01: End   : Wed Apr 06 19:45:41 2016
Job step EXPORTAR completed successfully
Job carlos completed successfully
Job start: Wed Apr 06 19:45:31 2016
Job end:   Wed Apr 06 19:45:41 2016

C:\Carlos\TPT\TPT_Oracle_To_Teradata>

Vemos que:

CARLOS@ORA11GR2> SELECT * FROM PRUEBA03;

      ID_N C_TXT
---------- ----------
         1 1
         2 2
         3 3
         4 4
         5 5
         6 6
         7 7
         8 8
         9 9
        10 10
        11 11
        12 12
        13 13
        14 14
        15 15
        16 16
        17 17
        18 18
        19 19
        20 20

20 rows selected.

CARLOS@ORA11GR2> 

Y así podríamos ejecutar DMLs y stored procedures usando TPT.

The rest is up to you…

Saludos.

Carlos.


SQL a Teradata desde Oracle (II): Oracle Database Gateways

21 abril \21\UTC 2015

Aprovechando que tenemos instalado en el servidor CentOS 6 el driver ODBC de Teradata y como allí hay instalado un Oracle 11g, vamos a retomar el viejo tema de la conexión de Oracle a Teradata. Anteriormente (Oracle 10g) esto se hacía mediante “Heterogeneous Services”. Más recientemente esto se hace mediante los llamados “Oracle Database Gateways”, y en nuestro caso particular con el “Oracle Database Gateway for ODBC”, heredero directo de aquellos “Heterogeneous Services” que configuramos en su día en un servidor Windows.

Los pasos son fundamentalmente los mismos, aunque con las particularidades propias del Sistema Operativo (de Windows a CentOS). También cambian algunos pequeños detalles, como los nombres de los módulos (ya no se llama “hsodbc”, sino que ahora su nombre es “dg4odbc”).

Así pues, vamos por partes.

Si queremos que la configuración ODBC sea global y no sólo para un usuario (quién sabe quién arrancará Oracle) deberemos especificarlo en el entorno (esto es análogo a la definición de DSN “de sistema” en Windows). En nuestro caso hemos creado el fichero /opt/odbc/.odbc.ini con los datos del DSN ODBC de Teradata (TD1410_SLES11, los mismos que utilizamos aquí). Definimos para ello una variable de entorno “ODBCINI” que apunte a él.

Con esto (y tras haberlo probado, con tdxodbc como también dijimos aquí) podemos comenzar.

Primero hay que crear un fichero de parámetros de inicialización para el “Gateway ODBC”. Se crea en $ORACLE_HOME/hs/admin y su nombre debe ser init<SID>.ora, siendo SID el identificador de sistema para el sitio remoto. En nuestro caso el fichero se llamará initTD1410.ora y, entre otros, contendrá los parámetros:

HS_FDS_CONNECT_INFO = TD1410_SLES11
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

En realidad /usr/lib64/libodbc.so es un symbolic link al “driver manager” odbc de Teradata que instalamos en su día en /opt/teradata/client/ODBC_64/lib/libodbc.so

Una vez con nuestro initTD1410.ora, vamos a configurar el listener para el “Gateway”, para ello añadiremos una entrada en listener.ora:

...
  (SID_DESC= 
     (SID_NAME = TD1410)
     (ORACLE_HOME = <valor de $ORACLE_HOME>)
     (PROGRAM = dg4odbc)
     (ENVS = "LD_LIBRARY_PATH=/usr/lib64:<valor de $ORACLE_HOME>/lib")
  )

Tras hacer estas modificaciones debemos parar (lsnrctl stop) y volver a arrancar (lsnrctl start) el listener y ver que su estado (lsnrctl status) es correcto.

[carlos@CentOS6 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production
...
Service "TD1410" has 1 instance(s).
  Instance "TD1410", status UNKNOWN, has 1 handler(s) for this service...
  ...

Ahora hay que hacer que Oracle pueda comunicarse con el “Gateway”. Y esto lo hacemos mediante el nunca bien conocido y siempre temido tnsnames.ora (¡Uuuuhhhh!, ¡tnsnames, qué miedo!). Pero no es para tanto. Sólo hay que incluir el “connect descriptor” y ya está:

teradata.remote =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CentOS6.2_64)(PORT = 1521))
    (CONNECT_DATA = (SID = TD1410))
    (HS=OK)
  )

Es muy importante no olvidarse del (HS=OK) ni de hacerse un lío con tanto paréntesis abierto y cerrado.

Podemos comprobar que todo va bien con tnsping:

[carlos@CentOS6 ~]$ tnsping teradata.remote

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 21-APR-2015 19:58:59

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = CentOS6.2_64)(PORT = 1521)) (CONNECT_DATA = (SID = TD1410)) (HS=OK))
OK (0 msec)
[carlos@CentOS6 ~]$ 

Bueno, pues ya casi está. Sólo queda crear el dblink :

CARLOS@CentOS6.2_64> CREATE DATABASE LINK teradata.remote 
  2  CONNECT TO carlos IDENTIFIED BY xxxxxxxx 
  3  USING 'teradata.remote';

Database link created.

Y ver que efectivamente podemos seleccionar datos de Teradata desde Oracle:

CARLOS@CentOS6.2_64> select * 
  2  from carlos.prueba01@teradata.remote;

      ID_N D_DATE
---------- -------------------
         3 2014/09/18 00:00:00
         1 2014/09/18 00:00:00
         2 2014/09/18 00:00:00

Mission accomplished!

Saludos.

Carlos.


DIRTY READS

13 febrero \13\UTC 2015

 

Teradata funciona en ISOLATION LEVEL SERIALIZABLE por defecto. Esto significa que para garantizar la integridad de los datos se implementan bloqueos que impiden modificaciones de los mismos por otras transacciones mientras estos están siendo leídos. Esta política de bloqueos es bastante restrictiva en sí (READERS BLOCK WRITERS) aunque es la más segura a la hora de garantizar integridad. Hay que tener en cuenta que en Datawarehouses lo normal es leer mucho y modificar poco (READERS DON’T BLOCK READERS). Otras bases de datos más orientadas a OLTP funcionan de otras formas (READ COMMITED en Oracle, por ejemplo. READERS DON’T BLOCK WRITERS)

Teradata nos permite relajar los bloqueos de lectura de forma que no interfiramos la actividad de actualización de tablas sobre las que únicamente queremos leer para que otras transacciones puedan desarrollar dichas actividades de actualización. La forma más habitual de hacerlo es con el modificador LOCKING … FOR ACCESS. Con este modificador podremos leer los datos sin bloquearlos, permitiendo actividades de actualización por parte de otras transacciones. Pero a un coste: estaremos permitiendo los llamados ‘dirty reads’: lectura de datos modificados por otras transacciones pero sobre los que no se ha hecho COMMIT (ISOLATION LEVEL READ UNCOMMITED). Esto puede tener consecuencias no deseadas.

Lo podemos ver con un ejemplo ‘de la vida real’. Uso la siguiente ‘query‘ para monitorizar la actividad sobre una tabla por parte de un agente externo que realiza constantes borrados e inserciones sobre ella:

 BTEQ -- Enter your SQL request or BTEQ command:
LOCKING TABLE THE_DATABASE.THE_TABLE FOR ACCESS
SELECT CURRENT_TIMESTAMP (FORMAT 'YYYY-MM-DDbhh:mi:ss') FECHA,
       ZEROIFNULL(SUM (CASE WHEN TS_PROCESADO IS NULL 
                            THEN 1 ELSE 0 END)) PENDIENTES,
       ZEROIFNULL(SUM (CASE WHEN TS_PROCESADO IS NOT NULL 
                            THEN 1 ELSE 0 END)) PROCESADOS,
       PENDIENTES + PROCESADOS TOTAL,
       MAX(TS_TIMESTAMP) ULTIMO_INSERTADO
  FROM THE_DATABASE.THE_TABLE
;


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

              FECHA PENDIENTES PROCESADOS TOTAL    ULTIMO_INSERTADO
------------------- ---------- ---------- ----- -------------------
2015-02-11 09:36:00          5         15    20 2015-02-11 09:36:00

 BTEQ -- Enter your SQL request or BTEQ command:
=1


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

              FECHA PENDIENTES PROCESADOS TOTAL    ULTIMO_INSERTADO
------------------- ---------- ---------- ----- -------------------
2015-02-11 09:36:02          0         20    20 2015-02-10 12:32:17

Aquí se ve como en la primera ejecución de la query estábamos leyendo datos modificados por una transacción del agente (había borrado cinco filas y las había vuelto a insertar como ‘pendientes’) pero sobre los que aun aun no había efectuado un COMMIT. Por algún problema en la transacción -o por una orden explícita- ésta es abortada con el consiguiente ROLLBACK, de tal forma que las segunda ejecución de nuestra SELECT (apenas dos segundos después) muestra los datos tal y como estaban al comienzo de la transacción abortada.

Hay que hacer notar que, al no existir un COMMIT, hemos leído datos que nunca han existido “realmente” en la base de datos.

Como siempre: no se trata de que esto sea algo malo o bueno, se trata de saber las necesidades y asumir los posibles efectos de aplicar las diferentes técnicas.

Nota: mis compañeros A. y N. lo vieron ‘in situ‘ y me animaron a escribir esto.

Saludos.

Carlos.