What Serves SQL statements on AS/400?

[David Abramowitz]

You do need an ODBC driver to use ODBC. There is an SQL server as part of TCP/IP utilities. This is started from operations navigator. It is free as past of the TCP/IP utilities. AFAIK this is used to process OLE/ADO requests. OLE/ADO is far more efficient than ODBC, and does not require the ODBC driver. Dave

[gary.shipp@s3t.co.uk]

I've got an ODBC driver installed on the PC that's running the requests. I don't think OLE/ADO is an option, because the I think the application only allows me setup ODBC connections. Getting back to the server, can I check from command line if the server job is running? (I presume there is just an active job)

[David Abramowitz]

You could use WRKACTJOB. I'd prefer WRKSBSJOB due to the use of resources. Dave

[Guest.Visitor]

S K Shipp wrote: > Getting back to the server, can I check from command line if the > server job is running? (I presume there is just an active job) ODBC requests (and probably OLE/DB) are served by QZDASOINIT jobs. I believe that you'll find at least one waiting for work in your QServer subsystem. Bill

[gary.shipp@s3t.co.uk]

I'm still experiencing problems with this issue at the moment and I'm not sure if ODBC is a symptom or just a side issue. Perhaps it would help if I expand on the problem a bit. We are currently implementing some third party software that uses ODBC to connect to Data sources using ODBC. It allows you to specify SQL statements that are then run against the database and result sets returned. So far, as well as various PC databases, we have been able to succesfully connect to and run SQL against our own iSeries (V5R2) using iSeries ODBC driver version 9. We can also connect enquire against a 2nd machine (V5R1) again using iSeries ODBC driver version 9. However, two other machines, both running V5R1, give various errors when attempting to execute SQL statements. The reason for my original reference to an SQL server is that the first machine we encountered that does not execute the SQL statements gives an "option not installed" error when I attempt to use STRSQL, which made me wonder if the SQL Server had to be "installed" in some way. Could there be some Licensed program that needs to be installed on the iSeries? The only difference that occurs to me when comparing the profiles of some of the machines is that we have installed the System Openess Includes (5722SS1) on our box, which I note is not on at least one of the machines that's not working (I can't connect to the other at present to confirm whether or not this is true in both cases). TIA

[David Abramowitz]

The following is taken from the STRTCPSVR help screen:<tt> EDRSQL The Extended Dynamic Remote SQL (EDRSQL) server is started. Subsequent use of the STRTCPSVR SERVER(*EDRSQL) command results in a diagnostic message if the EDRSQL server has already been started. </tt> Dave

[Guest.Visitor]

S K Shipp wrote: > So far, as well as various PC databases, we have been able to > succesfully connect to and run SQL against our own iSeries (V5R2) > using iSeries ODBC driver version 9. We can also connect enquire > against a 2nd machine (V5R1) again using iSeries ODBC driver version > 9. However, two other machines, both running V5R1, give various > errors when attempting to execute SQL statements. Try running a CWBPing against the failing machines (it's in Client Access). Then return with the specific error messages. Bill

[gary.shipp@s3t.co.uk]

Cheers for that Bill, I have run the CWBPing against 4 machines, two that are working (V5R2 and V5R1) and two that are not (both V5R1). Both the machines that work pass all the tests successfully. However, the other two both return an error at the same point. The test being performed is against application Management central and the received error codes are "CWBC01003 - Sockets error, function connect() returned 10061", "CWBC01049 - The iSeries server application (Management Central) is not started", and "CWBC01008 - Unable to connect to server application Management Central, returned 10061". There is also a warning that connection was verified but warnings found. I was under the impression though that Management Central has something to do with iSeries Navigator. Thanks for the help so far.

[Guest.Visitor]

S K Shipp wrote: > Both the machines that work pass all the tests successfully. However, > the other two both return an error at the same point. The test being > performed is against application Management central and the received > error codes are "CWBC01003 - Sockets error, function connect() > returned 10061", "CWBC01049 - The iSeries server application > (Management Central) is not started", and "CWBC01008 - Unable to > connect to server application Management Central, returned 10061". > There is also a warning that connection was verified but warnings > found. The help text for those error messages should give you a great starting point. Look for file CWBC0ERR.HLP. > I was under the impression though that Management Central has > something to do with iSeries Navigator. It might just be a symptom of a deeper problem. I'd start with the help text for CWBC01003, it's pretty straightforward. Bill

[gary.shipp@s3t.co.uk]

Bill, That certainly helped identify another factor. I've now discovered (through a process of elimination on one of the working boxes) that I need to ensure the *DATABASE host server is running in order for the connection to work. However, I continue to have the original problem on the machines that fail to work, so there is something else I am missing. I don't know if this is any help, but my application seems to be failing when it attempts to access the list of tables available for query. Thanks again for the suggestions so far.

[Guest.Visitor]

S K Shipp wrote: > That certainly helped identify another factor. I've now discovered > (through a process of elimination on one of the working boxes) that I > need to ensure the *DATABASE host server is running in order for the > connection to work. Yup, that's good news. > However, I continue to have the original problem on the machines that > fail to work, so there is something else I am missing. Ok, let's start again - what's the original problem? StrSQL? If so, you have to have the licensed program SQL400 to run that command. > I don't know if this is any help, but my application seems to be > failing when it attempts to access the list of tables available for > query. Via ODBC? This is just a wild guess, on a dedicated machine run RclStg Select(*dbxref) . bill

[gary.shipp@s3t.co.uk]

Bill, Maybe the SQL server question in the first place is a red herring (god knows I've found enough while trying to resolve this problem). It was just I was guessing it was the culprit as I could see the application was trying to run SQL statements, and as I knew the machine in question does not have a license for STRSQL, I wondered if this was preventing the statements being executed. Back to basics again then, this third party PC application we're trying to implement for a potential client uses Client Access ODBC to connect to the AS/400. When enquiring against our box, and several other clients machines, result sets are returned with no problem. However, our potential clients machine fails to pass a list of available tables/files back to the application. Indeed, the application fails completely and crashes. I don't know if there may be some configuration that hasn't been performed on the AS/400, or even if there are some security issues that need to be resolved. Sorry for the hazy information, but this whole exercise has been a steep learning curve for me. As I've said before, thanks for the information and help you have provided so far.

[Guest.Visitor]

S K Shipp wrote: > Back to basics again then, this third party PC application we're > trying to implement for a potential client uses Client Access ODBC to > connect to the AS/400. When enquiring against our box, and several > other clients machines, result sets are returned with no problem. > However, our potential clients machine fails to pass a list of > available tables/files back to the application. Indeed, the > application fails completely and crashes. Have you looked in the QEzJobLog out queue for a job log? I would think that the machine would generate one with these symptoms. If not, or the job log doesn't lend any useful information, I'd try tracing the statements. This is an option in the Control Panel's ODBC applet. Tell it to trace the statements specifying a file name, then after the crash, stop tracing and see if the trace file lends anything useful. One caveat: ODBC trace can sometimes be a hit and miss thing, for some reason it will sometimes not act logically. > Sorry for the hazy information, but this whole exercise has been a > steep learning curve for me. As I've said before, thanks for the > information and help you have provided so far. You're very welcome.

[gary.shipp@s3t.co.uk]

Obtaining any kind of error information is proving extremely difficult. There does not appear to be any joblogs generated, and I had already tried the ODBC trace without any success. The usual suspects in terms of message queues hold no obvious clues either The only thing I am getting out of the system is a Dr Watson report, which is obviously too long to include here in its entirity. However, one thing I've noticed within it is the following extract; "Obtaining any kind of error information is proving extremely difficult. There does not appear to be any joblogs generated, and I had already tried the ODBC trace without any success. The usual suspects in terms of message queues hold no obvious clues either The only thing I am getting out of the system is a Dr Watson report, which is obviously too long to include here in its entirity. However, one thing I've noticed within it is the following extract; "Obtaining any kind of error information is proving extremely difficult. There does not appear to be any joblogs generated, and I had already tried the ODBC trace without any success. The usual suspects in terms of message queues hold no obvious clues either The only thing I am getting out of the system is a Dr Watson report, which is obviously too long to include here in its entirity. However, one thing I've noticed within it is the following extract; " "Obtaining any kind of error information is proving extremely difficult. There does not appear to be any joblogs generated, and I had already tried the ODBC trace without any success. The usual suspects in terms of message queues hold no obvious clues either The only thing I am getting out of the system is a Dr Watson report, which is obviously too long to include here in its entirity. However, one thing I've noticed within it is the following extract; "Application exception occurred: App: (pid=1940) When: 7/11/2003 @ 15:27:54.929 Exception number: c0000005 (access violation)" Program Id 1940 does tally to the application which is falling over. Is this access violation a clue?

[gary.shipp@s3t.co.uk]

Two more bits of information that might aid our diagnosis. 1) I have just tried performing the connection with my own PC which is running Windows 98. This also displays the following two errors when the connection fails. "Error reading table list from ODBC driver - ODBC error -1" after clicking OK I then receive "Failed to establish Database connection". 2) The program is using the following connection string to connect to the AS/400. Does this contain any obvious discrepencies? (System and user details have obviously been changed to protect the guilty!) "DRIVER=iSeries Access ODBC driver;SYSTEM=AS400;UID=USER;PWD=PWD;DBQ=QGPL;DFTP KGLIB=QGPL;XLATEDLL=;LANGUAGEID=ENU;SORTTABLE=;TRA CEFILENAME=C:My DocumentsIBMClient AccessServiceTrace Files;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;QAQQINILIB=;DATABASE=;SQD IAGCODE=;DESC=WRUM;DEBUG=64;"

[Guest.Visitor]

S K Shipp wrote: > Two more bits of information that might aid our diagnosis. > > 1) I have just tried performing the connection with my own PC which > is running Windows 98. This also displays the following two errors > when the connection fails. "Error reading table list from ODBC driver > - ODBC error -1" > after clicking OK I then receive > "Failed to establish Database connection". > > 2) The program is using the following connection string to connect to > the AS/400. Does this contain any obvious discrepencies? (System and > user details have obviously been changed to protect the guilty!) Ok, let's be double sure the host machine is fine: Does CWBPing come back clean? If so, do you have another program that can access data on this 400 via ODBC, like Excel? If so, install MS Query, if not already installed, create an ODBC Name Source, then attempt to run an external data query in Excel (turn off the query wizard and auto-requery options). If that works, we can then compare the connection strings - if not, then we'll try debugging the Excel errors. Bill

[gary.shipp@s3t.co.uk]

Hi Bill, I've just had an infuriating couple of hours. I've tried running CWBPing against our client's AS/400 again as you suggested, and I'm continuing to receive the same errors as before, namely the test being performed against Application Management Central generating the CWBC01003, CWBC01049 and CWBC01008 errors. I then went on to to try to run MS Query against the database, which produces a couple of new sql errors codes, which I have investigated and acted upon, but with no success so far. The first error message I received was when trying to return the results set through MS Query into Excel, "Token * not valid". When I searched for the associated SQL0104 message on the net, I found various suggestions that I ADDRDBDIRE if no Relational Database entries are not already set up. This I did, unfortunately to no avail. The second error was again when trying to return the results set through MS Query into Excel, "*N" member is not allowed!". Again, when I searched for the associated SQL0113 message on the net, I found various suggestions of ODBC driver adjustments that could be made, which again I tried, but to no avail. If I manually enter the SQL statement into the freeform function within MS Query e.g select * from filename, a result set is returned to the spreadsheet no problem. Any more ideas? Gary

[Guest.Visitor]

S K Shipp wrote: > I've tried running CWBPing against our client's AS/400 again as you > suggested, and I'm continuing to receive the same errors as before, > namely the test being performed against Application Management > Central generating the CWBC01003, CWBC01049 and CWBC01008 errors. Gary, I think the prudent thing to do is to get these cleared up before attempting anything else. The help text for the CWBC01008 is pretty comprehensive. The help file is CWBC0ERR.HLP . > The first error message I received was when trying to return the > results set through MS Query into Excel, "Token * not valid". When I > searched for the associated SQL0104 message on the net, I found > various suggestions that I ADDRDBDIRE if no Relational Database > entries are not already set up. This I did, unfortunately to no > avail. This is a common problem and that's the correct solution. > If I manually enter the SQL statement into the freeform function > within MS Query e.g select * from filename, a result set is returned > to the spreadsheet no problem. Interesting, sounds like the 400 is capable of returning table data but it can't present the file and field picker data. Bill

[David Abramowitz]

Do you have Client Access and the ODBC driver configured to automatically translate CCSID 65535? Dave

[gary.shipp@s3t.co.uk]

Morning Bill, Am I correct in thinking that management central first appeared in 5r2, wheras the problem As/400 is on 5R1, in which case this may be a red herring again, my thinking being that CWBPing would be testing for a service that is not actually available and hence the error. To eradicate this possibility anyway, I've also done a STRHOSTSVR *ALL, and the as-mgtc port still does not appear in my list of TCP/IP Connections, and all other routing entries already exist, indicating the host server daemon's are already active. What are the implications of your comment about not being able to present the file and field picker data? Also, I've already tried experimenting with the 65535 option as suggested by David but with no joy. Gary

[gary.shipp@s3t.co.uk]

Bill, Don't know if they're any help, but I've just noticed QPSRVTRC spool files against my user profile that appear to be generated around the times when I've been testing these transfers. The reports range from 60 pages upwards, but I've noticed various sections titled "Message Handler Exception Handler" that contain references to the following programs and procedures, QQQDBLOG, QSQOPEN, QQQSRVI1 (Procedure GETMBRPTR) and QSQCALL (Procedure CLEAR RESULT SET SPACE). As I say, I've haven't noticed these reports before, so will try and analyse them today, however, any pointers on what I might be looking for would be appreciated. Thanks again Gary

[Guest.Visitor]

S K Shipp wrote: > Morning Bill, > > Am I correct in thinking that management central first appeared in > 5r2, Nope, I remember MC on my previous V4R5 installation. > eradicate this possibility anyway, I've also done a STRHOSTSVR *ALL, > and the as-mgtc port still does not appear in my list of TCP/IP > Connections, and all other routing entries already exist, indicating > the host server daemon's are already active. I know it's frustrating, but until CWBPing comes back clean you might be spinning your wheels. > What are the implications of your comment about not being able to > present the file and field picker data? I just commented on the fact that a Select * query worked, but you could never get the file and field pickers to work. Theoretically, if the SQL strings were pre-written, I'd think it should work. Bill

[Guest.Visitor]

S K Shipp wrote: > The reports range from 60 pages upwards, but I've noticed various > sections titled "Message Handler Exception Handler" that contain > references to the following programs and procedures, QQQDBLOG, > QSQOPEN, QQQSRVI1 (Procedure GETMBRPTR) and QSQCALL (Procedure CLEAR > RESULT SET SPACE). I think it's time to get IBM Software Support involved. Did you ever run the RclStg command with the DBXREF option? I'm also curious, does the machine have Query/400 installed? If so, does it present files and fields for selection when you create a query? Bill

[gary.shipp@s3t.co.uk]

Bill, Thanks ever so much for your help on all this, I really appreciate it. As you say, it is very frustrating, but it has exposed me to loads of issues that haven't cropped up before so it has certainly been good experience. I'm going to leave matters over the weekend now until Monday, but I will probably get in touch with IBM then. Hopefully they'll help me sort this out and I'll get back to the forum with the resolution. Anyway thanks again. Gary

[gary.shipp@s3t.co.uk]

My company is running a PC application that uses ODBC to connect to data sources including AS/400, and runs SQL statements against the data source. What exactly would the AS/400 need in order to perform SQL requests placed in this manner? The reason I ask is that we do not licensed program 5722ST1 DB2 Query Mgr and SQL DevKit installed on the box, and it would make sense to me that if we don't have an SQL server installed, nothing is able to translate the requests. Do we require this option?

[gary.shipp@s3t.co.uk]

Winner!! I've just had a ten minute phone call with IBM Assist, described my problem, including the information about SQL0113 from within the joblogs, and from that they made one suggestion (Have you an entry for *LOCAL in WRKRDBDIRE?) which I explained I'd already found documented and tried, to which they replied "and you have restarted the PJ jobs?" to which I replied "No, how?" A quick lesson then followed on the administration of the PJ jobs associated with ODBC connections (Check that no QZDASOINIT jobs exist in QUSRWRK, then ENDPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT) OPTION(*IMMED) followed by STRPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT) ). Try my connection again and Wahey! it worked! They also suggested we do the same with two other PJ jobs, QZDAINIT and QZDASSINIT. What I was pleased to see was that we had been working along the right lines with all this. Thanks once again to everybody, especially Bill, for all the help and time that has been provided on this problem. I really appreciate it.

[Guest.Visitor]

S K Shipp wrote: > Winner!! Congratulations. bill