Prev: CLOB insert with Batch Update
Next: !!!!! SOFTWARE FOR SALE !!!!! � 73058
From: Serge Rielau on 21 May 2010 23:47
In DB2 9.7 you want to take a look at:
ADMIN_REVALIDATE_DB_OBJECTS()
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0053626.html

This proc does "whatever it takes" to make an objects whole again.
The idea is to call it at the end of a series of DDL statements.
You can run it on a schema, an object or even the entire db.

Cheers
Serge


--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

From: Peter Headland on 22 May 2010 12:00
On May 21, 8:35 pm, Lennart Jonsson <erik.lennart.jons...(a)gmail.com>
wrote:
> On 2010-05-22 02:14, Peter Headland wrote:
> > However, when I do that, I get: "REMOTE OPERATION INVALID FOR
> > APPLICATION EXECUTION ENVIRONMENT"
>
> What exactly is the error message (including sqlstate et al)?

SQLState 25000, SQLCode -30090

This db is running on Windows (locally on my development machine), and
so far all I can find in the documentation is zOS-related stuff which
seems totally unrelated to what I am doing. I'm using DB2 Express
version 9.7 (sorry - should have said that up front).

--
Peter Headland
From: Peter Headland on 22 May 2010 12:15
On May 21, 8:47 pm, Serge Rielau <srie...(a)ca.ibm.com> wrote:
> In DB2 9.7 you want to take a look at:
> ADMIN_REVALIDATE_DB_OBJECTS()http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...

Fails with SQLState 5UA03:

[MyCompanyDD][DB2 JDBC Driver][DB2]Error occured with SQLCode -20508
with the following parameters: MY_SCHEMA .MY_TABLE, REORG, -30090,
25000, 2|, null, null, null, null, null, null, null, null, null, null,
null, null, null

I see the same SQLState and SQLCode in the message above that I saw
when I tried ADMIN_CMD.

Maybe this is some kind of privilege issue? I can execute both
ADMIN_CMD and ADMIN_REVALIDATE_DB_OBJECTS from the Command Editor
using exactly the same credentials (connected as the owner of
MY_SCHEMA), but perhaps doing this via JDBC requires some additional
privileges?

The user and schema were set up like this (fred is a Windows user):

GRANT CONNECT, LOAD ON DATABASE TO USER fred;
CREATE schema my_schema AUTHORIZATION fred;

Maybe this is related to the fact that I am using a DataDirect JDBC
driver?

--
Peter Headland
From: Peter Headland on 22 May 2010 14:35
On May 22, 9:15 am, Peter Headland <PHeadl...(a)excite.com> wrote:
> Maybe this is related to the fact that I am using a DataDirect JDBC
> driver?

And indeed further testing reveals that that is indeed the case. When
I hooked up the IBM JDBC driver it works just fine. Excuse me for a
moment ... AAAAAAARRRRRRRGHHH!!!!! .. thank you, I feel better
now. :-)

In the unlikely event anyone knows a magic trick to resolve this, I'd
love to know about it. Meanwhile, I'll go beat up on DataDirect (Not
my choice to use their drivers, BTW - I wanted to use the various
vendors' drivers because I fully expected that I would hit cr*p like
this with a 3rd-party driver).

I'll post back here with the resolution to the DD issue (OK, I am
still optimist enough to expect a resolution). A lot of these things
end up being curable via obscure driver flags, IME.

--
Peter Headland
From: Lennart Jonsson on 22 May 2010 23:28
On 2010-05-22 18:00, Peter Headland wrote:
> On May 21, 8:35 pm, Lennart Jonsson <erik.lennart.jons...(a)gmail.com>
> wrote:
>> On 2010-05-22 02:14, Peter Headland wrote:
>> > However, when I do that, I get: "REMOTE OPERATION INVALID FOR
>> > APPLICATION EXECUTION ENVIRONMENT"
>>
>> What exactly is the error message (including sqlstate et al)?
>
> SQLState 25000, SQLCode -30090
>
> This db is running on Windows (locally on my development machine), and
> so far all I can find in the documentation is zOS-related stuff which
> seems totally unrelated to what I am doing. I'm using DB2 Express
> version 9.7 (sorry - should have said that up front).
>

you appeared to have solved the problem below, so I'll just add a little
trick for future use. You can ask db2 for more details around a specific
sqlcode like: (this example is from 9.5 so there might be other details
for 9.7)

[....]$ db2 "? SQL30090"

SQL30090N Operation invalid for application execution environment.
Reason code = "<reason-code>".

Explanation:

The operation is invalid for the application execution environment. For
example, an operation might be invalid for applications that have
special restrictions on statements or APIs - applications such as those
that operate in an XA Distributed Transaction Processing environment,
such as CICS; those that operate with CONNECT type 2 connection
settings; or those that use federated system functionality to update
multiple heterogeneous data sources. The operation was rejected.

Possible reason codes are:

01 an SQL request that changes data (such as an INSERT or a
CREATE) was issued against a read-only database; or, a stored
procedure was invoked against a read-only database. Read-only
databases include the following types:
* Databases accessed using DRDA when operating in a unit of
work which has connection setting SYNCPOINT TWOPHASE and
executing in a non-XA/DTP environment when either a
Synchpoint Manager is not being used or the remote DRDA
database does not support level 2 DRDA protocol.
* Databases accessed by DRDA in an XA/DTP environment when
either a Synchpoint Manager gateway is unavailable or the
remote DRDA database does not support level 2 DRDA protocol.
* Databases which were not the first database updated when the
SYNCPOINT ONEPHASE connection setting is in effect for the
unit of work.


02 an API such as Precompile, Bind, or Reorganize Table which
causes internal commits was issued in an application which has
the setting CONNECT type 2 or is operating in an XA/DTP
environment.

[...]

User response:

Correct the problem by doing one of the following steps:

For reasons 01, 02, 03, 04, 06, 19, or 29,
remove the statement or API which is not supported.

[...]


/Lennart
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: CLOB insert with Batch Update
Next: !!!!! SOFTWARE FOR SALE !!!!! � 73058