Deploying in DB2 UDB for iSeries

Many methods exist for deploying stored procedures, triggers, and functions in DB2 UDB for iSeries. You can use the DB2 UDB for Linux, UNIX, and Windows client; the IBM iSeries Navigator for Windows; or commands natively on the iSeries server.

Using the Development Center

If you are connected remotely and have the DB2 UDB for Linux, UNIX, and Windows client, then you can use the Development Center for deploying stored procedures. Triggers and functions for iSeries are not supported in the Development Center. Within this environment, you can only deploy from one iSeries server to another.

There are two methods that you can use within this environment. The first method is to use the Export Wizard from the pop-up menu which appears after right-clicking on the Stored Procedure folder. The wizard will step you through, allowing you to pick and choose the procedures to export. The Export Wizard gives you the option of saving the procedures to a script, or exporting the procedures as a project. In both cases, a .zip file is created.

Saving them to a script will enable you to import the procedures one at time; however, you will have to unzip the file before you can import. You can import using the Import Wizard provided from inside the Development Center. Alternatively for DB2 LUW, the DB2 Command Line Processor can be used to issue the CREATE PROCEDURE statements directly.

Exporting as a project allows you to use the Deployment Tool, available from the Tools menu in the Development Center. This tool provides an interface where you can pick and choose which procedures you want to deploy to the target server.

Tip

Saving to a file allows you to deploy across platforms, as long as the procedure is portable.

Using iSeries Navigator and CL Commands in iSeries

The IBM iSeries Navigator for Windows can be used as well to help with deployment. You can drill down to the schema you are working on, multi-select the objects you want to deploy, and use the context menu (via right-clicking on the mouse) to select the Generate SQL option, as shown in Figure 11.21. The output of Generate SQL is a series of CREATE statements for the objects selected.

Figure 11.21. Using the IBM iSeries Navigator to generate SQL.

The Generate SQL option launches a wizard, shown in Figure 11.22, which allows you to add or remove objects to the list. The wizard also prompts you to select where you want the output; to a file or in the Run and SQL Script window. Saving it to a file saves the output on the iSeries server. Opening in a SQL script window allows you to modify, run, and/or save to a file on the remote client.

Figure 11.22. iSeries Generate SQL GUI.

The Generate SQL option can be used to generate DDL for tables, indexes, aliases, views, user-defined types, or SQL functions. If a table has triggers associated with it, then selecting the table will also generate the DDL for the corresponding triggers. Once the file is saved, you can launch a Run an SQL script window for a database on the target server. You can then load the file from the remote client's file system and execute the script through this interface. Figure 11.23 shows an example of a generated script.

Figure 11.23. Sample iSeries script.

 -- Generate SQL -- Version:                     V5R3M0 040528 -- Generated on:                06/16/04 19:55:43 -- Relational Database:         S103FAWM -- Standards Option:            DB2 UDB iSeries CREATE SEQUENCE DB2ADMIN.SERVICE_RQ_SEQ     AS SMALLINT     START WITH 1     INCREMENT BY 1     MINVALUE 1     MAXVALUE 5000     NO CYCLE CACHE 50 NO ORDER ; CREATE TABLE DB2ADMIN.ACT (     ACTNO SMALLINT NOT NULL ,     ACTKWD CHAR(6) CCSID 37 NOT NULL ,     ACTDESC VARCHAR(20) CCSID 37 NOT NULL ,     CONSTRAINT DB2ADMIN.Q_DB2ADMIN_ACT_ACTNO_00001 PRIMARY KEY( ACTNO ) ) ; CREATE TABLE DB2ADMIN.AUDIT (     EVENT_TIME TIMESTAMP DEFAULT NULL ,     DESC VARCHAR(100) CCSID 37 DEFAULT NULL ) ; SET PATH "DB2ADMIN","SYSIBM" ; CREATE PROCEDURE DB2ADMIN.UPDATE_DEPARTMENT ( )     LANGUAGE SQL     SPECIFIC DB2ADMIN.UPD_DEPT     NOT DETERMINISTIC     MODIFIES SQL DATA     CALLED ON NULL INPUT     UD : BEGIN     -- Declare variable     DECLARE V_DEPTNO CHAR ( 3 ) ;     -- Declare returncode     DECLARE SQLSTATE CHAR ( 5 ) ;     DECLARE C_DEPT CURSOR WITH HOLD FOR     SELECT DEPTNO     FROM DEPARTMENT     FOR UPDATE OF LOCATION ;     -- Declare condition handler     DECLARE CONTINUE HANDLER FOR SQLSTATE '24504' , SQLSTATE '24501'     L1 : LOOP -- (1)     LEAVE L1 ;     END LOOP ;     -- Procedure logic     OPEN C_DEPT ;     SAVEPOINT A ON ROLLBACK RETAIN CURSORS ;      FETCH FROM C_DEPT INTO V_DEPTNO ;                                  -- (2)      UPDATE DEPARTMENT SET LOCATION = 'FLOOR1' WHERE CURRENT OF C_DEPT ;-- (3)      COMMIT ;                                                           -- (4)     FETCH FROM C_DEPT INTO V_DEPTNO ;                                   -- (5)     COMMIT ;  -- (6)     UPDATE DEPARTMENT SET LOCATION = 'FLOOR2' WHERE CURRENT OF C_DEPT ; -- (7)     FETCH FROM C_DEPT INTO V_DEPTNO ;                                   -- (8)     UPDATE DEPARTMENT SET LOCATION = 'FLOOR3' WHERE CURRENT OF C_DEPT ; -- (9)     COMMIT ;                                                            -- (10)     FETCH FROM C_DEPT INTO V_DEPTNO ;                                   -- (11)     UPDATE DEPARTMENT SET LOCATION = 'FLOOR4' WHERE CURRENT OF C_DEPT ; -- (12)     ROLLBACK ;  -- (13)     FETCH FROM C_DEPT INTO V_DEPTNO ;  -- (14)     UPDATE DEPARTMENT SET LOCATION = 'FLOOR5' WHERE CURRENT OF C_DEPT ; -- (15)     CLOSE C_DEPT ;     RETURN 0 ;     END UD ; 

If the file output was saved on the iSeries server, the file can be transferred to a target server and the RUNSQLSTM CL command can be used to execute the script. For example, if the file is named EXPROUTINE, contains a member EXPROUTINE, and is saved in library TARGET, you would issue the following command:

 RUNSQLSTM SRCFILE(TARGET/EXPROUTINE) SRCMBR(EXPROUTINE) NAMING(*SQL) 

If you do not want to use the GUI tools, then you can use CL commands on the server to save and restore objects. Basically, you would need to save the objects on the source server and restore them on the target server. For example, if you wanted to save to a file (rather than to tape or some other device), you would need to create a SAVEFILE, save the objects to that file, transfer the file to the target server, and restore the objects from that file.

Figure 11.24 lists a the series of commands that you would execute in order to copy all program (stored procedures and triggers) and service program (functions) objects, from the SOURCE library and save them to a file called SAVEIT, and the corresponding restore command to restore to the TARGET library.

Figure 11.24. Save and restore on iSeries.

 On source server: CRTSAVF FILE(SOURCE/SAVEIT) SAVOBJ OBJ(*ALL) LIB(SOURCE) DEV(*SAVF) OBJTYPE(*PGM *SRVPGM) SAVF(SOURCE/SAVIT) On target server: RSTOBJ OBJ(*ALL) SAVLIB(SOURCE) DEV(*SAVF) OBJTYPE(*PGM *SRVPGM) SAVF(SOURCE/SAVIT) RSTLIB(TARGET) 

There are likely more methods and combination of methods that you can use to deploy stored procedures. Which method you choose will depend on your level for comfort and familiarity with the tools shown to you.