/********************************************************************************
** Source File Name = autoconf.sqC
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
** PURPOSE: This sample program demonstrates the use of the
** db2AutoConfig() API. The db2AutoConfig() API configures
** database and database manager configuration parameters.
** Within the sample program, a database is configured
** in two optional scenarios as determined by specifying "1" or
** "2" as the multinode command line paramter (any other value
** will default to option "1"). In the first case, the sample
** database is configured and applied automatically by
** db2AutoConfig() API. The recommendation/diagnostics are
** produced by the db2AutoConfig() API and printed out 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
** nodes.
**
**
** For more information about these samples see the README file.
**
** For more information on programming in C++, see the
** - "Programming in C and C++" section of the Application Development Guide
**
** For more information on Building C++ Applications, see the:
** - "Building C++ Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
**
** APIs USED :
** AUTOCONFIG db2AutoConfig()
** AUTOCONFIG FREE MEMORY db2AutoConfigMemory()
** SET CLIENT sqlesetc()
** SQLCA MESSAGE sqlaintp()
**
** STRUCTURES USED :
** sqle_conn_setting
** db2AutoConfigInterface
** db2AutoConfigArray
** db2AutoConfigOutput
** db2AutoConfigValues
** sqlfupd
** sqlca
**
** internal :
** print_info : Displays a diagnostics of system
** print_infodb : Displays a recommendations for db cfg
** print_infodbm : Displays a recommendations for dbm cfg
** alloc_mem : Allocates the proper amount of memory for the
** GET DATABASE CONFIGURATION API to fill with
** data.
**
** external :
** utilemb.sqC Contains functions for error-checking and
** rolling back a transaction in case of error.
** This file must be compiled and its object file
** linked in to the "autoconf" program.
**
**
** EXTERNAL DEPENDENCIES :
** - Ensure existence of database for precompile purposes.
** - Compile and link with the DB2 supported C++ compiler on your platform.
**
*********************************************************************************/
#include <stdio.h>
#include <iostream.h>
#include <iomanip.h>
#include <stdlib.h>
#include <string.h>
#include <sql.h>
#include <sqlutil.h>
#include "db2AuCfg.h"
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char dbName[16];
char userid[30];
char passwd[30];
EXEC SQL END DECLARE SECTION;
#define NUMSETTINGS 3
#define CHCKERR(s) if (sqlca.sqlcode < 0){ cout << s << ": " <<endl; print_err(&sqlca); return -1; }
/**************************************************************
This procedure prints error information released by the APIs.
**************************************************************/
void print_err(struct sqlca *ca)
{
cout<<"*****************ERROR INFO***********************"<<endl;
char err[512];
sqlaintp(err, sizeof(err), 80, ca);
cout << err << endl;
cout<<"SQLCODE :"<< ca->sqlcode<<endl;
cout<<"SQLERRMC :"<< ca->sqlerrml<<endl;
cout<<"SQLERRP :"<< ca->sqlerrp <<endl;
cout<<"*****************END ERROR INFO********************"<<endl;
return;
}
/********************************************************************************
This procedure prints diagnostic information released by the db2AutoConfig API.
*********************************************************************************/
int print_info(db2AutoConfigDiags *Diag )
{
int i =0;
cout << " Token, Value " << endl;
while( i < Diag->numElements)
{
cout << (Diag->pElements[i]).token
<< " "
<<(Diag->pElements[i]).value << endl ;
i++;
}
return 0;
}
/*************************************************************************
Procedure to print db cfg recommendations produced by db2AutoConfig API.
**************************************************************************/
int print_infodb(struct sqlfupd *itemList, int num)
{
int i =0;
cout << " Token, Value " << endl;
while( i < num )
{
int tok = itemList[i].token;
if( // these are db cfg tokens
tok == SQLF_DBTN_BUFF_PAGE
|| tok == SQLF_DBTN_CATALOGCACHE_SZ
|| tok == SQLF_DBTN_DFT_DEGREE
)
{
cout << itemList[i].token
<< " "<< *(int *)itemList[i].ptrvalue << endl;
}
i++;
}
return 0;
}
/**************************************************************************
Procedure to print dbm cfg recommendations produced by db2Autoconfig API.
***************************************************************************/
int print_infodbm(struct sqlfupd *itemList, int num)
{
int i =0;
cout << " Token, Value " << endl;
while( i < num )
{
int tok = itemList[i].token;
if( // these are dbm cfg tokens
tok == SQLF_KTN_NUM_POOLAGENTS
|| tok == SQLF_KTN_SHEAPTHRES
)
{
cout << itemList[i].token
<< " "<< *(int *)itemList[i].ptrvalue << endl;
}
if( // these are dbm cfg tokens
tok == SQLF_KTN_ASLHEAPSZ
)
{
cout << itemList[i].token
<< " "<< *(short *)itemList[i].ptrvalue << endl;
}
i++;
}
return 0;
}
int main(int argc, char *argv[])
{
struct sqle_conn_setting connSetting[NUMSETTINGS];
SQL_API_RC rc;
struct sqlca sqlca;
db2AutoConfigInterface autoConfigInterface;
if ( argc != 5)
{
cout << "autoconf <dbname> <userid> <passwd> <1|2>"
<< endl;
return -1;
}
strcpy(dbName, argv[1]);
strcpy(userid, argv[2]);
strcpy(passwd, argv[3]);
cout << "************ STARTING AUTOCONF TESTCASE ON INSTANCE OWNING NODE (node 0) \n" << endl;
/*1 set client */
if (atoi(argv[4]) == 2 ) /* need to connect and attach to node if MPP */
{
/* 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;
sqlesetc(&connSetting[0], NUMSETTINGS,&sqlca);
cout << "+++++++SQLESETC 1st 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 */
/* input to smartguide */
db2int32 mem_percent = 60; /* specify 60 percent of server memory used on dbm*/
db2int32 workload = 2; /* specify that our workload is mixed queries and transactions*/
int inputCount = 2;
/* allocating memory for input parameters */
autoConfigInterface.iParams.pElements =
(db2AutoConfigElement *) malloc(sizeof(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;
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(db2Version710, &autoConfigInterface, &sqlca);
cout << "++++++++DB2AUTOCONFIG+++++++++" <<endl;
CHCKERR("db2autoconfig");
/*5 PRINT RECOMMENDATION AND DIAGNOSTITCS */
if (rc == DB2_SG_RC_OK)
{
cout << "NUMBER OF DIAGNOSTICS: " <<
autoConfigInterface.oResult.oDiagnostics.numElements
<< endl ;
cout <<"==> NUMBER OF PRODUCED NEW DB CFG RECOMMENDATIONS: " << autoConfigInterface.oResult.oNewDbValues.numElements <<endl;
cout <<"==> NUMBER OF PRODUCED NEW DBM CFG RECOMMENDATIONS: " << autoConfigInterface.oResult.oNewDbmValues.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(db2Version710,&autoConfigInterface,&sqlca);
}
else
{
// handle error
cout <<(" ERROR in API CALL \n");
}
/* 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 NODE */
if (atoi(argv[4]) == 2)
{
/************************************************/
/***********************************************/
/* REPEAT FOR SECOND NODE (Node 1) */
cout << "************ OPTION 2 CONFIGURING DATABASE PORTION RESIDING ON NON INSTANCE OWNING NODE (node 1) \n" << 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 TO NODE ON COMMIT */
connSetting[2].type = SQL_DISCONNECT;
connSetting[2].value = SQL_DISCONNECT_AUTO;
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 = 60; /* percentage of memory for dbm server is 60% */
workload = 2; /* workload is mixed with queries and transactions */
inputCount = 2;
/* allocating memory for input parameters */
autoConfigInterface.iParams.pElements =
(db2AutoConfigElement *) malloc(sizeof(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 configuration parameters
**** are 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(db2Version710, &autoConfigInterface, &sqlca);
cout << "++++++++DB2AUTOCONFIG+++++++++" <<endl;
CHCKERR("autoconfig");
/*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: " <<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(db2Version710,&autoConfigInterface,&sqlca);
}
else
{
// handle error
cout <<(" ERROR in API CALL \n");
}
EXEC SQL CONNECT RESET;
cout << "+++++++CONNECT RESET " << endl;
CHCKERR("connect reset");
}
return 0;
}