728x90

시스템

AIX 7100-02-02-1316 , "DB2 v10.5.0.3", "s140203"

AIX 7100-02-05-1415 , "DB2 v10.5.0.4", "special_32990


환경변수

(locale 관)

LANG=ko_KR.UTF-8

LC_COLLATE="ko_KR.UTF-8"

LC_CTYPE="ko_KR.UTF-8"

LC_MONETARY="ko_KR.UTF-8"

LC_NUMERIC="ko_KR.UTF-8"

LC_TIME="ko_KR.UTF-8"

LC_MESSAGES="ko_KR.UTF-8"

LC_ALL=


(terminal 관련)

TERM=xterm



위와 같은 환경에서 db2top을 실행했을 때 화면이 어그러지는 현상이 발생합니다.

환경변수 LANG을 en_US으로, TERM을 xterm, dtterm 등으로 설정했을 때 정상적으로 표시되었습니다.


IBM APAR에는 9.5버전에 대한 이슈만 나와있는데 10.5에서도 마찬가지인 것으로 보입니다.



http://www-01.ibm.com/support/docview.wss?uid=swg1IC73092

http://db2commerce.com/2014/07/01/scrambled-output-from-db2top/

http://www-01.ibm.com/support/docview.wss?uid=swg21458782

728x90
728x90

Technote (FAQ)


Question

What is the recommended process for upgrading to a new Technology Level or Service Pack in AIX ?

Answer

-- Updating to a New Technology Level or Service Pack --


Update_All in 5.3, 6.1, and AIX 7

This document describes the recommended preparation and process when considering updating your system to a new technology level or adding a service pack to an existing technology level. In all we will review some key words and terminology, run through recommended pre-checks, discuss the update_all process using both SMIT and command line, and finally post-checks & FAQ. 

Key Words / Terminology : 
V.R.M.F(Version, Release, Maintenance Level, Fix Level) 
This represents your operating system level and is mentioned because of the change made in the 5300-07 technology level update. Previous to 5300-07 updates are recognized by their last numerical level entry only. 
Ex. 
bos.rte.install 5.3.0.60 

Beginning with 5300-07 the 3rd number, or “Maintenance Level” number will also be included and will represent the Technology level of the fileset, followed by the “Fix Level”. 
Ex. 
bos.rte.install 5.3.7.0 
*Note that the “Fix Level” entry does not necessarily represent the “Service Pack” level. Any similarity is simply coincidental. 

Oslevel 
You will also notice a change to how the operating system level reads. With the introduction of 5300-06 you will notice more information provided when running the ‘oslevel’ command. 

# oslevel -s 
6100-06-06-1140 

The addition of the last four digits simply represents the year (11) and week (40) of the release of your technology level/service pack (-06-06). 

Update_all 
This is the recommended method of installation when upgrading your technology level or service pack level only. This installation method should not be used when attempting to upgrade the version or release level of your operating system. 
Ex. 
5300-0x to 6100-0x - do NOT use update_all 
6100-0x to 7100-0x - do NOT use update_all 



Recommended Pre-Checks : 
1. The back-out 
It is recommended to have at least one back-out method available to you in case a restore is required. Recommended back out methods include : mksysb restore, sysback restore, altinst_rootvg clone, and multibos. More information on any of these back-out methods can be found at the publib documentation site by using this link to click through to the appropriate infocenter and search features found here : 

http://publib16.boulder.ibm.com/pseries/ 

2. Boot Image Verification 
The hd5 logical volume holds your boot image. There are a few checks to make against your boot image before starting your update_all process. 
First, find out which disk contains your hd5 logical volume. 

# lslv -m hd5 
hd5:N/A 
LP PP1 PV1 
0001 0001 hdisk0 

The listing under the “PV1” column indicates on which disk hd5 is located. Any other entries under “PV2”..etc, simply represent mirrored copies. 

Next, verify your boot image can be successfully recreated using the hdisk# from above and by using /dev/ipldevice. 

# bosboot -ad /dev/hdisk0 
bosboot: Boot image is 35795 512 byte blocks. 

# bosboot -ad /dev/ipldevice 
bosboot: Boot image is 35795 512 byte blocks. 

If either of these two commands fail for any reason, please call the support center for resolution before proceeding. 

Finally, with the continual introduction of new devices and hardware the boot images are growing larger. You will want to make sure your hd5 logical volume has enough free and contiguous space to hold the boot image. Currently the recommended allocated space to have for hd5 is 32meg. This is of concern in environments with smaller disk sizes, and should be checked before running an update. 

# lsvg -l rootvg |grep hd5 
hd5 boot 1 1 closed/syncd N/A 

# lsvg rootvg |grep SIZE 
VG STATE: active PP SIZE: 32 megabyte(s) 

The single partition (in bold (1)) is 32meg in size. This should be enough to contain the boot image. Smaller partition sizes will require more partitions to be allocated. 

Your hd5 partitions also need to be contiguous partitions. Check this by running the following command : 
# lspv -M hdisk0 |grep hd5 
hdisk0:1 hd5:1 
hdisk0:2 hd5:2 
hdisk0:3 hd5:3 

You can see in this example that the hd5 logical volume covers the first 3 partitions on the disk and they are all contiguous. If your partitions are not contiguous, or are not covered on the first partitions of the disk please call the software support line for assistance with correcting this. 
Again, you may only have 1 partition that is large enough to handle the boot image, or you may have multiple smaller partitions. Either is fine. 

3. Firmware 
Firmware should be kept up to date and be checked whenever a technology level update is considered. In general firmware updates should be applied before software updates, but that is not a rule set in stone. You should always refer to the firmware download site installation information and follow those instructions. 
You can access the firmware download site here : 
http://www-933.ibm.com/support/fixcentral/ 

If there are any questions concerning the firmware update, installation instructions, or if there are software considerations please contact the hardware support center first. 

4. Fileset Consistency 
You should run the following command to check fileset consistency : 
# lppchk -v 

Ideally this should return to the command line with no output. If you do receive output and are unfamiliar with how to resolve it, please call the support center for assistance before running your upgrade. 

5. Space 
In order to see if you have enough space for your update you can run the update_all operation in preview mode. This will provide you with an estimated system resources listing of the necessary space. In order to do this you will first have to install the fileset “bos.rte.install”. Following are two examples of how to update this fileset first, so a preview update_all can be executed. The example below shows initially “committing” the bos.rte.install update. If you would feel more comfortable using the “apply” feature in this case, feel free to do so. Note that if you do “Apply” you will need to “Commit” before continuing with the actual update_all process. 

From an unmounted cdrom device (cd0). 
# installp -acd /dev/cd0 bos.rte.install 

From a download directory (/fixes/6100-06) 
# installp -acd /fixes/6100-06 bos.rte.install 

Using SMITTY 
# smitty install_all 
* INPUT device / directory for software /dev/cd0 
* SOFTWARE to install [bos.rte.install] 
PREVIEW only? (install operation will NOT occur) no 

Once the installation is complete you will then be able to run the preview operation on the remaining filesets to get an estimate of the required space for your update. 

# smitty update_all 
* INPUT device / directory for software /dev/cd0 
* SOFTWARE to update _update_all 
PREVIEW only? (update operation will NOT occur) yes 


The preview will show something similar to this in regards to the space requirements : 
RESOURCES 
--------------- 
Estimated system resource requirements for filesets being installed: 
(All sizes are in 512-byte blocks) 


Filesystem
Needed Space
Free Space
/
402
239312
/usr
94352
165440
/var
1
237384
/tmp
52304
259240
/opt
6648
158576
Total
153707
1059952

Running the update using SMIT or command line : 
This section will cover the update_all process. In all cases the “input device” (location of the software) will be referred to as being a local cdrom drive (/dev/cd0). You can always use a download directory or nfs mount point, simply substitute the input device as necessary. This process is also executed presuming you are logged in as root (as opposed to using ‘sudo’). 

1. SMITTY : 
By menu selection options you would take the following path : 
# smitty 
Software Installation and Maintenance 
Install and Update Software 
Update Installed Software to Latest Level (Update All) 

-or use the fastpath- 

# smitty update_all 

INPUT device / directory for software /dev/cd0 
SOFTWARE to update _update_all 
PREVIEW only? (update operation will NOT occur) no 
COMMIT software updates? yes 
SAVE replaced files? no 
AUTOMATICALLY install requisite software? yes 
EXTEND file systems if space needed? yes 
VERIFY install and check file sizes? no 
DETAILED output? no 
Process multiple volumes? yes 
ACCEPT new license agreements? yes
Preview new LICENSE agreements? No 

The only required change would be the option for “ACCEPT new license agreements”. All other given defaults should remain as they are. Pressing <Enter> here will bring up a confirmation dialog box to which you press <Enter> again. The only other interaction you may have is if you need to switch out a volume of the media package. When complete, the upper left corner “Command:” field will have one of two outcomes : 

OK : Your installation has completed without error. You can either scroll down or exit out and view the /smit.log file for the log of the installation. 

FAILED : one or more filesets failed for some reason. You can either scroll down or exit out and view the /smit.log file for the log of the installation. Corrective action should be taken before rebooting. 

2. Command line : 
You will use the ‘install_all_updates’ command if opting to run this from command line. 
This command does have a representative manpage so feel free to review the flags, however this command was intended to be easily executed so running a basic update will not be a very long command. 

# install_all_updates -Y -cd /dev/cd0 

Once complete you will find the log of the installation in /var/adm/ras/install_all_updates.log. By default the fileset updates will be installed in the “APPLIED” state. The "-c" flag has been added to the above command to specify that all filesets install in the “COMMITTED” state. See the FAQ at the end of this document for more information concerning “APPLIED” vs “COMMITTED”. 



Post checks : 
Presuming your update_all was successful you will want to check the following commands. If you receive unexpected output please contact the support center for assistance. 
* Please see FAQ statement 11 and 11a below regarding post-update_all issues. 

# oslevel -r 
This should return with the expected TL output 

# oslevel -s 
This should return with the expected TL and SP output. 

# lppchk -v 
This should come back ideally with no output. 


FAQ and Opinions
This section is contains the most commonly asked questions and a few recommendations concerning the update_all. Most answers will be kept short and to the point (as much as possible). 

1. Is it okay to install my Technology Level in the “APPLIED” state ? What if I want to reject the TL update if there is a problem ? 
With the introduction of Technology Levels and the “all or nothing” packaging format, these updates are bringing in on the upwards of 400+ fileset updates for each TL. Attempting to perform a “reject” process on so much code simply does not work well, and is not supported. Recommended back-out methods are discussed earlier in this document. 

1a. Does the same hold true for Service Packs ? 
The Service Pack updates are certainly much smaller groups of updates....typically numbering around 40-50 per update. While you certainly will have a better chance of successfully rejecting 40 filesets instead of 400, it would still be best to have one of the back-out methods mentioned earlier. 

2. Why does my update_all have problems when I use an NFS mounted cdrom ? 
When running an update_all over NFS, you create your own mount point for the cdrom drive. This does not allow it to recognize the fact that there are multiple volumes available that contain the correct requisite filesets. Multiple update_all operations may be needed on each volume when NFS mounting a cdrom drive. A better option may be to ‘bffcreate’ the contents of all CDs down to a directory, then NFS mount that directory. 

3. The update_all failed. What now ? 
Do not reboot. You can review the log to find out what failed and why. If you are comfortable and familiar with these situations you can correct the failures and re-update the filesets. If not, please call the helpdesk and open a PMR for assistance. Have the log available to email in if necessary. 

4. I need to run my update today but I may not be able to reboot until next week. Is that a problem ? 
Plans should be made to reboot as soon as the update is complete and checks have been made to ensure there were no failures. System files will have been replaced, but the corresponding kernel and library updates will not be loaded until boot time. You will likely encounter problems if you delay rebooting. 

5. Is it recommended to reboot before issuing a TL upgrade ? 
If this is possible, absolutely. There are systems out there that have not been rebooted in a year or more. Who is to say that something has not happened in that time that simply would not show up until a reboot. Rebooting the system first assures a good boot image, a stable system, and would isolate any problem that normally would not be caught until the post-update reboot as either a preexisting issue, or an issue directly related to the TL update itself. 

6. Some say to use base AIX install media when updating the TL, others say the TL fix downloads or CDs should be used. Which is right ? 
The recommendation is to use the TL fix downloads from FixCentral, or the TL CDs that can be ordered either online or from AIX SupportLine. You can also use the base AIX installation media, however without getting into a long answer, the recommendation is using the TL fix packages. 

7. How long will the update_all take ? 
This is a question that does not quite have a definite answer. The length of time required for the update_all depends on how many filesets are being updated as well as the available system resources such as processors and memory. 
Giving a ballpark figure however, going up one TL should only take about 30-60 minutes plus reboot time. Some admins prefer to be more on the conservative side and block a 2-3 hour downtime. Consideration should also be made to the amount of time it would take to restore, depending on the backup method selected. 

8. Is it okay to run the update_all while people are online ? 
Updating could affect running processes. As such, applications should be down and users offline as a general rule. 

9. Where can I download new service packs or technology levels ? 
TL and SP updates can be acquired from the FixCentral website located here : 
http://www-933.ibm.com/support/fixcentral/ 

10. Will product X at level Y.Z work with my new technology level ? 
Some products may only be certified up to a certain operating system level while other products may require an update. The best thing to do would be to contactproduct X's support center. If it is an IBM product feel free to contact our support center and open a PMR requesting to speak to the product X team. Any 3rd party products should be cleared by their support before upgrading. 

11. The 'oslevel -s' and/or 'oslevel -r' commands do not report what I expect after the udpate. How can I determine what is missing ? 
If your 'oslevel' commands do not report correctly after your update_all you can add the "-l" flag to determine which filesets still need to be updated in order to complete your upgrade. 
The syntax : 
# oslevel -rl <level> 
# oslevel -sl <level> 

Example : 
# oslevel -rl 6100-01 
-or- 
# oslevel -sl 6100-00-01-0748 

The filesets listed will show an "Actual Level" heading (your current level) and a "Maintenance Level" heading (the level you need to be at to satisfy the TL/SP). 

11a. Some filesets occasionally require multiple update_all operations. 
There are cases where a second update_all operations need to be executed to pick up the full TL update. This is not defective behavior. Please also take note that this tip is not intended to resolve a "FAILED" status of your update_all. If you notice that your TL update was successful, but some filesets did not get updated andare on your media or in your download location, you may need to run the update_all a second time to pick them up. 

12. What are other best practices with regards to keeping the AIX operating system updated? 
IBM maintains a best practices site for service and support for Power Systems. 
Visit http://www.ibm.com/support/customercare/sas/f/best for more information. 



http://www-01.ibm.com/support/docview.wss?uid=isg3T1010755

728x90
728x90

아래 내용은 DB2 10.5 환경의 UTF-8 코드셋 데이터베이스를 기준으로 합니다.

(eucKR 코드셋에서도 비슷하게 작동할 것으로 보입니다만 테스트가 필요합니다)

DB2에서 멀티바이트 문자에 LENGTH 함수를 사용하면 바이트 값을 리턴합니다.


UTF-8 인코딩에서 한글은 일반적으로 3바이트로 표현됩니다. (가 : ea b0 80)

DB2의 LENGTH 함수에서는 3을 리턴합니다.


10.5버전부터 LENGTH2, LENGTH4 와 같은 스칼라 함수를 지원합니다.


$ db2 "values length('가')"


1

-----------

          3


  1 record(s) selected.


$ db2 "values length2('가')"


1

-----------

          1


  1 record(s) selected.


$ db2 "values length4('가')"


1

-----------

          1


위와 같이 DB2의 LENGTH 함수 실행결과에서는 순수하게 바이트 길이를 표시해주고 length2 와 length4는 멀티바이트에 대응한 글자길이를 보여주게 됩니다.10.5 이전 버전에서는 character_length (또는 length) 함수에서 codeunits16, codeunits32 와 같은 인자값을 지정하여 사용했습니다. 유니코드의 경우 보통 3바이트로 표현하나 4바이트로 표현하는 문자도 아주 간혹 있어서 이같은 경우에는 codeunit32를 사용할 필요도 있습니다. 그러나 한글의 경우에는 4바이트로 표현하는 예는 없는 것 같습니다. 이에 대해 아시는 바가 있다면 알려주시기를 부탁드립니다.


참조. 오라클 작동방식 (11.2 버전 기준)

오라클의 경우 LENGTH 함수 실행결과 글자길이값을 보여줍니다. 오라클에서도 LENGTH2, LENGTH4 함수가 있는데 역시 DB2와 동일하게 작동합니다. 원래는 ORACLE함수이나 DB2에서도 호환성 측면에서 추가된 것으로 보입니다. 유니코드 캐릭터셋의 DB에서는 LENGTH4 함수가 LENGTH와 동일한 결과값을 보여줍니다. (기타 캐릭터셋에서는 확인이 필요합니다)


# export LANG=KO_KR.UTF-8

# export NLS_LANG=NLS_LANG=AMERICAN_AMERICA.AL32UTF8

# sqlplus scott/tiger


SQL> create table utftest (a varchar(100));


Table created.


SQL> insert into utftest values ('한?글');


1 row created.


SQL> select * from utftest;


A

--------------------------------------------------------------------------------

한?글


SQL> select length(a) from utftest;


 LENGTH(A)

----------

         3


SQL> select length2(a) from utftest;


LENGTH2(A)

----------

         4


SQL>  select length4(a) from utftest;


LENGTH4(A)

----------

         3


SQL> insert into utftest values ('a한?글b');


1 row created.


SQL> select * from utftest;


A

--------------------------------------------------------------------------------

한?글

a한?글b


SQL> select length(a) from utftest;


 LENGTH(A)

----------

         3

         5


SQL> select length2(a) from utftest;


LENGTH2(A)

----------

         4

         6


SQL> select length4(a) from utftest;


LENGTH4(A)

----------

         3

         5



참조

http://www.utf8-chartable.de/unicode-utf8-table.pl

http://www.ibm.com/developerworks/data/library/techarticle/dm-0705nair/

728x90
728x90

Problem(Abstract)

A remote query against the Optim Connect Server running on AIX returns "[C012] Invalid username/password", even though the User ID and password are valid.

Environment

AIX 6.x

Optim Connect 5.3.2 with Workspace security enabled


Diagnosing the problem

Before contacting support, please confirm the following:

1) The ability to query the data source without security enabled (anonymously)

2) The user id that is running the Optim Connect Server, irpcd daemon, has the ability to validate local user accounts on the AIX server.


Resolving the problem

Please contact Optim Support for an update for extended password security on AIX 6.x.

Product Alias/Synonym

Attunity Optim Connect Server


http://www-01.ibm.com/support/docview.wss?uid=swg21640946

728x90
728x90


질문

Smart blobspace는 Blob data와 다른 특징들을 가지고 있다, 해당 특징을 잘 이해한다면, 효율적으로 데이타를 관리할수 있을것이다.

응답

아래의 내용은 Smart Blob space에 대한 특징을 전체적으로 정리한 내용이다. Smart blobspace의 특성에 대하여 이해를 하고, 이를 효율적으로 사용하게 하는데 목적이 있다.
참고로, 아래의 내용은 IDS11.5기준으로 작성된 내용이며, 자세한 내용은 버젼에 따라 차이점이 있을수 있습니다.

1. Sbspace의 특징

Smart blob 데이타는 일반 데이타의 저장방법과 다른 방법으로 저장 및 조회가 된다.

Smart Large Object 사용과 관련하여, Sbspace를 사용하는 경우, Sbspace에는 아래와 같이 3가지의 데이타 영역을 가지고 있다.

1) Meta Data area : sbspace 생성시 Meta데이타 영역의 크기를 지정하여 줄수 있다(또는 default 생성). LO데이타에 대한 Accesstime, update된 내용등이 로깅된다.

2) User Data area : 각각의 LO데이타 하나가 하나의 extent와 같은 개념. - 하나의 LO데이타를 저장하기 위한 page의 set으로 구성된다.

3) Reserved area : Sbspace에 청크가 추가될때/생성될때, User Data영역의 40%를 reserved 영역으로 설정하게 된다. 이는 Sbspace영역을 효율적으로 관리/사용하기 위한 방법으로서, 해당 청크의 Meta데이타 또는 User데이타 영역이 full이 된경우에, reserved 영역의 일부를 Meta데이타영역, 또는 User데이타영역으로 전환하여 사용한다. 이는, 기존, Meta데이타와, User데이타 영역으로만 구분되어 있을때, 어는 한쪽이 먼저 full이 되는경우, 나머지 영역의 free영역을 사용할수 없게 됨으로써, 디스크영역을 효율적으로 사용하지 못한점을 개선하여, 어느쪽에서 속하지 않은 영역을 미리 확보하여, 시스템 구조상 많이 사용하게 되는 데이타 영역을 확장하여 줌으로써, 최대한 디스크영역을 효율적으로 사용할수 있게 하였다.


다음은 Sbspace의 Meta Data의 구조에 대한 설명이다. 아래와 같은 데이타들이 default로 저장이 된다.

- sbspace descriptor tblspace : 최초 생성이후 증가하지 않음
- chunk adjust tblspace : sbsp에 chunk가 추가되는 경우에 증가됨
- Level-1 archive tblspace
- Level-2 archive tblspace
- Chunk LO header tblspace : 청크가 추가될때 증가됨
- Chunk user-data free-list tblspace : chunk가 많이 쪼개질수록, 증가한다 ( 즉, 작은 크기의 sblob가 많이 저장될수록 많이 증가하게된다 )


아래는 sbspace에 대하여 'oncheck -pe' 결과이다.

-----------------------------------------------------------------------------
% oncheck -pe mysbsp


DBspace Usage Report: mysbsp Owner: informix Created: 09/23/2012


Chunk Pathname Pagesize(k) Size(p) Used(p) Free(p)
2 /myids/chunk/sdatadbs.115 4 25000 see below see below

Description Offset(p) Size(p)
------------------------------------------ -------- --------
RESERVED PAGES 0 2
CHUNK FREELIST PAGE 2 1
sbsp:'informix'.TBLSpace 3 50
SBLOBSpace LO [2,2,1] 53 100
SBLOBSpace LO [2,2,2] 153 20
SBLOBSpace LO [2,2,3] 173 100
SBLOBSpace LO [2,2,4] 273 20
SBLOBSpace LO [2,2,5] 293 100
SBLOBSpace LO [2,2,6] 393 20
SBLOBSpace LO [2,2,7] 413 100
SBLOBSpace LO [2,2,8] 513 20
SBLOBSpace LO [2,2,9] 533 100
SBLOBSpace LO [2,2,10] 633 20
SBLOBSpace FREE USER DATA (AREA 1) 653 6049
SBLOBSpace RESERVED USER DATA (AREA 1) 6702 4988
sbsp:'informix'.sbspace_desc 11690 4
sbsp:'informix'.chunk_adjunc 11694 4
sbsp:'informix'.LO_ud_free 11698 32
sbsp:'informix'.LO_hdr_partn 11730 557
SBLOBSpace FREE META DATA 12287 1076
SBLOBSpace RESERVED USER DATA (AREA 2) 13363 4988
SBLOBSpace FREE USER DATA (AREA 2) 18351 6649

Total Used: 1250
Total SBLOBSpace FREE META DATA: 1076
Total SBLOBSpace FREE USER DATA: 22674
----------------------------------------------
파란색은, user data관련된 영역
녹색은, Meta data관련된 영역
빨강색은 Reserved 데이타 영역입니다.
-------------------------------------------------------------------------------------
일반적으로 초기에 할당된 Meta데이타영역을 다 소진하고, 추가로 할당되는 경우에는, 내부적으로 시스템이 그 크기를 결정하게 되므로, 임의로 증가하는 크기를 지정하여 줄수 없다.

참고 : Structure of the Meta Data
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.adref.doc/ids_adr_0337.htm?resultof=%22%6d%65%74%61%64%61%74%61%22%20



2. Smart blob 및 이의 Meta Data에 모니터링 방법

위의 'oncheck -pe'의 결과에서 Meta 데이타의 가용영역을 확인하기 위하여는 "FREE META DATA" 크기와, "RESERVED USER DATA (AREA 1/2)" 가 얼마나 남아있는지가 중요한 요소가 된다. 이에 대한 값을 확인후, 여유가 없는 경우에는 청크를 추가하여 주어야 한다.

참고 : Sbspace 모니터링
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.admin.doc/ids_admin_0637.htm


3. sbspace와 관련하여, online.log에 표시되는 메세지

Sbspace의 Metadata 관련하여 아래와 같은 메세지가 MSGPATH로그에 표시될수 있다. 
위에서 설명한 바와 같이, sbspace의 영역을 효율적으로 관리하기 위하여 선할당 되었던, reserved 영역이, Meta데이타 또는 User데이타 영역으로 할당 또는 free되는 과정에서 online.log에 아래와 같은 메세지들이 로깅될수 있습니다.

1) Allocated number pages to Metadata from chunk number.
2) Allocated number pages to Userdata from chunk number.
3) Freeing reserved space from chunk number to Metadata.
4) Freeing reserved space from chunk number to Userdata.

Freeing reserved space from chunk number to Userdata.
Cause : The user-data area in chunk number is full. The database server is trying to free space from the reserved area to the user-data area.
Action : None required.


참고 : Sbspace Metadata Messages.
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.adref.doc/ids_adr_0716.htm?resultof=%22%6d%65%74%61%64%61%74%61%22%20


http://www-01.ibm.com/support/docview.wss?uid=swg21631385&myns=swgimgmt&mynp=OCSSGU8G&mync=E

728x90
728x90


Question

How to perform a dummy update on a table to remove any inplace alters.

Answer

UPDATE tab1 SET col1=col1 WHERE 1=1 ; The table name in this example is tab1, the column name is col1. one restriction is that the column chosen must be numeric.


http://www-01.ibm.com/support/docview.wss?uid=swg21674841&myns=swgimgmt&mynp=OCSSGU8G&mync=E

728x90
728x90


Problem(Abstract)

You have multiple tables in your database that have changed extensively and you are experiencing a performance degradation. Reorg and runstats are recommended on these tables. You can create reorg and runstats scripts to make DB2® performance tuning easy.

Resolving the problem

To generate and run reorg and runstats scripts:

  1. Log in as the DB2 administrator, and connect to the database.

  2. To create a script for reorg table, run the following command on one line:
    db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG TABLE /g' | sed 's/$/;/g' > /tmp/reorg_tbl.ddl

  3. To create a script for reorg index, run the following command on one line:
    db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG INDEXES ALL FOR TABLE /g' | sed 's/$/;/g' > /tmp/reorg_idx.ddl

  4. To create a script for runstats, run the following command on one line:
    db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/RUNSTATS on TABLE /g' | sed 's/$/ WITH DISTRIBUTION AND DETAILED INDEXES ALL;/g' > /tmp/runstats_tbl.ddl

  5. Run reorg against tables and indexes, then runstats by entering the following lines:
    db2 -tvf /tmp/reorg_tbl.ddl
    db2 -tvf /tmp/reorg_idx.ddl
    db2 -tvf /tmp/runstats_tbl.ddl

  6. Ignore the following message if you receive it: 
    SQL2212N The specified table is a view. The Reorganize Table utility cannot be run against a view.

  7. To capture the message printed on screen, you can pipe the standard output into a file, such as with the following command:
    db2 -tvf /tmp/runstats_tbl.ddl > filename.log 2>&1

    NOTE: The commands in step 2, 3 and 4 will not run on windows system.


The following method can also be used to reorg all the tables in a database on Linux, Unix and Windows. 
  1. Log in as the DB2 administrator, and connect to the database.
  2. To create a script for reorg table, run the following command on one line:

    db2 -x "select 'REORG TABLE ' ||rtrim (tabschema)|| '.' || rtrim (tabname) || ' ;' from syscat.tables where tabschema not like 'SYS%' and type = 'T'" > reorg.out
  3. Run reorg against tables entering the following line:
    db2 -tvf reorg.out
    NOTE: The where clause in step 2 can be altered to include other tables in the reorg.out script. 
    It is recommended to runstats on any table that has been reorged. 
    Also, these commands may not work on all versions and levels of Unix and Linux.


http://www-01.ibm.com/support/docview.wss?uid=swg21444174&myns=swgimgmt&mynp=OCSSEPGG&mync=E

728x90
728x90

Question

High availability replication (HDR) requires a trust relationship for user informix between the machines in the HDR pair. Is there an alternative to using hosts.equiv for establishing the trust?

Answer

You can use the .netrc file to establish trust between computers. Locate the file in the informix user home directory on both machines. It should have a line like the following:

    machine machine_name login informix password informix_password 
      machine_name 
      the name of the remote machine in the HDR pair. 

      informix_password 
      the password for user informix on the remote machine.


Caution: Be sure to set appropriate permissions on the .netrc file to protect the password.



http://www-01.ibm.com/support/docview.wss?uid=swg21195877

728x90
728x90


Question

Is it possible to prevent incoming database-connection attempts from application layer/driver from automatically activating the database.

Cause

Often, during maintenance events, a database is deactivated and the database-instance is stopped but the application is not stopped and will continue to attempt to establish a connection to the database while the database is under maintenance, this is not a problem in itself.
Later after maintenance completes and the database-instance is restarted and the database is activated, the application's attempt to establish a database connection will be successful.

However, there is a timing window where, after maintenance completes and the database-instance is started but before the database is activated, an application's database connection attempt can come in and automatically start database activation before a DBA has explicitly activated it. Since database activation can sometimes take several minutes, the application might timeout before the activation completes, and rollback the database activation. If the number of application threads attempting to connect to the database is very large, they may repeatedly start database activation ahead of the DBA's request to explicitly activate the database, and repeatedly timeout and rollback the activation. 
This can cause a repeated loop of automatic database activation and timeout/rollback, while the DBA's explicit request to activate the database appears to hang.

Answer

In order to prevent this kind of phenomenon, we can temporarily pause application database connection attempts from reaching the database server and automatically starting database activation, so that the DBA's request to explicitly activate the database can complete.


There are two methods that can be used:

Method #1: use the db2trc -suspend option to pause incoming database connections from reaching the database server, and then un-pause them after the DBA has completed database activation. (note that this method does not actually perform a trace, so it does not have any performance impact).

1) db2trc on -debug DB2.SQLCC.sqlcctcpconnmgr_child.115 -suspend 
2) db2start 
3) db2 activate database <dbname>
4) db2trc off 


Method #2: use the 'db2start admin mode restricted access' command to quiesce the database in restricted mode during db2start, and then unquiesce after the database activation is complete.

1) db2start admin mode restricted access 
2) db2 activate database <dbname>
3) db2 unquiesce instance <instance name>


http://www-01.ibm.com/support/docview.wss?uid=swg21673436&myns=swgimgmt&mynp=OCSSEPGG&mync=E

728x90
728x90


Problem(Abstract)

This article shows several methods of producing values in minutes from the difference of two datetime values. These examples can also be applied to produce hours and seconds.

Resolving the problem

Q. How can I determine the number of hours, minutes or seconds difference between two datetime values?


A. This can be accomplished with SQL using the following methods:

  • The following SQL script uses a temp table to calculate the number of minutes difference between col1 and col2. This method works with any version of IBM® Informix® Dynamic Server (IDS):
      create temp table tmp_dt_tab 
      ( 
        col1 datetime year to second, 
        col2 datetime year to second, 
        col3 interval minute(9) to minute 
      ) with no log;
      insert into tmp_dt_tab values (CURRENT YEAR TO SECOND, "2001-09-11 12:00:00", NULL);
      update tmp_dt_tab set col3 = (col1 - col2);


  • The following SQL script uses an interval to implicitly cast into minutes the difference between the current datetime and noon on 9/11/2001. This method works with any version of IDS:
      select interval(0) minute(9) to minute + 
             (current year to minute - "2001-09-11 12:00") 
        from systables 
       where tabid = 99;


  • The following SQL script explicitly casts into minutes the difference between the current datetime and noon on 9/11/2001. This method requires IDS version 9.x or 10.x:
      select (current year to minute - 
              "2001-09-11 12:00")::interval minute(9) to minute 
        from systables 
       where tabid = 99;


  • The following SQL script explicitly casts into minutes the difference between the current datetime and noon on 9/11/2001. This method requires IDS version 9.x or 10.x:
      select cast ((current year to minute - "2001-09-11 12:00") as 
                    interval minute(9) to minute) 
        from systables 
       where tabid = 99;


  • http://www-01.ibm.com/support/docview.wss?uid=swg21216971

    728x90

    + Recent posts