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.

For running the Ant commands, you need to edit the default_master.properties file to add the settings for your database and application server as described in Installing the WAR File Using js-install Scripts.

PostgreSQL

To manually create the JasperReports Server database in PostgreSQL

1. On the Windows, Linux, or Mac command line, enter these commands: 
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. Run the following commands to install the JSAudit database: 
cd <js-install>/buildomatic/install_resources/sql/postgresql
psql -U postgres -W
postgres=#create database jsaudit;
postgres=#\c jsaudit;
postgres=#\i js-pro-create-audit.ddl
postgres=#\q
3. (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
4. If you didn't 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 (required), as described in Setting JVM Options for Application Servers.
6. Set up the JasperReports Server License (required) as described in Setting Up the JasperReports Server License.

MySQL

To manually create the JasperReports Server database in MySQL

You can use the MySQL client software, mysql.exe or mysql, 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 the following 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. Run these commands to create and initialize the JSAudit database. 
mysql -u root -p
mysql>create database jsaudit;
mysql>use jsaudit;
mysql>source js-pro-create-audit.ddl
mysql>exit
3. (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
4. If you didn't 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 (required), as described in Setting JVM Options for Application Servers.
6. Set up the JasperReports Server License (required) as described in Setting Up the JasperReports Server License.

Oracle

To manually create the JasperReports Server database in Oracle

You can use the Oracle client software, sqlplus.exe or sqlplus, 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 the following 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; (as sys user)
SQL> grant connect, resource to jasperserver; (as sys user)
SQL> grant unlimited tablespace to jasperserver; (as sys user)
SQL> connect jasperserver/password@ORCL (use your password, your SID)
SQL> @/opt/jasperreports-server-pro-8.0.0-bin/buildomatic/install_resources/sql/oracle/js-pro-create.ddl
SQL> @/opt/jasperreports-server-pro-8.0.0-bin/buildomatic/install_resources/sql/oracle/js-pro-create-audit.ddl
SQL> @/opt/jasperreports-server-pro-8.0.0-bin/buildomatic/install_resources/sql/oracle/quartz.ddl
SQL> exit
2. To create and initialize the JSAudit database, enter the following commands. 
SQL> create user jsaudit identified by password; (as sys user)
SQL> grant connect, resource to jsaudit; (as sys user)
SQL> grant unlimited tablespace to jsaudit; (as sys user)
SQL> connect jsaudit/password@ORCL
SQL> @/opt/jasperreports-server-pro-8.0.0-bin/buildomatic/install_resources/sql/oracle/js-sequence-create.ddl
SQL> @/opt/jasperreports-server-pro-8.0.0-bin/buildomatic/install_resources/sql/oracle/js-pro-create-audit.ddl
SQL> exit
3. Go to the <js-install>/buildomatic path and configure the default_master.properties file with the required values. For example:

cd <js-install>/buildomatic
dbUsername=jasperserver
dbPassword=password
sysUsername=jasperserver
sysPassword=password
dbHost=localhost
dbPort=1521 sid=ORCL

#audit props
installType=split
audit.dbHost=localhost
audit.dbPort=1521
audit.sid=ORCL
audit.dbUsername=jsaudit
audit.dbPassword=password
audit.dbName=jsaudit
audit.sysUsername=system
audit.sysPassword=password

You can set sysUsername and sysPassword the same as dbUsername and dbPassword.

4. Create server setting with audit db schema name (auditDB=JSAUDIT), to do so:
a. Go to <js-install>/buildomatic/bin path and edit the db-common.xml file.
b. Add the following target at the end of file and before </project>:
<target name="import-profile-attributes">
<import-profile-attribute key="auditDB" attrValue="${audit.dbName}"/>
</target>
c. Save the file.
d. Run the following command:
./js-ant import-profile-attributes

Server setting auditDB=JSAUDIT is needed for audit reports working properly on oracle in case of split installation.
5. (Optional) Special edit to the sugarcrm.sql script that 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 won't need to set any variables or make any script edits.
6. (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're manually loading the sugarcrm database.

Windows:

 
set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Linux:

 
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
7. (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
8. If you didn't 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.

9. Set Java JVM Options (required), as described in Setting JVM Options for Application Servers.
10. Set up the JasperReports Server License (required) as described in Setting Up the JasperReports Server License.

DB2

To manually create the JasperReports Server database in DB2

Use the DB2 client software, db2 or db2cmd, 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 connect to jsprsrvr
db2 -tf js-pro-create.ddl
db2 -tf quartz.ddl
3. To create and initialize the JSAudit database, enter the following commands in the DB2 command window: 
db2 create database jsaudit
db2 connect to jsaudit
db2 -tf js-pro-create-audit.ddl
db2 exit
4. (Optional) Run the following commands in the DB2 command window if you want to install sample databases: 
db2 create database sugarcrm
db2 connect to sugarcrm
db2 -tf sugarcrm.sql (first make sure file is unzipped)
db2 create database foodmart
db2 connect to foodmart
db2 -tf foodmart-db2.sql (first make sure file is unzipped)
db2 -tf supermart-update.sql (if script is available)
5. If you didn't 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 (required), as described in Setting JVM Options for Application Servers.
7. Set up the JasperReports Server License (required) as described in Setting Up the JasperReports Server License.

Further considerations:

• 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

Use 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
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 create and initialize the JSAudit database

5. Run the following commands: 
cd <js-install>\buildomatic\install_resources\sql\sqlserver
sqlcmd -S ServerName -Usa -Psa
1> CREATE DATABASE [jsaudit]
2> GO
1> USE [jsaudit]
2> GO
1> :r js-pro-create-audit.ddl
2> GO

To manually create the optional sample databases in SQL Server

6. 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

7. 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
8. You cannot initialize the foodmart database manually. Instead, change to the buildomatic directory and use the following buildomatic commands to create and initialize it from the command line:

js-ant create-foodmart-db

js-ant load-foodmart-db

Alternatively, you can replace the first command and create the database manually using the following SQL Server commands, but you still have to use the buildomatic command js-ant load-foodmart-db to load the data:

1> CREATE DATABASE [foodmart]
2> GO
1> USE [foodmart]
2> GO

To complete the manual installation of databases in SQL Server

9. If you didn't 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 (required), as described in Setting JVM Options for Application Servers.
11. Set up the JasperReports Server License (required) as described in Setting Up the JasperReports Server License.