SETLL for SQL???

[solpssi@bellsouth.net]

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/160420...andScience.pdf

[J.Pluta]

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

[solpssi@bellsouth.net]

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.

[J.Pluta]

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

[solpssi@bellsouth.net]

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.

[K.Forsythe]

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.

[dacust]

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.

[Guest.Visitor]

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

[T.Malik]

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.