1.Connect to DB2 z/OS 11 to ADS 17 Dev -30.
2.Expand any schema.
3.Open Query analyzer.
4.Run below script to create procedure.
------ Create table "EMPLOYEE" in "ADB" schema----------------
CREATE TABLE "EMPLOYEE" (
"EMPNO" CHAR(6) FOR MIXED DATA NOT NULL,
"FIRSTNME" VARCHAR(12) FOR MIXED DATA NOT NULL DEFAULT 'RAGHAV',
"MIDINIT" CHAR(1) FOR MIXED DATA,
"LASTNAME" VARCHAR(15) FOR MIXED DATA NOT NULL,
"WORKDEPT" CHAR(3) FOR MIXED DATA,
"PHONENO" CHAR(4) FOR MIXED DATA,
"HIREDATE" DATE,
"JOB" CHAR(8) FOR MIXED DATA,
"EDLEVEL" SMALLINT NOT NULL,
"SEX" CHAR(1) FOR MIXED DATA,
"BIRTHDATE" DATE,
"SALARY" DECIMAL(9,2),
"BONUS" DECIMAL(9,2),
"COMM" DECIMAL(9,2),
CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY("EMPNO")
)
GO
COMMENT ON TABLE "EMPLOYEE" IS 'Employee table working '
GO
----- create procedure ----------
CREATE PROCEDURE "ADB"."UPDATE_SALARY_IF"
(IN EMPLOYEE_NUMBER CHAR(6), IN RATING SMALLINT)
LANGUAGE SQL
BEGIN
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR NOT_FOUND
SIGNAL SQLSTATE '20000' SET MESSAGE_TEXT = 'EMPLOYEE NOT FOUND';
IF (RATING = 1)
THEN UPDATE EMPLOYEE
SET SALARY = SALARY * 1.10, BONUS = 1000
WHERE EMPNO = EMPLOYEE_NUMBER;
ELSEIF (RATING = 2)
THEN UPDATE EMPLOYEE
SET SALARY = SALARY * 1.05, BONUS = 500
WHERE EMPNO = EMPLOYEE_NUMBER;
ELSE UPDATE EMPLOYEE
SET SALARY = SALARY * 1.03, BONUS = 0
WHERE EMPNO = EMPLOYEE_NUMBER;
END IF;
END
GO
Table and procedure created successfully under ADB schema.
5.Now open "Alter procedure GUI" or try to display create script using right click menu :Script Object to Window As",
We can not see complete script, by which we created procedure.
Also see this Procedure_Example 2
1.First run create table "EMPLOYEE" script mentioned above.