/* * This C program will read an DSPJRN outfile of *TYPE1 and return a file * containing a list of objects, and the number of entries with a joentt * = 'FC' and the number of entries with a jocode = 'B'. * The following is an example of a DSPJRN command with the options * required: * DSPJRN JRN(LIB/JRN) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE1) * OUTFILE(LIB/OUTFILE) INCHIDENT(*YES) * * Syntax: * FCCOUNT lib file * lib - library * outfile - dspjrn outfile * viewlib - view library * view - view * outlib - output file library * output - output file (table) * * Compile Statement: CRTSQLCI OBJ(LIB/FCCOUNT) SRCFILE(LIB/FCCOUNT) COMMIT(*NONE) OBJTYPE(*PGM) * * Run Statement: CALL LIB/FCCOUNT PARM('LIB' 'OUTFILE' 'VIEWLIB' 'VIEW' 'OUTLIB' 'OUTPUT') * * Sample SQL query to retrieve all results, ordered from greatest number of * full closes to least: SELECT * FROM OUTLIB/OUTPUT ORDER BY NUMB DESC */ /* include the necessary C header files */ #include #include #include #include /* Statements required for embedded SQL */ EXEC sql include SQLCA; EXEC sql include SQLDA; /* Begin main function */ int main(int argc, char *argv[]) { EXEC SQL BEGIN DECLARE SECTION; int i; /* a simple counter */ long int numobjs = 0; /* number of object/lib types in outfile */ long int numBs = 0; /* number of updates (joentt is 'UP, UP, DL') */ long int numFCs = 0; /* number of puts (joentt is 'PT') */ long int curr_ent = 1; /* current entry being processed */ char selstmt[200]; /* character arrays to hold our select */ char sel2stmt[200]; /* statements */ char sel3stmt[200]; char sel4stmt[200]; char statement[200]; char lib[11]; /* character array to hold input library name */ char outfile[11]; /* character array to hold outfile name */ char viewlib[11]; /* character array to hold the view lib name */ char view[11]; /* character array to hold the view name */ char outlib[11]; /* character array to hold the output file library name */ char output[11]; /* character array to hold the output file name */ char objname[11]; /* character array to hold the object name */ char libname[11]; /* character array to hold the library name */ char mbrname[11]; /* character array to hold the member name */ char jobname[11]; /* character array to hold the job name */ EXEC SQL DECLARE c1 CURSOR FOR exestmt; /* declare SQL cursor C1 */ EXEC SQL DECLARE c2 CURSOR FOR actstmt; /* declare SQL cursor C2 */ EXEC SQL DECLARE c3 CURSOR FOR exestmt1; /* declare SQL cursor C3 */ EXEC SQL DECLARE c4 CURSOR FOR dbstmt; /* declare SQL cursor C4 */ EXEC SQL END DECLARE SECTION; /****************************************** * Parsing command line / error checking ******************************************/ if (argc != 7) { printf("ERROR - proper syntax is: "); printf("FCCOUNT lib outfile viewlib view outlib outputfile"); } else { /* extract the library from the first argument */ sprintf(lib, "%s", argv[1]); /* extract the outfile from the second argument */ sprintf(outfile, "%s", argv[2]); /* extract the view library from the third argument */ sprintf(viewlib, "%s", argv[3]); /* extract the view from the fourth argument */ sprintf(view, "%s", argv[4]); /* extract the output library from the fifth argument */ sprintf(outlib, "%s", argv[5]); /* extract the output file from the sixth argument */ sprintf(output, "%s", argv[6]); /* convert the lib, outfile, view lib, and view to upper case */ for (i = 0; i < 10; i++) { lib[i] = toupper(lib[i]); outfile[i] = toupper(outfile[i]); viewlib[i] = toupper(viewlib[i]); view[i] = toupper(view[i]); outlib[i] = toupper(outlib[i]); output[i] = toupper(output[i]); } /* create the view with the obj/lib/mbr/job quads */ sprintf(selstmt, "CREATE VIEW %s/%s AS SELECT JOOBJ, ", viewlib, view); sprintf(sel2stmt, "JOLIB, JOMBR, JOJOB FROM %s/%s ", lib, outfile); sprintf(sel3stmt, " GROUP BY JOOBJ, JOLIB, JOMBR, JOJOB"); sprintf(statement, "%s%s%s", selstmt, sel2stmt, sel3stmt); EXEC SQL EXECUTE IMMEDIATE :statement; /* count the number of rows in the view */ sprintf(selstmt, "SELECT COUNT(*) FROM %s/%s", viewlib, view); EXEC SQL PREPARE actstmt FROM :selstmt; EXEC SQL OPEN c2; /* open the SQL view (cursor) */ EXEC SQL FETCH c2 INTO :numobjs; /* set the number of objects */ EXEC SQL CLOSE c2; /* close the SQL cursor */ /* output the number of objects to the screen */ printf("\n\n\n\n\n\n\n"); printf("Number of object/library/member/jobs = %d\n", numobjs); /* Create SQL query which will return the object names */ sprintf(selstmt, "SELECT JOOBJ, JOLIB, JOMBR, "); sprintf(sel2stmt, " JOJOB FROM %s/%s GROUP BY ", lib, outfile); sprintf(sel3stmt, " JOOBJ, JOLIB, JOMBR, JOJOB"); sprintf(statement, "%s%s%s", selstmt, sel2stmt, sel3stmt); EXEC SQL PREPARE exestmt FROM :statement; EXEC SQL OPEN c1; /* open the SQL view (cursor) */ /* create the table in which output will be placed */ sprintf(selstmt, "CREATE TABLE %s/%s (OBJ CHAR(10), ", outlib, output); sprintf(sel2stmt, "LIB CHAR(10), MBR CHAR(10), JOB "); sprintf(sel3stmt, "CHAR(10), NUMB INT, NUMFC INT)"); sprintf(statement, "%s%s%s", selstmt, sel2stmt, sel3stmt); EXEC SQL EXECUTE IMMEDIATE :statement; /* loop through each object in the outfile */ while(curr_ent <= numobjs) { numBs = 0; numFCs = 0; /* get the next available object */ EXEC SQL FETCH c1 INTO :objname, :libname, :mbrname, :jobname; /* count the number of entries in an obj that have a entry type of 'FC' */ sprintf(selstmt, "SELECT COUNT(*) FROM %s/%s WHERE ", lib, outfile); sprintf(sel2stmt, "JOOBJ='%s' AND JOLIB='%s' AND ", objname, libname); sprintf(sel3stmt, "JOMBR='%s' AND JOJOB='%s' AND ", mbrname, jobname); sprintf(sel4stmt, "JOENTT='FC'"); sprintf(statement, "%s%s%s%s", selstmt, sel2stmt, sel3stmt, sel4stmt); EXEC SQL PREPARE exestmt1 FROM :statement; EXEC SQL OPEN c3; EXEC SQL FETCH c3 INTO :numFCs; EXEC SQL CLOSE c3; /* count the number of entries in an obj that have a code of 'B' */ sprintf(selstmt, "SELECT COUNT(*) FROM %s/%s WHERE ", lib, outfile); sprintf(sel2stmt, "JOOBJ='%s' AND JOLIB='%s' AND ", objname, libname); sprintf(sel3stmt, "JOMBR='%s' AND JOJOB='%s' AND ", mbrname, jobname); sprintf(sel4stmt, "JOCODE='B'"); sprintf(statement, "%s%s%s%s", selstmt, sel2stmt, sel3stmt, sel4stmt); EXEC SQL PREPARE exestmt1 FROM :statement; EXEC SQL OPEN c3; EXEC SQL FETCH c3 INTO :numBs; EXEC SQL CLOSE c3; /* if there are B types, insert the object name, number of * Bs and number of FCs into the output file. */ if(numBs != 0) { sprintf(selstmt, "INSERT INTO %s/%s ", outlib, output); sprintf(sel2stmt, "VALUES('%s', '%s', '%s', '%s',", objname, libname, mbrname, jobname); sprintf(sel3stmt, " %d, %d)", numBs, numFCs); sprintf(statement, "%s%s%s", selstmt, sel2stmt, sel3stmt); EXEC SQL EXECUTE IMMEDIATE :statement; } curr_ent++; } EXEC SQL CLOSE c1; } return 0; }