IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL IN predicate using COBOL table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 202
Location: Toronto, ON, Canada

PostPosted: Fri Jun 29, 2018 10:30 pm
Reply with quote

Is it possible to have an sql IN predicate that uses a COBOL table or list of values? I am trying to avoid using dynamics sql for performance reasons
Code:

WHERE column IN (:WS-MY-COBOL-TABLE)

WS-MY-COBOL-TABLE would be either an OCCURS of values or field with a list of values. I have been reading the SQL Reference manual but no luck.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sat Jun 30, 2018 4:10 am
Reply with quote

Why do you think for dynamic sql there would be any performance issues?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Sat Jun 30, 2018 3:43 pm
Reply with quote

As long as your host variable is a list of comma separated, quoted values then there shouldn't be a problem because that is what SQL expects. If it isn't then why ask?
Back to top
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania

PostPosted: Sat Jun 30, 2018 3:54 pm
Reply with quote

jerryte wrote:
Is it possible to have an sql IN predicate that uses a COBOL table or list of values? I am trying to avoid using dynamics sql for performance reasons
Code:

WHERE column IN (:WS-MY-COBOL-TABLE)

WS-MY-COBOL-TABLE would be either an OCCURS of values or field with a list of values. I have been reading the SQL Reference manual but no luck.

Just how hard would it have been to try it, compiling a tiny COBOL program?
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Tue Jul 03, 2018 7:42 pm
Reply with quote

The most obvious approach is a bit cumbersome if the IN list is long, but it works:

Quote:

Where column IN (:WS-HV1, :WS-HV2, ... :WS-HVn)
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Tue Jul 03, 2018 8:49 pm
Reply with quote

It looks like you want something like this:

Code:
01  WS-VALID-VALUE-INLIST.
    05  WS-VS-01        PIC S9(4)  COMP value +01.
    05  WS-VS-02        PIC S9(4)  COMP value +02.
    05  WS-VS-03        PIC S9(4)  COMP value +03.
    05  WS-VS-04        PIC S9(4)  COMP value +04.
    05  WS-VS-05        PIC S9(4)  COMP value +05.
    05  WS-VS-06        PIC S9(4)  COMP value +06.
    05  WS-VS-07        PIC S9(4)  COMP value +07.
...
EXEC SQL
   ...
   WHERE VALID_VALUE IN (:WS-VALID-VALUE-INLIST)
END-EXEC


This should work. Please try and let us know.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Tue Jul 03, 2018 9:23 pm
Reply with quote

I do not think that that will work. DB2 will only see one, long, binary string whereas it expects a list of comma separated values.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Tue Jul 03, 2018 9:29 pm
Reply with quote

There is no harm in trying!!!

Just like Prino said:
Quote:
Just how hard would it have been to try it, compiling a tiny COBOL program?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Jul 03, 2018 9:44 pm
Reply with quote

the fact that something compiles does NOT mean that the program will produce the expected results icon_cool.gif

imo when posting snippets it is the responsibility of the poster to check that the snippet provides the proper results

posting wrong coding snippets is bad forum behavior
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Tue Jul 03, 2018 9:51 pm
Reply with quote

enrico-sorichetti wrote:
the fact that something compiles does NOT mean that the program will produce the expected results icon_cool.gif

imo when posting snippets it is the responsibility of the poster to check that the snippet provides the proper results

posting wrong coding snippets is bad forum behavior


I understand that enrico. I have posted only because I have faith in my suggestion. I have tried this in past and it worked for me. The only reason I'm not sure is I don't know if it depends upon the DB2 version and currently I don't have access to mainframe to re-test. That's why I asked to try this approach.

And at the same time I would like to know: posting your opinion without having any concrete proof: Is that a bad forum behavior too, in your humble opinion?
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 202
Location: Toronto, ON, Canada

PostPosted: Wed Jul 04, 2018 6:19 pm
Reply with quote

mistah kurtz wrote:
It looks like you want something like this:

Code:
01  WS-VALID-VALUE-INLIST.
    05  WS-VS-01        PIC S9(4)  COMP value +01.
    05  WS-VS-02        PIC S9(4)  COMP value +02.
    05  WS-VS-03        PIC S9(4)  COMP value +03.
    05  WS-VS-04        PIC S9(4)  COMP value +04.
    05  WS-VS-05        PIC S9(4)  COMP value +05.
    05  WS-VS-06        PIC S9(4)  COMP value +06.
    05  WS-VS-07        PIC S9(4)  COMP value +07.
...
EXEC SQL
   ...
   WHERE VALID_VALUE IN (:WS-VALID-VALUE-INLIST)
END-EXEC


This should work. Please try and let us know.

I tried this and it worked. Thanka
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Wed Jul 04, 2018 8:09 pm
Reply with quote

Maybe I will throw that book away! It only goes up to V5.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Wed Jul 04, 2018 9:49 pm
Reply with quote

Nic Clouston wrote:
Maybe I will throw that book away! It only goes up to V5.
Surprised me too. I know there was a time when wasn't supported, but cannot say when it changed.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Jul 04, 2018 9:58 pm
Reply with quote

@jerryte: Glad to know that it worked. You are welcome.

@don.leahy: I tried this somewhere around 2013. Can't say for sure when it changed.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Jul 05, 2018 10:27 am
Reply with quote

Jerryte, This is very basic stuff that you could have tried , what was the hardest part that made you to post here? If it works it works else it won’t work and why afraid of using dynamic queries ?
www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_inpredicate.html
Such questions usually belongs to beginners forum here
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 202
Location: Toronto, ON, Canada

PostPosted: Thu Jul 05, 2018 8:16 pm
Reply with quote

I had tried but I used an TABLE instead of a list. DB2 didn't like it. I read the reference manuals but it doesn't say anything about this. I do research a topic before I post but I missed this one.

I don't know about the performance of dynamic sql in a high usage environment. I have not had a chance to research this. I will add this to my long list of things to try before I retire.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Jul 05, 2018 11:53 pm
Reply with quote

okay, I think much more effective way to handle in such situations is to make a use of GTT.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Replace each space in cobol string wi... COBOL Programming 2
No new posts Load new table with Old unload - DB2 DB2 6
No new posts COBOL -Linkage Section-Case Sensitive COBOL Programming 1
No new posts COBOL ZOS Web Enablement Toolkit HTTP... COBOL Programming 0
No new posts Pulling a fixed number of records fro... DB2 2
Search our Forums:

Back to Top