SQL30090N Operation invalid for application execution environment
Onno Ceelen
We are running DB2 v8.1.0.36 (see db2level output at bottom of message) and
are experiencing problems in a COM+ transactional environment.
I created a Visual Basic ActiveX COM+ component with a method that calls a
DB2 stored procedure through ADO and the IBM OLEDB Provider for DB2. The
stored procedure returns a recordset to the client.
Then I created a Visual Basic application that calls the method on the COM+
component.
At the moment the stored procedure is called, the following error message is
returned to the client: [DB2/NT] SQL30090N Operation invalid for
application execution environment. Reason code = "". SQLSTATE=25000.
Unfortunately, I did not get a reason code which does not really help me
investigating the problem.
I expect it has something to do with configuring DB2 or the client for use
in a transactional environment. I've never seen this problem in V7.2, where
it worked without a problem.
I could find specific notes on this issue in the documentation or clear
instructions on how to configure DB2 for COM+ (as it should be
'self-configuring').
Any help is greatly appreciated,
Regards,
Onno Ceelen
========= db2level output ==========================================
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08010" with
level identifier "01010106".
Informational tokens are "DB2 v8.1.0.36", "s021023", "", and FixPak "0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB".
========= CLI trace output =========================================
[ Process: 2168, Thread: 2372 ]
[ Date & Time: 05-26-2003 14:54:32.000008 ]
[ Product: QDB2/NT 8.1.0.36 ]
[ Level Identifier: 01010106 ]
[ CLI Driver Version: 08.01.0000 ]
[ Informational Tokens: "DB2 v8.1.0.36","s021023","" ]
SQLAllocHandle( fHandleType=SQL_HANDLE_ENV, hInput=0:0, phOutput=&01c9c714 )
---> Time elapsed - 0 seconds
SQLAllocHandle( phOutput=0:1 )
<--- SQL_SUCCESS Time elapsed - +2.177150E-001 seconds
SQLSetEnvAttr( hEnv=0:1, fAttribute=Unknown value 2473, vParam=2,
cbParam=0 )
---> Time elapsed - +3.290800E-002 seconds
SQLSetEnvAttr( )
<--- SQL_SUCCESS Time elapsed - +2.169100E-001 seconds
SQLAllocHandle( fHandleType=SQL_HANDLE_DBC, hInput=0:1, phOutput=&0220c13c )
---> Time elapsed - +3.140400E-002 seconds
SQLAllocHandle( phOutput=0:1 )
<--- SQL_SUCCESS Time elapsed - +2.187390E-001 seconds
SQLAllocHandle( fHandleType=SQL_HANDLE_DBC, hInput=0:1, phOutput=&0222c13c )
---> Time elapsed - +3.368000E-002 seconds
SQLAllocHandle( phOutput=0:2 )
<--- SQL_SUCCESS Time elapsed - +2.163090E-001 seconds
SQLDriverConnectW( hDbc=0:2, hwnd=0:0,
szConnStrIn="UID=mtsuser;PWD=*******;DSN=TCM51", cbConnStrIn=34,
szConnStrOut=NULL, cbConnStrOutMax=0, pcbConnStrOut=NULL,
fDriverCompletion=SQL_DRIVER_NOPROMPT )
---> Time elapsed - +3.187400E-002 seconds
sqlccsend( ulBytes - 255 )
sqlccsend( Handle - 35946560 )
sqlccsend( ) - rc - 0, time elapsed - +5.492300E-002
sqlccrecv( )
sqlccrecv( ulBytes - 111 ) - rc - 0, time elapsed - +3.132600E-002
sqlccsend( ulBytes - 271 )
sqlccsend( Handle - 35946560 )
sqlccsend( ) - rc - 0, time elapsed - +6.034700E-002
sqlccrecv( )
sqlccrecv( ulBytes - 260 ) - rc - 0, time elapsed - +2.573600E-002
( DBMS NAME="DB2/NT", Version="08.01.0000", Fixpack="0x21010106" )
( Application Codepage=1252, Database Codepage=1208, Char Send/Recv
Codepage=1208, Graphic Send/Recv Codepage=1200 )
SQLDriverConnectW( )
<--- SQL_SUCCESS Time elapsed - +9.993880E-001 seconds
( DSN="TCM51" )
( UID="mtsuser" )
( PWD="*******" )
( DBALIAS="TCM51" )
SQLSetConnectAttrW( hDbc=0:2, fOption=SQL_ATTR_ENLIST_IN_DTC,
pvParam=&02740108, iStrLen=0 )
---> Time elapsed - +4.065380E-001 seconds
sqlccsend( ulBytes - 474 )
sqlccsend( Handle - 35946560 )
sqlccsend( ) - rc - 0, time elapsed - +6.387300E-002
sqlccrecv( )
sqlccrecv( ulBytes - 370 ) - rc - 0, time elapsed - +3.270600E-002
SQLSetConnectAttrW( )
<--- SQL_SUCCESS Time elapsed - +2.374844E+000 seconds
SQLAllocHandle( fHandleType=SQL_HANDLE_STMT, hInput=0:2,
phOutput=&0224c1bc )
---> Time elapsed - +3.238600E-002 seconds
SQLAllocHandle( phOutput=2:1 )
<--- SQL_SUCCESS Time elapsed - +2.173540E-001 seconds
SQLGetFunctions( hDbc=0:2, fFunction=SQL_API_SQLDESCRIBEPARAM,
pfExists=&0147f630 )
---> Time elapsed - +3.148100E-002 seconds
SQLGetFunctions( pfExists=1 )
<--- SQL_SUCCESS Time elapsed - +2.188190E-001 seconds
SQLGetStmtAttrW( hStmt=2:1, fOption=SQL_CURSOR_HOLD, pvParam=&0147f470,
cbParamMax=4, pcbParam=NULL )
---> Time elapsed - +3.106000E-002 seconds
SQLGetStmtAttrW( pvParam=0 )
<--- SQL_SUCCESS Time elapsed - +2.812570E-001 seconds
SQLPrepareW( hStmt=2:1, pszSqlStr="{ call EDA_TRUSTEES_GETTRUSTEETOKEN(?, ?,
?) }" -
X"7B002000630061006C006C0020004500440041005F00540052005500530054004500450053
005F00470045005400540052005500530054004500450054004F004B0045004E0028003F002C
0020003F002C0020003F00290020007D00", cbSqlStr=46 )
---> Time elapsed - +3.166400E-002 seconds
( StmtOut="CALL EDA_TRUSTEES_GETTRUSTEETOKEN(? ,? ,? )" )
SQLPrepareW( )
<--- SQL_SUCCESS Time elapsed - +2.808810E-001 seconds
SQLNumParams( hStmt=2:1, pcPar=&0147f47c )
---> Time elapsed - +3.256100E-002 seconds
SQLNumParams( pcPar=3 )
<--- SQL_SUCCESS Time elapsed - +1.866140E-001 seconds
SQLCanConvertOLEDB( hStmt=2:1, wFromType=8, wToType=129,
pfConvert=&0147f460 )
---> Time elapsed - +3.094700E-002 seconds
SQLCanConvertOLEDB( )
<--- SQL_SUCCESS Time elapsed - +2.186270E-001 seconds
SQLCanConvertOLEDB( hStmt=2:1, wFromType=12, wToType=129,
pfConvert=&0147f460 )
---> Time elapsed - +3.105000E-002 seconds
SQLCanConvertOLEDB( )
<--- SQL_SUCCESS Time elapsed - +2.189670E-001 seconds
SQLCanConvertOLEDB( hStmt=2:1, wFromType=3, wToType=3, pfConvert=&0147f460 )
---> Time elapsed - +3.129800E-002 seconds
SQLCanConvertOLEDB( )
<--- SQL_SUCCESS Time elapsed - +2.187050E-001 seconds
SQLCanConvertOLEDB( hStmt=2:1, wFromType=12, wToType=3,
pfConvert=&0147f460 )
---> Time elapsed - +3.126900E-002 seconds
SQLCanConvertOLEDB( )
<--- SQL_SUCCESS Time elapsed - +2.193980E-001 seconds
SQLCanConvertOLEDB( hStmt=2:1, wFromType=8, wToType=129,
pfConvert=&0147f460 )
---> Time elapsed - +3.191700E-002 seconds
SQLCanConvertOLEDB( )
<--- SQL_SUCCESS Time elapsed - +2.174250E-001 seconds
SQLCanConvertOLEDB( hStmt=2:1, wFromType=12, wToType=129,
pfConvert=&0147f460 )
---> Time elapsed - +3.102600E-002 seconds
SQLCanConvertOLEDB( )
<--- SQL_SUCCESS Time elapsed - +2.189850E-001 seconds
SQLSetStmtAttrW( hStmt=2:1, fOption=SQL_ATTR_CURSOR_TYPE, pvParam=NULL,
uiStrLen=0 )
---> Time elapsed - +3.139200E-002 seconds
SQLSetStmtAttrW( )
<--- SQL_SUCCESS Time elapsed - +2.185160E-001 seconds
SQLSetStmtAttrW( hStmt=2:1, fOption=SQL_ATTR_MAX_ROWS, pvParam=NULL,
uiStrLen=0 )
---> Time elapsed - +3.132000E-002 seconds
SQLSetStmtAttrW( )
<--- SQL_SUCCESS Time elapsed - +2.192900E-001 seconds
SQLSetStmtAttrW( hStmt=2:1, fOption=SQL_ATTR_QUERY_TIMEOUT,
pvParam=&0000001e, uiStrLen=0 )
---> Time elapsed - +3.071500E-002 seconds
SQLSetStmtAttrW( )
<--- SQL_SUCCESS Time elapsed - +2.200240E-001 seconds
SQLSetStmtAttrW( hStmt=2:1, fOption=SQL_CURSOR_HOLD, pvParam=NULL,
uiStrLen=0 )
---> Time elapsed - +2.978000E-002 seconds
SQLSetStmtAttrW( )
<--- SQL_SUCCESS Time elapsed - +2.189100E-001 seconds
SQLExecuteOLEDB( hStmt=2:1, ulNumBindings=3, rgpBindings=&0224c4c0,
rgParamInfo=&0224c2c0, pRowData=&019f3168 )
---> Time elapsed - +3.129400E-002 seconds
( Row=1, iPar=1, fCType=SQL_C_WCHAR, rgbValue="TRIDION\OnnoC" - -
X"540052004900440049004F004E005C004F006E006E006F004300", pcbValue=26,
piIndicatorPtr=26 )
( Row=1, iPar=2, fCType=SQL_C_LONG, rgbValue=65552, pcbValue=16,
piIndicatorPtr=16 )
( Row=1, iPar=3, fCType=SQL_C_WCHAR, rgbValue="<Groups></Groups>" - -
X"3C00470072006F007500700073003E003C002F00470072006F007500700073003E00",
pcbValue=34, piIndicatorPtr=34 )
sqlccsend( ulBytes - 596 )
sqlccsend( Handle - 35946560 )
sqlccsend( ) - rc - 0, time elapsed - +6.229300E-002
sqlccrecv( )
sqlccrecv( ulBytes - 1303 ) - rc - 0, time elapsed - +3.129200E-002
sqlccsend( ulBytes - 90 )
sqlccsend( Handle - 35946560 )
sqlccsend( ) - rc - 0, time elapsed - +6.253200E-002
sqlccrecv( )
sqlccrecv( ulBytes - 106 ) - rc - 0, time elapsed - +3.146100E-002
SQLExecuteOLEDB( )
<--- SQL_ERROR Time elapsed - +1.312337E+000 seconds
SQLGetDiagFieldW( fHandleType=SQL_HANDLE_STMT, hHandle=2:1, iRecNumber=1,
fDiagIdentifier=SQL_DIAG_NUMBER, pDiagInfo=&0147f260, cbDiagInfoMax=-4,
pcbDiagInfo=NULL )
---> Time elapsed - +3.293100E-002 seconds
SQLGetDiagFieldW( pDiagInfo=1 )
<--- SQL_SUCCESS Time elapsed - +3.426110E-001 seconds
SQLGetDiagFieldW( fHandleType=SQL_HANDLE_STMT, hHandle=2:1, iRecNumber=1,
fDiagIdentifier=SQL_DIAG_NATIVE, pDiagInfo=&0147f050, cbDiagInfoMax=-4,
pcbDiagInfo=NULL )
---> Time elapsed - +3.122600E-002 seconds
SQLGetDiagFieldW( pDiagInfo=-30090 )
<--- SQL_SUCCESS Time elapsed - +3.438520E-001 seconds
SQLGetDiagFieldW( fHandleType=SQL_HANDLE_STMT, hHandle=2:1, iRecNumber=1,
fDiagIdentifier=SQL_DIAG_MESSAGE_TEXT, pDiagInfo=&0147f058,
cbDiagInfoMax=512, pcbDiagInfo=&0147f04c )
---> Time elapsed - +3.079900E-002 seconds
SQLGetDiagFieldW( pDiagInfo="[IBM][CLI Driver][DB2/NT] SQL30090N Operation
invalid for application execution environment. Reason code = "".
SQLSTATE=25000
" -
X"5B00490042004D005D005B0043004C00490020004400720069007600650072005D005B0044
00420032002F004E0054005D002000530051004C00330030003000390030004E00200020004F
007000650072006100740069006F006E00200069006E00760061006C0069006400200066006F
00720020006100700070006C00690063006100740069006F006E002000650078006500630075
00740069006F006E00200065006E007600690072006F006E006D0065006E0074002E00200020
0052006500610073006F006E00200063006F006400650020003D002000220022002E00200020
00530051004C00530054004100540045003D00320035003000300030000D000A00",
pcbDiagInfo=520 )
<--- SQL_SUCCESS Time elapsed - +3.756580E-001 seconds
SQLFreeHandle( fHandleType=SQL_HANDLE_STMT, hHandle=2:1 )
---> Time elapsed - +3.215100E-002 seconds
( Caching Statement Structure=1 )
SQLFreeHandle( )
<--- SQL_SUCCESS Time elapsed - +2.483690E-001 seconds
Onno Ceelen
tables and cursors returned to the client.
In the mean-time, I installed Fixpak 2:
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08012" with
level identifier "02030106".
Informational tokens are "DB2 v8.1.2.169", "s030508", "WR21318", and FixPak
"2". Product is installed at "C:\PROGRA~1\SQLLIB".
The stored procedure I use to test with:
CREATE PROCEDURE proc1()
DYNAMIC
RESULT SETS 0
LANGUAGE SQL
BEGIN
declare var1 integer;
set var1 = 1;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TRUSTEES (
TRUSTEE_ID INT
) WITH REPLACE NOT LOGGED;
BEGIN
DECLARE cur CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT TRUSTEE_ID
FROM SESSION.TRUSTEES;
INSERT INTO SESSION.TRUSTEES (TRUSTEE_ID)
VALUES(1);
INSERT INTO SESSION.TRUSTEES (TRUSTEE_ID)
VALUES(2);
OPEN cur;
END;
END!
Any idea?
"Onno Ceelen" <onn...@excite.com> wrote in message
news:3ed2170d$0$49105$e4fe...@news.xs4all.nl...
Mark Yudkin
Then open a PMR agains the IBM OLEDB driver.
The only thing I don't understand, is why it ever worked on 7.2 - the IBM
OLEDB drive has always been broken.
"Onno Ceelen" <onn...@excite.com> wrote in message
news:3ed46df4$0$49117$e4fe...@news.xs4all.nl...
Onno Ceelen
Thanks for your response. You are right in doubting that it ever worked in
V7.2. Actually, it did not work (so you were right). But, it did not return
an error and gave me the expected results. So, it is good to say that it
seemed to work.
The ODBC driver has other limitations which prevents us from working with
ODBC. Furthermore, it adds an extra layer which is not that ideal for
performance.
A PMR has already been opened for this issue, but still no solution.
What are your experiences with the ODBC driver and/or OLE DB driver for DB2,
if you look at performance, support of LOBs and distributed transactions?
Thanks,
Onno Ceelen
"Mark Yudkin" <myu...@compuserve.com> wrote in message
news:bb4gep$3bs$1...@ngspool-d02.news.aol.com...
James Chin
Have you considered trying a third-party ODBC Driver? OpenLink
Software provides high-performance ODBC Drivers for DB/2 on a variety
of platforms. You can download a free evaluation from
http://www.openlinksw.com.
Best regards,
James
OpenLink Software, Inc.
Universal Data Access Middleware Providers
http://www.openlinksw.com
On Thu, 29 May 2003 21:26:02 +0200, "Onno Ceelen" <onn...@excite.com>
wrote:
Mark Yudkin
work, so that the fact that it may be theoretically faster (if implemented
natively) has always been completely irrelevant. I am not aware of any
restrictons in the ODBC driver that can be circumvented by using the IBM OLE
DB driver, so I'm going to doubt that claim too.
To your specific questions - neither LOBs not distributed transactions are
supported by the IBM OLE DB driver, and since these are documented
restrictions on the OLE DB driver, opening a PMR wil be of no assistance.
Both work just fine using the MS OLE DB over ODBC driver, with the IBM DB2
ODBC driver, although for distributed transactions you are strongly advised
to consult the documentation on the Microsoft and IBM web site regarding the
configuration of the MTS / COM+ environment for distributed transactions.
There are some gotcha's since the IBM XA implementation has some
restrictions.
From the ADO viewpoint, refer to MS KB article Q269735. For IBM info, try
http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7d0db2d056.htm.
"Onno Ceelen" <onn...@excite.com> wrote in message
news:3ed65ecb$0$49117$e4fe...@news.xs4all.nl...
Onno Ceelen
The V8 documentation about the IBM OLE DB provider says that distributed
transactions are supported
(http://www-3.ibm.com/software/data/db2/udb/ad/v8/client/c0006800.htm and
http://www-3.ibm.com/software/data/db2/udb/ad/v8/client.html).
Although I can't find a document like "Writing Applications Using the IBM
OLE DB Provider for DB2" of Version 7.2, LOBs should be supported as well.
The ODBC driver has a limitation on LOB data. It can only retrieve 4k block
data for LOB fields as stored procedure parameters. Since we retrieve data
up to 1Mb, that is not an option (unless this restriction has been lifted in
V8).
Onno
"Mark Yudkin" <myu...@compuserve.com> wrote in message
news:bbcc7n$3hk$1...@ngspool-d02.news.aol.com...
Mark Yudkin
Restrictions for the IBM OLE DB Provider for DB2
Hints and tips
Abstract
Restrictions for the IBM OLE DB Provider for DB2.
Content
The current level of the IBM OLE DB Provider for DB2 is subject to the
following restrictions:
BLOB and CLOB data types are not supported.
The Enumerator component for listing database aliases is not available.
Only a single set of parameters is supported. Multiple parameter sets are
not yet supported.
ADO applications calling stored procedures must have their parameters
created and explicitly bound. Using the Parameters.Refresh method to
automatically generate the parameters is not currently supported.
The WithEvents keyword cannot be used in the declaration of the recordset
for Visual Basic ADO applications using the read-only/ forward-only server
cursor; that is, when CursorLocation = adUseServer.
Auto commit and user-controlled transition scope with the ITransactionLocal
interface is supported. The default scope is auto commit transaction scope.
Nested, Distributed, and Coordinated transactions are not supported.
---
Question
Why do my applications with LOB data types fail when I use the IBM OLE
DB Provider for DB2?
Answer
The current level of the IBM OLE DB Provider for DB2 does not include
LOB data type support. To execute OLE DB applications that use BLOB or CLOB
data types, use the Microsoft OLE DB Provider for ODBC.
There are more such articles, just search the DB2 support.
More important that throwing contradictory IBM web pages at each other is
the simple question "have you gotten either function to work using the
native DB2 OLE DB provider?"
---
OTOH, you claim to have hit a 4K limit with BLOBs in stored procedures using
ODBC. Are you sure it's not a coding error on your part - e.g. in the
SQLBindXXX call? If you are sure, open a PMR on the problem.
- Mark Yudkin
"Onno Ceelen" <onn...@excite.com> wrote in message
news:3edb22be$0$49108$e4fe...@news.xs4all.nl...
Onno Ceelen
Thanks for all your input. I will do some more research on ODBC and it's
limitations. I have also posted the question about the limitations of OLE DB
at IBM support.
In the mean-time, I got a response on the problem I started this thread
with.
If you look at the stored procedure in one of my posts, you see a DECLARE
CURSOR with the WITH HOLD option.
Removing this WITH HOLD option solves the problem and in our case does not
affect functionality.
Regards,
Onno Ceelen
"Mark Yudkin" <myu...@compuserve.com> wrote in message
news:bbhdn9$bch$1...@ngspool-d02.news.aol.com...
>
http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q=distributed+