To homepage To homepage

CData IBM DB2 - extracting results in The column [xxxx] does not exist

rory.smith
Rank
Userlevel 5
Badge +7

TX: 20.10.39

ODX: 20.10.37

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)
   at DataStorageEngine.SQL.SQLStorageEngine.<>c__DisplayClass50_4.<TransferData>b__6()

19 replies

daniel
Rank
Userlevel 4
Badge +5

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

rory.smith
Rank
Userlevel 5
Badge +7

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.

daniel
Rank
Userlevel 4
Badge +5

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?

rory.smith
Rank
Userlevel 5
Badge +7

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.

rvgfox
Rank
Userlevel 3
Badge +1

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

daniel
Rank
Userlevel 4
Badge +5

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.

 

rvgfox
Rank
Userlevel 3
Badge +1

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

 

rory.smith
Rank
Userlevel 5
Badge +7

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:

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

rory.smith
Rank
Userlevel 5
Badge +7

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.

Thomas Lind
Rank
Userlevel 6
Badge +5

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?

rory.smith
Rank
Userlevel 5
Badge +7

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.

Thomas Lind
Rank
Userlevel 6
Badge +5

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.

rory.smith
Rank
Userlevel 5
Badge +7

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.

Thomas Lind
Rank
Userlevel 6
Badge +5

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 [DW_SourceCode] part, if we need to have a change to the CData provider.

rory.smith
Rank
Userlevel 5
Badge +7

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.

rory.smith
Rank
Userlevel 5
Badge +7

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/

Thomas Lind
Rank
Userlevel 6
Badge +5

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"

 

Christian Hauggaard
Rank
Userlevel 6
Badge +5

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]    [META|Schema] Executed  column schema query: [XREGHLD] 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    [1|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?

 

rory.smith
Rank
Userlevel 5
Badge +7

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.

Reply


Terms and ConditionsCookie settings