TechTip: Full Outer Joins on DB2 for i5/OS

[Guest.Visitor]

TechTip: Full Outer Joins on DB2 for i5/OS

Before you all jump on that "identical" post above...of course anyone who has worked on at least two different DB's knows there are always built-in functions specific to the database. Example: Our SQL Server people are always consulting w/me regarding DB2 as: "In SQL Server we have a scalar "function"...it does this, Does DB2 have an equivalent? Most times it does or has an equivalent way of getting the same result...Is what I meant "identical".

[Guest.Visitor]

TechTip: Full Outer Joins on DB2 for i5/OS

SELECT TOP 5 (SQL Server), FETCH FIRST n ROWS (DB2), WHERE ROWNUM() <= 5 (Oracle), LIMIT 5 (MySQL), SELECT FIRST 5 (Informix). ...Without having the DB's right in front of me, my best guess, at a glance is that they all produce identical results.

[J.Pluta]

TechTip: Full Outer Joins on DB2 for i5/OS

First you said: 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! Then you said: of course anyone who has worked on at least two different DB's knows there are always built-in functions specific to the database. That's not identical. It means that every time you write a piece of SQL code, you are potentially locking yourself into a specific vendor, and if you want to use another database you have to translate all your code. It's certainly not learn one, know'em all. If anything, it's learn one and then rewrite for every other one. Joe

[J.Pluta]

TechTip: Full Outer Joins on DB2 for i5/OS

Without having the DB's right in front of me, my best guess, at a glance is that they all produce identical results. Well, you're the one saying "learn one, know'em all" and you can't be sure whether or not the syntaxes are even valid, much less whether each returns the same results. My point is that learning one variant of SQL does not make you an expert on all SQL databases. Joe

[jacobus]

TechTip: Full Outer Joins on DB2 for i5/OS

because companies investing in RPG already decided that portability isn't a big issue.

[J.Pluta]

TechTip: Full Outer Joins on DB2 for i5/OS

I didn't bring up the "identical" business. Remember, I use SQL all the time. I just want to make sure the facts are clear and that people make decisions based on correct information. Joe

[Guest.Visitor]

TechTip: Full Outer Joins on DB2 for i5/OS

Anyone with a sound knowledge of SQL in any implementation wouldn't have any trouble jumping into another SQL implementation. If the syntax checker in that database squalks on something you're trying to do...How long would it take you to find the correct syntax or function to do the same thing in any database...Maybe 5 minutes the 1st time?...And 30 seconds everytime after...??? When you know what you want to do...The only difference is syntax. Thank you, for making my point.

[Guest.Visitor]

TechTip: Full Outer Joins on DB2 for i5/OS

Perhaps you might consider switching to decaf...just a thought. Ok?

[J.Pluta]

TechTip: Full Outer Joins on DB2 for i5/OS

The only difference is syntax. They're identical except for their syntax. That's awfully close is to "It depends on what the meaning of the word 'is' is". But anyway, at least we seem to agree that different databases require different syntax. Good enough for this conversation. Joe

[Tom Daly]

TechTip: Full Outer Joins on DB2 for i5/OS

You've pushed it too far with that one, bub. In both this thread and previously Joe has clearly articulated his position vis-a-vis SQL and native IO and when to use each. I've enjoyed and learned from the comments you, Joe Pluta, and others have posted on this topic. But stay on topic! By the way, you don't speak for me. I think Joe's position is very sensible and yours a bit dogmatic. Just stay on topic, ok? Tom D.

[Guest.Visitor]

TechTip: Full Outer Joins on DB2 for i5/OS

I think the overall purpose here is to encourage participation...Not chase it away with louder voices and bigger sticks.

[David Abramowitz]

TechTip: Full Outer Joins on DB2 for i5/OS

efnkay wrote: I think I speak for all here I would take a strong contrarian position regarding your thoughts. Dave

[Guest.Visitor]

TechTip: Full Outer Joins on DB2 for i5/OS

My posts are meant to inform, encourage, and are generally in a positive light or at least (obviously to some) motivational. I haven't nor do I believe anyone has ever posted anything here with intent to mis-inform or otherwise mislead the honest inquiries submitted by others. So let's let it go... On another front...I've been working on pressiing keys with my mind. So far in controlled testing, I have been able to press the Space-bar and the Alt-keys. I having some difficulty pressing any key with my mind while holding down the Shift-key with my other mind... p.s. No problems pushing buttons with my posts. Hoo-hoo

[SoftwareTrend]

TechTip: Full Outer Joins on DB2 for i5/OS

You seem to be well versed in other languages and databases but have some sort of zeal to squash any discussion on any comparatives to contemporary systems. I don't get this at all. The original poster proposes an SQL workaround for an SQL deficiency in this version of DB2 (note - it isn't missing in other versions of DB2 - which ARE the "DB2" that wins the performance stakes you referred to). This then leads to alternative RPG solutions being proposed etc.. I tagged on to this thread after it degenerated into the norma ISeries's "use SQL for queries only" path. That was my fault. But seriously - you see what the opposition database and language products offer. For instance, in the context of the original article's focus what is wrong with solving the problem head on - Mr IBM why is it that the outer join is not fully supported? We are not solving the problem by hiding it, proposing app code workarounds, or admonishing anyone critical of the implementation. Nothing initiates action more than critical analysis and comparatives. MS has absolutely thrived through constant criticism. Never mind.

[J.Pluta]

TechTip: Full Outer Joins on DB2 for i5/OS

Let's stay focused here. First, I'm not arguing that DB2 for the System i (call it iDB2 for lack of a better name) lacks some features of DB2 UDB, or of other SQL engines. This has actually gotten considerably better over time; the classic query engine (CQE)capabilities were very limited. But the introduction of the SQE has allowed iDB2 to make great strides, no? But my argument was not with the statement that iDB2 is missing an OUTER JOIN; that's a valid criticism. I only got into the conversation when it got to the "SQL good, ISAM bad" stage. I will always get involved when people try to say that SQL is a better access method than ISAM, because it is not. SQL is simply a different tool, better at certain classes of problems, while ISAM is better at others. The beauty of the platform and especially of the RPG language is that you can so seamlessly employ both techniques as necessary. No other language on any other platform provides such flexibility! You imply that I'm admonishing anyone critical of the implementation. Not so; an OUTER JOIN would be nice. I'm simply providing a critical analysis of the strengths and weaknesses of SQL and ISAM. Joe