Gary Joehlin

Greenhorn
+ Follow
since Sep 18, 2003
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Gary Joehlin

The best book I've found for JDBC is "Java Database Programming Bible", by John O'Donahue. ISBN 0-7645-4924-3 - i get no royalties, but I've been where you are now. It also has some design and basic concepts that sounds like you need.
First you need to answer a few questions:
But first a statement of interest:
COBOL and ISAM do not own each other, one is a programming language and the other is a file access method, they are independent items.
Secondly, ISAM is considered obsolete, especially if you're talking about the mainframe environment, do you really mean VSAM, it's newer cousin?
If indeed it's VSAM, then what sub-type of VSAM file? (KSDS, ESDS, RRDS or LDS) You can find this out with the VSAM utility, LISTCAT, and some help of a system or application programmer.
Just curious, because I haven't heard of an ISAM file being used in over a decade.
You may wish to access it via a stored procedure, if it's VSAM. ISAM, I don't believe is supported any longer.
Regards,
Gary Joehlin
colo spgs.
The best book I've found for JDBC is "Java Database Programming Bible", by John O'Donahue. ISBN 0-7645-4924-3 - i get no royalties, but I've been where you are now.
Fellow Moose and Ranchers:
I have a 2-tier, client (PC win) - Server (z/OS MVS DB2) environment that I'm trying to write an application (it compiles cleanly, so I'm not asking for someone to code this from scratch, I've done that, however, to give fair warning, I'm a newbie at it.) that is a JAVA application using JDBC to do a simple query against a server database.
I'm looking for:
1) a definitive set of definitions for the PC (DB2 Connect) that will properly get me to the mainframe. I believe that I have them, however, I get so many conflicting answers from other sources.
2) a definitive set of definitions within the program for the two critical pieces of: url and connection parameters. I believe that I have them, however, I get so many conflicting answers from other souces on various combinations of these.
I've tried the different options such as .app. and .net. in the driver parm. The .app. says it cannot find the driver, at least the .net. is knocking on the door.
I'm quite certain it's the DB2 connect definition setup, that even the IBM documentation is clearly unclear, yet they have what appears to be a great 'cheat sheet' document and I've followed it.
Side question: Does anyone know about FixPak 4, for DB2 UDB Version 8.1? There was a reference on the IBM site to that FP, but looking for it, I cannot find it, has anyone else?
Regards,
Gary J
COLO SPGS
MS-Access, is rather limited in its ability to do certain things, however to its credit you can do some things with it by using the wizards to design forms (the output from a query), desing queries (although i find that more confusing than writing the SQL manually) and you have tools (wizards) that can help you load, very slowly, record by record manually, the database.
Yes, you could write application code to interface with that, however, you don't have the time, dude.
This quickie plan will allow you to have a single user the one that the database is located on. And for heaven's sake, make back ups of all your work, even the data. ANd when it's 'production', make sure that they, or you, back up their data, on a regular basis, dude.
Don't code anything yet!!! (Now is the time to plan and understand your data) DESIGN FIRST BEFORE CODING OTHERWISE, YOU"LL BE CODING TO CORRECT A Y 10K problem.
I.Design the database, if it isn't already:
What elements do you need? (What fields of information) (One important thing to keep in mind: Arrays are NOT our friend in a relational database, so don't design them in put them in another table and have a key that ties the two tables together so that you can find the info between them when needed) This also means, no group occurs "X" number of times either, it's the same (bad) design.
II. associate these elements into their respective tables (and give these tables a short but meaningful name. Oh, don't put everything in a single table, depending on the number of elements, you may want several tables. PUt the data in their respective tables, ie. MEMBERS (only membership information, name, addresses, phone numbers, membership number, expiration of membership, etc.), FACILITY (name of facility, days that it's available (to make it simple, plan 7 pairs of time (open / close) one for each day of the week.), a key (to identify the facility- simple numeric is ok), location (address or other identifiers), telephone number, cost, etc., SCHEDULE (this table is the cross between the MEMBERS and FACILITY table), it will contain entries when a member schedules a facility, but only if the request is within the date/time when the facility is available.
Other issues to consider: What is the maximum number of users to a Facility at any given time? (that's a design consideration), Delete and reschedule, Blocking holidays, etc.
== review design with user ==
II. Determine what functions you want to provide and what reports and screens you want to have.
II a. Do all the functions and reports and screens have all the data elements in part I defined or are any missing? Then, put them in the right place.
== review with user ==
III. Use the MS-Access wizard to define the tables.
User proper data types (use 'date' for things that are dates, don't use char types in any realtional database
IV. Use the wizard to create the relationships and reports and screens.
== review with user ==
V. Load starting data into the tables.
VI. MAKE A BACK UP OF DATA AND DEFINITIONS.
== review with user ==
VII. TEST and TRAIN USER
VIII. MAKE BACK UPs a regular thing.
Hope this helps.
Gary Joehlin
What is the target date to have the project finished?
You can't have a shell of a database, you must design it to account for all the user requirements, now, otherwise you'll have a design and implementation mess in the future.
SQL is a language, not a database, to extract and work with data in a realtional database.
What is the name of the database that you will be using, windows is not a database. Are you saying you are going to use MS-Access? Or some other, be specific.
Regards,
Gary J.
When did you commit to having it done?
Do you have a database with information already? (Or, does it need to be developed/designed?) What database are you going to use?
What platform are you going to run this application on?
What tools do you have to develop the application code?
Let's start there.
Regards,
Gary Joehlin
suhail,
I can only give you a partial answer, which is based on the way that SQL works, you'll have to get the rest of the answer on how to do this in JAVA, from another source. However, I can at least give you enough information to help compose a question to that other person. I also hope that I'm not insulting your intelligence with this reply, please forgive me if I am over simplifying.
First, when using the LIKE predicate, one has to be sure that you know that this is a pattern search process and not looking for and exact match (equality), which is a different predicate. Therefore, the pattern being used should contain either the underscore "_" which means look for any single character in that position, or use the percent sign "%", which means look for any number of characters (even zero characters) of any character in that position coded in the pattern.
Additionally, be aware of the fact that the non-pattern characters included in the string ARE case sensetive.
Where lastname LIKE '%SON'.
Will find: JOHNSON, ANDERSON, WESSON, and so on.
It will NOT find: johnson, anderson, ... and so on because of the case.
Secondly, some DBMS (Data Base Management Systems) don't use the % or _, but other characters, you'll have to check their doc for confirmation.
Thirdly, the test for whether or not the query finds any results will depend on the implementation of how you search. When I code non-Java programs (because I'm going through a great learning curve in JAVA and it's quite new to me), in other languages and platforms, I test for the results to come back from the server in either SQLCODE or SQLSTATE.
SQLCODE is a large integer, SQLSTATE is a character (5) field, both return a value that describes what happened in the last SQL statement you executed.
SQLCODE = 0 -- everything worked ok
SQLCODE < 0 -- an error, probably with some diagnostics
SQLCODE > 0, but not +100 - an SQL WARNING (it worked, but it wants to tell you about it with a qualification, usually, not to worry)
SQLCODE +100 - no more data to be returned (like end of file condition) or no data found in the first place.
SQLSTATE '00xxx' -- Unqualified success.
SQLSTATE '01xxx' -- Warning
SQLSTATE '02xxx' -- No data returned,
Then there are bunches of other prefix codes that imply all kinds of categories of errors.
I'd recommend test for the code then determine, in the logic of your program to issue the prompt... now, I return you back to your JAVA code issue.
Regards,
Gary J.
Colo Spgs, CO
dibs dibley,
The answer given in the prior response is correct for most SQL syntax. Some implementations of the LIKE predicate (that's what the father of relational theory calls them. Just remember from high school English, when diagramming a sentence? The predicate is the 'action' taken upon the subject. And that's what most documentation calls that part of an SQL statement, the predicate) use a different 'wild-card' character.'
I've seen some use the asterisk '*', it's not an asterick, Rick wouldn't like that. Just remember from the famous speach, "I regret that i have one as-te-risk for my country". sorry for the digression, but that helps me to remember the correct pronunciation.
Some SQL implementations use one or the other, but rarely both.
If you find that the "%"- which means any character AND any number of characters in this position, returns no rows, then try the "*" in its exact place.
Keep in mind that this is not an "=" predicate, but a LIKE predicate, they are not interchangeable when looking for "patterns". The "=" predicate, of course, will check for an exact (not a pattern) match.
FYI, there are 7 predicate types in SQL, these are two of them
Best of luck and hope this expands your understanding of fundamentals of the power of SQL.
REgards,
Gary Joehlin
Colorado Springs, CO
Dear followers (the two of you, so far) of this link.
Here's an irony I've discovered, quite by accident (you'd be surprised on how many frivilous details that stick in your brain when you're searching for something, but the peripheral items keep calling out to you) in my search for my holy graile.
It seems that a simple little innocuous thing, like a software firewall, can wreck havoc on trying to debug a pc-based JDBC request to an IBM (z/OS aka MVS) DB2 server, get in the way.
If you've been following my post(s), the past one suggests that the first scenario is what I have determined, by a 'filtered consensus' to be the most likely candidate to make things happen. But, alas, it was not successful, then I looked at my software firewall and it said that "java.exe" was BLOCKED from sending and receiving any communications. So, I quickly changed it to allow outbound only, and the error message changed to one that gives me belief that my server finally is being contacted by my request.
I went to the server, because I have super authority to query (DDF on the MVS spool, for those that are curious) and found that I have indeed piqued its curiosity. However, according to the cryptic message I found there, regarding my attempt to talk to it, the format of my request was 'not proper'.
Now, I move forward to find the answer to other obscure TCP/IP error codes on a UNIX-IBM box, we mainframers have UNIX and MVS (the traditional op/sys for IBM mainframes) combined together under a single skin and today we call that op/sys z/OS.
So, I still am looking for confirmation:
1) of my JDBC parameters to be confirmed as proper
2) the definition parameters in the PC interface software called DB2 Connect (there are several ways to implement them, through a wizard-- which doesn't give access to all the parameter options, or through the Command Line Processor (CLP) supplied with DB2 UDB (the PC/Windows version) which I found in an obscure reference on the web.
3) the definition and parameters on the Unix system Server and TCP/IP on the IBM mainframe.
Not asking for much, am I? lol
I hope that some one can help if not in all categories, the at least one.
Your kindness is very much appreciated.
Regards,
Gary joehlin
Colo Spgs, CO
One thing that tripped me up was that unbeknownst to me, this error is also caused by a firewall not letting the connection get through. Check if a new software firewall has been installed and/or that if it has 'always' been there, that parameters haven't changed pertaining to communications from your 'application' being BLOCKED, in both directions.
Along the same line as the classpath issue, that is another item that should be on your check list, did it get updated in one place but not the other?
Another question to ask is: "Did the code get changed?" Really, then analyze any refrences to the driver and its class.
I hope these few items will give you some insight on what I have, as a greenhorn, found in my frustrations.
Regards,
Gary J.
COS
Gregg:
Sorry about the confusion.
After further examination, from numerous conflicting sources, I found the consensus to be that COM should be capitalized. Additionally, I have found that the format of the 'url' string to be in great flux and variation. I have reasoned, that many offers are for different client-platforms. Which is why I think that the combination of: 1) the url, 2) the Class.forName and 3) javac ... -classpath and java -cp
are all suspect, which means, I haven't got solid evidence on the proper combination. Following are my tries and results:
Notes:
I am able to PING the IP address and it comes back ok.
I am able to use the DB2 UDB (PC-based) Configuration Assistant to successfully connect to and query the server database. Therefore, I know that a) the connection between my PC and the server (a z/OS platform) works and b) that the server Database system is there and is replying to my simple query. "SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1;" and it returns, as expected, the current date.
===============================================================
Setup:
javac -g d:\javajdbc\DisplayQueryResults.java -classpath d:\db2v813base\java\db2java.zip;d:\db2v813base\java\db2jcc.jar
java -cp d:\javajdbc;d:\db2v813base\java\db2java.zip;d:\db2v813base\java\db2jcc.jar DisplayQueryResults
================================================================
Secnario 1:
String url = "jdbc b2://xx.xxx.xx.xxx:446/D71A";
Class.forName("COM.ibm.db2.jdbc.net.DB2Driver").newInstance();
Results:
COM.ibm.db2.jdbc.DB2Exception: [IBM] {JDBC Driver] CLI0616E Error opening socket. SQLSTATE=08S01
[trace omitted]
==================================================================
Secnario 2: (Changed case of COM to com)
String url = "jdbc b2://xx.xxx.xx.xxx:446/D71A";
Class.forName("com.ibm.db2.jdbc.net.DB2Driver").newInstance();
Results:
java.lang.ClassNotFoundException: com.ibm.db2.jdbc.net.DB2Driver
[trace omitted]
====================================================================
Secnario 3: Changed format of url string. COM back to all caps.
String url = "jdbc b2 71A";
Class.forName("COM.ibm.db2.jdbc.net.DB2Driver").newInstance();
Results:
java.sql.SQLException: No suitable driver
[trace omitted]
====================================================================
Secnario 4: Changed "net" to "app" in Class.forName. All other parms back to values in Scenario 1.
String url = "jdbc b2://xx.xxx.xx.xx:446/D71A";
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
Results: (Same as Scenario 3)
java.sql.SQLException: No suitable driver
[trace omitted]
======================================================
This leads me to believe that there is something within the -classpath/-cp parameters that needs to be changed/added (probably changed). I'm more than glad to change it, however, what is it that I'm looking for to be included during the javac/java processes, then I can search for them in some .jar or .zip file, if i knew for sure.
Your continued kindness is very much appreciated.
Regards,
Gary Joehlin
COS
Gary Marten,
Sorry I spelled your name incorrectly.
Just wanted to add another piece of information.
When I searched for the runtime file, it was not part of the DB2 UDB distribution, but part of VAJ (VisualAge for JAVA), which does support SQLJ and JDBC.
I am developing a standalone JAVA application that needs to be portable, so that's why I have not employed SQLJ.
Thanks again,
Gary J.
COS
Gary martin:
I think that maybe you missed a key point or maybe I missed that point, so if you're familiar with this please let me know.
It is my observation that the ~runtime.zip is for SQLJ, not JDBC. Because, when I search for the "file name contains" runtime.zip on my machine, it returns sqlj-runtime.zip listings.
I'm not using sqlj, that's for another time and I find it tempting to go there, however, I need to resolve this JDBC issue first.
If you have other ideas, please continue to pass them along.
Regards,
Gary J.
Colo Spgs
Gregg,
You are correct! The driver name IS case sensitive. However, the documentation (the little that there is of it) and a recompile and execute when I changed to lowercase (com. yadda yadda...) give me a "failed to load driver" condition, so i put it back to upper case and am now back to "Unable to connect... Error opening socket", which gives me reason to believe that I'm closer to a solution with it in uppercase than lowercase.
Thanks dude, I appreciate the info!
Still looking for the solution. I know it has to exist, or is this a ruse by JAVA and IBM to keep us busy? lol