defineworkload: Creación de cargas de trabajo de consulta

Después de pasar a la API defineworkload una lista de sentencias SQL, la API crea y devuelve el nombre de una nueva carga de trabajo de consulta.

Parámetros de entrada

Pase los siguientes pares de claves y valores a la API mediante este URL: https://localhost/tuning/defineworkload

Tabla 1. Parámetros de entrada
Clave Valor Valores posibles
dbType El tipo de servidor de datos. ZOS
dbHost El host del servidor de datos. Nombre de dominio o dirección IP
dbPort El número de puerto del servidor de datos  
dbName El nombre del servidor de datos  
dbUser El ID que se ha de utilizar para conectarse al servidor de datos.  
dbPassword La contraseña que se ha de utilizar para conectarse al servidor de datos.  
fileContent El texto de las sentencias SQL que se han de incluir en la nueva carga de trabajo de consulta.  
workloadName El nombre que se asigna a la nueva carga de trabajo de consultas. Si ya se está utilizando el nombre, se añade "_1" al nombre de la nueva carga de trabajo de consulta .  
delimiter El delimitador de las sentencias SQL que se listan en el valor de la clave fileContent.  

Parámetros de salida

La API devuelve los siguientes pares de claves y valores:

Tabla 2. Parámetros de salida
Clave Valor
code El código de retorno de la API web.
0
Completado con avisos
4
Completado con avisos
8
Finalizado sin completar debido a un error
output El nombre de la nueva carga de trabajo de consulta.
message Cualquier mensaje de error o de aviso.
exception Información acerca de la excepción, si se ha producido una. Esta información puede ser útil para el servicio de soporte de IBM si trabaja con ellos para la resolución de un problema.

Ejemplo

En este ejemplo se utiliza cURL para pasar sentencias SQL a la API defineworkload, la cual devuelve el nombre de la nueva carga de trabajo de consulta.

Entrada
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
Salida
{ 
"code":0,
"message":"Sample_Workload"
}

Comentarios