Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

Hello to all

I'm not able to optimize my SQL statements in QlikView becouse I'm not able to use feature from DB2 database which is called "GLOBAL TEMPORARY TABLE". Becouse QlikView works only in read-only I cannot send any insert or update statement using SQL via ODBC connection. In program WInSQL (maybe some of you know this program) I'm able to send any SQL statement I want so it's not limitation from ODBC driver.

In fact I'm able to use insert/update when I use MS SQL server os MySQL so this "read-only" isn't so true in this case.

When I run my SQL statement in QV (which declares temporary table in DB2 database) i get such error:

SQL Error:[IBM][System i Access ODBC Driver]Statement violates access rule: Connection is set to read only.

SQL Scriptline:

SQL State:37000

SQL

DECLARE GLOBAL TEMPORARY TABLE TEMP

  (MMITNO      CHAR(15)     NOT NULL)

Anybody can help ?

1 Solution

Accepted Solutions
fortuinw
Contributor
Contributor

Hi Pawel

Had a similar issue in Oracle and did the following in the CONNECT statement. Check to see if this works for a DB2 connection.

ODBC CONNECT TO [XXXXXXXX] (XUserId is XXXXXXXX, XPassword is XXXXXXXX, Mode is write);

Regards

Wayne

View solution in original post

10 Replies
Miguel_Angel_Baeyens

Hi Pawel,

You can declare in your ODBC connection to set it to read-write as well. Go to the Control Panel, Administrative Tools, Data Sources, and set there the corresponding setting.

In addition, go to the Script Editor window, select from the bottom the tab Settings and ensure that "Open Databases in Read and Write mode" is enabled.

But apart from that, the user specified in the ODBC must have permissions in the database to create temporary tables.

Hope that helps.

Miguel

Not applicable
Author

Hi Miguel

The thing is that my ODBC conncection is set for read and write. I use this connection in another program and it works fine but in QV I'm not able to send any write statement at all.

Miguel_Angel_Baeyens

Hi,

Did you set the Script Editor as mentioned? Are you using the same user in your ODBC and the other computer? I only see here two issues: first is the ODBC configuration, second is QlikView configuration, but QlikView should allow you to write if you specify so.

Miguel

Gysbert_Wassenaar

Is there an key AllowProcCalls in the registry for that datasource with the value 1? Should be under

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ or HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\

If not try adding it.


talk is cheap, supply exceeds demand
Not applicable
Author

Hi

I get the same error even when I've checked option Open Database in Read and Write mode.

Maybe QlikView has problems to send write statements using iSeries ODBC driver (official from IBM for DB2).

Not applicable
Author

Yes I have it and it's set to "0". When I've changed it to "1" nothing has changed when I tried to run QV script

Not applicable
Author

I'm not sure but I think there is a problem when QV sends SQL statement with Declare clause at beginning. Maybe this causes a problem between QV and ODBC driver. I use "System i Access for Windows ODBC data source" driver. It works fine when I use it under WinSQL but then when I use the same ODBC connection I get error. I don't know who should I ask about it. This is very important for me becouse I need to optimize my SQL statements. In couple of my QV apps. I load data from many tables and limit their data using data from another table but now I need to SELECT this table many times in my script. With Temporary Tables I would be able to load it only once and use in during whole script execution.

fortuinw
Contributor
Contributor

Hi Pawel

Had a similar issue in Oracle and did the following in the CONNECT statement. Check to see if this works for a DB2 connection.

ODBC CONNECT TO [XXXXXXXX] (XUserId is XXXXXXXX, XPassword is XXXXXXXX, Mode is write);

Regards

Wayne

Not applicable
Author

Hi Wayne

That's it !!!!

Thanks man

You have bunch of beers from me.