Manually Creating the JasperReports Server Database

If you can’t use the js-install scripts to create the JasperReports Server database and the sample databases, you can create them manually. Follow the instructions for your database to create the repository database and optional sample databases:

PostgreSQL
MySQL
Oracle
DB2
SQL Server

The commands in these sections have been tested at Jaspersoft, but the commands you need to use on your database instance may be different.

PostgreSQL

To manually create the JasperReports Server database in PostgreSQL:

1. On the Windows, Linux, or Mac command line, enter these commands to create and initialize the JasperReports Server database:
cd <js-install>/buildomatic/install_resources/sql/postgresql
psql -U postgres -W
postgres=#create database jasperserver encoding=’utf8’;
postgres=#\c jasperserver;
postgres=#\i js-pro-create.ddl
postgres=#\i quartz.ddl
postgres=#\q
2. (Optional) Run the following commands if you want to install sample databases:
cd <js-install>/buildomatic/install_resources/sql/postgresql
psql -U postgres -W
postgres=#create database sugarcrm encoding=’utf8’;
postgres=#create database foodmart encoding=’utf8’;
postgres=#\c sugarcrm;
postgres=#\i sugarcrm.sql; (first make sure the file is unzipped)
postgres=#\c foodmart;
postgres=#\i foodmart-postqresql.sql; (first make sure the file is unzipped)
postgres=#\i supermart-update.sql;
postgres=#\q
3. If you did not install the optional sample databases, complete the installation with these commands:

cd <js-install>/buildomatic

js‑ant import-minimal-pro

js-ant deploy-webapp-pro

If you installed the optional sample databases, complete the installation with these commands:

cd <js-install>/buildomatic

js‑ant import-sample-data-pro

js-ant deploy-webapp-pro

For more information about executing the Ant scripts, see Installing the WAR File Manually.

4. Set Java JVM Options, as described in Setting JVM Options for Application Servers. This step is required.
5. Set up the JasperReports Server License as described in Setting Up the JasperReports Server License. This step is required.

MySQL

To manually create the JasperReports Server database in MySQL:

The MySQL client software, mysql.exe or mysql, can be used to interact with the MySQL database.

For specific details on connecting to the MySQL database and setting privileges for databases and db users, please refer to the documentation provided with your database.

1. On the Windows, Linux, or Mac command line, enter these commands to create and initialize the JasperReports Server database:
cd <js-install>/buildomatic/install_resources/sql/mysql
mysql -u root -p
mysql>create database jasperserver character set utf8;
mysql>use jasperserver;
mysql>source js-pro-create.ddl
mysql>source quartz.ddl
mysql>exit
2. (Optional) Run these commands to install sample databases:
cd <js-install>/buildomatic/install_resources/sql/mysql
mysql -u root -p
mysql>create database sugarcrm;
mysql>create database foodmart;
mysql>use sugarcrm;
mysql>source sugarcrm.sql;(first make sure the file is unzipped)
mysql>use foodmart;
mysql>source foodmart-mysql.sql; (first make sure the file is unzipped)
mysql>source supermart-update.sql;
mysql>exit
3. If you did not install the optional sample databases, complete the installation with these commands:

cd <js-install>/buildomatic

js‑ant import-minimal-pro

js-ant deploy-webapp-pro

If you installed the optional sample databases, complete the installation with these commands:

cd <js-install>/buildomatic

js‑ant import-sample-data-pro

js-ant deploy-webapp-pro

For more information about executing the Ant scripts, see Installing the WAR File Manually.

4. Set Java JVM Options, as described in Setting JVM Options for Application Servers. This step is required.
5. Set up the JasperReports Server License as described in Setting Up the JasperReports Server License. This step is required.

Oracle

To manually create the JasperReports Server database in Oracle:

The Oracle client software, sqlplus.exe or sqlplus, can be used to interact with Oracle.

For specific details on connecting to the Oracle database and setting privileges for databases and db users, please refer to the documentation provided with your database.

1. On the Windows, Linux, or Mac command line, enter these commands to create and initialize the JasperReports Server database:
cd <js-install>/buildomatic/install_resources/sql/oracle
sqlplus /nolog (start sqlplus client)
SQL> connect system/password (use your sysUsername and password)
(or SQL>connect sys/password as sysdba
SQL> create user jasperserver identified by password;
SQL> grant connect, resource to jasperserver;
SQL> connect jasperserver/password@ORCL (use your password, your SID)
SQL> @js-pro-create.ddl
SQL> @quartz.ddl
SQL> exit
2. (Optional) Special edit to the sugarcrm.sql script which creates the sugarcrm sample database. The sqlplus command line tool interprets SQL statements differently than a JDBC call (that is, the way buildomatic executes SQL scripts). Because of this, the sugarcrm.sql file must be edited in order to execute using sqlplus. To make these edits do the following:
Unzip the sugarcrm.zip file to get the sugarcrm.sql file. Open sugarcrm.sql for editing:
Uncomment the "-- set define off" line to look like this "set define off" (Line 7)
Uncomment the "--/" line that follows the CREATE TRIGGER statements (there are 12 of these toward the very end of the file on line 71,282. Just before the CREATE INDEX statements). Change to be just "/". (This terminates the trigger procedure definition in sqlplus.)
Save the file.

If you build and load the sample databases using buildomatic, the NLS_LANG setting is automatically handled via a JDBC driver setting.

If you load the sample databases using buildomatic, you will not need to set any variables or make any script edits.

3. (Optional) Set the NLS_LANG variable. The sugarcrm database has test data that requires a specific NLS_LANG setting in order to load into Oracle correctly. You will need to set this in your shell environment if you are manually loading the sugarcrm database.

Windows:

set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Linux:

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
4. (Optional) Run the following commands if you want to install sample databases:
cd <js-install>/buildomatic/install_resources/sql/oracle
sqlplus /nolog (start sqlplus client)
SQL> connect system/password (use your sysUsername and password)
(or SQL>connect sys/password as sysdba
SQL> create user sugarcrm identified by password;
SQL> create user foodmart identified by password;
SQL> grant connect, resource to sugarcrm;
SQL> grant connect, resource to foodmart;
SQL> connect sugarcrm/password@ORCL
SQL> @sugarcrm.sql (First, make sure file is unzipped)
SQL> connect foodmart/password@ORCL
SQL> @foodmart-oracle.sql (First, make sure file is unzipped)
SQL> @supermart-update.sql
SQL> exit
5. If you did not install the optional sample databases, complete the installation with these commands:

cd <js-install>/buildomatic

js‑ant import-minimal-pro

js-ant deploy-webapp-pro

If you installed the optional sample databases, complete the installation with these commands:

cd <js-install>/buildomatic

js‑ant import-sample-data-pro

js-ant deploy-webapp-pro

For more information about executing the Ant scripts, see Installing the WAR File Manually.

6. Set Java JVM Options, as described in Setting JVM Options for Application Servers. This step is required.
7. Set up the JasperReports Server License as described in Setting Up the JasperReports Server License. This step is required.

DB2

To manually create the JasperReports Server database in DB2:

The DB2 client software, db2 or db2cmd, can be used to interact with DB2.

For specific details on connecting to the DB2 database and setting privileges for databases and db users, please refer to the documentation provided with your database.

1. Change to the following directory:

cd <js-install>/buildomatic/install_resources/sql/db2

2. Enter these commands in the DB2 command window to create and initialize the repository database, called jsprsrvr in DB2 to conform to the 8-character limitation:
db2 create database jsprsrvr using codeset utf-8 territory us pagesize 16384
db2 -tf js-pro-create.ddl
db2 -tf quartz.ddl
3. (Optional) Run the following commands in the DB2 command window if you want to install sample databases:
db2 create database sugarcrm
db2 -tf sugarcrm.sql (first make sure file is unzipped)
db2 create database foodmart
db2 -tf foodmart-db2.sql (first make sure file is unzipped)
db2 -tf supermart-update.sql (if script is available)
4. If you did not install the optional sample databases, complete the installation with these commands:

cd <js-install>/buildomatic

js‑ant import-minimal-pro

js-ant deploy-webapp-pro

If you installed the optional sample databases, complete the installation with these commands:

cd <js-install>/buildomatic

js‑ant import-sample-data-pro

js-ant deploy-webapp-pro

For more information about executing the Ant scripts, see Installing the WAR File Manually.

5. Set Java JVM Options, as described in Setting JVM Options for Application Servers. This step is required.
6. Set up the JasperReports Server License as described in Setting Up the JasperReports Server License. This step is required.

Further considerations:

If you are using DB2 8.1, set the LOGFIL_SIZ parameter to at least 3000 to avoid possible log file errors while loading the foodmart database. Configure your foodmart database right after creating it by using Control Center.
If JasperReports Server is deployed on the same host as DB2, delete the following file to avoid conflicts:

<db2>/SQLLIB/java/db2jcc.jar

SQL Server

Using the sqlcmd utility to manually build the jasperserver database.

For specific details on connecting to the SQL Server database and setting privileges for databases and db users, please refer to the documentation provided with your database.

To manually create the JasperReports Server database in SQL Server:

1. Open a Command Prompt and enter the following commands using the administrator (sa) user name and password.
cd <js-install>\buildomatic\install_resources\sql\sqlserver<br />sqlcmd -S ServerName -Usa -Psa 
1> CREATE DATABASE [jasperserver]
2> GO
1> USE [jasperserver]
2> GO
1> :r js-pro-create.ddl
2> GO
1> :r quartz.ddl
2> GO
2. From the Windows Start Menu, select Microsoft SQL Server > SQL Server Management Studio.
3. Connect to SQL Server as the administrative database user, and check that the jasperserver database appears in the Object Explorer.
4. Expand Tables in the jasperserver database, and check that the tables have been added.

To manually create the optional sample databases in SQL Server:

5. Extract the files in the sugarcrm.zip file to the level above your current directory, placing the sugarcrm.sql file in this directory:

<js-install>\jasperserver\buildomatic\install_resources\sql\sqlserver

6. Enter these commands to create and initialize the sugarcrm database:
1> CREATE DATABASE [sugarcrm]
2> GO
1> USE [sugarcrm]
2> GO
1> :r sugarcrm.sql
2> GO
7. Extract the files in the FoodMartCreateData.zip into your current directory.
8. Enter these commands to create and initialize the foodmart database:
1> CREATE DATABASE [foodmart]
2> GO
1> USE [foodmart]
2> GO
1> :r FoodMartCreateData.sql
2> GO

To complete the manual installation of databases in SQL Server:

9. If you did not install the optional sample databases, complete the installation with these commands:

cd <js-install>/buildomatic

js‑ant import-minimal-pro

js-ant deploy-webapp-pro

If you installed the optional sample databases, complete the installation with these commands:

cd <js-install>/buildomatic

js‑ant import-sample-data-pro

js-ant deploy-webapp-pro

For more information about executing the Ant scripts, see Installing the WAR File Manually.

10. Set Java JVM Options, as described in Setting JVM Options for Application Servers. This step is required.
11. Set up the JasperReports Server License as described in Setting Up the JasperReports Server License. This step is required.
Version: 
Feedback