IBM DB2 for i: Code example

#include "string.h"
#include "stdlib.h"
#include "stdio.h"

main()
{
/* A sample program which updates the salaries for those employees   */
/* whose current commission total is greater than or equal to the    */
/* value of 'commission'. The salaries of those who qualify are in-  */
/* creased by the value of 'percentage' retroactive to 'raise_date'. */
/* A report is generated showing the projects which these employees  */
/* have contributed to ordered by project number and employee ID.    */
/* A second report shows each project having an end date occurring   */
/* after 'raise_date' (is potentially affected by the retroactive    */
/* raises) with its total salary expenses and a count of employees   */
/* who contributed to the project.                                   */

   short work_days  = 253;          /* work days during in one year */
   float commission = 2000.00;      /* cutoff to qualify for raise  */
   float percentage = 1.04;         /* raised salary as percentage  */
   char raise_date??(12??) = "1982-06-01"; /*  effective raise date */

   /* File declaration for qprint */
   FILE *qprint;

   /* Structure for report 1 */
   #pragma mapinc ("project","CORPDATA/PROJECT(PROJECT)","both","p z")
   #include "project"
   struct {
           CORPDATA_PROJECT_PROJECT_both_t Proj_struct;
           char  empno??(7??);
           char  name??(30??);
           float salary;
           } rpt1;

   /* Structure for report 2 */
   struct {
           char projno??(7??);
           char project_name??(37??);
           short employee_count;
           double total_proj_cost;
          } rpt2;

   exec sql include SQLCA;

   qprint=fopen("QPRINT","w");

   /* Update the selected projects by the new percentage. If an error */
   /* occurs during the update, ROLLBACK the changes.                 */
   EXEC SQL WHENEVER SQLERROR GO TO update_error;
   EXEC SQL
        UPDATE CORPDATA/EMPLOYEE
           SET SALARY = SALARY * :percentage
           WHERE COMM >= :commission ;

   /* Commit changes */
   EXEC SQL
        COMMIT;
   EXEC SQL WHENEVER SQLERROR GO TO report_error;

   /* Report the updated statistics for each employee assigned to the */
   /* selected projects.                                              */

   /* Write out the header for Report 1 */
   fprintf(qprint,"                     REPORT OF PROJECTS AFFECTED \
BY RAISES");
   fprintf(qprint,"\n\nPROJECT  EMPID     EMPLOYEE NAME    ");
   fprintf(qprint,  "                  SALARY\n");

   exec sql
        declare c1 cursor for
          select distinct projno, emp_act.empno,
                 lastname||', '||firstnme, salary
          from corpdata/emp_act, corpdata/employee
          where emp_act.empno = employee.empno and comm >= :commission
          order by projno, empno;
   EXEC SQL
        OPEN C1;

   /* Fetch and write the rows to QPRINT */
   EXEC SQL WHENEVER NOT FOUND GO TO done1;

   do {
     EXEC SQL
          FETCH C1 INTO :Proj_struct.PROJNO, :rpt1.empno,
                        :rpt1.name,:rpt1.salary;
     fprintf(qprint,"\n0.00",
             rpt1.Proj_struct.PROJNO,rpt1.empno,
             rpt1.name,rpt1.salary);
     }
   while (SQLCODE==0);

 done1:
   EXEC SQL
        CLOSE C1;

   /* For all projects ending at a date later than the 'raise_date'   * /
   /* (i.e. those projects potentially affected by the salary raises) */
   /* generate a report containing the project number, project name   */
   /* the count of employees participating in the project and the     */
   /* total salary cost of the project.                               */

   /* Write out the header for Report 2 */
   fprintf(qprint,"\n\n\n                     ACCUMULATED STATISTICS\
 BY PROJECT");
   fprintf(qprint,  "\n\nPROJECT                                    \
    NUMBER OF       TOTAL");
   fprintf(qprint,    "\nNUMBER   PROJECT NAME                      \
    EMPLOYEES       COST\n");

   EXEC SQL
        DECLARE C2 CURSOR FOR
          SELECT EMP_ACT.PROJNO, PROJNAME, COUNT(*),
             SUM ( ( DAYS(EMENDATE) - DAYS(EMSTDATE) ) * EMPTIME *
                   (DECIMAL( SALARY / :work_days ,8,2)))
          FROM CORPDATA/EMP_ACT, CORPDATA/PROJECT, CORPDATA/EMPLOYEE
          WHERE EMP_ACT.PROJNO=PROJECT.PROJNO  and
                EMP_ACT.EMPNO =EMPLOYEE.EMPNO  and
                PRENDATE > :raise_date
          GROUP BY EMP_ACT.PROJNO, PROJNAME
          ORDER BY 1;
   EXEC SQL
        OPEN C2;

   /* Fetch and write the rows to QPRINT */
   EXEC SQL WHENEVER NOT FOUND GO TO done2;

   do {
     EXEC SQL
          FETCH C2 INTO :rpt2;
     fprintf(qprint,"\n0.00",
             rpt2.projno,rpt2.project_name,rpt2.employee_count,
             rpt2.total_proj_cost);
   }
   while (SQLCODE==0);

 done2:
   EXEC SQL
        CLOSE C2;
   goto finished;

   /* Error occurred while updating table.  Inform user and rollback  */
   /* changes.                                                        */
 update_error:
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   fprintf(qprint,"*** ERROR Occurred while updating table.  SQLCODE="
           "    0\n",SQLCODE);
   EXEC SQL
        ROLLBACK;
   goto finished;

   /* Error occurred while generating reports.  Inform user and exit. */
 report_error:
   fprintf(qprint,"*** ERROR Occurred while generating reports.  "
           "SQLCODE=    0\n",SQLCODE);
   goto finished;

   /* All done  */
 finished:
   fclose(qprint);
   exit(0);

}