strange query behaviour
Norbert Munkel
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
Hardy
what's the version of your db2 client?
Norbert Munkel
> 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