Author: gsb

I was keen to use the BI Publisher Scheduler and configure it to use a MySQL database. The instructions for this imply that you need to hunt around and find out how to register the JDBC library for MySQL. The reality with the Oracle Application Server or standalone OC4J is much simpler than that.

1. Put the JDBC driver file in this directory: \j2ee\home\applib
2. Restart the OC4J or Oracle iAS instance.
3. When logged in now, you should be able to test a MySQL connection and find that it works properly (as show below):
   
4. You can now create the schema, by clicking the install schema button, and finally click the apply button to make the scheduler start working.
5. Restart the Oracle OC4J or iAS.

This time messages will appear in the log that indicate that the Publisher Scheduler has started up:

 - Job execution threads will use class loader of thread: OC4J Launcher
 - Quartz Scheduler v.1.5.1 created.
 - Using thread monitor-based data access locking (synchronization).
 - Removed 0 Volatile Trigger(s).
 - Removed 0 Volatile Job(s).
 - JobStoreTX initialized.
 - Quartz scheduler 'BIPublisherScheduler' initialized from the specified file : 'C:\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\xdo\cache\xmlp42298771.tmp'
 - Quartz scheduler version: 1.5.1
 - Freed 0 triggers from 'acquired' / 'blocked' state.
 - Recovering 0 jobs that were in-progress at the time of the last shut-down.
 - Recovery complete.
 - Removed 0 'complete' triggers.
 - Removed 0 stale fired job entries.
 - Scheduler BIPublisherScheduler_$_NON_CLUSTERED started.

And that’s it.

BI Publisher’s Scheduler should now be configured against MySQL and will now work. This means that jobs should now be able to be created and will run.

I am using a Windows PC to do this, so the instructions may not directly translate to UNIX/Linux.

1. Create a database for BIEE to use.
   Create the objects required using this script (this is a tweaked version of the standard SAJOBS.DB2.sql script):
   CREATE TABLE S_NQ_JOB (
    JOB_ID decimal(10,0) NOT Null,
    NAME varchar (50)  ,
    DESC_TEXT varchar (255)  ,
    SCRIPT_TYPE varchar (20)  ,
    SCRIPT varchar (255)  ,
    MAX_RUNTIME_MS decimal(10,0)  ,
    USER_ID varchar (128)  ,
    NEXT_RUNTIME_TS timestamp,
    LAST_RUNTIME_TS timestamp,
    MAX_CNCURRENT_INST decimal(10,0)  ,
    BEGIN_YEAR decimal(10,0)  ,
    BEGIN_MONTH decimal(10,0)  ,
    BEGIN_DAY decimal(10,0)  ,
    END_YEAR decimal(10,0)  ,
    END_MONTH decimal(10,0)  ,
    END_DAY decimal(10,0)  ,
    START_HOUR decimal(10,0)  ,
    START_MINUTE decimal(10,0)  ,
    END_HOUR decimal(10,0)  ,
    END_MINUTE decimal(10,0)  ,
    INTERVAL_MINUTE decimal(10,0)  ,
    TRIGGER_TYPE decimal(10,0)  ,
    TRIGGER_DAY_INT decimal(10,0)  ,
    TRIGGER_WEEK_INT decimal(10,0)  ,
    TRIGGER_RANGE_DOW decimal(10,0)  ,
    TRIGGER_RANGE_DOM decimal(10,0)  ,
    TRIGGER_RANGE_MTH decimal(10,0)  ,
    TRIG_RANGE_DAY_OCC decimal(10,0)  ,
    DELETE_DONE_FLG decimal(10,0) NOT Null,
    DISABLE_FLG decimal(10,0) NOT Null,
    HAS_END_DT_FLG decimal(10,0) NOT Null,
    EXEC_WHEN_MISS_FLG decimal(10,0) NOT Null,
    DEL_SCPT_DONE_FLG decimal(10,0) NOT Null,
    PATH_IN_SCPT_FLG decimal(10,0) NOT Null,
    ISUSER_SCPT_FLG decimal(10,0) NOT Null,
    DELETE_FLG decimal(10,0) NOT Null,
    TZ_NAME varchar(100)
);
CREATE INDEX S_NQ_JOB_M1 ON S_NQ_JOB (NEXT_RUNTIME_TS) ;
CREATE INDEX S_NQ_JOB_M2 ON S_NQ_JOB (USER_ID);
CREATE UNIQUE INDEX S_NQ_JOB_P1 ON S_NQ_JOB (JOB_ID);CREATE TABLE S_NQ_JOB_PARAM (
    JOB_ID decimal(10,0) NOT NULL,
    RELATIVE_ORDER decimal(10,0) NOT NULL ,
    JOB_PARAM varchar (255),
    DELETE_FLG decimal(10,0) NOT NULL
);
CREATE TABLE S_NQ_INSTANCE (
    JOB_ID decimal(10,0) NOT NULL,
    INSTANCE_ID decimal(20,0) NOT NULL,
    STATUS decimal(10,0),
    BEGIN_TS timestamp,
    END_TS timestamp,
    EXIT_CODE decimal(10,0) ,
    DELETE_FLG decimal(10,0) NOT NULL,
    ERROR_MSG_FLG decimal(10,0) NOT NULL
);
CREATE UNIQUE INDEX S_NQ_INSTANCE_U1 ON S_NQ_INSTANCE (JOB_ID, INSTANCE_ID);
CREATE INDEX S_NQ_INSTANCE_M1 ON S_NQ_INSTANCE (END_TS, STATUS, INSTANCE_ID ASC) ;
CREATE INDEX S_NQ_INSTANCE_M2 ON S_NQ_INSTANCE (BEGIN_TS, STATUS, INSTANCE_ID ASC) ;
CREATE INDEX S_NQ_INSTANCE_M3 ON S_NQ_INSTANCE (INSTANCE_ID ASC, DELETE_FLG ASC) ;
CREATE INDEX S_NQ_INSTANCE_M4 ON S_NQ_INSTANCE (JOB_ID, INSTANCE_ID, STATUS, DELETE_FLG) ;
CREATE INDEX S_NQ_INSTANCE_M5 ON S_NQ_INSTANCE (STATUS ASC, DELETE_FLG ASC) ;
CREATE TABLE S_NQ_ERR_MSG (
    JOB_ID decimal(10,0) NOT NULL,
    INSTANCE_ID decimal(20,0) NOT NULL,
    RELATIVE_ORDER decimal(10,0) NOT NULL,
    ERROR_MSG_TEXT varchar (255),
    DELETE_FLG decimal(10,0) NOT NULL
);
CREATE UNIQUE INDEX S_NQ_ERR_MSG_U1 ON S_NQ_ERR_MSG (JOB_ID, INSTANCE_ID, RELATIVE_ORDER);
CREATE INDEX S_NQ_ERR_MSG_F1 ON S_NQ_ERR_MSG (INSTANCE_ID ASC)  ;
2. Create a user, and grant it access to the tables.
3. Create an ODBC Connection to the MySQL Database, and check it works.
4. Now start the scheduler configuration tool.
   Tell it that the connection type is ODBC and let it use the default Call Interface. Then enter details of the Data source created in step three, and include the user name and password.
   So, using the Windows OS, I have set it as follows:
   
   Can also do this in the schconfig command line based tool, so I would expect this to work the same way under Linux.
5. Then start the Scheduler service.
6. On windows navigate to this directory:
   c:\OracleBIData\web\config
7. Then run the following:
   \OracleBI\web\bin\cryptotools.exe credstore -add -infile credentialstore.xml
   and input the following bold values:
   >Credential Alias: admin
>Username: Administrator
>Password: *************
>Do you want to encrypt the password? y/n (y): n
>File "credentialstore.xml" exists. Do you want to overwrite it? y/n (y): y
8. Now must edit instanceconfig.xml file, as modification by MBean didn’t work correctly.
   edit instanceconfig.xml
   Now, look for </ServerInstance> entry at the end of the file, and immediately before it, insert the following (path needs to be adapted appropriately for installation directory.
   <CredentialStore>
<CredentialStorage type="file" path="c:/OracleBIData/web/config/credentialstore.xml"/>
</CredentialStore>
   Save the file. Credentials have now been saved correctly and will be used by Presentation Server.
9. Restart the presentation server service.
10. You should now be able to save a iBot and not get a error reported by the application server.