Re: Which of the SYSIBM tables/views show the row count for

[Simon Coulter <shc@xxxxxxxxxxxxxxxxx>]

Here we go again ...

On 08/03/2008, at 9:37 AM, Dave Odom wrote:

> Sorry for taking so long to get back at you but, as I said, I had  
> to build a pro-System i5 presentation about building a data  
> warehouse in the i5.  The audience were end users and developers  
> that love MS Access, ORACLE and SQL Server.   The presentation went  
> well and the System i5 data warehouse project here is well underway  
> with more wanting access or to put their data into it, as we  
> "speak".    It doesn't mean the whole war is won but a major battle  
> along the way and things are looking good.   Now to the answer I  
> owe you for the statement I made about "DB2/400 is not a real RDBMS".
>
> I'll provide you with SOME of the total answer to this as a full  
> and complete answer would take an article or paper to fully  
> discuss.   Some of the answers are IBM some are how the system is  
> used and some are the two different cultures (Real RDBMs culture  
> vs. AS/400/System i5) Also, it would be VERY helpful to have a  
> pretty thorough understanding of the RDBMS history, technical  
> attributes of the most popular RDBMs, the accepted industry  
> standards for how RDBMs are used, ANSI standards, and, most  
> important, the marketplace for what is an acceptable and real RDBMS  
> with regard to function, features, architecture, security, etc.
>
> A typical question:  "I have heard people state that DB2/400 meets  
> more of the ANSI Standards for SQL databases than any other  
> database on any other platform. Isn't that the standard by which to  
> say what database is "best"?"

On this list we have pointed out your first sentence. That does not  
imply your second. What it means is that DB2 for System i is the most  
standards-compliant database available.

> In the real world, not really.  Those that look at buying an RDBMS  
> look at several things and this is a partial list: functions,

DB2 for System i: Tick.

> features,

DB2 for System i: Tick.

> robustness,

DB2 for System i: Tick.

> industry architecture compatibility,

DB2 for System i: Tick. (See point above about ANSI Standards--not  
platform or DB-specific extensions which you CONSIDER industry- 
standard.)

> which leads into can I find personnel that have the education to  
> create and maintain databases and applications against a particular  
> RDBMS engine or how easy is it to train them,

DB2 for System i: Tick.

> ease of care and feeding,

DB2 for System i: Tick.

> reputation in the IT space,

DB2 for System i: Tick. (At least for those with the wit to see.)

> how many vendors support with tools of various types,

DB2 for System i: Tick. (Most 3rd-party DB tools will work as  
expected against DB2 for System i even if the vendor has not  
explicitly tried that. All you need is access to the system catalogue  
tables and Rochester did work MANY releases ago to make that  
interface look like the one the tools expect.)

> security

DB2 for System i: Tick. (Damn sight more secure than the alternatives.)

> and price.

DB2 for System i: Tick. (Cost of entry is higher but TCO is much lower.)

> Another typical question: "What is it that these other databases  
> have that DB2/400 does not have that are useful for designing  
> business application databases?"

Have another go. You failed to answer your own question. Most of your  
responses below are things System i HAS that the others HAVEN'T--they  
are not things these other databases HAVE that make them better!

> - They (real industry standard RDBMs) are set up as a true RDBMS  
> with TABLES, NOT files, VIEWS, NOT logical files, INDEXES, NOT  
> logical files, etc.,

An externally-described System i file IS a table. A System i logical  
file IS a view. A System i logical file IS an index. Again your issue  
seems to be with terminology rather than function. THEY call it  
something else therefore WE have to too.

> - TABLES can only be in table format, usually designed using third  
> normal form to take advantage of the strengths of the RDBMS, NOT  
> old style flat files, or multi-member files that access data via  
> keyed access...

That's only because THEY DON'T HAVE THAT CONCEPT. Rochester took the  
RDBMS concepts and applied them to the system in such a way as to map  
SEAMLESSLY to traditional database I/O. That's a good thing--even  
though continued support for it makes it possible for developers to  
stick with what they know rather than being forced to learn so-called  
modern ways simply because they have no other choice. Your issue  
seems to be with backwards-compatibility--another good thing fairly  
uncommon amongst the alternative vendors.

> there is NO SUCH THING as a file in DB2 or ORACLE and I think in  
> SQL Server as well.
> - Multi-member TABLES are not allowed in a true RDBMS.

What do you think partitioned tables are then? They might not be  
implemented as MEMBERS on other RDBMS but the concept is the same.  
Rochester took advantage of existing system technology to implement  
this concept. Why is that a bad thing?

Next you'll be telling us that Rochester should discard *FILE objects  
for SQL use and put the DB into the Root file system as a collection  
of stream files just like all the other RDBMS.

> - SQL is the ONLY access language to get to the data, not some old  
> record-at-a-time processing back door technique like READ, WRITE,  
> CHAIN, etc.,

So what! That's a System i advantage. It's not a back-door technique  
either. All database access eventually goes through the same  
underlying interfaces. It gives CHOICE--something others don't have.

> - ALL information about the structure of every TABLE, VIEW, INDEX,  
> etc., in a SYSTEM catalog or tables, which are only accessed via  
> SQL, not in each of the files themselves wherein you have to use a  
> non-SQL command Display FILE FIELD Description or some such

Most of the additional information you want in catalogue tables is  
not standard SQL stuff anyway. It's all platform extensions. The  
others have to put it in tables because they don't have any other  
choice. Because tables are implemented as discrete OBJECTS on System  
i (another thing the others DON'T have) it makes sense to store  
information about an object IN THE OBJECT ITSELF. If you want it in a  
table you can put it in a table using a system-supplied command.

> - the System Catalogs are many and robust, like in DB2 for VM or  
> MVS or the V$ tables in ORACLE to support MANY types of database  
> and application designs from simple tables to the storage of BLOBs,  
> XML and much more.

DB2 for System i: Tick. (Again, most things System i doesn't support  
are non-standard extensions.)

> - HUGE numbers of vendor tools are available for database design  
> and build and reverse engineering, application design and build,  
> performance tuning, and much more

Most of which work against DB2 for System i also.

> - database has mechanisms for 24/7 operation WITH backup by being  
> able to quiesce portions of the database for backup while the rest  
> of the database is active

Never heard of Save-While-Active then?

> - DBAs and application developers think and design only around  
> TABLES, not files using record-at-a-time processing

Now we get to your real issue. As I said in a previous append (on 28- 
Feb-2008) your primary complaint is NOT that DB2 for System i is not  
a "real" database but rather that it supports alternative access to  
the SAME database as SQL **and** that many System i developers choose  
to use that alternative access rather than what YOU THINK they should  
be doing.

I can create a non-relational de-normalised set of tables using SQL  
on Oracle if I wish. Does that happen? Yes, when the DBA is  
inexperienced, has insufficient information, wants to extend a 3rd- 
party application without altering vendor tables, or deliberately  
uses 2nd normal form for performance reasons. None of that specific  
use makes their RDBMS of choice any less "real". Neither does the way  
some developers CHOOSE to use System i RDBMS make it any less "real".

> - DBAs design TABLES using Referential Integrity and other  
> functions on a regular basis such that many actions that once were  
> done by the developers program are now done at the database level

DB2 for System i: Tick. (Many System i applications make use of these  
features but just as many choose not to. It is the fact that the  
SYSTEM provides support for RI that makes it "real" not that some  
developers CHOOSE not to use it.)

> - Managers have an easy time finding personnel that can read, write  
> and speak in SQL and industry standard RDBMS methodologies whereas  
> they see folks that use RPG (they think that means Rocket Propelled  
> Grenade) and CL (what's that) and SEU, PDM, etc. as being from an  
> OLD, LEGACY system that is so out there in a vary narrow part of  
> the world.

I don't have space to argue this properly. Yes, it is easier to find  
DBAs with experience on other platforms. Why?
	One: Those platforms NEED a DBA. System i SHOULD have one but  
doesn't NEED one.
	Two: Students are exposed to those other platforms during tertiary  
education.

Using SQL embedded in RPG or COBOL is no different from using SQL in,  
say, PL/SQL. They are both ways of programmatically accessing SQL.

> - The real DB2s and ORACLE and SQLServer are well respected in the  
> industry for these and many other reasons and DB2 and ORACLE run on  
> many different OSs and MANY applications are built on those platforms
> - and on, and on, and on

DB2 runs on WinDOS, Linsux, and *nix (LUW). Oh AND System i. Oracle,  
Sybase, Informix, SQL Server, etc. don't run on System i. Why?  
Because System i already HAS an RDBMS supplied in the base system.  
Ain't no point in competing ON the System i when they can compete  
AGAINST it with their existing code-base.

> So, the IT industry, as a general and common rule,  recommending a  
> platform and an RDBMS to the CEO and CFO to buy will not even see  
> the System i5 as in the running.   This has to change if the  
> platform and the jobs it supports are to survive.   I hope it can  
> be done.  But the traditional System i5/AS/400 folks out there are  
> going to have to come into the reality of the rest of the world and  
> not only see things from their staid and outmoded perspective.

That has more to do with the ignorance of non-System i personnel  
(they only know LUW and RDBMS X so that's what they offer) combined  
with poor marketing from IBM (primary reason for which I've addressed  
in other appends).

Regards,
Simon Coulter.
--------------------------------------------------------------------
   FlyByNight Software         OS/400, i5/OS Technical Specialists

   http://www.flybynight.com.au/
   Phone: +61 2 6657 8251   Mobile: +61 0411 091 400        /"\
   Fax:   +61 2 6657 8251                                   \ /
                                                             X
                 ASCII Ribbon campaign against HTML E-Mail  / \
--------------------------------------------------------------------