defineworkload: 쿼리 워크로드 작성

defineworkload API에 SQL문 목록을 전달하면 API는 새 쿼리 워크로드의 이름을 작성하여 리턴합니다.

입력 매개변수

URL을 사용하여 API에 다음 키 및 값 쌍을 전달하십시오. https://localhost/tuning/defineworkload

표 1. 입력 매개변수
가능한 값
dbType 데이터 서버 유형 ZOS
dbHost 데이터 서버의 호스트입니다. 도메인 이름 또는 IP 주소
dbPort 데이터 서버의 포트 번호  
dbName 데이터 서버 이름  
dbUser 데이터 서버에 연결할 때 사용할 ID입니다.  
dbPassword 데이터 서버에 연결할 때 사용할 비밀번호입니다.  
fileContent 새 쿼리 워크로드에 포함시킬 SQL문의 텍스트입니다.  
workloadName 새 쿼리 워크로드에 제공할 이름입니다. 이름이 이미 사용되고 있는 경우, 새 쿼리 워크로드 이름에는 "_1"이 추가됩니다.  
delimiter fileContent 키 값에 나열된 SQL문의 구분 기호입니다.  

출력 매개변수

API는 다음 키 및 값 쌍을 리턴합니다.

표 2. 출력 매개변수
code 웹 API의 리턴 코드입니다.
0
경고 없이 완료됨
4
완료되었지만 경고가 표시됨
8
오류로 인해 완료되지 않고 종료됨
output 새 쿼리 워크로드의 이름입니다.
message 오류 또는 경고 메시지입니다.
exception 예외가 발생한 경우 예외에 대한 정보입니다. IBM 지원 센터와 작업하여 문제점을 해결하는 경우 이 정보는 IBM 지원 센터에 유용합니다.

예제

이 예제에서는 cURL을 사용하여 defineworkload API에 SQL문을 전달하며, 이 API는 새 쿼리 워크로드의 이름을 리턴합니다.

입력
curl 
--insecure 
-X POST 
-H "Content-Type: application/json; charset=UTF-8"
--data "{ 
   dbType:ZOS, 
   dbHost:host, 
   dbPort:port_number, 
   dbName:subsystem, 
   dbUser:user_ID, 
   dbPassword:password, 
   fileContent:\"
select 
  cd_gender,
  cd_marital_status,
  cd_education_status,
  count(*),
  cd_purchase_estimate,
  count(*),
  cd_credit_rating,
  count(*),
  cd_dep_count,
  count(*),
  cd_dep_employed_count,
  count(*),
  cd_dep_college_count,
  count(*)
 from
  customer c,customer_address ca,customer_demographics
 where
  c.c_current_addr_sk = ca.ca_address_sk and
  ca_county in ('Rush County','Toole County','Jefferson County','Dona Ana 
        County','La Porte County') and
  cd_tpcds_sk = c.c_current_ctpcds_sk and 
  exists (select *
     from store_sales,date_dim
     where c.c_customer_sk = ss_customer_sk and
      ss_sold_date_sk = d_date_sk and
      d_year = 2002 and
      d_moy between 1 and 1+3) and
   (exists (select *
       from web_sales,date_dim
       where c.c_customer_sk = ws_bill_customer_sk and
        ws_sold_date_sk = d_date_sk and
        d_year = 2002 and
        d_moy between 1 ANd 1+3) or 
    exists (select * 
       from catalog_sales,date_dim
       where c.c_customer_sk = cs_ship_customer_sk and
        cs_sold_date_sk = d_date_sk and
        d_year = 2002 and
        d_moy between 1 and 1+3))
 group by cd_gender,
     cd_marital_status,
     cd_education_status,
     cd_purchase_estimate,
     cd_credit_rating,
     cd_dep_count,
     cd_dep_employed_count,
     cd_dep_college_count
 order by cd_gender,
     cd_marital_status,
     cd_education_status,
     cd_purchase_estimate,
     cd_credit_rating,
     cd_dep_count,
     cd_dep_employed_count,
     cd_dep_college_count;
with frequent_ss_items as 
 (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date 
      solddate,
       count(*) cnt
  from store_sales
      ,date_dim 
      ,item
  where ss_sold_date_sk = d_date_sk
    and ss_item_sk = i_item_sk 
    and d_year in (1999,1999+1,1999+2,1999+3)
  group by substr(i_item_desc,1,30),i_item_sk,d_date
  having count(*) >4),
 max_store_sales as
 (select max(csales) tpcds_cmax 
  from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales
   from store_sales
       ,customer
       ,date_dim 
   where ss_customer_sk = c_customer_sk
    and ss_sold_date_sk = d_date_sk
    and d_year in (1999,1999+1,1999+2,1999+3) 
   group by c_customer_sk) x),
 best_ss_customer as
 (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales
  from store_sales
      ,customer
  where ss_customer_sk = c_customer_sk
  group by c_customer_sk
  having sum(ss_quantity*ss_sales_price) > 0.95 * (select
  *
from
 max_store_sales))
 select sum(sales)
 from ((select cs_quantity*cs_list_price sales
       from catalog_sales
      ,date_dim 
       where d_year = 1999 
    and d_moy = 3 
    and cs_sold_date_sk = d_date_sk 
    and cs_item_sk in (select item_sk from frequent_ss_items)
    and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer))
      union all
      (select ws_quantity*ws_list_price sales
       from web_sales 
      ,date_dim 
       where d_year = 1999 
    and d_moy = 3 
    and ws_sold_date_sk = d_date_sk 
    and ws_item_sk in (select item_sk from frequent_ss_items)
    and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer))) y
 ;

with frequent_ss_items as
 (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,
       count(*) cnt
  from store_sales
      ,date_dim
      ,item
  where ss_sold_date_sk = d_date_sk
    and ss_item_sk = i_item_sk
    and d_year in (1999,1999 + 1,1999 + 2,1999 + 3)
  group by substr(i_item_desc,1,30),i_item_sk,d_date
  having count(*) >4),
 max_store_sales as
 (select max(csales) tpcds_cmax
  from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales
   from store_sales
       ,customer
       ,date_dim 
   where ss_customer_sk = c_customer_sk
    and ss_sold_date_sk = d_date_sk
    and d_year in (1999,1999+1,1999+2,1999+3)
   group by c_customer_sk) x),
 best_ss_customer as
 (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales
  from store_sales
      ,customer
  where ss_customer_sk = c_customer_sk
  group by c_customer_sk
  having sum(ss_quantity*ss_sales_price) > 0.95 * (select
  *
 from max_store_sales))
 select c_last_name,c_first_name,sales
 from ((select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales
   from catalog_sales
       ,customer
       ,date_dim 
   where d_year = 1999 
    and d_moy = 3 
    and cs_sold_date_sk = d_date_sk 
    and cs_item_sk in (select item_sk from frequent_ss_items)
    and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer)
    and cs_bill_customer_sk = c_customer_sk 
       group by c_last_name,c_first_name)
      union all
      (select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales
       from web_sales
      ,customer
      ,date_dim 
       where d_year = 1999 
    and d_moy = 3 
    and ws_sold_date_sk = d_date_sk 
    and ws_item_sk in (select item_sk from frequent_ss_items)
    and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)
    and ws_bill_customer_sk = c_customer_sk
       group by c_last_name,c_first_name)) y;\"
   workloadName:\"Sample_Workload\",
   delimiter:\";\" 
   }"
https://localhost/tuning/defineworkload
출력
{ 
"code":0, 
"message":"Sample_Workload" 
}

피드백