ODX Storage: SQL Server DB on-prem
DSA/MDW: SQL Server DB on-prem
We are successfully able to connect to a DB2 system and synchronize, we can also successfully use the Data Source Explorer to query tables. Every table we try to extract fails with the following error (schema/table/column names obfuscated):
Executing table [schema].[table]:
failed with error:
edo220W.gm: The column [column] does not exist
at edo220W.ai.GetOrdinal(String )
at System.Data.CData.DB2.DB2DataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerCommon(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at DataStorageEngine.SQL.SQLStorageEngine.TransferWithBulkCopy(SqlCommand destinationCommand, String destinationTableName, IEnumerable`1 columnModels, IDbCommand transferCommand, DataTable transferTable)
at DataStorageEngine.SQL.SQLStorageEngine.ExecuteTransfer(IDataSourceEngine dataSourceEngine, SQLTransfer transfer, SQLTransferSettings transferSettings, SQLExecutionMethod executionMethod, SqlCommand destinationCommand, Boolean isEmptyDataTransfer)
at DataStorageEngine.SQL.SQLStorageEngine.<>c__DisplayClass52_0.<TransferDataToTable>b__1(SqlCommand destinationCommand)
at DataStorageEngine.SQL.SQLStorageExtensions.ExecuteCommand(SqlConnection connection, Int32 commandTimeout, Action`1 action)
at DataStorageEngine.SQL.SQLStorageEngine.<>c__DisplayClass50_6.<TransferData>b__8(SQLExecutionMethod executionMethod)
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:
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.
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.
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
0 AS pkCodEmpresa
, AVCAC AS pkAVCAC
, AVCAC AS AVCAC
, AVDES AS AVDES
, AVBAJ AS AVBAJ
It works very well
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:
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.
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.
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.
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.
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 [DW_SourceCode] part, if we need to have a change to the CData provider.
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.
@Thomas Lind ,
I sent you the logging by e-mail - looks like the query sent to DB2 does complete but it fails somewhere after/
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"