Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How To: Create an Alteryx In-DB Connection File

MargaritaW
Alteryx Alumni (Retired)
Created

How To: Create an Alteryx In-DB Connection File

As users collaborate, the sharing of workflows that connect to databases can be helpful. The In-DB Connection File type allows for this. A database connection is saved as an .indbc file so it can be packaged with a workflow.

Prerequisites

  • Alteryx Designer

Procedure

1. In Designer, navigate to Options > Advanced Options > Alias Manager > In-DB Connections

2. Select the Data Source from the dropdown (1). For our example we will select Oracle, then pick File for the connection type (2), Click the elipses (...) button to navigate to a folder where the .indbc file will be stored.

Please note that the first time you set up a connection file, you will need to name the file and clickOpento create the file (4) then provide a name for this file (3).

Then proceed to set up your connection as you would normally do by selecting the appropriate driver and setting up the connection string for Read & Write.

3. Now that your connection file is created, the next step is to connect to your database using this file. Bring a Connect In-DB Tool onto your canvas, click on the dropdown for the Connection name and select Open File Connection andbrowse to your file

4. You can then select a Table, use the Visual Query builder, or type in SQL directly in theChoose Table or Specific Query window

5. (Optional) If you would like to share this workflow, package your workflow in Designer by going to Options > Export workflow. Notice that the .indbc file is being packaged with the workflow.

N5a.png

At the receiving end, the database drivers/client, data sources for that database should be installed and set up. The .indbc file looks like below, with the password encrypted.

N5.png

Note: Workflow and all images created in this article are from Alteryx version 2019.2

Comments
mbane1
6 - Meteoroid

Hi,

I am unable to see the Alias repository creating option in Alteryx 11.

 

Alteryx alian repository.png

 

Can you anyone help me out?

 

Thanks,

Manidipa

KJennings
9 - Comet

@mbane1:

 

In Designer, navigate to Options > Advanced Options > Manage In-DB Connections.
The rest of the screens appear to follow in order once in the In-DB Connections Manager.

 

Regards

CraigAustin
6 - Meteoroid

What is the "valid connection string"   I can't find instructions for this.   How do I tell it what DB to go to?

And this seems to use OCI, which is 32 bit, right?  But other documentation says IN DB uses only 64 bit drivers.

wale_ilori
9 - Comet

Never mind. I figured it out. You have to give the connection a name and determine where it is going to be saved then go on and create the connection and click ok.

 

I still think there is a gap in the instructions for this. while we can make in-DB connections via the Manage In-DB connections tab, I do not see any instructions or guidelines on how to create the .INDBC file.

 

Can anyone address this please? Thanks

connelbell
5 - Atom

Has there been any updated on this? How do you create the .INDBC file in 11.0?

 

I still think there is a gap in the instructions for this. while we can make in-DB connections via the Manage In-DB connections tab, I do not see any instructions or guidelines on how to create the.INDBC file.

 

Can anyone address this please? Thanks

MargaritaW
Alteryx Alumni (Retired)

Hello @connelbell

 

This article will soon be refreshed to show the options for the latest released version.

 

Per @KJennings helpful comment, starting with version 11 you can get to manage In-DB connections right after Advance Options, navigate this way : 

Options > Advanced Options > Manage In-DB Connections

 

As for the creation of the file INDBC file. The below will show when selecting manage in-db connections

(1) select the data source, for my example I picked Oracle

(2) There are three options for Connection Type (File, User and System), select File

(3) Once File is selected, the Connection file box will show, click on the three dots... here is where you navigate to the place where you want to save your INDBC file, give your file a name

indbfile.jpg

 

When you click the three dots, a browse window will open, give your file a name and click on Open (bottom right hand side)

 

 

indb2.jpg

 

Then fill out the rest of the connection information for Read and Write. Click OK and the INDBC file will be created.

 

indb3.jpg

 

 

connelbell
5 - Atom

Very helpful! Thank you.

PeterPetersen
8 - Asteroid
What do I do if I only want read access? When I am at the "Manage In-DB Connections" window in Alteryx Designer 11.x I can choose between a read and a write tab. I only need to use the read tab but when I try leave the connection string in the write tab empty I get an error because the write tab is blank. What can I do?
wale_ilori
9 - Comet

@PeterPetersen I typically just select the same read and write option since you cant leave it empty.

 

As per being able to write to the db, I think that's set at the database level. At least with the databases I've worked with, whether alteryx was set to read or write did not matter because I know at the database, I cannot write anything.

Rawan
7 - Meteor

Hi, everyone, 

 

I already created a workflow that has few In-DB tools, when I first did that I did not go through the create .INDBC file, and now i want to export the workflow to share but it errors out at their end because of the connection. what are my options here? I am not going to recreate the workflow since it is a huge one, and when i export the workflow i don't see any .INDBC files packaged, I only see a .tde (tableau extract) packaged, which is my output file from the workflow.

 

your help and suggestions are highly appreciated..

 

Thanks

Rawan

bor2b00
6 - Meteoroid

Consider the following idea:

 

Step 1: Start a new flow with just an In-DB connection and configure it with a file connection connected to a Browse In-DB tool.

Step 2: Verify that the file connection works by clicking in the Query builder at the bottom of the Connect In-DB Configuration window to the left of the flow window

Step 3: Change your original flow to use the connect file you just created via the Connection Name drop down  and verify it works via the Query Builder window in the Connection Configuration window

Step 4: Publish your original flow with the file connection - you should see that the file is now included when you click on Workflow Options -> Manage workflow Assets link in the publish form

 

Hope this helps!

davidhenington
10 - Fireball

@MargaritaW When will this be updated? 

MargaritaW
Alteryx Alumni (Retired)

Hi @davidhenington ! Happy Monday !!! Pretty soon, within the next week. Stay tuned. Thank you !!!

AnthonyZuñiga05
5 - Atom

@MargaritaW  Thanks, very useful article.
One question, Does it take too long for your workflows to run with the Connect In-DB tool?

My connection usually takes around 30 min or more.

Example.png

wale_ilori
9 - Comet
I think it depends on what’s happening. We had some workflows that ran rather long with the normal input tools so when we switched to in-db ran 3-4 times faster. We did change the workflow a bit to do as much transformation within the in-dB tool set before streaming out to use the tools not available within in-db so you may need to consider that. We also restricted the fields being used to specific fields to so had to do away with the Select * as well.

Hope these points help.

Get Outlook for iOS
AnthonyZuñiga05
5 - Atom

Hi @wale_ilori ,

 

Thanks for your reply.

 

In the picture I shared last Tuesday, I was actually trying to run a query that I normally use on Impala however, this happens for any query I would like to insert into the canvas to automate several processes. Now, the queries that I use have several joins, does the number of tables within a query impact the Alteryx speed?

 

Thanks again.

 

Anthony Z.

wale_ilori
9 - Comet

@AnthonyZuñiga05 Like any database system, it depends on the table and the type of joins. As you know you can write efficient and inefficient joins so you'll have to factor this in

 

Something else is that I stumbled on was that hard drive space on your computer also affects performance. I had my drive filling up with temp files from several runs so you should check that out as well. Alteryx creates .bak files and you could have other files from other progams filling up your drive space too.

TravisCraven
6 - Meteoroid

I think you did a really good job adding screen shots for the steps. The only thing I would add is the impact for Server users. If there is a recommended way for managing the connection files, I would be interested. 

 

As it stands right now it is a bit tricky because technically once you publish the other users don't need to worry about the connection file. They can download, work on the workflow and even run on their local machine using the credentials of the published connection. The problem is if they save their work locally to avoid losing effort due to a system crash, that severs that gallery connection. If they have db credentials they could re-create the file so that they can publish but otherwise they would need to re-download the workflow. 

 

Again this could be changing or perhaps there are other solutions I am not aware of but this is what we were instructed through Alteryx Support.

 

 

Thanks

 

 

Travis

Robert_Blackey
7 - Meteor

Hi @MargaritaW 

 

Could this be expanded to include how to use the connection files with scheduled jobs on a separate server?

 

Thanks,
Bob

 

mopurv
6 - Meteoroid

Hi Experts,

 

The requirement is to input the .csv file and later join with the data flowing from Amazon redshift.

 

so, i have created a file connection following the instructions, but i do have a error when i run the workflow. Am i missing any step, please advise.

 

mopurv_0-1587743015076.png

 

Error: Data Stream In (5): Error creating table "AYX20042411a8d4679886d27a406ea0ee2d240c94": [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 3F000] ERROR: no schema has been selected to create in

CREATE TABLE "AYX20042411a8d4679886d27a406ea0ee2d240c94"

davidhenington
10 - Fireball

Looks like your default tablespace schema needs to be identified. Your IT DBA should be able to assist you with this. 

Is there any way to change the content of the INDBC file? I'm hoping to get a password from vault and update it in INDBC file without any manual touch points using Alteryx. Any lead would be appreciated

yuriy
8 - Asteroid

I am not able to see the images referenced to the external site (e.g. https://i.imgur.com/tzlW3LK.png). Can those images be uploaded to the community portal directly? Thank you

lepome
Alteryx Alumni (Retired)

@yuriy 
Try a different browser.  I can see all of them in Chrome.

JMart2135
8 - Asteroid

@MargaritaW Great article, I was able to get my Oracle connection set up and working easily. I wanted to ask what the changes in the connection settings might be for a SQL Server Connection? I dont get the OCR option for SQL Server (only thing other than the SQL Server ODBC is OLE DB). I tried as is but am getting a SQLDriverConnect error and data source name not found and no default driver specified.

 

Thank you for the help!

Mahadeva
8 - Asteroid

@ShonnyKurianMathew I have the same requirements too. Appreciate if you can help me if you have already cracked it. Thanks in advance.

Mahadeva
8 - Asteroid

Hi All, can the connection file approach be used to connect to normal input/output (not the in-DB tools) tools to achieve the dsn less/odbc less connection. ?

StellaBon
11 - Bolide

Hello!

I have a question about .indbc files.

I updated my database password, something I have to do regularly. I replaced my password in the connection string and everywhere but my .indbc connections are all broken. I suspect it is because my former password is hashed as in the final image of this article (the yellow highlighted portion). 

Is there a way to update the .indbc file with the new password, without deleting it and establishing a new .indbc file each time my password is updated?

 

Thanks for any help you can offer!

 

S-Bon