Support Article
Enterprise AES 7.1 Not Generating Correct DDL for DB2zOS
SA-3715
Summary
Installing new instance of AES 7.1 Enterprise on db2zos database and while importing the rules it requires the creation of a few table and modification of existing ones. The problem is that the Generated DDL does not have create tablespaces or create tables for the AUX tables that are needed for the BLOB columns.
Error Messages
Example
CREATE TABLE AESDataSchema.pegaam_cluster_data
(
ClusterName VARCHAR (128),
ProductionLevel VARCHAR (32),
SysNodeID VARCHAR (64),
pxCreateDateTime TIMESTAMP,
pxCreateOpName VARCHAR (128),
pxCreateOperator VARCHAR (128),
pxCreateSystemID VARCHAR (32),
pxInsName VARCHAR (255),
pxObjClass VARCHAR (96),
pxUpdateDateTime TIMESTAMP,
pxUpdateOpName VARCHAR (128),
pxUpdateOperator VARCHAR (128),
pxUpdateSystemID VARCHAR (32),
pyLabel VARCHAR (64),
pzInsKey VARCHAR (255) NOT NULL,
pzPVStream BLOB (5M),
ClusterURL VARCHAR (512),
CONSTRAINT PEGAAM_CLUSTER_DATA_PK PRIMARY KEY (pzInsKey) ) in DATABASE DB2GLOC
Steps to Reproduce
Import the AES_07_01.jar using the import wizard and manually download the DDL
Root Cause
The root cause of this problem is software use/operation error. The generated DDL will create the tablespaces and tables as needed for the db2zos database.
Resolution
The explanation for this behavior is as follows:
The import wizard uses implicit tablespace creation (the default of the provided database). The "create table" statements include a "IN DATABASE database-name" clause which will implicitly create the tablespaces and auxilliary table of any blob columns.
Please reference DB2 for z/OS “CREATE TABLE” Syntax. http://www-01.ibm.com/support/knowledgecenter/#!/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sql_createtable.dita
http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sql_createtable.dita%23db2z_sql_createtable__ctbllob?lang=en-us
As documented in the PRPC Installation Guide for a db2zos database you need to set the database DB2 z/OS database name for schema import. The configuration parameter is used in the "IN DATABASE database-name" clause.
When applied the DDL the tablespaces for the main and auxilliary tables and the tables are all created as expected.
Published January 31, 2016 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.