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.

[mceleavey]

Hi @4fit ,

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

M.

[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.