"Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=CONTROLLER_TABLE..." after clicking "Upgrade" inside "Database Conversion Utility"

Technote (troubleshooting)


Problem(Abstract)

User launches "Controller Configuration". User opens "Database Connections" and chooses connection. User clicks green "play" button to open "Database Conversion Utility". User chooses UDL file, and clicks 'Connect'. User clicks "upgrade". After a while, an error message appears.

Symptom

Creating a Controller application database:

...Running script: script/db2/TRG_XDB99.sql
Running DB Step: 822
Running DB Step: 823
Running script batch: script/db2_823.batch
Running script: script/db2/alter_CRDICBAL.sql
Running script: script/db2/create_CRDICBALLANDDIFF.sql
** ERROR: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=CONTROLLER_TABLE, DRIVER=3.59.81

Creating a Controller Data Mart:
...Running DB Step: 42
Running script batch: script/db2_42.batch
Running script: script/db2/dmtracurr_db2.tab
** ERROR: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=CONTROLLER_TABLE, DRIVER=3.59.81

Cause

Required tablespace "CONTROLLER_TABLE" had not been created inside the Controller database.

Environment

Controller database hosted on DB2.

Resolving the problem

Ensure that all scripts have been correctly run for the Controller application repository database, to ensure that bufferpools/tablespaces etc. have been correctly created.


For example, for most customer's needs, the following scripts should be run:

    CREATE BUFFERPOOL BP16K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 16 K ;
    CREATE BUFFERPOOL BP32K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 32 K ;

    CREATE REGULAR TABLESPACE CONTROLLER_TABLE PAGESIZE 16 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BP16K DROPPED TABLE RECOVERY ON;

    CREATE REGULAR TABLESPACE CONTROLLER_XDB PAGESIZE 16 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BP16K DROPPED TABLE RECOVERY ON;

    CREATE REGULAR TABLESPACE CONTROLLER_INDEX PAGESIZE 16 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BP16K DROPPED TABLE RECOVERY ON;

    CREATE LARGE TABLESPACE CONTROLLER_LOBS PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BP32K ;

    CREATE USER TEMPORARY TABLESPACE CONTROLLER_TEMP PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BP32K ;

    CREATE SYSTEM TEMPORARY TABLESPACE CONTROLLER_SYS PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BP32K ;

    update db cfg using auto_reval DEFERRED_FORCE;

    update db cfg using decflt_rounding ROUND_HALF_UP;


Steps:
The following are based on your DB2 server running on Windows.
  1. Logon to Windows as a DB2 administrator (member of "DB2ADMNS")
  2. Launch "Control Center"
  3. Click "Tools - Command Editor"


4. Change the "TARGET" so that is set to point to your database (e.g. "CTRLMART")
5. Copy the relevant script into the command window, and click the green "Play" button:

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Cognos Controller
Controller

Software version:

10.1

Operating system(s):

Windows

Reference #:

1503879

Modified date:

2012-05-10

Translate my page

Machine Translation

Content navigation