/**************************************************************************** ** (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