Appendix: Administering PeopleSoft Databases on Sybase

This appendix discusses:

• Required configuration.

• Trace options.

• Other considerations.

Required Configuration

PeopleSoft applications require certain standard configuration at the server and the database that are not optional and cannot be changed. This section discusses the following options that you must have enabled:

• Server Options.

• Database Options.

Server Options

This section discusses the following options:

• Lock scheme to datarows

• Lock promotion

• Language options

• Page size

• EBFs

Lock Scheme to Datarows

Row level locking is preferred and required with PeopleSoft applications. The following command enables the necessary configuration for your server:

sp_configure 'lock scheme', 0, 'datarows'
go

Lock Promotion

The following is the configuration delivered for the installation:

sp_setrowlockpromote server, NULL, 2147483647, 2147483647, 100
go

The purpose of setting this parameter to this value is to avoid the promotion of row level locking, which could potentially increase considerably the amount of locks required on certain activities such as loading a database through datamover. You can modify the configuration in order to allow the promotion of locks earlier when loading databases, but remember to change the configuration back after the process ends.

Language Options

When installing your server, use the iso_1 character set as the default for your server.

Page Size

PeopelSoft stopped supporting the 2k page size for tools 8.44 and above. Select a 4k or 8k page size during server installation. The 16k page size is not supported.

EBFs

Starting with PeopleTools 8.44, PeopleSoft certifies Sybase ASE by its interim release. All of the EBF’s above the certified interim release are certified until the next interim release is reached.

Database options

Make sure your database uses ansi nulls by default. This is a database option that is set up at installation. The configuration occurs automatically when using the database configuration wizard and is enabled by the SQL script createdb.sql when installed manually.

The following line shows how to enable this:

sp_dboption dbname, 'allow nulls',true
go

Another option that needs to be enabled is the following:

sp_dboption dbname, 'ddl in tran',true
go

During the database load it is recommended to truncate the transaction log. The following command is executed at installation:

sp_dboption dbname, 'trunc',true
go

Remember to disable the truncation of the transaction log if desired. This option should not be enabled for production databases.

Trace Options

This section discusses:

• Trace flags in PeopleSoft tools.

• Sybase API-specific tracing.

Trace Flags in PeopleSoft Tools

When reporting problems to customer support, it is advisable to generate files with traces of the problem that you want to report. Use the trace flags incorporated in PeopleSoft tools to generate these files. The trace flags are accessible through the configuration files for the Process Scheduler and the Application Server and through the selection of several flags when using the PeopleSoft Configuration Manager on your developer workstation.

Use “TRACESQL=63” to display the SQL statements executed when using PeopleSoft applications. This trace flag is very useful to identify problems in the SQL being executed against a database that hosts a PeopleSoft application.

The trace flag will show the details about the execution of a sql statement such as: if the statement was recompiled or if it’s using an old query plan, the time it took to execute, the time between executions, if the SQL was parametrized, among other things.

See Also

Setting Application Server Domain Parameters

Using the PSADMIN Utility

Using PeopleSoft Configuration Manager

Sybase API-Specific Tracing

When using Sybase, you can select from three SQL Trace options that you can use to enable very detailed Sybase tracing:

• Database API-specific Calls

• Sybase Bind Information

• Sybase Fetch Information

To set these options, use the Trace tab in the PeopleSoft Configuration Manager. Keep in mind that online performance will be affected.

The output of the Sybase tracing can be found in %TEMP%/SYBxxx.TMP, where xxx is a random integer that is different for each file based on each connection.

If you select any options other than Database API, Sybase Bind Information, and Sybase Fetch Information, the tracing output will be found in %TEMP%/DBG1.TMP.

Note. Only use tracing for debugging purposes, since performance will be affected. Depending on what level of tracing you select, a very large file can be created. To turn tracing off for Windows, clear the boxes in Configuration Manager.

See Also

Using PeopleSoft Configuration Manager

Enterprise PeopleTools 8.49 PeopleBook: PeopleSoft Process Scheduler

Other Considerations

This section discusses:

• Database monitoring.

• Device management.

• Caches.

• Segments.

• Tempdb.

• Network packet size.

• Updating statistics.

Database Monitoring

Activation of the “EnableDBMonitoring” flag, available through the configuration files for the Process Scheduler and the Application Server, allows you to populate context information of the query executed against the database. This is particularly useful when looking for information about the PeopleSoft user running a particular SQL statement.

Example of SQL Statement

The following is an example of SQL statements that will display the context information of a user once “EnableDBMonitoring” is enabled. Modify the scripts according to your needs.

select clientname, clienthostname, clientapplname from master..sysprocesses where spid=spid

If you don’t know the SPID of the user you are trying to monitor, start with the DBID.

Device Management

Try to use separate physical devices for the various servers in your system. Ideally, you should have one device for database data, one device for tempdb, one device for master, one device for syslogs, and one device for the operating system.

Note. Tempdb and transaction logs (syslogs) are used very heavily. PeopleSoft highly recommends using a separate device and allocating adequate space. Mirror the syslogs for recovery.

Caches

Consider using named caches on tempdb and syslogs. Also, consider experimenting with different private log cache sizes as this can improve performance. You can definitely reduce the contention for the last page of syslogs by increasing the size of the Private Log Cache for the users so that they will write to the syslogs table less frequently. Your Database Administrator should determine required memory to support your number of users.

Segments

Consider using segments to separate data, non-clustered indexes, and heavily used tables onto separate devices.

Tempdb

Tempdb is heavily used for sorting (order by statements) and to create worktables for “OR” and “GROUP BY” statements. It is rebuilt every time the dataserver is booted; no permanent data is stored in it. Because of this, the normal considerations for recoverability do not apply to tempdb.

Note. You should consider binding tempdb to its own named cache.

Sizing

Tempdb should be sized according the number of concurrent users, the size of the sorts or group by statements, and the largest possible sort that might be done in tempdb. You will need to consider all databases running on your dataserver because they all share tempdb.

Placement

Whether to place tempdb on a journaled file system, a logical volume, a raw device or a solid-state device is platform-dependent. Following are some considerations for each:

• Journaled File System

  Placing tempdb on a journaled file system is faster on many platforms because the OS buffers the writes so that Sybase Adaptive Server doesn’t have to wait for physical disk writes. If you have enough OS memory to buffer all of the writes, then tempdb can essentially stay in memory and never have to write to disk.

  When you initialize a file system device(file), Sybase Adaptive Server does not know that there is really enough space in the file system for the size you specify. At run time, the server may well run out of space if the file system was—or became—too full for the tempdb file.

  A precaution would be to configure a separate file system solely for the tempdb file, and set permissions so that only the server can write to this device.

  Note. Do not place user databases on a journaled file system as Adaptive Server cannot guarantee recoverability if the system shuts down abnormally.

• Raw Device

  There are no special considerations for placing tempdb on a raw device.

• Solid-State Device

  This is a special device that essentially runs in memory. If tempdb is an I/O bottleneck, then placing tempdb on a solid-state device can improve performance

Another consideration is placing the tempdb syslogs on a separate device.

Network Packet Size

You may be able to improve performance for large result sets by matching the Sybase data packet size to your network packet size and reclaiming unused network bandwidth. Larger packets will also improve network performance by reducing the number of packets sent between the client and server.

From within Configuration Manager, on the Common tab of the Edit Profile dialog box, you can increase the TCP Packet Size for Sybase. Sybase uses a default of 512 bytes and it accepts packet sizes in increments of 512.

The Sybase server must also be configured to accept the larger packet size. To increase the packet size at the server level, issue the following command using Sybase ISQL or a similar SQL utility:

1> sp_configure 'max network packet', 1024
2> go

Note. Increase the network memory allocated per connection using the sp_configuration additional network memory command when increasing the max network packet size.

These server commands will require the Sybase dataserver to be rebooted before the configurations will take effect

Note. PeopleSoft does not recommend increasing the dataserver default network packet size from the default value of 512. This will ensure that all PeopleSoft clients are able to connect. If the TCP Packet Size is increased on the client with Configuration Manager and the max network packet size is not increased on the server, Signon failure will occur.

See Also

Sybase Adaptive Server Enterprise Reference Manual

Sybase Adaptive Server Enterprise Performance and Tuning Guide

Updating Statistics

When an index gets created, the system gathers statistics about the table. These statistics help to determine the best search method for accessing a table. Each time an index gets created, the statistics are updated for that table. When an index is dropped, the statistics are not removed. In this case you will want to delete the statistics for the dropped index. Use the Sybase DELETE STATISTICS command follow by an UPDATE STATISTICS to rebuild your existing index and column statistics.

You should also update database statistics if there have been significant changes to the index—such as adding or deleting a large number of rows in a table. To do this, use the Sybase UPDATE STATISTICS command. You can run this command against tables and indexes in a database.

Note. There is no command to delete and update statistics for an entire database.