Using Oracle Data Integrator with Exasol. Problems with decimal (14,4)

Gallus
SQL-Fighter

Hello together

We have in our company a team, which needs to access the EXASOL database using Oracle Data Integrator (ODI). They only need to perform simple "Select" Statements from views. We were able to somehow setup the ODI Topoligy (using the lastest Exasol jdbc driver 7.0.7), but are not sure whether this is correctly done.

We were also able to access some views without problems, however whenever a view has a decimal(14,4) we get the following exception

java.sql.SQLException: type error
at com.exasol.jdbc.Column.type_error(Column.java:28)
at com.exasol.jdbc.Column.getBigDecimal(Column.java:102)
at com.exasol.jdbc.EXAResultSet.getBigDecimal(EXAResultSet.java:631)
at com.borland.dx.sql.dataset.JdbcProvider.a(Unknown Source)
at com.borland.dx.sql.dataset.JdbcProvider.c(Unknown Source)

.....

Would anybody have a hint how we could solve the problem?

Kind Regards

Gallus

9 REPLIES 9

ThomasE
Contributor

Does anybody know how to use the Exasol DB from Oracle ODI without thirdparty tools?

jens_areto
SQL-Fighter

Hey Thomas, 

sorry for taking this long to answer your question. Thanks for describing in detail what you did in ODI. I tested the same in our environment and got the same error which you got when I used the "VIEW DATA" functionality inside the ODI. But not only for 14,4, I got it for each table where I had a Decimal datatype defined with a Scale value. For all Decimal types which only had a precision value it was no problem at all.

Nevertheless using the table inside mappings and creating ETL-Pipelines which transfer the data correctly works fine. So you are not able to get a preview inside the ODI but you can build and run mappings for your piplelines.

I was not able to find the implementation inside the ODI which is used when you call the ViewData Function. I just found and article where Exasol mentions some points inside the jdbc driver with the getBigDecimal-Functionality which seems to be a problem in our case. https://www.exasol.com/support/browse/EXASOL-1922

 

But from the point of view that transfer of data inside mappings is working fine I think it is an implementation failure of the ViewData-Function inside ODI.

For your next question, what du you mean with thirdparty tools here? In our usecase we just have an ODI, an ODI Agent and an Exasol DB. All ETL-Processes are created inside Mappings and the Scenarios are scheduled via the Agent and thats it. And the DB can be accessed via DBeaver, Exaplus, DBVisualizer, etc...

Can you explain what do you mean here?

Best regards

Jens

ThomasE
Contributor

Hi Jens

Thanks for your reply. Yes, meanwhile we figured out that all scale factors lead to the error message. No big deal, as long as the mappings are running well.

Concerning third-party-tools, I had in mind that there are some out-of-the-box solutions with drivers, load modules and so on. Since I've created all from scratch, I'm not sure if I did everything well. So, my questions is: Is there any documentation or an example of how to implement the Exasol access i a correct manner?

Best Regards

Thomas

exa-Serdar
Team Exasol
Team Exasol

Hi @ThomasE ,@Gallus 

as @jens_areto mentioned, the issue could be the data type mapping definition.

Another source of error might be within the RKM.

Please check

  1. if the metadata select for the column definitions is correct and
  2. if the insert statements into the ODI repository receive the right data into the right columns.
    In case you have direct access to the ODI repositories, you can have a look at the respective tables, to ensure the metadata is correct.

exa-MathiasHo
Community Manager
Community Manager

Thank you @exa-Serdar for your guidance!

@ThomasE Did any of these comments help you? If so, please mark one as a solution or let us know how you handled this issue in the end.

PrivateChris
Padawan

Hi @Gallus @ThomasE ,

let me reach out to @exa-Serdar may be he has some insights available. Heard rumors the he might know more.

Christian

ThomasE
Contributor

Hi @PrivateChris 

That would be very appreciated. 😊

Best Regards,

Thomas

jens_areto
SQL-Fighter

Hello Gallus,

configuring the ODI to work with Exasol is really lots of manual work. We at areto did this at the past for some customers and developed special Knowledges Modules for Exasol to be used in the ODI and also the Technology which should be imported.

The view you are accessing is inside the Exasol DB or inside antoher technology defined in the ODI? Because then I think that there is something wrong inside the conversion Matrix you can configure inside the ODI between different technologies.

If it is inside then Exasol I can not answer you question out of the box but maybe you can post a screenshot of the Datastore inside the Mapping you are executing and the Code from the Scenario or Mapping execution where the error appears.

 

If you get the Error during the Reverse Engeneering Process of a table please tell me which kind of Knowledge Module you are using.

Best regards

Jens 

ThomasE
Contributor

Hello Jens

I'm answering in behalf of Gallus, we work in the same company at the same problem. He's the Exasol-DB guy and I'm the ODI guy.

Let me first explain what we did so far.

First of all, we copied the JDBC drivers from the Exasol website into the oracledi/userlib folder.

In the ODI topology tab, we created a new technology called “Exasol” and added a physical server as well as a physical schema. The connection to the database was successful. Then, we defined the datatypes of Exasol in the logical architecture and added the appropriate Oracle datatypes for the casting.

In the next step, we created a model in the ODI repository. Reverse engineering was successful, all available views from the Exasol DB are visible. Furthermore, with “right mouse click – view data” we can see the content of most of the datastores in the model, but not from such containing DECIMAL(14,4) attributes. We found, that changing 14,4 to 11,0 works, but this is certainly no solution.

At this point, we have no mappings and therefore no code to show. The log on the Exasol db shows a simple “select * from <viewname>.

From my perspective, there is something wrong with the datatype mapping in the logical architecture. It would be good if Exasol, or someone else, could provide us with more information about that.

Best Regards

Thomas