Prev: SQL0805N affected by DB/DBM CFG ?
Next: Tablespace rollforward condition
From: LorneSunley on 30 Jan 2007 06:27
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?

Thanks for any help

--
Lorne Sunley
From: Knut Stolze on 30 Jan 2007 13:35
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.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
From: LorneSunley on 30 Jan 2007 09:31
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

This query does not work ( this is another view in the database and is based
on another table in the database) AFAIK openoffice is issuing another
"select * from ..." type of query

All I am doing with open office is defining a database that is a connection
to the DB2 server with the JDBC driver, after OO.org fetches the database
information I just double-click on a table or view.

error is

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'

--
Lorne Sunley
From: LorneSunley on 30 Jan 2007 10:37
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
>
> This query does not work ( this is another view in the database and is
> based on another table in the database) AFAIK openoffice is issuing
> another "select * from ..." type of query
>
> All I am doing with open office is defining a database that is a
> connection to the DB2 server with the JDBC driver, after OO.org fetches
> the database information I just double-click on a table or view.
>
> error is
>
> 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'
>


This is the JDBC trace data for the failing query

[ibm][db2][jcc][Time:1170171024400][Thread:Thread-9419][Connection(a)789144]
prepareStatement (SELECT * FROM "RAWBANKDATA"."RBC_TRANSASCTION_UNION",
1005, 1008) called
[ibm][db2][jcc][Time:1170171024400][Thread:Thread-9419][Connection(a)789144]
prepareStatement () returned PreparedStatement(a)1551f60
[ibm][db2][jcc][Time:1170171024400][Thread:Thread-9419
[PreparedStatement(a)1551f60] executeQuery () called
[ibm][db2][jcc] [t4][time:1170171024401][thread:Thread-9419][tracepoint:1
[Request.flush]
[ibm][db2][jcc][t4] SEND BUFFER: PRPSQLSTT (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0 1 2 3 4 5 6 7 8 9 A B C D E F
0123456789ABCDEF 0123456789ABCDEF
[ibm][db2][jcc][t4] 0000 0058D05100010052
200D0044211387A2 .X.Q...R ..D!... ..}...........gs
[ibm][db2][jcc][t4] 0010 8986858440404040
4040404040404040 ....@@@@@@@@@@@@ ifed
[ibm][db2][jcc][t4] 0020 D5E4D3D3C9C44040
4040404040404040 ......@@@@@@@@@@ NULLID
[ibm][db2][jcc][t4] 0030 4040E2E8E2E2C8F2 F0F0404040404040
@@........@@@@@@ SYSSH200
[ibm][db2][jcc][t4] 0040 404040405359534C 564C303100010005
@@@@SYSLVL01.... ...<.<......
[ibm][db2][jcc][t4] 0050
2116F10005214604 !....!F. ..1.....
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4] SEND BUFFER: SQLATTR (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0000 0033D0530001002D
2450000000002353 .3.S...-$P....#S ..}......&......
[ibm][db2][jcc][t4] 0010 454E534954495645 2053544154494320 ENSITIVE
STATIC .+..............
[ibm][db2][jcc][t4] 0020 5343524F4C4C2046 4F52205550444154 SCROLL FOR
UPDAT ...|<<..|...&...
[ibm][db2][jcc][t4] 0030 4520FF
E . ...
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4] SEND BUFFER: SQLSTT (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0000 0044D0430001003E
2414000000003453 .D.C...>$.....4S ..}.............
[ibm][db2][jcc][t4] 0010 454C454354202A20 46524F4D20225241 ELECT * FROM
"RA .<........|(....
[ibm][db2][jcc][t4] 0020 5742414E4B444154 41222E225242435F
WBANKDATA"."RBC_ ...+............
[ibm][db2][jcc][t4] 0030 5452414E53415343 54494F4E5F554E49
TRANSASCTION_UNI ...+......|+..+.
[ibm][db2][jcc][t4] 0040 4F4E22FF ON".
|+..
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4] SEND BUFFER: DSCSQLSTT (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0000 0053D0410002004D
20080044211387A2 .S.A...M ..D!... ..}....(......gs
[ibm][db2][jcc][t4] 0010 8986858440404040
4040404040404040 ....@@@@@@@@@@@@ ifed
[ibm][db2][jcc][t4] 0020 D5E4D3D3C9C44040
4040404040404040 ......@@@@@@@@@@ NULLID
[ibm][db2][jcc][t4] 0030 4040E2E8E2E2C8F2 F0F0404040404040
@@........@@@@@@ SYSSH200
[ibm][db2][jcc][t4] 0040 404040405359534C 564C303100010005
@@@@SYSLVL01.... ...<.<......
[ibm][db2][jcc][t4] 0050
214605 !F. ...
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4] SEND BUFFER: OPNQRY (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0000 0069D00100030063
200C0044211387A2 .i.....c ..D!... ..}...........gs
[ibm][db2][jcc][t4] 0010 8986858440404040
4040404040404040 ....@@@@@@@@@@@@ ifed
[ibm][db2][jcc][t4] 0020 D5E4D3D3C9C44040
4040404040404040 ......@@@@@@@@@@ NULLID
[ibm][db2][jcc][t4] 0030 4040E2E8E2E2C8F2 F0F0404040404040
@@........@@@@@@ SYSSH200
[ibm][db2][jcc][t4] 0040 404040405359534C 564C303100010008
@@@@SYSLVL01.... ...<.<......
[ibm][db2][jcc][t4] 0050 211400007FFF0006
2141FFFF00082156 !.......!A....!V ...."...........
[ibm][db2][jcc][t4] 0060 000000400005215D
01 ...@..!]. ... ...).
[ibm][db2][jcc][t4]
[ibm][db2][jcc] [t4][time:1170171024408][thread:Thread-9419][tracepoint:2
[Reply.fill]
[ibm][db2][jcc][t4] RECEIVE BUFFER: SQLCARD (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0 1 2 3 4 5 6 7 8 9 A B C D E F
0123456789ABCDEF 0123456789ABCDEF
[ibm][db2][jcc][t4] 0000 0067D04300010061
2408000DFFFFFF33 .g.C...a$......3 ..}..../........
[ibm][db2][jcc][t4] 0010 3630303153514C4E 51373532006D001A
6001SQLNQ752.m.. ......<+....._..
[ibm][db2][jcc][t4] 0020 8000000000000000
000000000070FEFF .............p.. ................
[ibm][db2][jcc][t4] 0030 FF00000000202020
2020202020202020 ..... ................
[ibm][db2][jcc][t4] 0040 0012475349464544
2020202020202020 ..GSIFED ................
[ibm][db2][jcc][t4] 0050 202020200000000E
53514C5F43555253 ....SQL_CURS ..........<.....
[ibm][db2][jcc][t4] 0060 483230304331FF
H200C1. .......
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4] RECEIVE BUFFER: SQLCARD (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0000 0074D0430002006E
24080029FDFFFF35 .t.C...n$..)...5 ..}....>........
[ibm][db2][jcc][t4] 0010 3630393853514C4E 51373532006D0012
6098SQLNQ752.m.. ......<+....._..
[ibm][db2][jcc][t4] 0020 8000000000000000
0000000000000000 ................ ................
[ibm][db2][jcc][t4] 0030 0000000000202020
2020202020202020 ..... ................
[ibm][db2][jcc][t4] 0040 0012475349464544
2020202020202020 ..GSIFED ................
[ibm][db2][jcc][t4] 0050 202020200000001B
32FF2D323433FF33 ....2.-243.3 ................
[ibm][db2][jcc][t4] 0060 36303031FF53514C 5F43555253483230
6001.SQL_CURSH20 .......<........
[ibm][db2][jcc][t4] 0070 304331FF
0C1. ....
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4] RECEIVE BUFFER: OPNQFLRM (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0000 0026D05200030020 2212000611490008 .&.R...
"....I.. ..}.............
[ibm][db2][jcc][t4] 0010 00162110C7E2C9C6
C5C4404040404040 ..!.......@@@@@@ ....GSIFED
[ibm][db2][jcc][t4] 0020 404040404040 @@@@@@
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4] RECEIVE BUFFER: SQLCARD (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0000 0074D0030003006E
24080029FDFFFF35 .t.....n$..)...5 ..}....>........
[ibm][db2][jcc][t4] 0010 3630393853514C4E 51373532006D0012
6098SQLNQ752.m.. ......<+....._..
[ibm][db2][jcc][t4] 0020 8000000000000000
0000000000000000 ................ ................
[ibm][db2][jcc][t4] 0030 0000000000202020
2020202020202020 ..... ................
[ibm][db2][jcc][t4] 0040 0012475349464544
2020202020202020 ..GSIFED ................
[ibm][db2][jcc][t4] 0050 202020200000001B
32FF2D323433FF33 ....2.-243.3 ................
[ibm][db2][jcc][t4] 0060 36303031FF53514C 5F43555253483230
6001.SQL_CURSH20 .......<........
[ibm][db2][jcc][t4] 0070 304331FF
0C1. ....
[ibm][db2][jcc][t4]
[ibm][db2][jcc] BEGIN TRACE_DIAGNOSTICS
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d]
java.sql.SQLException
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d][Sqlca(a)1995d80] DB2
SQLCA from server
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d][Sqlca(a)1995d80]
SqlCode = -243
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d][Sqlca(a)1995d80]
SqlErrd = { -2145779603, 0, 0, 0, -400, 0 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d][Sqlca(a)1995d80]
SqlErrmc = SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d][Sqlca(a)1995d80]
SqlErrmcTokens = { SQL_CURSH200C1 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d][Sqlca(a)1995d80]
SqlErrp = SQLNQ752
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d][Sqlca(a)1995d80]
SqlState = 36001
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d][Sqlca(a)1995d80]
SqlWarn =
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d] SQL state = 36001
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d] Error code = -243
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d] Tokens =
SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)10bc49d] Stack trace
follows
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -243, SQLSTATE:
36001, SQLERRMC: SQL_CURSH200C1
at com.ibm.db2.jcc.a.hd.e(hd.java:1659)
at com.ibm.db2.jcc.a.hd.a(hd.java:1235)
at com.ibm.db2.jcc.c.jb.h(jb.java:149)
at com.ibm.db2.jcc.c.jb.a(jb.java:43)
at com.ibm.db2.jcc.c.w.a(w.java:30)
at com.ibm.db2.jcc.c.cc.g(cc.java:160)
at com.ibm.db2.jcc.a.hd.n(hd.java:1215)
at com.ibm.db2.jcc.a.id.gb(id.java:1780)
at com.ibm.db2.jcc.a.id.d(id.java:2255)
at com.ibm.db2.jcc.a.id.X(id.java:505)
at com.ibm.db2.jcc.a.id.executeQuery(id.java:488)
[ibm][db2][jcc] END TRACE_DIAGNOSTICS
[ibm][db2][jcc] BEGIN TRACE_DIAGNOSTICS
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3]
java.sql.SQLException
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3][Sqlca(a)1ab28fe] DB2
SQLCA from server
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3][Sqlca(a)1ab28fe]
SqlCode = -727
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3][Sqlca(a)1ab28fe]
SqlErrd = { -2146303891, 0, 0, 0, 0, 0 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3][Sqlca(a)1ab28fe]
SqlErrmc = 2;-243;36001;SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3][Sqlca(a)1ab28fe]
SqlErrmcTokens = { 2, -243, 36001, SQL_CURSH200C1 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3][Sqlca(a)1ab28fe]
SqlErrp = SQLNQ752
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3][Sqlca(a)1ab28fe]
SqlState = 56098
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3][Sqlca(a)1ab28fe]
SqlWarn =
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3] SQL state = 56098
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3] Error code = -727
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3] Tokens =
2;-243;36001;SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)c4aad3] Stack trace follows
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -727, SQLSTATE:
56098, SQLERRMC: 2;-243;36001;SQL_CURSH200C1
at com.ibm.db2.jcc.a.hd.e(hd.java:1659)
at com.ibm.db2.jcc.a.id.a(id.java:1717)
at com.ibm.db2.jcc.c.jb.a(jb.java:200)
at com.ibm.db2.jcc.c.jb.b(jb.java:88)
at com.ibm.db2.jcc.c.w.b(w.java:60)
at com.ibm.db2.jcc.c.dc.d(dc.java:339)
at com.ibm.db2.jcc.a.id.db(id.java:1710)
at com.ibm.db2.jcc.a.id.gb(id.java:1781)
at com.ibm.db2.jcc.a.id.d(id.java:2255)
at com.ibm.db2.jcc.a.id.X(id.java:505)
at com.ibm.db2.jcc.a.id.executeQuery(id.java:488)
[ibm][db2][jcc] END TRACE_DIAGNOSTICS
[ibm][db2][jcc] BEGIN TRACE_DIAGNOSTICS
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738]
java.sql.SQLException
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738][Sqlca(a)ce5b1c] DB2
SQLCA from server
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738][Sqlca(a)ce5b1c]
SqlCode = -727
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738][Sqlca(a)ce5b1c]
SqlErrd = { -2146303891, 0, 0, 0, 0, 0 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738][Sqlca(a)ce5b1c]
SqlErrmc = 2;-243;36001;SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738][Sqlca(a)ce5b1c]
SqlErrmcTokens = { 2, -243, 36001, SQL_CURSH200C1 }
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738][Sqlca(a)ce5b1c]
SqlErrp = SQLNQ752
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738][Sqlca(a)ce5b1c]
SqlState = 56098
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738][Sqlca(a)ce5b1c]
SqlWarn =
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738] SQL state = 56098
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738] Error code = -727
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738] Tokens =
2;-243;36001;SQL_CURSH200C1
[ibm][db2][jcc][Thread:Thread-9419][SQLException(a)105738] Stack trace follows
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -727, SQLSTATE:
56098, SQLERRMC: 2;-243;36001;SQL_CURSH200C1
at com.ibm.db2.jcc.a.hd.e(hd.java:1659)
at com.ibm.db2.jcc.a.hd.a(hd.java:1256)
at com.ibm.db2.jcc.c.jb.o(jb.java:655)
at com.ibm.db2.jcc.c.jb.j(jb.java:270)
at com.ibm.db2.jcc.c.jb.c(jb.java:57)
at com.ibm.db2.jcc.c.w.c(w.java:42)
at com.ibm.db2.jcc.c.cc.h(cc.java:177)
at com.ibm.db2.jcc.a.hd.p(hd.java:1252)
at com.ibm.db2.jcc.a.id.d(id.java:2298)
at com.ibm.db2.jcc.a.id.X(id.java:505)
at com.ibm.db2.jcc.a.id.executeQuery(id.java:488)
[ibm][db2][jcc] END TRACE_DIAGNOSTICS
[ibm][db2][jcc] [t4][time:1170171024416][thread:Thread-9419][tracepoint:1
[Request.flush]
[ibm][db2][jcc][t4] SEND BUFFER: RDBCMM (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0 1 2 3 4 5 6 7 8 9 A B C D E F
0123456789ABCDEF 0123456789ABCDEF
[ibm][db2][jcc][t4] 0000 000AD00100010004
200E ........ . ..}.......
[ibm][db2][jcc][t4]
[ibm][db2][jcc] [t4][time:1170171024416][thread:Thread-9419][tracepoint:2
[Reply.fill]
[ibm][db2][jcc][t4] RECEIVE BUFFER: ENDUOWRM (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0 1 2 3 4 5 6 7 8 9 A B C D E F
0123456789ABCDEF 0123456789ABCDEF
[ibm][db2][jcc][t4] 0000 002BD05200010025 220C000611490004 .+.R..
%"....I.. ..}.............
[ibm][db2][jcc][t4] 0010 0005211501001621
10C7E2C9C6C5C440 ..!....!.......@ .........GSIFED
[ibm][db2][jcc][t4] 0020 4040404040404040 404040 @@@@@@@@@@@
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4] RECEIVE BUFFER: SQLCARD (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0000 000BD00300010005 2408FF .......
$.. ..}........
[ibm][db2][jcc][t4]

--
Lorne Sunley
From: Knut Stolze on 31 Jan 2007 12:09
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.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
 |  Next  |  Last
Pages: 1 2
Prev: SQL0805N affected by DB/DBM CFG ?
Next: Tablespace rollforward condition