728x90

maxx님 | 관리 | 2013-02-02 15:11:03


디비사이즈를 확인해보고자 명령을 날렸는데...에러가 나네요.


db2 "call get_dbsize_info(?,?,?,-1)"
SQL0443N  Routine "*IZE_INFO" (specific name "") has returned an error 
SQLSTATE with diagnostic text "SQL0727 Token:1,-440,42884,
SNAPSHOT_CNTRFS|FUNCTION        ".  SQLSTATE=38553

무엇때문일까요? 어떻게 조정해야할까요?



pajama 2013-02-02 19:33:07
패키지 리바인드 후에 수행해보시기 바랍니다.

db2 bind $HOME/sqllib/bnd/st_admin.bnd blocking all grant public


728x90
728x90

maxx님 | 2013-02-01 22:18:00


사실...

테블스페이스를 증가하고 리발란싱 타임을 한번 체크해보고자 하는데...

스타트타임이 01/31/2013 13:49:04.982510 와 같이 찍혔네요.

처음에 time 을 붙이고 실행했으면 좋았을 건데

time을 붙인다는게 잊고 바로 실행했는데...

시간계산도 어렵고 해서요.

방법이 있으시면 도움주세요.




pajama 2013-02-02 00:11:35
list history 명령에서 확인하실 수 있습니다. 컨테이너 추가/삭제가 완료된 시간과, 그 직후 ALTER TABLESPACE REBALANCE END 이벤트 완료 시간의 차이를 보시면 리밸런스에 소요된 시간을 확인할 수 있습니다.

maxx 2013-02-02 11:08:37
(@@)알겠습니다.





728x90
728x90

glory님 | 2013-01-31 16:21:53


DB2 9.7 사용자 가이드 외에 

db2top 에 대한 자세한 문서가 있을까요...ㅠㅠ




pajama 2013-01-31 19:52:27
안녕하세요? 아마 아실지도 모르겠습니다만 아래 사이트가 꽤 상세합니다.

http://www.thekguy.com/db2top

developerworks나 ibm 자료는 아래를 참고하세요.

The db2top Monitoring Utility - User Manuals
http://www-01.ibm.com/support/docview.wss?uid=swg27009542

DB2 problem determination using db2top utility
http://www.ibm.com/developerworks/data/library/techarticle/dm-0812wang/

검색하기에 따라서 자료는 많이 있을 것 같습니다.


glory 2013-02-06 09:42:33
매번 감사합니다. 파자마님



728x90
728x90

스캇님 | 2013-01-29 11:23:04


안녕하세요. 

sqldbx로 db2 접속 하려고 하는데 

I 시리즈 9.0 
OS 7.1.0. I 시리즈 

어떻게 해야 할까요?



pajama 2013-01-29 12:12:29
안녕하세요? 사이트에서 살펴보니 personal edition에서는 iseries를 지원하지 않는다고 되어있네요


스캇 2013-01-29 12:41:53
접속 항목 iseries 있어서 접속 가능한줄 알았는데. ㅠㅠ 지원이 안되네요. 답변 감사 드립니다.

냉정과열정사이. 2013-01-29 17:04:02
CA400 사용이 불편하셔서 찾으시는 건가봐요?




728x90
728x90

glory님 | 2013-01-25 11:29:28


음. 좀 뜬금없지만..

DMS 유형 테이블스페이스는 자동스토리지 유형으로 변경이 가능합니다.

궁금한건  자동스토리지 유형에서 -> DMS 유형으로 변경이 가능한지요.



pajama 2013-01-26 00:08:37
automatic에서 dms로 변경하는 방법은 없습니다.

glory 2013-01-31 16:20:34
네 감사합니다.



728x90
728x90

윈드밀님 | 쿼리 | 2013-01-11 18:31:28


PMR을 했는데요. 여기도 올려봅니다. 

그리고 뒤에 local fix를 만든 분이 65살(IBM 정년 퇴임후 계속 일하고 있음.일본) 이란 것도 감동입니다.
우리도 그렇게 될 수 있을까요?

테스트 테이블 : create table xxx (c1 int);
[Fail SQL]
WITH ORGAN
(LVL)
AS
(
 SELECT 1 AS LVL
   FROM sysibm.sysdummy1 I
 UNION ALL
 SELECT P.LVL+1
   FROM ORGAN P
      , sysibm.sysdummy1 C
 WHERE P.LVL<=10
)
merge into xxx as t
using ORGAN as s
on(t.c1=s.lvl)
when matched then
 update set
  c1=s.lvl
when not matched then
 insert (c1)
 values (s.lvl)
;

[Local Fix SQL]
WITH ORGAN
(LVL)
AS
(
 SELECT 1 AS LVL
   FROM sysibm.sysdummy1 A
 UNION ALL
 SELECT B.LVL+1
   FROM ORGAN B
      , sysibm.sysdummy1 C
 WHERE B.LVL<=10
)

/*WHEN MATCHED 관련 UPDATE문을 수행하는 WITH SELECT문 */
, MATCHED
(UPDATED_ROWS)
AS
(
 SELECT COUNT(*) AS UPDATED_ROWS
   FROM FINAL TABLE
   (
   UPDATE xxx DD
      SET DD.c1 = ( SELECT AAA.LVL
                      FROM ORGAN AAA
                      WHERE AAA.LVL = DD.c1
                     )
    WHERE EXISTS ( SELECT BBB.LVL
                     FROM ORGAN BBB
                    WHERE BBB.LVL = DD.c1
                 )              
   ) D
)

/*WHEN NOT MATCHED 관련 INSERT문을 수행하는 WITH SELECT문 */
, NOT_MATCHED
(INSERTED_ROWS)
AS
(
 SELECT COUNT(*) AS INSERTED_ROWS
   FROM FINAL TABLE
   (
   INSERT INTO xxx
          (c1)
   SELECT EE.LVL
     FROM ORGAN EE
   WHERE NOT EXISTS ( SELECT *
                        FROM  xxx CCC
                       WHERE CCC.c1 = EE.LVL
                     )              
   ) E
)

SELECT *
 FROM MATCHED
    , NOT_MATCHED
;




pajama 2013-01-11 21:08:24
공유해주셔서 감사드립니다

현지수 2013-01-14 10:30:49
좋은 정보 감사합니다. 저도 65살에 좋아하는 일을 하고 있을 수 있기 위해 열심히 노력해야 할것 같습니다. ^^

김용민 2013-01-23 23:48:16
저게 되네요 ㅎㅎㅎ

배학생 2013-02-12 15:57:15
헐~ 저게 되네요... 생각도 못했는데 ㅋ



728x90
728x90


Question

This article describes a method of finding the number of free pages within a table while avoiding locking issues.

Answer

You may run into locking issues when running oncheck -pT tablename on a high use table. You can run a query using the sysmaster database instead to avoid encountering locking issues.
The following select statement from the sysmaster database will provide free page information similar information to oncheck -pt:

select count(pb_bitmap), pb_bitmap
  from sysptnbit p,systabnames t
 where t.tabname = "tablename"
   and t.dbsname = "databasename"
   and p.pb_partnum = t.partnum
 group by pb_bitmap;

This select will not place any locks on the base table tablename . The query produces output similar to:

         (count)   pb_bitmap

            2841          12
             196           0
              26           4
               1           8

4 row(s) retrieved.

The pb_bitmap column is the decimal value of the bitmap for each page in the table. The values mean the following:

pb_bitmap Description
========= =========
  0       Unused
  4       Data Page with room for another row
  8       Index Page
 12       Full Data Page

Note: You could use the query to help estimate the number of pages still available within the table. You should estimate on being able to use only 30% of the pages with the value of '4', meaning 'a data page with room for another row'.

Using the example data: The engine would need another extent after using the remaining space in the partially used pages:
(26 * 30%) = 7.8 pages


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

728x90
728x90


Question

Each variant of UNIX has its own method for naming tape devices. This document details how many different platforms define their tape devices.

Answer

Under UNIX, magnetic tape drives are given individual numbers, starting from zero. Tape drives are accessed by means of a device special file located in the /dev directory. These device special files appear to the user as normal files, which can be opened, read from and written to. All operations you would do on a normal file can be done on a special file. The only difference is that the data in the file resides on the tape loaded in the drive, not on the filesystem. In addition, because there are different ways of physically writing data to a tape (for example, low or high density), there are several different files that correspond to the same tape drive. Each variant of UNIX has its own naming convention for the tape drives. This document covers the Sun, HP, AIX, DEC, Linux, SGI, SCO, and Unixware operating systems. 
SUN
On Sun operating systems, the tape devices are defined in /dev/rmt. Device names begin with device number followed by a letter designating density and compression:

h = high
m = medium
l = low
c = compression
u = ultra-compression
n = for no-rewind

Run man st to see the man page on the SCSI tape interface. Also see the "FILES" section of the man pages for man mt.

Examples:
Device Name Description
/dev/rmt/0 Tape drive 0, auto-rewind
/dev/rmt/0n Tape drive 0, no auto-rewind
/dev/rmt/0cn Tape drive 0, compression, no auto-rewind

HP
On HP operating systems, the tape devices are defined in /dev/rmt. Device names begin with a device number followed by a letter designating density (h = high, m = medium, l = low), then have an optional "c" for compression and/or an "n" for no-rewind. Run man 7 mt to see the man page on the magnetic tape interface.

Examples:
Device Name Description
/dev/rmt/0 Tape drive 0, auto-rewind
/dev/rmt/0n Tape drive 0, no auto-rewind
/dev/rmt/0mn Tape drive 0, medium density, no auto-rewind

AIX
On IBM AIX operating systems, the tape devices are defined in /dev. Device names begin with "rmt", are followed by a device number followed by an optional period and a number suffix to indicate density and rewind options (See table for details):
Special File Name Rewind-on-Close Retension-on-Open Bytes per Inch
/dev/rmt* Yes No Density setting #1
/dev/rmt*.1 No No Density setting #1
/dev/rmt*.2 Yes Yes Density setting #1
/dev/rmt*.3 No Yes Density setting #1
/dev/rmt*.4 Yes No Density setting #2
/dev/rmt*.5 No No Density setting #2
/dev/rmt*.6 Yes Yes Density setting #2
/dev/rmt*.7 No Yes Density setting #2

Examples:
Device Name Description
/dev/rmt0 Tape drive 0, auto-rewind
/dev/rmt0.1 Tape drive 0, no auto-rewind

The values of density setting #1 and density setting #2 come from tape drive attributes that can be set using SMIT. Typically density setting #1 is set to the highest possible density for the tape drive while density setting #2 is set to a lower density. However, density settings are not required to follow this pattern. The density value (bytes per inch) is ignored when using a magnetic tape device that does not support multiple densities. For tape drives that do support multiple densities, the density value only applies when writing to the tape. When reading, the drive defaults to the density at which the tape is written. 

Run man rmt to see the man pages on the "rmt" interface.

LINUX
Tape devices under Linux the operating have different names depending on the type of tape involved. The most common sort of tape (SCSI Tape) will have a device name of 'st'. 
For example, the first SCSI tape has the following device special files:

Examples:
Device Name Description
/dev/st0 SCSI-tape 0, auto-rewind
/dev/nst0 SCSI-tape 0, no auto-rewind
/dev/rft0 Floppy tape 0, auto-rewind
/dev/nrft0 Floppy tape 0, no auto-rewind

One major difference with Linux devices and other operating systems is that the density code is not used on Linux. If you want to write a tape with a density other than the default (usually highest) density then you may need to use the mt command with the densities or set densities arguments to set the density of the device. Read operations usually do not need the density setting explicitly, as this will be automatically picked up. 

DEC
With DEC operating system tape devices, you cannot tell what the target number and SCSI bus number is for a tape device given just its name. The device name will also have other modifiers that determine things like whether the tape rewinds after use, and what density and compression settings are used on the tape. 

Tape device format:
/dev/{device type}{device number}{density/compression flag} 

{device type} rmt = Auto-rewind device, nrmt = Non-auto-rewind device
{device number} Tape device numbers start at 0. They only signify what order the device files were created in, not what SCSI bus they are attached to, or what their target id is.
{density/compression flag} Tape drives are capable of writing at multiple byte per inch densities, and some of them can use data compression hardware to compress the data before it is written to the tape. The following is a list of known flags:
h = high density / compressing device
m = medium density / compressing device
l = low density / compressing device
a = another density (QIC format drives only)

Examples:
Device Name Description
/dev/rmt0 Tape drive 0, auto-rewind
/dev/nrmt0 Tape drive 0, no auto-rewind
/dev/rmt0h Tape drive 0, auto-rewind, high density
/dev/nrmt0l Tape drive 0, no auto-rewind, low density
/dev/rmt0a Tape drive 0, auto-rewind, another density (QIC format drives only)

The density designation depends very much on the drive hardware. Running man tz gives the following information:

TLZ06: Single-density drive, compaction support.
rmt?a 61000 BPI
rmt?l 61000 BPI
rmt?m 61000 BPI, compaction turned on.
rmt?h 61000 BPI, compaction turned on.

SGI
On SGI operating systems, the tape devices are defined in /dev/rmt. Device names typically begin with an interface type, usually "tps" for tape-SCSI, followed by an interface number, a "d", a device number, and then a series of optional flags: 

nr = no-rewind
ns = no-byte-swap
v = variable record length

Run man tps to see the man pages on the SCSI tape interface.

Examples:
Device Name Description
/dev/rmt/tps0d3 SCSI-tape 0, device 3, auto-rewind
/dev/rmt/tps0d3nr SCSI-tape 0, device 3, without auto-rewind
/dev/rmt/tps0d2nrnsv SCSI-tape 0, device 2, non-rewind, no-byte-swap, variable record length

SCO
Examples:
Device Name Description
/dev/rct0 Tape drive 0, auto-rewind
/dev/nrct0 Tape drive 0, no auto-rewind

UNIXWARE
The tape drive device files are defined in /dev/rmt/*

Examples:
Device Name Description
/dev/rmt/ctape1 Tape drive 1, auto-rewind, non-retensioning
/dev/rmt/ntape1 Tape drive 1, no auto-rewind, non-retensioning
/dev/rmt/rtape1 Tape drive 1, auto-rewind, retensioning
/dev/rmt/nrtape1 Tape drive 1, auto-rewind, retensioning
/dev/rmt/utape1 Tape drive 1, unload on close


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


728x90
728x90


Question

Running a query on catalog table 'sysmaster' return tables with a value of zero in the partnum column like this :

tabname sysdomains
owner informix
partnum 0
tabid 70
rowsize 167

Answer


The partnum column in systables indicates the identification of the table inside a dbspace, when the value of partnum is zero, this means that it is a :

  • fragmented table
  • view
  • synonym


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

728x90
728x90


Problem(Abstract)

A long running session may allocate huge memory pool if DONTDRAINPOOLS environment variable was enabled on server's startup

Symptom

You notice that long running sessions have large memory pools allocated (can be up to several gigabytes).

onstat -g ses


session                                      #RSAM    total      used       dynamic 
id       user     tty      pid      hostname threads  memory     memory     explain

<...>
229697969 user1 -        -1       192.168. 1        94208      87480      off 
229694677 user3 162      21785    host1    1        7140388864 946472     off 
229673199 user5 -        29050    host1    1        118784     92752      off 
229668414 user1 -        -1       192.168. 1        131072     107280     off 
229667792 user1 -        -1       192.168. 1        102400     69608      off 
229667782 user1 -        -1       192.168. 1        106496     85304      off 
229667776 user1 -        -1       192.168. 1        94208      87480      off 
229657782 user6 494      11416    host1    1        102400     71400      off 
229634560 user2   -        -1       192.168. 1        94208      67504      off 
229630635 user1 -        -1       192.168. 1        131072     107280     off 
229630075 user1 -        -1       192.168. 1        102400     69608      off 
229630064 user1 -        -1       192.168. 1        106496     85304      off 
229630055 user1 -        -1       192.168. 1        94208      87480      off 
229626281 user7 406      125      host1    1        139264     76040      off 
229623552 user5 -        25571    host1    1        471040     399904     off 
229617823 user8  -        6118     host1    1        798720     693920     off 
229612008 user4  -        -1       192.168. 1        90112      67472      off 
229611119 user4  -        -1       192.168. 1        90112      67472      off 
229605475 user9   951      7697     host1    1        348160     121784     off 
229588448 user10 1207     28006    host1    1        737280     521296     off 
229578372 user4  -        -1       192.168. 1        90112      67448      off 
229576124 informix -        29244    host1    2        131670016  129610816  off 
229565621 user1 -        -1       192.168. 1        131072     107280     off 
229565020 user1 -        -1       192.168. 1        102400     69608      off 
229565011 user1 -        -1       192.168. 1        106496     85664      off 
229565004 user1 -        -1       192.168. 1        94208      87480      off 
229546565 user2   -        -1       192.168. 1        94208      67504      off 
229531732 user2   -        -1       192.168. 1        98304      90568      off 
229531707 user2   -        -1       192.168. 1        94208      84976      off 
229519448 user3 154      19733    host1    1        753565696  30887704   off 
229512098 user3 133      16367    host1    1        851968     626672     off 
<...>

However, when you look at the session information, most of the allocated memory is shown as free.

onstat -g ses 229519448 

session           effective                            #RSAM    total      used       dynamic 
id       user     user      tty      pid      hostname threads  memory     memory     explain 
229519448 billproc -         154      19733    mobis    1        753565696  30887704   off 

Program :
-

tid      name     rstcb            flags    curstk   status
241358080 sqlexec  9a0ea1170        --BPR--  11263    ready-

Memory pools    count 3
name         class addr              totalsize  freesize   #allocfrag #freefrag 
229519448    V     80cb19040        753258496  722660672  604        31        
229519448*O  V     7e51bd040        12288      9000       1          3         
229519448_S  V     7eed67040        294912     8320       29         3         

But in time, the amount of allocated memory keeps growing which may result in additional segments allocated for the virtual portion of shared memory.

online.log

<...>
13:35:08  Maximum server connections 2943 
13:35:08  Checkpoint Statistics - Avg. Txn Block Time 0.004, # Txns blocked 63, Plog used 1561232, Llog used 180616

13:35:14  Requested shared memory segment size rounded from 409600KB to 425984KB
13:35:15  Dynamically allocated new virtual shared memory segment (size 425984KB)
13:35:15  Memory sizes:resident:27820032 KB, virtual:9899008 KB, no SHMTOTAL limit
13:35:15  Segment locked: addr=987000000, size=436207616
13:35:50  Requested shared memory segment size rounded from 409600KB to 425984KB
13:35:50  Dynamically allocated new virtual shared memory segment (size 425984KB)
13:35:50  Memory sizes:resident:27820032 KB, virtual:10324992 KB, no SHMTOTAL limit
13:35:50  Segment locked: addr=9a1000000, size=436207616
<...>

Cause

Having DONTDRAINPOOLS environment variable set on server's startup changes behavior of Informix memory manager to keep the memory in the local session and not release it back to the main memory pool until that session terminates and frees its pool.

Diagnosing the problem

Run 'onstat -g env' and see if the DONTDRAINPOOLS environment variable was set on startup:


Server start-up environment:

Variable Value [values-list]
DBDATE dmy4
DBDELIMITER |
DBMONEY .
DBPATH .
DBPRINT lp -s
DBTEMP /tmp
DONTDRAINPOOLS 1
IGNORE_UNDERFLOW 1
INFORMIXCONRETRY 1
INFORMIXCONTIME 10
INFORMIXDIR /informix/mobserver/inf11
<...>

Check messages in online.log file produced during server's startup if there is a message like "Server is disabling pools draining".

Resolving the problem

- You can run 'onmode -F' to free allocated memory.

- Unset DONTDRAINPOOLS environment variable and restart the Informix server.


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

728x90

+ Recent posts