Prev: SQL0805N affected by DB/DBM CFG ?
Next: Tablespace rollforward condition
From: LorneSunley on 31 Jan 2007 09:09
Knut Stolze wrote:

> LorneSunley wrote:
>
>> LorneSunley wrote:
>>
>>> Knut Stolze wrote:
>>>
>>>> LorneSunley wrote:
>>>>
>>>>> I am trying to access an 8.2 database through openoffice.org version
>>>>> 2.0 or 2.1. I am using the type 4 JDBC driver. Tables work OK, but
>>>>> when I attempt to access a view I always get an sqlcode error value of
>>>>> -243 that seems to indicate that the cursor sensitivity is incorrect.
>>>>> Has anyone else done this and actually managed to display view in an
>>>>> openoffice.org database?
>>>>
>>>> Could you provide more information on the failing query? A JDBC trace
>>>> may help you with figuring out what's going on.
>>>>
>>>
>>> Sure
>>>
>>> This query works (this is a view defined in the database and openoffice
>>> is issuing a select * from ... type of query)
>>>
>>> create view rawbankdata.RBC_TX_RESOLVED as
>>> select cola as bank_ID,
>>> colb as transit_ID,
>>> colc as account_ID,
>>> cast( substr(cold,1,4) || '-' || substr(cold,5,2)|| '-' ||
>>> substr(cold,7,2) as date) as TX_Date1,
>>> cast( substr(cole,1,4) || '-' || substr(cole,5,2) || '-' ||
>>> substr(cole,7,2) as date) as TX_Date2,
>>> colf as ID4,
>>> colg as ID5,
>>> colh as Currency_type,
>>> coli as ID6, colj as TX_type,
>>> colk as TX_ID,
>>> coll as TX_merchant,
>>> colm as amex_merchant,
>>> cast(coln as
>>> decimal(18,2)) as Amount,
>>> colo as ID7,
>>> colp as ID8,
>>> colq as ID9 from rawbankdata.rbc_transactions
>>>
>>> In the above view "rawbankdata.rbc_transactions" is a table
>
> A view is also just a table. In fact, everything that holds data in an
> RDBMS is a table.
>
>>> DB2 SQL error: SQLCODE: -243, SQLSTATE 36001, SQLERRMC:: SQL_CURSH200C1
>>>
>>>
>>>
>>> create view rawbankdata.rbc_transasction_union as select
>>> cast('NDDS' as char(4)) as tx_types,
>>> TX_DATE1,
>>> TX_DATE2,
>>> tx_merchant,
>>> tx_id,
>>> amex_merchant,
>>> substr(tx_id, 11,3) as DepSlip,
>>> substr(tx_id, 8,3) as StoreNumber,
>>> case when id6 = '0' then amount * -1 else amount end as amount
>>> from rawbankdata.rbc_archive a
>>> where tx_merchant like 'NDDS%'
>>> union all
>>> select cast('AMEX' as char(4)) as tx_types,
>>> TX_DATE1,
>>> TX_DATE2,
>>> tx_merchant,
>>> tx_id,
>>> amex_merchant,
>>> substr(tx_id, 11,3) as DepSlip,
>>> substr(tx_id, 8,3) as StoreNumber,
>>> case when id6 = '0' then amount * -1 else amount end as amount
>>> from rawbankdata.rbc_archive a
>>> where tx_merchant = 'MISC PAYMENT' and substr(amex_merchant, 1, 4) =
>>> 'AMEX' union all
>>> select
>>> cast('DEPS' as char(4)) as tx_types,
>>> TX_DATE1,
>>> TX_DATE2,
>>> tx_merchant,
>>> tx_id,
>>> amex_merchant,
>>> substr(tx_id, 11,3) as DepSlip,
>>> substr(tx_id, 8,3) as StoreNumber,
>>> case when id6 = '0' then amount * -1 else amount end as amount
>>> from rawbankdata.rbc_archive a
>>> where tx_merchant like 'DEPOSIT%'
>>> union all
>>> select
>>> cast('OTHS' as char(4)) as tx_types,
>>> TX_DATE1,
>>> TX_DATE2,
>>> tx_merchant,
>>> tx_id,
>>> amex_merchant,
>>> substr(tx_id, 11,3) as DepSlip,
>>> substr(tx_id, 8,3) as StoreNumber,
>>> case when id6 = '0' then amount * -1 else amount end as amount
>>> from rawbankdata.rbc_archive a
>>> where substr(tx_merchant, 1, 2) not in ('MC', 'VI', 'EF', 'MI', 'DE',
>>> 'ND')
>>>
>>> union all
>>> select
>>> cast('EFTA' as char(4)) as tx_types,
>>> TX_DATE1,
>>> TX_DATE2,
>>> tx_merchant,
>>> tx_id,
>>> amex_merchant,
>>> substr(tx_id, 11,3) as DepSlip,
>>> storeno as StoreNumber,
>>> case when id6 = '0' then amount * -1 else amount end as amount
>>> from rawbankdata.rbc_archive a
>>> join rawbankdata.merchant_number on substr(tx_merchant, 8, 8) =
>>> rb_merch_short where substr(tx_merchant, 1, 6) = 'EFT 60'
>
> You have a JOIN here. The explanation for SQL0243 states:
>
> For example, if the query includes a join, the result table is
> read-only.
>
> DB2 won't be able to apply a reverse mapping for the join, i.e. it can't
> figure out how to apply changes from insert/update/delete to the correct
> underlying row(s).
>
> So you may want to consider rephrasing your view definition in such a way
> that no join occurs, for example by using subselects. Also, have a look
> at
> the access plan for "SELECT * FROM <view>"? You shouldn't see temp tables
> there.
>
>> [ibm][db2][jcc][Time:1170171024400][Thread:Thread-9419
[Connection(a)789144]
>> prepareStatement (SELECT * FROM "RAWBANKDATA"."RBC_TRANSASCTION_UNION",
>> 1005, 1008) called
>
> I guess the interesting part comes before that, namely where the statement
> attributes are set. You could change this to make it an
> INSENSITIVE/ASENSITIVE cursor. In any case, this looks to me like an
> application error and you should report it to the OpenOffice developers.
>

Yes, I can see that that is probably the route to take. I had set up a
couple of simple little tables and views to test this out with and noticed
that as soon as a join is in there the -243 error comes up. It is too bad
there is no way to tell the JDBC driver to use INSENSITIVE cursors through
a settable property (like cursorSensitivity) as that would make life much
easier.

The -243 error seems to be unique to the Type 4 JDBC driver.... If I define
an openoffice.org database that uses the Windows CLI driver through the
ODBC interface I can access the views with joins without an error of any
kind.

--
Lorne Sunley
First  |  Prev  | 
Pages: 1 2
Prev: SQL0805N affected by DB/DBM CFG ?
Next: Tablespace rollforward condition