SQL vs. OPNQRYF--The Battle Continues

[Guest.Visitor]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
I buy into a lot of what people say in articles I read, but not this time. 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. 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. Sorry,,,,I hope Mr. Cozzie isn't teaching this in his classes.

[R.Cozzi]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
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. 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.

[R.Cozzi]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
You're not going to get me to disagree. But the point is, they DON'T TEACH DDS in College (primarily). As to the issue with JDE, they have an "interesting" database to say the least. I just won't go there. 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.

[mpavlak]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
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). 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. 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...

[Guest.Visitor]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
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 http://www7b.software.ibm.com/dmdd/l...8milligan.html ) 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. 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.

[Guest.Visitor]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
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. I do use SQL all the time because it is standard across platforms for database access. I still do use OPNQRYF until this is rectified by IBM. You know the IBM that is to become our next utiltiy.

[Guest.Visitor]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
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.) 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. 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.) 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.

[Guest.Visitor]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
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? Lets flood them with the request for change.

[robberendt]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
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? 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. Two, CPYFRMQRYF. There are numerous free tools out there to do things like: chgvar &mysql = 'update...' CHGVAR VAR(&MYSQL) VALUE('update...')

[maxb@bfcsoftware.com]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
In reply to AS400NUT regarding his question about filling out a Request for Design Change form will get any response from IBM. 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.

[Guest.Visitor]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
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. Even old horses know can recognize these and adapt well.

[thull@fmic.com]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
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 on SQL, but with the rest of the world using it as a de-facto standard, I will put my bet on it staying around longer than OPNQRYF. I have also used SQL on several systems with out the SQL (IBM's 5722-ST1) product, several magazines have given me utility programs to run SQL statements from CL and the command line w/o ST1.

[J.Wells]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
I'll show my ignorance here - "...This allowed me to connect to remote databases..." - Can you point me to a reference book that explains how to do this? Thanks, Joe

[Guest.Visitor]

SQL vs. OPNQRYF--The Battle Continues

** This thread discusses the article: SQL vs. OPNQRYF--The Battle Continues **
THULL is probably referring to DRDA (Distributed Relational Database Architecture). This allows an SQL program on one platform like an iSeries to connect and access data on a dissimilar platform. Search this site for "DRDA". Chris