Packaging with DB2 on zOS

This guide covers the specifics of DB2 on zOS packaging behavior. To be successful with packaging on DB2 on zOS it is best to familiarize yourself with this guide.

Packager Behavior when packaging re-runnable stored logic

There are limitations to stored logic on DB2 on zOS. You can package four types of stored logic with limited packaging method options listed below and only Trigger works as expected. If you want to package view, procedure, or function, you must use the SQLFILE packaging method. You can use flexible folders such as a folder named SQL or the metadata.properties file.

Learn more about these topics here:
Best Practices for Developer Workflows
Using the metadata.properties file

Stored logic that can be packaged:

Trigger (SQLFILE OR STOREDLOGIC)

View (SQLFILE)

Procedure (SQLFILE)

Function (SQLFILE)

Packaging restrictions for DB2 on zOS:

Liquibase Enterprise Version 8.1 and earlier does not support the create or replace command.
DB2 on zOS Version 12.1.5 and earlier does not support the create or replace command.

`create tablespace` and `create table` limitations

Both create tablespace and create table can be in the same packager run using DDL. For these commands to run successfully, you must first commit create tablespace in a separate commit before committing create table and only after that run the packager.

How to commit both `create tablespace` and `create table` to the same packager:

Commit create tablespace in it's own commit.

developerWorking/sql_code/sql_direct/create_tablespace.sql

CREATE TABLESPACE A2759TS IN A2759966 USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 DSSIZE 2 G MAXPARTITIONS 256 SEGSIZE 32 BUFFERPOOL BP16K0 CCSID EBCDIC LOCKMAX SYSTEM LOCKSIZE ROW MAXROWS 255;

2. Commit create table in a separate commit.

developerWorking/sql_code/ddl/create_table_on_tablespace.sql

CREATE TABLE TABLE_04 (
    "ID" INTEGER NOT NULL,
    "NAME" VARCHAR(49))
    IN A2759966.A2759TS;

3. Run the packager.

Alter Statements

The Alter View statement regenerates a view using an existing view definition at the current server. Alter view is primarily used during Db2 migration or when Db2 maintenance is applied.

To change a view definition, you must drop the view and create a new view using the Create View statement.

Alter procedure can only be used for SQL - native
Alter Function can only be used for compiled SQL scalar.
Drop/Create cannot be used because of issues connected with dependent objects:
- Views - If there is a dependent view on the view which we want to drop, then when we drop the view the dependent view is also dropped.
- Procedures - cannot drop procedure if there is a dependent procedure.
- Functions cannot drop function if there is a dependent procedure.
CLPPlus does not support deployment of CREATE PROCEDURE or CREATE FUNCTION on partitioned DB2 on zOS.