Hi people!
I’ve been away for a while and haven’t been able to check my blog for some time. I’ve noticed a ton of comment and e-mails but it takes me a lot of time to answer all. I’ll get back to all my users soon! I hope you have been doing fine! Please, if you want faster help visit https://mtmcompetition.slack.com/.
Java SQL
IBM Master the Mainframe Part Three – Challenge #09
Get JCL necessary to complete challenge
Like most of the challenges we need to copy some data to one of our data sets. We should be familiar with this process by now.
First I’ll create a new member db2java inside my JCL data set. Then in the edit session I’ll COPY ‘zos.public.jcl(db2java)’ into it.
Somewhere in this code we need to change the output member to P3.OUTPUT(#09). I’ll enter the primary command save to save my changes and then I’ll sub ; =sd ; st to run the JCL. sub is actually shorter syntax for submit. This is a TIP for the lazy 😉
This is my output. It is identical to last challenge’s output.
Modify, compile, and execute Java program
We need to go back and edit the Java it executes to select only employeenumber from ibmuser.vphone where phonenumber is 2167. We also need to output Employee Number: instead of Phone:.
I seriously already forgot where the java files are located, I’m horrible. Let me visit my old blogs and see how it did that haha.
AHA! TSO OEDIT primary command to get to the file explorer.
Then I’ll edit db2java.java. My hex view is still on, I’ll issue the hex off primary command to disable it.
Somewhere on this screen there is a SQL statement and an output statement. Find where and make the necessary changes. As a non native English-speaker, I was surprised I wrote necessary correctly from the first go. Small victory!
Incase you’re wondering, yes some text is longer than the text that was already there. You’ll need to move some thing around. Don’t forget about spaces and closing statements like “;” “)” etc.
I can’t give the solution as IBM has told me not to, please refrain from posting solutions directly. Looking at you Mig 😉
By the way, hi Georges!
Enter the primary command save to save your changes. Then go back to the JCL to check if everything executed correctly.
I have some results! I’m not sure if that’s correct though. I could check using some SQL… buuuuuuuut I don’t have enough time.
hey Kevin you really help me a lot 🙂 Thanks for that you cleared my doubts
I have doubt, In the part 3 challenge 9. I follow all the steps but i got the desired o/p of Ch-9 in Ch- 8 and vice-versa . Is I executed the wrong command?
hii @kevin i really need your help please help me
Hi Vidit
Don’t forget there’s also a Slack channel that is out there to help you 🙂
You’re telling me your output of challenge 09 got into challenge 08’s output? Do challenge 08 again, then do 09 again. Be sure when doing 09 that you change the member in JCL(DB2JAVA) at line 000010 from #08 to #09.
Kind regards,
– Kevin
Sir, after fixing the program….submiting it….then also jcl(error)…..what to do….??
After doing everything … It shows error while submitting the changes… Please help Kevin😑
Hi Anonymous
I’m not able to help you if you don’t post the error you’re experiencing. Please read the error carefully and re-do the entire challenge.
Kind regards,
– Kevin
Sir i have made the necessary change from user id to statement change phonenumber to employeenumber but still my output is stuck on phone number even though i compiled still output is not chnging to employeenumber.
Yeah same with me too. I tried several times but I am getting an error while submitting the changes.
Sir …can you please give me a proper description about each line that how to modifiy that java code … I have changed what i understood but it shows error after submitting the code… Please tell me…which line i should change and what to write in the code.
I finally figure out how to do challenge 9.
I just need to look back at challenge 8 for a command.
The command to run on db2java.
I was overthinking on this challenge.
I only able to get one employee number.
Do I have to edit the SQL file?
Do I have to switch text from line 41 and 42?
I put a plus sign of the number.
Do I put different symbol?
Hi Mig
No you don’t have to edit the SQL file, all the changes are made in the Java file. There’s no need to switch text at said lines. You just need to change the SQL query.
I’m not sure if you’re familiar with string concatenation in Java. But the plus sign is used to concatenate strings. “select employeenumber from ibmuser.vphone ” + “where x = ‘y'”;
Please take note of the extra space I have put after vphone. Also I close the quotes like they did.
Kind regards,
-Kevin
Hello,
thank you again for this tutorial! Just a little thing, output format should be like this: “the employeenumber string preceeded by Employee Number:” and you wrote “ubmuser.vphone”.
Akos
Hi Akos
This is embarassing, thanks for pointing that out!
Kind regards,
-Kevin
Hi Akos,
I have a problem in Part 02 Challenge 12, I followed the the instructions as said but I didn’t get the needed result please help me how to fix this problem, because I stuck in it.
Look at the hexadecimal carefully.
Make sure you change the number correctly.
And line 1: type C12.
When I execute the first time, my 09 output is blank. This is strange!!
In this case please check line 52 and 56 in your file.
Shoot, I’m getting put on time out.
I could sometimes have loose lips.
I will try my best to have tight lips.
Thanks for all your help on the MTM!
Hi Kevin again!
The right slack account for 2019 is mtmcompetition.slack.com not https://masterthemainframe18.slack.com
The last one is for last year competition but is not existing now. Be careful !
Kind regards
Georges
Hi Again Georges
In my case that URL redirects to https://mtmcompetition.slack.com/, which is the correct one. But I have updated it anyway, thanks for helping out!
Kind regards,
– Kevin
Hi Kevin,
I see my name here, is me 😉 ?
Yes SQL could be very interesting, because all the info in this world is in a database!
I think in your own mainframe (Hercules) according to MOSHIX you don’t have access to DB2? That is sad! 🙁
Anyway good answer again! You are GOOD!
Kind regards
Georges
I am not able to find the necessary changes.
Can you please give me the clue if you can’t share the answer as it is.
Hi Bikash
You need to change the lines 41-42, 52 and 56. Also make sure your login credentials are correct.
Kind regards,
-Kevin
I am not getting what to change..??
Hi Rishika
The changes needed are not visible on my screenshot if that causes the confusion. You need to change the SQL statement and the print statement at said lines.
Kind regards,
Rishika
pls i am always still getting the same output with the phone: **** after editing the lines needed pls were else can the problem be
Hi Anonymous
Did you compile db2java after editing it?
Kind regards,
– Kevin
Hello Kevin,
Quick question, on line 52 and 56 when I changed the string variable “ephone” to something else and run the JCL and I received an error and when I leave it alone the program run successful. Do you know why I keep receiving an error when I change the string variable ? I already complete the challenge, I am just curious on why.
Thanks,
Nick