I have an application which, among other things, needs to modify a DB2
schema via JDBC. Up until now, I have been getting along very well,
but today I needed to drop a column. That in itself was no problem,
but having dropped the column, I discovered I needed to reorg its
parent table before I could perform any further operations on it. I
quickly discovered that I could not do REORG TABLE via JDBC. Is this
really true, or is there just some trick I have missed? If it is true,
is there some equivalent mechanism I can use via JDBC (i.e. without
using any proprietary interfaces)?

Hm - I think I am making an unproven assertion when I say that the
column drop is causing the reorg requirement - I am also doing a few
other operations on that table. So please don't get hung up on WHY I
need a reorg, just focus on how I can do the equivalent via JDBC.

OK - I isolated the DDL down to the equivalent of this:

ALTER TABLE t DROP COLUMN d;
UPDATE t SET c = 42;

And it fails requiring a reorg. This works, but I can't do it via
JDBC:

ALTER TABLE t DROP COLUMN d;
REORG TABLE t;
UPDATE t SET c = 42;

--
Peter Headland

Slaps forehead - I still forget dynamic statements! The solution is to
call SYSPROC.ADMIN_CMD ('REORG TABLE t'), which I can do via JDBC.


However, when I do that, I get: "REMOTE OPERATION INVALID FOR
APPLICATION EXECUTION ENVIRONMENT"

What do I do now?

--
Peter Headland

On 2010-05-22 02:14, Peter Headland wrote:
What exactly is the error message (including sqlstate et al)?


/Lennart

In DB2 9.7 you want to take a look at:
ADMIN_REVALIDATE_DB_OBJECTS()
http://publib.boulder.ibm.com/infoce.../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

On May 21, 8:35*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
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

On May 21, 8:47 pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
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

On May 22, 9:15*am, Peter Headland <PHeadl... (AT) excite (DOT) com> wrote:
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

On 2010-05-22 18:00, Peter Headland wrote:
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