Question re. configuration of dialect for DB2 driver using ontop-cli-4.2.1

[Duncan Nimmo]

Hi,

I'm investigating using ontop-cli-4.2.1 as an R2RML mapper for a DB2 database.  

At the moment the materialize function is failing with a SQLException caused by the query extension being generated for a different (default?) SQL dialect (casting the subject column to an unsized VARCHAR).

I've had a quick check of the code, the VARCHAR mapping looks correct in class DB2SQLDialectAdpater so I am wondering if there is something that I need to configure.

Could someone tell me how I can fix the dialect issue please?

Many thanks,

Details (unable to files load to Google docs from behind company firewall)

Properties file:

jdbc.url=jdbc:db2://hostsys1.mobi.ch:5051/DB2Z:currentSchema=DB2ZVIEW;
jdbc.user=xxx
jdbc.password=xxx

jdbc.driver=com.ibm.db2.jcc.DB2Driver
ontop.queryLogging=True
ontop.testMode=True

Mapping file excerpt:

PREFIX rr: <http://www.w3.org/ns/r2rml#>
PREFIX ex: <http://example.org/>
PREFIX iom: <https://schema.mobi.ch/iom/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

<#Adresse>
    rr:logicalTable [ rr:sqlQuery """
 SELECT DISTINCT
    ADRESSNUMMER ,
    CAST(PLZ4 AS VARCHAR(10)) AS PLZ4,
    HAUSNUMMER
FROM DB2ZVIEW.RDFPARTNER4POC
WHERE ADRESSNUMMER = 100
LIMIT 1
    """ ];
  ... 

Generated query, invalid generated statement in red

08:34:10.399 |-DEBUG in i.u.i.o.a.r.impl.QuestQueryProcessor - Resulting native query:
ans1(s,g)
CONSTRUCT [s, g] [s/RDF(http://example.com/Partner/Adresse/{}(v0),IRI), g/NULL]
   NATIVE [v0]
SELECT DISTINCT CAST(V1.ADRESSNUMMER AS VARCHAR) AS "v0"
FROM (SELECT DISTINCT
        ADRESSNUMMER ,
        CAST(PLZ4 AS VARCHAR(10)) AS PLZ4,
        HAUSNUMMER
FROM DB2ZVIEW.RDFPARTNER4POC
WHERE ADRESSNUMMER = 100
LIMIT 1) V1
WHERE V1.ADRESSNUMMER IS NOT NULL

Stack trace:

08:34:10.402 |-DEBUG in i.u.i.o.a.c.impl.QuestStatement - Executing the query and get the result...
08:34:10.518 |-ERROR in i.u.i.o.a.c.impl.QuestStatement - com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-604, SQLSTATE=42611, SQLERRMC=null, DRIVER=4.26.14
it.unibz.inf.ontop.exception.OntopQueryEvaluationException: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-604, SQLSTATE=42611, SQLERRMC=null, DRIVER=4.26.14
        at it.unibz.inf.ontop.answering.connection.impl.SQLQuestStatement.executeSelectQuery(SQLQuestStatement.java:202)
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement.executeSelectQuery(QuestStatement.java:122)
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement.executeSelectQuery(QuestStatement.java:114)
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement$QueryExecutionThread.run(QuestStatement.java:97)
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-604, SQLSTATE=42611, SQLERRMC=null, DRIVER=4.26.14
        at com.ibm.db2.jcc.am.b7.a(b7.java:810)

[Benjamin Cogrel]

Hi Ducan,

Thank you for reporting the issue.

Which version of DB2 are you using? On DB2 v11.5.4.0, I didn't manage to reproduce your issue with the unsized VARCHAR.

Anyway, probably a more robust way to do the cast would be using the VARCHAR function (e.g. VARCHAR(V1.ADRESSNUMMER)) [1]

I pushed a fix for it: https://github.com/ontop/ontop/commit/be4a009a84fd8f3140e2e46311f399956a2b6818

Looking at your mapping, I noticed you wrote a not-so-simple query with a LIMIT and DISTINCT. The presence of these operators causes the SQL query to be treated as a "black-box view" by Ontop, in the sense that it does not parse it but treat it as string (which is at the end injected in the final SQL query).

By default, Ontop does not fetch data types from black-box views. However, by turning the property ontop.allowRetrievingBlackBoxViewMetadataFromDB to true, data types will be fetched and, as a side-effect, the cast is likely to be post-processed, so the issue should disappear with 4.2.1.

As a best practice, I suggest you not use DISTINCT and LIMIT in the mapping, so as to avoid black-box views. Ontop automatically takes care of eliminating duplicates.

[1] https://www.ibm.com/docs/en/db2-for-zos/11?topic=functions-varchar

Best,

Benjamin

--
Please follow our guidelines on how to report a bug https://ontop-vkg.org/community/contributing/bug-report
---
You received this message because you are subscribed to the Google Groups "ontop4obda" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ontop4obda+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ontop4obda/eafe19b1-7fb8-4bae-84bb-90a4aefd3971n%40googlegroups.com.