View Full Version : SETLL for SQL???
07-01-2004, 11:37 AM
Not directly to your SETLL, but reading backwards with a cursor should be accommodated by FETCH PREVIOUS, just a reading forward with a cursor should be FETCH NEXT. As far as positioning the cursor to a specific instance within a cursor, what are you attempting?? Can you provide an example?? HTH - Lee.
07-02-2004, 06:38 AM
You can certainly use Fetch Next, Fetch Prior, Fetch Relative n, Fetch First, and Fetch last to jump around the result set trying to pinpoint your postion to data. However, if your result set conains thousands of records, the performance of all that jumping (no matter how you optimize it) is going to make it far too slow to be acceptable. When a large amount of data is retreived, its better to jsut close and reopen the cursor. Consider the posibility of coding the program similar to what we old RPG programmers call a single page subfile, only retrieve enough data to fill one page of data on teh screen. As the user rolls forward and back a new select is issued each time to retrieve the next page. As long as each record has a unique key, you should be able to "page" your way through the file Use the FETCH FIRST n ROWS ONLY clause to limit the number of records returned by each select.
07-07-2004, 05:58 AM
I would like to be able to build a dynamic access path that is fully scrollable in both directions (forward & backward), but be able to position to any place within the access path within an RPG green-screen program. For example, on the first screen the user fills in blanks to choose records. SQL builds a dynamic access path. select field1, field2, field3 from somefile where field4=:somevalue order by field1 The next screen has a single-page subfile, which I load depending on roll keys and a position-to field. If the user presses a roll key, I can read forward or backward thru the cursor to reload the page. But if the user fills in the position-to field, I can't directly set the cursor to the key value that was entered. In traditional file I/O, I could use SETLL to position to any place within the access path.
07-07-2004, 06:01 AM
The idea of executing select after select sounds inefficient when I compare it to the traditional method, but it seems to be the only way to go. Thanks.
07-09-2004, 03:37 AM
You are on the right track with Fetch Relative. Could you post the SQL statement? Or better yet the series of SQL Statements in order of execution. I'll try to return and look at it and see if I can suggest a course of action or any improvements. SQL efficiency depends on how you code the SQL statement and whether the File, Indexes, environment and other factors like compile options affect the final execution of the program.
07-09-2004, 03:54 AM
You might create a view to function as sort of a sub-subfile then perform new selects on that view as needed. I'm not sure, but the optimizer may be doing something similar under the hood, anyway, so your way may not be that innefficient.
07-13-2004, 05:44 AM
SQL does not have the capability to positions files. Sql does cant do chain either. Yeah I am all too familiar with Where clauses but that is not exactly setll and opening cursors and doing fetch is not exactly equal to setll. SQL is for trivial tasks only on the AS/400. Data extraction with SQL is probably the worse thing you can do on the As/400. It is understandable if you dont have a choice in case of sql server or Oracle (PL/sql).Only use SQL if you do not have RPG. SQL is strictly top down reading, it cant stop in the middle and do some other processing and then start reading from where it left off.It satisfy the where clause only by reading the entire file first. RPG is record at a time reading, It can position by key and then chain into other files based on a key retrieved from one file. Once finished then RPg can continue reading either by key or without a key. Another thing that is extremely anoying in SQL is joining... yeah you try joining five to six file each with over million records and let me know if you get anywhere. In my opinion, only use SQL to open cursors and return cursors back to web pages.. its worthless for data manupulation and extraction, it is extremely trivial and perform very bad with large databases. Whenever you can avoid SQL on the AS/400..even open query file is more flexible then SQL. AS/400 provide far better tools than SQL, try using RPG or open qeury and leave sql for the Windows developers.
07-13-2004, 06:01 AM
It is correct that SQL cannot do a database positioning function, but as to the other comments I respectfuly, but strongly disagree. I never use OPNQRYF. I use SQLRPG almost exclusively. When used properly and understood, SQL can work fine on very large databases. Sometimes it calls for building the righ logicals to get the performance right. For people who haven't tried EVI indexes, that is another way to improve SQL (or OPNQRYF, etc.) performance. As to OPNQRYF being more flexible, uhhh, I don't get it. SQL is MUCH more flexible. I'd also rather use SQLRPG over OPNQRYF because it keeps the data selection and HLL logic in the same object. Also, OPNQRYF and SQL uses the same SQL engine, so I'd see no reason why OPNQRYF would be recommended over SQL if the complaint about SQL is performance and flexibility. Performance-wise, they should be exactly the same for the same function. In my experience, people who have had performance problems with SQL are those who don't understand the DB2 database and the SQL engine on the 400. An in depth understanding of these areas results in being able to create SQLs that perform well. And the ones that don't perform well make sense as to why it didn't. -dan
07-13-2004, 07:11 AM
Exactly right, Dan. We are in the process of converting most of our RPG reports to SQL for performance and maintainability. I've seen ten-minute reports run in a few seconds just by adding an EVI and we've often reduced a few pages of RPG to a few lines of SQL. The more I get to know about SQL the better it gets.
07-13-2004, 08:51 AM
I know we are all creatures of habit and comfort. But please... Not use SQL? Might as well say why use an IF statement! COMP worked fine for my Dad, and it was less code too! IBM has stated that SQL is the direction of the future for the database on the iSeries. If you plan on working with databases anywhere in this industry - you had better be learning SQL. Antiquated close minded thinking will most likely lead you to early retirement.
07-13-2004, 09:55 AM
SELECT FIELD1 FROM FILE1 WHERE FIELD1 = 'CHICAGO' FILE1 has index FILE1L1 with KEY of FIELD1 KEY should have some Uniqueness (not Same value for more than 20% of table) DDS Indexes favor RPG, particularly where the first 4 keys are not very unique (Same values for first KEY have same value in all rows). If SQL Doesn't position, then you lack indexes that match your SQL Statement. SQL does Position! A production database and SQL is not an End User Tool. The application development directions for the future do not mention RPG. See this Article Simulated SETLL with SQL - For sorting a subfile. at http://www.as400pro.com/TipsSQL7.htm Does not demonstrate FETCH Relative, example only. Also see. http://www.common.be/pdffiles/16042002DB2ArtandScience.pdf
07-13-2004, 10:33 AM
Let me step in here for a moment. While SQL is good for a great many things, it is not the only solution for every problem. Native I/O in RPG has a great many capabilities that are difficult or impossible to duplicate in SQL, one of which being the ability to easily position the index via partial keys without incurring a lot of overhead. And as to whether RPG is part of the future of application development, that depends on what you want to use. RPG is not going away anytime soon, and the radical enhancement to the product in recent years should make that clear. But the broader architectural questions - RPG vs. Java, SQL vs. native, EJB vs. other persistence techniques - really need to be addressed specifically and quantitatively. Different approaches fit different business needs, and it's difficult to be able to decide which way is best. Joe
07-13-2004, 07:21 PM
This isn't about SQL vs RPG so back to the Subject: You can position in SQL via a partial key, a full key and filter the data without a lot of overhead or performance impact using SQL. I prefer using all SQL Stored Procedures, UDF's, etc over embedding SQL in RPG. Another SETLL Example: SELECT '1' FROM FILE_1 WHERE FILE_1.FIELD1 = 'VALUE' FOR READ ONLY OPTIMIZE FOR 1 ROW Embedded in RPG SELECT '1' FROM FILE_1 WHERE FILE_1.FIELD1 = :HostVar FOR READ ONLY OPTIMIZE FOR 1 ROW RPG Equivalent would be SETLL without a READ or in english Does this Key exist in the Index.
07-13-2004, 07:39 PM
I'm sorry, but I was just responding to your statement that "the application development directions for the future do not mention RPG"... a statement that I emphatically disagree with. But certainly, back to the topic: SETLL does NOT mean "does key exist". That's only part of its function. It also sets the cursor for subsequent I/O operations. With a SETLL you can then do a READ to get the next record or a READP to get the previous record. You can also do READE and READPE to get the next and previous records using a partial key. None of these things can be done equivalently in SQL without creating a cursor. Joe
07-14-2004, 04:55 AM
A Cursor is part of SQL and in SQL it is the Equivalent of a File Pointer that an RPG program would use. Code and Test a FETCH Relative Example or find one and debug it. Fetch relative is not valid in interactive SQL. ------- Sequential Access Try this in RPG, OPEN FILE and READ. Where was the File Pointer? At the begining of the File. Yes you could use an Index and the Data is sorted. Try this in SQL select * from FILE. Where was the File Pointer? At the begining of the File. Using a view is the equivalent of the sort (use of Index) above and DDS indexes are apparently some sort of view. ------- Adding a KLIST is the SQL Equivalent of adding a WHERE clause condition and I stated before, a loose rule to follow is that first 4 conditions need to match an index (KLIST). If Accessing the File by different keys (WHERE CLAUSE) you should have several CURSOR Statements just as you would set up KEY LISTs for the Separate Logicals. SQL returns this to you so that you can position within current result set. If the Current position is not what you want, close the cursor, change the Host Variables and Open the Cursor. The Cursor will be positioned to the result set of new Host variables. When you declare a Cursor statement the SQL is the equivalent of setting up the Key list, but also if it is Static SQL a statically compiled version of the Access plan to implement the query (RPG closest (loose) equivalent would be a subprocedure). When you Open the Cursor the Optimizer chooses an Index (Access Plan) and Opens the File (ODP) same as User Defined Open. When you FETCH (Relative) which is the same as READ you position within the Result set that matches your WHERE clause. FETCH Relative lets you read Backwards and Forward from current position of the Open Cursor.
07-14-2004, 05:05 AM
Its kind of like visiting relatives. If you want to visit your first cousins 10 records from where you are, then Fetch Relative 10 will work great. But, if you are trying to visit your long lost aunt on the other side of the database, Fetch Relative 10,000 will be a long and painful experience. Fetch Before and Fetch After, are quick ways to jump to either the beginning or the end of the result set, but unfortunately jumping any where else in the result set requires a painfuly slow search. So go ahead and use Fetch Relative to support Page Up and Page Down, but don't rely on it to perform "position to" type logic.
07-14-2004, 05:47 AM
solpssi said "DDS indexes are apparently some sort of view" DDS indexes combine an SQL index and an SQL view in one object. You seem very familiar with SQL, but I get the feeling you aren't as familiar with how SETLL and subsequent READs work. Like READ, REDPE, etc. It's much more versatile for certain types of access than SQL. If you don't understand how these work, try looking at the online IBM manuals, or ask questions in here. Just to clarify one point, earlier in this thread I stated that I use SQL almost exclusively. But that comment was in comparison to OPNQRYF. I seldom use SQL by itself. It's almost always SQL embedded in an RPG program.
07-14-2004, 06:03 AM
I would like to use SQL within an RPG IV green-screen inquiry program, but I am stumped on one point. There does not appear to be an SQL equivalent to RPG's SETLL op code. I would like to be able to open a cursor, as I would open a physical or logial file if I were using native I/O, then be able to position to any point within that cursor, as I would use SETLL. The only thing I can come up with is to close and reopen the cursor every time I need to reposition the subfile, but this seems excessive to me. It also prevents me from reading backward thru the cursor. Any ideas? DB
07-14-2004, 06:03 AM
Dont get me wrong folks. SQL does have its purposes, its not entirely useless. I use it with store procedures and embed it in RPGLE programs all the time. I just dont process data with it.Is just my preference. I am simply suggesting that If you know RPG, SQL is suddenly not the greatest tool to extract data.If you dont know RPG then SQL is the best thing since slice bread. Its a matter of what background do you have. Its a matter of preference.I have PL/SQL developers and SQL server developers come to me and specifically ask me to take some of their tables and put em on the AS/400 and process the data with RPG. These are windows programmers who are dealing with SQL all day and actually prefer RPG over their native sql. It makes a difference because of performance.Web services perform a lot faster when the datasets are being generated using RPG. They learned that real quick. Now they ask for it by name. These windows guys dont even know what RPG means but they know it comes back awfully quick. That's all what matters to them. I guess the trick is to use the best of both worlds and the toolset that is available to you. I use RPG for data extraction and ASP.NET for Web presentation layer. It just works fine for me. Plus by using RPG I can use existing logic and business rules in our environmnet. Our rule of thumb here is if your web page is taking more than 5 seconds to load come talk to me and I will get it to load in less than 5 seconds, no matter how many records you have in the back.You know how I can do that? eliminate the sql statements entirely. Use Rpg and it will work within 5 seconds. Our users dont react well to "please wait query is running" messages. I use what works for our environment, I dont care much what IBM says or microsoft says. I tend to lean on what works in production environment not what is suggested by people, and folks, let me tell you combination of ASP.NET and RPG backend works wonders for us. For the SQL die hard fans,please I dont mean any disrespect, I wish you best of luck... do let us old RPG guys know when SQL can position a file by key and is capable of doing read equals. I swear I will switch then. :)
Powered by vBulletin® Version 4.1.5 Copyright © 2013 vBulletin Solutions, Inc. All rights reserved.