Informix 25580 的問題!!!

Informix technical support suggested the following;

1) Trap the error (onmode -I 25580 (eye not el)). In the event the error occurred an af file would be generated. Status: Sounded like a great idea, it did not work however.

2) Check kernel parameter SEMMNU. Informix release notes indicate this to be related to shared memory connections. Suggest between 2k and 4k. HP: Minimum 1, Maximum nproc-4, Default 30 Status: Awaiting kernel listing.

3) Check kernel parameter SEMUME. Informix release notes - no recommendations. HP:Minimum 1, Maximum semmns, Default 10 Status: Awaiting kernel listing.

4) Monitor onstat -g ntd (Focus: rejected connections, q-exceed) Server A: Rejected connections exist but related (1 for 1) to incorrect password, or user not trusted.
global network information: #netscb connects read write q-free q-limits q-exceed
677/ 762 245 5751447 5753308 14/ 28 240/ 10 975/ 0

Observations: May have a problem here. If I'm reading this correctly, it appears that connections have exceeded the threshold of q-limits 975 times. Every connection is availed 10 network buffers before it is faced to wait.

Server B: Approximately 20 rejected connections not accounted for. (NETTYPE ???)
global network information: #netscb connects read write q-free q-limits q-exceed
124/ 134 1006 2065017 2075158 11/ 16 170/ 10 0/ 0
Observations: Looks OK.
5) Monitor onstat -u to see if the number of users exceeds the NETTYPE setting. Server A: OK Server B: Under configured??
6) Check Syslog: Nothing found.
7) Network daemons may need kernel tuning in order to service. Not sure what this means. Doesn't appear to be any configurable kernel parameters regarding network

上圖是DB2 Memory structure 很重要
在使用資料庫上，我深深覺得Memory control 決定了系統的穩定與否，因此我看到了這圖覺得自己又學了更多，以下是節錄IBM文件說明
基本上是建構在32bit上

We will discuss each of these memory sets in detail.

Instance shared memory

There is one instance shared memory set per DB2 instance. Instance shared memory is allocated when the database manager is started (db2start), and freed when the database manager is stopped (db2stop). It is used for instance level tasks such as monitoring, auditing and inter-node communication. The following database manager configuration (dbm cfg) parameters control the limits to the instance shared memory and its individual memory pools:

• Instance memory (instance_memory)
• Monitor heap (mon_heap_sz): for monitoring use.
• Audit Buffer (audit_buf_sz): for use of the db2audit facility.
• Fast Communication buffers (fcm_num_buffers): for inter-node communication between partitions and agents. Partitioned instances or instances with INTRA_PARALLEL set to ON

• The instance_memory parameter specifies the amount of memory reserved for instance management. The default value is AUTOMATIC. This means DB2 will calculate the amount of instance memory needed for the current configuration, based on the sizes of the monitor heap, audit buffer and the FCM buffers. In addition, DB2 will also allocate some additional memory for overflow buffer. The overflow buffer is used to satisfy peak memory requirements for any heap in the instance shared memory region whenever a heap exceeds its configured size. In this case, the settings for the individual heaps are soft limits; they can grow during memory usage peaks.

• If instance_memory is set to a number, then the larger of instance_memory or the sum of mon_heap_sz, audit_buf_sz and fcm_num_buffers will be used. In this case, you are setting a hard limit to the instance memory, as opposed to a soft limit. When this limit is reached, you will get memory allocation errors. For this reason, it is recommended to leave the instance_memory setting as AUTOMATIC.

  If instance_memory is set to AUTOMATIC, it is possible to determine its value using the following commands:

• db2 attach to instance_name (where instance_name is the name of the instance)
• db2 get dbm cfg show detail

• The instance_memory parameter only sets the limit for the instance shared memory. It does not tell you how much memory is currently being used. To find out the memory usage of an instance, use the DB2 memory tracker tool, db2mtrk. For example,

• db2start
• db2mtrk -i -v

• Memory for instance
• FCMBP Heap is of size 17432576 bytes
• Database Monitor Heap is of size 180224 bytes
• Other Memory is of size 3686400 bytes
• Total: 21299200 bytes

The above example showed that although 42 MB is memory is reserved for the instance shared memory set, only about 21 MB is being used at the time db2mtrk is run.

Note: In some cases the size displayed by the db2mtrk tool will be larger than the value assigned to the configuration parameter. In such cases, the value assigned to the configuration parameter is used as a soft limit, and the pool's actual memory usage might grow beyond the configured size

DB2 記憶體查詢!!

休了三天的假，今早來上班就有新的知識收穫，覺得好舒服喔!!
真覺得古人說得好，人生之樂，樂如何?月滿窗前，草不除
別誤會，我不是懶惰，是學到好東西的快樂!!!!
節錄IBM depvelopwork

Memory utilization on Linux

Let's start out by discussing a few terms and concepts that are important to understand when discussing memory utilization. First is the concept of a 32-bit address space, as opposed to a 64-bit address space. Every byte in memory has to have an "address" for a process to be able to locate it. The list of these addresses is known as the "address space." A single bit has two possible values and could refer to two possible addresses -- 2 bytes; 32 bits have 2³² combinations and could address 4,294,967,296 bytes (4 GB), and 64 bits have 2⁶⁴ combinations and could address 16 exabytes. (32 bit 限制)

Most modern non-64-bit systems have the ability to create an address space of 36 bits, yielding 64 GB of addressable memory. However, for details not available here, this is not available to every individual process but to the Linux OS, which creates a mapping of these addresses to addresses assigned to a process. The process addresses are still limited to 32 bits, yielding address spaces for individual processes in differing areas of memory. This allows systems with greater than 4 GB of memory to be utilized by many processes. However, if two or more processes want to communicate using shared memory, they need to have the same address space, thus limiting the addresses they can utilize. Systems utilizing 64 bits for the address space have virtually no limits.

Linux, under normal circumstances, uses a system file cache to buffer, read, and write requests from disk. Linux performs this task fairly aggressively, and you will quickly see a running system have very little unused/free memory. Memory allocated to the file cache can be reduced and given to requesting processes, reducing the amount of data being buffered; however, read and write requests still pass through the smaller file cache. The entire amount of memory allocated to all processes, kernel, and file cache is your total working set. If your entire working set exceeds the amount of physical RAM in the system, then Linux can use disk space as a virtual extension to RAM, known as the swap space. Naturally, reads and writes to this area are far slower than between areas of memory.

Memory utilization examples

Let's look at a couple of examples of memory utilization using the Linux free command. The command displays the total amount of free, used physical, and swap memory in the system, as well as the buffers used by the kernel. We will be using the options -m, which displays the values in megabytes, and -o, which suppresses buffer information, allowing a more readable output. shows an example of the free command. The information displayed is as follows:

free -mo 的命令
du -h 查大小 好可息Aix 沒有提供 我查查

One tool to determine how much memory DB2 is using is db2mtrk, the DB2 memory tracker command. While this command has several options, we will only be looking at the -d option, which shows database level memory

The largest values are usually assigned to bph, the DB2 bufferpools. The output shows values for five bufferpools. In reality, the first bph represents the IBMDEFAULTBP, the only user defined bufferpool on the system. The other four bufferpools are "hidden" bufferpools, one for each DB2 page size (4 K, 8 K, 32 K, and 64 K) to ensure DB2 can run if the allocation of the user bufferpool fails. In this example, 201.3 MB have been allocated to the IBMDEFAULTBP, an over-allocation for a system with only 357 MB of memory. The DB2 memory tracker can display information on memory used by the instance, as well as DB2 agents.

本文原文節錄http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0509wright/

DB2 Q&A

DB2

Q1. What is a DB2 bind?

A1. A DB2 bind is a process that builds an access path to DB2 tables.

Q2. What is a DB2 access path?

A2. An access path is the method used to access data specified in DB2 sql statements.

Q3. What is a DB2 plan?

A3. An application plan or package is generated by the bind to define an access path.

Q4. What is normalization and what are the five normal forms?

A4. Normalization is a design procedure for representing data in tabular format. The five normal forms are progressive rules to represent the data with minimal redundancy.

Q5. What are foreign keys?

A5. These are attributes of one table that have matching values in a primary key in another table, allowing for relationships between tables.

Q6. Describe the elements of the SELECT query syntax.

A6. SELECT element FROM table WHERE conditional statement.

Q7. Explain the use of the WHERE clause.

A7. WHERE is used with a relational statement to isolate the object element or row.

Q8. What techniques are used to retrieve data from more than one table in a single SQL statement?

A8. Joins, unions and nested selects are used to retrieve data.

Q9. What do the initials DDL and DML stand for and what is their meaning?

A9. DDL is data definition language and DML is data manipulation language. DDL statements are CREATE, ALTER, TRUNCATE. DML statements are SELECT, INSERT, DELETE and UPDATE.

Q10. What is a view? Why use it?

A10. A view is a virtual table made up of data from base tables and other views, but not stored separately.

Q11. Explain an outer join.

A11. An outer join includes rows from tables when there are no matching values in the tables.

Q12. What is a subselect? Is it different from a nested select?

A12. A subselect is a select which works in conjunction with another select. A nested select is a kind of subselect where the inner select passes to the where criteria for the outer select.

Q13. What is the difference between group by and order by?

A13. Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.

Q14. Explain the EXPLAIN statement.

A14. The explain statement provides information about the optimizer's choice of access path of the sql.

Q15. What is tablespace?

A15. Tables are stored in tablespaces (hence the name)! There are three types of tablespaces: simple, segmented and partitioned.

Q16. What is a cursor and what is its function?

A16. An embedded sql statement may return a number of rows while the programming language can only access one row at a time. The programming device called a cursor controls the position of the row.

Q17. What is referential integrity?

A17. Referential integrity refers to the consistency that must be maintained between primary and foreign keys, ie every foreign key value must have a corresponding primary key value.

Q18. Usually, which is more important for DB2 system performance - CPU processing or I/O access?

A18. I/O operations are usually most critical for DB2 performance (or any other database for that matter).

Q19. Is there any advantage to denormalizing DB2 tables?

A19. Denormalizing DB2 tables reduces the need for processing intensive relational joins and reduces the number of foreign keys.

Q20. What is the database descriptor?

A20. The database descriptor, DBD is the DB2 component that limits access to the database whenever objects are created, altered or dropped.

Q21. What is lock contention?

A21. To maintain the integrity of DB2 objects the DBD permits access to only on object at a time. Lock contention happens if several objects are required by contending application processes simultaneously.

Q22. What is SPUFI?

A22. SPUFI stands for SQL processing using file input. It is the DB2 interactive menu-driven tool used by developers to create database objects.

Q23. What is the significance of DB2 free space and what parameters control it?

A23. The two parameters used in the CREATE statement are the PCTFREE which specifies the percentage of free space for each page and FREEPAGE which indicates the number of pages to be loaded with data between each free page. Free space allows room for the insertion of new rows.

Q24. What is a NULL value? What are the pros and cons of using NULLS?

A24. A NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value. It's the DB2 equivalent of TBD on an organizational chart and often correctly portrays a business situation. Unfortunately, it requires extra coding for an application program to handle this situation.

Q25. What is a synonym? How is it used?

A25. A synonym is used to reference a table or view by another name. The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated. The synonym is linked to the AUTHID that created it.

Q26. What is an alias and how does it differ from a synonym?

A26. An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view. The alias is not dropped when the table is dropped.

Q27. What is a LIKE table and how is it created?

A27. A LIKE table is created by using the LIKE parameter in a CREATE table statement. LIKE tables are typically created for a test environment from the production environment.

Q28. If the base table underlying a view is restructured, eg. attributes are added, does the application code accessing the view need to be redone?

A28. No. The table and its view are created anew, but the programs accessing the view do not need to be changed if the view and attributes accessed remain the same.

Q29. Under what circumstances will DB2 allow an SQL statement to update more than one primary key value at a time?

A29. Never. Such processing could produce duplicate values violating entity integrity. Primary keys must be updated one at a time.

Q30. What is the cascade rule and how does it relate to deletions made with a subselect.

A30. The cascade rule will not allow deletions based on a subselect that references the same table from which the deletions are being made.

Q31. What is the self-referencing constraint?

A31. The self-referencing constraint limits in a single table the changes to a primary key that the related foreign key defines. The foreign key in a self referencing table must specify the DELETE CASCADE rule.

Q32. What are delete-connected tables?

A32. Tables related with a foreign key are called delete-connected because a deletion in the primary key table can affect the contents of the foreign key table.

Q33. When can an insert of a new primary key value threaten referential integrity?

A33. Never. New primary key values are not a problem. However, the values of foreign key inserts must have corresponding primary key values in their related tables. And updates of primary key values may require changes in foreign key values to maintain referential integrity.

Q34. In terms of DB2 indexing, what is the root page?

A34. The simplest DB2 index is the B-tree and the B-tree's top page is called the root page. The root page entries represent the upper range limits of the index and are referenced first in a search.

Q35. How does Db2 use multiple table indexes?

A35. DB2 use the multiple indexes to satisfy multiple predicates in a SELECT statement that are joined by an AND or OR.

Q36. What are some characteristics of columns that benefit from indexes?

A36. Primary key and foreign key columns; columns that have unique values; columns that have aggregates computed frequently and columns used to test the existence of a value.

Q37. What is a composite index and how does it differ from a multiple index?

A37. A multiple index is not one index but two indexes for two different columns of a table. A composite index is one index made up of combined values from two columns in a table. If two columns in a table will often be accessed together a composite index will be efficient.

Q38. What is meant by index cardinality?

A38. The number of distinct values for a column is called index cardinality. DB2's RUNSTATS utility analyzes column value redundancy to determine whether to use a tablespace or index scan to search for data.

Q39. What is a clustered index?

A39. For a clustered index DB2 maintains rows in the same sequence as the columns in the index for as long as there is free space. DB2 can then process that table in that order efficiently.

Q40. What keyword does an SQL SELECT statement use for a string search?

A40. The LIKE keyword allows for string searches. The % sign is used as a wildcard.

Q41. What are some sql aggregates and other built-in functions?

A41. The common aggregate, built-in functions are AVG, SUM, MIN, MAX, COUNT and DISTINCT.

Q42. How is the SUBSTR keyword used in sql?

A42. SUBSTR is used for string manipulation with column name, first position and string length used as arguments. Eg. SUBSTR (NAME, 1 3) refers to the first three characters in the column NAME.

Q43. What are the three DB2 date and time data types and their associated functions?

A43. The three data types are DATE, TIME and TIMESTAMP. CHAR can be used to specify the format of each type. The DAYS function calculates the number of days between two dates. (It's Y2K compliant).

Q44. Explain transactions, commits and rollbacks in DB2.

A44. In DB2 a transaction typically requires a series of updates, insertions and deletions that represent a logical unit of work. A transaction puts an implicit lock on the DB2 data. Programmers can use the COMMIT WORK statement to terminate the transaction creating smaller units for recovery. If the transaction fails DB2 uses the log to roll back values to the start of the transaction or to the preceding commit point.

Q45. What is deadlock?

A45. Deadlock occurs when transactions executing at the same time lock each other out of data that they need to complete their logical units of work.

Q46. What are the four lockable units for DB2?

A46. DB2 imposes locks of four differing sizes: pages, tables, tablespace and for indexes subpage.

Q47. What are the three lock types?

A47. The three types are shared, update and exclusive. Shared locks allow two or more programs to read simultaneously but not change the locked space. An exclusive lock bars all other users from accessing the space. An update lock is less restrictive; it allows other transactions to read or acquire shared locks on the space.

Q48. What is isolation level?

A48. SQL statements may return any number of rows, but most host languages deal with one row at a time by declaring a cursor that presents each row at a unique isolation level.

Q49. What is an intent lock?

A49. An intent lock is at the table level for a segmented tablespace or at the tablespace level for a nonsegmented tablespace. They indicate at the table or tablespace level the kinds of locks at lower levels.

Q50. What is the difference between static and dynamic sql?

A50. Static sql is hard-coded in a program when the programmer knows the statements to be executed. For dynamic sql the program must dynamically allocate memory to receive the query results.

Q51. What is cursor stability?

A51. Cursor stability means that DB2 takes a lock on the page the cursor is accessing and releases the lock when the cursor moves to another page.

Q52. What is the significance of the CURSOR WITH HOLD clause in a cursor declaration?

A52. The clause avoids closing the cursor and repositioning it to the last row processed when the cursor is reopened.

Q53. What is the SQL Communications Area and what are some of its key fields?

A53. It is a data structure that must be included in any host-language program using SQL. It is used to pass feedback about the sql operations to the program. Fields are return codes, error messages, handling codes and warnings.

Q54. What is the purpose of the WHENEVER statement?

A54. The WHENEVER statement is coded once in the host program to control program actions depending on the SQL-CODE returned by each sql statement within the program.

Q55. What is DCLGEN?

A55. DCLGEN stands for declarations generator; it is a facility to generate DB2 sql data structures in COBOL or PL/I programs.

Q56. What is the FREE command?

A56. The FREE command can be used to delete plans and/or packages no longer needed.

Q57. DB2 can implement a join in three ways using a merge join, a nested join or a hybrid join. Explain the differences.

A57. A merge join requires that the tables being joined be in a sequence; the rows are retrieved with a high cluster ratio index or are sorted by DB2. A nested join does not require a sequence and works best on joining a small number of rows. DB2 reads the outer table values and each time scans the inner table for matches. The hybrid join is a nested join that requires the outer table be in sequence.

Q58. Compare a subselect to a join.

A58. Any subselect can be rewritten as a join, but not vice versa. Joins are usually more efficient as join rows can be returned immediately, subselects require a temporary work area for inner selects results while processing the outer select.

Q59. What is the difference between IN subselects and EXISTS subselect?

A59. If there is an index on the attributes tested an IN is more efficient since DB2 uses the index for the IN. (IN for index is the mnemonic).

Q60. What is a Cartesian product?

A60. A Cartesian product results from a faulty query. It is a row in the results for every combination in the join tables.

Q61. 4/99 Mail from Joseph Howard: 'Q: DB2 What is the difference between a package and a plan? How does one bind 2 versions of a CICS transaction with the same module name in two different CICS regions that share the same DB2 subsystem?

A61. Package and plan are usually used synonomously, as in this site. Both contain optimized code for SQL statements - a package for a single program, module or subroutine contained in the datebase request module (DBRM) library. A plan may contain multiple packages and pointers to packages. The one CICS module would then exist in a package that could be referenced in two different plans.

Q62. What is an asychronous write?

A62. It is a write to disk that may occur before or long after a commit. The write is controlled by the buffer manager.

Q63. What is a lock?

A63. A lock is the mechanism that controls access to data pages and tablespaces.

Q64. What is meant by isolation level?

A64. This is a key concept for any relational database. Isolation level is the manner in which locks are applied and released during a transaction. For DB@ a 'repeatable read' holds all locks untile the transaction completes or a syncpoint is issued. For transactions using 'cursor stability' the page lock releases are issued as the cursor 'moves', i.e. as the transaction releases addressability to the records.

Q65. What are leaf pages?

A65. They are the opposite of root pages. Leaf pages are the lowest level index pages - the pages that contain index entries and information to the corresponding table rows.

Q66. What is a precompiler?

A66. It is a DB2 facility for static SQL statements - it replaces these statements with calls to the DB2 language interface module.

Q67. What is a root page?

A67. The opposite of a leaf page; it is the highest level index page. An index can contain only the one root page; all other index pages are associated to the root.

Q68. What is a thread?

A68. A thread is the connection between DB2 and some other subsystem, such as CICS or IMS/DC.

Informix 25588問題?

這個問題，困擾了我好久，從原廠中的文件中看不出來有什麼問題，我想一定有很多使用informix的人跟我一樣，在Informix的onconfig 檔中，記錄了不同的nettype，在建立的初期需要作一定的區分，避免到時因為Thread的用盡造成25588的問題，不過我也有遇過， Infiormix 32 bit client 連 64bit server也會出現這個訊息
以下是我截取國外的訊息

In my programs wrote in ESQLC, error -25588 encountered.
> My program forks several child processes,db connection is established in
> every process and keep alive till end of process.
> When I fork more processes, certain process will fail to establish
> connection to informix server(IDS 7.31).
> Since my program deployed on the same computer with IDS, we use onipcshm as
> commumication protocol.If I change onipcshm to onsoctcp,no 25588 error
> occur.
> What's the reason?

Most likely, the NETTYPE configuration parameter in the ONCONFIG file
has too few users listed for the number of connections you're attempting
to make. The shared memory connections are fixed at start-up and can't
be changed without restarting the server. OTOH, if you run out of
network connections, IDS simply starts a new listener thread...

第ㄧ次使用

想了好久覺得，為什麼要用blog，我想應該是記錄一些東西，因為如果保留在個人電腦中，往往因為電腦的關係而不見，記錄在此是一個好注意


曦晨 chirstwang 94/10/3 啟用日