defineworkload : Création de charges de travail de requête

Une fois que vous avez transmis à l'API defineworkload une liste d'instructions SQL, l'API crée et renvoie le nom d'une nouvelle charge de travail de requête.

Paramètres d'entrée

Transmettez à l'API les paires de clés et de valeurs suivantes en utilisant l'URL : https://localhost/tuning/defineworkload

Tableau 1. Paramètres d'entrée
Clé Valeur Valeurs admises
dbType Type de serveur de données. ZOS
dbHost Hôte du serveur de données. Nom du domaine ou adresse IP.
dbPort Numéro de port du serveur de données.  
dbName Nom du serveur de données.  
dbUser ID à utiliser lors de la connexion au serveur de données.  
dbPassword Mot de passe à utiliser lors d'une connexion au serveur de données.  
fileContent Texte des instructions SQL à inclure dans la nouvelle charge de travail de requête.  
workloadName Nom à donner à la nouvelle charge de travail de requête. Si le nom indiqué est déjà utilisé, "_1" est ajouté au nouveau nom de la charge de travail de requête.  
delimiter Délimiteur des instructions SQL indiquées dans la valeur de la clé fileContent.  

Paramètres de sortie

L'API renvoie les paires de clés et de valeurs suivantes :

Tableau 2. Paramètres de sortie
Clé Valeur
code Code retour de l'API Web.
0
Exécution sans avertissements
4
Exécution avec des avertissements
8
Arrêt avant la fin de l'exécution en raison d'une erreur
output Nom de la nouvelle charge de travail de requête.
message Messages d'erreur ou d'avertissement.
exception Informations sur l'exception, le cas échéant. Ces informations peuvent être utiles au service de support IBM si vous le contactez pour résoudre un problème.

Exemple

Cet exemple utilise cURL pour transmettre des instructions SQL à l'API defineworkload, qui renvoie le nom de la nouvelle charge de travail de requête.

Entrée
curl
--insecure
-X POST
-H "Content-Type: application/json; charset=UTF-8"
--data "{
   dbType:ZOS,
   dbHost:hôte,
   dbPort:numéro_port,
   dbName:sous-système,
   dbUser:ID_utilisateur,
   dbPassword:mot_de_passe,
   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
Sortie
{ 
"code":0,
"message":"Sample_Workload"
}

Feedback