Notes

CREATE PROCEDURE MYPROCEDURE1 ( )
DYNAMIC RESULT SETS 1

P1 : BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT *
FROM EMPLOYEE
WHERE WORKDEPT = 'A00';

OPEN cursor1;
END P1@

CALL MYPROCEDURE1 ()@

CREATE OR REPLACE PROCEDURE SP_UPDATE (
IN IN_EMPNO CHAR(6),
IN IN_FIRSTNAME VARCHAR (100),
OUT OUT_PESAN VARCHAR (100))
BEGIN
UPDATE EMPLOYEE SET FIRSTNME = IN_FIRSTNAME
WHERE EMPNO = IN_EMPNO;
SET OUT_PESAN = 'UPDATE BERHASIL';
END@

CALL SP_UPDATE(000010, 'KARIN', ?)@

CREATE FUNCTION myfunction( )
RETURNS INTEGER
NO EXTERNAL ACTION

F1 : BEGIN ATOMIC
RETURN SELECT count(*) FROM EMPLOYEE;
END@

select myfunction() from sysibm.sysdummy1@

CREATE TABLE BACKUP_EMP LIKE EMPLOYEE@

CREATE TRIGGER setDefault_Salary
NO CASCADE BEFORE INSERT ON backup_emp
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
WHEN (n.salary IS NULL)
SET n.salary = 50000@

CREATE TABLE audit (mytimestamp timestamp, comment varchar(1000))@
CREATE TRIGGER audit_emp_sal
AFTER UPDATE OF salary ON employee
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
MODE DB2SQL
INSERT INTO audit
VALUES (CURRENT TIMESTAMP, ' Employee ' || o.empno || ' salary changed from ' || CHAR(o.salary)
|| ' to ' || CHAR(n.salary) || ' by ' || USER)@

UPDATE EMPLOYEE SET SALARY = 20000
WHERE EMPNO = '000010'@

SELECT * FROM AUDIT

INSERT INTO BACKUP_EMP VALUES
('200350', 'KARINA', 'R', 'SARI', 'E31', '1111' , '2018-04-09', 'DESIGNER','17',
'F', '1998-05-21' , '1000', '1000000', '2018.00', CURRENT TIMESTAMP, CURRENT TIMESTAMP, NULL)@
INSERT INTO BACKUP_EMP VALUES
('200350', 'KARINA', 'R', 'SARI', 'E31', '1111' , '2018-04-09', 'DESIGNER','17',
'F', '1998-05-21' , NULL , '1000000', '2018.00', CURRENT TIMESTAMP, CURRENT TIMESTAMP, NULL)@