Update data source in BIRT report design files using BIRT utility

Once you copy db specific files as per the details mentioned above, you must update data sources which are required to run the reports using birtdbutil.sh/bat located at <PLATFORM_HOME>/Birt/birt/tools/bin

The parameters required to run this utility are:

  • ds for product datasource
  • bPath for birt report design file path
  • DBType for birt design file database type
  • URL for JDBC url for database
  • user for database user
  • pwd for database user password

Sample command to update the parameters: 

birtDBUtil -ds=<datasource name>
        -bPath=<Report folder path> -DBType=<databasetype> [-URL=JDBC connection URL>] [-user=<database
        user>] [-pwd=<database password>] [-locale=<Locale>]

The following command allows you to view the utility usage.

birtDBUtil -h

The following command allows you to view the usage for localize.

birtDBUtil -h -locale=<Locale>

Instructions:

For the first time, all parameters are mandatory.

Users are required to provide all parameters and jdbc URL in expected format as mentioned in help for specific database

  • Update password : -ds, -DBType, -URL -bPath and -pwd is mandatory
  • Update user name : -ds, -DBType, -URL -bPath and -user is mandatory

Parameters help:

  • bPath : Birt report design file path :mandatory
  • ds : This parameter is for product data source : mandatory
  • ds: Available options (case sensitive)
  • ds : Product : Campaign -> CampaignDS
  • ds : Product : Plan -> PlanDS
  • ds : Product : Interact -> InteractDTDS
  • ds : Product : Interact -> InteractRTDS
  • ds : Product : Interact -> InteractLearningDS
  • ds : Product : Interact -> InteractETLDS
  • DBType : This parameter is for Database type : mandatory
  • DBType Available options
  • DBType : Database : Sql Server -> sqlserver
  • DBType : Database : DB2 -> db2
  • DBType : Database : Oracle -> oracle
  • URL : This parameter is required for JDBC connection

URL is mandatory for the first time and when any parameter of JDBC url changes.

URL Available options

  • URL : Database : Sql Sever -> jdbc:sqlserver://<HOST>:<PORT>;instance=<INSTANCE/OPTIONAL>;databaseName=<DB NAME>
  • URL : Database : DB2 -> jdbc:db2://<HOST>:<PORT>/<sid>
  • URL : Database : Oracle -> jdbc:oracle:thin:@<HOST>:<PORT>:<sid>
  • user : Database user name
  • pwd :Database password
Note: In case of DB2, you must use the following URL if you are updating Interact report design files.
URL:
Database : DB2 -> jdbc:db2://<HOST>:<PORT>/<sid>:
      useJDBC4ColumnNameAndLabelSemantics=false;
 

Granting permissions for stored procedures for IBM DB2

Before you configure stored procedures for IBM DB2, you must grant permissions.

To grant permissions, complete the following steps.

  1. Enable the registry by completing the following steps:
    • Set the DB2_ATS_ENABLE registry variable to one of the following values:
      • YES
      • TRUE
      • 1
      • ON
    • Restart the DB2 database after you set the variable.
  2. Create the SYSTOOLSPACE table space.

    Users who belong to the SYSADM or SYSCTRL group can create this space. Use the following query to verify that the space exists:

    SELECT TBSPACE FROM
        SYSCAT.TABLESPACES WHERE TBSPACE = ’SYSTOOLSPACE’
  3. Grant permissions. In the following examples, substitute the values that are appropriate for your environment.
    • EMESSAGE: Database that contains the eMessage system tables
    • USER1: Owner of the EMESSAGE database
    • DB2ADMIN: DB2 administrative user
    • Administrator: Super user
  4. Connect to DB2 as an administrative user and run the following grant commands:
    • Connect to DB2 as an administrative user and run the following grant commands:
    • db2 GRANT DBADM ON DATABASE TO USER DB2ADMIN
    • db2 GRANT DBADM ON DATABASE TO USER USER1
    • db2 grant all on table SYSTOOLS.ADMINTASKS to USER1
    • db2 grant all on table SYSTOOLS.ADMINTASKS to DB2ADMIN
  5. If the SYSPROC.ADMIN_TASK_ADD table exists, run the following grant commands:
    • db2 grant execute on
        procedure SYSPROC.ADMIN_TASK_ADD to USER1
    • db2 grant execute on procedure SYSPROC.ADMIN_TASK_ADD to DB2ADMIN

Guidelines for configuring stored procedures

  • The database must be DB2 version 9.7.8 or higher.
  • Create new jobs in DB2 Administrative Task Scheduler (ATS).
  • Schedule the jobs to run at least daily. You must schedule sp_runid to run at least 10 minutes before the other scripts.