Journyx version 8.0 and higher fully supports native Unicode storage in the database. This means user-entered text from different languages can be freely mixed on the same site. However only one "collation" (linguistic sort ordering in things like search results) can be used on a site. See below for collation details.
Earlier version of Journyx such as version 7.9 have limited or no Unicode support.
Journyx 8.0 and higher automatically uses Unicode on Microsoft SQL Server, Oracle, and the internally provided PostgreSQL database option. However when setting up an external PostgreSQL database connection, you will need to create the database with the "UTF8" (Unicode) character set as described in the external PostgreSQL setup instructions.
In addition, you should be aware of the difference between the UTF8 and UTF16 encodings of Unicode. They are both "Unicode" and can store the exact same list of characters. However they are stored in different binary formats which mainly affects the amount of disk space and memory required. UTF8 is more efficient for databases which are primarily Western European languages. UTF16 is more efficient for Asian languages and some others. Using UTF16 to store English or other Western texts can double the size of the database compared to Journyx 7.
Microsoft SQL Server only stores Unicode in UTF16 format. PostgreSQL only stores Unicode in UTF8 format. Oracle offers a choice between the two, but the default is UTF16. You can only change it to UTF8 when initially creating the database and not after creation. Therefore if you are planning to use Oracle with Journyx 8 and your users primarily use Western European languages such as English, Spanish, French, Italian or German, then you should choose UTF8 for your Oracle "national character set" setting when initially creating the database. See the external Oracle instructions for details.
As mentioned above, only a single collation can be used on any given site. The collation determines the linguistic sort ordering of search results and similar things. The collation is determined during the external database setup. Please refer to the appropriate external database instructions for your platform for details. The collation for the internal database (the provided copy of PostgreSQL) is determined automatically based on your operating system language settings. Once it is set it cannot be changed without recreating the database environment.
In most cases a single collation combined with the Unicode universal character set will provide satisfactory results for most users.
Requirements for using Oracle on Journyx 8.0 or higher: 1. Oracle 10g or Oracle 11g or higher is required. Earlier versions of Oracle are not supported. 2. Oracle requires a certain level of database administrative knowledge, access, and expertise. If you are planning to install Journyx to Oracle, make sure you have an Oracle DBA (Database Administrator) available to answer any questions regarding the optimal setup. If you do not have an Oracle DBA available then Journyx recommends you use the included PostgreSQL database instead which offers competitive reliablity and performance in most situations. 3. Oracle has a set of default administration passwords that should be changed. The default Oracle accounts / passwords in some versions of Oracle are sys / change_on_install and system / manager. Both of these passwords should be changed immediately after installation, and a separate account for Journyx should be created as described below. 4. The instructions on how to create objects inside Oracle such as users and tablespaces are written based on Oracle 11g, the current version as of this writing. The instructions may have to be adjusted slightly under Oracle 10g. Earlier versions of Oracle such as 9i are no longer supported for Journyx. 5. Oracle provides two client packages for connecting to an Oracle server; the "Full Client" and the "Instant Client". Journyx works with either one, but normally you should only install one or othe other, not both. 6. With either client the connection to the Oracle server is specified in so-called "TNS-less" format. All you need to know is the hostname, Oracle Service Name, and your account username and password. 7. The Oracle Instant Client is a smaller download and is the fastest way to get going. Download it from here: http://www.oracle.com/technology/tech/oci/instantclient/index.html. (Note: it may require you to register with Oracle.com.)
- Only the "Basic" package is required for Journyx. The "Basic Lite" package can be used but has limited support for non-Western languages. You may wish to install the SQL*Plus package for your own testing purposes but it is not required or used by Journyx.
- Other questions about installing and using the Instant Client may be answered here: Oracle Instant Client FAQ.
- On Windows you must unzip the Instant Client files to a directory like c:\instantclient_11_2 and then add this directory to the beginning of your System Path variable and then reboot before installing Journyx. Detailed instructions are found below.
- On Unix you decompress the Instant Client files to a directory like /usr/local/instantclient_11_2 and then set the the ORACLE_HOME environment variable to that location. For instance, $ export ORACLE_HOME=/usr/local/instantclient_11_2
- Please note that you must not have a trailing / (slash) character at the end of ORACLE_HOME. Having a trailing slash can cause the error "Unable to acquire Oracle environment handle" and/or may cause the Journyx installer to abort.
8. If you use Oracle's "Full" client (not the Instant Client) then the appropriate directories are added to the system Path for you. However you may need to reboot after installing the Oracle Client. Be sure to read the setup instructions provided by Oracle. 9. If you have both Oracle Full Client and Instant Client installed, you may run into this error booting the Journyx database: ORA-24315: illegal attribute type. If you get this error, make sure that the Instant Client directory appears in your path before the Full Client path. Look for a path entry like C:\oracle...\bin, or c:\app\\product; and place the Instant Client directory before that one. Or place the Instant Client directory (such as c:\instantclient_11_2) at the beginning of the Path line. Make sure a semicolon (;) separates the entry from the next one. Click OK. You must now reboot the computer for this to take effect. Do so and rerun the "Setup Web Server and Database" program.
Oracle Setup Instructions (first steps for all platforms):
1. Make sure you have read the general instructions here.(http://ssl.salesforce.com/_ui/selfservice/pkb/PublicKnowledgeSolution/d?...)
2. Install the Oracle server (version 10g or higher) if not already installed. Change the passwords for the administrator accounts sys and system if that has not been done yet.
3. Install the Oracle client software on any Journyx application servers and verify that you can connect to your server using SQL*Plus or a similiar tool. Journyx recommends you install the Instant Client as described below.
4. Do not attempt to connect to an already-populated Journyx database, especially if the data is for an older version of Journyx. These instructions assume you're creating a new, blank database. You can run a "restoredb" command later to populate the site from a Journyx .jx backup file.
5. You should be aware of the difference between UTF16 (also called ALT16UTF16 by Oracle) and the UTF8 encoding. Both encodings are Unicode and can store the exact same list of characters. However they are stored in different binary formats which mainly affects the amount of disk space and memory required. UTF8 is more efficient for databases which are primarily Western European languages. UTF16 is more efficient for Asian languages and some others. Using UTF16 to store English or other Western texts can double the size of the database compared to Journyx 7. Oracle offers a choice between the two, but the default is UTF16. You can only set it to UTF8 when initially creating the database, not after creation. Therefore if you are planning to use Oracle with Journyx 8 and your users primarily use Western European languages such as English, Spanish, French, Italian or German, then you should choose UTF8 for your Oracle "national character set" setting when initially creating the database instance as described below. You cannot change this later. UTF16 works fine but has increased storage requirements - approximately double.
6. Open Oracle's Enterprise Manager program (aka Database Control) and log in as SYS or an equivalent role. Use the "Connect as SYSDBA" option.
7. Create the Oracle Instance, if you have not already, following Oracle's instructions for creating instances. Be sure to select UTF8 as the "national character set" if you use English or other Western European languages as described above. The default is UTF16 and this can require increased storage space. Note that the "national character set" setting is separate and distinct from the "character set". Typically to create a new instance you run the "Database Configuration Assistant." On the Initialization Parameters page, select the Character Sets tab. Notice there are two sections, "Database Character Set" and "National Character Set." Journyx only uses the latter, so even if UTF8 is selected for "Database Character Set", it will be ignored. If you wish to use UTF8, then you must select "UTF8" under National Character Set. The "Database Character Set" setting is ignored by Journyx. 8. Create a Data Tablespace for the Journyx user if one doesn't already exist. Normally you will want to create a separate tablespace for Journyx, but it is possible to use the Oracle-supplied USERS tablespace (which is the default for new users) provided there is enough free space. If you plan to use the USERS tablespace, when you create the Journyx user, be sure to leave his default tablespace as USERS
- Click on the "Server" tab.
- Click "Tablespaces" under the Storage heading.
- Click "Create".
- Give the tablespace a name like JOURNYX_DATA.
- Leave the default options: Extent: Locally Managed. Type: Permanent. Status: Read Write.
- Under Datafiles click Add and create a new storage file.
- Make the initial size at least 100 mb. The exact size is not important as long as the datafile is allowed to grow (as is the default.)
- Note: if you choose not to make the tablespace auto-grow, you will be responsible for monitoring Journyx's space usage and adjusting the tablespaces appropriately.
- Click "Create" to bring the tablespace online.
9. Create a Role for the Journyx user.
- In Enterprise Manager, click the Server tab.
- Under "Security" click the "Roles" option then click "Create" on the next screen.
- Name the role something like Journyx_USER. Leave "Authentication" set to None.
- Click on the Role tab then click "Edit List".
- Assign the CONNECT role, and no others, then click OK.
- Click on the "System Privileges" tab then click "Edit List".
- Assign only these privileges (without the Admin/Grant option):
- CREATE SEQUENCE
- CREATE TABLE
- CREATE VIEW
- CREATE TRIGGER
- CREATE PROCEDURE
- Then click OK.
- Click "OK" to finish creating the Role.
10. Create a Journyx user.
- In Enterprise Manager, click the Server tab then select "Users" under the Security header.
- Click "Create."
- Assign a user name like journyx and assign a password. You will need to supply this username and password later.
- Important: Change the default tablespace of the user to JOURNYX_DATA or whatever you called the Data Tablespace. Leave the Temporary tablespace "TEMP" unless your DBA has told you otherwise.
- Click on the Roles tab. Assign the user the Role you created above (Journyx_USER) and no other.
- Click on the Quota tab. Give this user "unlimited" quota on the JOURNYX_DATA tablespace. If you do not give the user quota on his default tablespace, the Journyx Database setup will fail because the user will be unable to create any objects. If you choose to set a quota, be prepared to manually monitor Journyx's space usage and adjust the quota as needed.
- Click "Create" to finish creating the user.
11. You must also grant the EXECUTE privilege on the DBMS_LOCK package to the Journyx database user (or PUBLIC). There are several ways to do this. The most direct way is to run this command in SQL*Plus when logged in as sys or another appropriate system user:
grant execute on dbms_lock to journyx
Replace journyx with the actual name of the database user, or use PUBLIC to grant this to everyone. The DBMS_LOCK package provides resource locking management code for Journyx.
You can also perform this grant through the Oracle Enterprise Manager console. In Oracle 11g, you should click on the instance in question (e.g. ORCL) and then click on Schema. Under the Schema screen, click on "Packages" under the Programs heading. On the search screen put DBMS_LOCK in the Object Name field then click Go. You should see DBMS_LOCK in the search results. Click on the package name DBMS_LOCK. On the next screen, find the Actions pulldown on the top right then select Object Privileges then click Go. Now choose the EXECUTE privilege then find the Journyx database user (or PUBLIC) in the Available Users and Roles list. Then click OK to create the privlege. Now click Apply on the resulting screen to perform the changes.
12. Verify that you can connect as this user from your desired Journyx application server using SQL*Plus or a similar tool. 13. Continue with the appropriate platform-specific instructions below. Windows-only instructions: 1. If using the Oracle Full Client, read Oracle's instructions and install the client and reboot. Test that you can connect to your desired Oracle Server with the SQL*Plus program. 2. If using Instant Client, download it for Windows here: http://www.oracle.com/technology/tech/oci/instantclient/index.html. Be sure to use the 64 bit version of Instant Client if you are using a 64 bit edition of Windows. Only the "Basic" package is required for Journyx. The "Basic Lite" package can be used but has limited support for non-Western languages. You may wish to install the SQL*Plus package for your own testing purposes but it is not required or used by Journyx. 3. Unzip the Instant Client download to a directory like c:\instantclient_11_2 4. Important: You must now add that directory to the system PATH. If you fail to do this, when you try to connect you will get a message like: "Sorry, I cannot load the cx_Oracle module." To add this directory to the path, do this: 1. Right click on "My Computer" (or "Computer") and select Properties. 2. Click "Advanced System Settings" (or sometimes just "Advanced".) 3. Click "Environment Variables." 4. Under "System Variables", scroll down until you see "Path" (or "PATH"). 5. Select Path and click Edit. 6. Move to either the beginning of the line, or else somewhere before any other Oracle related paths. 7. Now type the path from above, such as C:\instantclient_11_2. 8. Make sure it's separated from the other path components with a semicolon (;). So it should look like: c:\instantclient_11_2;c:\[other paths...] 9. Make sure there is NOT a trailing \ (backslash) or / (forward slash) character at the end of the Oracle Instant Client path. Only a semicolon should follow it. 10. Click OK. 11. You must now restart the computer for this change to take effect. Do so and continue following these instructions. 12. If you forgot to do this and got the "Sorry, I cannot load the cx_Oracle module" message, then click "Cancel Database Setup" and follow the above instructions. Then restart the "Setup Web Server and Database" program under Start -> Programs -> Journyx. 13. As mentioned above, if you get this error: ORA-24315: illegal attribute type then you should again check that the Instant Client directory is before any other Oracle directories in the System Path. 5. Once you have added the Oracle Instant Client directory to the system PATH you are ready to install Journyx. 6. You should NOT normally need to create a "tnsnames.ora" file. Journyx uses a "TNS-less" form of connection that can connect to any Oracle server that is accessible on your network. This applies whether you are using the Instant or Full Client, on both Windows and Unix. 7. Run the Journyx installation program. Allow it to reboot your computer at the end and the "Setup Web Server and Database" program will run. 8. Choose the webserver such as IIS and other options as prompted. 9. You will be asked if you want to use the internal PostgreSQL connection or if you want an external database connection. Choose "Connect to an external database system." 10. Select the Oracle option. 11. Put in your connection information as prompted:
- Oracle hostname or IP address
- Oracle port (default is 1521)
- Oracle Service Name. This is defined in the "service_names" configuration parameter in Oracle server.
- User name: the Oracle account name.
- Password: the password for that Oracle account.
- Index tablespace: normally you can leave this blank unless you want the indexes on a separate tablespace. Fill in this info and click OK. If a connection cannot be established, for instance due to a wrong password or hostname, you will be given the opportunity to either try again with the same info (for instance, if the Oracle service was temporarily offline) or else to create a new connection with different info.
12. At this point it will create all the tables and views for Journyx and will eventually send you to a login screen. It will pop up an alert message with the initial username and password. 13. If necessary, you can run a "restoredb" command from the command line to populate your Journyx data from a .jx backup file. Linux-only instructions: 1. Download the Instant Client for Linux here: http://www.oracle.com/technology/tech/oci/instantclient/index.html. Be sure to use the 64 bit version of Instant Client if you are using a 64 bit edition of Linux. Only the "Basic" package is required for Journyx. The "Basic Lite" package can be used but has limited support for non-Western languages. You may wish to install the SQL*Plus package for your own testing purposes but it is not required or used by Journyx. 2. Download the Journyx installation .tar.gz file for your platform. If you are using a 64 bit edition of Linux, you need to use the 64 bit edition of Journyx, as well as the 64 bit Oracle Instant Client. 3. Unzip the Instant Client download to a directory like /usr/local/instantclient_11_2 4. You should NOT normally need to create a "tnsnames.ora" file. Journyx uses a "TNS-less" form of connection that can connect to any Oracle server that is accessible on your network. 5. A symbolic link must be created in the Oracle Instant Client directory. The Journyx installer can create this for you if it has permissions, but just in case here is how to create the link manually. 1. su to root, or sudo su - 2. cd /usr/local/instantclient_11_2, or whatever directory you extracted the Instant Client files to. 3. Run: ls libclntsh.so.* You should see a file listed like libclntsh.so.11.1 but the number might be different. 4. Run: ln -s [FILE from above] libclntsh.so so something like: ln -s libclntsh.so.11.1 libclntsh.so 6. Un-tar-gz the Journyx installer. Typically you run something like tar xzvf JournyxJournyx_80_Linux.tar.gz 7. Change into the resulting jtime directory. 8. Set the ORACLE_HOME environment variable to the location you extracted Instant Client. For instance, export ORACLE_HOME=/usr/local/instantclient_11_2 9. Make sure there is NOT a trailing / (slash character) at the end of the ORACLE_HOME path. Having a trailing slash at the end of the path can cause installation problems or the connection error "Unable to acquire Oracle environment handle." 10. Run the ./jtinstall program. It will guide you through prompts for your Oracle information. Or you can save time by providing all the information at the command line in this format: ./jtinstall --oracle "username/password@//host:port/instance" where:
- username is the Oracle account name.
- password is the Oracle account password.
- host is the Oracle hostname or IP address.
- port is the Oracle port (default is 1521).
- Instance is the Oracle Service Name. This is defined in the "service_names" configuration parameter in Oracle server. Be sure to enclose the entire connection string in double-quotes.
11. If necessary, you can run a "restoredb" command from the command line to populate your Journyx data from a .jx backup file.