Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

SETLL for SQL???

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SETLL for SQL???

    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.

  • #2
    SETLL for SQL???

    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.

    Comment


    • #3
      SETLL for SQL???

      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.

      Comment


      • #4
        SETLL for SQL???

        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.

        Comment


        • #5
          SETLL for SQL???

          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.

          Comment


          • #6
            SETLL for SQL???

            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.

            Comment


            • #7
              SETLL for SQL???

              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.

              Comment


              • #8
                SETLL for SQL???

                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

                Comment


                • #9
                  SETLL for SQL???

                  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.

                  Comment


                  • #10
                    SETLL for SQL???

                    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.

                    Comment


                    • #11
                      SETLL for SQL???

                      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

                      Comment


                      • #12
                        SETLL for SQL???

                        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

                        Comment


                        • #13
                          SETLL for SQL???

                          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.

                          Comment


                          • #14
                            SETLL for SQL???

                            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

                            Comment


                            • #15
                              SETLL for SQL???

                              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.

                              Comment

                              Working...
                              X