Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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
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
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.
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
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.
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).
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
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.
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
Hi Wayne
That's it !!!!
Thanks man
You have bunch of beers from me.