Understanding SQL Objects
SQL objects are database structures created and managed using Data Definition Language (DDL).
| Object Type | Purpose | Example |
|---|---|---|
| Table | Stores data in rows and columns | CREATE TABLE MYLIB/CUSTOMERS (...) |
| View | Virtual table based on a query | CREATE VIEW ACTIVE_ORDERS AS SELECT ... |
| Index | Improves query performance | CREATE INDEX CUST_IDX ON CUSTOMERS(LASTNAME) |
| Stored Procedure | Reusable SQL + procedural logic | CREATE PROCEDURE UPDATE_INVENTORY ... |
| Function | Returns a value (scalar or table) | CREATE FUNCTION GET_TAX_RATE(...) |
| Sequence | Generates unique numbers | CREATE SEQUENCE ORDER_SEQ START WITH 1000 |
Pro Tip: All SQL objects are stored in the system catalog (SYSIBM schema).
Creating SQL Objects
Using IBM i Access Client Solutions (ACS)
Open Run SQL Scripts.
Connect to your IBM i system.
Paste and run DDL statements.
-- Example: Create a Customer Table
CREATE TABLE MYLIB/CUSTOMERS (
CUSTID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE),
FIRSTNAME VARCHAR(50),
LASTNAME VARCHAR(50),
EMAIL VARCHAR(100),
CREATEDATE TIMESTAMP DEFAULT CURRENT TIMESTAMP,
PRIMARY KEY (CUSTID)
);
CREATE TABLE MYLIB/CUSTOMERS (
CUSTID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE),
FIRSTNAME VARCHAR(50),
LASTNAME VARCHAR(50),
EMAIL VARCHAR(100),
CREATEDATE TIMESTAMP DEFAULT CURRENT TIMESTAMP,
PRIMARY KEY (CUSTID)
);
Using IBM i Navigator (Web GUI)
Navigate to Databases → Your Database → Schemas → MYLIB.
Right-click → New → Table → Use the GUI wizard.
Modifying SQL Objects
ALTER Statements
-- Add a column
ALTER TABLE MYLIB/CUSTOMERS
ADD COLUMN PHONE CHAR(15);
-- Modify column size
ALTER TABLE MYLIB/CUSTOMERS
ALTER COLUMN EMAIL SET DATA TYPE VARCHAR(150);
-- Drop a column (use with caution!)
ALTER TABLE MYLIB/CUSTOMERS
DROP COLUMN PHONE;
ALTER TABLE MYLIB/CUSTOMERS
ADD COLUMN PHONE CHAR(15);
-- Modify column size
ALTER TABLE MYLIB/CUSTOMERS
ALTER COLUMN EMAIL SET DATA TYPE VARCHAR(150);
-- Drop a column (use with caution!)
ALTER TABLE MYLIB/CUSTOMERS
DROP COLUMN PHONE;
Warning: Some ALTER operations recreate the table → plan for downtime or use QSQXRLF to avoid locks.
Tracking Changes
Enable Journaling
-- Start journaling on library and table
STRJRNPF FILE(MYLIB/CUSTOMERS) JRN(MYLIB/QSQJRN) IMAGES(*BOTH);
STRJRNPF FILE(MYLIB/CUSTOMERS) JRN(MYLIB/QSQJRN) IMAGES(*BOTH);
Query Change History
-- View journal entries
SELECT * FROM TABLE(QSYS2.Display_Journal(
'MYLIB', 'QSQJRN', OBJECT_NAME => 'CUSTOMERS'
)) WHERE ENTRY_TYPE IN ('PT', 'PX', 'UP', 'DL');
SELECT * FROM TABLE(QSYS2.Display_Journal(
'MYLIB', 'QSQJRN', OBJECT_NAME => 'CUSTOMERS'
)) WHERE ENTRY_TYPE IN ('PT', 'PX', 'UP', 'DL');
System Catalog Views
-- Who changed what and when?
SELECT
SYSTEM_TABLE_SCHEMA,
SYSTEM_TABLE_NAME,
LAST_ALTERED_TIMESTAMP,
LAST_USED_TIMESTAMP
FROM QSYS2.SYSTABLES
WHERE SYSTEM_TABLE_NAME = 'CUSTOMERS';
SELECT
SYSTEM_TABLE_SCHEMA,
SYSTEM_TABLE_NAME,
LAST_ALTERED_TIMESTAMP,
LAST_USED_TIMESTAMP
FROM QSYS2.SYSTABLES
WHERE SYSTEM_TABLE_NAME = 'CUSTOMERS';
Security & Authority
Grant Permissions
GRANT SELECT, INSERT, UPDATE ON MYLIB/CUSTOMERS TO USER SALES_TEAM;
GRANT ALL PRIVILEGES ON MYLIB/CUSTOMERS TO USER DEV_ADMIN;
GRANT ALL PRIVILEGES ON MYLIB/CUSTOMERS TO USER DEV_ADMIN;
Revoke Access
REVOKE INSERT, UPDATE ON MYLIB/CUSTOMERS FROM USER SALES_TEAM;
Best Practice: Use profiles/groups instead of individual users.
Best Practices Checklist
| Practice | Why It Matters |
|---|---|
| Version control DDL scripts | Enables rollback and audit |
Use schemas for environments (DEVLIB, TESTLIB, PRODLIB) | Prevents cross-contamination |
| Journal all production objects | Required for auditing & recovery |
| Refresh statistics regularly | ANALYZE TABLE or PRTSQLINF |
| Document dependencies | Views → Tables → Indexes |
| Test ALTER in DEV first | Avoid production locks |