/* * LICENSE AND DISCLAIMER * ---------------------- * THIS MATERIAL CONTAINS IBM COPYRIGHTED SAMPLE PROGRAMMING SOURCE * CODE ('SAMPLE CODE'). * IBM GRANTS YOU A NONEXCLUSIVE LICENSE TO COMPILE, LINK, EXECUTE, * DISPLAY, REPRODUCE, DISTRIBUTE AND PREPARE DERIVATIVE WORKS OF * THIS SAMPLE CODE. THE SAMPLE CODE HAS NOT BEEN THOROUGHLY * TESTED UNDER ALL CONDITIONS. IBM, THEREFORE, DOES NOT GUARANTEE * OR IMPLY ITS RELIABILITY, SERVICEABILITY, OR FUNCTION. IBM * PROVIDES NO PROGRAM SERVICES FOR THE SAMPLE CODE. * * ALL SAMPLE CODE CONTAINED HEREIN IS PROVIDED TO YOU "AS IS" WITHOUT * ANY WARRANTIES OF ANY KIND. THE IMPLIED WARRANTIES OF MERCHANTABILITY, * FITNESS FOR A PARTICULAR PURPOSE AND NON-INFRINGMENT ARE EXPRESSLY * DISCLAIMED. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED * WARRANTIES, SO THE ABOVE EXCLUSIONS MAY NOT APPLY TO YOU. IN NO * EVENT WILL IBM BE LIABLE TO ANY PARTY FOR ANY DIRECT, INDIRECT, * SPECIAL OR OTHER CONSEQUENTIAL DAMAGES FOR ANY USE OF THE SAMPLE CODE * INCLUDING, WITHOUT LIMITATION, ANY LOST PROFITS, BUSINESS * INTERRUPTION, LOSS OF PROGRAMS OR OTHER DATA ON YOUR INFORMATION * HANDLING SYSTEM OR OTHERWISE, EVEN IF WE ARE EXPRESSLY ADVISED OF * THE POSSIBILITY OF SUCH DAMAGES. * * (C) COPYRIGHT IBM CORP. 2006 * ALL RIGHTS RESERVED. * US GOVERNMENT USERS RESTRICTED RIGHTS - * USE, DUPLICATION OR DISCLOSURE RESTRICTED * BY GSA ADP SCHEDULE CONTRACT WITH IBM CORP. * LICENSED MATERIAL - PROPERTY OF IBM */ /***************************************************************************/ /* COMPILE STATEMENT */ /* CRTSQLCI OBJ(LIB/PSEUDOJRN) SRCFILE(LIB/PSEUDOJRN) */ /* COMMIT(*NONE) OBJTYPE(*PGM) OUTPUT(*PRINT) */ /***************************************************************************/ /***************************************************************************/ /* */ /* Program name: PSEUDOJRN */ /* Function: Monitors database files for Journal Traffic */ /* This is done through an SQL statement using */ /* SYSPARTITIONSTAT and SYSTABLES. The report generated */ /* is stored in argv[1] */ /* */ /* Enhancments: Batch Option is Not Completely Implemented */ /* Session structure to group reports. Work for */ /* SMAPP entries and Indexes. Count openning and closing */ /* of the files to add to disk writes. */ /* */ /* Limitations: Only 10000 Tables can be selected at one time */ /* */ /* */ /* Requirements: System - V5RR with PTF # SI24847 */ /* */ /* */ /* Parameters: argv[1] - char [10] - Library Name */ /***************************************************************************/ #include #include #include #include #include #include #include #include #include #include #include #include #include #include /***************************************************************************/ /* Function prototypes */ /***************************************************************************/ void printTitle(char * errorString); void tkGetLine(char* userInput ,int size); void my_exception(_INTRPT_Hndlr_Parms_T * __ptr128 exp_info); void retrieveTables(char* schemaName ,char* library); void returnTables(char *library); void clearSelected(char* library); void clearSchemas(char* library); char* strupr(char* string); void collectDataInter(char* library); void getTime(); void primeCollectData(char* library); void viewReports(char* library); void collectDataBatch(char* library); void rmvSpaces(char* string); void retrieveSelected(char* library); int numSelected(char* library); void createReport(char* library, char* reportName, char* tableName, char* schemaName); void clearReports(char* library); void rtvTableName(char* library, char* table); /***************************************************************************/ /* Global variables */ /***************************************************************************/ /*parameters for QCMDEXEC */ int length; char pgm[400]; decimal(15,5) packed_length; /***************************************************************************/ /* Constants used by this program */ /***************************************************************************/ #define TABLE_NAME "pseudoMonitor" #define REPORT_TABLE "reports" #define FALSE 0 #define TRUE 1 /***************************************************************************/ /* SQL Error Handling and SQL Descriptor areas in C and C++ */ /***************************************************************************/ EXEC sql include SQLCA; EXEC sql include SQLDA; /***************************************************************************/ /* M A I N F U N C T I O N */ /***************************************************************************/ int main(int argc, char *argv[]) { /***************************************************************************/ /* Variables to store Command Line Arguments */ /***************************************************************************/ char library[10]; //char session[10]; if choose to extend to session /***************************************************************************/ /* Variables to store User Input */ /***************************************************************************/ int choice=0; char schemaName[20]; /***************************************************************************/ /* SQL Variable Declaration */ /***************************************************************************/ EXEC SQL BEGIN DECLARE SECTION; char stmt[1024]; EXEC SQL END DECLARE SECTION; int numTables; /***************************************************************************/ /* Check if Valid Number of Arguments */ /***************************************************************************/ if(argc != 2) { printf("\n\n\n\n\n\n\n\n\n\n\nImproper Number of Paramaters\n"); printf(" 1) Library Name to House Statistics Generated\n");; return; } /***************************************************************************/ /* Copy command line arguments into variable name */ /***************************************************************************/ sprintf(library,"%10.10s",argv[1]); /***************************************************************************/ /* Enhancment for future - Sessions to organize reports generated */ /***************************************************************************/ /* if(argc == 3) sprintf(session,"%s",argv[1]); sprintf(stmt,"CREATE table %s/session (",library); EXEC SQL EXECUTE IMMEDIATE :stmt; if(sqlca.sqlcode < 0) { printf("Could not create the table %s SQL ERROR CODE %d\nReason: ", schema,sqlca.sqlcode); if(sqlca.sqlcode == -601) { printf("Loading previous session %s\n",schema); } } */ /***************************************************************************/ /* Create Pseudo Monitor Table if not already Created */ /***************************************************************************/ sprintf(stmt,"CREATE table %s/%s",library,TABLE_NAME); sprintf(stmt,"%s (SchemaName char(30), TableName char(30),",stmt); sprintf(stmt,"%s Selected int,",stmt); sprintf(stmt,"%s CONSTRAINT psmon_schema_un UNIQUE(SchemaName,TableName))", stmt); EXEC SQL EXECUTE IMMEDIATE :stmt; if(sqlca.sqlcode < 0) { if(sqlca.sqlcode == -601) { printf("Loading previous session from library %s\n",library); //sleep(3); } else if(sqlca.sqlcode == -204) { printf("Library %s could not be found\n",library); return; } else { printf("Unknown Error Number %i",sqlca.sqlcode); } } sprintf(stmt,"CREATE table %s/%s",library,REPORT_TABLE); sprintf(stmt,"%s (ReportName char(30), Status int,",stmt); sprintf(stmt,"%s CONSTRAINT reports_name_un UNIQUE(ReportName))", stmt); EXEC SQL EXECUTE IMMEDIATE :stmt; if(sqlca.sqlcode < 0) { if(sqlca.sqlcode == -601) { printf("Loading previous session from library %s\n",library); //sleep(3); } else if(sqlca.sqlcode == -204) { printf("Library %s could not be found\n",library); return; } } /***************************************************************************/ /* Print title and receive User input */ /***************************************************************************/ printTitle(""); scanf("%i", &choice); getchar(); //used to clear the newline char while(choice != 9) { switch(choice){ case 1: printf("Option %i has been selected",choice); printf("\n\n\n\n\n\n\n\n\n\nAdd Schema\n\n"); printf("************************************************\n\n"); printf("Enter Schema/Library Name:\n"); tkGetLine(schemaName,20); strupr(schemaName); retrieveTables(schemaName,library); printTitle(""); break; case 2: printf("\n\n\n\n\n\n\n\n\n\nSelect Table Name\n\n"); printf("************************************************\n\n"); printf("Select Table's To Monitor:\n"); returnTables(library); printTitle(""); break; case 3: while(choice != 9){ numTables = numSelected(library); if(!(numTables > 0)) { printf("\n\n\nThere are currently no tables select\n"); printf("Go back to Main menu and select table names\n"); sleep(3); break; } printf("\n\n\n\n\n\n\n\n\n\n\nStart Collecting Data\n\n"); printf("************************************************\n\n"); printf("How would you like to collect the data?\n"); printf("\t1. Interactive (number of samples and sample rate)\n"); printf("\t2. Batch (enter start/stop time and number of samples)\n"); printf("\t3. View Tables Selected\n"); printf("\t9. Exit to Main Menu\n"); scanf("%i",&choice); getchar(); if(choice == 1) { collectDataInter(library); break; } /*****************************************************************/ /* Batch Option Not Completely Implemented or Tested */ /*****************************************************************/ else if(choice == 2) { collectDataBatch(library); } else if(choice == 3) { retrieveSelected(library); } else if(choice == 9) { //Nothing to Be Done, Return To Main Menu } else { printf("Incorrect Input\n"); sleep(2); } } printTitle(""); break; case 4: printf("\n\n\n\n\n\n\n\n\n\nView Collected Data\n\n"); printf("************************************************\n\n"); viewReports(library); printTitle(""); break; case 5: printf("\n\n\n\n\n\n\n\n\n\nClear Selected\n\n"); printf("************************************************\n\n"); clearSelected(library); printTitle(""); break; case 6: printf("\n\n\n\n\n\n\n\n\n\nClear Schemas\n\n"); printf("************************************************\n\n"); clearSchemas(library); printTitle(""); break; case 7: printf("\n\n\n\n\n\n\n\n\n\nClear Collected Data\n\n"); printf("************************************************\n\n"); clearReports(library); printTitle(""); break; case 9: break; default: printTitle("Incorrect Input"); break; } scanf("%i", &choice); getchar(); } return; } /***************************************************************************/ /* */ /* Function: printTitle */ /* */ /* Description: Prints Menu and Hotel Statistic to Screen */ /***************************************************************************/ void printTitle(char * errorString) { printf("\n\n\n\n\n\n\n\n\n\nPseudo Journaling\n\n"); if(strncmp(errorString,"",1) != 0) printf("%s\n",errorString); printf("************************************************\n\n"); printf("Select Option\t\t\t\t\n"); printf(" 1. Add Schema/Library\n"); printf(" 2. Select Table Names from entered Schemas/Library\n"); printf(" 3. Start Collecting Data\n"); printf(" 4. View Data Collected\n"); printf(" 5. Clear Selected:\n"); printf(" 6. Clear Schema/Librarys Entered\n"); printf(" 7. Clear Collected Data\n"); printf(" 9. Exit\n"); printf("Please enter number: \n"); } /***************************************************************************/ /* */ /* Function: printLogin */ /* */ /* Description: Login for use of Sessions */ /* Currently Not being used */ /***************************************************************************/ void printLogin() { printf("\n\n\n\n\n\n\n\n\n\nPseudo Journaling\n\n"); printf("************************************************\n\n"); printf("--------------Login-------------------------\n"); printf("Select Option\t\t\t\t\n"); printf(" 1. Create Session\n"); printf(" 2. Load Session\n"); printf(" 9. Exit\n"); printf("Please enter number: \n"); } /***************************************************************************/ /* */ /* Function: tkGetLine */ /* */ /* Description: Retrives Line of User input and stores in userInput var */ /* */ /* Paramaters: userInput - char to store user input */ /* size - int max size of user input */ /***************************************************************************/ void tkGetLine(char* userInput ,int size) { char ch; int char_count= 0; ch = getchar(); while( ch != '\n' ) { if(char_count < size) userInput[char_count++] = ch; ch = getchar(); } userInput[char_count] = 0x00; } /***************************************************************************/ /* */ /* Function: retrieveTables */ /* */ /* Description: Retrives Tables from schema specified and enters into dbs */ /* */ /* Paramaters: schemaName - char specifying schema to retrieve tables */ /* library - library to store results */ /***************************************************************************/ void retrieveTables(char* schemaName ,char* library) { int counter = 0; /***************************************************************************/ /* SQL Variable Declaration */ /***************************************************************************/ EXEC SQL BEGIN DECLARE SECTION; char rtvstmt[1024]; char stmt1[1024]; char PUTTABLE[7]; char schema[30]; char table[30]; EXEC SQL END DECLARE SECTION; sprintf(rtvstmt,"select table_name, table_schema from Qsys2/syspartitionstat"); sprintf(rtvstmt,"%s where table_schema = '%s'",rtvstmt,schemaName); EXEC SQL PREPARE PUTTABLE FROM :rtvstmt; EXEC SQL DECLARE CURSOR1 CURSOR FOR PUTTABLE; EXEC SQL OPEN CURSOR1 USING :PUTTABLE; EXEC SQL WHENEVER NOT FOUND GOTO close_cursor; while(1) { EXEC SQL FETCH CURSOR1 INTO :table, :schema; if(sqlca.sqlcode < 0) { printf("SQL ERROR CODE %d\n",sqlca.sqlcode); } sprintf(stmt1,"INSERT INTO %s/%s values('%s','%s',0)", library,TABLE_NAME,schema,table); EXEC SQL EXECUTE IMMEDIATE :stmt1; if(sqlca.sqlcode == -803) { printf("Table %s in Schema %s already exists\n",table,schema); } else counter++; } close_cursor: EXEC SQL CLOSE CURSOR1; EXEC SQL WHENEVER NOT FOUND CONTINUE; printf("\n%i tables have been entered into %s/%s\n", counter,library,TABLE_NAME); sleep(4); } /***************************************************************************/ /* */ /* Function: returnTables */ /* */ /* Description: Retrurn Tables from TABLE_NAME */ /* */ /* Paramaters: library - library holding TABLE_NAME */ /***************************************************************************/ void returnTables(char *library) { int counter = 0; int tableCount = 0; int x= 0; int i = 0; /*************************************************************************/ /* tableNames/schemaNames stores tables - current limit 10000 */ /* Future the size of these values should be created dynamically */ /* alternative option is to allow only x number to be selected per time */ /*************************************************************************/ int tableNumber = -1; char tableNames[10000][30]; char schemaNames[10000][30]; int selected[100]; char userResponse[10]; int userInt = -1; /*************************************************************************/ /* SQL Variable Declaration */ /*************************************************************************/ EXEC SQL BEGIN DECLARE SECTION; char stmt2[1024]; char RTVTABLE[7]; char schema2[30]; char table2[30]; EXEC SQL END DECLARE SECTION; sprintf(stmt2,"select RTRIM(schemaName), RTRIM(tableName) from %s/%s", library,TABLE_NAME); sprintf(stmt2,"%s where selected = 0",stmt2); EXEC SQL PREPARE RTVTABLE FROM :stmt2; EXEC SQL DECLARE CURSOR2 CURSOR FOR RTVTABLE; EXEC SQL OPEN CURSOR2 USING :RTVTABLE; EXEC SQL WHENEVER NOT FOUND GOTO close_cursor2; while(1) { EXEC SQL FETCH CURSOR2 INTO :schema2, :table2; sprintf(tableNames[counter],"%s",table2); sprintf(schemaNames[counter],"%s",schema2); counter++; if(counter >= 10000) { printf("Number of Tables Exceeds %i\n",counter); printf("Only Displaying up to Max number of Tables\n"); printf("Work Around: Select tables in chunks\n"); break; } } close_cursor2: EXEC SQL CLOSE CURSOR2; EXEC SQL WHENEVER NOT FOUND CONTINUE; if(counter == 0) { printf("There are currently no tables to select\n"); printf("Go back to Main menu and enter in a schema\n"); sleep(3); return; } for(i;i 10) { tableCount = tableCount -10; } else if(toupper(userResponse[0]) == 'N' && x < counter && x != 0) { tableCount = tableCount+10; } x = tableCount; for(x;x 10) printf("To View the Previous 10 Tables Press 'P' and Then Enter:\n"); printf("To Select All of the Tables Press Enter:\n"); printf("To Exit to Main Menu Press 'E' and Then Enter:\n"); tkGetLine(userResponse,10); userInt = atoi(userResponse); /************************************************************************/ /* Change Status of Table to Selected */ /************************************************************************/ if(userInt <= counter && userInt >= 1) { if(selected[userInt-1] == 1) { selected[userInt-1] = 0; sprintf(stmt2,"update %s/%s set selected = 0",library,TABLE_NAME); sprintf(stmt2,"%s where tableName = '%s' and schemaName = '%s'", stmt2,tableNames[userInt-1],schemaNames[userInt-1]); EXEC SQL EXECUTE IMMEDIATE :stmt2; } else{ selected[userInt-1] = 1; sprintf(stmt2,"update %s/%s set selected = 1",library,TABLE_NAME); sprintf(stmt2,"%s where tableName = '%s' and schemaName = '%s'", stmt2,tableNames[userInt-1],schemaNames[userInt-1]); EXEC SQL EXECUTE IMMEDIATE :stmt2; } } /************************************************************************/ /* Change All Tables's status to Selected */ /************************************************************************/ else if(userResponse[0] == 0) { for(x=0;x= 10000) { printf("Number of Tables Exceeds %i\n",counter); printf("Only Displaying up to Max number of Tables\n"); printf("Work Around: De-Select tables in chunks\n"); break; } } close_cursor3: EXEC SQL CLOSE CURSOR3; EXEC SQL WHENEVER NOT FOUND CONTINUE; if(counter == 0) { printf("There are currently no tables select\n"); printf("Go back to Main menu and select table names\n"); sleep(3); return; } for(i;i 10) { tableCount = tableCount -10; } else if(toupper(userResponse[0]) == 'N' && x < counter && x != 0) { tableCount = tableCount+10; } x = tableCount; for(x;x 10) printf("To View the Previous 10 Tables Press 'P' and Then Enter:\n"); printf("To Exit to Main Menu Press 'E' and Then Enter:\n"); tkGetLine(userResponse,10); userInt = atoi(userResponse); /************************************************************************/ /* Change Status of Table to De-Selected */ /************************************************************************/ if(userInt <= counter && userInt >= 1) { if(selected[userInt-1] == 0) { selected[userInt-1] = 1; sprintf(stmt3,"update %s/%s set selected = 1",library,TABLE_NAME); sprintf(stmt3,"%s where tableName = '%s' and schemaName = '%s'", stmt3,tableNames[userInt-1],schemaNames[userInt-1]); EXEC SQL EXECUTE IMMEDIATE :stmt3; } else { selected[userInt-1] = 0; sprintf(stmt3,"update %s/%s set selected = 0",library,TABLE_NAME); sprintf(stmt3,"%s where tableName = '%s' and schemaName = '%s'", stmt3,tableNames[userInt-1],schemaNames[userInt-1]); EXEC SQL EXECUTE IMMEDIATE :stmt3; } } } } /***************************************************************************/ /* */ /* Function: getTime */ /* */ /* Description: Get Time - This Method is for Batch Testing */ /***************************************************************************/ void getTime(){ time_t rawtime; struct tm * timeinfo; time ( &rawtime ); timeinfo = localtime ( &rawtime ); printf ( "Current date and time are: %s", asctime (timeinfo) ); printf ( "Hour is %i\n",timeinfo->tm_hour); printf ( "Year is %i\n",timeinfo->tm_year); } /***************************************************************************/ /* */ /* Function: collectDataInter */ /* */ /* Description: Submits CRTREPORT to batch, which will run a query against */ /* tables specified in TABLE_NAME */ /* */ /* Paramaters: library - char to store library name */ /***************************************************************************/ void collectDataInter(char* library) { int samples = -1; int timeInterval = -1; char table[30]; rtvTableName(library,table); printf("Enter Number of Samples to Take:\n"); scanf("%i" ,&samples); getchar(); printf("Enter Time in Seconds between Samples:\n"); scanf("%i" ,&timeInterval); getchar(); sprintf(pgm,"SBMJOB CMD(CALL PGM(PSEUDOJRN/CRTREPORT) PARM('%s' '%s' '%s' ", library,TABLE_NAME,table); sprintf(pgm,"%s '%s' '0' '%i' '%i'))",pgm,REPORT_TABLE,samples,timeInterval); length = strlen(pgm); cpynv(NUM_DESCR(_T_PACKED,15,5), &packed_length, NUM_DESCR(_T_SIGNED,4,0), &length); QCMDEXC(pgm, packed_length); } /***************************************************************************/ /* */ /* Function: collectDataBatch */ /* */ /* Description: Submits CRTREPORT to batch, which will run a query against */ /* tables specified starting at startTime ending at endTime */ /* */ /* Paramaters: library - char to store library name */ /***************************************************************************/ void collectDataBatch(char* library) { char startTime[11]; char endTime[11]; char placeHolder[2]; int samples = -1; char table[30]; struct tm tmpstartT, *startT; struct tm tmpendT, *endT; int failed = 0; startT = &tmpstartT; endT = &tmpendT; rtvTableName(library,table); /************************************************************************/ /* Currently Little Error Checking for Times */ /* Future Make sure Times entered is a valid Time */ /* - startTime Greater then current time */ /* - or simple start right away/recalculate time per sample*/ /************************************************************************/ do { failed = 0; printf("Enter Start Time: (YYMMDDHHMM)\n"); tkGetLine(startTime ,30); if(strlen(startTime) != 10) { printf("Start Time was incorrect (YYMMDDHHMM):\n"); failed = 1; } sprintf(placeHolder,"1%c%c",startTime[0],startTime[1]); printf("%s\n",placeHolder); tmpstartT.tm_year = atoi(placeHolder);; printf ( "Year is %i\n",startT->tm_year); }while(failed); do { failed = 0; printf("Enter End Time: (YYMMDDHHMM)\n"); tkGetLine(endTime ,30); if(strlen(endTime) != 10) { printf("End Time was incorrect (YYMMDDHHMM):\n"); failed = 1; } }while(failed); printf("Enter Number of Samples to Take:\n"); scanf("%i" ,&samples); getchar(); //Method used for error checking of batch option //getTime(); sprintf(pgm,"SBMJOB CMD(CALL PGM(PSEUDOJRN/CRTREPORT) PARM('%s' '%s' '%s' ", library,TABLE_NAME,table); sprintf(pgm,"%s '%s' '0' '%i' '%s' '%s'))", pgm,REPORT_TABLE,samples,startTime,endTime); length = strlen(pgm); cpynv(NUM_DESCR(_T_PACKED,15,5), &packed_length, NUM_DESCR(_T_SIGNED,4,0), &length); QCMDEXC(pgm, packed_length); } /***************************************************************************/ /* */ /* Function: rtvTableName */ /* */ /* Description: Retrieves Table Name from User input */ /* */ /* Paramaters: library - char to store library name */ /***************************************************************************/ void rtvTableName(char* library, char* table) { /***************************************************************************/ /* SQL Variable Declaration */ /***************************************************************************/ EXEC SQL BEGIN DECLARE SECTION; char stmt6[1024]; EXEC SQL END DECLARE SECTION; while(1){ printf("Enter Name of Table to Store Collected Data:\n"); tkGetLine(table ,30); rmvSpaces(table); /*************************************************************************/ /* Update Status to Pending */ /*************************************************************************/ sprintf(stmt6,"INSERT INTO %s/%s VALUES('%s',-1)", library,REPORT_TABLE,table); EXEC SQL EXECUTE IMMEDIATE :stmt6; if(sqlca.sqlcode == -803) { printf("Table %s already exists\n",table); } else break; } } /***************************************************************************/ /* */ /* Function: viewReports */ /* */ /* Description: Get Time */ /***************************************************************************/ void viewReports(char* library){ int counter = 0; int counter2 = 0; char userResponse[3]; char userResponse2[3]; char userResponse3; int reportCount = 0; int tableCount = 0; int x= 0; int userInt = -1; int userInt2 = -1; /***************************************************************************/ /* Maximum Number of Reports currently allowed = 100 */ /***************************************************************************/ char reportNames[100][30]; char tableNames[10000][30]; char schemaNames[10000][30]; char noBefore[100]; int statusArray[100]; /***************************************************************************/ /* SQL Variable Declaration */ /***************************************************************************/ EXEC SQL BEGIN DECLARE SECTION; char stmt7[1024]; char RTVREPORTS[7]; char RTVTABLE4[7]; char reportName[30]; char tableName[30]; char schemaName[30]; long status = -1; EXEC SQL END DECLARE SECTION; sprintf(stmt7,"select RTRIM(ReportName), Status from %s/%s", library,REPORT_TABLE); sprintf(stmt7,"%s where status != -1",stmt7); EXEC SQL PREPARE RTVREPORTS FROM :stmt7; EXEC SQL DECLARE CURSOR5 CURSOR FOR RTVREPORTS; EXEC SQL OPEN CURSOR5 USING :RTVREPORTS; EXEC SQL WHENEVER NOT FOUND GOTO close_cursor5; while(1) { EXEC SQL FETCH CURSOR5 INTO :reportName, :status; sprintf(reportNames[counter],"%s",reportName); statusArray[counter] = status; counter++; //Exceede Maximum Allowable Reports if(counter >= 100) { printf("Exceeded Maximum Allowable Reports\n"); printf("Only Showing First 100 Reports\n"); break; } } close_cursor5: EXEC SQL CLOSE CURSOR5; EXEC SQL WHENEVER NOT FOUND CONTINUE; if(counter == 0) { printf("There are currently no reports to select\n"); printf("Go back to Main menu and create a reports\n"); sleep(3); return; } while(toupper(userResponse[0]) != 'E') { userResponse2[0] = ' '; counter2 = 0; printf("\n\n\n\n\n\n\n\n\n\n\n%i Reports have been generated\n", counter); printf("The Reports Generated Are:\n"); printf("\tNumber \tReportName \tStatus\n"); if(toupper(userResponse[0]) == 'P' && x > 10) { reportCount = reportCount -10; } else if(toupper(userResponse[0]) == 'N' && x < counter && x != 0) { reportCount = reportCount+10; } x = reportCount; for(x;x 10) printf("To View the Previous 10 Reports Press 'P' and Then Enter:\n"); printf("To Exit to Main Menu Press 'E' and Then Enter:\n"); scanf("%s",&userResponse); getchar(); userInt = atoi(userResponse); if(userInt <= counter && userInt > 0) { // printf("You have Selected Report %s\n",reportNames[userInt]); // printf("Currently This is not an option. Press Enter to continue\n"); // getchar(); // sprintf(stmt7,"select DISTINCT RTRIM(SCHEMA), RTRIM(TABLENAME)" " from %s/%s", library,reportNames[userInt-1]); EXEC SQL PREPARE RTVTABLE4 FROM :stmt7; EXEC SQL DECLARE CURSOR6 CURSOR FOR RTVTABLE4; EXEC SQL OPEN CURSOR6 USING :RTVTABLE4; EXEC SQL WHENEVER NOT FOUND GOTO close_cursor6; while(1) { EXEC SQL FETCH CURSOR6 INTO :schemaName, :tableName; sprintf(tableNames[counter2],"%s",tableName); sprintf(schemaNames[counter2],"%s",schemaName); counter2++; } close_cursor6: EXEC SQL CLOSE CURSOR6; EXEC SQL WHENEVER NOT FOUND CONTINUE; while(toupper(userResponse2[0]) != 'E') { printf("\n\n\n\n\n\n\n\n\n\n\n%i Tables are in Report %s\n", counter2,reportNames[userInt-1]); printf("The Reports Generated Are:\n"); printf("\tNumber \tLibrary \tTableName\n"); if(toupper(userResponse[0]) == 'P' && x > 10) { tableCount = tableCount -10; } else if(toupper(userResponse[0]) == 'N' && x < counter && x != 0) { tableCount = tableCount+10; } x = tableCount; for(x;x 10) printf("To View the Previous 10 Tabes Press 'P' and Then Enter:\n"); printf("To Exit to Main Menu Press 'E' and Then Enter:\n"); //scanf("%s",&userResponse2); //getchar(); tkGetLine(userResponse2,3); userInt2 = atoi(userResponse2); //Show data for entire Report if(userResponse2[0] == 0) { createReport(library,reportNames[userInt-1],"-1","-1"); printf("\nPress Enter to Continue\n"); userResponse3 = getchar(); sprintf(noBefore,"rmBefore%s",reportNames[userInt-1]); printf("\nIf Before Images were turned Off...\n"); createReport(library,noBefore,"-1","-2"); printf("\nPress Enter to Continue\n"); userResponse3 = getchar(); } else if(userInt2 > 0 && userInt2 <= counter2) { createReport(library,reportNames[userInt-1],tableNames[userInt2-1], schemaNames[userInt2-1]); printf("Press Enter to Continue\n"); userResponse3 = getchar(); } } } } } /***************************************************************************/ /* */ /* Function: createReport */ /* */ /* Description: Return bytes per second (MAX and AVG) */ /* */ /* Paramaters: library - char library name */ /* reportName - char report Name to analyze */ /* tableName - char name of table to group data by */ /***************************************************************************/ void createReport(char* library, char* reportName, char* tableName, char* schemaName) { char * substring; long diskWrites = -1; long cacheWrites = -1; /***************************************************************************/ /* SQL Variable Declaration */ /***************************************************************************/ EXEC SQL BEGIN DECLARE SECTION; char stmt8[1024]; char RTVDATA[7]; char RTVMAX[7]; char RTVAVG[7]; char RTVDATA2[7]; char RTVMAX2[7]; char RTVAVG2[7]; char DISKWRITE[7]; long long totalData = -1; long totalTime = -1; long maxData = -1; long avgData = -1; long deltaPt = -1; long deltaDl = -1; long deltaUp = -1; EXEC SQL END DECLARE SECTION; /***************************************************************************/ /* View to store delta values for report. naming scheame VIEW[reportName] */ /***************************************************************************/ sprintf(stmt8,"CREATE VIEW %s/VIEW%s AS SELECT A.SAMPLENUM, " "A.DATASINCEIPL-B.DATASINCEIPL AS DELTA_DATA, " "A.PT-B.PT AS DELTA_PT,A.UP-B.UP AS DELTA_UP, " "A.DL-B.DL AS DELTA_DL, TIMESTAMPDIFF(2,CHAR(A.TIME-B.TIME)) " "AS DELTA_TIME, A.TABLENAME, A.SCHEMA FROM %s/%s A, %s/%s B where " "A.TABLENAME = B.TABLENAME AND A.SCHEMA = B.SCHEMA AND " "A.SAMPLENUM = (B.SAMPLENUM+1)" ,library,reportName,library,reportName,library,reportName); EXEC SQL EXECUTE IMMEDIATE :stmt8; if(sqlca.sqlcode < 0 ) { //printf("ERROR %i",sqlca.sqlcode); if(sqlca.sqlcode = -601) { //printf("View already exists\n"); } } /***************************************************************************/ /* Generate Statistics for entire Report */ /***************************************************************************/ if(strcmp(tableName,"-1") == 0 ) { sprintf(stmt8,"select SUM(DELTA_DATA),SUM(DElTA_TIME)/(select count(*)" " from %s/view%s where samplenum = 2) from %s/view%s ", library,reportName,library,reportName); EXEC SQL PREPARE RTVDATA2 FROM :stmt8; EXEC SQL DECLARE CURSOR11 CURSOR FOR RTVDATA2; EXEC SQL OPEN CURSOR11 USING :RTVDATA2; if(sqlca.sqlcode < 0 ) { printf("ERROR %i",sqlca.sqlcode); } EXEC SQL FETCH CURSOR11 INTO :totalData, :totalTime; EXEC SQL CLOSE CURSOR11; if(strcmp(schemaName,"-2") == 0) { printf("\t%lld bytes of data over %d seconds would have been produced\n", totalData,totalTime); } else { printf("Report %s produced %lld bytes of data over %d seconds\n", reportName,totalData,totalTime); } sprintf(stmt8,"select SUM(Delta_PT),SUM(Delta_UP),SUM(Delta_DL)" " from %s/view%s", library,reportName); EXEC SQL PREPARE DISKWRITE FROM :stmt8; EXEC SQL DECLARE CURSOR14 CURSOR FOR DISKWRITE; EXEC SQL OPEN CURSOR14 USING :DISKWRITE; if(sqlca.sqlcode < 0 ) { printf("ERROR %i",sqlca.sqlcode); } EXEC SQL FETCH CURSOR14 INTO :deltaPt, :deltaUp, :deltaDl; EXEC SQL CLOSE CURSOR14; diskWrites = deltaPt+deltaUp+deltaDl; printf("\tProduced by %d Inserts, %d Updates, %d Deletes\n", deltaPt,deltaUp,deltaDl); printf("\tCausing up to %d writes to disk\n",diskWrites); sprintf(stmt8,"CREATE VIEW %s/tmpView2 as SELECT CAST(SUM(DELTA_DATA)" " AS FLOAT)/CAST(AVG(DELTA_TIME) AS FLOAT) AS DATASEC, samplenum," "AVG(DELTA_TIME) as TIME FROM %s/view%s group by samplenum", library,library,reportName); EXEC SQL EXECUTE IMMEDIATE :stmt8; if(sqlca.sqlcode < 0 ) { if(sqlca.sqlcode = -601) { // printf("View already exists\n"); } } sprintf(stmt8,"select MAX(DATASEC) from %s/tmpView2",library); EXEC SQL PREPARE RTVMAX2 FROM :stmt8; EXEC SQL DECLARE CURSOR12 CURSOR FOR RTVMAX2; EXEC SQL OPEN CURSOR12 USING :RTVMAX2; if(sqlca.sqlcode < 0 ) { printf("ERROR %i",sqlca.sqlcode); } EXEC SQL FETCH CURSOR12 INTO :maxData; EXEC SQL CLOSE CURSOR12; if(strcmp(schemaName,"-2") == 0) { printf("\tCreating a max throughput rate of %d bytes/second\n" ,maxData); } else { printf("\tProducing a max throughput rate of %d bytes/second\n", maxData); } sprintf(stmt8,"select AVG(DATASEC) from %s/tmpView2",library); EXEC SQL PREPARE RTVAVG2 FROM :stmt8; EXEC SQL DECLARE CURSOR13 CURSOR FOR RTVAVG2; EXEC SQL OPEN CURSOR13 USING :RTVAVG2; if(sqlca.sqlcode < 0 ) { printf("ERROR %i",sqlca.sqlcode); } EXEC SQL FETCH CURSOR13 INTO :avgData; EXEC SQL CLOSE CURSOR13; if(strcmp(schemaName,"-2") == 0) { printf("\tCreating an average throughput rate of %d bytes/second\n", avgData); } else { printf("\tProducing an average throughput rate %d bytes/second\n", avgData); } cacheWrites = ceil(totalData/131072); //Making sure caching option doesnt produce more diskwrites //this will happen if the row sizes are larger than 128 kb if(cacheWrites > diskWrites) { cacheWrites = diskWrites; } printf("\nIf Option 42 was purchased enabling Journal Caching" "...\n\tA Minimum of %d writes to disk would have been " "performed\n", cacheWrites); sprintf(stmt8,"drop view %s/tmpView2",library); EXEC SQL EXECUTE IMMEDIATE :stmt8; if(sqlca.sqlcode < 0 ) { printf("ERROR %i",sqlca.sqlcode); } } /***************************************************************************/ /* Generate Statistics for specific Table */ /***************************************************************************/ else{ sprintf(stmt8,"select SUM(DELTA_DATA),SUM(DElTA_TIME) from %s/view%s " "where tableName = '%s'", library,reportName,tableName); EXEC SQL PREPARE RTVDATA FROM :stmt8; EXEC SQL DECLARE CURSOR8 CURSOR FOR RTVDATA; EXEC SQL OPEN CURSOR8 USING :RTVDATA; if(sqlca.sqlcode < 0 ) { printf("ERROR %i",sqlca.sqlcode); } EXEC SQL FETCH CURSOR8 INTO :totalData, :totalTime; EXEC SQL CLOSE CURSOR8; printf("Table %s produced %lld bytes of data over %d seconds\n", tableName,totalData,totalTime); sprintf(stmt8,"CREATE VIEW %s/tmpView1 as SELECT CAST(DELTA_DATA AS FLOAT)" "/CAST(DELTA_TIME AS FLOAT) AS DATASEC, samplenum, DELTA_TIME" " FROM %s/view%s where tableName" "='%s' and schema = '%s'",library,library, reportName,tableName,schemaName); EXEC SQL EXECUTE IMMEDIATE :stmt8; if(sqlca.sqlcode < 0 ) { if(sqlca.sqlcode = -601) { // printf("View already exists\n"); } } sprintf(stmt8,"select MAX(DATASEC) from %s/tmpView1",library); EXEC SQL PREPARE RTVMAX FROM :stmt8; EXEC SQL DECLARE CURSOR9 CURSOR FOR RTVMAX; EXEC SQL OPEN CURSOR9 USING :RTVMAX; if(sqlca.sqlcode < 0 ) { printf("ERROR %i",sqlca.sqlcode); } EXEC SQL FETCH CURSOR9 INTO :maxData; EXEC SQL CLOSE CURSOR9; printf("Table %s produced a max throughput rate of %d bytes/second\n", tableName,maxData); sprintf(stmt8,"select AVG(DATASEC) from %s/tmpView1",library); EXEC SQL PREPARE RTVAVG FROM :stmt8; EXEC SQL DECLARE CURSOR10 CURSOR FOR RTVAVG; EXEC SQL OPEN CURSOR10 USING :RTVAVG; if(sqlca.sqlcode < 0 ) { printf("ERROR %i",sqlca.sqlcode); } EXEC SQL FETCH CURSOR10 INTO :avgData; EXEC SQL CLOSE CURSOR10; printf("Table %s produced an average throughput rate %d bytes/second\n", tableName,avgData); sprintf(stmt8,"drop view %s/tmpView1",library); EXEC SQL EXECUTE IMMEDIATE :stmt8; if(sqlca.sqlcode < 0 ) { printf("ERROR %i",sqlca.sqlcode); } } } /***************************************************************************/ /* */ /* Function: clearSelected */ /* */ /* Description: clear all selected tables */ /* */ /* Paramaters: library - library holding TABLE_NAME */ /***************************************************************************/ void clearSelected(char* library) { /***************************************************************************/ /* SQL Variable Declaration */ /***************************************************************************/ EXEC SQL BEGIN DECLARE SECTION; char stmt4[1024]; EXEC SQL END DECLARE SECTION; sprintf(stmt4,"update %s/%s set selected = 0",library,TABLE_NAME); EXEC SQL EXECUTE IMMEDIATE :stmt4; } /***************************************************************************/ /* */ /* Function: clearSchemas */ /* */ /* Description: clear all selected tables */ /* */ /* Paramaters: library - library holding TABLE_NAME */ /***************************************************************************/ void clearSchemas(char* library) { /***************************************************************************/ /* SQL Variable Declaration */ /***************************************************************************/ EXEC SQL BEGIN DECLARE SECTION; char stmt5[1024]; EXEC SQL END DECLARE SECTION; sprintf(stmt5,"delete from %s/%s",library,TABLE_NAME); EXEC SQL EXECUTE IMMEDIATE :stmt5; } /***************************************************************************/ /* */ /* Function: clearReports */ /* */ /* Description: clear all Reports from Report file */ /* */ /* Paramaters: library - library holding TABLE_NAME */ /***************************************************************************/ void clearReports(char* library) { /***************************************************************************/ /* SQL Variable Declaration */ /***************************************************************************/ EXEC SQL BEGIN DECLARE SECTION; char stmt10[1024]; char REPORTNAME[7]; char reportName2[30]; EXEC SQL END DECLARE SECTION; sprintf(stmt10,"select RTRIM(ReportName) from %s/%s", library,REPORT_TABLE); EXEC SQL PREPARE REPORTNAME FROM :stmt10; EXEC SQL DECLARE CURSOR7 CURSOR FOR REPORTNAME; EXEC SQL OPEN CURSOR7 USING :REPORTNAME; EXEC SQL WHENEVER NOT FOUND GOTO close_cursor7; /***************************************************************************/ /* First Drop Associated View then Drop TAble */ /***************************************************************************/ while(1) { EXEC SQL FETCH CURSOR7 INTO :reportName2; printf("*%s*\n",reportName2); sprintf(stmt10,"drop view %s/VIEW%s",library,reportName2); EXEC SQL EXECUTE IMMEDIATE :stmt10; sprintf(stmt10,"drop table %s/%s",library,reportName2); EXEC SQL EXECUTE IMMEDIATE :stmt10; } close_cursor7: EXEC SQL CLOSE CURSOR7; EXEC SQL WHENEVER NOT FOUND CONTINUE; sprintf(stmt10,"delete from %s/%s",library,REPORT_TABLE); EXEC SQL EXECUTE IMMEDIATE :stmt10; } /***************************************************************************/ /* */ /* Function: strupr */ /* */ /* Description: Convert Characer Array to upper case */ /* */ /* Parameters: string - Char array */ /***************************************************************************/ char* strupr(char* string) { int maxLen = strlen(string); int x = 0; for(x = 0; x