Regarding INFORMIX Basic Details

Hi 2 All, I am new to INFORMIX database. I am using INFORMIX 9.4 database in HP-UX System. I have few questions, Please clarify those questions for me : 1. What is in INFORMIX, similar to sqlplus in ORACLE ( to view the schema details - tables, indexes,... ) ? 2. What is dbaccess in INFORMIX ? 3. How to execute a sql script file in INFORMIX ? 4. What are the list of tables, that related to DBA, to tune the database ? 5. What are the tuning tools in INFORMIX ? 6. What is in INFORMIX, similar to statspack/AWR Report in ORACLE ? 7. What are the ways to tune a sql query in INFORMIX ? 8. What are the ways to tune a procedure in INFORMIX ? I would be very much thankful, if you Please explain me these questions separately with Examples. If you have any documents or URL, Please send me them. Thanks in Advance. With Regards, Raja.

raja said: > Hi 2 All, > > I am new to INFORMIX database. > > I am using INFORMIX 9.4 database in HP-UX System. > > I have few questions, Please clarify those questions for me : > > 1. What is in INFORMIX, similar to sqlplus in ORACLE ( to view the > schema details - tables, indexes,... ) ? See question 2. > 2. What is dbaccess in INFORMIX ? See question 1. > 3. How to execute a sql script file in INFORMIX ? See question 2. > 4. What are the list of tables, that related to DBA, to tune the > database ? $INFORMIXDIR/etc/onconfig > 5. What are the tuning tools in INFORMIX ? See question 4. > 6. What is in INFORMIX, similar to statspack/AWR Report in ORACLE ? Pass. onstat, perhaps? > 7. What are the ways to tune a sql query in INFORMIX ? Optimiser directives. > 8. What are the ways to tune a procedure in INFORMIX ? Code it better. > I would be very much thankful, if you Please explain me these > questions separately with Examples. > If you have any documents or URL, Please send me them. http://www-306.ibm.com/software/data/informix/pubs/library/ -- Bye now, Obnoxio "There were a myriad of problems which conspired to corrupt your reason and rob you of your common sense. Fear got the best of you, and in your panic you turned to the Labour Party. They promised you order, they promised you peace, and all they demanded in return was your silent, obedient consent."

I can answer some of your questions, some I don't know enough about Oracle to help with, others just don't relate to Informix. See below: On Fri, Jun 27, 2008 at 8:50 AM, raja <tssr2001@gmail.com> wrote: > Hi 2 All, > > I am new to INFORMIX database. Welcome. Keep an open mind and you'll grow to love it. > > > I am using INFORMIX 9.4 database in HP-UX System. Time for an upgrade. ;-) > > > I have few questions, Please clarify those questions for me : > > 1. What is in INFORMIX, similar to sqlplus in ORACLE ( to view the > schema details - tables, indexes,... ) ? You can see this information in tabular format in dbaccess. Run: dbaccess mydatabasename from the menu choose Table, then Info and select a table by typing in the name, or using the cursor keys if you terminal setup is correct. The top menu now presents elements of the table to examine. You can also see the SQL required to create the database with the dbschema utility. All are documented in the Administrator's Reference manual (dbaccess actually has its own manual). IDS manuals are online at: http://www-306.ibm.com/software/data/informix/pubs/library/ > > 2. What is dbaccess in INFORMIX ? It is a tool for executing SQL and viewing your database structure. It includes a rudimentary SQL editor and can be used to create, modify, and drop objects in the database. It has a menu mode and a commandline mode. See the manual. > > 3. How to execute a sql script file in INFORMIX ? 1. Three basic ways. In the dbaccess menus, select Query-language. Then you can use New, Use-editor, or Modify to create or modify a script, or Choose one from the current directory, then you can Run it. 2. dbaccess mydatabase myscriptname 3. dbaccess mydatabase - <myscriptname > > 4. What are the list of tables, that related to DBA, to tune the > database ? Read the Administrator's Guide, Administrator's Reference, and the Performance Guide then start scanning this list history for pointers. > > 5. What are the tuning tools in INFORMIX ? onstat, the ONCONFIG file ($INFORMIXDIR/etc/$ONCONFIG), the SET EXPLAIN ON; command, third party tools like AGS Server Studio, the bloody manuals! > 6. What is in INFORMIX, similar to statspack/AWR Report in ORACLE ? No idea. > > 7. What are the ways to tune a sql query in INFORMIX ? > 8. What are the ways to tune a procedure in INFORMIX ? See SET DEBUG FILE... and TRACE ... in the Guide to SQL Syntax manual. > > > I would be very much thankful, if you Please explain me these > questions separately with Examples. No. RTFM! You'll also find white papers and blogs on IDS on the IBM web site noted above. > > If you have any documents or URL, Please send me them. > > Thanks in Advance. > > With Regards, > Raja. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Performance tuning a database is a huge territory, so as others have said, without details of your system and the performance problems you are experiencing (if any), it's hard to help. A good first step is to read the Performance Guide. One major performance area I'll mention, because nobody else has so far, is the size of your bufferpool (onconfig "BUFFERPOOL" parameter). This is the main memory cache of database data. Like all aspects of computing, the memory cache needs to be large enough to hold the "working set" for your workload to avoid thrashing. In the case of a database, this means the data and index pages that are accessed frequently need to fit in the buffer pool. You can monitor buffer pool performance with "onstat -g buf" -- example output below (this from a version 11.50, but it will be similar in 9.40). You want the %cached for reads to be in the mid-to-upper 90's, and the %cached for writes to at least be around 90. These are the percentages of the time when data reads and data writes, respectively, found the page they needed already in the buffer pool, rather than having to read it in from disk which is a "page fault" from the standpoint of the database. If they are much below these, it means the buffer pool is too small for the working set and the database is thrashing. It is many orders of magnitude slower to have to read the page in from disk than if it was already present in memory in the buffer pool. You also want "Fg Writes" (meaning foreground writes) to be essentially zero. This is the number of times a page fault could not find a clean page in the buffer pool to use and had to write out a dirty page itself ("clean" it) before it could service the page fault. If you have frequent foreground writes, performance will be a dog. Look up the onconfig parameters LRU_MIN_DIRTY and LRU_MAX_DIRTY for how to tune page flusher behavior here. The page flushing should be tuned to be aggressive enough so that page faults can always be serviced with existing clean pages, thus avoiding foreground writes. You can zero out all onstat statistics (not just onstat -g buf's stats) via "onstat -z" so if you change something you can start the stats over at zero. (Changing the size of the buffer pool, however, requires rebooting IDS.) onstat -g buf output example (from 11.50): IBM Informix Dynamic Server Version 11.50.F -- On-Line -- Up 7 days 01:30:04 -- 1067632 Kbytes Profile Buffer pool page size: 2048 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 20226 22985 2292036 99.12 28202 92374 487634 94.22 bufwrits_sinceckpt bufwaits ovbuff flushes 81253 162 0 35 Fg Writes LRU Writes Avg. LRU Time Chunk Writes 0 0 nan 13818 Fast Cache Stats gets hits %hits puts 602493 580721 96.39 347705 -- Kevin Cherkauer Software Engineer IBM Informix Dynamic Server -- Database Kernel "raja" <tssr2001@gmail.com> wrote in message news:4c602408-b148-43f4-805f-79beb96878a6@s33g2000pri.googlegroups.com... > Hi 2 All, > > I am new to INFORMIX database. > > I am using INFORMIX 9.4 database in HP-UX System. > > I have few questions, Please clarify those questions for me : > > 1. What is in INFORMIX, similar to sqlplus in ORACLE ( to view the > schema details - tables, indexes,... ) ? > 2. What is dbaccess in INFORMIX ? > 3. How to execute a sql script file in INFORMIX ? > 4. What are the list of tables, that related to DBA, to tune the > database ? > 5. What are the tuning tools in INFORMIX ? > 6. What is in INFORMIX, similar to statspack/AWR Report in ORACLE ? > 7. What are the ways to tune a sql query in INFORMIX ? > 8. What are the ways to tune a procedure in INFORMIX ? > > I would be very much thankful, if you Please explain me these > questions separately with Examples. > If you have any documents or URL, Please send me them. > > Thanks in Advance. > > With Regards, > Raja.

-----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of raja Sent: Friday, June 27, 2008 8:51 AM To: informix-list@iiug.org Subject: Regarding INFORMIX Basic Details Hi 2 All, I am new to INFORMIX database. I am using INFORMIX 9.4 database in HP-UX System. I have few questions, Please clarify those questions for me : 1. What is in INFORMIX, similar to sqlplus in ORACLE ( to view the schema details - tables, indexes,... ) ? Dbschema --- 2. What is dbaccess in INFORMIX ? Similar to sqlplus 3. How to execute a sql script file in INFORMIX ? Dbaccess -e <database> <filename.sql> or run it from the dbaccess menu 4. What are the list of tables, that related to DBA, to tune the database ? You might look for the sysmaster tables in the Admin Guide 5. What are the tuning tools in INFORMIX ? Onstat, oncheck, explain, 6. What is in INFORMIX, similar to statspack/AWR Report in ORACLE ? Nothing other than tools mentioned above. 7. What are the ways to tune a sql query in INFORMIX ? 8. What are the ways to tune a procedure in INFORMIX ? What do you mean tune a query or procedure? You can run explain on a quey to see its execution plan. I would be very much thankful, if you Please explain me these questions separately with Examples. If you have any documents or URL, Please send me them. I don't have a url but the documentation is out there. Thanks in Advance. With Regards, Raja. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list