DDL for Tables created by PeopleCode & File Pre-Processors:
You must run the PeopleCode pre-processor to generate the database tables that Change Impact Analysis reads and analyzes.
The PeopleCode pre-processor generates two tables:
• PSCIAPCXREF
• PSCIAPCXREFTIME
You must have permission to create, delete from and write to these files. If you have full privileges, you can create these files prior to using Change Impact Analyzer.
****************************************************************************************************************
Note. Database administrators may consider assigning user IDs and passwords that grant read-only access to the database. However, users need create and write access to these work tables used by Change Impact Analyzer.
****************************************************************************************************************
The File Pre-processor generates the following database tables that Change Impact Analysis reads to perform analysis.
• PSFILEPROCESSRUN
• PSSQLXREFDEFN
• PSSQLXREFITEM
• PSFILESQLXREFDEFN
• PSFILEXREFDEFN
Note. You must have permission to create, delete from and write to these files. If you have full privileges, you run the pre-processor and create these files beforehand. DDL for above tables for DB2/Unix, DB2/OS390:
-- Table 1 - PSCIAPCXREF
CREATE TABLE OWNERID.PSCIAPCXREF (
PROGSEQ INT NOT NULL
,REFOBJECTID1 SMALLINT NOT NULL
,REFOBJECTVALUE1 CHAR(60) NOT NULL
,REFOBJECTID2 SMALLINT NOT NULL
,REFOBJECTVALUE2 CHAR(60) NOT NULL
,REFOBJECTID3 SMALLINT NOT NULL
,REFOBJECTVALUE3 CHAR(60) NOT NULL
,REFOBJECTID4 SMALLINT NOT NULL
,REFOBJECTVALUE4 CHAR(60) NOT NULL
,REFOBJECTID5 SMALLINT NOT NULL
,REFOBJECTVALUE5 CHAR(60) NOT NULL
,REFOBJECTID6 SMALLINT NOT NULL
,REFOBJECTVALUE6 CHAR(60) NOT NULL
,REFOBJECTID7 SMALLINT NOT NULL
,REFOBJECTVALUE7 CHAR(60) NOT NULL
,OBJECTID1 SMALLINT NOT NULL
,OBJECTVALUE1 CHAR(60) NOT NULL
,OBJECTID2 SMALLINT NOT NULL
,OBJECTVALUE2 CHAR(60) NOT NULL
,OBJECTID3 SMALLINT NOT NULL
,OBJECTVALUE3 CHAR(60) NOT NULL
,OBJECTID4 SMALLINT NOT NULL
,OBJECTVALUE4 CHAR(60) NOT NULL
,OBJECTID5 SMALLINT NOT NULL
,OBJECTVALUE5 CHAR(60) NOT NULL
,OBJECTID6 SMALLINT NOT NULL
,OBJECTVALUE6 CHAR(60) NOT NULL
,OBJECTID7 SMALLINT NOT NULL
,OBJECTVALUE7 CHAR(60) NOT NULL
)
IN
-- Table 2 - PSCIAPCXREFTIME
CREATE TABLE OWNERID.PSCIAPCXREFTIME (
LASTRUN CHAR(120)
)
IN
-- Table 3 - PSFILEPROCESSRUN
CREATE TABLE OWNERID.PSFILEPROCESSRUN (
LASTREFRESHDTTM TIMESTAMP
,FPPSTATUS CHAR(10) NOT NULL
)
IN
-- Table 4 - PSSQLXREFDEFN
CREATE TABLE OWNERID.PSSQLXREFDEFN (
XREF_ID CHAR(128) NOT NULL
,XREF_SQLTYPE CHAR(1) NOT NULL
,DBTYPE CHAR(1) NOT NULL
,XREF_GROUPBY_CNT SMALLINT NOT NULL
,XREF_ORDERBY_CNT SMALLINT NOT NULL
,XREF_HAVING_CNT SMALLINT NOT NULL
,XREF_SUBQUERY_CNT SMALLINT NOT NULL
,XREF_OUTERJOIN_CNT SMALLINT NOT NULL
,LASTUPDDTTM TIMESTAMP
,SQLTEXT LONG VARCHAR
)
IN
-- Table 5 - PSSQLXREFITEM
CREATE TABLE OWNERID.PSSQLXREFITEM (
XREF_ID CHAR(128) NOT NULL
,XREF_SQLTYPE CHAR(1) NOT NULL
,DBTYPE CHAR(1) NOT NULL
,XREF_ITEMSEQ SMALLINT NOT NULL
,XREF_ITEMTYPE CHAR(4) NOT NULL
,XREF_RECNAME CHAR(30) NOT NULL
,XREF_VALUE CHAR(128) NOT NULL
,XREF_USAGE CHAR(4) NOT NULL
,XREF_CLAUSE CHAR(5) NOT NULL
)
IN
-- Table 6 - PSFILESQLXREFDEFN
CREATE TABLE OWNERID.PSFILESQLXREFDEFN (
SOURCE_FILE CHAR(40) NOT NULL
,FILE_TYPE CHAR(4) NOT NULL
,XREF_ID CHAR(128) NOT NULL
)
IN
-- Table 7 - PSFILEXREFDEFN
CREATE TABLE OWNERID.PSFILEXREFDEFN (
SOURCE_FILE CHAR(40) NOT NULL
,CALLED_FILE CHAR(40) NOT NULL
,FILE_TYPE CHAR(3) NOT NULL
)
IN
