Operations Console issues in 9.1

[jhmckeever]

Hi,

I've recently set up our development instance of Information Server 9.1 on AIX 6.1. I had a lot of fun (!) setting up Operations Console which is now far more complicated to install than on 8.7. We're using the bundled DB2 for xmeta but a separate Oracle repository to drive the Ops Console. Many features appear to work, and many don't. Most notably, I get errors such as this in the DSODB/logs directory ...

Code: Select all

 2013-02-22 19:49:57,692 ERROR com.ibm.datastage.runtime.common.db.DBManager.insertRecordCmd(DBManager.java:819) - Error executing insert statement: INSERT INTO dev.JobStage (STAGEID,JOBID,StageName,ContainerPath,StageDescription,StageTypeName) VALUES (?,?,?,?,?,?). Values: [-8841748148679787574, -8581750872848403646, , -, , ]
java.sql.SQLIntegrityConstraintViolationException: [IBM][Oracle JDBC Driver][Oracle]ORA-01400: cannot insert NULL into ("DEV"."JOBSTAGE"."STAGENAME")

        at com.ibm.isf.jdbc.oraclebase.ddcg.b(Unknown Source)
        at com.ibm.isf.jdbc.oraclebase.ddcg.a(Unknown Source)
        at com.ibm.isf.jdbc.oraclebase.ddcf.b(Unknown Source)
        at com.ibm.isf.jdbc.oraclebase.ddcf.a(Unknown Source)
        at com.ibm.isf.jdbc.oracle.ddn.s(Unknown Source)
        at com.ibm.isf.jdbc.oraclebase.ddes.v(Unknown Source)
        at com.ibm.isf.jdbc.oraclebase.ddes.x(Unknown Source)
        at com.ibm.isf.jdbc.oraclebase.dddm.executeUpdate(Unknown Source)
        at com.ibm.datastage.runtime.common.db.DBManager.insertRecordCmd(DBManager.java:807)
        at com.ibm.datastage.runtime.common.db.DBManager.insertRecord(DBManager.java:753)
        at com.ibm.datastage.runtime.common.GeneratedKeyInserter.insertRecord(GeneratedKeyInserter.java:74)
        at com.ibm.datastage.runtime.engmonapp.FileManager.insertGenKeyRecord(FileManager.java:1766)
        at com.ibm.datastage.runtime.engmonapp.FileManager.insertGenKeyRecord(FileManager.java:1749)
        at com.ibm.datastage.runtime.engmonapp.FileManager.getOrAddStageRecord(FileManager.java:1909)
        at com.ibm.datastage.runtime.engmonapp.FileManager.processStageRun(FileManager.java:1439)
        at com.ibm.datastage.runtime.engmonapp.FileManager.processFile(FileManager.java:809)
        at com.ibm.datastage.runtime.engmonapp.FileManager.processDirectory(FileManager.java:526)
        at com.ibm.datastage.runtime.engmonapp.FileManager.watchForDirectories(FileManager.java:397)
        at com.ibm.datastage.runtime.engmonapp.EngMonApp.run(EngMonApp.java:102)
        at com.ibm.datastage.runtime.engmonapp.EngMonApp.main(EngMonApp.java:133)
 2013-02-22 19:49:57,694 ERROR com.ibm.datastage.runtime.engmonapp.FileManager.discardEventFile(FileManager.java:2531) - Content of event file 4948_000_10224342_00004_9.dsv:
ODBEVENT=9
AT=2013-02-22 08:49:48+0000
J.ProjectName=ANALYZERPROJECT
J.JobName=BaseProfileLoad1_ColumnAnalysisTask95321104
J.CompilationTimestamp=2013-02-22 08:41:30
R.InvocationId=
R.CreationTimestamp=2013-02-22 08:48:10.881+0000
S.StageName=
S.ContainerPath=
S.StageDescription=
S.StageTypeName=
T.StageEndTimestamp=2013-02-22 08:49:48+0000
T.ElapsedRunSecs=0
T.StageStatus=RDY
T.NumInstances=1
T.InstancePidList=
T.InstanceCPUList=0
T.TotalCPU=0

 2013-02-22 19:49:57,694  INFO com.ibm.datastage.runtime.engmonapp.FileManager.discardEventFile(FileManager.java:2543) - Event file removed: 4948_000_10224342_00004_9.dsv

I'm thinking of raising a PMR but wondered if anyone had experience of this issue?

Thanks,
John.

PS. @Moderator: You need to add "9.1" to the list of available DataStage releases when submitting a new post!

[chulett]

I know what you mean! However moderators move posts like this one, I have no powers to alter the workings of the website itself.

[vmcburney]

It's trying to put an Information Analyzer column analysis job into the Ops Console database and most of the stage metadata is missing. You could try excluding the IA project from Ops Console reporting, or cleaning out your IA project and removing all old jobs where "retain scripts" was used. You could also try removing some of the NULL constraints on the ops console database schema.

[ray.wurlod]

I understood that Ops Console is automatically pre-installed in 9.1. Is this not the case? (Currently at 8.7 site.)

[jhmckeever]

Ray - This wasn't my experience with either AIX or Windows installations. Certainly the WebSphere application is installed, but the underlying database isn't. You get the following message after logging in:

DSODB is turned off for the selected engine (GREDO). Functionality in this application will be limited for that engine until DSODB is configured, turned on, and the associated services are started. See the Support and Information tab for further guidance on how to configure DSODB on an engine system.

[ Yes, our server is called GREDO. Who shot first? ]

Here, for your delectation, are my installation notes the hopefully give some idea of what I did to get it working to the extent I have...

Code: Select all

# Copy the template for the Repository Registration Properties file
$> cp  /opt/IBM/InformationServer/Server/DSODB/DSODBrepos_registration.dev.properties .
 
# Edit it to add details about your target platform (details in the file)
$> vi DSODBrepos_registration.dev.properties
 
# Register the database server (doco says this wil be done automatically by registerRepository, but it isn't)
$> RepositoryAdmin.sh -registerDatabaseServer -propertyFile DSODBrepos_registration.dev.properties
 
# Register the DSODB database on that server
$> RepositoryAdmin.sh -registerDatabase -propertyFile DSODBrepos_registration.dev.properties
 
# Register that database as a repository
$> RepositoryAdmin.sh -registerRepository -propertyFile DSODBrepos_registration.dev.properties
 
# Check our work ...
$> RepositoryAdmin.sh -listRepositories
dsodb
QSSRDDB
DSODBDM (development)
$> RepositoryAdmin.sh -displayRepository -reposName DSODB
DatabasePlatform.databaseType=ORACLE
DatabasePlatform.version=11g
DatabaseServer.host=ORA2
DatabaseServer.port=1521
Database.name=dsodbdm
Database.alias=dsodbdm
Database.location=/
Repository.name=DSODBDM
Repository.description=Development engine ODB.
Repository.tool=DataStage
Repository.context=development
Repository.schema=dev
RepositoryConnection.name=dsodbdm
RepositoryConnection.userName=dev
RepositoryConnection.password={iisenc}blah blah blah==
RepositoryConnection.connectionURL=jdbc:ibm:oracle://ORA2:1521;SID=dsodbdm
RepositoryConnection.managedDataSourceName=
Tablespace.name=DSODBSPACE
 
# Generate SQL scripts for our specified DB platform (I place these in $DSHOME/../DSODB/ for neatness)
$> RepositoryAdmin.sh -saveSQLScripts -reposName DSODB -scriptLocation ../../Server/DSODB/
../../Server/DSODB/scripts/ora11/dsodb_remove_functions.sql
../../Server/DSODB/scripts/ora11/dsodb_table_creation.sql
../../Server/DSODB/scripts/ora11/dsodb_drop_tables.sql
../../Server/DSODB/scripts/ora11/dsodb_user_config.sql
../../Server/DSODB/scripts/ora11/dsodb_tablespace_creation.sql
../../Server/DSODB/scripts/ora11/dsodb_upgrade_87to91.sql
../../Server/DSODB/scripts/ora11/dsodb_remove_all.sql

# Get the DBA to execute the user_config, tablespace_creation and table_creation scripts

# Test the connection file
$> DSAppWatcher.sh -test
DSODB is turned ON in the DSODBConfig.cfg file.
        Link Monitoring is ON.
        Job Run Usage is ON.
        Resource Monitoring is ON.
Checking Database Connection:
        Driver: com.ibm.isf.jdbc.oracle.OracleDriver
        Connection URL: jdbc:ibm:oracle://ORA2:1521;SID=dsodbdm
                Successfully loaded the database driver.
                Successfully connected to the database.
        Schema: dev
                DB Schema version number: 2

I'm open to any contributions that might inform me I'm approaching this the wrong way.

[MrBlack]

Did you ever find a resolution to this?

I've had my environment up for a while then all of the sudden I started seeing these errors in my logs and I don't know what's happened to cause this. I have a PMR open with IBM but they're being slow providing a resolution.