MVSFORUMS.com

justaprogrammer · Beginner Joined: 01 Jun 2010 Posts: 3 Topics: 1

Hi All

I am trying to incorporate Multi Row fetch logic into my code.
The loginc I am trying to accomplish is something like this..

Fetch multiple records from an inout dataset which contains employee number , and SSN..

Now use these two fields in the WHERE condition of MULTI ROW FETCH ROWSET to fetch all the matching rows from the table...

Example;;

n INPUT file I have

dbzTHEdinosauer · Posted: Thu Sep 22, 2011 2:56 am Post subject:

Host Variable Arrays are not for WHERE Clause.
best you can do is create an IN List Array.
and, can one emp have more than one ssn
and can an ssn be assigned to more than one emp?
_________________
Dick Brenholtz
American living in Varel, Germany

justaprogrammer · Beginner Joined: 01 Jun 2010 Posts: 3 Topics: 1

I seriously doubt the feasibility behind using IN because I dont know how many records are there in input file.. In case I restrict it to a particular number of reads per cycle then in case if any cycle reads lesser than a specified number of predefined records I will not have anything for file which will be equivalent to SQLERRD(3) in DB@ which can cause me a lot of coding efforts to decide the IN factor for predicate..

Any dbzTHEdinosauer, thanks for the reply.. I had posted this infact to see if there is a possibility of HOST VARIABLE ARRAY for WHERE for which your answer is a well satisfactory one..

Thanks Again..

kolusu · Posted: Thu Sep 22, 2011 10:26 am Post subject:

justaprogrammer,

How about Fetching the rows using a BETWEEN for both EMP and SSN. Populate the low and high-values for both EMP and SSN from your file

ex: untested

justaprogrammer · Beginner Joined: 01 Jun 2010 Posts: 3 Topics: 1

kolusu

I had thought of Between and eve tried that on SPUFI with COUNT(*) so as to see if it looks feasible.. but compared to 200 rows I shold have fetched with my file contents I ended up with 2000.. thats for my particular instance.. When the file size will increase and the difference between the high and the low value this count will keep on increasing..

and I believe its much better to use 200 individual fetch rather than selecting 10 times of the rows expected for my code..

Please correct me if I am wrong..

dbzTHEdinosauer · Posted: Fri Sep 23, 2011 3:54 am Post subject:

justaprogrammer,

you never did answer my question about employee and ssn.
i would think that one or the other would be sufficient; using both would be unnecessary.

also, is there more than one name and destination for each employee?

i would think, based on the little info that you have provided, that a singleton select for each employee would suffice
and that you would not need a cursor.

following up on that thought / not knowing anything about the table/
why not unload the employee table / are there millions and millions of rows?
and do a simple match / or even a dfsort joinkeys/ to extract your information.

the two steps:

he unload
match

/ even thru a cobol program/ would probably require less resources than thousands of selects.

you have provided little or no information,
and have biased the question based on your presentation.

provide some information to dispute my alternative solution.

and last but not least. yes there is a point of diminishing returns with respect to number of rows retrieved by each FETCH.
But until that point is reached / and it is different in everycase/
pounding thru memory is often faster / though requires more code/
that leaving it up to db2 to make the same decisions.
_________________
Dick Brenholtz
American living in Varel, Germany