In this the last installment of the series on relational database design, we will cover the physical implementation of the sample database described last month. The implementation of our three files AUTHOR, ARTICLE and ISSUE will be done using three different methods available on the AS/400: DDS, IDDU and SQL. The examples are far from complete; they're intended only as a tool to give an idea of the different database implementation methods available on the AS/400 and which one may be right for your situation. I will first present each method individually, then I will compare and contrast each of them.

1 shows the completed object specifications that were the result of last month's logical design. Some database designers may disagree with the final result. For example, many times it is more advantageous to divide the name field into first, middle and last instead of one long field. It really depends on the purpose of the field and how it will be used. Remember, this series is a taste not the banquet.

Figure 1 shows the completed object specifications that were the result of last month's logical design. Some database designers may disagree with the final result. For example, many times it is more advantageous to divide the name field into first, middle and last instead of one long field. It really depends on the purpose of the field and how it will be used. Remember, this series is a taste not the banquet.

Each method approaches the definition of the fields and files differently, mainly in the area of ease of use. The easier a method is, the less flexibility you have in how you define the file.

The Data Dictionary

A data dictionary is one of most important steps in the implementation of your database. It provides a central source for the definition of all of the pieces of information that your data base will contain. This provides a common, consistent definition of data elements now and in the future.

The first step in the physical implementation is to create a data dictionary. This will be used to define all of the fields that we will be using in our files. Only two of the methods, IDDU and DDS, support a data dictionary. IDDU has a true data dictionary and DDS allows for one file to reference the fields of another file for their definition.

In DDS, the data dictionary is called a field reference file, which is a physical file whose fields are referenced in the definition of other files. This is done through the use of the DDS keywords, REF and REFFLD. An example of the DDS for a field reference file is shown in 2.

In DDS, the data dictionary is called a field reference file, which is a physical file whose fields are referenced in the definition of other files. This is done through the use of the DDS keywords, REF and REFFLD. An example of the DDS for a field reference file is shown in Figure 2.

One big advantage of a field reference file is the ability to define generic- type fields in your field reference file and then use a name that is appropriate for the file. For example, in the field reference file, you can define a file called AMOUNT. This field would have the size, editing and other criteria that you want for all amount fields on your system. Then, when you reference AMOUNT in a file definition, you can use a name that indicates the function of the AMOUNT field in this particular file, such as NETPAY or FEDTAX. This allows you to globally change the definition of a field, such as AMOUNT, if the need ever arises. It also ensures that you will always be dealing with the same field definition. No longer will you have to worry about an amount being truncated when moving an amount from one field to another. For consistency, since IDDU does not support this feature, it is not used in the field reference file shown in 2.

One big advantage of a field reference file is the ability to define generic- type fields in your field reference file and then use a name that is appropriate for the file. For example, in the field reference file, you can define a file called AMOUNT. This field would have the size, editing and other criteria that you want for all amount fields on your system. Then, when you reference AMOUNT in a file definition, you can use a name that indicates the function of the AMOUNT field in this particular file, such as NETPAY or FEDTAX. This allows you to globally change the definition of a field, such as AMOUNT, if the need ever arises. It also ensures that you will always be dealing with the same field definition. No longer will you have to worry about an amount being truncated when moving an amount from one field to another. For consistency, since IDDU does not support this feature, it is not used in the field reference file shown in Figure 2.

In the field reference file, you can also define any field editing, validation criteria or special characteristics of a field that are necessary. For example, if the field DOLLAR should always show a $, you can use the DDS keyword EDTCDE(K $).

IDDU provides a very user-friendly, easy-to-use interface to an AS/400 database. While it lacks many of the sophisticated options available in DDS, IDDU is an ideal interface for users new to the AS/400. IDDU makes defining the fields very easy. First, enter the command Start IDDU (STRIDD). You will then be presented with a menu. At this menu take option 1, "Work with data definitions." When the Select Definition Type screen comes up, take option 1, which is used for field definitions. You must also enter the name of a data dictionary. This dictionary is the one that will contain the field definitions. You can think of this data dictionary as a file that will hold all the definitions of a particular set of fields and files. If the data dictionary does not already exist, you will receive the message, "Dictionary not found. Press F22 to work with data dictionaries." If you get this message, press F22 and at the screen that comes up and follow the directions on creating a data dictionary. Be aware that a the name of the data dictionary must be the same as the library you are placing it in. For more information, refer to the IBM manual, Utilities: IDDU User's Guide.

Once you have created the data dictionary, press enter to return to the Select Definition Type screen; press enter again. A screen similar to the one shown in 3 comes up. This screen is used to define the fields for your data dictionary. It is simple to use and understand. Online help is available if something is unclear.

Once you have created the data dictionary, press enter to return to the Select Definition Type screen; press enter again. A screen similar to the one shown in Figure 3 comes up. This screen is used to define the fields for your data dictionary. It is simple to use and understand. Online help is available if something is unclear.

SQL/400 does not have a facility to define a data dictionary. The fields (columns) are defined when you create the file (table). This can be a major drawback, especially if you are trying to implement a common set of standards.

Creating the Files

The next step is to create the actual physical files. Since logical files are based on physical files and the definition and use of logical files is such a complex issue, we will not cover the definition of logical files in this article.

The DDS for the files AUTHOR, ARTICLE and ISSUE are shown in4, 5 and 6. The REF keyword indicates what file you will be using as a reference file. The R in column 29 of the fields indicates that the field is to get its definition from a field in the file named in the REF keyword. 6 shows the use of the REFFLD keyword. This keyword allows you to use a different name for the field than the one that is defined in the field reference file. The name you want to use in the file goes in columns 19 through 28, and the field whose definition you are using goes in the REFFLD keyword.

The DDS for the files AUTHOR, ARTICLE and ISSUE are shown in Figures 4, 5 and 6. The REF keyword indicates what file you will be using as a reference file. The R in column 29 of the fields indicates that the field is to get its definition from a field in the file named in the REF keyword. Figure 6 shows the use of the REFFLD keyword. This keyword allows you to use a different name for the field than the one that is defined in the field reference file. The name you want to use in the file goes in columns 19 through 28, and the field whose definition you are using goes in the REFFLD keyword.

When you define a file using a field reference file, you are not limited to only the fields in one field reference file. The REFFLD keyword also allows you to specify the name of the field reference file you want to use. This will override the file specified in the REF keyword. Also you can define new fields in the file and modify the fields being referenced. To learn more about this, read the IBM manual Programming: Data Description Specifications Reference.

IDDU requires two steps when you define the file. First you must define a record format then select that format when you define the file. To define the record format using IDDU again you use the command STRIDD. This time take option 2 from the Select Definition Type screen. Enter option 1 and the name of the record format. IDDU defaults to using the file name with a suffix of R. So for the file ARTICLE we would use a record format name of ARTICLER. This isn't mandatory, it just makes it easier to identify what record goes with what file.

On the screen that comes up specify a description of the format in the Text field and press enter. You will be shown a screen similar to the one shown in 7 (press F11 to see the text of each field). All you do is place a sequence number in front of each of the fields that you want to include in this record format. The sequence numbers should be incremented so that the fields will be in the order that you wish. When you have completed selecting the fields, press enter to accept you selection and then keep pressing enter until you are return to the Select Definition Type screen.

On the screen that comes up specify a description of the format in the Text field and press enter. You will be shown a screen similar to the one shown in Figure 7 (press F11 to see the text of each field). All you do is place a sequence number in front of each of the fields that you want to include in this record format. The sequence numbers should be incremented so that the fields will be in the order that you wish. When you have completed selecting the fields, press enter to accept you selection and then keep pressing enter until you are return to the Select Definition Type screen.

Next take option 3 to define the file. Enter option 1 with the name of the file. You will be presented with the screen shown in 8. Take an option 1 to select record formats. You will then be presented with a screen that lists all of the record formats available. Place a sequence number in front of the record format you just defined.

Next take option 3 to define the file. Enter option 1 with the name of the file. You will be presented with the screen shown in Figure 8. Take an option 1 to select record formats. You will then be presented with a screen that lists all of the record formats available. Place a sequence number in front of the record format you just defined.

There is one additional thing to mention about IDDU file definitions. Unlike DDS definitions IDDU definitions can be attached to program-described files. This allows the file to be accessed within many AS/400 program products such as Query/400. The Link Data Definition (LNKDTADFN) command is used to accomplish this link. For further information, refer to Appendix D in Utilities: IDDU User's Guide.

Using SQL to define files is not as easy as IDDU or as flexible as DDS. 9 shows the SQL statements used to define the file (table) ARTICLE. The first statement, CREATE TABLE, creates the file and the field definitions in one step. The next two statements are used to place descriptions on the fields (columns) and file (table). SQL can be very time consuming when creating more than one or two files. As you can see, SQL can be very wordy and clumsy when creating a database. SQL implementation products on other platforms usually provide a cleaner, easier-to-use front-end for database definition in SQL; hopefully, IBM will follow suit for the AS/400 sometime in the future.

Using SQL to define files is not as easy as IDDU or as flexible as DDS. Figure 9 shows the SQL statements used to define the file (table) ARTICLE. The first statement, CREATE TABLE, creates the file and the field definitions in one step. The next two statements are used to place descriptions on the fields (columns) and file (table). SQL can be very time consuming when creating more than one or two files. As you can see, SQL can be very wordy and clumsy when creating a database. SQL implementation products on other platforms usually provide a cleaner, easier-to-use front-end for database definition in SQL; hopefully, IBM will follow suit for the AS/400 sometime in the future.

Which Method to Use

Database definition languages, like any of the high-level programming languages, have their strengths and weaknesses. Certain languages are ideal for certain applications, but another language can be used in a pinch. This is also true of the three methods described in this article.

DDS is the most widely used database definition language on the AS/400, it offers access to all of the features of the AS/400 database. The main problem with DDS is that for a person new to the AS/400 it can be very difficult to learn and even harder to use. IBM's manual, Programming: Data Description Specifications Reference is far from a training manual. Many programmers who have worked on the S/38 and AS/400 still have problems with some of the areas of DDS. What DDS needs in a easy-to-use interface. IBM is attempting to provide this with CODE/400 (see Significa September 1991), but this product seems to have a high cost for something that should already be there.

IDDU had its origins on the S/36 and because of that, its features are more akin to the S/36 file structure than the AS/400 database. One of the biggest drawbacks is that only a small subset of the features available in an AS/400 database are available through IDDU. Its one redeeming feature is that it is very easy-to-use. Users new to the AS/400, whether they have a S/36 background or not, will find IDDU an easy way to define simple databases on the AS/400. IDDU is a good start, but as applications become more complex you will most likely need to turn to DDS.

SQL /400 is not an ideal method of file definition on the AS/400 at this time. SQL's strengths on the AS/400 lie in its ability to access databases, not define them. Some people may disagree with this but until IBM provides a more comprehensive database definition facility to SQL/400, I would stick to DDS.

Unfortunately, once you choose a method of data base implementation the AS/400 does not provide an easy way to convert one type of specification to another. It is possible to write a program that would do this for you. Just make sure to take into account the different levels of database function support each method handles.

What's right for you? If you're a beginner or your applications are simple, give IDDU a try. If your applications are complex or you are doing anything that requires full access to the features of the AS/400 database, use DDS. None of the methods of database definition on AS/400 are ideal, but all we can do is work with what's available.

Bibliography:

Programming: Data Description Specifications Reference

SQL/400 Reference

Utilities: IDDU User's Guide

Relational Database Design -- Part 3

Figure 1 Completed object specifications

 Figure 1: Completed Object Specifications Object Name: AUTHOR Purpose: An author is an individual that writes an article for possible publication in the magazine. Properties: Contact information Technical competence Writing competence Data Elements: Author ID Name Home street Home city Home state Home ZIP Home phone Work street Work city Work state Work ZIP Work phone Work extension Fax number Years of technical experience Main system Writing skill level Integrity Rules: Object Name: ARTICLE Purpose: An article is a story, program or any piece of information that is submitted for publication in the magazine. This can come from inside and/or outside sources. Properties: Description of article Status of article Data Elements: Article ID Article name Author ID Article description Article status Date in-house Issue date Integrity Rules: Author ID must already exist in AUTHOR. If article status is scheduled the issue date must exist in IS SUE. Object Name: ISSUE Purpose: An issue is a monthly edition of the magazine. Properties: Editorial information Data Elements: Issue date Theme Buyer's Guide Editorial due date Printer due date Integrity Rules: 
 
 Relational Database Design -- Part 3
 
 Figure 2 DDS for Field Reference File
 
 Figure 2: DDS for Field Reference File +... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ************************************************************************** * Magazine System Master Data Dictionary * * ************************************************************************** * Field Definitions ************************************************************************** A R MGREFR TEXT('Field Reference File') A ARTDSC 150A COLHDG('Article' 'Description') A ARTID 5P 0 COLHDG('Article' 'ID') A ARTNAM 35A COLHDG('Article' 'Name') A ARTSTS 3A COLHDG('Article' 'Status') A ATHID 5P 0 COLHDG('Author' 'ID') A ATHNAM 35A COLHDG('Author' 'Name') A DTINHS 7P 0 COLHDG('Date' 'Article' 'In-house') A EDDUDT 7P 0 COLHDG('Editorial' 'Due By' 'Date') A HMCITY 25A COLHDG('Home' 'City') A FAXNBR 10P 0 COLHDG('Fax' 'Phone' 'Number') A HMPHON 10P 0 COLHDG('Home' 'Phone' 'Number') A HMSTAT 2A COLHDG('Home' 'State') A HMSTRT 25A COLHDG('Home' 'Street') A HMZPCD 10A COLHDG('Home' 'Zip' 'Code') A ISSBYG 50A COLHDG('Issue' 'Buyers' 'Guide') A ISSDAT 7P 0 COLHDG('Issue' 'Date') A ISSTHM 50A COLHDG('Theme' 'of' 'Issue') A PRDUDT 7P 0 COLHDG('Date' 'Due at' 'Printers') A SYSTEM 5A COLHDG('System' 'Type') A TECHEX 2P 0 COLHDG('Years' 'Tech' 'Experience') A WKCITY 25A COLHDG('Work' 'City') A WKPHON 10P 0 COLHDG('Work' 'Phone' 'Number') A WKSTAT 2A COLHDG('Work' 'State') A WKSTRT 25A COLHDG('Work' 'Street') A WKZPCD 10A COLHDG('Work' 'Zip' 'Code') A WRKEXT 5A COLHDG('Work' 'Phone' 'Extension') A WRSKLV 3A COLHDG('Writing' 'Skill' 'Level') +... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 
 
 Relational Database Design -- Part 3
 
 Figure 3 IDDU Create Field definitions screen
 
 Figure 3: IDDU Create Field Definitions Screen Create Field Definitions Type information, press Enter to create. Field type . . . : 1=Character, 2=Numeric, 3=DBCS Field size . . . : 1-32766 (character), 1-31 (numeric), 4-32766 (DBCS) Decimal positions: 0-31 (numeric), blank (character, DBCS) More options . . : Y=Yes -------Field--------- Dec More Name Type Size Pos Opt Text ARTDSC____ 1 150__ __ _ Article_Description__________________________ ARTID_____ 2 5____ 0_ _ Article_ID___________________________________ ARTNAM____ 1 35___ __ _ Article_Name_________________________________ ARTSTS____ 1 3____ __ _ Article_Status_______________________________ ATHID_____ 2 5____ 0_ _ Author_ID____________________________________ ATHNAM____ 1 35___ __ _ Author_Name__________________________________ DTINHS____ 2 7____ 0_ _ Date_Aricle_In-house_________________________ __________ _ _____ __ _ _____________________________________________ __________ _ _____ __ _ _____________________________________________ F3=Exit F12=Cancel F14=Copy previous line 
 
 Relational Database Design -- Part 3
 
 Figure 4 DDS for AUTHOR file
 
 Figure 4: DDS for AUTHOR File +... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ************************************************************************** * Object Name: AUTHOR * * Purpose: An author is an individual that writes an article * for possible publication in the magazine. * * Integrity Rules: . * ************************************************************************** A REF(MGREF) A R AUTHORR TEXT('Author Master File') A ATHID R A ATHNAM R A HMSTRT R A HMCITY R A HMSTAT R A HMZPCD R A HMPHON R A WKSTRT R A WKCITY R A WKSTAT R A WKZPCD R A WRKEXT R A FAXNBR R A TECHEX R A SYSTEM R A WRSKLV R A K ATHID +... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 
 
 Relational Database Design -- Part 3
 
 Figure 5 DDS for ARTICLE file
 
 Figure 5: DDS for ARTICLE File +... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ************************************************************************** * Object Name: ARTICLE * * Purpose: An article is a story, program or any pice of * information that is submitted for publication * in the magazine. This can come from inside * and/or outside sources. * * Integrity Rules: Author ID must already exist in object AUTHOR. * If articel status is scheduled the Issue date * must exist in object ISSUE. * ************************************************************************** A REF(MGREF) A R ARTICLER TEXT('Article Master File') A ARTID R A ARTNAM R A ATHID R A ARTDSC R A ARTSTS R A DTINHS R A ISSDAT R A K ARTID +... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 
 
 Relational Database Design -- Part 3
 
 Figure 6 DDS for ISSUE file
 
 Figure 6: DDS for ISSUE File +... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ************************************************************************** * Object Name: ISSUE * * Purpose: An issue is a monthly edition of the magazine. * * Integrity Rules: . * ************************************************************************** A REF(MGREF) A R ISSUER TEXT('Issue Master File') A ISDT R REFFLD(ISSDAT) A ISTH R REFFLD(ISSTHM) A ISBG R REFFLD(ISSBYG) A EDDD R REFFLD(EDDUDT) A PRDD R REFFLD(PRDUDT) A K ISDT +... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 
 
 Relational Database Design -- Part 3
 
 Figure 7 IDDU Create Record Format screen
 
 Figure 7: IDDU Create Record Format Screen Create and Select Field Definitions Definition . . . . . . : ARTICLER Dictionary . . . . . : $GAUJOH Position to . . . . . . . __________ Field, sequence (0-99999) Type sequence numbers (0-99999), (and Field), press Enter. Type field, press F6 to create. Seq Field Date Length Type Text ___80 __________ ___10 ARTID 09/18/91 5,0 Packed Article ID ___20 ARTNAM 09/18/91 35 Char Article Name ___30 ATHID 09/18/91 5,0 Packed Author ID ___40 ARTDSC 09/18/91 150 Char Article Description ___50 ARTSTS 09/18/91 3 Char Article Status ___60 DTINHS 09/18/91 7,0 Packed Date Article In-house ___70 ISSDAT 09/18/91 7,0 Packed Issue Date Article Scheduled _____ ATHNAM 09/18/91 35 Char Author's Name _____ EDDUDT 09/18/91 7,0 Packed Editorail Due in by Date More... F3=Exit F5=Refresh F6=Create field definition F11=Display names only F12=Cancel F20=Renumber 
 
 Relational Database Design -- Part 3
 
 Figure 8 IDDU Create File screen
 
 Figure 8: IDDU Create File Screen Create File Definition Definition . . . . . : TEST2 Dictionary . . . . . : $GAUJOH Type choices, press Enter. Record format option . . . . . . . 1 1=Create and/or select formats 2=Create default format and select fields For choice 2=Create default format: Format . . . . . . TEST2R____ Name Select key field sequence . . . . . . N Y=Yes, N=No Long comment . . . . . _ 1=Select, 4=Remove Text . . . . . . . . . __________________________________________________ F3=Exit F12=Cancel 
 
 Relational Database Design -- Part 3
 
 Figure 9 SQL to create ARTICLE file
 
 Figure 9: SQL to Create ARTICLE File CREATE TABLE ARTICLE (ARTID DECIMAL (5) NOT NULL WITH DEFAULT, ARTNAM CHAR (35) NOT NULL WITH DEFAULT FOR SBCS DATA, ATHID DECIMAL (5) NOT NULL WITH DEFAULT, ARTDSC CHAR (150) NOT NULLWITH DEFAULT FOR SBCS DATA, ARTSTS CHAR (3) NOT NULL WITH DEFAULT FOR SBCS DATA, DTINHS DECIMAL (7) NOT NULL WITH DEFAULT, ISSDAT DECIMAL (7) NOT NULL WITH DEFAULT) COMMENT ON ARTICLE (ARTID IS 'Article ID', ARTNAME IS 'Article Name', ATHID IS 'Author ID', ARTDSC IS 'Article Description', ARTSTS IS 'Article Status', DTINHS IS 'Date Article In-house', ISSDAT IS 'Issue Date')