04 - SQL way of creating and maintaining Database objects

Using SQL to create and maintain database objects on AS/400 (now known as IBM iSeries or IBM Power Systems) is efficient and flexible. Here’s a guide on how to create, manipulate, and manage database objects using SQL:

1. Creating Database Objects

Creating a Table

Use the CREATE TABLE statement to define a new 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 schema/library 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.

Inserting Data

Use the INSERT INTO statement to add records to the table.

sql

INSERT INTO MYLIB.MYTABLE (FLD1, FLD2, FLD3) VALUES (‘Record1’, 123, 45.67);

Updating Data

Use the UPDATE statement to modify existing records.

sql

UPDATE MYLIB.MYTABLE

SET FLD2 = 456

WHERE FLD1 = ‘Record1’;

Deleting Data

Use the DELETE FROM statement to remove records.

sql

DELETE FROM MYLIB.MYTABLE

WHERE FLD1 = ‘Record1’;

Querying Data

Use the SELECT statement to retrieve data from the table.

sql

SELECT * FROM MYLIB.MYTABLE;

2. Altering Table Structure

Adding a Column

Use the ALTER TABLE statement to add a new column.

sql

ALTER TABLE MYLIB.MYTABLE

ADD COLUMN FLD4 DATE;

Dropping a Column

Use the ALTER TABLE statement to remove an existing column.

sql

ALTER TABLE MYLIB.MYTABLE

DROP COLUMN FLD4;

3. Managing Indexes and Views

Creating an Index

Use the CREATE INDEX statement to create an index on a table.

sql

CREATE INDEX MYLIB.MYINDEX ON MYLIB.MYTABLE (FLD2);

Creating a View

Use the CREATE VIEW statement to create a view that selects specific data.

sql

CREATE VIEW MYLIB.MYVIEW AS

SELECT FLD1, FLD3

FROM MYLIB.MYTABLE

WHERE FLD2 > 100;

4. Managing Database Objects

Dropping a Table

Use the DROP TABLE statement to delete a table.

sql

DROP TABLE MYLIB.MYTABLE;

Using Interactive SQL (STRSQL)

To execute these SQL statements on an AS/400 system:

1. Sign on to the AS/400 system.
2. Use the STRSQL command to start the SQL command interface.
3. Enter your SQL statements and execute them.

By using SQL to manage your database objects on AS/400, you can efficiently handle complex database operations and maintain your database with ease. If you have any specific questions or need more detailed examples, feel free to ask!