In-Database Create Temporary Table (DB2) "No table chosen" error

[4fit]

I am trying to create a temporary table in an IBM DB2 (zOS) database and continually receive the error "No table chose; Please select a Table from data source."  I'm able to create the temp table on SQL Server without issue and I can also create the temp table on the IBM DB2 (zOS) database just fine in SAS, Crystal Reports, etc.

Looking for any and all advice/tips.  I am trying to move an existing program from SAS over to Alteryx, but if I can't get this working, it will be a showstopper.  Thanks in advance.

[apathetichell]

Is this the driver you are using? https://www.ryslander.com/how-to-install-and-configure-db2-odbc-driver/ What do you see on Add Database?

Haven't used Declare/IBM SQL (at least not in eons - and i've forgotten anything that I once knew) so maybe someone else has an idea. This is pretty specific to that system. Normally these issues are covered by table creation permissions.

[4fit]

No, there is a very specific driver that we have to use for all of our IBM DB2 connections.  It is simply called IBM DB2 ODBC Driver - DB2COPY1.  

I added the Database in the Advanced Settings earlier, but experienced no difference in the workflow results.  

At this point, I reckon I'm going to scrap Alteryx for this project.  I do appreciate all of your assistance/troubleshooting. 

[DaveSchloat]

I would like to know this too.  I am also working on a IDB2 ODBC Driver - DB2COPY1.  My native query tool has the functionality to:
DECLARE GLOBAL TEMPORARY TABLE SESSION.name

(table stuff)

WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

insert data into session.name

select stuff;

Then query against session.name

But when I paste these queries into an Alteryx input, it simply doesn't function.  When I test connection, I don't even get any error worth noting.

[User123456789 · Accepted solution]

I was getting this same error message and found a workaround for the "Overwrite Table (Drop)" and "Create New Table" options in Data Stream In. My Alteryx version is 2023.1.1.247 if that matters. I had to make a custom ODBC User Data Source just for the writing to tables part. Within that data source, you can add CLI Parameters to fix the issue. In my case, adding "DBName" worked. I have my own database in DB2, so the value had to match the database name that was created just for me. If that doesn't work, I would suggest troubleshooting with your DBA to identify the correct CLI Parameter(s) to change or fix your permissions.

Here are the steps that worked for me:

1. Gain access to create tables, delete tables, drop rows and select tables in DB2 under a specific database name (Remember this database name for later).
2. In Windows, go into your ODBC Data Source Administrator.
3. Add a new User Data Source.
4. Select the appropriate DB2 driver for the environment you want to use.
5. a) Add a name that indicates this is for writing to DB2 only. b) Select a Database alias that represents the environment you want to write tables to. c) Save it.
6. Select the new User Data Source and click the "Configure..." button.
7. Select the Advanced Settings tab.
8. Here is where you can add CLI Parameters that will likely fix your problem. My problem related specifically to DBName. So, I selected "DBName".
9. a) I added the database name that was created for me in step #1. b) Click Ok until ODBC Data Source Administrator closes completely.
10. Open your workflow in Alteryx.
11. Locate the Connection Name related to DB2 you are using and click Manage Connection.
12. Uncheck the Use Data Connection Manager (DCM) option.
13. Leave the Read tab as is with whatever currently works for connecting and reading tables in DB2.
14. Click the Write tab.
15. Under Driver, select the Generic ODBC option.
16. Under Connection String, select New database connection.
17. Chose the new Data Source Name you created above for writing to DB2.
18. Enter your credentials and click OK.
19. Go to the configuration menu of your Data Stream In tool. Select Create New Table or Overwrite Table (Drop) options.
20. In the Table Name field, make sure your naming convention contains your database name, a dot (".") and the new table name you want to create/drop. Here's an example (Don't use the quotes in Alteryx): "MyDBNm.NwTblNm".
21. Run workflow.

If you still get errors, make sure you have access to create tables and drop rows in a separate tool. Also, make sure you have the correct database and table name syntax in the Table Name field for the Data Stream In tool.

Hope it helps or at least gets someone else a little closer to solving their issue.

[simonaubert_bd]

Hello @User123456789  Temporary table can be hard to set however I do think your issues are very specific for DB2, a database that your IT should consider to replace (not because of Alteyx but because it's obsolete).

Correct me if I'm wrong but it seems DB2 is not supported by Alteryx in-db ? Using generic ODBC for in-db may lead to some issues too, since as of today, it's made for db that work like MSQL. There are some ideas to improve generic in-db if yout want to add a vote :

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Generic-In-database-connection-abili...

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Generic-In-database-connection-pleas...

and if you plan to use indb, there are a lot more ideas that may interest you ;)

Best regards,

Simon

[User123456789]

Sorry for the late reply. I didn't get a notification.

I completely agree that we need to upgrade, but that decision is way above my pay grade. To answer your question, I think the issue is either between Alteryx and IBM DB2 or Alteryx and the ODBC data sources that are automatically setup for me. The only issue I have writing to tables is with Alteryx products. The standard tools in Alteryx work without any issue. Most of the In-DB tools also work without issue. Writing and Creating tables have been the biggest issue I have faced. The workaround I mentioned above has worked very well for me since inception. However, I cannot migrate my workflows to our Alteryx Server due to the custom setup I have boxed myself into.

On this web page, it does list the data sources that are supported. As you can see, IBM DB2 isn't listed for In-DB, but I can verify there is at least some support:

Data Sources (alteryx.com)

I have pushed for additional support from Alteryx but have come up empty handed. Also, I did upvote those two links.