Unconfigured Ad Widget

**bharvey_Chatt** · 08-27-2007, 07:33 AM

TechTip: Full Outer Joins on DB2 for i5/OS

Once again DB2 for the i5/iSeries/Series i/AS400 has fallen short of the mark. The code below shows how to get around this without too much nastiness...haha Use the temp tables. No sense running the same queries on both sides of the Union. Also, note the use of 'Union All'. I do this because I know that the combination of a Left outer and a Right exception joins eliminate the need for the SQE to remove duplicates.

Code

**David Abramowitz** · 08-27-2007, 11:18 AM

TechTip: Full Outer Joins on DB2 for i5/OS

The examples given in this excersize are remarkably similar to those given to a dinosaur like me when I was studying RPG II. The point of the study was to find creative ways to use "Matching Records". Without getting into too much detail, it is safe and accurate to say that the Matching Records facility is still available and supported in the latest incarnation of RPG. Some may say the RPG matching records uses indicators, and is kludgy. OTOH I don't think that the code necessary to formulate an equal solution to the SQL alternative offerred here would require as much work, lines of code, or debugging effort. I believe that it is important when pointing out a particular problem, to note that more than one solution may be possible. dave

**Greybeard400** · 08-28-2007, 11:57 AM

TechTip: Full Outer Joins on DB2 for i5/OS

It seems to me that all this SQL complexity is the result of the lack of desire to merge data with a well designed OS/400 DB and generate reports with specific results as required. Missing data? Mark the report line with necessary message. The length of time necessary to debug an SQL set like this has got to be enormous, let alone the impact of maintenance for the next schlub that comes along to make a change. I guess I just don't see the marvelous time saving that functions like this are supposed to bring, when a well designed program and/or OPNQRYF select with a driver CL program will produce the same results in a readable manner, in the same or less time, plus have the advantage of being maintainable by junior programmers. If you just gotta do this, this example is pretty impressive, and scary! Addendum: I refer to the example by bharvey_Chatt.

**David Abramowitz** · 08-28-2007, 01:44 PM

TechTip: Full Outer Joins on DB2 for i5/OS

If it's a one-time shot, you don't need OPNQRYF, CL, or even any level of RPG. Query/400 gives you an option to select all records from more than one file whether they match records from the primary file or not! Taking this one step further, once you have saved the query, you can go into QM query to generate SQL code from your *QRYDFN object. Since it already worked in Query/400 you have gone past the debugging stage. Dave

**Guest.Visitor** · 08-28-2007, 04:03 PM

TechTip: Full Outer Joins on DB2 for i5/OS

Dave, I don't think you can do that with Query/400. The 3 types of join offered in V5R1 are equivalent to Inner, Left Outer, and Exception. There's no Full Outer equivalent. And when you generate SQL from a *QRYDFN (at least with RTVQMQRY), the result ignores the effective JOIN type, and just says WHERE T01.field = T02.field, converting it to the equivalent of an Inner join no matter what.

**ukpi1b** · 08-29-2007, 08:18 AM

TechTip: Full Outer Joins on DB2 for i5/OS

Just one more link... http://www.itjungle.com/fhg/fhg041107-story01.html

**OzzieH** · 08-29-2007, 09:36 AM

TechTip: Full Outer Joins on DB2 for i5/OS

greybeard, Actually this is a pretty standard example of SQL, and it's not difficult to debug at all. It's all in the eye of the beholder of course, but here's some advantages of this approach over opnqryf: 1) It can be executed interactively, via STRSQL or Navigator, and with it you can see immediate results. 2) Related columns & tables can be added or subtracted very easily as the requirements change. 3) This can be changed into a stored procedure easily, opening up those benefits, which I won't go into. 4) More people, WAY MORE in fact, know SQL than OPNQRYF. This is quite readable IMO. Try it. You might like it.

**Guest.Visitor** · 08-29-2007, 03:10 PM

TechTip: Full Outer Joins on DB2 for i5/OS

Force yourself to learn and use embedded SQL in your ILR/RPG efforts. Learning the SQL syntax and methods of it's implementation in application development may seem messy and a burden at first compared to our well known I/O opcodes. However, once you get into it, see the ease and benefits of it's use...You will find it a rare occasion to ever use "native" I/O again. Consider for a moment the simple SQL "Select" statement using an "Order By" clause for reports and/or interactive displays where the User wants it sort ordered any number of ways. As a result of using embedded SQL in application development, I have not had the need or occasion to create, build, or even use a logical file in over 5 years. The query optimizer picks an appropriate index for you. (Unless database performance issues arise in your applications...Programmers should never have to create an index simply to sort I/O for a given physical file.) imho

**J.Pluta** · 09-03-2007, 07:30 PM

TechTip: Full Outer Joins on DB2 for i5/OS

I've let this one slide for a little bit because of the holiday (which I've spent the majority of getting my model 270 back up and running). But now I have a moment and I'll respond. SQL and ISAM are two different tools and should be used for different jobs. SQL is excellent for set at a time processing and for ad hoc sorting and selection of records. ISAM excels at positioning by key and at record-at-a-time transactional processing. Both tools should be in every programmer's toolbelt, along with the knowledge of when to use each. For tonight, I'll just debunk the "rare occasion to use native I/O" comment. Let's take arguably the single most common application in all of business applications: the "work with" function. Identify how to do the following: 1. Show the user the first five records in the master file (any master file). 2. The user wants to position to the first record starting with the letter F. Up until this point, both access methods work reasonably well. Again, the file and field are inconsequential to the discussion. Could be customers, parts, locations, people, I don't care. 3. The user decides to page BACKWARD to see the previous five records. With positioning by key, this is incredibly easy (READP 10 times, then READ 5). Without it, it is nearly impossible. ISAM supports positioning by key. SQL does not. Q.E.D. Joe P.S. This isn't to say SQL is bad. It allows more ad hoc capabilities, and even allows you to access data if you don't have an index already built. For this particular business case, you do have to have a logical view on the field in question. But even if you have an INDEX, for this class of business requirement SQL stinks. There are others. Moral? Use the right tool for the job.

**SoftwareTrend** · 09-04-2007, 03:54 AM

TechTip: Full Outer Joins on DB2 for i5/OS

I just found a UseNet thread with some bloke making an interesting argument for why record access is better than SQL. It was posted in 1996 and was with respect to dBase. dWhat? Exactly. If we take that Oracle, SQL Server, and others (including DB2-UDB) control most of the RDBMS market then it is interesting that they all focus on SQL as their data access methodology. They all manage quite nicely in all programming situations. The example you gave is classic language and presentation specific to ISeries and in particular the ubiquous subfile processing. And it is fast with code record access. But even here all SQL implementations have the ability to do this same type of thing - and in one statement and a lot less code. Some more experienced DB2 person might help out but isn't it something like this in DB2? select * from MyFile where TheKeyField < ThelastTopOfPageKey order by TheKeyField fetch last 5 only SQL Server would use the TOP n or BOT n command, I can't recall Oracle's equivalent, MySQL is Limit etc. Now the difference here is the RPG and DDS are not good at what to do next when multiple records need to displayed - either using Record Access or SQL Record Sets. But Oracle, MS, and the others don't use this language. They have their own language stategies that suit the SQL recordset feed that their databases provide. The most common is binding. Get a listbox, set the SQL string property, bind, and the data is there. A couple of properties to be set in the program code. That is it. No need to loop through code checking end of file etc. Is it faster to do record access? More than likely on Iseries but this reflects the system's origins. I've never found single record access via SQL on SQL Server or Oracle to be too slow. Read that as lightning fast. On the ISeries it isn't as good. Disappointingly, if you troll through some non-ISeries DB related sites this is fairly commonly known.

**J.Pluta** · 09-04-2007, 05:32 AM

TechTip: Full Outer Joins on DB2 for i5/OS

Isn't it somthing like this in DB2? fetch last 5 only No. The best you can do is reverse your ORDER BY clause completely. And that requires executing another SELECT statement. SQL Server would use the TOP n or BOT n command There is no BOTTOM, that's my point. You have to use the reverse access path, and write your SELECT statement in such a way as to exactly mirror the original SELECT. It can be done, certainly, but it's cumbersome and due to the double maintenance, prone to error. I've never found single record access via SQL on SQL Server or Oracle to be too slow. And I have. Unlike DB2, which is built for concurrent access, SQL Server and Oracle both have locking problems on files with hundreds of millions of records and lots of concurrent users. You'll find this information everywhere on the 'Net. But this isn't about SQL Server or Oracle. If you want to run your business on either one of those databases, then you really don't have a good reason to use the System i, and thus this discussion for you is moot. As to DB2 performance, it is consistently ranked as one of the fastest databases on the planet; in the current TPC-C results, five of the top ten entries are DB2, four are Oracle, only one is SQL Server. DB2 is the most ANSI standard, and has perhaps the widest platform coverage of any database. DB2 on an iSeries (specifically on i5/OS) typically doesn't fare as well as DB2 on a dedicated processor such as an xSeries or pSeries, but that's a measure of the machine. Comparing performance between the i5/OS version of the database and a dedicated server version is always a bad idea, but that's because of the many additional features of the operating system (such as never getting a virus, or being able to run applications written 20 years ago). But again, if you're not going to use a System i, which seems to be your point (you knock the System i, you knock DB2 and you knock RPG), then nothing in this thread is really relevant to you, which after all is about DB2 for i5/OS. Joe

**David Abramowitz** · 09-04-2007, 06:18 AM

TechTip: Full Outer Joins on DB2 for i5/OS

Most interestingly, I have recently entered a shop where every record is a historical transaction. Consequently in order to pull appropriate records for a variety of functions one must read records backwards until a valid record is reached. It is not uncommon to find myself in a situation where I have to work on systems that I did not design. Regardless of how I might feel about the design, the work still needs to be done. That being said, it is nice to know that I can work directly with an RDBMS, and do not have to rely on SQL. Yet I may choose to use SQL when and where appropriate. The more tools you have in your belt the easier it is to get the job done. After all, if all you have is a hammer, everything looks like a nail! Side note: I am currently in a multi-platform shop. A project was given to the Oracle staff who were not able to complete or integrate a particular project over an 11 month period. Our team started from scratch and had the project running and fully integrated within 1 month. There were some issues after the production release, but they too were resolved after an additional month. This is my own empirical observation and may not be indicative of other projects. Dave

**Guest.Visitor** · 09-04-2007, 08:16 AM

TechTip: Full Outer Joins on DB2 for i5/OS

Simply using a duplicate SQL statement for paging backwards by "keyed" access isn't cumbersome at all...As you said, the ORDER BY clause is reversed simply by adding the DESCending designation to the columns in your order by clause... My broad stroke regarding native I/O should be more accurately put as "You will find yourself using native I/O less and less as your SQL knowledge becomes more and more. Another point I will offer about SQL...If you know it on DB2, you know Informix, Oracle, MySQL, TSQL and any other implementation of SQL. Why? Almost all concepts are identical. I learned Informix first, supporting a PeopleSoft implementation. Then I had a chance to play w/Oracle...Now DB2. Except for minor syntax differences (typically left outer joins syntax for some reason.) they are for all intensive purposes...identical. Learn one, know'em all!

**J.Pluta** · 09-04-2007, 08:56 AM

TechTip: Full Outer Joins on DB2 for i5/OS

Simply using a duplicate SQL statement for paging backwards by "keyed" access isn't cumbersome at all...As you said, the ORDER BY clause is reversed simply by adding the DESCending designation to the columns in your order by clause... Unless it's already descending, at which point you need to remove the DESC attribute. And regardless of how easy you think it is, it is double maintenance; if you change the order in one, you have to change it in both. Except for minor syntax differences (typically left outer joins syntax for some reason.) they are for all intensive purposes...identical. Learn one, know'em all! Simply not true. Heck, the one brought up here (getting the first five rows) is different in every major dialect: SELECT TOP 5 (SQL Server), FETCH FIRST n ROWS (DB2), WHERE ROWNUM() <= 5 (Oracle), LIMIT 5 (MySQL), SELECT FIRST 5 (Informix). In fact, for all but the simplest SQL statements, it turns out that there are significant differences between the dialects, usually involving non-ANSI extensions for performance reasons. The closest to the ANSI standard is DB2. Joe

Unconfigured Ad Widget

Announcement

TechTip: Full Outer Joins on DB2 for i5/OS

TechTip: Full Outer Joins on DB2 for i5/OS

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment