SQL/400 has been around for a while now, and really hasn't caught on with most AS/400 shops. The reason is basic. That is the *BASE features of the AS/400, i-series, etc. <u>does <b>not</b> include SQL</u>! <p>DDS, and OPNQRYF are part of the base OS, and until SQL becomes as accessible, it will not be used. <p>Dave

I would just like to add my 2 cents worth. I am a 20+ year veteran of the programming world using COBOL, RPG, BASIC, SQL, on different machines from UNIX/LINUX, HP9000, IBM370, IBM43XX, etc. I have tried most tools available, and I have to say that the only thing I like about the AS400 is the ability to use ASC's SEQUEL. The AS400 is a stable machine, but very hard to get the information you need from, unless you have spent a long time with it. <p>I do admit I have only worked on the AS400 for the past 13 months, but without SQL I wouldn't be able to use my experience from the other environments... <p>Paul

What does come with the base operating system?<br>
- The ability to run sql scripts with RUNSQLSTM<br>
- The ability to run sql scripts with iSeries Navigator<br>
- The ability to create Query Management Queries with CRTQMQRY<br>
- The ability to run Query Management Queries with STRQMQRY<br>
- The ability to convert Query/400 queries into source with RTVQMQRY<br>
- The ability to set up referential integrity<br>
- The ability to use triggers<br>
- The ability to interactively run sql statements with iSeries Navigator<br>
- The ability to use sql in programs with the Call Level Interface (SQL-CLI). Albeit somewhat clunky.<br>
- The ability to graphically change your databases by adding/modifying/deleting fields by using iSeries Navigator<br>
- The ability to graphically see how one file is related to another by seeing the referential constraints by using iSeries Navigator.<br> <p>What does come with 57##ST1?<br>
- The 5250 prompter for SQL - STRSQL. Many statements cannot be prompted, yet still run, there.<br>
- The SQL precompiler for many languages. Much easier to understand than SQL-CLI. However often languishes behind the compiler release.<br>
- The prompter to generate Query Management Queries - STRQM. A competitive product to Query/400.<br>

I think Paul speaks volumes with this simple statement. While many experienced iSeries developers may feel smug about their knowledge of DDS, OPNQRYF, etc. I am finding that the new people here faster to adapt to SQL.

Pushing ASC's product might be detrimental for those shops that are on a tight budget. If they had to choose between ASC's product and IBM's 5722-ST1, I'd go for the IBM product for the sole reason to get the SQL precompiler.

I have been involved with IBM midrange systems since 1976. I have gone from Mainframes to System/3 to System/38 to AS/400. If we as programmers and developers wish to continue in this business we need to use SQL and SQL tools. SQL is fast becoming the defacto standard for all development. JDBC is based on SQL. iSeries navigator is empowered to use SQL. If I as a hard and fast RPG coder (plus COBOL, Assembler, Fortran and Basic) can learn ILE concepts and SQL then any other computer professional should be able to do so also. It is not enough to rest on your laurals. The writing is on the wall. Learn SQL.

I work for a company that has thousands of AS/400 customers. Why should we expect our customers to purchase an additional product(SQL from IBM) when that money could be better spent on more of OUR products? I don't believe that SQL will be a viable replacement until it is included free with the OS.

I was one of those who cheered OPNQRYF when it first came out. <BR>
But, I haven't touched OPNQRYF for years. I would never consider using it unless there was a gun to my head or a client forces me to. Once I started using SQL, I never turned back. It's so much easier to use than OPNQRYF.

Shops on a tight budget must have BOTH, otherwise they're spending way too<BR>
much on labor. I know, Sequel (ASC) has made a tremendous impact on<BR>
reducing backlog in our AS/400 shop. We've also given it to 3 end users<BR>
that have created wonderful reports, spreadsheets, email, etc. using Sequel.<BR>
That has reduced our programmer backlog as these users were the ones<BR>
submitting the most requests. The GUI front end called Viewpoint makes<BR>
doing all the work very easy.<BR>
<P>
Sequel can be purchased on a "per user" license and a single license for a<BR>
small AS/400 is very reasonable. Any manager that can't justify Sequel,<BR>
even a single user license, should find another line of work!<BR>
<P>
BTW, I have no affiliation with ASC other than being a satisfied customer.<BR>
<P>
chuck<BR>
Opinions expressed are not necessarily those of my employer.<BR>
<P>
<P>
"robberendt" < This e-mail address is being protected from spam bots, you need JavaScript enabled to view it > wrote in message<BR>
news: This e-mail address is being protected from spam bots, you need JavaScript enabled to view it <BR>
| Pushing ASC's product might be detrimental for those shops that are on a<BR>
tight budget. If they had to choose between ASC's product and IBM's<BR>
5722-ST1, I'd go for the IBM product for the sole reason to get the SQL<BR>
precompiler.<BR>
<P>
<P>

I agree that SQL is a strong language and I use it on a daily basis. However, as far as being the source for creating files, I have a few issues. RPG programs are pretty nasty about File Identifiers. We use 3rd party software (JDEdwards) and one of their products uses DDS to create files and their other product uses SQL to create tables. If we try to run an RPG program from the one set of software, over a table generated from their other software, we have problems due to format level id's and format names not matching. Maybe it's just JDE's tool for creating tables using SQL that is not using the correct naming conventions and keywords? Would you save the SQL statements you use to create files in a source file so that you could create the same file with the same format info in different libraries or testing environments and systems (which may have different operating system levels)? <p>Another use for DDS is documentation and data dictionary. When you create a DDS field name reference file and then design all of your files using DDS referencing your original member, you know that a particular field will be created the same throughout the database. I'm not good enough at SQL to be able to use a reference file for fields within a table.

I buy into a lot of what people say in articles I read, but not this time. <BR>
Why should I get rid of my DDS, field reference files and data base integrety, just so I can use SQL. Let's face it, once field reference files are gone, so is your data base integrety. <BR>
How do I know the next programmer that creates a table will create the customer number etc... with the same attributes used in previous data bases? Remember, the reason we love the as400 so is that every programmer knows what the database looks like from one file to another. <p>Sorry,,,,I hope Mr. Cozzie isn't teaching this in his classes.

Others have already pointed out, SQL is essentially free on the machine. As a software vendor, you can also purchase the IBM product that allows you to use embedded SQL in your RPG code. <p>Your customers will NOT need SQL on their machines in order to run that code. If you use ASC's SEQUEL product, you get a lot more power, but I don't know their pricing, whether or not they have a runtime module that can be liscensed.

You're not going to get me to disagree. But the point is, they DON'T TEACH DDS in College (primarily). <p>As to the issue with JDE, they have an "interesting" database to say the least. I just won't go there. :) <BR>
But, RPG has issues with SQL that are the fault of both IBM Rochester and Toronto. I just hope they work those out before we all just give up and go home.

In my early career with the AS/400 (1992-1996) we used OPNQRYF to create dynamic access paths for green bar printing. Since I discovered Query Manager, we have effectively thrown away RPG reports in favor of SQL generated reports. The Query Manager interface comes with 5722-ST1, but the runtime is part of base OS/400. Plus, if your shop is too cheap to buy 5722-ST1 and you are good with SQL, you don't need the interface. (I would recommend the interface). <p>I loved OPNQRYF in its day. But I get a great deal of increased programmer productivity from developers using SQL for ad-hoc queries and reports than OPNQRYF & RPG. I have taken skeptical RPG developers who couldn't even spell SQL and turned them into true believers. Plus, I just converted 3 PC developers to iSeries developers and by using SQL I minimized the learning curve. <p>Granted Query Manager has its quirks. But, GET OVER IT and realize how much money you can save by allowing your developers to concentrate on business logic and getting to the web rather than laying out print formats in DDS or O-Specs...

If your shop religiously uses a field reference file then I can see being hesitant on using SQL to create tables. However, V5R2 will allow SQL to use a Reference when defining fields ( columns ) in a table. ( see <a href="http://www7b.software.ibm.com/dmdd/library/techarticle/0208milligan/0208milligan.html">http://www7b.software.ibm.com/dmdd/library/techarticle/0208milligan/0208milligan.html</a> ) <p>I'm trying to learn as much as I can about SQL and the SQL Procedural Language, mostly in a test/learning environment but I don't want to leave my field reference behind. Since IBM is updating SQL and DB2 and not DDS, I expect all the pieces to eventually be in place, so I'll just wait. <p>This reminds me of the first incarnation if ILE, to me it initially seemed like extra work to create the programs but once IBM added the ability to use binding directories and include the directory name on an RPG pgm's "H spec" my complaints about ILE went away and I'm glad I made the switch.

SQL should run natively in CL is my biggest complaint. So we can have dynamic variables. Using the RUNSQLSTM is good only for static statements. <p>I do use SQL all the time because it is standard across platforms for database access. <p>I still do use OPNQRYF until this is rectified by IBM. You know the IBM that is to become our next utiltiy.

ASC's SEQUEL product and SQL overlap and complement each other to different degrees. SEQUEL runs on the QQQQUERY API used by OPNQRYF, and is limited to the same data types. No datalinks or BLOBs. It doesn't do things like Create Index or Grant or create stored procedures or logical files/views. (I used QUERY/400 before I convinced my current employer to buy SEQUEL.) <p>SEQUEL's strength is in data manipulation and presentation using SQL syntax. It's a great tool for that; I've been using it for 11 years. Like SQL, you can display data, print, update, insert, delete, or output to a PF. But you can also send data in various ASCII formats to QDLS or the IFS or to a Windows server over the /QNTC file system or via FTP or email. Formats include text, delimited (customizable!), Excel, dBase, XML, HTML, and PDF. That's all in just the Kernel module. <p>Among others, there's also the Reports module that lets you lay out reports with subtotals and calculations. The Tabler module pivots data. The ESEND module can "burst" reports and send sections independently to different email addresses; I think it's available separately. All this can run on the command line or in CL programs. A former COBOL programmer at my company says he can write a report in 1 hour with SEQUEL that took him a week to do in COBOL. It's the only reporting tool we use here, besides some legacy COBOL reports. (I have no financial relation to ASC except to send them money for maintenance and upgrades.) <p>SQL does the whole DDL/DML/SPL shebang, but doesn't do fancy output and reporting. It can do types of joins that SEQUEL can't. SQL can have things in its SELECT clause that SEQUEL does not (yet) support, like the MIDNIGHT_SECONDS special value, or the ESCAPE part of LIKE. I have started learning SQL recently and can see things I might do with it that SEQUEL can't do as easily, but for reporting, I'd use SEQUEL for my last steps.

In reference to KCM2, I have just had that same talk with IBM and I have document 5314847 (Request for Design Change) form to fill out and give them back. Maybe if enough of us respond this way we can get SQL to run in CL natively. Mr. Cozzi has this ever worked that the end users requested a change and IBM listened other than people like you and Al Barsa? <p>Lets flood them with the request for change.

Some say they are sticking with OPNQRYF until IBM allows SQL in CL. Excuse me but what can you do with OPNQRYF and host variables that you cannot do with SQL in CL? <BR>
As far as I remember people used OPNQRYF for two things. One, to set up an access path to be used in the RPG program that the CL then called. Easy, skip the OPNQRYF and just use embedded SQL in the RPG. <BR>
Two, CPYFRMQRYF. There are numerous free tools out there to do things like: <BR>
chgvar &mysql = 'update...' <BR>
CHGVAR VAR(&MYSQL) VALUE('update...')

In reply to AS400NUT regarding his question about filling out a Request for Design Change form will get any response from IBM. <BR>
I filled one out several years ago requesting IBM add an enhancement to the line/box keywords. It didn't happen overnight, but within the next release or two the functionality I requested was implemented. Now I can't say it was done because I asked for it, but it was added.

Like Paul, I've been doing this for a very long time on all different platforms. It's been my experience that SQL knowledge is transferrable from platform to platform. Each platform will have it's own quirks, but they are generally not attributeable to SQL but to the OS. <p>Even old horses know can recognize these and adapt well.

It seems strange to me that none of the responses mention MONMSG capabilities. OPNQRYF never gives much explanation when it doesn't work, let alone take any type of action. I used OPNQRYF for many years. Then I worked in a company with many IBM systems. I could embed SQL into my RPG. This allowed me to connect to remote databases, and read each of their inventory files from one system into one single report. I put SQL in my Item master edit program to verify the Item # was already valid on the mainframe, I could prompt a window to read and select the conversion factors from the mainframe DB2 (I haven't seen any SET CURSOR & read a record at a time in OPNQRYF). I could monitor for connection not available (try later), record not found, etc... The SQL book gives the response codes, -negative values are errors, try this with OPNQRYF. Also don't forget IBM's other attempts to support proprietary products (WORDPERFECT on AS/400, Office vision, etc...) I have no guarantees