'Informix' 태그의 글 목록 (3 Page)

Informix

Informix 14.10.xC4부터 SQL문장의 쿼리 플랜을 확인하는 getExplain 함수 제공 2020.07.07
Informix 14.10.xC4의 MSG_DATE 설정 변화 2020.07.07
Informix 14.10.xC4의 새로운 onstat -g top 옵션 2020.07.07
Informix의 Smart Trigger 구현 따라해보기 2020.04.23
SQL로 액셀의 NETWORKDAYS 기능 구현하기 2020.04.22
프로시저로 UNLOAD 실행하기 2020.04.09
인포믹스의 통합 백업 암호화 기능 2020.03.31
rlwrap으로 dbaccess 편하게 사용하기 2020.03.27
OLAP함수로 가장 자주 나타나는 문자열 검색 2020.03.19
USE_FALLOCATE 설정으로 Chunk 빠르게 만들기 2020.03.10

Informix 14.10.xC4부터 SQL문장의 쿼리 플랜을 확인하는 getExplain 함수 제공

@ipajama 2020. 7. 7. 13:12

2020. 7. 7. 13:12

14.10.xC4 버전부터 공식적으로 클라이언트에서도 쿼리 플랜을 확인할 수 있는 함수가 제공되는군요. 인포믹스는 과거부터 DB서버에서 set explain 문장을 실행한 후 SQL 문장을 실행하면 생성되는 파일에서 쿼리 플랜을 참조할 수 밖에 없었는데요. 그렇다보니 클라이언트에서는 쿼리 플랜을 볼 방법이 없었습니다.

그래서 11.50버전부터 쿼리 플랜을 시각화하는 explain_sql이라는 루틴이 제공되었는데 IBM Data Studio에서 제한적으로만 쓸 수 있어서 그렇게 많이 사용되지는 않았습니다. 사실 Data Studio는 Db2에 더 최적화 된 느낌이라 인포믹스 DB에 사용하기에는 좀 불편하지요.

그래서 Fernando Nunes씨가 SQL 프로시저를 사용해서 쿼리 플랜을 보는 방법을 제시하기도 했습니다. 사실 이번에 제공되는 루틴과 대동소이합니다. 여러번 루틴을 실행해야하는 번거로움이 있긴 하지만요.

informix-technology.blogspot.com/2012/12/execution-plans-on-client-planos-de.html

어쨌든 많이 늦었지만, 이제라도 쿼리 플랜을 IBM에서 제작된 루틴으로 조회할 수 있다는 점에서 매우 환영할만한 기능입니다. IBM Knowledge Center에서 제공되는 문서의 내용을 따라서 getExplain 함수를 만들어 보았습니다.

> CREATE FUNCTION getExplain(LVARCHAR) RETURNS LVARCHAR(30000) EXTERNAL NAME 'com.informix.judrs.Explain.getExplain(java.lang.String)' LANGUAGE JAVA;
Routine created.
> GRANT EXECUTE ON FUNCTION getExplain(LVARCHAR) TO PUBLIC;
Permission granted.

결과는 LVARCHAR 형식으로 출력되는데, 최대 32KB이니 어지간하면 잘리지 않을 것 같습니다. CLOB 형식으로 해도 괜찮지 않을까 싶습니다.

> execute function getExplain("SELECT * FROM systables a, syscolumns b where a.tabid = b.tabid");
(expression)
              QUERY: (OPTIMIZATION TIMESTAMP: 06-25-2020 22:42:56)
              ------
              SELECT * FROM systables a, syscolumns b where a.tabid = b.tabid
              Estimated Cost: 108
              Estimated # of Rows Returned: 779
                1) informix.a: SEQUENTIAL SCAN
                2) informix.b: INDEX PATH
                  (1) Index Name: informix.column
                      Index Keys: tabid colno
                      Lower Index Filter: informix.a.tabid = informix.b.tabid
              NESTED LOOP JOIN
              ----------
              Procedure: informix.getexplain
              Query statistics:
              -----------------
                Table map :
                ----------------------------
                Internal name     Table name
                ----------------------------
                t1                a
                t2                b
                type     table  rows_prod  est_rows  rows_scan  time       est_cost
                -------------------------------------------------------------------
                scan     t1     1          113       7          00:00.00   14
                type     table  rows_prod  est_rows  rows_scan  time       est_cost
                -------------------------------------------------------------------
                scan     t2     4          786       4          00:00.00   1
                type     rows_prod  est_rows  time       est_cost
                -------------------------------------------------
                nljoin   4          780       00:00.00   109
1 row(s) retrieved.

조금 더 욕심을 부리자면 SQL 문장을 실행하지 않는 AVOID_EXECUTE와 같은 기능을 제공하는 인자도 지정할 수 있다면 좋겠네요.

아래는 getExplain을 실행했을때 online.log에 표시되는 내용입니다.

2020-06-25 22:41:41.70  Booting Language <java> from module <$INFORMIXDIR/extend/krakatoa/lmjava.so>
2020-06-25 22:41:41.70  Loading Module <$INFORMIXDIR/extend/krakatoa/lmjava.so>
2020-06-25 22:41:41.82  cannot extract stack unwind information for /work1/informix/ids1410fc4w1/gls/dll/64-libicudata.so.60
2020-06-25 22:41:41.83  stack dumps out of /work1/informix/ids1410fc4w1/gls/dll/64-libicudata.so.60 may be incomplete
2020-06-25 22:41:41.83  The C Language Module </work1/informix/ids1410fc4w1/extend/krakatoa/lmjava.so> loaded
2020-06-25 22:41:41.83  Loading Module <com.informix.judrs.Explain>
2020-06-25 22:41:41.86  INFO (autoregvp 1) (Execution of [ autoregvp ] dymamically creating VPCLASS jvp)
2020-06-25 22:41:41.957  Dynamically added 1 jvp VP
2020-06-25 22:41:42.859  Got the mutex
2020-06-25 22:41:42.859  LD_LIBRARY_PATH=/work1/informix/ids1410fc4w1/extend/krakatoa/jre/bin/j9vm
2020-06-25 22:41:42.860  VM args[0]= -Xss512k
2020-06-25 22:41:42.860  VM args[1]= -Djava.security.policy=/work1/informix/ids1410fc4w1/tmp/JVM_security
2020-06-25 22:41:42.860  VM args[2]= -Xms16m
2020-06-25 22:41:42.860  VM args[3]= -Xmx16m
2020-06-25 22:41:42.860  VM args[4]= exit
2020-06-25 22:41:42.860  VM args[5]= abort
2020-06-25 22:41:42.860  VM args[6]= -Djava.class.path=/work1/informix/ids1410fc4w1/extend/krakatoa/krakatoa.jar:/work1/informix/ids1410fc4w1/extend/krakatoa
2020-06-25 22:41:43.24  Successfully created Java VM.
2020-06-25 22:41:43.820  Explain file for session 240 : /tmp/informix-explain1554368260018369330.tmp
2020-06-25 22:42:45.158  Explain file for session 240 : /tmp/informix-explain753362435265703881.tmp
2020-06-25 22:42:56.869  Explain file for session 241 : /tmp/informix-explain3777225348977126759.tmp

내용을 살펴보면 java 클래스를 사용한 사용자 함수를 호출한 형태임을 알 수 있습니다. 그리고 set explain 명령을 수행했을 때 처럼 'Explain file for session ...' 이라는 내용이 표시되는데요. 함수가 실행된 직후에는 해당 파일은 삭제되는 것 같습니다.

체감될만한 좋은 기능들이 계속 추가되고 있어서, 앞으로의 변화도 기대되는군요.

저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

Informix에서 Oracle 데이터베이스 참조하기 (0)	2020.09.14
Informix에서 MSSQL 데이터베이스 참조하기 (0)	2020.09.14
Informix 14.10.xC4의 MSG_DATE 설정 변화 (0)	2020.07.07
Informix 14.10.xC4의 새로운 onstat -g top 옵션 (0)	2020.07.07
Informix의 Smart Trigger 구현 따라해보기 (0)	2020.04.23

Informix 14.10.xC4의 MSG_DATE 설정 변화

@ipajama 2020. 7. 7. 13:03

2020. 7. 7. 13:03

online.log 파일을 보다가 바뀐부분을 또 찾았네요.

online.log 내용에서 라인마다 날짜를 표시하는 방식이 몇가지 추가되었습니다.

기존에는 MSG_DATE 파라미터 설정값을 0과 1로만 설정할 수 있었는데요. 14.10.xC4 버전부터는 0,1,2,3 의 값을 설정할 수 있습니다. 아래는 onstat에서 보여주는 MSG_DATE 파라미터의 설명입니다.

$ onstat -g cfg full MSG_DATE
IBM Informix Dynamic Server Version 14.10.FC4W1DE -- On-Line -- Up 23:01:10 -- 2631308 Kbytes
Configuration Parameter Info
id   name                      type    maxlen   units   rsvd  tunable
150  MSG_DATE                  INT4    12                     *
     min/max : 0,3
     default : 0
     onconfig: 3
     current : 3
     Description:
     Use the MSG_DATE configuration parameter to specify the format of the
     timestamp preceding messages in the log. The choices are as follows:
     Value     Format
     0         HH:MM:SS
     1         MM/DD/YYYY HH:MM:SS (Locale-dependent ordering of MM and DD)
     2         Milliseconds since epoch + MM/DD/YYYY HH:MM:SS (Locale-dependent
               ordering of MM and DD)
     3         YYYY-MM-DD HH:MM:SS.FFF

14.10.FC3과 14.10.FC4W1 버전에서 출력되는 내용을 비교해보았습니다.

-- 14.10.xC3 (MSG_DATE=1)
06/24/20 21:45:40  Checkpoint Completed:  duration was 0 seconds.
06/24/20 21:45:40  Wed Jun 24 - loguniq 1101, logpos 0x8234, timestamp: 0x13fca499 Interval: 358
06/24/20 21:45:40  Maximum server connections 5
06/24/20 21:45:40  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 1229, Llog used 10125
-- 14.10.xC4 (MSG_DATE=2, SERVER_LOCALE=ko_kr.ksc)
1593004908957 20/06/24 22:21:48 Checkpoint Completed:  duration was 0 seconds.
1593004908957 20/06/24 22:21:48 Wed Jun 24 - loguniq 20, logpos 0x861018, timestamp: 0x2875df Interval: 345
1593004908957 20/06/24 22:21:48 Maximum server connections 3
1593004908957 20/06/24 22:21:48 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 39, Llog used 5
-- 14.10.xC4 (MSG_DATE=3)
2020-06-24 21:46:42.356  Checkpoint Completed:  duration was 0 seconds.
2020-06-24 21:46:42.356  Wed Jun 24 - loguniq 20, logpos 0x5e64d4, timestamp: 0x27ae32 Interval: 333
2020-06-24 21:46:42.356  Maximum server connections 11
2020-06-24 21:46:42.356  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 63, Llog used 55

아무래도 연월일 순서로 나오는게 보기가 편하겠죠? 근데 밀리세컨드까진 필요없을 것도 같고..

저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

Informix에서 MSSQL 데이터베이스 참조하기 (0)	2020.09.14
Informix 14.10.xC4부터 SQL문장의 쿼리 플랜을 확인하는 getExplain 함수 제공 (0)	2020.07.07
Informix 14.10.xC4의 새로운 onstat -g top 옵션 (0)	2020.07.07
Informix의 Smart Trigger 구현 따라해보기 (0)	2020.04.23
SQL로 액셀의 NETWORKDAYS 기능 구현하기 (0)	2020.04.22

Informix 14.10.xC4의 새로운 onstat -g top 옵션

@ipajama 2020. 7. 7. 13:00

2020. 7. 7. 13:00

6월 23일자로 인포믹스 14.10 버전의 새로운 Fix Pack 14.10.xC4W1이 공개되었습니다.

아직 문서에 새로운 기능 소개는 나오지 않아서 onstat 유틸리티에 새로운 옵션이 생겼나 찾아보니 하나가 눈에 띄네요.

onstat -g top이라는 옵션이 새로 생겼습니다. 리눅스의 top과 유사한 기능을 제공하는 옵션인데요. 기본값으로 5초간의 성능수치를 비교하여 시스템 자원을 많이 사용하는 세션이나 스레드를 확인할 수 있습니다. 시간 간격이나 표시할 라인 수, 반복 횟수를 지정할 수 있습니다.

아래는 onstat 명령을 실행했을 때의 top 옵션 설명입니다.

        top [ <entity> <stat> [ <max lines> [ <intvl> [ <reps> ]]]]
            Print top consumers of various resources over specified interval
            Valid <entity> <stat> combinations:
                thread    cpu   (CPU usage)
                thread    drd   (disk reads)
                thread    bfr   (buffer reads)
                thread    bfw   (buffer writes)
                thread    plg   (physical log usage)
                thread    llg   (logical log usage)
                session   cpu   (CPU usage)
                session   drd   (disk reads)
                session   bfr   (buffer reads)
                session   bfw   (buffer writes)
                session   plg   (physical log usage)
                session   llg   (logical log usage)
                chunk     ios   (page reads/writes)
                chunk     art   (average read times)
                chunk     awt   (average write times)
                space     ios   (page reads/writes)
                space     art   (average read times)
                space     awt   (average write times)
                mempool   gro   (memory growth)
                sessmem   gro   (memory growth)
                partition drd   (disk reads)
                table     drd   (disk reads)

위에 나열된 항목을 전부 또는 지정해서 볼 수 있습니다.

watch 명령을 이용해서 인포믹스를 모니터링 하는 것도 괜찮아 보입니다.

아래는 제가 약간의 트랜잭션을 발생시키고 나서 top 옵션으로 본 화면입니다.

[informix@db2 skjeong]$ onstat -g top 10 3
IBM Informix Dynamic Server Version 14.10.FC4W1DE -- On-Line -- Up 18:46:36 -- 2631308 Kbytes
Top Resource Usage (Max lines 10, Time interval 3 seconds):
Top Threads (CPU usage)
 tid    name              sid        CPU_time   #scheds  status
 343    sqlexec           208          0.0014         6  sleeping secs: 1
 344    sqlexec           209          0.0009         6  sleeping secs: 1
 345    sqlexec           210          0.0009         6  sleeping secs: 1
 19     periodic          14           0.0001         5  sleeping secs: 1
Top pools (memory growth)
 name                           increase(b)      total_size(b)
 rsam                           8                3081712
(No partition disk reads to display)
(No DBspace activity to display)
(No physlog activity to display)
Top threads (logical log usage)
 tid      rstcb              llog_bytes   name
 344      0x459af1a8         552          sqlexec
 343      0x459afa88         552          sqlexec
 345      0x459b0c48         552          sqlexec

기존에 스크립트로 모니터링 하시는 분들도 계시겠지만, onstat 명령으로 본다면 좀 더 시스템에 부담은 덜 줄 것 같군요. 실시간 모니터링에 많은 도움이 될 것 같습니다.

다른 유용한 기능이 더 생겼는지 찾아봐야겠네요.

저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

Informix 14.10.xC4부터 SQL문장의 쿼리 플랜을 확인하는 getExplain 함수 제공 (0)	2020.07.07
Informix 14.10.xC4의 MSG_DATE 설정 변화 (0)	2020.07.07
Informix의 Smart Trigger 구현 따라해보기 (0)	2020.04.23
SQL로 액셀의 NETWORKDAYS 기능 구현하기 (0)	2020.04.22
Informix 12.10.xC14의 CHAR_LENGTH 함수 오류 (0)	2020.04.14

Informix의 Smart Trigger 구현 따라해보기

@ipajama 2020. 4. 23. 12:56

2020. 4. 23. 12:56

오늘은 IBM Community에서 인포믹스 Smart Trigger 구현중에 발생한 오류에 대해서 질문이 있었습니다. 인포믹스 12.10.xC9 버전부터 Smart Trigger라는 기능이 소개되었는데 IBM Knowledge Center에서의 설명은 아래와 같습니다.

In a relational database environment, client applications are constantly monitoring and triggering other complex jobs, based on changes happening in the database. Applications need to be able to detect events in the database as they happen, without adding overhead to the database server.
With the release of Informix 12.10.xC9, clients can now create JDBC Smart Triggers to register changes in a dataset, using SELECT statements and WHERE clauses, and push data from the server to the client. Scaling is achieved by clients not having to poll for data, while the database server's parallel architecture can feed the data to all clients by asynchronously reading logical log file changes. This design lets client applications scale linearly without adding significant overhead to the database server or any OLTP applications making changes to the database.

요약하면 스마트 트리거를 사용하면 데이터베이스 서버에 변경을 가하지 않고 클라이언트 프로그램에서 정의한 SELECT 문장의 조건으로 데이터베이스 데이터의 변경을 감지할 수 있다고 설명하고 있습니다. 데이터베이스의 트리거와는 달리 비동기식으로 작동하므로 데이터베이스에 가해지는 부하가 적다고 합니다.

테스트를 위해 HCL 커뮤니티의 포스트에서 Java 코드를 참고했습니다.

http://www.hcl-informix-user.com/blogs/introducing-hcl-informix-smart-triggers

Java 프로그램을 실행하기 전에 데이터를 입력할 테이블을 하나 만듭니다.

CREATE TABLE account (id integer primary key, name varchar(200), balance float);

코드 내용에서 JDBC url 내용을 수정합니다. sysadmin 데이터베이스와 모니터링할 데이터베이스의 로케일을 고려하여 DB_LOCALE과 CLIENT_LOCALE 설정값을 지정합니다. 이 예시의 경우 sysadmin은 en_us.819, bank는 en_us.utf8입니다.

import java.sql.SQLException;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.informix.smartTrigger.IfmxSmartTriggerCallback;
import com.informix.smartTrigger.IfxSmartTrigger;
public class SmartTrigger implements IfmxSmartTriggerCallback {
               private final JsonParser p = new JsonParser();
               public static void main(String[] args) throws SQLException {
                              try(IfxSmartTrigger trigger = new IfxSmartTrigger("jdbc:informix-sqli://xxx.xx.xx.xx:53331/sysadmin:user=informix;password=password;DB_LOCALE=en_us.utf8;CLIENT_LOCALE=en_us.utf8");) {
                                             trigger.label("bank_alert").timeout(5); // optional parameters
                                             trigger.addTrigger("account", "informix", "bank", "SELECT * FROM account WHERE balance < 0", new SmartTrigger());
                                             trigger.watch();
                              }
               }
               @Override
               public void notify(String jsonString) {
                              JsonObject json = p.parse(jsonString).getAsJsonObject();
                              System.out.println("Bank Account Ping!");
                              if (json.has("ifx_isTimeout")) {
                                             System.out.println("-- No balance issues");
                              } else {
                                             System.out.println("-- Bank Account Alert detected!");
                                             System.out.println("   " + json);
                              }
               }
}

코드 내용을 보면 GSON 라이브러리를 사용하고 있으므로 다운로드 받습니다. 2.8.6 버전을 받아서 컴파일해보니 오류가 발생해서 2.8.2 버전을 다시 받았습니다.

[informix@db2 skjeong]$ wget https://repo1.maven.org/maven2/com/google/code/gson/gson/2.8.2/gson-2.8.2.jar
--2020-04-23 11:17:35--  https://repo1.maven.org/maven2/com/google/code/gson/gson/2.8.2/gson-2.8.2.jar
Resolving repo1.maven.org (repo1.maven.org)... 151.101.196.209
Connecting to repo1.maven.org (repo1.maven.org)|151.101.196.209|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 232932 (227K) [application/java-archive]
Saving to: ‘gson-2.8.2.jar’
100%[===================================================================================================================================================>] 232,932      366KB/s   in 0.6s
2020-04-23 11:17:36 (366 KB/s) - ‘gson-2.8.2.jar’ saved [232932/232932]

아래는 Java 코드 컴파일 후 프로그램을 실행한 화면입니다. 저 같은 경우 서버에 java 실행파일들이 여기저기 흩어져있어서 되는 것으로 일단 사용했습니다. 1.8 이상의 JRE와 인포믹스 JDBC 드라이버는 4.10.xC9 이상을 사용하시면 됩니다.

실행하면 timeout 값(초) 간격으로 'Bank Account Ping!' 메시지가 출력됩니다.

[informix@db2 skjeong]$ export CLASSPATH=/work1/informix/1210FC10/jdbc/lib/ifxjdbc.jar:/work1/informix/ids1410fc3/skjeong/gson-2.8.2.jar:.
[informix@db2 skjeong]$ /opt/ibm/db2/V11.5.dc/java/jdk64/bin/javac SmartTrigger.java
[informix@db2 skjeong]$ /work1/informix/ids1410fc3/jvm/jre/bin/java SmartTrigger
Bank Account Ping!
-- No balance issues
Bank Account Ping!
-- No balance issues

이제 프로그램에서 정의한 조건에 감지될 수 있는 INSERT와 DELETE문장을 실행해보겠습니다.

[informix@db2 skjeong]$ dbaccess bank -
Database selected.
> insert into account (id, name, balance) values (22, 'John Doe', -23.45);
1 row(s) inserted.
> delete from account where id = 22;
1 row(s) deleted.

앞서 실행한 Java 프로그램에서 정의한 SELECT 문장(은 balance가 음수일 경우에 경고를 알리는 메시지를 출력하도록 되어 있습니다. (SELECT * FROM account WHERE balance < 0) 출력 데이터 형식은 JSON입니다.

-- Bank Account Alert detected!
   {"operation":"insert","table":"account","owner":"informix","database":"bank","label":"bank_alert_1","txnid":1421639512296,"operation_owner_id":1001,"operation_session_id":584,"commit_time":1587608886,"op_num":1,"restart_logid":331,"restart_logpos":5329128,"rowdata":{"id":22,"name":"John Doe","balance":-23.4499999999999990}}
Bank Account Ping!
-- No balance issues
Bank Account Ping!
-- No balance issues
-- Bank Account Alert detected!
   {"operation":"delete","table":"account","owner":"informix","database":"bank","label":"bank_alert_1","txnid":1421639540968,"operation_owner_id":1001,"operation_session_id":584,"commit_time":1587608955,"op_num":1,"restart_logid":331,"restart_logpos":5357804,"rowdata":{"id":22,"name":"John Doe","balance":-23.4499999999999990}}
Bank Account Ping!
-- No balance issues

참고사이트

https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.po.doc/new_features_ce.htm#newxc9__xc9_push_data

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_st_01.htm

저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

Informix 14.10.xC4의 MSG_DATE 설정 변화 (0)	2020.07.07
Informix 14.10.xC4의 새로운 onstat -g top 옵션 (0)	2020.07.07
SQL로 액셀의 NETWORKDAYS 기능 구현하기 (0)	2020.04.22
Informix 12.10.xC14의 CHAR_LENGTH 함수 오류 (0)	2020.04.14
프로시저로 UNLOAD 실행하기 (0)	2020.04.09

SQL로 액셀의 NETWORKDAYS 기능 구현하기

@ipajama 2020. 4. 22. 22:47

2020. 4. 22. 22:47

안녕하세요. 데이터베이스 사랑넷에 두 날짜사이에서 주말을 제외한 일수를 구하는 방법에 대한 질문이 있어서 이것 저것 찾아보고 시험해봤습니다.

기능에 대해서 잘 모르다보니 working day sql postgresql 키워드로 구글에 검색해보니 비슷한 기능을 구현한 예제가 많이 있었습니다. 먼저 질문 내용입니다.

아래 쿼리는 mysql에서
두 날짜사이 주말일수를 제외한 일수를 구하는 sql문인데
PostgreSQl에서 적용되게 변환하고 싶습니다.
부탁드립니다.
select ABS(DATEDIFF('2020-04-23', '2020-04-26')) + 1 - ABS(DATEDIFF(ADDDATE('2020-04-23', INTERVAL 1 - DAYOFWEEK('2020-04-23') DAY),
ADDDATE('2020-04-26', INTERVAL 1 - DAYOFWEEK('2020-04-26') DAY))) / 7 * 2
- (DAYOFWEEK(IF('2020-04-26' < '2020-04-23', '2020-04-26', '2020-04-23')) = 1)
- (DAYOFWEEK(IF('2020-04-26' > '2020-04-23', '2020-04-26', '2020-04-23')) = 7);

찬찬히 살펴보면 주말일수를 먼저 빼고 그외의 경우에 대한 조건이 포함되어 있습니다. 이것 저것 경우의 수를 고려한 것인데 mysql에서 날짜를 바꾸어서 실행해보아도 맞는 결과가 나오는 것 같습니다.

그래서 최대한 위의 것을 참고해서 PostgreSQL 문법에 맞게 만들어 보았습니다.

SELECT ABS(DATE '2020-04-04' - DATE '2020-04-26') - 
       ABS((DATE '2020-04-04' - EXTRACT(DOW FROM DATE '2020-04-04')::INT) -
           (DATE '2020-04-26' - EXTRACT(DOW FROM DATE '2020-04-26')::INT))/7*2 -
       CASE WHEN EXTRACT(DOW FROM DATE '2020-04-04') = 0 THEN 1 ELSE 0 END +
       CASE WHEN EXTRACT(DOW FROM DATE '2020-04-26') = 0 THEN 1 ELSE 0 END

질문하신 분의 계산식과 유사하게 만들었지만, 여기서는 빠른 날짜에서 이후 날짜를 빼는 식이라 날짜 순서가 바뀌면 쿼리도 바뀌어야 합니다. 이런식으로도 불가능하진 않지만 상당히 복잡해질 수 있습니다.

그래서 좀더 나은 방법이 없을까 해서 구글을 찾아보니 액셀의 NETWORKDAYS라는 함수가 이것과 동일한 기능인 것을 알게 됐습니다. 그럼 더 나아가 PostgreSQL에 NETWORKDAYS를 구현한 사례도 있겠거니해서 찾아보니 아래와 같은 예제를 찾았습니다.

select start_date, end_date, 
       sum(case when extract (dow from dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select start_date, end_date, 
              generate_series(start_date, end_date, '1 day'::interval) as dt
       from   tbl
     ) t
group by start_date, end_date;

PostgreSQL에서 요일을 확인하려면 extract (dow from date) 와 같은식으로 사용하는데 0(일요일)에서 6(토요일)까지 규정되어 있습니다. 이것 저것 따질 것도 없이 날짜를 쭉 나열하고 요일을 판별하는 식으로 아주 직관적입니다.

다만 날짜 구간이 길면 성능이 어떨지 모르겠군요.

우선 여기까지 질문에 대해서는 해결이 되었는데 인포믹스에서 사용하는 방법도 정리해두면 좋을 것 같아 위의 방법을 따라 해보았습니다. 인포믹스도 0은 일요일을 나타내고 1은 월요일을 나타내는 식입니다.

select sum(case when weekday(dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select '2020-04-04'::date + level units day as  dt connect by level < abs('2020-04-04'::date - '2020-04-26'::date)
     ) t
         thediff
              15
1 row(s) retrieved.

참고사이트

https://www.postgresql.org/docs/8.1/functions-datetime.html

https://dba.stackexchange.com/questions/207701/count-business-days-between-2-dates-in-postgresql

https://stackoverflow.com/questions/7388420/get-datediff-excluding-weekends-using-sql-server

https://stackoverflow.com/questions/1964544/timestamp-difference-in-hours-for-postgresql

저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

Informix 14.10.xC4의 새로운 onstat -g top 옵션 (0)	2020.07.07
Informix의 Smart Trigger 구현 따라해보기 (0)	2020.04.23
Informix 12.10.xC14의 CHAR_LENGTH 함수 오류 (0)	2020.04.14
프로시저로 UNLOAD 실행하기 (0)	2020.04.09
인포믹스의 통합 백업 암호화 기능 (0)	2020.03.31

프로시저로 UNLOAD 실행하기

@ipajama 2020. 4. 9. 10:55

2020. 4. 9. 10:55

인포믹스 프로시저에서는 UNLOAD나 OUTPUT 문장을 실행할 수 없습니다. 이를 대체하려면 External Table 기능을 사용해 데이터를 파일로 내려받는 방법이 있습니다.

참고로 External Table 기능은 인포믹스 11.5 버전부터 사용할 수 있습니다.

/work2/INFORMIX/1210FC13/skjeong]cat myproc.sql
DROP PROCEDURE MYPROC();
CREATE PROCEDURE MYPROC()
CREATE EXTERNAL TABLE load_tmp
(
   load_stmt char(1024)
)
USING (
DATAFILES    ("DISK:/tmp/load.sql"),
DELIMITER ";"
);
INSERT INTO load_tmp SELECT 'load from ' || trim(tabname) || ' insert into ' || trim(tabname) FROM systables WHERE tabid > 99 AND tabtype = 'T';
DROP TABLE load_tmp;
END PROCEDURE;
/work2/INFORMIX/1210FC13/skjeong]dbaccess stores_demo myproc.sql
Database selected.
Routine dropped.
Routine created.
Database closed.
/work2/INFORMIX/1210FC13/skjeong]cat /tmp/load.sql
load from customer insert into customer;
load from orders insert into orders;
load from manufact insert into manufact;
load from stock insert into stock;
load from items insert into items;
load from state insert into state;
load from call_type insert into call_type;
load from cust_calls insert into cust_calls;
load from catalog insert into catalog;
...
load from calendarpatterns insert into calendarpatterns;
load from calendartable insert into calendartable;
load from tsinstancetable insert into tsinstancetable;
load from tscontainertable insert into tscontainertable;
load from tscontainerusageactivewindowvti insert into tscontainerusageactivewindowvti;
load from tscontainerusagedormantwindowvti insert into tscontainerusagedormantwindowvti;
load from tscontainerwindowtable insert into tscontainerwindowtable;
load from ts_data insert into ts_data;
load from customer_ts_data insert into customer_ts_data;
load from ts_data_v insert into ts_data_v;
load from ts_data_multiplier_v insert into ts_data_multiplier_v;
load from spatial_references insert into spatial_references;
load from geometry_columns insert into geometry_columns;
load from st_units_of_measure insert into st_units_of_measure;
load from se_metadatatable insert into se_metadatatable;
load from se_views insert into se_views;
load from ts_data_location insert into ts_data_location;
load from tab insert into tab;
load from warehouses insert into warehouses;
load from classes insert into classes;
load from dbms_alert_events insert into dbms_alert_events;
load from dbms_alert_registered insert into dbms_alert_registered;
load from dbms_alert_signaled insert into dbms_alert_signaled;
load from employee insert into employee;
load from employee3 insert into employee3;
load from tab0 insert into tab0;
load from l_nextval insert into l_nextval;
load from stock3 insert into stock3;
load from test_bk insert into test_bk;
load from test4 insert into test4;
load from test1 insert into test1;
load from stock2_trans insert into stock2_trans;
load from catcopy insert into catcopy;
load from test insert into test;
load from test2 insert into test2;
load from test3 insert into test3;
load from stock2 insert into stock2;

프로시저 작성은 아래 문서를 참고했습니다.

https://stackoverflow.com/questions/31992388/using-an-unload-statement-in-an-informix-stored-procedure

저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

SQL로 액셀의 NETWORKDAYS 기능 구현하기 (0)	2020.04.22
Informix 12.10.xC14의 CHAR_LENGTH 함수 오류 (0)	2020.04.14
인포믹스의 통합 백업 암호화 기능 (0)	2020.03.31
rlwrap으로 dbaccess 편하게 사용하기 (0)	2020.03.27
Informix 12.10.xC14의 LPAD/RPAD 함수 오류 (0)	2020.03.20

인포믹스의 통합 백업 암호화 기능

@ipajama 2020. 3. 31. 10:40

2020. 3. 31. 10:40

안녕하세요. IBM 커뮤니티에서 인포믹스의 통합 백업 암호화(Integrated Backup Encryption) 기능에 대한 질문이 있어서 소개하려고 합니다.

질문의 요지는 암호화된 백업 파일의 압축이 잘 되는가 하는 것인데, PGP로 암호화된 파일은 압축이 잘 안되었던 모양입니다. 그래서 몇가지 상황을 가정하고 백업 암호화 기능의 작동 방식을 테스트 해보았습니다.

인포믹스의 통합 백업 암호화 기능은 14.10 버전부터 제공됩니다.

매뉴얼에서는 통합 백업 암호화 기능을 사용한다면 원격지의 키서버를 통해 백업 암호화 키를 생성할 것을 권장하고 있으나 여기서는 편의상 로컬 암호화 키를 사용하겠습니다. 로컬 암호화 키를 생성하는 방법은 IBM Knowledge Center의 아래 링크를 참고했습니다.

https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.bar.doc/ids_bar_local_encryption_key.htm

-- 1. 통합 백업 암호화 기능을 설정하지 않고 ontape 백업 수행
[informix@db2 backups]$ onstat -c | egrep '^BACKUP_FILTER|^RESTORE_FILTER|^BAR_ENC'
BACKUP_FILTER
RESTORE_FILTER
BAR_ENCRYPTION
[informix@db2 backups]$ ontape -s -L 0
10 percent done.
20 percent done.
30 percent done.
40 percent done.
100 percent done.
File created: /work1/informix/ids1410fc3/backups/db2_0_L0
Please label this tape as number 1 in the arc tape sequence.
This tape contains the following logical logs:
 247
Program over.
[informix@db2 backups]$ ls -la
total 266788
drwxrwxr-x.  3 informix informix        33 Mar 31 09:54 .
drwxr-xr-x. 28 informix informix      4096 Mar 21 22:30 ..
-rw-rw----.  1 informix informix 273186816 Mar 31 09:54 db2_0_L0
drwxrwxr-x.  2 informix informix       182 Mar 31 09:51 tmp
-- 2. 통합 백업 암호화 기능을 설정하고 ontape 백업 수행
[informix@db2 backups]$ openssl rand -base64 24 > /work1/informix/ids1410fc3/etc/l_key192
[informix@db2 backups]$ onstat -c | egrep '^BACKUP_FILTER|^RESTORE_FILTER|^BAR_ENC'
BACKUP_FILTER
RESTORE_FILTER
BAR_ENCRYPTION keyfile=/work1/informix/ids1410fc3/etc/l_key192,cipher=aes192
[informix@db2 backups]$ ontape -s -L 0
The backup volume will be encrypted.
10 percent done.
20 percent done.
30 percent done.
40 percent done.
100 percent done.
File created: /work1/informix/ids1410fc3/backups/db2_0_L0
Please label this tape as number 1 in the arc tape sequence.
This tape contains the following logical logs:
 247
Program over.
[informix@db2 backups]$ ls -la
total 533572
drwxrwxr-x.  3 informix informix        65 Mar 31 09:55 .
drwxr-xr-x. 28 informix informix      4096 Mar 21 22:30 ..
-rw-rw----.  1 informix informix 273186816 Mar 31 09:54 db2_0_20200331_095432_L0
-rw-rw----.  1 informix informix 273186816 Mar 31 09:55 db2_0_L0
drwxrwxr-x.  2 informix informix       182 Mar 31 09:51 tmp
-- 3. 백업 파일 내용 확인
[informix@db2 backups]$ strings db2_0_20200331_095432_L0 | head -30
Archive Backup TapeIBM Informix Dynamic Server Version 14.10.FC3DETue Mar 31 09:54:32 2020informix/dev/pts/0
/work1/informix/ids1410fc3/backups/
rootdbs                                                                                                                         plog                                                                                                                            llog                                                                                                                            datadbs1                                                                                                                        datadbs2                                                                                                                        datadbs3                                                                                                                        data8dbs1                                                                                                                       data8dbs2                                                                                                                       data8dbs3                                                                                                                       sbspace1
IBM Informix Dynamic Server Copyright 2001, 2019  IBM Corporation
14.10.xC2
        F"01-2020
ROOTNAME rootdbs
ROOTPATH /work1/informix/ids1410fc3/storage/rootdbs
ROOTOFFSET 0
ROOTSIZE 157696
MIRROR 0
MIRRORPATH /work1/informix/ids1410fc3/tmp/demo_on.root_mirror
MIRROROFFSET 0
DBSERVERNAME ol_informix1410
SERVERNUM 0
MSGPATH /work1/informix/ids1410fc3/ol_informix1410.log
TAPEDEV /work1/informix/ids1410fc3/backups
TAPESIZE 0
TAPEBLK 32
LTAPEDEV /dev/null
LTAPESIZE 0
LTAPEBLK 32
PHYSFILE 65430
PHYSBUFF 512
LOGFILES 20
LOGSIZE 6144
LOGBUFF 256
DYNAMIC_LOGS 2
LTXHWM 70
LTXEHWM 80
[informix@db2 backups]$ strings db2_0_L0 | head -30
Archive Backup TapeIBM Informix Dynamic Server Version 14.10.FC3DETue Mar 31 09:55:07 2020informix/dev/pts/0
/work1/informix/ids1410fc3/backups/
aes192
DQZQ
QX9p
a5b(
H%*g
wecI
)rO0"
z\$b
#_t;
azXX
aW`g
$9s~f
{Rus
T<.u
(;*&
[!wS
{'dT
g9c%
k#|~
G)9)1
>X2B
CnSq
]SJ~
kmt9
ol!n
>JMsy
b!9~`
[C4R
-- 4. 통합 백업 암호화 기능 및 BACKUP_FILTER, RESTORE_FILTER를 설정하고 ontape 백업 수행
[informix@db2 backups]$ vi $INFORMIXDIR/etc/$ONCONFIG
[informix@db2 backups]$ onstat -c | egrep '^BACKUP_FILTER|^RESTORE_FILTER|^BAR_ENC'
BACKUP_FILTER /bin/gzip
RESTORE_FILTER /bin/gunzip
BAR_ENCRYPTION keyfile=/work1/informix/ids1410fc3/etc/l_key192,cipher=aes192
[informix@db2 backups]$ ontape -s -L 0
The backup volume will be encrypted.
Using the backup and restore filter /bin/gzip.
10 percent done.
20 percent done.
30 percent done.
40 percent done.
100 percent done.
File created: /work1/informix/ids1410fc3/backups/db2_0_L0
Please label this tape as number 1 in the arc tape sequence.
This tape contains the following logical logs:
 247
Program over.
[informix@db2 backups]$ ls -la
total 548100
drwxrwxr-x.  3 informix informix        97 Mar 31 09:56 .
drwxr-xr-x. 28 informix informix      4096 Mar 21 22:30 ..
-rw-rw----.  1 informix informix 273186816 Mar 31 09:54 db2_0_20200331_095432_L0
-rw-rw----.  1 informix informix 273186816 Mar 31 09:55 db2_0_20200331_095507_L0
-rw-rw----.  1 informix informix  14876672 Mar 31 09:56 db2_0_L0
drwxrwxr-x.  2 informix informix       182 Mar 31 09:51 tmp
-- 5. 통합 백업 암호화 기능 및 BACKUP_FILTER, RESTORE_FILTER를 설정하고 ontape 리스토어 수행
[informix@db2 backups]$ onmode -ky
[informix@db2 backups]$ onstat -c | egrep '^BACKUP_FILTER|^RESTORE_FILTER|^BAR_ENC'
BACKUP_FILTER /bin/gzip
RESTORE_FILTER /bin/gunzip
BAR_ENCRYPTION keyfile=/work1/informix/ids1410fc3/etc/l_key192,cipher=aes192
[informix@db2 backups]$ ontape -r
Restore will use level 0 archive file /work1/informix/ids1410fc3/backups/db2_0_L0. Press Return to continue ...
The volume to restore is encrypted.
Using the backup and restore filter /bin/gunzip.
Archive Tape Information
Tape type:      Archive Backup Tape
Online version: IBM Informix Dynamic Server Version 14.10.FC3DE
Archive date:   Tue Mar 31 09:56:06 2020
User id:        informix
Terminal id:    /dev/pts/0
Archive level:  0
Tape device:    /work1/informix/ids1410fc3/backups/
Tape blocksize (in k): 32
Tape size (in k): system defined for directory
Tape number in series: 1
Backup filter:  /bin/gzip
Spaces to restore:1 [rootdbs                                                                                                                         ]
2 [plog                                                                                                                            ]
3 [llog                                                                                                                            ]
4 [datadbs1                                                                                                                        ]
5 [datadbs2                                                                                                                        ]
6 [datadbs3                                                                                                                        ]
7 [data8dbs1                                                                                                                       ]
8 [data8dbs2                                                                                                                       ]
9 [data8dbs3                                                                                                                       ]
10 [sbspace1                                                                                                                        ]
Archive Information
IBM Informix Dynamic Server Copyright 2001, 2019  IBM Corporation
Initialization Time       01/08/2020 22:26:54
System Page Size          2048
Version                   31
Index Page Logging        OFF
Archive CheckPoint Time   03/31/2020 09:56:05
Dbspaces
number   flags    fchunk   nchunks  flags    owner                            name
1        10000001 1        2        N   AE   informix                         rootdbs                                                                                                        
2        11000001 2        1        N P AE   informix                         plog                                                                                                           
3        10000001 3        1        N   AE   informix                         llog                                                                                                           
4        10000001 4        1        N   AE   informix                         datadbs1                                                                                                       
5        10000001 5        1        N   AE   informix                         datadbs2                                                                                                       
6        10000001 6        1        N   AE   informix                         datadbs3                                                                                                       
7        10000001 7        1        N   AE   informix                         data8dbs1                                                                                                      
8        10000001 8        1        N   AE   informix                         data8dbs2                                                                                                      
9        10000001 9        1        N   AE   informix                         data8dbs3                                                                                                      
10       10002001 10       1        N T AE   informix                         tmpdbspace                                                                                                     
11       10008001 11       1        N S AE   informix                         sbspace1                                                                                                       
12       1000a001 12       1        N U AE   informix                         tmpsbspace                                                                                                     
Chunks
chk/dbs offset   size     free     bpages   flags pathname
1   1   0        78848    9236              PO--- /work1/informix/ids1410fc3/storage/rootdbs
2   2   0        32768    0                 PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_plog_p_1
3   3   0        70244    4791              PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_llog_p_1
4   4   0        32768    21236             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_datadbs1_p_1
5   5   0        32768    32715             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_datadbs2_p_1
6   6   0        32768    32715             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_datadbs3_p_1
7   7   0        32768    32556             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_data8dbs1_p_1
8   8   0        32768    32556             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_data8dbs2_p_1
9   9   0        32768    32556             PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_data8dbs3_p_1
10  10  0        429812   429376            PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_2_tmpdbspace_p_1
11  11  0        32768    8466              POS-- /work1/informix/ids1410fc3/storage/ol_informix1410_2_sbspace1_p_1
12  12  0        32768    8466              POS-- /work1/informix/ids1410fc3/storage/ol_informix1410_2_tmpsbspace_p_1
13  1   0        60314    72                PO--E /work1/informix/ids1410fc3/storage/ol_informix1410_rootdbs_p_1
Continue restore? (y/n)y
Do you want to back up the logs? (y/n)n
The volume to restore is encrypted.
Using the backup and restore filter /bin/gunzip.
Warning: Parameter's user-configured value was adjusted. (DS_MAX_SCANS)
Warning: Parameter's user-configured value was adjusted. (ONLIDX_MAXMEM)
Restore a level 1 archive (y/n) n
Do you want to restore log tapes? (y/n)n
/work1/informix/ids1410fc3/bin/onmode -sy
Program over.
[informix@db2 backups]$ onstat -m
IBM Informix Dynamic Server Version 14.10.FC3DE -- Quiescent -- Up 00:00:26 -- 566920 Kbytes
Message Log File: /work1/informix/ids1410fc3/ol_informix1410.log
10:11:58  Physical Recovery Complete: 0 Pages Examined, 0 Pages Restored.
10:11:58  Clearing encrypted primary chunk 10 before initialization...
10:11:58  Clearing encrypted primary chunk 12 before initialization...
10:11:58  Logical Recovery Started.
10:11:58  72 recovery worker threads will be started.
10:12:00  Logical Recovery has reached the transaction cleanup phase.
10:12:00  Logical Recovery Complete.
          0 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks
10:12:01  Bringing system to Quiescent Mode with no Logical Restore.
10:12:02  Quiescent Mode
10:12:02  Checkpoint Completed:  duration was 0 seconds.
10:12:02  Tue Mar 31 - loguniq 247, logpos 0x1ee018, timestamp: 0xeb6d094 Interval: 264
10:12:02  Maximum server connections 0
10:12:02  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 0, Llog used 1
10:12:04  Defragmenter cleaner thread now running
10:12:04  Defragmenter cleaner thread cleaned:0 partitions

테스트 결과로 보면 암호화된 백업본이라도 압축이 잘 되는 것을 확인할 수 있습니다.

참고 사이트 :

https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.bar.doc/ids_bar_integrated_encryption.htm

저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

Informix 12.10.xC14의 CHAR_LENGTH 함수 오류 (0)	2020.04.14
프로시저로 UNLOAD 실행하기 (0)	2020.04.09
rlwrap으로 dbaccess 편하게 사용하기 (0)	2020.03.27
Informix 12.10.xC14의 LPAD/RPAD 함수 오류 (0)	2020.03.20
OLAP함수로 가장 자주 나타나는 문자열 검색 (0)	2020.03.19

rlwrap으로 dbaccess 편하게 사용하기

@ipajama 2020. 3. 27. 12:30

2020. 3. 27. 12:30

안녕하세요. IBM 커뮤니티에서 DBACCESS_COLUMNS 환경변수에 대한 질문을 올렸다가

Benjamin Thompson의 답변을 통해 rlwrap (readline wrapper) 이라는 유틸리티를 알게 되었습니다.

사실 알려진지는 꽤 된 것 같습니다만 저는 뒤늦게 알게되었네요 ^^;

리눅스나 유닉스용으로 설치패키지가 제공되네요. 리눅스의 경우는 yum 같은 툴을 이용해 설치하실 수 있습니다.

아래는 rlwrap을 이용해서 dbaccess를 실행한 과정입니다.

[informix@db2 ids1410fc3]$ alias dbaccess='DBACCESS_COLUMNS=1024 rlwrap dbaccess'
[informix@db2 ids1410fc3]$ dbaccess stores_demo -
Database selected.
> select * from customer;
customer_num fname           lname           company              address1             address2             city            state zipcode phone
         101 Ludwig          Pauli           All Sports Supplies  213 Erstwild Court                        Sunnyvale       CA    94086   408-789-8075
         102 Carole          Sadler          Sports Spot          785 Geary St                              San Francisco   CA    94117   415-822-1289
         103 Philip          Currie          Phil's Sports        654 Poplar           P. O. Box 3498       Palo Alto       CA    94303   415-328-4543
         104 Anthony         Higgins         Play Ball!           East Shopping Cntr.  422 Bay Road         Redwood City    CA    94026   415-368-1100
         105 Raymond         Vector          Los Altos Sports     1899 La Loma Drive                        Los Altos       CA    94022   415-776-3249
         106 George          Watson          Watson & Son         1143 Carver Place                         Mountain View   CA    94063   415-389-8789
         107 Charles         Ream            Athletic Supplies    41 Jordan Avenue                          Palo Alto       CA    94304   415-356-9876
         108 Donald          Quinn           Quinn's Sports       587 Alvarado                              Redwood City    CA    94063   415-544-8729
         109 Jane            Miller          Sport Stuff          Mayfair Mart         7345 Ross Blvd.      Sunnyvale       CA    94086   408-723-8789
         110 Roy             Jaeger          AA Athletics         520 Topaz Way                             Redwood City    CA    94062   415-743-3611
         111 Frances         Keyes           Sports Center        3199 Sterling Court                       Sunnyvale       CA    94085   408-277-7245
         112 Margaret        Lawson          Runners & Others     234 Wyandotte Way                         Los Altos       CA    94022   415-887-7235
         113 Lana            Beatty          Sportstown           654 Oak Grove                             Menlo Park      CA    94025   415-356-9982
         114 Frank           Albertson       Sporting Place       947 Waverly Place                         Redwood City    CA    94062   415-886-6677
         115 Alfred          Grant           Gold Medal Sports    776 Gary Avenue                           Menlo Park      CA    94025   415-356-1123
         116 Jean            Parmelee        Olympic City         1104 Spinosa Drive                        Mountain View   CA    94040   415-534-8822
         117 Arnold          Sipes           Kids Korner          850 Lytton Court                          Redwood City    CA    94063   415-245-4578
         118 Dick            Baxter          Blue Ribbon Sports   5427 College                              Oakland         CA    94609   415-655-0011
         119 Bob             Shorter         The Triathletes Club 2405 Kings Highway                        Cherry Hill     NJ    08002   609-663-6079
         120 Fred            Jewell          Century Pro Shop     6627 N. 17th Way                          Phoenix         AZ    85016   602-265-8754
         121 Jason           Wallack         City Sports          Lake Biltmore Mall   350 W. 23rd Street   Wilmington      DE    19898   302-366-7511
         122 Cathy           O'Brian         The Sporting Life    543 Nassau Street                         Princeton       NJ    08540   609-342-0054
         123 Marvin          Hanlon          Bay Sports           10100 Bay Meadows Ro Suite 1020           Jacksonville    FL    32256   904-823-4239
         124 Chris           Putnum          Putnum's Putters     4715 S.E. Adams Blvd Suite 909C           Bartlesville    OK    74006   918-355-2074
         125 James           Henry           Total Fitness Sports 1450 Commonwealth Av                      Brighton        MA    02135   617-232-4159
         126 Eileen          Neelie          Neelie's Discount Sp 2539 South Utica Str                      Denver          CO    80219   303-936-7731
         127 Kim             Satifer         Big Blue Bike Shop   Blue Island Square   12222 Gregory Street Blue Island     NY    60406   312-944-5691
         128 Frank           Lessor          Phoenix University   Athletic Department  1817 N. Thomas Road  Phoenix         AZ    85008   602-533-1817
28 row(s) retrieved.
> select * from customer;

rlwrap을 통해 dbaccess를 실행하면 프롬프트에서 화살표키를 사용해 이전에 수행한 문장을 불러올 수 있습니다.

또한 12.10.xC9 버전부터 dbaccess의 라인이 80자 이상 출력이 가능하도록 DBACCESS_COLUMNS라는 환경변수가 추가되었습니다. 이 기능을 dbaccess를 실행할 때 같이 응용하면 좋을 것 같네요.

저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

프로시저로 UNLOAD 실행하기 (0)	2020.04.09
인포믹스의 통합 백업 암호화 기능 (0)	2020.03.31
Informix 12.10.xC14의 LPAD/RPAD 함수 오류 (0)	2020.03.20
OLAP함수로 가장 자주 나타나는 문자열 검색 (0)	2020.03.19
USE_FALLOCATE 설정으로 Chunk 빠르게 만들기 (0)	2020.03.10

OLAP함수로 가장 자주 나타나는 문자열 검색

@ipajama 2020. 3. 19. 13:20

2020. 3. 19. 13:20

안녕하세요. 요즘 IBM 커뮤니티에서 열심히 댓글놀이를 하고 있는데요. 며칠전에 Informix에서 문자열에 대한 최빈값(MODE)을 구하는 방법에 대한 질문글이 올라왔습니다. 최빈값은 통계학에서 쓰는 용어로 액셀이나 분석 솔루션에서 MODE라는 함수로 사용되기도 합니다. 아래는 위키백과의 최빈값에 대한 설명입니다.

최빈값(最頻-), 모드(mode)는 통계학 용어로, 가장 많이 관측되는 수, 즉 주어진 값 중에서 가장 자주 나오는 값이다. 예를 들어, {1, 3, 6, 6, 6, 7, 7, 12, 12, 17}의 최빈값은 6이다. 최빈값은 산술 평균과 달리 유일한 값이 아닐 수도 있다.

또한 주어진 자료나 관측치의 값이 모두 다른 경우에는 존재하지 않는다.

주어진 자료에서 평균이나 중앙값을 구하기 어려운 경우에 특히 유용하다.

그런데 최빈값이라 함은 숫자가 대상이지 문자열은 아닙니다. 그리고 SQL에서는 MODE라는 함수가 따로 제공되는 것도 아니어서 문자열을 대상으로 하려면 별도의 로직을 구현해야합니다.

질문에서 제시하는 샘플데이터와 원하는 결과세트는 아래와 같습니다.

<샘플데이터>

Name	dob	score
JESSE	1992/10/27	10
JESSE	1992/10/27	20
JESSE	1992/11/06	30
JESSE	1992/11/11	40
JESSICA	1992/03/11	50
JESSICA	1992/11/03	60
JESSICA	1992/10/29	70
JESSICA	1992/11/10	80
JESSICA	1992/11/30	90
JESSICA	1992/11/12	10
JESSICA	1992/12/07	20
JESSICA	1992/12/09	30

<결과세트>

Name	dob	avg(score)
JESSE	1992/12/07
JESSICA	1992/11/??

결과세트에서 요구하는 사항은 다음과 같습니다.

1) 이름별로 가장 연도와 월 기준으로 가장 많은 값을 찾는다.

2) 일자는 해당 월의 아무 일자나 허용한다.

3) 1에서 찾은 값의 개수가 같은 것이 있으면 연도, 월, 일자를 같은 값을 우선한다.

그래서 이리저리 검색을 해서 아래와 같은 쿼리를 만들었습니다.

WITH t1 AS
(
SELECT 'JESSE' Name, '1992/10/27' dob, 10 score 
UNION ALL SELECT 'JESSE', '1992/10/27', 20 
UNION ALL SELECT 'JESSE', '1992/11/06', 30 
UNION ALL SELECT 'JESSE', '1992/11/11', 40 
UNION ALL SELECT 'JESSICA', '1992/03/11', 50 
UNION ALL SELECT 'JESSICA', '1992/11/03', 60 
UNION ALL SELECT 'JESSICA', '1992/10/29', 70 
UNION ALL SELECT 'JESSICA', '1992/11/10', 80 
UNION ALL SELECT 'JESSICA', '1992/11/30', 90 
UNION ALL SELECT 'JESSICA', '1992/11/12', 10 
UNION ALL SELECT 'JESSICA', '1992/12/07', 20  
UNION ALL SELECT 'JESSICA', '1992/12/09', 30 
)
SELECT a.Name,
       a.dob,
       a.avg	  
  FROM (SELECT t1.Name, 
               t1.dob, 
               ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rn, 
               AVG(t1.score) OVER (PARTITION BY t1.Name) avg
          FROM t1,
               (SELECT t1.Name, 
                       t1.dob[1,7], 
                       RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk
                  FROM t1
                 GROUP BY t1.Name, t1.dob[1,7]
               ) t2
         WHERE t2.rk = 1
           AND t1.Name = t2.Name
           AND t1.dob like t2.dob||'%'
         GROUP BY t1.Name, t1.dob, t1.score
       ) a
 WHERE rn =1

쿼리의 흐름은 다음과 같습니다.

1) 이름을 기준으로 연도,월별 개수가 가장 많은 것을 구합니다. 가장 많은 개수가 같을 경우를 고려해 RANK 함수를 사용합니다. 그러면 같은 랭킹인 경우는 모두 첫번째가 됩니다.

2) 1에서 구한 데이터를 기준으로 일자를 포함하여 개수가 가장 많은 것을 구합니다. 여기선 월별로 일자 하나만 선택해야하므로 ROW_NUMBER를 사용합니다.

아래는 실행한 결과입니다.

[informix@db2 skjeong]$ dbaccess stores_demo mode.sql
Database selected.
name    dob                     avg
JESSE   1992/10/27 25.0000000000000
JESSICA 1992/11/30 60.0000000000000
2 row(s) retrieved.

인포믹스에서의 예제이긴 하지만 대부분의 RDBMS에서 지원하는 함수이기 때문에 필요한 경우 적용하기는 어렵지 않으실겁니다. 혹시 이 글을 읽는 분들께서 더 나은 방법을 알고 계시다면 조언 부탁드리겠습니다.

제가 참고한 사이트는 아래와 같습니다.

https://www.mssqltips.com/sqlservertip/3543/calculate-the-statistical-mode-in-sql-server-using-tsql/

데이터베이스 사랑넷에 자문을 구해 쿼리를 수정해보고, 새로운 쿼리도 알게 됐습니다.

위의 요구사항보다 조건을 좀 더 구체적으로 설정했습니다.

1) 평균은 월평균이 되어야한다. 이전 것은 이름만 기준으로한 평균

2) 가장 많은 갯수가 동일할 경우의 우선순위는? (빠른 날짜, 느린 날짜, 낮은 평균, 높은 평균 등)

여기서는 빠른 날짜를 기준으로 합니다.

아래는 수정된 쿼리입니다. 제가 수정한 쿼리는 마농님의 조언에 따라 약간 수정한 것입니다.

테스트를 위해 WITH절에 샘플 레코드를 추가했습니다.

--- 제가 수정한 쿼리
WITH t1 AS
(
SELECT 'JESSE' Name , '1992/08/06' dob, 30 score
UNION ALL SELECT 'JESSE', '1992/08/06', 40 
UNION ALL SELECT 'JESSE', '1992/07/27', 10
UNION ALL SELECT 'JESSE', '1992/07/27', 20 
UNION ALL SELECT 'JESSE', '1992/11/06', 30 
UNION ALL SELECT 'JESSE', '1992/11/11', 40 
UNION ALL SELECT 'JESSICA', '1992/03/11', 50 
UNION ALL SELECT 'JESSICA', '1992/11/03', 60 
UNION ALL SELECT 'JESSICA', '1992/10/29', 70 
UNION ALL SELECT 'JESSICA', '1992/11/10', 80 
UNION ALL SELECT 'JESSICA', '1992/11/30', 90 
UNION ALL SELECT 'JESSICA', '1992/11/12', 10 
UNION ALL SELECT 'JESSICA', '1992/12/07', 20  
UNION ALL SELECT 'JESSICA', '1992/12/09', 30 
)
SELECT a.Name,
       a.dob,
       a.avg	  
  FROM (SELECT t1.Name, 
               t1.dob, 
               ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC, t1.dob ASC) rn, 
               AVG(t1.score) OVER (PARTITION BY t1.Name, t1.dob[1,7] ) avg
          FROM t1,
               (SELECT t1.Name, 
                       t1.dob[1,7], 
                       RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk
                  FROM t1
                 GROUP BY t1.Name, t1.dob[1,7]
               ) t2
         WHERE t2.rk = 1
           AND t1.Name = t2.Name
           AND t1.dob like t2.dob||'%'
         GROUP BY t1.Name, t1.dob, t1.score
       ) a
 WHERE rn =1
--- 마농님의 쿼리 (WITH문 생략)
SELECT *
  FROM (SELECT name
             , dob
             , avg_m
             , ROW_NUMBER() OVER(
               PARTITION BY name ORDER BY cnt_m DESC, cnt_d DESC, dob) rn
          FROM (SELECT name
                     , dob
                     , COUNT(*)   OVER(PARTITION BY name, dob[1,7]) cnt_m
                     , COUNT(*)   OVER(PARTITION BY name, dob     ) cnt_d
                     , AVG(score) OVER(PARTITION BY name, dob[1,7]) avg_m
                  FROM t1
                ) a
        ) a
 WHERE rn = 1
;

제가 올린 질문글은 아래 링크에서 참고해주세요.

http://database.sarang.net/?inc=read&aid=3484&criteria=informix&subcrit=qna&id=&limit=20&keyword=&page=1

저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

rlwrap으로 dbaccess 편하게 사용하기 (0)	2020.03.27
Informix 12.10.xC14의 LPAD/RPAD 함수 오류 (0)	2020.03.20
USE_FALLOCATE 설정으로 Chunk 빠르게 만들기 (0)	2020.03.10
프로시저의 PDQ 값 확인하기 (0)	2020.02.27
Extent 최대 개수 제한 증가 (0)	2020.02.17

USE_FALLOCATE 설정으로 Chunk 빠르게 만들기

@ipajama 2020. 3. 10. 13:31

2020. 3. 10. 13:31

안녕하세요. 인포믹스 14.10.xC2 버전부터 파일시스템의 Chunk를 빠르게 생성하는 기능이 추가되었습니다.

기존에는 파일시스템에서 Chunk를 생성하면 크기가 클수록 오래걸렸는데요.

이 기능을 사용하면 onspaces 명령을 실행하는 즉시 Chunk가 생성됩니다.

Configuration 설명을 보면 다음과 같이 나와있습니다.

Configuration Parameter Info
id   name                      type    maxlen   units   rsvd  tunable
108  USE_FALLOCATE             BOOL    2                      *
     default : 1
     onconfig: 1
     current : 1
     This parameter is undocumented.
     Description:
     Enabling USE_FALLOCATE allows the server to allocate space for a new
     cooked chunk much more quickly by making use of posix_fallocate(). This
     is only supported on platforms which have posix_fallocate() available.
     A performance improvement can only be expected when the underlying file
     system supports the required functionality. Please refer to your operating
     system vendor documentation for further details.

posix_fallocate 함수를 살펴보면 리눅스 커널에서만 지원되는 함수인 것 같습니다. 혹시 잘못된 내용이라면 알려주세요.

xfs , ext4 , btrfs, tmpfs 등의 파일시스템에서 이 동작을 지원합니다.

아래는 실제로 CentOS 7.6의 Informix 14.10.FC3 환경에서 테스트해본 내용입니다.

onspaces 명령을 실행하자마자 청크가 생성된 것을 확인할 수 있습니다.

[informix@db2 ids1410fc3]$ ls -la /work1/informix/storage/test
-rw-rw----. 1 informix informix 0 Mar 10 09:09 /work1/informix/storage/test
[informix@db2 ids1410fc3]$ time onspaces -c -d test -p /work1/informix/storage/test -o 0 -s 6000000
Verifying physical disk space, please wait ...
Space successfully added.
** WARNING **  A level 0 archive of Root DBSpace will need to be done.
real    0m0.094s
user    0m0.011s
sys     0m0.025s
[informix@db2 ids1410fc3]$ ls -la /work1/informix/storage/test
-rw-rw----. 1 informix informix 6144000000 Mar 10 09:18 /work1/informix/storage/test

해당 기능에 대해서는 아직 IBM Knowledge Center에는 나와있지 않네요.

아 그리고 참고로 스토리지 암호화 기능(DISK_ENCRYPTION)이 활성화 된 상태에서는 이 옵션이 적용되지 않는 것 같습니다.

저작자표시 비영리 변경금지

'Informix > informix reference' 카테고리의 다른 글

Informix 12.10.xC14의 LPAD/RPAD 함수 오류 (0)	2020.03.20
OLAP함수로 가장 자주 나타나는 문자열 검색 (0)	2020.03.19
프로시저의 PDQ 값 확인하기 (0)	2020.02.27
Extent 최대 개수 제한 증가 (0)	2020.02.17
Index의 페이지 수 제한 증가 (0)	2020.02.16

PREV 이전 1 2 3 4 5 6 ···9 NEXT 다음

pajama

Informix

Informix 14.10.xC4부터 SQL문장의 쿼리 플랜을 확인하는 getExplain 함수 제공

'Informix > informix reference' 카테고리의 다른 글

Informix 14.10.xC4의 MSG_DATE 설정 변화

'Informix > informix reference' 카테고리의 다른 글

Informix 14.10.xC4의 새로운 onstat -g top 옵션

'Informix > informix reference' 카테고리의 다른 글

Informix의 Smart Trigger 구현 따라해보기

'Informix > informix reference' 카테고리의 다른 글

SQL로 액셀의 NETWORKDAYS 기능 구현하기

'Informix > informix reference' 카테고리의 다른 글

프로시저로 UNLOAD 실행하기

'Informix > informix reference' 카테고리의 다른 글

인포믹스의 통합 백업 암호화 기능

'Informix > informix reference' 카테고리의 다른 글

rlwrap으로 dbaccess 편하게 사용하기

'Informix > informix reference' 카테고리의 다른 글

OLAP함수로 가장 자주 나타나는 문자열 검색

'Informix > informix reference' 카테고리의 다른 글

USE_FALLOCATE 설정으로 Chunk 빠르게 만들기

'Informix > informix reference' 카테고리의 다른 글

+ Recent posts

티스토리툴바