이 URL을 사용하여 API에 다음 키 및 값 쌍을 전달하십시오. https://localhost/tuning/defineworkload
| 키 | 값 | 가능한 값 |
|---|---|---|
| dbType | 데이터 서버 유형 | ZOS |
| dbHost | 데이터 서버의 호스트입니다. | 도메인 이름 또는 IP 주소 |
| dbPort | 데이터 서버의 포트 번호 | |
| dbName | 데이터 서버 이름 | |
| dbUser | 데이터 서버에 연결할 때 사용할 ID입니다. | |
| dbPassword | 데이터 서버에 연결할 때 사용할 비밀번호입니다. | |
| fileContent | 새 쿼리 워크로드에 포함시킬 SQL문의 텍스트입니다. | |
| workloadName | 새 쿼리 워크로드에 제공할 이름입니다. 이름이 이미 사용되고 있는 경우, 새 쿼리 워크로드 이름에는 "_1"이 추가됩니다. | |
| delimiter | fileContent 키 값에 나열된 SQL문의 구분 기호입니다. |
API는 다음 키 및 값 쌍을 리턴합니다.
| 키 | 값 |
|---|---|
| code | 웹 API의 리턴 코드입니다.
|
| 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"
}