SQL DDL Data Definition Language Copyright 1992 1993

SQL – DDL Data Definition Language Copyright 1992, 1993 BY DMC CONSULTING, INC. ALL RIGHTS RESERVED 5650 West Central Avenue, Suite B, Toledo, OH 43615 AS/400 is a trademark of International Business Machines Corporation SQL/400 03/03/2003 DMC Consulting, 1993

SQL – DDL – Creating Schemas SQL/400 03/03/2003 DMC Consulting, 1993

Objectives Upon completion of this segment, you will have a thorough understanding of: • • Collections and Schemas Creating Schemas Schema Contents Schema Commands You will also perform the following task: • Create a schema SQL/400 03/03/2003 DMC Consulting, 1993

What is a Schema Simply put, a schema (also called collection) is a group of tables. These tables are logically linked together for easier management. On the System i, schemas are implemented as Libraries within the DB 2 database. Not just a library though, but also all of the management objects within the schema that are used by the SQL engine to manage the schema. Knowing what these objects are and how to use them is very practical knowledge. Schemas contain the following objects by default: SQL/400 03/03/2003 DMC Consulting, 1993

What is a Schema Catalog SYSTABLES SYSCOLUMNS SYSINDEXES SYSKEYS SYSVIEWDEP SYSPACKAGE SYSCHKCST SYSCSTCOL SYSCSTDEP SYSKEYCST SYSREFCST SYSTRIGCOL SYSTRIGDEP SYSTRIGGER SYSTRIGUPD Syntax: CREATE SCHEMA schema-name SQL/400 03/03/2003 DMC Consulting, 1993 Journals QSQJRN 0001

What is a Schema SET CURRENT SCHEMA schema-name This statement sets the default schema for created objects as well as the default schema for objects referenced in other SQL statements, but not procedures and functions. Correctly specifying the current schema can greatly simplify SQL syntax by removing the location to find SQL/400 03/03/2003 DMC Consulting, 1993

What is a Schema SET OPTION DFTRDBCOL = schema-name When creating procedures and functions, its possible to define the default database schema (collection) using the SET OPTION statement as shown below: CREATE PROCEDURE procedure-name LANGUAGE SQL SET OPTION DFTRDBCOL = schema-name BEGIN procedure-code; END SQL/400 03/03/2003 DMC Consulting, 1993

What is a Schema SET PATH = “schema-name”, schema-name, … The SQL path is used to resolve unqualified references to User Defined Functions , User Defined Data Types, and Stored Procedures. The default behavior of SQLPATH changes based upon the naming convention in use: *SYS Naming, SQLPATH = “*LIBL” *SQL Naming, SQLPATH = “QSYS, QSYS 2, user-id” To best use this feature, create a procedure in each environment (test, production, etc) Define that procedure shown below, replacing the schema name with the data schema for that environment. SQL/400 03/03/2003 DMC Consulting, 1993

What is a Schema CREATE PROCEDURE Set_Schema LANGUAGE SQL BEGIN SET CURRENT SCHEMA schema-name END In any other stored procedure, call the Set_Schema procedure at the beginning of the code to correctly set the data schema being used in this environment. It should avoid the need for hard coded schema names. CREATE PROCEDURE Other_Proc LANGUAGE SQL BEGIN CALL Set_Schema; … SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Tables The CREATE statement allows the user to build a new table. All of the fields (columns) must also be defined. Their data type, length, and null attributes must be described. And optional constraints may be defined as well. There are three different version of the CREATE TABLE statement: 1) CREATE TABLE table-name (definition) 2) CREATE TABLE LIKE other-table 3) CREATE TABLE AS (select-statement) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement CREATE TABLE table-name (definition) CREATE TABLE lib/table (field 1 datatype(length) null, field 2 datatype(length) null, field 3 datatype(length) null, etc. . . , constraint 1, constraint 2, etc. . ) The library/schema name is optional. For compatibility with RPG IV, the file and field names should not exceed 10 characters. For RPG/400, field names cannot exceed 6 characters. SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Data Types CHAR – Up to 32, 767 bytes of alphanumeric data (32. 765 if Null capable) DECIMAL – Up to 63 digits of packed numeric data NUMERIC – Up to 63 digits of zoned numeric data. DATE – Date Data type using a 4 digit year TIME – Time data type in 24 hr format TIMESTAMP - Timestamp data type include date, time, and microseconds (6 digits) INTEGER (INT) – Large Integer (4 Bytes) SMALLINT – A small integer (2 Bytes) BIGINT – A big integer (8 Bytes) FLOAT – Floating Point data type (8 Bytes) VARCHAR - Up to 32, 740 bytes of character data (32. 739 if Null capable) LONG VARCHAR – Variable Length Character Data up to the length of the row BINARY – Up to 32, 767 bytes of binary data (32, 765 if Null Capable) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Data Types VARBINARY - Up to 32, 740 bytes of binary data (32. 739 if Null capable) GRAPHIC – Up to 16, 383 double byte characters (16, 382 if Null capable) VARGRAPHIC – Up tp 16370 double byte characters (16, 369 if Null capable) LONG VARGRAPHIC – A string of double byte characters up to the length of the row DOUBLE – Double precision floating point (8 Bytes) REAL – Single Precision Floating point (4 Bytes) BLOB – Variable length binary string up to 2 gigabytes in length CLOB – Variable length string of characters up to 1 gigabyte in length DATALINK – A string up to 32, 718 bytes containing a URL (32, 717 if Null capable) DBCLOB – Variable length double byte character string up to 1 gigabyte in length User-Defined (UDT) – Custom functions built with the CREATE FUNCTION statement ROWID - System generated key that uniquely defines a row (40 Bytes) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement When defining fields their null attribute can be: Omitted: To allow null values NOT NULL: To prevent null values from being loaded into a field NOT NULL WITH DEFAULT: To use default values in place of null values CREATE TABLE MYTEST (PART CHAR(10) NOT NULL WITH DEFAULT, OQTY NUMERIC(5, 0) NOT NULL, COST NUMERIC(7, 2) NOT NULL WITH DEFAULT 19. 99, DESC CHAR(20)) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Constraints Primary key: A set of fields which must contain unique, non-null values. Unique key: A set of fields which must contain unique values. Foreign key: A set of fields which must match the key to the parent file. Check: A set of fields whose values must meet a defined criteria. SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Constraints CONSTRAINT name type (values) CONSTRAINT CUST_KEY PRIMARY KEY (FIELDA) CONSTRAINT VALID_CUST FOREIGN KEY (CUSTNO) REFERENCES CUST (CNUMBER) ON DELETE CASCADE ON UPDATE NO ACTION CONSTRAINT FIELDC_VALIDITY CHECK (FIELDC <> 0 ) CONSTRAINT VALID_ORDER UNIQUE (ORDER#) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement CREATE TABLE LIKE other-table CREATE TABLE MYTABLE LIKE YOURTABLE INCLUDING IDENTITY or CREATE TABLE MYTABLE LIKE YOURTABLE EXCLUDING IDENTITY SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement CREATE TABLE AS (Select-Statement) CREATE TABLE MYTABLE AS (SELECT * FROM YOURTABLE) WITH DATA CREATE TABLE MYTABLE AS (SELECT * FROM YOURTABLE) WITH NO DATA CREATE TABLE MYTABLE AS (SELECT KEYFLD, SUM(QTYFLD) AS TOTAL FROM YOURTABLE GROUP BY KEYFLD) WITH DATA SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Identity Columns What is an Identity Column? An identity column is a column in an SQL table which is partially under system control, and is used to create a unique key to a database. These must defined at the time of the table creation, though they can altered after the fact, but not added. An Identity column must be defined as Decimal, Numeric, Integer, Smallint, or Bigint. It has a starting and ending value as well as an increment. If the CYCLE option is used, then when the ending value is reached, the next new record uses the starting value. Essentially “wrapping” the identity value. SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Identity Columns These columns are ideal as keys to a database table. Using an identity column in place of a meaningful data item such as Part Number, Employee Number, or Order Number enables us to freely change those values and treat them as easily maintained data columns. Part. ID Part# Description 1 A 101 Widget 2 A 102 Sprocket 3 C 100 Cog SQL/400 03/03/2003 DMC Consulting, 1993 Price 19. 99

SQL Create Statement Identity Columns CREATE TABLE PARTMAST (PARTKEY DECIMAL (6, 0) NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 100001 INCREMENT BY 1 CYCLE CACHE 100 NO ORDER), PARTDESC AS CHAR(30)) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Identity Columns CREATE TABLE PARTMAST (PARTKEY DECIMAL (6, 0) NOT NULL GENERATED BY DEFAULT AS IDENTITY (START with 100001 INCREMENT BY 1 MINVALUE 100001 MAXVALUE 900000 NO CYCLE NO CACHE ORDER), PARTDESC AS CHAR(30)) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Identity Columns GENERATED ALWAYS – System assigns value, no value can be specified for this field on INSERT statements GENERATED BY DEFAULT – System assigns value if no value was specified for this field on INSERT statements START WITH x – Beginning value for inserted rows INCREMENT BY x – Amount added to value for each row CYCLE – After reaching the MAXVALUE restart at MINVALUE NO CYCLE – No restart after reaching MAXVALUE (until alter table xxxxx alter column yyyyy restart is run) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement CACHE x – Allocate x identity values in memory for faster INSERTS (must be 2 or more) NO CACHE – Do not preallocate identity values ORDER – Identity values must be issued in order NO ORDER – Identity values may be issued in any order IDENTITY_VAL_LOCAL() – Function returns the last Identity value issued at “same level”. This means within the same function or procedure. Do not use in a before trigger. INSERT INTO PARTMAST (PARTDESC) VALUES(‘NEW PART’) INSERT INTO PARTHIST SELECT * FROM PARTMAST WHERE PARTID = IDENTITY_VAL_LOCAL() *See ROWID for information OVERRIDING options on Insert SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Row ID Columns What is a ROWID Column? ROWIDs are similar to identity columns in that they uniquely identify rows within the database. Unlike an identity column, ROWID is a datatype with a 40 byte length. Either the system will assign the ROWID, or one can be assigned by the user. But in the later case it must be a valid row-id generated by the database. CREATE TABLE PARTMAST (PARTKEY ROWID GENERATED ALWAYS, PARTDESC AS CHAR(30)) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Row ID Columns The ROWID() Function can be used to translate a character string back into a row id data type. This presumes the character string was created by casting a valid row id value into a character string with the CAST function. When inserting data into a table with either a ROW ID or an Identity Column the following clauses may precede the values clause. SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Row ID Columns OVERRIDING USER VALUE – This tells the SQL Engine to ignore row id or identity column values given on an insert, and that the system should generate appropriate values instead. OVERRIDING SYSTEM VALUE – This tells the SQL Engine to recognize row id or identity column values given on an insert, and that the system should not generate these values. INSERT INTO PARTMAST OVERRIDING USER VALUES(0, ’New Part’) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Table Creation Scripts It is useful to have an easy executed and modified script/program available to delete and recreate tables, indexes and views. Because the SQL Engine has certain predictable patterns when searching for an optimal index, controlling the sequence they are created in, can improve performance. Text members executed with RUNSQLSTM can be used for this purpose. CREATE TABLE PARTMAST (PARTID DECIMAL (6, 0) GENERATED BY DEFAULT AS IDENTITY (START WITH 100001 MINVALUE 100001 MAXVALUE 100010 INCREMENT BY 1 NO CYCLE NO CACHE NO ORDER), PARTDESC CHAR(30)); RUNSQLSTM SRCFILE(lib/file) SRCMBR(mbr) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Table Creation Scripts Besides using RUNSQLSTM, other options such as these may be used: This task can be performed by QMQRY script files. An SQL script file executed via i. Series Navigator A High Level Language program with embedded SQL A stored procedure. SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement CREATE PROCEDURE PARTMAST_BUILD LANGUAGE SQL MODIFIES SQL DATA BEGIN CREATE TABLE PARTMAST (PARTID DECIMAL (6, 0) GENERATED BY DEFAULT AS IDENTITY (START WITH 100001 MINVALUE 100001 MAXVALUE 100010 INCREMENT BY 1 NO CYCLE NO CACHE NO ORDER), PARTDESC CHAR(30)); CALL PARTMAST_ADD_INDEX; END SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement A complete set of procedures may be used to manage a table and all its related objects. CREATE PROCEDURE PARTMAST_ADD_INDEX LANGUAGE SQL MODIFIES SQL DATA BEGIN ALTER TABLE PARTMAST ADD CONSTRAINT PARTMAST_PRI_KEY PRIMARY KEY (PARTID); CREATE UNIQUE INDEX PARTMAST_UNIQUE_DESC ON PARTMAST(PARTDESC); END SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement CREATE PROCEDURE PARTMAST_DROP LANGUAGE SQL MODIFIES SQL DATA BEGIN CALL PARTMAST_DROP_INDEX; DROP TABLE PARTMAST; END CREATE PROCEDURE PARTMAST_DROP_INDEX LANGUAGE SQL MODIFIES SQL DATA BEGIN ALTER TABLE PARTMAST DROP CONSTRAINT PARTMAST_PRI_KEY; DROP INDEX PARTMAST_UNIQUE_DESC; END SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement CREATE PROCEDURE PARTMAST_REINDEX LANGUAGE SQL MODIFIES SQL DATA BEGIN CALL PARTMAST_DROP_INDEX; CALL PARTMAST_ADD_INDEX; END SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Materialized Query CREATE TABLE INVTOTALS AS (WITH TEMP AS (SELECT DEC(SUM(IQTY), 7, 0) as TOTAL, IITEM FROM INVDET GROUP BY IITEM) SELECT MITEM, MDESC, MITMPRC, MITMCST, TOTAL FROM ITMMST LEFT JOIN TEMP ON MITEM = IITEM) DATA INITIALLY DEFERRED REFRESH DEFERRED ENABLE QUERY OPTIMIZATION MAINTAINED BY USER See IBM’s White paper for more details at http: //www-03. ibm. com/servers/enable/site/education/wp/438 a. pdf SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement DATA INITIALLY DEFERRED indicates that the MQT is not loaded with data at the time of creation. Use DATA INITIALLY IMMEDIATE to load it with data right away if necessary REFRESH DEFERRED indicates that the user may choose to refresh the table at any time by issuing the REFRESH TABLE mqt-name statement. This clause is not optional. ENABLE QUERY OPTIMIZATION indicates that the SQL engine may choose to use this MQT to satisfy other SQL statements issued against the underlying table(s). To deactivate this feature, use DISBALE QUERY OPTIMIZATION instead. MAINTAINED BY USER indicates that the user is responsible for loading the correct data into the MQT using INSERT, UPDATE, DELETE, and REFRESH TABLE statements. This is not optional. SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Managing Materialized Queries The data within an MQT is static, meaning that once data is loaded into it, it will not change regardless of what happens to the underlying tables. Programmers may choose to manually update the MQT with INSERT, UPDATE, and DELETE statements, or more likely, they can repopulate the table with current data using the REFRESH TABLE statement such as the one shown below. REFRESH TABLE INVTOTALS QAQQINI File To enable the use of MQTs within the SQL optimizer, the QAQQINI file must be updated, as the default settings do not allow the use of Materialized Queries during the optimization process. These settings are needed: MATERIALIZED_QUERY_TABLE_USAGE = *ALL MATERIALIZED_QUERY_TABLE_REFRESH_AGE = *ANY SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Global Temporary Table DECLARE GLOBAL TEMPORARY TABLE ORDER_BATCH (ORDER DECIMAL(5, 0), CUSTOMER DECIMAL (7, 0), DUEDATE, STATUS CHAR(1)) DECLARE GLOBAL TEMPORARY TABLE ORDER_BATCH AS (SELECT ORDER, CUSTOMER, DUEDATE, STATUS FROM ORDMAST WHERE BATCHNO = 12345) WITH DATA SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Global Temporary Table DECLARE GLOBAL TEMPORARY TABLE ORDER_BATCH LIKE ORDMAST SELECT * FROM SESSION/ORDER_BATCH SQL/400 03/03/2003 DMC Consulting, 1993

Triggers and Constraints SQL/400 03/03/2003 DMC Consulting, 1993

Check Constraints RPG Program Check Constraint Database File The Check constraint enforces basic edit rules such as: • PLTNO <> 0 • STATUS <> “ “ • YNFLD = “Y” OR YNFLD = “N” • SHIFT > 0 AND SHIFT < 4 • DAYOFWEEK(WEDT) = 1 SQL/400 03/03/2003 DMC Consulting, 1993

Check Constraints ALTER TABLE PARTMAST ADD CONSTRAINT PARTMAST_PRICE_NOT_ZERO CHECK (PRICE > 0) ALTER TABLE ORDMAST ADD CONSTRAINT ORDMAST_VALID_STATUS CHECK (STATUS = ‘A’ OR STATUS = ‘P’ OR STATUS = ‘C’) ALTER TABLE PARTMAST ADD CONSTRAINT PARTMAST_VALID_DATA CHECK (TYPE = ‘L’ AND PRICE = 0 OR TYPE <> ‘L’) SQL/400 03/03/2003 DMC Consulting, 1993

Primary/Unique Key Constraints Child File - Primary Key Pugsley Adams Wed. Adams Bobby Brady Cindy Brady Greg Brady Jan Brady Marsha Brady ALTER TABLE PARTMAST ADD CONSTRAINT PARTMAST_PRI_KEY PRIMARY KEY (PARTID) SQL/400 03/03/2003 DMC Consulting, 1993

Primary/Unique Key Constraints Child File -Unique Key Pugsley Adams Wed. Adams null Brady Greg Brady Jan Brady Marsha Brady ALTER TABLE PARTMAST ADD CONSTRAINT PARTMAST_UNIQUE_DESC UNIQUE KEY (PARTDESC) SQL/400 03/03/2003 DMC Consulting, 1993

Referential Constraints Child File Pugsley Wed. Bobby Cindy Greg Jan Marsha SQL/400 03/03/2003 DMC Consulting, 1993 Adams Brady Brady RPG Program Referential Constraint Parent File Adams Brady Cleaver Munster

Referential Constraints ALTER TABLE INVDETAIL ADD CONSTRAINT “VALID PART” FOREIGN KEY (PARTID) REFERENCES PASRTMAST (PARTID) ON DELETE CASCADE ON UPDATE NO ACTION ON UPDATE… No Action – Do not allow parents records with dependents to have their parent key column(s) changed. . (Journalling required) Restrict - Do not allow parents records with dependents to have their parent key column(s) changed. (Journalling not required) ON DELETE… No Action – Do not allow parents records with dependents to be deleted. (Journalling required) Restrict - Do not allow parents records with dependents to be deleted. (Journalling not required) Cascade - If a parent record is deleted, cascade the delete to the dependent file, and delete all records associated with that parent record. Set Default - If a parent record is deleted, Set the key fields in the dependent file to their default values for all records associated with that parent record. Set Null- If a parent record is deleted, Set the key fields in the dependent file to null for all records associated with that parent record. SQL/400 03/03/2003 DMC Consulting, 1993

Indexes and Views kldjfhglkdjhfgpow this is jkhgoiwouiopuwe kldjfhglkdjhfgp really important fgowouiopuwe kldjfhglkdjhfg information for me oiwiouiopuwe kldjfhglkdjhfg selected out from ubilgloiopuwe kldjfhglkdjhfg the jumble of data igowoiopuwe kldjfhglkdjhfg that fills up all my llswiwuiopuwe kldjfhglkdjhfgp data tables hdfigiwuopuwe kldjfhglkdjhfgpowehtljxncbvljkhdfgoiwluiopuwe SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Index CREATE {UNIQUE} INDEX lib/index-name ON lib/table-name (field 1 {ASC/DESC}, field 2 {ASC/DESC}, field 3 {ASC/DESC}. . . ) CREATE UNIQUE INDEX CUSTNAME ON CUST (CNAME) CREATE INDEX CUSTCITY ON CUST (CSTATE, CCITY) CREATE INDEX INVAVAIL ON INVDET (IITEM, IQTY DESC) SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Encoded Vector Index CREATE ENCODED VECTOR INDEX lib/index ON lib/table (field 1 {ASC/DESC}, field 2 {ASC/DESC}, field 3 {ASC/DESC}. . . ) WITH n DISTINCT VALUES; CREATE ENCODED VECTOR INDEX USER 001/CUSTST ON CUST USER 001/CUST (CSTATE) WITH 60 DISTINCT VALUES; SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Views CREATE VIEW view-name AS SELECT field 1, field 2, field 3, etc. . . FROM base-table WHERE condition SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Summary Views ORDER 1 1 1 DATE 01/15/1999 01/16/1999 01/17/1999 01/20/1999 01/21/1999 LOC A 10 A 10 QTY 100 100 100 500 CREATE VIEW view-name (column 1, column 2, etc. . . ) AS SELECT column 1, Sum(column 2), AVG(column 3), etc. . . FROM base-table GROUP BY column 1 SQL/400 03/03/2003 DMC Consulting, 1993

SQL Create Statement Alias A A Alias D B C D E MULTI-MEMBER TABLE CREATE ALIAS PARTMAST_MBR_A FOR PARTMAST (A) SQL/400 03/03/2003 DMC Consulting, 1993

Triggers SQL/400 03/03/2003 DMC Consulting, 1993

Trigger Programs Data Base Changes Trigger Program Data Base Triggers can be written in SQL or as external programs in languages such as RPGLE. If the trigger program needs to abort the update, end it by sending an escape message with a severity of 50 or higher. Up to 300 Triggers can be associated with a single file. i. Series Navigator provides a wizard to assist in creating triggers. SQL/400 03/03/2003 DMC Consulting, 1993

Triggers CREATE TRIGGER your-lib. CUST_TRIGGER AFTER INSERT ON KPFLIB. CUST REFERENCING NEW TABLE AS N FOR EACH STATEMENT MODE DB 2 SQL BEGIN insert into your-lib. custhist (cnumber, cname) select n. cnumber, n. cname from n; END; SQL/400 03/03/2003 DMC Consulting, 1993

Triggers New/Old: Referencing New Row as xxx - for insert Referencing Old Row as yyy – for delete Referencing New Row as xxx Old Row as yyy – for update (use Row syntax with DB 2 SQL or DB 2 ROW modes) Referencing New Table as xxx - for insert Referencing Old Table as yyy – for delete Referencing New Table as xxx Old Table as yyy – for update (use Table syntax with DB 2 SQL mode and not BEFORE triggers) SQL/400 03/03/2003 DMC Consulting, 1993

Triggers CREATE TRIGGER your-lib. CUST_TRIGGER AFTER INSERT ON KPFLIB. CUST REFERENCING NEW ROW AS N FOR EACH ROW MODE DB 2 ROW BEGIN insert into your-lib. custhist (cnumber, cname) values(n. cnumber, n. cname); END; SQL/400 03/03/2003 DMC Consulting, 1993

Triggers For Each: Use FOR Each ROW to force the trigger to run for each row affected. Use FOR EACH STATEMENT to force the trigger to run once for each SQL statement that might trigger it. This trigger will run even if no rows are affected. Mode: DB 2 ROW fires the trigger as each row is affected, and allows access to individual rows only. DB 2 SQL fires after all the statement is complete, and allows access to either rows or tables. SQL/400 03/03/2003 DMC Consulting, 1993

Triggers CREATE TRIGGER Load_Candidates INSTEAD OF INSERT ON ORDERS REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB 2 SQL INSERT INTO CANDIDATES VALUES(NEW_ROW. A 1, NEW_ROW. A 2 SQL/400 03/03/2003 DMC Consulting, 1993 )

Summary • Schemas • Table Creation • Scripts • Materialized Queries • Global Temporary Tables • Indexes • Views • Constraints • Triggers SQL/400 03/03/2003 DMC Consulting, 1993