/********************************************************************************
** 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 the
** 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()
** UPDATE DB CFG sqlfudb()
** UPDATE DBM CFG sqlfusys()
** 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 your platform's DB2 supported C compiler.
**
*******************************************************************************/
#include <stdio.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){ print_err(&sqlca); return -1; }
/**************************************************************
This procedure prints error information released by the APIs.
***************************************************************/
void print_err(struct sqlca *ca)
{
char err[512];
printf("________________ERROR INFO_________________________\n");
sqlaintp(err, sizeof(err), 80, ca);
printf(" err \n");
printf("SQLCODE : %d \n",ca->sqlcode);
printf("SQLERRMC : %s \n",ca->sqlerrmc);
printf("SQLERRP : %s \n",ca->sqlerrp);
printf("_______________END ERROR INFO______________________ \n");
return;
}
/******************************************************************
This procedure prints diagnotic information released by the APIs.
*******************************************************************/
int print_info(db2AutoConfigDiags *Diag )
{
int i =0;
printf(" Token, Value \n") ;
while( i < (Diag->numElements) )
{
printf("%d %d \n",(Diag->pElements[i]).token ,(Diag->pElements[i]).value );
i++;
}
return 0;
}
/*****************************************************************
Procedure to produce db cfg recommendations produced by the API.
******************************************************************/
int print_infodb(struct sqlfupd *itemList, int num)
{
int i =0;
printf("Token, Value \n");
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
)
{
printf(" %d %d \n",itemList[i].token,*(int *)itemList[i].ptrvalue );
}
i++;
}
return 0;
}
/****************************************************************
procedure to produce dbm cfg recommendations produced by the API.
*****************************************************************/
int print_infodbm(struct sqlfupd *itemList, int num)
{
int i =0;
printf( " Token, Value \n");
while( i < num )
{
int tok = itemList[i].token;
if( /* these are dbm cfg tokens */
tok == SQLF_KTN_NUM_POOLAGENTS
|| tok == SQLF_KTN_SHEAPTHRES
)
{
printf("%d %d \n",itemList[i].token, *(int *)itemList[i].ptrvalue);
}
if( /* these are dbm cfg tokens */
tok == SQLF_KTN_ASLHEAPSZ
)
{
printf(" %d %d \n",itemList[i].token, *(short *)itemList[i].ptrvalue);
}
i++;
}
return 0;
}
int main(int argc, char *argv[])
{
struct sqle_conn_setting connSetting[NUMSETTINGS];
SQL_API_RC rc;
struct sqlca sqlca;
db2AutoConfigInterface autoConfigInterface;
int inputCount = 2;
/* input to API */
db2int32 mem_percent; /* specify 250 percent of server memory used on dbm*/
db2int32 workload; /* specify that our workload is mixed queries and transactions*/
unsigned short token;
int i = autoConfigInterface.oResult.oNewDbmValues.numElements - 1;
if ( argc != 5)
{
printf("autoconf <dbname> <userid> <passwd> <1|2> \n");
return -1;
}
strcpy(dbName, argv[1]);
strcpy(userid, argv[2]);
strcpy(passwd, argv[3]);
printf( "________ STARTING AUTOCONF TESTCASE ON INSTANCE OWNING NODE (node 0) \n");
/*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);
printf("SQLESETC instance owning node \n");
CHCKERR("set client");
}
/*2 connect to database */
EXEC SQL CONNECT TO :dbName USER :userid USING :passwd;
printf("______Connect \n");
CHCKERR("connect db");
/*3 ENTER PARMS INTO CONFIG API AND INTERFACE */
/* 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;
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(db2Version710, &autoConfigInterface, &sqlca);
printf("______DB2AUTOCONFIG_________ \n");
CHCKERR("db2autoconfig");
/*5 PRINT RECOMMENDATION AND DIAGNOSTICS */
if (rc == DB2_SG_RC_OK)
{
printf("NUMBER OF DIAGNOSTICS: %d \n",autoConfigInterface.oResult.oDiagnostics.numElements);
if (autoConfigInterface.oResult.oDiagnostics.numElements > 0)
{
/* handle the diagnostic results */
printf("==>resource values are not optimizable, diagnostics produced: \n");
print_info(&((autoConfigInterface.oResult).oDiagnostics)
);
printf(" __________________ \n");
}
if ( autoConfigInterface.oResult.oOldDbValues.numElements >0 )
{
/* handle the configuration results */
printf("==> resource values are optimizable recommendations produced: \n" );
printf("==> NUMBER OF PRODUCED OLD DB CFG RECOMMENDATIONS: \n", autoConfigInterface.oResult.oOldDbValues.numElements );
printf("==> PRODUCED VALUES \n" );
print_infodb(
autoConfigInterface.oResult.oOldDbValues.pConfigs,
autoConfigInterface.oResult.oOldDbValues.numElements
);
printf(" __________________ \n");
}
if ( autoConfigInterface.oResult.oNewDbValues.numElements >0 )
{
printf("==> NUMBER OF PRODUCED NEW DB CFG RECOMMENDATIONS:%d \n",autoConfigInterface.oResult.oNewDbValues.numElements);
printf("==> PRODUCED VALUES \n");
print_infodb(
autoConfigInterface.oResult.oNewDbValues.pConfigs,
autoConfigInterface.oResult.oNewDbValues.numElements
);
printf(" __________________ \n" );
}
if ( autoConfigInterface.oResult.oOldDbmValues.numElements >0 )
{
printf("==> NUMBER OF PRODUCED OLD DBM CFG RECOMMENDATIONS: %d \n",
autoConfigInterface.oResult.oOldDbmValues.numElements);
printf("==> PRODUCED VALUES \n");
print_infodbm(
autoConfigInterface.oResult.oOldDbmValues.pConfigs,
autoConfigInterface.oResult.oOldDbmValues.numElements
);
printf(" __________________ \n");
}
if ( autoConfigInterface.oResult.oNewDbmValues.numElements >0 )
{
printf("==> NUMBER OF PRODUCED NEW DBM CFG RECOMMENDATIONS: %d \n",autoConfigInterface.oResult.oNewDbmValues.numElements);
printf("==> PRODUCED VALUES \n" );
print_infodbm(
autoConfigInterface.oResult.oNewDbmValues.pConfigs,
autoConfigInterface.oResult.oNewDbmValues.numElements
);
printf(" __________________ \n");
}
/*6 FREE MEMORY */
/* must free all the memory allocated by db2AutoConfig() */
db2AutoConfigFreeMemory(db2Version710,&autoConfigInterface,&sqlca);
}
else
{
/* handle error */
printf("ERROR in API CALL \n");
}
/* DISCONNECT FROM NODE */
EXEC SQL CONNECT RESET;
printf("______+CONNECT RESET \n");
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) */
printf( "________ OPTION 2 CONFIGURING DATABASE PORTION RESIDING ON NON INSTANCE OWNING NODE (node 1) \n");
/*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);
printf("______+SQLESETC 2nd node (NODE 1) \n" );
CHCKERR("set client");
/*2 connect to database */
EXEC SQL CONNECT TO :dbName USER :userid USING :passwd;
printf("____________++connect to db \n");
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 =
(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 - 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(db2Version710, &autoConfigInterface, &sqlca);
printf("______+DB2AUTOCONFIG_________ \n");
CHCKERR("db2 connect to sample");
/*6 PRINT RECOMMENDATION AND DIAGNOSTITCS */
if (rc == DB2_SG_RC_OK)
{
printf("NUMBER OF DIAGNOSTICS: %d \n",autoConfigInterface.oResult.oDiagnostics.numElements);
if (autoConfigInterface.oResult.oDiagnostics.numElements > 0)
{
/* handle the diagnostic results */
printf("==>resource values are not optimizable,diagnostics produced: ");
print_info(&((autoConfigInterface.oResult).oDiagnostics));
printf(" ____________________ \n");
}
if ( autoConfigInterface.oResult.oOldDbValues.numElements >0 )
{
/* handle the configuration results */
printf("==> resource values are optimizable recommendations produced: \n" );
printf("==> NUMBER OF PRODUCED OLD DB CFG RECOMMENDATIONS: %d \n",
autoConfigInterface.oResult.oOldDbValues.numElements );
printf("==> PRODUCED VALUES \n" );
print_infodb(
autoConfigInterface.oResult.oOldDbValues.pConfigs,
autoConfigInterface.oResult.oOldDbValues.numElements
);
printf(" ____________________ \n");
}
if ( autoConfigInterface.oResult.oNewDbValues.numElements >0 )
{
printf("==> NUMBER OF PRODUCED NEW DB CFG RECOMMENDATIONS: %d \n",
autoConfigInterface.oResult.oNewDbValues.numElements);
printf("==> PRODUCED VALUES \n" );
print_infodb(
autoConfigInterface.oResult.oNewDbValues.pConfigs,
autoConfigInterface.oResult.oNewDbValues.numElements
);
printf(" ____________________ \n" );
}
if ( autoConfigInterface.oResult.oOldDbmValues.numElements >0 )
{
printf("==> NUMBER OF PRODUCED OLD DBM CFG RECOMMENDATIONS: %d \n",
autoConfigInterface.oResult.oOldDbmValues.numElements);
printf("==> PRODUCED VALUES \n");
print_infodbm(
autoConfigInterface.oResult.oOldDbmValues.pConfigs,
autoConfigInterface.oResult.oOldDbmValues.numElements
);
printf(" __________________** \n");
}
if ( autoConfigInterface.oResult.oNewDbmValues.numElements >0 )
{
printf("==> NUMBER OF PRODUCED NEW DBM CFG RECOMMENDATIONS: %d \n",
autoConfigInterface.oResult.oNewDbmValues.numElements);
printf("==> PRODUCED VALUES \n" );
print_infodbm(
autoConfigInterface.oResult.oNewDbmValues.pConfigs,
autoConfigInterface.oResult.oNewDbmValues.numElements
);
printf("__________________** \n");
}
/*6 FREE MEMORY */
/* must free all the memory allocated by db2AutoConfig() */
db2AutoConfigFreeMemory(db2Version710,&autoConfigInterface,&sqlca);
}
else
{
/* handle error */
printf(" ERROR in API CALL \n");
}
EXEC SQL CONNECT RESET;
printf("_____CONNECT RESET \n");
CHCKERR("connect reset");
}
return 0;
}