Let us start with the DB2 AS400 part. It is recommended that
you have client access express installed on your PC and be able to access AS400
trough the IBM ISeries navigator or through 5250 terminal. However, you should
be able to connect through a simple telnet client program provided with
Microsoft Windows.
After you log on to your AS400 machine, you should be able
to create a library for our tests (I prefer to call this library TESTNET).
Whatever you choose as a name, make sure you remember it for replacing
purposes.
After creating the library, access the SQL/400 command
window through STRSQL.
Listing 1 - Create Table Listing
/* Creating table TESTNET.TBLDEPT */
CREATE TABLE TESTNET.TBLDEPT
( DEPTID INTEGER NOT NULL , DEPTNM VARCHAR (30) NOT NULL , UNIQUE (DEPTID) ) ;
Listing 2 - Insert Procedure
CREATE PROCEDURE TESTNET.INSDEPT (
IN P_ID INTEGER ,
IN P_DESC VARCHAR(30) )
LANGUAGE SQL
SPECIFIC TESTNET.INSDEPT
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
INSERT INTO TESTNET . TBLDEPT
( DEPTID , DEPTNM ) VALUES ( P_ID , P_DESC ) ;
COMMENT ON SPECIFIC PROCEDURE TESTNET.INSDEPT
IS 'INSERT DEPARTMENT' ;
Listing 3 - Update Procedure
CREATE PROCEDURE TESTNET.UPDDEPT (
IN P_ID INTEGER ,
IN P_DESC VARCHAR(30) )
LANGUAGE SQL
SPECIFIC TESTNET.UPDDEPT
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
UPDATE TESTNET . TBLDEPT
SET DEPTNM = P_DESC
WHERE DEPTID = P_ID ;
Listing 4 - Delete Procedure
CREATE PROCEDURE TESTNET.DELDEPT (
IN P_ID INTEGER )
LANGUAGE SQL
SPECIFIC TESTNET.DELDEPT
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
DELETE FROM TESTNET . TBLDEPT
WHERE DEPTID = P_ID ;
COMMENT ON SPECIFIC PROCEDURE TESTNET.DELDEPT
IS 'DELETE DEPARTEMENT' ;
Listing 5 - Select All Procedure
CREATE PROCEDURE TESTNET.SELDEPT ( )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC TESTNET.SELDEPT
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
BEGIN
DECLARE DEPTCUR CURSOR FOR SELECT * FROM TESTNET. TBLDEPT ;
OPEN DEPTCUR;
END ;
COMMENT ON SPECIFIC PROCEDURE TESTNET.SELDEPT
IS 'DEPARTEMENT' ;
Listing 5 - Select By Department Id
CREATE PROCEDURE TESTNET.SDEPTID (IN P_ID INTEGER )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC TESTNET.SDEPTID
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
BEGIN
DECLARE DEPTCUR CURSOR FOR SELECT * FROM TESTNET. TBLDEPT WHERE P_ID = DEPTID;
OPEN DEPTCUR;
END ;
COMMENT ON SPECIFIC PROCEDURE TESTNET.SDEPTID
IS 'DEPARTEMENT BY ID';
