Bravery Family

Configure BIEE Scheduler with MySQL

Written by

gsb

in

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:
    BIEE Scheduler Connection Settings
    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.

More posts