Home

Informix I-Spy User`s Manual

1. To construct a command correctly start at the top left with the command Follow the diagram to the right including the elements that you want The elements in the diagram are case sensitive Figure 1 illustrates the following steps 1 Type setenv 2 Type INFORMIXC 3 Supply either a compiler name or a pathname After you choose compiler or pathname you come to the terminator Your command is complete 4 Press RETURN to execute the command Introduction 9 Sample Code Conventions 10 Sample Code Conventions Examples of SQL code occur throughout this manual Except where noted the code is not specific to any single Informix application development tool If only SQL statements are listed in the example they are not delimited by semicolons For instance you might see the code in the following example CONNECT TO stores demo DELETE FROM customer WHERE customer num 121 COMMIT WORK DISCONNECT CURRENT To use this SQL code for a specific product you must apply the syntax rules for that product For example if you are using DB Access you must delimit multiple statements with semicolons If you are using an SOL API you must use EXEC SQL at the start of each statement and a semicolon or other appro priate delimiter at the end of the statement Tip Ellipsis points in a code example indicate that more code would be added in a full application but it is not necessary to show it to describe th
2. ACTION NOLOG EXPLAINTAGS is also used as a global parameter for the configuration file instead of an action keyword as shown here This allows you to have a global setting for the value but also change the value for specific sessions ACTION NOLOG Syntax ACTION NOLOG Example ACTION NOLOG NOLCG specifies that this specific SOL statement should not be saved in the activity database A common use for this action is to filter out those SQL state ments that you are not interested in saving For example you could use the following rules to filter out all INSERT UPDATE and DELETE SQL statements as well as any that reference the system catalog tables systables syscolumns and sysindexes rule 1 SQL insert ACTIO 0106 rule 2 SQL upda ACTIO 0LOG rule 3 SQL dele ACTIO OLOG rule 4 SQL systables ACTIO OLOG rule 5 SQL syscolumns ACTIO OLOG rule 6 SQL sysindexes ACTIO 0LOG Configuration File 3 13 ACTIVITY CAPTURE 3 14 ACTIVITY CAPTURE Syntax ACTIVITY CAPTURE 0 1 2 Example ACTIVITY CAPTURE 1 Required Yes ACTIVITY CAPTURE specifies what the I 5py daemon should do with the SQL activity that is captured for a client connection You must specify one of the following values m 0 Nocapture 1 Save SQL statements in the activity database 2 Save SQL statements in the file system for later loading into the activity database ACTIV
3. s 200 a 4 eoe bee 1 4 Current Limitations 2 4 s 1 5 1 2 Informix I Spy User Manual In This Chapter Informix I Spy provides a query management facility to monitor and control SQL statements submitted to an Informix database server I Spy transparently intercepts all SOL statements submitted to a database server and saves them in an activity database Database administrators and developers can use this historical collection of SQL activity to tune the database server and gain a precise understanding of how clients use the system I Spy also provides a rule system by which you can place constraints such as maximum query time or maximum rows returned on individual SOL statements This manual provides detailed instructions for configuring and using I Spy to monitor the SQL activity of your database servers What Is I Spy I Spy is a daemon process that looks like a database server to client applica tions It listens on a TCP port for connections and routes those connections and all associated traffic to an actual Informix database server For the remainder of this manual this Informix database server is referred to as the shadow server While it is routing the network traffic the shadow server decodes all messages and logs the SQL statements and associated statistics to an activity database An I Spy configuration consists of the following components The Informix database clie
4. Informix I Spy User Manual Version 1 0 January 1999 Part No 000 5317 Published by INFORMIX Press Informix Corporation 4100 Bohannon Drive Menlo Park CA 94025 1032 1999 Informix Corporation All rights reserved The following are trademarks of Informix Corporation or its affiliates Answers OnLine Store C ISAM Client SDK ContentBase Cyber Planet DataBlade Data Director Decision Frontier Dynamic Scalable Architecture Dynamic Server Dynamic Server Developer Edition Dynamic Server with Advanced Decision Support Option Dynamic Server with Extended Parallel Option Dynamic Server with MetaCube ROLAP Option Dynamic Server with Universal Data Option Dynamic Server with Web Integration Option Dynamic Server Workgroup Edition FastStart 4GL for ToolBus If you can imagine it you can manage it INFORMIX Informix Data Warehouse Solutions Turning Data Into Business Advantage INFORMIX Enterprise Gateway with DRDA Informix Enterprise Merchant INFORMIX 4GL Informix JWorks M InformixLink Informix Session Proxy InfoShelf Interforum I Spy Mediazation MetaCube NewEra ON Bar OnLine Dynamic Server OnLine for NetWare OnLine Secure Dynamic Server OpenCase ORCA Regency Support Solution Design Labs Solution Design Program SuperView Universal Database Compo
5. select from customer and EXPLAINTAGS was set to 1 I 5py would change the SQL statement as follows before sending it to the shadow database server select from customer ISPY0 3 56 The database server would ignore the appended text since it is formatted as a comment but it would be written to the sqexplain out file for the isexplain utility to use later Configuration File 3 19 HOST This feature is configurable because appending the comment does add additional overhead to client connections that you might want to avoid You can also specify EXPLAINTAGS as a rule action if you only want to add this functionality in certain situations For example if you only want to use isexplain to capture explain text for users john and karen you would set EXPLAINTAGS to 0 and use the following two rules EXPLAINTAGS 0 PHASE connect RULE 1 USER john ACTION EXPLAINTAGS 1 RULE 2 USER karen ACTION EXPLAINTAGS 1 HOST Syntax HOST hostname Example HOST serverl Required No HOST is a rule condition and specifies a host name to match against the client host name If the names match the condition is considered true HOST is associated with whatever rule immediately preceded it in the configuration file Regular expressions are permitted for the host name allowing you flexibility in matching hosts For more information on regular expressions see Regular Expression Syntax on page 3 6 3 20 Informix I Spy User
6. of these files contains a single SQL statement used to create a report from the activity database Each file corresponds to a report available in the Web interface for I Spy You cannot use these files directly because they contain placeholders for various SOL WHERE clause values such as user names and dates You must substitute appropriate values before you run them against the activity database web The web directory contains all of the files required for the Web interface to I Spy Chapter 4 describes how to configure and operate the Web interface It is recommended that you successfully configure I Spy and make sure it is operating correctly before you attempt to use the Web interface Environment Variable Settings A number of environment variables are required to start the I 5py daemon process or execute any of the I Spy utilities The following environment variables are covered in this section ISPY DIR ISPY CONFIGFILE INFORMIXDIR and INFORMIXSQLHOSTS Shared library search path PATH Important These environment variable settings are only required on the computer where I Spy has been installed to execute and administer I Spy They are not required by client applications that connect through I Spy to an Informix database server Informix Spy User Manual ISPY DIR ISPY DIR ISPY DIR is a required environment variable and contains the full path to the directory where you have installed I Spy For example if you instal
7. 0 if there was no error act abortrule INTEGER The rule number from the I Spy configu ration file that caused this statement to be stopped or 0 if no rule was involved act explain id VARCHAR 25 A link into the ispy explain table where the explain information for this statement might be located 3 of 3 It is important to understand how clients execute SQL statements and how I Spy reflects this in populating the ispy activity table Simple SQL state ments in client applications that are not prepared and have no cursor associated with them result in a single row each time they are executed SQL statements that are prepared and executed result in a single row for each execution of the prepared statement with the text of the SOL statement contained in each row For example consider the following lines in an INFORMIX ESQL C program exec sql prepare pl from delete from customer where cust num 100 exec sql execute pl exec sql execute pl exec sql execute pl This example will result in three rows in the ispy activity table where each act sqlstmt column contains the text delete from customer where cust num 100 Each row will have a unique id value and they are not related in any way in the table SQL statements with cursors operate in the same manner Each time a cursor is opened and closed a new row is written to the ispy activity table If the act prepare column is null it indicates that the clien
8. ITIME parameters are all conditions for rule 10 If a client is user informix the client is on a host named cheetah and they are inactive for more than 10 minutes ITIME 600 I Spy performs two actions The first action is to end the session and the second is to execute the specified shell script Tip There is no parameter that signifies the end of a rule All conditions and actions that appear after a RULE parameter are associated with that rule until either another RULE or PHASE parameter is found or the end of the file is reached Configuration File 3 5 Rule Execution Order 3 6 Rule Execution Order Itis possible to have more than one rule match the execution of a single SOL statement In this case rules are executed in the order in which they appear in the configuration file not in the order of the rule number Along the same lines if a rule defines multiple actions to execute they will be executed in the order in which they appear in the configuration file Consider the following rules PHASE active RULE 1 SQLLENGTH 128 ACTION execute usr local bin long sql sh RULE 2 ROWS 1000 ACTION execute usr local bin rows1000 sh ACTION error Maximum 1000 rows exceeded If an SOL statement longer than 128 characters is submitted and it returns more than 1000 rows the following actions occur in the order listed 1 The script usr local bin long sql sh is executed 2 The script usr local bin rows1000 sh is
9. Intro 4 SQL 3 27 SQL code Intro 10 sqlhosts ACTIVITY_INFORMIXSERVER 3 17 ISPYSERVER 3 21 locating the file 2 8 preparing the file 2 11 SHADOWSERVER 3 27 SQLLENGTH 3 28 SQLTRIM 3 28 srm conf 4 5 4 6 System requirements database Intro 4 software Intro 4 T Tip icons Intro 7 U USER 3 30 Users types of Intro 4 Utilities isexplain 5 3 isload 5 5 ispy 5 6 W Warning icons Intro 7 Web interface Apache web server 4 3 Clients menu option 7 8 Config File menu option 7 7 configuring security 4 8 database Connect privilege 4 7 main window 7 4 Netscape web server 4 12 preparing web cfg 4 4 A BC D EF GH JK LM gt QR STU VW XY Z Reports menu option 7 10 Shutdown menu option 7 6 Startup menu option 7 5 Status menu option 7 4 User Manual menu option 7 12 View Log menu option 7 12 X XA Protocol 1 5 X Open compliance level Intro 12 Index 3
10. Manual ISPYSERVER ISPYSERVER Syntax ISPYSERVER servername Example ISPYSERVER cougar_spy Required Yes ISPYSERVER defines a new server name for client connections Connections to this new server name are routed to the database server defined by the SHADOWSERVER parameter For example consider the following sqlhosts file cougar_net ontlitcp cougar portl cougar_spy ontlitcp cougar port2 The server name cougar_net is the direct connection to the actual Informix database server or shadow server and the cougar spy entry is the new entry for I Spy The ISPYSERVER parameter should be set to cougar spy The I Spy daemon process will listen on the port specified port2 for client connections just as the database server would When a new client connection is received the daemon process will open a connection to the shadow server cougar net in this case and route all message traffic to this database server Set INFORMIXSERVER environment variable to the value of ISPYSERVER for clients that you want to use I Spy Warning The ISPYSERVER name must be exactly the same length as the SHADOWSERVER name Configuration File 3 21 ITIME ITIME Syntax ITIME seconds Example ITIME 3600 Required No ITIME IS a rule condition that specifies the amount of inactivity time for a client connection in seconds Another way to say this is the amount oftime a client has not submitted a request to the database server If the amou
11. View Log Menu Option Field Description Unload field delimiter A single character used to separate fields if the output format chosen is unload Show the SOL If this box is checked the SOL statement executed against statement used for the activity database is included with the report results this report This is useful if you want to create your own reports and see how they were generated The View Log Menu Option The View Log menu option displays the last 20 lines of the I Spy log filein a new window as Figure 7 9 shows Figure 7 9 View Log Window This window displays the output of the ispy m command 7 12 Informix I Spy User Manual The User Manual Menu Option The User Manual Menu Option The User Manual menu option opens the pdf file that contains the Informix I Spy User Manual The Adobe Acrobat Reader must be installed to view this file Using The Web Interface 7 13 Common Questions Common Questions About I Spy TEM 8 3 How do I prevent users from returning more than 10001 rows in a single query x e 8 3 How do I send an email to the datdbabe query has returned more than 1000 rows without ending the query 8 4 How do I prevent all users except informix from ME more than 1000 rows now en 4826 How do I assign a default PDO ior all t gt s 86 How do I check the syntax of the rules in my configuration file without shutting down and
12. Web administrator for this user name For example the Apache Web server uses a configuration file named httpd conf that specifies this user The following lines show the section of this file that specifies that user User Group The name or number of the user group to run httpd as On SCO ODT 3 use User nouser and Group nogroup if On HPUX you may not be able to use shared memory as nobody and the suggested workaround is to create a user www and use that user User www Group www In this example the user is named www so you would grant Connect privilege by executing the following SQL statement in DB Access grant connect to www The Web interface comprises a number of CGI or Common Gateway Interface scripts that perform the various activities on the Web interface main menu Your Web server executes these scripts as the user defined in the Web server configuration settings Since some of these scripts access the activity database it is necessary to grant Connect privilege If you have other users besides informix or the Web interface that need access to the activity database grant them Connect privilege as well These users might be accessing the activity database for reporting or other informational purposes For example to grant Connect privilege for users george and tom execute the following SOL statements in DB Access grant connect to george grant connect to tom Activity Database Layout 6 7 ISPY S
13. actions on UNIX use one of the following utilities Utility Description finderr Displays error messages on line rofferr Formats error messages for printing To read error messages and corrective actions in Windows environments use the Informix Find Error utility To display this utility choose Start2Programs Informix from the Task Bar Introduction 11 Documentation Notes Release Notes Machine Notes UNIX Instructions for using the preceding utilities are available in Answers OnLine Answers OnLine also provides a listing of error messages and corrective actions in HTML format Documentation Notes Release Notes Machine Notes In addition to printed documentation the following sections describe the on line files that supplement the information in this manual Please examine these files before you begin using your database server They contain vital information about application and performance issues On UNIX platforms the following on line files may appear in the INFORMIXDIR release directory Replace x y in the filenames with the version number of I Spy On Line File Purpose ISPYDOCx y The documentation notes file for your version of this manual describes topics that are not covered in the manual or that were modified since publication ISPYx y The release notes file describes feature differences from earlier versions of Informix products and how these differences might affect current products This fi
14. activity database This option displays the current version and serial number to standard output For example INFORMIX I Spy Version 1 00 UB11 Software Serial Number RDS N000000 A list of explain files to process For more information on the sqexplain out file see the SET EXPLAIN statement in the Informix Guide to SQL Syntax The isexplain utility processes each explain file listed on the command line and looks for SQL statements that have the tag ISPY at the beginning of a new line immediately following the text for the statement The ispy daemon appends this tag to queries if the EXPLAINTAGS parameter is set to 1 in the I Spy configuration file When it finds a tag isexplain executes the following steps 1 Theact explain id column of the ispy activity table is searched to see if an explain id matching the tag exists 2 Ifa matching explain id value is found a new row is inserted into the ispy explain table The exp text column contains the explain text from the file 3 Finally the matching act explain id column of the activity table is updated so that the first character of that column now contains a Y instead of an N isload Load Files into the Activity Database The first character of the act explain id column is either a Y or an N indicating that a corresponding row exists or does not exist in the ispy explain table The remaining characters in the column are three numbers separated by a hy
15. are not affected when ispy is stopped but new client connections fail until the daemon is restarted Also existing client connections continue to use the previous values in the configuration file and are unaware of any changes 5 8 Informix I Spy User Manual Activity Database Layout Creating the Activity Database Determine Database Location Determine act sqlstmt Column Type CHARACTER Data Type TEXT Data Type Prepare the ispy sql File Add Storage Specific Syntax Run ispy sql Grant Connect Privilege ISPY SESSION 5 ACTIVITY ISPY EXPLAIN ISPY_SQLTYPE 6 3 6 4 6 4 6 4 6 5 6 5 6 5 6 6 6 6 6 8 6 9 6 13 6 15 6 2 Informix I Spy User Manual In This Chapter This chapter discusses the tables involved in the activity database This database is used to save all the SOL statements submitted by clients communicating with an Informix database server This chapter describes the following items Creating the activity database The ispy session table The ispy activity table The ispy explain table The ispy_sqltype table Creating the Activity Database I Spy uses the activity database to record all the SQL statements submitted by client applications To create the activity database 1 Determine the location of the Informix database server where you want the activity database located Determine the column type used to store the text of SOL statements either C
16. htpasswd file To create the htpasswd file use the htpasswd utility that is part of the Apache Web server distribution Tip For more detailed information on configuring user authentication for the Apache Web server see http www apacheweek com features userauth Copy the Informix I Spy User Manual The User Manual button on the Web interface expects to find a file with a pdf extension in the ISPY DIR web userman directory To make the user manual available from the Web interface you need to copy the pdf file from your installation media into this directory The exact name of the pdf file is not important because the Web interface automatically adjusts to whatever file in the ISPY_DIR web userman directory ends with a pdf extension To view the user manual the Adobe Acrobat reader must be installed on the computer where the Web browser resides Configuring the Web Interface 4 9 Custom Reports Link on the Reports Page Custom Reports Link on the Reports Page The Reports page of the Web interface contains links to a number of predefined reports as the following window shows If the file SISPY DIR web reports custom reports html exists a link to this file is shown on the Reports page as the following window shows 4 10 Informix I Spy User Manual Custom Reports Link on the Reports Page The I Spy distribution contains the file web reports custom_reports html std that you can copy to custom repo
17. row and so on I Spy logs the start and end time of all client connections An Informix activity database is used to record all logged information This allows you to access the logged information using SOL I Spy can process explain files generated by the database server to add the explain text into the activity database This allows you to see exactly how the database server processed a specific query A rules system allows constraints to be placed on individual SQL statements The rules allow actions to be performed based on total query time number of rows returned or the actual text of the SOL statement A Web interface administers I Spy This interface allows you to perform all aspects of I Spy administration as well as run a number of predefined reports against the activity database Current Limitations I Spy does not currently support the following configurations Clients created prior to release 6 0 of the Informix development tools Clients that use shared memory or stream pipes for communications to the database server Only network connections are supported Connection multiplexing enabled in the sqlhosts file Database servers prior to 7 1x Database servers with the Universal Data Option Clients that communicate with the database server using the XA protocol Database servers performing distributed SOL Informix I Spy 1 5 I Spy Configuration I Spy Directory Structure 23
18. starting 1 5 87 8 2 Informix I Spy User Manual In This Chapter This chapter provides additional information on a number of practical issues Common Questions About I Spy The following sections answer these questions m How do I prevent users from returning more than 1000 rows in a single query m Howdolsendan email to the database administrator when a query has returned more than 1000 rows without ending the query m How do I prevent all users except informix from returning more than 1000 rows How do I assign a default PDQ priority for all users How do I check the syntax of the rules in my configuration file without shutting down and starting up I Spy How do prevent users from returning more than 1000 rows in a single query Create a rule with a rows condition in the active phase of the configuration file For example PHASE ACTIVE RULE 10 ROWS 1000 ACTION ERROR Row limit 1000 exceeded Common Questions 8 3 Common Questions About I Spy 8 4 The database server returns rows to the client in groups rather than one ata time This results in queries not being stopped at exactly 1000 rows but within one grouping of rows For example if the database server is returning 23 rows at a time the 1st group will contain rows 1 23 the 2nd rows 24 46 on to the 43rd group containing rows 967 989 and the 44th containing 990 1012 With the above rule I Spy will prevent further rows from retu
19. usr ispy Replace this directory with your installation directory name if appropriate This alias allows you to enter http hostname ispy to bring up the first page of the Web interface Warning It is assumed that your Apache Web server is configured to automatically retrieve a file named index html if one is not specified This name of this file is identified by the DirectoryIndex setting in the srm conf file If your configuration specifies a different filename you have two options 1 you can change this setting to index html or 2 you could rename the file ISPY_DIR weblindex html to the setting of DirectoryIndex Informix recommends that you use the first option because changing the name of index html could lead to problems when you install future versions of I Spy Allowing Execution and Directory Access By default most Apache server installations only allow files to be retrieved from specific directories identified by Directory sections in the access conf configuration file You need to create a Directory section for access to the files in ISPY DIR web Add the following lines to your Apache access conf configuration file lt Directory usr ispy web gt Options Indexes FollowSymLinks ExecCaGI AllowOverride None order allow deny allow from all lt Directory gt Change the path usr ispy web to reflect where you have installed I Spy Also verify that your Apache server has a handler defined to hand
20. your data or your SQL identifiers or if you want to conform to the nondefault collation rules of character data you need to specify the appropriate nondefault locale For instructions on how to specify a nondefault locale additional syntax and other considerations related to GLS locales see the Informix Guide to GLS Functionality Documentation Conventions This section describes the conventions that this manual uses These conventions make it easier to gather information from this and other volumes in the documentation set The following conventions are discussed Typographical conventions Icon conventions Command line conventions Sample code conventions Introduction 5 Typographical Conventions 6 Typographical Conventions This manual uses the following conventions to introduce new terms illustrate screen displays describe command syntax and so forth Convention Meaning KEYWORD All primary elements in a programming language statement keywords appear in uppercase letters in a serif font italics Within text new terms and emphasized words appear in italics italics Within syntax and code examples variable values that you are italics to specify appear in italics boldface Names of program entities such as classes events and tables boldface environment variables file and pathnames and interface elements such as icons menu items and buttons appear in boldface monospace Information tha
21. ESSION Column Name ses id ses uname ses hostname ses hostip ses dbname ses start ses end ses abortrule 6 8 Informix I Spy User Manual ISPY SESSION The ispy session table describes each client connection to the shadow database server Each row in the table indicates a single client session The ispy session table contains the columns that the following table shows Type SERIAL NOT NULL VARCHAR 255 VARCHAR 255 VARCHAR 255 VARCHAR 255 DATETIME YEAR TO SECOND DATETIME YEAR TO SECOND INTEGER Explanation Unique id for each session User name of client Host name of where the client application connected from The ip address of the clients host Example 192 147 100 202 The database name specified by the client when it connects to the database server otherwise null The date and time when the session was started The date and time when the session was ended The rule number from the I Spy configuration file for the rule that terminated this session otherwise 0 ISPY_ACTIVITY ISPY_ACTIVITY The ispy_activity table describes each SOL statement submitted by client applications Each row in the table represents the execution of exactly one SOL statement for a particular server session The ispy_activity table contains the columns that the following table shows Column Name Type Explanation ses_id INTEGER NOT A link to the ses_id column in the NULL ispy_ses
22. HARACTER or TEXT Add storage specific syntax for the CREATE TABLE statements Run ispy sql to create the activity tables Grant Connect privilege to the activity database for appropriate users Activity Database Layout 6 3 Determine Database Localion 6 4 Determine Database Location The activity database can be located on the same computer where I Spy is installed or it can be located on an entirely separate computer It is recom mended that you locate the activity database in a separate database server instance than the instance you are using I Spy to shadow If possible this instance should be located on another computer This should help to reduce the performance impact on the shadow database instance by providing dedicated resources for the activity database The database instance in which you create the activity database should match the server name used for the ACTIVITY INFORMIXSERVER setting in your I Spy configuration file Determine act sqlstmt Column Type The ispy activity table contains a column named act_sqlstmt that is used to store the text of SOL statements captured by I Spy In the ISPY DIR sql ispy sql std file this column is commented out because you have to determine what column type to use either CHARACTER or TEXT The length of SOL statements submitted by clients is entirely arbitrary so it is important that you choose a column type and length that is sufficient for your purposes but also minimi
23. ITY CAPTURE is a global parameter and is in effect for all clients that connect to I Spy You can use the NOLOG action of a rule to disable the logging of a specific SOL statement if the ACTIVITY CAPTURE value is set to either 1 or 2 Important If the ispy daemon encounters any type of database error while inserting rows into the activity database it will close that connection and begin logging SOL statements to the file system The daemon writes a message similar to the following example into the log file indicating that this action has taken place SQLCODE 9999 insert ispy session line 345 filesystem capture enabled This behavior is a safety net to ensure that SOL activity from clients is always logged Informix I Spy User Manual ACTIVITY DBNAME ACTIVITY DBNAME Syntax ACTIVITY DBNAME dbname Example ACTIVITY DBNAME Required Yes ACTIVITY DBNAME specifies the name of the database used to record client activity Chapter 6 contains more information on creating and configuring the activity database While you can use any database name you like to record client activity for I Spy it is recommended that you use the name ispy to avoid confusion ACTIVITY DIR Syntax ACTIVITY DIR directory path Example ACTIVITY DIR usr ispy activity Required Yes ACTIVITY DIR specifies a full path to a directory used for files when ACTIVITY CAPTURE is set to 2 Each client connection to I Spy creates two file
24. IXSERVER Syntax ACTIVITY INFORMIXSERVER dbserver name Example ACTIVITY INFORMIXSERVER menlo net Required Only if ACTIVITY CAPTURE 1 ACTIVITY INFORMIXSERVER specifies the Informix database server that contains the database that ACTIVITY DBNAME specifies The I Spy daemon uses that database at the specified database server to save all client activity The server name used for ACTIVITY INFORMIXSERVER must be present in the sqlhosts file that I Spy uses 3 16 Informix I Spy User Manual DEBUGFILE DEBUGFILE Syntax DEBUGFILE file path Example DEBUGFILE usr ispy log debug log Required No DEBUGEILE specifies the full path to a file that is used for writing debug infor mation from the ispy daemon process The debug file is intended for use by Informix Technical Support and the format and contents of this file are likely to change DEBUGLEVEL Syntax DEBUGLEVEL integer Example DEBUGLEVEL 5 Required No DEBUGLEVEL is an integer value that specifies the amount of debugging information to place in the debug file It defaults to a value of 0 which indicates no debugging and has a maximum value of 300 Values above 5 tend to produce a considerable amount of debug information DEBUGLEVEL greater than 0 should only be used in cases where you want to verify that I Spy is working or if instructed by Informix Technical Support Debugging introduces a considerable amount of overhead for a client connection and should n
25. LTRIM allowing you flexibility in statements For more information on regular expressions see Regular Expression Syntax on page 3 6 The value that you supply must be enclosed in double quotes to delineate the beginning and end of the expression You can use a V sequence to include a double quote character as part of the expression SOLTRIM differs from the SOL condition in that it trims the client supplied SQL statement into a simpler form before an expression match is attempted The trim process consists of the following three steps 1 characters in the statement are converted to lowercase 2 All leading and trailing white space is removed White space is defined as blank tab and newline characters 3 All remaining spans of more than one white space character are reduced to a single blank character The process should make it easier to write a regular expression Consider the following SQL statement submitted by the client SELECT from customer where last name jones It would be difficult to create a regular expression that looks for any SELECT statements that include the customer table because of the embedded newline characters and the possibility that all SOL keywords could be in uppercase or lowercase SOLTRIM makes the job simpler because it transforms the submitted SQL into this form before the expression match is attempted select from customer where last name jones Now you could use SQLTRI
26. M select from customer where to match this type of SELECT statement The SOLTRIM condition does not change in any way the actual text of the SOL statement that is sent to the database server The transformation described earlier is only an internal process that the I Spy daemon uses Configuration File 3 29 USER 3 30 Tip SOLTRIM and SQL conditions are both supplied because there is more overhead in using SOLTRIM to transform each SQL statement before expression matching is performed If you can create an expression that satisfies your needs using only the SQL condition that is preferable Use SQLTRIM only if you are having difficulty doing what you need using only SQL USER Syntax USER username Example USER johnr Required No USER is a rule condition that specifies a user name to match against the client s user name If the names match the condition is considered true USER is associated with whatever rule immediately preceded it in the configu ration file Regular expressions are permitted for the user name allowing you flexibility in matching clients For more information on regular expressions see Regular Expression Syntax on page 3 6 Advanced Configuration Options This section discusses a number of advanced configuration options for I Spy It covers the following topics m Using I Spy to monitor more than one Informix database server m Configuring I Spy with no changes to the client environm
27. Menu Option The Reports Menu Option The View Log Menu Option The User Manual Menu Option 4 10 4 12 4 12 4 13 6 3 6 3 6 4 6 4 6 5 6 6 6 6 6 8 6 9 6 13 6 15 Chapter 8 Common Questions In This Chapter Common Questions About I ae 3 How do I prevent users from returning more fhan 1000r rows ina single query dw That Au es How do I send an email to the when query has returned more than 1000 rows without ending the query mE rA gt UA How do I prevent all users dd informix from en more than 1000 rows How do I assign a default PDO priority for all ado How do I check the syntax of the rules in my configuration file without shutting down and starting I Spy Index 8 3 8 3 Table of Contents vii Introduction About This Manual Types of Users Software Dependencies Assumptions About Your Locale Documentation Conventions Typographical Conventions Icon Conventions Command Line an How to Read Command Line Sample Code Conventions Additional Documentation On Line Manuals Printed Manuals Error Message Documentation Documentation Notes Release Notes Machine Notes Compliance with Industry Standards Informix Welcomes Your Comments NNO oF 2 Informix I Spy User Manual In This Introduction This introduction provides an overview of the information in this manual and describes the conventions it use
28. OND DATETIME YEAR TO SECOND DATETIME YEAR TO SECOND INTERVAL HOUR 4 TO FRACTION 2 INTEGER INTEGER INTEGER SMALLINT FLOAT INTEGER Explanation The date and time when this statement began executing The date and time when the cursor for this statement was opened The date and time when the first row of the cursor for this statement was fetched from the database server The date and time when the last row of the cursor for this statement was fetched from the database server The date and time when the cursor for this statement was closed The date and time when this statement finished executing The amount of time the database server spent processing this statement The estimated number of rows this statement will return The estimated cost to execute this statement The total number of rows returned by this statement For an insert cursor this indicates the total number of rows sent to the database server The length of the result set in bytes for this statement The total number of TEXT bytes returned in all rows of this statement The number of fetch requests the client sent to the database server 2 of 3 ISPY_ACTIVITY Column Name Type Explanation act_sqlerr INTEGER The error returned by the database server for this statement This value will be 0 if there was no error act isamerr INTEGER The isam error code returned by the database server for this statement This value will be
29. aemon process Once the daemon process is running clients will connect to this process by changing their INFORMIXSERVER environment variable setting This chapter describes the following tasks Familiarization with the I Spy directory structure Setting the necessary environment variables for I Spy Preparing the ISCONFIG configuration file Preparing the sqlhosts file Starting the ispy daemon Configuring clients to use I Spy What To Do Next I Spy Directory Structure After you follow the installation instructions that accompanied your I Spy product you should have a new directory tree containing all of the files required for I Spy to operate You should have an owner and group named informix on your computer and all of the files should be owned by informix and have a group of informix If this is not the case review the installation instructions to confirm that you followed them correctly I Spy Configuration 2 3 activity 2 4 Warning If the computer where you installed I Spy already has existing Informix products installed on it make sure that you have installed I Spy into a separate directory If you install I Spy into the same directory as existing Informix products I Spy might fail to operate and you might introduce problems with your existing products For example if you have an Informix database server installed in lusrlinformix you might want to install I Spy in lusrlispy or even lusrlinformixlispy but not d
30. al in certain products Punctuation and mathematical notations are literal symbols that you must enter exactly as shown Single quotes are literal symbols that you must enter as shown Privileges p 5 17 A reference in a box represents a subdiagram Imagine that the subdiagram is spliced into the main diagram at this point When a page number is not specified the subdiagram appears on the same page ALL A shaded option is the default action Syntax within a pair of arrows indicates a subdiagram Informix Spy User Manual The vertical line terminates the command 1 of 2 setenv Element 4 OFF E ON Command Line Conventions Description A branch below the main path indicates an optional path Any term on the main path is required unless a branch can circumvent it E variable A loop indicates a path that you can repeat Punctuation along the top of the loop indicates the separator symbol for list items A gate 3 on a path indicates that you can only use that path the indicated number of times even if it is part of a larger loop You can specify size no more than three times within this statement segment 2 of 2 How to Read a Command Line Diagram Figure 1 shows a command line diagram that uses some of the elements that are listed in the previous table INFORMIXC DAC compiler T pathname Figure 1 Example of a Command Line Diagram
31. an only specify the USER and HOST conditions ITIME OTIME ROWS SQL and SQLTRIM are not allowed QTIME Syntax OTIME seconds Example OTIME 300 Required No OTIME is a rule condition that specifies the amount of query processing time in seconds for a single SOL statement Another way to say this is the amount of time the database server spends processing all client requests for the SOL statement If the amount of time is greater than the value supplied the condition is considered true QTIME is associated with whatever rule immediately preceded it in the configuration file Configuration File 3 23 QTIME You can only combine the OTIME condition with the USER HOST SQL or SOLTRIM conditions within a rule Important It is important to understand how I Spy calculates the query time for an SQL statement because it might not correspond exactly to your assumption Each client request to the database for an SQL statement causes query time to accumulate until the database server responds to the request Time between client requests but before the query is completed is not considered part of query time Consider the following example A client declares a cursor for an SQL statement that will return 1000 rows from the database server The client then opens the cursor and fetches the first 50 rows The time required for the database server to open the cursor and return the first 50 rows is accumulated in query time for this SOL state
32. d and so forth Each diagram displays the sequences of required and optional elements that are valid in a command A diagram begins at the upper left corner with a command It ends at the upper right corner with a vertical line Between these points you can trace any path that does not stop or back up Each path describes a valid form of the command You must supply a value for words that are in italics Introduction 7 Command Line Conventions 8 You might encounter one or more of the following elements on a command line path Element command Description This required element is usually the product name or other short word that invokes the product or calls the compiler or preprocessor script for a compiled Informix product It might appear alone or precede one or more options You must spell a command exactly as shown and use lowercase letters variable A word in italics represents a value that you must supply such as a database file or program name A table following the diagram explains the value flag A flag is usually an abbreviation for a function menu or option name or for a compiler or preprocessor argument You must enter a flag exactly as shown including the preceding hyphen ext A filename extension such as sql or cob might follow a variable that represents a filename Type this extension exactly as shown immediately after the name of the file The extension might be option
33. daemon from running Figure 7 3 Shutdown Window 7 6 Click the Shutdown button to execute the command ispy k to kill the ispy daemon process After the daemon process shuts down the Status window appears If you shut down I Spy client applications will no longer be able to connect to the ISPYSERVER value specified in the configuration file However clients that are currently connected will continue to operate without problems Informix Spy User Manual The Config File Menu Option The Config File Menu Option The Config File menu option takes you to the window in Figure 7 4 which allows you to edit the values in the I Spy configuration file Figure 7 4 Edit Configuration File Window Click the Submit Changes button to write the contents of the text window to the configuration file and save any changes you have made The current configuration file is copied to a file of the same name but with a old extension before the new changes are written Using The Web Interface 7 7 The Clients Menu Option The Clients Menu Option The Clients menu option takes you to the window in Figure 7 5 which displays a list of all clients currently connected to I Spy Figure 7 5 Clients Window 7 8 Informix I Spy User Manual The Clients Menu Option Each client name is a hot link that you can select to provide more detailed information on that particular client Figure 7 6 shows a Client Detail window tha
34. e concept being discussed For detailed directions on using SQL statements for a particular application development tool or SQL API see the manual for your product Additional Documentation For additional information you might want to refer to the following types of documentation On line manuals Printed manuals Error message documentation Documentation notes release notes and machine notes Related reading Informix I Spy User Manual UNIX WIN NT On Line Manuals On Line Manuals An Answers OnLine CD that contains Informix manuals in electronic format is provided with your Informix products You can install the documentation or access it directly from the CD For information about how to install read and print on line manuals see the installation insert that accompanies Answers OnLine Printed Manuals To order printed manuals call 1 800 331 1763 or send email to moreinfo informix com Please provide the following information when you place your order m The documentation that you need m The quantity that you need m Your name address and telephone number Error Message Documentation Informix software products provide ASCII files that contain all of the Informix error messages and their corrective actions Tip The error message utilities described in this section are not shipped as part of I Spy They are part of your Informix database server product To read error messages and corrective
35. ectory to reference the second isconfig configuration file Finally update your Apache Web server configuration to define a new alias that references the new directory structure Configuration File 3 31 Configuring I Spy with No Changes in the Client Environment 3 32 Configuring I Spy with No Changes in the Client Environment It is possible to configure I Spy so that no changes are required in the client environment for those clients to access I Spy This feature might be advanta geous in an existing environment where updating client environments is difficult Another reason this configuration option might be useful is that it allows you to determine whether clients connect directly to the Informix database server or to I Spy You can make this choice from the computer where the database server resides instead of having to update the environment of each client For this process to work I Spy must be installed on the same computer as the shadow database server because the third column of the client sqlhosts file is the name of the host where the database server resides If I Spy was installed on a different computer from the one that contains the database server you would always be required to update the client configuration to switch between I Spy and a direct connection to the database server Consider the case where you have I Spy installed on a computer that runs a database server currently named sales net Your various conf
36. ement The hostname of the client Inactivity time The amount of time that has elapsed without the client submitting a request to the database server Query time The amount of execution time elapsed for a single SOL statement The number of rows returned for an SQL statement The text of an SOL statement The length of an SOL statement The text of an SOL statement translated for easier expression matching The username of the client SHADOWSERVER SHADOWSERVER Syntax SHADOWSERVER INFORMIXSERVER Example SHADOWSERVER cougar net Required Yes This parameter specifies the Informix server name of the database server that clients connect to through I Spy If you already have users connecting to an existing database server you would set SHADOWSERVER to the current value of INFORMIXSERVER For example if you had the following entry in your sqlhosts file cougar_net ontlitcp cougar portl you would set SHADOWSERVER to the value cougar_net The server name you use must be a network connection I Spy does not support any other connection type SQL Syntax SOL expression Example SOL systables Required No SQL is a rule condition that specifies an SOL statement to compare with If the value supplied matches the SOL statement submitted by the client the condition is considered true SOL is associated with whatever rule immedi ately preceded it in the configuration file You cannot combine the SQL conditio
37. ent m Using the same activity database with multiple I Spy configurations Informix I Spy User Manual Monitoring Multiple Database Servers Monitoring Multiple Database Servers Each isconfig configuration file is associated with the monitoring of a single Informix database server To monitor more than one database server you must create a separate configuration file for each one Use the ISPY CONFIGFILE environment variable to specify which configuration file you are using The following parameters within each configuration file should be unique x SHADOWSERVER ISPYSERVER LOGFILE ACTIVITY DIR You must start an I Spy daemon for each configuration file Each configuration will have a separate server name log file and activity directory This setup works in exactly the same manner as multiple onconfig configuration files for multiple instances of an Informix database server on the same computer Limitations Be aware of the following limitation when you configure multiple isconfig configuration files on the same computer The default Web interface in ISPY_DIR web only supports a single configuration The Web interface is restricted to one configuration because a single web cfg file exists in the ISPY DIR web directory to define which isconfig configu ration file to reference One workaround to this issue would be to copy the entire Web directory to a new directory Then update the web cfg file in the new dir
38. er Manual 3 20 3 21 3 22 3 22 3 23 3 23 3 25 3 25 3 27 3 27 3 28 3 28 3 30 3 30 3 31 3 31 3 32 3 33 In This Chapter This chapter discusses the I Spy configuration file in detail The configuration file defines all aspects of how I Spy operates This chapter covers the following topics General configuration file syntax An overview of the rules system Regular expression syntax Detailed syntax and descriptions for all configuration file parameters General Configuration File Syntax The I Spy configuration file consists of multiple lines Each line contains a specific parameter Blank lines are ignored and anything after the character on a lineis ignored as a comment A sample configuration file might resemble the following example SHADOWSERVER godzilla net ISPYSERVER godzilla spy EXPLAINTAGS 0 iF 0 0ff 1 0n LOGNAME usr ispy log ispy log ACTIVITY_CAPTURE 2 if 0 None 1 Database 2 File ACTIVITY_DBNAME ispy 1 of 2 Configuration File 3 3 Rules Overview ACITIVITY INFORMIXSERVER ispy net ACTIVITY DIR usr ispy activity ACTIVITY ID 1 u I ASE CONNECT Tc I ASE DBOPEN uv I ASE ACTIVE RULE 1 USER informix ITIME 1800 ACTION endsess PHASE DISCONNECT 2 of 2 The order of the parameters in the configuration file is not significant although it is common to place the rules section at the end of the file after all the general parameters All parame
39. es the string customer1 but does not match the SOL statement select from customer 2 of 2 For more information on regular expressions see your system man pages on regexp ACTION Syntax ACTION action keyword Example ACTION ENDSESS Session Terminated Required No ACTION specifies a task for I Spy to perform if all the conditions for a rule are true Multiple actions might be defined for a rule and they are executed in the order in which they appear in the configuration file ACTION is associated with whatever rule immediately precedes it in the configuration file 3 8 Informix I Spy User Manual ACTION ENDSESS The following action keywords are available m ENDSESS m ERROR m EXECSOL m EXECUTE m EXPLAINTAGS m ACTION ENDSESS Syntax ACTION ENDSESS client message Example ACTION ENDSESS Session terminated ENDSESS ends a client session and returns an error message to the client The text on the line after the ENDSESS keyword is returned to the client appli cation as an error message with a 746 error number The maximum length of the error message returned to the client is 57 characters Longer messages are truncated The ispy process handling the client session closes the database connection and exits after sending the error message to the client application To continue working the client must create a new database connection ACTION ERROR Syntax ACTION ERROR client message Exam
40. executed 3 Thestatementis terminated with the error 746 I Spy 2 Maximum 1000 rows exceeded Regular Expression Syntax Regular expressions provide a mechanism to find a specific string within another string The four parameters HOST USER SQL and SOLTRIM in the isconfig configuration file allow you to use regular expressions for matching The ability to use regular expressions enables you to create more general rules than would be otherwise possible A regular expression RE is considered true if the characters within the expression find a match in the corresponding comparison element Consider the case where the client submits the SOL statement select from customer Informix Spy User Manual Regular Expression Syntax Tip All the examples presented here will be contained within double quotes to signify the beginning and the end of the string but the double quotes are not considered part of the string The RE select is true because the word select appears in the SOL statement while the RE SELECT is not true because the uppercase word SELECT does not appear The following table describes a number of characters that have special meanings when they are used in regular expressions Character RE Description A RE of a single character followed by an asterisk matches zero or more occurrences of the RE For example in the SOL statement insert into tab values 1000 the RE 0 matches the s
41. g stopped at exactly a specific number of rows but within one grouping of rows For example if the database server is returning 23 rows ata time the 1st group will contain rows 1 23 the2nd rows 24 46 on to the 43rd group containing rows 967 989 and the 44th containing 990 1012 With a rows condition of 1000 I Spy will prevent further rows from returning but the client will see all rows up to and including 1012 RULE Syntax RULE number Example RULE 39 Required No RULE specifies the start of a new rule that is applied to each client connection Rules can have any number of conditions and actions associated with them that appear on the lines that follow the RULE parameter Configuration File 3 25 RULE Condition Parameter Each rule in the configuration file must be assigned a unique number This number is used in the ERROR action and the sess abortrule or act abortrule columns of the activity database This provides you with the ability to reference the exact rule that caused a specific action to be taken If all actions defined for a rule are true then all defined actions for that rule are executed The following table lists all conditions available for a rule Description ECOST EROWS HOST ITIME QTIME ROWS SOL SOLLENGTH SOLTRIM USER 3 26 Informix I Spy User Manual The estimated cost for the execution of the SOL statement The estimated number of rows in the result set for the SOL stat
42. he ispy process would be unable to process it If such a statement is submitted it will be ignored and not executed by the database server ACTION EXECUTE Syntax ACTION program path EXECUTE Example ACTION usr ispy bin ispymail sh EXECUTE EXECUTE runs a program on the computer where I Spy resides Supply the full path to the program to eliminate problems with relative references 3 10 Informix I Spy User Manual ACTION EXECUTE I Spy forks itself to execute the program but does not wait for the program to complete The following table lists the variables present in the environment for the program executed Variable Description Example ISPY CLIENTHOST The host name of the client computer ISPY CLIENTHOST cougar ISPY CLIENTIP The IP address of the client computer ISPY CLIENTIP 192 147 100 15 ISPY DBNAME The name of the currently open ISPY DBNAME stores7 database ISPY ECOST The estimated cost of the current SOL ISPY_ECOST 3245 Statement ISPY EROWS The estimated number of rows ISPY EROWS 45 returned from the current SOL statement ISPY_EXECUTE The name of the program supplied ISPY_EXECUTE usr ispy local send for the EXECUTE keyword in the mail sh configuration file ISPY_NFETCH The number of fetch requests this ISPY_NFETCH 3 client has sent to the database server for the current SQL statement ISPY_PREPARE The date and time the current SQL ISPY_PREPARE 1998 12 24 12 56 57 statement was prepared ISPY_ROWS The number of
43. hosts is used 3 If neither of the previous two variables are present in the environment ISPY DIR etc sqlhosts is used If you installed I Spy on a computer that had other Informix products already installed you already have at least INFORMIXDIR set in your environment and should not need to make any change On the other hand if you have installed I Spy on a computer without other Informix products you might want to set INFORMIXSOLHOSTS to the full path of the sqlhosts file you want to use or simply use the default of ISPY_DIR etc sqlhosts Shared Library Search Path You need to set the appropriate environment variable for shared library searching to include ISPY DIR lib If you do not have this setting in your environment you will probably see an error similar to the following one when you run ispy 1ld so 1 ispy fatal libifsql so can t open file errno 2 Killed Informix Spy User Manual PATH Check with your system administrator to determine the correct environment variable to use for your platform m SOLARIS systems use LD_LIBRARY_PATH to specify directories to search for shared libraries For example if I Spy is installed in usr ispy LD LIBRARY PATH usr ispy lib LD LIBRARY PATH export LD LIBRARY PATH m HP UX systems use SHLIB PATH to specify directories to search for shared libraries For example if I Spy is installed in usr ispy SHLIB PATH usr ispy lib SHLIB PATH export SHLIB PATH
44. iguration files resemble Your isconfig configuration file resembles SHADOWSERVER sales net ISPYSERVER sales spy Your sqlhosts configuration file resembles sales net onsoctcp serverl porti sales spy onsoctcp serverl port2 Your onconfig configuration file resembles INFORMIXSERVER sales_net You should configure clients with the single sqlhosts entry of sales_net and set their INFORMIXSERVER environment variable to sales_net In this way based on the preceding configuration files they will connect directly to the Informix database server and not use I Spy at all Informix I Spy User Manual Multiple I Spy Configurations One Activity Database To route clients through I Spy without making any changes in the client configuration 1 Make sure all clients have disconnected from the database and then shut down both the database server and I Spy 2 Swap the values of SHADOWSERVER and ISPYSERVER in the isconfig configuration file The isconfig file should now resemble SHADOWSERVER sales spy ISPYSERVER sales net 3 Change the value of DBSERVERNAME in onconfig to reference sales spy instead of sales net DBSERVERNAME sales spy Tip You might need to change DBSERVERALIAS instead of DBSERVERNAME if sales net was originally defined using DBSERVERALIAS 4 Startup the database server and I Spy What you have done is swap the INFORMIXSERVER and ISPYSERVER names so that the database server is now listening on the name sa
45. irectly in the usrlinformix directory The I Spy installation script creates the directory l ispytmp Various files for I Spy are located in this directory Never edit rename or remove any files in this directory and never remove or rename the directory itself If you do I Spy will fail to operate The I Spy product installation includes the following directories activity The activity directory is an empty directory intended to hold activity files if I Spy is configured to capture activity to the file system The ACTIVITY CAPTURE parameter in the configuration file is used to specify where I Spy places activity information bin The bin directory contains all the I Spy utilities ispy isload and isexplain The ispy executable is the I 5py daemon process that intercepts all client activity and saves it to the activity database The isload executable is used to load files from the file system into the activity database The isexplain executable is used to process sqexplain out files created by Informix database servers and to insert the explain text for SOL statements into the activity database etc The etc directory contains a sample I Spy configuration file named isconfig std and a sample sqlhosts file named sqlhosts std The directory also contains some other files used only during product installation Unless told otherwise the ispy executable looks for a configuration file named isconfig in this directory A later section in
46. is selected thereby controlling access to the different options The Apache Web server allows you to configure basic authentication for a specific directory Each of the buttons on the I Spy Web interface is associated with a specific CGI script in a directory under ISPY DIR web as the following table shows Button Directory Status ISPY DIR web status Startup ISPY DIR web startup Shutdown ISPY DIR web shutdown Config File ISPY DIR web edit editco nfig Clients ISPY DIR web clients Reports ISPY DIR web reports View Log ISPY DIR web viewlog User Manual SISPY DIR web userman 4 8 Informix I Spy User Manual Copy the Informix I Spy User Manual To require a user name and password for a specific button add a Directory section for the corresponding directory in the Apache access conf configu ration file For example to require a password for startup add the following lines lt Directory home2 ispy current web startup gt AuthType Basic AuthName ispy require user markj AuthUserFile usr ispy web htpasswd AuthGroupFile usr ispy web htgroup You also must add a htpasswd and htgroup file to your ISPY_DIR web directory For the preceding example the htgroup file contains groupl markj The htpasswd file contains markj XrtIiBSBpwZQA For this configuration example the Web browser will prompt for a user name and password and only accept markj as the user name and the password contained in the
47. l and can be configured depending on your environment Configuring basic Web security if applicable Copying the Informix I Spy User Manual into the ISPY DIR web userman directory Custom reports link on the report page Preparing the web cfg Web Configuration File All files necessary for the Web interface are located in the ISPY DIR web directory You must create a new configuration file within this directory named web cfg The web cfg file will contain any environment variable settings needed for the interface To prepare the web cfg file using a standard text editor Copy the file ISPY DIR web web cfg std to ISPY DIR web web cfg Make sure the file has an owner and group informix and a permission mode of 644 Do not modify the web cfg std file All Common Gateway Interface CGI scripts in the Web interface look for a file named web cfg in the Web directory The file cannot have a different name Edit the web cfg file placing all necessary I Spy environment variables on a separate line Each line has the form VARNAME value as the following example shows ISPY DIR usr ispy The following environment variables must be present in this file ISPY DIR ISPY CONFIGFILE Shared library search path Configuring Web Server Directory Access Optionally you might need to include INFORMIXSOLHOSTS if you are not using the default sqlhosts file ISPY DIR etc sqlhosts The shared library search path represents the app
48. le CGI scripts Make sure that the following line is present in the srm conf configu ration file AddHandler cgi script cgi Informix Spy User Manual Granting Access to the Activity Database Granting Access to the Activity Database The report section of the Web interface requires access to the activity database For more information on granting the Web server this access see Grant Connect Privilege on page 6 6 Testing the Web Interface Atthis point you should be able to use the Web interface Open the following URL in your Web browser where hostname is the name of the computer where you have I Spy installed http hostname ispy You should see the following window If this page does not display review the preceding configuration topics to make sure everything is configured correctly Configuring the Web Interface 4 7 Configuration of Basic Web Security Configuration of Basic Web Security The Web interface contains the following buttons on the left side of the display Status Startup Shutdown Config File Clients Reports View Log and User Manual You might want to restrict access to one or more of these options For example you might want to allow some users access to the Reports section of the interface but not the ability to start up or shut down the I Spy daemon process You can use basic Web authentication to force a Web browser to prompt for a user name and password when any of the buttons
49. le also contains information about any known problems and their workarounds Compliance with Industry Standards The American National Standards Institute ANSI has established a set of industry standards for SQL Informix SQL based products are fully compliant with SQL 92 Entry Level published as ANSI X3 135 1992 which is identical to ISO 9075 1992 In addition many features of Informix database servers comply with the SQL 92 Intermediate and Full Level and X Open SQL CAE common applications environment standards 12 Informix I Spy User Manual Informix Welcomes Your Comments Informix Welcomes Your Comments Let us know what you like or dislike about our manuals To help us with future versions of our manuals we want to know about any corrections or clarifications that you would find useful Include the following information m The name and version of the manual that you are using m Any comments that you have about the manual m Yourname address and phone number Write to us at the following address Informix Software Inc SCT Technical Publications Department 4100 Bohannon Drive Menlo Park CA 94025 If you prefer to send electronic mail our address is doc informix com The doc alias is reserved exclusively for reporting errors and omissions in our documentation We appreciate your suggestions Introduction 13 Informix I Spy Whatlsl 5py9 4 xoxo Mb mele Woo uh oux IM d 1 3 Spy Features
50. led I Spy in usr ispy you would set ISPY DIR as follows ISPY DIR usr ispy export ISPY DIR ISPY CONFIGFILE ISPY CONFIGFILE is an optional environment variable that contains the full path to the configuration file you want I Spy to use I Spy looks for the configuration file it will use in the following order 1 Ifthe command line option c filename is present I Spy uses filename as the configuration file 2 IftheISPY CONFIGFILE environment variable is set I Spy uses it for the configuration file 3 Ifneither the c filename command line option is present nor the ISPY CONFIGFILE environment variable is set I Spy looks for the file ISPY_DIR etc isconfig If your configuration file is named usr ispy etc isconfig serverl you would set ISPY CONFIGFILE as follows ISPY CONFIGFILE usr ispy etc isconfig serverl export ISPY CONFIGFILE I Spy Configuration 2 7 INFORMIXDIR and INFORMIXSQLHOSTS 2 8 INFORMIXDIR and INFORMIXSQLHOSTS The INFORMIXDIR and INFORMIXSOLHOSTS environment variables are only used by I Spy to locate an sqlhosts file Both of these variables are optional If neither of them is present in the environment I Spy will use ISPY_DIR etc sqlhosts as the sqlhosts file The order of precedence then when looking for an sqlhosts file is as follows 1 If INFORMIXSQLHOSTS is present in the environment this path is used 2 If INFORMIXDIR is present in the environment INFORMIXDIR etc sql
51. les spy and I Spy is listening on the name sales net Clients will now connect through I Spy since they reference the name sales net You can configure clients to connect directly to the database server by changing the configuration back to the original set of files Multiple I Spy Configurations One Activity Database Multiple I Spy instances can all log into a single activity database as long as the ACTIVITY ID value in the configuration file of each instance is unique The ACTIVITY ID value is used to populate the act explain id column of the activity table This feature is useful if you are running Informix Dynamic Server with Advanced Decision Support and Extended Parallel Options and have multiple connection coservers configured Each connection coserver would be running an instance of I Spy but they would all log to a single activity database Configuration File 3 33 Configuring the Web Interface Preparing the web cfg Web Configuration File Configuring Web Server Access Adding a URL Alias Allowing CGI Execution and Directory on Granting Access to the Activity Database Testing the Web Interface Configuration of Basic Web Security Copy the Informix I Spy User Manual Custom Reports Link on the Reports Page Configuration for Netscape FastTrack Server Create New Document Directory Activate the CGI File Type 4 4 4 5 4 5 4 6 4 7 4 7 4 8 4 9 4 10 4 12 4 12 4 13 4 2 I
52. m AIX systems use LIBPATH to specify directories to search for shared libraries For example if I Spy is installed in usr ispy LIBPATH usr ispy lib LIBPATH export LIBPATH It is important to place ISPY DIR lib in the front of the shared library path list when you use I Spy so that the appropriate shared libraries are found If you have other Informix products installed on the same computer as I Spy you might already have the shared library path setting configured for these products In this case it is still necessary to add the I Spy lib directory to the front of the path list PATH The final environment variable setting is to include ISPY DIR bin in your existing PATH setting This allows you to execute the I Spy programs while they are located in any directory on your computer For example PATH ISPY_DIR bin PATH export PATH I Spy Configuration 2 9 Prepare the ISCONFIG Configuration File 2 10 Prepare the ISCONFIG Configuration File The configuration file for I Spy controls all aspects of how the product operates where SQL activity is stored what Informix database server names to use rules that control client activity and so on The ISPY_DIR etc isconfig std file resembles the following table shadowserver is ex logname ac ac ac ac ac tivi tivi tivi tivi tivi X y pyserver plaintags y_capture y_dbname y_informixserver dir id pha
53. ment At this point the client performs some other type of processing that makes no further requests of rows for this SOL statement from the database server This time is not accumulated in query time although the SOL statement is still executing because the cursor is open Assume for this example that a rule is defined that would match this SOL statement and QTIME is set to 300 seconds or 5 minutes If the client spends more than 5 minutes performing this other processing the QTIME condition would not become true and the actions would not be triggered Later the client fetches the remaining rows of the result set and closes the cursor These operations would be accumulated in query time for the statement Only if the accumulated time for all fetch requests to the database exceeded 5 minutes would the QTIME condition become true 3 24 Informix I Spy User Manual ROWS ROWS Syntax ROWS integer Example ROWS 1000 Required No ROWS is a rule condition that specifies the number of rows in the result set for an SQL statement If the number of returned rows is greater than the value supplied the condition is considered true ROWS is associated with whatever rule immediately preceded it in the configuration file You can only combine the ROWS condition with the USER HOST SQL or SOLTRIM conditions within a rule The database server returns rows to the client in groups rather than one at a time This results in queries not bein
54. n with a r command line option This command returns the process id number of the daemon if it is running or return no output if it is not running as the following example shows ispy r 358 Informix I Spy User Manual Configuring Clients for l Soy Configuring Clients for I Spy To configure Informix client applications to use I Spy follow these steps 1 Change the INFORMIXSERVER environment variable to the value of ISPYSERVER in the configuration file 2 Ifthe client is on a different computer than the I Spy installation add an entry in the client sqlhosts file for the ISPYSERVER name Important It is possible to configure I Spy so that no changes are required for client applications For more information on how this is accomplished see Advanced Configuration Options on page 3 30 Configuring INFORMIX Connect Use the Setnet32 utility to configure INFORMIX Connect clients The Server Information tab specifies the location of the database server as Figure 2 1 shows Figure 2 1 Specifying location of database server in Setnet32 utility Spy Configuration 2 13 What to Do Next 2 14 Informix I Spy User Manual In this example the ISPYSERVER value is sales spy and the sqlhosts entry for this server name is as follows sales spy onsoctcp cougar 6003 What to Do Next Depending on your environment you might want to explore the following topics in more depth To configure the web in
55. n with either the or ROWS condition within a single rule Configuration File 3 27 SQLLENGTH Regular expressions are permitted for SOL allowing you flexibility in matching statements For more information on regular expressions see Regular Expression Syntax on page 3 6 The value supplied must be contained with double quotes to delineate the begin and end of the expression A sequence can be used to include a double quote character as part of the expression SQLLENGTH Syntax SOLLENGTH integer Example SOLLENGTH 256 Required No SOLLENGTH is a rule condition that specifies the length of an SOL statement submitted for execution by a client application If the length of the statement is equal to or greater than the value supplied the condition is considered true SOLLENGTH is associated with whatever rule immediately preceded it in the configuration file SQLTRIM Syntax SOLTRIM expression Example SOLTRIM systables Required No SOLTRIM is a rule condition that specifies an SOL statement to compare with If the value supplied matches the SQL statement submitted by the client the condition is considered true SOLTRIM is associated with whatever rule immediately preceded it in the configuration file 3 28 Informix I Spy User Manual SQLTRIM You cannot combine the SOLTRIM condition with either the or ROWS condition within a single rule Regular expressions are permitted for SO
56. nents Universal Web Connect ViewPoint Visionary Web Integration Suite The Informix logo is registered with the United States Patent and Trademark Office The DataBlade logo is registered with the United States Patent and Trademark Office Documentation Team Mark Jeske Mary Kraemer Scott Nash Barbara Nomiyama Patrice O Neill Matt Veal Keldyn West Eileen Wollam GOVERNMENT LICENSE RIGHTS Software and documentation acquired by or for the US Government are provided with rights as follows 1 if for civilian agency use with rights as restricted by vendor s standard license as prescribed in FAR 12 212 2 if for Dept of Defense use with rights as restricted by vendor s standard license unless superseded by a negotiated vendor license as prescribed in DFARS 227 7202 Any whole or partial reproduction of software or documentation marked with this legend must reproduce this legend Informix Spy User Manual Chapter 1 Table of Contents Introduction In This Introduction About This Manual Types of Users Software Dependencies Assumptions About Your Locale Documentation Conventions Typographical Conventions Icon Conventions an amp Command Line Conventions Sample Code Conventions Additional Documentation On Line Manuals Printed Manuals Error Message Documentation Documentation Notes Release Notes Machine Nors Compliance with Industry Standards Informix Welcomes Y
57. nformix I Spy User Manual In This Chapter This chapter describes how to configure the Web interface for I Spy The Web interface is an optional component of I Spy You can perform all adminis trative activities using the command line utilities without the Web interface The advantage to using the Web interface is that it eliminates the need for you to remember all the details of your I Spy installation such as configuration filenames directory paths and so on This chapter discusses the configuration of the Apache Web server Version 1 2 5 You can directly apply the concepts presented to almost any other Web server although the specific syntax will be different than that presented here Contact your Web administrator for more information on your particular Web server For more information on the Apache Web server see http www apache org Important It is required that a Web server be installed on the same computer where I Spy is located It is not possible to use the Web interface if a Web server is not available on the same computer This chapter describes the following tasks Preparing the Web configuration file web cfg Configuring Web server directory access for the ISPY_DIR web directory m Granting access to the activity database m Testing the Web interface Configuring the Web Interface 4 3 Preparing the web cfg Web Configuration File 4 4 1 Informix Spy User Manual The following tasks are optiona
58. nt The I Spy daemon process that listens for client connections The Informix database server that the client wants to connect to The Informix database server that contains the I Spy activity database used to record all SOL statements submitted by clients Informix I Spy 1 3 I Spy Features Each of these components can be located on a different computer or they can be combined on the same computer Figure 1 1 illustrates these components Figure 1 1 An I Spy Configuration ANN Informix database server l SPY daemon daemon Activity database Each of these components can be run on the same computer or on separate computers Chapter 2 I Spy Configuration discusses the various configuration options I Spy Features I Spy is a query management tool that provides all of the following features m 1 Spy installs transparently into an existing environment No changes need to be made to either the client applications or the database server for I Spy to operate The only change necessary for the client is a different value for INFORMIXSERVER Depending on how I Spy is configured even the change to the client environment is unnecessary Configuration is covered in Chapter 2 1 4 Informix I Spy User Manual Current Limitations I Spy logs SQL statement text and various statistics related to the statement such as total time of execution number of rows returned length of the returned
59. nt of time is greater than the value supplied the condition is considered true ITIME is associated with whatever rule immediately preceded it in the configuration file You can only combine the ITIME condition with the USER and HOST conditions within a rule LOGFILE Syntax LOGFILE logfile path Example LOGFILE usr ispy log ispy log Required Yes LOGFILE specifies a file used to record messages from the I Spy daemon process The daemon writes messages to this file when it starts and termi nates and if any type of warning or fatal error condition is encountered Each line of the file contains the date and time the message was written for example 10 30 15 14 29 I Spy beginning execution pid 27433 The command ispy m displays the last 20 lines of the log file 3 22 Informix I Spy User Manual PHASE PHASE Syntax PHASE connect dbopen active disconnect Example PHASE dbopen Required No I Spy separates each client connection into one of four distinct phases connection database open active or disconnect The PHASE parameter indicates which phase should be associated with the rules that follow in the configuration file The phase specified is in effect until either another PHASE parameter is seen or the end of the file is reached It is not required to specify all phases in the configuration file only those phases in which you want to define rules Rules inthe connect dbopen and disconnect phase c
60. o 11 on line manuals Intro 11 printed manuals Intro 11 release notes Intro 12 E ECOST 3 18 ENDSESS 3 9 A BC D EF GH Environment variables Intro 6 INFORMIXDIR 2 8 INFORMIXSOLHOSTS 2 8 ISPY CONFIGFILE 2 7 ISPY DIR 2 7 overview of 2 6 PATH 2 9 shared library search path 2 8 en us 8859 1 locale Intro 5 EROWS 3 18 ERROR 3 9 Error message files Intro 11 EXECSOL 3 10 EXECUTE 3 10 EXPLAINTAGS 3 12 3 19 F Find Error utility Intro 11 finderr utility Intro 11 G Global Language Support GLS Intro 5 H HOST 3 20 httpd conf 6 7 Icons Important Intro 7 Tip Intro 7 Warning Intro 7 Important paragraphs icon for Intro 7 index html 4 6 Industry standards compliance with Intro 12 isexplain 5 3 isload 5 5 ISO 8859 1 code set Intro 5 ispy 5 6 ispymail sh 3 12 8 5 2 Informix I Spy User Manual JK L MN OP QRS TU VW XY 2 ISPYSERVER 3 21 ispy_activity table 6 9 ispy_explain table 6 13 ispy_session table 6 8 ispy_sqltype table 6 15 ITIME 3 22 L Limitations product 1 5 Locale Intro 5 LOGFILE 3 22 M Message file for error messages Intro 11 N Netscape Web server 4 12 NOLOG 3 13 O On line manuals Intro 11 P PDQ priority 8 6 PHASE 3 23 Printed manuals Intro 11 Q QTIME 3 23 R Release notes Intro 12 rofferr utility Intro 11 ROWS 3 25 RULE 3 25 S Sample code conventions Intro 10 Setnet32 2 13 SHADOWSERVER 3 27 Software dependencies
61. on Query appears in the explain text The number of times the string Subquery appears in the explain text 2 of 2 The isexplain utility adds new rows to this table by processing sqexplain out files created by an Informix database server This is the only manner in which this table is populated The ispy daemon process never accesses this table ISPY SQLTYPE The columns in this table such as exp seqscan count the number of specific strings in the explain text You can use these columns to quickly locate the SQL statements that might be performance problems or that contain a behavior that you want to investigate For example you might be interested in the queries that contain the string AUTOINDEX PATH because this string is probably an indication that an index is needed You could use the following SELECT statement to find those queries select exp text from ispy explain where exp autoindex gt 0 If you wanted further information on these queries from the activity table you could execute the following statement select e exp text duos from ispy activity a ispy explain e where e act explain id a act explain id and e exp autoindex gt 0 ISPY SQLTYPE The ispy_sqltype table describes the various types of SOL statements that you can submit to a database server Each row contains a unique identifier for a specific type of SOL statement and a short name that describes it The ispy sgltype table c
62. ontains the columns that the following table shows Column Name Type Explanation sql id SMALLINT Link to the act sqltype column in the ispy activity table sql name VARCHAR 255 Short descriptive name for this type of SOL statement Activity Database Layout 6 15 ISPY SQLTYPE 6 16 sql id 1 2 3 10 11 12 13 14 15 16 17 18 Informix I Spy User Manual sql name DATABASE SELECT SELINTO UPDATE DELETE INSERT UPDCURR DELCURR LDINSERT LOCK UNLOCK CREADB DROPDB CRETAB DRPTAB CREIDX DRPIDX GRANT The Web interface uses this reference table to provide descriptive names for SQL statement types The table is populated by the file ISPY_DIR sql ispy_sqltype unl when the ISPY_DIR sqlispy sql file is processed Currently only the reports in the ISPY DIR sql directory and the Web interface access this table The following table displays the data contained in the ispy_sqltype table 10f 4 ISPY SQLTYPE sql id sql name 19 REVOKE 20 RENTAB 21 RENCOL 22 CREAUD 23 STRAUD 24 STPAUD 25 DRPAUD 26 RECTAB 27 CHKTAB 28 REPTAB 29 ALTER 30 STATS 31 CLSDB 32 DELALL 33 UPDALL 34 BEGWORK 35 COMMIT 36 ROLLBACK 37 SAVEPOINT 38 STARTDB 39 RFORWARD 40 CREVIEW 41 DROPVIEW 42 DEBUG 2 of 4 Activity Database Layout 6 17 ISPY SQLTYPE 6 18 sql id 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 67 68 Informix I Spy User Manual
63. ot be used for production systems Configuration File 3 17 ECOST ECOST Syntax ECOST integer Example ECOST 9999 Required No ECOST is a rule condition that specifies the estimated cost of an SOL statement submitted for execution by a client application If the estimated cost of the statement is equal to or greater than the value supplied the condition is considered true ECOST is associated with whatever rule immediately preceded it in the configuration file EROWS Syntax EROWS integer Example EROWS 9999 Required No EROWS is a rule condition that specifies the estimated number of rows in the result set of an SQL statement submitted for execution by a client application If the estimated number of rows for the result set of the statement is equal to or greater than the value supplied the condition is considered true ROWS is associated with whatever rule immediately preceded it in the configuration file 3 18 Informix I Spy User Manual EXPLAINTAGS EXPLAINTAGS Syntax EXPLAINTAGS 011 1 EXPLAINTAGS 0 Required Yes EXPLAINTAGS specifies whether or not to append a unique comment identifier to each SOL statement submitted by clients The isexplain utility uses this unique identifier to load explain text into the activity database and link it to the original SOL statement The values 0 and 1 disable and enable this feature respectively If a client submitted the following SOL statement
64. our Comments Informix I Spy In This Chapter What Is I Spy I Spy Features Current Limitations NN GT OF FP BW 10 iv Chapter 2 Chapter 3 Informix Spy User Manual I Spy Configuration In This Chapter I Spy Directory Structure activity wrist bin etc gls lib log msg release sql web nes Environment Variable Settings ISPY DIR ISPY CONFIGFILE INFORMIXDIR and INFORMIXSQLHOSTS Shared Library Search Path PATH Prepare the ISCONFIG Cohgurati n File Prepare the sqlhosts File Start the ISPY Daemon Configuring Clients for I Spy 4 Configuring INFORMIX Connect What to Do Next Configuration File In This Chapter General Configuration File Ben Rules Overview Rule Execution Order Regular Expression m ACTION ACTION ENDSESS ACTION ERROR ACTION EXECSQL ACTION EXECUTE ACTION EXPLAINTAGS ACTION NOLOG 2 3 2 3 2 4 2 4 2 4 2 5 2 5 2 5 2 5 2 5 2 6 2 6 2 7 2 7 2 8 2 8 2 9 2 10 2 11 2 12 2 13 2 13 2 14 3 3 3 4 3 6 3 6 3 8 3 9 3 10 3 10 3 12 3 13 Chapter 4 ACTIVITY CAPTURE ACTIVITY DBNAME ACTIVITY DIR ACTIVITY ID ACTIVITY _ INFORMIXSERVER DEBUGFILE DEBUGLEVEL ECOST EROWS EXPLAINTAGS HOST ISPYSERVER ITIME LOGFILE PHASE QTIME ROWS RULE SHADOWSERVER SQL SQLLENGTH SQLTRIM USER Advanced Gontieuration Options Monitoring Multiple Database Serveis Configu
65. ow of the Web interface open the URL http hostname ispy in your Web browser where hostname is the name of the computer where you have installed I Spy Figure 7 1 shows the main window Figure 7 1 Main Window The interface consists of three frames one at the top with the product name one with all the menu options along the left side and a third frame that changes depending on the menu option selected 7 4 Informix l Spy User Manual The Startup Menu Option Status information on I Spy appears when you click the Status button from the menu or when the opening page is first loaded in your Web browser The status window contains the following fields Field Description Host The host name of the computer where LSpy is installed Version The version and serial number of I Spy This is the same information that the command ispy V generates Configuration File The full path to the configuration file that I Spy uses Status The ispy daemon process currently running The Startup Menu Option The Startup menu option takes you to the window in Figure 7 2 which allows you to start the ispy daemon Figure 7 2 Startup Window Using The Web Interface 7 5 The Shutdown Menu Option Click the Startup button to run the ispy daemon process After the daemon is started the Status window appears The Shutdown Menu Option The Shutdown menu option takes you to the window in Figure 7 3 which allows you to stop the ispy
66. p mindexpath SMALLINT The number of times the string MULTI INDEX PATH appears in the explain text exp keyonly SMALLINT The number of times the string Key Only appears in the explain text exp aggregate SMALLINT The number of times the string Aggregate appears in the explain text exp temptable SMALLINT The number of times the string Temporary File Required For or Temp Table for View appears in the explain text 10f2 Activity Database Layout 6 13 ISPY EXPLAIN 6 14 Column Name exp sortscan exp mergejoin exp hashjoin exp buildinner exp buildinnerb exp buildouter exp buildouterb exp unionquery exp subquery Informix I Spy User Manual Type SMALLINT SMALLINT SMALLINT SMALLINT SMALLINT SMALLINT SMALLINT SMALLINT SMALLINT Explanation The number of times the string SORT SCAN appears in the explain text The number of times the string MERGE JOIN appears in the explain text The number of times the string DYNAMIC HASH JOIN appears in the explain text The number of times the string Build Inner appears in the explain text The number of times the string Build Inner Broadcast appears in the explain text The number of times the string Build Outer appears in the explain text The number of times the string Build Outer Broadcast appears in the explain text The number of times the string Uni
67. p with a considerable number of files with a old extension in the activity directory These files can safely be removed The isload utility does not automatically remove the files after processing to ensure that any possible errors that occur can be corrected and the files can be processed again by manually removing the old extension and executing isload again ispy I Spy Daemon Process The ispy utility is the daemon process for I Spy that listens for new client connections and routes all message traffic to the shadow database server Syntax X c configfile E 5 6 Informix I Spy User Manual Options c configfile k m r ispy I Spy Daemon Process This option specifies the full path to the configuration file that you want I Spy to use If a configuration file is not specified ispy looks at the environment variable ISPY_CONFIGFILE for the full path If this environment variable is not set ispy uses the default directory ISPY DIR etc isconfig This option directs ispy to execute without becoming a daemon process In this mode it does not fork and disconnect itself from the controlling terminal but continues to operate in the foreground This option is intended to be used by Informix Technical Support and you will likely never use it Return value None This option kills the currently running ispy daemon The message I Spy process 456 kill with a SIGTERM signal by ispy k is written to standa
68. phen that contain a unique session number and SQL statement number respectively Both are generated internally by I Spy For example the following values are all possible for the act explain id column N1 35 69 Y4 2 500 NO 1 1 isload Load Files into the Activity Database Use the isload utility to load activity that was captured to the file system into the activity database Syntax c configfile V Options c configfile This option specifies the full path to the configuration file that you want to use If a configuration file is not specified isload looks at the environment variable ISPY_CONFIGFILE for the full path If this environment variable is not set isload uses the default directory ISPY DIR etc isconfig V This option displays the current version and serial number to standard output For example INFORMIX I Spy Version 1 00 UB11 Software Serial Number RDS NO00000 l Spy Utilities 5 5 ispy I Spy Daemon Process The isload utility looks for session and activity files that exist in the activity directory that the ACTIVITY DIR parameter specifies Each I Spy client session will create two files in the activity directory named session and activity The is a unique number that I Spy generates and is used to associate the two files to a single client session Warning files to session old and activity old to represent that they have already been loaded Over time you might end u
69. ple ACTION ERROR Statement canceled ERROR terminates an executing SQL statement and returns an error message to the client All text on the line after the ERROR keyword is returned to the client application as an error message with a 746 error number The maximum length of the error message returned to the client is 57 characters Longer messages are truncated Configuration File 3 9 ACTION EXECSQL This action affects only the currently submitted or executing SOL statement The client is able to continue submitting subsequent SQL statements after receiving the 746 error ACTION EXECSQL Syntax ACTION EXECSOL SQL statement Example ACTION EXECSQL set isolation to dirty read EXECSQL submits SQL statements to the database server independent of the client application The client application is unaware that the SQL submission has taken place All text on the line after the EXECSQL keyword is sent to the database server as a single SQL statement This action is typically used in the dbopen phase of a session to correctly configure a client database environment Typical SQL statements used are SET ISOLATION SET LOCK MODE SET ROLE and EXECUTE PROCEDURE For example the following rule could be used for user smith phase dbopen rule 1 user smith action execsql set isolation to dirty read action execsql set lock mode to wait 60 Warning SQL statements submitted with this action are not allowed to return a result set because t
70. rd output and also placed in the I Spy log file If an ispy daemon process is not currently running the message An ispy process is not currently running will be written to standard error Return value 0 if an ispy process was successfully killed otherwise 1 This option displays the last 20 lines of the I Spy log file to standard output Return value Always 0 This option determines if an ispy daemon process is currently running If a daemon process is currently running the process id number is written to standard output I Spy Utilities 5 7 ispy I Spy Daemon Process Return value 0 if a daemon process is running otherwise 1 V This option displays the current version and serial number to standard output For example INFORMIX I Spy Version 1 00 UB11 Software Serial Number RDS N000000 Return value Always 0 If ispy is executed without any options it becomes a daemon process and listens for new client connections The TCP port number used for listening is obtained from the sqlhosts file entry for the server name that matches the ISPYSERVER value in the configuration file If ispy detects any problems in the configuration file it will issue an appropriate error message to standard error and exit without becoming a daemon process In this case the return value is set to 1 Important For changes to the configuration file to take effect the ispy daemon must be stopped and restarted Existing client connections
71. rge amount of unused space TEXT data types can share dbspace blobpages if more than one row can fit on a single page or if more than one trailing portion of a TEXT column can fit on a single page For general information on how TEXT data is stored refer to your Administrator s Guide Prepare the ispy sql File To prepare the ispy sql file using a standard text editor follow these steps 1 Copy the ISPY_DIR sql ispy sql std file to a new file named ispy sql in the same directory The new file ispy sql will be used to create all the tables for the activity database Do not modify the ispy sql std file because it reflects the default configuration of the activity database 2 Editthe new ispy sql file and change the column type of the act sqlstmt column to either TEXT or CHAR depending on the considerations previously presented Add Storage Specific Syntax The CREATE TABLE statements in ispy sql std have no syntax for storage options lock modes or extent sizes If appropriate you can add this syntax For more information on these options see the CREATE TABLE statement in the Informix Guide to SQL Syntax Activity Database Layout 6 5 Run ispy sgl Run ispy sql Use DB Access as user informix to create the activity database in the database server where you want it located You can create the database with or without logging although it is recommended to create it without logging to reduce the performance overhead of wri
72. ring I Spy with No Changes in the Client Poton Multiple I Spy Configurations One Activity Database Configuring the Web Interface In This Chapter Preparing the web cfg Web File Configuring Web Server Directory Access Adding a URL Alias Allowing CGI Execution and Ditedioty TUNE Granting Access to the Activity Database Testing the Web Interface Configuration of Basic Web Sami 3 14 3 15 3 15 3 16 3 16 3 17 3 17 3 18 3 18 3 19 3 20 3 21 3 22 3 22 3 23 3 23 3 25 3 25 3 27 3 27 3 28 3 28 3 30 3 30 3 31 3 32 3 33 4 3 4 4 4 5 4 5 4 7 4 7 4 8 Table of Contents v Chapter 5 Chapter 6 Chapter 7 Copy the Informix I Spy User Manual Custom Reports Link on the Reports Page Configuration for Netscape FastTrack Server Create New Document Directory Activate the CGI File Type I Spy Utilities In This Chapter isexplain Load Explain Text isload Load Files into the Activity Daaba I Spy Daemon Process Activity Database Layout In This Chapter Creating the Activity Dee Determine Database Location Determine act_sqlstmt Column Type Prepare the ispy sql File Add Storage Specific Syntax Run ispy sql Grant Connect Privilege ISPY_SESSION ISPY_ACTIVITY ISPY_EXPLAIN ISPY SOLTYPE Using the Web Interface In This Chapter The Main Window The Startup Menu Option The Shutdown Menu Option The Config File Menu Option The Clients
73. rning but the client will see all rows up to and including 1012 How send an email to the database administrator when a query has returned more than 1000 rows without ending the query Use the EXECUTE action to run a shell script that sends an appropriate error message For example the following rule could be used PHASE ACTIVE RULE 10 ROWS 1000 ACTION EXECUTE usr ispy bin ispymail sh Informix Spy User Manual Common Questions About I Spy The script ispymail sh is included in the I Spy distribution and has the following text HH bin sh MAILTO dbaGcompany com SUBJECT I Spy Alert ec Th I zommcozuccoc c DOC Wr ho is email RNAME ENTHOST ENTIP AME Hmm OST OWS co ETCH ART EPARE is bei SPY EXECUTE ng C C CO CO Co Co Co Co C C CO mailx s 5UBJ ge PY U PAG PY_C PY_D PY_S PY E PY E PY_ PY_S PY_P ECT PY_R PY_R erated from the script SERNAME TENTHOST TENTIP AME LE r OST OWS co ETCH TART REPARE MAILTO You can use this script as a template to create a more specific one for you environment Common Questions 8 5 Common Questions About I Spy 8 6 How do I prevent all users except informix from returning more than 1000 rows Create two rules in the active phase of the configuration file The fir
74. ropriate value for your operating system For example on SOLARIS you would use LD LIBRARY PATH This environment variable is discussed in Chapter 2 Important You must include ISPY CONFIGFILE even if you are using the default value of ISPY_DIR etclisconfig The web cfg file should not include any lines other than those that set specific environment variables Even blank lines are not allowed For example if you installed I Spy in usr ispy and were on a HP UX computer your web cfg file would resemble the following example ISPY DIR usr ispy ISPY CONFIGFILE usr ispy etc isconfig serverl SHLIB PATH usr ispy lib INFORMIXSQLHOSTS usr ispy etc sqlhosts serverl Configuring Web Server Directory Access Follow these steps to configure your Web server to access the ISPY DIR web directory 1 Adda URL alias so that you can use the URL http hostname ispy to start the Web interface 2 Allow directory access and CGI script execution for ISPY DIR web Adding a URL Alias The Apache Web server allows you to define aliases for pathnames so that you do not have to enter the entire path to retrieve a file Informix recom mends that you configure an alias to simplify access to the I Spy Web interface In the Apache srm conf file add the following line Alias ispy usr ispy web Configuring the Web Interface 4 5 Allowing CGI Execution and Directory Access 4 6 This example assumes that you have installed I Spy in
75. rows returned to the ISPY ROWS 5000 client for the current SOL statement ISPY RULE The number of the rule that caused ISPY RULE 10 this execute action to start ISPY SOL The text of the current SQL statement ISPY_SQL select count from customer ISPY START The date and time the current SOL ISPY START 1998 12 24 12 56 58 statement began executing ISPY_USERNAME The username for the client ISPY USERNAME jsmith connection Configuration File 3 11 ACTION EXPLAINTAGS 3 12 The following sample shell script named ISPY DIR BIN ISPYMAIL SH demonstrates how you can use these environment variables to send an email 1 bin sh MAILTO dba company com SUBJECT I Spy Alert echo This email is being generated from the script ISPY_EXECUTE USERNAME SPY_USERNAME CLIENTHOST SPY_CLIENTHOST CLIENTIP SPY_CLIENTIP DBNAME SPY_DBNAME RULE SPY_RULE SQL SPY SQL ECOST SPY ECOST EROWS SPY EROWS ROWS SPY ROWS FETCH SPY NFETCH START SPY START PREPARE SPY PREPARE mailx s SUBJECT MAILTO Syntax ACTION to 1 EXPLAINTAGS Example ACTION 0 EXPLAINTAGS EXPLAINTAGS specifies whether or not to append a unique comment identifier to each SOL statement submitted by clients The isexplain utility uses this unique identifier to load explain text into the activity database and link it to the original SQL statement The values 0 and 1 disable and enable this feature respectively Informix I Spy User Manual
76. rts html You can use this file to create your own page of links that generate reports that will automatically be available with future releases of I Spy Configuring the Web Interface 4 11 Configuration for Netscape FastTrack Server 4 12 1 2 1 Informix I Spy User Manual Configuration for Netscape FastTrack Server To configure the Netscape FastTrack server for the I Spy Web interface follow these steps Create an additional document directory Activate the CGI file type Create New Document Directory To create a new document directory follow these steps Click the Content Management button on the FastTrack adminis trative interface Select Additional Document Directories from the menu on the left Insert ispy in the URL prefix text box Insert your ISPY DIR web directory in the Map to Directory text box Click OK to add the new directory Activate the CGI File Type The following window shows the result of adding a new document directory Activate the CGI File Type To activate the CGI file type follow these steps 1 Click the Programs button on the FastTrack administrative interface 2 Select CGI File Type from the menu on the left 3 Select Yes for Activate CGI as a file type The following window shows the result of making this change Configuring the Web Interface 4 13 Activate the CGl File Type You should now be able use the URL http hostname ispy from your Web brow
77. s About This Manual Informix I Spy is a tool that provides new and more effective ways to quantify manage and control utilization of data warehouses for database administrators DBAs and data warehouse architects Informix I Spy lets DBAs view the SOL being executed and the data being accessed It reports execution time and the quantity of data being returned You can use the product to identify inefficient queries and pinpoint users who might need help or additional training Informix I Spy has standard utilization reports that can help you avoid capacity problems by providing the precise data needed for capacity planning Warehouse architects can use the information collected by I Spy to help refine their warehouse model for its next version They can analyze query plans to develop optimal indexing strategies and determine which fact and dimension tables are really being used This manual provides detailed instructions to configure and use Informix I Spy to monitor and control SQL statements submitted to an Informix database server Introduction 3 Types of Users Types of Users This manual is written for the following users m Database administrators m Database developers m Data warehouse architects This manual assumes that you have the following background m Aworking knowledge of your computer your operating system and the utilities that your operating system provides m Some experience working with relational databa
78. s in this directory The first file is named session where is a unique number that I Spy generates to uniquely identify each session The second file is named activity where is the same unique number used for the session file Each file contains information in standard Informix load format You can use the isload utility to load the activity database with any files that exist in this directory Important Monitor the contents of this directory even if you have ACTIVITY CAPTURE set to 1 If I Spy encounters any type of database error while it saves information to the activity database it writes an error message into the log file and switches to saving information in the file system This allows clients to work without being interrupted and still record all of their activity Configuration File 3 15 ACTIVITY ID ACTIVITY ID Syntax ACTIVITY ID integer Example ACTIVITY_ID 5 Required Yes ACTIVITY_ID uniquely identifies multiple I Spy instances that log into the same activity database The integer value specified is used to create the value generated for the act_explain_id column of the ispy_activity table Each I Spy instance that logs to the same activity database should have a unique value for ACTIVITY ID Failure to define unique values for ACTIVITY ID results in insert errors when you log into the activity database or when you use the isload utility after logging to the file system has taken place ACTIVITY INFORM
79. se connect phase dbopen phase active phase disconnect on71_net on71_spy 0 usr ispy log ispy log 0 ispy on71 shm usr ispy activity 0 if Actual Informix Server iF Informix server value for clients iF O off 1 on 0 None 1 Database 2 File To prepare the ISCONFIG file using a standard text editor 1 Make a copy of the ISPY DIR etc isconfig std file Store the new file in the ISPY DIR etc directory Do not modify isconfig std Informix suggests that you choose a filename that reflects the name of the database server you want to log activity for For example isconfig sales net if your database server was named sales net Set your ISPY CONFIGFILE environment variable to the name of your new file Informix I Spy User Manual Prepare the sqlhosts File 2 Edit your new ISCONFIG file to modify the configuration parameters that you have decided to change For the initial configuration of I Spy you can leave most of the parameters set to their initial settings The following parameters must be reviewed and changed if necessary SHADOWSERVER ISPYSERVER LOGNAME ACTIVITY DIR ACTIVITY ID For more information on the parameters see Chapter 3 Prepare the sqlhosts File The sqlhosts file contains information that is required to allow an Informix client application to connect to an Informix database server For more infor mation on the content of the sqlhosts file see your Administrator s G
80. ser to access the I 5py Web interface 4 14 Informix I Spy User Manual I Spy Utilities isexplain Load Explain Text on non nn 5 3 isload Load Files into the Activity Database 55 ispy I Spy Daemon Process nn nn 5 6 5 2 Informix I Spy User Manual In This Chapter This chapter provides reference material for all of the I Spy utilities The utilities allow you to execute administrative tasks directly from your command line The following utilities are documented in this chapter m isexplain m isload m ispy isexplain Load Explain Text Use the isexplain utility to scan sqexplain out files generated by Informix database servers for query plans that you can load into the I Spy activity database Syntax isexplain explainfile c configfile l Spy Utilities 5 3 isexplain Load Explain Text 5 4 Options c configfile V V Informix l Spy User Manual explainfile This option specifies the full path to the configuration file that you want to use If a configuration file is not specified isexplain looks at the environment variable ISPY CONFIGFILE for the full path If this environment variable is not set isexplain uses the default directory ISPY DIR ETC ISCONFIG This option specifies a verbose mode where isexplain writes to standard output all the values for the row that will be inserted into the ispy explain table in the
81. ses or exposure to database concepts m Some experience with database server administration operating system administration or network administration If you have limited experience with relational databases SOL or your operating system refer to the Getting Started manual for your database server for a list of supplementary titles Software Dependencies This manual assumes that you are using one of the following database servers m Informix Dynamic Server with Advanced Decision Support and Extended Parallel Options Version 8 11 m Informix Dynamic Server with Advanced Decision Support and Extended Parallel Options Version 8 2 m Informix Dynamic Server Version 7 2 Informix Dynamic Server Version 7 3 Informix Dynamic Server Workgroup Edition Version 7 3 4 Informix I Spy User Manual Assumptions About Your Locale Assumptions About Your Locale Informix products can support many languages cultures and code sets All culture specific information is brought together in a single environment called a Global Language Support GLS locale The examples in this manual are written with the assumption that you are using the default locale en us 8859 1 This locale supports U S English format conventions for dates times and currency In addition this locale supports the ISO 8859 1 code set which includes the ASCII code set plus many 8 bit characters such as e and fi If you plan to use nondefault characters in
82. sion table act_id SERIAL NOT A unique id for this SQL statement NULL act_sqltype SMALLINT A value that indicates what type of SOL statement this is This value corresponds directly to the value of sqlca sqlcode after a DESCRIBE statement in an INFORMIX ESQL C program Labels for all possible SOL types are contained in the ispy_sqltype table act sglstmt Either CHAR or The text of this SOL statement TEXT act isdistrib SMALLINT A flag indicating if this SOL statement is distributed that is from another database server and not a client 0 1 Yes This functionality is currently supported The value should always be 0 act curname VARCHAR 255 The name of the cursor used for this SOL statement Will be null if no cursor name was defined act curishold SMALLINT A flag indicating if the cursor was declared with hold 0 No 1 Yes act curisscroll SMALLINT A flag indicating if the cursor was declared as scrolling 0 No 1 Yes act prepare DATETIME YEAR The date and time when this statement was TO SECOND prepared 1 of 3 Activity Database Layout 6 9 ISPY ACTIVITY 6 10 Column Name act starttime act curopen act firstrow act lastrow act curclose act endtime act enginetime act estrows act cost act nrows act rowlen blobbytes act nfetch Informix I Spy User Manual Type DATETIME YEAR TO SECOND DATETIME YEAR TO SECOND DATETIME YEAR TO SECOND DATETIME YEAR TO SEC
83. sql name CREASYN DROPSYN CTEMP WAITFOR ALTIDX ISOLATE SETLOG EXPLAIN SCHEMA OPTIM CREPROC DRPPROC CONSTRMODE EXECPROC DBGFILE CREOPCL ALTOPCL DRPOPCL OPRESERVE OPRELEASE OPTIMEOUT PROCSTATS SKINHIBIT SKSHOW 3 of 4 sql id 69 70 7 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 sql name SKSMALL CRETRIG DRPTRIG UNKNOWN SETDATASKIP PDOPRIORITY ALTFRAG SETOBJMODE START STOP SETMAC SETDAC SETTBLHI SETLVEXT CREATEROLE DROPROLE SETROLE PASSWD RENDB 4 of 4 ISPY SQLTYPE Activity Database Layout 6 19 Using the Web Interface The Main Window The Startup Menu Option The Shutdown Menu Option The Config File Menu Option The Clients Menu Option The Reports Menu Option The View Log Menu Option The User Manual Menu Option 7 4 7 5 7 6 7 7 7 8 7 10 7 12 7 12 7 2 Informix I Spy User Manual In This Chapter This chapter describes how to use the various options of the I 5py Web interface You should have already configured the Web interface following the directions in Chapter 4 Configuring the Web Interface This chapter covers the following topics The Main Screen The Startup Menu Option The Shutdown Menu Option The Config File Menu Option The Clients Menu Option The Reports Menu Option The View Log Menu Option The User Manual Menu Option Using The Web Interface 7 3 The Main Window The Main Window To display the main wind
84. st rule includes a user condition for informix while the second rule does not PHASE ACTIVE RULE 1 USER informix ROWS 99999999 ACTION ERROR Row limit exceeded RULE 2 ROWS 1000 ACTION ERROR Row limit of 1000 exceeded Be sure to include the rules in the configuration file in the order shown because I Spy processes rules in this order User informix will match rule number 1 and allow an arbitrarily large number of rows to be returned while all other users will mat How do assign a default PDQ priority for all users This question has two answers and both have different ramifications First you could create a rule in the database open phase of the configuration file For example PHASE DBOPEN RULE 1 ACTION EXECSQL set pdqpriority 30 This rule sets the pdqpriority when a database is first opened but the user can change it later Alternatively you could create the following rule in the active phase of the configuration file PHASE ACTIVE RULE 2 ACTION EXECSQL set pdqpriority 30 This rule sets the pdqpriority before every SOL statement that executes This prevents users from changing the pdqpriority themselves Informix Spy User Manual Common Questions About I Spy How do I check the syntax of the rules in my configuration file without shutting down and starting up I Spy The command ispy r which determines if an ispy process is running also checks the syntax of the configuration file and reports an
85. t is presented when you select a hot link Figure 7 6 Client Detail Window The Client Detail window displays the last five SOL statements that a client has executed If the client has a currently executing SOL statement it is not displayed Using The Web Interface 7 9 The Reports Menu Option The Reports Menu Option The Reports menu option takes you to the window in Figure 7 7 which displays a list of reports that you can execute against the activity database Figure 7 7 Reports Window 7 10 Informix I Spy User Manual The Reports Menu Option I Spy prompts for a number of input values before it generates the report Figure 7 8 shows the values required to execute the Longest running queries report Figure 7 8 Longest Running Queries Report Each report has a similar set of fields that you must fill in before you click the Run Query button on the bottom of the screen to execute the report Each report has the following set of common input fields that control how the report is returned Field Description How many rows to The maximum number of rows to return for this report return Output format The output format for the report HTML returns the report as a table while unload returns the report with one row per line and a specific delimiter between each field The unload format is intended to be used to import the results into other applications such as a spreadsheet Using The Web Interface 7 11 The
86. t never prepared the SQL statement If no cursor was associated with the SOL statement the act curname act curopen and act curclose columns are all null Activity Database Layout 6 11 ISPY ACTIVITY The elapsed time that the SOL statement took to execute is act endtime act starttime This includes any time the client spent processing with a cursor open but not waiting for a response from the database server For example a client application could open a cursor for an SQL statement that returns 100 fetch the first 10 rows from the database server and then perform some other type of processing After this extra processing concludes the application can fetch the remaining 90 rows This extra processing time is reflected in the act endtime column because it includes all the time while the cursor was open The act enginetime column is an accumulation of the engine time spent processing a client request In the preceding example the extra processing time spent by the application would not be included in the act enginetime column For most applications the act enginetime should be very close to the act endtime act starttime A significant difference between these values indicates that the application is spending the time performing nondatabase work You might want to explore this area to explore if application performance is a problem The act explain id column is a link into the ispy explain table which might contain the explain te
87. t the product displays and information that you monospace enter appear in a monospace typeface KEYSTROKE Keys that you are to press appear in uppercase letters in a sans serif font This symbol indicates the end of one or more product or platform specific paragraphs gt This symbol indicates a menu item For example Choose Tools Options means choose the Options item from the Tools menu Tip When you are instructed to enter characters or to execute a command immediately press RETURN after the entry When you are instructed to type the x text or to press other keys no RETURN is required Informix Spy User Manual Icon Conventions Icon Conventions Comment icons identify three types of information as the following table describes This information always appears in italics Icon Label Description Warning Identifies paragraphs that contain vital instructions N cautions or critical information Important Identifies paragraphs that contain significant information about the feature or operation that is being described Tip Identifies paragraphs that offer additional details or shortcuts for the functionality that is being described Command Line Conventions This section defines and illustrates the format of commands that are available in Informix products These commands have their own conventions which might include alternative forms of a command required and optional parts of the comman
88. terface for I Spy see Chapter 4 To configure activity logging see Chapter 6 on the activity database and the following configuration file parameters in Chapter 3 a a a a ACTIVITY CAPTURE ACTIVITY DBNAME ACTIVITY DIR ACTIVITY INFORMIXSERVER For more information on capturing the explain text for SOL state ments read about the isexplain utility on page 5 3 and review the configuration file parameter EXPLAINTAGS on page 3 6 For advanced configuration options such as using I Spy with more than one Informix database server see Chapter 3 Configuration File General Configuration File Syntax Rules Overview Rule Execution Order Regular Expression Syntax ACTION ACTION ENDSESS ACTION ERROR ACTION EXECSOL ACTION EXECUTE ACTION EXPLAINTAGS ACTION NOLOG ACTIVITY CAPTURE ACTIVITY DBNAME ACTIVITY DIR ACTIVITY ID ACTIVITY INFORMIXSERVER DEBUGEFILE DEBUGLEVEL ECOST EROWS EXPLAINTAGS 3 3 3 4 3 6 3 6 3 8 3 9 3 9 3 10 3 10 3 12 3 13 3 14 3 15 3 15 3 16 3 16 3 17 3 17 3 18 3 18 3 19 HOST ISPYSERVER ITIME LOGFILE PHASE QTIME ROWS RULE SHADOWSERVER SOL SQLLENGTH SOLTRIM USER Advanced Configuration Options Monitoring Multiple Database Servers Limitations Configuring I Spy with No Changes i in the Client Environment 1 Multiple I Spy Configurations One Activity Database 3 2 Informix Spy Us
89. ters such as ispyserver or activity_capture can be in lowercase or uppercase characters Rules Overview The rules section of the configuration file controls various aspects of client connections I Spy separates each client session into four distinct phases connection database open active and disconnect You can define rules in the configuration file for each phase The phase parameter signifies that all rules in the file after that point are associated with that phase until either the end of the file or another phase parameter is seen 3 4 Informix I Spy User Manual Rules Overview Consider the following section of a configuration file PHASE connect RULE 1 USER informix ACTION execute usr local bin ispy connect sh PHASE dbopen PHASE active RULE 2 QTIME 300 ACTION error Query exceeded maximum execution time of 5 minutes PHASE disconnect Rule 1 is only applied when a new client connection is made Rule 2 is only applied in the active phase or after a database is opened and the client is able to submit SQL statements Each rule contains a number of conditions and actions with each condition or action listed on a separate line in the file If all the conditions for a rule are true I Spy executes all actions defined for that rule Consider the following sample rule RULE 10 USER informix HOST cheetah ITIME 600 ACTION endsess ACTION execute usr local bin ispy endsess sh The USER HOST and
90. this chapter describes how to specify a different location and filename for the configuration file Informix Spy User Manual gls 915 The gls directory contains the GLS files necessary for the I Spy utilities The use of these files is entirely transparent to the operation of I Spy and you can safely ignore this directory lib The lib directory contains the shared library files necessary for the I Spy utilities to operate It is necessary to configure your environment so that the operating system looks into this directory for shared libraries before it runs any of the I Spy utilities This topic is discussed later in this chapter log The log directory is an empty directory you can use to hold the I Spy log file and the I Spy debug file if debugging is enabled msg The msg directory contains the Informix message files required for the I Spy utilities The use of these files is entirely transparent to the operation of I Spy and you can safely ignore this directory release Important The release directory contains any last minute information on I Spy that could not be added to this documentation It is very important that you read the files in this directory before you continue with the I Spy configuration I Spy Configuration 2 5 sql 2 6 E sql The sql directory contains ispy sql the schema file used to create the activity database A number of rpt_ sql files are also contained in this directory Each
91. ting SOL activity to a logged database For example in DB Access you would execute the following SOL statement create database ispy It is important that the database name you choose matches the ACTIVITY DBNAME parameter setting in the I Spy configuration file you are using Informix recommends that you use ispy for the database name unless you have a specific reason to choose another name Warning Do not make the activity database ANSI compliant because I Spy will fail to execute After the database is created change to the ISPY_DIR sql directory and execute the ispy sql script to create the activity database tables as the following example shows cd ISPY_DIR sql dbaccess ispy ispy sql Tip Ifthe activity database resides on a different computer than the I Spy instal lation you might have to copy the ispy sql file to this computer and run DB Access there Grant Connect Privilege It is necessary to grant Connect privilege on the I Spy activity database for those users who need access In the simplest configuration only user informix needs Connect privilege which it has automatically since you created the activity database as user informix In this case there is nothing further to do 6 6 Informix I Spy User Manual Grant Connect Privilege If you plan on using the Web interface to administer I Spy you must grant Connect privilege to the user that your Web server runs as You might need to contact your
92. tring 000 If the asterisk is the first character in a RE it has no special meaning and is treated as itself A period in a RE matches any single character For example in the SQL statement select coll col2 from customer the RE col matches the strings coll and col2 If used as the first character of a RE the circumflex matches the beginning of the string For example in the SQL statement select from customer the RE select matches the string select On the other hand the same RE select does not match the SQL statement select from customer because the SQL statement has a blank character preceding the word select 1 of 2 Configuration File 3 7 ACTION Character Description If used as the last character of a RE the dollar sign matches the end of the string For example in the SOL statement delete from orders the RE orders matches the string orders but does not match the SOL statement delete from orders A string of characters enclosed in square brackets matches any single character in that string If the first character after the left square bracket is a circumflex it matches any character except those listed after the circumflex The minus character represents a range of characters For example 0 9 is short for 0123456789 For example in the SOL statement select from customer1 the RE customer 0 9 match
93. uide If you installed I Spy on a computer that already contained Informix products you should already have an existing sqlhosts file In this case you must add a new line to that file that contains the ISPYSERVER name from the configuration file Otherwise you will have to create a new sqlhosts file that contains a line for both the SHADOWSERVER name and the ISPYSERVER name from the configuration file In either case when you add the ISPYSERVER name to the sqlhosts file you must choose an unused TCP port for I Spy to listen on for client connections This port must not be used by any other software installed on the computer On most operating systems TCP port numbers below a certain value such as 1024 can only be accessed by user root Since the I Spy daemon process is started as user informix choose a port number larger than this value Check with your system administrator to determine an appropriate port number to use I Spy Configuration 2 11 Start the ISPY Daemon 2 12 Start the ISPY Daemon Start the I 5py daemon process named ispy as user informix Assuming you have set the appropriate environment variables and prepared the configu ration file as described previously you can start the daemon with the command ispy as follows ispy The ispy process will now run in the background as a daemon listening for new client connections To verify that the daemon process is indeed running you can execute ispy agai
94. xt for the query The first character of this column is either a Y or an N indicating that a corresponding row exists or does not exist in the explain table When the isexplain utility loads the explain text for a query it updates the first character of this column changing the N to a Y The remaining characters in the column are three numbers separated by a hyphen which contain an activity id a unique session number and an SOL statement number respectively I Spy generates these three numbers internally For example the following values are all possible for the act explain id column N0 35 69 Y3 2 500 N10 1 1 6 12 Informix I Spy User Manual ISPY EXPLAIN ISPY EXPLAIN The ispy explain table describes the SOL explain text for queries contained in the ispy activity table Each row of this table corresponds to exactly one row in the explain table The explain table contains the columns that the following table shows Column Name Type Explanation act explain id VARCHAR 25 A link to the act explain id column of the act activity table exp text TEXT The explain text for this SOL statement exp seqscan SMALLINT The number of times the string SEQUENTIAL SCAN appears in the explain text exp autoindex SMALLINT The number of times the string AUTOINDEX PATH appears in the explain text exp indexpath SMALLINT The number of times the string INDEX PATH appears in the explain text ex
95. xu eme e ee Ue ESR UE wo out ZA Din abeunt eI AES fet aria Au te en xh tet eg LA d Pene 2 4 OLG x diu aX dedi Kd NU sodas d Ge ah io da W dH nita We amp lees ed 2 4 seats es an A Mur doe Klare we uocum doe Em o2 lbs Seu a el eS 5 2 5 NOGA scere ey ag pat a a Be ee Seg wi MISES eed at uda ae Ao Ge at oe i tok Ge oh ae Sed 2 5 reledse ed woe BO AE a Rk a Beene a od 2 5 Sal ct whi oe oet dnd diae ie ke et me ah oe ls ke eh eee eL 732 6 Webs e g as ese Bear Gad oo amp oth a de oed sv Gh ks 1 2 6 Environment Variable Settings nn 26 ISPA DIR S ao 4 4 Wo e ovo 1237 ISPY CONFIGFILE ec ee ee INFORMIXDIR and INFORMIXSQLHOSTS Pl dale ee 5258 Shared Library Search Path nn nn 28 x Vost et etu X ee ot 1279 Prepare the ISCONFIG Configuration File 210 Prepare the sqlhosts File 2 2 11 StarttheISPY Daemon 5 212 Configuring Clients for 5 ien edt So oh oe dne ei uer ee aee ATI Configuring INFORMIX Connect dcus Rep el ue WE pe rb ts s Ge ND What to Do Next 2 14 2 2 Informix I Spy User Manual In This Chapter Before you can begin using I Spy you must configure it for your environment and start to run the I 5py d
96. y errors This report provides a simple way to verify the contents of the configuration file before shutting down and restarting the ispy process For example if you had the following syntax error in your configuration file PHASE activelO ispy r would report Line 18 Invalid PHASE argument activel0 Common Questions 8 7 A BC EF GH JK LMNOPQRSTUV W X Z Index A access conf 4 6 4 9 ACTION 3 8 Activity database connect privilege 6 5 creating 6 3 ispy sql schema file 6 5 ispy activity table 6 9 ispy explain table 6 13 ispy session table 6 8 ispy_sqltype table 6 15 ACTIVITY CAPTURE 3 14 ACTIVITY DBNAME 3 15 ACTIVITY DIR 3 15 ACTIVITY ID 3 16 ACTIVITY INFORMIXSERVER 3 16 ANSI compliance level Intro 12 Apache web server 4 3 Boldface type Intro 6 C Clients configuring INFORMIX Connect 2 13 general configuration 2 13 no changes 3 30 Code sample conventions for Intro 10 Command line conventions elements of Intro 8 example diagram Intro 9 how to read Intro 9 Comment icons Intro 7 Compliance with industry standards Intro 12 Configuration file general syntax 3 3 rule execution order 3 6 rules overview 3 4 Contact information Intro 13 Conventions documentation Intro 5 custom reports html 4 10 D DEBUGFILE 3 17 DEBUGLEVEL 3 17 Defaultlocale Intro 5 Dependencies software Intro 4 Documentation types of documentation notes Intro 12 error message files Intr
97. zes the amount of disk space used CHARACTER Data Type If you choose the CHARACTER data type for act sglstmt you will likely waste disk space with a very large size or lose parts of SQL statements with a very small size For example a size of CHAR 256 is probably too small If the length of a stored SOL statement is longer than 256 characters it will be truncated when it is saved to the activity database and you will not be able to view the entire statement later If on the other hand you choose a size such as CHAR 16384 you will waste significant amounts of disk space because each SOL statement will consume 16 kilobytes Informix Spy User Manual Prepare the ispy sq File Informix recommends that you start with a small average size such as 512 1024 or 2048 bytes to begin with If you notice that SOL statements are being truncated you can always use the SOL ALTER TABLE statement to increase the size of the column Changing the column size later has no effect on I Spy TEXT Data Type The TEXT data type is an alternative to the CHARACTER data type for storing SQL statements The advantage of using the TEXT type is that it is entirely variable in length just like the SOL statements your clients submit The smallest amount of disk space is used by storing the SOL statements in a TEXT column in a regular dbspace not a blobspace A blobspace always stores TEXT data on separate blobpages which will typically result in a la

Informix I-Spy User`s Manual

Contents

Download Pdf Manuals

Related Search

Related Contents