/****************************************************************************  
** (c) Copyright IBM Corp. 2007 All rights reserved.
** 
** The following sample of source code ("Sample") is owned by International 
** Business Machines Corporation or one of its subsidiaries ("IBM") and is 
** copyrighted and licensed, not sold. You may use, copy, modify, and 
** distribute the Sample in any form without payment to IBM, for the purpose of 
** assisting you in the development of your applications.
** 
** The Sample code is provided to you on an "AS IS" basis, without warranty of 
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 
** not allow for the exclusion or limitation of implied warranties, so the above 
** limitations or exclusions may not apply to you. IBM shall not be liable for 
** any damages you suffer as a result of using, copying, modifying or 
** distributing the Sample, even if IBM has been advised of the possibility of 
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: dbcfg.sqC
**
** SAMPLE: Configure database and database manager configuration parameters
**          
**          This sample demonstrates the use of the db2AutoConfig() API
**          which configures database and database manager configuration 
**          parameters. The program configures the sample database in two
**          scenarios determined by specifying "1" or "2" as the multinode
**          command line parameter (any other value defaults to  option "1").
**          With option "1", the db2AutoConfig() API automatically configures 
**          the sample database. The recommendation and diagnostics are 
**          produced by the API and printed to standard output. By specifying
**          "2" as the multinode command line parameter, the db2AutoConfig()
**          API is used to generate optimized configuration recommendations 
**          for a database on two partitions. 
**
** DB2 APIs USED:
**          db2AutoConfig -- AUTOCONFIG
**          db2AutoConfigFreeMemory -- AUTOCONFIG FREE MEMORY 
**     �    sqlfusys -- UPDATE DBM CFG          
**          sqlesetc -- SET CLIENT              
**          sqlaintp -- SQLCA MESSAGE
**
** SQL STATEMENTS USED:
**          INCLUDE
**          CONNECT
**
** STRUCTURES USED:
**          sqle_conn_setting
**          db2AutoConfigInterface
**          db2AutoConfigArray
**          db2AutoConfigOutput
**          db2AutoConfigValues
**          sqlca
**
**                           
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For information on DB2 APIs, see the Administrative API Reference.
**
** For the latest information on programming, compiling, and running DB2
** applications, visit the DB2 Information Center at
**     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/

#include <string.h>
#include <sql.h>
#include <sqlutil.h>
#include "db2AuCfg.h"
#include "utilemb.h"
#if ((__cplusplus >= 199711L) && !defined DB2HP && !defined DB2AIX) || \
    (DB2LINUX && (__LP64__ || (__GNUC__ >= 3)) )
   #include <iomanip>
   #include <iostream>
   using namespace std; 
#else
   #include <iomanip.h>
   #include <iostream.h>
#endif

#define NUMSETTINGS 3
#define CHCKERR(s) if (sqlca.sqlcode < 0) { print_err(&sqlca); return -1; }

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
  char dbName[16];
  char userid[30];
  char passwd[30];
EXEC SQL END DECLARE SECTION;

// This procedure prints error information released by the APIs.
void print_err(struct sqlca *ca)
{
  char err[512];

  cout << "________________ERROR INFO_________________________" << endl;

  // get error message
  sqlaintp(err, sizeof(err), 80, ca);
  cout << err << endl;
  cout << "SQLCODE : " << ca->sqlcode << endl;
  cout << "SQLERRMC: " << ca->sqlerrmc << endl;;
  cout << "SQLERRP : " << ca->sqlerrp << endl;;
  cout << "_______________END ERROR INFO______________________" << endl;

  return;
} //print_err

// This procedure prints diagnotic information released by the APIs.
int print_info(db2AutoConfigDiags *Diag)
{
  int i = 0;

  cout << "Token, Value" << endl;

  while (i < (Diag->numElements))
  {
    cout << setw(5) << (Diag->pElements[i]).token
         << "  " << setw(5) << (Diag->pElements[i]).value << endl;
    i++;
  }

  return 0;
} //print_info

// Procedure to produce db cfg recommendations produced by the API.
int print_infodb(struct db2CfgParam *itemList, int num)
{
  int i = 0;

  cout << "Token, Value" << endl;

  while (i < num)
  {
    int tok = itemList[i].token;

    // these are db cfg tokens
    if (tok == SQLF_DBTN_BUFF_PAGE ||
        tok == SQLF_DBTN_CATALOGCACHE_SZ || tok == SQLF_DBTN_DFT_DEGREE)
    {
      cout << setw(5) << itemList[i].token << "  "
           << setw(5) << *(int *)itemList[i].ptrvalue << endl;
    }
    i++;
  }
  return 0;
} //print_infodb

// procedure to produce dbm cfg recommendations produced by the API.
int print_infodbm(struct db2CfgParam *itemList, int num)
{
  int i = 0;

  cout << "Token, Value" << endl;

  while (i < num)
  {
    int tok = itemList[i].token;

    // these are dbm cfg tokens
    if (tok == SQLF_KTN_NUM_POOLAGENTS || tok == SQLF_KTN_SHEAPTHRES)
    {
      cout << setw(5) << itemList[i].token << "  "
           << setw(5) << *(int *)itemList[i].ptrvalue << endl;
    }

    // these are dbm cfg tokens
    if (tok == SQLF_KTN_ASLHEAPSZ)
    {
      cout << setw(5) << itemList[i].token << "  "
           << setw(5) << *(int *)itemList[i].ptrvalue << endl;
    }
    i++;
  }
  return 0;
} //print_infodbm

int main(int argc, char *argv[])
{
  struct sqle_conn_setting connSetting[NUMSETTINGS];
  SQL_API_RC rc;
  struct sqlca sqlca;
  db2AutoConfigInterface autoConfigInterface;
  int inputCount = 2;
  unsigned short token;

  // input to API
  // specify 250 percent of server memory used on dbm
  db2int32 mem_percent;

  // specify that our workload is mixed queries and transactions
  db2int32 workload;

  

  if (argc != 5)
  {
    cout << "dbcfg <dbname> <userid> <passwd> <1|2>" << endl;
    return -1;
  }

  strcpy(dbName, argv[1]);
  strcpy(userid, argv[2]);
  strcpy(passwd, argv[3]);

  cout << "\nHOW TO CONFIGURE DATABASE AND DATABASE MANAGER CONFIGURATION"
       << "PARAMETERS" << endl;

  cout << "\n______STARTING AUTOCONF TESTCASE ON INSTANCE OWNING NODE "
       << "(node 0)" << endl;

  // 1 set client

  // need to connect and attach to node if MPP
  if (strcmp(argv[4], "2") == 0)
  {
    // attach to node 0
    connSetting[0].type = SQL_ATTACH_NODE;
    connSetting[0].value = 0;

    // connect to node 0
    connSetting[1].type = SQL_CONNECT_NODE;
    connSetting[1].value = 0;

    // disconnect from node on commit (implicit when connect reset)
    connSetting[2].type = SQL_DISCONNECT;
    connSetting[2].value = SQL_DISCONNECT_AUTO;

    // set client information
    sqlesetc(&connSetting[0], NUMSETTINGS, &sqlca);
    cout << "SQLESETC instance owning node" << endl;
    CHCKERR("set client");
  }

  // 2 connect to database

  EXEC SQL CONNECT TO :dbName USER :userid USING :passwd;
  cout << "______Connect" << endl;
  CHCKERR("connect db");

  // 3  ENTER PARMS INTO CONFIG API AND INTERFACE

  // allocate memory for input parameters
  autoConfigInterface.iParams.pElements =
    new db2AutoConfigElement[inputCount];

  // set up the interface for calling db2AutoConfig
  autoConfigInterface.iProductID = DB2_SG_PID_DEFAULT;
  strcpy(autoConfigInterface.iDbAlias, dbName);
  strcpy(autoConfigInterface.iProductVersion, "1.1");

  /*** IMPORTANT: the recommendations are automatically
              applied to db/dbm cfg
  ***/
  autoConfigInterface.iApply = DB2_SG_APPLY;

  mem_percent = 25;
  workload = 2;

  autoConfigInterface.iParams.numElements = 2;
  autoConfigInterface.iParams.pElements[0].token = DB2_SG_MEMORY_PERCENTAGE;
  autoConfigInterface.iParams.pElements[0].value = mem_percent;
  autoConfigInterface.iParams.pElements[1].token = DB2_SG_WORKLOAD;
  autoConfigInterface.iParams.pElements[1].value = workload;

  // 4 AUTOCONFIG CALL
  rc = db2AutoConfig(db2Version970, &autoConfigInterface, &sqlca);
  cout << "______DB2AUTOCONFIG_________" << endl;
  CHCKERR("db2autoconfig");

  // 5 PRINT RECOMMENDATION AND DIAGNOSTICS
  if (rc == DB2_SG_RC_OK)
  {
    cout << "NUMBER OF DIAGNOSTICS: "
         << autoConfigInterface.oResult.oDiagnostics.numElements << endl;

    if (autoConfigInterface.oResult.oDiagnostics.numElements > 0)
    {
      // handle the diagnostic results
      cout << "==> resource values are not optimizable, "
           << "diagnostics produced:" << endl;
      print_info(&((autoConfigInterface.oResult).oDiagnostics));
      cout << "___________________" << endl;
    }

    if (autoConfigInterface.oResult.oOldDbValues.numElements > 0)
    {
      // handle the configuration results
      cout << "==> resource values are optimizable "
           << "recommendations produced:" << endl;
      cout << "==> NUMBER OF PRODUCED OLD DB CFG RECOMMENDATIONS: "
           << autoConfigInterface.oResult.oOldDbValues.numElements << endl;
      cout << "==> PRODUCED VALUES" << endl;
      print_infodb(autoConfigInterface.oResult.oOldDbValues.pConfigs,
                   autoConfigInterface.oResult.oOldDbValues.numElements);
      cout << "___________________" << endl;
    }

    if (autoConfigInterface.oResult.oNewDbValues.numElements > 0)
    {
      cout << "==> NUMBER OF PRODUCED NEW DB CFG "
           << "RECOMMENDATIONS: "
           << autoConfigInterface.oResult.oNewDbValues.numElements << endl;
      cout << "==> PRODUCED VALUES" << endl;
      print_infodb(autoConfigInterface.oResult.oNewDbValues.pConfigs,
                   autoConfigInterface.oResult.oNewDbValues.numElements);
      cout << "___________________" << endl;
    }

    if (autoConfigInterface.oResult.oOldDbmValues.numElements > 0)
    {
      cout << "==> NUMBER OF PRODUCED OLD DBM CFG "
           << "RECOMMENDATIONS: "
           << autoConfigInterface.oResult.oOldDbmValues.numElements << endl;
      cout << "==> PRODUCED VALUES" << endl;
      print_infodbm(autoConfigInterface.oResult.oOldDbmValues.pConfigs,
                    autoConfigInterface.oResult.oOldDbmValues.numElements);
      cout << "___________________" << endl;
    }

    if (autoConfigInterface.oResult.oNewDbmValues.numElements > 0)
    {
      cout << "==> NUMBER OF PRODUCED NEW DBM CFG "
           << "RECOMMENDATIONS: "
           << autoConfigInterface.oResult.oNewDbmValues.numElements << endl;
      cout << "==> PRODUCED VALUES" << endl;
      print_infodbm(autoConfigInterface.oResult.oNewDbmValues.pConfigs,
                    autoConfigInterface.oResult.oNewDbmValues.numElements);
      cout << "___________________" << endl;
    }

    // 6 FREE MEMORY

    // must free all the memory allocated by db2AutoConfig()
    db2AutoConfigFreeMemory(db2Version970, &autoConfigInterface, &sqlca);
  }
  else
  {
    // handle error
    cout << "ERROR in API CALL" << endl;
  }

  // disconnect from node
  EXEC SQL CONNECT RESET;
  cout << "_____+CONNECT RESET" << endl;
  CHCKERR("connect reset");

  // IF 5 ARGUMENTS SPECIFIED, TREAT AS PARTIONED DATABASE
  // WITH A SECOND PARTITION
  if (strcmp(argv[4], "2") == 0)
  {
    // repeat for second node (node 1)
    cout << "________ OPTION 2 CONFIGURING DATABASE PORTION RESIDING "
         << "ON NON INSTANCE OWNING PARTITION (node 1)" << endl;

    // 1 SET CLIENT
    // attach to node 1
    connSetting[0].type = SQL_ATTACH_NODE;
    connSetting[0].value = 1;

    // connect to node 1
    connSetting[1].type = SQL_CONNECT_NODE;
    connSetting[1].value = 1;

    // disconnect from node on commit
    connSetting[2].type = SQL_DISCONNECT;
    connSetting[2].value = SQL_DISCONNECT_AUTO;

    // set client information
    sqlesetc(&connSetting[0], NUMSETTINGS, &sqlca);
    cout << "______+SQLESETC 2nd node (NODE 1)" << endl;
    CHCKERR("set client");

    // 2 CONNECT TO DATABASE
    EXEC SQL CONNECT TO :dbName USER :userid USING :passwd;
    cout << "____________++connect to db" << endl;
    CHCKERR("connect to db");

    // 3  ENTER PARMS INTO API AND INTERFACE

    // input to smartguide
    mem_percent = 45; // percentage of memory for dbm server is 34%
    workload = 2; // workload is mixed with queries and transactions
    inputCount = 2;

    // allocating memory for input parameters
    autoConfigInterface.iParams.pElements =
      new db2AutoConfigElement[inputCount];

    // set up the interface for calling db2AutoConfig
    autoConfigInterface.iProductID = DB2_SG_PID_DEFAULT;
    strcpy(autoConfigInterface.iDbAlias, dbName);
    strcpy(autoConfigInterface.iProductVersion, "1.1");

    /*** IMPORTANT - this time the configuration parameters
    ***             are not automatically applied
    ***/
    autoConfigInterface.iApply = DB2_SG_APPLY;

    autoConfigInterface.iParams.numElements = 2;
    autoConfigInterface.iParams.pElements[0].token =
      DB2_SG_MEMORY_PERCENTAGE;
    autoConfigInterface.iParams.pElements[0].value = mem_percent;
    autoConfigInterface.iParams.pElements[1].token = DB2_SG_WORKLOAD;
    autoConfigInterface.iParams.pElements[1].value = workload;

    // 4 AUTOCONFIG CALL
    rc = db2AutoConfig(db2Version970, &autoConfigInterface, &sqlca);
    cout << "______+DB2AUTOCONFIG_________" << endl;
    CHCKERR("db2 connect to sample");

    // 5 PRINT RECOMMENDATION AND DIAGNOSTITCS
    if (rc == DB2_SG_RC_OK)
    {
      cout << "NUMBER OF DIAGNOSTICS: "
           << autoConfigInterface.oResult.oDiagnostics.numElements << endl;

      if (autoConfigInterface.oResult.oDiagnostics.numElements > 0)
      {
        // handle the diagnostic results
        cout << "==>resource values are not optimizable, "
             << "diagnostics produced: ";
        print_info(&((autoConfigInterface.oResult).oDiagnostics));
        cout << "___________________" << endl;
      }

      if (autoConfigInterface.oResult.oOldDbValues.numElements > 0)
      {
        // handle the configuration results
        cout << "==> resource values are optimizable "
             << "recommendations produced:" << endl;
        cout << "==> NUMBER OF PRODUCED OLD DB CFG "
             << "RECOMMENDATIONS: "
             << autoConfigInterface.oResult.oOldDbValues.numElements << endl;
        cout << "==> PRODUCED VALUES" << endl;
        print_infodb(autoConfigInterface.oResult.oOldDbValues.pConfigs,
                     autoConfigInterface.oResult.oOldDbValues.numElements);
        cout << "___________________" << endl;
      }

      if (autoConfigInterface.oResult.oNewDbValues.numElements > 0)
      {
        cout << "==> NUMBER OF PRODUCED NEW DB CFG RECOMMENDATIONS: "
             << autoConfigInterface.oResult.oNewDbValues.numElements << endl;
        cout << "==> PRODUCED VALUES" << endl;
        print_infodb(autoConfigInterface.oResult.oNewDbValues.pConfigs,
                     autoConfigInterface.oResult.oNewDbValues.numElements);
        cout << "___________________" << endl;
      }

      if (autoConfigInterface.oResult.oOldDbmValues.numElements > 0)
      {
        cout << "==> NUMBER OF PRODUCED OLD DBM CFG RECOMMENDATIONS: "
             << autoConfigInterface.oResult.oOldDbmValues.numElements
             << endl;
        cout << "==> PRODUCED VALUES" << endl;
        print_infodbm(autoConfigInterface.oResult.oOldDbmValues.pConfigs,
                      autoConfigInterface.oResult.oOldDbmValues.numElements);
        cout << "___________________" << endl;
      }

      if (autoConfigInterface.oResult.oNewDbmValues.numElements > 0)
      {
        cout << "==> NUMBER OF PRODUCED NEW DBM CFG RECOMMENDATIONS: "
             << autoConfigInterface.oResult.oNewDbmValues.numElements
             << endl;
        cout << "==> PRODUCED VALUES" << endl;
        print_infodbm(autoConfigInterface.oResult.oNewDbmValues.pConfigs,
                      autoConfigInterface.oResult.oNewDbmValues.numElements);
        cout << "__________________**" << endl;
      }

      // 6 FREE MEMORY

      // must free all the memory allocated by db2AutoConfig()
      db2AutoConfigFreeMemory(db2Version970, &autoConfigInterface, &sqlca);
    }
    else
    {
      // handle error
      cout << "ERROR in API CALL" << endl;
    }

    EXEC SQL CONNECT RESET;
    cout << "_____CONNECT RESET" << endl;
    CHCKERR("connect reset");
  }
  return 0;
} //main