過了好久的年

N -POS 系統

N-POS 我開發了好一陣了,終於將一些技術上的問題排除了目前正開始設計相關功能與畫面與準備文件,管理系統好久了,終於可以寫一套自己發展的系統,我對它深具信心,一定可以重新E化POS系統的功能........

另外,昨天新學到一段SQL 適用於DB2 V8

Alter table XXXX activate not logged initially with empty table

很少人知道如此可快速清空TABLE,不過它有些限制
再創建TABLE時就必須於SQL DBSCHEMA 中指定才可

學寫POS 系統!!

做系統管理好久了，一直都在管理與建置系統，與設計系統架構

DB2 Backup and Restore or Restore other local

本文首先詳細地介紹IBM DB2的資料備份和資料恢復工具，向讀者展示IBM DB2的資料備份和資料恢復方法。同時還分析IBM DB2的資料備份和資料恢復性能方面的問題，以期能對讀者很好地利用IBM DB2快速高效地完成資料備份和恢復工作有所裨益。IBM DB2資料庫具有出眾的跨平臺開放性和可擴展性，它支持當前流行的主要操作平臺；IBM DB2資料庫具有良好的可攜性，能夠支援其他主流的資料庫，如Oracle、SyBase、Informix等資料庫都可以很容易地移植到DB2資料庫中；IBM DB2資料庫對海量資料的管理更是十分出色，它允許資料庫系統自己管理外存來提高效率，同時提供export、import、load等工具方便資料的快速移動，並提供了強大的backup、restore工具，方便DB2系統管理員創建安全的備份，建立恢復程式及執行快速、準確的恢復。相信我們都知道，維護企業資料的安全和完整是每個資料庫管理員的核心任務，而其中資料的及時備份和恢復又起著核心的作用。本文我們就IBM DB2強大的backup和restore工具做一下詳細的介紹，相信能夠對DB2系統管理員的日常資料管理有所幫助。
backup工具
backup用於DB2資料庫的資料備份操作，當你要對資料庫進行備份工作時，你首先要具有sysadm、sysctrl或者sysmaint的許可權。DB2採用的資料存儲策略在邏輯上分為三級：資料存儲在表（table）中，表存儲在表空間（tablespace）中，表空間存儲在資料庫（database）中，一個資料庫可以包含多個表空間，一個表空間可以包含多個表。因此，DB2的資料備份可以在資料庫和表空間兩個級別上進行。我們可以在命令中心、命令行處理器或者命令視窗中用backup命令來完成資料備份操作，其具體語法為：DB2 disconnect alias;DB2 backup database alias;例如，本例中系統的資料庫別名（alias）為edbase，所以其備份命令為：DB2 disconnect edbase；DB2 backup database edbase；其中第一條語句用來斷開資料庫與所有應用程式的連接，第二條語句執行backup操作，備份後可以在系統中的缺省目錄（例如，本例中該目錄是D:\IBM\SQLLIB\ EDBASE.0\DB2\NODE0000\CATN0000\20030514）下找到備份檔案，此目錄是由資料庫別名alias、instance名、節點名、cat node以及備份年月日組成，而備份檔案名是備份的時分秒。同樣，DB2也提供了簡單方便的圖形化用戶介面，在控制中心選擇目標資料庫或者表空間，單擊右鍵選擇backup選項，便可以根據備份嚮導完成備份操作。作為一款優秀的軟體，DB2還支援離線備份（offline backup）和線上備份（online backup）。
offline 資料備份offline資料備份是指當對資料進行備份時，資料庫必須斷開與其他程式的連接，此時，資料庫中的資料是靜止的，且在完成備份工作之前，資料庫不會回應應用程式的連接請求。offline資料備份要求資料庫的日誌方式是迴圈日誌（circular logging），默認情況下系統採用的是迴圈日誌，因此，默認情況下資料庫的備份方式也是offline備份。在特殊情況下我們要求offline備份時，必須修改資料庫配置檔（database configure）的日誌參數部分將LOG_RETAIN參數的值設為“否”。


online資料備份online資料備份是指當進行資料備份時，資料庫仍然保持與應用程式的連接，且在完成資料備份工作的過程中，資料庫繼續回應其他應用程式的連接請求，因此，資料庫中的資料仍在即時更新，顯然，online資料備份能夠提高系統的並行能力。當要進行online資料備份時，資料庫系統的日誌方式一定要是歸檔日誌（archive logging），因此，我們必須對資料庫配置檔的日誌部分的參數進行修改，將其LOG_RETAIN參數值設為“是”，USER_EXIT參數值設為“是”。


restore工具
restore用於DB2資料庫的資料恢復操作，同樣，當你要對資料庫進行恢復操作時，你應該具有sysadm、sysctrl或者sysmaint許可權。如果進行本機資料恢復，比較簡單，我們同樣可以在命令中心、命令行處理器或者命令視窗中通過執行restore命令完成，其語法為：DB2 restore database edbase;我們也可以運用DB2提供的圖形用戶介面來方便的完成，由於比較簡單，在此我們不作介紹。如果要把備份的資料庫恢復到另外一台機器上，就需要幾個額外的步驟了，因為目的機器上可能沒有源資料庫所使用的表空間等環境。因此首先要查看源資料庫的表空間結構：connect to edbase; list tablespaces ;對找到的表空間標識（id=0,1,2...)進行以下操作：list tablespace containers for id;這樣就可以開始恢復工作了，其具體步驟如下：1）執行命令restore database edbase redirect 其中redirect參數要求重新映射表空間。2）對找到的各個表空間依次運行如下的命令： set tablespace containers for id (path "absolute_container_path" )如果是檔型表空間則使用如下命令：set tablespace containers for id (file "absolute_container_filename", page_ number) 其中page_number是頁數，其具體的數值可以通過list tablespaces show detail得到。3）最後運行如下命令： restore database edbase continue重新恢復一下資料庫。有必要說明的是資料恢復在資料庫（database）級和表空間（tablespace）級對資料庫處理應用程式的連接會產生不同的影響。當對整個資料庫進行恢復操作的時候，它要求資料庫必須斷開與其他應用程式的連接，在進行資料恢復的過程中，資料庫將不會回應其他應用程式的連接請求；當進行表空間級的資料恢復時，資料庫允許應用程式訪問其他的表空間。
在backup/restore時的性能考慮
為了資料的安全，我們需要經常的對資料進行備份和恢復操作，而經常的資料備份和資料恢復在資料量較大的情況下將會嚴重影響資料庫的性能，降低工作效率，因此我們在資料備份和恢復的時候，還得採取一定的策略，以達到高效的目的。考慮表空間級的資料備份和恢復操作
圖2
對於LOB（large objects）資料，在進行資料庫的創建時，一般應放在單獨的表空間中。由於LOB資料的特性，LOB資料的備份和恢復將會比常規資料佔用更多的時間，因此一般情況下我們是不會對LOB資料進行頻繁的備份和恢復的，當把LOB資料放在單獨的表空間後，我們便可以選擇表空間級的操作，備份和恢復重要資料時，跳過對LOB資料的備份和恢復，當系統空閒的時候，再對存儲LOB資料的表空間進行備份和恢復操作。對於需要頻繁備份和恢復的資料，我們可以將它們放在磁片訪問速度快的表空間中，這樣對此部分資料的備份和恢復操作時也將會大大減少機時，提高工作效率。考慮buffer 的大小和並行度在資料備份的時候，我們可以通過給並行度、緩衝區數目和緩衝區大小適當的值來提高備份的性能。如下圖所示（如圖2）。增加並行度和緩衝區的數目，提高緩衝區的大小，將會提高資料備份的性能，但是對整個電腦硬體系統也將會有更高的要求。必須注意的是，當沒有足夠的記憶體分配給緩衝區時，資料備份將無法進行，系統會顯示錯誤，理想的緩衝區大小應該是記憶體的整數倍（該值可以在資料庫配置檔中的DFT_EXTENT_SZ找到），而理想的緩衝區數目應該至少是I/O緩衝區數目的兩倍。對於資料的恢復我們也應有同樣的考慮。

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/