VSE VSAM JDBC Connector question

[Bradshaw, Gary]

Hi all,

Has anyone tried to use a JOIN with the VsamJdbcDriver? It looked promising in the manual, but when I tried it I got the following message:

java.lang.ClassCastException: com.ibm.vse.jdbc.VsamJdbcWhereClause cannot be cast to com.ibm.vse.jdbc.VsamJdbcSelectStatement

I was trying to join two maps together:

select ex5.grp, ex5.sub, ex5.id, 99999999 - ex5.effective eff_date, emp.term_date, -
bit_switch, dep_last_name_1, dep_first_name_1, dep_date_of_birth_1 dob, -
dep_bit_switch_1 dep_cir -
from test.user.catalog\t800.employee\c800emp as emp - inner join test.user.catalog\t800.employee.aix5\c800ex5 as ex5 -
on ex5.prim_key = emp.key -
where ex5.grp = 'HVC' and ex5.sub = 'H01'

It seems to have parsed the JOIN part all right, but then it got confused on the WHERE clause. Is what I'm trying to do even possible? The ex5 key consists of the fields GRP, SUB, ID, EFFECTIVE, and the primary key to the emp file.

- gary



------------------------------------------------------------------------
The information contained in this communication is intended
only for the use of the recipient(s) named above. It may
contain information that is privileged or confidential, and
may be protected by State and/or Federal Regulations. If
the reader of this message is not the intended recipient,
you are hereby notified that any dissemination,
distribution, or copying of this communication, or any of
its contents, is strictly prohibited. If you have received
this communication in error, please return it to the sender
immediately and delete the original message and any copy
of it from your computer system. If you have any questions
concerning this message, please contact the sender.
------------------------------------------------------------------------





_______________________________________________
VSE-L mailing list
VS...@lists.lehigh.edu
https://lists.lehigh.edu/mailman/listinfo/vse-l

[indust...@winwholesale.com]

"VSE-L" <vse-l-bounces+industrynews=winwhole...@lists.lehigh.edu> wrote on 06/23/2016 04:55:46 PM:
> Has anyone tried to use a JOIN with the VsamJdbcDriver? It looked
> promising in the manual, but when I tried it I got the following message:

> java.lang.ClassCastException: com.ibm.vse.jdbc.VsamJdbcWhereClause
> cannot be cast to com.ibm.vse.jdbc.VsamJdbcSelectStatement

        Did you try turning it around?  Since your WHERE clause is for the ex5 table then it makes logical sense to make that the primary table in the join.

select ex5.grp, ex5.sub, ex5.id, -
       99999999 - ex5.effective as eff_date, -
       emp.term_date, bit_switch, -
       dep_last_name_1, dep_first_name_1, -
       dep_date_of_birth_1 as dob, -
       dep_bit_switch_1 as dep_cir -
  from test.user.catalog\t800.employee.aix5\c800ex5 as ex5 -
 inner join test.user.catalog\t800.employee\c800emp as emp -
    on emp.key = ex5.prim_key -
 where ex5.grp = 'HVC' -
   and ex5.sub = 'H01'

Sincerely,

Dave Clark
--
Winsupply Group Services
3110 Kettering Boulevard
Dayton, Ohio  45439  USA
(937) 294-5331




*********************************************************************************************
This email message and any attachments is for use only by the named addressee(s) and may contain confidential, privileged and/or proprietary information. If you have received this message in error, please immediately notify the sender and delete and destroy the message and all copies. All unauthorized direct or indirect use or disclosure of this message is strictly prohibited. No right to confidentiality or privilege is waived or lost by any error in transmission.
*********************************************************************************************

[Bradshaw, Gary]

Alas, I get the same result regardless of the order in which I name the tables. On an inner join the order shouldn’t matter anyway, but it was worth a try.

 

select ex5.grp, ex5.sub, ex5.id, 99999999 - ex5.effective eff_date, emp.term_date,     bit_switch, dep_last_name_1, dep_first_name_1, dep_date_of_birth_1 dob,     dep_bit_switch_1 dep_cir from test.user.catalog\t800.employee.aix5\c800ex5 as ex5 inner join test.user.catalog\t800.employee\c800emp as emp     on ex5.prim_key = emp.key where ex5.grp = 'HVC' and ex5.sub = 'H01'

Connecting to VSETEST... 375.0 ms

Creating statement... 15.0 ms

Executing query...ERROR! java.lang.ClassCastException: com.ibm.vse.jdbc.VsamJdbcWhereClause cannot be cast to com.ibm.vse.jdbc.VsamJdbcSelectStatement

 

[Bradshaw, Gary]

IBM support confirmed that there is a bug in the driver (thanks again Ingo), and it will likely be fixed in a future release. However what I was trying to do isn’t practical due to the size of my clusters. In a real world application I’d want to do it in two queries, or use the Redirector instead.

 

-          gary