topic Re: I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only) in QlikView

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

Tue, 02 Oct 2012 10:22:55 GMT

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 ?

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

Miguel_Angel_Baeyens — Tue, 02 Oct 2012 10:40:54 GMT

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

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

Tue, 02 Oct 2012 10:58:36 GMT

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.

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

Miguel_Angel_Baeyens — Tue, 02 Oct 2012 11:19:18 GMT

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

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

Gysbert_Wassenaar — Tue, 02 Oct 2012 11:28:19 GMT

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.

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

Tue, 02 Oct 2012 11:35:00 GMT

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

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

Tue, 02 Oct 2012 11:40:44 GMT

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

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

Fri, 05 Oct 2012 11:26:10 GMT

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.

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

fortuinw — Thu, 13 Jun 2013 15:45:55 GMT

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

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

Tue, 18 Jun 2013 08:44:12 GMT

Hi Wayne

That's it !!!!

Thanks man

You have bunch of beers from me.

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

Fri, 09 Aug 2013 09:15:24 GMT

You are my HERO! I've spend 2 days for it Thank you!