Hard-coded tablespace names in UC_DBSYN

[Michael A. Lowry]

The AE 12.3.4 HF2 initial data for UC_DBSYN contain two SQL statements with hard-coded table space names.

1. Oracle SQL statement to create new MQCP table.
This SQL statement includes the hard-coded tablespace names UC4_DATA and UC4_INDEX.

3612 F001CCREATE_MQCP
3613 F002CORACLE
3614 F003CCREATE TABLE &NEW_MQ#( MQCP_PK NUMBER(38,0) NOT NULL, MQCP_System VARCHAR2 (8 CHAR) NULL, MQCP_CAddr VARCHAR2 (32 CHAR) NULL, MQCP_ CSRName VARCHAR2 (8 CHAR) NULL, MQCP_CAcv NUMBER(38,0) NULL, MQCP_BAddr VARCHAR2 (32 CHAR) NULL, MQCP_BSRName VARCHAR2 (8 CHAR) NULL, MQ CP_BAcv NUMBER(38,0) NULL, MQCP_FAddr VARCHAR2 (32 CHAR) NULL, MQCP_LogAddr VARCHAR2 (32 CHAR) NULL, MQCP_PhysAddr VARCHAR2 (32 CHAR) NU LL, MQCP_BTable VARCHAR2 (8 CHAR) NULL, MQCP_SchedTime DATE NULL, MQCP_Status NUMBER(38,0) NULL, MQCP_Priority NUMBER(38,0) NULL, MQCP_D Role VARCHAR2 (32 CHAR) NULL, MQCP_LAddr VARCHAR2 (32 CHAR) NULL, MQCP_Len NUMBER(38,0) NULL, MQCP_Msg BLOB NULL, CONSTRAINT PK_&NEW_MQ# PRIMARY KEY ( MQCP_PK ) using index TABLESPACE UC4_INDEX) LOB(MQCP_Msg) STORE AS(ENABLE STORAGE IN ROW CACHE) TABLESPACE UC4_DATA ENABL E ROW MOVEMENT NOCOMPRESS ; CREATE SEQUENCE SQ_&NEW_MQ# INCREMENT BY 1 START WITH 1 MAXVALUE 2147483647MINVALUE 1 CYCLE CACHE 1000 NOORD ER ;CREATE INDEX NK_&NEW_MQ#_BAcv ON &NEW_MQ#(MQCP_BAcv) TABLESPACE UC4_INDEX ;CREATE INDEX NK_&NEW_MQ#_Prio ON &NEW_MQ#(MQCP_Priority, MQCP_SchedTime, MQCP_PK, MQCP_Status) TABLESPACE UC4_INDEX ;CREATE INDEX NK_&NEW_MQ#_SchTime ON &NEW_MQ#(MQCP_SchedTime) TABLESPACE UC4_ INDEX ;
3615 R

2. PostgreSQL statement to create new MQCP table.
This SQL statement includes the hard-coded tablespace names AE_DATA and AE_INDEX.

3620 F001CCREATE_MQCP
3621 F002CPOSTGRES
3622 F003CCREATE TABLE &NEW_MQ#(MQCP_PK int GENERATED BY DEFAULT AS IDENTITY NOT NULL,MQCP_System varchar (8) NULL,MQCP_CAddr varchar (32) NU LL,MQCP_CSRName varchar (8) NULL,MQCP_CAcv int NULL,MQCP_BAddr varchar (32) NULL,MQCP_BSRName varchar (8) NULL,MQCP_BAcv int NULL,MQCP_F Addr varchar (32) NULL,MQCP_LogAddr varchar (32) NULL,MQCP_PhysAddr varchar (32) NULL,MQCP_BTable varchar (8) NULL,MQCP_SchedTime timest amp NULL,MQCP_Status int NULL,MQCP_Priority int NULL,MQCP_DRole varchar (32) NULL,MQCP_LAddr varchar (32) NULL,MQCP_Len int NULL,MQCP_Ms g bytea NULL,CONSTRAINT PK_&NEW_MQ# PRIMARY KEY (MQCP_PK) USING INDEX TABLESPACE AE_INDEX) TABLESPACE AE_DATA; CREATE INDEX NK_&NEW_MQ#_ BAcv ON &NEW_MQ# (MQCP_BAcv)TABLESPACE AE_INDEX; CREATE INDEX NK_&NEW_MQ#_Prio ON &NEW_MQ# (MQCP_Priority, MQCP_SchedTime, MQCP_PK, MQCP _Status)TABLESPACE AE_INDEX; CREATE INDEX NK_&NEW_MQ#_SchTime ON &NEW_MQ# (MQCP_SchedTime)TABLESPACE AE_INDEX; CREATE SEQUENCE SQ_&NEW_M Q# INCREMENT BY 1 START WITH 1 MAXVALUE 2147483647 MINVALUE 1 CYCLE CACHE 1000;
3623 R

These statements should be updated to set the tablespace names dynamically, like the other similar statements.

[Michael A. Lowry]

If you use non-standard tablespace names and update the .sql files in the initial data but not the uc_ini.txt file, you can end up in a situation where your AE database is installed in the correct tablespaces, but the stored SQL statements in UC_DBSYN point to the wrong tablespaces. In this situation, if the AE tries to create a new MQCP table, e.g., when you try to start more than 5 CPs, you will get an error like this:

20210120/095658.449 - U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: 2723eb0 DB-HDBC: 2799758
20210120/095658.449 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
20210120/095658.449 - U00003592 UCUDB - Status: '' Native error: '942' Msg: 'ORA-00942: table or view does not exist'
20210120/095658.449 - U00003594 UCUDB Ret: '3590' opcode: 'SLCO' SQL Stmnt: 'SELECT * FROM MQ1CP013 WHERE MQCP_SchedTime <= sys_extract_utc(systimestamp(0)) AND MQCP_Status=0 ORDER BY MQCP_Priority, MQCP_SchedTime, MQCP_PK'
20210120/095658.449 - U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR ', '', 'ORA-00942: table or view does not exist'
20210120/095658.581 - U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: 2723eb0 DB-HDBC: 2799758
20210120/095658.581 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
20210120/095658.581 - U00003592 UCUDB - Status: '' Native error: '959' Msg: 'ORA-00959: tablespace 'UC4_DATA' does not exist'
20210120/095658.581 - U00003594 UCUDB Ret: '3590' opcode: 'EXEC' SQL Stmnt: 'CREATE TABLE MQ1CP013( MQCP_PK NUMBER(38,0) NOT NULL, MQCP_System VARCHAR2 (8 CHAR) NULL, MQCP_CAddr VARCHAR2 (32 CHAR) NULL, MQCP_CSRName VARCHAR2 (8 CHAR) NULL, MQCP_CAcv NUMBER(38,0) NULL, MQCP_BAddr VARCHAR2 (32 CHAR) NULL, MQCP_BSRName VARCHAR2 (8 CHAR) NULL, MQCP_BAcv NUMBER(38,0) NULL, MQCP_FAddr VARCHAR2 (32 CHAR) NULL, MQCP_LogAddr VARCHAR2 (32 CHAR) NULL, MQCP_PhysAddr VARCHAR2 (32 CHAR) NULL, MQCP_BTable VARCHAR2 (8 CHAR) NULL, MQCP_SchedTime DATE NULL, MQCP_Status NUMBER(38,0) NULL, MQCP_Priority NUMBER(38,0) NULL, MQCP_DRole VARCHAR2 (32 CHAR) NULL, MQCP_LAddr VARCHAR2 (32 CHAR) NULL, MQCP_Len NUMBER(38,0) NULL, MQCP_Msg BLOB NULL, CONSTRAINT PK_MQ1CP013 PRIMARY KEY ( MQCP_PK ) using index TABLESPACE UC4_DATA) LOB(MQCP_Msg) STORE AS(ENABLE STORAGE IN ROW CACHE) TABLESPACE UC4_DATA ENABLE ROW MOVEMENT NOCOMPRESS'
20210120/095658.581 - U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR ', '', 'ORA-00959: tablespace 'UC4_DATA' does not exist'

KB article 126471 describes the problem and a work-around.

[Michael A. Lowry]

For those at Broadcom who care about product quality & customer statisfaction, please note that I first identified this problem version 12.1 in October 2017, more than 3 years ago. In my humble opinion, there is room for improvement here.

[Pothen Verghese]

Hi,

But isn't this already documented? The docs states:

Notes:

• Changing the name of the tablespaces (UC4_DATA or UC4_INDEX) requires the names to be changed also in the installation file UC_DDL.SQL and the database table UC_DBSYN. The same applies for every AE update.

• It is best to use the tablespaces UC4_INDEX and UC4_DATA only, otherwise you would have to manually adjust all SQL files during the upgrade process.

I do agree, that the customers should be free to choose the tablespace names of their choice and the scripts should automatically adjust to the new new names instead of forcing folks to manually update the scripts each time they want to update.

Regards
Pothen

[Michael A. Lowry]

@Pothen Verghese: Good catch. I'm glad to know that it's documented.
​