Home
>
Lucent Technologies
>
Communications System
>
Lucent Technologies Intuity CONVERSANT System Version 7.0 Guide
Lucent Technologies Intuity CONVERSANT System Version 7.0 Guide
Have a look at the manual Lucent Technologies Intuity CONVERSANT System Version 7.0 Guide online for free. It’s possible to download the document as PDF or print. UserManuals.tech offer 413 Lucent Technologies manuals and user’s guides for free. Share the user manual or guide on Facebook, Twitter or Google+.
6 Database Administration Call Data Tables Intuity™ CONVERSANT® System Version 7.0 Administration 585-313-501 Issue 3 January 2000 357 Note:If the table name is lower case, do not use the describe command. Instead, use desc as follows: desc table_name 3 Enter quit The system exits the sqlplus session. Table Searches Think of a database table as a book. If you want to find information on a subject, you must search the book to find it. However, checking the index first helps you to locate the information in the book much more quickly than paging through the book. The same is true for finding data in a database table. Indexes and Key Fields For large databases, you can use indexes on key fields to greatly reduce the time necessary to search the tables. Indexed fields can be especially important in applications that require a “lookup” from a large table based on user input. This input generates an SQL statement for accessing the database that has the following form: SELECT * from table_name where FIELD1 = ‘data’;
6 Database Administration Call Data Tables Intuity™ CONVERSANT® System Version 7.0 Administration 585-313-501 Issue 3 January 2000 358 If FIELD1 has an index created for it in the database, all records that match the criteria specified in the select statement are located much faster than if there is no index. FIELD1 is a key field in this example because it is the field used to specify selection criteria. Indexes only decrease read time when they are created on key fields. An SQL statement may have more than one key field, as in the following example: SELECT * from table_name where FIELD1 = ‘data1’ AND FIELD2 = ‘data2’; In this example, FIELD1 and FIELD2 are key fields. Create an index for each of these fields to enhance system performance.Unique IndexesUnique indexes on fields enforce uniqueness of the data in that field across the entire table of records. For example, a field for which you might create a unique index is one that contains a social security number (SSN). A unique index on an SSN field ensures that only one record with a given SSN can exist in the table. Attempts to add records with that SSN will fail.
6 Database Administration Call Data Tables Intuity™ CONVERSANT® System Version 7.0 Administration 585-313-501 Issue 3 January 2000 359 Use the following procedure to create a unique index on a field called FIELD1 in a table called table_name . 1 At the Console Login: prompt, enter root The system prompts you for a password. 2 Enter your root password. The system displays the system prompt #. 3 Enter sqlplus sti/sti The system starts a sqlplus session. 4 Enter create unique index index_name on table_name (“FIELD1”); The system creates a unique index for FIELD1 in the table. Non-Unique IndexesNon-unique indexes do not prevent the same data from appearing in that field in several records in the same table. For example, if a field contains the area code of a telephone number and an index is created for that field, it must be a non-unique index since other records may require the same number in their area code field.
6 Database Administration Database Interface Process Intuity™ CONVERSANT® System Version 7.0 Administration 585-313-501 Issue 3 January 2000 360 Use the following procedure to create non-unique indexes on fields called FIELD1 and FIELD2 in a table called table_name : 1 At the Console Login: prompt, enter root The system prompts you for a password. 2 Enter your root password. The system displays the system prompt #. 3 Enter sqlplus sti/sti The system starts a sqlplus session. 4 Enter create index index_name on table_name (“FIELD1”); The system creates a non-unique index for FIELD1 in the table. 5 Enter create index index_name on table_name (“FIELD2”); The system creates a non-unique index for FIELD2 in the table. Database Interface Process A voice system application accesses the database tables through a single database interface process (DIP). A DIP is a software program that connects to the database and provides the only interface between the application and the database.
6 Database Administration Database Interface Process Intuity™ CONVERSANT® System Version 7.0 Administration 585-313-501 Issue 3 January 2000 361 Database DIP Timeout The voice system and remote database ORACLE connection is established when the voice system starts. After the connection is established, the voice system does not keep track of status changes on the remote machine. The connection between it and the voice system is dropped if the remote machine is turned off or rebooted. If the remote machine is shut down and rebooted while the voice system is still active, the voice system detects this status change only when calls come in to the system that involve remote database access for call processing. The voice system attempts to reestablish the remote connection and is not able to process calls during this time. In certain cases, the database DIP (oraldb) may not receive a timely response from the server machine. This may be due to a variety of factors, such as the server machine being down, the server machine is operating slowly, an application query of a large non-indexed table, network congestion, etc. While the DIP is waiting for a response from the server machine, the message queue of the DIP may back up. A full message queue (current maximum is 255) may result in performance problems for the voice system. In order to prevent this, the DIP is equipped with a timeout mechanism. By default, the DIP will timeout every 45 seconds while waiting for a response. After the timeout, the DIP deletes the messages currently queued and continues to wait for a response from the database. The DIP continues to timeout every 45 seconds and to empty the message queue. After the default of 300 seconds, the DIP will automatically respawn and reinitialize.
6 Database Administration Database Interface Process Intuity™ CONVERSANT® System Version 7.0 Administration 585-313-501 Issue 3 January 2000 362 The 45- and 300-seconds timeout values can be altered in /vs/data/ldbdip.rc file. This file is included with the generic package and contains the following default values: FIRST_TMOUT=45 SECOND_TMOUT=300 You may change these default values to any number that is appropriate for your database and applications. If the ldbdip.rc file is missing, the DIP uses the default timeout values of 45 and 300 seconds.! CAUTION: Always stop the voice system before shutting down the remote database machine to avoid an unexpected interruption of service. Database Cursors An internal data structure called a database cursor is used to monitor the point from which the DIP is reading in a specific database table. One cursor is allocated for each read of each database table by each channel running a service that requires access to that database. The cursor remains assigned to that table until the service ends on the channel for which the cursor was allocated.
6 Database Administration Database Interface Process Intuity™ CONVERSANT® System Version 7.0 Administration 585-313-501 Issue 3 January 2000 363 The number of cursors is tunable in the /oracle/dbs/initA.ora file (open_cursors is a tunable parameter). At least 500 cursors are supported in the Intuity CONVERSANT system. Once the limit of cursors is reached, database transactions do not complete successfully; that is, table reads may fail and inserts or updates may not occur. Database Cursor Calculations To insure the integrity and consistency of the data in the database, you must keep this limiting factor in mind when you design your applications. Use the following formula to determine the number of database tables that may be accessed by an application with the voice system: channel X read X cursor < 500 where:• channel = number of channels running application with database access • read = number of read table operations performed on different tables by applications (per channel) • cursor = number of cursors involved in read table Note:Multiple reads of the same table use only one cursor. The following are sample calculations using various configurations and numbers of read table operations.
6 Database Administration Database Interface Process Intuity™ CONVERSANT® System Version 7.0 Administration 585-313-501 Issue 3 January 2000 364 • If you have a 24-channel system running an application that performs four read table operations on four different tables per channel, the calculation is: 24 ´ 4 ´ 1 = 96 Since 96 is less than 500, the database operations proceed properly. • If you have a 36-channel system running an application that performs four read table operations on a single table per channel, the calculation is: 36 ´ 1 ´ 1 = 36 Since 36 is less than 500, the database operations proceed properly. • If you have a 48-channel system running an application with five Read table operations on five different tables per channel, the calculation is: 48 ´ 5 ´ 1 = 240 In this case, if all 48 channels are performing five read table operations, some database operations may fail because of the multiple read table operations on the same channels. • An application developer wants to develop an application that executed six read table operations on six different tables per channel: 500 / (6 ´ 1) = 83.33 Therefore, the application can run on as many as 80 channels before it encounters database access problems.
6 Database Administration Increase Database Storage Size Intuity™ CONVERSANT® System Version 7.0 Administration 585-313-501 Issue 3 January 2000 365 • An application developer wants to develop an application to run on 72 channels simultaneously: 500 / (72 ´ 1) = 6.94 Therefore, the application can perform up to six read table operations on up to six different tables per channel before it encounters database access problems. Increase Database Storage Size The amount of storage space needed for the database should be decided at the initial installation because the size of the database can affect the disk partition sizes (the database is stored in the /oracle file system). Use the following procedure to increase the database size: Note:The voice system does not need to be stopped while resizing the database. 1 At the Console Login: prompt, enter root The system prompts you for a password. 2 Enter your root password. The system displays the system prompt #.
6 Database Administration Increase Database Storage Size Intuity™ CONVERSANT® System Version 7.0 Administration 585-313-501 Issue 3 January 2000 366 3 Enter /vs/bin/util/dbfrag The system displays the number of free database blocks. 4 Multiply the number of free database blocks by 4 to get the actual number of free 512-byte blocks. 5 Enter df /oracle The system displays the number of free blocks available in the /oracle file system. 6 Determine the number of bytes to add to the database by subtracting the current database size (in blocks) from the optimum size. 7 Enter /oracle/bin/svrmgrl The system enters the server manager mode. 8 Enter connect internal 9 Enter alter tablespace system 10 Enter add datafile ‘/oracle/dbs/dbsA2.dbf’ Note:The file name dbsA2.dbf is a recommended name. If this file already exists in this directory, use dbsA3.dbf 11 Enter size number where number is the number of bytes calculated in step 6 that you want to add to the database.