Data Description Specifications (DDS) is a powerful tool for creating and maintaining database objects on the AS/400 (now IBM iSeries or IBM Power Systems). Here’s a step-by-step guide to using DDS:
1. Access PDM (Programming Development Manager)
- Step: Use the PDM tool to access the source file that contains the DDS specifications.
2. Edit the Source File with SEU (Source Entry Utility)
- Step: Use SEU to edit the source file and define the record format, field names, field types, and field descriptions.
3. Compile the Source File
- Step: Use option 14 to compile the source file and create the database file.
4. Check the Database File
- Step: Use the work file command to check the database file and ensure it was created correctly.
5. Enter Data with DFU (Data File Utility)
- Step: Use DFU to enter data into the newly created database file.
Example DDS Code
Here’s a simple example of DDS code to create a physical file with a single record format:
plaintext
FD MYFILE
FLD1 10A
FLD2 5 0
FLD3 8 2
In this example:
- MYFILE is the name of the file.
- FLD1 is a 10-character alphanumeric field.
- FLD2 is a 5-digit numeric field.
- FLD3 is an 8-digit numeric field with 2 decimal places.
Resources
For more detailed tutorials and examples, you can refer to the following resources:
- AS400 Tutorial – Compile DDS Database File: A YouTube video tutorial on how to use DDS to create a database file.
- AS400 Tutorial – Coding a Database File: Another YouTube video tutorial on coding a database file using DDS.
- IBM Documentation: IBM’s official documentation on DDS concepts and coding rules.
Would you like more detailed information on any specific aspect of DDS or additional examples?
SQL way of creating and maintaining Database objects AS/400
Using SQL to create and manage database objects on the AS/400 (IBM iSeries) can be efficient and flexible. Here’s a guide to help you get started:
Creating Database Objects with SQL
**1. Creating a Table:
sql
CREATE TABLE MYLIB.MYTABLE (
FLD1 CHAR(10) NOT NULL,
FLD2 INTEGER NOT NULL,
FLD3 DECIMAL(8, 2) NOT NULL,
PRIMARY KEY (FLD1)
);
In this example:
- MYLIB is the library (similar to a schema) where the table will be created.
- MYTABLE is the name of the table.
- FLD1 is a character field with a length of 10.
- FLD2 is an integer field.
- FLD3 is a decimal field with 8 digits, 2 of which are after the decimal point.
**2. Inserting Data:
sql
INSERT INTO MYLIB.MYTABLE (FLD1, FLD2, FLD3) VALUES (‘Record1’, 123, 45.67);
This statement inserts a record into MYTABLE with specified values for FLD1, FLD2, and FLD3.
**3. Updating Data:
sql
UPDATE MYLIB.MYTABLE
SET FLD2 = 456
WHERE FLD1 = ‘Record1’;
This statement updates the value of FLD2 for the record where FLD1 is ‘Record1’.
**4. Deleting Data:
sql
DELETE FROM MYLIB.MYTABLE
WHERE FLD1 = ‘Record1’;
This statement deletes the record where FLD1 is ‘Record1’.
**5. Querying Data:
sql
SELECT * FROM MYLIB.MYTABLE;
This statement retrieves all records from MYTABLE.
**6. Altering Table Structure:
sql
ALTER TABLE MYLIB.MYTABLE
ADD COLUMN FLD4 DATE;
This statement adds a new column FLD4 of type DATE to MYTABLE.
**7. Dropping a Table:
sql
DROP TABLE MYLIB.MYTABLE;
This statement deletes the table MYTABLE.
Managing Indexes and Views:
**1. Creating an Index:
sql
CREATE INDEX MYLIB.MYINDEX ON MYLIB.MYTABLE (FLD2);
This statement creates an index on FLD2 to improve query performance.
**2. Creating a View:
sql
CREATE VIEW MYLIB.MYVIEW AS
SELECT FLD1, FLD3
FROM MYLIB.MYTABLE
WHERE FLD2 > 100;
This statement creates a view named MYVIEW that displays FLD1 and FLD3 from MYTABLE where FLD2 is greater than 100.
Using Interactive SQL (STRSQL):
To execute these SQL statements on an AS/400 system:
- Sign on to the AS/400 system.
- Use the STRSQL command to start the SQL command interface.
- Enter your SQL statements and execute them.
These examples provide a basic framework for managing database objects on AS/400 using SQL. If you have more specific needs or questions, feel free to ask!
