Let’s start the move from DDS physical files to DDL tables. As you’ll see, there’s more to this move than a change of name. Keep reading to find out what changes!
If DDS physical files are the large container ships that hold your data in a turbulent and ever-changing sea of users, business logic, communications, and programs, SQL tables are the luxury yachts. They provide the same functionality as their DDS counterparts but offer a slew of additional features that IBM keeps increasing with each OS release. The many possibilities provided by the CREATE TABLE SQL instruction are the best proof of this silly little nautical metaphor. In a previous TechTip, I used a made-up Invoice Master physical file to explain the SELECT SQL instruction. In case you don’t remember, here’s a table describing the fields on that file:
Table 1: The InvMst File Description |
|||
Column Name |
Data Type |
Length |
Column Description |
ItemID |
Character |
15,0 |
Item ID |
LotNbr |
Decimal |
13,0 |
Lot Number |
ExpDate |
Date |
N/A |
Expiration Date |
WHID |
Decimal |
8,0 |
Warehouse ID |
ShelfID |
Decimal |
12,0 |
Shelf ID |
ItemUn |
Character |
3 |
Item Units |
ItemQty |
Decimal |
9,2 |
Item Quantity |
Let’s analyze the main features of CREATE TABLE and then see how the InvMst physical file definition would look if it were created as an SQL table.
Here’s the CREATE TABLE syntax:
CREATE TABLE <table SQL name> [FOR SYSTEM NAME <table system name>]
(<column_1_SQL_name> FOR COLUMN <column_1_System_name> <column1_data_type> <column_1_default_options> <colum_1_identity_options> <column_1_constraint_options>],
… ,
<column_N_SQL_name> FOR COLUMN <column_N_System_name> <column_N_data_type> <column_N_default_options> <colum_N_identity_options> <column_N_constraint_options>])
It seems complicated, but it’s just a different way of specifying information. Of course, there are a lot features available, but I’m saving them for later. Let’s apply this syntax to our InvMst physical file:
CREATE TABLE MYLIB/INVMST
(ITEMID CHAR ( 15) NOT NULL WITH DEFAULT,
LOTNBR DEC ( 13, 0) NOT NULL WITH DEFAULT,
EXPDATE DATE NOT NULL WITH DEFAULT,
WHID DEC ( 8, 0) NOT NULL WITH DEFAULT,
SHELFID DEC ( 12, 0) NOT NULL WITH DEFAULT,
ITEMUN CHAR ( 3) NOT NULL WITH DEFAULT,
ITEMQTY DEC ( 9, 2) NOT NULL WITH DEFAULT)
The indentation is not mandatory, but (as usual) it improves readability and future maintenance, so please use it at all times! Depending on the naming convention used, the first line of this statement can either be as shown above, for the *SYS naming convention, or this for the *SQL naming convention:
CREATE TABLE MYLIB.INVMST
To do things the “totally SQL” way, you’d also replace MYLIB with a schema name, using, for instance, the MYSCHEMA schema created on the previous TechTip. Next, notice the column definitions. I kept the same names, but I could have chosen longer, more-descriptive ones. The data type definitions should look familiar, especially because the PF data types have a direct SQL data type equivalent in this case.
Then, there’s the NOT NULL WITH DEFAULT “column option” after each data type. This is related to the way physical files and tables handle the absence of data. A DDS-defined file assumes, unless you specify otherwise, that the absence of data in a numeric field, regardless of its particular type, should return a zero when the field is read. Things work differently in a DDL-defined table, however. By default, the absence of value is returned as null value. The NOT NULL WITH DEFAULT serves the purpose of forcing the PF’s default behavior onto the table.
Now let’s start adding bells and whistles to the table definition:
- Taking advantage of the fact that SQL allows longer names, I’ll provide more descriptive column names and maintain the existing names as “system names.”
- I know that ItemID, WHID, and ShelfID form this table’s primary key, so I can also add that definition here.
- I also know that the record format name of an SQL table is the same as the table name by default, so I’ll change that to prevent problems when InvMst is used in an RPG module.
- To demonstrate how you can use the table definitions to replace RPG code, I’ll add a Last_Changed timestamp column, common in audited files. The difference here is that I’ll have the database engine take care of the column update operations instead of writing RPG code each time a record is inserted or updated.
Let’s see how the new CREATE TABLE statement looks with these changes:
CREATE TABLE MYSCHEMA.TBL_INVENTORY_MASTER
FOR SYSTEM NAME INVMST
(ITEM_ID FOR COLUMN ITEMID
CHAR ( 15) NOT NULL WITH DEFAULT,
LOT_NUMBER FOR COLUMN LOTNBR
DEC ( 13, 0) NOT NULL WITH DEFAULT,
EXPIRATION_DATE FOR COLUMN EXPDATE
DATE NOT NULL WITH DEFAULT,
WAREHOUSE_ID FOR COLUMN WHID
DEC ( 8, 0) NOT NULL WITH DEFAULT,
SHELF_ID FOR COLUMN SHELFID
DEC ( 12, 0) NOT NULL WITH DEFAULT,
ITEM_IN_UNIT FOR COLUMN ITEMUN
CHAR ( 3) NOT NULL WITH DEFAULT,
ITEM_QUANTITY FOR COLUMN ITEMQTY
DEC ( 9, 2) NOT NULL WITH DEFAULT,
LAST_CHANGED FOR COLUMN LSTCHG
TIMESTAMP NOT NULL
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
PRIMARY KEY (ITEM_ID, WAREHOUSE_ID, SHELF_ID))
RCDFMT ITMMSTR
The first thing you notice is that this is a much longer statement. In part, this is related to indentation. I chose to divide each column’s definition into two lines to remind you that you’re in no way restricted by column or line restrictions.
Now let’s look at the implementation of those four changes:
- Providing more descriptive names was achieved by changing the column name to longer names (up to 128 characters long) and including a FOR COLUMN definition for each column to maintain compatibility with any programs that might have been using the old, shorter names.
- A clear definition of the table’s primary key is isolated in this statement:
PRIMARY KEY (ITEM_ID, WAREHOUSE_ID, SHELF_ID)
There’s another way to do this, which you’ll see in one of the next examples.
- Overcoming the “table name is the same as the record format name” issue that I hear so many people complaining about is easily achieved with RCDFMT ITMMSTR, which “renames” the table record format to ITMMSTR.
- Finally, I’ve added an audit column that “automagically” keeps track of the row’s last update timestamp, with this definition:
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
Note that I could have omitted the data type definition of the LAST_CHANGED column, because the database engine would have figured it out by itself(!), but I chose not to do this because it might be confusing.
I changed the naming convention to SQL and used a schema name (MYSCHEMA) instead of the library name; I also used a longer table name. Let’s put this last feature to the test with an INSERT statement:
INSERT INTO MYSCHEMA.TBL_INVENTORY_MASTER
(ITEM_ID, LOT_NUMBER, EXPIRATION_DATE, WAREHOUSE_ID,
SHELF_ID, ITEM_IN_UNIT, ITEM_QUANTITY)
VALUES('A123', 1, Date('2015-12-31'), 24, 12, 'KG', 100)
Notice anything missing? I didn’t specify the LAST_CHANGED column, but if you run a SELECT after this insert, you’ll see that the database manager took care of its value. This is only the tip of the iceberg; in the next TechTip, I’ll revisit these concepts and add some more!
LATEST COMMENTS
MC Press Online