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

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.

In Database

Database Connection

In-Database

Accepted answers

User123456789

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:

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).
In Windows, go into your ODBC Data Source Administrator.
Add a new User Data Source.
Select the appropriate DB2 driver for the environment you want to use.
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.
Select the new User Data Source and click the "Configure..." button.
Select the Advanced Settings tab.
Here is where you can add CLI Parameters that will likely fix your problem. My problem related specifically to DBName. So, I selected "DBName".
a) I added the database name that was created for me in step #1. b) Click Ok until ODBC Data Source Administrator closes completely.
Open your workflow in Alteryx.
Locate the Connection Name related to DB2 you are using and click Manage Connection.
Uncheck the Use Data Connection Manager (DCM) option.
Leave the Read tab as is with whatever currently works for connecting and reading tables in DB2.
Click the Write tab.
Under Driver, select the Generic ODBC option.
Under Connection String, select New database connection.
Chose the new Data Source Name you created above for writing to DB2.
Enter your credentials and click OK.
Go to the configuration menu of your Data Stream In tool. Select Create New Table or Overwrite Table (Drop) options.
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".
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.

All comments

mceleavey

Hi @4fit ,

Can you please share your config so we can see what you're trying to do?

4fit

Here is the config for the tool causing the error. The 'Write' portion is using the same connection string.

4fit

Just wanted to bump this up to the top. Today is my drop-dead date for determining if Alteryx is a viable option for this particular process. In order for that to be true, creation of these temp tables is paramount.

I welcome any and all ideas or suggestions.

apathetichell

Not overly familiar with the structure of this IBM database - do you know how the DB.Table syntax is formatted? Can you shared your ODBC 64 configuration options? Also - in the config pane above you are only showing your Read toggle - can you show your Write toggle.

Here are the things I would look at:

1) can I read the data from the source?

2) what info can I glean from how the data is internally formatted via dynamic output in-DB

3) Do I need to specify a table/DB in my ODBC (some ODBC require this).

4) If I do not have to specify a table do I have write access to default?

4fit

The 'Write' portion connection string is identical to 'Read' portion I've included in the previous screenshot. Regarding your other questions:

1) Yes, I read from this data source all the time (dozens a time a day from Alteryx)

2) This example is simply a column named "Test" with 2 values; 123456 and 654321 (I've also tested with text values and receive the same error). These same values can be placed in a temporary table in SAS without issue.

3) Since I am creating a temporary table, the Table Name field is not available. If I specify a table in the connection string, I receive an error stating that I do not have Create privileges, which is true. I can not use CREATE TEMPORARY TABLE in other tools either. I have to use DECLARE TEMPORARY TABLE. I'm betting this is my problem actually.

4) See above.

Interestingly enough, I was able to create a standard output tool with a PreSQL that establishes the temporary table (using DECLARE TEMPORARY TABLE), load that temporary table with the output tool, and query that temporary table with the PostSQL. Of course, querying in the PostSQL doesn't do me any good since I can't get the data out of that.

apathetichell

@4fit- what you are saying in 3) makes me think that you need to specify the default database you are writing to in either your DB connection or you ODBC 64 config. I've seen this before where you cannot write a default table which needs to be specified in the ODBC itself. Is there a temp db you can write to? If you do not have permission to write to any temp tables you will not be able to write in-DB. In-DB cannot magically circumvent your DB's security restrictions.

4fit

Definitely not trying to circumvent any permission restrictions. I guess this entire question could come down to "when using the Data Stream In tool to create a temporary table, is the code behind the scenes stating "CREATE TEMPORARY TABLE" or "DECLARE TEMPORARY TABLE""? If it's CREATE (which I assume it is), then I'm likely hosed.

That said.....

The connection I am making is to a specific database. Where I am using 'DB' is an alias for the exact database I am trying to create the temp table in. The whole connection string is:

odbc:DSN=ACTUAL_DB_NAME_HERE;UID=myuser;PWD=__EncPwd1__;Schema=SESSION;connection=global;

The ultimate desired outcome is something like this (very much simplified):

In SAS, the whole process to create the temp table would look something like this:

CONNECT TO DB AS AliasName (DATABASE=ACTUAL_DB_NAME_HERE USER=myuser USING=mypassword connection=global);
EXECUTE(DECLARE GLOBAL TEMPORARY TABLE tmpExample(Test char(6)) ON COMMIT PRESERVE ROWS) BY AliasName;
INSERT INTO AliasName.tmpExample SELECT Test FROM DistTests;

I should clarify that there are certainly other ways to do this without using the temp table. However, my temp table has the potential to be hundreds of thousands of records and the table that it is joining to is well over 10 million records. So the most efficient way of doing this is definitely temp tables for joining.

apathetichell

@4fitsorry to be a pain - can you share the odbc64 config? I still think it needs a base database/schema to write-to there. and I think you need to have write permission to it.

I'd also recommend trying to output to a new table instead of a temp table with the DB.TABLE format - where you know that it's a DB.TABLE you have write permission to. Let's see if you error out there and what the error is.

4fit

Our IT has created a user group that has permission to create temporary tables, but only using the DECLARE statement, and not the CREATE statement. I definitely cannot create a static table in this database.

As for the ODBC config file, the only thing in the Advanced Settings is a DBALIAS setting under CLI Parameter. I've tried adding a Database setting and a few others, but no change in the workflow results. Still receive the "No table chosen; please select a table from data source" error.

I guess my main frustration is the fact that I can easily do this in other programs (SAS, Crystal Reports, etc) and this is the first time in my 5+ years of using Alteryx (literally develop in it every single day) where I feel like I need to just throw in the towel. Well, this and the fact that its near impossible to efficiently join one table that has a begin date field and end date field with another table that has a single date field (again, multi-million record tables), but that's a topic for another discussion.

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

Here are the steps that worked for me:

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).
In Windows, go into your ODBC Data Source Administrator.
Add a new User Data Source.
Select the appropriate DB2 driver for the environment you want to use.
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.
Select the new User Data Source and click the "Configure..." button.
Select the Advanced Settings tab.
Here is where you can add CLI Parameters that will likely fix your problem. My problem related specifically to DBName. So, I selected "DBName".
a) I added the database name that was created for me in step #1. b) Click Ok until ODBC Data Source Administrator closes completely.
Open your workflow in Alteryx.
Locate the Connection Name related to DB2 you are using and click Manage Connection.
Uncheck the Use Data Connection Manager (DCM) option.
Leave the Read tab as is with whatever currently works for connecting and reading tables in DB2.
Click the Write tab.
Under Driver, select the Generic ODBC option.
Under Connection String, select New database connection.
Chose the new Data Source Name you created above for writing to DB2.
Enter your credentials and click OK.
Go to the configuration menu of your Data Stream In tool. Select Create New Table or Overwrite Table (Drop) options.
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".
Run workflow.

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-ability-to-change-the-field/idi-p/414435

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Generic-In-database-connection-please-stop-inventing-and/idi-p/359763

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703