IBM Mainframe Forum

by **hakghen** » Mon Nov 10, 2008 5:27 pm

Hello again friends

I hope any of you could enlighten my mind with a little problem i'm having here with the select...

First of all, I created a table called ZCON039.EMPLOYEE using this code:

Select all

CREATE TABLE ZCON039.EMPLOYEE
(EMPNO CHAR(6) NOT NULL,
FORENAME CHAR(20) NOT NULL,
INITIAL CHAR(1),
SURNAME VARCHAR(50) NOT NULL,
SEX CHAR(1) NOT NULL,
DOB DATE,
TELEPHONE CHAR(6),
DATE_HIRED DATE,
JOB CHAR(20),
SALARY DECIMAL(9,2),
BONUS DECIMAL(9,2),
COMMISSION DECIMAL(9,2),
DEPTNO CHAR(3),
LOCID CHAR(10),
PRIMARY KEY(EMPNO),
FOREIGN KEY (DEPTNO)
REFERENCES DEPARTMENT(DEPTNO) ON DELETE NO ACTION,
FOREIGN KEY (LOCID)
REFERENCES LOCATION(LOCID) ON DELETE NO ACTION)
IN SMDB039. SMTS039;

I also have a ZCON039.DEPARTMENT with this structure:

Select all

Column Name Data Type (Length) Null Key
-----------------------------------------------------------------------------------------------------------
DEPTNO CHAR(3) NO PRIMARY
SUPERIOR_DEPTNO CHAR(3) YES
MGRNO CHAR(6) YES
NAME VARCHAR(50) NO

Now, what I am supposed to do is: Produce a list of the total salaries of each department.
I tried a lot of queries here but maybe, for lack of knowledge of mine, couldn't get the desired (or any) output...
Could any of you help me please?

I appreciate it, thanks in advance,

by **dick scherrer** » Tue Nov 11, 2008 12:58 am

Hello,

Please post your query and an example of what you want for output.

by **hakghen** » Wed Nov 12, 2008 3:37 am

Hello Dick,

I found out the query, but then, I got myself stuck into another error that is possibly preventing me from getting the results I need... In this reply I'll tell everything, but sorry for only 1 aspect that I'll have to mix both DB2 stuff with JCL (because I have a job to fill this table with data needed for the query).

So, here we go again, I had to use the SPUFI to build the following table:

Select all

CREATE TABLE ZCON039.EMPLOYEE
(EMPNO CHAR(6) NOT NULL,
FORENAME CHAR(20) NOT NULL,
INITIAL CHAR(1),
SURNAME VARCHAR(50) NOT NULL,
SEX CHAR(1) NOT NULL,
DOB DATE,
TELEPHONE CHAR(6),
DATE_HIRED DATE,
JOB CHAR(20),
SALARY DECIMAL(9,2),
BONUS DECIMAL(9,2),
COMMISSION DECIMAL(9,2),
DEPTNO CHAR(3),
LOCID CHAR(10),
PRIMARY KEY(EMPNO),
FOREIGN KEY (DEPTNO)
REFERENCES DEPARTMENT(DEPTNO) ON DELETE NO ACTION,
FOREIGN KEY (LOCID)
REFERENCES LOCATION(LOCID) ON DELETE NO ACTION)
IN SMDB039. SMTS039;

Then, I create a UNIQUE-PRIMARY-KEY index, using the following code on SPUFI.:

Select all

CREATE UNIQUE INDEX ZCON039.EMPLOYEEIX
ON ZCON039.EMPLOYEE (EMPNO);

After this, I have to fill the table, using the following JOB.:

Select all

//LOADEMP JOB NOTIFY=&SYSUID,REGION=64M
//*
// JCLLIB ORDER=(DB2.V8R1.PROCLIB)
//*
//SETPARM SET ALLOC='TRK',PRI='1',SEC='1'
//*
//STEP1 EXEC DSNUPROC,SYSTEM=DSNB,UID='ZCON039'
//STEPLIB DD DSN=DB2.V8R1.DSNB.SDSNEXIT,DISP=SHR
// DD DSN=DB2.V8R1.SDSNLOAD,DISP=SHR
//SYSREC00 DD DISP=SHR, INPUT DATA
// DSN=ZOS.DB2.DATA.EMPLOYEE
//SYSDISC DD SYSOUT=* DISCARDED RECORDS
//SYSERR DD UNIT=SYSALLDA,
// SPACE=(TRK,(1,1)) ERROR INFORMATION
//SYSMAP DD UNIT=SYSALLDA,
// SPACE=(TRK,(1,1)) INTERNAL MAPPING DATA SET
//SYSUT1 DD UNIT=SYSALLDA,
// SPACE=(&ALLOC,(&PRI,&SEC),RLSE)
//SORTOUT DD UNIT=SYSALLDA,
// SPACE=(&ALLOC,(&PRI,&SEC),RLSE)
//SYSIN DD *
LOAD DATA INDDN(SYSREC00) RESUME YES
INTO TABLE ZCON039.EMPLOYEE
(EMPNO POSITION(1) CHAR(6) ,
FORENAME POSITION(8) CHAR(9) ,
INITIAL POSITION(18) CHAR(1) ,
SURNAME POSITION(20) CHAR(10) ,
SEX POSITION(60) CHAR(1) ,
DOB POSITION(62) DATE EXTERNAL(10),
TELEPHONE POSITION(35) CHAR(4) ,
DATE_HIRED POSITION(40) DATE EXTERNAL(10),
JOB POSITION(51) CHAR(8) ,
SALARY POSITION(73) DECIMAL EXTERNAL(6),
BONUS POSITION(84) DECIMAL EXTERNAL(4),
COMMISSION POSITION(92) DECIMAL EXTERNAL(4),
DEPTNO POSITION(31) CHAR(3) ,
LOCID POSITION(99) CHAR(10))
//*

And then, finally, I think I'm supposed to use THIS query, to get an output of the of the total salaries for each department.:

Select all

SELECT SUM(SALARY + BONUS + COMMISSION) AS TOTAL_SALARIES, DEPTNO
FROM ZCON039.EMPLOYEE
GROUP BY EMPTNO

But now the problem is: When loading the data on the table using the JCL, I'm getting MAXCC=4 (EXTERNAL) error and my output is crapping giving an error about an "UNAVAILABLE RESOURCE".

Well, that's all the info I have, hope I can get some help

Thanks in advance,

[]'s!

by **dick scherrer** » Wed Nov 12, 2008 7:18 am

Hello,

Somewhere the system mentions which is the unavailable resource. What is unavailable?

What does the output from the load show? The load produces more information than has been posted.

by **hakghen** » Thu Nov 13, 2008 7:07 am

Hello,

Well dick, following the advice you gave me on other problem, I dropped the entire database and rebuilt it, the tables, indexes and submitted the jobs to populate them...

The result was just AWESOME. Something went wrong last time that messed my indexes, building everything again fixed and I got the following successfull result.:

Select all

SELECT DEPTNO,SUM(SALARY + BONUS + COMMISSION) AS TOTAL_SALARY
FROM ZCON039.EMPLOYEE GROUP BY DEPTNO ORDER BY 2;
---------+---------+---------+---------+---------+---------+---------+--
DEPTNO TOTAL_SALARY
---------+---------+---------+---------+---------+---------+---------+--
E01 81189.00
B01 95350.00
E21 287982.00
C01 312902.00
E11 321811.00
A00 358190.00
D21 365553.00
D11 658246.00
DSNE610I NUMBER OF ROWS DISPLAYED IS 8
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

Thanks for all the help!

by **dick scherrer** » Thu Nov 13, 2008 7:22 am

You're welcome - good to hear it is working

Thanks for letting us know,

d

IBM Mainframe Forum

Select Enlightment

Select Enlightment

Re: Select Enlightment

Re: Select Enlightment

Re: Select Enlightment

Re: Select Enlightment

Re: Select Enlightment