Prev: Location Intelligence demo
Next: Recursive SQL PL ?
From: Norbert Munkel on 30 Mar 2010 14:47
Hi,

I am experiencing problems running a query on a warehouse-server.
DB2 8.1 FP 12 on AIX 64 bit
(Upgrade to 9x cannot be done in the near future).

Situation:
One complex query with 2 different arguments:

1. where year='2008' (or 2006, 2007..)
- runs fine everywhere

2. where year='2009'
- runs locally on the DB2-Server and returns the expected results.
- hangs on remote Windows-Client (32bit)

"Hangs" means: The query never finishes. Same behaviour with command
editor and commandline-client (so it can´t be a jdbc-issue).
Application snapshot shows that the query runs and does the expected
reads and sorts. During fetch it stops after 138 records (about 2800
expected). The connection state changes from "executing" to "UOW
waiting". Command-Center says the query is still running. The CLI just
hangs.
No lock-waits.

- Query run by Microstrategy DWH-Engine:
runs about 10 Minutes (the expected time) but throws following
ODBC-error:
--snip--
Odbc success with info. Odbc operation attempted: SQLFetch. [01004:0: on
HSTMT] [MicroStrategy][ODBC DB2 Wire Protocol driver]String data, right
truncated. Error in column 15. For more information, please consult DB2
document for error code SQL00. Connection String:
DSN=XXDWH;UID=sercon;IPADDRESS=de-xx-dwh1;TCPPORT=52000;DATABASE=DWH00000;.

SQL Statement:

(...)
--snap--

Well, SQL00 doesn´t help much here.
The mentioned column 15 is a generated columnd (char(4)) which can only
contain one of the strings "JA" or "NEIN".

I tried to run the query through a view with the same result.


The DDL would be a bit to complex to post. Here is the describe output
of the statement:
sqltype sqllen sqlname.data
sqlname.length
-------------------- ------ ------------------------------
--------------
452 CHARACTER 2 BUNDESLAND_SCHLUESSEL
21
449 VARCHAR 50 BUNDESLAND_BEZ
14
453 CHARACTER 3 REG_BEZIRK_SCHLUESSEL
21
449 VARCHAR 50 REG_BEZIRK_BEZ
14
496 INTEGER 4 FINU
4
496 INTEGER 4 KUNU
4
449 VARCHAR 35 NAME_01
7
449 VARCHAR 35 ORT
3
449 VARCHAR 35 STRASSE
7
453 CHARACTER 7 PLZ0
4
453 CHARACTER 3 PLZ_ERWEITERUNG
15
453 CHARACTER 4 LAND0
5
452 CHARACTER 5 KREIS_SCHLUESSEL
16
449 VARCHAR 50 KREIS_BEZ
9
448 VARCHAR 4 CUSTCOL_65
10
481 DOUBLE 8 WJXBFS1
7
485 DECIMAL 31, 3 WJXBFS2
7


The rowcount of the output for 2009 does not differ from the rowcount
for 2008 so this can´t be an issue..

db2diag.log doesn´t show any errors.

Any ideas?

Thank you!

Regards,

Norbert
From: Hardy on 1 Apr 2010 10:29
On Mar 30, 1:47 pm, Norbert Munkel <linger...(a)munkel.net> wrote:
> Hi,
>
> I am experiencing problems running a query on a warehouse-server.
> DB2 8.1 FP 12 on AIX 64 bit
> (Upgrade to 9x cannot be done in the near future).
>
> Situation:
> One complex query with 2 different arguments:
>
> 1. where year='2008'    (or 2006, 2007..)
> - runs fine everywhere
>
> 2. where year='2009'
> - runs locally on the DB2-Server and returns the expected results.
> - hangs on remote Windows-Client (32bit)
>
>    "Hangs" means: The query never finishes. Same behaviour with command
>   editor and commandline-client (so it can´t be a jdbc-issue).
> Application snapshot shows that the query runs and does the expected
> reads and sorts. During fetch it stops after 138 records (about 2800
> expected). The connection state changes from "executing" to "UOW
> waiting". Command-Center says the query is still running. The CLI just
> hangs.
> No lock-waits.
>
> - Query run by Microstrategy DWH-Engine:
>    runs about 10 Minutes (the expected time) but throws following
>    ODBC-error:
> --snip--
> Odbc success with info. Odbc operation attempted: SQLFetch. [01004:0: on
> HSTMT] [MicroStrategy][ODBC DB2 Wire Protocol driver]String data, right
> truncated. Error in column 15. For more information, please consult DB2
> document for error code SQL00. Connection String:
> DSN=XXDWH;UID=sercon;IPADDRESS=de-xx-dwh1;TCPPORT=52000;DATABASE=DWH00000;.
>
> SQL Statement:
>
> (...)
> --snap--
>
> Well, SQL00 doesn´t help much here.
> The mentioned column 15 is a generated columnd (char(4)) which can only
> contain one of the strings "JA" or "NEIN".
>
> I tried to run the query through a view with the same result.
>
> The DDL would be a bit to complex to post. Here is the describe output
> of the statement:
>   sqltype               sqllen  sqlname.data
> sqlname.length
>   --------------------  ------  ------------------------------
> --------------
>   452   CHARACTER            2  BUNDESLAND_SCHLUESSEL
>      21
>   449   VARCHAR             50  BUNDESLAND_BEZ
>      14
>   453   CHARACTER            3  REG_BEZIRK_SCHLUESSEL
>      21
>   449   VARCHAR             50  REG_BEZIRK_BEZ
>      14
>   496   INTEGER              4  FINU
>       4
>   496   INTEGER              4  KUNU
>       4
>   449   VARCHAR             35  NAME_01
>       7
>   449   VARCHAR             35  ORT
>       3
>   449   VARCHAR             35  STRASSE
>       7
>   453   CHARACTER            7  PLZ0
>       4
>   453   CHARACTER            3  PLZ_ERWEITERUNG
>      15
>   453   CHARACTER            4  LAND0
>       5
>   452   CHARACTER            5  KREIS_SCHLUESSEL
>      16
>   449   VARCHAR             50  KREIS_BEZ
>       9
>   448   VARCHAR              4  CUSTCOL_65
>      10
>   481   DOUBLE               8  WJXBFS1
>       7
>   485   DECIMAL          31, 3  WJXBFS2
>       7
>
> The rowcount of the output for 2009 does not differ from the rowcount
> for 2008 so this can´t be an issue..
>
> db2diag.log doesn´t show any errors.
>
> Any ideas?
>
> Thank you!
>
> Regards,
>
> Norbert

what's the version of your db2 client?
From: Norbert Munkel on 2 Apr 2010 06:09
Hardy schrieb:

> what's the version of your db2 client?

8.1 FP 12
But the microstrategy-app brings its own ODBC-Driver (does not use local
db2 client installation).

I know I can run the query using a 9.7 client but this does not help.

cu,

Norbert
 | 
Pages: 1
Prev: Location Intelligence demo
Next: Recursive SQL PL ?