Required Database Configuration
PeopleSoft applications require a standard database configuration that's not optional and should not be changed. This section discusses the options that you must enable:
Quoted Identifier, Arithabort, and functional index.
Database collation settings.
Make sure your database uses ANSI nulls by default. This is a database option that will be set up at installation time. The configuration occurs automatically when using the Database Configuration Wizard and is enabled by the SQL script addobj.sql when installed manually.
The following line shows how to enable this parameter using Query Analyzer:
EXEC sp_dboption databasename, 'ansi null default', true
Working with Functional Indexes
PeopleSoft uses computed columns that allow the creation of functional indexes. A functional index is an index created to keep uniqueness in a table when the number of keys exceeds the SQL Server limit, which is a maximum of 16 key columns for an index. What makes a functional index special is that it's required only when the number of key columns exceeds the SQL Server limit.
PeopleSoft implements the functional index by creating an index over a computed column. The computed column MSSCONCATCOL is the sum of all the key columns required to keep uniqueness.
In order to create indexes on computed columns, SQL Server requires the Quoted Identifier option to be enabled in the database. This is the default configuration, but this option could be overridden as a connection option from any client. If you are using Query Analyzer to run SQL scripts, look at Tools, Options, Connection Properties on your Query Analyzer menu and make sure the Quoted Identifier option is selected, which will activate it for that particular connection.
Another important option that needs to be enabled to operate computed columns is the database property Arithabort. Make sure this option is enabled for your PeopleSoft database.
Note: Both Quoted Identifier and Arithabort are explicitly set during installation automatically by the Database Configuration Wizard or when running the script, createdb_2005.sql, at the database installation.
Database Collation Settings
The use of the right collation is very important for PeopleSoft applications. PeopleSoft delivers its applications with a standard collation of Latin1_General_Bin on SQL Server. This collation was selected for being compatible with the binary sort order used on previous versions of SQL Server.
However, PeopleSoft supports other sort orders with some applications. The application installation manual will point out whether this is permitted for a particular application. The sort order supported must be Kana sensitive, case sensitive and accent sensitive. Therefore a collation such as Latin1_CS_AS_KS is supported. Note that the Latin1_General_Bin collation also satisfies this requirement.
Consult your PeopleTools installation guide and the application installation manual for further details on the collation configuration required for your database server.
For environments running English-only databases and languages covered by the Latin1 character set (such as Western European languages), PeopleSoft recommends the collation delivered as default in the PeopleSoft installation scripts. The database collation is set when running the creatdb_2005.sql script at installation time. The script runs automatically when you use the database configuration wizard. It is a requirement to run the script when installing the database manually.
See the product documentation for PeopleTools 8.53 Installation for Microsoft SQL Server.