defineworkload: Abfrageworkloads erstellen

Nachdem Sie eine Liste von SQL-Anweisungen an die defineworkload-API übergeben haben, erstellt die API den Namen einer neuen Abfrageworkload und gibt ihn zurück.

Eingabeparameter

Übergeben Sie die folgenden Paare von Schlüsseln und Werten über diese URL an die API: https://localhost/tuning/defineworkload

Tabelle 1. Eingabeparameter
Key (Schlüssel) Wert Mögliche Werte
dbType Der Typ des Datenservers. ZOS
dbHost Der Host des Datenservers. Domänenname oder IP-Adresse
dbPort Die Portnummer des Datenservers  
dbName Der Name des Datenservers  
dbUser Die bei der Herstellung einer Verbindung zum Datenserver zu verwendende ID.  
dbPassword Das bei der Herstellung einer Verbindung zum Datenserver zu verwendende Kennwort.  
fileContent Der Text der SQL-Anweisungen, die in die neue Abfrageworkload eingefügt werden sollen.  
workloadName Der der neuen Abfrageworkload zuzuweisende Name. Wenn der Name bereits verwendet wird, wird "_1" an den Namen der neuen Abfrageworkload angehängt.  
delimiter Der Begrenzer der SQL-Anweisungen, die im Wert für den Schlüssel fileContent aufgelistet sind.  

Ausgabeparameter

Die API übergibt die folgenden Paare von Schlüsseln und Werten:

Tabelle 2. Ausgabeparameter
Schlüssel Wert
code Der Rückkehrcode vom Web-API.
0
Ohne Warnungen beendet
4
Mit Warnungen beendet
8
Infolge eines Fehlers ohne Abschluss beendet
output Der Name der neuen Abfrageworkload.
message Fehler- oder Warnnachrichten.
exception Informationen zu einer eventuell aufgetretenen Ausnahmebedingung. Diese Informationen können dem IBM Support während der Arbeit für Sie bei der Fehlerbehebung helfen.

Beispiel:

Dieses Beispiel verwendet cURL, um SQL-Anweisungen an die defineworkload-API zu übergeben, die den Namen der neuen Abfrageworkload zurückgibt.

Eingabe
curl 
--insecure 
-X POST 
-H "Content-Type: application/json; charset=UTF-8"
--data "{ 
   dbType:ZOS, 
   dbHost:Host,
   dbPort:Portnummer,
   dbName:Subsystem,
   dbUser:Benutzer-ID,
   dbPassword:Kennwort,
   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
Ausgabe
{ 
"code":0, 
"message":"Sample_Workload" 
}

Feedback