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
You cannot run both create tablespace and create table in the same packager run using DDL. For these commands to run successfully, you must run create tablespace in a separate packager before packaging create table as depicted below.
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;
developerWorking/sql_code/ddl/create_table_on_tablespace.sql
CREATE TABLE TABLE_04 (
"ID" INTEGER NOT NULL,
"NAME" VARCHAR(49))
IN A2759966.A2759TS;
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.
Add {{db2jcc_license_cisuz.jar}} to the application class path
There are two avenues to add {{db2jcc_license_cisuz.jar}} to your application class path.
Environment Variable route
Specify a full path to the license jar in an environment variable with the name: DATICAL_DB2Z_LICENSE_JAR
In this example of a Linux setting, the path can be:export DATICAL_DB2Z_LICENSE_JAR="/home/db2jcc_license_cisuz.jar"
It is important to note that the path can be different than the example above.
DATICAL_DB2Z_LICENSE_JAR allows the db2 license file to be visible for Liquibase Enterprise. When Liquibase Enterprise uses CLPPlus, and CLPPlus needs to see the DB2 license file, you need to set the CLASSPATH environmental variable to the same value as DATICAL_DB2Z_LICENSE_JAR.
License Jar Directory route
Place the license JAR file name db2jcc_license_cisuz.jar into a default directory located here: $INSTAL_DIR/repl/lib
The complete license jar directory route will look like this:
$INSTAL_DIR/repl/lib/db2jcc_license_cisuz.jar