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

Thanks for taking the time to write this out in detail Dave.

After reading this note, I have no doubt you will be successful in your
project since there is no hard limitation with DB2 for i5/OS that would
prevent you from achieving your goal.
I can see that you're challenged by being the only DB2 educator in your
shop, alas, that's true in many shops.

One piece of good news on SQL catalogue views is that IBM is going to
surface all table based statistics in a set of SQL catalogue views
leveraging UDTF technology. You're already using some on V5R4 (SYSPSTAT for
number of rows in a table). Additional ones will become available on V6R1
(possibly PTFed back to V5R4).

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dave Odom
Sent: Friday, March 07, 2008 4:37 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Which of the SYSIBM tables/views show the row count for

Eric,

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"?"


In the real world, not really. Those that look at buying an RDBMS look at
several things and this is a partial list: functions, features, robustness,
industry architecture compatibility, 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, ease of
care and feeding, reputation in the IT space, how many vendors support with
tools of various types, security and price.

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?"

- 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.,
- 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... 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.
- 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.,
- 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
- 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.
- HUGE numbers of vendor tools are available for database design and build
and reverse engineering, application design and build, performance tuning,
and much more
- 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
- DBAs and application developers think and design only around TABLES, not
files using record-at-a-time processing
- 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
- 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.
- 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

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.

Just a brief account.

Take care,

Dave