I am loading a column from an Oracle table defined as DECIMAL(30,3). I am using the ODBC interface to exract the data. I have the columns defined as DECIMAL(30,3) in the Teradata table. The completes without errors but the values are all divided by 1000. For example, Oracle value = 1000, Teradata = 1. Maybe it's an ODBC setting. Thanks in advance for any help.
U:\>tlogview -j testj-224 Found CheckPoint file: C:\Program Files\Teradata\Client\13.0\Teradata Parallel ransporter\\/checkpoint\testjLVCP This is a restart job; it restarts at step Insert_Into_Tables. Teradata Parallel Transporter Executor Version 13.00.00.02 Teradata Parallel Transporter Coordinator Version 13.00.00.02 Teradata Parallel Transporter Executor Version 13.00.00.02 Teradata Parallel Transporter ODBC Operator Version 13.00.00.02 ODBC_Operator: private log specified: odbc_log Teradata Parallel Transporter Load Operator Version 13.00.00.02 Load_Operator: private log specified: load_log ODBC_Operator: connecting sessions ODBC_Operator: TPT17175: Warning: Message received from ODBC driver: STATE=S1C00, CODE=0, MSG='[Oracle][ODBC]Driver not capable.' Load_Operator: connecting sessions Load_Operator: preparing target table Load_Operator: entering Acquisition Phase Job is running in Buffer Mode Task(APPLY_1): checkpoint completed, status = Success Task(SELECT_2): checkpoint completed, status = Success ODBC_Operator: sending SELECT request ODBC_Operator: data retrieval complete Task(SELECT_2) ready to checkpoint
Task(SELECT_2): checkpoint completed, status = Success Task(APPLY_1): checkpoint completed, status = Success Task(SELECT_2) ready to take the EOD checkpoint
Task(SELECT_2): checkpoint completed, status = Success Task(APPLY_1): checkpoint completed, status = Success Load_Operator: entering Application Phase Load_Operator: Statistics for Target Table: 'DLTB101_ENG.testj' Load_Operator: Total Rows Sent To RDBMS: 5 Load_Operator: Total Rows Applied: 5 PXTB_Terminate: Message Buffers Sent/Received = 1, Total Rows Received = 0, Tot l Rows Sent = 0 PXTB_Terminate: Message Buffers Sent/Received = 1, Total Rows Received = 0, Tot l Rows Sent = 0 ODBC_Operator: disconnecting sessions Load_Operator: disconnecting sessions ODBC_Operator: Total processor time used = '0.09375 Second(s)' ODBC_Operator: Start : Wed Mar 23 09:12:11 2011 ODBC_Operator: End : Wed Mar 23 09:12:30 2011 Load_Operator: Total processor time used = '0.328125 Second(s)' Load_Operator: Start : Wed Mar 23 09:12:11 2011 Load_Operator: End : Wed Mar 23 09:12:33 2011 Job step Insert_Into_Tables terminated (status 4) Job testj completed successfully, but with warning(s). Total available memory: 10000000 Largest allocable area: 10000000 Memory use high water mark: 71252 Free map size: 1024 Free map use high water mark: 20 Free list use high water mark: 0
Since you have such a small row set size right now, I will ask for you to do something. In the script, in the ODBC operator definition, add this: VARCHAR TraceLevel = 'row' And re-run the job. You will then need to look at the diagnostic information. To extract everything out, do this from the command line: $ tlogview -j -f "*" -g where is the name of the job (but I see you knew that already, that is good). Redirect the tlogview command to a text file. Look in the text file for all output prefixed by CONVERTDECIMAL. Provide only those messages (for now). You should see something like: CONVERTDECIMAL: entering CONVERTDECIMAL: column data: '%s' where "%s" is the decimal data string from the ODBC driver.
Here is what might be happening. If we get a string of "1000" we convert that to an integer and send to Teradata. If Teradata receives a value of 1000 and the column has a scale of 3, then Teradata will view the value as 1. For a decimal value on a DECIMAL(30,3) column to be considered equal to 1000.000 we need to receive a string of "1000.000" from the ODBC driver. I am wondering if the driver is not returning to us the value we need.
I also need to point out 2 things:
1. we only support the ODBC drivers from DataDirect (you are using the drivers from Oracle) 2. the TraceLevel of "row" is only to be used when instructed; it provides a lot of detailed information and if you try this while loading a lot of data, you could easily run out of disk space, the trace file would get pretty large
When I use the same Oracle ODBC driver in SQL Assistant, I get the correct results. The info you requested follows. I think it looks as you expected. Any suggestions on how to make Oracle ODBC work would be appreciated. I will try to obtain the ODB drivers from Data Direct. Thanks for your help.
Even though your source table has DECIMAL(30,3), if you know your data will never be larger than would fit into DECIMAL(18,3), can you change the TPT script so that the field has DECIMAL(18,3) and re-run the job?