View previous topic :: View next topic
|
Author |
Message |
xknight
Active User
Joined: 22 Jan 2008 Posts: 117 Location: Liberty city
|
|
|
|
1) Program A (Batch) Calls Program B (DB2Batch), how will you write the run JCL for the program A, if you intend to include PLAN for Program B, where do you include it in JCL
2) There are four files,
FILE A
FILE B
FILE C
FILE D
FILE A and FILE B are the input files,
IF WS-KEY-FILEA = WS-KEY-FILEB ( Search FILEB till EOF )
THEN
Write it in FILE C
ELSE
Write it in FILE D
END-IF
Situation: Since these files has millions of millions of records, normal read and logic should not be used, what are the optimization will you do for the performance / saving system time.
3) The input record is
00 AAA
11 BBB
22 CCC
...........
...........
...........
...........
...........
99 ZZZ
Requirement is to move the 99th record to third position
like,
00 AAA
11 BBB
99 ZZZ
22 CCC
How shall be done with the sort?
4) Using Select (Singleton) we are retrieving from DB, but the particular selects multiple record (say two records), how we shall fetch the second record alone ?
Dear experts, throw some light on this....... |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
It would be nice if your post the answers you have/had. And someone will be around with some suggestion/s if needed. |
|
Back to top |
|
|
xknight
Active User
Joined: 22 Jan 2008 Posts: 117 Location: Liberty city
|
|
|
|
These are the questions i haven't answered well....
So i am looking all your expert opinions.... |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
4) can't be answered because you have no idea which of the two returned rows is "the second". |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
2)
looks like the IBM interviewer never heard of sort
if the two files are sorted no need to read everytime until the end of file
if You are curious on how should be done
ibmmainframes.com/viewtopic.php?t=22649
Quote: |
... Since these files has millions of millions of records, normal read and logic should not be used, the performance ... |
stupid assertion, who said that !
not even the need to write a program, a properly written sort can do it do it |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
1. In the JCL rather than coding a EXEC PGM="pure-cobol", you would code a call to TSO, as in EXEC PGM=IKJEFT01, passing the actual execution in the SYSIN.
2. I'm not sure what information does this
Quote: |
Situation: Since these files has millions of millions of records, normal read and logic should not be used, what are the optimization will you do for the performance / saving system time. |
add to the question. As enrico has mentioned, that's a simple file-match requirement. May be interviewer wanted to listen that you can use a COBOL internal table to store the data from one file and then search in that table for the match from 2nd file, but who knows. Have you enquired about this?
3. If you are using the Release of SyncSort/DFSort which supoprts SUBSET, you can use:
Code: |
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
1111
2222
3333
4444
5555
6666
//OUT DD SYSOUT=*
//TOOLIN DD *
SUBSET FROM(IN) TO(OUT) KEEP INPUT RRN(1) RRN(2) RRN(6) |
replace RRN(6) to RRN(99), for the question asked.
However if you insist to use PGM=SORT, try (ofcourse substitute for 99);
Code: |
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
1111
2222
3333
4444
5555
6666
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC OVERLAY=(81:SEQNUM,2,ZD)
OUTFIL INCLUDE=(81,2,ZD,EQ,1,OR,81,2,ZD,EQ,2,OR,81,2,ZD,EQ,5),
BUILD=(1,80)
/* |
|
|
Back to top |
|
|
xknight
Active User
Joined: 22 Jan 2008 Posts: 117 Location: Liberty city
|
|
|
|
@ Enrico
Thanks for your expert opinion for Question 2), i answered the same like as your verdict, but he was saying the records werent sorted , so i suggested him to sort and put it in array, he refused that way.
Anywayz thanks enrico
@ Anuj
Thank you very much for the Sort JCL's and for other two answers, really helped me to understand it more. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
xavierrajnaveen wrote: |
Thank you very much for the Sort JCL's and for other two answers, really helped me to understand it more. |
You are welcome. I did not answer 4th question because you've not repiled anything to what GuyC has said. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
Quote: |
he refused that way. |
usual approach of stupid interviewers
if the reply is reasonable the interviewer should provide more details about the requirement and surroundings ( in this case number of records in both datasets )
or expalin why he would not accept the answer
if for example one of the datasets would contain only a few ( very very very few ) records
then building an incore table and checking each key against the table could be a reasonable even if not expandable approach |
|
Back to top |
|
|
xknight
Active User
Joined: 22 Jan 2008 Posts: 117 Location: Liberty city
|
|
|
|
Quote: |
I did not answer 4th question because you've not repiled anything to what GuyC has said. |
I couldnt answer as i am not sure of that, but i have an assumption, is it possible to fetch the last row only at -811 condition?
Do the values retain in SQL statement (Last row) ?
Please suggest |
|
Back to top |
|
|
xknight
Active User
Joined: 22 Jan 2008 Posts: 117 Location: Liberty city
|
|
|
|
Quote: |
if the reply is reasonable the interviewer should provide more details about the requirement and surroundings ( in this case number of records in both datasets ) |
Interviewer always specified "Millions of Millions of Records" and his intention was not to reveal answer at any situation, i tried with all the perspective, but it dint worked |
|
Back to top |
|
|
nareshkp
New User
Joined: 26 Sep 2007 Posts: 28 Location: Bangalore
|
|
|
|
Quote: |
4) Using Select (Singleton) we are retrieving from DB, but the particular selects multiple record (say two records), how we shall fetch the second record alone ? |
You can fetch a particular record from result table directly. Check LAST / ABSOLUTE / RELATIVE parameters of FETCH statement.
LAST : Positions the cursor on the last row of the result table, and returns data if a target is specified.
ABSOLUTE : host-variable or integer-constant is assigned to an integral value k. If k=0, the cursor is positioned before the first row of the result table. Otherwise, ABSOLUTE positions the cursor to row k of the result table if k>0, or to k rows from the bottom of the table if k<0. For example, "ABSOLUTE -1" is the same as "LAST".
RELATIVE : Positions the cursor to the row in the result table that is either k rows after the current row if k>0, or ABS(k) rows before the current row if k<0. For example, "RELATIVE -1" is the same as "PRIOR". If k=0, the position of the cursor does not change (that is, "RELATIVE 0" is the same as "CURRENT"). |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
nareshkp,
thx for cut&pasting the manual on Fetch Cursor options.
but what has that to do with a Singleton Select?
it may be that the interviewer wanted to know if the interviewee knew that from db2 vsn7, a Singleton Select receiving an -811 SQLCODE does not have any returned values for the columns.
what may have been more informative is that you can avoid a -811 sqlcode for a single select when you use the FETCH FIRST ROW ONLY clause.
this coupled with an ORDER BY desc (db2 vsn 8 and above) would allow for 'the second' row to be selected. |
|
Back to top |
|
|
nareshkp
New User
Joined: 26 Sep 2007 Posts: 28 Location: Bangalore
|
|
|
|
GuyC wrote :
Quote: |
4) can't be answered because you have no idea which of the two returned rows is "the second". |
I think the interviewer's objective is to retrieve 2nd/Last row of the result table. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
nareshkp wrote: |
GuyC wrote :
Quote: |
4) can't be answered because you have no idea which of the two returned rows is "the second". |
I think the interviewer's objective is to retrieve 2nd/Last row of the result table. |
Nareshkp,
you keep talking but provide no information.
without an ORDER BY clause there is no first or second - as GuyC indicated.
and again, if the sql results in a -811 - There is no result table |
|
Back to top |
|
|
nareshkp
New User
Joined: 26 Sep 2007 Posts: 28 Location: Bangalore
|
|
|
|
Quote: |
but what has that to do with a Singleton Select? |
Apologies... I overlooked. This applies to my previous post as well. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
nareshkp,
no apologies necessary, from you.
but mine to you are required. I should have posted in a more civil manner. |
|
Back to top |
|
|
nareshkp
New User
Joined: 26 Sep 2007 Posts: 28 Location: Bangalore
|
|
|
|
Dick Brenholtz,
No problem.... I took it easy.
Thanks. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Okay, I just thought what kind of practical ground this DB2 question can have?
On zOS, can there be case when some application requires to get the "5th", "6th" or some other row from the reselutant-table? And most possibly, interviewer (The Geek God, The Inventor of the Questions!) had mentioned specifically about singlton select so posibility of -811 is already ignored. That means, You have a resultant-table with more than one values and now get some specific-row out of it (yeah OREDR BY is already in effect)? |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
DB2 Version 9.1 for z/OS - can ROWID help?
PS. Sorry, too many questions, I'm curious and badly overloaded with work today |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
rownumber() might
Code: |
select a.* from
(select rownumber() over (order by col1) as RN
,t.cols
from tab1
where ...) a
where a.RN = 4 |
|
|
Back to top |
|
|
|