Select Enlightment



IBM's flagship relational database management system

Select Enlightment

Postby 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:

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:

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,

;)
[]'s,

Hakghen
User avatar
hakghen
 
Posts: 59
Joined: Thu Sep 11, 2008 8:15 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Select Enlightment

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

Hello,

Please post your query and an example of what you want for output.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Select Enlightment

Postby 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:

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.:

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


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

//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 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!
[]'s,

Hakghen
User avatar
hakghen
 
Posts: 59
Joined: Thu Sep 11, 2008 8:15 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Select Enlightment

Postby 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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Select Enlightment

Postby 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 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! ;)
[]'s,

Hakghen
User avatar
hakghen
 
Posts: 59
Joined: Thu Sep 11, 2008 8:15 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Select Enlightment

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

You're welcome - good to hear it is working :)

Thanks for letting us know,

d
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post