Dear Rory,

I had the same issue with this connector. Actually with almost all DB2 connectors.
Support / R&D doesn't recognise this as an issue, but I think that the issue is in the query that TX makes.
It adds a column for the DW_SourceCode and this is not done in DB2 SQL language.

What I did to fix the issue is find out which DB2 instance you're running. My client was running 11.1.
I went to this IBM site and jumped through all the hoops:
https://www.ibm.com/support/pages/db2-odbc-cli-driver-download-and-installation-information

You need to create a (free) IBM account and then the difficult part is finding the correct client between all the fixes. As this is the software for the DB2 Client, but it will also install the correct DB2 drivers on your system.
Download and install the client on the VM where TX is running. Choose the all ole db connectors and then you need to select the IBM OLE DB provider - DB2COPY1.
 

This is the only solution for me. It didnt work with the Microsoft DB2 driver and not with the CDATA DB2 connector.

Hope this helps. Good luck.

- Daniel

Hi,

thanks for the hints. Yuor suspicion seems plausible, but I think it should be fixed. Just finding the correct downloads on the IBM website is a nightmare.

Hey Rory,

I'm not sure of you instance of the DB2 but if you want I could send you mine?

the client may be incorrect but the drivers / provider might still work?

Can you send a DM here?

I think I have correct client for the specific version of iAccess V7R1M0 with DB2 10.5.7 . I got the ODBC cli only first and that does not contain the same driver you have. I just saw it mention the DB2COPY1 thing in the client installer.

Apparently the client requires either a license file or a connection to some IBM Connect server. It looks like we won't be able to resolve this without either installing the Client Access suite or getting the DB2 install media.

I’m using the connection to my iSeries using a Linked Server from SQL Server without problems.

That's too bad. Hopefully CDATA / TX can make the change so you can use the CDATA connector then.

I've probaly checked this but do you use the ODBC or the OLE DB driver? I'm using the OLE DB.

If you figuer it out, please post the solution here? I'm very curious now.

This is my linked server:

And I’using views to retrieve the data from TX:

ALTER VIEW bi.LOACTIF
AS
    SELECT 
          0 AS     pkCodEmpresa
        , AVCAC AS pkAVCAC
        , AVCAC AS AVCAC
        , AVDES AS AVDES
        , AVBAJ AS AVBAJ
    FROM   AS400.AS400UT.LOGECOMD30.LOACTIF

It works very well

Hi,

the linked server requires the correct OLE drivers to be installed (it is usually slightly slower than direct OLE). Once your DB2 drivers are properly set up, I guess it does not matter much between ODBC / OLEDB / Linked Server. I ended up installing:

• https://www.ibm.com/resources/mrs/assets/DownloadList?source=swg-ia&lang=en_US → https://www.ibm.com/support/pages/ibm-i-access-client-solutions with the AP pack (you need an IBM account). Instructions here: https://www.ibm.com/support/pages/ibm-i-access-acs-quick-start-guide
• You then get a Client Access ODBC Driver that works. You just need to set the Table schema name and View schema name settings. There is an Is_Hidden property on columns that you cannot map, but this does not seem to hinder things much

The above driver does not require a license of any sort and it also installs some other client tools.

Once you deploy this driver and set up an ODBC DNS, you will notice you cannot store a password in the connection. In theorie you could set up a file for the DNS that contains the password in plain text, but this is of course not secure. In TimeXtender 20.10.x when you set up a new ODBC Data Source in the ODX, you can set up the connection and then Export and Import the connection string. This allows you to add ;Password = xxxxx at the end (with xxxxx being your password), which magically makes that property appear in the TX interface. You then need to remap the table schema name and view schema name columns, as these are apparantly non-standard. You will notice that the IsHidden property for columns does not have an equivalent, I am not sure if this actually exists in the IBM DB2 world.

With all that set up, you should be good to go.

Hi @rory.smith 

Regarding the issue you get with the CData DB2 version.

Is it all tables that gets this error, does it stop synchronizing when it hits this and only have the tables that did not get this error or is it not have any tables once it failed?

I assume it is probably a issue due to unexpected prefix or suffix characters.

When you query the tables with the query tool how does the data come back and can you specify the fields to select?

It is all tables - at least I tried a lot, there may be many thousands of tables in this system. The synchronize works fully (across all tables in the library/catalog/schema in DB2. Preview queries work, but these differ from the extraction queries in that they do not include the system fields. The DB2 queries do something like SELECT "columnA” FROM "schema”."table” and perhaps the query TX generates for extraction is malformed, it may be it does something like: SELECT "columnA”, 'DB2’ AS 'DW_Source] FROM "schema”."table” and that cause IBM to misinterpret the query syntax.

I can run queries in the preview and they show results, but whenever I do an extract I get that error.

Hi @rory.smith 
Seems like that could be the issue.

Can you create a logfile with verbosity level 3. Then try to synchronize.
Send it to me on my email if it contains any private info.

It would probably contain the table it attempts to synchronize and we could prove that this is the issue.

Hi Thomas,

just to be clear: synchronize succeeds, extract does not.

I will be at the site coming Tuesday and supply you with logs, though I did not find any obvious clues even in the verbosity 4 logs I made earlier.

Hi Rory

OK, don’t bother then. I would only use it for showing it as being successful on the CData side.

Our other issue is that we have no internal access to DB2, so we can’t replicate the issues ourselves. We need to prove what the issue is, such as if it is the tDW_SourceCode] part, if we need to have a change to the CData provider.

Hi,

OK - I guess we would need to be able to log the query TimeXtender constructs for the extract. Probably that requires a debug version to output the data. I do not have access to DB2 other than through this customer.

Hi @Thomas Lind ,

I sent you the logging by e-mail - looks like the query sent to DB2 does complete but it fails somewhere after/

Hi Rory

It seems like it adds a AS "odx_batch_number" in the log where it fails. When synchronizing it does not add this field.

A simple test would be to add that as a query table and see if it gives an error. I do not see why it would, to me there should be no issues doing this. If it fails, does it also fail if you change it to be entirely uppercase like so 0 AS "ODXBATCHNUMBER"

Hi @rory.smith 

Thanks for the log files you sent us for the Cdata provider. There appears to be the following error in the sync log:

2023-02-14T16:45:53.431+01:00    1    �1|Q-Id]    IMETA|Schema] Executed  column schema query: eXREGHLD] Error: Open query failed.
ERROR:System Error.The SQL CODE is -901, SQL STATE is 58004, SQL ERROR PROC is QSQROUTA, MSG is :MCH36011025.

2023-02-14T16:45:53.431+01:00    1    01|Q-Id]    �SQL |Transc] Open query failed.
ERROR:System Error.The SQL CODE is -901, SQL STATE is 58004, SQL ERROR PROC is QSQROUTA, MSG is :MCH36011025.

Do you see any errors if you view the execution log for the sync task of the Cdata data source? Can you please send screenshots of the execution log for both the sync and transfer task?

Hi @Christian Hauggaard ,

I do not have access to this environment to do further debugging - using the IBM driver eventually worked though getting the “right” one is a rather painful process. The CData side would be nice to have working but I would need to find a DB2 I can touch to investigate further.