?
SolvedPrivate

sql vs rpg

Posted on 2014-09-23
34
Medium Priority
?
101 Views
Last Modified: 2017-07-25
I was told that essentially RPG and SQL
are the same and I somewhat perplexed.
As far as table structures,data types....
can someone shed light on this.
0
Comment
Question by:zachvaldez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 14
  • 4
  • +2
34 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 40338995
I don't know how they could be the same!

SQL is a database. RPG is programming language.
0
 
LVL 35

Assisted Solution

by:Gary Patterson
Gary Patterson earned 1200 total points
ID: 40339132
Hard to understand what you are talking about without any context.   I'll try, though.

First, I have to respectfully disagree with Eric above:  "SQL" is not a database.  "Microsoft SQL Server" is a database, and maybe that is what he means.

SQL is "Structured Query Language".  SQL, as the name implies, is a language used to compose database queries.  SQL also has many other capabilities, and SQL is one of the tools supported by IBM i DB2 for performing a wide variety of DB2 administration, query, and programming tasks.  In IBM i DB2 SQL (and other platforms, too) there are procedural SQL statements (sometimes called P-SQL), data definition (DDL) statements, and data manipulation (DML) statements.  

Procedural SQL statements are used to create stored procedures and functions.  That makes SQL a full-featured programming language.  Using procedural SQL, it is possible to do many of the same things as that can be done in an RPG program.  Not everything, but when it comes to manipulating data in an IBM i DB2 database, SQL can do just about everything an RPG program can do.

Embedded SQL in RPG

It is also possible to embed SQL statements in RPG programs - just like is commonly done in .NET programs, java programs, and other programming languages.  Look up "Embedded SQL in RPG" for some examples.  When manipulating IBM i DB2 data, a programmer can choose between using embedded SQL to perform I/O operations, or traditional RPG file I/O operations (READ/READE/READPE/CHAIN/UPDATE/DELETE/WRITE) - or even mix both in the same program.  

Data Definition

You can define IBM i DB2 tables, indexes, and views using two different interfaces:

Native Data Description Specification (DDS), or SQL Data Definition Language (DML).  Back before we had SQL on the AS/400, we used DDS to create DB2 physical files (tables), and logical files (indexes/views).  Now you can use either interface, though the SQL DML interface is now the preferred one.  It is possible that the comment you heard could have something to do with how database objects used in RPG programs are created, since DDS was traditionally used to support RPG programs that used native I/O statements.  The format of DDS looks a lot like RPG, so it is possible that the comment you heard really was related to DDS VS SQL-DML.

Regardless of how a table is created (DDS or SQL DML), you can use either SQL or RPG native I/O operations to access the table and add, update, and delete rows.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40339447
>> I was told that essentially RPG and SQL are the same <<

That's dead wrong.  While they both produce report-like output, they are absolutely not the same.

A car or an airplane will both get you to another city, but that doesn't make them the same thing.

I can understand you being perplexed, but whoever told you that is far more perplexed than you are, and he/she doesn't even realize it.
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 27

Expert Comment

by:tliotta
ID: 40340615
@zachvaldez:

Can you tell us a little about your background? Are you a student? Are you working as a programmer? (What programming languages do you know?) What platforms have you worked on? (Different platforms can provide very different internal capabilities for both languages.)

If you have different experiences, then different descriptions can make more sense. We can give better answers if we know more of the context.

There are definite similarities between RPG and SQL. But there are at least four generations of "RPG" and the later generations have many more capabilities that seem to get away from some SQL concepts. Similarly, SQL has evolved over the years and does much more than it did many years ago.

"RPG" originally meant "Report Program Generator". The purpose was to let you put a definition of the records in a file into the program and then put the description of a printed report into the same program. An example of some detailed specifications would be to define which fields would be summarized so that the output report could have sub-total and total lines printed. It was primarily a way to generate printed reports from files.

(Note that "SQL" generally has no inherent ability to print reports. SQL generally would be executed by some added component that took the rows provided from SQL and formatted them for printing. Most database systems (DBMS) have such reporting components added in. SQL is focused on 'data definition' and 'data manipulation'.)

In both cases, there is a distinct non-procedural element that can reduce and even eliminate program source code that specifies when to open or close a file or when to read or write a record and some other details. Such actions can be handled automatically by the compiled program or by the DBMS. The focus of both languages can be only on how data items move from input to output. (That's less and less true for current generations of both languages.)

Tom
0
 

Author Comment

by:zachvaldez
ID: 40346958
I noticed too that rpg programmer refer to a table in SQL as a "file" rather than a table and it just adds complexity if one is not aware.
0
 
LVL 35

Assisted Solution

by:Gary Patterson
Gary Patterson earned 1200 total points
ID: 40347032
IBM AS/400 / iSeries / i DB2 tables are also (correctly) called "Physical Files" in native terminology.  

They were "files" for many years before they were "tables".   If I recall correctly, SQL was added to DB2/400 in V2 of the operating system - back in the early 90's, and SQL table support was built on top of the native physical files that already existed in the system.

So many AS/400 programmers still (correctly) refer to these objects as "files".
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 40347036
On the AS/400, they really are "files".  They are just made to look like "tables" to the SQL user.
0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 400 total points
ID: 40347487
On the AS/400-series of systems a SQL "table" is a sub-class of a physical file (PF-DTA) *FILE object. SQL "tables" inherit most object attributes of PF-DTA files but have a few altered attributes, e.g., they cannot have more than a single data member and they cannot accept attributes such as embedded edit words.

Since physical files in the AS/400 line (going back to the System/38) work in a file system that was developed based on the original SQL, it's natural that SQL "tables" fit easily into the file system.

However, early on, a necessary requirement was that "files" from earlier systems that had no relational model had to be accommodated. If not, migration to the newer relational structure would be resisted by customers.

"Files" (in the AS/400 line) have historically allowed practically everything that "tables" can do and more. But they are also becoming limited as more time passes in that IBM has been investing almost all effort into improving "tables", and improvements for "files" happen only because of the shared internal fundamental code base. "Tables" have been extended in various ways that "files" can't take advantage of. Those improvements are only implemented in SQL internal routines and so are only available to SQL statements and not to native I/O statements.

Tom
0
 
LVL 35

Accepted Solution

by:
Gary Patterson earned 1200 total points
ID: 40348609
@ScottPletcher:

On the AS/400 they really are files.  

And they are also DB2 tables.  

They don't just "look like" tables:  they -are- full-fledged DB2 tables.

On this platform, files are much more sophisticated objects than in other operating system.  In Windows, Unix, Linux, etc., a "file" just refers to an unstructured chunk of disk space that can be used to contain anything: program instructions, a script, a MySQL database, a Java program, source code, etc.

In the native AS/400 file system (QSYS.LIB), a file is a data container.  Period.  You can't fill it with program instructions and execute it.  It is a data container, and supported relational features like keys, indexes, typed columns, the ability to perform row level IO, and lots of other database features that we now think of as generally being associated with SQL-based databases.

When the DB2 folks decided to implement SQL capabilities in the AS/400's existing database, they had a great foundation to build on, and we ended up with two perfectly valid, but different sets of nomenclature.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40351003
Unless the AS/400 file structure has changed dramatically, it still uses RRN (relative record number) logic to write file records.  That is fundamentally different from SQL Server and Oracle physical file writes (I can't 100% state it for other relational dbms's, but I would be absolutely shocked to find they are using an RRN approach).

So, while they are "files", they are not "tables" within the normal understanding of that physical structure in other relational dbms's.
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40351207
@ScottPletcher:

IBM hasn't made an AS/400 in 15 years, so I'm going to use the name for the current operating system: IBM i.  For the purposes of this discussion, though it is all the same.

Just so you know where I'm coming from:  I'm an AS/400 / iSeries / IBM i RPG programmer, SQL programmer, and DB2 DBA with 25 years of experience.  I understand the reason for your confusion.  I've trained many PC, Unix, and mainframe programmers over the years on IBM i programming, and many of them have wrestled with the same conceptual hurdle you're having trouble with.  The confusion stems from the fact that IBM i DB2 stores database objects on disk in a different way than you're used to in SQL Server, and because DB2/400 is much more tightly integrated into the IBM i operating system than most RDBMS are in other systems (including DB2 on other platforms).  Finally, IBM i DB2 has a fast, efficient row-level access system that still works even after all these years (though SQL is the preferred interface).

DB2 is integrated into the IBM i operating system, and it has been since the day the AS/400 was first introduced back in 1988 -  the year I started working as an AS/400 programmer and DBA, by the way.  Back in 1988, DB2/400 didn't speak SQL, but it was -still- a relational DBMS: tables (we called them physical files), keys, relationships between tables, indexes (we called them logical files), DBMS-enforced data typing of columns, journaling, an lots of other cool RDBMS features.  

We just didn't have the SQL language directly integrated into DB2 until a few years later.  So we had to use lower-level row-level I/O operations (sequentially or by key) to access the integrated DB2 database files (in RPG, READ, WRITE, UPDATE, DELETE, CHAIN, etc).  But all of those I/O requests were (and still are) processed by the DB2 RDB engine and handled similarly to DB2 single-row SELECT, INSERT, UPDATE, and DELETE queries.

Now in IBM i DB2 we have SQL-DDL (data definition language), SQL-DML (data manipulation language), SQL-PL (procedure language), triggers, constraints, column-level security, and a whole host of other modern SQL RDBMS features - and all the tables are implemented as physical files, and are still accessible in RPG using row-level IO operations as well as via SQL.

By the way, there is not (and never has been) a way to issue a WRITE to an AS/400 file in RPG, COBOL, C, Jave, Rexx, CL, or SQL by relative record number.   In native RPG, you issue a WRITE, and DB2 decides where to put the row in the file (table).  It may reuse some deleted space in the file (table), or it may append it to the end (or other options actually).  Or, if you prefer, you can use embedded SQL in your RPG program and issue an INSERT and you get virtually the same result.  When you use SQL vs native I/O, there are a few differences in validation timing, but ultimately you are still operating on AS/400 physical files.  Of course, SQL has the ability to operate on sets of rows within the same statement, where row-level RPG techniques require the programmer to make the associations in program logic.

I think you are making the (incorrect) assumption that in order for an object to be an SQL table, it has to be contained inside a big chunk of space controlled by the DBMS, and managed exclusively by the DBMS.  That's true on most platforms, but not on AS/400 - because DB2 is part of the operating system.  Integrated in tightly, unlike any other OS I've ever seen - and I've seen a few.  

Here's what I'll tell you. You can take this sql:

CREATE TABLE GARY (
  GARY1 CHAR(10),
  GARY2 CHAR(20),
  GARY3 CHAR(30));

And run it on AS/400 DB2, and you'll see the DBMS creates a physical file object called "GARY".  Run the same on SQL Server and you'll get the same table - but SQL Server prefers to store all of its tables in one giant file system object instead of discrete objects.  Doesn't make it "right" and IBM i  DB2 wrong - just makes it different - and often confuses people who have only ever seen it done the SQL Server way.

CREATE INDEX IDX_GARY on GARY (GARY1);

on DB2/400, Oracle, MySql, MySQL, etc, and you'll get an index called IDX_GARY.  

SELECT * from GARY where GARY1 = 'SOMEKEY';

And all of them will optimize the query using slightly different rules, discover the index called IDX_GARY, and then either grab the row using an index lookup (index search, find RRN of row in table, page in disk page containing row, extract row, present to user), or if the table is very small, a quick table scan.  

And on AS/400 DB2, it'll be retrieving the data from a native physical file object.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40351260
@ScottPletcher:

The RRN() function has nothing to do with it. That's just an IBM-extension that returns the physical ordinal sequence of rows. Rows are written (by default) in "files" by appending to the end of the data space; but rows are written (by default) into the first available unused space in "tables". The default can be explicitly changed to the opposite setting for both "files" and "tables".

The added capability of retrieving a RRN() value is no more relevant than is the lack of a TIME data type for SQL, possibly even less relevant since TIME is a SQL standard data type. RRN() is related to the underlying physical implementation and no effect on SQL nor is it in conflict with any SQL standard.

Tom
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40352310
>> RRN() is related to the underlying physical implementation <<

That's the point.  The underlying structure is traditional (flat) file-based, not table-based.  The GUI they put over the top of it is irrelevant to whether the underlying file structure is typically relational or not.
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40352642
@ScottPletcher:

I'm sure the DB2 on i developers at IBM will be greatly saddened to learn that their SQL-standards-compliant RDBMS doesn't have tables!

Again, you're just operating on some mistaken assumptions.

The underlying DB2 for i structure is built from the ground up on top of an earlier relational database system that made use of slightly different terminology (not structure - words) than SQL terminology - that's all.  AS/400 DB2 developers did have to do some retrofitting to make the existing DB2/400 RDBMS compatible with SQL standards.  For example, the existing DB2 database cross-reference files got views added to them with SQL-friendly names like SYSTABLES and SYSCOLUMNS.

Before we had SQL in the AS/400 DB2 RDBMS, physical files -still- were not flat files.  IBM i DB2 physical files have (and have always had):

* A data description language (called DDS) used to define the structure of each physical and logical file.
* Metadata that describes the table layout, columns, and data type.
* A data space containing defined columns and system-enforced data types.
* An optional integrated index (called an "access path") if a key is designated for the physical file.
* The ability to create related logical files that can provide alternate row ordering (SQL Indexes are implemented as logical files).
* The ability to create related logical files that provide selection criteria, relationships between tables (join criteria), calculated columns, and more (SQL views are implemented as logical files).
* A (non-SQL) query engine that allowed relational operations such as joins to be performed.

Of course, you can SIMULATE a flat file in an IBM i physical the same way you do it in SQL Server - by creating a table with one CHAR field.  Maybe you've seen something like that and it led you to your mistaken assumptions.

Ultimately, SQL Server tables are implemented as a data structure (that is conceptually similar to an IBM i DB2 physical file).  It it contained in a larger "flat" windows file system file, but it is still just a data structure.  And so is an IBM i physical file.  

And I have no idea what you GUI reference means.  What does a GUI have to do with a relational database?

I'm guessing you probably aren't very familiar with IBM i DB2 documentation.  Take five minutes to peruse the table of contents, if nothing else, of the IBM i DB2 SQL Reference:

http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzintro.htm

- Gary
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40352811
You seem not to be very knowledgeable about what constitutes a relational db management system if you believe that DDS is in any way, shape of form "relational".
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40353081
@ScottPletcher

What does DDS have to do with "relational".  It is just a language used to describe a table, and index, or a view.  Isn't that exactly what SQL-DML is?

The Association of Computer Manufacturers seems to disagree with you:

A great deal of research has been focused on the development of database machines. In parallel to this work some vendors have developed general purpose machines with database function built directly into the machine architecture. The IBM AS/400 is one of the principle examples of this approach. Designed with a strong object orientation and the basis functions of the relational database model integrated into it's architecture, the AS/400 has proved to be a commercial success. In the present work we look at the database component of the AS/400.

http://dl.acm.org/citation.cfm?id=166639

Sybase (didn't they write SQL Server originally for Microsoft?) seems to disagree with you, too:

DB2/400 Database – The Integrated Database

The AS400 contains a relational database called DB2/400. DB2/400 is integrated into the AS/400 partly above the MI and partly in the LIC. Conventional databases are separate software components that reside on top of the operating system. Since DB2/400 is integrated throughout the entire system it can achieve a higher level of efficiency because it is tightly integrated with the components with which it communicates. The database management system (DBMS) is a framework for storing and retrieving data. A DBMS must have an interface so users can access and manipulate the data. There are two interfaces to the AS/400: The Data Description Specifications (DDS) and Structured Query Language (SQL).  The DDS, or the native interface, was carried over from the IBM System/38. It has a look and feel similar to IBM’s Information Management System (IMS). The second interface for the AS/400 is SQL. This is the industry standard for relational databases and is an optional product that you must purchase separately.

The "two interfaces" comment isn't really accurate.  It would be more accurate to say that there are "two interfaces for database object creation", but close enough to make my point.

http://www.sybase.com/detail?id=1002621

Oracle seems to think the AS/400 database is relational, and specifically mentions the DDS interface:

IBM DB2/400 V4R5 is a relational database management system (RDBMS).
...
The IBM DB2/400 V4R5 database is integrated with the AS/400 operating system, OS/400. It includes the following features:

    Referential Integrity
    Triggers
    Stored Procedures
    Two-Phase Commitment Control
    Distributed Relational Database Access (DRDA) Level 2

The database uses SQL/400 as its native interface. SQL/400 includes non-SQL standard facilities such as Command Language (CL) and Data Description Specifications (DDS). These facilities originated with the IBM System/38 and existed before SQL became standard.


Again, not perfectly accurate (CL isn't part of the old SQL/400 product and neither is DDS), but close enough for you to get the point, which is "relational".

http://docs.oracle.com/cd/B10501_01/win.920/a97252/ch1.htm

Even Microsoft(!) disagrees with you:

Relational Database Access

Much of the operational data stored on OS/390, AS/400, and RS/6000 computers is accessed via a relational database management system. The most popular database on these host systems is IBM DB2. In the case of the AS/400, DB2 is integrated with the operating system.

http://msdn.microsoft.com/en-us/library/ms942939.aspx

I'll spare you the IBM  links - I bet you can imagine what those say.

I'm clearly wasting my time here.  You clearly have some tightly-held misperceptions or knowledge gaps, and there is no way to educate a closed mind.
- Gary
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40353180
>>  You clearly have some tightly-held misperceptions or knowledge gaps, and there is no way to educate a closed mind. <<

As do you, and same for you.


>> What does DDS have to do with "relational".[?]  <<

The foundational database rule ("rule 0"), as specified by Dr. Codd, is that " in order for a database to be considered relational, it must use its relational facilities exclusively to manage the database".


Btw, just to let you know, Sybase code has been completely written out of SQL Server.  One reason for that was likely that much of it was not inherently relational.


Just because something supports what it calls "tables" and allows SQL-like queries to be written does not automatically make it an RDBMS.

Many DB2 implementations are indeed relational.  I loved DB2/2 back in the day.  But the AS/400 is technically not an RDBMS, it just puts some RDBMS features on a mixed database implementation.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40353619
AFAIK, DB2/400 | UDB iSeries still has an 10 character internal limit for object names; you can specify a pseudo-name/"ALIAS" of up to 30 characters, but the O/S must internally change it to a 10-char "system" name to do backups, etc..  Can you name any other RDBMS that has these types of bizarre restrictions/work-arounds?
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40356656
The underlying structure is traditional (flat) file-based, not table-based.
As is SQL Server. So what? And Oracle uses whatever file system is used by the platform it's installed on. The physical implementation is irrelevant. And DB2 tables are implemented as "Tables", not as some artificial construct in a streamfile like SQL Server does it.

Can you name any other RDBMS that has these types of bizarre restrictions/work-arounds?
Restrictions? There's no restriction in 10-char system names. SQL Server does the same; in a different way, but it stores its tables in .MDF files. What a bizarre restriction that is.

And, oddly enough, you can even edit such .MDF files with Notepad.

In the AS/400 line, you must go through the DBMS to maintain tables. The DBMS offers both DDS and SQL for management; it's irrelevant that two possible languages are used. Codd's rules do not specify that "SQL" is the sole language. "SQL" didn't even exist at that time. Both DDS for files and DML for tables are handled by the DBMS in the AS/300 line, so both fit. You can't just open a DB2 file/table in the AS/400 line with Notepad and make changes.

Even using COBOL/RPG/C/Whatever, you must use the DBMS-provided methods (even if they're called READ/WRITE/whatever) and every action is controlled by the rules you configure in the DBMS. DB2 will reject a COBOL WRITE if it violates the DB2 rules. Will the same happen with Notepad? Nope. I have a .MDF opened right now in Notepad, and SQL Server won't stop me from messing it up. In fact, it didn't even notice that I'd made a change. If I want to take the time, I can analyze the structure of SQL Server "files" to write code to access and modify table data without needing an instance of SQL Server running.

No, it won't matter because DB2 in the AS/400 line follows the relational rule you referenced and SQL Server doesn't. (Care to determine whether or not that's similarly the case for Oracle?)

The specific one of Codd's rules that you mentioned is a fundamental reason that DB2 in the AS/400 line is just about the only DBMS that fits. No other feature of the system can manage DB2 files or tables except the DBMS. Demonstrate the same for another DBMS.

Tom
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40357246
SQL Server doesn't use flat files and it certainly doesn't use 10-char internally generated "system names" to manage anything.  Those are just truly bizarre claims.  You're completely into fantasy land.
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40363794
SQL Server doesn't use flat files

Of course it does.  Every bit of SQL server maps to a flat Windows OS file.  File sets.  Log files.  Even the executable programs that make up SQL server.  In the Windows OS that is all you have - flat files.  To make anything else, you stuff it into a flat file, tack on an extension (that isn't enforced by anything), and then hope everyone plays nice and doesn't alter the contents of the stream file by other means (like a hex editor, or a virus).

Here's what Microsoft says:

SQL Server maps a database over a set of operating-system files. Data and log information are never mixed in the same file, and individual files are used only by one database. Filegroups are named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.

http://technet.microsoft.com/en-us/library/ms179316%28v=sql.105%29.aspx

On IBM i, we have system-enforced objects (*FILE, *PGM, *DTAARA, *USRPRF) that are strongly typed.  A stream file editor simply can't open a *PGM object.  The OS simply won't permit it.

certainly doesn't use 10-char internally generated "system names" to manage anything.

Technically, you are right.  SQL Server doesn't use a 10-character internally generated "system name" to manage anything.  It uses a 4-byte integer called an OBJECT_ID.  Every major RDBMS does something like this, because SQL-friendly tables (and other object names) names can be long, and it is inefficient to use a long text label like a table name internally when you need to establish relationships between DB objects, refer to DB objects in programs, etc.

The OBJECT_ID in SQL Server is the fundamental identifier for sql server objects - including tables.

Look at the queries Microsoft themselves supply for querying SQL Server metadata, and note the use of OBJECT_ID (just like we use the system object name in DB2) to correlate metadata between different database metadata objects.

http://msdn.microsoft.com/en-us/library/ms345522.aspx
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40363992
The data files are not flat files.

I give up.  Anyone that considers:
fixed-byte-position input (col1 must be exactly this value, cols 2-5 this value, etc.), such as DDS, to be "relational";
that an internal integer object identifier is equivalent to a 10-character identifier generated with filler underscores, etc.;
is not being truly rational.
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40364247
What is a "data file" in SQL Server?  Do you mean a table?  SQL Server tables are just data structures stuffed inside one or more giant Windows operating system flat files.  Unlike DB2 on i tables, I should note, which are contained in a special operating system object (*FILE object with attribute *PF) that was SPECIFICALLY designed and built from the ground up into the operating system to hold relational, column-oriented data.

Not sure what has given you that incorrect impression about IBM i DB2.  My guess is that at some point you've probably seen some ancient System/36 (the AS/400's parent system) - style code that has been ported forward.  S/36  did not have an integrated RDBMS, and did flat-file processing - just like you describe.  Those old applications when ported to AS/400 and it's successors often used AS/400 DB files created with a single field to emulate a flat file.  that would at least explain your misconception.  But those old applications didn't use DDS - file layouts were typically described right in each program.

The DB2 on i method of implementing a table is different (by different, I mean "better") from SQL Server- in part because it runs on a completely different operating system with completely different rules, and in part because it was built on top of an earlier RDBMS.

Lol.

You and your hang-up with DDS.  

You keep insisting that DDS has something to do with DB2 being "relational" or not.  You either don't know what DDS is, and what it does, or you don't know what "relational" means - because the format of the data definition language used to create objects in a RDBMS has NOTHING to do with the relational FUNCTIONALITY of the system.

DDS is just a language that pre-dates SQL and can be used to describe the layout of certain objects in DB2/400.  Or you can use SQL.  Yes, DDS it is a fixed-position language.  What in the world does that have to do with the DB2 objects it is used to create?  I can write a program that would allow you to use DDS to create SQL Server objects, too.  Would that somehow change the fact that SQL Server is an RDBMS, and a SQL Server table is still a table?

Tell me you've never used the SQL Server Management Console GUI to create a table?  In the GUI, you fill out a form that looks almost identical to DDS to define the characteristics of each column.   The thing that gets created is still a table - doesn't matter what tool, language or technique you use to create it.

Before we had SQL, certain DB2/400 objects (physical files, logical files, database columns) had a 10-character length limit - that was a simple decision the DB2/400 SQL designers made for two key reasons:  performance (long object names are more expensive to use) and due to the nature of the underlying operating system: OS/400 imposes a 10-character length limitation on object names in the OS.

This is all just silly cosmetic stuff: length limitations, naming conventions, internal object identifiers, OS restrictions on naming, underlying storage mechanisms.  Every platform has them.

A table is just a data structure where data is organized into rows and columns, with each column assigned a name that is unique within that table, and a data type.  In a relational database, the metadata about the database also needs to be defined and stored withing the DBMS.  

DB2 on i does all this.  

Does it do some things the way it does to to backwards-compatibility?  You bet it does.  There are applications written 30 years ago, before the AS/400 was ever introduced, that can still run on IBM i.  Is it designed to conform with the OS requirements and restrictions on the platform it runs on?  Of course.  And so does SQL Server.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40364429
>> A table is just a data structure where data is organized into rows and columns <<

Then Paradox 3.1 on DOS was a fully relational db, right?

I guess notepad could be a relational db if it just termed each line a "row" with a single "column" of text in it??
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40364692
Lol.  That's one way to handle losing a debate:  change the subject.

OK, I'll play.  

"Table" and "relational database" are not synonyms.  You can have tables (a relatively simple data structure), without having an RDBMS.  Excel is a good example.  Excel has tables.  Excel is not a RDBMS.  COBOL has tables.  COBOL is not a RDBMS.  Can you create a table in Notepad?  Sure.  Does that make Notepad a RDBMS?  Of course not.

If you define "fully relational" as strict adherence to Codd's Rules, then I doubt any of the databases we are talking about qualify as "fully relational" in a strict academic sense.  So I'm going to drop the "fully" from the conversation, because, in my opinion that is just silly legalistic debating over fine academic points that simply don't apply to the real world or real products.  I'm going to use the common definition of RDBMS as used in the real world:  

A RDBMS is a database that is designed and implemented in accordance with good relational design principles, and that generally adheres to Codd's Rules.

So, was Paradox for DOS a RDBMS?  Of course it was.  It was an a key selling feature of the product:  a full-blown RDBMS for DOS.  http://en.wikipedia.org/wiki/Paradox_%28database%29

Notepad isn't.  Fails the Foundation Rule:  A relational database management system must manage its stored data using only its relational capabilities. The system must qualify as relational, as a database, and as a management system. For a system to qualify as a relational database management system (RDBMS), that system must use its relational facilities (exclusively) to manage the database.

So while Notepad can create an unlimited number of tables, it isn't relational, isn't a database, and isn't a management system.  

After all this time in this thread, I think I see the root of the issue:  

You simply don't know what it is that makes an RDBMS "relational".  You seem hung up on piddly little platform-specific implementation details that you don't like - such as the lengths of names of internal identifiers, or the fact that DDS exists, or the fact that you can retrieve the current RRN associated with a row in a DB2/400 database.  The simple fact is, that none of these things have a SINGLE THING TO DO with whether a DBMS is "relational" or not.  "Relational" is not about "how" features are implemented.  It is about the fundamental design and functionality of a DBMS.

If you're actually interested in learning (as opposed to seeing how many responses you can rack up to silly posts like the last one), I'd be happy to provide you with some reading material.  Here's a book by the same guy that wrote my college textbook, and I bet that if you took a class in Database Theory at your local university, there is a good chance this is the book that they'd use.

http://www.amazon.com/Database-Design-Relational-Theory-Practice/dp/1449328016/ref=pd_bxgy_b_text_y

Afraid I don't have the time to give one-on-one lessons in database theory.  I learned about the principles of various types of DB designs in college, and the contents of an entire course is too much to cover here.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40364711
I have far more db background than you do, and some things aren't simple semantics, they're fundamental to a relational db.  I just don't have so much love of the AS/400 architecture that I have to support its approaches no matter what.

You use the same rule to invalidate Notepad that you said the AS/400 should be excused from!!:
"
Notepad isn't.  Fails the Foundation Rule:  A relational database management system must manage its stored data using only its relational capabilities.
"
DDS is not a relational capability, no matter how often you repeat and pretend that it is.

On the Series i, one can create, load and list/report on data while never using a relational language of any kind.  So how is that a "RDBMS" under any normal meaning of the word??
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40365076
I have far more db background than you do

Of course you do.  I'm sure everyone's very proud of you.  I'd be more impressed if you had more than "I don't like DDS" to contribute to the discussion, though.  And that's all you keep coming back to.

You use the same rule to invalidate Notepad that you said the AS/400 should be excused from!!:

OK - I can actually see where you are coming from on this one.  Tell the truth though - you've never really studied relational theory, have you?  Don't get me wrong - a lot of very good technical people haven't.  It is just that this conversation leads me to believe that you've never really been through this evaluation process before.

The Foundation rule is just big broad strokes, but you have to remember that relatively simple words are being used to briefly describe what is essentially a mathematical model.  Rule 0 is kind of an "entry point", and gets further clarified and codified by the later rules.  Let's break it down:

A relational database management system must manage its stored data using only its relational capabilities.

DDS is not used to manage stored data - that is what a DML or a program does.  DDS is a DDL tool, used to describe the layout of certain database object to the DBMS.  Also note it says "relational capabilities" and not "relational language".  "Relational capabilities" in this context, just means that there can't be any way to get around enforcing relationships and rules defined in the DB - triggers must fire no matter how you update a row.  Authority must be checked.  Logging must happen.

The system must qualify as relational, as a database, and as a management system.

Relational: Check.

This is a mathematical term:  In the relational model of a database, all data is represented in terms of tuples, grouped into relations.  A tuple is just an ordered list of elements.  This is a basic design criterion, and one that DB2 on IBM i has always implemented.

Database: Check.

Management System: Check.

For a system to qualify as a relational database management system (RDBMS), that system must use its relational facilities (exclusively) to manage the database.

Here's the one you aren't going to like.  There is only one way to create a physical file / SQL table on IBM i: and that is to to ask DB2 to do it.  Doesn't matter what interface you use, doesn't matter what DDL you use, there is just no way to do it without handing it off the the DBMS and letting it handle the table creation request using it's "relational factilites".  If you were to use the legacy interface you would create the DDS source, then use the CRTPF to create the table.  CRTPF call the QDDCPF system program, which is a low-level interface to DB2.  A long list of thing happen: DDS is validated and parsed, field references to other tables are validated and retrieved, authority is checked to make sure the user can create a table in the target location, OS storage routines are called to allocate space, the actual *PF object is created, complete with header, data space, overflow space, and optional access path, and finally the database cross-reference (metadata) is updated - all handled by DB2's built in (relational) facilities.

Check.

DDS is not a relational capability, no matter how often you repeat and pretend that it is.

No, I haven't.  DDS is -not- a "relational capability", and I haven't and wouldn't say that it is.  DDS isn't a "capability" of any kind.  You're just putting words in my mouth.  Read the thread.  The correct term, I suggest, is "relational language", since DDS is a data definition language for IBM i DB2.  A relational capability is something inherent in the database: the ability to organize rows into tables and store them persistent;y.  the ability form, track and enforce primary key and foreign key relationships.  The ability to create and maintain indexes.  Those are capabilities.

DDS is a legacy DDL and it has some significant gaps that keep it from being, in my opinion, a full-fledged relational language.  DDS is supported in IBM i DB2 for backwards compatibility.  While DDS does have some nifty relational features (for example the ability to define columns "by example" - based on columns in other files - something that SQL isn't so great at) I certainly wouldn't describe it as a "relational language", and it certainly falls short compared to SQL DDL - which is superior to DDS in most other ways.

You just keep coming back to DDS, in spite of the fact that it is just an additional interface that is supported for legacy purposes.  Many of the shops I work with don't use it- except when dealing with legacy applications that were built before SQL became the primary way of dealing with DB2 on i.  You seem to be under the mistaken impression that fact that DDS exists somehow stops IBM i DB2 from being a RDBMS.

This is where I tall you that you need to read Codd's Rule 5, which deals with relational languages, and also makes allowances for non-relational languages by not prohibiting them:

Rule 5: The comprehensive data sublanguage rule:

    The system must support at least one relational language that
            Has a linear syntax
            Can be used both interactively and within application programs,
            Supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval), security and integrity constraints, and transaction management operations (begin, commit, and rollback).

Note there is no rule that a RDBMS must support ONLY relational languages, or how many, or that SQL has to be involved at all.  Codd, and others, recognized that there were existing DBMS out in the world, and that they already had interfaces and mechanisms for creating tables, indexes, etc, and that relational capabilities would be integrated onto these existing systems.  As long as all of these interfaces are ultimately under the control of the "relational facilities' of the RDBMS, they are fine.  

So the rule is just that there be at least one relational language available in a RDBMS.  

And IBM i DB2 has just that: SQL.

(Codd, I might note, apparently wasn't enamored with SQL as a "relational language", and you'll find plenty of debate about that, but if SQL is an "acceptable example" for your standard of a "relational language", then DB2 for i has this base covered.)  

SQL is now, and has been for years, the IBM-defined standard interface for DB2 for i, and DDS is supported as a "legacy interface".  New functions are added to the SQL interface that don't appear in DDS.  IBM i DB2 SQL supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval), security and integrity constraints, and transaction management operations (begin, commit, and rollback) - hey, that sounds like just what Rule 5 requires!  

On the Series i, one can create, load and list/report on data while never using a relational language of any kind.  So how is that a "RDBMS" under any normal meaning of the word??

Yes.  You can.  So what?  Alternate interfaces don't stop a DBMS from being an RDBMS.  Just read Rules 5 and 12.  I can do similar things on SQL Server or Oracle, too.   I can create a table using SQL Server Management Console GUI without every using a relational language (and that screen looks remarkably similar to DDS, doesn't it?).  Oracle Developer does the same thing.  Doesn't stop SQL Server or Oracle from being an RDBMS.

I can load tables in SQL Server using SSIS without ever writing a line of any relational language, or into Oracle using the Oracle Bulk Loader (or on IBM i Using Navigator, for that matter).

I can generate listings/reports without ever writing a line of a relational DB language in SQL Server, Oracle, and DB2 using a plethora of reporting tools like Crystal Reports, DB2 Web Query, SSRS, and a long list of GUI reporting tools.

I'm guessing from that comment that you might need a refresher on Rule 12, too:

Rule 12: The nonsubversion rule:

If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to subvert the system, for example, bypassing a relational security or integrity constraint.  

Rule 12 makes it clear that alternate interfaces are expected and allowed in an RDBMS, as long as they don't allow you to bypass the DBMS and the rules in it.  In IBM i, every single operation on any database object goes through the DBMS - you simply cannot get around it.  You can write an RPG program that inserts data using WRITE operations, but data type and length validation still happens, transaction journaling still happens, authority checking still happens, integrity checking / constraints verification still happens, triggers still fire, and database metadata that tracks IO operations is still updated.  You can use the CPYTOIMPF command to export data to a CSV, but the DBMS is called to handle the data retrieval, and does all the required stuff: code page conversion, logging (journaling), object and column-level authority checking, read triggers, etc.  You can use the old Query/400 interface to generate a report from DB2 tables, and the DBMS (Classic Query Engine) handles the request: logging, authority, query optimization and execution, triggers, etc.

I understand that you don't like these "other interfaces" - but you don't get to redefine what is and what isn't a RDBMS based on what you like and don't like.  I even get it - you're used to SQL Server, and IBM i DB2, with it's tight integration with the IBM i operating system, and legacy interfaces challenges some of your perceptions of how a RDBMS "ought to behave".

But here are the simple facts:

IBM i DB2 is a relational database management system.  Codd, the "father of relational theory", was an IBM employee - if you don't think these guys know the difference, then I really don't know what to say to you.  You saying it isn't  doesn't change the fact one bit.  DB2 DBAs that I work with are simply going to be stunned when I show them this thread - and especially when I tell them that the person I'm talking to is a pretty highly-qualified expert on EE!

A RDBMS can store tables and other DB objects internally in any fashion that suits the designers.  Relational theory is INTENTIONALLY SILENT on implementation.  In fact the IBM i DB2 low-level storage mechanism is significantly superior to SQL Server and Oracle, since DB2 RDB functionality is a tightly-integrated component of the IBM i operating system.  In SQL server, it all gets stuffed into a set of giant operating system flat files - and the conventional wisdom on Windows is "don't run anything else on your SQL Server machines".  Why?  Because SQL Server competes with other processes for Windows OS for resources - and it isn't particularly friendly about how it does it.  On IBM i, DB2 is a core part of the operating system - you cannot load or run the OS without it - and DB2 plays wonderfully with other processes.  We rarely dedicate an IBM i as solely a database server.

I notice that as we go though this all you really keep doing is coming back to harping on DDS.  I understand - DDS offends you.  I don't like it either.  Fortunately, I have to use it less and less as time marches on.  But it doesn't have a thing to do with whether DB2 on i is an RDBMS or not.

Hey here's a neat trick:  DB2 for i can actually generate SQL DDL for a table that was created with DDS.  So if you want, you can never look at a DDS source member again if it offends you so much.  Just go into Navigator, navigate to the table, and Generate SQL.

Finally, I can't help but notice you've failed to respond to a single one of my source citations (and you've provided none to back up your assertions), nor reply nor acknowledge any of the points I've clearly documented for you:

No response to the IBM i DB2 SQL documentation I provided (which I'm confident you didn't even glance at).
No response to the ACM AS/400 RDBMS quote.
No response to the Microsoft AS/400 RDBMS quote.
No response to the Oracle DB2/400 RDBMS quote.
No response to my Paradox being a RDBMS comment (lol, you did some research didn't you, and realized you were wrong)

You get the idea.

Well, this has been amusing, but I'm tired, and this sounds like it is a religious issue for you, instead of technical, so I don't think there is much benefit in continuing.  Hope I've given you some food for though.

- Gary
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40366167
I've taught relational theory, you just selectively state sub-parts of it to make your preferred platform compliant.


Rule 5: Comprehensive data sub-language rule

This rule states that a database must have a support for at least one language which has linear syntax which is capable of data definition, data manipulation and transaction management operations. Database can be accessed by means of this language only, either directly or by means of some application. If the database can be accessed or manipulated in some way without any help of this language, it is then a violation.


Codd's rules are intended to insure that the DBMS is designed from the ground up as a relational system.  That is the point.

DB2/2 was.  Mainframe DB2 was.  DB2/400 is not, because they had to wedge (selected) relational elements into an existing structure.

FOCUS db tried to claim they were "relational plus" back in the day when relational was taking over.  But they had the same problem -- their foundation was not relational, despite them changing their data container names from "segments" and "fields" to "tables" and "columns".

On the AS/400, files and tables are too tightly coupled (physical rule violated).  You could delete a physical file in order to drop the table, rather than having to drop a table.  That would not be allowed in a relational system.
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40367249
I've taught relational theory,

Interesting.  Where?

First, there is no requirement that a DBMS must blindly comply with every detail of Codd's rules to be an RDBMS.  The rules are a guideline intended to describe an ideal.  This isn't religious dogma - it is a description of the basic functions of one style of DBMS.  Ludicrous, if you ask me, to treat these 12 rules like a book of law.

Second, if you look long enough, you can find "editorialized" versions of Codd's rules, just like the one you found, to support any fussy little detail you want to use to make an exclusionary argument.  This sort of debate always just devolve into silly little legalistic details.  I can use Codd's rules to "prove" that SQL Server or Oracle isn't a "true" RDBMS, too.  But since I think that would be silly, I refuse to engage.  

Of course it is an RDBMS.  Just like IBM i DB2 is.  

DB vendors really got caught up in this "our RDBMS is more relational than your RDBMS", and "our RDBMS is relational and yours isn't" marketing wars, as a result, every marketing department got some computer scientist to support their view and publish a version of Codd's rules.

Best source I can find online is this Computerworld article, which quotes the rules from his 1985 two-part Computerworld articles (“Is Your DBMS Really Relational?” and “Does Your DBMS Run By theRules?”  E. F. Codd,
Computerworld, October 14 and October 21, 1985).

Here's rule 5 from that article:

Rule 5: Comprehensive Data Sublanguage Rule

A relational system may support several languages and various modes of terminal use. How-
ever, there must be at least one language whose statements are expressible, per some
well-defined syntax, as character strings and whose ability to support all of the following is
comprehensible: a. data definition b. view definition c. data manipulation (interactive and
by program) d. integrity constraints e. authorization f. transaction boundaries (begin, com-
mit, and rollback).

On the AS/400, files and tables are too tightly coupled (physical rule violated).

Do you mean rule 8?   I think perhaps you should read it again, or find some educated commentary on the rule.  Rule 8 is about isolating logical presentation of data vs storage.  Not too many years ago, IBM modified the way SQL indexes were stored on disk.  The change was completely transparent to programs - no re-compiles, no code changes required.  The logical operation of the system is completely isolated from the physical implementation - which is just that Rule 8 is talking about.

That would not be allowed in a relational system.

Lol.  It is allowed in a relational system.  You just seem to keep getting hung up on the idea that it doesn't have to be SQL to still be relational.  DLTF is an IBM i Control Language interface to DB2: perfectly equally allowed under rule 5.  If it got you around the relational controls of the DBMS, it would be bad - BUT IT DOESN'T.  A request to delete a physical file on IBM i DB2 is routed to the DBMS no matter how you do it:  DLTF or SQL DROP TABLE.  DB2 then handles the delete/drop using integrated relational capabilities that do all the things DB2 needs to do when a table id deleted - check authority, logging, relational consistency checking, space deallocation request to OS, object deletion, and DB metadata updates.  

The key is that you can't allow alternate interfaces to do things to violate the relational integrity integrity of the DBMS.  And on IBM i, you can't - and it is "baked in" even more strongly than on other platforms like SQL Server on Windows since IBM i DB2 is part of the IBM i operating system.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40367267
No, all twelve rules are not absolute laws.  But the foundation is.  The other 12 rules assume rule 0 has already been met.  Thus, rule 0 is the sine qua non to be an RDBMS.

Indeed, at the very start of the article you reference [emphasis added]:
"
•      Rule Zero.  A relational database management system must manage its stored data using only its relational capabilities.  This is the fundamental principle upon which the remaining 12 rules are based.
"


" A request to delete a physical file on IBM i DB2 is routed to the DBMS no matter how you do it:  DLTF or SQL DROP TABLE. "
Hmm, but isn't a "physical file" on the AS/400 just a "data container", i.e., the underlying data might not be a relational table at all, but some other type of file.  Why would DB2 be needed to delete such a non-db file??
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40368490
Agreed.  Rule 0 is important.  And IBM i DB2 manages and stores data using ONLY it's relational facilities - regardless of the interface you choose to use: CL/DDS, RPG/COBOL row-level I/O operations, or SQL.

Hmm, but isn't a "physical file" on the AS/400 just a "data container", i.e., the underlying data might not be a relational table at all, but some other type of file.  Why would DB2 be needed to delete such a non-db file??

OK, now we're getting somewhere.  

IBM i is an object-oriented operating system.  A *FILE object is the parent class of physical files and logical files.  

IBM i delegates management of all *FILE PF and LF objects (and their children) to the portion of the operating system called DB2.  

DB2 is the only interface for managing these objects - and that rule is enforced at a very low level in the OS.   This means that for the purposes of our discussion, there is NO SUCH THING in IBM i as a non-database file (there are other *FILE object sub-classes like device interfaces like a Tape File, and a Display File that are also sub-classes of *FILE, but these are not database objects, and are not managed by DB2.)  

So there are database objects that were created using the legacy interface, and DB objects that were created with the SQL interface.  All managed completely and exclusively by DB2.

Not to confuse the issue, I hope, but for the sake of completeness, there are "stream files" that exist elsewhere in the system (IFS) for compatibility with Posix, Java and Windows-style applications, and they are just like Windows files, but they have nothing to do with a *FILE object - any more than a Windows file that you might export some SQL Server data to in CSV format has to do with the SQL Server database.  These would be true "flat files".  You can't directly manipulate them with row-level operations in RPG, you have to use a Unix-style API to manipulate these stream files, just like in Unix or Windows.  

I wonder if this whole PF/LF/Table/Index View thing is the root of what is causing
such a strong objection from you.  It confuses the @#$% out of experienced RPG programmers making the change from native IO to SQL, so I understand that it presents a conceptual hurdle.

A PF object is a "data container" - in exactly the same sense that an SQL Server table object is a data container.  As opposed, say, to a LF/VIEW/INDEX, which only -refers- to the data contained in the data container object.  That's what the whole "data container" thing means: not in the sense that a Windows stream file is a "data container" for an exported CSV.

In the IBM i environment, we generally treat the whole issue as just one of nomenclature:  PF/LF is what we use to refer to (older) objects created using DDS, and TABLE/VIEW/INDEX is what we use to refer to (newer) objects created using SQL.  Technically, it isn't quite -just- nomenclature - there are some capability differences between a PF created with CL/DDS and a PF-TABLE created with SQL, but it is a small subset of features - and they all obey relational rules and are implemented though DB2.

The important point is that all of the DB2 relational restrictions apply regardless of creation method.  

There are a few features that are only available through DDS (typically things that would violate SQL compliance, but not relational compliance) so for SQL compliance reasons they mostly got left out of the TABLE sub-class of *FILE PF objects.

And there are a number of features only available through SQL - extensions to the PF parent object - because, I suspect, they were available through SQL and presumably the effort to retrofit into DDS wasn't justified since DDS was just there for legacy support anyway.  

And because IBM wants everyone to use SQL now anyway.

But - and here's the key point:  no matter which method you use to create one of these DB objects, they are ALWAYS managed exclusively using the relational facilities of DB2.  I can CRTPF a physical file with DDS, and use SQL to query it, join it to another table, open a cursor over it, ALTER TABLE it, DROP TABLE, add constraints, etc.

If you were under the impression that this wasn't the case - that you could get around the DB using native commands, row-level operations, etc - then no wonder you object so vehemently to the status of DB2 on i as a "true" RDBMS.  IF that was true, you'd be right, and I'd be standing right there beside you.

But that simply isn't the case, and that's the reason for my use of "misconceptions" in previous posts.  I don't mean it as a barb - I really think you are just missing that one crucial piece of the picture.

I can open a PF/TABLE in an RPG program using the native (non-SQL) interface, but every single OPEN, CLOSE, READ, WRITE, DELETE, UPDATE operation is processed by DB2, and relational integrity is enforced by the DBMS on every single file operation.  RPG (at least without embedded SQL) is not a rule 5 language, but it still has no choice but to comply with any relational rules configured in the database.  It doesn't do it voluntarily - RPG would be quite happy to ignore a UNIQUE constraint if it could.  But it can't, because that WRITE operation can only be implemented under the covers as a call to DB2 (QDBPUT, is the IBM i DB2 module that gets called when you issue an RPG WRITE.  guess what DB2 module is called when you issue an SQL INSERT?  Yep - gold star.  QDBPUT.)  

You simply can't use RPG (or COBOL, or C) to "get around" relational rules configured in DB2.  If there is an UPDATE trigger on a PF/TABLE and RPG does a native UPDATE operation, the trigger still fires - and if the trigger returns an error, the UPDATE fails, just like an SQL UPDATE would.  If there is a UNIQUE constraint on an index over a PF, and the RPG program attempts to WRITE a row that would create a duplicate key in that index, the WRITE fails - just like an SQL INSERT would.

This means that are not "other kinds of data" that you can store in an AS/400 physical file.  If it is in a PF (and a table is just a child class of a PF), then it is managed completely and exclusively by DB2.  Every Physical File is a relational object.  Every PF has DB2 metadata.  Every PF has strongly-typed columns.  Every PF can have triggers, constraints, logging (journaling), keys, foreign key relationships with other PFs,  

Sure, a programmer can EMULATE a flat file in DB2 for i - just like you can in SQL Server or Oracle - by creating a table/physical file with a single field - but the file is STILL managed by DB2, and obeys all the relational restrictions.  Of course if you only create one big CHAR column, and you don't create a key, and you don't create any constraints, and don't create any indexes, and you only use row-level operations on the file in an RPG program, it starts to look a lot like a flat file - and I think this is probably what you've seen that is making you so adamant about this whole "flat file" thing:  but you can do exactly the same thing in any RDBMS, including SQL Server.  Only difference is that if you don't have that row-level interface (rule 12), you have to emulate that, too, for example using a SELECT * cursor and FETCH a row at a time in a program.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40368611
>> but every single OPEN, CLOSE, READ, WRITE, DELETE, UPDATE operation is processed by DB2, and relational integrity is enforced by the DBMS on every single file operation <<

I thought you could selective turn *off* logging on tables in Series i?  You can selectively say, that table over there is recoverable, this table here is not.
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40368871
Yes, absolutely correct.  

Transaction logging can be selected on a table-by-table basis in DB2.  It is a recovery decision made by the DBA during database design.  Transaction logging is a much more fundamental requirement in SQL Server - SQL Server simply won't work without some minimal logging.  But if you configure tables with RI features enabled, or want support for transactional integrity (commitment control) in IBM i DB2 applications, logging (journaling) is required.

In short, you can only turn off journaling if you also remove those constructs and restraints from tables that require journaling - basically you have to alter the design of the database.  DB2 won't just ignore a cascaded delete, for example if you turn off journaling.  The delete request will fail (or maybe the request to stop journaling will fail until you actually remove the cascaded delete constraint - can't say as I don't recall every trying to stop journaling a file with RI constraints without removing the RI constraints first).

Bottom line, if there is a rule configured in the DB for a table, it gets enforced, or the operations that would violate RI fails - regardless of the interface.

SQL Server, as you know, supports minimal logging - we use it when doing bulk inserts to improve performance, for example, and lose point-in-time recovery capabilities as a result.

Oracle supports disabling logging too - and for the same purpose.

All of these capabilities in these systems can be interpreted as a Rule 12 violation (non-subversion) - but it is there for a primary practical reason:  bulk load performance.
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40368906
I might note just for the sake of clarity that RI means "referential integrity", not "relational integrity".  RI is all about rule 10.

Rule 10 requires that a RDBMS provide RI for each distinct non-null foreign key value in an RDB, there must exist a matching primary key for the same domain.  In IBM i DB2, you generally can't configure these relationships without also configuring journaling.  For the sake of completeness, there is an delete rule exception (*RESTRICT), but the system just uses a pre-check before each delete operation in this case to avoid an RI violation - as opposed to a rollback after.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question