IBM DB2 SQL5005C 오류 해결법

Windows에서 IBM DB2에 접속하려면 전용 Client를 설치해야 합니다.
Windows 7 이후 UAC가 적용되면서부터 일반 사용자로 DB2에 접근을 시도할 경우 SQL5005C 오류가 일어나는 경우가 있습니다.

SQL5005C 데이터베이스 관리자가 데이터베이스 관리자 구성 파일 또는 데이터베이스 구성 파일에 액세스하지 못했으므로 연산이 실패했습니다.

이 경우에는 아래와 같이 조치를 취해줘야 합니다.

1. DB2 Client 재설치 또는 변경

프로그램 및 기능 으로 가셔서 설치된 프로그램 목록에 DB2 Client를 선택한 다음 변경을 누르세요.
그럼 설치와 같은 화면이 나오는데, 그 단계 중 확장 윈도우 보안 (Extended Windows Security) 설정을 Off 합니다.

2. 해당 사용자를 DB2USERS 또는 DB2ADMINS에 추가합니다.

윈도우 + R 키를 눌러서 실행창을 연다음 mmc를 치세요.

Microsoft Management Console가 실행되면 왼쪽에서

그런 다음 왼쪽에서 로컬 사용자 및 그룹을 선택합니다.

(만약 해당 항목이 없다면 상단 메뉴에서 파일 -> 스냅인 추가 에서 선택하면 나옵니다.

그런뒤 DB2USERS 나 DB2ADMINS에 해당 Windows 사용자를 추가해 주세요.

3. 해당 사용자로 로그인(현재 접속중이라면 로그오프 후 새로 로그인) 하신 후에 사용하시면 됩니다.

* 참고
http://www-01.ibm.com/support/docview.wss?uid=swg21512440
http://windows.microsoft.com/ko-kr/windows/add-user-account-to-group#1TC=windows-7

DB2 Authorization & Privilege

DB2 authorization & priviliege from Seok-joon Yun

DB2 Pivot Query Example

• PIVOT
  The meaning of pivoting a table is to turn n-rows into n-columns.
  For example given a ales table with a composite primary key of year and quarter you may want to get a result that shows only one row for each year, but a separate column for each quarter.

CREATE TABLE Sales(Year    INTEGER NOT NULL,
                   Quarter INTEGER NOT NULL,
                   Results INTEGER);

CREATE UNIQUE INDEX PK_IDX ON Sales(Year, Quarter) INCLUDE(Results);

ALTER TABLE Sales ADD PRIMARY KEY (Year, Quarter);
 
INSERT INTO Sales VALUES
(2004, 1, 20),
(2004, 2, 30),
(2004, 3, 15),
(2004, 4, 10),
(2005, 1, 18),
(2005, 2, 40),
(2005, 3, 12),
(2005, 4, 27);

The desired result is:

Year Q1 Q2 Q3 Q4
---- -- -- -- --
2004 20 30 15 10
2005 18 40 12 27

There a many ways to achieve pivoting, but there is clearly one that is optimal which I will explain here.
Pivoting involves two events: First the payload (Results in this case) needs to be dispatch into the Q1 through Q4 columns.
This can be done with a CASE expression or DECODE (which is another syntax for CASE):

SELECT Year,
       DECODE(Quarter, 1, Results) AS Q1,
       DECODE(Quarter, 2, Results) AS Q2,
       DECODE(Quarter, 3, Results) AS Q3,
       DECODE(Quarter, 4, Results) AS Q4
  FROM Sales;

 
YEAR                 Q1          Q2          Q3          Q4
----------- ----------- ----------- ----------- -----------
       2004          20           -           -           -
       2004           -          30           -           -
       2004           -           -          15           -
       2004           -           -           -          10
       2005          18           -           -           -
       2005           -          40           -           -
       2005           -           -          12           -
       2005           -           -           -          27

  8 record(s) selected.

Note how DECODE injects NULL as an implicit ELSE.
The second step is to collapse the rows to get one row per Year.
The technique of choice is a GROUP BY here.
When grouping on Year we need to use some sort of column function for the columns Q1 through Q4.
MAX or MIN() work for most types and they are very cheap. Especially since all but one value will be NULL per group:

SELECT Year,
       MAX(DECODE(Quarter, 1, Results)) AS Q1,
       MAX(DECODE(Quarter, 2, Results)) AS Q2,           
       MAX(DECODE(Quarter, 3, Results)) AS Q3,
       MAX(DECODE(Quarter, 4, Results)) AS Q4
  FROM Sales
  GROUP BY Year;

YEAR                 Q1          Q2          Q3          Q4
----------- ----------- ----------- ----------- -----------
       2004          20          30          15          10
       2005          18          40          12          27

  2 record(s) selected.

I noted above that this approach is the best. How do I know. Well let's look at the access plan:

Access Plan:
-----------
     Total Cost:         0.0134932
    Query Degree:        1

        Rows
       RETURN
       (   1)
        Cost
         I/O
         |
         1.6
       GRPBY
       (   2)
       0.01322
          0
         |
          8
       IXSCAN
       (   3)
      0.0126886
          0
         |
          8
 INDEX:    SERGE  
       PK_IDX
         Q1

Pretty hard to beat!

• UNPIVOT
  Unpivot is the inverse operation of PIVOT. Here we have several similar columns which are to be combined into one column, but different rows.
  Let's reuse the same example from above and run it backwards so to speak

CREATE TABLE Sales(Year INTEGER NOT NULL PRIMARY KEY,
                   Q1   INTEGER,
                   Q2   INTEGER,
                   Q3   INTEGER,
                   Q4   INTEGER);

INSERT INTO Sales VALUES
(2004, 20, 30, 15, 10),
(2005, 18, 40, 12, 27);

The most efficient way to unpivot is to do a cross join between the Sales table and a correlated VALUES of as many rows as columns that need to be unpivoted.

SELECT Year, Quarter, Results
  FROM Sales AS S,
             LATERAL(VALUES (1, S.Q1),
                            (2, S.Q2),
                            (3, S.Q3),
                            (4, S.Q4)) AS T(Quarter, Results);

YEAR            QUARTER     RESULTS
----------- ----------- -----------
       2004           1          20
       2004           2          30
       2004           3          15
       2004           4          10
       2005           1          18
       2005           2          40
       2005           3          12
       2005           4          27

  8 record(s) selected.

Again let's look at the explain:

Access Plan:
-----------
     Total Cost:         7.57432
    Query Degree:        1

                  Rows
                 RETURN
                 (   1)
                  Cost
                   I/O
                   |
                    8
                 NLJOIN
                 (   2)
                 7.57432
                    1
           /-------+-------\
          2                   4
       TBSCAN              TBSCAN
       (   3)              (   4)
       7.57317          4.95961e-005
          1                   0
         |                   |
          2                   4
 TABLE:    SERGE     TABFNC:   SYSIBM 
        SALES              GENROW
         Q3                  Q1

Once more this is the most efficient plan. Since it only contains a single scan.

원문 : https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/pivoting_tables56?lang=en